[Home] [Help]
68: function compute_default_lesson_status(
69: p_lo_id ota_learning_objects.learning_object_id%type,
70: p_user_id fnd_user.user_id%type,
71: p_user_type ota_attempts.user_type%type,
72: p_old_lesson_status ota_performances.lesson_status%type,
73: p_starting_url ota_learning_objects.starting_url%type,
74: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
75: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
76:
70: p_user_id fnd_user.user_id%type,
71: p_user_type ota_attempts.user_type%type,
72: p_old_lesson_status ota_performances.lesson_status%type,
73: p_starting_url ota_learning_objects.starting_url%type,
74: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
75: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
76:
77: cursor child_los(
78: p_lo_id ota_learning_objects.learning_object_id%type,
71: p_user_type ota_attempts.user_type%type,
72: p_old_lesson_status ota_performances.lesson_status%type,
73: p_starting_url ota_learning_objects.starting_url%type,
74: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
75: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
76:
77: cursor child_los(
78: p_lo_id ota_learning_objects.learning_object_id%type,
79: p_user_id fnd_user.user_id%type,
78: p_lo_id ota_learning_objects.learning_object_id%type,
79: p_user_id fnd_user.user_id%type,
80: p_user_type ota_attempts.user_type%type) is
81: select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
82: from ota_learning_objects lo, ota_performances p
83: where lo.parent_learning_object_id = p_lo_id and
84: lo.published_flag = 'Y' and
85: p.learning_object_id(+) = lo.source_learning_object_id and
86: p.user_id(+) = p_user_id and
130: function compute_lesson_status(
131: p_lo_id ota_learning_objects.learning_object_id%type,
132: p_user_id fnd_user.user_id%type,
133: p_user_type ota_attempts.user_type%type,
134: p_old_lesson_status ota_performances.lesson_status%type,
135: p_starting_url ota_learning_objects.starting_url%type,
136: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
137: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
138:
132: p_user_id fnd_user.user_id%type,
133: p_user_type ota_attempts.user_type%type,
134: p_old_lesson_status ota_performances.lesson_status%type,
135: p_starting_url ota_learning_objects.starting_url%type,
136: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
137: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
138:
139: /* Completion reqs not yet implemented - GDHUTTON 12/24/03
140: cursor completion_requirements(
133: p_user_type ota_attempts.user_type%type,
134: p_old_lesson_status ota_performances.lesson_status%type,
135: p_starting_url ota_learning_objects.starting_url%type,
136: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
137: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
138:
139: /* Completion reqs not yet implemented - GDHUTTON 12/24/03
140: cursor completion_requirements(
141: p_lo_id ota_learning_objects.learning_object_id%type,
140: cursor completion_requirements(
141: p_lo_id ota_learning_objects.learning_object_id%type,
142: p_user_id fnd_user.user_id%type,
143: p_user_type ota_attempts.user_type%type,
144: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
145: select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,
146: null as match_lesson_status, nvl(p.lesson_status, 'N') as user_lesson_status
147: from ota_performances p, ota_completion_requirements cr
148: where cr.child_learning_object_id = p_lo_id and
143: p_user_type ota_attempts.user_type%type,
144: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
145: select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,
146: null as match_lesson_status, nvl(p.lesson_status, 'N') as user_lesson_status
147: from ota_performances p, ota_completion_requirements cr
148: where cr.child_learning_object_id = p_lo_id and
149: p.learning_object_id(+) = cr.parent_learning_object_id and
150: p.user_id(+) = p_user_id and
151: p.user_type(+) = p_user_type and
171:
172: -- Returns true if a transition from v_old_status to v_new_status is
173: -- allowed and necessary
174: function lesson_status_transition_valid(
175: p_old_lesson_status ota_performances.lesson_status%type,
176: p_new_lesson_status ota_performances.lesson_status%type) return boolean is
177: begin
178: return
179: p_new_lesson_status <> p_old_lesson_status and
172: -- Returns true if a transition from v_old_status to v_new_status is
173: -- allowed and necessary
174: function lesson_status_transition_valid(
175: p_old_lesson_status ota_performances.lesson_status%type,
176: p_new_lesson_status ota_performances.lesson_status%type) return boolean is
177: begin
178: return
179: p_new_lesson_status <> p_old_lesson_status and
180: p_old_lesson_status <> 'P' and
560:
561: cursor perf_lesson_status is
562: select per.lesson_status,
563: offe.learning_object_id
564: from ota_performances per,
565: ota_offerings offe,
566: ota_events evt
567: where evt.parent_offering_id = offe.offering_id and
568: offe.learning_object_id = per.learning_object_id(+) and
870: procedure set_performance_lesson_status(
871: p_lo_id ota_learning_objects.learning_object_id%type,
872: p_user_id fnd_user.user_id%type,
873: p_user_type ota_attempts.user_type%type,
874: p_lesson_status ota_performances.lesson_status%type,
875: p_cascaded boolean,
876: p_date date,
877: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
878: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
873: p_user_type ota_attempts.user_type%type,
874: p_lesson_status ota_performances.lesson_status%type,
875: p_cascaded boolean,
876: p_date date,
877: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
878: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
879:
880: cursor affected_los(
881: p_lo_id ota_learning_objects.learning_object_id%type,
874: p_lesson_status ota_performances.lesson_status%type,
875: p_cascaded boolean,
876: p_date date,
877: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
878: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
879:
880: cursor affected_los(
881: p_lo_id ota_learning_objects.learning_object_id%type,
882: p_user_id fnd_user.user_id%type,
880: cursor affected_los(
881: p_lo_id ota_learning_objects.learning_object_id%type,
882: p_user_id fnd_user.user_id%type,
883: p_user_type ota_attempts.user_type%type,
884: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
885: select parent.learning_object_id as learning_object_id,
886: nvl(perf.lesson_status, 'N') as lesson_status,
887: parent.starting_url as starting_url
888: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
884: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
885: select parent.learning_object_id as learning_object_id,
886: nvl(perf.lesson_status, 'N') as lesson_status,
887: parent.starting_url as starting_url
888: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
889: where child.source_learning_object_id = p_lo_id and
890: child.parent_learning_object_id = parent.learning_object_id and
891: parent.learning_object_id = parent.source_learning_object_id and
892: parent.starting_url is null and
899: union
900: select cr.child_learning_object_id as learning_object_id,
901: nvl(perf.lesson_status, 'N') as lesson_status,
902: lo.starting_url as starting_url
903: from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo
904: where cr.parent_learning_object_id = p_lo_id and
905: cr.child_learning_object_id = lo.learning_object_id and
906: perf.learning_object_id(+) = cr.child_learning_object_id and
907: perf.user_id(+) = p_user_id and
907: perf.user_id(+) = p_user_id and
908: perf.user_type(+) = p_user_type and
909: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
910: */
911: v_completed_date ota_performances.completed_date%type;
912: v_source_lo_id ota_learning_objects.learning_object_id%type;
913: v_business_group_id ota_learning_objects.business_group_id%type;
914: v_old_lesson_status ota_performances.lesson_status%type;
915: v_new_lesson_status ota_performances.lesson_status%type;
910: */
911: v_completed_date ota_performances.completed_date%type;
912: v_source_lo_id ota_learning_objects.learning_object_id%type;
913: v_business_group_id ota_learning_objects.business_group_id%type;
914: v_old_lesson_status ota_performances.lesson_status%type;
915: v_new_lesson_status ota_performances.lesson_status%type;
916: v_performance_source ota_performances.source%type;
917: begin
918: -- The performance is actually stored on the source learning object, so get that ID.
911: v_completed_date ota_performances.completed_date%type;
912: v_source_lo_id ota_learning_objects.learning_object_id%type;
913: v_business_group_id ota_learning_objects.business_group_id%type;
914: v_old_lesson_status ota_performances.lesson_status%type;
915: v_new_lesson_status ota_performances.lesson_status%type;
916: v_performance_source ota_performances.source%type;
917: begin
918: -- The performance is actually stored on the source learning object, so get that ID.
919: -- Also get the business group in case we have to create a new performance record.
912: v_source_lo_id ota_learning_objects.learning_object_id%type;
913: v_business_group_id ota_learning_objects.business_group_id%type;
914: v_old_lesson_status ota_performances.lesson_status%type;
915: v_new_lesson_status ota_performances.lesson_status%type;
916: v_performance_source ota_performances.source%type;
917: begin
918: -- The performance is actually stored on the source learning object, so get that ID.
919: -- Also get the business group in case we have to create a new performance record.
920: select source_learning_object_id, business_group_id
925: begin
926: -- Find the old status and completed date.
927: select p.lesson_status, p.completed_date
928: into v_old_lesson_status, v_completed_date
929: from ota_performances p
930: where p.learning_object_id = v_source_lo_id and
931: p.user_id = p_user_id and
932: p.user_type = p_user_type and
933: nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1) and
952: else
953: v_performance_source := 'ATTEMPT';
954: end if;
955:
956: update ota_performances
957: set lesson_status = p_lesson_status,
958: completed_date = v_completed_date,
959: last_updated_by = p_user_id,
960: last_update_date = p_date,
1009: else
1010: v_performance_source := 'ATTEMPT';
1011: end if;
1012:
1013: -- Insert a new ota_performances record.
1014: insert into ota_performances
1015: (performance_id, user_id, user_type, learning_object_id,
1016: lesson_status, score, time, completed_date,
1017: created_by, creation_date, last_updated_by, last_update_date,
1010: v_performance_source := 'ATTEMPT';
1011: end if;
1012:
1013: -- Insert a new ota_performances record.
1014: insert into ota_performances
1015: (performance_id, user_id, user_type, learning_object_id,
1016: lesson_status, score, time, completed_date,
1017: created_by, creation_date, last_updated_by, last_update_date,
1018: source, object_version_number, business_group_id, cert_prd_enrollment_id)
1016: lesson_status, score, time, completed_date,
1017: created_by, creation_date, last_updated_by, last_update_date,
1018: source, object_version_number, business_group_id, cert_prd_enrollment_id)
1019: values
1020: (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
1021: p_lesson_status, -1000, -1001, v_completed_date,
1022: p_user_id, p_date, p_user_id, p_date,
1023: v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
1024: else
1045: procedure set_performance_lesson_status(
1046: p_lo_id ota_learning_objects.learning_object_id%type,
1047: p_user_id fnd_user.user_id%type,
1048: p_user_type ota_attempts.user_type%type,
1049: p_lesson_status ota_performances.lesson_status%type,
1050: p_cascaded varchar2,
1051: p_date date,
1052: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1053: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1048: p_user_type ota_attempts.user_type%type,
1049: p_lesson_status ota_performances.lesson_status%type,
1050: p_cascaded varchar2,
1051: p_date date,
1052: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1053: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1054: l_cascaded boolean := false;
1055: begin
1056: if(p_cascaded = 'Y') then
1049: p_lesson_status ota_performances.lesson_status%type,
1050: p_cascaded varchar2,
1051: p_date date,
1052: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1053: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1054: l_cascaded boolean := false;
1055: begin
1056: if(p_cascaded = 'Y') then
1057: l_cascaded:=true;
1064: procedure set_performance_lesson_status(
1065: p_lo_id ota_learning_objects.learning_object_id%type,
1066: p_user_id fnd_user.user_id%type,
1067: p_user_type ota_attempts.user_type%type,
1068: p_lesson_status ota_performances.lesson_status%type,
1069: p_date date,
1070: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1071: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1072: begin
1066: p_user_id fnd_user.user_id%type,
1067: p_user_type ota_attempts.user_type%type,
1068: p_lesson_status ota_performances.lesson_status%type,
1069: p_date date,
1070: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1071: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1072: begin
1073: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, false, p_date, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1074: end set_performance_lesson_status;
1067: p_user_type ota_attempts.user_type%type,
1068: p_lesson_status ota_performances.lesson_status%type,
1069: p_date date,
1070: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1071: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1072: begin
1073: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, false, p_date, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1074: end set_performance_lesson_status;
1075:
1077: procedure set_performance_lesson_status(
1078: p_lo_id ota_learning_objects.learning_object_id%type,
1079: p_user_id fnd_user.user_id%type,
1080: p_user_type ota_attempts.user_type%type,
1081: p_lesson_status ota_performances.lesson_status%type,
1082: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1083: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1084: begin
1085: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1078: p_lo_id ota_learning_objects.learning_object_id%type,
1079: p_user_id fnd_user.user_id%type,
1080: p_user_type ota_attempts.user_type%type,
1081: p_lesson_status ota_performances.lesson_status%type,
1082: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1083: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1084: begin
1085: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1086: end set_performance_lesson_status;
1079: p_user_id fnd_user.user_id%type,
1080: p_user_type ota_attempts.user_type%type,
1081: p_lesson_status ota_performances.lesson_status%type,
1082: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1083: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1084: begin
1085: set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1086: end set_performance_lesson_status;
1087:
1089: procedure set_performance_time(
1090: p_lo_id ota_learning_objects.learning_object_id%type,
1091: p_user_id fnd_user.user_id%type,
1092: p_user_type ota_attempts.user_type%type,
1093: p_time ota_performances.time%type,
1094: p_cascaded boolean,
1095: p_date date,
1096: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1097: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1092: p_user_type ota_attempts.user_type%type,
1093: p_time ota_performances.time%type,
1094: p_cascaded boolean,
1095: p_date date,
1096: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1097: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1098:
1099: cursor parent_los(
1100: p_lo_id ota_learning_objects.learning_object_id%type,
1093: p_time ota_performances.time%type,
1094: p_cascaded boolean,
1095: p_date date,
1096: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1097: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1098:
1099: cursor parent_los(
1100: p_lo_id ota_learning_objects.learning_object_id%type,
1101: p_user_id fnd_user.user_id%type,
1099: cursor parent_los(
1100: p_lo_id ota_learning_objects.learning_object_id%type,
1101: p_user_id fnd_user.user_id%type,
1102: p_user_type ota_attempts.user_type%type,
1103: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1104: select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
1105: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
1106: where child.source_learning_object_id = p_lo_id and
1107: child.parent_learning_object_id = parent.learning_object_id and
1101: p_user_id fnd_user.user_id%type,
1102: p_user_type ota_attempts.user_type%type,
1103: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1104: select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
1105: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
1106: where child.source_learning_object_id = p_lo_id and
1107: child.parent_learning_object_id = parent.learning_object_id and
1108: parent.learning_object_id = parent.source_learning_object_id and
1109: parent.starting_url is null and
1116: cursor distinct_child_los(
1117: p_lo_id ota_learning_objects.learning_object_id%type,
1118: p_user_id fnd_user.user_id%type,
1119: p_user_type ota_attempts.user_type%type,
1120: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1121: select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
1122: from ota_performances p,
1123: (select distinct learning_object_id, source_learning_object_id, starting_url
1124: from ota_learning_objects
1118: p_user_id fnd_user.user_id%type,
1119: p_user_type ota_attempts.user_type%type,
1120: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1121: select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
1122: from ota_performances p,
1123: (select distinct learning_object_id, source_learning_object_id, starting_url
1124: from ota_learning_objects
1125: where learning_object_id <> p_lo_id
1126: start with learning_object_id = p_lo_id
1133: nvl(p.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
1134:
1135: v_source_lo_id ota_learning_objects.learning_object_id%type;
1136: v_business_group_id ota_learning_objects.business_group_id%type;
1137: v_old_time ota_performances.time%type;
1138: v_new_time ota_performances.time%type;
1139: v_performance_source ota_performances.source%type;
1140: begin
1141: -- The performance is actually stored on the source learning object, so get that ID.
1134:
1135: v_source_lo_id ota_learning_objects.learning_object_id%type;
1136: v_business_group_id ota_learning_objects.business_group_id%type;
1137: v_old_time ota_performances.time%type;
1138: v_new_time ota_performances.time%type;
1139: v_performance_source ota_performances.source%type;
1140: begin
1141: -- The performance is actually stored on the source learning object, so get that ID.
1142: -- Also get the business group in case we have to create a new performance record.
1135: v_source_lo_id ota_learning_objects.learning_object_id%type;
1136: v_business_group_id ota_learning_objects.business_group_id%type;
1137: v_old_time ota_performances.time%type;
1138: v_new_time ota_performances.time%type;
1139: v_performance_source ota_performances.source%type;
1140: begin
1141: -- The performance is actually stored on the source learning object, so get that ID.
1142: -- Also get the business group in case we have to create a new performance record.
1143: select source_learning_object_id, business_group_id
1147:
1148: begin
1149: select p.time
1150: into v_old_time
1151: from ota_performances p
1152: where p.learning_object_id = v_source_lo_id and
1153: p.user_id = p_user_id and
1154: p.user_type = p_user_type and
1155: nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1) and
1155: nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1) and
1156: nvl(p.scorm_learner_attempt_id, -1) = nvl(p_scorm_learner_attempt_id, -1);
1157:
1158: if p_time <> v_old_time then
1159: update ota_performances
1160: set time = p_time,
1161: last_updated_by = p_user_id,
1162: last_update_date = p_date
1163: where user_id = p_user_id and
1177: else
1178: v_performance_source := 'ATTEMPT';
1179: end if;
1180:
1181: insert into ota_performances
1182: (performance_id, user_id, user_type, learning_object_id,
1183: lesson_status, score, time,
1184: created_by, creation_date, last_updated_by, last_update_date,
1185: source, object_version_number, business_group_id, cert_prd_enrollment_id)
1183: lesson_status, score, time,
1184: created_by, creation_date, last_updated_by, last_update_date,
1185: source, object_version_number, business_group_id, cert_prd_enrollment_id)
1186: values
1187: (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
1188: 'N', -1000, p_time,
1189: p_user_id, p_date, p_user_id, p_date,
1190: v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
1191: else
1212: procedure set_performance_time(
1213: p_lo_id ota_learning_objects.learning_object_id%type,
1214: p_user_id fnd_user.user_id%type,
1215: p_user_type ota_attempts.user_type%type,
1216: p_time ota_performances.time%type,
1217: p_date date,
1218: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1219: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1220: begin
1214: p_user_id fnd_user.user_id%type,
1215: p_user_type ota_attempts.user_type%type,
1216: p_time ota_performances.time%type,
1217: p_date date,
1218: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1219: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1220: begin
1221: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, false, p_date, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1222: end set_performance_time;
1215: p_user_type ota_attempts.user_type%type,
1216: p_time ota_performances.time%type,
1217: p_date date,
1218: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1219: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1220: begin
1221: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, false, p_date, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1222: end set_performance_time;
1223:
1225: procedure set_performance_time(
1226: p_lo_id ota_learning_objects.learning_object_id%type,
1227: p_user_id fnd_user.user_id%type,
1228: p_user_type ota_attempts.user_type%type,
1229: p_time ota_performances.time%type,
1230: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1231: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1232: begin
1233: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1226: p_lo_id ota_learning_objects.learning_object_id%type,
1227: p_user_id fnd_user.user_id%type,
1228: p_user_type ota_attempts.user_type%type,
1229: p_time ota_performances.time%type,
1230: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1231: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1232: begin
1233: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1234: end set_performance_time;
1227: p_user_id fnd_user.user_id%type,
1228: p_user_type ota_attempts.user_type%type,
1229: p_time ota_performances.time%type,
1230: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1231: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1232: begin
1233: set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1234: end set_performance_time;
1235:
1313: /*
1314: cursor csr_chk_prereqs is
1315: select 1
1316: from ota_prerequisites preq,
1317: ota_performances perf
1318: where preq.parent_object_id = p_lo_id and
1319: preq.object_id = perf.learning_object_id(+) and
1320: perf.user_id(+) = p_user_id and
1321: perf.user_type(+) = p_user_type and
1337: and parent_type = 'LO';
1338:
1339: CURSOR csr_get_performances(csr_lo_id NUMBER) IS
1340: SELECT 1
1341: FROM ota_performances
1342: WHERE learning_object_id = csr_lo_id
1343: AND user_id = p_user_id
1344: AND user_type = p_user_type
1345: AND lesson_status in ('P', 'C');
2236: p_user_id IN NUMBER,
2237: p_user_type IN ota_attempts.user_type%type,
2238: p_mode IN NUMBER default 1,
2239: p_active_cert_flag varchar2 default 'N',
2240: p_scorm_learning_attempt_id In ota_performances.scorm_learner_attempt_id%type default null)
2241: RETURN varchar2
2242: IS
2243: l_proc VARCHAR2(72) := g_package||'get_lo_title_for_tree';
2244: l_lo_name varchar(240);
2253: l_formatted_min varchar(20) := '';
2254: l_formatted_sec varchar(20) := '';
2255: l_formatted_time varchar(20) := '';
2256: l_return_tree_title varchar(500) := '';
2257: l_max_performance_id ota_performances.performance_id%type;
2258: l_tst_grade_flag ota_tests.grade_flag%TYPE;
2259: l_var_score VARCHAR2(100);
2260: l_lo_completed_date VARCHAR2(100);
2261: l_lo_completed_time VARCHAR2(10);
2259: l_var_score VARCHAR2(100);
2260: l_lo_completed_date VARCHAR2(100);
2261: l_lo_completed_time VARCHAR2(10);
2262: l_lo_completed_date_tz VARCHAR2(100);
2263: l_lo_lesson_status ota_performances.lesson_status%type;
2264: l_lo_completion_date_tz Date;
2265:
2266: CURSOR c_get_lo_tree_link(p_performance_id in number) is
2267: Select
2285: , to_char(opf.completed_date, 'HH24:MI:SS')
2286: , opf.lesson_status
2287: , 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
2288: ,ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
2289: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
2290: OTA_TESTS tst
2291: Where
2292: olo.learning_object_id = opf.learning_object_id(+)
2293: AND tst.test_id(+) = olo.test_id
2320: , to_char(opf.completed_date, 'HH24:MI:SS')
2321: , opf.lesson_status
2322: , 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
2323: ,ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
2324: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
2325: OTA_TESTS tst
2326: Where
2327: olo.learning_object_id = opf.learning_object_id(+)
2328: AND tst.test_id(+) = olo.test_id
2334:
2335:
2336: CURSOR c_max_performance_id_scorm is
2337: Select max(per.performance_id)
2338: From ota_performances per
2339: Where per.learning_object_id(+) = p_lo_id
2340: And per.user_id(+) = p_user_id
2341: And per.user_type(+) = p_user_type
2342: And per.scorm_learner_attempt_id = p_scorm_learning_attempt_id
2343: AND per.cert_prd_enrollment_id(+) is null;
2344:
2345: CURSOR c_max_performance_id is
2346: Select max(per.performance_id)
2347: From ota_performances per
2348: Where per.learning_object_id(+) = p_lo_id
2349: And per.user_id(+) = p_user_id
2350: And per.user_type(+) = p_user_type
2351: AND per.cert_prd_enrollment_id(+) is null;
3073: AND oev.event_id = p_event_id;
3074:
3075: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3076: SELECT lesson_status
3077: FROM ota_performances
3078: WHERE user_id = p_user_id
3079: AND user_type = p_user_type
3080: AND learning_object_id = l_lo_id
3081: AND cert_prd_enrollment_id is null;
3117: fetch c_odb_lo_status into l_status;
3118: close c_odb_lo_status;
3119: Else
3120: -- EBS created, Status code should be taken from
3121: -- OTA_PERFORMANCES
3122: open c_learning_object_status(l_learning_object_id);
3123: fetch c_learning_object_status into l_status;
3124: close c_learning_object_status;
3125:
3173: AND oev.event_id = p_event_id;
3174:
3175: CURSOR c_learning_object_time(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3176: SELECT TO_CHAR(TRUNC(SYSDATE)+(time)/86400, 'HH24:Mi:SS')
3177: FROM ota_performances
3178: WHERE user_id = p_user_id
3179: AND user_type = p_user_type
3180: AND learning_object_id = l_lo_id
3181: AND cert_prd_enrollment_id is null;
3217: fetch c_odb_lo_time into l_time;
3218: close c_odb_lo_time;
3219: Else
3220: -- EBS created, time should be taken from
3221: -- OTA_PERFORMANCES
3222: open c_learning_object_time(l_learning_object_id);
3223: fetch c_learning_object_time into l_time;
3224: close c_learning_object_time;
3225:
3273: AND oev.event_id = p_event_id;
3274:
3275: CURSOR c_learning_object_score(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3276: SELECT score
3277: FROM ota_performances
3278: WHERE user_id = p_user_id
3279: AND user_type = p_user_type
3280: AND learning_object_id = l_lo_id
3281: AND cert_prd_enrollment_id is null;
3317: fetch c_odb_lo_score into l_score;
3318: close c_odb_lo_score;
3319: Else
3320: -- EBS created, Score should be taken from
3321: -- OTA_PERFORMANCES
3322: open c_learning_object_score(l_learning_object_id);
3323: fetch c_learning_object_score into l_score;
3324: close c_learning_object_score;
3325:
3393:
3394: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3395: SELECT lesson_status,
3396: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3397: FROM ota_performances
3398: WHERE user_id = p_user_id
3399: AND user_type = p_user_type
3400: AND learning_object_id = l_lo_id
3401: AND cert_prd_enrollment_id is null;
3420: --modified for 14000818.
3421: cursor csr_noof_perf_recs (l_lo_id in ota_offerings.learning_object_id%type) is
3422: select count(p.PERFORMANCE_ID)
3423: from
3424: ota_performances p, ota_learning_objects lo
3425: where lo.learning_object_id = p.learning_object_id
3426: and p.user_id = p_user_id
3427: and p.user_type = p_user_type
3428: and lo.learning_object_id = l_lo_id
3429: and p.cert_prd_enrollment_id is null;
3430:
3431: --added for 14000818
3432: cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type,
3433: l_cert_prd_enr_id in ota_performances.cert_prd_enrollment_id%type) is
3434: select count(p.PERFORMANCE_ID)
3435: from
3436: ota_performances p, ota_learning_objects lo
3437: where lo.learning_object_id = p.learning_object_id
3432: cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type,
3433: l_cert_prd_enr_id in ota_performances.cert_prd_enrollment_id%type) is
3434: select count(p.PERFORMANCE_ID)
3435: from
3436: ota_performances p, ota_learning_objects lo
3437: where lo.learning_object_id = p.learning_object_id
3438: and p.user_id = p_user_id
3439: and p.user_type = p_user_type
3440: and lo.learning_object_id = l_lo_id
3449: 'B', '5',
3450: 'N', '6') decode_lesson_status,
3451: prf.lesson_status lesson_status,
3452: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS', prf.lesson_status) lesson_status_name
3453: FROM ota_performances prf
3454: WHERE
3455: prf.user_id = p_user_id
3456: and prf.user_type = p_user_type
3457: and prf.learning_object_id = l_lo_id
3528: fetch c_odb_lo_status into l_status, l_status_name;
3529: close c_odb_lo_status;
3530: Else
3531: -- EBS created, Status should be taken from
3532: -- OTA_PERFORMANCES
3533: --Commented by shwnayak for 13066773
3534: /* If ( l_enrollment_status_code = 'A' and p_mode = 2 ) Then
3535: -- p_mode = 2 means that coming from admin side
3536: open csr_best_prf(l_learning_object_id);
3694: WHERE event_id = p_event_id;
3695:
3696: CURSOR c_history_enabled IS
3697: SELECT lesson_status
3698: FROM ota_performances
3699: WHERE learning_object_id = p_lo_id
3700: AND user_id = p_user_id
3701: AND lesson_status IN ('P', 'C');
3702:
3742: FETCH c_history_enabled_odb INTO l_type;
3743: CLOSE c_history_enabled_odb;
3744: ELSE
3745: -- EBS created, Status should be taken from
3746: -- OTA_PERFORMANCES
3747: OPEN c_history_enabled;
3748: FETCH c_history_enabled INTO l_type;
3749: CLOSE c_history_enabled;
3750: END IF;
3805: --Added method get_lo_completion_date_time,similar to get_lo_completion_date but returns a date allowing UI sort
3806: FUNCTION get_lo_completion_date(p_event_id IN ota_events.event_id%type,
3807: p_user_id IN NUMBER,
3808: p_user_type IN ota_attempts.user_type%type,
3809: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3810: p_module_name IN VARCHAR2 default 'LEARNER')
3811: RETURN varchar2
3812: IS
3813: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date';
3816: -- l_lo_completed_date VARCHAR2(100);
3817: l_lo_completed_date DATE;
3818: l_lo_completed_time VARCHAR2(10);
3819: l_lo_completed_date_tz VARCHAR2(100);
3820: l_lo_lesson_status ota_performances.lesson_status%type;
3821: l_sync_flag ota_category_usages.synchronous_flag%type;
3822: l_online_flag ota_category_usages.online_flag%type;
3823: l_return_completion_date varchar(50) := '';
3824: l_decode_lesson_status VARCHAR2(1);
3838: 'N', '6') decode_lesson_status,
3839: ocu.Synchronous_Flag Sync_Flag,
3840: ocu.Online_Flag Online_Flag,
3841: 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
3842: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3843: Where
3844: oev.parent_offering_id = ofr.offering_id
3845: And ofr.learning_object_id = opf.learning_object_id(+)
3846: And oev.event_id = p_event_id
3858: opf.lesson_status,
3859: ocu.Synchronous_Flag Sync_Flag,
3860: ocu.Online_Flag Online_Flag,
3861: 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
3862: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3863: Where
3864: oev.parent_offering_id = ofr.offering_id
3865: And ofr.learning_object_id = opf.learning_object_id(+)
3866: And oev.event_id = p_event_id
3912:
3913: FUNCTION get_lo_completion_date_time(p_event_id IN ota_events.event_id%type,
3914: p_user_id IN NUMBER,
3915: p_user_type IN ota_attempts.user_type%type,
3916: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3917: p_module_name IN VARCHAR2 default 'LEARNER')
3918: RETURN date
3919: IS
3920: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date_time';
3923: l_lo_completed_date DATE;
3924: l_lo_comp_date DATE;
3925: l_lo_completed_time VARCHAR2(10);
3926: l_lo_completed_date_tz VARCHAR2(100);
3927: l_lo_lesson_status ota_performances.lesson_status%type;
3928: l_sync_flag ota_category_usages.synchronous_flag%type;
3929: l_online_flag ota_category_usages.online_flag%type;
3930: l_return_completion_date date:= null;
3931: l_decode_lesson_status VARCHAR2(1);
3946: ocu.Synchronous_Flag Sync_Flag,
3947: ocu.Online_Flag Online_Flag,
3948: 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,
3949: 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
3950: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3951: Where
3952: oev.parent_offering_id = ofr.offering_id
3953: And ofr.learning_object_id = opf.learning_object_id(+)
3954: And oev.event_id = p_event_id
3967: ocu.Synchronous_Flag Sync_Flag,
3968: ocu.Online_Flag Online_Flag,
3969: 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,
3970: 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
3971: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3972: Where
3973: oev.parent_offering_id = ofr.offering_id
3974: And ofr.learning_object_id = opf.learning_object_id(+)
3975: And oev.event_id = p_event_id
4067:
4068: CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
4069: SELECT lesson_status,
4070: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4071: FROM ota_performances
4072: WHERE user_id = p_user_id
4073: AND user_type = p_user_type
4074: AND learning_object_id = l_lo_id
4075: AND cert_prd_enrollment_id = p_cert_prd_enrollment_id;
4097:
4098: -- Check for LO status for online classes
4099: If ( l_online_flag = 'Y' ) Then
4100:
4101: -- OTA_PERFORMANCES for CERT_PRD_ENROLLMENT_ID
4102: open c_prd_lo_status(l_learning_object_id);
4103: fetch c_prd_lo_status into l_status, l_status_name;
4104: close c_prd_lo_status;
4105:
4171:
4172: FUNCTION get_cert_lo_title_for_tree(p_lo_id IN NUMBER,
4173: p_user_id IN NUMBER,
4174: p_user_type IN ota_attempts.user_type%type,
4175: p_cert_prd_enrollment_id IN ota_performances.cert_prd_enrollment_id%type,
4176: p_mode IN NUMBER default 1,
4177: p_scormattempid IN ota_performances.scorm_learner_attempt_id%type default null)
4178: RETURN varchar2
4179: IS
4173: p_user_id IN NUMBER,
4174: p_user_type IN ota_attempts.user_type%type,
4175: p_cert_prd_enrollment_id IN ota_performances.cert_prd_enrollment_id%type,
4176: p_mode IN NUMBER default 1,
4177: p_scormattempid IN ota_performances.scorm_learner_attempt_id%type default null)
4178: RETURN varchar2
4179: IS
4180: l_proc VARCHAR2(72) := g_package||'get_cert_lo_title_for_tree';
4181: l_lo_name varchar(240);
4190: l_formatted_min varchar(20) := '';
4191: l_formatted_sec varchar(20) := '';
4192: l_formatted_time varchar(20) := '';
4193: l_return_tree_title varchar(500) := '';
4194: l_max_performance_id ota_performances.performance_id%type;
4195: l_tst_grade_flag ota_tests.grade_flag%TYPE;
4196: l_var_score VARCHAR2(100);
4197: l_lo_completed_date VARCHAR2(100);
4198: l_lo_completed_time VARCHAR2(10);
4196: l_var_score VARCHAR2(100);
4197: l_lo_completed_date VARCHAR2(100);
4198: l_lo_completed_time VARCHAR2(10);
4199: l_lo_completed_date_tz VARCHAR2(100);
4200: l_lo_lesson_status ota_performances.lesson_status%type;
4201: l_lo_completion_date_tz Date;
4202:
4203: CURSOR c_get_cert_lo_tree_link(p_performance_id in number) is
4204: Select
4222: , to_char(opf.completed_date, 'HH24:MI:SS')
4223: , opf.lesson_status
4224: , 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
4225: , ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
4226: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
4227: OTA_TESTS tst
4228: Where
4229: olo.learning_object_id = opf.learning_object_id(+)
4230: AND tst.test_id(+) = olo.test_id
4256: , to_char(opf.completed_date, 'HH24:MI:SS')
4257: , opf.lesson_status
4258: , 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
4259: , ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
4260: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
4261: OTA_TESTS tst
4262: Where
4263: olo.learning_object_id = opf.learning_object_id(+)
4264: AND tst.test_id(+) = olo.test_id
4270: And opf.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4271:
4272: CURSOR c_max_performance_id_scorm is
4273: Select max(per.performance_id)
4274: From ota_performances per
4275: Where per.learning_object_id(+) = p_lo_id
4276: And per.user_id(+) = p_user_id
4277: And per.user_type(+) = p_user_type
4278: And per.scorm_learner_attempt_id = p_scormattempid
4279: And per.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4280:
4281: CURSOR c_max_performance_id is
4282: Select max(per.performance_id)
4283: From ota_performances per
4284: Where per.learning_object_id(+) = p_lo_id
4285: And per.user_id(+) = p_user_id
4286: And per.user_type(+) = p_user_type
4287: And per.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4673: CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE,
4674: csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4675: SELECT lesson_status,
4676: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4677: FROM ota_performances
4678: WHERE user_id = p_user_id
4679: AND user_type = p_user_type
4680: AND learning_object_id = l_lo_id
4681: AND cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
4701: 'B', '5',
4702: 'N', '6') decode_lesson_status,
4703: prf.lesson_status lesson_status,
4704: prf.performance_id
4705: FROM ota_performances prf,
4706: ota_offerings ofr,
4707: ota_learning_objects lo
4708: WHERE
4709: prf.user_id = p_user_id
4950: -- Access Status
4951: -- Public
4952: -- {End of Comments}
4953: ------------------------------------------------------------------
4954: Function format_lo_time(pTime ota_performances.time%type)
4955: return varchar2 IS
4956:
4957: l_proc VARCHAR2(72) := g_package||'format_lo_time';
4958: l_lo_time number;
5251: 'B', '5',
5252: 'N', '6') decode_lesson_status,
5253: prf.lesson_status lesson_status,
5254: prf.performance_id
5255: FROM ota_performances prf,
5256: ota_offerings ofr,
5257: ota_learning_objects lo
5258: WHERE
5259: prf.user_id = p_user_id
5652:
5653: cursor csr_noof_perf_recs (l_lo_id in ota_offerings.learning_object_id%type) is
5654: select count(p.PERFORMANCE_ID)
5655: from
5656: ota_performances p, ota_learning_objects lo
5657: where lo.learning_object_id = p.learning_object_id
5658: and p.user_id = p_user_id
5659: and p.user_type = p_user_type
5660: and lo.learning_object_id = l_lo_id
5659: and p.user_type = p_user_type
5660: and lo.learning_object_id = l_lo_id
5661: and p.cert_prd_enrollment_id is null;
5662:
5663: cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type, l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type) is
5664: select count(p.PERFORMANCE_ID)
5665: from
5666: ota_performances p, ota_learning_objects lo
5667: where lo.learning_object_id = p.learning_object_id
5662:
5663: cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type, l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type) is
5664: select count(p.PERFORMANCE_ID)
5665: from
5666: ota_performances p, ota_learning_objects lo
5667: where lo.learning_object_id = p.learning_object_id
5668: and p.user_id = p_user_id
5669: and p.user_type = p_user_type
5670: and lo.learning_object_id = l_lo_id
5679: l_sysdate ota_events.course_end_date%TYPE;
5680:
5681: l_trackingtype ota_learning_objects.tracking_type %TYPE;
5682: l_perf_rec_count INTEGER := 0;
5683: l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type;
5684: l_person_id ota_cert_enrollments.person_id%type := null;
5685: l_contact_id ota_cert_enrollments.contact_id%type := null;
5686: BEGIN
5687: