CAC Onhand Lot Value (Real-Time)
Description:
Report for onhand inventory at the time you run the report (real-time). By organization, Lot and Subinventory. If you leave the Cost Type blank the report will value the inventory using the inventory organization’s Costing Method (Standard, Average, FIFO, LIFO). If you enter a Cost Type the report will use these item costs, plus, if any item costs are missing from the entered Cost Type, get the remaining item costs from the Costing Method Cost Type. Note that consigned quantities are reported but not valued. And if you choose to report expense subinventories, they are not valued either.
/* +=============================================================================+ – | Copyright 2009 - 2020 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_inventory_val_rept.sql – | – | Parameters: – | p_period_name – Accounting period you wish to report for – | p_org_code – Specific inventory organization you wish to report (optional) – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | p_cost_type – Enter a Cost Type to value the quantities – | using the Cost Type Item Costs; or, if – | Cost Type is blank or null the report will – | use the stored month-end snapshot values – | p_category_set1 – The first item category set to report, typically the – | Cost or Product Line Category Set – | p_category_set2 – The second item category set to report, typically the – | Inventory Category Set – | – | =================================================================== – | Note: if you enter a cost type this script 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. – | =================================================================== – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 27 Sep 2009 Douglas Volz Initial Coding – | 1.1 28 Sep 2009 Douglas Volz Added a sum for the ICP costs from cicd – | 1.16 23 Apr 2020 Douglas Volz Changed to multi-language views for the item – | master, item categories and operating units. – | Used mfg_lookups for “Intransit”. – +=============================================================================+*/
Parameters
Cost Type, Category Set 1, Category Set 2, Category Set 3, Include Expense Subinventories, Item Number, Organization Code, Operating Unit, Ledger
Used tables
cst_cost_groups, pa_projects_all, mtl_item_locations, mtl_system_items_vl, mtl_units_of_measure_vl, mtl_item_status_vl, mtl_parameters, mfg_lookups, fnd_common_lookups, fnd_lookups, hr_organization_information, hr_all_organization_units, gl_code_combinations, gl_ledgers, cst_cost_types, cst_item_costs, org_access_view, select, inventory_item_id, lot_number, expiration_date, locator_id, subinventory_code, cost_group_id, asset_inventory, material_account, quantity, is_consigned, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual
Categories
Related reports
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
CAC Onhand Lot Value (Real-Time) 07-Jul-2022 152614.xlsx
Report SQL
www.enginatics.com/reports/cac-onhand-lot-value-real-time/
Blitz Report™ import options
CAC_Onhand_Lot_Value_Real_Time.xml
Case Study & Technical Analysis: CAC Onhand Lot Value (Real-Time)
Executive Summary
The CAC Onhand Lot Value (Real-Time) report provides a valuation of current inventory at the Lot Number level. While standard reports typically value inventory at the Item level (Average or Standard cost), this report is essential for industries where value or cost is tracked by specific batches (Lots).
Business Challenge
- Lot-Specific Costing: In some industries (Pharma, Food), specific lots might have different costs (FIFO/LIFO layers).
- Expiry Analysis: High-value lots that are about to expire need to be identified and sold.
- Audit: Auditors often select specific lots on the floor and ask for their specific book value.
Solution
This report combines stock levels with cost.
- Granularity: Item + Subinventory + Locator + Lot Number.
- Valuation: Multiplies the On-hand Quantity of the lot by the Item Cost.
- Real-Time: Queries the live
mtl_onhand_quantities_detailtable, not a snapshot.
Technical Architecture
- Tables:
mtl_onhand_quantities_detail(MOQD),cst_item_costs(orcst_quantity_layersfor FIFO/LIFO). - Logic: Sums quantity by Lot and joins to the cost table.
- Note: If using Standard Costing, all lots of an item have the same unit cost. The value is simply Qty * Standard.
Parameters
- Cost Type: (Optional) Defaults to the Org’s costing method.
- Include Expense Subinventories: (Optional) Toggle to include non-asset locations.
Performance
- Heavy: MOQD can have millions of rows. Aggregating real-time on-hand balances can be slow during peak hours.
FAQ
Q: Does this show “Reserved” quantity? A: The report typically shows “Total Onhand”. It may or may not split out “Available to Reserve” depending on the specific SQL logic (standard MOQD queries show total physical stock).
Q: Can I see the Expiration Date?
A: Yes, Lot attributes like Expiration Date are usually included from mtl_lot_numbers.
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