CAC Load More4Apps Buy Item Costs – Oracle EBS SQL Report

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

Overview

Report to fetch all buy items (based on rollup = No). Used as a source of item costs for buy items which you wish to edit or change using the More4Apps Item Cost Wizard or similar tool. You can over-ride the Make Buy Code by removing the defaulted value, but the Oracle Item Cost Interface works best for Buy Items; it does not work well with rolled-up costs and accordingly, this report only downloads items whose costs are not based on the cost rollup.

This report approximates the layout for the More4Apps Item Cost Wizard; run this report to get your Buy Item costs into Excel, make your changes in Excel then paste these revised costs into the More4Apps Item Cost Wizard. Columns needed for the More4Apps Item Cost Wizard: Org Code, Cost Type, Item Number, Based on Rollup, Lot Size, Mfg Shrinkage, Cost Element, Sub-Element, Basis Type and Rate or Amount. The additional columns, Currency Code, UOM Code, Make Buy Code and Inventory Asset, are for reference purposes.

Parameters:

From Cost Type: enter the cost type you are downloading from (mandatory). To Cost Type: enter the cost type you are planning to upload back into the More4Apps Item Cost Wizard. This Cost Type will show up on the report output (mandatory). Item Status to Exclude: enter the item number status you want to exclude. Defaulted to ‘Inactive’ (optional). Make or Buy: enter the type of item you wish to report. Defaulted to Buy Items, as the Oracle Item Cost Interface works best with items that you purchase, as opposed to rolled up costs (optional). Cost Element: enter the specific cost element you wish to download; for Buy Items typically the Material and Material Overhead Cost Elements (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 2017 - 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_load_m4app_buy_item_costs.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 07 Jun 2017 Douglas Volz Initial Coding – | 1.1 16 Jun 2017 Douglas Volz Only report based on rollup = No – | 1.2 12 Nov 2018 Douglas Volz Remove prior client org restriction and – | add Ledger parameter. – | 1.3 27 Jan 2020 Douglas Volz Added Operating Unit parameter. – | 1.4 06 Jul 2022 Douglas Volz Changed to multi-language views for the item – | master and inventory orgs. – | 1.5 21 Oct 2023 Douglas Volz Added UOM Code, Make Buy Code and Inventory – | Asset columns; added Item Status, Make Buy – | and Cost Element parameters, removed tabs – | and added org access controls. – | 1.6 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – +=============================================================================+*/

Report Parameters

From Cost Type, To Cost Type, Item Status to Exclude, Make or Buy, Cost Element, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

bom_resources, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mtl_parameters, cst_cost_types, cst_cost_elements, cst_item_costs, cst_item_cost_details, mfg_lookups, 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

Report Categories

Enginatics

CAC Manufacturing Variance, CAC Item Cost & Routing, CAC Material Account Detail, CAC ICP PII Material Account Detail, CAC Last Standard Item Cost, CAC ICP PII Inventory and Intransit Value (Period-End), CAC New Standard Item Costs, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls

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 Load More4Apps Buy Item Costs 24-Jun-2022 051540.xlsx
Blitz Report™ XML Import CAC_Load_More4Apps_Buy_Item_Costs.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-load-more4apps-buy-item-costs/

Case Study & Technical Analysis: CAC Load More4Apps Buy Item Costs

Executive Summary

The CAC Load More4Apps Buy Item Costs report is a data migration and maintenance utility. It is specifically designed to support the “More4Apps Item Cost Wizard”, a popular third-party tool for mass-updating Oracle data. This report extracts existing “Buy” item costs into the exact format required for re-upload, streamlining the standard cost update process.

Business Challenge

Updating Standard Costs for thousands of purchased items is a massive manual effort.

Solution

This report automates the “Extract” phase of the ETL (Extract-Transform-Load) process.

Technical Architecture

The report queries the cost details table:

Parameters

Performance

FAQ

Q: Can I use this without More4Apps? A: Yes, it produces a clean CSV/Excel file that can be used as a source for Oracle WebADI or the standard Interface Table (cst_item_cst_dtls_interface).

Q: Why exclude “Make” items? A: “Make” items usually have their costs calculated via the Cost Rollup routine based on their BOM and Routing. Manually uploading a cost for a Make item overrides this calculation, which is usually not desired.

Q: Does it handle OSP? A: Yes, if the OSP cost is maintained as a static value (not rolled up), it will be included.

© 2026 Enginatics