Department Number, Category Number and Cost of item based on the Item ID
--------------------------------------------------------------------------------------------
The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 'Your Inventory Item ID'
and organization_id = 'Your Inventory Organization ID' and category_set_id = 1 and rownum = 1)
and cs.organization_id = 'Your Inventory Organization ID'
and cs.cost_type_id = 1
and cs.inventory_item_id = 'Your Inventory Item ID';
Example:-
-----------
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 122251
and organization_id = 22 and category_set_id = 1 and rownum = 1)
and cs.organization_id = 22
and cs.cost_type_id = 1
and cs.inventory_item_id = 122251;