CAC User-Defined and Rolled Up Costs

Description
Categories: Enginatics
Repository: Github
Use this report to find items with both user-defined (manually entered) and rolled up costs, for material and other cost elements. Useful to find rolled up assemblies where the item costs have been accidentally doubled-up.

Parameters:
===========
Cost Type: enter the cost type to report (mandatory).
Category Set 1: any item category you wish, typically the Cost or Product Line ca ... 
Use this report to find items with both user-defined (manually entered) and rolled up costs, for material and other cost elements. Useful to find rolled up assemblies where the item costs have been accidentally doubled-up.

Parameters:
===========
Cost Type: enter the cost type to report (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).
Assignment Set: enter the sourcing rule assignment set, used when transferring items between inventory organizations on internal requisitions.
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 2010-2023 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_user_defined_rolled_up_cost_rept.sql
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 29 Dec 2010 Douglas Volz Create new report to find items with
-- | both manually entered and rolled up material costs
-- | 1.1 24 May 2011 Douglas Volz Bug fix for the resource code column
-- | 1.2 19 Oct 2019 Douglas Volz Add columns for non-material costs
-- | 1.3 27 Jan 2020 Douglas Volz Added Operating_Unit parameter and outer
-- | join for Item_Type.
-- | 1.4 05 May 2021 Douglas Volz Modify for multi-language tables.
-- | 1.5 12 Jan 2023 Douglas Volz Correction for definition of manually entered
-- | material costs.
-- | 1.6 20 Nov 2023 Douglas Volz Fix for Manual Other Costs and Rolled Up Other Costs columns.
-- | 1.7 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions.
-- | 1.8 07 Jan 2024 Douglas Volz Add onhand quantities, to help find valuation issues. Remove tabs.
-- +=============================================================================+*/
   more
select  nvl(gl.short_name, gl.name) Ledger,
        haou2.name Operating_Unit,
        mp.organization_code Org_Code,
        cct.cost_type Cost_Type,
        msiv.concatenated_segments Item_Number,
        msiv.description Item_Description,
        -- Revision for version 1.4
        muomv.uom_code UOM_Code,
        fcl.meaning Item_Type,
        -- Revision for version 1.4
        misv.inventory_item_status_code Item_Status,
        ml1.meaning Make_Buy_Code,
