CAC New Items – Oracle EBS SQL Report

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

Overview

Report to show items which have been recently created, including various item controls, item costs (per your Costing Method), item master accounts, last transaction and onhand stock, based on the item master creation date.

Parameters:

Creation Date From: starting item master creation date (required). Creation Date To: ending item master creation date (required). Include Uncosted Items: enter Yes to display items which are set to not be costed in your Costing Method Cost Type, defaulted as Yes (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional).S 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 2010 - 2023 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_new_items_rept.sql – | – | – | Description: – | Report to show zero item costs in the “costing method” cost type, – | the creation date and any onhand stock. – | – | version modified on modified by description – | ======= =========== ============== ========================================= – | 1.0 14 jun 2017 Douglas Volz Initial coding based on the zero item cost – | report, xxx_zero_item_cost_report.sql, version 1.3 – | 1.1 20 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters. – | 1.2 07 Jul 2022 Douglas Volz Changed to multi-language views for the item – | master and inventory orgs. Added item master – | accounts and costs by cost element. – | 1.3 09 Jul 2023 Douglas Volz Remove tabs and restrict to only orgs you have – | access to, using the org access view. – | 1.4 08 Aug 2023 Douglas Volz Fix item status code to use translated values. – | 1.5 22 Nov 2023 Douglas Volz Add BOM/Routing/Sourcing Rule columns, item master – | created by and std lot size and costing created by, – | costing lot size and defaulted flag. – | 1.6 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – +=============================================================================+*/

Report Parameters

Creation Date From, Creation Date To, Include Uncosted Items, Assignment Set, Category Set 1, Category Set 2, Category Set 3, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_material_transactions, mtl_transaction_types, mtl_onhand_quantities_detail, bom_structures_b, bom_operational_routings, mrp_sr_receipt_org, mrp_sr_source_org, mrp_sourcing_rules, mrp_sr_assignments, mrp_assignment_sets, mtl_system_items_vl, mtl_parameters, cst_cost_types, org_access_view, cst_item_costs, mtl_item_status_vl, mtl_units_of_measure_vl, fnd_common_lookups, mfg_lookups, fnd_lookups, gl_code_combinations, fnd_user, gl_ledgers, hr_organization_information, hr_all_organization_units_vl, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Inventory Organization Summary, CAC Cost vs. Planning Item Controls, CAC User-Defined and Rolled Up Costs, CAC Calculate ICP PII Item Costs by Where Used, CAC Last Standard Item Cost, CAC New Standard Item Costs, CAC Item vs. Component Include in Rollup Controls, MRP Pegging, CAC Intercompany SO Price List vs. Item Cost Comparison

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 New Items 07-Jul-2022 142245.xlsx
Blitz Report™ XML Import CAC_New_Items.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-new-items/

Case Study & Technical Analysis: CAC New Items

Executive Summary

The CAC New Items report is a Master Data governance tool. It lists all items created within a specified date range, providing a comprehensive view of their setup status: Costing, Accounting, Categories, and Inventory Controls. It is the “New Hire Orientation” for your Inventory Master.

Business Challenge

Solution

This report provides a 360-degree view of the new item.

Technical Architecture

Parameters

Performance

FAQ

Q: Does it show who created the item? A: Yes, it typically includes the “Created By” user ID.

Q: Can I use this to find “Changed” items? A: No, this only looks at the Creation date. To find changed items, you would need to query the last_update_date or use an audit trail report.

© 2026 Enginatics