Search Results icx_por_category_items_uk1

Overview

The ICX_POR_CATEGORY_ITEMS table is a core intersection table within the Oracle iProcurement (ICX) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as a relational junction, explicitly defining the many-to-many association between procurement categories and the items they contain. This structure is fundamental to the iProcurement catalog browsing experience, enabling users to navigate and locate items by category. The table's integrity is maintained through primary and unique key constraints, ensuring that each relationship between a specific category and a specific item is recorded uniquely and consistently.

Key Information Stored

The table's structure is intentionally simple, consisting of two primary foreign key columns that form its composite keys. The column RT_CATEGORY_ID stores the unique identifier for a procurement category. The column RT_ITEM_ID, which was the focus of the user's search, stores the unique identifier for an item within the iProcurement catalog. As per the provided metadata, these two columns together constitute both the primary key (ICX_POR_CATEGORY_ITEMS_PK) and a unique key (ICX_POR_CATEGORY_ITEMS_UK1), preventing duplicate category-item assignments. The foreign key constraint explicitly documents that the RT_ITEM_ID column references the ICX_POR_ITEMS table, linking the intersection data to the master item record.

Common Use Cases and Queries

The primary use case for this table is supporting catalog search and reporting on item categorization. A common operational query is to list all items assigned to a specific category for catalog management or audit purposes. Conversely, administrators may query to find all categories to which a particular item belongs. For reporting, this table is frequently joined with ICX_POR_ITEMS and category dimension tables to generate user-friendly catalog content reports. A sample SQL pattern to retrieve item details for a category would be:

• SELECT i.item_id, i.item_description, c.category_name
• FROM icx_por_category_items ci,
• icx_por_items i,
• icx_por_categories c
• WHERE ci.rt_item_id = i.rt_item_id
• AND ci.rt_category_id = c.rt_category_id
• AND c.category_name = 'OFFICE SUPPLIES';

Related Objects

The ICX_POR_CATEGORY_ITEMS table has direct and essential dependencies within the iProcurement schema. Its primary relationship, as defined by the foreign key, is with ICX_POR_ITEMS, the master table for catalog items. The RT_CATEGORY_ID column implicitly relates to a category master table, typically ICX_POR_CATEGORIES or related EBS inventory category tables. This intersection table is a critical data source for catalog rendering within the iProcurement application interface and for any custom integrations or extensions that need to programmatically understand the hierarchical organization of the procurement catalog.