[Home] [Help]
PACKAGE BODY: APPS.OTA_LRNG_PATH_UTIL
Source
1 PACKAGE BODY OTA_LRNG_PATH_UTIL as
2 /* $Header: otlpswrs.pkb 120.6.12020000.7 2013/03/21 11:36:30 atadepal ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := 'ota_lrng_path_util.'; -- Global package name
9
10 -- ---------------------------------------------------------------------------
11 -- |----------------------< chk_complete_path_ok >--------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 Function chk_complete_path_ok(p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type)
15 return varchar2
16 IS
17 /*
18 CURSOR is_path_completed IS
19 SELECT 1
20 FROM ota_lp_enrollments lpe
21 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id
22 AND lpe.no_of_mandatory_courses = lpe.no_of_completed_courses;
23
24 CURSOR one_child_completed IS
25 SELECT lme.lp_member_enrollment_id
26 FROM ota_lp_member_enrollments lme,
27 ota_lp_enrollments lpe
28 WHERE lme.member_status_code = 'COMPLETED'
29 AND lpe.lp_enrollment_id = lme.lp_enrollment_id
30 AND lpe.lp_enrollment_id = p_lp_enrollment_id
31 AND rownum = 1;
32 */
33 -- Added for Bug#4052408
34 CURSOR csr_get_path_details IS
35 SELECT lps.path_source_code, lps.source_function_code, lpe.path_status_code
36 FROM ota_learning_paths lps,
37 ota_lp_enrollments lpe
38 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id
39 AND lpe.learning_path_id = lps.learning_path_id;
40 -- End of code added for bug#4052408
41
42 -- bug 7028384
43 CURSOR get_lp_section_details IS
44 SELECT lps.completion_type_code, lps.learning_path_section_id, lps.no_of_mandatory_courses
45 FROM ota_lp_sections lps, ota_lp_enrollments lpe
46 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id AND
47 lpe.learning_path_id = lps.learning_path_id;
48
49 CURSOR get_lp_member_status(p_lp_section_id ota_lp_sections.learning_path_section_id%TYPE) IS
50 SELECT member_status_code FROM ota_lp_member_enrollments
51 WHERE learning_path_section_id = p_lp_section_id AND
52 lp_enrollment_id = p_lp_enrollment_id;
53 -- bug 7028384
54 l_proc VARCHAR2(72) := g_package ||'chk_complete_path_ok';
55 l_exists Number(9);
56 l_complete Number(9);
57 l_result varchar2(3) :='F';
58 l_path_source_code ota_learning_paths.path_source_code%TYPE;
59 l_source_function_code ota_learning_paths.source_function_code%TYPE;
60 l_path_status ota_lp_enrollments.path_status_code%TYPE;
61 l_completed_course_count number := 0;
62
63 Begin
64
65 hr_utility.set_location(' Entering:' || l_proc,10);
66
67 -- Added for Bug#4052408
68 -- Return F for Talent Management learning paths whose status is AWAITING_APPROVAL
69 OPEN csr_get_path_details;
70 FETCH csr_get_path_details into l_path_source_code, l_source_function_code, l_path_status;
71 CLOSE csr_get_path_details;
72
73 IF l_path_source_code = 'TALENT_MGMT'
74 AND l_source_function_code = 'APPRAISAL'
75 AND l_path_status = 'AWAITING_APPROVAL' THEN
76 return l_result;
77 END IF;
78 -- End of code added for Bug#4052408
79
80 -- bug 7028384
81 FOR lp_sec_details IN get_lp_section_details LOOP
82 l_completed_course_count := 0;
83 l_result := 'F';
84 IF(lp_sec_details.completion_type_code = 'O')THEN --All optional
85 l_result := 'S';
86 ELSE
87 -- bug 13877229 added to skip empty mandatory sections
88 l_result := 'S';
89 FOR lp_mbr_status IN get_lp_member_status(lp_sec_details.learning_path_section_id) LOOP
90 -- bug 13877229
91 l_result := 'F';
92 IF(lp_sec_details.completion_type_code = 'M') THEN --All Mandatory
93 IF( lp_mbr_status.member_status_code <> 'COMPLETED') THEN
94 return 'F';
95 ELSE
96 l_result := 'S';
97 END IF;
98 ELSE
99 IF(lp_sec_details.completion_type_code = 'S')THEN --One or More Mandatory
100 IF(lp_mbr_status.member_status_code = 'COMPLETED') THEN
101 l_completed_course_count := l_completed_course_count+1;
102 END IF;
103 IF(l_completed_course_count >= lp_sec_details.no_of_mandatory_courses) THEN
104 l_result := 'S';
105 END IF;
106 END IF;
107 END IF;
108 END LOOP;
109 IF(l_result = 'F') THEN
110 return l_result;
111 END IF;
112 END IF;
113 END LOOP;
114
115 -- bug 7028384
116 /*
117 open is_path_completed;
118 fetch is_path_completed into l_exists;
119 if is_path_completed%FOUND then
120 open one_child_completed;
121 fetch one_child_completed into l_complete;
122 if one_child_completed%found then
123 l_result :='S';
124 end if;
125 close one_child_completed;
126 end if;
127 close is_path_completed;
128 */
129 return l_result;
130
131
132 end chk_complete_path_ok;
133
134 -- ---------------------------------------------------------------------------
135 -- |----------------------< chk_login_person >--------------------------|
136 -- ---------------------------------------------------------------------------
137 --
138 FUNCTION chk_login_person(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)
139 RETURN VARCHAR2
140 IS
141
142 l_person_id ota_learning_paths.person_id%TYPE;
143 l_login_person ota_learning_paths.person_id%TYPE;
144 l_login_customer NUMBER;
145 l_contact_id ota_learning_paths.contact_id%TYPE;
146 l_proc VARCHAR2(72) := g_package|| 'chk_login_person';
147
148 BEGIN
149
150 hr_utility.set_location(' Step:'|| l_proc, 10);
151
152 SELECT lpe.person_id,
153 lpe.contact_id
154 INTO l_person_id,
155 l_contact_id
156 FROM ota_lp_enrollments lpe
157 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id;
158
159 SELECT employee_id,
160 customer_id
161 INTO l_login_person,
162 l_login_customer
163 FROM fnd_user
164 WHERE user_id = fnd_profile.value('USER_ID');
165
166 hr_utility.set_location(' Step:'|| l_proc, 20);
167 IF l_login_person IS NOT NULL THEN
168 IF l_login_person = l_person_id THEN
169 RETURN 'E';
170 ELSE
171 RETURN 'M';
172 END IF;
173 ELSIF l_login_customer IS NOT NULL THEN
174 RETURN 'E';
175 END IF;
176
177
178
179 hr_utility.set_location(' Step:'|| l_proc, 30);
180 END chk_login_person;
181
182 -- ----------------------------------------------------------------------------
183 -- |---------------------------< get_person_id >----------------------------|
184 -- ----------------------------------------------------------------------------
185
186 FUNCTION get_person_id(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)
187 RETURN number
188 IS
189
190 CURSOR csr_person_id IS
191 SELECT person_id
192 FROM ota_lp_enrollments
193 WHERE lp_enrollment_id = p_lp_enrollment_id;
194
195 l_person_id number(9) := 0;
196
197 BEGIN
198
199 OPEN csr_person_id;
200 FETCH csr_person_id INTO l_person_id;
201 CLOSE csr_person_id;
202
203 IF l_person_id is null then
204 l_person_id := 0;
205 END IF;
206
207 RETURN l_person_id;
208
209 END get_person_id;
210
211 -- ----------------------------------------------------------------------------
212 -- |---------------------------< complete_path >-------------------------|
213 -- ----------------------------------------------------------------------------
214 Procedure complete_path(p_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE)
215 is
216
217 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
218 IS
219 SELECT lpe.lp_enrollment_id,
220 lpe.object_version_number
221 FROM ota_lp_enrollments lpe
222 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
223
224 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
225 l_object_version_number ota_lp_enrollments.object_version_number%type;
226 l_path_status_code ota_lp_enrollments.path_status_code%type;
227
228 BEGIN
229 l_path_status_code := 'COMPLETED';
230
231 OPEN csr_lpe_update(p_lp_enrollment_id);
232 FETCH csr_lpe_update into l_lp_enrollment_id,l_object_version_number;
233 IF csr_lpe_update%FOUND then
234 CLOSE csr_lpe_update;
235 ota_lp_enrollment_api.update_lp_enrollment
236 (p_effective_date => sysdate
237 ,p_lp_enrollment_id => p_lp_enrollment_id
238 ,p_object_version_number => l_object_version_number
239 ,p_path_status_code => l_path_status_code
240 ,p_completion_date => ota_lrng_path_member_util.get_lp_completion_date(p_lp_enrollment_id));
241 ELSE
242 CLOSE csr_lpe_update;
243 END IF;
244 END complete_path;
245 -- ----------------------------------------------------------------------------
246 -- |----------------------<get_no_of_mandatory_courses> -----------------------|
247 -- ----------------------------------------------------------------------------
248
249 FUNCTION get_no_of_mandatory_courses(p_learning_path_id IN ota_learning_paths.learning_path_id%TYPE,
250 p_path_source_code IN ota_learning_paths.path_source_code%TYPE)
251 RETURN number IS
252
253 CURSOR csr_s_lpm IS
254 SELECT sum(no_of_mandatory_courses)
255 FROM ota_lp_sections
256 WHERE learning_path_id = p_learning_path_id
257 AND completion_type_code = 'S';
258
259 CURSOR csr_m_lpm IS
260 SELECT count(lpm.learning_path_member_id)
261 FROM ota_lp_sections lpc,
262 ota_learning_path_members lpm
263 WHERE lpc.learning_path_id = p_learning_path_id
264 and lpm.learning_path_section_id = lpc.learning_path_section_id
265 AND completion_type_code = 'M';
266
267 l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
268 l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
269 l_return ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;
270
271 BEGIN
272 OPEN csr_m_lpm;
273 FETCH csr_m_lpm INTO l_m_lpm;
274 CLOSE csr_m_lpm;
275
276 IF l_m_lpm IS NULL THEN
277 l_m_lpm := 0;
278 END IF;
279
280 IF p_path_source_code = 'CATALOG' THEN
281
282 OPEN csr_s_lpm;
283 FETCH csr_s_lpm INTO l_s_lpm;
284 CLOSE csr_s_lpm;
285 IF l_s_lpm IS NULL THEN
286 l_s_lpm := 0;
287 END IF;
288 l_return := l_s_lpm + l_m_lpm;
289 ELSE
290 l_return := l_m_lpm;
291
292 END IF;
293
294
295 RETURN l_return;
296
297 END get_no_of_mandatory_courses;
298 -- ----------------------------------------------------------------------------
299 -- |----------------------<get_no_of_completed_courses> -----------------------|
300 -- ----------------------------------------------------------------------------
301 -- {Start Of Comments}
302 --
303 -- Description:
304 -- Returns the number of completed courses for a learning path
305 --
306 -- Prerequisites:
307 --
308 --
309 -- In Parameters:
310 --
311 -- p_lp_enrollment_id
312 -- p_path_source_code
313 --
314 -- Post Success:
315 -- Returns the number of completed courses for an lp enrollment
316 --
317 -- Post Failure:
318 -- If an error has occurred, an error message will be raised.
319 --
320 -- Developer Implementation Notes:
321 -- None
322 --
323 -- Access Status:
324 -- Internal Row Handler Use Only.
325 --
326 -- {End Of Comments}
327 -- ----------------------------------------------------------------------------
328
329 FUNCTION get_no_of_completed_courses(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
330 p_path_source_code IN ota_learning_paths.path_source_code%TYPE)
331 RETURN number IS
332
333
334 CURSOR csr_sections IS
335 SELECT lpc.no_of_mandatory_courses,
336 lpc.learning_path_section_id
337 FROM ota_lp_enrollments lme,
338 ota_lp_sections lpc
339 WHERE lpc.learning_path_id = lme.learning_path_id
340 AND lpc.completion_type_code = 'S'
341 AND lme.lp_enrollment_id = p_lp_enrollment_id;
342
343 CURSOR csr_s_lpm(l_learning_path_section_id NUMBER) IS
344 SELECT count(lp_member_enrollment_id)
345 FROM ota_lp_member_enrollments lme
346 WHERE lme.learning_path_section_id = l_learning_path_section_id
347 AND lme.member_status_code = 'COMPLETED'
348 AND lme.lp_enrollment_id = p_lp_enrollment_id;
349
350 CURSOR csr_m_lpm IS
351 SELECT count(lp_member_enrollment_id)
352 FROM ota_lp_member_enrollments lme,
353 ota_lp_sections lpc
354 WHERE lpc.learning_path_section_id = lme.learning_path_section_id
355 AND lpc.completion_type_code = 'M'
356 AND lme.member_status_code = 'COMPLETED'
357 AND lme.lp_enrollment_id = p_lp_enrollment_id;
358
359 l_no_of_mandatory_courses ota_lp_sections.no_of_mandatory_courses%TYPE;
360
361 l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
362 l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
363 l_completed_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;
364 l_return ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;
365
366 BEGIN
367
368 OPEN csr_m_lpm;
369 FETCH csr_m_lpm INTO l_m_lpm;
370 CLOSE csr_m_lpm;
371
372 IF l_m_lpm IS NULL THEN
373 l_m_lpm := 0;
374 END IF;
375
376 IF p_path_source_code = 'CATALOG' THEN
377 FOR rec_sections IN csr_sections
378 LOOP
379 l_no_of_mandatory_courses := rec_sections.no_of_mandatory_courses;
380
381 IF l_no_of_mandatory_courses IS NULL THEN
382 l_no_of_mandatory_courses := 0;
383 END IF;
384
385 OPEN csr_s_lpm(rec_sections.learning_path_section_id);
386 FETCH csr_s_lpm INTO l_s_lpm;
387 CLOSE csr_s_lpm;
388
389 IF l_s_lpm IS NULL THEN
390 l_s_lpm := 0;
391 END IF;
392
393 IF l_s_lpm <= l_no_of_mandatory_courses THEN
394 l_completed_courses := l_completed_courses + l_s_lpm;
395 ELSE
396 l_completed_courses := l_completed_courses + l_no_of_mandatory_courses;
397 END IF;
398
399
400 END LOOP;
401 l_return := l_completed_courses + l_m_lpm;
402
403 ELSE
404 l_return := l_m_lpm;
405
406 END IF;
407
408
409 RETURN l_return;
410
411 END get_no_of_completed_courses;
412
413 -- ---------------------------------------------------------------------------
414 -- |----------------------< Update_lpe_lpm_changes >--------------------------|
415 -- ---------------------------------------------------------------------------
416 -- {Start Of Comments}
417 --
418 -- Description:
419 -- Updates no_of_completed_courses and no_of_mandatory_courses for the
420 -- p_lp_enrollment_id passed, and marks the path completed if it meets the
421 -- completion criteria.
422 --
423 -- Prerequisites:
424 --
425 --
426 -- In Arguments:
427 -- p_lp_enrollment_id
428 -- p_completion_target_date new completion_target_date
429 --
430 -- Post Success:
431 -- 'S' is returned for successful update of no_of_completed_courses and
432 -- no_of_mandatory_courses
433 -- 'C' is returned for successful completion of path.
434 -- 'F' is returned for no update.
435 -- Post Failure:
436 --
437 --
438 -- Access Status:
439 -- Internal Development Use Only.
440 --
441 -- {End Of Comments}
442 -- ---------------------------------------------------------------------------
443
444 PROCEDURE Update_lpe_lpm_changes( p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
445 --Modified for Bug#3891087
446 --p_path_source_code IN ota_learning_paths.path_source_code%TYPE,
447 p_completion_target_date IN ota_lp_enrollments.completion_target_date%TYPE,
448 p_return_status OUT NOCOPY VARCHAR2)
449 is
450
451 CURSOR one_child_completed IS
452 SELECT lme.lp_member_enrollment_id
453 FROM ota_lp_member_enrollments lme,
454 ota_lp_enrollments lpe
455 WHERE lme.member_status_code = 'COMPLETED'
456 AND lpe.lp_enrollment_id = lme.lp_enrollment_id
457 AND lpe.lp_enrollment_id = p_lp_enrollment_id
458 AND rownum = 1;
459
460 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
461 IS
462 SELECT lpe.lp_enrollment_id,
463 lpe.learning_path_id,
464 lpe.completion_target_date,
465 lpe.object_version_number
466 FROM ota_lp_enrollments lpe
467 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
468
469 CURSOR csr_get_path_source_code
470 IS
471 -- Modified for Bug#4052408
472 --SELECT lps.path_source_code
473 SELECT lps.path_source_code, lps.source_function_code, lpe.path_status_code
474 FROM ota_learning_paths lps, ota_lp_enrollments lpe
475 WHERE lps.learning_path_id = lpe.learning_path_id
476 AND lpe.lp_enrollment_id = p_lp_enrollment_id;
477
478 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
479 l_learning_path_id ota_learning_paths.learning_path_id%TYPE;
480 l_object_version_number ota_lp_enrollments.object_version_number%type;
481 l_path_status_code ota_lp_enrollments.path_status_code%type;
482 l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%type;
483 l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%type;
484 l_completion_target_date ota_lp_enrollments.completion_target_date%TYPE;
485 l_result varchar2(3) :='F';
486 l_complete Number(9);
487 l_return_status varchar2(3) :='';
488 l_path_source_code ota_learning_paths.path_source_code%TYPE;
489 l_source_function_code ota_learning_paths.source_function_code%TYPE;
490
491
492 BEGIN
493 OPEN csr_get_path_source_code;
494 FETCH csr_get_path_source_code INTO l_path_source_code, l_source_function_code, l_path_status_code;
495 CLOSE csr_get_path_source_code;
496
497 OPEN csr_lpe_update(p_lp_enrollment_id);
498 FETCH csr_lpe_update into l_lp_enrollment_id,l_learning_path_id, l_completion_target_date, l_object_version_number;
499 CLOSE csr_lpe_update;
500
501 l_no_of_completed_courses := get_no_of_completed_courses(p_lp_enrollment_id,
502 l_path_source_code);
503 l_no_of_mandatory_courses := get_no_of_mandatory_courses(l_learning_path_id,
504 l_path_source_code);
505
506 IF (p_completion_target_date IS NOT NULL AND l_completion_target_date IS NOT NULL) THEN
507 IF (p_completion_target_date > l_completion_target_date) THEN
508 l_completion_target_date := p_completion_target_date;
509 END IF;
510 ELSIF p_completion_target_date IS NOT NULL THEN
511 l_completion_target_date := p_completion_target_date;
512 END IF;
513
514 -- Added for Bug#4052408
515 IF l_path_source_code = 'TALENT_MGMT'
516 AND l_source_function_code = 'APPRAISAL'
517 AND l_path_status_code = 'AWAITING_APPROVAL' THEN
518 l_result := 'F';
519 ELSE
520
521 if l_no_of_completed_courses = l_no_of_mandatory_courses then
522 open one_child_completed;
523 fetch one_child_completed into l_complete;
524 if one_child_completed%found then
525 l_result :='S';
526 end if;
527 close one_child_completed;
528 end if;
529 END IF;
530
531 IF l_result = 'S' THEN
532 l_path_status_code := 'COMPLETED';
533 ota_lp_enrollment_api.update_lp_enrollment
534 (p_effective_date => sysdate
535 ,p_lp_enrollment_id => p_lp_enrollment_id
536 ,p_object_version_number => l_object_version_number
537 ,p_no_of_completed_courses => l_no_of_completed_courses
538 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
539 ,p_completion_target_date => l_completion_target_date
540 ,p_path_status_code => l_path_status_code
541 ,p_completion_date => ota_lrng_path_member_util.get_lp_completion_date(p_lp_enrollment_id));
542 --set the return flag as completed
543 l_return_status := 'C';
544 ELSE
545 ota_lp_enrollment_api.update_lp_enrollment
546 (p_effective_date => sysdate
547 ,p_lp_enrollment_id => p_lp_enrollment_id
548 ,p_object_version_number => l_object_version_number
549 ,p_no_of_completed_courses => l_no_of_completed_courses
550 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
551 ,p_completion_target_date => l_completion_target_date);
552 --set the return flag as successful
553 l_return_status := 'S';
554 END IF;
555
556 p_return_status := l_return_status;
557
558 END Update_lpe_lpm_changes;
559
560
561 -- ---------------------------------------------------------------------------
562 -- |----------------------< get_lpe_crse_compl_status_msg >--------------------------|
563 -- ---------------------------------------------------------------------------
564 -- {Start Of Comments}
565 --
566 -- Description:
567 -- Retrieves tokenized message for displaying Learning Path course completion
568 -- status.
569 --
570 -- Prerequisites:
571 --
572 --
573 -- In Arguments:
574 -- no_of_completed_courses
575 -- no_of_mandatory_courses
576 --
577 -- Post Success:
578 -- Return of form of tokenized "no_of_completed_courses of no_of_mandatory_courses
579 -- courses completed."
580 --
581 -- Post Failure:
582 --
583 -- Access Status:
584 -- Internal Development Use Only.
585 --
586 -- {End Of Comments}
587 -- ---------------------------------------------------------------------------
588
589 FUNCTION get_lpe_crse_compl_status_msg(no_of_completed_courses IN number,
590 no_of_mandatory_courses IN number)
591 RETURN varchar2 IS
592
593 l_return_msg varchar2(200);
594
595 BEGIN
596
597 fnd_message.set_name('OTA', 'OTA_13081_LPE_CRS_CMPL_STATUS');
598 fnd_message.set_token('NO_OF_COMPLETED_COURSES', no_of_completed_courses);
599 fnd_message.set_token('NO_OF_MANDATORY_COURSES', no_of_mandatory_courses);
600 l_return_msg := fnd_message.get;
601
602 RETURN l_return_msg;
603
604 END get_lpe_crse_compl_status_msg;
605
606
607 -- ---------------------------------------------------------------------------
608 -- |----------------------< get_talent_mgmt_lp >--------------------------|
609 -- ---------------------------------------------------------------------------
610 --
611
612 FUNCTION get_talent_mgmt_lp(p_person_id IN ota_lp_enrollments.person_id%TYPE
613 ,p_source_function_code IN ota_learning_paths.source_function_code%TYPE
614 ,p_source_id IN ota_learning_paths.source_id%TYPE
615 ,p_assignment_id IN ota_learning_paths.assignment_id%TYPE
616 ,p_business_group_id IN NUMBER)
617 RETURN number
618 IS
619
620 CURSOR csr_get_lp IS
621 SELECT learning_path_id
622 FROM ota_learning_paths
623 WHERE source_function_code = p_source_function_code
624 AND business_group_id = p_business_group_id
625 AND person_id = p_person_id
626 AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND source_id = p_source_id))
627 AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND assignment_id = p_assignment_id));
628
629 l_proc VARCHAR2(72) := g_package|| 'get_talent_mgmt_lp';
630
631 l_learning_path_id ota_learning_paths.learning_path_id%TYPE := 0;
632
633 BEGIN
634
635 hr_utility.set_location(' Step:'|| l_proc, 10);
636
637 OPEN csr_get_lp ;
638 FETCH csr_get_lp INTO l_learning_path_id;
639 CLOSE csr_get_lp;
640 RETURN l_learning_path_id;
641
642
643 END get_talent_mgmt_lp;
644
645 -- ----------------------------------------------------------------------------
646 -- |----------------------<get_no_of_mand_compl_courses> ----------------------|
647 -- ----------------------------------------------------------------------------
648 -- {Start Of Comments}
649 --
650 -- Description:
651 -- Returns the number of mandatory completed courses for a learning path section
652 --
653 -- Prerequisites:
654 --
655 --
656 -- In Parameters:
657 --
658 -- p_learning_path_section_id
659 -- p_person_id
660 -- p_contact_id
661 --
662 -- Post Success:
663 -- Returns the number of mand completed courses for a learning path section
664 --
665 -- Post Failure:
666 -- If an error has occurred, an error message will be raised.
667 --
668 -- Developer Implementation Notes:
669 -- None
670 --
671 -- Access Status:
672 -- Internal Row Handler Use Only.
673 --
674 -- {End Of Comments}
675 -- ----------------------------------------------------------------------------
676
677 FUNCTION get_no_of_mand_compl_courses(p_learning_path_section_id IN ota_lp_sections.learning_path_section_id%TYPE,
678 p_person_id IN ota_learning_paths.person_id%TYPE,
679 p_contact_id IN ota_learning_paths.contact_id%TYPE)
680 RETURN number
681 IS
682 CURSOR csr_lps IS
683 SELECT learning_path_id,
684 no_of_mandatory_courses,
685 completion_type_code
686 FROM ota_lp_sections
687 WHERE learning_path_section_id = p_learning_path_section_id;
688
689 CURSOR csr_m_lpm IS
690 SELECT count(learning_path_member_id)
691 FROM ota_learning_path_members lpm,
692 ota_lp_sections lpc
693 WHERE lpc.learning_path_section_id = p_learning_path_section_id
694 AND lpm.learning_path_section_id = lpc.learning_path_section_id
695 AND lpc.completion_type_code = 'M';
696
697 CURSOR csr_mand_crs_cmpl_count(l_learning_path_id NUMBER) IS
698 select count(*)
699 from ota_lp_member_enrollments lme,
700 ota_lp_enrollments lpe
701 where
702 lpe.learning_path_id = l_learning_path_id
703 and lme.learning_path_section_id = p_learning_path_section_id
704 and (lpe.person_id = p_person_id or lpe.contact_id = p_contact_id)
705 and lpe.PATH_STATUS_CODE in ('ACTIVE', 'COMPLETED')
706 and lme.member_status_code like 'COMPLETED'
707 and lme.LP_ENROLLMENT_ID = lpe.LP_ENROLLMENT_ID;
708
709 l_learning_path_id ota_lp_sections.learning_path_id%TYPE;
710 l_no_of_mandatory_courses ota_lp_sections.no_of_mandatory_courses%TYPE;
711 l_completion_type_code ota_lp_sections.completion_type_code%TYPE;
712 l_mand_crse_compl_count NUMBER:= 0;
713 l_mand_crse_count NUMBER:= 0;
714
715 BEGIN
716
717 OPEN csr_lps;
718 FETCH csr_lps into l_learning_path_id, l_no_of_mandatory_courses, l_completion_type_code;
719 CLOSE csr_lps;
720
721 IF l_completion_type_code = 'M' THEN
722
723 OPEN csr_mand_crs_cmpl_count(l_learning_path_id);
724 FETCH csr_mand_crs_cmpl_count into l_mand_crse_compl_count;
725 CLOSE csr_mand_crs_cmpl_count;
726
727 OPEN csr_m_lpm;
728 FETCH csr_m_lpm into l_mand_crse_count;
729 CLOSE csr_m_lpm;
730
731 IF l_mand_crse_compl_count > l_mand_crse_count THEN
732 l_mand_crse_compl_count := l_mand_crse_count;
733 END IF;
734
735 ELSIF l_completion_type_code = 'S' THEN
736
737 OPEN csr_mand_crs_cmpl_count(l_learning_path_id);
738 FETCH csr_mand_crs_cmpl_count into l_mand_crse_compl_count;
739 CLOSE csr_mand_crs_cmpl_count;
740
741 IF l_mand_crse_compl_count > l_no_of_mandatory_courses THEN
742 l_mand_crse_compl_count := l_no_of_mandatory_courses;
743 END IF;
744
745 ELSIF l_completion_type_code = 'S' THEN
746 l_mand_crse_compl_count := 0;
747 END IF;
748
749 RETURN l_mand_crse_compl_count;
750
751 END get_no_of_mand_compl_courses;
752
753 Function is_path_successful(p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type)
754 return varchar2
755 IS
756 l_learning_path_id number;
757 l_path_status varchar2(30);
758 l_person_id number;
759 l_contact_id number;
760 l_exists number;
761
762 CURSOR csr_get_lpe_info IS
763 select lpe.learning_path_id,lpe.path_status_code, lpe.person_id, lpe.contact_id
764 FROM ota_lp_enrollments lpe
765 where lpe.lp_enrollment_id = p_lp_enrollment_id;
766
767 CURSOR csr_chk_person IS
768 select
769 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
770 max(lpc.no_of_mandatory_courses) no_of_courses
771 from ota_lp_sections lpc
772 , ota_learning_path_members lpm
773 , ota_events evt
774 , ota_delegate_bookings tdb
775 , ota_lp_enrollments lpe
776 , ota_booking_status_types bst
777 where lpc.learning_path_id = lpe.learning_path_id
778 and lpm.learning_path_section_id = lpc.learning_path_section_id
779 and lpm.activity_version_id = evt.activity_version_id
780 and tdb.event_id = evt.event_id
781 and lpc.completion_type_code = 'S'
782 and tdb.delegate_person_id = lpe.person_id
783 and lpe.lp_enrollment_id = p_lp_enrollment_id
784 and lpe.path_status_code = 'COMPLETED'
785 and tdb.booking_status_type_id = bst.booking_status_type_id
786 and bst.type = 'A'
787 group by lpc.learning_path_section_id
788
789 UNION ALL
790
791 select
792 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
793 sum(1) no_of_courses
794 from ota_lp_sections lpc
795 , ota_learning_path_members lpm
796 , ota_events evt
797 , ota_delegate_bookings tdb
798 , ota_lp_enrollments lpe
799 , ota_booking_status_types bst
800 where lpc.learning_path_id = lpe.learning_path_id
801 and lpm.learning_path_section_id = lpc.learning_path_section_id
802 and lpm.activity_version_id = evt.activity_version_id
803 and tdb.event_id = evt.event_id
804 and lpc.completion_type_code = 'M'
805 and tdb.delegate_person_id = lpe.person_id
806 and lpe.lp_enrollment_id = p_lp_enrollment_id
807 and lpe.path_status_code = 'COMPLETED'
808 and tdb.booking_status_type_id = bst.booking_status_type_id
809 and bst.type = 'A'
810 group by lpc.learning_path_section_id;
811
812 CURSOR csr_chk_contact IS
813 select
814 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
815 max(lpc.no_of_mandatory_courses) no_of_courses
816 from ota_lp_sections lpc
817 , ota_learning_path_members lpm
818 , ota_events evt
819 , ota_delegate_bookings tdb
820 , ota_lp_enrollments lpe
821 , ota_booking_status_types bst
822 where lpc.learning_path_id = lpe.learning_path_id
823 and lpm.learning_path_section_id = lpc.learning_path_section_id
824 and lpm.activity_version_id = evt.activity_version_id
825 and tdb.event_id = evt.event_id
826 and lpc.completion_type_code = 'S'
827 and tdb.delegate_contact_id = lpe.contact_id
828 and lpe.lp_enrollment_id = p_lp_enrollment_id
829 and lpe.path_status_code = 'COMPLETED'
830 and tdb.booking_status_type_id = bst.booking_status_type_id
831 and bst.type = 'A'
832 group by lpc.learning_path_section_id
833
834 UNION ALL
835
836 select
837 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
838 sum(1) no_of_courses
839 from ota_lp_sections lpc
840 , ota_learning_path_members lpm
841 , ota_events evt
842 , ota_delegate_bookings tdb
843 , ota_lp_enrollments lpe
844 , ota_booking_status_types bst
845 where lpc.learning_path_id = lpe.learning_path_id
846 and lpm.learning_path_section_id = lpc.learning_path_section_id
847 and lpm.activity_version_id = evt.activity_version_id
848 and tdb.event_id = evt.event_id
849 and lpc.completion_type_code = 'M'
850 and tdb.delegate_contact_id = lpe.contact_id
851 and lpe.lp_enrollment_id = p_lp_enrollment_id
852 and lpe.path_status_code = 'COMPLETED'
853 and tdb.booking_status_type_id = bst.booking_status_type_id
854 and bst.type = 'A'
855 group by lpc.learning_path_section_id;
856
857 l_lpc_rec csr_chk_person%ROWTYPE;
858 l_lpc_rec2 csr_chk_contact%ROWTYPE;
859
860 BEGIN
861 OPEN csr_get_lpe_info;
862 FETCH csr_get_lpe_info INTO l_learning_path_id, l_path_status, l_person_id, l_contact_id;
863 CLOSE csr_get_lpe_info;
864
865 IF l_path_status <> 'COMPLETED' THEN
866 return 'N';
867 END IF;
868
869 IF l_person_id IS NOT NULL THEN
870 FOR l_lpc_rec IN csr_chk_person LOOP
871 IF l_lpc_rec.completed_courses < l_lpc_rec.no_of_courses THEN
872 return 'N';
873 END IF;
874 END LOOP;
875
876 ELSIF l_contact_id IS NOT NULL THEN
877 FOR l_lpc_rec2 IN csr_chk_contact LOOP
878 IF l_lpc_rec2.completed_courses < l_lpc_rec.no_of_courses THEN
879 return 'N';
880 END IF;
881 END LOOP;
882 END IF;
883 return 'Y';
884 END is_path_successful;
885
886 Procedure Start_comp_proc_success_attnd(p_person_id in number ,
887 p_event_id in ota_Events.event_id%type)
888 is
889
890 cursor get_lp_enroll is
891 select lp_enrollment_id, lpe.learning_path_id
892 From ota_learning_path_members lpm
893 , ota_events evt
894 , ota_lp_enrollments lpe
895 where evt.activity_version_id = lpm.activity_version_id
896 and lpm.learning_path_id = lpe.learning_path_id
897 and evt.event_id = p_event_id
898 and lpe.person_id = p_person_id;
899
900 l_sucessful varchar(2) := 'N';
901
902 l_item_key wf_items.item_key%type;
903
904 begin
905
906 For rec in get_lp_enroll
907 loop
908
909 l_sucessful := is_path_successful(rec.lp_enrollment_id);
910
911 if l_sucessful = 'Y' then
912
913 ota_competence_ss.create_wf_process(p_process => 'OTA_COMPETENCE_UPDATE_JSP_PRC',
914 p_itemtype => 'HRSSA',
915 p_person_id => p_person_id,
916 p_eventid => null,
917 p_learningpath_ids => to_char(rec.learning_path_id),
918 p_itemkey => l_item_key);
919
920 end if;
921 end loop;
922
923 end Start_comp_proc_success_attnd;
924
925 -- Added this function for Bug# 7430475
926 FUNCTION get_no_of_mand_compl_courses(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)
927 RETURN number is
928
929 cursor get_lp_mand_completed is
930 select sum(OTA_LRNG_PATH_UTIL.get_no_of_mand_compl_courses(ols.learning_path_section_id, ole.person_id, ole.contact_id))
931 from ota_lp_enrollments ole, ota_lp_sections ols
932 where ole.learning_path_id = ols.learning_path_id
933 and ole.lp_enrollment_id = p_lp_enrollment_id;
934
935 l_mand_courses_completed number := 0;
936
937 begin
938 open get_lp_mand_completed;
939 fetch get_lp_mand_completed into l_mand_courses_completed;
940 close get_lp_mand_completed;
941
942 return l_mand_courses_completed;
943
944 end get_no_of_mand_compl_courses;
945
946 function get_lp_current_status(p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type)
947 return varchar2 is
948
949 CURSOR lp_members IS
950 SELECT lpe.lp_enrollment_id,
951 lpe.person_id,
952 lpe.contact_id,
953 lpe.learning_path_id,
954 lpe.path_status_code,
955 lpm.learning_path_member_id,
956 lpm.activity_version_id,
957 lpm.business_group_id,
958 lpm.learning_path_section_id,
959 lpme.lp_member_enrollment_id
960 FROM ota_lp_enrollments lpe,
961 ota_learning_path_members lpm,
962 ota_lp_member_enrollments lpme
963 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id
964 AND lpe.learning_path_id = lpm.learning_path_id
965 --AND lpe.path_status_code <> 'CANCELLED'
966 AND lpe.lp_enrollment_id = lpme.lp_enrollment_id
967 AND lpm.learning_path_member_id = lpme.learning_path_member_id ;
968
969 CURSOR enrolled_class(p_person_id IN ota_learning_paths.person_id%TYPE,
970 p_contact_id IN ota_learning_paths.contact_id%TYPE,
971 p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE) IS
972 SELECT oav.activity_version_id,
973 evt.event_id,
974 evt.course_start_date,
975 evt.course_start_time,
976 evt.course_end_date,
977 evt.course_end_time,
978 tdb.date_status_changed,
979 tdb.booking_status_type_id,
980 tdb.delegate_person_id,
981 tdb.delegate_contact_id
982 FROM ota_activity_versions oav,
983 ota_events evt,
984 ota_delegate_bookings tdb,
985 ota_booking_status_types bst
986 WHERE oav.activity_version_id = p_activity_version_id
987 AND oav.activity_version_id = evt.activity_version_id
988 AND evt.event_id = tdb.event_id
989 AND bst.booking_status_type_id = tdb.booking_status_type_id
990 AND bst.type = 'P'
991 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
992 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id));
993
994 l_path_status_code VARCHAR2(30);
995 l_enroll_type VARCHAR2(30);
996 l_date_status_changed DATE;
997 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%type;
998
999 begin
1000 if(ota_lrng_path_util.is_path_successful(p_lp_enrollment_id => p_lp_enrollment_id) = 'Y') then
1001 return ota_utility.get_lookup_meaning('OTA_LP_CURRENT_STATUS', 'COMPLETED', 810);
1002 else
1003 for lp_mbr_rec in lp_members loop
1004 if(lp_mbr_rec.path_status_code = 'CANCELLED') then
1005 return ota_utility.get_lookup_meaning('OTA_LP_CURRENT_STATUS', 'CANCELLED', 810);
1006 end if;
1007 OTA_LRNG_PATH_MEMBER_UTIL.get_enrollment_status(p_person_id => lp_mbr_rec.person_id,
1008 p_contact_id => lp_mbr_rec.contact_id,
1009 p_activity_version_id => lp_mbr_rec.activity_version_id,
1010 p_lp_member_enrollment_id => lp_mbr_rec.lp_member_enrollment_id,
1011 p_booking_status_type => l_enroll_type,
1012 p_date_status_changed => l_date_status_changed,
1013 p_successful_attendance_flag =>l_successful_attendance_flag);
1014 if(l_enroll_type = 'P') then
1015 for lp_cls_rec in enrolled_class(lp_mbr_rec.person_id, lp_mbr_rec.contact_id, lp_mbr_rec.activity_version_id)
1016 loop
1017 if(trunc(sysdate) <= trunc(nvl(lp_cls_rec.course_end_date, sysdate+1))) then
1018 l_path_status_code := 'ONPLAN';
1019 else
1020 l_path_status_code := 'NOTONPLAN';
1021 end if;
1022 end loop;
1023 else
1024 l_path_status_code := 'SUBSCRIBED';
1025 end if;
1026 end loop;
1027 end if;
1028 return ota_utility.get_lookup_meaning('OTA_LP_CURRENT_STATUS', l_path_status_code, 810);
1029 end get_lp_current_status;
1030
1031 function get_lp_enroll_id(p_event_id IN ota_events.event_id%type,
1032 p_person_id IN ota_lp_enrollments.person_id%type,
1033 p_contact_id ota_lp_enrollments.contact_id%type)
1034 return ota_lp_enrollments.lp_enrollment_id%type IS
1035
1036 CURSOR csr_lp_enrl_id_person IS
1037 SELECT lpe.lp_enrollment_id
1038 FROM ota_lp_enrollments lpe,
1039 ota_events oev,
1040 ota_learning_path_members lpm
1041 WHERE lpe.person_id = p_person_id
1042 AND oev.event_id = p_event_id
1043 AND NVL(lpe.is_history_flag, 'N') = 'N'
1044 AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
1045 AND lpe.business_group_id = ota_general.get_business_group_id
1046 AND oev.activity_version_id = lpm.activity_version_id
1047 AND lpe.learning_path_id = lpm.learning_path_id;
1048
1049 CURSOR csr_lp_enrl_id_contact IS
1050 SELECT lpe.lp_enrollment_id
1051 FROM ota_lp_enrollments lpe,
1052 ota_events oev,
1053 ota_learning_path_members lpm
1054 WHERE lpe.contact_id = p_contact_id
1055 AND oev.event_id = p_event_id
1056 AND NVL(lpe.is_history_flag, 'N') = 'N'
1057 AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
1058 AND lpe.business_group_id = ota_general.get_business_group_id
1059 AND oev.activity_version_id = lpm.activity_version_id
1060 AND lpe.learning_path_id = lpm.learning_path_id;
1061
1062 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%type;
1063
1064 BEGIN
1065
1066 if p_person_id is not null then
1067 OPEN csr_lp_enrl_id_person;
1068 FETCH csr_lp_enrl_id_person into l_lp_enrollment_id;
1069 CLOSE csr_lp_enrl_id_person;
1070
1071 else
1072 OPEN csr_lp_enrl_id_contact;
1073 FETCH csr_lp_enrl_id_contact into l_lp_enrollment_id;
1074 CLOSE csr_lp_enrl_id_contact;
1075 end if;
1076
1077 return l_lp_enrollment_id;
1078
1079 END get_lp_enroll_id;
1080 --
1081 END ota_lrng_path_util;
1082