[Home] [Help]
75: ', p_price_tiers_indicator = ' || p_price_tiers_indicator);
76:
77: IF (p_pf_type_allowed = 'NONE') THEN
78: UPDATE
79: pon_bid_item_prices pbip
80: SET
81: pbip.per_unit_price_component = pbip.unit_price,
82: pbip.fixed_amount_component = 0
83: WHERE
84: pbip.bid_number = p_bid_number;
85:
86: ELSE
87: UPDATE
88: pon_bid_item_prices pbip
89: SET
90: pbip.per_unit_price_component = pbip.unit_price +
91: nvl((
92: SELECT
117: WHERE
118: pbip.bid_number = p_bid_number;
119: END IF;
120:
121: print_log (l_api_name, 'Done updating pon_bid_item_prices, now updating pon_bid_shipments');
122:
123: IF (p_price_tiers_indicator = 'QUANTITY_BASED') THEN
124: UPDATE
125: pon_bid_shipments pbs
261: p_login_user_id,
262: pfs.pf_type
263: FROM
264: pon_price_elements pfs,
265: pon_bid_item_prices bid_lines
266: WHERE
267: pfs.auction_header_id = p_auction_header_id
268: AND bid_lines.bid_number = p_bid_number
269: AND bid_lines.line_number = pfs.line_number
490: UPDATE pon_bid_headers bh
491: SET partial_response_flag = 'Y'
492: WHERE bh.bid_number = p_bidNumber
493: AND (EXISTS (select 'x'
494: from pon_bid_item_prices bl,
495: pon_auction_item_prices_all al
496: where bl.bid_number = bh.bid_number
497: and bl.auction_header_id = al.auction_header_id
498: and bl.line_number = al.line_number
502: and al.quantity is not null
503: and bl.quantity is not null)
504: OR
505: EXISTS (select 'x'
506: from pon_bid_item_prices bl,
507: pon_auction_item_prices_all al
508: where bl.bid_number(+) = bh.bid_number
509: and al.auction_header_id = bh.auction_header_id
510: and bl.auction_header_id(+) = al.auction_header_id
518:
519:
520:
521: --
522: -- update pon_bid_item_prices.group_amount
523: -- needed whenever bid price is changed for any group lines
524:
525: PROCEDURE update_group_amount (p_bidNumber IN NUMBER )
526:
527: IS
528: l_api_name CONSTANT VARCHAR2(30) := 'update_group_amount';
529: BEGIN
530: print_log(l_api_name || '.BEGIN', p_bidNumber ||' Begin - update_group_amount');
531: update pon_bid_item_prices bl
532: set group_amount = (select sum(nvl(bl2.quantity, decode(al.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', 1, al.quantity))*bl2.price)
533: from pon_bid_item_prices bl2,
534: pon_auction_item_prices_all al
535: where bl2.auction_header_id = al.auction_header_id
529: BEGIN
530: print_log(l_api_name || '.BEGIN', p_bidNumber ||' Begin - update_group_amount');
531: update pon_bid_item_prices bl
532: set group_amount = (select sum(nvl(bl2.quantity, decode(al.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', 1, al.quantity))*bl2.price)
533: from pon_bid_item_prices bl2,
534: pon_auction_item_prices_all al
535: where bl2.auction_header_id = al.auction_header_id
536: and bl2.line_number = al.line_number
537: and bl2.bid_number = bl.bid_number
685: FROM fnd_documents dc, fnd_documents_tl dt, fnd_attached_documents fndat
686: WHERE fndat.document_id = dt.document_id
687: AND dt.document_id = dc.document_id
688: AND dt.language = userenv('LANG')
689: AND fndat.entity_name IN ('PON_BID_ITEM_PRICES', 'PON_BID_HEADERS')
690: AND fndat.pk1_value = p_auctionHeaderId
691: AND fndat.pk2_value = p_oldBidNum;
692: --
693: SHORT_TEXT number := 1;
928: FROM PON_BID_HEADERS
929: WHERE auction_header_id= p_auctionHeaderId
930: AND bid_number = p_bidNumber;
931: --
932: INSERT INTO pon_bid_item_prices (
933: auction_header_id,
934: auction_line_number,
935: bid_number,
936: line_number,
1025: BID_CURR_MAX_RETAINAGE_AMT,
1026: has_bid_flag,
1027: per_unit_price_component,
1028: fixed_amount_component
1029: FROM pon_bid_item_prices
1030: WHERE auction_header_id = p_auctionHeaderId
1031: AND bid_number = p_bidNumber;
1032: --
1033: INSERT INTO pon_bid_attribute_values (
1221: print_log(l_api_name, 'p_bid_curr_price=' || p_bid_curr_price);
1222: print_log(l_api_name, 'p_publish_date=' || p_publish_date);
1223:
1224:
1225: UPDATE pon_bid_item_prices
1226: SET price = p_price,
1227: bid_currency_trans_price = p_bid_curr_price,
1228: bid_currency_price = p_bid_curr_price,
1229: unit_price = p_price,
1501: AND vendor_site_id = p_vendorSiteId
1502: AND bid_status <> 'DRAFT';
1503:
1504: IF (v_oldBid IS NOT NULL) THEN
1505: UPDATE pon_bid_item_prices bidline
1506: SET rank = (SELECT oldbidline.rank
1507: FROM pon_bid_item_prices oldbidline
1508: WHERE oldbidline.bid_number = v_oldBid
1509: AND oldbidline.line_number= bidline.line_number)
1503:
1504: IF (v_oldBid IS NOT NULL) THEN
1505: UPDATE pon_bid_item_prices bidline
1506: SET rank = (SELECT oldbidline.rank
1507: FROM pon_bid_item_prices oldbidline
1508: WHERE oldbidline.bid_number = v_oldBid
1509: AND oldbidline.line_number= bidline.line_number)
1510: WHERE bidline.bid_number = p_bidNumber
1511: AND bidline.publish_date <> v_publish_date
1775:
1776: -- if line is proxying
1777: IF p_proxyBidLimitPrice IS NOT NULL AND p_price <> p_proxyBidLimitPrice THEN
1778: -- cancel proxy bid for line
1779: UPDATE pon_bid_item_prices
1780: SET proxy_bid_limit_price = price,
1781: bid_currency_limit_price = bid_currency_price,
1782: cancelled_limit_price = p_proxyBidLimitPrice,
1783: publish_date = sysdate,
1813: l_auctionHeaderId pon_auction_headers_all.auction_header_id%TYPE;
1814: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1815: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1816: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1817: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1818: l_price pon_bid_item_prices.price%TYPE;
1819: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1820: --
1821: no_active_bid_error EXCEPTION;
1814: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1815: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1816: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1817: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1818: l_price pon_bid_item_prices.price%TYPE;
1819: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1820: --
1821: no_active_bid_error EXCEPTION;
1822: --
1815: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1816: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1817: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1818: l_price pon_bid_item_prices.price%TYPE;
1819: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1820: --
1821: no_active_bid_error EXCEPTION;
1822: --
1823: CURSOR c_proxyBids (p_bidNumber NUMBER) IS
1825: pbip.line_number,
1826: pbip.price,
1827: pbip.proxy_bid_limit_price
1828: FROM
1829: pon_bid_item_prices pbip,
1830: pon_auction_item_prices_all paip,
1831: pon_auction_headers_all paha
1832: WHERE
1833: pbip.bid_number = p_bidNumber
1905: l_auctionHeaderId pon_auction_headers_all.auction_header_id%TYPE;
1906: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1907: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1908: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1909: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1910: l_price pon_bid_item_prices.price%TYPE;
1911: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1912: --
1913: no_active_bid_error EXCEPTION;
1906: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1907: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1908: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1909: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1910: l_price pon_bid_item_prices.price%TYPE;
1911: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1912: --
1913: no_active_bid_error EXCEPTION;
1914: --
1907: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1908: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1909: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1910: l_price pon_bid_item_prices.price%TYPE;
1911: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1912: --
1913: no_active_bid_error EXCEPTION;
1914: --
1915: BEGIN
1959: --
1960: -- get proxy info
1961: SELECT price, proxy_bid_limit_price
1962: INTO l_price, l_proxyBidLimitPrice
1963: FROM pon_bid_item_prices
1964: WHERE bid_number = l_bidNumber
1965: AND line_number = p_lineNumber;
1966: --
1967: -- cancel proxy on line
2041: -- column is_changed_line_flag. This follows the logic in publish_lines
2042: -- where we set the publish_date on the lines based on this flag.
2043: SELECT paip.line_number
2044: INTO v_negotiation_closed_line_num
2045: FROM pon_bid_item_prices pbip,
2046: pon_auction_item_prices_all paip,
2047: pon_auction_headers_all paha
2048: WHERE paip.auction_header_id = p_auctionHeaderId
2049: AND pbip.auction_header_id = p_auctionHeaderId
2277:
2278:
2279: CURSOR all_lines_cursor IS
2280: SELECT pbip.line_number, pbip.rank, pbh.technical_shortlist_flag
2281: FROM pon_bid_item_prices pbip, pon_bid_headers pbh
2282: WHERE pbh.auction_header_id = p_auctionHeaderId
2283: AND pbip.auction_header_id = pbh.auction_header_id
2284: AND pbh.bid_number = p_bidNumber
2285: AND pbip.bid_number = pbh.bid_number
2366: bidline.bid_currency_price,
2367: bidheader.bid_currency_code,
2368: bidline.first_bid_price,
2369: bidline.proxy_bid_limit_price
2370: FROM pon_bid_item_prices bidline,
2371: pon_bid_headers bidheader
2372: WHERE bidline.auction_header_id = p_auctionHeaderId
2373: AND bidheader.auction_header_id = bidline.auction_header_id
2374: AND bidheader.bid_number = bidline.bid_number
2456: bidline.total_weighted_score,
2457: bidline.bid_number,
2458: bidline.bid_currency_price,
2459: bidheader.bid_currency_code
2460: FROM pon_bid_item_prices bidline,
2461: pon_bid_headers bidheader
2462: WHERE bidline.auction_header_id = p_auctionHeaderId
2463: AND bidheader.auction_header_id = bidline.auction_header_id
2464: AND bidheader.bid_number = bidline.bid_number
2510: FOR bid_item_record IN all_lines_cursor LOOP -- {
2511:
2512: SELECT number_of_bids - (SELECT COUNT(bh.bid_number)
2513: FROM pon_bid_headers bh,
2514: pon_bid_item_prices bip
2515: WHERE bh.auction_header_id = p_auctionHeaderId
2516: AND bh.trading_partner_contact_id = p_tpcId
2517: AND bh.trading_partner_id = p_tpId
2518: AND (bh.bid_status = 'ARCHIVED' OR bh.bid_number = p_bidNumber)
2533: IF (p_rankIndicator = 'NUMBERING' AND (p_ignore_tech_nonshortlist = 'N'
2534: OR (p_ignore_tech_nonshortlist = 'Y' AND
2535: bid_item_record.technical_shortlist_flag = 'Y'))) THEN -- {
2536:
2537: UPDATE pon_bid_item_prices bip
2538: SET rank = rank - 1
2539: WHERE auction_header_id = p_auctionHeaderId
2540: AND line_number = bid_item_record.line_number
2541: AND EXISTS (SELECT 1
2764: --
2765: print_log(l_api_name, 'calling subroutine is_better_proxy_price_by_score');
2766: select count(*) + 1
2767: into v_new_rank
2768: from pon_bid_item_prices ip, pon_bid_headers h
2769: where ip.auction_header_id = h.auction_header_id
2770: and h.auction_header_id = p_auctionHeaderId
2771: and h.bid_number = ip.bid_number
2772: and ip.line_number = p_lineNumber
2796: -- decode (p_scoring_method, 'MULTI_ATTRIBUTE_SCORING', ip.total_weighted_score / ip.price , DECODE(p_auction_type, 'REVERSE', p_price, ip.price));
2797: --
2798: if (v_new_rank < p_oldRank) then
2799: --
2800: update pon_bid_item_prices ip
2801: set rank = rank + 1
2802: where auction_header_id = p_auctionHeaderId
2803: and line_number = p_lineNumber
2804: and exists (select 1
2808: and rank between v_new_rank and p_oldRank;
2809: --
2810: elsif (v_new_rank > p_oldRank) then
2811: --
2812: update pon_bid_item_prices ip
2813: set rank = rank - 1
2814: where auction_header_id = p_auctionHeaderId
2815: and line_number = p_lineNumber
2816: and exists (select 1
2820: and rank between p_oldRank and v_new_rank ;
2821: --
2822: end if;
2823: --
2824: update pon_bid_item_prices
2825: set rank = v_new_rank
2826: where auction_header_id = p_auctionHeaderId
2827: and bid_number = p_bidNumber
2828: and line_number = p_lineNumber;
3301: SET buyer_bid_total = (SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(pbip.quantity, paip.quantity)) *
3302: pbip.price)
3303: -- hack to set bid total to null if at least one line has a quantity of null
3304: + decode(min(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(paip.quantity, -9999))), -9999, NULL, 0) bid_total
3305: FROM pon_bid_item_prices pbip,
3306: pon_auction_item_prices_all paip
3307: WHERE pbip.auction_header_id = pbh.auction_header_id AND
3308: pbip.bid_number = pbh.bid_number AND
3309: pbip.auction_header_id = paip.auction_header_id AND
3549: WHERE auction_header_id = p_auctionHeaderId
3550: AND line_number IN
3551: (SELECT al.line_number
3552: FROM pon_bid_headers bh,
3553: pon_bid_item_prices bl,
3554: pon_auction_item_prices_all al
3555: WHERE bh.bid_number IN (SELECT * FROM TABLE(CAST (g_bidsGenerated AS fnd_table_of_number)))
3556: AND bl.bid_number = bh.bid_number
3557: AND al.auction_header_id = bh.auction_header_id
3685: v_hdrCloseBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
3686: v_hdrNumberOfExtensions pon_auction_headers_all.number_of_extensions%TYPE;
3687:
3688: v_triggerLineCloseBiddingDate pon_auction_item_prices_all.close_bidding_date%TYPE;
3689: v_bidPublishDate pon_bid_item_prices.publish_date%TYPE;
3690:
3691: v_extensionInterval NUMBER;
3692:
3693: l_api_name CONSTANT VARCHAR2(30) := 'auto_extend_negotiation';
3775: v_triggerLineCloseBiddingDate,
3776: v_bidPublishDate
3777: from
3778: pon_auction_item_prices_all a,
3779: pon_bid_item_prices b
3780: where
3781: -- We need to consider every bid generated during this publish - the current bid + all proxy bids
3782: b.bid_number IN (SELECT * FROM TABLE(CAST (g_bidsGenerated AS fnd_table_of_number)))
3783: and a.auction_header_id = p_auctionHeaderId
3960: NVL(bestbid.min_bid_change,0) as best_bid_min_change,
3961: NVL(bestbid.rate,1.0) as best_bid_rate,
3962: NVL(bestbid.number_price_decimals,10) as best_bid_decimals
3963: from pon_bid_headers bestbid,
3964: pon_bid_item_prices biditem,
3965: pon_auction_item_prices_all item
3966: where bestbid.bid_number = item.best_bid_number
3967: and item.auction_header_id = biditem.auction_header_id
3968: and item.line_number = biditem.line_number
4039: -- before the current bid - either thru proxy or otherwise thru another session.
4040: print_log(l_api_name, p_bidNumber || ': update_proxy point 1');
4041:
4042: IF (p_oldBidNumber IS NOT NULL) THEN
4043: UPDATE pon_bid_item_prices newbid
4044: SET (price,
4045: bid_currency_price ,
4046: proxy_bid_limit_price ,
4047: bid_currency_limit_price ,
4050: bid_currency_price,
4051: proxy_bid_limit_price,
4052: bid_currency_limit_price,
4053: publish_date
4054: FROM pon_bid_item_prices prevbid
4055: WHERE prevbid.auction_header_id = newbid.auction_header_id
4056: AND prevbid.bid_number = p_oldBidNumber
4057: AND prevbid.line_number = newbid.line_number)
4058: WHERE newbid.auction_header_id = p_auctionHeaderId
4146: -- added the decode to handle the case when the limit has been reached
4147: -- the original bid becomes a proxy bid because the system has
4148: -- changed the bidder's bid price
4149:
4150: UPDATE pon_bid_item_prices
4151: SET price = v_new_bid_price,
4152: unit_price = price,
4153: bid_currency_price = decode(v_new_bid_price,v_proxy_bid_limit_price,bid_currency_limit_price,
4154: round(v_new_bid_price * v_rate,v_bid_decimals)),
4320: IS
4321: l_api_name CONSTANT VARCHAR2(30) := 'update_and_rerank_group_lines';
4322: CURSOR groups_to_be_reranked(v_publish_date DATE) IS
4323: SELECT groupline.line_number
4324: FROM pon_bid_item_prices groupline,
4325: pon_auction_item_prices_all auctionline
4326: WHERE groupline.bid_number = p_bidNumber
4327: AND groupline.publish_date = v_publish_date
4328: AND auctionline.auction_header_id = groupline.auction_header_id
4331: AND groupline.group_amount IS NOT NULL;
4332:
4333: CURSOR bid_group_cursor(v_line_number NUMBER) IS
4334: SELECT groupline.bid_number
4335: FROM pon_bid_item_prices groupline,
4336: pon_bid_headers groupheader
4337: WHERE groupline.auction_header_id = p_auctionHeaderId
4338: AND groupline.bid_number = groupheader.bid_number
4339: AND (groupheader.bid_status = 'ACTIVE'
4420: AND line_number = t_itm_line_number(x)
4421: AND t_itm_rank(x) = 1;
4422:
4423: -- if the ranking type is NUMBERING, then we need to also
4424: -- set pon_bid_item_prices.rank with the appropriate value
4425: -- we calculated for all group-bids in the above double
4426: -- FOR loops.
4427: IF (p_rankIndicator = 'NUMBERING') THEN
4428:
4426: -- FOR loops.
4427: IF (p_rankIndicator = 'NUMBERING') THEN
4428:
4429: FORALL x in 1..t_itm_bid_number.COUNT
4430: UPDATE pon_bid_item_prices
4431: SET rank = t_itm_rank(x)
4432: WHERE bid_number = t_itm_bid_number(x)
4433: AND line_number = t_itm_line_number(x);
4434:
4449: l_api_name CONSTANT VARCHAR2(30) := 'update_worsened_lines';
4450:
4451: CURSOR bid_items_to_be_reranked(v_publish_date DATE) IS
4452: SELECT bidline.line_number
4453: FROM pon_bid_item_prices bidline,
4454: pon_bid_headers bidheader,
4455: pon_bid_headers bestbidheader,
4456: pon_auction_item_prices_all auctionline
4457: WHERE auctionline.auction_header_id = bidline.auction_header_id
4466: AND bidline.price >= auctionline.best_bid_price;
4467:
4468: CURSOR mas_bid_items_to_be_reranked(v_publish_date DATE) IS
4469: SELECT bidline.line_number
4470: FROM pon_bid_item_prices bidline,
4471: pon_bid_headers bidheader,
4472: pon_bid_headers bestbidheader,
4473: pon_auction_item_prices_all auctionline
4474: WHERE auctionline.auction_header_id = bidline.auction_header_id
4551: bidline.bid_currency_price,
4552: bidheader.bid_currency_code,
4553: bidline.first_bid_price,
4554: bidline.proxy_bid_limit_price
4555: FROM pon_bid_item_prices bidline,
4556: pon_bid_headers bidheader
4557: WHERE bidline.auction_header_id = p_auctionHeaderId
4558: AND bidheader.auction_header_id = bidline.auction_header_id
4559: AND bidheader.bid_number = bidline.bid_number
4621: bidline.total_weighted_score,
4622: bidline.bid_number,
4623: bidline.bid_currency_price,
4624: bidheader.bid_currency_code
4625: FROM pon_bid_item_prices bidline,
4626: pon_bid_headers bidheader
4627: WHERE bidline.auction_header_id = p_auctionHeaderId
4628: AND bidheader.auction_header_id = bidline.auction_header_id
4629: AND bidheader.bid_number = bidline.bid_number
4693: bidline.proxy_bid_limit_price,
4694: bidline.bid_currency_price,
4695: p_bidCurrencyCode,
4696: bidline.first_bid_price
4697: FROM pon_bid_item_prices bidline
4698: WHERE bidline.bid_number = p_bidNumber
4699: AND bidline.line_number = auctionline.line_number)
4700: WHERE auctionline.auction_header_id = p_auctionHeaderId
4701: AND auctionline.group_type <> 'GROUP'
4699: AND bidline.line_number = auctionline.line_number)
4700: WHERE auctionline.auction_header_id = p_auctionHeaderId
4701: AND auctionline.group_type <> 'GROUP'
4702: AND EXISTS (SELECT 'x'
4703: FROM pon_bid_item_prices bidline
4704: WHERE bidline.bid_number = p_bidNumber
4705: AND bidline.line_number = auctionline.line_number
4706: AND bidline.publish_date = p_publishDate)
4707: AND (auctionline.best_bid_number IS NULL
4712: bidline.publish_date,
4713: bestbidline.price,
4714: bestbidline.trigger_bid_number,
4715: bestbidline.publish_date)
4716: FROM pon_bid_item_prices bidline,
4717: pon_bid_item_prices bestbidline
4718: WHERE bidline.bid_number = p_bidNumber
4719: AND bestbidline.bid_number = auctionline.best_bid_number
4720: AND bidline.line_number = auctionline.line_number
4713: bestbidline.price,
4714: bestbidline.trigger_bid_number,
4715: bestbidline.publish_date)
4716: FROM pon_bid_item_prices bidline,
4717: pon_bid_item_prices bestbidline
4718: WHERE bidline.bid_number = p_bidNumber
4719: AND bestbidline.bid_number = auctionline.best_bid_number
4720: AND bidline.line_number = auctionline.line_number
4721: AND bestbidline.line_number = auctionline.line_number),
4736: bidline.price,
4737: bidline.total_weighted_score,
4738: bidline.bid_currency_price,
4739: p_bidCurrencyCode
4740: FROM pon_bid_item_prices bidline
4741: WHERE bidline.bid_number = p_bidNumber
4742: AND bidline.line_number = auctionline.line_number)
4743: WHERE auctionline.auction_header_id = p_auctionHeaderId
4744: AND auctionline.group_type <> 'GROUP'
4742: AND bidline.line_number = auctionline.line_number)
4743: WHERE auctionline.auction_header_id = p_auctionHeaderId
4744: AND auctionline.group_type <> 'GROUP'
4745: AND EXISTS (SELECT 'x'
4746: FROM pon_bid_item_prices bidline
4747: WHERE bidline.bid_number = p_bidNumber
4748: AND bidline.line_number = auctionline.line_number
4749: AND bidline.publish_date = p_publishDate)
4750: AND (auctionline.best_bid_bid_number IS NULL
4757: bestbidline.price,
4758: bestbidline.total_weighted_score,
4759: bestbidline.trigger_bid_number,
4760: bestbidline.publish_date)
4761: FROM pon_bid_item_prices bidline,
4762: pon_bid_item_prices bestbidline
4763: WHERE bidline.bid_number = p_bidNumber
4764: AND bestbidline.bid_number = auctionline.best_bid_bid_number
4765: AND bidline.line_number = auctionline.line_number
4758: bestbidline.total_weighted_score,
4759: bestbidline.trigger_bid_number,
4760: bestbidline.publish_date)
4761: FROM pon_bid_item_prices bidline,
4762: pon_bid_item_prices bestbidline
4763: WHERE bidline.bid_number = p_bidNumber
4764: AND bestbidline.bid_number = auctionline.best_bid_bid_number
4765: AND bidline.line_number = auctionline.line_number
4766: AND bestbidline.line_number = auctionline.line_number),
4794: bidline.price,
4795: bidline.total_weighted_score,
4796: bidline.proxy_bid_flag,
4797: NVL(oldBidline.rank, DEAD_LAST) as old_rank
4798: FROM pon_bid_item_prices bidline,
4799: pon_auction_item_prices_all auctionline,
4800: pon_bid_item_prices oldBidline
4801: WHERE bidline.bid_number = p_bidNumber
4802: AND bidline.publish_date = v_publish_date
4796: bidline.proxy_bid_flag,
4797: NVL(oldBidline.rank, DEAD_LAST) as old_rank
4798: FROM pon_bid_item_prices bidline,
4799: pon_auction_item_prices_all auctionline,
4800: pon_bid_item_prices oldBidline
4801: WHERE bidline.bid_number = p_bidNumber
4802: AND bidline.publish_date = v_publish_date
4803: AND auctionline.auction_header_id = bidline.auction_header_id
4804: AND auctionline.line_number = bidline.line_number
4812: v_new_rank NUMBER,
4813: v_old_rank NUMBER) IS
4814: SELECT bid_number,
4815: rank
4816: FROM pon_bid_item_prices bidline
4817: WHERE auction_header_id = p_auctionHeaderId
4818: AND bid_number <> p_bidNumber
4819: AND line_number = v_line_number
4820: AND EXISTS (SELECT 'x'
4835:
4836: v_counter PLS_INTEGER;
4837: v_newRank NUMBER;
4838: v_oldRank NUMBER;
4839: v_price pon_bid_item_prices.price%TYPE;
4840: v_score pon_bid_item_prices.total_weighted_score%TYPE;
4841: v_proxyFlag pon_bid_item_prices.proxy_bid_flag%TYPE;
4842:
4843:
4836: v_counter PLS_INTEGER;
4837: v_newRank NUMBER;
4838: v_oldRank NUMBER;
4839: v_price pon_bid_item_prices.price%TYPE;
4840: v_score pon_bid_item_prices.total_weighted_score%TYPE;
4841: v_proxyFlag pon_bid_item_prices.proxy_bid_flag%TYPE;
4842:
4843:
4844: BEGIN
4837: v_newRank NUMBER;
4838: v_oldRank NUMBER;
4839: v_price pon_bid_item_prices.price%TYPE;
4840: v_score pon_bid_item_prices.total_weighted_score%TYPE;
4841: v_proxyFlag pon_bid_item_prices.proxy_bid_flag%TYPE;
4842:
4843:
4844: BEGIN
4845:
4856: v_oldRank := rerank_line.old_rank;
4857:
4858: SELECT count(*) + 1
4859: INTO v_newRank
4860: FROM pon_bid_item_prices bidline,
4861: pon_bid_headers bidheader
4862: WHERE bidline.auction_header_id = bidheader.auction_header_id
4863: AND bidheader.auction_header_id = p_auctionHeaderId
4864: AND bidheader.bid_number = bidline.bid_number
4913:
4914: END LOOP;
4915:
4916: FORALL x in 1..t_itm_bid_number.COUNT
4917: UPDATE pon_bid_item_prices
4918: SET rank = t_itm_rank(x)
4919: WHERE bid_number = t_itm_bid_number(x)
4920: AND line_number = t_itm_line_number(x);
4921:
5038: UPDATE pon_auction_item_prices_all
5039: SET number_of_bids = nvl(number_of_bids,0) + 1
5040: WHERE auction_header_id = p_auctionHeaderId
5041: AND line_number IN (SELECT line_number
5042: FROM pon_bid_item_prices
5043: WHERE bid_number = p_bidNumber
5044: AND publish_date = p_publishDate
5045: AND line_number >= p_batchStart
5046: AND line_number <= p_batchEnd);
5389: END IF;
5390:
5391: --
5392: -- Populate the values for per_unit_price_component and
5393: -- fixed_amount_component in pon_bid_item_prices
5394: --
5395: IF (v_batchingRequired) THEN
5396: set_pf_price_components_auto (p_bidNumber,
5397: v_pfTypeAllowed,
5735: l_contract_type pon_auction_headers_all.contract_type%TYPE;
5736: l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
5737: l_pf_type_allowed pon_auction_headers_all.pf_type_allowed%TYPE;
5738:
5739: l_bid_auction_curr_unit_price pon_bid_item_prices.unit_price%TYPE;
5740: l_bid_quantity pon_bid_item_prices.quantity%TYPE;
5741:
5742: l_is_spo_transformed VARCHAR2(1);
5743:
5736: l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
5737: l_pf_type_allowed pon_auction_headers_all.pf_type_allowed%TYPE;
5738:
5739: l_bid_auction_curr_unit_price pon_bid_item_prices.unit_price%TYPE;
5740: l_bid_quantity pon_bid_item_prices.quantity%TYPE;
5741:
5742: l_is_spo_transformed VARCHAR2(1);
5743:
5744:
5784: SELECT unit_price,
5785: quantity
5786: INTO l_bid_auction_curr_unit_price,
5787: l_bid_quantity
5788: FROM pon_bid_item_prices
5789: WHERE bid_number = p_prev_auc_active_bid_number
5790: AND line_number = p_line_number;
5791:
5792: -- assign values to the buyer and supplier pf values
5950: v_best_bid_number pon_auction_item_prices_all.best_bid_number%TYPE;
5951: v_best_bid_bid_number pon_auction_item_prices_all.best_bid_bid_number%TYPE;
5952: v_bestbid_shortlist_flag pon_bid_headers.shortlist_flag%TYPE;
5953: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
5954: t_price pon_bid_item_prices.price%TYPE;
5955: t_quantity pon_bid_item_prices.quantity%TYPE;
5956: t_promised_date pon_bid_item_prices.promised_date%TYPE;
5957: t_bid_number pon_bid_item_prices.bid_number%TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5951: v_best_bid_bid_number pon_auction_item_prices_all.best_bid_bid_number%TYPE;
5952: v_bestbid_shortlist_flag pon_bid_headers.shortlist_flag%TYPE;
5953: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
5954: t_price pon_bid_item_prices.price%TYPE;
5955: t_quantity pon_bid_item_prices.quantity%TYPE;
5956: t_promised_date pon_bid_item_prices.promised_date%TYPE;
5957: t_bid_number pon_bid_item_prices.bid_number%TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5959: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
5952: v_bestbid_shortlist_flag pon_bid_headers.shortlist_flag%TYPE;
5953: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
5954: t_price pon_bid_item_prices.price%TYPE;
5955: t_quantity pon_bid_item_prices.quantity%TYPE;
5956: t_promised_date pon_bid_item_prices.promised_date%TYPE;
5957: t_bid_number pon_bid_item_prices.bid_number%TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5959: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
5960: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
5953: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
5954: t_price pon_bid_item_prices.price%TYPE;
5955: t_quantity pon_bid_item_prices.quantity%TYPE;
5956: t_promised_date pon_bid_item_prices.promised_date%TYPE;
5957: t_bid_number pon_bid_item_prices.bid_number%TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5959: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
5960: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
5961: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
5954: t_price pon_bid_item_prices.price%TYPE;
5955: t_quantity pon_bid_item_prices.quantity%TYPE;
5956: t_promised_date pon_bid_item_prices.promised_date%TYPE;
5957: t_bid_number pon_bid_item_prices.bid_number%TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5959: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
5960: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
5961: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
5962: t_score pon_bid_item_prices.total_weighted_score%TYPE;
5956: t_promised_date pon_bid_item_prices.promised_date%TYPE;
5957: t_bid_number pon_bid_item_prices.bid_number%TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5959: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
5960: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
5961: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
5962: t_score pon_bid_item_prices.total_weighted_score%TYPE;
5963:
5964: v_bid_numbers_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5957: t_bid_number pon_bid_item_prices.bid_number%TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5959: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
5960: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
5961: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
5962: t_score pon_bid_item_prices.total_weighted_score%TYPE;
5963:
5964: v_bid_numbers_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5965: v_rank_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5958: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
5959: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
5960: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
5961: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
5962: t_score pon_bid_item_prices.total_weighted_score%TYPE;
5963:
5964: v_bid_numbers_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5965: v_rank_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5966:
5990: pbh.bid_number
5991: INTO
5992: v_bid_number
5993: FROM
5994: pon_bid_item_prices pbip,
5995: pon_bid_headers pbh
5996: WHERE
5997: pbh.auction_header_id = p_auction_header_id
5998: AND pbip.bid_number = pbh.bid_number
6139: bidheader.bid_currency_code,
6140: bidline.first_bid_price,
6141: bidline.proxy_bid_limit_price
6142: FROM
6143: pon_bid_item_prices bidline,
6144: pon_bid_headers bidheader
6145: WHERE
6146: bidline.auction_header_id = p_auction_header_id
6147: AND bidheader.auction_header_id = bidline.auction_header_id
6282: bidline.bid_number,
6283: bidline.bid_currency_price,
6284: bidheader.bid_currency_code
6285: FROM
6286: pon_bid_item_prices bidline,
6287: pon_bid_headers bidheader
6288: WHERE
6289: bidline.auction_header_id = p_auction_header_id
6290: AND bidheader.auction_header_id = bidline.auction_header_id
6361: v_bid_numbers_bulk,
6362: v_rank_bulk
6363: FROM
6364: pon_bid_headers pbh,
6365: pon_bid_item_prices pbip
6366: WHERE
6367: pbh.auction_header_id = p_auction_header_id
6368: AND pbh.bid_number = pbip.bid_number
6369: AND pbh.bid_status = 'ACTIVE'
6389: || v_bid_numbers_bulk (x));
6390: END IF; --}
6391: END LOOP; -- }
6392:
6393: -- Update pon_bid_item_prices with the new rank
6394: FORALL x IN 1..v_bid_numbers_bulk.COUNT
6395: UPDATE pon_bid_item_prices pbip
6396: SET rank = v_rank_bulk (x)
6397: WHERE bid_number = v_bid_numbers_bulk (x)
6391: END LOOP; -- }
6392:
6393: -- Update pon_bid_item_prices with the new rank
6394: FORALL x IN 1..v_bid_numbers_bulk.COUNT
6395: UPDATE pon_bid_item_prices pbip
6396: SET rank = v_rank_bulk (x)
6397: WHERE bid_number = v_bid_numbers_bulk (x)
6398: AND line_number = auction_item_record.line_number;
6399: