AR Customer Site Listing (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR customer site listing query retrieves detailed customer account and billing site information in Oracle Fusion, including customer master data, account information, address details, and billing site.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves detailed customer account and billing site information
  HP.PARTY_NAME CUSTOMER_NAME,
  HCA.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
  HCA.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
  HPS.PARTY_SITE_NUMBER CUSTOMER_SITE_NUMBER,
  HPS.PARTY_SITE_NAME CUSTOMER_SITE_NAME,
  HCSUA.SITE_USE_CODE SITE_USE_TYPE,
  HCASA.CUSTOMER_CATEGORY_CODE,
  HCSUA.STATUS SITE_USE_STATUS,
  HCSUA.PRIMARY_FLAG,
  HL.ADDRESS1,
  HL.ADDRESS2,
  HL.ADDRESS3,
  HL.ADDRESS4,
  HL.CITY,
  HL.STATE,
  HL.COUNTRY,
  HL.POSTAL_CODE 
FROM
  HZ_PARTIES HP,
  HZ_CUST_ACCOUNTS HCA,
  HZ_CUST_ACCT_SITES_ALL HCASA,
  HZ_PARTY_SITES HPS,
  HZ_LOCATIONS HL,
  HZ_CUST_SITE_USES_ALL HCSUA
WHERE
  HCA.PARTY_ID = HP.PARTY_ID
  AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
  AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
  AND HPS.LOCATION_ID = HL.LOCATION_ID
  AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
  AND HCSUA.SITE_USE_CODE='BILL_TO'
  AND HCA.STATUS='A'
ORDER BY
  HP.PARTY_NAME,
  HPS.PARTY_SITE_NUMBER