[Home] [Help]
150:
151:
152: procedure update_enrollment(
153: p_booking_id ota_delegate_bookings.booking_id%type,
154: p_event_id ota_events.event_id%type,
155: p_business_group_id ota_delegate_bookings.business_group_id%type,
156: p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
157: p_object_version_number ota_delegate_bookings.object_version_number%type,
158: p_date_status_changed ota_delegate_bookings.date_status_changed%type,
165: end update_enrollment;
166:
167: procedure update_enrollment(
168: p_booking_id ota_delegate_bookings.booking_id%type,
169: p_event_id ota_events.event_id%type,
170: p_business_group_id ota_delegate_bookings.business_group_id%type,
171: p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
172: p_object_version_number ota_delegate_bookings.object_version_number%type,
173: p_date_status_changed ota_delegate_bookings.date_status_changed%type,
215: --for the learners who have completed the mandatory evaluation.
216: procedure update_enrollment_status(
217: p_user_id fnd_user.user_id%type,
218: p_user_type ota_attempts.user_type%type,
219: p_event_id ota_events.event_id%type) is
220:
221: -- This cursor finds all events in all offerings which offer the LO and in
222: -- which the person is enrolled with a status of 'PENDING EVALUATION'.
223:
222: -- which the person is enrolled with a status of 'PENDING EVALUATION'.
223:
224:
225: cursor person_bookings(
226: p_event_id ota_events.event_id%type,
227: p_person_id ota_delegate_bookings.delegate_person_id%type) is
228: select book.booking_id,
229: ev.event_id,
230: book.business_group_id,
229: ev.event_id,
230: book.business_group_id,
231: book.date_booking_placed,
232: book.object_version_number
233: from ota_events ev,
234: ota_delegate_bookings book,
235: ota_booking_status_types stype
236: where nvl(ev.course_start_date, sysdate) <= sysdate and
237: book.event_id = ev.event_id and
241: stype.type = 'E';
242:
243: --Added for 6826434.
244: cursor party_bookings(
245: p_event_id ota_events.event_id%type,
246: p_party_id ota_delegate_bookings.delegate_contact_id%type) is
247: select book.booking_id,
248: ev.event_id,
249: book.business_group_id,
248: ev.event_id,
249: book.business_group_id,
250: book.date_booking_placed,
251: book.object_version_number
252: from ota_events ev,
253: ota_delegate_bookings book,
254: ota_booking_status_types stype,
255: hz_cust_account_roles acct_role,
256: hz_relationships rel,
273: select per.lesson_status,
274: offe.learning_object_id
275: from ota_performances per,
276: ota_offerings offe,
277: ota_events evt
278: where evt.parent_offering_id = offe.offering_id and
279: offe.learning_object_id = per.learning_object_id(+) and
280: evt.event_id = p_event_id and
281: (per.user_type is null or per.user_type = p_user_type) and
367: book.business_group_id,
368: book.date_booking_placed,
369: book.object_version_number,
370: stype.type --Added for 7110517.
371: from ota_events ev,
372: ota_offerings offr,
373: ota_learning_objects lo,
374: ota_delegate_bookings book,
375: ota_booking_status_types stype
397: book.business_group_id,
398: book.date_booking_placed,
399: book.object_version_number,
400: stype.type --Added fro 7110517.
401: from ota_events ev,
402: ota_offerings offr,
403: ota_learning_objects lo,
404: ota_delegate_bookings book,
405: ota_booking_status_types stype,
423: rel.subject_id = p_party_id and
424: rel.subject_table_name = 'HZ_PARTIES' and
425: rel.object_table_name = 'HZ_PARTIES';
426:
427: l_event_id OTA_EVENTS.EVENT_ID%TYPE:= null;
428: l_test_id OTA_TESTS.TEST_ID%type:=null;
429: Cursor csr_attempt_info is
430: select attempt_id from ota_attempts where
431: event_id = l_event_id
439: select evt_eval.evaluation_id evt_eval_id
440: ,decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
441: decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag --bug 7184369
442: ,act_eval.evaluation_id act_eval_id
443: from ota_evaluations evt_eval, ota_evaluations act_eval,ota_events evt
444: where
445: evt_eval.object_id(+) = evt.event_id and
446: (evt_eval.object_type is null or evt_eval.object_type = 'E') and
447: act_eval.object_id(+) = evt.activity_version_id and
969: end lo_is_attemptable;
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;
977: v_root_lo_id ota_learning_objects.learning_object_id%type;
978: begin
979: select o.learning_object_id
980: into v_root_lo_id
981: from ota_events e, ota_offerings o
982: where e.event_id = p_event_id and
983: e.parent_offering_id = o.offering_id;
984:
985: select 'X'
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,
1232: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
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,
1284: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1296: return true;
1297: end user_can_attempt_lo;
1298:
1299: function event_is_attemptable(
1300: p_event_id ota_events.event_id%type,
1301: p_date date,
1302: p_launch_type ota_attempts.launch_type%type,
1303: p_reason out nocopy number) return boolean is
1304:
1301: p_date date,
1302: p_launch_type ota_attempts.launch_type%type,
1303: p_reason out nocopy number) return boolean is
1304:
1305: v_course_start_date ota_events.course_start_date%type;
1306: v_course_start_time ota_events.course_start_time%type;
1307: v_course_end_date ota_events.course_end_date%type;
1308: v_course_end_time ota_events.course_end_time%type;
1309: v_is_instructor boolean;
1302: p_launch_type ota_attempts.launch_type%type,
1303: p_reason out nocopy number) return boolean is
1304:
1305: v_course_start_date ota_events.course_start_date%type;
1306: v_course_start_time ota_events.course_start_time%type;
1307: v_course_end_date ota_events.course_end_date%type;
1308: v_course_end_time ota_events.course_end_time%type;
1309: v_is_instructor boolean;
1310: v_course_started boolean;
1303: p_reason out nocopy number) return boolean is
1304:
1305: v_course_start_date ota_events.course_start_date%type;
1306: v_course_start_time ota_events.course_start_time%type;
1307: v_course_end_date ota_events.course_end_date%type;
1308: v_course_end_time ota_events.course_end_time%type;
1309: v_is_instructor boolean;
1310: v_course_started boolean;
1311: v_course_expired boolean;
1304:
1305: v_course_start_date ota_events.course_start_date%type;
1306: v_course_start_time ota_events.course_start_time%type;
1307: v_course_end_date ota_events.course_end_date%type;
1308: v_course_end_time ota_events.course_end_time%type;
1309: v_is_instructor boolean;
1310: v_course_started boolean;
1311: v_course_expired boolean;
1312: begin
1313: p_reason := EVENT_REASON_NO_REASON;
1314:
1315: select course_start_date, course_start_time, course_end_date, course_end_time
1316: into v_course_start_date, v_course_start_time, v_course_end_date, v_course_end_time
1317: from ota_events
1318: where event_id = p_event_id;
1319:
1320: v_course_started := (v_course_start_date is null or p_date >= v_course_start_date);
1321: v_course_expired := (v_course_end_date is not null and p_date > v_course_end_date);
1339: end event_is_attemptable;
1340:
1341:
1342: function user_is_enrolled(
1343: p_event_id ota_events.event_id%type,
1344: p_user_id fnd_user.user_id%type,
1345: p_user_type ota_attempts.user_type%type,
1346: p_date date,
1347: p_reason out nocopy number) return boolean is
1346: p_date date,
1347: p_reason out nocopy number) return boolean is
1348:
1349: cursor person_bookings(
1350: p_event_id ota_events.event_id%type,
1351: p_person_id per_all_people.person_id%type) is
1352: select 1
1353: from ota_delegate_bookings book,
1354: ota_booking_status_types stype
1357: book.booking_status_type_id = stype.booking_status_type_id and
1358: stype.type in ('P', 'A' ,'E');
1359:
1360: cursor contact_bookings(
1361: p_event_id ota_events.event_id%type,
1362: p_contact_id per_all_people.person_id%type) is
1363: select 1
1364: from ota_delegate_bookings book,
1365: ota_booking_status_types stype
1386: end user_is_enrolled;
1387:
1388:
1389: function user_is_instructor(
1390: p_event_id ota_events.event_id%type,
1391: p_user_id fnd_user.user_id%type,
1392: p_user_type ota_attempts.user_type%type,
1393: p_date date,
1394: p_reason out nocopy number) return boolean is
1407: tsr.trainer_id = p_user_id and
1408: trb.supplied_resource_id = tsr.supplied_resource_id and
1409: (trb.event_id = p_event_id or
1410: trb.event_id in ( select ses.event_id
1411: from ota_events ses
1412: where ses.parent_event_id = p_event_id
1413: and ses.event_type = 'SESSION')
1414: ) and
1415: tsr.resource_type = 'T') loop
1426: end user_is_instructor;
1427:
1428:
1429: function user_can_attempt_event(
1430: p_event_id ota_events.event_id%type,
1431: p_user_id fnd_user.user_id%type,
1432: p_user_type ota_attempts.user_type%type,
1433: p_launch_type ota_attempts.launch_type%type,
1434: p_reason out nocopy number) return varchar2 is
1612: end get_previous_lo_id;
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,
1623: v_root_lo_id ota_learning_objects.learning_object_id%type;
1624: begin
1625: select o.learning_object_id
1626: into v_root_lo_id
1627: from ota_events e, ota_offerings o
1628: where e.event_id = p_event_id and
1629: e.parent_offering_id = o.offering_id;
1630:
1631: return get_previous_lo_id(v_root_lo_id, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1721: end get_next_lo_id;
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,
1732: v_root_lo_id ota_learning_objects.learning_object_id%type;
1733: begin
1734: select o.learning_object_id
1735: into v_root_lo_id
1736: from ota_events e, ota_offerings o
1737: where e.event_id = p_event_id and
1738: e.parent_offering_id = o.offering_id;
1739:
1740: return get_next_lo_id(v_root_lo_id, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1741: end get_next_event_lo_id;
1742:
1743:
1744: function get_most_recent_lo_id(
1745: p_event_id ota_events.event_id%type,
1746: p_user_id fnd_user.user_id%type,
1747: p_user_type ota_attempts.user_type%type,
1748: p_launch_type ota_attempts.launch_type%type,
1749: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
1773: v_reason number;
1774: begin
1775: select o.learning_object_id
1776: into v_root_lo_id
1777: from ota_events e, ota_offerings o
1778: where e.event_id = p_event_id and
1779: e.parent_offering_id = o.offering_id;
1780:
1781:
1789: end get_most_recent_lo_id;
1790:
1791:
1792: function get_first_lo_id(
1793: p_event_id ota_events.event_id%type,
1794: p_user_id fnd_user.user_id%type,
1795: p_user_type ota_attempts.user_type%type,
1796: p_launch_type ota_attempts.launch_type%type,
1797: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1801: v_reason number;
1802: begin
1803: select o.learning_object_id
1804: into v_root_lo_id
1805: from ota_events e, ota_offerings o
1806: where e.event_id = p_event_id and
1807: e.parent_offering_id = o.offering_id;
1808:
1809: return get_next_lo_id(v_root_lo_id, null, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
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,
1818: p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
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,
1829: p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1861: end get_lo_type;
1862:
1863:
1864: function user_can_attempt_event(
1865: p_event_id ota_events.event_id%type,
1866: p_user_id fnd_user.user_id%type,
1867: p_user_type ota_attempts.user_type%type) return varchar2 is
1868:
1869: l_reason number;
1872: end user_can_attempt_event;
1873:
1874:
1875: function user_can_attempt_event(
1876: p_event_id ota_events.event_id%type,
1877: p_user_id fnd_user.user_id%type,
1878: p_user_type ota_attempts.user_type%type,
1879: p_launch_type ota_attempts.launch_type%type) return varchar2 is
1880:
2079: function get_play_button(
2080: p_user_id fnd_user.user_id%type,
2081: p_user_type ota_attempts.user_type%type,
2082: p_is_manager varchar2,
2083: p_event_id ota_events.event_id%type,
2084: p_event_type ota_events.event_type%type,
2085: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2086: p_online_flag ota_category_usages.online_flag%type,
2087: p_course_start_date ota_events.course_start_date%type,
2080: p_user_id fnd_user.user_id%type,
2081: p_user_type ota_attempts.user_type%type,
2082: p_is_manager varchar2,
2083: p_event_id ota_events.event_id%type,
2084: p_event_type ota_events.event_type%type,
2085: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2086: p_online_flag ota_category_usages.online_flag%type,
2087: p_course_start_date ota_events.course_start_date%type,
2088: p_course_end_date ota_events.course_end_date%type,
2083: p_event_id ota_events.event_id%type,
2084: p_event_type ota_events.event_type%type,
2085: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2086: p_online_flag ota_category_usages.online_flag%type,
2087: p_course_start_date ota_events.course_start_date%type,
2088: p_course_end_date ota_events.course_end_date%type,
2089: p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
2090: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
2091: p_contact_id ota_cert_enrollments.contact_id%type default null,
2084: p_event_type ota_events.event_type%type,
2085: p_synchronous_flag ota_category_usages.synchronous_flag%type,
2086: p_online_flag ota_category_usages.online_flag%type,
2087: p_course_start_date ota_events.course_start_date%type,
2088: p_course_end_date ota_events.course_end_date%type,
2089: p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
2090: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
2091: p_contact_id ota_cert_enrollments.contact_id%type default null,
2092: p_chk_active_cert_flag varchar2 default 'N') return varchar2 is
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:
2103: l_reason number;
2104: l_sysdate date;
2105: l_course_start_time ota_events.course_start_time%type;
2101: v_offering_id ota_events.offering_id%type := null;
2102:
2103: l_reason number;
2104: l_sysdate date;
2105: l_course_start_time ota_events.course_start_time%type;
2106: l_course_end_time ota_events.course_end_time%type;
2107: l_course_start_date ota_events.course_start_date%type;
2108: l_course_end_date ota_events.course_end_date%type;
2109: l_timezone ota_events.timezone%type;
2102:
2103: l_reason number;
2104: l_sysdate date;
2105: l_course_start_time ota_events.course_start_time%type;
2106: l_course_end_time ota_events.course_end_time%type;
2107: l_course_start_date ota_events.course_start_date%type;
2108: l_course_end_date ota_events.course_end_date%type;
2109: l_timezone ota_events.timezone%type;
2110:
2103: l_reason number;
2104: l_sysdate date;
2105: l_course_start_time ota_events.course_start_time%type;
2106: l_course_end_time ota_events.course_end_time%type;
2107: l_course_start_date ota_events.course_start_date%type;
2108: l_course_end_date ota_events.course_end_date%type;
2109: l_timezone ota_events.timezone%type;
2110:
2111: CURSOR c_event_time_info is
2104: l_sysdate date;
2105: l_course_start_time ota_events.course_start_time%type;
2106: l_course_end_time ota_events.course_end_time%type;
2107: l_course_start_date ota_events.course_start_date%type;
2108: l_course_end_date ota_events.course_end_date%type;
2109: l_timezone ota_events.timezone%type;
2110:
2111: CURSOR c_event_time_info is
2112: Select course_start_time, course_end_time, timezone
2105: l_course_start_time ota_events.course_start_time%type;
2106: l_course_end_time ota_events.course_end_time%type;
2107: l_course_start_date ota_events.course_start_date%type;
2108: l_course_end_date ota_events.course_end_date%type;
2109: l_timezone ota_events.timezone%type;
2110:
2111: CURSOR c_event_time_info is
2112: Select course_start_time, course_end_time, timezone
2113: From ota_events
2109: l_timezone ota_events.timezone%type;
2110:
2111: CURSOR c_event_time_info is
2112: Select course_start_time, course_end_time, timezone
2113: From ota_events
2114: Where event_id = p_event_id;
2115:
2116: l_cert_prd_enrollment_ids varchar2(4000) := null;
2117: l_person_id ota_cert_enrollments.person_id%type := null;
2187: select a.rco_id
2188: ,e.offering_id
2189: into v_rco_id
2190: ,v_offering_id
2191: from ota_activity_versions a, ota_events e
2192: where a.activity_version_id = e.activity_version_id
2193: and e.event_id = p_event_id;
2194:
2195: if v_rco_id is not null and v_offering_id is not null then
2210: from ota_learning_objects
2211: start with learning_object_id =
2212: (select learning_object_id
2213: from ota_offerings o
2214: ,ota_events e
2215: where o.offering_id = e.parent_offering_id
2216: and event_id = p_event_id)
2217: connect by parent_learning_object_id = prior learning_object_id;
2218:
2298:
2299: function get_play_button_for_test(
2300: p_user_id fnd_user.user_id%type,
2301: p_user_type ota_attempts.user_type%type,
2302: p_event_id ota_events.event_id%type,
2303: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2304:
2305: v_lo_id ota_learning_objects.learning_object_id%type;
2306: begin
2305: v_lo_id ota_learning_objects.learning_object_id%type;
2306: begin
2307: select offr.learning_object_id
2308: into v_lo_id
2309: from ota_offerings offr, ota_events evt
2310: where evt.event_id = p_event_id and
2311: evt.parent_offering_id = offr.offering_id;
2312:
2313: /*===============================================
2336:
2337: --bug6683076.Added for getting the play button
2338:
2339: function get_play_eval_button(
2340: p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2341: p_user_id fnd_user.user_id%type,
2342: p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2343: p_object_id OTA_EVALUATIONS.OBJECT_ID%TYPE,
2344: p_object_type OTA_EVALUATIONS.OBJECT_TYPE%TYPE,
2363: and internal_state = 'F'; --bug#7311115
2364:
2365: CURSOR c_mandatory_info is
2366: select eval.eval_mandatory_flag,eval.evaluation_id
2367: from ota_evaluations eval,ota_events evt
2368: where evt.activity_version_id = eval.object_id(+)
2369: and evt.event_id = p_event_id
2370: and eval.evaluation_id is not null --bug 7184369
2371: and (eval.object_type = 'A' or eval.object_type is null);
2442:
2443: --Enhancement: 7310093 SIP: A NEW FIELD WHICH CAN GIVE THE STATUS OF THE COURSE / CLASS EVALUATION
2444:
2445: function get_admin_eval_status(
2446: p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2447: p_user_id fnd_user.user_id%type,
2448: p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2449: p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2450: p_test_id OTA_TESTS.TEST_ID%TYPE) return varchar2 is
2466: and internal_state = 'F';
2467:
2468: CURSOR c_mandatory_info is
2469: select eval.eval_mandatory_flag,eval.evaluation_id
2470: from ota_evaluations eval,ota_events evt
2471: where evt.activity_version_id = eval.object_id(+)
2472: and evt.event_id = p_event_id
2473: and eval.evaluation_id is not null --bug 7184369
2474: and (eval.object_type = 'A' or eval.object_type is null);
2585: -- ---------------------------------------------------------------------------
2586: function get_wait_duration_for_test(
2587: p_user_id fnd_user.user_id%type,
2588: p_user_type ota_attempts.user_type%type,
2589: p_event_id ota_events.event_id%type,
2590: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2591:
2592: v_lo_id ota_learning_objects.learning_object_id%type;
2593: v_wait_duration varchar2(80);
2597:
2598: begin
2599: select offr.learning_object_id
2600: into v_lo_id
2601: from ota_offerings offr, ota_events evt
2602: where evt.event_id = p_event_id and
2603: evt.parent_offering_id = offr.offering_id;
2604:
2605: v_wait_duration := get_wait_duration_for_lo(p_user_id, p_user_type, v_lo_id, p_cert_prd_enrollment_id);
2696: ----------------------------------------------------------------------------
2697: --
2698: FUNCTION get_player_status(p_user_id IN NUMBER,
2699: p_user_type IN ota_attempts.user_type%type,
2700: p_event_id IN ota_events.event_id%TYPE,
2701: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
2702: RETURN VARCHAR2 IS
2703:
2704: CURSOR c_learning_object IS
2702: RETURN VARCHAR2 IS
2703:
2704: CURSOR c_learning_object IS
2705: SELECT ofr.learning_object_id
2706: FROM ota_events oev, ota_offerings ofr
2707: WHERE oev.parent_offering_id = ofr.offering_id
2708: AND oev.event_id = p_event_id;
2709:
2710: CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2721: WHERE booking_id = p_booking_id;
2722:
2723: CURSOR c_imported_offering IS
2724: SELECT offering_id
2725: FROM ota_events
2726: WHERE event_id = p_event_id;
2727:
2728: l_proc VARCHAR2(72) := g_package|| 'get_player_status';
2729:
2730: l_learning_object_id ota_offerings.learning_object_id%TYPE;
2731:
2732: l_status VARCHAR2(30) := null;
2733:
2734: l_imported_offering ota_events.offering_id%TYPE := null;
2735:
2736: BEGIN
2737: hr_utility.set_location(' Step:'|| l_proc, 10);
2738:
2796: ----------------------------------------------------------------------------
2797: --
2798: FUNCTION get_enroll_lo_time(p_user_id IN NUMBER,
2799: p_user_type IN ota_attempts.user_type%type,
2800: p_event_id IN ota_events.event_id%TYPE,
2801: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
2802: RETURN VARCHAR2 IS
2803:
2804: CURSOR c_learning_object IS
2802: RETURN VARCHAR2 IS
2803:
2804: CURSOR c_learning_object IS
2805: SELECT ofr.learning_object_id
2806: FROM ota_events oev, ota_offerings ofr
2807: WHERE oev.parent_offering_id = ofr.offering_id
2808: AND oev.event_id = p_event_id;
2809:
2810: CURSOR c_learning_object_time(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2821: WHERE booking_id = p_booking_id;
2822:
2823: CURSOR c_imported_offering IS
2824: SELECT offering_id
2825: FROM ota_events
2826: WHERE event_id = p_event_id;
2827:
2828: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_time';
2829:
2830: l_learning_object_id ota_offerings.learning_object_id%TYPE;
2831:
2832: l_time VARCHAR2(30) := null;
2833:
2834: l_imported_offering ota_events.offering_id%TYPE := null;
2835:
2836: BEGIN
2837: hr_utility.set_location(' Step:'|| l_proc, 10);
2838:
2896: ----------------------------------------------------------------------------
2897: --
2898: FUNCTION get_enroll_lo_score(p_user_id IN NUMBER,
2899: p_user_type IN ota_attempts.user_type%type,
2900: p_event_id IN ota_events.event_id%TYPE,
2901: p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
2902: RETURN VARCHAR2 IS
2903:
2904: CURSOR c_learning_object IS
2902: RETURN VARCHAR2 IS
2903:
2904: CURSOR c_learning_object IS
2905: SELECT ofr.learning_object_id
2906: FROM ota_events oev, ota_offerings ofr
2907: WHERE oev.parent_offering_id = ofr.offering_id
2908: AND oev.event_id = p_event_id;
2909:
2910: CURSOR c_learning_object_score(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
2921: WHERE booking_id = p_booking_id;
2922:
2923: CURSOR c_imported_offering IS
2924: SELECT offering_id
2925: FROM ota_events
2926: WHERE event_id = p_event_id;
2927:
2928: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_score';
2929:
2930: l_learning_object_id ota_offerings.learning_object_id%TYPE;
2931:
2932: l_score VARCHAR2(30) := null;
2933:
2934: l_imported_offering ota_events.offering_id%TYPE := null;
2935:
2936: BEGIN
2937: hr_utility.set_location(' Step:'|| l_proc, 10);
2938:
3002: ----------------------------------------------------------------------------
3003: --
3004: FUNCTION get_enroll_lo_status(p_user_id IN NUMBER,
3005: p_user_type IN ota_attempts.user_type%type,
3006: p_event_id IN ota_events.event_id%TYPE,
3007: p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
3008: p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
3009: p_mode IN number default null,
3010: p_chk_active_cert_flag varchar2 default 'N')
3015: CURSOR c_delivery_mode IS
3016: SELECT ocu.online_flag, ofr.learning_object_id,
3017: to_date(to_char(nvl(oev.course_end_date, to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || nvl(oev.course_end_time, '23:59'), 'YYYY/MM/DD HH24:MI'),
3018: ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, oev.timezone)
3019: FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
3020: WHERE oev.parent_offering_id = ofr.offering_id
3021: AND ofr.delivery_mode_id = ocu.category_usage_id
3022: AND oev.event_id = p_event_id;
3023:
3042: WHERE booking_id = p_booking_id;
3043:
3044: CURSOR c_imported_offering IS
3045: SELECT offering_id
3046: FROM ota_events
3047: WHERE event_id = p_event_id;
3048:
3049: -- Bug 4665032
3050: CURSOR csr_best_prf (l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3072: l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_status';
3073:
3074: l_online_flag ota_category_usages.online_flag%TYPE;
3075: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3076: l_course_end_date ota_events.course_end_date%TYPE;
3077: l_sysdate ota_events.course_end_date%TYPE;
3078:
3079: l_status VARCHAR2(30) := null;
3080: l_status_name ota_booking_status_types_tl.name%TYPE := null;
3073:
3074: l_online_flag ota_category_usages.online_flag%TYPE;
3075: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3076: l_course_end_date ota_events.course_end_date%TYPE;
3077: l_sysdate ota_events.course_end_date%TYPE;
3078:
3079: l_status VARCHAR2(30) := null;
3080: l_status_name ota_booking_status_types_tl.name%TYPE := null;
3081:
3084: l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
3085: l_decode_lesson_status VARCHAR2(1);
3086:
3087: -- Bug 3725560
3088: l_imported_offering ota_events.offering_id%TYPE := null;
3089:
3090: l_cert_prd_enrollment_ids varchar2(4000) := null;
3091: l_person_id ota_cert_enrollments.person_id%type := null;
3092: l_contact_id ota_cert_enrollments.contact_id%type := null;
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:
3235: l_return VARCHAR2(100);
3236: l_enable_move_to_history boolean;
3237: l_type varchar2(1);
3238: l_proc VARCHAR2(72) := g_package||'get_history_button';
3239:
3240: l_imported_offering ota_events.offering_id%TYPE;
3241: l_booking_status_type ota_booking_status_types.type%TYPE;
3242:
3243: CURSOR c_imported_offering IS
3244: SELECT offering_id
3241: l_booking_status_type ota_booking_status_types.type%TYPE;
3242:
3243: CURSOR c_imported_offering IS
3244: SELECT offering_id
3245: FROM ota_events
3246: WHERE event_id = p_event_id;
3247:
3248: CURSOR c_history_enabled IS
3249: SELECT lesson_status
3354: -- Public
3355: -- {End of Comments}
3356: ------------------------------------------------------------------
3357: --Added method get_lo_completion_date_time,similar to get_lo_completion_date but returns a date allowing UI sort
3358: FUNCTION get_lo_completion_date(p_event_id IN ota_events.event_id%type,
3359: p_user_id IN NUMBER,
3360: p_user_type IN ota_attempts.user_type%type,
3361: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3362: p_module_name IN VARCHAR2 default 'LEARNER')
3362: p_module_name IN VARCHAR2 default 'LEARNER')
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);
3387: -- (Select Online_Flag from ota_category_usages where Category_Usage_Id = ofr.Delivery_Mode_Id) Online_Flag
3388: ocu.Synchronous_Flag Sync_Flag,
3389: ocu.Online_Flag Online_Flag,
3390: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3391: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3392: Where
3393: oev.parent_offering_id = ofr.offering_id
3394: And ofr.learning_object_id = opf.learning_object_id(+)
3395: And oev.event_id = p_event_id
3446:
3447: --Added for 6768606:COMPLETION DATE COLUMN SORT NUMERIC AND NOT BY ACTUAL DATE SORT
3448: --Similar to get_lo_completion_date,but the return type is date.Called from admin side
3449:
3450: FUNCTION get_lo_completion_date_time(p_event_id IN ota_events.event_id%type,
3451: p_user_id IN NUMBER,
3452: p_user_type IN ota_attempts.user_type%type,
3453: p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3454: p_module_name IN VARCHAR2 default 'LEARNER')
3454: p_module_name IN VARCHAR2 default 'LEARNER')
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);
3476: ocu.Synchronous_Flag Sync_Flag,
3477: ocu.Online_Flag Online_Flag,
3478: ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
3479: ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
3480: From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3481: Where
3482: oev.parent_offering_id = ofr.offering_id
3483: And ofr.learning_object_id = opf.learning_object_id(+)
3484: And oev.event_id = p_event_id
3548:
3549:
3550: FUNCTION get_cert_lo_status(p_user_id IN NUMBER,
3551: p_user_type IN ota_attempts.user_type%type,
3552: p_event_id IN ota_events.event_id%TYPE,
3553: p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
3554: p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
3555: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
3556: p_mode IN number default null)
3558:
3559:
3560: CURSOR c_delivery_mode IS
3561: SELECT ocu.online_flag, ofr.learning_object_id, nvl(oev.course_end_date, trunc(sysdate))
3562: FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
3563: WHERE oev.parent_offering_id = ofr.offering_id
3564: AND ofr.delivery_mode_id = ocu.category_usage_id
3565: AND oev.event_id = p_event_id;
3566:
3581: l_proc VARCHAR2(72) := g_package|| 'get_cert_lo_status';
3582:
3583: l_online_flag ota_category_usages.online_flag%TYPE;
3584: l_learning_object_id ota_offerings.learning_object_id%TYPE;
3585: l_course_end_date ota_events.course_end_date%TYPE;
3586:
3587: l_status VARCHAR2(30) := null;
3588: l_status_name ota_booking_status_types_tl.name%TYPE := null;
3589:
3832: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
3833: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
3834:
3835:
3836: CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
3837: csr_cert_period_start_date in date,
3838: csr_cert_period_end_date in date,
3839: csr_person_id in number,
3840: csr_contact_id in number) IS
3845: evt.event_type,
3846: ocu.synchronous_flag,
3847: ocu.online_flag,
3848: bst.type
3849: FROM ota_events evt,
3850: ota_delegate_bookings tdb,
3851: ota_booking_status_types bst,
3852: ota_offerings ofr,
3853: ota_category_usages ocu
3897: l_contact_id number;
3898: l_cert_period_start_date date;
3899: l_cert_period_end_date date;
3900:
3901: l_online_event_id ota_events.event_id%type;
3902: l_online_evt_count number;
3903:
3904: BEGIN
3905: hr_utility.set_location('Entering :'||l_proc,5);
3953: p_is_manager in varchar2,
3954: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
3955: return varchar2 is
3956:
3957: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
3958: SELECT
3959: evt.event_id,
3960: evt.event_type,
3961: ocu.synchronous_flag,
3961: ocu.synchronous_flag,
3962: ocu.online_flag,
3963: evt.course_start_date,
3964: evt.course_end_date
3965: FROM ota_events evt,
3966: ota_offerings ofr,
3967: ota_category_usages ocu
3968: WHERE evt.event_id = csr_event_id
3969: AND evt.parent_offering_id = ofr.offering_id
3976:
3977: l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
3978: rec csr_event%rowtype;
3979: rec_cme csr_cme%rowtype;
3980: l_online_event_id ota_events.event_id%type;
3981:
3982: l_return_play_btn VARCHAR2(30) := 'DISABLE_NOT_ENROLLED';
3983: l_online_evt_count number;
3984:
4024: p_user_type in ota_attempts.user_type%type,
4025: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4026: return varchar2 is
4027:
4028: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4029: select ofr.player_toolbar_flag
4030: from ota_events evt,
4031: ota_offerings ofr
4032: where evt.parent_offering_id = ofr.offering_id
4026: return varchar2 is
4027:
4028: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4029: select ofr.player_toolbar_flag
4030: from ota_events evt,
4031: ota_offerings ofr
4032: where evt.parent_offering_id = ofr.offering_id
4033: and evt.event_id = csr_event_id;
4034:
4035: l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
4036:
4037: l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
4038:
4039: l_online_event_id ota_events.event_id%type;
4040: l_online_evt_count number;
4041:
4042: BEGIN
4043: hr_utility.set_location('Entering :'||l_proc,5);
4071: p_user_type in ota_attempts.user_type%type,
4072: p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4073: return varchar2 is
4074:
4075: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4076: SELECT
4077: evt.event_id,
4078: evt.event_type,
4079: ocu.synchronous_flag,
4080: ocu.online_flag,
4081: evt.course_start_date,
4082: evt.course_end_date,
4083: ofr.learning_object_id
4084: FROM ota_events evt,
4085: ota_offerings ofr,
4086: ota_category_usages ocu
4087: WHERE evt.event_id = csr_event_id
4088: AND evt.parent_offering_id = ofr.offering_id
4139: order by decode_lesson_status;
4140:
4141: l_proc VARCHAR2(72) := g_package||'get_cert_lo_status';
4142: rec csr_event%rowtype;
4143: l_online_event_id ota_events.event_id%type;
4144:
4145: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
4146:
4147: l_status VARCHAR2(30) := null;
4248: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4249: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
4250:
4251:
4252: CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
4253: csr_cert_period_start_date in date,
4254: csr_cert_period_end_date in date,
4255: csr_person_id in number,
4256: csr_contact_id in number) IS
4261: evt.event_type,
4262: ocu.synchronous_flag,
4263: ocu.online_flag,
4264: bst.type
4265: FROM ota_events evt,
4266: ota_delegate_bookings tdb,
4267: ota_booking_status_types bst,
4268: ota_offerings ofr,
4269: ota_category_usages ocu
4312: l_contact_id number;
4313: l_cert_period_start_date date;
4314: l_cert_period_end_date date;
4315:
4316: l_online_event_id ota_events.event_id%type;
4317: l_online_event_count number := 0;
4318:
4319: BEGIN
4320: hr_utility.set_location('Entering :'||l_proc,5);
4447: AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
4448: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
4449:
4450:
4451: CURSOR get_enrl_status(csr_activity_version_id ota_events.activity_version_id%type,
4452: csr_person_id in number,
4453: csr_contact_id in number) IS
4454: SELECT bst.type status,
4455: tdb.DATE_STATUS_CHANGED,
4457: evt.event_type,
4458: ocu.synchronous_flag,
4459: ocu.online_flag,
4460: bst.type
4461: FROM ota_events evt,
4462: ota_delegate_bookings tdb,
4463: ota_booking_status_types bst,
4464: ota_offerings ofr,
4465: ota_category_usages ocu
4482: l_contact_id number;
4483: l_cert_period_start_date date;
4484: l_cert_period_end_date date;
4485:
4486: l_online_event_id ota_events.event_id%type;
4487: l_online_evt_count number;
4488:
4489: BEGIN
4490: hr_utility.set_location('Entering :'||l_proc,5);
4532: p_is_manager in varchar2,
4533: p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
4534: return varchar2 is
4535:
4536: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4537: SELECT
4538: evt.event_id,
4539: evt.event_type,
4540: ocu.synchronous_flag,
4540: ocu.synchronous_flag,
4541: ocu.online_flag,
4542: evt.course_start_date,
4543: evt.course_end_date
4544: FROM ota_events evt,
4545: ota_offerings ofr,
4546: ota_category_usages ocu
4547: WHERE evt.event_id = csr_event_id
4548: AND evt.parent_offering_id = ofr.offering_id
4549: AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4550:
4551: l_proc VARCHAR2(72) := g_package||'get_lme_play_button';
4552: rec csr_event%rowtype;
4553: l_online_event_id ota_events.event_id%type;
4554:
4555: l_return_play_btn VARCHAR2(30) := 'DISABLE_NOT_ENROLLED';
4556: l_online_evt_count number;
4557:
4591:
4592: function get_lme_player_toolbar_flag(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
4593: return varchar2 is
4594:
4595: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4596: select ofr.player_toolbar_flag
4597: from ota_events evt,
4598: ota_offerings ofr
4599: where evt.parent_offering_id = ofr.offering_id
4593: return varchar2 is
4594:
4595: cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4596: select ofr.player_toolbar_flag
4597: from ota_events evt,
4598: ota_offerings ofr
4599: where evt.parent_offering_id = ofr.offering_id
4600: and evt.event_id = csr_event_id;
4601:
4602: l_proc VARCHAR2(72) := g_package||'get_lme_player_toolbar_flag';
4603:
4604: l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
4605:
4606: l_online_event_id ota_events.event_id%type;
4607: l_online_evt_count number;
4608:
4609: BEGIN
4610: hr_utility.set_location('Entering :'||l_proc,5);
4638: p_user_type in ota_attempts.user_type%type,
4639: p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
4640: return varchar2 is
4641:
4642: CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4643: SELECT
4644: evt.event_id,
4645: evt.event_type,
4646: ocu.synchronous_flag,
4647: ocu.online_flag,
4648: evt.course_start_date,
4649: evt.course_end_date,
4650: ofr.learning_object_id
4651: FROM ota_events evt,
4652: ota_offerings ofr,
4653: ota_category_usages ocu
4654: WHERE evt.event_id = csr_event_id
4655: AND evt.parent_offering_id = ofr.offering_id
4692: rec csr_event%rowtype;
4693: rec_prf_ord csr_prf_ord%rowtype;
4694: rec_get_act csr_get_act%rowtype;
4695:
4696: l_online_event_id ota_events.event_id%type;
4697:
4698: l_status VARCHAR2(30) := null;
4699: l_status_name ota_booking_status_types_tl.name%TYPE := null;
4700:
4767: AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
4768: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
4769:
4770:
4771: CURSOR get_enrl_status(csr_activity_version_id ota_events.activity_version_id%type,
4772: csr_person_id in number,
4773: csr_contact_id in number) IS
4774: SELECT bst.type status,
4775: tdb.DATE_STATUS_CHANGED,
4777: evt.event_type,
4778: ocu.synchronous_flag,
4779: ocu.online_flag,
4780: bst.type
4781: FROM ota_events evt,
4782: ota_delegate_bookings tdb,
4783: ota_booking_status_types bst,
4784: ota_offerings ofr,
4785: ota_category_usages ocu
4802: l_contact_id number;
4803: l_cert_period_start_date date;
4804: l_cert_period_end_date date;
4805:
4806: l_online_event_id ota_events.event_id%type;
4807: l_online_event_count number := 0;
4808: l_online_evt_count number;
4809:
4810: BEGIN
4841:
4842: End get_lme_onl_evt_count;
4843:
4844: procedure get_active_cert_prds(
4845: p_event_id ota_events.event_id%type,
4846: p_person_id ota_cert_enrollments.contact_id%type,
4847: p_contact_id ota_cert_enrollments.contact_id%type,
4848: p_cert_prd_enrollment_ids OUT NOCOPY varchar2) is
4849:
4854: OTA_CERT_ENROLLMENTS cre,
4855: OTA_CERT_PRD_ENROLLMENTS cpe,
4856: OTA_CERT_MBR_ENROLLMENTS cme,
4857: OTA_CERTIFICATION_MEMBERS cmb,
4858: OTA_EVENTS evt
4859: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
4860: AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
4861: AND (cmb.OBJECT_TYPE = 'H' AND cmb.OBJECT_ID = evt.activity_version_id)
4862: AND evt.event_id = p_event_id
4880: )));
4881:
4882: l_proc VARCHAR2(72) := g_package|| 'get_active_cert_prds';
4883:
4884: l_activity_version_id ota_events.activity_version_id%type;
4885: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
4886: l_cert_prd_enrollment_ids varchar2(4000);
4887:
4888: