SELECT customer.party_name, customer.account_number
, hp_ship.party_name ship_party_name, hps_ship.party_site_name ship_party_site_name, 'Ship to site use id - '
|| hcs_ship.site_use_id
|| '; '
|| hl_ship.address1
|| ','
|| hl_ship.city
|| ','
|| NVL (hl_ship.state, hl_ship.province)
|| ' '
|| hl_ship.postal_code ship_to_info
,bill_to.bill_to_info, bill_to.party_site_name Bill_party_site_name
,bill_to.party_name bill_party_name
FROM ont.oe_order_headers_all oha
,ar.hz_cust_site_uses_all hcs_ship
,ar.hz_cust_acct_sites_all hca_ship
,ar.hz_party_sites hps_ship
,ar.hz_locations hl_ship
,ar.hz_parties hp_ship
,apps.hz_cust_accounts ac
, (SELECT 'Bill to site use id - '
|| hcs_bill.site_use_id
|| '; '
|| hl_bill.address1
|| ','
|| hl_bill.city
|| ','
|| NVL (hl_bill.state, hl_bill.province)
|| ' '
|| hl_bill.postal_code bill_to_info
,hcs_bill.site_use_id
,hps_bill.party_site_name
,hp_bill.party_name
FROM ar.hz_cust_site_uses_all hcs_bill
,ar.hz_cust_acct_sites_all hca_bill
,ar.hz_party_sites hps_bill
,ar.hz_locations hl_bill
,ar.hz_parties hp_bill
,apps.hz_cust_accounts ac
WHERE hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.location_id = hl_bill.location_id
AND hp_bill.party_id = hps_bill.party_id
AND ac.cust_account_id = hca_bill.cust_account_id) bill_to
, (SELECT hp.party_name, ac.cust_account_id, ac.account_number
FROM ar.hz_parties hp, apps.hz_cust_accounts ac
WHERE ac.party_id = hp.party_id) customer
WHERE oha.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.location_id = hl_ship.location_id
AND hp_ship.party_id = hps_ship.party_id
AND ac.cust_account_id = hca_ship.cust_account_id
AND oha.order_number = XXXXX
AND oha.invoice_to_org_id = bill_to.site_use_id
AND customer.cust_account_id = oha.sold_to_org_id
AND oha.ordered_date > SYSDATE - 100
No comments:
Post a Comment