Search Results po_clm_program_codes

Overview

PO_SGD_MOD_LINES_UDA_V is an Oracle E-Business Suite 12.1.1 / 12.2.2 view owned by the APPS schema and categorized under the PO - Purchasing product. It exposes user-defined attribute (UDA) values captured at the purchase order line level for the PO_LINE_EXT_ATTRS descriptive flexfield, presented in a normalized, column-per-attribute form. Rather than returning the flexfield segments as the raw C_EXT_ATTRn columns of the underlying draft line table, the view unpivots those segments and re-labels each one with a meaningful attribute name, and where applicable resolves coded values into their display descriptions.

Its role is reporting and integration. The unpivoted layout makes it straightforward to join PO line context to downstream analytics, extracts, or interfaces without hardcoding segment positions, since each row carries the flexfield context code and end-user column name derived from FND_DESCR_FLEX_COLUMN_USAGES. The view also applies descriptive lookups for a defined set of attribute groups, including UOM, MDAPS/MAIS, PRGM_CODE, FSC_PSC, NAICS, INSP_RESP, INSP_LOC, and notably CUST_PRJ_CODE — the attribute that a user searching on "cust_prj_code" is typically trying to reach.

Underlying Base Objects

The documented base objects include PO_LINES_DRAFT_ALL, PO_LINES_ALL_EXT_B, EGO_FND_DSC_FLX_CTX_EXT, FND_DESCR_FLEX_COLUMN_USAGES, FND_LOOKUP_VALUES, MTL_UNITS_OF_MEASURE_VL, HR_LOCATIONS_ALL, HR_LOCATION_EXTRA_INFO, and PO_LOOKUP_CODES, with supporting references to the FND_GLOBAL package and PO_GEN_DIFF_PKG. The draft line table supplies the PO_HEADER_ID, PO_LINE_ID, DRAFT_ID and the raw C_EXT_ATTRn values, which are first unpivoted in an inline subquery (PO_LINE_EXT_UNPIVOT_DATA). The flexfield usage view maps those columns to their application column name, context code, and end-user column name, filtered to APPLICATION_ID = 201 and DESCRIPTIVE_FLEXFIELD_NAME = 'PO_LINE_EXT_ATTRS' with non-hidden segments. The lookup-bearing objects drive value translation: PO_LOOKUP_CODES resolves MDAPS/MAIS, PRGM_CODE, CUST_PRJ_CODE, INSP_RESP and INSP_LOC; FND_LOOKUP_VALUES resolves FSC_PSC and NAICS; and MTL_UNITS_OF_MEASURE_VL resolves UOM codes.

Key Columns

• PK1_VALUE — PO_HEADER_ID, the purchase order header identifier.
• PK2_VALUE — DRAFT_ID, the draft line identifier used during change-order processing.
• PK3_VALUE — PO_LINE_ID, the purchase order line identifier.
• COL_NAME — concatenation of the flexfield context code and end-user column name, uniquely naming each attribute.
• COL_VALUE — the stored attribute value.
• COL_DESC — the decoded description, produced by DECODE on ATTR_NAME for the recognized attribute groups, including CUST_PRJ_CODE via PO_CLM_CUSTOMER_PROJECT_CODE, otherwise NULL.

The presence of COL_DESC is the primary reason this view is preferred over querying the flexfield columns directly. CUST_PRJ_CODE rows return the displayed value from PO_LOOKUP_CODES for lookup type PO_CLM_CUSTOMER_PROJECT_CODE. Attributes outside the recognized list retain their raw COL_VALUE only.

Common Use Cases and Queries

Typical scenarios include extracting customer or project code assignments on contract lines, validating UDA population for interface loads, and building supplier or program reporting that depends on flexfield context. A query targeting the customer project code attribute would filter on the end-user name embedded in COL_NAME:

• SELECT pk1_value po_header_id, pk3_value po_line_id, col_value, col_desc FROM po_sgd_mod_lines_uda_v WHERE col_name LIKE '%CUST_PRJ_CODE';
• SELECT pk3_value po_line_id, col_name, col_value, col_desc FROM po_sgd_mod_lines_uda_v WHERE pk1_value = :po_header_id;

Because the view is a draft-aware construct, consumers should confirm whether draft or approved line context is required before joining to PO_HEADERS_ALL or PO_LINES_ALL; the PK2_VALUE draft identifier provides the linkage needed to reconcile draft rows. Joins back to PO_LINES_ALL_EXT_B on PK3_VALUE allow approved segment values to be compared against the decoded attribute descriptions surfaced here.