SELECT
okh.org_id
,
okh.contract_number
,
okh.contract_number_modifier
,
okh.ship_to_site_use_id
,
ship_hps.party_site_number ship_to_site_number
,
ship_hcas.account_number ship_to_customer_number
,
okh.bill_to_site_use_id
,
bill_hps.party_site_number bill_to_site_number
,
bill_hcas.account_number bill_to_customer_number
FROM
okc_k_headers_all_b okh
,
hz_cust_site_uses_all ship_hcsua
,
hz_cust_acct_sites_all ship_hcasa
,
hz_party_sites ship_hps
,
hz_cust_accounts ship_hcas
,
hz_cust_site_uses_all bill_hcsua
,
hz_cust_acct_sites_all bill_hcasa
,
hz_party_sites bill_hps
,
hz_cust_accounts bill_hcas
WHERE 1 = 1
AND
okh.contract_number = '11346850'
AND
ship_hcsua.site_use_code = 'SHIP_TO'
AND
okh.ship_to_site_use_id = ship_hcsua.site_use_id
AND
ship_hcasa.cust_acct_site_id = ship_hcsua.cust_acct_site_id
AND
ship_hcasa.party_site_id = ship_hps.party_site_id
AND
ship_hps.party_id = ship_hcas.party_id
AND
ship_hcas.cust_account_id = ship_hcasa.cust_account_id
AND
bill_hcsua.site_use_code = 'BILL_TO'
AND
okh.bill_to_site_use_id = bill_hcsua.site_use_id
AND
bill_hcasa.cust_acct_site_id = bill_hcsua.cust_acct_site_id
AND
bill_hcasa.party_site_id = bill_hps.party_site_id
AND
bill_hps.party_id = bill_hcas.party_id
AND
bill_hcas.cust_account_id = bill_hcasa.cust_account_id
AND
ship_hcasa.status = 'A'
AND
ship_hcsua.status = 'A'
AND
ship_hcas.status = 'A'
AND
ship_hps.status = 'A'
AND
bill_hcasa.status = 'A'
AND
bill_hcsua.status = 'A'
AND
bill_hcas.status = 'A'
AND
bill_hps.status = 'A'
AND
okh.org_id = 111
AND
okh.scs_code = 'SUBSCRIPTION'
AND
okh.sts_code = 'ACTIVE'
ORDER BY okh.last_update_date DESC
