[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_HEADERS_PKG
Source
1 PACKAGE BODY PON_AUCTION_HEADERS_PKG AS
2 -- $Header: PONAUCHB.pls 120.48.12010000.4 2009/01/08 11:27:48 amundhra ship $
3 --
4
5 FUNCTION validate_price_precision(p_number IN NUMBER, p_precision IN NUMBER) RETURN BOOLEAN;
6 FUNCTION validate_currency_precision(p_number IN NUMBER, p_precision IN NUMBER) RETURN BOOLEAN;
7
8 --
9 -- LOGGING FEATURE
10 --
11 -- global variables used for logging
12 --
13 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
14 g_pkg_name CONSTANT VARCHAR2(50) := 'auctionHeadersPkg';
15 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
16
17 -- (Raja) global variables used to store the bid numbers generated per publish
18 g_bidsGenerated fnd_table_of_number;
19 g_bidsGeneratedCount NUMBER;
20 --
21 --private helper procedure for logging
22 PROCEDURE print_log(p_module IN VARCHAR2,
23 p_message IN VARCHAR2)
24 IS
25 BEGIN
26
27 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
28 FND_LOG.string(log_level => FND_LOG.level_statement,
29 module => g_module_prefix || p_module,
30 message => p_message);
31 END IF;
32
33
34 END;
35
36 -- FUNCTION BETTER_PRICE
37 --
38 -- this function returns whether x_price1 is better than x_price2
39 -- i.e. in reverse auction if (x_price1 < x_price2)
40 -- i.e. in forward auction if (x_price1 > x_price2)
41 --
42 FUNCTION better_price(x_auction_type VARCHAR2,
43 x_price1 IN NUMBER,
44 x_price2 IN NUMBER)
45 RETURN BOOLEAN IS
46 BEGIN
47
48 if (x_auction_type = 'REVERSE') then
49 return x_price1 < x_price2;
50 else
51 return x_price1 > x_price2;
52 end if;
53 END;
54
55
56 --========================================================================
57 -- PROCEDURE : set_pf_price_components PRIVATE
58 -- set_pf_price_components_auto PRIVATE
59 -- PARAMETERS:
60 -- VERSION : current version 1.x
61 -- initial version 1.0
62 -- COMMENT :
63 -- This procedure will be used to populate columns that store
64 -- components of a response price on a line and on its price
65 -- breaks based on the price factors that apply to the line.
66 --========================================================================
67 PROCEDURE set_pf_price_components (p_bid_number IN NUMBER,
68 p_pf_type_allowed IN VARCHAR2,
69 p_price_tiers_indicator IN VARCHAR2) IS
70 l_api_name CONSTANT VARCHAR2(30) := 'add_hidden_price_factors';
71 BEGIN
72
73 print_log (l_api_name, 'p_bid_number = ' || p_bid_number ||
74 ', p_pf_type_allowed = ' || p_pf_type_allowed ||
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
93 SUM(
94 DECODE (pbpe.pricing_basis,
95 'PER_UNIT',
96 pbpe.auction_currency_value,
97 'PERCENTAGE',
98 pbpe.auction_currency_value / 100 * pbip.unit_price, 0))
99 FROM
100 pon_bid_price_elements pbpe
101 WHERE
102 pbpe.bid_number = pbip.bid_number
103 AND pbpe.line_number = pbip.line_number
104 AND pbpe.pricing_basis IN ('PER_UNIT', 'PERCENTAGE')
105 AND pbpe.sequence_number <> -10), 0),
106 pbip.fixed_amount_component =
107 nvl((
108 SELECT
109 SUM(pbpe.auction_currency_value)
110 FROM
111 pon_bid_price_elements pbpe
112 WHERE
113 pbpe.bid_number = pbip.bid_number
114 AND pbpe.line_number = pbip.line_number
115 AND pbpe.pricing_basis = 'FIXED_AMOUNT'
116 AND pbpe.sequence_number <> -10), 0)
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
126 SET
127 pbs.per_unit_price_component = pbs.unit_price +
128 nvl((
129 SELECT SUM(DECODE(pbpe.pricing_basis,
130 'PER_UNIT', pbpe.auction_currency_value,
131 'PERCENTAGE', pbpe.auction_currency_value / 100 * pbs.unit_price, 0))
132 FROM
133 pon_bid_price_elements pbpe
134 WHERE
135 pbpe.bid_number = pbs.bid_number
136 AND pbpe.line_number = pbs.line_number
137 AND pbpe.pricing_basis IN ('PER_UNIT', 'PERCENTAGE')
138 AND pbpe.sequence_number <> -10), 0)
139 WHERE
140 pbs.bid_number = p_bid_number;
141 END IF;
142
143 print_log (l_api_name, 'END PROCEDURE');
144 END;
145
146 --========================================================================
147 -- PROCEDURE : set_pf_price_components PRIVATE
148 -- set_pf_price_components_auto PRIVATE
149 -- PARAMETERS:
150 -- VERSION : current version 1.x
151 -- initial version 1.0
152 -- COMMENT :
153 -- This procedure will be used to populate columns that store
154 -- components of a response price on a line and on its price
155 -- breaks based on the price factors that apply to the line.
156 --========================================================================
157 PROCEDURE set_pf_price_components_auto(p_bidNumber IN NUMBER,
158 p_pfTypeAllowed IN VARCHAR2,
159 p_priceTiersIndicator IN VARCHAR2)
160 IS
161 PRAGMA AUTONOMOUS_TRANSACTION;
162
163 l_api_name CONSTANT VARCHAR2(30) := 'set_pf_price_components_auto';
164 BEGIN
165
166 print_log(l_api_name, p_bidNumber || ' - BEGIN set_pf_price_components_auto AUTONOMOUS');
167
168 set_pf_price_components(p_bidNumber,
169 p_pfTypeAllowed,
170 p_priceTiersIndicator);
171
172 commit;
173 print_log(l_api_name, p_bidNumber || ' - set_pf_price_components_auto: committed!');
174 print_log(l_api_name, p_bidNumber || ' - END set_pf_price_components_auto AUTONOMOUS');
175
176 END set_pf_price_components_auto;
177
178 --========================================================================
179 -- PROCEDURE : add_hidden_price_factors PRIVATE
180 -- add_hidden_price_factors_auto PRIVATE
181 -- PARAMETERS:
182 -- VERSION : current version 1.x
183 -- initial version 1.0
184 -- COMMENT : copies any price factors that are applicable to a supplier/site
185 -- but cannot be displayed to the supplier/site
186 -- from the negotiation lines to the response lines
187 --
188 -- the only types of price factors that may not be displayed to a supplier/site
189 -- are buyer price factors and the Line Price price factor
190 -- as a result, those are the only price factors considered in the procedure below
191 --========================================================================
192
193 PROCEDURE add_hidden_price_factors(p_bid_number IN NUMBER,
194 p_auction_header_id IN NUMBER,
195 p_supplier_view_type IN VARCHAR2,
196 p_trading_partner_id IN NUMBER,
197 p_vendor_site_id IN NUMBER,
198 p_login_user_id IN NUMBER) IS
199 l_supplier_sequence_number NUMBER;
200 l_currency_rate NUMBER;
201 l_api_name CONSTANT VARCHAR2(30) := 'add_hidden_price_factors';
202 BEGIN
203
204 print_log(l_api_name, p_bid_number || ' - BEGIN add_hidden_price_factors');
205
206 -- determine the sequence number for the supplier/site
207 BEGIN
208 SELECT sequence
209 INTO l_supplier_sequence_number
210 FROM pon_bidding_parties
211 WHERE
212 auction_header_id = p_auction_header_id
213 AND trading_partner_id = p_trading_partner_id
214 AND vendor_site_id = p_vendor_site_id;
215 EXCEPTION
216 WHEN no_data_found THEN
217 l_supplier_sequence_number := -1;
218 END;
219
220 -- if the sequence number cannot be determined for the supplier/site
221 -- it means that the supplier/site was not invited to respond to the negotiation
222 -- as a result, there will be no buyer price factors applicable to the supplier/site
223 IF l_supplier_sequence_number <> -1 THEN
224 -- determine the currency rate for the response
225 SELECT rate
226 INTO l_currency_rate
227 FROM pon_bid_headers
228 WHERE bid_number = p_bid_number;
229
230 -- 1) add the Line Price price factor to each bid line that has an applicable buyer factor
231 -- if the supplier view type of the auction is UNTRANSFORMED
232 IF p_supplier_view_type = 'UNTRANSFORMED' THEN
233 INSERT INTO
234 pon_bid_price_elements (
235 bid_number,
236 line_number,
237 price_element_type_id,
238 auction_header_id,
239 pricing_basis,
240 auction_currency_value,
241 bid_currency_value,
242 sequence_number,
243 creation_date,
244 created_by,
245 last_update_date,
246 last_updated_by,
247 pf_type
248 )
249 SELECT
250 p_bid_number,
251 pfs.line_number,
252 pfs.price_element_type_id,
253 pfs.auction_header_id,
254 pfs.pricing_basis,
255 bid_lines.unit_price,
256 bid_lines.bid_currency_unit_price,
257 pfs.sequence_number,
258 SYSDATE,
259 p_login_user_id,
260 SYSDATE,
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
270 AND pfs.price_element_type_id = -10
271 AND EXISTS (SELECT NULL
272 FROM pon_pf_supplier_values pf_values
273 WHERE
274 pf_values.auction_header_id = bid_lines.auction_header_id
275 AND pf_values.line_number = bid_lines.line_number
276 AND pf_values.supplier_seq_number = l_supplier_sequence_number
277 AND NVL(pf_values.value, 0) <> 0);
278 END IF;
279
280 -- 2) add those buyer price factors that have not been added yet to pon_bid_price_elements
281 -- (these will end up being the price factors hidden from the bidding UI by the buyer)
282 INSERT INTO
283 pon_bid_price_elements (
284 bid_number,
285 line_number,
286 price_element_type_id,
287 auction_header_id,
288 pricing_basis,
289 auction_currency_value,
290 bid_currency_value,
291 sequence_number,
292 creation_date,
293 created_by,
294 last_update_date,
295 last_updated_by,
296 pf_type
297 )
298 SELECT
299 p_bid_number,
300 pfs.line_number,
301 pfs.price_element_type_id,
302 pfs.auction_header_id,
303 pfs.pricing_basis,
304 pf_values.value,
305 DECODE('PERCENTAGE', pfs.pricing_basis, pf_values.value,
306 pf_values.value * l_currency_rate),
307 pfs.sequence_number,
308 SYSDATE,
309 p_login_user_id,
310 SYSDATE,
311 p_login_user_id,
312 pfs.pf_type
313 FROM
314 pon_price_elements pfs,
315 pon_pf_supplier_values pf_values
316 WHERE
317 pfs.auction_header_id = p_auction_header_id
318 AND pf_values.auction_header_id = pfs.auction_header_id
319 AND pf_values.line_number = pfs.line_number
320 AND pf_values.pf_seq_number = pfs.sequence_number
321 AND pfs.pf_type = 'BUYER'
322 AND pf_values.supplier_seq_number = l_supplier_sequence_number
323 AND NVL(pf_values.value, 0) <> 0
324 AND NOT EXISTS (SELECT NULL
325 FROM pon_bid_price_elements bid_pfs
326 WHERE
327 bid_pfs.bid_number = p_bid_number
328 AND bid_pfs.line_number = pfs.line_number
329 AND bid_pfs.price_element_type_id = pfs.price_element_type_id);
330
331 END IF;
332
333 print_log(l_api_name, p_bid_number || ' - END add_hidden_price_factors');
334
335 END add_hidden_price_factors;
336
337
338 PROCEDURE add_hidden_price_factors_auto(p_bid_number IN NUMBER,
339 p_auction_header_id IN NUMBER,
340 p_supplier_view_type IN VARCHAR2,
341 p_trading_partner_id IN NUMBER,
342 p_vendor_site_id IN NUMBER,
343 p_login_user_id IN NUMBER)
344 IS
345 PRAGMA AUTONOMOUS_TRANSACTION;
346
347 l_api_name CONSTANT VARCHAR2(30) := 'add_hidden_price_factors_auto';
348 BEGIN
349
350 print_log(l_api_name, p_bid_number || ' - BEGIN add_hidden_price_factors AUTONOMOUS');
351
352 add_hidden_price_factors(p_bid_number, p_auction_header_id,
353 p_supplier_view_type, p_trading_partner_id,
354 p_vendor_site_id, p_login_user_id);
355
356 commit;
357 print_log(l_api_name, p_bid_number || ' - add_hidden_price_factors: committed!');
358 print_log(l_api_name, p_bid_number || ' - END add_hidden_price_factors AUTONOMOUS');
359
360 END add_hidden_price_factors_auto;
361
362
363 --========================================================================
364 -- PROCEDURE : archive_prev_active_bids PRIVATE
365 -- archive_prev_active_bids_auto PRIVATE
366 -- PARAMETERS:
367 -- VERSION : current version 1.x
368 -- initial version 1.0
369 -- COMMENT : called by update_auction_info, this procedure archives all
370 -- past active bids
371 --========================================================================
372
373 PROCEDURE archive_prev_active_bids(p_auctionHeaderId IN NUMBER,
374 p_bidNumber IN NUMBER,
375 p_vendorSiteId IN NUMBER,
376 p_oldBidNumber IN NUMBER)
377 IS
378 v_doctypeId pon_auction_headers_all.doctype_id%TYPE;
379 v_bidTradingPartnerId pon_bid_headers.trading_partner_id%TYPE;
380 v_bidTradingPartnerContactId pon_bid_headers.trading_partner_contact_id%TYPE;
381 v_fixedValue pon_auc_doctype_rules.fixed_value%TYPE;
382 v_amendmentNumber NUMBER;
383 v_auctionHeaderIdOrigAmend NUMBER;
384
385 l_api_name CONSTANT VARCHAR2(30) := 'archive_prev_active_bids';
386 BEGIN
387
388 print_log(l_api_name, p_bidNumber || ' - begin archive prev active bids');
389
390 SELECT trading_partner_id,
391 trading_partner_contact_id
392 INTO v_bidTradingPartnerId,
393 v_bidTradingPartnerContactId
394 FROM pon_bid_headers
395 WHERE bid_number = p_bidNumber;
396
397 SELECT doctype_id,
398 nvl(amendment_number, 0),
399 auction_header_id_orig_amend
400 INTO v_doctypeId,
401 v_amendmentNumber,
402 v_auctionHeaderIdOrigAmend
403 FROM pon_auction_headers_all
404 WHERE auction_header_id = p_auctionheaderid;
405
406 SELECT fixed_value
407 INTO v_fixedValue
408 FROM pon_auc_bizrules pab,
409 pon_auc_doctype_rules padr
410 WHERE pab.name = 'AWARD_TYPE'
411 AND pab.bizrule_id = padr.bizrule_id
412 AND padr.doctype_id = v_doctypeId;
413
414 IF (v_fixedValue IS NOT NULL) THEN
415 IF (v_fixedValue <> 'COMMIT') THEN
416
417 -- Update the previous active bid to archived for current auction
418 IF (p_oldBidNumber is not null) THEN
419 UPDATE PON_BID_HEADERS
420 SET BID_STATUS = 'ARCHIVED',
421 LAST_UPDATE_DATE = SYSDATE
422 WHERE AUCTION_HEADER_ID = p_auctionHeaderId
423 AND BID_NUMBER = p_oldBidNumber
424 AND BID_STATUS = 'ACTIVE';
425 END IF;
426
427
428 -- Go back to previous amendments, update all the active or
429 -- resubmission one to archived
430 IF (v_amendmentNumber > 0) THEN
431 UPDATE PON_BID_HEADERS
432 SET BID_STATUS = 'ARCHIVED',
433 LAST_UPDATE_DATE = SYSDATE
434 WHERE AUCTION_HEADER_ID in (
435 SELECT AUCTION_HEADER_ID
436 FROM PON_AUCTION_HEADERS_ALL
437 WHERE AUCTION_HEADER_ID_ORIG_AMEND = v_auctionHeaderIdOrigAmend)
438 AND BID_NUMBER <> p_bidNumber
439 AND NVL(VENDOR_SITE_ID, -1) = NVL(p_vendorSiteId, -1)
440 AND TRADING_PARTNER_ID = v_bidTradingPartnerId
441 AND TRADING_PARTNER_CONTACT_ID = v_bidtradingpartnercontactid
442 AND BID_STATUS in ('ACTIVE', 'RESUBMISSION');
443 END IF;
444 END IF;
445
446 END IF;
447
448 print_log(l_api_name, p_bidNumber || ' - end archive prev active bids');
449 END archive_prev_active_bids;
450
451 PROCEDURE archive_prev_active_bids_auto(p_auctionHeaderId IN NUMBER,
452 p_bidNumber IN NUMBER,
453 p_vendorSiteId IN NUMBER,
454 p_oldBidNumber IN NUMBER)
455 IS
456 PRAGMA AUTONOMOUS_TRANSACTION;
457
458 l_api_name CONSTANT VARCHAR2(30) := 'archive_prev_active_bids_auto';
459 BEGIN
460 print_log(l_api_name, p_bidNumber || ' - BEGIN archive prev active bids AUTONOMOUS');
461
462 archive_prev_active_bids(p_auctionHeaderId, p_bidNumber, p_vendorSiteId, p_oldBidNumber);
463 commit;
464
465 print_log(l_api_name, p_bidNumber || ' - archive prev active bids: committed!');
466 print_log(l_api_name, p_bidNumber || ' - END archive prev active bids AUTONOMOUS');
467
468 END;
469
470
471 --========================================================================
472 -- PROCEDURE : set_partial_response_flag PRIVATE
473 -- PARAMETERS:
474 -- VERSION : current version 1.x
475 -- initial version 1.0
476 -- COMMENT : called by update_auction_info, this procedure calculates
477 -- the partial response flag and sets it in pon_bid_headers
478 --========================================================================
479
480 PROCEDURE set_partial_response_flag(p_bidNumber IN NUMBER)
481 IS
482 l_api_name CONSTANT VARCHAR2(30) := 'set_partial_response_flag';
483 BEGIN
484
485 print_log(l_api_name || '.BEGIN', p_bidNumber ||' Begin - set_partial_response_flag');
486 UPDATE pon_bid_headers bh
487 SET partial_response_flag = 'N'
488 WHERE bh.bid_number = p_bidNumber;
489
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
499 and al.group_type <> 'GROUP'
500 and al.group_type <> 'LOT_LINE'
501 and al.quantity <> bl.quantity
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
511 and bl.line_number(+) = al.line_number
512 and al.group_type <> 'GROUP'
513 and al.group_type <> 'LOT_LINE'
514 and bl.line_number is null));
515 print_log(l_api_name || '.END', p_bidNumber ||' End - set_partial_response_flag');
516 END set_partial_response_flag;
517
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
536 and bl2.line_number = al.line_number
537 and bl2.bid_number = bl.bid_number
538 and al.parent_line_number = bl.line_number)
539 where bl.bid_number = p_bidNumber
540 and (select a2.group_type
541 from pon_auction_item_prices_all a2
542 where a2.auction_header_id = bl.auction_header_id
543 and a2.line_number = bl.line_number) = 'GROUP';
544
545 print_log(l_api_name || '.END', p_bidNumber ||' End - update_group_amount');
546
547 END update_group_amount;
548
549
550
551
552 --PROCEDURE CALCULATE_PRICES
553 --
554 --
555 procedure calculate_prices
556 (
557 p_auctionType IN VARCHAR2,
558 p_currentPrice IN NUMBER,
559 p_currentLimit IN NUMBER,
560 p_currentBidChange IN NUMBER,
561 p_bestPrice IN NUMBER,
562 p_bestLimit IN NUMBER,
563 p_bestBidChange IN NUMBER,
564 p_newPrice OUT NOCOPY NUMBER,
565 p_newBestPrice OUT NOCOPY NUMBER
566 ) IS
567 -- this function returns whether x_price1 is better than x_price2
568 -- i.e. in reverse auction if (x_price1 < x_price2)
569 -- i.e. in forward auction if (x_price1 > x_price2)
570 --
571 l_api_name CONSTANT VARCHAR2(30) := 'calculate_prices';
572
573
574 FUNCTION is_better_price(x_price1 IN NUMBER,
575 x_price2 IN NUMBER)
576 RETURN BOOLEAN IS
577 BEGIN
578 return better_price(p_auctionType, x_price1,x_price2);
579 END;
580 --
581 FUNCTION is_between(x_price IN NUMBER,
582 x_price1 IN NUMBER,
583 x_price2 IN NUMBER)
584 RETURN BOOLEAN IS
585 BEGIN
586 if (p_auctionType = 'REVERSE') then
587 return ((x_price1 <= x_price) AND (x_price <= x_price2));
588 else
589 return ((x_price1 >= x_price) AND (x_price >= x_price2));
590 end if;
591 END;
592 --
593 FUNCTION change_bid (x_price IN NUMBER,
594 x_delta IN NUMBER,
595 x_limit IN NUMBER)
596 RETURN NUMBER IS
597 BEGIN
598 if (p_auctionType = 'REVERSE') then
599 if ((x_price - x_delta) > x_limit) then
600 return (x_price - x_delta);
601 else
602 return x_limit;
603 end if;
604 else
605 if ((x_price + x_delta) < x_limit) then
606 return (x_price + x_delta);
607 else
608 return x_limit;
609 end if;
610 end if;
611 END;
612 BEGIN
613 --
614 -- logging
615 print_log(l_api_name || '.BEGIN', ' ');
616 print_log(l_api_name, 'p_auctionType=' || p_auctionType);
617 print_log(l_api_name, 'p_currentPrice=' || p_currentPrice);
618 print_log(l_api_name, 'p_currentLimit=' || p_currentLimit);
619 print_log(l_api_name, 'p_currentBidChange=' || p_currentBidChange);
620 print_log(l_api_name, 'p_bestPrice=' || p_bestPrice);
621 print_log(l_api_name, 'p_bestLimit=' || p_bestLimit);
622 print_log(l_api_name, 'p_bestBidChange=' || p_bestBidChange);
623
624 -- the first two cases will be true if the best bid price range
625 -- and the current bid price range do not intersect
626 -- OR part is fix for BUG #1787086
627 --
628 IF (is_better_price(p_bestPrice, p_currentLimit) OR
629 (p_currentLimit = p_bestPrice)) THEN
630 --
631 -- dbms_output.put_line(' best price better than current limit');
632 p_newPrice := p_currentLimit;
633 p_newBestPrice := p_bestPrice;
634 print_log(l_api_name || '.END', ' ');
635 RETURN;
636 ELSIF (is_better_price(p_currentPrice, p_bestLimit)) THEN
637 -- dbms_output.put_line(' current price better than best limit');
638 p_newPrice := p_currentPrice;
639 p_newBestPrice := p_bestLimit;
640 print_log(l_api_name || '.END', ' ');
641 RETURN;
642 --
643 -- the follow will be true if the best bid price range and
644 -- the current bid price range intersect
645 --
646 ELSIF ((is_between(p_bestPrice, p_currentLimit, p_currentPrice)) OR
647 (is_between(p_currentPrice, p_bestLimit, p_bestPrice))) THEN
648 --
649 -- dbms_output.put_line(' between case');
650 IF (is_better_price(p_currentLimit,p_bestLimit)) THEN
651 --
652 -- dbms_output.put_line(' between case1');
653 p_newPrice := change_bid(p_bestLimit, p_currentBidChange, p_currentLimit);
654 p_newBestPrice := p_bestLimit;
655 --
656 ELSIF (is_better_price(p_bestLimit,p_currentLimit)) THEN
657 -- dbms_output.put_line(' between case2');
658 p_newPrice := p_currentLimit;
659 p_newBestPrice := change_bid(p_currentLimit, p_bestBidChange, p_bestLimit);
660 ELSIF (p_currentLimit = p_bestLimit) THEN
661 -- dbms_output.put_line(' between case3');
662 p_newPrice := p_currentLimit;
663 p_newBestPrice := p_bestLimit;
664 END IF;
665 --
666 END IF;
667 --
668 print_log(l_api_name || '.END', ' ');
669 END calculate_prices;
670 --
671 PROCEDURE copy_attachments
672 (
673 p_auctionHeaderId IN NUMBER,
674 p_oldBidNum IN NUMBER,
675 p_newBidNum IN NUMBER)
676 IS
677 --
678
679 CURSOR bid_attachments IS
680 SELECT fndat.entity_name entity, fndat.attached_document_id attached_document_id,
681 fndat.seq_num seq_num, dc.datatype_id datatype_id,
682 dt.description description, dt.file_name file_name, dc.media_id media_id,
683 fndat.pk3_value pk3,
684 fndat.pk4_value pk4
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;
694 WEB_PAGE number := 5;
695 EXTERNAL_FILE number := 6;
696 ACCESS_URL VARCHAR2(255) := '/jsp/pon/attachments/get_attachment.jsp?sequence_num=';
697 v_text fnd_documents_short_text.short_text%TYPE;
698 v_url VARCHAR2(255);
699 v_document_id NUMBER := NULL;
700 v_file_id NUMBER := NULL;
701 v_attachment_cat_id NUMBER := NULL;
702
703 l_api_name CONSTANT VARCHAR2(30) := 'copy_attachments';
704
705 --
706 --
707 BEGIN
708 --
709
710 -- logging
711 print_log(l_api_name || '.BEGIN', ' ');
712 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
713 print_log(l_api_name, 'p_oldBidNum=' || p_oldBidNum);
714 print_log(l_api_name, 'p_newBidNum=' || p_newBidNum);
715
716 FOR bid_att IN bid_attachments LOOP
717 --
718 print_log(l_api_name, 'cursor=' || bid_att.attached_document_id);
719
720 IF (bid_att.datatype_id = SHORT_TEXT) THEN
721 SELECT short_text
722 INTO v_text
723 FROM fnd_documents_short_text
724 WHERE media_id = bid_att.media_id;
725 --
726 ELSE
727 v_text := null;
728 END IF;
729 --
730 IF ((bid_att.datatype_id = SHORT_TEXT) OR
731 (bid_att.datatype_id = EXTERNAL_FILE)) THEN
732 v_url := ACCESS_URL || to_char(bid_att.seq_num);
733 ELSIF (bid_att.datatype_id = WEB_PAGE) THEN
734 v_url := bid_att.file_name;
735 END IF;
736 --
737 SELECT category_id
738 INTO v_attachment_cat_id
739 FROM fnd_document_categories
740 WHERE name = 'Vendor';
741
742
743 PON_ATTACHMENTS.add_attachment(
744 bid_att.seq_num,
745 v_attachment_cat_id,
746 bid_att.description,
747 bid_att.datatype_id,
748 v_text,
749 bid_att.file_name,
750 v_url,
751 bid_att.entity,
752 p_auctionHeaderId,
753 p_newBidNum,
754 bid_att.pk3,
755 bid_att.pk4,
756 null,
757 bid_att.media_id,
758 -1,
759 v_document_id,
760 v_file_id);
761 --
762 END LOOP;
763 --
764 print_log(l_api_name || '.END', ' ');
765 --
766 END COPY_ATTACHMENTS;
767 --
768 --
769 -- this function returns the new bid number
770 --
771 FUNCTION clone_update_bid
772 ( p_auctionHeaderId IN NUMBER,
773 p_bidNumber IN NUMBER,
774 p_new_publish_date IN DATE,
775 p_triggerBidNumber IN NUMBER
776 ) RETURN NUMBER IS
777 --
778 v_nextBid NUMBER;
779 l_msg_data VARCHAR2(250);
780 l_msg_count NUMBER;
781 l_return_status VARCHAR2(1);
782 v_contermsExist VARCHAR2(1);
783 l_api_name CONSTANT VARCHAR2(30) := 'clone_update_bid';
784
785 BEGIN
786 --
787
788 -- logging
789 print_log(l_api_name || '.BEGIN', ' ');
790 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
791 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
792 print_log(l_api_name, 'p_new_publish_date=' || p_new_publish_date);
793 print_log(l_api_name, 'p_triggerBidNumber=' || p_triggerBidNumber);
794
795 SELECT pon_bid_headers_s.nextval
796 INTO v_nextBid
797 FROM dual;
798 --
799
800 -- need to activate deliverables for the new bid triggered due to proxy
801 -- xxx
802 --
803 INSERT INTO pon_bid_headers (
804 bid_status,
805 bid_status_name,
806 bid_number,
807 shortlist_flag,
808 proxy_bid_flag,
809 auction_header_id,
810 bidders_bid_number,
811 bid_type,
812 contract_type,
813 trading_partner_contact_name,
814 trading_partner_contact_id,
815 trading_partner_name,
816 trading_partner_id,
817 bid_effective_date,
818 bid_expiration_date,
819 publish_date,
820 cancelled_date,
821 payment_terms_id,
822 freight_terms_code,
823 carrier_code,
824 fob_code,
825 note_to_auction_owner,
826 creation_date,
827 created_by,
828 last_update_date,
829 last_updated_by,
830 auction_creation_date,
831 attachments_flag,
832 disqualify_reason,
833 language_code,
834 award_status,
835 award_status_name,
836 phone,
837 fax,
838 email,
839 --operator_id,
840 bid_currency_code,
841 rate,
842 rate_type,
843 rate_date,
844 min_bid_change,
845 number_price_decimals,
846 doctype_id,
847 vendor_id,
848 vendor_site_id,
849 vendor_site_code,
850 agent_id,
851 create_sourcing_rules,
852 update_sourcing_rules,
853 release_method,
854 po_header_id,
855 po_error_code,
856 po_wf_creation_rnd,
857 surrog_bid_flag,
858 surrog_bid_created_tp_id,
859 SURROG_BID_CREATED_CONTACT_ID,
860 SURROG_BID_RECEIPT_DATE,
861 SURROG_BID_ONLINE_ENTRY_DATE,
862 ATTRIBUTE_LINE_NUMBER,
863 partial_response_flag,
864 color_sequence_id,
865 old_bid_number)
866 SELECT bid_status,
867 bid_status_name,
868 v_nextBid,
869 'Y',
870 'Y',
871 auction_header_id,
872 bidders_bid_number,
873 bid_type,
874 contract_type,
875 trading_partner_contact_name,
876 trading_partner_contact_id,
877 trading_partner_name,
878 trading_partner_id,
879 bid_effective_date,
880 bid_expiration_date,
881 p_new_publish_date,
882 cancelled_date,
883 payment_terms_id,
884 freight_terms_code,
885 carrier_code,
886 fob_code,
887 note_to_auction_owner,
888 sysdate,
889 created_by,
890 sysdate,
891 last_updated_by,
892 auction_creation_date,
893 attachments_flag,
894 disqualify_reason,
895 language_code,
896 award_status,
897 award_status_name,
898 phone,
899 fax,
900 email,
901 --operator_id,
902 bid_currency_code,
903 rate,
904 rate_type,
905 rate_date,
906 min_bid_change,
907 number_price_decimals,
908 doctype_id,
909 vendor_id,
910 vendor_site_id,
911 vendor_site_code,
912 agent_id,
913 create_sourcing_rules,
914 update_sourcing_rules,
915 release_method,
916 po_header_id,
917 po_error_code,
918 po_wf_creation_rnd,
919 surrog_bid_flag,
920 surrog_bid_created_tp_id,
921 surrog_bid_created_contact_id,
922 surrog_bid_receipt_date,
923 sysdate,
924 -1,
925 partial_response_flag,
926 color_sequence_id,
927 old_bid_number
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,
937 item_description,
938 category_id,
939 UOM,
940 unit_of_measure,
941 quantity,
942 price,
943 unit_price,
944 minimum_bid_price,
945 promised_date,
946 award_status,
947 award_date,
948 note_to_auction_owner,
949 last_update_date,
950 creation_date,
951 created_by,
952 last_updated_by,
953 auction_creation_date,
954 attachments_flag,
955 order_number,
956 award_status_name,
957 category_name,
958 language_code,
959 ship_to_location_id,
960 --operator_id,
961 publish_date,
962 bid_currency_price,
963 bid_currency_unit_price,
964 bid_currency_trans_price,
965 proxy_bid_limit_price,
966 bid_currency_limit_price,
967 proxy_bid_flag,
968 first_bid_price,
969 has_attributes_flag,
970 total_weighted_score,
971 rank,
972 trigger_bid_number,
973 group_amount,
974 HAS_BID_PAYMENTS_FLAG,
975 RETAINAGE_RATE_PERCENT,
976 MAX_RETAINAGE_AMOUNT,
977 BID_CURR_MAX_RETAINAGE_AMT,
978 has_bid_flag,
979 per_unit_price_component, --bug 7673590
980 fixed_amount_component)
981 SELECT auction_header_id,
982 auction_line_number,
983 v_nextBid,
984 line_number,
985 item_description,
986 category_id,
987 UOM,
988 unit_of_measure,
989 quantity,
990 price,
991 unit_price,
992 minimum_bid_price,
993 promised_date,
994 award_status,
995 award_date,
996 note_to_auction_owner,
997 sysdate,
998 sysdate,
999 created_by,
1000 last_updated_by,
1001 auction_creation_date,
1002 attachments_flag,
1003 order_number,
1004 award_status_name,
1005 category_name,
1006 language_code,
1007 ship_to_location_id,
1008 --operator_id,
1009 publish_date,
1010 bid_currency_price,
1011 bid_currency_unit_price,
1012 bid_currency_trans_price,
1013 proxy_bid_limit_price,
1014 bid_currency_limit_price,
1015 proxy_bid_flag,
1016 first_bid_price,
1017 has_attributes_flag,
1018 total_weighted_score,
1019 rank,
1020 p_triggerBidNumber,
1021 group_amount,
1022 HAS_BID_PAYMENTS_FLAG,
1023 RETAINAGE_RATE_PERCENT,
1024 MAX_RETAINAGE_AMOUNT,
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 (
1034 auction_header_id,
1035 auction_line_number,
1036 bid_number,
1037 line_number,
1038 attribute_name,
1039 attr_level,
1040 datatype,
1041 value,
1042 creation_date,
1043 created_by,
1044 last_update_date,
1045 last_updated_by,
1046 score,
1047 weighted_score,
1048 sequence_number,
1049 attr_group_seq_number,
1050 attr_disp_seq_number)
1051 SELECT auction_header_id,
1052 auction_line_number,
1053 v_nextBid,
1054 line_number,
1055 attribute_name,
1056 attr_level,
1057 datatype,
1058 value,
1059 sysdate,
1060 created_by,
1061 sysdate,
1062 last_updated_by,
1063 score,
1064 weighted_score,
1065 sequence_number,
1066 attr_group_seq_number,
1067 attr_disp_seq_number
1068 FROM pon_bid_attribute_values
1069 WHERE auction_header_id = p_auctionHeaderId
1070 AND bid_number = p_bidNumber;
1071 --
1072 INSERT INTO pon_bid_price_elements (
1073 bid_number,
1074 line_number,
1075 price_element_type_id,
1076 auction_header_id,
1077 pricing_basis,
1078 auction_currency_value,
1079 bid_currency_value,
1080 sequence_number,
1081 creation_date,
1082 created_by,
1083 last_update_date,
1084 last_updated_by,
1085 pf_type)
1086 SELECT v_nextBid,
1087 line_number,
1088 price_element_type_id,
1089 auction_header_id,
1090 pricing_basis,
1091 auction_currency_value,
1092 bid_currency_value,
1093 sequence_number,
1094 sysdate,
1095 created_by,
1096 sysdate,
1097 last_updated_by,
1098 pf_type
1099 FROM pon_bid_price_elements
1100 WHERE auction_header_id = p_auctionHeaderId
1101 AND bid_number = p_bidNumber;
1102
1103
1104 -- TODO, still need to copy attachments
1105 --
1106 print_log(l_api_name, 'calling subroutine copy_attachments');
1107 copy_attachments(p_auctionHeaderId, p_bidNumber, v_nextBid);
1108 --
1109 UPDATE pon_bid_headers
1110 SET bid_status = 'ARCHIVED',
1111 bid_status_name = (SELECT meaning
1112 FROM fnd_lookups
1113 WHERE lookup_type='PON_BID_STATUS'
1114 AND lookup_code = 'ARCHIVED')
1115 WHERE auction_header_id = p_auctionHeaderId
1116 AND bid_number = p_bidNumber;
1117 --
1118 --
1119 UPDATE pon_auction_item_prices_all
1120 SET best_bid_number = v_nextBid,
1121 lowest_bid_number = decode(lowest_bid_number, null, null,
1122 v_nextBid)
1123 WHERE auction_header_id = p_auctionHeaderId
1124 AND best_bid_number = p_bidNumber;
1125
1126 UPDATE pon_auction_item_prices_all
1127 SET best_bid_bid_number = v_nextBid
1128 WHERE auction_header_id = p_auctionHeaderId
1129 AND best_bid_bid_number = p_bidNumber;
1130
1131 -- finally, we need to copy all the TandCs and deliverables
1132 -- from the p_bidNumber to v_nextBid
1133 -- since this bid has been triggered by a proxy
1134 -- we will maintain the statuses on the deliverables
1135 -- rrkulkar
1136
1137 if (PON_CONTERMS_UTL_PVT.is_contracts_installed() = FND_API.G_TRUE) then
1138
1139 begin
1140 select conterms_exist_flag into v_contermsExist
1141 from pon_auction_headers_all
1142 where auction_header_id = p_auctionHeaderId;
1143
1144 if(v_contermsExist = 'Y') then
1145
1146 -- first copy the response document from old bid
1147 -- to the new bid
1148 print_log(l_api_name, 'calling subroutine PON_CONTERMS_UTL_PVT.copyResponseDoc');
1149 PON_CONTERMS_UTL_PVT.copyResponseDoc(p_bidNumber , v_nextBid);
1150
1151 -- and then activate deliverables for the newly created bid
1152 print_log(l_api_name, 'calling subroutine PON_CONTERMS_UTL_PVT.activateDeliverables');
1153 PON_CONTERMS_UTL_PVT.activateDeliverables(p_auctionHeaderId,
1154 v_nextBid,
1155 p_bidNumber,
1156 l_msg_data,
1157 l_msg_count,
1158 l_return_status);
1159
1160 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1161 fnd_log.string(fnd_log.level_statement,
1162 'pon_auction_headers_pkg',
1163 'clone_update_bid failed for auction_id=' || p_auctionHeaderId || ':' || v_nextBid || ':' || p_bidNumber || ', msg_data=' || l_msg_data);
1164 end if;
1165 end if;
1166 exception
1167 when others then
1168 null;
1169 end;
1170 end if;
1171
1172
1173
1174
1175 --
1176 print_log(l_api_name || '.END', ' ');
1177 --
1178 RETURN v_nextBid;
1179 --
1180 END CLONE_UPDATE_BID;
1181 --
1182 PROCEDURE get_auc_header_id_orig_round
1183 ( p_auctionHeaderId IN NUMBER,
1184 p_auctionHeaderIdOrigRound OUT NOCOPY NUMBER)
1185 IS
1186 --
1187 l_api_name CONSTANT VARCHAR2(30) := 'get_auc_header_id_orig_round';
1188
1189 BEGIN
1190 print_log(l_api_name || '.BEGIN', ' ');
1191 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
1192 print_log(l_api_name, 'p_auctionHeaderIdOrigRound=' || p_auctionHeaderIdOrigRound);
1193
1194 SELECT nvl(auction_header_id_orig_round, auction_header_id)
1195 INTO p_auctionHeaderIdOrigRound
1196 FROM pon_auction_headers_all
1197 WHERE auction_header_id = p_auctionHeaderId;
1198
1199 print_log(l_api_name || '.END', ' ');
1200 END get_auc_header_id_orig_round;
1201 --
1202 --
1203 PROCEDURE update_new_bid_line
1204 ( p_auctionHeaderId IN NUMBER,
1205 p_bidNum IN NUMBER,
1206 p_line IN NUMBER,
1207 p_price IN NUMBER,
1208 p_bid_curr_price IN NUMBER,
1209 p_publish_date IN DATE)
1210 IS
1211 --
1212 l_api_name CONSTANT VARCHAR2(30) := 'update_new_bid_line';
1213
1214 BEGIN
1215
1216 print_log(l_api_name || '.BEGIN', ' ');
1217 print_log(l_api_name, 'p_auctionHeaderId='||p_auctionHeaderId);
1218 print_log(l_api_name, 'p_bidNum=' || p_bidNum);
1219 print_log(l_api_name, 'p_line=' || p_line);
1220 print_log(l_api_name, 'p_price=' || p_price);
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,
1230 bid_currency_unit_price = p_bid_curr_price,
1231 publish_date = p_publish_date,
1232 proxy_bid_flag = 'Y'
1233 WHERE auction_header_id = p_auctionHeaderId
1234 AND bid_number = p_bidNum
1235 AND line_number = p_line;
1236
1237 --
1238 UPDATE pon_auction_item_prices_all
1239 SET best_bid_price = p_price,
1240 best_bid_currency_price = p_bid_curr_price,
1241 lowest_bid_price = decode (lowest_bid_price, null,null,
1242 p_price)
1243 WHERE auction_header_id = p_auctionHeaderId
1244 AND line_number = p_line
1245 AND best_bid_number = p_bidNum;
1246
1247 UPDATE pon_auction_item_prices_all
1248 SET best_bid_bid_price = p_price,
1249 best_bid_bid_currency_price = p_bid_curr_price
1250 WHERE auction_header_id = p_auctionHeaderId
1251 AND line_number = p_line
1252 AND best_bid_bid_number = p_bidNum;
1253
1254
1255 --
1256 print_log(l_api_name || '.END', ' ');
1257 --
1258 END update_new_bid_line;
1259 --
1260 --
1261
1262
1263 PROCEDURE get_previous_bid( p_auctionHeaderId IN NUMBER,
1264 p_bidNumber IN NUMBER,
1265 v_oldBidNumber OUT NOCOPY NUMBER) IS
1266
1267 v_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
1268 v_tpid pon_bid_headers.trading_partner_id%TYPE;
1269 v_vendorSiteId pon_bid_headers.vendor_site_id%TYPE;
1270
1271 l_api_name CONSTANT VARCHAR2(30) := 'get_previous_bid';
1272
1273 BEGIN
1274
1275 -- logging
1276 print_log(l_api_name || '.BEGIN', ' ');
1277 print_log(l_api_name, 'p_auctionHeaderI=' || p_auctionHeaderId);
1278 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
1279
1280 --
1281 -- Get the current bid information
1282 -- Get the owner of the current bid
1283 --
1284 SELECT trading_partner_id,
1285 trading_partner_contact_id,
1286 vendor_site_id
1287 INTO v_tpid,
1288 v_tpcid,
1289 v_vendorSiteId
1290 FROM pon_bid_headers
1291 WHERE auction_header_id = p_auctionHeaderId
1292 AND bid_number = p_bidNumber;
1293
1294
1295 --
1296 -- Get the user's most recent bid
1297 -- Is it possible that taking the
1298 -- max bid number will not yield correct result ?
1299 -- due to draft bid project ?
1300
1301 SELECT max(bid_number)
1302 INTO v_oldBidNumber
1303 FROM pon_bid_headers
1304 WHERE auction_header_id = p_auctionHeaderId
1305 AND trading_partner_id = v_tpid
1306 AND trading_partner_contact_id = v_tpcid
1307 AND nvl(vendor_site_id,-1) = nvl(v_vendorSiteId, -1)
1308 AND bid_number <> p_bidNumber
1309 AND nvl(award_status, 'NONE') <> 'COMMITTED'
1310 AND bid_status <> 'DRAFT'
1311 AND bid_status <> 'ARCHIVED';
1312
1313 print_log(l_api_name || '.END', ' ');
1314
1315 END get_previous_bid;
1316
1317 PROCEDURE get_previous_nonproxy_bid( p_auctionHeaderId IN NUMBER,
1318 p_bidNumber IN NUMBER,
1319 p_vendorSiteId IN NUMBER,
1320 v_oldNonProxyBidNumber OUT NOCOPY NUMBER) IS
1321
1322 v_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
1323 v_tpid pon_bid_headers.trading_partner_id%TYPE;
1324 l_api_name CONSTANT VARCHAR2(30) := 'get_previous_nonproxy_bid';
1325
1326 BEGIN
1327
1328 -- logging
1329 print_log(l_api_name || '.BEGIN', ' ');
1330 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
1331 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
1332 print_log(l_api_name, 'p_vendorSiteId=' || p_vendorSiteId);
1333
1334 --
1335 -- Get the current bid information
1336 --
1337 SELECT trading_partner_id,
1338 trading_partner_contact_id
1339 INTO v_tpid,
1340 v_tpcid
1341 FROM pon_bid_headers
1342 WHERE auction_header_id = p_auctionHeaderId
1343 AND bid_number = p_bidnumber;
1344
1345
1346 --
1347 -- Get the user's most recent hard (non proxy) bid
1348 --
1349 -- Is it possible that taking the
1350 -- max bid number will not yield correct result ?
1351 -- due to draft bid project ?
1352
1353 -- by mxfang
1354 -- to fix the following bug
1355 -- supplier has an active bid which has been disqualified. Then supplier resubmits
1356 -- his bid and saves as a draft. When he tries to resubmit his draft, he gets rebid_error
1357 -- due to the validation logic in update_auction_info
1358 -- the fix here is to exclude the disqualifed bid
1359 SELECT max(bid_number)
1360 INTO v_oldNonProxyBidNumber
1361 FROM pon_bid_headers
1362 WHERE auction_header_id = p_auctionHeaderId
1363 AND trading_partner_id = v_tpid
1364 AND trading_partner_contact_id = v_tpcid
1365 AND nvl(vendor_site_id, -1) = nvl(p_vendorSiteId, -1)
1366 AND ((proxy_bid_flag IS null) OR (proxy_bid_flag <> 'Y'))
1367 AND bid_number <> p_bidnumber
1368 AND nvl(award_status, 'NONE') <> 'COMMITTED'
1369 AND bid_status <> 'DISQUALIFIED';
1370
1371 print_log(l_api_name || '.END', ' ');
1372
1373 END get_previous_nonproxy_bid;
1374
1375
1376 PROCEDURE get_most_recent_active_bid( p_auctionHeaderId IN NUMBER,
1377 p_bidNumber IN NUMBER,
1378 v_activeBidNumber OUT NOCOPY NUMBER,
1379 v_recentBidStatus OUT NOCOPY VARCHAR2) IS
1380
1381 v_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
1382 v_tpid pon_bid_headers.trading_partner_id%TYPE;
1383 l_api_name CONSTANT VARCHAR2(30) := 'get_most_recent_active_bid';
1384
1385 BEGIN
1386 --
1387 -- Get the current bid information
1388 --
1389 -- logging
1390 print_log(l_api_name || '.BEGIN', ' ');
1391 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
1392 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
1393
1394 SELECT trading_partner_id,
1395 trading_partner_contact_id
1396 INTO v_tpid,
1397 v_tpcid
1398 FROM pon_bid_headers
1399 WHERE auction_header_id = p_auctionHeaderId
1400 AND bid_number = p_bidnumber;
1401
1402 --
1403 -- Get the user's most recent hard (non proxy) bid
1404 -- Donot need to check whether the most recent active
1405 -- bid is not the current bid that user is trying to place
1406 -- remember in case of draft bids, the bid number does not
1407 -- change
1408 --
1409 -- Is it possible that taking the
1410 -- max bid number will not yield correct result ?
1411 -- due to draft bid project ?
1412
1413 BEGIN
1414
1415 SELECT max(bid_number)
1416 INTO v_activeBidNumber
1417 FROM pon_bid_headers
1418 WHERE auction_header_id = p_auctionHeaderId
1419 AND trading_partner_id = v_tpid
1420 AND trading_partner_contact_id = v_tpcid
1421 AND ((proxy_bid_flag IS null) OR (proxy_bid_flag <> 'Y'))
1422 AND nvl(award_status, 'NONE') <> 'COMMITTED';
1423
1424 SELECT bid_status
1425 INTO v_recentBidStatus
1426 FROM pon_bid_headers
1427 WHERE bid_number = v_activeBidNumber;
1428
1429 EXCEPTION
1430
1431 --
1432 -- If the user does not have a previous bid then do nothing...
1433 --
1434 WHEN NO_DATA_FOUND THEN null;
1435
1436 END;
1437
1438 print_log(l_api_name || '.END', ' ');
1439
1440 END get_most_recent_active_bid;
1441
1442
1443 -----------------------------------------------------------------
1444 -- Added the following for Bug 2149531 --
1445 -- --
1446 -- Procedure to update the user's current rank --
1447 -- based on the database's latest rank from the --
1448 -- user's previous active, non-disqualified bid. --
1449 -- --
1450 -- The rank that is inserted with the bid is the --
1451 -- middle tier rank, which is cached, and can be out --
1452 -- of sync with the database if another user submits a bid --
1453 -- that changes the current user's rank while in the --
1454 -- processes of bidding. --
1455 -----------------------------------------------------------------
1456 PROCEDURE update_unchanged_rank( p_auctionHeaderId IN NUMBER,
1457 p_bidNumber IN NUMBER,
1458 p_vendorSiteId IN NUMBER,
1459 p_batchStart IN NUMBER,
1460 p_batchEnd IN NUMBER,
1461 p_discard_tech_nonshort IN VARCHAR2) IS
1462
1463 v_publish_date DATE;
1464 v_oldBid NUMBER;
1465 v_rank NUMBER;
1466 v_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
1467 v_tpid pon_bid_headers.trading_partner_id%TYPE;
1468
1469 l_api_name CONSTANT VARCHAR2(30) := 'update_unchanged_rank';
1470
1471 BEGIN
1472
1473 -- logging
1474 print_log(l_api_name || '.BEGIN', ' ');
1475 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
1476 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
1477
1478 --
1479 -- Get the current bid information
1480 --
1481 SELECT publish_date,
1482 trading_partner_id,
1483 trading_partner_contact_id
1484 INTO v_publish_date,
1485 v_tpid,
1486 v_tpcid
1487 FROM pon_bid_headers
1488 WHERE bid_number = p_bidNumber;
1489
1490
1491 -- Get the user's most recent non-disqualified bid...if it exists
1492 SELECT max(bid_number)
1493 INTO v_oldBid
1494 FROM pon_bid_headers
1495 WHERE auction_header_id = p_auctionHeaderId
1496 AND trading_partner_id = v_tpid
1497 AND trading_partner_contact_id = v_tpcid
1498 AND bid_status <> 'DISQUALIFIED'
1499 AND decode (p_discard_tech_nonshort, 'Y', technical_shortlist_flag, 'Y') = 'Y'
1500 AND bid_number <> p_bidNumber
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)
1510 WHERE bidline.bid_number = p_bidNumber
1511 AND bidline.publish_date <> v_publish_date
1512 AND bidline.line_number >= p_batchStart
1513 AND bidline.line_number <= p_batchEnd;
1514 END IF;
1515
1516 print_log(l_api_name || '.END', ' ');
1517
1518 EXCEPTION
1519 -- If the user does not have a previous bid then do nothing...
1520 WHEN NO_DATA_FOUND THEN null;
1521
1522 END update_unchanged_rank;
1523
1524
1525
1526 -- This function checks that PE have not changed in the ReBid Scenario ....
1527 FUNCTION is_PE_Changed(x_oldBidNumber IN NUMBER,
1528 x_newBidNumber IN NUMBER,
1529 x_lineNumber IN NUMBER)
1530 RETURN BOOLEAN IS
1531 return_value NUMBER := 0;
1532 l_api_name CONSTANT VARCHAR2(30) := 'is_PE_Changed';
1533 BEGIN
1534 -- logging
1535 print_log(l_api_name || '.BEGIN', ' ');
1536 print_log(l_api_name, 'x_oldBidNumber=' || x_oldBidNumber);
1537 print_log(l_api_name, 'x_newBidNumber=' || x_newBidNumber);
1538 print_log(l_api_name, 'x_lineNumber=' || x_lineNumber );
1539
1540 select count(*)
1541 into return_value
1542 from PON_BID_PRICE_ELEMENTS b1,PON_BID_PRICE_ELEMENTS b2
1543 where b1.bid_number = x_oldBidNumber
1544 and b1.line_number = x_lineNumber
1545 and b2.bid_number = x_newBidNumber
1546 and b2.line_number = b1.line_number
1547 and b2.SEQUENCE_NUMBER = b1.SEQUENCE_NUMBER
1548 and b2.BID_CURRENCY_VALUE <> b1.BID_CURRENCY_VALUE;
1549
1550 if ( return_value = 0 ) then
1551 print_log(l_api_name || '.END', ' ');
1552 return FALSE;
1553 else
1554 print_log(l_api_name || '.END', ' ');
1555 return TRUE;
1556 end if;
1557
1558 print_log(l_api_name || '.END', ' ');
1559 return FALSE;
1560 EXCEPTION
1561 WHEN NO_DATA_FOUND THEN
1562 print_log(l_api_name || '.END', ' ');
1563 return FALSE;
1564 END;
1565
1566 -- This function checks that MAS have not changed in the ReBid Scenario ....
1567 FUNCTION is_MAS_Changed(x_oldBidNumber IN NUMBER,
1568 x_newBidNumber IN NUMBER,
1569 x_lineNumber IN NUMBER)
1570 RETURN BOOLEAN IS
1571 l_api_name CONSTANT VARCHAR2(30) := 'is_MAS_Changed';
1572 return_value NUMBER := 0;
1573 BEGIN
1574 -- logging
1575 print_log(l_api_name || '.BEGIN', ' ');
1576 print_log(l_api_name, 'x_oldBidNumber=' || x_oldBidNumber);
1577 print_log(l_api_name, 'x_newBidNumber=' || x_newBidNumber);
1578 print_log(l_api_name, 'x_lineNumber=' || x_lineNumber);
1579
1580 select count(*)
1581 into return_value
1582 from PON_BID_ATTRIBUTE_VALUES b1,PON_BID_ATTRIBUTE_VALUES b2
1583 where b1.bid_number = x_oldBidNumber
1584 and b1.line_number = x_lineNumber
1585 and b2.bid_number = x_newBidNumber
1586 and b2.line_number = b1.line_number
1587 and b2.SEQUENCE_NUMBER = b1.SEQUENCE_NUMBER
1588 --and b2.SCORE <> b1.SCORE;
1589 and b2.value <> b1.value;
1590
1591 if ( return_value = 0 ) then
1592 print_log(l_api_name || '.END', ' ');
1593 return FALSE;
1594 else
1595 print_log(l_api_name || '.END', ' ');
1596 return TRUE;
1597 end if;
1598
1599 print_log(l_api_name || '.END', ' ');
1600 return FALSE;
1601 EXCEPTION
1602 WHEN NO_DATA_FOUND THEN
1603 print_log(l_api_name || '.END', ' ');
1604 return FALSE;
1605 END;
1606
1607
1608 FUNCTION get_most_recent_bid_number(x_auction_header_id IN NUMBER,
1609 x_trading_partner_id IN NUMBER,
1610 x_trading_partner_contact_id IN NUMBER)
1611 RETURN NUMBER IS
1612
1613 l_api_name CONSTANT VARCHAR2(30) := 'get_most_recent_bid_number';
1614 x_bid_number NUMBER := NULL;
1615
1616 CURSOR bid_number IS
1617 SELECT bid_number
1618 FROM pon_bid_headers
1619 WHERE auction_header_id = x_auction_header_id
1620 AND trading_partner_id = x_trading_partner_id
1621 AND trading_partner_contact_id = x_trading_partner_contact_id
1622 AND bid_status IN ('ACTIVE', 'DRAFT', 'DISQUALIFIED')
1623 AND Nvl(award_status, 'NONE') <> 'COMMITTED'
1624 ORDER BY decode(bid_status, 'DRAFT', 1, 'ACTIVE', 2, 'DISQUALIFIED', 3) ASC;
1625
1626 BEGIN
1627
1628 -- logging
1629 print_log(l_api_name || '.BEGIN', ' ');
1630 print_log(l_api_name, 'x_auction_header_id=' || x_auction_header_id);
1631 print_log(l_api_name, 'x_trading_partner_id=' || x_trading_partner_id);
1632 print_log(l_api_name, 'x_trading_partner_contact_id=' || x_trading_partner_contact_id);
1633
1634 OPEN bid_number;
1635 FETCH bid_number INTO x_bid_number;
1636 CLOSE bid_number;
1637
1638 print_log(l_api_name || '.END', ' ');
1639 RETURN (x_bid_number);
1640
1641 END get_most_recent_bid_number;
1642
1643
1644 -- this function returns whether x_price1 is better than x_price2
1645 -- in case they are equal, x_price1 will be better only if price2 is
1646 -- a proxy bid and x_price1 is not
1647 --
1648 FUNCTION is_better_proxy_price(x_price1 IN NUMBER,
1649 x_bidNumber IN NUMBER,
1650 x_proxy1 IN VARCHAR2,
1651 x_date1 IN DATE,
1652 x_price2 IN NUMBER,
1653 x_triggerNumber IN NUMBER,
1654 x_date2 IN DATE)
1655 RETURN VARCHAR2 IS
1656 --
1657 t1 VARCHAR2(10) := 'TRUE';
1658 t2 VARCHAR2(10) := 'FALSE';
1659 --
1660 BEGIN
1661
1662 IF (x_price1 = x_price2) THEN
1663 --IF (abs(x_date1 - x_date2) < 3 ) THEN
1664 IF (x_date1 = x_date2 ) THEN
1665 IF (( x_bidNumber = x_triggerNumber ) AND (not (x_proxy1 = 'Y'))) THEN
1666 RETURN t1;
1667 END IF;
1668 --
1669 RETURN t2;
1670 ELSIF (x_date1 > x_date2) THEN
1671 RETURN t2;
1672 END IF;
1673 --
1674 RETURN t1;
1675 END IF;
1676 --
1677 IF (x_price1 < x_price2) THEN
1678 RETURN t1;
1679 END IF;
1680 --
1681 RETURN t2;
1682 END;
1683 --
1684 -- This will be the MAS equivalent of is_better_proxy_price
1685 FUNCTION is_better_proxy_price_by_score(x_price1 IN NUMBER,
1686 x_score1 IN NUMBER,
1687 x_proxy1 IN VARCHAR2,
1688 x_bidNumber IN NUMBER,
1689 x_date1 IN DATE,
1690 x_price2 IN NUMBER,
1691 x_score2 IN NUMBER,
1692 x_triggerNumber IN NUMBER,
1693 x_date2 IN DATE)
1694 RETURN VARCHAR2 IS
1695 --
1696 t1 VARCHAR2(10) := 'TRUE';
1697 t2 VARCHAR2(10) := 'FALSE';
1698 --
1699 BEGIN
1700
1701 IF (x_score1/x_price1 = x_score2/x_price2) THEN
1702 IF (x_date1 = x_date2) THEN
1703 IF (( x_bidNumber = x_triggerNumber ) AND (not (x_proxy1 = 'Y'))) THEN
1704 RETURN t1;
1705 END IF;
1706 --
1707 RETURN t2;
1708 ELSIF (x_date1 > x_date2) THEN
1709 RETURN t2;
1710 END IF;
1711 --
1712 RETURN t1;
1713 END IF;
1714 --
1715 IF ((x_score1/x_price1) > (x_score2/x_price2)) THEN
1716 RETURN t1;
1717 END IF;
1718 --
1719 RETURN t2;
1720 END;
1721 --
1722 --
1723 --
1724 --===================
1725 -- PROCEDURES
1726 --===================
1727 PROCEDURE get_active_bid(p_auctionHeaderId IN NUMBER,
1728 p_tradingPartnerId IN NUMBER,
1729 p_tradingPartnerContactId IN NUMBER,
1730 x_bidNumber OUT NOCOPY NUMBER)
1731 IS
1732 l_api_name CONSTANT VARCHAR2(30) := 'get_active_bid';
1733 BEGIN
1734
1735 -- logging
1736 print_log(l_api_name || '.BEGIN', ' ');
1737 print_log(l_api_name, 'p_auctionHeaderId=' ||p_auctionHeaderId );
1738 print_log(l_api_name, 'p_tradingPartnerId=' || p_tradingPartnerId);
1739 print_log(l_api_name, 'p_tradingPartnerContactId=' || p_tradingPartnerContactId);
1740
1741 SELECT bid_number
1742 INTO x_bidNumber
1743 FROM pon_bid_headers
1744 WHERE auction_header_id = p_auctionHeaderId
1745 AND trading_partner_id = p_tradingPartnerId
1746 AND trading_partner_contact_id = p_tradingPartnerContactId
1747 AND bid_status = 'ACTIVE';
1748
1749 print_log(l_api_name || '.END', ' ');
1750 END get_active_bid;
1751
1752
1753
1754 PROCEDURE cancel_line_proxy
1755 ( p_auctionHeaderId IN NUMBER
1756 , p_bidRanking IN VARCHAR2
1757 , p_lineNumber IN NUMBER
1758 , p_bidNumber IN NUMBER
1759 , p_price IN NUMBER
1760 , p_proxyBidLimitPrice IN NUMBER
1761 , x_status OUT NOCOPY VARCHAR2
1762 )
1763 IS
1764 l_api_name CONSTANT VARCHAR2(30) := 'cancel_line_proxy';
1765 BEGIN
1766
1767 -- logging
1768 print_log(l_api_name || '.BEGIN', ' ');
1769 print_log(l_api_name, 'p_auctionHeaderId=' ||p_auctionHeaderId );
1770 print_log(l_api_name, 'p_bidRanking=' || p_bidRanking);
1771 print_log(l_api_name, 'p_lineNumber=' || p_lineNumber);
1772 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber );
1773 print_log(l_api_name, 'p_price=' || p_price);
1774 print_log(l_api_name, 'p_proxyBidLimitPrice=' || p_proxyBidLimitPrice);
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,
1784 last_update_date = sysdate
1785 WHERE bid_number = p_bidNumber
1786 AND line_number = p_lineNumber;
1787 --
1788 -- if current bid is the winner, reset proxy limit price in item
1789 UPDATE pon_auction_item_prices_all
1790 SET best_bid_proxy_limit_price = best_bid_price
1791 WHERE auction_header_id = p_auctionHeaderId
1792 AND line_number = p_lineNumber
1793 AND best_bid_number = p_bidNumber;
1794
1795 x_status := 'PROXY_CANCELLED';
1796 ELSE
1797 x_status := 'NO_ACTIVE_PROXY_BID';
1798 END IF;
1799 print_log(l_api_name || '.END', ' ');
1800 END cancel_line_proxy;
1801 --
1802 --
1803 PROCEDURE cancel_all_proxy_bid_lines
1804 ( p_auctionHeaderId IN NUMBER
1805 , p_tradingPartnerId IN NUMBER
1806 , p_tradingPartnerContactId IN NUMBER
1807 , x_status OUT NOCOPY VARCHAR2
1808 )
1809 IS
1810 --
1811 l_api_name CONSTANT VARCHAR2(30) := 'cancel_all_proxy_bid_lines';
1812
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;
1822 --
1823 CURSOR c_proxyBids (p_bidNumber NUMBER) IS
1824 SELECT
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
1834 AND paip.auction_header_id = p_auctionHeaderId
1835 AND paha.auction_header_id = p_auctionHeaderId
1836 AND paip.line_number = pbip.line_number
1837 AND pbip.proxy_bid_limit_price IS NOT NULL
1838 AND pbip.price <> pbip.proxy_bid_limit_price
1839 AND nvl(paip.close_bidding_date, paha.close_bidding_date) >= decode (nvl (paha.is_paused, 'N'), 'Y', paha.last_pause_date, sysdate);
1840 --
1841 BEGIN
1842
1843 -- logging
1844 print_log(l_api_name || '.BEGIN', ' ');
1845 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
1846 print_log(l_api_name, 'p_tradingPartnerId=' || p_tradingPartnerId);
1847 print_log(l_api_name, 'p_tradingPartnerContactId=' || p_tradingPartnerContactId);
1848
1849 -- get the lock on the header row
1850 SELECT auction_header_id, bid_ranking, auction_status, DECODE ( NVL( is_paused, 'N'), 'Y', ( sysdate + ( close_bidding_date - last_pause_date ) ), close_bidding_date )
1851 INTO l_auctionHeaderId, l_bidRanking, l_auctionStatus, l_closeBiddingDate
1852 FROM pon_auction_headers_all
1853 WHERE auction_header_id = p_auctionHeaderId
1854 FOR UPDATE OF CLOSE_BIDDING_DATE;
1855 --
1856 BEGIN
1857 -- verify if auction is closed/cancelled etc.
1858 IF SYSDATE > l_closeBiddingDate THEN
1859 x_status := 'AUCTION_CLOSED';
1860 print_log(l_api_name || '.END', ' ');
1861 RETURN;
1862 ELSIF l_auctionStatus = 'CANCELLED' THEN
1863 x_status := 'AUCTION_CANCELLED';
1864 print_log(l_api_name || '.END', ' ');
1865 RETURN;
1866 END IF;
1867 -- get the latest active bid
1868 print_log(l_api_name, 'calling subroutine get_active_bid');
1869 get_active_bid(p_auctionHeaderId, p_tradingPartnerId, p_tradingPartnerContactId, l_bidNumber);
1870 --
1871 EXCEPTION
1872 WHEN NO_DATA_FOUND THEN
1873 x_status := 'NO_ACTIVE_BID';
1874 print_log(l_api_name || '.END', ' ');
1875 RETURN;
1876 END;
1877 --
1878 FOR cur in c_proxyBids(l_bidNumber) LOOP
1879 print_log(l_api_name, 'cursor=' || cur.line_number);
1880 -- cancel proxy on line
1881 print_log(l_api_name, 'calling subroutine cancel_line_proxy');
1882 cancel_line_proxy(p_auctionHeaderId, l_bidRanking, cur.line_Number, l_bidNumber, cur.price, cur.proxy_bid_limit_price, x_status);
1883 END LOOP;
1884 --
1885 x_status := 'ALL_PROXIES_CANCELLED';
1886 --
1887 print_log(l_api_name || '.END', ' ');
1888 --
1889 END cancel_all_proxy_bid_lines;
1890 --
1891 --
1892 PROCEDURE cancel_proxy_bid_line
1893 ( p_auctionHeaderId IN NUMBER
1894 , p_lineNumber IN NUMBER
1895 , p_bidNumber IN NUMBER
1896 , p_tradingPartnerId IN NUMBER
1897 , p_tradingPartnerContactId IN NUMBER
1898 , x_bidNumber OUT NOCOPY NUMBER
1899 , x_status OUT NOCOPY VARCHAR2
1900 )
1901 IS
1902 --
1903 l_api_name CONSTANT VARCHAR2(30) := 'cancel_proxy_bid_line';
1904
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;
1914 --
1915 BEGIN
1916
1917 -- logging
1918 print_log(l_api_name || '.BEGIN', ' ');
1919 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
1920 print_log(l_api_name, 'p_lineNumber=' || p_lineNumber);
1921 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
1922 print_log(l_api_name, 'p_tradingPartnerId=' || p_tradingPartnerId);
1923 print_log(l_api_name, 'p_tradingPartnerContactId=' || p_tradingPartnerContactId);
1924
1925 -- if this is the first run of the procedure, bid number will be 0
1926 IF p_bidNumber = 0 THEN
1927 -- get the lock on the header row
1928 SELECT auction_header_id, bid_ranking, auction_status, DECODE ( NVL( is_paused, 'N'), 'Y', ( sysdate + ( close_bidding_date - last_pause_date ) ), close_bidding_date )
1929 INTO l_auctionHeaderId, l_bidRanking, l_auctionStatus, l_closeBiddingDate
1930 FROM pon_auction_headers_all
1931 WHERE auction_header_id = p_auctionHeaderId
1932 FOR UPDATE OF CLOSE_BIDDING_DATE;
1933 --
1934 BEGIN
1935 -- verify if auction is closed/cancelled etc.
1936 IF SYSDATE > l_closeBiddingDate THEN
1937 x_status := 'AUCTION_CLOSED';
1938 print_log(l_api_name || '.END', ' ');
1939 RETURN;
1940 ELSIF l_auctionStatus = 'CANCELLED' THEN
1941 x_status := 'AUCTION_CANCELLED';
1942 print_log(l_api_name || '.END', ' ');
1943 RETURN;
1944 END IF;
1945 -- get the latest active bid
1946 print_log(l_api_name, 'calling subroutine get_active_bid');
1947 get_active_bid(p_auctionHeaderId, p_tradingPartnerId, p_tradingPartnerContactId, l_bidNumber);
1948 --
1949 EXCEPTION
1950 WHEN NO_DATA_FOUND THEN
1951 x_status := 'NO_ACTIVE_BID';
1952 print_log(l_api_name || '.END', ' ');
1953 RETURN;
1954 END;
1955 ELSE
1956 l_bidNumber := p_bidNumber;
1957 END IF;
1958 x_bidNumber := l_bidNumber;
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
1968 print_log(l_api_name, 'calling subroutine cancel_line_proxy');
1969 cancel_line_proxy(p_auctionHeaderId, l_bidRanking, p_lineNumber, l_bidNumber, l_price, l_proxyBidLimitPrice, x_status);
1970 --
1971 print_log(l_api_name || '.END', ' ');
1972 --
1973 END cancel_proxy_bid_line;
1974 --
1975 --
1976 --
1977 --========================================================================
1978 -- PROCEDURE : check_is_bid_valid PUBLIC
1979 -- PARAMETERS:
1980 -- VERSION : current version 1.x
1981 -- initial version 1.0
1982 -- COMMENT : this will be used by Save Draft routine to validate
1983 -- simple things. This is also called by update_auction_info
1984 --========================================================================
1985
1986 PROCEDURE check_is_bid_valid ( p_auctionHeaderId IN NUMBER,
1987 p_bidNumber IN NUMBER,
1988 p_vendorSiteId IN NUMBER,
1989 p_prevBidNumber IN NUMBER,
1990 p_isRebid IN VARCHAR2,
1991 p_isSavingDraft IN VARCHAR2,
1992 p_surrogBidFlag IN VARCHAR2,
1993 p_publishDate IN DATE,
1994 x_return_status OUT NOCOPY NUMBER,
1995 x_return_code OUT NOCOPY VARCHAR2)
1996
1997 IS
1998 v_oldBidNumber pon_bid_headers.bid_number%TYPE;
1999 v_bidStatus pon_bid_headers.bid_status%TYPE;
2000 v_oldNonProxyBidNumber pon_bid_headers.bid_number%TYPE;
2001 v_recentActiveBidNumber pon_bid_headers.bid_number%TYPE;
2002 v_recentBidStatus pon_bid_headers.bid_status%TYPE;
2003 v_is_paused pon_auction_headers_all.is_paused%TYPE;
2004
2005 v_bidTradingPartnerId pon_bid_headers.trading_partner_id%TYPE;
2006 v_sameCompanyBids NUMBER;
2007 v_sameCompanyDrafts NUMBER;
2008 v_bidFrequencyCode pon_auction_headers_all.bid_frequency_code%TYPE;
2009 v_negotiation_closed_line_num NUMBER;
2010
2011 l_api_name VARCHAR2(40) := 'check_is_bid_valid';
2012
2013 BEGIN
2014
2015 print_log(l_api_name, p_bidNumber || ': begin check_is_bid_valid ' ||
2016 'p_auctionHeaderId = ' || p_auctionHeaderId ||
2017 ', p_bidNumber =' || p_bidNumber ||
2018 ', p_vendorSiteId = ' || p_vendorSiteId ||
2019 ', p_prevBidNumber = ' || p_prevBidNumber ||
2020 ', p_isRebid = ' || p_isRebid ||
2021 ', p_isSavingDraft = ' || p_isSavingDraft ||
2022 ', p_surrogBidFlag = ' || p_surrogBidFlag ||
2023 ', p_publishDate = ' || to_char (p_publishDate, 'dd-mon-yyyy hh24:mi:ss'));
2024 --
2025 -- Get the user's most recent bid
2026 -- (in R12, we use p_prevBidNumber)
2027 --get_previous_bid(p_auctionHeaderId, p_bidNumber, v_oldBidNumber);
2028 v_oldBidNumber := p_prevBidNumber;
2029
2030 -- IS BID ON CLOSED LINES
2031 -- If any negotiation lines have closed while bidding
2032 -- we need to throw an error asking the user to delete
2033 -- the draft
2034 print_log(l_api_name, p_bidNumber || 'Checking if this is nonsurrogate and bid on closed lines.');
2035 IF (p_isSavingDraft = 'N' AND nvl (p_surrogBidFlag, 'N') = 'N') THEN
2036
2037 print_log(l_api_name, p_bidNumber || 'Executing query to find closed lines');
2038
2039 BEGIN
2040 -- To determine if a line is newly added/modified in this bid we use the
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
2050 AND paha.auction_header_id = p_auctionHeaderId
2051 AND pbip.bid_number = p_bidNumber
2052 AND paip.line_number = pbip.auction_line_number
2053 AND pbip.is_changed_line_flag = 'Y'
2054 AND p_publishDate > nvl (paip.close_bidding_date, paha.close_bidding_date)
2055 AND rownum = 1;
2056
2057 print_log(l_api_name, p_bidNumber || 'Found one line that is closed.');
2058
2059 x_return_code := 'BID_ON_CLOSED_LINE';
2060 x_return_status := 1;
2061 RETURN;
2062
2063 EXCEPTION WHEN NO_DATA_FOUND THEN
2064 print_log(l_api_name, p_bidNumber || 'Cound not find any line.');
2065 END;
2066 END IF;
2067
2068 --
2069 -- First check to see if the user's old bid was
2070 -- disqualified by the auctioneer
2071 --
2072 IF (v_oldBidNumber is not null) THEN
2073 SELECT bid_status
2074 INTO v_bidStatus
2075 FROM pon_bid_headers
2076 WHERE bid_number = v_oldBidNumber;
2077
2078 IF(nvl(p_isRebid,'N') = 'Y') THEN
2079
2080 IF(v_bidStatus = 'DISQUALIFIED') THEN
2081 x_return_code := 'DISQ_REBID';
2082 x_return_status := 1;
2083 RETURN;
2084 END IF;
2085 ELSE
2086 IF(v_bidStatus = 'DISQUALIFIED') THEN
2087 v_oldBidNumber := NULL;
2088 END IF;
2089 END IF;
2090 END IF;
2091
2092
2093 get_previous_nonproxy_bid(p_auctionHeaderId,p_bidNumber,p_vendorSiteId, v_oldNonProxyBidNumber);
2094
2095 get_most_recent_active_bid(p_auctionHeaderId,p_bidNumber,v_recentActiveBidNumber, v_recentBidStatus);
2096
2097
2098 --
2099 -- First check to see if the user's old bid is superseeded by
2100 -- another bid from the same user (by using a different session)
2101 --
2102 -- need to raise a rebid_error in case a draft is submitted as a bid in one session
2103 -- and the same draft is tried to be updated in the second session at the same time
2104 --
2105 -- condition 1 is satisfied when
2106 -- the user is bidding on an auction (fresh bid)
2107 -- but in some other session, a bid has been placed (manual bid)
2108 -- which is not placed due to proxy got kicked in
2109
2110 -- new condition (due to draft bid project)
2111 -- prevBidNumber is null (fresh_bid)
2112 -- user is editing a draft (in 2 separate sessions)
2113 -- session one : the user saves the draft as a bid
2114 -- session two : the user tries to save the draft as a draft
2115 -- this condition will be trapped in the 1st condition above
2116
2117 -- condition 2 is satisfied when
2118 -- when user is re-bidding on an auction
2119 -- but in some other session, the same user did re-bid
2120 -- hence got a new non-proxied bid number
2121
2122 IF ((p_prevBidNumber is null) AND
2123 (v_oldNonProxyBidNumber is not null) ) THEN
2124 x_return_code := 'MULTIPLE_REBID';
2125 x_return_status := 1;
2126 RETURN;
2127 ELSE
2128 IF ((p_prevBidNumber is not null) AND
2129 (v_oldNonProxyBidNumber is not null)) THEN
2130 IF (v_oldNonProxyBidNumber > p_prevBidNumber) THEN
2131 x_return_code := 'MULTIPLE_REBID';
2132 x_return_status := 1;
2133 RETURN;
2134 END IF;
2135 END IF;
2136 END IF;
2137
2138
2139
2140 -- need to check the bid status has changed or not
2141 -- since while saving a draft bid as an active bid
2142 -- the bid number does not change
2143
2144 -- check whether following conditions are satisfied:
2145 -- 1. user is editing a draft (in 2 separate sessions)
2146 -- 2. prevBidNumber is not null (re-bid)
2147 -- 3. Draft bid number is same as the most recent active bid from the same user
2148 -- session one : the user saves the draft as an active bid - an active bid exists in the database
2149 -- session two : the user tried to save the draft as a draft
2150 -- the user in session two gets an error
2151
2152 IF(p_isSavingDraft = 'Y' AND
2153 v_recentActiveBidNumber is not null AND
2154 p_bidNumber = v_recentActiveBidNumber AND
2155 v_recentBidStatus = 'ACTIVE') THEN
2156 x_return_code := 'MULTIPLE_DRAFTS';
2157 x_return_status := 1;
2158 RETURN;
2159 END IF;
2160
2161 -- if the auction is a single bid auction and another user
2162 -- from the same company has already bid then throw exception
2163 SELECT bid_frequency_code
2164 INTO v_bidFrequencyCode
2165 FROM pon_auction_headers_all
2166 WHERE auction_header_id = p_auctionHeaderId;
2167
2168 IF v_bidFrequencyCode = 'SINGLE_BID_ONLY' THEN
2169 -- get a hold of company's tp id
2170 SELECT TRADING_PARTNER_ID
2171 INTO v_bidTradingPartnerId
2172 FROM PON_BID_HEADERS
2173 WHERE AUCTION_HEADER_ID = p_auctionHeaderId
2174 AND BID_NUMBER = p_bidNumber;
2175 --
2176 -- check if there is more than one row in pon_bid_headers
2177 -- one from previous user's bid, one ours
2178
2179 SELECT COUNT(bid_number)
2180 INTO v_sameCompanyBids
2181 FROM PON_BID_HEADERS
2182 WHERE AUCTION_HEADER_ID = p_auctionHeaderId
2183 AND BID_STATUS = 'ACTIVE'
2184 AND TRADING_PARTNER_ID = v_bidTradingPartnerId
2185 AND NVL(VENDOR_SITE_ID, -1) = NVL(p_vendorSiteId, -1);
2186 --
2187 IF v_sameCompanyBids > 1 THEN
2188 x_return_code := 'SINGLE_BEST_BID';
2189 x_return_status := 1;
2190 RETURN;
2191 END IF;
2192
2193 -- also check whether there is another draft
2194 -- created in the meanwhile
2195
2196 SELECT COUNT(bid_number)
2197 INTO v_sameCompanyDrafts
2198 FROM PON_BID_HEADERS
2199 WHERE AUCTION_HEADER_ID = p_auctionHeaderId
2200 AND (BID_STATUS = 'ACTIVE' OR BID_STATUS = 'DRAFT')
2201 AND TRADING_PARTNER_ID = v_bidTradingPartnerId
2202 AND NVL(VENDOR_SITE_ID, -1) = NVL(p_vendorSiteId, -1);
2203 --
2204 IF v_sameCompanyDrafts > 1 THEN
2205 x_return_code := 'SINGLE_BEST_DRAFT';
2206 x_return_status := 1;
2207 RETURN;
2208 END IF;
2209
2210 END IF;
2211
2212
2213 -- IS PAUSED
2214 -- if the negotiation is paused and we're trying
2215 -- to publish the bid, then return with an error code.
2216 -- bug 4523484: do this check only for publish; not for savedraft
2217 IF(p_isSavingDraft = 'N') THEN
2218 SELECT nvl(is_paused, 'N')
2219 INTO v_is_paused
2220 FROM pon_auction_headers_all
2221 WHERE auction_header_id = p_auctionHeaderId;
2222
2223 IF (v_is_paused = 'Y') THEN
2224 x_return_code := 'AUCTION_PAUSED';
2225 x_return_status := 1;
2226 RETURN;
2227 END IF;
2228 END IF;
2229
2230 x_return_status := 0;
2231
2232 END check_is_bid_valid;
2233
2234
2235
2236
2237
2238
2239 PROCEDURE update_disq_lines
2240 ( p_auctionHeaderId IN NUMBER,
2241 p_bidNumber IN NUMBER,
2242 p_rankIndicator IN pon_auction_headers_all.rank_indicator%TYPE,
2243 p_bidRanking IN pon_auction_headers_all.bid_ranking%TYPE,
2244 p_tpId IN pon_bid_headers.trading_partner_id%TYPE,
2245 p_tpcId IN pon_bid_headers.trading_partner_contact_id%TYPE,
2246 p_batchStart IN NUMBER,
2247 p_batchEnd IN NUMBER,
2248 p_ignore_tech_nonshortlist IN VARCHAR2)
2249 IS
2250
2251 l_api_name CONSTANT VARCHAR2(30) := 'update_disq_lines';
2252
2253 v_bestBidNumber pon_auction_item_prices_all.lowest_bid_number%TYPE;
2254 v_bestBidBidNumber pon_auction_item_prices_all.best_bid_bid_number%TYPE;
2255
2256 TYPE t_tbl_number IS TABLE OF NUMBER
2257 INDEX BY PLS_INTEGER;
2258
2259 TYPE t_tbl_date IS TABLE OF DATE
2260 INDEX BY PLS_INTEGER;
2261
2262 TYPE t_tbl_varchar IS TABLE OF VARCHAR2(15)
2263 INDEX BY PLS_INTEGER;
2264
2265 t_line_number t_tbl_number;
2266 t_price t_tbl_number;
2267 t_quantity t_tbl_number;
2268 t_promised_date t_tbl_date;
2269 t_bid_number t_tbl_number;
2270 t_bid_currency_price t_tbl_number;
2271 t_bid_currency_code t_tbl_varchar;
2272 t_first_bid_price t_tbl_number;
2273 t_proxy_bid_limit_price t_tbl_number;
2274 t_score t_tbl_number;
2275 t_number_of_bids t_tbl_number;
2276 v_counter PLS_INTEGER;
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
2286 AND pbip.line_number >= p_batchStart
2287 AND pbip.line_number <= p_batchEnd
2288 ORDER BY pbip.line_number;
2289
2290 CURSOR best_bid_lines_cursor IS
2291 SELECT line_number
2292 FROM pon_auction_item_prices_all
2293 WHERE auction_header_id = p_auctionHeaderId
2294 AND best_bid_number = p_bidNumber
2295 AND line_number >= p_batchStart
2296 AND line_number <= p_batchEnd
2297 ORDER BY line_number;
2298
2299 CURSOR best_bid_bid_lines_cursor IS
2300 SELECT line_number
2301 FROM pon_auction_item_prices_all
2302 WHERE auction_header_id = p_auctionHeaderId
2303 AND best_bid_bid_number = p_bidNumber
2304 AND line_number >= p_batchStart
2305 AND line_number <= p_batchEnd
2306 ORDER BY line_number;
2307
2308 BEGIN
2309
2310
2311 print_log(l_api_name, p_bidNumber || ': begin update_disq_lines');
2312
2313 -- UPDATE BEST_BID INFO
2314 -- The disqualified bid has the best bid for the current item line.
2315 -- We need to get a new best bid.
2316 t_line_number.DELETE;
2317 t_price.DELETE;
2318 t_quantity.DELETE;
2319 t_promised_date.DELETE;
2320 t_bid_number.DELETE;
2321 t_bid_currency_price.DELETE;
2322 t_bid_currency_code.DELETE;
2323 t_first_bid_price.DELETE;
2324 t_proxy_bid_limit_price.DELETE;
2325 v_counter := 1;
2326
2327 print_log(l_api_name, p_bidNumber || ': update best bid lines');
2328 FOR best_bid_item_record IN best_bid_lines_cursor LOOP -- {
2329
2330 -- Get auction item price information corresponding to this bid item
2331 SELECT best_bid_number
2332 INTO v_bestBidNumber
2333 FROM pon_auction_item_prices_all
2334 WHERE auction_header_id = p_auctionHeaderId
2335 AND line_number = best_bid_item_record.line_number;
2336
2337 IF (v_bestBidNumber = p_bidNumber) THEN -- {
2338
2339 print_log(l_api_name, 'line ' || best_bid_item_record.line_number
2340 || ': need to update best_bid info');
2341
2342 BEGIN -- {
2343 SELECT line_number,
2344 price,
2345 quantity,
2346 promised_date,
2347 bid_number,
2348 bid_currency_price,
2349 bid_currency_code,
2350 first_bid_price,
2351 proxy_bid_limit_price
2352 INTO t_line_number(v_counter),
2353 t_price(v_counter),
2354 t_quantity(v_counter),
2355 t_promised_date(v_counter),
2356 t_bid_number(v_counter),
2357 t_bid_currency_price(v_counter),
2358 t_bid_currency_code(v_counter),
2359 t_first_bid_price(v_counter),
2360 t_proxy_bid_limit_price(v_counter)
2361 FROM (SELECT bidline.line_number,
2362 bidline.price,
2363 bidline.quantity,
2364 bidline.promised_date,
2365 bidline.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
2375 AND bidheader.bid_status = 'ACTIVE'
2376 AND (
2377 (p_ignore_tech_nonshortlist = 'Y' AND bidheader.technical_shortlist_flag = 'Y')
2378 OR
2379 (p_ignore_tech_nonshortlist = 'N')
2380 )
2381 AND bidheader.bid_number <> p_bidNumber
2382 AND bidline.line_number = best_bid_item_record.line_number
2383 ORDER BY decode(group_amount,null,bidline.price, group_amount), bidline.publish_date asc)
2384 WHERE rownum = 1;
2385 EXCEPTION
2386 WHEN NO_DATA_FOUND THEN
2387 print_log(l_api_name, ' line ' || best_bid_item_record.line_number || ': no replacement best bid was found; nulling out best bid info');
2388 t_line_number(v_counter) := best_bid_item_record.line_number;
2389 t_price(v_counter) := null;
2390 t_quantity(v_counter) := null;
2391 t_promised_date(v_counter) := null;
2392 t_bid_number(v_counter) := null;
2393 t_bid_currency_price(v_counter) := null;
2394 t_bid_currency_code(v_counter) := null;
2395 t_first_bid_price(v_counter) := null;
2396 t_proxy_bid_limit_price(v_counter) := null;
2397 END; -- }
2398
2399 v_counter := v_counter + 1;
2400
2401 END IF; -- }
2402 END LOOP; -- }
2403
2404 print_log(l_api_name, p_bidNumber || ': updating best bid info with data structs');
2405 FORALL x IN 1..t_line_number.COUNT
2406 UPDATE pon_auction_item_prices_all
2407 SET best_bid_price = t_price(x),
2408 best_bid_quantity = t_quantity(x),
2409 best_bid_promised_date = t_promised_date(x),
2410 best_bid_number = t_bid_number(x),
2411 best_bid_currency_price = t_bid_currency_price(x),
2412 best_bid_currency_code = t_bid_currency_code(x),
2413 best_bid_first_bid_price = t_first_bid_price(x),
2414 best_bid_proxy_limit_price = t_proxy_bid_limit_price(x)
2415 WHERE auction_header_id = p_auctionHeaderId
2416 AND line_number = t_line_number(x);
2417
2418
2419 -- UPDATE BEST_BID_BID_INFO
2420 -- The disqualified bid has the best bid for the current item line.
2421 -- We need to get a new best bid.
2422 IF(p_bidRanking = 'MULTI_ATTRIBUTE_SCORING') THEN
2423 print_log(l_api_name, p_bidNumber || ': updating best bid bid information');
2424 t_line_number.DELETE;
2425 t_price.DELETE;
2426 t_bid_number.DELETE;
2427 t_bid_currency_price.DELETE;
2428 t_bid_currency_code.DELETE;
2429 t_score.DELETE;
2430 v_counter := 1;
2431 FOR best_bid_bid_item_record IN best_bid_bid_lines_cursor LOOP
2432
2433 SELECT best_bid_bid_number
2434 INTO v_bestBidBidNumber
2435 FROM pon_auction_item_prices_all
2436 WHERE auction_header_id = p_auctionHeaderId
2437 AND line_number = best_bid_bid_item_record.line_number;
2438
2439 IF (v_bestBidBidNumber = p_bidNumber) THEN
2440
2441 BEGIN
2442 SELECT line_number,
2443 price,
2444 total_weighted_score,
2445 bid_number,
2446 bid_currency_price,
2447 bid_currency_code
2448 INTO t_line_number(v_counter),
2449 t_price(v_counter),
2450 t_score(v_counter),
2451 t_bid_number(v_counter),
2452 t_bid_currency_price(v_counter),
2453 t_bid_currency_code(v_counter)
2454 FROM (SELECT bidline.line_number,
2455 bidline.price,
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
2465 AND bidheader.bid_status = 'ACTIVE'
2466 AND (
2467 (p_ignore_tech_nonshortlist = 'Y' AND bidheader.technical_shortlist_flag = 'Y')
2468 OR
2469 (p_ignore_tech_nonshortlist = 'N')
2470 )
2471 AND bidheader.bid_number <> p_bidNumber
2472 AND bidline.line_number = best_bid_bid_item_record.line_number
2473 ORDER BY decode(bidline.group_amount, null, bidline.total_weighted_score/bidline.price, -bidline.group_amount) desc,
2474 bidline.publish_date asc)
2475 WHERE rownum = 1;
2476 EXCEPTION
2477 WHEN NO_DATA_FOUND THEN
2478 print_log(l_api_name, ' line ' || best_bid_bid_item_record.line_number || ': no replacement best bid was found; nulling out best bid info');
2479 t_line_number(v_counter) := best_bid_bid_item_record.line_number;
2480 t_price(v_counter) := null;
2481 t_score(v_counter) := null;
2482 t_bid_number(v_counter) := null;
2483 t_bid_currency_price(v_counter) := null;
2484 t_bid_currency_code(v_counter) := null;
2485 END;
2486
2487 v_counter := v_counter + 1;
2488
2489 END IF;
2490 END LOOP;
2491
2492 print_log(l_api_name, p_bidNumber || ': updating best bid bid info with data structs');
2493 FORALL x IN 1..t_line_number.COUNT
2494 UPDATE pon_auction_item_prices_all
2495 SET best_bid_bid_price = t_price(x),
2496 best_bid_score = t_score(x),
2497 best_bid_bid_number = t_bid_number(x),
2498 best_bid_bid_currency_price = t_bid_currency_price(x),
2499 best_bid_bid_currency_code = t_bid_currency_code(x)
2500 WHERE auction_header_id = p_auctionHeaderId
2501 AND line_number = t_line_number(x);
2502
2503 END IF;
2504
2505 -- For each item line in the bid, update the best price and rank
2506 print_log(l_api_name, p_bidNumber || ': for each line, update the numOfBids and rank');
2507 t_line_number.DELETE;
2508 t_number_of_bids.DELETE;
2509 v_counter := 1;
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)
2519 AND bh.bid_number = bip.bid_number
2520 AND bh.publish_date = bip.publish_date
2521 AND bip.line_number = bid_item_record.line_number),
2522 line_number
2523 INTO t_number_of_bids(v_counter),
2524 t_line_number(v_counter)
2525 FROM pon_auction_item_prices_all
2526 WHERE auction_header_id = p_auctionHeaderId
2527 AND line_number = bid_item_record.line_number;
2528
2529 -- UPDATE RANK FOR BIDS LOWER THAN THE DISQ BID
2530 -- (uday) If the bid getting disqualified is a technically non-shortlisted bid
2531 -- then this update should not happen provided the RFQ is already commercially
2532 -- unlocked
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
2542 FROM pon_bid_headers h
2543 WHERE h.bid_number = bip.bid_number
2544 AND h.bid_status = 'ACTIVE')
2545 AND rank > bid_item_record.rank;
2546
2547 END IF; -- }
2548
2549 v_counter := v_counter + 1;
2550 END LOOP; -- }
2551
2552 print_log(l_api_name, p_bidNumber || ': for each line, update the numOfBids and rank using structs');
2553 FORALL x IN 1..t_line_number.COUNT
2554 UPDATE pon_auction_item_prices_all
2555 SET number_of_bids = t_number_of_bids(x)
2556 WHERE auction_header_id = p_auctionHeaderId
2557 AND line_number = t_line_number(x);
2558
2559 print_log(l_api_name, p_bidNumber || ': END update_disq_lines');
2560 END update_disq_lines;
2561
2562 PROCEDURE update_disq_lines_batched
2563 ( p_auctionHeaderId IN NUMBER,
2564 p_bidNumber IN NUMBER,
2565 p_rankIndicator IN pon_auction_headers_all.rank_indicator%TYPE,
2566 p_bidRanking IN pon_auction_headers_all.bid_ranking%TYPE,
2567 p_tpId IN pon_bid_headers.trading_partner_id%TYPE,
2568 p_tpcId IN pon_bid_headers.trading_partner_contact_id%TYPE,
2569 p_maxLineNumber IN NUMBER,
2570 p_batchSize IN NUMBER,
2571 p_ignore_tech_nonshortlist IN VARCHAR2)
2572 IS
2573 PRAGMA AUTONOMOUS_TRANSACTION;
2574
2575 v_batchStart NUMBER;
2576 v_batchEnd NUMBER;
2577 l_api_name CONSTANT VARCHAR2(30) := 'update_disq_lines_batched';
2578 BEGIN
2579 print_log(l_api_name, p_bidNumber || ': BEGIN update_disq_lines_batched');
2580 v_batchStart := 1;
2581 v_batchEnd := p_batchSize;
2582
2583 WHILE (v_batchStart <= p_maxLineNumber) LOOP
2584
2585 update_disq_lines(p_auctionHeaderId, p_bidNumber, p_rankIndicator, p_bidRanking,
2586 p_tpId, p_tpcId, v_batchStart, v_batchEnd, p_ignore_tech_nonshortlist);
2587 commit;
2588
2589 v_batchStart := v_batchEnd + 1;
2590 IF (v_batchEnd + p_batchSize > p_maxLineNumber) THEN
2591 v_batchEnd := p_maxLineNumber;
2592 ELSE
2593 v_batchEnd := v_batchEnd + p_batchSize;
2594 END IF;
2595 END LOOP;
2596 print_log(l_api_name, p_bidNumber || ': END update_disq_lines_batched');
2597
2598 END update_disq_lines_batched;
2599
2600
2601
2602 PROCEDURE update_auction_info_disqualify
2603 ( p_auctionHeaderId IN NUMBER,
2604 p_bidNumber IN NUMBER)
2605 IS
2606
2607 l_api_name CONSTANT VARCHAR2(30) := 'update_auction_info_disqualify';
2608
2609 v_tpcId pon_bid_headers.trading_partner_contact_id%TYPE;
2610 v_tpId pon_bid_headers.trading_partner_id%TYPE;
2611 v_rankIndicator pon_auction_headers_all.rank_indicator%TYPE;
2612 v_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
2613 v_maxLineNumber NUMBER;
2614 v_batchSize NUMBER;
2615 v_batchingRequired BOOLEAN;
2616
2617 v_numOfBids NUMBER;
2618
2619 v_two_part_flag pon_auction_headers_all.two_part_flag%TYPE;
2620 v_sealed_auction_status pon_auction_headers_all.sealed_auction_status%TYPE;
2621 v_ignore_tech_nonshortlist VARCHAR2(1);
2622 BEGIN
2623
2624
2625 -- logging
2626 print_log(l_api_name || '.BEGIN', ' ');
2627 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
2628 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
2629
2630 print_log(l_api_name, 'BEGIN update_auction_info_disqualify for p_bidNumber=' || p_bidNumber);
2631
2632 -- Retrieve information from auction header
2633 SELECT rank_indicator,
2634 bid_ranking,
2635 max_internal_line_num,
2636 nvl (two_part_flag, 'N'),
2637 sealed_auction_status
2638 INTO v_rankIndicator,
2639 v_bidRanking,
2640 v_maxLineNumber,
2641 v_two_part_flag,
2642 v_sealed_auction_status
2643 FROM pon_auction_headers_all
2644 WHERE auction_header_id = p_auctionHeaderId
2645 FOR UPDATE OF CLOSE_BIDDING_DATE;
2646
2647 print_log(l_api_name, 'rank_indicator = ' || v_rankIndicator ||
2648 ', bid_ranking = ' || v_bidRanking ||
2649 ', max_internal_line_num = ' || v_maxLineNumber ||
2650 ', two_part_flag = ' || v_two_part_flag ||
2651 ', sealed_auction_status = ' || v_sealed_auction_status);
2652
2653 v_ignore_tech_nonshortlist := 'N';
2654
2655 -- (uday) If a quote is getting disqualified after commercial unlock
2656 -- has happened then during re-ranking the technically non-shortlisted
2657 -- quotes should be ignored. (Two-Part RFQ Project)
2658 IF (v_two_part_flag = 'Y' AND v_sealed_auction_status <> 'LOCKED') THEN --{
2659 v_ignore_tech_nonshortlist := 'Y';
2660 END IF; --}
2661
2662 SELECT trading_partner_contact_id,
2663 trading_partner_id
2664 INTO v_tpcId,
2665 v_tpId
2666 FROM pon_bid_headers
2667 WHERE bid_number = p_bidNumber;
2668
2669 -- UPDATE AUCTION'S NUMBER OF BIDS
2670 -- BUG: 1540882
2671 -- Actually all archived bids are disqualified as well.
2672 -- +1 is for the currect active bid that is being disqualified.
2673 SELECT number_of_bids
2674 INTO v_numOfBids
2675 FROM pon_auction_headers_all
2676 WHERE auction_header_id = p_auctionHeaderId;
2677 print_log(l_api_name, p_bidNumber || ': original numOfBids=' || v_numOfBids);
2678
2679 UPDATE pon_auction_headers_all
2680 SET last_update_date = sysdate,
2681 number_of_bids = (number_of_bids -
2682 (SELECT count(*)+1
2683 FROM pon_bid_headers
2684 WHERE auction_header_id = p_auctionHeaderId
2685 AND trading_partner_contact_id = v_tpcId
2686 AND bid_status = 'ARCHIVED'))
2687 WHERE auction_header_id = p_auctionHeaderId;
2688
2689 SELECT number_of_bids
2690 INTO v_numOfBids
2691 FROM pon_auction_headers_all
2692 WHERE auction_header_id = p_auctionHeaderId;
2693 print_log(l_api_name, p_bidNumber || ': new numOfBids=' || v_numOfBids);
2694
2695 -- for batching, we need to find out the max line number of
2696 -- this neg
2697 v_batchSize := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
2698 v_batchingRequired := (v_maxLineNumber > v_batchSize);
2699
2700 IF (v_batchingRequired) THEN
2701 print_log(l_api_name, p_bidNumber || ': calling update_disq_lines_batched');
2702 update_disq_lines_batched(p_auctionHeaderId, p_bidNumber, v_rankIndicator, v_bidRanking,
2703 v_tpId, v_tpcId, v_maxLineNumber, v_batchSize, v_ignore_tech_nonshortlist);
2704 ELSE
2705 print_log(l_api_name, p_bidNumber || ': calling update_disq_lines');
2706 update_disq_lines(p_auctionHeaderId, p_bidNumber, v_rankIndicator, v_bidRanking,
2707 v_tpId, v_tpcId, 1, v_maxLineNumber, v_ignore_tech_nonshortlist);
2708 END IF;
2709
2710 -- DISQ ALL PREVIOUS ARCHIVED BIDS
2711 print_log(l_api_name, p_bidNumber || ': disqualifying previous archived bids');
2712 UPDATE pon_bid_headers
2713 SET bid_status = 'DISQUALIFIED'
2714 WHERE auction_header_id = p_auctionHeaderId
2715 AND trading_partner_contact_id = v_tpcId
2716 AND bid_status = 'ARCHIVED';
2717
2718 -- DELIVERABLES INTEGRATION
2719 -- Contracts - fpj project
2720 -- once the current bid is disqualified,
2721 -- we need to cancel all the deliverables on the bid
2722 -- rrkulkar
2723 IF(PON_CONTERMS_UTL_PVT.is_contracts_installed = FND_API.G_TRUE) then
2724 PON_CONTERMS_UTL_PVT.disqualifyDeliverables(p_bidNumber);
2725 END IF;
2726
2727 print_log(l_api_name, p_bidNumber || ': END disqualify');
2728 print_log(l_api_name || '.END', ' ');
2729
2730 END update_auction_info_disqualify;
2731
2732
2733 procedure update_rank
2734 (
2735 p_auctionHeaderId IN NUMBER,
2736 p_bidNumber IN NUMBER,
2737 p_lineNumber IN NUMBER,
2738 p_scoring_method IN VARCHAR2,
2739 p_auction_type IN VARCHAR2,
2740 p_oldRank IN NUMBER,
2741 p_price IN NUMBER,
2742 p_score IN NUMBER,
2743 p_proxy IN VARCHAR2,
2744 p_date IN DATE
2745 )
2746 IS
2747 v_new_rank NUMBER;
2748 l_api_name CONSTANT VARCHAR2(30) := 'update_rank';
2749 BEGIN
2750 --
2751 -- logging
2752 print_log(l_api_name || '.BEGIN', ' ');
2753 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
2754 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
2755 print_log(l_api_name, 'p_lineNumber=' || p_lineNumber);
2756 print_log(l_api_name, 'p_scoring_method=' || p_scoring_method);
2757 print_log(l_api_name, 'p_auction_type=' || p_auction_type);
2758 print_log(l_api_name, 'p_oldRank=' || p_oldRank);
2759 print_log(l_api_name, 'p_price=' || p_price);
2760 print_log(l_api_name, 'p_score=' || p_score);
2761 print_log(l_api_name, 'p_proxy=' || p_proxy);
2762 print_log(l_api_name, 'p_date=' || p_date);
2763
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
2773 and h.bid_status = 'ACTIVE'
2774 and h.bid_number <> p_bidNumber
2775 and decode(p_scoring_method, 'MULTI_ATTRIBUTE_SCORING',
2776 is_better_proxy_price_by_score(p_price,
2777 p_score,
2778 p_proxy,
2779 p_bidNumber,
2780 p_date,
2781 nvl(ip.group_amount,ip.price),
2782 ip.total_weighted_score,
2783 ip.trigger_bid_number,
2784 ip.publish_date)
2785 ,
2786 is_better_proxy_price(p_price,
2787 p_bidNumber,
2788 p_proxy,
2789 p_date,
2790 nvl(ip.group_amount,ip.price),
2791 ip.trigger_bid_number,
2792 ip.publish_date)
2793 ) = 'FALSE';
2794 --
2795 -- and decode (p_scoring_method, 'MULTI_ATTRIBUTE_SCORING', p_score / p_price , DECODE(p_auction_type, 'REVERSE', ip.price, p_price)) <=
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
2805 from pon_bid_headers h
2806 where h.bid_number = ip.bid_number
2807 and h.bid_status = 'ACTIVE')
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
2817 from pon_bid_headers h
2818 where h.bid_number = ip.bid_number
2819 and h.bid_status = 'ACTIVE')
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;
2829 --
2830
2831 print_log(l_api_name || '.END', ' ');
2832 --
2833 END;
2834
2835
2836 FUNCTION get_bid_break_price(p_bid_number IN NUMBER,
2837 p_line_number IN NUMBER,
2838 p_ship_to_org IN NUMBER,
2839 p_ship_to_loc IN NUMBER,
2840 p_quantity IN NUMBER,
2841 p_need_by_date IN DATE)
2842 RETURN NUMBER IS
2843 l_api_name CONSTANT VARCHAR2(30) := 'get_bid_break_price';
2844 x_price NUMBER := NULL;
2845
2846 CURSOR break_price IS
2847 SELECT pbs.unit_price
2848 FROM pon_bid_shipments pbs
2849 WHERE pbs.shipment_type = 'PRICE BREAK'
2850 AND pbs.bid_number = p_bid_number
2851 AND pbs.line_number = p_line_number
2852 AND nvl(pbs.quantity, 0) <= nvl(p_quantity, 0)
2853 AND ((p_ship_to_org = pbs.ship_to_organization_id) OR
2854 (pbs.ship_to_organization_id is null))
2855 AND ((p_ship_to_loc = pbs.ship_to_location_id) OR
2856 (pbs.ship_to_location_id is null))
2857 AND (p_need_by_date IS NULL OR
2858 ((trunc(p_need_by_date) >= pbs.effective_start_date OR
2859 pbs.effective_start_date is null)
2860 AND
2861 (trunc(p_need_by_date) <= pbs.effective_end_date OR
2862 pbs.effective_end_date is null)))
2863 ORDER BY pbs.ship_to_organization_id ASC, pbs.ship_to_location_id ASC,
2864 NVL(pbs.quantity,-1) DESC,
2865 pbs.price ASC;
2866
2867 BEGIN
2868 -- logging
2869 print_log(l_api_name || '.BEGIN', ' ');
2870 print_log(l_api_name, 'p_bid_number=' || p_bid_number);
2871 print_log(l_api_name, 'p_line_number=' || p_line_number);
2872 print_log(l_api_name, 'p_ship_to_org=' || p_ship_to_org);
2873 print_log(l_api_name, 'p_ship_to_loc=' || p_ship_to_loc);
2874 print_log(l_api_name, 'p_quantity=' || p_quantity);
2875 print_log(l_api_name, 'p_need_by_date=' ||p_need_by_date );
2876
2877 OPEN break_price;
2878 FETCH break_price INTO x_price;
2879 CLOSE break_price;
2880
2881 print_log(l_api_name || '.END', ' ');
2882
2883 RETURN (x_price);
2884
2885
2886 END get_bid_break_price;
2887
2888
2889 FUNCTION get_bid_break_price_with_pe(p_bid_number IN NUMBER,
2890 p_line_number IN NUMBER,
2891 p_ship_to_org IN NUMBER,
2892 p_ship_to_loc IN NUMBER,
2893 p_quantity IN NUMBER,
2894 p_need_by_date IN DATE)
2895 RETURN NUMBER IS
2896 l_api_name CONSTANT VARCHAR2(30) := 'get_bid_break_price_with_pe';
2897 x_price NUMBER := NULL;
2898
2899 CURSOR break_price IS
2900 SELECT pbs.price
2901 FROM pon_bid_shipments pbs
2902 WHERE pbs.shipment_type = 'PRICE BREAK'
2903 AND pbs.bid_number = p_bid_number
2904 AND pbs.line_number = p_line_number
2905 AND nvl(pbs.quantity, 0) <= nvl(p_quantity, 0)
2906 AND ((p_ship_to_org = pbs.ship_to_organization_id) OR
2907 (pbs.ship_to_organization_id is null))
2908 AND ((p_ship_to_loc = pbs.ship_to_location_id) OR
2909 (pbs.ship_to_location_id is null))
2910 AND (p_need_by_date IS NULL OR
2911 ((trunc(p_need_by_date) >= pbs.effective_start_date OR
2912 pbs.effective_start_date is null)
2913 AND
2914 (trunc(p_need_by_date) <= pbs.effective_end_date OR
2915 pbs.effective_end_date is null)))
2916 ORDER BY pbs.ship_to_organization_id ASC, pbs.ship_to_location_id ASC,
2917 NVL(pbs.quantity,-1) DESC,
2918 pbs.price ASC;
2919
2920 BEGIN
2921 -- logging
2922 print_log(l_api_name || '.BEGIN', ' ');
2923 print_log(l_api_name, 'p_bid_number=' || p_bid_number);
2924 print_log(l_api_name, 'p_line_number=' || p_line_number);
2925 print_log(l_api_name, 'p_ship_to_org=' || p_ship_to_org);
2926 print_log(l_api_name, 'p_ship_to_loc=' || p_ship_to_loc);
2927 print_log(l_api_name, 'p_quantity=' || p_quantity);
2928 print_log(l_api_name, 'p_need_by_date=' ||p_need_by_date );
2929
2930 OPEN break_price;
2931 FETCH break_price INTO x_price;
2932 CLOSE break_price;
2933
2934 print_log(l_api_name || '.END', ' ');
2935
2936 RETURN (x_price);
2937
2938
2939 END get_bid_break_price_with_pe;
2940
2941
2942 /*
2943 * Procedure to validate the price precision of a number.
2944 */
2945 FUNCTION validate_price_precision(p_number IN NUMBER, p_precision IN NUMBER) RETURN BOOLEAN IS
2946 BEGIN
2947
2948 IF p_precision = 10000 THEN
2949 RETURN TRUE;
2950 ELSE
2951 RETURN MOD(MOD(ABS(p_number), 1) * POWER(10, p_precision), 1) = 0;
2952 END IF;
2953
2954 END validate_price_precision;
2955
2956 /*
2957 * Procedure to validate the currency precision of a number.
2958 */
2959 FUNCTION validate_currency_precision(p_number IN NUMBER, p_precision IN NUMBER) RETURN BOOLEAN IS
2960 BEGIN
2961
2962 RETURN MOD(MOD(ABS(p_number), 1) * POWER(10, p_precision), 1) = 0;
2963
2964 END validate_currency_precision;
2965
2966 /*
2967 * Procedure to apply a bid line's price factors to a price for a given entity level.
2968 * Entity level can either be LINE or SHIPMENT.
2969 *
2970 * Return the total price resulting from the application of the bid line's price factors to the price.
2971 *
2972 * see also BidItemPricesEOImpl.applyPriceFactors() and BidItemPricesEOImpl.transformPrice()
2973 */
2974 FUNCTION apply_price_factors(p_auction_header_id IN NUMBER,
2975 p_line_number IN NUMBER,
2976 p_bid_number IN NUMBER,
2977 p_price IN NUMBER,
2978 p_bid_quantity IN NUMBER,
2979 p_trading_partner_id IN NUMBER,
2980 p_vendor_site_id IN NUMBER,
2981 p_rate IN NUMBER,
2982 p_price_precision IN NUMBER,
2983 p_currency_precision IN NUMBER,
2984 p_entity_level IN VARCHAR2)
2985 RETURN NUMBER IS
2986 l_api_name CONSTANT VARCHAR2(30) := 'apply_price_factors';
2987 l_total_price NUMBER;
2988 l_bid_pf_unit_price NUMBER;
2989 l_unit_auc_pf_fixed_amount NUMBER;
2990
2991 l_contract_type VARCHAR2(25);
2992 l_full_quantity_bid_code VARCHAR2(25);
2993 l_order_type_lookup_code VARCHAR2(25);
2994 l_auction_quantity NUMBER;
2995 l_auc_pf_unit_price_formula NUMBER;
2996 l_auc_pf_fixed_amount_formula NUMBER;
2997 l_auc_pf_percentage_formula NUMBER;
2998
2999 bid_quantity_required BOOLEAN;
3000 valid_bid_quantity_specified BOOLEAN;
3001
3002 -- cursor to select the values of the supplier price factors (other than Line Price) on the bid
3003 -- buyer price factors will be accounted for separately using the buyer pf formulas on the line-level
3004 CURSOR l_price_element_list IS
3005 SELECT
3006 price_element_type_id,
3007 pricing_basis,
3008 bid_currency_value
3009 FROM pon_bid_price_elements bid_pfs
3010 WHERE
3011 bid_pfs.bid_number = p_bid_number
3012 AND bid_pfs.line_number = p_line_number
3013 AND bid_pfs.price_element_type_id <> -10
3014 AND bid_pfs.pf_type = 'SUPPLIER';
3015 BEGIN
3016
3017 -- logging
3018 print_log(l_api_name || '.BEGIN', ' ');
3019 print_log(l_api_name, 'p_auction_header_id=' || p_auction_header_id);
3020 print_log(l_api_name, 'p_line_number=' || p_line_number);
3021 print_log(l_api_name, 'p_bid_number=' || p_bid_number);
3022 print_log(l_api_name, 'p_price=' || p_price);
3023 print_log(l_api_name, 'p_bid_quantity=' || p_bid_quantity);
3024 print_log(l_api_name, 'p_trading_partner_id=' || p_trading_partner_id);
3025 print_log(l_api_name, 'p_vendor_site_id=' || p_vendor_site_id);
3026 print_log(l_api_name, 'p_rate=' || p_rate);
3027 print_log(l_api_name, 'p_price_precision=' || p_price_precision);
3028 print_log(l_api_name, 'p_currency_precision=' || p_currency_precision);
3029 print_log(l_api_name, 'p_entity_level=' || p_entity_level);
3030
3031 IF p_entity_level = 'SHIPMENT' THEN
3032 -- if the price argument is null or is negative or has an invalid precision
3033 -- cannot apply price factors to calculate total price
3034 IF p_price IS NULL OR p_price < 0 OR NOT(validate_price_precision(p_price, p_price_precision)) THEN
3035 RETURN NULL;
3036 END IF;
3037 ELSE
3038 -- if the price argument is null or is non-positive or has an invalid precision
3039 -- cannot apply price factors to calculate total price
3040 IF p_price IS NULL OR p_price <= 0 OR NOT(validate_price_precision(p_price, p_price_precision)) THEN
3041 RETURN NULL;
3042 END IF;
3043 END IF;
3044
3045 -- get auction header/line information
3046 SELECT
3047 auctions.contract_type,
3048 auctions.full_quantity_bid_code,
3049 items.order_type_lookup_code,
3050 items.quantity,
3051 NVL(pf_formula.unit_price, 0) pf_unit_price,
3052 NVL(pf_formula.fixed_amount, 0) pf_fixed_amount,
3053 NVL(pf_formula.percentage, 1) pf_percentage
3054 INTO
3055 l_contract_type,
3056 l_full_quantity_bid_code,
3057 l_order_type_lookup_code,
3058 l_auction_quantity,
3059 l_auc_pf_unit_price_formula,
3060 l_auc_pf_fixed_amount_formula,
3061 l_auc_pf_percentage_formula
3062 FROM
3063 pon_auction_headers_all auctions,
3064 pon_auction_item_prices_all items,
3065 pon_pf_supplier_formula pf_formula
3066 WHERE
3067 auctions.auction_header_id = p_auction_header_id
3068 AND items.line_number = p_line_number
3069 AND auctions.auction_header_id = items.auction_header_id
3070 AND pf_formula.auction_header_id(+) = items.auction_header_id
3071 AND pf_formula.line_number(+) = items.line_number
3072 AND pf_formula.trading_partner_id(+) = p_trading_partner_id
3073 AND pf_formula.vendor_site_id(+) = p_vendor_site_id;
3074
3075 -- if bid quantity is required and it cannot be determined
3076 -- cannot apply price factors to calculate total price
3077 IF l_contract_type NOT IN ('BLANKET', 'CONTRACT') AND
3078 l_full_quantity_bid_code <> 'FULL_QTY_BIDS_REQD' AND
3079 l_order_type_lookup_code NOT IN ('FIXED PRICE', 'RATE', 'AMOUNT') THEN
3080 IF p_bid_quantity IS NULL OR p_bid_quantity <= 0 THEN
3081 RETURN NULL;
3082 END IF;
3083 END IF;
3084
3085 -- determine whether bid quantity is required
3086 IF l_contract_type NOT IN ('BLANKET', 'CONTRACT') AND
3087 l_full_quantity_bid_code <> 'FULL_QTY_BIDS_REQD' AND
3088 l_order_type_lookup_code NOT IN ('FIXED PRICE', 'RATE', 'AMOUNT') THEN
3089 bid_quantity_required := TRUE;
3090 ELSE
3091 bid_quantity_required := FALSE;
3092 END IF;
3093
3094 -- determine whether a valid bid quantity is specified
3095 valid_bid_quantity_specified := p_bid_quantity IS NOT NULL AND p_bid_quantity > 0;
3096
3097 -- if there exists a supplier price factor other than Line Price:
3098 -- 1) for which a value is not specified
3099 -- 2) whose value is negative
3100 -- 3) of pricing basis PER_UNIT or FIXED_AMOUNT whose value has an invalid precision
3101 -- 4) of pricing basis FIXED_AMOUNT for which a bid quantity is required but is not specified
3102 -- cannot apply price factors to calculate total price
3103 FOR l_price_element IN l_price_element_list LOOP
3104
3105 IF l_price_element.bid_currency_value IS NULL OR l_price_element.bid_currency_value < 0 THEN
3106 RETURN NULL;
3107 END IF;
3108
3109 IF (l_price_element.pricing_basis = 'PER_UNIT' AND
3110 NOT(validate_price_precision(l_price_element.bid_currency_value, p_price_precision))) OR
3111 (l_price_element.pricing_basis = 'FIXED_AMOUNT' AND
3112 NOT(validate_currency_precision(l_price_element.bid_currency_value, p_currency_precision))) THEN
3113 RETURN NULL;
3114 END IF;
3115
3116 IF bid_quantity_required AND NOT(valid_bid_quantity_specified) THEN
3117 IF l_price_element.pricing_basis = 'FIXED_AMOUNT' THEN
3118 RETURN NULL;
3119 END IF;
3120 END IF;
3121
3122 END LOOP;
3123
3124 l_total_price := 0;
3125
3126 -- STEP 1: account for supplier price factors (except Line Price)
3127
3128 FOR l_price_element IN l_price_element_list LOOP
3129
3130 -- PER_UNIT pricing basis
3131 IF l_price_element.pricing_basis = 'PER_UNIT' THEN
3132 l_bid_pf_unit_price := l_price_element.bid_currency_value;
3133 -- PERCENTAGE pricing basis
3134 ELSIF l_price_element.pricing_basis = 'PERCENTAGE' THEN
3135 l_bid_pf_unit_price := l_price_element.bid_currency_value * p_price / 100;
3136 -- FIXED_AMOUNT pricing basis
3137 ELSIF l_price_element.pricing_basis = 'FIXED_AMOUNT' THEN
3138 -- FIXED PRICE based line type
3139 IF l_order_type_lookup_code = 'FIXED PRICE' THEN
3140 l_bid_pf_unit_price := l_price_element.bid_currency_value;
3141 -- RATE based line type
3142 ELSIF l_order_type_lookup_code = 'RATE' THEN
3143 l_bid_pf_unit_price := l_price_element.bid_currency_value / l_auction_quantity;
3144 -- AMOUNT based line type
3145 ELSIF l_order_type_lookup_code = 'AMOUNT' THEN
3146 l_bid_pf_unit_price := l_price_element.bid_currency_value / l_auction_quantity;
3147 -- other line types
3148 ELSE
3149 -- if negotiation is either a BPA or CPA or requires full bid quantity
3150 -- use the auction quantity
3151 IF l_contract_type IN ('BLANKET', 'CONTRACT') OR
3152 l_full_quantity_bid_code = 'FULL_QTY_BIDS_REQD' THEN
3153 l_bid_pf_unit_price := l_price_element.bid_currency_value / l_auction_quantity;
3154 -- otherwise, use the bid quantity
3155 ELSE
3156 l_bid_pf_unit_price := l_price_element.bid_currency_value / p_bid_quantity;
3157 END IF;
3158 END IF;
3159 END IF;
3160
3161 -- add the pf bid unit price to the total price
3162 l_total_price := l_total_price + l_bid_pf_unit_price;
3163
3164 END LOOP;
3165
3166 -- STEP 2: account for price argument and buyer price factors
3167 -- in this step, we will make use of the buyer price factor transform values for the line
3168
3169 -- for the FIXED_AMOUNT transform value
3170 -- we have to calculate its unit value based on the line type
3171 -- before we can account for it in the total price
3172
3173 IF l_auc_pf_fixed_amount_formula IS NULL OR l_auc_pf_fixed_amount_formula = 0 THEN
3174 -- if there is no fixed amount formula, the unit fixed amount is simply 0
3175 l_unit_auc_pf_fixed_amount := 0;
3176 ELSE
3177 -- there is a fixed amount formula, check if bid quantity is required
3178 -- if bid quantity is required and it is null
3179 -- the price cannot be transformed
3180 IF bid_quantity_required AND NOT(valid_bid_quantity_specified) THEN
3181 RETURN NULL;
3182 END IF;
3183
3184 -- FIXED PRICE based line type
3185 IF l_order_type_lookup_code = 'FIXED PRICE' THEN
3186 l_unit_auc_pf_fixed_amount := l_auc_pf_fixed_amount_formula;
3187 -- RATE based line type
3188 ELSIF l_order_type_lookup_code = 'RATE' THEN
3189 l_unit_auc_pf_fixed_amount := l_auc_pf_fixed_amount_formula / l_auction_quantity;
3190 -- AMOUNT based line type
3191 ELSIF l_order_type_lookup_code = 'AMOUNT' THEN
3192 l_unit_auc_pf_fixed_amount := l_auc_pf_fixed_amount_formula / l_auction_quantity;
3193 -- other line types
3194 ELSE
3195 -- if negotiation is either a BPA or CPA or requires full bid quantity
3196 -- use the auction quantity
3197 IF l_contract_type IN ('BLANKET', 'CONTRACT') OR
3198 l_full_quantity_bid_code = 'FULL_QTY_BIDS_REQD' THEN
3199 l_unit_auc_pf_fixed_amount := l_auc_pf_fixed_amount_formula / l_auction_quantity;
3200 -- otherwise, use the bid quantity
3201 ELSE
3202 l_unit_auc_pf_fixed_amount := l_auc_pf_fixed_amount_formula / p_bid_quantity;
3203 END IF;
3204 END IF;
3205 END IF;
3206
3207 l_total_price := l_total_price + (l_auc_pf_unit_price_formula * p_rate) + (l_unit_auc_pf_fixed_amount * p_rate) + (l_auc_pf_percentage_formula * p_price);
3208
3209 -- do not round total price to precision before returning
3210 print_log(l_api_name || '.END', l_total_price || ': END l_total_price');
3211 RETURN l_total_price;
3212
3213 END apply_price_factors;
3214
3215 FUNCTION GET_FND_USER_ID (p_person_party_id IN NUMBER)
3216 RETURN NUMBER
3217 IS
3218 x_user_id number;
3219 begin
3220 begin
3221 select user_id into x_user_id from fnd_user where person_party_id = p_person_party_id;
3222 exception
3223 when others then
3224 x_user_id := p_person_party_id;
3225 end;
3226 --
3227 return x_user_id;
3228 end;
3229 --
3230
3231
3232 /**
3233 * This procedure recovers the archived draft bid, if any,
3234 * on the most recent previous amendment for which the supplier entered bid values.
3235 * It sets the bid status on the most recent bid across all previous amendments to DRAFT
3236 * only if that most recent bid has a bid status of ARCHIVED_DRAFT
3237 *
3238 p_auction_header_id_orig_amend - the original auction
3239 p_trading_partner_id - the tp id of the supplier
3240 p_trading_partner_contact_id - the tp contact id of the supplier
3241 p_vendor_site_id - the site id of the supplier
3242 */
3243 PROCEDURE recover_prev_amend_draft (
3244 p_auction_header_id_orig_amend IN NUMBER,
3245 p_trading_partner_id IN NUMBER,
3246 p_trading_partner_contact_id IN NUMBER,
3247 p_vendor_site_id IN NUMBER,
3248 p_login_user_id IN NUMBER
3249 )
3250 IS
3251 l_api_name CONSTANT VARCHAR2(30) := 'recover_prev_amend_draft';
3252 BEGIN
3253 print_log(l_api_name, ' BEGIN recover_prev_amend_draft');
3254 UPDATE pon_bid_headers
3255 SET
3256 bid_status = 'DRAFT',
3257 last_update_date = SYSDATE,
3258 last_updated_by = p_login_user_id
3259 WHERE
3260 bid_number = (
3261 SELECT bid_number
3262 FROM
3263 (SELECT
3264 bh.bid_number,
3265 bh.bid_status,
3266 decode(bh.bid_status,
3267 'ARCHIVED_DRAFT', 3,
3268 'RESUBMISSION' , 2,
3269 'DISQUALIFIED', 1) bid_status_order,
3270 nvl(ah.amendment_number, 0) amendment_number,
3271 bh.publish_date
3272 FROM
3273 pon_bid_headers bh,
3274 pon_auction_headers_all ah
3275 WHERE
3276 bh.auction_header_id = ah.auction_header_id
3277 AND ah.auction_header_id_orig_amend = p_auction_header_id_orig_amend
3278 AND bh.trading_partner_id = p_trading_partner_id
3279 AND bh.trading_partner_contact_id = p_trading_partner_contact_id
3280 AND nvl(bh.vendor_site_id, -1) = nvl(p_vendor_site_id, -1)
3281 AND bh.bid_status in ('ARCHIVED_DRAFT', 'RESUBMISSION', 'DISQUALIFIED')
3282 ORDER BY amendment_number DESC, bid_status_order DESC, bh.publish_date DESC
3283 )
3284 WHERE ROWNUM = 1
3285 )
3286 AND bid_status = 'ARCHIVED_DRAFT';
3287
3288 print_log(l_api_name, ' END recover_prev_amend_draft');
3289 END recover_prev_amend_draft;
3290
3291
3292 PROCEDURE set_buyer_bid_total
3293 (p_auction_header_id IN NUMBER,
3294 p_bid_number IN NUMBER)
3295
3296 IS
3297 l_api_name CONSTANT VARCHAR2(30) := 'set_buyer_bid_total';
3298 BEGIN
3299 print_log(l_api_name, p_bid_number||': BEGIN set_buyer_bid_total');
3300 UPDATE pon_bid_headers pbh
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
3310 pbip.line_number = paip.line_number AND
3311 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE'))
3312 WHERE pbh.auction_header_id = p_auction_header_id AND
3313 pbh.bid_number = p_bid_number;
3314
3315 print_log(l_api_name, p_bid_number||': END set_buyer_bid_total');
3316
3317 END set_buyer_bid_total;
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340 --==============================================================
3341 --==============================================================
3342 --
3343 -- BATCHING
3344 --
3345 --==============================================================
3346 --==============================================================
3347
3348
3349
3350
3351
3352
3353 FUNCTION new_best_price (x_auction_type IN VARCHAR2,
3354 x_current_price IN NUMBER,
3355 x_current_limit_price IN NUMBER,
3356 x_best_bid_price IN NUMBER,
3357 x_best_bid_limit_price IN NUMBER)
3358 RETURN VARCHAR2 IS
3359 v_newBestPrice NUMBER;
3360 v_newPrice NUMBER;
3361 l_api_name CONSTANT VARCHAR2(30) := 'new_best_price';
3362 BEGIN
3363 -- logging
3364 print_log(l_api_name || '.BEGIN', ' ');
3365 print_log(l_api_name, 'x_auction_type=' || x_auction_type);
3366 print_log(l_api_name, 'x_current_price=' || x_current_price);
3367 print_log(l_api_name, 'x_current_limit_price=' || x_current_limit_price);
3368 print_log(l_api_name, 'x_best_bid_price=' || x_best_bid_price);
3369 print_log(l_api_name, 'x_best_bid_limit_price=' || x_best_bid_limit_price);
3370
3371 print_log(l_api_name, 'calling calculate_prices' );
3372 calculate_prices(x_auction_type,
3373 x_current_price,
3374 NVL(x_current_limit_price,x_current_price),
3375 1,
3376 x_best_bid_price,
3377 NVL(x_best_bid_limit_price,x_best_bid_price),
3378 1,
3379 v_newPrice,
3380 v_newBestPrice);
3381
3382 print_log(l_api_name || '.END', ' ');
3383
3384 IF ((v_newBestPrice <> x_best_bid_price) OR (better_price(x_auction_type, v_newPrice, x_best_bid_price))) THEN
3385 RETURN 'Y';
3386 ELSE
3387 RETURN 'N';
3388 END IF;
3389
3390 END;
3391
3392
3393 FUNCTION new_best_mas_price( p_auction_type IN VARCHAR2
3394 , p_current_price IN NUMBER
3395 , p_total_weighted_score IN NUMBER
3396 , p_current_limit_price IN NUMBER
3397 , p_best_bid_bid_price IN NUMBER
3398 , p_best_bid_score IN NUMBER
3399 , p_best_bid_limit_price IN NUMBER
3400 )
3401 RETURN VARCHAR2 IS
3402 --
3403 v_newBestPrice NUMBER;
3404 v_newPrice NUMBER;
3405 v_currentLimit NUMBER;
3406 v_bestLimit NUMBER;
3407 l_api_name CONSTANT VARCHAR2(30) := 'new_best_mas_price';
3408 --
3409 BEGIN
3410 --
3411
3412 -- logging
3413 print_log(l_api_name || '.BEGIN', ' ');
3414 print_log(l_api_name, 'p_auction_type=' || p_auction_type);
3415 print_log(l_api_name, 'p_current_price=' || p_current_price);
3416 print_log(l_api_name, 'p_total_weighted_score=' || p_total_weighted_score);
3417 print_log(l_api_name, 'p_current_limit_price=' || p_current_limit_price);
3418 print_log(l_api_name, 'p_best_bid_bid_price=' || p_best_bid_bid_price);
3419 print_log(l_api_name, 'p_best_bid_score=' || p_best_bid_score);
3420 print_log(l_api_name, 'p_best_bid_limit_price=' || p_best_bid_limit_price);
3421
3422 v_currentLimit := NVL(p_current_limit_price, p_current_price);
3423 v_bestLimit := NVL(p_best_bid_limit_price, p_best_bid_bid_price);
3424 --
3425 IF (p_best_bid_score/p_best_bid_bid_price) >= (p_total_weighted_score/v_currentLimit) THEN
3426 -- best price is better than current limit
3427 --
3428 v_newBestPrice := p_best_bid_bid_price;
3429 v_newPrice := v_currentLimit;
3430 --
3431 ELSIF (p_total_weighted_score/p_current_price) > (p_best_bid_score/v_bestLimit) THEN
3432 -- current price is better than best limit
3433 --
3434 v_newBestPrice := v_bestLimit;
3435 v_newPrice := p_current_price;
3436 --
3437 ELSIF (
3438 ((p_best_bid_score/p_best_bid_bid_price) >= (p_total_weighted_score/p_current_price)
3439 AND
3440 (p_total_weighted_score/v_currentLimit) >= (p_best_bid_score/p_best_bid_bid_price)
3441 )
3442 OR
3443 ((p_total_weighted_score/p_current_price) >= (p_best_bid_score/p_best_bid_bid_price)
3444 AND
3445 (p_best_bid_score/v_bestLimit) >= (p_total_weighted_score/p_current_price)
3446 )
3447 ) THEN
3448 -- best bid price range and current bid price range intersect
3449 --
3450 IF (p_total_weighted_score/v_currentLimit) > (p_best_bid_score/v_bestLimit) THEN
3451 --
3452 -- current bid when taken to limit is better than best bid taken to its limit
3453 v_newBestPrice := v_bestLimit;
3454 --
3455 IF (v_bestLimit - 1) > v_currentLimit THEN
3456 v_newPrice := v_bestLimit - 1;
3457 ELSE
3458 v_newPrice := v_currentLimit;
3459 END IF;
3460 --
3461 ELSIF (p_best_bid_score/v_bestLimit) > (p_total_weighted_score/v_currentLimit) THEN
3462 --
3463 -- best bid taken to its limit is better than current bid when taken to limit
3464 IF (v_currentLimit - 1) > v_bestLimit THEN
3465 v_newBestPrice := v_currentLimit - 1;
3466 ELSE
3467 v_newBestPrice := v_bestLimit;
3468 END IF;
3469 --
3470 v_newPrice := v_currentLimit;
3471 --
3472 ELSIF (p_total_weighted_score/v_currentLimit) = (p_best_bid_score/v_bestLimit) THEN
3473 --
3474 -- current bid when taken to limit is equal to best bid taken to its limit
3475 v_newBestPrice := v_bestLimit;
3476 v_newPrice := v_currentLimit;
3477 --
3478 END IF;
3479 --
3480 END IF;
3481 --
3482 IF ((v_newBestPrice <> p_best_bid_bid_price)
3483 OR (p_total_weighted_score/v_newPrice) > (p_best_bid_score/p_best_bid_bid_price)) THEN
3484 print_log(l_api_name || '.END', ' ');
3485 RETURN 'Y';
3486 END IF;
3487 --
3488 print_log(l_api_name || '.END', ' ');
3489 RETURN 'N';
3490 --
3491 END;
3492
3493 --========================================================================
3494 -- PROCEDURE : auto_extend_lines
3495 -- PARAMETERS:
3496 -- VERSION : current version 1.x
3497 -- initial version 1.0
3498 -- COMMENT : encapsulates the auto-extension logic. used in
3499 -- update_auction_info
3500 --========================================================================
3501
3502 PROCEDURE auto_extend_lines
3503 ( p_auctionHeaderId IN NUMBER,
3504 p_bidNumber IN NUMBER,
3505 p_batchStart IN NUMBER,
3506 p_batchEnd IN NUMBER,
3507 p_extensionInterval IN NUMBER, -- This is the calculated exten interval
3508 p_hdrExtensionTime IN NUMBER, -- THis is the one specified by the buyer
3509 p_bidPublishDate IN DATE,
3510 p_autoExtendAllLinesFlag IN VARCHAR2,
3511 p_hdrCloseBiddingDate IN DATE,
3512 p_autoExtendNumber IN NUMBER,
3513 p_hdrNumberOfExtensions IN NUMBER,
3514 p_autoExtendMinTriggerRank IN NUMBER,
3515 p_autoExtendTypeFlag IN VARCHAR2,
3516 p_bidRanking IN VARCHAR2,
3517 p_rankIndicator IN VARCHAR2
3518 )
3519 IS
3520
3521 l_api_name CONSTANT VARCHAR2(30) := 'auto_extend_lines';
3522
3523 BEGIN
3524
3525 print_log(l_api_name, p_bidNumber || ' - begin auto extend lines for batch ranges: ' ||
3526 p_batchStart || ' ~ ' || p_batchEnd || ' inclusive');
3527
3528 -- extend all lines
3529 IF (p_autoExtendAllLinesFlag ='Y') THEN
3530
3531 update
3532 pon_auction_item_prices_all
3533 SET
3534 number_of_extensions = nvl(number_of_extensions,0) + 1,
3535 close_bidding_date = close_bidding_date + p_extensionInterval
3536 WHERE
3537 auction_header_id = p_auctionHeaderId
3538 AND close_bidding_date >= p_bidPublishDate
3539 AND line_number >= p_batchStart
3540 AND line_number <= p_batchEnd;
3541
3542 ELSE
3543 -- extend only the lines with new bids
3544 UPDATE pon_auction_item_prices_all
3545 SET number_of_extensions = nvl(number_of_extensions, 0) + 1,
3546 close_bidding_date = decode(p_autoExtendTypeFlag, 'FROM_AUCTION_CLOSE_DATE',
3547 nvl(close_bidding_date, p_hdrCloseBiddingDate) + p_hdrExtensionTime,
3548 p_bidPublishDate + p_hdrExtensionTime)
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
3558 AND bl.line_number = al.line_number
3559
3560 -- consider only lines changed during this publish
3561 AND bl.publish_date = bh.publish_date
3562
3563 -- consider only lines with bids placed within the AutoExtend period
3564 AND bl.publish_date > (nvl(al.close_bidding_date, p_hdrCloseBiddingDate) - p_hdrExtensionTime)
3565
3566 -- consider only lines which have extensions left
3567 AND nvl(al.number_of_extensions, p_hdrNumberOfExtensions) < p_autoExtendNumber
3568
3569 -- We extend when any bid will trigger AutoExtend
3570 -- or we extend when the top bid is placed
3571 -- or, if ranking is numbered, we extend when the bid placed is within the top rank specified
3572 AND (p_autoExtendMinTriggerRank = 10000
3573 OR decode(p_bidRanking, 'MULTI_ATTRIBUTE_SCORING',
3574 al.best_bid_bid_number, al.best_bid_number) = bl.bid_number
3575 OR (p_rankIndicator = 'NUMBERING' AND bl.rank <= p_autoExtendMinTriggerRank))
3576
3577 -- consider only lines in the current batch
3578 AND al.line_number >= p_batchStart
3579 AND al.line_number <= p_batchEnd);
3580 END IF;
3581
3582 -- auto extend all group lines which had at least one
3583 -- other line in the group extended
3584
3585 UPDATE pon_auction_item_prices_all al
3586 SET (number_of_extensions,
3587 close_bidding_date) =
3588 (SELECT max(number_of_extensions),
3589 max(close_bidding_date)
3590 FROM pon_auction_item_prices_all al2
3591 WHERE al2.auction_header_id = al.auction_header_id
3592 AND nvl(al2.parent_line_number, al2.line_number) =
3593 nvl(al.parent_line_number, al.line_number))
3594 WHERE auction_header_id = p_auctionHeaderId
3595 AND group_type in ('LOT', 'LOT_LINE', 'GROUP', 'GROUP_LINE')
3596 AND line_number >= p_batchStart
3597 AND line_number <= p_batchEnd;
3598
3599 print_log(l_api_name, p_bidNumber || ' - end auto extend lines for batch ranges: ' || p_batchStart || ' ~ ' || p_batchEnd || ' inclusive');
3600
3601 END auto_extend_lines;
3602
3603 PROCEDURE auto_extend_lines_batch
3604 ( p_auctionHeaderId IN NUMBER,
3605 p_bidNumber IN NUMBER,
3606 p_maxLineNumber IN NUMBER,
3607 p_batchSize IN NUMBER,
3608 p_extensionInterval IN NUMBER,
3609 p_hdrExtensionTime IN NUMBER,
3610 p_bidPublishDate IN DATE,
3611 p_autoExtendAllLinesFlag IN VARCHAR2,
3612 p_hdrCloseBiddingDate IN DATE,
3613 p_autoExtendNumber IN NUMBER,
3614 p_hdrNumberOfExtensions IN NUMBER,
3615 p_autoExtendMinTriggerRank IN NUMBER,
3616 p_autoExtendTypeFlag IN VARCHAR2,
3617 p_bidRanking IN VARCHAR2,
3618 p_rankIndicator IN VARCHAR2
3619 )
3620 IS
3621 PRAGMA AUTONOMOUS_TRANSACTION;
3622
3623 v_batchStart NUMBER;
3624 v_batchEnd NUMBER;
3625
3626 l_api_name CONSTANT VARCHAR2(30) := 'auto_extend_lines_batch';
3627 BEGIN
3628
3629 print_log(l_api_name, p_bidNumber || ' - BEGIN auto extend lines AUTONOMOUS');
3630
3631 v_batchStart := 1;
3632 v_batchEnd := p_batchSize;
3633
3634 WHILE (v_batchStart <= p_maxLineNumber) LOOP
3635
3636 -- auto extend all lines
3637 auto_extend_lines (
3638 p_auctionHeaderId => p_auctionHeaderId,
3639 p_bidNumber => p_bidNumber,
3640 p_batchStart => v_batchStart,
3641 p_batchEnd => v_batchEnd,
3642 p_extensionInterval => p_extensionInterval,
3643 p_hdrExtensionTime => p_hdrExtensionTime,
3644 p_bidPublishDate => p_bidPublishDate,
3645 p_autoExtendAllLinesFlag => p_autoExtendAllLinesFlag,
3646 p_hdrCloseBiddingDate => p_hdrCloseBiddingDate,
3647 p_autoExtendNumber => p_autoExtendNumber,
3648 p_hdrNumberOfExtensions => p_hdrNumberOfExtensions,
3649 p_autoExtendMinTriggerRank => p_autoExtendMinTriggerRank,
3650 p_autoExtendTypeFlag => p_autoExtendTypeFlag,
3651 p_bidRanking => p_bidRanking,
3652 p_rankIndicator => p_rankIndicator);
3653 commit;
3654
3655 print_log(l_api_name, p_bidNumber || ' - auto extend lines: committed!');
3656
3657 v_batchStart := v_batchEnd + 1;
3658 IF (v_batchEnd + p_batchSize > p_maxLineNumber) THEN
3659 v_batchEnd := p_maxLineNumber;
3660 ELSE
3661 v_batchEnd := v_batchEnd + p_batchSize;
3662 END IF;
3663 END LOOP;
3664
3665 print_log(l_api_name, p_bidNumber || ' - END auto extend lines AUTONOMOUS');
3666
3667 END auto_extend_lines_batch;
3668
3669 procedure auto_extend_negotiation
3670 ( p_auctionHeaderId IN NUMBER,
3671 p_bidNumber IN NUMBER,
3672 p_maxLineNumber IN NUMBER,
3673 p_batchSize IN NUMBER,
3674 p_batchingRequired IN BOOLEAN)
3675 IS
3676 v_auctionType pon_auction_headers_all.auction_type%TYPE;
3677 v_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
3678 v_rankIndicator pon_auction_headers_all.rank_indicator%TYPE;
3679 v_autoExtendAllLinesFlag pon_auction_headers_all.auto_extend_all_lines_flag%TYPE;
3680 v_autoExtendNumber pon_auction_headers_all.auto_extend_number%TYPE;
3681 v_autoExtendDuration pon_auction_headers_all.auto_extend_duration%TYPE;
3682 v_autoExtendTypeFlag pon_auction_headers_all.auto_extend_type_flag%TYPE;
3683 v_autoExtendMinTriggerRank pon_auction_headers_all.auto_extend_min_trigger_rank%TYPE;
3684 v_staggeredClosingInterval pon_auction_headers_all.staggered_closing_interval%TYPE;
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';
3694 v_hdrExtensionTime NUMBER;
3695
3696 BEGIN
3697
3698
3699 print_log(l_api_name, p_bidNumber || ' - begin auto extend lines :' ||
3700 'p_auctionHeaderId = ' || p_auctionHeaderId ||
3701 ', p_bidNumber = ' || p_bidNumber ||
3702 ', p_maxLineNumber = ' || p_maxLineNumber ||
3703 ', p_batchSize = ' || p_batchSize);
3704
3705 --Get the auction details
3706 select
3707 pah.auction_type,
3708 pah.bid_ranking,
3709 pah.rank_indicator,
3710 nvl(pah.auto_extend_all_lines_flag,'Y'),
3711 pah.auto_extend_type_flag,
3712 pah.auto_extend_number,
3713 pah.auto_extend_duration,
3714 pah.close_bidding_date,
3715 nvl(pah.number_of_extensions,0),
3716 pah.auto_extend_min_trigger_rank,
3717 pah.staggered_closing_interval,
3718 bh.publish_date
3719 into
3720 v_auctionType,
3721 v_bidRanking,
3722 v_rankIndicator,
3723 v_autoExtendAllLinesFlag,
3724 v_autoExtendTypeFlag,
3725 v_autoExtendNumber,
3726 v_autoExtendDuration,
3727 v_hdrCloseBiddingDate,
3728 v_hdrNumberOfExtensions,
3729 v_autoExtendMinTriggerRank,
3730 v_staggeredClosingInterval,
3731 v_bidPublishDate
3732 from
3733 pon_bid_headers bh,
3734 pon_auction_headers_all pah
3735 where bh.bid_number = p_bidNumber
3736 AND pah.auction_header_id = bh.auction_header_id;
3737
3738 v_hdrExtensionTime := (1 / (24 * 60)) * v_autoExtendDuration;
3739
3740 print_log(l_api_name, 'Queried data from headers_all' ||
3741 'v_auctionType = ' || v_auctionType ||
3742 ', v_bidRanking = ' || v_bidRanking ||
3743 ', v_rankIndicator = ' || v_rankIndicator ||
3744 ', v_autoExtendAllLinesFlag = ' || v_autoExtendAllLinesFlag ||
3745 ', v_autoExtendTypeFlag = ' || v_autoExtendTypeFlag ||
3746 ', v_autoExtendNumber = ' || v_autoExtendNumber ||
3747 ', v_autoExtendDuration = ' || v_autoExtendDuration ||
3748 ', v_hdrCloseBiddingDate = ' || to_char(v_hdrCloseBiddingDate, 'dd-mon-yyyy hh24:mi:ss') ||
3749 ', v_hdrNumberOfExtensions = ' || v_hdrNumberOfExtensions ||
3750 ', v_autoExtendMinTriggerRank = ' || v_autoExtendMinTriggerRank ||
3751 ', v_staggeredClosingInterval = ' || v_staggeredClosingInterval);
3752
3753 --If EXTEND_ALL_LINES Then
3754 if (v_autoExtendAllLinesFlag ='Y') then -- {
3755 begin -- {
3756
3757 -- If any bid can trigger AutoExtend, and not staggered closing.
3758 IF (v_autoExtendMinTriggerRank = 10000
3759 AND NVL(v_staggeredClosingInterval, 0) = 0) THEN
3760 IF (v_hdrNumberOfExtensions < v_autoExtendNumber
3761 AND v_bidPublishDate > (v_hdrCloseBiddingDate - v_hdrExtensionTime)) THEN
3762 v_triggerLineCloseBiddingDate := v_hdrCloseBiddingDate;
3763 ELSE
3764 -- No AutoExtension will take place because we are not within the AutoExtension window
3765 print_log(l_api_name, 'No AutoExtension took place');
3766 RETURN;
3767 END IF;
3768
3769 --Fire SQL to determine if any extensions
3770 ELSE
3771 select
3772 nvl(a.close_bidding_date, v_hdrCloseBiddingDate),
3773 b.publish_date
3774 into
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
3784 and a.line_number = b.line_number
3785
3786 -- We extend when any bid will trigger AutoExtend
3787 -- or we extend when the top bid is placed
3788 -- or, if ranking is numbered, we extend when the bid placed is within the top rank specified
3789 and (v_autoExtendMinTriggerRank = 10000
3790 OR decode(v_bidRanking, 'MULTI_ATTRIBUTE_SCORING',
3791 a.best_bid_bid_number, a.best_bid_number) = b.bid_number
3792 OR (v_rankIndicator = 'NUMBERING' AND b.rank <= v_autoExtendMinTriggerRank))
3793
3794 and nvl(a.number_of_extensions, v_hdrNumberOfExtensions) < v_autoExtendNumber
3795 and b.publish_date > (nvl(a.close_bidding_date, v_hdrCloseBiddingDate) - v_hdrExtensionTime)
3796 and v_bidPublishDate <= nvl (a.close_bidding_date, v_hdrCloseBiddingDate)
3797 and rownum = 1;
3798 END IF;
3799
3800 --If any lines found, then determine the extension interval
3801 IF (v_autoExtendTypeFlag = 'FROM_AUCTION_CLOSE_DATE') then --{
3802 v_extensionInterval := v_hdrExtensionTime;
3803
3804 ELSE
3805 v_extensionInterval := (v_bidPublishDate + v_hdrExtensionTime) - v_triggerLineCloseBiddingDate;
3806
3807 END IF; --}
3808
3809 print_log(l_api_name, 'Found a line that autoextends v_extensionInterval = ' || v_extensionInterval);
3810 exception
3811 when no_data_found then
3812 --If no lines found, then return without doing anything
3813 print_log(l_api_name, 'Cound not find a line that autoextends');
3814 return;
3815 end; --}
3816 end if; --}
3817
3818 -- The reason we have a wrapper procedure auto_extend_negotiation
3819 -- is that auto_extend_lines_batch has PRAGMA AUTONOMOUS.
3820
3821 --Call in batches
3822 if (p_batchingRequired) then --{
3823 auto_extend_lines_batch (
3824 p_auctionHeaderId => p_auctionHeaderId,
3825 p_bidNumber => p_bidNumber,
3826 p_maxLineNumber => p_maxLineNumber,
3827 p_batchSize => p_batchSize,
3828 p_extensionInterval => v_extensionInterval,
3829 p_hdrExtensionTime => v_hdrExtensionTime,
3830 p_bidPublishDate => v_bidPublishDate,
3831 p_autoExtendAllLinesFlag => v_autoExtendAllLinesFlag,
3832 p_hdrCloseBiddingDate => v_hdrCloseBiddingDate,
3833 p_autoExtendNumber => v_autoExtendNumber,
3834 p_hdrNumberOfExtensions => v_hdrNumberOfExtensions,
3835 p_autoExtendMinTriggerRank => v_autoExtendMinTriggerRank,
3836 p_autoExtendTypeFlag => v_autoExtendTypeFlag,
3837 p_bidRanking => v_bidRanking,
3838 p_rankIndicator => v_rankIndicator);
3839 else
3840 auto_extend_lines (
3841 p_auctionHeaderId => p_auctionHeaderId,
3842 p_bidNumber => p_bidNumber,
3843 p_batchStart => 1,
3844 p_batchEnd => p_maxLineNumber,
3845 p_extensionInterval => v_extensionInterval,
3846 p_hdrExtensionTime => v_hdrExtensionTime,
3847 p_bidPublishDate => v_bidPublishDate,
3848 p_autoExtendAllLinesFlag => v_autoExtendAllLinesFlag,
3849 p_hdrCloseBiddingDate => v_hdrCloseBiddingDate,
3850 p_autoExtendNumber => v_autoExtendNumber,
3851 p_hdrNumberOfExtensions => v_hdrNumberOfExtensions,
3852 p_autoExtendMinTriggerRank => v_autoExtendMinTriggerRank,
3853 p_autoExtendTypeFlag => v_autoExtendTypeFlag,
3854 p_bidRanking => v_bidRanking,
3855 p_rankIndicator => v_rankIndicator);
3856 end if; --}
3857 print_log(l_api_name, p_bidNumber || ' - End auto extend lines.');
3858 END;
3859
3860 --========================================================================
3861 -- PROCEDURE : update_proxy_bid
3862 -- update_proxy_bid_auto
3863 -- PARAMETERS:
3864 -- VERSION : current version 1.x
3865 -- initial version 1.0
3866 -- COMMENT : encapsulates the proxy bid logic. used in
3867 -- update_auction_info
3868 --========================================================================
3869
3870 PROCEDURE update_proxy_bid
3871 ( p_auctionHeaderId IN NUMBER,
3872 p_bidNumber IN NUMBER,
3873 p_oldBidNumber IN NUMBER,
3874 p_isSurrogateBid IN VARCHAR2,
3875 p_isAuctionClosed IN VARCHAR2,
3876 x_isPriceChanged OUT NOCOPY VARCHAR2
3877 )
3878 IS
3879
3880 l_api_name CONSTANT VARCHAR2(30) := 'update_proxy_bid';
3881 --
3882 -- v_proxyBidList stores the new bid prices for proxy bids after
3883 -- all proxy has been performed
3884 TYPE t_proxyBidItem IS RECORD
3885 ( bid_number NUMBER,
3886 line_number NUMBER,
3887 bid_price NUMBER,
3888 bid_currency_price NUMBER);
3889
3890 TYPE t_proxyBidList IS TABLE OF t_proxyBidItem
3891 INDEX BY BINARY_INTEGER;
3892
3893 v_proxyBidList t_proxyBidList;
3894 v_emptyProxyBidList t_proxyBidList;
3895
3896 -- v_reBidList stores the list of bids that need to be "cloned"
3897 -- as a result of proxy bidding
3898 TYPE t_reBidList IS TABLE OF NUMBER
3899 INDEX BY BINARY_INTEGER;
3900
3901 v_reBidList t_reBidList;
3902 v_emptyReBidList t_reBidList;
3903
3904 -- t_itm_* records contain information about a bid's
3905 -- number_of_bids and best_bid_proxy_limit_prc
3906 TYPE t_tbl_number IS TABLE OF NUMBER
3907 INDEX BY PLS_INTEGER;
3908
3909 t_itm_index Number := 0;
3910 t_itm_line_number t_tbl_number;
3911 t_itm_number_of_bids t_tbl_number;
3912 t_itm_best_bid_proxy_limit_prc t_tbl_number;
3913 --
3914 -- other variables used in the procedure
3915 v_best_bid_proxy_limit_price NUMBER;
3916 v_proxy_bid_limit_price NUMBER;
3917 v_best_bid_min_change NUMBER;
3918 v_bid_min_change NUMBER;
3919 v_oldBidNum NUMBER;
3920 v_newBidNum NUMBER;
3921 v_idx NUMBER;
3922 v_tpid pon_bid_headers.trading_partner_id%TYPE;
3923 v_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
3924 v_bestTpid pon_bid_headers.trading_partner_id%TYPE;
3925 v_bestTpcid pon_bid_headers.trading_partner_contact_id%TYPE;
3926 v_rate pon_bid_headers.rate%TYPE;
3927 v_best_bid_rate pon_bid_headers.rate%TYPE;
3928 v_best_bid_decimals pon_bid_headers.number_price_decimals%TYPE;
3929 v_auction_type pon_auction_headers_all.auction_type%TYPE;
3930 v_bid_change_type pon_auction_headers_all.min_bid_change_type%TYPE;
3931 v_trading_partner_id pon_auction_headers_all.trading_partner_id%TYPE;
3932 v_auction_decimals NUMBER;
3933 v_publish_date DATE;
3934 v_count NUMBER;
3935 v_bid_decimals NUMBER;
3936 v_number_of_bids NUMBER;
3937 v_revision_number NUMBER;
3938 v_bid_min_change_save Number;
3939 v_new_bid_price Number;
3940 v_new_best_bid_price Number;
3941
3942 v_proxy_publish_date DATE;
3943 --
3944 -- this cursor selects all lines for which this
3945 -- bid has a new bid on AND can has proxy enabled.
3946 -- functionally, this is the group of lines that need to
3947 -- be examined for possible proxy activity.
3948 CURSOR c_proxy_bid_candidates is
3949 select biditem.line_number,
3950 biditem.price,
3951 biditem.proxy_bid_limit_price,
3952 biditem.first_bid_price,
3953 item.best_bid_number,
3954 item.best_bid_price,
3955 item.best_bid_proxy_limit_price,
3956 item.best_bid_first_bid_price,
3957 NVL(item.number_of_bids, 0) as number_of_bids,
3958 bestbid.trading_partner_id as best_bid_tp_id,
3959 bestbid.trading_partner_contact_id as best_bid_tpc_id,
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
3969 and biditem.auction_header_id= p_auctionHeaderId
3970 and biditem.bid_number = p_bidNumber
3971 and biditem.publish_date = v_publish_date
3972 and (item.best_bid_proxy_limit_price is not null
3973 or biditem.proxy_bid_limit_price is not null);
3974 --
3975 -- this function returns whether x_price1 is better than x_price2
3976 -- i.e. if (x_price1 < x_price2)
3977 FUNCTION is_better_price(x_price1 IN NUMBER,
3978 x_price2 IN NUMBER)
3979 RETURN BOOLEAN IS
3980 BEGIN
3981 return better_price(v_auction_type, x_price1,x_price2);
3982 END;
3983 --
3984
3985 BEGIN
3986
3987 -- logging
3988 print_log(l_api_name || '.BEGIN', ' ');
3989 print_log(l_api_name, 'p_auctionHeaderId=' || p_auctionHeaderId);
3990 print_log(l_api_name, 'p_bidNumber=' || p_bidNumber);
3991 print_log(l_api_name, 'p_oldBidNumber=' || p_oldBidNumber);
3992 print_log(l_api_name, 'p_isSurrogateBid=' || p_isSurrogateBid);
3993 print_log(l_api_name, 'p_isAuctionClosed=' || p_isAuctionClosed);
3994
3995 print_log(l_api_name, p_bidNumber || ': beginning update_proxy_bid');
3996
3997 SELECT auc.auction_type,
3998 auc.trading_partner_id,
3999 auc.number_price_decimals,
4000 auc.min_bid_change_type,
4001 NVL(bid.min_bid_change,0),
4002 bid.trading_partner_id,
4003 bid.trading_partner_contact_id,
4004 NVL(bid.rate, 1.0),
4005 bid.publish_date,
4006 NVL(bid.number_price_decimals,10),
4007 NVL(bid.bid_revision_number,1)
4008 INTO v_auction_type,
4009 v_trading_partner_id,
4010 v_auction_decimals,
4011 v_bid_change_type,
4012 v_bid_min_change,
4013 v_tpid,
4014 v_tpcid,
4015 v_rate,
4016 v_publish_date,
4017 v_bid_decimals,
4018 v_revision_number
4019 FROM pon_bid_headers bid,pon_auction_headers_all auc
4020 WHERE auc.auction_header_id = bid.auction_header_id
4021 AND bid.bid_number = p_bidNumber;
4022
4023 -- initialize this price changed flag to not changed
4024 x_isPriceChanged := 'N';
4025
4026 -- Save this for PERCENTAGE calculation
4027 v_bid_min_change_save := v_bid_min_change;
4028
4029
4030 -- bug #2470367
4031 -- here we want to copy over any proxy bid lines that could have
4032 -- changed since the time the user loaded the previous bid into
4033 -- the response flow.
4034 -- only the proxy bid lines could have changed
4035 -- if p_oldBidNumber is null, no active bid exists, hence no need to copy anything over.
4036 -- for all the lines supposedly under proxy in the current bid, copy over the bid info
4037 -- from the previous bid if the previous bid line has a later publish date than the
4038 -- current bid line's publish date. this will happen if there was another bid placed
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 ,
4048 publish_date) =
4049 (SELECT 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
4059 AND newbid.bid_number = p_bidNumber
4060 AND newbid.proxy_bid_limit_price IS NOT NULL
4061 AND newbid.publish_date <> v_publish_date;
4062
4063 END IF; -- end if (p_oldBidNumber IS NOT NULL)
4064
4065 print_log(l_api_name, p_bidNumber || ': update_proxy point 2');
4066
4067 -- initialize these
4068 t_itm_line_number.DELETE;
4069 t_itm_number_of_bids.DELETE;
4070 t_itm_best_bid_proxy_limit_prc.DELETE;
4071
4072
4073 -- MAIN PROXY LOOP
4074 -- Loop through all proxy bid candidate lines and determine
4075 -- which lines will need new proxy bids and also determine
4076 -- at what price the proxy stops, etc. Store all the details
4077 -- of any necessary proxy activity in the table of records.
4078 print_log(l_api_name, p_bidNumber || ': update_proxy before main loop (3)');
4079 FOR bidlist IN c_proxy_bid_candidates LOOP
4080
4081 v_number_of_bids := bidlist.number_of_bids;
4082 print_log(l_api_name, 'cursor=' || bidlist.line_number);
4083
4084 -- Reset to the original value for v_bid_min_change,
4085 -- as PERCENTAGE calculation might have changed the value
4086 v_bid_min_change := v_bid_min_change_save;
4087 IF (NOT (bidlist.best_bid_tpc_id = v_tpcid)) THEN
4088
4089
4090 -- if the proxy limit price(s) for the current bid or the best
4091 -- bid is(are) null, then copy over the bid price into proxy limit price.
4092 IF (bidlist.best_bid_proxy_limit_price IS NULL) THEN
4093 v_best_bid_proxy_limit_price := bidlist.best_bid_price;
4094 ELSE
4095 v_best_bid_proxy_limit_price := bidlist.best_bid_proxy_limit_price;
4096 END IF;
4097 IF (bidlist.proxy_bid_limit_price IS NULL) THEN
4098 v_proxy_bid_limit_price := bidlist.price;
4099 ELSE
4100 v_proxy_bid_limit_price := bidlist.proxy_bid_limit_price;
4101 END IF;
4102
4103
4104 IF (v_bid_change_type = 'PERCENTAGE') THEN
4105 -- first bid from the bidder where do we set this(ssthakur)
4106 v_bid_min_change := bidlist.first_bid_price * v_bid_min_change / 100;
4107 v_best_bid_min_change := bidlist.best_bid_first_bid_price * bidlist.best_bid_min_change / 100;
4108 ELSE
4109 v_best_bid_min_change := bidlist.best_bid_min_change;
4110 END IF;
4111
4112 calculate_prices(v_auction_type,
4113 bidlist.price,
4114 v_proxy_bid_limit_price,
4115 v_bid_min_change,
4116 bidlist.best_bid_price,
4117 v_best_bid_proxy_limit_price,
4118 v_best_bid_min_change,
4119 v_new_bid_price,
4120 v_new_best_bid_price);
4121
4122 IF (bidlist.best_bid_price <> v_new_best_bid_price) THEN
4123 v_proxyBidList(bidlist.line_number).bid_number := bidlist.best_bid_number;
4124 v_proxyBidList(bidlist.line_number).line_number:= bidlist.line_number;
4125 v_proxyBidList(bidlist.line_number).bid_price := v_new_best_bid_price;
4126
4127 -- (ssthakur) need to ask why are we setting the bid price as the v_new_best_bid_price,
4128 -- i thougtht we need to set the price as the limit price ie the limit price exhausted for this bid
4129
4130 -- (ssthakur) also when can be clear the best bid proxy limit price
4131 v_proxyBidList(bidlist.line_number).bid_currency_price :=
4132 round(v_proxyBidList(bidlist.line_number).bid_price * bidlist.best_bid_rate,bidlist.best_bid_decimals);
4133
4134 -- Because of Proxy Bid, increase the number of bids by 1
4135 v_number_of_bids := v_number_of_bids + 1;
4136
4137 IF (NOT v_reBidList.EXISTS(bidlist.best_bid_number)) THEN
4138 v_reBidList(bidlist.best_bid_number) := -1;
4139 END IF;
4140
4141 -- add this line, bidlist.best_bid_number,
4142 -- v_new_best_bid_price, to the rebid list
4143 END IF;
4144
4145 IF (bidlist.price <> v_new_bid_price) THEN
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)),
4155 bid_currency_unit_price = decode(v_new_bid_price,v_proxy_bid_limit_price,bid_currency_limit_price,
4156 round(v_new_bid_price * v_rate,v_bid_decimals)),
4157 bid_currency_trans_price = decode(v_new_bid_price,v_proxy_bid_limit_price,bid_currency_limit_price,
4158 round(v_new_bid_price * v_rate,v_bid_decimals)),
4159 proxy_bid_flag = 'Y'
4160 WHERE auction_header_id = p_auctionHeaderId
4161 AND bid_number = p_bidNumber
4162 AND line_number = bidlist.line_number;
4163
4164 -- the bid price has been changed due to the proxy bidding,
4165 -- set the isPriceChanged flag to be true
4166 x_isPriceChanged := 'Y';
4167
4168 END IF;
4169
4170 ELSE
4171
4172 -- Determine the new best_proxy_bid_limit_price
4173 -- Same bidder might have changed the proxy_bid_limit_price only
4174 IF ((bidlist.proxy_bid_limit_price IS NOT NULL) AND
4175 (NOT (bidlist.proxy_bid_limit_price = NVL(v_best_bid_proxy_limit_price, -1)))) THEN
4176 v_best_bid_proxy_limit_price := bidlist.proxy_bid_limit_price;
4177 END IF;
4178
4179 END IF;
4180
4181 -- update auction item's number of bids and best_bid_proxy_limit_price --
4182 -- should we consider forall loop here -- ssthakur
4183
4184 t_itm_index := t_itm_index +1;
4185 t_itm_line_number(t_itm_index) := bidlist.line_number;
4186 t_itm_number_of_bids(t_itm_index) := v_number_of_bids;
4187 t_itm_best_bid_proxy_limit_prc(t_itm_index) := v_best_bid_proxy_limit_price;
4188
4189
4190 END LOOP;
4191
4192 print_log(l_api_name, p_bidNumber || ': (proxy) after proxy loop');
4193 print_log(l_api_name, p_bidNumber || ': (proxy) we have ' || t_itm_line_number.COUNT || ' lines');
4194
4195 -- Update pon auction item prices, setting the no of bids and the new proxy limit price
4196 FORALL x IN 1..t_itm_line_number.COUNT
4197 UPDATE pon_auction_item_prices_all
4198 SET number_of_bids = t_itm_number_of_bids(x),
4199 best_bid_proxy_limit_price = t_itm_best_bid_proxy_limit_prc(x)
4200 WHERE auction_header_id = p_auctionHeaderId
4201 AND line_number = t_itm_line_number(x);
4202
4203 print_log(l_api_name, p_bidNumber || ': (proxy) after mass update');
4204
4205
4206 -- Update group_amount and buyer_bid_total for the bid here after all of its bid lines are updated.
4207 -- (woojin) can I just check x_isPriceChanged here?
4208 update_group_amount(p_bidNumber);
4209
4210 print_log(l_api_name, p_bidNumber || ': (proxy) after group total ');
4211
4212 -------------------------------------------------------------------------------
4213 -- ssthakur no changes are done below this line
4214 --------------------------------------------------------
4215
4216 IF (v_reBidList.COUNT > 0) THEN
4217
4218
4219 print_log(l_api_name, p_bidNumber || ': (proxy) rebid list is not empty');
4220
4221
4222
4223 v_oldBidNum := v_reBidList.FIRST;
4224
4225 -- proxy bids will be published a millisecond before the publish
4226 -- date to give the proxy bid a slight advantage if prices are tied.
4227 v_proxy_publish_date := v_publish_date - 1/(24*60*60);
4228
4229 LOOP
4230
4231 print_log(l_api_name, 'calling subroutine clone_update_bid');
4232
4233 v_newBidNum := clone_update_bid(p_auctionHeaderId, v_oldBidNum, v_proxy_publish_date,p_bidNumber);
4234
4235 -- (Raja) add the new proxy bid to the list of bids generated during this publish
4236 g_bidsGenerated.extend(1);
4237 g_bidsGeneratedCount := g_bidsGeneratedCount + 1;
4238 g_bidsGenerated(g_bidsGeneratedCount) := v_newBidNum;
4239
4240 v_idx := v_proxyBidList.FIRST;
4241
4242 LOOP
4243 IF (v_proxyBidList(v_idx).bid_number = v_oldBidNum) THEN
4244 print_log(l_api_name, 'calling subroutine update_new_bid_line');
4245 update_new_bid_line(p_auctionHeaderId,
4246 v_newBidNum,
4247 v_proxyBidList(v_idx).line_number,
4248 v_proxyBidList(v_idx).bid_price,
4249 v_proxyBidList(v_idx).bid_currency_price,
4250 v_proxy_publish_date);
4251 END IF;
4252
4253 EXIT WHEN v_idx = v_proxyBidList.LAST;
4254 v_idx := v_proxyBidList.NEXT(v_idx);
4255 END LOOP;
4256
4257 -- Update group_amount and buyer_bid_total for the new proxy bid here.
4258 update_group_amount(v_newBidNum);
4259 set_buyer_bid_total(p_auctionHeaderId, v_newBidNum);
4260
4261 EXIT WHEN v_oldBidNum = v_reBidList.LAST;
4262 v_oldBidNum := v_reBidList.NEXT(v_oldBidNum);
4263
4264 END LOOP;
4265
4266 -- need to explicitly empty the bid list;
4267 v_proxyBidList := v_emptyProxyBidList;
4268 v_reBidList := v_emptyReBidList;
4269
4270 print_log(l_api_name || '.END', ' ');
4271 print_log(l_api_name, p_bidNumber || ': ending update_proxy_bid');
4272
4273
4274
4275 END IF;
4276 END UPDATE_PROXY_BID;
4277
4278
4279 PROCEDURE update_proxy_bid_auto
4280 ( p_auctionHeaderId IN NUMBER,
4281 p_bidNumber IN NUMBER,
4282 p_oldBidNumber IN NUMBER,
4283 p_isSurrogateBid IN VARCHAR2,
4284 p_isAuctionClosed IN VARCHAR2,
4285 x_isPriceChanged OUT NOCOPY VARCHAR2
4286 )
4287 IS
4288
4289 l_api_name CONSTANT VARCHAR2(30) := 'update_proxy_bid_auto';
4290 PRAGMA AUTONOMOUS_TRANSACTION;
4291 BEGIN
4292 print_log(l_api_name, p_bidNumber || ': beginning update_proxy_bid_auto');
4293
4294 update_proxy_bid(p_auctionHeaderId, p_bidNumber, p_oldBidNumber,
4295 p_isSurrogateBid, p_isAuctionClosed, x_isPriceChanged);
4296 commit;
4297 print_log(l_api_name, p_bidNumber || ': ending update_proxy_bid_auto');
4298
4299 END update_proxy_bid_auto;
4300
4301
4302
4303 --========================================================================
4304 --========================================================================
4305 --========================================================================
4306 --========================================================================
4307
4308
4309
4310
4311
4312 PROCEDURE update_and_rerank_group_lines
4313 (p_auctionHeaderId NUMBER,
4314 p_bidNumber NUMBER,
4315 p_publishDate DATE,
4316 p_rankIndicator VARCHAR2,
4317 p_bidRanking VARCHAR2,
4318 p_discard_tech_nonshort VARCHAR2)
4319
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
4329 AND auctionline.line_number = groupline.line_number
4330 AND auctionline.group_type = 'GROUP'
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'
4340 OR groupheader.bid_number = p_bidNumber)
4341 AND groupline.line_number = v_line_number
4342 AND (
4343 (
4344 groupheader.bid_status = 'ACTIVE'
4345 AND
4346 decode (p_discard_tech_nonshort, 'Y', groupheader.technical_shortlist_flag, 'Y') = 'Y'
4347 )
4348 OR
4349 groupheader.bid_number = p_bidNumber
4350 )
4351 ORDER BY groupline.group_amount, groupline.publish_date ASC;
4352
4353
4354 TYPE t_tbl_number IS TABLE OF NUMBER
4355 INDEX BY PLS_INTEGER;
4356
4357 t_itm_line_number t_tbl_number;
4358 t_itm_bid_number t_tbl_number;
4359 t_itm_rank t_tbl_number;
4360
4361 v_counter PLS_INTEGER;
4362 v_bestGroupBidNumber NUMBER;
4363 v_group_rank NUMBER;
4364
4365 BEGIN
4366
4367 print_log(l_api_name, p_bidNumber || ': BEGIN update_and_rerank_group_lines');
4368 t_itm_line_number.DELETE;
4369 t_itm_bid_number.DELETE;
4370 t_itm_rank.DELETE;
4371
4372 v_counter := 1;
4373 FOR rerank_group IN groups_to_be_reranked(p_publishDate) LOOP
4374
4375 IF (p_rankIndicator = 'NUMBERING') THEN
4376
4377 v_group_rank := 1;
4378
4379 FOR bid_group IN bid_group_cursor(rerank_group.line_number) LOOP
4380 t_itm_bid_number(v_counter) := bid_group.bid_number;
4381 t_itm_line_number(v_counter) := rerank_group.line_number;
4382 t_itm_rank(v_counter) := v_group_rank;
4383
4384 v_group_rank := v_group_rank + 1;
4385 v_counter := v_counter + 1;
4386 END LOOP;
4387
4388 ELSE
4389
4390 OPEN bid_group_cursor(rerank_group.line_number);
4391 BEGIN
4392 FETCH bid_group_cursor into v_bestGroupBidNumber;
4393 EXCEPTION
4394 WHEN no_data_found THEN
4395 v_bestGroupBidNumber := NULL;
4396 END;
4397 CLOSE bid_group_cursor;
4398
4399 IF (v_bestGroupBidNumber IS NOT NULL) THEN
4400 t_itm_bid_number(v_counter) := v_bestGroupBidNumber;
4401 t_itm_line_number(v_counter) := rerank_group.line_number;
4402 t_itm_rank(v_counter) := 1;
4403 v_counter := v_counter + 1;
4404 END IF;
4405
4406 END IF; -- end if (p_rankIndicator = 'NUMBERING')
4407
4408 END LOOP;
4409
4410 -- set the pon_auction_item_prices_all.best_bid* attributes
4411 -- using rank #1 bid
4412 FORALL x in 1..t_itm_bid_number.COUNT
4413 UPDATE pon_auction_item_prices_all
4414 SET best_bid_number = t_itm_bid_number(x),
4415 best_bid_bid_number = decode(p_bidRanking,
4416 'MULTI_ATTRIBUTE_SCORING',
4417 t_itm_bid_number(x),
4418 null)
4419 WHERE auction_header_id = p_auctionHeaderId
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
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
4435 END IF;
4436 print_log(l_api_name, p_bidNumber || ': END update_and_rerank_group_lines');
4437 END update_and_rerank_group_lines;
4438
4439
4440
4441 PROCEDURE update_worsened_lines
4442 (p_auctionHeaderId NUMBER,
4443 p_bidNumber NUMBER,
4444 p_publishDate DATE,
4445 p_bidRanking VARCHAR2,
4446 p_discard_tech_nonshort VARCHAR2)
4447 IS
4448
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
4458 AND bidline.bid_number = p_bidNumber
4459 AND bidline.line_number = auctionline.line_number
4460 AND bidheader.bid_number = bidline.bid_number
4461 AND bestbidheader.bid_number = auctionline.best_bid_number
4462 AND bestbidheader.auction_header_id = bidheader.auction_header_id
4463 AND bidheader.trading_partner_id = bestbidheader.trading_partner_id
4464 AND bidheader.trading_partner_contact_id = bestbidheader.trading_partner_contact_id
4465 AND bidline.publish_date = v_publish_date
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
4475 AND bidline.bid_number = p_bidNumber
4476 AND bidline.line_number = auctionline.line_number
4477 AND bidheader.bid_number = bidline.bid_number
4478 AND bestbidheader.bid_number = auctionline.best_bid_bid_number
4479 AND bestbidheader.auction_header_id = bidheader.auction_header_id
4480 AND bidheader.trading_partner_id = bestbidheader.trading_partner_id
4481 AND bidheader.trading_partner_contact_id = bestbidheader.trading_partner_contact_id
4482 AND bidline.publish_date = v_publish_date
4483 AND (bidline.total_weighted_score / bidline.price) <=
4484 (auctionline.best_bid_score / auctionline.best_bid_bid_price);
4485
4486 TYPE t_tbl_number IS TABLE OF NUMBER
4487 INDEX BY PLS_INTEGER;
4488
4489 TYPE t_tbl_date IS TABLE OF DATE
4490 INDEX BY PLS_INTEGER;
4491
4492 TYPE t_tbl_varchar IS TABLE OF VARCHAR2(15)
4493 INDEX BY PLS_INTEGER;
4494
4495 t_line_number t_tbl_number;
4496 t_price t_tbl_number;
4497 t_quantity t_tbl_number;
4498 t_promised_date t_tbl_date;
4499 t_bid_number t_tbl_number;
4500 t_bid_currency_price t_tbl_number;
4501 t_bid_currency_code t_tbl_varchar;
4502 t_first_bid_price t_tbl_number;
4503 t_proxy_bid_limit_price t_tbl_number;
4504 t_score t_tbl_number;
4505 v_counter PLS_INTEGER;
4506
4507 BEGIN
4508
4509 print_log(l_api_name, p_bidNumber || ' - BEGIN update worsened lines');
4510
4511 t_line_number.DELETE;
4512 t_price.DELETE;
4513 t_quantity.DELETE;
4514 t_promised_date.DELETE;
4515 t_bid_number.DELETE;
4516 t_bid_currency_price.DELETE;
4517 t_bid_currency_code.DELETE;
4518 t_first_bid_price.DELETE;
4519 t_proxy_bid_limit_price.DELETE;
4520 t_score.DELETE;
4521 v_counter := 1;
4522
4523 print_log(l_api_name, p_bidNumber || ' - iterating through all non-MAS lines that were worsened');
4524 FOR rerank_line IN bid_items_to_be_reranked(p_publishDate) LOOP
4525
4526 print_log(l_api_name, p_bidNumber || ' - line ' || rerank_line.line_number || ' worsened');
4527
4528 SELECT line_number,
4529 price,
4530 quantity,
4531 promised_date,
4532 bid_number,
4533 bid_currency_price,
4534 bid_currency_code,
4535 first_bid_price,
4536 proxy_bid_limit_price
4537 INTO t_line_number(v_counter),
4538 t_price(v_counter),
4539 t_quantity(v_counter),
4540 t_promised_date(v_counter),
4541 t_bid_number(v_counter),
4542 t_bid_currency_price(v_counter),
4543 t_bid_currency_code(v_counter),
4544 t_first_bid_price(v_counter),
4545 t_proxy_bid_limit_price(v_counter)
4546 FROM (SELECT bidline.line_number,
4547 bidline.price,
4548 bidline.quantity,
4549 bidline.promised_date,
4550 bidline.bid_number,
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
4560 AND (
4561 (
4562 bidheader.bid_status = 'ACTIVE'
4563 AND
4564 decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y'
4565 )
4566 OR
4567 bidheader.bid_number = p_bidNumber
4568 )
4569 AND bidline.line_number = rerank_line.line_number
4570 ORDER BY bidline.price, bidline.publish_date asc)
4571 WHERE rownum = 1;
4572
4573 v_counter := v_counter + 1;
4574
4575 END LOOP;
4576
4577 FORALL x IN 1..t_line_number.COUNT
4578 UPDATE pon_auction_item_prices_all
4579 SET best_bid_price = t_price(x),
4580 best_bid_quantity = t_quantity(x),
4581 best_bid_promised_date = t_promised_date(x),
4582 best_bid_number = t_bid_number(x),
4583 best_bid_currency_price = t_bid_currency_price(x),
4584 best_bid_currency_code = t_bid_currency_code(x),
4585 best_bid_first_bid_price = t_first_bid_price(x),
4586 best_bid_proxy_limit_price = t_proxy_bid_limit_price(x)
4587 WHERE auction_header_id = p_auctionHeaderId
4588 AND line_number = t_line_number(x);
4589
4590 print_log(l_api_name, p_bidNumber || ' - finished non-MAS lines');
4591
4592
4593 IF (p_bidRanking = 'MULTI_ATTRIBUTE_SCORING') THEN
4594 t_line_number.DELETE;
4595 t_price.DELETE;
4596 t_score.DELETE;
4597 t_bid_number.DELETE;
4598 t_bid_currency_price.DELETE;
4599 t_bid_currency_code.DELETE;
4600 v_counter := 1;
4601
4602 print_log(l_api_name, p_bidNumber || ' - iterating through all MAS worsened lines');
4603 FOR mas_rerank_line IN mas_bid_items_to_be_reranked(p_publishDate) LOOP
4604
4605 print_log(l_api_name, p_bidNumber || ' - line ' || mas_rerank_line.line_number || ' worsened');
4606
4607 SELECT line_number,
4608 price,
4609 total_weighted_score,
4610 bid_number,
4611 bid_currency_price,
4612 bid_currency_code
4613 INTO t_line_number(v_counter),
4614 t_price(v_counter),
4615 t_score(v_counter),
4616 t_bid_number(v_counter),
4617 t_bid_currency_price(v_counter),
4618 t_bid_currency_code(v_counter)
4619 FROM (SELECT bidline.line_number,
4620 bidline.price,
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
4630 AND (
4631 (
4632 bidheader.bid_status = 'ACTIVE'
4633 AND
4634 decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y'
4635 )
4636 OR
4637 bidheader.bid_number = p_bidNumber
4638 )
4639 AND bidline.line_number = mas_rerank_line.line_number
4640 ORDER BY bidline.total_weighted_score/bidline.price desc,
4641 bidline.publish_date asc)
4642 WHERE rownum = 1;
4643
4644 v_counter := v_counter + 1;
4645
4646 END LOOP;
4647
4648 FORALL x IN 1..t_line_number.COUNT
4649 UPDATE pon_auction_item_prices_all
4650 SET best_bid_bid_price = t_price(x),
4651 best_bid_score = t_score(x),
4652 best_bid_bid_number = t_bid_number(x),
4653 best_bid_bid_currency_price = t_bid_currency_price(x),
4654 best_bid_bid_currency_code = t_bid_currency_code(x)
4655 WHERE auction_header_id = p_auctionHeaderId
4656 AND line_number = t_line_number(x);
4657
4658 print_log(l_api_name, p_bidNumber || ' - finished MAS lines');
4659 END IF;
4660
4661 print_log(l_api_name, p_bidNumber || ' - END update_worsened_lines');
4662
4663 END update_worsened_lines;
4664
4665
4666
4667 PROCEDURE update_new_best_lines
4668 (p_auctionHeaderId NUMBER,
4669 p_bidNumber NUMBER,
4670 p_publishDate DATE,
4671 p_bidRanking VARCHAR2,
4672 p_bidCurrencyCode VARCHAR2,
4673 p_batchStart NUMBER,
4674 p_batchEnd NUMBER)
4675
4676 IS
4677
4678 BEGIN
4679
4680 UPDATE pon_auction_item_prices_all auctionline
4681 SET (auctionline.best_bid_number,
4682 auctionline.best_bid_price,
4683 auctionline.best_bid_quantity,
4684 auctionline.best_bid_promised_date,
4685 auctionline.best_bid_proxy_limit_price,
4686 auctionline.best_bid_currency_price,
4687 auctionline.best_bid_currency_code,
4688 auctionline.best_bid_first_bid_price) =
4689 (SELECT bidline.bid_number,
4690 bidline.price,
4691 bidline.quantity,
4692 bidline.promised_date,
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'
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
4708 OR
4709 NVL((SELECT is_better_proxy_price(bidline.price,
4710 bidline.bid_number,
4711 bidline.proxy_bid_flag,
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
4721 AND bestbidline.line_number = auctionline.line_number),
4722 'FALSE') = 'TRUE')
4723 AND auctionline.line_number >= p_batchStart
4724 AND auctionline.line_number <= p_batchEnd;
4725
4726
4727 IF (p_bidRanking = 'MULTI_ATTRIBUTE_SCORING') THEN
4728
4729 UPDATE pon_auction_item_prices_all auctionline
4730 SET (auctionline.best_bid_bid_number,
4731 auctionline.best_bid_bid_price,
4732 auctionline.best_bid_score,
4733 auctionline.best_bid_bid_currency_price,
4734 auctionline.best_bid_bid_currency_code) =
4735 (SELECT bidline.bid_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'
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
4751 OR
4752 NVL((SELECT is_better_proxy_price_by_score(bidline.price,
4753 bidline.total_weighted_score,
4754 bidline.proxy_bid_flag,
4755 bidline.bid_number,
4756 bidline.publish_date,
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
4766 AND bestbidline.line_number = auctionline.line_number),
4767 'FALSE') = 'TRUE')
4768 AND auctionline.line_number >= p_batchStart
4769 AND auctionline.line_number <= p_batchEnd;
4770
4771 END IF;
4772
4773 END update_new_best_lines;
4774
4775
4776
4777
4778 PROCEDURE rerank_non_group_lines
4779 (p_auctionHeaderId IN NUMBER,
4780 p_bidNumber IN NUMBER,
4781 p_publishDate IN DATE,
4782 p_oldBidNumber IN NUMBER,
4783 p_bidRanking IN VARCHAR2,
4784 p_batchStart IN NUMBER,
4785 p_batchEnd IN NUMBER,
4786 p_discard_tech_nonshort IN VARCHAR2)
4787 IS
4788
4789 -- do not change this!
4790 DEAD_LAST CONSTANT NUMBER := 999999;
4791
4792 CURSOR lines_to_be_reranked(v_publish_date DATE) IS
4793 SELECT bidline.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
4803 AND auctionline.auction_header_id = bidline.auction_header_id
4804 AND auctionline.line_number = bidline.line_number
4805 AND auctionline.group_type <> 'GROUP'
4806 AND auctionline.line_number >= p_batchStart
4807 AND auctionline.line_number <= p_batchEnd
4808 AND bidline.line_number = oldBidline.line_number(+)
4809 and oldBidline.bid_number(+) = NVL(P_oldBidNumber, -1);
4810
4811 CURSOR bidlines_to_be_reranked(v_line_number 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'
4821 FROM pon_bid_headers bidheader
4822 WHERE bidheader.bid_number = bidline.bid_number
4823 AND bidheader.bid_status = 'ACTIVE'
4824 AND decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y')
4825 AND rank BETWEEN DECODE(sign(v_new_rank - v_old_rank), 1, v_old_rank, v_new_rank)
4826 AND DECODE(sign(v_new_rank - v_old_rank), 1, v_new_rank, v_old_rank);
4827
4828
4829 TYPE t_tbl_number IS TABLE OF NUMBER
4830 INDEX BY PLS_INTEGER;
4831
4832 t_itm_line_number t_tbl_number;
4833 t_itm_bid_number t_tbl_number;
4834 t_itm_rank t_tbl_number;
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
4844 BEGIN
4845
4846 t_itm_line_number.DELETE;
4847 t_itm_bid_number.DELETE;
4848 t_itm_rank.DELETE;
4849
4850 v_counter := 1;
4851 FOR rerank_line IN lines_to_be_reranked(p_publishDate) LOOP
4852
4853 v_price := rerank_line.price;
4854 v_score := rerank_line.total_weighted_score;
4855 v_proxyFlag := rerank_line.proxy_bid_flag;
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
4865 AND bidline.line_number = rerank_line.line_number
4866 AND bidheader.bid_status = 'ACTIVE'
4867 AND decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y'
4868 AND bidheader.bid_number <> p_bidNumber
4869 AND decode(p_bidRanking, 'MULTI_ATTRIBUTE_SCORING',
4870 is_better_proxy_price_by_score(v_price,
4871 v_score,
4872 v_proxyFlag,
4873 p_bidNumber,
4874 p_publishDate,
4875 nvl(bidline.group_amount, bidline.price),
4876 bidline.total_weighted_score,
4877 bidline.trigger_bid_number,
4878 bidline.publish_date),
4879 is_better_proxy_price(v_price,
4880 p_bidNumber,
4881 v_proxyFlag,
4882 p_publishDate,
4883 nvl(bidline.group_amount, bidline.price),
4884 bidline.trigger_bid_number,
4885 bidline.publish_date)) = 'FALSE';
4886
4887 t_itm_bid_number(v_counter) := p_bidNumber;
4888 t_itm_line_number(v_counter) := rerank_line.line_number;
4889 t_itm_rank(v_counter) := v_newRank;
4890 v_counter := v_counter + 1;
4891
4892 IF (v_newRank < v_oldRank) THEN
4893
4894 FOR rerank_bidline IN bidlines_to_be_reranked(rerank_line.line_number,
4895 v_newRank, v_oldRank) LOOP
4896 t_itm_bid_number(v_counter) := rerank_bidline.bid_number;
4897 t_itm_line_number(v_counter) := rerank_line.line_number;
4898 t_itm_rank(v_counter) := rerank_bidline.rank + 1;
4899 v_counter := v_counter + 1;
4900 END LOOP;
4901
4902 ELSIF (v_newRank > v_oldRank) THEN
4903
4904 FOR rerank_bidline IN bidlines_to_be_reranked(rerank_line.line_number,
4905 v_newRank, v_oldRank) LOOP
4906 t_itm_bid_number(v_counter) := rerank_bidline.bid_number;
4907 t_itm_line_number(v_counter) := rerank_line.line_number;
4908 t_itm_rank(v_counter) := rerank_bidline.rank - 1;
4909 v_counter := v_counter + 1;
4910 END LOOP;
4911
4912 END IF;
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
4922 END rerank_non_group_lines;
4923
4924
4925 PROCEDURE update_old_best_bid_number
4926 (p_auctionHeaderId IN NUMBER,
4927 p_oldBidNumber IN NUMBER,
4928 p_bidNumber IN NUMBER,
4929 p_batchStart IN NUMBER,
4930 p_batchEnd IN NUMBER)
4931 IS
4932 BEGIN
4933
4934 UPDATE pon_auction_item_prices_all
4935 SET best_bid_number = p_bidNumber
4936 WHERE auction_header_id = p_auctionHeaderId
4937 AND best_bid_number = p_oldBidNumber
4938 AND line_number >= p_batchStart
4939 AND line_number <= p_batchEnd;
4940
4941 UPDATE pon_auction_item_prices_all
4942 SET best_bid_bid_number = p_bidNumber
4943 WHERE auction_header_id = p_auctionHeaderId
4944 AND best_bid_bid_number = p_oldBidNumber
4945 AND line_number >= p_batchStart
4946 AND line_number <= p_batchEnd;
4947
4948 END update_old_best_bid_number;
4949
4950
4951 PROCEDURE update_non_batched_part
4952 (p_auctionHeaderId NUMBER,
4953 p_bidNumber NUMBER,
4954 p_publishDate DATE,
4955 p_rankIndicator VARCHAR2,
4956 p_bidRanking VARCHAR2,
4957 p_discard_tech_nonshort VARCHAR2)
4958 IS
4959 BEGIN
4960
4961 -- UPDATE GROUPS
4962 -- applies to lines: GROUP lines that were affected by this bid
4963 -- action performed: (1) re-set best_bid* attributes in pon_auction_item_prices_all
4964 -- (2) if ranking is NUMBERING, rerank all bids for each GROUP
4965 update_and_rerank_group_lines(p_auctionHeaderId, p_bidNumber, p_publishDate,
4966 p_rankIndicator, p_bidRanking, p_discard_tech_nonshort);
4967
4968
4969 -- UPDATE WORSENED LINES
4970 -- applies to lines: non-GROUP lines where this supplier user previously held the
4971 -- best bid, but on this bid, he worsened his bid.
4972 -- action performed: (1) re-set best_bid* attributes for price-only and MAS negs
4973 -- (2) re-set best_bid_bid* attributes for MAS negs
4974 update_worsened_lines(p_auctionHeaderId, p_bidNumber, p_publishDate, p_bidRanking, p_discard_tech_nonshort);
4975
4976 END update_non_batched_part;
4977
4978
4979 PROCEDURE update_non_batched_part_auto
4980 (p_auctionHeaderId NUMBER,
4981 p_bidNumber NUMBER,
4982 p_publishDate DATE,
4983 p_rankIndicator VARCHAR2,
4984 p_bidRanking VARCHAR2,
4985 p_discard_tech_nonshort VARCHAR2)
4986 IS
4987 PRAGMA AUTONOMOUS_TRANSACTION;
4988 BEGIN
4989 update_non_batched_part(p_auctionHeaderId, p_bidNumber, p_publishDate,
4990 p_rankIndicator, p_bidRanking, p_discard_tech_nonshort);
4991 commit;
4992 END update_non_batched_part_auto;
4993
4994
4995 PROCEDURE update_batched_part
4996 (p_auctionHeaderId NUMBER,
4997 p_bidNumber NUMBER,
4998 p_publishDate DATE,
4999 p_oldBidNumber NUMBER,
5000 p_vendorSiteId NUMBER,
5001 p_bidCurrencyCode VARCHAR2,
5002 p_bidRanking VARCHAR2,
5003 p_rankIndicator VARCHAR2,
5004 p_batchStart NUMBER,
5005 p_batchEnd NUMBER,
5006 p_discard_tech_nonshort VARCHAR2)
5007 IS
5008 BEGIN
5009
5010 -- 1) UPDATE NEW BEST LINES
5011 -- applies to lines: non-GROUP lines where this supplier user has submitted a bid
5012 -- that is better than the existing best_bid.
5013 -- action performed: (1) re-set best_bid* attributes for price-only and MAS negs
5014 -- (2) re-set best_bid_bid* attributes for MAS negs
5015 update_new_best_lines(p_auctionHeaderId, p_bidNumber, p_publishDate,
5016 p_bidRanking, p_bidCurrencyCode, p_batchStart, p_batchEnd);
5017
5018
5019 -- 2) RERANK ALL NON GROUP LINES
5020 -- applies to lines: all non-GROUP lines
5021 -- action performed: reranks all the bids for each of these lines
5022 IF (p_rankIndicator = 'NUMBERING') THEN
5023 rerank_non_group_lines(p_auctionHeaderId, p_bidNumber, p_publishDate,
5024 p_oldBidNumber, p_bidRanking, p_batchStart, p_batchEnd, p_discard_tech_nonshort);
5025 update_unchanged_rank(p_auctionHeaderId,p_bidNumber,p_vendorSiteId, p_batchStart, p_batchEnd, p_discard_tech_nonshort);
5026 END IF;
5027
5028
5029 -- 3) UPDATE UNCHANGED LINES
5030 -- will go in and replace old active bid number with current bid number
5031 -- for those lines that did not change
5032 update_old_best_bid_number(p_auctionHeaderId, p_oldBidNumber, p_bidNumber, p_batchStart, p_batchEnd);
5033
5034 -- 4) UPDATE NUMBER_OF_BIDS
5035 -- for all lines on which this bid has a new bid on, we must
5036 -- increment the pon_auction_item_prices_all.number_of_bids
5037 -- by 1.
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);
5047
5048 END update_batched_part;
5049
5050 PROCEDURE update_batched_part_batch
5051 (p_auctionHeaderId NUMBER,
5052 p_bidNumber NUMBER,
5053 p_publishDate DATE,
5054 p_oldBidNumber NUMBER,
5055 p_vendorSiteId NUMBER,
5056 p_bidCurrencyCode VARCHAR2,
5057 p_bidRanking VARCHAR2,
5058 p_rankIndicator VARCHAR2,
5059 p_maxLineNumber NUMBER,
5060 p_batchSize NUMBER,
5061 p_discard_tech_nonshort VARCHAR2)
5062
5063 IS PRAGMA AUTONOMOUS_TRANSACTION;
5064
5065 v_batchStart NUMBER;
5066 v_batchEnd NUMBER;
5067 BEGIN
5068
5069 v_batchStart := 1;
5070 v_batchEnd := p_batchSize;
5071
5072 WHILE (v_batchStart <= p_maxLineNumber) LOOP
5073
5074
5075 update_batched_part(p_auctionHeaderId, p_bidNumber, p_publishDate,
5076 p_oldBidNumber, p_vendorSiteId, p_bidCurrencyCode,
5077 p_bidRanking, p_rankIndicator, v_batchStart, v_batchEnd, p_discard_tech_nonshort);
5078 commit;
5079
5080 v_batchStart := v_batchEnd + 1;
5081 IF (v_batchEnd + p_batchSize > p_maxLineNumber) THEN
5082 v_batchEnd := p_maxLineNumber;
5083 ELSE
5084 v_batchEnd := v_batchEnd + p_batchSize;
5085 END IF;
5086 END LOOP;
5087
5088 END update_batched_part_batch;
5089
5090
5091
5092
5093 --========================================================================
5094 -- PROCEDURE : update_all_ranks
5095 -- PARAMETERS:
5096 -- VERSION : current version 1.x
5097 -- initial version 1.0
5098 -- COMMENT : updates all rank information for all lines and bids
5099 --========================================================================
5100
5101 PROCEDURE update_all_ranks
5102 (p_auctionHeaderId NUMBER,
5103 p_bidNumber NUMBER,
5104 p_vendorSiteId NUMBER,
5105 p_oldBidNumber NUMBER,
5106 p_maxLineNumber NUMBER,
5107 p_batchSize NUMBER,
5108 p_discard_tech_nonshort VARCHAR2)
5109 IS
5110
5111 v_publishDate pon_bid_headers.publish_date%TYPE;
5112 v_bidCurrencyCode pon_bid_headers.bid_currency_code%TYPE;
5113
5114 v_bidRanking pon_auction_headers_all.bid_ranking%TYPE;
5115 v_rankIndicator pon_auction_headers_all.rank_indicator%TYPE;
5116
5117 v_batchingRequired BOOLEAN;
5118
5119 l_api_name CONSTANT VARCHAR2(30) := 'update_all_ranks';
5120 BEGIN
5121
5122 SELECT publish_date,
5123 bid_currency_code
5124 INTO v_publishDate,
5125 v_bidCurrencyCode
5126 FROM pon_bid_headers
5127 WHERE bid_number = p_bidNumber;
5128
5129 SELECT pah.bid_ranking,
5130 pah.rank_indicator
5131 INTO v_bidRanking,
5132 v_rankIndicator
5133 FROM pon_auction_headers_all pah
5134 WHERE pah.auction_header_id = p_auctionHeaderId;
5135
5136 v_batchingRequired := (p_maxLineNumber > p_batchSize);
5137
5138 print_log(l_api_name, p_bidNumber || ' - BEGIN update all ranks');
5139 print_log(l_api_name, p_bidNumber || ' - batching required? batch size=' || p_batchSize || '; numOfLines=' || p_maxLineNumber);
5140 print_log(l_api_name, p_bidNumber || ' - p_auctionHeaderId=' || p_auctionHeaderId);
5141 print_log(l_api_name, p_bidNumber || ' - p_bidNumber=' || p_bidNumber );
5142 print_log(l_api_name, p_bidNumber || ' - p_vendorSiteId=' || p_vendorSiteId);
5143 print_log(l_api_name, p_bidNumber || ' - p_oldBidNumber=' || p_oldBidNumber);
5144 print_log(l_api_name, p_bidNumber || ' - p_maxLineNumber=' || p_maxLineNumber );
5145
5146 -- UPDATE NON BATCHED PART
5147 -- This procedure performs two tasks:
5148 --
5149 -- 1) UPDATE GROUPS
5150 -- applies to lines: GROUP lines that were affected by this bid
5151 -- action performed: (1) re-set best_bid* attributes in pon_auction_item_prices_all
5152 -- (2) if ranking is NUMBERING, rerank all bids for each GROUP
5153 -- 2) UPDATE WORSENED LINES
5154 -- applies to lines: non-GROUP lines where this supplier user previously held the
5155 -- best bid, but on this bid, he worsened his bid.
5156 -- action performed: (1) re-set best_bid* attributes for price-only and MAS negs
5157 -- (2) re-set best_bid_bid* attributes for MAS negs
5158 print_log(l_api_name, p_bidNumber || ' - do unbatched part first');
5159 IF (v_batchingRequired) THEN
5160 update_non_batched_part_auto(p_auctionHeaderId, p_bidNumber, v_publishDate,
5161 v_rankIndicator, v_bidRanking, p_discard_tech_nonshort);
5162 ELSE
5163 update_non_batched_part(p_auctionHeaderId, p_bidNumber, v_publishDate,
5164 v_rankIndicator, v_bidRanking, p_discard_tech_nonshort);
5165 END IF;
5166
5167 -- UPDATE BATCHED PART
5168 -- This procedure performs these tasks:
5169 --
5170 -- 1) UPDATE NEW BEST LINES
5171 -- applies to lines: non-GROUP lines where this supplier user has submitted a bid
5172 -- that is better than the existing best_bid.
5173 -- action performed: (1) re-set best_bid* attributes for price-only and MAS negs
5174 -- (2) re-set best_bid_bid* attributes for MAS negs
5175 -- 2) RERANK ALL NON GROUP LINES
5176 -- applies to lines: all non-GROUP lines
5177 -- action performed: reranks all the bids for each of these lines
5178 --
5179 -- 3) UPDATE NUMBER_OF_BIDS
5180 -- for all lines on which this bid has a new bid on, we must
5181 -- increment the pon_auction_item_prices_all.number_of_bids
5182 -- by 1.
5183 print_log(l_api_name, p_bidNumber || ' - do batched part second');
5184 IF (v_batchingRequired) THEN
5185 update_batched_part_batch(p_auctionHeaderId, p_bidNumber, v_publishDate,
5186 p_oldBidNumber, p_vendorSiteId, v_bidCurrencyCode,
5187 v_bidRanking, v_rankIndicator, p_maxLineNumber, p_batchSize, p_discard_tech_nonshort);
5188 ELSE
5189 update_batched_part(p_auctionHeaderId, p_bidNumber, v_publishDate,
5190 p_oldBidNumber, p_vendorSiteId, v_bidCurrencyCode,
5191 v_bidRanking, v_rankIndicator, 1, p_maxLineNumber, p_discard_tech_nonshort);
5192 END IF;
5193
5194 print_log(l_api_name, p_bidNumber || ' - END update all ranks');
5195 END update_all_ranks;
5196
5197
5198
5199
5200 --========================================================================
5201 -- PROCEDURE : update_auction_info PUBLIC
5202 -- PARAMETERS:
5203 -- VERSION : current version 1.x
5204 -- initial version 1.0
5205 -- COMMENT : Updating auction information
5206 --========================================================================
5207
5208 PROCEDURE UPDATE_AUCTION_INFO
5209 ( p_auctionHeaderId IN NUMBER,
5210 p_bidNumber IN NUMBER,
5211 p_vendorSiteId IN NUMBER,
5212 p_isRebid IN VARCHAR2,
5213 p_prevBidNumber IN NUMBER,
5214 p_isSavingDraft IN VARCHAR2,
5215 p_isSurrogateBid IN VARCHAR2,
5216 p_loginUserId IN NUMBER,
5217 x_return_status OUT NOCOPY NUMBER,
5218 x_return_code OUT NOCOPY VARCHAR2
5219 )
5220
5221 IS
5222 --
5223
5224 l_api_name CONSTANT VARCHAR2(30) := 'update_auction_info';
5225
5226 -- auto extension variables
5227 v_autoExtendFlag pon_auction_headers_all.auto_extend_flag%TYPE;
5228 v_autoExtendAllLinesFlag pon_auction_headers_all.auto_extend_all_lines_flag%TYPE;
5229 v_proxyEnabled pon_auc_doctype_rules.display_flag%TYPE;
5230 v_tradingPartnerId pon_auction_headers_all.trading_partner_id%TYPE;
5231 v_is_paused pon_auction_headers_all.is_paused%TYPE;
5232 v_supplierViewType pon_auction_headers_all.supplier_view_type%TYPE;
5233 v_max_bid_color_sequence_id pon_auction_headers_all.max_bid_color_sequence_id%TYPE; -- Added for Live Cosnole
5234 v_closeBiddingDateH pon_auction_item_prices_all.close_bidding_date%TYPE;
5235 v_closeBiddingDate_old pon_auction_item_prices_all.close_bidding_date%TYPE;
5236 v_color_sequence_id pon_bid_headers.color_sequence_id%TYPE; --Added for Live Console
5237 v_publishDate DATE;
5238 v_bidTradingPartnerId pon_bid_headers.trading_partner_id%TYPE;
5239 v_rankIndicator pon_auction_headers_all.rank_indicator%TYPE;
5240 v_doctypeId pon_auction_headers_all.doctype_id%TYPE;
5241 v_oldBidNumber pon_bid_headers.bid_number%TYPE;
5242 v_prevActiveBidNumber pon_bid_headers.bid_number%TYPE;
5243 v_oldBidStatus pon_bid_headers.bid_status%TYPE;
5244 v_hasCloseDateReached VARCHAR2(1);
5245 v_ispricechanged VARCHAR2(1);
5246
5247 v_pfTypeAllowed pon_auction_headers_all.pf_type_allowed%TYPE;
5248 v_priceTiersIndicator pon_auction_headers_all.price_tiers_indicator%TYPE;
5249
5250 v_maxLineNumber NUMBER;
5251 v_batchSize NUMBER;
5252 v_batchingRequired BOOLEAN;
5253
5254 v_max_close_bidding_date pon_auction_headers_all.close_bidding_date%TYPE;
5255 v_max_num_of_extensions pon_auction_headers_all.number_of_extensions%TYPE;
5256
5257 l_msg_data VARCHAR2(250);
5258 l_msg_count NUMBER;
5259 l_return_status VARCHAR2(1);
5260 v_contermsExist VARCHAR2(1);
5261
5262 v_extendInterval NUMBER;
5263 --for staggered closing
5264 v_orig_close_bidding_date pon_auction_headers_all.close_bidding_date%TYPE;
5265 v_first_line_close_date pon_auction_headers_all.first_line_close_date%TYPE;
5266 v_is_staggered_auction varchar2(1);
5267 v_sealed_auction_status pon_auction_headers_all.sealed_auction_status%TYPE;
5268 v_two_part_flag pon_auction_headers_all.two_part_flag%TYPE;
5269 v_discard_tech_nonshort varchar2(1);
5270
5271 --added by Allen Yang for Surrogate Bid 2008/10/07
5272 v_technical_evaluation_status pon_auction_headers_all.technical_evaluation_status%TYPE;
5273
5274 BEGIN
5275 --
5276 -- logging
5277 print_log(l_api_name, p_bidNumber || ' - beginning of update auction info');
5278 print_log(l_api_name, ' p_auctionHeaderId=' || p_auctionHeaderId);
5279 print_log(l_api_name, ' p_bidNumber =' || p_bidNumber );
5280 print_log(l_api_name, ' p_vendorSiteId=' || p_vendorSiteId);
5281 print_log(l_api_name, ' p_isRebid=' || p_isRebid);
5282 print_log(l_api_name, ' p_prevBidNumber=' || p_prevBidNumber);
5283 print_log(l_api_name, ' p_isSavingDraft=' ||p_isSavingDraft );
5284 print_log(l_api_name, ' p_isSurrogateBid=' || p_isSurrogateBid);
5285
5286 v_ispricechanged := 'N';
5287
5288 --
5289 -- First check to see if the user's old bid was
5290 -- disqualified by the auctioneer
5291 v_oldBidNumber := p_prevBidNumber;
5292
5293 IF (v_oldBidNumber is not null) THEN
5294 SELECT bid_status
5295 INTO v_oldBidStatus
5296 FROM pon_bid_headers
5297 WHERE bid_number = v_oldBidNumber;
5298
5299 IF(v_oldBidStatus = 'DISQUALIFIED') THEN
5300 v_oldBidNumber := NULL;
5301 END IF;
5302 END IF;
5303
5304 -- (woojin) select the variables we need and at the
5305 -- same time, lock auction headers to avoid concurrent
5306 -- access by other bids
5307 SELECT auto_extend_flag,
5308 nvl(auto_extend_all_lines_flag,'Y'),
5309 close_bidding_date,
5310 trading_partner_id,
5311 rank_indicator,
5312 doctype_id,
5313 nvl(is_paused, 'N'),
5314 supplier_view_type,
5315 max_internal_line_num,
5316 DECODE(nvl(max_bid_color_sequence_id,-99),-99,-1, max_bid_color_sequence_id), --Added for Live Console
5317 pf_type_allowed,
5318 price_tiers_indicator,
5319 sealed_auction_status,
5320 two_part_flag
5321 -- added by Allen Yang for Surrogate Bid 2008/10/07
5322 ---------------------------------------------------
5323 , technical_evaluation_status
5324 ---------------------------------------------------
5325 INTO v_autoExtendFlag,
5326 v_autoExtendAllLinesFlag,
5327 v_closeBiddingDate_old,
5328 v_tradingPartnerId,
5329 v_rankIndicator,
5330 v_doctypeid,
5331 v_is_paused,
5332 v_supplierViewType,
5333 v_maxLineNumber,
5334 v_max_bid_color_sequence_id, --Added for Live Console
5335 v_pfTypeAllowed,
5336 v_priceTiersIndicator,
5337 v_sealed_auction_status,
5338 v_two_part_flag
5339 -- added by Allen Yang for Surrogate Bid 2008/10/07
5340 ---------------------------------------------------
5341 , v_technical_evaluation_status
5342 ---------------------------------------------------
5343 FROM pon_auction_headers_all pah
5344 WHERE auction_header_id = p_auctionHeaderId;
5345
5346 SELECT publish_date,
5347 trading_partner_id,
5348 color_sequence_id --Added for Live Console
5349 INTO v_publishDate,
5350 v_bidTradingPartnerId,
5351 v_color_sequence_id --Added for Live Console
5352 FROM pon_bid_headers
5353 WHERE bid_number = p_bidNumber;
5354
5355 -- check whether this bid is being placed
5356 -- after close date has been reached, note that
5357 -- only surrogate bids created by a buyer user can be
5358 -- placed after close bidding date is reached
5359 if(v_closeBiddingDate_old < sysdate) then
5360 v_hasCloseDateReached := 'Y';
5361 else
5362 v_hasCloseDateReached := 'N';
5363 end if;
5364
5365 -- for batching, we need to find out the max line number of
5366 -- this neg
5367 v_batchSize := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
5368 v_batchingRequired := (v_maxLineNumber > v_batchSize);
5369
5370
5371 -- ADD HIDDEN PRICE FACTORS
5372 -- add all price factors hidden to this supplier/site
5373 -- that are applicable to the supplier/site
5374 print_log(l_api_name, p_bidNumber || ' - adding hidden price factors');
5375 IF (v_batchingRequired) THEN
5376 add_hidden_price_factors_auto(p_bidNumber,
5377 p_auctionHeaderId,
5378 v_supplierViewType,
5379 v_bidTradingPartnerId,
5380 p_vendorSiteId,
5381 p_loginUserId);
5382 ELSE
5383 add_hidden_price_factors(p_bidNumber,
5384 p_auctionHeaderId,
5385 v_supplierViewType,
5386 v_bidTradingPartnerId,
5387 p_vendorSiteId,
5388 p_loginUserId);
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,
5398 v_priceTiersIndicator);
5399 ELSE
5400 set_pf_price_components (p_bidNumber,
5401 v_pfTypeAllowed,
5402 v_priceTiersIndicator);
5403 END IF;
5404
5405 -- ARCHIVE PAST BIDS
5406 -- (bug 2227167) this used to be done in preSave() in NegotiationResponse
5407 -- but this caused deadlocks as the archiving was done w/o a lock
5408 -- on pon_auction_headers.
5409 --
5410 -- (amendment) previously, we took care of archiving previous amendment's
5411 -- and previous round's draft bids here. however, with LAS project
5412 -- we have moved archiving those draft bids to the PL/SQL bid defaulting
5413 -- logic.
5414 print_log(l_api_name, p_bidNumber || ' - archiving past bids');
5415 IF (v_batchingRequired) THEN
5416 archive_prev_active_bids_auto(p_auctionHeaderId, p_bidNumber,
5417 p_vendorSiteId, v_oldBidNumber);
5418 ELSE
5419 archive_prev_active_bids(p_auctionHeaderId, p_bidNumber,
5420 p_vendorSiteId, v_oldBidNumber);
5421 END IF;
5422
5423 -- (Raja) initialise the global variables that track the bids generated during this publish
5424 g_bidsGenerated := fnd_table_of_number(1);
5425 g_bidsGeneratedCount := 1;
5426 g_bidsGenerated(g_bidsGeneratedCount) := p_bidNumber;
5427
5428 -- AUTO EXTENSION AND PROXY BIDDING
5429 IF (nvl(p_isSurrogateBid, 'Y') = 'N' OR
5430 (nvl(p_isSurrogateBid, 'N') = 'Y' AND
5431 nvl(v_hasCloseDateReached, 'Y') = 'N')) THEN
5432
5433 -- PROXY BIDDING
5434 -- the procedure here will calculate and place any proxy bids as
5435 -- necessary. If the auction has already closed (surrog bidding)
5436 -- then don't bother with proxy bidding. Also, we only allow
5437 -- proxy bidding for AUCTION doctypes.
5438
5439 SELECT display_flag
5440 INTO v_proxyEnabled
5441 FROM pon_auc_doctype_rules rules,
5442 pon_auc_bizrules bizrules
5443 WHERE rules.doctype_id = v_doctypeid
5444 AND rules.bizrule_id = bizrules.bizrule_id
5445 AND bizrules.name = 'ALLOW_PROXYBID';
5446
5447 IF (v_proxyEnabled = 'Y') THEN
5448 print_log(l_api_name, p_bidNumber || ' - calling subroutine update_proxy_bid');
5449
5450 IF (v_batchingRequired) THEN
5451 update_proxy_bid_auto(p_auctionHeaderId, p_bidNumber, v_oldBidNumber,
5452 p_isSurrogateBid, v_hasCloseDateReached,v_isPriceChanged);
5453 ELSE
5454 update_proxy_bid(p_auctionHeaderId, p_bidNumber, v_oldBidNumber,
5455 p_isSurrogateBid, v_hasCloseDateReached,v_isPriceChanged);
5456 END IF;
5457 END IF;
5458
5459 END IF; -- if ((nvl(p_isSurrogateBid, 'N') = 'Y' AND ...
5460
5461 -- UPDATE RANK
5462 -- the following procedure will update the ranking information for all bids
5463 print_log(l_api_name, p_bidNumber || ' - updating ranking');
5464
5465 -- For update ranking, we only care about the previous active bid
5466 -- We can not always use the source bid. For example, in the
5467 -- amendment case, if the source bid is a bid in the previous
5468 -- amendment, then its rank should not be used as a reference
5469 -- to update new ranks
5470 IF (p_isRebid = 'Y') THEN
5471 v_prevActiveBidNumber := v_oldBidNumber;
5472 ELSE
5473 v_prevActiveBidNumber := NULL;
5474 END IF;
5475
5476 --
5477 -- (uday) For a surrogate bid that is submitted after the commercial unlock in a
5478 -- two part bid the technical nonshortlisted bids should not be considered
5479 -- during ranking
5480 --
5481 IF ( nvl (p_isSurrogateBid, 'N') = 'Y' AND v_sealed_auction_status <> 'LOCKED'
5482 AND nvl (v_two_part_flag, 'N') = 'Y') THEN
5483 v_discard_tech_nonshort := 'Y';
5484 ELSE
5485 v_discard_tech_nonshort := 'N';
5486 END IF;
5487
5488 -- modified by Allen Yang for Surrogate Bid 2008/10/07
5489 -------------------------------------------------------
5490 -- for those surrogate bids submitted in technical stage, we don't update their ranks for auction.
5491 -- update_all_ranks(p_auctionHeaderId, p_bidNumber, p_vendorSiteId, v_prevActiveBidNumber, v_maxLineNumber, v_batchSize, v_discard_tech_nonshort);
5492 IF (v_two_part_flag <> 'Y' OR v_technical_evaluation_status <> 'NOT_COMPLETED' OR p_isSurrogateBid <> 'Y') THEN
5493 update_all_ranks(p_auctionHeaderId, p_bidNumber, p_vendorSiteId, v_prevActiveBidNumber, v_maxLineNumber, v_batchSize, v_discard_tech_nonshort);
5494 END IF;
5495 -------------------------------------------------------
5496
5497 IF (nvl(p_isSurrogateBid, 'Y') = 'N' OR
5498 (nvl(p_isSurrogateBid, 'N') = 'Y' AND
5499 nvl(v_hasCloseDateReached, 'Y') = 'N')) THEN
5500 -- AUTO EXTENSION
5501 -- the below procedure takes care of auto-extending any necessary
5502 -- lines if auto_extend is turned on.
5503
5504 IF (v_autoExtendFlag = 'Y' OR v_autoExtendFlag = 'y') THEN
5505 print_log(l_api_name, p_bidNumber || ' - calling subroutine auto_extend_negotiation');
5506
5507 auto_extend_negotiation (
5508 p_auctionHeaderId => p_auctionHeaderId,
5509 p_bidNumber => p_bidNumber,
5510 p_maxLineNumber => v_maxLineNumber,
5511 p_batchSize => v_batchSize,
5512 p_batchingRequired => v_batchingRequired);
5513
5514 END IF;
5515 END IF; -- if ((nvl(p_isSurrogateBid, 'N') = 'Y' AND ...
5516
5517 -- (Raja) delete the global variables that track the bids generated during this publish
5518 g_bidsGenerated.DELETE;
5519
5520 -- COLOR SEQUENCE FOR LIVE CONSOLE
5521 -- For live console, each bid is assigned a color for display
5522 -- on the bid monitor charts. If this bid does not have
5523 -- a color yet, assign one.
5524 IF (v_color_sequence_id IS NULL) THEN
5525 print_log(l_api_name, p_bidNumber || ' - updating color sequences');
5526 UPDATE PON_BID_HEADERS
5527 SET Color_Sequence_Id = v_max_bid_color_sequence_id +1
5528 WHERE bid_number = p_bidNumber;
5529
5530 v_max_bid_color_sequence_id := v_max_bid_color_sequence_id +1;
5531 END IF; -- end if (v_color_sequence_id IS NULL)
5532 print_log(l_api_name, p_bidNumber || ' - after color sequencing');
5533
5534 -- PARTIAL RESPONSE FLAG
5535 -- calculate and set the partial response flag
5536 print_log(l_api_name, p_bidNumber || ' - setting partial response flag if necessary');
5537 set_partial_response_flag(p_bidNumber);
5538
5539 -- update the best bid number from the archive bid to the new bid
5540 IF (v_oldBidNumber is not null) THEN
5541
5542 print_log(l_api_name, p_bidNumber || ' - updating aution_item_prices by replacing old_bid_number with bid_number in best_bid_number');
5543
5544 -- (woojin) may need to add batching here
5545
5546 UPDATE pon_auction_item_prices_all
5547 SET best_bid_number = p_bidNumber
5548 WHERE auction_header_id = p_auctionHeaderId
5549 AND best_bid_number = v_oldBidNumber;
5550
5551 UPDATE pon_auction_item_prices_all
5552 SET best_bid_bid_number = p_bidNumber
5553 WHERE auction_header_id = p_auctionHeaderId
5554 AND best_bid_bid_number = v_oldBidNumber;
5555
5556 ----------------------------------------
5557
5558 END IF;
5559
5560 print_log(l_api_name, p_bidNumber || ' - after setting best bid number');
5561
5562 -- UPDATE AUCTION HEADER
5563 -- here we update fields in the auction header
5564 print_log(l_api_name, p_bidNumber || ' - updating auction header');
5565 UPDATE pon_auction_headers_all
5566 SET max_bid_color_sequence_id = v_max_bid_color_sequence_id, --Added for Live Console
5567 last_update_date = sysdate,
5568 number_of_bids = (SELECT COUNT(auction_header_id)
5569 FROM pon_bid_headers
5570 WHERE auction_header_id = p_auctionHeaderId
5571 AND (bid_status in ('ACTIVE', 'ARCHIVED')
5572 OR bid_number = p_bidNumber))
5573 WHERE auction_header_id = p_auctionHeaderId;
5574
5575 IF (v_autoExtendFlag = 'Y' OR v_autoExtendFlag = 'y') THEN
5576 SELECT max(close_bidding_date),
5577 max(number_of_extensions)
5578 INTO v_max_close_bidding_date,
5579 v_max_num_of_extensions
5580 FROM pon_auction_item_prices_all al
5581 WHERE al.auction_header_id = p_auctionHeaderId;
5582
5583 IF (v_max_close_bidding_date IS NOT NULL AND
5584 v_max_num_of_extensions IS NOT NULL) THEN
5585
5586 SELECT first_line_close_date,
5587 close_bidding_date,
5588 nvl2(staggered_closing_interval,'Y','N')
5589 INTO v_first_line_close_date,
5590 v_orig_close_bidding_date,
5591 v_is_staggered_auction
5592 FROM pon_auction_headers_all
5593 WHERE auction_header_id = p_auctionHeaderId;
5594
5595 print_log(l_api_name, p_bidNumber ||
5596 'v_first_line_close_date : ' || to_char(v_first_line_close_date,'dd:mm:yy hh:mi:ss' ) ||
5597 'v_orig_close_bidding_date : ' || to_char(v_orig_close_bidding_date,'dd:mm:yy hh:mi:ss' ) ||
5598 'v_max_close_bidding_date : ' || to_char(v_max_close_bidding_date,'dd:mm:yy hh:mi:ss' )||
5599 'v_is_staggered_auction : ' || v_is_staggered_auction);
5600
5601 if(v_is_staggered_auction = 'Y' AND sysdate <= v_first_line_close_date)
5602 then
5603 v_first_line_close_date := v_first_line_close_date + (v_max_close_bidding_date -v_orig_close_bidding_date);
5604 end if;
5605 print_log(l_api_name, p_bidNumber || ' New first_line_close_date : ' || to_char(v_first_line_close_date,'dd:mm:yy hh:mi:ss' ));
5606
5607 UPDATE pon_auction_headers_all
5608 SET close_bidding_date = v_max_close_bidding_date,
5609 number_of_extensions = v_max_num_of_extensions,
5610 first_line_close_date = v_first_line_close_date
5611 WHERE auction_header_id = p_auctionHeaderId;
5612 END IF;
5613 END IF;
5614
5615 print_log(l_api_name, p_bidNumber || ' - after updating auction header');
5616
5617 -- SET BUYER BID TOTAL
5618 -- set the bid total in the buyer's view
5619 print_log(l_api_name, p_bidNumber || ' - setting buyer bid total');
5620 set_buyer_bid_total(p_auctionHeaderId, p_bidNumber);
5621 print_log(l_api_name, p_bidNumber || ' - after setting buyer bid total');
5622
5623 -- DELIVERABLES INTEGRATION
5624 -- need to also check whether the current auction has
5625 -- contract terms associated with it
5626
5627 if (PON_CONTERMS_UTL_PVT.is_contracts_installed() = FND_API.G_TRUE) then
5628
5629 begin
5630 select conterms_exist_flag into v_contermsExist
5631 from pon_auction_headers_all
5632 where auction_header_id = p_auctionHeaderId;
5633
5634 if(v_contermsExist = 'Y') then
5635 print_log(l_api_name, p_bidNumber || ': ' ||'calling subroutine PON_CONTERMS_UTL_PVT.activateDeliverables');
5636 PON_CONTERMS_UTL_PVT.activateDeliverables(p_auctionHeaderId,
5637 p_bidNumber,
5638 v_oldBidNumber,
5639 l_msg_data,
5640 l_msg_count,
5641 l_return_status);
5642
5643 if(v_autoExtendFlag = 'Y' OR v_autoExtendFlag = 'y' OR
5644 v_autoExtendAllLinesFlag ='Y' OR v_autoExtendAllLinesFlag = 'y') then
5645
5646
5647 -- Get the old close bidding date, number of extensions in the header.
5648 v_closeBiddingDateH := v_closeBiddingDate_old;
5649
5650 select max(close_bidding_date)
5651 into v_closeBiddingDateH
5652 from pon_auction_item_prices_all
5653 where auction_header_id = p_auctionHeaderId;
5654
5655 -- we need to update all the deliverables
5656 -- if and only if this bid has caused auto-extension
5657 print_log(l_api_name, p_bidNumber || ': ' ||'calling subroutine PON_CONTERMS_UTL_PVT.updateDeliverables');
5658 PON_CONTERMS_UTL_PVT.updateDeliverables(p_auctionHeaderId,
5659 v_doctypeid,
5660 v_closeBiddingDateH,
5661 l_msg_data,
5662 l_msg_count,
5663 l_return_status);
5664 end if;
5665 end if;
5666 -- exception
5667 -- when others then
5668 -- null;
5669 end;
5670 end if;
5671
5672
5673 IF v_isPriceChanged = 'Y' THEN
5674 x_return_code := 'IS_PRICE_CHANGED';
5675 x_return_status := 2;
5676 print_log(l_api_name, p_bidNumber || ' - update auction info returns with price change=Y');
5677 END IF;
5678
5679 print_log(l_api_name, p_bidNumber || ' - END update auction info');
5680
5681 END update_auction_info;
5682
5683
5684 --
5685
5686 /**
5687 * This function calculates the total price on a line including the
5688 * buyer and the supplier price factors in auction currency.
5689 *
5690 * This function will be used in view objects to display supplier's
5691 * previous round price as the start price for this line instead of the
5692 * auction line start price.
5693 *
5694 * This is as per Cendant requirement to enforce upon suppliers to
5695 * bid lower than their bid on the previous round of the negotiation
5696 *
5697 * Currently anticipated usage of this function are on View Bid Page
5698 * (ViewBidItemsVO), Negotiation Summary page (AuctionItemPricesAllVO)
5699 * and bid creation page (ResponseAMImpl)
5700 *
5701 * p_auction_header_id - current round auction header id
5702 * p_prev_auc_active_bid_number - bid number on the previous round
5703 * p_line_number - current line number
5704 * p_contract_type - negotiation contract type
5705 * p_supplier_vuiew_type - supplier view TRANSFORMED/UNTRANSFORMED
5706 * p_pf_type_allowed - allowed price factors BOTH/BUYER/SUPPLIER/NONE
5707 * p_reverse_transform_flag - a flag indicating if buyer price factors should be
5708 * applied even if suppplier view is untransformed
5709 * The price is reverse transformed during the display
5710 * time therefore VOs using this funtion expect a
5711 * transformed price. However, the printing pkg
5712 * expects untransformed price if so defined by the
5713 * supplier view.
5714 */
5715
5716
5717 FUNCTION APPLY_PRICE_FACTORS(
5718 p_auction_header_id IN NUMBER,
5719 p_prev_auc_active_bid_number IN NUMBER,
5720 p_line_number IN NUMBER,
5721 p_contract_type IN VARCHAR2,
5722 p_supplier_view_type IN VARCHAR2,
5723 p_pf_type_allowed IN VARCHAR2,
5724 p_reverse_transform_flag IN VARCHAR2
5725 )
5726 RETURN NUMBER IS
5727
5728 l_api_name CONSTANT VARCHAR2(30) := 'apply_price_factors';
5729 l_progress VARCHAR2(100) := '0';
5730
5731 l_total_price NUMBER;
5732 l_bid_line_pf_unit_price NUMBER;
5733 l_auc_pf_unit_price NUMBER;
5734
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
5744
5745 BEGIN
5746
5747 -- auction information that we need
5748 -- Query auction headers only if the required information is available
5749 print_log(l_api_name, ' - BEGIN apply_price_factors');
5750 IF (p_contract_type IS NULL OR
5751 p_supplier_view_type IS NULL OR
5752 p_pf_type_allowed IS NULL) THEN
5753
5754 l_progress := '10: fetch auction information';
5755
5756 SELECT contract_type,
5757 supplier_view_type,
5758 pf_type_allowed
5759 INTO l_contract_type,
5760 l_supplier_view_type,
5761 l_pf_type_allowed
5762 FROM pon_auction_headers_all
5763 WHERE auction_header_id = p_auction_header_id;
5764 ELSE
5765 -- assign the input parameters to the local variables.
5766 l_contract_type := p_contract_type;
5767 l_supplier_view_type := p_supplier_view_type;
5768 l_pf_type_allowed := p_pf_type_allowed;
5769 END IF;
5770
5771 l_progress := '20: perform SPO/TRANSFORMED check';
5772
5773 IF (l_supplier_view_type = 'TRANSFORMED' AND
5774 l_contract_type = 'STANDARD') THEN
5775 l_is_spo_transformed := 'Y';
5776 ELSE
5777 l_is_spo_transformed := 'N';
5778 END IF;
5779
5780 -- bid information that we need
5781
5782 l_progress := '30: fetch previous round active bid information';
5783
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
5793 -- in case the following query is not executed or has no rows.
5794 l_auc_pf_unit_price := l_bid_auction_curr_unit_price;
5795 l_bid_line_pf_unit_price := 0;
5796
5797 -- calculate the buyer price factors if
5798 -- 1. Buyer price factors are allowed
5799 -- 2. supplier view type is transformed
5800 -- 3. reverse transformed value is not required
5801
5802 IF (l_pf_type_allowed = 'BUYER' OR l_pf_type_allowed = 'BOTH') THEN
5803 IF (l_supplier_view_type = 'TRANSFORMED' OR
5804 p_reverse_transform_flag = 'N') THEN
5805 l_progress := '40: calculate buyer price factors';
5806
5807 BEGIN
5808
5809 SELECT (l_bid_auction_curr_unit_price * ppsf.percentage) +
5810 ppsf.unit_price +
5811 ppsf.fixed_amount/decode(l_is_spo_transformed,
5812 'Y', nvl(l_bid_quantity, 1),
5813 nvl(aip.quantity, 1)
5814 )
5815 INTO l_auc_pf_unit_price
5816 FROM pon_pf_supplier_formula ppsf,
5817 pon_auction_item_prices_all aip,
5818 pon_bid_headers pbh
5819 WHERE ppsf.auction_header_id = p_auction_header_id
5820 AND ppsf.line_number = p_line_number
5821 AND ppsf.trading_partner_id = pbh.trading_partner_id
5822 AND ppsf.vendor_site_id = pbh.vendor_site_id
5823 AND pbh.bid_number = p_prev_auc_active_bid_number
5824 AND aip.auction_header_id = ppsf.auction_header_id
5825 AND aip.line_number = ppsf.line_number;
5826
5827 EXCEPTION
5828
5829 WHEN NO_DATA_FOUND THEN
5830 l_auc_pf_unit_price := l_bid_auction_curr_unit_price;
5831
5832 END;
5833
5834 END IF; -- supplier view is 'TRANSFORMED'
5835 END IF; -- buyer price factors are allowed.
5836
5837 -- calculate the supplier price factors
5838 -- 1. supplier price factors are allowed
5839
5840 l_progress := '50: calculate supplier price factors';
5841 IF (l_pf_type_allowed = 'SUPPLIER' OR l_pf_type_allowed = 'BOTH') THEN
5842 SELECT nvl(sum(decode(spf.pricing_basis,
5843 'PER_UNIT', spf.auction_currency_value,
5844 'PERCENTAGE', spf.auction_currency_value/100 * l_bid_auction_curr_unit_price,
5845 (spf.auction_currency_value / decode(l_is_spo_transformed,
5846 'Y', nvl(l_bid_quantity, 1),
5847 nvl(aip.quantity, 1)
5848 )
5849 )
5850 )
5851 )
5852 ,0)
5853 INTO l_bid_line_pf_unit_price
5854 FROM pon_bid_price_elements spf,
5855 pon_auction_item_prices_all aip
5856 WHERE spf.bid_number = p_prev_auc_active_bid_number
5857 AND spf.line_number = p_line_number
5858 AND spf.sequence_number <> -10
5859 AND spf.pf_type = 'SUPPLIER'
5860 AND aip.auction_header_id = spf.auction_header_id
5861 AND aip.line_number = spf.line_number;
5862
5863 END IF;
5864
5865 -- total price in auction currency
5866 l_progress := '60: return total price in auction currency';
5867 l_total_price := l_bid_line_pf_unit_price + l_auc_pf_unit_price;
5868
5869 print_log(l_api_name, 'returned l_total_price=' || l_total_price|| ' - END apply_price_factors');
5870 RETURN l_total_price;
5871
5872 EXCEPTION
5873
5874 WHEN OTHERS THEN
5875 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)THEN
5876 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
5877 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
5878 fnd_log.string(log_level => fnd_log.level_unexpected
5879 ,module => g_pkg_name||'.'||l_api_name
5880 ,message => l_progress || ': ' || SQLERRM);
5881 fnd_log.string(log_level=>fnd_log.level_unexpected,
5882 module =>g_pkg_name ||'.'||l_api_name,
5883 message => 'Input parameter list: ' );
5884 fnd_log.string(log_level=>fnd_log.level_unexpected,
5885 module =>g_pkg_name ||'.'||l_api_name,
5886 message => 'Auction Header Id = ' || p_auction_header_id);
5887 fnd_log.string(log_level=>fnd_log.level_unexpected,
5888 module =>g_pkg_name ||'.'||l_api_name,
5889 message => 'Previous Round Active Bid Number = ' || p_prev_auc_active_bid_number);
5890 fnd_log.string(log_level=>fnd_log.level_unexpected,
5891 module =>g_pkg_name ||'.'||l_api_name,
5892 message => 'Line Number = ' || p_line_number);
5893 fnd_log.string(log_level=>fnd_log.level_unexpected,
5894 module =>g_pkg_name ||'.'||l_api_name,
5895 message => 'contract type = ' || p_contract_type);
5896 fnd_log.string(log_level=>fnd_log.level_unexpected,
5897 module =>g_pkg_name ||'.'||l_api_name,
5898 message => 'supplier view type = ' || p_supplier_view_type);
5899 fnd_log.string(log_level=>fnd_log.level_unexpected,
5900 module =>g_pkg_name ||'.'||l_api_name,
5901 message => 'pf type allowed = ' || p_pf_type_allowed);
5902 fnd_log.string(log_level=>fnd_log.level_unexpected,
5903 module =>g_pkg_name ||'.'||l_api_name,
5904 message => 'reverse tranform flag = ' || p_reverse_transform_flag);
5905 END IF;
5906 END IF;
5907
5908 RETURN NULL;
5909 END apply_price_factors;
5910
5911 --========================================================================
5912 -- PROCEDURE : updt_tech_short_lines
5913 -- PARAMETERS: x_result : Standard Error OUT parameter
5914 -- x_error_code : Standard Error OUT parameter
5915 -- x_error_msg : Standard Error OUT parameter
5916 -- p_auction_header_id : Auction Header Id
5917 -- p_bid_ranking : Bid ranking (price only/MAS)
5918 -- p_rank_indicator : Rank Indicator (Numbering/Win-Lose/None)
5919 -- p_batch_start : line_number of first line in batch
5920 -- p_batch_end : line_number of last line in batch
5921 -- COMMENT : This procedure will process each of the lines between
5922 -- p_batch_start and p_batch_end. It will do the following
5923 --
5924 -- 1. Check to see if any bid on this line has been left out
5925 -- 2. If there is no such bid then we can go ahead with the next
5926 -- line.
5927 -- 3. If there is atleast one bid then check to see if this is the
5928 -- best bid for this line.
5929 -- 4. If this is the best bid then ignoring this bid line find out
5930 -- the best bid for this line and update pon_auction_item_prices_all
5931 -- 5. If this is not the best bid then proceed to next step.
5932 -- 6. Re-order all the bid lines (ranks) ignoring the bids that have
5933 -- not been shortlisted.
5934 --========================================================================
5935 PROCEDURE updt_tech_short_lines ( -- {
5936 x_result OUT NOCOPY VARCHAR2,
5937 x_error_code OUT NOCOPY VARCHAR2,
5938 x_error_msg OUT NOCOPY VARCHAR2,
5939 p_auction_header_id IN NUMBER,
5940 p_bid_ranking IN VARCHAR2,
5941 p_rank_indicator IN VARCHAR2,
5942 p_batch_start IN NUMBER,
5943 p_batch_end IN NUMBER
5944 )
5945 IS
5946
5947 l_module_name VARCHAR2(40) := 'updt_tech_short_lines';
5948
5949 v_bid_number pon_bid_headers.bid_number%TYPE;
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;
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
5967 CURSOR all_lines_cursor IS
5968 SELECT line_number
5969 FROM pon_auction_item_prices_all
5970 WHERE auction_header_id = p_auction_header_id
5971 AND line_number >= p_batch_start
5972 AND line_number <= p_batch_end
5973 ORDER BY line_number;
5974
5975 BEGIN
5976 x_result := FND_API.G_RET_STS_SUCCESS;
5977
5978 -- For each line do the following
5979 FOR auction_item_record IN all_lines_cursor LOOP -- {
5980
5981 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
5982 FND_LOG.string(log_level => FND_LOG.level_statement,
5983 module => g_module_prefix || l_module_name,
5984 message => 'Processing line: ' || auction_item_record.line_number);
5985 END IF; --}
5986
5987 -- Check if any bid on this line has been removed from the shortlist
5988 BEGIN -- {
5989 SELECT
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
5999 AND nvl (pbh.shortlist_flag, 'Y') = 'N'
6000 AND pbh.bid_status = 'ACTIVE'
6001 AND pbip.line_number = auction_item_record.line_number
6002 AND ROWNUM = 1;
6003
6004 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6005 FND_LOG.string(log_level => FND_LOG.level_statement,
6006 module => g_module_prefix || l_module_name,
6007 message => 'Got a bid ' || v_bid_number || ' that was removed from the shortlist.');
6008 END IF; --}
6009
6010 EXCEPTION WHEN NO_DATA_FOUND THEN
6011
6012 v_bid_number := null;
6013
6014 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6015 FND_LOG.string(log_level => FND_LOG.level_statement,
6016 module => g_module_prefix || l_module_name,
6017 message => 'Could not find any bid that was removed from the shortlist.');
6018 END IF; --}
6019
6020 END; -- }
6021
6022 -- If all are shortlisted then nothing to do for this line
6023 IF (v_bid_number IS NOT null) THEN -- {
6024
6025 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6026 FND_LOG.string(log_level => FND_LOG.level_statement,
6027 module => g_module_prefix || l_module_name,
6028 message => 'v_bid_number is not null, there is one bid to be removed.');
6029 END IF; --}
6030
6031 -- Get the best bid_number for this line
6032 SELECT
6033 paip.best_bid_number,
6034 paip.best_bid_bid_number,
6035 paip.group_type
6036 INTO
6037 v_best_bid_number,
6038 v_best_bid_bid_number,
6039 v_currentline_group_type
6040 FROM
6041 pon_auction_item_prices_all paip
6042 WHERE
6043 paip.auction_header_id = p_auction_header_id
6044 AND paip.line_number = auction_item_record.line_number;
6045
6046 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6047 FND_LOG.string(log_level => FND_LOG.level_statement,
6048 module => g_module_prefix || l_module_name,
6049 message => 'Got the best bid_number. v_best_bid_number = ' ||
6050 v_best_bid_number || ', v_best_bid_bid_number = ' ||
6051 v_best_bid_bid_number || ', v_currentline_group_type = ' ||
6052 v_currentline_group_type);
6053 END IF; --}
6054
6055 IF (v_best_bid_number IS NOT NULL) THEN -- {
6056
6057 -- Check the shortlist flag for this best bid
6058 SELECT
6059 nvl (pbh.shortlist_flag, 'Y')
6060 INTO
6061 v_bestbid_shortlist_flag
6062 FROM
6063 pon_bid_headers pbh
6064 WHERE
6065 pbh.bid_number = v_best_bid_number;
6066
6067 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6068 FND_LOG.string(log_level => FND_LOG.level_statement,
6069 module => g_module_prefix || l_module_name,
6070 message => 'Got the best bid_number. ' || ', v_bestbid_shortlist_flag = ' ||
6071 v_bestbid_shortlist_flag);
6072 END IF; --}
6073
6074 IF (v_bestbid_shortlist_flag = 'Y' AND p_bid_ranking =
6075 'MULTI_ATTRIBUTE_SCORING') THEN -- {
6076
6077 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6078 FND_LOG.string(log_level => FND_LOG.level_statement,
6079 module => g_module_prefix || l_module_name,
6080 message => 'This is an MAS and the best_bid_number has been shortlisted');
6081 END IF; --}
6082
6083 SELECT
6084 nvl (pbh.shortlist_flag, 'Y')
6085 INTO
6086 v_bestbid_shortlist_flag
6087 FROM
6088 pon_bid_headers pbh
6089 WHERE
6090 pbh.bid_number = v_best_bid_bid_number;
6091
6092 END IF; -- }
6093
6094 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6095 FND_LOG.string(log_level => FND_LOG.level_statement,
6096 module => g_module_prefix || l_module_name,
6097 message => 'After MAS check. Got the best bid_number. ' || ', v_bestbid_shortlist_flag = ' ||
6098 v_bestbid_shortlist_flag);
6099 END IF; --}
6100
6101 -- If this bid has not been shortlisted then
6102 IF (v_bestbid_shortlist_flag = 'N') THEN -- {
6103
6104 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6105 FND_LOG.string(log_level => FND_LOG.level_statement,
6106 module => g_module_prefix || l_module_name,
6107 message => 'The best bid has not been shortlisted.');
6108 END IF; --}
6109
6110 BEGIN -- {
6111 -- Update the columns in pon_auction_item_prices corresponding to
6112 -- the best_bid after ignoring the non-shortlisted bids
6113 SELECT
6114 price,
6115 quantity,
6116 promised_date,
6117 bid_number,
6118 bid_currency_price,
6119 bid_currency_code,
6120 first_bid_price,
6121 proxy_bid_limit_price
6122 INTO
6123 t_price,
6124 t_quantity,
6125 t_promised_date,
6126 t_bid_number,
6127 t_bid_currency_price,
6128 t_bid_currency_code,
6129 t_first_bid_price,
6130 t_proxy_bid_limit_price
6131 FROM
6132 (SELECT
6133 bidline.line_number,
6134 bidline.price,
6135 bidline.quantity,
6136 bidline.promised_date,
6137 bidline.bid_number,
6138 bidline.bid_currency_price,
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
6148 AND bidheader.bid_number = bidline.bid_number
6149 AND bidheader.bid_status = 'ACTIVE'
6150 AND nvl (bidheader.shortlist_flag, 'Y') = 'Y'
6151 AND bidline.line_number = auction_item_record.line_number
6152 ORDER BY
6153 decode (v_currentline_group_type, 'GROUP', bidline.group_amount, bidline.price),
6154 bidline.publish_date asc)
6155 WHERE
6156 rownum = 1;
6157
6158 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6159 FND_LOG.string(log_level => FND_LOG.level_statement,
6160 module => g_module_prefix || l_module_name,
6161 message => 'Found another best bid.');
6162 END IF; --}
6163
6164 EXCEPTION WHEN NO_DATA_FOUND THEN
6165
6166 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6167 FND_LOG.string(log_level => FND_LOG.level_statement,
6168 module => g_module_prefix || l_module_name,
6169 message => 'Could not find another best bid.');
6170 END IF; --}
6171
6172 t_price := null;
6173 t_quantity := null;
6174 t_promised_date := null;
6175 t_bid_number := null;
6176 t_bid_currency_price := null;
6177 t_bid_currency_code := null;
6178 t_first_bid_price := null;
6179 t_proxy_bid_limit_price := null;
6180 END; -- }
6181
6182 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6183 FND_LOG.string(log_level => FND_LOG.level_statement,
6184 module => g_module_prefix || l_module_name,
6185 message => 'Queries values from best bid: t_price = ' || t_price
6186 || ', t_quantity = ' || t_quantity || ', t_promised_date = ' ||
6187 t_promised_date || ', t_bid_number = ' || t_bid_number
6188 || ', t_bid_currency_price = ' || t_bid_currency_price
6189 || ', t_bid_currency_code = ' || t_bid_currency_code ||
6190 ', t_first_bid_price = ' || t_first_bid_price ||
6191 ', t_proxy_bid_limit_price = ' || t_proxy_bid_limit_price);
6192 END IF; --}
6193
6194 IF (v_currentline_group_type = 'GROUP') THEN -- {
6195
6196 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6197 FND_LOG.string(log_level => FND_LOG.level_statement,
6198 module => g_module_prefix || l_module_name,
6199 message => 'Current line is a group no need to set all columns, '
6200 || 'just best_bid_number and best_bid_bid_number');
6201 END IF; --}
6202
6203 UPDATE
6204 pon_auction_item_prices_all
6205 SET
6206 best_bid_number = t_bid_number
6207 WHERE
6208 auction_header_id = p_auction_header_id
6209 AND line_number = auction_item_record.line_number;
6210
6211 ELSE -- } {
6212
6213 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6214 FND_LOG.string(log_level => FND_LOG.level_statement,
6215 module => g_module_prefix || l_module_name,
6216 message => 'This is not a GROUP line.');
6217 END IF; --}
6218
6219 UPDATE
6220 pon_auction_item_prices_all
6221 SET
6222 best_bid_price = t_price,
6223 best_bid_quantity = t_quantity,
6224 best_bid_promised_date = t_promised_date,
6225 best_bid_number = t_bid_number,
6226 best_bid_currency_price = t_bid_currency_price,
6227 best_bid_currency_code = t_bid_currency_code,
6228 best_bid_first_bid_price = t_first_bid_price,
6229 best_bid_proxy_limit_price = t_proxy_bid_limit_price
6230 WHERE
6231 auction_header_id = p_auction_header_id
6232 AND line_number = auction_item_record.line_number;
6233
6234 END IF; -- }
6235
6236 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6237 FND_LOG.string(log_level => FND_LOG.level_statement,
6238 module => g_module_prefix || l_module_name,
6239 message => 'Updated pon_auction_item_prices_all');
6240 END IF; --}
6241
6242 IF (p_bid_ranking = 'MULTI_ATTRIBUTE_SCORING') THEN -- {
6243
6244 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6245 FND_LOG.string(log_level => FND_LOG.level_statement,
6246 module => g_module_prefix || l_module_name,
6247 message => 'This is an MAS auction.');
6248 END IF; --}
6249
6250 IF (v_currentline_group_type = 'GROUP') THEN -- {
6251
6252 UPDATE
6253 pon_auction_item_prices_all
6254 SET
6255 best_bid_bid_number = decode (p_bid_ranking,
6256 'MULTI_ATTRIBUTE_SCORING', t_bid_number,
6257 null)
6258 WHERE
6259 auction_header_id = p_auction_header_id
6260 AND line_number = auction_item_record.line_number;
6261
6262 ELSE -- } {
6263
6264 BEGIN -- {
6265 SELECT
6266 price,
6267 total_weighted_score,
6268 bid_number,
6269 bid_currency_price,
6270 bid_currency_code
6271 INTO
6272 t_price,
6273 t_score,
6274 t_bid_number,
6275 t_bid_currency_price,
6276 t_bid_currency_code
6277 FROM
6278 (SELECT
6279 bidline.line_number,
6280 bidline.price,
6281 bidline.total_weighted_score,
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
6291 AND bidheader.bid_number = bidline.bid_number
6292 AND bidheader.bid_status = 'ACTIVE'
6293 AND nvl (bidheader.shortlist_flag, 'Y') = 'Y'
6294 AND bidline.line_number = auction_item_record.line_number
6295 ORDER BY
6296 bidline.total_weighted_score/bidline.price desc,
6297 bidline.publish_date asc)
6298 WHERE
6299 rownum = 1;
6300 EXCEPTION WHEN NO_DATA_FOUND THEN
6301 t_price := null;
6302 t_score := null;
6303 t_bid_number := null;
6304 t_bid_currency_price := null;
6305 t_bid_currency_code := null;
6306 END; -- }
6307
6308 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6309 FND_LOG.string(log_level => FND_LOG.level_statement,
6310 module => g_module_prefix || l_module_name,
6311 message => 'Queries best MAS bid values: t_price = ' || t_price
6312 || 't_score = ' || t_score || ', t_bid_number = ' ||
6313 t_bid_number || ', t_bid_currency_price = ' ||
6314 t_bid_currency_price || ', t_bid_currency_code = ' ||
6315 t_bid_currency_code);
6316 END IF; --}
6317
6318 UPDATE
6319 pon_auction_item_prices_all
6320 SET
6321 best_bid_bid_price = t_price,
6322 best_bid_score = t_score,
6323 best_bid_bid_number = t_bid_number,
6324 best_bid_bid_currency_price = t_bid_currency_price,
6325 best_bid_bid_currency_code = t_bid_currency_code
6326 WHERE
6327 auction_header_id = p_auction_header_id
6328 AND line_number = auction_item_record.line_number;
6329
6330 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6331 FND_LOG.string(log_level => FND_LOG.level_statement,
6332 module => g_module_prefix || l_module_name,
6333 message => 'Updated pon_auction_item_prices_all');
6334 END IF; --}
6335
6336 END IF; -- }
6337
6338 END IF; -- }
6339
6340 END IF; -- }
6341
6342 -- Do the resetting of ranks only if the rank indicator is numbering
6343 IF (p_rank_indicator = 'NUMBERING') THEN -- {
6344
6345 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6346 FND_LOG.string(log_level => FND_LOG.level_statement,
6347 module => g_module_prefix || l_module_name,
6348 message => 'The rank indicator for this auction is NUMBERING.');
6349 END IF; --}
6350
6351 -- Empty out the table of numbers before using them
6352 v_bid_numbers_bulk.DELETE;
6353 v_rank_bulk.DELETE;
6354
6355 -- Bulk collect all the bid numbers, rank, status of bids that are
6356 -- active and shortlisted ordered by rank
6357 SELECT
6358 pbh.bid_number,
6359 pbip.rank
6360 BULK COLLECT INTO
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'
6370 AND pbip.line_number = auction_item_record.line_number
6371 AND nvl (pbh.shortlist_flag, 'Y') = 'Y'
6372 ORDER BY
6373 pbip.rank;
6374
6375 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6376 FND_LOG.string(log_level => FND_LOG.level_statement,
6377 module => g_module_prefix || l_module_name,
6378 message => 'Bulk collected the ranks and bid numbers.');
6379 END IF; --}
6380
6381 -- Loop over and set the rank starting from 1
6382 FOR x IN 1..v_bid_numbers_bulk.COUNT LOOP -- {
6383 v_rank_bulk (x) := x;
6384
6385 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6386 FND_LOG.string(log_level => FND_LOG.level_statement,
6387 module => g_module_prefix || l_module_name,
6388 message => 'Setting rank as ' || v_rank_bulk (x) || ' for bid '
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)
6398 AND line_number = auction_item_record.line_number;
6399
6400 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6401 FND_LOG.string(log_level => FND_LOG.level_statement,
6402 module => g_module_prefix || l_module_name,
6403 message => 'Done updating the ranks for all the bid items.');
6404 END IF; --}
6405
6406 END IF; -- }
6407
6408 END IF; --}
6409
6410 END IF; --}
6411
6412 END LOOP; -- }
6413
6414 EXCEPTION
6415 WHEN OTHERS THEN
6416 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
6417 x_error_code := SQLCODE;
6418 x_error_msg := SUBSTR(SQLERRM, 1, 100);
6419
6420 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
6421 FND_LOG.string (log_level => FND_LOG.level_exception,
6422 module => g_module_prefix || l_module_name,
6423 message => 'Unexpected exception occured error_code = ' || x_error_code ||
6424 ', error_message = ' || x_error_msg);
6425 END IF;
6426
6427 END; --}
6428
6429 --========================================================================
6430 -- PROCEDURE : updt_tech_short_lines_batched
6431 -- PARAMETERS: x_result : Standard Error OUT parameter
6432 -- x_error_code : Standard Error OUT parameter
6433 -- x_error_msg : Standard Error OUT parameter
6434 -- p_auction_header_id : Auction Header Id
6435 -- p_bid_ranking : Bid ranking (price only/MAS)
6436 -- p_rank_indicator : Rank Indicator (Numbering/Win-Lose/None)
6437 -- p_max_line_number : The max line number in this auction
6438 -- p_batch_size : Batch size (in case of super large)
6439 -- COMMENT : This method is a wrapper over updt_tech_short_lines to call
6440 -- that method multiple times in case of batching.
6441 --
6442 --========================================================================
6443 PROCEDURE updt_tech_short_lines_batched ( -- {
6444 x_result OUT NOCOPY VARCHAR2,
6445 x_error_code OUT NOCOPY VARCHAR2,
6446 x_error_msg OUT NOCOPY VARCHAR2,
6447 p_auction_header_id IN NUMBER,
6448 p_bid_ranking IN VARCHAR2,
6449 p_rank_indicator IN VARCHAR2,
6450 p_max_line_number IN NUMBER,
6451 p_batch_size IN NUMBER
6452 )
6453 IS
6454
6455 PRAGMA AUTONOMOUS_TRANSACTION;
6456
6457 v_batch_start NUMBER;
6458 v_batch_end NUMBER;
6459 l_module_name CONSTANT VARCHAR2 (40) := 'updt_tech_short_lines_batched';
6460
6461 BEGIN
6462
6463 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN --{
6464 FND_LOG.string(log_level => FND_LOG.level_procedure,
6465 module => g_module_prefix || l_module_name,
6466 message => 'Entering procedure with p_auction_header_id: ' || p_auction_header_id || '; p_bid_ranking: ' || p_bid_ranking || '; p_max_line_number: '
6467 || p_max_line_number || '; p_batch_size: ' || p_batch_size);
6468 END IF; --}
6469
6470 x_result := FND_API.G_RET_STS_SUCCESS;
6471
6472 v_batch_start := 1;
6473 v_batch_end := p_batch_size;
6474
6475 WHILE (v_batch_start <= p_max_line_number) LOOP -- {
6476
6477 updt_tech_short_lines (x_result, x_error_code, x_error_msg,
6478 p_auction_header_id, p_bid_ranking, p_rank_indicator, v_batch_start,
6479 v_batch_end);
6480 commit;
6481
6482 v_batch_start := v_batch_end + 1;
6483 IF (v_batch_end + p_batch_size > p_max_line_number) THEN -- {
6484 v_batch_end := p_max_line_number;
6485 ELSE
6486 v_batch_end := v_batch_end + p_batch_size;
6487 END IF; -- }
6488
6489 END LOOP; -- }
6490 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN --{
6491 FND_LOG.string(log_level => FND_LOG.level_procedure,
6492 module => g_module_prefix || l_module_name,
6493 message => 'Leaving procedure');
6494 END IF; --}
6495
6496 EXCEPTION
6497 WHEN OTHERS THEN
6498 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
6499 x_error_code := SQLCODE;
6500 x_error_msg := SUBSTR(SQLERRM, 1, 100);
6501
6502 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
6503 FND_LOG.string (log_level => FND_LOG.level_exception,
6504 module => g_module_prefix || l_module_name,
6505 message => 'Unexpected exception occured error_code = ' || x_error_code ||
6506 ', error_message = ' || x_error_msg);
6507 END IF;
6508
6509 END; -- }
6510
6511 -- (uday)
6512 --========================================================================
6513 -- PROCEDURE : update_auction_info_tech_short
6514 -- PARAMETERS: x_result : Standard Error OUT parameter
6515 -- x_error_code : Standard Error OUT parameter
6516 -- x_error_msg : Standard Error OUT parameter
6517 -- p_auction_header_id : Auction Header Id
6518 -- p_user_id : The user requesting the unlock commercial
6519 -- COMMENT : This method will take care of commercially unlocking the
6520 -- RFQ. It will re-rank the bids after excluding the non-shortlisted
6521 -- bids.
6522 --========================================================================
6523 PROCEDURE update_auction_info_tech_short ( -- {
6524 x_result OUT NOCOPY VARCHAR2,
6525 x_error_code OUT NOCOPY VARCHAR2,
6526 x_error_msg OUT NOCOPY VARCHAR2,
6527 p_auction_header_id IN NUMBER,
6528 p_user_id IN NUMBER
6529 )
6530 IS
6531
6532 l_module_name VARCHAR2(40) := 'update_auction_info_tech_short';
6533 l_reranking_required VARCHAR2(1) := 'Y';
6534
6535 v_rank_indicator PON_AUCTION_HEADERS_ALL.RANK_INDICATOR%TYPE;
6536 v_bid_ranking PON_AUCTION_HEADERS_ALL.BID_RANKING%TYPE;
6537 v_max_internal_line_num PON_AUCTION_HEADERS_ALL.MAX_INTERNAL_LINE_NUM%TYPE;
6538 v_bid_number PON_BID_HEADERS.BID_NUMBER%TYPE;
6539
6540 v_batch_size NUMBER;
6541 v_batching_required BOOLEAN;
6542
6543 BEGIN
6544
6545 x_result := FND_API.G_RET_STS_SUCCESS;
6546
6547 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN --{
6548 FND_LOG.string(log_level => FND_LOG.level_procedure,
6549 module => g_module_prefix || l_module_name,
6550 message => 'Entered procedure with p_auction_header_id = ' ||
6551 p_auction_header_id || ', p_user_id = ' ||
6552 p_user_id);
6553 END IF; --}
6554
6555 -- Check if there is any bid that has been removed from the shortlist
6556 BEGIN --{
6557
6558 SELECT
6559 bid_number
6560 INTO
6561 v_bid_number
6562 FROM
6563 pon_bid_headers
6564 WHERE
6565 auction_header_id = p_auction_header_id
6566 AND nvl (shortlist_flag, 'Y') = 'N'
6567 AND bid_status = 'ACTIVE'
6568 AND ROWNUM = 1;
6569
6570 l_reranking_required := 'Y';
6571
6572 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6573 FND_LOG.string(log_level => FND_LOG.level_statement,
6574 module => g_module_prefix || l_module_name,
6575 message => 'Found a bid that was not shortlisted.');
6576 END IF; --}
6577
6578 EXCEPTION WHEN NO_DATA_FOUND THEN
6579 l_reranking_required := 'N';
6580
6581 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6582 FND_LOG.string(log_level => FND_LOG.level_statement,
6583 module => g_module_prefix || l_module_name,
6584 message => 'Could not find a bid that was not shortlisted.');
6585 END IF; --}
6586
6587 END; --}
6588
6589 -- Take lock on auction headers all
6590 SELECT
6591 rank_indicator,
6592 bid_ranking,
6593 max_internal_line_num
6594 INTO
6595 v_rank_indicator,
6596 v_bid_ranking,
6597 v_max_internal_line_num
6598 FROM
6599 pon_auction_headers_all
6600 WHERE
6601 auction_header_id = p_auction_header_id
6602 FOR
6603 UPDATE OF close_bidding_date;
6604
6605 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6606 FND_LOG.string(log_level => FND_LOG.level_statement,
6607 module => g_module_prefix || l_module_name,
6608 message => 'rank_indicator = ' || v_rank_indicator ||
6609 ', bid_ranking = ' || v_bid_ranking ||
6610 ', max_internal_line_num = ' || v_max_internal_line_num);
6611 END IF; --}
6612
6613 -- Copy over the shortlist flag into technical_short_flag in pon_bid_headers
6614 UPDATE
6615 pon_bid_headers pbh
6616 SET
6617 pbh.last_update_date = sysdate,
6618 pbh.last_updated_by = fnd_global.user_id,
6619 pbh.technical_shortlist_flag = decode (pbh.bid_status, 'ACTIVE', nvl (pbh.shortlist_flag, 'Y'), 'N')
6620 WHERE
6621 pbh.auction_header_id = p_auction_header_id;
6622
6623 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
6624 FND_LOG.string(log_level => FND_LOG.level_statement,
6625 module => g_module_prefix || l_module_name,
6626 message => 'Done with updating pon_bid_headers.');
6627 END IF; --}
6628
6629 --While commercial unlocking the 2 part RFQ, reset the scores to 'not entered'
6630 -- so that user will enter scores for commercial stage.
6631 UPDATE pon_team_member_bid_scores
6632 SET SCORE_STATUS = 'NA'
6633 WHERE
6634 SCORE_STATUS = 'SUBMIT'
6635 AND auction_header_id = p_auction_header_id;
6636
6637 -- Re-ranking is required only if we have atleast one non-shortlisted bid
6638 -- in this RFQ.
6639 IF (l_reranking_required = 'Y') THEN -- {
6640
6641 -- for batching, we need to find out the max line number of
6642 -- this negotiation
6643 v_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
6644 v_batching_required := (v_max_internal_line_num > v_batch_size);
6645
6646 IF (v_batching_required) THEN -- {
6647 updt_tech_short_lines_batched (x_result, x_error_code, x_error_msg,
6648 p_auction_header_id, v_bid_ranking, v_rank_indicator,
6649 v_max_internal_line_num, v_batch_size);
6650 ELSE
6651 updt_tech_short_lines (x_result, x_error_code, x_error_msg,
6652 p_auction_header_id, v_bid_ranking, v_rank_indicator, 1, v_max_internal_line_num);
6653 END IF; -- }
6654
6655 END IF; -- }
6656
6657 -- Unlock the commercial part of the auction
6658 UPDATE
6659 pon_auction_headers_all paha
6660 SET
6661 paha.last_update_date = sysdate,
6662 paha.last_updated_by = fnd_global.user_id,
6663 paha.scoring_lock_date = null,
6664 paha.scoring_lock_tp_contact_id = p_user_id,
6665 paha.sealed_auction_status = 'UNLOCKED',
6666 paha.sealed_actual_unlock_date = sysdate,
6667 paha.sealed_unlock_tp_contact_id = p_user_id
6668 WHERE
6669 paha.auction_header_id = p_auction_header_id;
6670
6671 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN --{
6672 FND_LOG.string(log_level => FND_LOG.level_procedure,
6673 module => g_module_prefix || l_module_name,
6674 message => 'Leaving procedure');
6675 END IF; --}
6676
6677 EXCEPTION
6678 WHEN OTHERS THEN
6679 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
6680 x_error_code := SQLCODE;
6681 x_error_msg := SUBSTR(SQLERRM, 1, 100);
6682
6683 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
6684 FND_LOG.string (log_level => FND_LOG.level_exception,
6685 module => g_module_prefix || l_module_name,
6686 message => 'Unexpected exception occured error_code = ' || x_error_code ||
6687 ', error_message = ' || x_error_msg);
6688 END IF;
6689
6690 END update_auction_info_tech_short; --}
6691
6692 END PON_AUCTION_HEADERS_PKG;