[Home] [Help]
PACKAGE BODY: APPS.PON_WITH_DRAW_PKG
Source
1 PACKAGE BODY PON_WITH_DRAW_PKG AS
2 /* $Header: PONWDRAWB.pls 120.10 2012/03/05 10:57:34 puppulur noship $ */
3
4 PROCEDURE SEND_WDRAW_BY_BUYER_NOTIF
5 (
6 L_AUCTION_HEADER_ID IN NUMBER,
7 L_BID_NUMBER IN NUMBER,
8 L_WITHDRAW_REASON IN VARCHAR2
9
10 ) AS
11 l_itemtype wf_items.item_type%TYPE;
12 l_itemkey wf_items.item_key%TYPE;
13 l_process wf_process_activities.process_name%TYPE;
14 x_role_name VARCHAR2(100);
15 l_user_table wf_directory.usertable;
16 l_document_number pon_auction_headers_all.document_number%TYPE;
17 l_auction_title pon_auction_headers_all.auction_title%TYPE;
18 L_TRADING_PARTNER_ID pon_auction_headers_all.TRADING_PARTNER_ID%TYPE;
19 L_TRADING_PARTNER_CONTACT_ID pon_auction_headers_all.TRADING_PARTNER_CONTACT_ID%TYPE;
20 L_PREPARER_TP_NAME HZ_PARTIES.PARTY_NAME%TYPE;
21 L_TRADING_PARTNER_CONTACT_NAME PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
22 L_AUCTION_HEADER_ID_ORIG_AMEND PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID_ORIG_AMEND%TYPE;
23 L_BID_TRADING_PARTNER_ID pon_bid_headers.TRADING_PARTNER_ID%TYPE;
24 L_BID_TRADPART_CNTC_ID pon_bid_headers.TRADING_PARTNER_CONTACT_ID%TYPE;
25 L_DOCUMENT_TYPE VARCHAR2(100);
26 L_DOCUMENT_TYPE_CODE VARCHAR2(100);
27 L_PREVIEW_DATE DATE;
28 L_START_DATE DATE;
29 L_END_DATE DATE;
30 L_SUPPLIER AP_SUPPLIERS.VENDOR_NAME%TYPE;
31 L_SUPPLIER_SITE_ID NUMBER;
32 L_SUPPLIER_SITE_NAME AP_SUPPLIER_SITES_ALL.VENDOR_SITE_CODE%TYPE:=NULL;
33 L_FROM_USER HZ_PARTIES.PARTY_NAME%TYPE;
34 X_VIEW_QUOTE_URL VARCHAR2(2000);
35 X_NOTIFICATION_ID NUMBER;
36 L_NOTIF_SUBSCR_FLAG VARCHAR2(1);
37
38 L_STEP NUMBER;
39
40 L_RECORD_STATUS VARCHAR2(1);
41
42 BEGIN
43
44 L_STEP:=1;
45
46 l_itemtype:='PONWDRAW';
47 l_itemkey:=To_Char(l_bid_number)||'-'||To_Char(l_auction_header_id);
48 l_process:='RESPONSE_WITH_DRAW';
49
50 /* Document Type */
51
52 SELECT FL.LOOKUP_CODE,FL.MEANING INTO L_DOCUMENT_TYPE_CODE,L_DOCUMENT_TYPE
53 FROM
54 PON_AUCTION_HEADERS_ALL PAH,
55 PON_AUC_DOCTYPES DOC,
56 FND_LOOKUPS FL
57 WHERE
58 PAH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
59 AND
60 PAH.DOCTYPE_ID=DOC.DOCTYPE_ID
61 AND
62 FL.LOOKUP_TYPE='PON_AUCTION_DOC_TYPES'
63 AND
64 FL.LOOKUP_CODE = DOC.INTERNAL_NAME ;
65
66 /* Now check the notification subscription */
67
68 L_NOTIF_SUBSCR_FLAG:='N';
69
70 IF L_DOCUMENT_TYPE_CODE='SOLICITATION'
71 THEN
72
73 SELECT Nvl(SOL_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
74 FROM
75 PON_NOTIF_SUBSCRIPTION_GROUPS
76 WHERE
77 NOTIF_GROUP_CODE='S_OFFER_WITH_DRAW'
78 AND
79 NOTIF_GROUP_TYPE='TO_SUPPLIER'
80 AND
81 ROWNUM=1;
82 END IF;
83
84 IF L_DOCUMENT_TYPE_CODE='BUYER_AUCTION'
85 THEN
86
87 SELECT Nvl(AUCTION_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
88 FROM
89 PON_NOTIF_SUBSCRIPTION_GROUPS
90 WHERE
91 NOTIF_GROUP_CODE='S_OFFER_WITH_DRAW'
92 AND
93 NOTIF_GROUP_TYPE='TO_SUPPLIER'
94 AND
95 ROWNUM=1;
96 END IF;
97 IF L_DOCUMENT_TYPE_CODE='REQUEST_FOR_INFORMATION'
98 THEN
99
100 SELECT Nvl(RFI_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
101 FROM
102 PON_NOTIF_SUBSCRIPTION_GROUPS
103 WHERE
104 NOTIF_GROUP_CODE='S_OFFER_WITH_DRAW'
105 AND
106 NOTIF_GROUP_TYPE='TO_SUPPLIER'
107 AND
108 ROWNUM=1;
109 END IF;
110 IF L_DOCUMENT_TYPE_CODE='REQUEST_FOR_QUOTE'
111 THEN
112
113 SELECT Nvl(RFQ_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
114 FROM
115 PON_NOTIF_SUBSCRIPTION_GROUPS
116 WHERE
117 NOTIF_GROUP_CODE='S_OFFER_WITH_DRAW'
118 AND
119 NOTIF_GROUP_TYPE='TO_SUPPLIER'
120 AND
121 ROWNUM=1;
122 END IF;
123 IF L_NOTIF_SUBSCR_FLAG='Y'
124 THEN
125
126 wf_engine.CreateProcess(itemtype => l_itemtype,
127 itemkey => l_itemkey,
128 process => l_process);
129
130 L_STEP:=2;
131
132 SELECT PAH.DOCUMENT_NUMBER,PAH.AUCTION_TITLE,PAH.TRADING_PARTNER_ID,
133 PAH.VIEW_BY_DATE,PAH.OPEN_BIDDING_DATE,PAH.CLOSE_BIDDING_DATE,PAH.TRADING_PARTNER_CONTACT_ID,
134 PAH.TRADING_PARTNER_CONTACT_NAME,
135 PAH.AUCTION_HEADER_ID_ORIG_AMEND
136 INTO L_DOCUMENT_NUMBER,L_AUCTION_TITLE,L_TRADING_PARTNER_ID,
137 L_PREVIEW_DATE,L_START_DATE,L_END_DATE,L_TRADING_PARTNER_CONTACT_ID,
138 L_FROM_USER,
139 L_AUCTION_HEADER_ID_ORIG_AMEND
140 FROM
141 PON_AUCTION_HEADERS_ALL PAH WHERE PAH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID;
142
143 L_STEP:=3;
144
145 /* Receiver will be the supplier user in this case */
146
147 SELECT PBH.TRADING_PARTNER_CONTACT_NAME INTO L_TRADING_PARTNER_CONTACT_NAME
148 FROM PON_BID_HEADERS PBH
149 WHERE
150 PBH.BID_NUMBER=L_BID_NUMBER
151 AND
152 PBH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
153 AND
154 ROWNUM<2;
155
156 L_STEP:=5;
157
158 IF L_DOCUMENT_TYPE_CODE IN ('SOLICITATION')
159 THEN
160
161 wf_engine.SetItemAttrText (itemtype => l_itemtype,
162 itemkey => l_itemkey,
163 aname => 'DOCUMENT_TYPE',
164 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUC_NEG_X'));
165
166 wf_engine.SetItemAttrText (itemtype => l_itemtype,
167 itemkey => l_itemkey,
168 aname => 'RESPONSE_TYPE',
169 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_X')||' '||L_BID_NUMBER);
170
171 wf_engine.SetItemAttrText (itemtype => l_itemtype,
172 itemkey => l_itemkey,
173 aname => 'RESPONSE',
174 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_X'));
175
176
177 ELSE
178
179 wf_engine.SetItemAttrText (itemtype => l_itemtype,
180 itemkey => l_itemkey,
181 aname => 'DOCUMENT_TYPE',
182 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUC_NEG'));
183
184 wf_engine.SetItemAttrText (itemtype => l_itemtype,
185 itemkey => l_itemkey,
186 aname => 'RESPONSE_TYPE',
187 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_R')||' '||L_BID_NUMBER);
188
189 wf_engine.SetItemAttrText (itemtype => l_itemtype,
190 itemkey => l_itemkey,
191 aname => 'RESPONSE',
192 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_R'));
193
194 END IF;
195
196 wf_engine.SetItemAttrText (itemtype => l_itemtype,
197 itemkey => l_itemkey,
198 aname => 'SOURCE_DOC_TYPE',
199 avalue => L_DOCUMENT_TYPE||' '||L_DOCUMENT_NUMBER);
200
201
202 wf_engine.SetItemAttrText (itemtype => l_itemtype,
203 itemkey => l_itemkey,
204 aname => 'RECEIVER',
205 avalue => L_TRADING_PARTNER_CONTACT_NAME);
206
207 SELECT HZ.PARTY_NAME INTO L_PREPARER_TP_NAME
208 FROM
209 HZ_PARTIES HZ
210 WHERE
211 PARTY_ID=L_TRADING_PARTNER_ID;
212
213 L_STEP:=6;
214
215 wf_engine.SetItemAttrText (itemtype => l_itemtype,
216 itemkey => l_itemkey,
217 aname => 'PREPARER_TP_NAME',
218 avalue => L_PREPARER_TP_NAME);
219
220 wf_engine.SetItemAttrText (itemtype => l_itemtype,
221 itemkey => l_itemkey,
222 aname => 'DOC_NUMBER',
223 avalue => L_DOCUMENT_NUMBER);
224
225 wf_engine.SetItemAttrText (itemtype => l_itemtype,
226 itemkey => l_itemkey,
227 aname => 'AUCTION_TITLE',
228 avalue => L_AUCTION_TITLE);
229
230 wf_engine.SetItemAttrText (itemtype => l_itemtype,
231 itemkey => l_itemkey,
232 aname => 'SUB_AUCTION_TITLE',
233 avalue => L_AUCTION_TITLE);
234
235 wf_engine.SetItemAttrText (itemtype => l_itemtype,
236 itemkey => l_itemkey,
237 aname => 'ORIGIN_USER_NAME',
238 avalue => L_FROM_USER);
239
240 /* Response URL Bug Chnages */
241 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
242 itemkey => l_itemkey,
243 aname => 'BID_ID',
244 avalue => L_BID_NUMBER);
245
246 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
247 itemkey => l_itemkey,
248 aname => 'AUCTION_ID',
249 avalue => L_AUCTION_HEADER_ID);
250
251 /* Preview Date,Open Date,Close Date,Withdrawn Date */
252
253 IF L_PREVIEW_DATE IS NULL THEN
254 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
255 itemkey => l_itemkey,
256 aname => 'NEG_PREVIEW_DATE',
257 avalue => NULL);
258 ELSE
259 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
260 itemkey => l_itemkey,
261 aname => 'NEG_PREVIEW_DATE',
262 avalue => L_PREVIEW_DATE);
263 END IF;
264
265
266 IF L_START_DATE IS NULL THEN
267 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
268 itemkey => l_itemkey,
269 aname => 'NEG_OPEN_DATE',
270 avalue => NULL);
271 ELSE
272 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
273 itemkey => l_itemkey,
274 aname => 'NEG_OPEN_DATE',
275 avalue => L_START_DATE);
276 END IF;
277
278 IF L_END_DATE IS NULL THEN
279
280 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
281 itemkey => l_itemkey,
282 aname => 'NEG_CLOSE_DATE',
283 avalue => NULL);
284 ELSE
285
286 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
287 itemkey => l_itemkey,
288 aname => 'NEG_CLOSE_DATE',
289 avalue => L_END_DATE);
290 END IF;
291
292 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
293 itemkey => l_itemkey,
294 aname => 'NEG_WITH_DRAWN_DATE',
295 avalue => SYSDATE);
296
297 /* Supplier, Supplier Site */
298
299 SELECT PBH.TRADING_PARTNER_NAME,PBH.VENDOR_SITE_ID
300 INTO L_SUPPLIER,L_SUPPLIER_SITE_ID
301 FROM
302 PON_BID_HEADERS PBH
303 WHERE
304 PBH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
305 AND
306 PBH.BID_NUMBER=L_BID_NUMBER;
307
308 L_STEP:=7;
309
310 wf_engine.SetItemAttrText (itemtype => l_itemtype,
311 itemkey => l_itemkey,
312 aname => 'SUPPLIER',
313 avalue => L_SUPPLIER);
314
315 IF L_SUPPLIER_SITE_ID IS NOT NULL AND L_SUPPLIER_SITE_ID <> -1
316 THEN
317 SELECT APS.VENDOR_SITE_CODE INTO L_SUPPLIER_SITE_NAME
318 FROM
319 AP_SUPPLIER_SITES_ALL APS
320 WHERE
321 APS.VENDOR_SITE_ID=L_SUPPLIER_SITE_ID;
322
323 END IF;
324
325 L_STEP:=8;
326
327 wf_engine.SetItemAttrText (itemtype => l_itemtype,
328 itemkey => l_itemkey,
329 aname => 'SUPPLIER_SITE',
330 avalue => L_SUPPLIER_SITE_NAME);
331
332 wf_engine.SetItemAttrText (itemtype => l_itemtype,
333 itemkey => l_itemkey,
334 aname => 'WITHDRAW_REASON',
335 avalue => L_WITHDRAW_REASON);
336
337 /* Set View Response URL */
338
339 x_view_quote_url:=pon_wf_utl_pkg.get_dest_page_url (p_dest_func => 'PONRESENQ_VIEWBID',p_notif_performer => 'SUPPLIER');
340
341 Begin
342 SELECT notification_id INTO x_notification_id from
343 WF_ITEM_ACTIVITY_STATUSES WHERE ITEM_TYPE=l_itemtype
344 AND ITEM_KEY=l_itemkey
345 AND ASSIGNED_USER IS NOT NULL
346 AND ROWNUM<=1;
347 EXCEPTION
348 WHEN No_Data_Found THEN
349 x_notification_id:=NULL;
350 WHEN OTHERS THEN
351 NULL;
352 END;
353 IF(x_notification_id IS NOT null) THEN
354 x_view_quote_url:=REPLACE(x_view_quote_url,'&#NID',x_notification_id);
355 END IF;
356
357 wf_engine.SetItemAttrText (itemtype => l_itemtype,
358 itemkey => l_itemkey,
359 aname => 'URL',
360 avalue => x_view_quote_url);
361
362
363 wf_engine.StartProcess(itemtype => l_itemtype,
364 itemkey => l_itemkey );
365
366 END IF;
367
368
369 UPDATE PON_BID_HEADERS
370 SET
371 WITHDRAW_REASON=L_WITHDRAW_REASON,
372 BID_STATUS='ARCHIVED'
373 WHERE
374 AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
375 AND
376 BID_NUMBER=L_BID_NUMBER;
377
378 /* Record an Entry in Supplier Activities table for Withdraw Action */
379
380
381 SELECT PBH.TRADING_PARTNER_ID,PBH.TRADING_PARTNER_CONTACT_ID,
382 PAH.AUCTION_HEADER_ID_ORIG_AMEND
383 INTO L_BID_TRADING_PARTNER_ID,L_BID_TRADPART_CNTC_ID,
384 L_AUCTION_HEADER_ID_ORIG_AMEND
385 FROM PON_BID_HEADERS PBH,
386 PON_AUCTION_HEADERS_ALL PAH
387 WHERE
388 PAH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID and
389 PBH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID AND
390 PBH.BID_NUMBER=L_BID_NUMBER;
391
392 PON_CONSOLE_PVT.record_supplier_activity(
393 L_AUCTION_HEADER_ID ,
394 L_AUCTION_HEADER_ID_ORIG_AMEND ,
395 L_BID_TRADING_PARTNER_ID ,
396 L_BID_TRADPART_CNTC_ID ,
397 userenv('SESSIONID') ,
398 'WITHDRAW_OFFER' ,
399 L_BID_NUMBER,
400 L_RECORD_STATUS ) ;
401
402 /* End */
403
404 COMMIT;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 WF_CORE.CONTEXT('SEND_WDRAW_BY_BUYER_NOTIF','SEND_WDRAW_BY_BUYER_NOTIF',L_ITEMTYPE,L_ITEMKEY);
409 WF_CORE.RAISE('ERROR_NAME');
410 RAISE_APPLICATION_ERROR(-20041, 'FAILURE AT STEP ' , TRUE);
411 END;
412
413 PROCEDURE SEND_WDRAW_BY_SUPPLIER_NOTIF
414 (
415 L_AUCTION_HEADER_ID IN NUMBER,
416 L_BID_NUMBER IN NUMBER,
417 L_WITHDRAW_REASON IN VARCHAR2
418
419 ) AS
420 l_itemtype wf_items.item_type%TYPE;
421 l_itemkey wf_items.item_key%TYPE;
422 l_process wf_process_activities.process_name%TYPE;
423 x_role_name VARCHAR2(100);
424 l_user_table wf_directory.usertable;
425 l_document_number pon_auction_headers_all.document_number%TYPE;
426 l_auction_title pon_auction_headers_all.auction_title%TYPE;
427 L_TRADING_PARTNER_ID pon_auction_headers_all.TRADING_PARTNER_ID%TYPE;
428 L_TRADING_PARTNER_CONTACT_ID pon_auction_headers_all.TRADING_PARTNER_CONTACT_ID%TYPE;
429 L_PREPARER_TP_NAME HZ_PARTIES.PARTY_NAME%TYPE;
430 L_TRADING_PARTNER_CONTACT_NAME PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
431 L_AUCTION_HEADER_ID_ORIG_AMEND PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID_ORIG_AMEND%TYPE;
432 L_BID_TRADING_PARTNER_ID pon_bid_headers.TRADING_PARTNER_ID%TYPE;
433 L_BID_TRADPART_CNTC_ID pon_bid_headers.TRADING_PARTNER_CONTACT_ID%TYPE;
434 L_DOCUMENT_TYPE VARCHAR2(100);
435 L_DOCUMENT_TYPE_CODE VARCHAR2(100);
436 L_PREVIEW_DATE DATE;
437 L_START_DATE DATE;
438 L_END_DATE DATE;
439 L_SUPPLIER AP_SUPPLIERS.VENDOR_NAME%TYPE;
440 L_SUPPLIER_SITE_ID NUMBER;
441 L_SUPPLIER_SITE_NAME AP_SUPPLIER_SITES_ALL.VENDOR_SITE_CODE%TYPE:=NULL;
442 L_FROM_USER HZ_PARTIES.PARTY_NAME%TYPE;
443
444 X_VIEW_QUOTE_URL VARCHAR2(2000);
445 X_NOTIFICATION_ID NUMBER;
446 L_NOTIF_SUBSCR_FLAG VARCHAR2(1);
447
448 L_RECORD_STATUS VARCHAR2(1);
449
450 BEGIN
451
452 l_itemtype:='PONWDRAW';
453 l_itemkey:=To_Char(l_bid_number)||'-'||To_Char(l_auction_header_id);
454 l_process:='RESPONSE_WITH_DRAW';
455
456 /* Document Type */
457
458 SELECT FL.LOOKUP_CODE,FL.MEANING INTO L_DOCUMENT_TYPE_CODE,L_DOCUMENT_TYPE
459 FROM
460 PON_AUCTION_HEADERS_ALL PAH,
461 PON_AUC_DOCTYPES DOC,
462 FND_LOOKUPS FL
463 WHERE
464 PAH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
465 AND
466 PAH.DOCTYPE_ID=DOC.DOCTYPE_ID
467 AND
468 FL.LOOKUP_TYPE='PON_AUCTION_DOC_TYPES'
469 AND
470 FL.LOOKUP_CODE = DOC.INTERNAL_NAME ;
471
472 /* Now check the notification subscription */
473
474 L_NOTIF_SUBSCR_FLAG:='N';
475
476 IF L_DOCUMENT_TYPE_CODE='SOLICITATION'
477 THEN
478
479 SELECT Nvl(SOL_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
480 FROM
481 PON_NOTIF_SUBSCRIPTION_GROUPS
482 WHERE
483 NOTIF_GROUP_CODE='B_OFFER_WITH_DRAW'
484 AND
485 NOTIF_GROUP_TYPE='TO_BUYER'
486 AND
487 ROWNUM=1;
488 END IF;
489
490 IF L_DOCUMENT_TYPE_CODE='BUYER_AUCTION'
491 THEN
492
493 SELECT Nvl(AUCTION_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
494 FROM
495 PON_NOTIF_SUBSCRIPTION_GROUPS
496 WHERE
497 NOTIF_GROUP_CODE='B_OFFER_WITH_DRAW'
498 AND
499 NOTIF_GROUP_TYPE='TO_BUYER'
500 AND
501 ROWNUM=1;
502 END IF;
503 IF L_DOCUMENT_TYPE_CODE='REQUEST_FOR_INFORMATION'
504 THEN
505
506 SELECT Nvl(RFI_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
507 FROM
508 PON_NOTIF_SUBSCRIPTION_GROUPS
509 WHERE
510 NOTIF_GROUP_CODE='B_OFFER_WITH_DRAW'
511 AND
512 NOTIF_GROUP_TYPE='TO_BUYER'
513 AND
514 ROWNUM=1;
515 END IF;
516 IF L_DOCUMENT_TYPE_CODE='REQUEST_FOR_QUOTE'
517 THEN
518
519 SELECT Nvl(RFQ_SUBSCRIPTION_FLAG,'N') INTO L_NOTIF_SUBSCR_FLAG
520 FROM
521 PON_NOTIF_SUBSCRIPTION_GROUPS
522 WHERE
523 NOTIF_GROUP_CODE='B_OFFER_WITH_DRAW'
524 AND
525 NOTIF_GROUP_TYPE='TO_BUYER'
526 AND
527 ROWNUM=1;
528 END IF;
529 IF L_NOTIF_SUBSCR_FLAG='Y'
530 THEN
531
532 wf_engine.CreateProcess(itemtype => l_itemtype,
533 itemkey => l_itemkey,
534 process => l_process);
535
536 SELECT PAH.TRADING_PARTNER_CONTACT_NAME,PAH.DOCUMENT_NUMBER,PAH.AUCTION_TITLE,PAH.TRADING_PARTNER_ID,
537 PAH.VIEW_BY_DATE,PAH.OPEN_BIDDING_DATE,PAH.CLOSE_BIDDING_DATE,PAH.TRADING_PARTNER_CONTACT_ID,
538 PAH.AUCTION_HEADER_ID_ORIG_AMEND
539 INTO L_TRADING_PARTNER_CONTACT_NAME,L_DOCUMENT_NUMBER,L_AUCTION_TITLE,L_TRADING_PARTNER_ID,
540 L_PREVIEW_DATE,L_START_DATE,L_END_DATE,L_TRADING_PARTNER_CONTACT_ID,
541 L_AUCTION_HEADER_ID_ORIG_AMEND
542 FROM
543 PON_AUCTION_HEADERS_ALL PAH WHERE PAH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID;
544
545 SELECT PBH.TRADING_PARTNER_CONTACT_NAME INTO L_FROM_USER
546 FROM PON_BID_HEADERS PBH
547 WHERE
548 PBH.BID_NUMBER=L_BID_NUMBER
549 AND
550 PBH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
551 AND
552 ROWNUM<2;
553
554
555
556
557 IF L_DOCUMENT_TYPE_CODE IN ('SOLICITATION')
558 THEN
559
560 wf_engine.SetItemAttrText (itemtype => l_itemtype,
561 itemkey => l_itemkey,
562 aname => 'DOCUMENT_TYPE',
563 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUC_NEG_X'));
564
565 wf_engine.SetItemAttrText (itemtype => l_itemtype,
566 itemkey => l_itemkey,
567 aname => 'RESPONSE_TYPE',
568 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_X')||' '||L_BID_NUMBER);
569
570 wf_engine.SetItemAttrText (itemtype => l_itemtype,
571 itemkey => l_itemkey,
572 aname => 'RESPONSE',
573 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_X'));
574
575
576 ELSE
577
578 wf_engine.SetItemAttrText (itemtype => l_itemtype,
579 itemkey => l_itemkey,
580 aname => 'DOCUMENT_TYPE',
581 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUC_NEG'));
582
583 wf_engine.SetItemAttrText (itemtype => l_itemtype,
584 itemkey => l_itemkey,
585 aname => 'RESPONSE_TYPE',
586 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_R')||' '||L_BID_NUMBER);
587
588 wf_engine.SetItemAttrText (itemtype => l_itemtype,
589 itemkey => l_itemkey,
590 aname => 'RESPONSE',
591 avalue => FND_MESSAGE.GET_STRING('PON','PON_AUCTS_BID_R'));
592
593 END IF;
594
595 wf_engine.SetItemAttrText (itemtype => l_itemtype,
596 itemkey => l_itemkey,
597 aname => 'SOURCE_DOC_TYPE',
598 avalue => L_DOCUMENT_TYPE||' '||L_DOCUMENT_NUMBER);
599
600
601 wf_engine.SetItemAttrText (itemtype => l_itemtype,
602 itemkey => l_itemkey,
603 aname => 'RECEIVER',
604 avalue => L_TRADING_PARTNER_CONTACT_NAME);
605
606 SELECT HZ.PARTY_NAME INTO L_PREPARER_TP_NAME
607 FROM
608 HZ_PARTIES HZ
609 WHERE
610 PARTY_ID=L_TRADING_PARTNER_ID;
611
612 wf_engine.SetItemAttrText (itemtype => l_itemtype,
613 itemkey => l_itemkey,
614 aname => 'PREPARER_TP_NAME',
615 avalue => L_PREPARER_TP_NAME);
616
617 wf_engine.SetItemAttrText (itemtype => l_itemtype,
618 itemkey => l_itemkey,
619 aname => 'DOC_NUMBER',
620 avalue => L_DOCUMENT_NUMBER);
621
622 wf_engine.SetItemAttrText (itemtype => l_itemtype,
623 itemkey => l_itemkey,
624 aname => 'AUCTION_TITLE',
625 avalue => L_AUCTION_TITLE);
626
627 wf_engine.SetItemAttrText (itemtype => l_itemtype,
628 itemkey => l_itemkey,
629 aname => 'SUB_AUCTION_TITLE',
630 avalue => L_AUCTION_TITLE);
631
632 wf_engine.SetItemAttrText (itemtype => l_itemtype,
633 itemkey => l_itemkey,
634 aname => 'ORIGIN_USER_NAME',
635 avalue => L_FROM_USER);
636
637 /* Response URL Bug Chnages */
638 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
639 itemkey => l_itemkey,
640 aname => 'BID_ID',
641 avalue => L_BID_NUMBER);
642
643 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
644 itemkey => l_itemkey,
645 aname => 'AUCTION_ID',
646 avalue => L_AUCTION_HEADER_ID);
647
648
649 /* Preview Date,Open Date,Close Date,Withdrawn Date */
650
651 IF L_PREVIEW_DATE IS NULL THEN
652 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
653 itemkey => l_itemkey,
654 aname => 'NEG_PREVIEW_DATE',
655 avalue => NULL);
656 ELSE
657 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
658 itemkey => l_itemkey,
659 aname => 'NEG_PREVIEW_DATE',
660 avalue => L_PREVIEW_DATE);
661 END IF;
662
663
664 IF L_START_DATE IS NULL THEN
665 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
666 itemkey => l_itemkey,
667 aname => 'NEG_OPEN_DATE',
668 avalue => NULL);
669 ELSE
670 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
671 itemkey => l_itemkey,
672 aname => 'NEG_OPEN_DATE',
673 avalue => L_START_DATE);
674 END IF;
675
676 IF L_END_DATE IS NULL THEN
677
678 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
679 itemkey => l_itemkey,
680 aname => 'NEG_CLOSE_DATE',
681 avalue => NULL);
682 ELSE
683
684 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
685 itemkey => l_itemkey,
686 aname => 'NEG_CLOSE_DATE',
687 avalue => L_END_DATE);
688 END IF;
689
690 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
691 itemkey => l_itemkey,
692 aname => 'NEG_WITH_DRAWN_DATE',
693 avalue => SYSDATE);
694
695 /* Supplier, Supplier Site */
696
697 SELECT PBH.TRADING_PARTNER_NAME,PBH.VENDOR_SITE_ID
698 INTO L_SUPPLIER,L_SUPPLIER_SITE_ID
699 FROM
700 PON_BID_HEADERS PBH
701 WHERE
702 PBH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
703 AND
704 PBH.BID_NUMBER=L_BID_NUMBER;
705
706 wf_engine.SetItemAttrText (itemtype => l_itemtype,
707 itemkey => l_itemkey,
708 aname => 'SUPPLIER',
709 avalue => L_SUPPLIER);
710
711 IF L_SUPPLIER_SITE_ID IS NOT NULL AND L_SUPPLIER_SITE_ID <> -1
712 THEN
713
714 SELECT APS.VENDOR_SITE_CODE INTO L_SUPPLIER_SITE_NAME
715 FROM
716 AP_SUPPLIER_SITES_ALL APS
717 WHERE
718 APS.VENDOR_SITE_ID=L_SUPPLIER_SITE_ID;
719
720 END IF;
721
722 wf_engine.SetItemAttrText (itemtype => l_itemtype,
723 itemkey => l_itemkey,
724 aname => 'SUPPLIER_SITE',
725 avalue => L_SUPPLIER_SITE_NAME);
726
727 wf_engine.SetItemAttrText (itemtype => l_itemtype,
728 itemkey => l_itemkey,
729 aname => 'WITHDRAW_REASON',
730 avalue => L_WITHDRAW_REASON);
731
732 x_view_quote_url:=pon_wf_utl_pkg.get_dest_page_url (p_dest_func => 'PONRESENQ_VIEWBID',p_notif_performer => 'BUYER');
733
734 Begin
735 SELECT notification_id INTO x_notification_id from
736 WF_ITEM_ACTIVITY_STATUSES WHERE ITEM_TYPE=l_itemtype
737 AND ITEM_KEY=l_itemkey
738 AND ASSIGNED_USER IS NOT NULL
739 AND ROWNUM<=1;
740 EXCEPTION
741 WHEN No_Data_Found THEN
742 x_notification_id:=NULL;
743 WHEN OTHERS THEN
744 NULL;
745 END;
746 IF(x_notification_id IS NOT null) THEN
747 x_view_quote_url:=REPLACE(x_view_quote_url,'&#NID',x_notification_id);
748 END IF;
749
750 wf_engine.SetItemAttrText (itemtype => l_itemtype,
751 itemkey => l_itemkey,
752 aname => 'URL',
753 avalue => x_view_quote_url);
754
755
756 wf_engine.StartProcess(itemtype => l_itemtype,
757 itemkey => l_itemkey );
758
759 END IF;
760
761 UPDATE PON_BID_HEADERS
762 SET
763 WITHDRAW_REASON=L_WITHDRAW_REASON,
764 BID_STATUS='ARCHIVED'
765 WHERE
766 AUCTION_HEADER_ID=L_AUCTION_HEADER_ID
767 AND
768 BID_NUMBER=L_BID_NUMBER;
769
770 /* Record an Entry in Supplier Activities table for Withdraw Action */
771
772 SELECT PBH.TRADING_PARTNER_ID,PBH.TRADING_PARTNER_CONTACT_ID,
773 PAH.AUCTION_HEADER_ID_ORIG_AMEND
774 INTO L_BID_TRADING_PARTNER_ID,L_BID_TRADPART_CNTC_ID,
775 L_AUCTION_HEADER_ID_ORIG_AMEND
776 FROM PON_BID_HEADERS PBH,
777 PON_AUCTION_HEADERS_ALL PAH
778 WHERE
779 PAH.AUCTION_HEADER_ID = L_AUCTION_HEADER_ID AND
780 PBH.AUCTION_HEADER_ID=L_AUCTION_HEADER_ID AND
781 PBH.BID_NUMBER=L_BID_NUMBER;
782
783 PON_CONSOLE_PVT.record_supplier_activity(
784 L_AUCTION_HEADER_ID ,
785 L_AUCTION_HEADER_ID_ORIG_AMEND ,
786 L_BID_TRADING_PARTNER_ID ,
787 L_BID_TRADPART_CNTC_ID ,
788 userenv('SESSIONID') ,
789 'WITHDRAW_OFFER' ,
790 L_BID_NUMBER,
791 L_RECORD_STATUS ) ;
792
793 /* End */
794
795 COMMIT;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 WF_CORE.CONTEXT('SEND_WDRAW_BY_SUPPLIER_NOTIF','SEND_WDRAW_BY_SUPPLIER_NOTIF',L_ITEMTYPE,L_ITEMKEY);
800 WF_CORE.RAISE('ERROR_NAME');
801 RAISE_APPLICATION_ERROR(-20041, 'FAILURE AT STEP ' , TRUE);
802 END;
803
804 END PON_WITH_DRAW_PKG;