CAC Item Cost Comparison

Description

Categories
Enginatics
Repository
GitHub
Report to compare the item costs from two cost types in any two inventory orgs, converting the Org Code 1 (Source Org) into the currency of Org Code 2 (To Org). Put the smallest cost type into Cost Type 1, as the values for cost type 1 are always reported even if not in cost type 2. Note that the Source Org and the Compared To Org default from the inventory organization as set for your session.

/* +=============================================================================+
-- | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | 1.14 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards.
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Parameters:
-- | p_org_code1 -- The inventory organization that is the source
-- | p_org_code2 -- The inventory organization that is the target
-- | p_cost_type1 -- The source comparison cost type
-- | p_cost_type2 -- The target comparison cost type
-- | p_curr_conv_type -- Conversion conversion type, to convert the Source
-- | org, org_code1, into the currency of the To_Org,
-- | org_code2
-- | p_curr_conv_date -- The currency conversion date, typically a month-end
-- | date
-- | p_min_cost_diff -- Minimum material cost diff. to show on the report
-- | p_category_set1 -- The first item category set to report, typically the
-- | Cost or Product Line Category Set
-- | p_category_set2 -- The second item category set to report, typically the
-- | Inventory Category Set
-- |
-- | Description:
-- | Report to compare the item costs from two cost types in any two inventory orgs,
-- | converting the Org_Code 1 (Source_Org) into the currency of Org_Code 2 (To_Org).
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 02 APR 2006 Douglas Volz Initial Coding
-- | 1.1 14 Apr 2006 Douglas Volz Final Coding for client
-- | 1.4 13 JUN 2006 Douglas Volz Added nvl to all material costs columns
-- | 1.5 02 NOV 2009 Douglas Volz Changed to compare the item costs for two orgs
-- | across different currencies
-- | 1.6 21 Nov 2016 Douglas Volz Take out currencies, allow comparison within
-- | the same inventory org but using different
-- | cost types
-- | 1.7 18 Dec 2018 Douglas Volz Add currencies back in
-- | 1.8 30 Aug 2019 Douglas Volz Add cost and inventory item categories,
-- | item type and item status.
-- | 1.9 01 Sep 2020 Douglas Volz Added costs by cost element.
-- | 1.10 11 Sep 2020 Douglas Volz Added ability to report cost type1 even when
-- | the item is not in cost type1. Made cic2 a
-- | table select to avoid sql outer join errors.
-- | 1.11 24 Oct 2020 Douglas Volz Fix bug to convert into any currency, not just USD.
-- | 1.12 02 Nov 2020 Douglas Volz Again, fix to report cost type 1 even when the
-- | item is not in cost type 2.
-- | 1.13 05 Jan 2022 Douglas Volz Add lot size, shrinkage rate, based on rollup
-- | flags. Change calc. for percent difference.
+=============================================================================+*/

SQL 134 lines

Parameters 11

ParameterTypeList of values SQL
Cost Type 1LOV
cct1.cost_type=:p_cost_type1
Compared to Cost Type 2LOV
cct2.cost_type=:p_cost_type2
Source Org (Org Code 1)LOV
mp.organization_code=:p_org_code1
Compared to Org (Org Code 2)LOV
mp2.organization_code=:p_org_code2
Minimum Cost DifferenceNumber
abs(nvl(cic1.item_cost,0)*o2.conversion_rate-nvl(cic2.item_cost,0))>=:p_min_cost_diff
Currency Conversion TypeLOV
Currency Conversion DateDate
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
Item NumberLOV
msiv.concatenated_segments=:p_item_number