CAC Inventory Lot and Locator OPM Value (Period-End)
Description:
Report showing amount of inventory at the end of the month for Process Manufacturing (OPM) inventory organizations, for both onhand and intransit inventory. 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 snapshot. In either case this report uses the month-end quantities, based on the entered period name, calendar code and period code. As these quantities come from the month-end snapshot, this also also allows you to specify the lot number and locator (row/rack/bin) for your onhand quantities. And as a default valuation account, this report uses the Material Account from your subinventory setups and your Intransit Account from your Shipping Network setups.
Note: OPM intransit balances based upon last two years of Intransit Shipments. As of Release 12.2.13, OPM does not have a month-end snapshot for intransit quantities or balances.
General Parameters:
Period Name (Closed): the closed inventory accounting period you wish to report (mandatory). Cost Type: enter a Cost Type to value the quantities using the Cost Type item costs; or, if Cost Type is not entered the report will use the stored month-end snapshot values (optional). Show OPM Lot Number: choose Yes to show the OPM lot number for the inventory quantities. Otherwise choose No (mandatory). Show OPM Locator: choose Yes to show the OPM locator and Lot Expiration Date for the inventory quantities. Otherwise choose No (mandatory). OPM Calendar Code: Choose the OPM Calendar Code which corresponds to the inventory accounting period you wish to report (mandatory). OPM Period Code: enter the OPM Period Code related to the inventory accounting period and OPM Calendar Code you wish to report (mandatory). 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: specific buy or make item you wish to report (optional). Subinventory: specific area within the warehouse or inventory area you wish to report (optional). Organization Code: any inventory organization, defaults to your session’s inventory organization (optional). Operating Unit: specific operating unit (optional). Ledger: specific ledger (optional).
/* +=============================================================================+
– | Copyright 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.
– +=============================================================================+
– |
– | Version Modified on Modified by Description
– | ======= =========== ============== =========================================
– | 1.0 10 May 2024 Douglas Volz Initial Coding.
– | 1.1 30 Jun 2024 Douglas Volz Cumulative fixes for OPM intransit balances and accounts.
– | 1.2 02 Aug 2024 Douglas Volz Add OPM Cost Organizations to get correct item costs and qtys.
– | 1.3 30 Sep 2024 Douglas Volz Add Lot expiration date to report.
– +=============================================================================+*/
Parameters
Period Name (Closed), Cost Type, Show Lot Number, Show Locator, OPM Calendar Code, OPM Period Code, Category Set 1, Category Set 2, Category Set 3, Item Number, Subinventory, Organization Code, Operating Unit, Ledger
Used tables
inv_organizations, mtl_units_of_measure_vl, mtl_item_status_vl, mtl_secondary_inventories, mtl_item_locations_kfv, mtl_material_statuses_vl, mtl_lot_numbers, fnd_common_lookups, mfg_lookups, gl_code_combinations, gl_item_cst, gmf_period_statuses, gmf_fiscal_policies, gmf_calendar_assignments, gmf_period_balances, mtl_system_items_vl, org_acct_periods, cm_cmpt_dtl, cm_cmpt_mst_b, cm_mthd_mst, mtl_interorg_parameters
Categories
Dependencies
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
Example Report
Report SQL
www.enginatics.com/reports/cac-inventory-lot-and-locator-opm-value-period-end/
Blitz Report™ import options
CAC_Inventory_Lot_and_Locator_OPM_Value_Period_End.xml
Case Study & Technical Analysis: CAC Inventory Lot and Locator OPM Value (Period-End)
Executive Summary
The CAC Inventory Lot and Locator OPM Value (Period-End) report provides a granular audit trail for Process Manufacturing (OPM) inventory. Unlike high-level summary reports, this tool drills down to the specific Lot and Locator (Row/Rack/Bin) level, providing the detailed evidence needed to substantiate inventory valuations for sensitive or regulated materials (e.g., chemicals, pharmaceuticals).
Business Challenge
In Process Manufacturing, the value of inventory is often tied to specific batches or lots which may have different costs or expiration dates.
- Audit Granularity: Auditors often require a “floor-to-sheet” count where they verify specific lots in specific bins. Summary reports are insufficient for this.
- Expiration Risk: High-value chemicals often expire. Finance needs to know the value of inventory that is approaching its shelf life to accrue for potential write-offs.
- Locator Accuracy: Verifying that hazardous materials are stored in the correct locations requires visibility into the locator-level balances.
Solution
This report unlocks the detailed subledger data for OPM.
- Deep Dive: Reports Quantity and Value by Item, Lot, and Locator.
- Expiration Visibility: Includes the Lot Expiration Date, enabling “At-Risk” inventory analysis.
- Intransit Detail: Also covers intransit inventory, providing a complete picture of owned assets.
Technical Architecture
The report navigates the complex OPM table structure:
- Data Sources: Joins
mtl_system_itemswith OPM-specific quantity tables (likelygmf_period_balancesor similar derived logic) andmtl_item_locationsfor locator definitions. - Lot Details: Pulls lot attributes from
mtl_lot_numbers. - Valuation: Applies the period-end cost to the detailed quantities to derive the line-level value.
Parameters
- Show OPM Lot Number: (Mandatory) Toggle to enable/disable lot detail.
- Show OPM Locator: (Mandatory) Toggle to enable/disable bin location detail.
- OPM Calendar/Period: (Mandatory) Defines the reporting timeframe.
Performance
- High Volume: Enabling Lot and Locator details can result in a massive number of rows (millions) for large warehouses.
- Export Strategy: It is recommended to export this report to Excel or a database for further analysis rather than printing it.
FAQ
Q: Can I see expired lots only? A: The report lists all lots. You can filter the output in Excel based on the “Lot Expiration Date” column.
Q: Why is the locator field empty? A: If the item is not locator-controlled, or if the inventory is in an intransit location, the locator field will be blank.
Q: Does this include Quality status? A: Yes, it typically includes the Lot Status (e.g., “Quarantine”, “Released”), which is crucial for valuation (e.g., valuing quarantined items at zero).
Oracle E-Business Suite Reporting Library
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.
The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.
You can download and use Blitz Report free of charge for your first 30 reports.
The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
Useful Links
Blitz Report™ – World’s fastest data upload and reporting for Oracle EBS
Oracle Discoverer replacement – importing worksheets into Blitz Report™
Blitz Report™ Questions & Answers
Supply Chain Hub by Blitz Report™
© 2025 Enginatics