&category_columns
        -- Revision for version 1.2 and 1.4
        nvl((select max(ml2.meaning)
             from   bom_resources br,
                    cst_item_cost_details cicd,
                    mfg_lookups ml2 -- rollup source type
             where  br.resource_id         = cicd.resource_id
             and    cicd.inventory_item_id = cic.inventory_item_id
             and    cicd.organization_id   = cic.organization_id
             and    cicd.cost_type_id      = cic.cost_type_id
             and    cicd.item_cost        <> 0
             and    cicd.cost_element_id   = 1
             and    ml2.lookup_type        = 'CST_SOURCE_TYPE'
             and    ml2.lookup_code        = cicd.rollup_source_type),'')  Rollup_Source_Type,
        -- End revision for version 1.2
        -- Bug fix for version 1.1
        -- (select distinct br.resource_code
        (select max(br.resource_code)
        -- End bug fix for version 1.1
         from   bom_resources br,
                cst_item_cost_details cicd
         where  br.resource_id         = cicd.resource_id
         and    cicd.inventory_item_id = cic.inventory_item_id
         and    cicd.organization_id   = cic.organization_id
         and    cicd.cost_type_id      = cic.cost_type_id
         and    cicd.item_cost        <> 0
         -- End bug fix for version 1.1
         and    cicd.cost_element_id   = 1) Material_Sub_Element,
        -- check to see if a bom exists
        (select fl.meaning
         from   fnd_lookups fl
         where  fl.lookup_type = 'YES_NO'
         and    fl.lookup_code =  
                nvl((select distinct 'Y'
                     from   bom_structures_b bom
                     where  bom.organization_id      = mp.organization_id
                     and    bom.assembly_item_id     = cic.inventory_item_id
                     and    bom.alternate_bom_designator is null),
                'N')
        ) BOM,
        -- check to see if a routing exists
        (select fl.meaning
         from   fnd_lookups fl
         where  fl.lookup_type = 'YES_NO'
         and    fl.lookup_code =  
                nvl((select distinct 'Y'
                     from   bom_operational_routings bor
                     where  bor.organization_id      = mp.organization_id
                     and    bor.assembly_item_id     = cic.inventory_item_id
                     and    bor.alternate_routing_designator is null),
                'N')
        ) Routing,
        -- check to see if a sourcing rule exists for the receipt org
        (select fl.meaning
         from   fnd_lookups fl
         where  fl.lookup_type = 'YES_NO'
         and    fl.lookup_code =  
                 nvl((select distinct 'Y'
                      from   mrp_sr_receipt_org msro,
                             mrp_sr_source_org msso,
                             mrp_sourcing_rules msr,
                             mrp_sr_assignments msa,
                             mrp_assignment_sets mas
                      where  msr.sourcing_rule_id    = msro.sourcing_rule_id
                      -- fix for version 1.4, check to see if the sourcing rule is
                      -- for an inventory org, not a vendor
                      and    msso.sr_receipt_id      = msro.sr_receipt_id
                      and    msso.source_organization_id is not null
                      and    msa.sourcing_rule_id    = msr.sourcing_rule_id
                      and    msa.assignment_set_id   = mas.assignment_set_id
                      and    msiv.organization_id    = msa.organization_id
                      and    msiv.inventory_item_id  = msa.inventory_item_id
                      and    mp.organization_id      = msa.organization_id
                      and    2=2),                  -- p_assignment_set
        'N')
        ) Sourcing_Rule,
        -- Revision for version 1.4
        ml2.meaning Based_on_Rollup,
        ml3.meaning Inventory_Asset,
        -- End revision for version 1.4
        gl.currency_code Currency_Code,
        cic.item_cost Item_Cost,
        nvl((select sum(nvl(cicd.item_cost,0))
             from   bom_resources br,
                    cst_item_cost_details cicd
             where  br.resource_id          = cicd.resource_id
             and    cicd.inventory_item_id  = cic.inventory_item_id
             and    cicd.organization_id    = cic.organization_id
             and    cicd.cost_type_id       = cic.cost_type_id
             and    cicd.item_cost         <> 0
             and    cicd.cost_element_id    = 1 -- Material Costs
             and    cicd.level_type         = 1 -- This Level
             -- Revision for version 1.5
             -- and    cicd.rollup_source_type in (1,2) -- manually entered or defaulted
             and    cicd.rollup_source_type = 1      -- manually entered
             and    cicd.cost_element_id    = 1),0) Manual_Material_Costs,
        nvl((select sum(nvl(cicd.item_cost,0))
             from   cst_item_cost_details cicd
             where  cicd.inventory_item_id  = cic.inventory_item_id
             and    cicd.organization_id    = cic.organization_id
             and    cicd.cost_type_id       = cic.cost_type_id
             and    cicd.item_cost         <> 0
             and    cicd.cost_element_id    = 1 -- Material Costs
             and    cicd.level_type         = 2 -- Previous Level
             and    cicd.rollup_source_type = 3 -- rolled up material costs
             and    cicd.cost_element_id    = 1),0) Rolled_Up_Material_Costs,
        -- Revision for version 1.2
        nvl((select sum(nvl(cicd.item_cost,0))
             -- Revision for version 1.6
             -- from   bom_resources br,
             --        cst_item_cost_details cicd
             from   cst_item_cost_details cicd
             -- Revision for version 1.6
             -- where  br.resource_id          = cicd.resource_id
             -- and    cicd.inventory_item_id  = cic.inventory_item_id
             where  cicd.inventory_item_id  = cic.inventory_item_id
             and    cicd.organization_id    = cic.organization_id
             and    cicd.cost_type_id       = cic.cost_type_id
             and    cicd.item_cost         <> 0
             and    cicd.cost_element_id   <> 1 -- Material Costs
             and    cicd.level_type         = 1 -- This Level
             -- Revision for version 1.5
             -- and    cicd.rollup_source_type in (1,2) -- manually entered or defaulted
             and    cicd.rollup_source_type = 1      -- manually entered
             -- Revision for version 1.6
             -- and    cicd.cost_element_id    = 1),0) Manual_Other_Costs,
            ),0) Manual_Other_Costs,
        nvl((select sum(nvl(cicd.item_cost,0))
             from   cst_item_cost_details cicd
             where  cicd.inventory_item_id  = cic.inventory_item_id
             and    cicd.organization_id    = cic.organization_id
             and    cicd.cost_type_id       = cic.cost_type_id
             and    cicd.item_cost         <> 0
             and    cicd.cost_element_id   <> 1 -- Material Costs
             -- Revision for version 1.6
             -- and    cicd.level_type         = 2 -- Previous Level
             -- Revision for version 1.5
             -- and    cicd.rollup_source_type = 3      -- rolled up material costs
             and    cicd.rollup_source_type in (2,3) -- rolled up and defaulted material costs
             -- Revision for version 1.6
             -- and    cicd.cost_element_id    = 1),0) Rolled_Up_Other_Costs,
            ),0) Rolled_Up_Other_Costs,
        -- End revision for version 1.6
        -- Revision for version 1.8
        onhand.quantity Onhand_Quantity,
        -- End revision for version 1.8
        cic.creation_date Cost_Creation_Date,
        msiv.creation_date Item_Creation_Date
        -- End revision for version 1.2
