Item Category Query

-- To Retrieve item category details -- 

SELECT distinct  msi.segment1 item_code, msi.description item_desc,
       mc.segment12 item_category, mc.CONCATENATED_SEGMENTS
  FROM mtl_item_categories mic,
       mtl_category_sets_tl mcst,
       mtl_category_sets_b mcs,
       --mfg_lookups ml,
       mtl_categories_b_kfv mc,
       mtl_system_items_b msi
 WHERE mic.category_set_id = mcs.category_set_id
   AND mcs.category_set_id = mcst.category_set_id
   AND mcst.LANGUAGE = USERENV ('LANG')
   AND mic.category_id = mc.category_id
   --AND mic.organization_id = 3
   --and upper(mc.concatenated_segments) like '%CAPITAL%'
   AND msi.organization_id = mic.organization_id
   AND msi.inventory_item_id = mic.inventory_item_id
   --and mcst.category_set_name='PURCHASING CATEGORY SET'
   AND ( mc.segment12 IN ('COMPONENT', 'NONE') OR mc.segment12 LIKE '%TOOL%')