1 PACKAGE BODY PON_AWARD_PKG as
2 -- $Header: PONAWRDB.pls 120.63.12020000.5 2013/04/29 12:04:55 hvutukur ship $
3
4 -- a collection that stores numbers
5 TYPE integerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6
7 g_debug_mode CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8
9 procedure award_child_line(
10 p_auction_header_id IN Number,
11 p_line_num IN Number,
12 p_award_lines IN t_award_lines,
13 p_auctioneer_id IN Number);
14
15 /* Bug : 16721126 : This function checks if supplier placed offer on a line or not?
16 * If supplier does not place offer on a line, record is deleted
17 * from pon_bid_item_prices table.
18 */
19 FUNCTION check_bid_line_exist(p_auction_header_id IN Number,
20 p_bid_number IN NUMBER,
21 p_line_number IN NUMBER)
22 RETURN NUMBER
23 IS
24
25 l_count NUMBER := 0;
26 BEGIN
27
28 SELECT Count(1)
29 INTO l_count
30 FROM pon_bid_item_prices
31 WHERE auction_header_id = p_auction_header_id
32 AND bid_number = p_bid_number
33 AND auction_line_number = p_line_number;
34
35 RETURN l_count;
36
37 END check_bid_line_exist;
38
39 -- choli update for emd
40 PROCEDURE NotifyEmdAdmin(
41 p_auction_header_id NUMBER, -- 2
42 p_emd_admin_name VARCHAR2, -- 3
43 p_auction_tp_name VARCHAR2, -- 4
44 p_auction_title VARCHAR2, -- 5
45 p_auction_header_id_encrypted VARCHAR2, -- 6
46 x_doc_number_dsp PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE) IS
47
48
49
50 x_number_awarded NUMBER;
51 x_number_rejected NUMBER;
52 x_sequence NUMBER;
53 x_itemtype VARCHAR2(8) := 'PONAWARD';
54 x_itemkey VARCHAR2(50);
55 x_bid_list VARCHAR2(1);
56 x_progress VARCHAR2(3);
57 x_bid_contact_tp_dp_name varchar2(240);
58 x_auction_type varchar2(30);
59 x_auction_type_name varchar2(30) := '';
60 x_event_title varchar2(80);
61 x_event_id NUMBER;
62 x_auction_open_bidding_date DATE;
63 x_auction_close_bidding_date DATE;
64 x_language_code VARCHAR2(3) := null;
65 x_timezone VARCHAR2(80);
66 x_newstarttime DATE;
67 x_newendtime DATE;
68 x_newawardtime DATE;
69 x_doctype_group_name VARCHAR2(60);
70 x_msg_suffix VARCHAR2(3) := '';
71
72 x_auction_round_number NUMBER;
73 x_doctype_id_value NUMBER;
74 x_oex_timezone VARCHAR2(80);
75 x_bidder_contact_id NUMBER;
76 x_timezone_disp VARCHAR2(240);
77 x_bid VARCHAR2(10);
78 x_bid_caps VARCHAR2(10);
79 x_note_to_supplier PON_BID_HEADERS.NOTE_TO_SUPPLIER%TYPE;
80 x_view_quote_url_supplier VARCHAR2(2000);
81 x_award_date PON_AUCTION_HEADERS_ALL.AWARD_DATE%TYPE;
82 x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
83 x_tp_display_name PON_BID_HEADERS.TRADING_PARTNER_NAME%TYPE;
84 x_tp_address_name PON_BID_HEADERS.VENDOR_SITE_CODE%TYPE;
85 x_preview_date DATE;
86 x_preview_date_in_tz DATE;
87 x_timezone1_disp VARCHAR2(240);
88 x_has_items_flag PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
89 x_staggered_closing_interval NUMBER;
90 x_staggered_close_note VARCHAR2(1000);
91 x_bid_award_status PON_BID_HEADERS.AWARD_STATUS%TYPE;
92
93
94
95 BEGIN
96
97 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
98 fnd_log.string(log_level => fnd_log.level_unexpected
99 ,module => 'pon_award_pkg.NotifyEmdAdmin'
100 ,message => 'Start calling NotifyEmdAdmin');
101 END IF;
102 x_progress := '010';
103
104 --
105 -- Get the bidder's language code so that the c1_bid_info
106 -- has right value for x_language_code
107 --
108 IF p_emd_admin_name is not null THEN
109 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(p_emd_admin_name,x_language_code);
110 END IF;
111
112 -- Set the userenv language so the message token (attribute) values that we retrieve using the
113 -- getMessage call return the message in the correct language => x_language_code
114
115
116 pon_auction_pkg.SET_SESSION_LANGUAGE(null, x_language_code);
117
118 --
119 -- Get next value in sequence for itemkey
120 --
121
122 SELECT pon_auction_wf_acbid_s.nextval
123 INTO x_sequence
124 FROM dual;
125
126 --
127 -- get the contact name and auction type
128 --
129
130
131 x_progress := '020';
132
133
134
135 x_itemkey := (to_char(p_emd_admin_name)||'-'||to_char(x_sequence));
136
137 x_progress := '022';
138
139 --
140 -- Create the wf process
141 --
142
143 wf_engine.CreateProcess(itemtype => x_itemtype,
144 itemkey => x_itemkey,
145 process => 'NOTIFY_EMD_ADMIN_PROCESS');
146
147 --
148 -- Set all the item attributes
149 --
150 wf_engine.SetItemAttrNumber (itemtype => x_itemtype,
151 itemkey => x_itemkey,
152 aname => 'AUCTION_ID',
153 avalue => p_auction_header_id);
154 /* Setting the Company header attribute */
155 wf_engine.SetItemAttrText(itemtype => x_itemtype
156 ,itemkey => x_itemkey
157 ,aname => 'AUCTION_TP_NAME'
158 ,avalue => p_auction_tp_name);
159 /* wf_engine.SetItemAttrText (itemtype => x_itemtype,
160 itemkey => x_itemkey,
161 aname => 'PON_AUC_WF_AWARD_SUBJECT',
162 avalue => pon_auction_pkg.getMessage('PON_AUC_WF_AWARD_SUBJECT', x_msg_suffix,
163 'DOC_NUMBER', x_doc_number_dsp,
164 'AUCTION_TITLE', pon_auction_pkg.replaceHtmlChars(p_auction_title))); */
165
166 wf_engine.SetItemAttrText (itemtype => x_itemtype,
167 itemkey => x_itemkey,
168 aname => 'PON_AUC_WF_AWARD_SUBJECT',
169 avalue => 'Auction' || x_doc_number_dsp || '(' || p_auction_title || ') has been awarded');
170 /* Setting the negotiation title header attribute */
171 wf_engine.SetItemAttrText(itemtype => x_itemtype
172 ,itemkey => x_itemkey
173 ,aname => 'AUCTION_TITLE'
174 ,avalue => pon_auction_pkg.replaceHtmlChars(p_auction_title));
175 wf_engine.SetItemAttrText (itemtype => x_itemtype,
176 itemkey => x_itemkey,
177 aname => 'AUCTION_TYPE_NAME',
178 avalue => x_auction_type_name);
179
180 wf_engine.SetItemAttrText (itemtype => x_itemtype,
181 itemkey => x_itemkey,
182 aname => 'EMD_APPROVER',
183 avalue => p_emd_admin_name);
184 -- Bug 4295915: Set the workflow owner
185 wf_engine.SetItemAttrText (itemtype => x_itemtype,
186 itemkey => x_itemkey,
187 aname => 'ORIGIN_USER_NAME',
188 avalue => fnd_global.user_name);
189
190 wf_engine.SetItemOwner(itemtype => x_itemtype,
191 itemkey => x_itemkey,
192 owner => fnd_global.user_name);
193
194
195 --
196 -- Start the workflow
197 --
198
199 wf_engine.StartProcess(itemtype => x_itemtype,
200 itemkey => x_itemkey );
201 pon_auction_pkg.UNSET_SESSION_LANGUAGE;
202
203 x_progress := '029';
204 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
205 fnd_log.string(log_level => fnd_log.level_unexpected
206 ,module => 'pon_award_pkg.NotifyEmdAdmin'
207 ,message => 'End calling NotifyEmdAdmin');
208 END IF;
209
210 END;
211
212 PROCEDURE update_all_bid_item_prices
213 (
214 p_bid_number IN NUMBER,
215 p_award_status IN VARCHAR2,
216 p_award_date IN DATE,
217 p_auctioneer_id IN NUMBER
218 ) IS
219
220 l_max_line_number NUMBER;
221 l_batch_size NUMBER;
222 l_batch_start NUMBER;
223 l_batch_end NUMBER;
224 l_commit_flag BOOLEAN;
225
226 BEGIN
227
228 -- by default, we do not want to commit intermittently
229 l_commit_flag := FALSE;
230
231 -- just set award_qty same as bid_qty
232 select nvl(max(line_number),0)
233 into l_max_line_number
234 from pon_bid_item_prices
235 where bid_number = p_bid_number
236 AND auction_line_number <> -1; --Unsolicited Lines Project : Fetch only solicitation lines.
237
238 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
239
240 l_batch_start := 1;
241 IF (l_max_line_number <l_batch_size) THEN
242 l_batch_end := l_max_line_number;
243 ELSE
244 l_commit_flag := TRUE; -- commit if we are going to loop over multiple times
245 l_batch_end := l_batch_size;
246 END IF;
247
248
249 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop
250
251 UPDATE PON_BID_ITEM_PRICES pbip
252 SET
253 (pbip.award_status,
254 pbip.award_quantity,
255 pbip.award_date,
256 pbip.last_update_date,
257 pbip.last_updated_by,
258 pbip.award_price,
259 pbip.award_shipment_number) =
260 (
261 select
262 p_award_status,
263 decode (paip.group_type, 'LOT_LINE', null,
264 'GROUP', null,
265 decode (paha.contract_type, 'BLANKET',paip.quantity,
266 decode (paip.order_type_lookup_code, 'FIXED PRICE', 1,
267 'AMOUNT', 1,
268 'RATE', 1, pbip.quantity ))),
269 p_award_date,
270 p_award_date,
271 p_auctioneer_id,
272 pbip.price,
273 null
274 from
275 pon_auction_item_prices_all paip,
276 pon_auction_headers_all paha
277 where pbip.bid_number = p_bid_number
278 and pbip.auction_header_id = paip.auction_header_id
279 and pbip.line_number = paip.line_number
280 and paha.auction_header_id = pbip.auction_header_id
281 )
282 where
283 pbip.bid_number = p_bid_number and
284 pbip.line_number >= l_batch_start and
285 pbip.line_number <= l_batch_end AND
286 pbip.auction_line_number IN (SELECT line_number
287 FROM pon_auction_item_prices_all
288 WHERE auction_header_id = pbip.auction_header_id
289 AND Nvl(award_status, 'NO') <> 'COMPLETED'); --Staggered Awards project
290
291 l_batch_start := l_batch_end + 1;
292
293 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
294 l_batch_end := l_max_line_number;
295 ELSE
296 l_batch_end := l_batch_end + l_batch_size;
297 END IF;
298
299
300 IF(l_commit_flag = TRUE) THEN
301 COMMIT;
302 END IF;
303
304 END LOOP; --} --end-loop
305
306 END update_all_bid_item_prices;
307
308
309 PROCEDURE update_all_auction_item_prices
310 (
311 p_auction_id IN NUMBER,
312 p_bid_number IN NUMBER,
313 p_award_date IN DATE,
314 p_auctioneer_id IN NUMBER
315 ) IS
316
317 l_batch_size NUMBER;
318 l_batch_start NUMBER;
319 l_batch_end NUMBER;
320 l_max_line_number NUMBER;
321 l_commit_flag BOOLEAN;
322
323 BEGIN
324
325 -- by default, we do not want to commit intermittently
326 l_commit_flag := FALSE;
327
328 select nvl(max(line_number),0)
329 into l_max_line_number
330 from pon_auction_item_prices_all
331 where auction_header_id = p_auction_id;
332
333 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
334
335 l_batch_start := 1;
336
337 IF (l_max_line_number <l_batch_size) THEN
338 l_batch_end := l_max_line_number;
339 ELSE
340 l_commit_flag := TRUE; -- commit if we are going to loop over multiple times
341 l_batch_end := l_batch_size;
342 END IF;
343
344 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop
345
346 UPDATE pon_auction_item_prices_all paip
347 SET
348 (paip.award_status,
349 paip.awarded_quantity,
350 paip.award_mode,
351 paip.last_update_date,
352 paip.last_updated_by) =
353 (
354 select
355 decode (pbip.award_status, 'AWARDED', 'AWARDED', 'REJECTED', 'AWARDED', 'PARTIAL', 'AWARDED', to_char(null)),
356 decode (paip.group_type, 'LOT_LINE', null,
357 'GROUP', null,
358 decode (paip.order_type_lookup_code, 'FIXED PRICE', 1,
359 'AMOUNT', 1,
360 'RATE', 1,
361 decode (paha.contract_type, 'BLANKET', 1, 'CONTRACT', 1, pbip.quantity) ) ),
362 g_AWARD_QUOTE,
363 p_award_date,
364 p_auctioneer_id
365 from
366 pon_bid_item_prices pbip,
367 pon_auction_headers_all paha
368 where pbip.bid_number = p_bid_number
369 and pbip.auction_header_id = paip.auction_header_id
370 and pbip.line_number = paip.line_number
371 and paha.auction_header_id = pbip.auction_header_id
372 )
373 where
374 paip.auction_header_id = p_auction_id and
375 paip.line_number >= l_batch_start and
376 paip.line_number <= l_batch_end AND
377 Nvl(paip.award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
378
379 l_batch_start := l_batch_end + 1;
380
381 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
382 l_batch_end := l_max_line_number;
383 ELSE
384 l_batch_end := l_batch_end + l_batch_size;
385 END IF;
386
387 IF(l_commit_flag = TRUE) THEN
388 COMMIT;
389 END IF;
390
391 END LOOP; --} --end-loop- batching
392
393 --
394 END update_all_auction_item_prices;
395
396
397
398 -- This method is kind of redundant because we now use the
399 -- award status flag to determine which item is awarded and
400 -- which is not.
401 -- So removed all the old code to delete unawarded items and only
402 -- doing an update of the award quantity
403
404 PROCEDURE clean_unawarded_items (p_batch_id IN NUMBER) IS
405
406 BEGIN
407
408 -- update the award quantity to 0 for those fields that have award quantity
409 -- less than 0
410 UPDATE pon_award_items_interface
411 SET award_quantity = 0
412 WHERE batch_id = p_batch_id
413 AND award_quantity < 0;
414
415 COMMIT;
416
417 END clean_unawarded_items;
418
419
420 /*
421 Reject the active bids on negotiation items that were not awarded
422 and cancel backing requisitions for the items, if any
423 */
424 PROCEDURE reject_unawarded_active_bids(p_auction_header_id IN NUMBER,
425 p_user_id IN NUMBER,
426 p_note_to_rejected IN VARCHAR2,
427 p_neg_has_lines IN VARCHAR2) IS
428
429 -- select all active bid items for for which award decision is not made.
430 -- this includes auction lines with no award decision made
431 -- and auction lines with award decision made but have bid items unawarded
432 CURSOR active_bid_lines(p_auction_header_id NUMBER) IS
433 select al.line_number,
434 al.line_origination_code,
435 nvl(al.award_status,'NO'),
436 bl.bid_number,
437 bl.order_number,
438 bl.award_quantity
439 from pon_auction_item_prices_all al,
440 pon_bid_item_prices bl,
441 pon_bid_headers bh
442 where al.auction_header_id = p_auction_header_id
443 and bl.auction_header_id = al.auction_header_id
444 and bl.line_number = al.line_number
445 and bh.bid_number = bl.bid_number
446 and nvl(bh.bid_status,'NONE') = 'ACTIVE'
447 -- we get lines with award decision made but have some bids unawarded
448 --and nvl(al.award_status,'NO') = 'NO'
449 and nvl(bl.award_status,'NO') = 'NO';
450
451 -- FPK: CPA select all active bids for which award decision is not made.
452 CURSOR active_bid_headers(p_auction_header_id NUMBER) IS
453 select nvl(ah.award_status,'NO'),
454 bh.bid_number
455 from pon_auction_headers_all ah,
456 pon_bid_headers bh
457 where bh.auction_header_id = p_auction_header_id
458 and bh.auction_header_id = ah.auction_header_id
459 and nvl(bh.bid_status,'NONE') = 'ACTIVE'
460 and nvl(bh.award_status,'NO') = 'NO';
461
462
463 x_line_number pon_auction_item_prices_all.line_number%type;
464 x_old_line_number pon_auction_item_prices_all.line_number%type;
465 x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
466 x_bid_number pon_bid_headers.bid_number%type;
467 x_order_number pon_bid_headers.order_number%type;
468 x_award_quantity pon_bid_item_prices.award_quantity%type;
469 x_line_award_status pon_auction_item_prices_all.award_status%type;
470 x_stored_note_to_rejected pon_acceptances.reason%type;
471 x_error_code VARCHAR2(20);
472
473 x_bid_number_list integerList;
474 x_bid_number_found BOOLEAN;
475 x_count NUMBER;
476
477 -- FPK: CPA
478 x_header_award_status PON_BID_HEADERS.AWARD_STATUS%TYPE;
479
480 --Bug : 14134092
481 l_is_line_type_enabled VARCHAR2(1);
482
483 BEGIN
484
485 --Bug : 14134092 : If line type structure changes are enabled, then fund allocation will be done for all lines.
486 l_is_line_type_enabled := PON_CLM_UTIL_PKG.get_line_structure_enabled(p_auction_header_id => p_auction_header_id);
487
488 IF p_neg_has_lines = 'Y' THEN -- FPK: CPA
489 open active_bid_lines(p_auction_header_id); --Bug : 14134092
490 loop
491 fetch active_bid_lines
492 into x_line_number,
493 x_line_origination_code,
494 x_line_award_status,
495 x_bid_number,
496 x_order_number,
497 x_award_quantity;
498 exit when active_bid_lines%notfound;
499 -- "AND x_line_award_status = 'NO'" condition added
500 -- to ensure ONLY lines with NO award decision made are put back into the pool.
501 --Bug : 14134092 : If an unsol line linked to autocreated sol line is awarded, donot take req back to pool.
502 if (x_line_origination_code = 'REQUISITION' AND x_line_award_status = 'NO'
503 AND PON_UNSOL_UTIL_PKG.CAN_PUT_REQ_IN_POOL(p_auction_header_id, x_line_number, l_is_line_type_enabled) = 'Y') then
504 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF_BY_LINE(p_auction_header_id, x_line_number, x_error_code);
505 end if;
506 /*
507 -- reject the bid line (note that reject shares the same
508 -- procedure as award)
509 award_bid (x_order_number, -- p_order_number
510 p_user_id, -- p_auctioneer_id
511 p_auction_header_id, -- p_auction_header_id
512 x_bid_number, -- p_bid_number
513 x_line_number, -- p_auction_line_number
514 x_award_quantity, -- p_award_quantity
515 'REJECTED', -- p_award_status
516 p_note_to_rejected, -- p_reason
517 sysdate, -- p_award_date,
518 null, -- p_originPartyId: obsolete
519 null, -- p_originUserId: obsolete
520 null, -- p_currency: obsolete
521 null -- p_billType: obsolete
522 );
523 */
524 -- added for the new award flow in FPJ
525 -- Need to take care of notes in pon_acceptances.
526 update_single_bid_item_prices
527 (
528 x_bid_number,
529 x_line_number,
530 'REJECTED',
531 x_award_quantity,
532 sysdate,
533 p_user_id
534 );
535
536 -- determine if the bid number of the bid line has been added to the list
537 x_bid_number_found := FALSE;
538
539 FOR i IN 1 .. x_bid_number_list.COUNT LOOP
540 IF x_bid_number = x_bid_number_list(i) THEN
541 x_bid_number_found := TRUE;
542 EXIT;
543 END IF;
544 END LOOP;
545
546 -- if not, add it to the list
547 IF NOT(x_bid_number_found) THEN
548 x_bid_number_list(x_bid_number_list.COUNT + 1) := x_bid_number;
549 END IF;
550
551 -- complete or award item disposition as necessary
552 -- there could be multiple bids for the same line
553 -- only need to call it once for each line
554 IF (x_old_line_number is null OR
555 x_old_line_number <> x_line_number) THEN
556 x_old_line_number := x_line_number;
557
558 IF (x_line_award_status = 'NO') THEN
559 -- Update acceptances for the lines with no award decision made
560 update_unawarded_acceptances(
561 p_auction_header_id, -- auction header id
562 x_line_number, -- line number
563 p_note_to_rejected, --note to rejected suppliers
564 SYSDATE, -- award_date
565 p_user_id);
566 ELSE
567 -- Update acceptances for the lines with award decision already made
568 x_stored_note_to_rejected := null;
569 x_count := 0;
570 SELECT count(*) INTO x_count FROM pon_acceptances
571 WHERE auction_header_id = p_auction_header_id
572 AND line_number = x_line_number
573 AND ACCEPTANCE_TYPE = 'REJECTED';
574 --
575 IF x_count > 0 THEN
576 -- rejection note exists and carried over for rejected suppliers
577 SELECT distinct REASON INTO x_stored_note_to_rejected
578 FROM pon_acceptances
579 WHERE auction_header_id = p_auction_header_id
580 AND line_number = x_line_number
581 AND ACCEPTANCE_TYPE = 'REJECTED';
582 END IF;
583 --
584 update_unawarded_acceptances(
585 p_auction_header_id, -- auction header id
586 x_line_number, -- line number
587 x_stored_note_to_rejected, --note to rejected suppliers
588 SYSDATE, -- award_date
589 p_user_id);
590 END IF;
591 award_item_disposition (p_auction_header_id, x_line_number, 0);
592 END IF;
593
594 END LOOP;
595 CLOSE active_bid_lines;
596
597 -- update the award status for the bids whose lines were rejected
598 FOR i IN 1 .. x_bid_number_list.COUNT LOOP
599 update_single_bid_header(x_bid_number_list(i), p_user_id);
600 END LOOP;
601
602 -- update the award status for the auction that was bidded on
603 -- if any bid line was rejected
604 IF x_bid_number_list.COUNT > 0 THEN
605 update_auction_headers(p_auction_header_id, g_AWARD_LINE, SYSDATE, p_user_id, 'Y');
606 END IF;
607 ELSE -- negotiation does not have lines
608 OPEN active_bid_headers(p_auction_header_id);
609 LOOP
610 FETCH active_bid_headers
611 INTO x_header_award_status, x_bid_number;
612 EXIT WHEN active_bid_headers%NOTFOUND;
613
614 -- determine if the bid number has been added to the list
615 x_bid_number_found := FALSE;
616
617 FOR i IN 1 .. x_bid_number_list.COUNT LOOP
618 IF x_bid_number = x_bid_number_list(i) THEN
619 x_bid_number_found := TRUE;
620 EXIT;
621 END IF;
622 END LOOP;
623
624 -- if not, add it to the list
625 IF NOT(x_bid_number_found) THEN
626 x_bid_number_list(x_bid_number_list.COUNT + 1) := x_bid_number;
627 END IF;
628 END LOOP;
629 CLOSE active_bid_headers;
630
631 -- update the award status for the active bids in this auction where no
632 -- award decision made (all bids will be rejected)
633 FORALL k IN 1..x_bid_number_list.COUNT
634
635 UPDATE PON_BID_HEADERS
636 SET AWARD_STATUS = 'REJECTED',
637 AWARD_DATE = SYSDATE, /* new column created as part of CPA project.
638 It will be updated only when negotiation does
639 not have lines. */
640 last_update_date = SYSDATE,
641 last_updated_by = p_user_id
642 WHERE bid_number = x_bid_number_list(k);
643
644 -- update the award status for the auction that was bidded on
645 -- and no award decision made
646 IF x_bid_number_list.COUNT > 0 THEN
647 update_auction_headers(p_auction_header_id, g_AWARD_QUOTE, SYSDATE,
648 p_user_id, 'N');
649 END IF;
650 END IF; -- IF neg. has lines
651 END reject_unawarded_active_bids;
652
653 ----------------------------------------------------------------
654 -- Complete award process for a negotiation
655 -- mirrors NegotiationDoc.completeAward which is gone after
656 -- migration to OA
657 -- also contains some logic from reviewComplete.jsp
658 ----------------------------------------------------------------
659
660 PROCEDURE complete_award (p_auction_header_id_encrypted IN VARCHAR2,
661 p_auction_header_id IN NUMBER,
662 p_note_to_rejected IN VARCHAR2,
663 p_shared_award_decision IN VARCHAR2,
664 p_user_id IN NUMBER,
665 p_create_po_flag IN VARCHAR2,
666 p_source_reqs_flag IN VARCHAR2,
667 p_no_bids_flag IN VARCHAR2,
668 p_has_backing_reqs_flag IN VARCHAR2,
669 p_outcome_status IN VARCHAR2,
670 p_has_scoring_teams_flag IN VARCHAR2,
671 p_scoring_lock_tpc_id IN NUMBER,
672 p_finally_complete IN VARCHAR2 DEFAULT 'N') IS
673
674 x_line_number pon_auction_item_prices_all.line_number%type;
675 x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
676 x_error_code VARCHAR2(20);
677 x_awarded_quantity NUMBER;
678 l_neg_has_lines PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE; -- FPK: CPA
679
680 --Business Events Changes
681 x_return_status VARCHAR2(20);
682 x_msg_count NUMBER;
683 x_msg_data VARCHAR2(2000);
684
685 -- select items without any bids that had backing requisitions
686 /*Bug : 14134092 : If there are no bids for autocreated lines, check if any unsol lines are linked
687 and line type is enabled. */
688 CURSOR items_with_reqs_no_bids(p_auction_header_id NUMBER, p_is_line_type_enabled VARCHAR2) IS
689 SELECT line_number, line_origination_code
690 FROM PON_AUCTION_ITEM_PRICES_ALL paip
691 WHERE auction_header_id = p_auction_header_id
692 AND nvl(number_of_bids,0) = 0
693 AND line_origination_code = 'REQUISITION'
694 AND NOT EXISTS
695 (SELECT 1
696 FROM pon_auction_headers_all pah,
697 pon_bid_item_prices pbip,
698 pon_bid_item_references pbir
699 WHERE pah.auction_header_id = p_auction_header_id
700 AND Nvl(pah.ALLOW_UNSOL_OFFER_LINES,'N') = 'Y'
701 AND pbip.auction_header_id = pah.auction_header_id
702 AND pbip.auction_line_number = -1
703 AND Nvl(pbip.award_status, 'REJECTED') = 'AWARDED'
704 AND pbir.auction_header_id = pbip.auction_header_id
705 AND pbir.line_number = pbip.line_number
706 AND pbir.auction_line_number = paip.line_number
707 AND pbir.link_done_by = 'BOTH'
708 AND p_is_line_type_enabled = 'Y');
709
710
711 CURSOR auction_items_all (p_auction_header_id NUMBER) IS
712 SELECT line_number, nvl(awarded_quantity, 0)
713 FROM PON_AUCTION_ITEM_PRICES_ALL
714 WHERE auction_header_id = p_auction_header_id;
715
716 --Line Type and Structute Changes Project
717 CURSOR c_has_linked_pr_refs IS
718 SELECT Count(*) FROM pon_backing_requisitions
719 WHERE auction_header_id = p_auction_header_id;
720
721 l_linked_pr_refs_count NUMBER;
722
723 -- Unsolicited Lines Project
724 l_allow_unsol_lines VARCHAR2(1);
725 l_neg_has_unsol_lines VARCHAR2(1);
726
727 --Bug : 14134092
728 l_is_line_type_enabled VARCHAR2(1);
729
730 --Staggered Awards project
731 l_allow_stag_awards VARCHAR2(1);
732 l_has_unawarded_lines VARCHAR2(1);
733 l_unawarded_line_count NUMBER;
734 l_line_count NUMBER;
735
736
737 BEGIN
738
739 l_neg_has_lines := PON_AUCTION_PKG.neg_has_lines(p_auction_header_id); -- FPK: CPA
740
741 select nvl(allow_staggered_awards, 'N') into l_allow_stag_awards from pon_auction_headers_all where auction_header_id = p_auction_header_id;
742
743 select count(DISTINCT line_number) into l_unawarded_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id and nvl(award_status,'NO') = 'NO';
744
745 select count(DISTINCT line_number) into l_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id;
746
747 if (l_unawarded_line_count > 0 AND l_unawarded_line_count <> l_line_count AND p_finally_complete <> 'Y') then
748 l_has_unawarded_lines := 'Y';
749 else
750 l_has_unawarded_lines := 'N';
751 end if;
752 ----
753 -- Unsolicited Lines Project
754 l_allow_unsol_lines := PON_UNSOL_UTIL_PKG.ARE_UNSOL_LINES_ALLOWED(p_auction_header_id);
755 IF l_allow_unsol_lines = 'Y' THEN
756 l_neg_has_unsol_lines := PON_UNSOL_UTIL_PKG.HAS_UNSOL_LINES(p_auction_header_id);
757 ELSE
758 l_neg_has_unsol_lines := 'N';
759 END IF;
760 -- End Unsolicited Lines Project
761
762 -- Donot put back the req is staggered awarding is enabled and neg is partially awarded
763 if (p_create_po_flag <> 'Y' and p_has_backing_reqs_flag = 'Y' and (l_allow_stag_awards <> 'Y' or l_has_unawarded_lines = 'N')) then
764 -- put requisitions back in pool if auction
765 -- has backing req and no outcome creation
766 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF(p_auction_header_id, x_error_code);
767 end if;
768
769 --Line Type and Structute Changes Project
770 OPEN c_has_linked_pr_refs;
771 FETCH c_has_linked_pr_refs INTO l_linked_pr_refs_count;
772 CLOSE c_has_linked_pr_refs;
773
774 -- put requisitions back in pool if auction
775 -- has backing req and no outcome creation
776 -- Donot put back the req is staggered awarding is enabled and sol is partially awarded
777 IF (p_create_po_flag <> 'Y' and l_linked_pr_refs_count > 0 and (l_allow_stag_awards <> 'Y' or l_has_unawarded_lines = 'N')) then
778 po_negotiations_sv1.update_sol_ref_delete_all(p_auction_header_id => p_auction_header_id,
779 p_delete_pbr_yn => 'N',
780 x_return_status => x_return_status,
781 x_error_msg => x_msg_data,
782 x_error_code => x_error_code);
783 END IF;
784
785 /* FPK: CPA
786 If negotiation has lines: for all items that have active bids but are not awarded,
787 reject the active bids and cancel backing requisitions, if any
788 If negotiation does not have lines: reject all active bids that were not awarded n
789 nor rejected (no award decision was made)
790 */
791 /* Unsolicited lines project : If unsol lines are allowed and any offer has unsol lines, call a different procedure
792 * to reject unawarded neg and unsol lines.
793 */
794 --Donot reject un awarded lines if staggered awards are enabled
795 if (l_allow_stag_awards = 'N' OR (l_allow_stag_awards = 'Y' AND l_has_unawarded_lines = 'N')) then
796 IF l_neg_has_unsol_lines = 'Y' THEN
797 PON_UNSOL_AWARD_PKG.reject_unawarded_unsol_bids(p_auction_header_id, p_user_id, p_note_to_rejected);
798 ELSE
799 reject_unawarded_active_bids(p_auction_header_id, p_user_id, p_note_to_rejected, l_neg_has_lines);
800 END IF;
801 end if;
802
803 IF l_neg_has_unsol_lines = 'Y' THEN
804 PON_UNSOL_UTIL_PKG.DUPLICATE_UNSOL_AWARDED_LINES(p_auction_header_id);
805 END IF;
806
807 --Bug : 14134092
808 l_is_line_type_enabled := PON_CLM_UTIL_PKG.get_line_structure_enabled(p_auction_header_id => p_auction_header_id);
809
810 /* Line structure changes project:
811 When completing award, copy all the PR references of lines awarded to pon_bid_backing_requisitions table.
812 We can award unsolicited lines and relate to solicited lines. So, we should copy all PR references to pon_bid_backing_requisitions*/
813
814 --CLM-LnSc-st
815 --copy only IF(IS CLm PO, outcome document is award and if encumberence is 'on' and if Outcome Document is created.
816
817 --Bug : 14134092
818 IF(l_is_line_type_enabled = 'Y' AND p_create_po_flag = 'Y') THEN
819
820 create_pon_bid_back_req(p_auction_header_id);
821 END IF;
822 --CLM-LnSc-end
823
824 -- Requisitions should be put back into the pool if
825 -- an item has received no bids and the buyer
826 -- is completing the auction
827
828 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
829 --Bug : 14134092
830 open items_with_reqs_no_bids(p_auction_header_id, l_is_line_type_enabled);
831 loop
832
833 /*
834 rrkulkar-large-auction-support changes
835 modified the cursor to simply loop over the exact set of lines
836 rather than looping over all the lines, and filtering the lines
837 in the cursor-loop by adding an if condition
838
839 */
840 fetch items_with_reqs_no_bids
841 into x_line_number,
842 x_line_origination_code;
843 exit when items_with_reqs_no_bids%notfound;
844
845 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF_BY_LINE(p_auction_header_id, x_line_number, x_error_code);
846
847 end loop;
848 close items_with_reqs_no_bids;
849 --
850
851 /*
852 rrkulkar-large-auction-support changes
853 instead of looping over all the lines, we can update all
854 lines in a single query
855
856 */
857
858 /*
859 rrkulkar-large-auction-support : commented out the call to complete_item_disposition
860 need to add batching here
861 */
862 --complete award should update only for awarded lines as COMPLETED if staggered awards are enabled
863 if l_allow_stag_awards = 'Y' and l_has_unawarded_lines = 'Y' then
864 update pon_auction_item_prices_all
865 set AWARD_STATUS = 'COMPLETED',
866 LAST_UPDATE_DATE = sysdate,
867 AWARDED_QUANTITY = nvl(awarded_quantity,0)
868 where auction_header_id = p_auction_header_id
869 and AWARD_STATUS = 'AWARDED';
870 else
871 update pon_auction_item_prices_all
872 set AWARD_STATUS = 'COMPLETED',
873 LAST_UPDATE_DATE = sysdate,
874 AWARDED_QUANTITY = nvl(awarded_quantity,0)
875 where auction_header_id = p_auction_header_id;
876 end if;
877
878
879 END IF; -- IF l_neg_has_lines = 'Y'
880 --
881 -- if team scoring is enabled, call routine to lock team scoring
882 IF (p_has_scoring_teams_flag = 'Y') THEN
883 -- check to see if the auction was already locked for scoring
884 -- if this were true, the p_scoring_lock_tpc_id will be -1
885 -- as determined in the CompleteAwardAM from where this API is called.
886 IF (p_scoring_lock_tpc_id = -1) THEN
887 NULL;
888 ELSE
889 -- call pvt API to lock scoring
890 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
891 fnd_log.string(log_level => fnd_log.level_unexpected
892 ,module => 'pon_award_pkg.complete_award'
893 ,message => 'before calling private API to lock team scoring');
894 END IF;
895
896 PON_TEAM_SCORING_UTIL_PVT.lock_scoring(p_api_version => 1
897 ,p_auction_header_id => p_auction_header_id
898 ,p_tpc_id => p_scoring_lock_tpc_id
899 ,x_return_status => x_return_status
900 ,x_msg_data => x_msg_data
901 ,x_msg_count => x_msg_count);
902
903 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
904 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
905 fnd_log.string(log_level => fnd_log.level_unexpected
906 ,module => 'pon_award_pkg.complete_award'
907 ,message => 'Error while locking team scoring');
908 END IF;
909 END IF;
910 END IF;
911 END IF;
912
913
914 IF l_allow_stag_awards = 'Y' and l_has_unawarded_lines = 'Y' then
915 complete_partial_auction(p_auction_header_id); --retain partial award_status for further awarding
916 ELSE
917 complete_auction (p_auction_header_id);
918 END IF;
919
920 award_notification (p_auction_header_id_encrypted,
921 p_auction_header_id,
922 p_shared_award_decision);
923
924 -- post processing: originally in reviewComplete.jsp
925 -- For no bids we would come directly to this page.
926 -- Thus setShareAwardDecision will be N. We do not want that.
927 -- As otherwise the button will appear in bidViewAuction
928 -- update database w/ outcome status, source req result,
929 -- and award completion date
930 update pon_auction_headers_all
931 set outcome_status = p_outcome_status,
932 award_complete_date = sysdate,
933 source_reqs_flag = p_source_reqs_flag,
934 share_award_decision = decode(p_no_bids_flag, 'Y', 'I', share_award_decision),
935 last_update_date = sysdate
936 where auction_header_id = p_auction_header_id;
937
938 -- Raise Business Event
939 PON_BIZ_EVENTS_PVT.RAISE_NEG_AWRD_COMPLETE_EVENT(
940 p_api_version => 1.0 ,
941 p_init_msg_list => FND_API.G_FALSE,
942 p_commit => FND_API.G_FALSE,
943 p_auction_header_id => p_auction_header_id,
944 p_create_po_flag => p_create_po_flag,
945 x_return_status => x_return_status,
946 x_msg_count => x_msg_count,
947 x_msg_data => x_msg_data);
948
949 END complete_award;
950
951 ----------------------------------------------------------------
952 -- complete auction
953 -- mirrors NegotiationDoc.completeAuction which is gone after
954 -- migration to OA
955 ----------------------------------------------------------------
956
957 PROCEDURE complete_auction (p_auction_header_id IN NUMBER ) IS
958
959 x_event_id pon_auction_headers_all.event_id%type;
960 x_count NUMBER;
961
962 BEGIN
963 -- complete auction header disposition
964 -- clear out the request_id for super-large auctions
965 update pon_auction_headers_all
966 set AWARD_STATUS = 'COMPLETED',
967 AUCTION_STATUS = 'AUCTION_CLOSED',
968 REQUEST_ID = NULL,
969 LAST_UPDATE_DATE = sysdate
970 where auction_header_id = p_auction_header_id;
971
972 -- complete work flow
973 pon_auction_pkg.COMPLETE_AUCTION(p_auction_header_id);
974
975 END complete_auction;
976
977 ----------------------------------------------------------------
978 -- send award notifications
979 -- mirrors NegotiationDoc.awardNotification which is gone after
980 -- migration to OA
981 ----------------------------------------------------------------
982
983 PROCEDURE award_notification (p_auction_header_id_encrypted IN VARCHAR2,
984 p_auction_header_id IN NUMBER,
985 p_shared_award_decision IN VARCHAR2) IS
986
987 x_bid_number pon_bid_headers.bid_number%type;
988 x_bid_tp_contact_name pon_bid_headers.trading_partner_contact_name%type;
989 x_auction_tp_name pon_auction_headers_all.trading_partner_name%type;
990 x_auction_title pon_auction_headers_all.auction_title%type;
991 x_emd_admin_name pon_neg_team_members.user_name%type;
992 x_doc_number_dsp pon_auction_headers_all.document_number%type;
993 CURSOR all_bidders(p_auction_header_id NUMBER) IS
994 select b.bid_number,
995 b.trading_partner_contact_name contact,
996 a.trading_partner_name auctioneer,
997 a.auction_title,
998 a.allow_staggered_awards, --added for staggered award project
999 b.award_status -- added for staggered award project
1000 from pon_bid_headers b,
1001 pon_auction_headers_all a
1002 where b.auction_header_id = p_auction_header_id
1003 and not nvl(b.bid_status,'NONE') in ('ARCHIVED','DISQUALIFIED','DRAFT')
1004 and a.auction_header_id = b.auction_header_id;
1005
1006 -- choli update for emd
1007 CURSOR all_emdAdmins(p_auction_header_id NUMBER) IS
1008 select u.user_name,
1009 a.trading_partner_name auctioneer,
1010 a.auction_title,
1011 a.document_number
1012 from pon_neg_team_members b, pon_auction_headers_all a, fnd_user u
1013 where b.menu_name = 'EMD_ADMIN'
1014 and b.approver_flag = 'Y'
1015 and a.auction_header_id = b.auction_header_id
1016 and u.user_id = b.user_id
1017 and a.auction_header_id = p_auction_header_id;
1018
1019 --Staggered award project
1020 x_bid_award_status pon_bid_headers.award_status%type;
1021 x_allow_staggered_awards pon_auction_headers_all.allow_staggered_awards%TYPE;
1022 x_completed_lines NUMBER;
1023
1024 BEGIN
1025
1026 if (p_shared_award_decision = 'Y') then
1027
1028 -- send a notification to each supplier
1029 open all_bidders(p_auction_header_id);
1030 loop
1031 fetch all_bidders
1032 into x_bid_number,
1033 x_bid_tp_contact_name,
1034 x_auction_tp_name,
1035 x_auction_title,
1036 x_allow_staggered_awards,
1037 x_bid_award_status;
1038 exit when all_bidders%notfound;
1039
1040 SELECT Count(*)
1041 INTO x_completed_lines
1042 FROM pon_bid_item_prices
1043 WHERE auction_header_id = p_auction_header_id
1044 AND bid_number = x_bid_number
1045 AND auction_line_number IN (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id AND Nvl(award_status, 'NO') = 'COMPLETED');
1046
1047 IF x_completed_lines > 0 THEN
1048
1049 pon_auction_pkg.AWARD_BID(x_bid_number,
1050 p_auction_header_id,
1051 x_bid_tp_contact_name,
1052 x_auction_tp_name,
1053 x_auction_title,
1054 p_auction_header_id_encrypted);
1055 END IF;
1056 end loop;
1057 close all_bidders;
1058
1059 -- update pon_auction_headers_all
1060 update pon_auction_headers_all
1061 set SHARE_AWARD_DECISION = p_shared_award_decision
1062 where auction_header_id = p_auction_header_id;
1063
1064 end if;
1065
1066 -- choli update for emd
1067 open all_emdAdmins(p_auction_header_id);
1068 loop
1069 fetch all_emdAdmins
1070 into x_emd_admin_name,
1071 x_auction_tp_name,
1072 x_auction_title,
1073 x_doc_number_dsp;
1074 exit when all_emdAdmins%notfound;
1075
1076 NotifyEmdAdmin(p_auction_header_id,
1077 x_emd_admin_name,
1078 x_auction_tp_name,
1079 x_auction_title,
1080 p_auction_header_id_encrypted,
1081 x_doc_number_dsp);
1082 end loop;
1083 close all_emdAdmins;
1084
1085 END award_notification;
1086
1087 ----------------------------------------------------------------
1088 -- complete item disposition
1089 -- mirrors NegotiationItem.completeDisposition which is gone after
1090 -- migration to OA
1091 ----------------------------------------------------------------
1092
1093 PROCEDURE complete_item_disposition (p_auction_header_id IN NUMBER,
1094 p_line_number IN NUMBER,
1095 p_award_quantity IN NUMBER) IS
1096
1097 BEGIN
1098 update pon_auction_item_prices_all
1099 set AWARD_STATUS = 'COMPLETED',
1100 LAST_UPDATE_DATE = sysdate,
1101 AWARDED_QUANTITY = p_award_quantity
1102 where auction_header_id = p_auction_header_id
1103 and line_number = p_line_number;
1104
1105 END complete_item_disposition;
1106
1107
1108 ----------------------------------------------------------------
1109 -- award item disposition
1110 -- mirrors complete_item_disposition
1111 -- except that pon_auction_item_prices_all.award_status column is set to AWARDED (instead of COMPLETED)
1112 ----------------------------------------------------------------
1113
1114 PROCEDURE award_item_disposition (p_auction_header_id IN NUMBER,
1115 p_line_number IN NUMBER,
1116 p_award_quantity IN NUMBER) IS
1117
1118 BEGIN
1119 update pon_auction_item_prices_all
1120 set AWARD_STATUS = 'AWARDED',
1121 LAST_UPDATE_DATE = sysdate,
1122 AWARDED_QUANTITY = p_award_quantity
1123 where auction_header_id = p_auction_header_id
1124 and line_number = p_line_number;
1125
1126 END award_item_disposition;
1127
1128
1129 /* ELINs project : This procedure will award exhibit lines associated to particular line
1130 Call this procedure in AWARD_BY_LINE mode.
1131 */
1132 PROCEDURE award_exhibit_lines(p_auction_header_id IN Number,
1133 p_line_num IN Number,
1134 p_award_lines IN t_award_lines,
1135 p_auctioneer_id IN Number)
1136
1137 IS
1138 Cursor exhibitsCursor Is
1139 select paip.line_number
1140 from pon_auction_exhibit_details paed,
1141 pon_auction_item_prices_all paip
1142 where paed.auction_header_id = p_auction_header_id
1143 and paed.associated_to_line = p_line_num
1144 AND paip.auction_header_id = paed.auction_header_id
1145 AND paip.exhibit_number IS NOT NULL
1146 AND paip.exhibit_number = paed.exhibit_number ;
1147
1148 l_award_price pon_bid_item_prices.award_price%TYPE;
1149 l_award_quantity pon_bid_item_prices.award_quantity%TYPE;
1150 l_size NUMBER;
1151 l_index NUMBER;
1152 l_auction_award_qty NUMBER;
1153
1154 BEGIN
1155 IF (g_debug_mode = 'Y') THEN
1156 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1157 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.award_exhibit_lines', 'Entering procedure with p_auction_header_id : ' || p_auction_header_id || ' p_line_num : ' || p_line_num || ' ,p_auctioneer_id : '|| p_auctioneer_id);
1158 END IF;
1159 END IF;
1160
1161 FOR elin IN exhibitsCursor LOOP
1162 --This will mark current exhibit line with appropriate award status
1163 award_child_line(p_auction_header_id, elin.line_number, p_award_lines, p_auctioneer_id);
1164
1165 l_auction_award_qty := 0;
1166 l_size := p_award_lines.count;
1167
1168 FOR l_index IN 1..l_size LOOP
1169 l_award_price := NULL;
1170 l_award_quantity := NULL;
1171
1172 /* Bug : 16721126 : Update award_price, award_quantity only when offer exists on a line */
1173 IF check_bid_line_exist(p_auction_header_id, p_award_lines(l_index).bid_Number, elin.line_number) = 1 THEN
1174
1175 SELECT decode(pbip.award_status, 'REJECTED', null,
1176 decode (paip.order_type_lookup_code,
1177 'FIXED PRICE', 1,
1178 'AMOUNT', 1,
1179 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', pbip.quantity, 1), pbip.quantity ))
1180 INTO l_award_quantity
1181 FROM pon_bid_item_prices pbip,
1182 pon_auction_item_prices_all paip
1183 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1184 AND pbip.auction_line_number = elin.line_number
1185 AND paip.auction_header_id = pbip.auction_header_id
1186 AND paip.line_number = pbip.line_number;
1187
1188 l_auction_award_qty := l_auction_award_qty + Nvl(l_award_quantity,0);
1189
1190 IF (l_award_quantity <> 0 ) THEN
1191
1192 SELECT decode(pbip.award_status, 'REJECTED', null,
1193 pbip.per_unit_price_component + pbip.fixed_amount_component /l_award_quantity)
1194 INTO l_award_price
1195 FROM pon_bid_item_prices pbip,
1196 pon_auction_item_prices_all paip
1197 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1198 AND pbip.auction_line_number = elin.line_number
1199 AND paip.auction_header_id = pbip.auction_header_id
1200 AND paip.line_number = pbip.line_number;
1201
1202 END IF;
1203
1204 UPDATE pon_bid_item_prices pbip
1205 SET award_price = l_award_price,
1206 award_quantity = l_award_quantity
1207 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1208 AND pbip.auction_line_number = elin.line_number;
1209
1210 END IF; -- End check_bid_line_exist
1211
1212 END LOOP; -- End p_award_lines loop
1213
1214 UPDATE pon_auction_item_prices_all paip
1215 SET paip.awarded_quantity = decode (paip.order_type_lookup_code,
1216 'FIXED PRICE', 1,
1217 'AMOUNT', 1,
1218 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', l_auction_award_qty, 1),l_auction_award_qty )
1219 WHERE auction_header_id = p_auction_header_id
1220 AND line_number = elin.line_number;
1221
1222 END LOOP; --End exhibitsCursor loop
1223
1224 END award_exhibit_lines;
1225
1226
1227 /* ELINs project : This procedure will award parent exhibit line. */
1228 PROCEDURE award_elin_parent_line(p_auction_header_id IN Number,
1229 p_line_num IN Number,
1230 p_award_lines IN t_award_lines,
1231 p_auctioneer_id IN Number)
1232 IS
1233 l_parent_line pon_auction_item_prices_all.line_number%TYPE;
1234 is_parent_slin VARCHAR2(1);
1235 l_award_price pon_bid_item_prices.award_price%TYPE;
1236 l_award_quantity pon_bid_item_prices.award_quantity%TYPE;
1237 l_size NUMBER;
1238 l_index NUMBER;
1239 l_auction_award_qty NUMBER;
1240 l_award_status VARCHAR2(10);
1241 BEGIN
1242 -- This sql gets parent line for an elin. Also whether parent is clin/slin.
1243 SELECT paed.associated_to_line, Decode(Nvl(paip_parent.group_line_id, -1), -1, 'N', 'Y')
1244 INTO l_parent_line, is_parent_slin
1245 FROM pon_auction_item_prices_all paip_elin,
1246 pon_auction_exhibit_details paed,
1247 pon_auction_item_prices_all paip_parent
1248 WHERE paip_elin.auction_header_id = p_auction_header_id
1249 AND paip_elin.line_number = p_line_num
1250 AND paed.auction_header_id = paip_elin.auction_header_id
1251 AND paed.exhibit_number = paip_elin.exhibit_number
1252 AND paip_parent.auction_header_id = paed.auction_header_id
1253 AND paip_parent.line_number = paed.associated_to_line;
1254
1255 l_auction_award_qty := 0;
1256 l_size := p_award_lines.count;
1257
1258 /* Update elin parent line with appropriate award status.
1259 * Update only when award_status is 'REJECTED'.
1260 */
1261 FOR l_index IN 1..l_size LOOP
1262
1263 /* Bug : 16721126 : Update parent only when offer exisits on parent line. */
1264 IF check_bid_line_exist(p_auction_header_id, p_award_lines(l_index).bid_number, l_parent_line) = 1 THEN
1265 UPDATE PON_BID_ITEM_PRICES
1266 set award_status = p_award_lines(l_index).award_status,
1267 award_date = p_award_lines(l_index).award_date,
1268 last_update_date = p_award_lines(l_index).award_date,
1269 last_updated_by = p_auctioneer_id
1270 where bid_number = p_award_lines(l_index).bid_number
1271 and line_number = l_parent_line
1272 AND Nvl(award_status, 'REJECTED') = 'REJECTED';
1273
1274 if ( (l_award_status is NULL) AND
1275 ((p_award_lines(l_index).award_status = 'AWARDED') OR
1276 -- (p_award_lines(l_index).award_status = 'REJECTED') OR -- bug#9741473
1277 (p_award_lines(l_index).award_status = 'PARTIAL')
1278 )) THEN
1279 l_award_status := 'AWARDED';
1280 end if;
1281 END IF; --end check_bid_line_exist() = 1
1282
1283 END LOOP;
1284 -- update only when award_status = 'REJECTED'
1285 UPDATE pon_auction_item_prices_all
1286 set award_status = l_award_status,
1287 last_update_date = sysdate,
1288 last_updated_by = p_auctioneer_id
1289 where auction_header_id = p_auction_header_id
1290 and line_number = l_parent_line
1291 AND Nvl(award_status, 'REJECTED') = 'REJECTED';
1292
1293 FOR l_index IN 1..l_size LOOP
1294 l_award_price := NULL;
1295 l_award_quantity := NULL;
1296
1297 /* Bug : 16721126 : Update parent only when offer exisits on parent line. */
1298 IF check_bid_line_exist(p_auction_header_id, p_award_lines(l_index).bid_number, l_parent_line) = 1 THEN
1299 SELECT decode(pbip.award_status, 'REJECTED', null,
1300 decode (paip.order_type_lookup_code,
1301 'FIXED PRICE', 1,
1302 'AMOUNT', 1,
1303 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', pbip.quantity, 1), pbip.quantity ))
1304 INTO l_award_quantity
1305 FROM pon_bid_item_prices pbip,
1306 pon_auction_item_prices_all paip
1307 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1308 AND pbip.auction_line_number = l_parent_line
1309 AND paip.auction_header_id = pbip.auction_header_id
1310 AND paip.line_number = pbip.line_number;
1311
1312 l_auction_award_qty := l_auction_award_qty + Nvl(l_award_quantity,0);
1313
1314 IF (l_award_quantity <> 0 ) THEN
1315
1316 SELECT decode(pbip.award_status, 'REJECTED', null,
1317 pbip.per_unit_price_component + pbip.fixed_amount_component /l_award_quantity)
1318 INTO l_award_price
1319 FROM pon_bid_item_prices pbip,
1320 pon_auction_item_prices_all paip
1321 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1322 AND pbip.auction_line_number = l_parent_line
1323 AND paip.auction_header_id = pbip.auction_header_id
1324 AND paip.line_number = pbip.line_number;
1325
1326 END IF;
1327
1328 UPDATE pon_bid_item_prices pbip
1329 SET award_price = l_award_price,
1330 award_quantity = l_award_quantity
1331 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1332 AND pbip.auction_line_number = l_parent_line;
1333
1334 END IF; --end check_bid_line_exist(p_auction_header_id, p_award_lines(l_index).bid_number, l_parent_line) = 1
1335
1336
1337 END LOOP; -- End p_award_lines loop
1338
1339 --If exhibit parent line is SLIN, mark parent info clin.
1340 IF(is_parent_slin = 'Y') THEN
1341 award_parent_line(p_auction_header_id, l_parent_line, p_award_lines, p_auctioneer_id);
1342 ELSE
1343 --If parent line is clin, update info slins.
1344 award_info_slin_lines(p_auction_header_id, l_parent_line, p_award_lines, p_auctioneer_id);
1345 END IF;
1346
1347 UPDATE pon_auction_item_prices_all paip
1348 SET paip.awarded_quantity = decode (paip.order_type_lookup_code,
1349 'FIXED PRICE', 1,
1350 'AMOUNT', 1,
1351 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', l_auction_award_qty, 1),l_auction_award_qty )
1352 WHERE auction_header_id = p_auction_header_id
1353 AND line_number = l_parent_line;
1354
1355
1356 END award_elin_parent_line;
1357
1358 --
1359 ----------------------------------------------------------------
1360 -- handles awarding for award by quote, award by line, award line
1361 -- procedure added by snatu on 08/15/03
1362 -- Coded for FPJ
1363 ----------------------------------------------------------------
1364 PROCEDURE award_auction
1365 ( p_auctioneer_id IN NUMBER
1366 , p_auction_header_id IN NUMBER
1367 , p_last_update_date IN DATE
1368 , p_mode IN VARCHAR2
1369 , p_line_num IN NUMBER
1370 , p_award_table IN PON_AWARD_TABLE
1371 , p_note_to_accepted IN VARCHAR2
1372 , p_note_to_rejected IN VARCHAR2
1373 , p_batch_id IN NUMBER
1374 , x_status OUT NOCOPY VARCHAR2
1375 )
1376 IS
1377 --
1378 l_counter BINARY_INTEGER;
1379 l_size NUMBER;
1380 l_index BINARY_INTEGER;
1381 l_rec PON_AWARD_REC;
1382 --
1383 l_award_lines t_award_lines;
1384 -- FPK: CPA
1385 l_awarded_bid_headers t_awarded_bid_headers;
1386 l_neg_has_lines PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
1387
1388 l_matrix_index NUMBER;
1389 --
1390 l_current_bid_number NUMBER;
1391 l_bid_list_index NUMBER;
1392 l_tmp_award_quantity NUMBER;
1393 l_group_type pon_auction_item_prices_all.group_type%TYPE;
1394 l_award_date DATE;
1395 TYPE BID_LIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1396 l_bid_list BID_LIST_TYPE;
1397 l_winning_bid NUMBER;
1398 l_neg_contract_type pon_auction_headers_all.contract_type%TYPE;
1399
1400 --
1401 l_has_quantity_tiers pon_auction_item_prices_all.has_quantity_tiers%TYPE;
1402 l_award_shipment_number NUMBER;
1403 l_suffix VARCHAR2(2);
1404
1405 --Unsolicited Lines Project
1406 l_allow_unsol_lines pon_auction_headers_all.ALLOW_UNSOL_OFFER_LINES%TYPE;
1407
1408 --Staggered Awards project
1409 l_allow_staggered_awards pon_auction_headers_all.ALLOW_STAGGERED_AWARDS%TYPE;
1410 l_total_agreed_amt pon_bid_headers.po_agreed_amount%TYPE;
1411
1412 l_org_id pon_auction_headers_all.org_id%TYPE;
1413 l_doctype_id pon_auction_headers_all.doctype_id%TYPE;
1414 l_are_exhibits_enabled VARCHAR2(1);
1415 --
1416 BEGIN
1417 --
1418 --
1419 IF (g_debug_mode = 'Y') THEN
1420 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1421 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION', 'Entering procedure with p_auctioneer_id: ' || p_auctioneer_id );
1422 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION',' p_last_update_date : '|| p_last_update_date || ' ,p_mode : '|| p_mode || ' ,p_line_num : '|| p_line_num);
1423 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION',' p_batch_id : '|| p_batch_id || ' ,p_auction_header_id : ' || p_auction_header_id );
1424 END IF;
1425 END IF;
1426
1427 l_neg_has_lines := PON_AUCTION_PKG.neg_has_lines(p_auction_header_id);
1428
1429 select contract_type,Nvl(ALLOW_UNSOL_OFFER_LINES,'Y'),
1430 Nvl(allow_staggered_awards, 'N'), org_id, doctype_id --ELINs project
1431 into l_neg_contract_type,l_allow_unsol_lines, -- Unsolicited Lines Project
1432 l_allow_staggered_awards, l_org_id, l_doctype_id --ELINs project
1433 from pon_auction_headers_all
1434 where auction_header_id = p_auction_header_id
1435 and rownum =1;
1436
1437 --ELINs project
1438 l_are_exhibits_enabled := PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED(l_org_id, l_doctype_id);
1439
1440 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1441 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'START');
1442 END IF;
1443
1444 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1445 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.l_are_exhibits_enabled:' || l_are_exhibits_enabled,'');
1446 END IF;
1447 --
1448 l_matrix_index := 0;
1449 l_award_date := SYSDATE;
1450 --
1451 IF (p_mode = g_AWARD_QUOTE) THEN
1452
1453 --Donot reject the other lines when only few lines are awarded and allo_staggered_awards flag is Y
1454 IF(l_allow_staggered_awards = 'Y') THEN
1455 clear_unawarded_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
1456 ELSE
1457 clear_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
1458 END IF;
1459
1460 /* Unsolicited Lines Project : If unsol lines are allowed, clear unsolicited lines award
1461 on all non-winning bids.
1462 */
1463 --Commenting as the clearing is taken care by the save unsol lines award logic in java
1464 --IF l_allow_unsol_lines = 'Y' THEN
1465 -- PON_UNSOL_AWARD_PKG.clear_loosing_unsol_awards(p_auction_header_id,p_auctioneer_id,p_award_table,l_award_date);
1466 --END IF;
1467
1468 /* update auction-header by nulling out the request-id */
1469
1470 update pon_auction_headers_all
1471 set request_id = to_number(null)
1472 where auction_header_id = p_auction_header_id;
1473
1474 -- Need to expand all the AWARDED bids to build the lines
1475 l_size := p_award_table.COUNT;
1476 FOR l_index IN 1..l_size LOOP -- Loop through all the bids
1477 l_rec := p_award_table(l_index);
1478 -- Construct Matrix only in case of awarded bids
1479 IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN THEN
1480
1481 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
1482
1483 l_winning_bid := l_rec.bid_number;
1484
1485 update_all_bid_item_prices(l_winning_bid, get_award_status(l_rec.award_outcome), l_award_date, p_auctioneer_id);
1486
1487 if(l_neg_contract_type in ('BLANKET', 'CONTRACT')) then
1488 update_all_auction_item_prices(p_auction_header_id, l_winning_bid, l_award_date, p_auctioneer_id);
1489 end if;
1490
1491 ELSE -- negotiation does not have lines
1492 -- Build table of active bids.
1493 -- All the bids will have one of the award outcome status: win, lose or no award.
1494 l_matrix_index := l_matrix_index + 1;
1495 l_awarded_bid_headers(l_matrix_index).bid_number := l_rec.bid_number;
1496 l_awarded_bid_headers(l_matrix_index).award_status := get_award_status(l_rec.award_outcome);
1497 l_awarded_bid_headers(l_matrix_index).award_date := l_award_date;
1498 END IF;
1499
1500 --Staggered Awards project
1501 --Add the new agreed amount to already agreed amount
1502 SELECT po_agreed_amount INTO l_total_agreed_amt FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id AND bid_number = l_rec.bid_number;
1503
1504 --update total agreed amount (if any)
1505 IF l_rec.total_agreement_amount is not null THEN
1506 IF l_total_agreed_amt IS NOT NULL THEN
1507 l_total_agreed_amt := l_total_agreed_amt + l_rec.total_agreement_amount;
1508 ELSE
1509 l_total_agreed_amt := l_rec.total_agreement_amount;
1510 END IF;
1511
1512 UPDATE pon_bid_headers
1513 SET po_agreed_amount = l_total_agreed_amt
1514 WHERE bid_number = l_rec.bid_number;
1515 END IF;
1516 END IF; -- IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN
1517 -- update notes
1518 update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
1519 END LOOP;
1520
1521 -- outside the loop, update all auction_lines
1522 -- in case of BPA outcome, we can award multiple bids -> but we will update
1523 -- all auction lines just once
1524
1525 IF (l_neg_has_lines = 'Y') THEN
1526 IF (l_neg_contract_type <> 'BLANKET' and l_neg_contract_type <> 'CONTRACT') THEN
1527 update_all_auction_item_prices(p_auction_header_id, l_winning_bid, l_award_date, p_auctioneer_id);
1528 END IF;
1529 END IF;
1530
1531
1532 END IF ;
1533 --
1534 IF (p_mode = g_AWARD_MULTIPLE_LINES) THEN --{
1535 --Donot reject the other lines when only few lines are awarded and allo_staggered_awards flag is Y
1536 IF(l_allow_staggered_awards = 'Y') THEN
1537 clear_unawarded_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
1538 ELSE
1539 clear_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
1540 END IF;
1541 -- Need to set award quantity and award_status
1542 l_size := p_award_table.COUNT;
1543 l_current_bid_number := -1;
1544 --
1545 FOR l_index IN 1..l_size LOOP -- Loop through all the bids
1546 l_rec := p_award_table(l_index);
1547 -- Construct Matrix only in case of awarded bids
1548 IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN THEN
1549 IF ( l_neg_contract_type = 'BLANKET' ) THEN --Get Award Qty FROM Response Qty
1550 SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , ai.quantity) INTO l_tmp_award_quantity
1551 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
1552 WHERE bi.bid_number = l_rec.bid_number
1553 AND bi.line_number = l_rec.line_number
1554 AND ai.auction_header_id = bi.auction_header_id
1555 AND ai.line_number = bi.line_number;
1556 ELSE ---- Get Award Qty FROM response Qty
1557 SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , bi.quantity) INTO l_tmp_award_quantity
1558 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
1559 WHERE bi.bid_number = l_rec.bid_number
1560 AND bi.line_number = l_rec.line_number
1561 AND ai.auction_header_id = bi.auction_header_id
1562 AND ai.line_number = bi.line_number;
1563 END IF;
1564 --
1565 SELECT ai.group_type INTO l_group_type
1566 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
1567 WHERE bi.bid_number = l_rec.bid_number
1568 AND bi.line_number = l_rec.line_number
1569 AND ai.auction_header_id = bi.auction_header_id
1570 AND ai.line_number = bi.line_number;
1571 --
1572 l_matrix_index := l_matrix_index + 1;
1573 l_award_lines(l_matrix_index).bid_number := l_rec.bid_number;
1574 l_award_lines(l_matrix_index).line_number := l_rec.line_number;
1575 l_award_lines(l_matrix_index).award_status := get_award_status(l_rec.award_outcome);
1576 l_award_lines(l_matrix_index).award_quantity := l_tmp_award_quantity;
1577 l_award_lines(l_matrix_index).award_date := l_award_date;
1578 l_award_lines(l_matrix_index).group_type := l_group_type;
1579 END IF;
1580 -- Update Internal Notes and Notes to Suppliers for each bid
1581 IF (l_current_bid_number <> l_rec.bid_number) THEN
1582 update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
1583
1584 --Staggered Awards project
1585 --Add the new agreed amount to already agreed amount
1586 SELECT po_agreed_amount INTO l_total_agreed_amt FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id AND bid_number = l_rec.bid_number;
1587 --update total agreed amount (if any)
1588 IF l_rec.total_agreement_amount is not null THEN
1589 IF l_total_agreed_amt IS NOT NULL THEN
1590 l_total_agreed_amt := l_total_agreed_amt + l_rec.total_agreement_amount;
1591 ELSE
1592 l_total_agreed_amt := l_rec.total_agreement_amount;
1593 END IF;
1594 UPDATE pon_bid_headers
1595 SET po_agreed_amount = l_total_agreed_amt
1596 WHERE bid_number = l_rec.bid_number;
1597 END IF;
1598
1599 l_current_bid_number := l_rec.bid_number;
1600 END IF;
1601 END LOOP;
1602 END IF; --}
1603 --
1604 IF ((p_mode = g_AWARD_LINE) OR (p_mode = g_AWARD_LINE_H)
1605 OR(p_mode = g_AWARD_GROUP) OR (p_mode = g_AWARD_GROUP_H)) THEN --{
1606 --
1607 -- First, REJECT the group line level awards - No cumulative awards
1608 IF (p_mode = g_AWARD_GROUP) THEN
1609 --Donot reject the other lines when only few lines are awarded and allo_staggered_awards flag is Y
1610 IF(l_allow_staggered_awards = 'Y') THEN
1611 clear_unawarded_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
1612 ELSE
1613 clear_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
1614 END IF;
1615 END IF;
1616 --
1617 l_bid_list_index := 0;
1618 SELECT ai.group_type INTO l_group_type
1619 FROM pon_auction_item_prices_all ai
1620 WHERE ai.auction_header_id = p_auction_header_id
1621 AND ai.line_number = p_line_num;
1622 --
1623 -- Getting the suffix to display the error message correctly.
1624 l_suffix := PON_LARGE_AUCTION_UTIL_PKG.GET_DOCTYPE_SUFFIX (p_auction_header_id);
1625
1626 -- Need to set award quantity and award_status
1627 l_size := p_award_table.COUNT;
1628 FOR l_index IN 1..l_size LOOP -- Loop through all the bids
1629 l_rec := p_award_table(l_index);
1630
1631 --R12.1 price tiers changes
1632 select nvl(has_quantity_tiers,'N') into l_has_quantity_tiers
1633 from pon_bid_item_prices
1634 where bid_number = l_rec.bid_number
1635 and line_number = p_line_num;
1636
1637 IF (g_debug_mode = 'Y') THEN
1638 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1639 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION', 'bid_number ' || l_rec.bid_number ||' ; line number ' ||p_line_num|| ' ; has_quantity_tiers ' || l_has_quantity_tiers);
1640 END IF;
1641 END IF;
1642
1643 -- Construct Matrix in any case (WIN/LOSR)
1644 l_matrix_index := l_matrix_index +1;
1645 l_award_lines(l_matrix_index).bid_number := l_rec.bid_number;
1646 l_award_lines(l_matrix_index).line_number := p_line_num; --l_rec.line_number;
1647 l_award_lines(l_matrix_index).award_status := get_award_status(l_rec.award_outcome);
1648 l_award_lines(l_matrix_index).award_date := l_award_date;
1649 l_award_lines(l_matrix_index).group_type := l_group_type;
1650 --
1651 IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN THEN --{
1652
1653 /*
1654 R12.1 Quantity based price tiers changes
1655 If quantity tiers are present for a line and award quantity is not null
1656 validating if award qty falls within the quantity tiers specified by the supplier
1657 Update the award shipment number acoordingly.
1658 */
1659
1660 IF ( 'Y' = l_has_quantity_tiers AND l_rec.award_quantity IS NOT NULL)
1661 THEN --{
1662 l_award_shipment_number := -1;
1663
1664 select nvl((select pbs.shipment_number
1665 from pon_bid_shipments pbs, pon_auction_item_prices_all paip
1666 where pbs.bid_number = l_rec.bid_number
1667 and pbs.line_number = p_line_num
1668 AND l_rec.award_quantity >= pbs.quantity
1669 AND l_rec.award_quantity <= pbs.max_quantity
1670 AND paip.auction_header_id = pbs.auction_header_id
1671 AND paip.line_number = pbs.line_number ),-1)
1672 into l_award_shipment_number from dual;
1673
1674 IF (g_debug_mode = 'Y') THEN
1675 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1676 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION', 'award_shipment_number ' || l_award_shipment_number);
1677 END IF;
1678 END IF;
1679
1680 IF ( l_award_shipment_number = -1) THEN --{
1681 -- Insert errors in interface table.
1682 INSERT INTO PON_INTERFACE_ERRORS(
1683 batch_id
1684 , column_name
1685 , error_message_name
1686 , table_name
1687 , INTERFACE_LINE_ID
1688 , expiration_date
1689 , created_by
1690 , creation_date
1691 , last_updated_by
1692 , last_update_date
1693 , last_update_login
1694 , TOKEN1_NAME
1695 , TOKEN1_VALUE
1696 )
1697 Values(
1698 p_batch_id
1699 , fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
1700 , 'PON_QUANTITY_TIER_VIOLATION' || l_suffix
1701 , 'PON_BID_ITEM_PRICES'
1702 , p_line_num
1703 , SYSDATE+7
1704 , fnd_global.user_id
1705 , sysdate
1706 , fnd_global.user_id
1707 , sysdate
1708 , fnd_global.login_id
1709 , 'BID_NUM'
1710 , l_rec.bid_number
1711 );
1712
1713
1714 x_status := 'FAILURE';
1715
1716 IF (g_debug_mode = 'Y') THEN
1717 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1718 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id||' bid_number : '||l_rec.bid_number||' line_num: '||p_line_num, 'Quantity Tier Violation');
1719 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT,' award_quantity: '||l_rec.award_quantity,'Quantity Tier Violation');
1720 END IF;
1721 END IF;
1722 --} End of l_shipment_number = -1
1723 ELSE --{ START of l_award_shipment_number != -1,
1724
1725 --
1726 -- Award qty falls withtin the quantity specified by the supplier.
1727 -- saving the corresponding shipment number
1728 --
1729 l_award_lines(l_matrix_index).award_shipment_number := l_award_shipment_number;
1730
1731 END IF; --} END of l_award_shipment_number != -1,
1732 --} End of has_quantity_tiers='Y' and award_quantity not null
1733 ELSE --{ Start of has_quantity_tiers <> 'Y' or award_quantity <> null
1734
1735 --
1736 -- Line does not have quantity tiers so setting the default value as -1.
1737 --
1738 l_award_lines(l_matrix_index).award_shipment_number := -1;
1739
1740 END IF; --} End of has_quantity_tiers <> 'Y' or award_quantity <> null ; END of Quantity tiers loop
1741
1742 l_award_lines(l_matrix_index).award_quantity := l_rec.award_quantity;
1743 l_award_lines(l_matrix_index).note_to_supplier := p_note_to_accepted;
1744
1745 --} End of award_outcome_win
1746 ELSE --{ Start of award_outcome_lose
1747
1748 IF ((x_status is NULL) OR (x_status = 'SUCCESS')) THEN --{
1749 l_award_lines(l_matrix_index).award_quantity := null;
1750 l_award_lines(l_matrix_index).note_to_supplier := p_note_to_rejected;
1751 END IF; --}
1752
1753 END IF; --} End of award_outcome_lose
1754
1755 --Update Notes only in case of Award Line V Page and NOT for Award Line H Page
1756 IF ((p_mode = g_AWARD_LINE OR p_mode = g_AWARD_GROUP) AND ((x_status is NULL) OR (x_status = 'SUCCESS'))) THEN
1757 update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
1758 END IF;
1759 -- Add new bid to the array
1760 l_bid_list_index := l_bid_list_index + 1;
1761 l_bid_list(l_bid_list_index) := l_rec.bid_number;
1762 --
1763 END LOOP;
1764 END IF; --}
1765 --
1766 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1767 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'MATRIX BUILT');
1768 END IF;
1769 --
1770 -- Clears the award history in case any for this auction
1771 IF (p_mode = g_AWARD_QUOTE) or (p_mode = g_AWARD_MULTIPLE_LINES) THEN --{
1772
1773 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
1774 IF(p_mode = g_AWARD_MULTIPLE_LINES) THEN
1775 update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
1776 END IF;
1777 END IF;
1778
1779 update_bid_headers(p_auction_header_id, p_auctioneer_id, l_awarded_bid_headers, l_neg_has_lines);
1780
1781 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
1782 IF(p_mode = g_AWARD_MULTIPLE_LINES) THEN
1783 update_auction_item_prices(p_auction_header_id, null, l_award_date, p_auctioneer_id, p_mode);
1784 END IF;
1785 END IF;
1786
1787 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, l_neg_has_lines);
1788
1789 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
1790
1791 bulk_update_pon_acceptances(
1792 p_auction_header_id,
1793 null, null, null,
1794 l_award_date, p_auctioneer_id, p_mode);
1795 END IF;
1796 --}
1797 ELSE --{
1798 update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
1799 l_size := l_bid_list.count;
1800 FOR l_index IN 1..l_size LOOP
1801 update_single_bid_header(l_bid_list(l_index),p_auctioneer_id);
1802 END LOOP;
1803 update_auction_item_prices(p_auction_header_id,p_line_num, l_award_date, p_auctioneer_id, p_mode);
1804 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, l_neg_has_lines);
1805 bulk_update_pon_acceptances(
1806 p_auction_header_id, p_line_num,
1807 p_note_to_accepted, p_note_to_rejected,
1808 l_award_date, p_auctioneer_id, p_mode);
1809
1810 if ( pon_clm_util_pkg.is_neg_document_federal(p_auction_header_id) = 1 ) THEN
1811 if ( pon_clm_util_pkg.is_line_clin(p_auction_header_id,p_line_num) = TRUE ) THEN
1812 if ( pon_clm_util_pkg.does_clin_have_child(p_auction_header_id,p_line_num) = TRUE ) THEN --clin has slin
1813 AWARD_INFO_SLIN_LINES(p_auction_header_id, p_line_num, L_AWARD_LINES, p_auctioneer_id);
1814 end if;
1815 if ( pon_clm_util_pkg.does_line_have_option(p_auction_header_id,p_line_num) = TRUE ) THEN --clin has option
1816 AWARD_option_LINES(p_auction_header_id, p_line_num, L_AWARD_LINES, p_auctioneer_id);
1817 end if;
1818 --ELINs project
1819 IF(l_are_exhibits_enabled = 'Y') THEN
1820 IF(PON_EXHIBITS_PKG.IS_LINE_ELIN(p_auction_header_id, p_line_num) = 'Y') THEN
1821 --This is a exhibit line. Mark parent line as awarded.
1822 AWARD_ELIN_PARENT_LINE(p_auction_header_id, p_line_num, L_AWARD_LINES, p_auctioneer_id);
1823 ELSE
1824 IF(PON_EXHIBITS_PKG.HAS_ELIN_ASSOC_FOR_LINE(p_auction_header_id, p_line_num) = 'Y') THEN
1825 --This line is associated to exhibits, update all exhibit lines as awarded.
1826 AWARD_EXHIBIT_LINES(p_auction_header_id, p_line_num, L_AWARD_LINES, p_auctioneer_id);
1827 END IF;
1828 END IF;
1829 END IF;
1830 else --slin line
1831 if ( pon_clm_util_pkg.does_line_have_option(p_auction_header_id,p_line_num) = TRUE ) THEN --slin has option
1832 AWARD_option_LINES(p_auction_header_id, p_line_num, L_AWARD_LINES, p_auctioneer_id);
1833 AWARD_PARENT_CROSS_LINKED_OPT(p_auction_header_id, p_line_num, L_AWARD_LINES, p_auctioneer_id);
1834 end if;
1835 --ELINs project
1836 IF(l_are_exhibits_enabled = 'Y'
1837 AND PON_EXHIBITS_PKG.HAS_ELIN_ASSOC_FOR_LINE(p_auction_header_id, p_line_num) = 'Y') THEN
1838 --This line is associated to exhibits, update all exhibit lines with award_status.
1839 AWARD_EXHIBIT_LINES(p_auction_header_id, p_line_num, L_AWARD_LINES, p_auctioneer_id);
1840 END IF;--End ELINs project changes
1841
1842 award_parent_line(p_auction_header_id,p_line_num,l_award_lines,p_auctioneer_id);
1843 end if;
1844 end if;
1845 End IF; --}
1846 --
1847 --
1848 --
1849 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1850 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'READY_TO_COMMIT');
1851 END IF;
1852 --
1853
1854 /* check if the auction has been modified by some other user
1855 If it has been modified, status returns failure
1856 else this is the only user modifying hte auction
1857 changes are committed to the database in the middle tier
1858 */
1859 IF (((x_status is NULL) OR (x_status = 'SUCCESS')) AND (is_auction_not_updated (p_auction_header_id, p_last_update_date))) THEN
1860 x_status := 'SUCCESS';
1861 -- update the last update date
1862 UPDATE pon_Auction_headers_all
1863 SET last_update_date = SYSDATE
1864 WHERE auction_header_id = p_auction_header_id;
1865 --
1866 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1867 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'SUCCEEDED');
1868 END IF;
1869 --
1870 ELSE
1871 x_status := 'FAILURE';
1872 --
1873 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1874 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'FAILED');
1875 END IF;
1876 --
1877 END IF;
1878 --
1879 END award_auction;
1880 --
1881 --
1882
1883 --<Sol Project>
1884 --Award CLM SLIN Lines when Parent CLIN Line is Awarded
1885 --
1886 --PROCEDURE AWARD_SLIN_LINES(p_auction_header_id, p_line_num, p_award_status, p_award_date, p_auctioneer_id);
1887
1888
1889
1890 procedure award_child_line(
1891 p_auction_header_id IN Number,
1892 p_line_num IN Number,
1893 p_award_lines IN t_award_lines,
1894 p_auctioneer_id IN Number)
1895 IS
1896 l_award_status varchar2(20);
1897 l_size Number;
1898 l_index Number;
1899 l_awarded_subline NUMBER;
1900
1901 Begin
1902 l_award_status := NULL;
1903 l_size := p_award_lines.count;
1904 for l_index in 1..l_size loop
1905
1906 --Mark Clin AS awarded IF ANY one of slin IS awarded
1907 SELECT Count(*) INTO l_awarded_subline from PON_BID_ITEM_PRICES
1908 WHERE bid_number = p_award_lines(l_index).bid_number
1909 AND group_line_id = p_line_num
1910 AND award_status = 'AWARDED';
1911
1912 IF (l_awarded_subline > 0) THEN
1913 UPDATE PON_BID_ITEM_PRICES
1914 set award_status = 'AWARDED',
1915 award_date = p_award_lines(l_index).award_date,
1916 last_update_date = p_award_lines(l_index).award_date,
1917 last_updated_by = p_auctioneer_id
1918 where bid_number = p_award_lines(l_index).bid_number
1919 and line_number = p_line_num;
1920 ELSE
1921 UPDATE PON_BID_ITEM_PRICES
1922 set award_status = p_award_lines(l_index).award_status,
1923 award_date = p_award_lines(l_index).award_date,
1924 last_update_date = p_award_lines(l_index).award_date,
1925 last_updated_by = p_auctioneer_id
1926 where bid_number = p_award_lines(l_index).bid_number
1927 and line_number = p_line_num;
1928 END IF;
1929
1930 if ( (l_award_status is NULL) AND
1931 ((p_award_lines(l_index).award_status = 'AWARDED') OR
1932 -- (p_award_lines(l_index).award_status = 'REJECTED') OR -- bug#9741473
1933 (p_award_lines(l_index).award_status = 'PARTIAL')
1934 )) THEN
1935 l_award_status := 'AWARDED';
1936 end if;
1937 end loop;
1938 UPDATE pon_auction_item_prices_all
1939 set award_status = l_award_status,
1940 last_update_date = sysdate,
1941 last_updated_by = p_auctioneer_id
1942 where auction_header_id = p_auction_header_id
1943 and line_number = p_line_num;
1944 End Award_Child_Line;
1945
1946 PROCEDURE AWARD_INFO_SLIN_LINES(
1947 p_auction_header_id IN Number,
1948 p_line_num IN Number,
1949 p_award_lines IN t_award_lines,
1950 p_auctioneer_id IN Number)
1951 IS
1952 Cursor slinCursor Is select line_number from pon_auction_item_prices_all where
1953 auction_header_id = p_auction_header_id and group_line_id = p_line_num and nvl(clm_info_flag,'N') = 'Y';
1954 Begin
1955
1956 for slinLine in slinCursor Loop
1957 award_child_line(p_auction_header_id,slinLine.line_number,p_award_lines,p_auctioneer_id);
1958 end loop;
1959
1960 END AWARD_INFO_SLIN_LINES;
1961
1962
1963 PROCEDURE AWARD_option_LINES(
1964 p_auction_header_id IN Number,
1965 p_line_num IN Number,
1966 p_award_lines IN t_award_lines,
1967 p_auctioneer_id IN Number)
1968 IS
1969 Cursor optCursor Is select line_number from pon_auction_item_prices_all where
1970 auction_header_id = p_auction_header_id and clm_base_line_num = p_line_num;
1971 l_award_price pon_bid_item_prices.award_price%TYPE;
1972 l_award_quantity pon_bid_item_prices.award_quantity%TYPE;
1973 l_size NUMBER;
1974 l_index NUMBER;
1975 l_auction_award_qty NUMBER; -- bug 9940219
1976
1977 --ELINs project
1978 l_are_exh_enabled VARCHAR2(1);
1979
1980 Begin
1981
1982 IF (g_debug_mode = 'Y') THEN
1983 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1984 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_option_LINES', 'p_auction_header_id ' ||p_auction_header_id || ' p_line_num ' ||p_line_num );
1985 END IF;
1986 END IF;
1987
1988 --ELINs project
1989 l_are_exh_enabled := PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id);
1990
1991 for optLine in optCursor Loop
1992 award_child_line(p_auction_header_id,optLine.line_number,p_award_lines,p_auctioneer_id);
1993
1994 /* ELINs project : Update all elins associated to this option line */
1995 IF(l_are_exh_enabled = 'Y') THEN
1996 award_exhibit_lines(p_auction_header_id,optLine.line_number,p_award_lines,p_auctioneer_id);
1997 END IF;
1998 --End ELINs project changes
1999
2000 l_auction_award_qty := 0;
2001
2002 -- bug 9746442 option line award details should be taken from bid item table
2003 l_size := p_award_lines.count;
2004 for l_index in 1..l_size loop
2005
2006 l_award_price := NULL;
2007 l_award_quantity := NULL;
2008
2009 SELECT decode(pbip.award_status, 'REJECTED', null,
2010 decode (paip.order_type_lookup_code,
2011 'FIXED PRICE', 1,
2012 'AMOUNT', 1,
2013 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', pbip.quantity, 1), pbip.quantity ))
2014 INTO l_award_quantity
2015 FROM pon_bid_item_prices pbip,
2016 pon_auction_item_prices_all paip
2017 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
2018 AND pbip.auction_line_number = optLine.line_number
2019 AND paip.auction_header_id = pbip.auction_header_id
2020 AND paip.line_number = pbip.line_number;
2021
2022 l_auction_award_qty := l_auction_award_qty + Nvl(l_award_quantity,0);
2023
2024
2025 IF (l_award_quantity <> 0 ) THEN
2026 SELECT decode(pbip.award_status, 'REJECTED', null,
2027 pbip.per_unit_price_component + pbip.fixed_amount_component /l_award_quantity)
2028 INTO l_award_price
2029 FROM pon_bid_item_prices pbip,
2030 pon_auction_item_prices_all paip
2031 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
2032 AND pbip.auction_line_number = optLine.line_number
2033 AND paip.auction_header_id = pbip.auction_header_id
2034 AND paip.line_number = pbip.line_number;
2035
2036 END IF;
2037
2038 UPDATE pon_bid_item_prices pbip
2039 SET award_price = l_award_price,
2040 award_quantity = l_award_quantity
2041 WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
2042 AND pbip.auction_line_number = optLine.line_number;
2043
2044
2045 END LOOP;
2046
2047 -- bug 9940219
2048 UPDATE pon_auction_item_prices_all paip
2049 SET paip.awarded_quantity = decode (paip.order_type_lookup_code,
2050 'FIXED PRICE', 1,
2051 'AMOUNT', 1,
2052 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', l_auction_award_qty, 1),l_auction_award_qty )
2053 WHERE auction_header_id = p_auction_header_id
2054 AND line_number = optLine.line_number;
2055
2056
2057 end loop;
2058
2059 END AWARD_option_LINES;
2060
2061 PROCEDURE AWARD_PARENT_CROSS_LINKED_OPT(p_auction_header_id IN Number,
2062 p_line_num IN Number,
2063 p_award_LINES IN T_AWARD_LINES,
2064 p_auctioneer_id IN Number)
2065
2066 IS
2067
2068 CURSOR optLineCur IS SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id=p_auction_header_id
2069 AND clm_base_line_num=p_line_num AND group_line_id<>(SELECT group_line_id FROM pon_auction_item_prices_all WHERE
2070 line_number=p_line_num AND auction_header_id=p_auction_header_id );
2071
2072 BEGIN
2073
2074 FOR optLine IN optLineCur LOOP
2075
2076 award_parent_line(p_auction_header_id,optLine.line_number,p_award_LINES,p_auctioneer_id);
2077
2078 END LOOP;
2079
2080 END AWARD_PARENT_CROSS_LINKED_OPT;
2081
2082
2083 PROCEDURE AWARD_parent_LINE(
2084 p_auction_header_id IN Number,
2085 p_line_num IN Number,
2086 p_award_lines IN t_award_lines,
2087 p_auctioneer_id IN Number)
2088 IS
2089 l_group_line_id Number;
2090 l_not_awd_lines Number;
2091 l_award_status pon_auction_item_prices_all.award_status%TYPE;
2092 l_award_lines NUMBER;
2093
2094 Begin
2095 select group_line_id,award_status into l_group_line_id,l_award_status from
2096 pon_auction_item_prices_all where
2097 auction_header_id = p_auction_header_id and line_number = p_line_num;
2098 select count(*) into l_not_awd_lines from pon_auction_item_prices_all where
2099 auction_header_id = p_auction_header_id and group_line_id = l_group_line_id
2100 and award_status is null
2101 AND Nvl(clm_info_flag,'N') <> 'Y'; -- bug 9746442
2102 if ( l_not_awd_lines =0 OR l_award_status IS NULL ) then --all lines are awarded
2103 --award the Parent CLIN
2104 award_child_line(p_auction_header_id, l_group_line_id, p_award_lines,p_auctioneer_id);
2105 --clin can have options
2106 award_option_lines(p_auction_header_id,l_group_line_id,p_award_lines,p_auctioneer_id);
2107 --clin can have info lines
2108 award_info_slin_lines(p_auction_header_id,l_group_line_id,p_award_lines,p_auctioneer_id);
2109 end if;
2110
2111 --Line Type and Structure Changes
2112 --Update CLIN as 'AWARDED' if atleast one slin in structure is 'AWARDED'
2113
2114 SELECT Count(*) INTO l_award_lines FROM pon_auction_item_prices_all where
2115 auction_header_id = p_auction_header_id and group_line_id = l_group_line_id
2116 and award_status = 'AWARDED';
2117
2118 IF(l_award_lines> 0) THEN
2119
2120 --award the Parent CLIN
2121 award_child_line(p_auction_header_id, l_group_line_id, p_award_lines,p_auctioneer_id);
2122 END IF;
2123
2124
2125 END AWARD_parent_line;
2126
2127
2128 /* This procedure updates bid exhibit lines associated to i/p bid, line number.
2129 * pon_auction_item_prices will not be updated here as it will be done in
2130 * award_auction procedure for mode = AWARD_MULTIPLE_LINES
2131 * This procedure should be called for Award Multiple Lines mode.
2132 */
2133 PROCEDURE update_bid_exhibit_lines
2134 (
2135 p_auction_id IN NUMBER,
2136 p_contract_type IN VARCHAR2,
2137 p_bid_number IN NUMBER,
2138 p_line_number IN NUMBER,
2139 p_award_status IN VARCHAR2,
2140 p_auctioneer_id IN NUMBER,
2141 p_mode IN VARCHAR2,
2142 p_award_date IN DATE
2143 )
2144 IS
2145
2146 --This cursor gets all the elins associated to particular line.
2147 CURSOR get_elins_for_line IS
2148 SELECT paip.line_number
2149 FROM pon_auction_exhibit_details exhibit,
2150 pon_auction_item_prices_all paip,
2151 pon_bid_item_prices pbip
2152 WHERE exhibit.auction_header_id = p_auction_id
2153 AND exhibit.associated_to_line = p_line_number
2154 AND paip.auction_header_id = exhibit.auction_header_id
2155 AND paip.exhibit_number IS NOT NULL
2156 AND paip.exhibit_number = exhibit.exhibit_number
2157 AND pbip.bid_number = p_bid_number
2158 AND pbip.line_number = paip.line_number;
2159
2160 l_tmp_award_quantity NUMBER;
2161 BEGIN
2162 IF (g_debug_mode = 'Y') THEN
2163 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2164 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.update_bid_exhibit_lines', 'Entering procedure with p_auction_id: ' || p_auction_id );
2165 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.update_bid_exhibit_lines',' p_bid_number : '|| p_bid_number || ' ,p_mode : '|| p_mode || ' ,p_line_number : '|| p_line_number);
2166 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.update_bid_exhibit_lines',' p_award_status : ' || p_award_status );
2167 END IF;
2168 END IF;
2169
2170 FOR elin IN get_elins_for_line LOOP
2171 IF (g_debug_mode = 'Y') THEN
2172 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2173 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.update_bid_exhibit_lines', 'Updating line : ' || elin.line_number );
2174 END IF;
2175 END IF;
2176 IF Nvl(p_award_status, 'REJECTED') = 'AWARDED' THEN
2177 IF ( p_contract_type IN ('BLANKET','CONTRACT') ) THEN --Get Award Qty FROM Response Qty
2178 SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , ai.quantity) INTO l_tmp_award_quantity
2179 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
2180 WHERE bi.bid_number = p_bid_number
2181 AND bi.line_number = elin.line_number
2182 AND ai.auction_header_id = bi.auction_header_id
2183 AND ai.line_number = bi.line_number;
2184 ELSE ---- Get Award Qty FROM response Qty
2185 SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , bi.quantity) INTO l_tmp_award_quantity
2186 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
2187 WHERE bi.bid_number = p_bid_number
2188 AND bi.line_number = elin.line_number
2189 AND ai.auction_header_id = bi.auction_header_id
2190 AND ai.line_number = bi.line_number;
2191 END IF;
2192 END IF;
2193 IF (g_debug_mode = 'Y') THEN
2194 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2195 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.update_bid_exhibit_lines', 'Updating line : ' || elin.line_number || ' Quantity : ' ||l_tmp_award_quantity);
2196 END IF;
2197 END IF;
2198
2199 UPDATE PON_BID_ITEM_PRICES pbip
2200 SET award_quantity = Decode(Nvl(p_award_status, 'REJECTED'), 'REJECTED', null, l_tmp_award_quantity),
2201 award_price = Decode(Nvl(p_award_status, 'REJECTED'), 'REJECTED', null,
2202 decode(nvl(l_tmp_award_quantity, 0), 0, pbip.price,
2203 pbip.per_unit_price_component + pbip.fixed_amount_component /l_tmp_award_quantity)),
2204 award_status = p_award_status,
2205 award_date = p_award_date,
2206 last_update_date = p_award_date,
2207 last_updated_by = p_auctioneer_id,
2208 award_shipment_number = NULL
2209 WHERE bid_number = p_bid_number
2210 AND line_Number = elin.line_number;
2211
2212 IF (g_debug_mode = 'Y') THEN
2213 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2214 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.update_bid_exhibit_lines', 'Line Updated : ' || elin.line_number );
2215 END IF;
2216 END IF;
2217
2218 END LOOP;
2219
2220 END update_bid_exhibit_lines;
2221
2222 PROCEDURE update_bid_item_prices
2223 (
2224 p_auction_id IN NUMBER,
2225 p_award_lines IN t_award_lines,
2226 p_auctioneer_id IN NUMBER,
2227 p_mode IN VARCHAR2
2228 )
2229 IS
2230 l_size NUMBER;
2231 l_index NUMBER;
2232 l_group_type pon_auction_item_prices_all.group_type%type;
2233 l_award_quantity pon_bid_item_prices.award_quantity%type;
2234 l_award_shipment_number NUMBER;
2235 --
2236 /* for updating group for each bid's group line,
2237 we maintain an associative array (hashmap equivalant)
2238 in the form of (bid:group_number ==> 1234:56
2239 and once all bid lines are updated, we traverse through this map
2240 and update the required bid groups
2241 */
2242 type bid_line_asso is table of varchar2(30) index by varchar2(30);
2243 l_bid_group_map bid_line_asso;
2244 l_bid_line_key VARCHAR2(30);
2245 l_parent_line_number pon_auction_item_prices_all.parent_line_number%type;
2246 l_bid_number pon_bid_item_prices.bid_number%type;
2247
2248 --ELINs project
2249 l_are_exhibits_enabled VARCHAR2(1);
2250 l_org_id pon_auction_headers_all.org_id%TYPE;
2251 l_doctype_id pon_auction_headers_all.doctype_id%TYPE;
2252 l_contract_type pon_auction_headers_all.contract_type%TYPE;
2253 --
2254 BEGIN
2255
2256 IF (g_debug_mode = 'Y') THEN
2257 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2258 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'Entering procedure with p_mode: ' || p_mode || ' ,p_auction_id : ' || p_auction_id || ' ,p_auctioneer_id : '|| p_auctioneer_id);
2259 END IF;
2260 END IF;
2261
2262 --ELINs project
2263 SELECT org_id, doctype_id, contract_type INTO l_org_id, l_doctype_id, l_contract_type
2264 FROM pon_auction_headers_all
2265 WHERE auction_header_id = p_auction_id;
2266
2267 l_are_exhibits_enabled := PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED(l_org_id, l_doctype_id);
2268 IF (g_debug_mode = 'Y') THEN
2269 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2270 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'Are Exhibits enabled? ' || l_are_exhibits_enabled);
2271 END IF;
2272 END IF;
2273 --End ELINs project
2274
2275
2276
2277 l_size := p_award_lines.COUNT;
2278 -- Loop through the matrix to update bid items and acceptances
2279 FOR l_index IN 1..l_size LOOP
2280 l_group_type := p_award_lines(l_index).group_type;
2281 IF (l_group_type = 'GROUP') THEN
2282 l_award_quantity := null;
2283 ELSE
2284 l_award_quantity := p_award_lines(l_index).award_quantity;
2285 l_award_shipment_number := p_award_lines(l_index).award_shipment_number;
2286 END IF;
2287
2288 IF (g_debug_mode = 'Y') THEN
2289 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2290 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'l_index : ' || l_index || ' ; award shipment number : ' || l_award_shipment_number || ' ; award quantity : ' || l_award_quantity);
2291 END IF;
2292 END IF;
2293
2294 --
2295 -- Price Tiers Changes. If quantity based price tiers are present call the upd_single_bid_item_prices_qt
2296 -- Api which takes the unit price from pon_bid_shipments according to awarded shipment number.
2297 --
2298
2299 IF ( l_award_shipment_number IS NULL OR
2300 l_award_shipment_number = -1 ) THEN --{
2301 update_single_bid_item_prices
2302 (
2303 p_award_lines(l_index).bid_number,
2304 p_award_lines(l_index).line_number,
2305 p_award_lines(l_index).award_status,
2306 l_award_quantity,
2307 p_award_lines(l_index).award_date,
2308 p_auctioneer_id
2309 );
2310 ELSE
2311
2312 -- Quantity Tiers Case.
2313 upd_single_bid_item_prices_qt
2314 (
2315 p_award_lines(l_index).bid_number,
2316 p_award_lines(l_index).line_number,
2317 p_award_lines(l_index).award_status,
2318 l_award_quantity,
2319 p_award_lines(l_index).award_date,
2320 p_auctioneer_id,
2321 l_award_shipment_number
2322 );
2323 END IF; --}
2324
2325 --ELINs project : If exhibits are enabled, all exhibit lines associated to this line should be updated.
2326 IF(l_are_exhibits_enabled = 'Y' AND p_mode = g_AWARD_MULTIPLE_LINES) THEN
2327 update_bid_exhibit_lines(p_auction_id,
2328 l_contract_type,
2329 p_award_lines(l_index).bid_number,
2330 p_award_lines(l_index).line_number,
2331 p_award_lines(l_index).award_status,
2332 p_auctioneer_id,
2333 p_mode,
2334 p_award_lines(l_index).award_date);
2335
2336 END IF;
2337 --End ELINs project
2338
2339 --
2340 IF ( (p_mode = g_AWARD_MULTIPLE_LINES AND l_group_type = 'LOT')
2341 OR (p_mode = g_AWARD_LINE AND l_group_type = 'LOT')
2342 OR (p_mode = g_AWARD_LINE_H AND l_group_type = 'LOT')
2343 OR (p_mode = g_AWARD_AUTO_RECOMMEND AND l_group_type = 'LOT')
2344 OR (p_mode = g_AWARD_GROUP_H AND l_group_type = 'GROUP')
2345 OR (p_mode = g_AWARD_GROUP AND l_group_type = 'GROUP') ) THEN
2346 award_bi_subline (
2347 p_auction_id,
2348 p_award_lines(l_index).bid_number,
2349 p_award_lines(l_index).line_number,
2350 p_award_lines(l_index).award_status,
2351 p_award_lines(l_index).award_date,
2352 p_auctioneer_id );
2353 ELSIF ( (p_mode = g_AWARD_MULTIPLE_LINES OR p_mode = g_AWARD_LINE
2354 OR p_mode = g_AWARD_LINE_H OR p_mode= g_AWARD_AUTO_RECOMMEND)
2355 AND (l_group_type = 'GROUP_LINE') ) THEN
2356
2357 -- get parent line number
2358 SELECT parent_line_number INTO l_parent_line_number
2359 FROM pon_auction_item_prices_all
2360 WHERE auction_header_id = p_auction_id
2361 AND line_number = p_award_lines(l_index).line_number;
2362
2363 /* Key will be bid:group 1234:56 */
2364 l_bid_line_key := to_char(p_award_lines(l_index).bid_number) || ':' || to_char(l_parent_line_number);
2365 IF NOT (l_bid_group_map.exists(l_bid_line_key)) THEN
2366 l_bid_group_map(l_bid_line_key) := l_bid_line_key;
2367 END IF;
2368 END IF;
2369 END LOOP;
2370 l_bid_line_key := l_bid_group_map.FIRST;
2371 WHILE l_bid_line_key IS NOT NULL LOOP
2372 -- update bid group
2373 l_bid_number := to_number(SUBSTR(l_bid_line_key ,1, instr(l_bid_line_key, ':') -1 ));
2374 l_parent_line_number := to_number(SUBSTR(l_bid_line_key, instr(l_bid_line_key, ':') +1));
2375 -- update parent group
2376 update_bi_group_award(
2377 p_auction_id,
2378 l_bid_number,
2379 l_parent_line_number,
2380 sysdate,
2381 p_auctioneer_id
2382 );
2383
2384 l_bid_line_key := l_bid_group_map.NEXT(l_bid_line_key);
2385 END LOOP;
2386
2387 IF (g_debug_mode = 'Y') THEN
2388 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2389 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'Returning to the caller.....');
2390 END IF;
2391 END IF;
2392
2393 EXCEPTION
2394 WHEN OTHERS THEN
2395 IF (g_debug_mode = 'Y') THEN
2396 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2397 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'An exception occurred during the execution. Raising the exception.....');
2398 END IF;
2399 END IF;
2400 RAISE;
2401 --
2402 END update_bid_item_prices;
2403 --
2404 --
2405 PROCEDURE update_single_bid_item_prices
2406 (
2407 p_bid_number IN NUMBER,
2408 p_line_number IN NUMBER,
2409 p_award_status IN VARCHAR2,
2410 p_award_quantity IN NUMBER,
2411 p_award_date IN DATE,
2412 p_auctioneer_id IN NUMBER
2413 )
2414 IS
2415 l_award_price NUMBER;
2416 BEGIN
2417
2418 IF (g_debug_mode = 'Y') THEN
2419 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2420
2421 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'Entering the procedure for p_bid_number : ' || p_bid_number || ' ; p_line_number : ' || p_line_number);
2422
2423 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'p_award_quantity : ' || p_award_quantity || ' ;p_award_date : ' || p_award_date || ' ;p_auctioneer_id : ' || p_auctioneer_id);
2424
2425 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES',' ;p_award_status : ' || p_award_status);
2426
2427 END IF;
2428 END IF;
2429
2430 --
2431 -- Cost Factors Enhancements
2432 -- If award_quantity is not zero or null then use the per unit and fixed amount component and award
2433 -- quantity to calculate the award price
2434 --
2435 SELECT decode(p_award_status, 'REJECTED', null,
2436 decode(nvl(p_award_quantity,0), 0,pbip.price,
2437 pbip.per_unit_price_component + pbip.fixed_amount_component /p_award_quantity))
2438 INTO l_award_price
2439 FROM pon_bid_item_prices pbip,
2440 pon_auction_item_prices_all paip
2441 WHERE pbip.bid_number = p_bid_number
2442 AND pbip.line_number = p_line_number
2443 AND paip.auction_header_id = pbip.auction_header_id
2444 AND paip.line_number = pbip.line_number;
2445
2446 IF (g_debug_mode = 'Y') THEN
2447 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2448 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'award_price: ' || l_award_price);
2449 END IF;
2450 END IF;
2451
2452 --
2453 -- as this procedure will be called only if price tiers are not applicable so reseting the award_Shipment_number
2454 --
2455 UPDATE PON_BID_ITEM_PRICES
2456 SET award_quantity = p_award_quantity,
2457 award_price = l_award_price,
2458 award_status = p_award_status,
2459 award_date = p_award_date,
2460 last_update_date = p_award_date,
2461 last_updated_by = p_auctioneer_id,
2462 award_shipment_number = NULL
2463 WHERE Bid_number = p_bid_number AND
2464 Line_Number = p_line_number;
2465
2466 IF (g_debug_mode = 'Y') THEN
2467 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2468 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'PON_BID_ITEM_PRICES has been updated. Returning to the caller....');
2469 END IF;
2470 END IF;
2471
2472 END update_single_bid_item_prices;
2473
2474 /*==========================================================================================================================
2475 * PROCEDURE : upd_single_bid_item_prices_qt
2476 * PARAMETERS: 1. p_bid_number - bid number for which the award_price and shipment no to be updated.
2477 * 2. p_line_number - corresponding line number
2478 * 3. p_award_status - award status 'AWARDED' or 'REJECTED'
2479 * 4. p_award_quantity - The quantity awarded
2480 * 5. p_award_date -- Award Datw
2481 * 6. p_auctioneer_id - Id of person who is saving award
2482 * 7. p_award_shipment_number - Quantity awarded falls in the tiers range corresponding to the shipment number
2483 * COMMENT : This procedure calculates the award price based on the per unit and fixed amount component and
2484 * corresponding to the award shipment number. PON_BID_ITEM_PRICES is updated accordingly
2485 *==========================================================================================================================*/
2486 PROCEDURE upd_single_bid_item_prices_qt
2487 (
2488 p_bid_number IN NUMBER,
2489 p_line_number IN NUMBER,
2490 p_award_status IN VARCHAR2,
2491 p_award_quantity IN NUMBER,
2492 p_award_date IN DATE,
2493 p_auctioneer_id IN NUMBER,
2494 p_award_shipment_number IN NUMBER
2495 )
2496 IS
2497 l_award_price NUMBER;
2498 BEGIN
2499
2500 IF (g_debug_mode = 'Y') THEN
2501 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2502 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPD_SINGLE_BID_ITEM_PRICES_QT', 'Entering the procedure for p_bid_number : ' || p_bid_number || ' ; p_line_number : ' || p_line_number);
2503 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPD_SINGLE_BID_ITEM_PRICES_QT', 'p_award_status : ' || p_award_status || ' ;p_award_quantity : ' || p_award_quantity || ' ;p_award_date : ' || p_award_date);
2504 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPD_SINGLE_BID_ITEM_PRICES_QT',' p_auctioneer_id : ' || p_auctioneer_id || ' ;p_award_shipment_number : ' || p_award_shipment_number);
2505 END IF;
2506 END IF;
2507
2508 --
2509 -- Cost Factors Enhancements
2510 -- If award_quantity is not zero or null then use the per unit and fixed amount component and award
2511 -- quantity to calculate the award price
2512 --
2513
2514 SELECT DECODE(p_award_status, 'REJECTED', NULL,
2515 DECODE (NVL(p_award_quantity,0), 0, pbs.price,
2516 pbs.per_unit_price_component+pbip.fixed_amount_component/p_award_quantity))
2517 INTO l_award_price
2518 FROM pon_bid_item_prices pbip,
2519 pon_auction_item_prices_all paip,
2520 pon_bid_shipments pbs
2521 WHERE pbip.bid_number = p_bid_number
2522 AND pbip.line_number = p_line_number
2523 AND paip.auction_header_id = pbip.auction_header_id
2524 AND paip.line_number = pbip.line_number
2525 AND pbs.bid_number = pbip.bid_number
2526 AND pbs.line_number = pbip.line_number
2527 AND pbs.shipment_number = p_award_shipment_number;
2528
2529 IF (g_debug_mode = 'Y') THEN --{
2530 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN --}
2531 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPD_SINGLE_BID_ITEM_PRICES_QT', 'award_price: ' || l_award_price);
2532 END IF; --}
2533 END IF; --}
2534
2535 UPDATE PON_BID_ITEM_PRICES
2536 SET award_quantity = p_award_quantity,
2537 award_status = p_award_status,
2538 award_date = p_award_date,
2539 last_update_date = p_award_date,
2540 last_updated_by = p_auctioneer_id,
2541 award_price = l_award_price,
2542 award_shipment_number = p_award_shipment_number
2543 WHERE Bid_number = p_bid_number AND
2544 Line_Number = p_line_number;
2545
2546 IF (g_debug_mode = 'Y') THEN
2547 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2548 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPD_SINGLE_BID_ITEM_PRICES_QT', 'PON_BID_ITEM_PRICES has been updated. Returning to the caller....');
2549 END IF;
2550 END IF;
2551
2552 END upd_single_bid_item_prices_qt;
2553
2554 --
2555 --
2556 PROCEDURE update_bid_headers
2557 (
2558 p_auction_id IN NUMBER,
2559 p_auctioneer_id IN NUMBER,
2560 p_awarded_bid_headers IN t_awarded_bid_headers DEFAULT t_emptytbl, -- FPK: CPA
2561 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
2562 )
2563 IS
2564 --
2565
2566 CURSOR c_active_bids (c_auction_id NUMBER) is
2567 SELECT bh.bid_number
2568 FROM pon_bid_headers bh
2569 WHERE bh.auction_header_id = c_auction_id
2570 AND bid_status = 'ACTIVE';
2571
2572 /* Unsolicited Lines Project : New cursor if unsol lines are allowed. Check if lines exist in each bid or not. */
2573 CURSOR c_unsol_active_bids (c_auction_id NUMBER) is
2574 SELECT bh.bid_number,Decode((SELECT Count(*) FROM pon_bid_item_prices pbip WHERE pbip.bid_number = bh.bid_number),0,'N','Y') has_lines
2575 FROM pon_bid_headers bh
2576 WHERE bh.auction_header_id = c_auction_id
2577 AND bid_status = 'ACTIVE';
2578
2579 l_unsol_active_bids_rec c_unsol_active_bids%ROWTYPE; --Unsolicited Lines Project
2580 l_active_bids_rec c_active_bids%ROWTYPE;
2581
2582 -- FPK: CPA
2583 l_index PLS_INTEGER;
2584 l_bid_headers_count PLS_INTEGER :=0; -- generic pon_bid_headers index
2585
2586 -- Declaration of individual elements to avoid ORA-3113 error because
2587 -- FORALL does not allow update of elements using rec(i).field
2588 l_bid_number_tbl Number_tbl_type;
2589 l_award_status_tbl Char25_tbl_type;
2590 l_award_date_tbl Date_tbl_type;
2591 --
2592 l_has_unsol_lines VARCHAR2(1); --Unsolicited Lines Project
2593 l_unsol_lines_allowed VARCHAR2(1); --Unsolicited Lines Project
2594
2595 BEGIN
2596 l_has_unsol_lines := 'N';
2597 /* Unsolicited Lines Project : Check if neg has any unsolcited lines on offers or not. */
2598 l_has_unsol_lines := PON_UNSOL_UTIL_PKG.HAS_UNSOL_LINES(p_auction_id) ;
2599 l_unsol_lines_allowed := PON_UNSOL_UTIL_PKG.ARE_UNSOL_LINES_ALLOWED(p_auction_id) ;
2600
2601 IF p_neg_has_lines = 'Y' OR l_has_unsol_lines = 'Y' THEN -- FPK: CPA
2602 /* Unsolicited Lines Project : If unsol lines are allowed, some offers can have lines
2603 * and others may not have. Call update_single_bid_header procedure only when
2604 * Response has lines.
2605 */
2606 IF l_unsol_lines_allowed = 'Y' THEN
2607 OPEN c_unsol_active_bids (p_auction_id);
2608 LOOP
2609 FETCH c_unsol_active_bids into l_unsol_active_bids_rec;
2610 EXIT WHEN c_unsol_active_bids%NOTFOUND;
2611 IF l_unsol_active_bids_rec.has_lines = 'Y' THEN
2612 update_single_bid_header (l_unsol_active_bids_rec.bid_number,
2613 p_auctioneer_id );
2614 ELSE
2615 /* Award By Quote Mode : If response donot have lines, then header level awarding
2616 * Iterate through p_awarded_bid_headers to get award status
2617 * If this bid is found in p_awarded_bid_headers, then set award status as 'AWARDED'
2618 */
2619 IF p_awarded_bid_headers.count > 0 THEN
2620 FOR l_award_index IN p_awarded_bid_headers.first..p_awarded_bid_headers.last
2621 LOOP
2622 IF l_unsol_active_bids_rec.bid_number = p_awarded_bid_headers(l_award_index).bid_number then
2623 --This is a winning bid, update pon_bid_headers table
2624 UPDATE PON_BID_HEADERS
2625 SET AWARD_STATUS = p_awarded_bid_headers(l_award_index).award_status,
2626 AWARD_DATE = p_awarded_bid_headers(l_award_index).award_date,
2627 last_update_date = SYSDATE,
2628 last_updated_by = p_auctioneer_id
2629 WHERE bid_number = l_unsol_active_bids_rec.bid_number;
2630
2631 END IF; --End if this is a winning bid.
2632 END LOOP;
2633
2634 END IF; --end p_awarded_bid_headers loop
2635
2636 END IF; -- end if bid has lines
2637 END LOOP; --end c_unsol_active_bids loop
2638 CLOSE c_unsol_active_bids;
2639 ELSE
2640 OPEN c_active_bids (p_auction_id);
2641 LOOP
2642 FETCH c_active_bids into l_active_bids_rec;
2643 EXIT WHEN c_active_bids%NOTFOUND;
2644 update_single_bid_header (l_active_bids_rec.bid_number,
2645 p_auctioneer_id );
2646 END LOOP;
2647 CLOSE c_active_bids;
2648 END IF;
2649 ELSE -- negotiation does not have lines
2650 -- Loop through the matrix to update bid headers
2651 -- Map all values into single table arrays to avoid Oracle errors
2652 -- caused by using rec(i).field
2653 IF p_awarded_bid_headers.count > 0 THEN
2654 FOR l_index IN p_awarded_bid_headers.first..p_awarded_bid_headers.last
2655 LOOP
2656 l_bid_headers_count := l_bid_headers_count + 1;
2657 l_bid_number_tbl(l_bid_headers_count) := p_awarded_bid_headers(l_index).bid_number;
2658 l_award_status_tbl(l_bid_headers_count):= p_awarded_bid_headers(l_index).award_status;
2659 l_award_date_tbl(l_bid_headers_count) := p_awarded_bid_headers(l_index).award_date;
2660 END LOOP;
2661 END IF;
2662 FORALL k IN 1..l_bid_headers_count
2663 UPDATE PON_BID_HEADERS
2664 SET AWARD_STATUS = l_award_status_tbl(k),
2665 AWARD_DATE = l_award_date_tbl(k), /* new column created as part of CPA project.
2666 It will be updated only when negotiation does
2667 not have lines. */
2668 last_update_date = SYSDATE,
2669 last_updated_by = p_auctioneer_id
2670 WHERE bid_number = l_bid_number_tbl(k);
2671 END IF;
2672
2673 END update_bid_headers;
2674 --
2675 --
2676 PROCEDURE update_single_bid_header
2677 (
2678 p_bid_number IN NUMBER,
2679 p_auctioneer_id IN NUMBER
2680 )
2681 IS
2682 --
2683 --Unsolicited Lines Projcet : Modify cursor query to fetch unsolicited lines also.
2684 CURSOR c_bid_lines (c_bid_number NUMBER) is
2685
2686 ((SELECT bi.Line_number,
2687 bi.award_status,
2688 nvl(bi.award_price , bi.price) * bi.award_quantity award_price
2689 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
2690 WHERE bi.bid_number = c_bid_number
2691 and bi.auction_header_id = ai.auction_header_id
2692 and bi.line_number = ai.line_number
2693 and ai.group_type in ('LOT', 'LINE', 'GROUP_LINE'))
2694 UNION
2695 (SELECT pbip.line_number,
2696 pbip.award_status,
2697 nvl(pbip.award_price , pbip.price) * pbip.award_quantity award_price
2698 FROM pon_bid_item_prices pbip
2699 WHERE pbip.bid_number = c_bid_number
2700 AND pbip.auction_line_number = -1));
2701
2702 l_bid_lines_rec c_bid_lines%ROWTYPE;
2703 --
2704 l_award_status VARCHAR2(30);
2705 l_awarded_lines NUMBER;
2706 l_rejected_lines NUMBER;
2707 l_total_lines NUMBER ;
2708 l_award_amount NUMBER;
2709 l_contract_type VARCHAR2(20);
2710 --
2711 --
2712 BEGIN
2713 l_award_amount := null;
2714 l_awarded_lines := 0;
2715 l_rejected_lines := 0;
2716 l_total_lines := 0;
2717 l_award_status := null;
2718 --
2719 SELECT ah.contract_type INTO l_contract_type
2720 FROM pon_auction_headers_all ah, pon_bid_headers bh
2721 WHERE bh.bid_number = p_bid_number
2722 AND bh.auction_header_id = ah.auction_header_id;
2723 --
2724 OPEN c_bid_lines (p_bid_number);
2725 LOOP
2726 FETCH c_bid_lines into l_bid_lines_rec;
2727 EXIT WHEN c_bid_lines%NOTFOUND;
2728 l_total_lines := l_total_lines + 1;
2729 IF l_bid_lines_rec.AWARD_STATUS = 'AWARDED' THEN
2730 l_awarded_lines := l_awarded_lines + 1;
2731 END IF;
2732 IF l_bid_lines_rec.AWARD_STATUS = 'REJECTED' THEN
2733 l_rejected_lines := l_rejected_lines + 1;
2734 END IF;
2735 IF l_contract_type = 'STANDARD' THEN
2736 l_award_amount := nvl(l_award_amount,0) + nvl(l_bid_lines_rec.award_price,0);
2737 END IF;
2738 END LOOP;
2739
2740 CLOSE c_bid_lines;
2741 IF (l_awarded_lines <> 0) AND (l_awarded_lines = l_total_lines) THEN
2742 l_award_status := 'AWARDED';
2743 ELSIF (l_rejected_lines <> 0) AND (l_rejected_lines = l_total_lines) THEN
2744 l_award_status := 'REJECTED';
2745 ELSIF l_awarded_lines > 0 THEN
2746 l_award_status := 'PARTIAL';
2747 END IF;
2748 --
2749 UPDATE PON_BID_HEADERS
2750 SET AWARD_STATUS = l_award_status,
2751 total_award_amount = l_award_amount,
2752 last_update_date = SYSDATE,
2753 last_updated_by = p_auctioneer_id
2754 WHERE bid_number = p_bid_number;
2755 --
2756 END update_single_bid_header;
2757 --
2758 --
2759 PROCEDURE update_auction_item_prices
2760 (
2761 p_auction_id IN NUMBER,
2762 p_line_number IN NUMBER,
2763 p_award_date IN DATE,
2764 p_auctioneer_id IN NUMBER,
2765 p_mode IN VARCHAR2
2766 )
2767 IS
2768 CURSOR c_auction_items (c_auction_id NUMBER) IS
2769 SELECT line_number, group_type
2770 FROM pon_auction_item_prices_all
2771 WHERE auction_header_id = c_auction_id
2772 AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
2773 l_auction_items_rec c_auction_items%ROWTYPE;
2774 --
2775 CURSOR c_item_sublines (c_auction_id NUMBER, c_parent_line_number NUMBER) IS
2776 SELECT line_number, group_type
2777 FROM pon_auction_item_prices_all
2778 WHERE auction_header_id = c_auction_id
2779 AND parent_line_number = c_parent_line_number
2780 AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
2781
2782 l_item_sublines_rec c_item_sublines%ROWTYPE;
2783 --
2784 l_group_type pon_auction_item_prices_all.group_type%TYPE;
2785 --
2786 BEGIN
2787
2788 OPEN c_auction_items(p_auction_id);
2789
2790 IF (p_mode = g_AWARD_QUOTE OR p_mode = g_AWARD_MULTIPLE_LINES
2791 OR p_mode=g_AWARD_AUTO_RECOMMEND OR p_mode = g_AWARD_OPTIMIZATION) THEN
2792 LOOP
2793 FETCH c_auction_items INTO l_auction_items_rec;
2794 EXIT WHEN c_auction_items%NOTFOUND;
2795 update_single_auction_item(p_auction_id,
2796 l_auction_items_rec.line_number,
2797 p_auctioneer_id, p_mode);
2798 END LOOP;
2799 CLOSE c_auction_items;
2800 ELSE
2801 update_single_auction_item (p_auction_id,
2802 p_line_number,
2803 p_auctioneer_id,
2804 p_mode);
2805
2806 SELECT group_type INTO l_group_type FROM pon_auction_item_prices_all
2807 WHERE auction_header_id = p_auction_id
2808 AND line_number = p_line_number;
2809 IF ((p_mode = g_AWARD_LINE AND l_group_type = 'LOT')
2810 OR (p_mode = g_AWARD_LINE_H AND l_group_type = 'LOT')
2811 OR (p_mode = g_AWARD_GROUP_H AND l_group_type = 'GROUP')
2812 OR (p_mode = g_AWARD_GROUP AND l_group_type = 'GROUP') ) THEN
2813 --
2814 OPEN c_item_sublines (p_auction_id, p_line_number);
2815 --
2816 LOOP
2817 fetch c_item_sublines into l_item_sublines_rec;
2818 EXIT WHEN c_item_sublines%NOTFOUND;
2819 -- update the child lines
2820
2821 update_single_auction_item(p_auction_id,
2822 l_item_sublines_rec.line_number,
2823 p_auctioneer_id,
2824 p_mode);
2825 END LOOP;
2826 --
2827 ELSIF ((p_mode = g_AWARD_LINE OR p_mode = g_AWARD_LINE_H)
2828 AND l_group_type = 'GROUP_LINE') THEN
2829 update_ai_group_award(p_auction_id,
2830 p_line_number,
2831 p_award_date,
2832 p_auctioneer_id);
2833
2834 END IF;
2835 --
2836 END IF;
2837 --
2838 --
2839 END update_auction_item_prices;
2840 --
2841 --
2842 PROCEDURE update_single_auction_item
2843 (
2844 p_auction_id IN NUMBER,
2845 p_line_number IN NUMBER,
2846 p_auctioneer_id IN NUMBER,
2847 p_mode IN pon_auction_item_prices_all.award_mode%type
2848 )
2849 IS
2850 CURSOR c_bid_items (c_auction_id NUMBER, c_line_number NUMBER) IS
2851 SELECT bi.Line_number,
2852 ai.order_type_lookup_code,
2853 bi.award_status,
2854 bi.award_quantity,
2855 ai.group_type
2856 FROM pon_bid_item_prices bi,
2857 pon_bid_headers bh,
2858 pon_auction_item_prices_all ai
2859 WHERE bi.auction_header_id = c_auction_id
2860 AND bi.line_number = c_line_number
2861 AND bh.bid_status = 'ACTIVE'
2862 AND bh.auction_header_id = bi.auction_header_id
2863 AND bh.bid_number = bi.bid_number
2864 AND ai.auction_header_id = bi.auction_header_id
2865 AND ai.line_number = bi.line_number
2866 AND Nvl(ai.award_status, 'NO') <> 'COMPLETED';--Staggered Awards project
2867 l_bid_items_rec c_bid_items%ROWTYPE;
2868 l_award_status VARCHAR2(20);
2869 l_award_made BOOLEAN;
2870 l_award_quantity NUMBER;
2871 l_line_type VARCHAR2(20);
2872 l_contract_type VARCHAR2(20);
2873 l_group_type VARCHAR2(20);
2874 l_item_award_mode pon_auction_item_prices_all.award_mode%type;
2875 --
2876 BEGIN
2877 l_award_status := null;
2878 l_award_quantity := null;
2879 l_line_type := NULL;
2880 l_award_made := FALSE;
2881 l_item_award_mode := null;
2882 --
2883 SELECT ah.contract_type INTO l_contract_type
2884 FROM pon_auction_headers_all ah
2885 WHERE ah.auction_header_id = p_auction_id;
2886 --
2887 OPEN c_bid_items(p_auction_id, p_line_number) ;
2888 LOOP
2889 FETCH c_bid_items INTO l_bid_items_rec;
2890 EXIT WHEN c_bid_items%NOTFOUND;
2891 l_line_type := l_bid_items_rec.order_type_lookup_code;
2892 l_group_type:= l_bid_items_rec.group_type;
2893 IF l_bid_items_rec.award_status = 'AWARDED' THEN
2894 l_award_quantity := nvl(l_award_quantity,0) + l_bid_items_rec.award_quantity;
2895 IF (NOT l_award_made) THEN
2896 l_award_made := TRUE;
2897 END IF;
2898 END IF;
2899 IF ((l_award_status is null)
2900 AND ((l_bid_items_rec.award_status = 'AWARDED'))
2901 --Bug 12573845
2902 --PON_AUCTION_ITEM_PRICES_ALL table was being updated with AWARDED status regardless of the bid status
2903 --Modified such that only if Bid line is awarded, Negotiation line is set to AWARDED
2904
2905 --OR (l_bid_items_rec.award_status = 'REJECTED') --bug No: 9741473
2906 --OR (l_bid_items_rec.award_status = 'PARTIAL'))
2907 ) THEN
2908 l_award_status := 'AWARDED';
2909 END IF;
2910 END LOOP;
2911 CLOSE c_bid_items;
2912 --
2913 IF (l_award_made) AND (NOT (l_contract_type = 'STANDARD' AND l_line_type = 'QUANTITY'))
2914 AND (l_group_type <> 'LOT_LINE' AND l_group_type <> 'GROUP') THEN
2915 l_award_quantity := 1;
2916 END IF ;
2917 --
2918 -- set award mode = GROUP for group level award.
2919 IF(p_mode = g_AWARD_GROUP OR p_mode = g_AWARD_GROUP_H) AND l_group_type = 'GROUP' THEN
2920 l_item_award_mode := 'GROUP';
2921 END IF;
2922 UPDATE pon_auction_item_prices_all
2923 SET award_status = l_award_status,
2924 awarded_quantity = l_award_quantity,
2925 award_mode = l_item_award_mode,
2926 last_update_date = SYSDATE,
2927 last_updated_by = p_auctioneer_id
2928 WHERE auction_header_id = p_auction_id
2929 AND line_number = p_line_number
2930 AND Nvl(award_status, 'NO') <> 'COMPLETED';
2931 --
2932 END update_single_auction_item;
2933 --
2934 --
2935 PROCEDURE update_auction_headers
2936 (
2937 p_auction_id IN NUMBER,
2938 p_mode IN VARCHAR2,
2939 p_award_date IN DATE,
2940 p_auctioneer_id IN NUMBER,
2941 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
2942 )
2943 IS
2944 --
2945 /*
2946 CURSOR c_auction_lines (c_auction_id NUMBER) is
2947 SELECT Line_number, award_status
2948 FROM pon_auction_item_prices_all
2949 WHERE auction_header_id = c_auction_id
2950 AND number_of_bids > 0
2951 AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
2952
2953 l_auction_lines_rec c_auction_lines%ROWTYPE;
2954 */
2955 --
2956 l_award_status VARCHAR2(20);
2957 l_award_mode VARCHAR2(20);
2958 l_awarded_lines NUMBER;
2959 l_total_lines NUMBER;
2960
2961 l_awarded_bids NUMBER; -- FPK: CPA
2962
2963 l_has_unsol_lines VARCHAR2(1); --Unsolicited Lines Project
2964 l_awarded_unsol_line_count NUMBER; --Unsolicited Lines Project
2965 --
2966 BEGIN
2967 l_awarded_lines := 0;
2968 l_total_lines := 0;
2969 l_award_status := 'NO';
2970 l_award_mode := null;
2971
2972 -- Unsolicited Lines Project
2973 l_has_unsol_lines := PON_UNSOL_UTIL_PKG.HAS_UNSOL_LINES(p_auction_id);
2974 l_awarded_unsol_line_count := 0;
2975
2976 /* CASE p_mode
2977 WHEN g_AWARD_QUOTE THEN l_Award_mode := 'HEADER';
2978 WHEN g_AWARD_MULTIPLE_LINES THEN l_award_mode := 'MULTIPLE_LINES';
2979 WHEN g_AWARD_LINE THEN l_award_mode := 'LINE';
2980 WHEN g_AWARD_LINE_H THEN l_award_mode := 'LINE';
2981 WHEN g_AWARD_AUTO_RECOMMEND THEN l_award_mode := 'LINE';
2982 ELSE l_Award_Status := null;
2983 END CASE;
2984 */
2985 IF p_mode = g_AWARD_QUOTE THEN
2986 l_Award_mode := 'HEADER';
2987 ELSIF p_mode = g_AWARD_MULTIPLE_LINES THEN
2988 l_award_mode := 'MULTIPLE_LINES';
2989 ELSIF p_mode = g_AWARD_LINE THEN
2990 l_award_mode := 'LINE';
2991 ELSIF p_mode = g_AWARD_LINE_H THEN
2992 l_award_mode := 'LINE';
2993 ELSIF p_mode = g_AWARD_GROUP THEN
2994 l_award_mode := 'LINE';
2995 ELSIF p_mode = g_AWARD_GROUP_H THEN
2996 l_award_mode := 'LINE';
2997 ELSIF p_mode = g_AWARD_AUTO_RECOMMEND THEN
2998 l_award_mode := 'LINE';
2999 ELSIF p_mode = g_AWARD_OPTIMIZATION THEN
3000 l_award_mode := 'LINE';
3001 ELSE l_award_mode := null;
3002 END IF;
3003 --
3004 --Unsolicited Lines Project : If unsol lines exist on any of the bids.
3005 IF p_neg_has_lines = 'Y' OR l_has_unsol_lines = 'Y' THEN -- FPK: CPA
3006
3007 /*
3008 large-auction-support changes
3009 rather than looping over all the lines, we will simply
3010 execute a single query
3011 */
3012
3013 SELECT count(Line_number), sum(decode(award_status, 'AWARDED', 1, 0))
3014 INTO l_total_lines, l_awarded_lines
3015 FROM pon_auction_item_prices_all
3016 WHERE auction_header_id = p_auction_id
3017 AND number_of_bids > 0
3018 AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
3019
3020 IF ((l_awarded_lines <> 0) AND (l_awarded_lines = l_total_lines) ) THEN
3021 l_award_status := 'AWARDED';
3022 ELSIF (l_awarded_lines > 0) THEN
3023 l_award_status := 'PARTIAL';
3024 ELSIF l_has_unsol_lines = 'Y' THEN
3025 /* Unsolicited Lines Project : If no lines in the auction are awarded,
3026 * check if any of the unsolicited lines are awarded or not.
3027 * If any of the unsolcited line on any offer is awarded, then mark auction
3028 * award_status as 'PARTIAL'
3029 */
3030 SELECT sum(Decode(award_status, 'AWARDED', 1, 0))
3031 INTO l_awarded_unsol_line_count
3032 FROM pon_bid_item_prices
3033 WHERE auction_header_id = p_auction_id
3034 AND auction_line_number = -1;
3035
3036 IF l_awarded_unsol_line_count > 0 THEN
3037 l_award_status := 'PARTIAL';
3038 END IF;
3039
3040 END IF;
3041 ELSE -- negotiation does not have lines
3042 BEGIN
3043 select 'AWARDED' -- it means an award decision was made
3044 into l_award_status
3045 from dual
3046 where exists (select 1
3047 from pon_bid_headers
3048 where auction_header_id = p_auction_id
3049 and bid_status = 'ACTIVE'
3050 and award_status IN ('AWARDED', 'REJECTED'));
3051 EXCEPTION
3052 WHEN NO_DATA_FOUND THEN
3053 NULL; -- award_status is set to 'NO' in the beggining of the procedure.
3054 END;
3055
3056 END IF; -- IF p_neg_has_lines = 'Y'
3057
3058 UPDATE PON_Auction_HEADERS_all
3059 SET AWARD_STATUS = l_award_status,
3060 award_mode = l_award_mode,
3061 award_date = p_award_date,
3062 last_updated_by = p_auctioneer_id
3063 -- modified after last update date check
3064 --award_approval_status = 'REQUIRED'
3065 --last_update_date = SYSDATE
3066 WHERE auction_header_id = p_auction_id ;
3067
3068 UPDATE PON_AUCTION_HEADERS_ALL
3069 SET award_approval_status = 'REQUIRED'
3070 WHERE auction_header_id = p_auction_id
3071 AND nvl(award_approval_flag, 'N') = 'Y';
3072 --
3073 END update_auction_headers;
3074 --
3075 --
3076 -- Updates the award_approval_status and sets to REQUIRED
3077 -- since the award is modified, sets the upadated by column as well.
3078 PROCEDURE update_award_agreement_amount
3079 (
3080 p_auction_id IN NUMBER,
3081 p_auctioneer_id IN NUMBER
3082 )
3083 IS
3084 BEGIN
3085 -- Updates approval_status if approval flag is set
3086 UPDATE PON_AUCTION_HEADERS_ALL
3087 SET award_approval_status = 'REQUIRED'
3088 WHERE auction_header_id = p_auction_id
3089 AND nvl(award_approval_flag, 'N') = 'Y';
3090 --
3091 -- Updates last update date etc since award is modified.
3092 UPDATE PON_Auction_HEADERS_all
3093 SET award_date = SYSDATE,
3094 last_updated_by = p_auctioneer_id,
3095 last_update_date = SYSDATE
3096 WHERE auction_header_id = p_auction_id ;
3097 --
3098 END update_award_agreement_amount;
3099 --
3100 --
3101 PROCEDURE bulk_update_pon_acceptances
3102 ( p_auction_header_id IN NUMBER,
3103 p_line_number IN NUMBER,
3104 p_note_to_accepted IN VARCHAR2,
3105 p_note_to_rejected IN VARCHAR2,
3106 p_award_date IN DATE,
3107 p_auctioneer_id IN NUMBER,
3108 p_mode IN VARCHAR2
3109 )
3110 IS
3111 BEGIN
3112 IF(p_line_number > 0 ) THEN
3113 IF (p_mode = g_AWARD_GROUP OR p_mode = g_AWARD_GROUP_H) THEN
3114 -- Group Level Awards
3115 -- Insert empty notes for group lines
3116 -- Delete notes for a line
3117 DELETE FROM pon_acceptances
3118 WHERE auction_header_id = p_auction_header_id
3119 AND line_number IN (SELECT line_number FROM pon_auction_item_prices_all
3120 WHERE parent_line_number = p_line_number
3121 AND auction_header_id = p_auction_header_id);
3122 INSERT INTO pon_acceptances (
3123 acceptance_id,
3124 auction_header_id,
3125 auction_line_number,
3126 bid_number,
3127 line_number,
3128 acceptance_type,
3129 acceptance_date,
3130 reason,
3131 creation_date,
3132 created_by)
3133 SELECT pon_acceptances_s.nextval,
3134 bi.auction_header_id,
3135 bi.auction_line_number,
3136 bi.bid_number,
3137 bi.line_number,
3138 bi.award_status,
3139 p_award_date,
3140 null,
3141 p_award_date,
3142 p_auctioneer_id
3143 FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
3144 WHERE bi.auction_header_id = ai.auction_header_id
3145 AND ai.line_number = bi.line_number
3146 AND (bi.award_status = 'AWARDED'
3147 OR bi.award_status = 'REJECTED')
3148 AND bi.bid_number = bh.bid_number
3149 AND bh.bid_status = 'ACTIVE'
3150 AND ai.auction_header_id = p_auction_header_id
3151 AND ai.parent_line_number = p_line_number;
3152 --
3153 ELSE
3154 -- Award Line Mode
3155 -- Delete notes for a line
3156 DELETE FROM pon_acceptances
3157 WHERE auction_header_id = p_auction_header_id
3158 AND line_number = p_line_number;
3159 INSERT INTO pon_acceptances (
3160 acceptance_id,
3161 auction_header_id,
3162 auction_line_number,
3163 bid_number,
3164 line_number,
3165 acceptance_type,
3166 acceptance_date,
3167 reason,
3168 creation_date,
3169 created_by)
3170 SELECT pon_acceptances_s.nextval,
3171 bi.auction_header_id,
3172 bi.auction_line_number,
3173 bi.bid_number,
3174 bi.line_number,
3175 bi.award_status,
3176 p_award_date,
3177 decode (bi.award_status,
3178 'AWARDED', p_note_to_accepted,
3179 'REJECTED', p_note_to_rejected,
3180 null),
3181 SYSDATE,
3182 p_auctioneer_id
3183 FROM pon_bid_item_prices bi, pon_bid_headers bh
3184 WHERE bi.auction_header_id = p_auction_header_id
3185 AND bi.line_number = p_line_number
3186 AND (bi.award_status = 'AWARDED'
3187 OR bi.award_status = 'REJECTED')
3188 AND bi.bid_number = bh.bid_number
3189 AND bh.bid_status = 'ACTIVE';
3190 END IF;
3191 ELSE
3192 -- Header Level Award
3193 -- Delete notes for an auction
3194 DELETE FROM pon_acceptances
3195 WHERE auction_header_id = p_auction_header_id;
3196 --
3197 INSERT INTO pon_acceptances (
3198 acceptance_id,
3199 auction_header_id,
3200 auction_line_number,
3201 bid_number,
3202 line_number,
3203 acceptance_type,
3204 acceptance_date,
3205 reason,
3206 creation_date,
3207 created_by)
3208 SELECT pon_acceptances_s.nextval,
3209 bi.auction_header_id,
3210 bi.auction_line_number,
3211 bi.bid_number,
3212 bi.line_number,
3213 bi.award_status,
3214 p_award_date,
3215 decode (bi.award_status,
3216 'AWARDED', p_note_to_accepted,
3217 'REJECTED', p_note_to_rejected,
3218 null),
3219 p_award_date,
3220 p_auctioneer_id
3221 FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
3222 WHERE bi.auction_header_id = p_auction_header_id
3223 AND (bi.award_status = 'AWARDED'
3224 OR bi.award_status = 'REJECTED')
3225 AND bi.bid_number = bh.bid_number
3226 AND bh.bid_status = 'ACTIVE'
3227 AND bi.auction_header_id = ai.auction_header_id
3228 AND bi.line_number = ai.line_number
3229 AND ai.group_type IN ('LOT', 'LINE', 'GROUP_LINE');
3230 END IF;
3231 END bulk_update_pon_acceptances;
3232 --
3233 --
3234 FUNCTION get_award_status (award_outcome IN VARCHAR2)
3235 RETURN VARCHAR2
3236 IS
3237 --
3238 l_award_status VARCHAR2(20);
3239 BEGIN
3240 /*
3241 CASE award_outcome
3242 WHEN g_AWARD_OUTCOME_WIN THEN l_Award_Status := 'AWARDED';
3243 WHEN g_AWARD_OUTCOME_LOSE THEN l_Award_Status := 'REJECTED';
3244 WHEN g_AWARD_OUTCOME_NOAWARD THEN l_Award_Status := 'REJECTED';
3245 ELSE l_Award_Status := null;
3246 END CASE;
3247 */
3248 IF award_outcome = g_AWARD_OUTCOME_WIN THEN
3249 l_Award_Status := 'AWARDED';
3250 ELSIF award_outcome = g_AWARD_OUTCOME_LOSE THEN
3251 l_Award_Status := 'REJECTED';
3252 ELSIF award_outcome = g_AWARD_OUTCOME_NOAWARD THEN
3253 l_Award_Status := 'REJECTED';
3254 ELSE l_Award_Status := null;
3255 END IF;
3256
3257 RETURN l_award_status;
3258 --
3259 END get_award_status;
3260 --
3261 --
3262 PROCEDURE update_unawarded_acceptances
3263 ( p_auction_header_id IN NUMBER,
3264 p_line_number IN NUMBER,
3265 p_note_to_rejected IN VARCHAR2,
3266 p_award_date IN DATE,
3267 p_auctioneer_id IN NUMBER
3268 )
3269 IS
3270 BEGIN
3271 -- Award Line Mode
3272 -- Delete rejected notes for a line
3273 DELETE FROM pon_acceptances
3274 WHERE auction_header_id = p_auction_header_id
3275 AND line_number = p_line_number
3276 AND acceptance_type = 'REJECTED';
3277 -- insert rejection note for all rejected suppliers
3278 INSERT INTO pon_acceptances (
3279 acceptance_id,
3280 auction_header_id,
3281 auction_line_number,
3282 bid_number,
3283 line_number,
3284 acceptance_type,
3285 acceptance_date,
3286 reason,
3287 creation_date,
3288 created_by)
3289 SELECT pon_acceptances_s.nextval,
3290 bi.auction_header_id,
3291 bi.auction_line_number,
3292 bi.bid_number,
3293 bi.line_number,
3294 'REJECTED',
3295 p_award_date,
3296 p_note_to_rejected,
3297 p_award_date,
3298 p_auctioneer_id
3299 FROM pon_bid_item_prices bi, pon_bid_headers bh
3300 WHERE bi.auction_header_id = p_auction_header_id
3301 AND bi.line_number = p_line_number
3302 AND nvl(bi.award_status, 'NO') <> 'AWARDED' -- can be REJECTED/ NO
3303 AND bi.bid_number = bh.bid_number
3304 AND bh.bid_status = 'ACTIVE';
3305 END update_unawarded_acceptances;
3306 --
3307 --
3308 PROCEDURE update_notes_for_bid
3309 (
3310 p_bid_number IN NUMBER,
3311 p_note_to_supplier IN VARCHAR2,
3312 p_internal_note IN VARCHAR2,
3313 p_auctioneer_id IN NUMBER
3314 )
3315 IS
3316 BEGIN
3317 UPDATE pon_bid_headers
3318 SET Internal_note = p_internal_note,
3319 note_to_supplier = p_note_to_supplier
3320 WHERE bid_number = p_bid_number;
3321 END update_notes_for_bid;
3322 --
3323 --
3324 PROCEDURE clear_draft_awards
3325 (
3326 p_auction_header_id IN NUMBER,
3327 p_line_number IN NUMBER,
3328 p_award_date IN DATE,
3329 p_auctioneer_id IN NUMBER,
3330 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
3331 )
3332 IS
3333 BEGIN
3334
3335 IF p_neg_has_lines = 'Y' THEN -- FPK: CPA
3336
3337
3338 IF (p_line_number IS NULL OR p_line_number <= 0 )THEN
3339 -- Header level awards
3340 --Update award status to REJECTED for all the bids
3341 UPDATE pon_bid_item_prices
3342 SET award_status = 'REJECTED',
3343 award_quantity = NULL,
3344 award_date = p_award_date,
3345 last_update_date = p_award_date,
3346 last_updated_by = p_auctioneer_id,
3347 award_shipment_number = NULL,
3348 award_price = NULL
3349 WHERE bid_number IN (
3350 SELECT bid_number
3351 FROM pon_bid_headers
3352 WHERE auction_header_id = p_auction_header_id
3353 AND bid_status = 'ACTIVE'
3354 )
3355 AND auction_line_number <> -1; -- Unsolicited Lines Project : Donot clear unsolicited lines awarded.
3356
3357 -- Delete All Awards since it is a header-level awarding
3358 DELETE FROM pon_acceptances
3359 WHERE auction_header_id = p_auction_header_id
3360 AND auction_line_number <> -1; -- Unsolicited Lines Project : Donot delete unsolicited lines records.
3361
3362
3363 -- reset the award mode at auction item level
3364 UPDATE pon_auction_item_prices_all
3365 SET award_mode = null
3366 WHERE auction_header_id = p_auction_header_id;
3367 ELSE
3368 -- Group Level awards need to be rejected first
3369 --Update award status to REJECTED for all the bids
3370 UPDATE pon_bid_item_prices
3371 SET award_status = 'REJECTED',
3372 award_quantity = NULL,
3373 award_date = p_award_date,
3374 last_update_date = p_award_date,
3375 last_updated_by = p_auctioneer_id,
3376 award_shipment_number = NULL,
3377 award_price = NULL
3378 WHERE bid_number IN (
3379 SELECT bid_number
3380 FROM pon_bid_headers
3381 WHERE auction_header_id = p_auction_header_id
3382 AND bid_status = 'ACTIVE'
3383 )
3384 AND line_number IN (SELECT line_number
3385 FROM pon_auction_item_prices_all
3386 WHERE auction_header_id = p_auction_header_id
3387 AND (line_number = p_line_number
3388 OR parent_line_number = p_line_number));
3389
3390 -- Delete All group line awards since it is a group-level awarding
3391 DELETE FROM pon_acceptances
3392 WHERE auction_header_id = p_auction_header_id
3393 AND line_number IN (SELECT line_number
3394 FROM pon_auction_item_prices_all
3395 WHERE auction_header_id = p_auction_header_id
3396 AND parent_line_number = p_line_number);
3397 END IF;
3398 END IF;
3399
3400 /* FPK: CPA
3401 Reset notes for all the bids and update all active bids award status to REJECTED no matter
3402 if negotiation has lines or not. Previoulsy award_status was not being updated to REJECTED when
3403 negotiation had lines, but there is no harm in doing so at this point, as award_status will be
3404 updated later in update_single_bid_header procedure. */
3405
3406 UPDATE pon_bid_headers
3407 SET award_status = 'REJECTED',
3408 note_to_supplier = NULL,
3409 internal_note = NULL,
3410 po_agreed_amount = NULL,
3411 last_update_date = SYSDATE,
3412 last_updated_by = p_auctioneer_id
3413 WHERE auction_header_id = p_auction_header_id
3414 AND bid_status = 'ACTIVE';
3415 END clear_draft_awards;
3416 --
3417 --
3418 --
3419 --
3420 PROCEDURE clear_awards_recommendation
3421 (
3422 p_auction_header_id NUMBER,
3423 p_award_date DATE,
3424 p_auctioneer_id IN NUMBER
3425 )
3426 IS
3427 BEGIN
3428 --Update award status to REJECTED for all the bids
3429 UPDATE pon_bid_item_prices
3430 SET award_status = 'REJECTED',
3431 award_quantity = NULL,
3432 award_date = p_award_date,
3433 last_update_date = SYSDATE,
3434 last_updated_by = p_auctioneer_id,
3435 award_price = NULL
3436 WHERE bid_number IN (
3437 SELECT bid_number
3438 FROM pon_bid_headers
3439 WHERE auction_header_id = p_auction_header_id
3440 AND bid_status = 'ACTIVE'
3441 );
3442 --reset notes for all the bids
3443 UPDATE pon_bid_headers
3444 SET po_agreed_amount = NULL,
3445 last_update_date = SYSDATE,
3446 last_updated_by = p_auctioneer_id
3447 WHERE bid_number IN (
3448 SELECT bid_number
3449 FROM pon_bid_headers
3450 WHERE auction_header_id = p_auction_header_id
3451 AND bid_status = 'ACTIVE'
3452 );
3453 END clear_awards_recommendation;
3454
3455 --
3456 --
3457 ----------------------------------------------------------------
3458 -- handles accepting an AutoAward Scenario
3459 ----------------------------------------------------------------
3460 --
3461 PROCEDURE accept_award_scenario
3462 (
3463 p_scenario_id IN NUMBER,
3464 p_auctioneer_id IN NUMBER,
3465 p_last_update_date IN DATE,
3466 x_status OUT NOCOPY VARCHAR2
3467 )
3468 IS
3469
3470 l_auction_header_id NUMBER;
3471 l_mode VARCHAR2(50);
3472 l_batch_id NUMBER;
3473 l_num_of_non_shortlisted_supp NUMBER;
3474 BEGIN
3475
3476 -- retrieve auction header id and batch id
3477 BEGIN
3478 SELECT COUNT(DISTINCT pbh.bid_number)
3479 INTO l_num_of_non_shortlisted_supp
3480 FROM pon_optimize_results por, pon_bid_headers pbh
3481 WHERE por.bid_number = pbh.bid_number
3482 AND pbh.shortlist_flag = 'N'
3483 AND por.scenario_id = p_scenario_id;
3484
3485 IF (l_num_of_non_shortlisted_supp > 0) THEN
3486 x_status := 'NOT_SHORTLISTED';
3487 RETURN;
3488 END IF;
3489
3490 SELECT auction_header_id, pon_auction_summary_s.NEXTVAL
3491 INTO l_auction_header_id, l_batch_id
3492 FROM pon_optimize_scenarios
3493 WHERE scenario_id = p_scenario_id;
3494 EXCEPTION WHEN NO_DATA_FOUND THEN
3495 x_status := 'FAILURE';
3496 RETURN;
3497 END;
3498
3499 l_mode := g_AWARD_OPTIMIZATION;
3500
3501 -- insert award results into interface table
3502 INSERT into pon_auction_summary
3503 (batch_id,
3504 auction_id,
3505 bid_number,
3506 line_number,
3507 award_quantity,
3508 award_shipment_number)
3509 SELECT
3510 l_batch_id,
3511 l_auction_header_id,
3512 por.bid_number,
3513 por.line_number,
3514 por.award_quantity,
3515 por.award_shipment_number
3516 FROM pon_optimize_results por, pon_auction_item_prices_all paip,
3517 pon_auction_headers_all pah,
3518 pon_bid_item_prices pbip
3519 WHERE pah.auction_header_id = l_auction_header_id
3520 AND pah.auction_header_id = paip.auction_header_id
3521 AND por.bid_number = pbip.bid_number
3522 AND por.line_number = pbip.line_number
3523 AND por.scenario_id = p_scenario_id
3524 AND paip.line_number = por.line_number;
3525
3526
3527 -- save the award result
3528 save_award_recommendation(l_batch_id, p_auctioneer_id, p_last_update_date, l_mode, x_status);
3529
3530 IF (x_status = 'FAILURE') THEN
3531 RETURN;
3532 END IF;
3533
3534 -- unset the accepted date of the previously accepted scenario
3535 UPDATE pon_optimize_scenarios
3536 SET accepted_date = NULL
3537 WHERE accepted_date IS NOT NULL
3538 AND auction_header_id= l_auction_header_id;
3539
3540 -- set accepted date of the accepted scenario
3541 UPDATE pon_optimize_scenarios
3542 SET accepted_date = SYSDATE,
3543 last_update_date = SYSDATE,
3544 last_updated_by = p_auctioneer_id
3545 WHERE scenario_id = p_scenario_id;
3546
3547 -- clean up inteface table
3548 DELETE FROM pon_auction_summary
3549 WHERE batch_id = l_batch_id;
3550
3551 END accept_award_scenario;
3552
3553 --
3554 --
3555 ----------------------------------------------------------------
3556 -- procedure added by snatu on 08/15/03
3557 -- handles awarding in FPJ for system recommended awards
3558 ----------------------------------------------------------------
3559 --
3560 PROCEDURE save_award_recommendation
3561 (
3562 p_batch_id IN NUMBER,
3563 p_auctioneer_id IN NUMBER,
3564 p_last_update_date IN DATE,
3565 p_mode IN VARCHAR2,
3566 x_status OUT NOCOPY VARCHAR2
3567 )
3568 IS
3569 CURSOR c_reco_awards (c_batch_id NUMBER) IS
3570 SELECT
3571 pas.auction_id,
3572 pas.line_number,
3573 pas.bid_number,
3574 decode(p_mode,
3575 g_AWARD_OPTIMIZATION, decode(ai.order_type_lookup_code, 'RATE', decode(ai.quantity, NULL, NULL, pas.award_quantity), 'QUANTITY', decode(ai.quantity, NULL, NULL, pas.award_quantity), pas.award_quantity),
3576 decode(ai.order_type_lookup_code, 'RATE', decode(ai.quantity, NULL, NULL, pas.award_quantity), 'QUANTITY', decode(ai.quantity, NULL, NULL, pas.award_quantity), pas.award_quantity))award_quantity,
3577 pas.bid_price,
3578 pas.trading_partner_id,
3579 pas.trading_partner_contact_id,
3580 pas.batch_id,
3581 ai.group_type,
3582 pas.award_shipment_number
3583 FROM pon_auction_summary pas
3584 , pon_auction_item_prices_all ai
3585 , pon_auction_headers_all ah
3586 WHERE
3587 pas.award_quantity >0
3588 AND pas.batch_id = c_batch_id
3589 AND ah.auction_header_id = pas.auction_id
3590 AND ai.auction_header_id = pas.auction_id
3591 AND ai.line_number = pas.line_number
3592 ORDER BY
3593 pas.line_number;
3594 l_reco_awards_rec c_reco_awards%ROWTYPE;
3595 --
3596 l_award_lines t_award_lines;
3597 l_matrix_index NUMBER;
3598 l_auction_header_id NUMBER;
3599 l_current_bid_number NUMBER;
3600 l_current_update_date DATE;
3601 l_award_date DATE;
3602
3603 BEGIN
3604
3605 l_current_bid_number := null;
3606 l_matrix_index := 0;
3607 l_award_date := SYSDATE;
3608 --
3609 -- Need to set award quantity and award_status and award_date
3610 OPEN c_reco_awards (p_batch_id);
3611 LOOP
3612 fetch c_reco_awards into l_reco_awards_rec;
3613 EXIT WHEN c_reco_awards%NOTFOUND;
3614 -- Get Auction Header Id only once
3615 IF (l_matrix_index = 0) THEN
3616 l_auction_header_id := l_reco_awards_rec.auction_id;
3617 END IF;
3618
3619 --DBMS_OUTPUT.PUT_LINE('l_auction_header_id' || l_auction_header_id);
3620 -- Construct Matrix for the awarded bids
3621 l_matrix_index := l_matrix_index + 1;
3622 l_award_lines(l_matrix_index).bid_number := l_reco_awards_rec.bid_number;
3623 l_award_lines(l_matrix_index).line_number := l_reco_awards_rec.line_number;
3624 l_award_lines(l_matrix_index).award_status := 'AWARDED';
3625 l_award_lines(l_matrix_index).award_quantity := l_reco_awards_rec.award_quantity;
3626 l_award_lines(l_matrix_index).award_date := l_award_date;
3627 l_award_lines(l_matrix_index).group_type := l_reco_awards_rec.group_type;
3628 l_award_lines(l_matrix_index).award_shipment_number := l_reco_awards_rec.award_shipment_number;
3629 END LOOP;
3630
3631 clear_awards_recommendation (l_auction_header_id, l_award_date, p_auctioneer_id);
3632 update_bid_item_prices(l_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
3633 /* FPK: CPA p_neg_has_lines parameter hardcoded to 'Y' as save_award_recommendation
3634 procedure will only be called if negotiation has lines */
3635 update_bid_headers(l_auction_header_id, p_auctioneer_id, t_emptytbl, 'Y');
3636 update_auction_item_prices(l_auction_header_id,null, l_award_date, p_auctioneer_id, p_mode);
3637 /* FPK: CPA p_neg_has_lines parameter hardcoded to 'Y' as save_award_recommendation
3638 procedure will only be called if negotiation has lines */
3639 update_auction_headers(l_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
3640
3641 bulk_update_pon_acceptances(
3642 l_auction_header_id,
3643 null, null, null,
3644 l_award_date, p_auctioneer_id, p_mode);
3645 --
3646 /* check if the auction has been modified by some other user
3647 If it has been modified, status returns failure
3648 else this is the only user modifying hte auction
3649 changes are committed to the database in the middle tier
3650 */
3651 IF (is_auction_not_updated (l_auction_header_id, p_last_update_date)) THEN
3652 x_status := 'SUCCESS';
3653 -- update the last update date
3654 UPDATE PON_Auction_HEADERS_all
3655 SET last_update_date = SYSDATE
3656 WHERE auction_header_id = l_auction_header_id;
3657 --
3658 ELSE
3659 x_status := 'FAILURE';
3660 END IF;
3661 --
3662 END save_award_recommendation;
3663
3664
3665 --
3666 --
3667 ----------------------------------------------------------------
3668 -- handles copying of a scenario
3669 ----------------------------------------------------------------
3670
3671 PROCEDURE copy_award_scenario
3672 (
3673 p_scenario_id IN NUMBER,
3674 p_user_id IN NUMBER,
3675 p_cost_scenario_flag IN VARCHAR2,
3676 x_cost_scenario_id OUT NOCOPY NUMBER,
3677 x_status OUT NOCOPY VARCHAR2
3678 )
3679 IS
3680
3681 l_fnd_user_id NUMBER;
3682 l_next_scenario_number NUMBER;
3683 l_num_constraints NUMBER;
3684 l_num_bid_classes NUMBER;
3685 l_auction_header_id NUMBER;
3686 l_dummy_scenario_number NUMBER;
3687 l_new_scenario_id NUMBER;
3688
3689 BEGIN
3690
3691 -- Derive fnd_user_id from tp_id
3692 -- This will not fail if more than one user setup w/ same buyer.
3693 -- But, this will reurn any of the matched user id in such cases
3694 BEGIN
3695 SELECT USER_ID
3696 INTO l_fnd_user_id
3697 FROM FND_USER
3698 WHERE PERSON_PARTY_ID = p_user_id
3699 AND NVL(END_DATE,SYSDATE+1) > SYSDATE;
3700 EXCEPTION
3701 WHEN TOO_MANY_ROWS THEN
3702 IF (NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y') THEN
3703 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
3704 FND_LOG.string(log_level => FND_LOG.level_unexpected,
3705 module => 'pon.plsql.pon_award_pkg.copy_award_scenario',
3706 message => 'Multiple Users found for person_party_id:'||p_user_id);
3707 END IF;
3708 END IF;
3709
3710 SELECT USER_ID
3711 INTO l_fnd_user_id
3712 FROM FND_USER
3713 WHERE PERSON_PARTY_ID = p_user_id
3714 AND NVL(END_DATE,SYSDATE+1) > SYSDATE
3715 AND ROWNUM = 1;
3716 END;
3717
3718 -- derive auction_header_id
3719 select auction_header_id into l_auction_header_id
3720 from pon_optimize_scenarios
3721 where scenario_id = p_scenario_id;
3722
3723 -- store the next scenario id in a local variable
3724 select pon_optimize_scenarios_s.nextval
3725 into l_new_scenario_id from dual;
3726
3727 IF(p_cost_scenario_flag = 'Y') THEN
3728
3729 -- we donot display this number any place
3730 -- all we want is to have a unique combination
3731 l_next_scenario_number := l_new_scenario_id;
3732
3733 ELSE
3734 select max(scenario_number) + 1
3735 into l_next_scenario_number
3736 from pon_optimize_scenarios
3737 where auction_header_id = l_auction_header_id
3738 and (cost_scenario_flag is null or cost_scenario_flag <> 'Y');
3739
3740 END IF;
3741
3742 --first copy the scenario
3743 INSERT INTO PON_OPTIMIZE_SCENARIOS(
3744 auction_header_id,
3745 scenario_id,
3746 scenario_name,
3747 scenario_number,
3748 objective_code,
3749 status,
3750 price_type,
3751 internal_note,
3752 updated_tp_contact_id,
3753 last_tp_update_date,
3754 creation_date,
3755 created_by,
3756 last_update_date,
3757 last_updated_by,
3758 last_update_login,
3759 cost_scenario_flag,
3760 parent_scenario_id,
3761 constraint_priority_type)
3762 SELECT auction_header_id,
3763 l_new_scenario_id,
3764 scenario_name,
3765 l_next_scenario_number,
3766 objective_code,
3767 'NOT_RUN',
3768 price_type,
3769 internal_note,
3770 p_user_id,
3771 sysdate,
3772 sysdate,
3773 l_fnd_user_id,
3774 sysdate,
3775 l_fnd_user_id,
3776 l_fnd_user_id,
3777 p_cost_scenario_flag,
3778 decode(p_cost_scenario_flag, 'Y', p_scenario_id, to_number(null)),
3779 nvl(constraint_priority_type, 'MANDATORY')
3780 FROM pon_optimize_scenarios
3781 WHERE scenario_id = p_scenario_id;
3782
3783 -- copy the constraints
3784
3785 -- make sure there is at least 1 row to prevent no data found exception
3786 select count(*) into l_num_constraints
3787 from pon_optimize_constraints
3788 where scenario_id = p_scenario_id;
3789
3790 IF (l_num_constraints > 0) THEN
3791
3792 INSERT INTO PON_OPTIMIZE_CONSTRAINTS(
3793 scenario_id,
3794 sequence_number,
3795 auction_header_id,
3796 constraint_type,
3797 line_number,
3798 min_amount,
3799 max_amount,
3800 amount_type,
3801 min_quantity,
3802 max_quantity,
3803 quantity_cutoff,
3804 price_cutoff,
3805 split_award_flag,
3806 integral_qty_award_flag,
3807 excluded_flag,
3808 from_date,
3809 to_date,
3810 min_score,
3811 supp_classification,
3812 attr_sequence_number,
3813 attr_group_name,
3814 trading_partner_id,
3815 trading_partner_contact_id,
3816 vendor_site_id,
3817 creation_date,
3818 created_by,
3819 last_update_date,
3820 last_updated_by,
3821 last_update_login,
3822 MIN_MAX_AMOUNT_PRIORITY
3823 ,MIN_MAX_AMOUNT_COST
3824 ,MIN_MAX_AMOUNT_INFEAS_FLAG
3825 ,MIN_MAX_QUANTITY_PRIORITY
3826 ,MIN_MAX_QUANTITY_COST
3827 ,MIN_MAX_QUANTITY_INFEAS_FLAG
3828 ,QUANTITY_CUTOFF_PRIORITY
3829 ,QUANTITY_CUTOFF_COST
3830 ,QUANTITY_CUTOFF_INFEAS_FLAG
3831 ,PRICE_CUTOFF_PRIORITY
3832 ,PRICE_CUTOFF_COST
3833 ,PRICE_CUTOFF_INFEAS_FLAG
3834 ,SPLIT_AWARD_PRIORITY
3835 ,SPLIT_AWARD_INFEAS_FLAG
3836 ,SPLIT_AWARD_COST
3837 ,INTEGRAL_QTY_AWARD_PRIORITY
3838 ,INTEGRAL_QTY_AWARD_INFEAS_FLAG
3839 ,INTEGRAL_QTY_AWARD_COST
3840 ,EXCLUDED_SUPPLIER_PRIORITY
3841 ,EXCLUDED_SUPPLIER_INFEAS_FLAG
3842 ,EXCLUDED_SUPPLIER_COST
3843 ,PROMISED_DATE_PRIORITY
3844 ,PROMISED_DATE_COST
3845 ,PROMISED_DATE_INFEAS_FLAG
3846 ,MIN_SCORE_PRIORITY
3847 ,MIN_SCORE_COST
3848 ,MIN_SCORE_INFEAS_FLAG)
3849 SELECT l_new_scenario_id,
3850 sequence_number,
3851 auction_header_id,
3852 constraint_type,
3853 line_number,
3854 min_amount,
3855 max_amount,
3856 amount_type,
3857 min_quantity,
3858 max_quantity,
3859 quantity_cutoff,
3860 price_cutoff,
3861 split_award_flag,
3862 integral_qty_award_flag,
3863 excluded_flag,
3864 from_date,
3865 to_date,
3866 min_score,
3867 supp_classification,
3868 attr_sequence_number,
3869 attr_group_name,
3870 trading_partner_id,
3871 trading_partner_contact_id,
3872 vendor_site_id,
3873 sysdate,
3874 l_fnd_user_id,
3875 sysdate,
3876 l_fnd_user_id,
3877 l_fnd_user_id,
3878 MIN_MAX_AMOUNT_PRIORITY
3879 ,TO_NUMBER(NULL)
3880 ,TO_CHAR(NULL)
3881 ,MIN_MAX_QUANTITY_PRIORITY
3882 ,TO_NUMBER(NULL)
3883 ,TO_CHAR(NULL)
3884 ,QUANTITY_CUTOFF_PRIORITY
3885 ,TO_NUMBER(NULL)
3886 ,TO_CHAR(NULL)
3887 ,PRICE_CUTOFF_PRIORITY
3888 ,TO_NUMBER(NULL)
3889 ,TO_CHAR(NULL)
3890 ,SPLIT_AWARD_PRIORITY
3891 ,TO_NUMBER(NULL)
3892 ,TO_CHAR(NULL)
3893 ,INTEGRAL_QTY_AWARD_PRIORITY
3894 ,TO_NUMBER(NULL)
3895 ,TO_CHAR(NULL)
3896 ,EXCLUDED_SUPPLIER_PRIORITY
3897 ,TO_NUMBER(NULL)
3898 ,TO_CHAR(NULL)
3899 ,PROMISED_DATE_PRIORITY
3900 ,TO_NUMBER(NULL)
3901 ,TO_CHAR(NULL)
3902 ,MIN_SCORE_PRIORITY
3903 ,TO_NUMBER(NULL)
3904 ,TO_CHAR(NULL)
3905 FROM pon_optimize_constraints
3906 WHERE scenario_id = p_scenario_id;
3907
3908 END IF;
3909
3910 -- copy the bid class information
3911 -- make sure a row exists first to prevent no data found exception
3912 select count(*) into l_num_bid_classes
3913 from pon_optimize_bid_class
3914 where scenario_id = p_scenario_id;
3915
3916 IF (l_num_bid_classes > 0) THEN
3917 INSERT INTO PON_OPTIMIZE_BID_CLASS(
3918 scenario_id,
3919 sequence_number,
3920 bid_number,
3921 creation_date,
3922 created_by,
3923 last_update_date,
3924 last_updated_by,
3925 last_update_login
3926 )
3927 SELECT l_new_scenario_id,
3928 sequence_number,
3929 bid_number,
3930 sysdate,
3931 l_fnd_user_id,
3932 sysdate,
3933 l_fnd_user_id,
3934 l_fnd_user_id
3935 FROM pon_optimize_bid_class
3936 WHERE scenario_id = p_scenario_id;
3937 END IF;
3938
3939 x_status := 'SUCCESS';
3940
3941 IF (p_cost_scenario_flag = 'Y') THEN
3942
3943 -- populate this value only when we are
3944 -- copying a scenario to do cost of constriant
3945 -- calculation
3946
3947 x_cost_scenario_id := l_new_scenario_id;
3948 ELSE
3949
3950 -- else, set it to some dummy value
3951
3952 x_cost_scenario_id := -9999;
3953
3954 END IF;
3955
3956
3957 EXCEPTION
3958 when others then
3959 x_status := 'FAILURE';
3960 raise;
3961 END copy_award_scenario;
3962
3963
3964
3965 PROCEDURE batch_award_spreadsheet
3966 (
3967 p_auction_header_id IN NUMBER,
3968 p_mode IN VARCHAR2,
3969 p_auctioneer_id IN NUMBER,
3970 p_last_update_date IN DATE,
3971 x_status OUT NOCOPY VARCHAR2
3972 )
3973 IS
3974
3975 l_award_date DATE;
3976
3977 BEGIN
3978 l_award_date := SYSDATE;
3979
3980 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
3981 --
3982 /* check if the auction has been modified by some other user
3983 If it has been modified, status returns failure
3984 else this is the only user modifying the auction
3985 */
3986
3987 IF (is_auction_not_updated (p_auction_header_id, p_last_update_date)) THEN
3988 x_status := 'SUCCESS';
3989 -- update the last update date
3990 UPDATE pon_Auction_headers_all
3991 SET last_update_date = SYSDATE
3992 WHERE auction_header_id = p_auction_header_id;
3993 --
3994 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3995 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.BATCH_AWARD_SPREADSHEET.AUCTION_ID:'
3996 || p_auction_header_id,'SUCCEEDED.');
3997 END IF;
3998 --
3999 ELSE
4000 x_status := 'FAILURE';
4001 --
4002 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4003 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.BATCH_AWARD_SPREADSHEET.AUCTION_ID:'
4004 || p_auction_header_id,'FAILED.');
4005 END IF;
4006 --
4007 END IF;
4008
4009
4010 END BATCH_AWARD_SPREADSHEET;
4011 --
4012 --
4013 ----------------------------------------------------------------
4014 -- procedure added by snatu on 08/15/03
4015 -- handles awarding in FPJ for awarding through spreadsheet
4016 ----------------------------------------------------------------
4017 --
4018 --
4019 PROCEDURE save_award_spreadsheet
4020 (
4021 p_batch_id IN NUMBER,
4022 p_auction_header_id IN NUMBER,
4023 p_mode IN VARCHAR2,
4024 p_auctioneer_id IN NUMBER,
4025 p_last_update_date IN DATE,
4026 p_batch_enabled IN VARCHAR2,
4027 p_is_xml_upload IN VARCHAR2,
4028 x_status OUT NOCOPY VARCHAR2
4029 )
4030 IS
4031 CURSOR c_spsheet_awards (c_batch_id NUMBER, c_auction_header_id NUMBER, c_is_xml_upload VARCHAR2) IS
4032 --Query retrives rows ordered in way exported in spreadhsheet
4033 SELECT
4034 aii.auction_header_id,
4035 aii.auction_line_number,
4036 aii.bid_number,
4037 DECODE (nvl(aii.award_status,'N'),'N',null,
4038 DECODE (ai.ORDER_TYPE_LOOKUP_CODE,
4039 'QUANTITY', aii.award_quantity,
4040 'RATE' , decode (ai.purchase_basis , 'TEMP LABOR' , aii.award_quantity ,1) ,
4041 1) )award_quantity,
4042 decode (nvl(aii.award_status,'N'),'Y', g_AWARD_OUTCOME_WIN,
4043 g_AWARD_OUTCOME_LOSE) award_outcome,
4044 aii.awardreject_reason,
4045 ai.group_type,
4046 aii.award_shipment_number
4047 FROM pon_award_items_interface aii,
4048 pon_auction_item_prices_all ai,
4049 pon_bid_item_prices bi,
4050 pon_auction_headers_all pah
4051 WHERE
4052 aii.batch_id = c_batch_id
4053 AND aii.auction_header_id = c_auction_header_id
4054 AND aii.auction_header_id = ai.auction_header_id
4055 AND aii.AUCTION_LINE_NUMBER = ai.LINE_NUMBER
4056 AND bi.bid_number = aii.bid_number
4057 AND bi.line_number = aii.AUCTION_LINE_NUMBER
4058 AND pah.auction_header_id = aii.auction_header_id
4059 ORDER BY
4060 ai.disp_line_number asc,
4061 decode(c_is_xml_upload, 'Y', decode(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', decode(nvl(bi.PRICE,0), 0, 0, nvl(bi.TOTAL_WEIGHTED_SCORE,0)/bi.PRICE), decode(bi.PRICE,0,0, 1/bi.PRICE)), decode(bi.PRICE, 0, 0, 1/bi.PRICE)) desc, bi.PUBLISH_DATE asc;
4062 l_spsheet_awards_rec c_spsheet_awards%ROWTYPE;
4063 --
4064 l_award_lines t_award_lines;
4065 l_matrix_index NUMBER;
4066 l_size NUMBER;
4067 l_current_bid_number NUMBER;
4068 l_current_update_date DATE;
4069 l_award_date DATE;
4070 l_curr_line_num NUMBER;
4071 --
4072 TYPE BID_LIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4073 l_bid_list BID_LIST_TYPE;
4074 TYPE ITEM_LIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4075 l_item_list ITEM_LIST_TYPE;
4076 TYPE ACCEPT_LIST_TYPE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
4077 l_accept_list ACCEPT_LIST_TYPE;
4078 TYPE REJECT_LIST_TYPE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
4079 l_reject_list REJECT_LIST_TYPE;
4080 --
4081 BEGIN
4082
4083 l_matrix_index := 0;
4084 l_award_date := SYSDATE;
4085 l_curr_line_num := NULL;
4086 --
4087
4088 -- Get distinct lines awarded/rejected
4089 SELECT DISTINCT auction_line_number BULK COLLECT INTO l_item_list
4090 FROM pon_award_items_interface
4091 WHERE batch_id = p_batch_id
4092 AND auction_header_id = p_auction_header_id;
4093
4094 -- Get distinct bids awarded/rejected
4095 SELECT DISTINCT bid_number BULK COLLECT INTO l_bid_list
4096 FROM pon_award_items_interface
4097 WHERE batch_id = p_batch_id
4098 AND auction_header_id = p_auction_header_id;
4099 --
4100 -- Need to set award quantity and award_status and award_date
4101
4102 OPEN c_spsheet_awards (p_batch_id, p_auction_header_id, p_is_xml_upload);
4103
4104 LOOP --{
4105 fetch c_spsheet_awards into l_spsheet_awards_rec;
4106 EXIT WHEN c_spsheet_awards%NOTFOUND;
4107 -- Construct Matrix in any case (WIN/LOSR)
4108 l_matrix_index := l_matrix_index +1;
4109 l_award_lines(l_matrix_index).bid_number := l_spsheet_awards_rec.bid_number;
4110 l_award_lines(l_matrix_index).line_number := l_spsheet_awards_rec.auction_line_number;
4111 l_award_lines(l_matrix_index).award_status := get_award_status(l_spsheet_awards_rec.award_outcome);
4112 l_award_lines(l_matrix_index).award_date := l_award_date;
4113 l_award_lines(l_matrix_index).award_quantity := l_spsheet_awards_rec.award_quantity;
4114 l_award_lines(l_matrix_index).group_type := l_spsheet_awards_rec.group_type;
4115 l_award_lines(l_matrix_index).award_shipment_number := l_spsheet_awards_rec.award_shipment_number;
4116
4117 -- Update notes per line for awarded / rejected suppleirs
4118 IF (l_curr_line_num IS NULL ) THEN
4119 l_curr_line_num := l_spsheet_awards_rec.auction_line_number;
4120 l_accept_list(l_curr_line_num) := NULL;
4121 l_reject_list(l_curr_line_num) := NULL;
4122 END IF;
4123 IF (l_curr_line_num <> l_spsheet_awards_rec.auction_line_number ) THEN
4124 -- new line ; update curr line
4125 l_curr_line_num := l_spsheet_awards_rec.auction_line_number;
4126 l_accept_list(l_curr_line_num) := NULL;
4127 l_reject_list(l_curr_line_num) := NULL;
4128 END IF ;
4129 --
4130 IF (l_accept_list(l_curr_line_num) IS NULL
4131 AND l_award_lines(l_matrix_index).award_status = 'AWARDED') THEN
4132 l_accept_list(l_curr_line_num) := l_spsheet_awards_rec.awardreject_reason;
4133 END IF ;
4134 --
4135 IF (l_reject_list(l_curr_line_num) IS NULL
4136 AND l_award_lines(l_matrix_index).award_status = 'REJECTED') THEN
4137 l_reject_list(l_curr_line_num) := l_spsheet_awards_rec.awardreject_reason;
4138 END IF ;
4139 --
4140 END LOOP;
4141
4142 --}
4143
4144 --
4145 -- this procedure updates all the bid lines one-by-one
4146
4147 update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
4148
4149 l_size := l_bid_list.count;
4150
4151 FOR l_index IN 1..l_size LOOP
4152
4153 -- this procedure updates the award_status
4154 -- for all the bids at the bid-header level
4155 -- we don't need to invoke this over here -> this should be invoked after all
4156 -- batches are exhausted
4157
4158 update_single_bid_header(l_bid_list(l_index),p_auctioneer_id);
4159
4160 END LOOP;
4161
4162 l_size := l_item_list.count;
4163
4164 FOR l_index IN 1..l_size LOOP
4165
4166 update_auction_item_prices(p_auction_header_id, l_item_list(l_index), l_award_date, p_auctioneer_id, p_mode);
4167
4168 -- update acceptances per auction line
4169 bulk_update_pon_acceptances (
4170 p_auction_header_id, l_item_list(l_index),
4171 l_accept_list(l_item_list(l_index)), l_reject_list(l_item_list(l_index)),
4172 l_award_date, p_auctioneer_id, p_mode);
4173
4174 END LOOP;
4175
4176 /* FPK: CPA p_neg_has_lines parameter hardcoded to 'Y' as save_award_spreadsheet
4177 procedure will only be called if negotiation has lines */
4178
4179 -- this procedure loops over all the lines to determine the auction-header-level
4180 -- award-status etc.
4181
4182 if(p_batch_enabled = 'N') then --{
4183
4184 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
4185 --
4186 /* check if the auction has been modified by some other user
4187 If it has been modified, status returns failure
4188 else this is the only user modifying the auction
4189 */
4190
4191 IF (is_auction_not_updated (p_auction_header_id, p_last_update_date)) THEN
4192 x_status := 'SUCCESS';
4193 -- update the last update date
4194 UPDATE pon_Auction_headers_all
4195 SET last_update_date = SYSDATE
4196 WHERE auction_header_id = p_auction_header_id;
4197 --
4198 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4199 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.SAVE_AWARD_SPREADSHEET.AUCTION_ID:'
4200 || p_auction_header_id,'SUCCEEDED.');
4201 END IF;
4202 --
4203 ELSE
4204 x_status := 'FAILURE';
4205 --
4206 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4207 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.SAVE_AWARD_SPREADSHEET.AUCTION_ID:'
4208 || p_auction_header_id,'FAILED.');
4209 END IF;
4210 --
4211 END IF;
4212
4213 else
4214
4215 -- delete interface data
4216 delete from pon_award_items_interface
4217 where batch_id = p_batch_id;
4218
4219 x_status := 'SUCCESS';
4220
4221 end if; --}
4222
4223 --
4224 END save_award_spreadsheet;
4225 --
4226 --
4227 FUNCTION is_auction_not_updated (
4228 p_auction_header_id NUMBER,
4229 p_last_update_date DATE
4230 ) RETURN BOOLEAN
4231 IS
4232 l_current_update_date DATE;
4233 BEGIN
4234 SELECT last_update_date INTO l_current_update_date
4235 FROM pon_auction_headers_all
4236 WHERE auction_header_id = p_auction_header_id;
4237 IF (l_current_update_date = p_last_update_date) THEN
4238 RETURN TRUE;
4239 ELSE
4240 RETURN FALSE;
4241 END IF;
4242 END is_auction_not_updated;
4243 --
4244 PROCEDURE toggle_shortlisting
4245 ( p_user_id IN NUMBER
4246 , p_bid_number IN NUMBER
4247 , p_event IN VARCHAR2
4248 )
4249 IS
4250
4251 l_person_id NUMBER;
4252
4253 BEGIN
4254 -- This will never fail even if more than one user setup w/ same buyer
4255 -- as always there will be one record for an user_id in fnd_user
4256 SELECT PERSON_PARTY_ID INTO l_person_id
4257 FROM FND_USER
4258 WHERE user_id = p_user_id;
4259
4260 IF (p_event = 'NOT_SHORTLIST') THEN
4261 UPDATE PON_BID_HEADERS
4262 SET SHORTLIST_FLAG = 'N'
4263 , LAST_UPDATE_DATE = SYSDATE
4264 , LAST_UPDATED_BY = p_user_id
4265 , SHORTLIST_TPC_ID = l_person_id
4266 WHERE BID_NUMBER = p_bid_number;
4267 ELSIF (p_event = 'SHORTLIST') THEN
4268 UPDATE PON_BID_HEADERS
4269 SET SHORTLIST_FLAG = 'Y'
4270 , LAST_UPDATE_DATE = SYSDATE
4271 , LAST_UPDATED_BY = p_user_id
4272 , SHORTLIST_TPC_ID = l_person_id
4273 WHERE BID_NUMBER = p_bid_number;
4274 END IF;
4275
4276 EXCEPTION
4277 when others then
4278 raise;
4279
4280 END toggle_shortlisting;
4281
4282
4283 -- Returns the award amount for a negotiation.
4284 FUNCTION get_award_amount(p_auction_header_id IN NUMBER) RETURN NUMBER IS
4285 l_award_amount NUMBER;
4286 BEGIN
4287
4288 BEGIN
4289 SELECT SUM(DECODE(ah.contract_type, 'STANDARD', bh.total_award_amount, bh.po_agreed_amount * (1/nvl(bh.rate, 1))))
4290 INTO l_award_amount
4291 FROM
4292 pon_auction_headers_all ah,
4293 pon_bid_headers bh
4294 WHERE
4295 ah.auction_header_id = p_auction_header_id
4296 AND ah.auction_header_id = bh.auction_header_id
4297 AND bh.award_status in ('AWARDED', 'PARTIAL');
4298 EXCEPTION
4299 WHEN no_data_found THEN
4300 NULL;
4301 END;
4302
4303 RETURN l_award_amount;
4304
4305 END get_award_amount;
4306 --
4307 --
4308 PROCEDURE award_bi_subline (
4309 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4310 p_bid_number IN pon_bid_headers.bid_number%TYPE,
4311 p_parent_line_number IN pon_bid_item_prices.line_number%TYPE,
4312 p_award_status IN pon_bid_item_prices.award_status%TYPE,
4313 p_award_date IN pon_bid_item_prices.award_date%TYPE,
4314 p_auctioneer_id pon_bid_item_prices.LAST_UPDATED_BY%TYPE)
4315 IS
4316 CURSOR c_sublines (c_auction_header_id pon_bid_headers.auction_header_id%TYPE,
4317 c_bid_number pon_bid_headers.bid_number%TYPE,
4318 c_parent_line_number pon_bid_item_prices.line_number%TYPE) IS
4319 --Query retrives sublines for the given parent line
4320 SELECT
4321 bi.line_number,
4322 DECODE (p_award_status, 'AWARDED',decode (aii.group_type,
4323 'LOT_LINE', null, decode (aii.order_type_lookup_code,
4324 'FIXED PRICE', 1,
4325 'AMOUNT', 1,
4326 'RATE', decode (aii.purchase_basis, 'TEMP LABOR', bi.quantity, 1), bi.quantity )), null) award_quantity
4327 FROM pon_bid_item_prices bi, pon_auction_item_prices_all aii
4328 WHERE
4329 bi.bid_number = c_bid_number
4330 AND bi.line_number IN (SELECT ai.line_number
4331 FROM pon_auction_item_prices_all ai
4332 WHERE ai.parent_line_number = c_parent_line_number
4333 AND ai.auction_header_id = bi.auction_header_id )
4334 AND aii.auction_header_id = bi.auction_header_id
4335 AND aii.line_number = bi.line_number
4336 AND Nvl(aii.award_status, 'NO') <> 'COMPLETED';
4337
4338 l_sublines_rec c_sublines%ROWTYPE;
4339
4340 BEGIN
4341 OPEN c_sublines (p_auction_header_id, p_bid_number, p_parent_line_number);
4342 LOOP
4343 FETCH c_sublines INTO l_sublines_rec;
4344 EXIT WHEN c_sublines%NOTFOUND;
4345 -- update the child lines
4346 update_single_bid_item_prices
4347 (
4348 p_bid_number,
4349 l_sublines_rec.line_number,
4350 p_award_status,
4351 l_sublines_rec.award_quantity,
4352 p_award_date,
4353 p_auctioneer_id
4354 );
4355 END LOOP;
4356 END award_bi_subline;
4357 --
4358 --
4359 ----------------------------------------------------------------
4360 -- gets the parent line
4361 --and sets the award status of parent line by querying up the child lines
4362 ----------------------------------------------------------------
4363 PROCEDURE update_bi_group_award (
4364 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4365 p_bid_number IN pon_bid_headers.bid_number%TYPE,
4366 p_parent_line_number IN pon_auction_item_prices_all.parent_line_number%TYPE,
4367 p_award_date IN pon_bid_item_prices.award_date%TYPE,
4368 p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE )
4369 IS
4370 l_total_lines NUMBER;
4371 l_awarded_lines NUMBER;
4372 l_rejected_lines NUMBER;
4373 l_award_status pon_bid_item_prices.award_status%TYPE;
4374 BEGIN
4375 --get total, awarded/ rejected lines
4376 --
4377 SELECT count (*) ,
4378 sum(decode(bi.award_status,'AWARDED',1,0)) ,
4379 sum(decode(bi.award_status,'REJECTED',1,0))
4380 INTO l_total_lines,
4381 l_awarded_lines,
4382 l_rejected_lines
4383 FROM pon_auction_item_prices_all ai, pon_bid_item_prices bi
4384 WHERE ai.parent_line_number = p_parent_line_number
4385 AND ai.auction_header_id = p_auction_header_id
4386 and ai.auction_header_id = bi.auction_header_id(+)
4387 and bi.bid_number = p_bid_number
4388 and bi.line_number = ai.line_number;
4389
4390 IF (l_total_lines = l_awarded_lines) THEN
4391 l_award_status := 'AWARDED' ;
4392 ELSIF (l_awarded_lines > 0) THEN
4393 l_award_status := 'PARTIAL';
4394 ELSIF (l_total_lines = l_rejected_lines) THEN
4395 l_award_status := 'REJECTED';
4396 ELSE
4397 l_award_status := null;
4398 END IF;
4399 --
4400 update_single_bid_item_prices
4401 (
4402 p_bid_number,
4403 p_parent_line_number,
4404 l_award_status,
4405 null,
4406 p_award_date,
4407 p_auctioneer_id
4408 );
4409
4410 END update_bi_group_award;
4411 --
4412 --
4413 PROCEDURE update_ai_group_award (
4414 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4415 p_line_number IN pon_bid_item_prices.line_number%TYPE,
4416 p_award_date IN pon_bid_item_prices.award_date%TYPE,
4417 p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE)
4418 IS
4419 l_total_lines NUMBER;
4420 l_awarded_lines NUMBER;
4421 l_parent_line_number NUMBER;
4422 l_award_status pon_auction_item_prices_all.award_status%TYPE;
4423 BEGIN
4424 --get total and awarded lines
4425 SELECT parent_line_number INTO l_parent_line_number FROM pon_auction_item_prices_all
4426 WHERE auction_header_id = p_auction_header_id AND line_number = p_line_number;
4427 --
4428 -- all the group lines have bids if a single group line has a bid
4429 -- hence all group lines are awardable and hence considered for the count
4430 SELECT COUNT(*) INTO l_total_lines
4431 FROM pon_auction_item_prices_all ai
4432 WHERE parent_line_number = l_parent_line_number
4433 and auction_header_id = p_auction_header_id
4434 AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
4435 --
4436 select COUNT(*) INTO l_awarded_lines
4437 FROM pon_auction_item_prices_all
4438 WHERE parent_line_number = l_parent_line_number
4439 AND award_status IN ('AWARDED', 'COMPLETED') --Staggered Awards project
4440 and auction_header_id = p_auction_header_id;
4441
4442 IF (l_total_lines = l_awarded_lines) THEN
4443 l_award_status := 'AWARDED' ;
4444 ELSIF (l_awarded_lines > 0) THEN
4445 l_award_status := 'PARTIAL';
4446 ELSE
4447 l_award_status := null;
4448 END IF;
4449 --
4450 UPDATE pon_auction_item_prices_all
4451 SET award_status = l_award_status,
4452 awarded_quantity = null,
4453 award_mode = null,
4454 last_update_date = p_award_date,
4455 last_updated_by = p_auctioneer_id
4456 WHERE auction_header_id = p_auction_header_id
4457 AND line_number = l_parent_line_number;
4458
4459 END update_ai_group_award;
4460 --
4461 --
4462
4463
4464 PROCEDURE get_award_totals(
4465 p_auction_header_id in number,
4466 p_award_total out nocopy number,
4467 p_current_total out nocopy number,
4468 p_savings_total out nocopy number,
4469 p_savings_percent out nocopy number)
4470 IS
4471 l_current_total_temp NUMBER;
4472 l_unsol_lines_award_total NUMBER;
4473 l_sol_lines_award_total NUMBER;
4474 BEGIN
4475
4476 p_award_total := 0;
4477 p_current_total := 0;
4478 p_savings_total := 0;
4479 p_savings_percent := 0;
4480 l_current_total_temp := 0;
4481 l_unsol_lines_award_total := 0;
4482 l_sol_lines_award_total := 0;
4483
4484 SELECT sum(nvl2(PAIP.current_price,
4485 PAIP.current_price * nvl(PAIP.awarded_quantity, 0),
4486 sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))))
4487 INTO p_current_total
4488 FROM pon_bid_item_prices PBIP,
4489 pon_bid_headers PBH,
4490 pon_auction_item_prices_all PAIP
4491 WHERE PAIP.auction_header_id = p_auction_header_id
4492 AND PAIP.auction_header_id = PBIP.auction_header_id (+)
4493 AND PAIP.line_number = PBIP.line_number (+)
4494 AND PBIP.bid_number = PBH.bid_number (+)
4495 AND PBH.bid_status (+) = 'ACTIVE'
4496 AND NVL(PBH.award_status, 'NONE') IN ('PARTIAL', 'AWARDED')
4497 GROUP BY
4498 PAIP.line_number, PAIP.current_price, PAIP.awarded_quantity;
4499
4500 SELECT sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))
4501 INTO l_sol_lines_award_total
4502 FROM pon_bid_item_prices PBIP,
4503 pon_bid_headers PBH
4504 WHERE PBH.auction_header_id = p_auction_header_id
4505 AND PBIP.bid_number = PBH.bid_number (+)
4506 AND PBH.bid_status (+) = 'ACTIVE'
4507 AND NVL(PBH.award_status, 'NONE') IN ('PARTIAL', 'AWARDED')
4508 AND PBIP.AUCTION_LINE_NUMBER <> -1;
4509
4510 --Bug 16372466 fix
4511 --Donot include unsolicited lines amount when calculating the Savings Amount and Savings Percent
4512
4513 SELECT nvl(sum(bl.award_quantity * bl.price), 0)
4514 INTO l_unsol_lines_award_total
4515 FROM pon_bid_item_prices bl
4516 WHERE bl.auction_header_id = p_auction_header_id
4517 AND bl.auction_line_number = -1
4518 AND bl.award_status = 'AWARDED';
4519
4520 p_award_total := l_sol_lines_award_total + l_unsol_lines_award_total;
4521
4522 p_savings_total := p_current_total - l_sol_lines_award_total;
4523
4524 -- safety check to avoid divide-by-zero exception
4525 IF p_current_total IS NULL OR p_current_total = 0 THEN
4526 p_savings_percent := 0;
4527 ELSE
4528 p_savings_percent := (p_savings_total / p_current_total) * 100;
4529 END IF;
4530
4531 EXCEPTION
4532
4533 WHEN OTHERS THEN
4534
4535 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4536 fnd_log.string(log_level => fnd_log.level_unexpected
4537 ,module => 'pon_award_pkg.get_award_totals'
4538 ,message => 'exception occurred while calculating totals ' || SUBSTR(SQLERRM, 1, 200));
4539 END IF;
4540
4541
4542 NULL;
4543
4544
4545 END;
4546
4547
4548 FUNCTION does_bid_exist
4549 (
4550 p_scenario_id IN PON_OPTIMIZE_CONSTRAINTS.SCENARIO_ID%TYPE,
4551 p_sequence_number IN PON_OPTIMIZE_CONSTRAINTS.SEQUENCE_NUMBER%TYPE,
4552 p_bid_number IN PON_BID_HEADERS.BID_NUMBER%TYPE
4553 ) RETURN VARCHAR2
4554 IS
4555 l_bid_exists VARCHAR2(1);
4556 BEGIN
4557
4558 BEGIN
4559 SELECT 'Y'
4560 INTO l_bid_exists
4561 FROM dual
4562 WHERE EXISTS (SELECT 1
4563 FROM pon_optimize_bid_class pobc
4564 WHERE pobc.scenario_id = p_scenario_id
4565 AND pobc.sequence_number = p_sequence_number
4566 AND pobc.bid_number = p_bid_number);
4567
4568 EXCEPTION
4569 WHEN NO_DATA_FOUND THEN
4570 l_bid_exists := 'N';
4571 END;
4572 return l_bid_exists;
4573 END;
4574
4575 FUNCTION has_scored_attribute
4576 (
4577 p_auction_header_id IN PON_AUCTION_ATTRIBUTES.AUCTION_HEADER_ID%TYPE,
4578 p_line_number IN PON_AUCTION_ATTRIBUTES.LINE_NUMBER%TYPE
4579 ) RETURN VARCHAR2
4580 IS
4581 l_scored_attribute_exists VARCHAR2(1);
4582 BEGIN
4583
4584 BEGIN
4585 SELECT 'Y'
4586 INTO l_scored_attribute_exists
4587 FROM dual
4588 WHERE EXISTS (SELECT 1
4589 FROM pon_attribute_scores pas
4590 WHERE pas.auction_header_id = p_auction_header_id
4591 AND pas.line_number = p_line_number);
4592
4593 EXCEPTION
4594 WHEN NO_DATA_FOUND THEN
4595 l_scored_attribute_exists := 'N';
4596 END;
4597 return l_scored_attribute_exists;
4598 END;
4599
4600 PROCEDURE preprocess_cost_of_constraint
4601 (
4602 p_scenario_id IN NUMBER,
4603 p_user_id IN NUMBER,
4604 p_cost_constraint_flag IN VARCHAR2,
4605 p_constraint_type IN VARCHAR2,
4606 p_internal_type IN VARCHAR2,
4607 p_line_number IN NUMBER,
4608 p_sequence_number IN NUMBER,
4609 x_cost_scenario_id OUT NOCOPY NUMBER,
4610 x_status OUT NOCOPY VARCHAR2
4611 )
4612 IS
4613
4614 l_new_scenario_id NUMBER;
4615 l_status VARCHAR2(10);
4616 l_order_type_lookup_code PON_AUCTION_ITEM_PRICES_ALL.order_type_lookup_code%TYPE;
4617 l_auction_qty NUMBER;
4618 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
4619 l_module VARCHAR2(200);
4620 l_priority VARCHAR2(30);
4621 BEGIN
4622
4623 l_module := 'pon.plsql.PON_AWARD_PKG.preprocess_cost_of_constraint';
4624 l_priority := '1_CRITICAL';
4625 -- basic initialization
4626
4627 l_new_scenario_id := -9999;
4628
4629 IF (g_debug_mode = 'Y') THEN
4630 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4631 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Entering procedure with p_scenario_id: ' || p_scenario_id );
4632 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module,' p_cost_constraint_flag : '|| p_cost_constraint_flag || ' ,p_constraint_type : '|| p_constraint_type || ' ,p_internal_type : '|| p_internal_type);
4633 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module,' p_line_number : '|| p_line_number || ' ,p_sequence_number : ' || p_sequence_number );
4634 END IF;
4635 END IF;
4636
4637 copy_award_scenario(p_scenario_id => p_scenario_id,
4638 p_user_id => p_user_id,
4639 p_cost_scenario_flag => p_cost_constraint_flag,
4640 x_cost_scenario_id => l_new_scenario_id,
4641 x_status => l_status);
4642
4643
4644 IF (g_debug_mode = 'Y') THEN
4645 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4646 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'After copy_award_scenario is called. New scenario id is' || l_new_scenario_id ||' ; Status ' ||l_status);
4647 END IF;
4648 END IF;
4649
4650 IF (l_status <> 'FAILURE') THEN
4651
4652 -- these initial conditions are applicable to those
4653 -- scenarios where multiple constraints are saved on the
4654 -- same row of pon_optimize_constraints table
4655
4656 IF (p_constraint_type = 'LINE_CONST') THEN --{
4657
4658 IF (p_internal_type = 'LINE_SPLIT_AWARD') THEN
4659
4660 IF (g_debug_mode = 'Y') THEN
4661 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4662 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SPLIT_AWARD internal type constraint');
4663 END IF;
4664 END IF;
4665
4666 UPDATE PON_OPTIMIZE_CONSTRAINTS
4667 SET SPLIT_AWARD_FLAG = decode(SPLIT_AWARD_FLAG, 'Y', 'N', 'Y'),
4668 SPLIT_AWARD_PRIORITY = NVL2(SPLIT_AWARD_PRIORITY,l_priority,null),
4669 SPLIT_AWARD_INFEAS_FLAG = TO_CHAR(NULL),
4670 SPLIT_AWARD_COST = TO_NUMBER(NULL)
4671 WHERE SCENARIO_ID = l_new_scenario_id
4672 AND SEQUENCE_NUMBER = p_sequence_number
4673 AND CONSTRAINT_TYPE = p_constraint_type;
4674
4675 ELSIF (p_internal_type = 'LINE_INTEGER_QUANTITY') THEN
4676
4677 IF (g_debug_mode = 'Y') THEN
4678 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4679 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_INTEGER_QUANTITY internal type constraint');
4680 END IF;
4681 END IF;
4682
4683 UPDATE PON_OPTIMIZE_CONSTRAINTS
4684 SET INTEGRAL_QTY_AWARD_FLAG = decode(INTEGRAL_QTY_AWARD_FLAG, 'Y', 'N', 'Y'),
4685 INTEGRAL_QTY_AWARD_PRIORITY = NVL2(INTEGRAL_QTY_AWARD_PRIORITY,l_priority,null),
4686 INTEGRAL_QTY_AWARD_INFEAS_FLAG = TO_CHAR(NULL),
4687 INTEGRAL_QTY_AWARD_COST = TO_NUMBER(NULL)
4688 WHERE SCENARIO_ID = l_new_scenario_id
4689 AND SEQUENCE_NUMBER = p_sequence_number
4690 AND CONSTRAINT_TYPE = p_constraint_type;
4691
4692
4693 ELSIF (p_internal_type = 'LINE_AWARD_QTY') THEN
4694
4695 BEGIN
4696 SELECT pah.contract_type,
4697 pai.order_type_lookup_code,
4698 nvl(pai.quantity, 1)
4699 INTO l_contract_type,
4700 l_order_type_lookup_code,
4701 l_auction_qty
4702 FROM pon_auction_headers_all pah,
4703 pon_auction_item_prices_all pai,
4704 pon_optimize_scenarios pos
4705 WHERE pah.auction_header_id = pai.auction_header_id
4706 AND pah.auction_header_id = pos.auction_header_id
4707 AND pai.line_number = p_line_number
4708 AND pos.scenario_id = l_new_scenario_id;
4709 EXCEPTION
4710 WHEN OTHERS THEN
4711 IF (g_debug_mode = 'Y') THEN
4712 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4713 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED, l_module, 'Selecting auction info in LINE_AWARD_QTY internal type constraint if condition caused error');
4714 END IF;
4715 END IF;
4716
4717 END;
4718
4719 -- We need to set Max Qty to 1 for FIXED PRICE and AMOUNT based lines for SPO, BPA and CPA
4720 -- For SPO, We need to set Max Qty to auction qty for QUANTITY based lines
4721 -- For BPA and CPA, If auction qty is null we need to set Max Qty to 1, otherwise we should set it to auction qty
4722
4723 -- In the above sql for selecting the auction qty as we use nvl to set auction qty to 1,
4724 -- all the above conditions should be satisfied
4725
4726 IF (g_debug_mode = 'Y') THEN
4727 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4728 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_QTY internal type constraint');
4729 END IF;
4730 END IF;
4731
4732 UPDATE PON_OPTIMIZE_CONSTRAINTS
4733 SET MIN_QUANTITY = 0,
4734 MAX_QUANTITY = DECODE(l_order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, l_auction_qty),
4735 MIN_MAX_QUANTITY_PRIORITY = NVL2(MIN_MAX_QUANTITY_PRIORITY,l_priority,null),
4736 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
4737 MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
4738 WHERE SCENARIO_ID = l_new_scenario_id
4739 AND SEQUENCE_NUMBER = p_sequence_number
4740 AND CONSTRAINT_TYPE = p_constraint_type;
4741
4742 ELSIF (p_internal_type = 'LINE_AWARD_AMOUNT') THEN
4743
4744 IF (g_debug_mode = 'Y') THEN
4745 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4746 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_AMOUNT internal type constraint');
4747 END IF;
4748 END IF;
4749
4750 UPDATE PON_OPTIMIZE_CONSTRAINTS
4751 SET MIN_AMOUNT = TO_NUMBER(NULL),
4752 MAX_AMOUNT = TO_NUMBER(NULL),
4753 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
4754 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
4755 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
4756 WHERE SCENARIO_ID = l_new_scenario_id
4757 AND SEQUENCE_NUMBER = p_sequence_number
4758 AND CONSTRAINT_TYPE = p_constraint_type;
4759
4760
4761 ELSIF (p_internal_type = 'LINE_MIN_QTY') THEN
4762
4763 IF (g_debug_mode = 'Y') THEN
4764 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4765 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_QTY internal type constraint');
4766 END IF;
4767 END IF;
4768
4769 UPDATE PON_OPTIMIZE_CONSTRAINTS
4770 SET QUANTITY_CUTOFF = TO_NUMBER(NULL),
4771 QUANTITY_CUTOFF_PRIORITY = TO_CHAR(NULL),
4772 QUANTITY_CUTOFF_INFEAS_FLAG = TO_CHAR(NULL),
4773 QUANTITY_CUTOFF_COST = TO_NUMBER(NULL)
4774 WHERE SCENARIO_ID = l_new_scenario_id
4775 AND SEQUENCE_NUMBER = p_sequence_number
4776 AND CONSTRAINT_TYPE = p_constraint_type;
4777
4778
4779
4780 ELSIF (p_internal_type = 'LINE_MAX_PRICE') THEN
4781
4782 IF (g_debug_mode = 'Y') THEN
4783 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4784 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MAX_PRICE internal type constraint');
4785 END IF;
4786 END IF;
4787
4788 UPDATE PON_OPTIMIZE_CONSTRAINTS
4789 SET PRICE_CUTOFF = TO_NUMBER(NULL),
4790 PRICE_CUTOFF_PRIORITY = TO_CHAR(NULL),
4791 PRICE_CUTOFF_INFEAS_FLAG = TO_CHAR(NULL),
4792 PRICE_CUTOFF_COST = TO_NUMBER(NULL)
4793 WHERE SCENARIO_ID = l_new_scenario_id
4794 AND SEQUENCE_NUMBER = p_sequence_number
4795 AND CONSTRAINT_TYPE = p_constraint_type;
4796
4797
4798
4799 ELSIF (p_internal_type = 'LINE_MIN_SCORE') THEN
4800
4801 IF (g_debug_mode = 'Y') THEN
4802 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4803 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_SCORE internal type constraint');
4804 END IF;
4805 END IF;
4806 -- XYZ - SIMILAR CONDITION NEEDED FOR
4807 -- AwardOptConstraint.SINGLE_HEADER_ATTR_CUTOFF
4808 -- AwardOptConstraint.GROUP_HEADER_ATTR_CUTOFF
4809
4810 UPDATE PON_OPTIMIZE_CONSTRAINTS
4811 SET MIN_SCORE = TO_NUMBER(NULL),
4812 MIN_SCORE_PRIORITY = TO_CHAR(NULL),
4813 MIN_SCORE_INFEAS_FLAG = TO_CHAR(NULL),
4814 MIN_SCORE_COST = TO_NUMBER(NULL)
4815 WHERE SCENARIO_ID = l_new_scenario_id
4816 AND SEQUENCE_NUMBER = p_sequence_number
4817 AND CONSTRAINT_TYPE = p_constraint_type;
4818
4819 ELSIF (p_internal_type = 'LINE_PROMISED_DATE') THEN
4820
4821 IF (g_debug_mode = 'Y') THEN
4822 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4823 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_PROMISED_DATE internal type constraint');
4824 END IF;
4825 END IF;
4826
4827 UPDATE PON_OPTIMIZE_CONSTRAINTS
4828 SET FROM_DATE = TO_DATE(NULL),
4829 TO_DATE = TO_DATE(NULL),
4830 PROMISED_DATE_PRIORITY = TO_CHAR(NULL),
4831 PROMISED_DATE_INFEAS_FLAG = TO_CHAR(NULL),
4832 PROMISED_DATE_COST = TO_NUMBER(NULL)
4833 WHERE SCENARIO_ID = l_new_scenario_id
4834 AND SEQUENCE_NUMBER = p_sequence_number
4835 AND CONSTRAINT_TYPE = p_constraint_type;
4836
4837 END IF;
4838
4839 --}
4840
4841 ELSIF (p_constraint_type = 'SUPP_LINE_CONST') THEN --{
4842
4843 -- two constraints
4844 -- Line Number of Suppliers : same seqnum
4845 -- Line Any One Supplier : same seqnum
4846
4847 IF (p_internal_type = 'LINE_NUM_SUPP') THEN
4848
4849 IF (g_debug_mode = 'Y') THEN
4850 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4851 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_NUM_SUPP internal type constraint');
4852 END IF;
4853 END IF;
4854
4855 UPDATE PON_OPTIMIZE_CONSTRAINTS
4856 SET MIN_QUANTITY = TO_NUMBER(NULL),
4857 MAX_QUANTITY = TO_NUMBER(NULL),
4858 MIN_MAX_QUANTITY_PRIORITY = TO_CHAR(NULL),
4859 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
4860 MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
4861 WHERE SCENARIO_ID = l_new_scenario_id
4862 AND SEQUENCE_NUMBER = p_sequence_number
4863 AND CONSTRAINT_TYPE = p_constraint_type;
4864
4865
4866 ELSIF (p_internal_type = 'LINE_ANY_ONE_SUPP') THEN
4867
4868 IF (g_debug_mode = 'Y') THEN
4869 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4870 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_ANY_ONE_SUPP internal type constraint');
4871 END IF;
4872 END IF;
4873
4874 UPDATE PON_OPTIMIZE_CONSTRAINTS
4875 SET MIN_AMOUNT = TO_NUMBER(NULL),
4876 MAX_AMOUNT = TO_NUMBER(NULL),
4877 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
4878 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
4879 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
4880 WHERE SCENARIO_ID = l_new_scenario_id
4881 AND SEQUENCE_NUMBER = p_sequence_number
4882 AND CONSTRAINT_TYPE = p_constraint_type;
4883
4884
4885 --ELSE
4886
4887 --DELETE FROM PON_OPTIMIZE_CONSTRAINTS
4888 --WHERE SCENARIO_ID = p_scenario_id
4889 --AND CONSTRAINT_TYPE = p_constraint_type
4890 --AND SEQUENCE_NUMBER = p_sequence_number;
4891
4892 END IF;
4893
4894 --}
4895
4896 ELSIF (p_constraint_type = 'SUPP_BIZ_CONST') THEN --{
4897
4898 IF (g_debug_mode = 'Y') THEN
4899 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4900 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating SUPP_BIZ_CONST constraint type');
4901 END IF;
4902 END IF;
4903
4904 UPDATE PON_OPTIMIZE_CONSTRAINTS
4905 SET MIN_AMOUNT = TO_NUMBER(NULL),
4906 MAX_AMOUNT = TO_NUMBER(NULL),
4907 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
4908 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
4909 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
4910 WHERE SCENARIO_ID = l_new_scenario_id
4911 AND SEQUENCE_NUMBER = p_sequence_number
4912 AND CONSTRAINT_TYPE = p_constraint_type;
4913
4914 --}
4915
4916 ELSIF (p_constraint_type = 'BID_LINE_CONST') THEN --{
4917 IF (p_internal_type = 'LINE_SUPP_SITE_AMT') THEN
4918
4919 IF (g_debug_mode = 'Y') THEN
4920 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4921 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_AMT internal type constraint');
4922 END IF;
4923 END IF;
4924
4925 UPDATE PON_OPTIMIZE_CONSTRAINTS
4926 SET MIN_AMOUNT = TO_NUMBER(NULL),
4927 MAX_AMOUNT = TO_NUMBER(NULL),
4928 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
4929 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
4930 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
4931 WHERE SCENARIO_ID = l_new_scenario_id
4932 AND SEQUENCE_NUMBER = p_sequence_number
4933 AND CONSTRAINT_TYPE = p_constraint_type;
4934
4935 ELSIF (p_internal_type = 'LINE_SUPP_SITE_QTT') THEN
4936
4937 IF (g_debug_mode = 'Y') THEN
4938 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4939 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_QTT internal type constraint');
4940 END IF;
4941 END IF;
4942
4943 UPDATE PON_OPTIMIZE_CONSTRAINTS
4944 SET MIN_QUANTITY = TO_NUMBER(NULL),
4945 MAX_QUANTITY = TO_NUMBER(NULL),
4946 MIN_MAX_QUANTITY_PRIORITY = TO_CHAR(NULL),
4947 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
4948 MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
4949 WHERE SCENARIO_ID = l_new_scenario_id
4950 AND SEQUENCE_NUMBER = p_sequence_number
4951 AND CONSTRAINT_TYPE = p_constraint_type;
4952
4953 END IF;
4954 ELSE
4955
4956 IF (g_debug_mode = 'Y') THEN
4957 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4958 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before deleting constraint from pon_optimize_constraints');
4959 END IF;
4960 END IF;
4961 -- INCLUDES LINE-LEVEL INDIVIDUAL SUPPLIER CONSTRAINTS AS WELL
4962
4963 -- if the constraint type is either of the remaining types,
4964 -- we can delete the row from pon_optimize_constraints
4965 -- using the sequence_number
4966
4967 DELETE FROM PON_OPTIMIZE_CONSTRAINTS
4968 WHERE SCENARIO_ID = l_new_scenario_id
4969 AND CONSTRAINT_TYPE = p_constraint_type
4970 AND SEQUENCE_NUMBER = p_sequence_number;
4971
4972 END IF;
4973
4974 x_cost_scenario_id := l_new_scenario_id;
4975 x_status := 'SUCCESS';
4976
4977 ELSE
4978 x_cost_scenario_id := -9999;
4979 x_status := 'FAILURE';
4980
4981
4982 END IF;
4983
4984
4985
4986 EXCEPTION
4987 WHEN OTHERS THEN
4988 x_cost_scenario_id := -9999;
4989 x_status := 'FAILURE';
4990 RAISE;
4991
4992 END;
4993
4994
4995 PROCEDURE postprocess_cost_of_constraint
4996 (
4997 p_scenario_id IN NUMBER,
4998 p_constraint_type IN VARCHAR2,
4999 p_internal_type IN VARCHAR2,
5000 p_line_number IN NUMBER,
5001 p_sequence_number IN NUMBER,
5002 x_status OUT NOCOPY VARCHAR2
5003 )
5004
5005 IS
5006 l_num_constraints NUMBER;
5007 l_num_bid_classes NUMBER;
5008 l_num_results NUMBER;
5009 l_cost_of_constraint NUMBER;
5010 l_parent_scenario_id NUMBER;
5011 l_module VARCHAR2(250);
5012 BEGIN
5013
5014 l_module := 'pon.plsql.PON_AWARD_PKG.postprocess_cost_of_constraint';
5015 IF (g_debug_mode = 'Y') THEN
5016 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5017 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Entering procedure with p_scenario_id: ' || p_scenario_id );
5018 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module,' p_constraint_type : '|| p_constraint_type || ' ,p_internal_type : '|| p_internal_type);
5019 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module,' p_line_number : '|| p_line_number || ' ,p_sequence_number : ' || p_sequence_number );
5020 END IF;
5021 END IF;
5022
5023 -- USE PARENT_SCENARIO_ID TO JOIN TO MAIN SCENARIO
5024 -- DETERMINE THE COST AS FOLLOWS -
5025
5026 SELECT (PARENT_SCENARIO.TOTAL_AWARD_AMOUNT - COST_SCENARIO.TOTAL_AWARD_AMOUNT),
5027 PARENT_SCENARIO.SCENARIO_ID
5028 INTO L_COST_OF_CONSTRAINT,
5029 L_PARENT_SCENARIO_ID
5030 FROM PON_OPTIMIZE_SCENARIOS COST_SCENARIO,
5031 PON_OPTIMIZE_SCENARIOS PARENT_SCENARIO
5032 WHERE COST_SCENARIO.SCENARIO_ID = p_scenario_id
5033 AND PARENT_SCENARIO.SCENARIO_ID = COST_SCENARIO.PARENT_SCENARIO_ID;
5034
5035 IF (g_debug_mode = 'Y') THEN
5036 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5037 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'After selecting cost: L_COST_OF_CONSTRAINT: ' || L_COST_OF_CONSTRAINT || ' , L_PARENT_SCENARIO_ID: '||L_PARENT_SCENARIO_ID );
5038 END IF;
5039 END IF;
5040
5041 -- UPDATE THE CORRESPONDING ROW IN CONSTRAINTS TABLE
5042 -- OF PARENT SCENARIO WITH THIS COST OF CONSTRAINT
5043
5044 IF (p_constraint_type = 'LINE_CONST') THEN
5045
5046 IF (p_internal_type = 'LINE_SPLIT_AWARD') THEN
5047
5048 IF (g_debug_mode = 'Y') THEN
5049 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5050 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SPLIT_AWARD internal type constraint');
5051 END IF;
5052 END IF;
5053
5054 UPDATE PON_OPTIMIZE_CONSTRAINTS
5055 SET SPLIT_AWARD_COST = l_cost_of_constraint
5056 WHERE SCENARIO_ID = l_parent_scenario_id
5057 AND SEQUENCE_NUMBER = p_sequence_number
5058 AND CONSTRAINT_TYPE = p_constraint_type;
5059
5060 ELSIF (p_internal_type = 'LINE_INTEGER_QUANTITY') THEN
5061
5062 IF (g_debug_mode = 'Y') THEN
5063 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5064 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_INTEGER_QUANTITY internal type constraint');
5065 END IF;
5066 END IF;
5067
5068 UPDATE PON_OPTIMIZE_CONSTRAINTS
5069 SET INTEGRAL_QTY_AWARD_COST = l_cost_of_constraint
5070 WHERE SCENARIO_ID = l_parent_scenario_id
5071 AND SEQUENCE_NUMBER = p_sequence_number
5072 AND CONSTRAINT_TYPE = p_constraint_type;
5073
5074
5075 ELSIF (p_internal_type = 'LINE_AWARD_QTY') THEN
5076
5077 IF (g_debug_mode = 'Y') THEN
5078 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5079 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_QTY internal type constraint');
5080 END IF;
5081 END IF;
5082
5083 UPDATE PON_OPTIMIZE_CONSTRAINTS
5084 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
5085 WHERE SCENARIO_ID = l_parent_scenario_id
5086 AND SEQUENCE_NUMBER = p_sequence_number
5087 AND CONSTRAINT_TYPE = p_constraint_type;
5088
5089
5090 ELSIF (p_internal_type = 'LINE_AWARD_AMOUNT') THEN
5091
5092 IF (g_debug_mode = 'Y') THEN
5093 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5094 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_AMOUNT internal type constraint');
5095 END IF;
5096 END IF;
5097
5098 UPDATE PON_OPTIMIZE_CONSTRAINTS
5099 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
5100 WHERE SCENARIO_ID = l_parent_scenario_id
5101 AND SEQUENCE_NUMBER = p_sequence_number
5102 AND CONSTRAINT_TYPE = p_constraint_type;
5103
5104
5105 ELSIF (p_internal_type = 'LINE_MIN_QTY') THEN
5106
5107 IF (g_debug_mode = 'Y') THEN
5108 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5109 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_QTY internal type constraint');
5110 END IF;
5111 END IF;
5112
5113 UPDATE PON_OPTIMIZE_CONSTRAINTS
5114 SET QUANTITY_CUTOFF_COST = l_cost_of_constraint
5115 WHERE SCENARIO_ID = l_parent_scenario_id
5116 AND SEQUENCE_NUMBER = p_sequence_number
5117 AND CONSTRAINT_TYPE = p_constraint_type;
5118
5119
5120
5121 ELSIF (p_internal_type = 'LINE_MAX_PRICE') THEN
5122
5123 IF (g_debug_mode = 'Y') THEN
5124 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5125 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MAX_PRICE internal type constraint');
5126 END IF;
5127 END IF;
5128
5129 UPDATE PON_OPTIMIZE_CONSTRAINTS
5130 SET PRICE_CUTOFF_COST = l_cost_of_constraint
5131 WHERE SCENARIO_ID = l_parent_scenario_id
5132 AND SEQUENCE_NUMBER = p_sequence_number
5133 AND CONSTRAINT_TYPE = p_constraint_type;
5134
5135
5136
5137 ELSIF (p_internal_type = 'LINE_MIN_SCORE') THEN
5138
5139 IF (g_debug_mode = 'Y') THEN
5140 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5141 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_SCORE internal type constraint');
5142 END IF;
5143 END IF;
5144
5145 UPDATE PON_OPTIMIZE_CONSTRAINTS
5146 SET MIN_SCORE_COST = l_cost_of_constraint
5147 WHERE SCENARIO_ID = l_parent_scenario_id
5148 AND SEQUENCE_NUMBER = p_sequence_number
5149 AND CONSTRAINT_TYPE = p_constraint_type;
5150
5151 ELSIF (p_internal_type = 'LINE_PROMISED_DATE') THEN
5152
5153 IF (g_debug_mode = 'Y') THEN
5154 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5155 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_PROMISED_DATE internal type constraint');
5156 END IF;
5157 END IF;
5158
5159 UPDATE PON_OPTIMIZE_CONSTRAINTS
5160 SET PROMISED_DATE_COST = l_cost_of_constraint
5161 WHERE SCENARIO_ID = l_parent_scenario_id
5162 AND SEQUENCE_NUMBER = p_sequence_number
5163 AND CONSTRAINT_TYPE = p_constraint_type;
5164
5165 END IF;
5166
5167 ELSIF (p_constraint_type = 'SUPP_LINE_CONST') THEN
5168
5169 -- three constraints
5170
5171 -- Line Number of Suppliers : same seqnum
5172 -- Line Any One Supplier : same seqnum
5173 -- Line-level award amount and
5174 -- award quantity for each supplier : different seqnum
5175
5176
5177 IF (p_internal_type = 'LINE_NUM_SUPP') THEN
5178
5179 IF (g_debug_mode = 'Y') THEN
5180 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5181 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_NUM_SUPP internal type constraint');
5182 END IF;
5183 END IF;
5184
5185 UPDATE PON_OPTIMIZE_CONSTRAINTS
5186 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
5187 WHERE SCENARIO_ID = l_parent_scenario_id
5188 AND SEQUENCE_NUMBER = p_sequence_number
5189 AND CONSTRAINT_TYPE = p_constraint_type;
5190
5191
5192 ELSIF (p_internal_type = 'LINE_ANY_ONE_SUPP') THEN
5193
5194 IF (g_debug_mode = 'Y') THEN
5195 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5196 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_ANY_ONE_SUPP internal type constraint');
5197 END IF;
5198 END IF;
5199
5200 UPDATE PON_OPTIMIZE_CONSTRAINTS
5201 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
5202 WHERE SCENARIO_ID = l_parent_scenario_id
5203 AND SEQUENCE_NUMBER = p_sequence_number
5204 AND CONSTRAINT_TYPE = p_constraint_type;
5205
5206 END IF;
5207
5208 ELSIF (p_constraint_type = 'BID_LINE_CONST') THEN
5209
5210 IF (p_internal_type = 'LINE_SUPP_SITE_AMT') THEN
5211
5212 IF (g_debug_mode = 'Y') THEN
5213 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5214 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_AMT internal type constraint');
5215 END IF;
5216 END IF;
5217
5218 UPDATE PON_OPTIMIZE_CONSTRAINTS
5219 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
5220 WHERE SCENARIO_ID = l_parent_scenario_id
5221 AND SEQUENCE_NUMBER = p_sequence_number
5222 AND CONSTRAINT_TYPE = p_constraint_type;
5223
5224 ELSIF (p_internal_type = 'LINE_SUPP_SITE_QTT') THEN
5225
5226 IF (g_debug_mode = 'Y') THEN
5227 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5228 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_QTT internal type constraint');
5229 END IF;
5230 END IF;
5231
5232 UPDATE PON_OPTIMIZE_CONSTRAINTS
5233 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
5234 WHERE SCENARIO_ID = l_parent_scenario_id
5235 AND SEQUENCE_NUMBER = p_sequence_number
5236 AND CONSTRAINT_TYPE = p_constraint_type;
5237
5238 END IF;
5239 ELSIF (p_constraint_type = 'SUPP_BIZ_CONST') THEN
5240
5241 IF (g_debug_mode = 'Y') THEN
5242 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5243 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating SUPP_BIZ_CONST constraint type');
5244 END IF;
5245 END IF;
5246
5247 UPDATE PON_OPTIMIZE_CONSTRAINTS
5248 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
5249 WHERE SCENARIO_ID = l_parent_scenario_id
5250 AND SEQUENCE_NUMBER = p_sequence_number
5251 AND CONSTRAINT_TYPE = p_constraint_type;
5252
5253 ELSIF ( p_constraint_type = 'BUDGET_AMT_CONST' OR
5254 p_constraint_type = 'INCUMBENT_SUPP_CONST' OR
5255 p_constraint_type = 'ANY_SUPP_CONST' OR
5256 p_constraint_type = 'SUPP_ASL_CONST' OR
5257 p_constraint_type = 'SUPPLIER_CONST' ) THEN
5258
5259
5260 IF (g_debug_mode = 'Y') THEN
5261 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5262 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating BUDGET_AMT_CONST, INCUMBENT_SUPP_CONST, ANY_SUPP_CONST, ');
5263 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'SUPP_ASL_CONST, SUPPLIER_CONST constraint types');
5264 END IF;
5265 END IF;
5266
5267 UPDATE PON_OPTIMIZE_CONSTRAINTS
5268 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
5269 WHERE SCENARIO_ID = l_parent_scenario_id
5270 AND SEQUENCE_NUMBER = p_sequence_number
5271 AND CONSTRAINT_TYPE = p_constraint_type;
5272
5273 ELSIF (p_constraint_type = 'NUM_OF_SUPP_CONST') THEN
5274
5275 IF (g_debug_mode = 'Y') THEN
5276 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5277 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating NUM_OF_SUPP_CONST constraint type');
5278 END IF;
5279 END IF;
5280
5281 UPDATE PON_OPTIMIZE_CONSTRAINTS
5282 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
5283 WHERE SCENARIO_ID = l_parent_scenario_id
5284 AND SEQUENCE_NUMBER = p_sequence_number
5285 AND CONSTRAINT_TYPE = p_constraint_type;
5286
5287 ELSIF ( p_constraint_type = 'TOTAL_HDR_ATTR_CONST' OR
5288 p_constraint_type = 'HDR_ATTR_CONST' ) THEN
5289
5290 IF (g_debug_mode = 'Y') THEN
5291 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5292 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating TOTAL_HDR_ATTR_CONST, HDR_ATTR_CONST constraint types');
5293 END IF;
5294 END IF;
5295
5296 UPDATE PON_OPTIMIZE_CONSTRAINTS
5297 SET MIN_SCORE_COST = l_cost_of_constraint
5298 WHERE SCENARIO_ID = l_parent_scenario_id
5299 AND SEQUENCE_NUMBER = p_sequence_number
5300 AND CONSTRAINT_TYPE = p_constraint_type;
5301
5302 ELSIF (p_constraint_type = 'INCL_HOLD_SUPP_CONST') THEN
5303
5304 IF (g_debug_mode = 'Y') THEN
5305 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5306 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating INCL_HOLD_SUPP_CONST constraint type');
5307 END IF;
5308 END IF;
5309
5310 UPDATE PON_OPTIMIZE_CONSTRAINTS
5311 SET EXCLUDED_SUPPLIER_COST = l_cost_of_constraint
5312 WHERE SCENARIO_ID = l_parent_scenario_id
5313 AND SEQUENCE_NUMBER = p_sequence_number
5314 AND CONSTRAINT_TYPE = p_constraint_type;
5315
5316 ELSIF (p_constraint_type = 'NO_SPLIT_GROUP_CONST') THEN
5317
5318 IF (g_debug_mode = 'Y') THEN
5319 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5320 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating NO_SPLIT_GROUP_CONST constraint type');
5321 END IF;
5322 END IF;
5323
5324 UPDATE PON_OPTIMIZE_CONSTRAINTS
5325 SET SPLIT_AWARD_COST = l_cost_of_constraint
5326 WHERE SCENARIO_ID = l_parent_scenario_id
5327 AND SEQUENCE_NUMBER = p_sequence_number
5328 AND CONSTRAINT_TYPE = p_constraint_type;
5329 END IF;
5330
5331 IF (g_debug_mode = 'Y') THEN
5332 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5333 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before deleting the dummy scneario with p_scenario_id: ' || p_scenario_id );
5334 END IF;
5335 END IF;
5336
5337 DELETE FROM PON_OPTIMIZE_SCENARIOS WHERE SCENARIO_ID = P_SCENARIO_ID;
5338
5339 -- do some basic initialization
5340
5341 l_num_constraints := 0;
5342 l_num_bid_classes := 0;
5343 l_num_results := 0;
5344
5345
5346 -- make sure there is at least 1 row to prevent no data found exception
5347 select count(*) into l_num_constraints
5348 from pon_optimize_constraints
5349 where scenario_id = p_scenario_id;
5350
5351 IF (l_num_constraints > 0) THEN
5352 IF (g_debug_mode = 'Y') THEN
5353 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5354 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before deleting the dummy scenario constraints from PON_OPTIMIZE_CONSTRAINTS for p_scenario_id: ' || p_scenario_id );
5355 END IF;
5356 END IF;
5357
5358 DELETE FROM PON_OPTIMIZE_CONSTRAINTS WHERE SCENARIO_ID = P_SCENARIO_ID;
5359
5360 END IF;
5361
5362 select count(*) into l_num_bid_classes
5363 from pon_optimize_bid_class
5364 where scenario_id = p_scenario_id;
5365
5366 IF (l_num_bid_classes > 0) THEN
5367 IF (g_debug_mode = 'Y') THEN
5368 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5369 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before deleting the dummy scenario details from PON_OPTIMIZE_BID_CLASS for p_scenario_id: ' || p_scenario_id );
5370 END IF;
5371 END IF;
5372
5373 DELETE FROM PON_OPTIMIZE_BID_CLASS WHERE SCENARIO_ID = P_SCENARIO_ID;
5374 END IF;
5375
5376 select count(*) into l_num_results
5377 from pon_optimize_results
5378 where scenario_id = p_scenario_id;
5379
5380 IF(l_num_results > 0) THEN
5381 IF (g_debug_mode = 'Y') THEN
5382 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5383 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before deleting the dummy scenario results from PON_OPTIMIZE_RESULTS for p_scenario_id: ' || p_scenario_id );
5384 END IF;
5385 END IF;
5386
5387 DELETE FROM PON_OPTIMIZE_RESULTS WHERE SCENARIO_ID = P_SCENARIO_ID;
5388
5389 END IF;
5390
5391 X_STATUS := 'SUCCESS';
5392
5393 EXCEPTION
5394 WHEN OTHERS THEN
5395 X_STATUS := 'FAILURE';
5396 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5397 FND_LOG.STRING(log_level => fnd_log.LEVEL_UNEXPECTED
5398 ,module => l_module
5399 ,message => 'When others exception raised in postprocess_cost_of_constraint');
5400 END IF;
5401
5402 RAISE;
5403 END;
5404
5405
5406 PROCEDURE reset_cost_of_constraint
5407 (
5408 p_scenario_id IN NUMBER,
5409 x_status OUT NOCOPY VARCHAR2
5410 )
5411
5412 IS
5413
5414 l_num_constraints NUMBER;
5415
5416 BEGIN
5417
5418 -- initialize number of constraints
5419 l_num_constraints := -1;
5420
5421 -- make sure there is at least 1 row to prevent no data found exception
5422 select count(*) into l_num_constraints
5423 from pon_optimize_constraints
5424 where scenario_id = p_scenario_id;
5425
5426 IF (l_num_constraints > 0) THEN
5427
5428 UPDATE PON_OPTIMIZE_CONSTRAINTS
5429 SET
5430 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
5431 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
5432 PRICE_CUTOFF_COST = TO_NUMBER(NULL),
5433 SPLIT_AWARD_COST = TO_NUMBER(NULL),
5434 QUANTITY_CUTOFF_COST = TO_NUMBER(NULL),
5435 INTEGRAL_QTY_AWARD_COST = TO_NUMBER(NULL),
5436 EXCLUDED_SUPPLIER_COST = TO_NUMBER(NULL),
5437 PROMISED_DATE_COST = TO_NUMBER(NULL),
5438 MIN_SCORE_COST = TO_NUMBER(NULL)
5439 WHERE
5440 SCENARIO_ID = P_SCENARIO_ID;
5441
5442 END IF;
5443
5444 X_STATUS := 'SUCCESS';
5445
5446 EXCEPTION
5447 WHEN OTHERS THEN
5448 X_STATUS := 'FAILURE';
5449 RAISE;
5450 END;
5451
5452 /*======================================================================
5453 FUNCTION : GET_SAVING_PERCENT_INCENTIVE PUBLIC
5454 PARAMETERS:
5455 p_scenario_id IN scenario id
5456
5457 COMMENT : Returns the saving percent of the given scenario.
5458 ======================================================================*/
5459 FUNCTION GET_SAVING_PERCENT_INCENTIVE (p_scenario_id IN NUMBER)
5460 RETURN NUMBER
5461 IS
5462 L_SAVING_PERCENT NUMBER := 0;
5463
5464 l_total_award NUMBER := 0;
5465 l_new_rebate NUMBER := 0;
5466 l_incentive NUMBER := 0;
5467 l_total_incentive NUMBER := 0;
5468 l_total_current_amount NUMBER := 0;
5469
5470 l_bid_number_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5471 l_award_total_sum_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5472 l_savings_amount_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5473 l_current_amount_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5474 l_current_total_spend_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5475 l_fixed_incentive_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5476 l_current_rebate_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
5477
5478 BEGIN
5479
5480 SELECT
5481 bi.bid_number as selected_bid_number,
5482 SUM(por.award_quantity * decode(nvl(por.award_shipment_number,-1),-1,bi.per_unit_price_component,pbs.per_unit_price_component) + bi.fixed_amount_component) AS award_total_sum,
5483 SUM(por.award_quantity * nvl2(ai.current_price, (ai.current_price -por.award_price), 0)) AS savings_amount,
5484 SUM(por.award_quantity * nvl(ai.current_price,por.award_price)) AS current_amount,
5485 nvl(pbh.CURRENT_TOTAL_SPEND, 0) CURRENT_TOTAL_SPEND,
5486 nvl(pbh.FIXED_INCENTIVE, 0) FIXED_INCENTIVE,
5487 nvl(pbh.CURRENT_REBATE,0) CURRENT_REBATE
5488 BULK COLLECT INTO
5489 l_bid_number_col ,
5490 l_award_total_sum_col ,
5491 l_savings_amount_col,
5492 l_current_amount_col,
5493 l_current_total_spend_col,
5494 l_fixed_incentive_col,
5495 l_current_rebate_col
5496 FROM
5497 pon_bid_item_prices bi,
5498 pon_auction_item_prices_all ai,
5499 pon_optimize_scenarios pos,
5500 pon_optimize_results por,
5501 pon_bid_shipments pbs,
5502 pon_bid_headers pbh
5503 WHERE
5504 por.scenario_id = pos.scenario_id
5505 AND
5506 por.bid_number = bi.bid_number
5507 AND
5508 pos.auction_header_id = bi.auction_header_id
5509 AND
5510 ai.auction_header_id = bi.auction_header_id
5511 AND
5512 bi.line_number = por.line_number
5513 AND
5514 ai.line_number = bi.line_number
5515 AND
5516 ai.group_type in ('LINE', 'LOT', 'GROUP_LINE')
5517 AND
5518 nvl(por.award_quantity, -1) > 0
5519 AND
5520 por.bid_number = pbh.bid_number
5521 and
5522 pos.scenario_id = p_scenario_id
5523 AND por.bid_number = pbs.bid_number(+)
5524 AND por.line_number = pbs.line_number(+)
5525 AND nvl(por.award_shipment_number, -1) = pbs.shipment_number(+)
5526 GROUP BY bi.bid_number, pbh.CURRENT_TOTAL_SPEND, pbh.FIXED_INCENTIVE, pbh.CURRENT_REBATE;
5527
5528 FOR i IN 1..l_bid_number_col.COUNT LOOP
5529
5530 l_total_Award := l_award_total_sum_col(i) + l_current_total_spend_col(i);
5531
5532 BEGIN
5533 select rebate
5534 into l_new_rebate
5535 from pon_bid_rebates
5536 where bid_number = l_bid_number_col(i)
5537 and l_total_award between lower_spend and upper_spend;
5538
5539 EXCEPTION WHEN NO_DATA_FOUND THEN
5540 l_new_rebate := 0;
5541 END;
5542
5543 if l_new_rebate = 0 then
5544 l_new_rebate := l_current_rebate_col(i);
5545 end if;
5546
5547 l_incentive := l_fixed_incentive_col(i) + (l_award_total_sum_col(i) * l_new_rebate / 100 )
5548 + l_current_total_spend_col(i) * ( l_new_rebate - l_current_rebate_col(i) )/100;
5549
5550 l_total_incentive := l_total_incentive + l_incentive + l_savings_amount_col(i);
5551 l_total_current_amount := l_total_current_amount + l_current_amount_col(i);
5552 END LOOP;
5553
5554 if l_total_current_amount <> 0 then
5555 l_saving_percent := l_total_incentive/l_total_current_amount*100;
5556 end if;
5557
5558 RETURN l_saving_percent;
5559 EXCEPTION
5560 WHEN OTHERS THEN
5561 RETURN 0;
5562 END GET_SAVING_PERCENT_INCENTIVE;
5563
5564 /*Line Type and Structure Changes Project:
5565 Procedure to copy all backing requisition Associated to SOl lines from pon_backing_requisitions to pon_bid_backing_requisitions.*/
5566
5567 PROCEDURE create_pon_bid_back_req(p_auction_header_id IN NUMBER)
5568 IS
5569
5570 l_user_id NUMBER;
5571 l_login_id NUMBER;
5572 l_module VARCHAR2(30) := 'create_pon_bid_back_req';
5573
5574 l_encumberence_flag VARCHAR2(1);
5575 l_outcome_document_type VARCHAR2(100);
5576
5577 BEGIN
5578
5579 IF (g_debug_mode = 'Y') THEN
5580 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5581 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Begin: ');
5582 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Param: p_auction_header_id '|| p_auction_header_id);
5583 END IF;
5584 END IF;
5585
5586 l_user_id := fnd_global.user_id;
5587 l_login_id := fnd_global.login_id;
5588
5589 INSERT INTO pon_bid_backing_requisitions(auction_header_id,
5590 auction_line_number,
5591 bid_number,
5592 bid_line_number,
5593 requisition_header_id ,
5594 requisition_line_id ,
5595 requisition_quantity ,
5596 requisition_number,
5597 created_by,
5598 last_update_date,
5599 last_updated_by,
5600 last_update_login,
5601 creation_date )
5602 (
5603 SELECT pbr.auction_header_id,
5604 pbr.line_number,
5605 -1,
5606 -1,
5607 pbr.requisition_header_id,
5608 pbr.requisition_line_id,
5609 pbr.requisition_quantity,
5610 pbr.requisition_number,
5611 l_user_id,
5612 sysdate,
5613 l_user_id,
5614 l_login_id,
5615 SYSDATE
5616 FROM pon_auction_item_prices_all paip, pon_backing_requisitions pbr
5617 WHERE pbr.auction_header_id = p_auction_header_id
5618 AND paip.auction_header_id = pbr.auction_header_id
5619 AND paip.line_number = pbr.line_number);
5620
5621 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5622 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'END: ');
5623 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Insert into pon_bid_backing_requisitions Succesful');
5624 END IF;
5625
5626 EXCEPTION
5627 WHEN OTHERS THEN
5628 IF (g_debug_mode = 'Y') THEN
5629 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5630 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'An exception occurred during the INSERT INTO pon_bid_backing_requisitions.
5631 Raising the exception.....');
5632 END IF;
5633 END IF;
5634 RAISE;
5635
5636 END create_pon_bid_back_req;
5637
5638 PROCEDURE update_total_agreement_amount(
5639 p_auction_header_id IN NUMBER,
5640 p_bid_number IN NUMBER,
5641 p_override_amount IN VARCHAR2,
5642 p_total_agreement_amount OUT NOCOPY NUMBER)
5643 IS
5644 CURSOR c_bid_lines
5645 IS
5646 SELECT bi.Line_number,
5647 bi.award_status,
5648 NVL(bi.award_price , bi.price) * Nvl(BI.AWARD_QUANTITY, DECODE(AI.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE',1 , ai.quantity)) award_amount,
5649 pbh.po_agreed_amount
5650 FROM pon_bid_item_prices bi,
5651 pon_auction_item_prices_all ai,
5652 pon_bid_headers pbh
5653 WHERE bi.bid_number = p_bid_number
5654 AND bi.auction_header_id = ai.auction_header_id
5655 AND ai. auction_header_id = pbh.auction_header_id
5656 AND pbh.bid_number = bi.bid_number
5657 AND bi.line_number = ai.line_number
5658 AND ai.group_type IN ('LOT', 'LINE', 'GROUP_LINE')
5659 AND bi.award_status = 'AWARDED';
5660 l_bid_lines_rec c_bid_lines%ROWTYPE;
5661 x_total_agreement_amount NUMBER := 0;
5662 l_update_amount VARCHAR2(5);
5663 BEGIN
5664
5665 OPEN c_bid_lines;
5666 LOOP
5667 FETCH c_bid_lines INTO l_bid_lines_rec;
5668 EXIT
5669 WHEN c_bid_lines%NOTFOUND;
5670 IF(p_override_amount = 'TRUE' OR l_bid_lines_rec.po_agreed_amount IS NULL OR l_bid_lines_rec.po_agreed_amount = 0) THEN
5671 l_update_amount := 'TRUE';
5672 END IF;
5673 x_total_agreement_amount := NVL(x_total_agreement_amount,0) + NVL(l_bid_lines_rec.award_amount,0);
5674 END LOOP;
5675 p_total_agreement_amount := x_total_agreement_amount;
5676 END update_total_agreement_amount;
5677
5678 PROCEDURE complete_partial_auction (p_auction_header_id IN NUMBER ) IS
5679
5680 x_event_id pon_auction_headers_all.event_id%type;
5681 x_count NUMBER;
5682
5683 BEGIN
5684 -- complete auction header disposition
5685 -- clear out the request_id for super-large auctions
5686 update pon_auction_headers_all
5687 set AUCTION_STATUS = 'AUCTION_CLOSED',
5688 REQUEST_ID = NULL,
5689 LAST_UPDATE_DATE = sysdate
5690 where auction_header_id = p_auction_header_id;
5691
5692 -- complete work flow
5693 pon_auction_pkg.COMPLETE_AUCTION(p_auction_header_id);
5694
5695 END complete_partial_auction;
5696
5697 PROCEDURE clear_unawarded_draft_awards
5698 (
5699 p_auction_header_id IN NUMBER,
5700 p_line_number IN NUMBER,
5701 p_award_date IN DATE,
5702 p_auctioneer_id IN NUMBER,
5703 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
5704 )
5705 IS
5706 BEGIN
5707
5708 IF p_neg_has_lines = 'Y' THEN -- FPK: CPA
5709
5710
5711 IF (p_line_number IS NULL OR p_line_number <= 0 )THEN
5712 -- Header level awards
5713 --Update award status to REJECTED for all the bids
5714 UPDATE pon_bid_item_prices
5715 SET award_status = 'REJECTED',
5716 award_quantity = NULL,
5717 award_date = p_award_date,
5718 last_update_date = p_award_date,
5719 last_updated_by = p_auctioneer_id,
5720 award_shipment_number = NULL,
5721 award_price = NULL
5722 WHERE bid_number IN (
5723 SELECT bid_number
5724 FROM pon_bid_headers
5725 WHERE auction_header_id = p_auction_header_id
5726 AND bid_status = 'ACTIVE'
5727 )
5728 AND auction_line_number <> -1 -- Unsolicited Lines Project : Donot clear unsolicited lines awarded.
5729 AND auction_line_number NOT IN (SELECT line_number
5730 FROM pon_auction_item_prices_all
5731 WHERE auction_header_id = p_auction_header_id
5732 AND Nvl(award_status, 'NO') = 'COMPLETED');
5733
5734 -- Delete All Awards since it is a header-level awarding
5735 DELETE FROM pon_acceptances
5736 WHERE auction_header_id = p_auction_header_id
5737 AND auction_line_number <> -1 -- Unsolicited Lines Project : Donot delete unsolicited lines records.
5738 AND auction_line_number NOT IN (SELECT line_number
5739 FROM pon_auction_item_prices_all
5740 WHERE auction_header_id = p_auction_header_id
5741 AND Nvl(award_status, 'NO') = 'COMPLETED');
5742
5743
5744 -- reset the award mode at auction item level
5745 UPDATE pon_auction_item_prices_all
5746 SET award_mode = null
5747 WHERE auction_header_id = p_auction_header_id
5748 AND Nvl(award_status, 'NO') = 'COMPLETED';
5749 ELSE
5750 -- Group Level awards need to be rejected first
5751 --Update award status to REJECTED for all the bids
5752 UPDATE pon_bid_item_prices
5753 SET award_status = 'REJECTED',
5754 award_quantity = NULL,
5755 award_date = p_award_date,
5756 last_update_date = p_award_date,
5757 last_updated_by = p_auctioneer_id,
5758 award_shipment_number = NULL,
5759 award_price = NULL
5760 WHERE bid_number IN (
5761 SELECT bid_number
5762 FROM pon_bid_headers
5763 WHERE auction_header_id = p_auction_header_id
5764 AND bid_status = 'ACTIVE'
5765 )
5766 AND line_number IN (SELECT line_number
5767 FROM pon_auction_item_prices_all
5768 WHERE auction_header_id = p_auction_header_id
5769 AND (line_number = p_line_number
5770 OR parent_line_number = p_line_number));
5771
5772 -- Delete All group line awards since it is a group-level awarding
5773 DELETE FROM pon_acceptances
5774 WHERE auction_header_id = p_auction_header_id
5775 AND line_number IN (SELECT line_number
5776 FROM pon_auction_item_prices_all
5777 WHERE auction_header_id = p_auction_header_id
5778 AND parent_line_number = p_line_number);
5779 END IF;
5780 END IF;
5781 END clear_unawarded_draft_awards;
5782
5783 END PON_AWARD_PKG;
[Home] [Help]