VIEW: APPS.PO_SGD_MOD_ADDRESSES_UDA_V

Source

View Text - Preformatted

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.getModPK1 and ext.draft_id = PO_GEN_DIFF_PKG.getModPK2 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

View Text - HTML Formatted

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.GETMODPK1
AND EXT.DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2
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