VIEW: APPS.PO_ADDRESSES_DRAFT_DIFF_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 , poext.address_type || '_' || Chr(0) || ega.attr_name col_name , poext.col_value, poext.revision_num 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' ) ega , ( WITH po_address_ext_unpivot_data AS ( SELECT ext.po_header_id , ext.draft_id , egg.descriptive_flex_context_code attr_group_name, phd.revision_num, C_EXT_ATTR1 address_type , 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, UOM_EXT_ATTR1, UOM_EXT_ATTR2, UOM_EXT_ATTR3, UOM_EXT_ATTR4, UOM_EXT_ATTR5, UOM_EXT_ATTR6, UOM_EXT_ATTR7, UOM_EXT_ATTR8, UOM_EXT_ATTR9, UOM_EXT_ATTR10, UOM_EXT_ATTR11, UOM_EXT_ATTR12, UOM_EXT_ATTR13, UOM_EXT_ATTR14, UOM_EXT_ATTR15, UOM_EXT_ATTR16, UOM_EXT_ATTR17, UOM_EXT_ATTR18, UOM_EXT_ATTR19, UOM_EXT_ATTR20, 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, to_char(N_EXT_ATTR4) N_EXT_ATTR4, to_char(N_EXT_ATTR5) N_EXT_ATTR5, to_char(N_EXT_ATTR6) N_EXT_ATTR6, to_char(N_EXT_ATTR7) N_EXT_ATTR7, to_char(N_EXT_ATTR8) N_EXT_ATTR8, to_char(N_EXT_ATTR9) N_EXT_ATTR9, to_char(N_EXT_ATTR10) N_EXT_ATTR10, to_char(N_EXT_ATTR11) N_EXT_ATTR11, to_char(N_EXT_ATTR12) N_EXT_ATTR12, to_char(N_EXT_ATTR13) N_EXT_ATTR13, to_char(N_EXT_ATTR14) N_EXT_ATTR14, to_char(N_EXT_ATTR15) N_EXT_ATTR15, to_char(N_EXT_ATTR16) N_EXT_ATTR16, to_char(N_EXT_ATTR17) N_EXT_ATTR17, to_char(N_EXT_ATTR18) N_EXT_ATTR18, to_char(N_EXT_ATTR19) N_EXT_ATTR19, to_char(N_EXT_ATTR20) N_EXT_ATTR20, to_char(D_EXT_ATTR1) D_EXT_ATTR1, to_char(D_EXT_ATTR2) D_EXT_ATTR2, to_char(D_EXT_ATTR3) D_EXT_ATTR3, to_char(D_EXT_ATTR4) D_EXT_ATTR4, to_char(D_EXT_ATTR5) D_EXT_ATTR5, to_char(D_EXT_ATTR6) D_EXT_ATTR6, to_char(D_EXT_ATTR7) D_EXT_ATTR7, to_char(D_EXT_ATTR8) D_EXT_ATTR8, to_char(D_EXT_ATTR9) D_EXT_ATTR9, to_char(D_EXT_ATTR10) D_EXT_ATTR10, to_char(DATA_LEVEL_ID) DATA_LEVEL_ID, to_char(ext.ATTR_GROUP_ID) ATTR_GROUP_ID, to_char(ext.UDA_TEMPLATE_ID) UDA_TEMPLATE_ID FROM po_headers_all_ext_b ext, ego_fnd_dsc_flx_ctx_ext egg, po_headers_draft_all phd WHERE 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 ext.po_header_id = phd. po_header_id AND ext.draft_id = phd.draft_id AND ext.draft_id <> -1 AND ext.po_header_id = PO_GEN_DIFF_PKG.getModPK1 AND ext.draft_id = PO_GEN_DIFF_PKG.getModPK2 ) SELECT po_header_id, draft_id , attr_group_name , address_type , col_name , col_value, revision_num FROM po_address_ext_unpivot_data unpivot include nulls (col_value FOR col_name IN ( 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, ATTR_GROUP_ID, D_EXT_ATTR1, D_EXT_ATTR2, D_EXT_ATTR3, D_EXT_ATTR4, D_EXT_ATTR5, D_EXT_ATTR6, D_EXT_ATTR7, D_EXT_ATTR8, D_EXT_ATTR9, D_EXT_ATTR10, DATA_LEVEL_ID, N_EXT_ATTR4, N_EXT_ATTR5, N_EXT_ATTR6, N_EXT_ATTR7, N_EXT_ATTR8, N_EXT_ATTR9, N_EXT_ATTR10, N_EXT_ATTR11, N_EXT_ATTR12, N_EXT_ATTR13, N_EXT_ATTR14, N_EXT_ATTR15, N_EXT_ATTR16, N_EXT_ATTR17, N_EXT_ATTR18, N_EXT_ATTR19, N_EXT_ATTR20, UDA_TEMPLATE_ID, UOM_EXT_ATTR1, UOM_EXT_ATTR2, UOM_EXT_ATTR3, UOM_EXT_ATTR4, UOM_EXT_ATTR5, UOM_EXT_ATTR6, UOM_EXT_ATTR7, UOM_EXT_ATTR8, UOM_EXT_ATTR9, UOM_EXT_ATTR10, UOM_EXT_ATTR11, UOM_EXT_ATTR12, UOM_EXT_ATTR13, UOM_EXT_ATTR14, UOM_EXT_ATTR15, UOM_EXT_ATTR16, UOM_EXT_ATTR17, UOM_EXT_ATTR18, UOM_EXT_ATTR19, UOM_EXT_ATTR20) ) ) 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
, POEXT.ADDRESS_TYPE || '_' || CHR(0) || EGA.ATTR_NAME COL_NAME
, POEXT.COL_VALUE
, POEXT.REVISION_NUM
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' ) EGA
, ( WITH PO_ADDRESS_EXT_UNPIVOT_DATA AS ( SELECT EXT.PO_HEADER_ID
, EXT.DRAFT_ID
, EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, PHD.REVISION_NUM
, C_EXT_ATTR1 ADDRESS_TYPE
, 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
, UOM_EXT_ATTR1
, UOM_EXT_ATTR2
, UOM_EXT_ATTR3
, UOM_EXT_ATTR4
, UOM_EXT_ATTR5
, UOM_EXT_ATTR6
, UOM_EXT_ATTR7
, UOM_EXT_ATTR8
, UOM_EXT_ATTR9
, UOM_EXT_ATTR10
, UOM_EXT_ATTR11
, UOM_EXT_ATTR12
, UOM_EXT_ATTR13
, UOM_EXT_ATTR14
, UOM_EXT_ATTR15
, UOM_EXT_ATTR16
, UOM_EXT_ATTR17
, UOM_EXT_ATTR18
, UOM_EXT_ATTR19
, UOM_EXT_ATTR20
, 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
, TO_CHAR(N_EXT_ATTR4) N_EXT_ATTR4
, TO_CHAR(N_EXT_ATTR5) N_EXT_ATTR5
, TO_CHAR(N_EXT_ATTR6) N_EXT_ATTR6
, TO_CHAR(N_EXT_ATTR7) N_EXT_ATTR7
, TO_CHAR(N_EXT_ATTR8) N_EXT_ATTR8
, TO_CHAR(N_EXT_ATTR9) N_EXT_ATTR9
, TO_CHAR(N_EXT_ATTR10) N_EXT_ATTR10
, TO_CHAR(N_EXT_ATTR11) N_EXT_ATTR11
, TO_CHAR(N_EXT_ATTR12) N_EXT_ATTR12
, TO_CHAR(N_EXT_ATTR13) N_EXT_ATTR13
, TO_CHAR(N_EXT_ATTR14) N_EXT_ATTR14
, TO_CHAR(N_EXT_ATTR15) N_EXT_ATTR15
, TO_CHAR(N_EXT_ATTR16) N_EXT_ATTR16
, TO_CHAR(N_EXT_ATTR17) N_EXT_ATTR17
, TO_CHAR(N_EXT_ATTR18) N_EXT_ATTR18
, TO_CHAR(N_EXT_ATTR19) N_EXT_ATTR19
, TO_CHAR(N_EXT_ATTR20) N_EXT_ATTR20
, TO_CHAR(D_EXT_ATTR1) D_EXT_ATTR1
, TO_CHAR(D_EXT_ATTR2) D_EXT_ATTR2
, TO_CHAR(D_EXT_ATTR3) D_EXT_ATTR3
, TO_CHAR(D_EXT_ATTR4) D_EXT_ATTR4
, TO_CHAR(D_EXT_ATTR5) D_EXT_ATTR5
, TO_CHAR(D_EXT_ATTR6) D_EXT_ATTR6
, TO_CHAR(D_EXT_ATTR7) D_EXT_ATTR7
, TO_CHAR(D_EXT_ATTR8) D_EXT_ATTR8
, TO_CHAR(D_EXT_ATTR9) D_EXT_ATTR9
, TO_CHAR(D_EXT_ATTR10) D_EXT_ATTR10
, TO_CHAR(DATA_LEVEL_ID) DATA_LEVEL_ID
, TO_CHAR(EXT.ATTR_GROUP_ID) ATTR_GROUP_ID
, TO_CHAR(EXT.UDA_TEMPLATE_ID) UDA_TEMPLATE_ID
FROM PO_HEADERS_ALL_EXT_B EXT
, EGO_FND_DSC_FLX_CTX_EXT EGG
, PO_HEADERS_DRAFT_ALL PHD
WHERE 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 EXT.PO_HEADER_ID = PHD. PO_HEADER_ID
AND EXT.DRAFT_ID = PHD.DRAFT_ID
AND EXT.DRAFT_ID <> -1
AND EXT.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETMODPK1
AND EXT.DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2 ) SELECT PO_HEADER_ID
, DRAFT_ID
, ATTR_GROUP_NAME
, ADDRESS_TYPE
, COL_NAME
, COL_VALUE
, REVISION_NUM
FROM PO_ADDRESS_EXT_UNPIVOT_DATA UNPIVOT INCLUDE NULLS (COL_VALUE FOR COL_NAME IN ( 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
, ATTR_GROUP_ID
, D_EXT_ATTR1
, D_EXT_ATTR2
, D_EXT_ATTR3
, D_EXT_ATTR4
, D_EXT_ATTR5
, D_EXT_ATTR6
, D_EXT_ATTR7
, D_EXT_ATTR8
, D_EXT_ATTR9
, D_EXT_ATTR10
, DATA_LEVEL_ID
, N_EXT_ATTR4
, N_EXT_ATTR5
, N_EXT_ATTR6
, N_EXT_ATTR7
, N_EXT_ATTR8
, N_EXT_ATTR9
, N_EXT_ATTR10
, N_EXT_ATTR11
, N_EXT_ATTR12
, N_EXT_ATTR13
, N_EXT_ATTR14
, N_EXT_ATTR15
, N_EXT_ATTR16
, N_EXT_ATTR17
, N_EXT_ATTR18
, N_EXT_ATTR19
, N_EXT_ATTR20
, UDA_TEMPLATE_ID
, UOM_EXT_ATTR1
, UOM_EXT_ATTR2
, UOM_EXT_ATTR3
, UOM_EXT_ATTR4
, UOM_EXT_ATTR5
, UOM_EXT_ATTR6
, UOM_EXT_ATTR7
, UOM_EXT_ATTR8
, UOM_EXT_ATTR9
, UOM_EXT_ATTR10
, UOM_EXT_ATTR11
, UOM_EXT_ATTR12
, UOM_EXT_ATTR13
, UOM_EXT_ATTR14
, UOM_EXT_ATTR15
, UOM_EXT_ATTR16
, UOM_EXT_ATTR17
, UOM_EXT_ATTR18
, UOM_EXT_ATTR19
, UOM_EXT_ATTR20) ) ) POEXT
WHERE EGA.ATTR_GROUP_NAME = POEXT.ATTR_GROUP_NAME
AND EGA.APPLICATION_COLUMN_NAME = POEXT.COL_NAME