CAC Category 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 the category accounts in use. If category accounts have been set up with your Subledger Accounting Rules, the Inventory Cost Processor can use them and bypass the organization accounts (Average, LIFO, FIFO Costing) or the subinventory accounts (Standard Costing).

/* +=============================================================================+ – | Copyright 2021 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_category_setup_accts_rept.sql – | – | Parameters: – | 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 accounts used for the subinventories – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 16 Aug 2021 Douglas Volz Initial Coding – +=============================================================================+*/

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

Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_cost_groups, mfg_lookups, mtl_secondary_inventories, mtl_category_accounts, mtl_categories_v, mtl_parameters, gl_code_combinations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, fnd_user, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

Related Reports

CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Inventory Pending Cost Adjustment - No Currencies, CAC ICP PII Inventory Pending Cost Adjustment, CAC Inventory and Intransit Value (Period-End), CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory Pending Cost Adjustment, CAC ICP PII Material Account Detail, CAC Material Account Detail, CAC Inventory Out-of-Balance

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 Category Accounts Setup 23-Jun-2022 144507.xlsx
Blitz Report™ XML Import	CAC_Category_Accounts_Setup.xml
SQL Source	CAC Category Accounts Setup.sql
Full Report on Enginatics	www.enginatics.com/reports/cac-category-accounts-setup/

Case Study & Technical Analysis: CAC Category Accounts Setup

Executive Summary

The CAC Category Accounts Setup report is a configuration audit tool for Oracle Inventory and Cost Management. It provides a detailed view of the General Ledger (GL) accounts assigned to item categories. In Oracle EBS, category-level accounting allows for more granular financial tracking than organization or subinventory-level accounts, enabling businesses to drive accounting entries based on the specific type of material (e.g., Raw Materials vs. Finished Goods) regardless of where it is stored.

Business Challenge

Configuring the accounting engine (SLA) and Cost Management rules requires precise setup. Common challenges include:

• Account Visibility: It is difficult to see all accounts assigned to a category across different organizations in a single view.
• Inconsistent Setup: Ensuring that all cost elements (Material, Overhead, Resource, etc.) have the correct accounts defined for every category.
• Troubleshooting: Identifying why a specific transaction hit a particular GL account often requires checking if a category-specific override exists.
• Audit Compliance: Verifying that high-value categories are mapped to the correct balance sheet and expense accounts.

The Solution

The CAC Category Accounts Setup report solves these issues by:

• Consolidated View: Listing all account assignments (Material, Overhead, WIP, etc.) for each category in a unified format.
• Granularity: Showing the specific Cost Group and Subinventory associations if the category accounts are defined at that level.
• Validation: Displaying the full accounting flexfield segments to ensure the correct cost centers and natural accounts are used.
• Change Tracking: Including “Created By” and “Last Updated By” fields to audit who made changes to the setup and when.

Technical Architecture (High Level)

The report uses a UNION ALL structure to normalize the data, as different account types are stored in columns but reported as rows.

• Primary Table: MTL_CATEGORY_ACCOUNTS holds the mapping between categories and GL code combinations.
• Account Types: The query explicitly selects and labels each account type:
  • Material Account
  • Material Overhead Account
  • Resource Account
  • Overhead Account
  • Outside Processing Account
  • Expense Account
  • Bridging Account
• Joins:
  • MTL_CATEGORIES_V for category names.
  • GL_CODE_COMBINATIONS for account segments.
  • CST_COST_GROUPS to show cost group specific setups.
  • MTL_SECONDARY_INVENTORIES to validate subinventory associations.

Parameters & Filtering

The report is designed for broad or specific audits:

• Organization Code: Filter by a specific inventory organization.
• Operating Unit: Filter by the financial operating unit.
• Ledger: Filter by the General Ledger set.

Performance & Optimization

• Union All: Uses UNION ALL instead of UNION to avoid expensive sorting/deduplication, as the datasets for each account type are distinct.
• Indexed Access: Joins are performed on primary keys (CATEGORY_ID, ORGANIZATION_ID, CODE_COMBINATION_ID), ensuring fast retrieval even with large category sets.
• Security: Implements standard Oracle MOAC (Multi-Org Access Control) to ensure users only see data for organizations they are authorized to access.

FAQ

Q: When does the system use Category Accounts? A: The Inventory Cost Processor looks for accounts in a specific hierarchy. If Subledger Accounting (SLA) rules are configured to use “Category Accounts,” the system will prioritize these over Subinventory or Organization-level accounts.

Q: Why do I see “Bridging Account”? A: The Bridging Account is typically used in average costing environments or specific inter-org transfer scenarios to bridge the gap between different valuation methods or organizations.

Q: Can I see accounts for a specific Cost Group? A: Yes, the report includes a “Cost Group” column. If category accounts are defined specifically for a Cost Group (common in Project Manufacturing), it will be visible here.

Q: What if a category has no accounts defined? A: It will not appear in this report. This report only lists existing records in MTL_CATEGORY_ACCOUNTS. If a category is missing, it means it falls back to the default Subinventory or Organization accounts.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH