| layout | default |
|---|---|
| title | CAC Inventory Accounts Setup | Oracle EBS SQL Report |
| description | Report to show Valuation, Receiving, Profit and Loss and Inter-Org accounts as setup in the inventory organization parameters. /… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, CAC, Inventory, Accounts, Setup, mtl_parameters, gl_code_combinations, hr_organization_information |
| permalink | /CAC%20Inventory%20Accounts%20Setup/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
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. |
| -- +=============================================================================+ |
|---|
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- +=============================================================================+*/ |
Account Group, Organization Code, Operating Unit, Ledger
mtl_parameters, gl_code_combinations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual
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.
| Resource | Link |
|---|---|
| Excel Example Output | CAC Inventory Accounts Setup 07-Jul-2022 143822.xlsx |
| Blitz Report™ XML Import | CAC_Inventory_Accounts_Setup.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/cac-inventory-accounts-setup/ |
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.
The financial accuracy of an Oracle EBS implementation hinges on the correct setup of Inventory Organization Parameters.
- Setup Errors: Incorrect account assignments during implementation can lead to pervasive accounting errors that are difficult to unwind.
- Inconsistent Standards: In multi-org environments, different organizations might inadvertently use different accounting standards or accounts for the same purpose.
- Troubleshooting: When accounting entries look wrong, the first place to check is often the default accounts configured at the organization level.
- Change Management: Tracking changes to these critical default accounts over time is difficult without a snapshot tool.
The CAC Inventory Accounts Setup report extracts and organizes these critical account definitions.
- Comprehensive Account View: Reports on a wide range of accounts including Material, Overhead, Resource, Receiving, PPV, and COGS accounts.
- Grouped Reporting: Allows users to report by "Account Group" (e.g., Valuation, Receiving, P&L) to focus on specific financial areas.
- Multi-Org Validation: Enables side-by-side comparison of account setups across multiple organizations and ledgers, facilitating standardization.
- Detailed Segments: Shows the full GL account string, ensuring complete visibility into the accounting flexfield segments.
The report queries the core inventory parameter table and resolves the account IDs into readable segments.
Primary Tables Involved:
MTL_PARAMETERS: The master table containing all configuration settings for an Inventory Organization, including the default account IDs.GL_CODE_COMBINATIONS: Used to resolve the account IDs fromMTL_PARAMETERSinto readable account strings.HR_ALL_ORGANIZATION_UNITS_VL: Provides organization names.GL_LEDGERS: Links the organization to its financial ledger.
Logical Relationships:
- Parameter to Account: The
MTL_PARAMETERStable has numerous columns (e.g.,material_account,ap_accrual_account) that storecode_combination_ids. The report joins each of these toGL_CODE_COMBINATIONS(often using outer joins to handle nulls) to retrieve the account details. - Organization Hierarchy: Links the inventory organization to its Operating Unit and Ledger to provide the full enterprise context.
- Account Group: A powerful filter that allows users to select a subset of accounts to view (e.g., only "Valuation" accounts or only "Receiving" accounts).
- Organization Code: Filters for a specific inventory organization.
- Operating Unit: Limits the report to organizations within a specific Operating Unit.
- Ledger: Filters by the associated General Ledger.
- Setup Data Query: Since this report queries configuration data rather than transaction history, it is extremely fast and lightweight.
- Efficient Decoding: The report likely uses efficient SQL decoding or case statements to categorize accounts into groups, making the output user-friendly without heavy processing.
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.
- 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
© 2026 Enginatics