[Home] [Help]
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
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
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
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';
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:
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;
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;
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)
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:
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;
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:
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
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
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
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
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