1 PACKAGE BODY PON_AWARD_PKG as
2 -- $Header: PONAWRDB.pls 120.42 2008/02/22 16:08:24 sssahai 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 update_all_bid_item_prices
10 (
11 p_bid_number IN NUMBER,
12 p_award_status IN VARCHAR2,
13 p_award_date IN DATE,
14 p_auctioneer_id IN NUMBER
15 ) IS
16
17 l_max_line_number NUMBER;
18 l_batch_size NUMBER;
19 l_batch_start NUMBER;
20 l_batch_end NUMBER;
21 l_commit_flag BOOLEAN;
22
23 BEGIN
24
25 -- by default, we do not want to commit intermittently
26 l_commit_flag := FALSE;
27
28 -- just set award_qty same as bid_qty
29 select nvl(max(line_number),0)
30 into l_max_line_number
31 from pon_bid_item_prices
32 where bid_number = p_bid_number;
33
34 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
35
36 l_batch_start := 1;
37
38 IF (l_max_line_number <l_batch_size) THEN
39 l_batch_end := l_max_line_number;
40 ELSE
41 l_commit_flag := TRUE; -- commit if we are going to loop over multiple times
42 l_batch_end := l_batch_size;
43 END IF;
44
45
46 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop
47
48 UPDATE PON_BID_ITEM_PRICES pbip
49 SET
50 (pbip.award_status,
51 pbip.award_quantity,
52 pbip.award_date,
53 pbip.last_update_date,
54 pbip.last_updated_by,
55 pbip.award_price,
56 pbip.award_shipment_number) =
57 (
58 select
59 p_award_status,
60 decode (paip.group_type, 'LOT_LINE', null,
61 'GROUP', null,
62 decode (paha.contract_type, 'BLANKET', null,
63 decode (paip.order_type_lookup_code, 'FIXED PRICE', 1,
64 'AMOUNT', 1,
65 'RATE', 1, pbip.quantity ))),
66 p_award_date,
67 p_award_date,
68 p_auctioneer_id,
69 pbip.price,
70 null
71 from
72 pon_auction_item_prices_all paip,
73 pon_auction_headers_all paha
74 where pbip.bid_number = p_bid_number
75 and pbip.auction_header_id = paip.auction_header_id
76 and pbip.line_number = paip.line_number
77 and paha.auction_header_id = pbip.auction_header_id
78 )
79 where
80 pbip.bid_number = p_bid_number and
81 pbip.line_number >= l_batch_start and
82 pbip.line_number <= l_batch_end;
83
84 l_batch_start := l_batch_end + 1;
85
86 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
87 l_batch_end := l_max_line_number;
88 ELSE
89 l_batch_end := l_batch_end + l_batch_size;
90 END IF;
91
92
93 IF(l_commit_flag = TRUE) THEN
94 COMMIT;
95 END IF;
96
97 END LOOP; --} --end-loop
98
99 END update_all_bid_item_prices;
100
101
102 PROCEDURE update_all_auction_item_prices
103 (
104 p_auction_id IN NUMBER,
105 p_bid_number IN NUMBER,
106 p_award_date IN DATE,
107 p_auctioneer_id IN NUMBER
108 ) IS
109
110 l_batch_size NUMBER;
111 l_batch_start NUMBER;
112 l_batch_end NUMBER;
113 l_max_line_number NUMBER;
114 l_commit_flag BOOLEAN;
115
116 BEGIN
117
118 -- by default, we do not want to commit intermittently
119 l_commit_flag := FALSE;
120
121 select nvl(max(line_number),0)
122 into l_max_line_number
123 from pon_auction_item_prices_all
124 where auction_header_id = p_auction_id;
125
126 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
127
128 l_batch_start := 1;
129
130 IF (l_max_line_number <l_batch_size) THEN
131 l_batch_end := l_max_line_number;
132 ELSE
133 l_commit_flag := TRUE; -- commit if we are going to loop over multiple times
134 l_batch_end := l_batch_size;
135 END IF;
136
137 WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop
138
139 UPDATE pon_auction_item_prices_all paip
140 SET
141 (paip.award_status,
142 paip.awarded_quantity,
143 paip.award_mode,
144 paip.last_update_date,
145 paip.last_updated_by) =
146 (
147 select
148 decode (pbip.award_status, 'AWARDED', 'AWARDED', 'REJECTED', 'AWARDED', 'PARTIAL', 'AWARDED', to_char(null)),
149 decode (paip.group_type, 'LOT_LINE', null,
150 'GROUP', null,
151 decode (paip.order_type_lookup_code, 'FIXED PRICE', 1,
152 'AMOUNT', 1,
153 'RATE', 1,
154 decode (paha.contract_type, 'BLANKET', 1, 'CONTRACT', 1, pbip.quantity) ) ),
155 g_AWARD_QUOTE,
156 p_award_date,
157 p_auctioneer_id
158 from
159 pon_bid_item_prices pbip,
160 pon_auction_headers_all paha
161 where pbip.bid_number = p_bid_number
162 and pbip.auction_header_id = paip.auction_header_id
163 and pbip.line_number = paip.line_number
164 and paha.auction_header_id = pbip.auction_header_id
165 )
166 where
167 paip.auction_header_id = p_auction_id and
168 paip.line_number >= l_batch_start and
169 paip.line_number <= l_batch_end;
170
171 l_batch_start := l_batch_end + 1;
172
173 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
174 l_batch_end := l_max_line_number;
175 ELSE
176 l_batch_end := l_batch_end + l_batch_size;
177 END IF;
178
179 IF(l_commit_flag = TRUE) THEN
180 COMMIT;
181 END IF;
182
183 END LOOP; --} --end-loop- batching
184
185 --
186 END update_all_auction_item_prices;
187
188
189
190 -- This method is kind of redundant because we now use the
191 -- award status flag to determine which item is awarded and
192 -- which is not.
193 -- So removed all the old code to delete unawarded items and only
194 -- doing an update of the award quantity
195
196 PROCEDURE clean_unawarded_items (p_batch_id IN NUMBER) IS
197
198 BEGIN
199
200 -- update the award quantity to 0 for those fields that have award quantity
201 -- less than 0
202 UPDATE pon_award_items_interface
203 SET award_quantity = 0
204 WHERE batch_id = p_batch_id
205 AND award_quantity < 0;
206
207 COMMIT;
208
209 END clean_unawarded_items;
210
211
212 /*
213 Reject the active bids on negotiation items that were not awarded
214 and cancel backing requisitions for the items, if any
215 */
216 PROCEDURE reject_unawarded_active_bids(p_auction_header_id IN NUMBER,
217 p_user_id IN NUMBER,
218 p_note_to_rejected IN VARCHAR2,
219 p_neg_has_lines IN VARCHAR2) IS
220
221 -- select all active bid items for for which award decision is not made.
222 -- this includes auction lines with no award decision made
223 -- and auction lines with award decision made but have bid items unawarded
224 CURSOR active_bid_lines(p_auction_header_id NUMBER) IS
225 select al.line_number,
226 al.line_origination_code,
227 nvl(al.award_status,'NO'),
228 bl.bid_number,
229 bl.order_number,
230 bl.award_quantity
231 from pon_auction_item_prices_all al,
232 pon_bid_item_prices bl,
233 pon_bid_headers bh
234 where al.auction_header_id = p_auction_header_id
235 and bl.auction_header_id = al.auction_header_id
236 and bl.line_number = al.line_number
237 and bh.bid_number = bl.bid_number
238 and nvl(bh.bid_status,'NONE') = 'ACTIVE'
239 -- we get lines with award decision made but have some bids unawarded
240 --and nvl(al.award_status,'NO') = 'NO'
241 and nvl(bl.award_status,'NO') = 'NO';
242
243
244 -- FPK: CPA select all active bids for which award decision is not made.
245 CURSOR active_bid_headers(p_auction_header_id NUMBER) IS
246 select nvl(ah.award_status,'NO'),
247 bh.bid_number
248 from pon_auction_headers_all ah,
249 pon_bid_headers bh
250 where bh.auction_header_id = p_auction_header_id
251 and bh.auction_header_id = ah.auction_header_id
252 and nvl(bh.bid_status,'NONE') = 'ACTIVE'
253 and nvl(bh.award_status,'NO') = 'NO';
254
255
256 x_line_number pon_auction_item_prices_all.line_number%type;
257 x_old_line_number pon_auction_item_prices_all.line_number%type;
258 x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
259 x_bid_number pon_bid_headers.bid_number%type;
260 x_order_number pon_bid_headers.order_number%type;
261 x_award_quantity pon_bid_item_prices.award_quantity%type;
262 x_line_award_status pon_auction_item_prices_all.award_status%type;
263 x_stored_note_to_rejected pon_acceptances.reason%type;
264 x_error_code VARCHAR2(20);
265
266 x_bid_number_list integerList;
267 x_bid_number_found BOOLEAN;
268 x_count NUMBER;
269
270 -- FPK: CPA
271 x_header_award_status PON_BID_HEADERS.AWARD_STATUS%TYPE;
272
273 BEGIN
274 IF p_neg_has_lines = 'Y' THEN -- FPK: CPA
275 open active_bid_lines(p_auction_header_id);
276 loop
277 fetch active_bid_lines
278 into x_line_number,
279 x_line_origination_code,
280 x_line_award_status,
281 x_bid_number,
282 x_order_number,
283 x_award_quantity;
284 exit when active_bid_lines%notfound;
285 -- "AND x_line_award_status = 'NO'" condition added
286 -- to ensure ONLY lines with NO award decision made are put back into the pool.
287 if (x_line_origination_code = 'REQUISITION' AND x_line_award_status = 'NO') then
288 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF_BY_LINE(p_auction_header_id, x_line_number, x_error_code);
289 end if;
290 /*
291 -- reject the bid line (note that reject shares the same
292 -- procedure as award)
293 award_bid (x_order_number, -- p_order_number
294 p_user_id, -- p_auctioneer_id
295 p_auction_header_id, -- p_auction_header_id
296 x_bid_number, -- p_bid_number
297 x_line_number, -- p_auction_line_number
298 x_award_quantity, -- p_award_quantity
299 'REJECTED', -- p_award_status
300 p_note_to_rejected, -- p_reason
301 sysdate, -- p_award_date,
302 null, -- p_originPartyId: obsolete
303 null, -- p_originUserId: obsolete
304 null, -- p_currency: obsolete
305 null -- p_billType: obsolete
306 );
307 */
308 -- added for the new award flow in FPJ
309 -- Need to take care of notes in pon_acceptances.
310 update_single_bid_item_prices
311 (
312 x_bid_number,
313 x_line_number,
314 'REJECTED',
315 x_award_quantity,
316 sysdate,
317 p_user_id
318 );
319
320 -- determine if the bid number of the bid line has been added to the list
321 x_bid_number_found := FALSE;
322
323 FOR i IN 1 .. x_bid_number_list.COUNT LOOP
324 IF x_bid_number = x_bid_number_list(i) THEN
325 x_bid_number_found := TRUE;
326 EXIT;
327 END IF;
328 END LOOP;
329
330 -- if not, add it to the list
331 IF NOT(x_bid_number_found) THEN
332 x_bid_number_list(x_bid_number_list.COUNT + 1) := x_bid_number;
333 END IF;
334
335 -- complete or award item disposition as necessary
336 -- there could be multiple bids for the same line
337 -- only need to call it once for each line
338 IF (x_old_line_number is null OR
339 x_old_line_number <> x_line_number) THEN
340 x_old_line_number := x_line_number;
341
342 IF (x_line_award_status = 'NO') THEN
343 -- Update acceptances for the lines with no award decision made
344 update_unawarded_acceptances(
345 p_auction_header_id, -- auction header id
346 x_line_number, -- line number
347 p_note_to_rejected, --note to rejected suppliers
348 SYSDATE, -- award_date
349 p_user_id);
350 ELSE
351 -- Update acceptances for the lines with award decision already made
352 x_stored_note_to_rejected := null;
353 x_count := 0;
354 SELECT count(*) INTO x_count FROM pon_acceptances
355 WHERE auction_header_id = p_auction_header_id
356 AND line_number = x_line_number
357 AND ACCEPTANCE_TYPE = 'REJECTED';
358 --
359 IF x_count > 0 THEN
360 -- rejection note exists and carried over for rejected suppliers
361 SELECT distinct REASON INTO x_stored_note_to_rejected
362 FROM pon_acceptances
363 WHERE auction_header_id = p_auction_header_id
364 AND line_number = x_line_number
365 AND ACCEPTANCE_TYPE = 'REJECTED';
366 END IF;
367 --
368 update_unawarded_acceptances(
369 p_auction_header_id, -- auction header id
370 x_line_number, -- line number
371 x_stored_note_to_rejected, --note to rejected suppliers
372 SYSDATE, -- award_date
373 p_user_id);
374 END IF;
375 award_item_disposition (p_auction_header_id, x_line_number, 0);
376 END IF;
377
378 END LOOP;
379 CLOSE active_bid_lines;
380
381 -- update the award status for the bids whose lines were rejected
382 FOR i IN 1 .. x_bid_number_list.COUNT LOOP
383 update_single_bid_header(x_bid_number_list(i), p_user_id);
384 END LOOP;
385
386 -- update the award status for the auction that was bidded on
387 -- if any bid line was rejected
388 IF x_bid_number_list.COUNT > 0 THEN
389 update_auction_headers(p_auction_header_id, g_AWARD_LINE, SYSDATE, p_user_id, 'Y');
390 END IF;
391 ELSE -- negotiation does not have lines
392 OPEN active_bid_headers(p_auction_header_id);
393 LOOP
394 FETCH active_bid_headers
395 INTO x_header_award_status, x_bid_number;
396 EXIT WHEN active_bid_headers%NOTFOUND;
397
398 -- determine if the bid number has been added to the list
399 x_bid_number_found := FALSE;
400
401 FOR i IN 1 .. x_bid_number_list.COUNT LOOP
402 IF x_bid_number = x_bid_number_list(i) THEN
403 x_bid_number_found := TRUE;
404 EXIT;
405 END IF;
406 END LOOP;
407
408 -- if not, add it to the list
409 IF NOT(x_bid_number_found) THEN
410 x_bid_number_list(x_bid_number_list.COUNT + 1) := x_bid_number;
411 END IF;
412 END LOOP;
413 CLOSE active_bid_headers;
414
415 -- update the award status for the active bids in this auction where no
416 -- award decision made (all bids will be rejected)
417 FORALL k IN 1..x_bid_number_list.COUNT
418
419 UPDATE PON_BID_HEADERS
420 SET AWARD_STATUS = 'REJECTED',
421 AWARD_DATE = SYSDATE, /* new column created as part of CPA project.
422 It will be updated only when negotiation does
423 not have lines. */
424 last_update_date = SYSDATE,
425 last_updated_by = p_user_id
426 WHERE bid_number = x_bid_number_list(k);
427
428 -- update the award status for the auction that was bidded on
429 -- and no award decision made
430 IF x_bid_number_list.COUNT > 0 THEN
431 update_auction_headers(p_auction_header_id, g_AWARD_QUOTE, SYSDATE,
432 p_user_id, 'N');
433 END IF;
434 END IF; -- IF neg. has lines
435 END reject_unawarded_active_bids;
436
437 ----------------------------------------------------------------
438 -- Complete award process for a negotiation
439 -- mirrors NegotiationDoc.completeAward which is gone after
440 -- migration to OA
441 -- also contains some logic from reviewComplete.jsp
442 ----------------------------------------------------------------
443
444 PROCEDURE complete_award (p_auction_header_id_encrypted IN VARCHAR2,
445 p_auction_header_id IN NUMBER,
446 p_note_to_rejected IN VARCHAR2,
447 p_shared_award_decision IN VARCHAR2,
448 p_user_id IN NUMBER,
449 p_create_po_flag IN VARCHAR2,
450 p_source_reqs_flag IN VARCHAR2,
451 p_no_bids_flag IN VARCHAR2,
452 p_has_backing_reqs_flag IN VARCHAR2,
453 p_outcome_status IN VARCHAR2,
454 p_has_scoring_teams_flag IN VARCHAR2,
455 p_scoring_lock_tpc_id IN NUMBER) IS
456
457 x_line_number pon_auction_item_prices_all.line_number%type;
458 x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
459 x_error_code VARCHAR2(20);
460 x_awarded_quantity NUMBER;
461 l_neg_has_lines PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE; -- FPK: CPA
462
463 --Business Events Changes
464 x_return_status VARCHAR2(20);
465 x_msg_count NUMBER;
466 x_msg_data VARCHAR2(2000);
467
468 -- select items without any bids that had backing requisitions
469 CURSOR items_with_reqs_no_bids(p_auction_header_id NUMBER) IS
470 SELECT line_number, line_origination_code
471 FROM PON_AUCTION_ITEM_PRICES_ALL
472 WHERE auction_header_id = p_auction_header_id
473 AND nvl(number_of_bids,0) = 0
474 AND line_origination_code = 'REQUISITION';
475
476
477 CURSOR auction_items_all (p_auction_header_id NUMBER) IS
478 SELECT line_number, nvl(awarded_quantity, 0)
479 FROM PON_AUCTION_ITEM_PRICES_ALL
480 WHERE auction_header_id = p_auction_header_id;
481
482 BEGIN
483
484 l_neg_has_lines := PON_AUCTION_PKG.neg_has_lines(p_auction_header_id); -- FPK: CPA
485 ----
486 if (p_create_po_flag <> 'Y' and p_has_backing_reqs_flag = 'Y') then
487 -- put requisitions back in pool if auction
488 -- has backing req and no outcome creation
489 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF(p_auction_header_id, x_error_code);
490 end if;
491
492 /* FPK: CPA
493 If negotiation has lines: for all items that have active bids but are not awarded,
494 reject the active bids and cancel backing requisitions, if any
495 If negotiation does not have lines: reject all active bids that were not awarded n
496 nor rejected (no award decision was made)
497 */
498 reject_unawarded_active_bids(p_auction_header_id, p_user_id, p_note_to_rejected, l_neg_has_lines);
499
500 -- Requisitions should be put back into the pool if
501 -- an item has received no bids and the buyer
502 -- is completing the auction
503
504 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
505 open items_with_reqs_no_bids(p_auction_header_id);
506 loop
507
508 /*
509 rrkulkar-large-auction-support changes
510 modified the cursor to simply loop over the exact set of lines
511 rather than looping over all the lines, and filtering the lines
512 in the cursor-loop by adding an if condition
513
514 */
515 fetch items_with_reqs_no_bids
516 into x_line_number,
517 x_line_origination_code;
518 exit when items_with_reqs_no_bids%notfound;
519
520 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF_BY_LINE(p_auction_header_id, x_line_number, x_error_code);
521
522 end loop;
523 close items_with_reqs_no_bids;
524 --
525
526 /*
527 rrkulkar-large-auction-support changes
528 instead of looping over all the lines, we can update all
529 lines in a single query
530
531 */
532
533 /*
534 rrkulkar-large-auction-support : commented out the call to complete_item_disposition
535 need to add batching here
536 */
537
538 update pon_auction_item_prices_all
539 set AWARD_STATUS = 'COMPLETED',
540 LAST_UPDATE_DATE = sysdate,
541 AWARDED_QUANTITY = nvl(awarded_quantity,0)
542 where auction_header_id = p_auction_header_id;
543
544 END IF; -- IF l_neg_has_lines = 'Y'
545 --
546 -- if team scoring is enabled, call routine to lock team scoring
547 IF (p_has_scoring_teams_flag = 'Y') THEN
548 -- check to see if the auction was already locked for scoring
549 -- if this were true, the p_scoring_lock_tpc_id will be -1
550 -- as determined in the CompleteAwardAM from where this API is called.
551 IF (p_scoring_lock_tpc_id = -1) THEN
552 NULL;
553 ELSE
554 -- call pvt API to lock scoring
555 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
556 fnd_log.string(log_level => fnd_log.level_unexpected
557 ,module => 'pon_award_pkg.complete_award'
558 ,message => 'before calling private API to lock team scoring');
559 END IF;
560
561 PON_TEAM_SCORING_UTIL_PVT.lock_scoring(p_api_version => 1
562 ,p_auction_header_id => p_auction_header_id
563 ,p_tpc_id => p_scoring_lock_tpc_id
564 ,x_return_status => x_return_status
565 ,x_msg_data => x_msg_data
566 ,x_msg_count => x_msg_count);
567
568 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
569 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
570 fnd_log.string(log_level => fnd_log.level_unexpected
571 ,module => 'pon_award_pkg.complete_award'
572 ,message => 'Error while locking team scoring');
573 END IF;
574 END IF;
575 END IF;
576 END IF;
577
578
579 complete_auction (p_auction_header_id);
580
581 award_notification (p_auction_header_id_encrypted,
582 p_auction_header_id,
583 p_shared_award_decision);
584
585 -- post processing: originally in reviewComplete.jsp
586 -- For no bids we would come directly to this page.
587 -- Thus setShareAwardDecision will be N. We do not want that.
588 -- As otherwise the button will appear in bidViewAuction
589 -- update database w/ outcome status, source req result,
590 -- and award completion date
591 update pon_auction_headers_all
592 set outcome_status = p_outcome_status,
593 award_complete_date = sysdate,
594 source_reqs_flag = p_source_reqs_flag,
595 share_award_decision = decode(p_no_bids_flag, 'Y', 'I', share_award_decision),
596 last_update_date = sysdate
597 where auction_header_id = p_auction_header_id;
598
599 -- Raise Business Event
600 PON_BIZ_EVENTS_PVT.RAISE_NEG_AWRD_COMPLETE_EVENT(
601 p_api_version => 1.0 ,
602 p_init_msg_list => FND_API.G_FALSE,
603 p_commit => FND_API.G_FALSE,
604 p_auction_header_id => p_auction_header_id,
605 p_create_po_flag => p_create_po_flag,
606 x_return_status => x_return_status,
607 x_msg_count => x_msg_count,
608 x_msg_data => x_msg_data);
609
610 END complete_award;
611
612 ----------------------------------------------------------------
613 -- complete auction
614 -- mirrors NegotiationDoc.completeAuction which is gone after
615 -- migration to OA
616 ----------------------------------------------------------------
617
618 PROCEDURE complete_auction (p_auction_header_id IN NUMBER ) IS
619
620 x_event_id pon_auction_headers_all.event_id%type;
621 x_count NUMBER;
622
623 BEGIN
624 -- complete auction header disposition
625 -- clear out the request_id for super-large auctions
626 update pon_auction_headers_all
627 set AWARD_STATUS = 'COMPLETED',
628 AUCTION_STATUS = 'AUCTION_CLOSED',
629 REQUEST_ID = NULL,
630 LAST_UPDATE_DATE = sysdate
631 where auction_header_id = p_auction_header_id;
632
633 -- complete work flow
634 pon_auction_pkg.COMPLETE_AUCTION(p_auction_header_id);
635
636 END complete_auction;
637
638 ----------------------------------------------------------------
639 -- send award notifications
640 -- mirrors NegotiationDoc.awardNotification which is gone after
641 -- migration to OA
642 ----------------------------------------------------------------
643
644 PROCEDURE award_notification (p_auction_header_id_encrypted IN VARCHAR2,
645 p_auction_header_id IN NUMBER,
646 p_shared_award_decision IN VARCHAR2) IS
647
648 x_bid_number pon_bid_headers.bid_number%type;
649 x_bid_tp_contact_name pon_bid_headers.trading_partner_contact_name%type;
650 x_auction_tp_name pon_auction_headers_all.trading_partner_name%type;
651 x_auction_title pon_auction_headers_all.auction_title%type;
652
653 CURSOR all_bidders(p_auction_header_id NUMBER) IS
654 select b.bid_number,
655 b.trading_partner_contact_name contact,
656 a.trading_partner_name auctioneer,
657 a.auction_title
658 from pon_bid_headers b,
659 pon_auction_headers_all a
660 where b.auction_header_id = p_auction_header_id
661 and not nvl(b.bid_status,'NONE') in ('ARCHIVED','DISQUALIFIED')
662 and a.auction_header_id = b.auction_header_id;
663
664 BEGIN
665
666 if (p_shared_award_decision = 'Y') then
667
668 -- send a notification to each supplier
669 open all_bidders(p_auction_header_id);
670 loop
671 fetch all_bidders
672 into x_bid_number,
673 x_bid_tp_contact_name,
674 x_auction_tp_name,
675 x_auction_title;
676 exit when all_bidders%notfound;
677
678 pon_auction_pkg.AWARD_BID(x_bid_number,
679 p_auction_header_id,
680 x_bid_tp_contact_name,
681 x_auction_tp_name,
682 x_auction_title,
683 p_auction_header_id_encrypted);
684 end loop;
685 close all_bidders;
686
687 -- update pon_auction_headers_all
688 update pon_auction_headers_all
689 set SHARE_AWARD_DECISION = p_shared_award_decision
690 where auction_header_id = p_auction_header_id;
691
692 end if;
693
694 END award_notification;
695
696 ----------------------------------------------------------------
697 -- complete item disposition
698 -- mirrors NegotiationItem.completeDisposition which is gone after
699 -- migration to OA
700 ----------------------------------------------------------------
701
702 PROCEDURE complete_item_disposition (p_auction_header_id IN NUMBER,
703 p_line_number IN NUMBER,
704 p_award_quantity IN NUMBER) IS
705
706 BEGIN
707 update pon_auction_item_prices_all
708 set AWARD_STATUS = 'COMPLETED',
709 LAST_UPDATE_DATE = sysdate,
710 AWARDED_QUANTITY = p_award_quantity
711 where auction_header_id = p_auction_header_id
712 and line_number = p_line_number;
713
714 END complete_item_disposition;
715
716
717 ----------------------------------------------------------------
718 -- award item disposition
719 -- mirrors complete_item_disposition
720 -- except that pon_auction_item_prices_all.award_status column is set to AWARDED (instead of COMPLETED)
721 ----------------------------------------------------------------
722
723 PROCEDURE award_item_disposition (p_auction_header_id IN NUMBER,
724 p_line_number IN NUMBER,
725 p_award_quantity IN NUMBER) IS
726
727 BEGIN
728 update pon_auction_item_prices_all
729 set AWARD_STATUS = 'AWARDED',
730 LAST_UPDATE_DATE = sysdate,
731 AWARDED_QUANTITY = p_award_quantity
732 where auction_header_id = p_auction_header_id
733 and line_number = p_line_number;
734
735 END award_item_disposition;
736
737 --
738 ----------------------------------------------------------------
739 -- handles awarding for award by quote, award by line, award line
740 -- procedure added by snatu on 08/15/03
741 -- Coded for FPJ
742 ----------------------------------------------------------------
743 PROCEDURE award_auction
744 ( p_auctioneer_id IN NUMBER
745 , p_auction_header_id IN NUMBER
746 , p_last_update_date IN DATE
747 , p_mode IN VARCHAR2
748 , p_line_num IN NUMBER
749 , p_award_table IN PON_AWARD_TABLE
750 , p_note_to_accepted IN VARCHAR2
751 , p_note_to_rejected IN VARCHAR2
752 , p_batch_id IN NUMBER
753 , x_status OUT NOCOPY VARCHAR2
754 )
755 IS
756 --
757 l_counter BINARY_INTEGER;
758 l_size NUMBER;
759 l_index BINARY_INTEGER;
760 l_rec PON_AWARD_REC;
761 --
762 l_award_lines t_award_lines;
763 -- FPK: CPA
764 l_awarded_bid_headers t_awarded_bid_headers;
765 l_neg_has_lines PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
766
767 l_matrix_index NUMBER;
768 --
769 l_current_bid_number NUMBER;
770 l_bid_list_index NUMBER;
771 l_tmp_award_quantity NUMBER;
772 l_group_type pon_auction_item_prices_all.group_type%TYPE;
773 l_award_date DATE;
774 TYPE BID_LIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
775 l_bid_list BID_LIST_TYPE;
776 l_winning_bid NUMBER;
777 l_neg_contract_type pon_auction_headers_all.contract_type%TYPE;
778
779 --
780 l_has_quantity_tiers pon_auction_item_prices_all.has_quantity_tiers%TYPE;
781 l_award_shipment_number NUMBER;
782 l_suffix VARCHAR2(2);
783
784 --
785 BEGIN
786 --
787 --
788 IF (g_debug_mode = 'Y') THEN
789 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
790 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION', 'Entering procedure with p_auctioneer_id: ' || p_auctioneer_id );
791 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);
792 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 );
793 END IF;
794 END IF;
795
796 l_neg_has_lines := PON_AUCTION_PKG.neg_has_lines(p_auction_header_id);
797
798 select contract_type
799 into l_neg_contract_type
800 from pon_auction_headers_all
801 where auction_header_id = p_auction_header_id
802 and rownum =1;
803
804 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
805 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'START');
806 END IF;
807 --
808 l_matrix_index := 0;
809 l_award_date := SYSDATE;
810 --
811 IF (p_mode = g_AWARD_QUOTE) THEN
812
813 clear_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
814
815 /* update auction-header by nulling out the request-id */
816
817 update pon_auction_headers_all
818 set request_id = to_number(null)
819 where auction_header_id = p_auction_header_id;
820
821 -- Need to expand all the AWARDED bids to build the lines
822 l_size := p_award_table.COUNT;
823 FOR l_index IN 1..l_size LOOP -- Loop through all the bids
824 l_rec := p_award_table(l_index);
825 -- Construct Matrix only in case of awarded bids
826 IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN THEN
827
828 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
829
830 l_winning_bid := l_rec.bid_number;
831
832 update_all_bid_item_prices(l_winning_bid, get_award_status(l_rec.award_outcome), l_award_date, p_auctioneer_id);
833
834 if(l_neg_contract_type in ('BLANKET', 'CONTRACT')) then
835 update_all_auction_item_prices(p_auction_header_id, l_winning_bid, l_award_date, p_auctioneer_id);
836 end if;
837
838 ELSE -- negotiation does not have lines
839 -- Build table of active bids.
840 -- All the bids will have one of the award outcome status: win, lose or no award.
841 l_matrix_index := l_matrix_index + 1;
842 l_awarded_bid_headers(l_matrix_index).bid_number := l_rec.bid_number;
843 l_awarded_bid_headers(l_matrix_index).award_status := get_award_status(l_rec.award_outcome);
844 l_awarded_bid_headers(l_matrix_index).award_date := l_award_date;
845 END IF;
846
847 --update total agreed amount (if any)
848 IF l_rec.total_agreement_amount is not null THEN
849 UPDATE pon_bid_headers
850 SET po_agreed_amount = l_rec.total_agreement_amount
851 WHERE bid_number = l_rec.bid_number;
852 END IF;
853 END IF; -- IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN
854 -- update notes
855 update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
856 END LOOP;
857
858 -- outside the loop, update all auction_lines
859 -- in case of BPA outcome, we can award multiple bids -> but we will update
860 -- all auction lines just once
861
862 IF (l_neg_has_lines = 'Y') THEN
863 IF (l_neg_contract_type <> 'BLANKET' and l_neg_contract_type <> 'CONTRACT') THEN
864 update_all_auction_item_prices(p_auction_header_id, l_winning_bid, l_award_date, p_auctioneer_id);
865 END IF;
866 END IF;
867
868
869 END IF ;
870 --
871 IF (p_mode = g_AWARD_MULTIPLE_LINES) THEN --{
872 clear_draft_awards (p_auction_header_id, p_line_num, l_award_date, p_auctioneer_id, l_neg_has_lines);
873 -- Need to set award quantity and award_status
874 l_size := p_award_table.COUNT;
875 l_current_bid_number := -1;
876 --
877 FOR l_index IN 1..l_size LOOP -- Loop through all the bids
878 l_rec := p_award_table(l_index);
879 -- Construct Matrix only in case of awarded bids
880 IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN THEN
881 --Get Award Qty FROM Response Qty
882 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
883 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
884 WHERE bi.bid_number = l_rec.bid_number
885 AND bi.line_number = l_rec.line_number
886 AND ai.auction_header_id = bi.auction_header_id
887 AND ai.line_number = bi.line_number;
888 --
889 SELECT ai.group_type INTO l_group_type
890 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
891 WHERE bi.bid_number = l_rec.bid_number
892 AND bi.line_number = l_rec.line_number
893 AND ai.auction_header_id = bi.auction_header_id
894 AND ai.line_number = bi.line_number;
895 --
896 l_matrix_index := l_matrix_index + 1;
897 l_award_lines(l_matrix_index).bid_number := l_rec.bid_number;
898 l_award_lines(l_matrix_index).line_number := l_rec.line_number;
899 l_award_lines(l_matrix_index).award_status := get_award_status(l_rec.award_outcome);
900 l_award_lines(l_matrix_index).award_quantity := l_tmp_award_quantity;
901 l_award_lines(l_matrix_index).award_date := l_award_date;
902 l_award_lines(l_matrix_index).group_type := l_group_type;
903 END IF;
904 -- Update Internal Notes and Notes to Suppliers for each bid
905 IF (l_current_bid_number <> l_rec.bid_number) THEN
906 update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
907 --update total agreed amount (if any)
908 IF l_rec.total_agreement_amount is not null THEN
909 UPDATE pon_bid_headers
910 SET po_agreed_amount = l_rec.total_agreement_amount
911 WHERE bid_number = l_rec.bid_number;
912 END IF;
913
914 l_current_bid_number := l_rec.bid_number;
915 END IF;
916 END LOOP;
917 END IF; --}
918 --
919 IF ((p_mode = g_AWARD_LINE) OR (p_mode = g_AWARD_LINE_H)
920 OR(p_mode = g_AWARD_GROUP) OR (p_mode = g_AWARD_GROUP_H)) THEN --{
921 --
922 -- First, REJECT the group line level awards - No cumulative awards
923 IF (p_mode = g_AWARD_GROUP) THEN
924 clear_draft_awards (p_auction_header_id, p_line_num, l_award_date,
925 p_auctioneer_id, l_neg_has_lines);
926 END IF;
927 --
928 l_bid_list_index := 0;
929 SELECT ai.group_type INTO l_group_type
930 FROM pon_auction_item_prices_all ai
931 WHERE ai.auction_header_id = p_auction_header_id
932 AND ai.line_number = p_line_num;
933 --
934 -- Getting the suffix to display the error message correctly.
935 l_suffix := PON_LARGE_AUCTION_UTIL_PKG.GET_DOCTYPE_SUFFIX (p_auction_header_id);
936
937 -- Need to set award quantity and award_status
938 l_size := p_award_table.COUNT;
939 FOR l_index IN 1..l_size LOOP -- Loop through all the bids
940 l_rec := p_award_table(l_index);
941
942 --R12.1 price tiers changes
943 select nvl(has_quantity_tiers,'N') into l_has_quantity_tiers
944 from pon_bid_item_prices
945 where bid_number = l_rec.bid_number
946 and line_number = p_line_num;
947
948 IF (g_debug_mode = 'Y') THEN
949 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
950 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);
951 END IF;
952 END IF;
953
954 -- Construct Matrix in any case (WIN/LOSR)
955 l_matrix_index := l_matrix_index +1;
956 l_award_lines(l_matrix_index).bid_number := l_rec.bid_number;
957 l_award_lines(l_matrix_index).line_number := p_line_num; --l_rec.line_number;
958 l_award_lines(l_matrix_index).award_status := get_award_status(l_rec.award_outcome);
959 l_award_lines(l_matrix_index).award_date := l_award_date;
960 l_award_lines(l_matrix_index).group_type := l_group_type;
961 --
962 IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN THEN --{
963
964 /*
965 R12.1 Quantity based price tiers changes
966 If quantity tiers are present for a line and award quantity is not null
967 validating if award qty falls within the quantity tiers specified by the supplier
968 Update the award shipment number acoordingly.
969 */
970
971 IF ( 'Y' = l_has_quantity_tiers AND l_rec.award_quantity IS NOT NULL)
972 THEN --{
973 l_award_shipment_number := -1;
974
975 select nvl((select pbs.shipment_number
976 from pon_bid_shipments pbs, pon_auction_item_prices_all paip
977 where pbs.bid_number = l_rec.bid_number
978 and pbs.line_number = p_line_num
979 AND l_rec.award_quantity >= pbs.quantity
980 AND l_rec.award_quantity <= pbs.max_quantity
981 AND paip.auction_header_id = pbs.auction_header_id
982 AND paip.line_number = pbs.line_number ),-1)
983 into l_award_shipment_number from dual;
984
985 IF (g_debug_mode = 'Y') THEN
986 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
987 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION', 'award_shipment_number ' || l_award_shipment_number);
988 END IF;
989 END IF;
990
991 IF ( l_award_shipment_number = -1) THEN --{
992 -- Insert errors in interface table.
993 INSERT INTO PON_INTERFACE_ERRORS(
994 batch_id
995 , column_name
996 , error_message_name
997 , table_name
998 , INTERFACE_LINE_ID
999 , expiration_date
1000 , created_by
1001 , creation_date
1002 , last_updated_by
1003 , last_update_date
1004 , last_update_login
1005 , TOKEN1_NAME
1006 , TOKEN1_VALUE
1007 )
1008 Values(
1009 p_batch_id
1010 , fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
1011 , 'PON_QUANTITY_TIER_VIOLATION' || l_suffix
1012 , 'PON_BID_ITEM_PRICES'
1013 , p_line_num
1014 , SYSDATE+7
1015 , fnd_global.user_id
1016 , sysdate
1017 , fnd_global.user_id
1018 , sysdate
1019 , fnd_global.login_id
1020 , 'BID_NUM'
1021 , l_rec.bid_number
1022 );
1023
1024
1025 x_status := 'FAILURE';
1026
1027 IF (g_debug_mode = 'Y') THEN
1028 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1029 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');
1030 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT,' award_quantity: '||l_rec.award_quantity,'Quantity Tier Violation');
1031 END IF;
1032 END IF;
1033 --} End of l_shipment_number = -1
1034 ELSE --{ START of l_award_shipment_number != -1,
1035
1036 --
1037 -- Award qty falls withtin the quantity specified by the supplier.
1038 -- saving the corresponding shipment number
1039 --
1040 l_award_lines(l_matrix_index).award_shipment_number := l_award_shipment_number;
1041
1042 END IF; --} END of l_award_shipment_number != -1,
1043 --} End of has_quantity_tiers='Y' and award_quantity not null
1044 ELSE --{ Start of has_quantity_tiers <> 'Y' or award_quantity <> null
1045
1046 --
1047 -- Line does not have quantity tiers so setting the default value as -1.
1048 --
1049 l_award_lines(l_matrix_index).award_shipment_number := -1;
1050
1051 END IF; --} End of has_quantity_tiers <> 'Y' or award_quantity <> null ; END of Quantity tiers loop
1052
1053 l_award_lines(l_matrix_index).award_quantity := l_rec.award_quantity;
1054 l_award_lines(l_matrix_index).note_to_supplier := p_note_to_accepted;
1055
1056 --} End of award_outcome_win
1057 ELSE --{ Start of award_outcome_lose
1058
1059 IF ((x_status is NULL) OR (x_status = 'SUCCESS')) THEN --{
1060 l_award_lines(l_matrix_index).award_quantity := null;
1061 l_award_lines(l_matrix_index).note_to_supplier := p_note_to_rejected;
1062 END IF; --}
1063
1064 END IF; --} End of award_outcome_lose
1065
1066 --Update Notes only in case of Award Line V Page and NOT for Award Line H Page
1067 IF ((p_mode = g_AWARD_LINE OR p_mode = g_AWARD_GROUP) AND ((x_status is NULL) OR (x_status = 'SUCCESS'))) THEN
1068 update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
1069 END IF;
1070 -- Add new bid to the array
1071 l_bid_list_index := l_bid_list_index + 1;
1072 l_bid_list(l_bid_list_index) := l_rec.bid_number;
1073 --
1074 END LOOP;
1075 END IF; --}
1076 --
1077 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1078 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'MATRIX BUILT');
1079 END IF;
1080 --
1081 -- Clears the award history in case any for this auction
1082 IF (p_mode = g_AWARD_QUOTE) or (p_mode = g_AWARD_MULTIPLE_LINES) THEN --{
1083
1084 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
1085 IF(p_mode = g_AWARD_MULTIPLE_LINES) THEN
1086 update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
1087 END IF;
1088 END IF;
1089
1090 update_bid_headers(p_auction_header_id, p_auctioneer_id, l_awarded_bid_headers, l_neg_has_lines);
1091
1092 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
1093 IF(p_mode = g_AWARD_MULTIPLE_LINES) THEN
1094 update_auction_item_prices(p_auction_header_id, null, l_award_date, p_auctioneer_id, p_mode);
1095 END IF;
1096 END IF;
1097
1098 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, l_neg_has_lines);
1099
1100 IF l_neg_has_lines = 'Y' THEN -- FPK: CPA
1101
1102 bulk_update_pon_acceptances(
1103 p_auction_header_id,
1104 null, null, null,
1105 l_award_date, p_auctioneer_id, p_mode);
1106 END IF;
1107 --}
1108 ELSE --{
1109 update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
1110 l_size := l_bid_list.count;
1111 FOR l_index IN 1..l_size LOOP
1112 update_single_bid_header(l_bid_list(l_index),p_auctioneer_id);
1113 END LOOP;
1114 update_auction_item_prices(p_auction_header_id,p_line_num, l_award_date, p_auctioneer_id, p_mode);
1115 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, l_neg_has_lines);
1116 bulk_update_pon_acceptances(
1117 p_auction_header_id, p_line_num,
1118 p_note_to_accepted, p_note_to_rejected,
1119 l_award_date, p_auctioneer_id, p_mode);
1120 End IF; --}
1121 --
1122 --
1123 --
1124 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1125 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'READY_TO_COMMIT');
1126 END IF;
1127 --
1128
1129 /* check if the auction has been modified by some other user
1130 If it has been modified, status returns failure
1131 else this is the only user modifying hte auction
1132 changes are committed to the database in the middle tier
1133 */
1134 IF (((x_status is NULL) OR (x_status = 'SUCCESS')) AND (is_auction_not_updated (p_auction_header_id, p_last_update_date))) THEN
1135 x_status := 'SUCCESS';
1136 -- update the last update date
1137 UPDATE pon_Auction_headers_all
1138 SET last_update_date = SYSDATE
1139 WHERE auction_header_id = p_auction_header_id;
1140 --
1141 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1142 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'SUCCEEDED');
1143 END IF;
1144 --
1145 ELSE
1146 x_status := 'FAILURE';
1147 --
1148 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1149 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.AWARD_AUCTION.AUCTION_ID:' || p_auction_header_id,'FAILED');
1150 END IF;
1151 --
1152 END IF;
1153 --
1154 END award_auction;
1155 --
1156 --
1157 PROCEDURE update_bid_item_prices
1158 (
1159 p_auction_id IN NUMBER,
1160 p_award_lines IN t_award_lines,
1161 p_auctioneer_id IN NUMBER,
1162 p_mode IN VARCHAR2
1163 )
1164 IS
1165 l_size NUMBER;
1166 l_index NUMBER;
1167 l_group_type pon_auction_item_prices_all.group_type%type;
1168 l_award_quantity pon_bid_item_prices.award_quantity%type;
1169 l_award_shipment_number NUMBER;
1170 --
1171 /* for updating group for each bid's group line,
1172 we maintain an associative array (hashmap equivalant)
1173 in the form of (bid:group_number ==> 1234:56
1174 and once all bid lines are updated, we traverse through this map
1175 and update the required bid groups
1176 */
1177 type bid_line_asso is table of varchar2(30) index by varchar2(30);
1178 l_bid_group_map bid_line_asso;
1179 l_bid_line_key VARCHAR2(30);
1180 l_parent_line_number pon_auction_item_prices_all.parent_line_number%type;
1181 l_bid_number pon_bid_item_prices.bid_number%type;
1182 --
1183 BEGIN
1184
1185 IF (g_debug_mode = 'Y') THEN
1186 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1187 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);
1188 END IF;
1189 END IF;
1190
1191 l_size := p_award_lines.COUNT;
1192 -- Loop through the matrix to update bid items and acceptances
1193 FOR l_index IN 1..l_size LOOP
1194 l_group_type := p_award_lines(l_index).group_type;
1195 IF (l_group_type = 'GROUP') THEN
1196 l_award_quantity := null;
1197 ELSE
1198 l_award_quantity := p_award_lines(l_index).award_quantity;
1199 l_award_shipment_number := p_award_lines(l_index).award_shipment_number;
1200 END IF;
1201
1202 IF (g_debug_mode = 'Y') THEN
1203 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1204 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);
1205 END IF;
1206 END IF;
1207
1208 --
1209 -- Price Tiers Changes. If quantity based price tiers are present call the upd_single_bid_item_prices_qt
1210 -- Api which takes the unit price from pon_bid_shipments according to awarded shipment number.
1211 --
1212
1213 IF ( l_award_shipment_number IS NULL OR
1214 l_award_shipment_number = -1 ) THEN --{
1215 update_single_bid_item_prices
1216 (
1217 p_award_lines(l_index).bid_number,
1218 p_award_lines(l_index).line_number,
1219 p_award_lines(l_index).award_status,
1220 l_award_quantity,
1221 p_award_lines(l_index).award_date,
1222 p_auctioneer_id
1223 );
1224 ELSE
1225
1226 -- Quantity Tiers Case.
1227 upd_single_bid_item_prices_qt
1228 (
1229 p_award_lines(l_index).bid_number,
1230 p_award_lines(l_index).line_number,
1231 p_award_lines(l_index).award_status,
1232 l_award_quantity,
1233 p_award_lines(l_index).award_date,
1234 p_auctioneer_id,
1235 l_award_shipment_number
1236 );
1237 END IF; --}
1238
1239 --
1240 IF ( (p_mode = g_AWARD_MULTIPLE_LINES AND l_group_type = 'LOT')
1241 OR (p_mode = g_AWARD_LINE AND l_group_type = 'LOT')
1242 OR (p_mode = g_AWARD_LINE_H AND l_group_type = 'LOT')
1243 OR (p_mode = g_AWARD_AUTO_RECOMMEND AND l_group_type = 'LOT')
1244 OR (p_mode = g_AWARD_GROUP_H AND l_group_type = 'GROUP')
1245 OR (p_mode = g_AWARD_GROUP AND l_group_type = 'GROUP') ) THEN
1246 award_bi_subline (
1247 p_auction_id,
1248 p_award_lines(l_index).bid_number,
1249 p_award_lines(l_index).line_number,
1250 p_award_lines(l_index).award_status,
1251 p_award_lines(l_index).award_date,
1252 p_auctioneer_id );
1253 ELSIF ( (p_mode = g_AWARD_MULTIPLE_LINES OR p_mode = g_AWARD_LINE
1254 OR p_mode = g_AWARD_LINE_H OR p_mode= g_AWARD_AUTO_RECOMMEND)
1255 AND (l_group_type = 'GROUP_LINE') ) THEN
1256
1257 -- get parent line number
1258 SELECT parent_line_number INTO l_parent_line_number
1259 FROM pon_auction_item_prices_all
1260 WHERE auction_header_id = p_auction_id
1261 AND line_number = p_award_lines(l_index).line_number;
1262
1263 /* Key will be bid:group 1234:56 */
1264 l_bid_line_key := to_char(p_award_lines(l_index).bid_number) || ':' || to_char(l_parent_line_number);
1265 IF NOT (l_bid_group_map.exists(l_bid_line_key)) THEN
1266 l_bid_group_map(l_bid_line_key) := l_bid_line_key;
1267 END IF;
1268 END IF;
1269 END LOOP;
1270 l_bid_line_key := l_bid_group_map.FIRST;
1271 WHILE l_bid_line_key IS NOT NULL LOOP
1272 -- update bid group
1273 l_bid_number := to_number(SUBSTR(l_bid_line_key ,1, instr(l_bid_line_key, ':') -1 ));
1274 l_parent_line_number := to_number(SUBSTR(l_bid_line_key, instr(l_bid_line_key, ':') +1));
1275 -- update parent group
1276 update_bi_group_award(
1277 p_auction_id,
1278 l_bid_number,
1279 l_parent_line_number,
1280 sysdate,
1281 p_auctioneer_id
1282 );
1283
1284 l_bid_line_key := l_bid_group_map.NEXT(l_bid_line_key);
1285 END LOOP;
1286
1287 IF (g_debug_mode = 'Y') THEN
1288 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1289 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'Returning to the caller.....');
1290 END IF;
1291 END IF;
1292
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295 IF (g_debug_mode = 'Y') THEN
1296 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1297 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.....');
1298 END IF;
1299 END IF;
1300 RAISE;
1301 --
1302 END update_bid_item_prices;
1303 --
1304 --
1305 PROCEDURE update_single_bid_item_prices
1306 (
1307 p_bid_number IN NUMBER,
1308 p_line_number IN NUMBER,
1309 p_award_status IN VARCHAR2,
1310 p_award_quantity IN NUMBER,
1311 p_award_date IN DATE,
1312 p_auctioneer_id IN NUMBER
1313 )
1314 IS
1315 l_award_price NUMBER;
1316 BEGIN
1317
1318 IF (g_debug_mode = 'Y') THEN
1319 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1320
1321 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);
1322
1323 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);
1324
1325 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES',' ;p_award_status : ' || p_award_status);
1326
1327 END IF;
1328 END IF;
1329
1330 --
1331 -- Cost Factors Enhancements
1332 -- If award_quantity is not zero or null then use the per unit and fixed amount component and award
1333 -- quantity to calculate the award price
1334 --
1335 SELECT decode(p_award_status, 'REJECTED', null,
1336 decode(nvl(p_award_quantity,0), 0,pbip.price,
1337 pbip.per_unit_price_component + pbip.fixed_amount_component /p_award_quantity))
1338 INTO l_award_price
1339 FROM pon_bid_item_prices pbip,
1340 pon_auction_item_prices_all paip
1341 WHERE pbip.bid_number = p_bid_number
1342 AND pbip.line_number = p_line_number
1343 AND paip.auction_header_id = pbip.auction_header_id
1344 AND paip.line_number = pbip.line_number;
1345
1346 IF (g_debug_mode = 'Y') THEN
1347 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1348 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'award_price: ' || l_award_price);
1349 END IF;
1350 END IF;
1351
1352 --
1353 -- as this procedure will be called only if price tiers are not applicable so reseting the award_Shipment_number
1354 --
1355 UPDATE PON_BID_ITEM_PRICES
1356 SET award_quantity = p_award_quantity,
1357 award_price = l_award_price,
1358 award_status = p_award_status,
1359 award_date = p_award_date,
1360 last_update_date = p_award_date,
1361 last_updated_by = p_auctioneer_id,
1362 award_shipment_number = NULL
1363 WHERE Bid_number = p_bid_number AND
1364 Line_Number = p_line_number;
1365
1366 IF (g_debug_mode = 'Y') THEN
1367 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1368 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....');
1369 END IF;
1370 END IF;
1371
1372 END update_single_bid_item_prices;
1373
1374 /*==========================================================================================================================
1375 * PROCEDURE : upd_single_bid_item_prices_qt
1376 * PARAMETERS: 1. p_bid_number - bid number for which the award_price and shipment no to be updated.
1377 * 2. p_line_number - corresponding line number
1378 * 3. p_award_status - award status 'AWARDED' or 'REJECTED'
1379 * 4. p_award_quantity - The quantity awarded
1380 * 5. p_award_date -- Award Datw
1381 * 6. p_auctioneer_id - Id of person who is saving award
1382 * 7. p_award_shipment_number - Quantity awarded falls in the tiers range corresponding to the shipment number
1383 * COMMENT : This procedure calculates the award price based on the per unit and fixed amount component and
1384 * corresponding to the award shipment number. PON_BID_ITEM_PRICES is updated accordingly
1385 *==========================================================================================================================*/
1386 PROCEDURE upd_single_bid_item_prices_qt
1387 (
1388 p_bid_number IN NUMBER,
1389 p_line_number IN NUMBER,
1390 p_award_status IN VARCHAR2,
1391 p_award_quantity IN NUMBER,
1392 p_award_date IN DATE,
1393 p_auctioneer_id IN NUMBER,
1394 p_award_shipment_number IN NUMBER
1395 )
1396 IS
1397 l_award_price NUMBER;
1398 BEGIN
1399
1400 IF (g_debug_mode = 'Y') THEN
1401 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1402 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);
1403 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);
1404 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);
1405 END IF;
1406 END IF;
1407
1408 --
1409 -- Cost Factors Enhancements
1410 -- If award_quantity is not zero or null then use the per unit and fixed amount component and award
1411 -- quantity to calculate the award price
1412 --
1413
1414 SELECT DECODE(p_award_status, 'REJECTED', NULL,
1415 DECODE (NVL(p_award_quantity,0), 0, pbs.price,
1416 pbs.per_unit_price_component+pbip.fixed_amount_component/p_award_quantity))
1417 INTO l_award_price
1418 FROM pon_bid_item_prices pbip,
1419 pon_auction_item_prices_all paip,
1420 pon_bid_shipments pbs
1421 WHERE pbip.bid_number = p_bid_number
1422 AND pbip.line_number = p_line_number
1423 AND paip.auction_header_id = pbip.auction_header_id
1424 AND paip.line_number = pbip.line_number
1425 AND pbs.bid_number = pbip.bid_number
1426 AND pbs.line_number = pbip.line_number
1427 AND pbs.shipment_number = p_award_shipment_number;
1428
1429 IF (g_debug_mode = 'Y') THEN --{
1430 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN --}
1431 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPD_SINGLE_BID_ITEM_PRICES_QT', 'award_price: ' || l_award_price);
1432 END IF; --}
1433 END IF; --}
1434
1435 UPDATE PON_BID_ITEM_PRICES
1436 SET award_quantity = p_award_quantity,
1437 award_status = p_award_status,
1438 award_date = p_award_date,
1439 last_update_date = p_award_date,
1440 last_updated_by = p_auctioneer_id,
1441 award_price = l_award_price,
1442 award_shipment_number = p_award_shipment_number
1443 WHERE Bid_number = p_bid_number AND
1444 Line_Number = p_line_number;
1445
1446 IF (g_debug_mode = 'Y') THEN
1447 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1448 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....');
1449 END IF;
1450 END IF;
1451
1452 END upd_single_bid_item_prices_qt;
1453
1454 --
1455 --
1456 PROCEDURE update_bid_headers
1457 (
1458 p_auction_id IN NUMBER,
1459 p_auctioneer_id IN NUMBER,
1460 p_awarded_bid_headers IN t_awarded_bid_headers DEFAULT t_emptytbl, -- FPK: CPA
1461 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
1462 )
1463 IS
1464 --
1465 CURSOR c_active_bids (c_auction_id NUMBER) is
1466 SELECT bh.bid_number
1467 FROM pon_bid_headers bh
1468 WHERE bh.auction_header_id = c_auction_id
1469 AND bid_status = 'ACTIVE';
1470
1471 l_active_bids_rec c_active_bids%ROWTYPE;
1472
1473 -- FPK: CPA
1474 l_index PLS_INTEGER;
1475 l_bid_headers_count PLS_INTEGER :=0; -- generic pon_bid_headers index
1476
1477 -- Declaration of individual elements to avoid ORA-3113 error because
1478 -- FORALL does not allow update of elements using rec(i).field
1479 l_bid_number_tbl Number_tbl_type;
1480 l_award_status_tbl Char25_tbl_type;
1481 l_award_date_tbl Date_tbl_type;
1482 --
1483 BEGIN
1484 IF p_neg_has_lines = 'Y' THEN -- FPK: CPA
1485 OPEN c_active_bids (p_auction_id);
1486 LOOP
1487 FETCH c_active_bids into l_active_bids_rec;
1488 EXIT WHEN c_active_bids%NOTFOUND;
1489 update_single_bid_header (l_active_bids_rec.bid_number,
1490 p_auctioneer_id );
1491 END LOOP;
1492 CLOSE c_active_bids;
1493 ELSE -- negotiation does not have lines
1494 -- Loop through the matrix to update bid headers
1495 -- Map all values into single table arrays to avoid Oracle errors
1496 -- caused by using rec(i).field
1497 IF p_awarded_bid_headers.count > 0 THEN
1498 FOR l_index IN p_awarded_bid_headers.first..p_awarded_bid_headers.last
1499 LOOP
1500 l_bid_headers_count := l_bid_headers_count + 1;
1501 l_bid_number_tbl(l_bid_headers_count) := p_awarded_bid_headers(l_index).bid_number;
1502 l_award_status_tbl(l_bid_headers_count):= p_awarded_bid_headers(l_index).award_status;
1503 l_award_date_tbl(l_bid_headers_count) := p_awarded_bid_headers(l_index).award_date;
1504
1505 END LOOP;
1506 END IF;
1507
1508 FORALL k IN 1..l_bid_headers_count
1509 UPDATE PON_BID_HEADERS
1510 SET AWARD_STATUS = l_award_status_tbl(k),
1511 AWARD_DATE = l_award_date_tbl(k), /* new column created as part of CPA project.
1512 It will be updated only when negotiation does
1513 not have lines. */
1514 last_update_date = SYSDATE,
1515 last_updated_by = p_auctioneer_id
1516 WHERE bid_number = l_bid_number_tbl(k);
1517 END IF;
1518
1519 END update_bid_headers;
1520 --
1521 --
1522 PROCEDURE update_single_bid_header
1523 (
1524 p_bid_number IN NUMBER,
1525 p_auctioneer_id IN NUMBER
1526 )
1527 IS
1528 --
1529 CURSOR c_bid_lines (c_bid_number NUMBER) is
1530
1531 SELECT bi.Line_number,
1532 bi.award_status,
1533 nvl(bi.award_price , bi.price) * bi.award_quantity award_price
1534 FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
1535 WHERE bi.bid_number = c_bid_number
1536 and bi.auction_header_id = ai.auction_header_id
1537 and bi.line_number = ai.line_number
1538 and ai.group_type in ('LOT', 'LINE', 'GROUP_LINE');
1539
1540 l_bid_lines_rec c_bid_lines%ROWTYPE;
1541 --
1542 l_award_status VARCHAR2(30);
1543 l_awarded_lines NUMBER;
1544 l_rejected_lines NUMBER;
1545 l_total_lines NUMBER ;
1546 l_award_amount NUMBER;
1547 l_contract_type VARCHAR2(20);
1548 --
1549 --
1550 BEGIN
1551 l_award_amount := null;
1552 l_awarded_lines := 0;
1553 l_rejected_lines := 0;
1554 l_total_lines := 0;
1555 l_award_status := null;
1556 --
1557 SELECT ah.contract_type INTO l_contract_type
1558 FROM pon_auction_headers_all ah, pon_bid_headers bh
1559 WHERE bh.bid_number = p_bid_number
1560 AND bh.auction_header_id = ah.auction_header_id;
1561 --
1562 OPEN c_bid_lines (p_bid_number);
1563 LOOP
1564 FETCH c_bid_lines into l_bid_lines_rec;
1565 EXIT WHEN c_bid_lines%NOTFOUND;
1566 l_total_lines := l_total_lines + 1;
1567 IF l_bid_lines_rec.AWARD_STATUS = 'AWARDED' THEN
1568 l_awarded_lines := l_awarded_lines + 1;
1569 END IF;
1570 IF l_bid_lines_rec.AWARD_STATUS = 'REJECTED' THEN
1571 l_rejected_lines := l_rejected_lines + 1;
1572 END IF;
1573 IF l_contract_type = 'STANDARD' THEN
1574 l_award_amount := nvl(l_award_amount,0) + nvl(l_bid_lines_rec.award_price,0);
1575 END IF;
1576 END LOOP;
1577
1578 CLOSE c_bid_lines;
1579 IF (l_awarded_lines <> 0) AND (l_awarded_lines = l_total_lines) THEN
1580 l_award_status := 'AWARDED';
1581 ELSIF (l_rejected_lines <> 0) AND (l_rejected_lines = l_total_lines) THEN
1582 l_award_status := 'REJECTED';
1583 ELSIF l_awarded_lines > 0 THEN
1584 l_award_status := 'PARTIAL';
1585 END IF;
1586 --
1587 UPDATE PON_BID_HEADERS
1588 SET AWARD_STATUS = l_award_status,
1589 total_award_amount = l_award_amount,
1590 last_update_date = SYSDATE,
1591 last_updated_by = p_auctioneer_id
1592 WHERE bid_number = p_bid_number;
1593 --
1594 END update_single_bid_header;
1595 --
1596 --
1597 PROCEDURE update_auction_item_prices
1598 (
1599 p_auction_id IN NUMBER,
1600 p_line_number IN NUMBER,
1601 p_award_date IN DATE,
1602 p_auctioneer_id IN NUMBER,
1603 p_mode IN VARCHAR2
1604 )
1605 IS
1606 CURSOR c_auction_items (c_auction_id NUMBER) IS
1607 SELECT line_number, group_type
1608 FROM pon_auction_item_prices_all
1609 WHERE auction_header_id = c_auction_id;
1610 l_auction_items_rec c_auction_items%ROWTYPE;
1611 --
1612 CURSOR c_item_sublines (c_auction_id NUMBER, c_parent_line_number NUMBER) IS
1613 SELECT line_number, group_type
1614 FROM pon_auction_item_prices_all
1615 WHERE auction_header_id = c_auction_id
1616 AND parent_line_number = c_parent_line_number;
1617
1618 l_item_sublines_rec c_item_sublines%ROWTYPE;
1619 --
1620 l_group_type pon_auction_item_prices_all.group_type%TYPE;
1621 --
1622 BEGIN
1623
1624 OPEN c_auction_items(p_auction_id);
1625
1626 IF (p_mode = g_AWARD_QUOTE OR p_mode = g_AWARD_MULTIPLE_LINES
1627 OR p_mode=g_AWARD_AUTO_RECOMMEND OR p_mode = g_AWARD_OPTIMIZATION) THEN
1628 LOOP
1629 FETCH c_auction_items INTO l_auction_items_rec;
1630 EXIT WHEN c_auction_items%NOTFOUND;
1631 update_single_auction_item(p_auction_id,
1632 l_auction_items_rec.line_number,
1633 p_auctioneer_id, p_mode);
1634 END LOOP;
1635 CLOSE c_auction_items;
1636 ELSE
1637 update_single_auction_item (p_auction_id,
1638 p_line_number,
1639 p_auctioneer_id,
1640 p_mode);
1641
1642 SELECT group_type INTO l_group_type FROM pon_auction_item_prices_all
1643 WHERE auction_header_id = p_auction_id
1644 AND line_number = p_line_number;
1645 IF ((p_mode = g_AWARD_LINE AND l_group_type = 'LOT')
1646 OR (p_mode = g_AWARD_LINE_H AND l_group_type = 'LOT')
1647 OR (p_mode = g_AWARD_GROUP_H AND l_group_type = 'GROUP')
1648 OR (p_mode = g_AWARD_GROUP AND l_group_type = 'GROUP') ) THEN
1649 --
1650 OPEN c_item_sublines (p_auction_id, p_line_number);
1651 --
1652 LOOP
1653 fetch c_item_sublines into l_item_sublines_rec;
1654 EXIT WHEN c_item_sublines%NOTFOUND;
1655 -- update the child lines
1656
1657 update_single_auction_item(p_auction_id,
1658 l_item_sublines_rec.line_number,
1659 p_auctioneer_id,
1660 p_mode);
1661 END LOOP;
1662 --
1663 ELSIF ((p_mode = g_AWARD_LINE OR p_mode = g_AWARD_LINE_H)
1664 AND l_group_type = 'GROUP_LINE') THEN
1665 update_ai_group_award(p_auction_id,
1666 p_line_number,
1667 p_award_date,
1668 p_auctioneer_id);
1669
1670 END IF;
1671 --
1672 END IF;
1673 --
1674 --
1675 END update_auction_item_prices;
1676 --
1677 --
1678 PROCEDURE update_single_auction_item
1679 (
1680 p_auction_id IN NUMBER,
1681 p_line_number IN NUMBER,
1682 p_auctioneer_id IN NUMBER,
1683 p_mode IN pon_auction_item_prices_all.award_mode%type
1684 )
1685 IS
1686 CURSOR c_bid_items (c_auction_id NUMBER, c_line_number NUMBER) IS
1687 SELECT bi.Line_number,
1688 ai.order_type_lookup_code,
1689 bi.award_status,
1690 bi.award_quantity,
1691 ai.group_type
1692 FROM pon_bid_item_prices bi,
1693 pon_bid_headers bh,
1694 pon_auction_item_prices_all ai
1695 WHERE bi.auction_header_id = c_auction_id
1696 AND bi.line_number = c_line_number
1697 AND bh.bid_status = 'ACTIVE'
1698 AND bh.auction_header_id = bi.auction_header_id
1699 AND bh.bid_number = bi.bid_number
1700 AND ai.auction_header_id = bi.auction_header_id
1701 AND ai.line_number = bi.line_number;
1702 l_bid_items_rec c_bid_items%ROWTYPE;
1703 l_award_status VARCHAR2(20);
1704 l_award_made BOOLEAN;
1705 l_award_quantity NUMBER;
1706 l_line_type VARCHAR2(20);
1707 l_contract_type VARCHAR2(20);
1708 l_group_type VARCHAR2(20);
1709 l_item_award_mode pon_auction_item_prices_all.award_mode%type;
1710 --
1711 BEGIN
1712 l_award_status := null;
1713 l_award_quantity := null;
1714 l_line_type := NULL;
1715 l_award_made := FALSE;
1716 l_item_award_mode := null;
1717 --
1718 SELECT ah.contract_type INTO l_contract_type
1719 FROM pon_auction_headers_all ah
1720 WHERE ah.auction_header_id = p_auction_id;
1721 --
1722 OPEN c_bid_items(p_auction_id, p_line_number) ;
1723 LOOP
1724 FETCH c_bid_items INTO l_bid_items_rec;
1725 EXIT WHEN c_bid_items%NOTFOUND;
1726 l_line_type := l_bid_items_rec.order_type_lookup_code;
1727 l_group_type:= l_bid_items_rec.group_type;
1728 IF l_bid_items_rec.award_status = 'AWARDED' THEN
1729 l_award_quantity := nvl(l_award_quantity,0) + l_bid_items_rec.award_quantity;
1730 IF (NOT l_award_made) THEN
1731 l_award_made := TRUE;
1732 END IF;
1733 END IF;
1734 IF ((l_award_status is null)
1735 AND ((l_bid_items_rec.award_status = 'AWARDED')
1736 OR (l_bid_items_rec.award_status = 'REJECTED')
1737 OR (l_bid_items_rec.award_status = 'PARTIAL'))
1738 ) THEN
1739 l_award_status := 'AWARDED';
1740 END IF;
1741 END LOOP;
1742 CLOSE c_bid_items;
1743 --
1744 IF (l_award_made) AND (NOT (l_contract_type = 'STANDARD' AND l_line_type = 'QUANTITY'))
1745 AND (l_group_type <> 'LOT_LINE' AND l_group_type <> 'GROUP') THEN
1746 l_award_quantity := 1;
1747 END IF ;
1748 --
1749 -- set award mode = GROUP for group level award.
1750 IF(p_mode = g_AWARD_GROUP OR p_mode = g_AWARD_GROUP_H) AND l_group_type = 'GROUP' THEN
1751 l_item_award_mode := 'GROUP';
1752 END IF;
1753 UPDATE pon_auction_item_prices_all
1754 SET award_status = l_award_status,
1755 awarded_quantity = l_award_quantity,
1756 award_mode = l_item_award_mode,
1757 last_update_date = SYSDATE,
1758 last_updated_by = p_auctioneer_id
1759 WHERE auction_header_id = p_auction_id
1760 AND line_number = p_line_number;
1761 --
1762 END update_single_auction_item;
1763 --
1764 --
1765 PROCEDURE update_auction_headers
1766 (
1767 p_auction_id IN NUMBER,
1768 p_mode IN VARCHAR2,
1769 p_award_date IN DATE,
1770 p_auctioneer_id IN NUMBER,
1771 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
1772 )
1773 IS
1774 --
1775 /*
1776 CURSOR c_auction_lines (c_auction_id NUMBER) is
1777 SELECT Line_number, award_status
1778 FROM pon_auction_item_prices_all
1779 WHERE auction_header_id = c_auction_id
1780 AND number_of_bids > 0
1781 AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
1782
1783 l_auction_lines_rec c_auction_lines%ROWTYPE;
1784 */
1785 --
1786 l_award_status VARCHAR2(20);
1787 l_award_mode VARCHAR2(20);
1788 l_awarded_lines NUMBER;
1789 l_total_lines NUMBER;
1790
1791 l_awarded_bids NUMBER; -- FPK: CPA
1792 --
1793 BEGIN
1794 l_awarded_lines := 0;
1795 l_total_lines := 0;
1796 l_award_status := 'NO';
1797 l_award_mode := null;
1798
1799 /* CASE p_mode
1800 WHEN g_AWARD_QUOTE THEN l_Award_mode := 'HEADER';
1801 WHEN g_AWARD_MULTIPLE_LINES THEN l_award_mode := 'MULTIPLE_LINES';
1802 WHEN g_AWARD_LINE THEN l_award_mode := 'LINE';
1803 WHEN g_AWARD_LINE_H THEN l_award_mode := 'LINE';
1804 WHEN g_AWARD_AUTO_RECOMMEND THEN l_award_mode := 'LINE';
1805 ELSE l_Award_Status := null;
1806 END CASE;
1807 */
1808 IF p_mode = g_AWARD_QUOTE THEN
1809 l_Award_mode := 'HEADER';
1810 ELSIF p_mode = g_AWARD_MULTIPLE_LINES THEN
1811 l_award_mode := 'MULTIPLE_LINES';
1812 ELSIF p_mode = g_AWARD_LINE THEN
1813 l_award_mode := 'LINE';
1814 ELSIF p_mode = g_AWARD_LINE_H THEN
1815 l_award_mode := 'LINE';
1816 ELSIF p_mode = g_AWARD_GROUP THEN
1817 l_award_mode := 'LINE';
1818 ELSIF p_mode = g_AWARD_GROUP_H THEN
1819 l_award_mode := 'LINE';
1820 ELSIF p_mode = g_AWARD_AUTO_RECOMMEND THEN
1821 l_award_mode := 'LINE';
1822 ELSIF p_mode = g_AWARD_OPTIMIZATION THEN
1823 l_award_mode := 'LINE';
1824 ELSE l_award_mode := null;
1825 END IF;
1826 --
1827 IF p_neg_has_lines = 'Y' THEN -- FPK: CPA
1828
1829 /*
1830 large-auction-support changes
1831 rather than looping over all the lines, we will simply
1832 execute a single query
1833 */
1834
1835 SELECT count(Line_number), sum(decode(award_status, 'AWARDED', 1, 0))
1836 INTO l_total_lines, l_awarded_lines
1837 FROM pon_auction_item_prices_all
1838 WHERE auction_header_id = p_auction_id
1839 AND number_of_bids > 0
1840 AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
1841
1842 IF ((l_awarded_lines <> 0) AND (l_awarded_lines = l_total_lines) ) THEN
1843 l_award_status := 'AWARDED';
1844 ELSIF (l_awarded_lines > 0) THEN
1845 l_award_status := 'PARTIAL';
1846 END IF;
1847 ELSE -- negotiation does not have lines
1848 BEGIN
1849 select 'AWARDED' -- it means an award decision was made
1850 into l_award_status
1851 from dual
1852 where exists (select 1
1853 from pon_bid_headers
1854 where auction_header_id = p_auction_id
1855 and bid_status = 'ACTIVE'
1856 and award_status IN ('AWARDED', 'REJECTED'));
1857 EXCEPTION
1858 WHEN NO_DATA_FOUND THEN
1859 NULL; -- award_status is set to 'NO' in the beggining of the procedure.
1860 END;
1861
1862 END IF; -- IF p_neg_has_lines = 'Y'
1863
1864 UPDATE PON_Auction_HEADERS_all
1865 SET AWARD_STATUS = l_award_status,
1866 award_mode = l_award_mode,
1867 award_date = p_award_date,
1868 last_updated_by = p_auctioneer_id
1869 -- modified after last update date check
1870 --award_approval_status = 'REQUIRED'
1871 --last_update_date = SYSDATE
1872 WHERE auction_header_id = p_auction_id ;
1873
1874 UPDATE PON_AUCTION_HEADERS_ALL
1875 SET award_approval_status = 'REQUIRED'
1876 WHERE auction_header_id = p_auction_id
1877 AND nvl(award_approval_flag, 'N') = 'Y';
1878 --
1879 END update_auction_headers;
1880 --
1881 --
1882 -- Updates the award_approval_status and sets to REQUIRED
1883 -- since the award is modified, sets the upadated by column as well.
1884 PROCEDURE update_award_agreement_amount
1885 (
1886 p_auction_id IN NUMBER,
1887 p_auctioneer_id IN NUMBER
1888 )
1889 IS
1890 BEGIN
1891 -- Updates approval_status if approval flag is set
1892 UPDATE PON_AUCTION_HEADERS_ALL
1893 SET award_approval_status = 'REQUIRED'
1894 WHERE auction_header_id = p_auction_id
1895 AND nvl(award_approval_flag, 'N') = 'Y';
1896 --
1897 -- Updates last update date etc since award is modified.
1898 UPDATE PON_Auction_HEADERS_all
1899 SET award_date = SYSDATE,
1900 last_updated_by = p_auctioneer_id,
1901 last_update_date = SYSDATE
1902 WHERE auction_header_id = p_auction_id ;
1903 --
1904 END update_award_agreement_amount;
1905 --
1906 --
1907 PROCEDURE bulk_update_pon_acceptances
1908 ( p_auction_header_id IN NUMBER,
1909 p_line_number IN NUMBER,
1910 p_note_to_accepted IN VARCHAR2,
1911 p_note_to_rejected IN VARCHAR2,
1912 p_award_date IN DATE,
1913 p_auctioneer_id IN NUMBER,
1914 p_mode IN VARCHAR2
1915 )
1916 IS
1917 BEGIN
1918 IF(p_line_number > 0 ) THEN
1919 IF (p_mode = g_AWARD_GROUP OR p_mode = g_AWARD_GROUP_H) THEN
1920 -- Group Level Awards
1921 -- Insert empty notes for group lines
1922 -- Delete notes for a line
1923 DELETE FROM pon_acceptances
1924 WHERE auction_header_id = p_auction_header_id
1925 AND line_number IN (SELECT line_number FROM pon_auction_item_prices_all
1926 WHERE parent_line_number = p_line_number
1927 AND auction_header_id = p_auction_header_id);
1928 INSERT INTO pon_acceptances (
1929 acceptance_id,
1930 auction_header_id,
1931 auction_line_number,
1932 bid_number,
1933 line_number,
1934 acceptance_type,
1935 acceptance_date,
1936 reason,
1937 creation_date,
1938 created_by)
1939 SELECT pon_acceptances_s.nextval,
1940 bi.auction_header_id,
1941 bi.auction_line_number,
1942 bi.bid_number,
1943 bi.line_number,
1944 bi.award_status,
1945 p_award_date,
1946 null,
1947 p_award_date,
1948 p_auctioneer_id
1949 FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
1950 WHERE bi.auction_header_id = ai.auction_header_id
1951 AND ai.line_number = bi.line_number
1952 AND (bi.award_status = 'AWARDED'
1953 OR bi.award_status = 'REJECTED')
1954 AND bi.bid_number = bh.bid_number
1955 AND bh.bid_status = 'ACTIVE'
1956 AND ai.auction_header_id = p_auction_header_id
1957 AND ai.parent_line_number = p_line_number;
1958 --
1959 ELSE
1960 -- Award Line Mode
1961 -- Delete notes for a line
1962 DELETE FROM pon_acceptances
1963 WHERE auction_header_id = p_auction_header_id
1964 AND line_number = p_line_number;
1965 INSERT INTO pon_acceptances (
1966 acceptance_id,
1967 auction_header_id,
1968 auction_line_number,
1969 bid_number,
1970 line_number,
1971 acceptance_type,
1972 acceptance_date,
1973 reason,
1974 creation_date,
1975 created_by)
1976 SELECT pon_acceptances_s.nextval,
1977 bi.auction_header_id,
1978 bi.auction_line_number,
1979 bi.bid_number,
1980 bi.line_number,
1981 bi.award_status,
1982 p_award_date,
1983 decode (bi.award_status,
1984 'AWARDED', p_note_to_accepted,
1985 'REJECTED', p_note_to_rejected,
1986 null),
1987 SYSDATE,
1988 p_auctioneer_id
1989 FROM pon_bid_item_prices bi, pon_bid_headers bh
1990 WHERE bi.auction_header_id = p_auction_header_id
1991 AND bi.line_number = p_line_number
1992 AND (bi.award_status = 'AWARDED'
1993 OR bi.award_status = 'REJECTED')
1994 AND bi.bid_number = bh.bid_number
1995 AND bh.bid_status = 'ACTIVE';
1996 END IF;
1997 ELSE
1998 -- Header Level Award
1999 -- Delete notes for an auction
2000 DELETE FROM pon_acceptances
2001 WHERE auction_header_id = p_auction_header_id;
2002 --
2003 INSERT INTO pon_acceptances (
2004 acceptance_id,
2005 auction_header_id,
2006 auction_line_number,
2007 bid_number,
2008 line_number,
2009 acceptance_type,
2010 acceptance_date,
2011 reason,
2012 creation_date,
2013 created_by)
2014 SELECT pon_acceptances_s.nextval,
2015 bi.auction_header_id,
2016 bi.auction_line_number,
2017 bi.bid_number,
2018 bi.line_number,
2019 bi.award_status,
2020 p_award_date,
2021 decode (bi.award_status,
2022 'AWARDED', p_note_to_accepted,
2023 'REJECTED', p_note_to_rejected,
2024 null),
2025 p_award_date,
2026 p_auctioneer_id
2027 FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
2028 WHERE bi.auction_header_id = p_auction_header_id
2029 AND (bi.award_status = 'AWARDED'
2030 OR bi.award_status = 'REJECTED')
2031 AND bi.bid_number = bh.bid_number
2032 AND bh.bid_status = 'ACTIVE'
2033 AND bi.auction_header_id = ai.auction_header_id
2034 AND bi.line_number = ai.line_number
2035 AND ai.group_type IN ('LOT', 'LINE', 'GROUP_LINE');
2036 END IF;
2037 END bulk_update_pon_acceptances;
2038 --
2039 --
2040 FUNCTION get_award_status (award_outcome IN VARCHAR2)
2041 RETURN VARCHAR2
2042 IS
2043 --
2044 l_award_status VARCHAR2(20);
2045 BEGIN
2046 /*
2047 CASE award_outcome
2048 WHEN g_AWARD_OUTCOME_WIN THEN l_Award_Status := 'AWARDED';
2049 WHEN g_AWARD_OUTCOME_LOSE THEN l_Award_Status := 'REJECTED';
2050 WHEN g_AWARD_OUTCOME_NOAWARD THEN l_Award_Status := 'REJECTED';
2051 ELSE l_Award_Status := null;
2052 END CASE;
2053 */
2054 IF award_outcome = g_AWARD_OUTCOME_WIN THEN
2055 l_Award_Status := 'AWARDED';
2056 ELSIF award_outcome = g_AWARD_OUTCOME_LOSE THEN
2057 l_Award_Status := 'REJECTED';
2058 ELSIF award_outcome = g_AWARD_OUTCOME_NOAWARD THEN
2059 l_Award_Status := 'REJECTED';
2060 ELSE l_Award_Status := null;
2061 END IF;
2062
2063 RETURN l_award_status;
2064 --
2065 END get_award_status;
2066 --
2067 --
2068 PROCEDURE update_unawarded_acceptances
2069 ( p_auction_header_id IN NUMBER,
2070 p_line_number IN NUMBER,
2071 p_note_to_rejected IN VARCHAR2,
2072 p_award_date IN DATE,
2073 p_auctioneer_id IN NUMBER
2074 )
2075 IS
2076 BEGIN
2077 -- Award Line Mode
2078 -- Delete rejected notes for a line
2079 DELETE FROM pon_acceptances
2080 WHERE auction_header_id = p_auction_header_id
2081 AND line_number = p_line_number
2082 AND acceptance_type = 'REJECTED';
2083 -- insert rejection note for all rejected suppliers
2084 INSERT INTO pon_acceptances (
2085 acceptance_id,
2086 auction_header_id,
2087 auction_line_number,
2088 bid_number,
2089 line_number,
2090 acceptance_type,
2091 acceptance_date,
2092 reason,
2093 creation_date,
2094 created_by)
2095 SELECT pon_acceptances_s.nextval,
2096 bi.auction_header_id,
2097 bi.auction_line_number,
2098 bi.bid_number,
2099 bi.line_number,
2100 'REJECTED',
2101 p_award_date,
2102 p_note_to_rejected,
2103 p_award_date,
2104 p_auctioneer_id
2105 FROM pon_bid_item_prices bi, pon_bid_headers bh
2106 WHERE bi.auction_header_id = p_auction_header_id
2107 AND bi.line_number = p_line_number
2108 AND nvl(bi.award_status, 'NO') <> 'AWARDED' -- can be REJECTED/ NO
2109 AND bi.bid_number = bh.bid_number
2110 AND bh.bid_status = 'ACTIVE';
2111 END update_unawarded_acceptances;
2112 --
2113 --
2114 PROCEDURE update_notes_for_bid
2115 (
2116 p_bid_number IN NUMBER,
2117 p_note_to_supplier IN VARCHAR2,
2118 p_internal_note IN VARCHAR2,
2119 p_auctioneer_id IN NUMBER
2120 )
2121 IS
2122 BEGIN
2123 UPDATE pon_bid_headers
2124 SET Internal_note = p_internal_note,
2125 note_to_supplier = p_note_to_supplier
2126 WHERE bid_number = p_bid_number;
2127 END update_notes_for_bid;
2128 --
2129 --
2130 PROCEDURE clear_draft_awards
2131 (
2132 p_auction_header_id IN NUMBER,
2133 p_line_number IN NUMBER,
2134 p_award_date IN DATE,
2135 p_auctioneer_id IN NUMBER,
2136 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
2137 )
2138 IS
2139 BEGIN
2140
2141 IF p_neg_has_lines = 'Y' THEN -- FPK: CPA
2142
2143
2144 IF (p_line_number IS NULL OR p_line_number <= 0 )THEN
2145 -- Header level awards
2146 --Update award status to REJECTED for all the bids
2147 UPDATE pon_bid_item_prices
2148 SET award_status = 'REJECTED',
2149 award_quantity = NULL,
2150 award_date = p_award_date,
2151 last_update_date = p_award_date,
2152 last_updated_by = p_auctioneer_id,
2153 award_shipment_number = NULL,
2154 award_price = NULL
2155 WHERE bid_number IN (
2156 SELECT bid_number
2157 FROM pon_bid_headers
2158 WHERE auction_header_id = p_auction_header_id
2159 AND bid_status = 'ACTIVE'
2160 );
2161
2162 -- Delete All Awards since it is a header-level awarding
2163 DELETE FROM pon_acceptances
2164 WHERE auction_header_id = p_auction_header_id;
2165
2166 -- reset the award mode at auction item level
2167 UPDATE pon_auction_item_prices_all
2168 SET award_mode = null
2169 WHERE auction_header_id = p_auction_header_id;
2170 ELSE
2171 -- Group Level awards need to be rejected first
2172 --Update award status to REJECTED for all the bids
2173 UPDATE pon_bid_item_prices
2174 SET award_status = 'REJECTED',
2175 award_quantity = NULL,
2176 award_date = p_award_date,
2177 last_update_date = p_award_date,
2178 last_updated_by = p_auctioneer_id,
2179 award_shipment_number = NULL,
2180 award_price = NULL
2181 WHERE bid_number IN (
2182 SELECT bid_number
2183 FROM pon_bid_headers
2184 WHERE auction_header_id = p_auction_header_id
2185 AND bid_status = 'ACTIVE'
2186 )
2187 AND line_number IN (SELECT line_number
2188 FROM pon_auction_item_prices_all
2189 WHERE auction_header_id = p_auction_header_id
2190 AND (line_number = p_line_number
2191 OR parent_line_number = p_line_number));
2192
2193 -- Delete All group line awards since it is a group-level awarding
2194 DELETE FROM pon_acceptances
2195 WHERE auction_header_id = p_auction_header_id
2196 AND line_number IN (SELECT line_number
2197 FROM pon_auction_item_prices_all
2198 WHERE auction_header_id = p_auction_header_id
2199 AND parent_line_number = p_line_number);
2200 END IF;
2201 END IF;
2202
2203 /* FPK: CPA
2204 Reset notes for all the bids and update all active bids award status to REJECTED no matter
2205 if negotiation has lines or not. Previoulsy award_status was not being updated to REJECTED when
2206 negotiation had lines, but there is no harm in doing so at this point, as award_status will be
2207 updated later in update_single_bid_header procedure. */
2208
2209 UPDATE pon_bid_headers
2210 SET award_status = 'REJECTED',
2211 note_to_supplier = NULL,
2212 internal_note = NULL,
2213 po_agreed_amount = NULL,
2214 last_update_date = SYSDATE,
2215 last_updated_by = p_auctioneer_id
2216 WHERE auction_header_id = p_auction_header_id
2217 AND bid_status = 'ACTIVE';
2218 END clear_draft_awards;
2219 --
2220 --
2221 --
2222 --
2223 PROCEDURE clear_awards_recommendation
2224 (
2225 p_auction_header_id NUMBER,
2226 p_award_date DATE,
2227 p_auctioneer_id IN NUMBER
2228 )
2229 IS
2230 BEGIN
2231 --Update award status to REJECTED for all the bids
2232 UPDATE pon_bid_item_prices
2233 SET award_status = 'REJECTED',
2234 award_quantity = NULL,
2235 award_date = p_award_date,
2236 last_update_date = SYSDATE,
2237 last_updated_by = p_auctioneer_id,
2238 award_price = NULL
2239 WHERE bid_number IN (
2240 SELECT bid_number
2241 FROM pon_bid_headers
2242 WHERE auction_header_id = p_auction_header_id
2243 AND bid_status = 'ACTIVE'
2244 );
2245 --reset notes for all the bids
2246 UPDATE pon_bid_headers
2247 SET po_agreed_amount = NULL,
2248 last_update_date = SYSDATE,
2249 last_updated_by = p_auctioneer_id
2250 WHERE bid_number IN (
2251 SELECT bid_number
2252 FROM pon_bid_headers
2253 WHERE auction_header_id = p_auction_header_id
2254 AND bid_status = 'ACTIVE'
2255 );
2256 END clear_awards_recommendation;
2257
2258 --
2259 --
2260 ----------------------------------------------------------------
2261 -- handles accepting an AutoAward Scenario
2262 ----------------------------------------------------------------
2263 --
2264 PROCEDURE accept_award_scenario
2265 (
2266 p_scenario_id IN NUMBER,
2267 p_auctioneer_id IN NUMBER,
2268 p_last_update_date IN DATE,
2269 x_status OUT NOCOPY VARCHAR2
2270 )
2271 IS
2272
2273 l_auction_header_id NUMBER;
2274 l_mode VARCHAR2(50);
2275 l_batch_id NUMBER;
2276 l_num_of_non_shortlisted_supp NUMBER;
2277 BEGIN
2278
2279 -- retrieve auction header id and batch id
2280 BEGIN
2281 SELECT COUNT(DISTINCT pbh.bid_number)
2282 INTO l_num_of_non_shortlisted_supp
2283 FROM pon_optimize_results por, pon_bid_headers pbh
2284 WHERE por.bid_number = pbh.bid_number
2285 AND pbh.shortlist_flag = 'N'
2286 AND por.scenario_id = p_scenario_id;
2287
2288 IF (l_num_of_non_shortlisted_supp > 0) THEN
2289 x_status := 'NOT_SHORTLISTED';
2290 RETURN;
2291 END IF;
2292
2293 SELECT auction_header_id, pon_auction_summary_s.NEXTVAL
2294 INTO l_auction_header_id, l_batch_id
2295 FROM pon_optimize_scenarios
2296 WHERE scenario_id = p_scenario_id;
2297 EXCEPTION WHEN NO_DATA_FOUND THEN
2298 x_status := 'FAILURE';
2299 RETURN;
2300 END;
2301
2302 l_mode := g_AWARD_OPTIMIZATION;
2303
2304 -- insert award results into interface table
2305 INSERT into pon_auction_summary
2306 (batch_id,
2307 auction_id,
2308 bid_number,
2309 line_number,
2310 award_quantity,
2311 award_shipment_number)
2312 SELECT
2313 l_batch_id,
2314 l_auction_header_id,
2315 por.bid_number,
2316 por.line_number,
2317 por.award_quantity,
2318 por.award_shipment_number
2319 FROM pon_optimize_results por, pon_auction_item_prices_all paip,
2320 pon_auction_headers_all pah,
2321 pon_bid_item_prices pbip
2322 WHERE pah.auction_header_id = l_auction_header_id
2323 AND pah.auction_header_id = paip.auction_header_id
2324 AND por.bid_number = pbip.bid_number
2325 AND por.line_number = pbip.line_number
2326 AND por.scenario_id = p_scenario_id
2327 AND paip.line_number = por.line_number;
2328
2329
2330 -- save the award result
2331 save_award_recommendation(l_batch_id, p_auctioneer_id, p_last_update_date, l_mode, x_status);
2332
2333 IF (x_status = 'FAILURE') THEN
2334 RETURN;
2335 END IF;
2336
2337 -- unset the accepted date of the previously accepted scenario
2338 UPDATE pon_optimize_scenarios
2339 SET accepted_date = NULL
2340 WHERE accepted_date IS NOT NULL
2341 AND auction_header_id= l_auction_header_id;
2342
2343 -- set accepted date of the accepted scenario
2344 UPDATE pon_optimize_scenarios
2345 SET accepted_date = SYSDATE,
2346 last_update_date = SYSDATE,
2347 last_updated_by = p_auctioneer_id
2348 WHERE scenario_id = p_scenario_id;
2349
2350 -- clean up inteface table
2351 DELETE FROM pon_auction_summary
2352 WHERE batch_id = l_batch_id;
2353
2354 END accept_award_scenario;
2355
2356 --
2357 --
2358 ----------------------------------------------------------------
2359 -- procedure added by snatu on 08/15/03
2360 -- handles awarding in FPJ for system recommended awards
2361 ----------------------------------------------------------------
2362 --
2363 PROCEDURE save_award_recommendation
2364 (
2365 p_batch_id IN NUMBER,
2366 p_auctioneer_id IN NUMBER,
2367 p_last_update_date IN DATE,
2368 p_mode IN VARCHAR2,
2369 x_status OUT NOCOPY VARCHAR2
2370 )
2371 IS
2372 CURSOR c_reco_awards (c_batch_id NUMBER) IS
2373 SELECT
2374 pas.auction_id,
2375 pas.line_number,
2376 pas.bid_number,
2377 decode(p_mode,
2378 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),
2379 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,
2380 pas.bid_price,
2381 pas.trading_partner_id,
2382 pas.trading_partner_contact_id,
2383 pas.batch_id,
2384 ai.group_type,
2385 pas.award_shipment_number
2386 FROM pon_auction_summary pas
2387 , pon_auction_item_prices_all ai
2388 , pon_auction_headers_all ah
2389 WHERE
2390 pas.award_quantity >0
2391 AND pas.batch_id = c_batch_id
2392 AND ah.auction_header_id = pas.auction_id
2393 AND ai.auction_header_id = pas.auction_id
2394 AND ai.line_number = pas.line_number
2395 ORDER BY
2396 pas.line_number;
2397 l_reco_awards_rec c_reco_awards%ROWTYPE;
2398 --
2399 l_award_lines t_award_lines;
2400 l_matrix_index NUMBER;
2401 l_auction_header_id NUMBER;
2402 l_current_bid_number NUMBER;
2403 l_current_update_date DATE;
2404 l_award_date DATE;
2405
2406 BEGIN
2407
2408 l_current_bid_number := null;
2409 l_matrix_index := 0;
2410 l_award_date := SYSDATE;
2411 --
2412 -- Need to set award quantity and award_status and award_date
2413 OPEN c_reco_awards (p_batch_id);
2414 LOOP
2415 fetch c_reco_awards into l_reco_awards_rec;
2416 EXIT WHEN c_reco_awards%NOTFOUND;
2417 -- Get Auction Header Id only once
2418 IF (l_matrix_index = 0) THEN
2419 l_auction_header_id := l_reco_awards_rec.auction_id;
2420 END IF;
2421
2422 --DBMS_OUTPUT.PUT_LINE('l_auction_header_id' || l_auction_header_id);
2423 -- Construct Matrix for the awarded bids
2424 l_matrix_index := l_matrix_index + 1;
2425 l_award_lines(l_matrix_index).bid_number := l_reco_awards_rec.bid_number;
2426 l_award_lines(l_matrix_index).line_number := l_reco_awards_rec.line_number;
2427 l_award_lines(l_matrix_index).award_status := 'AWARDED';
2428 l_award_lines(l_matrix_index).award_quantity := l_reco_awards_rec.award_quantity;
2429 l_award_lines(l_matrix_index).award_date := l_award_date;
2430 l_award_lines(l_matrix_index).group_type := l_reco_awards_rec.group_type;
2431 l_award_lines(l_matrix_index).award_shipment_number := l_reco_awards_rec.award_shipment_number;
2432 END LOOP;
2433
2434 clear_awards_recommendation (l_auction_header_id, l_award_date, p_auctioneer_id);
2435 update_bid_item_prices(l_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
2436 /* FPK: CPA p_neg_has_lines parameter hardcoded to 'Y' as save_award_recommendation
2437 procedure will only be called if negotiation has lines */
2438 update_bid_headers(l_auction_header_id, p_auctioneer_id, t_emptytbl, 'Y');
2439 update_auction_item_prices(l_auction_header_id,null, l_award_date, p_auctioneer_id, p_mode);
2440 /* FPK: CPA p_neg_has_lines parameter hardcoded to 'Y' as save_award_recommendation
2441 procedure will only be called if negotiation has lines */
2442 update_auction_headers(l_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
2443
2444 bulk_update_pon_acceptances(
2445 l_auction_header_id,
2446 null, null, null,
2447 l_award_date, p_auctioneer_id, p_mode);
2448 --
2449 /* check if the auction has been modified by some other user
2450 If it has been modified, status returns failure
2451 else this is the only user modifying hte auction
2452 changes are committed to the database in the middle tier
2453 */
2454 IF (is_auction_not_updated (l_auction_header_id, p_last_update_date)) THEN
2455 x_status := 'SUCCESS';
2456 -- update the last update date
2457 UPDATE PON_Auction_HEADERS_all
2458 SET last_update_date = SYSDATE
2459 WHERE auction_header_id = l_auction_header_id;
2460 --
2461 ELSE
2462 x_status := 'FAILURE';
2463 END IF;
2464 --
2465 END save_award_recommendation;
2466
2467
2468 --
2469 --
2470 ----------------------------------------------------------------
2471 -- handles copying of a scenario
2472 ----------------------------------------------------------------
2473
2474 PROCEDURE copy_award_scenario
2475 (
2476 p_scenario_id IN NUMBER,
2477 p_user_id IN NUMBER,
2478 p_cost_scenario_flag IN VARCHAR2,
2479 x_cost_scenario_id OUT NOCOPY NUMBER,
2480 x_status OUT NOCOPY VARCHAR2
2481 )
2482 IS
2483
2484 l_fnd_user_id NUMBER;
2485 l_next_scenario_number NUMBER;
2486 l_num_constraints NUMBER;
2487 l_num_bid_classes NUMBER;
2488 l_auction_header_id NUMBER;
2489 l_dummy_scenario_number NUMBER;
2490 l_new_scenario_id NUMBER;
2491
2492 BEGIN
2493
2494 -- Derive fnd_user_id from tp_id
2495 -- This will not fail if more than one user setup w/ same buyer.
2496 -- But, this will reurn any of the matched user id in such cases
2497 BEGIN
2498 SELECT USER_ID
2499 INTO l_fnd_user_id
2500 FROM FND_USER
2501 WHERE PERSON_PARTY_ID = p_user_id
2502 AND NVL(END_DATE,SYSDATE+1) > SYSDATE;
2503 EXCEPTION
2504 WHEN TOO_MANY_ROWS THEN
2505 IF (NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y') THEN
2506 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2507 FND_LOG.string(log_level => FND_LOG.level_unexpected,
2508 module => 'pon.plsql.pon_award_pkg.copy_award_scenario',
2509 message => 'Multiple Users found for person_party_id:'||p_user_id);
2510 END IF;
2511 END IF;
2512
2513 SELECT USER_ID
2514 INTO l_fnd_user_id
2515 FROM FND_USER
2516 WHERE PERSON_PARTY_ID = p_user_id
2517 AND NVL(END_DATE,SYSDATE+1) > SYSDATE
2518 AND ROWNUM = 1;
2519 END;
2520
2521 -- derive auction_header_id
2522 select auction_header_id into l_auction_header_id
2523 from pon_optimize_scenarios
2524 where scenario_id = p_scenario_id;
2525
2526 -- store the next scenario id in a local variable
2527 select pon_optimize_scenarios_s.nextval
2528 into l_new_scenario_id from dual;
2529
2530 IF(p_cost_scenario_flag = 'Y') THEN
2531
2532 -- we donot display this number any place
2533 -- all we want is to have a unique combination
2534 l_next_scenario_number := l_new_scenario_id;
2535
2536 ELSE
2537 select max(scenario_number) + 1
2538 into l_next_scenario_number
2539 from pon_optimize_scenarios
2540 where auction_header_id = l_auction_header_id
2541 and (cost_scenario_flag is null or cost_scenario_flag <> 'Y');
2542
2543 END IF;
2544
2545 --first copy the scenario
2546 INSERT INTO PON_OPTIMIZE_SCENARIOS(
2547 auction_header_id,
2548 scenario_id,
2549 scenario_name,
2550 scenario_number,
2551 objective_code,
2552 status,
2553 price_type,
2554 internal_note,
2555 updated_tp_contact_id,
2556 last_tp_update_date,
2557 creation_date,
2558 created_by,
2559 last_update_date,
2560 last_updated_by,
2561 last_update_login,
2562 cost_scenario_flag,
2563 parent_scenario_id,
2564 constraint_priority_type)
2565 SELECT auction_header_id,
2566 l_new_scenario_id,
2567 scenario_name,
2568 l_next_scenario_number,
2569 objective_code,
2570 'NOT_RUN',
2571 price_type,
2572 internal_note,
2573 p_user_id,
2574 sysdate,
2575 sysdate,
2576 l_fnd_user_id,
2577 sysdate,
2578 l_fnd_user_id,
2579 l_fnd_user_id,
2580 p_cost_scenario_flag,
2581 decode(p_cost_scenario_flag, 'Y', p_scenario_id, to_number(null)),
2582 nvl(constraint_priority_type, 'MANDATORY')
2583 FROM pon_optimize_scenarios
2584 WHERE scenario_id = p_scenario_id;
2585
2586 -- copy the constraints
2587
2588 -- make sure there is at least 1 row to prevent no data found exception
2589 select count(*) into l_num_constraints
2590 from pon_optimize_constraints
2591 where scenario_id = p_scenario_id;
2592
2593 IF (l_num_constraints > 0) THEN
2594
2595 INSERT INTO PON_OPTIMIZE_CONSTRAINTS(
2596 scenario_id,
2597 sequence_number,
2598 auction_header_id,
2599 constraint_type,
2600 line_number,
2601 min_amount,
2602 max_amount,
2603 amount_type,
2604 min_quantity,
2605 max_quantity,
2606 quantity_cutoff,
2607 price_cutoff,
2608 split_award_flag,
2609 integral_qty_award_flag,
2610 excluded_flag,
2611 from_date,
2612 to_date,
2613 min_score,
2614 supp_classification,
2615 attr_sequence_number,
2616 attr_group_name,
2617 trading_partner_id,
2618 trading_partner_contact_id,
2619 vendor_site_id,
2620 creation_date,
2621 created_by,
2622 last_update_date,
2623 last_updated_by,
2624 last_update_login,
2625 MIN_MAX_AMOUNT_PRIORITY
2626 ,MIN_MAX_AMOUNT_COST
2627 ,MIN_MAX_AMOUNT_INFEAS_FLAG
2628 ,MIN_MAX_QUANTITY_PRIORITY
2629 ,MIN_MAX_QUANTITY_COST
2630 ,MIN_MAX_QUANTITY_INFEAS_FLAG
2631 ,QUANTITY_CUTOFF_PRIORITY
2632 ,QUANTITY_CUTOFF_COST
2633 ,QUANTITY_CUTOFF_INFEAS_FLAG
2634 ,PRICE_CUTOFF_PRIORITY
2635 ,PRICE_CUTOFF_COST
2636 ,PRICE_CUTOFF_INFEAS_FLAG
2637 ,SPLIT_AWARD_PRIORITY
2638 ,SPLIT_AWARD_INFEAS_FLAG
2639 ,SPLIT_AWARD_COST
2640 ,INTEGRAL_QTY_AWARD_PRIORITY
2641 ,INTEGRAL_QTY_AWARD_INFEAS_FLAG
2642 ,INTEGRAL_QTY_AWARD_COST
2643 ,EXCLUDED_SUPPLIER_PRIORITY
2644 ,EXCLUDED_SUPPLIER_INFEAS_FLAG
2645 ,EXCLUDED_SUPPLIER_COST
2646 ,PROMISED_DATE_PRIORITY
2647 ,PROMISED_DATE_COST
2648 ,PROMISED_DATE_INFEAS_FLAG
2649 ,MIN_SCORE_PRIORITY
2650 ,MIN_SCORE_COST
2651 ,MIN_SCORE_INFEAS_FLAG)
2652 SELECT l_new_scenario_id,
2653 sequence_number,
2654 auction_header_id,
2655 constraint_type,
2656 line_number,
2657 min_amount,
2658 max_amount,
2659 amount_type,
2660 min_quantity,
2661 max_quantity,
2662 quantity_cutoff,
2663 price_cutoff,
2664 split_award_flag,
2665 integral_qty_award_flag,
2666 excluded_flag,
2667 from_date,
2668 to_date,
2669 min_score,
2670 supp_classification,
2671 attr_sequence_number,
2672 attr_group_name,
2673 trading_partner_id,
2674 trading_partner_contact_id,
2675 vendor_site_id,
2676 sysdate,
2677 l_fnd_user_id,
2678 sysdate,
2679 l_fnd_user_id,
2680 l_fnd_user_id,
2681 MIN_MAX_AMOUNT_PRIORITY
2682 ,TO_NUMBER(NULL)
2683 ,TO_CHAR(NULL)
2684 ,MIN_MAX_QUANTITY_PRIORITY
2685 ,TO_NUMBER(NULL)
2686 ,TO_CHAR(NULL)
2687 ,QUANTITY_CUTOFF_PRIORITY
2688 ,TO_NUMBER(NULL)
2689 ,TO_CHAR(NULL)
2690 ,PRICE_CUTOFF_PRIORITY
2691 ,TO_NUMBER(NULL)
2692 ,TO_CHAR(NULL)
2693 ,SPLIT_AWARD_PRIORITY
2694 ,TO_NUMBER(NULL)
2695 ,TO_CHAR(NULL)
2696 ,INTEGRAL_QTY_AWARD_PRIORITY
2697 ,TO_NUMBER(NULL)
2698 ,TO_CHAR(NULL)
2699 ,EXCLUDED_SUPPLIER_PRIORITY
2700 ,TO_NUMBER(NULL)
2701 ,TO_CHAR(NULL)
2702 ,PROMISED_DATE_PRIORITY
2703 ,TO_NUMBER(NULL)
2704 ,TO_CHAR(NULL)
2705 ,MIN_SCORE_PRIORITY
2706 ,TO_NUMBER(NULL)
2707 ,TO_CHAR(NULL)
2708 FROM pon_optimize_constraints
2709 WHERE scenario_id = p_scenario_id;
2710
2711 END IF;
2712
2713 -- copy the bid class information
2714 -- make sure a row exists first to prevent no data found exception
2715 select count(*) into l_num_bid_classes
2716 from pon_optimize_bid_class
2717 where scenario_id = p_scenario_id;
2718
2719 IF (l_num_bid_classes > 0) THEN
2720 INSERT INTO PON_OPTIMIZE_BID_CLASS(
2721 scenario_id,
2722 sequence_number,
2723 bid_number,
2724 creation_date,
2725 created_by,
2726 last_update_date,
2727 last_updated_by,
2728 last_update_login
2729 )
2730 SELECT l_new_scenario_id,
2731 sequence_number,
2732 bid_number,
2733 sysdate,
2734 l_fnd_user_id,
2735 sysdate,
2736 l_fnd_user_id,
2737 l_fnd_user_id
2738 FROM pon_optimize_bid_class
2739 WHERE scenario_id = p_scenario_id;
2740 END IF;
2741
2742 x_status := 'SUCCESS';
2743
2744 IF (p_cost_scenario_flag = 'Y') THEN
2745
2746 -- populate this value only when we are
2747 -- copying a scenario to do cost of constriant
2748 -- calculation
2749
2750 x_cost_scenario_id := l_new_scenario_id;
2751 ELSE
2752
2753 -- else, set it to some dummy value
2754
2755 x_cost_scenario_id := -9999;
2756
2757 END IF;
2758
2759
2760 EXCEPTION
2761 when others then
2762 x_status := 'FAILURE';
2763 raise;
2764 END copy_award_scenario;
2765
2766
2767
2768 PROCEDURE batch_award_spreadsheet
2769 (
2770 p_auction_header_id IN NUMBER,
2771 p_mode IN VARCHAR2,
2772 p_auctioneer_id IN NUMBER,
2773 p_last_update_date IN DATE,
2774 x_status OUT NOCOPY VARCHAR2
2775 )
2776 IS
2777
2778 l_award_date DATE;
2779
2780 BEGIN
2781 l_award_date := SYSDATE;
2782
2783 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
2784 --
2785 /* check if the auction has been modified by some other user
2786 If it has been modified, status returns failure
2787 else this is the only user modifying the auction
2788 */
2789
2790 IF (is_auction_not_updated (p_auction_header_id, p_last_update_date)) THEN
2791 x_status := 'SUCCESS';
2792 -- update the last update date
2793 UPDATE pon_Auction_headers_all
2794 SET last_update_date = SYSDATE
2795 WHERE auction_header_id = p_auction_header_id;
2796 --
2797 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2798 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.BATCH_AWARD_SPREADSHEET.AUCTION_ID:'
2799 || p_auction_header_id,'SUCCEEDED.');
2800 END IF;
2801 --
2802 ELSE
2803 x_status := 'FAILURE';
2804 --
2805 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2806 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.BATCH_AWARD_SPREADSHEET.AUCTION_ID:'
2807 || p_auction_header_id,'FAILED.');
2808 END IF;
2809 --
2810 END IF;
2811
2812
2813 END BATCH_AWARD_SPREADSHEET;
2814 --
2815 --
2816 ----------------------------------------------------------------
2817 -- procedure added by snatu on 08/15/03
2818 -- handles awarding in FPJ for awarding through spreadsheet
2819 ----------------------------------------------------------------
2820 --
2821 --
2822 PROCEDURE save_award_spreadsheet
2823 (
2824 p_batch_id IN NUMBER,
2825 p_auction_header_id IN NUMBER,
2826 p_mode IN VARCHAR2,
2827 p_auctioneer_id IN NUMBER,
2828 p_last_update_date IN DATE,
2829 p_batch_enabled IN VARCHAR2,
2830 p_is_xml_upload IN VARCHAR2,
2831 x_status OUT NOCOPY VARCHAR2
2832 )
2833 IS
2834 CURSOR c_spsheet_awards (c_batch_id NUMBER, c_auction_header_id NUMBER, c_is_xml_upload VARCHAR2) IS
2835 --Query retrives rows ordered in way exported in spreadhsheet
2836 SELECT
2837 aii.auction_header_id,
2838 aii.auction_line_number,
2839 aii.bid_number,
2840 DECODE (nvl(aii.award_status,'N'),'N',null,
2841 DECODE (ai.ORDER_TYPE_LOOKUP_CODE,
2842 'QUANTITY', aii.award_quantity,
2843 'RATE' , decode (ai.purchase_basis , 'TEMP LABOR' , aii.award_quantity ,1) ,
2844 1) )award_quantity,
2845 decode (nvl(aii.award_status,'N'),'Y', g_AWARD_OUTCOME_WIN,
2846 g_AWARD_OUTCOME_LOSE) award_outcome,
2847 aii.awardreject_reason,
2848 ai.group_type,
2849 aii.award_shipment_number
2850 FROM pon_award_items_interface aii,
2851 pon_auction_item_prices_all ai,
2852 pon_bid_item_prices bi,
2853 pon_auction_headers_all pah
2854 WHERE
2855 aii.batch_id = c_batch_id
2856 AND aii.auction_header_id = c_auction_header_id
2857 AND aii.auction_header_id = ai.auction_header_id
2858 AND aii.AUCTION_LINE_NUMBER = ai.LINE_NUMBER
2859 AND bi.bid_number = aii.bid_number
2860 AND bi.line_number = aii.AUCTION_LINE_NUMBER
2861 AND pah.auction_header_id = aii.auction_header_id
2862 ORDER BY
2863 ai.disp_line_number asc,
2864 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;
2865 l_spsheet_awards_rec c_spsheet_awards%ROWTYPE;
2866 --
2867 l_award_lines t_award_lines;
2868 l_matrix_index NUMBER;
2869 l_size NUMBER;
2870 l_current_bid_number NUMBER;
2871 l_current_update_date DATE;
2872 l_award_date DATE;
2873 l_curr_line_num NUMBER;
2874 --
2875 TYPE BID_LIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2876 l_bid_list BID_LIST_TYPE;
2877 TYPE ITEM_LIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2878 l_item_list ITEM_LIST_TYPE;
2879 TYPE ACCEPT_LIST_TYPE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
2880 l_accept_list ACCEPT_LIST_TYPE;
2881 TYPE REJECT_LIST_TYPE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
2882 l_reject_list REJECT_LIST_TYPE;
2883 --
2884 BEGIN
2885
2886 l_matrix_index := 0;
2887 l_award_date := SYSDATE;
2888 l_curr_line_num := NULL;
2889 --
2890
2891 -- Get distinct lines awarded/rejected
2892 SELECT DISTINCT auction_line_number BULK COLLECT INTO l_item_list
2893 FROM pon_award_items_interface
2894 WHERE batch_id = p_batch_id
2895 AND auction_header_id = p_auction_header_id;
2896
2897 -- Get distinct bids awarded/rejected
2898 SELECT DISTINCT bid_number BULK COLLECT INTO l_bid_list
2899 FROM pon_award_items_interface
2900 WHERE batch_id = p_batch_id
2901 AND auction_header_id = p_auction_header_id;
2902 --
2903 -- Need to set award quantity and award_status and award_date
2904
2905 OPEN c_spsheet_awards (p_batch_id, p_auction_header_id, p_is_xml_upload);
2906
2907 LOOP --{
2908 fetch c_spsheet_awards into l_spsheet_awards_rec;
2909 EXIT WHEN c_spsheet_awards%NOTFOUND;
2910 -- Construct Matrix in any case (WIN/LOSR)
2911 l_matrix_index := l_matrix_index +1;
2912 l_award_lines(l_matrix_index).bid_number := l_spsheet_awards_rec.bid_number;
2913 l_award_lines(l_matrix_index).line_number := l_spsheet_awards_rec.auction_line_number;
2914 l_award_lines(l_matrix_index).award_status := get_award_status(l_spsheet_awards_rec.award_outcome);
2915 l_award_lines(l_matrix_index).award_date := l_award_date;
2916 l_award_lines(l_matrix_index).award_quantity := l_spsheet_awards_rec.award_quantity;
2917 l_award_lines(l_matrix_index).group_type := l_spsheet_awards_rec.group_type;
2918 l_award_lines(l_matrix_index).award_shipment_number := l_spsheet_awards_rec.award_shipment_number;
2919
2920 -- Update notes per line for awarded / rejected suppleirs
2921 IF (l_curr_line_num IS NULL ) THEN
2922 l_curr_line_num := l_spsheet_awards_rec.auction_line_number;
2923 l_accept_list(l_curr_line_num) := NULL;
2924 l_reject_list(l_curr_line_num) := NULL;
2925 END IF;
2926 IF (l_curr_line_num <> l_spsheet_awards_rec.auction_line_number ) THEN
2927 -- new line ; update curr line
2928 l_curr_line_num := l_spsheet_awards_rec.auction_line_number;
2929 l_accept_list(l_curr_line_num) := NULL;
2930 l_reject_list(l_curr_line_num) := NULL;
2931 END IF ;
2932 --
2933 IF (l_accept_list(l_curr_line_num) IS NULL
2934 AND l_award_lines(l_matrix_index).award_status = 'AWARDED') THEN
2935 l_accept_list(l_curr_line_num) := l_spsheet_awards_rec.awardreject_reason;
2936 END IF ;
2937 --
2938 IF (l_reject_list(l_curr_line_num) IS NULL
2939 AND l_award_lines(l_matrix_index).award_status = 'REJECTED') THEN
2940 l_reject_list(l_curr_line_num) := l_spsheet_awards_rec.awardreject_reason;
2941 END IF ;
2942 --
2943 END LOOP;
2944
2945 --}
2946
2947 --
2948 -- this procedure updates all the bid lines one-by-one
2949
2950 update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
2951
2952 l_size := l_bid_list.count;
2953
2954 FOR l_index IN 1..l_size LOOP
2955
2956 -- this procedure updates the award_status
2957 -- for all the bids at the bid-header level
2958 -- we don't need to invoke this over here -> this should be invoked after all
2959 -- batches are exhausted
2960
2961 update_single_bid_header(l_bid_list(l_index),p_auctioneer_id);
2962
2963 END LOOP;
2964
2965 l_size := l_item_list.count;
2966
2967 FOR l_index IN 1..l_size LOOP
2968
2969 update_auction_item_prices(p_auction_header_id, l_item_list(l_index), l_award_date, p_auctioneer_id, p_mode);
2970
2971 -- update acceptances per auction line
2972 bulk_update_pon_acceptances (
2973 p_auction_header_id, l_item_list(l_index),
2974 l_accept_list(l_item_list(l_index)), l_reject_list(l_item_list(l_index)),
2975 l_award_date, p_auctioneer_id, p_mode);
2976
2977 END LOOP;
2978
2979 /* FPK: CPA p_neg_has_lines parameter hardcoded to 'Y' as save_award_spreadsheet
2980 procedure will only be called if negotiation has lines */
2981
2982 -- this procedure loops over all the lines to determine the auction-header-level
2983 -- award-status etc.
2984
2985 if(p_batch_enabled = 'N') then --{
2986
2987 update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
2988 --
2989 /* check if the auction has been modified by some other user
2990 If it has been modified, status returns failure
2991 else this is the only user modifying the auction
2992 */
2993
2994 IF (is_auction_not_updated (p_auction_header_id, p_last_update_date)) THEN
2995 x_status := 'SUCCESS';
2996 -- update the last update date
2997 UPDATE pon_Auction_headers_all
2998 SET last_update_date = SYSDATE
2999 WHERE auction_header_id = p_auction_header_id;
3000 --
3001 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3002 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.SAVE_AWARD_SPREADSHEET.AUCTION_ID:'
3003 || p_auction_header_id,'SUCCEEDED.');
3004 END IF;
3005 --
3006 ELSE
3007 x_status := 'FAILURE';
3008 --
3009 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3010 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'PON_AWARD_PKG.SAVE_AWARD_SPREADSHEET.AUCTION_ID:'
3011 || p_auction_header_id,'FAILED.');
3012 END IF;
3013 --
3014 END IF;
3015
3016 else
3017
3018 -- delete interface data
3019 delete from pon_award_items_interface
3020 where batch_id = p_batch_id;
3021
3022 x_status := 'SUCCESS';
3023
3024 end if; --}
3025
3026 --
3027 END save_award_spreadsheet;
3028 --
3029 --
3030 FUNCTION is_auction_not_updated (
3031 p_auction_header_id NUMBER,
3032 p_last_update_date DATE
3033 ) RETURN BOOLEAN
3034 IS
3035 l_current_update_date DATE;
3036 BEGIN
3037 SELECT last_update_date INTO l_current_update_date
3038 FROM pon_auction_headers_all
3039 WHERE auction_header_id = p_auction_header_id;
3040 IF (l_current_update_date = p_last_update_date) THEN
3041 RETURN TRUE;
3042 ELSE
3043 RETURN FALSE;
3044 END IF;
3045 END is_auction_not_updated;
3046 --
3047 PROCEDURE toggle_shortlisting
3048 ( p_user_id IN NUMBER
3049 , p_bid_number IN NUMBER
3050 , p_event IN VARCHAR2
3051 )
3052 IS
3053
3054 l_person_id NUMBER;
3055
3056 BEGIN
3057 -- This will never fail even if more than one user setup w/ same buyer
3058 -- as always there will be one record for an user_id in fnd_user
3059 SELECT PERSON_PARTY_ID INTO l_person_id
3060 FROM FND_USER
3061 WHERE user_id = p_user_id;
3062
3063 IF (p_event = 'NOT_SHORTLIST') THEN
3064 UPDATE PON_BID_HEADERS
3065 SET SHORTLIST_FLAG = 'N'
3066 , LAST_UPDATE_DATE = SYSDATE
3067 , LAST_UPDATED_BY = p_user_id
3068 , SHORTLIST_TPC_ID = l_person_id
3069 WHERE BID_NUMBER = p_bid_number;
3070 ELSIF (p_event = 'SHORTLIST') THEN
3071 UPDATE PON_BID_HEADERS
3072 SET SHORTLIST_FLAG = 'Y'
3073 , LAST_UPDATE_DATE = SYSDATE
3074 , LAST_UPDATED_BY = p_user_id
3075 , SHORTLIST_TPC_ID = l_person_id
3076 WHERE BID_NUMBER = p_bid_number;
3077 END IF;
3078
3079 EXCEPTION
3080 when others then
3081 raise;
3082
3083 END toggle_shortlisting;
3084
3085
3086 -- Returns the award amount for a negotiation.
3087 FUNCTION get_award_amount(p_auction_header_id IN NUMBER) RETURN NUMBER IS
3088 l_award_amount NUMBER;
3089 BEGIN
3090
3091 BEGIN
3092 SELECT SUM(DECODE(ah.contract_type, 'STANDARD', bh.total_award_amount, bh.po_agreed_amount * (1/nvl(bh.rate, 1))))
3093 INTO l_award_amount
3094 FROM
3095 pon_auction_headers_all ah,
3096 pon_bid_headers bh
3097 WHERE
3098 ah.auction_header_id = p_auction_header_id
3099 AND ah.auction_header_id = bh.auction_header_id
3100 AND bh.award_status in ('AWARDED', 'PARTIAL');
3101 EXCEPTION
3102 WHEN no_data_found THEN
3103 NULL;
3104 END;
3105
3106 RETURN l_award_amount;
3107
3108 END get_award_amount;
3109 --
3110 --
3111 PROCEDURE award_bi_subline (
3112 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
3113 p_bid_number IN pon_bid_headers.bid_number%TYPE,
3114 p_parent_line_number IN pon_bid_item_prices.line_number%TYPE,
3115 p_award_status IN pon_bid_item_prices.award_status%TYPE,
3116 p_award_date IN pon_bid_item_prices.award_date%TYPE,
3117 p_auctioneer_id pon_bid_item_prices.LAST_UPDATED_BY%TYPE)
3118 IS
3119 CURSOR c_sublines (c_auction_header_id pon_bid_headers.auction_header_id%TYPE,
3120 c_bid_number pon_bid_headers.bid_number%TYPE,
3121 c_parent_line_number pon_bid_item_prices.line_number%TYPE) IS
3122 --Query retrives sublines for the given parent line
3123 SELECT
3124 bi.line_number,
3125 DECODE (p_award_status, 'AWARDED',decode (aii.group_type,
3126 'LOT_LINE', null, decode (aii.order_type_lookup_code,
3127 'FIXED PRICE', 1,
3128 'AMOUNT', 1,
3129 'RATE', decode (aii.purchase_basis, 'TEMP LABOR', bi.quantity, 1), bi.quantity )), null) award_quantity
3130 FROM pon_bid_item_prices bi, pon_auction_item_prices_all aii
3131 WHERE
3132 bi.bid_number = c_bid_number
3133 AND bi.line_number IN (SELECT ai.line_number
3134 FROM pon_auction_item_prices_all ai
3135 WHERE ai.parent_line_number = c_parent_line_number
3136 AND ai.auction_header_id = bi.auction_header_id )
3137 AND aii.auction_header_id = bi.auction_header_id
3138 AND aii.line_number = bi.line_number;
3139
3140 l_sublines_rec c_sublines%ROWTYPE;
3141
3142 BEGIN
3143 OPEN c_sublines (p_auction_header_id, p_bid_number, p_parent_line_number);
3144 LOOP
3145 FETCH c_sublines INTO l_sublines_rec;
3146 EXIT WHEN c_sublines%NOTFOUND;
3147 -- update the child lines
3148 update_single_bid_item_prices
3149 (
3150 p_bid_number,
3151 l_sublines_rec.line_number,
3152 p_award_status,
3153 l_sublines_rec.award_quantity,
3154 p_award_date,
3155 p_auctioneer_id
3156 );
3157 END LOOP;
3158 END award_bi_subline;
3159 --
3160 --
3161 ----------------------------------------------------------------
3162 -- gets the parent line
3163 --and sets the award status of parent line by querying up the child lines
3164 ----------------------------------------------------------------
3165 PROCEDURE update_bi_group_award (
3166 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
3167 p_bid_number IN pon_bid_headers.bid_number%TYPE,
3168 p_parent_line_number IN pon_auction_item_prices_all.parent_line_number%TYPE,
3169 p_award_date IN pon_bid_item_prices.award_date%TYPE,
3170 p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE )
3171 IS
3172 l_total_lines NUMBER;
3173 l_awarded_lines NUMBER;
3174 l_rejected_lines NUMBER;
3175 l_award_status pon_bid_item_prices.award_status%TYPE;
3176 BEGIN
3177 --get total, awarded/ rejected lines
3178 --
3179 SELECT count (*) ,
3180 sum(decode(bi.award_status,'AWARDED',1,0)) ,
3181 sum(decode(bi.award_status,'REJECTED',1,0))
3182 INTO l_total_lines,
3183 l_awarded_lines,
3184 l_rejected_lines
3185 FROM pon_auction_item_prices_all ai, pon_bid_item_prices bi
3186 WHERE ai.parent_line_number = p_parent_line_number
3187 AND ai.auction_header_id = p_auction_header_id
3188 and ai.auction_header_id = bi.auction_header_id(+)
3189 and bi.bid_number = p_bid_number
3190 and bi.line_number = ai.line_number;
3191
3192 IF (l_total_lines = l_awarded_lines) THEN
3193 l_award_status := 'AWARDED' ;
3194 ELSIF (l_awarded_lines > 0) THEN
3195 l_award_status := 'PARTIAL';
3196 ELSIF (l_total_lines = l_rejected_lines) THEN
3197 l_award_status := 'REJECTED';
3198 ELSE
3199 l_award_status := null;
3200 END IF;
3201 --
3202 update_single_bid_item_prices
3203 (
3204 p_bid_number,
3205 p_parent_line_number,
3206 l_award_status,
3207 null,
3208 p_award_date,
3209 p_auctioneer_id
3210 );
3211
3212 END update_bi_group_award;
3213 --
3214 --
3215 PROCEDURE update_ai_group_award (
3216 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
3217 p_line_number IN pon_bid_item_prices.line_number%TYPE,
3218 p_award_date IN pon_bid_item_prices.award_date%TYPE,
3219 p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE)
3220 IS
3221 l_total_lines NUMBER;
3222 l_awarded_lines NUMBER;
3223 l_parent_line_number NUMBER;
3224 l_award_status pon_auction_item_prices_all.award_status%TYPE;
3225 BEGIN
3226 --get total and awarded lines
3227 SELECT parent_line_number INTO l_parent_line_number FROM pon_auction_item_prices_all
3228 WHERE auction_header_id = p_auction_header_id AND line_number = p_line_number;
3229 --
3230 -- all the group lines have bids if a single group line has a bid
3231 -- hence all group lines are awardable and hence considered for the count
3232 SELECT COUNT(*) INTO l_total_lines
3233 FROM pon_auction_item_prices_all ai
3234 WHERE parent_line_number = l_parent_line_number
3235 and auction_header_id = p_auction_header_id;
3236 --
3237 select COUNT(*) INTO l_awarded_lines
3238 FROM pon_auction_item_prices_all
3239 WHERE parent_line_number = l_parent_line_number
3240 AND award_status = 'AWARDED'
3241 and auction_header_id = p_auction_header_id;
3242
3243 IF (l_total_lines = l_awarded_lines) THEN
3244 l_award_status := 'AWARDED' ;
3245 ELSIF (l_awarded_lines > 0) THEN
3246 l_award_status := 'PARTIAL';
3247 ELSE
3248 l_award_status := null;
3249 END IF;
3250 --
3251 UPDATE pon_auction_item_prices_all
3252 SET award_status = l_award_status,
3253 awarded_quantity = null,
3254 award_mode = null,
3255 last_update_date = p_award_date,
3256 last_updated_by = p_auctioneer_id
3257 WHERE auction_header_id = p_auction_header_id
3258 AND line_number = l_parent_line_number;
3259
3260 END update_ai_group_award;
3261 --
3262 --
3263
3264
3265 PROCEDURE get_award_totals(
3266 p_auction_header_id in number,
3267 p_award_total out nocopy number,
3268 p_current_total out nocopy number,
3269 p_savings_total out nocopy number,
3270 p_savings_percent out nocopy number)
3271 IS
3272 l_current_total_temp NUMBER;
3273 BEGIN
3274
3275 p_award_total := 0;
3276 p_current_total := 0;
3277 p_savings_total := 0;
3278 p_savings_percent := 0;
3279 l_current_total_temp := 0;
3280
3281 SELECT sum(nvl2(PAIP.current_price,
3282 PAIP.current_price * nvl(PAIP.awarded_quantity, 0),
3283 sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))))
3284 INTO p_current_total
3285 FROM pon_bid_item_prices PBIP,
3286 pon_bid_headers PBH,
3287 pon_auction_item_prices_all PAIP
3288 WHERE PAIP.auction_header_id = p_auction_header_id
3289 AND PAIP.auction_header_id = PBIP.auction_header_id (+)
3290 AND PAIP.line_number = PBIP.line_number (+)
3291 AND PBIP.bid_number = PBH.bid_number (+)
3292 AND PBH.bid_status (+) = 'ACTIVE'
3293 AND NVL(PBH.award_status, 'NONE') IN ('PARTIAL', 'AWARDED')
3294 GROUP BY
3295 PAIP.line_number, PAIP.current_price, PAIP.awarded_quantity;
3296
3297 SELECT sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))
3298 INTO p_award_total
3299 FROM pon_bid_item_prices PBIP,
3300 pon_bid_headers PBH
3301 WHERE PBH.auction_header_id = p_auction_header_id
3302 AND PBIP.bid_number = PBH.bid_number (+)
3303 AND PBH.bid_status (+) = 'ACTIVE'
3304 AND NVL(PBH.award_status, 'NONE') IN ('PARTIAL', 'AWARDED');
3305
3306 p_savings_total := p_current_total - p_award_total;
3307
3308 -- safety check to avoid divide-by-zero exception
3309 IF p_current_total IS NULL OR p_current_total = 0 THEN
3310 p_savings_percent := 0;
3311 ELSE
3312 p_savings_percent := (p_savings_total / p_current_total) * 100;
3313 END IF;
3314
3315 EXCEPTION
3316
3317 WHEN OTHERS THEN
3318
3319 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3320 fnd_log.string(log_level => fnd_log.level_unexpected
3321 ,module => 'pon_award_pkg.get_award_totals'
3322 ,message => 'exception occurred while calculating totals ' || SUBSTR(SQLERRM, 1, 200));
3323 END IF;
3324
3325
3326 NULL;
3327
3328
3329 END;
3330
3331
3332 FUNCTION does_bid_exist
3333 (
3334 p_scenario_id IN PON_OPTIMIZE_CONSTRAINTS.SCENARIO_ID%TYPE,
3335 p_sequence_number IN PON_OPTIMIZE_CONSTRAINTS.SEQUENCE_NUMBER%TYPE,
3336 p_bid_number IN PON_BID_HEADERS.BID_NUMBER%TYPE
3337 ) RETURN VARCHAR2
3338 IS
3339 l_bid_exists VARCHAR2(1);
3340 BEGIN
3341
3342 BEGIN
3343 SELECT 'Y'
3344 INTO l_bid_exists
3345 FROM dual
3346 WHERE EXISTS (SELECT 1
3347 FROM pon_optimize_bid_class pobc
3348 WHERE pobc.scenario_id = p_scenario_id
3349 AND pobc.sequence_number = p_sequence_number
3350 AND pobc.bid_number = p_bid_number);
3351
3352 EXCEPTION
3353 WHEN NO_DATA_FOUND THEN
3354 l_bid_exists := 'N';
3355 END;
3356 return l_bid_exists;
3357 END;
3358
3359 FUNCTION has_scored_attribute
3360 (
3361 p_auction_header_id IN PON_AUCTION_ATTRIBUTES.AUCTION_HEADER_ID%TYPE,
3362 p_line_number IN PON_AUCTION_ATTRIBUTES.LINE_NUMBER%TYPE
3363 ) RETURN VARCHAR2
3364 IS
3365 l_scored_attribute_exists VARCHAR2(1);
3366 BEGIN
3367
3368 BEGIN
3369 SELECT 'Y'
3370 INTO l_scored_attribute_exists
3371 FROM dual
3372 WHERE EXISTS (SELECT 1
3373 FROM pon_attribute_scores pas
3374 WHERE pas.auction_header_id = p_auction_header_id
3375 AND pas.line_number = p_line_number);
3376
3377 EXCEPTION
3378 WHEN NO_DATA_FOUND THEN
3379 l_scored_attribute_exists := 'N';
3380 END;
3381 return l_scored_attribute_exists;
3382 END;
3383
3384 PROCEDURE preprocess_cost_of_constraint
3385 (
3386 p_scenario_id IN NUMBER,
3387 p_user_id IN NUMBER,
3388 p_cost_constraint_flag IN VARCHAR2,
3389 p_constraint_type IN VARCHAR2,
3390 p_internal_type IN VARCHAR2,
3391 p_line_number IN NUMBER,
3392 p_sequence_number IN NUMBER,
3393 x_cost_scenario_id OUT NOCOPY NUMBER,
3394 x_status OUT NOCOPY VARCHAR2
3395 )
3396 IS
3397
3398 l_new_scenario_id NUMBER;
3399 l_status VARCHAR2(10);
3400 l_order_type_lookup_code PON_AUCTION_ITEM_PRICES_ALL.order_type_lookup_code%TYPE;
3401 l_auction_qty NUMBER;
3402 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
3403 l_module VARCHAR2(200);
3404 l_priority VARCHAR2(30);
3405 BEGIN
3406
3407 l_module := 'pon.plsql.PON_AWARD_PKG.preprocess_cost_of_constraint';
3408 l_priority := '1_CRITICAL';
3409 -- basic initialization
3410
3411 l_new_scenario_id := -9999;
3412
3413 IF (g_debug_mode = 'Y') THEN
3414 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3415 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Entering procedure with p_scenario_id: ' || p_scenario_id );
3416 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);
3417 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module,' p_line_number : '|| p_line_number || ' ,p_sequence_number : ' || p_sequence_number );
3418 END IF;
3419 END IF;
3420
3421 copy_award_scenario(p_scenario_id => p_scenario_id,
3422 p_user_id => p_user_id,
3423 p_cost_scenario_flag => p_cost_constraint_flag,
3424 x_cost_scenario_id => l_new_scenario_id,
3425 x_status => l_status);
3426
3427
3428 IF (g_debug_mode = 'Y') THEN
3429 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3430 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);
3431 END IF;
3432 END IF;
3433
3434 IF (l_status <> 'FAILURE') THEN
3435
3436 -- these initial conditions are applicable to those
3437 -- scenarios where multiple constraints are saved on the
3438 -- same row of pon_optimize_constraints table
3439
3440 IF (p_constraint_type = 'LINE_CONST') THEN --{
3441
3442 IF (p_internal_type = 'LINE_SPLIT_AWARD') THEN
3443
3444 IF (g_debug_mode = 'Y') THEN
3445 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3446 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SPLIT_AWARD internal type constraint');
3447 END IF;
3448 END IF;
3449
3450 UPDATE PON_OPTIMIZE_CONSTRAINTS
3451 SET SPLIT_AWARD_FLAG = decode(SPLIT_AWARD_FLAG, 'Y', 'N', 'Y'),
3452 SPLIT_AWARD_PRIORITY = NVL2(SPLIT_AWARD_PRIORITY,l_priority,null),
3453 SPLIT_AWARD_INFEAS_FLAG = TO_CHAR(NULL),
3454 SPLIT_AWARD_COST = TO_NUMBER(NULL)
3455 WHERE SCENARIO_ID = l_new_scenario_id
3456 AND SEQUENCE_NUMBER = p_sequence_number
3457 AND CONSTRAINT_TYPE = p_constraint_type;
3458
3459 ELSIF (p_internal_type = 'LINE_INTEGER_QUANTITY') THEN
3460
3461 IF (g_debug_mode = 'Y') THEN
3462 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3463 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_INTEGER_QUANTITY internal type constraint');
3464 END IF;
3465 END IF;
3466
3467 UPDATE PON_OPTIMIZE_CONSTRAINTS
3468 SET INTEGRAL_QTY_AWARD_FLAG = decode(INTEGRAL_QTY_AWARD_FLAG, 'Y', 'N', 'Y'),
3469 INTEGRAL_QTY_AWARD_PRIORITY = NVL2(INTEGRAL_QTY_AWARD_PRIORITY,l_priority,null),
3470 INTEGRAL_QTY_AWARD_INFEAS_FLAG = TO_CHAR(NULL),
3471 INTEGRAL_QTY_AWARD_COST = TO_NUMBER(NULL)
3472 WHERE SCENARIO_ID = l_new_scenario_id
3473 AND SEQUENCE_NUMBER = p_sequence_number
3474 AND CONSTRAINT_TYPE = p_constraint_type;
3475
3476
3477 ELSIF (p_internal_type = 'LINE_AWARD_QTY') THEN
3478
3479 BEGIN
3480 SELECT pah.contract_type,
3481 pai.order_type_lookup_code,
3482 nvl(pai.quantity, 1)
3483 INTO l_contract_type,
3484 l_order_type_lookup_code,
3485 l_auction_qty
3486 FROM pon_auction_headers_all pah,
3487 pon_auction_item_prices_all pai,
3488 pon_optimize_scenarios pos
3489 WHERE pah.auction_header_id = pai.auction_header_id
3490 AND pah.auction_header_id = pos.auction_header_id
3491 AND pai.line_number = p_line_number
3492 AND pos.scenario_id = l_new_scenario_id;
3493 EXCEPTION
3494 WHEN OTHERS THEN
3495 IF (g_debug_mode = 'Y') THEN
3496 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3497 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED, l_module, 'Selecting auction info in LINE_AWARD_QTY internal type constraint if condition caused error');
3498 END IF;
3499 END IF;
3500
3501 END;
3502
3503 -- We need to set Max Qty to 1 for FIXED PRICE and AMOUNT based lines for SPO, BPA and CPA
3504 -- For SPO, We need to set Max Qty to auction qty for QUANTITY based lines
3505 -- 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
3506
3507 -- In the above sql for selecting the auction qty as we use nvl to set auction qty to 1,
3508 -- all the above conditions should be satisfied
3509
3510 IF (g_debug_mode = 'Y') THEN
3511 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3512 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_QTY internal type constraint');
3513 END IF;
3514 END IF;
3515
3516 UPDATE PON_OPTIMIZE_CONSTRAINTS
3517 SET MIN_QUANTITY = 0,
3518 MAX_QUANTITY = DECODE(l_order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, l_auction_qty),
3519 MIN_MAX_QUANTITY_PRIORITY = NVL2(MIN_MAX_QUANTITY_PRIORITY,l_priority,null),
3520 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
3521 MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
3522 WHERE SCENARIO_ID = l_new_scenario_id
3523 AND SEQUENCE_NUMBER = p_sequence_number
3524 AND CONSTRAINT_TYPE = p_constraint_type;
3525
3526 ELSIF (p_internal_type = 'LINE_AWARD_AMOUNT') THEN
3527
3528 IF (g_debug_mode = 'Y') THEN
3529 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3530 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_AMOUNT internal type constraint');
3531 END IF;
3532 END IF;
3533
3534 UPDATE PON_OPTIMIZE_CONSTRAINTS
3535 SET MIN_AMOUNT = TO_NUMBER(NULL),
3536 MAX_AMOUNT = TO_NUMBER(NULL),
3537 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
3538 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
3539 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
3540 WHERE SCENARIO_ID = l_new_scenario_id
3541 AND SEQUENCE_NUMBER = p_sequence_number
3542 AND CONSTRAINT_TYPE = p_constraint_type;
3543
3544
3545 ELSIF (p_internal_type = 'LINE_MIN_QTY') THEN
3546
3547 IF (g_debug_mode = 'Y') THEN
3548 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3549 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_QTY internal type constraint');
3550 END IF;
3551 END IF;
3552
3553 UPDATE PON_OPTIMIZE_CONSTRAINTS
3554 SET QUANTITY_CUTOFF = TO_NUMBER(NULL),
3555 QUANTITY_CUTOFF_PRIORITY = TO_CHAR(NULL),
3556 QUANTITY_CUTOFF_INFEAS_FLAG = TO_CHAR(NULL),
3557 QUANTITY_CUTOFF_COST = TO_NUMBER(NULL)
3558 WHERE SCENARIO_ID = l_new_scenario_id
3559 AND SEQUENCE_NUMBER = p_sequence_number
3560 AND CONSTRAINT_TYPE = p_constraint_type;
3561
3562
3563
3564 ELSIF (p_internal_type = 'LINE_MAX_PRICE') THEN
3565
3566 IF (g_debug_mode = 'Y') THEN
3567 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3568 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MAX_PRICE internal type constraint');
3569 END IF;
3570 END IF;
3571
3572 UPDATE PON_OPTIMIZE_CONSTRAINTS
3573 SET PRICE_CUTOFF = TO_NUMBER(NULL),
3574 PRICE_CUTOFF_PRIORITY = TO_CHAR(NULL),
3575 PRICE_CUTOFF_INFEAS_FLAG = TO_CHAR(NULL),
3576 PRICE_CUTOFF_COST = TO_NUMBER(NULL)
3577 WHERE SCENARIO_ID = l_new_scenario_id
3578 AND SEQUENCE_NUMBER = p_sequence_number
3579 AND CONSTRAINT_TYPE = p_constraint_type;
3580
3581
3582
3583 ELSIF (p_internal_type = 'LINE_MIN_SCORE') THEN
3584
3585 IF (g_debug_mode = 'Y') THEN
3586 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3587 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_SCORE internal type constraint');
3588 END IF;
3589 END IF;
3590 -- XYZ - SIMILAR CONDITION NEEDED FOR
3591 -- AwardOptConstraint.SINGLE_HEADER_ATTR_CUTOFF
3592 -- AwardOptConstraint.GROUP_HEADER_ATTR_CUTOFF
3593
3594 UPDATE PON_OPTIMIZE_CONSTRAINTS
3595 SET MIN_SCORE = TO_NUMBER(NULL),
3596 MIN_SCORE_PRIORITY = TO_CHAR(NULL),
3597 MIN_SCORE_INFEAS_FLAG = TO_CHAR(NULL),
3598 MIN_SCORE_COST = TO_NUMBER(NULL)
3599 WHERE SCENARIO_ID = l_new_scenario_id
3600 AND SEQUENCE_NUMBER = p_sequence_number
3601 AND CONSTRAINT_TYPE = p_constraint_type;
3602
3603 ELSIF (p_internal_type = 'LINE_PROMISED_DATE') THEN
3604
3605 IF (g_debug_mode = 'Y') THEN
3606 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3607 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_PROMISED_DATE internal type constraint');
3608 END IF;
3609 END IF;
3610
3611 UPDATE PON_OPTIMIZE_CONSTRAINTS
3612 SET FROM_DATE = TO_DATE(NULL),
3613 TO_DATE = TO_DATE(NULL),
3614 PROMISED_DATE_PRIORITY = TO_CHAR(NULL),
3615 PROMISED_DATE_INFEAS_FLAG = TO_CHAR(NULL),
3616 PROMISED_DATE_COST = TO_NUMBER(NULL)
3617 WHERE SCENARIO_ID = l_new_scenario_id
3618 AND SEQUENCE_NUMBER = p_sequence_number
3619 AND CONSTRAINT_TYPE = p_constraint_type;
3620
3621 END IF;
3622
3623 --}
3624
3625 ELSIF (p_constraint_type = 'SUPP_LINE_CONST') THEN --{
3626
3627 -- two constraints
3628 -- Line Number of Suppliers : same seqnum
3629 -- Line Any One Supplier : same seqnum
3630
3631 IF (p_internal_type = 'LINE_NUM_SUPP') THEN
3632
3633 IF (g_debug_mode = 'Y') THEN
3634 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3635 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_NUM_SUPP internal type constraint');
3636 END IF;
3637 END IF;
3638
3639 UPDATE PON_OPTIMIZE_CONSTRAINTS
3640 SET MIN_QUANTITY = TO_NUMBER(NULL),
3641 MAX_QUANTITY = TO_NUMBER(NULL),
3642 MIN_MAX_QUANTITY_PRIORITY = TO_CHAR(NULL),
3643 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
3644 MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
3645 WHERE SCENARIO_ID = l_new_scenario_id
3646 AND SEQUENCE_NUMBER = p_sequence_number
3647 AND CONSTRAINT_TYPE = p_constraint_type;
3648
3649
3650 ELSIF (p_internal_type = 'LINE_ANY_ONE_SUPP') THEN
3651
3652 IF (g_debug_mode = 'Y') THEN
3653 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3654 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_ANY_ONE_SUPP internal type constraint');
3655 END IF;
3656 END IF;
3657
3658 UPDATE PON_OPTIMIZE_CONSTRAINTS
3659 SET MIN_AMOUNT = TO_NUMBER(NULL),
3660 MAX_AMOUNT = TO_NUMBER(NULL),
3661 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
3662 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
3663 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
3664 WHERE SCENARIO_ID = l_new_scenario_id
3665 AND SEQUENCE_NUMBER = p_sequence_number
3666 AND CONSTRAINT_TYPE = p_constraint_type;
3667
3668
3669 --ELSE
3670
3671 --DELETE FROM PON_OPTIMIZE_CONSTRAINTS
3672 --WHERE SCENARIO_ID = p_scenario_id
3673 --AND CONSTRAINT_TYPE = p_constraint_type
3674 --AND SEQUENCE_NUMBER = p_sequence_number;
3675
3676 END IF;
3677
3678 --}
3679
3680 ELSIF (p_constraint_type = 'SUPP_BIZ_CONST') THEN --{
3681
3682 IF (g_debug_mode = 'Y') THEN
3683 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3684 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating SUPP_BIZ_CONST constraint type');
3685 END IF;
3686 END IF;
3687
3688 UPDATE PON_OPTIMIZE_CONSTRAINTS
3689 SET MIN_AMOUNT = TO_NUMBER(NULL),
3690 MAX_AMOUNT = TO_NUMBER(NULL),
3691 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
3692 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
3693 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
3694 WHERE SCENARIO_ID = l_new_scenario_id
3695 AND SEQUENCE_NUMBER = p_sequence_number
3696 AND CONSTRAINT_TYPE = p_constraint_type;
3697
3698 --}
3699
3700 ELSIF (p_constraint_type = 'BID_LINE_CONST') THEN --{
3701 IF (p_internal_type = 'LINE_SUPP_SITE_AMT') THEN
3702
3703 IF (g_debug_mode = 'Y') THEN
3704 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3705 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_AMT internal type constraint');
3706 END IF;
3707 END IF;
3708
3709 UPDATE PON_OPTIMIZE_CONSTRAINTS
3710 SET MIN_AMOUNT = TO_NUMBER(NULL),
3711 MAX_AMOUNT = TO_NUMBER(NULL),
3712 MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
3713 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
3714 MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
3715 WHERE SCENARIO_ID = l_new_scenario_id
3716 AND SEQUENCE_NUMBER = p_sequence_number
3717 AND CONSTRAINT_TYPE = p_constraint_type;
3718
3719 ELSIF (p_internal_type = 'LINE_SUPP_SITE_QTT') THEN
3720
3721 IF (g_debug_mode = 'Y') THEN
3722 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3723 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_QTT internal type constraint');
3724 END IF;
3725 END IF;
3726
3727 UPDATE PON_OPTIMIZE_CONSTRAINTS
3728 SET MIN_QUANTITY = TO_NUMBER(NULL),
3729 MAX_QUANTITY = TO_NUMBER(NULL),
3730 MIN_MAX_QUANTITY_PRIORITY = TO_CHAR(NULL),
3731 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
3732 MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
3733 WHERE SCENARIO_ID = l_new_scenario_id
3734 AND SEQUENCE_NUMBER = p_sequence_number
3735 AND CONSTRAINT_TYPE = p_constraint_type;
3736
3737 END IF;
3738 ELSE
3739
3740 IF (g_debug_mode = 'Y') THEN
3741 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3742 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before deleting constraint from pon_optimize_constraints');
3743 END IF;
3744 END IF;
3745 -- INCLUDES LINE-LEVEL INDIVIDUAL SUPPLIER CONSTRAINTS AS WELL
3746
3747 -- if the constraint type is either of the remaining types,
3748 -- we can delete the row from pon_optimize_constraints
3749 -- using the sequence_number
3750
3751 DELETE FROM PON_OPTIMIZE_CONSTRAINTS
3752 WHERE SCENARIO_ID = l_new_scenario_id
3753 AND CONSTRAINT_TYPE = p_constraint_type
3754 AND SEQUENCE_NUMBER = p_sequence_number;
3755
3756 END IF;
3757
3758 x_cost_scenario_id := l_new_scenario_id;
3759 x_status := 'SUCCESS';
3760
3761 ELSE
3762 x_cost_scenario_id := -9999;
3763 x_status := 'FAILURE';
3764
3765
3766 END IF;
3767
3768
3769
3770 EXCEPTION
3771 WHEN OTHERS THEN
3772 x_cost_scenario_id := -9999;
3773 x_status := 'FAILURE';
3774 RAISE;
3775
3776 END;
3777
3778
3779 PROCEDURE postprocess_cost_of_constraint
3780 (
3781 p_scenario_id IN NUMBER,
3782 p_constraint_type IN VARCHAR2,
3783 p_internal_type IN VARCHAR2,
3784 p_line_number IN NUMBER,
3785 p_sequence_number IN NUMBER,
3786 x_status OUT NOCOPY VARCHAR2
3787 )
3788
3789 IS
3790 l_num_constraints NUMBER;
3791 l_num_bid_classes NUMBER;
3792 l_num_results NUMBER;
3793 l_cost_of_constraint NUMBER;
3794 l_parent_scenario_id NUMBER;
3795 l_module VARCHAR2(250);
3796 BEGIN
3797
3798 l_module := 'pon.plsql.PON_AWARD_PKG.postprocess_cost_of_constraint';
3799 IF (g_debug_mode = 'Y') THEN
3800 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3801 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Entering procedure with p_scenario_id: ' || p_scenario_id );
3802 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module,' p_constraint_type : '|| p_constraint_type || ' ,p_internal_type : '|| p_internal_type);
3803 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module,' p_line_number : '|| p_line_number || ' ,p_sequence_number : ' || p_sequence_number );
3804 END IF;
3805 END IF;
3806
3807 -- USE PARENT_SCENARIO_ID TO JOIN TO MAIN SCENARIO
3808 -- DETERMINE THE COST AS FOLLOWS -
3809
3810 SELECT (PARENT_SCENARIO.TOTAL_AWARD_AMOUNT - COST_SCENARIO.TOTAL_AWARD_AMOUNT),
3811 PARENT_SCENARIO.SCENARIO_ID
3812 INTO L_COST_OF_CONSTRAINT,
3813 L_PARENT_SCENARIO_ID
3814 FROM PON_OPTIMIZE_SCENARIOS COST_SCENARIO,
3815 PON_OPTIMIZE_SCENARIOS PARENT_SCENARIO
3816 WHERE COST_SCENARIO.SCENARIO_ID = p_scenario_id
3817 AND PARENT_SCENARIO.SCENARIO_ID = COST_SCENARIO.PARENT_SCENARIO_ID;
3818
3819 IF (g_debug_mode = 'Y') THEN
3820 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3821 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 );
3822 END IF;
3823 END IF;
3824
3825 -- UPDATE THE CORRESPONDING ROW IN CONSTRAINTS TABLE
3826 -- OF PARENT SCENARIO WITH THIS COST OF CONSTRAINT
3827
3828 IF (p_constraint_type = 'LINE_CONST') THEN
3829
3830 IF (p_internal_type = 'LINE_SPLIT_AWARD') THEN
3831
3832 IF (g_debug_mode = 'Y') THEN
3833 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3834 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SPLIT_AWARD internal type constraint');
3835 END IF;
3836 END IF;
3837
3838 UPDATE PON_OPTIMIZE_CONSTRAINTS
3839 SET SPLIT_AWARD_COST = l_cost_of_constraint
3840 WHERE SCENARIO_ID = l_parent_scenario_id
3841 AND SEQUENCE_NUMBER = p_sequence_number
3842 AND CONSTRAINT_TYPE = p_constraint_type;
3843
3844 ELSIF (p_internal_type = 'LINE_INTEGER_QUANTITY') THEN
3845
3846 IF (g_debug_mode = 'Y') THEN
3847 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3848 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_INTEGER_QUANTITY internal type constraint');
3849 END IF;
3850 END IF;
3851
3852 UPDATE PON_OPTIMIZE_CONSTRAINTS
3853 SET INTEGRAL_QTY_AWARD_COST = l_cost_of_constraint
3854 WHERE SCENARIO_ID = l_parent_scenario_id
3855 AND SEQUENCE_NUMBER = p_sequence_number
3856 AND CONSTRAINT_TYPE = p_constraint_type;
3857
3858
3859 ELSIF (p_internal_type = 'LINE_AWARD_QTY') THEN
3860
3861 IF (g_debug_mode = 'Y') THEN
3862 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3863 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_QTY internal type constraint');
3864 END IF;
3865 END IF;
3866
3867 UPDATE PON_OPTIMIZE_CONSTRAINTS
3868 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
3869 WHERE SCENARIO_ID = l_parent_scenario_id
3870 AND SEQUENCE_NUMBER = p_sequence_number
3871 AND CONSTRAINT_TYPE = p_constraint_type;
3872
3873
3874 ELSIF (p_internal_type = 'LINE_AWARD_AMOUNT') THEN
3875
3876 IF (g_debug_mode = 'Y') THEN
3877 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3878 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_AWARD_AMOUNT internal type constraint');
3879 END IF;
3880 END IF;
3881
3882 UPDATE PON_OPTIMIZE_CONSTRAINTS
3883 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
3884 WHERE SCENARIO_ID = l_parent_scenario_id
3885 AND SEQUENCE_NUMBER = p_sequence_number
3886 AND CONSTRAINT_TYPE = p_constraint_type;
3887
3888
3889 ELSIF (p_internal_type = 'LINE_MIN_QTY') THEN
3890
3891 IF (g_debug_mode = 'Y') THEN
3892 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3893 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_QTY internal type constraint');
3894 END IF;
3895 END IF;
3896
3897 UPDATE PON_OPTIMIZE_CONSTRAINTS
3898 SET QUANTITY_CUTOFF_COST = l_cost_of_constraint
3899 WHERE SCENARIO_ID = l_parent_scenario_id
3900 AND SEQUENCE_NUMBER = p_sequence_number
3901 AND CONSTRAINT_TYPE = p_constraint_type;
3902
3903
3904
3905 ELSIF (p_internal_type = 'LINE_MAX_PRICE') THEN
3906
3907 IF (g_debug_mode = 'Y') THEN
3908 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3909 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MAX_PRICE internal type constraint');
3910 END IF;
3911 END IF;
3912
3913 UPDATE PON_OPTIMIZE_CONSTRAINTS
3914 SET PRICE_CUTOFF_COST = l_cost_of_constraint
3915 WHERE SCENARIO_ID = l_parent_scenario_id
3916 AND SEQUENCE_NUMBER = p_sequence_number
3917 AND CONSTRAINT_TYPE = p_constraint_type;
3918
3919
3920
3921 ELSIF (p_internal_type = 'LINE_MIN_SCORE') THEN
3922
3923 IF (g_debug_mode = 'Y') THEN
3924 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3925 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_MIN_SCORE internal type constraint');
3926 END IF;
3927 END IF;
3928
3929 UPDATE PON_OPTIMIZE_CONSTRAINTS
3930 SET MIN_SCORE_COST = l_cost_of_constraint
3931 WHERE SCENARIO_ID = l_parent_scenario_id
3932 AND SEQUENCE_NUMBER = p_sequence_number
3933 AND CONSTRAINT_TYPE = p_constraint_type;
3934
3935 ELSIF (p_internal_type = 'LINE_PROMISED_DATE') THEN
3936
3937 IF (g_debug_mode = 'Y') THEN
3938 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3939 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_PROMISED_DATE internal type constraint');
3940 END IF;
3941 END IF;
3942
3943 UPDATE PON_OPTIMIZE_CONSTRAINTS
3944 SET PROMISED_DATE_COST = l_cost_of_constraint
3945 WHERE SCENARIO_ID = l_parent_scenario_id
3946 AND SEQUENCE_NUMBER = p_sequence_number
3947 AND CONSTRAINT_TYPE = p_constraint_type;
3948
3949 END IF;
3950
3951 ELSIF (p_constraint_type = 'SUPP_LINE_CONST') THEN
3952
3953 -- three constraints
3954
3955 -- Line Number of Suppliers : same seqnum
3956 -- Line Any One Supplier : same seqnum
3957 -- Line-level award amount and
3958 -- award quantity for each supplier : different seqnum
3959
3960
3961 IF (p_internal_type = 'LINE_NUM_SUPP') THEN
3962
3963 IF (g_debug_mode = 'Y') THEN
3964 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3965 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_NUM_SUPP internal type constraint');
3966 END IF;
3967 END IF;
3968
3969 UPDATE PON_OPTIMIZE_CONSTRAINTS
3970 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
3971 WHERE SCENARIO_ID = l_parent_scenario_id
3972 AND SEQUENCE_NUMBER = p_sequence_number
3973 AND CONSTRAINT_TYPE = p_constraint_type;
3974
3975
3976 ELSIF (p_internal_type = 'LINE_ANY_ONE_SUPP') THEN
3977
3978 IF (g_debug_mode = 'Y') THEN
3979 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3980 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_ANY_ONE_SUPP internal type constraint');
3981 END IF;
3982 END IF;
3983
3984 UPDATE PON_OPTIMIZE_CONSTRAINTS
3985 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
3986 WHERE SCENARIO_ID = l_parent_scenario_id
3987 AND SEQUENCE_NUMBER = p_sequence_number
3988 AND CONSTRAINT_TYPE = p_constraint_type;
3989
3990 END IF;
3991
3992 ELSIF (p_constraint_type = 'BID_LINE_CONST') THEN
3993
3994 IF (p_internal_type = 'LINE_SUPP_SITE_AMT') THEN
3995
3996 IF (g_debug_mode = 'Y') THEN
3997 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3998 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_AMT internal type constraint');
3999 END IF;
4000 END IF;
4001
4002 UPDATE PON_OPTIMIZE_CONSTRAINTS
4003 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
4004 WHERE SCENARIO_ID = l_parent_scenario_id
4005 AND SEQUENCE_NUMBER = p_sequence_number
4006 AND CONSTRAINT_TYPE = p_constraint_type;
4007
4008 ELSIF (p_internal_type = 'LINE_SUPP_SITE_QTT') THEN
4009
4010 IF (g_debug_mode = 'Y') THEN
4011 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4012 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SUPP_SITE_QTT internal type constraint');
4013 END IF;
4014 END IF;
4015
4016 UPDATE PON_OPTIMIZE_CONSTRAINTS
4017 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
4018 WHERE SCENARIO_ID = l_parent_scenario_id
4019 AND SEQUENCE_NUMBER = p_sequence_number
4020 AND CONSTRAINT_TYPE = p_constraint_type;
4021
4022 END IF;
4023 ELSIF (p_constraint_type = 'SUPP_BIZ_CONST') THEN
4024
4025 IF (g_debug_mode = 'Y') THEN
4026 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4027 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating SUPP_BIZ_CONST constraint type');
4028 END IF;
4029 END IF;
4030
4031 UPDATE PON_OPTIMIZE_CONSTRAINTS
4032 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
4033 WHERE SCENARIO_ID = l_parent_scenario_id
4034 AND SEQUENCE_NUMBER = p_sequence_number
4035 AND CONSTRAINT_TYPE = p_constraint_type;
4036
4037 ELSIF ( p_constraint_type = 'BUDGET_AMT_CONST' OR
4038 p_constraint_type = 'INCUMBENT_SUPP_CONST' OR
4039 p_constraint_type = 'ANY_SUPP_CONST' OR
4040 p_constraint_type = 'SUPP_ASL_CONST' OR
4041 p_constraint_type = 'SUPPLIER_CONST' ) THEN
4042
4043
4044 IF (g_debug_mode = 'Y') THEN
4045 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4046 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating BUDGET_AMT_CONST, INCUMBENT_SUPP_CONST, ANY_SUPP_CONST, ');
4047 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'SUPP_ASL_CONST, SUPPLIER_CONST constraint types');
4048 END IF;
4049 END IF;
4050
4051 UPDATE PON_OPTIMIZE_CONSTRAINTS
4052 SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
4053 WHERE SCENARIO_ID = l_parent_scenario_id
4054 AND SEQUENCE_NUMBER = p_sequence_number
4055 AND CONSTRAINT_TYPE = p_constraint_type;
4056
4057 ELSIF (p_constraint_type = 'NUM_OF_SUPP_CONST') THEN
4058
4059 IF (g_debug_mode = 'Y') THEN
4060 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4061 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating NUM_OF_SUPP_CONST constraint type');
4062 END IF;
4063 END IF;
4064
4065 UPDATE PON_OPTIMIZE_CONSTRAINTS
4066 SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
4067 WHERE SCENARIO_ID = l_parent_scenario_id
4068 AND SEQUENCE_NUMBER = p_sequence_number
4069 AND CONSTRAINT_TYPE = p_constraint_type;
4070
4071 ELSIF ( p_constraint_type = 'TOTAL_HDR_ATTR_CONST' OR
4072 p_constraint_type = 'HDR_ATTR_CONST' ) THEN
4073
4074 IF (g_debug_mode = 'Y') THEN
4075 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4076 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating TOTAL_HDR_ATTR_CONST, HDR_ATTR_CONST constraint types');
4077 END IF;
4078 END IF;
4079
4080 UPDATE PON_OPTIMIZE_CONSTRAINTS
4081 SET MIN_SCORE_COST = l_cost_of_constraint
4082 WHERE SCENARIO_ID = l_parent_scenario_id
4083 AND SEQUENCE_NUMBER = p_sequence_number
4084 AND CONSTRAINT_TYPE = p_constraint_type;
4085
4086 ELSIF (p_constraint_type = 'INCL_HOLD_SUPP_CONST') THEN
4087
4088 IF (g_debug_mode = 'Y') THEN
4089 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4090 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating INCL_HOLD_SUPP_CONST constraint type');
4091 END IF;
4092 END IF;
4093
4094 UPDATE PON_OPTIMIZE_CONSTRAINTS
4095 SET EXCLUDED_SUPPLIER_COST = l_cost_of_constraint
4096 WHERE SCENARIO_ID = l_parent_scenario_id
4097 AND SEQUENCE_NUMBER = p_sequence_number
4098 AND CONSTRAINT_TYPE = p_constraint_type;
4099
4100 ELSIF (p_constraint_type = 'NO_SPLIT_GROUP_CONST') THEN
4101
4102 IF (g_debug_mode = 'Y') THEN
4103 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4104 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating NO_SPLIT_GROUP_CONST constraint type');
4105 END IF;
4106 END IF;
4107
4108 UPDATE PON_OPTIMIZE_CONSTRAINTS
4109 SET SPLIT_AWARD_COST = l_cost_of_constraint
4110 WHERE SCENARIO_ID = l_parent_scenario_id
4111 AND SEQUENCE_NUMBER = p_sequence_number
4112 AND CONSTRAINT_TYPE = p_constraint_type;
4113 END IF;
4114
4115 IF (g_debug_mode = 'Y') THEN
4116 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4117 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before deleting the dummy scneario with p_scenario_id: ' || p_scenario_id );
4118 END IF;
4119 END IF;
4120
4121 DELETE FROM PON_OPTIMIZE_SCENARIOS WHERE SCENARIO_ID = P_SCENARIO_ID;
4122
4123 -- do some basic initialization
4124
4125 l_num_constraints := 0;
4126 l_num_bid_classes := 0;
4127 l_num_results := 0;
4128
4129
4130 -- make sure there is at least 1 row to prevent no data found exception
4131 select count(*) into l_num_constraints
4132 from pon_optimize_constraints
4133 where scenario_id = p_scenario_id;
4134
4135 IF (l_num_constraints > 0) THEN
4136 IF (g_debug_mode = 'Y') THEN
4137 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4138 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 );
4139 END IF;
4140 END IF;
4141
4142 DELETE FROM PON_OPTIMIZE_CONSTRAINTS WHERE SCENARIO_ID = P_SCENARIO_ID;
4143
4144 END IF;
4145
4146 select count(*) into l_num_bid_classes
4147 from pon_optimize_bid_class
4148 where scenario_id = p_scenario_id;
4149
4150 IF (l_num_bid_classes > 0) THEN
4151 IF (g_debug_mode = 'Y') THEN
4152 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4153 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 );
4154 END IF;
4155 END IF;
4156
4157 DELETE FROM PON_OPTIMIZE_BID_CLASS WHERE SCENARIO_ID = P_SCENARIO_ID;
4158 END IF;
4159
4160 select count(*) into l_num_results
4161 from pon_optimize_results
4162 where scenario_id = p_scenario_id;
4163
4164 IF(l_num_results > 0) THEN
4165 IF (g_debug_mode = 'Y') THEN
4166 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4167 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 );
4168 END IF;
4169 END IF;
4170
4171 DELETE FROM PON_OPTIMIZE_RESULTS WHERE SCENARIO_ID = P_SCENARIO_ID;
4172
4173 END IF;
4174
4175 X_STATUS := 'SUCCESS';
4176
4177 EXCEPTION
4178 WHEN OTHERS THEN
4179 X_STATUS := 'FAILURE';
4180 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4181 FND_LOG.STRING(log_level => fnd_log.LEVEL_UNEXPECTED
4182 ,module => l_module
4183 ,message => 'When others exception raised in postprocess_cost_of_constraint');
4184 END IF;
4185
4186 RAISE;
4187 END;
4188
4189
4190 PROCEDURE reset_cost_of_constraint
4191 (
4192 p_scenario_id IN NUMBER,
4193 x_status OUT NOCOPY VARCHAR2
4194 )
4195
4196 IS
4197
4198 l_num_constraints NUMBER;
4199
4200 BEGIN
4201
4202 -- initialize number of constraints
4203 l_num_constraints := -1;
4204
4205 -- make sure there is at least 1 row to prevent no data found exception
4206 select count(*) into l_num_constraints
4207 from pon_optimize_constraints
4208 where scenario_id = p_scenario_id;
4209
4210 IF (l_num_constraints > 0) THEN
4211
4212 UPDATE PON_OPTIMIZE_CONSTRAINTS
4213 SET
4214 MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
4215 MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
4216 PRICE_CUTOFF_COST = TO_NUMBER(NULL),
4217 SPLIT_AWARD_COST = TO_NUMBER(NULL),
4218 QUANTITY_CUTOFF_COST = TO_NUMBER(NULL),
4219 INTEGRAL_QTY_AWARD_COST = TO_NUMBER(NULL),
4220 EXCLUDED_SUPPLIER_COST = TO_NUMBER(NULL),
4221 PROMISED_DATE_COST = TO_NUMBER(NULL),
4222 MIN_SCORE_COST = TO_NUMBER(NULL)
4223 WHERE
4224 SCENARIO_ID = P_SCENARIO_ID;
4225
4226 END IF;
4227
4228 X_STATUS := 'SUCCESS';
4229
4230 EXCEPTION
4231 WHEN OTHERS THEN
4232 X_STATUS := 'FAILURE';
4233 RAISE;
4234 END;
4235
4236 /*======================================================================
4237 FUNCTION : GET_SAVING_PERCENT_INCENTIVE PUBLIC
4238 PARAMETERS:
4239 p_scenario_id IN scenario id
4240
4241 COMMENT : Returns the saving percent of the given scenario.
4242 ======================================================================*/
4243 FUNCTION GET_SAVING_PERCENT_INCENTIVE (p_scenario_id IN NUMBER)
4244 RETURN NUMBER
4245 IS
4246 L_SAVING_PERCENT NUMBER := 0;
4247
4248 l_total_award NUMBER := 0;
4249 l_new_rebate NUMBER := 0;
4250 l_incentive NUMBER := 0;
4251 l_total_incentive NUMBER := 0;
4252 l_total_current_amount NUMBER := 0;
4253
4254 l_bid_number_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
4255 l_award_total_sum_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
4256 l_savings_amount_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
4257 l_current_amount_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
4258 l_current_total_spend_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
4259 l_fixed_incentive_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
4260 l_current_rebate_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
4261
4262 BEGIN
4263
4264 SELECT
4265 bi.bid_number as selected_bid_number,
4266 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,
4267 SUM(por.award_quantity * nvl2(ai.current_price, (ai.current_price -por.award_price), 0)) AS savings_amount,
4268 SUM(por.award_quantity * nvl(ai.current_price,por.award_price)) AS current_amount,
4269 nvl(pbh.CURRENT_TOTAL_SPEND, 0) CURRENT_TOTAL_SPEND,
4270 nvl(pbh.FIXED_INCENTIVE, 0) FIXED_INCENTIVE,
4271 nvl(pbh.CURRENT_REBATE,0) CURRENT_REBATE
4272 BULK COLLECT INTO
4273 l_bid_number_col ,
4274 l_award_total_sum_col ,
4275 l_savings_amount_col,
4276 l_current_amount_col,
4277 l_current_total_spend_col,
4278 l_fixed_incentive_col,
4279 l_current_rebate_col
4280 FROM
4281 pon_bid_item_prices bi,
4282 pon_auction_item_prices_all ai,
4283 pon_optimize_scenarios pos,
4284 pon_optimize_results por,
4285 pon_bid_shipments pbs,
4286 pon_bid_headers pbh
4287 WHERE
4288 por.scenario_id = pos.scenario_id
4289 AND
4290 por.bid_number = bi.bid_number
4291 AND
4292 pos.auction_header_id = bi.auction_header_id
4293 AND
4294 ai.auction_header_id = bi.auction_header_id
4295 AND
4296 bi.line_number = por.line_number
4297 AND
4298 ai.line_number = bi.line_number
4299 AND
4300 ai.group_type in ('LINE', 'LOT', 'GROUP_LINE')
4301 AND
4302 nvl(por.award_quantity, -1) > 0
4303 AND
4304 por.bid_number = pbh.bid_number
4305 and
4306 pos.scenario_id = p_scenario_id
4307 AND por.bid_number = pbs.bid_number(+)
4308 AND por.line_number = pbs.line_number(+)
4309 AND nvl(por.award_shipment_number, -1) = pbs.shipment_number(+)
4310 GROUP BY bi.bid_number, pbh.CURRENT_TOTAL_SPEND, pbh.FIXED_INCENTIVE, pbh.CURRENT_REBATE;
4311
4312 FOR i IN 1..l_bid_number_col.COUNT LOOP
4313
4314 l_total_Award := l_award_total_sum_col(i) + l_current_total_spend_col(i);
4315
4316 BEGIN
4317 select rebate
4318 into l_new_rebate
4319 from pon_bid_rebates
4320 where bid_number = l_bid_number_col(i)
4321 and l_total_award between lower_spend and upper_spend;
4322
4323 EXCEPTION WHEN NO_DATA_FOUND THEN
4324 l_new_rebate := 0;
4325 END;
4326
4327 if l_new_rebate = 0 then
4328 l_new_rebate := l_current_rebate_col(i);
4329 end if;
4330
4331 l_incentive := l_fixed_incentive_col(i) + (l_award_total_sum_col(i) * l_new_rebate / 100 )
4332 + l_current_total_spend_col(i) * ( l_new_rebate - l_current_rebate_col(i) )/100;
4333
4334 l_total_incentive := l_total_incentive + l_incentive + l_savings_amount_col(i);
4335 l_total_current_amount := l_total_current_amount + l_current_amount_col(i);
4336 END LOOP;
4337
4338 if l_total_current_amount <> 0 then
4339 l_saving_percent := l_total_incentive/l_total_current_amount*100;
4340 end if;
4341
4342 RETURN l_saving_percent;
4343 EXCEPTION
4344 WHEN OTHERS THEN
4345 RETURN 0;
4346 END GET_SAVING_PERCENT_INCENTIVE;
4347
4348
4349 END PON_AWARD_PKG;
[Home] [Help]