Results for “po_commodities_vl”

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

Overview

PO_COMMODITIES_VL is a multilingual (VL) view owned by the APPS schema in Oracle E-Business Suite, belonging to the Purchasing (PO) product family. It presents commodity definitions in the language of the current session by joining the base commodity table with its translations table. The "_VL" suffix denotes a view that merges a "_B" (base) table containing language-independent attributes with a "_TL" (translation) table holding translatable text, filtered to the session's language. In Oracle EBS 12.1.1 and 12.2.2 the object is documented as VALID and remains available for reporting, integration, and inquiry against purchasing commodity setup data.

Because commodities can be assigned to purchasing categories and referenced on requisitions, purchase orders, and sourcing documents, this view serves as the standard read interface for obtaining a commodity's code, active status, and its linguistic name and description. It shields downstream consumers from having to join the base and translation tables manually and from the language-filtering logic themselves.

Underlying Base Objects

The view is defined over two synonyms that resolve to the underlying Purchasing tables:

• PO_COMMODITIES_B — the base table holding language-independent commodity data such as COMMODITY_ID, COMMODITY_CODE, ACTIVE_FLAG, and the standard WHO audit columns.
• PO_COMMODITIES_TL — the translation table holding the language-specific NAME and DESCRIPTION, keyed by COMMODITY_ID and LANGUAGE.

The view text joins these on COMMODITY_ID and restricts the translation row to T.LANGUAGE = USERENV('LANG'), so that only the translation matching the environment's current language is returned. The join is effectively an inner join, meaning a commodity row is visible only when a translation exists for the active language. Every row is exposed with the base table's ROWID as ROW_ID, preserving the base entity's identity for tools that rely on ROWID.

Key Columns

• ROW_ID — ROWID of the corresponding PO_COMMODITIES_B row; used by EBS forms and tools for row identification.
• COMMODITY_ID — primary key of the commodity, shared between the base and translation tables.
• COMMODITY_CODE — user-defined code that uniquely identifies the commodity; the primary business key.
• ACTIVE_FLAG — indicates whether the commodity is enabled ('Y') or disabled ('N') for use.
• NAME — the translated commodity name in the session language.
• DESCRIPTION — the translated description in the session language.
• CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns inherited from the base table.

Common Use Cases and Queries

Typical uses include LOV lookups in custom forms or OAF pages, reporting on active commodities, and validating commodity codes during data conversion or interface loads. A straightforward query listing active commodities is:

• SELECT commodity_code, name, description FROM po_commodities_vl WHERE active_flag = 'Y' ORDER BY commodity_code;
• SELECT commodity_id, commodity_code, name FROM po_commodities_vl WHERE commodity_code = :p_code; — single-commodity lookup by business key.
• SELECT b.commodity_code, v.name FROM po_commodities_vl v, po_commodities_b b WHERE b.commodity_id = v.commodity_id AND b.active_flag = 'Y';

Because the view enforces the current-language filter, callers requiring all translations must query PO_COMMODITIES_TL directly. When referencing the view from a report or interface, ensure the session language (USERENV('LANG')) is set appropriately so expected translations are returned.