Oracle Cloud ERP (Fusion SaaS) – Sales Order – Customer Details
Sales Order with Customer Org Detials
SELECT
dha.org_id,
xabu.bu_name,
dha.order_number,
dha.created_by,
dha.creation_date,
dha.source_order_system,
dha.source_order_number,
dha.orig_sys_document_ref,
hp.party_number,
hp.party_name,
hp.party_id,
hca.account_number,
dheb.context_code,
doa.address_use_type bill_to_address_use_type,
hcasa.cust_acct_site_id bill_to_cust_acct_site_id,
hcsu.site_use_id bill_to_site_use_id,
dosha.address_use_type ship_to_type,
dosha.contact_id ship_to_contact_id,
dosha.party_site_id ship_to_party_site_id,
ship_hps.party_site_number ship_to_party_site_number
FROM
fusion.doo_headers_all dha,
(
SELECT
context_code,
attribute_char1,
header_id
FROM
fusion.doo_headers_eff_b
WHERE
context_code = 'xx'
) dheb,
fusion.doo_order_addresses doa,
fusion.doo_order_addresses dosha,
fusion.hz_cust_accounts hca,
fusion.hz_cust_acct_sites_all hcasa,
fusion.hz_cust_site_uses_all hcsu,
fusion.hz_party_sites hps,
fusion.hz_locations hl,
fusion.hz_parties hp,
xxfun_all_business_units_v xabu,
fusion.hz_party_sites ship_hps
WHERE
1 = 1
AND dha.change_version_number = (
SELECT
MAX(dha_max.change_version_number)
FROM
fusion.doo_headers_all dha_max
WHERE
dha_max.order_number = dha.order_number
AND dha_max.open_flag = 'Y'
)
AND xabu.bu_id = dha.org_id
AND dha.header_id = dheb.header_id (+)
AND dha.header_id = doa.header_id
AND dha.header_id = dosha.header_id
AND dosha.address_use_type = 'SHIP_TO'
AND doa.address_use_type = 'BILL_TO'
AND dha.sold_to_party_id = hca.party_id
AND hp.party_id = hca.party_id
AND hcsu.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcsu.site_use_id = doa.cust_acct_site_use_id
AND hps.party_site_id = hcasa.party_site_id
AND hps.location_id = hl.location_id
AND ship_hps.party_site_id = dosha.party_site_id
Comments |0|
Category: OracleSaaS