Oracle Applications – Receipt On Account & Unapplied – SQL Query
Posted by
Jiltin 5 March, 2009 5,122 views
A cash receipt is an applied receipt if it is associated with a customer number and an open invoice(s).
An unapplied receipt is a cash receipt that can be applied to a customer account if it is associated with a customer number but not associated with an invoice (that is, there is no invoice for the sale or the invoice number is unknown).
An on-account receipt (cash-in-advance) is:
* recorded to the customer account
* applied against the invoice when the invoice is generated
An unidentified receipt is from an unknown source.
An Application Advice Form is required for any receipt labeled as unidentified.
Here is the simple query to find the On-Account and UnApplied amount for a customer.
SELECT NVL (SUM (DECODE (
ara.STATUS,
‘ACC’, -amount_applied,
0
)), 0),
NVL (SUM (DECODE (
ara.STATUS,
‘UNAPP’, -amount_applied,
0
)), 0)
FROM ar_receivable_applications_all ara,
ar_cash_receipts_all acr
WHERE ara.cash_receipt_id = acr.cash_receipt_id
AND acr.customer_site_use_id = ‘&&site_use_id’
AND ARA.STATUS IN ( ‘ACC’, ‘UNAPP’ )
AND ara.confirmed_flag IS NULL
GROUP BY acr.currency_code,
acr.customer_site_use_id;
ara.STATUS,
‘ACC’, -amount_applied,
0
)), 0),
NVL (SUM (DECODE (
ara.STATUS,
‘UNAPP’, -amount_applied,
0
)), 0)
FROM ar_receivable_applications_all ara,
ar_cash_receipts_all acr
WHERE ara.cash_receipt_id = acr.cash_receipt_id
AND acr.customer_site_use_id = ‘&&site_use_id’
AND ARA.STATUS IN ( ‘ACC’, ‘UNAPP’ )
AND ara.confirmed_flag IS NULL
GROUP BY acr.currency_code,
acr.customer_site_use_id;
Categories :
11i Scripts, Receivables
Comments
No comments yet.