This AR Unapplied Receipt Amount query retrieves unapplied receipt amounts along with detailed customer and receipt information. It joins multiple tables to gather payment schedules, cash receipts, and accounting data, filtering out cleared statuses. The result includes organization details, payment types, currency, locations, and outstanding balances for billing sites.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query retrieves unapplied receipts with customer, payment, and location details.
HAOU.NAME ORGANIZATION_NAME,
HCSU.LOCATION,
HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
HP.PARTY_NUMBER,
ACRA.RECEIPT_NUMBER,
ACRA.RECEIPT_DATE,
APSA.GL_DATE,
ACRA.CURRENCY_CODE,
NVL(ACRH.AMOUNT, 0) RECEIPT_AMOUNT,
NVL(ACRH.ACCTD_AMOUNT, 0) ACCOUNTED_AMOUNT,
NVL(SUM(APSA.AMOUNT_DUE_REMAINING), 0) TOTAL_UNCLEARED_RECEIPTS,
ACRA.TYPE PAYMENT_TYPE
FROM
AR_PAYMENT_SCHEDULES_ALL APSA,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_PARTY_SITES HPS,
AR_CASH_RECEIPTS_ALL ACRA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
HZ_CUST_SITE_USES_ALL HCSU,
AR_CASH_RECEIPT_HISTORY_ALL ACRH,
HR_ALL_ORGANIZATION_UNITS HAOU,
HZ_PARTIES HP
WHERE
1 = 1
AND APSA.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID (+)
AND HCAS.CUST_ACCOUNT_ID (+) = HCA.CUST_ACCOUNT_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND APSA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID (+)
AND ARAA.PAYMENT_SCHEDULE_ID = APSA.PAYMENT_SCHEDULE_ID
AND APSA.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID(+)
AND HCSU.SITE_USE_CODE = 'BILL_TO'
AND ARAA.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID (+)
AND ACRH.STATUS NOT IN ('CLEARED')
AND HAOU.ORGANIZATION_ID = ACRA.ORG_ID
AND HP.PARTY_ID = HPS.PARTY_ID
AND ARAA.STATUS = 'UNAPP'
AND APSA.STATUS = 'OP'
AND APSA.CLASS = 'PMT'
GROUP BY
HAOU.NAME,
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HCA.ACCOUNT_NUMBER,
ACRA.RECEIPT_NUMBER,
ACRA.RECEIPT_DATE,
APSA.GL_DATE,
NVL(ACRH.AMOUNT, 0),
NVL(ACRH.ACCTD_AMOUNT, 0),
ACRA.CURRENCY_CODE,
ACRA.TYPE,
HCSU.LOCATION,
HPS.PARTY_ID,
ACRA.ORG_ID,
ACRH.CASH_RECEIPT_ID