eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PON_SGD_LINES_V

Source

View Text - Preformatted


SELECT p_auction_header_id pk1_value, p_line_number pk2_value, null pk3_value, null pk4_value, null pk5_value, col_name , col_value, decode(col_name, 'LINE_TYPE_ID', (select line_type from po_line_types_tl where line_type_id = col_value and language = userenv('lang')), 'CLM_INFO_FLAG', (select meaning from fnd_lookups where lookup_code = col_value and lookup_type = 'YES_NO'), 'ITEM_ID', (select p.item_number|| nvl2(p.item_revision, ', ', '') || p.item_revision || jobs.name from per_jobs_vl jobs, pon_auction_item_prices_all p where jobs.job_id (+) = p.job_id and p.auction_header_id = p_auction_header_id and p.line_number = p_line_number), 'UOM_CODE', (select nvl(unit_of_measure_tl, col_value) from mtl_units_of_measure_tl where uom_code = col_value and language = userenv('lang')), 'CATEGORY_ID', (select concatenated_segments from mtl_categories_kfv where category_id = col_value), 'CLM_CONTRACT_TYPE', (select description from po_lookup_codes where lookup_code = col_value and lookup_type = 'PO_FEDERAL_CONTRACT_TYPES_AMT'), 'CLM_COST_CONSTRAINT', (select description from po_lookup_codes where lookup_code = col_value and lookup_type = 'PO_FEDERAL_COST_CONSTRAINTS'), 'CLM_DELIVERY_EVENT_DATE', nvl2(col_value,(select regexp_substr(col_value,'[^_]+', 1, 1)||' '||(SELECT MEANING FROM fnd_lookup_values_vl flv WHERE flv.lookup_type = 'CLM_PERIOD' AND flv.lookup_code=regexp_substr(col_value,'[^_]+', 1, 2)) ||' '|| (SELECT MEANING FROM fnd_lookup_values_vl flv WHERE flv.lookup_type = 'CLM_DELIVERY_EVENT' AND flv.lookup_code=regexp_substr(col_value,'[^_]+', 1, 3)) from dual),null), 'CLM_POP_EVENT_DATE', nvl2(col_value,(select regexp_substr(col_value,'[^_]+', 1, 1) ||' '|| (SELECT MEANING FROM fnd_lookup_values_vl flv WHERE flv.lookup_type = 'CLM_PERIOD' AND flv.lookup_code=regexp_substr(col_value,'[^_]+', 1, 2)) ||' '||fnd_message.get_string('PO','PO_WF_NOTIF_S_FROM')||' '||fnd_message.get_string('PO','PO_SGD_POP_STDT') FROM dual),null), null) col_desc from ( SELECT paip.auction_header_id p_auction_header_id, paip.line_number p_line_number, to_char(paip.line_type_id) line_type_id, paip.clm_info_flag, to_char(paip.item_id) item_id, paip.item_number, paip.item_revision, paip.item_description, to_char(paip.category_id) category_id, paip.category_name, decode(paip.order_type_lookup_code, 'QUANTITY', paip.uom_code, null) uom_code, to_char(decode(paip.order_type_lookup_code, 'QUANTITY', paip.quantity, null)) quantity, paip.clm_contract_type, paip.clm_cost_constraint, to_char(paip.bid_start_price) bid_start_price, to_char(paip.target_price) target_price, to_char(paip.current_price) current_price, to_char(paip.need_by_date) need_by_date, to_char(paip.need_by_start_date) need_by_start_date, to_char(paip.clm_need_by_date) clm_need_by_date, Nvl2(paip.clm_delivery_event_code, to_char(paip.clm_delivery_period||'_'||paip.clm_delivery_period_uom||'_'||paip.clm_delivery_event_code), NULL) clm_delivery_event_date, Nvl2(paip.clm_pop_duration,To_Char(paip.clm_pop_duration||'_'||paip.clm_pop_duration_uom),NULL) clm_pop_event_date, to_char(paip.clm_option_from_date) clm_option_from_date, to_char(paip.clm_option_to_date) clm_option_to_date, to_char(paip.ship_to_location_id) ship_to_location_id, to_char(paip.unit_target_price) unit_target_price, paip.note_to_bidders FROM pon_auction_item_prices_all paip, pon_auction_headers_all pah, pon_auction_item_prices_all mod_line WHERE paip.auction_header_id = PO_GEN_DIFF_PKG.getBasePK1 and paip.line_number = nvl(PO_GEN_DIFF_PKG.getBasePK2, paip.line_number) and pah.auction_header_id_prev_amend = paip.auction_header_id and pah.auction_header_id = mod_line.auction_header_id and mod_line.line_number = paip.line_number and nvl(mod_line.modified_flag, 'N') = 'Y' ) pon_line_unpivot_data unpivot include nulls ( col_value for col_name in ( LINE_TYPE_ID, CLM_INFO_FLAG, ITEM_ID, ITEM_NUMBER, ITEM_REVISION, ITEM_DESCRIPTION, CATEGORY_ID, CATEGORY_NAME, UOM_CODE, QUANTITY, CLM_CONTRACT_TYPE, CLM_COST_CONSTRAINT, BID_START_PRICE, TARGET_PRICE, CURRENT_PRICE, NEED_BY_DATE, NEED_BY_START_DATE, CLM_NEED_BY_DATE, CLM_DELIVERY_EVENT_DATE, CLM_POP_EVENT_DATE, CLM_OPTION_FROM_DATE, CLM_OPTION_TO_DATE, SHIP_TO_LOCATION_ID, UNIT_TARGET_PRICE, NOTE_TO_BIDDERS ) )

