[Home] [Help]
PACKAGE BODY: APPS.PON_REMINDER_NOTIFICATION_PKG
Source
1 PACKAGE BODY pon_reminder_notification_pkg AS
2 --$Header: PONSREMB.pls 120.1.12010000.1 2009/06/23 08:58:10 appldev noship $
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'reminder_notification';
5 PON_SEND_NOTIF CONSTANT VARCHAR2(10) := 'PON_NOT';
6
7 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8 g_module CONSTANT VARCHAR2(50) := 'pon.plsql.pon_reminder_notification_pkg';
9 g_debug_enabled VARCHAR2(1) := 'N';
10
11 g_before_close_date CONSTANT VARCHAR2(80) := 'BEFORE_CLOSE_DATE';
12 g_after_open_date CONSTANT VARCHAR2(80) := 'AFTER_OPEN_DATE';
13 g_after_preview_date CONSTANT VARCHAR2(80) := 'AFTER_PREVIEW_DATE';
14
15
16 --TYPE t_number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
17
18
19 PROCEDURE send_notification_wrapper(
20 ErrCode number,
21 ErrMesg Varchar2,
22 p_send_when IN VARCHAR2,
23 p_days_values IN VARCHAR2
24 ) IS
25
26
27
28 p_trimmed_days_values VARCHAR2(255);
29
30 days_values_table t_number_table_type ;
31
32 count_elements NUMBER;
33 pointer NUMBER;
34 comma_position NUMBER;
35 temp_day_value VARCHAR2(10);
36
37 temp_num NUMBER;
38
39 l_purge_done BOOLEAN;
40
41 l_api_name VARCHAR2(100) := ' send_notification_wrapper ';
42 l_progress NUMBER := 0;
43 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
44
45 BEGIN
46 IF(g_fnd_debug = 'Y') then
47
48 if (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
49 g_debug_enabled := 'Y';
50 end if;
51
52 END IF;
53
54 IF(g_debug_enabled = 'Y') then
55 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
56 log_message(x_progress);
57 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : '
58 || 'p_send_when : ' || p_send_when
59 || 'p_days_values' || p_days_values;
60 log_message(x_progress);
61 END IF;
62
63
64 IF(g_debug_enabled = 'Y') then
65 x_progress := ++l_progress || l_api_name || ' : calling purge procedure for purging unused notifications.';
66 log_message(x_progress);
67 END IF;
68
69
70 -------------------
71 -- purge logic for the unused workflows
72 purge_notif_wf(TRUE, l_purge_done);
73
74 ---------------------
75
76 IF(g_debug_enabled = 'Y') then
77 x_progress := ++l_progress || l_api_name || ' : begin analysing of comma separated values passed from the conc program';
78 log_message(x_progress);
79 END IF;
80
81 p_trimmed_days_values := Trim(p_days_values);
82 count_elements := 0;
83 pointer := 1;
84
85 IF(g_debug_enabled = 'Y') then
86 x_progress := ++l_progress || l_api_name || ' : values added in the table of numbers';
87 log_message(x_progress);
88 END IF;
89
90
91
92 WHILE(Length(p_trimmed_days_values) > 0)
93 LOOP
94 comma_position := InStr(p_trimmed_days_values, ',', 1, 1);
95
96 IF (comma_position = 0)
97 THEN
98 comma_position := Length(p_trimmed_days_values) + 1;
99 END IF;
100
101 temp_day_value := Trim(SubStr(p_trimmed_days_values, 1, comma_position - 1 ));
102 p_trimmed_days_values := SubStr(p_trimmed_days_values, comma_position + 1 );
103
104 IF(temp_day_value IS NOT NULL)THEN
105 BEGIN
106 days_values_table(count_elements + 1) := To_Number(temp_day_value);
107 IF(g_debug_enabled = 'Y') then
108 x_progress := ++l_progress || l_api_name
109 || 'value no. ' || count_elements + 1
110 || ' : ' || days_values_table(count_elements + 1);
111 log_message(x_progress);
112 END IF;
113 EXCEPTION WHEN OTHERS THEN
114 NULL;
115 END;
116
117 temp_num := days_values_table(count_elements + 1);
118 count_elements := count_elements + 1;
119
120
121
122 END IF;
123
124 END LOOP;
125 IF(g_debug_enabled = 'Y') then
126 x_progress := ++l_progress || l_api_name || ' : CSV converted to table of numbers';
127 log_message(x_progress);
128 END IF;
129 --now days_values_table has the required values of number of days
130
131 IF(g_debug_enabled = 'Y') then
132 x_progress := ++l_progress || l_api_name || ' : calling send_notification procedure with the table of numbers';
133 log_message(x_progress);
134 END IF;
135 send_notification(To_Char(p_send_when), days_values_table);
136
137 IF(g_debug_enabled = 'Y') then
138 x_progress := ++l_progress || l_api_name || ' : END :';
139 log_message(x_progress);
140 END IF;
141
142 EXCEPTION WHEN OTHERS THEN
143 IF(g_debug_enabled = 'Y') then
144 x_progress := l_api_name|| 'Exception Found. Error Code : ' || ErrCode || ' Error Name : ' || ErrMesg;
145 log_message(x_progress);
146 END IF;
147
148
149 END send_notification_wrapper;
150
151 --*************************************************************************************
152
153 PROCEDURE send_notification(
154 p_send_when IN VARCHAR2,
155 p_days_values_number_table t_number_table_type
156 ) IS
157
158 l_check_exists NUMBER;
159 l_diff_days NUMBER;
160 found_flag BOOLEAN;
161
162 l_temp_sys_date pon_auction_headers_all.close_bidding_date%TYPE;
163
164 CURSOR active_neg_cursor IS
165 SELECT pah.auction_header_id auction_header_id,
166 pah.close_bidding_date close_bidding_date,
167 pah.open_bidding_date open_bidding_date,
168 pah.view_by_date preview_date
169 FROM pon_auction_headers_all pah
170 WHERE pah.auction_status = 'ACTIVE'
171 AND pah.close_bidding_date > SYSDATE
172 AND SYSDATE > pah.open_bidding_date;
173
174 c_neg_details active_neg_cursor%ROWTYPE;
175
176 l_api_name VARCHAR2(100) := ' send_notification ';
177 l_progress NUMBER := 0;
178 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
179
180 BEGIN
181 IF(g_debug_enabled = 'Y') then
182 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
183 log_message(x_progress);
184 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS : '
185 || 'p_send_when : ' || p_send_when
186 || 'p_days_values_number_table : table of numbers';
187 log_message(x_progress);
188 END IF;
189
190
191 OPEN active_neg_cursor;
192 LOOP
193
194 fetch active_neg_cursor into c_neg_details;
195 EXIT when active_neg_cursor%NOTFOUND;
196
197 l_temp_sys_date := SYSDATE;
198
199
200 IF p_send_when = g_before_close_date
201 THEN
202 SELECT Floor(c_neg_details.close_bidding_date - l_temp_sys_date) INTO l_diff_days
203 FROM dual;
204 ELSIF p_send_when = g_after_open_date
205 THEN
206 SELECT Floor(l_temp_sys_date - c_neg_details.open_bidding_date) INTO l_diff_days
207 FROM dual;
208 ELSIF p_send_when = g_after_preview_date
209 THEN
210 SELECT Floor(l_temp_sys_date - c_neg_details.preview_date) INTO l_diff_days
211 FROM dual;
212 END IF;
213
214 IF(g_debug_enabled = 'Y') then
215 x_progress := ++l_progress || l_api_name || ' : difference of days calculated, l_diff_days = ' || l_diff_days;
216 log_message(x_progress);
217 END IF;
218
219 BEGIN
220 found_flag := FALSE;
221 FOR i IN p_days_values_number_table.first .. p_days_values_number_table.last
222 LOOP
223 IF (p_days_values_number_table(i) = l_diff_days)
224 THEN
225 BEGIN
226 found_flag := TRUE;
227 EXIT;
228 END;
229 END IF;
230
231 END LOOP;
232
233 IF found_flag = TRUE
234 THEN
235 BEGIN
236
237 IF(g_debug_enabled = 'Y') then
238 x_progress := ++l_progress || l_api_name || ' : calling call_wf_process_to_send_notif for auction_id : ' || c_neg_details.auction_header_id ;
239 log_message(x_progress);
240 END IF;
241 BEGIN
242 call_wf_process_to_send_notif(c_neg_details.auction_header_id);
243
244 --update pon_auction_headers_all
245 --increment the value of the no-of-notification-sent column
246 UPDATE pon_auction_headers_all pah
247 SET pah.no_of_notifications_sent = Nvl(pah.no_of_notifications_sent, 0) + 1
248 WHERE auction_header_id = c_neg_details.auction_header_id;
249
250 EXCEPTION WHEN OTHERS THEN
251 IF(g_debug_enabled = 'Y') then
252 x_progress := ++l_progress || l_api_name || ' : exception found in the subsequent procedure calls :';
253 log_message(x_progress);
254 END IF;
255
256 END;
257
258 END;
259 END IF;
260
261 END;
262 -- code
263
264 END LOOP;
265
266 IF(g_debug_enabled = 'Y') then
267 x_progress := ++l_progress || l_api_name || ' : END :';
268 log_message(x_progress);
269 END IF;
270
271 END send_notification;
272 --*************************************************************************************
273
274
275 PROCEDURE call_wf_process_to_send_notif(
276 p_auction_header_id IN NUMBER
277 )
278 IS
279
280 TYPE l_TP_contact_id_table_type IS TABLE OF pon_bidding_parties.trading_partner_contact_id%TYPE;
281 TYPE l_TP_contact_name_table_type IS TABLE OF pon_bidding_parties.trading_partner_contact_name%TYPE;
282 TYPE l_TP_name_table_type IS TABLE OF pon_bidding_parties.trading_partner_name%TYPE;
283 TYPE l_vendor_site_code_table_type IS TABLE OF pon_bidding_parties.vendor_site_code%TYPE;
284 TYPE l_vendor_site_id_table_type IS TABLE OF pon_bidding_parties.vendor_site_id%TYPE;
285 TYPE l_add_con_email_table_type IS TABLE OF pon_bidding_parties.additional_contact_email%TYPE;
286 TYPE l_TP_id_table_type IS TABLE OF pon_bidding_parties.trading_partner_id%TYPE;
287
288 l_TP_contact_id_table l_TP_contact_id_table_type := l_TP_contact_id_table_type(NULL);
289 l_TP_contact_name_table l_TP_contact_name_table_type := l_TP_contact_name_table_type(NULL);
290 l_TP_name_table l_TP_name_table_type := l_TP_name_table_type(NULL);
291 l_vendor_site_code_table l_vendor_site_code_table_type := l_vendor_site_code_table_type(NULL);
292 l_vendor_site_id_table l_vendor_site_id_table_type := l_vendor_site_id_table_type(NULL);
293 l_add_con_email_table l_add_con_email_table_type := l_add_con_email_table_type(NULL);
294 l_TP_id_table l_TP_id_table_type := l_TP_id_table_type(NULL);
295
296 p_wf_item_key VARCHAR2(240);
297
298 l_api_name VARCHAR2(100) := ' call_wf_process_to_send_notif ';
299 l_progress NUMBER := 0;
300 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
301
302 --------------- vars reqd to call wflow
303
304 p_trading_partner_contact_name VARCHAR2(80);
305 p_trading_partner_name VARCHAR2(80);
306 p_auction_title VARCHAR2(80);
307 p_reminder_date DATE;
308 p_neg_preview_date DATE;
309 p_neg_open_date DATE;
310 p_neg_close_date DATE;
311 p_supplier_name VARCHAR2(255);
312 p_supplier_id NUMBER;
313 p_supplier_contact_name VARCHAR2(225);
314 p_supplier_role_name VARCHAR2(80);
315 p_supplier_site VARCHAR2(80);
316 p_item_key VARCHAR2(350);
317 p_notification_no NUMBER;
318 p_supplier_site_id NUMBER;
319 p_person_id NUMBER(10);
320 p_name VARCHAR2(244);
321
322 p_add_con_email VARCHAR(240);
323 p_add_con_role_name VARCHAR2(240);
324 p_supplier_contact_id NUMBER;
325 p_document_number VARCHAR2(240);
326
327 ------------------
328
329 BEGIN
330
331 IF(g_debug_enabled = 'Y') then
332 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
333 log_message(x_progress);
334 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS:'
335 || 'auction header id : ' || p_auction_header_id ;
336 log_message(x_progress);
337 END IF;
338
339 SELECT pbp.trading_partner_contact_id,
340 pbp.trading_partner_contact_name,
341 pbp.vendor_site_code,
342 pbp.trading_partner_name,
343 pbp.trading_partner_id,
344 pbp.vendor_site_id,
345 pbp.additional_contact_email
346
347 BULK COLLECT INTO l_TP_contact_id_table,
348 l_TP_contact_name_table,
349 l_vendor_site_code_table,
350 l_TP_name_table,
351 l_TP_id_table,
352 l_vendor_site_id_table,
353 l_add_con_email_table
354
355 FROM pon_bidding_parties pbp
356
357 WHERE
358 pbp.auction_header_id = p_auction_header_id
359 AND pbp.trading_partner_contact_id NOT IN
360 (SELECT pbh.trading_partner_contact_id
361 FROM pon_bid_headers pbh
362 WHERE pbh.auction_header_id = p_auction_header_id
363 AND pbh.bid_status = 'ACTIVE'
364 );
365
366
367
368 --get the values from the pon_auction_headers_all table
369 SELECT
370 auction_title,
371 trading_partner_name,
372 open_bidding_date,
373 close_bidding_date,
374 view_by_date,
375 wf_item_key,
376 Nvl(no_of_notifications_sent, 0) + 1,
377 document_number
378 INTO
379 p_auction_title,
380 p_trading_partner_name,
381 p_neg_open_date,
382 p_neg_close_date,
383 p_neg_preview_date,
384 p_wf_item_key,
385 p_notification_no,
386 p_document_number
387
388 FROM pon_auction_headers_all
389 WHERE auction_header_id = p_auction_header_id;
390
391
392 --get the name of the buyer
393 BEGIN
394 SELECT person_id
395 INTO p_person_id
396 FROM per_all_people_f
397 WHERE party_id = (
398 SELECT trading_partner_contact_id
399 FROM pon_auction_headers_all
400 WHERE auction_header_id = p_auction_header_id
401 )
402 AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
403
404 EXCEPTION WHEN OTHERS THEN
405 IF(g_debug_enabled = 'Y') THEN
406 x_progress := ++l_progress || l_api_name || ' : exception while finding buyer name :';
407 log_message(x_progress);
408 END IF;
409 RAISE;
410 END;
411
412
413
414 wf_directory.GetUserName('PER',
415 p_person_id,
416 p_name,
417 p_trading_partner_contact_name);
418 --set the sending date as the sysdate
419 p_reminder_date := SYSDATE;
420
421 IF l_TP_contact_id_table.first > 0 THEN
422 BEGIN
423
424 FOR i IN l_TP_contact_id_table.first .. l_TP_contact_id_table.last
425 LOOP
426 p_supplier_contact_id := l_TP_contact_id_table(i);
427 p_supplier_contact_name := l_TP_contact_name_table(i);
428 p_supplier_name := l_TP_name_table(i);
429 p_supplier_id := l_TP_id_table(i);
430 p_supplier_site := l_vendor_site_code_table(i);
431 p_supplier_site_id := l_vendor_site_id_table(i);
432 --if vendor site id is -1, change site code to null
433
434 IF p_supplier_site_id = -1 THEN
435 p_supplier_site := '';
436 END IF;
437
438 p_add_con_email := l_add_con_email_table(i);
439
440 IF p_supplier_contact_id IS NOT NULL THEN
441
442 BEGIN
443 SELECT USER_NAME INTO p_supplier_role_name FROM FND_USER
444 WHERE person_party_id = l_TP_contact_id_table(i)
445 AND SYSDATE BETWEEN start_date AND nvl(end_date,SYSDATE+1);
446 EXCEPTION
447 WHEN TOO_MANY_ROWS THEN
448 IF(g_debug_enabled = 'Y') then
449 x_progress := ++l_progress || l_api_name || ': too many user name found for the supplier :';
450 log_message(x_progress);
451 END IF;
452
453 SELECT USER_NAME
454 INTO p_supplier_role_name
455 FROM FND_USER
456 WHERE person_party_id = l_TP_contact_id_table(i)
457 AND SYSDATE BETWEEN start_date AND nvl(end_date,SYSDATE+1)
458 AND ROWNUM = 1;
459
460 END;
461
462
463 --create the item key for the workflow
464 p_item_key := p_wf_item_key||'_'||l_TP_contact_id_table(i)||'_'||p_auction_header_id
465 ||'_' ||to_char(sysdate, 'JSSSSS')||dbms_random.value;
466
467 IF(g_debug_enabled = 'Y') then
468 x_progress := ++l_progress || l_api_name || ' : starting workflow process'
469 || 'auction header id' || p_auction_header_id;
470 log_message(x_progress);
471 END IF;
472
473
474 start_wf_process(p_auction_header_id,
475 p_trading_partner_contact_name,
476 p_trading_partner_name,
477 p_auction_title,
478 p_reminder_date,
479 p_neg_preview_date,
480 p_neg_open_date,
481 p_neg_close_date,
482 p_supplier_name,
483 p_supplier_contact_name,
484 p_supplier_role_name,
485 p_supplier_site,
486 p_item_key,
487 p_notification_no,
488 p_supplier_site_id,
489 p_document_number
490 );
491 END IF;
492 IF p_add_con_email IS NOT NULL THEN
493
494 IF(g_debug_enabled = 'Y') then
495 x_progress := ++l_progress || l_api_name || ' : additional contact email is not null : ';
496 log_message(x_progress);
497 END IF;
498
499
500 BEGIN
501 SELECT wf_user_name
502 INTO
503 p_add_con_role_name
504 FROM pon_bidding_parties
505 WHERE auction_header_id = p_auction_header_id
506 AND trading_partner_id = p_supplier_id
507 AND Nvl(vendor_site_id,-1) = p_supplier_site_id;
508
509 EXCEPTION WHEN OTHERS THEN
510 IF(g_debug_enabled = 'Y') then
511 x_progress := ++l_progress || l_api_name || ' : role name not found : ';
512 log_message(x_progress);
513 END IF;
514 RAISE;
515 END;
516
517 p_item_key := p_wf_item_key||'_'||p_add_con_role_name||'_'
518 ||p_auction_header_id||'_' ||to_char(sysdate, 'JSSSSS')||dbms_random.value;
519
520
521 IF(g_debug_enabled = 'Y') then
522 x_progress := ++l_progress || l_api_name || ' : starting workflow process'
523 || 'auction header id' || p_auction_header_id;
524 log_message(x_progress);
525 END IF;
526
527 --start workflow id add_con user name
528 start_wf_process(p_auction_header_id,
529 p_trading_partner_contact_name,
530 p_trading_partner_name,
531 p_auction_title,
532 p_reminder_date,
533 p_neg_preview_date,
534 p_neg_open_date,
535 p_neg_close_date,
536 p_supplier_name,
537 p_supplier_contact_name,
538 p_add_con_role_name,
539 p_supplier_site,
540 p_item_key,
541 p_notification_no,
542 p_supplier_site_id,
543 p_document_number
544 );
545 END IF;
546
547 END LOOP;
548 END;
549 END IF;
550
551 IF(g_debug_enabled = 'Y') then
552 x_progress := ++l_progress || l_api_name || ' : END :';
553 log_message(x_progress);
554 END IF;
555
556 END call_wf_process_to_send_notif;
557 --*************************************************************************************
558
559 PROCEDURE start_wf_process(
560 p_auction_header_id IN NUMBER,
561 p_trading_partner_contact_name IN VARCHAR2,
562 p_trading_partner_name IN VARCHAR2,
563 p_auction_title IN VARCHAR2,
564 p_reminder_date IN DATE,
565 p_neg_preview_date IN DATE,
566 p_neg_open_date IN DATE,
567 p_neg_close_date IN DATE,
568 p_supplier_name IN VARCHAR2,
569 p_supplier_contact_name IN VARCHAR2,
570 p_supplier_role_name IN VARCHAR2,
571 p_supplier_site IN VARCHAR2,
572 p_item_key IN VARCHAR2,
573 p_notification_no IN NUMBER,
574 p_supplier_site_id IN NUMBER,
575 p_document_number IN VARCHAR2
576 )
577 IS
578
579 x_neg_summary_url_supplier VARCHAR2(2000);
580
581 l_api_name VARCHAR2(100) := ' start_wf_process ';
582 l_progress NUMBER := 0;
583 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
584
585 l_server_timezone VARCHAR2(80);
586 l_user_timezone VARCHAR2(80);
587 l_user_timezone_desc VARCHAR2(240);
588
589 l_language_code VARCHAR2(5);
590
591 l_neg_open_date_tz DATE;
592 l_neg_close_date_tz DATE;
593 l_neg_preview_date_tz DATE;
594 l_reminder_date_tz DATE;
595
596 l_user_name VARCHAR2(80);
597
598
599
600 BEGIN
601
602
603
604 IF(g_debug_enabled = 'Y') then
605 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
606
607 x_progress := ++l_progress || l_api_name || ' : IN PARAMETERS: all parameters required to start the workflow'
608 || p_auction_header_id || ', ' || p_trading_partner_contact_name
609 || ', ' || p_trading_partner_name || ', ' || p_auction_title || ', '
610 || p_reminder_date || ', ' || p_neg_preview_date || ', '
611 || p_neg_open_date || ', ' || p_neg_close_date || ', '
612 || p_supplier_name || ', ' || p_supplier_contact_name || ', '
613 || p_supplier_role_name || ', ' || p_supplier_site
614 || ', ' || p_item_key || ', ' || p_notification_no || ', ' || p_supplier_site_id
615 || ' item key for the workflow : ' || p_item_key;
616
617 log_message(x_progress);
618 END IF;
619 l_user_name := p_supplier_role_name;
620
621 -- set the db session language
622 PON_PROFILE_UTIL_PKG.get_wf_language(l_user_name, l_language_code);
623 PON_AUCTION_PKG.set_session_language(null, l_language_code);
624
625 --------------------------------
626 --get the user time zone based on the p_supplier_role_name
627
628 l_user_timezone := PON_AUCTION_PKG.get_time_zone(l_user_name);
629 l_server_timezone := PON_AUCTION_PKG.get_oex_time_zone;
630
631
632 IF (PON_OEX_TIMEZONE_PKG.valid_zone(l_user_timezone) = 1) THEN
633 BEGIN
634 l_neg_open_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(p_neg_open_date, l_server_timezone, l_user_timezone);
635 l_neg_close_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(p_neg_close_date, l_server_timezone, l_user_timezone);
636 l_reminder_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(p_reminder_date, l_server_timezone, l_user_timezone);
637 IF p_neg_preview_date IS NOT NULL THEN
638 l_neg_preview_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(p_neg_preview_date, l_server_timezone, l_user_timezone);
639 END IF;
640 END;
641 ELSE
642 BEGIN
643 l_user_timezone := l_server_timezone;
644 l_neg_open_date_tz := p_neg_open_date;
645 l_neg_close_date_tz := p_neg_close_date;
646 l_neg_preview_date_tz := p_neg_preview_date;
647 l_reminder_date_tz := p_reminder_date;
648 END;
649 END IF;
650
651 l_user_timezone_desc := PON_AUCTION_PKG.get_timezone_description(l_user_timezone, l_language_code);
652
653
654 wf_engine.createProcess(itemtype => PON_SEND_NOTIF,
655 itemkey => p_item_key,
656 process => 'SEND_NOTIF');
657
658 --1
659
660 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
661 itemkey => p_item_key,
662 aname => 'BUYER',
663 avalue => p_trading_partner_contact_name);
664 --2
665 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
666 itemkey => p_item_key,
667 aname => 'TRADING_PARTNER_NAME_BUYER',
668 avalue => p_trading_partner_name);
669 --3
670 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
671 itemkey => p_item_key,
672 aname => 'AUCTION_TITLE',
673 avalue => p_auction_title);
674 --4
675 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
676 itemkey => p_item_key,
677 aname => 'TP_CONTACT_NAME_SUPP',
678 avalue => p_supplier_contact_name);
679 --5
680 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
681 itemkey => p_item_key,
682 aname => 'TRADING_PARTNER_NAME_SUPP',
683 avalue => p_supplier_name);
684 --6
685 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
686 itemkey => p_item_key,
687 aname => 'SUPPLIER_SITE',
688 avalue => p_supplier_site);
689 --7
690 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
691 itemkey => p_item_key,
692 aname => 'AUCTION_HEADER_ID',
693 avalue => p_document_number);
694 --8
695 wf_engine.SetItemAttrDate(itemtype => PON_SEND_NOTIF,
696 itemkey => p_item_key,
697 aname => 'SENT_ON_DATE',
698 avalue => l_reminder_date_tz);
699 --9
700 wf_engine.SetItemAttrDate(itemtype => PON_SEND_NOTIF,
701 itemkey => p_item_key,
702 aname => 'PREVIEW_DATE',
703 avalue => l_neg_preview_date_tz);
704 --10
705 wf_engine.SetItemAttrDate(itemtype => PON_SEND_NOTIF,
706 itemkey => p_item_key,
707 aname => 'OPEN_DATE',
708 avalue => l_neg_open_date_tz);
709 --11
710 wf_engine.SetItemAttrDate(itemtype => PON_SEND_NOTIF,
711 itemkey => p_item_key,
712 aname => 'CLOSE_DATE',
713 avalue => l_neg_close_date_tz);
714 --12
715
716
717 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
718 itemkey => p_item_key,
719 aname => 'SUPPLIER_TO_SEND_NOTIF',
720 avalue => p_supplier_role_name);
721
722
723 --13
724 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
725 itemkey => p_item_key,
726 aname => 'NOTIFICATION_NO',
727 avalue => p_notification_no);
728
729 -- Get the supplier dest URL
730 x_neg_summary_url_supplier := pon_wf_utl_pkg.get_dest_page_url (
731 p_dest_func => 'PON_NEG_SUMMARY'
732 ,p_notif_performer => 'SUPPLIER');
733 --14
734 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
735 itemkey => p_item_key,
736 aname => 'PON_NEG_SUMMARY_DEST_URL',
737 avalue => x_neg_summary_url_supplier);
738
739
740
741 --15
742 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
743 itemkey => p_item_key,
744 aname => 'VENDOR_SITE_ID',
745 avalue => p_supplier_site_id);
746
747
748 --16
749 wf_engine.SetItemAttrNumber(itemtype => PON_SEND_NOTIF,
750 itemkey => p_item_key,
751 aname => 'AUCTION_ID',
752 avalue => p_auction_header_id);
753
754 --17
755 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
756 itemkey => p_item_key,
757 aname => 'USER_TIMEZONE',
758 avalue => l_user_timezone_desc);
759
760 --18
761 wf_engine.SetItemAttrText(itemtype => PON_SEND_NOTIF,
762 itemkey => p_item_key,
763 aname => 'SET_FROM_ROLE',
764 avalue => p_trading_partner_contact_name);
765
766
767 --start workflow
768 BEGIN
769 wf_engine.StartProcess(itemtype => PON_SEND_NOTIF,
770 itemkey => p_item_key);
771
772
773 EXCEPTION WHEN OTHERS THEN
774
775 IF(g_debug_enabled = 'Y') then
776 x_progress := ++l_progress || l_api_name || 'Exception : '|| SQLERRM ;
777 log_message(x_progress);
778 END IF;
779
780 END;
781
782
783
784 IF(g_debug_enabled = 'Y') then
785 x_progress := ++l_progress || l_api_name || ' : END :';
786 log_message(x_progress);
787 END IF;
788
789 END start_wf_process;
790 --*************************************************************************************
791
792 PROCEDURE purge_notif_wf(
793 p_start_purge IN BOOLEAN,
794 p_purge_done OUT NOCOPY BOOLEAN
795 ) IS
796
797 l_item_key_like VARCHAR2(255);
798 l_exact_item_key VARCHAR2(255);
799
800 TYPE t_auction_header_id_type IS TABLE OF PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE;
801 TYPE t_wf_item_key_type IS TABLE OF PON_AUCTION_HEADERS_ALL.wf_item_key%TYPE;
802 TYPE t_exact_item_key_type is TABLE OF WF_ITEMS.item_key%TYPE;
803
804 t_auction_header_id t_auction_header_id_type := t_auction_header_id_type(null);
805 t_wf_item_key t_wf_item_key_type := t_wf_item_key_type(null);
806 t_exact_item_key t_exact_item_key_type := t_exact_item_key_type(null);
807
808 l_api_name VARCHAR2(100) := ' purge_notif_wf ';
809 l_progress NUMBER := 0;
810 x_progress FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
811
812 BEGIN
813
814 IF(g_debug_enabled = 'Y') then
815 x_progress := ++l_progress || l_api_name || ' : BEGIN :';
816 log_message(x_progress);
817 END IF;
818
819 SELECT
820 pah.auction_header_id auction_header_id,
821 pah.wf_item_key
822
823 BULK COLLECT INTO
824 t_auction_header_id,
825 t_wf_item_key
826
827 FROM pon_auction_headers_all pah
828 WHERE pah.close_bidding_date < SYSDATE
829 AND SYSDATE > pah.open_bidding_date
830 AND Nvl(pah.no_of_notifications_sent, 0) > 0;
831
832 IF(t_auction_header_id.first > 0) THEN
833
834
835 FOR i IN t_auction_header_id.first..t_auction_header_id.last
836 LOOP
837
838 l_item_key_like := t_wf_item_key(i)||'_%_'||t_auction_header_id(i)||'_%';
839
840 IF(g_debug_enabled = 'Y') then
841 x_progress := ++l_progress || l_api_name
842 || 'auction header id ' || t_auction_header_id(i)
843 || ': like item key to find the exact item key:' || l_item_key_like;
844 log_message(x_progress);
845 END IF;
846
847 BEGIN
848 SELECT item_key
849 BULK COLLECT INTO
850 t_exact_item_key
851 FROM wf_items
852 WHERE item_key LIKE l_item_key_like;
853
854 EXCEPTION WHEN OTHERS THEN
855
856 IF(g_debug_enabled = 'Y') then
857 x_progress := ++l_progress || l_api_name || ' : no item key for the auction ' ;
858 log_message(x_progress);
859 END IF;
860
861 END; -- SELECT STATEMENT
862
863 IF (t_exact_item_key.first > 0) THEN
864
865 FOR j IN t_exact_item_key.first..t_exact_item_key.last
866 LOOP
867
868 IF(g_debug_enabled = 'Y') then
869 x_progress := ++l_progress || l_api_name || ' : exact item key to purge the workflow:' || t_exact_item_key(j);
870 log_message(x_progress);
871 END IF;
872
873
874 WF_PURGE.ITEMS (itemtype => PON_SEND_NOTIF,
875 itemkey => t_exact_item_key(j),
876 enddate => SYSDATE,
877 docommit => TRUE,
878 force => TRUE
879 );
880
881
882
883
884 END LOOP;
885 END IF;
886 --update pon_auction_headers_all
887 --change the value to -1 after purging
888 UPDATE pon_auction_headers_all pah
889 SET pah.no_of_notifications_sent = -1
890 WHERE auction_header_id = t_auction_header_id(i);
891 END LOOP;
892
893 END IF;
894
895
896
897 IF(g_debug_enabled = 'Y') then
898 x_progress := ++l_progress || l_api_name || ' : END :';
899 log_message(x_progress);
900 END IF;
901
902 EXCEPTION WHEN No_Data_Found THEN
903
904 NULL;
905
906 END purge_notif_wf;
907 --*************************************************************************************
908
909
910 PROCEDURE log_message(p_message IN VARCHAR2) IS
911 BEGIN
912 IF (g_fnd_debug = 'Y') THEN
913 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
914 FND_LOG.string(log_level => FND_LOG.level_statement,
915 module => g_module,
916 message => substr(p_message, 0, 4000));
917 END IF;
918 END IF;
919 END log_message;
920 --*************************************************************************************
921
922 END pon_reminder_notification_pkg;