ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A473301D91B23851E053B46B6358233AY SELECT mtlsi_model.segment1 MODEL , mtlsi.segment1 BOM_LIST , mtlsi.description , bome.item_num , decode(bome.bom_item_type, 1, 'MODEL', 2, 'OPTION CLASS', 3, 'NA', 4, 'OPTION') BOM_ITEM_TYPE , mtlsi.inventory_item_status_code , bome.primary_unit_of_measure , bome.component_quantity , bome.ATP_flag , bome.shippable_item_flag , bome.internal_order_flag , bome.explosion_type , bome.effectivity_date FROM apps.mtl_system_items_b mtlsi_model , apps.bom_bill_of_materials bombom , apps.bom_explosions bome , apps.mtl_system_items_b mtlsi , apps.mtl_parameters mtlp WHERE 1=1 AND mtlsi_model.inventory_item_id = bombom.assembly_item_id AND bombom.bill_sequence_id = bome.top_bill_sequence_id AND bome.component_item_id = mtlsi.inventory_item_id AND mtlsi_model.organization_id = mtlsi.organization_id AND mtlsi.organization_id = bome.organization_id AND bome.organization_id = mtlp.organization_id AND trunc(SYSDATE) BETWEEN nvl(bome.effectivity_date, trunc(SYSDATE)) AND nvl(bome.disable_date, trunc(SYSDATE)) AND mtlsi_model.bom_item_type = 1 -- AND mtlp.organization_code = 'M1' -- AND mtlsi_model.segment1 = '&model_number' ORDER BY bome.top_bill_sequence_id , bome.sort_order , bome.item_num US BOM Models Master data report showing the exploded Bill of Materials Draft &model_number 1=1 1 10 1=1 mtlp.organization_code=:organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y 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 mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) 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 mtlp.organization_code from mtl_parameters mtlp where mtlp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Organization Code 2 20 1=1 mtlsi_model.segment1=:segment1 LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Model