CAC AP Accrual IR ISO Match Analysis – Oracle EBS SQL Report

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

Overview

Use this report to match the A/P Accrual entries for internal order inventory receipts with the internal order payables invoices, by the internal sale order and line number. If run in Detail Mode, shows inventory receipts and payables invoices on separate lines with detailed information, if run in Summary Mode, the sales order line is summarized into one row.

Parameters:

Report Mode: you can get a summary by sales order and line or a detailed report, enter Detail or Summary (mandatory). Transaction Date from: enter the IR/ISO beginning transaction date you wish to report (optional). Transaction Date to: enter the IR/ISO ending transaction date you wish to report (optional). Transaction Type: enter the transaction type you wish to report (optional). Category Set 1: the first item category set to report, typically the Cost or Product Line Category Set (optional). Category Set 2: the second item category set to report, typically the Inventory Category Set (optional). Category Set 3: the third item category set to report (optional). Item Number: enter the item numbers you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+ – | Copyright 2016 - 2025 Douglas Volz Consulting, Inc. – | All rights reserved. – | Permission to use this code is granted provided the original author is – | acknowledged.09o warranties, express or otherwise is included in this permission. – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 23 Nov 2016 Douglas Volz Initial Coding – | 1.1 15 Dec 2016 Douglas Volz Added Customer Name for A/P Invoice – | 1.2 20 Dec 2016 Douglas Volz Fixed calculation bug for Net Amount – | 1.3 10 Jan 2017 Douglas Volz Added Transaction Type Code 61 and Transaction – | Source Type 7 in order to report internal – | order receipts for txn type Int Req Intr Rcpt – | and corrected SIGN of net quantity. – | 1.4 17 Jan 2017 Douglas Volz Added Int Req Intr Rcpt to the Inventory Queries – | corrected the transaction type code for material – | transactions, truncated the transaction date – | and corrected the report sort by order number and – | transaction type (receipts first). – | 1.5 18 Jan 2017 Douglas Volz Added Vendor Name and Transaction Source. – | 1.6 19 Jan 2017 Douglas Volz Commented out the customer type indicator, was – | preventing A/P invoices from being reported. – | 1.7 09 Jun 2017 Douglas Volz Added new section to report Intransit Shipments – | for FOB Shipment with the order booked in one – | OU but shipped from another OU – | 1.8 11 Jun 2017 Douglas Volz Added Transaction Date and Operating Unit – | Parameters. Added new section 5 for Payables – | invoices which are unmatched to POs or for IR/ISOs. – | 1.9 26 Feb 2025 Douglas Volz Removed tabs, cleaned up for Blitz Report. – +=============================================================================+*/

Report Parameters

Report Mode, Transaction Date From, Transaction Date To, Transaction Type, Item Number, Category Set 1, Category Set 2, Category Set 3, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

gl_code_combinations_kfv, gl_ledgers, mtl_system_items_vl, mfg_lookups, cst_misc_reconciliation, mtl_material_transactions, mtl_transaction_types, oe_order_headers_all, oe_order_lines_all, po_vendors, xla_ae_headers, xla_ae_lines, mtl_parameters, hr_organization_information, hr_all_organization_units, mo_glob_org_access_tmp, dual, ap_invoices_all, ap_invoice_lines_all, ap_invoice_distributions_all, ra_customer_trx_lines_all, ra_customer_trx_all, hz_cust_accounts, hz_parties

Report Categories

Enginatics

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource Link
Excel Example Output None
Blitz Report™ XML Import CAC_AP_Accrual_IR_ISO_Match_Analysis.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-ap-accrual-ir-iso-match-analysis/

Case Study & Technical Analysis: CAC AP Accrual IR ISO Match Analysis

Executive Summary

The CAC AP Accrual IR ISO Match Analysis is a specialized reconciliation tool designed to solve one of the most complex accounting challenges in Oracle E-Business Suite: reconciling Internal Requisitions (IR) and Internal Sales Orders (ISO). This report bridges the gap between the Inventory Receipt (which generates the accrual liability) and the Intercompany AP Invoice (which clears that liability). By matching these transactions at the Sales Order Line level, it provides a clear picture of the intercompany accrual balance, identifying missing invoices or receipts that cause period-end write-off issues.

Business Challenge

Intercompany transfers involve two distinct accounting events that often happen at different times and in different modules:

  1. Shipment/Receipt: The receiving organization records a receipt, debiting Inventory and crediting the Intercompany Accrual account.
  2. Invoicing: The shipping organization generates an AR invoice, which triggers the creation of an AP invoice in the receiving organization to clear the accrual.

The Problem: Standard Oracle reports often struggle to link these two events effectively. If the AP invoice creation fails, is delayed, or is matched incorrectly, the accrual balance remains open indefinitely. Finance teams often spend days manually searching for the corresponding AP invoice for a specific inventory receipt to prove the balance is valid.

Solution

This report automates the matching process by using the Internal Sales Order (ISO) number as the common identifier between the Inventory and Payables modules.

Technical Architecture

The report employs a complex multi-module join strategy to reconstruct the transaction lifecycle:

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does the report show a “Net Amount” difference? A: A difference implies that the amount accrued upon receipt differs from the amount invoiced. This can happen due to price variances, currency exchange rate differences between the receipt date and invoice date, or partial invoicing.

Q: Does this report handle FOB Shipment vs. FOB Destination? A: Yes, the SQL logic (specifically around version 1.7) includes sections to report Intransit Shipments for FOB Shipment scenarios where the order is booked in one OU but shipped from another.

Q: What should I do with “Unmatched Receipts”? A: These represent liabilities that have not been cleared. You should investigate if the “Create Intercompany AP Invoices” program has run successfully or if the invoice is stuck in the AP Interface tables.

© 2026 Enginatics