View: PO_SGD_ADDRESSES_UDA_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.PO_SGD_ADDRESSES_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
, UPPER(POEXT.ADDRESS_TYPE || '_' || EGA.ATTR_NAME) COL_NAME
, POEXT.COL_VALUE
, DECODE(EGA.ATTR_NAME
, 'ADDRESSCODE'
, (SELECT LEI_INFORMATION1
FROM HR_LOCATION_EXTRA_INFO
WHERE LOCATION_ID = POEXT.COL_VALUE
AND INFORMATION_TYPE = 'CLM_OFFICE_CODE')
, NULL) COL_DESC
FROM (SELECT FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, FCU.APPLICATION_COLUMN_NAME
, FCU.END_USER_COLUMN_NAME ATTR_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE FCU.APPLICATION_ID = 201
AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ADDRESSES'
AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
AND FCU.DISPLAY_FLAG <> 'H' ) EGA
, ( WITH PO_ADDRESS_EXT_UNPIVOT_DATA AS (SELECT PO_HEADER_ID
, DRAFT_ID
, EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, C_EXT_ATTR1 ADDRESS_TYPE
, C_EXT_ATTR5
, C_EXT_ATTR6
, 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 ('ADDRESSES')
AND C_EXT_ATTR1 IN ('SBA_OFFICE'
, 'PRO_ADMIN_OFFICE'
, 'INV_OFFICE'
, 'PAY_OFFICE'
, 'ISSUING_OFFICE'
, 'COTR_OFFICE'
, 'ADMIN_OFFICE'
, 'REQ_OFFICE') ) SELECT PO_HEADER_ID
, DRAFT_ID
, ATTR_GROUP_NAME
, ADDRESS_TYPE
, COL_NAME
, COL_VALUE
FROM PO_ADDRESS_EXT_UNPIVOT_DATA UNPIVOT INCLUDE NULLS (COL_VALUE FOR COL_NAME IN ( C_EXT_ATTR5
, C_EXT_ATTR6
, 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