View: PO_SGD_HDRS_UDA_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.PO_SGD_HDRS_UDA_V

View Text

SELECT POEXT.PO_HEADER_ID PK1_VALUE
, POEXT.DRAFT_ID PK2_VALUE
, NULL PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, EGA.COL_NAME
, POEXT.COL_VALUE
, DECODE(EGA.ATTR_NAME
, 'MOD_AUTH'
, (SELECT DESCRIPTION
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_UDA_MOD_AUTHORITY'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'NO_COMP_RSN'
, (SELECT DESCRIPTION
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_AUTH_OTR_FULL_OPEN_COMP'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'CONTRACT_CATEGORY'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CONTRACT_CATEGORY'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'ADMIN_SCD'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_ADMIN_SCD'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'DPAS_RATING'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_DPAS_RATING'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'PRIORITY'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_PRIORITY_CODE'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'CUST_PROJ_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'SET_ASIDE_TYPE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_SET_ASIDE_TYPE'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'SIZE_STANDARD'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_SIZE_STANDARD'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'SET_ASIDE_STAT'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_SET_ASIDE_STATUS'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'NAICS'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'FV_NAICS_TYPE'
AND LOOKUP_CODE = POEXT.COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'CCR_EXP_REASON'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CCR_EXP_REASON'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'SUPLR_SIZE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_VENDOR_SIZE'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'EFT_EXC_RESN'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_EFT_EXCLUSION_REASON'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'EFT_INDCTR'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EGO_YES_NO'
AND LOOKUP_CODE = POEXT.COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'PERIOD_STATUS'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_PERIOD_STATUS'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'BOND_REQUIRED'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_BOND_REQUIRED_YN'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, NULL) COL_DESC
FROM ( SELECT FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, FCU.APPLICATION_COLUMN_NAME
, UPPER(FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE || '_' || FCU.END_USER_COLUMN_NAME) COL_NAME
, UPPER(FCU.END_USER_COLUMN_NAME) ATTR_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE FCU.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
AND FCU.APPLICATION_ID = 201
AND FCU.DISPLAY_FLAG <> 'H' ) EGA
, ( WITH PO_HEADER_EXT_UNPIVOT_DATA AS ( SELECT PO_HEADER_ID
, DRAFT_ID
, EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, C_EXT_ATTR1
, C_EXT_ATTR2
, C_EXT_ATTR3
, C_EXT_ATTR4
, C_EXT_ATTR5
, C_EXT_ATTR6
, C_EXT_ATTR7
, C_EXT_ATTR8
, C_EXT_ATTR9
, C_EXT_ATTR10
, C_EXT_ATTR11
, C_EXT_ATTR12
, C_EXT_ATTR13
, C_EXT_ATTR14
, C_EXT_ATTR15
, C_EXT_ATTR16
, C_EXT_ATTR17
, C_EXT_ATTR18
, C_EXT_ATTR19
, C_EXT_ATTR20
, C_EXT_ATTR21
, C_EXT_ATTR39
, C_EXT_ATTR40
, TO_CHAR(N_EXT_ATTR1) AS N_EXT_ATTR1
, TO_CHAR(N_EXT_ATTR2) AS N_EXT_ATTR2
, TO_CHAR(N_EXT_ATTR3) AS N_EXT_ATTR3
FROM PO_HEADERS_ALL_EXT_B EXT
, EGO_FND_DSC_FLX_CTX_EXT EGG
WHERE EXT.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1 AND EXT.DRAFT_ID = PO_GEN_DIFF_PKG.GETBASEPK2 AND EXT.ATTR_GROUP_ID = EGG.ATTR_GROUP_ID
AND EGG.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
AND EGG.APPLICATION_ID = 201
AND EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('SF252_INFORMAT'
, 'AWD_CTGR'
, 'SET_ASIDE_INFO'
, 'SF1442_INFORMA'
, 'BU_PRI_PRJ_INF'
, 'SUPPLIER_DTLS') ) SELECT PO_HEADER_ID
, DRAFT_ID
, ATTR_GROUP_NAME
, COL_NAME
, COL_VALUE
FROM PO_HEADER_EXT_UNPIVOT_DATA UNPIVOT INCLUDE NULLS (COL_VALUE FOR COL_NAME IN ( C_EXT_ATTR1
, C_EXT_ATTR2
, C_EXT_ATTR3
, C_EXT_ATTR4
, C_EXT_ATTR5
, C_EXT_ATTR6
, C_EXT_ATTR7
, C_EXT_ATTR8
, C_EXT_ATTR9
, C_EXT_ATTR10
, C_EXT_ATTR11
, C_EXT_ATTR12
, C_EXT_ATTR13
, C_EXT_ATTR14
, C_EXT_ATTR15
, C_EXT_ATTR16
, C_EXT_ATTR17
, C_EXT_ATTR18
, C_EXT_ATTR19
, C_EXT_ATTR20
, C_EXT_ATTR21
, C_EXT_ATTR39
, C_EXT_ATTR40
, N_EXT_ATTR1
, N_EXT_ATTR2
, N_EXT_ATTR3 ) ) ) POEXT
WHERE EGA.ATTR_GROUP_NAME = POEXT.ATTR_GROUP_NAME
AND EGA.APPLICATION_COLUMN_NAME = POEXT.COL_NAME

Columns