CAC Material Account Summary – Oracle EBS SQL Report

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

Overview

Report to get the Material accounting distributions, in summary, for each item, organization and subinventory. Including Ship From and Ship To information for inter-org transfers. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. With parameters to also limit the report size. Use Show Subinventories to display or not display the subinventory information. Use Show Projects to display or not display the project number and name and use Show WIP Job to display or not display the WIP job information (WIP class, class type, WIP job, description, assembly number and assembly description). For Discrete, Flow and Workorderless WIP (but not Repetitive Schedules). Both Flow and Workorderless show up as the WIP Type “Flow schedule”. Also note this report version shows the latest item status and make buy codes, even if you run the report for prior accounting periods.

Note: this report has identical code and logic as the CAC ICP PII Material Account Summary report, however, with the use of hidden parameters, the PII (profit in inventory) features have been turned off.

Parameters:

Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Show SLA Accounting: enter Yes to use the Subledger Accounting rules for your accounting information (mandatory). If you choose No the report uses the pre-Create Accounting entries. Show Projects: display the project number and name. Enter Yes or No, use to limit the report size (mandatory). Show Subinventories: display the subinventory code and description. Enter Yes or No, use to limit the report size (mandatory). Show WIP: display the WIP job or flow schedule information (WIP class, class type, WIP job, description, assembly number and assembly description). Enter Yes or No, use to limit the report size (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Item Number: enter the specific item number(s) 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 2009- 2024 Douglas Volz Consulting, Inc. – | All rights reserved. – | Permission to use this code is granted provided the original author is – | acknowledged. No warranties, express or otherwise is included in this permission. – +=============================================================================+ – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 06 Nov 2009 Douglas Volz Initial Coding – | 1.27 07 Oct 2022 Douglas Volz Correction for period name joins for interorg transactions. – | 1.28 16 Oct 2022 Douglas Volz Correction for quantity calculations and PII logic. – | 1.30 13 Jun 2024 Douglas Volz Removed tabs, reinstalled parameters, including org access restrictions. – +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, Show SLA Accounting, Show Subinventory, Show Projects, Show WIP Job, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mfg_lookups, mtl_system_items_vl, org_acct_periods, mtl_transaction_types, mtl_units_of_measure_vl, mtl_item_status_vl, mtl_txn_source_types, pii, gl_code_combinations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, &subledger_tab, &subinventory_table

Report Categories

Enginatics, R12 only

CAC Material Account Detail, CAC ICP PII Material Account Detail, CAC ICP PII Material Account Summary, CAC ICP PII WIP Material Usage Variance, CAC Material Account Alias with Lot Numbers, CAC Recost Cost of Goods Sold, CAC Deferred COGS Out-of-Balance, CAC WIP Material Usage Variance, CAC Manufacturing Variance

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 CAC Material Account Summary - Pivot by Org 16-Oct-2022 165743.xlsx
Blitz Report™ XML Import CAC_Material_Account_Summary.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-material-account-summary/

Case Study & Technical Analysis: CAC Material Account Summary

Executive Summary

The CAC Material Account Summary report is a comprehensive accounting analysis tool designed to bridge the gap between Inventory operations and the General Ledger. It provides a summarized view of material transactions, grouped by General Ledger (GL) account, Item, and Organization. This report is essential for:

  1. Month-End Reconciliation: Validating that the inventory subledger matches the GL balance.
  2. Variance Analysis: Identifying specific items or transactions driving unexpected account balances (e.g., high Purchase Price Variance).
  3. Inter-Company Auditing: Tracking goods moving between organizations with clear “Ship From” and “Ship To” visibility.

Business Challenge

In Oracle EBS, the link between a physical inventory transaction (e.g., “PO Receipt”) and the resulting financial journal entry can be opaque.

The Solution

This report solves these challenges by offering a flexible, dual-mode architecture:

Technical Architecture (High Level)

The query uses a sophisticated structure to handle the complexity of Oracle’s inventory accounting model.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why do I see different accounts when I switch “Show SLA Accounting” to Yes? A: This indicates that your organization uses SLA rules to transform the account generated by the inventory transaction manager. The “Yes” view is the correct one for GL reconciliation.

Q: Can I use this report for WIP reconciliation? A: Yes, if you enable Show WIP, you can see material issues and completions tied to specific jobs. However, for full WIP value analysis, the “WIP Value Report” is more specialized.

Q: What is the “PII” logic mentioned in the header? A: PII stands for “Profit in Inventory”. It refers to the markup added when goods are sold between internal organizations. This report shares code with a PII-specific version but has those features disabled by default to focus on standard material accounting.

© 2026 Enginatics