View: PON_MI_AUC_SAVINGS_V

Product:	PON - Sourcing
Description:	This view is used by the Buyer Auction Savings report. It contains data for all Awarded Auctions summarized by Category.
Implementation/DBA Data:	APPS.PON_MI_AUC_SAVINGS_V

View Text

SELECT AUC.TRADING_PARTNER_ID BUYER_ID
, AUC.TRADING_PARTNER_NAME BUYER_NAME
, BH.TRADING_PARTNER_ID SELLER_ID
, BH.TRADING_PARTNER_NAME SELLER_NAME
, BIDITM.AWARD_DATE COMPLETED_DATE
, AUC.CURRENCY_CODE CURRENCY_CODE
, BIDITM.CATEGORY_ID CATEGORY_ID
, AUC.AUCTION_HEADER_ID AUCTION_NUMBER
, AUC.AUCTION_TITLE AUCTION_TITLE
, AUC.TRADING_PARTNER_CONTACT_ID BUYER_CONTACT_ID
, AUC.TRADING_PARTNER_CONTACT_NAME BUYER_CONTACT_NAME
, DECODE(AUCITM.CURRENT_PRICE
, NULL
, 0
, (AUCITM.CURRENT_PRICE-BIDITM.AWARD_PRICE)) * BIDITM.AWARD_QUANTITY SAVINGS
, NVL( AUCITM.CURRENT_PRICE
, BIDITM.AWARD_PRICE ) * BIDITM.AWARD_QUANTITY INITIAL_BID
, BIDITM.AWARD_PRICE PRICE
, BIDITM.AWARD_QUANTITY AWARD_QUANTITY
, BIDITM.ITEM_DESCRIPTION ITEM_DESCRIPTION
, AUCITM.ITEM_NUMBER
, AUCITM.ITEM_REVISION
, AD.INTERNAL_NAME
FROM PON_AUCTION_HEADERS AUC
, PON_AUCTION_ITEM_PRICES_ALL AUCITM
, PON_BID_ITEM_PRICES BIDITM
, PON_BID_HEADERS BH
, PON_AUC_DOCTYPES AD
WHERE AUCITM.AUCTION_HEADER_ID = AUC.AUCTION_HEADER_ID
AND AUCITM.GROUP_TYPE <> 'LOT_LINE'
AND AUCITM.GROUP_TYPE <> 'GROUP'
AND AUC.AWARD_STATUS = 'COMPLETED'
AND BIDITM.AUCTION_HEADER_ID = AUC.AUCTION_HEADER_ID
AND AUC.CONTRACT_TYPE <> 'BLANKET'
AND AUC.CONTRACT_TYPE <> 'CONTRACT'
AND BH.BID_NUMBER = BIDITM.BID_NUMBER
AND AUC.AUCTION_HEADER_ID = BH.AUCTION_HEADER_ID
AND BIDITM.LINE_NUMBER = AUCITM.LINE_NUMBER
AND BIDITM.AWARD_STATUS = 'AWARDED'
AND AUC.DOCTYPE_ID = AD.DOCTYPE_ID (+)
AND (((AD.INTERNAL_NAME IS NULL)
AND (AUC.AUCTION_TYPE = 'REVERSE')) OR (AD.INTERNAL_NAME IN ('BUYER_AUCTION'
, 'REQUEST_FOR_QUOTE')))
AND AUCITM.GROUP_TYPE <> 'LOT_LINE'
AND AUCITM.GROUP_TYPE <> 'GROUP'

Columns