Oracle Ebs-sql (BOM-13): Checks for item classes that do not have a defined inventory score. SQL

Select DISTINCT MSI.SEGMENT1 encoding,

Msi.description description,

Msi.primary_unit_of_measure units,

Msi.inventory_item_status_code State,

flv. Meaning item type,

Catb1.segment1 Large inventory category,

Catb1.segment2 Inventory Small category,

Msi.creation_date creation Time

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 '% tool spare parts '

and decode (Catb1.segment1, ', ' 0 ', catb1.segment1) = ' 0 '

ORDER BY Msi.segment1