Item Catalog – Category Assignment in Fusion

If you have defined items in Oracle Fusion, then you certainly would have defined catalog and categories and attached them to the items. And even more certain is the fact that you’ll end up creating report for fetching item details based on the Catalog – Category. So let’s discuss the queries needed for report. But before that let me list down the important tables you’d need:

Imp Tables of Catalog – Category

egp_category_sets_tl: Table to hold the catalog
egp_category_set_valid_cats: This table contains the relationship between catalog and categories.
egp_category_sets_b: Base table of catalog
egp_categories_b: Category base table
egp_categories_tl: Translate table of categories
egp_item_categories: Table to hold relationship between item and the category.

Want to see the table details of the tables used, here you go: Item Catalog Category Table Details

1. item Catalog Query: This query can be used to create the catalog parameter to run the report for a specific catalog.
select category_set_name
from egp_category_sets_tl


2. item Category Query: Once you have chosen the item catalog, ideally you would want your item category parameter to show only relevant categories. So here is the query:

select distinct ect.category_name
from egp_category_set_valid_cats eic,
egp_category_sets_tl ecst,
egp_category_sets_b ecsb,
egp_categories_b ecb,
egp_categories_tl ect
where eic.category_set_id = ecst.category_set_id
and ecst.language = ‘US’
and ecst.category_set_id = ecsb.category_set_id
and eic.category_id = ect.category_id
and ect.language = ‘US’
and ect.category_id = ecb.category_id
and ecst.category_set_name = :Catalog
Order by ect.category_name

Here “:P_Catalog” is the parameter name for catalog.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

3. item – categories relationship: this query you can use in your report to fetch various categories and catalogs assigned to the item:

select distinct ESI.item_number, ecst.category_set_name, ect.category_name
from inv_org_parameters iop,
EGP_SYSTEM_ITEMS_B ESI,
egp_item_categories eic,
egp_category_sets_tl ecst,
egp_categories_tl ect
where 1=1
and esi.organization_id=iop.organization_id
and eic.inventory_item_id = esi.inventory_item_id
and eic.organization_id = esi.organization_id
and eic.category_set_id = ecst.category_set_id
and ecst.language = USERENV(‘LANG’)
and eic.category_id = ect.category_id
and ect.language = USERENV(‘LANG’)
and ect.category_name=NVL(:P_category,ect.category_name)
and ecst.category_set_name = NVL(:P_Catalog,ecst.category_set_name)
and iop.organization_code=:Inv_Organization

Here :P_Category is input parameter for category name and :inv_organization is your inventory org which you need to query.

Also watch out for end_date in table egp_item_categories depending on the requirement of fetching expired relationships.

Related Posts:

Item Table in Oracle Fusion – Inventory – EGP_SYSTEM_ITEMS_B
Item Catalog Category Table Details

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com