This AR Billing History Report query generates a Customer Outstanding Invoice Report that provides detailed information about open transactions for customers.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project).
-- This query generates a report detailing outstanding invoices for customers.
HAOU.NAME ORGANIZATION_NAME,
SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HL.CITY,
APSA.TRX_NUMBER TRANSACTION_NUMBER,
APSA.DUE_DATE,
APSA.TRX_DATE TRANSACTION_DATE,
APSA.AMOUNT_DUE_ORIGINAL,
APSA.AMOUNT_DUE_REMAINING,
NVL (APSA.AMOUNT_APPLIED, 0) APPLIED_AMOUNT,
NVL (APSA.AMOUNT_CREDITED, 0) CREDITED_AMOUNT,
NVL (APSA.AMOUNT_ADJUSTED, 0) ADJUSTED_AMOUNT,
APSA.INVOICE_CURRENCY_CODE CURRENCY_CODE,
AC.NAME COLLECTOR_NAME,
AC.COLLECTOR_ID,
APSA.CUSTOMER_TRX_ID,
RCTA.PURCHASE_ORDER,
APSA.PAYMENT_SCHEDULE_ID,
HCAS.CUST_ACCT_SITE_ID,
HL.STATE,
APSA.CUSTOMER_ID,
HAOU.ORGANIZATION_ID
FROM
AR_PAYMENT_SCHEDULES_ALL APSA,
RA_CUSTOMER_TRX_ALL RCTA,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES HCAS,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
RA_TERMS RT,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
AR_COLLECTORS AC,
HZ_CUSTOMER_PROFILES_F HCPF1,
HZ_CUSTOMER_PROFILES_F HCPF2,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND APSA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND RCTA.PREVIOUS_CUSTOMER_TRX_ID IS NULL
AND RCTA.BILL_TO_SITE_USE_ID = HCSUA.SITE_USE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID = HP.PARTY_ID(+)
AND RCTA.TERM_ID = RT.TERM_ID(+)
AND HCSUA.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HCA.CUST_ACCOUNT_ID = HCPF1.CUST_ACCOUNT_ID
AND NVL (HCPF2.COLLECTOR_ID, HCPF1.COLLECTOR_ID) = AC.COLLECTOR_ID
AND HCPF1.SITE_USE_ID IS NULL
AND HCSUA.SITE_USE_ID = HCPF2.SITE_USE_ID(+)
AND APSA.CLASS != 'PMT'
AND HAOU.ORGANIZATION_ID = APSA.ORG_ID
ORDER BY
HP.PARTY_NAME,
HCA.CUST_ACCOUNT_ID,
APSA.INVOICE_CURRENCY_CODE,
APSA.TRX_NUMBER,
APSA.DUE_DATE