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

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

Overview

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. | – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: xxx_intransit_value_report.sql – | – | Parameters: – | 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_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 – | – | Description: – | 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. – | (Note: Used the cst_intransit_value_view to simplify the design.) – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 18 Nov 2008 Douglas Volz Initial Coding – | 1.1 10 Dec 2009 Douglas Volz Modified for Celgene – | 1.2 04 Jan 2010 Douglas Volz Added intransit value accounts – | 1.3 03 Mar 2010 Douglas Volz Set the company account number based on – | the item’s cost of goods sold account – | 1.4 04 Mar 2010 Douglas Volz Screen out invalid Intransit balances from – | Dec 2009 transactions for Ledger CHE_EUR_PL. – | These intransit balances were written-off – | in the G/L in December 2009. Was not able – | to get these entries fixed in Oracle. – | 1.10 23 Apr 2020 Douglas Volz Changed to multi-language views for the item – | master, item categories and operating units. – | 1.11 23 Aug 2020 Douglas Volz Modified to get intransit_owning_org_id directly – | from mtl_supply; if you change the shipping – | network FOB Point and you can no longer get – | the FOB Point from cst_intransit_value_view. – | 1.12 27 Nov 2024 Eric Clegg. Added Minimum Absolute Intransit Quantity parameter – +=============================================================================+*/

Report Parameters

Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger, Minimum Absolute Intransit Qty

Oracle EBS Tables Used

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

Report Categories

Enginatics

CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Material Account Detail, CAC ICP PII Material Account Detail, PO Internal Requisitions and Sales Orders, CAC Inventory Lot and Locator OPM Value (Period-End), CAC Receiving Value (Period-End), MRP Pegging, CAC PO Price vs. Costing Method Comparison, CAC Purchase Price Variance

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 Intransit Value (Real-Time) 23-Jun-2022 160746.xlsx
Blitz Report™ XML Import CAC_Intransit_Value_Real_Time.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-intransit-value-real-time/

Case Study & Technical Analysis: CAC Intransit Value (Real-Time)

Executive Summary

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.

Business Challenge

In complex supply chains, a significant amount of inventory can be “on the water” or “on the road” at any given time.

The Solution

The CAC Intransit Value (Real-Time) report solves these problems by querying the current state of supply and shipment tables.

Technical Architecture (High Level)

The report is built to extract data from the receiving and supply subsystems of Oracle Inventory.

Primary Tables Involved:

Logical Relationships:

Parameters & Filtering

Performance & Optimization

FAQ

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.

© 2026 Enginatics