VIEW: APPS.PON_MI_AUC_SAVINGS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'