This AR Uncleared Receipt Amount query retrieves detailed payment and receipt information, including organization and customer account details, receipt numbers, dates, amounts, and payment types. It aggregates uncleared payment schedules, linking customer sites and locations, providing a comprehensive view of outstanding cash receipts.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves receipt details, aggregating outstanding cash receipts.
HAOU.NAME AS ORGANIZATION_NAME,
HCA.ACCOUNT_NUMBER,
HCA.ACCOUNT_NAME,
ACRA.RECEIPT_NUMBER,
ACRA.RECEIPT_DATE,
APSA.GL_DATE,
ACRA.TYPE AS PAYMENT_TYPE,
HL.COUNTRY,
HL.CITY,
ACRA.CURRENCY_CODE,
NVL(ACRHAA.AMOUNT, 0) AS RECEIPT_AMOUNT,
NVL(ACRHAA.ACCTD_AMOUNT, 0) AS ACCOUNTED_AMOUNT,
NVL(SUM(APSA.AMOUNT_DUE_REMAINING), 0) AS AMOUNT_DUE,
HPS.PARTY_ID,
ACRA.ORG_ID AS ORGANIZATION_ID,
ACRHAA.CASH_RECEIPT_ID
FROM
AR_PAYMENT_SCHEDULES_ALL APSA,
HZ_CUST_ACCOUNTS HCA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
AR_CASH_RECEIPTS_ALL ACRA,
AR_CASH_RECEIPT_HISTORY_ALL ACRHAA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE 1=1
AND APSA.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+)
AND APSA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID(+)
AND APSA.CLASS = 'PMT'
AND ARAA.STATUS = 'UNAPP'
AND APSA.STATUS = 'OP'
AND APSA.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID (+)
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID(+)
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID (+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
AND APSA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
AND ACRA.CASH_RECEIPT_ID = ACRHAA.CASH_RECEIPT_ID
AND ACRHAA.STATUS NOT IN ('CLEARED')
AND APSA.ORG_ID = HAOU.ORGANIZATION_ID
GROUP BY
ARAA.AMOUNT_APPLIED,
HCA.ACCOUNT_NUMBER,
HCA.ACCOUNT_NAME,
ACRA.RECEIPT_NUMBER,
ACRA.RECEIPT_DATE,
APSA.GL_DATE,
NVL(ACRHAA.AMOUNT, 0),
NVL(ACRHAA.ACCTD_AMOUNT, 0),
ACRA.CURRENCY_CODE,
ACRA.TYPE,
HL.COUNTRY,
HL.CITY,
HAOU.NAME,
HPS.PARTY_ID,
ACRA.ORG_ID,
ACRHAA.CASH_RECEIPT_ID