BOM Models

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