1 PACKAGE BODY PON_NEG_UPDATE_PKG as
2 /* $Header: PONUPDTB.pls 120.16.12020000.2 2013/02/09 11:08:23 hvutukur 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 v_amend_last_update_date pon_auction_headers_all.last_update_date%type;
385 v_changedesc_gen_date pon_auction_headers_all.cd_generated_date%type;
386
387 l_back_req_exists VARCHAR2(1) := 'N';
388 l_return_status varchar2(1);
389
390 BEGIN
391
392 x_error_code := 'SUCCESS';
393 x_error_msg := '';
394
395 -- lock negotiation
396 SELECT LAST_UPDATE_DATE, document_number
397 INTO v_temp, v_doc_number_prev_doc
398 FROM PON_AUCTION_HEADERS_ALL
399 WHERE AUCTION_HEADER_ID = p_auction_header_id_prev_doc
400 FOR UPDATE;
401
402 --Line Type and Structure Changes project
403 --If Solicitation has lines with linked PR's then copy these ref also
404 IF (PON_CLM_UTIL_PKG.get_line_structure_enabled(p_auction_header_id => p_auction_header_id_prev_doc) = 'Y') THEN
405 BEGIN
406 SELECT 'Y' INTO l_back_req_exists
407 FROM pon_backing_requisitions
408 WHERE auction_header_id = p_auction_header_id_prev_doc
409 AND ROWNUM = 1;
410 EXCEPTION
411 WHEN No_Data_Found THEN
412 l_back_req_exists := 'N';
413 END;
414 END IF;
415
416 -- when saving the document for the first time...
417 -- change the status of the previous round document
418 -- copy negotiation references in our tables (pon_backing_requisitions)
419
420
421 if (p_is_new = 'Y') then
422
423 -- Bug 9868103 - Solicitation Amendment SGD
424 -- set the value 'Y', if the solicitation amendment document is updated
425 -- after the change description generated. Set value 'N', if the amendment
426 -- document is not changed after change description generated.
427 -- Fix starts
428 IF (p_transaction_type = 'CREATE_CONFORMED_DOC') THEN
429
430 SELECT CD_GENERATED_DATE, LAST_UPDATE_DATE
431 INTO v_changedesc_gen_date,v_amend_last_update_date
432 FROM pon_auction_headers_all
433 WHERE auction_header_id = p_auction_header_id_prev_doc;
434
435 IF v_changedesc_gen_date IS NOT NULL AND v_amend_last_update_date IS NOT NULL THEN
436 IF( v_amend_last_update_date > v_changedesc_gen_date) then
437 update pon_auction_headers_all
438 set AMENDUPDATED_AFTERCDGENERATED = 'Y'
439 where auction_header_id = p_auction_header_id_prev_doc;
440 ELSE
441 update pon_auction_headers_all
442 set AMENDUPDATED_AFTERCDGENERATED = 'N'
443 where auction_header_id = p_auction_header_id_prev_doc;
444 END IF;
445 END IF;
446 END IF;
447 -- Fix ends
448
449
450 -- update status of previous round document
451
452 if (p_transaction_type = 'CREATE_NEW_ROUND') then
453
454 update pon_auction_headers_all
455 set AWARD_STATUS = 'NO',
456 AUCTION_STATUS = 'AUCTION_CLOSED',
457 LAST_UPDATE_DATE = sysdate,
458 LAST_UPDATED_BY = p_user_id
459 where auction_header_id = p_auction_header_id_prev_doc;
460 ELSIF ( p_transaction_type = 'CREATE_CONFORMED_DOC' ) then
461
462 update pon_auction_headers_all
463 set AWARD_STATUS = 'NO',
464 AUCTION_STATUS = 'APPLIED',
465 LAST_UPDATE_DATE = sysdate,
466 LAST_UPDATED_BY = p_user_id
467 where auction_header_id = p_auction_header_id_prev_doc;
468
469
470 end if;
471
472 -- if an autocreated document is being taken to a new round or amended,
473 -- copy negotiation references locally
474
475 if (p_auction_origination_code = 'REQUISITION' OR l_back_req_exists = 'Y') then
476
477 PON_AUCTION_PKG.COPY_BACKING_REQ(p_auction_header_id_prev_doc, p_auction_header_id_curr_doc, x_error_code);
478
479 end if;
480 end if;
481
482 -- when publishing the document....
483 -- call PO api to change negotiation references in their tables
484 -- if we're amended a document
485 -- 1) change the auction status and award status of the previous amendment to be AMENDED and NO respectively
486 -- 2) update previous 'ACTIVE' bids to be 'RESUBMISSION_REQUIRED'
487
488 if (p_is_publish = 'Y' and x_error_code = 'SUCCESS') then
489
490 if (p_auction_origination_code = 'REQUISITION' OR l_back_req_exists = 'Y') then
491
492 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id_curr_doc) = 1 ) THEN
493
494 po_negotiations_sv1.update_sol_ref_amend(p_old_auction_id => p_auction_header_id_prev_doc,
495 p_new_auction_id => p_auction_header_id_curr_doc,
496 x_return_status => l_return_status,
497 x_error_msg => x_error_msg,
498 x_error_code => x_error_code);
499 ELSE
500 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);
501 END IF;
502 end if;
503
504 -- previous active bids are no longer valid
505 -- suppliers will need to acknowledge amendments before their bid
506 -- becomes active again
507
508
509 -- update pause details also.
510 if (p_transaction_type = 'CREATE_AMENDMENT') then
511
512 update pon_auction_headers_all
513 set AWARD_STATUS = 'NO',
514 AUCTION_STATUS = 'AMENDED',
515 is_paused = null,
516 pause_remarks = null,
517 last_pause_date = null,
518 LAST_UPDATE_DATE = sysdate,
519 LAST_UPDATED_BY = p_user_id
520 where auction_header_id = p_auction_header_id_prev_doc;
521
522 update pon_bid_headers
523 set BID_STATUS = 'RESUBMISSION'
524 where auction_header_id = p_auction_header_id_prev_doc and
525 bid_status = 'ACTIVE';
526
527 end if;
528
529
530 end if;
531
532
533 END UPDATE_TO_NEW_DOCUMENT;
534
535
536 -- Updates the modified flag and last_amendment_update columns by comparing
537 -- the values of the user-enterable fields of the new negotiation
538 -- to the previous negotiation
539
540 PROCEDURE UPDATE_MODIFIED_FIELDS (p_currAuctionHeaderId IN NUMBER,
541 p_prevAuctionHeaderId IN NUMBER,
542 p_action IN VARCHAR2) IS
543
544 BEGIN
545
546 UPDATE_NEG_TEAM_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
547
548 UPDATE_CURRENCY_RATES_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
549
550 UPDATE_INVITEES_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
551
552 UPDATE_HDR_ATTR_MODIFIED(p_currAuctionHeaderId, p_prevAuctionHeaderId, p_action);
553
554 END UPDATE_MODIFIED_FIELDS;
555
556
557 -- Updates the modified flag and last_amendment_update columns by comparing
558 -- the values of the user-enterable fields of the new negotiation
559 -- to the previous negotiation
560
561 PROCEDURE UPDATE_CURRENCY_RATES_MODIFIED (p_currAuctionHeaderId IN NUMBER,
562 p_prevAuctionHeaderId IN NUMBER,
563 p_action IN VARCHAR2) IS
564
565 v_currAmendmentNumber NUMBER;
566
567 BEGIN
568
569 select nvl(amendment_number, 0)
570 into v_currAmendmentNumber
571 from pon_auction_headers_all
572 where auction_header_id = p_currAuctionHeaderId;
573
574
575 -- first, reset the modified flag and last amendment update columns
576
577 if (p_action = 'MULTIROUND') then
578
579 update pon_auction_currency_rates
580 set MODIFIED_FLAG = null,
581 LAST_AMENDMENT_UPDATE = 0
582 where auction_header_id = p_currAuctionHeaderId;
583
584 else
585
586 update pon_auction_currency_rates rates
587 set MODIFIED_FLAG = null,
588 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
589 from pon_auction_currency_rates
590 where auction_header_id = p_prevAuctionHeaderId and
591 bid_currency_code = rates.bid_currency_code)
592 where auction_header_id = p_currAuctionHeaderId;
593
594 -- since above query will set last_amendment_update to null for new rows
595 -- need to set last_amendment_update to current amendment number for those rows
596
597 update pon_auction_currency_rates rates
598 set LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
599 where auction_header_id = p_currAuctionHeaderId and
600 last_amendment_update is null;
601
602 end if;
603
604 -- next, do pairwise comparisons to find updated rows
605
606 update pon_auction_currency_rates currRates
607 set MODIFIED_FLAG = 'Y',
608 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
609 where auction_header_id = p_currAuctionHeaderId and
610 exists (select null
611 from pon_auction_currency_rates prevRates
612 where prevRates.auction_header_id = p_prevAuctionHeaderId and
613 prevRates.bid_currency_code = currRates.bid_currency_code and
614 (nvl(prevRates.rate_dsp, -9999) <> nvl(currRates.rate_dsp, -9999) or
615 nvl(prevRates.number_price_decimals, -9999) <> nvl(currRates.number_price_decimals, -9999)));
616
617 END UPDATE_CURRENCY_RATES_MODIFIED;
618
619 -- Updates the modified flag and last_amendment_update columns by comparing
620 -- the values of the user-enterable fields of the new negotiation
621 -- to the previous negotiation
622
623 PROCEDURE UPDATE_NEG_TEAM_MODIFIED (p_currAuctionHeaderId IN NUMBER,
624 p_prevAuctionHeaderId IN NUMBER,
625 p_action IN VARCHAR2) IS
626
627 v_currAmendmentNumber NUMBER;
628
629 BEGIN
630
631 select nvl(amendment_number, 0)
632 into v_currAmendmentNumber
633 from pon_auction_headers_all
634 where auction_header_id = p_currAuctionHeaderId;
635
636
637 -- first, reset the modified flag and last amendment update columns
638
639 if (p_action = 'MULTIROUND') then
640
641 update pon_neg_team_members
642 set MODIFIED_FLAG = null,
643 LAST_AMENDMENT_UPDATE = 0
644 where auction_header_id = p_currAuctionHeaderId;
645
646 else
647
648 update pon_neg_team_members neg
649 set MODIFIED_FLAG = null,
650 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
651 from pon_neg_team_members
652 where auction_header_id = p_prevAuctionHeaderId and
653 user_id = neg.user_id)
654 where auction_header_id = p_currAuctionHeaderId;
655
656 -- since above query will set last_amendment_update to null for new rows
657 -- need to set last_amendment_update to current amendment number for those rows
658
659 update pon_neg_team_members neg
660 set LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
661 where auction_header_id = p_currAuctionHeaderId and
662 last_amendment_update is null;
663
664 end if;
665
666 -- next, do pairwise comparisons to find updated rows
667
668 update pon_neg_team_members currNeg
669 set MODIFIED_FLAG = 'Y',
670 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
671 where auction_header_id = p_currAuctionHeaderId and
672 exists (select null
673 from pon_neg_team_members prevNeg
674 where prevNeg.auction_header_id = p_prevAuctionHeaderId and
675 prevNeg.user_id = currNeg.user_id and
676
677 (nvl(prevNeg.approver_flag, 'N') <> nvl(currNeg.approver_flag, 'N') or
678 nvl(prevNeg.menu_name, 'PON_SOURCING_VIEWNEG') <> nvl(currNeg.menu_name, 'PON_SOURCING_VIEWNEG') or
679 nvl(prevNeg.task_name, 'null') <> nvl(currNeg.task_name, 'null') or
680 nvl(prevNeg.target_date, sysdate) <> nvl(currNeg.target_date, sysdate)));
681
682
683 END UPDATE_NEG_TEAM_MODIFIED;
684
685 -- Updates the modified flag and last_amendment_update columns by comparing
686 -- the values of the user-enterable fields of the new negotiation
687 -- to the previous negotiation
688
689 PROCEDURE UPDATE_INVITEES_MODIFIED (p_currAuctionHeaderId IN NUMBER,
690 p_prevAuctionHeaderId IN NUMBER,
691 p_action IN VARCHAR2) IS
692
693 v_currAmendmentNumber NUMBER;
694
695 BEGIN
696
697 select nvl(amendment_number, 0)
698 into v_currAmendmentNumber
699 from pon_auction_headers_all
700 where auction_header_id = p_currAuctionHeaderId;
701
702 -- first, reset the modified flag and last amendment update columns
703 -- only if modified flag is not "P" or "S" ...
704 -- the value "P" indicates that the buyer price factor values have been modified
705 -- and the value "S" indicates that the Supplier Access has been modified
706 -- so regardless if supplier info (contact, currency, etc...) has changed
707 -- modified flag will still have a value of "P" or "S"...
708 -- this will influence all sql below...
709
710 if (p_action = 'MULTIROUND') then
711
712 update pon_bidding_parties
713 set MODIFIED_FLAG = null,
714 LAST_AMENDMENT_UPDATE = 0
715 where auction_header_id = p_currAuctionHeaderId and
716 nvl(modified_flag, 'N') <> 'P' and
717 nvl(modified_flag, 'N') <> 'S';
718
719 else
720
721 update pon_bidding_parties invitees
722 set MODIFIED_FLAG = null,
723 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
724 from pon_bidding_parties
725 where auction_header_id = p_prevAuctionHeaderId and
726 (trading_partner_id = invitees.trading_partner_id
727 OR requested_supplier_id =invitees.requested_supplier_id) and
728 nvl(vendor_site_id, -9999) = nvl(invitees.vendor_site_id, -9999))
729 where auction_header_id = p_currAuctionHeaderId and
730 nvl(modified_flag, 'N') <> 'P' and
731 nvl(modified_flag, 'N') <> 'S';
732
733 -- since above query will set last_amendment_update to null for new rows
734 -- need to set last_amendment_update to current amendment number for those rows
735
736 update pon_bidding_parties invitees
737 set LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
738 where auction_header_id = p_currAuctionHeaderId and
739 last_amendment_update is null;
740
741 end if;
742
743 -- next, do pairwise comparisons to find updated rows
744
745 update pon_bidding_parties currInvitees
746 set MODIFIED_FLAG = 'Y',
747 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
748 where auction_header_id = p_currAuctionHeaderId and
749 nvl(modified_flag, 'N') <> 'P' and
750 nvl(modified_flag, 'N') <> 'S' and
751 exists (select null
752 from pon_bidding_parties prevInvitees
753 where prevInvitees.auction_header_id = p_prevAuctionHeaderId and
754 (prevInvitees.trading_partner_id = currInvitees.trading_partner_id
755 or prevInvitees.requested_supplier_id = currInvitees.requested_supplier_id) and
756 nvl(prevInvitees.vendor_site_id, -9999) = nvl(currInvitees.vendor_site_id, -9999) and
757 (nvl(prevInvitees.trading_partner_contact_id, -9999) <> nvl(currInvitees.trading_partner_contact_id, -9999) or
758 nvl(prevInvitees.requested_supplier_contact_id, -999) <> nvl(currInvitees.requested_supplier_contact_id, -999) or
759 nvl(prevInvitees.additional_contact_email, 'null') <> nvl(currInvitees.additional_contact_email, 'null') or
760 nvl(prevInvitees.bid_currency_code, 'null') <> nvl(currInvitees.bid_currency_code, 'null') or
761 nvl(prevInvitees.rate_dsp, -9999) <> nvl(currInvitees.rate_dsp, -9999) or
762 nvl(prevInvitees.number_price_decimals, -9999) <> nvl(currInvitees.number_price_decimals, -9999)));
763
764
765 END UPDATE_INVITEES_MODIFIED;
766
767 -- Updates the modified flag and last_amendment_update columns by comparing
768 -- the values of the user-enterable fields of the new negotiation
769 -- to the previous negotiation
770
771 PROCEDURE UPDATE_HDR_ATTR_MODIFIED (p_currAuctionHeaderId IN NUMBER,
772 p_prevAuctionHeaderId IN NUMBER,
773 p_action IN VARCHAR2) IS
774
775 v_currAmendmentNumber NUMBER;
776
777 BEGIN
778
779 select nvl(amendment_number, 0)
780 into v_currAmendmentNumber
781 from pon_auction_headers_all
782 where auction_header_id = p_currAuctionHeaderId;
783
784
785 -- first, reset the modified flag and last amendment update columns
786
787 if (p_action = 'MULTIROUND') then
788
789 update pon_auction_attributes auctionAttr
790 set MODIFIED_FLAG = null,
791 MODIFIED_DATE = (select modified_date
792 from pon_auction_attributes
793 where auction_header_id = p_prevAuctionHeaderId and
794 sequence_number = auctionAttr.sequence_number and
795 line_number = -1),
796 LAST_AMENDMENT_UPDATE = 0
797 where auction_header_id = p_currAuctionHeaderId
798 and line_number = -1;
799
800 -- since above query will set modified_date to null for new rows
801 -- need to set modified_date to sysdate for those rows
802
803 update pon_auction_attributes auctionAttr
804 set MODIFIED_DATE = sysdate
805 where auction_header_id = p_currAuctionHeaderId and
806 line_number = -1 and
807 modified_date is null;
808
809 else
810
811 update pon_auction_attributes auctionAttr
812 set MODIFIED_FLAG = null,
813 MODIFIED_DATE = (select modified_date
814 from pon_auction_attributes
815 where auction_header_id = p_prevAuctionHeaderId and
816 sequence_number = auctionAttr.sequence_number and
817 line_number = -1),
818 LAST_AMENDMENT_UPDATE = (select nvl(last_amendment_update, 0)
819 from pon_auction_attributes
820 where auction_header_id = p_prevAuctionHeaderId and
821 sequence_number = auctionAttr.sequence_number and
822 line_number = -1)
823 where auction_header_id = p_currAuctionHeaderId
824 and line_number = -1;
825
826 -- since above query will set modified_date and last_amendment_update to null for new rows
827 -- need to set modified_date and last_amendment_update to sysdate and current amendment number respectively for those rows
828
829 update pon_auction_attributes auctionAttr
830 set MODIFIED_DATE = sysdate,
831 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
832 where auction_header_id = p_currAuctionHeaderId and
833 line_number = -1 and
834 last_amendment_update is null;
835
836 end if;
837
838 -- next, do pairwise comparisons to find updated rows
839
840 update pon_auction_attributes currAttr
841 set MODIFIED_FLAG = 'Y',
842 MODIFIED_DATE = sysdate,
843 LAST_AMENDMENT_UPDATE = v_currAmendmentNumber
844 where auction_header_id = p_currAuctionHeaderId and
845 line_number = -1 and
846 exists (select null
847 from pon_auction_attributes prevAttr
848 where prevAttr.auction_header_id = p_prevAuctionHeaderId and
849 prevAttr.line_number = -1 and
850 prevAttr.sequence_number = currAttr.sequence_number and
851 (nvl(currAttr.attribute_name, 'null') <> nvl(prevAttr.attribute_name, 'null') OR
852 nvl(currAttr.description, 'null') <> nvl(prevAttr.description, 'null') OR
853 nvl(currAttr.datatype, 'null') <> nvl(prevAttr.datatype, 'null') OR
854 nvl(currAttr.mandatory_flag, 'null') <> nvl(prevAttr.mandatory_flag, 'null') OR
855 nvl(currAttr.value, 'null') <> nvl(prevAttr.value, 'null') OR
856 nvl(currAttr.display_prompt, 'null') <> nvl(prevAttr.display_prompt, 'null') OR
857 nvl(currAttr.help_text, 'null') <> nvl(prevAttr.help_text, 'null') OR
858 nvl(currAttr.display_target_flag, 'null') <> nvl(prevAttr.display_target_flag, 'null') OR
859 nvl(currAttr.attribute_list_id, -99) <> nvl(prevAttr.attribute_list_id, -99) OR
860 nvl(currAttr.display_only_flag, 'null') <> nvl(prevAttr.display_only_flag, 'null') OR
861 nvl(currAttr.copied_from_cat_flag, 'null') <> nvl(prevAttr.copied_from_cat_flag, 'null') OR
862 nvl(currAttr.weight, -99) <> nvl(prevAttr.weight, -99) OR
863 nvl(currAttr.scoring_type, 'null') <> nvl(prevAttr.scoring_type, 'null') OR nvl(currAttr.attr_level, 'null') <> nvl(prevAttr.attr_level, 'null') OR
864 nvl(currAttr.attr_group, 'null') <> nvl(prevAttr.attr_group, 'null') OR
865 nvl(currAttr.attr_max_score, -99) <> nvl(prevAttr.attr_max_score, -99) OR
866 nvl(currAttr.internal_attr_flag, 'null') <> nvl(prevAttr.internal_attr_flag, 'null')));
867
868 END UPDATE_HDR_ATTR_MODIFIED;
869
870 -- As a general rule...
871 -- Any action taken outside of the amendment process on
872 -- them most current negotiation will indirectly be taken on
873 -- the previous amended negotiations
874
875 -- The following function is to add suppliers to previous amended negotiations
876 -- if suppliers are added from the Invite Additional Suppliers page
877
878 PROCEDURE PROPAGATE_BACK_INSERT_INVITEE(p_currAuctionHeaderId IN NUMBER,
879 p_sequence IN NUMBER ) IS
880
881 v_auctionHeaderIdOrigAmend NUMBER;
882 v_currAmendmentNumber NUMBER;
883
884 BEGIN
885
886 select auction_header_id_orig_amend, nvl(amendment_number, 0)
887 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
888 from pon_auction_headers_all
889 where auction_header_id = p_currAuctionHeaderId;
890
891 insert into pon_bidding_parties
892 (auction_header_id,
893 list_id,
894 last_update_date,
895 last_updated_by,
896 sequence,
897 trading_partner_name,
898 trading_partner_id,
899 trading_partner_contact_name,
900 trading_partner_contact_id,
901 wf_user_name,
902 creation_date,
903 created_by,
904 bid_currency_code,
905 number_price_decimals,
906 rate,
907 derive_type,
908 additional_contact_email,
909 round_number,
910 registration_id,
911 rate_dsp,
912 wf_item_key,
913 last_amendment_update,
914 vendor_site_id,
915 vendor_site_code,
916 modified_flag,
917 access_type)
918
919 select pah.auction_header_id,
920 pbp.list_id,
921 pbp.last_update_date,
922 pbp.last_updated_by,
923 pbp.sequence,
924 pbp.trading_partner_name,
925 pbp.trading_partner_id,
926 pbp.trading_partner_contact_name,
927 pbp.trading_partner_contact_id,
928 pbp.wf_user_name,
929 pbp.creation_date,
930 pbp.created_by,
931 pbp.bid_currency_code,
932 pbp.number_price_decimals,
933 pbp.rate,
934 pbp.derive_type,
935 pbp.additional_contact_email,
936 pbp.round_number,
937 pbp.registration_id,
938 pbp.rate_dsp,
939 pbp.wf_item_key,
940 pbp.last_amendment_update,
941 pbp.vendor_site_id,
942 pbp.vendor_site_code,
943 pbp.modified_flag,
944 pbp.access_type
945 from pon_auction_headers_all pah,
946 pon_bidding_parties pbp
947 where pah.auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
948 pah.amendment_number < v_currAmendmentNumber and
949 pbp.auction_header_id = p_currAuctionHeaderId and
950 pbp.sequence = p_sequence ;
951
952 END PROPAGATE_BACK_INSERT_INVITEE;
953
954 -- As a general rule...
955 -- Any action taken outside of the amendment process on
956 -- them most current negotiation will indirectly be taken on
957 -- the previous amended negotiations
958
959 -- The following function is to update supplier acknowledgments in
960 -- previous amended negotiations
961
962 PROCEDURE PROPAGATE_BACK_UPDATE_INVITEE(p_currAuctionHeaderId IN NUMBER,
963 p_sequence IN NUMBER) IS
964
965 v_auctionHeaderIdOrigAmend NUMBER;
966 v_currAmendmentNumber NUMBER;
967
968 BEGIN
969
970 select auction_header_id_orig_amend, nvl(amendment_number, 0)
971 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
972 from pon_auction_headers_all
973 where auction_header_id = p_currAuctionHeaderId;
974
975 update pon_bidding_parties pbp
976 set (ack_partner_contact_id, supp_acknowledgement, ack_note_to_auctioneer, acknowledgement_time) =
977
978 (select currPbp.ack_partner_contact_id, currPbp.supp_acknowledgement, currPbp.ack_note_to_auctioneer, currPbp.acknowledgement_time
979 from pon_bidding_parties currPbp
980 where currPbp.auction_header_id = p_currAuctionHeaderId and
981 currPbp.sequence = p_sequence)
982
983 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
984 pbp.sequence = p_sequence ;
985
986 END PROPAGATE_BACK_UPDATE_INVITEE;
987
988 -- As a general rule...
989 -- Any action taken outside of the amendment process on
990 -- them most current negotiation will indirectly be taken on
991 -- the previous amended negotiations
992
993 -- The following function is to add collaboration team members to
994 -- previous amended negotiations if members are added from the
995 -- Manage Collaboration Team page
996
997 PROCEDURE PROPAGATE_BACK_INSERT_MEMBER(p_currAuctionHeaderId IN NUMBER,
998 p_userId IN NUMBER) IS
999
1000 v_auctionHeaderIdOrigAmend NUMBER;
1001 v_currAmendmentNumber NUMBER;
1002
1003 BEGIN
1004
1005 select auction_header_id_orig_amend, nvl(amendment_number, 0)
1006 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
1007 from pon_auction_headers_all
1008 where auction_header_id = p_currAuctionHeaderId;
1009
1010 insert into pon_neg_team_members
1011 (auction_header_id,
1012 list_id,
1013 user_id,
1014 menu_name,
1015 member_type,
1016 approver_flag,
1017 approval_status,
1018 task_name,
1019 target_date,
1020 completion_date,
1021 creation_date,
1022 created_by,
1023 last_update_date,
1024 last_updated_by,
1025 last_amendment_update,
1026 modified_flag)
1027
1028 select pah.auction_header_id,
1029 pntm.list_id,
1030 pntm.user_id,
1031 pntm.menu_name,
1032 pntm.member_type,
1033 pntm.approver_flag,
1034 pntm.approval_status,
1035 pntm.task_name,
1036 pntm.target_date,
1037 pntm.completion_date,
1038 pntm.creation_date,
1039 pntm.created_by,
1040 pntm.last_update_date,
1041 pntm.last_updated_by,
1042 pntm.last_amendment_update,
1043 pntm.modified_flag
1044 from pon_auction_headers_all pah,
1045 pon_neg_team_members pntm
1046 where pah.auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1047 pah.amendment_number < v_currAmendmentNumber and
1048 pntm.auction_header_id = p_currAuctionHeaderId and
1049 pntm.user_id = p_userId;
1050
1051 END PROPAGATE_BACK_INSERT_MEMBER;
1052
1053 PROCEDURE PROPAGATE_BACK_UNLOCK(p_currAuctionHeaderId IN NUMBER,
1054 p_userId IN NUMBER,
1055 p_unlock_date IN DATE,
1056 p_unlock_type IN VARCHAR2) IS
1057
1058 v_auctionHeaderIdOrigAmend NUMBER;
1059 v_currAmendmentNumber NUMBER;
1060 l_module_name VARCHAR2 (30);
1061
1062 BEGIN
1063
1064 l_module_name := 'PROPAGATE_BACK_UNLOCK';
1065
1066 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1067 FND_LOG.string (log_level => FND_LOG.level_procedure,
1068 module => g_module_prefix || l_module_name,
1069 message => 'Entered procedure = ' || l_module_name || '.' ||
1070 ' Parameters: p_currAuctionHeaderId = ' || p_currAuctionHeaderId || ', ' ||
1071 ' p_userId = ' || p_userId || ', ' || ' p_unlock_date = ' || p_unlock_date || ', ' ||
1072 ' p_unlock_type = ' || p_unlock_type);
1073 END IF;
1074
1075 select auction_header_id_orig_amend, nvl(amendment_number, 0)
1076 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
1077 from pon_auction_headers_all
1078 where auction_header_id = p_currAuctionHeaderId;
1079
1080 IF p_unlock_type = 'Technical' THEN
1081 update pon_auction_headers_all
1082 set technical_lock_status = 'UNLOCKED',
1083 technical_unlock_tp_contact_id = p_userId,
1084 technical_actual_unlock_date = p_unlock_date
1085 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1086 amendment_number < v_currAmendmentNumber;
1087 ELSE
1088 update pon_auction_headers_all
1089 set sealed_auction_status = 'UNLOCKED',
1090 sealed_unlock_tp_contact_id = p_userId,
1091 sealed_actual_unlock_date = p_unlock_date
1092 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1093 amendment_number < v_currAmendmentNumber;
1094 END IF;
1095
1096 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1097 FND_LOG.string (log_level => FND_LOG.level_procedure,
1098 module => g_module_prefix || l_module_name,
1099 message => 'Leaving procedure = ' || l_module_name);
1100 END IF;
1101
1102 END PROPAGATE_BACK_UNLOCK;
1103
1104 PROCEDURE PROPAGATE_BACK_UNSEAL(p_currAuctionHeaderId IN NUMBER,
1105 p_userId IN NUMBER,
1106 p_unseal_date IN DATE,
1107 p_unseal_type IN VARCHAR2) IS
1108
1109 v_auctionHeaderIdOrigAmend NUMBER;
1110 v_currAmendmentNumber NUMBER;
1111 v_technicalLockStatus VARCHAR2(20);
1112 l_module_name VARCHAR2 (30);
1113
1114 BEGIN
1115
1116 l_module_name := 'PROPAGATE_BACK_UNSEAL';
1117
1118 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1119 FND_LOG.string (log_level => FND_LOG.level_procedure,
1120 module => g_module_prefix || l_module_name,
1121 message => 'Entered procedure = ' || l_module_name || '.' ||
1122 ' Parameters: p_currAuctionHeaderId = ' || p_currAuctionHeaderId || ', ' ||
1123 ' p_userId = ' || p_userId || ', ' || ' p_unseal_date = ' || p_unseal_date || ', ' ||
1124 ' p_unseal_type = ' || p_unseal_type);
1125 END IF;
1126
1127 select auction_header_id_orig_amend, nvl(amendment_number, 0), technical_lock_status
1128 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber, v_technicalLockStatus
1129 from pon_auction_headers_all
1130 where auction_header_id = p_currAuctionHeaderId;
1131
1132 IF p_unseal_type = 'Technical' THEN
1133 update pon_auction_headers_all
1134 set technical_lock_status = 'ACTIVE',
1135 technical_unseal_tp_contact_id = p_userId,
1136 technical_actual_unseal_date = p_unseal_date
1137 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1138 amendment_number < v_currAmendmentNumber;
1139
1140 ELSIF p_unseal_type = 'Commercial' THEN
1141 IF v_technicalLockStatus = 'ACTIVE' THEN
1142 update pon_auction_headers_all
1143 set sealed_auction_status = 'ACTIVE',
1144 sealed_unseal_tp_contact_id = p_userId,
1145 sealed_actual_unseal_date = p_unseal_date
1146 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1147 amendment_number < v_currAmendmentNumber;
1148 ELSE
1149 update pon_auction_headers_all
1150 set sealed_auction_status = 'ACTIVE',
1151 sealed_unseal_tp_contact_id = p_userId,
1152 sealed_actual_unseal_date = p_unseal_date,
1153 technical_lock_status = 'ACTIVE',
1154 technical_unseal_tp_contact_id = p_userId,
1155 technical_actual_unseal_date = p_unseal_date
1156 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1157 amendment_number < v_currAmendmentNumber;
1158 END IF;
1159 ELSE
1160 update pon_auction_headers_all
1161 set sealed_auction_status = 'ACTIVE',
1162 sealed_unseal_tp_contact_id = p_userId,
1163 sealed_actual_unseal_date = p_unseal_date
1164 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1165 amendment_number < v_currAmendmentNumber;
1166 END IF;
1167
1168 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1169 FND_LOG.string (log_level => FND_LOG.level_procedure,
1170 module => g_module_prefix || l_module_name,
1171 message => 'Leaving procedure = ' || l_module_name);
1172 END IF;
1173
1174 END PROPAGATE_BACK_UNSEAL;
1175
1176 PROCEDURE PROCESS_PRICE_FACTORS(p_auction_header_id IN NUMBER,
1177 p_user_id IN NUMBER,
1178 p_login_id IN NUMBER) IS
1179
1180 l_auction_has_price_elements VARCHAR2(2);
1181 BEGIN
1182
1183 SELECT HAS_PRICE_ELEMENTS
1184 INTO l_auction_has_price_elements
1185 FROM PON_AUCTION_HEADERS_ALL
1186 WHERE AUCTION_HEADER_ID = p_auction_header_id;
1187
1188 IF ('Y' = l_auction_has_price_elements) THEN
1189
1190 insert into pon_price_elements
1191 (auction_header_id,
1192 line_number,
1193 list_id,
1194 price_element_type_id,
1195 pricing_basis,
1196 value,
1197 display_target_flag,
1198 sequence_number,
1199 creation_date,
1200 created_by,
1201 last_update_date,
1202 last_updated_by,
1203 pf_type,
1204 display_to_suppliers_flag)
1205
1206 select auction_header_id,
1207 line_number,
1208 -1,
1209 -10,
1210 decode(order_type_lookup_code, 'FIXED PRICE', 'FIXED_AMOUNT', 'PER_UNIT'),
1211 unit_target_price,
1212 unit_display_target_flag,
1213 -10,
1214 sysdate,
1215 p_user_id,
1216 sysdate,
1217 p_user_id,
1218 'SUPPLIER',
1219 'Y'
1220 from pon_auction_item_prices_all
1221 where auction_header_id = p_auction_header_id and
1222 (has_price_elements_flag = 'Y' or has_buyer_pfs_flag = 'Y');
1223
1224 insert into pon_pf_supplier_formula
1225 (auction_header_id,
1226 line_number,
1227 trading_partner_id,
1228 vendor_site_id,
1229 unit_price,
1230 fixed_amount,
1231 percentage,
1232 creation_date,
1233 created_by,
1234 last_update_date,
1235 last_updated_by,
1236 last_update_login)
1237
1238 select paip.auction_header_id,
1239 paip.line_number,
1240 pbp.trading_partner_id,
1241 pbp.vendor_site_id,
1242 sum(decode(ppe.pricing_basis, 'PER_UNIT', Decode(Nvl(ppe.negative_cost_factor_flag,'N'),'Y', -ppsv.Value,ppsv.Value) , 0)) unit_price,
1243 sum(decode(ppe.pricing_basis, 'FIXED_AMOUNT', Decode(Nvl(ppe.negative_cost_factor_flag,'N'),'Y', -ppsv.Value,ppsv.Value), 0)) fixed_amount,
1244 1 + sum(decode(ppe.pricing_basis, 'PERCENTAGE', Decode(Nvl(ppe.negative_cost_factor_flag,'N'),'Y', -ppsv.Value/100,ppsv.Value/100), 0)) percentage,
1245 sysdate,
1246 p_user_id,
1247 sysdate,
1248 p_user_id,
1249 p_login_id
1250 from pon_auction_item_prices_all paip,
1251 pon_bidding_parties pbp,
1252 pon_pf_supplier_values ppsv,
1253 pon_price_elements ppe
1254 where paip.auction_header_id = p_auction_header_id and
1255 pbp.auction_header_id = paip.auction_header_id and
1256 pbp.auction_header_id = ppsv.auction_header_id and
1257 pbp.sequence = ppsv.supplier_seq_number and
1258 paip.line_number = ppsv.line_number and
1259 ppsv.auction_header_id = ppe.auction_header_id and
1260 ppsv.line_number = ppe.line_number and
1261 ppsv.pf_seq_number = ppe.sequence_number
1262 group by paip.auction_header_id, paip.line_number, pbp.trading_partner_id, pbp.vendor_site_id;
1263
1264 END IF;
1265
1266 END PROCESS_PRICE_FACTORS;
1267
1268 PROCEDURE MANUAL_CLOSE_LINE (
1269 x_result OUT NOCOPY VARCHAR2,
1270 x_error_code OUT NOCOPY VARCHAR2,
1271 x_error_message OUT NOCOPY VARCHAR2,
1272 p_auction_header_id IN NUMBER,
1273 p_line_number IN NUMBER,
1274 p_user_id IN NUMBER,
1275 x_is_auction_closed OUT NOCOPY VARCHAR2
1276 ) IS
1277
1278 l_module_name VARCHAR2 (30);
1279 x_temp PON_AUCTION_HEADERS_ALL.LAST_UPDATE_DATE%TYPE;
1280 x_close_bidding_date PON_AUCTION_HEADERS_ALL.CLOSE_BIDDING_DATE%TYPE;
1281 v_auction_last_line_number PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
1282 p_new_close_date PON_AUCTION_HEADERS_ALL.CLOSE_BIDDING_DATE%TYPE;
1283 BEGIN
1284
1285 l_module_name := 'MANUAL_CLOSE_LINE';
1286 x_result := FND_API.g_ret_sts_success;
1287
1288 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1289 FND_LOG.string (log_level => FND_LOG.level_procedure,
1290 module => g_module_prefix || l_module_name,
1291 message => 'Entered procedure = ' || l_module_name || '.' ||
1292 ' Parameters: p_auction_header_id = ' || p_auction_header_id || ', ' ||
1293 ' p_user_id = ' || p_user_id);
1294 END IF;
1295
1296 -- lock negotiation header
1297 SELECT LAST_UPDATE_DATE, CLOSE_BIDDING_DATE
1298 INTO x_temp, x_close_bidding_date
1299 FROM PON_AUCTION_HEADERS_ALL
1300 WHERE AUCTION_HEADER_ID = p_auction_header_id
1301 FOR UPDATE;
1302
1303 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1304 FND_LOG.string (log_level => FND_LOG.level_statement,
1305 module => g_module_prefix || l_module_name,
1306 message => 'Locked the negotiation header');
1307 END IF;
1308
1309 -- update header date
1310 update pon_auction_headers_all
1311 set last_update_date = sysdate,
1312 last_updated_by = p_user_id
1313 where auction_header_id = p_auction_header_id;
1314
1315 p_new_close_date := sysdate;
1316
1317 -- update item close date
1318 update pon_auction_item_prices_all
1319 set close_bidding_date = p_new_close_date,
1320 last_update_date = sysdate,
1321 last_updated_by = p_user_id
1322 where auction_header_id = p_auction_header_id
1323 and (line_number = p_line_number
1324 or parent_line_number = p_line_number);
1325
1326 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1327 FND_LOG.string (log_level => FND_LOG.level_statement,
1328 module => g_module_prefix || l_module_name,
1329 message => 'Update header and the line.');
1330 END IF;
1331
1332 --In case this is the last line then the auction has to be closed.
1333 select line_number into v_auction_last_line_number
1334 from pon_auction_item_prices_all
1335 where auction_header_id = p_auction_header_id
1336 and disp_line_number =
1337 (select max(disp_line_number)
1338 from pon_auction_item_prices_all
1339 where auction_header_id=p_auction_header_id
1340 and group_type in ('LINE', 'LOT', 'GROUP'));
1341
1342 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1343 FND_LOG.string (log_level => FND_LOG.level_statement,
1344 module => g_module_prefix || l_module_name,
1345 message => 'The last line in the auction is = ' || v_auction_last_line_number);
1346 END IF;
1347
1348 if (p_line_number = v_auction_last_line_number) then
1349
1350 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1351 FND_LOG.string (log_level => FND_LOG.level_statement,
1352 module => g_module_prefix || l_module_name,
1353 message => 'The last line number in the auction matches with p_line_number, closing the negotiation');
1354 END IF;
1355
1356 update pon_auction_headers_all set close_bidding_date = p_new_close_date
1357 where auction_header_id = p_auction_header_id;
1358 x_is_auction_closed := 'Y';
1359
1360 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1361 FND_LOG.string (log_level => FND_LOG.level_statement,
1362 module => g_module_prefix || l_module_name,
1363 message => 'Updated header sending notification.');
1364 END IF;
1365
1366 pon_auction_pkg.CLOSEEARLY_AUCTION(p_auction_header_id,
1367 p_new_close_date,
1368 null);
1369 end if;
1370
1371 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1372 FND_LOG.string (log_level => FND_LOG.level_procedure,
1373 module => g_module_prefix || l_module_name,
1374 message => 'Leaving procedure = ' || l_module_name);
1375 END IF;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 x_result := FND_API.g_ret_sts_unexp_error;
1380 x_error_code := SQLCODE;
1381 x_error_message := SUBSTR(SQLERRM, 1, 100);
1382
1383 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1384 FND_LOG.string (log_level => FND_LOG.level_exception,
1385 module => g_module_prefix || l_module_name,
1386 message => 'Unexpected exception occured error_code = ' ||
1387 x_error_code || ', error_message = ' || x_error_message);
1388 END IF;
1389
1390 END MANUAL_CLOSE_LINE;
1391
1392 PROCEDURE PROPAGATE_BACK_TECH_EVAL(p_currAuctionHeaderId IN NUMBER,
1393 p_tech_eval_status IN VARCHAR2) IS
1394
1395 v_auctionHeaderIdOrigAmend NUMBER;
1396 v_currAmendmentNumber NUMBER;
1397 l_module_name VARCHAR2 (30);
1398
1399 BEGIN
1400
1401 l_module_name := 'PROPAGATE_BACK_TECH_EVAL';
1402
1403 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1404 FND_LOG.string (log_level => FND_LOG.level_procedure,
1405 module => g_module_prefix || l_module_name,
1406 message => 'Entered procedure = ' || l_module_name || '.' ||
1407 ' Parameters: p_currAuctionHeaderId = ' || p_currAuctionHeaderId || ', ' ||
1408 ' p_tech_eval_status = ' || p_tech_eval_status);
1409 END IF;
1410
1411 select auction_header_id_orig_amend, nvl(amendment_number, 0)
1412 into v_auctionHeaderIdOrigAmend, v_currAmendmentNumber
1413 from pon_auction_headers_all
1414 where auction_header_id = p_currAuctionHeaderId;
1415
1416 update pon_auction_headers_all
1417 set technical_evaluation_status = p_tech_eval_status
1418 where auction_header_id_orig_amend = v_auctionHeaderIdOrigAmend and
1419 amendment_number < v_currAmendmentNumber;
1420
1421 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1422 FND_LOG.string (log_level => FND_LOG.level_procedure,
1423 module => g_module_prefix || l_module_name,
1424 message => 'Leaving procedure = ' || l_module_name);
1425 END IF;
1426
1427 END PROPAGATE_BACK_TECH_EVAL;
1428
1429 /* Added for federal CLM Sol. Amendments Project.
1430 * Will be called in case of cancelling federal documents via amendments.
1431 * This procedure is a wrapper to existing CANCEL_NEGOTIATION method except that
1432 * this procedure also returns document_number of cancelled document.
1433 */
1434 PROCEDURE CANCEL_FEDERAL_NEGOTIATION (p_auction_header_id IN NUMBER,
1435 p_send_note_flag IN VARCHAR2,
1436 p_reason IN VARCHAR2,
1437 p_user_id IN NUMBER,
1438 x_prev_doc_number OUT NOCOPY VARCHAR2,
1439 x_error_code OUT NOCOPY VARCHAR2) IS
1440
1441 l_prev_auction_header_id pon_auction_headers_all.auction_header_id%TYPE;
1442
1443 --Line Type and STructute Changes Project
1444 CURSOR c_has_linked_pr_refs IS
1445 SELECT Count(*) FROM pon_backing_requisitions
1446 WHERE auction_header_id = p_auction_header_id;
1447
1448 l_linked_pr_refs_count NUMBER;
1449 l_return_status VARCHAR2(1);
1450 l_msg_data VARCHAR2(4000);
1451
1452 Begin
1453 --Get details of original document.
1454 select auction_header_id,document_number
1455 into l_prev_auction_header_id,x_prev_doc_number
1456 from pon_auction_headers_all
1457 where
1458 auction_header_id = (select auction_header_id_prev_amend
1459 from pon_auction_headers_all
1460 where auction_header_id=p_auction_header_id);
1461
1462 -- Call CANCEL_NEGOTIATION
1463 CANCEL_NEGOTIATION(l_prev_auction_header_id,
1464 p_send_note_flag,
1465 p_reason,
1466 p_user_id,
1467 x_error_code);
1468
1469 --Line Type and Strucure Changes
1470 -- If negotiation is cancelled via an amendment, then return all linked reqs back to pool
1471 --Line Type and STructute Changes Project
1472 OPEN c_has_linked_pr_refs;
1473 FETCH c_has_linked_pr_refs INTO l_linked_pr_refs_count;
1474 CLOSE c_has_linked_pr_refs;
1475
1476 IF (l_linked_pr_refs_count > 0) then
1477 po_negotiations_sv1.update_sol_ref_delete_all(p_auction_header_id => l_prev_auction_header_id,
1478 p_delete_pbr_yn => 'N',
1479 x_return_status => l_return_status,
1480 x_error_msg => l_msg_data,
1481 x_error_code => x_error_code);
1482 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1483 x_error_code := 'SUCCESS';
1484 END IF;
1485
1486 END IF;
1487
1488 if x_error_code = 'SUCCESS' then
1489 update pon_auction_headers_all
1490 set auction_status = 'APPLIED'
1491 where auction_header_id = p_auction_header_id;
1492 end if;
1493
1494 End CANCEL_FEDERAL_NEGOTIATION;
1495
1496 --Bug 15829214
1497 PROCEDURE UPDATE_REQ_REF_LINES (p_auction_header_id IN NUMBER,
1498 p_document_number IN VARCHAR2) IS
1499
1500 l_module_name VARCHAR2(30) := 'UPDATE_REQ_REF_LINES';
1501
1502 BEGIN
1503
1504 UPDATE PO_REQUISITION_LINES_ALL
1505 SET AUCTION_DISPLAY_NUMBER = p_document_number
1506 WHERE REQUISITION_LINE_ID IN (SELECT REQUISITION_LINE_ID FROM PON_BACKING_REQUISITIONS WHERE AUCTION_HEADER_ID = p_auction_header_id)
1507 AND AUCTION_HEADER_ID = p_auction_header_id;
1508
1509 EXCEPTION
1510 WHEN OTHERS THEN
1511 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1512 FND_LOG.string (log_level => FND_LOG.level_procedure,
1513 module => g_module_prefix || l_module_name,
1514 message => 'Exception when updating new docuemnt_number in po_requistion_lines_all for auction_header_id : ' || p_auction_header_id);
1515 END IF;
1516
1517 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1518 FND_LOG.string (log_level => FND_LOG.level_procedure,
1519 module => g_module_prefix || l_module_name,
1520 message => 'Updated po_requistion_lines_all with new docuemnt_number for auction_header_id : ' || p_auction_header_id);
1521 END IF;
1522
1523 END UPDATE_REQ_REF_LINES;
1524
1525 END PON_NEG_UPDATE_PKG;
[Home] [Help]