ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 09737AE530CF0E64E0630100007F4174Y select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, cct.cost_type, msiv.concatenated_segments item_number, msiv.description item_description, msiv.primary_uom_code uom_code, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, misv.inventory_item_status_code_tl item_status_code, xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code, msiv.std_lot_size, &category_columns xxen_util.yes(msiv.costing_enabled_flag) allow_costs, xxen_util.yes(decode(cic.inventory_asset_flag,1,'Y')) inventory_asset, xxen_util.yes(decode(cic.defaulted_flag,1,'Y')) use_default_controls, xxen_util.meaning(cic.based_on_rollup_flag,'CST_BONROLLUP_VAL',700) based_on_rollup, cic.lot_size costing_lot_size, cic.shrinkage_rate, gl.currency_code, cic.item_cost from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, mtl_system_items_vl msiv, mtl_item_status_vl misv, cst_item_costs cic, cst_cost_types cct where 1=1 and mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate+1)>sysdate and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=msiv.organization_id and msiv.inventory_item_status_code=misv.inventory_item_status_code and msiv.organization_id=cic.organization_id and msiv.inventory_item_id=cic.inventory_item_id and cic.based_on_rollup_flag=1 and cic.cost_type_id=cct.cost_type_id order by ledger, operating_unit, org_code, cost_type, item_number Rebuilt to the Blitz Report SQL standards. Allow Costs, Inventory Asset and Use Default Controls show Yes or blank. Setup report without organization security, so that every user sees the setup of all organizations. #,##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. -- | 1.2 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &category_columns 1=1 1 10 1=1 cct.cost_type=:p_cost_type LOV CST Cost Type (No AvgRates No PII) A486BD05719A2A82E053BB6B6358FC8E 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 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 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 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 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 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') ) ) 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 (all) 8E2FF36EDEA279D2E0530100007F1FF2 select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.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 and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) 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 haouv_ou.name=:p_operating_unit LOV HR Operating Unit (all) 8E2FF36EDE8879D2E0530100007F1FF2 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')) order by hou.name US Operating Unit 9 90 1=1 gl.name=:p_ledger LOV GL Ledger (all) A95C471001B20DD1E0530100007FBF83 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$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and gl.object_type_code='L' 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,'NONE',1,'PRIMARY',2,'SECONDARY',3,'ALC',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