[Fusion] SQL Query to get BU Name, Legal Entity & Ledger Information of Customer Site in Oracle Fusion ERP

Use the below query to get BU Name, Legal Entity & Ledger Information of Customer Site in Oracle Fusion ERP:

 

SELECT

    hp.party_id,

    hca.cust_account_id,

    hps.party_site_id,

    hcas.cust_acct_site_id,

    hcsu.site_use_id,

    hl.location_id,

    hp.party_number,

    hp.party_name,

    hca.account_number,

    hps.party_site_number,

    hcsu.site_use_code,

    hcsu.primary_flag,

    hl.address1,

    hl.address2,

    hl.address3,

    hl.address4,

    hl.city,

    hl.state,

    hl.county,

    hl.province,

    hl.country,

    hl.postal_code,

    hcsu.set_id,

    bu.bu_id,

    bu.bu_name,

    bu.short_code,

    bu.legal_entity_id,

    bu.primary_ledger_id    

FROM

    hz_parties                  hp,

    hz_party_sites              hps,

    hz_locations                hl,

    hz_cust_accounts            hca,

    hz_cust_acct_sites_all      hcas,

    hz_cust_site_uses_all       hcsu,

    fun_all_business_units_v    bu,

    FND_SETID_ASSIGNMENTS       seta

WHERE 1=1

    AND hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND hps.party_site_id = hcas.party_site_id

    AND hps.location_id = hl.location_id

    AND hca.cust_account_id     = hcas.cust_account_id

    AND hcas.cust_acct_site_id  = hcsu.cust_acct_site_id

    AND hcsu.site_use_code IN('SHIP_TO','BILL_TO')  

    AND seta.DETERMINANT_VALUE      = bu.BU_ID

    AND seta.SET_ID                 = hcsu.SET_ID

    AND seta.REFERENCE_GROUP_NAME   = 'HZ_CUSTOMER_ACCOUNT_SITE'