Saturday, August 15, 2020

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!!

1 Responses to “Query to fetch PO Item Category Details in Oracle Apps (MTL_CATEGORY_SETS_B, MTL_CATEGORIES)”

Anonymous said...
June 7, 2023 at 2:59 AM

Thanks


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.