[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.0.12010000.2 2008/08/05 11:44:42 ubhat 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 IS
105
106
107 CURSOR csr_lp_enr IS
108 SELECT DECODE(bst.type,'C','Z',bst.type) status,
109 tdb.date_status_changed
110 FROM ota_learning_path_members lpm,
111 ota_lp_member_enrollments lme,
112 ota_events evt,
113 ota_delegate_bookings tdb,
114 ota_booking_status_types bst
115 WHERE lpm.activity_version_id = evt.activity_version_id
116 AND evt.event_id = tdb.event_id
117 AND bst.booking_status_type_id = tdb.booking_status_type_id
118 AND lme.learning_path_member_id = lpm.learning_path_member_id
119 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id
120 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
121 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
122 )
123 ORDER BY status, evt.course_start_date;
124
125 CURSOR csr_act_enr IS
126 SELECT DECODE(bst.type,'C','Z',bst.type) status,
127 tdb.date_status_changed
128 FROM ota_events evt,
129 ota_delegate_bookings tdb,
130 ota_booking_status_types_vl bst
131 WHERE evt.event_id = tdb.event_id
132 AND bst.booking_status_type_id = tdb.booking_status_type_id
133 AND evt.activity_version_id = p_activity_version_id
134 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
135 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
136 )
137 ORDER BY status, evt.course_start_date;
138
139 l_proc VARCHAR2(72) := g_package|| 'get_enrollment_status';
140
141 v_enroll_status VARCHAR2(30);
142 v_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
143
144 BEGIN
145
146 hr_utility.set_location(' Step:'|| l_proc, 10);
147 IF p_lp_member_enrollment_id IS NOT NULL THEN
148 FOR rec_lp_enr IN csr_lp_enr
149 LOOP
150 v_enroll_status := rec_lp_enr.status ;
151 v_date_status_changed := rec_lp_enr.date_status_changed;
152 EXIT;
153 END LOOP;
154 ELSE
155 FOR rec_act_enr IN csr_act_enr
156 LOOP
157 v_enroll_status := rec_act_enr.status ;
158 v_date_status_changed := rec_act_enr.date_status_changed;
159 EXIT;
160 END LOOP;
161 END IF;
162
163 p_booking_status_type := v_enroll_status;
164 p_date_status_changed := v_date_status_changed;
165
166 hr_utility.set_location(' Step:'|| l_proc, 20);
167
168 END get_enrollment_status;
169 --
170 -- ---------------------------------------------------------------------------
171 -- |----------------------< chk_enrollment_exist >--------------------------|
172 -- ---------------------------------------------------------------------------
173 FUNCTION chk_enrollment_exist(p_person_id IN ota_learning_paths.person_id%TYPE,
174 p_contact_id IN ota_learning_paths.contact_id%TYPE,
175 p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE)
176 RETURN boolean
177 IS
178 CURSOR chk_enr IS
179 SELECT NULL
180 FROM ota_events e,
181 ota_activity_versions a,
182 ota_delegate_bookings b,
183 ota_booking_status_types s,
184 ota_learning_path_members lpm
185 WHERE e.event_id = b.event_id
186 AND lpm.activity_version_id = a.activity_version_id
187 AND e.activity_version_id = a.activity_version_id
188 AND b.booking_status_type_id = s.booking_status_type_id
189 AND ((p_person_id IS NOT NULL AND b.delegate_person_id = p_person_id)
190 OR (p_contact_id IS NOT NULL AND b.delegate_contact_id = p_contact_id)
191 )
192 AND lpm.learning_path_member_id = p_learning_path_member_id;
193
194 l_proc VARCHAR2(72) := g_package|| 'chk_enrollment_exist';
195 l_return_val VARCHAR2(1);
196 l_found BOOLEAN := FALSE;
197
198 BEGIN
199
200 hr_utility.set_location(' Step:'|| l_proc, 10);
201
202 OPEN chk_enr;
203 FETCH chk_enr INTO l_return_val;
204 IF chk_enr%FOUND THEN
205 --
206 l_found := TRUE;
207 --
208 END IF;
209 CLOSE chk_enr;
210 --
211 hr_utility.set_location('Leaving '||l_proc,10);
212
213 RETURN l_found;
214
215 END chk_enrollment_exist;
216
217 -- ---------------------------------------------------------------------------
218 -- |----------------------< calculate_lme_status >-----------------------------|
219 -- ---------------------------------------------------------------------------
220 PROCEDURE calculate_lme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
221 p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
222 p_member_status_code OUT nocopy VARCHAR2,
223 p_completion_date OUT nocopy DATE)
224 IS
225
226 l_proc VARCHAR2(72) := g_package|| 'calculate_lme_status';
227 l_enroll_status VARCHAR2(30);
228 l_date_status_changed DATE;
229 l_person_id ota_learning_paths.person_id%TYPE;
230 l_contact_id ota_learning_paths.contact_id%TYPE;
231
232 BEGIN
233
234 hr_utility.set_location('Entering:'|| l_proc, 10);
235
236 SELECT lpe.person_id, lpe.contact_id
237 INTO l_person_id , l_contact_id
238 FROM ota_lp_enrollments lpe
239 where lpe.lp_enrollment_id = p_lp_enrollment_id;
240
241 get_enrollment_status(p_person_id => l_person_id,
242 p_contact_id => l_contact_id,
243 p_activity_version_id => p_activity_version_id,
244 p_lp_member_enrollment_id => null,
245 p_booking_status_type => l_enroll_status,
246 p_date_status_changed => l_date_status_changed);
247
248 IF ( l_enroll_status='A' ) THEN
249
250 p_member_status_code := 'COMPLETED';
251 p_completion_date := l_date_status_changed;
252
253 ELSIF ( l_enroll_status='P'
254 OR l_enroll_status='W'
255 OR l_enroll_status ='R') THEN
256
257 p_member_status_code := 'ACTIVE';
258 p_completion_date := null;
259 ELSE
260 p_member_status_code := 'PLANNED';
261 p_completion_date := null;
262 END IF;
263
264 hr_utility.set_location('LEAVING:'|| l_proc, 20);
265
266 EXCEPTION
267 WHEN others THEN
268 p_member_status_code := 'PLANNED';
269 RAISE;
270
271 END calculate_lme_status;
272
273 -- ---------------------------------------------------------------------------
274 -- |----------------------< get_lme_status >-----------------------------|
275 -- ---------------------------------------------------------------------------
276 FUNCTION get_lme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
277 p_person_id IN ota_learning_paths.person_id%TYPE,
278 p_contact_id IN ota_learning_paths.contact_id%TYPE)
279 RETURN VARCHAR2
280 IS
281
282 l_proc VARCHAR2(72) := g_package|| 'get_lme_status';
283 l_enroll_status VARCHAR2(30);
284 l_member_status_code VARCHAR2(30);
285 l_date_status_changed DATE;
286 l_person_id ota_learning_paths.person_id%TYPE;
287 l_contact_id ota_learning_paths.contact_id%TYPE;
288
289 BEGIN
290
291 hr_utility.set_location('Entering:'|| l_proc, 10);
292
293 get_enrollment_status(p_person_id => p_person_id,
294 p_contact_id => p_contact_id,
295 p_activity_version_id => p_activity_version_id,
296 p_lp_member_enrollment_id => null,
297 p_booking_status_type => l_enroll_status,
298 p_date_status_changed => l_date_status_changed);
299
300 IF ( l_enroll_status='A' ) THEN
301
302 l_member_status_code := 'COMPLETED';
303
304
305 ELSIF ( l_enroll_status='P'
306 OR l_enroll_status='W'
307 OR l_enroll_status ='R') THEN
308
309 l_member_status_code := 'ACTIVE';
310
311 ELSE
312 l_member_status_code := 'PLANNED';
313
314 END IF;
315
316 hr_utility.set_location('LEAVING:'|| l_proc, 20);
317
318 RETURN l_member_status_code;
319
320 END get_lme_status;
321
322
323 -- ----------------------------------------------------------------------------
324 -- |----------------------< get_lpc_completed_courses >---------------------|
325 -- ---------------------------------------------------------------------------
326 FUNCTION get_lpc_completed_courses(p_learning_path_section_id IN ota_lp_sections.learning_path_section_id%TYPE)
327 RETURN NUMBER IS
328
329 CURSOR csr_lpc_comp IS
330 SELECT count(lp_member_enrollment_id)
331 FROM ota_lp_member_enrollments
332 WHERE learning_path_section_id = p_learning_path_section_id
333 AND member_status_code = 'COMPLETED';
334
335 l_proc VARCHAR2(72) := g_package|| 'get_lpc_completed_courses';
336
337 l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
338
339 BEGIN
340
341 hr_utility.set_location(' Step:'|| l_proc, 10);
342 OPEN csr_lpc_comp;
343 FETCH csr_lpc_comp INTO l_completed_courses;
344 CLOSE csr_lpc_comp;
345
346 hr_utility.set_location(' Step:'|| l_proc, 20);
347 RETURN l_completed_courses;
348
349 END get_lpc_completed_courses;
350
351 -- ----------------------------------------------------------------------------
352 -- |----------------------< chk_section_completion_type >---------------------|
353 -- ---------------------------------------------------------------------------
354 FUNCTION chk_section_completion_type(p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE)
355 RETURN VARCHAR2 IS
356
357 CURSOR csr_lpc_dtl IS
358 SELECT completion_type_code
359 FROM ota_lp_sections lpc,
360 ota_learning_path_members lpm
361 WHERE lpc.learning_path_section_id = lpm.learning_path_section_id
362 AND lpm.learning_path_member_id = p_learning_path_member_id;
363
364 l_proc VARCHAR2(72) := g_package|| 'chk_section_completion_type';
365
366 l_completion_type ota_lp_sections.completion_type_code%TYPE;
367
368 BEGIN
369
370 hr_utility.set_location(' Step:'|| l_proc, 10);
371 OPEN csr_lpc_dtl;
372 FETCH csr_lpc_dtl INTO l_completion_type;
373 CLOSE csr_lpc_dtl;
374
375 hr_utility.set_location(' Step:'|| l_proc, 20);
376 RETURN l_completion_type;
377
378 END chk_section_completion_type;
379
380 -- ----------------------------------------------------------------------------
381 -- |---------------------------< get_valid_enroll >-------------------------|
382 -- ----------------------------------------------------------------------------
383 PROCEDURE get_valid_enroll (p_person_id IN ota_learning_paths.person_id%TYPE
384 ,p_contact_id IN ota_learning_paths.contact_id%TYPE
385 ,p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE
386 ,p_return_status OUT nocopy VARCHAR2)
387 IS
388 l_enr_type VARCHAR2(30);
389 l_proc VARCHAR2(72) := g_package|| 'get_valid_enroll';
390 BEGIN
391
392 l_enr_type:= get_enrollment_status(p_person_id => p_person_id,
393 p_contact_id => p_contact_id,
394 p_activity_version_id => null,
395 p_lp_member_enrollment_id => p_lp_member_enrollment_id,
396 p_return_code => 'TYPE');
397 p_return_status := 'S';
398
399 IF ( l_enr_type IS NOT NULL AND l_enr_type <> 'Z' ) THEN
400 p_return_status := 'E';
401 END IF;
402
403 END get_valid_enroll;
404
405
406
407 -- ---------------------------------------------------------------------------
408 -- |----------------------< update_lme_enroll_status_chg >--------------------------|
409 -- ---------------------------------------------------------------------------
410 PROCEDURE update_lme_enroll_status_chg (p_event_id IN ota_events.event_id%TYPE,
411 p_person_id IN ota_lp_enrollments.person_id%TYPE,
412 p_contact_id IN ota_lp_enrollments.contact_id%TYPE,
413 p_lp_enrollment_ids OUT NOCOPY varchar2)
414 IS
415
416 l_proc VARCHAR2(72) := g_package|| 'update_lme_enroll_status_chg';
417
418
419 CURSOR evt_det IS
420 SELECT activity_version_id
421 FROM ota_events
422 WHERE event_id = p_event_id;
423
424 --get all the lpms which have the passed event as a component
425 CURSOR csr_lpm_info(csr_activity_version_id number) IS
426 SELECT olme.lp_member_enrollment_id,
427 olpe.lp_enrollment_id,
428 olme.object_version_number,
429 olpm.learning_path_section_id,
430 olpm.learning_path_member_id,
431 olpe.no_of_completed_courses,
432 olpe.no_of_mandatory_courses,
433 olme.member_status_code
434 FROM ota_learning_path_members olpm,
435 ota_lp_member_enrollments olme,
436 ota_lp_enrollments olpe
437 WHERE olpe.learning_path_id = olpm.learning_path_id
438 AND olpm.learning_path_member_id = olme.learning_path_member_id
439 AND olpe.lp_enrollment_id = olme.lp_enrollment_id
440 AND (( p_person_id IS NOT NULL AND olpe.person_id = p_person_id)
441 OR (p_contact_id IS NOT NULL AND olpe.contact_id = p_contact_id))
442 AND olpm.activity_version_id = csr_activity_version_id
443 AND olme.member_status_code <> 'CANCELLED';
444
445 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
446 l_lp_section_id ota_lp_sections.learning_path_section_id%TYPE;
447 l_completion_type_code ota_lp_sections.completion_type_code%TYPE;
448 l_enroll_type ota_booking_status_types.type%TYPE;
449 l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
450 l_completion_date ota_lp_enrollments.completion_date%TYPE;
451 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
452 l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE := 0;
453 l_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
454 l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
455
456 --variables to store old values
457 l_old_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
458 l_old_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
459 l_old_member_status ota_lp_member_enrollments.member_status_code%TYPE;
460
461
462 BEGIN
463
464
465 OPEN evt_det;
466 FETCH evt_det
467 INTO l_activity_version_id;
468 CLOSE evt_det;
469
470 hr_utility.set_location(' Step:'|| l_proc, 20);
471
472 FOR rec IN csr_lpm_info(l_activity_version_id)
473
474 LOOP
475
476 get_enrollment_status(p_person_id => p_person_id,
477 p_contact_id => p_contact_id,
478 p_activity_version_id => l_activity_version_id,
479 p_lp_member_enrollment_id => rec.lp_member_enrollment_id,
480 p_booking_status_type => l_enroll_type,
481 p_date_status_changed => l_date_status_changed);
482 l_completion_date := null;
483
484 IF l_enroll_type = 'A' THEN
485 l_member_status_code := 'COMPLETED';
486 l_completion_date := l_date_status_changed;
487 ELSIF ( l_enroll_type = 'P'
488 OR l_enroll_type = 'W'
489 OR l_enroll_type = 'R') THEN
490 l_member_status_code := 'ACTIVE';
491 ELSE l_member_status_code := 'PLANNED';
492 END IF;
493 l_old_member_status := rec.member_status_code;
494
495 IF l_old_member_status <> l_member_status_code THEN
496 --call upd lme api after lck
497 ota_lp_member_enrollment_api.update_lp_member_enrollment
498 (p_effective_date => sysdate
499 ,p_object_version_number => rec.object_version_number
500 ,p_learning_path_member_id => rec.learning_path_member_id
501 ,p_lp_enrollment_id => rec.lp_enrollment_id
502 ,p_lp_member_enrollment_id => rec.lp_member_enrollment_id
503 ,p_member_status_code => l_member_status_code
504 ,p_completion_date => l_completion_date);
505
506
507 l_completion_type_code := chk_section_completion_type(rec.learning_path_member_id);
508 l_old_mandatory_courses := NVL(rec.no_of_mandatory_courses,0);
509 l_old_completed_courses := NVL(rec.no_of_completed_courses,0);
510 l_completed_courses := l_old_completed_courses;
511
512
513 IF l_old_member_status IN ('PLANNED', 'ACTIVE', 'AWAITING_APPROVAL') and l_member_status_code = 'COMPLETED' THEN
514
515 IF l_completion_type_code = 'M' THEN
516 l_completed_courses := l_old_completed_courses +1 ;
517 ELSIF l_completion_type_code = 'S' THEN
518 l_section_completed_courses := get_lpc_completed_courses(rec.learning_path_section_id);
519 IF l_old_completed_courses < l_old_mandatory_courses THEN
520 l_completed_courses := l_old_completed_courses +1 ;
521 END IF;
522 END IF;
523 END IF;
524 IF l_old_member_status = 'COMPLETED' and l_member_status_code <> 'COMPLETED' THEN
525 IF l_completion_type_code = 'M' THEN
526 l_completed_courses := l_old_completed_courses -1;
527 ELSIF l_completion_type_code = 'S' THEN
528 l_section_completed_courses := get_lpc_completed_courses(rec.learning_path_section_id);
529 IF l_old_completed_courses <= l_old_mandatory_courses AND
530 l_section_completed_courses < l_old_mandatory_courses THEN
531 l_completed_courses := l_old_completed_courses - 1 ;
532 END IF;
533 END IF;
534 END IF;
535
536
537 Update_lpe_lme_change(rec.lp_member_enrollment_id, l_completed_courses, p_lp_enrollment_ids);
538
539 END IF;
540 END LOOP;
541
542 hr_utility.set_location(' Step:'|| l_proc, 30);
543
544 --MULTI MESSAGE SUPPORT
545
546
547 END update_lme_enroll_status_chg;
548
549 -- ---------------------------------------------------------------------------
550 -- |----------------------< Update_lpe_lme_change >--------------------------|
551 -- ---------------------------------------------------------------------------
552 --
553 -- This procedure will get called only when a tpc is Cancelled
554 Procedure Update_lpe_lme_change( p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE)
555 is
556
557 CURSOR csr_lpe_with_lme
558 IS
559 SELECT lpe.lp_enrollment_id,
560 lpe.path_status_code
561 FROM ota_lp_enrollments lpe,
562 ota_lp_member_enrollments lme
563 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
564 AND lpe.path_status_code <> 'CANCELLED'
565 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
566
567 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
568 IS
569 SELECT lpe.object_version_number
570 FROM ota_lp_enrollments lpe
571 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
572
573
574 l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
575 l_object_version_number ota_lp_enrollments.object_version_number%type;
576 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
577 l_complete_ok varchar2(1);
578
579 BEGIN
580 FOR rec1 in csr_lpe_with_lme LOOP
581 l_path_status_code :=rec1.path_status_code;
582 l_complete_ok := ota_lrng_path_util.chk_complete_path_ok(rec1.lp_enrollment_id);
583 IF l_complete_ok = 'S'
584 AND rec1.path_status_code = 'ACTIVE'
585 THEN
586 -- The Plan can be completed
587 l_path_status_code := 'COMPLETED';
588 ELSIF l_complete_ok = 'F' AND rec1.path_status_code = 'COMPLETED' THEN
589 l_path_status_code := 'ACTIVE';
590 END IF;
591
592 IF l_path_status_code <> rec1.path_status_code THEN
593 OPEN csr_lpe_update(rec1.lp_enrollment_id);
594 FETCH csr_lpe_update into l_object_version_number;
595 IF csr_lpe_update%FOUND then
596 CLOSE csr_lpe_update;
597 ota_lp_enrollment_api.update_lp_enrollment
598 (p_effective_date => sysdate
599 ,p_lp_enrollment_id => rec1.lp_enrollment_id
600 ,p_object_version_number => l_object_version_number
601 ,p_path_status_code => l_path_status_code);
602
603 ELSE
604 CLOSE csr_lpe_update;
605 END IF;
606 END IF;
607 END LOOP;
608 END Update_lpe_lme_change;
609
610
611 Procedure Update_lpe_lme_change (p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
612 p_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE,
613 p_lp_enrollment_ids OUT NOCOPY VARCHAR2)
614 is
615
616 CURSOR csr_lpe_with_lme
617 IS
618 SELECT lpe.lp_enrollment_id,
619 lpe.path_status_code,
620 lpe.learning_path_id,
621 lpe.no_of_mandatory_courses
622 FROM ota_lp_enrollments lpe,
623 ota_lp_member_enrollments lme
624 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
625 AND lpe.path_status_code <> 'CANCELLED'
626 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
627
628
629 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
630 IS
631 SELECT lpe.object_version_number
632 FROM ota_lp_enrollments lpe
633 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
634
635 l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
636 l_object_version_number ota_lp_enrollments.object_version_number%type;
637 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
638 l_completion_date DATE;
639 l_complete_ok varchar2(1);
640 l_lp_enrollment_ids varchar2(4000) := '';
641
642 BEGIN
643 FOR rec1 in csr_lpe_with_lme LOOP
644 l_path_status_code :=rec1.path_status_code;
645 l_complete_ok := ota_lrng_path_util.chk_complete_path_ok(rec1.lp_enrollment_id); --Bug#7028384
646 -- IF p_no_of_completed_courses = rec1.no_of_mandatory_courses AND
647 IF rec1.path_status_code = 'ACTIVE' AND l_complete_ok = 'S'
648 THEN
649 -- The Plan can be completed
650 l_path_status_code := 'COMPLETED';
651
652 IF rec1.lp_enrollment_id IS NOT NULL THEN
653 if l_lp_enrollment_ids = '' or l_lp_enrollment_ids is null then
654 l_lp_enrollment_ids := rec1.lp_enrollment_id;
655 else
656 l_lp_enrollment_ids := l_lp_enrollment_ids || '^' || rec1.lp_enrollment_id;
657
658 end if;
659 END IF;
660 ELSIF p_no_of_completed_courses < rec1.no_of_mandatory_courses AND rec1.path_status_code = 'COMPLETED' THEN
661 l_path_status_code := 'ACTIVE';
662 END IF;
663
664 OPEN csr_lpe_update(rec1.lp_enrollment_id);
665 FETCH csr_lpe_update into l_object_version_number;
666 IF csr_lpe_update%FOUND then
667 CLOSE csr_lpe_update;
668 IF l_path_status_code = 'COMPLETED' THEN
669 l_completion_date := sysdate;
670 ELSE
671 l_completion_date := null;
672 END IF;
673 ota_lp_enrollment_api.update_lp_enrollment
674 (p_effective_date => sysdate
675 ,p_lp_enrollment_id => rec1.lp_enrollment_id
676 ,p_object_version_number => l_object_version_number
677 ,p_path_status_code => l_path_status_code
678 ,p_no_of_completed_courses => p_no_of_completed_courses
679 ,p_completion_date => l_completion_date);
680
681 ELSE
682 CLOSE csr_lpe_update;
683 END IF;
684 END LOOP;
685 p_lp_enrollment_ids := l_lp_enrollment_ids;
686 END Update_lpe_lme_change;
687
688 -- ----------------------------------------------------------------------------
689 -- |----------------------<create_talent_mgmt_lpm>-----------------------------|
690 -- ----------------------------------------------------------------------------
691 PROCEDURE create_talent_mgmt_lpm
692 (p_validate IN NUMBER DEFAULT hr_api.g_false_num
693 ,p_effective_date IN DATE
694 ,p_business_group_id IN NUMBER
695 ,p_learning_path_id IN NUMBER DEFAULT NULL
696 ,p_lp_enrollment_id IN NUMBER DEFAULT NULL
697 ,p_learning_path_section_id IN NUMBER DEFAULT NULL
698 ,p_path_name IN VARCHAR2 DEFAULT NULL
699 ,p_path_purpose IN VARCHAR2 DEFAULT NULL
700 ,p_path_status_code IN VARCHAR2
701 ,p_path_start_date_active IN DATE DEFAULT NULL
702 ,p_path_end_date_active IN DATE DEFAULT NULL
703 ,p_source_function_code IN VARCHAR2
704 ,p_assignment_id IN NUMBER DEFAULT NULL
705 ,p_source_id IN NUMBER DEFAULT NULL
706 ,p_creator_person_id IN NUMBER
707 ,p_person_id IN NUMBER
708 ,p_display_to_learner_flag IN VARCHAR2
709 ,p_activity_version_id IN NUMBER
710 ,p_course_sequence IN NUMBER
711 ,p_member_status_code IN VARCHAR2 DEFAULT NULL
712 ,p_completion_target_date IN DATE
713 ,p_notify_days_before_target IN NUMBER
714 ,p_object_version_NUMBER OUT NOCOPY NUMBER
715 ,p_return_status OUT NOCOPY VARCHAR2
716 ) IS
717 --
718 -- Variables for API Boolean parameters
719 l_validate boolean;
720 --
721 -- Variables for IN/OUT parameters
722 --
723 -- Other variables
724 l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
725 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
726 l_learning_path_section_id ota_lp_sections.learning_path_section_id%TYPE;
727 l_learning_path_member_id ota_learning_path_members.learning_path_member_id%TYPE;
728 l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
729
730 l_lp_ovn number;
731 l_lpe_ovn number;
732 l_lpc_ovn number;
733 l_lpm_ovn number;
734 l_lme_ovn number;
735
736 l_lp_rtn_status varchar2(30);
737 l_lpe_rtn_status varchar2(30);
738 l_lpc_rtn_status varchar2(30);
739 l_lpm_rtn_status varchar2(30);
740 l_lme_rtn_status varchar2(30);
741 l_member_status_code varchar2(30) := p_member_status_code;
742 l_exists boolean;
743
744 l_proc varchar2(72) := g_package ||'create_talent_mgmt_lpm';
745 l_path_source_code ota_learning_paths.path_source_code%TYPE;
746 l_path_name ota_lp_sections_tl.name%TYPE := p_path_name;
747
748 CURSOR csr_get_lp IS
749 SELECT lps.learning_path_id,
750 lpe.lp_enrollment_id,
751 lpc.learning_path_section_id
752 FROM ota_learning_paths lps,
753 ota_lp_enrollments lpe,
754 ota_lp_sections lpc
755 WHERE lps.learning_path_id = lpe.learning_path_id
756 AND lpc.learning_path_id = lps.learning_path_id
757 AND lps.path_source_code = 'TALENT_MGMT'
758 AND lps.source_function_code = p_source_function_code
759 AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND lps.source_id = p_source_id))
760 AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND lps.assignment_id = p_assignment_id));
761
762 CURSOR csr_get_lpe IS
763 SELECT lp_enrollment_id
764 FROM ota_lp_enrollments
765 WHERE learning_path_id = l_learning_path_id
766 AND person_id = p_person_id;
767
768 CURSOR csr_get_lpc IS
769 SELECT learning_path_section_id
770 FROM ota_lp_sections
771 WHERE learning_path_id = l_learning_path_id
772 AND completion_type_code = 'M';
773
774 CURSOR csr_get_lpm IS
775 SELECT learning_path_member_id
776 FROM ota_learning_path_members
777 WHERE learning_path_section_id = l_learning_path_section_id
778 AND activity_version_id = p_activity_version_id;
779
780 BEGIN
781 hr_utility.set_location(' Entering:' || l_proc,10);
782 --
783 -- Issue a savepoint
784 --
785 SAVEPOINT create_talent_mgmt_lpm;
786
787 --
788 -- Check if the call is from SSHR - Appraisal / Suitability Matching / Succession Planning
789 --
790
791 -- SSHR call should have person Id. Mandatory check for personId.
792 hr_api.mandatory_arg_error
793 (p_api_name => l_proc
794 ,p_argument => 'p_person_id'
795 ,p_argument_value => p_person_id
796 );
797 IF p_learning_path_id IS NULL THEN
798 OPEN csr_get_lp;
799 FETCH csr_get_lp INTO l_learning_path_id,
800 l_lp_enrollment_id,
801 l_learning_path_section_id;
802 l_exists := csr_get_lp%FOUND;
803 CLOSE csr_get_lp;
804 ELSE
805 l_learning_path_id := p_learning_path_id;
806 l_lp_enrollment_id := p_lp_enrollment_id;
807 l_learning_path_section_id := p_learning_path_section_id;
808 END IF;
809
810 IF NOT l_exists THEN
811 ota_learning_path_swi.create_learning_path
812 (p_effective_date => p_effective_date
813 ,p_validate => p_validate
814 ,p_path_name => l_path_name
815 ,p_business_group_id => p_business_group_id
816 ,p_duration => null
817 ,p_duration_units => null
818 ,p_start_date_active => NVL(p_path_start_date_active, trunc(sysdate))
819 ,p_end_date_active => p_path_end_date_active
820 ,p_description => null
821 ,p_objectives => null
822 ,p_keywords => null
823 ,p_purpose => p_path_purpose
824 ,p_path_source_code => 'TALENT_MGMT'
825 ,p_source_function_code => p_source_function_code
826 ,p_assignment_id => p_assignment_id
827 ,p_source_id => p_source_id
828 ,p_notify_days_before_target => null
829 ,p_person_id => p_person_id
830 ,p_display_to_learner_flag => p_display_to_learner_flag
831 ,p_learning_path_id => l_learning_path_id
832 ,p_object_version_number => l_lp_ovn
833 ,p_return_status => l_lp_rtn_status
834 );
835
836 l_learning_path_id := ota_lrng_path_util.get_talent_mgmt_lp
837 (p_person_id => p_person_id
838 ,p_source_function_code => p_source_function_code
839 ,p_source_id => p_source_id
840 ,p_assignment_id => p_assignment_id
841 ,p_business_group_id => p_business_group_id);
842
843
844 -- If Learning Path is not created, rollback and return
845 if (l_lp_rtn_status = 'E') then
846 ROLLBACK TO create_talent_mgmt_lpm;
847 p_object_version_number := NULL;
848 p_return_status := hr_multi_message.get_return_status_disable;
849 return;
850 end if;
851
852 ota_lp_enrollment_swi.create_lp_enrollment
853 (p_effective_date => p_effective_date
854 ,p_validate => p_validate
855 ,p_learning_path_id => l_learning_path_id
856 ,p_person_id => p_person_id
857 ,p_path_status_code => p_path_status_code
858 ,p_enrollment_source_code => 'TALENT_MGMT'
859 ,p_completion_target_date => p_completion_target_date
860 ,p_creator_person_id => p_creator_person_id
861 ,p_business_group_id => p_business_group_id
862 ,p_lp_enrollment_id => l_lp_enrollment_id
863 ,p_object_version_number => l_lpe_ovn
864 ,p_return_status => l_lpe_rtn_status
865 );
866
867 OPEN csr_get_lpe;
868 FETCH csr_get_lpe INTO l_lp_enrollment_id;
869 CLOSE csr_get_lpe;
870
871
872 -- If Learning Path enrollment is not created, rollback and return
873 if (l_lpe_rtn_status = 'E') then
874 ROLLBACK TO create_talent_mgmt_lpm;
875 p_object_version_number := NULL;
876 p_return_status := hr_multi_message.get_return_status_disable;
877 return;
878 end if;
879
880 ota_lp_section_swi.create_lp_section
881 (p_validate => p_validate
882 ,p_effective_date => p_effective_date
883 ,p_business_group_id => p_business_group_id
884 ,p_section_name => l_path_name
885 ,p_learning_path_id => l_learning_path_id
886 ,p_section_sequence => 1
887 ,p_completion_type_code => 'M'
888 ,p_learning_path_section_id => l_learning_path_section_id
889 ,p_object_version_number => l_lpc_ovn
890 ,p_return_status => l_lpc_rtn_status
891 );
892
893 OPEN csr_get_lpc;
894 FETCH csr_get_lpc INTO l_learning_path_section_id;
895 CLOSE csr_get_lpc;
896
897
898 -- If Learning Path section is not created, rollback and return
899 if (l_lpc_rtn_status = 'E') then
900 ROLLBACK TO create_talent_mgmt_lpm;
901 p_object_version_number := NULL;
902 p_return_status := hr_multi_message.get_return_status_disable;
903 return;
904 end if;
905 END IF;
906
907 ota_lp_member_swi.create_learning_path_member
908 (p_validate => p_validate
909 ,p_effective_date => p_effective_date
910 ,p_business_group_id => p_business_group_id
911 ,p_learning_path_id => l_learning_path_id
912 ,p_activity_version_id => p_activity_version_id
913 ,p_course_sequence => p_course_sequence
914 ,p_learning_path_section_id => l_learning_path_section_id
915 ,p_notify_days_before_target => p_notify_days_before_target
916 ,p_learning_path_member_id => l_learning_path_member_id
917 ,p_object_version_number => l_lpm_ovn
918 ,p_return_status => l_lpm_rtn_status
919 );
920
921 OPEN csr_get_lpm;
922 FETCH csr_get_lpm INTO l_learning_path_member_id;
923 CLOSE csr_get_lpm;
924
925
926 -- If Learning Path member is not created, rollback and return
927 if (l_lpm_rtn_status = 'E') then
928 ROLLBACK TO create_talent_mgmt_lpm;
929 p_object_version_number := NULL;
930 p_return_status := hr_multi_message.get_return_status_disable;
931 return;
932 end if;
933
934
935 ota_lp_member_enrollment_swi.create_lp_member_enrollment
936 (p_effective_date => p_effective_date
937 ,p_validate => p_validate
938 ,p_lp_enrollment_id => l_lp_enrollment_id
939 ,p_learning_path_section_id => l_learning_path_section_id
940 ,p_learning_path_member_id => l_learning_path_member_id
941 ,p_member_status_code => p_member_status_code
942 ,p_completion_target_date => p_completion_target_date
943 ,p_business_group_id => p_business_group_id
944 ,p_lp_member_enrollment_id => l_lp_member_enrollment_id
945 ,p_object_version_number => l_lme_ovn
946 ,p_return_status => l_lme_rtn_status
947 );
948
949
950 -- Derive the API return status value based on whether
951 -- messages of any type exist in the Multiple Message List.
952 -- Also disable Multiple Message Detection.
953 --
954 p_return_status := hr_multi_message.get_return_status_disable;
955 hr_utility.set_location(' Leaving:' || l_proc,20);
956 --
957 EXCEPTION
958 WHEN hr_multi_message.error_message_exist THEN
959 --
960 -- Catch the Multiple Message List exception which
961 -- indicates API processing has been aborted because
962 -- at least one message exists in the list.
963 --
964 ROLLBACK TO create_talent_mgmt_lpm;
965 --
966 -- Reset IN OUT parameters and set OUT parameters
967 --
968 p_object_version_NUMBER := NULL;
969 p_return_status := hr_multi_message.get_return_status_disable;
970 hr_utility.set_location(' Leaving:' || l_proc, 30);
971
972 WHEN others THEN
973 --
974 -- When Multiple Message Detection is enabled catch
975 -- any Application specific or other unexpected
976 -- exceptions. Adding appropriate details to the
977 -- Multiple Message List. Otherwise re-raise the
978 -- error.
979 --
980 ROLLBACK TO create_talent_mgmt_lpm;
981 IF hr_multi_message.unexpected_error_add(l_proc) THEN
982 hr_utility.set_location(' Leaving:' || l_proc,40);
983 RAISE;
984 END IF;
985 --
986 -- Reset IN OUT and set OUT parameters
987 --
988 p_object_version_NUMBER := NULL;
989 p_return_status := hr_multi_message.get_return_status_disable;
990 hr_utility.set_location(' Leaving:' || l_proc,50);
991 END create_talent_mgmt_lpm;
992
993 -- ----------------------------------------------------------------------------
994 -- ----------------------------------------------------------------------------
995 -- |----------------------<update_talent_mgmt_lp >-----------------------------|
996 -- ----------------------------------------------------------------------------
997 PROCEDURE update_talent_mgmt_lp
998 (p_validate IN NUMBER DEFAULT hr_api.g_false_num
999 ,p_effective_date IN DATE
1000 ,p_mode IN VARCHAR2
1001 ,p_learning_path_id IN NUMBER DEFAULT NULL
1002 ,p_lp_enrollment_id IN NUMBER DEFAULT NULL
1003 ,p_source_function_code IN VARCHAR2
1004 ,p_assignment_id IN NUMBER DEFAULT NULL
1005 ,p_source_id IN NUMBER DEFAULT NULL
1006 ,p_person_id IN NUMBER
1007 ,p_display_to_learner_flag IN VARCHAR2
1008 ,p_lps_ovn IN OUT NOCOPY NUMBER
1009 ,p_lpe_ovn IN OUT NOCOPY NUMBER
1010 ,p_return_status OUT NOCOPY VARCHAR2
1011 ) IS
1012 --
1013 -- Variables for API Boolean parameters
1014 l_validate boolean;
1015 --
1016 -- Variables for IN/OUT parameters
1017 --
1018 -- Other variables
1019 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
1020 l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
1021
1022 l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
1023 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1024 l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
1025 l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
1026 l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
1027 l_completion_date ota_lp_enrollments.completion_date%TYPE := null;
1028
1029 l_lp_ovn number;
1030 l_lpe_ovn number;
1031 l_lme_ovn number;
1032
1033 l_lp_rtn_status varchar2(30);
1034 l_lpe_rtn_status varchar2(30);
1035 l_lme_rtn_status varchar2(30);
1036
1037 l_proc varchar2(72) := g_package ||'update_talent_mgmt_lp';
1038
1039 CURSOR csr_get_lp IS
1040 SELECT lps.learning_path_id,
1041 lps.object_version_number lps_ovn,
1042 lpe.lp_enrollment_id,
1043 lpe.object_version_number lpe_ovn
1044 FROM ota_learning_paths lps,
1045 ota_lp_enrollments lpe
1046 WHERE lps.learning_path_id = lpe.learning_path_id
1047 AND lps.path_source_code = 'TALENT_MGMT'
1048 AND lps.source_function_code = p_source_function_code
1049 AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND lps.source_id = p_source_id))
1050 AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND lps.assignment_id = p_assignment_id));
1051
1052 CURSOR csr_get_appr_lme IS
1053 SELECT lme.lp_member_enrollment_id,
1054 lme.object_version_number,
1055 lme.learning_path_member_id,
1056 lme.lp_enrollment_id
1057 FROM ota_lp_member_enrollments lme,
1058 ota_learning_path_members lpm
1059 WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1060 AND lpm.learning_path_id = l_learning_path_id
1061 AND lme.member_status_code = 'AWAITING_APPROVAL';
1062
1063 CURSOR csr_get_cncl_lme IS
1064 SELECT lme.lp_member_enrollment_id,
1065 lme.object_version_number
1066 FROM ota_lp_member_enrollments lme,
1067 ota_learning_path_members lpm
1068 WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1069 AND lpm.learning_path_id = l_learning_path_id
1070 AND lme.member_status_code <> 'CANCELLED';
1071
1072 CURSOR csr_get_lpe_dtls(lp_id NUMBER, lpe_id NUMBER) IS
1073 SELECT ota_lrng_path_util.get_no_of_mandatory_courses(lp_id,'TALENT_MGMT') mandatory_courses,
1074 ota_lrng_path_util.get_no_of_completed_courses(lpe_id,'TALENT_MGMT') completed_courses
1075 FROM dual;
1076
1077
1078 BEGIN
1079 hr_utility.set_location(' Entering:' || l_proc,10);
1080 --
1081 -- Issue a savepoint
1082 --
1083 SAVEPOINT update_talent_mgmt_lp;
1084
1085 --
1086 -- Check if the call is from SSHR - Appraisal / Suitability Matching / Succession Planning
1087 --
1088
1089 -- SSHR call should have person Id. Mandatory check for personId.
1090 hr_api.mandatory_arg_error
1091 (p_api_name => l_proc
1092 ,p_argument => 'p_person_id'
1093 ,p_argument_value => p_person_id
1094 );
1095 IF p_learning_path_id IS NULL THEN
1096 OPEN csr_get_lp;
1097 FETCH csr_get_lp INTO l_learning_path_id,
1098 l_lp_ovn,
1099 l_lp_enrollment_id,
1100 l_lpe_ovn;
1101 CLOSE csr_get_lp;
1102 ELSE
1103 l_learning_path_id := p_learning_path_id;
1104 l_lp_enrollment_id := p_lp_enrollment_id;
1105 l_lp_ovn := p_lps_ovn;
1106 l_lpe_ovn := p_lpe_ovn;
1107 END IF;
1108
1109
1110 IF p_mode = 'APPROVED' THEN
1111 l_path_status_code := 'ACTIVE';
1112 ELSE l_path_status_code := 'CANCELLED';
1113 END IF;
1114
1115 ota_learning_path_swi.update_learning_path
1116 (p_effective_date => p_effective_date
1117 ,p_learning_path_id => l_learning_path_id
1118 ,p_object_version_number => l_lp_ovn
1119 ,p_display_to_learner_flag => p_display_to_learner_flag
1120 ,p_validate => p_validate
1121 ,p_return_status => l_lp_rtn_status
1122 );
1123
1124
1125 -- If Learning Path is not updated, rollback and return
1126 if (l_lp_rtn_status = 'E') then
1127 ROLLBACK TO update_talent_mgmt_lp;
1128 p_return_status := hr_multi_message.get_return_status_disable;
1129 return;
1130 end if;
1131
1132 ota_lp_enrollment_swi.update_lp_enrollment
1133 (p_effective_date => p_effective_date
1134 ,p_lp_enrollment_id => l_lp_enrollment_id
1135 ,p_object_version_number => l_lpe_ovn
1136 ,p_path_status_code => l_path_status_code
1137 ,p_return_status => l_lpe_rtn_status
1138 );
1139
1140 -- If Learning Path enrollment is not created, rollback and return
1141 if (l_lpe_rtn_status = 'E') then
1142 ROLLBACK TO update_talent_mgmt_lp;
1143 p_return_status := hr_multi_message.get_return_status_disable;
1144 return;
1145 end if;
1146
1147 IF p_mode = 'APPROVED' THEN
1148 l_member_status_code := 'PLANNED';
1149
1150 FOR appr_rec IN csr_get_appr_lme
1151 LOOP
1152
1153 l_lme_ovn := appr_rec.object_version_number;
1154
1155 ota_lp_member_enrollment_swi.update_lp_member_enrollment
1156 (p_effective_date => p_effective_date
1157 ,p_lp_member_enrollment_id => appr_rec.lp_member_enrollment_id
1158 ,p_lp_enrollment_id => appr_rec.lp_enrollment_id
1159 ,p_learning_path_member_id => appr_rec.learning_path_member_id
1160 ,p_object_version_number => l_lme_ovn
1161 ,p_validate => p_validate
1162 ,p_member_status_code => l_member_status_code
1163 ,p_return_status => l_lme_rtn_status
1164 );
1165
1166 if (l_lme_rtn_status = 'E') then
1167 ROLLBACK TO update_talent_mgmt_lp;
1168 p_return_status := hr_multi_message.get_return_status_disable;
1169 return;
1170 end if;
1171
1172 END LOOP;
1173
1174 OPEN csr_get_lpe_dtls(l_learning_path_id, l_lp_enrollment_id);
1175 FETCH csr_get_lpe_dtls INTO l_no_of_mandatory_courses,
1176 l_no_of_completed_courses;
1177 CLOSE csr_get_lpe_dtls;
1178
1179 IF l_no_of_mandatory_courses = l_no_of_completed_courses THEN
1180 l_path_status_code := 'COMPLETED';
1181 l_completion_date := trunc(sysdate);
1182 END IF;
1183
1184
1185 ota_lp_enrollment_swi.update_lp_enrollment
1186 (p_effective_date => p_effective_date
1187 ,p_lp_enrollment_id => l_lp_enrollment_id
1188 ,p_object_version_number => l_lpe_ovn
1189 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
1190 ,p_no_of_completed_courses => l_no_of_completed_courses
1191 ,p_path_status_code => l_path_status_code
1192 ,p_completion_date => l_completion_date
1193 ,p_return_status => l_lpe_rtn_status
1194 );
1195
1196
1197 -- If Learning Path enrollment is not created, rollback and return
1198 if (l_lpe_rtn_status = 'E') then
1199 ROLLBACK TO update_talent_mgmt_lp;
1200 p_return_status := hr_multi_message.get_return_status_disable;
1201 return;
1202 end if;
1203
1204
1205
1206 ELSE
1207
1208 l_member_status_code := 'CANCELLED';
1209
1210 FOR cncl_rec IN csr_get_cncl_lme
1211 LOOP
1212 l_lme_ovn := cncl_rec.object_version_number;
1213
1214 ota_lp_member_enrollment_swi.update_lp_member_enrollment
1215 (p_effective_date => p_effective_date
1216 ,p_lp_member_enrollment_id => cncl_rec.lp_member_enrollment_id
1217 ,p_object_version_number => l_lme_ovn
1218 ,p_validate => p_validate
1219 ,p_member_status_code => l_member_status_code
1220 ,p_return_status => l_lme_rtn_status
1221 );
1222
1223 if (l_lme_rtn_status = 'E') then
1224 ROLLBACK TO update_talent_mgmt_lp;
1225 p_return_status := hr_multi_message.get_return_status_disable;
1226 return;
1227 end if;
1228
1229 END LOOP;
1230 END IF;
1231
1232 -- Derive the API return status value based on whether
1233 -- messages of any type exist in the Multiple Message List.
1234 -- Also disable Multiple Message Detection.
1235 --
1236 p_return_status := hr_multi_message.get_return_status_disable;
1237 hr_utility.set_location(' Leaving:' || l_proc,20);
1238 --
1239 EXCEPTION
1240 WHEN hr_multi_message.error_message_exist THEN
1241 --
1242 -- Catch the Multiple Message List exception which
1243 -- indicates API processing has been aborted because
1244 -- at least one message exists in the list.
1245 --
1246 ROLLBACK TO update_talent_mgmt_lp;
1247 --
1248 -- Reset IN OUT parameters and set OUT parameters
1249 --
1250 p_return_status := hr_multi_message.get_return_status_disable;
1251 hr_utility.set_location(' Leaving:' || l_proc, 30);
1252
1253 WHEN others THEN
1254 --
1255 -- When Multiple Message Detection is enabled catch
1256 -- any Application specific or other unexpected
1257 -- exceptions. Adding appropriate details to the
1258 -- Multiple Message List. Otherwise re-raise the
1259 -- error.
1260 --
1261 ROLLBACK TO update_talent_mgmt_lp;
1262 IF hr_multi_message.unexpected_error_add(l_proc) THEN
1263 hr_utility.set_location(' Leaving:' || l_proc,40);
1264 RAISE;
1265 END IF;
1266 --
1267 -- Reset IN OUT and set OUT parameters
1268 --
1269 p_return_status := hr_multi_message.get_return_status_disable;
1270 hr_utility.set_location(' Leaving:' || l_proc,50);
1271 END update_talent_mgmt_lp;
1272 -- ----------------------------------------------------------------------------
1273 -- |-------------------< chk_no_of_mandatory_courses >-------------------------|
1274 -- ----------------------------------------------------------------------------
1275 --
1276 PROCEDURE chk_no_of_mandatory_courses
1277 (p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE
1278 , p_return_status OUT NOCOPY VARCHAR2)
1279 IS
1280 --
1281 l_proc VARCHAR2(72) := g_package|| 'chk_no_of_mandatory_courses';
1282 l_exists VARCHAR2(1);
1283 l_lpm_count NUMBER;
1284 l_lpc_id ota_lp_sections.learning_path_section_id%TYPE;
1285 l_mandatory_courses ota_lp_sections.no_of_mandatory_courses%TYPE;
1286 --
1287 CURSOR get_section_info IS
1288 SELECT lpm.learning_path_section_id,
1289 lpc.no_of_mandatory_courses
1290 FROM ota_learning_path_members lpm,
1291 ota_lp_sections lpc
1292 WHERE lpm.learning_path_section_id = lpc.learning_path_section_id
1293 AND lpc.completion_type_code = 'S'
1294 AND lpm.learning_path_member_id = p_learning_path_member_id;
1295
1296 CURSOR get_lpm_count IS
1297 SELECT count(learning_path_member_id)
1298 FROM ota_learning_path_members
1299 WHERE learning_path_section_id = l_lpc_id;
1300
1301 BEGIN
1302 --
1303
1304
1305 hr_utility.set_location(' Step:'|| l_proc, 30);
1306
1307
1308 p_return_status := 'S';
1309 OPEN get_section_info;
1310 FETCH get_section_info INTO l_lpc_id, l_mandatory_courses;
1311 IF get_section_info%FOUND THEN
1312 OPEN get_lpm_count;
1313 FETCH get_lpm_count INTO l_lpm_count;
1314 CLOSE get_lpm_count;
1315 IF l_lpm_count <= l_mandatory_courses THEN
1316 p_return_status := 'E';
1317 -- fnd_message.set_name('OTA', 'OTA_13076_LPC_MNDTRY_ACT_ERR');
1318 -- fnd_message.raise_error;
1319 END IF;
1320 END IF;
1321 CLOSE get_section_info;
1322
1323 hr_utility.set_location(' Leaving:'||l_proc, 90);
1324 END chk_no_of_mandatory_courses;
1325
1326 END ota_lrng_path_member_util;
1327