Overview: This query is used to fetch customers sites details in Oracle Application Release 12
select hp.party_name customer_name, hca.account_number customer_number, hpsa.PARTY_SITE_NAME, hpsa.PARTY_SITE_NUMBER, (select name from hr_operating_units where organization_id=hcas.org_id) operating_unit, hcsu_bill.location bill_to_location, hcsu_bill.location ship_to_location from hz_cust_accounts hca, hz_parties hp, hz_party_sites hpsa, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu_bill, hz_cust_site_uses_all hcsu_ship where hca.party_id = hp.party_id and hp.party_id=hpsa.party_id and hcas.cust_account_id=hca.cust_account_id and hcas.party_site_id=hpsa.party_site_id and hcas.CUST_ACCT_SITE_ID=hcsu_bill.CUST_ACCT_SITE_ID(+) and hcas.CUST_ACCT_SITE_ID=hcsu_ship.CUST_ACCT_SITE_ID(+) and hcsu_bill.site_use_code(+)='BILL_TO' and hcsu_ship.site_use_code(+)='SHIP_TO' and hca.status='A' and hcas.status='A'