eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.OTA_LO_UTILITY dependencies on OTA_LEARNING_OBJECTS

Line 7: p_lo_id ota_learning_objects.learning_object_id%type,

3:
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,

Line 11: p_starting_url ota_learning_objects.starting_url%type,

7: p_lo_id ota_learning_objects.learning_object_id%type,
8: p_user_id fnd_user.user_id%type,
9: p_user_type ota_attempts.user_type%type,
10: p_old_lesson_status ota_performances.lesson_status%type,
11: p_starting_url ota_learning_objects.starting_url%type,
12: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
13:
14: cursor child_los(
15: p_lo_id ota_learning_objects.learning_object_id%type,

Line 15: p_lo_id ota_learning_objects.learning_object_id%type,

11: p_starting_url ota_learning_objects.starting_url%type,
12: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
13:
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

Line 19: from ota_learning_objects lo, ota_performances p

15: p_lo_id ota_learning_objects.learning_object_id%type,
16: p_user_id fnd_user.user_id%type,
17: p_user_type ota_attempts.user_type%type) is
18: select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
19: from ota_learning_objects lo, ota_performances p
20: where lo.parent_learning_object_id = p_lo_id and
21: lo.published_flag = 'Y' and
22: p.learning_object_id(+) = lo.source_learning_object_id and
23: p.user_id(+) = p_user_id and

Line 67: p_lo_id ota_learning_objects.learning_object_id%type,

63: end compute_default_lesson_status;
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,

