[Home] [Help]
272: cursor perf_lesson_status is
273: select per.lesson_status,
274: offe.learning_object_id
275: from ota_performances per,
276: ota_offerings offe,
277: ota_events evt
278: where evt.parent_offering_id = offe.offering_id and
279: offe.learning_object_id = per.learning_object_id(+) and
280: evt.event_id = p_event_id and
284:
285: l_proc varchar2(72) := g_package||'update_enrollment_status';
286: l_status varchar2(80) :='ATTENDED';
287: l_perf_status varchar2(1);
288: l_learning_object_id ota_offerings.learning_object_id%type;
289: l_failed varchar2(1):= null;
290:
291: begin
292: hr_utility.set_location('Entering:'|| l_proc, 10);
368: book.date_booking_placed,
369: book.object_version_number,
370: stype.type --Added for 7110517.
371: from ota_events ev,
372: ota_offerings offr,
373: ota_learning_objects lo,
374: ota_delegate_bookings book,
375: ota_booking_status_types stype
376: where lo.source_learning_object_id = p_lo_id and
398: book.date_booking_placed,
399: book.object_version_number,
400: stype.type --Added fro 7110517.
401: from ota_events ev,
402: ota_offerings offr,
403: ota_learning_objects lo,
404: ota_delegate_bookings book,
405: ota_booking_status_types stype,
406: hz_cust_account_roles acct_role,
560: cme.object_version_number,
561: cme.cert_member_id,
562: cme.cert_prd_enrollment_id
563: from ota_certification_members cm, ota_cert_mbr_enrollments cme,
564: ota_offerings o, ota_cert_prd_enrollments cpe
565: where
566: cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
567: and cme.cert_member_id = cm.certification_member_id
568: and cm.object_id = o.activity_version_id
977: v_root_lo_id ota_learning_objects.learning_object_id%type;
978: begin
979: select o.learning_object_id
980: into v_root_lo_id
981: from ota_events e, ota_offerings o
982: where e.event_id = p_event_id and
983: e.parent_offering_id = o.offering_id;
984:
985: select 'X'
1249: p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
1250:
1251: cursor root_los(p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
1252: select o.learning_object_id
1253: from ota_offerings o, ota_certification_members cm, ota_cert_mbr_enrollments cme
1254: where o.activity_version_id = cm.object_id
1255: and cm.certification_member_id = cme.cert_member_id
1256: and cme.cert_prd_enrollment_id = p_cert_prd_enroll_id;
1257:
1623: v_root_lo_id ota_learning_objects.learning_object_id%type;
1624: begin
1625: select o.learning_object_id
1626: into v_root_lo_id
1627: from ota_events e, ota_offerings o
1628: where e.event_id = p_event_id and
1629: e.parent_offering_id = o.offering_id;
1630:
1631: return get_previous_lo_id(v_root_lo_id, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1732: v_root_lo_id ota_learning_objects.learning_object_id%type;
1733: begin
1734: select o.learning_object_id
1735: into v_root_lo_id
1736: from ota_events e, ota_offerings o
1737: where e.event_id = p_event_id and
1738: e.parent_offering_id = o.offering_id;
1739:
1740: return get_next_lo_id(v_root_lo_id, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1773: v_reason number;
1774: begin
1775: select o.learning_object_id
1776: into v_root_lo_id
1777: from ota_events e, ota_offerings o
1778: where e.event_id = p_event_id and
1779: e.parent_offering_id = o.offering_id;
1780:
1781:
1801: v_reason number;
1802: begin
1803: select o.learning_object_id
1804: into v_root_lo_id
1805: from ota_events e, ota_offerings o
1806: where e.event_id = p_event_id and
1807: e.parent_offering_id = o.offering_id;
1808:
1809: return get_next_lo_id(v_root_lo_id, null, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
2209: ,v_published_flag
2210: from ota_learning_objects
2211: start with learning_object_id =
2212: (select learning_object_id
2213: from ota_offerings o
2214: ,ota_events e
2215: where o.offering_id = e.parent_offering_id
2216: and event_id = p_event_id)
2217: connect by parent_learning_object_id = prior learning_object_id;
2305: v_lo_id ota_learning_objects.learning_object_id%type;
2306: begin
2307: select offr.learning_object_id
2308: into v_lo_id
2309: from ota_offerings offr, ota_events evt
2310: where evt.event_id = p_event_id and
2311: evt.parent_offering_id = offr.offering_id;
2312:
2313: /*===============================================
2597:
2598: begin
2599: select offr.learning_object_id
2600: into v_lo_id
2601: from ota_offerings offr, ota_events evt
2602: where evt.event_id = p_event_id and
2603: evt.parent_offering_id = offr.offering_id;
2604:
2605: v_wait_duration := get_wait_duration_for_lo(p_user_id, p_user_type, v_lo_id, p_cert_prd_enrollment_id);
2702: RETURN VARCHAR2 IS
2703:
2704: CURSOR c_learning_object IS
2705: SELECT ofr.learning_object_id
2706: FROM ota_events oev, ota_offerings ofr
2707: WHERE oev.parent_offering_id = ofr.offering_id
2708: AND oev.event_id = p_event_id;
2709:
2710: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2706: FROM ota_events oev, ota_offerings ofr
2707: WHERE oev.parent_offering_id = ofr.offering_id
2708: AND oev.event_id = p_event_id;
2709:
2710: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2711: SELECT lesson_status
2712: FROM ota_performances
2713: WHERE user_id = p_user_id
2714: AND user_type = p_user_type
2726: WHERE event_id = p_event_id;
2727:
2728: l_proc VARCHAR2(72) := g_package|| 'get_player_status';
2729:
2730: l_learning_object_id ota_offerings.learning_object_id%TYPE;
2731:
2732: l_status VARCHAR2(30) := null;
2733:
2734: l_imported_offering ota_events.offering_id%TYPE := null;
2802: RETURN VARCHAR2 IS
2803:
2804: CURSOR c_learning_object IS
2805: SELECT ofr.learning_object_id
2806: FROM ota_events oev, ota_offerings ofr
2807: WHERE oev.parent_offering_id = ofr.offering_id
2808: AND oev.event_id = p_event_id;
2809:
2810: CURSOR c_learning_object_time(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2806: FROM ota_events oev, ota_offerings ofr
2807: WHERE oev.parent_offering_id = ofr.offering_id
2808: AND oev.event_id = p_event_id;
2809:
2810: CURSOR c_learning_object_time(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2811: SELECT TO_CHAR(TRUNC(SYSDATE)+(time)/86400, 'HH24:Mi:SS')
2812: FROM ota_performances
2813: WHERE user_id = p_user_id
2814: AND user_type = p_user_type
2826: WHERE event_id = p_event_id;
2827:
2828: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_time';
2829:
2830: l_learning_object_id ota_offerings.learning_object_id%TYPE;
2831:
2832: l_time VARCHAR2(30) := null;
2833:
2834: l_imported_offering ota_events.offering_id%TYPE := null;
2902: RETURN VARCHAR2 IS
2903:
2904: CURSOR c_learning_object IS
2905: SELECT ofr.learning_object_id
2906: FROM ota_events oev, ota_offerings ofr
2907: WHERE oev.parent_offering_id = ofr.offering_id
2908: AND oev.event_id = p_event_id;
2909:
2910: CURSOR c_learning_object_score(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2906: FROM ota_events oev, ota_offerings ofr
2907: WHERE oev.parent_offering_id = ofr.offering_id
2908: AND oev.event_id = p_event_id;
2909:
2910: CURSOR c_learning_object_score(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2911: SELECT score
2912: FROM ota_performances
2913: WHERE user_id = p_user_id
2914: AND user_type = p_user_type
2926: WHERE event_id = p_event_id;
2927:
2928: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_score';
2929:
2930: l_learning_object_id ota_offerings.learning_object_id%TYPE;
2931:
2932: l_score VARCHAR2(30) := null;
2933:
2934: l_imported_offering ota_events.offering_id%TYPE := null;
3015: CURSOR c_delivery_mode IS
3016: SELECT ocu.online_flag, ofr.learning_object_id,
3017: to_date(to_char(nvl(oev.course_end_date, to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || nvl(oev.course_end_time, '23:59'), 'YYYY/MM/DD HH24:MI'),
3018: ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, oev.timezone)
3019: FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
3020: WHERE oev.parent_offering_id = ofr.offering_id
3021: AND ofr.delivery_mode_id = ocu.category_usage_id
3022: AND oev.event_id = p_event_id;
3023:
3025: SELECT BST.type, BST.name
3026: FROM ota_booking_status_types_vl BST
3027: WHERE BST.booking_status_type_id = p_booking_status_type_id;
3028:
3029: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3030: SELECT lesson_status,
3031: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3032: FROM ota_performances
3033: WHERE user_id = p_user_id
3046: FROM ota_events
3047: WHERE event_id = p_event_id;
3048:
3049: -- Bug 4665032
3050: CURSOR csr_best_prf (l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3051: SELECT decode(prf.lesson_status, 'P', '1',
3052: 'C', '2',
3053: 'F', '3',
3054: 'I', '4',
3071:
3072: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_status';
3073:
3074: l_online_flag ota_category_usages.online_flag%TYPE;
3075: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3076: l_course_end_date ota_events.course_end_date%TYPE;
3077: l_sysdate ota_events.course_end_date%TYPE;
3078:
3079: l_status VARCHAR2(30) := null;
3387: -- (Select Online_Flag from ota_category_usages where Category_Usage_Id = ofr.Delivery_Mode_Id) Online_Flag
3388: ocu.Synchronous_Flag Sync_Flag,
3389: ocu.Online_Flag Online_Flag,
3390: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3391: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3392: Where
3393: oev.parent_offering_id = ofr.offering_id
3394: And ofr.learning_object_id = opf.learning_object_id(+)
3395: And oev.event_id = p_event_id
3476: ocu.Synchronous_Flag Sync_Flag,
3477: ocu.Online_Flag Online_Flag,
3478: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
3479: ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
3480: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3481: Where
3482: oev.parent_offering_id = ofr.offering_id
3483: And ofr.learning_object_id = opf.learning_object_id(+)
3484: And oev.event_id = p_event_id
3558:
3559:
3560: CURSOR c_delivery_mode IS
3561: SELECT ocu.online_flag, ofr.learning_object_id, nvl(oev.course_end_date, trunc(sysdate))
3562: FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
3563: WHERE oev.parent_offering_id = ofr.offering_id
3564: AND ofr.delivery_mode_id = ocu.category_usage_id
3565: AND oev.event_id = p_event_id;
3566:
3568: SELECT BST.type, BST.name
3569: FROM ota_booking_status_types_vl BST
3570: WHERE BST.booking_status_type_id = p_booking_status_type_id;
3571:
3572: CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3573: SELECT lesson_status,
3574: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3575: FROM ota_performances
3576: WHERE user_id = p_user_id
3580:
3581: l_proc VARCHAR2(72) := g_package|| 'get_cert_lo_status';
3582:
3583: l_online_flag ota_category_usages.online_flag%TYPE;
3584: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3585: l_course_end_date ota_events.course_end_date%TYPE;
3586:
3587: l_status VARCHAR2(30) := null;
3588: l_status_name ota_booking_status_types_tl.name%TYPE := null;
3848: bst.type
3849: FROM ota_events evt,
3850: ota_delegate_bookings tdb,
3851: ota_booking_status_types bst,
3852: ota_offerings ofr,
3853: ota_category_usages ocu
3854: WHERE evt.event_id = tdb.event_id
3855: AND bst.booking_status_type_id = tdb.booking_status_type_id
3856: /* AND (
3962: ocu.online_flag,
3963: evt.course_start_date,
3964: evt.course_end_date
3965: FROM ota_events evt,
3966: ota_offerings ofr,
3967: ota_category_usages ocu
3968: WHERE evt.event_id = csr_event_id
3969: AND evt.parent_offering_id = ofr.offering_id
3970: AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4027:
4028: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4029: select ofr.player_toolbar_flag
4030: from ota_events evt,
4031: ota_offerings ofr
4032: where evt.parent_offering_id = ofr.offering_id
4033: and evt.event_id = csr_event_id;
4034:
4035: l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
4033: and evt.event_id = csr_event_id;
4034:
4035: l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
4036:
4037: l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
4038:
4039: l_online_event_id ota_events.event_id%type;
4040: l_online_evt_count number;
4041:
4081: evt.course_start_date,
4082: evt.course_end_date,
4083: ofr.learning_object_id
4084: FROM ota_events evt,
4085: ota_offerings ofr,
4086: ota_category_usages ocu
4087: WHERE evt.event_id = csr_event_id
4088: AND evt.parent_offering_id = ofr.offering_id
4089: AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4093: from ota_cert_mbr_enrollments
4094: where cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4095:
4096:
4097: CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE,
4098: csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4099: SELECT lesson_status,
4100: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4101: FROM ota_performances
4126: 'N', '6') decode_lesson_status,
4127: prf.lesson_status lesson_status,
4128: prf.performance_id
4129: FROM ota_performances prf,
4130: ota_offerings ofr,
4131: ota_learning_objects lo
4132: WHERE
4133: prf.user_id = p_user_id
4134: and prf.user_type = p_user_type
4264: bst.type
4265: FROM ota_events evt,
4266: ota_delegate_bookings tdb,
4267: ota_booking_status_types bst,
4268: ota_offerings ofr,
4269: ota_category_usages ocu
4270: WHERE evt.event_id = tdb.event_id
4271: AND bst.booking_status_type_id = tdb.booking_status_type_id
4272: /* AND (
4460: bst.type
4461: FROM ota_events evt,
4462: ota_delegate_bookings tdb,
4463: ota_booking_status_types bst,
4464: ota_offerings ofr,
4465: ota_category_usages ocu
4466: WHERE evt.event_id = tdb.event_id
4467: AND bst.booking_status_type_id = tdb.booking_status_type_id
4468: AND evt.activity_version_id = csr_activity_version_id
4541: ocu.online_flag,
4542: evt.course_start_date,
4543: evt.course_end_date
4544: FROM ota_events evt,
4545: ota_offerings ofr,
4546: ota_category_usages ocu
4547: WHERE evt.event_id = csr_event_id
4548: AND evt.parent_offering_id = ofr.offering_id
4549: AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4594:
4595: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4596: select ofr.player_toolbar_flag
4597: from ota_events evt,
4598: ota_offerings ofr
4599: where evt.parent_offering_id = ofr.offering_id
4600: and evt.event_id = csr_event_id;
4601:
4602: l_proc VARCHAR2(72) := g_package||'get_lme_player_toolbar_flag';
4600: and evt.event_id = csr_event_id;
4601:
4602: l_proc VARCHAR2(72) := g_package||'get_lme_player_toolbar_flag';
4603:
4604: l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
4605:
4606: l_online_event_id ota_events.event_id%type;
4607: l_online_evt_count number;
4608:
4648: evt.course_start_date,
4649: evt.course_end_date,
4650: ofr.learning_object_id
4651: FROM ota_events evt,
4652: ota_offerings ofr,
4653: ota_category_usages ocu
4654: WHERE evt.event_id = csr_event_id
4655: AND evt.parent_offering_id = ofr.offering_id
4656: AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4676: 'N', '6') decode_lesson_status,
4677: prf.lesson_status lesson_status,
4678: prf.performance_id
4679: FROM ota_performances prf,
4680: ota_offerings ofr,
4681: ota_learning_objects lo
4682: WHERE
4683: prf.user_id = p_user_id
4684: and prf.user_type = p_user_type
4780: bst.type
4781: FROM ota_events evt,
4782: ota_delegate_bookings tdb,
4783: ota_booking_status_types bst,
4784: ota_offerings ofr,
4785: ota_category_usages ocu
4786: WHERE evt.event_id = tdb.event_id
4787: AND bst.booking_status_type_id = tdb.booking_status_type_id
4788: AND evt.activity_version_id = csr_activity_version_id