CAC Subinventory Accounts Setup
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report to show accounts used for the subinventories; these valuation and expense accounts are used with Standard Costing.
/* +=============================================================================+
-- | Copyright 2009 - 22 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permiss ... more
/* +=============================================================================+
-- | Copyright 2009 - 22 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permiss ... more
Run
CAC Subinventory Accounts Setup and other Oracle EBS reports with Blitz Report™ on our demo environment
select nvl(gl.short_name, gl.name) Ledger, -- Get the Material Account haou2.name Operating_Unit, mp.organization_code Org_Code, -- Revision for version 1.4 haou.name Organization_Name, -- Revision for version 1.4 (select ccg.cost_group from cst_cost_groups ccg where ccg.cost_group_id = msub.default_cost_group_id) Cost_Group, -- End revision for version 1.4 msub.secondary_inventory_name Subinventory, -- Revision for version 1.8 ml1.meaning Inventory_Asset, ml2.meaning Quantity_Tracked, -- Revision for version 1.9 -- 'Material Account' Account Type, ml3.meaning Account_Type, &segment_columns msub.creation_date Creation_Date, msub.disable_date Disable_Date, fu1.user_name Created_By, fu2.user_name Last_Updated_By -- End revision for version 1.6 from -- Revision for version 1.4 -- Get valuation accounts based on Costing Method and Cost Group Accounting -- mtl_secondary_inventories msub, (-- Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, msub.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method = 1 -- Standard Costing union all -- Not Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, mp.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method <> 1 -- not Standard Costing union all -- With Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, ccga.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and ccga.cost_group_id = msub.default_cost_group_id union all -- Cost Group Accounting but the Subinventory Cost Group Id is null select msub.organization_id, msub.secondary_inventory_name, ccga.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and msub.default_cost_group_id is null and ccga.cost_group_id = mp.default_cost_group_id ) msub, -- End for revision 1.4 mtl_parameters mp, -- Revision for version 1.8 mfg_lookups ml1, mfg_lookups ml2, -- Revision for version 1.9 mfg_lookups ml3, -- Account Type gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_user fu1, fnd_user fu2 where msub.material_account = gcc.code_combination_id (+) and msub.organization_id = mp.organization_id -- Revision for version 1.8 and ml1.lookup_type = 'SYS_YES_NO' and ml1.lookup_code = msub.asset_inventory and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = msub.quantity_tracked -- Revision for version 1.9 and ml3.lookup_type = 'CST_COST_CODE_TYPE' and ml3.lookup_code = 1 -- Material -- =========================================== -- 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 -- Revision for version 1.4 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- Revision for version 1.6 and fu1.user_id = msub.created_by and fu2.user_id = msub.last_updated_by -- Revision for version 1.1, 1.3 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 1=1 -- p_org_code, p_operating_unit, p_ledger union all -- Get the Material Overhead Account select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, -- Revision for version 1.4 haou.name Organization_Name, -- Revision for version 1.4 (select ccg.cost_group from cst_cost_groups ccg where ccg.cost_group_id = msub.default_cost_group_id) Cost_Group, -- End revision for version 1.4 msub.secondary_inventory_name Subinventory, -- Revision for version 1.8 ml1.meaning Inventory_Asset, ml2.meaning Quantity_Tracked, -- Revision for version 1.9 -- 'Matl Ovhd Account' Account_Type, ml3.meaning Account_Type, &segment_columns -- Revision for version 1.6 msub.creation_date Creation_Date, msub.disable_date Disable_Date, fu1.user_name Created_By, fu2.user_name Last_Updated_By -- End revision for version 1.6 from -- Revision for version 1.4 -- Get valuation accounts based on Costing Method and Cost Group Accounting -- mtl_secondary_inventories msub, (-- Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, msub.material_overhead_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method = 1 -- Standard Costing union all -- Not Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, mp.material_overhead_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method <> 1 -- not Standard Costing union all -- With Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, ccga.material_overhead_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and ccga.cost_group_id = msub.default_cost_group_id union all -- Cost Group Accounting but the Subinventory Cost Group Id is null select msub.organization_id, msub.secondary_inventory_name, ccga.material_overhead_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and msub.default_cost_group_id is null and ccga.cost_group_id = mp.default_cost_group_id ) msub, -- End for revision 1.4 mtl_parameters mp, -- Revision for version 1.8 mfg_lookups ml1, mfg_lookups ml2, -- Revision for version 1.9 mfg_lookups ml3, -- Account Type gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_user fu1, fnd_user fu2 where msub.material_overhead_account = gcc.code_combination_id (+) and msub.organization_id = mp.organization_id -- Revision for version 1.8 and ml1.lookup_type = 'SYS_YES_NO' and ml1.lookup_code = msub.asset_inventory and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = msub.quantity_tracked -- Revision for version 1.9 and ml3.lookup_type = 'CST_COST_CODE_TYPE' and ml3.lookup_code = 2 -- Material Overhead -- =========================================== -- 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 -- Revision for version 1.4 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- Revision for version 1.6 and fu1.user_id = msub.created_by and fu2.user_id = msub.last_updated_by -- Revision for version 1.1, 1.3 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 1=1 -- p_org_code, p_operating_unit, p_ledger union all -- Get the Resource Account select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, -- Revision for version 1.4 haou.name Organization_Name, -- Revision for version 1.4 (select ccg.cost_group from cst_cost_groups ccg where ccg.cost_group_id = msub.default_cost_group_id) Cost_Group, -- End revision for version 1.4 msub.secondary_inventory_name Subinventory, -- Revision for version 1.8 ml1.meaning Inventory_Asset, ml2.meaning Quantity_Tracked, -- Revision for version 1.9 -- 'Resource Account' Account_Type, ml3.meaning Account_Type, &segment_columns -- Revision for version 1.4 -- Revision for version 1.3 -- decode(msub.attribute1, 'Y', 'Yes', 'No') Use Item_Type Accts, -- Revision for version 1.6 msub.creation_date Creation_Date, msub.disable_date Disable_Date, fu1.user_name Created_By, fu2.user_name Last_Updated_By -- End revision for version 1.6 from -- Revision for version 1.4 -- Get valuation accounts based on Costing Method and Cost Group Accounting -- mtl_secondary_inventories msub, (-- Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, msub.resource_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method = 1 -- Standard Costing union all -- Not Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, mp.resource_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method <> 1 -- not Standard Costing union all -- With Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, ccga.resource_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and ccga.cost_group_id = msub.default_cost_group_id union all -- Cost Group Accounting but the Subinventory Cost Group Id is null select msub.organization_id, msub.secondary_inventory_name, ccga.resource_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and msub.default_cost_group_id is null and ccga.cost_group_id = mp.default_cost_group_id ) msub, -- End for revision 1.4 mtl_parameters mp, -- Revision for version 1.8 mfg_lookups ml1, mfg_lookups ml2, -- Revision for version 1.9 mfg_lookups ml3, -- Account Type gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_user fu1, fnd_user fu2 where msub.resource_account = gcc.code_combination_id (+) and msub.organization_id = mp.organization_id -- Revision for version 1.8 and ml1.lookup_type = 'SYS_YES_NO' and ml1.lookup_code = msub.asset_inventory and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = msub.quantity_tracked -- Revision for version 1.9 and ml3.lookup_type = 'CST_COST_CODE_TYPE' and ml3.lookup_code = 3 -- Resource -- =========================================== -- 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 -- Revision for version 1.4 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- Revision for version 1.6 and fu1.user_id = msub.created_by and fu2.user_id = msub.last_updated_by -- Revision for version 1.1, 1.3 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 1=1 -- p_org_code, p_operating_unit, p_ledger union all -- Get the Outside Processing Account select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, -- Revision for version 1.4 haou.name Organization_Name, -- Revision for version 1.4 (select ccg.cost_group from cst_cost_groups ccg where ccg.cost_group_id = msub.default_cost_group_id) Cost_Group, -- End revision for version 1.4 msub.secondary_inventory_name Subinventory, -- Revision for version 1.8 ml1.meaning Inventory_Asset, ml2.meaning Quantity_Tracked, -- Revision for version 1.9 -- 'OSP Account' Account_Type, ml3.meaning Account_Type, &segment_columns -- Revision for version 1.4 -- Revision for version 1.3 -- decode(msub.attribute1, 'Y', 'Yes', 'No') Use Item_Type Accts, -- Revision for version 1.6 msub.creation_date Creation_Date, msub.disable_date Disable_Date, fu1.user_name Created_By, fu2.user_name Last_Updated_By -- End revision for version 1.6 from -- Revision for version 1.4 -- Get valuation accounts based on Costing Method and Cost Group Accounting -- mtl_secondary_inventories msub, (-- Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, msub.outside_processing_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method = 1 -- Standard Costing union all -- Not Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, mp.outside_processing_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method <> 1 -- not Standard Costing union all -- With Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, ccga.outside_processing_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and ccga.cost_group_id = msub.default_cost_group_id union all -- Cost Group Accounting but the Subinventory Cost Group Id is null select msub.organization_id, msub.secondary_inventory_name, ccga.outside_processing_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, mtl_parameters mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and msub.default_cost_group_id is null and ccga.cost_group_id = mp.default_cost_group_id ) msub, -- End for revision 1.4 mtl_parameters mp, -- Revision for version 1.8 mfg_lookups ml1, mfg_lookups ml2, -- Revision for version 1.9 mfg_lookups ml3, -- Account Type gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_user fu1, fnd_user fu2 where msub.outside_processing_account = gcc.code_combination_id (+) and msub.organization_id = mp.organization_id -- Revision for version 1.8 and ml1.lookup_type = 'SYS_YES_NO' and ml1.lookup_code = msub.asset_inventory and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = msub.quantity_tracked -- Revision for version 1.9 and ml3.lookup_type = 'CST_COST_CODE_TYPE' and ml3.lookup_code = 4 -- Outside Processing -- =========================================== -- 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 -- Revision for version 1.4 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- Revision for version 1.6 and fu1.user_id = msub.created_by and fu2.user_id = msub.last_updated_by -- Revision for version 1.1, 1.3 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 1=1 -- p_org_code, p_operating_unit, p_ledger union all -- Get the Overhead Account select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, -- Revision for version 1.4 haou.name Organization_Name, -- Revision for version 1.4 (select ccg.cost_group from cst_cost_groups ccg where ccg.cost_group_id = msub.default_cost_group_id) Cost_Group, -- End revision for version 1.4 msub.secondary_inventory_name Subinventory, -- Revision for version 1.8 ml1.meaning Inventory_Asset, ml2.meaning Quantity_Tracked, -- Revision for version 1.9 -- 'Overhead Account' Account_Type, ml3.meaning Account_Type, &segment_columns -- Revision for version 1.6 msub.creation_date Creation_Date, msub.disable_date Disable_Date, fu1.user_name Created_By, fu2.user_name Last_Updated_By -- End revision for version 1.6 from -- Revision for version 1.4 -- Get valuation accounts based on Costing Method and Cost Group Accounting -- mtl_secondary_inventories msub, (-- Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, msub.overhead_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method = 1 -- Standard Costing union all -- Not Standard Costing, no Cost Group Accounting select msub.organization_id, msub.secondary_inventory_name, mp.overhead_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id, -- Revision for version 1.6 msub.creation_date, msub.disable_date, msub.created_by, msub.last_updated_by -- End revision for version 1.6 from mtl_secondary_inventories msub, mtl_parameters mp where |