Saturday, August 15, 2020

Query to Fetch PO Item Category and Oracle iProcurement Shopping Category Mapping Details (ICX_POR_CATEGORY_DATA_SOURCES, ICX_CAT_CATEGORIES_TL )

 

 

 

 

 

 

                In this post, we have a given a query which helps you to fetch the item category and shopping category mapping in Oracle iProcurement. This helps us in understanding the table level links as well.  

Key Tables:

MTL_CATEGORIES_KFV 

ICX_POR_CATEGORY_DATA_SOURCES

ICX_CAT_CATEGORIES_TL

Query:

SELECT purchcategories.concatenated_segments item_category_name, purchcategoriestl.description, purchcategories.supplier_enabled_flag, categories1.category_name mapped_shopping_category, categories1.type, purchasingcatmapping.external_source, purchasingcatmapping.external_source_key, purchasingcatmapping.rt_category_id FROM mtl_categories_kfv purchcategories ,icx.icx_por_category_data_sources purchasingcatmapping ,icx.icx_cat_categories_tl categories1 ,mtl_categories purchcategoriestl WHERE purchcategories.structure_id = 201 AND NVL(purchcategories.start_date_active,SYSDATE) <= SYSDATE AND SYSDATE < NVL(purchcategories.end_date_active,SYSDATE + 1) AND SYSDATE < NVL(purchcategories.disable_date,SYSDATE + 1) AND TO_CHAR(purchcategories.category_id) = purchasingcatmapping.external_source_key AND categories1.LANGUAGE = USERENV('LANG') AND purchasingcatmapping.category_key = categories1.key AND purchcategoriestl.category_id = purchcategories.category_id

1 Responses to “Query to Fetch PO Item Category and Oracle iProcurement Shopping Category Mapping Details (ICX_POR_CATEGORY_DATA_SOURCES, ICX_CAT_CATEGORIES_TL )”

Jon C said...
January 18, 2022 at 6:01 AM

Thanks, Helped a lot.


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.