Query to find item Category in oracle apps r12

Query to find item Category in oracle apps r12

In this post, We will be discuss about the Query to find item Category in oracle apps r12. We do have item category in oracle apps inventory , to divide the items based on the Category. Category helps to bifurcate the Item in terms of use in oracle apps. This query will help to extract the Item categories attached to each item in oracle apps. We will get the complete item category information using this query. Please find below the detail sql query to find item Category in oracle apps r12.

Most Important Table used by Query to find item Category in oracle apps r12

1.mtl_system_items_b
2.mtl_categories_kfv
3.mtl_item_categories
4.mtl_category_sets_tl

Detail SQL Query to find item Category in oracle apps r12

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 CATEGORY
  from apps.mtl_system_items_b MSB,
       apps.mtl_categories_kfv MCK,
       apps.mtl_item_categories MIC,
        apps.mtl_category_sets_tl MCST
       where MIC.inventory_item_id = MSB.inventory_item_id
       and MIC.category_id = MCK.category_id
      -- and a.attribute20 = 'GNU'
      and MSB.organization_id =MIC.organization_id
     and MSB.organization_id = :P_INV_ORGANIZATION
     and MCST.category_set_id=MIC.category_set_id