Results for “po_commodity_categories_u2”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PO_COMMODITY_CATEGORIES is a Purchasing (PO) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that functions as a cross-reference between purchasing commodities and Oracle inventory categories. Its documented description, "List of Categories in Commodities," captures its purpose precisely: it enumerates the category assignments associated with each commodity record. Commodities in EBS represent high-level groupings used in procurement and sourcing workflows (for example, in commodity-based sourcing rules, blanket agreements, and supplier qualification), while categories derive from the MTL_CATEGORIES_B structure used elsewhere in inventory, purchasing, and iProcurement. This table therefore bridges two classification hierarchies.

In Data Vault modeling terms, the heuristic classification mined from the foreign key structure is link. This is consistent with the table's role: it holds no descriptive attributes of its own beyond standard audit columns and instead resolves a many-to-many relationship between PO_COMMODITIES_B and MTL_CATEGORIES_B through the COMMODITY_ID and CATEGORY_ID pair. Any downstream dimensional or Data Vault implementation should treat it as a link table rather than a hub or satellite.

Key Information Stored

The table is documented with seven physical columns. The most significant are:

• CATEGORY_ID — The foreign key to MTL_CATEGORIES_B. It is also documented as the primary key column of PO_COMMODITY_CATEGORIES_PK and as the sole column of unique index PO_COMMODITY_CATEGORIES_U1. This dual role as both PK and FK is the source of the table's link semantics.
• COMMODITY_ID — The foreign key to PO_COMMODITIES_B, identifying the commodity to which the category is attached.
• CREATION_DATE — Standard audit column recording when the association row was created.
• CREATED_BY — The user ID of the creator of the row.
• LAST_UPDATE_DATE — Timestamp of the most recent modification.
• LAST_UPDATED_BY — User ID of the last modifier.
• LAST_UPDATE_LOGIN — Login identifier associated with the last update, used by EBS auditing.

Two unique indexes are documented: PO_COMMODITY_CATEGORIES_U1 on CATEGORY_ID and PO_COMMODITY_CATEGORIES_U2 on the composite (COMMODITY_ID, CATEGORY_ID). The U2 index is the true business-key candidate, enforcing that a given commodity-category pairing appears only once. The U1 index on CATEGORY_ID alone implies a stronger constraint — effectively that each category may be linked to only one commodity — which callers should verify against their specific patch level, because the primary key definition and the U1 unique index both reference CATEGORY_ID.

Common Use Cases and Queries

The principal use case is resolving the commodity-to-category mapping for reporting, sourcing rule validation, and data migration. A typical join retrieves the commodity description alongside its associated category:

• Reporting: join PO_COMMODITY_CATEGORIES to PO_COMMODITIES_B on COMMODITY_ID and to MTL_CATEGORIES_B on CATEGORY_ID to produce a commodity-category listing for procurement analysis.
• Sourcing and qualification: identify which categories are covered by a commodity when configuring commodity-based sourcing rules or supplier qualification criteria.
• Data validation: detect orphaned or duplicate assignments using the composite uniqueness enforced by PO_COMMODITY_CATEGORIES_U2.
• Audit reporting: use CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY to trace when commodity-category relationships were established or changed.

A representative query pattern is a three-way join: SELECT pcb.commodity_name, mcb.category_name FROM po.po_commodity_categories pcc, po.po_commodities_b pcb, mtl_categories_b mcb WHERE pcc.commodity_id = pcb.commodity_id AND pcc.category_id = mcb.category_id. Filters on CREATION_DATE or LAST_UPDATE_DATE support incremental extracts.

Related Objects

The documented foreign keys identify the two most significant related tables:

• PO_COMMODITIES_B — Referenced via PO_COMMODITY_CATEGORIES.COMMODITY_ID. This is the base commodity definition table and the primary parent in the relationship.
• MTL_CATEGORIES_B — Referenced via PO_COMMODITY_CATEGORIES.CATEGORY_ID. This is the base inventory category table, shared across Inventory, Purchasing, and iProcurement.

Additional objects that commonly participate in reporting joins include MTL_CATEGORIES_TL (category translations), MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL (category set definitions), PO_COMMODITIES_TL (commodity translations where description language is required), and the PO_COMMODITIES_VL and MTL_CATEGORIES_VL views that expose translated names. The PO_COMMODITY_CATEGORIES_PK and the two unique indexes described above are the primary structural dependencies. Because the table is a pure link with no denormalized attributes, dependent customizations typically resolve it through its parent tables rather than querying it in isolation.