[Home] [Help]
1: package body ota_lo_utility as
2: /* $Header: otloutil.pkb 120.106.12020000.10 2013/03/29 12:58:08 jaysridh ship $ */
3:
4: g_package varchar2(33) := ' ota_lo_utility.'; -- Global package name
5: cursor csr_active_cert_prd_person(p_event_id ota_events.event_id%type,
6: p_person_id ota_cert_enrollments.contact_id%type) is
7: SELECT cpe.cert_prd_enrollment_id
8: FROM OTA_CERTIFICATIONS_B crt,
9: OTA_CERT_ENROLLMENTS cre,
9: OTA_CERT_ENROLLMENTS cre,
10: OTA_CERT_PRD_ENROLLMENTS cpe,
11: OTA_CERT_MBR_ENROLLMENTS cme,
12: OTA_CERTIFICATION_MEMBERS cmb,
13: OTA_EVENTS evt
14: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
15: AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
16: AND cmb.OBJECT_TYPE = 'H'
17: AND cmb.OBJECT_ID = evt.activity_version_id
32: cpe.cert_period_end_date >= evt.course_start_date AND
33: NVL(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= cpe.cert_period_start_date)
34: );
35:
36: cursor csr_active_cert_prd_contact(p_event_id ota_events.event_id%type,
37: p_contact_id ota_cert_enrollments.contact_id%type) is
38: SELECT cpe.cert_prd_enrollment_id
39: FROM OTA_CERTIFICATIONS_B crt,
40: OTA_CERT_ENROLLMENTS cre,
40: OTA_CERT_ENROLLMENTS cre,
41: OTA_CERT_PRD_ENROLLMENTS cpe,
42: OTA_CERT_MBR_ENROLLMENTS cme,
43: OTA_CERTIFICATION_MEMBERS cmb,
44: OTA_EVENTS evt
45: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
46: AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
47: AND cmb.OBJECT_TYPE = 'H'
48: AND cmb.OBJECT_ID = evt.activity_version_id
293:
294:
295: procedure update_enrollment(
296: p_booking_id ota_delegate_bookings.booking_id%type,
297: p_event_id ota_events.event_id%type,
298: p_business_group_id ota_delegate_bookings.business_group_id%type,
299: p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
300: p_object_version_number ota_delegate_bookings.object_version_number%type,
301: p_sign_eval_status ota_delegate_bookings.sign_eval_status%type,
319: --Added cursor to check whether update is needed for the enrollment in case of online classes
320: --after learner has signed the course.
321: cursor csr_book_details is
322: select odb.delegate_person_id, odb.delegate_contact_id, offer.learning_object_id
323: from ota_delegate_bookings odb, ota_events evt, ota_offerings offer
324: where odb.event_id = evt.event_id
325: and evt.parent_offering_id = offer.offering_id
326: and booking_id = p_booking_id;
327:
468:
469:
470: procedure update_enrollment(
471: p_booking_id ota_delegate_bookings.booking_id%type,
472: p_event_id ota_events.event_id%type,
473: p_business_group_id ota_delegate_bookings.business_group_id%type,
474: p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
475: p_object_version_number ota_delegate_bookings.object_version_number%type,
476: p_sign_eval_status ota_delegate_bookings.sign_eval_status%type,
483: end update_enrollment;
484:
485: procedure update_enrollment(
486: p_booking_id ota_delegate_bookings.booking_id%type,
487: p_event_id ota_events.event_id%type,
488: p_business_group_id ota_delegate_bookings.business_group_id%type,
489: p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
490: p_object_version_number ota_delegate_bookings.object_version_number%type,
491: p_date_status_changed ota_delegate_bookings.date_status_changed%type,
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
509: -- which the person is enrolled with a status of 'PENDING EVALUATION'.
510:
509: -- which the person is enrolled with a status of 'PENDING EVALUATION'.
510:
511:
512: cursor person_bookings(
513: p_event_id ota_events.event_id%type,
514: p_person_id ota_delegate_bookings.delegate_person_id%type) is
515: select book.booking_id,
516: book.sign_eval_status,
517: ev.event_id,
517: ev.event_id,
518: book.business_group_id,
519: book.date_booking_placed,
520: book.object_version_number
521: from ota_events ev,
522: ota_delegate_bookings book,
523: ota_booking_status_types stype
524: where nvl(ev.course_start_date, sysdate) <= sysdate and
525: book.event_id = ev.event_id and
529: (stype.type = 'E' or (stype.type ='A' and book.sign_eval_status in ('OE'))); --11795316
530:
531: --Added for 6826434.
532: cursor party_bookings(
533: p_event_id ota_events.event_id%type,
534: p_party_id ota_delegate_bookings.delegate_contact_id%type) is
535: select book.booking_id,
536: book.sign_eval_status,
537: ev.event_id,
537: ev.event_id,
538: book.business_group_id,
539: book.date_booking_placed,
540: book.object_version_number
541: from ota_events ev,
542: ota_delegate_bookings book,
543: ota_booking_status_types stype,
544: hz_cust_account_roles acct_role,
545: hz_relationships rel,
562: select per.lesson_status,
563: offe.learning_object_id
564: from ota_performances per,
565: ota_offerings offe,
566: ota_events evt
567: where evt.parent_offering_id = offe.offering_id and
568: offe.learning_object_id = per.learning_object_id(+) and
569: evt.event_id = p_event_id and
570: (per.user_type is null or per.user_type = p_user_type) and
663: stype.type, --Added for 7110517.
664: act.eres_enabled,
665: book.delegate_person_id,
666: book.successful_attendance_flag
667: from ota_events ev,
668: ota_offerings offr,
669: ota_learning_objects lo,
670: ota_delegate_bookings book,
671: ota_booking_status_types stype,
699: stype.type, --Added fro 7110517.
700: act.eres_enabled,
701: book.delegate_contact_id,
702: book.successful_attendance_flag
703: from ota_events ev,
704: ota_offerings offr,
705: ota_learning_objects lo,
706: ota_delegate_bookings book,
707: ota_booking_status_types stype,
725: rel.subject_table_name = 'HZ_PARTIES' and
726: rel.object_table_name = 'HZ_PARTIES' and
727: act.activity_version_id = offr.activity_version_id;
728:
729: l_event_id OTA_EVENTS.EVENT_ID%TYPE:= null;
730: l_test_id OTA_TESTS.TEST_ID%type:=null;
731: Cursor csr_attempt_info is
732: select attempt_id from ota_attempts where
733: event_id = l_event_id
736: and user_type = p_user_type;
737:
738: -- Added following two cursors in order to get the certification status of the
739: -- learner in case he is either a person or a contact.
740: Cursor csr_person_cert_status(p_event_id ota_events.event_id%type,
741: p_person_id ota_cert_enrollments.person_id%type) is
742: SELECT cre.CERTIFICATION_STATUS_CODE
743: FROM OTA_CERTIFICATIONS_B crt,
744: OTA_CERT_ENROLLMENTS cre,
743: FROM OTA_CERTIFICATIONS_B crt,
744: OTA_CERT_ENROLLMENTS cre,
745: OTA_CERT_MBR_ENROLLMENTS cme,
746: OTA_CERTIFICATION_MEMBERS cmb,
747: OTA_EVENTS evt
748: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
749: AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
750: AND cmb.OBJECT_TYPE = 'H'
751: AND cmb.OBJECT_ID = evt.activity_version_id
755: AND cre.person_id = p_person_id
756: AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
757: AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
758:
759: Cursor csr_contact_cert_status(p_event_id ota_events.event_id%type,
760: p_contact_id ota_cert_enrollments.contact_id%type) is
761: SELECT cre.CERTIFICATION_STATUS_CODE
762: FROM OTA_CERTIFICATIONS_B crt,
763: OTA_CERT_ENROLLMENTS cre,
762: FROM OTA_CERTIFICATIONS_B crt,
763: OTA_CERT_ENROLLMENTS cre,
764: OTA_CERT_MBR_ENROLLMENTS cme,
765: OTA_CERTIFICATION_MEMBERS cmb,
766: OTA_EVENTS evt
767: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
768: AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
769: AND cmb.OBJECT_TYPE = 'H'
770: AND cmb.OBJECT_ID = evt.activity_version_id
1276: end lo_is_attemptable;
1277:
1278:
1279: function lo_is_in_event(
1280: p_event_id ota_events.event_id%type,
1281: p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
1282:
1283: v_dummy char(1);
1284: v_root_lo_id ota_learning_objects.learning_object_id%type;
1284: v_root_lo_id ota_learning_objects.learning_object_id%type;
1285: begin
1286: select o.learning_object_id
1287: into v_root_lo_id
1288: from ota_events e, ota_offerings o
1289: where e.event_id = p_event_id and
1290: e.parent_offering_id = o.offering_id;
1291:
1292: select 'X'
1530:
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,
1582: end lo_is_in_cert;
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,
1602: return true;
1603: end user_can_attempt_lo;
1604:
1605: function event_is_attemptable(
1606: p_event_id ota_events.event_id%type,
1607: p_date date,
1608: p_launch_type ota_attempts.launch_type%type,
1609: p_reason out nocopy number) return boolean is
1610:
1607: p_date date,
1608: p_launch_type ota_attempts.launch_type%type,
1609: p_reason out nocopy number) return boolean is
1610:
1611: v_course_start_date ota_events.course_start_date%type;
1612: v_course_start_time ota_events.course_start_time%type;
1613: v_course_end_date ota_events.course_end_date%type;
1614: v_course_end_time ota_events.course_end_time%type;
1615: v_is_instructor boolean;
1608: p_launch_type ota_attempts.launch_type%type,
1609: p_reason out nocopy number) return boolean is
1610:
1611: v_course_start_date ota_events.course_start_date%type;
1612: v_course_start_time ota_events.course_start_time%type;
1613: v_course_end_date ota_events.course_end_date%type;
1614: v_course_end_time ota_events.course_end_time%type;
1615: v_is_instructor boolean;
1616: v_course_started boolean;
1609: p_reason out nocopy number) return boolean is
1610:
1611: v_course_start_date ota_events.course_start_date%type;
1612: v_course_start_time ota_events.course_start_time%type;
1613: v_course_end_date ota_events.course_end_date%type;
1614: v_course_end_time ota_events.course_end_time%type;
1615: v_is_instructor boolean;
1616: v_course_started boolean;
1617: v_course_expired boolean;
1610:
1611: v_course_start_date ota_events.course_start_date%type;
1612: v_course_start_time ota_events.course_start_time%type;
1613: v_course_end_date ota_events.course_end_date%type;
1614: v_course_end_time ota_events.course_end_time%type;
1615: v_is_instructor boolean;
1616: v_course_started boolean;
1617: v_course_expired boolean;
1618: begin
1619: p_reason := EVENT_REASON_NO_REASON;
1620:
1621: select course_start_date, course_start_time, course_end_date, course_end_time
1622: into v_course_start_date, v_course_start_time, v_course_end_date, v_course_end_time
1623: from ota_events
1624: where event_id = p_event_id;
1625:
1626: v_course_started := (v_course_start_date is null or p_date >= v_course_start_date);
1627: v_course_expired := (v_course_end_date is not null and p_date > v_course_end_date);
1645: end event_is_attemptable;
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
1652: p_date date,
1653: p_reason out nocopy number) return boolean is
1654:
1655: cursor person_bookings(
1656: p_event_id ota_events.event_id%type,
1657: p_person_id per_all_people.person_id%type) is
1658: select 1
1659: from ota_delegate_bookings book,
1660: ota_booking_status_types stype
1663: book.booking_status_type_id = stype.booking_status_type_id and
1664: stype.type in ('P', 'A' ,'E');
1665:
1666: cursor contact_bookings(
1667: p_event_id ota_events.event_id%type,
1668: p_contact_id per_all_people.person_id%type) is
1669: select 1
1670: from ota_delegate_bookings book,
1671: ota_booking_status_types stype
1710: end user_is_enrolled;
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
1731: tsr.trainer_id = p_user_id and
1732: trb.supplied_resource_id = tsr.supplied_resource_id and
1733: (trb.event_id = p_event_id or
1734: trb.event_id in ( select ses.event_id
1735: from ota_events ses
1736: where ses.parent_event_id = p_event_id
1737: and ses.event_type = 'SESSION')
1738: ) and
1739: tsr.resource_type = 'T') loop
1750: end user_is_instructor;
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
1936: end get_previous_lo_id;
1937:
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,
1947: v_root_lo_id ota_learning_objects.learning_object_id%type;
1948: begin
1949: select o.learning_object_id
1950: into v_root_lo_id
1951: from ota_events e, ota_offerings o
1952: where e.event_id = p_event_id and
1953: e.parent_offering_id = o.offering_id;
1954:
1955: 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);
2045: end get_next_lo_id;
2046:
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,
2056: v_root_lo_id ota_learning_objects.learning_object_id%type;
2057: begin
2058: select o.learning_object_id
2059: into v_root_lo_id
2060: from ota_events e, ota_offerings o
2061: where e.event_id = p_event_id and
2062: e.parent_offering_id = o.offering_id;
2063:
2064: 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);
2065: end get_next_event_lo_id;
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
2097: v_reason number;
2098: begin
2099: select o.learning_object_id
2100: into v_root_lo_id
2101: from ota_events e, ota_offerings o
2102: where e.event_id = p_event_id and
2103: e.parent_offering_id = o.offering_id;
2104:
2105:
2113: end get_most_recent_lo_id;
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
2125: v_reason number;
2126: begin
2127: select o.learning_object_id
2128: into v_root_lo_id
2129: from ota_events e, ota_offerings o
2130: where e.event_id = p_event_id and
2131: e.parent_offering_id = o.offering_id;
2132:
2133: return get_next_lo_id(v_root_lo_id, null, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
2134: end get_first_lo_id;
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
2145: end get_jump_lo_id;
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,
2185: end get_lo_type;
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;
2196: end user_can_attempt_event;
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:
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,
2470: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2471: p_online_flag ota_category_usages.online_flag%type,
2472: p_course_start_date ota_events.course_start_date%type,
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,
2470: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2471: p_online_flag ota_category_usages.online_flag%type,
2472: p_course_start_date ota_events.course_start_date%type,
2473: p_course_end_date ota_events.course_end_date%type,
2468: p_event_id ota_events.event_id%type,
2469: p_event_type ota_events.event_type%type,
2470: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2471: p_online_flag ota_category_usages.online_flag%type,
2472: p_course_start_date ota_events.course_start_date%type,
2473: p_course_end_date ota_events.course_end_date%type,
2474: p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
2475: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
2476: p_contact_id ota_cert_enrollments.contact_id%type default null,
2469: p_event_type ota_events.event_type%type,
2470: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2471: p_online_flag ota_category_usages.online_flag%type,
2472: p_course_start_date ota_events.course_start_date%type,
2473: p_course_end_date ota_events.course_end_date%type,
2474: p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
2475: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
2476: p_contact_id ota_cert_enrollments.contact_id%type default null,
2477: p_chk_active_cert_flag varchar2 default 'N') return varchar2 is
2482: v_published_flag ota_learning_objects.published_flag%type := null;
2483: v_first_lo ota_learning_objects.learning_object_id%type := null;
2484: v_play_button_for_test varchar2(100) := null;
2485: v_rco_id ota_activity_versions.rco_id%type := null;
2486: v_offering_id ota_events.offering_id%type := null;
2487:
2488: l_reason number;
2489: l_sysdate date;
2490: l_course_start_time ota_events.course_start_time%type;
2486: v_offering_id ota_events.offering_id%type := null;
2487:
2488: l_reason number;
2489: l_sysdate date;
2490: l_course_start_time ota_events.course_start_time%type;
2491: l_course_end_time ota_events.course_end_time%type;
2492: l_course_start_date ota_events.course_start_date%type;
2493: l_course_end_date ota_events.course_end_date%type;
2494: l_timezone ota_events.timezone%type;
2487:
2488: l_reason number;
2489: l_sysdate date;
2490: l_course_start_time ota_events.course_start_time%type;
2491: l_course_end_time ota_events.course_end_time%type;
2492: l_course_start_date ota_events.course_start_date%type;
2493: l_course_end_date ota_events.course_end_date%type;
2494: l_timezone ota_events.timezone%type;
2495:
2488: l_reason number;
2489: l_sysdate date;
2490: l_course_start_time ota_events.course_start_time%type;
2491: l_course_end_time ota_events.course_end_time%type;
2492: l_course_start_date ota_events.course_start_date%type;
2493: l_course_end_date ota_events.course_end_date%type;
2494: l_timezone ota_events.timezone%type;
2495:
2496: CURSOR c_event_time_info is
2489: l_sysdate date;
2490: l_course_start_time ota_events.course_start_time%type;
2491: l_course_end_time ota_events.course_end_time%type;
2492: l_course_start_date ota_events.course_start_date%type;
2493: l_course_end_date ota_events.course_end_date%type;
2494: l_timezone ota_events.timezone%type;
2495:
2496: CURSOR c_event_time_info is
2497: Select course_start_time, course_end_time, timezone
2490: l_course_start_time ota_events.course_start_time%type;
2491: l_course_end_time ota_events.course_end_time%type;
2492: l_course_start_date ota_events.course_start_date%type;
2493: l_course_end_date ota_events.course_end_date%type;
2494: l_timezone ota_events.timezone%type;
2495:
2496: CURSOR c_event_time_info is
2497: Select course_start_time, course_end_time, timezone
2498: From ota_events
2494: l_timezone ota_events.timezone%type;
2495:
2496: CURSOR c_event_time_info is
2497: Select course_start_time, course_end_time, timezone
2498: From ota_events
2499: Where event_id = p_event_id;
2500:
2501: l_cert_prd_enrollment_ids varchar2(4000) := null;
2502: l_person_id ota_cert_enrollments.person_id%type := null;
2580: select a.rco_id
2581: ,e.offering_id
2582: into v_rco_id
2583: ,v_offering_id
2584: from ota_activity_versions a, ota_events e
2585: where a.activity_version_id = e.activity_version_id
2586: and e.event_id = p_event_id;
2587:
2588: if v_rco_id is not null and v_offering_id is not null then
2603: from ota_learning_objects
2604: start with learning_object_id =
2605: (select learning_object_id
2606: from ota_offerings o
2607: ,ota_events e
2608: where o.offering_id = e.parent_offering_id
2609: and event_id = p_event_id)
2610: connect by parent_learning_object_id = prior learning_object_id;
2611:
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:
2680: v_lo_id ota_learning_objects.learning_object_id%type;
2681: begin
2680: v_lo_id ota_learning_objects.learning_object_id%type;
2681: begin
2682: select offr.learning_object_id
2683: into v_lo_id
2684: from ota_offerings offr, ota_events evt
2685: where evt.event_id = p_event_id and
2686: evt.parent_offering_id = offr.offering_id;
2687:
2688: /*===============================================
2746:
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,
2773: and internal_state = 'F'; --bug#7311115
2774:
2775: CURSOR c_mandatory_info is
2776: select eval.eval_mandatory_flag,eval.evaluation_id
2777: from ota_evaluations eval,ota_events evt
2778: where evt.activity_version_id = eval.object_id(+)
2779: and evt.event_id = p_event_id
2780: and eval.evaluation_id is not null --bug 7184369
2781: and (eval.object_type = 'A' or eval.object_type is null);
2853: --Enhancement: 7310093 SIP: A NEW FIELD WHICH CAN GIVE THE STATUS OF THE COURSE / CLASS EVALUATION
2854: --Modified for 8855548.
2855:
2856: function get_admin_eval_status(
2857: p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2858: p_sign_eval_status OTA_DELEGATE_BOOKINGS.SIGN_EVAL_STATUS%TYPE
2859: ) return varchar2 is
2860:
2861: l_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE;
2863:
2864: Cursor csr_evt_evaluations is
2865: select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
2866: decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag
2867: from ota_evaluations evt_eval, ota_evaluations act_eval,ota_events evt
2868: where evt_eval.object_id(+) = evt.event_id
2869: and evt_eval.object_type(+) = 'E'
2870: and act_eval.object_id(+) = evt.activity_version_id
2871: and act_eval.object_type(+) = 'A'
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:
2957: v_lo_id ota_learning_objects.learning_object_id%type;
2958: v_wait_duration varchar2(80);
2962:
2963: begin
2964: select offr.learning_object_id
2965: into v_lo_id
2966: from ota_offerings offr, ota_events evt
2967: where evt.event_id = p_event_id and
2968: evt.parent_offering_id = offr.offering_id;
2969:
2970: v_wait_duration := get_wait_duration_for_lo(p_user_id, p_user_type, v_lo_id, p_cert_prd_enrollment_id);
3061: ----------------------------------------------------------------------------
3062: --
3063: FUNCTION get_player_status(p_user_id IN NUMBER,
3064: p_user_type IN ota_attempts.user_type%type,
3065: p_event_id IN ota_events.event_id%TYPE,
3066: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
3067: RETURN VARCHAR2 IS
3068:
3069: CURSOR c_learning_object IS
3067: RETURN VARCHAR2 IS
3068:
3069: CURSOR c_learning_object IS
3070: SELECT ofr.learning_object_id
3071: FROM ota_events oev, ota_offerings ofr
3072: WHERE oev.parent_offering_id = ofr.offering_id
3073: AND oev.event_id = p_event_id;
3074:
3075: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3086: WHERE booking_id = p_booking_id;
3087:
3088: CURSOR c_imported_offering IS
3089: SELECT offering_id
3090: FROM ota_events
3091: WHERE event_id = p_event_id;
3092:
3093: l_proc VARCHAR2(72) := g_package|| 'get_player_status';
3094:
3095: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3096:
3097: l_status VARCHAR2(30) := null;
3098:
3099: l_imported_offering ota_events.offering_id%TYPE := null;
3100:
3101: BEGIN
3102: hr_utility.set_location(' Step:'|| l_proc, 10);
3103:
3161: ----------------------------------------------------------------------------
3162: --
3163: FUNCTION get_enroll_lo_time(p_user_id IN NUMBER,
3164: p_user_type IN ota_attempts.user_type%type,
3165: p_event_id IN ota_events.event_id%TYPE,
3166: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
3167: RETURN VARCHAR2 IS
3168:
3169: CURSOR c_learning_object IS
3167: RETURN VARCHAR2 IS
3168:
3169: CURSOR c_learning_object IS
3170: SELECT ofr.learning_object_id
3171: FROM ota_events oev, ota_offerings ofr
3172: WHERE oev.parent_offering_id = ofr.offering_id
3173: AND oev.event_id = p_event_id;
3174:
3175: CURSOR c_learning_object_time(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3186: WHERE booking_id = p_booking_id;
3187:
3188: CURSOR c_imported_offering IS
3189: SELECT offering_id
3190: FROM ota_events
3191: WHERE event_id = p_event_id;
3192:
3193: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_time';
3194:
3195: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3196:
3197: l_time VARCHAR2(30) := null;
3198:
3199: l_imported_offering ota_events.offering_id%TYPE := null;
3200:
3201: BEGIN
3202: hr_utility.set_location(' Step:'|| l_proc, 10);
3203:
3261: ----------------------------------------------------------------------------
3262: --
3263: FUNCTION get_enroll_lo_score(p_user_id IN NUMBER,
3264: p_user_type IN ota_attempts.user_type%type,
3265: p_event_id IN ota_events.event_id%TYPE,
3266: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
3267: RETURN VARCHAR2 IS
3268:
3269: CURSOR c_learning_object IS
3267: RETURN VARCHAR2 IS
3268:
3269: CURSOR c_learning_object IS
3270: SELECT ofr.learning_object_id
3271: FROM ota_events oev, ota_offerings ofr
3272: WHERE oev.parent_offering_id = ofr.offering_id
3273: AND oev.event_id = p_event_id;
3274:
3275: CURSOR c_learning_object_score(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3286: WHERE booking_id = p_booking_id;
3287:
3288: CURSOR c_imported_offering IS
3289: SELECT offering_id
3290: FROM ota_events
3291: WHERE event_id = p_event_id;
3292:
3293: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_score';
3294:
3295: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3296:
3297: l_score VARCHAR2(30) := null;
3298:
3299: l_imported_offering ota_events.offering_id%TYPE := null;
3300:
3301: BEGIN
3302: hr_utility.set_location(' Step:'|| l_proc, 10);
3303:
3367: ----------------------------------------------------------------------------
3368: --
3369: FUNCTION get_enroll_lo_status(p_user_id IN NUMBER,
3370: p_user_type IN ota_attempts.user_type%type,
3371: p_event_id IN ota_events.event_id%TYPE,
3372: p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
3373: p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
3374: p_mode IN number default null,
3375: p_chk_active_cert_flag varchar2 default 'N')
3380: CURSOR c_delivery_mode IS
3381: SELECT ocu.online_flag, ofr.learning_object_id,
3382: 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'),
3383: ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, oev.timezone)
3384: FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
3385: WHERE oev.parent_offering_id = ofr.offering_id
3386: AND ofr.delivery_mode_id = ocu.category_usage_id
3387: AND oev.event_id = p_event_id;
3388:
3407: WHERE booking_id = p_booking_id;
3408:
3409: CURSOR c_imported_offering IS
3410: SELECT offering_id
3411: FROM ota_events
3412: WHERE event_id = p_event_id;
3413:
3414: -- Bug 13496386
3415: cursor c_learning_object_tracktype (l_lo_id in ota_offerings.learning_object_id%type) IS
3471: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_status';
3472:
3473: l_online_flag ota_category_usages.online_flag%TYPE;
3474: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3475: l_course_end_date ota_events.course_end_date%TYPE;
3476: l_sysdate ota_events.course_end_date%TYPE;
3477:
3478: l_status VARCHAR2(30) := null;
3479: l_status_name ota_booking_status_types_tl.name%TYPE := null;
3472:
3473: l_online_flag ota_category_usages.online_flag%TYPE;
3474: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3475: l_course_end_date ota_events.course_end_date%TYPE;
3476: l_sysdate ota_events.course_end_date%TYPE;
3477:
3478: l_status VARCHAR2(30) := null;
3479: l_status_name ota_booking_status_types_tl.name%TYPE := null;
3480:
3483: l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
3484: l_decode_lesson_status VARCHAR2(1);
3485:
3486: -- Bug 3725560
3487: l_imported_offering ota_events.offering_id%TYPE := null;
3488:
3489: l_cert_prd_enrollment_ids varchar2(4000) := null;
3490: l_person_id ota_cert_enrollments.person_id%type := null;
3491: l_contact_id ota_cert_enrollments.contact_id%type := null;
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
3682:
3683: l_return VARCHAR2(100);
3684: l_enable_move_to_history boolean;
3685: l_type varchar2(1);
3686: l_proc VARCHAR2(72) := g_package||'get_history_button';
3687:
3688: l_imported_offering ota_events.offering_id%TYPE;
3689: l_booking_status_type ota_booking_status_types.type%TYPE;
3690:
3691: CURSOR c_imported_offering IS
3692: SELECT offering_id
3689: l_booking_status_type ota_booking_status_types.type%TYPE;
3690:
3691: CURSOR c_imported_offering IS
3692: SELECT offering_id
3693: FROM ota_events
3694: WHERE event_id = p_event_id;
3695:
3696: CURSOR c_history_enabled IS
3697: SELECT lesson_status
3802: -- Public
3803: -- {End of Comments}
3804: ------------------------------------------------------------------
3805: --Added method get_lo_completion_date_time,similar to get_lo_completion_date but returns a date allowing UI sort
3806: FUNCTION get_lo_completion_date(p_event_id IN ota_events.event_id%type,
3807: p_user_id IN NUMBER,
3808: p_user_type IN ota_attempts.user_type%type,
3809: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3810: p_module_name IN VARCHAR2 default 'LEARNER')
3810: p_module_name IN VARCHAR2 default 'LEARNER')
3811: RETURN varchar2
3812: IS
3813: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date';
3814: l_event_id ota_events.event_id%type;
3815: l_lo_id ota_learning_objects.learning_object_id%type;
3816: -- l_lo_completed_date VARCHAR2(100);
3817: l_lo_completed_date DATE;
3818: l_lo_completed_time VARCHAR2(10);
3838: 'N', '6') decode_lesson_status,
3839: ocu.Synchronous_Flag Sync_Flag,
3840: ocu.Online_Flag Online_Flag,
3841: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3842: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3843: Where
3844: oev.parent_offering_id = ofr.offering_id
3845: And ofr.learning_object_id = opf.learning_object_id(+)
3846: And oev.event_id = p_event_id
3858: opf.lesson_status,
3859: ocu.Synchronous_Flag Sync_Flag,
3860: ocu.Online_Flag Online_Flag,
3861: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3862: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3863: Where
3864: oev.parent_offering_id = ofr.offering_id
3865: And ofr.learning_object_id = opf.learning_object_id(+)
3866: And oev.event_id = p_event_id
3909:
3910: --Added for 6768606:COMPLETION DATE COLUMN SORT NUMERIC AND NOT BY ACTUAL DATE SORT
3911: --Similar to get_lo_completion_date,but the return type is date.Called from admin side
3912:
3913: FUNCTION get_lo_completion_date_time(p_event_id IN ota_events.event_id%type,
3914: p_user_id IN NUMBER,
3915: p_user_type IN ota_attempts.user_type%type,
3916: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3917: p_module_name IN VARCHAR2 default 'LEARNER')
3917: p_module_name IN VARCHAR2 default 'LEARNER')
3918: RETURN date
3919: IS
3920: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date_time';
3921: l_event_id ota_events.event_id%type;
3922: l_lo_id ota_learning_objects.learning_object_id%type;
3923: l_lo_completed_date DATE;
3924: l_lo_comp_date DATE;
3925: l_lo_completed_time VARCHAR2(10);
3946: ocu.Synchronous_Flag Sync_Flag,
3947: ocu.Online_Flag Online_Flag,
3948: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
3949: ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
3950: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3951: Where
3952: oev.parent_offering_id = ofr.offering_id
3953: And ofr.learning_object_id = opf.learning_object_id(+)
3954: And oev.event_id = p_event_id
3967: ocu.Synchronous_Flag Sync_Flag,
3968: ocu.Online_Flag Online_Flag,
3969: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
3970: ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
3971: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3972: Where
3973: oev.parent_offering_id = ofr.offering_id
3974: And ofr.learning_object_id = opf.learning_object_id(+)
3975: And oev.event_id = p_event_id
4044:
4045:
4046: FUNCTION get_cert_lo_status(p_user_id IN NUMBER,
4047: p_user_type IN ota_attempts.user_type%type,
4048: p_event_id IN ota_events.event_id%TYPE,
4049: p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
4050: p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
4051: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
4052: p_mode IN number default null)
4054:
4055:
4056: CURSOR c_delivery_mode IS
4057: SELECT ocu.online_flag, ofr.learning_object_id, nvl(oev.course_end_date, trunc(sysdate))
4058: FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
4059: WHERE oev.parent_offering_id = ofr.offering_id
4060: AND ofr.delivery_mode_id = ocu.category_usage_id
4061: AND oev.event_id = p_event_id;
4062:
4082: l_proc VARCHAR2(72) := g_package|| 'get_cert_lo_status';
4083:
4084: l_online_flag ota_category_usages.online_flag%TYPE;
4085: l_learning_object_id ota_offerings.learning_object_id%TYPE;
4086: l_course_end_date ota_events.course_end_date%TYPE;
4087:
4088: l_status VARCHAR2(30) := null;
4089: l_status_name ota_booking_status_types_tl.name%TYPE := null;
4090: l_sign_eval_status OTA_DELEGATE_BOOKINGS.sign_eval_status%type;
4408: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4409: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
4410:
4411:
4412: CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
4413: csr_cert_period_start_date in date,
4414: csr_cert_period_end_date in date,
4415: csr_person_id in number,
4416: csr_contact_id in number) IS
4421: evt.event_type,
4422: ocu.synchronous_flag,
4423: ocu.online_flag,
4424: bst.type
4425: FROM ota_events evt,
4426: ota_delegate_bookings tdb,
4427: ota_booking_status_types bst,
4428: ota_offerings ofr,
4429: ota_category_usages ocu
4473: l_contact_id number;
4474: l_cert_period_start_date date;
4475: l_cert_period_end_date date;
4476:
4477: l_online_event_id ota_events.event_id%type;
4478: l_online_evt_count number;
4479:
4480: BEGIN
4481: hr_utility.set_location('Entering :'||l_proc,5);
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
4532:
4533: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4534: SELECT
4535: evt.event_id,
4536: evt.event_type,
4537: ocu.synchronous_flag,
4537: ocu.synchronous_flag,
4538: ocu.online_flag,
4539: evt.course_start_date,
4540: evt.course_end_date
4541: FROM ota_events evt,
4542: ota_offerings ofr,
4543: ota_category_usages ocu
4544: WHERE evt.event_id = csr_event_id
4545: AND evt.parent_offering_id = ofr.offering_id
4552:
4553: l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
4554: rec csr_event%rowtype;
4555: rec_cme csr_cme%rowtype;
4556: l_online_event_id ota_events.event_id%type;
4557:
4558: l_return_play_btn VARCHAR2(30) := 'DISABLE_NOT_ENROLLED';
4559: l_online_evt_count number;
4560:
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:
4604: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4605: select ofr.player_toolbar_flag
4606: from ota_events evt,
4607: ota_offerings ofr
4608: where evt.parent_offering_id = ofr.offering_id
4602: return varchar2 is
4603:
4604: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4605: select ofr.player_toolbar_flag
4606: from ota_events evt,
4607: ota_offerings ofr
4608: where evt.parent_offering_id = ofr.offering_id
4609: and evt.event_id = csr_event_id;
4610:
4611: l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
4612:
4613: l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
4614:
4615: l_online_event_id ota_events.event_id%type;
4616: l_online_evt_count number;
4617:
4618: BEGIN
4619: hr_utility.set_location('Entering :'||l_proc,5);
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:
4651: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4652: SELECT
4653: evt.event_id,
4654: evt.event_type,
4655: ocu.synchronous_flag,
4656: ocu.online_flag,
4657: evt.course_start_date,
4658: evt.course_end_date,
4659: ofr.learning_object_id
4660: FROM ota_events evt,
4661: ota_offerings ofr,
4662: ota_category_usages ocu
4663: WHERE evt.event_id = csr_event_id
4664: AND evt.parent_offering_id = ofr.offering_id
4715: order by decode_lesson_status;
4716:
4717: l_proc VARCHAR2(72) := g_package||'get_cert_lo_status';
4718: rec csr_event%rowtype;
4719: l_online_event_id ota_events.event_id%type;
4720:
4721: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
4722:
4723: l_status VARCHAR2(30) := null;
4824: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4825: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
4826:
4827:
4828: CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
4829: csr_cert_period_start_date in date,
4830: csr_cert_period_end_date in date,
4831: csr_person_id in number,
4832: csr_contact_id in number) IS
4837: evt.event_type,
4838: ocu.synchronous_flag,
4839: ocu.online_flag,
4840: bst.type
4841: FROM ota_events evt,
4842: ota_delegate_bookings tdb,
4843: ota_booking_status_types bst,
4844: ota_offerings ofr,
4845: ota_category_usages ocu
4888: l_contact_id number;
4889: l_cert_period_start_date date;
4890: l_cert_period_end_date date;
4891:
4892: l_online_event_id ota_events.event_id%type;
4893: l_online_event_count number := 0;
4894:
4895: BEGIN
4896: hr_utility.set_location('Entering :'||l_proc,5);
5023: AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
5024: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
5025:
5026:
5027: CURSOR get_enrl_status(csr_activity_version_id ota_events.activity_version_id%type,
5028: csr_person_id in number,
5029: csr_contact_id in number) IS
5030: SELECT bst.type status,
5031: tdb.DATE_STATUS_CHANGED,
5033: evt.event_type,
5034: ocu.synchronous_flag,
5035: ocu.online_flag,
5036: bst.type
5037: FROM ota_events evt,
5038: ota_delegate_bookings tdb,
5039: ota_booking_status_types bst,
5040: ota_offerings ofr,
5041: ota_category_usages ocu
5058: l_contact_id number;
5059: l_cert_period_start_date date;
5060: l_cert_period_end_date date;
5061:
5062: l_online_event_id ota_events.event_id%type;
5063: l_online_evt_count number;
5064:
5065: BEGIN
5066: hr_utility.set_location('Entering :'||l_proc,5);
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
5111:
5112: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
5113: SELECT
5114: evt.event_id,
5115: evt.event_type,
5116: ocu.synchronous_flag,
5116: ocu.synchronous_flag,
5117: ocu.online_flag,
5118: evt.course_start_date,
5119: evt.course_end_date
5120: FROM ota_events evt,
5121: ota_offerings ofr,
5122: ota_category_usages ocu
5123: WHERE evt.event_id = csr_event_id
5124: AND evt.parent_offering_id = ofr.offering_id
5125: AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
5126:
5127: l_proc VARCHAR2(72) := g_package||'get_lme_play_button';
5128: rec csr_event%rowtype;
5129: l_online_event_id ota_events.event_id%type;
5130:
5131: l_return_play_btn VARCHAR2(30) := 'DISABLE_NOT_ENROLLED';
5132: l_online_evt_count number;
5133:
5167:
5168: function get_lme_player_toolbar_flag(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5169: return varchar2 is
5170:
5171: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
5172: select ofr.player_toolbar_flag
5173: from ota_events evt,
5174: ota_offerings ofr
5175: where evt.parent_offering_id = ofr.offering_id
5169: return varchar2 is
5170:
5171: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
5172: select ofr.player_toolbar_flag
5173: from ota_events evt,
5174: ota_offerings ofr
5175: where evt.parent_offering_id = ofr.offering_id
5176: and evt.event_id = csr_event_id;
5177:
5178: l_proc VARCHAR2(72) := g_package||'get_lme_player_toolbar_flag';
5179:
5180: l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
5181:
5182: l_online_event_id ota_events.event_id%type;
5183: l_online_evt_count number;
5184:
5185: BEGIN
5186: hr_utility.set_location('Entering :'||l_proc,5);
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:
5218: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
5219: SELECT
5220: evt.event_id,
5221: evt.event_type,
5222: ocu.synchronous_flag,
5223: ocu.online_flag,
5224: evt.course_start_date,
5225: evt.course_end_date,
5226: ofr.learning_object_id
5227: FROM ota_events evt,
5228: ota_offerings ofr,
5229: ota_category_usages ocu
5230: WHERE evt.event_id = csr_event_id
5231: AND evt.parent_offering_id = ofr.offering_id
5268: rec csr_event%rowtype;
5269: rec_prf_ord csr_prf_ord%rowtype;
5270: rec_get_act csr_get_act%rowtype;
5271:
5272: l_online_event_id ota_events.event_id%type;
5273:
5274: l_status VARCHAR2(30) := null;
5275: l_status_name ota_booking_status_types_tl.name%TYPE := null;
5276:
5343: AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
5344: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
5345:
5346:
5347: CURSOR get_enrl_status(csr_activity_version_id ota_events.activity_version_id%type,
5348: csr_person_id in number,
5349: csr_contact_id in number) IS
5350: SELECT bst.type status,
5351: tdb.DATE_STATUS_CHANGED,
5353: evt.event_type,
5354: ocu.synchronous_flag,
5355: ocu.online_flag,
5356: bst.type
5357: FROM ota_events evt,
5358: ota_delegate_bookings tdb,
5359: ota_booking_status_types bst,
5360: ota_offerings ofr,
5361: ota_category_usages ocu
5378: l_contact_id number;
5379: l_cert_period_start_date date;
5380: l_cert_period_end_date date;
5381:
5382: l_online_event_id ota_events.event_id%type;
5383: l_online_event_count number := 0;
5384: l_online_evt_count number;
5385:
5386: BEGIN
5417:
5418: End get_lme_onl_evt_count;
5419:
5420: procedure get_active_cert_prds(
5421: p_event_id ota_events.event_id%type,
5422: p_person_id ota_cert_enrollments.contact_id%type,
5423: p_contact_id ota_cert_enrollments.contact_id%type,
5424: p_cert_prd_enrollment_ids OUT NOCOPY varchar2) is
5425:
5424: p_cert_prd_enrollment_ids OUT NOCOPY varchar2) is
5425:
5426: l_proc VARCHAR2(72) := g_package|| 'get_active_cert_prds';
5427:
5428: l_activity_version_id ota_events.activity_version_id%type;
5429: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
5430: l_cert_prd_enrollment_ids varchar2(4000);
5431:
5432:
5512: RETURN l_return;
5513: --
5514: End Lo_has_cld_and_no_strt_url;
5515:
5516: FUNCTION get_member_in(p_event_id ota_events.event_id%type,
5517: p_person_id ota_cert_enrollments.person_id%type,
5518: p_contact_id ota_cert_enrollments.contact_id%type) RETURN VARCHAR2 IS
5519:
5520: CURSOR csr_is_lp_mbr_person IS
5519:
5520: CURSOR csr_is_lp_mbr_person IS
5521: SELECT lpe.lp_enrollment_id
5522: FROM ota_lp_enrollments lpe,
5523: ota_events oev,
5524: ota_learning_path_members lpm
5525: WHERE lpe.person_id = p_person_id
5526: AND oev.event_id = p_event_id
5527: AND NVL(lpe.is_history_flag, 'N') = 'N'
5532:
5533: CURSOR csr_is_lp_mbr_contact IS
5534: SELECT lpe.lp_enrollment_id
5535: FROM ota_lp_enrollments lpe,
5536: ota_events oev,
5537: ota_learning_path_members lpm
5538: WHERE lpe.contact_id = p_contact_id
5539: AND oev.event_id = p_event_id
5540: AND NVL(lpe.is_history_flag, 'N') = 'N'
5546: CURSOR csr_is_cert_mbr_person IS
5547: SELECT cre.cert_enrollment_id
5548: FROM ota_cert_enrollments cre,
5549: ota_certification_members cmb,
5550: ota_events oev
5551: WHERE cre.person_id = p_person_id
5552: AND oev.event_id = p_event_id
5553: AND NVL(cre.is_history_flag, 'N') = 'N'
5554: AND cre.certification_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5560: CURSOR csr_is_cert_mbr_contact IS
5561: SELECT cre.cert_enrollment_id
5562: FROM ota_cert_enrollments cre,
5563: ota_certification_members cmb,
5564: ota_events oev
5565: WHERE cre.contact_id = p_contact_id
5566: AND oev.event_id = p_event_id
5567: AND NVL(cre.is_history_flag, 'N') = 'N'
5568: AND cre.certification_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5635: --
5636: --modified for 14000818
5637: FUNCTION get_num_of_online_records(p_user_id IN NUMBER,
5638: p_user_type IN ota_attempts.user_type%type,
5639: p_event_id IN ota_events.event_id%TYPE,
5640: p_mode IN number default null,
5641: p_chk_active_cert_flag varchar2 default 'N')
5642: RETURN INTEGER IS
5643:
5644:
5645: --Added for Performance Multiple record Learner page -aswani
5646: cursor c_learning_object_tracktype is
5647: select lo.Tracking_type , lo.learning_object_id
5648: from ota_learning_objects lo, ota_offerings ofr, ota_events oev
5649: where ofr.Learning_object_id = lo.learning_object_id
5650: and oev.parent_offering_id = ofr.offering_id
5651: and oev.event_id = p_event_id ;
5652:
5674: l_proc VARCHAR2(72) := g_package|| 'get_num_of_online_records';
5675:
5676: l_online_flag ota_category_usages.online_flag%TYPE;
5677: l_learning_object_id ota_offerings.learning_object_id%TYPE;
5678: l_course_end_date ota_events.course_end_date%TYPE;
5679: l_sysdate ota_events.course_end_date%TYPE;
5680:
5681: l_trackingtype ota_learning_objects.tracking_type %TYPE;
5682: l_perf_rec_count INTEGER := 0;
5675:
5676: l_online_flag ota_category_usages.online_flag%TYPE;
5677: l_learning_object_id ota_offerings.learning_object_id%TYPE;
5678: l_course_end_date ota_events.course_end_date%TYPE;
5679: l_sysdate ota_events.course_end_date%TYPE;
5680:
5681: l_trackingtype ota_learning_objects.tracking_type %TYPE;
5682: l_perf_rec_count INTEGER := 0;
5683: l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type;