[Home] [Help]
6: function compute_default_lesson_status(
7: p_lo_id ota_learning_objects.learning_object_id%type,
8: p_user_id fnd_user.user_id%type,
9: p_user_type ota_attempts.user_type%type,
10: p_old_lesson_status ota_performances.lesson_status%type,
11: p_starting_url ota_learning_objects.starting_url%type,
12: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
13:
14: cursor child_los(
8: p_user_id fnd_user.user_id%type,
9: p_user_type ota_attempts.user_type%type,
10: p_old_lesson_status ota_performances.lesson_status%type,
11: p_starting_url ota_learning_objects.starting_url%type,
12: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
13:
14: cursor child_los(
15: p_lo_id ota_learning_objects.learning_object_id%type,
16: p_user_id fnd_user.user_id%type,
15: p_lo_id ota_learning_objects.learning_object_id%type,
16: p_user_id fnd_user.user_id%type,
17: p_user_type ota_attempts.user_type%type) is
18: select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
19: from ota_learning_objects lo, ota_performances p
20: where lo.parent_learning_object_id = p_lo_id and
21: lo.published_flag = 'Y' and
22: p.learning_object_id(+) = lo.source_learning_object_id and
23: p.user_id(+) = p_user_id and
66: function compute_lesson_status(
67: p_lo_id ota_learning_objects.learning_object_id%type,
68: p_user_id fnd_user.user_id%type,
69: p_user_type ota_attempts.user_type%type,
70: p_old_lesson_status ota_performances.lesson_status%type,
71: p_starting_url ota_learning_objects.starting_url%type,
72: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
73:
74: /* Completion reqs not yet implemented - GDHUTTON 12/24/03
68: p_user_id fnd_user.user_id%type,
69: p_user_type ota_attempts.user_type%type,
70: p_old_lesson_status ota_performances.lesson_status%type,
71: p_starting_url ota_learning_objects.starting_url%type,
72: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
73:
74: /* Completion reqs not yet implemented - GDHUTTON 12/24/03
75: cursor completion_requirements(
76: p_lo_id ota_learning_objects.learning_object_id%type,
75: cursor completion_requirements(
76: p_lo_id ota_learning_objects.learning_object_id%type,
77: p_user_id fnd_user.user_id%type,
78: p_user_type ota_attempts.user_type%type,
79: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
80: select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,
81: null as match_lesson_status, nvl(p.lesson_status, 'N') as user_lesson_status
82: from ota_performances p, ota_completion_requirements cr
83: where cr.child_learning_object_id = p_lo_id and
78: p_user_type ota_attempts.user_type%type,
79: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
80: select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,
81: null as match_lesson_status, nvl(p.lesson_status, 'N') as user_lesson_status
82: from ota_performances p, ota_completion_requirements cr
83: where cr.child_learning_object_id = p_lo_id and
84: p.learning_object_id(+) = cr.parent_learning_object_id and
85: p.user_id(+) = p_user_id and
86: p.user_type(+) = p_user_type and
106:
107: -- Returns true if a transition from v_old_status to v_new_status is
108: -- allowed and necessary
109: function lesson_status_transition_valid(
110: p_old_lesson_status ota_performances.lesson_status%type,
111: p_new_lesson_status ota_performances.lesson_status%type) return boolean is
112: begin
113: return
114: p_new_lesson_status <> p_old_lesson_status and
107: -- Returns true if a transition from v_old_status to v_new_status is
108: -- allowed and necessary
109: function lesson_status_transition_valid(
110: p_old_lesson_status ota_performances.lesson_status%type,
111: p_new_lesson_status ota_performances.lesson_status%type) return boolean is
112: begin
113: return
114: p_new_lesson_status <> p_old_lesson_status and
115: p_old_lesson_status <> 'P' and
271:
272: cursor perf_lesson_status is
273: select per.lesson_status,
274: offe.learning_object_id
275: from ota_performances per,
276: ota_offerings offe,
277: ota_events evt
278: where evt.parent_offering_id = offe.offering_id and
279: offe.learning_object_id = per.learning_object_id(+) and
602: procedure set_performance_lesson_status(
603: p_lo_id ota_learning_objects.learning_object_id%type,
604: p_user_id fnd_user.user_id%type,
605: p_user_type ota_attempts.user_type%type,
606: p_lesson_status ota_performances.lesson_status%type,
607: p_cascaded boolean,
608: p_date date,
609: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
610:
605: p_user_type ota_attempts.user_type%type,
606: p_lesson_status ota_performances.lesson_status%type,
607: p_cascaded boolean,
608: p_date date,
609: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
610:
611: cursor affected_los(
612: p_lo_id ota_learning_objects.learning_object_id%type,
613: p_user_id fnd_user.user_id%type,
611: cursor affected_los(
612: p_lo_id ota_learning_objects.learning_object_id%type,
613: p_user_id fnd_user.user_id%type,
614: p_user_type ota_attempts.user_type%type,
615: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
616: select parent.learning_object_id as learning_object_id,
617: nvl(perf.lesson_status, 'N') as lesson_status,
618: parent.starting_url as starting_url
619: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
615: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
616: select parent.learning_object_id as learning_object_id,
617: nvl(perf.lesson_status, 'N') as lesson_status,
618: parent.starting_url as starting_url
619: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
620: where child.source_learning_object_id = p_lo_id and
621: child.parent_learning_object_id = parent.learning_object_id and
622: parent.learning_object_id = parent.source_learning_object_id and
623: parent.starting_url is null and
629: union
630: select cr.child_learning_object_id as learning_object_id,
631: nvl(perf.lesson_status, 'N') as lesson_status,
632: lo.starting_url as starting_url
633: from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo
634: where cr.parent_learning_object_id = p_lo_id and
635: cr.child_learning_object_id = lo.learning_object_id and
636: perf.learning_object_id(+) = cr.child_learning_object_id and
637: perf.user_id(+) = p_user_id and
637: perf.user_id(+) = p_user_id and
638: perf.user_type(+) = p_user_type and
639: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
640: */
641: v_completed_date ota_performances.completed_date%type;
642: v_source_lo_id ota_learning_objects.learning_object_id%type;
643: v_business_group_id ota_learning_objects.business_group_id%type;
644: v_old_lesson_status ota_performances.lesson_status%type;
645: v_new_lesson_status ota_performances.lesson_status%type;
640: */
641: v_completed_date ota_performances.completed_date%type;
642: v_source_lo_id ota_learning_objects.learning_object_id%type;
643: v_business_group_id ota_learning_objects.business_group_id%type;
644: v_old_lesson_status ota_performances.lesson_status%type;
645: v_new_lesson_status ota_performances.lesson_status%type;
646: v_performance_source ota_performances.source%type;
647: begin
648: -- The performance is actually stored on the source learning object, so get that ID.
641: v_completed_date ota_performances.completed_date%type;
642: v_source_lo_id ota_learning_objects.learning_object_id%type;
643: v_business_group_id ota_learning_objects.business_group_id%type;
644: v_old_lesson_status ota_performances.lesson_status%type;
645: v_new_lesson_status ota_performances.lesson_status%type;
646: v_performance_source ota_performances.source%type;
647: begin
648: -- The performance is actually stored on the source learning object, so get that ID.
649: -- Also get the business group in case we have to create a new performance record.
642: v_source_lo_id ota_learning_objects.learning_object_id%type;
643: v_business_group_id ota_learning_objects.business_group_id%type;
644: v_old_lesson_status ota_performances.lesson_status%type;
645: v_new_lesson_status ota_performances.lesson_status%type;
646: v_performance_source ota_performances.source%type;
647: begin
648: -- The performance is actually stored on the source learning object, so get that ID.
649: -- Also get the business group in case we have to create a new performance record.
650: select source_learning_object_id, business_group_id
655: begin
656: -- Find the old status and completed date.
657: select p.lesson_status, p.completed_date
658: into v_old_lesson_status, v_completed_date
659: from ota_performances p
660: where p.learning_object_id = v_source_lo_id and
661: p.user_id = p_user_id and
662: p.user_type = p_user_type and
663: nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
689: else
690: v_performance_source := 'ATTEMPT';
691: end if;
692:
693: update ota_performances
694: set lesson_status = p_lesson_status,
695: completed_date = v_completed_date,
696: last_updated_by = p_user_id,
697: last_update_date = p_date,
730: else
731: v_performance_source := 'ATTEMPT';
732: end if;
733:
734: -- Insert a new ota_performances record.
735: insert into ota_performances
736: (performance_id, user_id, user_type, learning_object_id,
737: lesson_status, score, time, completed_date,
738: created_by, creation_date, last_updated_by, last_update_date,
731: v_performance_source := 'ATTEMPT';
732: end if;
733:
734: -- Insert a new ota_performances record.
735: insert into ota_performances
736: (performance_id, user_id, user_type, learning_object_id,
737: lesson_status, score, time, completed_date,
738: created_by, creation_date, last_updated_by, last_update_date,
739: source, object_version_number, business_group_id, cert_prd_enrollment_id)
737: lesson_status, score, time, completed_date,
738: created_by, creation_date, last_updated_by, last_update_date,
739: source, object_version_number, business_group_id, cert_prd_enrollment_id)
740: values
741: (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
742: p_lesson_status, -1000, -1001, v_completed_date,
743: p_user_id, p_date, p_user_id, p_date,
744: v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
745: else
766: procedure set_performance_lesson_status(
767: p_lo_id ota_learning_objects.learning_object_id%type,
768: p_user_id fnd_user.user_id%type,
769: p_user_type ota_attempts.user_type%type,
770: p_lesson_status ota_performances.lesson_status%type,
771: p_date date,
772: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
773: begin
774: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, false, p_date, p_cert_prd_enroll_id);
768: p_user_id fnd_user.user_id%type,
769: p_user_type ota_attempts.user_type%type,
770: p_lesson_status ota_performances.lesson_status%type,
771: p_date date,
772: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
773: begin
774: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, false, p_date, p_cert_prd_enroll_id);
775: end set_performance_lesson_status;
776:
778: procedure set_performance_lesson_status(
779: p_lo_id ota_learning_objects.learning_object_id%type,
780: p_user_id fnd_user.user_id%type,
781: p_user_type ota_attempts.user_type%type,
782: p_lesson_status ota_performances.lesson_status%type,
783: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
784: begin
785: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, sysdate, p_cert_prd_enroll_id);
786: end set_performance_lesson_status;
779: p_lo_id ota_learning_objects.learning_object_id%type,
780: p_user_id fnd_user.user_id%type,
781: p_user_type ota_attempts.user_type%type,
782: p_lesson_status ota_performances.lesson_status%type,
783: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
784: begin
785: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, sysdate, p_cert_prd_enroll_id);
786: end set_performance_lesson_status;
787:
789: procedure set_performance_time(
790: p_lo_id ota_learning_objects.learning_object_id%type,
791: p_user_id fnd_user.user_id%type,
792: p_user_type ota_attempts.user_type%type,
793: p_time ota_performances.time%type,
794: p_cascaded boolean,
795: p_date date,
796: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
797:
792: p_user_type ota_attempts.user_type%type,
793: p_time ota_performances.time%type,
794: p_cascaded boolean,
795: p_date date,
796: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
797:
798: cursor parent_los(
799: p_lo_id ota_learning_objects.learning_object_id%type,
800: p_user_id fnd_user.user_id%type,
798: cursor parent_los(
799: p_lo_id ota_learning_objects.learning_object_id%type,
800: p_user_id fnd_user.user_id%type,
801: p_user_type ota_attempts.user_type%type,
802: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
803: select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
804: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
805: where child.source_learning_object_id = p_lo_id and
806: child.parent_learning_object_id = parent.learning_object_id and
800: p_user_id fnd_user.user_id%type,
801: p_user_type ota_attempts.user_type%type,
802: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
803: select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
804: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
805: where child.source_learning_object_id = p_lo_id and
806: child.parent_learning_object_id = parent.learning_object_id and
807: parent.learning_object_id = parent.source_learning_object_id and
808: parent.starting_url is null and
814: cursor distinct_child_los(
815: p_lo_id ota_learning_objects.learning_object_id%type,
816: p_user_id fnd_user.user_id%type,
817: p_user_type ota_attempts.user_type%type,
818: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
819: select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
820: from ota_performances p,
821: (select distinct learning_object_id, source_learning_object_id, starting_url
822: from ota_learning_objects
816: p_user_id fnd_user.user_id%type,
817: p_user_type ota_attempts.user_type%type,
818: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
819: select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
820: from ota_performances p,
821: (select distinct learning_object_id, source_learning_object_id, starting_url
822: from ota_learning_objects
823: where learning_object_id <> p_lo_id
824: start with learning_object_id = p_lo_id
830: nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
831:
832: v_source_lo_id ota_learning_objects.learning_object_id%type;
833: v_business_group_id ota_learning_objects.business_group_id%type;
834: v_old_time ota_performances.time%type;
835: v_new_time ota_performances.time%type;
836: v_performance_source ota_performances.source%type;
837: begin
838: -- The performance is actually stored on the source learning object, so get that ID.
831:
832: v_source_lo_id ota_learning_objects.learning_object_id%type;
833: v_business_group_id ota_learning_objects.business_group_id%type;
834: v_old_time ota_performances.time%type;
835: v_new_time ota_performances.time%type;
836: v_performance_source ota_performances.source%type;
837: begin
838: -- The performance is actually stored on the source learning object, so get that ID.
839: -- Also get the business group in case we have to create a new performance record.
832: v_source_lo_id ota_learning_objects.learning_object_id%type;
833: v_business_group_id ota_learning_objects.business_group_id%type;
834: v_old_time ota_performances.time%type;
835: v_new_time ota_performances.time%type;
836: v_performance_source ota_performances.source%type;
837: begin
838: -- The performance is actually stored on the source learning object, so get that ID.
839: -- Also get the business group in case we have to create a new performance record.
840: select source_learning_object_id, business_group_id
844:
845: begin
846: select p.time
847: into v_old_time
848: from ota_performances p
849: where p.learning_object_id = v_source_lo_id and
850: p.user_id = p_user_id and
851: p.user_type = p_user_type and
852: nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
851: p.user_type = p_user_type and
852: nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
853:
854: if p_time <> v_old_time then
855: update ota_performances
856: set time = p_time,
857: last_updated_by = p_user_id,
858: last_update_date = p_date
859: where user_id = p_user_id and
872: else
873: v_performance_source := 'ATTEMPT';
874: end if;
875:
876: insert into ota_performances
877: (performance_id, user_id, user_type, learning_object_id,
878: lesson_status, score, time,
879: created_by, creation_date, last_updated_by, last_update_date,
880: source, object_version_number, business_group_id, cert_prd_enrollment_id)
878: lesson_status, score, time,
879: created_by, creation_date, last_updated_by, last_update_date,
880: source, object_version_number, business_group_id, cert_prd_enrollment_id)
881: values
882: (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
883: 'N', -1000, p_time,
884: p_user_id, p_date, p_user_id, p_date,
885: v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
886: else
907: procedure set_performance_time(
908: p_lo_id ota_learning_objects.learning_object_id%type,
909: p_user_id fnd_user.user_id%type,
910: p_user_type ota_attempts.user_type%type,
911: p_time ota_performances.time%type,
912: p_date date,
913: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
914: begin
915: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, false, p_date, p_cert_prd_enroll_id);
909: p_user_id fnd_user.user_id%type,
910: p_user_type ota_attempts.user_type%type,
911: p_time ota_performances.time%type,
912: p_date date,
913: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
914: begin
915: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, false, p_date, p_cert_prd_enroll_id);
916: end set_performance_time;
917:
919: procedure set_performance_time(
920: p_lo_id ota_learning_objects.learning_object_id%type,
921: p_user_id fnd_user.user_id%type,
922: p_user_type ota_attempts.user_type%type,
923: p_time ota_performances.time%type,
924: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
925: begin
926: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, sysdate, p_cert_prd_enroll_id);
927: end set_performance_time;
920: p_lo_id ota_learning_objects.learning_object_id%type,
921: p_user_id fnd_user.user_id%type,
922: p_user_type ota_attempts.user_type%type,
923: p_time ota_performances.time%type,
924: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
925: begin
926: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, sysdate, p_cert_prd_enroll_id);
927: end set_performance_time;
928:
1006: /*
1007: cursor csr_chk_prereqs is
1008: select 1
1009: from ota_prerequisites preq,
1010: ota_performances perf
1011: where preq.parent_object_id = p_lo_id and
1012: preq.object_id = perf.learning_object_id(+) and
1013: perf.user_id(+) = p_user_id and
1014: perf.user_type(+) = p_user_type and
1030: and parent_type = 'LO';
1031:
1032: CURSOR csr_get_performances(csr_lo_id NUMBER) IS
1033: SELECT 1
1034: FROM ota_performances
1035: WHERE learning_object_id = csr_lo_id
1036: AND user_id = p_user_id
1037: AND user_type = p_user_type
1038: AND lesson_status in ('P', 'C');
1928: l_formatted_min varchar(20) := '';
1929: l_formatted_sec varchar(20) := '';
1930: l_formatted_time varchar(20) := '';
1931: l_return_tree_title varchar(500) := '';
1932: l_max_performance_id ota_performances.performance_id%type;
1933: l_tst_grade_flag ota_tests.grade_flag%TYPE;
1934: l_var_score VARCHAR2(100);
1935: l_lo_completed_date VARCHAR2(100);
1936: l_lo_completed_time VARCHAR2(10);
1934: l_var_score VARCHAR2(100);
1935: l_lo_completed_date VARCHAR2(100);
1936: l_lo_completed_time VARCHAR2(10);
1937: l_lo_completed_date_tz VARCHAR2(100);
1938: l_lo_lesson_status ota_performances.lesson_status%type;
1939:
1940: CURSOR c_get_lo_tree_link(p_performance_id in number) is
1941: Select
1942: olo.name Name,
1958: , to_char(opf.completed_date)
1959: , to_char(opf.completed_date, 'HH24:MI:SS')
1960: , opf.lesson_status
1961: , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
1962: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
1963: OTA_TESTS tst
1964: Where
1965: olo.learning_object_id = opf.learning_object_id(+)
1966: AND tst.test_id(+) = olo.test_id
1971: AND cert_prd_enrollment_id(+) is null;
1972:
1973: CURSOR c_max_performance_id is
1974: Select max(per.performance_id)
1975: From ota_performances per
1976: Where per.learning_object_id(+) = p_lo_id
1977: And per.user_id(+) = p_user_id
1978: And per.user_type(+) = p_user_type
1979: AND per.cert_prd_enrollment_id(+) is null;
2708: AND oev.event_id = p_event_id;
2709:
2710: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2711: SELECT lesson_status
2712: FROM ota_performances
2713: WHERE user_id = p_user_id
2714: AND user_type = p_user_type
2715: AND learning_object_id = l_lo_id
2716: AND cert_prd_enrollment_id is null;
2752: fetch c_odb_lo_status into l_status;
2753: close c_odb_lo_status;
2754: Else
2755: -- EBS created, Status code should be taken from
2756: -- OTA_PERFORMANCES
2757: open c_learning_object_status(l_learning_object_id);
2758: fetch c_learning_object_status into l_status;
2759: close c_learning_object_status;
2760:
2808: AND oev.event_id = p_event_id;
2809:
2810: CURSOR c_learning_object_time(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2811: SELECT TO_CHAR(TRUNC(SYSDATE)+(time)/86400, 'HH24:Mi:SS')
2812: FROM ota_performances
2813: WHERE user_id = p_user_id
2814: AND user_type = p_user_type
2815: AND learning_object_id = l_lo_id
2816: AND cert_prd_enrollment_id is null;
2852: fetch c_odb_lo_time into l_time;
2853: close c_odb_lo_time;
2854: Else
2855: -- EBS created, time should be taken from
2856: -- OTA_PERFORMANCES
2857: open c_learning_object_time(l_learning_object_id);
2858: fetch c_learning_object_time into l_time;
2859: close c_learning_object_time;
2860:
2908: AND oev.event_id = p_event_id;
2909:
2910: CURSOR c_learning_object_score(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2911: SELECT score
2912: FROM ota_performances
2913: WHERE user_id = p_user_id
2914: AND user_type = p_user_type
2915: AND learning_object_id = l_lo_id
2916: AND cert_prd_enrollment_id is null;
2952: fetch c_odb_lo_score into l_score;
2953: close c_odb_lo_score;
2954: Else
2955: -- EBS created, Score should be taken from
2956: -- OTA_PERFORMANCES
2957: open c_learning_object_score(l_learning_object_id);
2958: fetch c_learning_object_score into l_score;
2959: close c_learning_object_score;
2960:
3028:
3029: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3030: SELECT lesson_status,
3031: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3032: FROM ota_performances
3033: WHERE user_id = p_user_id
3034: AND user_type = p_user_type
3035: AND learning_object_id = l_lo_id
3036: AND cert_prd_enrollment_id is null;
3055: 'B', '5',
3056: 'N', '6') decode_lesson_status,
3057: prf.lesson_status lesson_status,
3058: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS', prf.lesson_status) lesson_status_name
3059: FROM ota_performances prf
3060: WHERE
3061: prf.user_id = p_user_id
3062: and prf.user_type = p_user_type
3063: and prf.learning_object_id = l_lo_id
3119: fetch c_odb_lo_status into l_status, l_status_name;
3120: close c_odb_lo_status;
3121: Else
3122: -- EBS created, Status should be taken from
3123: -- OTA_PERFORMANCES
3124:
3125: If ( l_enrollment_status_code = 'A' and p_mode = 2 ) Then
3126: -- p_mode = 2 means that coming from admin side
3127: open csr_best_prf(l_learning_object_id);
3246: WHERE event_id = p_event_id;
3247:
3248: CURSOR c_history_enabled IS
3249: SELECT lesson_status
3250: FROM ota_performances
3251: WHERE learning_object_id = p_lo_id
3252: AND user_id = p_user_id
3253: AND lesson_status IN ('P', 'C');
3254:
3294: FETCH c_history_enabled_odb INTO l_type;
3295: CLOSE c_history_enabled_odb;
3296: ELSE
3297: -- EBS created, Status should be taken from
3298: -- OTA_PERFORMANCES
3299: OPEN c_history_enabled;
3300: FETCH c_history_enabled INTO l_type;
3301: CLOSE c_history_enabled;
3302: END IF;
3357: --Added method get_lo_completion_date_time,similar to get_lo_completion_date but returns a date allowing UI sort
3358: FUNCTION get_lo_completion_date(p_event_id IN ota_events.event_id%type,
3359: p_user_id IN NUMBER,
3360: p_user_type IN ota_attempts.user_type%type,
3361: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3362: p_module_name IN VARCHAR2 default 'LEARNER')
3363: RETURN varchar2
3364: IS
3365: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date';
3368: -- l_lo_completed_date VARCHAR2(100);
3369: l_lo_completed_date DATE;
3370: l_lo_completed_time VARCHAR2(10);
3371: l_lo_completed_date_tz VARCHAR2(100);
3372: l_lo_lesson_status ota_performances.lesson_status%type;
3373: l_sync_flag ota_category_usages.synchronous_flag%type;
3374: l_online_flag ota_category_usages.online_flag%type;
3375: l_return_completion_date varchar(50) := '';
3376:
3387: -- (Select Online_Flag from ota_category_usages where Category_Usage_Id = ofr.Delivery_Mode_Id) Online_Flag
3388: ocu.Synchronous_Flag Sync_Flag,
3389: ocu.Online_Flag Online_Flag,
3390: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3391: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3392: Where
3393: oev.parent_offering_id = ofr.offering_id
3394: And ofr.learning_object_id = opf.learning_object_id(+)
3395: And oev.event_id = p_event_id
3449:
3450: FUNCTION get_lo_completion_date_time(p_event_id IN ota_events.event_id%type,
3451: p_user_id IN NUMBER,
3452: p_user_type IN ota_attempts.user_type%type,
3453: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3454: p_module_name IN VARCHAR2 default 'LEARNER')
3455: RETURN date
3456: IS
3457: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date_time';
3460: l_lo_completed_date DATE;
3461: l_lo_comp_date DATE;
3462: l_lo_completed_time VARCHAR2(10);
3463: l_lo_completed_date_tz VARCHAR2(100);
3464: l_lo_lesson_status ota_performances.lesson_status%type;
3465: l_sync_flag ota_category_usages.synchronous_flag%type;
3466: l_online_flag ota_category_usages.online_flag%type;
3467: l_return_completion_date date:= null;
3468:
3476: ocu.Synchronous_Flag Sync_Flag,
3477: ocu.Online_Flag Online_Flag,
3478: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
3479: ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
3480: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3481: Where
3482: oev.parent_offering_id = ofr.offering_id
3483: And ofr.learning_object_id = opf.learning_object_id(+)
3484: And oev.event_id = p_event_id
3571:
3572: CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3573: SELECT lesson_status,
3574: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3575: FROM ota_performances
3576: WHERE user_id = p_user_id
3577: AND user_type = p_user_type
3578: AND learning_object_id = l_lo_id
3579: AND cert_prd_enrollment_id = p_cert_prd_enrollment_id;
3595:
3596: -- Check for LO status for online classes
3597: If ( l_online_flag = 'Y' ) Then
3598:
3599: -- OTA_PERFORMANCES for CERT_PRD_ENROLLMENT_ID
3600: open c_prd_lo_status(l_learning_object_id);
3601: fetch c_prd_lo_status into l_status, l_status_name;
3602: close c_prd_lo_status;
3603:
3655:
3656: FUNCTION get_cert_lo_title_for_tree(p_lo_id IN NUMBER,
3657: p_user_id IN NUMBER,
3658: p_user_type IN ota_attempts.user_type%type,
3659: p_cert_prd_enrollment_id IN ota_performances.cert_prd_enrollment_id%type,
3660: p_mode IN NUMBER default 1)
3661: RETURN varchar2
3662: IS
3663: l_proc VARCHAR2(72) := g_package||'get_cert_lo_title_for_tree';
3673: l_formatted_min varchar(20) := '';
3674: l_formatted_sec varchar(20) := '';
3675: l_formatted_time varchar(20) := '';
3676: l_return_tree_title varchar(500) := '';
3677: l_max_performance_id ota_performances.performance_id%type;
3678: l_tst_grade_flag ota_tests.grade_flag%TYPE;
3679: l_var_score VARCHAR2(100);
3680: l_lo_completed_date VARCHAR2(100);
3681: l_lo_completed_time VARCHAR2(10);
3679: l_var_score VARCHAR2(100);
3680: l_lo_completed_date VARCHAR2(100);
3681: l_lo_completed_time VARCHAR2(10);
3682: l_lo_completed_date_tz VARCHAR2(100);
3683: l_lo_lesson_status ota_performances.lesson_status%type;
3684:
3685: CURSOR c_get_cert_lo_tree_link(p_performance_id in number) is
3686: Select
3687: olo.name Name,
3703: , to_char(opf.completed_date)
3704: , to_char(opf.completed_date, 'HH24:MI:SS')
3705: , opf.lesson_status
3706: , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3707: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
3708: OTA_TESTS tst
3709: Where
3710: olo.learning_object_id = opf.learning_object_id(+)
3711: AND tst.test_id(+) = olo.test_id
3716: And opf.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
3717:
3718: CURSOR c_max_performance_id is
3719: Select max(per.performance_id)
3720: From ota_performances per
3721: Where per.learning_object_id(+) = p_lo_id
3722: And per.user_id(+) = p_user_id
3723: And per.user_type(+) = p_user_type
3724: And per.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4097: CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE,
4098: csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4099: SELECT lesson_status,
4100: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4101: FROM ota_performances
4102: WHERE user_id = p_user_id
4103: AND user_type = p_user_type
4104: AND learning_object_id = l_lo_id
4105: AND cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
4125: 'B', '5',
4126: 'N', '6') decode_lesson_status,
4127: prf.lesson_status lesson_status,
4128: prf.performance_id
4129: FROM ota_performances prf,
4130: ota_offerings ofr,
4131: ota_learning_objects lo
4132: WHERE
4133: prf.user_id = p_user_id
4374: -- Access Status
4375: -- Public
4376: -- {End of Comments}
4377: ------------------------------------------------------------------
4378: Function format_lo_time(pTime ota_performances.time%type)
4379: return varchar2 IS
4380:
4381: l_proc VARCHAR2(72) := g_package||'format_lo_time';
4382: l_lo_time number;
4675: 'B', '5',
4676: 'N', '6') decode_lesson_status,
4677: prf.lesson_status lesson_status,
4678: prf.performance_id
4679: FROM ota_performances prf,
4680: ota_offerings ofr,
4681: ota_learning_objects lo
4682: WHERE
4683: prf.user_id = p_user_id