Oracle Apps Knowledge Sharing

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