CAC OPM Costed Formula – 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 showing OPM formulas and item costs, by OPM Cost Component Class. The report automatically displays the first thirty Cost Components, sorted by Usage Indicator (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc), then by the Cost Component Class. With the “Other Costs” column summing up any other non-displayed Cost Component Classes. For a different selection of Cost Component Classes, you may override any of the defaulted Cost Component Classes. If you have fewer than thirty Cost Components the report automatically displays “Not Available”, for the succeeding Cost Component columns. And if you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end item costs.

Note: The Label Approval column is from a user-defined Formula field, attribute4. Your use of these descriptive flexfields, may be different and may require you to customize this report.

– Version Modified on Modified by Description
– ======= =========== ==============
– 1.0 02 Jun 2024 Douglas Volz Initial Coding based on client’s sample report.
– 1.1 03 Jun 2024 Douglas Volz UOM conversions for formula line quantities
– 1.2 08 Jun 2024 Douglas Volz Replaced Cost Component rownum sort logic.
– 1.3 12 Jun 2024 Douglas Volz Cleaned-up naming for Cost Component parameters, fixed item number parameter.
– 1.4 03 Aug 2024 Douglas Volz Add OPM Cost Organizations to get correct item costs.
– 1.5 05 Aug 2024 Douglas Volz Add inventory organization access control security.
– 1.6 07 Aug 2024 Douglas Volz Not all formulas are assigned to classes, needs outer join.
– 1.7 08 Aug 2024 Douglas Volz Add item status and Make/Buy columns.
– 1.8 09 Aug 2024 Douglas Volz Add parameter “Show More Details” for Scale Type, Contribute to Yield, Std Lot Size and End Date.
– 1.9 17 Aug 2024 Douglas Volz Add parameters “Only Show Latest Version” and “Effective Date”.
– 1.10 18 Aug 2024 Douglas Volz Restructured code.
– 1.11 07 Sep 2024 Douglas Volz Fixed Cost Component parameters, from lexicals to bind variables.
– 1.12 10 Sep 2024 Douglas Volz Add Std Lot Size UOM column.
– 1.13 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards.

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

Cost Type, OPM Calendar Code, OPM Period Code, Only Show Latest Version, Show More Details, Effective Date, Status to Include, Product Category Set, Line Category Set 1, Line Category Set 2, Item Number, Organization Code, Operating Unit, Ledger, Cost Component 1, Cost Component 2, Cost Component 3, Cost Component 4, Cost Component 5, Cost Component 6, Cost Component 7, Cost Component 8, Cost Component 9, Cost Component 10, Cost Component 11, Cost Component 12, Cost Component 13, Cost Component 14, Cost Component 15, Cost Component 16, Cost Component 17, Cost Component 18, Cost Component 19, Cost Component 20, Cost Component 21, Cost Component 22, Cost Component 23, Cost Component 24, Cost Component 25, Cost Component 26, Cost Component 27, Cost Component 28, Cost Component 29, Cost Component 30

Oracle EBS Tables Used

mtl_parameters, hr_organization_information, cm_whse_asc, gmf_fiscal_policies, gmf_period_statuses, cm_mthd_mst, gl_item_cst, gl_item_dtl, cm_cmpt_mst_b, cm_cmpt_dtl, fm_form_mst_vl, gmd_status_vl, mtl_item_status_vl, hr_all_organization_units_vl, gl_ledgers, gmd_formula_class_vl, fm_matl_dtl, mtl_system_items_vl, mtl_uom_conversions_view, xxen_report_org_access

Report Categories

Enginatics, R12 only

CAC Inventory Lot and Locator OPM Value (Period-End), CAC OPM Batch Material Summary, CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, OPM Actual Cost Adjustment Upload, CAC Inventory Organization Summary, CAC OPM Item Cost Detail

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 Example
Blitz Report™ XML Import CAC_OPM_Costed_Formula.xml
SQL Source CAC OPM Costed Formula.sql
Full Report on Enginatics www.enginatics.com/reports/cac-opm-costed-formula/

Case Study & Technical Analysis: CAC OPM Costed Formula

Executive Summary

The CAC OPM Costed Formula report is a “Standard Cost” report for Process Manufacturing. It displays the calculated cost of a Formula (Recipe), broken down by the OPM Cost Component Classes (e.g., Material, Labor, Overhead, Depreciation). It is the OPM equivalent of the “Indented Bill of Materials Cost” report.

Business Challenge

Solution

This report flattens the cost structure.

Technical Architecture

Parameters

Performance

FAQ

Q: Why “30” components? A: OPM allows unlimited cost components, but a flat report needs a fixed number of columns. 30 covers 99% of use cases.

Q: Does it show the Routing? A: It shows the costs derived from the routing (Resources), but not the operations themselves.

Q: What is “Validity Rule”? A: It determines when and for whom a recipe is valid (e.g., “Recipe A is for Plant 1, Recipe B is for Plant 2”).

© Enginatics GmbH