CAC Resource Costs

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to show the resource and outside processing costs by organization and cost type.

Parameters:
Cost Type: enter the cost type you wish to report, defaults to your Costing Method (mandatory).
Include Non-Costed Resources: choose No to exclude non-costed resources, choose Yes to include them. You normally use non-costed resources for scheduling purposes (mandatory).
Organizat ...  Report to show the resource and outside processing costs by organization and cost type.

Parameters:
Cost Type: enter the cost type you wish to report, defaults to your Costing Method (mandatory).
Include Non-Costed Resources: choose No to exclude non-costed resources, choose Yes to include them. You normally use non-costed resources for scheduling purposes (mandatory).
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-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_resource_costs_rept.sql
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 18 Nov 2010 Douglas Volz Initial Coding
-- | 1.1 15 Dec 2010 Douglas Volz Final Coding
-- | 1.2 28 Dec 2010 Douglas Volz Removed condition to omit Orgs 108, 109, 110
-- | and 331 as these may be used at any time
-- | 1.3 18 Nov 2015 Douglas Volz Removed organization restrictions
-- | 1.4 15 Nov 2016 Douglas Volz Added DFF for Resource_Type, changed
-- | cancatenated segments to separate columns.
-- | 1.5 18 Nov 2016 Douglas Volz Added Activity to this report
-- | 1.6 16 Feb 2017 Douglas Volz Added Creation_Date and Who Created
-- | 1.7 17 Feb 2017 Douglas Volz Add Resource_Description
-- | 1.8 18 Sep 2019 Douglas Volz Added last update by and last update date
-- | 1.9 27 Jan 2020 Douglas Volz Changed to multi-language views for the item
-- | master, inventory orgs and operating units.
-- | 1.10 21 Jul 2021 Douglas Volz Add Basis Type, UOM Code and changed Allow
-- | Costs and Std Rate to lookup codes.
-- | 1.11 11 Oct 2023 Douglas Volz Modified for invalid created by, invalid
-- | last updated by and invalid code_combination_id
-- | values, to ensure resources are reported.
-- | Removed tabs and added inventory org access controls.
-- | Reversed revision 1.4, added back concatentated
-- | account number values.
-- +=============================================================================+*/   more
Run CAC Resource Costs and other Oracle EBS reports with Blitz Report™ on our demo environment
select  nvl(gl.short_name, gl.name) Ledger,
-- =================================================
-- Get the costed bom resources
-- =================================================
        haou2.name Operating_Unit,
        mp.organization_code Org_Code,
        br.resource_code Resource_Code,
        -- Revision for version 1.7
        br.description Resource_Description,
        cec.cost_element Cost_Element,
        cct.cost_type Cost_Type,
        gl.currency_code Currency_Code,
        nvl(crc.resource_rate,0) Resource_Rate,
        ml1.meaning Resource_Type,
        ml2.meaning Charge_Type,
        -- Revision for version 1.10
        ml3.meaning Allow_Costs,
        br.unit_of_measure UOM_Code,
        ml4.meaning Standard_Rate,
        -- End revision for version 1.10
        msiv.concatenated_segments OSP_Item_Number,
        msiv.description OSP_Item_Description,
        -- Revision for version 1.4 
        -- Revision for version 1.5
        -- br.attribute1 Res Type, 
        (select   ca.activity
         from     cst_activities ca
         where    ca.activity_id = br.default_activity_id) Activity, 
        -- End revision for version 1.5
        -- Revision for version 1.4
        &segment_columns
 -- Revision for version 1.11
        gcc2.concatenated_segments Absorption_Account,
        gcc1.concatenated_segments Variance_Account,
 -- End revision for version 1.11
        br.disable_date Disable_Date,
        -- Revision for version 1.6
        br.creation_date Creation_Date,
        fu.user_name Created_By,
        -- Revision for version 1.8
        br.last_update_date Last_Update_Date,
        fu2.user_name Last_Updated_By
