[Home] [Help]
PACKAGE BODY: APPS.OTA_LRNG_PATH_UTIL
Source
1 PACKAGE BODY OTA_LRNG_PATH_UTIL as
2 /* $Header: otlpswrs.pkb 120.0.12010000.2 2008/08/05 11:44:53 ubhat 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 FOR lp_mbr_status IN get_lp_member_status(lp_sec_details.learning_path_section_id) LOOP
88 IF(lp_sec_details.completion_type_code = 'M') THEN --All Mandatory
89 IF(lp_mbr_status.member_status_code <> 'COMPLETED') THEN
90 return 'F';
91 ELSE
92 l_result := 'S';
93 END IF;
94 ELSE
95 IF(lp_sec_details.completion_type_code = 'S')THEN --One or More Mandatory
96 IF(lp_mbr_status.member_status_code = 'COMPLETED') THEN
97 l_completed_course_count := l_completed_course_count+1;
98 IF(l_completed_course_count >= lp_sec_details.no_of_mandatory_courses) THEN
99 l_result := 'S';
100 END IF;
101 END IF;
102 END IF;
103 END IF;
104 END LOOP;
105 IF(l_result = 'F') THEN
106 return l_result;
107 END IF;
108 END IF;
109 END LOOP;
110
111 -- bug 7028384
112 /*
113 open is_path_completed;
114 fetch is_path_completed into l_exists;
115 if is_path_completed%FOUND then
116 open one_child_completed;
117 fetch one_child_completed into l_complete;
118 if one_child_completed%found then
119 l_result :='S';
120 end if;
121 close one_child_completed;
122 end if;
123 close is_path_completed;
124 */
125 return l_result;
126
127
128 end chk_complete_path_ok;
129
130 -- ---------------------------------------------------------------------------
131 -- |----------------------< chk_login_person >--------------------------|
132 -- ---------------------------------------------------------------------------
133 --
134 FUNCTION chk_login_person(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)
135 RETURN VARCHAR2
136 IS
137
138 l_person_id ota_learning_paths.person_id%TYPE;
139 l_login_person ota_learning_paths.person_id%TYPE;
140 l_login_customer NUMBER;
141 l_contact_id ota_learning_paths.contact_id%TYPE;
142 l_proc VARCHAR2(72) := g_package|| 'chk_login_person';
143
144 BEGIN
145
146 hr_utility.set_location(' Step:'|| l_proc, 10);
147
148 SELECT lpe.person_id,
149 lpe.contact_id
150 INTO l_person_id,
151 l_contact_id
152 FROM ota_lp_enrollments lpe
153 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id;
154
155 SELECT employee_id,
156 customer_id
157 INTO l_login_person,
158 l_login_customer
159 FROM fnd_user
160 WHERE user_id = fnd_profile.value('USER_ID');
161
162 hr_utility.set_location(' Step:'|| l_proc, 20);
163 IF l_login_person IS NOT NULL THEN
164 IF l_login_person = l_person_id THEN
165 RETURN 'E';
166 ELSE
167 RETURN 'M';
168 END IF;
169 ELSIF l_login_customer IS NOT NULL THEN
170 RETURN 'E';
171 END IF;
172
173
174
175 hr_utility.set_location(' Step:'|| l_proc, 30);
176 END chk_login_person;
177
178 -- ----------------------------------------------------------------------------
179 -- |---------------------------< get_person_id >----------------------------|
180 -- ----------------------------------------------------------------------------
181
182 FUNCTION get_person_id(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)
183 RETURN number
184 IS
185
186 CURSOR csr_person_id IS
187 SELECT person_id
188 FROM ota_lp_enrollments
189 WHERE lp_enrollment_id = p_lp_enrollment_id;
190
191 l_person_id number(9) := 0;
192
193 BEGIN
194
195 OPEN csr_person_id;
196 FETCH csr_person_id INTO l_person_id;
197 CLOSE csr_person_id;
198
199 IF l_person_id is null then
200 l_person_id := 0;
201 END IF;
202
203 RETURN l_person_id;
204
205 END get_person_id;
206
207 -- ----------------------------------------------------------------------------
208 -- |---------------------------< complete_path >-------------------------|
209 -- ----------------------------------------------------------------------------
210 Procedure complete_path(p_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE)
211 is
212
213 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
214 IS
215 SELECT lpe.lp_enrollment_id,
216 lpe.object_version_number
217 FROM ota_lp_enrollments lpe
218 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
219
220 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
221 l_object_version_number ota_lp_enrollments.object_version_number%type;
222 l_path_status_code ota_lp_enrollments.path_status_code%type;
223
224 BEGIN
225 l_path_status_code := 'COMPLETED';
226
227 OPEN csr_lpe_update(p_lp_enrollment_id);
228 FETCH csr_lpe_update into l_lp_enrollment_id,l_object_version_number;
229 IF csr_lpe_update%FOUND then
230 CLOSE csr_lpe_update;
231 ota_lp_enrollment_api.update_lp_enrollment
232 (p_effective_date => sysdate
233 ,p_lp_enrollment_id => p_lp_enrollment_id
234 ,p_object_version_number => l_object_version_number
235 ,p_path_status_code => l_path_status_code
236 ,p_completion_date => sysdate);
237 ELSE
238 CLOSE csr_lpe_update;
239 END IF;
240 END complete_path;
241 -- ----------------------------------------------------------------------------
242 -- |----------------------<get_no_of_mandatory_courses> -----------------------|
243 -- ----------------------------------------------------------------------------
244
245 FUNCTION get_no_of_mandatory_courses(p_learning_path_id IN ota_learning_paths.learning_path_id%TYPE,
246 p_path_source_code IN ota_learning_paths.path_source_code%TYPE)
247 RETURN number IS
248
249 CURSOR csr_s_lpm IS
250 SELECT sum(no_of_mandatory_courses)
251 FROM ota_lp_sections
252 WHERE learning_path_id = p_learning_path_id
253 AND completion_type_code = 'S';
254
255 CURSOR csr_m_lpm IS
256 SELECT count(lpm.learning_path_member_id)
257 FROM ota_lp_sections lpc,
258 ota_learning_path_members lpm
259 WHERE lpc.learning_path_id = p_learning_path_id
260 and lpm.learning_path_section_id = lpc.learning_path_section_id
261 AND completion_type_code = 'M';
262
263 l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
264 l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
265 l_return ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;
266
267 BEGIN
268 OPEN csr_m_lpm;
269 FETCH csr_m_lpm INTO l_m_lpm;
270 CLOSE csr_m_lpm;
271
272 IF l_m_lpm IS NULL THEN
273 l_m_lpm := 0;
274 END IF;
275
276 IF p_path_source_code = 'CATALOG' THEN
277
278 OPEN csr_s_lpm;
279 FETCH csr_s_lpm INTO l_s_lpm;
280 CLOSE csr_s_lpm;
281 IF l_s_lpm IS NULL THEN
282 l_s_lpm := 0;
283 END IF;
284 l_return := l_s_lpm + l_m_lpm;
285 ELSE
286 l_return := l_m_lpm;
287
288 END IF;
289
290
291 RETURN l_return;
292
293 END get_no_of_mandatory_courses;
294 -- ----------------------------------------------------------------------------
295 -- |----------------------<get_no_of_completed_courses> -----------------------|
296 -- ----------------------------------------------------------------------------
297 -- {Start Of Comments}
298 --
299 -- Description:
300 -- Returns the number of completed courses for a learning path
301 --
302 -- Prerequisites:
303 --
304 --
305 -- In Parameters:
306 --
307 -- p_lp_enrollment_id
308 -- p_path_source_code
309 --
310 -- Post Success:
311 -- Returns the number of completed courses for an lp enrollment
312 --
313 -- Post Failure:
314 -- If an error has occurred, an error message will be raised.
315 --
316 -- Developer Implementation Notes:
317 -- None
318 --
319 -- Access Status:
320 -- Internal Row Handler Use Only.
321 --
322 -- {End Of Comments}
323 -- ----------------------------------------------------------------------------
324
325 FUNCTION get_no_of_completed_courses(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
326 p_path_source_code IN ota_learning_paths.path_source_code%TYPE)
327 RETURN number IS
328
329
330 CURSOR csr_sections IS
331 SELECT lpc.no_of_mandatory_courses,
332 lpc.learning_path_section_id
333 FROM ota_lp_enrollments lme,
334 ota_lp_sections lpc
335 WHERE lpc.learning_path_id = lme.learning_path_id
336 AND lpc.completion_type_code = 'S'
337 AND lme.lp_enrollment_id = p_lp_enrollment_id;
338
339 CURSOR csr_s_lpm(l_learning_path_section_id NUMBER) IS
340 SELECT count(lp_member_enrollment_id)
341 FROM ota_lp_member_enrollments lme
342 WHERE lme.learning_path_section_id = l_learning_path_section_id
343 AND lme.member_status_code = 'COMPLETED'
344 AND lme.lp_enrollment_id = p_lp_enrollment_id;
345
346 CURSOR csr_m_lpm IS
347 SELECT count(lp_member_enrollment_id)
348 FROM ota_lp_member_enrollments lme,
349 ota_lp_sections lpc
350 WHERE lpc.learning_path_section_id = lme.learning_path_section_id
351 AND lpc.completion_type_code = 'M'
352 AND lme.member_status_code = 'COMPLETED'
353 AND lme.lp_enrollment_id = p_lp_enrollment_id;
354
355 l_no_of_mandatory_courses ota_lp_sections.no_of_mandatory_courses%TYPE;
356
357 l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
358 l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;
359 l_completed_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;
360 l_return ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;
361
362 BEGIN
363
364 OPEN csr_m_lpm;
365 FETCH csr_m_lpm INTO l_m_lpm;
366 CLOSE csr_m_lpm;
367
368 IF l_m_lpm IS NULL THEN
369 l_m_lpm := 0;
370 END IF;
371
372 IF p_path_source_code = 'CATALOG' THEN
373 FOR rec_sections IN csr_sections
374 LOOP
375 l_no_of_mandatory_courses := rec_sections.no_of_mandatory_courses;
376
377 IF l_no_of_mandatory_courses IS NULL THEN
378 l_no_of_mandatory_courses := 0;
379 END IF;
380
381 OPEN csr_s_lpm(rec_sections.learning_path_section_id);
382 FETCH csr_s_lpm INTO l_s_lpm;
383 CLOSE csr_s_lpm;
384
385 IF l_s_lpm IS NULL THEN
386 l_s_lpm := 0;
387 END IF;
388
389 IF l_s_lpm <= l_no_of_mandatory_courses THEN
390 l_completed_courses := l_completed_courses + l_s_lpm;
391 ELSE
392 l_completed_courses := l_completed_courses + l_no_of_mandatory_courses;
393 END IF;
394
395
396 END LOOP;
397 l_return := l_completed_courses + l_m_lpm;
398
399 ELSE
400 l_return := l_m_lpm;
401
402 END IF;
403
404
405 RETURN l_return;
406
407 END get_no_of_completed_courses;
408
409 -- ---------------------------------------------------------------------------
410 -- |----------------------< Update_lpe_lpm_changes >--------------------------|
411 -- ---------------------------------------------------------------------------
412 -- {Start Of Comments}
413 --
414 -- Description:
415 -- Updates no_of_completed_courses and no_of_mandatory_courses for the
416 -- p_lp_enrollment_id passed, and marks the path completed if it meets the
417 -- completion criteria.
418 --
419 -- Prerequisites:
420 --
421 --
422 -- In Arguments:
423 -- p_lp_enrollment_id
424 -- p_completion_target_date new completion_target_date
425 --
426 -- Post Success:
427 -- 'S' is returned for successful update of no_of_completed_courses and
428 -- no_of_mandatory_courses
429 -- 'C' is returned for successful completion of path.
430 -- 'F' is returned for no update.
431 -- Post Failure:
432 --
433 --
434 -- Access Status:
435 -- Internal Development Use Only.
436 --
437 -- {End Of Comments}
438 -- ---------------------------------------------------------------------------
439
440 PROCEDURE Update_lpe_lpm_changes( p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
441 --Modified for Bug#3891087
442 --p_path_source_code IN ota_learning_paths.path_source_code%TYPE,
443 p_completion_target_date IN ota_lp_enrollments.completion_target_date%TYPE,
444 p_return_status OUT NOCOPY VARCHAR2)
445 is
446
447 CURSOR one_child_completed IS
448 SELECT lme.lp_member_enrollment_id
449 FROM ota_lp_member_enrollments lme,
450 ota_lp_enrollments lpe
451 WHERE lme.member_status_code = 'COMPLETED'
452 AND lpe.lp_enrollment_id = lme.lp_enrollment_id
453 AND lpe.lp_enrollment_id = p_lp_enrollment_id
454 AND rownum = 1;
455
456 CURSOR csr_lpe_update(csr_lp_enrollment_id number)
457 IS
458 SELECT lpe.lp_enrollment_id,
459 lpe.learning_path_id,
460 lpe.completion_target_date,
461 lpe.object_version_number
462 FROM ota_lp_enrollments lpe
463 WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
464
465 CURSOR csr_get_path_source_code
466 IS
467 -- Modified for Bug#4052408
468 --SELECT lps.path_source_code
469 SELECT lps.path_source_code, lps.source_function_code, lpe.path_status_code
470 FROM ota_learning_paths lps, ota_lp_enrollments lpe
471 WHERE lps.learning_path_id = lpe.learning_path_id
472 AND lpe.lp_enrollment_id = p_lp_enrollment_id;
473
474 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
475 l_learning_path_id ota_learning_paths.learning_path_id%TYPE;
476 l_object_version_number ota_lp_enrollments.object_version_number%type;
477 l_path_status_code ota_lp_enrollments.path_status_code%type;
478 l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%type;
479 l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%type;
480 l_completion_target_date ota_lp_enrollments.completion_target_date%TYPE;
481 l_result varchar2(3) :='F';
482 l_complete Number(9);
483 l_return_status varchar2(3) :='';
484 l_path_source_code ota_learning_paths.path_source_code%TYPE;
485 l_source_function_code ota_learning_paths.source_function_code%TYPE;
486
487
488 BEGIN
489 OPEN csr_get_path_source_code;
490 FETCH csr_get_path_source_code INTO l_path_source_code, l_source_function_code, l_path_status_code;
491 CLOSE csr_get_path_source_code;
492
493 OPEN csr_lpe_update(p_lp_enrollment_id);
494 FETCH csr_lpe_update into l_lp_enrollment_id,l_learning_path_id, l_completion_target_date, l_object_version_number;
495 CLOSE csr_lpe_update;
496
497 l_no_of_completed_courses := get_no_of_completed_courses(p_lp_enrollment_id,
498 l_path_source_code);
499 l_no_of_mandatory_courses := get_no_of_mandatory_courses(l_learning_path_id,
500 l_path_source_code);
501
502 IF (p_completion_target_date IS NOT NULL AND l_completion_target_date IS NOT NULL) THEN
503 IF (p_completion_target_date > l_completion_target_date) THEN
504 l_completion_target_date := p_completion_target_date;
505 END IF;
506 ELSIF p_completion_target_date IS NOT NULL THEN
507 l_completion_target_date := p_completion_target_date;
508 END IF;
509
510 -- Added for Bug#4052408
511 IF l_path_source_code = 'TALENT_MGMT'
512 AND l_source_function_code = 'APPRAISAL'
513 AND l_path_status_code = 'AWAITING_APPROVAL' THEN
514 l_result := 'F';
515 ELSE
516
517 if l_no_of_completed_courses = l_no_of_mandatory_courses then
518 open one_child_completed;
519 fetch one_child_completed into l_complete;
520 if one_child_completed%found then
521 l_result :='S';
522 end if;
523 close one_child_completed;
524 end if;
525 END IF;
526
527 IF l_result = 'S' THEN
528 l_path_status_code := 'COMPLETED';
529 ota_lp_enrollment_api.update_lp_enrollment
530 (p_effective_date => sysdate
531 ,p_lp_enrollment_id => p_lp_enrollment_id
532 ,p_object_version_number => l_object_version_number
533 ,p_no_of_completed_courses => l_no_of_completed_courses
534 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
535 ,p_completion_target_date => l_completion_target_date
536 ,p_path_status_code => l_path_status_code
537 ,p_completion_date => trunc(sysdate));
538 --set the return flag as completed
539 l_return_status := 'C';
540 ELSE
541 ota_lp_enrollment_api.update_lp_enrollment
542 (p_effective_date => sysdate
543 ,p_lp_enrollment_id => p_lp_enrollment_id
544 ,p_object_version_number => l_object_version_number
545 ,p_no_of_completed_courses => l_no_of_completed_courses
546 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
547 ,p_completion_target_date => l_completion_target_date);
548 --set the return flag as successful
549 l_return_status := 'S';
550 END IF;
551
552 p_return_status := l_return_status;
553
554 END Update_lpe_lpm_changes;
555
556
557 -- ---------------------------------------------------------------------------
558 -- |----------------------< get_lpe_crse_compl_status_msg >--------------------------|
559 -- ---------------------------------------------------------------------------
560 -- {Start Of Comments}
561 --
562 -- Description:
563 -- Retrieves tokenized message for displaying Learning Path course completion
564 -- status.
565 --
566 -- Prerequisites:
567 --
568 --
569 -- In Arguments:
570 -- no_of_completed_courses
571 -- no_of_mandatory_courses
572 --
573 -- Post Success:
574 -- Return of form of tokenized "no_of_completed_courses of no_of_mandatory_courses
575 -- courses completed."
576 --
577 -- Post Failure:
578 --
579 -- Access Status:
580 -- Internal Development Use Only.
581 --
582 -- {End Of Comments}
583 -- ---------------------------------------------------------------------------
584
585 FUNCTION get_lpe_crse_compl_status_msg(no_of_completed_courses IN number,
586 no_of_mandatory_courses IN number)
587 RETURN varchar2 IS
588
589 l_return_msg varchar2(200);
590
591 BEGIN
592
593 fnd_message.set_name('OTA', 'OTA_13081_LPE_CRS_CMPL_STATUS');
594 fnd_message.set_token('NO_OF_COMPLETED_COURSES', no_of_completed_courses);
595 fnd_message.set_token('NO_OF_MANDATORY_COURSES', no_of_mandatory_courses);
596 l_return_msg := fnd_message.get;
597
598 RETURN l_return_msg;
599
600 END get_lpe_crse_compl_status_msg;
601
602
603 -- ---------------------------------------------------------------------------
604 -- |----------------------< get_talent_mgmt_lp >--------------------------|
605 -- ---------------------------------------------------------------------------
606 --
607
608 FUNCTION get_talent_mgmt_lp(p_person_id IN ota_lp_enrollments.person_id%TYPE
609 ,p_source_function_code IN ota_learning_paths.source_function_code%TYPE
610 ,p_source_id IN ota_learning_paths.source_id%TYPE
611 ,p_assignment_id IN ota_learning_paths.assignment_id%TYPE
612 ,p_business_group_id IN NUMBER)
613 RETURN number
614 IS
615
616 CURSOR csr_get_lp IS
617 SELECT learning_path_id
618 FROM ota_learning_paths
619 WHERE source_function_code = p_source_function_code
620 AND business_group_id = p_business_group_id
621 AND person_id = p_person_id
622 AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND source_id = p_source_id))
623 AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND assignment_id = p_assignment_id));
624
625 l_proc VARCHAR2(72) := g_package|| 'get_talent_mgmt_lp';
626
627 l_learning_path_id ota_learning_paths.learning_path_id%TYPE := 0;
628
629 BEGIN
630
631 hr_utility.set_location(' Step:'|| l_proc, 10);
632
633 OPEN csr_get_lp ;
634 FETCH csr_get_lp INTO l_learning_path_id;
635 CLOSE csr_get_lp;
636 RETURN l_learning_path_id;
637
638
639 END get_talent_mgmt_lp;
640
641 -- ----------------------------------------------------------------------------
642 -- |----------------------<get_no_of_mand_compl_courses> ----------------------|
643 -- ----------------------------------------------------------------------------
644 -- {Start Of Comments}
645 --
646 -- Description:
647 -- Returns the number of mandatory completed courses for a learning path section
648 --
649 -- Prerequisites:
650 --
651 --
652 -- In Parameters:
653 --
654 -- p_learning_path_section_id
655 -- p_person_id
656 -- p_contact_id
657 --
658 -- Post Success:
659 -- Returns the number of mand completed courses for a learning path section
660 --
661 -- Post Failure:
662 -- If an error has occurred, an error message will be raised.
663 --
664 -- Developer Implementation Notes:
665 -- None
666 --
667 -- Access Status:
668 -- Internal Row Handler Use Only.
669 --
670 -- {End Of Comments}
671 -- ----------------------------------------------------------------------------
672
673 FUNCTION get_no_of_mand_compl_courses(p_learning_path_section_id IN ota_lp_sections.learning_path_section_id%TYPE,
674 p_person_id IN ota_learning_paths.person_id%TYPE,
675 p_contact_id IN ota_learning_paths.contact_id%TYPE)
676 RETURN number
677 IS
678 CURSOR csr_lps IS
679 SELECT learning_path_id,
680 no_of_mandatory_courses,
681 completion_type_code
682 FROM ota_lp_sections
683 WHERE learning_path_section_id = p_learning_path_section_id;
684
685 CURSOR csr_m_lpm IS
686 SELECT count(learning_path_member_id)
687 FROM ota_learning_path_members lpm,
688 ota_lp_sections lpc
689 WHERE lpc.learning_path_section_id = p_learning_path_section_id
690 AND lpm.learning_path_section_id = lpc.learning_path_section_id
691 AND lpc.completion_type_code = 'M';
692
693 CURSOR csr_mand_crs_cmpl_count(l_learning_path_id NUMBER) IS
694 select count(*)
695 from ota_lp_member_enrollments lme,
696 ota_lp_enrollments lpe
697 where
698 lpe.learning_path_id = l_learning_path_id
699 and lme.learning_path_section_id = p_learning_path_section_id
700 and (lpe.person_id = p_person_id or lpe.contact_id = p_contact_id)
701 and lpe.PATH_STATUS_CODE in ('ACTIVE', 'COMPLETED')
702 and lme.member_status_code like 'COMPLETED'
703 and lme.LP_ENROLLMENT_ID = lpe.LP_ENROLLMENT_ID;
704
705 l_learning_path_id ota_lp_sections.learning_path_id%TYPE;
706 l_no_of_mandatory_courses ota_lp_sections.no_of_mandatory_courses%TYPE;
707 l_completion_type_code ota_lp_sections.completion_type_code%TYPE;
708 l_mand_crse_compl_count NUMBER:= 0;
709 l_mand_crse_count NUMBER:= 0;
710
711 BEGIN
712
713 OPEN csr_lps;
714 FETCH csr_lps into l_learning_path_id, l_no_of_mandatory_courses, l_completion_type_code;
715 CLOSE csr_lps;
716
717 IF l_completion_type_code = 'M' THEN
718
719 OPEN csr_mand_crs_cmpl_count(l_learning_path_id);
720 FETCH csr_mand_crs_cmpl_count into l_mand_crse_compl_count;
721 CLOSE csr_mand_crs_cmpl_count;
722
723 OPEN csr_m_lpm;
724 FETCH csr_m_lpm into l_mand_crse_count;
725 CLOSE csr_m_lpm;
726
727 IF l_mand_crse_compl_count > l_mand_crse_count THEN
728 l_mand_crse_compl_count := l_mand_crse_count;
729 END IF;
730
731 ELSIF l_completion_type_code = 'S' THEN
732
733 OPEN csr_mand_crs_cmpl_count(l_learning_path_id);
734 FETCH csr_mand_crs_cmpl_count into l_mand_crse_compl_count;
735 CLOSE csr_mand_crs_cmpl_count;
736
737 IF l_mand_crse_compl_count > l_no_of_mandatory_courses THEN
738 l_mand_crse_compl_count := l_no_of_mandatory_courses;
739 END IF;
740
741 ELSIF l_completion_type_code = 'S' THEN
742 l_mand_crse_compl_count := 0;
743 END IF;
744
745 RETURN l_mand_crse_compl_count;
746
747 END get_no_of_mand_compl_courses;
748
749 Function is_path_successful(p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type)
750 return varchar2
751 IS
752 l_learning_path_id number;
753 l_path_status varchar2(30);
754 l_person_id number;
755 l_contact_id number;
756 l_exists number;
757
758 CURSOR csr_get_lpe_info IS
759 select lpe.learning_path_id,lpe.path_status_code, lpe.person_id, lpe.contact_id
760 FROM ota_lp_enrollments lpe
761 where lpe.lp_enrollment_id = p_lp_enrollment_id;
762
763 CURSOR csr_chk_person IS
764 select
765 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
766 max(lpc.no_of_mandatory_courses) no_of_courses
767 from ota_lp_sections lpc
768 , ota_learning_path_members lpm
769 , ota_events evt
770 , ota_delegate_bookings tdb
771 , ota_lp_enrollments lpe
772 , ota_booking_status_types bst
773 where lpc.learning_path_id = lpe.learning_path_id
774 and lpm.learning_path_section_id = lpc.learning_path_section_id
775 and lpm.activity_version_id = evt.activity_version_id
776 and tdb.event_id = evt.event_id
777 and lpc.completion_type_code = 'S'
778 and tdb.delegate_person_id = lpe.person_id
779 and lpe.lp_enrollment_id = p_lp_enrollment_id
780 and lpe.path_status_code = 'COMPLETED'
781 and tdb.booking_status_type_id = bst.booking_status_type_id
782 and bst.type = 'A'
783 group by lpc.learning_path_section_id
784
785 UNION ALL
786
787 select
788 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
789 sum(1) no_of_courses
790 from ota_lp_sections lpc
791 , ota_learning_path_members lpm
792 , ota_events evt
793 , ota_delegate_bookings tdb
794 , ota_lp_enrollments lpe
795 , ota_booking_status_types bst
796 where lpc.learning_path_id = lpe.learning_path_id
797 and lpm.learning_path_section_id = lpc.learning_path_section_id
798 and lpm.activity_version_id = evt.activity_version_id
799 and tdb.event_id = evt.event_id
800 and lpc.completion_type_code = 'M'
801 and tdb.delegate_person_id = lpe.person_id
802 and lpe.lp_enrollment_id = p_lp_enrollment_id
803 and lpe.path_status_code = 'COMPLETED'
804 and tdb.booking_status_type_id = bst.booking_status_type_id
805 and bst.type = 'A'
806 group by lpc.learning_path_section_id;
807
808 CURSOR csr_chk_contact IS
809 select
810 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
811 max(lpc.no_of_mandatory_courses) no_of_courses
812 from ota_lp_sections lpc
813 , ota_learning_path_members lpm
814 , ota_events evt
815 , ota_delegate_bookings tdb
816 , ota_lp_enrollments lpe
817 , ota_booking_status_types bst
818 where lpc.learning_path_id = lpe.learning_path_id
819 and lpm.learning_path_section_id = lpc.learning_path_section_id
820 and lpm.activity_version_id = evt.activity_version_id
821 and tdb.event_id = evt.event_id
822 and lpc.completion_type_code = 'S'
823 and tdb.delegate_contact_id = lpe.contact_id
824 and lpe.lp_enrollment_id = p_lp_enrollment_id
825 and lpe.path_status_code = 'COMPLETED'
826 and tdb.booking_status_type_id = bst.booking_status_type_id
827 and bst.type = 'A'
828 group by lpc.learning_path_section_id
829
830 UNION ALL
831
832 select
833 sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
834 sum(1) no_of_courses
835 from ota_lp_sections lpc
836 , ota_learning_path_members lpm
837 , ota_events evt
838 , ota_delegate_bookings tdb
839 , ota_lp_enrollments lpe
840 , ota_booking_status_types bst
841 where lpc.learning_path_id = lpe.learning_path_id
842 and lpm.learning_path_section_id = lpc.learning_path_section_id
843 and lpm.activity_version_id = evt.activity_version_id
844 and tdb.event_id = evt.event_id
845 and lpc.completion_type_code = 'M'
846 and tdb.delegate_contact_id = lpe.contact_id
847 and lpe.lp_enrollment_id = p_lp_enrollment_id
848 and lpe.path_status_code = 'COMPLETED'
849 and tdb.booking_status_type_id = bst.booking_status_type_id
850 and bst.type = 'A'
851 group by lpc.learning_path_section_id;
852
853 l_lpc_rec csr_chk_person%ROWTYPE;
854 l_lpc_rec2 csr_chk_contact%ROWTYPE;
855
856 BEGIN
857 OPEN csr_get_lpe_info;
858 FETCH csr_get_lpe_info INTO l_learning_path_id, l_path_status, l_person_id, l_contact_id;
859 CLOSE csr_get_lpe_info;
860
861 IF l_path_status <> 'COMPLETED' THEN
862 return 'N';
863 END IF;
864
865 IF l_person_id IS NOT NULL THEN
866 FOR l_lpc_rec IN csr_chk_person LOOP
867 IF l_lpc_rec.completed_courses < l_lpc_rec.no_of_courses THEN
868 return 'N';
869 END IF;
870 END LOOP;
871
872 ELSIF l_contact_id IS NOT NULL THEN
873 FOR l_lpc_rec2 IN csr_chk_contact LOOP
874 IF l_lpc_rec2.completed_courses < l_lpc_rec.no_of_courses THEN
875 return 'N';
876 END IF;
877 END LOOP;
878 END IF;
879 return 'Y';
880 END is_path_successful;
881
882 Procedure Start_comp_proc_success_attnd(p_person_id in number ,
883 p_event_id in ota_Events.event_id%type)
884 is
885
886 cursor get_lp_enroll is
887 select lp_enrollment_id, lpe.learning_path_id
888 From ota_learning_path_members lpm
889 , ota_events evt
890 , ota_lp_enrollments lpe
891 where evt.activity_version_id = lpm.activity_version_id
892 and lpm.learning_path_id = lpe.learning_path_id
893 and evt.event_id = p_event_id
894 and lpe.person_id = p_person_id;
895
896 l_sucessful varchar(2) := 'N';
897
898 l_item_key wf_items.item_key%type;
899
900 begin
901
902 For rec in get_lp_enroll
903 loop
904
905 l_sucessful := is_path_successful(rec.lp_enrollment_id);
906
907 if l_sucessful = 'Y' then
908
909 ota_competence_ss.create_wf_process(p_process => 'OTA_COMPETENCE_UPDATE_JSP_PRC',
910 p_itemtype => 'HRSSA',
911 p_person_id => p_person_id,
912 p_eventid => null,
913 p_learningpath_ids => to_char(rec.learning_path_id),
914 p_itemkey => l_item_key);
915
916 end if;
917 end loop;
918
919 end Start_comp_proc_success_attnd;
920
921
922 --
923 END ota_lrng_path_util;
924