CAC Margin Analysis 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, based on the standard Oracle Margin table, cst_margin_summary. (If you want to show the COGS and Sales Accounts use report CAC Margin Analysis Account Summary.)

Note: in order to run this report, you first need to run the Margin Analysis Load Run request (to populate the standard Oracle Margin table).

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) – | – | 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 apps.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 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

cst_margin_summary, mtl_system_items_vl, mtl_units_of_measure_vl, fnd_common_lookups, 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, org_access_view

Report Categories

Enginatics

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 Summary 24-Jun-2022 063517.xlsx
Blitz Report™ XML Import	CAC_Margin_Analysis_Summary.xml
Full SQL on Enginatics	www.enginatics.com/reports/cac-margin-analysis-summary/

Case Study & Technical Analysis: CAC Margin Analysis Summary

Executive Summary

The CAC Margin Analysis Summary report is a high-level profitability tool. Unlike its “Account Summary” counterpart, this report strips away the General Ledger complexity to focus purely on the business metrics: Quantity, Revenue, Cost, and Margin. It is designed for Sales Managers, Product Managers, and Executives.

Business Challenge

Business leaders need quick answers to profitability questions.

Customer Profitability: Which customers are generating the most margin?
Product Mix: Are we selling high-volume/low-margin items or low-volume/high-margin items?
Trend Analysis: How is our margin trending month-over-month?

Solution

This report provides a clean, tabular view of margin performance.

Aggregated View: Can be summarized by Customer, Item Category, or Sales Order.
Metrics: Reports Invoiced Qty, Sales Amount, COGS Amount, Margin Amount, and Margin %.
Source: Uses the official Oracle Margin table to ensure consistency with financial reports.

Technical Architecture

Source: cst_margin_summary.
Simplification: Removes the joins to gl_code_combinations found in the “Account Summary” version, making the output cleaner for non-finance users.
Categorization: Includes Item Category sets to allow for product line analysis.

Parameters

Transaction Date From/To: (Mandatory) Reporting period.
Category Set: (Optional) To group items by product family.
Customer: (Optional) To focus on key accounts.

Performance

Fast: Reading from the summary table is generally faster than querying raw Order Management and AR tables.
Prerequisite: Requires the “Margin Analysis Load Run” to be up to date.

FAQ

Q: Does this match the P&L? A: It should match the Gross Margin line of the P&L, assuming the “Margin Analysis Load Run” was executed for the same period and all COGS/Revenue entries were captured.

Q: Can I see the invoice number? A: Yes, the underlying table links to the AR Invoice, allowing for drill-down if the report layout is modified.

Q: How are returns handled? A: RMAs (Returns) typically appear as negative revenue and negative cost, reducing the total margin.

CAC Margin Analysis Summary | Oracle EBS SQL Report

Report for the margin from the customer invoices and shipments, based on the standard Oracle Margin table, cstmarginsummary. (If you want to show the COGS…