[Home] [Help]
PACKAGE BODY: APPS.PON_NEG_UPDATE_PKG
Source
1 PACKAGE BODY PON_NEG_UPDATE_PKG as
2 /* $Header: PONUPDTB.pls 120.11 2007/06/01 09:26:03 mshujath ship $ */
3 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_NEG_UPDATE_PKG.';
4
5 -------Variables---------
6 -- used for AutoExtension, to represent unlimited int, this needs to
7 -- match SourcingUtil.UNLIMITED_INT
8 UNLIMITED_INT NUMBER := 10000;
9 --------------------------
10
11
12 PROCEDURE MANUAL_CLOSE (p_auction_header_id IN NUMBER,
13 p_close_now_flag IN VARCHAR2,
14 p_new_close_date IN DATE,
15 p_reason IN VARCHAR2,
16 p_user_id IN NUMBER) IS
17
18 x_temp DATE;
19 x_close_bidding_date DATE;
20 BEGIN
21
22 -- lock negotiation header
23 SELECT LAST_UPDATE_DATE, CLOSE_BIDDING_DATE
24 INTO x_temp, x_close_bidding_date
25 FROM PON_AUCTION_HEADERS_ALL
26 WHERE AUCTION_HEADER_ID = p_auction_header_id
27 FOR UPDATE;
28
29 -- update header close date
30 update pon_auction_headers_all
31 set close_bidding_date = p_new_close_date,
32 original_close_bidding_date = x_close_bidding_date,
33 last_update_date = sysdate,
34 last_updated_by = p_user_id
35 where auction_header_id = p_auction_header_id;
36
37 -- update item close date
38 update pon_auction_item_prices_all
39 set close_bidding_date = p_new_close_date,
40 last_update_date = sysdate,
41 last_updated_by = p_user_id
42 where auction_header_id = p_auction_header_id
43 and close_bidding_date > p_new_close_date;
44
45 -- send notifications
46 if (p_close_now_flag = 'Y') then
47 pon_auction_pkg.CLOSEEARLY_AUCTION(p_auction_header_id,
48 p_new_close_date,
49 p_reason);
50 else
51 pon_auction_pkg.CLOSECHANGED_AUCTION(p_auction_header_id,
52 2, -- type code
53 p_new_close_date,
54 p_reason);
55 end if;
56
57 END MANUAL_CLOSE;
58
59
60 PROCEDURE CANCEL_NEGOTIATION (p_auction_header_id IN NUMBER,
61 p_send_note_flag IN VARCHAR2,
62 p_reason IN VARCHAR2,
63 p_user_id IN NUMBER,
64 x_error_code OUT NOCOPY VARCHAR2) IS
65
66
67 x_temp DATE;
68 x_auction_origination_code pon_auction_headers_all.auction_origination_code%TYPE;
69 x_trading_partner_contact_id pon_auction_headers_all.trading_partner_contact_id%TYPE;
70
71 BEGIN
72
73 x_error_code := 'SUCCESS';
74
75 -- select data from header
76 select auction_origination_code, trading_partner_contact_id
77 into x_auction_origination_code, x_trading_partner_contact_id
78 from pon_auction_headers_all
79 where auction_header_id = p_auction_header_id;
80
81 -- lock the negotiation
82 SELECT LAST_UPDATE_DATE
83 INTO x_temp
84 FROM PON_AUCTION_HEADERS_ALL
85 WHERE AUCTION_HEADER_ID = p_auction_header_id
86 FOR UPDATE;
87
88 -- update header
89 -- update the pause details also.
90 update pon_auction_headers_all
91 set auction_status = 'CANCELLED',
92 cancel_date = sysdate,
93 last_update_date = sysdate,
94 last_updated_by = p_user_id,
95 is_paused = null,
96 pause_remarks = null,
97 last_pause_date = null
98 where auction_header_id = p_auction_header_id;
99
100 -- Record Action History
101 PON_ACTION_HIST_PKG.RECORDHISTORY(p_auction_header_id,
102 -1,
103 'PON_AUCTION',
104 'CANCEL',
105 x_trading_partner_contact_id,
106 p_reason,
107 null,
108 null,
109 'N');
110
111 -- if an autocreated document is cancelled, return the
112 -- backing requisitions to the req. pool
113 if (x_auction_origination_code = 'REQUISITION') then
114 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF(p_auction_header_id, x_error_code);
115 end if;
116
117 -- if good so far, kick off workflow notification process.
118 if (p_send_note_flag = 'Y' and x_error_code = 'SUCCESS') then
119 PON_AUCTION_PKG.CANCEL_AUCTION(p_auction_header_id);
120 end if;
121
122 END CANCEL_NEGOTIATION;
123
124 --
125 -- Procedure to Manually Extend a Negotiation
126 -- Called from oracle.apps.pon.negotiation.tools.server.ManualExtendAMImpl.java
127 -- Called from oracle.apps.pon.negotiation.tools.server.NegPauseAMImpl.java
128 -- Negotiation Resume also will be treated as Manual Extend operation.
129 --
130 PROCEDURE MANUAL_EXTEND (p_auction_header_id IN NUMBER,
131 p_close_date IN DATE,
132 p_new_close_date IN DATE,
133 p_is_autoExtend IN VARCHAR2,
134 p_new_autoextend_num IN NUMBER,
135 p_is_allExtend IN VARCHAR2,
136 p_new_duration IN NUMBER,
137 p_new_extend_type IN VARCHAR2,
138 p_user_id IN NUMBER,
139 p_last_updated_date IN DATE,
140 p_auto_extend_min_trigger IN NUMBER,
141 p_result OUT NOCOPY NUMBER,
142 p_extended_close_bidding_date OUT NOCOPY DATE ) IS
143
144
145 x_update_date DATE;
146 x_new_close_date DATE;
147 x_close_date DATE;
148 v_pause_date DATE;
149 v_ispaused VARCHAR2(1);
150 v_is_staggered VARCHAR2(1);
151 x_new_autoextend_num NUMBER;
152 x_num_extension_occurred NUMBER;
153 x_first_line_close_date DATE;
154
155 BEGIN
156
157 ------------- RETURN VALUES ----------------------------------
158 -- Value -- MEANING --------- -
159 -- 0 Successful -
160 -- 1 Concurrency Problem (STALE DATA) -
161 -- 2 New Close Date is less than SYSDATE -
162 -- 3 Negotiation is already closed -
163 -- 4 New Close Date less than Original one -
164 -- 6 Negotiation is already paused -
165 ---------------------------------------------------------------------
166
167 -- lock the negotiation header
168 SELECT LAST_UPDATE_DATE, nvl( IS_PAUSED, 'N' ), nvl( LAST_PAUSE_DATE, sysdate ), nvl(number_of_extensions, 0),
169 nvl2(staggered_closing_interval,'Y','N'), first_line_close_date
170 INTO x_update_date, v_ispaused, v_pause_date, x_num_extension_occurred, v_is_staggered, x_first_line_close_date
171 FROM PON_AUCTION_HEADERS_ALL
172 WHERE AUCTION_HEADER_ID = p_auction_header_id
173 FOR UPDATE;
174
175
176 -- check the negotiation is already paused or not.
177 -- for resume operation, p_is_autoExtend argument will have "R" value.
178 -- 'R' indicates : "Resume" operation.
179 -- the p_new_close_date value will be null for resume operations.
180 if (p_is_autoExtend <> 'R' and v_ispaused = 'Y' ) then
181 p_result := 6;
182 return;
183 end if;
184
185 --Verify concurrency
186 if (x_update_date <> p_last_updated_date) then
187 p_result := 1;
188 return;
189 end if;
190
191 if (v_ispaused = 'Y') then
192 x_new_close_date := sysdate + ( p_close_date - v_pause_date );
193
194 IF (v_is_staggered = 'Y' AND sysdate <= x_first_line_close_date) THEN
195 x_first_line_close_date := x_first_line_close_date + ( sysdate - v_pause_date );
196 END IF;
197
198 else
199 if (SYSDATE >= p_close_date) then
200 p_result := 3;
201 return;
202 end if;
203
204 IF (v_is_staggered = 'Y' AND sysdate <= x_first_line_close_date) THEN
205
206 x_first_line_close_date := x_first_line_close_date + ( p_new_close_date - p_close_date);
207
208 END IF;
209
210 x_new_close_date := p_new_close_date;
211
212 if (SYSDATE >= x_new_close_date) then
213 p_result := 2;
214 return;
215 end if;
216
217 if (p_close_date > x_new_close_date) then
218 p_result := 4;
219 return;
220 end if;
221 end if;
222
223 -- This extended close bidding date will be retrieved from the java layer.
224 p_extended_close_bidding_date := x_new_close_date;
225
226 -- update header close date and reset the pause related fields.
227 UPDATE PON_AUCTION_HEADERS_ALL
228 SET CLOSE_BIDDING_DATE = x_new_close_date,
229 LAST_UPDATE_DATE= sysdate,
230 LAST_UPDATED_BY = p_user_id,
231 ORIGINAL_CLOSE_BIDDING_DATE = p_close_date,
232 FIRST_LINE_CLOSE_DATE = x_first_line_close_date,
233 IS_PAUSED = NULL,
234 PAUSE_REMARKS = NULL,
235 LAST_PAUSE_DATE = NULL
236 WHERE AUCTION_HEADER_ID = p_auction_header_id;
237
238 -- update the item prices all table close bidding date.
239 -- do not update the close date for already closed items.
240 UPDATE PON_AUCTION_ITEM_PRICES_ALL
241 SET CLOSE_BIDDING_DATE = CLOSE_BIDDING_DATE + (x_new_close_date-p_close_date),
242 LAST_UPDATE_DATE= sysdate,
243 LAST_UPDATED_BY = p_user_id
244 WHERE AUCTION_HEADER_ID = p_auction_header_id
245 AND CLOSE_BIDDING_DATE > decode(v_ispaused, 'Y', v_pause_date, SYSDATE) ;
246
247
248 -- for manual extend, if the need by date is before the new close bidding
249 -- date, then need extend need by date behind the scene
250 UPDATE PON_AUCTION_ITEM_PRICES_ALL
251 SET NEED_BY_DATE = NEED_BY_DATE + (x_new_close_date-p_close_date),
252 LAST_UPDATE_DATE= sysdate,
253 LAST_UPDATED_BY = p_user_id
254 WHERE AUCTION_HEADER_ID = p_auction_header_id
255 AND NEED_BY_DATE < x_new_close_date;
256
257 UPDATE PON_AUCTION_ITEM_PRICES_ALL
258 SET NEED_BY_START_DATE = NEED_BY_START_DATE + (x_new_close_date-p_close_date),
259 LAST_UPDATE_DATE= sysdate,
260 LAST_UPDATED_BY = p_user_id
261 WHERE AUCTION_HEADER_ID = p_auction_header_id
262 AND NEED_BY_START_DATE < x_new_close_date;
263
264 -- Update AutoExtend information
265 -- For the Auto-extension case, the p_new_autoextend_num represents
266 -- additional extensions needed. If it's set to unlimited, set the
267 -- new auto extension number to unlimited, else add it to the number
268 -- of extensions that have already occurred.
269 if (p_is_autoExtend = 'Y') then
270 if (p_new_autoextend_num = UNLIMITED_INT) then
271 x_new_autoextend_num := UNLIMITED_INT;
272 else
273 x_new_autoextend_num := x_num_extension_occurred+p_new_autoextend_num;
274 end if;
275 UPDATE PON_AUCTION_HEADERS_ALL
276 SET AUTO_EXTEND_FLAG = 'Y',
277 AUTO_EXTEND_NUMBER = x_new_autoextend_num,
278 AUTO_EXTEND_ALL_LINES_FLAG = p_is_allExtend,
279 AUTO_EXTEND_DURATION = p_new_duration,
280 AUTO_EXTEND_TYPE_FLAG = p_new_extend_type,
281 AUTOEXTEND_CHANGED_FLAG = 'Y',
282 AUTO_EXTEND_MIN_TRIGGER_RANK = p_auto_extend_min_trigger
283 WHERE AUCTION_HEADER_ID = p_auction_header_id;
284 end if;
285
286 -- Call WorkFlow
287 -- dont call the work flow if the negotiation is paused (Resume Negotaition case).
288 if (v_ispaused <> 'Y') then
289 if ( p_close_date < x_new_close_date) then
290 pon_auction_pkg.CLOSECHANGED_AUCTION(p_auction_header_id,
291 1, -- type code
292 x_new_close_date,
293 NULL);
294 end if;
295 end if;
296
297 p_result := 0;
298
299 END MANUAL_EXTEND;
300
301 --
302 -- When deleting a new round draft negotiation, need to activate previous round negotiation
303 --
304
305
306 PROCEDURE ACTIVATE_PREV_ROUND_NEG (p_prev_round_auction_header_id IN NUMBER) IS
307
308 BEGIN
309
310 update pon_auction_headers_all
311 set auction_status = 'ACTIVE'
312 where auction_header_id = p_prev_round_auction_header_id;
313
314 END ACTIVATE_PREV_ROUND_NEG;
315
316
317 --
318 -- If the negotiation that the buyer is amending becomes closed or cancelled,
319 -- the buyer will not be able to submit the amendment.
320 -- An error messaage will be displayed to the buyer
321
322 PROCEDURE CAN_EDIT_DRAFT_AMEND (p_auction_header_id_prev_doc IN NUMBER,
323 x_error_code OUT NOCOPY VARCHAR2) IS
324
325
326 v_auction_status VARCHAR2(25);
327 v_award_status VARCHAR2(25);
328 v_view_by_date DATE;
329 v_open_bidding_date DATE;
330 v_close_bidding_date DATE;
331 v_sysdate DATE;
332 v_is_paused VARCHAR2(1);
333
334 l_module_name VARCHAR2(30) := 'CAN_EDIT_DRAFT_AMEND';
335
336 BEGIN
337
338 select auction_status, award_status, view_by_date, open_bidding_date, close_bidding_date, sysdate, NVL( is_paused, 'N')
339 into v_auction_status, v_award_status, v_view_by_date, v_open_bidding_date, v_close_bidding_date, v_sysdate, v_is_paused
340 from pon_auction_headers_all
341 where auction_header_id = p_auction_header_id_prev_doc;
342
343 if v_is_paused <> 'Y' then
344
345 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
346 FND_LOG.string (log_level => FND_LOG.level_procedure,
347 module => g_module_prefix || l_module_name,
348 message => 'The auction is not paused.');
349 END IF;
350
351 if (v_close_bidding_date < v_sysdate OR
352 v_auction_status is not null AND v_auction_status = 'CANCELLED') then
353 x_error_code := 'ERROR';
354 else
355 x_error_code := 'SUCCESS';
356 end if;
357 else
358 x_error_code := 'SUCCESS';
359 end if;
360
361 END CAN_EDIT_DRAFT_AMEND;
362
363
364 --
365 -- Called during the amendment and new round processes, the procedure below
366 -- makes the necessary changes to move from the old document to the new one
367 -- Called from oracle.apps.pon.schema.server.AuctionHeadersALLEOImpl.java
368 --
369
370 PROCEDURE UPDATE_TO_NEW_DOCUMENT (p_auction_header_id_curr_doc IN NUMBER,
371 p_doc_number_curr_doc IN VARCHAR2,
372 p_auction_header_id_prev_doc IN NUMBER,
373 p_auction_origination_code IN VARCHAR2,
374 p_is_new IN VARCHAR2,
375 p_is_publish IN VARCHAR2,
376 p_transaction_type IN VARCHAR2,
377 p_user_id IN NUMBER,
378 x_error_code OUT NOCOPY VARCHAR2,
379 x_error_msg OUT NOCOPY VARCHAR2) IS
380
381
382 v_temp DATE;
383 v_doc_number_prev_doc pon_auction_headers_all.document_number%TYPE;
384
385 BEGIN
386
390 -- lock negotiation
387 x_error_code := 'SUCCESS';
388 x_error_msg := '';
389
391 SELECT LAST_UPDATE_DATE, document_number
392 INTO v_temp, v_doc_number_prev_doc
393 FROM PON_AUCTION_HEADERS_ALL
394 WHERE AUCTION_HEADER_ID = p_auction_header_id_prev_doc
395 FOR UPDATE;
396
397 -- when saving the document for the first time...
398 -- change the status of the previous round document
399 -- copy negotiation references in our tables (pon_backing_requisitions)
400
401
402 if (p_is_new = 'Y') then
403
404 -- update status of previous round document
405
406 if (p_transaction_type = 'CREATE_NEW_ROUND') then
407
408 update pon_auction_headers_all
409 set AWARD_STATUS = 'NO',
410 AUCTION_STATUS = 'AUCTION_CLOSED',
411 LAST_UPDATE_DATE = sysdate,
412 LAST_UPDATED_BY = p_user_id
413 where auction_header_id = p_auction_header_id_prev_doc;
414
415 end if;
416
417 -- if an autocreated document is being taken to a new round or amended,
418 -- copy negotiation references locally
419
420 if (p_auction_origination_code = 'REQUISITION') then
421
422 PON_AUCTION_PKG.COPY_BACKING_REQ(p_auction_header_id_prev_doc, p_auction_header_id_curr_doc, x_error_code);
423
424 end if;
425
426
427 end if;
428
429
430
431 -- when publishing the document....
432 -- call PO api to change negotiation references in their tables
433 -- if we're amended a document
434 -- 1) change the auction status and award status of the previous amendment to be AMENDED and NO respectively
435 -- 2) update previous 'ACTIVE' bids to be 'RESUBMISSION_REQUIRED'
436
437
438 if (p_is_publish = 'Y' and x_error_code = 'SUCCESS') then
439
440 if (p_auction_origination_code = 'REQUISITION') then
441
442 PON_AUCTION_PKG.UPDATE_NEGOTIATION_REF(p_auction_header_id_prev_doc, v_doc_number_prev_doc, p_auction_header_id_curr_doc, p_doc_number_curr_doc, x_error_code, x_error_msg);
443
444 end if;
445
446 -- previous active bids are no longer valid
447 -- suppliers will need to acknowledge amendments before their bid
448 -- becomes active again
449
450
451 -- update pause details also.
452 if (p_transaction_type = 'CREATE_AMENDMENT') then
453
454 update pon_auction_headers_all
455 set AWARD_STATUS = 'NO',
456 AUCTION_STATUS = 'AMENDED',
457 is_paused = null,
458 pause_remarks = null,
459 last_pause_date = null,
460 LAST_UPDATE_DATE = sysdate,
461 LAST_UPDATED_BY = p_user_id
462 where auction_header_id = p_auction_header_id_prev_doc;
463
464 update pon_bid_headers
465 set BID_STATUS = 'RESUBMISSION'
466 where auction_header_id = p_auction_header_id_prev_doc and
467 bid_status = 'ACTIVE';
468
469 end if;
470
471
472 end if;
473
474
475 END UPDATE_TO_NEW_DOCUMENT;
476
477
478 -- Updates the modified flag and last_amendment_update columns by comparing
479 -- the values of the user-enterable fields of the new negotiation
480 -- to the previous negotiation
481
482 PROCEDURE UPDATE_MODIFIED_FIELDS (p_currAuctionHeaderId IN NUMBER,
483 p_prevAuctionHeaderId IN NUMBER,
484 p_action IN VARCHAR2) IS
485
486 BEGIN
487
488 UPDATE_NEG_TEAM_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
489
490 UPDATE_CURRENCY_RATES_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
491
492 UPDATE_INVITEES_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
493
494 UPDATE_HDR_ATTR_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
495
496 END UPDATE_MODIFIED_FIELDS;
497
498
499 -- Updates the modified flag and last_amendment_update columns by comparing
500 -- the values of the user-enterable fields of the new negotiation
501 -- to the previous negotiation
502
503 PROCEDURE UPDATE_CURRENCY_RATES_MODIFIED (p_currAuctionHeaderId IN NUMBER,
504 p_prevAuctionHeaderId IN NUMBER,
505 p_action IN VARCHAR2) IS
506
507 v_currAmendmentNumber NUMBER;
508
509 BEGIN
510
511 select nvl(amendment_number, 0)
512 into v_currAmendmentNumber
513 from pon_auction_headers_all
514 where auction_header_id = p_currAuctionHeaderId;
515
516
517 -- first, reset the modified flag and last amendment update columns
518
519 if (p_action = 'MULTIROUND') then
520
521 update pon_auction_currency_rates
522 set MODIFIED_FLAG = null,
523 LAST_AMENDMENT_UPDATE = 0
524 where auction_header_id = p_currAuctionHeaderId;
525
526 else
527
528 update pon_auction_currency_rates rates
529 set MODIFIED_FLAG = null,
530 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
531 from pon_auction_currency_rates
532 where auction_header_id = p_prevAuctionHeaderId and
533 bid_currency_code = rates.bid_currency_code)
537 -- need to set last_amendment_update to current amendment number for those rows
534 where auction_header_id = p_currAuctionHeaderId;
535
536 -- since above query will set last_amendment_update to null for new rows
538
539 update pon_auction_currency_rates rates
540 set LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
541 where auction_header_id = p_currAuctionHeaderId and
542 last_amendment_update is null;
543
544 end if;
545
546 -- next, do pairwise comparisons to find updated rows
547
548 update pon_auction_currency_rates currRates
549 set MODIFIED_FLAG = 'Y',
550 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
551 where auction_header_id = p_currAuctionHeaderId and
552 exists (select null
553 from pon_auction_currency_rates prevRates
554 where prevRates.auction_header_id = p_prevAuctionHeaderId and
555 prevRates.bid_currency_code = currRates.bid_currency_code and
556 (nvl(prevRates.rate_dsp, -9999) <> nvl(currRates.rate_dsp, -9999) or
557 nvl(prevRates.number_price_decimals, -9999) <> nvl(currRates.number_price_decimals, -9999)));
558
559 END UPDATE_CURRENCY_RATES_MODIFIED;
560
561 -- Updates the modified flag and last_amendment_update columns by comparing
562 -- the values of the user-enterable fields of the new negotiation
563 -- to the previous negotiation
564
565 PROCEDURE UPDATE_NEG_TEAM_MODIFIED (p_currAuctionHeaderId IN NUMBER,
566 p_prevAuctionHeaderId IN NUMBER,
567 p_action IN VARCHAR2) IS
568
569 v_currAmendmentNumber NUMBER;
570
571 BEGIN
572
573 select nvl(amendment_number, 0)
574 into v_currAmendmentNumber
575 from pon_auction_headers_all
576 where auction_header_id = p_currAuctionHeaderId;
577
578
579 -- first, reset the modified flag and last amendment update columns
580
581 if (p_action = 'MULTIROUND') then
582
583 update pon_neg_team_members
584 set MODIFIED_FLAG = null,
585 LAST_AMENDMENT_UPDATE = 0
586 where auction_header_id = p_currAuctionHeaderId;
587
588 else
589
590 update pon_neg_team_members neg
591 set MODIFIED_FLAG = null,
592 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
593 from pon_neg_team_members
594 where auction_header_id = p_prevAuctionHeaderId and
595 user_id = neg.user_id)
596 where auction_header_id = p_currAuctionHeaderId;
597
598 -- since above query will set last_amendment_update to null for new rows
599 -- need to set last_amendment_update to current amendment number for those rows
600
601 update pon_neg_team_members neg
602 set LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
603 where auction_header_id = p_currAuctionHeaderId and
604 last_amendment_update is null;
605
606 end if;
607
608 -- next, do pairwise comparisons to find updated rows
609
610 update pon_neg_team_members currNeg
611 set MODIFIED_FLAG = 'Y',
612 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
613 where auction_header_id = p_currAuctionHeaderId and
614 exists (select null
615 from pon_neg_team_members prevNeg
616 where prevNeg.auction_header_id = p_prevAuctionHeaderId and
617 prevNeg.user_id = currNeg.user_id and
618
619 (nvl(prevNeg.approver_flag, 'N') <> nvl(currNeg.approver_flag, 'N') or
620 nvl(prevNeg.menu_name, 'PON_SOURCING_VIEWNEG') <> nvl(currNeg.menu_name, 'PON_SOURCING_VIEWNEG') or
621 nvl(prevNeg.task_name, 'null') <> nvl(currNeg.task_name, 'null') or
622 nvl(prevNeg.target_date, sysdate) <> nvl(currNeg.target_date, sysdate)));
623
624
625 END UPDATE_NEG_TEAM_MODIFIED;
626
627 -- Updates the modified flag and last_amendment_update columns by comparing
628 -- the values of the user-enterable fields of the new negotiation
629 -- to the previous negotiation
630
631 PROCEDURE UPDATE_INVITEES_MODIFIED (p_currAuctionHeaderId IN NUMBER,
632 p_prevAuctionHeaderId IN NUMBER,
633 p_action IN VARCHAR2) IS
634
635 v_currAmendmentNumber NUMBER;
636
637 BEGIN
638
639 select nvl(amendment_number, 0)
640 into v_currAmendmentNumber
641 from pon_auction_headers_all
642 where auction_header_id = p_currAuctionHeaderId;
643
644 -- first, reset the modified flag and last amendment update columns
645 -- only if modified flag is not "P" or "S" ...
646 -- the value "P" indicates that the buyer price factor values have been modified
647 -- and the value "S" indicates that the Supplier Access has been modified
648 -- so regardless if supplier info (contact, currency, etc...) has changed
649 -- modified flag will still have a value of "P" or "S"...
650 -- this will influence all sql below...
651
652 if (p_action = 'MULTIROUND') then
653
654 update pon_bidding_parties
655 set MODIFIED_FLAG = null,
656 LAST_AMENDMENT_UPDATE = 0
657 where auction_header_id = p_currAuctionHeaderId and
661 else
658 nvl(modified_flag, 'N') <> 'P' and
659 nvl(modified_flag, 'N') <> 'S';
660
662
663 update pon_bidding_parties invitees
664 set MODIFIED_FLAG = null,
665 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
666 from pon_bidding_parties
667 where auction_header_id = p_prevAuctionHeaderId and
668 (trading_partner_id = invitees.trading_partner_id
669 OR requested_supplier_id =invitees.requested_supplier_id) and
670 nvl(vendor_site_id, -9999) = nvl(invitees.vendor_site_id, -9999))
671 where auction_header_id = p_currAuctionHeaderId and
672 nvl(modified_flag, 'N') <> 'P' and
673 nvl(modified_flag, 'N') <> 'S';
674
675 -- since above query will set last_amendment_update to null for new rows
676 -- need to set last_amendment_update to current amendment number for those rows
677
678 update pon_bidding_parties invitees
679 set LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
680 where auction_header_id = p_currAuctionHeaderId and
681 last_amendment_update is null;
682
683 end if;
684
685 -- next, do pairwise comparisons to find updated rows
686
687 update pon_bidding_parties currInvitees
688 set MODIFIED_FLAG = 'Y',
689 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
690 where auction_header_id = p_currAuctionHeaderId and
691 nvl(modified_flag, 'N') <> 'P' and
692 nvl(modified_flag, 'N') <> 'S' and
693 exists (select null
694 from pon_bidding_parties prevInvitees
695 where prevInvitees.auction_header_id = p_prevAuctionHeaderId and
696 (prevInvitees.trading_partner_id = currInvitees.trading_partner_id
697 or prevInvitees.requested_supplier_id = currInvitees.requested_supplier_id) and
698 nvl(prevInvitees.vendor_site_id, -9999) = nvl(currInvitees.vendor_site_id, -9999) and
699 (nvl(prevInvitees.trading_partner_contact_id, -9999) <> nvl(currInvitees.trading_partner_contact_id, -9999) or
700 nvl(prevInvitees.requested_supplier_contact_id, -999) <> nvl(currInvitees.requested_supplier_contact_id, -999) or
701 nvl(prevInvitees.additional_contact_email, 'null') <> nvl(currInvitees.additional_contact_email, 'null') or
702 nvl(prevInvitees.bid_currency_code, 'null') <> nvl(currInvitees.bid_currency_code, 'null') or
703 nvl(prevInvitees.rate_dsp, -9999) <> nvl(currInvitees.rate_dsp, -9999) or
704 nvl(prevInvitees.number_price_decimals, -9999) <> nvl(currInvitees.number_price_decimals, -9999)));
705
706
707 END UPDATE_INVITEES_MODIFIED;
708
709 -- Updates the modified flag and last_amendment_update columns by comparing
710 -- the values of the user-enterable fields of the new negotiation
711 -- to the previous negotiation
712
713 PROCEDURE UPDATE_HDR_ATTR_MODIFIED (p_currAuctionHeaderId IN NUMBER,
714 p_prevAuctionHeaderId IN NUMBER,
715 p_action IN VARCHAR2) IS
716
717 v_currAmendmentNumber NUMBER;
718
719 BEGIN
720
721 select nvl(amendment_number, 0)
722 into v_currAmendmentNumber
723 from pon_auction_headers_all
724 where auction_header_id = p_currAuctionHeaderId;
725
726
727 -- first, reset the modified flag and last amendment update columns
728
729 if (p_action = 'MULTIROUND') then
730
731 update pon_auction_attributes auctionAttr
732 set MODIFIED_FLAG = null,
733 MODIFIED_DATE = (select modified_date
734 from pon_auction_attributes
735 where auction_header_id = p_prevAuctionHeaderId and
736 sequence_number = auctionAttr.sequence_number and
737 line_number = -1),
738 LAST_AMENDMENT_UPDATE = 0
739 where auction_header_id = p_currAuctionHeaderId
740 and line_number = -1;
741
742 -- since above query will set modified_date to null for new rows
743 -- need to set modified_date to sysdate for those rows
744
745 update pon_auction_attributes auctionAttr
746 set MODIFIED_DATE = sysdate
747 where auction_header_id = p_currAuctionHeaderId and
748 line_number = -1 and
749 modified_date is null;
750
751 else
752
753 update pon_auction_attributes auctionAttr
754 set MODIFIED_FLAG = null,
755 MODIFIED_DATE = (select modified_date
756 from pon_auction_attributes
757 where auction_header_id = p_prevAuctionHeaderId and
758 sequence_number = auctionAttr.sequence_number and
759 line_number = -1),
760 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
761 from pon_auction_attributes
762 where auction_header_id = p_prevAuctionHeaderId and
763 sequence_number = auctionAttr.sequence_number and
767
764 line_number = -1)
765 where auction_header_id = p_currAuctionHeaderId
766 and line_number = -1;
768 -- since above query will set modified_date and last_amendment_update to null for new rows
769 -- need to set modified_date and last_amendment_update to sysdate and current amendment number respectively for those rows
770
771 update pon_auction_attributes auctionAttr
772 set MODIFIED_DATE = sysdate,
773 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
774 where auction_header_id = p_currAuctionHeaderId and
775 line_number = -1 and
776 last_amendment_update is null;
777
778 end if;
779
780 -- next, do pairwise comparisons to find updated rows
781
782 update pon_auction_attributes currAttr
783 set MODIFIED_FLAG = 'Y',
784 MODIFIED_DATE = sysdate,
785 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
786 where auction_header_id = p_currAuctionHeaderId and
787 line_number = -1 and
788 exists (select null
789 from pon_auction_attributes prevAttr
790 where prevAttr.auction_header_id = p_prevAuctionHeaderId and
791 prevAttr.line_number = -1 and
792 prevAttr.sequence_number = currAttr.sequence_number and
793 (nvl(currAttr.attribute_name, 'null') <> nvl(prevAttr.attribute_name, 'null') OR
794 nvl(currAttr.description, 'null') <> nvl(prevAttr.description, 'null') OR
795 nvl(currAttr.datatype, 'null') <> nvl(prevAttr.datatype, 'null') OR
796 nvl(currAttr.mandatory_flag, 'null') <> nvl(prevAttr.mandatory_flag, 'null') OR
797 nvl(currAttr.value, 'null') <> nvl(prevAttr.value, 'null') OR
798 nvl(currAttr.display_prompt, 'null') <> nvl(prevAttr.display_prompt, 'null') OR
799 nvl(currAttr.help_text, 'null') <> nvl(prevAttr.help_text, 'null') OR
800 nvl(currAttr.display_target_flag, 'null') <> nvl(prevAttr.display_target_flag, 'null') OR
801 nvl(currAttr.attribute_list_id, -99) <> nvl(prevAttr.attribute_list_id, -99) OR
802 nvl(currAttr.display_only_flag, 'null') <> nvl(prevAttr.display_only_flag, 'null') OR
803 nvl(currAttr.copied_from_cat_flag, 'null') <> nvl(prevAttr.copied_from_cat_flag, 'null') OR
804 nvl(currAttr.weight, -99) <> nvl(prevAttr.weight, -99) OR
805 nvl(currAttr.scoring_type, 'null') <> nvl(prevAttr.scoring_type, 'null') OR nvl(currAttr.attr_level, 'null') <> nvl(prevAttr.attr_level, 'null') OR
806 nvl(currAttr.attr_group, 'null') <> nvl(prevAttr.attr_group, 'null') OR
807 nvl(currAttr.attr_max_score, -99) <> nvl(prevAttr.attr_max_score, -99) OR
808 nvl(currAttr.internal_attr_flag, 'null') <> nvl(prevAttr.internal_attr_flag, 'null')));
809
810 END UPDATE_HDR_ATTR_MODIFIED;
811
812 -- As a general rule...
813 -- Any action taken outside of the amendment process on
814 -- them most current negotiation will indirectly be taken on
815 -- the previous amended negotiations
816
817 -- The following function is to add suppliers to previous amended negotiations
818 -- if suppliers are added from the Invite Additional Suppliers page
819
820 PROCEDURE PROPAGATE_BACK_INSERT_INVITEE(p_currAuctionHeaderId IN NUMBER,
821 p_sequence IN NUMBER ) IS
822
823 v_auctionHeaderIdOrigAmend NUMBER;
824 v_currAmendmentNumber NUMBER;
825
826 BEGIN
827
828 select auction_header_id_orig_amend, nvl(amendment_number, 0)
829 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
830 from pon_auction_headers_all
831 where auction_header_id = p_currAuctionHeaderId;
832
833 insert into pon_bidding_parties
834 (auction_header_id,
835 list_id,
836 last_update_date,
837 last_updated_by,
838 sequence,
839 trading_partner_name,
840 trading_partner_id,
841 trading_partner_contact_name,
842 trading_partner_contact_id,
843 wf_user_name,
844 creation_date,
845 created_by,
846 bid_currency_code,
847 number_price_decimals,
848 rate,
849 derive_type,
850 additional_contact_email,
851 round_number,
852 registration_id,
853 rate_dsp,
854 wf_item_key,
855 last_amendment_update,
856 vendor_site_id,
857 vendor_site_code,
858 modified_flag,
859 access_type)
860
861 select pah.auction_header_id,
862 pbp.list_id,
863 pbp.last_update_date,
864 pbp.last_updated_by,
865 pbp.sequence,
866 pbp.trading_partner_name,
867 pbp.trading_partner_id,
868 pbp.trading_partner_contact_name,
869 pbp.trading_partner_contact_id,
870 pbp.wf_user_name,
871 pbp.creation_date,
872 pbp.created_by,
873 pbp.bid_currency_code,
874 pbp.number_price_decimals,
875 pbp.rate,
876 pbp.derive_type,
877 pbp.additional_contact_email,
878 pbp.round_number,
879 pbp.registration_id,
880 pbp.rate_dsp,
881 pbp.wf_item_key,
882 pbp.last_amendment_update,
883 pbp.vendor_site_id,
887 from pon_auction_headers_all pah,
884 pbp.vendor_site_code,
885 pbp.modified_flag,
886 pbp.access_type
888 pon_bidding_parties pbp
889 where pah.auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
890 pah.amendment_number < v_currAmendmentNumber and
891 pbp.auction_header_id = p_currAuctionHeaderId and
892 pbp.sequence = p_sequence ;
893
894 END PROPAGATE_BACK_INSERT_INVITEE;
895
896 -- As a general rule...
897 -- Any action taken outside of the amendment process on
898 -- them most current negotiation will indirectly be taken on
899 -- the previous amended negotiations
900
901 -- The following function is to update supplier acknowledgments in
902 -- previous amended negotiations
903
904 PROCEDURE PROPAGATE_BACK_UPDATE_INVITEE(p_currAuctionHeaderId IN NUMBER,
905 p_sequence IN NUMBER) IS
906
907 v_auctionHeaderIdOrigAmend NUMBER;
908 v_currAmendmentNumber NUMBER;
909
910 BEGIN
911
912 select auction_header_id_orig_amend, nvl(amendment_number, 0)
913 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
914 from pon_auction_headers_all
915 where auction_header_id = p_currAuctionHeaderId;
916
917 update pon_bidding_parties pbp
918 set (ack_partner_contact_id, supp_acknowledgement, ack_note_to_auctioneer, acknowledgement_time) =
919
920 (select currPbp.ack_partner_contact_id, currPbp.supp_acknowledgement, currPbp.ack_note_to_auctioneer, currPbp.acknowledgement_time
921 from pon_bidding_parties currPbp
922 where currPbp.auction_header_id = p_currAuctionHeaderId and
923 currPbp.sequence = p_sequence)
924
925 where pbp.auction_header_id in (select auction_header_id from pon_auction_headers_all where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and amendment_number <> v_currAmendmentNumber) and
926 pbp.sequence = p_sequence ;
927
928 END PROPAGATE_BACK_UPDATE_INVITEE;
929
930 -- As a general rule...
931 -- Any action taken outside of the amendment process on
932 -- them most current negotiation will indirectly be taken on
933 -- the previous amended negotiations
934
935 -- The following function is to add collaboration team members to
936 -- previous amended negotiations if members are added from the
937 -- Manage Collaboration Team page
938
939 PROCEDURE PROPAGATE_BACK_INSERT_MEMBER(p_currAuctionHeaderId IN NUMBER,
940 p_userId IN NUMBER) IS
941
942 v_auctionHeaderIdOrigAmend NUMBER;
943 v_currAmendmentNumber NUMBER;
944
945 BEGIN
946
947 select auction_header_id_orig_amend, nvl(amendment_number, 0)
948 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
949 from pon_auction_headers_all
950 where auction_header_id = p_currAuctionHeaderId;
951
952 insert into pon_neg_team_members
953 (auction_header_id,
954 list_id,
955 user_id,
956 menu_name,
957 member_type,
958 approver_flag,
959 approval_status,
960 task_name,
961 target_date,
962 completion_date,
963 creation_date,
964 created_by,
965 last_update_date,
966 last_updated_by,
967 last_amendment_update,
968 modified_flag)
969
970 select pah.auction_header_id,
971 pntm.list_id,
972 pntm.user_id,
973 pntm.menu_name,
974 pntm.member_type,
975 pntm.approver_flag,
976 pntm.approval_status,
977 pntm.task_name,
978 pntm.target_date,
979 pntm.completion_date,
980 pntm.creation_date,
981 pntm.created_by,
982 pntm.last_update_date,
983 pntm.last_updated_by,
984 pntm.last_amendment_update,
985 pntm.modified_flag
986 from pon_auction_headers_all pah,
987 pon_neg_team_members pntm
988 where pah.auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
989 pah.amendment_number < v_currAmendmentNumber and
990 pntm.auction_header_id = p_currAuctionHeaderId and
991 pntm.user_id = p_userId;
992
993 END PROPAGATE_BACK_INSERT_MEMBER;
994
995 PROCEDURE PROPAGATE_BACK_UNLOCK(p_currAuctionHeaderId IN NUMBER,
996 p_userId IN NUMBER,
997 p_unlock_date IN DATE,
998 p_unlock_type IN VARCHAR2) IS
999
1000 v_auctionHeaderIdOrigAmend NUMBER;
1001 v_currAmendmentNumber NUMBER;
1002 l_module_name VARCHAR2 (30);
1003
1004 BEGIN
1005
1006 l_module_name := 'PROPAGATE_BACK_UNLOCK';
1007
1008 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1009 FND_LOG.string (log_level => FND_LOG.level_procedure,
1010 module => g_module_prefix || l_module_name,
1011 message => 'Entered procedure = ' || l_module_name || '.' ||
1012 ' Parameters: p_currAuctionHeaderId = ' || p_currAuctionHeaderId || ', ' ||
1013 ' p_userId = ' || p_userId || ', ' || ' p_unlock_date = ' || p_unlock_date || ', ' ||
1014 ' p_unlock_type = ' || p_unlock_type);
1015 END IF;
1016
1017 select auction_header_id_orig_amend, nvl(amendment_number, 0)
1021
1018 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
1019 from pon_auction_headers_all
1020 where auction_header_id = p_currAuctionHeaderId;
1022 IF p_unlock_type = 'Technical' THEN
1023 update pon_auction_headers_all
1024 set technical_lock_status = 'UNLOCKED',
1025 technical_unlock_tp_contact_id = p_userId,
1026 technical_actual_unlock_date = p_unlock_date
1027 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1028 amendment_number < v_currAmendmentNumber;
1029 ELSE
1030 update pon_auction_headers_all
1031 set sealed_auction_status = 'UNLOCKED',
1032 sealed_unlock_tp_contact_id = p_userId,
1033 sealed_actual_unlock_date = p_unlock_date
1034 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1035 amendment_number < v_currAmendmentNumber;
1036 END IF;
1037
1038 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1039 FND_LOG.string (log_level => FND_LOG.level_procedure,
1040 module => g_module_prefix || l_module_name,
1041 message => 'Leaving procedure = ' || l_module_name);
1042 END IF;
1043
1044 END PROPAGATE_BACK_UNLOCK;
1045
1046 PROCEDURE PROPAGATE_BACK_UNSEAL(p_currAuctionHeaderId IN NUMBER,
1047 p_userId IN NUMBER,
1048 p_unseal_date IN DATE,
1049 p_unseal_type IN VARCHAR2) IS
1050
1051 v_auctionHeaderIdOrigAmend NUMBER;
1052 v_currAmendmentNumber NUMBER;
1053 v_technicalLockStatus VARCHAR2(20);
1054 l_module_name VARCHAR2 (30);
1055
1056 BEGIN
1057
1058 l_module_name := 'PROPAGATE_BACK_UNSEAL';
1059
1060 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1061 FND_LOG.string (log_level => FND_LOG.level_procedure,
1062 module => g_module_prefix || l_module_name,
1063 message => 'Entered procedure = ' || l_module_name || '.' ||
1064 ' Parameters: p_currAuctionHeaderId = ' || p_currAuctionHeaderId || ', ' ||
1065 ' p_userId = ' || p_userId || ', ' || ' p_unseal_date = ' || p_unseal_date || ', ' ||
1066 ' p_unseal_type = ' || p_unseal_type);
1067 END IF;
1068
1069 select auction_header_id_orig_amend, nvl(amendment_number, 0), technical_lock_status
1070 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber, v_technicalLockStatus
1071 from pon_auction_headers_all
1072 where auction_header_id = p_currAuctionHeaderId;
1073
1074 IF p_unseal_type = 'Technical' THEN
1075 update pon_auction_headers_all
1076 set technical_lock_status = 'ACTIVE',
1077 technical_unseal_tp_contact_id = p_userId,
1078 technical_actual_unseal_date = p_unseal_date
1079 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1080 amendment_number < v_currAmendmentNumber;
1081
1082 ELSIF p_unseal_type = 'Commercial' THEN
1083 IF v_technicalLockStatus = 'ACTIVE' THEN
1084 update pon_auction_headers_all
1085 set sealed_auction_status = 'ACTIVE',
1086 sealed_unseal_tp_contact_id = p_userId,
1087 sealed_actual_unseal_date = p_unseal_date
1088 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1089 amendment_number < v_currAmendmentNumber;
1090 ELSE
1091 update pon_auction_headers_all
1092 set sealed_auction_status = 'ACTIVE',
1093 sealed_unseal_tp_contact_id = p_userId,
1094 sealed_actual_unseal_date = p_unseal_date,
1095 technical_lock_status = 'ACTIVE',
1096 technical_unseal_tp_contact_id = p_userId,
1097 technical_actual_unseal_date = p_unseal_date
1098 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1099 amendment_number < v_currAmendmentNumber;
1100 END IF;
1101 ELSE
1102 update pon_auction_headers_all
1103 set sealed_auction_status = 'ACTIVE',
1104 sealed_unseal_tp_contact_id = p_userId,
1105 sealed_actual_unseal_date = p_unseal_date
1106 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1107 amendment_number < v_currAmendmentNumber;
1108 END IF;
1109
1110 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1111 FND_LOG.string (log_level => FND_LOG.level_procedure,
1112 module => g_module_prefix || l_module_name,
1113 message => 'Leaving procedure = ' || l_module_name);
1114 END IF;
1115
1116 END PROPAGATE_BACK_UNSEAL;
1117
1118 PROCEDURE PROCESS_PRICE_FACTORS(p_auction_header_id IN NUMBER,
1119 p_user_id IN NUMBER,
1120 p_login_id IN NUMBER) IS
1121
1122 l_auction_has_price_elements VARCHAR2(2);
1123 BEGIN
1124
1125 SELECT HAS_PRICE_ELEMENTS
1126 INTO l_auction_has_price_elements
1127 FROM PON_AUCTION_HEADERS_ALL
1128 WHERE AUCTION_HEADER_ID = p_auction_header_id;
1129
1130 IF ('Y' = l_auction_has_price_elements) THEN
1131
1132 insert into pon_price_elements
1133 (auction_header_id,
1134 line_number,
1135 list_id,
1136 price_element_type_id,
1137 pricing_basis,
1138 value,
1139 display_target_flag,
1140 sequence_number,
1141 creation_date,
1145 pf_type,
1142 created_by,
1143 last_update_date,
1144 last_updated_by,
1146 display_to_suppliers_flag)
1147
1148 select auction_header_id,
1149 line_number,
1150 -1,
1151 -10,
1152 decode(order_type_lookup_code, 'FIXED PRICE', 'FIXED_AMOUNT', 'PER_UNIT'),
1153 unit_target_price,
1154 unit_display_target_flag,
1155 -10,
1156 sysdate,
1157 p_user_id,
1158 sysdate,
1159 p_user_id,
1160 'SUPPLIER',
1161 'Y'
1162 from pon_auction_item_prices_all
1163 where auction_header_id = p_auction_header_id and
1164 (has_price_elements_flag = 'Y' or has_buyer_pfs_flag = 'Y');
1165
1166 insert into pon_pf_supplier_formula
1167 (auction_header_id,
1168 line_number,
1169 trading_partner_id,
1170 vendor_site_id,
1171 unit_price,
1172 fixed_amount,
1173 percentage,
1174 creation_date,
1175 created_by,
1176 last_update_date,
1177 last_updated_by,
1178 last_update_login)
1179
1180 select paip.auction_header_id,
1181 paip.line_number,
1182 pbp.trading_partner_id,
1183 pbp.vendor_site_id,
1184 sum(decode(ppe.pricing_basis, 'PER_UNIT', ppsv.value, 0)) unit_price,
1185 sum(decode(ppe.pricing_basis, 'FIXED_AMOUNT', ppsv.value, 0)) fixed_amount,
1186 1 + sum(decode(ppe.pricing_basis, 'PERCENTAGE', ppsv.value/100, 0)) percentage,
1187 sysdate,
1188 p_user_id,
1189 sysdate,
1190 p_user_id,
1191 p_login_id
1192 from pon_auction_item_prices_all paip,
1193 pon_bidding_parties pbp,
1194 pon_pf_supplier_values ppsv,
1195 pon_price_elements ppe
1196 where paip.auction_header_id = p_auction_header_id and
1197 pbp.auction_header_id = paip.auction_header_id and
1198 pbp.auction_header_id = ppsv.auction_header_id and
1199 pbp.sequence = ppsv.supplier_seq_number and
1200 paip.line_number = ppsv.line_number and
1201 ppsv.auction_header_id = ppe.auction_header_id and
1202 ppsv.line_number = ppe.line_number and
1203 ppsv.pf_seq_number = ppe.sequence_number
1204 group by paip.auction_header_id, paip.line_number, pbp.trading_partner_id, pbp.vendor_site_id;
1205
1206 END IF;
1207
1208 END PROCESS_PRICE_FACTORS;
1209
1210 PROCEDURE MANUAL_CLOSE_LINE (
1211 x_result OUT NOCOPY VARCHAR2,
1212 x_error_code OUT NOCOPY VARCHAR2,
1213 x_error_message OUT NOCOPY VARCHAR2,
1214 p_auction_header_id IN NUMBER,
1215 p_line_number IN NUMBER,
1216 p_user_id IN NUMBER,
1217 x_is_auction_closed OUT NOCOPY VARCHAR2
1218 ) IS
1219
1220 l_module_name VARCHAR2 (30);
1221 x_temp PON_AUCTION_HEADERS_ALL.LAST_UPDATE_DATE%TYPE;
1222 x_close_bidding_date PON_AUCTION_HEADERS_ALL.CLOSE_BIDDING_DATE%TYPE;
1223 v_auction_last_line_number PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
1224 p_new_close_date PON_AUCTION_HEADERS_ALL.CLOSE_BIDDING_DATE%TYPE;
1225 BEGIN
1226
1227 l_module_name := 'MANUAL_CLOSE_LINE';
1228 x_result := FND_API.g_ret_sts_success;
1229
1230 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1231 FND_LOG.string (log_level => FND_LOG.level_procedure,
1232 module => g_module_prefix || l_module_name,
1233 message => 'Entered procedure = ' || l_module_name || '.' ||
1234 ' Parameters: p_auction_header_id = ' || p_auction_header_id || ', ' ||
1235 ' p_user_id = ' || p_user_id);
1236 END IF;
1237
1238 -- lock negotiation header
1239 SELECT LAST_UPDATE_DATE, CLOSE_BIDDING_DATE
1240 INTO x_temp, x_close_bidding_date
1241 FROM PON_AUCTION_HEADERS_ALL
1242 WHERE AUCTION_HEADER_ID = p_auction_header_id
1243 FOR UPDATE;
1244
1245 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1246 FND_LOG.string (log_level => FND_LOG.level_statement,
1247 module => g_module_prefix || l_module_name,
1248 message => 'Locked the negotiation header');
1249 END IF;
1250
1251 -- update header date
1252 update pon_auction_headers_all
1253 set last_update_date = sysdate,
1254 last_updated_by = p_user_id
1255 where auction_header_id = p_auction_header_id;
1256
1257 p_new_close_date := sysdate;
1258
1259 -- update item close date
1260 update pon_auction_item_prices_all
1261 set close_bidding_date = p_new_close_date,
1262 last_update_date = sysdate,
1263 last_updated_by = p_user_id
1264 where auction_header_id = p_auction_header_id
1265 and (line_number = p_line_number
1266 or parent_line_number = p_line_number);
1267
1268 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1269 FND_LOG.string (log_level => FND_LOG.level_statement,
1270 module => g_module_prefix || l_module_name,
1271 message => 'Update header and the line.');
1272 END IF;
1273
1274 --In case this is the last line then the auction has to be closed.
1275 select line_number into v_auction_last_line_number
1276 from pon_auction_item_prices_all
1277 where auction_header_id = p_auction_header_id
1281 where auction_header_id=p_auction_header_id
1278 and disp_line_number =
1279 (select max(disp_line_number)
1280 from pon_auction_item_prices_all
1282 and group_type in ('LINE', 'LOT', 'GROUP'));
1283
1284 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1285 FND_LOG.string (log_level => FND_LOG.level_statement,
1286 module => g_module_prefix || l_module_name,
1287 message => 'The last line in the auction is = ' || v_auction_last_line_number);
1288 END IF;
1289
1290 if (p_line_number = v_auction_last_line_number) then
1291
1292 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1293 FND_LOG.string (log_level => FND_LOG.level_statement,
1294 module => g_module_prefix || l_module_name,
1295 message => 'The last line number in the auction matches with p_line_number, closing the negotiation');
1296 END IF;
1297
1298 update pon_auction_headers_all set close_bidding_date = p_new_close_date
1299 where auction_header_id = p_auction_header_id;
1300 x_is_auction_closed := 'Y';
1301
1302 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1303 FND_LOG.string (log_level => FND_LOG.level_statement,
1304 module => g_module_prefix || l_module_name,
1305 message => 'Updated header sending notification.');
1306 END IF;
1307
1308 pon_auction_pkg.CLOSEEARLY_AUCTION(p_auction_header_id,
1309 p_new_close_date,
1310 null);
1311 end if;
1312
1313 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1314 FND_LOG.string (log_level => FND_LOG.level_procedure,
1315 module => g_module_prefix || l_module_name,
1316 message => 'Leaving procedure = ' || l_module_name);
1317 END IF;
1318
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 x_result := FND_API.g_ret_sts_unexp_error;
1322 x_error_code := SQLCODE;
1323 x_error_message := SUBSTR(SQLERRM, 1, 100);
1324
1325 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1326 FND_LOG.string (log_level => FND_LOG.level_exception,
1327 module => g_module_prefix || l_module_name,
1328 message => 'Unexpected exception occured error_code = ' ||
1329 x_error_code || ', error_message = ' || x_error_message);
1330 END IF;
1331
1332 END MANUAL_CLOSE_LINE;
1333
1334 PROCEDURE PROPAGATE_BACK_TECH_EVAL(p_currAuctionHeaderId IN NUMBER,
1335 p_tech_eval_status IN VARCHAR2) IS
1336
1337 v_auctionHeaderIdOrigAmend NUMBER;
1338 v_currAmendmentNumber NUMBER;
1339 l_module_name VARCHAR2 (30);
1340
1341 BEGIN
1342
1343 l_module_name := 'PROPAGATE_BACK_TECH_EVAL';
1344
1345 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1346 FND_LOG.string (log_level => FND_LOG.level_procedure,
1347 module => g_module_prefix || l_module_name,
1348 message => 'Entered procedure = ' || l_module_name || '.' ||
1349 ' Parameters: p_currAuctionHeaderId = ' || p_currAuctionHeaderId || ', ' ||
1350 ' p_tech_eval_status = ' || p_tech_eval_status);
1351 END IF;
1352
1353 select auction_header_id_orig_amend, nvl(amendment_number, 0)
1354 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
1355 from pon_auction_headers_all
1356 where auction_header_id = p_currAuctionHeaderId;
1357
1358 update pon_auction_headers_all
1359 set technical_evaluation_status = p_tech_eval_status
1360 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1361 amendment_number < v_currAmendmentNumber;
1362
1363 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1364 FND_LOG.string (log_level => FND_LOG.level_procedure,
1365 module => g_module_prefix || l_module_name,
1366 message => 'Leaving procedure = ' || l_module_name);
1367 END IF;
1368
1369 END PROPAGATE_BACK_TECH_EVAL;
1370
1371 END PON_NEG_UPDATE_PKG;