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