from    mtl_parameters mp,
        mtl_system_items_vl msiv,
        -- Revision for version 1.4
        mtl_units_of_measure_vl muomv,
        mtl_item_status_vl misv, 
        -- End revision for version 1.4
        cst_item_costs cic,
        cst_cost_types cct,
        -- Revision for version 1.8
        (select moqd.organization_id,
                moqd.inventory_item_id,
                sum(moqd.transaction_quantity) quantity
         from   mtl_onhand_quantities_detail moqd,
                mtl_parameters mp
         where  moqd.is_consigned              = 2 -- No
         and    mp.organization_id             = moqd.organization_id
         and    3=3                            -- p_org_code
         group by
                moqd.organization_id,
                moqd.inventory_item_id
        ) onhand,
        -- End revision for version 1.8
        mfg_lookups ml1, -- planning make buy code
        -- Revision for version 1.4
        mfg_lookups ml2, -- based on rollup, CST_BONROLLUP_VAL
        mfg_lookups ml3, -- Cost inventory_asset_flag, SYS_YES_NO
        -- End revision for version 1.4
        fnd_common_lookups fcl,
        hr_organization_information hoi,
        hr_all_organization_units haou,  -- inv_organization_id
        hr_all_organization_units haou2, -- operating unit
        gl_ledgers gl
