Results for “no_comp_rsn”

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

Overview

APPS.PO_SGD_HDRS_UDA_V is a reporting view in Oracle E-Business Suite that exposes user-defined attributes (UDAs) and descriptive flexfield segments captured against purchasing document headers. It is part of the Supplier Governance / Supplier Diversity data model used by the Procurement Contract Compliance (CLM) and related federal reporting features, and it presents these attributes in a horizontally normalized form so the same header can contribute multiple (attribute name, value) rows. The view is primarily consumed by Oracle Business Intelligence, Discoverer worksheets, and OBIEE/BI Publisher extracts, and it also surfaces the decoded, human-readable meaning of coded flexfield values rather than the raw stored codes.

The user search term "no_comp_rsn" maps to the view attribute name NO_COMP_RSN, which appears as one of the decode branches in the view text. When ega.col_name equals 'NO_COMP_RSN', the view resolves the stored code against the lookup type PO_CLM_AUTH_OTR_FULL_OPEN_COMP so report consumers receive the lookup description. This makes the view the canonical source for the "no competition reason" value displayed on contract compliance and full/open competition reports.

Underlying Base Objects

The documented base objects for this view are:

• PO_HEADERS_ALL_EXT_B (synonym) — the descriptive flexfield extension table for purchasing document headers.
• EGO_FND_DSC_FLX_CTX_EXT (synonym) — the context/segment metadata used to resolve flexfield column names and attribute names.
• FND_DESCR_FLEX_COLUMN_USAGES (synonym) — used to map segment columns to their descriptive usage.
• PO_LOOKUP_CODES (view) and FND_LOOKUP_VALUES (synonym) — used in the DECODE logic to translate stored codes into displayed descriptions and meanings.
• FND_GLOBAL (package) — supplies session context, notably the language used in userenv('lang') for FND_LOOKUP_VALUES lookups.
• PO_GEN_DIFF_PKG (package) — the supplied PL/SQL package called from within the view to materialize the flexfield attribute/column/value combinations.

Functionally the view is a decode-and-pivot layer: PO_GEN_DIFF_PKG produces the col_name/col_value pairs drawn from PO_HEADERS_ALL_EXT_B, EGO_FND_DSC_FLX_CTX_EXT and FND_DESCR_FLEX_COLUMN_USAGES supply the segment identities, and the lookup tables resolve codes into displayed values.

Key Columns

• PK1_VALUE — the PO_HEADER_ID of the purchasing document header.
• PK2_VALUE — the draft (version) identifier associated with the header.
• PK3_VALUE through PK5_VALUE — reserved for multi-part keys and returned as NULL in this definition, allowing uniform key handling downstream.
• COL_NAME — the flexfield attribute name, for example NO_COMP_RSN, MOD_AUTH, CONTRACT_CATEGORY, DPAS_RATING, NAICS.
• COL_VALUE — the decoded display value of the attribute, obtained from PO_LOOKUP_CODES or FND_LOOKUP_VALUES using the appropriate lookup type (such as PO_CLM_AUTH_OTR_FULL_OPEN_COMP for NO_COMP_RSN).

Common Use Cases and Queries

The view is used to report federal contract compliance attributes, supplier diversity classifications, and authorization reasons on purchase orders and agreements. A typical query filtering on the no-competition reason is:

• SELECT pk1_value, pk2_value, col_value FROM apps.po_sgd_hdrs_uda_v WHERE col_name = 'NO_COMP_RSN';
• SELECT h.segment1, v.col_name, v.col_value FROM apps.po_sgd_hdrs_uda_v v, apps.po_headers_all h WHERE h.po_header_id = v.pk1_value AND v.col_name IN ('MOD_AUTH','CONTRACT_CATEGORY','SET_ASIDE_TYPE');
• Reporting full and open competition justifications by joining on pk1_value and grouping by col_name.

Because the view already performs the lookup decode, reports should not re-join to PO_LOOKUP_CODES for these attributes; doing so risks double decoding. Also note that PK3_VALUE through PK5_VALUE are intentionally NULL and should not be relied upon as join keys. Standard ETRM practice is to query the view with the header identifier filters pushed down to PK1_VALUE to minimize the cost of the flexfield derivation and lookup resolution.