BOM Models

Description
Categories: Draft
Columns: Model, Bom List, Description, Item Num, Bom Item Type, Inventory Item Status Code, Primary Unit Of Measure, Component Quantity, Atp Flag, Shippable Item Flag ...
Master data report showing the exploded Bill of Materials
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
Parameter NameSQL textValidation
Organization Code
mtlp.organization_code=:organization_code
LOV
Model
mtlsi_model.segment1=:segment1
LOV