CAC Margin Analysis Account Summary – Oracle EBS SQL Report

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

Overview

Report for the margin from the customer invoices and shipments, including the Sales and COGS accounts, based on the standard Oracle Margin table, cst_margin_summary. (If you want to show the COGS and Sales Margins without accounts, use report CAC Margin Analysis Summary.)

Notes: 1) In order to run this report, you first need to run the Margin Analysis Load Run request (to populate the standard Oracle Margin table). 2) If you have customized Subledger Accounting or used custom programs to record COGS by cost element, this report shows only the first COGS account, as there is only one reported row per sales order line.

Parameters:

Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Customer Name: enter the specific customer name you wish to report (optional). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+ – | Copyright 2006 - 2024 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_margin_analysis_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 18 APR 2006 Douglas Volz Initial Coding – | 1.1 15 MAY 2006 Douglas Volz Working version – | 1.2 17 MAY 2006 Douglas Volz Added order line and date information – | 1.3 14 Dec 2012 Douglas Volz Modified for Garlock, changed category set – | 1.4 19 Dec 2012 Douglas Volz Bug fix for category set name – | 1.5 29 Jan 2013 Douglas Volz Fixed date parameters to have same format – | as other reports; had to remove sales and COGS accounts as these are – | on different rows and would need to rewrite the code to include this. – | Also added a join for mic and mcs for category_set_id to avoid duplicate rows. – | And added a having clause to screen out zero rows. – | 1.6 25 Feb 2013 Douglas Volz Added mtl_default_category_sets mdcs table – | to make the script more generic – | 1.7 27 Feb 2017 Douglas Volz Modified for Item Category and customer – | information.
– | 1.8 28 Feb 2017 Douglas Volz Removed sales rep information, – | was causing cross-joining. – | 1.9 22 May 2017 Douglas Volz Adding Inventory item category – | 1.10 23 May 2020 Douglas Volz Use multi-language table for UOM Code, item master – | OE transaction types and hr organization names. – | 1.11 06 Nov 2020 Douglas Volz Fix for having custom, multiple COGS accounts by – | cost element. Now only get one COGS account. – | 1.12 14 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and org access controls. – +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, Category Set 1, Category Set 2, Category Set 3, Customer Name, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_system_items_vl, mtl_units_of_measure_vl, fnd_common_lookups, gl_code_combinations, mtl_parameters, so_order_types_all, oe_transaction_types_tl, hz_cust_accounts_all, hz_parties, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, cst_margin_summary, org_access_view

Report Categories

Enginatics

CAC Margin Analysis Summary, CAC Internal Order Shipment Margin, CAC Intercompany SO Price List vs. Item Cost Comparison, GL Account Distribution Analysis, GL Account Analysis, CAC AP Accrual IR ISO Match Analysis

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 Margin Analysis Account Summary 07-Jul-2022 151652.xlsx
Blitz Report™ XML Import CAC_Margin_Analysis_Account_Summary.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-margin-analysis-account-summary/

Case Study & Technical Analysis: CAC Margin Analysis Account Summary

Executive Summary

The CAC Margin Analysis Account Summary report bridges the gap between Sales Operations and Financial Accounting. It reports the Gross Margin (Revenue - COGS) for customer shipments, while explicitly listing the General Ledger accounts used for both sides of the transaction. This is essential for reconciling the “Managerial” margin to the “Financial” margin.

Business Challenge

Solution

This report leverages the CST_MARGIN_SUMMARY table (populated by a concurrent request).

Technical Architecture

Parameters

Performance

FAQ

Q: Why is the report empty? A: You likely haven’t run the “Margin Analysis Load Run” program for the requested period. This report reads a snapshot table, not raw transactions.

Q: Why do I see multiple lines for one order line? A: If a single sales order line was shipped in multiple partial shipments, or if the COGS account was split (e.g., across cost centers), you will see multiple rows.

Q: Does it include freight? A: Only if the freight is invoiced as a line item or included in the COGS calculation.

© 2026 Enginatics