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