CAC Onhand Lot Value (Real-Time) – Oracle EBS SQL Report

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

Overview

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”. – +=============================================================================+*/

Report Parameters

Cost Type, Category Set 1, Category Set 2, Category Set 3, Include Expense Subinventories, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

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

Report Categories

Enginatics

CAC Manufacturing Variance, CAC Receiving Value (Period-End), CAC PO Price vs. Costing Method Comparison, CAC WIP Resource Efficiency, CAC ICP PII WIP Material Usage Variance, CAC WIP Material Usage Variance, CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Inventory Out-of-Balance, CAC ICP PII Inventory and Intransit Value (Period-End)

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 Onhand Lot Value (Real-Time) 07-Jul-2022 152614.xlsx
Blitz Report™ XML Import CAC_Onhand_Lot_Value_Real_Time.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-onhand-lot-value-real-time/

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

Solution

This report combines stock levels with cost.

Technical Architecture

Parameters

Performance

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.

© 2026 Enginatics