| layout | default |
|---|---|
| title | AR Unapplied Receipts Register | Oracle EBS SQL Report |
| description | Detail unapplied receipts report with receipt number, date and amount – Oracle E-Business Suite SQL report |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, Unapplied, Receipts, Register, ar_receivable_applications_all, gl_daily_conversion_types, gl_daily_rates |
| permalink | /AR%20Unapplied%20Receipts%20Register/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Detail unapplied receipts report with receipt number, date and amount
Ledger, Operating Unit, As of GL Date, Receipt GL Date Low, Receipt GL Date High, Application GL Date Low, Application GL Date High, Receipt Last Updated From, Receipt Last Updated To, Entered Currency, Customer Name, Customer Number, Batch Name, Batch Source Name, Receipt Number Low, Receipt Number High, Revaluation Currency, Revaluation Rate Type, Revaluation Date, Show Receipt DFF Segments
ar_receivable_applications_all, gl_daily_conversion_types, gl_daily_rates, gl_ledgers, hr_operating_units, ar_payment_schedules_all, hz_cust_accounts, hz_parties, hz_cust_site_uses_all, hz_cust_acct_sites_all, hz_party_sites, hz_locations, fnd_territories_vl, hz_customer_profiles, ar_collectors, ar_receipt_methods, gl_code_combinations, ar_cash_receipts_all, ar_cash_receipt_history_all, ar_batches_all, ar_batch_sources_all, ce_bank_accounts, ce_bank_acct_uses_all, ce_bank_branches_v, mo_glob_org_access_tmp, dual
AR Open Balances Revaluation, AR Receipt Register, AR Aging
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
| Resource | Link |
|---|---|
| Excel Example Output | AR Unapplied Receipts Register 18-Jul-2023 085905.xlsx |
| Blitz Report™ XML Import | AR_Unapplied_Receipts_Register.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/ar-unapplied-receipts-register/ |
The AR Unapplied Receipts Register is a primary operational report for the Cash Application team. It identifies all customer payments that have been recorded in the system but not yet fully applied to specific invoices. Reducing the volume and value of unapplied receipts is a critical Key Performance Indicator (KPI) for the Accounts Receivable department, as it directly impacts the accuracy of customer aging and the efficiency of the collections process.
"Unapplied Cash" represents a disconnect in the Order-to-Cash cycle.
- Distorted Aging: A customer may have $1M in open invoices and $1M in unapplied cash. Their net balance is zero, but the aging report shows them as $1M past due, triggering unnecessary dunning actions.
- Customer Satisfaction: Calling a customer to demand payment when they have already paid (but the cash is sitting unapplied) damages the relationship.
- Accounting Risk: Unapplied cash sits in a suspense or liability account. If not cleared promptly, it can hide revenue recognition issues or duplicate payments.
The AR Unapplied Receipts Register serves as a worklist for resolving these items:
- Identification: Lists every receipt with a non-zero "Unapplied" balance.
- Aging: Shows how long the cash has been sitting unapplied (via Receipt Date), allowing managers to target old items first.
- Ownership: Includes the "Collector" assigned to the customer, enabling the distribution of the workload.
The report focuses on the "Open" portion of the payment schedules associated with receipts.
- Open Balance:
AR_PAYMENT_SCHEDULES_ALLis the driver. The query looks for records whereCLASS = 'PMT'andSTATUS = 'OP'(Open). TheAMOUNT_DUE_REMAININGon a payment schedule represents the unapplied amount. - Receipt Header:
AR_CASH_RECEIPTS_ALLprovides the Receipt Number, Date, and Currency. - Customer:
HZ_PARTIESandHZ_CUST_ACCOUNTSidentify the payer. - Bank:
CE_BANK_ACCOUNTSidentifies where the money was deposited.
- Selection: Finds receipts where the unapplied amount is not zero.
- As-Of Logic: If an "As of Date" is provided, the report must reconstruct the balance. It takes the current balance and "rolls back" any applications that happened after the As-Of Date to show what the balance was at that point in time.
- Exclusion: Typically excludes "Reversed" receipts, as they are no longer valid assets.
- As of GL Date: The most critical parameter for month-end reconciliation. It ensures the report matches the General Ledger balance for the "Unapplied Cash" account.
- Customer Name: Filters for a specific client.
- Receipt Number: Locates a specific payment.
- Collector: Segments the report by the responsible agent.
Q: What is the difference between "Unapplied" and "On Account"?
- Unapplied: The cash is in the system, but no decision has been made on how to use it.
- On Account: The cash has been deliberately placed "On Account" (e.g., a prepayment or deposit) to be used later.
- Note: Both result in a negative balance on the customer account, and this report typically includes both unless filtered.
Q: Why does the report show a receipt that I applied yesterday? A: If you run the report with an "As of Date" from last week, it will show the receipt as unapplied because it was unapplied at that time.
Q: How do I clear items from this report?
A: You must "Apply" the receipt to an invoice, a debit memo, or refund it to the customer. Once the AMOUNT_DUE_REMAINING on the receipt becomes zero, it drops off the list.
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics