[Home] [Help]
5:
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:
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,
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
65:
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
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
214: --Added for updating the enrollment status to 'Attended'
215: --for the learners who have completed the mandatory evaluation.
216: procedure update_enrollment_status(
217: p_user_id fnd_user.user_id%type,
218: p_user_type ota_attempts.user_type%type,
219: p_event_id ota_events.event_id%type) is
220:
221: -- This cursor finds all events in all offerings which offer the LO and in
222: -- which the person is enrolled with a status of 'PENDING EVALUATION'.
331:
332: procedure update_enroll_status_for_lo(
333: p_lo_id ota_learning_objects.learning_object_id%type,
334: p_user_id fnd_user.user_id%type,
335: p_user_type ota_attempts.user_type%type,
336: p_date date) is
337:
338:
339:
350:
351: procedure update_enroll_status_for_lo(
352: p_lo_id ota_learning_objects.learning_object_id%type,
353: p_user_id fnd_user.user_id%type,
354: p_user_type ota_attempts.user_type%type,
355: p_date date,
356: p_failed varchar2) is
357:
358: -- This cursor finds all events in all offerings which offer the LO and in
426:
427: l_event_id OTA_EVENTS.EVENT_ID%TYPE:= null;
428: l_test_id OTA_TESTS.TEST_ID%type:=null;
429: Cursor csr_attempt_info is
430: select attempt_id from ota_attempts where
431: event_id = l_event_id
432: and test_id = l_test_id
433: and user_id = p_user_id
434: and user_type = p_user_type;
452: l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
453: l_act_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
454: l_evt_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
455: l_eval_mand_flag varchar2(1);
456: l_dummy ota_attempts.attempt_id%type;
457: l_is_attempted boolean :=false;
458: l_status varchar2(80) :='ATTENDED';
459: l_user_type ota_attempts.user_type%type;
460:
455: l_eval_mand_flag varchar2(1);
456: l_dummy ota_attempts.attempt_id%type;
457: l_is_attempted boolean :=false;
458: l_status varchar2(80) :='ATTENDED';
459: l_user_type ota_attempts.user_type%type;
460:
461: begin
462: hr_utility.set_location('Entering:'|| l_proc, 10);
463: if p_user_type = 'E' then
601:
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:
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
765:
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
777:
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);
788:
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:
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
813:
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
906:
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
918:
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);
928:
929:
930: function lo_is_attemptable(
931: p_lo_id ota_learning_objects.learning_object_id%type,
932: p_launch_type ota_attempts.launch_type%type,
933: p_reason out nocopy number) return boolean is
934:
935: v_published_flag ota_learning_objects.published_flag%type;
936: v_starting_url ota_learning_objects.starting_url%type;
998:
999: function user_meets_prerequisites(
1000: p_lo_id ota_learning_objects.learning_object_id%type,
1001: p_user_id fnd_user.user_id%type,
1002: p_user_type ota_attempts.user_type%type) return boolean is
1003:
1004: --Commented for Bug#3582893
1005: -- The cursor below is replaced with two cursor since the below query causes a Full Table Scan
1006: /*
1062:
1063: function user_exceeded_attempt_limit(
1064: p_lo_id ota_learning_objects.learning_object_id%type,
1065: p_user_id fnd_user.user_id%type,
1066: p_user_type ota_attempts.user_type%type,
1067: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1068:
1069: v_max_attempts ota_tests.max_attempts%type;
1070: v_user_attempts number;
1063: function user_exceeded_attempt_limit(
1064: p_lo_id ota_learning_objects.learning_object_id%type,
1065: p_user_id fnd_user.user_id%type,
1066: p_user_type ota_attempts.user_type%type,
1067: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1068:
1069: v_max_attempts ota_tests.max_attempts%type;
1070: v_user_attempts number;
1071: begin
1087: -- Count the user's attempts
1088: begin
1089: select count(*)
1090: into v_user_attempts
1091: from ota_attempts a, ota_tests t
1092: where a.user_id = p_user_id and
1093: a.user_type = p_user_type and
1094: a.learning_object_id = p_lo_id and
1095: nvl(a.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enrollment_id, -1) and
1114:
1115: function get_next_attempt_date_for_lo(
1116: p_lo_id ota_learning_objects.learning_object_id%type,
1117: p_user_id fnd_user.user_id%type,
1118: p_user_type ota_attempts.user_type%type,
1119: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return date is
1120:
1121: v_earliest_attempt_date date;
1122: begin
1115: function get_next_attempt_date_for_lo(
1116: p_lo_id ota_learning_objects.learning_object_id%type,
1117: p_user_id fnd_user.user_id%type,
1118: p_user_type ota_attempts.user_type%type,
1119: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return date is
1120:
1121: v_earliest_attempt_date date;
1122: begin
1123: select nvl(max(a.timestamp), sysdate) + nvl(t.duration_between_attempt, 0)
1123: select nvl(max(a.timestamp), sysdate) + nvl(t.duration_between_attempt, 0)
1124: into v_earliest_attempt_date
1125: from ota_tests t,
1126: ota_learning_objects lo,
1127: ota_attempts a
1128: where t.test_id = a.test_id
1129: and a.initialized_flag = 'Y'
1130: and ((t.resume_flag = 'Y'
1131: and a.internal_state <> 'A'
1151:
1152: function user_must_wait_to_attempt(
1153: p_lo_id ota_learning_objects.learning_object_id%type,
1154: p_user_id fnd_user.user_id%type,
1155: p_user_type ota_attempts.user_type%type,
1156: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1157:
1158: v_earliest_attempt_date date;
1159: begin
1152: function user_must_wait_to_attempt(
1153: p_lo_id ota_learning_objects.learning_object_id%type,
1154: p_user_id fnd_user.user_id%type,
1155: p_user_type ota_attempts.user_type%type,
1156: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1157:
1158: v_earliest_attempt_date date;
1159: begin
1160: v_earliest_attempt_date := get_next_attempt_date_for_lo(
1169:
1170: function user_can_attempt_lo(
1171: p_lo_id ota_learning_objects.learning_object_id%type,
1172: p_user_id fnd_user.user_id%type,
1173: p_user_type ota_attempts.user_type%type,
1174: p_launch_type ota_attempts.launch_type%type,
1175: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1176: p_reason out nocopy number) return boolean is
1177: begin
1170: function user_can_attempt_lo(
1171: p_lo_id ota_learning_objects.learning_object_id%type,
1172: p_user_id fnd_user.user_id%type,
1173: p_user_type ota_attempts.user_type%type,
1174: p_launch_type ota_attempts.launch_type%type,
1175: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1176: p_reason out nocopy number) return boolean is
1177: begin
1178: if not lo_is_attemptable(p_lo_id, p_launch_type, p_reason) then
1171: p_lo_id ota_learning_objects.learning_object_id%type,
1172: p_user_id fnd_user.user_id%type,
1173: p_user_type ota_attempts.user_type%type,
1174: p_launch_type ota_attempts.launch_type%type,
1175: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1176: p_reason out nocopy number) return boolean is
1177: begin
1178: if not lo_is_attemptable(p_lo_id, p_launch_type, p_reason) then
1179: return false;
1208:
1209: function user_can_attempt_lo(
1210: p_lo_id ota_learning_objects.learning_object_id%type,
1211: p_user_id fnd_user.user_id%type,
1212: p_user_type ota_attempts.user_type%type,
1213: p_launch_type ota_attempts.launch_type%type default '',
1214: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
1215:
1216: l_reason number;
1209: function user_can_attempt_lo(
1210: p_lo_id ota_learning_objects.learning_object_id%type,
1211: p_user_id fnd_user.user_id%type,
1212: p_user_type ota_attempts.user_type%type,
1213: p_launch_type ota_attempts.launch_type%type default '',
1214: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
1215:
1216: l_reason number;
1217: begin
1210: p_lo_id ota_learning_objects.learning_object_id%type,
1211: p_user_id fnd_user.user_id%type,
1212: p_user_type ota_attempts.user_type%type,
1213: p_launch_type ota_attempts.launch_type%type default '',
1214: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
1215:
1216: l_reason number;
1217: begin
1218: if user_can_attempt_lo(p_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id, l_reason) then
1226: function user_can_attempt_lo_3(
1227: p_lo_id ota_learning_objects.learning_object_id%type,
1228: p_event_id ota_events.event_id%type,
1229: p_user_id fnd_user.user_id%type,
1230: p_user_type ota_attempts.user_type%type,
1231: p_launch_type ota_attempts.launch_type%type,
1232: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1233: p_reason out nocopy number) return boolean is
1234: begin
1227: p_lo_id ota_learning_objects.learning_object_id%type,
1228: p_event_id ota_events.event_id%type,
1229: p_user_id fnd_user.user_id%type,
1230: p_user_type ota_attempts.user_type%type,
1231: p_launch_type ota_attempts.launch_type%type,
1232: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1233: p_reason out nocopy number) return boolean is
1234: begin
1235: if not lo_is_in_event(p_event_id, p_lo_id) then
1228: p_event_id ota_events.event_id%type,
1229: p_user_id fnd_user.user_id%type,
1230: p_user_type ota_attempts.user_type%type,
1231: p_launch_type ota_attempts.launch_type%type,
1232: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1233: p_reason out nocopy number) return boolean is
1234: begin
1235: if not lo_is_in_event(p_event_id, p_lo_id) then
1236: p_reason := LO_REASON_NOT_IN_EVENT;
1278: function user_can_attempt_lo(
1279: p_lo_id ota_learning_objects.learning_object_id%type,
1280: p_event_id ota_events.event_id%type,
1281: p_user_id fnd_user.user_id%type,
1282: p_user_type ota_attempts.user_type%type,
1283: p_launch_type ota_attempts.launch_type%type,
1284: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1285: p_reason out nocopy number) return boolean is
1286: begin
1279: p_lo_id ota_learning_objects.learning_object_id%type,
1280: p_event_id ota_events.event_id%type,
1281: p_user_id fnd_user.user_id%type,
1282: p_user_type ota_attempts.user_type%type,
1283: p_launch_type ota_attempts.launch_type%type,
1284: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1285: p_reason out nocopy number) return boolean is
1286: begin
1287: if p_cert_prd_enroll_id is not null and not lo_is_in_cert(p_cert_prd_enroll_id, p_lo_id) then
1280: p_event_id ota_events.event_id%type,
1281: p_user_id fnd_user.user_id%type,
1282: p_user_type ota_attempts.user_type%type,
1283: p_launch_type ota_attempts.launch_type%type,
1284: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1285: p_reason out nocopy number) return boolean is
1286: begin
1287: if p_cert_prd_enroll_id is not null and not lo_is_in_cert(p_cert_prd_enroll_id, p_lo_id) then
1288: p_reason := LO_REASON_LO_NOT_IN_CERT;
1298:
1299: function event_is_attemptable(
1300: p_event_id ota_events.event_id%type,
1301: p_date date,
1302: p_launch_type ota_attempts.launch_type%type,
1303: p_reason out nocopy number) return boolean is
1304:
1305: v_course_start_date ota_events.course_start_date%type;
1306: v_course_start_time ota_events.course_start_time%type;
1341:
1342: function user_is_enrolled(
1343: p_event_id ota_events.event_id%type,
1344: p_user_id fnd_user.user_id%type,
1345: p_user_type ota_attempts.user_type%type,
1346: p_date date,
1347: p_reason out nocopy number) return boolean is
1348:
1349: cursor person_bookings(
1388:
1389: function user_is_instructor(
1390: p_event_id ota_events.event_id%type,
1391: p_user_id fnd_user.user_id%type,
1392: p_user_type ota_attempts.user_type%type,
1393: p_date date,
1394: p_reason out nocopy number) return boolean is
1395: begin
1396:
1428:
1429: function user_can_attempt_event(
1430: p_event_id ota_events.event_id%type,
1431: p_user_id fnd_user.user_id%type,
1432: p_user_type ota_attempts.user_type%type,
1433: p_launch_type ota_attempts.launch_type%type,
1434: p_reason out nocopy number) return varchar2 is
1435:
1436: v_now date := trunc(sysdate);--Bug 3554773
1429: function user_can_attempt_event(
1430: p_event_id ota_events.event_id%type,
1431: p_user_id fnd_user.user_id%type,
1432: p_user_type ota_attempts.user_type%type,
1433: p_launch_type ota_attempts.launch_type%type,
1434: p_reason out nocopy number) return varchar2 is
1435:
1436: v_now date := trunc(sysdate);--Bug 3554773
1437: begin
1492:
1493: function user_is_enrolled_in_cert(
1494: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1495: p_user_id fnd_user.user_id%type,
1496: p_user_type ota_attempts.user_type%type,
1497: p_date date,
1498: p_reason out nocopy number) return boolean is
1499:
1500: v_cert_unsubscribe_date ota_cert_enrollments.unenrollment_date%type;
1532:
1533: function user_can_attempt_cert(
1534: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1535: p_user_id fnd_user.user_id%type,
1536: p_user_type ota_attempts.user_type%type,
1537: p_reason out nocopy number) return varchar2 is
1538:
1539: v_now date := trunc(sysdate);--Bug 3554773
1540: begin
1586: function get_previous_lo_id(
1587: p_root_lo_id ota_learning_objects.learning_object_id%type,
1588: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1589: p_user_id fnd_user.user_id%type,
1590: p_user_type ota_attempts.user_type%type,
1591: p_launch_type ota_attempts.launch_type%type,
1592: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1593:
1594: v_starting_url ota_learning_objects.starting_url%type :=null;
1587: p_root_lo_id ota_learning_objects.learning_object_id%type,
1588: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1589: p_user_id fnd_user.user_id%type,
1590: p_user_type ota_attempts.user_type%type,
1591: p_launch_type ota_attempts.launch_type%type,
1592: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1593:
1594: v_starting_url ota_learning_objects.starting_url%type :=null;
1595: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1588: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1589: p_user_id fnd_user.user_id%type,
1590: p_user_type ota_attempts.user_type%type,
1591: p_launch_type ota_attempts.launch_type%type,
1592: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1593:
1594: v_starting_url ota_learning_objects.starting_url%type :=null;
1595: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1596: v_reason number;
1615: function get_previous_event_lo_id(
1616: p_event_id ota_events.event_id%type,
1617: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1618: p_user_id fnd_user.user_id%type,
1619: p_user_type ota_attempts.user_type%type,
1620: p_launch_type ota_attempts.launch_type%type,
1621: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1622:
1623: v_root_lo_id ota_learning_objects.learning_object_id%type;
1616: p_event_id ota_events.event_id%type,
1617: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1618: p_user_id fnd_user.user_id%type,
1619: p_user_type ota_attempts.user_type%type,
1620: p_launch_type ota_attempts.launch_type%type,
1621: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1622:
1623: v_root_lo_id ota_learning_objects.learning_object_id%type;
1624: begin
1617: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1618: p_user_id fnd_user.user_id%type,
1619: p_user_type ota_attempts.user_type%type,
1620: p_launch_type ota_attempts.launch_type%type,
1621: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1622:
1623: v_root_lo_id ota_learning_objects.learning_object_id%type;
1624: begin
1625: select o.learning_object_id
1668: function get_next_lo_id(
1669: p_root_lo_id ota_learning_objects.learning_object_id%type,
1670: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1671: p_user_id fnd_user.user_id%type,
1672: p_user_type ota_attempts.user_type%type,
1673: p_launch_type ota_attempts.launch_type%type,
1674: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1675:
1676:
1669: p_root_lo_id ota_learning_objects.learning_object_id%type,
1670: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1671: p_user_id fnd_user.user_id%type,
1672: p_user_type ota_attempts.user_type%type,
1673: p_launch_type ota_attempts.launch_type%type,
1674: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1675:
1676:
1677: v_starting_url ota_learning_objects.starting_url%type := null;
1670: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1671: p_user_id fnd_user.user_id%type,
1672: p_user_type ota_attempts.user_type%type,
1673: p_launch_type ota_attempts.launch_type%type,
1674: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1675:
1676:
1677: v_starting_url ota_learning_objects.starting_url%type := null;
1678: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1688: p_root_lo_id ota_learning_objects.learning_object_id%type,
1689: p_root_starting_url ota_learning_objects.starting_url%type,
1690: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1691: p_user_id fnd_user.user_id%type,
1692: p_user_type ota_attempts.user_type%type,
1693: p_launch_type ota_attempts.launch_type%type,
1694: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1695:
1696: v_starting_url ota_learning_objects.starting_url%type := null;
1689: p_root_starting_url ota_learning_objects.starting_url%type,
1690: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1691: p_user_id fnd_user.user_id%type,
1692: p_user_type ota_attempts.user_type%type,
1693: p_launch_type ota_attempts.launch_type%type,
1694: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1695:
1696: v_starting_url ota_learning_objects.starting_url%type := null;
1697: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1690: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1691: p_user_id fnd_user.user_id%type,
1692: p_user_type ota_attempts.user_type%type,
1693: p_launch_type ota_attempts.launch_type%type,
1694: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1695:
1696: v_starting_url ota_learning_objects.starting_url%type := null;
1697: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1698: v_false boolean := false;
1724: function get_next_event_lo_id(
1725: p_event_id ota_events.event_id%type,
1726: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1727: p_user_id fnd_user.user_id%type,
1728: p_user_type ota_attempts.user_type%type,
1729: p_launch_type ota_attempts.launch_type%type,
1730: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1731:
1732: v_root_lo_id ota_learning_objects.learning_object_id%type;
1725: p_event_id ota_events.event_id%type,
1726: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1727: p_user_id fnd_user.user_id%type,
1728: p_user_type ota_attempts.user_type%type,
1729: p_launch_type ota_attempts.launch_type%type,
1730: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1731:
1732: v_root_lo_id ota_learning_objects.learning_object_id%type;
1733: begin
1726: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1727: p_user_id fnd_user.user_id%type,
1728: p_user_type ota_attempts.user_type%type,
1729: p_launch_type ota_attempts.launch_type%type,
1730: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1731:
1732: v_root_lo_id ota_learning_objects.learning_object_id%type;
1733: begin
1734: select o.learning_object_id
1743:
1744: function get_most_recent_lo_id(
1745: p_event_id ota_events.event_id%type,
1746: p_user_id fnd_user.user_id%type,
1747: p_user_type ota_attempts.user_type%type,
1748: p_launch_type ota_attempts.launch_type%type,
1749: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
1750:
1751: cursor ordered_los(
1744: function get_most_recent_lo_id(
1745: p_event_id ota_events.event_id%type,
1746: p_user_id fnd_user.user_id%type,
1747: p_user_type ota_attempts.user_type%type,
1748: p_launch_type ota_attempts.launch_type%type,
1749: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
1750:
1751: cursor ordered_los(
1752: p_root_lo_id ota_learning_objects.learning_object_id%type,
1745: p_event_id ota_events.event_id%type,
1746: p_user_id fnd_user.user_id%type,
1747: p_user_type ota_attempts.user_type%type,
1748: p_launch_type ota_attempts.launch_type%type,
1749: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
1750:
1751: cursor ordered_los(
1752: p_root_lo_id ota_learning_objects.learning_object_id%type,
1753: p_user_id fnd_user.user_id%type,
1750:
1751: cursor ordered_los(
1752: p_root_lo_id ota_learning_objects.learning_object_id%type,
1753: p_user_id fnd_user.user_id%type,
1754: p_user_type ota_attempts.user_type%type,
1755: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
1756: select a.learning_object_id, max(a.attempt_id) max_attempt
1757: from ota_attempts a,
1758: (select learning_object_id, starting_url
1751: cursor ordered_los(
1752: p_root_lo_id ota_learning_objects.learning_object_id%type,
1753: p_user_id fnd_user.user_id%type,
1754: p_user_type ota_attempts.user_type%type,
1755: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
1756: select a.learning_object_id, max(a.attempt_id) max_attempt
1757: from ota_attempts a,
1758: (select learning_object_id, starting_url
1759: from ota_learning_objects
1753: p_user_id fnd_user.user_id%type,
1754: p_user_type ota_attempts.user_type%type,
1755: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
1756: select a.learning_object_id, max(a.attempt_id) max_attempt
1757: from ota_attempts a,
1758: (select learning_object_id, starting_url
1759: from ota_learning_objects
1760: start with learning_object_id = p_root_lo_id
1761: connect by parent_learning_object_id = prior learning_object_id) lo
1791:
1792: function get_first_lo_id(
1793: p_event_id ota_events.event_id%type,
1794: p_user_id fnd_user.user_id%type,
1795: p_user_type ota_attempts.user_type%type,
1796: p_launch_type ota_attempts.launch_type%type,
1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1798:
1799: v_root_lo_id ota_learning_objects.learning_object_id%type;
1792: function get_first_lo_id(
1793: p_event_id ota_events.event_id%type,
1794: p_user_id fnd_user.user_id%type,
1795: p_user_type ota_attempts.user_type%type,
1796: p_launch_type ota_attempts.launch_type%type,
1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1798:
1799: v_root_lo_id ota_learning_objects.learning_object_id%type;
1800: v_first_lo_id ota_learning_objects.learning_object_id%type;
1793: p_event_id ota_events.event_id%type,
1794: p_user_id fnd_user.user_id%type,
1795: p_user_type ota_attempts.user_type%type,
1796: p_launch_type ota_attempts.launch_type%type,
1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1798:
1799: v_root_lo_id ota_learning_objects.learning_object_id%type;
1800: v_first_lo_id ota_learning_objects.learning_object_id%type;
1801: v_reason number;
1812: function get_jump_lo_id(
1813: p_lo_id ota_learning_objects.learning_object_id%type,
1814: p_event_id ota_events.event_id%type,
1815: p_user_id fnd_user.user_id%type,
1816: p_user_type ota_attempts.user_type%type,
1817: p_launch_type ota_attempts.launch_type%type,
1818: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1819: begin
1820: return get_jump_lo_id(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, null, p_reason);
1813: p_lo_id ota_learning_objects.learning_object_id%type,
1814: p_event_id ota_events.event_id%type,
1815: p_user_id fnd_user.user_id%type,
1816: p_user_type ota_attempts.user_type%type,
1817: p_launch_type ota_attempts.launch_type%type,
1818: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1819: begin
1820: return get_jump_lo_id(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, null, p_reason);
1821: end get_jump_lo_id;
1823: function get_jump_lo_id(
1824: p_lo_id ota_learning_objects.learning_object_id%type,
1825: p_event_id ota_events.event_id%type,
1826: p_user_id fnd_user.user_id%type,
1827: p_user_type ota_attempts.user_type%type,
1828: p_launch_type ota_attempts.launch_type%type,
1829: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1830: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1831: begin
1824: p_lo_id ota_learning_objects.learning_object_id%type,
1825: p_event_id ota_events.event_id%type,
1826: p_user_id fnd_user.user_id%type,
1827: p_user_type ota_attempts.user_type%type,
1828: p_launch_type ota_attempts.launch_type%type,
1829: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1830: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1831: begin
1832: if user_can_attempt_lo(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id, p_reason) then
1825: p_event_id ota_events.event_id%type,
1826: p_user_id fnd_user.user_id%type,
1827: p_user_type ota_attempts.user_type%type,
1828: p_launch_type ota_attempts.launch_type%type,
1829: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1830: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1831: begin
1832: if user_can_attempt_lo(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id, p_reason) then
1833: return p_lo_id;
1863:
1864: function user_can_attempt_event(
1865: p_event_id ota_events.event_id%type,
1866: p_user_id fnd_user.user_id%type,
1867: p_user_type ota_attempts.user_type%type) return varchar2 is
1868:
1869: l_reason number;
1870: begin
1871: return user_can_attempt_event(p_event_id, p_user_id, p_user_type, '', l_reason);
1874:
1875: function user_can_attempt_event(
1876: p_event_id ota_events.event_id%type,
1877: p_user_id fnd_user.user_id%type,
1878: p_user_type ota_attempts.user_type%type,
1879: p_launch_type ota_attempts.launch_type%type) return varchar2 is
1880:
1881: l_reason number;
1882: begin
1875: function user_can_attempt_event(
1876: p_event_id ota_events.event_id%type,
1877: p_user_id fnd_user.user_id%type,
1878: p_user_type ota_attempts.user_type%type,
1879: p_launch_type ota_attempts.launch_type%type) return varchar2 is
1880:
1881: l_reason number;
1882: begin
1883: return user_can_attempt_event(p_event_id, p_user_id, p_user_type, p_launch_type, l_reason);
1909: -- {End of Comments}
1910: ------------------------------------------------------------------
1911: FUNCTION get_lo_title_for_tree(p_lo_id IN NUMBER,
1912: p_user_id IN NUMBER,
1913: p_user_type IN ota_attempts.user_type%type,
1914: p_mode IN NUMBER default 1,
1915: p_active_cert_flag varchar2 default 'N')
1916: RETURN varchar2
1917: IS
2077: End get_lo_title_for_tree;
2078:
2079: function get_play_button(
2080: p_user_id fnd_user.user_id%type,
2081: p_user_type ota_attempts.user_type%type,
2082: p_is_manager varchar2,
2083: p_event_id ota_events.event_id%type,
2084: p_event_type ota_events.event_type%type,
2085: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2086: p_online_flag ota_category_usages.online_flag%type,
2087: p_course_start_date ota_events.course_start_date%type,
2088: p_course_end_date ota_events.course_end_date%type,
2089: p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
2090: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
2091: p_contact_id ota_cert_enrollments.contact_id%type default null,
2092: p_chk_active_cert_flag varchar2 default 'N') return varchar2 is
2093:
2094:
2297:
2298:
2299: function get_play_button_for_test(
2300: p_user_id fnd_user.user_id%type,
2301: p_user_type ota_attempts.user_type%type,
2302: p_event_id ota_events.event_id%type,
2303: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2304:
2305: v_lo_id ota_learning_objects.learning_object_id%type;
2299: function get_play_button_for_test(
2300: p_user_id fnd_user.user_id%type,
2301: p_user_type ota_attempts.user_type%type,
2302: p_event_id ota_events.event_id%type,
2303: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2304:
2305: v_lo_id ota_learning_objects.learning_object_id%type;
2306: begin
2307: select offr.learning_object_id
2353: l_act_eval_id OTA_EVALUATIONS.evaluation_id%TYPE;
2354:
2355: CURSOR c_attempts_info is
2356: Select attempt_id
2357: From ota_attempts
2358: Where event_id = p_event_id
2359: and user_id = p_user_id
2360: and (act_eval_id = l_act_eval_id
2361: or test_id = p_test_id)
2457: l_act_eval_id OTA_EVALUATIONS.evaluation_id%TYPE;
2458:
2459: CURSOR c_attempts_info is
2460: Select attempt_id
2461: From ota_attempts
2462: Where event_id = p_event_id
2463: and user_id = p_user_id
2464: and (act_eval_id = l_act_eval_id
2465: or test_id = p_test_id)
2554: -- [End of Comments]
2555: -- ---------------------------------------------------------------------------
2556: function get_wait_duration_for_lo(
2557: p_user_id fnd_user.user_id%type,
2558: p_user_type ota_attempts.user_type%type,
2559: p_lo_id ota_learning_objects.learning_object_id%type,
2560: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2561:
2562: v_earliest_attempt_date date;
2556: function get_wait_duration_for_lo(
2557: p_user_id fnd_user.user_id%type,
2558: p_user_type ota_attempts.user_type%type,
2559: p_lo_id ota_learning_objects.learning_object_id%type,
2560: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2561:
2562: v_earliest_attempt_date date;
2563: begin
2564: v_earliest_attempt_date :=
2584: -- [End of Comments]
2585: -- ---------------------------------------------------------------------------
2586: function get_wait_duration_for_test(
2587: p_user_id fnd_user.user_id%type,
2588: p_user_type ota_attempts.user_type%type,
2589: p_event_id ota_events.event_id%type,
2590: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2591:
2592: v_lo_id ota_learning_objects.learning_object_id%type;
2586: function get_wait_duration_for_test(
2587: p_user_id fnd_user.user_id%type,
2588: p_user_type ota_attempts.user_type%type,
2589: p_event_id ota_events.event_id%type,
2590: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2591:
2592: v_lo_id ota_learning_objects.learning_object_id%type;
2593: v_wait_duration varchar2(80);
2594: --Bug 5166350
2695: -- {End Of Comments}
2696: ----------------------------------------------------------------------------
2697: --
2698: FUNCTION get_player_status(p_user_id IN NUMBER,
2699: p_user_type IN ota_attempts.user_type%type,
2700: p_event_id IN ota_events.event_id%TYPE,
2701: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
2702: RETURN VARCHAR2 IS
2703:
2795: -- {End Of Comments}
2796: ----------------------------------------------------------------------------
2797: --
2798: FUNCTION get_enroll_lo_time(p_user_id IN NUMBER,
2799: p_user_type IN ota_attempts.user_type%type,
2800: p_event_id IN ota_events.event_id%TYPE,
2801: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
2802: RETURN VARCHAR2 IS
2803:
2895: -- {End Of Comments}
2896: ----------------------------------------------------------------------------
2897: --
2898: FUNCTION get_enroll_lo_score(p_user_id IN NUMBER,
2899: p_user_type IN ota_attempts.user_type%type,
2900: p_event_id IN ota_events.event_id%TYPE,
2901: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
2902: RETURN VARCHAR2 IS
2903:
3001: -- {End Of Comments}
3002: ----------------------------------------------------------------------------
3003: --
3004: FUNCTION get_enroll_lo_status(p_user_id IN NUMBER,
3005: p_user_type IN ota_attempts.user_type%type,
3006: p_event_id IN ota_events.event_id%TYPE,
3007: p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
3008: p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
3009: p_mode IN number default null,
3356: ------------------------------------------------------------------
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
3448: --Similar to get_lo_completion_date,but the return type is date.Called from admin side
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
3547: ----------------------------------------------------------------------------
3548:
3549:
3550: FUNCTION get_cert_lo_status(p_user_id IN NUMBER,
3551: p_user_type IN ota_attempts.user_type%type,
3552: p_event_id IN ota_events.event_id%TYPE,
3553: p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
3554: p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
3555: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
3654: ------------------------------------------------------------------
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
3807: RETURN l_return_tree_title;
3808: End get_cert_lo_title_for_tree;
3809:
3810: function get_cme_online_event_id(p_user_id in fnd_user.user_id%type,
3811: p_user_type in ota_attempts.user_type%type,
3812: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
3813: return varchar2 is
3814: CURSOR csr_cme_info IS
3815: SELECT cme.cert_mbr_enrollment_id,
3948:
3949: End get_cme_online_event_id;
3950:
3951: function get_cme_play_button(p_user_id in fnd_user.user_id%type,
3952: p_user_type in ota_attempts.user_type%type,
3953: p_is_manager in varchar2,
3954: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
3955: return varchar2 is
3956:
4020: End get_cme_play_button;
4021:
4022:
4023: function get_cme_player_toolbar_flag(p_user_id in fnd_user.user_id%type,
4024: p_user_type in ota_attempts.user_type%type,
4025: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4026: return varchar2 is
4027:
4028: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4067:
4068: End get_cme_player_toolbar_flag;
4069:
4070: function get_cert_lo_status(p_user_id in fnd_user.user_id%type,
4071: p_user_type in ota_attempts.user_type%type,
4072: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4073: return varchar2 is
4074:
4075: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4223:
4224: End get_cert_lo_status;
4225:
4226: function get_cme_onl_evt_count(p_user_id in fnd_user.user_id%type,
4227: p_user_type in ota_attempts.user_type%type,
4228: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4229: return varchar2 is
4230: CURSOR csr_cme_info IS
4231: SELECT cme.cert_mbr_enrollment_id,
4527:
4528: End get_lme_online_event_id;
4529:
4530: function get_lme_play_button(p_user_id in fnd_user.user_id%type,
4531: p_user_type in ota_attempts.user_type%type,
4532: p_is_manager in varchar2,
4533: p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
4534: return varchar2 is
4535:
4634:
4635: End get_lme_player_toolbar_flag;
4636:
4637: function get_lpe_lo_status(p_user_id in fnd_user.user_id%type,
4638: p_user_type in ota_attempts.user_type%type,
4639: p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
4640: return varchar2 is
4641:
4642: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS