AR Miscellaneous Receipts – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Receivables · all Receivables reports

Overview

Receivables Miscellaneous Receipts Imported from Concurrent Program Application: Receivables Source: Miscellaneous Receipts Register Short Name: ARRXMTRG

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Reporting Level, Reporting Context, Reporting Detail, Balancing Segment Low, Balancing Segment High, Accounting Period, GL Date From, GL Date To, Entered Currency, Customer Name, Customer Account Number, Receipt Method, Receipt Status, Batch Name Low, Batch Name High, Receipt Date From, Receipt Date To, Deposit Date From, Deposit Date To, Receipt Number Low, Receipt Number High, Document Sequence Name, Document Number Low, Document Number High

Oracle EBS Tables Used

ar_batches_all, ar_cash_receipts_all, ap_inv_selection_criteria_all, ap_checks_all, ra_customer_trx_all, hr_all_organization_units, gl_ledgers, ar_cash_receipt_history_all, ar_batch_sources_all, ar_receipt_methods, ar_distribution_sets_all, ar_receivables_trx_all, ar_misc_cash_distributions_all, ar_distributions_all, gl_code_combinations_kfv, hz_cust_accounts, hz_parties, hz_cust_site_uses_all, hz_cust_acct_sites_all, hz_party_sites, hz_customer_profiles, ar_collectors, ce_bank_accounts, ce_bank_acct_uses_all, ce_bank_branches_v, ar_vat_tax_all, fnd_document_sequences, iby_fndcpt_pmt_chnnls_tl, iby_fndcpt_tx_extensions, fnd_application, iby_pmt_instr_uses_all, iby_ext_bank_accounts, iby_creditcard

Report Categories

Enginatics

Related Reports

GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, AR Receipt Register, CE Cleared Transactions, AR Transactions and Payments, GL Account Analysis (Drilldown), CE Bank Statement and Reconciliation, GL Account Analysis 11g

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource	Link
Excel Example Output	AR Miscellaneous Receipts 24-Jul-2017 151304.xlsx
Blitz Report™ XML Import	AR_Miscellaneous_Receipts.xml
SQL Source	AR Miscellaneous Receipts.sql
Full Report on Enginatics	www.enginatics.com/reports/ar-miscellaneous-receipts/

Case Study & Technical Analysis: AR Miscellaneous Receipts

1. Executive Summary

Business Problem

Miscellaneous receipts in Oracle Receivables represent cash inflows that are not directly related to customer invoices. These include interest income, investment returns, tax refunds, or stock sales. Because these transactions do not follow the standard “Invoice -> Receipt” matching process, they are often difficult to track, reconcile, and audit. Finance teams struggle with:

• Visibility: Lack of a centralized view for non-trade cash receipts.
• Reconciliation: Difficulty in tracing miscellaneous cash to the General Ledger, especially when complex distribution sets are used.
• Audit Compliance: Ensuring that all non-standard cash inflows are properly authorized, categorized, and accounted for.
• Tax Reporting: Identifying VAT or other taxes collected on miscellaneous income.

Solution Overview

The AR Miscellaneous Receipts report provides a detailed register of all non-invoice related cash receipts. It captures the entire lifecycle of the receipt, from creation to remittance and clearance. By exposing the underlying accounting distributions, tax codes, and reference information, it allows the Treasury and Accounting teams to fully validate and reconcile miscellaneous cash entries against bank statements and GL balances.

Key Benefits

• Complete Audit Trail: Tracks the status history of receipts (Approved, Remitted, Cleared, Reversed).
• Accounting Detail: Shows the specific GL accounts credited (e.g., Interest Income, Gain/Loss) for each receipt line.
• Tax Compliance: Identifies tax codes and amounts associated with taxable miscellaneous receipts.
• Reference Tracking: Links receipts to external reference numbers (e.g., Legacy System IDs, Check Numbers) for easier cross-referencing.
• Bank Reconciliation: Provides deposit dates and clearing dates to assist in month-end bank reconciliation.

2. Technical Analysis

Core Tables and Views

The report queries the core AR receipt tables:

• AR_CASH_RECEIPTS_ALL: The primary header table for all receipts, including miscellaneous ones (identified by type = 'MISC').
• AR_MISC_CASH_DISTRIBUTIONS_ALL: Stores the accounting distributions (GL accounts) for the miscellaneous receipt.
• AR_CASH_RECEIPT_HISTORY_ALL: Tracks the status changes (Creation -> Remittance -> Clearance) and the accounting date for each event.
• AR_RECEIVABLES_TRX_ALL: Defines the “Receivables Activity” (e.g., “Interest Income”) which drives the default accounting.
• AR_BATCHES_ALL: Links receipts to their batch if entered via a batch.
• GL_CODE_COMBINATIONS_KFV: Decodes the GL account segments.

SQL Logic and Data Flow

The SQL is designed to handle the one-to-many relationship between receipts and their distributions/history.

• Row Number Partitioning: Uses row_number() over (partition by ...) to handle the display of amounts. This technique ensures that the header-level Receipt Amount is only displayed on the first distribution line to prevent double-counting in summations.
• Status Decoding: Complex DECODE logic is used to interpret the STATUS columns from both the receipt header and history tables, providing user-friendly statuses like ‘Reversed’, ‘Cleared’, etc.
• Reference Resolution: A CASE statement resolves the reference_type and reference_id to fetch human-readable values (e.g., if type is ‘PAYMENT’, it fetches the Check Number from AP_CHECKS_ALL).
• Bank Account Security: Joins to IBY (Payments) tables (IBY_EXT_BANK_ACCOUNTS, IBY_CREDITCARD) to securely fetch masked bank account or credit card numbers.

Integration Points

• General Ledger: Validates the credited accounts via AR_MISC_CASH_DISTRIBUTIONS_ALL.
• Cash Management: Provides data for bank reconciliation (Bank Account, Deposit Date).
• Tax (E-Business Tax): Links to AR_VAT_TAX_ALL (or the E-Business Tax repository in R12) for tax codes.

3. Functional Capabilities

Reporting Dimensions

• Receipt Source: Analyze receipts by “Activity” (e.g., Interest vs. Refund).
• Status: Filter by Receipt Status (e.g., Remitted, Cleared, Reversed).
• Time: Analyze cash inflows by GL Date, Receipt Date, or Deposit Date.
• Bank Account: Group receipts by the depositing Bank Account.

Key Parameters

• Date Ranges: GL Date, Receipt Date, Deposit Date.
• Receipt Method: Filter by Check, Wire, Cash, etc.
• Batch Name: Filter for specific receipt batches.
• Currency: Filter by Entered Currency.

4. Implementation Considerations

Best Practices

• Distribution Sets: Encourage the use of “Distribution Sets” in AR to standardize the accounting for common miscellaneous receipt types. This report can then be used to audit adherence to these standards.
• Reversal Tracking: Use the report to monitor reversed receipts, which can be an indicator of entry errors or potential fraud.
• Month-End: Run this report as part of the month-end close to ensure all miscellaneous cash is posted to the correct period and accounts.

---

Useful Links

• 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

© Enginatics GmbH