CAC OPM Batch Material Summary – Oracle EBS SQL Report

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

Overview

Report showing Batch materials in summary for each product, byproduct and ingredient. Displaying batches which were open during the monthly inventory accounting period or batches which were closed during the monthly inventory accounting period. 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.

Parameters:

Period Name: enter the monthly inventory accounting period you wish to report (mandatory). Cost Type: enter a Cost Type to value the quantities using the Cost Type item costs; if Cost Type is not entered the report will use the Cost Type from your Fiscal Policies (optional). OPM Calendar Code: choose the OPM Calendar Code which corresponds to the period costs you wish to report (mandatory). OPM Period Code: enter the OPM Period Code which corresponds to the period costs and OPM Calendar Code you wish to report (mandatory). Category Sets 1 - 3: enter up to three item category sets you wish to report (optional). Item Number: specific Product, By-Product or Ingredient you wish to report (optional). Batch Number: enter any batch number which is open or was closed within the date range of the OPM Period Code and Calendar Code (optional). Batch Status: to minimize the report size, specify the batch statuses you wish to report (optional). Batch Number From: (optional). Batch Number To: (optional). Organization Code: enter the inventory organization(s), defaults to your session’s inventory organization (optional). Operating Unit: enter the operating unit(s) you wish to report (optional). Ledger: enter the ledger(s) you wish to report (optional).

/* +=============================================================================+ – | Copyright 2025 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. – +=============================================================================+ – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 22 Jan 2025 Douglas Volz Initial Coding based on client’s sample report. – +=============================================================================*/

Report Parameters

Period Name, Cost Type, OPM Calendar Code, OPM Period Code, Batch Status, Batch Number, Batch Number From, Batch Number To, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

gme_batch_header, gme_material_details, mtl_uom_conversions_view, item_cost, gmf_period_statuses, gmf_fiscal_policies, gmf_calendar_assignments, org_acct_periods, mtl_system_items_vl, mtl_item_status_vl, gem_lookups, fnd_common_lookups, mfg_lookups, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers

Report Categories

Enginatics

CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Inventory Lot and Locator OPM Value (Period-End), CAC OPM WIP Account Value, CAC OPM Costed Formula, GL Account Analysis

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 None
Blitz Report™ XML Import CAC_OPM_Batch_Material_Summary.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-opm-batch-material-summary/

Case Study & Technical Analysis: CAC OPM Batch Material Summary

Executive Summary

The CAC OPM Batch Material Summary report is a production analysis tool for Oracle Process Manufacturing (OPM). It summarizes the material activity (Ingredients consumed, Products yielded) for production batches. This is the OPM equivalent of a “Material Usage” report in Discrete manufacturing.

Business Challenge

Solution

This report aggregates the details.

Technical Architecture

Parameters

Performance

FAQ

Q: Does this show “WIP” value? A: No, this shows the Material flow (Issues and Completions). For the value of the Batch itself (WIP), use the “OPM WIP Account Value” report.

Q: What is a “By-product”? A: A secondary item produced by the process (e.g., Skim Milk produced when making Cream). It typically has a negative cost or a recovery value.

© 2026 Enginatics