select DISTINCT
     msi.segment1 成品编码
    ,msi.description 成品描述
    ,msi.item_type 物料类型
    ,msi.inventory_item_status_code 状态
    ,msi.build_in_wip_flag 允许制造
from
    INV.MTL_SYSTEM_ITEMS_B msi,
    apps.BOM_BILL_OF_MATERIALS bom,
    apps.BOM_INVENTORY_COMPONENTS bic
where
         msi.organization_id=X
   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.DISABLE_DATE is null
--and msi.build_in_wip_flag =‘N‘   --可制造
   and msi.inventory_item_status_code=‘New‘
order by msi.segment1