This AR Customer Summary Report query retrieves customer account and site-level details, including customer name, account status, site use information, address, tax classification, and organizational context.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves customer details
HOP.ORGANIZATION_NAME,
SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
DECODE(HCA.STATUS,'A','ACTIVE','I','INACTIVE',HCA.STATUS) CUSTOMER_STATUS,
HP.CATEGORY_CODE CUSTOMER_CATEGORY_CODE,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
AL1.MEANING CUSTOMER_ACCOUNT_STATUS,
HPS.PARTY_SITE_NAME CUSTOMER_SITE_NAME,
HCSUA.SITE_USE_CODE,
AL.MEANING SITE_USE_DESCRIPTION,
SUBSTRB (HL.ADDRESS1, 1, 30) ADDRESS,
SUBSTRB (HL.CITY, 1, 15) CITY,
SUBSTRB (HL.STATE, 1, 2) STATE,
SUBSTRB (HL.POSTAL_CODE, 1, 10) ZIP_CODE,
HCSUA.TAX_CLASSIFICATION,
HCSUA.TAX_ROUNDING_RULE
FROM
HZ_ORGANIZATION_PROFILES HOP,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_LOCATIONS HL,
AR_LOOKUPS AL,
AR_LOOKUPS AL1
WHERE
1 = 1
AND HOP.PARTY_ID=HP.PARTY_ID
AND HP.PARTY_ID=HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID(+)
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID(+)
AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID =HL.LOCATION_ID(+)
AND AL.LOOKUP_CODE(+) = HCSUA.SITE_USE_CODE
AND AL.LOOKUP_TYPE(+) = 'SITE_USE_CODE'
AND AL1.LOOKUP_CODE(+) = NVL (HCA.STATUS, 'A')
AND AL1.LOOKUP_TYPE(+) = 'CODE_STATUS'
ORDER BY
HP.PARTY_NAME,
HCA.ACCOUNT_NUMBER,
HCSUA.SITE_USE_CODE,
HL.ADDRESS1