COALESCE(c1.agent_company, c2.agent_company) AS sub_agent_company, q.circuit_id, q.mrc, CASE WHEN chargebacks.mrc < 0 THEN q.mrc + chargebacks.mrc ELSE q.mrc END AS mrc, CASE WHEN chargebacks.mrc < 0 THEN chargebacks.mrc ELSE 0 END AS chargeback_amount, q.account_id, agents.is_account_past_due(q.account_id), q.expected_mrc, q.bill_start_date, q.om_customer_nm, q.order_type_cd, q.base_order_id, q.service_type_cd, q.service_bandwidth_bps, CASE WHEN q.term_cd = \'NOCH\' or q.term_cd = \'UK\' THEN agents.get_term_fallback(q.base_order_id) ELSE q.term_cd END AS term_cd, q.customer_nm, u.last_nm || \', \' || u.first_nm as channel_manager_nm, q.order_submitted, extract(epoch from q.bill_start_date) as epoch_timestamp, CASE WHEN ((:previous_bill_date)::date - \'1 month\'::interval) < q.bill_start_date THEN false ELSE true END AS was_previously_sold, CASE WHEN ((:previous_bill_date)::date - \'1 month\'::interval) < q.bill_start_date AND q.order_type_cd IN(\'NW\',\'RW\') AND q.term_cd NOT IN (\'NOCH\',\'UK\') AND agents.get_term_sub(q.term_cd) != \'MTM\' THEN true ELSE false END AS apply_upfront, CASE WHEN agents.is_upfront_blocked(q.base_order_id) THEN true ELSE false END AS block_upfront, q.expected_nrc, COALESCE(c.assuming_agent_company_id, q.parent_agent_company_id) as assuming_agent_company_id, c.assumed_revenue_level, agents.get_order_submitted_fallback(q.base_order_id) as ica_order_submitted, q.product_cd FROM ( SELECT q.circuit_id, q.mrc,q.expected_mrc, q.expected_nrc, bo.bill_start_date, bo.order_submitted, bo.order_type_cd, bo.service_type_cd, omc.om_customer_nm, ac.agent_company_id, bo.base_order_id, bo.term_cd, q.customer_nm, q.customer_service_bundle_id, COALESCE(ac.parent_agent_company_id, ac.agent_company_id) as parent_agent_company_id, q.account_id, q.first_applied, bo.product_cd, sb.service_bandwidth_bps FROM ( SELECT * FROM crm.billing_snapshot WHERE statement_date = (:previous_bill_date)::date ) q JOIN om."BASE_ORDERS" bo ON q.circuit_id = bo.uss_circuit_id LEFT OUTER JOIN om."SERVICE_BANDWIDTHS" sb ON sb.service_bandwidth_id = bo.service_bandwidth_id JOIN om."OM_CUSTOMERS" omc ON bo.om_customer_id = omc.om_customer_id