[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
509: UPDATE pon_bid_headers bh
510: SET partial_response_flag = 'Y'
511: WHERE bh.bid_number = p_bidNumber
512: AND (EXISTS (select 'x'
513: from pon_bid_item_prices bl,
514: pon_auction_item_prices_all al
515: where bl.bid_number = bh.bid_number
516: and bl.auction_header_id = al.auction_header_id
517: and bl.line_number = al.line_number
522: and bl.quantity is not null
523: and Nvl(al.clm_info_flag,'N') <> 'Y') -- bug#9745944
524: OR
525: EXISTS (select 'x'
526: from pon_bid_item_prices bl,
527: pon_auction_item_prices_all al
528: where bl.bid_number(+) = bh.bid_number
529: and al.auction_header_id = bh.auction_header_id
530: and bl.auction_header_id(+) = al.auction_header_id
539:
540:
541:
542: --
543: -- update pon_bid_item_prices.group_amount
544: -- needed whenever bid price is changed for any group lines
545:
546: PROCEDURE update_group_amount (p_bidNumber IN NUMBER )
547:
548: IS
549: l_api_name CONSTANT VARCHAR2(30) := 'update_group_amount';
550: BEGIN
551: print_log(l_api_name || '.BEGIN', p_bidNumber ||' Begin - update_group_amount');
552: update pon_bid_item_prices bl
553: set group_amount = (select sum(nvl(bl2.quantity, decode(al.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', 1, al.quantity))*bl2.price)
554: from pon_bid_item_prices bl2,
555: pon_auction_item_prices_all al
556: where bl2.auction_header_id = al.auction_header_id
550: BEGIN
551: print_log(l_api_name || '.BEGIN', p_bidNumber ||' Begin - update_group_amount');
552: update pon_bid_item_prices bl
553: set group_amount = (select sum(nvl(bl2.quantity, decode(al.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', 1, al.quantity))*bl2.price)
554: from pon_bid_item_prices bl2,
555: pon_auction_item_prices_all al
556: where bl2.auction_header_id = al.auction_header_id
557: and bl2.line_number = al.line_number
558: and bl2.bid_number = bl.bid_number
708: FROM fnd_documents dc, fnd_documents_tl dt, fnd_attached_documents fndat
709: WHERE fndat.document_id = dt.document_id
710: AND dt.document_id = dc.document_id
711: AND dt.language = userenv('LANG')
712: AND fndat.entity_name IN ('PON_BID_ITEM_PRICES', 'PON_BID_HEADERS')
713: AND fndat.pk1_value = to_char(p_auctionHeaderId)
714: AND fndat.pk2_value = to_char(p_oldBidNum);
715: --
716: SHORT_TEXT number := 1;
951: FROM PON_BID_HEADERS
952: WHERE auction_header_id= p_auctionHeaderId
953: AND bid_number = p_bidNumber;
954: --
955: INSERT INTO pon_bid_item_prices (
956: auction_header_id,
957: auction_line_number,
958: bid_number,
959: line_number,
1048: BID_CURR_MAX_RETAINAGE_AMT,
1049: has_bid_flag,
1050: per_unit_price_component,
1051: fixed_amount_component
1052: FROM pon_bid_item_prices
1053: WHERE auction_header_id = p_auctionHeaderId
1054: AND bid_number = p_bidNumber;
1055: --
1056: INSERT INTO pon_bid_attribute_values (
1244: print_log(l_api_name, 'p_bid_curr_price=' || p_bid_curr_price);
1245: print_log(l_api_name, 'p_publish_date=' || p_publish_date);
1246:
1247:
1248: UPDATE pon_bid_item_prices
1249: SET price = p_price,
1250: bid_currency_trans_price = p_bid_curr_price,
1251: bid_currency_price = p_bid_curr_price,
1252: unit_price = p_price,
1558: AND vendor_site_id = p_vendorSiteId
1559: AND bid_status <> 'DRAFT';
1560:
1561: IF (v_oldBid IS NOT NULL) THEN
1562: UPDATE pon_bid_item_prices bidline
1563: SET rank = (SELECT oldbidline.rank
1564: FROM pon_bid_item_prices oldbidline
1565: WHERE oldbidline.bid_number = v_oldBid
1566: AND oldbidline.line_number= bidline.line_number)
1560:
1561: IF (v_oldBid IS NOT NULL) THEN
1562: UPDATE pon_bid_item_prices bidline
1563: SET rank = (SELECT oldbidline.rank
1564: FROM pon_bid_item_prices oldbidline
1565: WHERE oldbidline.bid_number = v_oldBid
1566: AND oldbidline.line_number= bidline.line_number)
1567: WHERE bidline.bid_number = p_bidNumber
1568: AND bidline.publish_date <> v_publish_date
1832:
1833: -- if line is proxying
1834: IF p_proxyBidLimitPrice IS NOT NULL AND p_price <> p_proxyBidLimitPrice THEN
1835: -- cancel proxy bid for line
1836: UPDATE pon_bid_item_prices
1837: SET proxy_bid_limit_price = price,
1838: bid_currency_limit_price = bid_currency_price,
1839: cancelled_limit_price = p_proxyBidLimitPrice,
1840: publish_date = sysdate,
1870: l_auctionHeaderId pon_auction_headers_all.auction_header_id%TYPE;
1871: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1872: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1873: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1874: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1875: l_price pon_bid_item_prices.price%TYPE;
1876: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1877: --
1878: no_active_bid_error EXCEPTION;
1871: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1872: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1873: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1874: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1875: l_price pon_bid_item_prices.price%TYPE;
1876: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1877: --
1878: no_active_bid_error EXCEPTION;
1879: --
1872: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1873: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1874: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1875: l_price pon_bid_item_prices.price%TYPE;
1876: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1877: --
1878: no_active_bid_error EXCEPTION;
1879: --
1880: CURSOR c_proxyBids (p_bidNumber NUMBER) IS
1882: pbip.line_number,
1883: pbip.price,
1884: pbip.proxy_bid_limit_price
1885: FROM
1886: pon_bid_item_prices pbip,
1887: pon_auction_item_prices_all paip,
1888: pon_auction_headers_all paha
1889: WHERE
1890: pbip.bid_number = p_bidNumber
1962: l_auctionHeaderId pon_auction_headers_all.auction_header_id%TYPE;
1963: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1964: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1965: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1966: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1967: l_price pon_bid_item_prices.price%TYPE;
1968: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1969: --
1970: no_active_bid_error EXCEPTION;
1963: l_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
1964: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1965: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1966: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1967: l_price pon_bid_item_prices.price%TYPE;
1968: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1969: --
1970: no_active_bid_error EXCEPTION;
1971: --
1964: l_auctionStatus pon_auction_headers_all.auction_status%TYPE;
1965: l_closeBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
1966: l_bidNumber pon_bid_item_prices.bid_number%TYPE;
1967: l_price pon_bid_item_prices.price%TYPE;
1968: l_proxyBidLimitPrice pon_bid_item_prices.proxy_bid_limit_price%TYPE;
1969: --
1970: no_active_bid_error EXCEPTION;
1971: --
1972: BEGIN
2016: --
2017: -- get proxy info
2018: SELECT price, proxy_bid_limit_price
2019: INTO l_price, l_proxyBidLimitPrice
2020: FROM pon_bid_item_prices
2021: WHERE bid_number = l_bidNumber
2022: AND line_number = p_lineNumber;
2023: --
2024: -- cancel proxy on line
2102: -- column is_changed_line_flag. This follows the logic in publish_lines
2103: -- where we set the publish_date on the lines based on this flag.
2104: SELECT paip.line_number
2105: INTO v_negotiation_closed_line_num
2106: FROM pon_bid_item_prices pbip,
2107: pon_auction_item_prices_all paip,
2108: pon_auction_headers_all paha
2109: WHERE paip.auction_header_id = p_auctionHeaderId
2110: AND pbip.auction_header_id = p_auctionHeaderId
2348:
2349:
2350: CURSOR all_lines_cursor IS
2351: SELECT pbip.line_number, pbip.rank, pbh.technical_shortlist_flag
2352: FROM pon_bid_item_prices pbip, pon_bid_headers pbh
2353: WHERE pbh.auction_header_id = p_auctionHeaderId
2354: AND pbip.auction_header_id = pbh.auction_header_id
2355: AND pbh.bid_number = p_bidNumber
2356: AND pbip.bid_number = pbh.bid_number
2437: bidline.bid_currency_price,
2438: bidheader.bid_currency_code,
2439: bidline.first_bid_price,
2440: bidline.proxy_bid_limit_price
2441: FROM pon_bid_item_prices bidline,
2442: pon_bid_headers bidheader
2443: WHERE bidline.auction_header_id = p_auctionHeaderId
2444: AND bidheader.auction_header_id = bidline.auction_header_id
2445: AND bidheader.bid_number = bidline.bid_number
2527: bidline.total_weighted_score,
2528: bidline.bid_number,
2529: bidline.bid_currency_price,
2530: bidheader.bid_currency_code
2531: FROM pon_bid_item_prices bidline,
2532: pon_bid_headers bidheader
2533: WHERE bidline.auction_header_id = p_auctionHeaderId
2534: AND bidheader.auction_header_id = bidline.auction_header_id
2535: AND bidheader.bid_number = bidline.bid_number
2581: FOR bid_item_record IN all_lines_cursor LOOP -- {
2582:
2583: SELECT number_of_bids - (SELECT COUNT(bh.bid_number)
2584: FROM pon_bid_headers bh,
2585: pon_bid_item_prices bip
2586: WHERE bh.auction_header_id = p_auctionHeaderId
2587: AND bh.trading_partner_contact_id = p_tpcId
2588: AND bh.trading_partner_id = p_tpId
2589: AND (bh.bid_status = 'ARCHIVED' OR bh.bid_number = p_bidNumber)
2604: IF (p_rankIndicator = 'NUMBERING' AND (p_ignore_tech_nonshortlist = 'N'
2605: OR (p_ignore_tech_nonshortlist = 'Y' AND
2606: bid_item_record.technical_shortlist_flag = 'Y'))) THEN -- {
2607:
2608: UPDATE pon_bid_item_prices bip
2609: SET rank = rank - 1
2610: WHERE auction_header_id = p_auctionHeaderId
2611: AND line_number = bid_item_record.line_number
2612: AND EXISTS (SELECT 1
2839: --
2840: print_log(l_api_name, 'calling subroutine is_better_proxy_price_by_score');
2841: select count(*) + 1
2842: into v_new_rank
2843: from pon_bid_item_prices ip, pon_bid_headers h
2844: where ip.auction_header_id = h.auction_header_id
2845: and h.auction_header_id = p_auctionHeaderId
2846: and h.bid_number = ip.bid_number
2847: and ip.line_number = p_lineNumber
2871: -- decode (p_scoring_method, 'MULTI_ATTRIBUTE_SCORING', ip.total_weighted_score / ip.price , DECODE(p_auction_type, 'REVERSE', p_price, ip.price));
2872: --
2873: if (v_new_rank < p_oldRank) then
2874: --
2875: update pon_bid_item_prices ip
2876: set rank = rank + 1
2877: where auction_header_id = p_auctionHeaderId
2878: and line_number = p_lineNumber
2879: and exists (select 1
2883: and rank between v_new_rank and p_oldRank;
2884: --
2885: elsif (v_new_rank > p_oldRank) then
2886: --
2887: update pon_bid_item_prices ip
2888: set rank = rank - 1
2889: where auction_header_id = p_auctionHeaderId
2890: and line_number = p_lineNumber
2891: and exists (select 1
2895: and rank between p_oldRank and v_new_rank ;
2896: --
2897: end if;
2898: --
2899: update pon_bid_item_prices
2900: set rank = v_new_rank
2901: where auction_header_id = p_auctionHeaderId
2902: and bid_number = p_bidNumber
2903: and line_number = p_lineNumber;
3382: print_log(l_api_name, p_bid_number||': BEGIN set_buyer_bid_total');
3383:
3384: SELECT Count(*)
3385: INTO l_unsol_line_count
3386: FROM pon_bid_item_prices pbip
3387: WHERE pbip.bid_number = p_bid_number
3388: AND pbip.auction_line_number = -1;
3389:
3390: /* Unsolicited Lines Project : If unsolcited lines donot exist, execute current code.
3397: SET buyer_bid_total = (SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(pbip.quantity, paip.quantity)) *
3398: pbip.price)
3399: -- hack to set bid total to null if at least one line has a quantity of null
3400: + decode(min(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(paip.quantity, -9999))), -9999, NULL, 0) bid_total
3401: FROM pon_bid_item_prices pbip,
3402: pon_auction_item_prices_all paip
3403: WHERE pbip.auction_header_id = pbh.auction_header_id AND
3404: pbip.bid_number = pbh.bid_number AND
3405: pbip.auction_header_id = paip.auction_header_id AND
3412: ELSE
3413: SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(pbip.quantity, paip.quantity)) * pbip.price)
3414: + decode(min(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(paip.quantity, -9999))), -9999, NULL, 0) bid_total
3415: INTO l_neg_line_total
3416: FROM pon_bid_item_prices pbip,
3417: pon_auction_item_prices_all paip
3418: WHERE pbip.auction_header_id = p_auction_header_id AND
3419: pbip.bid_number = p_bid_number AND
3420: pbip.auction_header_id = paip.auction_header_id AND
3438: --If Blanket or any non Standard case
3439: --check if there are offers on neg lines
3440: SELECT Count(*)
3441: INTO l_offer_sol_line_total
3442: FROM PON_BID_ITEM_PRICES
3443: WHERE auction_header_id = p_auction_header_id
3444: AND bid_number = p_bid_number
3445: AND auction_line_number <> -1;
3446:
3462: --Calculate unsolicited lines total.
3463: SELECT sum(pbip.quantity * pbip.price)
3464: + decode(Min(nvl(pbip.quantity, -9999)), -9999, NULL, 0) bid_total
3465: INTO l_unsol_line_total
3466: FROM pon_bid_item_prices pbip
3467: WHERE pbip.auction_header_id = p_auction_header_id AND
3468: pbip.bid_number = p_bid_number AND
3469: pbip.auction_line_number = -1 AND
3470: Nvl(pbip.clm_info_flag,'N') = 'N';
3716: WHERE auction_header_id = p_auctionHeaderId
3717: AND line_number IN
3718: (SELECT al.line_number
3719: FROM pon_bid_headers bh,
3720: pon_bid_item_prices bl,
3721: pon_auction_item_prices_all al
3722: WHERE bh.bid_number IN (SELECT * FROM TABLE(CAST (g_bidsGenerated AS fnd_table_of_number)))
3723: AND bl.bid_number = bh.bid_number
3724: AND al.auction_header_id = bh.auction_header_id
3852: v_hdrCloseBiddingDate pon_auction_headers_all.close_bidding_date%TYPE;
3853: v_hdrNumberOfExtensions pon_auction_headers_all.number_of_extensions%TYPE;
3854:
3855: v_triggerLineCloseBiddingDate pon_auction_item_prices_all.close_bidding_date%TYPE;
3856: v_bidPublishDate pon_bid_item_prices.publish_date%TYPE;
3857:
3858: v_extensionInterval NUMBER;
3859:
3860: l_api_name CONSTANT VARCHAR2(30) := 'auto_extend_negotiation';
3942: v_triggerLineCloseBiddingDate,
3943: v_bidPublishDate
3944: from
3945: pon_auction_item_prices_all a,
3946: pon_bid_item_prices b
3947: where
3948: -- We need to consider every bid generated during this publish - the current bid + all proxy bids
3949: b.bid_number IN (SELECT * FROM TABLE(CAST (g_bidsGenerated AS fnd_table_of_number)))
3950: and a.auction_header_id = p_auctionHeaderId
4127: NVL(bestbid.min_bid_change,0) as best_bid_min_change,
4128: NVL(bestbid.rate,1.0) as best_bid_rate,
4129: NVL(bestbid.number_price_decimals,10) as best_bid_decimals
4130: from pon_bid_headers bestbid,
4131: pon_bid_item_prices biditem,
4132: pon_auction_item_prices_all item
4133: where bestbid.bid_number = item.best_bid_number
4134: and item.auction_header_id = biditem.auction_header_id
4135: and item.line_number = biditem.line_number
4206: -- before the current bid - either thru proxy or otherwise thru another session.
4207: print_log(l_api_name, p_bidNumber || ': update_proxy point 1');
4208:
4209: IF (p_oldBidNumber IS NOT NULL) THEN
4210: UPDATE pon_bid_item_prices newbid
4211: SET (price,
4212: bid_currency_price ,
4213: proxy_bid_limit_price ,
4214: bid_currency_limit_price ,
4217: bid_currency_price,
4218: proxy_bid_limit_price,
4219: bid_currency_limit_price,
4220: publish_date
4221: FROM pon_bid_item_prices prevbid
4222: WHERE prevbid.auction_header_id = newbid.auction_header_id
4223: AND prevbid.bid_number = p_oldBidNumber
4224: AND prevbid.line_number = newbid.line_number)
4225: WHERE newbid.auction_header_id = p_auctionHeaderId
4313: -- added the decode to handle the case when the limit has been reached
4314: -- the original bid becomes a proxy bid because the system has
4315: -- changed the bidder's bid price
4316:
4317: UPDATE pon_bid_item_prices
4318: SET price = v_new_bid_price,
4319: unit_price = price,
4320: bid_currency_price = decode(v_new_bid_price,v_proxy_bid_limit_price,bid_currency_limit_price,
4321: round(v_new_bid_price * v_rate,v_bid_decimals)),
4487: IS
4488: l_api_name CONSTANT VARCHAR2(30) := 'update_and_rerank_group_lines';
4489: CURSOR groups_to_be_reranked(v_publish_date DATE) IS
4490: SELECT groupline.line_number
4491: FROM pon_bid_item_prices groupline,
4492: pon_auction_item_prices_all auctionline
4493: WHERE groupline.bid_number = p_bidNumber
4494: AND groupline.publish_date = v_publish_date
4495: AND auctionline.auction_header_id = groupline.auction_header_id
4498: AND groupline.group_amount IS NOT NULL;
4499:
4500: CURSOR bid_group_cursor(v_line_number NUMBER) IS
4501: SELECT groupline.bid_number
4502: FROM pon_bid_item_prices groupline,
4503: pon_bid_headers groupheader
4504: WHERE groupline.auction_header_id = p_auctionHeaderId
4505: AND groupline.bid_number = groupheader.bid_number
4506: AND (groupheader.bid_status = 'ACTIVE'
4587: AND line_number = t_itm_line_number(x)
4588: AND t_itm_rank(x) = 1;
4589:
4590: -- if the ranking type is NUMBERING, then we need to also
4591: -- set pon_bid_item_prices.rank with the appropriate value
4592: -- we calculated for all group-bids in the above double
4593: -- FOR loops.
4594: IF (p_rankIndicator = 'NUMBERING') THEN
4595:
4593: -- FOR loops.
4594: IF (p_rankIndicator = 'NUMBERING') THEN
4595:
4596: FORALL x in 1..t_itm_bid_number.COUNT
4597: UPDATE pon_bid_item_prices
4598: SET rank = t_itm_rank(x)
4599: WHERE bid_number = t_itm_bid_number(x)
4600: AND line_number = t_itm_line_number(x);
4601:
4616: l_api_name CONSTANT VARCHAR2(30) := 'update_worsened_lines';
4617:
4618: CURSOR bid_items_to_be_reranked(v_publish_date DATE) IS
4619: SELECT bidline.line_number
4620: FROM pon_bid_item_prices bidline,
4621: pon_bid_headers bidheader,
4622: pon_bid_headers bestbidheader,
4623: pon_auction_item_prices_all auctionline
4624: WHERE auctionline.auction_header_id = bidline.auction_header_id
4633: AND bidline.price >= auctionline.best_bid_price;
4634:
4635: CURSOR mas_bid_items_to_be_reranked(v_publish_date DATE) IS
4636: SELECT bidline.line_number
4637: FROM pon_bid_item_prices bidline,
4638: pon_bid_headers bidheader,
4639: pon_bid_headers bestbidheader,
4640: pon_auction_item_prices_all auctionline
4641: WHERE auctionline.auction_header_id = bidline.auction_header_id
4718: bidline.bid_currency_price,
4719: bidheader.bid_currency_code,
4720: bidline.first_bid_price,
4721: bidline.proxy_bid_limit_price
4722: FROM pon_bid_item_prices bidline,
4723: pon_bid_headers bidheader
4724: WHERE bidline.auction_header_id = p_auctionHeaderId
4725: AND bidheader.auction_header_id = bidline.auction_header_id
4726: AND bidheader.bid_number = bidline.bid_number
4788: bidline.total_weighted_score,
4789: bidline.bid_number,
4790: bidline.bid_currency_price,
4791: bidheader.bid_currency_code
4792: FROM pon_bid_item_prices bidline,
4793: pon_bid_headers bidheader
4794: WHERE bidline.auction_header_id = p_auctionHeaderId
4795: AND bidheader.auction_header_id = bidline.auction_header_id
4796: AND bidheader.bid_number = bidline.bid_number
4860: bidline.proxy_bid_limit_price,
4861: bidline.bid_currency_price,
4862: p_bidCurrencyCode,
4863: bidline.first_bid_price
4864: FROM pon_bid_item_prices bidline
4865: WHERE bidline.bid_number = p_bidNumber
4866: AND bidline.line_number = auctionline.line_number)
4867: WHERE auctionline.auction_header_id = p_auctionHeaderId
4868: AND auctionline.group_type <> 'GROUP'
4866: AND bidline.line_number = auctionline.line_number)
4867: WHERE auctionline.auction_header_id = p_auctionHeaderId
4868: AND auctionline.group_type <> 'GROUP'
4869: AND EXISTS (SELECT 'x'
4870: FROM pon_bid_item_prices bidline
4871: WHERE bidline.bid_number = p_bidNumber
4872: AND bidline.line_number = auctionline.line_number
4873: AND bidline.publish_date = p_publishDate)
4874: AND (auctionline.best_bid_number IS NULL
4879: bidline.publish_date,
4880: bestbidline.price,
4881: bestbidline.trigger_bid_number,
4882: bestbidline.publish_date)
4883: FROM pon_bid_item_prices bidline,
4884: pon_bid_item_prices bestbidline
4885: WHERE bidline.bid_number = p_bidNumber
4886: AND bestbidline.bid_number = auctionline.best_bid_number
4887: AND bidline.line_number = auctionline.line_number
4880: bestbidline.price,
4881: bestbidline.trigger_bid_number,
4882: bestbidline.publish_date)
4883: FROM pon_bid_item_prices bidline,
4884: pon_bid_item_prices bestbidline
4885: WHERE bidline.bid_number = p_bidNumber
4886: AND bestbidline.bid_number = auctionline.best_bid_number
4887: AND bidline.line_number = auctionline.line_number
4888: AND bestbidline.line_number = auctionline.line_number),
4903: bidline.price,
4904: bidline.total_weighted_score,
4905: bidline.bid_currency_price,
4906: p_bidCurrencyCode
4907: FROM pon_bid_item_prices bidline
4908: WHERE bidline.bid_number = p_bidNumber
4909: AND bidline.line_number = auctionline.line_number)
4910: WHERE auctionline.auction_header_id = p_auctionHeaderId
4911: AND auctionline.group_type <> 'GROUP'
4909: AND bidline.line_number = auctionline.line_number)
4910: WHERE auctionline.auction_header_id = p_auctionHeaderId
4911: AND auctionline.group_type <> 'GROUP'
4912: AND EXISTS (SELECT 'x'
4913: FROM pon_bid_item_prices bidline
4914: WHERE bidline.bid_number = p_bidNumber
4915: AND bidline.line_number = auctionline.line_number
4916: AND bidline.publish_date = p_publishDate)
4917: AND (auctionline.best_bid_bid_number IS NULL
4924: bestbidline.price,
4925: bestbidline.total_weighted_score,
4926: bestbidline.trigger_bid_number,
4927: bestbidline.publish_date)
4928: FROM pon_bid_item_prices bidline,
4929: pon_bid_item_prices bestbidline
4930: WHERE bidline.bid_number = p_bidNumber
4931: AND bestbidline.bid_number = auctionline.best_bid_bid_number
4932: AND bidline.line_number = auctionline.line_number
4925: bestbidline.total_weighted_score,
4926: bestbidline.trigger_bid_number,
4927: bestbidline.publish_date)
4928: FROM pon_bid_item_prices bidline,
4929: pon_bid_item_prices bestbidline
4930: WHERE bidline.bid_number = p_bidNumber
4931: AND bestbidline.bid_number = auctionline.best_bid_bid_number
4932: AND bidline.line_number = auctionline.line_number
4933: AND bestbidline.line_number = auctionline.line_number),
4961: bidline.price,
4962: bidline.total_weighted_score,
4963: bidline.proxy_bid_flag,
4964: NVL(oldBidline.rank, DEAD_LAST) as old_rank
4965: FROM pon_bid_item_prices bidline,
4966: pon_auction_item_prices_all auctionline,
4967: pon_bid_item_prices oldBidline
4968: WHERE bidline.bid_number = p_bidNumber
4969: AND bidline.publish_date = v_publish_date
4963: bidline.proxy_bid_flag,
4964: NVL(oldBidline.rank, DEAD_LAST) as old_rank
4965: FROM pon_bid_item_prices bidline,
4966: pon_auction_item_prices_all auctionline,
4967: pon_bid_item_prices oldBidline
4968: WHERE bidline.bid_number = p_bidNumber
4969: AND bidline.publish_date = v_publish_date
4970: AND auctionline.auction_header_id = bidline.auction_header_id
4971: AND auctionline.line_number = bidline.line_number
4979: v_new_rank NUMBER,
4980: v_old_rank NUMBER) IS
4981: SELECT bid_number,
4982: rank
4983: FROM pon_bid_item_prices bidline
4984: WHERE auction_header_id = p_auctionHeaderId
4985: AND bid_number <> p_bidNumber
4986: AND line_number = v_line_number
4987: AND EXISTS (SELECT 'x'
5002:
5003: v_counter PLS_INTEGER;
5004: v_newRank NUMBER;
5005: v_oldRank NUMBER;
5006: v_price pon_bid_item_prices.price%TYPE;
5007: v_score pon_bid_item_prices.total_weighted_score%TYPE;
5008: v_proxyFlag pon_bid_item_prices.proxy_bid_flag%TYPE;
5009:
5010:
5003: v_counter PLS_INTEGER;
5004: v_newRank NUMBER;
5005: v_oldRank NUMBER;
5006: v_price pon_bid_item_prices.price%TYPE;
5007: v_score pon_bid_item_prices.total_weighted_score%TYPE;
5008: v_proxyFlag pon_bid_item_prices.proxy_bid_flag%TYPE;
5009:
5010:
5011: BEGIN
5004: v_newRank NUMBER;
5005: v_oldRank NUMBER;
5006: v_price pon_bid_item_prices.price%TYPE;
5007: v_score pon_bid_item_prices.total_weighted_score%TYPE;
5008: v_proxyFlag pon_bid_item_prices.proxy_bid_flag%TYPE;
5009:
5010:
5011: BEGIN
5012:
5023: v_oldRank := rerank_line.old_rank;
5024:
5025: SELECT count(*) + 1
5026: INTO v_newRank
5027: FROM pon_bid_item_prices bidline,
5028: pon_bid_headers bidheader
5029: WHERE bidline.auction_header_id = bidheader.auction_header_id
5030: AND bidheader.auction_header_id = p_auctionHeaderId
5031: AND bidheader.bid_number = bidline.bid_number
5080:
5081: END LOOP;
5082:
5083: FORALL x in 1..t_itm_bid_number.COUNT
5084: UPDATE pon_bid_item_prices
5085: SET rank = t_itm_rank(x)
5086: WHERE bid_number = t_itm_bid_number(x)
5087: AND line_number = t_itm_line_number(x);
5088:
5205: UPDATE pon_auction_item_prices_all
5206: SET number_of_bids = nvl(number_of_bids,0) + 1
5207: WHERE auction_header_id = p_auctionHeaderId
5208: AND line_number IN (SELECT line_number
5209: FROM pon_bid_item_prices
5210: WHERE bid_number = p_bidNumber
5211: AND publish_date = p_publishDate
5212: AND line_number >= p_batchStart
5213: AND line_number <= p_batchEnd);
5556: END IF;
5557:
5558: --
5559: -- Populate the values for per_unit_price_component and
5560: -- fixed_amount_component in pon_bid_item_prices
5561: --
5562: IF (v_batchingRequired) THEN
5563: set_pf_price_components_auto (p_bidNumber,
5564: v_pfTypeAllowed,
5903: l_contract_type pon_auction_headers_all.contract_type%TYPE;
5904: l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
5905: l_pf_type_allowed pon_auction_headers_all.pf_type_allowed%TYPE;
5906:
5907: l_bid_auction_curr_unit_price pon_bid_item_prices.unit_price%TYPE;
5908: l_bid_quantity pon_bid_item_prices.quantity%TYPE;
5909:
5910: l_is_spo_transformed VARCHAR2(1);
5911:
5904: l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
5905: l_pf_type_allowed pon_auction_headers_all.pf_type_allowed%TYPE;
5906:
5907: l_bid_auction_curr_unit_price pon_bid_item_prices.unit_price%TYPE;
5908: l_bid_quantity pon_bid_item_prices.quantity%TYPE;
5909:
5910: l_is_spo_transformed VARCHAR2(1);
5911:
5912:
5952: SELECT unit_price,
5953: quantity
5954: INTO l_bid_auction_curr_unit_price,
5955: l_bid_quantity
5956: FROM pon_bid_item_prices
5957: WHERE bid_number = p_prev_auc_active_bid_number
5958: AND line_number = p_line_number;
5959:
5960: -- assign values to the buyer and supplier pf values
6118: v_best_bid_number pon_auction_item_prices_all.best_bid_number%TYPE;
6119: v_best_bid_bid_number pon_auction_item_prices_all.best_bid_bid_number%TYPE;
6120: v_bestbid_shortlist_flag pon_bid_headers.shortlist_flag%TYPE;
6121: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
6122: t_price pon_bid_item_prices.price%TYPE;
6123: t_quantity pon_bid_item_prices.quantity%TYPE;
6124: t_promised_date pon_bid_item_prices.promised_date%TYPE;
6125: t_bid_number pon_bid_item_prices.bid_number%TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6119: v_best_bid_bid_number pon_auction_item_prices_all.best_bid_bid_number%TYPE;
6120: v_bestbid_shortlist_flag pon_bid_headers.shortlist_flag%TYPE;
6121: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
6122: t_price pon_bid_item_prices.price%TYPE;
6123: t_quantity pon_bid_item_prices.quantity%TYPE;
6124: t_promised_date pon_bid_item_prices.promised_date%TYPE;
6125: t_bid_number pon_bid_item_prices.bid_number%TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6127: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
6120: v_bestbid_shortlist_flag pon_bid_headers.shortlist_flag%TYPE;
6121: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
6122: t_price pon_bid_item_prices.price%TYPE;
6123: t_quantity pon_bid_item_prices.quantity%TYPE;
6124: t_promised_date pon_bid_item_prices.promised_date%TYPE;
6125: t_bid_number pon_bid_item_prices.bid_number%TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6127: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
6128: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
6121: v_currentline_group_type pon_auction_item_prices_all.group_type%TYPE;
6122: t_price pon_bid_item_prices.price%TYPE;
6123: t_quantity pon_bid_item_prices.quantity%TYPE;
6124: t_promised_date pon_bid_item_prices.promised_date%TYPE;
6125: t_bid_number pon_bid_item_prices.bid_number%TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6127: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
6128: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
6129: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
6122: t_price pon_bid_item_prices.price%TYPE;
6123: t_quantity pon_bid_item_prices.quantity%TYPE;
6124: t_promised_date pon_bid_item_prices.promised_date%TYPE;
6125: t_bid_number pon_bid_item_prices.bid_number%TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6127: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
6128: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
6129: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
6130: t_score pon_bid_item_prices.total_weighted_score%TYPE;
6124: t_promised_date pon_bid_item_prices.promised_date%TYPE;
6125: t_bid_number pon_bid_item_prices.bid_number%TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6127: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
6128: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
6129: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
6130: t_score pon_bid_item_prices.total_weighted_score%TYPE;
6131:
6132: v_bid_numbers_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
6125: t_bid_number pon_bid_item_prices.bid_number%TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6127: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
6128: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
6129: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
6130: t_score pon_bid_item_prices.total_weighted_score%TYPE;
6131:
6132: v_bid_numbers_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
6133: v_rank_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
6126: t_bid_currency_price pon_bid_item_prices.bid_currency_price%TYPE;
6127: t_bid_currency_code pon_bid_headers.bid_currency_code%TYPE;
6128: t_first_bid_price pon_bid_item_prices.first_bid_price%TYPE;
6129: t_proxy_bid_limit_price pon_bid_item_prices.proxy_bid_limit_price%TYPE;
6130: t_score pon_bid_item_prices.total_weighted_score%TYPE;
6131:
6132: v_bid_numbers_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
6133: v_rank_bulk PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
6134:
6158: pbh.bid_number
6159: INTO
6160: v_bid_number
6161: FROM
6162: pon_bid_item_prices pbip,
6163: pon_bid_headers pbh
6164: WHERE
6165: pbh.auction_header_id = p_auction_header_id
6166: AND pbip.bid_number = pbh.bid_number
6307: bidheader.bid_currency_code,
6308: bidline.first_bid_price,
6309: bidline.proxy_bid_limit_price
6310: FROM
6311: pon_bid_item_prices bidline,
6312: pon_bid_headers bidheader
6313: WHERE
6314: bidline.auction_header_id = p_auction_header_id
6315: AND bidheader.auction_header_id = bidline.auction_header_id
6450: bidline.bid_number,
6451: bidline.bid_currency_price,
6452: bidheader.bid_currency_code
6453: FROM
6454: pon_bid_item_prices bidline,
6455: pon_bid_headers bidheader
6456: WHERE
6457: bidline.auction_header_id = p_auction_header_id
6458: AND bidheader.auction_header_id = bidline.auction_header_id
6529: v_bid_numbers_bulk,
6530: v_rank_bulk
6531: FROM
6532: pon_bid_headers pbh,
6533: pon_bid_item_prices pbip
6534: WHERE
6535: pbh.auction_header_id = p_auction_header_id
6536: AND pbh.bid_number = pbip.bid_number
6537: AND pbh.bid_status = 'ACTIVE'
6557: || v_bid_numbers_bulk (x));
6558: END IF; --}
6559: END LOOP; -- }
6560:
6561: -- Update pon_bid_item_prices with the new rank
6562: FORALL x IN 1..v_bid_numbers_bulk.COUNT
6563: UPDATE pon_bid_item_prices pbip
6564: SET rank = v_rank_bulk (x)
6565: WHERE bid_number = v_bid_numbers_bulk (x)
6559: END LOOP; -- }
6560:
6561: -- Update pon_bid_item_prices with the new rank
6562: FORALL x IN 1..v_bid_numbers_bulk.COUNT
6563: UPDATE pon_bid_item_prices pbip
6564: SET rank = v_rank_bulk (x)
6565: WHERE bid_number = v_bid_numbers_bulk (x)
6566: AND line_number = auction_item_record.line_number;
6567: