AR Unaccounted Transaction Sweep – 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
Imported from BI Publisher Application: Receivables Source: Unaccounted Transaction Sweep Short Name: ARTRXSWP DB package: ar_unaccounted_trx_sweep
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, Period Name, Sweep Now, Sweep To Period Name, Debug Flag
Oracle EBS Tables Used
ar_period_close_excps_gt, hz_cust_accounts, hz_parties, ar_payment_schedules_all, hr_operating_units, xla_accounting_errors, ra_cust_trx_line_gl_dist_all, gl_code_combinations_kfv, ar_distributions_all, ar_adjustments_all, ra_customer_trx_all
Report Categories
BI Publisher, Enginatics, R12 only
Related Reports
GL Account Distribution Analysis, GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), AR Aging, AR Open Balances Revaluation, AR Open Items Revaluation
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 | Example |
| Blitz Report™ XML Import | AR_Unaccounted_Transaction_Sweep.xml |
| SQL Source | AR Unaccounted Transaction Sweep.sql |
| Full Report on Enginatics | www.enginatics.com/reports/ar-unaccounted-transaction-sweep/ |
AR Unaccounted Transaction Sweep - Case Study & Technical Analysis
Executive Summary
The AR Unaccounted Transaction Sweep is a specialized period-close utility in Oracle Receivables. Its primary purpose is to unblock the period closing process. Oracle enforces a strict rule: an accounting period cannot be closed if it contains transactions that have not been successfully accounted (transferred to the General Ledger). This report identifies such transactions and offers the option to “sweep” them—i.e., move their accounting date—to the next open period.
Business Challenge
The financial close is a time-sensitive process.
- The Blocker: A single unaccounted invoice (e.g., due to a missing exchange rate or invalid account code) can prevent the entire AR period from closing.
- The Deadline: If the accounting team cannot resolve the error before the corporate deadline, they risk delaying the entire company’s financial reporting.
- The Fix: Fixing the root cause (e.g., defining a new tax rule) might take days, which is time the team doesn’t have.
Solution
The AR Unaccounted Transaction Sweep provides a tactical solution:
- Identification: First, run in “Review” mode (Sweep Now = No) to list all stuck transactions and the associated error messages (from Subledger Accounting).
- Resolution (Sweep): If the errors cannot be fixed immediately, run in “Execute” mode (Sweep Now = Yes). This updates the GL Date of the problematic transactions to the first day of the next period.
- Result: The current period is now clean and can be closed. The transactions remain in the system but will be processed in the following month.
Technical Architecture
The tool interacts with the Subledger Accounting (SLA) engine and the core AR transaction tables.
Key Tables & Logic
- Exception Table:
AR_PERIOD_CLOSE_EXCPS_GTis a global temporary table populated by the sweep program. It aggregates exceptions from various sources. - Sources Checked:
- Invoices: Checks
RA_CUST_TRX_LINE_GL_DIST_ALLfor unposted distributions. - Receipts: Checks
AR_DISTRIBUTIONS_ALL(Cash/Misc Receipts). - Adjustments: Checks
AR_ADJUSTMENTS_ALL.
- Invoices: Checks
- Error Details: Links to
XLA_ACCOUNTING_ERRORSto retrieve the specific reason why the accounting failed (e.g., “Code Combination ID not found”).
The Sweep Action
When “Sweep Now” is enabled, the program executes an UPDATE statement:
\(\text{GL\_DATE} = \text{Start Date of 'Sweep To Period'}\)
This applies to the distribution lines of the affected transactions.
Parameters
- Period Name: The period you are trying to close (e.g., ‘Oct-23’).
- Sweep Now:
- No: Report mode only. Lists the exceptions.
- Yes: Action mode. Moves the transactions.
- Sweep To Period Name: The target period (e.g., ‘Nov-23’). Must be Open or Future-Entry.
FAQ
Q: Is sweeping recommended? A: It is a fallback mechanism. The best practice is to resolve the accounting error in the correct period to ensure expenses/revenue are matched to the right month. Sweeping moves the P&L impact to the next month.
Q: Can I sweep a transaction that has been partially accounted? A: Generally, no. If part of a transaction (e.g., the Revenue line) is already posted, you cannot change the GL date of the whole transaction. The sweep program typically handles completely unaccounted events.
Q: What happens if I don’t sweep? A: You simply cannot close the AR period until the transactions are either fixed (and accounted) or deleted.
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