CAC Onhand Lot Value (Real-Time) – 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 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”. – | 1.17 23 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, 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, mtl_item_locations, pa_projects_all, mtl_parameters, mtl_onhand_quantities_detail, mtl_secondary_inventories, mtl_lot_numbers, xxen_report_org_access, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, mtl_system_items_vl, mtl_item_status_vl, cst_item_costs, cst_cost_types

Report Categories

Enginatics

INV Onhand Quantities, CAC ICP PII Inventory Pending Cost Adjustment, INV Aging, CAC Inventory Pending Cost Adjustment - No Currencies, CAC Inventory Pending Cost Adjustment, CAC Inventory Lot and Locator OPM Value (Period-End), INV Material Transactions

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 CAC Onhand Lot Value (Real-Time) 07-Jul-2022 152614.xlsx
Blitz Report™ XML Import CAC_Onhand_Lot_Value_Real_Time.xml
SQL Source CAC Onhand Lot Value (Real-Time).sql
Full Report 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.

© Enginatics GmbH