from    cst_resource_costs crc,
        bom_resources br,
        mtl_system_items_vl msiv,
        mtl_parameters mp,
        cst_cost_elements cec,
        cst_cost_types cct,
        hr_organization_information hoi,
        hr_all_organization_units_vl haou,
        hr_all_organization_units_vl haou2,
        gl_ledgers gl,
        mfg_lookups ml1, -- Resource Type
        mfg_lookups ml2, -- Charge Type
        -- Revision for version 1.10
        mfg_lookups ml3, -- Allow Costs
        mfg_lookups ml4, -- Standard Rate Flag
        -- End revision for version 1.10
        -- Revision for version 1.4
        gl_code_combinations_kfv gcc1,
        gl_code_combinations_kfv gcc2,
        -- Revision for version 1.6
        fnd_user fu,
        -- Revision for version 1.8
        fnd_user fu2
-- =================================================
-- Joins for the items, costs and resources
-- =================================================
where
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) and
crc.resource_id             = br.resource_id
and     crc.organization_id         = mp.organization_id
and     crc.cost_type_id            = cct.cost_type_id
and     1=1
and     br.purchase_item_id         = msiv.inventory_item_id (+)
and     br.organization_id          = msiv.organization_id (+)
and     mp.organization_id          = br.organization_id
and     br.cost_element_id          = cec.cost_element_id
and     br.allow_costs_flag         = 1 -- Yes
-- =================================================
-- Eliminate orgs no longer in use
and     mp.organization_id         <> mp.master_organization_id
-- =================================================
-- Joins for the lookup codes
-- =================================================
and     ml1.lookup_type             = 'BOM_RESOURCE_TYPE'
and     ml1.lookup_code             = br.resource_type
and     ml2.lookup_type             = 'BOM_AUTOCHARGE_TYPE'
and     ml2.lookup_code             = br.autocharge_type
-- Revision for version 1.10
and     ml3.lookup_type             = 'SYS_YES_NO'
and     ml3.lookup_code             = br.allow_costs_flag
and     ml4.lookup_type             = 'SYS_YES_NO'
and     ml4.lookup_code             = br.standard_rate_flag
-- End revision for version 1.10
-- =================================================
-- Organization joins to the HR org model
-- =================================================
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     haou2.organization_id       = to_number(hoi.org_information3) -- this gets the operating unit id
and     gl.ledger_id                = to_number(hoi.org_information1) -- get the ledger_id
-- avoid selecting disabled inventory organizations
and     sysdate < nvl(haou.date_to, sysdate + 1)
and     2=2
-- =================================================
-- Joins for the account numbers
-- =================================================
-- Revision for version 1.11, add outer joins
and     br.rate_variance_account    = gcc1.code_combination_id (+)
and     br.absorption_account       = gcc2.code_combination_id (+)
-- =================================================
-- Find the Created_By, revision 1.6 and 1.8
-- =================================================
and     fu.user_id (+)              = br.created_by
and     fu2.user_id (+)             = br.last_updated_by
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)
-- End revision for version 1.11
union all
-- =================================================
-- Get the non-costed bom resources
-- =================================================
select  nvl(gl.short_name, gl.name) Ledger,
        haou2.name Operating_Unit,
        mp.organization_code Org_Code,
        br.resource_code Resource_Code,
        -- Revision for version 1.7
        br.description Resource_Description,
        null Cost_Element,
        null Cost_Type,
        null Currency_Code,
        null Resource_Rate,
        ml1.meaning Resource_Type,
        ml2.meaning Charge_Type,
        -- Revision for version 1.10
        ml3.meaning Allow_Costs,
        br.unit_of_measure UOM_Code,
        ml4.meaning Standard_Rate,
        -- End revision for version 1.10
        msiv.concatenated_segments OSP_Item_Number,
        msiv.description OSP_Item_Description,
        -- Revision for version 1.5
        -- br.attribute1 Res Type, 
        (select   ca.activity
         from     cst_activities ca
         where    ca.activity_id = br.default_activity_id) Activity, 
        -- End revision for version 1.5
        -- Revision for version 1.4
        &segment_columns2
 -- Revision for version 1.11
        null Absorption_Account,
        null Variance_Account,
 -- End revision for version 1.11
        br.disable_date Disable_Date,
        -- Revision for version 1.6
        br.creation_date Creation_Date,
        fu.user_name Created_By,
        -- Revision for version 1.8
        br.last_update_date Last_Update_Date,
        fu2.user_name Last_Updated_By
