CAC AP Accrual Reconciliation Summary by Match Type – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Cost Accounting · all Cost Accounting reports

Overview

Use this report to summarize the A/P Accrual entries from the Accrual Reconcilation Report tables, by operating unit, accrual match type and inventory organization. Use this report for summary reconciliation purposes, to justify the “at time of receipt” accrual balances for your inventory and expense A/P accrual accounts.

Parameters:

Transaction Date From: enter the accrual starting transaction date you wish to report. Defaults to the earliest date found (mandatory). Transaction Date To: enter the accrual ending transaction date you wish to report. Defaults to the latest date found (mandatory). Operating Unit: operating unit you wish to report (optional). Ledger: general ledger you wish to report (optional).

/* +=============================================================================+ – | Copyright 2011-2025 Douglas Volz Consulting, Inc. – | All rights reserved. – | Permission to use this code is granted provided the original author is – | acknowledged. – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 11 Nov 2011 Douglas Volz Initial Coding based on xxx_ap_accrual_summary_rept.sql – | Retrofitted to R12 A/P Accrual tables – | 1.1 07 May 2019 Douglas Volz Modified for upgrade client – | 1.2 06 Feb 2020 Douglas Volz Added Ledger parameter. – | 1.3 09 Apr 2020 Douglas Volz Commented out capr.inventory_transaction_id column – | Was added by Oracle for consignment transactions in – | Release 12.1.6. – | 1.4 15 Apr 2020 Douglas Volz Undid modifications for upgrade client – | 1.5 17 Feb 2025 Douglas Volz Formatted for Blitz Report. – | 1.6 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – +=============================================================================+*/

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

Transaction Date From, Transaction Date To, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_transaction_types, cst_ap_po_reconciliation, po_distributions_all, xxen_report_org_access, cst_misc_reconciliation, xla_ae_lines, xla_ae_headers, hr_all_organization_units_vl, hr_organization_information, gl_ledgers, mtl_parameters, gl_code_combinations_kfv

Report Categories

Enginatics, R12 only

CAC AP Accrual IR ISO Match Analysis, GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), CAC Purchase Price Variance, CAC Receiving Activity Summary, CAC Invoice Price Variance, INV Material Account Distribution Detail, GL Account Distribution Analysis, CAC Recost Cost of Goods Sold

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 CAC_AP_Accrual_Reconciliation_Summary_by_Match_Type.xml
SQL Source CAC AP Accrual Reconciliation Summary by Match Type.sql
Full Report on Enginatics www.enginatics.com/reports/cac-ap-accrual-reconciliation-summary-by-match-type/

Case Study & Technical Analysis: CAC AP Accrual Reconciliation Summary by Match Type

Executive Summary

The CAC AP Accrual Reconciliation Summary by Match Type is a strategic financial report designed to streamline the month-end closing process for Oracle Cost Management. It provides a high-level aggregation of the Accounts Payable (AP) Accrual liability, categorized by Match Type (e.g., PO Match, Consignment, Miscellaneous). This summary view allows Finance teams to quickly understand the composition of their accrual balance without getting lost in the details of thousands of individual transactions.

Business Challenge

The “Received Not Invoiced” (RNI) account is often one of the most difficult accounts to reconcile.

Solution

This report solves these challenges by summarizing the data already present in the Oracle Accrual Reconciliation tables.

Technical Architecture

The report leverages the standard Oracle Accrual Reconciliation architecture:

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why is the report output empty? A: This report relies on the CST_AP_PO_RECONCILIATION table. You must run the Accrual Reconciliation Load Run concurrent request for the specific Operating Unit and period before running this report.

Q: What does “Match Type” mean? A: The Match Type indicates the nature of the accrual entry. Common types include:

Q: Can I use this for “On Receipt” and “Period End” accruals? A: This report is designed for On Receipt accruals (Perpetual Accruals). Period End accruals are typically handled differently in the General Ledger.

© Enginatics GmbH