Search Results clm_office_code

Overview

PO_SGD_MOD_ADDRESSES_UDA_V is a Purchasing (PO) module view owned by the APPS schema. It is a modification (draft) address attribute view that surfaces user-defined descriptive flexfield attributes recorded against purchasing document header address contexts. The view name encodes its purpose: PO (Purchasing), SGD (Document/Descriptive Flexfield generation context), MOD (modification or draft), ADDRESSES (the descriptive flexfield context code), and UDA (user-defined attributes).

The view is designed for the change-order and modification workflow where address attributes are captured in draft form on PO_HEADERS_ALL_EXT_B before being promoted to the base PO header. Its primary consumers are Oracle EBS reporting engines, Oracle BI Publisher data templates, and integration layers that need to extract descriptive flexfield values in a normalized key-value format. A notable characteristic of this view is its use of the UNPIVOT operator, which converts the wide attribute columns of the flexfield into a narrow COL_NAME / COL_VALUE shape, making the output suitable for generic attribute rendering and comparison logic.

Underlying Base Objects

The view is defined over four documented base objects:

• PO_HEADERS_ALL_EXT_B — the extension table that stores draft header descriptive flexfield values, including C_EXT_ATTR1, C_EXT_ATTR5, C_EXT_ATTR6, and N_EXT_ATTR1 through N_EXT_ATTR3. This is the row-level source of attribute data.
• EGO_FND_DSC_FLX_CTX_EXT — resolves the internal ATTR_GROUP_ID to the descriptive flexfield context code, restricted here to the ADDRESSES context for the PO_HEADER_EXT_ATTRS flexfield.
• FND_DESCR_FLEX_COLUMN_USAGES — supplies the end-user column names (ATTR_NAME) that give each UNPIVOTED column its human-readable label. Rows with DISPLAY_FLAG of 'H' (hidden) are excluded.
• HR_LOCATION_EXTRA_INFO — referenced in a scalar subquery to translate an ADDRESSCODE value into the CLM_OFFICE_CODE location attribute, populating COL_DESC.
• PO_GEN_DIFF_PKG — the package supplying PO_GEN_DIFF_PKG.GETMODPK1 and GETMODPK2, which bind the view to the currently active modification (draft) primary keys and drive the join predicate on PO_HEADER_ID and DRAFT_ID.

Key Columns

• PK1_VALUE — the PO_HEADER_ID of the purchasing document, forming the first component of the composite key identifying the modified record.
• PK2_VALUE — the DRAFT_ID, the second key component identifying the change-order draft version.
• PK3_VALUE, PK4_VALUE, PK5_VALUE — reserved key columns, currently returned as NULL.
• COL_NAME — the concatenation of the address type C_EXT_ATTR1 (for example SBA_OFFICE, PAY_OFFICE, ADMIN_OFFICE or ISSUING_OFFICE) with the flexfield attribute name, formatted as ADDRESS_TYPE_ATTR_NAME in upper case.
• COL_VALUE — the underlying value of the attribute, unpivoted from C_EXT_ATTR5, C_EXT_ATTR6, N_EXT_ATTR1, N_EXT_ATTR2, or N_EXT_ATTR3. This column is where the user's search term, N_EXT_ATTR1, appears in the source data.
• COL_DESC — a descriptive value populated only for the ADDRESSCODE attribute, obtained from HR_LOCATION_EXTRA_INFO.LEI_INFORMATION1; otherwise NULL.

Common Use Cases and Queries

Typical usage involves pulling the current modification's address flexfield values for a given document, or locating a document by a specific user-defined attribute such as N_EXT_ATTR1.

To list all address attributes for a specific purchase order draft:

SELECT pk1_value, pk2_value, col_name, col_value
FROM   apps.po_sgd_mod_addresses_uda_v
WHERE  pk1_value = :po_header_id;

To filter on the user's search term, note that the attribute is exposed through COL_NAME rather than as a standalone column:

SELECT pk1_value, col_name, col_value
FROM   apps.po_sgd_mod_addresses_uda_v
WHERE  col_name LIKE '%N_EXT_ATTR1%';

Because the view is coupled to PO_GEN_DIFF_PKG.GETMODPK1 and GETMODPK2, results are scoped to the modification context currently held by that package. Queries executed outside the appropriate change-order or modification transaction may return no rows. This makes the view well suited to interactive change-order comparison reports, attribute-difference audits between base and draft records, and integration extracts feeding downstream systems that expect a normalized attribute pair format.