Oracle Ebs-sql (BOM-12): BOM Query

Select
MSI.SEGMENT1 Assembly Parts Code
, Msi.description fitting Description
, Msi.item_type type
, Msi.planner_code Planner
, Msi1.segment1 part encoding
, Msi1.description part description
, msi1.primary_unit_of_measure units
--, NVL (BOM. Alternate_bom_designator, ' primary ') alternatives
, Bic. Item_num Serial Number
, bic.component_quantity quota
, Bic.component_yield_factor yield
, Decode (BIC. wip_supply_type,1, ' push ', 2, ' Assembly pull ', 3, ' Operation Pull ', 6, ' virtual parts ' BOM supply type
, Bic. Supply_subinventory BOM Supply Sub-Library
, Msi1.item_type type
, Msi1.wip_supply_type Item Supply type
, Msi1.wip_supply_subinventory Item Supply sub-Library
From
Apps. Mtl_system_items MSI,
Apps. Mtl_system_items Msi1,
Apps.bom_bill_of_materials BOM,--bom.bom_structures_b
Apps. Bom_inventory_components Bic--bom.bom_components_b
where
Msi.organization_id=x
--and msi.inventory_item_status_code = ' Active '
and bom.organization_id=x
and bom.assembly_item_id=msi.inventory_item_id
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.component_item_id=msi1.inventory_item_id
and msi1.organization_id=msi.organization_id
--and Msi.item_type in (' FG ', ' SA ', ' PH ')
and Msi.item_type in (' P ')
--and msi.item_type = ' PH '
--and msi1.wip_supply_type= ' 6 '
--and Msi1.item_type in (' SA ', ' MP ')
and Bic. Disable_date is null
--and msi.segment1 like ' a0000000% '--BOM
--and msi1.segment1 in (' B00000000001 ', ' B00000000002 ')--material use
ORDER BY Msi.segment1,

Bic. Item_num