eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_LRNG_PATH_UTIL dependencies on OTA_LP_ENROLLMENTS

Line 14: Function chk_complete_path_ok(p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type)

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

Line 20: FROM ota_lp_enrollments lpe

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

Line 27: ota_lp_enrollments lpe

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;

Line 37: ota_lp_enrollments lpe

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:

Line 45: FROM ota_lp_sections lps, ota_lp_enrollments lpe

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

Line 60: l_path_status ota_lp_enrollments.path_status_code%TYPE;

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:

Line 134: FUNCTION chk_login_person(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)

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;

Line 152: FROM ota_lp_enrollments lpe

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

Line 182: FUNCTION get_person_id(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)

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

Line 188: FROM ota_lp_enrollments

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:

Line 210: Procedure complete_path(p_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE)

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

Line 217: FROM ota_lp_enrollments lpe

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;

Line 220: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;

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

Line 221: l_object_version_number ota_lp_enrollments.object_version_number%type;

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';

Line 222: l_path_status_code ota_lp_enrollments.path_status_code%type;

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:

Line 263: l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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

Line 264: l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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;

Line 265: l_return ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;

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;

Line 325: FUNCTION get_no_of_completed_courses(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,

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:

Line 333: FROM ota_lp_enrollments lme,

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;

Line 357: l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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:

Line 358: l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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

Line 359: l_completed_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;

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:

Line 360: l_return ota_lp_enrollments.no_of_mandatory_courses%TYPE := 0;

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;

Line 440: PROCEDURE Update_lpe_lpm_changes( p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,

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)

Line 443: p_completion_target_date IN ota_lp_enrollments.completion_target_date%TYPE,

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

Line 450: ota_lp_enrollments lpe

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;

Line 462: FROM ota_lp_enrollments lpe

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

Line 470: FROM ota_learning_paths lps, ota_lp_enrollments lpe

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;

Line 474: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;

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;

Line 476: l_object_version_number ota_lp_enrollments.object_version_number%type;

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;

Line 477: l_path_status_code ota_lp_enrollments.path_status_code%type;

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';

Line 478: l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%type;

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);

Line 479: l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%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) :='';

Line 480: l_completion_target_date ota_lp_enrollments.completion_target_date%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;

Line 608: FUNCTION get_talent_mgmt_lp(p_person_id IN ota_lp_enrollments.person_id%TYPE

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)

Line 696: ota_lp_enrollments lpe

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)

Line 749: Function is_path_successful(p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type)

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);

Line 760: FROM ota_lp_enrollments lpe

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

Line 771: , ota_lp_enrollments lpe

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

Line 794: , ota_lp_enrollments lpe

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

Line 816: , ota_lp_enrollments lpe

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

Line 839: , ota_lp_enrollments lpe

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

Line 890: , ota_lp_enrollments lpe

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;