[Home] [Help]
PACKAGE BODY: APPS.OTA_INITIALIZATION_WF
Source
1 Package body ota_initialization_wf as
2 /* $Header: ottomint.pkb 120.43.12010000.3 2008/09/17 08:04:02 srgnanas ship $ */
3
4 g_package varchar2(33) := ' ota_initialization_wf.'; -- Global package name
5
6
7 -- ----------------------------------------------------------------------------
8 -- |-----------------< initialize_cancel_enrollment >-------------------------|
9 -- ----------------------------------------------------------------------------
10 -- {Start Of Comments}
11 --
12 -- Description:
13 -- This procedure will be a used to start a workflow process for Enrollment
14 -- Cancellation.
15 --
16 -- This procedure will only be used for OTA and OM integration.
17 --
18 -- Pre Conditions:
19 -- None.
20 --
21 -- In Arguments:
22 -- p_booking_id
23 -- p_Line_id
24 -- p_org_id
25 -- p_Status
26 -- p_Event_id
27 -- p_Itemtype
28 -- p_process
29 --
30 -- Post Success:
31 -- Processing continues.
32 --
33 --
34 -- Post Failure:
35 -- None.
36 --
37 -- Access Status:
38 -- Public.
39 --
40 -- {End Of Comments}
41 ----------------------------------------------------------------------------
42
43 PROCEDURE INITIALIZE_CANCEL_ENROLLMENT
44 (
45 p_booking_id IN NUMBER,
46 p_Line_id IN NUMBER,
47 p_org_id IN NUMBER,
48 p_Status IN VARCHAR2,
49 p_Event_id IN NUMBER,
50 p_Itemtype IN VARCHAR2,
51 p_process IN VARCHAR2)
52
53 IS
54
55 l_order_number oe_order_headers.order_number%type;
56 l_header_id oe_order_headers.header_id%type;
57 l_process wf_activities.name%type := upper(p_process);
58 l_itemkey wf_items.item_key%type := to_char(p_line_id);
59 l_owner_name per_people_f.full_name%type;
60 l_event_title ota_events_tl.title%type; --MLS change _tl added
61 l_owner_id ota_events.owner_id%type;
62 l_email_address per_people_f.email_address%type;
63 l_full_name per_people_f.full_name%type;
64
65
66 CURSOR C_ORDER
67 IS
68 SELECT ORDER_NUMBER ,
69 HEADER_ID
70 FROM OE_ORDER_HEADERS_ALL
71 WHERE HEADER_ID IN(
72 SELECT HEADER_ID
73 FROM OE_ORDER_LINES_ALL
74 WHERE LINE_ID = p_line_id);
75
76
77 CURSOR c_event
78 IS
79 SELECT TITLE,
80 owner_id
81 FROM OTA_EVENTS_VL --MLS change _VL added
82 WHERE event_id = p_event_id;
83
84
85 CURSOR C_person
86 IS
87 SELECT user_name
88 FROM
89 fnd_user
90 WHERE
91 employee_id = l_owner_id
92 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
93
94 l_proc varchar2(72) := g_package||'initialize_cancel_enrollment';
95
96 BEGIN
97 hr_utility.set_location('Entering:'||l_proc, 5);
98
99 OPEN C_ORDER;
100 FETCH C_ORDER INTO l_order_number,l_header_id;
101 CLOSE C_ORDER;
102 hr_utility.set_location('Entering:'||l_proc, 10);
103 OPEN c_event;
104 FETCH c_event INTO l_event_title,l_owner_id;
105 CLOSE c_event;
106 hr_utility.set_location('Entering:'||l_proc, 15);
107
108 /* OPEN c_person;
109 FETCH c_person INTO l_full_name,l_email_address;
110 CLOSE c_person;*/
111 hr_utility.set_location('Entering:'||l_proc, 20);
112
113 WF_ENGINE.CREATEPROCESS(p_itemtype, l_itemkey, 'OTA_ENR_CANCEL' );
114 -- WF_ENGINE.setitemattrnumber(p_itemtype, l_itemkey,'BOOKING_ID', p_booking_id);
115 WF_ENGINE.setitemattrtext(p_itemtype, l_itemkey,'EVENT_TITLE', l_Event_title);
116 -- WF_ENGINE.SetItemattrtext(p_itemtype,l_itemkey,'EVENT_OWNER',l_email_address);
117 WF_ENGINE.SetItemattrnumber(p_itemtype,l_itemkey,'ORDER_NUMBER',l_order_number);
118 WF_ENGINE.SetItemattrnumber(p_itemtype,l_itemkey,'HEADER_ID',l_header_id);
119 WF_ENGINE.SetItemattrnumber(p_itemtype,l_itemkey,'ORG_ID',p_org_id);
120
121 -- WF_ENGINE.SetItemattrtext(p_itemtype,l_itemkey,'FULL_NAME',l_full_name);
122 WF_ENGINE.STARTPROCESS(p_itemtype,l_itemkey);
123 hr_utility.set_location('Leaving:'||l_proc, 25);
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 --RAISE;
129
130
131 END;
132 -- ----------------------------------------------------------------------------
133 -- |-----------------------< initialize_cancel_event >-------------------------|
134 -- ----------------------------------------------------------------------------
135 -- {Start Of Comments}
136 --
137 -- Description:
138 -- This procedure is used to start workflow process for event cancellation.
139 --
140 -- This procedure will only be used for OTA and OM integration.
141 --
142 -- Pre Conditions:
143 -- None.
144 --
145 -- In Arguments:
146 -- p_event_id
147 -- p_line_id
148 -- p_status
149 -- p_event_title
150 -- p_itemtype
151 -- p_owner_id
152 -- p_org_id
153 --
154 -- Post Success:
155 -- Processing continues.
156 --
157 --
158 -- Post Failure:
159 -- None.
160 --
161 -- Access Status:
162 -- Public.
163 --
164 -- {End Of Comments}
165 ----------------------------------------------------------------------------
166
167
168 Procedure INITIALIZE_CANCEL_EVENT
169 (
170 p_event_id IN NUMBER,
171 p_Line_id IN NUMBER,
172 p_Status IN VARCHAR2,
173 p_Event_title IN VARCHAR2 ,
174 p_owner_id IN NUMBER,
175 p_org_id IN NUMBER,
176 p_itemtype IN VARCHAR2)
177 IS
178
179 CURSOR C_ORDER IS
180 SELECT
181 order_number ,
182 header_id
183 FROM
184 OE_ORDER_HEADERS_ALL
185 WHERE
186 HEADER_ID IN(
187 SELECT
188 HEADER_ID
189 FROM
190 OE_ORDER_LINES_ALL
191 WHERE
192 LINE_ID = p_line_id);
193
194 CURSOR
195 c_people
196 IS
197 SELECT
198 USER_NAME
199 FROM
200 FND_USER
201 WHERE
202 employee_id = p_owner_id
203 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
204
205
206 CURSOR C_ORDER_LINE IS
207 Select
208 line_number
209 from
210 oe_order_lines_all
211 where
212 line_id = p_line_id;
213
214
215 l_user_name varchar2(100);
216 l_full_name per_all_people_f.full_name%TYPE;
217 l_order_number oe_order_headers.order_number%type;
218 l_header_id oe_order_headers.header_id%type;
219 l_proc varchar2(72) := g_package||'initialize_cancel_event';
220 l_wf_date VARCHAR2(30);
221 l_item_key wf_items.item_key%TYPE;
222 l_line_number oe_order_lines_all.line_number%type;
223
224 BEGIN
225 hr_utility.set_location('Entering:'||l_proc, 5);
226
227 OPEN C_ORDER;
228 FETCH C_ORDER INTO l_order_number,l_header_id;
229 CLOSE C_ORDER;
230 hr_utility.set_location('Entering:'||l_proc, 15);
231 OPEN C_PEOPLE;
232 FETCH c_people INTO l_user_name;
233 CLOSE c_people;
234 hr_utility.set_location('Entering:'||l_proc, 17);
235 SELECT to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS')
236 INTO l_wf_date
237 FROM dual;
238 --Bug#2587983 get line number
239 OPEN C_ORDER_LINE;
240 FETCH C_ORDER_LINE into l_line_number;
241 CLOSE C_ORDER_LINE;
242
243 --Bug#2587983 get line number
244
245 l_item_key := p_line_id||l_wf_date;
246 WF_ENGINE.CREATEPROCESS(p_itemtype, l_item_key, 'OTA_EVT_CANCEL');
247 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key,'EVENT_TITLE', p_event_id); --Enh 5606090: Language support for Event Details.
248 WF_ENGINE.SetItemattrnumber(p_itemtype,l_item_key,'ORDER_NUMBER',l_order_number);
249 WF_ENGINE.SetItemattrtext(p_itemtype,l_item_key,'EVENT_OWNER',l_user_name);
250 WF_ENGINE.SetItemattrtext(p_itemtype,l_item_key,'STATUS',p_status);
251 WF_ENGINE.SetItemattrnumber(p_itemtype,l_item_key,'LINE_NUMBER',l_line_number);
252 WF_ENGINE.STARTPROCESS(p_itemtype,l_item_key);
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 hr_utility.set_location('Leaving:'||l_proc, 20);
258
259 END;
260
261
262 -- ----------------------------------------------------------------------------
263 -- |-------------------< initialize_event_date_changed >-----------------------|
264 -- ----------------------------------------------------------------------------
265 -- {Start Of Comments}
266 --
267 -- Description:
268 -- This procedure will be to start workflow process for course end date changed.
269 --
270 --
271 -- Pre Conditions:
272 -- None.
273 --
274 -- In Arguments:
275 -- p_Line_id
276 -- p_org_id
277 -- p_Event_title
278 -- p_Itemtype
279 -- p_process
280 -- p_emailid
281 -- p_name
282 --
283 -- Post Success:
284 -- Processing continues.
285 --
286 --
287 -- Post Failure:
288 -- None.
289 --
290 -- Access Status:
291 -- Public.
292 --
293 -- {End Of Comments}
294 ----------------------------------------------------------------------------
295
296 PROCEDURE INITIALIZE_EVENT_DATE_CHANGED
297 (
298 p_Line_id IN NUMBER,
299 p_org_id IN NUMBER,
300 p_Event_title IN VARCHAR2,
301 p_Itemtype IN VARCHAR2,
302 p_process IN VARCHAR2,
303 p_emailid IN VARCHAR2,
304 p_name IN VARCHAR2)
305 IS
306 BEGIN
307 null;
308 END;
309
310 -- ----------------------------------------------------------------------------
311 -- |----------------------< initialize_cancel_order >-------------------------|
312 -- ----------------------------------------------------------------------------
313 -- {Start Of Comments}
314 --
315 -- Description:
316 -- This procedure will be used to start a workflow if order got cancel from OM.
317 --
318 -- This procedure will only be used for OTA and OM integration.
319 --
320 -- Pre Conditions:
321 -- None.
322 --
323 -- In Arguments:
324 -- p_itemtype
325 -- p_process
326 -- p_Event_title
327 -- p_event_id
328 -- p_email_address
329 -- p_line_id
330 -- p_status
331 -- p_full_name
332 --
333 -- Post Success:
334 -- Processing continues.
335 --
336 --
337 -- Post Failure:
338 -- None.
339 --
340 -- Access Status:
341 -- Public.
342 --
343 -- {End Of Comments}
344 ----------------------------------------------------------------------------
345
346
347 PROCEDURE INITIALIZE_CANCEL_ORDER (
348 p_itemtype IN VARCHAR2,
349 p_process IN VARCHAR2,
350 p_Event_title IN VARCHAR2,
351 p_event_id IN NUMBER,
352 p_user_name IN VARCHAR2,
353 p_line_id IN NUMBER,
354 p_status IN VARCHAR2,
355 p_full_name IN VARCHAR2
356 ) IS
357
358
359 CURSOR C_ORDER IS
360 SELECT
361 order_number ,
362 header_id
363 FROM
364 OE_ORDER_HEADERS_ALL
365 WHERE
366 HEADER_ID IN(
367 SELECT
368 HEADER_ID
369 FROM
370 OE_ORDER_LINES_ALL
371 WHERE
372 LINE_ID = p_line_id);
373
374 CURSOR C_ORDER_LINE IS
375 Select
376 line_number
377 from
378 oe_order_lines_all
379 where
380 line_id = p_line_id;
381
382 l_order_number oe_order_headers.order_number%type;
383 l_header_id oe_order_headers.header_id%type;
384 l_process wf_activities.name%type := upper(p_process);
385 l_itemkey wf_items.item_key%type;
386 l_line_number oe_order_lines_all.line_number%type;
387
388 l_proc varchar2(72) := g_package||'initialize_cancel_order';
389
390 BEGIN
391 hr_utility.set_location('Entering:'||l_proc, 5);
392
393 OPEN C_ORDER;
394 FETCH C_ORDER INTO l_order_number,l_header_id;
395 CLOSE C_ORDER;
396 hr_utility.set_location('Entering:'||l_proc, 10);
397
398 --Bug#2587983 get line number
399 OPEN C_ORDER_LINE;
400 FETCH C_ORDER_LINE into l_line_number;
401 CLOSE C_ORDER_LINE;
402
403 --Bug#2587983 get line number
404
405 WF_ENGINE.CREATEPROCESS(p_itemtype, to_char(p_line_id), l_process);
406 WF_ENGINE.setitemattrtext(p_itemtype, to_char(p_line_id), 'EVENT_TITLE', p_event_id); --Enh 5606090: Language support for Event Details.
407 WF_ENGINE.SetItemattrtext(p_itemtype,to_char(p_line_id), 'EVENT_OWNER',p_user_name);
408 WF_ENGINE.SetItemattrnumber(p_itemtype,to_char(p_line_id), 'ORDER_NUMBER',l_order_number);
409 WF_ENGINE.SetItemattrtext(p_itemtype,to_char(p_line_id), 'STATUS',p_status);
410 WF_ENGINE.SetItemattrnumber(p_itemtype,to_char(p_line_id),'LINE_NUMBER',l_line_number);
411 WF_ENGINE.STARTPROCESS(p_itemtype,to_char(p_line_id));
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 hr_utility.set_location('leaving:'||l_proc, 20);
417
418 END;
419
420
421 -- ----------------------------------------------------------------------------
422 -- |-----------------------------< Manual_waitlist >-------------------------|
423 -- ----------------------------------------------------------------------------
424 -- {Start Of Comments}
425 --
426 -- Description:
427 -- This procedure will be used to start a workflow to notify event owner to
428 -- do manual waitlist enrollment .
429 --
430 --
431 -- Pre Conditions:
432 -- None.
433 --
434 -- In Arguments:
435 -- p_itemtype
436 -- p_process
437 -- p_Event_title
438 -- p_event_id
439 -- p_user_name
440 --
441 -- Post Success:
442 -- Processing continues.
443 --
444 --
445 -- Post Failure:
446 -- None.
447 --
448 -- Access Status:
449 -- Public.
450 --
451 -- {End Of Comments}
452 ----------------------------------------------------------------------------
453
454
455 PROCEDURE MANUAL_WAITLIST (
456 p_itemtype IN VARCHAR2,
457 p_process IN VARCHAR2,
458 p_Event_title IN VARCHAR2,
459 p_event_id IN NUMBER,
460 p_item_key IN VARCHAR2,
461 p_user_name IN VARCHAR2
462 ) IS
463
464 l_proc varchar2(72) := g_package||'manual_waitlist';
465 l_process wf_activities.name%type := upper(p_process);
466
467 BEGIN
468 hr_utility.set_location('Entering:'||l_proc, 5);
469
470
471 hr_utility.set_location('Entering:'||l_proc, 10);
472
473 WF_ENGINE.CREATEPROCESS(p_itemtype, p_item_key, l_process);
474 WF_ENGINE.setitemattrtext(p_itemtype, p_item_key, 'EVENT_TITLE', p_event_id); --Enh 5606090: Language support for Event Details.
475 WF_ENGINE.SetItemattrtext(p_itemtype, p_item_key,'EVENT_OWNER',p_user_name);
476 WF_ENGINE.STARTPROCESS(p_itemtype,p_item_key);
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
481 hr_utility.set_location('leaving:'||l_proc, 20);
482
483 END;
484
485 -- ----------------------------------------------------------------------------
486 -- |----------------------< Manual_enroll_waitlist >-------------------------|
487 -- ----------------------------------------------------------------------------
488 -- {Start Of Comments}
489 --
490 -- Description:
491 -- This procedure will be used to start a workflow to notify event owner to
492 -- do manual waitlist enrollment .
493 --
494 --
495 -- Pre Conditions:
496 -- None.
497 --
498 -- In Arguments:
499 -- p_itemtype
500 -- p_process
501 -- p_Event_title
502 -- p_event_id
503 -- p_user_name
504 --
505 -- Post Success:
506 -- Processing continues.
507 --
508 --
509 -- Post Failure:
510 -- None.
511 --
512 -- Access Status:
513 -- Public.
514 --
515 -- {End Of Comments}
516 ----------------------------------------------------------------------------
517
518 PROCEDURE MANUAL_ENROLL_WAITLIST (
519 p_itemtype IN VARCHAR2,
520 p_process IN VARCHAR2,
521 p_Event_title IN VARCHAR2,
522 p_item_key IN VARCHAR2,
523 p_owner_id IN NUMBER
524 ) IS
525
526 l_proc varchar2(72) := g_package||'manual_enroll_waitlist';
527 l_process wf_activities.name%type := upper(p_process);
528
529 l_user_name varchar2(80);
530
531 CURSOR C_USER IS
532 SELECT
533 USER_NAME
534 FROM
535 FND_USER
536 WHERE
537 Employee_id = p_owner_id
538 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
539
540 BEGIN
541 hr_utility.set_location('Entering:'||l_proc, 5);
542
543 OPEN C_USER;
544 FETCH C_USER INTO l_user_name;
545 CLOSE C_USER;
546
547 hr_utility.set_location('Entering:'||l_proc, 10);
548
549 WF_ENGINE.CREATEPROCESS(p_itemtype, p_item_key, l_process);
550 WF_ENGINE.setitemattrtext(p_itemtype, p_item_key, 'EVENT_TITLE', p_event_title);
551 WF_ENGINE.SetItemattrtext(p_itemtype,p_item_key, 'EVENT_OWNER',l_user_name);
552 WF_ENGINE.STARTPROCESS(p_itemtype,p_item_key);
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 hr_utility.set_location('leaving:'||l_proc, 20);
558
559 END;
560
561 Procedure set_addnl_attributes(p_item_type in wf_items.item_type%type,
562 p_item_key in wf_items.item_key%type,
563 p_eventid in ota_events.event_id%type,
564 p_from in varchar2 default null
565 )
566
567 is
568
569 l_proc varchar2(72) := g_package||'set_addnl_attributes';
570
571 l_actual_cost ota_events.actual_cost%type;
572 l_budget_currency_code ota_events.budget_currency_code%type;
573 l_act_ver_id ota_events.activity_version_id%type;
574 l_off_id ota_events.parent_offering_id%type;
575 l_event_id ota_events.event_id%type;
576 l_event_type ota_events.event_type%type;
577 l_object_type varchar2(240);
578
579 cursor get_event_type is
580 select parent_event_id,event_type from
581 ota_events where event_id =p_eventid;
582
583
584 cursor get_addnl_event_info
585 is
586 select
587 --added after show n tell
588 oev.activity_version_id,oev.actual_cost, oev.budget_currency_code,
589 oev.parent_offering_id,ota_timezone_util.get_timezone_name(oev.timezone) timezone
590 from ota_events_tl evt, ota_events oev
591 where evt.event_id =oev.event_id
592 and oev.event_id = l_event_id
593 and evt.language=USERENV('LANG');
594
595 cursor get_lang_det is
596 select ofe.language_id, ocu.category
597 from ota_offerings ofe, ota_category_usages_tl ocu
598 where ofe.delivery_mode_id = ocu.category_usage_id
599 and ocu.language=USERENV('LANG')
600 and ofe.offering_id = l_off_id;
601
602 l_course_name OTA_ACTIVITY_VERSIONS_TL.version_name%TYPE;
603 l_lang_description fnd_languages_vl.description%TYPE;
604 l_curr_name fnd_currencies_vl.name%TYPE;
605 l_lang_id ota_offerings.language_id%type;
606 l_delivery_method ota_category_usages.category%type;
607 l_timezone varchar2(300);
608
609 begin
610
611 -- first check whether event is class or session
612
613 open get_event_type;
614 fetch get_event_type into l_event_id,l_event_type;
615 close get_event_type;
616
617
618 if l_event_type <> 'SESSION' then
619 l_event_id := p_eventid;
620 end if;
621
622 open get_addnl_event_info;
623 fetch get_addnl_event_info into l_act_ver_id,l_actual_cost,
624 l_budget_currency_code,l_off_id, l_timezone;
625 close get_addnl_event_info;
626
627 open get_lang_det;
628 fetch get_lang_det into l_lang_id,l_delivery_method;
629 close get_lang_det;
630
631 l_course_name := ota_general.get_course_name(l_act_ver_id);
632 l_curr_name := ota_general.fnd_currency_name(l_budget_currency_code);
633 l_curr_name := l_actual_cost || ' ' || l_curr_name;
634
635 l_lang_description := ota_general.fnd_lang_desc(l_lang_id);
636
637 --set wf item attributes
638
639 wf_engine.setItemAttrText(p_item_type,p_item_key,'COST',l_curr_name );
640 wf_engine.setItemAttrText(p_item_type,p_item_key,'COURSE_NAME',l_course_name );
641 wf_engine.setItemAttrText(p_item_type,p_item_key,'LANGUAGE',l_lang_description );
642 wf_engine.setItemAttrText(p_item_type,p_item_key,'DELIVERY_METHOD',l_delivery_method );
643
644
645 if p_from is null then
646 wf_engine.setItemAttrText(p_item_type,p_item_key,'TIMEZONE',l_timezone );
647 end if;
648
649 -- get object type
650
651 if l_event_type = 'SESSION' then
652
653 l_object_type := ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','S', 800);
654
655 else
656
657 l_object_type := ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','CL', 800);
658 end if;
659
660
661 WF_ENGINE.setitemattrText(p_item_type, p_item_key, 'SECTION_NAME', l_object_type);
662
663
664
665 end set_addnl_attributes;
666
667 Procedure Initialize_instructor_wf(
668 p_item_type in wf_items.item_type%type,
669 p_eventid in ota_events.event_id%type,
670 p_sup_res_id in ota_resource_bookings.supplied_resource_id%type,
671 p_start_date in varchar2,
672 p_end_date in varchar2,
673 p_start_time in ota_events.course_start_time%type,
674 p_end_time in ota_events.course_start_time%type,
675 p_status in varchar2,
676 p_res_book_id in ota_resource_bookings.resource_booking_id%type,
677 p_person_id in number,
678 p_event_fired in varchar2)
679
680 is
681 l_proc varchar2(72) := g_package||'Initialize_instructor_wf';
682
683 l_process wf_activities.name%type := 'OTA_INSTRUCTOR_NTF_JSP_PRC';
684 l_item_key wf_items.item_key%type;
685
686 l_title ota_events_tl.title%type;
687 l_start_date varchar2(100);
688 l_end_date varchar2(100);
689 l_start_time ota_events.course_start_time%type;
690 l_end_time ota_events.course_start_time%type;
691
692 l_location_id ota_events.location_id %type;
693 l_event_type ota_events.event_type%type;
694
695 l_person_id per_people_f.person_id%type;
696
697 l_object_type varchar2(240);
698 l_location_name hr_locations_all_tl.location_code%type;
699 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
700 l_timezone varchar2(300);
701
702
703
704
705 --l_booking_id ota_delegate_bookings.booking_id%type;
706 /*
707 cursor get_resource_info
708 is
709 select osr.trainer_id
710 from ota_suppliable_resources osr
711 where
712 osr.supplied_resource_id=p_sup_res_id;
713 */
714 cursor get_event_info
715 is
716 select evt.title,
717 oev.course_start_date,oev.course_end_date,oev.course_start_time, oev.course_end_time,
718 oev.location_id,oev.event_type,ota_timezone_util.get_timezone_name(oev.timezone)
719 from ota_events_tl evt, ota_events oev
720 where evt.event_id =oev.event_id
721 and oev.event_id = p_eventid
722 and evt.language= USERENV('LANG');
723
724 cursor get_all_resources_info
725 is
726 select distinct(osr.trainer_id) trainer_id ,orb.required_date_from,orb.required_date_to,
727 orb.required_start_time,orb.required_end_time,orb.status,ota_timezone_util.get_timezone_name(orb.timezone_code) timezone,
728 orb.resource_booking_id resource_booking_id
729 from ota_resource_bookings orb,ota_suppliable_resources osr
730 where orb.supplied_resource_id = osr.supplied_resource_id
731 and osr.resource_type ='T'
732 and orb.event_id = p_eventid
733 and (p_res_book_id is null or orb.resource_booking_id=p_res_book_id);
734
735 cursor get_resource_info
736 is
737 select osr.trainer_id trainer_id ,orb.required_date_from,orb.required_date_to,
738 orb.required_start_time,orb.required_end_time,orb.status,ota_timezone_util.get_timezone_name(orb.timezone_code) timezone,
739 orb.resource_booking_id resource_booking_id
740 from ota_resource_bookings orb,ota_suppliable_resources osr
741 where orb.supplied_resource_id = osr.supplied_resource_id
742 and osr.supplied_resource_id = p_sup_res_id
743 and osr.resource_type ='T'
744 and orb.event_id = p_eventid;
745
746 resource_rec get_resource_info%ROWTYPE;
747
748 begin
749 hr_utility.set_location('Entering:'||l_proc, 5);
750
751 open get_event_info;
752 fetch get_event_info into l_title,l_start_date,l_end_date,l_start_time,
753 l_end_time,l_location_id,l_event_type,l_timezone;
754 close get_event_info;
755
756 hr_utility.trace ('after get_event_info ' ||l_title);
757
758 -- get location
759 l_location_name := ota_general.get_Location_code(l_location_id);
760
761 if p_event_fired = 'INSTRUCTOR_CANCEL' then
762
763 -- Get the next item key from the sequence
764 select hr_workflow_item_key_s.nextval
765 into l_item_key
766 from sys.dual;
767
768
769 -- get item key for the process
770 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
771
772 hr_utility.trace ('after Createprocess ' ||l_item_key);
773
774
775 -- set creator,current and supervisor name
776 set_wf_item_attr(p_person_id => p_person_id,
777 p_item_type => p_item_type,
778 p_item_key => l_item_key);
779
780 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
781
782
783
784
785 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'EVENT_TITLE', p_eventid); --Enh 5606090: Language support for Event Details.
786 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'TARGET_DATE', p_start_date);
787 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'COMPLETION_DATE', p_end_date);
788 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_START_TIME', nvl(p_start_time,'00:00'));
789 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', nvl(p_end_time,'23:59'));
790
791
792
793
794
795
796 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_NAME', l_location_name);
797
798
799
800 -- get trainer enrollment status
801
802 --l_enrollment_status_name := ota_utility.get_lookup_meaning('RESOURCE_BOOKING_STATUS',p_status, 800); Enh 5606090: Language support for Event Details.
803
804 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_ENROLLEE', p_status); --Enh 5606090: Language support for Event Details.
805
806 set_addnl_attributes(p_item_type => p_item_type,
807 p_item_key => l_item_key,
808 p_eventid => p_eventid
809 );
810
811
812 WF_ENGINE.STARTPROCESS(p_item_type,l_item_key);
813
814 elsif p_event_fired = 'INSTRUCTOR_REMIND' then
815
816 open get_resource_info;
817 fetch get_resource_info into resource_rec;
818 if get_resource_info%FOUND then
819 -- Get the next item key from the sequence
820 select hr_workflow_item_key_s.nextval
821 into l_item_key
822 from sys.dual;
823
824 hr_utility.trace ('Before Createprocess ' ||resource_rec.trainer_id );
825 -- get item key for the process
826 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
827
828 hr_utility.trace ('after Createprocess ' ||l_item_key);
829
830 set_wf_item_attr(p_person_id => resource_rec.trainer_id,
831 p_item_type => p_item_type,
832 p_item_key => l_item_key);
833
834 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
835
836 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'EVENT_TITLE', p_eventid); --Enh 5606090: Language support for Event Details.
837 WF_ENGINE.setitemAttrText(p_item_type,l_item_key, 'BOOKING_ID', resource_rec.resource_booking_id); --Enh 5606090: Language support for Event Details.
838 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'TARGET_DATE', resource_rec.required_date_from);
839 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'COMPLETION_DATE', resource_rec.required_date_to);
840 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_START_TIME', nvl(resource_rec.required_start_time,'00:00'));
841 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', nvl(resource_rec.required_end_time,'23:59'));
842 wf_engine.setItemAttrText(p_item_type,l_item_key,'TIMEZONE',resource_rec.timezone );
843
844 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_NAME', l_location_name);
845
846 -- get trainer enrollment status
847
848 l_enrollment_status_name := ota_utility.get_lookup_meaning('RESOURCE_BOOKING_STATUS',resource_rec.status, 800);
849
850 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_ENROLLEE', l_enrollment_status_name);
851
852 set_addnl_attributes(p_item_type => p_item_type,
853 p_item_key => l_item_key,
854 p_eventid => p_eventid,
855 p_from => 'I'
856 );
857
858
859 WF_ENGINE.STARTPROCESS(p_item_type,l_item_key);
860 end if;
861 close get_resource_info;
862 else
863
864 for rec in get_all_resources_info
865 Loop
866
867 -- Get the next item key from the sequence
868 select hr_workflow_item_key_s.nextval
869 into l_item_key
870 from sys.dual;
871
872 hr_utility.trace ('Before Createprocess ' ||rec.trainer_id);
873 -- get item key for the process
874 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
875
876 hr_utility.trace ('after Createprocess ' ||l_item_key);
877
878
879 /*
880 open get_resource_info;
881 fetch get_resource_info into l_person_id;
882 close get_resource_info;*/
883 -- set creator,current and supervisor name
884 set_wf_item_attr(p_person_id => rec.trainer_id,
885 p_item_type => p_item_type,
886 p_item_key => l_item_key);
887
888 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
889
890
891
892 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'EVENT_TITLE', p_eventid); --Enh 5606090: Language support for Event Details.
893 WF_ENGINE.setitemAttrText(p_item_type,l_item_key, 'BOOKING_ID', rec.resource_booking_id); --Enh 5606090: Language support for Event Details.
894 if p_event_fired = 'CLASS_CANCEL' or p_event_fired = 'CLASS_RESCHEDULE' then
895
896 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'TARGET_DATE', l_start_date);
897 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'COMPLETION_DATE', l_end_date);
898 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_START_TIME', nvl(l_start_time,'00:00'));
899 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', nvl(l_end_time,'23:59'));
900 wf_engine.setItemAttrText(p_item_type,l_item_key,'TIMEZONE',l_timezone );
901
902 else
903 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'TARGET_DATE', rec.required_date_from);
904 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'COMPLETION_DATE', rec.required_date_to);
905 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_START_TIME', nvl(rec.required_start_time,'00:00'));
906 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', nvl(rec.required_end_time,'23:59'));
907 wf_engine.setItemAttrText(p_item_type,l_item_key,'TIMEZONE',rec.timezone );
908
909 end if;
910
911
912
913
914 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_NAME', l_location_name);
915
916
917
918 -- get trainer enrollment status
919
920 l_enrollment_status_name := ota_utility.get_lookup_meaning('RESOURCE_BOOKING_STATUS',rec.status, 800);
921
922 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_ENROLLEE', l_enrollment_status_name);
923
924 set_addnl_attributes(p_item_type => p_item_type,
925 p_item_key => l_item_key,
926 p_eventid => p_eventid,
927 p_from => 'I'
928 );
929
930
931 WF_ENGINE.STARTPROCESS(p_item_type,l_item_key);
932 end loop;
933
934 end if;
935 hr_utility.set_location('Leaving:'||l_proc, 5);
936
937 end Initialize_instructor_wf;
938
939 Procedure Initialize_wf(p_process in wf_process_activities.process_name%type,
940 p_item_type in wf_items.item_type%type,
941 p_person_id in number ,
942 p_eventid in ota_Events.event_id%type,
943 p_event_fired in varchar2)
944
945 is
946 l_proc varchar2(72) := g_package||'Initialize_wf';
947
948 l_process wf_activities.name%type := upper(p_process);
949 l_item_key wf_items.item_key%type;
950
951 l_title ota_events_tl.title%type;
952 l_start_date varchar2(100);
953 l_end_date varchar2(100);
954 l_start_time ota_events.course_start_time%type;
955 l_end_time ota_events.course_start_time%type;
956 l_location_id ota_events.location_id %type;
957 l_event_type ota_events.event_type%type;
958
959 --l_object_type varchar2(240);
960 l_location_name hr_locations_all_tl.location_code%type;
961 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
962
963 l_booking_id ota_delegate_bookings.booking_id%type;
964 l_person_id number(15);
965
966 cursor get_event_info
967 is
968 select evt.title,oev.course_start_date,oev.course_end_date,
969 oev.course_start_time, oev.course_end_time,
970 oev.location_id
971 from ota_events_tl evt, ota_events oev
972 where evt.event_id =oev.event_id
973 and oev.event_id = p_eventid
974 and evt.language=USERENV('LANG');
975
976 -- get all the person's enrolled into the event
977 cursor get_booking_info
978 is
979 select odb.booking_id , odb.delegate_person_id
980 from ota_delegate_bookings odb, ota_booking_status_types bst
981 where (p_person_id is null or
982 odb.delegate_person_id = p_person_id)
983 and odb.event_id =p_eventid
984 and odb.booking_status_type_id = bst.booking_status_type_id
985 and bst.type in ('P','W','R');
986
987 --Enh 5606090: Language support for Event Details.
988 cursor get_booking_info_class_cancel
989 is
990 select odb.booking_id , odb.delegate_person_id
991 from ota_delegate_bookings odb, ota_booking_status_types bst
992 where (p_person_id is null or
993 odb.delegate_person_id = p_person_id)
994 and odb.event_id =p_eventid
995 and odb.booking_status_type_id = bst.booking_status_type_id;
996
997
998 begin
999 hr_utility.set_location('Entering:'||l_proc, 5);
1000
1001 open get_event_info;
1002 fetch get_event_info into l_title,l_start_date,l_end_date,l_start_time,
1003 l_end_time,l_location_id;
1004 close get_event_info;
1005
1006 if p_event_fired = 'CLASS_CANCEL' then
1007
1008 -- Get the next item key from the sequence
1009 select hr_workflow_item_key_s.nextval
1010 into l_item_key
1011 from sys.dual;
1012 -- get item key for the process
1013 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
1014
1015 -- set creator,current and supervisor name
1016 set_wf_item_attr(p_person_id => p_person_id,
1017 p_item_type => p_item_type,
1018 p_item_key => l_item_key);
1019
1020 open get_booking_info_class_cancel;
1021 fetch get_booking_info_class_cancel into l_booking_id,l_person_id;
1022 close get_booking_info_class_cancel;
1023
1024 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
1025
1026
1027
1028 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'EVENT_TITLE', p_eventid); --Enh 5606090: Language support for Event Details.
1029 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'TARGET_DATE', l_start_date);
1030 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'COMPLETION_DATE', l_end_date);
1031 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_START_TIME', nvl(l_start_time,'00:00'));
1032 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', nvl(l_end_time,'23:59'));
1033 WF_ENGINE.setitemattrNumber(p_item_type, l_item_key, 'BOOKING_ID', l_booking_id);
1034
1035 -- get location
1036 l_location_name := ota_general.get_Location_code(l_location_id);
1037
1038 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_NAME', l_location_name);
1039
1040 set_addnl_attributes(p_item_type => p_item_type,
1041 p_item_key => l_item_key,
1042 p_eventid => p_eventid
1043 );
1044
1045
1046 WF_ENGINE.STARTPROCESS(p_item_type,l_item_key);
1047
1048
1049 else
1050
1051 for rec in get_booking_info
1052 Loop
1053
1054 -- Get the next item key from the sequence
1055 select hr_workflow_item_key_s.nextval
1056 into l_item_key
1057 from sys.dual;
1058 -- get item key for the process
1059 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
1060
1061 -- set creator,current and supervisor name
1062 set_wf_item_attr(p_person_id => rec.delegate_person_id,
1063 p_item_type => p_item_type,
1064 p_item_key => l_item_key);
1065
1066
1067
1068 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
1069
1070
1071
1072 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'EVENT_TITLE', p_eventid); --Enh 5606090: Language support for Event Details.
1073 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'TARGET_DATE', l_start_date);
1074 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'COMPLETION_DATE', l_end_date);
1075 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_START_TIME', nvl(l_start_time,'00:00'));
1076 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', nvl(l_end_time,'23:59'));
1077
1078 -- get location
1079 l_location_name := ota_general.get_Location_code(l_location_id);
1080
1081 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'LP_NAME', l_location_name);
1082
1083 -- if l_process = 'OTA_ENROLL_STATUS_CHNG_JSP_PRC' then
1084
1085 WF_ENGINE.setitemattrNumber(p_item_type, l_item_key, 'BOOKING_ID', rec.booking_id);
1086 -- end if;
1087
1088 set_addnl_attributes(p_item_type => p_item_type,
1089 p_item_key => l_item_key,
1090 p_eventid => p_eventid
1091 );
1092 WF_ENGINE.STARTPROCESS(p_item_type,l_item_key);
1093
1094 end loop;
1095
1096 end if;
1097 hr_utility.set_location('Leaving:'||l_proc, 5);
1098
1099 end initialize_wf;
1100
1101
1102 Procedure Initialize_auto_wf(p_process in wf_process_activities.process_name%type,
1103 p_item_type in wf_items.item_type%type,
1104 p_event_fired in varchar2,
1105 p_event_id in ota_events.event_id%type default null)
1106 is
1107 l_proc varchar2(72) := g_package||'Initialize_auto_wf';
1108
1109 l_notify_days_before number(9) := fnd_profile.value('OTA_INST_REMIND_NTF_DAYS');
1110
1111 --get all classes begining today
1112
1113 cursor get_all_class_info
1114 is
1115 select event_id
1116 from ota_events
1117 where (course_start_date = trunc(sysdate) )
1118 and event_type in ('SCHEDULED' , 'SELFPACED')
1119 and event_status <> 'A';
1120
1121 -- get all waitlisted learners in a class
1122
1123 cursor get_all_wait_Learners(crs_event_id number)
1124 is
1125 select odb.delegate_person_id
1126 from ota_delegate_bookings odb,ota_booking_status_types bst
1127 where odb.event_id = crs_event_id
1128 and odb.booking_status_type_id = bst.booking_status_type_id
1129 and bst.type = 'W';
1130
1131 --- get all class info for instructor
1132
1133 cursor get_supp_res_id is
1134 select supplied_resource_id
1135 from ota_suppliable_resources
1136 where resource_type ='T';
1137
1138 cursor get_all_cls_info (l_supp_res_id number) is
1139 select orb.event_id event_id
1140 from ota_resource_bookings orb
1141 where orb.supplied_resource_id = l_supp_res_id
1142 and orb.required_date_from = (trunc(sysdate)+l_notify_days_before)
1143 and orb.status= 'C'
1144 and orb.event_id is not null;
1145
1146 /*cursor get_all_cls_info
1147 is
1148 select distinct(orb.event_id) event_id, orb.supplied_resource_id
1149 from ota_resource_bookings orb,ota_suppliable_resources osr
1150 where orb.supplied_resource_id = osr.supplied_resource_id
1151 and osr.resource_type ='T'
1152 and orb.required_date_from = (trunc(sysdate) + l_notify_days_before)
1153 and orb.status= 'C'
1154 and orb.event_id is not null;*/
1155
1156
1157 begin
1158 hr_utility.set_location('Entering:'||l_proc, 5);
1159 fnd_file.put_line(FND_FILE.LOG,'Event Fired ' ||p_event_fired);
1160
1161 if p_event_fired ='CLASS_START' then
1162 --this code would be called when classbeginning notification has to be fired
1163 -- from API code on change of class dates
1164 if p_event_id is not null then
1165
1166 for lrnr in get_all_wait_Learners(p_event_id)
1167 loop
1168
1169 Initialize_wf(p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC',
1170 p_item_type => 'OTWF',
1171 p_person_id => lrnr.delegate_person_id,
1172 p_eventid => p_event_id,
1173 p_event_fired => p_event_fired);
1174
1175 end loop;
1176 else
1177
1178 for cls in get_all_class_info
1179 Loop
1180 fnd_file.put_line(FND_FILE.LOG,'Event Id ' ||cls.event_id);
1181 for lrnr in get_all_wait_Learners(cls.event_id)
1182 loop
1183 fnd_file.put_line(FND_FILE.LOG,'Person Id ' ||lrnr.delegate_person_id);
1184 Initialize_wf(p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC',
1185 p_item_type => 'OTWF',
1186 p_person_id => lrnr.delegate_person_id,
1187 p_eventid => cls.event_id,
1188 p_event_fired => p_event_fired);
1189
1190 end loop;
1191
1192 end loop;
1193 end if; --for event_id
1194 elsif p_event_fired ='INSTRUCTOR_REMIND' then
1195 for trn in get_supp_res_id
1196 loop
1197 for cls in get_all_cls_info(trn.supplied_resource_id)
1198 Loop
1199 fnd_file.put_line(FND_FILE.LOG,'Event Id ' ||cls.event_id);
1200 fnd_file.put_line(FND_FILE.LOG,'Supplied Resource Id ' ||trn.supplied_resource_id);
1201 OTA_INITIALIZATION_WF.initialize_instructor_wf(
1202 p_item_type => 'OTWF',
1203 p_eventid => cls.event_id,
1204 p_sup_res_id => trn.supplied_resource_id,
1205 p_event_fired => p_event_fired);
1206
1207 end loop;
1208 end loop;
1209 end if;
1210
1211
1212 hr_utility.set_location('Leaving:'||l_proc, 5);
1213 end Initialize_auto_wf;
1214
1215 Procedure Init_LP_wf(p_item_type in wf_items.item_type%type,
1216 p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type,
1217 p_event_fired in varchar2)
1218 is
1219
1220 cursor get_lp_info
1221 is
1222 select lpt.name, lpe.person_id, lp.start_date_active
1223 from ota_learning_paths lp , ota_learning_paths_tl lpt, ota_lp_enrollments lpe
1224 where lpt.learning_path_id = lp.learning_path_id
1225 and lpt.Language= USERENV('LANG')
1226 and lp.Learning_path_id = lpe.Learning_path_id
1227 and lpe.lp_enrollment_id = p_lp_enrollment_id;
1228
1229 l_person_id per_people_f.person_id%type;
1230 l_LP_name ota_learning_paths_tl.name%type;
1231 l_start_date ota_learning_paths.start_date_active%type;
1232 l_process wf_activities.name%type := 'OTA_LP_JSP_PRC';
1233 l_item_key wf_items.item_key%type;
1234
1235
1236
1237 begin
1238
1239 open get_lp_info;
1240 fetch get_lp_info into l_LP_name,l_person_id,l_start_date;
1241 close get_lp_info;
1242
1243 -- Get the next item key from the sequence
1244 select hr_workflow_item_key_s.nextval
1245 into l_item_key
1246 from sys.dual;
1247 -- get item key for the process
1248 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
1249
1250 -- set creator,current and supervisor name
1251 set_wf_item_attr(p_person_id => l_person_id,
1252 p_item_type => p_item_type,
1253 p_item_key => l_item_key);
1254
1255 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
1256
1257
1258 --Enh 5606090: Language support for LP Details.
1259 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_ACTIVITY_VERSION_NAME', p_lp_enrollment_id);
1260 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_COURSE_START_DATE', l_start_date);
1261
1262 WF_ENGINE.STARTPROCESS(p_item_type,l_item_key);
1263
1264 end init_LP_wf;
1265
1266
1267
1268
1269
1270 Procedure get_event_fired(itemtype in varchar2,
1271 itemkey in varchar2,
1272 actid in number,
1273 funcmode in varchar2,
1274 resultout out nocopy varchar2)
1275 is
1276 l_value varchar2(100);
1277
1278 begin
1279 hr_utility.set_location('ENTERING get_event_fired', 10);
1280 IF (funcmode='RUN') THEN
1281
1282 l_value := wf_engine.getItemAttrText(itemtype => itemtype
1283 ,itemkey => itemkey
1284 , aname => 'OTA_NTF_EVENT_FIRED');
1285 hr_utility.trace ('l_value ' ||l_value);
1286 if l_value is not null then
1287
1288 resultout:='COMPLETE:' || l_value;
1289
1290 else
1291 resultout:='COMPLETE';
1292 end if;
1293 hr_utility.trace ('resultout ' ||resultout);
1294 RETURN;
1295 end if;
1296 IF (funcmode='CANCEL') THEN
1297 resultout:='COMPLETE';
1298 RETURN;
1299 END IF;
1300
1301 end get_event_fired;
1302
1303
1304 procedure set_wf_item_attr(p_person_id in number,
1305 p_item_type in wf_items.item_type%type,
1306 p_item_key in wf_items.item_key%type)
1307 is
1308
1309 l_proc varchar2(72) := g_package||'set_wf_item_attr';
1310
1311 l_creator_username varchar2(80):= fnd_profile.value('USERNAME');
1312 l_creator_user_Id number := fnd_profile.value('USER_ID');
1313 l_creator_full_name per_all_people_f.full_name%TYPE;
1314
1315 l_current_username varchar2(80);
1316
1317 l_current_user_Id number ;
1318 l_current_full_name per_all_people_f.full_name%TYPE;
1319
1320 l_creator_person_id per_all_people_f.person_id%type;
1321
1322 l_supervisor_id per_all_people_f.person_id%Type;
1323 l_supervisor_username fnd_user.user_name%TYPE;
1324 l_supervisor_full_name per_all_people_f.full_name%TYPE;
1325
1326 l_role_name wf_roles.name%type;
1327 l_role_display_name wf_roles.display_name%type;
1328
1329
1330 -- get current person's user name
1331 cursor curr_per_info
1332 is
1333 Select user_id ,user_name
1334 from
1335 fnd_user
1336 where employee_id=p_person_id
1337 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
1338
1339 -- get creator person's person id
1340 CURSOR C_USER IS
1341 SELECT
1342 EMPLOYEE_ID
1343 FROM
1344 FND_USER
1345 WHERE
1346 user_id = l_creator_user_id ;
1347
1348 -- get full name
1349 CURSOR csr_person_name (crs_person_id number) IS
1350 SELECT
1351 pp.full_name
1352 FROM per_people_f pp
1353 WHERE pp.person_id = crs_person_id
1354 AND trunc(sysdate) BETWEEN pp.effective_start_date AND pp.effective_end_date;
1355 -- get supervisor name
1356 CURSOR csr_supervisor_id IS
1357 SELECT asg.supervisor_id, per.full_name
1358 FROM per_all_assignments_f asg,
1359 per_all_people_f per
1360 WHERE asg.person_id = p_person_id
1361 AND per.person_id = asg.supervisor_id
1362 AND asg.primary_flag = 'Y'
1363 AND trunc(sysdate)
1364 BETWEEN asg.effective_start_date AND asg.effective_end_date
1365 AND trunc(sysdate)
1366 BETWEEN per.effective_start_date AND per.effective_end_date;
1367 -- get supervisor full name
1368 CURSOR csr_supervisor_user IS
1369 SELECT user_name
1370 FROM fnd_user
1371 WHERE employee_id= l_supervisor_id
1372 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
1373
1374
1375
1376 begin
1377 hr_utility.set_location('Entering:'||l_proc, 5);
1378
1379 OPEN curr_per_info;
1380 FETCH curr_per_info INTO l_current_user_id, l_current_username;
1381 CLOSE curr_per_info;
1382
1383 OPEN C_USER;
1384 FETCH C_USER INTO l_creator_person_id;
1385 CLOSE C_USER;
1386
1387
1388
1389
1390 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CURRENT_PERSON_USERNAME', l_current_username);
1391 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CREATOR_PERSON_USERNAME', l_creator_username);
1392
1393 hr_utility.trace ('Current username ' ||l_current_username);
1394
1395 open csr_person_name(l_creator_person_id);
1396 fetch csr_person_name into l_creator_full_name;
1397 close csr_person_name;
1398
1399 open csr_person_name(p_person_id);
1400 fetch csr_person_name into l_current_full_name;
1401 close csr_person_name;
1402
1403
1404 WF_ENGINE.setitemattrtext(p_item_type,
1405 p_item_key,
1406 'CURRENT_PERSON_DISPLAY_NAME',
1407 l_current_full_name);
1408
1409 --for forum notification Login person can be contact
1410 if p_item_type = 'OTWF' and l_creator_full_name is not null then
1411
1412
1413 WF_ENGINE.setitemattrtext(p_item_type,
1414 p_item_key,
1415 'LP_CREATOR_NAME',
1416 l_creator_full_name);
1417
1418 elsif p_item_type <> 'OTWF' then
1419
1420 WF_ENGINE.setitemattrtext(p_item_type,
1421 p_item_key,
1422 'CREATOR_PERSON_DISPLAY_NAME',
1423 l_creator_full_name);
1424 WF_ENGINE.setitemattrtext(p_item_type,
1425 p_item_key,
1426 'APPROVAL_CREATOR_DISPLAY_NAME',
1427 l_creator_full_name);
1428 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CURRENT_PERSON_ID', p_person_id);
1429 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CREATOR_PERSON_ID', l_creator_person_id);
1430
1431 end if;
1432
1433
1434
1435 FOR a IN csr_supervisor_id LOOP
1436 l_supervisor_id := a.supervisor_id;
1437 l_supervisor_full_name := a.full_name;
1438 END LOOP;
1439
1440
1441 FOR b IN csr_supervisor_user LOOP
1442 l_supervisor_username := b.user_name;
1443 END LOOP;
1444
1445 hr_utility.set_location('after supervisor cursor'||l_proc, 20);
1446
1447
1448 wf_engine.setitemattrtext
1449 (p_item_type,
1450 p_item_key,
1451 'SUPERVISOR_USERNAME',
1452 l_supervisor_username);
1453 hr_utility.set_location('after supervisor username'||l_supervisor_username, 20);
1454 if p_item_type <> 'OTWF' then
1455 wf_engine.setitemattrtext
1456 (p_item_type,
1457 p_item_key,
1458 'SUPERVISOR_DISPLAY_NAME',
1459 l_supervisor_full_name);
1460
1461 wf_engine.setitemattrnumber
1462 (p_item_type,
1463 p_item_key,
1464 'SUPERVISOR_ID',
1465 l_supervisor_id);
1466 end if;
1467
1468 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
1469
1470 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
1471 p_orig_system_id => p_person_id,
1472 p_name =>l_role_name,
1473 p_display_name =>l_role_display_name);
1474
1475
1476 WF_ENGINE.SetItemOwner(
1477 itemtype => p_item_type,
1478 itemkey =>p_item_key,
1479 owner =>l_role_name);
1480
1481 hr_utility.trace ('after setowner ' ||l_role_display_name);
1482
1483 hr_utility.set_location('Leaving:'||l_proc, 5);
1484
1485 end set_wf_item_attr;
1486
1487 procedure init_assessment_wf(p_person_id in number,
1488 p_attempt_id in varchar2)
1489
1490 is
1491
1492 l_proc varchar2(72) := g_package||'init_assessment_wf';
1493
1494 cursor get_test_info is
1495 select olb.name,oa.raw_score,oa.time
1496 -- ,oa.event_id
1497 from ota_learning_objects olb, ota_attempts oa
1498 where olb.learning_object_id = oa.learning_object_id
1499 -- and oa.attempt_id = op.attempt_id
1500 and oa.attempt_id =p_attempt_id;
1501
1502
1503 l_item_key wf_items.item_key%type;
1504 l_item_type wf_items.item_type%type := 'OTWF';
1505 l_process wf_activities.name%type := 'OTA_ASSESSMENT_NTF_JSP_PRC';
1506
1507 l_title ota_learning_objects.name%type;
1508 l_score varchar2(100);
1509
1510 l_time ota_events.course_start_time%type;
1511 l_format_time varchar2(50);
1512
1513
1514
1515 begin
1516 hr_utility.set_location('Entering:'||l_proc, 5);
1517
1518 open get_test_info;
1519 fetch get_test_info into l_title,l_score,l_time;
1520 --l_end_time,l_location_id;
1521 close get_test_info;
1522
1523 if l_score = '-1000' then
1524 l_score := null;
1525 end if;
1526
1527 l_format_time := ota_utility.get_test_time(l_time);
1528
1529 hr_utility.trace ('title ' ||l_title);
1530
1531 -- Get the next item key from the sequence
1532 select hr_workflow_item_key_s.nextval
1533 into l_item_key
1534 from sys.dual;
1535 -- get item key for the process
1536 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1537 hr_utility.trace ('item key ' ||l_item_key);
1538 -- set creator,current and supervisor name
1539 set_wf_item_attr(p_person_id => p_person_id,
1540 p_item_type => l_item_type,
1541 p_item_key => l_item_key);
1542
1543
1544
1545 --WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
1546
1547
1548 --Enh 5606090: Language support for Assessment Notification.
1549 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'EVENT_TITLE', p_attempt_id);
1550
1551 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'LINE_NUMBER', l_score);
1552 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'OTA_START_TIME', l_format_time);
1553
1554
1555 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1556
1557
1558 hr_utility.set_location('Leaving:'||l_proc, 5);
1559
1560 exception
1561 when others then
1562
1563 raise;
1564
1565
1566 end init_assessment_wf;
1567
1568 -- ----------------------------------------------------------------------------
1569 -- |----------------------< initialize_cert_ntf_wf >-------------------------|
1570 -- ----------------------------------------------------------------------------
1571
1572 -- This wf would be used for OTA_CERTIFICATION_NTF_JSP_PRC
1573 -- Called from alert
1574
1575 Procedure initialize_cert_ntf_wf(p_item_type in wf_items.item_type%type,
1576 p_person_id in number default null,
1577 p_certification_id in ota_certifications_b.certification_id%type,
1578 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1579 p_cert_ntf_type in varchar2) is
1580
1581
1582 cursor get_certification_info is
1583 Select cet.name
1584 ,cet.end_date_comments
1585 ,ceb.end_date_active
1586 From ota_certifications_b ceb
1587 ,ota_certifications_tl cet
1588 Where
1589 ceb.certification_id = cet.certification_id
1590 and cet.language = userenv('LANG')
1591 and ceb.certification_id = p_certification_id;
1592
1593 l_certification_name ota_certifications_tl.name%type;
1594 l_end_date_comments ota_certifications_tl.end_date_comments%type;
1595 l_end_date_active ota_certifications_b.end_date_active%type;
1596
1597 l_process wf_activities.name%type := 'OTA_CERTIFICATION_NTF_JSP_PRC';
1598 l_item_key wf_items.item_key%type;
1599
1600 begin
1601
1602 open get_certification_info;
1603 fetch get_certification_info into l_certification_name, l_end_date_comments, l_end_date_active;
1604 close get_certification_info;
1605
1606 if ( l_end_date_comments is null ) then
1607 l_end_date_comments := '';
1608 end if;
1609
1610 -- Get the next item key from the sequence
1611 select hr_workflow_item_key_s.nextval
1612 into l_item_key
1613 from sys.dual;
1614
1615 -- get item key for the process
1616 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
1617
1618 -- set creator,current and supervisor name
1619 set_wf_item_attr(p_person_id => p_person_id,
1620 p_item_type => p_item_type,
1621 p_item_key => l_item_key);
1622
1623 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_cert_ntf_type);
1624 --Enh 5606090: Language support for Certification Notification.
1625 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_CERTIFICATION_NAME', p_certification_id);
1626 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', l_end_date_active);
1627 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_CERTIFICATION_ID', p_certification_id);
1628 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_CERT_PRD_ENROLLMENT_ID', p_cert_prd_enrollment_id);
1629 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'PERSON_ID', p_person_id);
1630 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_COMMENTS', l_end_date_comments);
1631
1632 WF_ENGINE.STARTPROCESS(p_item_type, l_item_key);
1633
1634 end initialize_cert_ntf_wf;
1635
1636 --
1637 Procedure process_cert_alert(
1638 ERRBUF OUT NOCOPY VARCHAR2,
1639 RETCODE OUT NOCOPY VARCHAR2,
1640 p_cert_ntf_type in varchar2) is
1641
1642 l_proc varchar2(72) := g_package || ' process_cert_alert';
1643
1644 l_certification_id ota_certifications_b.certification_id%type;
1645 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
1646 l_person_id ota_cert_enrollments.person_id%type;
1647
1648 l_item_type VARCHAR2(5) := 'OTWF';
1649
1650 --get all enrollment records in current period for reminder ntf
1651 cursor get_data_for_reminder_ntf is
1652 Select ceb.certification_id
1653 ,prd.cert_prd_enrollment_id
1654 ,enr.person_id
1655 From ota_certifications_b ceb
1656 ,ota_cert_enrollments enr
1657 ,ota_cert_prd_enrollments prd
1658 Where
1659 ceb.certification_id = enr.certification_id
1660 and enr.cert_enrollment_id = prd.cert_enrollment_id
1661 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1662 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1663 and trunc(sysdate) between nvl(trunc(prd.cert_period_start_date), trunc(sysdate)) and
1664 nvl(trunc(prd.cert_period_end_date), trunc(sysdate))
1665 and prd.period_status_code <> 'COMPLETED'
1666 and enr.person_id is not null
1667 and ceb.notify_days_before_expire is not null
1668 and (trunc(sysdate) + ceb.notify_days_before_expire) = trunc(prd.cert_period_end_date);
1669
1670 --get all enrollment records in last period for expiration ntf
1671 cursor get_data_for_expiration_ntf is
1672 Select ceb.certification_id
1673 ,prd.cert_prd_enrollment_id
1674 ,enr.person_id
1675 From ota_certifications_b ceb
1676 ,ota_cert_enrollments enr
1677 ,ota_cert_prd_enrollments prd
1678 Where
1679 ceb.certification_id = enr.certification_id
1680 and enr.cert_enrollment_id = prd.cert_enrollment_id
1681 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1682 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1683 and prd.period_status_code <> 'COMPLETED'
1684 and trunc(prd.cert_period_end_date) + 1 <= trunc(sysdate)
1685 and enr.person_id is not null;
1686
1687 --get all enrollment records in current period for completion ntf
1688 cursor get_data_for_completion_ntf is
1689 Select ceb.certification_id
1690 ,prd.cert_prd_enrollment_id
1691 ,enr.person_id
1692 From ota_certifications_b ceb
1693 ,ota_cert_enrollments enr
1694 ,ota_cert_prd_enrollments prd
1695 Where
1696 ceb.certification_id = enr.certification_id
1697 and enr.cert_enrollment_id = prd.cert_enrollment_id
1698 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1699 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1700 and trunc(sysdate) between nvl(trunc(prd.cert_period_start_date), trunc(sysdate)) and
1701 nvl(trunc(prd.cert_period_end_date), trunc(sysdate))
1702 and prd.period_status_code = 'COMPLETED'
1703 and trunc(prd.completion_date) = trunc(sysdate)
1704 and enr.person_id is not null;
1705
1706 --get all enrollment records for renewal ntf
1707 cursor get_data_for_renewal_ntf is
1708 Select ceb.certification_id
1709 ,prd.cert_prd_enrollment_id
1710 ,enr.person_id
1711 From ota_certifications_b ceb
1712 ,ota_cert_enrollments enr
1713 ,ota_cert_prd_enrollments prd
1714 Where
1715 ceb.certification_id = enr.certification_id
1716 and enr.cert_enrollment_id = prd.cert_enrollment_id
1717 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1718 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1719 and trunc(enr.earliest_enroll_date) = trunc(sysdate)
1720 and enr.person_id is not null;
1721
1722 --get all enrollment records for cancellation ntf
1723 cursor get_data_for_cancellation_ntf is
1724 Select ceb.certification_id
1725 ,enr.person_id
1726 From ota_certifications_b ceb
1727 ,ota_cert_enrollments enr
1728 Where
1729 ceb.certification_id = enr.certification_id
1730 and enr.person_id is not null
1731 and (trunc(sysdate) + nvl(ceb.notify_days_before_expire, 0)) = trunc(ceb.end_date_active);
1732
1733 begin
1734 hr_utility.set_location('Entering:'||l_proc, 5);
1735
1736 if p_cert_ntf_type = 'Completion' OR p_cert_ntf_type = 'All' THEN
1737 for prd_enr_rem in get_data_for_reminder_ntf
1738 Loop
1739 initialize_cert_ntf_wf(p_item_type => l_item_type,
1740 p_person_id => prd_enr_rem.person_id,
1741 p_certification_id => prd_enr_rem.certification_id,
1742 p_cert_prd_enrollment_id => prd_enr_rem.cert_prd_enrollment_id,
1743 p_cert_ntf_type => 'CERT_REMINDER');
1744 End Loop;
1745 end if;
1746
1747 /* Might need to call API to set Certification Enrollment Status to EXPIRED before sending the notification */
1748 if p_cert_ntf_type ='Expiry' OR p_cert_ntf_type = 'All' THEN
1749 for prd_enr_exp in get_data_for_expiration_ntf
1750 Loop
1751 initialize_cert_ntf_wf(p_item_type => l_item_type,
1752 p_person_id => prd_enr_exp.person_id,
1753 p_certification_id => prd_enr_exp.certification_id,
1754 p_cert_prd_enrollment_id => prd_enr_exp.cert_prd_enrollment_id,
1755 p_cert_ntf_type => 'CERT_EXPIRATION');
1756 End Loop;
1757 end if;
1758
1759 /* Needs to be moved to API call
1760 if p_cert_ntf_type ='' OR p_cert_ntf_type = 'ALL' THEN
1761 for prd_enr_cmp in get_data_for_completion_ntf
1762 Loop
1763 initialize_cert_ntf_wf(p_item_type => l_item_type,
1764 p_person_id => prd_enr_cmp.person_id,
1765 p_certification_id => prd_enr_cmp.certification_id,
1766 p_cert_prd_enrollment_id => prd_enr_cmp.cert_prd_enrollment_id,
1767 p_cert_ntf_type => 'CERT_COMPLETION');
1768 End Loop;
1769 end if;
1770 */
1771
1772 if p_cert_ntf_type ='Renewal' OR p_cert_ntf_type = 'All' THEN
1773 for prd_enr_rnw in get_data_for_renewal_ntf
1774 Loop
1775 initialize_cert_ntf_wf(p_item_type => l_item_type,
1776 p_person_id => prd_enr_rnw.person_id,
1777 p_certification_id => prd_enr_rnw.certification_id,
1778 p_cert_prd_enrollment_id => prd_enr_rnw.cert_prd_enrollment_id,
1779 p_cert_ntf_type => 'CERT_RENEWAL');
1780 End Loop;
1781 end if;
1782
1783 if p_cert_ntf_type ='Cancellation' OR p_cert_ntf_type = 'All' THEN
1784 for prd_enr_can in get_data_for_cancellation_ntf
1785 Loop
1786 initialize_cert_ntf_wf(p_item_type => l_item_type,
1787 p_person_id => prd_enr_can.person_id,
1788 p_certification_id => prd_enr_can.certification_id,
1789 p_cert_prd_enrollment_id => null,
1790 p_cert_ntf_type => 'CERT_CANCELLATION' );
1791 End Loop;
1792 end if;
1793 hr_utility.set_location('Leaving:'||l_proc, 5);
1794 EXCEPTION
1795 when others then
1796 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1797 ||','||SUBSTR(SQLERRM, 1, 500));
1798 end process_cert_alert;
1799 --
1800 Procedure init_forum_notif(p_Forum_id in ota_forum_messages.forum_id%type,
1801 p_Forum_message_id in ota_forum_messages.forum_message_id%type)
1802 is
1803
1804 l_proc varchar2(72) := g_package||'init_forum_notif';
1805
1806 l_item_key wf_items.item_key%type;
1807 l_item_type wf_items.item_type%type := 'OTWF';
1808 l_process wf_activities.name%type := 'OTA_FORUM_NTF_JSP_PRC';
1809
1810 cursor get_message_details
1811 is
1812 select oft.name,fth.subject,ofm.person_id,ofm.contact_id,
1813 ofm.creation_date,ofm.message_body
1814 from ota_forum_messages ofm ,ota_forum_threads fth ,ota_forums_tl oft
1815 where oft.forum_id = ofm.forum_id
1816 and ofm.forum_thread_id = fth.forum_thread_id
1817 and ofm.forum_message_id = p_Forum_message_id
1818 and oft.language= USERENV('LANG');
1819
1820 cursor get_frm_subscriber
1821 is
1822 select person_id
1823 from ota_frm_notif_subscribers
1824 where forum_id = p_forum_id
1825 and person_id is not null;
1826
1827
1828 l_forum_name ota_forums_tl.name%type;
1829 l_subject ota_forum_threads.subject%type;
1830 l_person_id ota_forum_messages.person_id%type;
1831 l_contact_id ota_forum_messages.contact_id%type;
1832 l_creation_date varchar2(50);
1833 l_message_body ota_forum_messages.message_body%type;
1834 l_author_name varchar2(300);
1835
1836
1837
1838
1839 begin
1840 hr_utility.set_location('Entering:'||l_proc, 5);
1841
1842 open get_message_details;
1843 fetch get_message_details into l_forum_name,l_subject, l_person_id,l_contact_id,
1844 l_creation_date, l_message_body;
1845 close get_message_details;
1846
1847 -- get author name from person_id or contact_id
1848 l_author_name := ota_utility.get_learner_name(p_person_id => l_person_id,
1849 p_customer_id => null,
1850 p_contact_id => l_contact_id);
1851
1852 for rec in get_frm_subscriber
1853
1854 Loop
1855
1856 -- Get the next item key from the sequence
1857 select hr_workflow_item_key_s.nextval
1858 into l_item_key
1859 from sys.dual;
1860 -- get item key for the process
1861 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1862
1863 -- set creator,current and supervisor name
1864 set_wf_item_attr(p_person_id => rec.person_id,
1865 p_item_type => l_item_type,
1866 p_item_key => l_item_key);
1867 --Enh 5606090: Language support for Forum Notification.
1868 wf_engine.setitemattrtext(l_item_type,l_item_key, 'COURSE_NAME' ,p_Forum_message_id);
1869 wf_engine.setitemattrtext(l_item_type,l_item_key, 'EVENT_TITLE' ,l_subject);
1870 wf_engine.setitemattrtext(l_item_type,l_item_key, 'SECTION_NAME' ,l_message_body);
1871
1872 wf_engine.setitemattrtext(l_item_type,l_item_key, 'LP_CREATOR_NAME' ,l_author_name);
1873 wf_engine.setitemattrtext(l_item_type,l_item_key, 'SECTION_NAME' ,l_message_body);
1874
1875 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1876
1877 end loop;
1878
1879 hr_utility.set_location('Leaving:'||l_proc, 10);
1880
1881 end init_forum_notif;
1882
1883
1884
1885
1886 Procedure send_event_beginning_ntf(
1887 ERRBUF OUT NOCOPY VARCHAR2,
1888 RETCODE OUT NOCOPY VARCHAR2) IS
1889
1890 l_proc varchar2(72) := g_package||' send_event_beginning_ntf';
1891 BEGIN
1892 ota_initialization_wf.Initialize_auto_wf(
1893 p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC'
1894 ,p_item_type => 'OTWF'
1895 ,p_event_fired => 'CLASS_START');
1896 EXCEPTION
1897 when others then
1898 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1899 ||','||SUBSTR(SQLERRM, 1, 500));
1900 END send_event_beginning_ntf;
1901
1902
1903 Procedure send_instructor_reminder_ntf(
1904 ERRBUF OUT NOCOPY VARCHAR2,
1905 RETCODE OUT NOCOPY VARCHAR2) IS
1906
1907 l_proc varchar2(72) := g_package||' send_instructor_reminder_ntf';
1908 BEGIN
1909 ota_initialization_wf.Initialize_auto_wf(
1910 p_process => 'OTA_INSTRUCTOR_NTF_JSP_PRC'
1911 ,p_item_type => 'OTWF'
1912 ,p_event_fired => 'INSTRUCTOR_REMIND');
1913 EXCEPTION
1914 when others then
1915 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1916 ||','||SUBSTR(SQLERRM, 1, 500));
1917 END send_instructor_reminder_ntf;
1918
1919 procedure init_course_eval_notif(p_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type) is
1920
1921 l_proc varchar2(72) := g_package||'init_course_eval_notif';
1922
1923 l_item_key wf_items.item_key%type;
1924 l_item_type wf_items.item_type%type := 'OTWF';
1925 l_process wf_activities.name%type := 'OTA_COURSE_EVAL_PRC';
1926
1927 l_person_id ota_forum_messages.person_id%type;
1928 l_contact_id ota_forum_messages.contact_id%type;
1929 l_event_id ota_events.event_id%type;
1930 l_status_type ota_booking_status_types.type%type;
1931
1932 cursor csr_booking_status is
1933 SELECT bst.Type, tdb.delegate_person_id, tdb.contact_id, tdb.event_id
1934 FROM OTA_DELEGATE_BOOKINGS tdb,
1935 OTA_BOOKING_STATUS_TYPES bst
1936 WHERE tdb.booking_id = p_booking_id
1937 AND bst.booking_status_type_id = tdb.booking_status_type_id;
1938
1939 begin
1940 hr_utility.set_location('Entering:'||l_proc, 5);
1941
1942 open csr_booking_status;
1943 fetch csr_booking_status into l_status_type,l_person_id, l_contact_id,l_event_id;
1944 close csr_booking_status;
1945
1946 select hr_workflow_item_key_s.nextval into l_item_key from sys.dual;
1947
1948 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1949 set_wf_item_attr(p_person_id => l_person_id, p_item_type => l_item_type, p_item_key => l_item_key);
1950 wf_engine.setitemattrtext(l_item_type,l_item_key, 'EVENT_ID' ,l_event_id);
1951 wf_engine.setitemattrtext(l_item_type,l_item_key, 'STATUS' ,l_status_type);
1952 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1953
1954 hr_utility.set_location('Leaving:'||l_proc, 10);
1955
1956 end init_course_eval_notif;
1957
1958 procedure get_course_eval_status ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1959 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1960 actid IN NUMBER,
1961 funcmode IN VARCHAR2,
1962 resultout OUT nocopy VARCHAR2 ) is
1963
1964 l_status_type ota_booking_status_types.type%type;
1965 begin
1966 if(funcmode = 'RUN') then
1967 l_status_type := wf_engine.getItemAttrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'STATUS');
1968
1969 If (l_status_type is not null and upper(l_status_type) = 'E') then
1970 resultout := 'COMPLETE:PENDING_EVAL';
1971 else
1972 resultout := 'COMPLETE:ATTENDED_EVAL';
1973 end if;
1974 else
1975 if(funcmode='CANCEL') then
1976 resultout := 'COMPLETE';
1977 end if;
1978 end if;
1979
1980 end get_course_eval_status;
1981
1982 procedure get_course_eval_del_mode ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1983 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1984 actid IN NUMBER,
1985 funcmode IN VARCHAR2,
1986 resultout OUT nocopy VARCHAR2 ) is
1987
1988 l_event_id varchar2(80);
1989 l_synchronous_flag ota_category_usages.synchronous_flag%type;
1990 l_online_flag ota_category_usages.online_flag%type;
1991
1992 CURSOR delivery_mode(p_event_id varchar2) IS
1993 Select OCU.synchronous_flag, OCU.online_flag
1994 From ota_events OEV,
1995 ota_offerings OFR,
1996 ota_category_usages OCU
1997 Where OFR.offering_id = OEV.parent_offering_id
1998 And OCU.category_usage_id = OFR.delivery_mode_id
1999 And OEV.event_id = p_event_id;
2000
2001 begin
2002 if(funcmode = 'RUN') then
2003 l_event_id := wf_engine.getItemAttrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'EVENT_ID');
2004
2005 OPEN delivery_mode(l_event_id);
2006 FETCH delivery_mode INTO l_synchronous_flag, l_online_flag;
2007 CLOSE delivery_mode;
2008
2009 if upper(l_online_flag) = 'Y' then
2010 resultout := 'COMPLETE:ONLINE';
2011 else
2012 resultout := 'COMPLETE:OFFLINE';
2013 end if;
2014 else
2015 if(funcmode='CANCEL') then
2016 resultout := 'COMPLETE';
2017 end if;
2018 end if;
2019
2020 end get_course_eval_del_mode;
2021
2022 procedure get_class_name(document_id in varchar2,
2023 display_type in varchar2,
2024 document in out nocopy varchar2,
2025 document_type in out nocopy varchar2) is
2026 CURSOR csr_get_class_name IS
2027 SELECT title FROM ota_events_tl
2028 WHERE to_char(event_id) = document_id AND language = USERENV('LANG');
2029
2030 l_class_name varchar2(80);
2031 begin
2032 OPEN csr_get_class_name;
2033 FETCH csr_get_class_name INTO l_class_name;
2034 CLOSE csr_get_class_name;
2035
2036 document := l_class_name;
2037
2038 end get_class_name;
2039
2040 procedure RAISE_BUSINESS_EVENT(
2041 p_eventid in ota_Events.event_id%type,
2042 p_event_fired in varchar2
2043 )
2044 is
2045 l_proc varchar2(72) := g_package||'RAISE_BUSINESS_EVENT';
2046
2047 l_item_key wf_items.item_key%type;
2048
2049 l_title ota_events_tl.title%type;
2050 l_start_date varchar2(100);
2051 l_end_date varchar2(100);
2052 l_start_time ota_events.course_start_time%type;
2053 l_end_time ota_events.course_start_time%type;
2054 l_location_id ota_events.location_id %type;
2055 l_training_center_id ota_events.training_center_id %type;
2056 l_event_type ota_events.event_type%type;
2057
2058 l_location_name hr_locations_all_tl.location_code%type;
2059 l_training_center hr_all_organization_units.name%TYPE;
2060 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
2061
2062 l_booking_id ota_delegate_bookings.booking_id%type;
2063 l_person_id number(15);
2064
2065 l_event_data clob;
2066 l_text varchar2(2000);
2067
2068
2069 cursor get_event_info
2070 is
2071 select evt.title,oev.course_start_date,oev.course_end_date,
2072 oev.course_start_time, oev.course_end_time,
2073 oev.location_id,oev.training_center_id
2074 from ota_events_tl evt, ota_events oev
2075 where evt.event_id =oev.event_id
2076 and oev.event_id = p_eventid
2077 and evt.language=USERENV('LANG');
2078
2079 -- get all the person's enrolled into the event
2080 cursor get_booking_info
2081 is
2082 select odb.booking_id , odb.delegate_person_id, odb.delegate_contact_id
2083 from ota_delegate_bookings odb, ota_booking_status_types bst
2084 where odb.event_id =p_eventid
2085 and odb.booking_status_type_id = bst.booking_status_type_id
2086 and bst.type in ('P','W','R');
2087
2088
2089 begin
2090
2091 open get_event_info;
2092 fetch get_event_info into l_title,l_start_date,l_end_date,l_start_time,
2093 l_end_time,l_location_id,l_training_center_id;
2094 close get_event_info;
2095
2096 if (p_event_fired = 'oracle.apps.ota.api.event_api.update_location' or
2097 p_event_fired = 'oracle.apps.ota.api.event_api.update_training_center' or
2098 p_event_fired = 'oracle.apps.ota.api.event_api.update_trng_cntr_and_location')then
2099
2100
2101
2102 for rec in get_booking_info
2103 Loop
2104
2105 -- Get the next item key from the sequence
2106 select hr_workflow_item_key_s.nextval
2107 into l_item_key
2108 from sys.dual;
2109
2110 -- get location
2111 l_location_name := ota_general.get_Location_code(l_location_id);
2112
2113 -- get Traininig center
2114 l_training_center := ota_general.get_training_center(l_training_center_id);
2115
2116
2117 -- raise the event with the event data
2118 -- start BE
2119
2120 -- build the xml data for the event
2121 --
2122 dbms_lob.createTemporary(l_event_data,false,dbms_lob.call);
2123 l_text:='<?xml version =''1.0'' encoding =''ASCII''?>';
2124 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2125
2126 l_text:='<class_change>';
2127 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2128
2129 l_text:='<delegate_person_id>';
2130 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_person_id);
2131 l_text:=l_text||'</delegate_person_id>';
2132 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2133
2134 l_text:='<delegate_contact_id>';
2135 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_contact_id);
2136 l_text:=l_text||'</delegate_contact_id>';
2137 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2138
2139 l_text:='<booking_id>';
2140 l_text:=l_text||fnd_number.number_to_canonical(rec.booking_id);
2141 l_text:=l_text||'</booking_id>';
2142 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2143
2144 l_text:='<location_id>';
2145 l_text:=l_text||fnd_number.number_to_canonical(l_location_id);
2146 l_text:=l_text||'</location_id>';
2147 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2148
2149 l_text:='<location_name>';
2150 l_text:=l_text||irc_utilities_pkg.removeTags(l_location_name);
2151 l_text:=l_text||'</location_name>';
2152 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2153
2154 l_text:='<training_center_id>';
2155 l_text:=l_text||fnd_number.number_to_canonical(l_training_center_id);
2156 l_text:=l_text||'</training_center_id>';
2157 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2158
2159 l_text:='<training_center_name>';
2160 l_text:=l_text||irc_utilities_pkg.removeTags(l_training_center);
2161 l_text:=l_text||'</training_center_name>';
2162 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2163
2164 l_text:='<event_id>';
2165 l_text:=l_text||fnd_number.number_to_canonical(p_eventid);
2166 l_text:=l_text||'</event_id>';
2167 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2168
2169 l_text:='<ota_ntf_event_fired>';
2170 l_text:=l_text||irc_utilities_pkg.removeTags(p_event_fired);
2171 l_text:=l_text||'</ota_ntf_event_fired>';
2172 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2173
2174 l_text:='<target_date>';
2175 l_text:=l_text||fnd_date.date_to_canonical(l_start_date);
2176 l_text:=l_text||'</target_date>';
2177 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2178
2179 l_text:='<completion_date>';
2180 l_text:=l_text||fnd_date.date_to_canonical(l_end_date);
2181 l_text:=l_text||'</completion_date>';
2182 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2183
2184 l_text:='<ota_start_time>';
2185 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_start_time,'00:00'));
2186 l_text:=l_text||'</ota_start_time>';
2187 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2188
2189 l_text:='<ota_end_time>';
2190 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_end_time,'23:59'));
2191 l_text:=l_text||'</ota_end_time>';
2192 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2193
2194 l_text:='</class_change>';
2195 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2196
2197
2198 wf_event.raise(p_event_name=>p_event_fired
2199 ,p_event_key=>l_item_key
2200 ,p_event_data=>l_event_data);
2201
2202 -- end BE
2203
2204
2205 end loop;
2206
2207 elsif p_event_fired = 'oracle.apps.ota.api.event_api.update_class_schedule' then
2208
2209 for rec in get_booking_info
2210 Loop
2211
2212 -- Get the next item key from the sequence
2213 select hr_workflow_item_key_s.nextval
2214 into l_item_key
2215 from sys.dual;
2216
2217 -- get location
2218 l_location_name := ota_general.get_Location_code(l_location_id);
2219
2220 -- build the xml data for the event
2221 --
2222 dbms_lob.createTemporary(l_event_data,false,dbms_lob.call);
2223 l_text:='<?xml version =''1.0'' encoding =''ASCII''?>';
2224 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2225
2226 l_text:='<class_change>';
2227 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2228
2229 l_text:='<delegate_person_id>';
2230 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_person_id);
2231 l_text:=l_text||'</delegate_person_id>';
2232 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2233
2234 l_text:='<delegate_contact_id>';
2235 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_contact_id);
2236 l_text:=l_text||'</delegate_contact_id>';
2237 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2238
2239 l_text:='<booking_id>';
2240 l_text:=l_text||fnd_number.number_to_canonical(rec.booking_id);
2241 l_text:=l_text||'</booking_id>';
2242 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2243
2244 l_text:='<location_id>';
2245 l_text:=l_text||fnd_number.number_to_canonical(l_location_id);
2246 l_text:=l_text||'</location_id>';
2247 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2248
2249 l_text:='<location_name>';
2250 l_text:=l_text||irc_utilities_pkg.removeTags(l_location_name);
2251 l_text:=l_text||'</location_name>';
2252 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2253
2254 l_text:='<event_id>';
2255 l_text:=l_text||fnd_number.number_to_canonical(p_eventid);
2256 l_text:=l_text||'</event_id>';
2257 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2258
2259 l_text:='<ota_ntf_event_fired>';
2260 l_text:=l_text||irc_utilities_pkg.removeTags(p_event_fired);
2261 l_text:=l_text||'</ota_ntf_event_fired>';
2262 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2263
2264 l_text:='<target_date>';
2265 l_text:=l_text||fnd_date.date_to_canonical(l_start_date);
2266 l_text:=l_text||'</target_date>';
2267 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2268
2269 l_text:='<completion_date>';
2270 l_text:=l_text||fnd_date.date_to_canonical(l_end_date);
2271 l_text:=l_text||'</completion_date>';
2272 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2273
2274 l_text:='<ota_start_time>';
2275 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_start_time,'00:00'));
2276 l_text:=l_text||'</ota_start_time>';
2277 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2278
2279 l_text:='<ota_end_time>';
2280 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_end_time,'23:59'));
2281 l_text:=l_text||'</ota_end_time>';
2282 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2283
2284 l_text:='</class_change>';
2285 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2286
2287
2288 wf_event.raise(p_event_name=>p_event_fired
2289 ,p_event_key=>l_item_key
2290 ,p_event_data=>l_event_data);
2291
2292 end loop;
2293
2294 end if;
2295
2296 end RAISE_BUSINESS_EVENT;
2297
2298 end ota_initialization_wf;