Query to fetch Level -1 BOM Details for an Inventory Item HOME » QUERY TO FETCH LEVEL -1 BOM DETAILS FOR AN INVENTORY ITEM

 Query to fetch Level -1 BOM Details for an Inventory Item HOME » QUERY TO FETCH LEVEL -1 BOM DETAILS FOR AN INVENTORY ITEM 

SELECT 

  msi.segment1 “Parent material coding”, 

  msi.inventory_item_id, 

  msi.description “Parent item description”, 

  msi1.segment1 “Sub item code”, 

  msi1.inventory_item_id sub_item_id, 

  msi1.description “Sub item description” 

FROM 

  mtl_system_items_b msi, 

  bom_bill_of_materials bom, 

  bom_inventory_components bic, 

  mtl_system_items_b msi1 

WHERE 

  1 = 1 

  AND msi.organization_id = : organization_id 

  AND msi.inventory_item_id = bom.assembly_item_id 

  AND msi.organization_id = bom.organization_id 

  AND bom.organization_id = : organization_id 

  AND bom.alternate_bom_designator IS NULL 

  AND bom.bill_sequence_id = bic.bill_sequence_id 

  AND bic.disable_date IS NULL 

  AND bic.component_item_id = msi1.inventory_item_id 

  AND msi1.organization_id = : organization_id 

  AND msi.segment1 =: item_number;