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