Line 71: p_starting_url ota_learning_objects.starting_url%type,

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(

Line 76: p_lo_id ota_learning_objects.learning_object_id%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,

Line 333: p_lo_id ota_learning_objects.learning_object_id%type,

329:
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:

Line 352: p_lo_id ota_learning_objects.learning_object_id%type,

348: hr_utility.set_location('Exiting:'|| l_proc, 15);
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

Line 363: p_lo_id ota_learning_objects.learning_object_id%type,

359: -- which the person is enrolled with a status of 'PLACED'. Note that we
360: -- use source_learning_object_id here in case reuse by reference is ever
361: -- implemented.
362: cursor person_bookings(
363: p_lo_id ota_learning_objects.learning_object_id%type,
364: p_person_id ota_delegate_bookings.delegate_person_id%type) is
365: select book.booking_id,
366: ev.event_id,
367: book.business_group_id,

Line 373: ota_learning_objects lo,

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
377: lo.learning_object_id = offr.learning_object_id and

Line 393: p_lo_id ota_learning_objects.learning_object_id%type,

389: -- This cursor finds all events in all offerings which offer the LO and in
390: -- which the party is enrolled. Note that we use source_learning_object_id
391: -- here in case reuse by reference is ever implemented.
392: cursor party_bookings(
393: p_lo_id ota_learning_objects.learning_object_id%type,
394: p_party_id ota_delegate_bookings.delegate_contact_id%type) is
395: select book.booking_id,
396: ev.event_id,
397: book.business_group_id,

Line 403: ota_learning_objects lo,

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,
407: hz_relationships rel,

Line 549: p_lo_id ota_learning_objects.learning_object_id%type,

545: end update_enroll_status_for_lo;
546:
547:
548: procedure update_cme_status_for_lo(
549: p_lo_id ota_learning_objects.learning_object_id%type,
550: p_date date,
551: p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
552:
553: -- This cursor finds all certification member records in the certification

Line 556: p_learning_object_id ota_learning_objects.learning_object_id%type,

552:
553: -- This cursor finds all certification member records in the certification
554: -- period that area associated with this learning object.
555: cursor cert_member_enrollments(
556: p_learning_object_id ota_learning_objects.learning_object_id%type,
557: p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
558: select distinct(cme.cert_mbr_enrollment_id),
559: cme.member_status_code,
560: cme.object_version_number,

Line 603: p_lo_id ota_learning_objects.learning_object_id%type,

599: end update_cme_status_for_lo;
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,

Line 612: p_lo_id ota_learning_objects.learning_object_id%type,

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,

Line 619: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf

615: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
616: select parent.learning_object_id as learning_object_id,
617: nvl(perf.lesson_status, 'N') as lesson_status,
618: parent.starting_url as starting_url
619: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
620: where child.source_learning_object_id = p_lo_id and
621: child.parent_learning_object_id = parent.learning_object_id and
622: parent.learning_object_id = parent.source_learning_object_id and
623: parent.starting_url is null and

Line 633: from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo

629: union
630: select cr.child_learning_object_id as learning_object_id,
631: nvl(perf.lesson_status, 'N') as lesson_status,
632: lo.starting_url as starting_url
633: from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo
634: where cr.parent_learning_object_id = p_lo_id and
635: cr.child_learning_object_id = lo.learning_object_id and
636: perf.learning_object_id(+) = cr.child_learning_object_id and
637: perf.user_id(+) = p_user_id and

Line 642: v_source_lo_id ota_learning_objects.learning_object_id%type;

638: perf.user_type(+) = p_user_type and
639: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
640: */
641: v_completed_date ota_performances.completed_date%type;
642: v_source_lo_id ota_learning_objects.learning_object_id%type;
643: v_business_group_id ota_learning_objects.business_group_id%type;
644: v_old_lesson_status ota_performances.lesson_status%type;
645: v_new_lesson_status ota_performances.lesson_status%type;
646: v_performance_source ota_performances.source%type;

Line 643: v_business_group_id ota_learning_objects.business_group_id%type;

639: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
640: */
641: v_completed_date ota_performances.completed_date%type;
642: v_source_lo_id ota_learning_objects.learning_object_id%type;
643: v_business_group_id ota_learning_objects.business_group_id%type;
644: v_old_lesson_status ota_performances.lesson_status%type;
645: v_new_lesson_status ota_performances.lesson_status%type;
646: v_performance_source ota_performances.source%type;
647: begin

Line 652: from ota_learning_objects

648: -- The performance is actually stored on the source learning object, so get that ID.
649: -- Also get the business group in case we have to create a new performance record.
650: select source_learning_object_id, business_group_id
651: into v_source_lo_id, v_business_group_id
652: from ota_learning_objects
653: where learning_object_id = p_lo_id;
654:
655: begin
656: -- Find the old status and completed date.

Line 751: -- ota_learning_objects, plus those that indicate this ota_learning_objects as a completion

747: end if;
748: end;
749:
750: -- Cascade the change to any affected RCOs. These include parents of this
751: -- ota_learning_objects, plus those that indicate this ota_learning_objects as a completion
752: -- requirement.
753: for a_lo in affected_los(v_source_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
754: -- if the current status is Passed, nothing can override it, so we will
755: -- save the effort of computing the new status

Line 767: p_lo_id ota_learning_objects.learning_object_id%type,

763: end set_performance_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,

Line 779: p_lo_id ota_learning_objects.learning_object_id%type,

775: end set_performance_lesson_status;
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

Line 790: p_lo_id ota_learning_objects.learning_object_id%type,

786: end set_performance_lesson_status;
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,

Line 799: p_lo_id ota_learning_objects.learning_object_id%type,

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

Line 804: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf

800: p_user_id fnd_user.user_id%type,
801: p_user_type ota_attempts.user_type%type,
802: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
803: select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
804: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
805: where child.source_learning_object_id = p_lo_id and
806: child.parent_learning_object_id = parent.learning_object_id and
807: parent.learning_object_id = parent.source_learning_object_id and
808: parent.starting_url is null and

Line 815: p_lo_id ota_learning_objects.learning_object_id%type,

811: perf.learning_object_id(+) = parent.learning_object_id and
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

Line 822: from ota_learning_objects

818: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
819: select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
820: from ota_performances p,
821: (select distinct learning_object_id, source_learning_object_id, starting_url
822: from ota_learning_objects
823: where learning_object_id <> p_lo_id
824: start with learning_object_id = p_lo_id
825: connect by parent_learning_object_id = prior learning_object_id) lo
826: where lo.starting_url is not null and

Line 832: v_source_lo_id ota_learning_objects.learning_object_id%type;

828: p.user_id(+) = p_user_id and
829: p.user_type(+) = p_user_type and
830: nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
831:
832: v_source_lo_id ota_learning_objects.learning_object_id%type;
833: v_business_group_id ota_learning_objects.business_group_id%type;
834: v_old_time ota_performances.time%type;
835: v_new_time ota_performances.time%type;
836: v_performance_source ota_performances.source%type;

Line 833: v_business_group_id ota_learning_objects.business_group_id%type;

829: p.user_type(+) = p_user_type and
830: nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
831:
832: v_source_lo_id ota_learning_objects.learning_object_id%type;
833: v_business_group_id ota_learning_objects.business_group_id%type;
834: v_old_time ota_performances.time%type;
835: v_new_time ota_performances.time%type;
836: v_performance_source ota_performances.source%type;
837: begin

Line 842: from ota_learning_objects

838: -- The performance is actually stored on the source learning object, so get that ID.
839: -- Also get the business group in case we have to create a new performance record.
840: select source_learning_object_id, business_group_id
841: into v_source_lo_id, v_business_group_id
842: from ota_learning_objects
843: where learning_object_id = p_lo_id;
844:
845: begin
846: select p.time

Line 891: -- Get all the source-parents of all the targets of the ota_learning_objects

887: return;
888: end if;
889: end;
890:
891: -- Get all the source-parents of all the targets of the ota_learning_objects
892: for a_parent_lo in parent_los(p_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
893: v_new_time := 0;
894: for a_child_lo in distinct_child_los(a_parent_lo.learning_object_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
895: if a_child_lo.time > 0 then

Line 908: p_lo_id ota_learning_objects.learning_object_id%type,

904: end set_performance_time;
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,

Line 920: p_lo_id ota_learning_objects.learning_object_id%type,

916: end set_performance_time;
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

Line 931: p_lo_id ota_learning_objects.learning_object_id%type,

927: end set_performance_time;
928:
929:
930: function lo_is_attemptable(
931: p_lo_id ota_learning_objects.learning_object_id%type,
932: p_launch_type ota_attempts.launch_type%type,
933: p_reason out nocopy number) return boolean is
934:
935: v_published_flag ota_learning_objects.published_flag%type;

Line 935: v_published_flag ota_learning_objects.published_flag%type;

931: p_lo_id ota_learning_objects.learning_object_id%type,
932: p_launch_type ota_attempts.launch_type%type,
933: p_reason out nocopy number) return boolean is
934:
935: v_published_flag ota_learning_objects.published_flag%type;
936: v_starting_url ota_learning_objects.starting_url%type;
937: v_start_date_active ota_learning_objects.start_date_active%type;
938: v_end_date_active ota_learning_objects.end_date_active%type;
939:

Line 936: v_starting_url ota_learning_objects.starting_url%type;

932: p_launch_type ota_attempts.launch_type%type,
933: p_reason out nocopy number) return boolean is
934:
935: v_published_flag ota_learning_objects.published_flag%type;
936: v_starting_url ota_learning_objects.starting_url%type;
937: v_start_date_active ota_learning_objects.start_date_active%type;
938: v_end_date_active ota_learning_objects.end_date_active%type;
939:
940: begin

Line 937: v_start_date_active ota_learning_objects.start_date_active%type;

933: p_reason out nocopy number) return boolean is
934:
935: v_published_flag ota_learning_objects.published_flag%type;
936: v_starting_url ota_learning_objects.starting_url%type;
937: v_start_date_active ota_learning_objects.start_date_active%type;
938: v_end_date_active ota_learning_objects.end_date_active%type;
939:
940: begin
941: select published_flag,

Line 938: v_end_date_active ota_learning_objects.end_date_active%type;

934:
935: v_published_flag ota_learning_objects.published_flag%type;
936: v_starting_url ota_learning_objects.starting_url%type;
937: v_start_date_active ota_learning_objects.start_date_active%type;
938: v_end_date_active ota_learning_objects.end_date_active%type;
939:
940: begin
941: select published_flag,
942: starting_url,

Line 949: from ota_learning_objects

945: into v_published_flag,
946: v_starting_url,
947: v_start_date_active,
948: v_end_date_active
949: from ota_learning_objects
950: where learning_object_id = p_lo_id;
951:
952: if v_published_flag = 'N' then
953: p_reason := LO_REASON_NOT_PUBLISHED;

Line 974: p_lo_id ota_learning_objects.learning_object_id%type) return boolean is

970:
971:
972: function lo_is_in_event(
973: p_event_id ota_events.event_id%type,
974: p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
975:
976: v_dummy char(1);
977: v_root_lo_id ota_learning_objects.learning_object_id%type;
978: begin

Line 977: v_root_lo_id ota_learning_objects.learning_object_id%type;

973: p_event_id ota_events.event_id%type,
974: p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
975:
976: v_dummy char(1);
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

Line 987: from ota_learning_objects

983: e.parent_offering_id = o.offering_id;
984:
985: select 'X'
986: into v_dummy
987: from ota_learning_objects
988: where learning_object_id = p_lo_id
989: start with learning_object_id = v_root_lo_id
990: connect by parent_learning_object_id = prior learning_object_id;
991:

Line 1000: p_lo_id ota_learning_objects.learning_object_id%type,

996: end lo_is_in_event;
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

Line 1064: p_lo_id ota_learning_objects.learning_object_id%type,

1060: end user_meets_prerequisites;
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:

Line 1075: from ota_tests t, ota_learning_objects lo

1071: begin
1072: begin
1073: select t.max_attempts
1074: into v_max_attempts
1075: from ota_tests t, ota_learning_objects lo
1076: where lo.learning_object_id = p_lo_id and
1077: lo.test_id = t.test_id;
1078: exception
1079: when NO_DATA_FOUND then -- the LO is not a test, so no attempt limit exists

Line 1116: p_lo_id ota_learning_objects.learning_object_id%type,

1112: end user_exceeded_attempt_limit;
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:

Line 1126: ota_learning_objects lo,

1122: begin
1123: select nvl(max(a.timestamp), sysdate) + nvl(t.duration_between_attempt, 0)
1124: into v_earliest_attempt_date
1125: from ota_tests t,
1126: ota_learning_objects lo,
1127: ota_attempts a
1128: where t.test_id = a.test_id
1129: and a.initialized_flag = 'Y'
1130: and ((t.resume_flag = 'Y'

Line 1153: p_lo_id ota_learning_objects.learning_object_id%type,

1149: end get_next_attempt_date_for_lo;
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:

Line 1171: p_lo_id ota_learning_objects.learning_object_id%type,

1167: end user_must_wait_to_attempt;
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,

Line 1210: p_lo_id ota_learning_objects.learning_object_id%type,

1206: end user_can_attempt_lo;
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

Line 1227: p_lo_id ota_learning_objects.learning_object_id%type,

1223: end user_can_attempt_lo;
1224:
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,

Line 1249: p_lo_id ota_learning_objects.learning_object_id%type) return boolean is

1245: end user_can_attempt_lo_3;
1246:
1247: function lo_is_in_cert(
1248: p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type,
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

Line 1258: cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,

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:
1258: cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1259: p_root_lo_id ota_learning_objects.learning_object_id%type) is
1260: select learning_object_id
1261: from ota_learning_objects
1262: where learning_object_id = p_lo_id

Line 1259: p_root_lo_id ota_learning_objects.learning_object_id%type) is

1255: and cm.certification_member_id = cme.cert_member_id
1256: and cme.cert_prd_enrollment_id = p_cert_prd_enroll_id;
1257:
1258: cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1259: p_root_lo_id ota_learning_objects.learning_object_id%type) is
1260: select learning_object_id
1261: from ota_learning_objects
1262: where learning_object_id = p_lo_id
1263: start with learning_object_id = p_root_lo_id

Line 1261: from ota_learning_objects

1257:
1258: cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1259: p_root_lo_id ota_learning_objects.learning_object_id%type) is
1260: select learning_object_id
1261: from ota_learning_objects
1262: where learning_object_id = p_lo_id
1263: start with learning_object_id = p_root_lo_id
1264: connect by parent_learning_object_id = prior learning_object_id;
1265:

Line 1279: p_lo_id ota_learning_objects.learning_object_id%type,

1275:
1276: end lo_is_in_cert;
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,

Line 1556: p_start ota_learning_objects.learning_object_id%type,

1552: end user_can_attempt_cert;
1553:
1554:
1555: function find_previous_lo_id(
1556: p_start ota_learning_objects.learning_object_id%type,
1557: p_current ota_learning_objects.learning_object_id%type,
1558: p_current_starting_url ota_learning_objects.starting_url%type,
1559: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1560:

Line 1557: p_current ota_learning_objects.learning_object_id%type,

1553:
1554:
1555: function find_previous_lo_id(
1556: p_start ota_learning_objects.learning_object_id%type,
1557: p_current ota_learning_objects.learning_object_id%type,
1558: p_current_starting_url ota_learning_objects.starting_url%type,
1559: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1560:
1561: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is

Line 1558: p_current_starting_url ota_learning_objects.starting_url%type,

1554:
1555: function find_previous_lo_id(
1556: p_start ota_learning_objects.learning_object_id%type,
1557: p_current ota_learning_objects.learning_object_id%type,
1558: p_current_starting_url ota_learning_objects.starting_url%type,
1559: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1560:
1561: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1562: select learning_object_id, starting_url

Line 1559: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is

1555: function find_previous_lo_id(
1556: p_start ota_learning_objects.learning_object_id%type,
1557: p_current ota_learning_objects.learning_object_id%type,
1558: p_current_starting_url ota_learning_objects.starting_url%type,
1559: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1560:
1561: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1562: select learning_object_id, starting_url
1563: from ota_learning_objects

Line 1561: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is

1557: p_current ota_learning_objects.learning_object_id%type,
1558: p_current_starting_url ota_learning_objects.starting_url%type,
1559: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1560:
1561: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1562: select learning_object_id, starting_url
1563: from ota_learning_objects
1564: where parent_learning_object_id = p_lo_id and
1565: published_flag = 'Y'

Line 1563: from ota_learning_objects

1559: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1560:
1561: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1562: select learning_object_id, starting_url
1563: from ota_learning_objects
1564: where parent_learning_object_id = p_lo_id and
1565: published_flag = 'Y'
1566: order by child_seq asc;
1567: begin

Line 1587: p_root_lo_id ota_learning_objects.learning_object_id%type,

1583: end find_previous_lo_id;
1584:
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,

Line 1588: p_starting_lo_id ota_learning_objects.learning_object_id%type,

1584:
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

Line 1592: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

1588: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1589: p_user_id fnd_user.user_id%type,
1590: p_user_type ota_attempts.user_type%type,
1591: p_launch_type ota_attempts.launch_type%type,
1592: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1593:
1594: v_starting_url ota_learning_objects.starting_url%type :=null;
1595: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1596: v_reason number;

Line 1594: v_starting_url ota_learning_objects.starting_url%type :=null;

1590: p_user_type ota_attempts.user_type%type,
1591: p_launch_type ota_attempts.launch_type%type,
1592: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1593:
1594: v_starting_url ota_learning_objects.starting_url%type :=null;
1595: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1596: v_reason number;
1597: begin
1598: select starting_url

Line 1595: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;

1591: p_launch_type ota_attempts.launch_type%type,
1592: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1593:
1594: v_starting_url ota_learning_objects.starting_url%type :=null;
1595: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1596: v_reason number;
1597: begin
1598: select starting_url
1599: into v_starting_url

Line 1600: from ota_learning_objects

1596: v_reason number;
1597: begin
1598: select starting_url
1599: into v_starting_url
1600: from ota_learning_objects
1601: where learning_object_id = p_root_lo_id;
1602:
1603: if find_previous_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_previous_lo_id) then
1604: if user_can_attempt_lo(v_previous_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id, v_reason) then

Line 1617: p_starting_lo_id ota_learning_objects.learning_object_id%type,

1613:
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

Line 1621: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

1617: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1618: p_user_id fnd_user.user_id%type,
1619: p_user_type ota_attempts.user_type%type,
1620: p_launch_type ota_attempts.launch_type%type,
1621: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1622:
1623: v_root_lo_id ota_learning_objects.learning_object_id%type;
1624: begin
1625: select o.learning_object_id

Line 1623: v_root_lo_id ota_learning_objects.learning_object_id%type;

1619: p_user_type ota_attempts.user_type%type,
1620: p_launch_type ota_attempts.launch_type%type,
1621: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1622:
1623: v_root_lo_id ota_learning_objects.learning_object_id%type;
1624: begin
1625: select o.learning_object_id
1626: into v_root_lo_id
1627: from ota_events e, ota_offerings o

Line 1636: p_start ota_learning_objects.learning_object_id%type,

1632: end get_previous_event_lo_id;
1633:
1634:
1635: function find_next_lo_id(
1636: p_start ota_learning_objects.learning_object_id%type,
1637: p_current ota_learning_objects.learning_object_id%type,
1638: p_current_starting_url ota_learning_objects.starting_url%type,
1639: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1640:

Line 1637: p_current ota_learning_objects.learning_object_id%type,

1633:
1634:
1635: function find_next_lo_id(
1636: p_start ota_learning_objects.learning_object_id%type,
1637: p_current ota_learning_objects.learning_object_id%type,
1638: p_current_starting_url ota_learning_objects.starting_url%type,
1639: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1640:
1641: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is

Line 1638: p_current_starting_url ota_learning_objects.starting_url%type,

1634:
1635: function find_next_lo_id(
1636: p_start ota_learning_objects.learning_object_id%type,
1637: p_current ota_learning_objects.learning_object_id%type,
1638: p_current_starting_url ota_learning_objects.starting_url%type,
1639: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1640:
1641: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1642: select learning_object_id, starting_url

Line 1639: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is

1635: function find_next_lo_id(
1636: p_start ota_learning_objects.learning_object_id%type,
1637: p_current ota_learning_objects.learning_object_id%type,
1638: p_current_starting_url ota_learning_objects.starting_url%type,
1639: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1640:
1641: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1642: select learning_object_id, starting_url
1643: from ota_learning_objects

Line 1641: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is

1637: p_current ota_learning_objects.learning_object_id%type,
1638: p_current_starting_url ota_learning_objects.starting_url%type,
1639: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1640:
1641: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1642: select learning_object_id, starting_url
1643: from ota_learning_objects
1644: where parent_learning_object_id = p_lo_id and
1645: published_flag = 'Y'

Line 1643: from ota_learning_objects

1639: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1640:
1641: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1642: select learning_object_id, starting_url
1643: from ota_learning_objects
1644: where parent_learning_object_id = p_lo_id and
1645: published_flag = 'Y'
1646: order by child_seq asc;
1647: v_result ota_learning_objects.learning_object_id%type;

Line 1647: v_result ota_learning_objects.learning_object_id%type;

1643: from ota_learning_objects
1644: where parent_learning_object_id = p_lo_id and
1645: published_flag = 'Y'
1646: order by child_seq asc;
1647: v_result ota_learning_objects.learning_object_id%type;
1648: begin
1649: if (p_found_start or p_start is null) and p_current_starting_url is not null then
1650: return p_current;
1651: end if;

Line 1669: p_root_lo_id ota_learning_objects.learning_object_id%type,

1665: end find_next_lo_id;
1666:
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,

Line 1670: p_starting_lo_id ota_learning_objects.learning_object_id%type,

1666:
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

Line 1674: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

1670: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1671: p_user_id fnd_user.user_id%type,
1672: p_user_type ota_attempts.user_type%type,
1673: p_launch_type ota_attempts.launch_type%type,
1674: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1675:
1676:
1677: v_starting_url ota_learning_objects.starting_url%type := null;
1678: v_next_lo_id ota_learning_objects.learning_object_id%type := null;

Line 1677: v_starting_url ota_learning_objects.starting_url%type := null;

1673: p_launch_type ota_attempts.launch_type%type,
1674: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1675:
1676:
1677: v_starting_url ota_learning_objects.starting_url%type := null;
1678: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1679: v_false boolean := false;
1680: v_reason number;
1681: begin

Line 1678: v_next_lo_id ota_learning_objects.learning_object_id%type := null;

1674: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1675:
1676:
1677: v_starting_url ota_learning_objects.starting_url%type := null;
1678: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1679: v_false boolean := false;
1680: v_reason number;
1681: begin
1682: return get_next_lo_id(p_root_lo_id,null, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);

Line 1688: p_root_lo_id ota_learning_objects.learning_object_id%type,

1684:
1685:
1686:
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,

Line 1689: p_root_starting_url ota_learning_objects.starting_url%type,

1685:
1686:
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,

Line 1690: p_starting_lo_id ota_learning_objects.learning_object_id%type,

1686:
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

Line 1694: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

1690: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1691: p_user_id fnd_user.user_id%type,
1692: p_user_type ota_attempts.user_type%type,
1693: p_launch_type ota_attempts.launch_type%type,
1694: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1695:
1696: v_starting_url ota_learning_objects.starting_url%type := null;
1697: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1698: v_false boolean := false;

Line 1696: v_starting_url ota_learning_objects.starting_url%type := null;

1692: p_user_type ota_attempts.user_type%type,
1693: p_launch_type ota_attempts.launch_type%type,
1694: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1695:
1696: v_starting_url ota_learning_objects.starting_url%type := null;
1697: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1698: v_false boolean := false;
1699: v_reason number;
1700: begin

Line 1697: v_next_lo_id ota_learning_objects.learning_object_id%type := null;

1693: p_launch_type ota_attempts.launch_type%type,
1694: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1695:
1696: v_starting_url ota_learning_objects.starting_url%type := null;
1697: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1698: v_false boolean := false;
1699: v_reason number;
1700: begin
1701:

Line 1707: from ota_learning_objects

1703: v_starting_url := p_root_starting_url;
1704: else
1705: select starting_url
1706: into v_starting_url
1707: from ota_learning_objects
1708: where learning_object_id = p_root_lo_id;
1709: end if;
1710:
1711: v_next_lo_id := find_next_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_false);

Line 1726: p_starting_lo_id ota_learning_objects.learning_object_id%type,

1722:
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

Line 1730: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

1726: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1727: p_user_id fnd_user.user_id%type,
1728: p_user_type ota_attempts.user_type%type,
1729: p_launch_type ota_attempts.launch_type%type,
1730: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1731:
1732: v_root_lo_id ota_learning_objects.learning_object_id%type;
1733: begin
1734: select o.learning_object_id

Line 1732: v_root_lo_id ota_learning_objects.learning_object_id%type;

1728: p_user_type ota_attempts.user_type%type,
1729: p_launch_type ota_attempts.launch_type%type,
1730: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1731:
1732: v_root_lo_id ota_learning_objects.learning_object_id%type;
1733: begin
1734: select o.learning_object_id
1735: into v_root_lo_id
1736: from ota_events e, ota_offerings o

Line 1749: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is

1745: p_event_id ota_events.event_id%type,
1746: p_user_id fnd_user.user_id%type,
1747: p_user_type ota_attempts.user_type%type,
1748: p_launch_type ota_attempts.launch_type%type,
1749: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
1750:
1751: cursor ordered_los(
1752: p_root_lo_id ota_learning_objects.learning_object_id%type,
1753: p_user_id fnd_user.user_id%type,

Line 1752: p_root_lo_id ota_learning_objects.learning_object_id%type,

1748: p_launch_type ota_attempts.launch_type%type,
1749: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
1750:
1751: cursor ordered_los(
1752: p_root_lo_id ota_learning_objects.learning_object_id%type,
1753: p_user_id fnd_user.user_id%type,
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

Line 1759: from ota_learning_objects

1755: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
1756: select a.learning_object_id, max(a.attempt_id) max_attempt
1757: from ota_attempts a,
1758: (select learning_object_id, starting_url
1759: from ota_learning_objects
1760: start with learning_object_id = p_root_lo_id
1761: connect by parent_learning_object_id = prior learning_object_id) lo
1762: where a.user_id = p_user_id and
1763: a.user_type = p_user_type and

Line 1772: v_root_lo_id ota_learning_objects.learning_object_id%type;

1768: )
1769: group by a.learning_object_id
1770: order by max_attempt desc;
1771:
1772: v_root_lo_id ota_learning_objects.learning_object_id%type;
1773: v_reason number;
1774: begin
1775: select o.learning_object_id
1776: into v_root_lo_id

Line 1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

1793: p_event_id ota_events.event_id%type,
1794: p_user_id fnd_user.user_id%type,
1795: p_user_type ota_attempts.user_type%type,
1796: p_launch_type ota_attempts.launch_type%type,
1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1798:
1799: v_root_lo_id ota_learning_objects.learning_object_id%type;
1800: v_first_lo_id ota_learning_objects.learning_object_id%type;
1801: v_reason number;

Line 1799: v_root_lo_id ota_learning_objects.learning_object_id%type;

1795: p_user_type ota_attempts.user_type%type,
1796: p_launch_type ota_attempts.launch_type%type,
1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1798:
1799: v_root_lo_id ota_learning_objects.learning_object_id%type;
1800: v_first_lo_id ota_learning_objects.learning_object_id%type;
1801: v_reason number;
1802: begin
1803: select o.learning_object_id

Line 1800: v_first_lo_id ota_learning_objects.learning_object_id%type;

1796: p_launch_type ota_attempts.launch_type%type,
1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1798:
1799: v_root_lo_id ota_learning_objects.learning_object_id%type;
1800: v_first_lo_id ota_learning_objects.learning_object_id%type;
1801: v_reason number;
1802: begin
1803: select o.learning_object_id
1804: into v_root_lo_id

Line 1813: p_lo_id ota_learning_objects.learning_object_id%type,

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);
1810: end get_first_lo_id;
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,

Line 1818: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is

1814: p_event_id ota_events.event_id%type,
1815: p_user_id fnd_user.user_id%type,
1816: p_user_type ota_attempts.user_type%type,
1817: p_launch_type ota_attempts.launch_type%type,
1818: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1819: begin
1820: return get_jump_lo_id(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, null, p_reason);
1821: end get_jump_lo_id;
1822:

Line 1824: p_lo_id ota_learning_objects.learning_object_id%type,

1820: return get_jump_lo_id(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, null, p_reason);
1821: end get_jump_lo_id;
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,

Line 1830: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is

1826: p_user_id fnd_user.user_id%type,
1827: p_user_type ota_attempts.user_type%type,
1828: p_launch_type ota_attempts.launch_type%type,
1829: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1830: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1831: begin
1832: if user_can_attempt_lo(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id, p_reason) then
1833: return p_lo_id;
1834: else

Line 1841: p_lo_id ota_learning_objects.learning_object_id%type) return varchar2 is

1837: end get_jump_lo_id;
1838:
1839: --7574667
1840: function get_lo_type(
1841: p_lo_id ota_learning_objects.learning_object_id%type) return varchar2 is
1842:
1843: --7622768
1844: cursor lo_type is
1845: select nvl(test_type_flag,'LO')

Line 1847: ota_learning_objects lo

1843: --7622768
1844: cursor lo_type is
1845: select nvl(test_type_flag,'LO')
1846: from ota_tests ot,
1847: ota_learning_objects lo
1848: where ot.test_id(+) = lo.test_id
1849: and lo.learning_object_id = p_lo_id;
1850:
1851: type_flag varchar2(10);

Line 1962: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,

1958: , to_char(opf.completed_date)
1959: , to_char(opf.completed_date, 'HH24:MI:SS')
1960: , opf.lesson_status
1961: , 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
1962: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
1963: OTA_TESTS tst
1964: Where
1965: olo.learning_object_id = opf.learning_object_id(+)
1966: AND tst.test_id(+) = olo.test_id

Line 2096: v_solo_lo_id ota_learning_objects.learning_object_id%type := null;

2092: p_chk_active_cert_flag varchar2 default 'N') return varchar2 is
2093:
2094:
2095: v_number_of_los number(10);
2096: v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
2097: v_published_flag ota_learning_objects.published_flag%type := null;
2098: v_first_lo ota_learning_objects.learning_object_id%type := null;
2099: v_play_button_for_test varchar2(100) := null;
2100: v_rco_id ota_activity_versions.rco_id%type := null;

Line 2097: v_published_flag ota_learning_objects.published_flag%type := null;

2093:
2094:
2095: v_number_of_los number(10);
2096: v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
2097: v_published_flag ota_learning_objects.published_flag%type := null;
2098: v_first_lo ota_learning_objects.learning_object_id%type := null;
2099: v_play_button_for_test varchar2(100) := null;
2100: v_rco_id ota_activity_versions.rco_id%type := null;
2101: v_offering_id ota_events.offering_id%type := null;

Line 2098: v_first_lo ota_learning_objects.learning_object_id%type := null;

2094:
2095: v_number_of_los number(10);
2096: v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
2097: v_published_flag ota_learning_objects.published_flag%type := null;
2098: v_first_lo ota_learning_objects.learning_object_id%type := null;
2099: v_play_button_for_test varchar2(100) := null;
2100: v_rco_id ota_activity_versions.rco_id%type := null;
2101: v_offering_id ota_events.offering_id%type := null;
2102:

Line 2210: from ota_learning_objects

2206: ,nvl(max(published_flag),'N')
2207: into v_number_of_los
2208: ,v_solo_lo_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

Line 2305: v_lo_id ota_learning_objects.learning_object_id%type;

2301: p_user_type ota_attempts.user_type%type,
2302: p_event_id ota_events.event_id%type,
2303: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2304:
2305: v_lo_id ota_learning_objects.learning_object_id%type;
2306: begin
2307: select offr.learning_object_id
2308: into v_lo_id
2309: from ota_offerings offr, ota_events evt

Line 2559: p_lo_id ota_learning_objects.learning_object_id%type,

2555: -- ---------------------------------------------------------------------------
2556: function get_wait_duration_for_lo(
2557: p_user_id fnd_user.user_id%type,
2558: p_user_type ota_attempts.user_type%type,
2559: p_lo_id ota_learning_objects.learning_object_id%type,
2560: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2561:
2562: v_earliest_attempt_date date;
2563: begin

Line 2592: v_lo_id ota_learning_objects.learning_object_id%type;

2588: p_user_type ota_attempts.user_type%type,
2589: p_event_id ota_events.event_id%type,
2590: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2591:
2592: v_lo_id ota_learning_objects.learning_object_id%type;
2593: v_wait_duration varchar2(80);
2594: --Bug 5166350
2595: wait_dur_date varchar2(20);
2596: wait_dur_time varchar2(20);

Line 3230: p_lo_id ota_learning_objects.learning_object_id%TYPE,

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
3234:

Line 3367: l_lo_id ota_learning_objects.learning_object_id%type;

3363: RETURN varchar2
3364: IS
3365: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date';
3366: l_event_id ota_events.event_id%type;
3367: l_lo_id ota_learning_objects.learning_object_id%type;
3368: -- l_lo_completed_date VARCHAR2(100);
3369: l_lo_completed_date DATE;
3370: l_lo_completed_time VARCHAR2(10);
3371: l_lo_completed_date_tz VARCHAR2(100);

Line 3459: l_lo_id ota_learning_objects.learning_object_id%type;

3455: RETURN date
3456: IS
3457: l_proc VARCHAR2(72) := g_package||'get_lo_completion_date_time';
3458: l_event_id ota_events.event_id%type;
3459: l_lo_id ota_learning_objects.learning_object_id%type;
3460: l_lo_completed_date DATE;
3461: l_lo_comp_date DATE;
3462: l_lo_completed_time VARCHAR2(10);
3463: l_lo_completed_date_tz VARCHAR2(100);

Line 3707: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,

3703: , to_char(opf.completed_date)
3704: , to_char(opf.completed_date, 'HH24:MI:SS')
3705: , opf.lesson_status
3706: , 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
3707: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
3708: OTA_TESTS tst
3709: Where
3710: olo.learning_object_id = opf.learning_object_id(+)
3711: AND tst.test_id(+) = olo.test_id

Line 4131: ota_learning_objects lo

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
4135: and lo.learning_object_id = prf.learning_object_id

Line 4681: ota_learning_objects lo

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
4685: and lo.learning_object_id = prf.learning_object_id

Line 4927: From ota_learning_objects cld, ota_learning_objects par

4923: --
4924: -- decalare cursor
4925: Cursor csr_par_with_cld_no_url is
4926: Select null
4927: From ota_learning_objects cld, ota_learning_objects par
4928: Where par.learning_object_id = cld.parent_learning_object_id
4929: And par.starting_url is null
4930: And par.learning_object_id = p_learning_object_id
4931: And rownum = 1;