Results for “po_commodities_tl”

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

Overview

PO_COMMODITIES_TL is the translation table for Purchasing commodities in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It resides in the PO (Purchasing) schema and stores the language-dependent attributes of a commodity record — specifically the translatable NAME and DESCRIPTION text — while the language-independent attributes are held in the base table PO_COMMODITIES_B. Oracle EBS follows a "_B / _TL" pattern for translatable entities: the "_B" table holds the base, language-neutral row (identified by the primary key, in this case COMMODITY_ID), and the "_TL" table holds one row per installed language for each base row. PO_COMMODITIES_TL therefore enables multi-language commodity catalogs so that each operating unit and user sees commodity names and descriptions in their session language.

Commodities in Purchasing are the classification values used to categorize goods and services on requisitions, purchase orders, and related documents, and are also used to drive sourcing, spend analysis, and supplier qualification. From a Data Vault modeling perspective, the documented heuristic classification is satellite-leaning: PO_COMMODITIES_TL depends directly on PO_COMMODITIES_B via a foreign key and adds descriptive, historized-style attributes rather than representing an independent hub or an associative link.

Key Information Stored

The table contains ten documented columns. The most important are:

• COMMODITY_ID — the surrogate key referencing the base commodity in PO_COMMODITIES_B. It is the first column of the composite primary key PO_COMMODITIES_TL_PK and of the foreign key to PO_COMMODITIES_B.
• LANGUAGE — the NLS language code for the translated row; it is the second column of the primary key, so a single commodity has one translated row per installed language.
• NAME — the translated commodity name. It participates in the business-key unique index PO_COMMODITIES_TL_U2 (LANGUAGE, NAME), which enforces uniqueness of a name within a language.
• DESCRIPTION — the translated long description of the commodity.
• SOURCE_LANG — the language code of the source (usually the base) record from which the translation was derived.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns recording insert and update context.

Two unique indexes are documented: PO_COMMODITIES_TL_U1 (COMMODITY_ID, LANGUAGE), which mirrors the primary key and guarantees one row per commodity-language pair, and PO_COMMODITIES_TL_U2 (LANGUAGE, NAME), the business-key candidate enforcing that commodity names are unique per language. The surrogate primary key is thus (COMMODITY_ID, LANGUAGE), while NAME and LANGUAGE together form the natural business key.

Common Use Cases and Queries

The most frequent requirement is retrieving the translated name and description for a commodity, constrained to a specific language. A typical query joins the translation to the base table:

• SELECT b.commodity_id, t.name, 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');
• Lookup by name within a language, exploiting the U2 index: SELECT commodity_id FROM po.po_commodities_tl WHERE language = :lang AND name = :name;
• Reporting that counts how many translations exist per commodity: SELECT commodity_id, COUNT(*) FROM po.po_commodities_tl GROUP BY commodity_id;
• Detecting commodities missing a translation in a target language: SELECT b.commodity_id FROM po.po_commodities_b b WHERE NOT EXISTS (SELECT 1 FROM po.po_commodities_tl t WHERE t.commodity_id = b.commodity_id AND t.language = :target_lang);

These patterns support commodity catalog maintenance, global implementations requiring translated classification values, and reconciliation of translation completeness during multi-language rollouts.

Related Objects

• PO_COMMODITIES_B — the base table; PO_COMMODITIES_TL.COMMODITY_ID references PO_COMMODITIES_B, and joins are made on COMMODITY_ID.
• PO_COMMODITIES_V — the commonly used view that presents base and translated attributes together for forms and reports.
• PO_LINES_ALL / PO_LINE_LOCATIONS_ALL — purchasing document lines that carry a commodity reference used for spend classification.
• PO_REQUISITION_LINES_ALL — requisition lines referencing commodities on demand.
• PO_COMMODITIES_TL_PK / PO_COMMODITIES_TL_U1 / PO_COMMODITIES_TL_U2 — the primary key and unique indexes enforcing row and business-key integrity.
• FND_LANGUAGES — the language definition view that validates the LANGUAGE code used in translation rows.

Collectively, these objects make PO_COMMODITIES_TL a compact but essential part of the Purchasing commodity classification model in Oracle EBS.