CAC Margin Analysis Summary

Description
Categories: Enginatics
Repository: Github
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:
 ... 
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 ([email protected])
-- |
-- | 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.
-- +=============================================================================+*/
   more
select  nvl(gl.short_name, gl.name) Ledger,
 haou2.name Operating_Unit,
 mp.organization_code Org_Code,
 -- Revision for version 1.7
 -- cms.sold_to_customer_name Sold_To_Customer,
 -- Revision for version 1.10
 cms.customer_class_code Customer_Class_Code,
 hz.party_name Customer,
 hca.account_number Customer_Number,
 -- Revision for version 1.8
 -- Not useful at client site
 -- rsa.name Sales_Rep,
 cms.order_number Order_Number,
 cms.line_number Order_Line,
 -- Revision for version 1.10
 -- sot.name Order_Type,
 ottt.name Order_Type,
 -- Revision for version 1.7
 -- COGS entry has a timestamp, causing two rows
 trunc(cms.gl_date) Transaction_Date,
 msiv.concatenated_segments Item,
 msiv.description  Item_Description,
 -- Revision for version 1.10
 fcl.meaning Item_Type,
 -- Revision for version 1.9
&category_columns
 muomv.uom_code UOM_Code,
        round(sum(nvl(cms.invoiced_amount,0)) / 
            decode(sum(nvl(cms.invoice_line_quantity,0)), 0, 1, sum(nvl(cms.invoice_line_quantity,0))),5) Unit_Price,
        round(sum(nvl(cms.cogs_amount,0)) /
            decode(sum(nvl(cms.ship_quantity,0)),0,1, sum(nvl(cms.ship_quantity,0))),5) Unit_Cost,
 sum(nvl(cms.invoice_line_quantity,0))     Invoice_Quantity,
 sum(nvl(cms.ship_quantity,0))      Ship_Quantity,
 sum(nvl(cms.invoiced_amount,0))     Sales_Amount,
 sum(nvl(cms.cogs_amount,0))      COGS_Amount,
 sum(nvl(cms.invoiced_amount,0)) - sum(nvl(cms.cogs_amount,0))  Margin,
 round((sum(nvl(cms.invoiced_amount,0)) - sum(nvl(cms.cogs_amount,0))) /
  decode(sum(nvl(cms.invoiced_amount,0)), 0, 1, sum(nvl(cms.invoiced_amount,0))) * 100,1) Percent
from cst_margin_summary cms,
 -- Revision for version 1.10
 mtl_system_items_vl msiv,
 mtl_units_of_measure_vl muomv,
 fnd_common_lookups fcl,
 -- End for revision for version 1.10
 mtl_parameters mp,
 so_order_types_all sot,
 -- Revision for version 1.10
 oe_transaction_types_tl ottt,
 -- Revision for version 1.7
 -- ra_customers rc,
 hz_cust_accounts_all hca,
 hz_parties hz,
 -- End revision for version 1.7
 -- Revision for version 1.8
 -- Causing cross-joining
 -- ra_salesreps_all rsa,
 hr_organization_information hoi,
 hr_all_organization_units_vl haou,  -- inv_organization_id
 hr_all_organization_units_vl haou2, -- operating unit
 gl_ledgers gl
-- Revision for version 1.10
where msiv.organization_id         = mp.organization_id
and msiv.organization_id         = cms.parent_organization_id
and msiv.inventory_item_id       = cms.parent_inventory_item_id
and muomv.uom_code               = msiv.primary_uom_code
and fcl.lookup_code (+)          = msiv.item_type
and fcl.lookup_type (+)          = 'ITEM_TYPE'
and ottt.transaction_type_id     = sot.order_type_id
and ottt.language                = userenv('lang')
-- End for revision for version 1.10
and sot.order_type_id            = cms.order_type_id
-- Performance improvement, comment this out
-- and sot.org_id  = cms.org_id
-- Revision for version 1.7
-- and rc.customer_id               = cms.customer_id
and hca.cust_account_id          = cms.customer_id
and hz.party_id                  = hca.party_id
-- Revision for version 1.8
-- Causing cross-joining
-- and rsa.salesrep_id              = cms.primary_salesrep_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 1=1    -- p_trx_date_from, p_trx_date_to, p_customer, p_item_number, p_org_code, p_operating_unit, p_ledger
-- End revision for version 1.5
-- ===================================================================
-- using the base tables to avoid using
-- org_organization_definitions and hr_operating_units
-- ===================================================================
and hoi.org_information_context  = 'Accounting Information'
and hoi.organization_id          = mp.organization_id
and hoi.organization_id          = haou.organization_id   -- this gets the organization name
and cms.org_id                   = haou2.organization_id
and haou2.organization_id        = hoi.org_information3 -- this gets the operating unit id
and gl.ledger_id                 = to_number(hoi.org_information1) -- get the ledger_id
-- ===================================================================
group by 
 nvl(gl.short_name, gl.name),
 haou2.name,
 mp.organization_code,
 -- Revision for version 1.10
 cms.customer_class_code,
 cms.sold_to_customer_name,
 hz.party_name,
 hca.account_number,
 cms.order_number,
 cms.line_number,
 -- Revision for version 1.10
 ottt.name,
 -- Revision for version 1.7
 -- COGS entry has a timestamp, causing two rows
 trunc(cms.gl_date),
 msiv.concatenated_segments,
 msiv.description,
 fcl.meaning, -- Item Type
 -- Revision for version 1.10
 muomv.uom_code,
 -- Revision for version 1.9, needed for inline select
 msiv.inventory_item_id,
 msiv.organization_id
order by
 nvl(gl.short_name, gl.name), -- Ledger
 haou2.name, -- Operating Unit
 mp.organization_code, -- Org Code
 hz.party_name, -- Customer
 cms.order_number, -- Order Number
 cms.line_number -- Order Line
Parameter NameSQL textValidation
Transaction Date From
cms.gl_date >= :p_trx_date_from
Date
Transaction Date To
cms.gl_date < :p_trx_date_to + 1
Date
Category Set 1
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Category Set 2
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Category Set 3
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Customer Name
hz.party_name = :p_customer_name
LOV
Item Number
msiv.concatenated_segments = :p_item_number
LOV
Organization Code
mp.organization_code = :p_org_code
LOV
Operating Unit
haou2.name = :p_operating_unit
LOV
Ledger
gl.name = :p_ledger
LOV