ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 92C63F4C20A21E85E053BB6B63585CC8 CST Cost Type 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 or cct.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) ) order by cct.cost_type US 8E2FF36EDF3A79D2E0530100007F1FF2 GL Ledger (inv org related) Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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, gl.name AR جميع دفاتر الأستاذ المتعلقة بجميع منظمات المخزون النشطة D Alle Ledger, die sich auf alle aktiven Inventarorganisationen beziehen E Todos los libros de contabilidad relacionados con todas las organizaciones de inventario activas F Tous les grands livres relatifs à tous les organismes d'inventaire actifs I Tutti i libri contabili relativi a tutte le organizzazioni di inventario attive JA すべてのアクティブな在庫組織に関連するすべての台帳 KO 모든 활성 재고 조직과 관련된 모든 원장 PTB Todos os livros contábeis relacionados a todas as organizações de inventário ativas RU Все бухгалтерские книги, относящиеся ко всем организациям, занимающимся активным инвентаризацией. S Alla huvudböcker relaterade till alla aktiva lagerorganisationer TR Tüm aktif envanter organizasyonlarıyla ilgili tüm defterler US All ledgers related to all active inventory organizations ZHS 与所有现行清单组织有关的所有分类账 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set 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 US EC5CECA520510FC8E0530100007F5EB3 INV Item Number (costing enabled only items) 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 9B9627743E84607DE053BB6B635805FB INV Operating Unit Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where 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)>=sysdate ) order by 1 AR جميع وحدات التشغيل المرتبطة بمؤسسة مخزون غير رئيسية نشطة D Alle Betriebseinheiten, die mit einer aktiven Nicht-Master-Inventarisierungsorganisation verbunden sind E Todas las unidades operativas relacionadas con una organización de inventario no maestra activa F Toutes les unités opérationnelles liées à un organisme d'inventaire non maître actif I Tutte le unità operative legate a un'organizzazione di inventario non master attiva JA アクティブな非マスターインベントリ組織に関連するすべてのオペレーティング・ユニット KO 활성 비 마스터 재고 조직과 관련된 모든 운영 단위 PTB Todas as unidades operacionais relacionadas a uma organização ativa de inventário não-mestre RU Все операционные единицы, относящиеся к действующей организации, не являющейся ведущим поставщиком инвентарных запасов. S Alla operativa enheter relaterade till en aktiv organisation som inte är huvudlager TR Etkin bir ana envanter organizasyonu ile ilgili tüm işletim birimleri US All operating units related to an active non master inventory organization ZHS 与在用的非总清单组织有关的所有业务单位： 91D3167A33531204E053BB6B63585EE3 INV Organization Code (excluding master) 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 AR جميع رموز تنظيم المخزون النشط باستثناء المؤسسات الرئيسية D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master JA マスター組織を除くすべてのアクティブなインベントリ組織コード KO 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre RU Все действующие инвентаризационные коды организаций, за исключением основных организаций S Alla aktiva organisationskoder för inventarier exklusive huvudorganisationer TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları US All active inventory organization codes excluding master organizations ZHS 所有现行清单组织代码，不包括主组织 92EDCA82621F0FBDE053BB6B6358DEED MRP Assignment Set Y select mas.assignment_set_name value, mas.description from mrp_assignment_sets mas order by mas.assignment_set_name US C1B7B7403A5D7839E053BB6B6358045B with rept as (select mp.organization_code, cct.cost_type, cct.cost_type_id, msiv.concatenated_segments, msiv.description, muomv.uom_code, msiv.item_type, misv.inventory_item_status_code_tl, msiv.planning_make_buy_code, msiv.inventory_item_id, msiv.organization_id, -- check to see if a bom exists -- Revision for version 1.38, fix ORA-43916 -- nvl((select distinct 'Y' nvl((select distinct mp.organization_code from bom_structures_b bom where bom.organization_id = mp.organization_id and bom.assembly_item_id = msiv.inventory_item_id and bom.alternate_bom_designator is null -- Revision for version 1.38, fix ORA-43916 -- ), 'N') bom, ), null) bom, -- check to see if a routing exists -- Revision for version 1.38, fix ORA-43916 -- nvl((select distinct 'Y' nvl((select distinct mp.organization_code from bom_operational_routings bor where bor.organization_id = mp.organization_id and bor.assembly_item_id = msiv.inventory_item_id and bor.alternate_routing_designator is null -- Revision for version 1.38, fix ORA-43916 -- ), 'N') routing, ), null) routing, -- check to see if a sourcing rule exists for the receipt org -- Revision for version 1.38, fix ORA-43916 -- nvl((select distinct 'Y' nvl((select distinct mp.organization_code from mrp_sr_receipt_org msro, mrp_sr_source_org msso, mrp_sourcing_rules msr, mrp_sr_assignments msa, mrp_assignment_sets mas where msr.sourcing_rule_id = msro.sourcing_rule_id -- fix for version 1.4, check to see if the sourcing rule is -- for an inventory org, not a vendor and msso.sr_receipt_id = msro.sr_receipt_id and msso.source_organization_id is not null and msa.sourcing_rule_id = msr.sourcing_rule_id and msa.assignment_set_id = mas.assignment_set_id and msiv.organization_id = msa.organization_id and msiv.inventory_item_id = msa.inventory_item_id and mp.organization_id = msa.organization_id and 3=3 -- p_assignment_set -- Revision for version 1.38, fix ORA-43916 -- ), 'N') sourcing_rule, ), null) sourcing_rule, cic.based_on_rollup_flag, -- Revision for version 1.35 cic.defaulted_flag, msiv.costing_enabled_flag, msiv.inventory_asset_flag, to_char(cic.inventory_asset_flag) cost_asset_flag, msiv.std_lot_size, cic.lot_size cost_lot_size, cic.item_cost, -- Revision for version 1.36 onhand.quantity Onhand_Quantity, cic.creation_date cost_creation_date, -- End revision for version 1.36 msiv.creation_date item_creation_date from mtl_system_items_vl msiv, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, cst_item_costs cic, cst_cost_types cct, mtl_parameters mp, -- Revision for version 1.36 (select moqd.organization_id, moqd.inventory_item_id, sum(moqd.transaction_quantity) quantity from mtl_onhand_quantities_detail moqd, mtl_parameters mp where moqd.is_consigned = 2 -- No and mp.organization_id = moqd.organization_id and 4=4 -- p_org_code group by moqd.organization_id, moqd.inventory_item_id ) onhand -- End revision for version 1.36 -- =================================================================== -- Cost type, organization, item master and report specific controls -- =================================================================== where cic.cost_type_id = cct.cost_type_id and mp.organization_id = cic.organization_id and msiv.organization_id = cic.organization_id and msiv.inventory_item_id = cic.inventory_item_id and msiv.primary_uom_code = muomv.uom_code and misv.inventory_item_status_code = msiv.inventory_item_status_code and msiv.inventory_item_status_code <> 'Inactive' -- Revision for version 1.36 and onhand.inventory_item_id (+) = msiv.inventory_item_id and onhand.organization_id (+) = msiv.organization_id -- End revision for version 1.36 -- Avoid unused inventory organizations and mp.organization_id <> mp.master_organization_id -- the item master org usually does not have costs 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 2=2 -- p_cost_type, p_item_number and 4=4 -- p_org_code ) --------------- main sql starts here ----------------------- select rept_all.report_type, nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, rept_all.organization_code Org_Code, rept_all.cost_type Cost_Type, rept_all.concatenated_segments Item_Number, rept_all.description Item_Description, rept_all.uom_code UOM_Code, fcl.meaning Item_Type, rept_all.inventory_item_status_code_tl Item_Status, ml1.meaning Make_Buy_Code, &category_columns fl1.meaning BOM, fl2.meaning Routing, fl3.meaning Sourcing_Rule, ml2.meaning Based_on_Rollup, -- Revision for version 1.35 ml4.meaning Defaulted_Flag, fl4.meaning Costing_Enabled, fl5.meaning Item_Inventory_Asset, ml3.meaning Cost_Inventory_Asset, rept_all.std_lot_size Item_Std_Lot_Size, rept_all.cost_lot_size Cost_Lot_Size, gl.currency_code Currency_Code, rept_all.Item_Cost, -- Revision for version 1.36 rept_all.Onhand_Quantity, rept_all.Cost_Creation_Date, rept_all.item_creation_date Item_Creation_Date -- End revision for version 1.36 from mfg_lookups ml1, -- Make/buy code, MTL_PLANNING_MAKE_BUY mfg_lookups ml2, -- based on rollup, CST_BONROLLUP_VAL mfg_lookups ml3, -- Cost inventory_asset_flag, SYS_YES_NO -- Revision for version 1.35 mfg_lookups ml4, -- Cost defaulted_flag, SYS_YES_NO fnd_lookups fl1, -- BOM, YES_NO fnd_lookups fl2, -- Routing, YES_NO fnd_lookups fl3, -- Sourcing_Rule, YES_NO fnd_lookups fl4, -- Item costing enabled, YES_NO fnd_lookups fl5, -- Item inventory asset, 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, ( -- =============================================== -- Report 1 - 'Based on Rollup Yes - No BOMs' -- =============================================== select 'Based on Rollup Yes - No BOMs' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 1 and rept.bom = 'N' union all -- =============================================== -- Report 2 - 'Based on Rollup Yes - No Routing' -- =============================================== select 'Based on Rollup Yes - No Routing' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 1 and rept.routing = 'N' union all -- =============================================== -- Report 3 - 'Based on Rollup Yes - No Rollup' -- =============================================== select 'Based on Rollup Yes - No Rollup' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and (rept.planning_make_buy_code = 1 or (rept.planning_make_buy_code = 2 and rept.sourcing_rule = 'Y')) and rept.based_on_rollup_flag = 1 and not exists (select 'x' from cst_item_cost_details cicd where cicd.organization_id = rept.organization_id and cicd.inventory_item_id = rept.inventory_item_id and cicd.cost_type_id = rept.cost_type_id and cicd.rollup_source_type = 3 -- rolled up ) union all -- =============================================== -- Report 4 - 'Based on Rollup Yes - Buy Items' -- =============================================== select 'Based on Rollup Yes - Buy Items' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 2 and rept.based_on_rollup_flag = 1 and rept.sourcing_rule = 'N' union all -- =============================================== -- Report 5 - 'Based on Rollup No - With BOMs' -- =============================================== select 'Based on Rollup No - With BOMs' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 2 and (rept.bom = 'Y' or rept.routing = 'Y') union all -- =============================================== -- Report 6 - 'Based on Rollup No - With Sourcing Rules' -- =============================================== select 'Based on Rollup No - With Sourcing Rules' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.based_on_rollup_flag = 2 and rept.sourcing_rule = 'Y' union all -- =============================================== -- Report 7 - 'Based on Rollup No - Make Items' -- =============================================== select 'Based on Rollup No - Make Items' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.planning_make_buy_code = 1 and rept.based_on_rollup_flag = 2 union all -- =============================================== -- Report 8 - 'Lot-Based Resources With Lot Size 1' -- =============================================== select 'Lot-Based Resources With Lot Size 1' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and nvl(rept.cost_lot_size,1) = 1 and rept.planning_make_buy_code = 1 and exists -- check to see if there are material or resource charges based on Lot (select 'x' from cst_item_cost_details cicd where cicd.organization_id = rept.organization_id and cicd.inventory_item_id = rept.inventory_item_id and cicd.cost_type_id = rept.cost_type_id and cicd.basis_type = 2 -- Lot ) union all -- =============================================== -- Report 9 - BOMs With No Components -- =============================================== select 'BOMs With No Components' report_type, rept.* from rept where rept.costing_enabled_flag = 'Y' and rept.based_on_rollup_flag = 1 and rept.bom = 'Y' and not exists -- check to see if a BOM exists with components (select 'x' from bom_structures_b bom, bom_components_b comp where bom.organization_id = rept.organization_id and bom.assembly_item_id = rept.inventory_item_id and bom.bill_sequence_id = comp.bill_sequence_id and comp.effectivity_date <= sysdate and nvl(comp.disable_date, sysdate+1) > sysdate ) union all -- ========================================== -- Report 10 - Item Costing vs. Item Asset Controls -- ========================================== -- Costing_Enabled <> Item_Inventory_Asset select 'Item Costing vs. Item Asset Controls' report_type, rept.* from rept where rept.costing_enabled_flag <> rept.inventory_asset_flag union all -- =============================================== -- Report 11 - Item Asset vs. Costing Asset Controls -- =============================================== -- Item_Inventory_Asset <> Cost Inventory_Asset select 'Item Asset vs. Costing Asset Controls' report_type, rept.* from rept where rept.inventory_asset_flag <> decode(rept.cost_asset_flag, 1, 'Y', 'N') -- Revision for version 1.35 union all -- =============================================== -- Report 12 - Based on Rollup No - Defaulted Costs -- =============================================== select 'Based on Rollup No - Defaulted Costs' report_type, rept.* from rept where rept.defaulted_flag = 1 and rept.based_on_rollup_flag = 2 -- End revision for version 1.35 ) rept_all -- =================================================================== -- Joins for the lookup codes -- =================================================================== where ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = rept_all.planning_make_buy_code and ml2.lookup_type = 'CST_BONROLLUP_VAL' and ml2.lookup_code = rept_all.based_on_rollup_flag and ml3.lookup_type = 'SYS_YES_NO' and ml3.lookup_code = rept_all.cost_asset_flag -- Revision for version 1.35 and ml4.lookup_type = 'SYS_YES_NO' and ml4.lookup_code = rept_all.defaulted_flag -- End revision for version 1.35 -- Revision for version 1.38 and fl1.lookup_type = 'YES_NO' and fl1.lookup_code = decode(rept_all.bom, rept_all.organization_code, 'Y', 'N') and fl2.lookup_type = 'YES_NO' and fl2.lookup_code = decode(rept_all.routing, rept_all.organization_code, 'Y', 'N') and fl3.lookup_type = 'YES_NO' and fl3.lookup_code = decode(rept_all.sourcing_rule, rept_all.organization_code, 'Y', 'N') -- End revision for version 1.38 and fl4.lookup_type = 'YES_NO' and fl4.lookup_code = rept_all.costing_enabled_flag and fl5.lookup_type = 'YES_NO' and fl5.lookup_code = rept_all.inventory_asset_flag and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = rept_all.item_type -- =================================================================== -- using the base tables to avoid hr views -- =================================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = rept_all.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and hoi.org_information1 = gl.ledger_id -- this gets the ledger id -- Revision for version 1.37 -- Revision for Operating Unit and Ledger Controls and Parameters, replacing prior method. and (nvl(fnd_profile.value('XXEN_REPORT_USE_LEDGER_SECURITY'),'N')='N' or 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 (nvl(fnd_profile.value('XXEN_REPORT_USE_OPERATING_UNIT_SECURITY'),'N')='N' or 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.37 and 1=1 -- p_operating_unit, p_ledger -- Order by Report Type, Ledger, Operating_Unit, Org_Code, Cost_Type, Item_Number order by rept_all.report_type, nvl(gl.short_name, gl.name), haou2.name, rept_all.organization_code, rept_all.cost_type, rept_all.concatenated_segments 1.38 13 Apr 2025 Douglas Volz Second fix ORA-43916 Collation Error for character expressions 'Y', 'N'. Y US CAC Cost vs. Planning Item Controls Compare item make/buy controls vs. costing based on rollup controls, to find errors with your cost rollup results. There are twelve included reports, see below description for more information. Available Reports: 1. Based on Rollup Yes - No BOMS Find make items where the item is set to be rolled up but there are no BOMs. May roll up to a zero cost. 2. Based on Rollup Yes - No Routing Find make items costs are based on the cost rollup, but there are no routings. 3. Based on Rollup Yes - No Rollup Find make items where it is set to be rolled up but there are no rolled up costs 4. Based on Rollup Yes - Buy Items Find buy items where the item is set to rolled up 5. Based on Rollup No - With BOMS Find make items where the item is not set to be rolled up but BOMS or routings exist. 6. Based on Rollup No - With Sourcing Rules Find buy items where costs are not based on the cost rollup, but sourcing rules exist. 7. Based on Rollup No - Make Items Find make items where the item is not set to rolled up, whether or not BOMs or routings exist. 8. Lot-Based Resources With Lot Size One Find make items where there are charges based on Lot but the lot size is one. Duplicates the setup charges for each item you make. 9. BOMs With No Components Find make items with BOMS that have no components. 10. Item Costing vs. Item Asset Controls Find items where the item master costed flag (costed enabled) and the item asset flag do not match. 11. Item Asset vs. Costing Asset Controls Find items where the item master asset and the costing asset flags do not match. 12. Based on Rollup No - Defaulted Costs Find items where the item is not rolled up but the defaulted flag says Yes Parameters: =========== Cost Type: the Frozen or Pending cost type you wish to report (mandatory). Assignment Set: for your organization sourcing rules, enter an assignment set (optional). Category Sets 1 - 3: any item category you wish, typically the Cost or Product Line category sets (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 2008-2025 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. -- +=============================================================================+ -- | 1.0 15 Oct 2008 Douglas Volz Initial Coding -- | 1.34 06 May 2021 Douglas Volz Using a with statement, summarized report type queries for efficiency. -- | 1.35 23 Apr 2022 Douglas Volz Add new column "Defaulted Costs" and new report type "Based on -- | Rollup No - Defaulted Costs". The defaulted flag indicates -- | whether the cost of the item is defaulted from the default cost -- | type during cost rollup. -- | 1.36 07 Jan 2024 Douglas Volz Add current onhand quantities, to help find valuation issues. Remove -- | tabs, add operating unit and ledger security and inventory access controls. -- | 1.37 10 Apr 2025 Douglas Volz Added in new GL and OU security profiles. -- | 1.38 13 Apr 2025 Douglas Volz Fix ORA-43916 Collation Error for character expressions 'Y', 'N'. -- +=============================================================================+*/ Enginatics R12 only &category_columns 1=1 2=2 3=3 4=4 1 10 2=2 cct.cost_type = :p_cost_type LOV CST Cost Type 92C63F4C20A21E85E053BB6B63585CC8 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 or cct.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) ) order by cct.cost_type Y US Cost Type 2 20 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'rept_all') 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 3 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'rept_all') 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 4 40 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'rept_all') 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 5 50 3=3 mas.assignment_set_name = :p_assignment_set LOV MRP Assignment Set 92EDCA82621F0FBDE053BB6B6358DEED Y select mas.assignment_set_name value, mas.description from mrp_assignment_sets mas order by mas.assignment_set_name US Assignment Set 6 60 2=2 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 4=4 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 INV Operating Unit 9B9627743E84607DE053BB6B635805FB Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where 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)>=sysdate ) order by 1 US Operating Unit 9 90 1=1 gl.name = :p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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, gl.name US Ledger :$flex$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Category Set 3 :$flex$.organization_code Organization Code Item Number 3B553ECA773852E56D3B7976734CA583 Count by Report, Org and Cost Type Y Count by Report Type, Ledger, OU, Org Code and Cost Type MFG 20 BASED_ON_ROLLUP 17 BOM 22 COSTING_ENABLED 30 COST_CREATION_DATE 24 COST_INVENTORY_ASSET 26 COST_LOT_SIZE 5 COST_TYPE 27 CURRENCY_CODE 15 Class 21 DEFAULTED_FLAG 14 Family 28 ITEM_COST 31 ITEM_CREATION_DATE 7 ITEM_DESCRIPTION 23 ITEM_INVENTORY_ASSET 6 ITEM_NUMBER 10 ITEM_STATUS 25 ITEM_STD_LOT_SIZE 9 ITEM_TYPE 16 Inv.Items Description 2 LEDGER 11 MAKE_BUY_CODE 29 ONHAND_QUANTITY 3 OPERATING_UNIT 4 ORG_CODE 12 Product Cat 13 Product Description 1 REPORT_TYPE 18 ROUTING 19 SOURCING_RULE 8 UOM_CODE COST_TYPE ROW 5 ITEM_NUMBER VALUE 1 COUNT LEDGER ROW 2 OPERATING_UNIT ROW 3 ORG_CODE ROW 4 REPORT_TYPE ROW 1 S Site 3B553ECA773852E56D3B7976734CA583