Skip to the content.

CAC Intransit 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 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.)

A shipment is listed when the responsibility can access its owning organization, or its receiving organization when To Organization Code is entered.

Category Set and Category restrict the shipments by the item’s category in the owning organization. Category Set 1 to 3 only add category columns.

/* +=============================================================================+ – | 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 – | – | 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 – | 1.13 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – | 1.14 05 Nov 2026 Eric Clegg Added To Organization Code, Planner, To Org – | Planner, Category Set, Category, From Org – | Planner and Expected Delivery Date for demand – | planners, and shipments into an accessible – | receiving org when To Organization Code is – | entered. – +=============================================================================+*/

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

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

Oracle EBS Tables Used

mtl_supply, rcv_shipment_headers, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, mtl_interorg_parameters, gl_code_combinations, cst_item_costs, mtl_system_items_vl, mtl_item_status_vl, mtl_system_items_b, xxen_report_org_access

Report Categories

Enginatics

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 Intransit Value (Real-Time) 23-Jun-2022 160746.xlsx
Blitz Report™ XML Import CAC_Intransit_Value_Real_Time.xml
SQL Source CAC Intransit Value (Real-Time).sql
Full Report 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.


© Enginatics GmbH