from    bom_resources br,
        mtl_system_items_vl msiv,
        mtl_parameters mp,
        hr_organization_information hoi,
        hr_all_organization_units_vl haou,
        hr_all_organization_units_vl haou2,
        gl_ledgers gl,
        mfg_lookups ml1,
        mfg_lookups ml2,
        -- Revision for version 1.10
        mfg_lookups ml3, -- Allow Costs
        mfg_lookups ml4, -- Standard Rate Flag
        -- End revision for version 1.10
        -- Revision for version 1.6
        fnd_user fu,
        -- Revision for version 1.8
        fnd_user fu2
-- =================================================
-- Joins for the items, costs and resources
-- =================================================
where
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)
and     br.purchase_item_id         = msiv.inventory_item_id (+)
and     br.organization_id          = msiv.organization_id (+)
and     mp.organization_id          = br.organization_id
and     br.allow_costs_flag         = 2 -- No
-- =================================================
-- Include or exclude non-costed resources
-- =================================================
and     3=3
-- =================================================
-- Eliminate orgs no longer in use
and     mp.organization_id         <> mp.master_organization_id
-- =================================================
-- Joins for the lookup codes
-- =================================================
and     ml1.lookup_type             = 'BOM_RESOURCE_TYPE'
and     ml1.lookup_code             = br.resource_type
and     ml2.lookup_type             = 'BOM_AUTOCHARGE_TYPE'
and     ml2.lookup_code             = br.autocharge_type
-- Revision for version 1.10
and     ml3.lookup_type             = 'SYS_YES_NO'
and     ml3.lookup_code             = br.allow_costs_flag
and     ml4.lookup_type             = 'SYS_YES_NO'
and     ml4.lookup_code             = br.standard_rate_flag
-- End revision for version 1.10
-- =================================================
-- Organization joins to the HR org model
-- =================================================
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     haou2.organization_id       = to_number(hoi.org_information3) -- this gets the operating unit id
and     gl.ledger_id                = to_number(hoi.org_information1) -- get the ledger_id
-- avoid selecting disabled inventory organizations
and     sysdate < nvl(haou.date_to, sysdate + 1)
and     2=2
-- =================================================
-- Find the Created_By, revision 1.6 and 1.8
-- =================================================
-- Revision for version 1.11
and     fu.user_id (+)              = br.created_by
and     fu2.user_id (+)             = br.last_updated_by
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)
-- End revision for version 1.11
-- Order by Ledger, Operating_Unit, Org_Code, Resource and Cost_Element
order by 1,2,3,4,6
Parameter Name SQL text Validation
Cost Type 
cct.cost_type = :p_cost_type
 LOV
Include Non-Costed Resources 
:p_include_non_costed_res = 'Y'
 LOV Oracle
Organization Code 
mp.organization_code = :p_org_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
 
Related Blitz Reports
COPY OF: CAC Resource Costs CAC Item Cost & Routing CAC Item Cost Break-Out by Activity CAC Missing WIP Accounting Transactions CAC Load More4Apps Buy Item Costs CAC Material Account Detail CAC ICP PII Material Account Detail CAC Resources by Department Setup CAC WIP Account Detail
Blitz Report™

Blitz Report™ provides multiple benefits: