AR Transaction Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

This upload can be used to create Invoices, Debit Memos and Credit Memos. Credit memos can be created On Account, or created and applied directly to an existing transaction (an applied credit memo).

The ‘Upload Trx Identifier’ column is used to uniquely identify each individual transaction (invoice, credit memo, debit memo) to be uploaded. If the selected batch source uses manual transaction numbering, the Upload Trx Identifier will be copied to the Transaction Number column (the Oracle Transaction Number), but this can be overridden in the upload excel. If the selected transaction type uses a Manual Document Sequence, the Upload Trx Identifier will be used as the Document Sequence Value in Oracle.

The upload supports the entry of

• Standard Invoice Lines
• Manual Tax Lines (if permitted)
• Header Level Freight Lines (if permitted) – identified by leaving the Link to Line Number null
• Line Level Freight Lines (if permitted) – identified by populating the Link to Line Number column

For Tax Lines, and Line level Freight Lines, use the Link to Line Number to identify the Invoice Line to which the Tax Line or Freight Line should be linked. Tax Lines must be linked to a standard invoice line.

Note. Tax Lines and Line level Freight Lines must occur after the row containing the invoice line to which they will be linked, although it does not need to be immediately following.

• A Quantity and Unit Price must be specified for standard invoice lines. The upload will calculate the line amount.
• You can specify a quantity and unit price for a freight line in which case the upload will calculate the line amount, or you enter the line amount directly. Only the amount is uploaded to Oracle.
• For manual Tax Lines, you can enter the line amount directly. If left blank, the upload will calculate the amount based on the selected Tax Rate and the line amount from the linked standard invoice.

For Manual Tax Lines you must specify the Tax Regime, Tax, Tax Jurisdiction, Tax Status, Tax Rate Name, and Tax Rate columns.

Applied Credit Memos To create a credit memo and apply it to an existing transaction in one step, populate the Credit Against Transaction column with the transaction to be credited. A Credit Memo Reason is required for an applied credit memo.

The Transaction Type and Transaction Date are derived automatically from the credited invoice and are informational on this flow. The Transaction Type list shows only the credit memo type linked to the credited invoice’s transaction type, and the Transaction Date defaults to the later of today and the credited invoice’s date.

You can credit the invoice at header level or at line level.

Header level – leave the Credit Against Line column blank.

• Leave Credit Line Amount, Credit Tax Amount and Credit Freight Amount blank to credit the full outstanding balance of the invoice.
• Or enter negative Credit Line, Credit Tax and/or Credit Freight Amounts to credit part of the invoice.

Line level – populate the Credit Against Line column.

• Enter one row per invoice line to be credited, identifying the line in the Credit Against Line column.
• A negative Credit Line Amount is required for each credited line. It defaults to the full line amount; edit it for a partial line credit.
• Credit Tax Amount and Credit Freight Amount are not used at line level. Credit tax and freight using a header level credit, or let the credit method spread the tax automatically.

The report parameters Credit Method for Rules and Credit Method for Installments control how the credit is applied across accounting rule periods and payment schedules.

On a line with an invoicing rule, enter one distribution row per accounting class - Revenue plus Unearned Revenue or Unbilled Receivable - each allocating the whole line.

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

Operating Unit, Source, Default Transaction Type, Default Transaction Currency, Exchange Rate Type, Default Transaction Date, Default GL Date, Use SalesPersons, Derive Applied CM Batch Source, Credit Method for Rules, Credit Method for Installments, Historical Transaction Load?

Oracle EBS Tables Used

jtf_rs_salesreps, jtf_rs_resource_extns_vl, ra_terms_vl, ar_receipt_methods, iby_trxn_extensions_v, gl_daily_conversion_types, ra_cust_trx_line_gl_dist_all, gl_code_combinations_kfv, ra_customer_trx_lines_all, mtl_system_items_vl, ar_memo_lines_all_vl, mtl_units_of_measure_vl, ra_rules, org_freight, fnd_territories_vl, zx_output_classifications_v, zx_fc_business_categories_v, zx_fc_product_fiscal_v, zx_fc_product_categories_v, zx_product_types_v, zx_fc_codes_vl, zx_fc_types_b, mtl_category_sets_b, fnd_id_flex_structures_vl, mtl_categories_b_kfv, mtl_categories_tl, hr_all_organization_units_vl, ra_customer_trx_all, ar_payment_schedules_all, ra_cust_trx_line_salesreps_all, ra_batch_sources_all, ra_batches_all, ra_cust_trx_types_all, hz_cust_accounts, hz_parties, hz_cust_site_uses_all, hz_cust_acct_sites_all, hz_party_sites, zx_lines_det_factors, zx_lines