-- ===================================================================
-- Cost type, organization, item master and report specific controls
-- ===================================================================
where   cic.cost_type_id                = cct.cost_type_id
and     cct.cost_type                   = nvl('&p_cost_type',cct.cost_type)                             -- p_cost_type
and     mp.organization_id              = cic.organization_id
and     msiv.organization_id            = cic.organization_id
and     msiv.inventory_item_id          = cic.inventory_item_id
and     cic.based_on_rollup_flag        = 1 -- rolled up
-- Revision for version 1.2
and     msiv.inventory_item_status_code <> 'Inactive'
-- Revision for version 1.4
and     msiv.primary_uom_code           = muomv.uom_code
and     msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End revision for version 1.4
-- Revision for version 1.8
and     onhand.organization_id (+)      = cic.organization_id
and     onhand.inventory_item_id (+)    = cic.inventory_item_id
-- End revision for version 1.8
-- Avoid master inventory organizations
and     mp.organization_id             <> mp.master_organization_id -- the item master org usually does not have costs
-- ===================================================================
-- Joins for the lookup codes
-- ===================================================================
and     ml1.lookup_type                 = 'MTL_PLANNING_MAKE_BUY'
and     ml1.lookup_code                 = msiv.planning_make_buy_code
-- Revision for version 1.4
and     ml2.lookup_type                 = 'CST_BONROLLUP_VAL'
and     ml2.lookup_code                 = cic.based_on_rollup_flag
and     ml3.lookup_type                 = 'SYS_YES_NO'
and     ml3.lookup_code                 = cic.inventory_asset_flag
-- End revision for version 1.4
and     fcl.lookup_type (+)             = 'ITEM_TYPE'
and     fcl.lookup_code (+)             = msiv.item_type
-- ===================================================================
-- Using the base tables for HR organization information
-- ===================================================================
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
-- avoid selecting disabled inventory organizations
and     sysdate                         < nvl(haou.date_to, sysdate +1)
and     haou2.organization_id           = to_number(hoi.org_information3) -- this gets the operating unit id
and     hoi.org_information1            = gl.ledger_id                    -- this gets the ledger id
-- Revision for version 1.7
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     gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and     haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
-- End revision for version 1.7
and     1=1                             -- p_cost_type, p_item_number, p_operating_unit, p_ledger
and     3=3                             -- p_org_code
-- ===================================================================
-- Check to see if BOMS or Routings or Sourcing Rules exist
-- ===================================================================
and     exists 
                -- check to see if a BOM exists
                ((select 'x' 
                  from   bom_structures_b bsb
                  where  bsb.organization_id        = cic.organization_id
                  and    bsb.assembly_item_id       = cic.inventory_item_id
                  and    bsb.alternate_bom_designator is null)
                  union all
                  -- check to see if a routing exists
                 (select 'x' 
                  from   bom_operational_routings bor
                  where  bor.organization_id        = cic.organization_id
                  and    bor.assembly_item_id       = cic.inventory_item_id)
                union all
                 (select 'x'
                  from   mrp_sr_receipt_org msro,
                         mrp_sr_source_org msso,
                         mrp_sourcing_rules msr,
                         mrp_sr_assignments msa,
                         mrp_assignment_sets mas
                  where  msr.sourcing_rule_id    = msro.SOURCING_RULE_ID
                  -- fix for version 1.4, check to see if the sourcing rule is
                  -- for an inventory org, not a vendor
                  and    msso.sr_receipt_id      = msro.sr_receipt_id
                  and    msso.source_organization_id is  not null
                  and    msa.sourcing_rule_id    = msr.sourcing_rule_id
                  and    msa.assignment_set_id   = mas.assignment_set_id
                  and    msiv.organization_id    = msa.organization_id
                  and    msiv.inventory_item_id  = msa.inventory_item_id
                  and    2=2)                    -- p_assignment_set
                )
-- ===================================================================
-- Check to see if manually entered costs exist for This Level Costs
-- ===================================================================
and     exists
                (select 'x'
                 from   cst_item_cost_details cicd
                 where  cicd.organization_id    = cic.organization_id
                 and    cicd.inventory_item_id  = cic.inventory_item_id
                 and    cicd.cost_type_id       = cic.cost_type_id
                 and    cicd.item_cost         <> 0
                 and    cicd.level_type         = 1 -- This Level
                 and    cicd.cost_element_id    = 1 -- Material Costs
                 -- Revision for version 1.5
                 -- and    cicd.rollup_source_type in (1,2)) -- manually entered or defaulted
                 and    cicd.rollup_source_type = 1)      -- manually entered
-- ===================================================================
-- Check to see if costs exist for Previous Levels / Rolled Up Costs
-- ===================================================================
and     exists
                (select 'x'
                 from   cst_item_cost_details cicd
                 where  cicd.organization_id    = cic.organization_id
                 and    cicd.inventory_item_id  = cic.inventory_item_id
                 and    cicd.cost_type_id       = cic.cost_type_id
                 and    cicd.item_cost         <> 0
                 and    cicd.cost_element_id    = 1 -- Material Costs
                 and    cicd.level_type         = 2 -- Previous Level
                 and    cicd.rollup_source_type in (3)) -- rolled up
-- Order by Ledger, Operating Unit, Org Code, Cost Type, Item Number
order by
        nvl(gl.short_name, gl.name),
        haou2.name,
        mp.organization_code,
        cct.cost_type,
        msiv.concatenated_segments
Parameter Name SQL text Validation
Cost Type
cct.cost_type = :p_cost_type
LOV
Category Set 1