VIEW: APPS.PON_SGD_AMD_QTY_PRICE_BRKS_V

Source

View Text - Preformatted

SELECT auction_header_id pk1_value, line_number pk2_value, shipment_number pk3_value, null pk4_value, null pk5_value, col_name , col_value, null col_desc from ( SELECT pas.auction_header_id, pas.line_number, pas.shipment_number, to_char(pas.quantity) quantity, to_char(pas.max_quantity) max_quantity, to_char(pas.price) price, to_char(pas.ship_to_organization_id) ship_to_organization_id, to_char(pas.ship_to_location_id) ship_to_location_id, to_char(pas.effective_start_date) effective_start_date, to_char(pas.effective_end_date) effective_end_date FROM pon_auction_shipments_all pas WHERE pas.auction_header_id = PO_GEN_DIFF_PKG.getModPK1 and pas.line_number = nvl(PO_GEN_DIFF_PKG.getModPK2, pas.line_number) and pas.shipment_number = nvl(PO_GEN_DIFF_PKG.getModPK3, pas.shipment_number) and pas.shipment_type IN ('QUANTITY BASED', 'PRICE BREAK' ) and EXISTS (select 1 from pon_auction_shipments_all where auction_header_id = PO_GEN_DIFF_PKG.getBasePK1 and line_number = pas.line_number and shipment_number = pas.shipment_number) ) pon_price_break_unpivot_data unpivot include nulls ( col_value for col_name in ( QUANTITY, MAX_QUANTITY, PRICE, SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE ) )

View Text - HTML Formatted

SELECT AUCTION_HEADER_ID PK1_VALUE
, LINE_NUMBER PK2_VALUE
, SHIPMENT_NUMBER PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, COL_NAME
, COL_VALUE
, NULL COL_DESC
FROM ( SELECT PAS.AUCTION_HEADER_ID
, PAS.LINE_NUMBER
, PAS.SHIPMENT_NUMBER
, TO_CHAR(PAS.QUANTITY) QUANTITY
, TO_CHAR(PAS.MAX_QUANTITY) MAX_QUANTITY
, TO_CHAR(PAS.PRICE) PRICE
, TO_CHAR(PAS.SHIP_TO_ORGANIZATION_ID) SHIP_TO_ORGANIZATION_ID
, TO_CHAR(PAS.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID
, TO_CHAR(PAS.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
, TO_CHAR(PAS.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
FROM PON_AUCTION_SHIPMENTS_ALL PAS
WHERE PAS.AUCTION_HEADER_ID = PO_GEN_DIFF_PKG.GETMODPK1
AND PAS.LINE_NUMBER = NVL(PO_GEN_DIFF_PKG.GETMODPK2
, PAS.LINE_NUMBER)
AND PAS.SHIPMENT_NUMBER = NVL(PO_GEN_DIFF_PKG.GETMODPK3
, PAS.SHIPMENT_NUMBER)
AND PAS.SHIPMENT_TYPE IN ('QUANTITY BASED'
, 'PRICE BREAK' )
AND EXISTS (SELECT 1
FROM PON_AUCTION_SHIPMENTS_ALL
WHERE AUCTION_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1
AND LINE_NUMBER = PAS.LINE_NUMBER
AND SHIPMENT_NUMBER = PAS.SHIPMENT_NUMBER) ) PON_PRICE_BREAK_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( QUANTITY
, MAX_QUANTITY
, PRICE
, SHIP_TO_ORGANIZATION_ID
, SHIP_TO_LOCATION_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE ) )