VIEW: APPS.PON_SGD_AMD_LINES_UDA_V

Source

View Text - Preformatted

SELECT ponext.auction_header_id pk1_value, ponext.line_number pk2_value, null pk3_value, null pk4_value, null pk5_value, ega.col_name , ponext.col_value, decode(ega.attr_name, 'UOM', (select unit_of_measure from mtl_units_of_measure_vl where uom_code = ponext.col_value), 'MDAPS_MAIS', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_MDAPS/MAIS' and lookup_code = ponext.col_value), 'PRGM_CODE', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_PROGRAM_CODES' and lookup_code = ponext.col_value), 'FSC_PSC', (select meaning from fnd_lookup_values where lookup_type IN ('FV_FSC_TYPE', 'FV_PSC_TYPE') and lookup_code = ponext.col_value and language = userenv('lang')), 'NAICS', (select meaning from fnd_lookup_values where lookup_type = 'FV_NAICS_TYPE' and lookup_code = ponext.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 = ponext.col_value), 'INSP_RESP', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_INSPECTION_RESP' and lookup_code = ponext.col_value), 'INSP_LOC', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_INSPECTION_LOC' and lookup_code = ponext.col_value), 'HAZMAT', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_HAZMAT' and lookup_code = ponext.col_value), 'FOB', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_FOB_VALUES' and lookup_code = ponext.col_value), 'SHIP_MODE', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_SHIPMENT_MODES' and lookup_code = ponext.col_value), 'PREC_MTL_CODE', (select displayed_field from po_lookup_codes where lookup_type = 'PO_CLM_PRECIOUS_METAL_CODE' and lookup_code = ponext.col_value), 'ACCP_LOC',(SELECT displayed_field FROM po_lookup_codes WHERE lookup_type = 'PO_CLM_INSPECTION_LOC' AND lookup_code = ponext.col_value), 'INSP_ADD_LOC', (SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL WHERE location_id=ponext.col_value AND Nvl(inactive_date , SYSDATE + 1 ) > SYSDATE), 'INSP_ADD_CODE', (SELECT LEI_INFORMATION1 from HR_LOCATION_EXTRA_INFO WHERE LOCATION_ID=ponext.col_value AND information_type = 'CLM_OFFICE_CODE'), 'ACCP_ADD_LOC', (SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL WHERE location_id=ponext.col_value AND Nvl(inactive_date , SYSDATE + 1 ) > SYSDATE), 'ACCP_ADD_CODE', (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 , 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 = 'PON_AUC_PRICES_EXT_ATTRS' and fcu.display_flag <> 'H' ) ega , ( With pon_line_ext_unpivot_data as ( select ext.auction_header_id, ext.line_number, 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_ATTR12, C_EXT_ATTR13, 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 from pon_auction_item_prices_ext_b ext, pon_auction_item_prices_all paip, ego_fnd_dsc_flx_ctx_ext egg where ext.auction_header_id = PO_GEN_DIFF_PKG.getModPK1 and ext.line_number = nvl(PO_GEN_DIFF_PKG.getModPK2, ext.line_number) and ext.attr_group_id = egg.attr_group_id and egg.descriptive_flexfield_name = 'PON_AUC_PRICES_EXT_ATTRS' and egg.application_id = 201 and egg.descriptive_flex_context_code IN ('ADD_ITEM_INFO', 'FED_CUST_DESG', 'INSPECT_INFO', 'SHIP_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 paip.auction_header_id = ext.auction_header_id and paip.line_number = ext.line_number and nvl(paip.modified_flag, 'N') = 'Y' ) select auction_header_id, line_number, attr_group_id, attr_group_name, col_name, col_value from pon_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_ATTR12, C_EXT_ATTR13, N_EXT_ATTR1, N_EXT_ATTR2, N_EXT_ATTR3, N_EXT_ATTR4) ) ) 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
, PONEXT.LINE_NUMBER PK2_VALUE
, NULL PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, EGA.COL_NAME
, PONEXT.COL_VALUE
, DECODE(EGA.ATTR_NAME
, 'UOM'
, (SELECT UNIT_OF_MEASURE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = PONEXT.COL_VALUE)
, 'MDAPS_MAIS'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_MDAPS/MAIS'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'PRGM_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_PROGRAM_CODES'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'FSC_PSC'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE IN ('FV_FSC_TYPE'
, 'FV_PSC_TYPE')
AND LOOKUP_CODE = PONEXT.COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'NAICS'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'FV_NAICS_TYPE'
AND LOOKUP_CODE = PONEXT.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 = PONEXT.COL_VALUE)
, 'INSP_RESP'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_RESP'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'INSP_LOC'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_LOC'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'HAZMAT'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_HAZMAT'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'FOB'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_FOB_VALUES'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'SHIP_MODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_SHIPMENT_MODES'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'PREC_MTL_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_PRECIOUS_METAL_CODE'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'ACCP_LOC'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_LOC'
AND LOOKUP_CODE = PONEXT.COL_VALUE)
, 'INSP_ADD_LOC'
, (SELECT LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID=PONEXT.COL_VALUE
AND NVL(INACTIVE_DATE
, SYSDATE + 1 ) > SYSDATE)
, 'INSP_ADD_CODE'
, (SELECT LEI_INFORMATION1
FROM HR_LOCATION_EXTRA_INFO
WHERE LOCATION_ID=PONEXT.COL_VALUE
AND INFORMATION_TYPE = 'CLM_OFFICE_CODE')
, 'ACCP_ADD_LOC'
, (SELECT LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID=PONEXT.COL_VALUE
AND NVL(INACTIVE_DATE
, SYSDATE + 1 ) > SYSDATE)
, 'ACCP_ADD_CODE'
, (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
, 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 = 'PON_AUC_PRICES_EXT_ATTRS'
AND FCU.DISPLAY_FLAG <> 'H' ) EGA
, ( WITH PON_LINE_EXT_UNPIVOT_DATA AS ( SELECT EXT.AUCTION_HEADER_ID
, EXT.LINE_NUMBER
, 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_ATTR12
, C_EXT_ATTR13
, 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
FROM PON_AUCTION_ITEM_PRICES_EXT_B EXT
, PON_AUCTION_ITEM_PRICES_ALL PAIP
, EGO_FND_DSC_FLX_CTX_EXT EGG
WHERE EXT.AUCTION_HEADER_ID = PO_GEN_DIFF_PKG.GETMODPK1
AND EXT.LINE_NUMBER = NVL(PO_GEN_DIFF_PKG.GETMODPK2
, EXT.LINE_NUMBER)
AND EXT.ATTR_GROUP_ID = EGG.ATTR_GROUP_ID
AND EGG.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_PRICES_EXT_ATTRS'
AND EGG.APPLICATION_ID = 201
AND EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('ADD_ITEM_INFO'
, 'FED_CUST_DESG'
, 'INSPECT_INFO'
, 'SHIP_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 PAIP.AUCTION_HEADER_ID = EXT.AUCTION_HEADER_ID
AND PAIP.LINE_NUMBER = EXT.LINE_NUMBER
AND NVL(PAIP.MODIFIED_FLAG
, 'N') = 'Y' ) SELECT AUCTION_HEADER_ID
, LINE_NUMBER
, ATTR_GROUP_ID
, ATTR_GROUP_NAME
, COL_NAME
, COL_VALUE
FROM PON_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_ATTR12
, C_EXT_ATTR13
, N_EXT_ATTR1
, N_EXT_ATTR2
, N_EXT_ATTR3
, N_EXT_ATTR4) ) ) PONEXT
WHERE EGA.ATTR_GROUP_NAME = PONEXT.ATTR_GROUP_NAME
AND EGA.APPLICATION_COLUMN_NAME = PONEXT.COL_NAME