VIEW: APPS.PO_SGD_LINES_UDA_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, DECODE(ega.attr_name, 'UOM', (SELECT unit_of_measure FROM mtl_units_of_measure_vl WHERE uom_code = poext.col_value ), 'MDAPS_MAIS', (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type = 'PO_CLM_MDAPS/MAIS' AND lookup_code = poext.col_value ), 'PRGM_CODE', (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type = 'PO_CLM_PROGRAM_CODES' AND lookup_code = poext.col_value ), 'FSC_PSC', (SELECT meaning FROM fnd_lookup_values WHERE lookup_type IN ('FV_FSC_TYPE', 'FV_PSC_TYPE') AND lookup_code = poext.col_value AND language = userenv('lang') ), 'NAICS', (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'FV_NAICS_TYPE' AND lookup_code = poext.col_value AND language = userenv('lang') ), 'CUST_PRJ_CODE', (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type = 'PO_CLM_CUSTOMER_PROJECT_CODE' AND lookup_code = poext.col_value ), 'INSP_RESP', (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type = 'PO_CLM_INSPECTION_RESP' AND lookup_code = poext.col_value ), 'INSP_LOC', (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type = 'PO_CLM_INSPECTION_LOC' AND lookup_code = poext.col_value ), 'INSP_ADD_LOC', (SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL WHERE location_id =poext.col_value AND NVL(inactive_date , SYSDATE + 1 ) > SYSDATE ), 'INSP_ADD_CODE', (SELECT LEI_INFORMATION1 FROM HR_LOCATION_EXTRA_INFO WHERE LOCATION_ID =poext.col_value AND information_type = 'CLM_OFFICE_CODE' ), 'ACCP_LOC', (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type = 'PO_CLM_INSPECTION_LOC' AND lookup_code = poext.col_value ), 'ACCP_ADD_LOC', (SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL WHERE location_id =poext.col_value AND NVL(inactive_date , SYSDATE + 1 ) > SYSDATE ), 'ACCP_ADD_CODE', (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 , upper(fcu.descriptive_flex_context_code || '_' || fcu.end_user_column_name) col_name, upper(fcu.end_user_column_name) attr_name from fnd_descr_flex_column_usages fcu where fcu.application_id = 201 and fcu.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS' and fcu.display_flag <> 'H' ) ega , ( With po_line_ext_unpivot_data as ( select pl.po_header_id, ext.po_line_id, ext.draft_id, ext.attr_group_id, egg.descriptive_flex_context_code attr_group_name, 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, 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) as N_EXT_ATTR4, to_char(N_EXT_ATTR5) as N_EXT_ATTR5, to_char(N_EXT_ATTR6) as N_EXT_ATTR6, to_char(N_EXT_ATTR7) as N_EXT_ATTR7, to_char(N_EXT_ATTR8) as N_EXT_ATTR8, to_char(N_EXT_ATTR9) as N_EXT_ATTR9, to_char(N_EXT_ATTR10) as N_EXT_ATTR10, to_char(N_EXT_ATTR11) as N_EXT_ATTR11, to_char(N_EXT_ATTR20) as N_EXT_ATTR20 from po_lines_all_ext_b ext, ego_fnd_dsc_flx_ctx_ext egg, po_lines_all pl where ext.pk1_value IS NULL and ext.draft_id = PO_GEN_DIFF_PKG.getBasePK2 and ext.po_line_id = pl.po_line_id and pl.po_header_id = po_gen_diff_pkg.getBasePK1 and pl.po_line_id = nvl(PO_GEN_DIFF_PKG.getBasePK3,pl.po_line_id) and ext.attr_group_id = egg.attr_group_id and egg.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS' and egg.application_id = 201 and egg.descriptive_flex_context_code IN ('ADD_ITEM_INFO', 'FED_CUST_DESG', 'INSPECT_INFO', 'AMT_CPFF_IN', 'AMT_AF_IN', 'AMT_FF_IN', 'AMT_CS_IN', 'AMT_FAF_IN', 'AMT_FPR_IN', 'AMT_TMA_IN', 'AMT_CPIF_IN', 'AMT_FLE_IN', 'AMT_CPAF_IN', 'AMT_FIST_IN', 'AMT_LH_IN', 'AMT_FRR_IN', 'AMT_CC_IN', 'AMT_FIFT_IN', 'AMT_TM_IN', 'AMT_ODC_IN', 'AMT_FEPA_IN', 'AMT_CPFF_IR', 'AMT_AF_IR', 'AMT_FF_IR', 'AMT_CS_IR', 'AMT_FAF_IR', 'AMT_FPR_IR', 'AMT_TMA_IR', 'AMT_CPIF_IR', 'AMT_FLE_IR', 'AMT_CPAF_IR', 'AMT_FIST_IR', 'AMT_LH_IR', 'AMT_FRR_IR', 'AMT_CC_IR', 'AMT_FIFT_IR', 'AMT_TM_IR', 'AMT_ODC_IR', 'AMT_FEPA_IR', 'AMT_CPFF_IDQ', 'AMT_AF_IDQ', 'AMT_FF_IDQ', 'AMT_CS_IDQ', 'AMT_FAF_IDQ', 'AMT_FPR_IDQ', 'AMT_CPIF_IDQ', 'AMT_FLE_IDQ', 'AMT_CPAF_IDQ', 'AMT_FIST_IDQ', 'AMT_LH_IDQ', 'AMT_FRR_IDQ', 'AMT_CC_IDQ', 'AMT_FIFT_IDQ', 'AMT_TM_IDQ', 'AMT_ODC_IDQ', 'AMT_FEPA_IDQ', 'AMT_CPFF_IIQ', 'AMT_AF_IIQ', 'AMT_FF_IIQ', 'AMT_CS_IIQ', 'AMT_FAF_IIQ', 'AMT_FPR_IIQ', 'AMT_TMA_IIQ', 'AMT_CPIF_IIQ', 'AMT_FLE_IIQ', 'AMT_CPAF_IIQ', 'AMT_FIST_IIQ', 'AMT_LH_IIQ', 'AMT_FRR_IIQ', 'AMT_CC_IIQ', 'AMT_FIFT_IIQ', 'AMT_TM_IIQ', 'AMT_ODC_IIQ', 'AMT_FEPA_IIQ') and EXISTS (select 1 from po_lines_draft_all where draft_id = PO_GEN_DIFF_PKG.getModPK2 and po_line_id = pl.po_line_id and change_status IN ('UPDATE')) ) select po_header_id,po_line_id, draft_id, attr_group_id, attr_group_name, col_name, col_value 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, N_EXT_ATTR1, N_EXT_ATTR2, N_EXT_ATTR3, 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_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
, DECODE(EGA.ATTR_NAME
, 'UOM'
, (SELECT UNIT_OF_MEASURE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = POEXT.COL_VALUE )
, 'MDAPS_MAIS'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_MDAPS/MAIS'
AND LOOKUP_CODE = POEXT.COL_VALUE )
, 'PRGM_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_PROGRAM_CODES'
AND LOOKUP_CODE = POEXT.COL_VALUE )
, 'FSC_PSC'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE IN ('FV_FSC_TYPE'
, 'FV_PSC_TYPE')
AND LOOKUP_CODE = POEXT.COL_VALUE
AND LANGUAGE = USERENV('LANG') )
, 'NAICS'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'FV_NAICS_TYPE'
AND LOOKUP_CODE = POEXT.COL_VALUE
AND LANGUAGE = USERENV('LANG') )
, 'CUST_PRJ_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
AND LOOKUP_CODE = POEXT.COL_VALUE )
, 'INSP_RESP'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_RESP'
AND LOOKUP_CODE = POEXT.COL_VALUE )
, 'INSP_LOC'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_LOC'
AND LOOKUP_CODE = POEXT.COL_VALUE )
, 'INSP_ADD_LOC'
, (SELECT LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID =POEXT.COL_VALUE
AND NVL(INACTIVE_DATE
, SYSDATE + 1 ) > SYSDATE )
, 'INSP_ADD_CODE'
, (SELECT LEI_INFORMATION1
FROM HR_LOCATION_EXTRA_INFO
WHERE LOCATION_ID =POEXT.COL_VALUE
AND INFORMATION_TYPE = 'CLM_OFFICE_CODE' )
, 'ACCP_LOC'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_LOC'
AND LOOKUP_CODE = POEXT.COL_VALUE )
, 'ACCP_ADD_LOC'
, (SELECT LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID =POEXT.COL_VALUE
AND NVL(INACTIVE_DATE
, SYSDATE + 1 ) > SYSDATE )
, 'ACCP_ADD_CODE'
, (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
, UPPER(FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE || '_' || FCU.END_USER_COLUMN_NAME) COL_NAME
, UPPER(FCU.END_USER_COLUMN_NAME) ATTR_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE FCU.APPLICATION_ID = 201
AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_LINE_EXT_ATTRS'
AND FCU.DISPLAY_FLAG <> 'H' ) EGA
, ( WITH PO_LINE_EXT_UNPIVOT_DATA AS ( SELECT PL.PO_HEADER_ID
, EXT.PO_LINE_ID
, EXT.DRAFT_ID
, EXT.ATTR_GROUP_ID
, EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, 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
, 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) AS N_EXT_ATTR4
, TO_CHAR(N_EXT_ATTR5) AS N_EXT_ATTR5
, TO_CHAR(N_EXT_ATTR6) AS N_EXT_ATTR6
, TO_CHAR(N_EXT_ATTR7) AS N_EXT_ATTR7
, TO_CHAR(N_EXT_ATTR8) AS N_EXT_ATTR8
, TO_CHAR(N_EXT_ATTR9) AS N_EXT_ATTR9
, TO_CHAR(N_EXT_ATTR10) AS N_EXT_ATTR10
, TO_CHAR(N_EXT_ATTR11) AS N_EXT_ATTR11
, TO_CHAR(N_EXT_ATTR20) AS N_EXT_ATTR20
FROM PO_LINES_ALL_EXT_B EXT
, EGO_FND_DSC_FLX_CTX_EXT EGG
, PO_LINES_ALL PL
WHERE EXT.PK1_VALUE IS NULL
AND EXT.DRAFT_ID = PO_GEN_DIFF_PKG.GETBASEPK2
AND EXT.PO_LINE_ID = PL.PO_LINE_ID
AND PL.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1
AND PL.PO_LINE_ID = NVL(PO_GEN_DIFF_PKG.GETBASEPK3
, PL.PO_LINE_ID)
AND EXT.ATTR_GROUP_ID = EGG.ATTR_GROUP_ID
AND EGG.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_LINE_EXT_ATTRS'
AND EGG.APPLICATION_ID = 201
AND EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('ADD_ITEM_INFO'
, 'FED_CUST_DESG'
, 'INSPECT_INFO'
, 'AMT_CPFF_IN'
, 'AMT_AF_IN'
, 'AMT_FF_IN'
, 'AMT_CS_IN'
, 'AMT_FAF_IN'
, 'AMT_FPR_IN'
, 'AMT_TMA_IN'
, 'AMT_CPIF_IN'
, 'AMT_FLE_IN'
, 'AMT_CPAF_IN'
, 'AMT_FIST_IN'
, 'AMT_LH_IN'
, 'AMT_FRR_IN'
, 'AMT_CC_IN'
, 'AMT_FIFT_IN'
, 'AMT_TM_IN'
, 'AMT_ODC_IN'
, 'AMT_FEPA_IN'
, 'AMT_CPFF_IR'
, 'AMT_AF_IR'
, 'AMT_FF_IR'
, 'AMT_CS_IR'
, 'AMT_FAF_IR'
, 'AMT_FPR_IR'
, 'AMT_TMA_IR'
, 'AMT_CPIF_IR'
, 'AMT_FLE_IR'
, 'AMT_CPAF_IR'
, 'AMT_FIST_IR'
, 'AMT_LH_IR'
, 'AMT_FRR_IR'
, 'AMT_CC_IR'
, 'AMT_FIFT_IR'
, 'AMT_TM_IR'
, 'AMT_ODC_IR'
, 'AMT_FEPA_IR'
, 'AMT_CPFF_IDQ'
, 'AMT_AF_IDQ'
, 'AMT_FF_IDQ'
, 'AMT_CS_IDQ'
, 'AMT_FAF_IDQ'
, 'AMT_FPR_IDQ'
, 'AMT_CPIF_IDQ'
, 'AMT_FLE_IDQ'
, 'AMT_CPAF_IDQ'
, 'AMT_FIST_IDQ'
, 'AMT_LH_IDQ'
, 'AMT_FRR_IDQ'
, 'AMT_CC_IDQ'
, 'AMT_FIFT_IDQ'
, 'AMT_TM_IDQ'
, 'AMT_ODC_IDQ'
, 'AMT_FEPA_IDQ'
, 'AMT_CPFF_IIQ'
, 'AMT_AF_IIQ'
, 'AMT_FF_IIQ'
, 'AMT_CS_IIQ'
, 'AMT_FAF_IIQ'
, 'AMT_FPR_IIQ'
, 'AMT_TMA_IIQ'
, 'AMT_CPIF_IIQ'
, 'AMT_FLE_IIQ'
, 'AMT_CPAF_IIQ'
, 'AMT_FIST_IIQ'
, 'AMT_LH_IIQ'
, 'AMT_FRR_IIQ'
, 'AMT_CC_IIQ'
, 'AMT_FIFT_IIQ'
, 'AMT_TM_IIQ'
, 'AMT_ODC_IIQ'
, 'AMT_FEPA_IIQ')
AND EXISTS (SELECT 1
FROM PO_LINES_DRAFT_ALL
WHERE DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2
AND PO_LINE_ID = PL.PO_LINE_ID
AND CHANGE_STATUS IN ('UPDATE')) ) SELECT PO_HEADER_ID
, PO_LINE_ID
, DRAFT_ID
, ATTR_GROUP_ID
, ATTR_GROUP_NAME
, COL_NAME
, COL_VALUE
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
, N_EXT_ATTR1
, N_EXT_ATTR2
, N_EXT_ATTR3
, 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_ATTR20) ) ) POEXT
WHERE EGA.ATTR_GROUP_NAME = POEXT.ATTR_GROUP_NAME
AND EGA.APPLICATION_COLUMN_NAME = POEXT.COL_NAME