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 138: FUNCTION chk_login_person(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)

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;

Line 156: FROM ota_lp_enrollments lpe

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

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

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

Line 192: FROM ota_lp_enrollments

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:

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

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

Line 221: FROM ota_lp_enrollments lpe

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;

Line 224: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;

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

Line 225: l_object_version_number ota_lp_enrollments.object_version_number%type;

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

Line 226: l_path_status_code ota_lp_enrollments.path_status_code%type;

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:

Line 267: l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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

Line 268: l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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;

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

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;

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

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:

Line 337: FROM ota_lp_enrollments lme,

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;

Line 361: l_s_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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:

Line 362: l_m_lpm ota_lp_enrollments.no_of_mandatory_courses%TYPE;

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

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

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:

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

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;

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

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)

Line 447: p_completion_target_date IN ota_lp_enrollments.completion_target_date%TYPE,

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

Line 454: ota_lp_enrollments lpe

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;

Line 466: FROM ota_lp_enrollments lpe

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

Line 474: FROM ota_learning_paths lps, ota_lp_enrollments lpe

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;

Line 478: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;

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;

Line 480: l_object_version_number ota_lp_enrollments.object_version_number%type;

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;

Line 481: l_path_status_code ota_lp_enrollments.path_status_code%type;

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

Line 482: l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%type;

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

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

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

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

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)

Line 700: ota_lp_enrollments lpe

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)

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

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

Line 764: FROM ota_lp_enrollments lpe

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

Line 775: , ota_lp_enrollments lpe

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

Line 798: , ota_lp_enrollments lpe

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

Line 820: , ota_lp_enrollments lpe

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

Line 843: , ota_lp_enrollments lpe

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

Line 894: , ota_lp_enrollments lpe

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;

Line 926: FUNCTION get_no_of_mand_compl_courses(p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE)

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

Line 931: from ota_lp_enrollments ole, ota_lp_sections ols

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;

Line 946: function get_lp_current_status(p_lp_enrollment_id in ota_lp_enrollments.lp_enrollment_id%type)

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,

Line 960: FROM ota_lp_enrollments lpe,

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

Line 1032: p_person_id IN ota_lp_enrollments.person_id%type,

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

Line 1033: p_contact_id ota_lp_enrollments.contact_id%type)

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

Line 1034: return ota_lp_enrollments.lp_enrollment_id%type IS

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,

Line 1038: FROM ota_lp_enrollments lpe,

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

Line 1051: FROM ota_lp_enrollments lpe,

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

Line 1062: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%type;

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