[Home] [Help]
PACKAGE BODY: APPS.OTA_LO_UTILITY
Source
1 package body ota_lo_utility as
2 /* $Header: otloutil.pkb 120.106.12020000.10 2013/03/29 12:58:08 jaysridh ship $ */
3
4 g_package varchar2(33) := ' ota_lo_utility.'; -- Global package name
5 cursor csr_active_cert_prd_person(p_event_id ota_events.event_id%type,
6 p_person_id ota_cert_enrollments.contact_id%type) is
7 SELECT cpe.cert_prd_enrollment_id
8 FROM OTA_CERTIFICATIONS_B crt,
9 OTA_CERT_ENROLLMENTS cre,
10 OTA_CERT_PRD_ENROLLMENTS cpe,
11 OTA_CERT_MBR_ENROLLMENTS cme,
12 OTA_CERTIFICATION_MEMBERS cmb,
13 OTA_EVENTS evt
14 WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
15 AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
16 AND cmb.OBJECT_TYPE = 'H'
17 AND cmb.OBJECT_ID = evt.activity_version_id
18 AND evt.event_id = p_event_id
19 AND cme.cert_member_id = cmb.certification_member_id
20 AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
21 AND cre.CERT_ENROLLMENT_ID = cpe.CERT_ENROLLMENT_ID
22 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
23 AND cre.person_id = p_person_id
24 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
25 AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
26 AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
27 --AND trunc(sysdate) between trunc(cpe.CERT_PERIOD_START_DATE) and trunc(cpe.CERT_PERIOD_END_DATE)
28 AND ((evt.event_type = 'SCHEDULED' AND
29 evt.course_start_date >= cpe.cert_period_start_date AND
30 evt.course_end_date <= cpe.cert_period_end_date )
31 OR (evt.event_type = 'SELFPACED' AND
32 cpe.cert_period_end_date >= evt.course_start_date AND
33 NVL(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= cpe.cert_period_start_date)
34 );
35
36 cursor csr_active_cert_prd_contact(p_event_id ota_events.event_id%type,
37 p_contact_id ota_cert_enrollments.contact_id%type) is
38 SELECT cpe.cert_prd_enrollment_id
39 FROM OTA_CERTIFICATIONS_B crt,
40 OTA_CERT_ENROLLMENTS cre,
41 OTA_CERT_PRD_ENROLLMENTS cpe,
42 OTA_CERT_MBR_ENROLLMENTS cme,
43 OTA_CERTIFICATION_MEMBERS cmb,
44 OTA_EVENTS evt
45 WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
46 AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
47 AND cmb.OBJECT_TYPE = 'H'
48 AND cmb.OBJECT_ID = evt.activity_version_id
49 AND evt.event_id = p_event_id
50 AND cme.cert_member_id = cmb.certification_member_id
51 AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
52 AND cre.CERT_ENROLLMENT_ID = cpe.CERT_ENROLLMENT_ID
53 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
54 AND cre.contact_id = p_contact_id
55 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
56 AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
57 AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
58 --AND trunc(sysdate) between trunc(cpe.CERT_PERIOD_START_DATE) and trunc(cpe.CERT_PERIOD_END_DATE)
59 AND ((evt.event_type = 'SCHEDULED' AND
60 evt.course_start_date >= cpe.cert_period_start_date AND
61 evt.course_end_date <= cpe.cert_period_end_date )
62 OR (evt.event_type = 'SELFPACED' AND
63 cpe.cert_period_end_date >= evt.course_start_date AND
64 NVL(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= cpe.cert_period_start_date)
65 );
66
67
68 function compute_default_lesson_status(
69 p_lo_id ota_learning_objects.learning_object_id%type,
70 p_user_id fnd_user.user_id%type,
71 p_user_type ota_attempts.user_type%type,
72 p_old_lesson_status ota_performances.lesson_status%type,
73 p_starting_url ota_learning_objects.starting_url%type,
74 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
75 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
76
77 cursor child_los(
78 p_lo_id ota_learning_objects.learning_object_id%type,
79 p_user_id fnd_user.user_id%type,
80 p_user_type ota_attempts.user_type%type) is
81 select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
82 from ota_learning_objects lo, ota_performances p
83 where lo.parent_learning_object_id = p_lo_id and
84 lo.published_flag = 'Y' and
85 p.learning_object_id(+) = lo.source_learning_object_id and
86 p.user_id(+) = p_user_id and
87 p.user_type(+) = p_user_type and
88 nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1) and
89 nvl(p.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
90
91 v_completed boolean := false;
92 v_not_attempted boolean := false;
93 v_other boolean := false;
94 v_has_children boolean := false;
95 begin
96 if p_starting_url is not null then
97 return p_old_lesson_status;
98 end if;
99
100 for a_child_lo in child_los(p_lo_id, p_user_id, p_user_type) loop
101 v_has_children := true;
102
103 if a_child_lo.lesson_status = 'P' or a_child_lo.lesson_status = 'C' then
104 v_completed := true;
105 elsif a_child_lo.lesson_status = 'N' then
106 v_not_attempted := true;
107 else
108 v_other := true;
109 end if;
110 end loop;
111
112 if not v_has_children then
113 return p_old_lesson_status;
114 else
115 if v_other then
116 return 'I';
117 elsif v_completed then
118 if v_not_attempted then
119 return 'I';
120 else
121 return 'C';
122 end if;
123 else
124 return 'N';
125 end if;
126 end if;
127 end compute_default_lesson_status;
128
129
130 function compute_lesson_status(
131 p_lo_id ota_learning_objects.learning_object_id%type,
132 p_user_id fnd_user.user_id%type,
133 p_user_type ota_attempts.user_type%type,
134 p_old_lesson_status ota_performances.lesson_status%type,
135 p_starting_url ota_learning_objects.starting_url%type,
136 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
137 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) return ota_performances.lesson_status%type is
138
139 /* Completion reqs not yet implemented - GDHUTTON 12/24/03
140 cursor completion_requirements(
141 p_lo_id ota_learning_objects.learning_object_id%type,
142 p_user_id fnd_user.user_id%type,
143 p_user_type ota_attempts.user_type%type,
144 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
145 select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,
146 null as match_lesson_status, nvl(p.lesson_status, 'N') as user_lesson_status
147 from ota_performances p, ota_completion_requirements cr
148 where cr.child_learning_object_id = p_lo_id and
149 p.learning_object_id(+) = cr.parent_learning_object_id and
150 p.user_id(+) = p_user_id and
151 p.user_type(+) = p_user_type and
152 nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1)
153 order by cr.seq asc;
154 */
155
156 begin
157 /* Completion reqs not yet implemented - GDHUTTON 12/24/03
158 for a_cr in completion_requirements(p_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
159 if (a_cr.match_lesson_status is null and (a_cr.user_lesson_status = 'P' or a_cr.user_lesson_status = 'C'))
160 or a_cr.user_lesson_status = a_cr.match_lesson_status then
161 return a_cr.assigned_lesson_status;
162 end if;
163 end loop;
164 */
165
166 -- Either there were no completion requirements or none were fulfilled.
167 -- Return the default status.
168 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, p_scorm_learner_attempt_id);
169 end compute_lesson_status;
170
171
172 -- Returns true if a transition from v_old_status to v_new_status is
173 -- allowed and necessary
174 function lesson_status_transition_valid(
175 p_old_lesson_status ota_performances.lesson_status%type,
176 p_new_lesson_status ota_performances.lesson_status%type) return boolean is
177 begin
178 return
179 p_new_lesson_status <> p_old_lesson_status and
180 p_old_lesson_status <> 'P' and
181 (p_old_lesson_status <> 'C' or p_new_lesson_status = 'P');
182 end lesson_status_transition_valid;
183
184
185 function get_contact_id_for_party(
186 p_party_id hz_parties.party_id%type) return number is
187
188 cursor contact(
189 p_party_id hz_parties.party_id%type) is
190 select acct_role.cust_account_role_id
191 from hz_cust_account_roles acct_role,
192 hz_relationships rel,
193 hz_cust_accounts role_acct
194 where acct_role.party_id = rel.party_id and
195 acct_role.role_type = 'CONTACT' and
196 acct_role.cust_account_id = role_acct.cust_account_id and
197 role_acct.party_id = rel.object_id and
198 rel.subject_id = p_party_id and
199 rel.subject_table_name = 'HZ_PARTIES' and
200 rel.object_table_name = 'HZ_PARTIES';
201
202 v_result hz_parties.party_id%type;
203 begin
204 open contact(p_party_id);
205 fetch contact into v_result;
206 close contact;
207 return v_result;
208 exception
209 when others then
210 if contact%isopen then
211 close contact;
212 end if;
213 raise;
214 end get_contact_id_for_party;
215
216
217 procedure update_cme_status_for_lo(
218 p_lo_id ota_learning_objects.learning_object_id%type,
219 p_date date,
220 p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
221
222 -- This cursor finds all certification member records in the certification
223 -- period that area associated with this learning object.
224 cursor cert_member_enrollments(
225 p_learning_object_id ota_learning_objects.learning_object_id%type,
226 p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
227 select distinct(cme.cert_mbr_enrollment_id),
228 cme.member_status_code,
229 cme.object_version_number,
230 cme.cert_member_id,
231 cme.cert_prd_enrollment_id,
232 nvl(act.eres_enabled,'N') sign_enabled,
233 cm.object_id
234 from ota_certification_members cm, ota_cert_mbr_enrollments cme,
235 ota_offerings o, ota_cert_prd_enrollments cpe, ota_activity_versions act
236 where
237 cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
238 and cme.cert_member_id = cm.certification_member_id
239 and cm.object_id = o.activity_version_id
240 and o.learning_object_id = p_learning_object_id
241 -- filter ended offerings
242 and trunc(sysdate) between trunc(o.start_date) and nvl(trunc(o.end_date), trunc(sysdate))
243 and cme.cert_prd_enrollment_id = cpe.cert_prd_enrollment_id
244 and cpe.period_status_code <> 'CANCELLED'
245 and trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
246 and cme.member_status_code <> 'CANCELLED'
247 and act.activity_version_id = o.activity_version_id;
248
249 l_new_mbr_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
250 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
251 l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
252
253 begin
254 l_new_mbr_status_code := 'COMPLETED';
255
256 for a_cert_mbr_enrollment in cert_member_enrollments(p_lo_id, p_cert_prd_enroll_id) loop
257
258 -- calculating cme status depending on enrollment status even in case of online classes as
259 -- the enrollment status drives the cme status now. If the enrollment status is in pending signature/evaluation,
260 -- then the cme status should be pending even if the player status for the online course is completed.
261 ota_cme_util.calculate_cme_status(p_activity_version_id => a_cert_mbr_enrollment.object_id,
262 p_cert_prd_enrollment_id => p_cert_prd_enroll_id,
263 p_mode => 'U',
264 p_member_status_code => l_member_status_code,
265 p_completion_date => l_completion_date);
266 --call upd cme api
267 -- Added the following if clause for the newly introduced cme status 'pending'. This would prevent updation of
268 -- certification status to certified even if the member status is completed when the enrollments are in pending signature/evaluation status.
269 if l_member_status_code = 'PENDING' then
270 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
271 (p_effective_date => sysdate
272 ,p_object_version_number => a_cert_mbr_enrollment.object_version_number
273 ,p_cert_member_id => a_cert_mbr_enrollment.cert_member_id
274 ,p_cert_prd_enrollment_id => p_cert_prd_enroll_id
275 ,p_cert_mbr_enrollment_id => a_cert_mbr_enrollment.cert_mbr_enrollment_id
276 ,p_member_status_code => l_member_status_code);
277 elsif l_member_status_code = 'COMPLETED' then
278 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
279 (p_effective_date => sysdate
280 ,p_object_version_number => a_cert_mbr_enrollment.object_version_number
281 ,p_cert_member_id => a_cert_mbr_enrollment.cert_member_id
282 ,p_cert_prd_enrollment_id => p_cert_prd_enroll_id
283 ,p_cert_mbr_enrollment_id => a_cert_mbr_enrollment.cert_mbr_enrollment_id
284 ,p_member_status_code => l_new_mbr_status_code
285 ,p_completion_date => p_date);
286 end if;
287
288 -- cascade status to certification period
289 ota_cme_util.update_cpe_status(a_cert_mbr_enrollment.cert_mbr_enrollment_id, a_cert_mbr_enrollment.cert_prd_enrollment_id);
290
291 end loop;
292 end update_cme_status_for_lo;
293
294
295 procedure update_enrollment(
296 p_booking_id ota_delegate_bookings.booking_id%type,
297 p_event_id ota_events.event_id%type,
298 p_business_group_id ota_delegate_bookings.business_group_id%type,
299 p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
300 p_object_version_number ota_delegate_bookings.object_version_number%type,
301 p_sign_eval_status ota_delegate_bookings.sign_eval_status%type,
302 p_date_status_changed ota_delegate_bookings.date_status_changed%type,
303 p_new_status varchar2,
304 p_failed varchar2,
305 p_signed varchar2) is
306
307 v_result_object_version_number ota_finance_lines.object_version_number%type;
308 v_finance_line_id ota_finance_lines.finance_line_id%type;
309 v_booking_status_row ota_booking_status_types%rowtype;
310 v_object_version_number ota_delegate_bookings.object_version_number%type;
311 v_date_status_changed ota_delegate_bookings.date_status_changed%type:=p_date_status_changed;
312 l_successful_attendance_flag varchar2(1):='N';
313 l_failure_reason varchar2(20);
314 l_new_sign_eval_status ota_delegate_bookings.sign_eval_status%type;
315 l_status varchar2(20):=p_new_status;
316 L_PROC varchar2 (72):= G_PACKAGE || 'UPDATE_ENROLLMENT';
317
318
319 --Added cursor to check whether update is needed for the enrollment in case of online classes
320 --after learner has signed the course.
321 cursor csr_book_details is
322 select odb.delegate_person_id, odb.delegate_contact_id, offer.learning_object_id
323 from ota_delegate_bookings odb, ota_events evt, ota_offerings offer
324 where odb.event_id = evt.event_id
325 and evt.parent_offering_id = offer.offering_id
326 and booking_id = p_booking_id;
327
328 cursor csr_succ_att_fail_details is
329 select successful_attendance_flag, failure_reason
330 from ota_delegate_bookings
331 where booking_id = p_booking_id;
332
333 l_person_id ota_delegate_bookings.delegate_person_id%type;
334 l_contact_id ota_delegate_bookings.delegate_contact_id%type;
335 l_lo_id ota_offerings.learning_object_id%type;
336 l_cert_prd_enr_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
337 l_member_in varchar2(10);
338 l_booking_successful_att_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
339 l_booking_failure_reason ota_delegate_bookings.failure_reason%Type;
340 begin
341 HR_UTILITY.SET_LOCATION ('Entering:' || L_PROC, 5);
342 --HR_UTILITY.TRACE ('SIGN_EVAL_STATUS: ' || p_sign_eval_status);
343 --HR_UTILITY.TRACE ('ENR STATUS: ' || p_new_status);
344 --HR_UTILITY.TRACE ('FAILURE: ' || p_failed);
345 --HR_UTILITY.TRACE ('DATE_BOOKING_PLACED: ' || p_date_booking_placed);
346 v_finance_line_id := null;
347 v_object_version_number := p_object_version_number;
348 if p_sign_eval_status is not null then
349 if p_signed='Y' then
350 case p_sign_eval_status
351 when 'SE' then l_new_sign_eval_status:='ME';
352 l_status:='PENDING EVALUATION';
353 when 'VE' then l_new_sign_eval_status:='OE';
354 l_status:='ATTENDED';
355 when 'UE' then l_new_sign_eval_status:='DD';
356 l_status:='ATTENDED';
357 when 'CE' then l_new_sign_eval_status:='DD'; -- learner signed after renewing the certification.
358 l_status:='ATTENDED';
359 else l_new_sign_eval_status:=p_sign_eval_status;
360 end case;
361 elsif p_signed is null then
362 case p_sign_eval_status
363 when 'SD' then l_new_sign_eval_status:='SE';
364 l_status:='PENDING EVALUATION';
365 when 'VD' then l_new_sign_eval_status:='VE';
366 l_status:='PENDING EVALUATION';
367 when 'MD' then l_new_sign_eval_status:='ME';
368 l_status:='PENDING EVALUATION';
369 when 'UD' then l_new_sign_eval_status:='UE';
370 l_status:='PENDING EVALUATION';
371 when 'OD' then l_new_sign_eval_status:='OE';
372 l_status:='ATTENDED';
373 when 'SE' then l_new_sign_eval_status:='ME';
374 l_status:='PENDING EVALUATION';
375 when 'UE' then l_new_sign_eval_status:='DD';
376 l_status:='ATTENDED';
377 when 'ME' then l_new_sign_eval_status:='DD';
378 l_status:='ATTENDED';
379 when 'OE' then l_new_sign_eval_status:='DD';
380 l_status:='ATTENDED';
381 when 'VE' then l_new_sign_eval_status:='OE';
382 l_status:='ATTENDED';
383 else l_new_sign_eval_status:=p_sign_eval_status;
384 l_status:='ATTENDED';
385 end case;
386 end if;
387 end if;
388 if p_new_status = 'RENEWING' then -- check to see if we need to enable the signature after the learner has renewed and played the course.
389 l_status:='ATTENDED';
390 l_new_sign_eval_status:='CE';
391 end if;
392 v_booking_status_row :=
393 ota_enroll_in_training_ss.get_booking_status_for_web(
394 p_web_booking_status_type => l_status,
395 p_business_group_id => p_business_group_id);
396
397
398
399
400 if p_failed = 'Y' then
401 l_failure_reason:= 'OF';
402 elsif l_status = 'ATTENDED' then
403 --Bug 14793946. The successfully attended flag should be set only for mandatory eval scenario.
404 if (p_sign_eval_status is null) or (p_sign_eval_status is not null and p_sign_eval_status not in ('OD','VE','UE','OE')) then
405 l_successful_attendance_flag:='Y';
406 else
407 --Bug 14793946. Set the existing successfully attended flag and failure reason for voluntary eval and sign only.
408 open csr_succ_att_fail_details;
409 fetch csr_succ_att_fail_details into l_booking_successful_att_flag, l_booking_failure_reason;
410 if csr_succ_att_fail_details%FOUND then
411 l_successful_attendance_flag := nvl(l_booking_successful_att_flag,'N');
412 l_failure_reason := l_booking_failure_reason;
413 end if;
414 close csr_succ_att_fail_details;
415 end if;
416 end if;
417
418 if v_date_status_changed is null then
419 v_date_status_changed := sysdate;
420 end if;
421
422
423 ota_tdb_api_upd2.update_enrollment(
424 p_booking_id => p_booking_id,
425 p_event_id => p_event_id,
426 p_failure_reason => l_failure_reason,
427 p_number_of_places => 1, --Added for bug 10149446
428 p_object_version_number => v_object_version_number,
429 p_booking_status_type_id => v_booking_status_row.booking_status_type_id,
430 p_tfl_object_version_number => v_result_object_version_number,
431 p_finance_line_id => v_finance_line_id,
432 p_date_status_changed => p_date_status_changed,
433 p_date_booking_placed => p_date_booking_placed,
434 p_successful_attendance_flag => l_successful_attendance_flag,
435 p_sign_eval_status => l_new_sign_eval_status);
436
437
438
439 --Added the following code to update cme status after learner has given the signature for a course
440 --which is part of a cert. This code is not present earlier as signature have not been implemented
441 --earlier for certifications. This is needed only for online courses as the code in ota_cme_util.update_cme_status
442 --will ignore the online case whenever there is a enrollment update.
443 if p_signed = 'Y' then
444 open csr_book_details;
445 fetch csr_book_details into l_person_id, l_contact_id, l_lo_id;
446 close csr_book_details;
447
448 if l_lo_id is not null then
449 l_member_in := get_member_in(p_event_id, l_person_id, l_contact_id);
450 if(instr(l_member_in, 'CERT')<>0) then
451 if l_person_id is not null then
452 open csr_active_cert_prd_person(p_event_id, l_person_id);
453 fetch csr_active_cert_prd_person into l_cert_prd_enr_id;
454 close csr_active_cert_prd_person;
455 elsif l_contact_id is not null then
456 open csr_active_cert_prd_contact(p_event_id, l_contact_id);
457 fetch csr_active_cert_prd_contact into l_cert_prd_enr_id;
458 close csr_active_cert_prd_contact;
459 end if;
460 end if;
461 if l_cert_prd_enr_id is not null then
462 update_cme_status_for_lo(l_lo_id, sysdate, l_cert_prd_enr_id);
463 end if;
464 end if;
465 end if;
466 HR_UTILITY.SET_LOCATION ('Leaving:' || L_PROC, 10);
467 end update_enrollment;
468
469
470 procedure update_enrollment(
471 p_booking_id ota_delegate_bookings.booking_id%type,
472 p_event_id ota_events.event_id%type,
473 p_business_group_id ota_delegate_bookings.business_group_id%type,
474 p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
475 p_object_version_number ota_delegate_bookings.object_version_number%type,
476 p_sign_eval_status ota_delegate_bookings.sign_eval_status%type,
477 p_date_status_changed ota_delegate_bookings.date_status_changed%type,
478 p_new_status varchar2,
479 p_failed varchar2) is
480
481 begin
482 update_enrollment(p_booking_id,p_event_id,p_business_group_id,p_date_booking_placed,p_object_version_number,p_sign_eval_status,p_date_status_changed,p_new_status,p_failed,null);
483 end update_enrollment;
484
485 procedure update_enrollment(
486 p_booking_id ota_delegate_bookings.booking_id%type,
487 p_event_id ota_events.event_id%type,
488 p_business_group_id ota_delegate_bookings.business_group_id%type,
489 p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
490 p_object_version_number ota_delegate_bookings.object_version_number%type,
491 p_date_status_changed ota_delegate_bookings.date_status_changed%type,
492 p_new_status varchar2) is
493
494
495 begin
496 update_enrollment(p_booking_id,p_event_id,p_business_group_id,p_date_booking_placed,p_object_version_number,null,p_date_status_changed,p_new_status,null);
497
498 end update_enrollment;
499
500
501 --Added for updating the enrollment status to 'Attended'
502 --for the learners who have completed the mandatory evaluation.
503 procedure update_enrollment_status(
504 p_user_id fnd_user.user_id%type,
505 p_user_type ota_attempts.user_type%type,
506 p_event_id ota_events.event_id%type) is
507
508 -- This cursor finds all events in all offerings which offer the LO and in
509 -- which the person is enrolled with a status of 'PENDING EVALUATION'.
510
511
512 cursor person_bookings(
513 p_event_id ota_events.event_id%type,
514 p_person_id ota_delegate_bookings.delegate_person_id%type) is
515 select book.booking_id,
516 book.sign_eval_status,
517 ev.event_id,
518 book.business_group_id,
519 book.date_booking_placed,
520 book.object_version_number
521 from ota_events ev,
522 ota_delegate_bookings book,
523 ota_booking_status_types stype
524 where nvl(ev.course_start_date, sysdate) <= sysdate and
525 book.event_id = ev.event_id and
526 book.delegate_person_id = p_person_id and
527 ev.event_id = p_event_id and
528 book.booking_status_type_id = stype.booking_status_type_id and
529 (stype.type = 'E' or (stype.type ='A' and book.sign_eval_status in ('OE'))); --11795316
530
531 --Added for 6826434.
532 cursor party_bookings(
533 p_event_id ota_events.event_id%type,
534 p_party_id ota_delegate_bookings.delegate_contact_id%type) is
535 select book.booking_id,
536 book.sign_eval_status,
537 ev.event_id,
538 book.business_group_id,
539 book.date_booking_placed,
540 book.object_version_number
541 from ota_events ev,
542 ota_delegate_bookings book,
543 ota_booking_status_types stype,
544 hz_cust_account_roles acct_role,
545 hz_relationships rel,
546 hz_cust_accounts role_acct
547 where nvl(ev.course_start_date, sysdate) <= sysdate and
548 book.event_id = ev.event_id and
549 book.booking_status_type_id = stype.booking_status_type_id and
550 ev.event_id = p_event_id and
551 (stype.type = 'E' or (stype.type ='A' and book.sign_eval_status in ('OE'))) and --11795316
552 book.delegate_contact_id = acct_role.cust_account_role_id and
553 acct_role.party_id = rel.party_id and
554 acct_role.role_type = 'CONTACT' and
555 acct_role.cust_account_id = role_acct.cust_account_id and
556 role_acct.party_id = rel.object_id and
557 rel.subject_id = p_party_id and
558 rel.subject_table_name = 'HZ_PARTIES' and
559 rel.object_table_name = 'HZ_PARTIES';
560
561 cursor perf_lesson_status is
562 select per.lesson_status,
563 offe.learning_object_id
564 from ota_performances per,
565 ota_offerings offe,
566 ota_events evt
567 where evt.parent_offering_id = offe.offering_id and
568 offe.learning_object_id = per.learning_object_id(+) and
569 evt.event_id = p_event_id and
570 (per.user_type is null or per.user_type = p_user_type) and
571 (per.user_id is null or per.user_id = p_user_id);
572
573
574 l_proc varchar2(72) := g_package||'update_enrollment_status';
575 l_status varchar2(80) :='ATTENDED';
576 l_perf_status varchar2(1);
577 l_learning_object_id ota_offerings.learning_object_id%type;
578 l_failed varchar2(1):= null;
579 l_new_sign_eval_status varchar2(2) := null;
580
581 begin
582 hr_utility.set_location('Entering:'|| l_proc, 10);
583 open perf_lesson_status;
584 fetch perf_lesson_status into l_perf_status,l_learning_object_id;
585 close perf_lesson_status;
586 if(l_perf_status = 'F') then
587 l_failed := 'Y';
588 elsif ((l_perf_status is null) and (l_learning_object_id is null)) then
589 l_failed := 'N';
590 end if;
591 If (p_user_type = 'E') Then
592 for a_booking in person_bookings(p_event_id, p_user_id) loop
593 update_enrollment(
594 a_booking.booking_id,
595 a_booking.event_id,
596 a_booking.business_group_id,
597 a_booking.date_booking_placed,
598 a_booking.object_version_number,
599 a_booking.sign_eval_status,
600 sysdate,
601 l_status,
602 l_failed);
603 end loop;
604 else --Added for 6826434.
605 for a_booking in party_bookings(p_event_id, p_user_id) loop
606 update_enrollment(
607 a_booking.booking_id,
608 a_booking.event_id,
609 a_booking.business_group_id,
610 a_booking.date_booking_placed,
611 a_booking.object_version_number,
612 a_booking.sign_eval_status,
613 sysdate,
614 l_status,
615 l_failed);
616 end loop;
617
618 End If;
619 hr_utility.set_location('Exiting:'|| l_proc, 20);
620 end update_enrollment_status;
621
622
623
624 procedure update_enroll_status_for_lo(
625 p_lo_id ota_learning_objects.learning_object_id%type,
626 p_user_id fnd_user.user_id%type,
627 p_user_type ota_attempts.user_type%type,
628 p_date date) is
629
630
631
632
633
634 l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
635
636 begin
637 hr_utility.set_location('Entering:'|| l_proc, 10);
638 update_enroll_status_for_lo(p_lo_id,p_user_id,p_user_type,p_date,null);
639
640 hr_utility.set_location('Exiting:'|| l_proc, 15);
641 end update_enroll_status_for_lo;
642
643 procedure update_enroll_status_for_lo(
644 p_lo_id ota_learning_objects.learning_object_id%type,
645 p_user_id fnd_user.user_id%type,
646 p_user_type ota_attempts.user_type%type,
647 p_date date,
648 p_failed varchar2) is
649
650 -- This cursor finds all events in all offerings which offer the LO and in
651 -- which the person is enrolled with a status of 'PLACED'. Note that we
652 -- use source_learning_object_id here in case reuse by reference is ever
653 -- implemented.
654 cursor person_bookings(
655 p_lo_id ota_learning_objects.learning_object_id%type,
656 p_person_id ota_delegate_bookings.delegate_person_id%type) is
657 select book.booking_id,
658 book.sign_eval_status,
659 ev.event_id,
660 book.business_group_id,
661 book.date_booking_placed,
662 book.object_version_number,
663 stype.type, --Added for 7110517.
664 act.eres_enabled,
665 book.delegate_person_id,
666 book.successful_attendance_flag
667 from ota_events ev,
668 ota_offerings offr,
669 ota_learning_objects lo,
670 ota_delegate_bookings book,
671 ota_booking_status_types stype,
672 ota_activity_versions act
673 where lo.source_learning_object_id = p_lo_id and
674 lo.learning_object_id = offr.learning_object_id and
675 offr.offering_id = ev.parent_offering_id and
676 nvl(ev.course_start_date, sysdate) <= sysdate and
677 book.event_id = ev.event_id and
678 book.delegate_person_id = p_person_id and
679 book.booking_status_type_id = stype.booking_status_type_id and
680 act.activity_version_id = offr.activity_version_id and
681 (stype.type = 'P' or stype.type = 'A'); -- Used Attended in order to update cert enrollments with the new sign_eval_status
682 -- so that signature will be enabled everytime the learner has played the course after
683 -- renewal.
684
685
686
687 -- This cursor finds all events in all offerings which offer the LO and in
688 -- which the party is enrolled. Note that we use source_learning_object_id
689 -- here in case reuse by reference is ever implemented.
690 cursor party_bookings(
691 p_lo_id ota_learning_objects.learning_object_id%type,
692 p_party_id ota_delegate_bookings.delegate_contact_id%type) is
693 select book.booking_id,
694 book.sign_eval_status,
695 ev.event_id,
696 book.business_group_id,
697 book.date_booking_placed,
698 book.object_version_number,
699 stype.type, --Added fro 7110517.
700 act.eres_enabled,
701 book.delegate_contact_id,
702 book.successful_attendance_flag
703 from ota_events ev,
704 ota_offerings offr,
705 ota_learning_objects lo,
706 ota_delegate_bookings book,
707 ota_booking_status_types stype,
708 hz_cust_account_roles acct_role,
709 hz_relationships rel,
710 hz_cust_accounts role_acct,
711 ota_activity_versions act
712 where lo.source_learning_object_id = p_lo_id and
713 lo.learning_object_id = offr.learning_object_id and
714 offr.offering_id = ev.parent_offering_id and
715 nvl(ev.course_start_date, sysdate) <= sysdate and
716 book.event_id = ev.event_id and
717 book.booking_status_type_id = stype.booking_status_type_id and
718 (stype.type = 'P' or stype.type = 'A') and -- Used Attended in order to update cert enrollments with the new sign_eval_status
719 book.delegate_contact_id = acct_role.cust_account_role_id and -- so that signature will be enabled everytime the learner has played the course after
720 acct_role.party_id = rel.party_id and -- renewal.
721 acct_role.role_type = 'CONTACT' and
722 acct_role.cust_account_id = role_acct.cust_account_id and
723 role_acct.party_id = rel.object_id and
724 rel.subject_id = p_party_id and
725 rel.subject_table_name = 'HZ_PARTIES' and
726 rel.object_table_name = 'HZ_PARTIES' and
727 act.activity_version_id = offr.activity_version_id;
728
729 l_event_id OTA_EVENTS.EVENT_ID%TYPE:= null;
730 l_test_id OTA_TESTS.TEST_ID%type:=null;
731 Cursor csr_attempt_info is
732 select attempt_id from ota_attempts where
733 event_id = l_event_id
734 and test_id = l_test_id
735 and user_id = p_user_id
736 and user_type = p_user_type;
737
738 -- Added following two cursors in order to get the certification status of the
739 -- learner in case he is either a person or a contact.
740 Cursor csr_person_cert_status(p_event_id ota_events.event_id%type,
741 p_person_id ota_cert_enrollments.person_id%type) is
742 SELECT cre.CERTIFICATION_STATUS_CODE
743 FROM OTA_CERTIFICATIONS_B crt,
744 OTA_CERT_ENROLLMENTS cre,
745 OTA_CERT_MBR_ENROLLMENTS cme,
746 OTA_CERTIFICATION_MEMBERS cmb,
747 OTA_EVENTS evt
748 WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
749 AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
750 AND cmb.OBJECT_TYPE = 'H'
751 AND cmb.OBJECT_ID = evt.activity_version_id
752 AND evt.event_id = p_event_id
753 AND cme.cert_member_id = cmb.certification_member_id
754 AND cre.BUSINESS_GROUP_ID = ota_general.get_business_group_id
755 AND cre.person_id = p_person_id
756 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
757 AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
758
759 Cursor csr_contact_cert_status(p_event_id ota_events.event_id%type,
760 p_contact_id ota_cert_enrollments.contact_id%type) is
761 SELECT cre.CERTIFICATION_STATUS_CODE
762 FROM OTA_CERTIFICATIONS_B crt,
763 OTA_CERT_ENROLLMENTS cre,
764 OTA_CERT_MBR_ENROLLMENTS cme,
765 OTA_CERTIFICATION_MEMBERS cmb,
766 OTA_EVENTS evt
767 WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
768 AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
769 AND cmb.OBJECT_TYPE = 'H'
770 AND cmb.OBJECT_ID = evt.activity_version_id
771 AND evt.event_id = p_event_id
772 AND cme.cert_member_id = cmb.certification_member_id
773 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
774 AND cre.contact_id = p_contact_id
775 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
776 AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
777
778
779
780 l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
781 l_act_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
782 l_evt_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
783 l_eval_mand_flag varchar2(1);
784 l_dummy ota_attempts.attempt_id%type;
785 l_is_attempted boolean :=false;
786 l_status varchar2(80) :='ATTENDED';
787 l_user_type ota_attempts.user_type%type;
788 l_new_sign_eval_status varchar2(2):= null;
789 l_member_in varchar2(30);
790 l_cert_status OTA_CERT_ENROLLMENTS.CERTIFICATION_STATUS_CODE%type;
791 begin
792 hr_utility.set_location('Entering:'|| l_proc, 10);
793 if p_user_type = 'E' then
794 for a_booking in person_bookings(p_lo_id, p_user_id) loop
795 l_member_in := get_member_in(a_booking.event_id, a_booking.delegate_person_id,null); -- added to check if the course played by learner is part of a certification.
796 --checking if the course is part of cert and signature is enabled for the course and whether the course has already been completed(with signature) once by the learner(person)
797 if(instr(l_member_in,'CERT')<>0 and a_booking.eres_enabled = 'Y' and a_booking.type = 'A' and a_booking.sign_eval_status in('DD','OE'))then --should we consider optional eval enabled('OE') also as 'DD'
798 open csr_person_cert_status(a_booking.event_id, a_booking.delegate_person_id);
799 fetch csr_person_cert_status into l_cert_status;
800 close csr_person_cert_status;
801 if(l_cert_status = 'RENEWING') then -- checking whether learner has renewed the certification.
802 update_enrollment(
803 a_booking.booking_id,
804 a_booking.event_id,
805 a_booking.business_group_id,
806 a_booking.date_booking_placed,
807 a_booking.object_version_number,
808 a_booking.sign_eval_status,
809 p_date,
810 'RENEWING',
811 p_failed);
812 end if;
813 elsif(a_booking.successful_attendance_flag is null or a_booking.successful_attendance_flag<>'Y') then --this is the normal case where the learner is playing this course for the first time or
814 update_enrollment( --there is no signature and he is playing it after failing the course.
815 a_booking.booking_id,
816 a_booking.event_id,
817 a_booking.business_group_id,
818 a_booking.date_booking_placed,
819 a_booking.object_version_number,
820 a_booking.sign_eval_status,
821 p_date,
822 l_status,
823 p_failed);
824 end if;
825
826 end loop;
827 else
828 for a_booking in party_bookings(p_lo_id, p_user_id) loop
829 l_member_in := get_member_in(a_booking.event_id, a_booking.delegate_contact_id,null);
830 if(instr(l_member_in,'CERT')<>0 and a_booking.eres_enabled = 'Y' and a_booking.type = 'A' and a_booking.sign_eval_status = 'DD')then
831 open csr_contact_cert_status(a_booking.event_id, a_booking.delegate_contact_id);
832 fetch csr_contact_cert_status into l_cert_status;
833 close csr_contact_cert_status;
834 if(l_cert_status = 'RENEWING') then
835 update_enrollment(
836 a_booking.booking_id,
837 a_booking.event_id,
838 a_booking.business_group_id,
839 a_booking.date_booking_placed,
840 a_booking.object_version_number,
841 a_booking.sign_eval_status,
842 p_date,
843 'RENEWING',
844 p_failed);
845 end if;
846 -- Modified for --Bug 12857480 - r.tst122: for online course, evaluation icon always disabled for customer logins
847 -- elsif(a_booking.successful_attendance_flag<>'Y') then
848 elsif(a_booking.successful_attendance_flag is null or a_booking.successful_attendance_flag<>'Y') then
849 --this is the normal case where the learner is playing this course for the first time or
850 --there is no signature and he is playing it after failing the course.
851 update_enrollment(
852 a_booking.booking_id,
853 a_booking.event_id,
854 a_booking.business_group_id,
855 a_booking.date_booking_placed,
856 a_booking.object_version_number,
857 a_booking.sign_eval_status,
858 p_date,
859 l_status,
860 p_failed);
861 end if;
862 end loop;
863 end if;
864 hr_utility.set_location('Exiting:'|| l_proc, 15);
865 end update_enroll_status_for_lo;
866
867
868
869
870 procedure set_performance_lesson_status(
871 p_lo_id ota_learning_objects.learning_object_id%type,
872 p_user_id fnd_user.user_id%type,
873 p_user_type ota_attempts.user_type%type,
874 p_lesson_status ota_performances.lesson_status%type,
875 p_cascaded boolean,
876 p_date date,
877 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
878 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
879
880 cursor affected_los(
881 p_lo_id ota_learning_objects.learning_object_id%type,
882 p_user_id fnd_user.user_id%type,
883 p_user_type ota_attempts.user_type%type,
884 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
885 select parent.learning_object_id as learning_object_id,
886 nvl(perf.lesson_status, 'N') as lesson_status,
887 parent.starting_url as starting_url
888 from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
889 where child.source_learning_object_id = p_lo_id and
890 child.parent_learning_object_id = parent.learning_object_id and
891 parent.learning_object_id = parent.source_learning_object_id and
892 parent.starting_url is null and
893 perf.user_id(+) = p_user_id and
894 perf.user_type(+) = p_user_type and
895 perf.learning_object_id(+) = parent.learning_object_id and
896 nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1) and
897 nvl(perf.scorm_learner_attempt_id(+),-1) = nvl(p_scorm_learner_attempt_id, -1);
898 /* Completion requirements not yet implemented - GDHUTTON 12/24/03
899 union
900 select cr.child_learning_object_id as learning_object_id,
901 nvl(perf.lesson_status, 'N') as lesson_status,
902 lo.starting_url as starting_url
903 from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo
904 where cr.parent_learning_object_id = p_lo_id and
905 cr.child_learning_object_id = lo.learning_object_id and
906 perf.learning_object_id(+) = cr.child_learning_object_id and
907 perf.user_id(+) = p_user_id and
908 perf.user_type(+) = p_user_type and
909 nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
910 */
911 v_completed_date ota_performances.completed_date%type;
912 v_source_lo_id ota_learning_objects.learning_object_id%type;
913 v_business_group_id ota_learning_objects.business_group_id%type;
914 v_old_lesson_status ota_performances.lesson_status%type;
915 v_new_lesson_status ota_performances.lesson_status%type;
916 v_performance_source ota_performances.source%type;
917 begin
918 -- The performance is actually stored on the source learning object, so get that ID.
919 -- Also get the business group in case we have to create a new performance record.
920 select source_learning_object_id, business_group_id
921 into v_source_lo_id, v_business_group_id
922 from ota_learning_objects
923 where learning_object_id = p_lo_id;
924
925 begin
926 -- Find the old status and completed date.
927 select p.lesson_status, p.completed_date
928 into v_old_lesson_status, v_completed_date
929 from ota_performances p
930 where p.learning_object_id = v_source_lo_id and
931 p.user_id = p_user_id and
932 p.user_type = p_user_type and
933 nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1) and
934 nvl(p.scorm_learner_attempt_id, -1) = nvl(p_scorm_learner_attempt_id, -1);
935
936 -- Check if any work needs to be done
937 if (not p_cascaded and p_lesson_status <> v_old_lesson_status) or
938 lesson_status_transition_valid(v_old_lesson_status, p_lesson_status) then
939
940 -- See if the completed_date should change.
941 if v_old_lesson_status <> 'P' and v_old_lesson_status <> 'C' and
942 (p_lesson_status = 'P' or p_lesson_status = 'C' ) then
943 v_completed_date := p_date;
944 elsif (v_old_lesson_status = 'P' or v_old_lesson_status = 'C') and
945 p_lesson_status <> 'P' and p_lesson_status <> 'C' then
946 -- With current rules, this should never happen.
947 v_completed_date := null;
948 end if;
949
950 if p_cascaded then
951 v_performance_source := 'CASCADE';
952 else
953 v_performance_source := 'ATTEMPT';
954 end if;
955
956 update ota_performances
957 set lesson_status = p_lesson_status,
958 completed_date = v_completed_date,
959 last_updated_by = p_user_id,
960 last_update_date = p_date,
961 source = v_performance_source,
962 overridden_by = null,
963 overridden_date = null
964 where user_id = p_user_id and
965 user_type = p_user_type and
966 learning_object_id = v_source_lo_id and
967 nvl(cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1) and
968 nvl(scorm_learner_attempt_id, -1) = nvl(p_scorm_learner_attempt_id, -1);
969
970 -- Fix for 13965710
971 if v_completed_date = p_date then
972 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
973 if p_cert_prd_enroll_id is not null then
974 update_cme_status_for_lo(p_lo_id, p_date, p_cert_prd_enroll_id);
975 end if;
976 elsif p_lesson_status = 'F' then
977 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date, 'Y');
978 end if;
979 -- Fix for 13965710
980
981
982
983
984 --6777581.Added to update the enrollment status of learners who have already completed the learning object.
985 elsif (p_lesson_status = 'P' or p_lesson_status = 'C')and (p_lesson_status = v_old_lesson_status) then
986 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
987 -- Since we did not actually change anything, we just return
988 -- so that we don't try to cascade the non-change.
989 return;
990 end if;
991
992 exception
993 when NO_DATA_FOUND then
994 if p_lesson_status <> 'N' then
995
996 -- See if the completed_date should be filled in.
997 if p_lesson_status = 'P' or p_lesson_status = 'C' then
998 v_completed_date := p_date;
999 update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
1000 if p_cert_prd_enroll_id is not null then
1001 update_cme_status_for_lo(v_source_lo_id, p_date, p_cert_prd_enroll_id);
1002 end if;
1003 else
1004 v_completed_date := null;
1005 end if;
1006
1007 if p_cascaded then
1008 v_performance_source := 'CASCADE';
1009 else
1010 v_performance_source := 'ATTEMPT';
1011 end if;
1012
1013 -- Insert a new ota_performances record.
1014 insert into ota_performances
1015 (performance_id, user_id, user_type, learning_object_id,
1016 lesson_status, score, time, completed_date,
1017 created_by, creation_date, last_updated_by, last_update_date,
1018 source, object_version_number, business_group_id, cert_prd_enrollment_id)
1019 values
1020 (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
1021 p_lesson_status, -1000, -1001, v_completed_date,
1022 p_user_id, p_date, p_user_id, p_date,
1023 v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
1024 else
1025 return;
1026 end if;
1027 end;
1028
1029 -- Cascade the change to any affected RCOs. These include parents of this
1030 -- ota_learning_objects, plus those that indicate this ota_learning_objects as a completion
1031 -- requirement.
1032 for a_lo in affected_los(v_source_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
1033 -- if the current status is Passed, nothing can override it, so we will
1034 -- save the effort of computing the new status
1035 if a_lo.lesson_status <> 'P' then
1036 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, p_scorm_learner_attempt_id);
1037 if lesson_status_transition_valid(a_lo.lesson_status, v_new_lesson_status) then
1038 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, p_scorm_learner_attempt_id);
1039 end if;
1040 end if;
1041 end loop;
1042 end set_performance_lesson_status;
1043
1044 --13485033
1045 procedure set_performance_lesson_status(
1046 p_lo_id ota_learning_objects.learning_object_id%type,
1047 p_user_id fnd_user.user_id%type,
1048 p_user_type ota_attempts.user_type%type,
1049 p_lesson_status ota_performances.lesson_status%type,
1050 p_cascaded varchar2,
1051 p_date date,
1052 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1053 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1054 l_cascaded boolean := false;
1055 begin
1056 if(p_cascaded = 'Y') then
1057 l_cascaded:=true;
1058 end if;
1059 set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, l_cascaded, p_date, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1060 end set_performance_lesson_status;
1061
1062
1063
1064 procedure set_performance_lesson_status(
1065 p_lo_id ota_learning_objects.learning_object_id%type,
1066 p_user_id fnd_user.user_id%type,
1067 p_user_type ota_attempts.user_type%type,
1068 p_lesson_status ota_performances.lesson_status%type,
1069 p_date date,
1070 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1071 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1072 begin
1073 set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, false, p_date, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1074 end set_performance_lesson_status;
1075
1076
1077 procedure set_performance_lesson_status(
1078 p_lo_id ota_learning_objects.learning_object_id%type,
1079 p_user_id fnd_user.user_id%type,
1080 p_user_type ota_attempts.user_type%type,
1081 p_lesson_status ota_performances.lesson_status%type,
1082 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1083 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1084 begin
1085 set_performance_lesson_status(p_lo_id, p_user_id, p_user_type, p_lesson_status, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1086 end set_performance_lesson_status;
1087
1088
1089 procedure set_performance_time(
1090 p_lo_id ota_learning_objects.learning_object_id%type,
1091 p_user_id fnd_user.user_id%type,
1092 p_user_type ota_attempts.user_type%type,
1093 p_time ota_performances.time%type,
1094 p_cascaded boolean,
1095 p_date date,
1096 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1097 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1098
1099 cursor parent_los(
1100 p_lo_id ota_learning_objects.learning_object_id%type,
1101 p_user_id fnd_user.user_id%type,
1102 p_user_type ota_attempts.user_type%type,
1103 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1104 select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
1105 from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
1106 where child.source_learning_object_id = p_lo_id and
1107 child.parent_learning_object_id = parent.learning_object_id and
1108 parent.learning_object_id = parent.source_learning_object_id and
1109 parent.starting_url is null and
1110 perf.user_id(+) = p_user_id and
1111 perf.user_type(+) = p_user_type and
1112 perf.learning_object_id(+) = parent.learning_object_id and
1113 nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1) and
1114 nvl(perf.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
1115
1116 cursor distinct_child_los(
1117 p_lo_id ota_learning_objects.learning_object_id%type,
1118 p_user_id fnd_user.user_id%type,
1119 p_user_type ota_attempts.user_type%type,
1120 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type) is
1121 select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
1122 from ota_performances p,
1123 (select distinct learning_object_id, source_learning_object_id, starting_url
1124 from ota_learning_objects
1125 where learning_object_id <> p_lo_id
1126 start with learning_object_id = p_lo_id
1127 connect by parent_learning_object_id = prior learning_object_id) lo
1128 where lo.starting_url is not null and
1129 p.learning_object_id(+) = lo.source_learning_object_id and
1130 p.user_id(+) = p_user_id and
1131 p.user_type(+) = p_user_type and
1132 nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1) and
1133 nvl(p.scorm_learner_attempt_id(+), -1) = nvl(p_scorm_learner_attempt_id, -1);
1134
1135 v_source_lo_id ota_learning_objects.learning_object_id%type;
1136 v_business_group_id ota_learning_objects.business_group_id%type;
1137 v_old_time ota_performances.time%type;
1138 v_new_time ota_performances.time%type;
1139 v_performance_source ota_performances.source%type;
1140 begin
1141 -- The performance is actually stored on the source learning object, so get that ID.
1142 -- Also get the business group in case we have to create a new performance record.
1143 select source_learning_object_id, business_group_id
1144 into v_source_lo_id, v_business_group_id
1145 from ota_learning_objects
1146 where learning_object_id = p_lo_id;
1147
1148 begin
1149 select p.time
1150 into v_old_time
1151 from ota_performances p
1152 where p.learning_object_id = v_source_lo_id and
1153 p.user_id = p_user_id and
1154 p.user_type = p_user_type and
1155 nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1) and
1156 nvl(p.scorm_learner_attempt_id, -1) = nvl(p_scorm_learner_attempt_id, -1);
1157
1158 if p_time <> v_old_time then
1159 update ota_performances
1160 set time = p_time,
1161 last_updated_by = p_user_id,
1162 last_update_date = p_date
1163 where user_id = p_user_id and
1164 user_type = p_user_type and
1165 learning_object_id = v_source_lo_id and
1166 nvl(cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1) and
1167 nvl(scorm_learner_attempt_id, -1) = nvl(p_scorm_learner_attempt_id, -1);
1168 else
1169 return;
1170 end if;
1171 exception
1172 when NO_DATA_FOUND then
1173 if p_time <> 0 then
1174
1175 if p_cascaded then
1176 v_performance_source := 'CASCADE';
1177 else
1178 v_performance_source := 'ATTEMPT';
1179 end if;
1180
1181 insert into ota_performances
1182 (performance_id, user_id, user_type, learning_object_id,
1183 lesson_status, score, time,
1184 created_by, creation_date, last_updated_by, last_update_date,
1185 source, object_version_number, business_group_id, cert_prd_enrollment_id)
1186 values
1187 (ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
1188 'N', -1000, p_time,
1189 p_user_id, p_date, p_user_id, p_date,
1190 v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
1191 else
1192 return;
1193 end if;
1194 end;
1195
1196 -- Get all the source-parents of all the targets of the ota_learning_objects
1197 for a_parent_lo in parent_los(p_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
1198 v_new_time := 0;
1199 for a_child_lo in distinct_child_los(a_parent_lo.learning_object_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
1200 if a_child_lo.time > 0 then
1201 v_new_time := v_new_time + a_child_lo.time;
1202 end if;
1203 end loop;
1204
1205 if v_new_time <> a_parent_lo.time then
1206 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, p_scorm_learner_attempt_id);
1207 end if;
1208 end loop;
1209 end set_performance_time;
1210
1211
1212 procedure set_performance_time(
1213 p_lo_id ota_learning_objects.learning_object_id%type,
1214 p_user_id fnd_user.user_id%type,
1215 p_user_type ota_attempts.user_type%type,
1216 p_time ota_performances.time%type,
1217 p_date date,
1218 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1219 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1220 begin
1221 set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, false, p_date, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1222 end set_performance_time;
1223
1224
1225 procedure set_performance_time(
1226 p_lo_id ota_learning_objects.learning_object_id%type,
1227 p_user_id fnd_user.user_id%type,
1228 p_user_type ota_attempts.user_type%type,
1229 p_time ota_performances.time%type,
1230 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type,
1231 p_scorm_learner_attempt_id ota_performances.scorm_learner_attempt_id%type) is
1232 begin
1233 set_performance_time(p_lo_id, p_user_id, p_user_type, p_time, sysdate, p_cert_prd_enroll_id, p_scorm_learner_attempt_id);
1234 end set_performance_time;
1235
1236
1237 function lo_is_attemptable(
1238 p_lo_id ota_learning_objects.learning_object_id%type,
1239 p_launch_type ota_attempts.launch_type%type,
1240 p_reason out nocopy number) return boolean is
1241
1242 v_published_flag ota_learning_objects.published_flag%type;
1243 v_starting_url ota_learning_objects.starting_url%type;
1244 v_start_date_active ota_learning_objects.start_date_active%type;
1245 v_end_date_active ota_learning_objects.end_date_active%type;
1246
1247 begin
1248 select published_flag,
1249 starting_url,
1250 start_date_active,
1251 end_date_active
1252 into v_published_flag,
1253 v_starting_url,
1254 v_start_date_active,
1255 v_end_date_active
1256 from ota_learning_objects
1257 where learning_object_id = p_lo_id;
1258
1259 if v_published_flag = 'N' then
1260 p_reason := LO_REASON_NOT_PUBLISHED;
1261 elsif v_starting_url is null then
1262 p_reason := LO_REASON_NO_STARTING_URL;
1263 elsif v_start_date_active is not null and sysdate < v_start_date_active then
1264 p_reason := LO_REASON_NOT_STARTED;
1265 elsif v_end_date_active is not null and v_end_date_active < sysdate then
1266 p_reason := LO_REASON_EXPIRED;
1267 else
1268 p_reason := LO_REASON_NO_REASON;
1269 end if;
1270
1271 return (p_reason = LO_REASON_NO_REASON);
1272 exception
1273 when NO_DATA_FOUND then
1274 p_reason := LO_REASON_NO_SUCH_LO;
1275 return false;
1276 end lo_is_attemptable;
1277
1278
1279 function lo_is_in_event(
1280 p_event_id ota_events.event_id%type,
1281 p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
1282
1283 v_dummy char(1);
1284 v_root_lo_id ota_learning_objects.learning_object_id%type;
1285 begin
1286 select o.learning_object_id
1287 into v_root_lo_id
1288 from ota_events e, ota_offerings o
1289 where e.event_id = p_event_id and
1290 e.parent_offering_id = o.offering_id;
1291
1292 select 'X'
1293 into v_dummy
1294 from ota_learning_objects
1295 where learning_object_id = p_lo_id
1296 start with learning_object_id = v_root_lo_id
1297 connect by parent_learning_object_id = prior learning_object_id;
1298
1299 return true;
1300 exception
1301 when NO_DATA_FOUND then
1302 return false;
1303 end lo_is_in_event;
1304
1305
1306 function user_meets_prerequisites(
1307 p_lo_id ota_learning_objects.learning_object_id%type,
1308 p_user_id fnd_user.user_id%type,
1309 p_user_type ota_attempts.user_type%type) return boolean is
1310
1311 --Commented for Bug#3582893
1312 -- The cursor below is replaced with two cursor since the below query causes a Full Table Scan
1313 /*
1314 cursor csr_chk_prereqs is
1315 select 1
1316 from ota_prerequisites preq,
1317 ota_performances perf
1318 where preq.parent_object_id = p_lo_id and
1319 preq.object_id = perf.learning_object_id(+) and
1320 perf.user_id(+) = p_user_id and
1321 perf.user_type(+) = p_user_type and
1322 nvl(perf.lesson_status, 'N') not in ('P', 'C');
1323
1324 l_found number;
1325 l_return boolean;
1326 begin
1327 open csr_chk_prereqs;
1328 fetch csr_chk_prereqs into l_found;
1329 l_return := csr_chk_prereqs%notfound;
1330 close csr_chk_prereqs;
1331 return l_return;
1332 */
1333 CURSOR csr_get_prereqs IS
1334 SELECT object_id
1335 FROM ota_prerequisites
1336 where parent_object_id = p_lo_id
1337 and parent_type = 'LO';
1338
1339 CURSOR csr_get_performances(csr_lo_id NUMBER) IS
1340 SELECT 1
1341 FROM ota_performances
1342 WHERE learning_object_id = csr_lo_id
1343 AND user_id = p_user_id
1344 AND user_type = p_user_type
1345 AND lesson_status in ('P', 'C');
1346
1347 l_found number;
1348 l_return boolean := TRUE;
1349 begin
1350 FOR rec IN csr_get_prereqs
1351 LOOP
1352 IF csr_get_performances%ISOPEN THEN
1353 CLOSE csr_get_performances;
1354 END IF;
1355 OPEN csr_get_performances(rec.object_id);
1356 FETCH csr_get_performances INTO l_found;
1357 IF csr_get_performances%NOTFOUND THEN
1358 l_return := FALSE;
1359 CLOSE csr_get_performances;
1360 exit;
1361 END IF;
1362 END LOOP;
1363 IF csr_get_performances%ISOPEN THEN
1364 CLOSE csr_get_performances;
1365 END IF;
1366 return l_return;
1367 end user_meets_prerequisites;
1368
1369
1370 function user_exceeded_attempt_limit(
1371 p_lo_id ota_learning_objects.learning_object_id%type,
1372 p_user_id fnd_user.user_id%type,
1373 p_user_type ota_attempts.user_type%type,
1374 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1375
1376 v_max_attempts ota_tests.max_attempts%type;
1377 v_user_attempts number;
1378 begin
1379 begin
1380 select t.max_attempts
1381 into v_max_attempts
1382 from ota_tests t, ota_learning_objects lo
1383 where lo.learning_object_id = p_lo_id and
1384 lo.test_id = t.test_id;
1385 exception
1386 when NO_DATA_FOUND then -- the LO is not a test, so no attempt limit exists
1387 return false;
1388 end;
1389
1390 -- The LO is a test... if no max attempts return false immediately.
1391 if v_max_attempts is null then
1392 return false;
1393 else
1394 -- Count the user's attempts
1395 begin
1396 select count(*)
1397 into v_user_attempts
1398 from ota_attempts a, ota_tests t
1399 where a.user_id = p_user_id and
1400 a.user_type = p_user_type and
1401 a.learning_object_id = p_lo_id and
1402 nvl(a.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enrollment_id, -1) and
1403 a.test_id = t.test_id and
1404 a.initialized_flag = 'Y' and
1405 a.attempt_type <> 'I' and
1406 ((a.suspend_data is null and
1407 t.resume_flag = 'Y' and
1408 a.internal_state <> 'A' and
1409 (a.suspend_data is null or a.suspend_data <> 'I')) or
1410 (t.resume_flag = 'N' and (a.suspend_data is null or a.suspend_data <> 'I')));
1411 exception
1412 when NO_DATA_FOUND then
1413 -- No attempts, can't have exceeded the limit
1414 return false;
1415 end;
1416
1417 return v_user_attempts >= v_max_attempts;
1418 end if;
1419 end user_exceeded_attempt_limit;
1420
1421
1422 function get_next_attempt_date_for_lo(
1423 p_lo_id ota_learning_objects.learning_object_id%type,
1424 p_user_id fnd_user.user_id%type,
1425 p_user_type ota_attempts.user_type%type,
1426 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return date is
1427
1428 v_earliest_attempt_date date;
1429 begin
1430 select nvl(max(a.timestamp), sysdate) + nvl(t.duration_between_attempt, 0)
1431 into v_earliest_attempt_date
1432 from ota_tests t,
1433 ota_attempts a
1434 where t.test_id = a.test_id
1435 and a.initialized_flag = 'Y'
1436 and a.internal_state <> 'A' --14598945
1437 and ((t.resume_flag = 'Y'
1438 and (a.suspend_data is null)) --16055590
1439 OR (t.resume_flag = 'N'
1440 and (a.suspend_data is null or a.suspend_data <> 'I')))
1441 and a.learning_object_id = p_lo_id
1442 and a.user_id = p_user_id
1443 and a.user_type = p_user_type
1444 and nvl(p_cert_prd_enrollment_id, -1) = nvl(a.cert_prd_enrollment_id, -1)
1445 group by t.test_id,t.max_attempts, t.duration_between_attempt;
1446
1447 return v_earliest_attempt_date;
1448 exception
1449 /* this is NOT a test, or there are no attempts */
1450 when NO_DATA_FOUND then
1451 return null;
1452
1453 when others then
1454 return null;
1455 end get_next_attempt_date_for_lo;
1456
1457
1458 function user_must_wait_to_attempt(
1459 p_lo_id ota_learning_objects.learning_object_id%type,
1460 p_user_id fnd_user.user_id%type,
1461 p_user_type ota_attempts.user_type%type,
1462 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1463
1464 v_earliest_attempt_date date;
1465 begin
1466 v_earliest_attempt_date := get_next_attempt_date_for_lo(
1467 p_lo_id,
1468 p_user_id,
1469 p_user_type,
1470 p_cert_prd_enrollment_id);
1471
1472 return (v_earliest_attempt_date is not null and sysdate < v_earliest_attempt_date);
1473 end user_must_wait_to_attempt;
1474
1475
1476 function user_can_attempt_lo(
1477 p_lo_id ota_learning_objects.learning_object_id%type,
1478 p_user_id fnd_user.user_id%type,
1479 p_user_type ota_attempts.user_type%type,
1480 p_launch_type ota_attempts.launch_type%type,
1481 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1482 p_reason out nocopy number) return boolean is
1483 begin
1484 if not lo_is_attemptable(p_lo_id, p_launch_type, p_reason) then
1485 return false;
1486 end if;
1487
1488 -- Instructors need not meet prerequisites because their performance is never
1489 -- recorded (therefore they CAN'T meet them). Also, instructors are not subject
1490 -- to attempt limits or waits between attempts, although their attempts would
1491 -- never be counted anyway.
1492 if p_launch_type = 'INSTRUCTOR' then
1493 return true;
1494 else
1495 if not user_meets_prerequisites(p_lo_id, p_user_id, p_user_type) then
1496 p_reason := LO_REASON_PREREQS_NOT_MET;
1497 return false;
1498 end if;
1499
1500 if user_exceeded_attempt_limit(p_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
1501 p_reason := LO_REASON_ATTEMPTS_EXCEEDED;
1502 return false;
1503 end if;
1504
1505 if user_must_wait_to_attempt(p_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
1506 p_reason := LO_REASON_DURATION_NOT_MET;
1507 return false;
1508 end if;
1509 end if;
1510
1511 return true;
1512 end user_can_attempt_lo;
1513
1514
1515 function user_can_attempt_lo(
1516 p_lo_id ota_learning_objects.learning_object_id%type,
1517 p_user_id fnd_user.user_id%type,
1518 p_user_type ota_attempts.user_type%type,
1519 p_launch_type ota_attempts.launch_type%type default '',
1520 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
1521
1522 l_reason number;
1523 begin
1524 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
1525 return 'Y';
1526 else
1527 return 'N';
1528 end if;
1529 end user_can_attempt_lo;
1530
1531
1532 function user_can_attempt_lo_3(
1533 p_lo_id ota_learning_objects.learning_object_id%type,
1534 p_event_id ota_events.event_id%type,
1535 p_user_id fnd_user.user_id%type,
1536 p_user_type ota_attempts.user_type%type,
1537 p_launch_type ota_attempts.launch_type%type,
1538 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
1539 p_reason out nocopy number) return boolean is
1540 begin
1541 if not lo_is_in_event(p_event_id, p_lo_id) then
1542 p_reason := LO_REASON_NOT_IN_EVENT;
1543 return false;
1544 end if;
1545
1546 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
1547 return false;
1548 end if;
1549
1550 return true;
1551 end user_can_attempt_lo_3;
1552
1553 function lo_is_in_cert(
1554 p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type,
1555 p_lo_id ota_learning_objects.learning_object_id%type) return boolean is
1556
1557 cursor root_los(p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
1558 select o.learning_object_id
1559 from ota_offerings o, ota_certification_members cm, ota_cert_mbr_enrollments cme
1560 where o.activity_version_id = cm.object_id
1561 and cm.certification_member_id = cme.cert_member_id
1562 and cme.cert_prd_enrollment_id = p_cert_prd_enroll_id;
1563
1564 cursor child_los(p_lo_id ota_learning_objects.learning_object_id%type,
1565 p_root_lo_id ota_learning_objects.learning_object_id%type) is
1566 select learning_object_id
1567 from ota_learning_objects
1568 where learning_object_id = p_lo_id
1569 start with learning_object_id = p_root_lo_id
1570 connect by parent_learning_object_id = prior learning_object_id;
1571
1572 begin
1573
1574 for a_root_lo in root_los(p_cert_prd_enroll_id) loop
1575 for a_child_lo in child_los(p_lo_id, a_root_lo.learning_object_id) loop
1576 return true;
1577 end loop;
1578 end loop;
1579
1580 return false;
1581
1582 end lo_is_in_cert;
1583
1584 function user_can_attempt_lo(
1585 p_lo_id ota_learning_objects.learning_object_id%type,
1586 p_event_id ota_events.event_id%type,
1587 p_user_id fnd_user.user_id%type,
1588 p_user_type ota_attempts.user_type%type,
1589 p_launch_type ota_attempts.launch_type%type,
1590 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
1591 p_reason out nocopy number) return boolean is
1592 begin
1593 if p_cert_prd_enroll_id is not null and not lo_is_in_cert(p_cert_prd_enroll_id, p_lo_id) then
1594 p_reason := LO_REASON_LO_NOT_IN_CERT;
1595 return false;
1596 end if;
1597
1598 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
1599 return false;
1600 end if;
1601
1602 return true;
1603 end user_can_attempt_lo;
1604
1605 function event_is_attemptable(
1606 p_event_id ota_events.event_id%type,
1607 p_date date,
1608 p_launch_type ota_attempts.launch_type%type,
1609 p_reason out nocopy number) return boolean is
1610
1611 v_course_start_date ota_events.course_start_date%type;
1612 v_course_start_time ota_events.course_start_time%type;
1613 v_course_end_date ota_events.course_end_date%type;
1614 v_course_end_time ota_events.course_end_time%type;
1615 v_is_instructor boolean;
1616 v_course_started boolean;
1617 v_course_expired boolean;
1618 begin
1619 p_reason := EVENT_REASON_NO_REASON;
1620
1621 select course_start_date, course_start_time, course_end_date, course_end_time
1622 into v_course_start_date, v_course_start_time, v_course_end_date, v_course_end_time
1623 from ota_events
1624 where event_id = p_event_id;
1625
1626 v_course_started := (v_course_start_date is null or p_date >= v_course_start_date);
1627 v_course_expired := (v_course_end_date is not null and p_date > v_course_end_date);
1628 v_is_instructor := (p_launch_type = 'INSTRUCTOR');
1629
1630 -- Neither learners not instructors can play a course past its end date.
1631 if v_course_expired then
1632 p_reason := EVENT_REASON_EXPIRED;
1633
1634 -- Only instructors can play a course before it starts.
1635 elsif not v_course_started and not v_is_instructor then
1636 p_reason := EVENT_REASON_NOT_STARTED;
1637
1638 end if;
1639
1640 return (p_reason = EVENT_REASON_NO_REASON);
1641 exception
1642 when NO_DATA_FOUND then
1643 p_reason := EVENT_REASON_NO_SUCH_EVENT;
1644 return false;
1645 end event_is_attemptable;
1646
1647
1648 function user_is_enrolled(
1649 p_event_id ota_events.event_id%type,
1650 p_user_id fnd_user.user_id%type,
1651 p_user_type ota_attempts.user_type%type,
1652 p_date date,
1653 p_reason out nocopy number) return boolean is
1654
1655 cursor person_bookings(
1656 p_event_id ota_events.event_id%type,
1657 p_person_id per_all_people.person_id%type) is
1658 select 1
1659 from ota_delegate_bookings book,
1660 ota_booking_status_types stype
1661 where book.delegate_person_id = p_person_id and
1662 book.event_id = p_event_id and
1663 book.booking_status_type_id = stype.booking_status_type_id and
1664 stype.type in ('P', 'A' ,'E');
1665
1666 cursor contact_bookings(
1667 p_event_id ota_events.event_id%type,
1668 p_contact_id per_all_people.person_id%type) is
1669 select 1
1670 from ota_delegate_bookings book,
1671 ota_booking_status_types stype
1672 where book.delegate_contact_id = p_contact_id and
1673 book.event_id = p_event_id and
1674 book.booking_status_type_id = stype.booking_status_type_id and
1675 stype.type in ('P', 'A', 'E');
1676
1677 cursor get_delegate_contact(
1678 p_party_id hz_parties.party_id%type) is
1679 select acct_role.cust_account_role_id
1680 from hz_cust_account_roles acct_role,
1681 hz_relationships rel,
1682 hz_cust_accounts role_acct
1683 where acct_role.party_id = rel.party_id and
1684 acct_role.role_type = 'CONTACT' and
1685 acct_role.cust_account_id = role_acct.cust_account_id and
1686 role_acct.party_id = rel.object_id and
1687 rel.subject_id = p_party_id and
1688 rel.subject_table_name = 'HZ_PARTIES' and
1689 rel.object_table_name = 'HZ_PARTIES';
1690
1691 a_contact get_delegate_contact%ROWTYPE;
1692 begin
1693 p_reason := EVENT_REASON_NO_REASON;
1694
1695 if p_user_type = 'E' then
1696 for a_booking in person_bookings(p_event_id, p_user_id) loop
1697 return true;
1698 end loop;
1699 else
1700 for a_contact in get_delegate_contact(p_user_id) loop
1701 for a_booking in contact_bookings(p_event_id,nvl(a_contact.cust_account_role_id, get_contact_id_for_party(p_user_id))) loop
1702 return true;
1703 end loop;
1704 end loop;
1705 end if;
1706
1707 -- If we fall through, the user is not enrolled
1708 p_reason := EVENT_REASON_NOT_ENROLLED;
1709 return false;
1710 end user_is_enrolled;
1711
1712
1713 function user_is_instructor(
1714 p_event_id ota_events.event_id%type,
1715 p_user_id fnd_user.user_id%type,
1716 p_user_type ota_attempts.user_type%type,
1717 p_date date,
1718 p_reason out nocopy number) return boolean is
1719 begin
1720
1721 if p_user_type = 'E' then
1722
1723 for an_instructor in (
1724 select 1 as dummy
1725 from
1726 ota_suppliable_resources tsr,
1727 ota_resource_bookings trb
1728 where
1729 trb.forum_id is null and
1730 trb.chat_id is null and
1731 tsr.trainer_id = p_user_id and
1732 trb.supplied_resource_id = tsr.supplied_resource_id and
1733 (trb.event_id = p_event_id or
1734 trb.event_id in ( select ses.event_id
1735 from ota_events ses
1736 where ses.parent_event_id = p_event_id
1737 and ses.event_type = 'SESSION')
1738 ) and
1739 tsr.resource_type = 'T') loop
1740
1741 p_reason := EVENT_REASON_NO_REASON;
1742 return true;
1743 end loop;
1744
1745 end if;
1746
1747 -- If we fall through, the user is not an instructor.
1748 p_reason := EVENT_REASON_NOT_INSTRUCTOR;
1749 return false;
1750 end user_is_instructor;
1751
1752
1753 function user_can_attempt_event(
1754 p_event_id ota_events.event_id%type,
1755 p_user_id fnd_user.user_id%type,
1756 p_user_type ota_attempts.user_type%type,
1757 p_launch_type ota_attempts.launch_type%type,
1758 p_reason out nocopy number) return varchar2 is
1759
1760 v_now date := trunc(sysdate);--Bug 3554773
1761 begin
1762 if not event_is_attemptable(p_event_id, v_now, p_launch_type, p_reason) then
1763 return 'N';
1764 end if;
1765
1766 if p_launch_type = 'INSTRUCTOR' then
1767 if not user_is_instructor(p_event_id, p_user_id, p_user_type, v_now, p_reason) then
1768 return 'N';
1769 end if;
1770 else
1771 if not user_is_enrolled(p_event_id, p_user_id, p_user_type, v_now, p_reason) then
1772 return 'N';
1773 end if;
1774 end if;
1775
1776 p_reason := EVENT_REASON_NO_REASON;
1777 return 'Y';
1778 end user_can_attempt_event;
1779
1780
1781 function cert_is_attemptable(
1782 p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1783 p_date date,
1784 p_reason out nocopy number) return boolean is
1785
1786 v_cert_start_date ota_certifications_b.start_date_active%type;
1787 v_cert_end_date ota_certifications_b.end_date_active%type;
1788 v_cert_prd_enroll_start ota_cert_prd_enrollments.cert_period_start_date%type;
1789 v_cert_prd_enroll_end ota_cert_prd_enrollments.cert_period_end_date%type;
1790 begin
1791 select c.start_date_active, c.end_date_active, cpe.cert_period_start_date, cpe.cert_period_end_date
1792 into v_cert_start_date, v_cert_end_date, v_cert_prd_enroll_start, v_cert_prd_enroll_end
1793 from ota_certifications_b c, ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
1794 where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
1795 cpe.cert_enrollment_id = ce.cert_enrollment_id and
1796 ce.certification_id = c.certification_id;
1797
1798 if v_cert_start_date is not null and p_date < v_cert_start_date then
1799 p_reason := CERT_REASON_NOT_STARTED;
1800 elsif v_cert_end_date is not null and p_date > v_cert_end_date then
1801 p_reason := CERT_REASON_EXPIRED;
1802 elsif v_cert_prd_enroll_start is not null and p_date < v_cert_prd_enroll_start then
1803 p_reason := CERT_PRD_REASON_NOT_STARTED;
1804 elsif v_cert_prd_enroll_end is not null and p_date > v_cert_prd_enroll_end then
1805 p_reason := CERT_PRD_REASON_EXPIRED;
1806 else
1807 p_reason := CERT_REASON_NO_REASON;
1808 end if;
1809
1810 return (p_reason = CERT_REASON_NO_REASON);
1811 exception
1812 when NO_DATA_FOUND then
1813 p_reason := CERT_REASON_NO_SUCH_CERT;
1814 return false;
1815 end cert_is_attemptable;
1816
1817 function user_is_enrolled_in_cert(
1818 p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1819 p_user_id fnd_user.user_id%type,
1820 p_user_type ota_attempts.user_type%type,
1821 p_date date,
1822 p_reason out nocopy number) return boolean is
1823
1824 v_cert_unsubscribe_date ota_cert_enrollments.unenrollment_date%type;
1825 --v_cert_enr_user_id ota_cert_enrollments.person_id%type;
1826 v_cert_enr_user_id number;
1827 l_user_id number;
1828 begin
1829 select ce.unenrollment_date, nvl(ce.person_id, ce.contact_id)
1830 into v_cert_unsubscribe_date, v_cert_enr_user_id
1831 from ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
1832 where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
1833 cpe.cert_enrollment_id = ce.cert_enrollment_id;
1834
1835 --bug 4725513
1836 --for ext learner call get_contact_id_for_party(p_user_id)
1837 if p_user_type = 'E' then
1838 l_user_id := p_user_id;
1839 else
1840 l_user_id := get_contact_id_for_party(p_user_id);
1841 end if;
1842
1843 if v_cert_unsubscribe_date is not null then
1844 p_reason := CERT_REASON_UNSUBSCRIBED;
1845 elsif (v_cert_enr_user_id is not null) and (not v_cert_enr_user_id = l_user_id) then
1846 p_reason := CERT_REASON_INVALID_USER;
1847 else
1848 p_reason := CERT_REASON_NO_REASON;
1849 end if;
1850 return (p_reason = CERT_REASON_NO_REASON);
1851 exception
1852 when NO_DATA_FOUND then
1853 p_reason := CERT_REASON_NO_SUCH_CERT;
1854 return false;
1855 end user_is_enrolled_in_cert;
1856
1857 function user_can_attempt_cert(
1858 p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1859 p_user_id fnd_user.user_id%type,
1860 p_user_type ota_attempts.user_type%type,
1861 p_reason out nocopy number) return varchar2 is
1862
1863 v_now date := trunc(sysdate);--Bug 3554773
1864 begin
1865
1866 if not cert_is_attemptable(p_cert_prd_enroll_id, v_now, p_reason) then
1867 return 'N';
1868 end if;
1869
1870 if not user_is_enrolled_in_cert(p_cert_prd_enroll_id, p_user_id, p_user_type, v_now, p_reason) then
1871 return 'N';
1872 end if;
1873
1874 p_reason := CERT_REASON_NO_REASON;
1875 return 'Y';
1876 end user_can_attempt_cert;
1877
1878
1879 function find_previous_lo_id(
1880 p_start ota_learning_objects.learning_object_id%type,
1881 p_current ota_learning_objects.learning_object_id%type,
1882 p_current_starting_url ota_learning_objects.starting_url%type,
1883 p_previous in out nocopy ota_learning_objects.learning_object_id%type) return boolean is
1884
1885 cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1886 select learning_object_id, starting_url
1887 from ota_learning_objects
1888 where parent_learning_object_id = p_lo_id and
1889 published_flag = 'Y'
1890 order by child_seq asc;
1891 begin
1892 if p_current = p_start then
1893 return true;
1894 end if;
1895
1896 if p_current_starting_url is not null then
1897 p_previous := p_current;
1898 end if;
1899
1900 for a_child in children(p_current) loop
1901 if find_previous_lo_id(p_start, a_child.learning_object_id, a_child.starting_url, p_previous) then
1902 return true;
1903 end if;
1904 end loop;
1905
1906 return false;
1907 end find_previous_lo_id;
1908
1909
1910 function get_previous_lo_id(
1911 p_root_lo_id ota_learning_objects.learning_object_id%type,
1912 p_starting_lo_id ota_learning_objects.learning_object_id%type,
1913 p_user_id fnd_user.user_id%type,
1914 p_user_type ota_attempts.user_type%type,
1915 p_launch_type ota_attempts.launch_type%type,
1916 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1917
1918 v_starting_url ota_learning_objects.starting_url%type :=null;
1919 v_previous_lo_id ota_learning_objects.learning_object_id%type := null;
1920 v_reason number;
1921 begin
1922 select starting_url
1923 into v_starting_url
1924 from ota_learning_objects
1925 where learning_object_id = p_root_lo_id;
1926
1927 if find_previous_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_previous_lo_id) then
1928 if user_can_attempt_lo(v_previous_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id, v_reason) then
1929 return v_previous_lo_id;
1930 else
1931 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);
1932 end if;
1933 else
1934 return null;
1935 end if;
1936 end get_previous_lo_id;
1937
1938
1939 function get_previous_event_lo_id(
1940 p_event_id ota_events.event_id%type,
1941 p_starting_lo_id ota_learning_objects.learning_object_id%type,
1942 p_user_id fnd_user.user_id%type,
1943 p_user_type ota_attempts.user_type%type,
1944 p_launch_type ota_attempts.launch_type%type,
1945 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1946
1947 v_root_lo_id ota_learning_objects.learning_object_id%type;
1948 begin
1949 select o.learning_object_id
1950 into v_root_lo_id
1951 from ota_events e, ota_offerings o
1952 where e.event_id = p_event_id and
1953 e.parent_offering_id = o.offering_id;
1954
1955 return get_previous_lo_id(v_root_lo_id, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
1956 end get_previous_event_lo_id;
1957
1958
1959 function find_next_lo_id(
1960 p_start ota_learning_objects.learning_object_id%type,
1961 p_current ota_learning_objects.learning_object_id%type,
1962 p_current_starting_url ota_learning_objects.starting_url%type,
1963 p_found_start in out nocopy boolean) return ota_learning_objects.learning_object_id%type is
1964
1965 cursor children(p_lo_id ota_learning_objects.learning_object_id%type) is
1966 select learning_object_id, starting_url
1967 from ota_learning_objects
1968 where parent_learning_object_id = p_lo_id and
1969 published_flag = 'Y'
1970 order by child_seq asc;
1971 v_result ota_learning_objects.learning_object_id%type;
1972 begin
1973 if (p_found_start or p_start is null) and p_current_starting_url is not null then
1974 return p_current;
1975 end if;
1976
1977 if p_current = p_start then
1978 p_found_start := true;
1979 end if;
1980
1981 for a_child in children(p_current) loop
1982 v_result := find_next_lo_id(p_start, a_child.learning_object_id, a_child.starting_url, p_found_start);
1983 if v_result is not null then
1984 return v_result;
1985 end if;
1986 end loop;
1987
1988 return null;
1989 end find_next_lo_id;
1990
1991
1992 function get_next_lo_id(
1993 p_root_lo_id ota_learning_objects.learning_object_id%type,
1994 p_starting_lo_id ota_learning_objects.learning_object_id%type,
1995 p_user_id fnd_user.user_id%type,
1996 p_user_type ota_attempts.user_type%type,
1997 p_launch_type ota_attempts.launch_type%type,
1998 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
1999
2000
2001 v_starting_url ota_learning_objects.starting_url%type := null;
2002 v_next_lo_id ota_learning_objects.learning_object_id%type := null;
2003 v_false boolean := false;
2004 v_reason number;
2005 begin
2006 return get_next_lo_id(p_root_lo_id,null, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
2007 end get_next_lo_id;
2008
2009
2010
2011 function get_next_lo_id(
2012 p_root_lo_id ota_learning_objects.learning_object_id%type,
2013 p_root_starting_url ota_learning_objects.starting_url%type,
2014 p_starting_lo_id ota_learning_objects.learning_object_id%type,
2015 p_user_id fnd_user.user_id%type,
2016 p_user_type ota_attempts.user_type%type,
2017 p_launch_type ota_attempts.launch_type%type,
2018 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2019
2020 v_starting_url ota_learning_objects.starting_url%type := null;
2021 v_next_lo_id ota_learning_objects.learning_object_id%type := null;
2022 v_false boolean := false;
2023 v_reason number;
2024 begin
2025
2026 if p_root_starting_url is not null then
2027 v_starting_url := p_root_starting_url;
2028 else
2029 select starting_url
2030 into v_starting_url
2031 from ota_learning_objects
2032 where learning_object_id = p_root_lo_id;
2033 end if;
2034
2035 v_next_lo_id := find_next_lo_id(p_starting_lo_id, p_root_lo_id, v_starting_url, v_false);
2036 if v_next_lo_id is not null then
2037 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
2038 return v_next_lo_id;
2039 else
2040 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);
2041 end if;
2042 else
2043 return null;
2044 end if;
2045 end get_next_lo_id;
2046
2047
2048 function get_next_event_lo_id(
2049 p_event_id ota_events.event_id%type,
2050 p_starting_lo_id ota_learning_objects.learning_object_id%type,
2051 p_user_id fnd_user.user_id%type,
2052 p_user_type ota_attempts.user_type%type,
2053 p_launch_type ota_attempts.launch_type%type,
2054 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2055
2056 v_root_lo_id ota_learning_objects.learning_object_id%type;
2057 begin
2058 select o.learning_object_id
2059 into v_root_lo_id
2060 from ota_events e, ota_offerings o
2061 where e.event_id = p_event_id and
2062 e.parent_offering_id = o.offering_id;
2063
2064 return get_next_lo_id(v_root_lo_id, p_starting_lo_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
2065 end get_next_event_lo_id;
2066
2067
2068 function get_most_recent_lo_id(
2069 p_event_id ota_events.event_id%type,
2070 p_user_id fnd_user.user_id%type,
2071 p_user_type ota_attempts.user_type%type,
2072 p_launch_type ota_attempts.launch_type%type,
2073 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type is
2074
2075 cursor ordered_los(
2076 p_root_lo_id ota_learning_objects.learning_object_id%type,
2077 p_user_id fnd_user.user_id%type,
2078 p_user_type ota_attempts.user_type%type,
2079 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) is
2080 select a.learning_object_id, max(a.attempt_id) max_attempt
2081 from ota_attempts a,
2082 (select learning_object_id, starting_url
2083 from ota_learning_objects
2084 start with learning_object_id = p_root_lo_id
2085 connect by parent_learning_object_id = prior learning_object_id) lo
2086 where a.user_id = p_user_id and
2087 a.user_type = p_user_type and
2088 a.learning_object_id = lo.learning_object_id and
2089 lo.starting_url is not null and
2090 ((p_cert_prd_enroll_id is null and a.cert_prd_enrollment_id is null) OR
2091 (p_cert_prd_enroll_id is not null and a.cert_prd_enrollment_id = p_cert_prd_enroll_id)
2092 )
2093 group by a.learning_object_id
2094 order by max_attempt desc;
2095
2096 v_root_lo_id ota_learning_objects.learning_object_id%type;
2097 v_reason number;
2098 begin
2099 select o.learning_object_id
2100 into v_root_lo_id
2101 from ota_events e, ota_offerings o
2102 where e.event_id = p_event_id and
2103 e.parent_offering_id = o.offering_id;
2104
2105
2106 for a_lo in ordered_los(v_root_lo_id, p_user_id, p_user_type, p_cert_prd_enroll_id) loop
2107 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
2108 return a_lo.learning_object_id;
2109 end if;
2110 end loop;
2111
2112 return get_first_lo_id(p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id);
2113 end get_most_recent_lo_id;
2114
2115
2116 function get_first_lo_id(
2117 p_event_id ota_events.event_id%type,
2118 p_user_id fnd_user.user_id%type,
2119 p_user_type ota_attempts.user_type%type,
2120 p_launch_type ota_attempts.launch_type%type,
2121 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type is
2122
2123 v_root_lo_id ota_learning_objects.learning_object_id%type;
2124 v_first_lo_id ota_learning_objects.learning_object_id%type;
2125 v_reason number;
2126 begin
2127 select o.learning_object_id
2128 into v_root_lo_id
2129 from ota_events e, ota_offerings o
2130 where e.event_id = p_event_id and
2131 e.parent_offering_id = o.offering_id;
2132
2133 return get_next_lo_id(v_root_lo_id, null, p_user_id, p_user_type, p_launch_type, p_cert_prd_enrollment_id);
2134 end get_first_lo_id;
2135
2136 function get_jump_lo_id(
2137 p_lo_id ota_learning_objects.learning_object_id%type,
2138 p_event_id ota_events.event_id%type,
2139 p_user_id fnd_user.user_id%type,
2140 p_user_type ota_attempts.user_type%type,
2141 p_launch_type ota_attempts.launch_type%type,
2142 p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
2143 begin
2144 return get_jump_lo_id(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, null, p_reason);
2145 end get_jump_lo_id;
2146
2147 function get_jump_lo_id(
2148 p_lo_id ota_learning_objects.learning_object_id%type,
2149 p_event_id ota_events.event_id%type,
2150 p_user_id fnd_user.user_id%type,
2151 p_user_type ota_attempts.user_type%type,
2152 p_launch_type ota_attempts.launch_type%type,
2153 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
2154 p_reason out nocopy number) return ota_learning_objects.learning_object_id%type is
2155 begin
2156 if user_can_attempt_lo(p_lo_id, p_event_id, p_user_id, p_user_type, p_launch_type, p_cert_prd_enroll_id, p_reason) then
2157 return p_lo_id;
2158 else
2159 return null;
2160 end if;
2161 end get_jump_lo_id;
2162
2163 --7574667
2164 function get_lo_type(
2165 p_lo_id ota_learning_objects.learning_object_id%type) return varchar2 is
2166
2167 --7622768
2168 cursor lo_type is
2169 select nvl(test_type_flag,'LO')
2170 from ota_tests ot,
2171 ota_learning_objects lo
2172 where ot.test_id(+) = lo.test_id
2173 and lo.learning_object_id = p_lo_id;
2174
2175 type_flag varchar2(10);
2176 begin
2177 open lo_type;
2178 fetch lo_type into type_flag;
2179 close lo_type;
2180 if type_flag is null then
2181 return 'LO';
2182 end if;
2183 return type_flag;
2184
2185 end get_lo_type;
2186
2187
2188 function user_can_attempt_event(
2189 p_event_id ota_events.event_id%type,
2190 p_user_id fnd_user.user_id%type,
2191 p_user_type ota_attempts.user_type%type) return varchar2 is
2192
2193 l_reason number;
2194 begin
2195 return user_can_attempt_event(p_event_id, p_user_id, p_user_type, '', l_reason);
2196 end user_can_attempt_event;
2197
2198
2199 function user_can_attempt_event(
2200 p_event_id ota_events.event_id%type,
2201 p_user_id fnd_user.user_id%type,
2202 p_user_type ota_attempts.user_type%type,
2203 p_launch_type ota_attempts.launch_type%type) return varchar2 is
2204
2205 l_reason number;
2206 begin
2207 return user_can_attempt_event(p_event_id, p_user_id, p_user_type, p_launch_type, l_reason);
2208 end user_can_attempt_event;
2209
2210
2211 -- Author: sbhullar
2212 -- Author: sbhullar
2213 -- ----------------------------------------------------------------
2214 -- ------------------<get_lo_title_for_tree >--------------------
2215 -- ----------------------------------------------------------------
2216 -- {Start of Comments}
2217 --
2218 -- Description:
2219 -- This function will be used to show get lo name, status and time
2220 -- in the format lo_name [Status: status, Time: hh:mm:ss] if p_mode
2221 -- is 1 else it gives the lo status icon
2222 -- IN
2223 -- p_lo_id
2224 -- p_user_id
2225 -- p_user_type
2226 -- p_mode
2227 -- p_active_cert_flag
2228 --
2229 -- Post Failure:
2230 -- None.
2231 -- Access Status
2232 -- Public
2233 -- {End of Comments}
2234 ------------------------------------------------------------------
2235 FUNCTION get_lo_title_for_tree(p_lo_id IN NUMBER,
2236 p_user_id IN NUMBER,
2237 p_user_type IN ota_attempts.user_type%type,
2238 p_mode IN NUMBER default 1,
2239 p_active_cert_flag varchar2 default 'N',
2240 p_scorm_learning_attempt_id In ota_performances.scorm_learner_attempt_id%type default null)
2241 RETURN varchar2
2242 IS
2243 l_proc VARCHAR2(72) := g_package||'get_lo_title_for_tree';
2244 l_lo_name varchar(240);
2245 l_lo_status varchar(80);
2246 l_lo_time number;
2247 l_lo_score number;
2248 l_lo_status_icon varchar(30);
2249 l_Seconds number;
2250 l_Minutes number;
2251 l_Hours number;
2252 l_formatted_hour varchar(20) := '';
2253 l_formatted_min varchar(20) := '';
2254 l_formatted_sec varchar(20) := '';
2255 l_formatted_time varchar(20) := '';
2256 l_return_tree_title varchar(500) := '';
2257 l_max_performance_id ota_performances.performance_id%type;
2258 l_tst_grade_flag ota_tests.grade_flag%TYPE;
2259 l_var_score VARCHAR2(100);
2260 l_lo_completed_date VARCHAR2(100);
2261 l_lo_completed_time VARCHAR2(10);
2262 l_lo_completed_date_tz VARCHAR2(100);
2263 l_lo_lesson_status ota_performances.lesson_status%type;
2264 l_lo_completion_date_tz Date;
2265
2266 CURSOR c_get_lo_tree_link(p_performance_id in number) is
2267 Select
2268 olo.name Name,
2269 nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
2270 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
2271 nvl(opf.time,0) Time,
2272 opf.score Score,
2273 decode(opf.lesson_status
2274 ,'C','player_status_c.gif'
2275 ,'F','player_status_f.gif'
2276 ,'I','player_status_i.gif'
2277 ,'P','player_status_p.gif'
2278 ,'N','player_status_n.gif'
2279 ,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type),
2280 'N','player_status_no_prereq.gif',
2281 'Y','player_status_n.gif')
2282 ,'player_status_n.gif') STATUS_ICON
2283 , tst.grade_flag
2284 , to_char(opf.completed_date)
2285 , to_char(opf.completed_date, 'HH24:MI:SS')
2286 , opf.lesson_status
2287 , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
2288 ,ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
2289 From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
2290 OTA_TESTS tst
2291 Where
2292 olo.learning_object_id = opf.learning_object_id(+)
2293 AND tst.test_id(+) = olo.test_id
2294 And olo.Learning_object_id = p_lo_id
2295 And opf.User_id(+) = p_user_id
2296 And opf.User_type(+) = p_user_type
2297 AND opf.scorm_learner_attempt_id = p_scorm_learning_attempt_id
2298 AND opf.performance_id(+) = p_performance_id
2299 AND cert_prd_enrollment_id(+) is null;
2300
2301 CURSOR c_get_lo_tree_link_null(p_performance_id in number) is
2302 Select
2303 olo.name Name,
2304 nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
2305 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
2306 nvl(opf.time,0) Time,
2307 opf.score Score,
2308 decode(opf.lesson_status
2309 ,'C','player_status_c.gif'
2310 ,'F','player_status_f.gif'
2311 ,'I','player_status_i.gif'
2312 ,'P','player_status_p.gif'
2313 ,'N','player_status_n.gif'
2314 ,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type),
2315 'N','player_status_no_prereq.gif',
2316 'Y','player_status_n.gif')
2317 ,'player_status_n.gif') STATUS_ICON
2318 , tst.grade_flag
2319 , to_char(opf.completed_date)
2320 , to_char(opf.completed_date, 'HH24:MI:SS')
2321 , opf.lesson_status
2322 , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
2323 ,ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
2324 From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
2325 OTA_TESTS tst
2326 Where
2327 olo.learning_object_id = opf.learning_object_id(+)
2328 AND tst.test_id(+) = olo.test_id
2329 And olo.Learning_object_id = p_lo_id
2330 And opf.User_id(+) = p_user_id
2331 And opf.User_type(+) = p_user_type
2332 AND opf.performance_id(+) = p_performance_id
2333 AND cert_prd_enrollment_id(+) is null;
2334
2335
2336 CURSOR c_max_performance_id_scorm is
2337 Select max(per.performance_id)
2338 From ota_performances per
2339 Where per.learning_object_id(+) = p_lo_id
2340 And per.user_id(+) = p_user_id
2341 And per.user_type(+) = p_user_type
2342 And per.scorm_learner_attempt_id = p_scorm_learning_attempt_id
2343 AND per.cert_prd_enrollment_id(+) is null;
2344
2345 CURSOR c_max_performance_id is
2346 Select max(per.performance_id)
2347 From ota_performances per
2348 Where per.learning_object_id(+) = p_lo_id
2349 And per.user_id(+) = p_user_id
2350 And per.user_type(+) = p_user_type
2351 AND per.cert_prd_enrollment_id(+) is null;
2352 Begin
2353 hr_utility.set_location('Entering :'||l_proc,5);
2354
2355 if(p_scorm_learning_attempt_id is null) then
2356 open c_max_performance_id;
2357 fetch c_max_performance_id into l_max_performance_id;
2358 close c_max_performance_id;
2359 else
2360 open c_max_performance_id_scorm;
2361 fetch c_max_performance_id_scorm into l_max_performance_id;
2362 close c_max_performance_id_scorm;
2363 end if;
2364
2365 if ( l_max_performance_id is null ) then
2366 l_max_performance_id := -1;
2367 end if;
2368
2369 if(p_scorm_learning_attempt_id is null) then
2370 open c_get_lo_tree_link_null(l_max_performance_id);
2371 fetch c_get_lo_tree_link_null 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,l_lo_completion_date_tz;
2372 close c_get_lo_tree_link_null;
2373 else
2374 open c_get_lo_tree_link(l_max_performance_id);
2375 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,l_lo_completion_date_tz;
2376 close c_get_lo_tree_link;
2377
2378 end if;
2379
2380 If ( p_mode = 1) Then
2381 l_lo_time := round(l_lo_time);
2382
2383 l_Seconds := l_lo_time mod 60;
2384 l_Minutes := floor(l_lo_time / 60);
2385 l_Hours := floor(l_Minutes/60);
2386 l_Minutes := l_Minutes - l_Hours * 60;
2387
2388 If (l_Hours < 10) Then
2389 l_formatted_hour := '0' || l_Hours;
2390 Else
2391 l_formatted_hour := l_Hours;
2392 End If;
2393
2394 If (l_Minutes < 10) Then
2395 l_formatted_min := '0' || l_Minutes;
2396 Else
2397 l_formatted_min := l_Minutes;
2398 End If;
2399
2400 If (l_Seconds < 10) Then
2401 l_formatted_sec := '0' || l_Seconds;
2402 Else
2403 l_formatted_sec := l_Seconds;
2404 End If;
2405
2406 fnd_message.set_name('OTA', 'OTA_443358_SRCH_LO_TIME');
2407 fnd_message.set_token ('HOUR', l_formatted_hour);
2408 fnd_message.set_token ('MIN', l_formatted_min);
2409 fnd_message.set_token ('SEC', l_formatted_sec);
2410 l_formatted_time := fnd_message.get();
2411
2412 if p_active_cert_flag = 'Y' then
2413 fnd_message.set_name('OTA', 'OTA_443968_SRCH_TREE_TITLE3');
2414 elsif ((l_lo_score is null) or (l_lo_score < 0)) Then
2415 fnd_message.set_name('OTA', 'OTA_443453_SRCH_TREE_TITLE2');
2416 Else
2417 --Added for bug 3550407
2418 IF ( l_tst_grade_flag = 'P' ) THEN
2419 l_var_score := l_lo_score||'%';
2420 ELSE
2421 l_var_score := l_lo_score;
2422 END IF;
2423
2424 fnd_message.set_name('OTA', 'OTA_443357_SRCH_TREE_TITLE');
2425 fnd_message.set_token ('SCORE', l_var_score);
2426 End If;
2427
2428 If ( (l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P')
2429 and l_lo_completed_date is not null ) Then
2430 -- l_lo_status := l_lo_status || ' ' || l_lo_completed_date_tz; Modified for iCal Bug 12696284
2431 l_lo_status := l_lo_status || ' ' || fnd_date.date_to_displaydate(l_lo_completion_date_tz,2);
2432 End If;
2433
2434 --if this is part of active cert prd, return status as "Not Applicable"
2435 if p_active_cert_flag = 'Y' then
2436 fnd_message.set_token ('LO_NAME', l_lo_name);
2437 fnd_message.set_token ('STATUS', hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','D'));
2438 else
2439 fnd_message.set_token ('LO_NAME', l_lo_name);
2440 fnd_message.set_token ('STATUS', l_lo_status);
2441 fnd_message.set_token ('TIME', l_formatted_time);
2442 end if;
2443
2444 l_return_tree_title := fnd_message.get();
2445 Else
2446 --if this is part of active cert prd, return icon empty circle used for "Not Attempted"
2447 if p_active_cert_flag = 'Y' then
2448 l_return_tree_title := 'player_status_n.gif';
2449 else
2450 l_return_tree_title := l_lo_status_icon;
2451 end if;
2452
2453 End If;
2454
2455 hr_utility.set_location('Leaving :'||l_proc,10);
2456 RETURN l_return_tree_title;
2457 EXCEPTION
2458 WHEN others THEN
2459 hr_utility.set_location('Leaving :'||l_proc,15);
2460
2461 RETURN l_return_tree_title;
2462 End get_lo_title_for_tree;
2463
2464 function get_play_button(
2465 p_user_id fnd_user.user_id%type,
2466 p_user_type ota_attempts.user_type%type,
2467 p_is_manager varchar2,
2468 p_event_id ota_events.event_id%type,
2469 p_event_type ota_events.event_type%type,
2470 p_synchronous_flag ota_category_usages.synchronous_flag%type,
2471 p_online_flag ota_category_usages.online_flag%type,
2472 p_course_start_date ota_events.course_start_date%type,
2473 p_course_end_date ota_events.course_end_date%type,
2474 p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
2475 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
2476 p_contact_id ota_cert_enrollments.contact_id%type default null,
2477 p_chk_active_cert_flag varchar2 default 'N') return varchar2 is
2478
2479
2480 v_number_of_los number(10);
2481 v_solo_lo_id ota_learning_objects.learning_object_id%type := null;
2482 v_published_flag ota_learning_objects.published_flag%type := null;
2483 v_first_lo ota_learning_objects.learning_object_id%type := null;
2484 v_play_button_for_test varchar2(100) := null;
2485 v_rco_id ota_activity_versions.rco_id%type := null;
2486 v_offering_id ota_events.offering_id%type := null;
2487
2488 l_reason number;
2489 l_sysdate date;
2490 l_course_start_time ota_events.course_start_time%type;
2491 l_course_end_time ota_events.course_end_time%type;
2492 l_course_start_date ota_events.course_start_date%type;
2493 l_course_end_date ota_events.course_end_date%type;
2494 l_timezone ota_events.timezone%type;
2495
2496 CURSOR c_event_time_info is
2497 Select course_start_time, course_end_time, timezone
2498 From ota_events
2499 Where event_id = p_event_id;
2500
2501 l_cert_prd_enrollment_ids varchar2(4000) := null;
2502 l_person_id ota_cert_enrollments.person_id%type := null;
2503 l_cert_prd_enrollment_id OTA_CERT_PRD_ENROLLMENTS.cert_prd_enrollment_id%type;
2504 l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
2505 begin
2506
2507 if p_chk_active_cert_flag = 'Y' then
2508 l_cert_mbr_enrollment_id := ota_cme_util.get_cert_mbr_enroll_id(p_event_id, p_user_id, p_contact_id);
2509 if(l_cert_mbr_enrollment_id is not null) then
2510 return get_cme_play_button(p_user_id,p_user_type,p_is_manager,l_cert_mbr_enrollment_id);
2511 end if;
2512 end if;
2513
2514 /*===============================================
2515 * check if this is an Online class
2516 *===============================================*/
2517 if p_online_flag <> 'Y' then
2518 return 'ENABLE_DETAIL';
2519 end if;
2520
2521
2522 /*===============================================
2523 * user is logged in as manager
2524 *===============================================*/
2525 if (p_is_manager = 'IS_MANAGER') then
2526 return 'ENABLE_DETAIL';
2527 end if;
2528
2529 /*===============================================
2530 * bug 3401855. play icon should be disabled when
2531 * enrollment status is Cancelled.
2532 * bug 3661345. Enrollment Status Validation should
2533 * be done before Class Dates Validation.
2534 *===============================================*/
2535 if (p_enrollment_status_type = 'C') then
2536 return 'DISABLE_ENR_CANCELLED';
2537 end if;
2538
2539 /*===============================================
2540 * event is scheduled but not within time window
2541 *===============================================*/
2542 open c_event_time_info;
2543 fetch c_event_time_info into l_course_start_time, l_course_end_time, l_timezone;
2544 close c_event_time_info;
2545
2546 l_sysdate := ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, l_timezone);
2547
2548 if (p_event_type = 'SCHEDULED') then
2549
2550 --Bug 5107276
2551 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');
2552 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');
2553
2554 --l_sysdate := to_date(to_char(trunc(sysdate),'YYYY/MM/DD')||' '|| to_char(sysdate,'HH24:MI'),'YYYY/MM/DD HH24:MI');
2555
2556 if l_sysdate < l_course_start_date then --Bug 3559931
2557 return 'DISABLE_BEFORE_START';
2558 end if;
2559 if l_course_end_date < l_sysdate then --Bug 3559931
2560 return 'DISABLE_AFTER_END';
2561 end if;
2562 elsif ( p_event_type = 'SELFPACED') then --bug 3559931
2563 if p_course_start_date is not null then
2564 if trunc(l_sysdate) < p_course_start_date then
2565 return 'DISABLE_BEFORE_START';
2566 end if;
2567 end if;
2568 if p_course_end_date is not null then
2569 if p_course_end_date < trunc(l_sysdate) then
2570 return 'DISABLE_AFTER_END';
2571 end if;
2572 end if;
2573 end if;
2574
2575 /*===============================================
2576 * if the rco_id is not null, integrate with external player
2577 * This condition needs to be on top to avoid unnecessary
2578 * checks of Lo, prerequisites etc..
2579 *===============================================*/
2580 select a.rco_id
2581 ,e.offering_id
2582 into v_rco_id
2583 ,v_offering_id
2584 from ota_activity_versions a, ota_events e
2585 where a.activity_version_id = e.activity_version_id
2586 and e.event_id = p_event_id;
2587
2588 if v_rco_id is not null and v_offering_id is not null then
2589 return 'ENABLE_EXTERNAL_PLAY';
2590 end if;
2591
2592
2593 /*===============================================
2594 * check that this event contains only one LO
2595 * if so get the id
2596 *===============================================*/
2597 select count(learning_object_id)
2598 ,sum(learning_object_id)
2599 ,nvl(max(published_flag),'N')
2600 into v_number_of_los
2601 ,v_solo_lo_id
2602 ,v_published_flag
2603 from ota_learning_objects
2604 start with learning_object_id =
2605 (select learning_object_id
2606 from ota_offerings o
2607 ,ota_events e
2608 where o.offering_id = e.parent_offering_id
2609 and event_id = p_event_id)
2610 connect by parent_learning_object_id = prior learning_object_id;
2611
2612
2613 /*===============================================
2614 * if there is only a single LO in the class then
2615 *===============================================*/
2616 if v_number_of_los = 1 then
2617
2618 /*===============================================
2619 * check that the LO is published
2620 *===============================================*/
2621 if v_published_flag <> 'Y' then
2622 return 'DISABLE_LO_UNPUBLISHED';
2623 end if;
2624
2625 /*===============================================
2626 * check if it is a disabled test (assessment)
2627 *===============================================*/
2628 v_play_button_for_test := get_play_button_for_test(p_user_id, p_user_type, p_event_id, p_cert_prd_enrollment_id);
2629
2630 if v_play_button_for_test is not null then
2631 return v_play_button_for_test;
2632 end if;
2633
2634 /*===============================================
2635 * check if it is a disabled from an unmet prerequisite
2636 *===============================================*/
2637
2638 if not user_meets_prerequisites(v_solo_lo_id , p_user_id, p_user_type) then
2639 return 'DISABLE_UNMET_PREREQ';
2640 end if;
2641
2642 end if;
2643
2644
2645 /*===============================================
2646 * checks end dates and enrollment
2647 *===============================================*/
2648 if user_can_attempt_event(p_event_id,p_user_id,p_user_type) = 'N' then
2649 return 'DISABLE_UNKNOWN';
2650 end if;
2651
2652 /*===============================================
2653 * Disable if there are no playable LOs
2654 *===============================================*/
2655 v_first_lo := get_first_lo_id(p_event_id, p_user_id, p_user_type, '',p_cert_prd_enrollment_id);
2656
2657 if v_first_lo is null then
2658 return 'DISABLE_UNKNOWN';
2659 end if;
2660
2661 /*===============================================
2662 * no reason found to disable
2663 *===============================================*/
2664 return 'ENABLE_PLAY';
2665
2666
2667 exception
2668 when others then
2669 return 'ENABLE_PLAY';
2670
2671 end get_play_button;
2672
2673
2674 function get_play_button_for_test(
2675 p_user_id fnd_user.user_id%type,
2676 p_user_type ota_attempts.user_type%type,
2677 p_event_id ota_events.event_id%type,
2678 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2679
2680 v_lo_id ota_learning_objects.learning_object_id%type;
2681 begin
2682 select offr.learning_object_id
2683 into v_lo_id
2684 from ota_offerings offr, ota_events evt
2685 where evt.event_id = p_event_id and
2686 evt.parent_offering_id = offr.offering_id;
2687
2688 /*===============================================
2689 * if this is a single lo that is a test with
2690 * no attempts remaining, disable the play
2691 *===============================================*/
2692 if user_exceeded_attempt_limit(v_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
2693 return 'DISABLE_MAX_ATTEMPTS';
2694 /*===============================================
2695 * if this is a single lo that is a test and
2696 * it is within the wait duration, disable the play
2697 *===============================================*/
2698 elsif user_must_wait_to_attempt(v_lo_id, p_user_id, p_user_type, p_cert_prd_enrollment_id) then
2699 return 'DISABLE_WAIT_DURATION';
2700 else
2701 /*===============================================
2702 * no reason found to disable
2703 *===============================================*/
2704 return null;
2705 end if;
2706
2707 exception
2708 when others then
2709 return null;
2710 end get_play_button_for_test;
2711
2712 --bug8785933.Added for getting the sign/play button
2713 function get_sign_eval_button(
2714 p_sign_eval_status OTA_DELEGATE_BOOKINGS.SIGN_EVAL_STATUS%TYPE
2715 ) return varchar2 is
2716
2717
2718 l_symbol varchar2(100):=null;
2719
2720
2721 begin
2722
2723 case p_sign_eval_status
2724 when 'SD' then l_symbol:='DISABLE_M_SIGN_EVAL';
2725 when 'UD' then l_symbol:='DISABLE_SIGN';
2726 when 'MD' then l_symbol:='DISABLE_M_EVAL';
2727 when 'OD' then l_symbol:='DISABLE_V_EVAL';
2728 when 'VD' then l_symbol:='DISABLE_V_SIGN_EVAL';
2729 when 'SE' then l_symbol:='ENABLE_SIGN';
2730 when 'ME' then l_symbol:='ENABLE_EVAL';
2731 when 'UE' then l_symbol:='ENABLE_SIGN';
2732 when 'OE' then l_symbol:='ENABLE_EVAL';
2733 when 'VE' then l_symbol:='ENABLE_SIGN';
2734 when 'DD' then l_symbol:='SIGN_EVAL_DONE';
2735 when 'MF' then l_symbol:='EVAL_CLOSED'; --10649099
2736 when 'CE' then l_symbol:='ENABLE_SIGN'; --added(CE) for sign in onaynch cert courses after renewal and after playing the course.
2737 else l_symbol:='NO_SIGN_EVAL';
2738 end case;
2739 return l_symbol;
2740
2741
2742 exception
2743 when others then
2744 return null;
2745 end get_sign_eval_button;
2746
2747 --bug6683076.Added for getting the play button
2748
2749 function get_play_eval_button(
2750 p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2751 p_user_id fnd_user.user_id%type,
2752 p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2753 p_object_id OTA_EVALUATIONS.OBJECT_ID%TYPE,
2754 p_object_type OTA_EVALUATIONS.OBJECT_TYPE%TYPE,
2755 p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2756 p_test_id OTA_TESTS.TEST_ID%TYPE) return varchar2 is
2757
2758 l_is_mandatory boolean:= true;
2759 l_is_attempted boolean:= false;
2760 l_act_mandatory OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE;
2761 l_booking_status_type OTA_BOOKING_STATUS_TYPES.TYPE%TYPE;
2762 l_attempt_id number;
2763 l_act_eval_id OTA_EVALUATIONS.evaluation_id%TYPE;
2764
2765 CURSOR c_attempts_info is
2766 Select attempt_id
2767 From ota_attempts
2768 Where event_id = p_event_id
2769 and user_id = p_user_id
2770 and (act_eval_id = l_act_eval_id
2771 or test_id = p_test_id)
2772 and attempt_status = 'C' --bug#7574667
2773 and internal_state = 'F'; --bug#7311115
2774
2775 CURSOR c_mandatory_info is
2776 select eval.eval_mandatory_flag,eval.evaluation_id
2777 from ota_evaluations eval,ota_events evt
2778 where evt.activity_version_id = eval.object_id(+)
2779 and evt.event_id = p_event_id
2780 and eval.evaluation_id is not null --bug 7184369
2781 and (eval.object_type = 'A' or eval.object_type is null);
2782 begin
2783
2784
2785 /*===============================================
2786 * check if there is an evaluation
2787 *===============================================*/
2788 open c_mandatory_info;
2789 fetch c_mandatory_info into l_act_mandatory, l_act_eval_id;
2790 close c_mandatory_info;
2791
2792 if p_mand_flag is not null then
2793 if p_mand_flag = 'N' then
2794 if l_act_mandatory is null or l_act_mandatory = 'N' then
2795 l_is_mandatory:= false;
2796 end if;
2797 end if;
2798 elsif l_act_eval_id is null then
2799 return 'NO_EVAL';
2800 elsif l_act_mandatory = 'N' then
2801 l_is_mandatory:= false;
2802 end if;
2803
2804 /*===============================================
2805 * Evaluation exists, check whether to enable,
2806 * disable the evaluation, or mark as 'Done' .
2807 *===============================================*/
2808
2809 select type into l_booking_status_type
2810 from ota_booking_status_types
2811 where booking_status_type_id in (p_booking_status_type_id);
2812
2813 if l_is_mandatory then
2814 if l_booking_status_type = 'E' then
2815 return 'ENABLE_EVAL' ;
2816 elsif l_booking_status_type = 'A' then
2817 open c_attempts_info;
2818 fetch c_attempts_info into l_attempt_id;
2819 if c_attempts_info%found then
2820 l_is_attempted := true;
2821 end if;
2822 close c_attempts_info;
2823 if not l_is_attempted then
2824 return 'EVAL_CLOSED'; --7046809
2825 else
2826 return 'EVAL_DONE';
2827 end if;
2828 else
2829 return 'DISABLE_EVAL' ;
2830 end if;
2831 else
2832 if l_booking_status_type = 'A' then
2833 open c_attempts_info;
2834 fetch c_attempts_info into l_attempt_id;
2835 if c_attempts_info%found then
2836 l_is_attempted := true;
2837 end if;
2838 close c_attempts_info;
2839 if not l_is_attempted then
2840 return 'ENABLE_EVAL';
2841 else
2842 return 'EVAL_DONE';
2843 end if;
2844 else
2845 return 'DISABLE_EVAL';
2846 end if;
2847 end if;
2848 exception
2849 when others then
2850 return null;
2851 end get_play_eval_button;
2852
2853 --Enhancement: 7310093 SIP: A NEW FIELD WHICH CAN GIVE THE STATUS OF THE COURSE / CLASS EVALUATION
2854 --Modified for 8855548.
2855
2856 function get_admin_eval_status(
2857 p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2858 p_sign_eval_status OTA_DELEGATE_BOOKINGS.SIGN_EVAL_STATUS%TYPE
2859 ) return varchar2 is
2860
2861 l_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE;
2862 l_symbol varchar2(100);
2863
2864 Cursor csr_evt_evaluations is
2865 select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
2866 decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag
2867 from ota_evaluations evt_eval, ota_evaluations act_eval,ota_events evt
2868 where evt_eval.object_id(+) = evt.event_id
2869 and evt_eval.object_type(+) = 'E'
2870 and act_eval.object_id(+) = evt.activity_version_id
2871 and act_eval.object_type(+) = 'A'
2872 and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null)
2873 and evt.event_id = p_event_id;
2874 begin
2875
2876
2877 open csr_evt_evaluations;
2878 fetch csr_evt_evaluations into l_mand_flag;
2879 if csr_evt_evaluations%NOTFOUND then
2880 close csr_evt_evaluations;
2881 return 'NO_EVAL';
2882 end if;
2883 close csr_evt_evaluations;
2884
2885
2886 case p_sign_eval_status
2887 when 'SD' then l_symbol:='DISABLE_MANDATORY_EVAL';
2888 when 'UD' then l_symbol:='NO_EVAL';
2889 when 'MD' then l_symbol:='DISABLE_MANDATORY_EVAL';
2890 when 'OD' then l_symbol:='DISABLE_VOLUNTARY_EVAL';
2891 when 'VD' then l_symbol:='DISABLE_VOLUNTARY_EVAL';
2892 when 'SE' then l_symbol:='DISABLE_MANDATORY_EVAL';
2893 when 'ME' then l_symbol:='ENABLE_MANDATORY_EVAL';
2894 when 'UE' then l_symbol:='NO_EVAL';
2895 when 'OE' then l_symbol:='ENABLE_VOLUNTARY_EVAL';
2896 when 'VE' then l_symbol:='DISABLE_VOLUNTARY_EVAL';
2897 when 'MF' then l_symbol:='MANDATORY_EVAL_CLOSED'; --10649099
2898 when 'DD' then if (l_mand_flag = 'N') then l_symbol:='VOLUNTARY_EVAL_DONE';
2899 elsif (l_mand_flag = 'Y') then l_symbol:='MANDATORY_EVAL_DONE';
2900 end if;
2901 when 'CE' then l_symbol:='VOLUNTARY_EVAL_DONE'; -- This(CE) is after renewal of cert and prior to giving sign. Voluntary evaluation is no longer enabled after renewal.
2902 else l_symbol:='NO_EVAL';
2903 end case;
2904 return l_symbol;
2905
2906
2907 exception
2908 when others then
2909 return null;
2910 end get_admin_eval_status;
2911
2912 --
2913 -- ----------------------------------------------------------------------------
2914 -- |-------------------------< get_wait_duration_for_lo >----------------------
2915 -- ----------------------------------------------------------------------------
2916 -- Author: gdhutton
2917 -- This function is used to get the date until which the learner has to
2918 -- wait before playing the LO again.
2919 -- [End of Comments]
2920 -- ---------------------------------------------------------------------------
2921 function get_wait_duration_for_lo(
2922 p_user_id fnd_user.user_id%type,
2923 p_user_type ota_attempts.user_type%type,
2924 p_lo_id ota_learning_objects.learning_object_id%type,
2925 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2926
2927 v_earliest_attempt_date date;
2928 begin
2929 v_earliest_attempt_date :=
2930 get_next_attempt_date_for_lo(
2931 p_lo_id,
2932 p_user_id,
2933 p_user_type,
2934 p_cert_prd_enrollment_id);
2935
2936 if v_earliest_attempt_date is not null then
2937 return to_char(v_earliest_attempt_date) || to_char(v_earliest_attempt_date, ' HH24:MI:SS');
2938 else
2939 return null;
2940 end if;
2941 end get_wait_duration_for_lo;
2942 --
2943 -- ----------------------------------------------------------------------------
2944 -- |-------------------------< get_wait_duration_for_test >--------------------
2945 -- ----------------------------------------------------------------------------
2946 -- Author: smanjuna
2947 -- This function is used to get the timestamp until which the learner has to
2948 -- wait before playing the test again. This is displayed as flyover text.
2949 -- [End of Comments]
2950 -- ---------------------------------------------------------------------------
2951 function get_wait_duration_for_test(
2952 p_user_id fnd_user.user_id%type,
2953 p_user_type ota_attempts.user_type%type,
2954 p_event_id ota_events.event_id%type,
2955 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2 is
2956
2957 v_lo_id ota_learning_objects.learning_object_id%type;
2958 v_wait_duration varchar2(80);
2959 --Bug 5166350
2960 wait_dur_date varchar2(20);
2961 wait_dur_time varchar2(20);
2962
2963 begin
2964 select offr.learning_object_id
2965 into v_lo_id
2966 from ota_offerings offr, ota_events evt
2967 where evt.event_id = p_event_id and
2968 evt.parent_offering_id = offr.offering_id;
2969
2970 v_wait_duration := get_wait_duration_for_lo(p_user_id, p_user_type, v_lo_id, p_cert_prd_enrollment_id);
2971
2972 --Bug 5166350
2973 wait_dur_date := substr(v_wait_duration, 1, length(v_wait_duration)-9);
2974 wait_dur_time := substr(v_wait_duration, length(v_wait_duration)-7);
2975 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');
2976
2977 if v_wait_duration is not null then
2978 fnd_message.set_name('OTA', 'OTA_443502_TEST_WAIT_DRTN_TEXT');
2979 fnd_message.set_token('DATETIME', v_wait_duration);
2980 return fnd_message.get();
2981 else
2982 return null;
2983 end if;
2984 end get_wait_duration_for_test;
2985 --
2986 -- ----------------------------------------------------------------------------
2987 -- |-------------------------< Root_Folder_Exists>----------------------------|
2988 -- ----------------------------------------------------------------------------
2989 --
2990 function Root_Folder_Exists
2991 (p_folder_id in number default hr_api.g_number
2992 ,p_business_group_id in number default ota_general.get_business_group_id
2993 )
2994 return varchar2 is
2995 --
2996 -- decalare cursor
2997 cursor root_folder is
2998 select
2999 distinct 'found'
3000 from
3001 ota_lo_folders
3002 where
3003 business_group_id = p_business_group_id
3004 and folder_id <> p_folder_id
3005 and parent_folder_id is null;
3006 --
3007 -- decalre variables
3008 l_folder_flag varchar2(10);
3009 l_return varchar2(2) := 'N';
3010 l_proc varchar2(72) := g_package||'Root_Folder_Exists';
3011 --
3012 begin
3013 --
3014 hr_utility.set_location('Entering:'|| l_proc, 10);
3015 --
3016 open root_folder;
3017 fetch root_folder into l_folder_flag;
3018 IF root_folder%found THEN
3019 --
3020 l_return := 'Y';
3021 hr_utility.set_location('returning Y:'|| l_proc, 10);
3022 End if;
3023 --
3024 close root_folder;
3025 --
3026 hr_utility.set_location('Exiting:'|| l_proc, 20);
3027 --
3028 return l_return;
3029 --
3030 End Root_Folder_Exists;
3031 --
3032 -- ----------------------------------------------------------------------------
3033 -- |-------------------------< get_player_status >--------------------------|
3034 -- ----------------------------------------------------------------------------
3035 -- {Start Of Comments}
3036 --
3037 -- Description:
3038 -- This function will fetch the player staus code for online classes and enrollment
3039 -- content player status for offline classes.
3040 --
3041 -- Pre Conditions:
3042 -- None.
3043 --
3044 -- Out Arguments:
3045 -- p_user_id
3046 -- p_user_type
3047 -- p_event_id
3048 -- p_booking_id
3049 --
3050 -- Post Success:
3051 -- Processing continues.
3052 --
3053 --
3054 -- Post Failure:
3055 -- None.
3056 --
3057 -- Access Status:
3058 -- Public.
3059 --
3060 -- {End Of Comments}
3061 ----------------------------------------------------------------------------
3062 --
3063 FUNCTION get_player_status(p_user_id IN NUMBER,
3064 p_user_type IN ota_attempts.user_type%type,
3065 p_event_id IN ota_events.event_id%TYPE,
3066 p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
3067 RETURN VARCHAR2 IS
3068
3069 CURSOR c_learning_object IS
3070 SELECT ofr.learning_object_id
3071 FROM ota_events oev, ota_offerings ofr
3072 WHERE oev.parent_offering_id = ofr.offering_id
3073 AND oev.event_id = p_event_id;
3074
3075 CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3076 SELECT lesson_status
3077 FROM ota_performances
3078 WHERE user_id = p_user_id
3079 AND user_type = p_user_type
3080 AND learning_object_id = l_lo_id
3081 AND cert_prd_enrollment_id is null;
3082
3083 CURSOR c_odb_lo_status IS
3084 SELECT content_player_status
3085 FROM ota_delegate_bookings
3086 WHERE booking_id = p_booking_id;
3087
3088 CURSOR c_imported_offering IS
3089 SELECT offering_id
3090 FROM ota_events
3091 WHERE event_id = p_event_id;
3092
3093 l_proc VARCHAR2(72) := g_package|| 'get_player_status';
3094
3095 l_learning_object_id ota_offerings.learning_object_id%TYPE;
3096
3097 l_status VARCHAR2(30) := null;
3098
3099 l_imported_offering ota_events.offering_id%TYPE := null;
3100
3101 BEGIN
3102 hr_utility.set_location(' Step:'|| l_proc, 10);
3103
3104 open c_learning_object;
3105 fetch c_learning_object into l_learning_object_id;
3106 close c_learning_object;
3107
3108 -- Check if the event is imported
3109 open c_imported_offering;
3110 fetch c_imported_offering into l_imported_offering;
3111 close c_imported_offering;
3112
3113 If (l_imported_offering is not null) Then
3114 -- Imported Offering, Status code should be taken from
3115 -- OTA_DELEGATE_BOOKINGS
3116 open c_odb_lo_status;
3117 fetch c_odb_lo_status into l_status;
3118 close c_odb_lo_status;
3119 Else
3120 -- EBS created, Status code should be taken from
3121 -- OTA_PERFORMANCES
3122 open c_learning_object_status(l_learning_object_id);
3123 fetch c_learning_object_status into l_status;
3124 close c_learning_object_status;
3125
3126 End If;
3127
3128 RETURN l_status;
3129 hr_utility.set_location(' Step:'|| l_proc, 20);
3130 END get_player_status;
3131
3132 -- ----------------------------------------------------------------------------
3133 -- |-------------------------< get_enroll_lo_time >--------------------------|
3134 -- ----------------------------------------------------------------------------
3135 -- {Start Of Comments}
3136 --
3137 -- Description:
3138 -- This function will fetch the player time for online classes and enrollment
3139 -- training time for offline classes.
3140 --
3141 -- Pre Conditions:
3142 -- None.
3143 --
3144 -- Out Arguments:
3145 -- p_user_id
3146 -- p_user_type
3147 -- p_event_id
3148 -- p_booking_id
3149 --
3150 -- Post Success:
3151 -- Processing continues.
3152 --
3153 --
3154 -- Post Failure:
3155 -- None.
3156 --
3157 -- Access Status:
3158 -- Public.
3159 --
3160 -- {End Of Comments}
3161 ----------------------------------------------------------------------------
3162 --
3163 FUNCTION get_enroll_lo_time(p_user_id IN NUMBER,
3164 p_user_type IN ota_attempts.user_type%type,
3165 p_event_id IN ota_events.event_id%TYPE,
3166 p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
3167 RETURN VARCHAR2 IS
3168
3169 CURSOR c_learning_object IS
3170 SELECT ofr.learning_object_id
3171 FROM ota_events oev, ota_offerings ofr
3172 WHERE oev.parent_offering_id = ofr.offering_id
3173 AND oev.event_id = p_event_id;
3174
3175 CURSOR c_learning_object_time(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3176 SELECT TO_CHAR(TRUNC(SYSDATE)+(time)/86400, 'HH24:Mi:SS')
3177 FROM ota_performances
3178 WHERE user_id = p_user_id
3179 AND user_type = p_user_type
3180 AND learning_object_id = l_lo_id
3181 AND cert_prd_enrollment_id is null;
3182
3183 CURSOR c_odb_lo_time IS
3184 SELECT total_training_time
3185 FROM ota_delegate_bookings
3186 WHERE booking_id = p_booking_id;
3187
3188 CURSOR c_imported_offering IS
3189 SELECT offering_id
3190 FROM ota_events
3191 WHERE event_id = p_event_id;
3192
3193 l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_time';
3194
3195 l_learning_object_id ota_offerings.learning_object_id%TYPE;
3196
3197 l_time VARCHAR2(30) := null;
3198
3199 l_imported_offering ota_events.offering_id%TYPE := null;
3200
3201 BEGIN
3202 hr_utility.set_location(' Step:'|| l_proc, 10);
3203
3204 open c_learning_object;
3205 fetch c_learning_object into l_learning_object_id;
3206 close c_learning_object;
3207
3208 -- Check if the event is imported
3209 open c_imported_offering;
3210 fetch c_imported_offering into l_imported_offering;
3211 close c_imported_offering;
3212
3213 If (l_imported_offering is not null) Then
3214 -- Imported Offering, Score should be taken from
3215 -- OTA_DELEGATE_BOOKINGS
3216 open c_odb_lo_time;
3217 fetch c_odb_lo_time into l_time;
3218 close c_odb_lo_time;
3219 Else
3220 -- EBS created, time should be taken from
3221 -- OTA_PERFORMANCES
3222 open c_learning_object_time(l_learning_object_id);
3223 fetch c_learning_object_time into l_time;
3224 close c_learning_object_time;
3225
3226 End If;
3227
3228 RETURN l_time;
3229 hr_utility.set_location(' Step:'|| l_proc, 20);
3230 END get_enroll_lo_time;
3231
3232 -- ----------------------------------------------------------------------------
3233 -- |-------------------------< get_enroll_lo_score >--------------------------|
3234 -- ----------------------------------------------------------------------------
3235 -- {Start Of Comments}
3236 --
3237 -- Description:
3238 -- This function will fetch the player score for online classes and enrollment
3239 -- score for offline classes.
3240 --
3241 -- Pre Conditions:
3242 -- None.
3243 --
3244 -- Out Arguments:
3245 -- p_user_id
3246 -- p_user_type
3247 -- p_event_id
3248 -- p_booking_id
3249 --
3250 -- Post Success:
3251 -- Processing continues.
3252 --
3253 --
3254 -- Post Failure:
3255 -- None.
3256 --
3257 -- Access Status:
3258 -- Public.
3259 --
3260 -- {End Of Comments}
3261 ----------------------------------------------------------------------------
3262 --
3263 FUNCTION get_enroll_lo_score(p_user_id IN NUMBER,
3264 p_user_type IN ota_attempts.user_type%type,
3265 p_event_id IN ota_events.event_id%TYPE,
3266 p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
3267 RETURN VARCHAR2 IS
3268
3269 CURSOR c_learning_object IS
3270 SELECT ofr.learning_object_id
3271 FROM ota_events oev, ota_offerings ofr
3272 WHERE oev.parent_offering_id = ofr.offering_id
3273 AND oev.event_id = p_event_id;
3274
3275 CURSOR c_learning_object_score(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3276 SELECT score
3277 FROM ota_performances
3278 WHERE user_id = p_user_id
3279 AND user_type = p_user_type
3280 AND learning_object_id = l_lo_id
3281 AND cert_prd_enrollment_id is null;
3282
3283 CURSOR c_odb_lo_score IS
3284 SELECT score
3285 FROM ota_delegate_bookings
3286 WHERE booking_id = p_booking_id;
3287
3288 CURSOR c_imported_offering IS
3289 SELECT offering_id
3290 FROM ota_events
3291 WHERE event_id = p_event_id;
3292
3293 l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_score';
3294
3295 l_learning_object_id ota_offerings.learning_object_id%TYPE;
3296
3297 l_score VARCHAR2(30) := null;
3298
3299 l_imported_offering ota_events.offering_id%TYPE := null;
3300
3301 BEGIN
3302 hr_utility.set_location(' Step:'|| l_proc, 10);
3303
3304 open c_learning_object;
3305 fetch c_learning_object into l_learning_object_id;
3306 close c_learning_object;
3307
3308 -- Check if the event is imported
3309 open c_imported_offering;
3310 fetch c_imported_offering into l_imported_offering;
3311 close c_imported_offering;
3312
3313 If (l_imported_offering is not null) Then
3314 -- Imported Offering, Score should be taken from
3315 -- OTA_DELEGATE_BOOKINGS
3316 open c_odb_lo_score;
3317 fetch c_odb_lo_score into l_score;
3318 close c_odb_lo_score;
3319 Else
3320 -- EBS created, Score should be taken from
3321 -- OTA_PERFORMANCES
3322 open c_learning_object_score(l_learning_object_id);
3323 fetch c_learning_object_score into l_score;
3324 close c_learning_object_score;
3325
3326 End If;
3327
3328 If ( l_score = -1000) Then
3329 l_score := null;
3330 End If;
3331
3332 RETURN l_score;
3333 hr_utility.set_location(' Step:'|| l_proc, 20);
3334 END get_enroll_lo_score;
3335 -- ----------------------------------------------------------------------------
3336 -- |-------------------------< get_enroll_lo_status >--------------------------|
3337 -- ----------------------------------------------------------------------------
3338 -- {Start Of Comments}
3339 --
3340 -- Description:
3341 -- This function will fetch the player status for online classes and enrollment
3342 -- status for offline classes.
3343 --
3344 -- Pre Conditions:
3345 -- None.
3346 --
3347 -- Out Arguments:
3348 -- p_user_id
3349 -- p_user_type
3350 -- p_event_id
3351 -- p_booking_status_type_id
3352 -- p_booking_id
3353 -- p_mode
3354 -- p_chk_active_cert_flag
3355 --
3356 -- Post Success:
3357 -- Processing continues.
3358 --
3359 --
3360 -- Post Failure:
3361 -- None.
3362 --
3363 -- Access Status:
3364 -- Public.
3365 --
3366 -- {End Of Comments}
3367 ----------------------------------------------------------------------------
3368 --
3369 FUNCTION get_enroll_lo_status(p_user_id IN NUMBER,
3370 p_user_type IN ota_attempts.user_type%type,
3371 p_event_id IN ota_events.event_id%TYPE,
3372 p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
3373 p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
3374 p_mode IN number default null,
3375 p_chk_active_cert_flag varchar2 default 'N')
3376 RETURN VARCHAR2 IS
3377
3378
3379 --Bug 5222464
3380 CURSOR c_delivery_mode IS
3381 SELECT ocu.online_flag, ofr.learning_object_id,
3382 to_date(to_char(nvl(oev.course_end_date, to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || nvl(oev.course_end_time, '23:59'), 'YYYY/MM/DD HH24:MI'),
3383 ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, oev.timezone)
3384 FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
3385 WHERE oev.parent_offering_id = ofr.offering_id
3386 AND ofr.delivery_mode_id = ocu.category_usage_id
3387 AND oev.event_id = p_event_id;
3388
3389 CURSOR c_booking_status IS
3390 SELECT BST.type, BST.name
3391 FROM ota_booking_status_types_vl BST
3392 WHERE BST.booking_status_type_id = p_booking_status_type_id;
3393
3394 CURSOR c_learning_object_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3395 SELECT lesson_status,
3396 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
3397 FROM ota_performances
3398 WHERE user_id = p_user_id
3399 AND user_type = p_user_type
3400 AND learning_object_id = l_lo_id
3401 AND cert_prd_enrollment_id is null;
3402
3403 CURSOR c_odb_lo_status IS
3404 SELECT CONTENT_PLAYER_STATUS,
3405 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',CONTENT_PLAYER_STATUS)lesson_status_name
3406 FROM ota_delegate_bookings
3407 WHERE booking_id = p_booking_id;
3408
3409 CURSOR c_imported_offering IS
3410 SELECT offering_id
3411 FROM ota_events
3412 WHERE event_id = p_event_id;
3413
3414 -- Bug 13496386
3415 cursor c_learning_object_tracktype (l_lo_id in ota_offerings.learning_object_id%type) IS
3416 select Tracking_type
3417 from OTA_Learning_objects
3418 where learning_object_id = l_lo_id;
3419
3420 --modified for 14000818.
3421 cursor csr_noof_perf_recs (l_lo_id in ota_offerings.learning_object_id%type) is
3422 select count(p.PERFORMANCE_ID)
3423 from
3424 ota_performances p, ota_learning_objects lo
3425 where lo.learning_object_id = p.learning_object_id
3426 and p.user_id = p_user_id
3427 and p.user_type = p_user_type
3428 and lo.learning_object_id = l_lo_id
3429 and p.cert_prd_enrollment_id is null;
3430
3431 --added for 14000818
3432 cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type,
3433 l_cert_prd_enr_id in ota_performances.cert_prd_enrollment_id%type) is
3434 select count(p.PERFORMANCE_ID)
3435 from
3436 ota_performances p, ota_learning_objects lo
3437 where lo.learning_object_id = p.learning_object_id
3438 and p.user_id = p_user_id
3439 and p.user_type = p_user_type
3440 and lo.learning_object_id = l_lo_id
3441 and p.cert_prd_enrollment_id = l_cert_prd_enr_id;
3442
3443 -- Bug 4665032
3444 CURSOR csr_best_prf (l_lo_id in ota_offerings.learning_object_id%TYPE) IS
3445 SELECT decode(prf.lesson_status, 'P', '1',
3446 'C', '2',
3447 'F', '3',
3448 'I', '4',
3449 'B', '5',
3450 'N', '6') decode_lesson_status,
3451 prf.lesson_status lesson_status,
3452 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS', prf.lesson_status) lesson_status_name
3453 FROM ota_performances prf
3454 WHERE
3455 prf.user_id = p_user_id
3456 and prf.user_type = p_user_type
3457 and prf.learning_object_id = l_lo_id
3458 order by decode_lesson_status;
3459
3460 cursor csr_contact_id is
3461 select delegate_contact_id
3462 from ota_delegate_bookings
3463 where booking_id = p_booking_id;
3464
3465 cursor csr_enr_sign_info is
3466 select odb.sign_eval_status from
3467 ota_delegate_bookings odb
3468 where odb.booking_id = p_booking_id;
3469
3470
3471 l_proc VARCHAR2(72) := g_package|| 'get_enroll_lo_status';
3472
3473 l_online_flag ota_category_usages.online_flag%TYPE;
3474 l_learning_object_id ota_offerings.learning_object_id%TYPE;
3475 l_course_end_date ota_events.course_end_date%TYPE;
3476 l_sysdate ota_events.course_end_date%TYPE;
3477
3478 l_status VARCHAR2(30) := null;
3479 l_status_name ota_booking_status_types_tl.name%TYPE := null;
3480
3481 --Bug 4665032
3482 l_enrollment_status_code ota_booking_status_types.type%TYPE;
3483 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
3484 l_decode_lesson_status VARCHAR2(1);
3485
3486 -- Bug 3725560
3487 l_imported_offering ota_events.offering_id%TYPE := null;
3488
3489 l_cert_prd_enrollment_ids varchar2(4000) := null;
3490 l_person_id ota_cert_enrollments.person_id%type := null;
3491 l_contact_id ota_cert_enrollments.contact_id%type := null;
3492 l_cert_prd_enrollment_id OTA_CERT_PRD_ENROLLMENTS.cert_prd_enrollment_id%type;
3493 l_sign_eval_status OTA_DELEGATE_BOOKINGS.sign_eval_status%type;
3494 l_trackingtype ota_learning_objects.tracking_type %TYPE;
3495 l_perf_rec_count INTEGER := 0;
3496 l_multiple boolean:=false;
3497
3498 BEGIN
3499 hr_utility.set_location(' Step:'|| l_proc, 10);
3500 open c_delivery_mode;
3501 fetch c_delivery_mode into l_online_flag, l_learning_object_id, l_course_end_date, l_sysdate; --Bug 5222464
3502 close c_delivery_mode;
3503
3504 --Fetch the enrollment status of the booking
3505 open c_booking_status;
3506 fetch c_booking_status into l_enrollment_status_code, l_enrollment_status_name;
3507 close c_booking_status;
3508
3509 -- Check for LO status for online classes
3510 If ( l_online_flag = 'Y' ) Then
3511
3512
3513 -- Check if Tracking type of learning object is S or not
3514 open c_learning_object_tracktype(l_learning_object_id);
3515 fetch c_learning_object_tracktype into l_trackingtype;
3516 close c_learning_object_tracktype;
3517
3518 -- Bug 3725560
3519 -- Check if the event is imported
3520 open c_imported_offering;
3521 fetch c_imported_offering into l_imported_offering;
3522 close c_imported_offering;
3523
3524 If (l_imported_offering is not null) Then
3525 -- Imported Offering, Status should be taken from
3526 -- OTA_DELEGATE_BOOKINGS
3527 open c_odb_lo_status;
3528 fetch c_odb_lo_status into l_status, l_status_name;
3529 close c_odb_lo_status;
3530 Else
3531 -- EBS created, Status should be taken from
3532 -- OTA_PERFORMANCES
3533 --Commented by shwnayak for 13066773
3534 /* If ( l_enrollment_status_code = 'A' and p_mode = 2 ) Then
3535 -- p_mode = 2 means that coming from admin side
3536 open csr_best_prf(l_learning_object_id);
3537 fetch csr_best_prf into l_decode_lesson_status, l_status, l_status_name;
3538 close csr_best_prf;*/
3539
3540 If p_mode = 2 then
3541 -- p_mode = 2 means that coming from admin side
3542 open csr_best_prf(l_learning_object_id);
3543 fetch csr_best_prf into l_decode_lesson_status, l_status, l_status_name;
3544 close csr_best_prf;
3545 Else
3546 -- Coming from learner side
3547 open c_learning_object_status(l_learning_object_id);
3548 fetch c_learning_object_status into l_status, l_status_name;
3549 close c_learning_object_status;
3550
3551 --- Added for bug 1349386
3552 -- if(l_trackingtype IS not null) then
3553 if(l_trackingtype = 'S') then
3554 open csr_noof_perf_recs(l_learning_object_id);
3555 fetch csr_noof_perf_recs into l_perf_rec_count;
3556 if(l_perf_rec_count > 1) then
3557 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','M');
3558 End if;
3559 End If;
3560 -- End if;
3561
3562
3563 End If;
3564 End If;
3565
3566 If ( l_status is null ) Then
3567 l_status := 'N';
3568 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
3569 End If;
3570
3571 If ( (l_status = 'N') or (l_status = 'I') ) Then
3572 If ( l_course_end_date < l_sysdate --Class is expired --Bug 3554773 --Bug 5222464
3573 AND p_mode is null ) Then -- Bug 3594377
3574 l_status := 'E';
3575 l_status_name := ota_utility.get_message('OTA','OTA_443001_OFFR_EXPIRED_STATUS');
3576 End If;
3577 End If;
3578
3579 --check if the enrollment is part of active certs
3580 if p_chk_active_cert_flag is not null and p_chk_active_cert_flag = 'Y' then
3581 if p_user_type = 'E' then
3582 l_person_id := p_user_id;
3583 elsif p_user_type = 'C' then
3584 open csr_contact_id;
3585 fetch csr_contact_id into l_contact_id;
3586 close csr_contact_id;
3587 end if;
3588
3589 l_cert_prd_enrollment_id := ota_cme_util.get_cert_prd_enroll_id(p_event_id, l_person_id, l_contact_id);
3590
3591 if l_cert_prd_enrollment_id is not null then
3592 --return status mng as 'Available under certification'
3593 --added for 14000818
3594 if(l_trackingtype = 'S') then
3595 open csr_noof_perf_recs_cert(l_learning_object_id,l_cert_prd_enrollment_id);
3596 fetch csr_noof_perf_recs_cert into l_perf_rec_count;
3597 close csr_noof_perf_recs_cert;
3598 if(l_perf_rec_count > 1) then
3599 l_multiple := true;
3600 End if;
3601 End if;
3602 if l_multiple then
3603 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','M');
3604 else
3605 l_status := 'D';
3606 l_status_name := get_cert_lo_status(p_user_id,p_user_type,p_event_id,p_booking_status_type_id,p_booking_id,l_cert_prd_enrollment_id,p_mode);
3607 end if;
3608 end if;--l_cert_prd_enrollment_id
3609 end if;--p_chk_active_cert_flag
3610 --bug 6683076.Added to show 'Pending Evaluation' as player status for
3611 --learners who have to take mandatory evaluation after
3612 --completing the online class.
3613 --bug 7175372.Added condition so that this player status is shown
3614 --only from learner side.
3615 If (l_status = 'C' or l_status = 'P' or l_status = 'F') Then
3616 If (l_enrollment_status_code = 'E' and (p_mode is null or p_mode <>2)) Then
3617 open csr_enr_sign_info;
3618 fetch csr_enr_sign_info into l_sign_eval_status;
3619 close csr_enr_sign_info;
3620 if(l_sign_eval_status in ('SE','UE','VE','ME')) then --8855548
3621 l_status_name:= l_enrollment_status_name;
3622 end if;
3623 End If;
3624 End If;
3625 --Added below code for 12683928
3626 if(l_enrollment_status_code='C' and (p_mode is null or p_mode <>2)) Then
3627 l_status:= l_enrollment_status_code;
3628 l_status_name:= l_enrollment_status_name;
3629 End If;
3630
3631 Else
3632 If p_mode <> 2 Then -- Bug#4465495 If p_mode is not 2
3633 -- for off-line class return enrollment status
3634 l_status := l_enrollment_status_code;
3635 l_status_name := l_enrollment_status_name;
3636 Else --Bug#4465495 If p_mode is 2 for off-line class return null;
3637 l_status_name := null;
3638 End If;
3639 End If;
3640
3641 RETURN l_status_name;
3642 hr_utility.set_location(' Step:'|| l_proc, 20);
3643 END get_enroll_lo_status;
3644
3645 -- ----------------------------------------------------------------------------
3646 -- |-------------------------< get_history_button >----------------------------|
3647 -- ----------------------------------------------------------------------------
3648 -- {Start Of Comments}
3649 --
3650 -- Description:
3651 -- This function will return a value based on which the Move to History
3652 -- Button will be enabled. it will be enabled for online classes with a
3653 -- performance status of Completed, Passed or Failed.
3654 --
3655 -- Pre Conditions:
3656 -- None.
3657 --
3658 -- Out Arguments:
3659 -- p_user_id
3660 -- p_lo_id
3661 -- p_event_id
3662 -- p_booking_id
3663 --
3664 -- Post Success:
3665 -- Processing continues.
3666 --
3667 --
3668 -- Post Failure:
3669 -- None.
3670 --
3671 -- Access Status:
3672 -- Public.
3673 --
3674 -- {End Of Comments}
3675 ----------------------------------------------------------------------------
3676
3677 FUNCTION get_history_button(p_user_id fnd_user.user_id%TYPE,
3678 p_lo_id ota_learning_objects.learning_object_id%TYPE,
3679 p_event_id ota_events.event_id%TYPE,
3680 p_booking_id ota_delegate_bookings.booking_id%TYPE)
3681 RETURN VARCHAR2 IS
3682
3683 l_return VARCHAR2(100);
3684 l_enable_move_to_history boolean;
3685 l_type varchar2(1);
3686 l_proc VARCHAR2(72) := g_package||'get_history_button';
3687
3688 l_imported_offering ota_events.offering_id%TYPE;
3689 l_booking_status_type ota_booking_status_types.type%TYPE;
3690
3691 CURSOR c_imported_offering IS
3692 SELECT offering_id
3693 FROM ota_events
3694 WHERE event_id = p_event_id;
3695
3696 CURSOR c_history_enabled IS
3697 SELECT lesson_status
3698 FROM ota_performances
3699 WHERE learning_object_id = p_lo_id
3700 AND user_id = p_user_id
3701 AND lesson_status IN ('P', 'C');
3702
3703 CURSOR c_booking_status_type IS
3704 SELECT type
3705 FROM ota_delegate_bookings odb,
3706 ota_booking_status_types bst
3707 WHERE booking_id = p_booking_id
3708 and odb.booking_status_type_id = bst.booking_status_type_id;
3709
3710 CURSOR c_history_enabled_odb IS
3711 SELECT content_player_status
3712 FROM ota_delegate_bookings
3713 WHERE booking_id = p_booking_id
3714 AND content_player_status in ('P', 'C');
3715
3716 BEGIN
3717
3718 hr_utility.set_location(' Step:'|| l_proc, 10);
3719
3720 --Initialize local variables
3721 l_enable_move_to_history := false;
3722 l_return := 'MoveToHistoryDisabled';
3723
3724 --First check the enrollment status
3725 OPEN c_booking_status_type;
3726 FETCH c_booking_status_type INTO l_booking_status_type;
3727 CLOSE c_booking_status_type;
3728
3729 if (l_booking_status_type = 'A' ) Then --If status is A then move to history should be enabled
3730 l_enable_move_to_history := true;
3731 elsif (l_booking_status_type <> 'E') Then -- Bug 6683076
3732 -- Bug 3725560
3733 -- Check if the event is imported
3734 OPEN c_imported_offering;
3735 FETCH c_imported_offering into l_imported_offering;
3736 CLOSE c_imported_offering;
3737
3738 IF (l_imported_offering is not null) Then
3739 -- Imported Offering, Status should be taken from
3740 -- OTA_DELEGATE_BOOKINGS - Bug 3725560
3741 OPEN c_history_enabled_odb;
3742 FETCH c_history_enabled_odb INTO l_type;
3743 CLOSE c_history_enabled_odb;
3744 ELSE
3745 -- EBS created, Status should be taken from
3746 -- OTA_PERFORMANCES
3747 OPEN c_history_enabled;
3748 FETCH c_history_enabled INTO l_type;
3749 CLOSE c_history_enabled;
3750 END IF;
3751 if (l_type is not null) then
3752 l_enable_move_to_history := true;
3753 end if;
3754
3755 end if;
3756 IF (l_enable_move_to_history) THEN
3757 l_return := 'MoveToHistoryImage';
3758 END IF;
3759
3760 RETURN l_return;
3761
3762 EXCEPTION
3763 WHEN others then
3764
3765 RETURN l_return;
3766
3767 END get_history_button;
3768
3769 FUNCTION get_nls_language
3770 RETURN varchar2
3771 IS
3772 CURSOR csr_get_nls_lang IS
3773 SELECT NLS_LANGUAGE
3774 FROM fnd_languages
3775 WHERE language_code = userenv('LANG');
3776 l_nls_language fnd_languages.NLS_LANGUAGE%TYPE;
3777 BEGIN
3778 OPEN csr_get_nls_lang;
3779 FETCH csr_get_nls_lang INTO l_nls_language;
3780 CLOSE csr_get_nls_lang;
3781
3782 RETURN l_nls_language;
3783 END get_nls_language;
3784
3785 -- Author: sbhullar
3786 -- ----------------------------------------------------------------
3787 -- ------------------<get_lo_completion_date >--------------------
3788 -- ----------------------------------------------------------------
3789 -- {Start of Comments}
3790 --
3791 -- Description:
3792 -- This function will be used to show get lo completion date for
3793 -- online asynchronous offering
3794 -- IN
3795 -- p_event_id
3796 -- p_user_id
3797 -- p_user_type
3798 --
3799 -- Post Failure:
3800 -- None.
3801 -- Access Status
3802 -- Public
3803 -- {End of Comments}
3804 ------------------------------------------------------------------
3805 --Added method get_lo_completion_date_time,similar to get_lo_completion_date but returns a date allowing UI sort
3806 FUNCTION get_lo_completion_date(p_event_id IN ota_events.event_id%type,
3807 p_user_id IN NUMBER,
3808 p_user_type IN ota_attempts.user_type%type,
3809 p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3810 p_module_name IN VARCHAR2 default 'LEARNER')
3811 RETURN varchar2
3812 IS
3813 l_proc VARCHAR2(72) := g_package||'get_lo_completion_date';
3814 l_event_id ota_events.event_id%type;
3815 l_lo_id ota_learning_objects.learning_object_id%type;
3816 -- l_lo_completed_date VARCHAR2(100);
3817 l_lo_completed_date DATE;
3818 l_lo_completed_time VARCHAR2(10);
3819 l_lo_completed_date_tz VARCHAR2(100);
3820 l_lo_lesson_status ota_performances.lesson_status%type;
3821 l_sync_flag ota_category_usages.synchronous_flag%type;
3822 l_online_flag ota_category_usages.online_flag%type;
3823 l_return_completion_date varchar(50) := '';
3824 l_decode_lesson_status VARCHAR2(1);
3825
3826 CURSOR c_get_lo_completion_date is
3827 Select
3828 oev.event_id,
3829 ofr.learning_object_id,
3830 opf.completed_date,
3831 to_char(opf.completed_date, 'HH24:MI:SS'),
3832 opf.lesson_status,
3833 decode(opf.lesson_status, 'P', '1',
3834 'C', '2',
3835 'F', '3',
3836 'I', '4',
3837 'B', '5',
3838 'N', '6') decode_lesson_status,
3839 ocu.Synchronous_Flag Sync_Flag,
3840 ocu.Online_Flag Online_Flag,
3841 ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3842 From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3843 Where
3844 oev.parent_offering_id = ofr.offering_id
3845 And ofr.learning_object_id = opf.learning_object_id(+)
3846 And oev.event_id = p_event_id
3847 And opf.User_id(+) = p_user_id
3848 And opf.User_type(+) = p_user_type
3849 And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id
3850 order by decode_lesson_status;
3851
3852 CURSOR c_get_lo_completion_date_cert is
3853 Select
3854 oev.event_id,
3855 ofr.learning_object_id,
3856 opf.completed_date,
3857 to_char(opf.completed_date, 'HH24:MI:SS'),
3858 opf.lesson_status,
3859 ocu.Synchronous_Flag Sync_Flag,
3860 ocu.Online_Flag Online_Flag,
3861 ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3862 From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3863 Where
3864 oev.parent_offering_id = ofr.offering_id
3865 And ofr.learning_object_id = opf.learning_object_id(+)
3866 And oev.event_id = p_event_id
3867 And opf.User_id(+) = p_user_id
3868 And opf.User_type(+) = p_user_type
3869 And opf.cert_prd_enrollment_id(+) = p_cert_prd_enroll_id
3870 And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id;
3871
3872 Begin
3873 hr_utility.set_location('Entering :'||l_proc,5);
3874 if(p_cert_prd_enroll_id is not null) then
3875 open c_get_lo_completion_date_cert;
3876 fetch c_get_lo_completion_date_cert 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;
3877 close c_get_lo_completion_date_cert;
3878 else
3879 open c_get_lo_completion_date;
3880 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_decode_lesson_status, l_sync_flag, l_online_flag, l_lo_completed_date_tz;
3881 close c_get_lo_completion_date;
3882 end if;
3883
3884 If ( l_online_flag = 'Y' and l_sync_flag = 'N'
3885 and l_lo_completed_date is not null and ( l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P') ) Then
3886 If ( p_module_name = 'LEARNER' or p_module_name = 'INSTRUCTOR' ) Then
3887 l_return_completion_date := l_lo_completed_date_tz;
3888 Else
3889
3890 l_return_completion_date :=
3891 to_char(trunc(l_lo_completed_date)
3892 ,hr_util_misc_web.get_nls_parameter('NLS_DATE_FORMAT')
3893 , 'nls_date_language = ' || get_nls_language())
3894 || ' ' || l_lo_completed_time;
3895 End If;
3896 End If;
3897
3898
3899 RETURN l_return_completion_date;
3900 EXCEPTION
3901 WHEN others THEN
3902 hr_utility.set_location('Leaving :'||l_proc,15);
3903 RETURN l_return_completion_date;
3904 End get_lo_completion_date;
3905
3906 -- ----------------------------------------------------------------------------
3907 -- |--------------------< GET_LO_COMPLETION_DATE_TIME>-------------------------|
3908 -- ----------------------------------------------------------------------------
3909
3910 --Added for 6768606:COMPLETION DATE COLUMN SORT NUMERIC AND NOT BY ACTUAL DATE SORT
3911 --Similar to get_lo_completion_date,but the return type is date.Called from admin side
3912
3913 FUNCTION get_lo_completion_date_time(p_event_id IN ota_events.event_id%type,
3914 p_user_id IN NUMBER,
3915 p_user_type IN ota_attempts.user_type%type,
3916 p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
3917 p_module_name IN VARCHAR2 default 'LEARNER')
3918 RETURN date
3919 IS
3920 l_proc VARCHAR2(72) := g_package||'get_lo_completion_date_time';
3921 l_event_id ota_events.event_id%type;
3922 l_lo_id ota_learning_objects.learning_object_id%type;
3923 l_lo_completed_date DATE;
3924 l_lo_comp_date DATE;
3925 l_lo_completed_time VARCHAR2(10);
3926 l_lo_completed_date_tz VARCHAR2(100);
3927 l_lo_lesson_status ota_performances.lesson_status%type;
3928 l_sync_flag ota_category_usages.synchronous_flag%type;
3929 l_online_flag ota_category_usages.online_flag%type;
3930 l_return_completion_date date:= null;
3931 l_decode_lesson_status VARCHAR2(1);
3932
3933 CURSOR c_get_lo_completion_date is
3934 Select
3935 oev.event_id,
3936 ofr.learning_object_id,
3937 opf.completed_date,
3938 to_char(opf.completed_date, 'HH24:MI:SS'),
3939 opf.lesson_status,
3940 decode(opf.lesson_status, 'P', '1',
3941 'C', '2',
3942 'F', '3',
3943 'I', '4',
3944 'B', '5',
3945 'N', '6') decode_lesson_status,
3946 ocu.Synchronous_Flag Sync_Flag,
3947 ocu.Online_Flag Online_Flag,
3948 ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
3949 ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
3950 From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3951 Where
3952 oev.parent_offering_id = ofr.offering_id
3953 And ofr.learning_object_id = opf.learning_object_id(+)
3954 And oev.event_id = p_event_id
3955 And opf.User_id(+) = p_user_id
3956 And opf.User_type(+) = p_user_type
3957 And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id
3958 order by decode_lesson_status;
3959
3960 CURSOR c_get_lo_completion_date_cert is
3961 Select
3962 oev.event_id,
3963 ofr.learning_object_id,
3964 opf.completed_date,
3965 to_char(opf.completed_date, 'HH24:MI:SS'),
3966 opf.lesson_status,
3967 ocu.Synchronous_Flag Sync_Flag,
3968 ocu.Online_Flag Online_Flag,
3969 ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
3970 ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
3971 From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
3972 Where
3973 oev.parent_offering_id = ofr.offering_id
3974 And ofr.learning_object_id = opf.learning_object_id(+)
3975 And oev.event_id = p_event_id
3976 And opf.User_id(+) = p_user_id
3977 And opf.User_type(+) = p_user_type
3978 And opf.cert_prd_enrollment_id(+) = p_cert_prd_enroll_id
3979 And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id;
3980
3981 Begin
3982 hr_utility.set_location('Entering :'||l_proc,5);
3983
3984 if(p_cert_prd_enroll_id is not null) then
3985 open c_get_lo_completion_date_cert;
3986 fetch c_get_lo_completion_date_cert 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;
3987 close c_get_lo_completion_date_cert;
3988 else
3989 open c_get_lo_completion_date;
3990 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_decode_lesson_status, l_sync_flag, l_online_flag, l_lo_completed_date_tz,l_lo_comp_date;
3991 close c_get_lo_completion_date;
3992 end if;
3993
3994 If ( l_online_flag = 'Y' and l_sync_flag = 'N'
3995 and l_lo_completed_date is not null and ( l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P') ) Then
3996
3997 IF (p_module_name = 'ADMIN' or p_module_name = 'LEARNER' or p_module_name = 'INSTRUCTOR')then -- Bug#7441027
3998 l_return_completion_date := l_lo_comp_date;
3999 END IF;
4000
4001 End If;
4002
4003
4004 RETURN l_return_completion_date;
4005 EXCEPTION
4006 WHEN others THEN
4007 hr_utility.set_location('Leaving :'||l_proc,15);
4008 RETURN l_return_completion_date;
4009 End get_lo_completion_date_time;
4010
4011 -- ----------------------------------------------------------------------------
4012 -- |-------------------------< get_cert_lo_status >--------------------------|
4013 -- ----------------------------------------------------------------------------
4014 -- {Start Of Comments}
4015 --
4016 -- Description:
4017 -- This function will fetch the player status for online classes and enrollment
4018 -- status for offline classes within the certification details.
4019 -- 12425778.Also shows cert memeber status when the learner is pending with signature.
4020 -- Pre Conditions:
4021 -- None.
4022 --
4023 -- Out Arguments:
4024 -- p_user_id
4025 -- p_user_type
4026 -- p_event_id
4027 -- p_booking_status_type_id
4028 -- p_booking_id
4029 -- p_cert_prd_enrollment_id
4030 -- p_mode
4031 --
4032 -- Post Success:
4033 -- Processing continues.
4034 --
4035 --
4036 -- Post Failure:
4037 -- None.
4038 --
4039 -- Access Status:
4040 -- Public.
4041 --
4042 -- {End Of Comments}
4043 ----------------------------------------------------------------------------
4044
4045
4046 FUNCTION get_cert_lo_status(p_user_id IN NUMBER,
4047 p_user_type IN ota_attempts.user_type%type,
4048 p_event_id IN ota_events.event_id%TYPE,
4049 p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
4050 p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
4051 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
4052 p_mode IN number default null)
4053 RETURN VARCHAR2 IS
4054
4055
4056 CURSOR c_delivery_mode IS
4057 SELECT ocu.online_flag, ofr.learning_object_id, nvl(oev.course_end_date, trunc(sysdate))
4058 FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
4059 WHERE oev.parent_offering_id = ofr.offering_id
4060 AND ofr.delivery_mode_id = ocu.category_usage_id
4061 AND oev.event_id = p_event_id;
4062
4063 CURSOR c_booking_status IS
4064 SELECT BST.type, BST.name
4065 FROM ota_booking_status_types_vl BST
4066 WHERE BST.booking_status_type_id = p_booking_status_type_id;
4067
4068 CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE) IS
4069 SELECT lesson_status,
4070 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4071 FROM ota_performances
4072 WHERE user_id = p_user_id
4073 AND user_type = p_user_type
4074 AND learning_object_id = l_lo_id
4075 AND cert_prd_enrollment_id = p_cert_prd_enrollment_id;
4076
4077 CURSOR csr_enr_sign_info IS
4078 SELECT odb.sign_eval_status FROM
4079 ota_delegate_bookings odb
4080 WHERE odb.booking_id = p_booking_id;
4081
4082 l_proc VARCHAR2(72) := g_package|| 'get_cert_lo_status';
4083
4084 l_online_flag ota_category_usages.online_flag%TYPE;
4085 l_learning_object_id ota_offerings.learning_object_id%TYPE;
4086 l_course_end_date ota_events.course_end_date%TYPE;
4087
4088 l_status VARCHAR2(30) := null;
4089 l_status_name ota_booking_status_types_tl.name%TYPE := null;
4090 l_sign_eval_status OTA_DELEGATE_BOOKINGS.sign_eval_status%type;
4091
4092 BEGIN
4093 hr_utility.set_location(' Step:'|| l_proc, 10);
4094 open c_delivery_mode;
4095 fetch c_delivery_mode into l_online_flag, l_learning_object_id, l_course_end_date;
4096 close c_delivery_mode;
4097
4098 -- Check for LO status for online classes
4099 If ( l_online_flag = 'Y' ) Then
4100
4101 -- OTA_PERFORMANCES for CERT_PRD_ENROLLMENT_ID
4102 open c_prd_lo_status(l_learning_object_id);
4103 fetch c_prd_lo_status into l_status, l_status_name;
4104 close c_prd_lo_status;
4105
4106 If ( l_status is null ) Then
4107 l_status := 'N';
4108 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4109 End If;
4110
4111 If ( (l_status = 'N') or (l_status = 'I') ) Then
4112 If ( l_course_end_date < trunc(sysdate) --Class is expired --Bug 3554773
4113 AND p_mode is null ) Then -- Bug 3594377
4114 l_status := 'E';
4115 l_status_name := ota_utility.get_message('OTA','OTA_443001_OFFR_EXPIRED_STATUS');
4116 End If;
4117 End If;
4118
4119 --Added for bug 12425778
4120 If (l_status = 'C' or l_status = 'P') Then
4121 open csr_enr_sign_info;
4122 fetch csr_enr_sign_info into l_sign_eval_status;
4123 close csr_enr_sign_info;
4124
4125 if(l_sign_eval_status in ('UE','VE','CE')) then
4126 l_status_name:= hr_general_utilities.get_lookup_meaning('OTA_CERT_MBR_ENROLL_STATUS','PENDING');
4127 end if;
4128
4129 End If;
4130 -- end of changes for 12425778
4131
4132 Else
4133 open c_booking_status;
4134 fetch c_booking_status into l_status, l_status_name;
4135 close c_booking_status;
4136 End If;
4137
4138 RETURN l_status_name;
4139 hr_utility.set_location(' Step:'|| l_proc, 20);
4140
4141 EXCEPTION
4142 WHEN others THEN
4143 hr_utility.set_location('Leaving :'||l_proc,30);
4144 RETURN NULL;
4145
4146 END get_cert_lo_status;
4147
4148 -- ----------------------------------------------------------------
4149 -- ------------------<get_cert_lo_title_for_tree >--------------------
4150 -- ----------------------------------------------------------------
4151 -- {Start of Comments}
4152 --
4153 -- Description:
4154 -- This function will be used within the certifications to show get
4155 -- lo name, status and time in the format
4156 -- lo_name [Status: status, Time: hh:mm:ss]
4157 -- if p_mode is 1 else it gives the lo status icon
4158 -- IN
4159 -- p_lo_id
4160 -- p_user_id
4161 -- p_user_type
4162 -- p_cert_prd_enrollment_id
4163 -- p_mode
4164 --
4165 -- Post Failure:
4166 -- None.
4167 -- Access Status
4168 -- Public
4169 -- {End of Comments}
4170 ------------------------------------------------------------------
4171
4172 FUNCTION get_cert_lo_title_for_tree(p_lo_id IN NUMBER,
4173 p_user_id IN NUMBER,
4174 p_user_type IN ota_attempts.user_type%type,
4175 p_cert_prd_enrollment_id IN ota_performances.cert_prd_enrollment_id%type,
4176 p_mode IN NUMBER default 1,
4177 p_scormattempid IN ota_performances.scorm_learner_attempt_id%type default null)
4178 RETURN varchar2
4179 IS
4180 l_proc VARCHAR2(72) := g_package||'get_cert_lo_title_for_tree';
4181 l_lo_name varchar(240);
4182 l_lo_status varchar(80);
4183 l_lo_time number;
4184 l_lo_score number;
4185 l_lo_status_icon varchar(30);
4186 l_Seconds number;
4187 l_Minutes number;
4188 l_Hours number;
4189 l_formatted_hour varchar(20) := '';
4190 l_formatted_min varchar(20) := '';
4191 l_formatted_sec varchar(20) := '';
4192 l_formatted_time varchar(20) := '';
4193 l_return_tree_title varchar(500) := '';
4194 l_max_performance_id ota_performances.performance_id%type;
4195 l_tst_grade_flag ota_tests.grade_flag%TYPE;
4196 l_var_score VARCHAR2(100);
4197 l_lo_completed_date VARCHAR2(100);
4198 l_lo_completed_time VARCHAR2(10);
4199 l_lo_completed_date_tz VARCHAR2(100);
4200 l_lo_lesson_status ota_performances.lesson_status%type;
4201 l_lo_completion_date_tz Date;
4202
4203 CURSOR c_get_cert_lo_tree_link(p_performance_id in number) is
4204 Select
4205 olo.name Name,
4206 nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
4207 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
4208 nvl(opf.time,0) Time,
4209 opf.score Score,
4210 decode(opf.lesson_status
4211 ,'C','player_status_c.gif'
4212 ,'F','player_status_f.gif'
4213 ,'I','player_status_i.gif'
4214 ,'P','player_status_p.gif'
4215 ,'N','player_status_n.gif'
4216 ,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type,'', p_cert_prd_enrollment_id),
4217 'N','player_status_no_prereq.gif',
4218 'Y','player_status_n.gif')
4219 ,'player_status_n.gif') STATUS_ICON
4220 , tst.grade_flag
4221 , to_char(opf.completed_date)
4222 , to_char(opf.completed_date, 'HH24:MI:SS')
4223 , opf.lesson_status
4224 , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
4225 , ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
4226 From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
4227 OTA_TESTS tst
4228 Where
4229 olo.learning_object_id = opf.learning_object_id(+)
4230 AND tst.test_id(+) = olo.test_id
4231 And olo.Learning_object_id = p_lo_id
4232 And opf.User_id(+) = p_user_id
4233 And opf.User_type(+) = p_user_type
4234 AND opf.performance_id(+) = p_performance_id
4235 And opf.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4236
4237 CURSOR c_get_cert_lo_tree_link_nn(p_performance_id in number) is
4238 Select
4239 olo.name Name,
4240 nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
4241 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
4242 nvl(opf.time,0) Time,
4243 opf.score Score,
4244 decode(opf.lesson_status
4245 ,'C','player_status_c.gif'
4246 ,'F','player_status_f.gif'
4247 ,'I','player_status_i.gif'
4248 ,'P','player_status_p.gif'
4249 ,'N','player_status_n.gif'
4250 ,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type,'', p_cert_prd_enrollment_id),
4251 'N','player_status_no_prereq.gif',
4252 'Y','player_status_n.gif')
4253 ,'player_status_n.gif') STATUS_ICON
4254 , tst.grade_flag
4255 , to_char(opf.completed_date)
4256 , to_char(opf.completed_date, 'HH24:MI:SS')
4257 , opf.lesson_status
4258 , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
4259 , ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code) Comp_Date
4260 From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
4261 OTA_TESTS tst
4262 Where
4263 olo.learning_object_id = opf.learning_object_id(+)
4264 AND tst.test_id(+) = olo.test_id
4265 And olo.Learning_object_id = p_lo_id
4266 And opf.User_id(+) = p_user_id
4267 And opf.User_type(+) = p_user_type
4268 And opf.scorm_learner_attempt_id = p_scormattempid
4269 AND opf.performance_id(+) = p_performance_id
4270 And opf.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4271
4272 CURSOR c_max_performance_id_scorm is
4273 Select max(per.performance_id)
4274 From ota_performances per
4275 Where per.learning_object_id(+) = p_lo_id
4276 And per.user_id(+) = p_user_id
4277 And per.user_type(+) = p_user_type
4278 And per.scorm_learner_attempt_id = p_scormattempid
4279 And per.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4280
4281 CURSOR c_max_performance_id is
4282 Select max(per.performance_id)
4283 From ota_performances per
4284 Where per.learning_object_id(+) = p_lo_id
4285 And per.user_id(+) = p_user_id
4286 And per.user_type(+) = p_user_type
4287 And per.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
4288 Begin
4289 hr_utility.set_location('Entering :'||l_proc,5);
4290
4291 if(p_scormattempid is null) then
4292 open c_max_performance_id;
4293 fetch c_max_performance_id into l_max_performance_id;
4294 close c_max_performance_id;
4295 else
4296 open c_max_performance_id_scorm;
4297 fetch c_max_performance_id_scorm into l_max_performance_id;--14000818
4298 close c_max_performance_id_scorm;
4299 end if;
4300
4301 if ( l_max_performance_id is null ) then
4302 l_max_performance_id := -1;
4303 end if;
4304
4305
4306 if(p_scormattempid is null) then
4307 open c_get_cert_lo_tree_link(l_max_performance_id);
4308 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,l_lo_completion_date_tz;
4309 close c_get_cert_lo_tree_link;
4310 else
4311 open c_get_cert_lo_tree_link_nn(l_max_performance_id);
4312 fetch c_get_cert_lo_tree_link_nn 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,l_lo_completion_date_tz;
4313 close c_get_cert_lo_tree_link_nn;
4314
4315 end if ;
4316 If ( p_mode = 1) Then
4317 l_lo_time := round(l_lo_time);
4318
4319 l_Seconds := l_lo_time mod 60;
4320 l_Minutes := floor(l_lo_time / 60);
4321 l_Hours := floor(l_Minutes/60);
4322 l_Minutes := l_Minutes - l_Hours * 60;
4323
4324 If (l_Hours < 10) Then
4325 l_formatted_hour := '0' || l_Hours;
4326 Else
4327 l_formatted_hour := l_Hours;
4328 End If;
4329
4330 If (l_Minutes < 10) Then
4331 l_formatted_min := '0' || l_Minutes;
4332 Else
4333 l_formatted_min := l_Minutes;
4334 End If;
4335
4336 If (l_Seconds < 10) Then
4337 l_formatted_sec := '0' || l_Seconds;
4338 Else
4339 l_formatted_sec := l_Seconds;
4340 End If;
4341
4342 fnd_message.set_name('OTA', 'OTA_443358_SRCH_LO_TIME');
4343 fnd_message.set_token ('HOUR', l_formatted_hour);
4344 fnd_message.set_token ('MIN', l_formatted_min);
4345 fnd_message.set_token ('SEC', l_formatted_sec);
4346 l_formatted_time := fnd_message.get();
4347
4348 If ((l_lo_score is null) or (l_lo_score < 0)) Then
4349 fnd_message.set_name('OTA', 'OTA_443453_SRCH_TREE_TITLE2');
4350 Else
4351 --Added for bug 3550407
4352 IF ( l_tst_grade_flag = 'P' ) THEN
4353 l_var_score := l_lo_score||'%';
4354 ELSE
4355 l_var_score := l_lo_score;
4356 END IF;
4357
4358 fnd_message.set_name('OTA', 'OTA_443357_SRCH_TREE_TITLE');
4359 fnd_message.set_token ('SCORE', l_var_score);
4360 End If;
4361
4362 If ( (l_lo_lesson_status = 'C' or l_lo_lesson_status = 'P')
4363 and l_lo_completed_date is not null ) Then
4364 -- l_lo_status := l_lo_status || ' ' || l_lo_completed_date_tz; Modified for iCal Bug 12696284
4365 l_lo_status := l_lo_status || ' ' || fnd_date.date_to_displaydate(l_lo_completion_date_tz,2);
4366 End If;
4367
4368 fnd_message.set_token ('LO_NAME', l_lo_name);
4369 fnd_message.set_token ('STATUS', l_lo_status);
4370 fnd_message.set_token ('TIME', l_formatted_time);
4371
4372 l_return_tree_title := fnd_message.get();
4373 Else
4374 l_return_tree_title := l_lo_status_icon;
4375 End If;
4376
4377 hr_utility.set_location('Leaving :'||l_proc,10);
4378 RETURN l_return_tree_title;
4379 EXCEPTION
4380 WHEN others THEN
4381 hr_utility.set_location('Leaving :'||l_proc,15);
4382
4383 RETURN l_return_tree_title;
4384 End get_cert_lo_title_for_tree;
4385
4386 function get_cme_online_event_id(p_user_id in fnd_user.user_id%type,
4387 p_user_type in ota_attempts.user_type%type,
4388 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4389 return varchar2 is
4390 CURSOR csr_cme_info IS
4391 SELECT cme.cert_mbr_enrollment_id,
4392 cpe.cert_prd_enrollment_id,
4393 cme.object_version_number,
4394 cmb.object_id,
4395 cmb.certification_member_id,
4396 cme.member_status_code
4397 FROM ota_certification_members cmb,
4398 ota_cert_mbr_enrollments cme,
4399 ota_cert_prd_enrollments cpe
4400 WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
4401 AND cme.cert_member_id = cmb.certification_member_id
4402 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4403
4404 CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4405 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
4406 FROM ota_cert_enrollments cre,
4407 ota_cert_prd_enrollments cpe
4408 where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4409 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
4410
4411
4412 CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
4413 csr_cert_period_start_date in date,
4414 csr_cert_period_end_date in date,
4415 csr_person_id in number,
4416 csr_contact_id in number) IS
4417
4418 SELECT bst.type status,
4419 tdb.DATE_STATUS_CHANGED,
4420 evt.event_id,
4421 evt.event_type,
4422 ocu.synchronous_flag,
4423 ocu.online_flag,
4424 bst.type
4425 FROM ota_events evt,
4426 ota_delegate_bookings tdb,
4427 ota_booking_status_types bst,
4428 ota_offerings ofr,
4429 ota_category_usages ocu
4430 WHERE evt.event_id = tdb.event_id
4431 AND bst.booking_status_type_id = tdb.booking_status_type_id
4432 /* AND (
4433 evt.course_start_date >= csr_cert_period_start_date
4434 AND
4435 (
4436 evt.course_end_date IS NOT NULL
4437 AND evt.course_end_date <= csr_cert_period_end_date
4438 )
4439 OR
4440 (
4441 evt.event_type = 'SELFPACED'
4442 AND csr_cert_period_end_date >= evt.course_start_date
4443 )
4444 )
4445 */
4446 ---
4447 AND ( ( evt.course_start_date >= csr_cert_period_start_date and
4448 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= csr_cert_period_end_date )
4449 /* Bug 4515924
4450 or (evt.event_type ='SELFPACED' and
4451 evt.course_start_date< csr_cert_period_end_date AND
4452 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= csr_cert_period_end_date ))*/
4453 or (evt.event_type = 'SELFPACED' AND
4454 ((csr_cert_period_end_date >= evt.course_start_date) AND
4455 ((evt.course_end_date is null) or
4456 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))
4457 )))
4458 ---
4459 AND evt.activity_version_id = csr_activity_version_id
4460 --AND tdb.delegate_person_id = p_person_id
4461 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
4462 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
4463 )
4464 AND evt.parent_offering_id = ofr.offering_id
4465 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
4466 AND tdb.booking_status_type_id = bst.booking_status_type_id
4467 AND bst.type <> 'C';
4468
4469 l_proc VARCHAR2(72) := g_package||'get_cme_online_event_id';
4470 rec_cme_info csr_cme_info%rowtype;
4471
4472 l_person_id number;
4473 l_contact_id number;
4474 l_cert_period_start_date date;
4475 l_cert_period_end_date date;
4476
4477 l_online_event_id ota_events.event_id%type;
4478 l_online_evt_count number;
4479
4480 BEGIN
4481 hr_utility.set_location('Entering :'||l_proc,5);
4482
4483 --check for mult online evts and throw null if so
4484 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4485
4486 if (l_online_evt_count > 1) then
4487 return null;
4488 end if;
4489
4490 open csr_cme_info;
4491 fetch csr_cme_info into rec_cme_info;
4492 close csr_cme_info;
4493
4494 OPEN csr_cert_enrl(rec_cme_info.cert_prd_enrollment_id);
4495 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
4496 CLOSE csr_cert_enrl;
4497
4498 hr_utility.set_location(' Step:'||l_proc,10);
4499
4500 FOR rec IN get_enrl_status_on_update(rec_cme_info.object_id,
4501 l_cert_period_start_date,
4502 l_cert_period_end_date,
4503 l_person_id,
4504 l_contact_id)
4505 LOOP
4506
4507 if rec.online_flag = 'Y' then
4508 l_online_event_id := rec.event_id;
4509 exit;
4510 end if;
4511
4512
4513 END LOOP;
4514
4515 hr_utility.set_location('Leaving :'||l_proc,10);
4516
4517 return l_online_event_id;
4518
4519 EXCEPTION
4520 WHEN others THEN
4521 hr_utility.set_location('Leaving :'||l_proc,15);
4522
4523 RETURN null;
4524
4525 End get_cme_online_event_id;
4526
4527 function get_cme_play_button(p_user_id in fnd_user.user_id%type,
4528 p_user_type in ota_attempts.user_type%type,
4529 p_is_manager in varchar2,
4530 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4531 return varchar2 is
4532
4533 CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4534 SELECT
4535 evt.event_id,
4536 evt.event_type,
4537 ocu.synchronous_flag,
4538 ocu.online_flag,
4539 evt.course_start_date,
4540 evt.course_end_date
4541 FROM ota_events evt,
4542 ota_offerings ofr,
4543 ota_category_usages ocu
4544 WHERE evt.event_id = csr_event_id
4545 AND evt.parent_offering_id = ofr.offering_id
4546 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4547
4548 CURSOR csr_cme is
4549 select cert_prd_enrollment_id
4550 from ota_cert_mbr_enrollments
4551 where cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4552
4553 l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
4554 rec csr_event%rowtype;
4555 rec_cme csr_cme%rowtype;
4556 l_online_event_id ota_events.event_id%type;
4557
4558 l_return_play_btn VARCHAR2(30) := 'DISABLE_NOT_ENROLLED';
4559 l_online_evt_count number;
4560
4561 BEGIN
4562 hr_utility.set_location('Entering :'||l_proc,5);
4563
4564 --check for mult online evts and throw null if so
4565 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4566
4567 if (l_online_evt_count > 1) then
4568 return 'DISABLE_MULT_ENRL_EVENTS';
4569 end if;
4570
4571 open csr_cme;
4572 fetch csr_cme into rec_cme;
4573 close csr_cme;
4574
4575 l_online_event_id := get_cme_online_event_id(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4576
4577 open csr_event(l_online_event_id);
4578 fetch csr_event into rec;
4579 close csr_event;
4580
4581 if (l_online_event_id is not null) then
4582 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
4583 ,rec.SYNCHRONOUS_FLAG, rec.ONLINE_FLAG
4584 ,rec.COURSE_START_DATE ,rec.COURSE_END_DATE, null,rec_cme.cert_prd_enrollment_id);
4585 end if;
4586 hr_utility.set_location('Leaving :'||l_proc,10);
4587
4588 return l_return_play_btn;
4589
4590 EXCEPTION
4591 WHEN others THEN
4592 hr_utility.set_location('Leaving :'||l_proc,15);
4593
4594 RETURN null;
4595
4596 End get_cme_play_button;
4597
4598
4599 function get_cme_player_toolbar_flag(p_user_id in fnd_user.user_id%type,
4600 p_user_type in ota_attempts.user_type%type,
4601 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4602 return varchar2 is
4603
4604 cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
4605 select ofr.player_toolbar_flag
4606 from ota_events evt,
4607 ota_offerings ofr
4608 where evt.parent_offering_id = ofr.offering_id
4609 and evt.event_id = csr_event_id;
4610
4611 l_proc VARCHAR2(72) := g_package||'get_cme_Player_Toolbar_Flag';
4612
4613 l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
4614
4615 l_online_event_id ota_events.event_id%type;
4616 l_online_evt_count number;
4617
4618 BEGIN
4619 hr_utility.set_location('Entering :'||l_proc,5);
4620
4621 --check for mult online evts and throw null if so
4622 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4623
4624 if (l_online_evt_count > 1) then
4625 return null;
4626 end if;
4627
4628 l_online_event_id := get_cme_online_event_id(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4629
4630 open csr_toolbar_flag(l_online_event_id);
4631 fetch csr_toolbar_flag into l_player_toolbar_flag;
4632 close csr_toolbar_flag;
4633
4634 hr_utility.set_location('Leaving :'||l_proc,10);
4635
4636 return l_player_toolbar_flag;
4637
4638 EXCEPTION
4639 WHEN others THEN
4640 hr_utility.set_location('Leaving :'||l_proc,15);
4641
4642 RETURN null;
4643
4644 End get_cme_player_toolbar_flag;
4645
4646 function get_cert_lo_status(p_user_id in fnd_user.user_id%type,
4647 p_user_type in ota_attempts.user_type%type,
4648 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4649 return varchar2 is
4650
4651 CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
4652 SELECT
4653 evt.event_id,
4654 evt.event_type,
4655 ocu.synchronous_flag,
4656 ocu.online_flag,
4657 evt.course_start_date,
4658 evt.course_end_date,
4659 ofr.learning_object_id
4660 FROM ota_events evt,
4661 ota_offerings ofr,
4662 ota_category_usages ocu
4663 WHERE evt.event_id = csr_event_id
4664 AND evt.parent_offering_id = ofr.offering_id
4665 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
4666
4667 CURSOR csr_cme is
4668 select cert_prd_enrollment_id
4669 from ota_cert_mbr_enrollments
4670 where cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4671
4672
4673 CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE,
4674 csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4675 SELECT lesson_status,
4676 hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4677 FROM ota_performances
4678 WHERE user_id = p_user_id
4679 AND user_type = p_user_type
4680 AND learning_object_id = l_lo_id
4681 AND cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
4682
4683 cursor csr_get_act is
4684 select cmb.object_id
4685 from ota_certification_members cmb,
4686 ota_cert_mbr_enrollments cme
4687 where cmb.certification_member_id = cme.cert_member_id
4688 and cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
4689 and cmb.object_type = 'H';
4690
4691 CURSOR csr_prf_ord(p_activity_version_id in ota_activity_versions.activity_version_id%type,
4692 p_user_id in number,
4693 p_user_type in varchar2,
4694 csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4695 SELECT prf.user_id,
4696 lo.learning_object_id,
4697 decode(prf.lesson_status, 'P', '1',
4698 'C', '2',
4699 'F', '3',
4700 'I', '4',
4701 'B', '5',
4702 'N', '6') decode_lesson_status,
4703 prf.lesson_status lesson_status,
4704 prf.performance_id
4705 FROM ota_performances prf,
4706 ota_offerings ofr,
4707 ota_learning_objects lo
4708 WHERE
4709 prf.user_id = p_user_id
4710 and prf.user_type = p_user_type
4711 and lo.learning_object_id = prf.learning_object_id
4712 AND prf.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4713 and ofr.learning_object_id = lo.learning_object_id
4714 and ofr.activity_version_id = p_activity_version_id
4715 order by decode_lesson_status;
4716
4717 l_proc VARCHAR2(72) := g_package||'get_cert_lo_status';
4718 rec csr_event%rowtype;
4719 l_online_event_id ota_events.event_id%type;
4720
4721 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
4722
4723 l_status VARCHAR2(30) := null;
4724 l_status_name ota_booking_status_types_tl.name%TYPE := null;
4725
4726 l_return_lo_status VARCHAR2(30);
4727 l_online_evt_count number;
4728
4729 rec_prf_ord csr_prf_ord%rowtype;
4730 rec_get_act csr_get_act%rowtype;
4731
4732 BEGIN
4733 hr_utility.set_location('Entering :'||l_proc,5);
4734
4735 open csr_cme;
4736 fetch csr_cme into l_cert_prd_enrollment_id;
4737 close csr_cme;
4738
4739 --check for mult online evts and throw null if so
4740 l_online_evt_count := get_cme_onl_evt_count(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4741
4742 if (l_online_evt_count > 1) then
4743 --Bug 4560354
4744 --return as Not Attempted
4745 --l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4746 --return l_return_lo_status;
4747 open csr_get_act;
4748 fetch csr_get_act into rec_get_act;
4749 close csr_get_act;
4750
4751 if rec_get_act.object_id is not null then
4752 open csr_prf_ord(rec_get_act.object_id, p_user_id, p_user_type, l_cert_prd_enrollment_id);
4753 fetch csr_prf_ord into rec_prf_ord;
4754 close csr_prf_ord;
4755
4756 if rec_prf_ord.lesson_status is not null then
4757 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',rec_prf_ord.lesson_status);
4758 else
4759 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4760 end if;
4761 end if;
4762
4763 return l_return_lo_status;
4764 end if;
4765
4766 l_online_event_id := get_cme_online_event_id(p_user_id, p_user_type, p_cert_mbr_enrollment_id);
4767
4768 open csr_event(l_online_event_id);
4769 fetch csr_event into rec;
4770 close csr_event;
4771
4772 open c_prd_lo_status(rec.learning_object_id, l_cert_prd_enrollment_id);
4773 fetch c_prd_lo_status into l_status, l_status_name;
4774 close c_prd_lo_status;
4775
4776 If ( l_status is null ) Then
4777 l_status := 'N';
4778 l_status_name := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
4779 End If;
4780
4781 If ( (l_status = 'N') or (l_status = 'I') ) Then
4782 If ( rec.course_end_date < trunc(sysdate) --Class is expired --Bug 3554773
4783 -- AND p_mode is null ) Then -- Bug 3594377
4784 ) then
4785 l_status := 'E';
4786 l_status_name := ota_utility.get_message('OTA','OTA_443001_OFFR_EXPIRED_STATUS');
4787 End If;
4788 End If;
4789
4790 hr_utility.set_location('Leaving :'||l_proc,10);
4791
4792 return l_status_name;
4793
4794 EXCEPTION
4795 WHEN others THEN
4796 hr_utility.set_location('Leaving :'||l_proc,15);
4797
4798 RETURN null;
4799
4800 End get_cert_lo_status;
4801
4802 function get_cme_onl_evt_count(p_user_id in fnd_user.user_id%type,
4803 p_user_type in ota_attempts.user_type%type,
4804 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
4805 return varchar2 is
4806 CURSOR csr_cme_info IS
4807 SELECT cme.cert_mbr_enrollment_id,
4808 cpe.cert_prd_enrollment_id,
4809 cme.object_version_number,
4810 cmb.object_id,
4811 cmb.certification_member_id,
4812 cme.member_status_code
4813 FROM ota_certification_members cmb,
4814 ota_cert_mbr_enrollments cme,
4815 ota_cert_prd_enrollments cpe
4816 WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
4817 AND cme.cert_member_id = cmb.certification_member_id
4818 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4819
4820 CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4821 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
4822 FROM ota_cert_enrollments cre,
4823 ota_cert_prd_enrollments cpe
4824 where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4825 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
4826
4827
4828 CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
4829 csr_cert_period_start_date in date,
4830 csr_cert_period_end_date in date,
4831 csr_person_id in number,
4832 csr_contact_id in number) IS
4833
4834 SELECT bst.type status,
4835 tdb.DATE_STATUS_CHANGED,
4836 evt.event_id,
4837 evt.event_type,
4838 ocu.synchronous_flag,
4839 ocu.online_flag,
4840 bst.type
4841 FROM ota_events evt,
4842 ota_delegate_bookings tdb,
4843 ota_booking_status_types bst,
4844 ota_offerings ofr,
4845 ota_category_usages ocu
4846 WHERE evt.event_id = tdb.event_id
4847 AND bst.booking_status_type_id = tdb.booking_status_type_id
4848 /* AND (
4849 evt.course_start_date >= csr_cert_period_start_date
4850 AND
4851 (
4852 evt.course_end_date IS NOT NULL
4853 AND evt.course_end_date <= csr_cert_period_end_date
4854 )
4855 OR
4856 (
4857 evt.event_type = 'SELFPACED'
4858 AND csr_cert_period_end_date >= evt.course_start_date
4859 )
4860 )
4861 */
4862 ---
4863 AND ( ( evt.course_start_date >= csr_cert_period_start_date and
4864 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= csr_cert_period_end_date )
4865 /* Bug 4515924 or (evt.event_type ='SELFPACED' and
4866 evt.course_start_date< csr_cert_period_end_date AND
4867 nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= csr_cert_period_end_date ))*/
4868 or (evt.event_type = 'SELFPACED' AND
4869 ((csr_cert_period_end_date >= evt.course_start_date) AND
4870 ((evt.course_end_date is null) or
4871 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))
4872 )))
4873 ---
4874 AND evt.activity_version_id = csr_activity_version_id
4875 --AND tdb.delegate_person_id = p_person_id
4876 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
4877 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
4878 )
4879 AND evt.parent_offering_id = ofr.offering_id
4880 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
4881 AND tdb.booking_status_type_id = bst.booking_status_type_id
4882 AND bst.type <> 'C';
4883
4884 l_proc VARCHAR2(72) := g_package||'get_cme_onl_evt_count';
4885 rec_cme_info csr_cme_info%rowtype;
4886
4887 l_person_id number;
4888 l_contact_id number;
4889 l_cert_period_start_date date;
4890 l_cert_period_end_date date;
4891
4892 l_online_event_id ota_events.event_id%type;
4893 l_online_event_count number := 0;
4894
4895 BEGIN
4896 hr_utility.set_location('Entering :'||l_proc,5);
4897
4898
4899 open csr_cme_info;
4900 fetch csr_cme_info into rec_cme_info;
4901 close csr_cme_info;
4902
4903 OPEN csr_cert_enrl(rec_cme_info.cert_prd_enrollment_id);
4904 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
4905 CLOSE csr_cert_enrl;
4906
4907 hr_utility.set_location(' Step:'||l_proc,10);
4908
4909 FOR rec IN get_enrl_status_on_update(rec_cme_info.object_id,
4910 l_cert_period_start_date,
4911 l_cert_period_end_date,
4912 l_person_id,
4913 l_contact_id)
4914 LOOP
4915
4916 if rec.online_flag = 'Y' then
4917 l_online_event_id := rec.event_id;
4918 l_online_event_count := l_online_event_count + 1;
4919 --exit;
4920 end if;
4921
4922
4923 END LOOP;
4924
4925 hr_utility.set_location('Leaving :'||l_proc,10);
4926
4927 return '' || l_online_event_count;
4928
4929 EXCEPTION
4930 WHEN others THEN
4931 hr_utility.set_location('Leaving :'||l_proc,15);
4932
4933 RETURN null;
4934
4935 End get_cme_onl_evt_count;
4936
4937 -- ----------------------------------------------------------------
4938 -- -----------------------< format_lo_time >-----------------------
4939 -- ----------------------------------------------------------------
4940 -- {Start of Comments}
4941 --
4942 -- Description:
4943 -- This function formats time in HH:MM:SS format
4944 --
4945 -- IN
4946 -- pTime
4947 --
4948 -- Post Failure:
4949 -- None.
4950 -- Access Status
4951 -- Public
4952 -- {End of Comments}
4953 ------------------------------------------------------------------
4954 Function format_lo_time(pTime ota_performances.time%type)
4955 return varchar2 IS
4956
4957 l_proc VARCHAR2(72) := g_package||'format_lo_time';
4958 l_lo_time number;
4959 l_Seconds number;
4960 l_Minutes number;
4961 l_Hours number;
4962 l_formatted_hour varchar(20) := '';
4963 l_formatted_min varchar(20) := '';
4964 l_formatted_sec varchar(20) := '';
4965 l_formatted_time varchar(20) := '';
4966
4967 Begin
4968 If (pTime is null) Then
4969 l_lo_time := 0;
4970 Else
4971 l_lo_time := pTime;
4972 End If;
4973
4974 l_lo_time := round(pTime);
4975
4976 l_Seconds := l_lo_time mod 60;
4977 l_Minutes := floor(l_lo_time / 60);
4978 l_Hours := floor(l_Minutes/60);
4979 l_Minutes := l_Minutes - l_Hours * 60;
4980
4981 If (l_Hours < 10) Then
4982 l_formatted_hour := '0' || l_Hours;
4983 Else
4984 l_formatted_hour := l_Hours;
4985 End If;
4986
4987 If (l_Minutes < 10) Then
4988 l_formatted_min := '0' || l_Minutes;
4989 Else
4990 l_formatted_min := l_Minutes;
4991 End If;
4992
4993 If (l_Seconds < 10) Then
4994 l_formatted_sec := '0' || l_Seconds;
4995 Else
4996 l_formatted_sec := l_Seconds;
4997 End If;
4998
4999 fnd_message.set_name('OTA', 'OTA_443358_SRCH_LO_TIME');
5000 fnd_message.set_token ('HOUR', l_formatted_hour);
5001 fnd_message.set_token ('MIN', l_formatted_min);
5002 fnd_message.set_token ('SEC', l_formatted_sec);
5003 l_formatted_time := fnd_message.get();
5004 return l_formatted_time;
5005 End format_lo_time;
5006
5007
5008 function get_lme_online_event_id(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5009 return varchar2 is
5010 CURSOR csr_lme_info IS
5011 SELECT lme.lp_member_enrollment_id,
5012 lmb.ACTIVITY_VERSION_ID,
5013 lpe.lp_enrollment_id,
5014 lme.object_version_number,
5015 lmb.learning_path_member_id,
5016 lme.member_status_code,
5017 lpe.person_id,
5018 lpe.contact_id
5019 FROM ota_learning_path_members lmb,
5020 ota_lp_member_enrollments lme,
5021 ota_lp_enrollments lpe
5022 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
5023 AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
5024 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
5025
5026
5027 CURSOR get_enrl_status(csr_activity_version_id ota_events.activity_version_id%type,
5028 csr_person_id in number,
5029 csr_contact_id in number) IS
5030 SELECT bst.type status,
5031 tdb.DATE_STATUS_CHANGED,
5032 evt.event_id,
5033 evt.event_type,
5034 ocu.synchronous_flag,
5035 ocu.online_flag,
5036 bst.type
5037 FROM ota_events evt,
5038 ota_delegate_bookings tdb,
5039 ota_booking_status_types bst,
5040 ota_offerings ofr,
5041 ota_category_usages ocu
5042 WHERE evt.event_id = tdb.event_id
5043 AND bst.booking_status_type_id = tdb.booking_status_type_id
5044 AND evt.activity_version_id = csr_activity_version_id
5045 --AND tdb.delegate_person_id = p_person_id
5046 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
5047 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
5048 )
5049 AND evt.parent_offering_id = ofr.offering_id
5050 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
5051 AND tdb.booking_status_type_id = bst.booking_status_type_id
5052 AND bst.type <> 'C';
5053
5054 l_proc VARCHAR2(72) := g_package||'get_lme_online_event_id';
5055 rec_lme_info csr_lme_info%rowtype;
5056
5057 l_person_id number;
5058 l_contact_id number;
5059 l_cert_period_start_date date;
5060 l_cert_period_end_date date;
5061
5062 l_online_event_id ota_events.event_id%type;
5063 l_online_evt_count number;
5064
5065 BEGIN
5066 hr_utility.set_location('Entering :'||l_proc,5);
5067
5068 --check for mult online evts and throw null if so
5069 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
5070
5071 if (l_online_evt_count > 1) then
5072 return null;
5073 end if;
5074
5075 open csr_lme_info;
5076 fetch csr_lme_info into rec_lme_info;
5077 close csr_lme_info;
5078
5079 hr_utility.set_location(' Step:'||l_proc,10);
5080
5081 FOR rec IN get_enrl_status(rec_lme_info.activity_version_id,
5082 rec_lme_info.person_id,
5083 rec_lme_info.contact_id)
5084 LOOP
5085
5086 if rec.online_flag = 'Y' then
5087 l_online_event_id := rec.event_id;
5088 exit;
5089 end if;
5090
5091
5092 END LOOP;
5093
5094 hr_utility.set_location('Leaving :'||l_proc,10);
5095
5096 return l_online_event_id;
5097
5098 EXCEPTION
5099 WHEN others THEN
5100 hr_utility.set_location('Leaving :'||l_proc,15);
5101
5102 RETURN null;
5103
5104 End get_lme_online_event_id;
5105
5106 function get_lme_play_button(p_user_id in fnd_user.user_id%type,
5107 p_user_type in ota_attempts.user_type%type,
5108 p_is_manager in varchar2,
5109 p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5110 return varchar2 is
5111
5112 CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
5113 SELECT
5114 evt.event_id,
5115 evt.event_type,
5116 ocu.synchronous_flag,
5117 ocu.online_flag,
5118 evt.course_start_date,
5119 evt.course_end_date
5120 FROM ota_events evt,
5121 ota_offerings ofr,
5122 ota_category_usages ocu
5123 WHERE evt.event_id = csr_event_id
5124 AND evt.parent_offering_id = ofr.offering_id
5125 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
5126
5127 l_proc VARCHAR2(72) := g_package||'get_lme_play_button';
5128 rec csr_event%rowtype;
5129 l_online_event_id ota_events.event_id%type;
5130
5131 l_return_play_btn VARCHAR2(30) := 'DISABLE_NOT_ENROLLED';
5132 l_online_evt_count number;
5133
5134 BEGIN
5135 hr_utility.set_location('Entering :'||l_proc,5);
5136
5137 --check for mult online evts and throw null if so
5138 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
5139
5140 if (l_online_evt_count > 1) then
5141 return 'DISABLE_MULT_ENRL_EVENTS';
5142 end if;
5143
5144 l_online_event_id := get_lme_online_event_id(p_lp_member_enrollment_id);
5145
5146 open csr_event(l_online_event_id);
5147 fetch csr_event into rec;
5148 close csr_event;
5149
5150 if (l_online_event_id is not null) then
5151 l_return_play_btn := GET_PLAY_BUTTON(p_user_id,p_user_type, p_is_manager, rec.EVENT_ID, rec.EVENT_TYPE
5152 ,rec.SYNCHRONOUS_FLAG, rec.ONLINE_FLAG
5153 ,rec.COURSE_START_DATE ,rec.COURSE_END_DATE);
5154 end if;
5155 hr_utility.set_location('Leaving :'||l_proc,10);
5156
5157 return l_return_play_btn;
5158
5159 EXCEPTION
5160 WHEN others THEN
5161 hr_utility.set_location('Leaving :'||l_proc,15);
5162
5163 RETURN null;
5164
5165 End get_lme_play_button;
5166
5167
5168 function get_lme_player_toolbar_flag(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5169 return varchar2 is
5170
5171 cursor csr_toolbar_flag(csr_event_id ota_events.event_id%type) is
5172 select ofr.player_toolbar_flag
5173 from ota_events evt,
5174 ota_offerings ofr
5175 where evt.parent_offering_id = ofr.offering_id
5176 and evt.event_id = csr_event_id;
5177
5178 l_proc VARCHAR2(72) := g_package||'get_lme_player_toolbar_flag';
5179
5180 l_player_toolbar_flag ota_offerings.player_toolbar_flag%type;
5181
5182 l_online_event_id ota_events.event_id%type;
5183 l_online_evt_count number;
5184
5185 BEGIN
5186 hr_utility.set_location('Entering :'||l_proc,5);
5187
5188 --check for mult online evts and throw null if so
5189 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
5190
5191 if (l_online_evt_count > 1) then
5192 return null;
5193 end if;
5194
5195 l_online_event_id := get_lme_online_event_id(p_lp_member_enrollment_id);
5196
5197 open csr_toolbar_flag(l_online_event_id);
5198 fetch csr_toolbar_flag into l_player_toolbar_flag;
5199 close csr_toolbar_flag;
5200
5201 hr_utility.set_location('Leaving :'||l_proc,10);
5202
5203 return l_player_toolbar_flag;
5204
5205 EXCEPTION
5206 WHEN others THEN
5207 hr_utility.set_location('Leaving :'||l_proc,15);
5208
5209 RETURN null;
5210
5211 End get_lme_player_toolbar_flag;
5212
5213 function get_lpe_lo_status(p_user_id in fnd_user.user_id%type,
5214 p_user_type in ota_attempts.user_type%type,
5215 p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5216 return varchar2 is
5217
5218 CURSOR csr_event(csr_event_id ota_events.event_id%type) IS
5219 SELECT
5220 evt.event_id,
5221 evt.event_type,
5222 ocu.synchronous_flag,
5223 ocu.online_flag,
5224 evt.course_start_date,
5225 evt.course_end_date,
5226 ofr.learning_object_id
5227 FROM ota_events evt,
5228 ota_offerings ofr,
5229 ota_category_usages ocu
5230 WHERE evt.event_id = csr_event_id
5231 AND evt.parent_offering_id = ofr.offering_id
5232 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
5233
5234 cursor csr_get_act is
5235 select lpm.activity_version_id
5236 from ota_learning_path_members lpm,
5237 ota_lp_member_enrollments lme
5238 where lpm.LEARNING_PATH_MEMBER_ID = lme.LEARNING_PATH_MEMBER_ID
5239 and lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
5240
5241
5242 CURSOR csr_prf_ord(p_activity_version_id in ota_activity_versions.activity_version_id%type,
5243 p_user_id in number,
5244 p_user_type in varchar2) IS
5245 SELECT prf.user_id,
5246 lo.learning_object_id,
5247 decode(prf.lesson_status, 'P', '1',
5248 'C', '2',
5249 'F', '3',
5250 'I', '4',
5251 'B', '5',
5252 'N', '6') decode_lesson_status,
5253 prf.lesson_status lesson_status,
5254 prf.performance_id
5255 FROM ota_performances prf,
5256 ota_offerings ofr,
5257 ota_learning_objects lo
5258 WHERE
5259 prf.user_id = p_user_id
5260 and prf.user_type = p_user_type
5261 and lo.learning_object_id = prf.learning_object_id
5262 and prf.cert_prd_enrollment_id is null
5263 and ofr.learning_object_id = lo.learning_object_id
5264 and ofr.activity_version_id = p_activity_version_id
5265 order by decode_lesson_status;
5266
5267 l_proc VARCHAR2(72) := g_package||'get_lpe_lo_status';
5268 rec csr_event%rowtype;
5269 rec_prf_ord csr_prf_ord%rowtype;
5270 rec_get_act csr_get_act%rowtype;
5271
5272 l_online_event_id ota_events.event_id%type;
5273
5274 l_status VARCHAR2(30) := null;
5275 l_status_name ota_booking_status_types_tl.name%TYPE := null;
5276
5277 l_return_lo_status VARCHAR2(30);
5278 l_online_evt_count number;
5279 BEGIN
5280 hr_utility.set_location('Entering :'||l_proc,5);
5281
5282 --check for mult online evts and throw null if so
5283 l_online_evt_count := get_lme_onl_evt_count(p_lp_member_enrollment_id);
5284
5285 if (l_online_evt_count > 1) then
5286 --return as Not Attempted
5287 --Bug 4560354
5288 --l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
5289 open csr_get_act;
5290 fetch csr_get_act into rec_get_act;
5291 close csr_get_act;
5292
5293 if rec_get_act.activity_version_id is not null then
5294 open csr_prf_ord(rec_get_act.activity_version_id, p_user_id, p_user_type);
5295 fetch csr_prf_ord into rec_prf_ord;
5296 close csr_prf_ord;
5297
5298 if rec_prf_ord.lesson_status is not null then
5299 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',rec_prf_ord.lesson_status);
5300 else
5301 l_return_lo_status := hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N');
5302 end if;
5303 end if;
5304
5305 return l_return_lo_status;
5306 end if;
5307
5308 l_online_event_id := get_lme_online_event_id(p_lp_member_enrollment_id);
5309
5310 open csr_event(l_online_event_id);
5311 fetch csr_event into rec;
5312 close csr_event;
5313
5314 l_status_name := get_enroll_lo_status(p_user_id, p_user_type, rec.event_id, null, null, 1);
5315
5316 hr_utility.set_location('Leaving :'||l_proc,10);
5317
5318 return l_status_name;
5319
5320 EXCEPTION
5321 WHEN others THEN
5322 hr_utility.set_location('Leaving :'||l_proc,15);
5323
5324 RETURN null;
5325
5326 End get_lpe_lo_status;
5327
5328 function get_lme_onl_evt_count(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
5329 return varchar2 is
5330 CURSOR csr_lme_info IS
5331 SELECT lme.lp_member_enrollment_id,
5332 lmb.ACTIVITY_VERSION_ID,
5333 lpe.lp_enrollment_id,
5334 lme.object_version_number,
5335 lmb.learning_path_member_id,
5336 lme.member_status_code,
5337 lpe.person_id,
5338 lpe.contact_id
5339 FROM ota_learning_path_members lmb,
5340 ota_lp_member_enrollments lme,
5341 ota_lp_enrollments lpe
5342 WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
5343 AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
5344 AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
5345
5346
5347 CURSOR get_enrl_status(csr_activity_version_id ota_events.activity_version_id%type,
5348 csr_person_id in number,
5349 csr_contact_id in number) IS
5350 SELECT bst.type status,
5351 tdb.DATE_STATUS_CHANGED,
5352 evt.event_id,
5353 evt.event_type,
5354 ocu.synchronous_flag,
5355 ocu.online_flag,
5356 bst.type
5357 FROM ota_events evt,
5358 ota_delegate_bookings tdb,
5359 ota_booking_status_types bst,
5360 ota_offerings ofr,
5361 ota_category_usages ocu
5362 WHERE evt.event_id = tdb.event_id
5363 AND bst.booking_status_type_id = tdb.booking_status_type_id
5364 AND evt.activity_version_id = csr_activity_version_id
5365 --AND tdb.delegate_person_id = p_person_id
5366 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
5367 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
5368 )
5369 AND evt.parent_offering_id = ofr.offering_id
5370 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
5371 AND tdb.booking_status_type_id = bst.booking_status_type_id
5372 AND bst.type <> 'C';
5373
5374 l_proc VARCHAR2(72) := g_package||'get_lme_onl_evt_count';
5375 rec_lme_info csr_lme_info%rowtype;
5376
5377 l_person_id number;
5378 l_contact_id number;
5379 l_cert_period_start_date date;
5380 l_cert_period_end_date date;
5381
5382 l_online_event_id ota_events.event_id%type;
5383 l_online_event_count number := 0;
5384 l_online_evt_count number;
5385
5386 BEGIN
5387 hr_utility.set_location('Entering :'||l_proc,5);
5388
5389 open csr_lme_info;
5390 fetch csr_lme_info into rec_lme_info;
5391 close csr_lme_info;
5392
5393 hr_utility.set_location(' Step:'||l_proc,10);
5394
5395 FOR rec IN get_enrl_status(rec_lme_info.activity_version_id,
5396 rec_lme_info.person_id,
5397 rec_lme_info.contact_id)
5398 LOOP
5399
5400 if rec.online_flag = 'Y' then
5401 l_online_event_id := rec.event_id;
5402 l_online_event_count := l_online_event_count + 1;
5403 --exit;
5404 end if;
5405
5406 END LOOP;
5407
5408 hr_utility.set_location('Leaving :'||l_proc,10);
5409
5410 return '' || l_online_event_count;
5411
5412 EXCEPTION
5413 WHEN others THEN
5414 hr_utility.set_location('Leaving :'||l_proc,15);
5415
5416 RETURN null;
5417
5418 End get_lme_onl_evt_count;
5419
5420 procedure get_active_cert_prds(
5421 p_event_id ota_events.event_id%type,
5422 p_person_id ota_cert_enrollments.contact_id%type,
5423 p_contact_id ota_cert_enrollments.contact_id%type,
5424 p_cert_prd_enrollment_ids OUT NOCOPY varchar2) is
5425
5426 l_proc VARCHAR2(72) := g_package|| 'get_active_cert_prds';
5427
5428 l_activity_version_id ota_events.activity_version_id%type;
5429 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
5430 l_cert_prd_enrollment_ids varchar2(4000);
5431
5432
5433 begin
5434
5435 hr_utility.set_location(' Step:'||l_proc,10);
5436
5437 if(p_person_id is not null) then
5438 for rec_active_cert_prd in csr_active_cert_prd_person(p_event_id, p_person_id)
5439 loop
5440 --populate OUT cert_prd_enrollment_ids params
5441 IF rec_active_cert_prd.cert_prd_enrollment_id IS NOT NULL THEN
5442 if l_cert_prd_enrollment_ids is null then
5443 l_cert_prd_enrollment_ids := rec_active_cert_prd.cert_prd_enrollment_id;
5444 else
5445 l_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids || '^' || rec_active_cert_prd.cert_prd_enrollment_id;
5446 end if;
5447 END IF;
5448 end loop;
5449 else
5450 for rec_active_cert_prd in csr_active_cert_prd_contact(p_event_id, p_contact_id)
5451 loop
5452 --populate OUT cert_prd_enrollment_ids params
5453 IF rec_active_cert_prd.cert_prd_enrollment_id IS NOT NULL THEN
5454 if l_cert_prd_enrollment_ids is null then
5455 l_cert_prd_enrollment_ids := rec_active_cert_prd.cert_prd_enrollment_id;
5456 else
5457 l_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids || '^' || rec_active_cert_prd.cert_prd_enrollment_id;
5458 end if;
5459 END IF;
5460 end loop;
5461 end if;
5462
5463 p_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids;
5464 hr_utility.set_location(' Step:'||l_proc,30);
5465
5466 EXCEPTION
5467 WHEN others THEN
5468 hr_utility.set_location('Leaving :'||l_proc,40);
5469 p_cert_prd_enrollment_ids := null;
5470 --MULTI MESSAGE SUPPORT
5471
5472 end get_active_cert_prds;
5473
5474 -- ----------------------------------------------------------------------------
5475 -- |--------------------< LO_has_cld_and_no_strt_url>-------------------------|
5476 -- ----------------------------------------------------------------------------
5477 --
5478 FUNCTION Lo_has_cld_and_no_strt_url
5479 (p_learning_object_id in number default hr_api.g_number)
5480 RETURN varchar2 is
5481 --
5482 -- decalare cursor
5483 Cursor csr_par_with_cld_no_url is
5484 Select null
5485 From ota_learning_objects cld, ota_learning_objects par
5486 Where par.learning_object_id = cld.parent_learning_object_id
5487 And par.starting_url is null
5488 And par.learning_object_id = p_learning_object_id
5489 And rownum = 1;
5490 --
5491 -- decalre variables
5492 l_learning_object_flag varchar2(10);
5493 l_return varchar2(2) := 'N';
5494 l_proc varchar2(72) := g_package||'LO_has_child_and_having_url';
5495 --
5496 BEGIN
5497 --
5498 hr_utility.set_location('Entering:'|| l_proc, 10);
5499 --
5500 OPEN csr_par_with_cld_no_url;
5501 FETCH csr_par_with_cld_no_url INTO l_learning_object_flag;
5502 IF csr_par_with_cld_no_url%found THEN
5503 --
5504 l_return := 'Y';
5505 hr_utility.set_location('returning Y:'|| l_proc, 10);
5506 END IF;
5507 --
5508 CLOSE csr_par_with_cld_no_url;
5509 --
5510 hr_utility.set_location('Exiting:'|| l_proc, 20);
5511 --
5512 RETURN l_return;
5513 --
5514 End Lo_has_cld_and_no_strt_url;
5515
5516 FUNCTION get_member_in(p_event_id ota_events.event_id%type,
5517 p_person_id ota_cert_enrollments.person_id%type,
5518 p_contact_id ota_cert_enrollments.contact_id%type) RETURN VARCHAR2 IS
5519
5520 CURSOR csr_is_lp_mbr_person IS
5521 SELECT lpe.lp_enrollment_id
5522 FROM ota_lp_enrollments lpe,
5523 ota_events oev,
5524 ota_learning_path_members lpm
5525 WHERE lpe.person_id = p_person_id
5526 AND oev.event_id = p_event_id
5527 AND NVL(lpe.is_history_flag, 'N') = 'N'
5528 AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5529 AND lpe.business_group_id = ota_general.get_business_group_id
5530 AND oev.activity_version_id = lpm.activity_version_id
5531 AND lpe.learning_path_id = lpm.learning_path_id;
5532
5533 CURSOR csr_is_lp_mbr_contact IS
5534 SELECT lpe.lp_enrollment_id
5535 FROM ota_lp_enrollments lpe,
5536 ota_events oev,
5537 ota_learning_path_members lpm
5538 WHERE lpe.contact_id = p_contact_id
5539 AND oev.event_id = p_event_id
5540 AND NVL(lpe.is_history_flag, 'N') = 'N'
5541 AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5542 AND lpe.business_group_id = ota_general.get_business_group_id
5543 AND oev.activity_version_id = lpm.activity_version_id
5544 AND lpe.learning_path_id = lpm.learning_path_id;
5545
5546 CURSOR csr_is_cert_mbr_person IS
5547 SELECT cre.cert_enrollment_id
5548 FROM ota_cert_enrollments cre,
5549 ota_certification_members cmb,
5550 ota_events oev
5551 WHERE cre.person_id = p_person_id
5552 AND oev.event_id = p_event_id
5553 AND NVL(cre.is_history_flag, 'N') = 'N'
5554 AND cre.certification_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5555 AND cre.business_group_id = ota_general.get_business_group_id
5556 AND cmb.object_id = oev.activity_version_id
5557 AND cmb.object_type = 'H'
5558 AND cre.certification_id = cmb.certification_id;
5559
5560 CURSOR csr_is_cert_mbr_contact IS
5561 SELECT cre.cert_enrollment_id
5562 FROM ota_cert_enrollments cre,
5563 ota_certification_members cmb,
5564 ota_events oev
5565 WHERE cre.contact_id = p_contact_id
5566 AND oev.event_id = p_event_id
5567 AND NVL(cre.is_history_flag, 'N') = 'N'
5568 AND cre.certification_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5569 AND cre.business_group_id = ota_general.get_business_group_id
5570 AND cmb.object_id = oev.activity_version_id
5571 AND cmb.object_type = 'H'
5572 AND cre.certification_id = cmb.certification_id;
5573
5574 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%type;
5575 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type;
5576 l_return_value VARCHAR2(30);
5577 BEGIN
5578 if(p_person_id is not null) then
5579 open csr_is_lp_mbr_person;
5580 fetch csr_is_lp_mbr_person into l_lp_enrollment_id;
5581 close csr_is_lp_mbr_person;
5582
5583 open csr_is_cert_mbr_person;
5584 fetch csr_is_cert_mbr_person into l_cert_enrollment_id;
5585 close csr_is_cert_mbr_person;
5586 else
5587 open csr_is_lp_mbr_contact;
5588 fetch csr_is_lp_mbr_contact into l_lp_enrollment_id;
5589 close csr_is_lp_mbr_contact;
5590
5591 open csr_is_cert_mbr_contact;
5592 fetch csr_is_cert_mbr_contact into l_cert_enrollment_id;
5593 close csr_is_cert_mbr_contact;
5594 end if;
5595
5596 if(l_lp_enrollment_id is not null) then
5597 l_return_value := 'LP';
5598 end if;
5599 if(l_cert_enrollment_id is not null) then
5600 l_return_value := l_return_value || 'CERT';
5601 end if;
5602
5603 return l_return_value;
5604 END get_member_in;
5605 -- ----------------------------------------------------------------------------
5606 -- |-------------------------< get_num_of_online_records >--------------------------|
5607 -- ----------------------------------------------------------------------------
5608 -- {Start Of Comments}
5609 --
5610 -- Description:
5611 -- This function will fetch number of online performance records
5612 --
5613 -- Pre Conditions:
5614 -- None.
5615 --
5616 -- IN Arguments:
5617 -- p_user_id
5618 -- p_user_type
5619 -- p_event_id
5620 -- p_mode
5621 -- p_chk_active_cert_flag
5622 --
5623 -- Post Success:
5624 -- Processing continues.
5625 --
5626 --
5627 -- Post Failure:
5628 -- None.
5629 --
5630 -- Access Status:
5631 -- Public.
5632 --
5633 -- {End Of Comments}
5634 ----------------------------------------------------------------------------
5635 --
5636 --modified for 14000818
5637 FUNCTION get_num_of_online_records(p_user_id IN NUMBER,
5638 p_user_type IN ota_attempts.user_type%type,
5639 p_event_id IN ota_events.event_id%TYPE,
5640 p_mode IN number default null,
5641 p_chk_active_cert_flag varchar2 default 'N')
5642 RETURN INTEGER IS
5643
5644
5645 --Added for Performance Multiple record Learner page -aswani
5646 cursor c_learning_object_tracktype is
5647 select lo.Tracking_type , lo.learning_object_id
5648 from ota_learning_objects lo, ota_offerings ofr, ota_events oev
5649 where ofr.Learning_object_id = lo.learning_object_id
5650 and oev.parent_offering_id = ofr.offering_id
5651 and oev.event_id = p_event_id ;
5652
5653 cursor csr_noof_perf_recs (l_lo_id in ota_offerings.learning_object_id%type) is
5654 select count(p.PERFORMANCE_ID)
5655 from
5656 ota_performances p, ota_learning_objects lo
5657 where lo.learning_object_id = p.learning_object_id
5658 and p.user_id = p_user_id
5659 and p.user_type = p_user_type
5660 and lo.learning_object_id = l_lo_id
5661 and p.cert_prd_enrollment_id is null;
5662
5663 cursor csr_noof_perf_recs_cert(l_lo_id in ota_offerings.learning_object_id%type, l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type) is
5664 select count(p.PERFORMANCE_ID)
5665 from
5666 ota_performances p, ota_learning_objects lo
5667 where lo.learning_object_id = p.learning_object_id
5668 and p.user_id = p_user_id
5669 and p.user_type = p_user_type
5670 and lo.learning_object_id = l_lo_id
5671 and p.cert_prd_enrollment_id = l_cert_prd_enr_id;
5672
5673
5674 l_proc VARCHAR2(72) := g_package|| 'get_num_of_online_records';
5675
5676 l_online_flag ota_category_usages.online_flag%TYPE;
5677 l_learning_object_id ota_offerings.learning_object_id%TYPE;
5678 l_course_end_date ota_events.course_end_date%TYPE;
5679 l_sysdate ota_events.course_end_date%TYPE;
5680
5681 l_trackingtype ota_learning_objects.tracking_type %TYPE;
5682 l_perf_rec_count INTEGER := 0;
5683 l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type;
5684 l_person_id ota_cert_enrollments.person_id%type := null;
5685 l_contact_id ota_cert_enrollments.contact_id%type := null;
5686 BEGIN
5687
5688 hr_utility.set_location(' Step:'|| l_proc, 10);
5689 if p_chk_active_cert_flag is not null and p_chk_active_cert_flag = 'Y' then
5690 if p_user_type = 'E' then
5691 l_person_id := p_user_id;
5692 elsif p_user_type = 'C' then
5693 l_contact_id := get_contact_id_for_party(p_user_id);
5694 end if;
5695 l_cert_prd_enr_id := ota_cme_util.get_cert_prd_enroll_id(p_event_id, l_person_id, l_contact_id);
5696 end if;
5697
5698 -- Check if Tracking type of learning object is S or not
5699 open c_learning_object_tracktype;
5700 fetch c_learning_object_tracktype into l_trackingtype , l_learning_object_id;
5701 close c_learning_object_tracktype;
5702
5703 if(l_trackingtype is not null and l_trackingtype = 'S') then
5704 if l_cert_prd_enr_id is not null then
5705 open csr_noof_perf_recs_cert(l_learning_object_id, l_cert_prd_enr_id);
5706 fetch csr_noof_perf_recs_cert into l_perf_rec_count;
5707 close csr_noof_perf_recs_cert;
5708 --return l_perf_rec_count;
5709 else
5710 open csr_noof_perf_recs(l_learning_object_id);
5711 fetch csr_noof_perf_recs into l_perf_rec_count;
5712 close csr_noof_perf_recs;
5713 --return l_perf_rec_count;
5714 end if;
5715 End if;
5716 return l_perf_rec_count;
5717 end get_num_of_online_records;
5718
5719
5720 end ota_lo_utility;