View Text - HTML Formatted

SELECT P_AUCTION_HEADER_ID PK1_VALUE
, P_LINE_NUMBER PK2_VALUE
, NULL PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, COL_NAME
, COL_VALUE
, DECODE(COL_NAME
, 'LINE_TYPE_ID'
, (SELECT LINE_TYPE
FROM PO_LINE_TYPES_TL
WHERE LINE_TYPE_ID = COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'CLM_INFO_FLAG'
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'YES_NO')
, 'ITEM_ID'
, (SELECT P.ITEM_NUMBER|| NVL2(P.ITEM_REVISION
, '
, '
, '') || P.ITEM_REVISION || JOBS.NAME
FROM PER_JOBS_VL JOBS
, PON_AUCTION_ITEM_PRICES_ALL P
WHERE JOBS.JOB_ID (+) = P.JOB_ID
AND P.AUCTION_HEADER_ID = P_AUCTION_HEADER_ID
AND P.LINE_NUMBER = P_LINE_NUMBER)
, 'UOM_CODE'
, (SELECT NVL(UNIT_OF_MEASURE_TL
, COL_VALUE)
FROM MTL_UNITS_OF_MEASURE_TL
WHERE UOM_CODE = COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'CATEGORY_ID'
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_CATEGORIES_KFV
WHERE CATEGORY_ID = COL_VALUE)
, 'CLM_CONTRACT_TYPE'
, (SELECT DESCRIPTION
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_AMT')
, 'CLM_COST_CONSTRAINT'
, (SELECT DESCRIPTION
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_FEDERAL_COST_CONSTRAINTS')
, 'CLM_DELIVERY_EVENT_DATE'
, NVL2(COL_VALUE
, (SELECT REGEXP_SUBSTR(COL_VALUE
, '[^_]+'
, 1
, 1)||' '||(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL FLV
WHERE FLV.LOOKUP_TYPE = 'CLM_PERIOD'
AND FLV.LOOKUP_CODE=REGEXP_SUBSTR(COL_VALUE
, '[^_]+'
, 1
, 2)) ||' '|| (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL FLV
WHERE FLV.LOOKUP_TYPE = 'CLM_DELIVERY_EVENT'
AND FLV.LOOKUP_CODE=REGEXP_SUBSTR(COL_VALUE
, '[^_]+'
, 1
, 3))
FROM DUAL)
, NULL)
, 'CLM_POP_EVENT_DATE'
, NVL2(COL_VALUE
, (SELECT REGEXP_SUBSTR(COL_VALUE
, '[^_]+'
, 1
, 1) ||' '|| (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL FLV
WHERE FLV.LOOKUP_TYPE = 'CLM_PERIOD'
AND FLV.LOOKUP_CODE=REGEXP_SUBSTR(COL_VALUE
, '[^_]+'
, 1
, 2)) ||' '||FND_MESSAGE.GET_STRING('PO'
, 'PO_WF_NOTIF_S_FROM')||' '||FND_MESSAGE.GET_STRING('PO'
, 'PO_SGD_POP_STDT')
FROM DUAL)
, NULL)
, NULL) COL_DESC
FROM ( SELECT PAIP.AUCTION_HEADER_ID P_AUCTION_HEADER_ID
, PAIP.LINE_NUMBER P_LINE_NUMBER
, TO_CHAR(PAIP.LINE_TYPE_ID) LINE_TYPE_ID
, PAIP.CLM_INFO_FLAG
, TO_CHAR(PAIP.ITEM_ID) ITEM_ID
, PAIP.ITEM_NUMBER
, PAIP.ITEM_REVISION
, PAIP.ITEM_DESCRIPTION
, TO_CHAR(PAIP.CATEGORY_ID) CATEGORY_ID
, PAIP.CATEGORY_NAME
, DECODE(PAIP.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, PAIP.UOM_CODE
, NULL) UOM_CODE
, TO_CHAR(DECODE(PAIP.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, PAIP.QUANTITY
, NULL)) QUANTITY
, PAIP.CLM_CONTRACT_TYPE
, PAIP.CLM_COST_CONSTRAINT
, TO_CHAR(PAIP.BID_START_PRICE) BID_START_PRICE
, TO_CHAR(PAIP.TARGET_PRICE) TARGET_PRICE
, TO_CHAR(PAIP.CURRENT_PRICE) CURRENT_PRICE
, TO_CHAR(PAIP.NEED_BY_DATE) NEED_BY_DATE
, TO_CHAR(PAIP.NEED_BY_START_DATE) NEED_BY_START_DATE
, TO_CHAR(PAIP.CLM_NEED_BY_DATE) CLM_NEED_BY_DATE
, NVL2(PAIP.CLM_DELIVERY_EVENT_CODE
, TO_CHAR(PAIP.CLM_DELIVERY_PERIOD||'_'||PAIP.CLM_DELIVERY_PERIOD_UOM||'_'||PAIP.CLM_DELIVERY_EVENT_CODE)
, NULL) CLM_DELIVERY_EVENT_DATE
, NVL2(PAIP.CLM_POP_DURATION
, TO_CHAR(PAIP.CLM_POP_DURATION||'_'||PAIP.CLM_POP_DURATION_UOM)
, NULL) CLM_POP_EVENT_DATE
, TO_CHAR(PAIP.CLM_OPTION_FROM_DATE) CLM_OPTION_FROM_DATE
, TO_CHAR(PAIP.CLM_OPTION_TO_DATE) CLM_OPTION_TO_DATE
, TO_CHAR(PAIP.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID
, TO_CHAR(PAIP.UNIT_TARGET_PRICE) UNIT_TARGET_PRICE
, PAIP.NOTE_TO_BIDDERS
FROM PON_AUCTION_ITEM_PRICES_ALL PAIP
, PON_AUCTION_HEADERS_ALL PAH
, PON_AUCTION_ITEM_PRICES_ALL MOD_LINE
WHERE PAIP.AUCTION_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1
AND PAIP.LINE_NUMBER = NVL(PO_GEN_DIFF_PKG.GETBASEPK2
, PAIP.LINE_NUMBER)
AND PAH.AUCTION_HEADER_ID_PREV_AMEND = PAIP.AUCTION_HEADER_ID
AND PAH.AUCTION_HEADER_ID = MOD_LINE.AUCTION_HEADER_ID
AND MOD_LINE.LINE_NUMBER = PAIP.LINE_NUMBER
AND NVL(MOD_LINE.MODIFIED_FLAG
, 'N') = 'Y' ) PON_LINE_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( LINE_TYPE_ID
, CLM_INFO_FLAG
, ITEM_ID
, ITEM_NUMBER
, ITEM_REVISION
, ITEM_DESCRIPTION
, CATEGORY_ID
, CATEGORY_NAME
, UOM_CODE
, QUANTITY
, CLM_CONTRACT_TYPE
, CLM_COST_CONSTRAINT
, BID_START_PRICE
, TARGET_PRICE
, CURRENT_PRICE
, NEED_BY_DATE
, NEED_BY_START_DATE
, CLM_NEED_BY_DATE
, CLM_DELIVERY_EVENT_DATE
, CLM_POP_EVENT_DATE
, CLM_OPTION_FROM_DATE
, CLM_OPTION_TO_DATE
, SHIP_TO_LOCATION_ID
, UNIT_TARGET_PRICE
, NOTE_TO_BIDDERS ) )