Search Results po_attribute_values_tlp_u1

Overview

PO.PO_ATTRIBUTE_VALUES_TLP is a translatable ("TLP") descriptor value table owned by the Oracle Purchasing (PO) schema. As its name and the ETRM documentation note, it "serves as a data structure to hold translatable descriptor values." In Oracle EBS releases 12.1.1 and 12.2.2, descriptor values are the user-defined, category-specific attributes attached to purchasing documents and requisition templates, and this table stores those values in a language-dependent form so that descriptive text renders correctly per session language.

The table is physically organized in the APPS_TS_TX_DATA tablespace with PCT Free 10, while its two unique indexes reside in APPS_TS_TX_IDX. The documented physical schema describes 174 columns, making it a wide, denormalized staging structure typical of EBS descriptor and flexfield storage. Under a Data Vault modeling heuristic, the metadata classifies this object as a standalone entity — that is, it has no mined foreign-key relationships to parent hubs or links, and is best modeled as an independent table rather than a hub, link, or satellite component of a larger integrated model.

Key Information Stored

The primary key is ATTRIBUTE_VALUES_TLP_ID, a NUMBER column, and the ETRM metadata also names its unique index PO_ATTRIBUTE_VALUES_TLP_U1 on this single column. A second unique index, PO_ATTRIBUTE_VALUES_TLP_U2, spans the composite business key (PO_LINE_ID, REQ_TEMPLATE_NAME, REQ_TEMPLATE_LINE_NUM, INVENTORY_ITEM_ID, ORG_ID, LANGUAGE), which identifies a descriptor value per line/revision, template context, item, organization, and language.

• ATTRIBUTE_VALUES_TLP_ID — surrogate primary key and the target of index _U1.
• PO_LINE_ID — foreign reference to a purchasing line; the documentation states it holds a positive value when the record is a child of a blanket line or quotation line, and -2 otherwise.
• REQ_TEMPLATE_NAME, REQ_TEMPLATE_LINE_NUM — identify the requisition template and template line the value belongs to; both store -2 when not template-driven.
• IP_CATEGORY_ID — the item category used by iProcurement.
• INVENTORY_ITEM_ID, ORG_ID — the inventory item and organization context for the descriptor value.
• LANGUAGE — the language in which the descriptor values are stored, enabling multilingual display.
• DESCRIPTION, LONG_DESCRIPTION — short (240) and extended (4000) descriptor text.
• MANUFACTURER, COMMENTS, ALIAS — additional translatable descriptor attributes.
• TL_TEXT_BASE_ATTRIBUTE1–100 — a large numbered block of base translatable text attributes.
• TL_TEXT_CAT_ATTRIBUTE1–50 — numbered category-specific translatable text attributes.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) plus REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, LAST_UPDATED_PROGRAM, and REBUILD_SEARCH_INDEX_FLAG support audit and concurrent-program maintenance.

Common Use Cases and Queries

The table is queried primarily for descriptor reporting on blanket agreements, quotations, and requisition templates, and for iProcurement category-attribute display. A typical lookup resolves descriptor values for a specific purchasing line and language:

• SELECT attribute_values_tlp_id, description, long_description, language FROM po.po_attribute_values_tlp WHERE po_line_id = :line_id AND org_id = :org_id;
• Join to a base (non-TL) descriptor table on the shared identifier to obtain the value pair, then filter by LANGUAGE to render the correct translation.
• Reporting iProcurement category attributes: filter on IP_CATEGORY_ID and INVENTORY_ITEM_ID to retrieve category-attribute text for a given item.
• Template-driven queries use REQ_TEMPLATE_NAME and REQ_TEMPLATE_LINE_NUM, recognizing the -2 sentinel meaning "not applicable."
• Maintenance scripts detect stale search indexes via REBUILD_SEARCH_INDEX_FLAG or audit drift through LAST_UPDATE_DATE.

Because descriptor values are frequently stored in the TL base table and joined to this TLP table for translation, queries should always constrain LANGUAGE to avoid duplicate rows.

Related Objects

The ETRM metadata classifies PO_ATTRIBUTE_VALUES_TLP as standalone, with no mined foreign keys; related objects are therefore inferred from the documented business-key columns rather than enforced constraints:

• PO.PO_ATTRIBUTE_VALUES_TL — the base translatable table joined via the descriptor value identifier.
• PO.PO_LINES_ALL — joined on PO_LINE_ID to link descriptor values to purchasing lines.
• PO.PO_REQ_TEMPLATES_ALL / PO_REQ_TEMPLATE_LINES — joined on REQ_TEMPLATE_NAME and REQ_TEMPLATE_LINE_NUM.
• PO.PO_HEADERS_ALL — indirectly related through PO_LINE_ID for document-level reporting.
• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID for item context.
• HR_OPERATING_UNITS / org views — joined on ORG_ID for organization filtering.
• FND_LANGUAGES — joined on LANGUAGE for translation display.
• PO_ATTRIBUTE_VALUES_TLP_U1 / _U2 indexes — the unique indexes enforcing primary and business keys.