When working with Oracle E-Business Suite (EBS), especially in the context of customer data management, it’s often necessary to retrieve comprehensive information about a customer, including all the associated site use codes. Site use codes—such as BILL_TO, SHIP_TO, SOLD_TO, etc.—indicate the purpose of each customer site.
In this blog post, we’ll walk through a SQL query that pulls together key customer site details, aggregating the site use codes into a single, readable format.
Query 1: Aggregated Site Use Codes
SELECT HP.PARTY_NAME AS CUSTOMER_NAME, HPS.PARTY_SITE_NUMBER, HPS.PARTY_SITE_NAME, LISTAGG(HCSUA.SITE_USE_CODE, ', ')
WITHIN GROUP (ORDER BY HCSUA.SITE_USE_CODE) AS SITE_USE_CODES FROM HZ_CUST_ACCT_SITES_ALL HCSA, HZ_PARTY_SITES HPS, HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, HZ_CUST_SITE_USES_ALL HCSUA WHERE HPS.PARTY_SITE_ID = HCSA.PARTY_SITE_ID AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID AND HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_ID = :PARTY_ID AND HCSUA.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID GROUP BY HP.PARTY_NAME, HPS.PARTY_SITE_NUMBER, HPS.PARTY_SITE_NAME;
Key Highlights:
-
Tables Used:
-
HZ_PARTIES: Holds information about parties (individuals, organizations). -
HZ_CUST_ACCOUNTS: Links a party to a customer account. -
HZ_CUST_ACCT_SITES_ALL: Represents the locations for a customer. -
HZ_PARTY_SITES: Contains party site-level details. -
HZ_CUST_SITE_USES_ALL: Contains usage types (likeBILL_TO,SHIP_TO) for each site.
-
-
Use of
LISTAGG: TheLISTAGGfunction is used to combine all site use codes into a single string, grouped by customer site. This makes it much easier to read and report on.
Sample Output Format:
Query 2: Retrieve Detailed Address Information by Site Use Code
In addition to aggregating site use codes, you may often need the complete address information for each specific use type—like SHIP_TO, BILL_TO, INSTALL_AT, etc. The following extended query provides just that.
SELECT hp.party_id PARTY_ID ,hca.cust_account_id CUST_ACCOUNT_ID ,hp.party_number PARTY_NUMBER ,hps.party_site_number PARTY_SITE_NUMBER ,hps.party_site_name PARTY_SITE_NAME ,hp.party_name PARTY_NAME ,hp.party_type PARTY_TYPE ,hp.status PARTY_STATUS ,hca.account_number CUSTOMER_NUMBER -- SHIP_TO Details ,HCSUA_SHIP.LOCATION SHIP_TO_LOCATION ,HL_SHIP.ADDRESS1 SHIP_TO_ADDRESS_LINE1 ,HL_SHIP.ADDRESS2 SHIP_TO_ADDRESS_LINE2 ,HL_SHIP.ADDRESS3 SHIP_TO_ADDRESS_LINE3 ,HL_SHIP.ADDRESS4 SHIP_TO_ADDRESS_LINE4 ,HL_SHIP.CITY SHIP_TO_CITY ,HL_SHIP.STATE SHIP_TO_STATE ,HL_SHIP.POSTAL_CODE SHIP_TO_POSTAL_CODE ,HL_SHIP.COUNTRY SHIP_TO_COUNTRY ,HL_SHIP.COUNTY SHIP_TO_COUNTY ,HL_SHIP.PROVINCE SHIP_TO_PROVINCE -- BILL_TO Details ,HCSUA_BILL.LOCATION BILL_TO_LOCATION ,HL_BILL.ADDRESS1 BILL_TO_ADDRESS_LINE1 ,HL_BILL.ADDRESS2 BILL_TO_ADDRESS_LINE2 ,HL_BILL.ADDRESS3 BILL_TO_ADDRESS_LINE3 ,HL_BILL.ADDRESS4 BILL_TO_ADDRESS_LINE4 ,HL_BILL.CITY BILL_TO_CITY ,HL_BILL.STATE BILL_TO_STATE ,HL_BILL.POSTAL_CODE BILL_TO_POSTAL_CODE ,HL_BILL.COUNTRY BILL_TO_COUNTRY ,HL_BILL.COUNTY BILL_TO_COUNTY ,HL_BILL.PROVINCE BILL_TO_PROVINCE -- DELIVER_TO Details ,HCSUA_DELIVER.LOCATION DELIVER_TO_LOCATION ,HL_DELIVER.ADDRESS1 DELIVER_TO_ADDRESS_LINE1 ,HL_DELIVER.ADDRESS2 DELIVER_TO_ADDRESS_LINE2 ,HL_DELIVER.ADDRESS3 DELIVER_TO_ADDRESS_LINE3 ,HL_DELIVER.ADDRESS4 DELIVER_TO_ADDRESS_LINE4 ,HL_DELIVER.CITY DELIVER_TO_CITY ,HL_DELIVER.STATE DELIVER_TO_STATE ,HL_DELIVER.POSTAL_CODE DELIVER_TO_POSTAL_CODE ,HL_DELIVER.COUNTRY DELIVER_TO_COUNTRY ,HL_DELIVER.COUNTY DELIVER_TO_COUNTY ,HL_DELIVER.PROVINCE DELIVER_TO_PROVINCE -- INSTALL_TO Details ,HCSUA_INSTALL.LOCATION INSTALL_TO_LOCATION ,HL_INSTALL.ADDRESS1 INSTALL_TO_ADDRESS_LINE1 ,HL_INSTALL.ADDRESS2 INSTALL_TO_ADDRESS_LINE2 ,HL_INSTALL.ADDRESS3 INSTALL_TO_ADDRESS_LINE3 ,HL_INSTALL.ADDRESS4 INSTALL_TO_ADDRESS_LINE4 ,HL_INSTALL.CITY INSTALL_TO_CITY ,HL_INSTALL.STATE INSTALL_TO_STATE ,HL_INSTALL.POSTAL_CODE INSTALL_TO_POSTAL_CODE ,HL_INSTALL.COUNTRY INSTALL_TO_COUNTRY ,HL_INSTALL.COUNTY INSTALL_TO_COUNTY ,HL_INSTALL.PROVINCE INSTALL_TO_PROVINCE -- SOLD_TO Details ,HCSUA_SOLD.LOCATION SOLD_TO_LOCATION ,HL_SOLD.ADDRESS1 SOLD_TO_ADDRESS_LINE1 ,HL_SOLD.ADDRESS2 SOLD_TO_ADDRESS_LINE2 ,HL_SOLD.ADDRESS3 SOLD_TO_ADDRESS_LINE3 ,HL_SOLD.ADDRESS4 SOLD_TO_ADDRESS_LINE4 ,HL_SOLD.CITY SOLD_TO_CITY ,HL_SOLD.STATE SOLD_TO_STATE ,HL_SOLD.POSTAL_CODE SOLD_TO_POSTAL_CODE ,HL_SOLD.COUNTRY SOLD_TO_COUNTRY ,HL_SOLD.COUNTY SOLD_TO_COUNTY ,HL_SOLD.PROVINCE SOLD_TO_PROVINCE FROM hz_parties hp ,hz_cust_accounts_all hca ,hz_cust_acct_sites_all hcsa ,hz_party_sites hps -- BILL_TO ,hz_cust_site_uses_all hcsua_bill ,hz_locations hl_bill -- SHIP_TO ,hz_cust_site_uses_all hcsua_ship ,hz_locations hl_ship -- DELIVER_TO ,hz_cust_site_uses_all hcsua_deliver ,hz_locations hl_deliver -- INSTALL_AT ,hz_cust_site_uses_all hcsua_install ,hz_locations hl_install -- SOLD_TO ,hz_cust_site_uses_all hcsua_sold ,hz_locations hl_sold WHERE 1=1 AND HP.PARTY_ID = HCA.PARTY_ID AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID AND HPS.PARTY_SITE_ID = HCSA.PARTY_SITE_ID AND HP.PARTY_ID = 11923735 --11958808 -- BILL_TO join AND HCSUA_BILL.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID AND HCSUA_BILL.SITE_USE_CODE(+) = 'BILL_TO' AND HL_BILL.LOCATION_ID(+) = HPS.LOCATION_ID -- SHIP_TO join AND HCSUA_SHIP.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID AND HCSUA_SHIP.SITE_USE_CODE(+) = 'SHIP_TO' AND HL_SHIP.LOCATION_ID(+) = HPS.LOCATION_ID -- DELIVER_TO join AND HCSUA_DELIVER.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID AND HCSUA_DELIVER.SITE_USE_CODE(+) = 'DELIVER_TO' AND HL_DELIVER.LOCATION_ID(+) = HPS.LOCATION_ID -- INSTALL_AT join AND HCSUA_INSTALL.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID AND HCSUA_INSTALL.SITE_USE_CODE(+) = 'INSTALL_AT' AND HL_INSTALL.LOCATION_ID(+) = HPS.LOCATION_ID -- SOLD_TO join AND HCSUA_SOLD.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID AND HCSUA_SOLD.SITE_USE_CODE(+) = 'SOLD_TO' AND HL_SOLD.LOCATION_ID(+) = HPS.LOCATION_ID ;
What This Query Does:
-
Returns full customer identity and account information, including party ID, customer account number, and party site details.
-
Retrieves separate address blocks for each type of customer site use:
-
SHIP_TO
-
BILL_TO
-
DELIVER_TO
-
INSTALL_AT
-
SOLD_TO
-
Each section includes standard address fields such as ADDRESS_LINE1, CITY, STATE, POSTAL_CODE, and COUNTRY.
Comments
Post a Comment