[Home] [Help]
PACKAGE BODY: APPS.OTA_CPR_UTILITY
Source
1 package body ota_cpr_utility as
2 /* $Header: otcprutl.pkb 120.8 2005/12/27 01:20 rdola noship $ */
3
4 g_package varchar2(33) := ' ota_cpr_utility.'; -- Global package name
5
6 FUNCTION is_competency_acheived
7 (p_person_id in per_all_people_f.person_id%type,
8 p_comp_id in per_competence_elements.competence_id%type,
9 p_level_id in per_competence_elements.proficiency_level_id%type
10 ) RETURN varchar2 is
11
12 Cursor CompRecordWithLevel is
13 Select nvl(pce.proficiency_level_id, -100)
14 from per_competence_elements pce, per_rating_levels prl
15 where pce.competence_id = p_comp_id
16 and pce.person_id = p_person_id
17 and prl.rating_level_id(+) = pce.proficiency_level_id
18 and prl.step_value >= (select step_value from per_rating_levels where rating_level_id = p_level_id)
19 and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));
20
21 Cursor CompRecordWithNullLevel is
22 Select nvl(pce.proficiency_level_id, -100)
23 from per_competence_elements pce
24 where pce.competence_id = p_comp_id
25 and pce.person_id = p_person_id
26 and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
27 and pce.proficiency_level_id is null;
28
29 Cursor CompRecordWithoutLevel is
30 Select nvl(pce.proficiency_level_id, -100)
31 from per_competence_elements pce
32 where pce.competence_id = p_comp_id
33 and pce.person_id = p_person_id
34 and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));
35
36 l_level_id per_competence_elements.proficiency_level_id%type;
37
38 Begin
39 --Note: l_level_id = -100 means Data found but level is null
40
41 If p_level_id is null Then -- Any record found will be considered as acheiving the competency
42 OPEN CompRecordWithoutLevel;
43 FETCH CompRecordWithoutLevel INTO l_level_id;
44
45 If CompRecordWithoutLevel%NOTFOUND then --implies competence not found
46 l_level_id := -1;
47 End If;
48 CLOSE CompRecordWithoutLevel;
49 Else
50 OPEN CompRecordWithNullLevel;
51 FETCH CompRecordWithNullLevel INTO l_level_id;
52
53 If CompRecordWithNullLevel%NOTFOUND then --implies competence with null level not found
54 l_level_id := -1;
55 End If;
56 CLOSE CompRecordWithNullLevel;
57
58 If (l_level_id = -1) Then
59 OPEN CompRecordWithLevel;
60 FETCH CompRecordWithLevel INTO l_level_id;
61
62 If CompRecordWithLevel%NOTFOUND then --implies competence not found
63 l_level_id := -1;
64 End If;
65 CLOSE CompRecordWithLevel;
66 End If;
67 End If;
68
69 If (l_level_id = -1) Then
70 RETURN 'N';
71 Else
72 RETURN 'Y';
73 End If;
74 End is_competency_acheived;
75
76
77 FUNCTION check_learner_comp_step_values
78 (p_act_ver_id in ota_activity_versions.activity_version_id%type,
79 p_comp_id in per_competence_elements.competence_id%type,
80 p_level_id in per_competence_elements.proficiency_level_id%type
81 ) RETURN varchar2 is
82
83 Cursor GetLrnCompMaxStepValue is
84 SELECT
85 Decode(PRL.STEP_VALUE, null, -1, PRL.STEP_VALUE),
86 PCE.COMPETENCE_ELEMENT_ID
87 FROM
88 PER_RATING_LEVELS PRL,
89 PER_COMPETENCE_ELEMENTS PCE
90 WHERE
91 PCE.PROFICIENCY_LEVEL_ID = PRL.RATING_LEVEL_ID (+)
92 and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
93 AND PCE.type = 'DELIVERY'
94 AND PCE.ACTIVITY_VERSION_ID = p_act_ver_id
95 AND PCE.COMPETENCE_ID = p_comp_id
96 ORDER BY PRL.STEP_VALUE DESC;
97
98 Cursor GetPrereqCompStepValue is
99 SELECT
100 PRL.STEP_VALUE
101 FROM
102 PER_RATING_LEVELS PRL
103 WHERE
104 PRL.RATING_LEVEL_ID = p_level_id;
105
106 Cursor GetCompHighesteStepValue IS
107 /* Modified for bug#4905777
108 SELECT MAX(STEP_VALUE)
109 FROM per_competence_levels_v
110 WHERE COMPETENCE_ID = p_comp_id;
111 */
112 select MAX(step_value)
113 from
114 per_rating_levels prl
115 ,per_competences pce
116 where (prl.rating_scale_id = pce.rating_scale_id
117 OR pce.competence_id = prl.competence_id)
118 AND pce.competence_id = p_comp_id;
119
120 Cursor GetCompLowestStepValue IS
121 /* Modified for bug#4905777
122 SELECT MIN(STEP_VALUE)
123 FROM per_competence_levels_v
124 WHERE COMPETENCE_ID = p_comp_id;
125 */
126 select MIN(step_value)
127 from
128 per_rating_levels prl
129 ,per_competences pce
130 where (prl.rating_scale_id = pce.rating_scale_id
131 OR pce.competence_id = prl.competence_id)
132 AND pce.competence_id = p_comp_id;
133
134
135 l_lrn_comp_el_id per_competence_elements.competence_element_id%type;
136 l_lrn_step_value per_rating_levels.step_value%type;
137
138 l_prereq_step_value per_rating_levels.step_value%type;
139 l_comp_max_step_value per_rating_levels.step_value%type;
140 l_comp_min_step_value per_rating_levels.step_value%type;
141 l_return_status varchar2(1);
142
143 Begin
144 l_return_status := 'S'; -- Prereq comp(PC) can be specified
145
146 OPEN GetLrnCompMaxStepValue;
147 FETCH GetLrnCompMaxStepValue INTO l_lrn_step_value, l_lrn_comp_el_id;
148
149 If ( p_level_id is not null ) Then
150 OPEN GetPrereqCompStepValue;
151 FETCH GetPrereqCompStepValue INTO l_prereq_step_value;
152 CLOSE GetPrereqCompStepValue;
153 End If;
154
155 If GetLrnCompMaxStepValue%NOTFOUND then --implies no learner competency(LC) at any level found
156 CLOSE GetLrnCompMaxStepValue;
157 l_return_status := 'S'; -- Success -> Prereq comp can be specified
158 RETURN l_return_status;
159 Else
160 If ( ( l_lrn_step_value = -1 ) and ( p_level_id is null ) ) Then
161 l_return_status := 'E'; -- Error -> Prereq comp cannot be specified since PC >= LC
162 RETURN l_return_status;
163 ElsIf ( ( l_lrn_step_value = -1 ) and ( p_level_id is not null ) ) Then
164 OPEN GetCompHighesteStepValue;
165 FETCH GetCompHighesteStepValue INTO l_comp_max_step_value;
166 CLOSE GetCompHighesteStepValue;
167
168 If ( l_prereq_step_value = l_comp_max_step_value ) Then
169 l_return_status := 'E'; -- Error -> Prereq comp cannot be specified since PC >= LC
170 -- Here null LC is assumed as HIGHEST level
171 Else
172 l_return_status := 'S'; -- Success -> Prereq comp can be specified
173 End If;
174
175 RETURN l_return_status;
176 ElsIf ( ( l_lrn_step_value <> -1 ) and ( p_level_id is null ) ) Then
177 OPEN GetCompLowestStepValue;
178 FETCH GetCompLowestStepValue INTO l_comp_min_step_value;
179 CLOSE GetCompLowestStepValue;
180
181 If ( l_lrn_step_value = l_comp_min_step_value ) Then
182 l_return_status := 'E'; -- Error -> Prereq comp cannot be specified since PC >= LC
183 -- Here null prereq comp level is assumed as LOWEST level
184 Else
185 l_return_status := 'S'; -- Success -> Prereq comp can be specified
186 End If;
187
188 RETURN l_return_status;
189 ElsIf ( ( l_lrn_step_value <> -1 ) and ( p_level_id is not null ) ) Then
190 -- Prerequisite competency level cannot be same or greater than learner competency level
191 If ( l_prereq_step_value >= l_lrn_step_value ) Then
192 l_return_status := 'E'; -- Error -> Prereq comp cannot be specified since PC >= LC
193 Else
194 l_return_status := 'S'; -- Success -> Prereq comp can be specified
195 End If;
196 End If;
197 End If;
198
199 CLOSE GetLrnCompMaxStepValue;
200 RETURN l_return_status;
201 End check_learner_comp_step_values;
202
203
204 FUNCTION is_course_completed
205 (p_person_id in per_all_people_f.person_id%type,
206 p_delegate_contact_id in NUMBER,
207 p_user_id in NUMBER,
208 p_user_type in ota_attempts.user_type%type,
209 p_act_ver_id in ota_activity_versions.activity_version_id%type
210 ) RETURN varchar2 is
211
212 Cursor c_get_classes is
213 SELECT
214 oev.event_id,
215 OCU.online_flag,
216 OFR.learning_object_id
217 FROM
218 ota_activity_versions OAV,
219 ota_offerings OFR,
220 ota_events OEV,
221 ota_category_usages OCU
222 WHERE
223 OFR.activity_version_id = OAV.activity_version_id
224 And OEV.parent_offering_id = OFR.offering_id
225 And OFR.delivery_mode_id = OCU.category_usage_id
226 And OCU.type ='DM'
227 And (OEV.event_type = 'SCHEDULED' or OEV.event_type = 'SELFPACED')
228 And OEV.event_status <> 'A'
229 And OAV.activity_version_id = p_act_ver_id;
230
231 CURSOR c_learning_object_status(p_lo_id in ota_offerings.learning_object_id%TYPE) is
232 SELECT lesson_status
233 FROM ota_performances
234 WHERE
235 user_id = p_user_id
236 AND user_type = p_user_type
237 AND learning_object_id = p_lo_id;
238
239 l_enrollment_status varchar2(1);
240 l_completed_status varchar2(1);
241 l_lo_status ota_performances.lesson_status%type;
242
243 Begin
244 l_completed_status := 'N';
245 For a_event_rec In c_get_classes() Loop
246 If a_event_rec.online_flag = 'Y' Then -- Online class
247 Open c_learning_object_status(a_event_rec.learning_object_id);
248 Fetch c_learning_object_status into l_lo_status;
249
250 If c_learning_object_status%FOUND Then
251 If ( l_lo_status = 'C' or l_lo_status = 'P' ) Then
252 l_completed_status := 'Y';
253 Close c_learning_object_status;
254 RETURN l_completed_status;
255 End If;
256 End If;
257 Close c_learning_object_status;
258 Else -- Offline class
259 l_enrollment_status := ota_utility.get_enrollment_status(p_person_id, p_delegate_contact_id, a_event_rec.event_id, 1);
260 If ( l_enrollment_status = 'A' ) Then
261 l_completed_status := 'Y';
262 RETURN l_completed_status;
263 End If;
264 End If;
265 End Loop;
266 RETURN l_completed_status;
267 End is_course_completed;
268
269 FUNCTION is_mandatory_prereqs_completed
270 (p_person_id in per_all_people_f.person_id%type,
271 p_delegate_contact_id in NUMBER,
272 p_user_id in NUMBER,
273 p_user_type in ota_attempts.user_type%type,
274 p_act_ver_id in ota_activity_versions.activity_version_id%type
275 ) RETURN varchar2 is
276
277 Cursor c_get_mandatory_courses( p_enforcement_mode in ota_course_prerequisites.enforcement_mode%TYPE) is
278 SELECT cpr.prerequisite_course_id
279 FROM ota_course_prerequisites cpr
280 WHERE cpr.activity_version_id = p_act_ver_id
281 and cpr.prerequisite_type = 'M'
282 and cpr.enforcement_mode in ('B', p_enforcement_mode);
283
284 Cursor c_get_mandatory_competencies is
285 SELECT cpe.competence_id,
286 cpe.proficiency_level_id
287 FROM per_competence_elements cpe
288 WHERE cpe.object_id = p_act_ver_id
289 and cpe.mandatory = 'Y'
290 and cpe.type = 'OTA_COMP_PREREQ';
291
292 l_completed_status varchar2(1);
293 l_enforcement_mode ota_course_prerequisites.enforcement_mode%TYPE;
294 Begin
295 l_completed_status := 'Y';
296 If ( p_user_type = 'C') Then --Customer
297 l_enforcement_mode := 'E'; --External
298 Else -- Employee
299 l_enforcement_mode := 'I'; --Internal
300 End If;
301
302 --Check the completion of mandatory courses
303 For a_prereq_crs_rec In c_get_mandatory_courses(l_enforcement_mode) Loop
304 l_completed_status := is_course_completed(p_person_id, p_delegate_contact_id,
305 p_user_id, p_user_type, a_prereq_crs_rec.prerequisite_course_id);
306 If ( l_completed_status = 'N' ) Then
307 l_completed_status := 'N';
308 RETURN l_completed_status;
309 End If;
310 End Loop;
311
312 If ( p_user_type = 'E') Then --Employee
313 --Check the completion of mandatory competencies
314 For a_prereq_comp_rec In c_get_mandatory_competencies Loop
315 l_completed_status := is_competency_acheived(p_person_id, a_prereq_comp_rec.competence_id,
316 a_prereq_comp_rec.proficiency_level_id);
317 If ( l_completed_status = 'N' ) Then
318 l_completed_status := 'N';
319 RETURN l_completed_status;
320 End If;
321 End Loop;
322 End If;
323 RETURN l_completed_status;
324 End is_mandatory_prereqs_completed;
325
326 FUNCTION is_advisory_prereqs_completed
327 (p_person_id in per_all_people_f.person_id%type,
328 p_delegate_contact_id in NUMBER,
329 p_user_id in NUMBER,
330 p_user_type in ota_attempts.user_type%type,
331 p_act_ver_id in ota_activity_versions.activity_version_id%type
332 ) RETURN varchar2 is
333
334 Cursor c_get_advisory_courses( p_enforcement_mode in ota_course_prerequisites.enforcement_mode%TYPE) is
335 SELECT cpr.prerequisite_course_id
336 FROM ota_course_prerequisites cpr
337 WHERE cpr.activity_version_id = p_act_ver_id
338 and cpr.prerequisite_type = 'A'
339 and cpr.enforcement_mode in ('B', p_enforcement_mode);
340
341 Cursor c_get_advisory_competencies is
342 SELECT cpe.competence_id,
343 cpe.proficiency_level_id
344 FROM per_competence_elements cpe
345 WHERE cpe.object_id = p_act_ver_id
346 and ( cpe.mandatory = 'N' or cpe.mandatory is null )
347 and cpe.type = 'OTA_COMP_PREREQ';
348
349 l_completed_status varchar2(1);
350 l_enforcement_mode ota_course_prerequisites.enforcement_mode%TYPE;
351 Begin
352 l_completed_status := 'Y';
353 If ( p_user_type = 'C') Then --Customer
354 l_enforcement_mode := 'E'; --External
355 Else -- Employee
356 l_enforcement_mode := 'I'; --Internal
357 End If;
358
359 --Check the completion of advisory courses
360 For a_prereq_crs_rec In c_get_advisory_courses(l_enforcement_mode) Loop
361 l_completed_status := is_course_completed(p_person_id, p_delegate_contact_id,
362 p_user_id, p_user_type, a_prereq_crs_rec.prerequisite_course_id);
363 If ( l_completed_status = 'N' ) Then
364 l_completed_status := 'N';
365 RETURN l_completed_status;
366 End If;
367 End Loop;
368
369 If ( p_user_type = 'E') Then --Employee
370 --Check the completion of advisory competencies
371 For a_prereq_comp_rec In c_get_advisory_competencies Loop
372 l_completed_status := is_competency_acheived(p_person_id, a_prereq_comp_rec.competence_id,
373 a_prereq_comp_rec.proficiency_level_id);
374 If ( l_completed_status = 'N' ) Then
375 l_completed_status := 'N';
376 RETURN l_completed_status;
377 End If;
378 End Loop;
379 End If;
380 RETURN l_completed_status;
381 End is_advisory_prereqs_completed;
382
383 FUNCTION get_enroll_image
384 (p_person_id in per_all_people_f.person_id%type,
385 p_delegate_contact_id in NUMBER,
386 p_user_id in NUMBER,
387 p_user_type in ota_attempts.user_type%type,
388 p_event_id in ota_events.event_id%type
389 ) RETURN varchar2 is
390
391 Cursor c_get_act_ver_id is
392 SELECT activity_version_id
393 FROM ota_events
394 WHERE event_id = p_event_id;
395
396 l_enroll_image varchar2(20);
397 l_enrollment_status varchar2(1);
398 l_prereq_completed_status varchar2(1);
399 l_act_ver_id ota_activity_versions.activity_version_id%type;
400 Begin
401 l_enrollment_status := ota_utility.get_enrollment_status(p_person_id, p_delegate_contact_id, p_event_id, 1);
402 If ( l_enrollment_status = 'P' or l_enrollment_status = 'W' or l_enrollment_status = 'R' or l_enrollment_status = 'A') Then --Bug 4518920
403 l_enroll_image := 'AE'; -- Already enrolled
404 RETURN l_enroll_image;
405 End If;
406
407 Open c_get_act_ver_id;
408 Fetch c_get_act_ver_id into l_act_ver_id;
409 Close c_get_act_ver_id;
410
411 l_prereq_completed_status := is_mandatory_prereqs_completed(p_person_id, p_delegate_contact_id,
412 p_user_id, p_user_type, l_act_ver_id);
413
414 If ( l_prereq_completed_status = 'Y' ) Then
415 l_enroll_image := 'EA'; --Enrollment allowed
416 Else
417 l_enroll_image := 'MPNC'; --Mandatory prerequisites not completed
418 End If;
419 RETURN l_enroll_image;
420 End get_enroll_image;
421
422
423 Procedure chk_mandatory_prereqs
424 (p_person_id ota_delegate_bookings.delegate_person_id%TYPE,
425 p_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE,
426 p_event_id ota_events.event_id%TYPE
427 ) IS
428
429 Cursor c_get_act_ver_id is
430 SELECT ofr.activity_version_id
431 FROM ota_events evt, ota_offerings ofr
432 WHERE evt.event_id = p_event_id
433 and evt.parent_offering_id = ofr.offering_id;
434
435 Cursor get_ext_lrn_party_id is
436 select party.party_id
437 from HZ_CUST_ACCOUNT_ROLES acct_role,
438 HZ_PARTIES party,
439 HZ_RELATIONSHIPS rel,
440 HZ_ORG_CONTACTS org_cont,
441 HZ_PARTIES rel_party,
442 HZ_CUST_ACCOUNTS role_acct
443 where acct_role.party_id = rel.party_id
447 and rel.party_id = rel_party.party_id
444 and acct_role.role_type = 'CONTACT'
445 and org_cont.party_relationship_id = rel.relationship_id
446 and rel.subject_id = party.party_id
448 and rel.subject_table_name = 'HZ_PARTIES'
449 and rel.object_table_name = 'HZ_PARTIES'
450 and acct_role.cust_account_id = role_acct.cust_account_id
451 and role_acct.party_id = rel.object_id
452 and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
453
454 l_ext_lrn_party_id HZ_PARTIES.Party_Id%TYPE;
455 l_user_id number;
456 l_user_type ota_attempts.user_type%type;
457 l_act_ver_id ota_activity_versions.activity_version_id%type;
458 l_prereq_completed_status varchar2(1);
459
460 Begin
461 If ( p_delegate_contact_id is not null ) Then
462 Open get_ext_lrn_party_id;
463 Fetch get_ext_lrn_party_id into l_ext_lrn_party_id;
464 Close get_ext_lrn_party_id;
465
466 l_user_id := l_ext_lrn_party_id;
467 l_user_type := 'C'; --Customer
468 Else
469 l_user_id := p_person_id;
470 l_user_type := 'E'; --Employee
471 End If;
472
473 Open c_get_act_ver_id;
474 Fetch c_get_act_ver_id into l_act_ver_id;
475 Close c_get_act_ver_id;
476
477 l_prereq_completed_status := is_mandatory_prereqs_completed(p_person_id, p_delegate_contact_id, l_user_id, l_user_type, l_act_ver_id);
478 If ( l_prereq_completed_status = 'N' ) Then
479 fnd_message.set_name ('OTA', 'OTA_443729_PREREQ_NOT_COMPLETE');
480 fnd_message.raise_error;
481 End If;
482 End chk_mandatory_prereqs;
483
484 FUNCTION is_mand_crs_prereqs_completed
485 (p_person_id in per_all_people_f.person_id%type,
486 p_delegate_contact_id in NUMBER,
487 p_user_id in NUMBER,
488 p_user_type in ota_attempts.user_type%type,
489 p_act_ver_id in ota_activity_versions.activity_version_id%type
490 ) RETURN varchar2
491 IS
492
493 Cursor c_get_mandatory_courses( p_enforcement_mode in ota_course_prerequisites.enforcement_mode%TYPE) is
494 SELECT cpr.prerequisite_course_id
495 FROM ota_course_prerequisites cpr
496 WHERE cpr.activity_version_id = p_act_ver_id
497 and cpr.prerequisite_type = 'M'
498 and cpr.enforcement_mode in ('B', p_enforcement_mode);
499
500 l_completed_status varchar2(1):= 'Y';
501 l_enforcement_mode ota_course_prerequisites.enforcement_mode%TYPE;
502 BEGIN
503 If ( p_user_type = 'C') Then --Customer
504 l_enforcement_mode := 'E'; --External
505 Else -- Employee
506 l_enforcement_mode := 'I'; --Internal
507 End If;
508
509 --Check the completion of mandatory courses
510 For a_prereq_crs_rec In c_get_mandatory_courses(l_enforcement_mode) Loop
511 l_completed_status := is_course_completed(p_person_id, p_delegate_contact_id,
512 p_user_id, p_user_type, a_prereq_crs_rec.prerequisite_course_id);
513 If ( l_completed_status = 'N' ) Then
514 l_completed_status := 'N';
515 RETURN l_completed_status;
516 End If;
517 End Loop;
518 RETURN l_completed_status;
519 END is_mand_crs_prereqs_completed;
520
521 FUNCTION is_mand_comp_prereqs_completed
522 (p_person_id in per_all_people_f.person_id%type,
523 p_act_ver_id in ota_activity_versions.activity_version_id%type
524 ) RETURN varchar2
525 IS
526 Cursor c_get_mandatory_competencies is
527 SELECT cpe.competence_id,
528 cpe.proficiency_level_id
529 FROM per_competence_elements cpe
530 WHERE cpe.object_id = p_act_ver_id
531 and cpe.mandatory = 'Y'
532 and cpe.type = 'OTA_COMP_PREREQ';
533
534 l_completed_status varchar2(1):= 'Y';
535 BEGIN
536 --Check the completion of mandatory competencies
537 For a_prereq_comp_rec In c_get_mandatory_competencies Loop
538 l_completed_status := is_competency_acheived(p_person_id, a_prereq_comp_rec.competence_id,
539 a_prereq_comp_rec.proficiency_level_id);
540 If ( l_completed_status = 'N' ) Then
541 l_completed_status := 'N';
542 RETURN l_completed_status;
543 End If;
544 End Loop;
545 RETURN l_completed_status;
546 END is_mand_comp_prereqs_completed;
547
548 Function Get_course_prereq_count
549 (p_prereq_met varchar2
550 ,p_event_id in ota_events.event_id%type
551 ,p_prerequisite_course_id ota_activity_versions.activity_version_id%type
552 ) Return varchar2 is
553 --
554 --
555 Cursor c_course_prereq_status is
556 select
557 ota_cpr_utility.is_course_completed
558 (odb.delegate_person_id
559 ,odb.delegate_contact_id
560 ,nvl(odb.delegate_person_id,odb.delegate_contact_id)
561 ,decode(odb.delegate_person_id,null,'C', 'E')
562 ,p_prerequisite_course_id
563 ) prereq_met
564 from
565 ota_delegate_bookings odb,
566 ota_booking_status_types bst,
567 ota_events evb
568 where
569 odb.booking_status_type_id = bst.booking_status_type_id
570 and evb.event_id = odb.event_id
571 and evb.event_id = p_event_id;
572 --
573 l_prereq_count Number := 0;
574 --
575 Begin
576 --
577 For prereq_status in c_course_prereq_status Loop
578 --
579 If prereq_status.prereq_met = p_prereq_met Then
580 --
581 l_prereq_count := l_prereq_count + 1;
582 --
583 End If;
584 End Loop;
585 --
586 return to_char(l_prereq_count);
587 --
588 End Get_course_prereq_count;
589
590 Function Get_comp_prereq_count
591 (p_prereq_met varchar2
592 ,p_event_id in ota_events.event_id%type
593 ,p_comp_id in per_competence_elements.competence_id%type
594 ,p_level_id in per_competence_elements.proficiency_level_id%type
595 ) Return varchar2 is
596 --
597 --
598 Cursor c_comp_prereq_status is
599 select
600 ota_cpr_utility.is_competency_acheived
604 ) prereq_met
601 (odb.delegate_person_id
602 ,p_comp_id
603 ,p_level_id
605 from
606 ota_delegate_bookings odb,
607 ota_events evb
608 where
609 evb.event_id = odb.event_id
610 and evb.event_id = p_event_id;
611 --
612 l_prereq_count Number := 0;
613 --
614 Begin
615 --
616 For prereq_status in c_comp_prereq_status Loop
617 --
618 If prereq_status.prereq_met = p_prereq_met Then
619 --
620 l_prereq_count := l_prereq_count + 1;
621 --
622 End If;
623 End Loop;
624 --
625 return to_char(l_prereq_count);
626 --
627 End Get_comp_prereq_count;
628
629 FUNCTION get_prereq_met_count
630 (p_event_id in ota_events.event_id%type,
631 p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
632 p_comp_id in per_competence_elements.competence_id%type,
633 p_level_id in per_competence_elements.proficiency_level_id%type
634 ) RETURN varchar2 is
635 --
636 l_met_count Varchar2(15) := '0';
637 --
638 Begin
639 --
640 If p_prerequisite_course_id is not null then
641 --
642 l_met_count := Get_course_prereq_count
643 (p_prereq_met => 'Y'
644 ,p_event_id => p_event_id
645 ,p_prerequisite_course_id => p_prerequisite_course_id
646 );
647 --
648 Else
649 --
650 l_met_count := Get_comp_prereq_count
651 (p_prereq_met => 'Y'
652 ,p_event_id => p_event_id
653 ,p_comp_id => p_comp_id
654 ,p_level_id => p_level_id
655 );
656
657 --
658 End If;
659 --
660 Return l_met_count;
661 --
662 End get_prereq_met_count;
663
664
665 FUNCTION get_prereq_not_met_count
666 (p_event_id in ota_events.event_id%type,
667 p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
668 p_comp_id in per_competence_elements.competence_id%type,
669 p_level_id in per_competence_elements.proficiency_level_id%type
670 ) RETURN varchar2 is
671
672 l_not_met_cnt Number := 0;
673 --
674 Begin
675 --
676 If p_prerequisite_course_id is not null then
677 --
678 l_not_met_cnt := Get_course_prereq_count
679 (p_prereq_met => 'N'
680 ,p_event_id => p_event_id
681 ,p_prerequisite_course_id => p_prerequisite_course_id
682 );
683 --
684 Else
685 --
686 l_not_met_cnt := Get_comp_prereq_count
687 (p_prereq_met => 'N'
688 ,p_event_id => p_event_id
689 ,p_comp_id => p_comp_id
690 ,p_level_id => p_level_id
691 );
692 --
693 End If;
694 --
695 Return l_not_met_cnt;
696 --
697 end get_prereq_not_met_count;
698
699
700 -- Added for Bug#4485646
701 FUNCTION is_mand_comp_prereqs_comp_evt
702 (p_person_id in per_all_people_f.person_id%type,
703 p_event_id in ota_events.event_id%type
704 ) RETURN varchar2
705 IS
706 CURSOR csr_get_course_id IS
707 SELECT activity_version_id
708 FROM ota_events
709 WHERE event_id = p_event_id;
710
711 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
712
713 BEGIN
714 OPEN csr_get_course_id;
715 FETCH csr_get_course_id INTO l_activity_version_id;
716 CLOSE csr_get_course_id;
717 RETURN is_mand_comp_prereqs_completed(
718 p_person_id => p_person_id
719 ,p_act_ver_id => l_activity_version_id);
720 END is_mand_comp_prereqs_comp_evt;
721
722 FUNCTION is_mand_crs_prereqs_comp_evt
723 (p_person_id in per_all_people_f.person_id%type
724 ,p_delegate_contact_id in ota_delegate_bookings.delegate_contact_id%TYPE
725 ,p_user_id in number
726 ,p_user_type in varchar2
727 ,p_event_id in ota_events.event_id%type
728 ) RETURN varchar2
729 IS
730 CURSOR csr_get_course_id IS
731 SELECT activity_version_id
732 FROM ota_events
733 WHERE event_id = p_event_id;
734
735 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
736
737 BEGIN
738 OPEN csr_get_course_id;
739 FETCH csr_get_course_id INTO l_activity_version_id;
740 CLOSE csr_get_course_id;
741 RETURN is_mand_crs_prereqs_completed(
742 p_person_id => p_person_id
743 ,p_delegate_contact_id => p_delegate_contact_id
744 ,p_user_id => p_user_id
745 ,p_user_type => p_user_type
746 ,p_act_ver_id => l_activity_version_id);
747 END is_mand_crs_prereqs_comp_evt;
748
749 FUNCTION is_mandatory_prereqs_comp_evt
750 (p_person_id in per_all_people_f.person_id%type
751 ,p_delegate_contact_id in ota_delegate_bookings.delegate_contact_id%TYPE default NULL
752 ,p_user_id in number default NULL
753 ,p_user_type in varchar2 default 'E'
754 , p_event_id in ota_events.event_id%type
755 ) RETURN varchar2
756 IS
757 l_user_type varchar2(10):= p_user_type;
758 l_user_id number(15):=p_user_id;
759 BEGIN
760
761 If p_delegate_contact_id is not null then
762 l_user_type := 'C';
763 l_user_id := ota_utility.get_ext_lrnr_party_id(p_delegate_contact_id);
764
765 end if;
766 IF is_mand_crs_prereqs_comp_evt(p_person_id => p_person_id
767 ,p_delegate_contact_id => p_delegate_contact_id
768 ,p_user_id => l_user_id
769 ,p_user_type => l_user_type
770 ,p_event_id => p_event_id) = 'N' THEN
771 RETURN 'N';
772 ELSIF p_person_id IS NOT NULL THEN
773 RETURN is_mand_comp_prereqs_comp_evt(p_person_id => p_person_id
774 ,p_event_id => p_event_id);
775 END IF;
776
777 RETURN 'Y';
778 END is_mandatory_prereqs_comp_evt;
779
780 -- ----------------------------------------------------------------------------
781 -- |-------------------------< is_valid_classes_available >--------------------|
782 -- ----------------------------------------------------------------------------
783 -- PUBLIC
784 -- Description:
785 -- Validates whether prerequisite course contains valid classes or not.
786 -- Course should have associated offering and valid classes. Valid classes
787 -- include classes whose class type is SCHEDULED or SELFPACED and whose
788 -- class status is not Cancelled and which are not expired
789 --
790 FUNCTION is_valid_classes_available
791 (p_prerequisite_course_id in number
792 ) RETURN varchar2 is
793 --
794 cursor get_valid_classes is
795 select 'Y'
796 from OTA_EVENTS oev
797 where oev.ACTIVITY_VERSION_ID = p_prerequisite_course_id
798 and (oev.EVENT_TYPE = 'SCHEDULED' or oev.EVENT_TYPE = 'SELFPACED')
799 and oev.EVENT_STATUS <> 'A'
800 and nvl(trunc(oev.course_end_date), trunc(sysdate)) >= trunc(sysdate);
801
802 l_proc varchar2(72) := g_package||'is_valid_classes_available';
803 l_flag varchar2(1);
804 --
805 Begin
806 --
807 hr_utility.set_location('Entering:'|| l_proc, 5);
808 --
809 Open get_valid_classes;
810 fetch get_valid_classes into l_flag;
811
812 If ( get_valid_classes%notfound ) Then
813 l_flag := 'N';
814 else
815 l_flag := 'Y';
816 End If;
817
818 close get_valid_classes;
819 return l_flag;
820 hr_utility.set_location(' Leaving:'|| l_proc, 10);
821 End is_valid_classes_available;
822 --
823 end ota_cpr_utility;