CAC Margin Analysis Account Summary

Description

Categories
Enginatics
Repository
GitHub
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.

/* +=============================================================================+
-- | 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])
-- |
-- | 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.
-- | 1.13 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards.
-- +=============================================================================+*/

SQL 94 lines

Parameters 10

ParameterTypeList of values SQL
Transaction Date FromDate
cms.gl_date>=:p_trx_date_from
Transaction Date ToDate
cms.gl_date<:p_trx_date_to+1
Category Set 1LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Category Set 2LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Category Set 3LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Customer NameLOV
hp.party_name=:p_customer_name
Item NumberLOV
msiv.concatenated_segments=:p_item_number
Organization CodeLOV
mp.organization_code=:p_org_code
Operating UnitLOV
haouv_ou.name=:p_operating_unit
LedgerLOV
gl.name=:p_ledger