Receipt On Account & Unapplied – SQL Query
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.
NVL (SUM (DECODE (
FROM ar_receivable_applications_all ara,
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,
Receipt Status – Oracle Applications
A receipt can have one of the following statuses:
Approved: This receipt has been approved for automatic receipt creation. This status is only valid for automatic receipts.
Confirmed: The customer has approved the application of this receipt and their account balances have been updated within Receivables. This status is only valid for automatic receipts.
Remitted: This receipt has been remitted. This status is valid for both automatic and manually entered receipts.
Cleared: The payment of this receipt was transferred to your bank account and the bank statement has been reconciled within Receivables. This status is valid for both automatic and manually entered receipts.
Reversed: This receipt has been reversed. You can reverse a receipt when your customer stops payment on a receipt, if a receipt comes from an account with non-sufficient funds or if you want to re-enter and reapply it in Receivables. You can reverse cash receipts and miscellaneous transactions.