Results for “po_commodities_b”

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

Overview

PO_COMMODITIES_B is the base (non-translatable) table that stores commodity definitions used by the Oracle Purchasing (PO) module in Oracle E-Business Suite 12.1.1 and 12.2.2. In EBS terminology, a "commodity" is a classification entity used to categorize and group goods and services for purchasing, sourcing, and spend analysis, sitting alongside related classification constructs such as categories and UNSPSC codes. The table resides in the PO schema and, per the documented metadata, is flagged with a status of VALID.

Structurally, PO_COMMODITIES_B functions as the master or header record for each commodity. Because the table holds only the base-language, attribute-level definition and relies on a companion translation table for the descriptive text, it is a candidate hub in a Data Vault modeling sense — a central business entity keyed by a single surrogate identifier, from which multiple downstream tables and materialized views branch. This classification is offered as a heuristic suggestion mined from the foreign-key topology rather than as a prescriptive design.

Key Information Stored

The documented physical schema for release 12.2.2 lists eight columns. The most significant are:

• COMMODITY_ID — the surrogate primary key, defined by the constraint PO_COMMODITIES_B_PK and backed by unique index PO_COMMODITIES_B_U1. This is the internal identifier referenced by every dependent table and view.
• COMMODITY_CODE — the user-facing business key for the commodity, enforced unique by PO_COMMODITIES_B_U2. This is the value typically presented to buyers and suppliers.
• ACTIVE_FLAG — indicates whether the commodity is enabled for use in purchasing transactions, enabling soft retirement of obsolete classifications without deleting history.
• CREATION_DATE / LAST_UPDATE_DATE — audit timestamps recording when the row was inserted and last modified.
• CREATED_BY / LAST_UPDATED_BY — foreign references to the application user who created and last maintained the record.
• LAST_UPDATE_LOGIN — the login identifier associated with the most recent update, used for auditing and concurrency tracking.

Note the distinction between the two unique indexes: COMMODITY_ID is a system-assigned surrogate, while COMMODITY_CODE is the natural business key suitable for external integration and lookup by name.

Common Use Cases and Queries

The table is most often joined to its translation table to retrieve descriptions for reporting, and to category-cross-reference tables to resolve commodity-to-category mappings. Typical patterns include:

• Listing active commodities for a value-list or LOV: SELECT commodity_code FROM po.po_commodities_b WHERE active_flag = 'Y' ORDER BY commodity_code;
• Resolving a code to its identifier for integration: SELECT commodity_id FROM po.po_commodities_b WHERE commodity_code = :code;
• Retrieving descriptive text via the translation table: SELECT b.commodity_code, t.description FROM po.po_commodities_b b, po.po_commodities_tl t WHERE b.commodity_id = t.commodity_id AND t.language = USERENV('LANG');
• Mapping commodities to purchasing categories through PO_COMMODITY_CATEGORIES for spend and category-management reporting.
• Audit queries using CREATION_DATE, LAST_UPDATE_DATE, and LAST_UPDATED_BY to trace commodity maintenance activity.

The numerous POA_* materialized views in the Purchasing intelligence/analytics schema consume COMMODITY_ID for aggregated reporting, so commodity changes can affect the freshness of those summaries and may require refresh cycles.

Related Objects

The most significant dependents and companions, with their documented join columns, are:

• PO_COMMODITIES_TL — the translation table; joins on COMMODITY_ID and supplies the language-specific commodity description and name.
• PO_COMMODITY_CATEGORIES — cross-reference linking commodities to purchasing categories; joins on COMMODITY_ID.
• PO_COMMODITY_GRANTS — access-control records governing which users or organizations may use a given commodity; joins on COMMODITY_ID.
• POA_RTX_BS_MV, POA_API_002_MV, POA_POD_002_MV, POA_PQC_BS_MV and the wider set of POA_* materialized views — analytics summaries that reference COMMODITY_ID for spend, receipt, and quality reporting.

These relationships confirm PO_COMMODITIES_B as a central hub referenced by both transactional cross-reference tables and the Purchasing analytics layer.