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.

APPS.OTA_LO_UTILITY dependencies on OTA_LEARNING_OBJECTS

Line 69: p_lo_id ota_learning_objects.learning_object_id%type,

65: );
66:
67:
68: function compute_default_lesson_status(
69: p_lo_id ota_learning_objects.learning_object_id%type,
70: p_user_id fnd_user.user_id%type,
71: p_user_type ota_attempts.user_type%type,
72: p_old_lesson_status ota_performances.lesson_status%type,
73: p_starting_url ota_learning_objects.starting_url%type,

Line 73: p_starting_url ota_learning_objects.starting_url%type,

69: p_lo_id ota_learning_objects.learning_object_id%type,
70: p_user_id fnd_user.user_id%type,
71: p_user_type ota_attempts.user_type%type,
72: p_old_lesson_status ota_performances.lesson_status%type,
73: p_starting_url ota_learning_objects.starting_url%type,
74: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
75: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
76:
77: cursor child_los(

Line 78: p_lo_id ota_learning_objects.learning_object_id%type,

74: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
75: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
76:
77: cursor child_los(
78: p_lo_id ota_learning_objects.learning_object_id%type,
79: p_user_id fnd_user.user_id%type,
80: p_user_type ota_attempts.user_type%type) is
81: select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
82: from ota_learning_objects lo, ota_performances p

Line 82: from ota_learning_objects lo, ota_performances p

78: p_lo_id ota_learning_objects.learning_object_id%type,
79: p_user_id fnd_user.user_id%type,
80: p_user_type ota_attempts.user_type%type) is
81: select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
82: from ota_learning_objects lo, ota_performances p
83: where lo.parent_learning_object_id = p_lo_id and
84: lo.published_flag = 'Y' and
85: p.learning_object_id(+) = lo.source_learning_object_id and
86: p.user_id(+) = p_user_id and

Line 131: p_lo_id ota_learning_objects.learning_object_id%type,

127: end compute_default_lesson_status;
128:
129:
130: function compute_lesson_status(
131: p_lo_id ota_learning_objects.learning_object_id%type,
132: p_user_id fnd_user.user_id%type,
133: p_user_type ota_attempts.user_type%type,
134: p_old_lesson_status ota_performances.lesson_status%type,
135: p_starting_url ota_learning_objects.starting_url%type,

Line 135: p_starting_url ota_learning_objects.starting_url%type,

131: p_lo_id ota_learning_objects.learning_object_id%type,
132: p_user_id fnd_user.user_id%type,
133: p_user_type ota_attempts.user_type%type,
134: p_old_lesson_status ota_performances.lesson_status%type,
135: p_starting_url ota_learning_objects.starting_url%type,
136: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
137: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
138:
139: /* Completion reqs not yet implemented - GDHUTTON 12/24/03

Line 141: p_lo_id ota_learning_objects.learning_object_id%type,

137: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
138:
139: /* Completion reqs not yet implemented - GDHUTTON 12/24/03
140: cursor completion_requirements(
141: p_lo_id ota_learning_objects.learning_object_id%type,
142: p_user_id fnd_user.user_id%type,
143: p_user_type ota_attempts.user_type%type,
144: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
145: select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,

Line 218: p_lo_id ota_learning_objects.learning_object_id%type,

214: end get_contact_id_for_party;
215:
216:
217: procedure update_cme_status_for_lo(
218: p_lo_id ota_learning_objects.learning_object_id%type,
219: p_date date,
220: p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
221:
222: -- This cursor finds all certification member records in the certification

Line 225: p_learning_object_id ota_learning_objects.learning_object_id%type,

221:
222: -- This cursor finds all certification member records in the certification
223: -- period that area associated with this learning object.
224: cursor cert_member_enrollments(
225: p_learning_object_id ota_learning_objects.learning_object_id%type,
226: p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
227: select distinct(cme.cert_mbr_enrollment_id),
228: cme.member_status_code,
229: cme.object_version_number,

Line 625: p_lo_id ota_learning_objects.learning_object_id%type,

621:
622:
623:
624: procedure update_enroll_status_for_lo(
625: p_lo_id ota_learning_objects.learning_object_id%type,
626: p_user_id fnd_user.user_id%type,
627: p_user_type ota_attempts.user_type%type,
628: p_date date) is
629:

Line 644: p_lo_id ota_learning_objects.learning_object_id%type,

640: hr_utility.set_location('Exiting:'|| l_proc, 15);
641: end update_enroll_status_for_lo;
642:
643: procedure update_enroll_status_for_lo(
644: p_lo_id ota_learning_objects.learning_object_id%type,
645: p_user_id fnd_user.user_id%type,
646: p_user_type ota_attempts.user_type%type,
647: p_date date,
648: p_failed varchar2) is

Line 655: p_lo_id ota_learning_objects.learning_object_id%type,

651: -- which the person is enrolled with a status of 'PLACED'. Note that we
652: -- use source_learning_object_id here in case reuse by reference is ever
653: -- implemented.
654: cursor person_bookings(
655: p_lo_id ota_learning_objects.learning_object_id%type,
656: p_person_id ota_delegate_bookings.delegate_person_id%type) is
657: select book.booking_id,
658: book.sign_eval_status,
659: ev.event_id,

Line 669: ota_learning_objects lo,

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,
672: ota_activity_versions act
673: where lo.source_learning_object_id = p_lo_id and

Line 691: p_lo_id ota_learning_objects.learning_object_id%type,

687: -- This cursor finds all events in all offerings which offer the LO and in
688: -- which the party is enrolled. Note that we use source_learning_object_id
689: -- here in case reuse by reference is ever implemented.
690: cursor party_bookings(
691: p_lo_id ota_learning_objects.learning_object_id%type,
692: p_party_id ota_delegate_bookings.delegate_contact_id%type) is
693: select book.booking_id,
694: book.sign_eval_status,
695: ev.event_id,

Line 705: ota_learning_objects lo,

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,
708: hz_cust_account_roles acct_role,
709: hz_relationships rel,

Line 871: p_lo_id ota_learning_objects.learning_object_id%type,

867:
868:
869:
870: procedure set_performance_lesson_status(
871: p_lo_id ota_learning_objects.learning_object_id%type,
872: p_user_id fnd_user.user_id%type,
873: p_user_type ota_attempts.user_type%type,
874: p_lesson_status ota_performances.lesson_status%type,
875: p_cascaded boolean,

Line 881: p_lo_id ota_learning_objects.learning_object_id%type,

877: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
878: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
879:
880: cursor affected_los(
881: p_lo_id ota_learning_objects.learning_object_id%type,
882: p_user_id fnd_user.user_id%type,
883: p_user_type ota_attempts.user_type%type,
884: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
885: select parent.learning_object_id as learning_object_id,

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

884: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
885: select parent.learning_object_id as learning_object_id,
886: nvl(perf.lesson_status, 'N') as lesson_status,
887: parent.starting_url as starting_url
888: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
889: where child.source_learning_object_id = p_lo_id and
890: child.parent_learning_object_id = parent.learning_object_id and
891: parent.learning_object_id = parent.source_learning_object_id and
892: parent.starting_url is null and

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

899: union
900: select cr.child_learning_object_id as learning_object_id,
901: nvl(perf.lesson_status, 'N') as lesson_status,
902: lo.starting_url as starting_url
903: from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo
904: where cr.parent_learning_object_id = p_lo_id and
905: cr.child_learning_object_id = lo.learning_object_id and
906: perf.learning_object_id(+) = cr.child_learning_object_id and
907: perf.user_id(+) = p_user_id and

Line 912: v_source_lo_id ota_learning_objects.learning_object_id%type;

908: perf.user_type(+) = p_user_type and
909: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
910: */
911: v_completed_date ota_performances.completed_date%type;
912: v_source_lo_id ota_learning_objects.learning_object_id%type;
913: v_business_group_id ota_learning_objects.business_group_id%type;
914: v_old_lesson_status ota_performances.lesson_status%type;
915: v_new_lesson_status ota_performances.lesson_status%type;
916: v_performance_source ota_performances.source%type;

Line 913: v_business_group_id ota_learning_objects.business_group_id%type;

909: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
910: */
911: v_completed_date ota_performances.completed_date%type;
912: v_source_lo_id ota_learning_objects.learning_object_id%type;
913: v_business_group_id ota_learning_objects.business_group_id%type;
914: v_old_lesson_status ota_performances.lesson_status%type;
915: v_new_lesson_status ota_performances.lesson_status%type;
916: v_performance_source ota_performances.source%type;
917: begin

Line 922: from ota_learning_objects

918: -- The performance is actually stored on the source learning object, so get that ID.
919: -- Also get the business group in case we have to create a new performance record.
920: select source_learning_object_id, business_group_id
921: into v_source_lo_id, v_business_group_id
922: from ota_learning_objects
923: where learning_object_id = p_lo_id;
924:
925: begin
926: -- Find the old status and completed date.

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

1026: end if;
1027: end;
1028:
1029: -- Cascade the change to any affected RCOs. These include parents of this
1030: -- ota_learning_objects, plus those that indicate this ota_learning_objects as a completion
1031: -- requirement.
1032: for a_lo in affected_los(v_source_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
1033: -- if the current status is Passed, nothing can override it, so we will
1034: -- save the effort of computing the new status

Line 1046: p_lo_id ota_learning_objects.learning_object_id%type,

1042: end set_performance_lesson_status;
1043:
1044: --13485033
1045: procedure set_performance_lesson_status(
1046: p_lo_id ota_learning_objects.learning_object_id%type,
1047: p_user_id fnd_user.user_id%type,
1048: p_user_type ota_attempts.user_type%type,
1049: p_lesson_status ota_performances.lesson_status%type,
1050: p_cascaded varchar2,

Line 1065: p_lo_id ota_learning_objects.learning_object_id%type,

1061:
1062:
1063:
1064: procedure set_performance_lesson_status(
1065: p_lo_id ota_learning_objects.learning_object_id%type,
1066: p_user_id fnd_user.user_id%type,
1067: p_user_type ota_attempts.user_type%type,
1068: p_lesson_status ota_performances.lesson_status%type,
1069: p_date date,

Line 1078: p_lo_id ota_learning_objects.learning_object_id%type,

1074: end set_performance_lesson_status;
1075:
1076:
1077: procedure set_performance_lesson_status(
1078: p_lo_id ota_learning_objects.learning_object_id%type,
1079: p_user_id fnd_user.user_id%type,
1080: p_user_type ota_attempts.user_type%type,
1081: p_lesson_status ota_performances.lesson_status%type,
1082: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,

Line 1090: p_lo_id ota_learning_objects.learning_object_id%type,

1086: end set_performance_lesson_status;
1087:
1088:
1089: procedure set_performance_time(
1090: p_lo_id ota_learning_objects.learning_object_id%type,
1091: p_user_id fnd_user.user_id%type,
1092: p_user_type ota_attempts.user_type%type,
1093: p_time ota_performances.time%type,
1094: p_cascaded boolean,

Line 1100: p_lo_id ota_learning_objects.learning_object_id%type,

1096: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1097: p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1098:
1099: cursor parent_los(
1100: p_lo_id ota_learning_objects.learning_object_id%type,
1101: p_user_id fnd_user.user_id%type,
1102: p_user_type ota_attempts.user_type%type,
1103: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1104: select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time

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

1101: p_user_id fnd_user.user_id%type,
1102: p_user_type ota_attempts.user_type%type,
1103: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1104: select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
1105: from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
1106: where child.source_learning_object_id = p_lo_id and
1107: child.parent_learning_object_id = parent.learning_object_id and
1108: parent.learning_object_id = parent.source_learning_object_id and
1109: parent.starting_url is null and

Line 1117: p_lo_id ota_learning_objects.learning_object_id%type,

1113: nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1) and
1114: nvl(perf.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
1115:
1116: cursor distinct_child_los(
1117: p_lo_id ota_learning_objects.learning_object_id%type,
1118: p_user_id fnd_user.user_id%type,
1119: p_user_type ota_attempts.user_type%type,
1120: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1121: select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time

Line 1124: from ota_learning_objects

1120: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1121: select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
1122: from ota_performances p,
1123: (select distinct learning_object_id, source_learning_object_id, starting_url
1124: from ota_learning_objects
1125: where learning_object_id <> p_lo_id
1126: start with learning_object_id = p_lo_id
1127: connect by parent_learning_object_id = prior learning_object_id) lo
1128: where lo.starting_url is not null and

Line 1135: v_source_lo_id ota_learning_objects.learning_object_id%type;

1131: p.user_type(+) = p_user_type and
1132: nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1) and
1133: nvl(p.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
1134:
1135: v_source_lo_id ota_learning_objects.learning_object_id%type;
1136: v_business_group_id ota_learning_objects.business_group_id%type;
1137: v_old_time ota_performances.time%type;
1138: v_new_time ota_performances.time%type;
1139: v_performance_source ota_performances.source%type;

Line 1136: v_business_group_id ota_learning_objects.business_group_id%type;

1132: nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1) and
1133: nvl(p.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
1134:
1135: v_source_lo_id ota_learning_objects.learning_object_id%type;
1136: v_business_group_id ota_learning_objects.business_group_id%type;
1137: v_old_time ota_performances.time%type;
1138: v_new_time ota_performances.time%type;
1139: v_performance_source ota_performances.source%type;
1140: begin

Line 1145: from ota_learning_objects

1141: -- The performance is actually stored on the source learning object, so get that ID.
1142: -- Also get the business group in case we have to create a new performance record.
1143: select source_learning_object_id, business_group_id
1144: into v_source_lo_id, v_business_group_id
1145: from ota_learning_objects
1146: where learning_object_id = p_lo_id;
1147:
1148: begin
1149: select p.time

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

1192: return;
1193: end if;
1194: end;
1195:
1196: -- Get all the source-parents of all the targets of the ota_learning_objects
1197: for a_parent_lo in parent_los(p_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
1198: v_new_time := 0;
1199: 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
1200: if a_child_lo.time > 0 then

Line 1213: p_lo_id ota_learning_objects.learning_object_id%type,

1209: end set_performance_time;
1210:
1211:
1212: procedure set_performance_time(
1213: p_lo_id ota_learning_objects.learning_object_id%type,
1214: p_user_id fnd_user.user_id%type,
1215: p_user_type ota_attempts.user_type%type,
1216: p_time ota_performances.time%type,
1217: p_date date,

Line 1226: p_lo_id ota_learning_objects.learning_object_id%type,

1222: end set_performance_time;
1223:
1224:
1225: procedure set_performance_time(
1226: p_lo_id ota_learning_objects.learning_object_id%type,
1227: p_user_id fnd_user.user_id%type,
1228: p_user_type ota_attempts.user_type%type,
1229: p_time ota_performances.time%type,
1230: p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,

Line 1238: p_lo_id ota_learning_objects.learning_object_id%type,

1234: end set_performance_time;
1235:
1236:
1237: function lo_is_attemptable(
1238: p_lo_id ota_learning_objects.learning_object_id%type,
1239: p_launch_type ota_attempts.launch_type%type,
1240: p_reason out nocopy number) return boolean is
1241:
1242: v_published_flag ota_learning_objects.published_flag%type;

Line 1242: v_published_flag ota_learning_objects.published_flag%type;

1238: p_lo_id ota_learning_objects.learning_object_id%type,
1239: p_launch_type ota_attempts.launch_type%type,
1240: p_reason out nocopy number) return boolean is
1241:
1242: v_published_flag ota_learning_objects.published_flag%type;
1243: v_starting_url ota_learning_objects.starting_url%type;
1244: v_start_date_active ota_learning_objects.start_date_active%type;
1245: v_end_date_active ota_learning_objects.end_date_active%type;
1246:

Line 1243: v_starting_url ota_learning_objects.starting_url%type;

1239: p_launch_type ota_attempts.launch_type%type,
1240: p_reason out nocopy number) return boolean is
1241:
1242: v_published_flag ota_learning_objects.published_flag%type;
1243: v_starting_url ota_learning_objects.starting_url%type;
1244: v_start_date_active ota_learning_objects.start_date_active%type;
1245: v_end_date_active ota_learning_objects.end_date_active%type;
1246:
1247: begin

Line 1244: v_start_date_active ota_learning_objects.start_date_active%type;

1240: p_reason out nocopy number) return boolean is
1241:
1242: v_published_flag ota_learning_objects.published_flag%type;
1243: v_starting_url ota_learning_objects.starting_url%type;
1244: v_start_date_active ota_learning_objects.start_date_active%type;
1245: v_end_date_active ota_learning_objects.end_date_active%type;
1246:
1247: begin
1248: select published_flag,

Line 1245: v_end_date_active ota_learning_objects.end_date_active%type;

1241:
1242: v_published_flag ota_learning_objects.published_flag%type;
1243: v_starting_url ota_learning_objects.starting_url%type;
1244: v_start_date_active ota_learning_objects.start_date_active%type;
1245: v_end_date_active ota_learning_objects.end_date_active%type;
1246:
1247: begin
1248: select published_flag,
1249: starting_url,

Line 1256: from ota_learning_objects

1252: into v_published_flag,
1253: v_starting_url,
1254: v_start_date_active,
1255: v_end_date_active
1256: from ota_learning_objects
1257: where learning_object_id = p_lo_id;
1258:
1259: if v_published_flag = 'N' then
1260: p_reason := LO_REASON_NOT_PUBLISHED;

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

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;
1285: begin

Line 1284: v_root_lo_id ota_learning_objects.learning_object_id%type;

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;
1285: begin
1286: select o.learning_object_id
1287: into v_root_lo_id
1288: from ota_events e, ota_offerings o

Line 1294: from ota_learning_objects

1290: e.parent_offering_id = o.offering_id;
1291:
1292: select 'X'
1293: into v_dummy
1294: from ota_learning_objects
1295: where learning_object_id = p_lo_id
1296: start with learning_object_id = v_root_lo_id
1297: connect by parent_learning_object_id = prior learning_object_id;
1298:

Line 1307: p_lo_id ota_learning_objects.learning_object_id%type,

1303: end lo_is_in_event;
1304:
1305:
1306: function user_meets_prerequisites(
1307: p_lo_id ota_learning_objects.learning_object_id%type,
1308: p_user_id fnd_user.user_id%type,
1309: p_user_type ota_attempts.user_type%type) return boolean is
1310:
1311: --Commented for Bug#3582893

Line 1371: p_lo_id ota_learning_objects.learning_object_id%type,

1367: end user_meets_prerequisites;
1368:
1369:
1370: function user_exceeded_attempt_limit(
1371: p_lo_id ota_learning_objects.learning_object_id%type,
1372: p_user_id fnd_user.user_id%type,
1373: p_user_type ota_attempts.user_type%type,
1374: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1375:

Line 1382: from ota_tests t, ota_learning_objects lo

1378: begin
1379: begin
1380: select t.max_attempts
1381: into v_max_attempts
1382: from ota_tests t, ota_learning_objects lo
1383: where lo.learning_object_id = p_lo_id and
1384: lo.test_id = t.test_id;
1385: exception
1386: when NO_DATA_FOUND then -- the LO is not a test, so no attempt limit exists

Line 1423: p_lo_id ota_learning_objects.learning_object_id%type,

1419: end user_exceeded_attempt_limit;
1420:
1421:
1422: function get_next_attempt_date_for_lo(
1423: p_lo_id ota_learning_objects.learning_object_id%type,
1424: p_user_id fnd_user.user_id%type,
1425: p_user_type ota_attempts.user_type%type,
1426: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return date is
1427:

Line 1459: p_lo_id ota_learning_objects.learning_object_id%type,

1455: end get_next_attempt_date_for_lo;
1456:
1457:
1458: function user_must_wait_to_attempt(
1459: p_lo_id ota_learning_objects.learning_object_id%type,
1460: p_user_id fnd_user.user_id%type,
1461: p_user_type ota_attempts.user_type%type,
1462: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1463:

Line 1477: p_lo_id ota_learning_objects.learning_object_id%type,

1473: end user_must_wait_to_attempt;
1474:
1475:
1476: function user_can_attempt_lo(
1477: p_lo_id ota_learning_objects.learning_object_id%type,
1478: p_user_id fnd_user.user_id%type,
1479: p_user_type ota_attempts.user_type%type,
1480: p_launch_type ota_attempts.launch_type%type,
1481: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,

Line 1516: p_lo_id ota_learning_objects.learning_object_id%type,

1512: end user_can_attempt_lo;
1513:
1514:
1515: function user_can_attempt_lo(
1516: p_lo_id ota_learning_objects.learning_object_id%type,
1517: p_user_id fnd_user.user_id%type,
1518: p_user_type ota_attempts.user_type%type,
1519: p_launch_type ota_attempts.launch_type%type default '',
1520: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is

Line 1533: p_lo_id ota_learning_objects.learning_object_id%type,

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

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

1551: end user_can_attempt_lo_3;
1552:
1553: function lo_is_in_cert(
1554: p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type,
1555: p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
1556:
1557: cursor root_los(p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
1558: select o.learning_object_id
1559: from ota_offerings o, ota_certification_members cm, ota_cert_mbr_enrollments cme

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

1560: where o.activity_version_id = cm.object_id
1561: and cm.certification_member_id = cme.cert_member_id
1562: and cme.cert_prd_enrollment_id = p_cert_prd_enroll_id;
1563:
1564: cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1565: p_root_lo_id ota_learning_objects.learning_object_id%type) is
1566: select learning_object_id
1567: from ota_learning_objects
1568: where learning_object_id = p_lo_id

Line 1565: p_root_lo_id ota_learning_objects.learning_object_id%type) is

1561: and cm.certification_member_id = cme.cert_member_id
1562: and cme.cert_prd_enrollment_id = p_cert_prd_enroll_id;
1563:
1564: cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1565: p_root_lo_id ota_learning_objects.learning_object_id%type) is
1566: select learning_object_id
1567: from ota_learning_objects
1568: where learning_object_id = p_lo_id
1569: start with learning_object_id = p_root_lo_id

Line 1567: from ota_learning_objects

1563:
1564: cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1565: p_root_lo_id ota_learning_objects.learning_object_id%type) is
1566: select learning_object_id
1567: from ota_learning_objects
1568: where learning_object_id = p_lo_id
1569: start with learning_object_id = p_root_lo_id
1570: connect by parent_learning_object_id = prior learning_object_id;
1571:

Line 1585: p_lo_id ota_learning_objects.learning_object_id%type,

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

Line 1880: p_start ota_learning_objects.learning_object_id%type,

1876: end user_can_attempt_cert;
1877:
1878:
1879: function find_previous_lo_id(
1880: p_start ota_learning_objects.learning_object_id%type,
1881: p_current ota_learning_objects.learning_object_id%type,
1882: p_current_starting_url ota_learning_objects.starting_url%type,
1883: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1884:

Line 1881: p_current ota_learning_objects.learning_object_id%type,

1877:
1878:
1879: function find_previous_lo_id(
1880: p_start ota_learning_objects.learning_object_id%type,
1881: p_current ota_learning_objects.learning_object_id%type,
1882: p_current_starting_url ota_learning_objects.starting_url%type,
1883: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1884:
1885: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is

Line 1882: p_current_starting_url ota_learning_objects.starting_url%type,

1878:
1879: function find_previous_lo_id(
1880: p_start ota_learning_objects.learning_object_id%type,
1881: p_current ota_learning_objects.learning_object_id%type,
1882: p_current_starting_url ota_learning_objects.starting_url%type,
1883: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1884:
1885: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1886: select learning_object_id, starting_url

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

1879: function find_previous_lo_id(
1880: p_start ota_learning_objects.learning_object_id%type,
1881: p_current ota_learning_objects.learning_object_id%type,
1882: p_current_starting_url ota_learning_objects.starting_url%type,
1883: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1884:
1885: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1886: select learning_object_id, starting_url
1887: from ota_learning_objects

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

1881: p_current ota_learning_objects.learning_object_id%type,
1882: p_current_starting_url ota_learning_objects.starting_url%type,
1883: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1884:
1885: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1886: select learning_object_id, starting_url
1887: from ota_learning_objects
1888: where parent_learning_object_id = p_lo_id and
1889: published_flag = 'Y'

Line 1887: from ota_learning_objects

1883: p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1884:
1885: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1886: select learning_object_id, starting_url
1887: from ota_learning_objects
1888: where parent_learning_object_id = p_lo_id and
1889: published_flag = 'Y'
1890: order by child_seq asc;
1891: begin

Line 1911: p_root_lo_id ota_learning_objects.learning_object_id%type,

1907: end find_previous_lo_id;
1908:
1909:
1910: function get_previous_lo_id(
1911: p_root_lo_id ota_learning_objects.learning_object_id%type,
1912: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1913: p_user_id fnd_user.user_id%type,
1914: p_user_type ota_attempts.user_type%type,
1915: p_launch_type ota_attempts.launch_type%type,

Line 1912: p_starting_lo_id ota_learning_objects.learning_object_id%type,

1908:
1909:
1910: function get_previous_lo_id(
1911: p_root_lo_id ota_learning_objects.learning_object_id%type,
1912: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1913: p_user_id fnd_user.user_id%type,
1914: p_user_type ota_attempts.user_type%type,
1915: p_launch_type ota_attempts.launch_type%type,
1916: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

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

1912: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1913: p_user_id fnd_user.user_id%type,
1914: p_user_type ota_attempts.user_type%type,
1915: p_launch_type ota_attempts.launch_type%type,
1916: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1917:
1918: v_starting_url ota_learning_objects.starting_url%type :=null;
1919: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1920: v_reason number;

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

1914: p_user_type ota_attempts.user_type%type,
1915: p_launch_type ota_attempts.launch_type%type,
1916: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1917:
1918: v_starting_url ota_learning_objects.starting_url%type :=null;
1919: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1920: v_reason number;
1921: begin
1922: select starting_url

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

1915: p_launch_type ota_attempts.launch_type%type,
1916: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1917:
1918: v_starting_url ota_learning_objects.starting_url%type :=null;
1919: v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1920: v_reason number;
1921: begin
1922: select starting_url
1923: into v_starting_url

Line 1924: from ota_learning_objects

1920: v_reason number;
1921: begin
1922: select starting_url
1923: into v_starting_url
1924: from ota_learning_objects
1925: where learning_object_id = p_root_lo_id;
1926:
1927: if find_previous_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_previous_lo_id) then
1928: 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 1941: p_starting_lo_id ota_learning_objects.learning_object_id%type,

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,
1945: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

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

1941: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1942: p_user_id fnd_user.user_id%type,
1943: p_user_type ota_attempts.user_type%type,
1944: p_launch_type ota_attempts.launch_type%type,
1945: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1946:
1947: v_root_lo_id ota_learning_objects.learning_object_id%type;
1948: begin
1949: select o.learning_object_id

Line 1947: v_root_lo_id ota_learning_objects.learning_object_id%type;

1943: p_user_type ota_attempts.user_type%type,
1944: p_launch_type ota_attempts.launch_type%type,
1945: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1946:
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

Line 1960: p_start ota_learning_objects.learning_object_id%type,

1956: end get_previous_event_lo_id;
1957:
1958:
1959: function find_next_lo_id(
1960: p_start ota_learning_objects.learning_object_id%type,
1961: p_current ota_learning_objects.learning_object_id%type,
1962: p_current_starting_url ota_learning_objects.starting_url%type,
1963: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1964:

Line 1961: p_current ota_learning_objects.learning_object_id%type,

1957:
1958:
1959: function find_next_lo_id(
1960: p_start ota_learning_objects.learning_object_id%type,
1961: p_current ota_learning_objects.learning_object_id%type,
1962: p_current_starting_url ota_learning_objects.starting_url%type,
1963: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1964:
1965: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is

Line 1962: p_current_starting_url ota_learning_objects.starting_url%type,

1958:
1959: function find_next_lo_id(
1960: p_start ota_learning_objects.learning_object_id%type,
1961: p_current ota_learning_objects.learning_object_id%type,
1962: p_current_starting_url ota_learning_objects.starting_url%type,
1963: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1964:
1965: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1966: select learning_object_id, starting_url

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

1959: function find_next_lo_id(
1960: p_start ota_learning_objects.learning_object_id%type,
1961: p_current ota_learning_objects.learning_object_id%type,
1962: p_current_starting_url ota_learning_objects.starting_url%type,
1963: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1964:
1965: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1966: select learning_object_id, starting_url
1967: from ota_learning_objects

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

1961: p_current ota_learning_objects.learning_object_id%type,
1962: p_current_starting_url ota_learning_objects.starting_url%type,
1963: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1964:
1965: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1966: select learning_object_id, starting_url
1967: from ota_learning_objects
1968: where parent_learning_object_id = p_lo_id and
1969: published_flag = 'Y'

Line 1967: from ota_learning_objects

1963: p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1964:
1965: cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1966: select learning_object_id, starting_url
1967: from ota_learning_objects
1968: where parent_learning_object_id = p_lo_id and
1969: published_flag = 'Y'
1970: order by child_seq asc;
1971: v_result ota_learning_objects.learning_object_id%type;

Line 1971: v_result ota_learning_objects.learning_object_id%type;

1967: from ota_learning_objects
1968: where parent_learning_object_id = p_lo_id and
1969: published_flag = 'Y'
1970: order by child_seq asc;
1971: v_result ota_learning_objects.learning_object_id%type;
1972: begin
1973: if (p_found_start or p_start is null) and p_current_starting_url is not null then
1974: return p_current;
1975: end if;

Line 1993: p_root_lo_id ota_learning_objects.learning_object_id%type,

1989: end find_next_lo_id;
1990:
1991:
1992: function get_next_lo_id(
1993: p_root_lo_id ota_learning_objects.learning_object_id%type,
1994: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1995: p_user_id fnd_user.user_id%type,
1996: p_user_type ota_attempts.user_type%type,
1997: p_launch_type ota_attempts.launch_type%type,

Line 1994: p_starting_lo_id ota_learning_objects.learning_object_id%type,

1990:
1991:
1992: function get_next_lo_id(
1993: p_root_lo_id ota_learning_objects.learning_object_id%type,
1994: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1995: p_user_id fnd_user.user_id%type,
1996: p_user_type ota_attempts.user_type%type,
1997: p_launch_type ota_attempts.launch_type%type,
1998: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

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

1994: p_starting_lo_id ota_learning_objects.learning_object_id%type,
1995: p_user_id fnd_user.user_id%type,
1996: p_user_type ota_attempts.user_type%type,
1997: p_launch_type ota_attempts.launch_type%type,
1998: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1999:
2000:
2001: v_starting_url ota_learning_objects.starting_url%type := null;
2002: v_next_lo_id ota_learning_objects.learning_object_id%type := null;

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

1997: p_launch_type ota_attempts.launch_type%type,
1998: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1999:
2000:
2001: v_starting_url ota_learning_objects.starting_url%type := null;
2002: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
2003: v_false boolean := false;
2004: v_reason number;
2005: begin

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

1998: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1999:
2000:
2001: v_starting_url ota_learning_objects.starting_url%type := null;
2002: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
2003: v_false boolean := false;
2004: v_reason number;
2005: begin
2006: 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 2012: p_root_lo_id ota_learning_objects.learning_object_id%type,

2008:
2009:
2010:
2011: function get_next_lo_id(
2012: p_root_lo_id ota_learning_objects.learning_object_id%type,
2013: p_root_starting_url ota_learning_objects.starting_url%type,
2014: p_starting_lo_id ota_learning_objects.learning_object_id%type,
2015: p_user_id fnd_user.user_id%type,
2016: p_user_type ota_attempts.user_type%type,

Line 2013: p_root_starting_url ota_learning_objects.starting_url%type,

2009:
2010:
2011: function get_next_lo_id(
2012: p_root_lo_id ota_learning_objects.learning_object_id%type,
2013: p_root_starting_url ota_learning_objects.starting_url%type,
2014: p_starting_lo_id ota_learning_objects.learning_object_id%type,
2015: p_user_id fnd_user.user_id%type,
2016: p_user_type ota_attempts.user_type%type,
2017: p_launch_type ota_attempts.launch_type%type,

Line 2014: p_starting_lo_id ota_learning_objects.learning_object_id%type,

2010:
2011: function get_next_lo_id(
2012: p_root_lo_id ota_learning_objects.learning_object_id%type,
2013: p_root_starting_url ota_learning_objects.starting_url%type,
2014: p_starting_lo_id ota_learning_objects.learning_object_id%type,
2015: p_user_id fnd_user.user_id%type,
2016: p_user_type ota_attempts.user_type%type,
2017: p_launch_type ota_attempts.launch_type%type,
2018: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

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

2014: p_starting_lo_id ota_learning_objects.learning_object_id%type,
2015: p_user_id fnd_user.user_id%type,
2016: p_user_type ota_attempts.user_type%type,
2017: p_launch_type ota_attempts.launch_type%type,
2018: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2019:
2020: v_starting_url ota_learning_objects.starting_url%type := null;
2021: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
2022: v_false boolean := false;

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

2016: p_user_type ota_attempts.user_type%type,
2017: p_launch_type ota_attempts.launch_type%type,
2018: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2019:
2020: v_starting_url ota_learning_objects.starting_url%type := null;
2021: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
2022: v_false boolean := false;
2023: v_reason number;
2024: begin

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

2017: p_launch_type ota_attempts.launch_type%type,
2018: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2019:
2020: v_starting_url ota_learning_objects.starting_url%type := null;
2021: v_next_lo_id ota_learning_objects.learning_object_id%type := null;
2022: v_false boolean := false;
2023: v_reason number;
2024: begin
2025:

Line 2031: from ota_learning_objects

2027: v_starting_url := p_root_starting_url;
2028: else
2029: select starting_url
2030: into v_starting_url
2031: from ota_learning_objects
2032: where learning_object_id = p_root_lo_id;
2033: end if;
2034:
2035: v_next_lo_id := find_next_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_false);

Line 2050: p_starting_lo_id ota_learning_objects.learning_object_id%type,

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,
2054: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is

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

2050: p_starting_lo_id ota_learning_objects.learning_object_id%type,
2051: p_user_id fnd_user.user_id%type,
2052: p_user_type ota_attempts.user_type%type,
2053: p_launch_type ota_attempts.launch_type%type,
2054: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2055:
2056: v_root_lo_id ota_learning_objects.learning_object_id%type;
2057: begin
2058: select o.learning_object_id

Line 2056: v_root_lo_id ota_learning_objects.learning_object_id%type;

2052: p_user_type ota_attempts.user_type%type,
2053: p_launch_type ota_attempts.launch_type%type,
2054: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2055:
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

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

2069: p_event_id ota_events.event_id%type,
2070: p_user_id fnd_user.user_id%type,
2071: p_user_type ota_attempts.user_type%type,
2072: p_launch_type ota_attempts.launch_type%type,
2073: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
2074:
2075: cursor ordered_los(
2076: p_root_lo_id ota_learning_objects.learning_object_id%type,
2077: p_user_id fnd_user.user_id%type,

Line 2076: p_root_lo_id ota_learning_objects.learning_object_id%type,

2072: p_launch_type ota_attempts.launch_type%type,
2073: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
2074:
2075: cursor ordered_los(
2076: p_root_lo_id ota_learning_objects.learning_object_id%type,
2077: p_user_id fnd_user.user_id%type,
2078: p_user_type ota_attempts.user_type%type,
2079: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
2080: select a.learning_object_id, max(a.attempt_id) max_attempt

Line 2083: from ota_learning_objects

2079: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
2080: select a.learning_object_id, max(a.attempt_id) max_attempt
2081: from ota_attempts a,
2082: (select learning_object_id, starting_url
2083: from ota_learning_objects
2084: start with learning_object_id = p_root_lo_id
2085: connect by parent_learning_object_id = prior learning_object_id) lo
2086: where a.user_id = p_user_id and
2087: a.user_type = p_user_type and

Line 2096: v_root_lo_id ota_learning_objects.learning_object_id%type;

2092: )
2093: group by a.learning_object_id
2094: order by max_attempt desc;
2095:
2096: v_root_lo_id ota_learning_objects.learning_object_id%type;
2097: v_reason number;
2098: begin
2099: select o.learning_object_id
2100: into v_root_lo_id

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

2117: p_event_id ota_events.event_id%type,
2118: p_user_id fnd_user.user_id%type,
2119: p_user_type ota_attempts.user_type%type,
2120: p_launch_type ota_attempts.launch_type%type,
2121: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2122:
2123: v_root_lo_id ota_learning_objects.learning_object_id%type;
2124: v_first_lo_id ota_learning_objects.learning_object_id%type;
2125: v_reason number;

Line 2123: v_root_lo_id ota_learning_objects.learning_object_id%type;

2119: p_user_type ota_attempts.user_type%type,
2120: p_launch_type ota_attempts.launch_type%type,
2121: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2122:
2123: v_root_lo_id ota_learning_objects.learning_object_id%type;
2124: v_first_lo_id ota_learning_objects.learning_object_id%type;
2125: v_reason number;
2126: begin
2127: select o.learning_object_id

Line 2124: v_first_lo_id ota_learning_objects.learning_object_id%type;

2120: p_launch_type ota_attempts.launch_type%type,
2121: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2122:
2123: v_root_lo_id ota_learning_objects.learning_object_id%type;
2124: v_first_lo_id ota_learning_objects.learning_object_id%type;
2125: v_reason number;
2126: begin
2127: select o.learning_object_id
2128: into v_root_lo_id

Line 2137: p_lo_id ota_learning_objects.learning_object_id%type,

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,

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

2138: p_event_id ota_events.event_id%type,
2139: p_user_id fnd_user.user_id%type,
2140: p_user_type ota_attempts.user_type%type,
2141: p_launch_type ota_attempts.launch_type%type,
2142: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
2143: begin
2144: return get_jump_lo_id(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, null, p_reason);
2145: end get_jump_lo_id;
2146:

Line 2148: p_lo_id ota_learning_objects.learning_object_id%type,

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

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

2150: p_user_id fnd_user.user_id%type,
2151: p_user_type ota_attempts.user_type%type,
2152: p_launch_type ota_attempts.launch_type%type,
2153: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
2154: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
2155: begin
2156: 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
2157: return p_lo_id;
2158: else

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

2161: end get_jump_lo_id;
2162:
2163: --7574667
2164: function get_lo_type(
2165: p_lo_id ota_learning_objects.learning_object_id%type) return varchar2 is
2166:
2167: --7622768
2168: cursor lo_type is
2169: select nvl(test_type_flag,'LO')

Line 2171: ota_learning_objects lo

2167: --7622768
2168: cursor lo_type is
2169: select nvl(test_type_flag,'LO')
2170: from ota_tests ot,
2171: ota_learning_objects lo
2172: where ot.test_id(+) = lo.test_id
2173: and lo.learning_object_id = p_lo_id;
2174:
2175: type_flag varchar2(10);

Line 2289: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,

2285: , to_char(opf.completed_date, 'HH24:MI:SS')
2286: , opf.lesson_status
2287: , 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
2288: ,ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
2289: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
2290: OTA_TESTS tst
2291: Where
2292: olo.learning_object_id = opf.learning_object_id(+)
2293: AND tst.test_id(+) = olo.test_id

Line 2324: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,

2320: , to_char(opf.completed_date, 'HH24:MI:SS')
2321: , opf.lesson_status
2322: , 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
2323: ,ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
2324: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
2325: OTA_TESTS tst
2326: Where
2327: olo.learning_object_id = opf.learning_object_id(+)
2328: AND tst.test_id(+) = olo.test_id

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

2477: p_chk_active_cert_flag varchar2 default 'N') return varchar2 is
2478:
2479:
2480: v_number_of_los number(10);
2481: v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
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;

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

2478:
2479:
2480: v_number_of_los number(10);
2481: v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
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;

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

2479:
2480: v_number_of_los number(10);
2481: v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
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:

Line 2603: from ota_learning_objects

2599: ,nvl(max(published_flag),'N')
2600: into v_number_of_los
2601: ,v_solo_lo_id
2602: ,v_published_flag
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

Line 2680: v_lo_id ota_learning_objects.learning_object_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
2682: select offr.learning_object_id
2683: into v_lo_id
2684: from ota_offerings offr, ota_events evt

Line 2924: p_lo_id ota_learning_objects.learning_object_id%type,

2920: -- ---------------------------------------------------------------------------
2921: function get_wait_duration_for_lo(
2922: p_user_id fnd_user.user_id%type,
2923: p_user_type ota_attempts.user_type%type,
2924: p_lo_id ota_learning_objects.learning_object_id%type,
2925: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2926:
2927: v_earliest_attempt_date date;
2928: begin

Line 2957: v_lo_id ota_learning_objects.learning_object_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);
2959: --Bug 5166350
2960: wait_dur_date varchar2(20);
2961: wait_dur_time varchar2(20);

Line 3417: from OTA_Learning_objects

3413:
3414: -- Bug 13496386
3415: cursor c_learning_object_tracktype (l_lo_id in ota_offerings.learning_object_id%type) IS
3416: select Tracking_type
3417: from OTA_Learning_objects
3418: where learning_object_id = l_lo_id;
3419:
3420: --modified for 14000818.
3421: cursor csr_noof_perf_recs (l_lo_id in ota_offerings.learning_object_id%type) is

Line 3424: ota_performances p, ota_learning_objects lo

3420: --modified for 14000818.
3421: cursor csr_noof_perf_recs (l_lo_id in ota_offerings.learning_object_id%type) is
3422: select count(p.PERFORMANCE_ID)
3423: from
3424: ota_performances p, ota_learning_objects lo
3425: where lo.learning_object_id = p.learning_object_id
3426: and p.user_id = p_user_id
3427: and p.user_type = p_user_type
3428: and lo.learning_object_id = l_lo_id

Line 3436: ota_performances p, ota_learning_objects lo

3432: cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type,
3433: l_cert_prd_enr_id in ota_performances.cert_prd_enrollment_id%type) is
3434: select count(p.PERFORMANCE_ID)
3435: from
3436: ota_performances p, ota_learning_objects lo
3437: where lo.learning_object_id = p.learning_object_id
3438: and p.user_id = p_user_id
3439: and p.user_type = p_user_type
3440: and lo.learning_object_id = l_lo_id

Line 3494: l_trackingtype ota_learning_objects.tracking_type %TYPE;

3490: l_person_id ota_cert_enrollments.person_id%type := null;
3491: l_contact_id ota_cert_enrollments.contact_id%type := null;
3492: l_cert_prd_enrollment_id OTA_CERT_PRD_ENROLLMENTS.cert_prd_enrollment_id%type;
3493: l_sign_eval_status OTA_DELEGATE_BOOKINGS.sign_eval_status%type;
3494: l_trackingtype ota_learning_objects.tracking_type %TYPE;
3495: l_perf_rec_count INTEGER := 0;
3496: l_multiple boolean:=false;
3497:
3498: BEGIN

Line 3678: p_lo_id ota_learning_objects.learning_object_id%TYPE,

3674: -- {End Of Comments}
3675: ----------------------------------------------------------------------------
3676:
3677: FUNCTION get_history_button(p_user_id fnd_user.user_id%TYPE,
3678: p_lo_id ota_learning_objects.learning_object_id%TYPE,
3679: p_event_id ota_events.event_id%TYPE,
3680: p_booking_id ota_delegate_bookings.booking_id%TYPE)
3681: RETURN VARCHAR2 IS
3682:

Line 3815: l_lo_id ota_learning_objects.learning_object_id%type;

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);
3819: l_lo_completed_date_tz VARCHAR2(100);

Line 3922: l_lo_id ota_learning_objects.learning_object_id%type;

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);
3926: l_lo_completed_date_tz VARCHAR2(100);

Line 4226: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,

4222: , to_char(opf.completed_date, 'HH24:MI:SS')
4223: , opf.lesson_status
4224: , 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
4225: , ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
4226: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
4227: OTA_TESTS tst
4228: Where
4229: olo.learning_object_id = opf.learning_object_id(+)
4230: AND tst.test_id(+) = olo.test_id

Line 4260: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,

4256: , to_char(opf.completed_date, 'HH24:MI:SS')
4257: , opf.lesson_status
4258: , 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
4259: , ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
4260: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
4261: OTA_TESTS tst
4262: Where
4263: olo.learning_object_id = opf.learning_object_id(+)
4264: AND tst.test_id(+) = olo.test_id

Line 4707: ota_learning_objects lo

4703: prf.lesson_status lesson_status,
4704: prf.performance_id
4705: FROM ota_performances prf,
4706: ota_offerings ofr,
4707: ota_learning_objects lo
4708: WHERE
4709: prf.user_id = p_user_id
4710: and prf.user_type = p_user_type
4711: and lo.learning_object_id = prf.learning_object_id

Line 5257: ota_learning_objects lo

5253: prf.lesson_status lesson_status,
5254: prf.performance_id
5255: FROM ota_performances prf,
5256: ota_offerings ofr,
5257: ota_learning_objects lo
5258: WHERE
5259: prf.user_id = p_user_id
5260: and prf.user_type = p_user_type
5261: and lo.learning_object_id = prf.learning_object_id

Line 5485: From ota_learning_objects cld, ota_learning_objects par

5481: --
5482: -- decalare cursor
5483: Cursor csr_par_with_cld_no_url is
5484: Select null
5485: From ota_learning_objects cld, ota_learning_objects par
5486: Where par.learning_object_id = cld.parent_learning_object_id
5487: And par.starting_url is null
5488: And par.learning_object_id = p_learning_object_id
5489: And rownum = 1;

Line 5648: from ota_learning_objects lo, ota_offerings ofr, ota_events oev

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:

Line 5656: ota_performances p, ota_learning_objects lo

5652:
5653: cursor csr_noof_perf_recs (l_lo_id in ota_offerings.learning_object_id%type) is
5654: select count(p.PERFORMANCE_ID)
5655: from
5656: ota_performances p, ota_learning_objects lo
5657: where lo.learning_object_id = p.learning_object_id
5658: and p.user_id = p_user_id
5659: and p.user_type = p_user_type
5660: and lo.learning_object_id = l_lo_id

Line 5666: ota_performances p, ota_learning_objects lo

5662:
5663: cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type, l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type) is
5664: select count(p.PERFORMANCE_ID)
5665: from
5666: ota_performances p, ota_learning_objects lo
5667: where lo.learning_object_id = p.learning_object_id
5668: and p.user_id = p_user_id
5669: and p.user_type = p_user_type
5670: and lo.learning_object_id = l_lo_id

Line 5681: l_trackingtype ota_learning_objects.tracking_type %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;
5684: l_person_id ota_cert_enrollments.person_id%type := null;
5685: l_contact_id ota_cert_enrollments.contact_id%type := null;