| layout | default |
|---|---|
| title | CAC Item Cost Summary | Oracle EBS SQL Report |
| description | Report to show item costs in any cost type. For one or more inventory organizations. Parameters: =========== Cost Type: enter the cost type(s) you wish to… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, CAC, Item, Cost, Summary, cst_item_costs, cst_cost_types, mtl_system_items_vl |
| permalink | /CAC%20Item%20Cost%20Summary/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Report to show item costs in any cost type. For one or more inventory organizations.
Cost Type: enter the cost type(s) you wish to report (mandatory). Include Uncosted Items: enter Yes to include non-costed items, enter No to exclude them (mandatory). Item Status to Exclude: enter the item status you wish to exclude, defaulted to Inactive (optional). Category Set 1: the first item category set to report, typically the Cost or Product Line Category Set (optional). Category Set 2: the second item category set to report, typically the Inventory Category Set (optional). Item Number: enter the item numbers 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.
| -- +=============================================================================+ |
|---|
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- +=============================================================================+*/ |
Cost Type, Include Uncosted Items, Item Status to Exclude, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger
cst_item_costs, cst_cost_types, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mtl_parameters, mfg_lookups, fnd_lookups, fnd_common_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, gl_code_combinations, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual
CAC Last Standard Item Cost, CAC Material Account Summary, CAC Inventory and Intransit Value (Period-End), CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC New Standard Item Costs, CAC Missing Material Accounting Transactions, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls
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 Item Cost Summary 07-Jul-2022 150443.xlsx |
| Blitz Report™ XML Import | CAC_Item_Cost_Summary.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/cac-item-cost-summary/ |
The CAC Item Cost Summary report is the workhorse of cost reporting. It provides a straightforward, comprehensive list of item costs for a specified Cost Type and Organization. It is the "go-to" report for answering basic questions about inventory value, standard costs, and item attributes.
Users need a simple, reliable way to extract cost data.
- Accessibility: Navigating screen-by-screen to check costs is inefficient.
- Attribute Context: Knowing the cost is not enough; users also need to know the Item Status, Make/Buy code, and Default Accounts to understand the context.
- Uncosted Items: Identifying items that have been created but not yet costed (Cost = 0) is a critical maintenance task.
This report provides a flat, filterable list of item costs.
- Comprehensive: Includes Unit Cost, Material Cost, Material Overhead, Resource, OSP, and Overhead buckets.
- Context Rich: Includes Inventory Asset Flag, Planning Make/Buy Code, and Default COGS/Sales accounts.
- Maintenance Aid: The "Include Uncosted Items" parameter helps identify gaps in the costing setup.
The report queries the primary costing view:
- Core Table:
cst_item_costs. - Item Details: Joins to
mtl_system_itemsfor descriptions and control flags. - Account Resolution: Joins to
gl_code_combinationsto show the default accounts associated with the item.
- Cost Type: (Mandatory) The cost type to display (e.g., Frozen, Pending, Average).
- Include Uncosted Items: (Mandatory) Toggle to show items with no cost record.
- Item Status to Exclude: (Optional) Filter out Inactive or Obsolete items.
- Scalable: Designed to handle large item masters.
- Indexed: Efficiently filters by Organization and Cost Type.
Q: Does this show the breakdown of resources? A: No, it shows the total Resource cost. For a breakdown by specific resource (e.g., Labor vs. Machine), use the "Item Cost & Routing" report.
Q: Why is the cost zero? A: Either the item is new and hasn't been costed, or it is an Expense item (Inventory Asset Flag = No) which is not tracked with a value in inventory.
Q: Can I use this for Average Costing? A: Yes, simply select the "Average" cost type.
- 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