DBA Data[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;