VIEW: APPS.PON_SGD_HEADERS_V

Source

View Text - Preformatted

SELECT auction_header_id pk1_value, null pk2_value, null pk3_value, null pk4_value, null pk5_value, col_name , col_value, decode(col_name, 'SOLICITATION_TYPE', (select meaning from fnd_lookup_values where lookup_code = col_value and lookup_type = 'PON_SOLICITATION_TYPE' and language = userenv('lang')), 'PROJECT_ID', (select segment1 from pa_projects_all where project_id = col_value), 'PAYMENT_TERMS_ID', (select name from ap_terms where term_id = col_value), 'CARRIER_CODE', (select orgf.description from financials_system_params_all fsp, org_freight orgf where nvl(fsp.org_id, -9999)= nvl(p_org_id, -9999) and orgf.organization_id = fsp.inventory_organization_id and orgf.freight_code = col_value and orgf.language = userenv('lang')), 'FREIGHT_TERMS_CODE', (select meaning from fnd_lookup_values where lookup_code = col_value and lookup_type = 'FREIGHT TERMS' and language = userenv('lang')), null) col_desc from ( SELECT pah.auction_header_id, pah.auction_title, pah.solicitation_type, to_char(pah.project_id) project_id, pah.abstract_details, to_char(pah.payment_terms_id) payment_terms_id, pah.carrier_code, pah.freight_terms_code, to_char(pah.ship_to_location_id) ship_to_location_id, to_char(pah.bill_to_location_id) bill_to_location_id, pah.currency_code, pah.note_to_bidders, to_char(pah.close_bidding_date) close_bidding_date, to_char(pah.award_by_date) award_by_date, pah.manual_close_flag, pah.award_approval_flag, pah.display_best_price_blind_flag, to_char(pah.no_of_copies) no_of_copies, pah.org_id p_org_id FROM pon_auction_headers_all pah WHERE pah.auction_header_id = PO_GEN_DIFF_PKG.getBasePK1 ) pon_header_unpivot_data unpivot include nulls ( col_value for col_name in ( AUCTION_TITLE, SOLICITATION_TYPE, PROJECT_ID, ABSTRACT_DETAILS, PAYMENT_TERMS_ID, CARRIER_CODE, FREIGHT_TERMS_CODE, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID, CURRENCY_CODE, NOTE_TO_BIDDERS, CLOSE_BIDDING_DATE, AWARD_BY_DATE, MANUAL_CLOSE_FLAG, AWARD_APPROVAL_FLAG, DISPLAY_BEST_PRICE_BLIND_FLAG, NO_OF_COPIES ) )

View Text - HTML Formatted

SELECT AUCTION_HEADER_ID PK1_VALUE
, NULL PK2_VALUE
, NULL PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, COL_NAME
, COL_VALUE
, DECODE(COL_NAME
, 'SOLICITATION_TYPE'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PON_SOLICITATION_TYPE'
AND LANGUAGE = USERENV('LANG'))
, 'PROJECT_ID'
, (SELECT SEGMENT1
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = COL_VALUE)
, 'PAYMENT_TERMS_ID'
, (SELECT NAME
FROM AP_TERMS
WHERE TERM_ID = COL_VALUE)
, 'CARRIER_CODE'
, (SELECT ORGF.DESCRIPTION
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
, ORG_FREIGHT ORGF
WHERE NVL(FSP.ORG_ID
, -9999)= NVL(P_ORG_ID
, -9999)
AND ORGF.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID
AND ORGF.FREIGHT_CODE = COL_VALUE
AND ORGF.LANGUAGE = USERENV('LANG'))
, 'FREIGHT_TERMS_CODE'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'FREIGHT TERMS'
AND LANGUAGE = USERENV('LANG'))
, NULL) COL_DESC
FROM ( SELECT PAH.AUCTION_HEADER_ID
, PAH.AUCTION_TITLE
, PAH.SOLICITATION_TYPE
, TO_CHAR(PAH.PROJECT_ID) PROJECT_ID
, PAH.ABSTRACT_DETAILS
, TO_CHAR(PAH.PAYMENT_TERMS_ID) PAYMENT_TERMS_ID
, PAH.CARRIER_CODE
, PAH.FREIGHT_TERMS_CODE
, TO_CHAR(PAH.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID
, TO_CHAR(PAH.BILL_TO_LOCATION_ID) BILL_TO_LOCATION_ID
, PAH.CURRENCY_CODE
, PAH.NOTE_TO_BIDDERS
, TO_CHAR(PAH.CLOSE_BIDDING_DATE) CLOSE_BIDDING_DATE
, TO_CHAR(PAH.AWARD_BY_DATE) AWARD_BY_DATE
, PAH.MANUAL_CLOSE_FLAG
, PAH.AWARD_APPROVAL_FLAG
, PAH.DISPLAY_BEST_PRICE_BLIND_FLAG
, TO_CHAR(PAH.NO_OF_COPIES) NO_OF_COPIES
, PAH.ORG_ID P_ORG_ID
FROM PON_AUCTION_HEADERS_ALL PAH
WHERE PAH.AUCTION_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1 ) PON_HEADER_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( AUCTION_TITLE
, SOLICITATION_TYPE
, PROJECT_ID
, ABSTRACT_DETAILS
, PAYMENT_TERMS_ID
, CARRIER_CODE
, FREIGHT_TERMS_CODE
, SHIP_TO_LOCATION_ID
, BILL_TO_LOCATION_ID
, CURRENCY_CODE
, NOTE_TO_BIDDERS
, CLOSE_BIDDING_DATE
, AWARD_BY_DATE
, MANUAL_CLOSE_FLAG
, AWARD_APPROVAL_FLAG
, DISPLAY_BEST_PRICE_BLIND_FLAG
, NO_OF_COPIES ) )