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;