select distinct msi.segment1            编码 ,  

         msi.description                      描述  ,

         msi.primary_unit_of_measure 单位 ,

         msi.inventory_item_status_code 状态 ,

         flv.MEANING                    物料类型 ,

         catb1.segment1               库存大类 ,

         catb1.segment2               库存小类 ,

         msi.creation_date            创建时间

from inv.mtl_system_items_b msi,

        applsys.fnd_lookup_values flv,

        inv.mtl_categories_b catb1,

        inv.mtl_item_categories cat 1,

        apps.mfg_lookups  lkp

where msi.organization_id=X

   and flv.lookup_type=‘ITEM_TYPE‘ and lkp.LOOKUP_TYPE(+) =  ‘WIP_SUPPLY‘

   and lkp.LOOKUP_CODE(+) = msi.wip_supply_type

   and flv.language=‘ZHS‘

   and msi.item_type=flv.lookup_code

   and catb1.category_id(+) =  cat1.category_id 

   and msi.inventory_item_id=cat1.inventory_item_id(+)

   and cat1.category_set_id(+)=1 --and msi.item_type in (‘P‘,‘SA‘,‘FG‘)

   and msi.inventory_item_status_code not like ‘In%‘

--and msi.description not like ‘%工具备件%‘

   and decode(catb1.segment1,‘‘,‘0‘,catb1.segment1) =‘0‘

order by msi.segment1