[Home] [Help]
PACKAGE BODY: APPS.OTA_LO_UTILITY
Source
1 package body ota_lo_utility as
2 /* $Header: otloutil.pkb 120.68.12010000.8 2008/12/10 11:24:25 smahanka ship $ */
3
4 g_package varchar2(33) := ' ota_lo_utility.'; -- Global package name
5
6 function compute_default_lesson_status(
7 p_lo_id ota_learning_objects.learning_object_id%type,
8 p_user_id fnd_user.user_id%type,
9 p_user_type ota_attempts.user_type%type,
10 p_old_lesson_status ota_performances.lesson_status%type,
11 p_starting_url ota_learning_objects.starting_url%type,
12 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
13
14 cursor child_los(
15 p_lo_id ota_learning_objects.learning_object_id%type,
16 p_user_id fnd_user.user_id%type,
17 p_user_type ota_attempts.user_type%type) is
18 select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
19 from ota_learning_objects lo, ota_performances p
20 where lo.parent_learning_object_id = p_lo_id and
21 lo.published_flag = 'Y' and
22 p.learning_object_id(+) = lo.source_learning_object_id and
23 p.user_id(+) = p_user_id and
24 p.user_type(+) = p_user_type and
25 nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
26
27 v_completed boolean := false;
28 v_not_attempted boolean := false;
29 v_other boolean := false;
30 v_has_children boolean := false;
31 begin
32 if p_starting_url is not null then
33 return p_old_lesson_status;
34 end if;
35
36 for a_child_lo in child_los(p_lo_id, p_user_id, p_user_type) loop
37 v_has_children := true;
38
39 if a_child_lo.lesson_status = 'P' or a_child_lo.lesson_status = 'C' then
40 v_completed := true;
41 elsif a_child_lo.lesson_status = 'N' then
42 v_not_attempted := true;
43 else
44 v_other := true;
45 end if;
46 end loop;
47
48 if not v_has_children then
49 return p_old_lesson_status;
50 else
51 if v_other then
52 return 'I';
53 elsif v_completed then
54 if v_not_attempted then
55 return 'I';
56 else
57 return 'C';
58 end if;
59 else
60 return 'N';
61 end if;
62 end if;
63 end compute_default_lesson_status;
64
65
66 function compute_lesson_status(
67 p_lo_id ota_learning_objects.learning_object_id%type,
68 p_user_id fnd_user.user_id%type,
69 p_user_type ota_attempts.user_type%type,
70 p_old_lesson_status ota_performances.lesson_status%type,
71 p_starting_url ota_learning_objects.starting_url%type,
72 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) return ota_performances.lesson_status%type is
73
74 /* Completion reqs not yet implemented - GDHUTTON 12/24/03
75 cursor completion_requirements(
76 p_lo_id ota_learning_objects.learning_object_id%type,
77 p_user_id fnd_user.user_id%type,
78 p_user_type ota_attempts.user_type%type,
79 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
80 select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,
81 null as match_lesson_status, nvl(p.lesson_status, 'N') as user_lesson_status
82 from ota_performances p, ota_completion_requirements cr
83 where cr.child_learning_object_id = p_lo_id and
84 p.learning_object_id(+) = cr.parent_learning_object_id and
85 p.user_id(+) = p_user_id and
86 p.user_type(+) = p_user_type and
87 nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1)
88 order by cr.seq asc;
89 */
90
91 begin
92 /* Completion reqs not yet implemented - GDHUTTON 12/24/03
93 for a_cr in completion_requirements(p_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
94 if (a_cr.match_lesson_status is null and (a_cr.user_lesson_status = 'P' or a_cr.user_lesson_status = 'C'))
95 or a_cr.user_lesson_status = a_cr.match_lesson_status then
96 return a_cr.assigned_lesson_status;
97 end if;
98 end loop;
99 */
100
101 -- Either there were no completion requirements or none were fulfilled.
102 -- Return the default status.
103 return compute_default_lesson_status(p_lo_id, p_user_id, p_user_type, p_old_lesson_status, p_starting_url, p_cert_prd_enroll_id);
104 end compute_lesson_status;
105
106
107 -- Returns true if a transition from v_old_status to v_new_status is
108 -- allowed and necessary
109 function lesson_status_transition_valid(
110 p_old_lesson_status ota_performances.lesson_status%type,
111 p_new_lesson_status ota_performances.lesson_status%type) return boolean is
112 begin
113 return
114 p_new_lesson_status <> p_old_lesson_status and
115 p_old_lesson_status <> 'P' and
116 (p_old_lesson_status <> 'C' or p_new_lesson_status = 'P');
117 end lesson_status_transition_valid;
118
119
120 function get_contact_id_for_party(
121 p_party_id hz_parties.party_id%type) return number is
122
123 cursor contact(
124 p_party_id hz_parties.party_id%type) is
125 select acct_role.cust_account_role_id
126 from hz_cust_account_roles acct_role,
127 hz_relationships rel,
128 hz_cust_accounts role_acct
129 where acct_role.party_id = rel.party_id and
130 acct_role.role_type = 'CONTACT' and
131 acct_role.cust_account_id = role_acct.cust_account_id and
132 role_acct.party_id = rel.object_id and
133 rel.subject_id = p_party_id and
134 rel.subject_table_name = 'HZ_PARTIES' and
135 rel.object_table_name = 'HZ_PARTIES';
136
137 v_result hz_parties.party_id%type;
138 begin
139 open contact(p_party_id);
140 fetch contact into v_result;
141 close contact;
142 return v_result;
143 exception
144 when others then
145 if contact%isopen then
146 close contact;
147 end if;
148 raise;
149 end get_contact_id_for_party;
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,
159 p_new_status varchar2) is
160
161
162 begin
163 update_enrollment(p_booking_id,p_event_id,p_business_group_id,p_date_booking_placed,p_object_version_number,p_date_status_changed,p_new_status,null);
164
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,
174 p_new_status varchar2,
175 p_failed varchar2) is
176
177 v_result_object_version_number ota_finance_lines.object_version_number%type;
178 v_finance_line_id ota_finance_lines.finance_line_id%type;
179 v_booking_status_row ota_booking_status_types%rowtype;
180 v_object_version_number ota_delegate_bookings.object_version_number%type;
181 l_successful_attendance_flag varchar2(1):='N';
182 l_failure_reason varchar2(20);
183 begin
184 v_finance_line_id := null;
185 v_object_version_number := p_object_version_number;
186 v_booking_status_row :=
187 ota_enroll_in_training_ss.get_booking_status_for_web(
188 p_web_booking_status_type => p_new_status,
189 p_business_group_id => p_business_group_id);
190 If p_new_status = 'PENDING EVALUATION' Then
191 l_successful_attendance_flag:='N';
192 elsif p_new_status = 'ATTENDED' Then
193 if p_failed = 'Y' then
194 l_failure_reason:= 'OF';--6777581
195 elsif (p_failed is null) then
196 l_successful_attendance_flag:='Y';
197 end if;
198 End If;
199
200 ota_tdb_api_upd2.update_enrollment(
201 p_booking_id => p_booking_id,
202 p_event_id => p_event_id,
203 p_failure_reason => l_failure_reason,
204 p_object_version_number => v_object_version_number,
205 p_booking_status_type_id => v_booking_status_row.booking_status_type_id,
206 p_tfl_object_version_number => v_result_object_version_number,
207 p_finance_line_id => v_finance_line_id,
208 p_date_status_changed => p_date_status_changed,
209 p_date_booking_placed => p_date_booking_placed,
210 p_successful_attendance_flag => l_successful_attendance_flag);
211 end update_enrollment;
212
213
214 --Added for updating the enrollment status to 'Attended'
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
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,
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
238 book.delegate_person_id = p_person_id and
239 ev.event_id = p_event_id and
240 book.booking_status_type_id = stype.booking_status_type_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,
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,
257 hz_cust_accounts role_acct
258 where nvl(ev.course_start_date, sysdate) <= sysdate and
259 book.event_id = ev.event_id and
260 book.booking_status_type_id = stype.booking_status_type_id and
261 ev.event_id = p_event_id and
262 stype.type = 'E' and
263 book.delegate_contact_id = acct_role.cust_account_role_id and
264 acct_role.party_id = rel.party_id and
265 acct_role.role_type = 'CONTACT' and
266 acct_role.cust_account_id = role_acct.cust_account_id and
267 role_acct.party_id = rel.object_id and
268 rel.subject_id = p_party_id and
269 rel.subject_table_name = 'HZ_PARTIES' and
270 rel.object_table_name = 'HZ_PARTIES';
271
272 cursor perf_lesson_status is
273 select per.lesson_status,
274 offe.learning_object_id
275 from ota_performances per,
276 ota_offerings offe,
277 ota_events evt
278 where evt.parent_offering_id = offe.offering_id and
279 offe.learning_object_id = per.learning_object_id(+) and
280 evt.event_id = p_event_id and
281 (per.user_type is null or per.user_type = p_user_type) and
282 (per.user_id is null or per.user_id = p_user_id);
283
284
285 l_proc varchar2(72) := g_package||'update_enrollment_status';
286 l_status varchar2(80) :='ATTENDED';
287 l_perf_status varchar2(1);
288 l_learning_object_id ota_offerings.learning_object_id%type;
289 l_failed varchar2(1):= null;
290
291 begin
292 hr_utility.set_location('Entering:'|| l_proc, 10);
293 open perf_lesson_status;
294 fetch perf_lesson_status into l_perf_status,l_learning_object_id;
295 close perf_lesson_status;
296 if(l_perf_status = 'F') then
297 l_failed := 'Y';
298 elsif ((l_perf_status is null) and (l_learning_object_id is null)) then
299 l_failed := 'N';
300 end if;
301 If (p_user_type = 'E') Then
302 for a_booking in person_bookings(p_event_id, p_user_id) loop
303 update_enrollment(
304 a_booking.booking_id,
305 a_booking.event_id,
306 a_booking.business_group_id,
307 a_booking.date_booking_placed,
308 a_booking.object_version_number,
309 sysdate,
310 l_status,
311 l_failed);
312 end loop;
313 else --Added for 6826434.
314 for a_booking in party_bookings(p_event_id, p_user_id) loop
315 update_enrollment(
316 a_booking.booking_id,
317 a_booking.event_id,
318 a_booking.business_group_id,
319 a_booking.date_booking_placed,
320 a_booking.object_version_number,
321 sysdate,
322 l_status,
323 l_failed);
324 end loop;
325
326 End If;
327 hr_utility.set_location('Exiting:'|| l_proc, 20);
328 end update_enrollment_status;
329
330
331
332 procedure update_enroll_status_for_lo(
333 p_lo_id ota_learning_objects.learning_object_id%type,
334 p_user_id fnd_user.user_id%type,
335 p_user_type ota_attempts.user_type%type,
336 p_date date) is
337
338
339
340
341
342 l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
343
344 begin
345 hr_utility.set_location('Entering:'|| l_proc, 10);
346 update_enroll_status_for_lo(p_lo_id,p_user_id,p_user_type,p_date,null);
347
348 hr_utility.set_location('Exiting:'|| l_proc, 15);
349 end update_enroll_status_for_lo;
350
351 procedure update_enroll_status_for_lo(
352 p_lo_id ota_learning_objects.learning_object_id%type,
353 p_user_id fnd_user.user_id%type,
354 p_user_type ota_attempts.user_type%type,
355 p_date date,
356 p_failed varchar2) is
357
358 -- This cursor finds all events in all offerings which offer the LO and in
359 -- which the person is enrolled with a status of 'PLACED'. Note that we
360 -- use source_learning_object_id here in case reuse by reference is ever
361 -- implemented.
362 cursor person_bookings(
363 p_lo_id ota_learning_objects.learning_object_id%type,
364 p_person_id ota_delegate_bookings.delegate_person_id%type) is
365 select book.booking_id,
366 ev.event_id,
367 book.business_group_id,
368 book.date_booking_placed,
369 book.object_version_number,
370 stype.type --Added for 7110517.
371 from ota_events ev,
372 ota_offerings offr,
373 ota_learning_objects lo,
374 ota_delegate_bookings book,
375 ota_booking_status_types stype
376 where lo.source_learning_object_id = p_lo_id and
377 lo.learning_object_id = offr.learning_object_id and
378 offr.offering_id = ev.parent_offering_id and
379 nvl(ev.course_start_date, sysdate) <= sysdate and
380 book.event_id = ev.event_id and
381 book.delegate_person_id = p_person_id and
382 book.booking_status_type_id = stype.booking_status_type_id and
383 (stype.type = 'P' or
384 (stype.type = 'A' and --6777581.Added 'A' for
385 nvl(book.successful_attendance_flag,'N')='N'));--failed candidates.
386
387
388
389 -- This cursor finds all events in all offerings which offer the LO and in
390 -- which the party is enrolled. Note that we use source_learning_object_id
391 -- here in case reuse by reference is ever implemented.
392 cursor party_bookings(
393 p_lo_id ota_learning_objects.learning_object_id%type,
394 p_party_id ota_delegate_bookings.delegate_contact_id%type) is
395 select book.booking_id,
396 ev.event_id,
397 book.business_group_id,
398 book.date_booking_placed,
399 book.object_version_number,
400 stype.type --Added fro 7110517.
401 from ota_events ev,
402 ota_offerings offr,
403 ota_learning_objects lo,
404 ota_delegate_bookings book,
405 ota_booking_status_types stype,
406 hz_cust_account_roles acct_role,
407 hz_relationships rel,
408 hz_cust_accounts role_acct
409 where lo.source_learning_object_id = p_lo_id and
410 lo.learning_object_id = offr.learning_object_id and
411 offr.offering_id = ev.parent_offering_id and
412 nvl(ev.course_start_date, sysdate) <= sysdate and
413 book.event_id = ev.event_id and
414 book.booking_status_type_id = stype.booking_status_type_id and
415 (stype.type = 'P' or
416 (stype.type = 'A' and --6777581.Added 'A' for
417 nvl(book.successful_attendance_flag,'N')='N')) and --failed candidates.
418 book.delegate_contact_id = acct_role.cust_account_role_id and
419 acct_role.party_id = rel.party_id and
420 acct_role.role_type = 'CONTACT' and
421 acct_role.cust_account_id = role_acct.cust_account_id and
422 role_acct.party_id = rel.object_id and
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
432 and test_id = l_test_id
433 and user_id = p_user_id
434 and user_type = p_user_type;
435
436
437
438 Cursor csr_evt_evaluations is
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
448 (act_eval.object_type is null or act_eval.object_type = 'A')
449 and evt.event_id = l_event_id
450 and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --7172501
451
452 l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
453 l_act_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
454 l_evt_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
455 l_eval_mand_flag varchar2(1);
456 l_dummy ota_attempts.attempt_id%type;
457 l_is_attempted boolean :=false;
458 l_status varchar2(80) :='ATTENDED';
459 l_user_type ota_attempts.user_type%type;
460
461 begin
462 hr_utility.set_location('Entering:'|| l_proc, 10);
463 if p_user_type = 'E' then
464 for a_booking in person_bookings(p_lo_id, p_user_id) loop
465 l_event_id := a_booking.event_id;
466 open csr_evt_evaluations;
467 fetch csr_evt_evaluations into l_evt_eval_id,l_eval_mand_flag,l_act_eval_id;
468 close csr_evt_evaluations;
469 if(l_eval_mand_flag = 'Y') then
470 select decode(l_evt_eval_id,l_act_eval_id,null,nvl(l_evt_eval_id,l_act_eval_id))
471 into l_test_id from dual;
472 open csr_attempt_info;
473 fetch csr_attempt_info into l_dummy;
474 if csr_attempt_info%found Then
475 l_is_attempted := true;
476 else
477 l_is_attempted := false;
478 end if;
479 close csr_attempt_info;
480 if(not l_is_attempted) then
481 if(a_booking.type<>'A') then --Added for 7110517.
482 l_status:='PENDING EVALUATION';
483 else
484 l_status:='ATTENDED';
485 end if;
486 else
487 l_status:='ATTENDED';
488 end if;
489 else
490 l_status := 'ATTENDED';
491 end if;
492 update_enrollment(
493 a_booking.booking_id,
494 a_booking.event_id,
495 a_booking.business_group_id,
496 a_booking.date_booking_placed,
497 a_booking.object_version_number,
498 p_date,
499 l_status,
500 p_failed);
501 end loop;
502 else
503 for a_booking in party_bookings(p_lo_id, p_user_id) loop
504 --Added for 6826434.
505 l_event_id := a_booking.event_id;
506 open csr_evt_evaluations;
507 fetch csr_evt_evaluations into l_evt_eval_id,l_eval_mand_flag,l_act_eval_id;
508 close csr_evt_evaluations;
509 if(l_eval_mand_flag = 'Y') then
510 select decode(l_evt_eval_id,l_act_eval_id,null,nvl(l_evt_eval_id,l_act_eval_id))
511 into l_test_id from dual;
512 open csr_attempt_info;
513 fetch csr_attempt_info into l_dummy;
514 if csr_attempt_info%found Then
515 l_is_attempted := true;
516 else
517 l_is_attempted := false;
518 end if;
519 close csr_attempt_info;
520 if(not l_is_attempted) then
521 if(a_booking.type<>'A') then --Added for 7110517.
522 l_status:='PENDING EVALUATION';
523 else
524 l_status:='ATTENDED';
525 end if;
526 else
527 l_status:='ATTENDED';
528 end if;
529 else
530 l_status := 'ATTENDED';
531 end if;
532 --end of code changes fro 6826434.
533 update_enrollment(
534 a_booking.booking_id,
535 a_booking.event_id,
536 a_booking.business_group_id,
537 a_booking.date_booking_placed,
538 a_booking.object_version_number,
539 p_date,
540 l_status,
541 p_failed);
542 end loop;
543 end if;
544 hr_utility.set_location('Exiting:'|| l_proc, 15);
545 end update_enroll_status_for_lo;
546
547
548 procedure update_cme_status_for_lo(
549 p_lo_id ota_learning_objects.learning_object_id%type,
550 p_date date,
551 p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
552
553 -- This cursor finds all certification member records in the certification
554 -- period that area associated with this learning object.
555 cursor cert_member_enrollments(
556 p_learning_object_id ota_learning_objects.learning_object_id%type,
557 p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
558 select distinct(cme.cert_mbr_enrollment_id),
559 cme.member_status_code,
560 cme.object_version_number,
561 cme.cert_member_id,
562 cme.cert_prd_enrollment_id
563 from ota_certification_members cm, ota_cert_mbr_enrollments cme,
564 ota_offerings o, ota_cert_prd_enrollments cpe
565 where
566 cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
567 and cme.cert_member_id = cm.certification_member_id
568 and cm.object_id = o.activity_version_id
569 and o.learning_object_id = p_learning_object_id
570 -- filter ended offerings
571 and trunc(sysdate) between trunc(o.start_date) and nvl(trunc(o.end_date), trunc(sysdate))
572 and cme.cert_prd_enrollment_id = cpe.cert_prd_enrollment_id
573 and cpe.period_status_code <> 'CANCELLED'
574 and trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
575 and cme.member_status_code <> 'CANCELLED';
576
577 l_new_mbr_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
578
579 begin
580 l_new_mbr_status_code := 'COMPLETED';
581
582 for a_cert_mbr_enrollment in cert_member_enrollments(p_lo_id, p_cert_prd_enroll_id) loop
583
584
585 --call upd cme api
586 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
587 (p_effective_date => sysdate
588 ,p_object_version_number => a_cert_mbr_enrollment.object_version_number
589 ,p_cert_member_id => a_cert_mbr_enrollment.cert_member_id
590 ,p_cert_prd_enrollment_id => p_cert_prd_enroll_id
591 ,p_cert_mbr_enrollment_id => a_cert_mbr_enrollment.cert_mbr_enrollment_id
592 ,p_member_status_code => l_new_mbr_status_code
593 ,p_completion_date => p_date);
594
595 -- cascade status to certification period
596 ota_cme_util.update_cpe_status(a_cert_mbr_enrollment.cert_mbr_enrollment_id, a_cert_mbr_enrollment.cert_prd_enrollment_id);
597
598 end loop;
599 end update_cme_status_for_lo;
600
601
602 procedure set_performance_lesson_status(
603 p_lo_id ota_learning_objects.learning_object_id%type,
604 p_user_id fnd_user.user_id%type,
605 p_user_type ota_attempts.user_type%type,
606 p_lesson_status ota_performances.lesson_status%type,
607 p_cascaded boolean,
608 p_date date,
609 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
610
611 cursor affected_los(
612 p_lo_id ota_learning_objects.learning_object_id%type,
613 p_user_id fnd_user.user_id%type,
614 p_user_type ota_attempts.user_type%type,
615 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
616 select parent.learning_object_id as learning_object_id,
617 nvl(perf.lesson_status, 'N') as lesson_status,
618 parent.starting_url as starting_url
619 from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
620 where child.source_learning_object_id = p_lo_id and
621 child.parent_learning_object_id = parent.learning_object_id and
622 parent.learning_object_id = parent.source_learning_object_id and
623 parent.starting_url is null and
624 perf.user_id(+) = p_user_id and
625 perf.user_type(+) = p_user_type and
626 perf.learning_object_id(+) = parent.learning_object_id and
627 nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
628 /* Completion requirements not yet implemented - GDHUTTON 12/24/03
629 union
630 select cr.child_learning_object_id as learning_object_id,
631 nvl(perf.lesson_status, 'N') as lesson_status,
632 lo.starting_url as starting_url
633 from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo
634 where cr.parent_learning_object_id = p_lo_id and
635 cr.child_learning_object_id = lo.learning_object_id and
636 perf.learning_object_id(+) = cr.child_learning_object_id and
637 perf.user_id(+) = p_user_id and
638 perf.user_type(+) = p_user_type and
639 nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
640 */
641 v_completed_date ota_performances.completed_date%type;
642 v_source_lo_id ota_learning_objects.learning_object_id%type;
643 v_business_group_id ota_learning_objects.business_group_id%type;
644 v_old_lesson_status ota_performances.lesson_status%type;
645 v_new_lesson_status ota_performances.lesson_status%type;
646 v_performance_source ota_performances.source%type;
647 begin
648 -- The performance is actually stored on the source learning object, so get that ID.
649 -- Also get the business group in case we have to create a new performance record.
650 select source_learning_object_id, business_group_id
651 into v_source_lo_id, v_business_group_id
652 from ota_learning_objects
653 where learning_object_id = p_lo_id;
654
655 begin
656 -- Find the old status and completed date.
657 select p.lesson_status, p.completed_date
658 into v_old_lesson_status, v_completed_date
659 from ota_performances p
660 where p.learning_object_id = v_source_lo_id and
661 p.user_id = p_user_id and
662 p.user_type = p_user_type and
663 nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
664
665 -- Check if any work needs to be done
666 if (not p_cascaded and p_lesson_status <> v_old_lesson_status) or
667 lesson_status_transition_valid(v_old_lesson_status, p_lesson_status) then
668
669 -- See if the completed_date should change.
670 if v_old_lesson_status <> 'P' and v_old_lesson_status <> 'C' and
671 (p_lesson_status = 'P' or p_lesson_status = 'C' or p_lesson_status = 'F') then
672 if(p_lesson_status <> 'F') then
673 v_completed_date := p_date;
674 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
675 if p_cert_prd_enroll_id is not null then
676 update_cme_status_for_lo(p_lo_id, p_date, p_cert_prd_enroll_id);
677 end if;
678 else
679 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date, 'Y');
680 end if;
681 elsif (v_old_lesson_status = 'P' or v_old_lesson_status = 'C') and
682 p_lesson_status <> 'P' and p_lesson_status <> 'C' then
683 -- With current rules, this should never happen.
684 v_completed_date := null;
685 end if;
686
687 if p_cascaded then
688 v_performance_source := 'CASCADE';
689 else
690 v_performance_source := 'ATTEMPT';
691 end if;
692
693 update ota_performances
694 set lesson_status = p_lesson_status,
695 completed_date = v_completed_date,
696 last_updated_by = p_user_id,
697 last_update_date = p_date,
698 source = v_performance_source,
699 overridden_by = null,
700 overridden_date = null
701 where user_id = p_user_id and
702 user_type = p_user_type and
703 learning_object_id = v_source_lo_id and
704 nvl(cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
705 --6777581.Added to update the enrollment status of learners who have already completed the learning object.
706 elsif (p_lesson_status = 'P' or p_lesson_status = 'C')and (p_lesson_status = v_old_lesson_status) then
707 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
708 -- Since we did not actually change anything, we just return
709 -- so that we don't try to cascade the non-change.
710 return;
711 end if;
712
713 exception
714 when NO_DATA_FOUND then
715 if p_lesson_status <> 'N' then
716
717 -- See if the completed_date should be filled in.
718 if p_lesson_status = 'P' or p_lesson_status = 'C' then
719 v_completed_date := p_date;
720 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
721 if p_cert_prd_enroll_id is not null then
722 update_cme_status_for_lo(v_source_lo_id, p_date, p_cert_prd_enroll_id);
723 end if;
724 else
725 v_completed_date := null;
726 end if;
727
728 if p_cascaded then
729 v_performance_source := 'CASCADE';
730 else
731 v_performance_source := 'ATTEMPT';
732 end if;
733
734 -- Insert a new ota_performances record.
735 insert into ota_performances
736 (performance_id, user_id, user_type, learning_object_id,
737 lesson_status, score, time, completed_date,
738 created_by, creation_date, last_updated_by, last_update_date,
739 source, object_version_number, business_group_id, cert_prd_enrollment_id)
740 values
741 (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
742 p_lesson_status, -1000, -1001, v_completed_date,
743 p_user_id, p_date, p_user_id, p_date,
744 v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
745 else
746 return;
747 end if;
748 end;
749
750 -- Cascade the change to any affected RCOs. These include parents of this
751 -- ota_learning_objects, plus those that indicate this ota_learning_objects as a completion
752 -- requirement.
753 for a_lo in affected_los(v_source_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
754 -- if the current status is Passed, nothing can override it, so we will
755 -- save the effort of computing the new status
756 if a_lo.lesson_status <> 'P' then
757 v_new_lesson_status := compute_lesson_status(a_lo.learning_object_id, p_user_id, p_user_type, a_lo.lesson_status, a_lo.starting_url, p_cert_prd_enroll_id);
758 if lesson_status_transition_valid(a_lo.lesson_status, v_new_lesson_status) then
759 set_performance_lesson_status(a_lo.learning_object_id, p_user_id, p_user_type, v_new_lesson_status, true, p_date, p_cert_prd_enroll_id);
760 end if;
761 end if;
762 end loop;
763 end set_performance_lesson_status;
764
765
766 procedure set_performance_lesson_status(
767 p_lo_id ota_learning_objects.learning_object_id%type,
768 p_user_id fnd_user.user_id%type,
769 p_user_type ota_attempts.user_type%type,
770 p_lesson_status ota_performances.lesson_status%type,
771 p_date date,
772 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
773 begin
774 set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, false, p_date, p_cert_prd_enroll_id);
775 end set_performance_lesson_status;
776
777
778 procedure set_performance_lesson_status(
779 p_lo_id ota_learning_objects.learning_object_id%type,
780 p_user_id fnd_user.user_id%type,
781 p_user_type ota_attempts.user_type%type,
782 p_lesson_status ota_performances.lesson_status%type,
783 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
784 begin
785 set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, sysdate, p_cert_prd_enroll_id);
786 end set_performance_lesson_status;
787
788
789 procedure set_performance_time(
790 p_lo_id ota_learning_objects.learning_object_id%type,
791 p_user_id fnd_user.user_id%type,
792 p_user_type ota_attempts.user_type%type,
793 p_time ota_performances.time%type,
794 p_cascaded boolean,
795 p_date date,
796 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
797
798 cursor parent_los(
799 p_lo_id ota_learning_objects.learning_object_id%type,
800 p_user_id fnd_user.user_id%type,
801 p_user_type ota_attempts.user_type%type,
802 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
803 select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
804 from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
805 where child.source_learning_object_id = p_lo_id and
806 child.parent_learning_object_id = parent.learning_object_id and
807 parent.learning_object_id = parent.source_learning_object_id and
808 parent.starting_url is null and
809 perf.user_id(+) = p_user_id and
810 perf.user_type(+) = p_user_type and
811 perf.learning_object_id(+) = parent.learning_object_id and
812 nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
813
814 cursor distinct_child_los(
815 p_lo_id ota_learning_objects.learning_object_id%type,
816 p_user_id fnd_user.user_id%type,
817 p_user_type ota_attempts.user_type%type,
818 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
819 select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
820 from ota_performances p,
821 (select distinct learning_object_id, source_learning_object_id, starting_url
822 from ota_learning_objects
823 where learning_object_id <> p_lo_id
824 start with learning_object_id = p_lo_id
825 connect by parent_learning_object_id = prior learning_object_id) lo
826 where lo.starting_url is not null and
827 p.learning_object_id(+) = lo.source_learning_object_id and
828 p.user_id(+) = p_user_id and
829 p.user_type(+) = p_user_type and
830 nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
831
832 v_source_lo_id ota_learning_objects.learning_object_id%type;
833 v_business_group_id ota_learning_objects.business_group_id%type;
834 v_old_time ota_performances.time%type;
835 v_new_time ota_performances.time%type;
836 v_performance_source ota_performances.source%type;
837 begin
838 -- The performance is actually stored on the source learning object, so get that ID.
839 -- Also get the business group in case we have to create a new performance record.
840 select source_learning_object_id, business_group_id
841 into v_source_lo_id, v_business_group_id
842 from ota_learning_objects
843 where learning_object_id = p_lo_id;
844
845 begin
846 select p.time
847 into v_old_time
848 from ota_performances p
849 where p.learning_object_id = v_source_lo_id and
850 p.user_id = p_user_id and
851 p.user_type = p_user_type and
852 nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
853
854 if p_time <> v_old_time then
855 update ota_performances
856 set time = p_time,
857 last_updated_by = p_user_id,
858 last_update_date = p_date
859 where user_id = p_user_id and
860 user_type = p_user_type and
861 learning_object_id = v_source_lo_id and
862 nvl(cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
863 else
864 return;
865 end if;
866 exception
867 when NO_DATA_FOUND then
868 if p_time <> 0 then
869
870 if p_cascaded then
871 v_performance_source := 'CASCADE';
872 else
873 v_performance_source := 'ATTEMPT';
874 end if;
875
876 insert into ota_performances
877 (performance_id, user_id, user_type, learning_object_id,
878 lesson_status, score, time,
879 created_by, creation_date, last_updated_by, last_update_date,
880 source, object_version_number, business_group_id, cert_prd_enrollment_id)
881 values
882 (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
883 'N', -1000, p_time,
884 p_user_id, p_date, p_user_id, p_date,
885 v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
886 else
887 return;
888 end if;
889 end;
890
891 -- Get all the source-parents of all the targets of the ota_learning_objects
892 for a_parent_lo in parent_los(p_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
893 v_new_time := 0;
894 for a_child_lo in distinct_child_los(a_parent_lo.learning_object_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
895 if a_child_lo.time > 0 then
896 v_new_time := v_new_time + a_child_lo.time;
897 end if;
898 end loop;
899
900 if v_new_time <> a_parent_lo.time then
901 set_performance_time(a_parent_lo.learning_object_id, p_user_id, p_user_type, v_new_time, true, p_date, p_cert_prd_enroll_id);
902 end if;
903 end loop;
904 end set_performance_time;
905
906
907 procedure set_performance_time(
908 p_lo_id ota_learning_objects.learning_object_id%type,
909 p_user_id fnd_user.user_id%type,
910 p_user_type ota_attempts.user_type%type,
911 p_time ota_performances.time%type,
912 p_date date,
913 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
914 begin
915 set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, false, p_date, p_cert_prd_enroll_id);
916 end set_performance_time;
917
918
919 procedure set_performance_time(
920 p_lo_id ota_learning_objects.learning_object_id%type,
921 p_user_id fnd_user.user_id%type,
922 p_user_type ota_attempts.user_type%type,
923 p_time ota_performances.time%type,
924 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
925 begin
926 set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, sysdate, p_cert_prd_enroll_id);
927 end set_performance_time;
928
929
930 function lo_is_attemptable(
931 p_lo_id ota_learning_objects.learning_object_id%type,
932 p_launch_type ota_attempts.launch_type%type,
933 p_reason out nocopy number) return boolean is
934
935 v_published_flag ota_learning_objects.published_flag%type;
936 v_starting_url ota_learning_objects.starting_url%type;
937 v_start_date_active ota_learning_objects.start_date_active%type;
938 v_end_date_active ota_learning_objects.end_date_active%type;
939
940 begin
941 select published_flag,
942 starting_url,
943 start_date_active,
944 end_date_active
945 into v_published_flag,
946 v_starting_url,
947 v_start_date_active,
948 v_end_date_active
949 from ota_learning_objects
950 where learning_object_id = p_lo_id;
951
952 if v_published_flag = 'N' then
953 p_reason := LO_REASON_NOT_PUBLISHED;
954 elsif v_starting_url is null then
955 p_reason := LO_REASON_NO_STARTING_URL;
956 elsif v_start_date_active is not null and sysdate < v_start_date_active then
957 p_reason := LO_REASON_NOT_STARTED;
958 elsif v_end_date_active is not null and v_end_date_active < sysdate then
959 p_reason := LO_REASON_EXPIRED;
960 else
961 p_reason := LO_REASON_NO_REASON;
962 end if;
963
964 return (p_reason = LO_REASON_NO_REASON);
965 exception
966 when NO_DATA_FOUND then
967 p_reason := LO_REASON_NO_SUCH_LO;
968 return false;
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;
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'
986 into v_dummy
987 from ota_learning_objects
988 where learning_object_id = p_lo_id
989 start with learning_object_id = v_root_lo_id
990 connect by parent_learning_object_id = prior learning_object_id;
991
992 return true;
993 exception
994 when NO_DATA_FOUND then
995 return false;
996 end lo_is_in_event;
997
998
999 function user_meets_prerequisites(
1000 p_lo_id ota_learning_objects.learning_object_id%type,
1001 p_user_id fnd_user.user_id%type,
1002 p_user_type ota_attempts.user_type%type) return boolean is
1003
1004 --Commented for Bug#3582893
1005 -- The cursor below is replaced with two cursor since the below query causes a Full Table Scan
1006 /*
1007 cursor csr_chk_prereqs is
1008 select 1
1009 from ota_prerequisites preq,
1010 ota_performances perf
1011 where preq.parent_object_id = p_lo_id and
1012 preq.object_id = perf.learning_object_id(+) and
1013 perf.user_id(+) = p_user_id and
1014 perf.user_type(+) = p_user_type and
1015 nvl(perf.lesson_status, 'N') not in ('P', 'C');
1016
1017 l_found number;
1018 l_return boolean;
1019 begin
1020 open csr_chk_prereqs;
1021 fetch csr_chk_prereqs into l_found;
1022 l_return := csr_chk_prereqs%notfound;
1023 close csr_chk_prereqs;
1024 return l_return;
1025 */
1026 CURSOR csr_get_prereqs IS
1027 SELECT object_id
1028 FROM ota_prerequisites
1029 where parent_object_id = p_lo_id
1030 and parent_type = 'LO';
1031
1032 CURSOR csr_get_performances(csr_lo_id NUMBER) IS
1033 SELECT 1
1034 FROM ota_performances
1035 WHERE learning_object_id = csr_lo_id
1036 AND user_id = p_user_id
1037 AND user_type = p_user_type
1038 AND lesson_status in ('P', 'C');
1039
1040 l_found number;
1041 l_return boolean := TRUE;
1042 begin
1043 FOR rec IN csr_get_prereqs
1044 LOOP
1045 IF csr_get_performances%ISOPEN THEN
1046 CLOSE csr_get_performances;
1047 END IF;
1048 OPEN csr_get_performances(rec.object_id);
1049 FETCH csr_get_performances INTO l_found;
1050 IF csr_get_performances%NOTFOUND THEN
1051 l_return := FALSE;
1052 CLOSE csr_get_performances;
1053 exit;
1054 END IF;
1055 END LOOP;
1056 IF csr_get_performances%ISOPEN THEN
1057 CLOSE csr_get_performances;
1058 END IF;
1059 return l_return;
1060 end user_meets_prerequisites;
1061
1062
1063 function user_exceeded_attempt_limit(
1064 p_lo_id ota_learning_objects.learning_object_id%type,
1065 p_user_id fnd_user.user_id%type,
1066 p_user_type ota_attempts.user_type%type,
1067 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1068
1069 v_max_attempts ota_tests.max_attempts%type;
1070 v_user_attempts number;
1071 begin
1072 begin
1073 select t.max_attempts
1074 into v_max_attempts
1075 from ota_tests t, ota_learning_objects lo
1076 where lo.learning_object_id = p_lo_id and
1077 lo.test_id = t.test_id;
1078 exception
1079 when NO_DATA_FOUND then -- the LO is not a test, so no attempt limit exists
1080 return false;
1081 end;
1082
1083 -- The LO is a test... if no max attempts return false immediately.
1084 if v_max_attempts is null then
1085 return false;
1086 else
1087 -- Count the user's attempts
1088 begin
1089 select count(*)
1090 into v_user_attempts
1091 from ota_attempts a, ota_tests t
1092 where a.user_id = p_user_id and
1093 a.user_type = p_user_type and
1094 a.learning_object_id = p_lo_id and
1095 nvl(a.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enrollment_id, -1) and
1096 a.test_id = t.test_id and
1097 a.initialized_flag = 'Y' and
1098 a.attempt_type <> 'I' and
1099 ((a.suspend_data is null and
1100 t.resume_flag = 'Y' and
1101 a.internal_state <> 'A' and
1102 (a.suspend_data is null or a.suspend_data <> 'I')) or
1103 (t.resume_flag = 'N' and (a.suspend_data is null or a.suspend_data <> 'I')));
1104 exception
1105 when NO_DATA_FOUND then
1106 -- No attempts, can't have exceeded the limit
1107 return false;
1108 end;
1109
1110 return v_user_attempts >= v_max_attempts;
1111 end if;
1112 end user_exceeded_attempt_limit;
1113
1114
1115 function get_next_attempt_date_for_lo(
1116 p_lo_id ota_learning_objects.learning_object_id%type,
1117 p_user_id fnd_user.user_id%type,
1118 p_user_type ota_attempts.user_type%type,
1119 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return date is
1120
1121 v_earliest_attempt_date date;
1122 begin
1123 select nvl(max(a.timestamp), sysdate) + nvl(t.duration_between_attempt, 0)
1124 into v_earliest_attempt_date
1125 from ota_tests t,
1126 ota_learning_objects lo,
1127 ota_attempts a
1128 where t.test_id = a.test_id
1129 and a.initialized_flag = 'Y'
1130 and ((t.resume_flag = 'Y'
1131 and a.internal_state <> 'A'
1132 and (a.suspend_data is null or a.suspend_data <> 'I'))
1133 OR (t.resume_flag = 'N'
1134 and (a.suspend_data is null or a.suspend_data <> 'I')))
1135 and a.learning_object_id = p_lo_id
1136 and a.user_id = p_user_id
1137 and a.user_type = p_user_type
1138 and nvl(p_cert_prd_enrollment_id, -1) = nvl(a.cert_prd_enrollment_id, -1)
1139 group by t.test_id,t.max_attempts, t.duration_between_attempt;
1140
1141 return v_earliest_attempt_date;
1142 exception
1143 /* this is NOT a test, or there are no attempts */
1144 when NO_DATA_FOUND then
1145 return null;
1146
1147 when others then
1148 return null;
1149 end get_next_attempt_date_for_lo;
1150
1151
1152 function user_must_wait_to_attempt(
1153 p_lo_id ota_learning_objects.learning_object_id%type,
1154 p_user_id fnd_user.user_id%type,
1155 p_user_type ota_attempts.user_type%type,
1156 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1157
1158 v_earliest_attempt_date date;
1159 begin
1160 v_earliest_attempt_date := get_next_attempt_date_for_lo(
1161 p_lo_id,
1162 p_user_id,
1163 p_user_type,
1164 p_cert_prd_enrollment_id);
1165
1166 return (v_earliest_attempt_date is not null and sysdate < v_earliest_attempt_date);
1167 end user_must_wait_to_attempt;
1168
1169
1170 function user_can_attempt_lo(
1171 p_lo_id ota_learning_objects.learning_object_id%type,
1172 p_user_id fnd_user.user_id%type,
1173 p_user_type ota_attempts.user_type%type,
1174 p_launch_type ota_attempts.launch_type%type,
1175 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1176 p_reason out nocopy number) return boolean is
1177 begin
1178 if not lo_is_attemptable(p_lo_id, p_launch_type, p_reason) then
1179 return false;
1180 end if;
1181
1182 -- Instructors need not meet prerequisites because their performance is never
1183 -- recorded (therefore they CAN'T meet them). Also, instructors are not subject
1184 -- to attempt limits or waits between attempts, although their attempts would
1185 -- never be counted anyway.
1186 if p_launch_type = 'INSTRUCTOR' then
1187 return true;
1188 else
1189 if not user_meets_prerequisites(p_lo_id, p_user_id, p_user_type) then
1190 p_reason := LO_REASON_PREREQS_NOT_MET;
1191 return false;
1192 end if;
1193
1194 if user_exceeded_attempt_limit(p_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
1195 p_reason := LO_REASON_ATTEMPTS_EXCEEDED;
1196 return false;
1197 end if;
1198
1199 if user_must_wait_to_attempt(p_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
1200 p_reason := LO_REASON_DURATION_NOT_MET;
1201 return false;
1202 end if;
1203 end if;
1204
1205 return true;
1206 end user_can_attempt_lo;
1207
1208
1209 function user_can_attempt_lo(
1210 p_lo_id ota_learning_objects.learning_object_id%type,
1211 p_user_id fnd_user.user_id%type,
1212 p_user_type ota_attempts.user_type%type,
1213 p_launch_type ota_attempts.launch_type%type default '',
1214 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
1215
1216 l_reason number;
1217 begin
1218 if user_can_attempt_lo(p_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id, l_reason) then
1219 return 'Y';
1220 else
1221 return 'N';
1222 end if;
1223 end user_can_attempt_lo;
1224
1225
1226 function user_can_attempt_lo_3(
1227 p_lo_id ota_learning_objects.learning_object_id%type,
1228 p_event_id ota_events.event_id%type,
1229 p_user_id fnd_user.user_id%type,
1230 p_user_type ota_attempts.user_type%type,
1231 p_launch_type ota_attempts.launch_type%type,
1232 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1233 p_reason out nocopy number) return boolean is
1234 begin
1235 if not lo_is_in_event(p_event_id, p_lo_id) then
1236 p_reason := LO_REASON_NOT_IN_EVENT;
1237 return false;
1238 end if;
1239
1240 if not user_can_attempt_lo(p_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id, p_reason) then
1241 return false;
1242 end if;
1243
1244 return true;
1245 end user_can_attempt_lo_3;
1246
1247 function lo_is_in_cert(
1248 p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type,
1249 p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
1250
1251 cursor root_los(p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
1252 select o.learning_object_id
1253 from ota_offerings o, ota_certification_members cm, ota_cert_mbr_enrollments cme
1254 where o.activity_version_id = cm.object_id
1255 and cm.certification_member_id = cme.cert_member_id
1256 and cme.cert_prd_enrollment_id = p_cert_prd_enroll_id;
1257
1258 cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1259 p_root_lo_id ota_learning_objects.learning_object_id%type) is
1260 select learning_object_id
1261 from ota_learning_objects
1262 where learning_object_id = p_lo_id
1263 start with learning_object_id = p_root_lo_id
1264 connect by parent_learning_object_id = prior learning_object_id;
1265
1266 begin
1267
1268 for a_root_lo in root_los(p_cert_prd_enroll_id) loop
1269 for a_child_lo in child_los(p_lo_id, a_root_lo.learning_object_id) loop
1270 return true;
1271 end loop;
1272 end loop;
1273
1274 return false;
1275
1276 end lo_is_in_cert;
1277
1278 function user_can_attempt_lo(
1279 p_lo_id ota_learning_objects.learning_object_id%type,
1280 p_event_id ota_events.event_id%type,
1281 p_user_id fnd_user.user_id%type,
1282 p_user_type ota_attempts.user_type%type,
1283 p_launch_type ota_attempts.launch_type%type,
1284 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1285 p_reason out nocopy number) return boolean is
1286 begin
1287 if p_cert_prd_enroll_id is not null and not lo_is_in_cert(p_cert_prd_enroll_id, p_lo_id) then
1288 p_reason := LO_REASON_LO_NOT_IN_CERT;
1289 return false;
1290 end if;
1291
1292 if not user_can_attempt_lo_3(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id, p_reason) then
1293 return false;
1294 end if;
1295
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
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);
1322 v_is_instructor := (p_launch_type = 'INSTRUCTOR');
1323
1324 -- Neither learners not instructors can play a course past its end date.
1325 if v_course_expired then
1326 p_reason := EVENT_REASON_EXPIRED;
1327
1328 -- Only instructors can play a course before it starts.
1329 elsif not v_course_started and not v_is_instructor then
1330 p_reason := EVENT_REASON_NOT_STARTED;
1331
1332 end if;
1333
1334 return (p_reason = EVENT_REASON_NO_REASON);
1335 exception
1336 when NO_DATA_FOUND then
1337 p_reason := EVENT_REASON_NO_SUCH_EVENT;
1338 return false;
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
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
1355 where book.delegate_person_id = p_person_id and
1356 book.event_id = p_event_id and
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
1366 where book.delegate_contact_id = p_contact_id and
1367 book.event_id = p_event_id and
1368 book.booking_status_type_id = stype.booking_status_type_id and
1369 stype.type in ('P', 'A');
1370 begin
1371 p_reason := EVENT_REASON_NO_REASON;
1372
1373 if p_user_type = 'E' then
1374 for a_booking in person_bookings(p_event_id, p_user_id) loop
1375 return true;
1376 end loop;
1377 else
1378 for a_booking in contact_bookings(p_event_id, get_contact_id_for_party(p_user_id)) loop
1379 return true;
1380 end loop;
1381 end if;
1382
1383 -- If we fall through, the user is not enrolled
1384 p_reason := EVENT_REASON_NOT_ENROLLED;
1385 return false;
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
1395 begin
1396
1397 if p_user_type = 'E' then
1398
1399 for an_instructor in (
1400 select 1 as dummy
1401 from
1402 ota_suppliable_resources tsr,
1403 ota_resource_bookings trb
1404 where
1405 trb.forum_id is null and
1406 trb.chat_id is null and
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
1416
1417 p_reason := EVENT_REASON_NO_REASON;
1418 return true;
1419 end loop;
1420
1421 end if;
1422
1423 -- If we fall through, the user is not an instructor.
1424 p_reason := EVENT_REASON_NOT_INSTRUCTOR;
1425 return false;
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
1435
1436 v_now date := trunc(sysdate);--Bug 3554773
1437 begin
1438 if not event_is_attemptable(p_event_id, v_now, p_launch_type, p_reason) then
1439 return 'N';
1440 end if;
1441
1442 if p_launch_type = 'INSTRUCTOR' then
1443 if not user_is_instructor(p_event_id, p_user_id, p_user_type, v_now, p_reason) then
1444 return 'N';
1445 end if;
1446 else
1447 if not user_is_enrolled(p_event_id, p_user_id, p_user_type, v_now, p_reason) then
1448 return 'N';
1449 end if;
1450 end if;
1451
1452 p_reason := EVENT_REASON_NO_REASON;
1453 return 'Y';
1454 end user_can_attempt_event;
1455
1456
1457 function cert_is_attemptable(
1458 p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1459 p_date date,
1460 p_reason out nocopy number) return boolean is
1461
1462 v_cert_start_date ota_certifications_b.start_date_active%type;
1463 v_cert_end_date ota_certifications_b.end_date_active%type;
1464 v_cert_prd_enroll_start ota_cert_prd_enrollments.cert_period_start_date%type;
1465 v_cert_prd_enroll_end ota_cert_prd_enrollments.cert_period_end_date%type;
1466 begin
1467 select c.start_date_active, c.end_date_active, cpe.cert_period_start_date, cpe.cert_period_end_date
1468 into v_cert_start_date, v_cert_end_date, v_cert_prd_enroll_start, v_cert_prd_enroll_end
1469 from ota_certifications_b c, ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
1470 where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
1471 cpe.cert_enrollment_id = ce.cert_enrollment_id and
1472 ce.certification_id = c.certification_id;
1473
1474 if v_cert_start_date is not null and p_date < v_cert_start_date then
1475 p_reason := CERT_REASON_NOT_STARTED;
1476 elsif v_cert_end_date is not null and p_date > v_cert_end_date then
1477 p_reason := CERT_REASON_EXPIRED;
1478 elsif v_cert_prd_enroll_start is not null and p_date < v_cert_prd_enroll_start then
1479 p_reason := CERT_PRD_REASON_NOT_STARTED;
1480 elsif v_cert_prd_enroll_end is not null and p_date > v_cert_prd_enroll_end then
1481 p_reason := CERT_PRD_REASON_EXPIRED;
1482 else
1483 p_reason := CERT_REASON_NO_REASON;
1484 end if;
1485
1486 return (p_reason = CERT_REASON_NO_REASON);
1487 exception
1488 when NO_DATA_FOUND then
1489 p_reason := CERT_REASON_NO_SUCH_CERT;
1490 return false;
1491 end cert_is_attemptable;
1492
1493 function user_is_enrolled_in_cert(
1494 p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1495 p_user_id fnd_user.user_id%type,
1496 p_user_type ota_attempts.user_type%type,
1497 p_date date,
1498 p_reason out nocopy number) return boolean is
1499
1500 v_cert_unsubscribe_date ota_cert_enrollments.unenrollment_date%type;
1501 --v_cert_enr_user_id ota_cert_enrollments.person_id%type;
1502 v_cert_enr_user_id number;
1503 l_user_id number;
1504 begin
1505 select ce.unenrollment_date, nvl(ce.person_id, ce.contact_id)
1506 into v_cert_unsubscribe_date, v_cert_enr_user_id
1507 from ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
1508 where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
1509 cpe.cert_enrollment_id = ce.cert_enrollment_id;
1510
1511 --bug 4725513
1512 --for ext learner call get_contact_id_for_party(p_user_id)
1513 if p_user_type = 'E' then
1514 l_user_id := p_user_id;
1515 else
1516 l_user_id := get_contact_id_for_party(p_user_id);
1517 end if;
1518
1519 if v_cert_unsubscribe_date is not null then
1520 p_reason := CERT_REASON_UNSUBSCRIBED;
1521 elsif (v_cert_enr_user_id is not null) and (not v_cert_enr_user_id = l_user_id) then
1522 p_reason := CERT_REASON_INVALID_USER;
1523 else
1524 p_reason := CERT_REASON_NO_REASON;
1525 end if;
1526 return (p_reason = CERT_REASON_NO_REASON);
1527 exception
1528 when NO_DATA_FOUND then
1529 p_reason := CERT_REASON_NO_SUCH_CERT;
1530 return false;
1531 end user_is_enrolled_in_cert;
1532
1533 function user_can_attempt_cert(
1534 p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1535 p_user_id fnd_user.user_id%type,
1536 p_user_type ota_attempts.user_type%type,
1537 p_reason out nocopy number) return varchar2 is
1538
1539 v_now date := trunc(sysdate);--Bug 3554773
1540 begin
1541
1542 if not cert_is_attemptable(p_cert_prd_enroll_id, v_now, p_reason) then
1543 return 'N';
1544 end if;
1545
1546 if not user_is_enrolled_in_cert(p_cert_prd_enroll_id, p_user_id, p_user_type, v_now, p_reason) then
1547 return 'N';
1548 end if;
1549
1550 p_reason := CERT_REASON_NO_REASON;
1551 return 'Y';
1552 end user_can_attempt_cert;
1553
1554
1555 function find_previous_lo_id(
1556 p_start ota_learning_objects.learning_object_id%type,
1557 p_current ota_learning_objects.learning_object_id%type,
1558 p_current_starting_url ota_learning_objects.starting_url%type,
1559 p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1560
1561 cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1562 select learning_object_id, starting_url
1563 from ota_learning_objects
1564 where parent_learning_object_id = p_lo_id and
1565 published_flag = 'Y'
1566 order by child_seq asc;
1567 begin
1568 if p_current = p_start then
1569 return true;
1570 end if;
1571
1572 if p_current_starting_url is not null then
1573 p_previous := p_current;
1574 end if;
1575
1576 for a_child in children(p_current) loop
1577 if find_previous_lo_id(p_start, a_child.learning_object_id, a_child.starting_url, p_previous) then
1578 return true;
1579 end if;
1580 end loop;
1581
1582 return false;
1583 end find_previous_lo_id;
1584
1585
1586 function get_previous_lo_id(
1587 p_root_lo_id ota_learning_objects.learning_object_id%type,
1588 p_starting_lo_id ota_learning_objects.learning_object_id%type,
1589 p_user_id fnd_user.user_id%type,
1590 p_user_type ota_attempts.user_type%type,
1591 p_launch_type ota_attempts.launch_type%type,
1592 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1593
1594 v_starting_url ota_learning_objects.starting_url%type :=null;
1595 v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1596 v_reason number;
1597 begin
1598 select starting_url
1599 into v_starting_url
1600 from ota_learning_objects
1601 where learning_object_id = p_root_lo_id;
1602
1603 if find_previous_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_previous_lo_id) then
1604 if user_can_attempt_lo(v_previous_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id, v_reason) then
1605 return v_previous_lo_id;
1606 else
1607 return get_previous_lo_id(p_root_lo_id, v_previous_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1608 end if;
1609 else
1610 return null;
1611 end if;
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,
1621 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1622
1623 v_root_lo_id ota_learning_objects.learning_object_id%type;
1624 begin
1625 select o.learning_object_id
1626 into v_root_lo_id
1627 from ota_events e, ota_offerings o
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);
1632 end get_previous_event_lo_id;
1633
1634
1635 function find_next_lo_id(
1636 p_start ota_learning_objects.learning_object_id%type,
1637 p_current ota_learning_objects.learning_object_id%type,
1638 p_current_starting_url ota_learning_objects.starting_url%type,
1639 p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1640
1641 cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1642 select learning_object_id, starting_url
1643 from ota_learning_objects
1644 where parent_learning_object_id = p_lo_id and
1645 published_flag = 'Y'
1646 order by child_seq asc;
1647 v_result ota_learning_objects.learning_object_id%type;
1648 begin
1649 if (p_found_start or p_start is null) and p_current_starting_url is not null then
1650 return p_current;
1651 end if;
1652
1653 if p_current = p_start then
1654 p_found_start := true;
1655 end if;
1656
1657 for a_child in children(p_current) loop
1658 v_result := find_next_lo_id(p_start, a_child.learning_object_id, a_child.starting_url, p_found_start);
1659 if v_result is not null then
1660 return v_result;
1661 end if;
1662 end loop;
1663
1664 return null;
1665 end find_next_lo_id;
1666
1667
1668 function get_next_lo_id(
1669 p_root_lo_id ota_learning_objects.learning_object_id%type,
1670 p_starting_lo_id ota_learning_objects.learning_object_id%type,
1671 p_user_id fnd_user.user_id%type,
1672 p_user_type ota_attempts.user_type%type,
1673 p_launch_type ota_attempts.launch_type%type,
1674 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1675
1676
1677 v_starting_url ota_learning_objects.starting_url%type := null;
1678 v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1679 v_false boolean := false;
1680 v_reason number;
1681 begin
1682 return get_next_lo_id(p_root_lo_id,null, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1683 end get_next_lo_id;
1684
1685
1686
1687 function get_next_lo_id(
1688 p_root_lo_id ota_learning_objects.learning_object_id%type,
1689 p_root_starting_url ota_learning_objects.starting_url%type,
1690 p_starting_lo_id ota_learning_objects.learning_object_id%type,
1691 p_user_id fnd_user.user_id%type,
1692 p_user_type ota_attempts.user_type%type,
1693 p_launch_type ota_attempts.launch_type%type,
1694 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1695
1696 v_starting_url ota_learning_objects.starting_url%type := null;
1697 v_next_lo_id ota_learning_objects.learning_object_id%type := null;
1698 v_false boolean := false;
1699 v_reason number;
1700 begin
1701
1702 if p_root_starting_url is not null then
1703 v_starting_url := p_root_starting_url;
1704 else
1705 select starting_url
1706 into v_starting_url
1707 from ota_learning_objects
1708 where learning_object_id = p_root_lo_id;
1709 end if;
1710
1711 v_next_lo_id := find_next_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_false);
1712 if v_next_lo_id is not null then
1713 if user_can_attempt_lo(v_next_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id, v_reason) then
1714 return v_next_lo_id;
1715 else
1716 return get_next_lo_id(p_root_lo_id, v_starting_url, v_next_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1717 end if;
1718 else
1719 return null;
1720 end if;
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,
1730 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1731
1732 v_root_lo_id ota_learning_objects.learning_object_id%type;
1733 begin
1734 select o.learning_object_id
1735 into v_root_lo_id
1736 from ota_events e, ota_offerings o
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
1750
1751 cursor ordered_los(
1752 p_root_lo_id ota_learning_objects.learning_object_id%type,
1753 p_user_id fnd_user.user_id%type,
1754 p_user_type ota_attempts.user_type%type,
1755 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
1756 select a.learning_object_id, max(a.attempt_id) max_attempt
1757 from ota_attempts a,
1758 (select learning_object_id, starting_url
1759 from ota_learning_objects
1760 start with learning_object_id = p_root_lo_id
1761 connect by parent_learning_object_id = prior learning_object_id) lo
1762 where a.user_id = p_user_id and
1763 a.user_type = p_user_type and
1764 a.learning_object_id = lo.learning_object_id and
1765 lo.starting_url is not null and
1766 ((p_cert_prd_enroll_id is null and a.cert_prd_enrollment_id is null) OR
1767 (p_cert_prd_enroll_id is not null and a.cert_prd_enrollment_id = p_cert_prd_enroll_id)
1768 )
1769 group by a.learning_object_id
1770 order by max_attempt desc;
1771
1772 v_root_lo_id ota_learning_objects.learning_object_id%type;
1773 v_reason number;
1774 begin
1775 select o.learning_object_id
1776 into v_root_lo_id
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
1782 for a_lo in ordered_los(v_root_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
1783 if user_can_attempt_lo(a_lo.learning_object_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id, v_reason) then
1784 return a_lo.learning_object_id;
1785 end if;
1786 end loop;
1787
1788 return get_first_lo_id(p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id);
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
1798
1799 v_root_lo_id ota_learning_objects.learning_object_id%type;
1800 v_first_lo_id ota_learning_objects.learning_object_id%type;
1801 v_reason number;
1802 begin
1803 select o.learning_object_id
1804 into v_root_lo_id
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
1819 begin
1820 return get_jump_lo_id(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, null, p_reason);
1821 end get_jump_lo_id;
1822
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,
1830 p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
1831 begin
1832 if user_can_attempt_lo(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id, p_reason) then
1833 return p_lo_id;
1834 else
1835 return null;
1836 end if;
1837 end get_jump_lo_id;
1838
1839 --7574667
1840 function get_lo_type(
1841 p_lo_id ota_learning_objects.learning_object_id%type) return varchar2 is
1842
1843 --7622768
1844 cursor lo_type is
1845 select nvl(test_type_flag,'LO')
1846 from ota_tests ot,
1847 ota_learning_objects lo
1848 where ot.test_id(+) = lo.test_id
1849 and lo.learning_object_id = p_lo_id;
1850
1851 type_flag varchar2(10);
1852 begin
1853 open lo_type;
1854 fetch lo_type into type_flag;
1855 close lo_type;
1856 if type_flag is null then
1857 return 'LO';
1858 end if;
1859 return type_flag;
1860
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;
1870 begin
1871 return user_can_attempt_event(p_event_id, p_user_id, p_user_type, '', l_reason);
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
1881 l_reason number;
1882 begin
1883 return user_can_attempt_event(p_event_id, p_user_id, p_user_type, p_launch_type, l_reason);
1884 end user_can_attempt_event;
1885
1886
1887 -- Author: sbhullar
1888 -- Author: sbhullar
1889 -- ----------------------------------------------------------------
1890 -- ------------------<get_lo_title_for_tree >--------------------
1891 -- ----------------------------------------------------------------
1892 -- {Start of Comments}
1893 --
1894 -- Description:
1895 -- This function will be used to show get lo name, status and time
1896 -- in the format lo_name [Status: status, Time: hh:mm:ss] if p_mode
1897 -- is 1 else it gives the lo status icon
1898 -- IN
1899 -- p_lo_id
1900 -- p_user_id
1901 -- p_user_type
1902 -- p_mode
1903 -- p_active_cert_flag
1904 --
1905 -- Post Failure:
1906 -- None.
1907 -- Access Status
1908 -- Public
1909 -- {End of Comments}
1910 ------------------------------------------------------------------
1911 FUNCTION get_lo_title_for_tree(p_lo_id IN NUMBER,
1912 p_user_id IN NUMBER,
1913 p_user_type IN ota_attempts.user_type%type,
1914 p_mode IN NUMBER default 1,
1915 p_active_cert_flag varchar2 default 'N')
1916 RETURN varchar2
1917 IS
1918 l_proc VARCHAR2(72) := g_package||'get_lo_title_for_tree';
1919 l_lo_name varchar(240);
1920 l_lo_status varchar(80);
1921 l_lo_time number;
1922 l_lo_score number;
1923 l_lo_status_icon varchar(30);
1924 l_Seconds number;
1925 l_Minutes number;
1926 l_Hours number;
1927 l_formatted_hour varchar(20) := '';
1928 l_formatted_min varchar(20) := '';
1929 l_formatted_sec varchar(20) := '';
1930 l_formatted_time varchar(20) := '';
1931 l_return_tree_title varchar(500) := '';
1932 l_max_performance_id ota_performances.performance_id%type;
1933 l_tst_grade_flag ota_tests.grade_flag%TYPE;
1934 l_var_score VARCHAR2(100);
1935 l_lo_completed_date VARCHAR2(100);
1936 l_lo_completed_time VARCHAR2(10);
1937 l_lo_completed_date_tz VARCHAR2(100);
1938 l_lo_lesson_status ota_performances.lesson_status%type;
1939
1940 CURSOR c_get_lo_tree_link(p_performance_id in number) is
1941 Select
1942 olo.name Name,
1943 nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
1944 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
1945 nvl(opf.time,0) Time,
1946 opf.score Score,
1947 decode(opf.lesson_status
1948 ,'C','player_status_c.gif'
1949 ,'F','player_status_f.gif'
1950 ,'I','player_status_i.gif'
1951 ,'P','player_status_p.gif'
1952 ,'N','player_status_n.gif'
1953 ,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type),
1954 'N','player_status_no_prereq.gif',
1955 'Y','player_status_n.gif')
1956 ,'player_status_n.gif') STATUS_ICON
1957 , tst.grade_flag
1958 , to_char(opf.completed_date)
1959 , to_char(opf.completed_date, 'HH24:MI:SS')
1960 , opf.lesson_status
1961 , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
1962 From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
1963 OTA_TESTS tst
1964 Where
1965 olo.learning_object_id = opf.learning_object_id(+)
1966 AND tst.test_id(+) = olo.test_id
1967 And olo.Learning_object_id = p_lo_id
1968 And opf.User_id(+) = p_user_id
1969 And opf.User_type(+) = p_user_type
1970 AND opf.performance_id(+) = p_performance_id
1971 AND cert_prd_enrollment_id(+) is null;
1972
1973 CURSOR c_max_performance_id is
1974 Select max(per.performance_id)
1975 From ota_performances per
1976 Where per.learning_object_id(+) = p_lo_id
1977 And per.user_id(+) = p_user_id
1978 And per.user_type(+) = p_user_type
1979 AND per.cert_prd_enrollment_id(+) is null;
1980
1981 Begin
1982 hr_utility.set_location('Entering :'||l_proc,5);
1983
1984 open c_max_performance_id;
1985 fetch c_max_performance_id into l_max_performance_id;
1986 close c_max_performance_id;
1987
1988 if ( l_max_performance_id is null ) then
1989 l_max_performance_id := -1;
1990 end if;
1991
1992 open c_get_lo_tree_link(l_max_performance_id);
1993 fetch c_get_lo_tree_link into l_lo_name,l_lo_status,l_lo_time,l_lo_score,l_lo_status_icon , l_tst_grade_flag, l_lo_completed_date, l_lo_completed_time, l_lo_lesson_status, l_lo_completed_date_tz;
1994 close c_get_lo_tree_link;
1995
1996 If ( p_mode = 1) Then
1997 l_lo_time := round(l_lo_time);
1998
1999 l_Seconds := l_lo_time mod 60;
2000 l_Minutes := floor(l_lo_time / 60);
2001 l_Hours := floor(l_Minutes/60);
2002 l_Minutes := l_Minutes - l_Hours * 60;
2003
2004 If (l_Hours < 10) Then
2005 l_formatted_hour := '0' || l_Hours;
2006 Else
2007 l_formatted_hour := l_Hours;
2008 End If;
2009
2010 If (l_Minutes < 10) Then
2011 l_formatted_min := '0' || l_Minutes;
2012 Else
2013 l_formatted_min := l_Minutes;
2014 End If;
2015
2016 If (l_Seconds < 10) Then
2017 l_formatted_sec := '0' || l_Seconds;
2018 Else
2019 l_formatted_sec := l_Seconds;
2020 End If;
2021
2022 fnd_message.set_name('OTA', 'OTA_443358_SRCH_LO_TIME');
2023 fnd_message.set_token ('HOUR', l_formatted_hour);
2024 fnd_message.set_token ('MIN', l_formatted_min);
2025 fnd_message.set_token ('SEC', l_formatted_sec);
2026 l_formatted_time := fnd_message.get();
2027
2028 if p_active_cert_flag = 'Y' then
2029 fnd_message.set_name('OTA', 'OTA_443968_SRCH_TREE_TITLE3');
2030 elsif ((l_lo_score is null) or (l_lo_score < 0)) Then
2031 fnd_message.set_name('OTA', 'OTA_443453_SRCH_TREE_TITLE2');
2032 Else
2033 --Added for bug 3550407
2034 IF ( l_tst_grade_flag = 'P' ) THEN
2035 l_var_score := l_lo_score||'%';
2036 ELSE
2037 l_var_score := l_lo_score;
2038 END IF;
2039
2040 fnd_message.set_name('OTA', 'OTA_443357_SRCH_TREE_TITLE');
2041 fnd_message.set_token ('SCORE', l_var_score);
2042 End If;
2043
2044 If ( (l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P')
2045 and l_lo_completed_date is not null ) Then
2046 l_lo_status := l_lo_status || ' ' || l_lo_completed_date_tz;
2047 End If;
2048
2049 --if this is part of active cert prd, return status as "Not Applicable"
2050 if p_active_cert_flag = 'Y' then
2051 fnd_message.set_token ('LO_NAME', l_lo_name);
2052 fnd_message.set_token ('STATUS', hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','D'));
2053 else
2054 fnd_message.set_token ('LO_NAME', l_lo_name);
2055 fnd_message.set_token ('STATUS', l_lo_status);
2056 fnd_message.set_token ('TIME', l_formatted_time);
2057 end if;
2058
2059 l_return_tree_title := fnd_message.get();
2060 Else
2061 --if this is part of active cert prd, return icon empty circle used for "Not Attempted"
2062 if p_active_cert_flag = 'Y' then
2063 l_return_tree_title := 'player_status_n.gif';
2064 else
2065 l_return_tree_title := l_lo_status_icon;
2066 end if;
2067
2068 End If;
2069
2070 hr_utility.set_location('Leaving :'||l_proc,10);
2071 RETURN l_return_tree_title;
2072 EXCEPTION
2073 WHEN others THEN
2074 hr_utility.set_location('Leaving :'||l_proc,15);
2075
2076 RETURN l_return_tree_title;
2077 End get_lo_title_for_tree;
2078
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,
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
2093
2094
2095 v_number_of_los number(10);
2096 v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
2097 v_published_flag ota_learning_objects.published_flag%type := null;
2098 v_first_lo ota_learning_objects.learning_object_id%type := null;
2099 v_play_button_for_test varchar2(100) := null;
2100 v_rco_id ota_activity_versions.rco_id%type := null;
2101 v_offering_id ota_events.offering_id%type := null;
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
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;
2118
2119 begin
2120
2121 /*===============================================
2122 * check if this is an Online class
2123 *===============================================*/
2124 if p_online_flag <> 'Y' then
2125 return 'ENABLE_DETAIL';
2126 end if;
2127
2128
2129 /*===============================================
2130 * user is logged in as manager
2131 *===============================================*/
2132 if (p_is_manager = 'IS_MANAGER') then
2133 return 'ENABLE_DETAIL';
2134 end if;
2135
2136 /*===============================================
2137 * bug 3401855. play icon should be disabled when
2138 * enrollment status is Cancelled.
2139 * bug 3661345. Enrollment Status Validation should
2140 * be done before Class Dates Validation.
2141 *===============================================*/
2142 if (p_enrollment_status_type = 'C') then
2143 return 'DISABLE_ENR_CANCELLED';
2144 end if;
2145
2146 /*===============================================
2147 * event is scheduled but not within time window
2148 *===============================================*/
2149 open c_event_time_info;
2150 fetch c_event_time_info into l_course_start_time, l_course_end_time, l_timezone;
2151 close c_event_time_info;
2152
2153 l_sysdate := ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, l_timezone);
2154
2155 if (p_event_type = 'SCHEDULED') then
2156
2157 --Bug 5107276
2158 l_course_start_date := to_date(nvl(to_char(p_course_start_date,'YYYY/MM/DD'),'4712/12/31')||' '||nvl(l_course_start_time, '00:00'),'YYYY/MM/DD HH24:MI');
2159 l_course_end_date := to_date(nvl(to_char(p_course_end_date,'YYYY/MM/DD'),'4712/12/31')||' '||nvl(l_course_end_time, '23:59'),'YYYY/MM/DD HH24:MI');
2160
2161 --l_sysdate := to_date(to_char(trunc(sysdate),'YYYY/MM/DD')||' '|| to_char(sysdate,'HH24:MI'),'YYYY/MM/DD HH24:MI');
2162
2163 if l_sysdate < l_course_start_date then --Bug 3559931
2164 return 'DISABLE_BEFORE_START';
2165 end if;
2166 if l_course_end_date < l_sysdate then --Bug 3559931
2167 return 'DISABLE_AFTER_END';
2168 end if;
2169 elsif ( p_event_type = 'SELFPACED') then --bug 3559931
2170 if p_course_start_date is not null then
2171 if trunc(l_sysdate) < p_course_start_date then
2172 return 'DISABLE_BEFORE_START';
2173 end if;
2174 end if;
2175 if p_course_end_date is not null then
2176 if p_course_end_date < trunc(l_sysdate) then
2177 return 'DISABLE_AFTER_END';
2178 end if;
2179 end if;
2180 end if;
2181
2182 /*===============================================
2183 * if the rco_id is not null, integrate with external player
2184 * This condition needs to be on top to avoid unnecessary
2185 * checks of Lo, prerequisites etc..
2186 *===============================================*/
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
2196 return 'ENABLE_EXTERNAL_PLAY';
2197 end if;
2198
2199
2200 /*===============================================
2201 * check that this event contains only one LO
2202 * if so get the id
2203 *===============================================*/
2204 select count(learning_object_id)
2205 ,sum(learning_object_id)
2206 ,nvl(max(published_flag),'N')
2207 into v_number_of_los
2208 ,v_solo_lo_id
2209 ,v_published_flag
2210 from ota_learning_objects
2211 start with learning_object_id =
2212 (select learning_object_id
2213 from ota_offerings o
2214 ,ota_events e
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
2219
2220 /*===============================================
2221 * if there is only a single LO in the class then
2222 *===============================================*/
2223 if v_number_of_los = 1 then
2224
2225 /*===============================================
2226 * check that the LO is published
2227 *===============================================*/
2228 if v_published_flag <> 'Y' then
2229 return 'DISABLE_LO_UNPUBLISHED';
2230 end if;
2231
2232 /*===============================================
2233 * check if it is a disabled test (assessment)
2234 *===============================================*/
2235 v_play_button_for_test := get_play_button_for_test(p_user_id, p_user_type, p_event_id, p_cert_prd_enrollment_id);
2236
2237 if v_play_button_for_test is not null then
2238 return v_play_button_for_test;
2239 end if;
2240
2241 /*===============================================
2242 * check if it is a disabled from an unmet prerequisite
2243 *===============================================*/
2244
2245 if not user_meets_prerequisites(v_solo_lo_id , p_user_id, p_user_type) then
2246 return 'DISABLE_UNMET_PREREQ';
2247 end if;
2248
2249 end if;
2250
2251
2252 /*===============================================
2253 * checks end dates and enrollment
2254 *===============================================*/
2255 if user_can_attempt_event(p_event_id,p_user_id,p_user_type) = 'N' then
2256 return 'DISABLE_UNKNOWN';
2257 end if;
2258
2259 /*===============================================
2260 * Disable if there are no playable LOs
2261 *===============================================*/
2262 v_first_lo := get_first_lo_id(p_event_id, p_user_id, p_user_type, '',p_cert_prd_enrollment_id);
2263
2264 if v_first_lo is null then
2265 return 'DISABLE_UNKNOWN';
2266 end if;
2267
2268 if p_chk_active_cert_flag = 'Y' then
2269 /*===============================================
2270 * Disable if its part of active cert periods
2271 *===============================================*/
2272 if p_user_type is not null and p_user_type = 'E' then
2273 l_person_id := p_user_id;
2274 end if;
2275
2276 get_active_cert_prds(p_event_id,
2277 l_person_id,
2278 p_contact_id,
2279 l_cert_prd_enrollment_ids);
2280
2281 if l_cert_prd_enrollment_ids is not null then
2282 return 'DISABLE_CERTIFICATION';
2283 end if;
2284 end if;
2285
2286 /*===============================================
2287 * no reason found to disable
2288 *===============================================*/
2289 return 'ENABLE_PLAY';
2290
2291
2292 exception
2293 when others then
2294 return 'ENABLE_PLAY';
2295
2296 end get_play_button;
2297
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
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 /*===============================================
2314 * if this is a single lo that is a test with
2315 * no attempts remaining, disable the play
2316 *===============================================*/
2317 if user_exceeded_attempt_limit(v_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
2318 return 'DISABLE_MAX_ATTEMPTS';
2319 /*===============================================
2320 * if this is a single lo that is a test and
2321 * it is within the wait duration, disable the play
2322 *===============================================*/
2323 elsif user_must_wait_to_attempt(v_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
2324 return 'DISABLE_WAIT_DURATION';
2325 else
2326 /*===============================================
2327 * no reason found to disable
2328 *===============================================*/
2329 return null;
2330 end if;
2331
2332 exception
2333 when others then
2334 return null;
2335 end get_play_button_for_test;
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,
2345 p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2346 p_test_id OTA_TESTS.TEST_ID%TYPE) return varchar2 is
2347
2348 l_is_mandatory boolean:= true;
2349 l_is_attempted boolean:= false;
2350 l_act_mandatory OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE;
2351 l_booking_status_type OTA_BOOKING_STATUS_TYPES.TYPE%TYPE;
2352 l_attempt_id number;
2353 l_act_eval_id OTA_EVALUATIONS.evaluation_id%TYPE;
2354
2355 CURSOR c_attempts_info is
2356 Select attempt_id
2357 From ota_attempts
2358 Where event_id = p_event_id
2359 and user_id = p_user_id
2360 and (act_eval_id = l_act_eval_id
2361 or test_id = p_test_id)
2362 and attempt_status = 'C' --bug#7574667
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);
2372 begin
2373
2374
2375 /*===============================================
2376 * check if there is an evaluation
2377 *===============================================*/
2378 open c_mandatory_info;
2379 fetch c_mandatory_info into l_act_mandatory, l_act_eval_id;
2380 close c_mandatory_info;
2381
2382 if p_mand_flag is not null then
2383 if p_mand_flag = 'N' then
2384 if l_act_mandatory is null or l_act_mandatory = 'N' then
2385 l_is_mandatory:= false;
2386 end if;
2387 end if;
2388 elsif l_act_eval_id is null then
2389 return 'NO_EVAL';
2390 elsif l_act_mandatory = 'N' then
2391 l_is_mandatory:= false;
2392 end if;
2393
2394 /*===============================================
2395 * Evaluation exists, check whether to enable,
2396 * disable the evaluation, or mark as 'Done' .
2397 *===============================================*/
2398
2399 select type into l_booking_status_type
2400 from ota_booking_status_types
2401 where booking_status_type_id in (p_booking_status_type_id);
2402
2403 if l_is_mandatory then
2404 if l_booking_status_type = 'E' then
2405 return 'ENABLE_EVAL' ;
2406 elsif l_booking_status_type = 'A' then
2407 open c_attempts_info;
2408 fetch c_attempts_info into l_attempt_id;
2409 if c_attempts_info%found then
2410 l_is_attempted := true;
2411 end if;
2412 close c_attempts_info;
2413 if not l_is_attempted then
2414 return 'EVAL_CLOSED'; --7046809
2415 else
2416 return 'EVAL_DONE';
2417 end if;
2418 else
2419 return 'DISABLE_EVAL' ;
2420 end if;
2421 else
2422 if l_booking_status_type = 'A' then
2423 open c_attempts_info;
2424 fetch c_attempts_info into l_attempt_id;
2425 if c_attempts_info%found then
2426 l_is_attempted := true;
2427 end if;
2428 close c_attempts_info;
2429 if not l_is_attempted then
2430 return 'ENABLE_EVAL';
2431 else
2432 return 'EVAL_DONE';
2433 end if;
2434 else
2435 return 'DISABLE_EVAL';
2436 end if;
2437 end if;
2438 exception
2439 when others then
2440 return null;
2441 end get_play_eval_button;
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
2451
2452 l_is_mandatory boolean:= true;
2453 l_is_attempted boolean:= false;
2454 l_act_mandatory OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE;
2455 l_booking_status_type OTA_BOOKING_STATUS_TYPES.TYPE%TYPE;
2456 l_attempt_id number;
2457 l_act_eval_id OTA_EVALUATIONS.evaluation_id%TYPE;
2458
2459 CURSOR c_attempts_info is
2460 Select attempt_id
2461 From ota_attempts
2462 Where event_id = p_event_id
2463 and user_id = p_user_id
2464 and (act_eval_id = l_act_eval_id
2465 or test_id = p_test_id)
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);
2475 begin
2476
2477
2478 /*===============================================
2479 * check if there is an evaluation
2480 *===============================================*/
2481 open c_mandatory_info;
2482 fetch c_mandatory_info into l_act_mandatory, l_act_eval_id;
2483 close c_mandatory_info;
2484
2485 if p_mand_flag is not null then
2486 if p_mand_flag = 'N' then
2487 if l_act_mandatory is null or l_act_mandatory = 'N' then
2488 l_is_mandatory:= false;
2489 end if;
2490 end if;
2491 elsif l_act_eval_id is null then
2492 return 'NO_EVAL';
2493 elsif l_act_mandatory = 'N' then
2494 l_is_mandatory:= false;
2495 end if;
2496
2497 /*===============================================
2498 * Evaluation exists, check whether to enable,
2499 * disable the evaluation, or mark as 'Done' .
2500 *===============================================*/
2501
2502 select type into l_booking_status_type
2503 from ota_booking_status_types
2504 where booking_status_type_id in (p_booking_status_type_id);
2505
2506 if l_is_mandatory then
2507 if l_booking_status_type = 'E' then
2508 return 'ENABLE_MANDATORY_EVAL' ;
2509 elsif l_booking_status_type = 'A' then
2510 open c_attempts_info;
2511 fetch c_attempts_info into l_attempt_id;
2512 if c_attempts_info%found then
2513 l_is_attempted := true;
2514 end if;
2515 close c_attempts_info;
2516 if not l_is_attempted then
2517 return 'MANDATORY_EVAL_CLOSED'; --7046809
2518 else
2519 return 'MANDATORY_EVAL_DONE';
2520 end if;
2521 else
2522 return 'DISABLE_MANDATORY_EVAL' ;
2523 end if;
2524 else
2525 if l_booking_status_type = 'A' then
2526 open c_attempts_info;
2527 fetch c_attempts_info into l_attempt_id;
2528 if c_attempts_info%found then
2529 l_is_attempted := true;
2530 end if;
2531 close c_attempts_info;
2532 if not l_is_attempted then
2533 return 'ENABLE_VOLUNTARY_EVAL';
2534 else
2535 return 'VOLUNTARY_EVAL_DONE';
2536 end if;
2537 else
2538 return 'DISABLE_VOLUNTARY_EVAL';
2539 end if;
2540 end if;
2541 exception
2542 when others then
2543 return null;
2544 end get_admin_eval_status;
2545
2546
2547 --
2548 -- ----------------------------------------------------------------------------
2549 -- |-------------------------< get_wait_duration_for_lo >----------------------
2550 -- ----------------------------------------------------------------------------
2551 -- Author: gdhutton
2552 -- This function is used to get the date until which the learner has to
2553 -- wait before playing the LO again.
2554 -- [End of Comments]
2555 -- ---------------------------------------------------------------------------
2556 function get_wait_duration_for_lo(
2557 p_user_id fnd_user.user_id%type,
2558 p_user_type ota_attempts.user_type%type,
2559 p_lo_id ota_learning_objects.learning_object_id%type,
2560 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2561
2562 v_earliest_attempt_date date;
2563 begin
2564 v_earliest_attempt_date :=
2565 get_next_attempt_date_for_lo(
2566 p_lo_id,
2567 p_user_id,
2568 p_user_type,
2569 p_cert_prd_enrollment_id);
2570
2571 if v_earliest_attempt_date is not null then
2572 return to_char(v_earliest_attempt_date) || to_char(v_earliest_attempt_date, ' HH24:MI:SS');
2573 else
2574 return null;
2575 end if;
2576 end get_wait_duration_for_lo;
2577 --
2578 -- ----------------------------------------------------------------------------
2579 -- |-------------------------< get_wait_duration_for_test >--------------------
2580 -- ----------------------------------------------------------------------------
2581 -- Author: smanjuna
2582 -- This function is used to get the timestamp until which the learner has to
2583 -- wait before playing the test again. This is displayed as flyover text.
2584 -- [End of Comments]
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);
2594 --Bug 5166350
2595 wait_dur_date varchar2(20);
2596 wait_dur_time varchar2(20);
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);
2606
2607 --Bug 5166350
2608 wait_dur_date := substr(v_wait_duration, 1, length(v_wait_duration)-9);
2609 wait_dur_time := substr(v_wait_duration, length(v_wait_duration)-7);
2610 v_wait_duration := ota_timezone_util.get_date_time(wait_dur_date, wait_dur_time, 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS');
2611
2612 if v_wait_duration is not null then
2613 fnd_message.set_name('OTA', 'OTA_443502_TEST_WAIT_DRTN_TEXT');
2614 fnd_message.set_token('DATETIME', v_wait_duration);
2615 return fnd_message.get();
2616 else
2617 return null;
2618 end if;
2619 end get_wait_duration_for_test;
2620 --
2621 -- ----------------------------------------------------------------------------
2622 -- |-------------------------< Root_Folder_Exists>----------------------------|
2623 -- ----------------------------------------------------------------------------
2624 --
2625 function Root_Folder_Exists
2626 (p_folder_id in number default hr_api.g_number
2627 ,p_business_group_id in number default ota_general.get_business_group_id
2628 )
2629 return varchar2 is
2630 --
2631 -- decalare cursor
2632 cursor root_folder is
2633 select
2634 distinct 'found'
2635 from
2636 ota_lo_folders
2637 where
2638 business_group_id = p_business_group_id
2639 and folder_id <> p_folder_id
2640 and parent_folder_id is null;
2641 --
2642 -- decalre variables
2643 l_folder_flag varchar2(10);
2644 l_return varchar2(2) := 'N';
2645 l_proc varchar2(72) := g_package||'Root_Folder_Exists';
2646 --
2647 begin
2648 --
2649 hr_utility.set_location('Entering:'|| l_proc, 10);
2650 --
2651 open root_folder;
2652 fetch root_folder into l_folder_flag;
2653 IF root_folder%found THEN
2654 --
2655 l_return := 'Y';
2656 hr_utility.set_location('returning Y:'|| l_proc, 10);
2657 End if;
2658 --
2659 close root_folder;
2660 --
2661 hr_utility.set_location('Exiting:'|| l_proc, 20);
2662 --
2663 return l_return;
2664 --
2665 End Root_Folder_Exists;
2666 --
2667 -- ----------------------------------------------------------------------------
2668 -- |-------------------------< get_player_status >--------------------------|
2669 -- ----------------------------------------------------------------------------
2670 -- {Start Of Comments}
2671 --
2672 -- Description:
2673 -- This function will fetch the player staus code for online classes and enrollment
2674 -- content player status for offline classes.
2675 --
2676 -- Pre Conditions:
2677 -- None.
2678 --
2679 -- Out Arguments:
2680 -- p_user_id
2681 -- p_user_type
2682 -- p_event_id
2683 -- p_booking_id
2684 --
2685 -- Post Success:
2686 -- Processing continues.
2687 --
2688 --
2689 -- Post Failure:
2690 -- None.
2691 --
2692 -- Access Status:
2693 -- Public.
2694 --
2695 -- {End Of Comments}
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
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
2711 SELECT lesson_status
2712 FROM ota_performances
2713 WHERE user_id = p_user_id
2714 AND user_type = p_user_type
2715 AND learning_object_id = l_lo_id
2716 AND cert_prd_enrollment_id is null;
2717
2718 CURSOR c_odb_lo_status IS
2719 SELECT content_player_status
2720 FROM ota_delegate_bookings
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
2739 open c_learning_object;
2740 fetch c_learning_object into l_learning_object_id;
2741 close c_learning_object;
2742
2743 -- Check if the event is imported
2744 open c_imported_offering;
2745 fetch c_imported_offering into l_imported_offering;
2746 close c_imported_offering;
2747
2748 If (l_imported_offering is not null) Then
2749 -- Imported Offering, Status code should be taken from
2750 -- OTA_DELEGATE_BOOKINGS
2751 open c_odb_lo_status;
2752 fetch c_odb_lo_status into l_status;
2753 close c_odb_lo_status;
2754 Else
2755 -- EBS created, Status code should be taken from
2756 -- OTA_PERFORMANCES
2757 open c_learning_object_status(l_learning_object_id);
2758 fetch c_learning_object_status into l_status;
2759 close c_learning_object_status;
2760
2761 End If;
2762
2763 RETURN l_status;
2764 hr_utility.set_location(' Step:'|| l_proc, 20);
2765 END get_player_status;
2766
2767 -- ----------------------------------------------------------------------------
2768 -- |-------------------------< get_enroll_lo_time >--------------------------|
2769 -- ----------------------------------------------------------------------------
2770 -- {Start Of Comments}
2771 --
2772 -- Description:
2773 -- This function will fetch the player time for online classes and enrollment
2774 -- training time for offline classes.
2775 --
2776 -- Pre Conditions:
2777 -- None.
2778 --
2779 -- Out Arguments:
2780 -- p_user_id
2781 -- p_user_type
2782 -- p_event_id
2783 -- p_booking_id
2784 --
2785 -- Post Success:
2786 -- Processing continues.
2787 --
2788 --
2789 -- Post Failure:
2790 -- None.
2791 --
2792 -- Access Status:
2793 -- Public.
2794 --
2795 -- {End Of Comments}
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
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
2811 SELECT TO_CHAR(TRUNC(SYSDATE)+(time)/86400, 'HH24:Mi:SS')
2812 FROM ota_performances
2813 WHERE user_id = p_user_id
2814 AND user_type = p_user_type
2815 AND learning_object_id = l_lo_id
2816 AND cert_prd_enrollment_id is null;
2817
2818 CURSOR c_odb_lo_time IS
2819 SELECT total_training_time
2820 FROM ota_delegate_bookings
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
2839 open c_learning_object;
2840 fetch c_learning_object into l_learning_object_id;
2841 close c_learning_object;
2842
2843 -- Check if the event is imported
2844 open c_imported_offering;
2845 fetch c_imported_offering into l_imported_offering;
2846 close c_imported_offering;
2847
2848 If (l_imported_offering is not null) Then
2849 -- Imported Offering, Score should be taken from
2850 -- OTA_DELEGATE_BOOKINGS
2851 open c_odb_lo_time;
2852 fetch c_odb_lo_time into l_time;
2853 close c_odb_lo_time;
2854 Else
2855 -- EBS created, time should be taken from
2856 -- OTA_PERFORMANCES
2857 open c_learning_object_time(l_learning_object_id);
2858 fetch c_learning_object_time into l_time;
2859 close c_learning_object_time;
2860
2861 End If;
2862
2863 RETURN l_time;
2864 hr_utility.set_location(' Step:'|| l_proc, 20);
2865 END get_enroll_lo_time;
2866
2867 -- ----------------------------------------------------------------------------
2868 -- |-------------------------< get_enroll_lo_score >--------------------------|
2869 -- ----------------------------------------------------------------------------
2870 -- {Start Of Comments}
2871 --
2872 -- Description:
2873 -- This function will fetch the player score for online classes and enrollment
2874 -- score for offline classes.
2875 --
2876 -- Pre Conditions:
2877 -- None.
2878 --
2879 -- Out Arguments:
2880 -- p_user_id
2881 -- p_user_type
2882 -- p_event_id
2883 -- p_booking_id
2884 --
2885 -- Post Success:
2886 -- Processing continues.
2887 --
2888 --
2889 -- Post Failure:
2890 -- None.
2891 --
2892 -- Access Status:
2893 -- Public.
2894 --
2895 -- {End Of Comments}
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
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
2911 SELECT score
2912 FROM ota_performances
2913 WHERE user_id = p_user_id
2914 AND user_type = p_user_type
2915 AND learning_object_id = l_lo_id
2916 AND cert_prd_enrollment_id is null;
2917
2918 CURSOR c_odb_lo_score IS
2919 SELECT score
2920 FROM ota_delegate_bookings
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
2939 open c_learning_object;
2940 fetch c_learning_object into l_learning_object_id;
2941 close c_learning_object;
2942
2943 -- Check if the event is imported
2944 open c_imported_offering;
2945 fetch c_imported_offering into l_imported_offering;
2946 close c_imported_offering;
2947
2948 If (l_imported_offering is not null) Then
2949 -- Imported Offering, Score should be taken from
2950 -- OTA_DELEGATE_BOOKINGS
2951 open c_odb_lo_score;
2952 fetch c_odb_lo_score into l_score;
2953 close c_odb_lo_score;
2954 Else
2955 -- EBS created, Score should be taken from
2956 -- OTA_PERFORMANCES
2957 open c_learning_object_score(l_learning_object_id);
2958 fetch c_learning_object_score into l_score;
2959 close c_learning_object_score;
2960
2961 End If;
2962
2963 If ( l_score = -1000) Then
2964 l_score := null;
2965 End If;
2966
2967 RETURN l_score;
2968 hr_utility.set_location(' Step:'|| l_proc, 20);
2969 END get_enroll_lo_score;
2970 -- ----------------------------------------------------------------------------
2971 -- |-------------------------< get_enroll_lo_status >--------------------------|
2972 -- ----------------------------------------------------------------------------
2973 -- {Start Of Comments}
2974 --
2975 -- Description:
2976 -- This function will fetch the player status for online classes and enrollment
2977 -- status for offline classes.
2978 --
2979 -- Pre Conditions:
2980 -- None.
2981 --
2982 -- Out Arguments:
2983 -- p_user_id
2984 -- p_user_type
2985 -- p_event_id
2986 -- p_booking_status_type_id
2987 -- p_booking_id
2988 -- p_mode
2989 -- p_chk_active_cert_flag
2990 --
2991 -- Post Success:
2992 -- Processing continues.
2993 --
2994 --
2995 -- Post Failure:
2996 -- None.
2997 --
2998 -- Access Status:
2999 -- Public.
3000 --
3001 -- {End Of Comments}
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')
3011 RETURN VARCHAR2 IS
3012
3013
3014 --Bug 5222464
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
3024 CURSOR c_booking_status IS
3025 SELECT BST.type, BST.name
3026 FROM ota_booking_status_types_vl BST
3027 WHERE BST.booking_status_type_id = p_booking_status_type_id;
3028
3029 CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3030 SELECT lesson_status,
3031 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3032 FROM ota_performances
3033 WHERE user_id = p_user_id
3034 AND user_type = p_user_type
3035 AND learning_object_id = l_lo_id
3036 AND cert_prd_enrollment_id is null;
3037
3038 CURSOR c_odb_lo_status IS
3039 SELECT CONTENT_PLAYER_STATUS,
3040 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',CONTENT_PLAYER_STATUS)lesson_status_name
3041 FROM ota_delegate_bookings
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
3051 SELECT decode(prf.lesson_status, 'P', '1',
3052 'C', '2',
3053 'F', '3',
3054 'I', '4',
3055 'B', '5',
3056 'N', '6') decode_lesson_status,
3057 prf.lesson_status lesson_status,
3058 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS', prf.lesson_status) lesson_status_name
3059 FROM ota_performances prf
3060 WHERE
3061 prf.user_id = p_user_id
3062 and prf.user_type = p_user_type
3063 and prf.learning_object_id = l_lo_id
3064 order by decode_lesson_status;
3065
3066 cursor csr_contact_id is
3067 select delegate_contact_id
3068 from ota_delegate_bookings
3069 where booking_id = p_booking_id;
3070
3071
3072 l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_status';
3073
3074 l_online_flag ota_category_usages.online_flag%TYPE;
3075 l_learning_object_id ota_offerings.learning_object_id%TYPE;
3076 l_course_end_date ota_events.course_end_date%TYPE;
3077 l_sysdate ota_events.course_end_date%TYPE;
3078
3079 l_status VARCHAR2(30) := null;
3080 l_status_name ota_booking_status_types_tl.name%TYPE := null;
3081
3082 --Bug 4665032
3083 l_enrollment_status_code ota_booking_status_types.type%TYPE;
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;
3093
3094
3095 BEGIN
3096 hr_utility.set_location(' Step:'|| l_proc, 10);
3097 open c_delivery_mode;
3098 fetch c_delivery_mode into l_online_flag, l_learning_object_id, l_course_end_date, l_sysdate; --Bug 5222464
3099 close c_delivery_mode;
3100
3101 --Fetch the enrollment status of the booking
3102 open c_booking_status;
3103 fetch c_booking_status into l_enrollment_status_code, l_enrollment_status_name;
3104 close c_booking_status;
3105
3106 -- Check for LO status for online classes
3107 If ( l_online_flag = 'Y' ) Then
3108
3109 -- Bug 3725560
3110 -- Check if the event is imported
3111 open c_imported_offering;
3112 fetch c_imported_offering into l_imported_offering;
3113 close c_imported_offering;
3114
3115 If (l_imported_offering is not null) Then
3116 -- Imported Offering, Status should be taken from
3117 -- OTA_DELEGATE_BOOKINGS
3118 open c_odb_lo_status;
3119 fetch c_odb_lo_status into l_status, l_status_name;
3120 close c_odb_lo_status;
3121 Else
3122 -- EBS created, Status should be taken from
3123 -- OTA_PERFORMANCES
3124
3125 If ( l_enrollment_status_code = 'A' and p_mode = 2 ) Then
3126 -- p_mode = 2 means that coming from admin side
3127 open csr_best_prf(l_learning_object_id);
3128 fetch csr_best_prf into l_decode_lesson_status, l_status, l_status_name;
3129 close csr_best_prf;
3130 Else
3131 -- Coming from learner side
3132 open c_learning_object_status(l_learning_object_id);
3133 fetch c_learning_object_status into l_status, l_status_name;
3134 close c_learning_object_status;
3135 End If;
3136 End If;
3137
3138 If ( l_status is null ) Then
3139 l_status := 'N';
3140 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
3141 End If;
3142
3143 If ( (l_status = 'N') or (l_status = 'I') ) Then
3144 If ( l_course_end_date < l_sysdate --Class is expired --Bug 3554773 --Bug 5222464
3145 AND p_mode is null ) Then -- Bug 3594377
3146 l_status := 'E';
3147 l_status_name := ota_utility.get_message('OTA','OTA_443001_OFFR_EXPIRED_STATUS');
3148 End If;
3149 End If;
3150
3151 --check if the enrollment is part of active certs
3152 if p_chk_active_cert_flag is not null and p_chk_active_cert_flag = 'Y' then
3153 if p_user_type = 'E' then
3154 l_person_id := p_user_id;
3155 elsif p_user_type = 'C' then
3156 open csr_contact_id;
3157 fetch csr_contact_id into l_contact_id;
3158 close csr_contact_id;
3159 end if;
3160
3161 get_active_cert_prds(p_event_id,
3162 l_person_id,
3163 l_contact_id,
3164 l_cert_prd_enrollment_ids);
3165
3166 if l_cert_prd_enrollment_ids is not null then
3167 --return status mng as 'Available under certification'
3168 l_status := 'D';
3169 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','D');
3170 end if;
3171 end if;
3172 --bug 6683076.Added to show 'Pending Evaluation' as player status for
3173 --learners who have to take mandatory evaluation after
3174 --completing the online class.
3175 --bug 7175372.Added condition so that this player status is shown
3176 --only from learner side.
3177 If (l_status = 'C' or l_status = 'P' or l_status = 'F') Then
3178 If (l_enrollment_status_code = 'E' and (p_mode is null or p_mode <>2)) Then
3179 l_status_name:= hr_general_utilities.get_lookup_meaning('DELEGATE_BOOKING_STATUS','E');
3180 End If;
3181 End If;
3182
3183 Else
3184 If p_mode <> 2 Then -- Bug#4465495 If p_mode is not 2
3185 -- for off-line class return enrollment status
3186 l_status := l_enrollment_status_code;
3187 l_status_name := l_enrollment_status_name;
3188 Else --Bug#4465495 If p_mode is 2 for off-line class return null;
3189 l_status_name := null;
3190 End If;
3191 End If;
3192
3193 RETURN l_status_name;
3194 hr_utility.set_location(' Step:'|| l_proc, 20);
3195 END get_enroll_lo_status;
3196
3197 -- ----------------------------------------------------------------------------
3198 -- |-------------------------< get_history_button >----------------------------|
3199 -- ----------------------------------------------------------------------------
3200 -- {Start Of Comments}
3201 --
3202 -- Description:
3203 -- This function will return a value based on which the Move to History
3204 -- Button will be enabled. it will be enabled for online classes with a
3205 -- performance status of Completed, Passed or Failed.
3206 --
3207 -- Pre Conditions:
3208 -- None.
3209 --
3210 -- Out Arguments:
3211 -- p_user_id
3212 -- p_lo_id
3213 -- p_event_id
3214 -- p_booking_id
3215 --
3216 -- Post Success:
3217 -- Processing continues.
3218 --
3219 --
3220 -- Post Failure:
3221 -- None.
3222 --
3223 -- Access Status:
3224 -- Public.
3225 --
3226 -- {End Of Comments}
3227 ----------------------------------------------------------------------------
3228
3229 FUNCTION get_history_button(p_user_id fnd_user.user_id%TYPE,
3230 p_lo_id ota_learning_objects.learning_object_id%TYPE,
3231 p_event_id ota_events.event_id%TYPE,
3232 p_booking_id ota_delegate_bookings.booking_id%TYPE)
3233 RETURN VARCHAR2 IS
3234
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
3245 FROM ota_events
3246 WHERE event_id = p_event_id;
3247
3248 CURSOR c_history_enabled IS
3249 SELECT lesson_status
3250 FROM ota_performances
3251 WHERE learning_object_id = p_lo_id
3252 AND user_id = p_user_id
3253 AND lesson_status IN ('P', 'C');
3254
3255 CURSOR c_booking_status_type IS
3256 SELECT type
3257 FROM ota_delegate_bookings odb,
3258 ota_booking_status_types bst
3259 WHERE booking_id = p_booking_id
3260 and odb.booking_status_type_id = bst.booking_status_type_id;
3261
3262 CURSOR c_history_enabled_odb IS
3263 SELECT content_player_status
3264 FROM ota_delegate_bookings
3265 WHERE booking_id = p_booking_id
3266 AND content_player_status in ('P', 'C');
3267
3268 BEGIN
3269
3270 hr_utility.set_location(' Step:'|| l_proc, 10);
3271
3272 --Initialize local variables
3273 l_enable_move_to_history := false;
3274 l_return := 'MoveToHistoryDisabled';
3275
3276 --First check the enrollment status
3277 OPEN c_booking_status_type;
3278 FETCH c_booking_status_type INTO l_booking_status_type;
3279 CLOSE c_booking_status_type;
3280
3281 if (l_booking_status_type = 'A' ) Then --If status is A then move to history should be enabled
3282 l_enable_move_to_history := true;
3283 elsif (l_booking_status_type <> 'E') Then -- Bug 6683076
3284 -- Bug 3725560
3285 -- Check if the event is imported
3286 OPEN c_imported_offering;
3287 FETCH c_imported_offering into l_imported_offering;
3288 CLOSE c_imported_offering;
3289
3290 IF (l_imported_offering is not null) Then
3291 -- Imported Offering, Status should be taken from
3292 -- OTA_DELEGATE_BOOKINGS - Bug 3725560
3293 OPEN c_history_enabled_odb;
3294 FETCH c_history_enabled_odb INTO l_type;
3295 CLOSE c_history_enabled_odb;
3296 ELSE
3297 -- EBS created, Status should be taken from
3298 -- OTA_PERFORMANCES
3299 OPEN c_history_enabled;
3300 FETCH c_history_enabled INTO l_type;
3301 CLOSE c_history_enabled;
3302 END IF;
3303 if (l_type is not null) then
3304 l_enable_move_to_history := true;
3305 end if;
3306
3307 end if;
3308 IF (l_enable_move_to_history) THEN
3309 l_return := 'MoveToHistoryImage';
3310 END IF;
3311
3312 RETURN l_return;
3313
3314 EXCEPTION
3315 WHEN others then
3316
3317 RETURN l_return;
3318
3319 END get_history_button;
3320
3321 FUNCTION get_nls_language
3322 RETURN varchar2
3323 IS
3324 CURSOR csr_get_nls_lang IS
3325 SELECT NLS_LANGUAGE
3326 FROM fnd_languages
3327 WHERE language_code = userenv('LANG');
3328 l_nls_language fnd_languages.NLS_LANGUAGE%TYPE;
3329 BEGIN
3330 OPEN csr_get_nls_lang;
3331 FETCH csr_get_nls_lang INTO l_nls_language;
3332 CLOSE csr_get_nls_lang;
3333
3334 RETURN l_nls_language;
3335 END get_nls_language;
3336
3337 -- Author: sbhullar
3338 -- ----------------------------------------------------------------
3339 -- ------------------<get_lo_completion_date >--------------------
3340 -- ----------------------------------------------------------------
3341 -- {Start of Comments}
3342 --
3343 -- Description:
3344 -- This function will be used to show get lo completion date for
3345 -- online asynchronous offering
3346 -- IN
3347 -- p_event_id
3348 -- p_user_id
3349 -- p_user_type
3350 --
3351 -- Post Failure:
3352 -- None.
3353 -- Access 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')
3363 RETURN varchar2
3364 IS
3365 l_proc VARCHAR2(72) := g_package||'get_lo_completion_date';
3366 l_event_id ota_events.event_id%type;
3367 l_lo_id ota_learning_objects.learning_object_id%type;
3368 -- l_lo_completed_date VARCHAR2(100);
3369 l_lo_completed_date DATE;
3370 l_lo_completed_time VARCHAR2(10);
3371 l_lo_completed_date_tz VARCHAR2(100);
3372 l_lo_lesson_status ota_performances.lesson_status%type;
3373 l_sync_flag ota_category_usages.synchronous_flag%type;
3374 l_online_flag ota_category_usages.online_flag%type;
3375 l_return_completion_date varchar(50) := '';
3376
3377 CURSOR c_get_lo_completion_date is
3378 Select
3379 oev.event_id,
3380 ofr.learning_object_id,
3381 -- to_char(opf.completed_date),
3382 opf.completed_date,
3383 to_char(opf.completed_date, 'HH24:MI:SS'),
3384 opf.lesson_status,
3385 -- Bug#4582683
3386 -- (Select Synchronous_Flag from ota_category_usages where Category_Usage_Id = ofr.Delivery_Mode_Id) Sync_Flag,
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
3396 And opf.User_id(+) = p_user_id
3397 And opf.User_type(+) = p_user_type
3398 And nvl(opf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1)
3399 And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id;
3400
3401 Begin
3402 hr_utility.set_location('Entering :'||l_proc,5);
3403 /* if p_event_id = 3051 THEN
3404 hr_utility.trace_on(NULL, 'ORACLE');
3405 END IF;
3406 hr_utility.trace(' inside lo completion method');
3407 hr_utility.trace('nls lan param ' || get_nls_language);
3408
3409 hr_utility.trace('nls date format ' || hr_util_misc_web.get_nls_parameter('NLS_DATE_FORMAT') );
3410 hr_utility.trace('nls language2 ' || hr_util_misc_web.get_nls_parameter('NLS_LANGUAGE') );
3411 hr_utility.trace('nls date language ' || hr_util_misc_web.get_nls_parameter('NLS_DATE_LANGUAGE') );
3412 hr_utility.trace('ICX Language ' || fnd_profile.value('ICX_LANGUAGE'));
3413 hr_utility.trace('ICX Date format ' || fnd_profile.value('ICX_DATE_FORMAT_MASK'));
3414 */
3415
3416
3417 open c_get_lo_completion_date;
3418 fetch c_get_lo_completion_date into l_event_id, l_lo_id, l_lo_completed_date, l_lo_completed_time, l_lo_lesson_status, l_sync_flag, l_online_flag, l_lo_completed_date_tz;
3419 close c_get_lo_completion_date;
3420
3421 If ( l_online_flag = 'Y' and l_sync_flag = 'N'
3422 and l_lo_completed_date is not null and ( l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P') ) Then
3423 If ( p_module_name = 'LEARNER' or p_module_name = 'INSTRUCTOR' ) Then
3424 l_return_completion_date := l_lo_completed_date_tz;
3425 Else
3426
3427 l_return_completion_date :=
3428 to_char(trunc(l_lo_completed_date)
3429 ,hr_util_misc_web.get_nls_parameter('NLS_DATE_FORMAT')
3430 , 'nls_date_language = ' || get_nls_language())
3431 || ' ' || l_lo_completed_time;
3432 End If;
3433 End If;
3434
3435
3436 RETURN l_return_completion_date;
3437 EXCEPTION
3438 WHEN others THEN
3439 hr_utility.set_location('Leaving :'||l_proc,15);
3440 RETURN l_return_completion_date;
3441 End get_lo_completion_date;
3442
3443 -- ----------------------------------------------------------------------------
3444 -- |--------------------< GET_LO_COMPLETION_DATE_TIME>-------------------------|
3445 -- ----------------------------------------------------------------------------
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')
3455 RETURN date
3456 IS
3457 l_proc VARCHAR2(72) := g_package||'get_lo_completion_date_time';
3458 l_event_id ota_events.event_id%type;
3459 l_lo_id ota_learning_objects.learning_object_id%type;
3460 l_lo_completed_date DATE;
3461 l_lo_comp_date DATE;
3462 l_lo_completed_time VARCHAR2(10);
3463 l_lo_completed_date_tz VARCHAR2(100);
3464 l_lo_lesson_status ota_performances.lesson_status%type;
3465 l_sync_flag ota_category_usages.synchronous_flag%type;
3466 l_online_flag ota_category_usages.online_flag%type;
3467 l_return_completion_date date:= null;
3468
3469 CURSOR c_get_lo_completion_date is
3470 Select
3471 oev.event_id,
3472 ofr.learning_object_id,
3473 opf.completed_date,
3474 to_char(opf.completed_date, 'HH24:MI:SS'),
3475 opf.lesson_status,
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
3485 And opf.User_id(+) = p_user_id
3486 And opf.User_type(+) = p_user_type
3487 And nvl(opf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1)
3488 And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id;
3489
3490 Begin
3491 hr_utility.set_location('Entering :'||l_proc,5);
3492
3493
3494 open c_get_lo_completion_date;
3495 fetch c_get_lo_completion_date into l_event_id, l_lo_id, l_lo_completed_date, l_lo_completed_time, l_lo_lesson_status, l_sync_flag, l_online_flag, l_lo_completed_date_tz,l_lo_comp_date;
3496 close c_get_lo_completion_date;
3497
3498 If ( l_online_flag = 'Y' and l_sync_flag = 'N'
3499 and l_lo_completed_date is not null and ( l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P') ) Then
3500
3501 IF (p_module_name = 'ADMIN')then
3502 l_return_completion_date := l_lo_comp_date;
3503 END IF;
3504
3505 End If;
3506
3507
3508 RETURN l_return_completion_date;
3509 EXCEPTION
3510 WHEN others THEN
3511 hr_utility.set_location('Leaving :'||l_proc,15);
3512 RETURN l_return_completion_date;
3513 End get_lo_completion_date_time;
3514
3515 -- ----------------------------------------------------------------------------
3516 -- |-------------------------< get_cert_lo_status >--------------------------|
3517 -- ----------------------------------------------------------------------------
3518 -- {Start Of Comments}
3519 --
3520 -- Description:
3521 -- This function will fetch the player status for online classes and enrollment
3522 -- status for offline classes within the certification details.
3523 --
3524 -- Pre Conditions:
3525 -- None.
3526 --
3527 -- Out Arguments:
3528 -- p_user_id
3529 -- p_user_type
3530 -- p_event_id
3531 -- p_booking_status_type_id
3532 -- p_booking_id
3533 -- p_cert_prd_enrollment_id
3534 -- p_mode
3535 --
3536 -- Post Success:
3537 -- Processing continues.
3538 --
3539 --
3540 -- Post Failure:
3541 -- None.
3542 --
3543 -- Access Status:
3544 -- Public.
3545 --
3546 -- {End Of Comments}
3547 ----------------------------------------------------------------------------
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)
3557 RETURN VARCHAR2 IS
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
3567 CURSOR c_booking_status IS
3568 SELECT BST.type, BST.name
3569 FROM ota_booking_status_types_vl BST
3570 WHERE BST.booking_status_type_id = p_booking_status_type_id;
3571
3572 CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3573 SELECT lesson_status,
3574 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3575 FROM ota_performances
3576 WHERE user_id = p_user_id
3577 AND user_type = p_user_type
3578 AND learning_object_id = l_lo_id
3579 AND cert_prd_enrollment_id = p_cert_prd_enrollment_id;
3580
3581 l_proc VARCHAR2(72) := g_package|| 'get_cert_lo_status';
3582
3583 l_online_flag ota_category_usages.online_flag%TYPE;
3584 l_learning_object_id ota_offerings.learning_object_id%TYPE;
3585 l_course_end_date ota_events.course_end_date%TYPE;
3586
3587 l_status VARCHAR2(30) := null;
3588 l_status_name ota_booking_status_types_tl.name%TYPE := null;
3589
3590 BEGIN
3591 hr_utility.set_location(' Step:'|| l_proc, 10);
3592 open c_delivery_mode;
3593 fetch c_delivery_mode into l_online_flag, l_learning_object_id, l_course_end_date;
3594 close c_delivery_mode;
3595
3596 -- Check for LO status for online classes
3597 If ( l_online_flag = 'Y' ) Then
3598
3599 -- OTA_PERFORMANCES for CERT_PRD_ENROLLMENT_ID
3600 open c_prd_lo_status(l_learning_object_id);
3601 fetch c_prd_lo_status into l_status, l_status_name;
3602 close c_prd_lo_status;
3603
3604 If ( l_status is null ) Then
3605 l_status := 'N';
3606 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
3607 End If;
3608
3609 If ( (l_status = 'N') or (l_status = 'I') ) Then
3610 If ( l_course_end_date < trunc(sysdate) --Class is expired --Bug 3554773
3611 AND p_mode is null ) Then -- Bug 3594377
3612 l_status := 'E';
3613 l_status_name := ota_utility.get_message('OTA','OTA_443001_OFFR_EXPIRED_STATUS');
3614 End If;
3615 End If;
3616 Else
3617 open c_booking_status;
3618 fetch c_booking_status into l_status, l_status_name;
3619 close c_booking_status;
3620 End If;
3621
3622 RETURN l_status_name;
3623 hr_utility.set_location(' Step:'|| l_proc, 20);
3624
3625 EXCEPTION
3626 WHEN others THEN
3627 hr_utility.set_location('Leaving :'||l_proc,30);
3628 RETURN NULL;
3629
3630 END get_cert_lo_status;
3631
3632 -- ----------------------------------------------------------------
3633 -- ------------------<get_cert_lo_title_for_tree >--------------------
3634 -- ----------------------------------------------------------------
3635 -- {Start of Comments}
3636 --
3637 -- Description:
3638 -- This function will be used within the certifications to show get
3639 -- lo name, status and time in the format
3640 -- lo_name [Status: status, Time: hh:mm:ss]
3641 -- if p_mode is 1 else it gives the lo status icon
3642 -- IN
3643 -- p_lo_id
3644 -- p_user_id
3645 -- p_user_type
3646 -- p_cert_prd_enrollment_id
3647 -- p_mode
3648 --
3649 -- Post Failure:
3650 -- None.
3651 -- Access Status
3652 -- Public
3653 -- {End of Comments}
3654 ------------------------------------------------------------------
3655
3656 FUNCTION get_cert_lo_title_for_tree(p_lo_id IN NUMBER,
3657 p_user_id IN NUMBER,
3658 p_user_type IN ota_attempts.user_type%type,
3659 p_cert_prd_enrollment_id IN ota_performances.cert_prd_enrollment_id%type,
3660 p_mode IN NUMBER default 1)
3661 RETURN varchar2
3662 IS
3663 l_proc VARCHAR2(72) := g_package||'get_cert_lo_title_for_tree';
3664 l_lo_name varchar(240);
3665 l_lo_status varchar(80);
3666 l_lo_time number;
3667 l_lo_score number;
3668 l_lo_status_icon varchar(30);
3669 l_Seconds number;
3670 l_Minutes number;
3671 l_Hours number;
3672 l_formatted_hour varchar(20) := '';
3673 l_formatted_min varchar(20) := '';
3674 l_formatted_sec varchar(20) := '';
3675 l_formatted_time varchar(20) := '';
3676 l_return_tree_title varchar(500) := '';
3677 l_max_performance_id ota_performances.performance_id%type;
3678 l_tst_grade_flag ota_tests.grade_flag%TYPE;
3679 l_var_score VARCHAR2(100);
3680 l_lo_completed_date VARCHAR2(100);
3681 l_lo_completed_time VARCHAR2(10);
3682 l_lo_completed_date_tz VARCHAR2(100);
3683 l_lo_lesson_status ota_performances.lesson_status%type;
3684
3685 CURSOR c_get_cert_lo_tree_link(p_performance_id in number) is
3686 Select
3687 olo.name Name,
3688 nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
3689 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
3690 nvl(opf.time,0) Time,
3691 opf.score Score,
3692 decode(opf.lesson_status
3693 ,'C','player_status_c.gif'
3694 ,'F','player_status_f.gif'
3695 ,'I','player_status_i.gif'
3696 ,'P','player_status_p.gif'
3697 ,'N','player_status_n.gif'
3698 ,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type,'', p_cert_prd_enrollment_id),
3699 'N','player_status_no_prereq.gif',
3700 'Y','player_status_n.gif')
3701 ,'player_status_n.gif') STATUS_ICON
3702 , tst.grade_flag
3703 , to_char(opf.completed_date)
3704 , to_char(opf.completed_date, 'HH24:MI:SS')
3705 , opf.lesson_status
3706 , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3707 From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
3708 OTA_TESTS tst
3709 Where
3710 olo.learning_object_id = opf.learning_object_id(+)
3711 AND tst.test_id(+) = olo.test_id
3712 And olo.Learning_object_id = p_lo_id
3713 And opf.User_id(+) = p_user_id
3714 And opf.User_type(+) = p_user_type
3715 AND opf.performance_id(+) = p_performance_id
3716 And opf.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
3717
3718 CURSOR c_max_performance_id is
3719 Select max(per.performance_id)
3720 From ota_performances per
3721 Where per.learning_object_id(+) = p_lo_id
3722 And per.user_id(+) = p_user_id
3723 And per.user_type(+) = p_user_type
3724 And per.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
3725
3726 Begin
3727 hr_utility.set_location('Entering :'||l_proc,5);
3728
3729 open c_max_performance_id;
3730 fetch c_max_performance_id into l_max_performance_id;
3731 close c_max_performance_id;
3732
3733 if ( l_max_performance_id is null ) then
3734 l_max_performance_id := -1;
3735 end if;
3736
3737 open c_get_cert_lo_tree_link(l_max_performance_id);
3738 fetch c_get_cert_lo_tree_link into l_lo_name,l_lo_status,l_lo_time,l_lo_score,l_lo_status_icon , l_tst_grade_flag, l_lo_completed_date, l_lo_completed_time, l_lo_lesson_status, l_lo_completed_date_tz;
3739 close c_get_cert_lo_tree_link;
3740
3741 If ( p_mode = 1) Then
3742 l_lo_time := round(l_lo_time);
3743
3744 l_Seconds := l_lo_time mod 60;
3745 l_Minutes := floor(l_lo_time / 60);
3746 l_Hours := floor(l_Minutes/60);
3747 l_Minutes := l_Minutes - l_Hours * 60;
3748
3749 If (l_Hours < 10) Then
3750 l_formatted_hour := '0' || l_Hours;
3751 Else
3752 l_formatted_hour := l_Hours;
3753 End If;
3754
3755 If (l_Minutes < 10) Then
3756 l_formatted_min := '0' || l_Minutes;
3757 Else
3758 l_formatted_min := l_Minutes;
3759 End If;
3760
3761 If (l_Seconds < 10) Then
3762 l_formatted_sec := '0' || l_Seconds;
3763 Else
3764 l_formatted_sec := l_Seconds;
3765 End If;
3766
3767 fnd_message.set_name('OTA', 'OTA_443358_SRCH_LO_TIME');
3768 fnd_message.set_token ('HOUR', l_formatted_hour);
3769 fnd_message.set_token ('MIN', l_formatted_min);
3770 fnd_message.set_token ('SEC', l_formatted_sec);
3771 l_formatted_time := fnd_message.get();
3772
3773 If ((l_lo_score is null) or (l_lo_score < 0)) Then
3774 fnd_message.set_name('OTA', 'OTA_443453_SRCH_TREE_TITLE2');
3775 Else
3776 --Added for bug 3550407
3777 IF ( l_tst_grade_flag = 'P' ) THEN
3778 l_var_score := l_lo_score||'%';
3779 ELSE
3780 l_var_score := l_lo_score;
3781 END IF;
3782
3783 fnd_message.set_name('OTA', 'OTA_443357_SRCH_TREE_TITLE');
3784 fnd_message.set_token ('SCORE', l_var_score);
3785 End If;
3786
3787 If ( (l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P')
3788 and l_lo_completed_date is not null ) Then
3789 l_lo_status := l_lo_status || ' ' || l_lo_completed_date_tz;
3790 End If;
3791
3792 fnd_message.set_token ('LO_NAME', l_lo_name);
3793 fnd_message.set_token ('STATUS', l_lo_status);
3794 fnd_message.set_token ('TIME', l_formatted_time);
3795
3796 l_return_tree_title := fnd_message.get();
3797 Else
3798 l_return_tree_title := l_lo_status_icon;
3799 End If;
3800
3801 hr_utility.set_location('Leaving :'||l_proc,10);
3802 RETURN l_return_tree_title;
3803 EXCEPTION
3804 WHEN others THEN
3805 hr_utility.set_location('Leaving :'||l_proc,15);
3806
3807 RETURN l_return_tree_title;
3808 End get_cert_lo_title_for_tree;
3809
3810 function get_cme_online_event_id(p_user_id in fnd_user.user_id%type,
3811 p_user_type in ota_attempts.user_type%type,
3812 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
3813 return varchar2 is
3814 CURSOR csr_cme_info IS
3815 SELECT cme.cert_mbr_enrollment_id,
3816 cpe.cert_prd_enrollment_id,
3817 cme.object_version_number,
3818 cmb.object_id,
3819 cmb.certification_member_id,
3820 cme.member_status_code
3821 FROM ota_certification_members cmb,
3822 ota_cert_mbr_enrollments cme,
3823 ota_cert_prd_enrollments cpe
3824 WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
3825 AND cme.cert_member_id = cmb.certification_member_id
3826 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
3827
3828 CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
3829 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
3830 FROM ota_cert_enrollments cre,
3831 ota_cert_prd_enrollments cpe
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
3841
3842 SELECT bst.type status,
3843 tdb.DATE_STATUS_CHANGED,
3844 evt.event_id,
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
3854 WHERE evt.event_id = tdb.event_id
3855 AND bst.booking_status_type_id = tdb.booking_status_type_id
3856 /* AND (
3857 evt.course_start_date >= csr_cert_period_start_date
3858 AND
3859 (
3860 evt.course_end_date IS NOT NULL
3861 AND evt.course_end_date <= csr_cert_period_end_date
3862 )
3863 OR
3864 (
3865 evt.event_type = 'SELFPACED'
3866 AND csr_cert_period_end_date >= evt.course_start_date
3867 )
3868 )
3869 */
3870 ---
3871 AND ( ( evt.course_start_date >= csr_cert_period_start_date and
3872 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= csr_cert_period_end_date )
3873 /* Bug 4515924
3874 or (evt.event_type ='SELFPACED' and
3875 evt.course_start_date< csr_cert_period_end_date AND
3876 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= csr_cert_period_end_date ))*/
3877 or (evt.event_type = 'SELFPACED' AND
3878 ((csr_cert_period_end_date >= evt.course_start_date) AND
3879 ((evt.course_end_date is null) or
3880 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))
3881 )))
3882 ---
3883 AND evt.activity_version_id = csr_activity_version_id
3884 --AND tdb.delegate_person_id = p_person_id
3885 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
3886 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
3887 )
3888 AND evt.parent_offering_id = ofr.offering_id
3889 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
3890 AND tdb.booking_status_type_id = bst.booking_status_type_id
3891 AND bst.type <> 'C';
3892
3893 l_proc VARCHAR2(72) := g_package||'get_cme_online_event_id';
3894 rec_cme_info csr_cme_info%rowtype;
3895
3896 l_person_id number;
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);
3906
3907 --check for mult online evts and throw null if so
3908 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
3909
3910 if (l_online_evt_count > 1) then
3911 return null;
3912 end if;
3913
3914 open csr_cme_info;
3915 fetch csr_cme_info into rec_cme_info;
3916 close csr_cme_info;
3917
3918 OPEN csr_cert_enrl(rec_cme_info.cert_prd_enrollment_id);
3919 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
3920 CLOSE csr_cert_enrl;
3921
3922 hr_utility.set_location(' Step:'||l_proc,10);
3923
3924 FOR rec IN get_enrl_status_on_update(rec_cme_info.object_id,
3925 l_cert_period_start_date,
3926 l_cert_period_end_date,
3927 l_person_id,
3928 l_contact_id)
3929 LOOP
3930
3931 if rec.online_flag = 'Y' then
3932 l_online_event_id := rec.event_id;
3933 exit;
3934 end if;
3935
3936
3937 END LOOP;
3938
3939 hr_utility.set_location('Leaving :'||l_proc,10);
3940
3941 return l_online_event_id;
3942
3943 EXCEPTION
3944 WHEN others THEN
3945 hr_utility.set_location('Leaving :'||l_proc,15);
3946
3947 RETURN null;
3948
3949 End get_cme_online_event_id;
3950
3951 function get_cme_play_button(p_user_id in fnd_user.user_id%type,
3952 p_user_type in ota_attempts.user_type%type,
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,
3962 ocu.online_flag,
3963 evt.course_start_date,
3964 evt.course_end_date
3965 FROM ota_events evt,
3966 ota_offerings ofr,
3967 ota_category_usages ocu
3968 WHERE evt.event_id = csr_event_id
3969 AND evt.parent_offering_id = ofr.offering_id
3970 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
3971
3972 CURSOR csr_cme is
3973 select cert_prd_enrollment_id
3974 from ota_cert_mbr_enrollments
3975 where cert_mbr_enrollment_id = p_cert_mbr_enrollment_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
3985 BEGIN
3986 hr_utility.set_location('Entering :'||l_proc,5);
3987
3988 --check for mult online evts and throw null if so
3989 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
3990
3991 if (l_online_evt_count > 1) then
3992 return 'DISABLE_MULT_ENRL_EVENTS';
3993 end if;
3994
3995 open csr_cme;
3996 fetch csr_cme into rec_cme;
3997 close csr_cme;
3998
3999 l_online_event_id := get_cme_online_event_id(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4000
4001 open csr_event(l_online_event_id);
4002 fetch csr_event into rec;
4003 close csr_event;
4004
4005 if (l_online_event_id is not null) then
4006 l_return_play_btn := OTA_LO_UTILITY.GET_PLAY_BUTTON(p_user_id,p_user_type, p_is_manager, rec.EVENT_ID, rec.EVENT_TYPE
4007 ,rec.SYNCHRONOUS_FLAG, rec.ONLINE_FLAG
4008 ,rec.COURSE_START_DATE ,rec.COURSE_END_DATE, null,rec_cme.cert_prd_enrollment_id);
4009 end if;
4010 hr_utility.set_location('Leaving :'||l_proc,10);
4011
4012 return l_return_play_btn;
4013
4014 EXCEPTION
4015 WHEN others THEN
4016 hr_utility.set_location('Leaving :'||l_proc,15);
4017
4018 RETURN null;
4019
4020 End get_cme_play_button;
4021
4022
4023 function get_cme_player_toolbar_flag(p_user_id in fnd_user.user_id%type,
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
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);
4044
4045 --check for mult online evts and throw null if so
4046 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4047
4048 if (l_online_evt_count > 1) then
4049 return null;
4050 end if;
4051
4052 l_online_event_id := get_cme_online_event_id(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4053
4054 open csr_toolbar_flag(l_online_event_id);
4055 fetch csr_toolbar_flag into l_player_toolbar_flag;
4056 close csr_toolbar_flag;
4057
4058 hr_utility.set_location('Leaving :'||l_proc,10);
4059
4060 return l_player_toolbar_flag;
4061
4062 EXCEPTION
4063 WHEN others THEN
4064 hr_utility.set_location('Leaving :'||l_proc,15);
4065
4066 RETURN null;
4067
4068 End get_cme_player_toolbar_flag;
4069
4070 function get_cert_lo_status(p_user_id in fnd_user.user_id%type,
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
4089 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4090
4091 CURSOR csr_cme is
4092 select cert_prd_enrollment_id
4093 from ota_cert_mbr_enrollments
4094 where cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4095
4096
4097 CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE,
4098 csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4099 SELECT lesson_status,
4100 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4101 FROM ota_performances
4102 WHERE user_id = p_user_id
4103 AND user_type = p_user_type
4104 AND learning_object_id = l_lo_id
4105 AND cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
4106
4107 cursor csr_get_act is
4108 select cmb.object_id
4109 from ota_certification_members cmb,
4110 ota_cert_mbr_enrollments cme
4111 where cmb.certification_member_id = cme.cert_member_id
4112 and cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
4113 and cmb.object_type = 'H';
4114
4115 CURSOR csr_prf_ord(p_activity_version_id in ota_activity_versions.activity_version_id%type,
4116 p_user_id in number,
4117 p_user_type in varchar2,
4118 csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4119 SELECT prf.user_id,
4120 lo.learning_object_id,
4121 decode(prf.lesson_status, 'P', '1',
4122 'C', '2',
4123 'F', '3',
4124 'I', '4',
4125 'B', '5',
4126 'N', '6') decode_lesson_status,
4127 prf.lesson_status lesson_status,
4128 prf.performance_id
4129 FROM ota_performances prf,
4130 ota_offerings ofr,
4131 ota_learning_objects lo
4132 WHERE
4133 prf.user_id = p_user_id
4134 and prf.user_type = p_user_type
4135 and lo.learning_object_id = prf.learning_object_id
4136 AND prf.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4137 and ofr.learning_object_id = lo.learning_object_id
4138 and ofr.activity_version_id = p_activity_version_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;
4148 l_status_name ota_booking_status_types_tl.name%TYPE := null;
4149
4150 l_return_lo_status VARCHAR2(30);
4151 l_online_evt_count number;
4152
4153 rec_prf_ord csr_prf_ord%rowtype;
4154 rec_get_act csr_get_act%rowtype;
4155
4156 BEGIN
4157 hr_utility.set_location('Entering :'||l_proc,5);
4158
4159 open csr_cme;
4160 fetch csr_cme into l_cert_prd_enrollment_id;
4161 close csr_cme;
4162
4163 --check for mult online evts and throw null if so
4164 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4165
4166 if (l_online_evt_count > 1) then
4167 --Bug 4560354
4168 --return as Not Attempted
4169 --l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4170 --return l_return_lo_status;
4171 open csr_get_act;
4172 fetch csr_get_act into rec_get_act;
4173 close csr_get_act;
4174
4175 if rec_get_act.object_id is not null then
4176 open csr_prf_ord(rec_get_act.object_id, p_user_id, p_user_type, l_cert_prd_enrollment_id);
4177 fetch csr_prf_ord into rec_prf_ord;
4178 close csr_prf_ord;
4179
4180 if rec_prf_ord.lesson_status is not null then
4181 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',rec_prf_ord.lesson_status);
4182 else
4183 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4184 end if;
4185 end if;
4186
4187 return l_return_lo_status;
4188 end if;
4189
4190 l_online_event_id := get_cme_online_event_id(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4191
4192 open csr_event(l_online_event_id);
4193 fetch csr_event into rec;
4194 close csr_event;
4195
4196 open c_prd_lo_status(rec.learning_object_id, l_cert_prd_enrollment_id);
4197 fetch c_prd_lo_status into l_status, l_status_name;
4198 close c_prd_lo_status;
4199
4200 If ( l_status is null ) Then
4201 l_status := 'N';
4202 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4203 End If;
4204
4205 If ( (l_status = 'N') or (l_status = 'I') ) Then
4206 If ( rec.course_end_date < trunc(sysdate) --Class is expired --Bug 3554773
4207 -- AND p_mode is null ) Then -- Bug 3594377
4208 ) then
4209 l_status := 'E';
4210 l_status_name := ota_utility.get_message('OTA','OTA_443001_OFFR_EXPIRED_STATUS');
4211 End If;
4212 End If;
4213
4214 hr_utility.set_location('Leaving :'||l_proc,10);
4215
4216 return l_status_name;
4217
4218 EXCEPTION
4219 WHEN others THEN
4220 hr_utility.set_location('Leaving :'||l_proc,15);
4221
4222 RETURN null;
4223
4224 End get_cert_lo_status;
4225
4226 function get_cme_onl_evt_count(p_user_id in fnd_user.user_id%type,
4227 p_user_type in ota_attempts.user_type%type,
4228 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4229 return varchar2 is
4230 CURSOR csr_cme_info IS
4231 SELECT cme.cert_mbr_enrollment_id,
4232 cpe.cert_prd_enrollment_id,
4233 cme.object_version_number,
4234 cmb.object_id,
4235 cmb.certification_member_id,
4236 cme.member_status_code
4237 FROM ota_certification_members cmb,
4238 ota_cert_mbr_enrollments cme,
4239 ota_cert_prd_enrollments cpe
4240 WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
4241 AND cme.cert_member_id = cmb.certification_member_id
4242 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4243
4244 CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4245 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
4246 FROM ota_cert_enrollments cre,
4247 ota_cert_prd_enrollments cpe
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
4257
4258 SELECT bst.type status,
4259 tdb.DATE_STATUS_CHANGED,
4260 evt.event_id,
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
4270 WHERE evt.event_id = tdb.event_id
4271 AND bst.booking_status_type_id = tdb.booking_status_type_id
4272 /* AND (
4273 evt.course_start_date >= csr_cert_period_start_date
4274 AND
4275 (
4276 evt.course_end_date IS NOT NULL
4277 AND evt.course_end_date <= csr_cert_period_end_date
4278 )
4279 OR
4280 (
4281 evt.event_type = 'SELFPACED'
4282 AND csr_cert_period_end_date >= evt.course_start_date
4283 )
4284 )
4285 */
4286 ---
4287 AND ( ( evt.course_start_date >= csr_cert_period_start_date and
4288 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= csr_cert_period_end_date )
4289 /* Bug 4515924 or (evt.event_type ='SELFPACED' and
4290 evt.course_start_date< csr_cert_period_end_date AND
4291 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= csr_cert_period_end_date ))*/
4292 or (evt.event_type = 'SELFPACED' AND
4293 ((csr_cert_period_end_date >= evt.course_start_date) AND
4294 ((evt.course_end_date is null) or
4295 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))
4296 )))
4297 ---
4298 AND evt.activity_version_id = csr_activity_version_id
4299 --AND tdb.delegate_person_id = p_person_id
4300 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
4301 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
4302 )
4303 AND evt.parent_offering_id = ofr.offering_id
4304 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
4305 AND tdb.booking_status_type_id = bst.booking_status_type_id
4306 AND bst.type <> 'C';
4307
4308 l_proc VARCHAR2(72) := g_package||'get_cme_onl_evt_count';
4309 rec_cme_info csr_cme_info%rowtype;
4310
4311 l_person_id number;
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);
4321
4322
4323 open csr_cme_info;
4324 fetch csr_cme_info into rec_cme_info;
4325 close csr_cme_info;
4326
4327 OPEN csr_cert_enrl(rec_cme_info.cert_prd_enrollment_id);
4328 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
4329 CLOSE csr_cert_enrl;
4330
4331 hr_utility.set_location(' Step:'||l_proc,10);
4332
4333 FOR rec IN get_enrl_status_on_update(rec_cme_info.object_id,
4334 l_cert_period_start_date,
4335 l_cert_period_end_date,
4336 l_person_id,
4337 l_contact_id)
4338 LOOP
4339
4340 if rec.online_flag = 'Y' then
4341 l_online_event_id := rec.event_id;
4342 l_online_event_count := l_online_event_count + 1;
4343 --exit;
4344 end if;
4345
4346
4347 END LOOP;
4348
4349 hr_utility.set_location('Leaving :'||l_proc,10);
4350
4351 return '' || l_online_event_count;
4352
4353 EXCEPTION
4354 WHEN others THEN
4355 hr_utility.set_location('Leaving :'||l_proc,15);
4356
4357 RETURN null;
4358
4359 End get_cme_onl_evt_count;
4360
4361 -- ----------------------------------------------------------------
4362 -- -----------------------< format_lo_time >-----------------------
4363 -- ----------------------------------------------------------------
4364 -- {Start of Comments}
4365 --
4366 -- Description:
4367 -- This function formats time in HH:MM:SS format
4368 --
4369 -- IN
4370 -- pTime
4371 --
4372 -- Post Failure:
4373 -- None.
4374 -- Access Status
4375 -- Public
4376 -- {End of Comments}
4377 ------------------------------------------------------------------
4378 Function format_lo_time(pTime ota_performances.time%type)
4379 return varchar2 IS
4380
4381 l_proc VARCHAR2(72) := g_package||'format_lo_time';
4382 l_lo_time number;
4383 l_Seconds number;
4384 l_Minutes number;
4385 l_Hours number;
4386 l_formatted_hour varchar(20) := '';
4387 l_formatted_min varchar(20) := '';
4388 l_formatted_sec varchar(20) := '';
4389 l_formatted_time varchar(20) := '';
4390
4391 Begin
4392 If (pTime is null) Then
4393 l_lo_time := 0;
4394 Else
4395 l_lo_time := pTime;
4396 End If;
4397
4398 l_lo_time := round(pTime);
4399
4400 l_Seconds := l_lo_time mod 60;
4401 l_Minutes := floor(l_lo_time / 60);
4402 l_Hours := floor(l_Minutes/60);
4403 l_Minutes := l_Minutes - l_Hours * 60;
4404
4405 If (l_Hours < 10) Then
4406 l_formatted_hour := '0' || l_Hours;
4407 Else
4408 l_formatted_hour := l_Hours;
4409 End If;
4410
4411 If (l_Minutes < 10) Then
4412 l_formatted_min := '0' || l_Minutes;
4413 Else
4414 l_formatted_min := l_Minutes;
4415 End If;
4416
4417 If (l_Seconds < 10) Then
4418 l_formatted_sec := '0' || l_Seconds;
4419 Else
4420 l_formatted_sec := l_Seconds;
4421 End If;
4422
4423 fnd_message.set_name('OTA', 'OTA_443358_SRCH_LO_TIME');
4424 fnd_message.set_token ('HOUR', l_formatted_hour);
4425 fnd_message.set_token ('MIN', l_formatted_min);
4426 fnd_message.set_token ('SEC', l_formatted_sec);
4427 l_formatted_time := fnd_message.get();
4428 return l_formatted_time;
4429 End format_lo_time;
4430
4431
4432 function get_lme_online_event_id(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
4433 return varchar2 is
4434 CURSOR csr_lme_info IS
4435 SELECT lme.lp_member_enrollment_id,
4436 lmb.ACTIVITY_VERSION_ID,
4437 lpe.lp_enrollment_id,
4438 lme.object_version_number,
4439 lmb.learning_path_member_id,
4440 lme.member_status_code,
4441 lpe.person_id,
4442 lpe.contact_id
4443 FROM ota_learning_path_members lmb,
4444 ota_lp_member_enrollments lme,
4445 ota_lp_enrollments lpe
4446 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
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,
4456 evt.event_id,
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
4466 WHERE evt.event_id = tdb.event_id
4467 AND bst.booking_status_type_id = tdb.booking_status_type_id
4468 AND evt.activity_version_id = csr_activity_version_id
4469 --AND tdb.delegate_person_id = p_person_id
4470 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
4471 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
4472 )
4473 AND evt.parent_offering_id = ofr.offering_id
4474 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
4475 AND tdb.booking_status_type_id = bst.booking_status_type_id
4476 AND bst.type <> 'C';
4477
4478 l_proc VARCHAR2(72) := g_package||'get_lme_online_event_id';
4479 rec_lme_info csr_lme_info%rowtype;
4480
4481 l_person_id number;
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);
4491
4492 --check for mult online evts and throw null if so
4493 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
4494
4495 if (l_online_evt_count > 1) then
4496 return null;
4497 end if;
4498
4499 open csr_lme_info;
4500 fetch csr_lme_info into rec_lme_info;
4501 close csr_lme_info;
4502
4503 hr_utility.set_location(' Step:'||l_proc,10);
4504
4505 FOR rec IN get_enrl_status(rec_lme_info.activity_version_id,
4506 rec_lme_info.person_id,
4507 rec_lme_info.contact_id)
4508 LOOP
4509
4510 if rec.online_flag = 'Y' then
4511 l_online_event_id := rec.event_id;
4512 exit;
4513 end if;
4514
4515
4516 END LOOP;
4517
4518 hr_utility.set_location('Leaving :'||l_proc,10);
4519
4520 return l_online_event_id;
4521
4522 EXCEPTION
4523 WHEN others THEN
4524 hr_utility.set_location('Leaving :'||l_proc,15);
4525
4526 RETURN null;
4527
4528 End get_lme_online_event_id;
4529
4530 function get_lme_play_button(p_user_id in fnd_user.user_id%type,
4531 p_user_type in ota_attempts.user_type%type,
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,
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
4558 BEGIN
4559 hr_utility.set_location('Entering :'||l_proc,5);
4560
4561 --check for mult online evts and throw null if so
4562 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
4563
4564 if (l_online_evt_count > 1) then
4565 return 'DISABLE_MULT_ENRL_EVENTS';
4566 end if;
4567
4568 l_online_event_id := get_lme_online_event_id(p_lp_member_enrollment_id);
4569
4570 open csr_event(l_online_event_id);
4571 fetch csr_event into rec;
4572 close csr_event;
4573
4574 if (l_online_event_id is not null) then
4575 l_return_play_btn := GET_PLAY_BUTTON(p_user_id,p_user_type, p_is_manager, rec.EVENT_ID, rec.EVENT_TYPE
4576 ,rec.SYNCHRONOUS_FLAG, rec.ONLINE_FLAG
4577 ,rec.COURSE_START_DATE ,rec.COURSE_END_DATE);
4578 end if;
4579 hr_utility.set_location('Leaving :'||l_proc,10);
4580
4581 return l_return_play_btn;
4582
4583 EXCEPTION
4584 WHEN others THEN
4585 hr_utility.set_location('Leaving :'||l_proc,15);
4586
4587 RETURN null;
4588
4589 End get_lme_play_button;
4590
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
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);
4611
4612 --check for mult online evts and throw null if so
4613 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
4614
4615 if (l_online_evt_count > 1) then
4616 return null;
4617 end if;
4618
4619 l_online_event_id := get_lme_online_event_id(p_lp_member_enrollment_id);
4620
4621 open csr_toolbar_flag(l_online_event_id);
4622 fetch csr_toolbar_flag into l_player_toolbar_flag;
4623 close csr_toolbar_flag;
4624
4625 hr_utility.set_location('Leaving :'||l_proc,10);
4626
4627 return l_player_toolbar_flag;
4628
4629 EXCEPTION
4630 WHEN others THEN
4631 hr_utility.set_location('Leaving :'||l_proc,15);
4632
4633 RETURN null;
4634
4635 End get_lme_player_toolbar_flag;
4636
4637 function get_lpe_lo_status(p_user_id in fnd_user.user_id%type,
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
4656 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4657
4658 cursor csr_get_act is
4659 select lpm.activity_version_id
4660 from ota_learning_path_members lpm,
4661 ota_lp_member_enrollments lme
4662 where lpm.LEARNING_PATH_MEMBER_ID = lme.LEARNING_PATH_MEMBER_ID
4663 and lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
4664
4665
4666 CURSOR csr_prf_ord(p_activity_version_id in ota_activity_versions.activity_version_id%type,
4667 p_user_id in number,
4668 p_user_type in varchar2) IS
4669 SELECT prf.user_id,
4670 lo.learning_object_id,
4671 decode(prf.lesson_status, 'P', '1',
4672 'C', '2',
4673 'F', '3',
4674 'I', '4',
4675 'B', '5',
4676 'N', '6') decode_lesson_status,
4677 prf.lesson_status lesson_status,
4678 prf.performance_id
4679 FROM ota_performances prf,
4680 ota_offerings ofr,
4681 ota_learning_objects lo
4682 WHERE
4683 prf.user_id = p_user_id
4684 and prf.user_type = p_user_type
4685 and lo.learning_object_id = prf.learning_object_id
4686 and prf.cert_prd_enrollment_id is null
4687 and ofr.learning_object_id = lo.learning_object_id
4688 and ofr.activity_version_id = p_activity_version_id
4689 order by decode_lesson_status;
4690
4691 l_proc VARCHAR2(72) := g_package||'get_lpe_lo_status';
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
4701 l_return_lo_status VARCHAR2(30);
4702 l_online_evt_count number;
4703 BEGIN
4704 hr_utility.set_location('Entering :'||l_proc,5);
4705
4706 --check for mult online evts and throw null if so
4707 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
4708
4709 if (l_online_evt_count > 1) then
4710 --return as Not Attempted
4711 --Bug 4560354
4712 --l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4713 open csr_get_act;
4714 fetch csr_get_act into rec_get_act;
4715 close csr_get_act;
4716
4717 if rec_get_act.activity_version_id is not null then
4718 open csr_prf_ord(rec_get_act.activity_version_id, p_user_id, p_user_type);
4719 fetch csr_prf_ord into rec_prf_ord;
4720 close csr_prf_ord;
4721
4722 if rec_prf_ord.lesson_status is not null then
4723 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',rec_prf_ord.lesson_status);
4724 else
4725 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4726 end if;
4727 end if;
4728
4729 return l_return_lo_status;
4730 end if;
4731
4732 l_online_event_id := get_lme_online_event_id(p_lp_member_enrollment_id);
4733
4734 open csr_event(l_online_event_id);
4735 fetch csr_event into rec;
4736 close csr_event;
4737
4738 l_status_name := get_enroll_lo_status(p_user_id, p_user_type, rec.event_id, null, null, 1);
4739
4740 hr_utility.set_location('Leaving :'||l_proc,10);
4741
4742 return l_status_name;
4743
4744 EXCEPTION
4745 WHEN others THEN
4746 hr_utility.set_location('Leaving :'||l_proc,15);
4747
4748 RETURN null;
4749
4750 End get_lpe_lo_status;
4751
4752 function get_lme_onl_evt_count(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
4753 return varchar2 is
4754 CURSOR csr_lme_info IS
4755 SELECT lme.lp_member_enrollment_id,
4756 lmb.ACTIVITY_VERSION_ID,
4757 lpe.lp_enrollment_id,
4758 lme.object_version_number,
4759 lmb.learning_path_member_id,
4760 lme.member_status_code,
4761 lpe.person_id,
4762 lpe.contact_id
4763 FROM ota_learning_path_members lmb,
4764 ota_lp_member_enrollments lme,
4765 ota_lp_enrollments lpe
4766 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
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,
4776 evt.event_id,
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
4786 WHERE evt.event_id = tdb.event_id
4787 AND bst.booking_status_type_id = tdb.booking_status_type_id
4788 AND evt.activity_version_id = csr_activity_version_id
4789 --AND tdb.delegate_person_id = p_person_id
4790 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
4791 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
4792 )
4793 AND evt.parent_offering_id = ofr.offering_id
4794 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
4795 AND tdb.booking_status_type_id = bst.booking_status_type_id
4796 AND bst.type <> 'C';
4797
4798 l_proc VARCHAR2(72) := g_package||'get_lme_onl_evt_count';
4799 rec_lme_info csr_lme_info%rowtype;
4800
4801 l_person_id number;
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
4811 hr_utility.set_location('Entering :'||l_proc,5);
4812
4813 open csr_lme_info;
4814 fetch csr_lme_info into rec_lme_info;
4815 close csr_lme_info;
4816
4817 hr_utility.set_location(' Step:'||l_proc,10);
4818
4819 FOR rec IN get_enrl_status(rec_lme_info.activity_version_id,
4820 rec_lme_info.person_id,
4821 rec_lme_info.contact_id)
4822 LOOP
4823
4824 if rec.online_flag = 'Y' then
4825 l_online_event_id := rec.event_id;
4826 l_online_event_count := l_online_event_count + 1;
4827 --exit;
4828 end if;
4829
4830 END LOOP;
4831
4832 hr_utility.set_location('Leaving :'||l_proc,10);
4833
4834 return '' || l_online_event_count;
4835
4836 EXCEPTION
4837 WHEN others THEN
4838 hr_utility.set_location('Leaving :'||l_proc,15);
4839
4840 RETURN null;
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
4850 cursor csr_active_cert_prd is
4851 SELECT cpe.cert_prd_enrollment_id
4852 FROM
4853 OTA_CERTIFICATIONS_B crt,
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
4863 AND cme.cert_member_id = cmb.certification_member_id
4864 AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
4865 AND cre.CERT_ENROLLMENT_ID = cpe.CERT_ENROLLMENT_ID
4866 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
4867 AND ((p_person_id is not null AND cre.PERSON_ID = p_person_id)
4868 OR (p_contact_id is not null AND cre.CONTACT_ID = p_contact_id))
4869 AND (cre.IS_HISTORY_FLAG IS NULL OR cre.IS_HISTORY_FLAG = 'N')
4870 AND (NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE))
4871 AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
4872 AND trunc(sysdate) between trunc(cpe.CERT_PERIOD_START_DATE)
4873 and trunc(cpe.CERT_PERIOD_END_DATE)
4874 AND ( ( evt.course_start_date >= cpe.cert_period_start_date and
4875 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= cpe.cert_period_end_date )
4876 or (evt.event_type = 'SELFPACED' AND
4877 ((cpe.cert_period_end_date >= evt.course_start_date) AND
4878 ((evt.course_end_date is null) or
4879 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= cpe.cert_period_start_date))
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
4889 begin
4890
4891 hr_utility.set_location(' Step:'||l_proc,10);
4892
4893 for rec_active_cert_prd in csr_active_cert_prd
4894 loop
4895 --populate OUT cert_prd_enrollment_ids params
4896 IF rec_active_cert_prd.cert_prd_enrollment_id IS NOT NULL THEN
4897 if l_cert_prd_enrollment_ids is null then
4898 l_cert_prd_enrollment_ids := rec_active_cert_prd.cert_prd_enrollment_id;
4899 else
4900 l_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids || '^' || rec_active_cert_prd.cert_prd_enrollment_id;
4901 end if;
4902 END IF;
4903 end loop;
4904
4905 p_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids;
4906 hr_utility.set_location(' Step:'||l_proc,30);
4907
4908 EXCEPTION
4909 WHEN others THEN
4910 hr_utility.set_location('Leaving :'||l_proc,40);
4911 p_cert_prd_enrollment_ids := null;
4912 --MULTI MESSAGE SUPPORT
4913
4914 end get_active_cert_prds;
4915
4916 -- ----------------------------------------------------------------------------
4917 -- |--------------------< LO_has_cld_and_no_strt_url>-------------------------|
4918 -- ----------------------------------------------------------------------------
4919 --
4920 FUNCTION Lo_has_cld_and_no_strt_url
4921 (p_learning_object_id in number default hr_api.g_number)
4922 RETURN varchar2 is
4923 --
4924 -- decalare cursor
4925 Cursor csr_par_with_cld_no_url is
4926 Select null
4927 From ota_learning_objects cld, ota_learning_objects par
4928 Where par.learning_object_id = cld.parent_learning_object_id
4929 And par.starting_url is null
4930 And par.learning_object_id = p_learning_object_id
4931 And rownum = 1;
4932 --
4933 -- decalre variables
4934 l_learning_object_flag varchar2(10);
4935 l_return varchar2(2) := 'N';
4936 l_proc varchar2(72) := g_package||'LO_has_child_and_having_url';
4937 --
4938 BEGIN
4939 --
4940 hr_utility.set_location('Entering:'|| l_proc, 10);
4941 --
4942 OPEN csr_par_with_cld_no_url;
4943 FETCH csr_par_with_cld_no_url INTO l_learning_object_flag;
4944 IF csr_par_with_cld_no_url%found THEN
4945 --
4946 l_return := 'Y';
4947 hr_utility.set_location('returning Y:'|| l_proc, 10);
4948 END IF;
4949 --
4950 CLOSE csr_par_with_cld_no_url;
4951 --
4952 hr_utility.set_location('Exiting:'|| l_proc, 20);
4953 --
4954 RETURN l_return;
4955 --
4956 End Lo_has_cld_and_no_strt_url;
4957
4958 end ota_lo_utility;