CAC Intercompany SO Shipping Transaction vs. Invoice Price – 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 compare the transaction item cost from the selling internal org to the invoice sales price the selling internal organization is charging the internal receiving organization.

/* +=============================================================================+ – | Copyright 2017 - 2022 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_verify_so_price_vs_cost_txn.sql – | – | Parameters: – | p_trx_date_from – starting shipping transaction date, mandatory – | p_trx_date_to – ending shipping transaction date, mandatory – | – | Description: – | Report to compare the transaction item cost from the selling internal org – | to the invoice sales price the selling internal organization is charging the – | receiving organization. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 01 May 2017 Douglas Volz Initial Coding – | 1.1 03 Sep 2019 Douglas Volz Add Ledger, Operating Unit, Item Type, Status – | and item categories for cost and inventory. – | 1.2 27 Jan 2020 Douglas Volz Added Ledger, Operating Unit and Org Code – | parameters. – | 1.3 09 Jul 2022 Douglas Volz Changes for multi-language lookup values. – | 1.4 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

Transaction Date From, Transaction Date To, Category Set 1, Category Set 2, Category Set 3, Ledger

Oracle EBS Tables Used

ra_customer_trx_lines_all, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, mtl_material_transactions, mtl_transaction_types, oe_order_lines_all, oe_order_headers_all, mtl_system_items_vl, xxen_report_org_access

Report Categories

Enginatics

CAC Internal Order Shipment Margin, CAC Interface Error Summary, CAC AP Accrual IR ISO Match Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, GL Account Analysis, INV Material Account Distribution Detail, CAC Deferred COGS Out-of-Balance, CAC Purchase Price Variance

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 Intercompany SO Shipping Transaction vs. Invoice Price 10-Jul-2022 224012.xlsx
Blitz Report™ XML Import CAC_Intercompany_SO_Shipping_Transaction_vs_Invoice_Price.xml
SQL Source CAC Intercompany SO Shipping Transaction vs- Invoice Price.sql
Full Report on Enginatics www.enginatics.com/reports/cac-intercompany-so-shipping-transaction-vs-invoice-price/

Case Study & Technical Analysis: CAC Intercompany SO Shipping Transaction vs. Invoice Price

Executive Summary

The CAC Intercompany SO Shipping Transaction vs. Invoice Price report is a transactional audit tool that verifies the financial accuracy of intercompany shipments. It compares the cost of the item at the time of shipment (from the selling organization) against the actual invoice price charged to the receiving organization. This report is vital for detecting revenue leakage and ensuring that intercompany billing aligns with transfer pricing policies.

Business Challenge

In day-to-day operations, discrepancies can arise between the expected transfer price and the actual invoiced amount.

Solution

This report provides a line-level reconciliation of shipping transactions.

Technical Architecture

The report bridges the gap between Supply Chain and Finance:

Parameters

Performance

FAQ

Q: Why would the invoice price differ from the price list? A: Manual overrides on the sales order, modifiers/discounts applied inadvertently, or price list changes between order booking and invoicing can cause discrepancies.

Q: Does this report show the receiving side? A: This report focuses on the selling side (Shipment and AR Invoice). The receiving side (Receipt and AP Invoice) is typically handled by separate reconciliation reports.

Q: Can I use this for external customers? A: While the logic is similar, this report is tailored for Intercompany flows where the relationship between Cost and Price is strictly governed by policy.

© Enginatics GmbH