[Home] [Help]
PACKAGE BODY: APPS.OTA_INITIALIZATION_WF
Source
1 Package body ota_initialization_wf as
2 /* $Header: ottomint.pkb 120.58.12020000.3 2012/06/29 11:19:18 rpahune 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 date;
688 l_end_date date;
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.setitemattrdate(p_item_type, l_item_key, 'TARGET_DATE', l_start_date);
897 WF_ENGINE.setitemattrdate(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 date;
953 l_end_date date;
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.setitemattrdate(p_item_type, l_item_key, 'TARGET_DATE', l_start_date);
1030 WF_ENGINE.setitemattrdate(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.setitemattrdate(p_item_type, l_item_key, 'TARGET_DATE', l_start_date);
1074 WF_ENGINE.setitemattrdate(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 AND asg.assignment_type in ('E', 'A', 'C'); --Bug#8614003
1368
1369 -- get supervisor full name
1370 CURSOR csr_supervisor_user IS
1371 SELECT user_name
1372 FROM fnd_user
1373 WHERE employee_id= l_supervisor_id
1374 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
1375
1376
1377
1378 begin
1379 hr_utility.set_location('Entering:'||l_proc, 5);
1380
1381 OPEN curr_per_info;
1382 FETCH curr_per_info INTO l_current_user_id, l_current_username;
1383 CLOSE curr_per_info;
1384
1385 OPEN C_USER;
1386 FETCH C_USER INTO l_creator_person_id;
1387 CLOSE C_USER;
1388
1389
1390
1391
1392 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CURRENT_PERSON_USERNAME', l_current_username);
1393 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CREATOR_PERSON_USERNAME', l_creator_username);
1394
1395 hr_utility.trace ('Current username ' ||l_current_username);
1396
1397 open csr_person_name(l_creator_person_id);
1398 fetch csr_person_name into l_creator_full_name;
1399 close csr_person_name;
1400
1401 open csr_person_name(p_person_id);
1402 fetch csr_person_name into l_current_full_name;
1403 close csr_person_name;
1404
1405
1406 WF_ENGINE.setitemattrtext(p_item_type,
1407 p_item_key,
1408 'CURRENT_PERSON_DISPLAY_NAME',
1409 l_current_full_name);
1410
1411 --for forum notification Login person can be contact
1412 if p_item_type = 'OTWF' and l_creator_full_name is not null then
1413
1414
1415 WF_ENGINE.setitemattrtext(p_item_type,
1416 p_item_key,
1417 'LP_CREATOR_NAME',
1418 l_creator_full_name);
1419
1420 elsif p_item_type <> 'OTWF' then
1421
1422 WF_ENGINE.setitemattrtext(p_item_type,
1423 p_item_key,
1424 'CREATOR_PERSON_DISPLAY_NAME',
1425 l_creator_full_name);
1426 WF_ENGINE.setitemattrtext(p_item_type,
1427 p_item_key,
1428 'APPROVAL_CREATOR_DISPLAY_NAME',
1429 l_creator_full_name);
1430 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CURRENT_PERSON_ID', p_person_id);
1431 WF_ENGINE.setitemattrtext(p_item_type, p_item_key, 'CREATOR_PERSON_ID', l_creator_person_id);
1432
1433 end if;
1434
1435
1436
1437 FOR a IN csr_supervisor_id LOOP
1438 l_supervisor_id := a.supervisor_id;
1439 l_supervisor_full_name := a.full_name;
1440 END LOOP;
1441
1442
1443 FOR b IN csr_supervisor_user LOOP
1444 l_supervisor_username := b.user_name;
1445 END LOOP;
1446
1447 hr_utility.set_location('after supervisor cursor'||l_proc, 20);
1448
1449
1450 wf_engine.setitemattrtext
1451 (p_item_type,
1452 p_item_key,
1453 'SUPERVISOR_USERNAME',
1454 l_supervisor_username);
1455 hr_utility.set_location('after supervisor username'||l_supervisor_username, 20);
1456 if p_item_type <> 'OTWF' then
1457 wf_engine.setitemattrtext
1458 (p_item_type,
1459 p_item_key,
1460 'SUPERVISOR_DISPLAY_NAME',
1461 l_supervisor_full_name);
1462
1463 wf_engine.setitemattrnumber
1464 (p_item_type,
1465 p_item_key,
1466 'SUPERVISOR_ID',
1467 l_supervisor_id);
1468 end if;
1469
1470 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
1471
1472 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
1473 p_orig_system_id => p_person_id,
1474 p_name =>l_role_name,
1475 p_display_name =>l_role_display_name);
1476
1477
1478 WF_ENGINE.SetItemOwner(
1479 itemtype => p_item_type,
1480 itemkey =>p_item_key,
1481 owner =>l_role_name);
1482
1483 hr_utility.trace ('after setowner ' ||l_role_display_name);
1484
1485 hr_utility.set_location('Leaving:'||l_proc, 5);
1486
1487 end set_wf_item_attr;
1488
1489 procedure init_assessment_wf(p_person_id in number,
1490 p_attempt_id in varchar2)
1491
1492 is
1493
1494 l_proc varchar2(72) := g_package||'init_assessment_wf';
1495
1496 cursor get_test_info is
1497 select olb.name,oa.raw_score,oa.time
1498 -- ,oa.event_id
1499 from ota_learning_objects olb, ota_attempts oa
1500 where olb.learning_object_id = oa.learning_object_id
1501 -- and oa.attempt_id = op.attempt_id
1502 and oa.attempt_id =p_attempt_id;
1503
1504
1505 l_item_key wf_items.item_key%type;
1506 l_item_type wf_items.item_type%type := 'OTWF';
1507 l_process wf_activities.name%type := 'OTA_ASSESSMENT_NTF_JSP_PRC';
1508
1509 l_title ota_learning_objects.name%type;
1510 l_score varchar2(100);
1511
1512 l_time ota_events.course_start_time%type;
1513 l_format_time varchar2(50);
1514
1515
1516
1517 begin
1518 hr_utility.set_location('Entering:'||l_proc, 5);
1519
1520 open get_test_info;
1521 fetch get_test_info into l_title,l_score,l_time;
1522 --l_end_time,l_location_id;
1523 close get_test_info;
1524
1525 if l_score = '-1000' then
1526 l_score := null;
1527 end if;
1528
1529 l_format_time := ota_utility.get_test_time(l_time);
1530
1531 hr_utility.trace ('title ' ||l_title);
1532
1533 -- Get the next item key from the sequence
1534 select hr_workflow_item_key_s.nextval
1535 into l_item_key
1536 from sys.dual;
1537 -- get item key for the process
1538 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1539 hr_utility.trace ('item key ' ||l_item_key);
1540 -- set creator,current and supervisor name
1541 set_wf_item_attr(p_person_id => p_person_id,
1542 p_item_type => l_item_type,
1543 p_item_key => l_item_key);
1544
1545
1546
1547 --WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_event_fired);
1548
1549
1550 --Enh 5606090: Language support for Assessment Notification.
1551 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'EVENT_TITLE', p_attempt_id);
1552
1553 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'LINE_NUMBER', l_score);
1554 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'OTA_START_TIME', l_format_time);
1555
1556
1557 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1558
1559
1560 hr_utility.set_location('Leaving:'||l_proc, 5);
1561
1562 exception
1563 when others then
1564
1565 raise;
1566
1567
1568 end init_assessment_wf;
1569
1570 -- ----------------------------------------------------------------------------
1571 -- |----------------------< initialize_cert_ntf_wf >-------------------------|
1572 -- ----------------------------------------------------------------------------
1573
1574 -- This wf would be used for OTA_CERTIFICATION_NTF_JSP_PRC
1575 -- Called from alert
1576
1577 Procedure initialize_cert_ntf_wf(p_item_type in wf_items.item_type%type,
1578 p_person_id in number default null,
1579 p_certification_id in ota_certifications_b.certification_id%type,
1580 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1581 p_cert_ntf_type in varchar2) is
1582
1583
1584 cursor get_certification_info is
1585 Select cet.name
1586 ,cet.end_date_comments
1587 ,ceb.end_date_active
1588 From ota_certifications_b ceb
1589 ,ota_certifications_tl cet
1590 Where
1591 ceb.certification_id = cet.certification_id
1592 and cet.language = userenv('LANG')
1593 and ceb.certification_id = p_certification_id;
1594
1595 l_certification_name ota_certifications_tl.name%type;
1596 l_end_date_comments ota_certifications_tl.end_date_comments%type;
1597 l_end_date_active ota_certifications_b.end_date_active%type;
1598
1599 l_process wf_activities.name%type := 'OTA_CERTIFICATION_NTF_JSP_PRC';
1600 l_item_key wf_items.item_key%type;
1601
1602 begin
1603
1604 open get_certification_info;
1605 fetch get_certification_info into l_certification_name, l_end_date_comments, l_end_date_active;
1606 close get_certification_info;
1607
1608 if ( l_end_date_comments is null ) then
1609 l_end_date_comments := '';
1610 end if;
1611
1612 -- Get the next item key from the sequence
1613 select hr_workflow_item_key_s.nextval
1614 into l_item_key
1615 from sys.dual;
1616
1617 -- get item key for the process
1618 WF_ENGINE.CREATEPROCESS(p_item_type, l_item_key, l_process);
1619
1620 -- set creator,current and supervisor name
1621 set_wf_item_attr(p_person_id => p_person_id,
1622 p_item_type => p_item_type,
1623 p_item_key => l_item_key);
1624
1625 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_EVENT_FIRED', p_cert_ntf_type);
1626 --Enh 5606090: Language support for Certification Notification.
1627 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_CERTIFICATION_NAME', p_certification_id);
1628 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_END_TIME', l_end_date_active);
1629 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_CERTIFICATION_ID', p_certification_id);
1630 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_CERT_PRD_ENROLLMENT_ID', p_cert_prd_enrollment_id);
1631 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'PERSON_ID', p_person_id);
1632 WF_ENGINE.setitemattrText(p_item_type, l_item_key, 'OTA_NTF_COMMENTS', l_end_date_comments);
1633
1634 WF_ENGINE.STARTPROCESS(p_item_type, l_item_key);
1635
1636 end initialize_cert_ntf_wf;
1637
1638 --
1639 Procedure process_cert_alert(
1640 ERRBUF OUT NOCOPY VARCHAR2,
1641 RETCODE OUT NOCOPY VARCHAR2,
1642 p_cert_ntf_type in varchar2) is
1643
1644 l_proc varchar2(72) := g_package || ' process_cert_alert';
1645
1646 l_certification_id ota_certifications_b.certification_id%type;
1647 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
1648 l_person_id ota_cert_enrollments.person_id%type;
1649
1650 l_item_type VARCHAR2(5) := 'OTWF';
1651
1652 --get all enrollment records in current period for reminder ntf
1653 cursor get_data_for_reminder_ntf is
1654 Select ceb.certification_id
1655 ,prd.cert_prd_enrollment_id
1656 ,enr.person_id
1657 From ota_certifications_b ceb
1658 ,ota_cert_enrollments enr
1659 ,ota_cert_prd_enrollments prd
1660 Where
1661 ceb.certification_id = enr.certification_id
1662 and enr.cert_enrollment_id = prd.cert_enrollment_id
1663 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1664 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1665 and trunc(sysdate) between nvl(trunc(prd.cert_period_start_date), trunc(sysdate)) and
1666 nvl(trunc(prd.cert_period_end_date), trunc(sysdate))
1667 and prd.period_status_code not in('COMPLETED', 'CANCELLED')
1668 and enr.certification_status_code not in ('CERTIFIED', 'CANCELLED')
1669 and enr.person_id is not null
1670 and ceb.notify_days_before_expire is not null
1671 and (trunc(sysdate) + ceb.notify_days_before_expire) = trunc(prd.cert_period_end_date);
1672
1673 --get all enrollment records in last period for expiration ntf
1674 cursor get_data_for_expiration_ntf is
1675 Select ceb.certification_id
1676 ,prd.cert_prd_enrollment_id
1677 ,enr.person_id
1678 From ota_certifications_b ceb
1679 ,ota_cert_enrollments enr
1680 ,ota_cert_prd_enrollments prd
1681 Where
1682 ceb.certification_id = enr.certification_id
1683 and enr.cert_enrollment_id = prd.cert_enrollment_id
1684 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1685 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1686 and prd.period_status_code not in('COMPLETED', 'CANCELLED')
1687 and enr.certification_status_code not in ('CERTIFIED', 'CANCELLED')
1688 and trunc(prd.cert_period_end_date) + 1 = trunc(sysdate)
1689 and enr.person_id is not null;
1690
1691 --get all enrollment records in current period for completion ntf
1692 cursor get_data_for_completion_ntf is
1693 Select ceb.certification_id
1694 ,prd.cert_prd_enrollment_id
1695 ,enr.person_id
1696 From ota_certifications_b ceb
1697 ,ota_cert_enrollments enr
1698 ,ota_cert_prd_enrollments prd
1699 Where
1700 ceb.certification_id = enr.certification_id
1701 and enr.cert_enrollment_id = prd.cert_enrollment_id
1702 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1703 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1704 and trunc(sysdate) between nvl(trunc(prd.cert_period_start_date), trunc(sysdate)) and
1705 nvl(trunc(prd.cert_period_end_date), trunc(sysdate))
1706 and prd.period_status_code = 'COMPLETED'
1707 and trunc(prd.completion_date) = trunc(sysdate)
1708 and enr.person_id is not null;
1709
1710 --get all enrollment records for renewal ntf
1711 cursor get_data_for_renewal_ntf is
1712 Select ceb.certification_id
1713 ,prd.cert_prd_enrollment_id
1714 ,enr.person_id
1715 From ota_certifications_b ceb
1716 ,ota_cert_enrollments enr
1717 ,ota_cert_prd_enrollments prd
1718 Where
1719 ceb.certification_id = enr.certification_id
1720 and enr.cert_enrollment_id = prd.cert_enrollment_id
1721 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1722 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1723 and trunc(enr.earliest_enroll_date) = trunc(sysdate)
1724 and enr.person_id is not null;
1725
1726 --get all enrollment records for cancellation ntf
1727 cursor get_data_for_cancellation_ntf is
1728 Select ceb.certification_id
1729 ,enr.person_id
1730 From ota_certifications_b ceb
1731 ,ota_cert_enrollments enr
1732 Where
1733 ceb.certification_id = enr.certification_id
1734 and enr.person_id is not null
1735 and (trunc(sysdate) + nvl(ceb.notify_days_before_expire, 0)) = trunc(ceb.end_date_active);
1736
1737 begin
1738 hr_utility.set_location('Entering:'||l_proc, 5);
1739
1740 if p_cert_ntf_type = 'Completion' OR p_cert_ntf_type = 'All' THEN
1741 for prd_enr_rem in get_data_for_reminder_ntf
1742 Loop
1743 initialize_cert_ntf_wf(p_item_type => l_item_type,
1744 p_person_id => prd_enr_rem.person_id,
1745 p_certification_id => prd_enr_rem.certification_id,
1746 p_cert_prd_enrollment_id => prd_enr_rem.cert_prd_enrollment_id,
1747 p_cert_ntf_type => 'CERT_REMINDER');
1748 End Loop;
1749 end if;
1750
1751 /* Might need to call API to set Certification Enrollment Status to EXPIRED before sending the notification */
1752 if p_cert_ntf_type ='Expiry' OR p_cert_ntf_type = 'All' THEN
1753 for prd_enr_exp in get_data_for_expiration_ntf
1754 Loop
1755 initialize_cert_ntf_wf(p_item_type => l_item_type,
1756 p_person_id => prd_enr_exp.person_id,
1757 p_certification_id => prd_enr_exp.certification_id,
1758 p_cert_prd_enrollment_id => prd_enr_exp.cert_prd_enrollment_id,
1759 p_cert_ntf_type => 'CERT_EXPIRATION');
1760 End Loop;
1761 end if;
1762
1763 /* Needs to be moved to API call
1764 if p_cert_ntf_type ='' OR p_cert_ntf_type = 'ALL' THEN
1765 for prd_enr_cmp in get_data_for_completion_ntf
1766 Loop
1767 initialize_cert_ntf_wf(p_item_type => l_item_type,
1768 p_person_id => prd_enr_cmp.person_id,
1769 p_certification_id => prd_enr_cmp.certification_id,
1770 p_cert_prd_enrollment_id => prd_enr_cmp.cert_prd_enrollment_id,
1771 p_cert_ntf_type => 'CERT_COMPLETION');
1772 End Loop;
1773 end if;
1774 */
1775
1776 if p_cert_ntf_type ='Renewal' OR p_cert_ntf_type = 'All' THEN
1777 for prd_enr_rnw in get_data_for_renewal_ntf
1778 Loop
1779 initialize_cert_ntf_wf(p_item_type => l_item_type,
1780 p_person_id => prd_enr_rnw.person_id,
1781 p_certification_id => prd_enr_rnw.certification_id,
1782 p_cert_prd_enrollment_id => prd_enr_rnw.cert_prd_enrollment_id,
1783 p_cert_ntf_type => 'CERT_RENEWAL');
1784 End Loop;
1785 end if;
1786
1787 if p_cert_ntf_type ='Cancellation' OR p_cert_ntf_type = 'All' THEN
1788 for prd_enr_can in get_data_for_cancellation_ntf
1789 Loop
1790 initialize_cert_ntf_wf(p_item_type => l_item_type,
1791 p_person_id => prd_enr_can.person_id,
1792 p_certification_id => prd_enr_can.certification_id,
1793 p_cert_prd_enrollment_id => null,
1794 p_cert_ntf_type => 'CERT_CANCELLATION' );
1795 End Loop;
1796 end if;
1797 hr_utility.set_location('Leaving:'||l_proc, 5);
1798 EXCEPTION
1799 when others then
1800 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1801 ||','||SUBSTR(SQLERRM, 1, 500));
1802 end process_cert_alert;
1803 --
1804 Procedure init_forum_notif(p_Forum_id in ota_forum_messages.forum_id%type,
1805 p_Forum_message_id in ota_forum_messages.forum_message_id%type)
1806 is
1807
1808 l_proc varchar2(72) := g_package||'init_forum_notif';
1809
1810 l_item_key wf_items.item_key%type;
1811 l_item_type wf_items.item_type%type := 'OTWF';
1812 l_process wf_activities.name%type := 'OTA_FORUM_NTF_JSP_PRC';
1813
1814 cursor get_message_details
1815 is
1816 select oft.name,fth.subject,ofm.person_id,ofm.contact_id,
1817 ofm.creation_date,ofm.message_body
1818 from ota_forum_messages ofm ,ota_forum_threads fth ,ota_forums_tl oft
1819 where oft.forum_id = ofm.forum_id
1820 and ofm.forum_thread_id = fth.forum_thread_id
1821 and ofm.forum_message_id = p_Forum_message_id
1822 and oft.language= USERENV('LANG');
1823
1824 cursor get_frm_subscriber
1825 is
1826 select person_id
1827 from ota_frm_notif_subscribers
1828 where forum_id = p_forum_id
1829 and person_id is not null;
1830
1831
1832 l_forum_name ota_forums_tl.name%type;
1833 l_subject ota_forum_threads.subject%type;
1834 l_person_id ota_forum_messages.person_id%type;
1835 l_contact_id ota_forum_messages.contact_id%type;
1836 l_creation_date varchar2(50);
1837 l_message_body ota_forum_messages.message_body%type;
1838 l_author_name varchar2(300);
1839
1840
1841
1842
1843 begin
1844 hr_utility.set_location('Entering:'||l_proc, 5);
1845
1846 open get_message_details;
1847 fetch get_message_details into l_forum_name,l_subject, l_person_id,l_contact_id,
1848 l_creation_date, l_message_body;
1849 close get_message_details;
1850
1851 -- get author name from person_id or contact_id
1852 l_author_name := ota_utility.get_learner_name(p_person_id => l_person_id,
1853 p_customer_id => null,
1854 p_contact_id => l_contact_id);
1855
1856 for rec in get_frm_subscriber
1857
1858 Loop
1859
1860 -- Get the next item key from the sequence
1861 select hr_workflow_item_key_s.nextval
1862 into l_item_key
1863 from sys.dual;
1864 -- get item key for the process
1865 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1866
1867 -- set creator,current and supervisor name
1868 set_wf_item_attr(p_person_id => rec.person_id,
1869 p_item_type => l_item_type,
1870 p_item_key => l_item_key);
1871 --Enh 5606090: Language support for Forum Notification.
1872 wf_engine.setitemattrtext(l_item_type,l_item_key, 'COURSE_NAME' ,p_Forum_message_id);
1873 wf_engine.setitemattrtext(l_item_type,l_item_key, 'EVENT_TITLE' ,l_subject);
1874 wf_engine.setitemattrtext(l_item_type,l_item_key, 'SECTION_NAME' ,l_message_body);
1875
1876 wf_engine.setitemattrtext(l_item_type,l_item_key, 'LP_CREATOR_NAME' ,l_author_name);
1877 wf_engine.setitemattrtext(l_item_type,l_item_key, 'SECTION_NAME' ,l_message_body);
1878
1879 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1880
1881 end loop;
1882
1883 hr_utility.set_location('Leaving:'||l_proc, 10);
1884
1885 end init_forum_notif;
1886
1887
1888
1889
1890 Procedure send_event_beginning_ntf(
1891 ERRBUF OUT NOCOPY VARCHAR2,
1892 RETCODE OUT NOCOPY VARCHAR2) IS
1893
1894 l_proc varchar2(72) := g_package||' send_event_beginning_ntf';
1895 BEGIN
1896 ota_initialization_wf.Initialize_auto_wf(
1897 p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC'
1898 ,p_item_type => 'OTWF'
1899 ,p_event_fired => 'CLASS_START');
1900 EXCEPTION
1901 when others then
1902 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1903 ||','||SUBSTR(SQLERRM, 1, 500));
1904 END send_event_beginning_ntf;
1905
1906
1907 Procedure send_instructor_reminder_ntf(
1908 ERRBUF OUT NOCOPY VARCHAR2,
1909 RETCODE OUT NOCOPY VARCHAR2) IS
1910
1911 l_proc varchar2(72) := g_package||' send_instructor_reminder_ntf';
1912 BEGIN
1913 ota_initialization_wf.Initialize_auto_wf(
1914 p_process => 'OTA_INSTRUCTOR_NTF_JSP_PRC'
1915 ,p_item_type => 'OTWF'
1916 ,p_event_fired => 'INSTRUCTOR_REMIND');
1917 EXCEPTION
1918 when others then
1919 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1920 ||','||SUBSTR(SQLERRM, 1, 500));
1921 END send_instructor_reminder_ntf;
1922
1923 procedure init_course_eval_notif(p_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type) is
1924
1925 l_proc varchar2(72) := g_package||'init_course_eval_notif';
1926
1927 l_item_key wf_items.item_key%type;
1928 l_item_type wf_items.item_type%type := 'OTWF';
1929 l_process wf_activities.name%type := 'OTA_COURSE_EVAL_PRC';
1930
1931 l_person_id ota_forum_messages.person_id%type;
1932 l_contact_id ota_forum_messages.contact_id%type;
1933 l_event_id ota_events.event_id%type;
1934 l_status_type ota_booking_status_types.type%type;
1935
1936 cursor csr_booking_status is
1937 SELECT bst.Type, tdb.delegate_person_id, tdb.contact_id, tdb.event_id
1938 FROM OTA_DELEGATE_BOOKINGS tdb,
1939 OTA_BOOKING_STATUS_TYPES bst
1940 WHERE tdb.booking_id = p_booking_id
1941 AND bst.booking_status_type_id = tdb.booking_status_type_id;
1942
1943 begin
1944 hr_utility.set_location('Entering:'||l_proc, 5);
1945
1946 open csr_booking_status;
1947 fetch csr_booking_status into l_status_type,l_person_id, l_contact_id,l_event_id;
1948 close csr_booking_status;
1949
1950 select hr_workflow_item_key_s.nextval into l_item_key from sys.dual;
1951
1952 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1953 set_wf_item_attr(p_person_id => l_person_id, p_item_type => l_item_type, p_item_key => l_item_key);
1954 wf_engine.setitemattrtext(l_item_type,l_item_key, 'EVENT_ID' ,l_event_id);
1955 wf_engine.setitemattrtext(l_item_type,l_item_key, 'STATUS' ,l_status_type);
1956 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1957
1958 hr_utility.set_location('Leaving:'||l_proc, 10);
1959
1960 end init_course_eval_notif;
1961
1962 procedure get_course_eval_status ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1963 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1964 actid IN NUMBER,
1965 funcmode IN VARCHAR2,
1966 resultout OUT nocopy VARCHAR2 ) is
1967
1968 l_status_type ota_booking_status_types.type%type;
1969 begin
1970 if(funcmode = 'RUN') then
1971 l_status_type := wf_engine.getItemAttrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'STATUS');
1972
1973 If (l_status_type is not null and upper(l_status_type) = 'E') then
1974 resultout := 'COMPLETE:PENDING_EVAL';
1975 else
1976 resultout := 'COMPLETE:ATTENDED_EVAL';
1977 end if;
1978 else
1979 if(funcmode='CANCEL') then
1980 resultout := 'COMPLETE';
1981 end if;
1982 end if;
1983
1984 end get_course_eval_status;
1985
1986 procedure get_course_eval_del_mode ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1987 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1988 actid IN NUMBER,
1989 funcmode IN VARCHAR2,
1990 resultout OUT nocopy VARCHAR2 ) is
1991
1992 l_event_id varchar2(80);
1993 l_synchronous_flag ota_category_usages.synchronous_flag%type;
1994 l_online_flag ota_category_usages.online_flag%type;
1995
1996 CURSOR delivery_mode(p_event_id varchar2) IS
1997 Select OCU.synchronous_flag, OCU.online_flag
1998 From ota_events OEV,
1999 ota_offerings OFR,
2000 ota_category_usages OCU
2001 Where OFR.offering_id = OEV.parent_offering_id
2002 And OCU.category_usage_id = OFR.delivery_mode_id
2003 And OEV.event_id = p_event_id;
2004
2005 begin
2006 if(funcmode = 'RUN') then
2007 l_event_id := wf_engine.getItemAttrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'EVENT_ID');
2008
2009 OPEN delivery_mode(l_event_id);
2010 FETCH delivery_mode INTO l_synchronous_flag, l_online_flag;
2011 CLOSE delivery_mode;
2012
2013 if upper(l_online_flag) = 'Y' then
2014 resultout := 'COMPLETE:ONLINE';
2015 else
2016 resultout := 'COMPLETE:OFFLINE';
2017 end if;
2018 else
2019 if(funcmode='CANCEL') then
2020 resultout := 'COMPLETE';
2021 end if;
2022 end if;
2023
2024 end get_course_eval_del_mode;
2025
2026 procedure get_class_name(document_id in varchar2,
2027 display_type in varchar2,
2028 document in out nocopy varchar2,
2029 document_type in out nocopy varchar2) is
2030 CURSOR csr_get_class_name IS
2031 SELECT title FROM ota_events_tl
2032 WHERE to_char(event_id) = document_id AND language = USERENV('LANG');
2033
2034 l_class_name varchar2(80);
2035 begin
2036 OPEN csr_get_class_name;
2037 FETCH csr_get_class_name INTO l_class_name;
2038 CLOSE csr_get_class_name;
2039
2040 document := l_class_name;
2041
2042 end get_class_name;
2043
2044 procedure RAISE_BUSINESS_EVENT(
2045 p_eventid in ota_Events.event_id%type,
2046 p_event_fired in varchar2,
2047 p_type in varchar2 default null
2048 )
2049 is
2050 l_proc varchar2(72) := g_package||'RAISE_BUSINESS_EVENT';
2051
2052 l_item_key wf_items.item_key%type;
2053
2054 l_title ota_events_tl.title%type;
2055 l_start_date varchar2(100);
2056 l_end_date varchar2(100);
2057 l_start_time ota_events.course_start_time%type;
2058 l_end_time ota_events.course_start_time%type;
2059 l_location_id ota_events.location_id %type;
2060 l_training_center_id ota_events.training_center_id %type;
2061 l_event_type ota_events.event_type%type;
2062
2063 l_location_name hr_locations_all_tl.location_code%type;
2064 l_training_center hr_all_organization_units.name%TYPE;
2065 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
2066
2067 l_booking_id ota_delegate_bookings.booking_id%type;
2068 l_person_id number(15);
2069
2070 l_event_data clob;
2071 l_text varchar2(2000);
2072
2073
2074 cursor get_event_info
2075 is
2076 select oev.title,oev.course_start_date,oev.course_end_date,
2077 oev.course_start_time, oev.course_end_time,
2078 oev.location_id,oev.training_center_id
2079 from ota_events_tl evt, ota_events oev
2080 where evt.event_id =oev.event_id
2081 and oev.event_id = p_eventid
2082 and evt.language=USERENV('LANG');
2083
2084 -- get all the person's enrolled into the event
2085 cursor get_booking_info
2086 is
2087 select odb.booking_id , odb.delegate_person_id, odb.delegate_contact_id
2088 from ota_delegate_bookings odb, ota_booking_status_types bst
2089 where odb.event_id =p_eventid
2090 and odb.booking_status_type_id = bst.booking_status_type_id
2091 and bst.type in ('P','W','R');
2092
2093
2094 begin
2095
2096 open get_event_info;
2097 fetch get_event_info into l_title,l_start_date,l_end_date,l_start_time,
2098 l_end_time,l_location_id,l_training_center_id;
2099 close get_event_info;
2100
2101 if (p_event_fired = 'oracle.apps.ota.api.event_api.update_location' or
2102 p_event_fired = 'oracle.apps.ota.api.event_api.update_training_center' or
2103 p_event_fired = 'oracle.apps.ota.api.event_api.update_trng_cntr_and_location')then
2104
2105
2106
2107 for rec in get_booking_info
2108 Loop
2109
2110 -- Get the next item key from the sequence
2111 select hr_workflow_item_key_s.nextval
2112 into l_item_key
2113 from sys.dual;
2114
2115 -- get location
2116 l_location_name := ota_general.get_Location_code(l_location_id);
2117
2118 -- get Traininig center
2119 l_training_center := ota_general.get_training_center(l_training_center_id);
2120
2121
2122 -- raise the event with the event data
2123 -- start BE
2124
2125 -- build the xml data for the event
2126 --
2127 dbms_lob.createTemporary(l_event_data,false,dbms_lob.call);
2128 l_text:='<?xml version =''1.0'' encoding =''ASCII''?>';
2129 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2130
2131 l_text:='<class_change>';
2132 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2133
2134 l_text:='<delegate_person_id>';
2135 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_person_id);
2136 l_text:=l_text||'</delegate_person_id>';
2137 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2138
2139 l_text:='<delegate_contact_id>';
2140 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_contact_id);
2141 l_text:=l_text||'</delegate_contact_id>';
2142 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2143
2144 l_text:='<booking_id>';
2145 l_text:=l_text||fnd_number.number_to_canonical(rec.booking_id);
2146 l_text:=l_text||'</booking_id>';
2147 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2148
2149 l_text:='<location_id>';
2150 l_text:=l_text||fnd_number.number_to_canonical(l_location_id);
2151 l_text:=l_text||'</location_id>';
2152 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2153
2154 l_text:='<location_name>';
2155 l_text:=l_text||irc_utilities_pkg.removeTags(l_location_name);
2156 l_text:=l_text||'</location_name>';
2157 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2158
2159 l_text:='<training_center_id>';
2160 l_text:=l_text||fnd_number.number_to_canonical(l_training_center_id);
2161 l_text:=l_text||'</training_center_id>';
2162 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2163
2164 l_text:='<training_center_name>';
2165 l_text:=l_text||irc_utilities_pkg.removeTags(l_training_center);
2166 l_text:=l_text||'</training_center_name>';
2167 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2168
2169 l_text:='<event_id>';
2170 l_text:=l_text||fnd_number.number_to_canonical(p_eventid);
2171 l_text:=l_text||'</event_id>';
2172 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2173
2174 l_text:='<ota_ntf_event_fired>';
2175 l_text:=l_text||irc_utilities_pkg.removeTags(p_event_fired);
2176 l_text:=l_text||'</ota_ntf_event_fired>';
2177 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2178
2179 l_text:='<target_date>';
2180 l_text:=l_text||fnd_date.date_to_canonical(l_start_date);
2181 l_text:=l_text||'</target_date>';
2182 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2183
2184 l_text:='<completion_date>';
2185 l_text:=l_text||fnd_date.date_to_canonical(l_end_date);
2186 l_text:=l_text||'</completion_date>';
2187 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2188
2189 l_text:='<ota_start_time>';
2190 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_start_time,'00:00'));
2191 l_text:=l_text||'</ota_start_time>';
2192 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2193
2194 l_text:='<ota_end_time>';
2195 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_end_time,'23:59'));
2196 l_text:=l_text||'</ota_end_time>';
2197 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2198
2199 l_text:='</class_change>';
2200 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2201
2202
2203 wf_event.raise(p_event_name=>p_event_fired
2204 ,p_event_key=>l_item_key
2205 ,p_event_data=>l_event_data);
2206
2207 -- end BE
2208
2209
2210 end loop;
2211
2212 elsif p_event_fired = 'oracle.apps.ota.api.event_api.update_class_schedule' then
2213
2214 for rec in get_booking_info
2215 Loop
2216
2217 -- Get the next item key from the sequence
2218 select hr_workflow_item_key_s.nextval
2219 into l_item_key
2220 from sys.dual;
2221
2222 -- get location
2223 l_location_name := ota_general.get_Location_code(l_location_id);
2224
2225 -- build the xml data for the event
2226 --
2227 dbms_lob.createTemporary(l_event_data,false,dbms_lob.call);
2228 l_text:='<?xml version =''1.0'' encoding =''ASCII''?>';
2229 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2230
2231 l_text:='<class_change>';
2232 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2233
2234 l_text:='<delegate_person_id>';
2235 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_person_id);
2236 l_text:=l_text||'</delegate_person_id>';
2237 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2238
2239 l_text:='<delegate_contact_id>';
2240 l_text:=l_text||fnd_number.number_to_canonical(rec.delegate_contact_id);
2241 l_text:=l_text||'</delegate_contact_id>';
2242 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2243
2244 l_text:='<booking_id>';
2245 l_text:=l_text||fnd_number.number_to_canonical(rec.booking_id);
2246 l_text:=l_text||'</booking_id>';
2247 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2248
2249 l_text:='<location_id>';
2250 l_text:=l_text||fnd_number.number_to_canonical(l_location_id);
2251 l_text:=l_text||'</location_id>';
2252 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2253
2254 l_text:='<location_name>';
2255 l_text:=l_text||irc_utilities_pkg.removeTags(l_location_name);
2256 l_text:=l_text||'</location_name>';
2257 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2258
2259 l_text:='<event_id>';
2260 l_text:=l_text||fnd_number.number_to_canonical(p_eventid);
2261 l_text:=l_text||'</event_id>';
2262 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2263
2264 l_text:='<title>';
2265 l_text:=l_text||irc_utilities_pkg.removeTags(l_title);
2266 l_text:=l_text||'</title>';
2267 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2268
2269 l_text:='<ota_ntf_event_fired>';
2270 l_text:=l_text||irc_utilities_pkg.removeTags(p_event_fired);
2271 l_text:=l_text||'</ota_ntf_event_fired>';
2272 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2273
2274 if p_type is not null then
2275 l_text:='<event_sub_type>';
2276 l_text:=l_text||irc_utilities_pkg.removeTags(p_type);
2277 l_text:=l_text||'</event_sub_type>';
2278 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2279 end if;
2280
2281 l_text:='<target_date>';
2282 l_text:=l_text||fnd_date.date_to_canonical(l_start_date);
2283 l_text:=l_text||'</target_date>';
2284 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2285
2286 l_text:='<completion_date>';
2287 l_text:=l_text||fnd_date.date_to_canonical(l_end_date);
2288 l_text:=l_text||'</completion_date>';
2289 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2290
2291 l_text:='<ota_start_time>';
2292 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_start_time,'00:00'));
2293 l_text:=l_text||'</ota_start_time>';
2294 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2295
2296 l_text:='<ota_end_time>';
2297 l_text:=l_text||irc_utilities_pkg.removeTags(nvl(l_end_time,'23:59'));
2298 l_text:=l_text||'</ota_end_time>';
2299 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2300
2301 l_text:='</class_change>';
2302 dbms_lob.writeAppend(l_event_data,length(l_text),l_text);
2303
2304
2305 wf_event.raise(p_event_name=>p_event_fired
2306 ,p_event_key=>l_item_key
2307 ,p_event_data=>l_event_data);
2308
2309 end loop;
2310
2311 end if;
2312
2313 end RAISE_BUSINESS_EVENT;
2314
2315 end ota_initialization_wf;