CAC Item Cost Summary
Description
Categories: Enginatics, Toolkit - Cost Accounting
Repository: Github Columns: Ledger, Operating Unit, Org Code, Cost Type, Item Number, Item Description, UOM Code, Item Type, Item Status, Make Buy Code ...
Repository: Github Columns: Ledger, Operating Unit, Org Code, Cost Type, Item Number, Item Description, UOM Code, Item Type, Item Status, Make Buy Code ...
Report to show item costs in any cost type. For one or more inventory organizations.
/* +=============================================================================+
-- | Copyright 2009-2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provi ... more
/* +=============================================================================+
-- | Copyright 2009-2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provi ... more
select nvl(gl.short_name, gl.name) Ledger, -- =================================================================== -- First get the items which are costing enabled -- =================================================================== 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.7 muomv.uom_code UOM_Code, fcl.meaning Item_Type, -- Revision for version 1.7 misv.inventory_item_status_code Item_Status, ml1.meaning Make_Buy_Code, &category_columns fl1.meaning Allow_Costs, ml2.meaning Inventory_Asset, ml3.meaning Based_on_Rollup, cic.shrinkage_rate Shrinkage_Rate, gl.currency_code Currency_Code, -- Revision for version 1.8 msiv.market_price Market_Price, msiv.list_price_per_unit Target_or_PO_List_Price, -- End revision for version 1.8 nvl(cic.material_cost,0) Material_Cost, nvl(cic.material_overhead_cost,0) Material_Overhead_Cost, nvl(cic.resource_cost,0) Resource_Cost, nvl(cic.outside_processing_cost,0) Outside_Processing_Cost, nvl(cic.overhead_cost,0) Overhead_Cost, nvl(cic.item_cost,0) Item_Cost, -- Fix for version 1.3 &segment_columns -- End fix for version 1.3 cic.creation_date Cost_Creation_Date, cic.last_update_date Last_Cost_Update_Date, -- Revision for version 1.9 msiv.creation_date Item_Creation_Date, msiv.last_update_date Item_Last_Update_Date from cst_item_costs cic, cst_cost_types cct, mtl_system_items_vl msiv, -- Revision for version 1.7 mtl_item_status_vl misv, mtl_units_of_measure_vl muomv, -- End revision for version 1.7 mtl_parameters mp, mfg_lookups ml1, -- planning make/buy code, MTL_PLANNING_MAKE_BUY mfg_lookups ml2, -- inventory_asset_flag, SYS_YES_NO mfg_lookups ml3, -- based on rollup, CST_BONROLLUP_VAL fnd_lookups fl1, -- allow costs, YES_NO fnd_common_lookups fcl, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Fix for version 1.3 gl_code_combinations gcc1, gl_code_combinations gcc2 -- End fix for version 1.3 -- =================================================================== -- Item master, organization and item master to cost joins -- =================================================================== where mp.organization_id = msiv.organization_id and msiv.inventory_item_id = cic.inventory_item_id and msiv.organization_id = cic.organization_id -- Revision for version 1.7 and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code -- End revision for version 1.7 and cic.cost_type_id = cct.cost_type_id and 1=1 -- p_item_number, p_org_code, p_operating_unit, p_ledger and 4=4 -- p_cost_type -- =================================================================== -- Don't report the unused inventory organizations -- =================================================================== and mp.organization_id <> mp.master_organization_id -- remove the global master org -- =================================================================== -- Lookup codes -- =================================================================== and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = msiv.planning_make_buy_code and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = to_char(cic.inventory_asset_flag) and ml3.lookup_type = 'CST_BONROLLUP_VAL' and ml3.lookup_code = cic.based_on_rollup_flag and fl1.lookup_type = 'YES_NO' and fl1.lookup_code = msiv.costing_enabled_flag and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv.item_type -- =================================================================== -- 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 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) -- =================================================================== -- Fix for version 1.3 -- joins to get the COGS and revenue accounts -- =================================================================== and gcc1.code_combination_id (+) = msiv.cost_of_sales_account and gcc2.code_combination_id (+) = msiv.sales_account -- End fix for version 1.3 union all -- =================================================================== -- Now get the items which are not costing enabled -- =================================================================== select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, null Cost_Type, msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.7 muomv.uom_code UOM_Code, fcl.meaning Item_Type, -- Revision for version 1.7 misv.inventory_item_status_code Item_Status, ml1.meaning Make_Buy_Code, &category_columns fl1.meaning Allow_Costs, fl2.meaning Inventory_Asset, null Based_on_Rollup, null Shrinkage_Rate, gl.currency_code Currency_Code, -- Revision for version 1.8 msiv.market_price Market_Price, msiv.list_price_per_unit Target_or_PO_List_Price, -- End revision for version 1.8 null Material_Cost, null Material_Overhead_Cost, null Resource_Cost, null Outside_Processing_Cost, null Overhead_Cost, null Item_Cost, -- Fix for version 1.3 &segment_columns null Cost_Creation_Date, null Last_Cost_Update_Date, -- Revision for version 1.9 msiv.creation_date Item_Creation_Date, msiv.last_update_date Item_Last_Update_Date from mtl_system_items_vl msiv, -- Revision for version 1.7 mtl_item_status_vl misv, mtl_units_of_measure_vl muomv, -- End revision for version 1.7 mtl_parameters mp, mfg_lookups ml1, -- planning make/buy code, MTL_PLANNING_MAKE_BUY fnd_lookups fl1, -- inventory_asset_flag, YES_NO fnd_lookups fl2, -- allow costs, YES_NO fnd_common_lookups fcl, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Fix for version 1.3 gl_code_combinations gcc1, gl_code_combinations gcc2 -- End fix for version 1.3 -- =================================================================== -- Item master, organization and item master to cost joins -- =================================================================== where mp.organization_id = msiv.organization_id -- Revision for version 1.7 and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code -- End revision for version 1.7 and 1=1 -- p_item_number, p_org_code, p_operating_unit, p_ledger -- Include or exclude uncosted items and 5=5 -- =================================================================== -- Find items where the item has no cost information -- =================================================================== and msiv.costing_enabled_flag = 'N' -- =================================================================== -- Don't report the unused inventory organizations -- =================================================================== and mp.organization_id <> mp.master_organization_id -- remove the global master org -- =================================================================== -- Lookup codes -- =================================================================== and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = msiv.planning_make_buy_code and fl1.lookup_type = 'YES_NO' and fl1.lookup_code = msiv.costing_enabled_flag and fl2.lookup_type = 'YES_NO' and fl2.lookup_code = msiv.inventory_asset_flag and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv.item_type -- =================================================================== -- 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 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) -- =================================================================== -- Fix for version 1.3 -- joins to get the COGS and revenue accounts -- =================================================================== and gcc1.code_combination_id (+) = msiv.cost_of_sales_account and gcc2.code_combination_id (+) = msiv.sales_account -- order by Ledger, Operating_Unit, Org_Code, Item and Cost_Type order by 1,2,3,4,5 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Chart of Accounts |
|
LOV | |
Ledger |
|
LOV | |
Category Set 1 |
|
LOV | |
Category Set 2 |
|
LOV | |
Item Number |
|
LOV | |
Cost Type |
|
LOV | |
Operating Unit |
|
LOV | |
Organization Code |
|
LOV | |
Include Inactive Items |
|
LOV Oracle | |
Include Uncosted Items |
|
LOV Oracle |