Skip to the content.

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

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.

Solution

The AR Unaccounted Transaction Sweep provides a tactical solution:

Technical Architecture

The tool interacts with the Subledger Accounting (SLA) engine and the core AR transaction tables.

Key Tables & Logic

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

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.


© Enginatics GmbH