query.sql
text/x-sql
SELECT bso.service_order_id
, service_order_detail_id
, bsod.service_id AS to_retail_service_id
, bsod.quantity AS to_retail_service_quantity
, sst.service_semantic_type_code AS to_retail_service_semantic_type_code
, tss.service_id AS to_contract_service_id
, tsb.quantity * bsod.quantity AS to_contract_service_quantity
, tsst.service_semantic_type_code AS to_contract_service_semantic_type_code
, bso.account_id AS client_account_id
, bsod.account_instance_id AS client_account_instance_id
, aa.account_id AS inquent_plc_account_id
, bso.status_timestamp AS timestamp
FROM billing.service_order AS bso
JOIN billing.service_order_detail AS bsod USING (service_order_id)
JOIN billing.service_order_detail_type USING (service_order_detail_type_id)
JOIN service.service AS ss USING (service_id)
JOIN service.semantic_type AS sst USING (service_semantic_type_id)
JOIN account.breakdown AS ab ON (ab.account_id = bso.account_id)
JOIN account.account AS aa ON (aa.account_id = ab.parent_account_id)
JOIN account.account AS ap
ON (ap.account_id = aa.parent_account_id AND ap.parent_account_id IS NULL)
JOIN service.breakdown AS tsb ON (ss.service_id = tsb.service_id)
JOIN service.service AS tss
ON (tsb.subservice_id = tss.service_id AND tss.account_id = ap.account_id)
JOIN service.semantic_type AS tsst
ON (tsst.service_semantic_type_id = tss.service_semantic_type_id)
LEFT JOIN billing.service_order_service_change AS bsosc
ON (bsod.service_order_detail_id = bsosc.service_order_detail_id_new)
LEFT JOIN service.exposure AS tse ON (tss.service_id = tse.service_id)
WHERE service_order_detail_type_code = 'create'
AND bsosc.service_order_detail_id_new IS NULL
AND (tse.account_id = aa.account_id OR tse.account_id IS NULL);