CAC Item Master Accounts Setup – Oracle EBS SQL Report

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

Overview

Report to show item master accounts and related information by item.

/* +=============================================================================+ – | Copyright 2011 - 2022 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_item_master_accts_rept.sql – | – | Parameters: – | p_include_non_costed_items – Yes/No flag to include or not include non-costed items – | p_item_number – Enter the specific inventory organization code – | 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) – | p_category_set1 – The first item category set to report, typically the – | Cost or Product Line Category Set – | p_category_set2 – The second item category set to report, typically the – | Inventory Category Set – | – | Description: – | Report to show item master accounts and related information – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 30 Mar 2011 Douglas Volz Initial Coding – | 1.1 18 Nov 2012 Douglas Volz Removed client-specific org conditions – | 1.2 12 Feb 2013 Douglas Volz Changed inventory category to be more generic – | 1.3 16 Nov 2015 Douglas Volz Modified for client’s chart of accounts – | 1.4 21 Feb 2017 Douglas Volz Modified for client’s chart of accounts and – | added parameters to this report – | 1.5 17 Jul 2018 Douglas Volz Modified for client’s chart of accounts and – | modified to use the default category for Inventory – | 1.6 12 Jan 2019 Douglas Volz Use gl short_name, modify for any two categories, – | and add operating unit parameter. – | 1.7 28 Apr 2020 Douglas Volz Changed to multi-language views for the – | inventory orgs and operating units. – | 1.8 29 Apr 2022 Douglas Volz Modify summary report into the detailed report. – +=============================================================================+*/

Report Parameters

Category Set 1, Category Set 2, Category Set 3, Item, Include Non-Costed Items, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

gl_ledgers, org_organization_definitions, hr_all_organization_units_vl, mtl_parameters, mtl_system_items_vl, mtl_item_status_vl, cst_item_costs, cst_cost_types, gl_code_combinations, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual, org_access_view

Report Categories

Enginatics

CAC Item Cost Summary, CAC New Items, CAC Recost Cost of Goods Sold, CAC Items Without This Level Material Overhead, CAC Onhand Lot Value (Real-Time), CAC Open Purchase Orders, CAC ICP PII Item Cost Summary, CAC New Standard Item Costs, CAC Standard Cost Update Submissions

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 Item Master Accounts Setup 26-Jun-2023 171002.xlsx
Blitz Report™ XML Import CAC_Item_Master_Accounts_Setup.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-item-master-accounts-setup/

Case Study & Technical Analysis: CAC Item Master Accounts Setup

Executive Summary

The CAC Item Master Accounts Setup report is a configuration audit tool. It lists the General Ledger accounts defined at the Item level (Sales, Cost of Goods Sold, Expense, Encumbrance). This is critical for validating that new items have been set up with the correct accounting drivers before transactions occur.

Business Challenge

In Oracle EBS, the “Item” is a key driver for accounting logic.

Solution

This report dumps the accounting attributes for review.

Technical Architecture

The report joins the Item Master to the GL Code Combinations:

Parameters

Performance

FAQ

Q: Does this show Subinventory accounts? A: No, this report focuses on the Item Master accounts. Subinventory accounts are a separate setup (though they override item accounts in some transactions).

Q: What if the account is blank? A: If the account field is null in the database, it will show as blank. This usually indicates a setup deficiency, unless the organization uses Subinventory-level accounting exclusively.

Q: Can I see the account description? A: The standard output shows the account numbers (segments). You would typically cross-reference this with your Chart of Accounts to verify the meaning.

© 2026 Enginatics