Here below is the detail sql query to find the item category value in oracle apps.
select MSB.segment1 ITEM_CODE,MSB.DESCRIPTION ITEM_DESCRIPTION,MCST.category_set_name,MCK.CONCATENATED_SEGMENTS CATEGORYfrom apps.mtl_system_items_b MSB,apps.mtl_categories_kfv MCK,apps.mtl_item_categories MIC,apps.mtl_category_sets_tl MCSTwhere MIC.inventory_item_id = MSB.inventory_item_idand MIC.category_id = MCK.category_idand MSB.organization_id =MIC.organization_idand MSB.organization_id = :P_INV_ORGANIZATIONand MCST.category_set_id=MIC.category_set_id