VIEW: APPS.PO_LINES_EXT_DIFF_V

Source

View Text - Preformatted

SELECT poext.po_header_id pk1_value, poext.draft_id pk2_value, poext.po_line_id pk3_value, null pk4_value, null pk5_value, ega.col_name , poext.col_value, poext.revision_num FROM ( SELECT fcu.descriptive_flex_context_code attr_group_name, fcu.application_column_name , fcu.descriptive_flex_context_code || '_' || Chr(0) || fcu.end_user_column_name col_name, fcu.end_user_column_name attr_name from fnd_descr_flex_column_usages fcu WHERE fcu.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS' AND fcu.application_id = 201 ) ega , ( WITH po_line_ext_unpivot_data as ( SELECT pla.po_header_id, ext.po_line_id, ext.draft_id, egg.descriptive_flex_context_code attr_group_name, ext.revision_num revision_num, 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, 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_lines_all_ext_b_archive ext, ego_fnd_dsc_flx_ctx_ext egg, po_lines_all pla WHERE ext.attr_group_id = egg.attr_group_id AND egg.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS' AND egg.application_id = 201 AND pla.po_line_id = ext.po_line_id AND ext.pk1_value IS NULL AND pla.po_header_id = PO_GEN_DIFF_PKG.getBasePK1 AND ext.draft_id = -1 ) SELECT po_header_id, po_line_id, draft_id, attr_group_name, col_name, col_value, revision_num FROM po_line_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, 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
, POEXT.PO_LINE_ID PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, EGA.COL_NAME
, POEXT.COL_VALUE
, POEXT.REVISION_NUM
FROM ( SELECT FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, FCU.APPLICATION_COLUMN_NAME
, FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE || '_' || CHR(0) || FCU.END_USER_COLUMN_NAME COL_NAME
, FCU.END_USER_COLUMN_NAME ATTR_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE FCU.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_LINE_EXT_ATTRS'
AND FCU.APPLICATION_ID = 201 ) EGA
, ( WITH PO_LINE_EXT_UNPIVOT_DATA AS ( SELECT PLA.PO_HEADER_ID
, EXT.PO_LINE_ID
, EXT.DRAFT_ID
, EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, EXT.REVISION_NUM REVISION_NUM
, 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
, 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_LINES_ALL_EXT_B_ARCHIVE EXT
, EGO_FND_DSC_FLX_CTX_EXT EGG
, PO_LINES_ALL PLA
WHERE EXT.ATTR_GROUP_ID = EGG.ATTR_GROUP_ID
AND EGG.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_LINE_EXT_ATTRS'
AND EGG.APPLICATION_ID = 201
AND PLA.PO_LINE_ID = EXT.PO_LINE_ID
AND EXT.PK1_VALUE IS NULL
AND PLA.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1
AND EXT.DRAFT_ID = -1 ) SELECT PO_HEADER_ID
, PO_LINE_ID
, DRAFT_ID
, ATTR_GROUP_NAME
, COL_NAME
, COL_VALUE
, REVISION_NUM
FROM PO_LINE_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
, 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