CAC Inventory Accounts Setup – 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

Report to show Valuation, Receiving, Profit and Loss and Inter-Org accounts as setup in the inventory organization parameters.

/* +=============================================================================+ – | Copyright 2009 - 2020 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. | – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: xxx_inv_accts_setup_rept.sql – | – | Parameters: – | p_account_group – The group of inventory organization parameter accounts – | you wish to report – | p_org_code – Specific inventory organization you wish to report (optional) – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | Description: – | Report to show Valuation, Receiving, Profit and Loss and Inter-Org accounts – | as setup in the inventory organization parameters – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.11 06 Mar 2019 Douglas Volz Added Cost Variance Account, used with Average, FIFO, LIFO – | 1.12 14 Mar 2019 Douglas Volz Added Expense A/P Accrual Account, Retroactive Price – | Adjustment Account, Receiving Clearing Account. – | 1.13 12 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters, – | an outer join to gcc in case of invalid accounts – | and change to gl.short_name. – | 1.14 28 Feb 2020 Douglas Volz Changed to multi-language views for the item – | master, inventory orgs and operating units. – | 1.15 24 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

Account Group, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, wsh_shipping_parameters, rcv_parameters, po_system_parameters_all, gl_code_combinations

Report Categories

Enginatics

CAC Interface Error Summary, INV Items, CAC Receiving Account Detail, CAC WIP Account Detail, CAC Receiving Value (Period-End), CAC Purchase Price Variance, CAC WIP Account Summary, CAC Receiving Account Summary, CAC Receiving Expense Value (Period-End)

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 CAC Inventory Accounts Setup 07-Jul-2022 143822.xlsx
Blitz Report™ XML Import CAC_Inventory_Accounts_Setup.xml
SQL Source CAC Inventory Accounts Setup.sql
Full Report on Enginatics www.enginatics.com/reports/cac-inventory-accounts-setup/

Case Study & Technical Analysis: CAC Inventory Accounts Setup

Executive Summary

The CAC Inventory Accounts Setup report is a foundational configuration audit tool. It provides a detailed listing of the critical General Ledger accounts defined in the Inventory Organization Parameters. These accounts—including Valuation, Receiving, and Profit & Loss accounts—dictate how all inventory transactions are accounted for financially. This report is indispensable for system implementers, financial controllers, and auditors to verify the integrity of the financial-inventory bridge.

Business Challenge

The financial accuracy of an Oracle EBS implementation hinges on the correct setup of Inventory Organization Parameters.

The Solution

The CAC Inventory Accounts Setup report extracts and organizes these critical account definitions.

Technical Architecture (High Level)

The report queries the core inventory parameter table and resolves the account IDs into readable segments.

Primary Tables Involved:

Logical Relationships:

Parameters & Filtering

Performance & Optimization

FAQ

Q: Which accounts are included in the “Valuation” group? A: Typically, this includes the asset accounts for Material, Material Overhead, Resource, Outside Processing, and Overhead.

Q: Why are some accounts blank? A: Not all accounts are mandatory for all organizations. For example, a Standard Costing organization requires variance accounts, while an Average Costing organization might not use them in the same way.

Q: Can this report detect invalid accounts? A: Yes, by showing the resolved account string, users can visually verify if an account is correct. Additionally, the report logic often handles “invalid” or missing account links gracefully to highlight setup gaps.

© Enginatics GmbH