[Home] [Help]
66:
67:
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:
77: cursor child_los(
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
128:
129:
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,
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,
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
500:
501: --Added for updating the enrollment status to 'Attended'
502: --for the learners who have completed the mandatory evaluation.
503: procedure update_enrollment_status(
504: p_user_id fnd_user.user_id%type,
505: p_user_type ota_attempts.user_type%type,
506: p_event_id ota_events.event_id%type) is
507:
508: -- This cursor finds all events in all offerings which offer the LO and in
622:
623:
624: procedure update_enroll_status_for_lo(
625: p_lo_id ota_learning_objects.learning_object_id%type,
626: p_user_id fnd_user.user_id%type,
627: p_user_type ota_attempts.user_type%type,
628: p_date date) is
629:
630:
641: end update_enroll_status_for_lo;
642:
643: procedure update_enroll_status_for_lo(
644: p_lo_id ota_learning_objects.learning_object_id%type,
645: p_user_id fnd_user.user_id%type,
646: p_user_type ota_attempts.user_type%type,
647: p_date date,
648: p_failed varchar2) is
649:
868:
869:
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,
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,
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,
1043:
1044: --13485033
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,
1062:
1063:
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,
1075:
1076:
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
1087:
1088:
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,
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,
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
1114: nvl(perf.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
1115:
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,
1210:
1211:
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,
1223:
1224:
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
1304:
1305:
1306: function user_meets_prerequisites(
1307: p_lo_id ota_learning_objects.learning_object_id%type,
1308: p_user_id fnd_user.user_id%type,
1309: p_user_type ota_attempts.user_type%type) return boolean is
1310:
1311: --Commented for Bug#3582893
1312: -- The cursor below is replaced with two cursor since the below query causes a Full Table Scan
1368:
1369:
1370: function user_exceeded_attempt_limit(
1371: p_lo_id ota_learning_objects.learning_object_id%type,
1372: p_user_id fnd_user.user_id%type,
1373: p_user_type ota_attempts.user_type%type,
1374: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1375:
1376: v_max_attempts ota_tests.max_attempts%type;
1420:
1421:
1422: function get_next_attempt_date_for_lo(
1423: p_lo_id ota_learning_objects.learning_object_id%type,
1424: p_user_id fnd_user.user_id%type,
1425: p_user_type ota_attempts.user_type%type,
1426: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return date is
1427:
1428: v_earliest_attempt_date date;
1456:
1457:
1458: function user_must_wait_to_attempt(
1459: p_lo_id ota_learning_objects.learning_object_id%type,
1460: p_user_id fnd_user.user_id%type,
1461: p_user_type ota_attempts.user_type%type,
1462: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1463:
1464: v_earliest_attempt_date date;
1474:
1475:
1476: function user_can_attempt_lo(
1477: p_lo_id ota_learning_objects.learning_object_id%type,
1478: p_user_id fnd_user.user_id%type,
1479: p_user_type ota_attempts.user_type%type,
1480: p_launch_type ota_attempts.launch_type%type,
1481: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1482: p_reason out nocopy number) return boolean is
1513:
1514:
1515: function user_can_attempt_lo(
1516: p_lo_id ota_learning_objects.learning_object_id%type,
1517: p_user_id fnd_user.user_id%type,
1518: p_user_type ota_attempts.user_type%type,
1519: p_launch_type ota_attempts.launch_type%type default '',
1520: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
1521:
1531:
1532: function user_can_attempt_lo_3(
1533: p_lo_id ota_learning_objects.learning_object_id%type,
1534: p_event_id ota_events.event_id%type,
1535: p_user_id fnd_user.user_id%type,
1536: p_user_type ota_attempts.user_type%type,
1537: p_launch_type ota_attempts.launch_type%type,
1538: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1539: p_reason out nocopy number) return boolean is
1583:
1584: function user_can_attempt_lo(
1585: p_lo_id ota_learning_objects.learning_object_id%type,
1586: p_event_id ota_events.event_id%type,
1587: p_user_id fnd_user.user_id%type,
1588: p_user_type ota_attempts.user_type%type,
1589: p_launch_type ota_attempts.launch_type%type,
1590: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1591: p_reason out nocopy number) return boolean is
1646:
1647:
1648: function user_is_enrolled(
1649: p_event_id ota_events.event_id%type,
1650: p_user_id fnd_user.user_id%type,
1651: p_user_type ota_attempts.user_type%type,
1652: p_date date,
1653: p_reason out nocopy number) return boolean is
1654:
1711:
1712:
1713: function user_is_instructor(
1714: p_event_id ota_events.event_id%type,
1715: p_user_id fnd_user.user_id%type,
1716: p_user_type ota_attempts.user_type%type,
1717: p_date date,
1718: p_reason out nocopy number) return boolean is
1719: begin
1751:
1752:
1753: function user_can_attempt_event(
1754: p_event_id ota_events.event_id%type,
1755: p_user_id fnd_user.user_id%type,
1756: p_user_type ota_attempts.user_type%type,
1757: p_launch_type ota_attempts.launch_type%type,
1758: p_reason out nocopy number) return varchar2 is
1759:
1815: end cert_is_attemptable;
1816:
1817: function user_is_enrolled_in_cert(
1818: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1819: p_user_id fnd_user.user_id%type,
1820: p_user_type ota_attempts.user_type%type,
1821: p_date date,
1822: p_reason out nocopy number) return boolean is
1823:
1855: end user_is_enrolled_in_cert;
1856:
1857: function user_can_attempt_cert(
1858: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1859: p_user_id fnd_user.user_id%type,
1860: p_user_type ota_attempts.user_type%type,
1861: p_reason out nocopy number) return varchar2 is
1862:
1863: v_now date := trunc(sysdate);--Bug 3554773
1909:
1910: function get_previous_lo_id(
1911: p_root_lo_id ota_learning_objects.learning_object_id%type,
1912: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1913: p_user_id fnd_user.user_id%type,
1914: p_user_type ota_attempts.user_type%type,
1915: p_launch_type ota_attempts.launch_type%type,
1916: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1917:
1938:
1939: function get_previous_event_lo_id(
1940: p_event_id ota_events.event_id%type,
1941: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1942: p_user_id fnd_user.user_id%type,
1943: p_user_type ota_attempts.user_type%type,
1944: p_launch_type ota_attempts.launch_type%type,
1945: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1946:
1991:
1992: function get_next_lo_id(
1993: p_root_lo_id ota_learning_objects.learning_object_id%type,
1994: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1995: p_user_id fnd_user.user_id%type,
1996: p_user_type ota_attempts.user_type%type,
1997: p_launch_type ota_attempts.launch_type%type,
1998: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1999:
2011: function get_next_lo_id(
2012: p_root_lo_id ota_learning_objects.learning_object_id%type,
2013: p_root_starting_url ota_learning_objects.starting_url%type,
2014: p_starting_lo_id ota_learning_objects.learning_object_id%type,
2015: p_user_id fnd_user.user_id%type,
2016: p_user_type ota_attempts.user_type%type,
2017: p_launch_type ota_attempts.launch_type%type,
2018: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2019:
2047:
2048: function get_next_event_lo_id(
2049: p_event_id ota_events.event_id%type,
2050: p_starting_lo_id ota_learning_objects.learning_object_id%type,
2051: p_user_id fnd_user.user_id%type,
2052: p_user_type ota_attempts.user_type%type,
2053: p_launch_type ota_attempts.launch_type%type,
2054: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2055:
2066:
2067:
2068: function get_most_recent_lo_id(
2069: p_event_id ota_events.event_id%type,
2070: p_user_id fnd_user.user_id%type,
2071: p_user_type ota_attempts.user_type%type,
2072: p_launch_type ota_attempts.launch_type%type,
2073: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
2074:
2073: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
2074:
2075: cursor ordered_los(
2076: p_root_lo_id ota_learning_objects.learning_object_id%type,
2077: p_user_id fnd_user.user_id%type,
2078: p_user_type ota_attempts.user_type%type,
2079: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
2080: select a.learning_object_id, max(a.attempt_id) max_attempt
2081: from ota_attempts a,
2114:
2115:
2116: function get_first_lo_id(
2117: p_event_id ota_events.event_id%type,
2118: p_user_id fnd_user.user_id%type,
2119: p_user_type ota_attempts.user_type%type,
2120: p_launch_type ota_attempts.launch_type%type,
2121: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2122:
2135:
2136: function get_jump_lo_id(
2137: p_lo_id ota_learning_objects.learning_object_id%type,
2138: p_event_id ota_events.event_id%type,
2139: p_user_id fnd_user.user_id%type,
2140: p_user_type ota_attempts.user_type%type,
2141: p_launch_type ota_attempts.launch_type%type,
2142: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
2143: begin
2146:
2147: function get_jump_lo_id(
2148: p_lo_id ota_learning_objects.learning_object_id%type,
2149: p_event_id ota_events.event_id%type,
2150: p_user_id fnd_user.user_id%type,
2151: p_user_type ota_attempts.user_type%type,
2152: p_launch_type ota_attempts.launch_type%type,
2153: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
2154: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
2186:
2187:
2188: function user_can_attempt_event(
2189: p_event_id ota_events.event_id%type,
2190: p_user_id fnd_user.user_id%type,
2191: p_user_type ota_attempts.user_type%type) return varchar2 is
2192:
2193: l_reason number;
2194: begin
2197:
2198:
2199: function user_can_attempt_event(
2200: p_event_id ota_events.event_id%type,
2201: p_user_id fnd_user.user_id%type,
2202: p_user_type ota_attempts.user_type%type,
2203: p_launch_type ota_attempts.launch_type%type) return varchar2 is
2204:
2205: l_reason number;
2461: RETURN l_return_tree_title;
2462: End get_lo_title_for_tree;
2463:
2464: function get_play_button(
2465: p_user_id fnd_user.user_id%type,
2466: p_user_type ota_attempts.user_type%type,
2467: p_is_manager varchar2,
2468: p_event_id ota_events.event_id%type,
2469: p_event_type ota_events.event_type%type,
2671: end get_play_button;
2672:
2673:
2674: function get_play_button_for_test(
2675: p_user_id fnd_user.user_id%type,
2676: p_user_type ota_attempts.user_type%type,
2677: p_event_id ota_events.event_id%type,
2678: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2679:
2747: --bug6683076.Added for getting the play button
2748:
2749: function get_play_eval_button(
2750: p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2751: p_user_id fnd_user.user_id%type,
2752: p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2753: p_object_id OTA_EVALUATIONS.OBJECT_ID%TYPE,
2754: p_object_type OTA_EVALUATIONS.OBJECT_TYPE%TYPE,
2755: p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2918: -- wait before playing the LO again.
2919: -- [End of Comments]
2920: -- ---------------------------------------------------------------------------
2921: function get_wait_duration_for_lo(
2922: p_user_id fnd_user.user_id%type,
2923: p_user_type ota_attempts.user_type%type,
2924: p_lo_id ota_learning_objects.learning_object_id%type,
2925: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2926:
2948: -- wait before playing the test again. This is displayed as flyover text.
2949: -- [End of Comments]
2950: -- ---------------------------------------------------------------------------
2951: function get_wait_duration_for_test(
2952: p_user_id fnd_user.user_id%type,
2953: p_user_type ota_attempts.user_type%type,
2954: p_event_id ota_events.event_id%type,
2955: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2956:
3673: --
3674: -- {End Of Comments}
3675: ----------------------------------------------------------------------------
3676:
3677: FUNCTION get_history_button(p_user_id fnd_user.user_id%TYPE,
3678: p_lo_id ota_learning_objects.learning_object_id%TYPE,
3679: p_event_id ota_events.event_id%TYPE,
3680: p_booking_id ota_delegate_bookings.booking_id%TYPE)
3681: RETURN VARCHAR2 IS
4382:
4383: RETURN l_return_tree_title;
4384: End get_cert_lo_title_for_tree;
4385:
4386: function get_cme_online_event_id(p_user_id in fnd_user.user_id%type,
4387: p_user_type in ota_attempts.user_type%type,
4388: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4389: return varchar2 is
4390: CURSOR csr_cme_info IS
4523: RETURN null;
4524:
4525: End get_cme_online_event_id;
4526:
4527: function get_cme_play_button(p_user_id in fnd_user.user_id%type,
4528: p_user_type in ota_attempts.user_type%type,
4529: p_is_manager in varchar2,
4530: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4531: return varchar2 is
4595:
4596: End get_cme_play_button;
4597:
4598:
4599: function get_cme_player_toolbar_flag(p_user_id in fnd_user.user_id%type,
4600: p_user_type in ota_attempts.user_type%type,
4601: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4602: return varchar2 is
4603:
4642: RETURN null;
4643:
4644: End get_cme_player_toolbar_flag;
4645:
4646: function get_cert_lo_status(p_user_id in fnd_user.user_id%type,
4647: p_user_type in ota_attempts.user_type%type,
4648: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4649: return varchar2 is
4650:
4798: RETURN null;
4799:
4800: End get_cert_lo_status;
4801:
4802: function get_cme_onl_evt_count(p_user_id in fnd_user.user_id%type,
4803: p_user_type in ota_attempts.user_type%type,
4804: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4805: return varchar2 is
4806: CURSOR csr_cme_info IS
5102: RETURN null;
5103:
5104: End get_lme_online_event_id;
5105:
5106: function get_lme_play_button(p_user_id in fnd_user.user_id%type,
5107: p_user_type in ota_attempts.user_type%type,
5108: p_is_manager in varchar2,
5109: p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5110: return varchar2 is
5209: RETURN null;
5210:
5211: End get_lme_player_toolbar_flag;
5212:
5213: function get_lpe_lo_status(p_user_id in fnd_user.user_id%type,
5214: p_user_type in ota_attempts.user_type%type,
5215: p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5216: return varchar2 is
5217: