Report to show intransit values across all ledgers for current onhand balances and current costing method costs. This is a "real-time" report, showing the quantities and values at the time you run this report. (Used the cst_intransit_value_view to simplify the design.)
/* +=============================================================================+ -- | Copyright 2009-20 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. |
| -- +=============================================================================+ |
|---|
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- +=============================================================================+*/ |
Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger, Minimum Absolute Intransit Qty
rcv_shipment_headers, rcv_shipment_lines, mtl_supply, mtl_parameters, mtl_interorg_parameters, mtl_system_items_vl, mtl_units_of_measure_vl, mtl_item_status_vl, cst_item_costs, mfg_lookups, fnd_common_lookups, gl_code_combinations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual
CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Material Account Detail
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.
CAC Intransit Value (Real-Time) 23-Jun-2022 160746.xlsx
www.enginatics.com/reports/cac-intransit-value-real-time/
Blitz Report™ import options
CAC_Intransit_Value_Real_Time.xml
The CAC Intransit Value (Real-Time) report is a critical inventory management tool that provides a snapshot of the value of goods currently in transit between inventory organizations. Unlike period-end reports, this report offers "real-time" visibility, enabling logistics and finance teams to understand their immediate financial exposure and stock availability within the supply chain network.
In complex supply chains, a significant amount of inventory can be "on the water" or "on the road" at any given time.
- Financial Blind Spots: Organizations often struggle to get an accurate, up-to-the-minute valuation of this floating inventory.
- Stockout Risks: Without knowing what is in transit, planners may over-order or face unexpected stockouts.
- Period-End Surprises: Waiting for month-end close processes to see intransit values can lead to surprises in financial reporting.
- FOB Confusion: Determining ownership (and thus financial liability) based on FOB points (Shipment vs. Receipt) is often manual and error-prone.
The CAC Intransit Value (Real-Time) report solves these problems by querying the current state of supply and shipment tables.
- Real-Time Valuation: Calculates the value of intransit stock based on current quantities and current costs, providing an immediate financial picture.
- Global Visibility: Spans across all ledgers and operating units, offering a consolidated view for the entire enterprise.
- Ownership Logic: Correctly handles FOB logic to determine which organization "owns" the inventory and should report it on their balance sheet.
- Simplified Design: Leverages standard Oracle views (like
CST_INTRANSIT_VALUE_VIEWlogic) to ensure consistency with standard Oracle costing.
The report is built to extract data from the receiving and supply subsystems of Oracle Inventory.
Primary Tables Involved:
RCV_SHIPMENT_HEADERS/RCV_SHIPMENT_LINES: Stores details of the shipments that are currently active.MTL_SUPPLY: The central table for tracking supply availability, including intransit stock.MTL_SYSTEM_ITEMS_VL: Provides item definitions and attributes.CST_ITEM_COSTS: Used to retrieve the current cost of items to calculate valuation.MTL_INTERORG_PARAMETERS: Defines the shipping network rules, including FOB points and transfer types.
Logical Relationships:
- Supply to Shipment: The report links
MTL_SUPPLYrecords (wheresupply_type_codeindicates intransit) toRCV_SHIPMENT_LINESto get specific shipment details. - Cost Application: It applies the current item cost from
CST_ITEM_COSTSto the quantity derived from the supply/shipment tables. - Ownership Determination: Logic checks the FOB point defined in the shipping network (
MTL_INTERORG_PARAMETERS) or the shipment itself to assign the value to the correct owning organization.
- Category Sets: Allows filtering by item categories to focus on specific segments of inventory (e.g., Raw Materials vs. Finished Goods).
- Item Number: Enables tracking of specific high-value items.
- Organization Context: Parameters for
Organization Code,Operating Unit, andLedgerallow for scoping the report from a single warehouse to the entire enterprise. - Minimum Absolute Intransit Qty: A useful filter to exclude negligible or "dust" quantities, focusing the report on material variances.
- View Utilization: By basing logic on or similar to
CST_INTRANSIT_VALUE_VIEW, the report leverages Oracle's pre-optimized logic for intransit calculations. - Current State Query: As a real-time report, it queries current balance tables rather than aggregating millions of historical transaction records, making it generally faster than retrospective period-end reports.
- Efficient Filtering: Parameters are applied early in the query execution to limit the dataset to relevant organizations or items.
Q: How does this differ from the Period-End Intransit Value report? A: This report shows the current status at the moment of execution. The Period-End report reconstructs the value as of a specific past date, which is required for reconciliation but more computationally intensive.
Q: Does it handle both Discrete and OPM inventory? A: The description suggests it is designed for standard discrete inventory costing. OPM (Process Manufacturing) often has separate valuation logic, though the principles of intransit tracking are similar.
Q: What determines if inventory is "Intransit"? A: Inventory is considered intransit if it has been shipped from the source organization but not yet received (delivered) at the destination, and the shipment transaction is complete.
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.
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