[Home] [Help]
PACKAGE BODY: APPS.OTA_LRNG_PATH_MEMBER_UTIL
Source
1 PACKAGE BODY OTA_LRNG_PATH_MEMBER_UTIL as
2 /* $Header: otlpmwrs.pkb 120.5.12020000.4 2013/03/21 07:47:25 atadepal ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' ota_lrng_path_member_util.'; -- Global package name
9 --
10
11 -- ---------------------------------------------------------------------------
12 -- |----------------------< get_enrollment_status >--------------------------|
13 -- ---------------------------------------------------------------------------
14 FUNCTION get_enrollment_status(p_person_id IN ota_learning_paths.person_id%TYPE,
15 p_contact_id IN ota_learning_paths.contact_id%TYPE,
16 p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE,
17 p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE DEFAULT NULL,
18 p_return_code IN VARCHAR2)
19 RETURN VARCHAR2 IS
20
21 CURSOR csr_lp_enr IS
22 SELECT DECODE(bst.type,'C','Z',bst.type) status,
23 bst.name
24 FROM ota_learning_path_members lpm,
25 ota_lp_member_enrollments lme,
26 ota_events evt,
27 ota_delegate_bookings tdb,
28 ota_booking_status_types_vl bst
29 WHERE lpm.activity_version_id = evt.activity_version_id
30 AND evt.event_id = tdb.event_id
31 AND bst.booking_status_type_id = tdb.booking_status_type_id
32 AND lme.learning_path_member_id = lpm.learning_path_member_id
33 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id
34 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
35 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
36 )
37 ORDER BY status, evt.course_start_date;
38
39
40 CURSOR csr_act_enr IS
41 SELECT DECODE(bst.type,'C','Z',bst.type) status,
42 bst.name
43 FROM ota_events evt,
44 ota_delegate_bookings tdb,
45 ota_booking_status_types_vl bst
46 WHERE evt.event_id = tdb.event_id
47 AND bst.booking_status_type_id = tdb.booking_status_type_id
48 AND evt.activity_version_id = p_activity_version_id
49 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
50 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
51 )
52 ORDER BY status, evt.course_start_date;
53
54 l_proc VARCHAR2(72) := g_package|| 'get_enrollment_status';
55
56 l_status_type ota_booking_status_types.type%TYPE;
57 l_status_name ota_booking_status_types_vl.name%TYPE := null;
58 l_return ota_booking_status_types_vl.name%TYPE;
59
60 BEGIN
61
62 hr_utility.set_location(' Step:'|| l_proc, 10);
63
64 IF p_lp_member_enrollment_id IS NOT NULL THEN
65 FOR rec_lp_enr IN csr_lp_enr
66 LOOP
67 l_status_type := rec_lp_enr.status ;
68 l_status_name := rec_lp_enr.name;
69 EXIT;
70 END LOOP;
71 ELSE
72 FOR rec_act_enr IN csr_act_enr
73 LOOP
74 l_status_type := rec_act_enr.status ;
75 l_status_name := rec_act_enr.name;
76 EXIT;
77 END LOOP;
78 END IF;
79
80 IF p_return_code = 'NAME' THEN
81 IF l_status_name IS NULL THEN
82 l_status_name := ota_utility.get_message('OTA','OTA_13080_NOT_ENROLLED');
83 END IF;
84 l_return := l_status_name;
85 ELSE l_return := l_status_type;
86 END IF;
87
88 hr_utility.set_location(' Step:'|| l_proc, 20);
89 RETURN l_return;
90
91 END get_enrollment_status;
92
93
94 -- ---------------------------------------------------------------------------
95 -- |----------------------< get_enrollment_status >--------------------------|
96 -- ---------------------------------------------------------------------------
97 --
98 PROCEDURE get_enrollment_status(p_person_id IN ota_learning_paths.person_id%TYPE,
99 p_contact_id IN ota_learning_paths.contact_id%TYPE,
100 p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE,
101 p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
102 p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
103 p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE,
104 p_successful_attendance_flag OUT NOCOPY ota_delegate_bookings.successful_attendance_flag%TYPE)
105 IS
106
107
108 CURSOR csr_lp_enr IS
109 SELECT DECODE(bst.type,'C','Z',bst.type) status,
110 tdb.date_status_changed,
111 tdb.successful_attendance_flag
112 FROM ota_learning_path_members lpm,
113 ota_lp_member_enrollments lme,
114 ota_events evt,
115 ota_delegate_bookings tdb,
116 ota_booking_status_types bst
117 WHERE lpm.activity_version_id = evt.activity_version_id
118 AND evt.event_id = tdb.event_id
119 AND bst.booking_status_type_id = tdb.booking_status_type_id
120 AND lme.learning_path_member_id = lpm.learning_path_member_id
121 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id
122 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
123 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
124 )
125 ORDER BY status, evt.course_start_date;
126
127 CURSOR csr_act_enr IS
128 SELECT DECODE(bst.type,'C','Z',bst.type) status,
129 tdb.date_status_changed,
130 tdb.successful_attendance_flag
131 FROM ota_events evt,
132 ota_delegate_bookings tdb,
133 ota_booking_status_types_vl bst
134 WHERE evt.event_id = tdb.event_id
135 AND bst.booking_status_type_id = tdb.booking_status_type_id
136 AND evt.activity_version_id = p_activity_version_id
137 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
138 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
139 )
140 ORDER BY status, evt.course_start_date;
141
142 l_proc VARCHAR2(72) := g_package|| 'get_enrollment_status';
143
144 v_enroll_status VARCHAR2(30);
145 v_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
146 v_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
147 BEGIN
148
149 hr_utility.set_location(' Step:'|| l_proc, 10);
150 IF p_lp_member_enrollment_id IS NOT NULL THEN
151 FOR rec_lp_enr IN csr_lp_enr
152 LOOP
153 v_enroll_status := rec_lp_enr.status ;
154 v_date_status_changed := rec_lp_enr.date_status_changed;
155 v_successful_attendance_flag := rec_lp_enr.successful_attendance_flag;
156 EXIT;
157 END LOOP;
158 ELSE
159 FOR rec_act_enr IN csr_act_enr
160 LOOP
161 v_enroll_status := rec_act_enr.status ;
162 v_date_status_changed := rec_act_enr.date_status_changed;
163 v_successful_attendance_flag := rec_act_enr.successful_attendance_flag;
164 EXIT;
165 END LOOP;
166 END IF;
167
168 p_booking_status_type := v_enroll_status;
169 p_date_status_changed := v_date_status_changed;
170 p_successful_attendance_flag := v_successful_attendance_flag;
171
172 hr_utility.set_location(' Step:'|| l_proc, 20);
173
174 END get_enrollment_status;
175 --
176 -- ---------------------------------------------------------------------------
177 -- |----------------------< chk_enrollment_exist >--------------------------|
178 -- ---------------------------------------------------------------------------
179 FUNCTION chk_enrollment_exist(p_person_id IN ota_learning_paths.person_id%TYPE,
180 p_contact_id IN ota_learning_paths.contact_id%TYPE,
181 p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE)
182 RETURN boolean
183 IS
184 CURSOR chk_enr IS
185 SELECT NULL
186 FROM ota_events e,
187 ota_activity_versions a,
188 ota_delegate_bookings b,
189 ota_booking_status_types s,
190 ota_learning_path_members lpm
191 WHERE e.event_id = b.event_id
192 AND lpm.activity_version_id = a.activity_version_id
193 AND e.activity_version_id = a.activity_version_id
194 AND b.booking_status_type_id = s.booking_status_type_id
195 AND ((p_person_id IS NOT NULL AND b.delegate_person_id = p_person_id)
196 OR (p_contact_id IS NOT NULL AND b.delegate_contact_id = p_contact_id)
197 )
198 AND lpm.learning_path_member_id = p_learning_path_member_id;
199
200 l_proc VARCHAR2(72) := g_package|| 'chk_enrollment_exist';
201 l_return_val VARCHAR2(1);
202 l_found BOOLEAN := FALSE;
203
204 BEGIN
205
206 hr_utility.set_location(' Step:'|| l_proc, 10);
207
208 OPEN chk_enr;
209 FETCH chk_enr INTO l_return_val;
210 IF chk_enr%FOUND THEN
211 --
212 l_found := TRUE;
213 --
214 END IF;
215 CLOSE chk_enr;
216 --
217 hr_utility.set_location('Leaving '||l_proc,10);
218
219 RETURN l_found;
220
221 END chk_enrollment_exist;
222
223 -- ---------------------------------------------------------------------------
224 -- |----------------------< calculate_lme_status >-----------------------------|
225 -- ---------------------------------------------------------------------------
226 PROCEDURE calculate_lme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
227 p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
228 p_member_status_code OUT nocopy VARCHAR2,
229 p_completion_date OUT nocopy DATE)
230 IS
231
232 l_proc VARCHAR2(72) := g_package|| 'calculate_lme_status';
233 l_enroll_status VARCHAR2(30);
234 l_date_status_changed DATE;
235 l_person_id ota_learning_paths.person_id%TYPE;
236 l_contact_id ota_learning_paths.contact_id%TYPE;
237 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
238 BEGIN
239
240 hr_utility.set_location('Entering:'|| l_proc, 10);
241
242 SELECT lpe.person_id, lpe.contact_id
243 INTO l_person_id , l_contact_id
244 FROM ota_lp_enrollments lpe
245 where lpe.lp_enrollment_id = p_lp_enrollment_id;
246
247 get_enrollment_status(p_person_id => l_person_id,
248 p_contact_id => l_contact_id,
249 p_activity_version_id => p_activity_version_id,
250 p_lp_member_enrollment_id => null,
251 p_booking_status_type => l_enroll_status,
252 p_date_status_changed => l_date_status_changed,
253 p_successful_attendance_flag =>l_successful_attendance_flag);
254
255 IF ( l_enroll_status='A' AND l_successful_attendance_flag = 'Y' ) THEN
256
257 p_member_status_code := 'COMPLETED';
258 --p_completion_date := l_date_status_changed;
259 p_completion_date := get_lpm_completion_date(null,p_activity_version_id,l_person_id,l_contact_id);
260
261 ELSIF ( l_enroll_status='P'
262 OR l_enroll_status='W'
263 OR l_enroll_status ='R'
264 OR (l_enroll_status = 'A' AND l_successful_attendance_flag = 'N') ) THEN
265
266 p_member_status_code := 'ACTIVE';
267 p_completion_date := null;
268 ELSE
269 p_member_status_code := 'PLANNED';
270 p_completion_date := null;
271 END IF;
272
273 hr_utility.set_location('LEAVING:'|| l_proc, 20);
274
275 EXCEPTION
276 WHEN others THEN
277 p_member_status_code := 'PLANNED';
278 RAISE;
279
280 END calculate_lme_status;
281
282 -- ---------------------------------------------------------------------------
283 -- |----------------------< get_lme_status >-----------------------------|
284 -- ---------------------------------------------------------------------------
285 FUNCTION get_lme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
286 p_person_id IN ota_learning_paths.person_id%TYPE,
287 p_contact_id IN ota_learning_paths.contact_id%TYPE)
288 RETURN VARCHAR2
289 IS
290
291 l_proc VARCHAR2(72) := g_package|| 'get_lme_status';
292 l_enroll_status VARCHAR2(30);
293 l_member_status_code VARCHAR2(30);
294 l_date_status_changed DATE;
295 l_person_id ota_learning_paths.person_id%TYPE;
296 l_contact_id ota_learning_paths.contact_id%TYPE;
297 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
298 BEGIN
299
300 hr_utility.set_location('Entering:'|| l_proc, 10);
301
302 get_enrollment_status(p_person_id => p_person_id,
303 p_contact_id => p_contact_id,
304 p_activity_version_id => p_activity_version_id,
305 p_lp_member_enrollment_id => null,
306 p_booking_status_type => l_enroll_status,
307 p_date_status_changed => l_date_status_changed,
308 p_successful_attendance_flag => l_successful_attendance_flag);
309
310 IF ( l_enroll_status='A' AND l_successful_attendance_flag = 'Y' ) THEN
311
312 l_member_status_code := 'COMPLETED';
313
314
315 ELSIF ( l_enroll_status='P'
316 OR l_enroll_status='W'
317 OR l_enroll_status ='R'
318 OR (l_enroll_status = 'A' AND l_successful_attendance_flag = 'N')) THEN
319
320 l_member_status_code := 'ACTIVE';
321
322 ELSE
323 l_member_status_code := 'PLANNED';
324
325 END IF;
326
327 hr_utility.set_location('LEAVING:'|| l_proc, 20);
328
329 RETURN l_member_status_code;
330
331 END get_lme_status;
332
333
334 -- ----------------------------------------------------------------------------
335 -- |----------------------< get_lpc_completed_courses >---------------------|
336 -- ---------------------------------------------------------------------------
337 FUNCTION get_lpc_completed_courses(p_learning_path_section_id IN ota_lp_sections.learning_path_section_id%TYPE)
338 RETURN NUMBER IS
339
340 CURSOR csr_lpc_comp IS
341 SELECT count(lp_member_enrollment_id)
342 FROM ota_lp_member_enrollments
343 WHERE learning_path_section_id = p_learning_path_section_id
344 AND member_status_code = 'COMPLETED';
345
346 l_proc VARCHAR2(72) := g_package|| 'get_lpc_completed_courses';
347
348 l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
349
350 BEGIN
351
352 hr_utility.set_location(' Step:'|| l_proc, 10);
353 OPEN csr_lpc_comp;
354 FETCH csr_lpc_comp INTO l_completed_courses;
355 CLOSE csr_lpc_comp;
356
357 hr_utility.set_location(' Step:'|| l_proc, 20);
358 RETURN l_completed_courses;
359
360 END get_lpc_completed_courses;
361
362 -- ----------------------------------------------------------------------------
363 -- |----------------------< chk_section_completion_type >---------------------|
364 -- ---------------------------------------------------------------------------
365 FUNCTION chk_section_completion_type(p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE)
366 RETURN VARCHAR2 IS
367
368 CURSOR csr_lpc_dtl IS
369 SELECT completion_type_code
370 FROM ota_lp_sections lpc,
371 ota_learning_path_members lpm
372 WHERE lpc.learning_path_section_id = lpm.learning_path_section_id
373 AND lpm.learning_path_member_id = p_learning_path_member_id;
374
375 l_proc VARCHAR2(72) := g_package|| 'chk_section_completion_type';
376
377 l_completion_type ota_lp_sections.completion_type_code%TYPE;
378
379 BEGIN
380
381 hr_utility.set_location(' Step:'|| l_proc, 10);
382 OPEN csr_lpc_dtl;
383 FETCH csr_lpc_dtl INTO l_completion_type;
384 CLOSE csr_lpc_dtl;
385
386 hr_utility.set_location(' Step:'|| l_proc, 20);
387 RETURN l_completion_type;
388
389 END chk_section_completion_type;
390
391 -- ----------------------------------------------------------------------------
392 -- |---------------------------< get_valid_enroll >-------------------------|
393 -- ----------------------------------------------------------------------------
394 PROCEDURE get_valid_enroll (p_person_id IN ota_learning_paths.person_id%TYPE
395 ,p_contact_id IN ota_learning_paths.contact_id%TYPE
396 ,p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE
397 ,p_return_status OUT nocopy VARCHAR2)
398 IS
399 l_enr_type VARCHAR2(30);
400 l_proc VARCHAR2(72) := g_package|| 'get_valid_enroll';
401 BEGIN
402
403 l_enr_type:= get_enrollment_status(p_person_id => p_person_id,
404 p_contact_id => p_contact_id,
405 p_activity_version_id => null,
406 p_lp_member_enrollment_id => p_lp_member_enrollment_id,
407 p_return_code => 'TYPE');
408 p_return_status := 'S';
409
410 IF ( l_enr_type IS NOT NULL AND l_enr_type <> 'Z' ) THEN
411 p_return_status := 'E';
412 END IF;
413
414 END get_valid_enroll;
415
416
417
418 -- ---------------------------------------------------------------------------
419 -- |----------------------< update_lme_enroll_status_chg >--------------------------|
420 -- ---------------------------------------------------------------------------
421 PROCEDURE update_lme_enroll_status_chg (p_event_id IN ota_events.event_id%TYPE,
422 p_person_id IN ota_lp_enrollments.person_id%TYPE,
423 p_contact_id IN ota_lp_enrollments.contact_id%TYPE,
424 p_lp_enrollment_ids OUT NOCOPY varchar2)
425 IS
426
427 l_proc VARCHAR2(72) := g_package|| 'update_lme_enroll_status_chg';
428
429
430 CURSOR evt_det IS
431 SELECT activity_version_id
432 FROM ota_events
433 WHERE event_id = p_event_id;
434
435 --get all the lpms which have the passed event as a component
436 CURSOR csr_lpm_info(csr_activity_version_id number) IS
437 SELECT olme.lp_member_enrollment_id,
438 olpe.lp_enrollment_id,
439 olme.object_version_number,
440 olpm.learning_path_section_id,
441 olpm.learning_path_member_id,
442 olpe.no_of_completed_courses,
443 olpe.no_of_mandatory_courses,
444 olme.member_status_code,
445 olme.event_id
446 FROM ota_learning_path_members olpm,
447 ota_lp_member_enrollments olme,
448 ota_lp_enrollments olpe
449 WHERE olpe.learning_path_id = olpm.learning_path_id
450 AND olpm.learning_path_member_id = olme.learning_path_member_id
451 AND olpe.lp_enrollment_id = olme.lp_enrollment_id
452 AND (( p_person_id IS NOT NULL AND olpe.person_id = p_person_id)
453 OR (p_contact_id IS NOT NULL AND olpe.contact_id = p_contact_id))
454 AND olpm.activity_version_id = csr_activity_version_id
455 AND olme.member_status_code <> 'CANCELLED';
456
457 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
458 l_lp_section_id ota_lp_sections.learning_path_section_id%TYPE;
459 l_completion_type_code ota_lp_sections.completion_type_code%TYPE;
460 l_enroll_type ota_booking_status_types.type%TYPE;
461 l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
462 l_completion_date ota_lp_enrollments.completion_date%TYPE;
463 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
464 l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE := 0;
465 l_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
466 l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
467
468 --variables to store old values
469 l_old_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
470 l_old_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
471 l_old_member_status ota_lp_member_enrollments.member_status_code%TYPE;
472 l_event_id ota_events.event_id%TYPE;
473 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
474
475 BEGIN
476
477
478 OPEN evt_det;
479 FETCH evt_det
480 INTO l_activity_version_id;
481 CLOSE evt_det;
482
483 hr_utility.set_location(' Step:'|| l_proc, 20);
484
485 FOR rec IN csr_lpm_info(l_activity_version_id)
486
487 LOOP
488
489 get_enrollment_status(p_person_id => p_person_id,
490 p_contact_id => p_contact_id,
491 p_activity_version_id => l_activity_version_id,
492 p_lp_member_enrollment_id => rec.lp_member_enrollment_id,
493 p_booking_status_type => l_enroll_type,
494 p_date_status_changed => l_date_status_changed,
495 p_successful_attendance_flag =>l_successful_attendance_flag);
496 l_completion_date := null;
497 l_event_id := rec.event_id;
498
499 IF l_enroll_type = 'A' AND l_successful_attendance_flag = 'Y' THEN
500 l_member_status_code := 'COMPLETED';
501 --l_completion_date := l_date_status_changed;
502 l_completion_date := get_lpm_completion_date(rec.lp_member_enrollment_id,null, null,null);
503 ELSIF ( l_enroll_type = 'P'
504 OR l_enroll_type = 'W'
505 OR l_enroll_type = 'E' --added for Bug 10152493
506 OR l_enroll_type = 'R'
507 OR (l_enroll_type = 'A' AND l_successful_attendance_flag = 'N')) THEN
508 l_member_status_code := 'ACTIVE';
509 ELSE
510 l_member_status_code := 'PLANNED';
511 l_event_id := null;
512 END IF;
513 l_old_member_status := rec.member_status_code;
514
515 IF l_old_member_status <> l_member_status_code THEN
516 --call upd lme api after lck
517 ota_lp_member_enrollment_api.update_lp_member_enrollment
518 (p_effective_date => sysdate
519 ,p_object_version_number => rec.object_version_number
520 ,p_learning_path_member_id => rec.learning_path_member_id
521 ,p_lp_enrollment_id => rec.lp_enrollment_id
522 ,p_lp_member_enrollment_id => rec.lp_member_enrollment_id
523 ,p_member_status_code => l_member_status_code
524 ,p_completion_date => l_completion_date
525 ,p_event_id => l_event_id);
526
527
528 l_completion_type_code := chk_section_completion_type(rec.learning_path_member_id);
529 l_old_mandatory_courses := NVL(rec.no_of_mandatory_courses,0);
530 l_old_completed_courses := NVL(rec.no_of_completed_courses,0);
531 l_completed_courses := l_old_completed_courses;
532
533
534 IF l_old_member_status IN ('PLANNED', 'ACTIVE', 'AWAITING_APPROVAL') and l_member_status_code = 'COMPLETED' THEN
535
536 IF l_completion_type_code = 'M' THEN
537 l_completed_courses := l_old_completed_courses +1 ;
538 ELSIF l_completion_type_code = 'S' THEN
539 l_section_completed_courses := get_lpc_completed_courses(rec.learning_path_section_id);
540 IF l_old_completed_courses < l_old_mandatory_courses THEN
541 l_completed_courses := l_old_completed_courses +1 ;
542 END IF;
543 END IF;
544 END IF;
545 IF l_old_member_status = 'COMPLETED' and l_member_status_code <> 'COMPLETED' THEN
546 IF l_completion_type_code = 'M' THEN
547 l_completed_courses := l_old_completed_courses -1;
548 ELSIF l_completion_type_code = 'S' THEN
549 l_section_completed_courses := get_lpc_completed_courses(rec.learning_path_section_id);
550 IF l_old_completed_courses <= l_old_mandatory_courses AND
551 l_section_completed_courses < l_old_mandatory_courses THEN
552 l_completed_courses := l_old_completed_courses - 1 ;
553 END IF;
554 END IF;
555 END IF;
556
557
558 Update_lpe_lme_change(rec.lp_member_enrollment_id, l_completed_courses, p_lp_enrollment_ids);
559
560 END IF;
561 END LOOP;
562
563 hr_utility.set_location(' Step:'|| l_proc, 30);
564
565 --MULTI MESSAGE SUPPORT
566
567
568 END update_lme_enroll_status_chg;
569
570 -- ---------------------------------------------------------------------------
571 -- |----------------------< Update_lpe_lme_change >--------------------------|
572 -- ---------------------------------------------------------------------------
573 --
574 -- This procedure will get called only when a tpc is Cancelled
575 Procedure Update_lpe_lme_change( p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE)
576 is
577
578 CURSOR csr_lpe_with_lme
579 IS
580 SELECT lpe.lp_enrollment_id,
581 lpe.path_status_code
582 FROM ota_lp_enrollments lpe,
583 ota_lp_member_enrollments lme
584 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
585 AND lpe.path_status_code <> 'CANCELLED'
586 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
587
588 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
589 IS
590 SELECT lpe.object_version_number
591 FROM ota_lp_enrollments lpe
592 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
593
594
595 l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
596 l_object_version_number ota_lp_enrollments.object_version_number%type;
597 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
598 l_complete_ok varchar2(1);
599
600 BEGIN
601 FOR rec1 in csr_lpe_with_lme LOOP
602 l_path_status_code :=rec1.path_status_code;
603 l_complete_ok := ota_lrng_path_util.chk_complete_path_ok(rec1.lp_enrollment_id);
604 IF l_complete_ok = 'S'
605 AND rec1.path_status_code = 'ACTIVE'
606 THEN
607 -- The Plan can be completed
608 l_path_status_code := 'COMPLETED';
609 ELSIF l_complete_ok = 'F' AND rec1.path_status_code = 'COMPLETED' THEN
610 l_path_status_code := 'ACTIVE';
611 END IF;
612
613 IF l_path_status_code <> rec1.path_status_code THEN
614 OPEN csr_lpe_update(rec1.lp_enrollment_id);
615 FETCH csr_lpe_update into l_object_version_number;
616 IF csr_lpe_update%FOUND then
617 CLOSE csr_lpe_update;
618 ota_lp_enrollment_api.update_lp_enrollment
619 (p_effective_date => sysdate
620 ,p_lp_enrollment_id => rec1.lp_enrollment_id
621 ,p_object_version_number => l_object_version_number
622 ,p_path_status_code => l_path_status_code);
623
624 ELSE
625 CLOSE csr_lpe_update;
626 END IF;
627 END IF;
628 END LOOP;
629 END Update_lpe_lme_change;
630
631
632 Procedure Update_lpe_lme_change (p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
633 p_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE,
634 p_lp_enrollment_ids OUT NOCOPY VARCHAR2)
635 is
636
637 CURSOR csr_lpe_with_lme
638 IS
639 SELECT lpe.lp_enrollment_id,
640 lpe.path_status_code,
641 lpe.learning_path_id,
642 lpe.no_of_mandatory_courses
643 FROM ota_lp_enrollments lpe,
644 ota_lp_member_enrollments lme
645 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
646 AND lpe.path_status_code <> 'CANCELLED'
647 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
648
649
650 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
651 IS
652 SELECT lpe.object_version_number
653 FROM ota_lp_enrollments lpe
654 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
655
656 l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
657 l_object_version_number ota_lp_enrollments.object_version_number%type;
658 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
659 l_completion_date DATE;
660 l_complete_ok varchar2(1);
661 l_lp_enrollment_ids varchar2(4000) := '';
662
663 BEGIN
664 FOR rec1 in csr_lpe_with_lme LOOP
665 l_path_status_code :=rec1.path_status_code;
666 l_complete_ok := ota_lrng_path_util.chk_complete_path_ok(rec1.lp_enrollment_id); --Bug#7028384
667 -- IF p_no_of_completed_courses = rec1.no_of_mandatory_courses AND
668 IF rec1.path_status_code = 'ACTIVE' AND l_complete_ok = 'S'
669 THEN
670 -- The Plan can be completed
671 l_path_status_code := 'COMPLETED';
672
673 IF rec1.lp_enrollment_id IS NOT NULL THEN
674 if l_lp_enrollment_ids = '' or l_lp_enrollment_ids is null then
675 l_lp_enrollment_ids := rec1.lp_enrollment_id;
676 else
677 l_lp_enrollment_ids := l_lp_enrollment_ids || '^' || rec1.lp_enrollment_id;
678
679 end if;
680 END IF;
681 ELSIF p_no_of_completed_courses < rec1.no_of_mandatory_courses AND rec1.path_status_code = 'COMPLETED' THEN
682 l_path_status_code := 'ACTIVE';
683 END IF;
684
685 OPEN csr_lpe_update(rec1.lp_enrollment_id);
686 FETCH csr_lpe_update into l_object_version_number;
687 IF csr_lpe_update%FOUND then
688 CLOSE csr_lpe_update;
689 IF l_path_status_code = 'COMPLETED' THEN
690 --l_completion_date := sysdate;
691 l_completion_date := get_lp_completion_date(rec1.lp_enrollment_id);
692 ELSE
693 l_completion_date := null;
694 END IF;
695 ota_lp_enrollment_api.update_lp_enrollment
696 (p_effective_date => sysdate
697 ,p_lp_enrollment_id => rec1.lp_enrollment_id
698 ,p_object_version_number => l_object_version_number
699 ,p_path_status_code => l_path_status_code
700 ,p_no_of_completed_courses => p_no_of_completed_courses
701 ,p_completion_date => l_completion_date);
702
703 ELSE
704 CLOSE csr_lpe_update;
705 END IF;
706 END LOOP;
707 p_lp_enrollment_ids := l_lp_enrollment_ids;
708 END Update_lpe_lme_change;
709
710 -- ----------------------------------------------------------------------------
711 -- |----------------------<create_talent_mgmt_lpm>-----------------------------|
712 -- ----------------------------------------------------------------------------
713 PROCEDURE create_talent_mgmt_lpm
714 (p_validate IN NUMBER DEFAULT hr_api.g_false_num
715 ,p_effective_date IN DATE
716 ,p_business_group_id IN NUMBER
717 ,p_learning_path_id IN NUMBER DEFAULT NULL
718 ,p_lp_enrollment_id IN NUMBER DEFAULT NULL
719 ,p_learning_path_section_id IN NUMBER DEFAULT NULL
720 ,p_path_name IN VARCHAR2 DEFAULT NULL
721 ,p_path_purpose IN VARCHAR2 DEFAULT NULL
722 ,p_path_status_code IN VARCHAR2
723 ,p_path_start_date_active IN DATE DEFAULT NULL
724 ,p_path_end_date_active IN DATE DEFAULT NULL
725 ,p_source_function_code IN VARCHAR2
726 ,p_assignment_id IN NUMBER DEFAULT NULL
727 ,p_source_id IN NUMBER DEFAULT NULL
728 ,p_creator_person_id IN NUMBER
729 ,p_person_id IN NUMBER
730 ,p_display_to_learner_flag IN VARCHAR2
731 ,p_activity_version_id IN NUMBER
732 ,p_course_sequence IN NUMBER
733 ,p_member_status_code IN VARCHAR2 DEFAULT NULL
734 ,p_completion_target_date IN DATE
735 ,p_notify_days_before_target IN NUMBER
736 ,p_object_version_NUMBER OUT NOCOPY NUMBER
737 ,p_return_status OUT NOCOPY VARCHAR2
738 ) IS
739 --
740 -- Variables for API Boolean parameters
741 l_validate boolean;
742 --
743 -- Variables for IN/OUT parameters
744 --
745 -- Other variables
746 l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
747 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
748 l_learning_path_section_id ota_lp_sections.learning_path_section_id%TYPE;
749 l_learning_path_member_id ota_learning_path_members.learning_path_member_id%TYPE;
750 l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
751
752 l_lp_ovn number;
753 l_lpe_ovn number;
754 l_lpc_ovn number;
755 l_lpm_ovn number;
756 l_lme_ovn number;
757
758 l_lp_rtn_status varchar2(30);
759 l_lpe_rtn_status varchar2(30);
760 l_lpc_rtn_status varchar2(30);
761 l_lpm_rtn_status varchar2(30);
762 l_lme_rtn_status varchar2(30);
763 l_member_status_code varchar2(30) := p_member_status_code;
764 l_exists boolean;
765
766 l_proc varchar2(72) := g_package ||'create_talent_mgmt_lpm';
767 l_path_source_code ota_learning_paths.path_source_code%TYPE;
768 l_path_name ota_lp_sections_tl.name%TYPE := p_path_name;
769
770 CURSOR csr_get_lp IS
771 SELECT lps.learning_path_id,
772 lpe.lp_enrollment_id,
773 lpc.learning_path_section_id
774 FROM ota_learning_paths lps,
775 ota_lp_enrollments lpe,
776 ota_lp_sections lpc
777 WHERE lps.learning_path_id = lpe.learning_path_id
778 AND lpc.learning_path_id = lps.learning_path_id
779 AND lps.path_source_code = 'TALENT_MGMT'
780 AND lps.source_function_code = p_source_function_code
781 AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND lps.source_id = p_source_id))
782 AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND lps.assignment_id = p_assignment_id));
783
784 CURSOR csr_get_lpe IS
785 SELECT lp_enrollment_id
786 FROM ota_lp_enrollments
787 WHERE learning_path_id = l_learning_path_id
788 AND person_id = p_person_id;
789
790 CURSOR csr_get_lpc IS
791 SELECT learning_path_section_id
792 FROM ota_lp_sections
793 WHERE learning_path_id = l_learning_path_id
794 AND completion_type_code = 'M';
795
796 CURSOR csr_get_lpm IS
797 SELECT learning_path_member_id
798 FROM ota_learning_path_members
799 WHERE learning_path_section_id = l_learning_path_section_id
800 AND activity_version_id = p_activity_version_id;
801
802 BEGIN
803 hr_utility.set_location(' Entering:' || l_proc,10);
804 --
805 -- Issue a savepoint
806 --
807 SAVEPOINT create_talent_mgmt_lpm;
808
809 --
810 -- Check if the call is from SSHR - Appraisal / Suitability Matching / Succession Planning
811 --
812
813 -- SSHR call should have person Id. Mandatory check for personId.
814 hr_api.mandatory_arg_error
815 (p_api_name => l_proc
816 ,p_argument => 'p_person_id'
817 ,p_argument_value => p_person_id
818 );
819 IF p_learning_path_id IS NULL THEN
820 OPEN csr_get_lp;
821 FETCH csr_get_lp INTO l_learning_path_id,
822 l_lp_enrollment_id,
823 l_learning_path_section_id;
824 l_exists := csr_get_lp%FOUND;
825 CLOSE csr_get_lp;
826 ELSE
827 l_learning_path_id := p_learning_path_id;
828 l_lp_enrollment_id := p_lp_enrollment_id;
829 l_learning_path_section_id := p_learning_path_section_id;
830 END IF;
831
832 IF NOT l_exists THEN
833 ota_learning_path_swi.create_learning_path
834 (p_effective_date => p_effective_date
835 ,p_validate => p_validate
836 ,p_path_name => l_path_name
837 ,p_business_group_id => p_business_group_id
838 ,p_duration => null
839 ,p_duration_units => null
840 ,p_start_date_active => NVL(p_path_start_date_active, trunc(sysdate))
841 ,p_end_date_active => p_path_end_date_active
842 ,p_description => null
843 ,p_objectives => null
844 ,p_keywords => null
845 ,p_purpose => p_path_purpose
846 ,p_path_source_code => 'TALENT_MGMT'
847 ,p_source_function_code => p_source_function_code
848 ,p_assignment_id => p_assignment_id
849 ,p_source_id => p_source_id
850 ,p_notify_days_before_target => null
851 ,p_person_id => p_person_id
852 ,p_display_to_learner_flag => p_display_to_learner_flag
853 ,p_learning_path_id => l_learning_path_id
854 ,p_object_version_number => l_lp_ovn
855 ,p_return_status => l_lp_rtn_status
856 );
857
858 l_learning_path_id := ota_lrng_path_util.get_talent_mgmt_lp
859 (p_person_id => p_person_id
860 ,p_source_function_code => p_source_function_code
861 ,p_source_id => p_source_id
862 ,p_assignment_id => p_assignment_id
863 ,p_business_group_id => p_business_group_id);
864
865
866 -- If Learning Path is not created, rollback and return
867 if (l_lp_rtn_status = 'E') then
868 ROLLBACK TO create_talent_mgmt_lpm;
869 p_object_version_number := NULL;
870 p_return_status := hr_multi_message.get_return_status_disable;
871 return;
872 end if;
873
874 ota_lp_enrollment_swi.create_lp_enrollment
875 (p_effective_date => p_effective_date
876 ,p_validate => p_validate
877 ,p_learning_path_id => l_learning_path_id
878 ,p_person_id => p_person_id
879 ,p_path_status_code => p_path_status_code
880 ,p_enrollment_source_code => 'TALENT_MGMT'
881 ,p_completion_target_date => p_completion_target_date
882 ,p_creator_person_id => p_creator_person_id
883 ,p_business_group_id => p_business_group_id
884 ,p_lp_enrollment_id => l_lp_enrollment_id
885 ,p_object_version_number => l_lpe_ovn
886 ,p_return_status => l_lpe_rtn_status
887 );
888
889 OPEN csr_get_lpe;
890 FETCH csr_get_lpe INTO l_lp_enrollment_id;
891 CLOSE csr_get_lpe;
892
893
894 -- If Learning Path enrollment is not created, rollback and return
895 if (l_lpe_rtn_status = 'E') then
896 ROLLBACK TO create_talent_mgmt_lpm;
897 p_object_version_number := NULL;
898 p_return_status := hr_multi_message.get_return_status_disable;
899 return;
900 end if;
901
902 ota_lp_section_swi.create_lp_section
903 (p_validate => p_validate
904 ,p_effective_date => p_effective_date
905 ,p_business_group_id => p_business_group_id
906 ,p_section_name => l_path_name
907 ,p_learning_path_id => l_learning_path_id
908 ,p_section_sequence => 1
909 ,p_completion_type_code => 'M'
910 ,p_learning_path_section_id => l_learning_path_section_id
911 ,p_object_version_number => l_lpc_ovn
912 ,p_return_status => l_lpc_rtn_status
913 );
914
915 OPEN csr_get_lpc;
916 FETCH csr_get_lpc INTO l_learning_path_section_id;
917 CLOSE csr_get_lpc;
918
919
920 -- If Learning Path section is not created, rollback and return
921 if (l_lpc_rtn_status = 'E') then
922 ROLLBACK TO create_talent_mgmt_lpm;
923 p_object_version_number := NULL;
924 p_return_status := hr_multi_message.get_return_status_disable;
925 return;
926 end if;
927 END IF;
928
929 ota_lp_member_swi.create_learning_path_member
930 (p_validate => p_validate
931 ,p_effective_date => p_effective_date
932 ,p_business_group_id => p_business_group_id
933 ,p_learning_path_id => l_learning_path_id
934 ,p_activity_version_id => p_activity_version_id
935 ,p_course_sequence => p_course_sequence
936 ,p_learning_path_section_id => l_learning_path_section_id
937 ,p_notify_days_before_target => p_notify_days_before_target
938 ,p_learning_path_member_id => l_learning_path_member_id
939 ,p_object_version_number => l_lpm_ovn
940 ,p_return_status => l_lpm_rtn_status
941 );
942
943 OPEN csr_get_lpm;
944 FETCH csr_get_lpm INTO l_learning_path_member_id;
945 CLOSE csr_get_lpm;
946
947
948 -- If Learning Path member is not created, rollback and return
949 if (l_lpm_rtn_status = 'E') then
950 ROLLBACK TO create_talent_mgmt_lpm;
951 p_object_version_number := NULL;
952 p_return_status := hr_multi_message.get_return_status_disable;
953 return;
954 end if;
955
956
957 ota_lp_member_enrollment_swi.create_lp_member_enrollment
958 (p_effective_date => p_effective_date
959 ,p_validate => p_validate
960 ,p_lp_enrollment_id => l_lp_enrollment_id
961 ,p_learning_path_section_id => l_learning_path_section_id
962 ,p_learning_path_member_id => l_learning_path_member_id
963 ,p_member_status_code => p_member_status_code
964 ,p_completion_target_date => p_completion_target_date
965 ,p_business_group_id => p_business_group_id
966 ,p_lp_member_enrollment_id => l_lp_member_enrollment_id
967 ,p_object_version_number => l_lme_ovn
968 ,p_return_status => l_lme_rtn_status
969 );
970
971
972 -- Derive the API return status value based on whether
973 -- messages of any type exist in the Multiple Message List.
974 -- Also disable Multiple Message Detection.
975 --
976 p_return_status := hr_multi_message.get_return_status_disable;
977 hr_utility.set_location(' Leaving:' || l_proc,20);
978 --
979 EXCEPTION
980 WHEN hr_multi_message.error_message_exist THEN
981 --
982 -- Catch the Multiple Message List exception which
983 -- indicates API processing has been aborted because
984 -- at least one message exists in the list.
985 --
986 ROLLBACK TO create_talent_mgmt_lpm;
987 --
988 -- Reset IN OUT parameters and set OUT parameters
989 --
990 p_object_version_NUMBER := NULL;
991 p_return_status := hr_multi_message.get_return_status_disable;
992 hr_utility.set_location(' Leaving:' || l_proc, 30);
993
994 WHEN others THEN
995 --
996 -- When Multiple Message Detection is enabled catch
997 -- any Application specific or other unexpected
998 -- exceptions. Adding appropriate details to the
999 -- Multiple Message List. Otherwise re-raise the
1000 -- error.
1001 --
1002 ROLLBACK TO create_talent_mgmt_lpm;
1003 IF hr_multi_message.unexpected_error_add(l_proc) THEN
1004 hr_utility.set_location(' Leaving:' || l_proc,40);
1005 RAISE;
1006 END IF;
1007 --
1008 -- Reset IN OUT and set OUT parameters
1009 --
1010 p_object_version_NUMBER := NULL;
1011 p_return_status := hr_multi_message.get_return_status_disable;
1012 hr_utility.set_location(' Leaving:' || l_proc,50);
1013 END create_talent_mgmt_lpm;
1014
1015 -- ----------------------------------------------------------------------------
1016 -- ----------------------------------------------------------------------------
1017 -- |----------------------<update_talent_mgmt_lp >-----------------------------|
1018 -- ----------------------------------------------------------------------------
1019 PROCEDURE update_talent_mgmt_lp
1020 (p_validate IN NUMBER DEFAULT hr_api.g_false_num
1021 ,p_effective_date IN DATE
1022 ,p_mode IN VARCHAR2
1023 ,p_learning_path_id IN NUMBER DEFAULT NULL
1024 ,p_lp_enrollment_id IN NUMBER DEFAULT NULL
1025 ,p_source_function_code IN VARCHAR2
1026 ,p_assignment_id IN NUMBER DEFAULT NULL
1027 ,p_source_id IN NUMBER DEFAULT NULL
1028 ,p_person_id IN NUMBER
1029 ,p_display_to_learner_flag IN VARCHAR2
1030 ,p_lps_ovn IN OUT NOCOPY NUMBER
1031 ,p_lpe_ovn IN OUT NOCOPY NUMBER
1032 ,p_return_status OUT NOCOPY VARCHAR2
1033 ) IS
1034 --
1035 -- Variables for API Boolean parameters
1036 l_validate boolean;
1037 --
1038 -- Variables for IN/OUT parameters
1039 --
1040 -- Other variables
1041 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
1042 l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
1043
1044 l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
1045 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1046 l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
1047 l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
1048 l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
1049 l_completion_date ota_lp_enrollments.completion_date%TYPE := null;
1050
1051 l_lp_ovn number;
1052 l_lpe_ovn number;
1053 l_lme_ovn number;
1054
1055 l_lp_rtn_status varchar2(30);
1056 l_lpe_rtn_status varchar2(30);
1057 l_lme_rtn_status varchar2(30);
1058
1059 l_proc varchar2(72) := g_package ||'update_talent_mgmt_lp';
1060
1061 CURSOR csr_get_lp IS
1062 SELECT lps.learning_path_id,
1063 lps.object_version_number lps_ovn,
1064 lpe.lp_enrollment_id,
1065 lpe.object_version_number lpe_ovn
1066 FROM ota_learning_paths lps,
1067 ota_lp_enrollments lpe
1068 WHERE lps.learning_path_id = lpe.learning_path_id
1069 AND lps.path_source_code = 'TALENT_MGMT'
1070 AND lps.source_function_code = p_source_function_code
1071 AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND lps.source_id = p_source_id))
1072 AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND lps.assignment_id = p_assignment_id));
1073
1074 CURSOR csr_get_appr_lme IS
1075 SELECT lme.lp_member_enrollment_id,
1076 lme.object_version_number,
1077 lme.learning_path_member_id,
1078 lme.lp_enrollment_id
1079 FROM ota_lp_member_enrollments lme,
1080 ota_learning_path_members lpm
1081 WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1082 AND lpm.learning_path_id = l_learning_path_id
1083 AND lme.member_status_code = 'AWAITING_APPROVAL';
1084
1085 CURSOR csr_get_cncl_lme IS
1086 SELECT lme.lp_member_enrollment_id,
1087 lme.object_version_number
1088 FROM ota_lp_member_enrollments lme,
1089 ota_learning_path_members lpm
1090 WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1091 AND lpm.learning_path_id = l_learning_path_id
1092 AND lme.member_status_code <> 'CANCELLED';
1093
1094 CURSOR csr_get_lpe_dtls(lp_id NUMBER, lpe_id NUMBER) IS
1095 SELECT ota_lrng_path_util.get_no_of_mandatory_courses(lp_id,'TALENT_MGMT') mandatory_courses,
1096 ota_lrng_path_util.get_no_of_completed_courses(lpe_id,'TALENT_MGMT') completed_courses
1097 FROM dual;
1098
1099
1100 BEGIN
1101 hr_utility.set_location(' Entering:' || l_proc,10);
1102 --
1103 -- Issue a savepoint
1104 --
1105 SAVEPOINT update_talent_mgmt_lp;
1106
1107 --
1108 -- Check if the call is from SSHR - Appraisal / Suitability Matching / Succession Planning
1109 --
1110
1111 -- SSHR call should have person Id. Mandatory check for personId.
1112 hr_api.mandatory_arg_error
1113 (p_api_name => l_proc
1114 ,p_argument => 'p_person_id'
1115 ,p_argument_value => p_person_id
1116 );
1117 IF p_learning_path_id IS NULL THEN
1118 OPEN csr_get_lp;
1119 FETCH csr_get_lp INTO l_learning_path_id,
1120 l_lp_ovn,
1121 l_lp_enrollment_id,
1122 l_lpe_ovn;
1123 CLOSE csr_get_lp;
1124 ELSE
1125 l_learning_path_id := p_learning_path_id;
1126 l_lp_enrollment_id := p_lp_enrollment_id;
1127 l_lp_ovn := p_lps_ovn;
1128 l_lpe_ovn := p_lpe_ovn;
1129 END IF;
1130
1131
1132 IF p_mode = 'APPROVED' THEN
1133 l_path_status_code := 'ACTIVE';
1134 ELSE l_path_status_code := 'CANCELLED';
1135 END IF;
1136
1137 ota_learning_path_swi.update_learning_path
1138 (p_effective_date => p_effective_date
1139 ,p_learning_path_id => l_learning_path_id
1140 ,p_object_version_number => l_lp_ovn
1141 ,p_display_to_learner_flag => p_display_to_learner_flag
1142 ,p_validate => p_validate
1143 ,p_return_status => l_lp_rtn_status
1144 );
1145
1146
1147 -- If Learning Path is not updated, rollback and return
1148 if (l_lp_rtn_status = 'E') then
1149 ROLLBACK TO update_talent_mgmt_lp;
1150 p_return_status := hr_multi_message.get_return_status_disable;
1151 return;
1152 end if;
1153
1154 ota_lp_enrollment_swi.update_lp_enrollment
1155 (p_effective_date => p_effective_date
1156 ,p_lp_enrollment_id => l_lp_enrollment_id
1157 ,p_object_version_number => l_lpe_ovn
1158 ,p_path_status_code => l_path_status_code
1159 ,p_return_status => l_lpe_rtn_status
1160 );
1161
1162 -- If Learning Path enrollment is not created, rollback and return
1163 if (l_lpe_rtn_status = 'E') then
1164 ROLLBACK TO update_talent_mgmt_lp;
1165 p_return_status := hr_multi_message.get_return_status_disable;
1166 return;
1167 end if;
1168
1169 IF p_mode = 'APPROVED' THEN
1170 l_member_status_code := 'PLANNED';
1171
1172 FOR appr_rec IN csr_get_appr_lme
1173 LOOP
1174
1175 l_lme_ovn := appr_rec.object_version_number;
1176
1177 ota_lp_member_enrollment_swi.update_lp_member_enrollment
1178 (p_effective_date => p_effective_date
1179 ,p_lp_member_enrollment_id => appr_rec.lp_member_enrollment_id
1180 ,p_lp_enrollment_id => appr_rec.lp_enrollment_id
1181 ,p_learning_path_member_id => appr_rec.learning_path_member_id
1182 ,p_object_version_number => l_lme_ovn
1183 ,p_validate => p_validate
1184 ,p_member_status_code => l_member_status_code
1185 ,p_return_status => l_lme_rtn_status
1186 );
1187
1188 if (l_lme_rtn_status = 'E') then
1189 ROLLBACK TO update_talent_mgmt_lp;
1190 p_return_status := hr_multi_message.get_return_status_disable;
1191 return;
1192 end if;
1193
1194 END LOOP;
1195
1196 OPEN csr_get_lpe_dtls(l_learning_path_id, l_lp_enrollment_id);
1197 FETCH csr_get_lpe_dtls INTO l_no_of_mandatory_courses,
1198 l_no_of_completed_courses;
1199 CLOSE csr_get_lpe_dtls;
1200
1201 IF l_no_of_mandatory_courses = l_no_of_completed_courses THEN
1202 l_path_status_code := 'COMPLETED';
1203 l_completion_date := get_lp_completion_date(l_lp_enrollment_id);
1204 END IF;
1205
1206
1207 ota_lp_enrollment_swi.update_lp_enrollment
1208 (p_effective_date => p_effective_date
1209 ,p_lp_enrollment_id => l_lp_enrollment_id
1210 ,p_object_version_number => l_lpe_ovn
1211 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
1212 ,p_no_of_completed_courses => l_no_of_completed_courses
1213 ,p_path_status_code => l_path_status_code
1214 ,p_completion_date => l_completion_date
1215 ,p_return_status => l_lpe_rtn_status
1216 );
1217
1218
1219 -- If Learning Path enrollment is not created, rollback and return
1220 if (l_lpe_rtn_status = 'E') then
1221 ROLLBACK TO update_talent_mgmt_lp;
1222 p_return_status := hr_multi_message.get_return_status_disable;
1223 return;
1224 end if;
1225
1226
1227
1228 ELSE
1229
1230 l_member_status_code := 'CANCELLED';
1231
1232 FOR cncl_rec IN csr_get_cncl_lme
1233 LOOP
1234 l_lme_ovn := cncl_rec.object_version_number;
1235
1236 ota_lp_member_enrollment_swi.update_lp_member_enrollment
1237 (p_effective_date => p_effective_date
1238 ,p_lp_member_enrollment_id => cncl_rec.lp_member_enrollment_id
1239 ,p_object_version_number => l_lme_ovn
1240 ,p_validate => p_validate
1241 ,p_member_status_code => l_member_status_code
1242 ,p_return_status => l_lme_rtn_status
1243 );
1244
1245 if (l_lme_rtn_status = 'E') then
1246 ROLLBACK TO update_talent_mgmt_lp;
1247 p_return_status := hr_multi_message.get_return_status_disable;
1248 return;
1249 end if;
1250
1251 END LOOP;
1252 END IF;
1253
1254 -- Derive the API return status value based on whether
1255 -- messages of any type exist in the Multiple Message List.
1256 -- Also disable Multiple Message Detection.
1257 --
1258 p_return_status := hr_multi_message.get_return_status_disable;
1259 hr_utility.set_location(' Leaving:' || l_proc,20);
1260 --
1261 EXCEPTION
1262 WHEN hr_multi_message.error_message_exist THEN
1263 --
1264 -- Catch the Multiple Message List exception which
1265 -- indicates API processing has been aborted because
1266 -- at least one message exists in the list.
1267 --
1268 ROLLBACK TO update_talent_mgmt_lp;
1269 --
1270 -- Reset IN OUT parameters and set OUT parameters
1271 --
1272 p_return_status := hr_multi_message.get_return_status_disable;
1273 hr_utility.set_location(' Leaving:' || l_proc, 30);
1274
1275 WHEN others THEN
1276 --
1277 -- When Multiple Message Detection is enabled catch
1278 -- any Application specific or other unexpected
1279 -- exceptions. Adding appropriate details to the
1280 -- Multiple Message List. Otherwise re-raise the
1281 -- error.
1282 --
1283 ROLLBACK TO update_talent_mgmt_lp;
1284 IF hr_multi_message.unexpected_error_add(l_proc) THEN
1285 hr_utility.set_location(' Leaving:' || l_proc,40);
1286 RAISE;
1287 END IF;
1288 --
1289 -- Reset IN OUT and set OUT parameters
1290 --
1291 p_return_status := hr_multi_message.get_return_status_disable;
1292 hr_utility.set_location(' Leaving:' || l_proc,50);
1293 END update_talent_mgmt_lp;
1294 -- ----------------------------------------------------------------------------
1295 -- |-------------------< chk_no_of_mandatory_courses >-------------------------|
1296 -- ----------------------------------------------------------------------------
1297 --
1298 PROCEDURE chk_no_of_mandatory_courses
1299 (p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE
1300 , p_return_status OUT NOCOPY VARCHAR2)
1301 IS
1302 --
1303 l_proc VARCHAR2(72) := g_package|| 'chk_no_of_mandatory_courses';
1304 l_exists VARCHAR2(1);
1305 l_lpm_count NUMBER;
1306 l_lpc_id ota_lp_sections.learning_path_section_id%TYPE;
1307 l_mandatory_courses ota_lp_sections.no_of_mandatory_courses%TYPE;
1308 --
1309 CURSOR get_section_info IS
1310 SELECT lpm.learning_path_section_id,
1311 lpc.no_of_mandatory_courses
1312 FROM ota_learning_path_members lpm,
1313 ota_lp_sections lpc
1314 WHERE lpm.learning_path_section_id = lpc.learning_path_section_id
1315 AND lpc.completion_type_code = 'S'
1316 AND lpm.learning_path_member_id = p_learning_path_member_id;
1317
1318 CURSOR get_lpm_count IS
1319 SELECT count(learning_path_member_id)
1320 FROM ota_learning_path_members
1321 WHERE learning_path_section_id = l_lpc_id;
1322
1323 BEGIN
1324 --
1325
1326
1327 hr_utility.set_location(' Step:'|| l_proc, 30);
1328
1329
1330 p_return_status := 'S';
1331 OPEN get_section_info;
1332 FETCH get_section_info INTO l_lpc_id, l_mandatory_courses;
1333 IF get_section_info%FOUND THEN
1334 OPEN get_lpm_count;
1335 FETCH get_lpm_count INTO l_lpm_count;
1336 CLOSE get_lpm_count;
1337 IF l_lpm_count <= l_mandatory_courses THEN
1338 p_return_status := 'E';
1339 -- fnd_message.set_name('OTA', 'OTA_13076_LPC_MNDTRY_ACT_ERR');
1340 -- fnd_message.raise_error;
1341 END IF;
1342 END IF;
1343 CLOSE get_section_info;
1344
1345 hr_utility.set_location(' Leaving:'||l_proc, 90);
1346 END chk_no_of_mandatory_courses;
1347
1348 FUNCTION get_class_completion_date(p_event_id IN ota_events.event_id%type,
1349 p_person_id IN NUMBER,
1350 p_contact_id IN ota_attempts.user_type%type)
1351 RETURN DATE IS
1352
1353 CURSOR class_type IS
1354 SELECT ocu.synchronous_flag,
1355 ocu.online_flag
1356 FROM ota_events oev,
1357 ota_offerings ofr,
1358 ota_category_usages ocu
1359 WHERE oev.parent_offering_id = ofr.offering_id
1360 AND ocu.category_usage_Id = ofr.delivery_mode_id
1361 AND oev.event_id = p_event_id;
1362
1363 CURSOR get_online_compl_date_p(p_event_id IN ota_events.event_id%type,
1364 p_user_id IN ota_attempts.user_id%type,
1365 p_user_type IN ota_attempts.user_type%type) IS
1366 SELECT ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) completion_date
1367 FROM ota_events oev,
1368 ota_offerings ofr,
1369 ota_performances opf,
1370 ota_category_usages ocu,
1371 ota_delegate_bookings odb,
1372 ota_booking_status_types obst
1373 WHERE oev.parent_offering_id = ofr.offering_id
1374 AND ofr.learning_object_id = opf.learning_object_id
1375 AND ocu.category_usage_Id = ofr.delivery_mode_id
1376 AND opf.completed_date is not null
1377 AND oev.event_id = p_event_id
1378 AND opf.User_id = p_user_id
1379 AND opf.User_type = p_user_type
1380 AND odb.booking_status_type_id = obst.booking_status_type_id
1381 AND obst.type = 'A'
1382 AND odb.delegate_person_id = p_person_id
1383 AND oev.event_id = odb.event_id;
1384
1385 CURSOR get_online_compl_date_c(p_event_id IN ota_events.event_id%type,
1386 p_user_id IN ota_attempts.user_id%type,
1387 p_user_type IN ota_attempts.user_type%type) IS
1388 SELECT ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) completion_date
1389 FROM ota_events oev,
1390 ota_offerings ofr,
1391 ota_performances opf,
1392 ota_category_usages ocu,
1393 ota_delegate_bookings odb,
1394 ota_booking_status_types obst
1395 WHERE oev.parent_offering_id = ofr.offering_id
1396 AND ofr.learning_object_id = opf.learning_object_id
1397 AND ocu.category_usage_Id = ofr.delivery_mode_id
1398 AND opf.completed_date is not null
1399 AND oev.event_id = p_event_id
1400 AND opf.User_id = p_user_id
1401 AND opf.User_type = p_user_type
1402 AND odb.booking_status_type_id = obst.booking_status_type_id
1403 AND obst.type = 'A'
1404 AND odb.delegate_contact_id = p_contact_id
1405 AND oev.event_id = odb.event_id;
1406
1407 CURSOR get_offsync_class_compl_date_p IS
1408 SELECT to_date(to_char(nvl(oev.course_end_date,trunc(sysdate)),'YYYY/MM/DD') || ' ' || nvl(oev.course_end_time,'23:59'), 'YYYY MM/DD HH24:MI') event_end_date
1409 FROM ota_events oev,
1410 ota_delegate_bookings odb,
1411 ota_booking_status_types obst
1412 WHERE oev.event_id = p_event_id
1413 AND oev.event_id = odb.event_id
1414 AND odb.booking_status_type_id = obst.booking_status_type_id
1415 AND obst.type = 'A'
1416 AND odb.delegate_person_id = p_person_id;
1417
1418 CURSOR get_offsync_class_compl_date_c IS
1419 SELECT to_date(to_char(nvl(oev.course_end_date,trunc(sysdate)),'YYYY/MM/DD') || ' ' || nvl(oev.course_end_time,'23:59'), 'YYYY MM/DD HH24:MI') event_end_date
1420 FROM ota_events oev,
1421 ota_delegate_bookings odb,
1422 ota_booking_status_types obst
1423 WHERE oev.event_id = p_event_id
1424 AND oev.event_id = odb.event_id
1425 AND odb.booking_status_type_id = obst.booking_status_type_id
1426 AND obst.type = 'A'
1427 AND odb.delegate_contact_id = p_contact_id;
1428
1429 CURSOR get_offasync_compl_date_p IS
1430 SELECT odb.date_status_changed
1431 FROM ota_delegate_bookings odb,
1432 ota_booking_status_types obst
1433 WHERE odb.booking_status_type_id = obst.booking_status_type_id
1434 AND obst.type = 'A'
1435 AND odb.delegate_person_id = p_person_id
1436 AND odb.event_id = p_event_id;
1437
1438 CURSOR get_offasync_compl_date_c IS
1439 SELECT odb.date_status_changed
1440 FROM ota_delegate_bookings odb,
1441 ota_booking_status_types obst
1442 WHERE odb.booking_status_type_id = obst.booking_status_type_id
1443 AND obst.type = 'A'
1444 AND odb.delegate_contact_id = p_contact_id
1445 AND odb.event_id = p_event_id;
1446
1447 l_sync_flag ota_category_usages.synchronous_flag%type;
1448 l_online_flag ota_category_usages.online_flag%type;
1449 l_completion_date date:= null;
1450 l_user_id ota_attempts.user_id%type;
1451 l_user_type ota_attempts.user_type%type;
1452
1453 BEGIN
1454
1455 OPEN class_type;
1456 FETCH class_type into l_sync_flag, l_online_flag;
1457 CLOSE class_type;
1458
1459 if(l_online_flag = 'Y') then
1460 --get the compeltion date from ota_performances
1461 l_user_id := nvl(p_person_id, p_contact_id);
1462 if(p_person_id is not null) then
1463 l_user_type := 'E';
1464
1465 OPEN get_online_compl_date_p(p_event_id, l_user_id, l_user_type);
1466 FETCH get_online_compl_date_p into l_completion_date;
1467 CLOSE get_online_compl_date_p;
1468
1469 else
1470 l_user_type := 'C';
1471
1472 OPEN get_online_compl_date_c(p_event_id, l_user_id, l_user_type);
1473 FETCH get_online_compl_date_c into l_completion_date;
1474 CLOSE get_online_compl_date_c;
1475
1476 end if;
1477
1478 elsif(l_online_flag = 'N' and l_sync_flag = 'Y') then
1479 --get the end date of the class as compeltion date
1480 if(p_person_id is not null) then
1481 OPEN get_offsync_class_compl_date_p;
1482 FETCH get_offsync_class_compl_date_p into l_completion_date;
1483 CLOSE get_offsync_class_compl_date_p;
1484 else
1485 OPEN get_offsync_class_compl_date_c;
1486 FETCH get_offsync_class_compl_date_c into l_completion_date;
1487 CLOSE get_offsync_class_compl_date_c;
1488 end if;
1489
1490 elsif(l_online_flag = 'N' and l_sync_flag = 'N') then
1491 --get the date_status_changed of the class as compeltion date
1492 if(p_person_id is not null) then
1493 OPEN get_offasync_compl_date_p;
1494 FETCH get_offasync_compl_date_p into l_completion_date;
1495 CLOSE get_offasync_compl_date_p;
1496 else
1497 OPEN get_offasync_compl_date_c;
1498 FETCH get_offasync_compl_date_c into l_completion_date;
1499 CLOSE get_offasync_compl_date_c;
1500 end if;
1501 end if;
1502
1503 RETURN l_completion_date;
1504
1505 END get_class_completion_date;
1506
1507 FUNCTION get_lp_completion_date(p_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE)
1508 RETURN DATE IS
1509
1510 CURSOR get_completion_date IS
1511 SELECT max(ota_lrng_path_member_util.get_class_completion_date(oev.event_id,lpe.person_id, lpe.contact_id)) completion_date
1512 FROM ota_lp_enrollments lpe,
1513 ota_learning_path_members lpm,
1514 ota_events oev
1515 WHERE lpe.learning_path_id = lpm.learning_path_id
1516 AND oev.activity_version_id = lpm.activity_version_id
1517 AND lpe.lp_enrollment_id = p_lp_enrollment_id;
1518
1519 l_completion_date date := null;
1520
1521 BEGIN
1522 if(ota_lrng_path_util.chk_complete_path_ok(p_lp_enrollment_id) = 'S') then
1523 OPEN get_completion_date;
1524 FETCH get_completion_date into l_completion_date;
1525 CLOSE get_completion_date;
1526 end if;
1527
1528 RETURN l_completion_date;
1529
1530 END get_lp_completion_date;
1531
1532 FUNCTION get_lpm_completion_date(p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
1533 p_activity_version_id ota_activity_versions.activity_version_id%TYPE,
1534 p_person_id ota_lp_enrollments.person_id%TYPE,
1535 p_contact_id ota_lp_enrollments.contact_id%TYPE)
1536 RETURN DATE IS
1537
1538 CURSOR get_lpm_completion_date IS
1539 SELECT max(ota_lrng_path_member_util.get_class_completion_date(oev.event_id,lpe.person_id, lpe.contact_id)) completion_date
1540 FROM ota_lp_enrollments lpe,
1541 ota_learning_path_members lpm,
1542 ota_lp_member_enrollments lpme,
1543 ota_events oev
1544 WHERE lpe.learning_path_id = lpm.learning_path_id
1545 AND oev.activity_version_id = lpm.activity_version_id
1546 AND lpe.lp_enrollment_id = lpme.lp_enrollment_id
1547 AND lpme.learning_path_member_id = lpm.learning_path_member_id
1548 AND lpme.lp_member_enrollment_id = p_lp_member_enrollment_id;
1549
1550 CURSOR get_crs_completion_date IS
1551 SELECT max(ota_lrng_path_member_util.get_class_completion_date(oev.event_id,p_person_id, p_contact_id)) completion_date
1552 FROM ota_events oev
1553 WHERE oev.activity_version_id = p_activity_version_id;
1554
1555 l_completion_date date := null;
1556
1557 BEGIN
1558 if(p_lp_member_enrollment_id is not null) then
1559 OPEN get_lpm_completion_date;
1560 FETCH get_lpm_completion_date into l_completion_date;
1561 CLOSE get_lpm_completion_date;
1562 elsif (p_activity_version_id is not null) then
1563 OPEN get_crs_completion_date;
1564 FETCH get_crs_completion_date into l_completion_date;
1565 CLOSE get_crs_completion_date;
1566 end if;
1567
1568 RETURN l_completion_date;
1569
1570 END get_lpm_completion_date;
1571
1572 END ota_lrng_path_member_util;
1573
1574