The blog provides the SQL Query to get On-Account Receipt Amount Total in Oracle Receivables.
What is On-account receipt in Receivables ?
Oracle Receivables R12 allows to apply on-account receipts to customer’s open debit items. Consider and example Customer receives the goods worth $1000 but was not happy with the received goods and thus company agrees to credit $200 to customer account. When customer makes the payment of $800, then $200 is credited as customer on-account
Oracle Receivables On-Account Receipt Amount
SELECT NVL(SUM(AR_PAYMENT_SCH_ALL_TBL.AMOUNT_DUE_REMAINING), 0) OnAccount_Recpt_TotalAmt
FROM HZ_CUST_AGCC_TBLOUNTS_ALL HZ_CUST_AGCC_TBLTS_ALL_TBL,
AR_PAYMENT_SCHEDULES_ALL AR_PAYMENT_SCH_ALL_TBL,
AR_RECEIVABLE_APPLICATIONS_ALL AR_RECV_APPLS_ALL_TBL,
HZ_HZ_CUST_ACCT_ALL_TBL_SITES_ALL HZ_CUST_AGCC_TBLT_SITES ,
HZ_HZ_PARTY_SITES_TBLS HZ_PARTY_SITES_TBL,
HZ_LOCATIONS LOC,
HZ_CUST_HZ_SITE_USES_TBL_ALL HZ_SITE_USES_TBL,
AR_CASH_RECEIPTS_ALL AR_CASH_RECPT_ALL_TBL,
AR_CASH_RECEIPT_HISTORY_ALL AR_CASH_RECPT_HIST_TBL,
GL_CODE_COMBINATIONS GCC_TBL
WHERE 1=1
AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_ID = HZ_CUST_AGCC_TBLTS_ALL_TBL.CUST_AGCC_TBLOUNT_ID
AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_ID = HZ_CUST_AGCC_TBLTS_ALL_TBL.CUST_AGCC_TBLOUNT_ID
AND HZ_CUST_AGCC_TBLT_SITES .HZ_PARTY_SITES_TBL_ID = HZ_PARTY_SITES_TBL.HZ_PARTY_SITES_TBL_ID
AND LOC.LOCATION_ID = HZ_PARTY_SITES_TBL.LOCATION_ID
AND AR_PAYMENT_SCH_ALL_TBL.CASH_RECEIPT_ID = AR_CASH_RECPT_ALL_TBL.CASH_RECEIPT_ID
AND AR_CASH_RECPT_ALL_TBL.CASH_RECEIPT_ID = AR_CASH_RECPT_HIST_TBL.CASH_RECEIPT_ID
AND AR_CASH_RECPT_HIST_TBL.AGCC_TBLOUNT_CODE_COMBINATION_ID = GCC_TBL.CODE_COMBINATION_ID
AND AR_PAYMENT_SCH_ALL_TBL.CLASS = 'PMT'
AND AR_PAYMENT_SCH_ALL_TBL.CASH_RECEIPT_ID = AR_RECV_APPLS_ALL_TBL.CASH_RECEIPT_ID
AND AR_RECV_APPLS_ALL_TBL.STATUS IN ('AGCC_TBL')
AND AR_PAYMENT_SCH_ALL_TBL.STATUS = 'OP'
AND HZ_SITE_USES_TBL.SITE_USE_CODE = 'BILL_TO'
AND HZ_SITE_USES_TBL.HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID = HZ_CUST_AGCC_TBLT_SITES .HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID
AND NVL(HZ_SITE_USES_TBL.STATUS, 'A') = 'A'
AND HZ_CUST_AGCC_TBLTS_ALL_TBL.CUST_AGCC_TBLOUNT_ID = HZ_CUST_AGCC_TBLT_SITES .CUST_AGCC_TBLOUNT_ID
AND HZ_CUST_AGCC_TBLT_SITES .HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID = HZ_SITE_USES_TBL.HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID
AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_ID = HZ_CUST_AGCC_TBLT_SITES .CUST_AGCC_TBLOUNT_ID
AND HZ_CUST_AGCC_TBLTS_ALL_TBL.AGCC_TBLOUNT_NUMBER = '<ENTER_ACCOUNT_NUMBER>'
AND AR_PAYMENT_SCH_ALL_TBL.TRX_DATE <= '<ENTER_TRX_DATE>'
AND TRUNC(AR_PAYMENT_SCH_ALL_TBL.GL_DATE) <= '<ENTER_GL_DATE>'
AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_SITE_USE_ID = HZ_SITE_USES_TBL.SITE_USE_ID HAVING
NVL(SUM(AR_RECV_APPLS_ALL_TBL.AMOUNT_APPLIED), 0) > 0;