Search Results po_commodities_tl




Overview
Explore in AI Chat

Overview

The PO_COMMODITIES_TL table is a core translation table within the Oracle E-Business Suite Purchasing (PO) module. It is designed to support the multilingual capabilities of the application by storing translated names and descriptions for commodities. In Oracle EBS, a commodity is a classification used to categorize purchased items or services for reporting, sourcing, and compliance purposes. This table operates in conjunction with its base table, PO_COMMODITIES_B, which holds the language-independent attributes. The TL (Translation) table is a standard Oracle EBS architecture pattern, enabling the deployment of a single global instance that can present data in a user's preferred language.

Key Information Stored

The table's primary purpose is to store language-specific textual data. Its structure is defined by a composite primary key consisting of the COMMODITY_ID and LANGUAGE columns. The COMMODITY_ID is a foreign key that links each translated row to a unique record in the PO_COMMODITIES_B table. The LANGUAGE column stores the language code (e.g., 'US' for American English, 'KO' for Korean) for the translation. The most critical user-facing columns are NAME and DESCRIPTION, which hold the translated text for the commodity's name and a longer descriptive note, respectively. The table also includes standard Oracle Application Object Library columns such as CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN for auditing.

Common Use Cases and Queries

The primary use case is the dynamic presentation of commodity data in forms and reports based on the user's session language. For example, when a buyer creates a purchase order, the list of values for item categories will display names from this table in their language. A common reporting query involves joining the translation table to its base table to extract a comprehensible list. A typical SQL pattern is:

  • SELECT b.COMMODITY_CODE, tl.NAME, tl.DESCRIPTION, tl.LANGUAGE
  • FROM PO_COMMODITIES_B b, PO_COMMODITIES_TL tl
  • WHERE b.COMMODITY_ID = tl.COMMODITY_ID
  • AND tl.LANGUAGE = USERENV('LANG');

Data maintenance involves inserting or updating records in this table when new languages are added or translations are corrected, often performed via the application's translation forms or the standard Oracle Translation Editor.

Related Objects

PO_COMMODITIES_TL has a direct and dependent relationship with several key objects in the Purchasing schema, as indicated by the provided metadata.

  • PO_COMMODITIES_B: This is the primary related table. PO_COMMODITIES_TL is a child table of PO_COMMODITIES_B, joined via the COMMODITY_ID column. Every record in the TL table must correspond to a valid COMMODITY_ID in the base table.
  • Primary Key (PO_COMMODITIES_TL_PK): Enforces uniqueness on the combination of COMMODITY_ID and LANGUAGE.
  • Unique Key (PO_COMMODITIES_TL_UK1): Enforces a unique constraint on the NAME and LANGUAGE columns, preventing duplicate commodity names within the same language.

Other related objects not listed in the excerpt but central to the commodity's function include application forms (e.g., the Commodities form) and various views that likely join these two tables to present a complete record to the application layer.