[Home] [Help]
4: g_package varchar2(33) := ' ota_lo_utility.'; -- Global package name
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
12: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
13:
14: cursor child_los(
15: p_lo_id ota_learning_objects.learning_object_id%type,
16: p_user_id fnd_user.user_id%type,
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
64:
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
213:
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
330:
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:
349: end update_enroll_status_for_lo;
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:
600:
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,
764:
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
776:
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
787:
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
812: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
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,
905:
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
917:
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
997:
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
1061:
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;
1113:
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;
1150:
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;
1168:
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
1207:
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:
1225:
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
1277:
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
1340:
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:
1387:
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
1427:
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:
1491: end cert_is_attemptable;
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:
1531: end user_is_enrolled_in_cert;
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
1585:
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:
1614:
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:
1667:
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:
1687: function get_next_lo_id(
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:
1723:
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:
1742:
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:
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,
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,
1790:
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:
1811:
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
1822:
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
1862:
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
1873:
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;
2076: RETURN l_return_tree_title;
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,
2296: end get_play_button;
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:
2337: --bug6683076.Added for getting the play button
2338:
2339: function get_play_eval_button(
2340: p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2341: p_user_id fnd_user.user_id%type,
2342: p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2343: p_object_id OTA_EVALUATIONS.OBJECT_ID%TYPE,
2344: p_object_type OTA_EVALUATIONS.OBJECT_TYPE%TYPE,
2345: p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2443: --Enhancement: 7310093 SIP: A NEW FIELD WHICH CAN GIVE THE STATUS OF THE COURSE / CLASS EVALUATION
2444:
2445: function get_admin_eval_status(
2446: p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2447: p_user_id fnd_user.user_id%type,
2448: p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2449: p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2450: p_test_id OTA_TESTS.TEST_ID%TYPE) return varchar2 is
2451:
2553: -- wait before playing the LO again.
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:
2583: -- wait before playing the test again. This is displayed as flyover text.
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:
3225: --
3226: -- {End Of Comments}
3227: ----------------------------------------------------------------------------
3228:
3229: FUNCTION get_history_button(p_user_id fnd_user.user_id%TYPE,
3230: p_lo_id ota_learning_objects.learning_object_id%TYPE,
3231: p_event_id ota_events.event_id%TYPE,
3232: p_booking_id ota_delegate_bookings.booking_id%TYPE)
3233: RETURN VARCHAR2 IS
3806:
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
3947: RETURN null;
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
4019:
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:
4066: RETURN null;
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:
4222: RETURN null;
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
4526: RETURN null;
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
4633: RETURN null;
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: