AR Customer Detail Report (SQL Script)

Enterprise Resource Planning (ERP) Account Receivables (AR)
1

This AR Customer Detail Report query retrieves detailed information about customer details in Oracle Fusion, including organizational details, contact and address information, tax identifiers, certification details, and account classifications.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query retrieves detailed customer information
  HOP.ORGANIZATION_NAME,
  HP.PARTY_TYPE,
  HP.PARTY_NAME CUSTOMER_NAME,
  HP.PARTY_NUMBER CUSTOMER_NUMBER,
  HP.ADDRESS1,
  HP.ADDRESS2,
  HP.ADDRESS3,
  HP.ADDRESS4,
  HP.CITY,
  HP.PROVINCE,
  HP.STATE,
  HP.COUNTY,
  HP.COUNTRY,
  HP.POSTAL_CODE,
  HP.CATEGORY_CODE CUSTOMER_CATEGORY,
  --HP.CERT_REASON_CODE,
  HP.CERTIFICATION_LEVEL CURRENT_CERTIFICATION_LEVEL,
  HP.DUNS_NUMBER_C DUNS_NUMBER,
  HP.EMAIL_ADDRESS,
--  HP.GSA_INDICATOR_FLAG,
  HP.JGZZ_FISCAL_CODE TAXPAYER_IDENTIFICATION_NUMBER,
--  HP.PARTY_ID, 
  HP.PREFERRED_NAME,
  HP.THIRD_PARTY_FLAG,
  HP.VALIDATED_FLAG,
  HP.PARTY_UNIQUE_NAME,
  HCA.CUSTOMER_CLASS_CODE CUSTOMER_CLASS,
  HCA.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
  HCA.ACCOUNT_TERMINATION_DATE,
  HCA.STATUS AS ACCOUNT_STATUS
FROM
  HZ_PARTIES HP,
  HZ_CUST_ACCOUNTS HCA,
  HZ_ORGANIZATION_PROFILES HOP
WHERE HCA.PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HOP.PARTY_ID
AND HCA.STATUS='A'
AND SYSDATE BETWEEN HOP.EFFECTIVE_START_DATE AND HOP.EFFECTIVE_END_DATE
ORDER BY HP.PARTY_NAME,
         HCA.ACCOUNT_NUMBER