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