select     msi.segment1,

             msi.description, 、

             msi.item_type,   

             msi.inventory_item_status_code 状态

from    inv.mtl_system_items_b   msi

where  msi.organization_id =  X

   and msi.item_type                 in (‘FG‘,‘SA‘)   

   and msi.inventory_item_status_code <> ‘Inactive‘

  and not exists ( select null from bom.bom_structures_b    bsb, 

                                                bom.bom_components_b    bcb               

                                       where bsb.organization_id   = msi.organization_id

                                          and bsb.assembly_item_id  = msi.inventory_item_id   

                                          and bsb.bill_sequence_id  = bcb.bill_sequence_id          

                                          and nvl(bcb.disable_date,sysdate+1)> sysdate           

                                          and rownum = 1)