Results for “po_sgd_lines_uda_v”

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

Overview

The APPS.PO_SGD_LINES_UDA_V view is a Purchasing (PO) module reporting object that exposes user-defined attribute (UDA) values captured against purchasing document lines, including lines held on draft purchasing documents. Its name reflects its role: "SGD" denotes the sourcing/governance document context used by the Public Sector and Supplier Governance extensions, while "UDA" identifies the user-defined attributes populated through a descriptive flexfield. The view is the primary read interface for line-level flexfield attribute data that has been assigned a specific attribute group and column usage.

In Oracle EBS 12.1.1 and 12.2.2, purchasing line descriptive flexfields are stored physically in a pivoted structure on PO_LINES_ALL_EXT_B and PO_LINES_DRAFT_ALL, where each column maps to a single flexfield segment. The view dynamically determines the active attribute group and column definitions from FND_DESCR_FLEX_COLUMN_USAGES and then unpivots the stored attribute columns into a normalized row-per-attribute layout. This makes the view suitable for reporting tools, extracts, and integrations that require a stable attribute-per-row interface rather than the wide, context-dependent physical table layout.

The view further enriches stored codes with their descriptive meanings, resolving lookups such as unit of measure codes, MDAPS/MAIS indicators, program codes, FSC/PSC classifications, NAICS codes, customer project codes, and inspection responsibility and location values into user-facing descriptions.

Underlying Base Objects

The view is defined over a set of documented base objects. The attribute definitions are derived from FND_DESCR_FLEX_COLUMN_USAGES, filtered to application identifier 201 and the descriptive flexfield name PO_LINE_EXT_ATTRS, excluding hidden segments. Attribute group context details are obtained through EGO_FND_DSC_FLX_CTX_EXT. Line-level and draft attribute values are sourced from PO_LINES_ALL_EXT_B, PO_LINES_DRAFT_ALL, and PO_LINES_ALL, which supply the primary keys used by the view.

Code translation relies on MTL_UNITS_OF_MEASURE_VL, PO_LOOKUP_CODES, FND_LOOKUP_VALUES, HR_LOCATIONS_ALL, and HR_LOCATION_EXTRA_INFO. Runtime session information such as language is retrieved from the FND_GLOBAL package, and PO_GEN_DIFF_PKG supports the underlying unpivot logic. For the 12.1.1 environment, the older flexfield storage model is referenced while the columns and keys remain consistent with the 12.2.2 metadata provided.

Key Columns

• PK1_VALUE — The PO_HEADER_ID of the purchasing document owning the attribute.
• PK2_VALUE — The DRAFT_ID, populated when the attribute belongs to a draft document; otherwise null.
• PK3_VALUE — The PO_LINE_ID of the line to which the attribute applies.
• PK4_VALUE / PK5_VALUE — Reserved placeholders, generally null.
• COL_NAME — The fully qualified attribute column name, formed as the uppercase attribute group context code concatenated with the end-user column name.
• COL_VALUE — The raw stored value of the attribute as held on the line or draft extension table.
• COL_DESC — The translated, displayable description of the attribute value, resolved via the decode logic for recognized lookups; null where no lookup applies.
• ATTR_GROUP_NAME / ATTR_NAME — Derived from the flexfield column usages, identifying the attribute group and the individual attribute (used internally to drive the COL_DESC translation).

Common Use Cases and Queries

The view is commonly used to extract line-level user-defined attributes for spend analysis, regulatory reporting, and downstream system interfaces, particularly where FSC/PSC, NAICS, or program code classifications must be reported alongside header and line identifiers.

A typical query retrieves all attributes for a purchasing document:

• SELECT pk1_value, pk3_value, col_name, col_value, col_desc FROM apps.po_sgd_lines_uda_v WHERE pk1_value = :po_header_id ORDER BY pk3_value, col_name;
• SELECT pk3_value, col_value FROM apps.po_sgd_lines_uda_v WHERE col_name = 'MDAPS_MAIS' AND pk2_value IS NULL;
• SELECT pk3_value, col_desc FROM apps.po_sgd_lines_uda_v WHERE col_name LIKE '%NAICS%';

Because the view resolves coded values into descriptions, it is preferred over direct queries against the extension tables when human-readable output is required. Transactions against the view are not supported; it is intended strictly for read-only reporting and integration extracts.