[Home] [Help]
PACKAGE BODY: APPS.OTA_LP_NOTIFY_SS
Source
1 PACKAGE BODY ota_lp_notify_ss as
2 /* $Header: otlpsnot.pkb 120.10.12020000.3 2012/06/29 11:14:32 rpahune ship $ */
3
4 g_package varchar2(33) := ' ota_lp_notify_ss.'; -- Global package name
5
6
7 -- --------------------------------------------------------------------
8 -- |--------------------< create_item_attrib_if_notexist >---------|
9 -- --------------------------------------------------------------------
10 --
11 -- Description
12 --
13 -- This procedure checks to see if an item attribute exists. If it does
14 -- not the one is created
15 --
16 procedure create_item_attrib_if_notexist
17 (p_item_type in varchar2
18 ,p_item_key in varchar2
19 ,p_name in varchar2) is
20 --
21 l_dummy number(1);
22 -- cursor determines if an attribute exists
23 cursor csr_wiav is
24 select 1
25 from wf_item_attribute_values wiav
26 where wiav.item_type = p_item_type
27 and wiav.item_key = p_item_key
28 and wiav.name = p_name;
29 --
30 begin
31 -- open the cursor to determine if the a
32 open csr_wiav;
33 fetch csr_wiav into l_dummy;
34 if csr_wiav%notfound then
35 --
36 -- item attribute does not exist so create it
37 wf_engine.additemattr
38 (itemtype => p_item_type
39 ,itemkey => p_item_key
40 ,aname => p_name);
41 end if;
42 close csr_wiav;
43 --
44 end create_item_attrib_if_notexist;
45 --
46
47 PROCEDURE create_wf_process( p_lp_notification_type in varchar2,
48 p_lp_enrollment_id in number default null,
49 p_lp_member_enrollment_id in number default null
50 )
51 IS
52 l_proc varchar2(72) := g_package||'create_wf_process';
53 l_process wf_activities.name%type :='OTA_LRNG_PATH_NOTIFY_PRC';
54 l_item_type wf_items.item_type%type := 'OTWF';
55 l_item_key wf_items.item_key%type;
56
57
58 l_user_name varchar2(80);
59 l_learner_full_name varchar2(240);
60 l_creator_person_name PER_ALL_PEOPLE_F.full_name%TYPE;
61
62 l_creator_person_id per_all_people_f.person_id%type;
63 l_person_id per_all_people_f.person_id%type;
64 l_contact_id number;
65 l_contact_name HZ_PARTIES.party_name%TYPE;
66
67 l_person_details OTA_LEARNER_ENROLL_SS.csr_person_to_enroll_details%ROWTYPE;
68 l_role_name wf_roles.name%type;
69 l_role_display_name wf_roles.display_name%type;
70
71 l_process_display_name varchar2(240);
72
73
74 Cursor get_display_name is
75 SELECT wrpv.display_name displayName
76 FROM wf_runnable_processes_v wrpv
77 WHERE wrpv.item_type = l_item_type
78 AND wrpv.process_name = l_process;
79
80
81 CURSOR csr_get_user_name(p_person_id IN VARCHAR2) IS
82 SELECT user_name
83 FROM fnd_user
84 WHERE employee_id=p_person_id;
85
86 CURSOR csr_get_lp_details is
87 SELECT lpt.name path_name,
88 lpe.person_id person_id,
89 lpe.contact_id contact_id,
90 lpe.completion_target_date completion_target,
91 lpe.completion_date,
92 lps.notify_days_before_target,
93 lpe.creator_person_id
94 FROM ota_learning_paths lps,
95 ota_learning_paths_tl lpt,
96 ota_lp_enrollments lpe
97 WHERE lpt.language = USERENV('LANG')
98 AND lps.learning_path_id = lpt.learning_path_id
99 AND lpe.lp_enrollment_id = p_lp_enrollment_id
100 AND lpt.learning_path_id = lpe.learning_path_id;
101
102
103 CURSOR csr_get_lpm_details IS
104 SELECT avt.version_name course_name,
105 lpe.person_id person_id,
106 lpe.contact_id contact_id,
107 lpme.completion_target_date completion_target,
108 lpt.name path_name,
109 lpe.creator_person_id,
110 lpm.notify_days_before_target
111 FROM ota_lp_member_enrollments lpme,
112 ota_learning_path_members lpm,
113 ota_activity_versions_tl avt,
114 ota_lp_enrollments lpe,
115 ota_learning_paths lps,
116 ota_learning_paths_tl lpt
117 WHERE lpme.lp_member_enrollment_id = p_lp_member_enrollment_id
118 AND lps.learning_path_id = lpt.learning_path_id
119 AND avt.language = USERENV('LANG')
120 AND avt.activity_version_id = lpm.activity_version_id
121 AND lpm.learning_path_member_id = lpme.learning_path_member_id
122 AND lpe.lp_enrollment_id = lpme.lp_enrollment_id
123 AND lpt.learning_path_id = lpm.learning_path_id
124 AND lpt.language = USERENV('LANG');
125
126 CURSOR csr_get_contact_name IS
127 SELECT PARTY.party_name contact_name
128 FROM HZ_CUST_ACCOUNT_ROLES acct_role,
129 HZ_PARTIES party,
130 HZ_RELATIONSHIPS rel,
131 HZ_ORG_CONTACTS org_cont,
132 HZ_CUST_ACCOUNTS role_acct
133 WHERE acct_role.party_id = rel.party_id
134 and acct_role.role_type = 'CONTACT'
135 and org_cont.party_relationship_id = rel.relationship_id
136 and rel.subject_id = party.party_id
137 and rel.subject_table_name = 'HZ_PARTIES'
138 and rel.object_table_name = 'HZ_PARTIES'
139 and acct_role.cust_account_id = role_acct.cust_account_id
140 and role_acct.party_id = rel.object_id
141 AND acct_role.cust_account_role_id = l_contact_id;
142
143
144 CURSOR csr_get_person_name(p_person_id IN number) IS
145 SELECT ppf.full_name
146 FROM per_all_people_f ppf
147 WHERE person_id = p_person_id
148 AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;--Bug 13826398
149
150 l_lp_details csr_get_lp_details%ROWTYPE;
151 l_lpm_details csr_get_lpm_details%ROWTYPE;
152 l_section_name OTA_LP_SECTIONS_TL.NAME%TYPE;
153
154 CURSOR csr_get_section_name IS
155 SELECT lpst.name
156 FROM ota_lp_sections_tl lpst,
157 ota_lp_member_enrollments lpme,
158 ota_learning_path_members lpm
159 WHERE lpme.learning_path_member_id = lpm.learning_path_member_id
160 AND lpst.learning_path_section_id = lpm.learning_path_section_id
161 AND lpst.language = USERENV('LANG')
162 AND lpme.lp_member_enrollment_id = p_lp_member_enrollment_id;
163
164 CURSOR csr_get_person_user_name(p_person_id IN NUMBER) IS
165 SELECT user_name
166 FROM fnd_user
167 WHERE employee_id=p_person_id;
168
169 CURSOR csr_get_contact_user_name(p_contact_id IN NUMBER) IS
170 SELECT usr.user_name
171 FROM
172 hz_parties party,
173 fnd_user usr,
174 hz_cust_account_roles rol
175 WHERE
176 rol.party_id = party.party_id
177 AND rol.party_id = usr.customer_id
178 AND rol.cust_account_role_id = p_contact_id;
179
180
181 BEGIN
182 hr_utility.set_location('Entering:'||l_proc, 5);
183
184
185 OPEN get_display_name;
186 FETCH get_display_name INTO l_process_display_name;
187 CLOSE get_display_name;
188
189
190
191 IF p_lp_notification_type ='LP_COMPLETE'
192 OR p_lp_notification_type = 'LRN_LP_REMINDER'
193 OR p_lp_notification_type = 'MGR_LP_REMINDER' THEN
194
195 OPEN csr_get_lp_details;
196 FETCH csr_get_lp_details INTO l_lp_details;
197 CLOSE csr_get_lp_details;
198
199 IF p_lp_notification_type ='LP_COMPLETE' THEN
200 -- Get the next item key from the sequence
201 select hr_workflow_item_key_s.nextval
202 into l_item_key
203 from sys.dual;
204 ELSE
205 l_item_key := 'LP^' ||p_lp_enrollment_id || '^' || l_lp_details.notify_days_before_target||'^' ||to_char(sysdate,'DDMMRRRR');
206 END IF;
207
208 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
209 --Enh 5606090: Language support for LrngPath.
210 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'LP_NAME', p_lp_enrollment_id);
211 WF_ENGINE.setitemattrdate(l_item_type, l_item_key, 'TARGET_DATE', l_lp_details.completion_target);
212 WF_ENGINE.setitemattrdate(l_item_type, l_item_key, 'COMPLETION_DATE', l_lp_details.completion_date);
213 WF_ENGINE.setitemattrnumber(l_item_type, l_item_key, 'NOTIFY_DAYS_BEFORE_TARGET', l_lp_details.notify_days_before_target);
214
215 create_item_attrib_if_notexist(p_item_type => l_item_type
216 ,p_item_key => l_item_key
217 ,p_name => 'LP_ENROLLMENT_ID');
218 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'LP_ENROLLMENT_ID',p_lp_enrollment_id);
219 l_person_id := l_lp_details.person_id;
220 l_contact_id := l_lp_details.contact_id;
221
222 IF l_lp_details.creator_person_id <> l_person_id THEN
223 OPEN csr_get_person_name(l_lp_details.creator_person_id);
224 FETCH csr_get_person_name INTO l_creator_person_name;
225 CLOSE csr_get_person_name;
226 fnd_file.put_line(FND_FILE.LOG,'creator_person_id ' || l_creator_person_name);
227 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'LP_CREATOR_NAME',l_creator_person_name);
228 END IF;
229
230 ELSIF p_lp_notification_type IS NOT NULL THEN
231 OPEN csr_get_lpm_details;
232 FETCH csr_get_lpm_details INTO l_lpm_details;
233 CLOSE csr_get_lpm_details;
234
235 l_item_key := 'LPM^' ||p_lp_member_enrollment_id || '^' || l_lpm_details.notify_days_before_target||'^' ||to_char(sysdate,'DDMMRRRR');
236
237 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
238
239
240 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'COURSE_NAME', l_lpm_details.course_name);
241 --Enh 5606090: Language support for LrngPath.
242 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'LP_NAME', p_lp_member_enrollment_id);
243 WF_ENGINE.setitemattrdate(l_item_type, l_item_key, 'TARGET_DATE', l_lpm_details.completion_target);
244 create_item_attrib_if_notexist(p_item_type => l_item_type
245 ,p_item_key => l_item_key
246 ,p_name => 'LPM_ENROLLMENT_ID');
247 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'LPM_ENROLLMENT_ID',p_lp_member_enrollment_id);
248 WF_ENGINE.setitemattrnumber(l_item_type, l_item_key, 'NOTIFY_DAYS_BEFORE_TARGET', l_lpm_details.notify_days_before_target);
249 l_person_id := l_lpm_details.person_id;
250 l_contact_id := l_lpm_details.contact_id;
251
252
253 IF l_lpm_details.creator_person_id <> l_person_id THEN
254 OPEN csr_get_person_name(l_lpm_details.creator_person_id);
255 FETCH csr_get_person_name INTO l_creator_person_name;
256 CLOSE csr_get_person_name;
257 fnd_file.put_line(FND_FILE.LOG,'creator_person_id ' || l_creator_person_name);
258 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'LP_CREATOR_NAME',l_creator_person_name);
259 END IF;
260
261 IF p_lp_notification_type = 'LNR_CTG_LPM_REMINDER' OR p_lp_notification_type = 'MGR_CTG_LPM_REMINDER' THEN
262 OPEN csr_get_section_name;
263 FETCH csr_get_section_name INTO l_section_name;
264 CLOSE csr_get_section_name;
265 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'SECTION_NAME',l_section_name);
266 END IF;
267
268 END IF;
269
270 create_item_attrib_if_notexist(p_item_type => l_item_type
271 ,p_item_key => l_item_key
272 ,p_name => 'LP_NOTIFICATION_TYPE');
273 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'LP_NOTIFICATION_TYPE', p_lp_notification_type);
274
275
276 IF l_person_id IS NOT NULL THEN
277 --Commented for Bug 9976677,as it doesn't retrieve rows for terminated learners.Since the use of below call is only to
278 --retrieve person full name,using csr_get_person_name
279 --l_person_details := ota_learner_enroll_ss.Get_Person_To_Enroll_Details(p_person_id => l_person_id);
280
281 --Modified for bug#4644019
282 /*SELECT user_name INTO l_user_name
283 FROM fnd_user
284 WHERE employee_id=l_person_id
285 AND ROWNUM =1 ; */
286
287 OPEN csr_get_person_user_name(l_person_id);
288 FETCH csr_get_person_user_name INTO l_user_name;
289 IF csr_get_person_user_name%NOTFOUND THEN
290 CLOSE csr_get_person_user_name;
291 RETURN;
292 ELSE
293 CLOSE csr_get_person_user_name;
294 END IF;
295
296 --Added for Bug 9976677
297 OPEN csr_get_person_name(l_person_id);
298 FETCH csr_get_person_name INTO l_learner_full_name;
299 IF csr_get_person_name%NOTFOUND THEN
300 CLOSE csr_get_person_name;
301 RETURN;
302 ELSE
303 CLOSE csr_get_person_name;
304 END IF;
305
306 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'EVENT_OWNER',l_user_name);
307 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'LP_ENROLLEE',l_learner_full_name);
308
309
310
311
312 --Commented for Bug 9976677
313 /*IF l_person_details.full_name IS NOT NULL then
314 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'EVENT_OWNER',l_user_name);
315 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'LP_ENROLLEE',l_person_details.full_name);
316 END IF;*/
317
318 ELSIF l_contact_id IS NOT NULL THEN
319 OPEN csr_get_contact_name;
320 FETCH csr_get_contact_name INTO l_contact_name;
321 CLOSE csr_get_contact_name;
322
323 --Modified for bug#4644019
324 /*SELECT usr.user_name INTO l_user_name
325 FROM
326 hz_parties party,
327 fnd_user usr,
328 hz_cust_account_roles rol
329 WHERE
330 rol.party_id = party.party_id
331 AND rol.party_id = usr.customer_id
332 AND rol.cust_account_role_id = l_contact_id;*/
333
334 OPEN csr_get_contact_user_name(l_contact_id);
335 FETCH csr_get_contact_user_name INTO l_user_name;
336 IF csr_get_contact_user_name%NOTFOUND THEN
337 CLOSE csr_get_contact_user_name;
338 RETURN;
339 ELSE
340 CLOSE csr_get_contact_user_name;
341 END IF;
342
343 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'LP_ENROLLEE',l_contact_name);
344 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'EVENT_OWNER',l_user_name);
345 END IF;
346
347
348
349 -- Get and set owner role
350
351 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
352
353 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
354 p_orig_system_id => l_creator_person_id,
355 p_name =>l_role_name,
356 p_display_name =>l_role_display_name);
357
358
359 WF_ENGINE.SetItemOwner(itemtype => l_item_type,
360 itemkey =>l_item_key,
361 owner =>l_role_name);
362
363 hr_utility.set_location('After Setting Owner'||l_proc, 10);
364
365
366 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
367
368 hr_utility.set_location('leaving:'||l_proc, 20);
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373
374 end create_wf_process;
375
376 PROCEDURE get_notification_type
377 (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE
378 ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
379 ,actid IN NUMBER
380 ,funcmode IN VARCHAR2
381 ,resultout OUT nocopy VARCHAR2 ) AS
382
383 l_notification_type varchar2(30);
384 BEGIN
385 IF (funcmode='RUN') THEN
386 l_notification_type := WF_ENGINE.getitemattrtext(itemtype => itemtype,
387 itemkey => itemkey,
388 aname =>'LP_NOTIFICATION_TYPE',
389 ignore_notfound => true);
390 resultout := 'COMPLETE:'||l_notification_type;
391 ELSE IF (funcmode='CANCEL') THEN
392 resultout := 'COMPLETE';
393 END IF;
394 END IF;
395 END get_notification_type;
396
397 PROCEDURE is_Manager_enrolled_path
398 (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE
399 ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
400 ,actid IN NUMBER
401 ,funcmode IN VARCHAR2
402 ,resultout OUT nocopy VARCHAR2 ) AS
403
404 l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
405 l_manager_user_name fnd_user.user_name%TYPE;
406
407 CURSOR csr_get_manager_name IS
408 SELECT ppf.full_name, ppf.person_id
409 FROM ota_lp_enrollments lpe,
410 ota_learning_paths lps,
411 per_all_people_f ppf,
412 per_all_assignments_f paf
413 WHERE trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
414 AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
415 AND paf.person_id = lpe.person_id
416 AND paf.supervisor_id = ppf.person_id
417 AND paf.primary_flag = 'Y'
418 AND lps.learning_path_id = lpe.learning_path_id
419 AND lpe.person_id <> lpe.creator_person_id
420 AND lpe.lp_enrollment_id = l_lp_enrollment_id;
421
422 l_manager_details csr_get_manager_name%ROWTYPE;
423
424 CURSOR csr_get_user_name(l_person_id IN VARCHAR2) IS
425 SELECT user_name
426 FROM fnd_user
427 WHERE employee_id=l_person_id
428 and trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
429
430 BEGIN
431 --
432 IF (funcmode = 'RUN') THEN
433 l_lp_enrollment_id := wf_engine.getItemAttrNumber
434 (itemtype => itemtype
435 ,itemkey => itemkey
436 ,aname => 'LP_ENROLLMENT_ID');
437
438 OPEN csr_get_manager_name;
439 FETCH csr_get_manager_name INTO l_manager_details;
440 IF csr_get_manager_name%FOUND THEN
441 WF_ENGINE.setitemattrtext(itemtype,itemkey,'SUPERVISOR_USERNAME',l_manager_details.full_name);
442 create_item_attrib_if_notexist(p_item_type => itemtype
443 ,p_item_key => itemkey
444 ,p_name => 'MANAGER_ID');
445 WF_ENGINE.setitemattrNumber(itemtype,itemkey,'MANAGER_ID',l_manager_details.person_id);
446 CLOSE csr_get_manager_name;
447 OPEN csr_get_user_name(l_manager_details.person_id);
448 FETCH csr_get_user_name INTO l_manager_user_name;
449 IF csr_get_user_name%FOUND THEN
450 WF_ENGINE.setitemattrText(itemtype,itemkey,'EVENT_OWNER',l_manager_user_name);
451 END IF;
452 CLOSE csr_get_user_name;
453 resultout := 'COMPLETE:T';
454 ELSE
455 CLOSE csr_get_manager_name;
456 resultout := 'COMPLETE:F';
457 END IF;
458 ELSE IF (funcmode = 'CANCEL') THEN
459 resultout := 'COMPLETE';
460 END IF;
461 END IF;
462 --
463 END is_Manager_enrolled_path;
464
465 PROCEDURE is_Manager_same_as_creator
466 (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE
467 ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
468 ,actid IN NUMBER
469 ,funcmode IN VARCHAR2
470 ,resultout OUT nocopy VARCHAR2 ) AS
471 l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
472 l_manager_id OTA_LP_ENROLLMENTS.creator_person_id%TYPE;
473 l_manager_user_name fnd_user.user_name%TYPE;
474
475 CURSOR csr_get_creator_name IS
476 SELECT ppf.full_name, ppf.person_id
477 FROM ota_lp_enrollments lpe,
478 ota_learning_paths lps,
479 per_all_people_f ppf
480 WHERE trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
481 AND ppf.person_id = lpe.creator_person_id
482 AND lpe.lp_enrollment_id = l_lp_enrollment_id
483 AND lpe.learning_path_id = lps.learning_path_id;
484
485 l_creator_details csr_get_creator_name%ROWTYPE;
486
487 CURSOR csr_get_user_name(l_person_id IN VARCHAR2) IS
488 SELECT user_name
489 FROM fnd_user
490 WHERE employee_id=l_person_id
491 and trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
492
493 BEGIN
494 --
495 IF (funcmode = 'RUN') THEN
496 l_manager_id := wf_engine.getItemAttrNumber
497 (itemtype => itemtype
498 ,itemkey => itemkey
499 ,aname => 'MANAGER_ID');
500
501 l_lp_enrollment_id := wf_engine.getItemAttrNumber
502 (itemtype => itemtype
503 ,itemkey => itemkey
504 ,aname => 'LP_ENROLLMENT_ID');
505 OPEN csr_get_creator_name;
506 FETCH csr_get_creator_name INTO l_creator_details;
507 IF l_creator_details.person_id = l_manager_id THEN
508 CLOSE csr_get_creator_name;
509 resultout := 'COMPLETE:T';
510 ELSE
511 WF_ENGINE.setitemattrtext(itemtype,itemkey,'LP_CREATOR_NAME',l_creator_details.full_name);
512 create_item_attrib_if_notexist(p_item_type => itemtype
513 ,p_item_key => itemkey
514 ,p_name => 'CREATOR_ID');
515 WF_ENGINE.setitemattrNumber(itemtype,itemkey,'CREATOR_ID',l_creator_details.person_id);
516 CLOSE csr_get_creator_name;
517 OPEN csr_get_user_name(l_creator_details.person_id);
518 FETCH csr_get_user_name INTO l_manager_user_name;
519 IF csr_get_user_name%FOUND THEN
520 WF_ENGINE.setitemattrText(itemtype,itemkey,'EVENT_OWNER',l_manager_user_name);
521 END IF;
522 CLOSE csr_get_user_name;
523 resultout := 'COMPLETE:F';
524 END IF;
525 ELSE IF (funcmode = 'CANCEL') THEN
526 resultout := 'COMPLETE';
527 END IF;
528 END IF;
529 --
530
531 END is_Manager_same_as_creator;
532
533
534 FUNCTION is_notification_sent(p_itemkey in varchar2)
535 RETURN BOOLEAN
536 IS
537 CURSOR csr_notification_sent IS
538 SELECT 1
539 FROM wf_items
540 WHERE item_type = 'OTWF'
541 AND item_key = p_itemkey;
542
543 l_sent number;
544 BEGIN
545 OPEN csr_notification_sent;
546 FETCH csr_notification_sent INTO l_sent;
547 IF csr_notification_sent%NOTFOUND THEN
548 RETURN false;
549 ELSE
550 RETURN true;
551 END IF;
552 END is_notification_sent;
553 PROCEDURE send_lp_ct_notifications
554 (ERRBUF OUT NOCOPY VARCHAR2,
555 RETCODE OUT NOCOPY VARCHAR2) as
556
557 CURSOR csr_get_learning_paths IS
558 SELECT lpe.lp_enrollment_id
559 ,lpe.person_id
560 ,lpe.contact_id
561 ,lpe.creator_person_id
562 ,lps.notify_days_before_target
563 FROM ota_learning_paths lps,
564 ota_lp_enrollments lpe
565 WHERE lps.learning_path_id = lpe.learning_path_id
566 AND lpe.path_status_code = 'ACTIVE'
567 AND trunc(sysdate) + lps.notify_days_before_target = trunc(lpe.completion_target_date)
568 AND lps.business_group_id = ota_general.get_business_group_id;
569
570 l_lp_enrollment_id number;
571 failure exception;
572 l_proc varchar2(72) := g_package||' send_lp_ct_notifications';
573 l_lp_notification_type VARCHAR2(30);
574 l_key VARCHAR2(50);
575 BEGIN
576
577 FOR lp_rec IN csr_get_learning_paths
578 LOOP
579 l_lp_enrollment_id := lp_rec.lp_enrollment_id;
580 l_key := 'LP^' ||l_lp_enrollment_id || '^' || lp_rec.notify_days_before_target||'^' ||to_char(sysdate,'DDMMRRRR');
581 IF NOT is_notification_sent( p_itemkey => l_key) THEN
582 IF lp_rec.person_id <> lp_rec.creator_person_id THEN
583 l_lp_notification_type := 'MGR_LP_REMINDER';
584 ELSE
585 l_lp_notification_type := 'LRN_LP_REMINDER';
586 END IF;
587
588 create_wf_process(
589 p_lp_notification_type => l_lp_notification_type
590 ,p_lp_enrollment_id => l_lp_enrollment_id);
591 END IF;
592 END LOOP;
593
594 EXCEPTION
595 when others then
596 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
597 ||','||SUBSTR(SQLERRM, 1, 500));
598 END send_lp_ct_notifications;
599
600
601 PROCEDURE send_lpm_ct_notifications
602 (ERRBUF OUT NOCOPY VARCHAR2,
603 RETCODE OUT NOCOPY VARCHAR2) as
604 CURSOR csr_get_lp_comps IS
605
606 SELECT lpme.lp_member_enrollment_id
607 ,lpe.person_id
608 ,lpe.creator_person_id
609 ,lps.path_source_code
610 ,lpm.notify_days_before_target
611 FROM ota_learning_path_members lpm,
612 ota_lp_member_enrollments lpme,
613 ota_learning_paths lps,
614 ota_lp_enrollments lpe,
615 ota_lp_sections lpc
616 WHERE lpe.learning_path_id = lps.learning_path_id
617 AND lpc.learning_path_id = lps.learning_path_id
618 AND lpc.learning_path_section_id = lpm.learning_path_section_id
619 AND lpme.learning_path_member_id = lpm.learning_path_member_id
620 AND lpme.lp_enrollment_id = lpe.lp_enrollment_id
621 AND lpe.path_status_code NOT IN ('CANCELLED', 'COMPLETED')
622 AND lpme.member_status_code NOT IN ('CANCELLED','COMPLETED')
623 AND trunc(sysdate) + lpm.notify_days_before_target = trunc(lpme.completion_target_date)
624 AND (lpc.completion_type_code = 'M'
625 OR (lpc.completion_type_code = 'S'
626 AND lpc.no_of_mandatory_courses < (SELECT count(lp_member_enrollment_id)
627 FROM ota_lp_member_enrollments
628 WHERE learning_path_section_id = lpc.learning_path_section_id
629 AND lp_enrollment_id = lpe.lp_enrollment_id
630 AND member_status_code = 'COMPLETED')))
631
632 AND lps.business_group_id = ota_general.get_business_group_id;
633
634 l_lpm_enrollment_id number;
635 failure exception;
636 l_proc varchar2(72) := g_package|| ' send_lpm_ct_notifications';
637 l_lpm_notification_type VARCHAR2(30);
638 l_person_id OTA_LEARNING_PATHS.PERSON_ID%TYPE;
639 l_contact_id OTA_LEARNING_PATHS.CONTACT_ID%TYPE;
640 l_creator_person_id OTA_LP_ENROLLMENTS.CREATOR_PERSON_ID%TYPE;
641 l_path_source_code OTA_LEARNING_PATHS.PATH_SOURCE_CODE%TYPE;
642 l_key varchar2(80);
643 BEGIN
644
645 FOR lpm_rec IN csr_get_lp_comps
646 LOOP
647 l_lpm_enrollment_id := lpm_rec.lp_member_enrollment_id;
648 l_key := 'LPM^' ||l_lpm_enrollment_id || '^' ||lpm_rec.notify_days_before_target||'^' || to_char(sysdate,'DDMMRRRR');
649 IF NOT is_notification_sent( p_itemkey => l_key) THEN
650 l_person_id := lpm_rec.person_id;
651 l_path_source_code := lpm_rec.path_source_code;
652 l_creator_person_id := lpm_rec.creator_person_id;
653
654 IF l_path_source_code = 'CATALOG' THEN
655 IF l_person_id <> l_creator_person_id THEN
656 l_lpm_notification_type := 'MGR_CTG_LPM_REMINDER';
657 ELSE
658 l_lpm_notification_type := 'LRN_CTG_LPM_REMINDER';
659 END IF;
660 ELSIF l_path_source_code = 'EMPLOYEE' THEN
661 l_lpm_notification_type := 'LRN_LPM_REMINDER';
662 ELSIF l_path_source_code = 'MANAGER' OR l_path_source_code = 'TALENT_MGMT' THEN
663 l_lpm_notification_type := 'MGR_LPM_REMINDER';
664 END IF;
665 create_wf_process(
666 p_lp_notification_type => l_lpm_notification_type
667 ,p_lp_member_enrollment_id => l_lpm_enrollment_id);
668 END IF;
669 END LOOP;
670
671 EXCEPTION
672 when others then
673 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
674 ||','||SUBSTR(SQLERRM, 1, 500));
675 END send_lpm_ct_notifications;
676
677
678
679 end ota_lp_notify_ss;