Report Categories

Enginatics, Upload

Related Reports

AR Transactions and Lines, GL Account Distribution Analysis, AR Customer Upload, AR Customers and Sites, AR Transactions and Lines 11i, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Analysis, AR Transactions and Payments, AR Aging

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_Transaction_Upload.xml
SQL Source	AR Transaction Upload.sql
Full Report on Enginatics	www.enginatics.com/reports/ar-transaction-upload/

AR Transaction Upload - Case Study & Technical Analysis

Executive Summary

The AR Transaction Upload is a high-productivity utility designed to streamline the creation of Receivables transactions. Unlike standard reports that only read data, this tool allows users to bulk-load Invoices, Credit Memos, and Debit Memos directly from an Excel spreadsheet into Oracle EBS. It serves as a user-friendly alternative to the complex AutoInvoice interface or manual form entry.

Business Challenge

Data entry in Oracle Receivables can be a bottleneck.

• Volume: Manually keying in hundreds of monthly service invoices is time-consuming and error-prone.
• Integration: Loading data from external systems (e.g., a specialized billing platform) typically requires IT to build SQL loader scripts for the AutoInvoice interface tables.
• Flexibility: Business users often need a quick way to upload ad-hoc corrections or one-off billing batches without waiting for a formal integration project.

Solution

The AR Transaction Upload empowers end-users to manage their own data loads:

• Excel Interface: Users work in a familiar spreadsheet environment to prepare their data.
• Validation: The tool validates customers, items, and dates against Oracle master data before attempting the upload.
• Complexity Handling: It supports advanced features like multi-line invoices, manual tax lines, and freight charges.

Technical Architecture

This tool leverages Oracle’s public APIs to ensure data integrity and standard validation.

Key Features & Logic

• Grouping: The Upload Trx Identifier column is the key. All rows with the same identifier are grouped into a single transaction header.
• Line Types:
  • Standard Lines: Created with Item, Quantity, and Price.
  • Freight: Can be header-level or line-level (linked via Link to Line Number).
  • Tax: Manual tax lines can be added and linked to specific invoice lines.
• API Usage: Instead of inserting directly into tables (which is unsafe), the upload typically calls standard Oracle APIs (e.g., AR_INVOICE_API_PUB) or populates the AutoInvoice interface tables (RA_INTERFACE_LINES_ALL) and triggers the import program.

Data Mapping

• Customer: Mapped via Name or Account Number.
• Item: Validated against MTL_SYSTEM_ITEMS_VL.
• Tax: Validated against the E-Business Tax engine (ZX_ tables).
• Terms: Validated against RA_TERMS.

Parameters

• Operating Unit: The target business unit for the transactions.
• Source: The Batch Source (e.g., ‘Blitz Upload’) which determines numbering and validation rules.
• Default Transaction Type: Used if a specific type is not provided in the spreadsheet row.
• Default GL Date: The accounting date for the batch (must be in an open period).

FAQ

Q: Can I upload invoices for multiple customers in one sheet? A: Yes. Each row specifies the customer. The tool groups rows by the “Upload Trx Identifier,” so you can have Invoice A for Customer X and Invoice B for Customer Y in the same upload.

Q: How are invoice numbers assigned? A: It depends on the “Batch Source.” If the source is set to “Automatic Numbering,” Oracle assigns the number. If “Manual,” the value in the “Transaction Number” column (or the Upload Trx Identifier) is used.

Q: What happens if one line fails validation? A: Typically, the entire transaction (invoice) is rejected to ensure data consistency. The error message is returned to the Excel sheet for correction.

---

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