ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 09737AE530CF0E64E0630100007F4174Y 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, muomv.uom_code UOM_Code, fcl.meaning Item_Type, misv.inventory_item_status_code_tl Item_Status_Code, ml1.meaning Make_Buy_Code, msiv.std_lot_size Std_Lot_Size, &category_columns fl1.meaning Allow_Costs, ml2.meaning Inventory_Asset, ml3.meaning Use_Default_Controls, ml4.meaning Based_on_Rollup, cic.lot_size Costing_Lot_Size, cic.shrinkage_rate Shrinkage_Rate, gl.currency_code Currency_Code, cic.item_cost Item_Cost from cst_item_costs cic, cst_cost_types cct, mtl_parameters mp, mtl_system_items_vl msiv, mtl_item_status_vl misv, mtl_units_of_measure_vl muomv, mfg_lookups ml1, -- planning make/buy code, MTL_PLANNING_MAKE_BUY mfg_lookups ml2, -- inventory_asset_flag, SYS_YES_NO mfg_lookups ml3, -- defaulted_flag, SYS_YES_NO mfg_lookups ml4, -- based on rollup, CST_BONROLLUP_VAL fnd_lookups fl1, -- allow costs, YES_NO fnd_common_lookups fcl, -- Item Type hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl where cic.cost_type_id = cct.cost_type_id and cic.organization_id = msiv.organization_id and cic.inventory_item_id = msiv.inventory_item_id and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code and mp.organization_id = msiv.organization_id -- =================================================================== -- Don't report the unused or disabled inventory organizations -- =================================================================== and mp.organization_id <> mp.master_organization_id -- remove the global master org and sysdate < nvl(haou.date_to, sysdate + 1) -- =================================================================== -- Find items to be rolled up -- =================================================================== and cic.based_on_rollup_flag = 1 -- Yes -- =================================================================== -- 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 = 'SYS_YES_NO' and ml3.lookup_code = cic.defaulted_flag and ml4.lookup_type = 'CST_BONROLLUP_VAL' and ml4.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 -- Revision for version 1.1 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.1 and 1=1 -- p_inactive_item_status, p_cost_type, p_item_number, p_org_code, p_operating_unit, p_ledger -- =================================================================== -- Order by Ledger, Operating Unit, Org Code, Cost Type and Item Number order by 1,2,3,4,5 #,##0.00000;[Red](#,##0.00000) US CAC Inactive Items Set to Roll Up Report to show items which are set to roll up even though these items have an inactive status. The Cost Rollup uses the Based on Rollup Flag to control if an item is rolled up, as opposed to the item status. Parameters: =========== Cost Type: enter the cost type to report (mandatory). Defaults to your Costing Method cost type. Inactive Item Status: enter the item statuses which should not be rolled up (mandatory). Defaults to 'Inactive'. 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). 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 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 (doug@volzconsulting.com) -- | -- | Program Name: xxx_inactive_items_set_to_rollup_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 15 Nov 2023 Douglas Volz Initial version -- | 1.1 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. -- +=============================================================================+*/ Enginatics &category_columns 1=1 1 10 1=1 cct.cost_type = :p_cost_type LOV CST Cost Type (No AvgRates No PII) A486BD05719A2A82E053BB6B6358FC8E Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type select cct.cost_type value, cct.description from cst_cost_types cct, mtl_parameters mp where nvl(cct.disable_date,sysdate + 1) > sysdate and cct.organization_id is null and cct.cost_type_id = mp.primary_cost_method and (mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or (fnd_profile.value('MFG_ORGANIZATION_ID') is null and cct.cost_type_id = mp.primary_cost_method) ) and rownum < 2 group by cct.cost_type, cct.description order by cct.cost_type Y US Cost Type 2 20 1=1 misv.inventory_item_status_code_tl = :p_inactive_item_status LOV INV Item Status 8E2FF36EDE9C79D2E0530100007F1FF2 Y select misv.inventory_item_status_code_tl value, misv.description from mtl_item_status_vl misv order by misv.inventory_item_status_code_tl Inactive Y US Inactive Item Status 3 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 4 40 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 5 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 6 60 1=1 msiv.concatenated_segments = :p_item_number LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null 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 (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Item Number 7 70 1=1 mp.organization_code = :p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 8 80 1=1 haou2.name = :p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.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) order by hou.name US Operating Unit 9 90 1=1 gl.name = :p_ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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 gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger E401B58F0DFE1EF227D2C08AE9C6A1BE Count by Org by Cost Type Y MFG 17 ALLOW_COSTS 20 BASED_ON_ROLLUP -31.0034 COGS Account -31.0032 COGS Company -31.0033 COGS Department -31.0036 COGS Product -31.0035 COGS Sub-Account 21 COSTING_LOT_SIZE -31.0042 COST_CREATION_DATE 4 COST_TYPE 23 CURRENCY_CODE 15 Class 14 Family 18 INVENTORY_ASSET 24 ITEM_COST -31.0044 ITEM_CREATION_DATE 6 ITEM_DESCRIPTION -31.0045 ITEM_LAST_UPDATE_DATE 5 ITEM_NUMBER -8.0009 ITEM_STATUS 9 ITEM_STATUS_CODE 8 ITEM_TYPE 16 Inv.Items Description -31.0043 LAST_COST_UPDATE_DATE 1 LEDGER 10 MAKE_BUY_CODE -23.0024 MARKET_PRICE -23.0026 MATERIAL_COST -23.0027 MATERIAL_OVERHEAD_COST 2 OPERATING_UNIT 3 ORG_CODE -23.0029 OUTSIDE_PROCESSING_COST -23.003 OVERHEAD_COST 12 Product Cat 13 Product Description -23.0028 RESOURCE_COST -31.0039 Rev Account -31.0037 Rev Company -31.0038 Rev Department -31.0041 Rev Product -31.004 Rev Sub-Account 22 SHRINKAGE_RATE 11 STD_LOT_SIZE -23.0025 TARGET_OR_PO_LIST_PRICE 7 UOM_CODE 19 USE_DEFAULT_CONTROLS COST_TYPE ROW 4 ITEM_NUMBER VALUE 1 COUNT LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE ROW 3 S Site F0AA966438C54479747A83A99649A1B0 Count by Org by Item Y MFG 17 ALLOW_COSTS 20 BASED_ON_ROLLUP -31.0034 COGS Account -31.0032 COGS Company -31.0033 COGS Department -31.0036 COGS Product -31.0035 COGS Sub-Account 21 COSTING_LOT_SIZE -31.0042 COST_CREATION_DATE 4 COST_TYPE 23 CURRENCY_CODE 15 Class 14 Family 18 INVENTORY_ASSET 24 ITEM_COST -31.0044 ITEM_CREATION_DATE 6 ITEM_DESCRIPTION -31.0045 ITEM_LAST_UPDATE_DATE 5 ITEM_NUMBER -8.0009 ITEM_STATUS 9 ITEM_STATUS_CODE 8 ITEM_TYPE 16 Inv.Items Description -31.0043 LAST_COST_UPDATE_DATE 1 LEDGER 10 MAKE_BUY_CODE -23.0024 MARKET_PRICE -23.0026 MATERIAL_COST -23.0027 MATERIAL_OVERHEAD_COST 2 OPERATING_UNIT 3 ORG_CODE -23.0029 OUTSIDE_PROCESSING_COST -23.003 OVERHEAD_COST 12 Product Cat 13 Product Description -23.0028 RESOURCE_COST -31.0039 Rev Account -31.0037 Rev Company -31.0038 Rev Department -31.0041 Rev Product -31.004 Rev Sub-Account 22 SHRINKAGE_RATE 11 STD_LOT_SIZE -23.0025 TARGET_OR_PO_LIST_PRICE 7 UOM_CODE 19 USE_DEFAULT_CONTROLS COST_TYPE ROW 4 ITEM_NUMBER VALUE 1 COUNT LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE ROW 3 S Site E401B58F0DFE1EF227D2C08AE9C6A1BE