Oracle Apps Knowledge Sharing

Query to fetch PO Item Category Details in Oracle Apps (MTL_CATEGORY_SETS_B, MTL_CATEGORIES)

                In this post, we have a given a query which helps you to fetch the item category details defined at Inventory. This helps us in understanding the table level links.  

Key Tables:

MTL_CATEGORY_SETS_TL

MTL_CATEGORY_SETS_B

MTL_CATEGORIES

FND_ID_FLEX_STRUCTURES_VL

Query:

SELECT fifv.id_flex_structure_name,
       fifv.id_flex_code,
       mcst.category_set_id,
       mcst.category_set_name,
       mcst.description category_set_description,
       mcs.validate_flag,
       mic.category_id,
       mic.disable_date,
       mic.web_status,
       mic.supplier_enabled_flag,
       mic.segment1 category_name,
       mic.enabled_flag,
       mic.description category_description    
  FROM mtl_category_sets_tl mcst
      ,mtl_category_sets_b  mcs
      ,mtl_categories       mic
      ,fnd_id_flex_structures_vl fifv
 WHERE 1 = 1
   --AND mcst.category_set_name = 'XX PO CATEGORY'
   AND mcst.category_set_id   = mcs.category_set_id
   AND mcst.language          = USERENV('LANG') 
   AND mcs.structure_id       = mic.structure_id
   AND fifv.id_flex_num       = mic.structure_id
   AND NVL(mic.disable_date,SYSDATE + 1) > SYSDATE;
 

Hope it helps!!