AR Transactions and Lines – 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

AR transaction report. Can be run at Header, Line and/or Distribution Level

Display Level Lines and Sales Credits lists each line once per salesperson credit, with the revenue and non-revenue split. Transaction and line amounts are shown on the first row of a transaction or line only, so they can be summed.

Revenue As Of Date adds the accounting rule and the deferred revenue position of each line: Revenue Recognized is the sum of the revenue distributions with a GL date up to that date, and Unearned Balance is the line amount less that, for lines with an accounting rule or with revenue deferred by Revenue Management. Optionally include special columns for service contracts (OKS) and lease contracts (OKL) data

When India Localization is installed, the following GST columns are also included: IGST Amount, CGST Amount, SGST Amount, CESS Amount, Custom Amount, Unclassified Tax Amount, HSN SAC Code, HSN Length Warning, GST State Tax Mismatch, CGST Recoverable, CGST Non-Recoverable, SGST Recoverable, SGST Non-Recoverable, IGST Recoverable, IGST Non-Recoverable, GST Registration No, GST PAN No, Reverse Charge Flag, Place of Supply, Ship From State, GST Warehouse Registration No, Taxable Value.

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

Ledger, Operating Unit, Display Level, Bill To Customer like, Bill To Customer Number, Consolidated Invoice Number, Transaction Number, Inv. Date Period, Inv. Date From, Inv. Date To, GL Period, GL Date From, GL Date To, Inv. Creation Date From, Inv. Creation Date To, Status, Overdue for more than x Days, Salesperson, Transaction Class, Transaction Type, Batch Source, Batch Name, Interface Category, Sales Order, Ship From Warehouse, Distribution Class, Distribution Account From, Distribution Account To, Print Date from, Print Date to, Display Contracts Details, Category Set 1, Category Set 2, Category Set 3, DFF Display, Revenue As Of Date, Deferred Revenue Only

Oracle EBS Tables Used

ra_customer_trx_all, ar_cons_inv_trx_all, ar_cons_inv_all, ra_customer_trx_lines_all, gl_ledgers, hr_all_organization_units_vl, ar_payment_schedules_all, oe_sys_parameters_all, ar_xla_ctlgd_lines_v, mtl_system_items_vl, gl_code_combinations_kfv, ra_batch_sources_all, ra_batches_all, ra_cust_trx_types_all, ra_terms_vl, hz_cust_accounts, hz_parties, hz_cust_site_uses_all, hz_cust_acct_sites_all, hz_party_sites, hz_locations, fnd_territories_vl, ra_territories_kfv, jtf_rs_salesreps, jtf_rs_resource_extns_vl, oe_order_lines_all, oe_order_headers_all, ar_receipt_methods, iby_fndcpt_pmt_chnnls_vl, iby_fndcpt_tx_extensions, iby_pmt_instr_uses_all, iby_creditcard, iby_ext_bank_accounts

Report Categories

Enginatics

GL Account Distribution Analysis, AR Transactions and Lines 11i, GL Account Analysis, AR Customer Upload, CST COGS Revenue Matching, GL Account Analysis (Drilldown) (with inventory and WIP), AR Aging, AR Transactions and Payments

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 Transactions and Lines 20-Jan-2019 115611.xlsx
Blitz Report™ XML Import AR_Transactions_and_Lines.xml
SQL Source AR Transactions and Lines.sql
Full Report on Enginatics www.enginatics.com/reports/ar-transactions-and-lines/

Case Study & Technical Analysis: AR Transactions and Lines

1. Executive Summary

Business Problem

Accounts Receivable (AR) departments need detailed visibility into customer transactions to manage collections, resolve disputes, and analyze revenue. Standard reports often lack the flexibility to show data at different levels of granularity (Header, Line, Distribution) or to include industry-specific details like Service Contracts (OKS) or Lease Management (OKL). Common challenges include:

Solution Overview

The AR Transactions and Lines report is a versatile, multi-level reporting tool that serves as the “Swiss Army Knife” for AR analysis. It allows users to run the report at the Header level (for aging and balances), Line level (for product and revenue analysis), or Distribution level (for accounting reconciliation). It enriches standard AR data with critical context from Order Management, Service Contracts, and Payments.

Key Benefits

2. Technical Analysis

Core Tables and Views

The report queries the core AR transaction tables and links to several peripheral modules:

SQL Logic and Data Flow

The SQL uses a modular approach with lexical parameters (&line_columns, &distribution_columns) to dynamically adjust the query based on the user’s selected “Display Level”.

Integration Points

3. Functional Capabilities

Reporting Dimensions

Key Parameters

4. Implementation Considerations

Performance

Best Practices

© Enginatics GmbH