VIEW: APPS.PON_SGD_ADDRESSES_UDA_V

Source

View Text - Preformatted

SELECT ponext.auction_header_id pk1_value, null pk2_value, null pk3_value, null pk4_value, null pk5_value, upper(ponext.address_type || '_' || ega.attr_name) col_name , ponext.col_value, decode(ega.attr_name, 'addresscode', (select lei_information1 from hr_location_extra_info where location_id = ponext.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 = 'PON_AUC_HDRS_EXT_ATTRS' and fcu.display_flag <> 'H' ) ega , ( With pon_address_ext_unpivot_data as ( select auction_header_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 pon_auction_headers_ext_b ext, ego_fnd_dsc_flx_ctx_ext egg where ext.auction_header_id = PO_GEN_DIFF_PKG.getBasePK1 and ext.attr_group_id = egg.attr_group_id and egg.descriptive_flexfield_name = 'PON_AUC_HDRS_EXT_ATTRS' and egg.application_id = 201 and egg.descriptive_flex_context_code IN ('addresses') and c_ext_attr1 IN ('PRO_ADMIN_OFFICE', 'ISSUING_OFFICE', 'COTR_OFFICE', 'REQ_OFFICE') ) select auction_header_id, attr_group_name, address_type, col_name, col_value from pon_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) ) ) ponext where ega.attr_group_name = ponext.attr_group_name and ega.application_column_name = ponext.col_name

View Text - HTML Formatted

SELECT PONEXT.AUCTION_HEADER_ID PK1_VALUE
, NULL PK2_VALUE
, NULL PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, UPPER(PONEXT.ADDRESS_TYPE || '_' || EGA.ATTR_NAME) COL_NAME
, PONEXT.COL_VALUE
, DECODE(EGA.ATTR_NAME
, 'ADDRESSCODE'
, (SELECT LEI_INFORMATION1
FROM HR_LOCATION_EXTRA_INFO
WHERE LOCATION_ID = PONEXT.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 = 'PON_AUC_HDRS_EXT_ATTRS'
AND FCU.DISPLAY_FLAG <> 'H' ) EGA
, ( WITH PON_ADDRESS_EXT_UNPIVOT_DATA AS ( SELECT AUCTION_HEADER_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 PON_AUCTION_HEADERS_EXT_B EXT
, EGO_FND_DSC_FLX_CTX_EXT EGG
WHERE EXT.AUCTION_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1
AND EXT.ATTR_GROUP_ID = EGG.ATTR_GROUP_ID
AND EGG.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_HDRS_EXT_ATTRS'
AND EGG.APPLICATION_ID = 201
AND EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('ADDRESSES')
AND C_EXT_ATTR1 IN ('PRO_ADMIN_OFFICE'
, 'ISSUING_OFFICE'
, 'COTR_OFFICE'
, 'REQ_OFFICE') ) SELECT AUCTION_HEADER_ID
, ATTR_GROUP_NAME
, ADDRESS_TYPE
, COL_NAME
, COL_VALUE
FROM PON_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) ) ) PONEXT
WHERE EGA.ATTR_GROUP_NAME = PONEXT.ATTR_GROUP_NAME
AND EGA.APPLICATION_COLUMN_NAME = PONEXT.COL_NAME