[Home] [Help]
PACKAGE BODY: APPS.OTA_CPR_UTILITY
Source
1 package body ota_cpr_utility as
2 /* $Header: otcprutl.pkb 120.11 2011/12/02 12:03:52 jaysridh ship $ */
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 -- Bug 13035257
277 Cursor c_get_mandatory_courses( p_enforcement_mode in ota_course_prerequisites.enforcement_mode%TYPE) is
278 select ocp.prerequisite_course_id
279 from ota_activity_versions oav,
280 ota_course_prerequisites ocp
281 where ocp.activity_version_id = p_act_ver_id
282 and ocp.enforcement_mode in ('B', p_enforcement_mode)
283 and ocp.prerequisite_type = 'M'
284 and ocp.prerequisite_course_id = oav.activity_version_id
285 and (oav.end_date is null or trunc(oav.end_date) >= trunc(sysdate));
286
287 Cursor c_get_mandatory_competencies is
288 SELECT cpe.competence_id,
289 cpe.proficiency_level_id
290 FROM per_competence_elements cpe
291 WHERE cpe.object_id = p_act_ver_id
292 and cpe.mandatory = 'Y'
293 and cpe.type = 'OTA_COMP_PREREQ';
294
295 l_completed_status varchar2(1);
296 l_enforcement_mode ota_course_prerequisites.enforcement_mode%TYPE;
297 Begin
298 l_completed_status := 'Y';
299 If ( p_user_type = 'C') Then --Customer
300 l_enforcement_mode := 'E'; --External
301 Else -- Employee
302 l_enforcement_mode := 'I'; --Internal
303 End If;
304
305 --Check the completion of mandatory courses
306 For a_prereq_crs_rec In c_get_mandatory_courses(l_enforcement_mode) Loop
307 l_completed_status := is_course_completed(p_person_id, p_delegate_contact_id,
308 p_user_id, p_user_type, a_prereq_crs_rec.prerequisite_course_id);
309 If ( l_completed_status = 'N' ) Then
310 l_completed_status := 'N';
311 RETURN l_completed_status;
312 End If;
313 End Loop;
314
315 If ( p_user_type = 'E') Then --Employee
316 --Check the completion of mandatory competencies
317 For a_prereq_comp_rec In c_get_mandatory_competencies Loop
318 l_completed_status := is_competency_acheived(p_person_id, a_prereq_comp_rec.competence_id,
319 a_prereq_comp_rec.proficiency_level_id);
320 If ( l_completed_status = 'N' ) Then
321 l_completed_status := 'N';
322 RETURN l_completed_status;
323 End If;
324 End Loop;
325 End If;
326 RETURN l_completed_status;
327 End is_mandatory_prereqs_completed;
328
329 FUNCTION is_advisory_prereqs_completed
330 (p_person_id in per_all_people_f.person_id%type,
331 p_delegate_contact_id in NUMBER,
332 p_user_id in NUMBER,
333 p_user_type in ota_attempts.user_type%type,
334 p_act_ver_id in ota_activity_versions.activity_version_id%type
335 ) RETURN varchar2 is
336
337 Cursor c_get_advisory_courses( p_enforcement_mode in ota_course_prerequisites.enforcement_mode%TYPE) is
338 select ocp.prerequisite_course_id
339 from ota_activity_versions oav,
340 ota_course_prerequisites ocp
341 where ocp.activity_version_id = p_act_ver_id
342 and ocp.enforcement_mode in ('B', p_enforcement_mode)
343 and ocp.prerequisite_type = 'A'
344 and ocp.prerequisite_course_id = oav.activity_version_id
345 and (oav.end_date is null or trunc(oav.end_date) >= trunc(sysdate));
346
347 Cursor c_get_advisory_competencies is
348 SELECT cpe.competence_id,
349 cpe.proficiency_level_id
350 FROM per_competence_elements cpe
351 WHERE cpe.object_id = p_act_ver_id
352 and ( cpe.mandatory = 'N' or cpe.mandatory is null )
353 and cpe.type = 'OTA_COMP_PREREQ';
354
355 l_completed_status varchar2(1);
359 If ( p_user_type = 'C') Then --Customer
356 l_enforcement_mode ota_course_prerequisites.enforcement_mode%TYPE;
357 Begin
358 l_completed_status := 'Y';
360 l_enforcement_mode := 'E'; --External
361 Else -- Employee
362 l_enforcement_mode := 'I'; --Internal
363 End If;
364
365 --Check the completion of advisory courses
366 For a_prereq_crs_rec In c_get_advisory_courses(l_enforcement_mode) Loop
367 l_completed_status := is_course_completed(p_person_id, p_delegate_contact_id,
368 p_user_id, p_user_type, a_prereq_crs_rec.prerequisite_course_id);
369 If ( l_completed_status = 'N' ) Then
370 l_completed_status := 'N';
371 RETURN l_completed_status;
372 End If;
373 End Loop;
374
375 If ( p_user_type = 'E') Then --Employee
376 --Check the completion of advisory competencies
377 For a_prereq_comp_rec In c_get_advisory_competencies Loop
378 l_completed_status := is_competency_acheived(p_person_id, a_prereq_comp_rec.competence_id,
379 a_prereq_comp_rec.proficiency_level_id);
380 If ( l_completed_status = 'N' ) Then
381 l_completed_status := 'N';
382 RETURN l_completed_status;
383 End If;
384 End Loop;
385 End If;
386 RETURN l_completed_status;
387 End is_advisory_prereqs_completed;
388
389 FUNCTION get_enroll_image
390 (p_person_id in per_all_people_f.person_id%type,
391 p_delegate_contact_id in NUMBER,
392 p_user_id in NUMBER,
393 p_user_type in ota_attempts.user_type%type,
394 p_event_id in ota_events.event_id%type
395 ) RETURN varchar2 is
396
397 Cursor c_get_act_ver_id is
398 SELECT activity_version_id
399 FROM ota_events
400 WHERE event_id = p_event_id;
401
402 l_enroll_image varchar2(20);
403 l_enrollment_status varchar2(1);
404 l_prereq_completed_status varchar2(1);
405 l_act_ver_id ota_activity_versions.activity_version_id%type;
406 Begin
407 l_enrollment_status := ota_utility.get_enrollment_status(p_person_id, p_delegate_contact_id, p_event_id, 1);
408 If ( l_enrollment_status = 'P' or l_enrollment_status = 'W' or l_enrollment_status = 'R' or l_enrollment_status = 'A') Then --Bug 4518920
409 l_enroll_image := 'AE'; -- Already enrolled
410 RETURN l_enroll_image;
411 End If;
412
413 Open c_get_act_ver_id;
414 Fetch c_get_act_ver_id into l_act_ver_id;
415 Close c_get_act_ver_id;
416
417 l_prereq_completed_status := is_mandatory_prereqs_completed(p_person_id, p_delegate_contact_id,
418 p_user_id, p_user_type, l_act_ver_id);
419
420 If ( l_prereq_completed_status = 'Y' ) Then
421 l_enroll_image := 'EA'; --Enrollment allowed
422 Else
423 l_enroll_image := 'MPNC'; --Mandatory prerequisites not completed
424 End If;
425 RETURN l_enroll_image;
426 End get_enroll_image;
427
428
429 Procedure chk_mandatory_prereqs
430 (p_person_id ota_delegate_bookings.delegate_person_id%TYPE,
431 p_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE,
432 p_event_id ota_events.event_id%TYPE
433 ) IS
434
435 Cursor c_get_act_ver_id is
436 SELECT ofr.activity_version_id
437 FROM ota_events evt, ota_offerings ofr
438 WHERE evt.event_id = p_event_id
439 and evt.parent_offering_id = ofr.offering_id;
440
441 Cursor get_ext_lrn_party_id is
442 select party.party_id
443 from HZ_CUST_ACCOUNT_ROLES acct_role,
444 HZ_PARTIES party,
445 HZ_RELATIONSHIPS rel,
446 HZ_ORG_CONTACTS org_cont,
447 HZ_PARTIES rel_party,
448 HZ_CUST_ACCOUNTS role_acct
449 where acct_role.party_id = rel.party_id
450 and acct_role.role_type = 'CONTACT'
451 and org_cont.party_relationship_id = rel.relationship_id
452 and rel.subject_id = party.party_id
453 and rel.party_id = rel_party.party_id
454 and rel.subject_table_name = 'HZ_PARTIES'
455 and rel.object_table_name = 'HZ_PARTIES'
456 and acct_role.cust_account_id = role_acct.cust_account_id
457 and role_acct.party_id = rel.object_id
458 and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
459
460 l_ext_lrn_party_id HZ_PARTIES.Party_Id%TYPE;
461 l_user_id number;
462 l_user_type ota_attempts.user_type%type;
463 l_act_ver_id ota_activity_versions.activity_version_id%type;
464 l_prereq_completed_status varchar2(1);
465
466 Begin
467 If ( p_delegate_contact_id is not null ) Then
468 Open get_ext_lrn_party_id;
469 Fetch get_ext_lrn_party_id into l_ext_lrn_party_id;
470 Close get_ext_lrn_party_id;
471
472 l_user_id := l_ext_lrn_party_id;
473 l_user_type := 'C'; --Customer
474 Else
475 l_user_id := p_person_id;
476 l_user_type := 'E'; --Employee
477 End If;
478
479 Open c_get_act_ver_id;
480 Fetch c_get_act_ver_id into l_act_ver_id;
481 Close c_get_act_ver_id;
482
483 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);
484 If ( l_prereq_completed_status = 'N' ) Then
485 fnd_message.set_name ('OTA', 'OTA_443729_PREREQ_NOT_COMPLETE');
486 fnd_message.raise_error;
487 End If;
488 End chk_mandatory_prereqs;
489
490 FUNCTION is_mand_crs_prereqs_completed
491 (p_person_id in per_all_people_f.person_id%type,
492 p_delegate_contact_id in NUMBER,
493 p_user_id in NUMBER,
494 p_user_type in ota_attempts.user_type%type,
495 p_act_ver_id in ota_activity_versions.activity_version_id%type
496 ) RETURN varchar2
497 IS
498
499 Cursor c_get_mandatory_courses( p_enforcement_mode in ota_course_prerequisites.enforcement_mode%TYPE) is
500 SELECT cpr.prerequisite_course_id
501 FROM ota_course_prerequisites cpr
502 WHERE cpr.activity_version_id = p_act_ver_id
503 and cpr.prerequisite_type = 'M'
507 l_enforcement_mode ota_course_prerequisites.enforcement_mode%TYPE;
504 and cpr.enforcement_mode in ('B', p_enforcement_mode);
505
506 l_completed_status varchar2(1):= 'Y';
508 BEGIN
509 If ( p_user_type = 'C') Then --Customer
510 l_enforcement_mode := 'E'; --External
511 Else -- Employee
512 l_enforcement_mode := 'I'; --Internal
513 End If;
514
515 --Check the completion of mandatory courses
516 For a_prereq_crs_rec In c_get_mandatory_courses(l_enforcement_mode) Loop
517 l_completed_status := is_course_completed(p_person_id, p_delegate_contact_id,
518 p_user_id, p_user_type, a_prereq_crs_rec.prerequisite_course_id);
519 If ( l_completed_status = 'N' ) Then
520 l_completed_status := 'N';
521 RETURN l_completed_status;
522 End If;
523 End Loop;
524 RETURN l_completed_status;
525 END is_mand_crs_prereqs_completed;
526
527 FUNCTION is_mand_comp_prereqs_completed
528 (p_person_id in per_all_people_f.person_id%type,
529 p_act_ver_id in ota_activity_versions.activity_version_id%type
530 ) RETURN varchar2
531 IS
532 Cursor c_get_mandatory_competencies is
533 SELECT cpe.competence_id,
534 cpe.proficiency_level_id
535 FROM per_competence_elements cpe
536 WHERE cpe.object_id = p_act_ver_id
537 and cpe.mandatory = 'Y'
538 and cpe.type = 'OTA_COMP_PREREQ';
539
540 l_completed_status varchar2(1):= 'Y';
541 BEGIN
542 --Check the completion of mandatory competencies
543 For a_prereq_comp_rec In c_get_mandatory_competencies Loop
544 l_completed_status := is_competency_acheived(p_person_id, a_prereq_comp_rec.competence_id,
545 a_prereq_comp_rec.proficiency_level_id);
546 If ( l_completed_status = 'N' ) Then
547 l_completed_status := 'N';
548 RETURN l_completed_status;
549 End If;
550 End Loop;
551 RETURN l_completed_status;
552 END is_mand_comp_prereqs_completed;
553
554 Function Get_course_prereq_count
555 (p_prereq_met varchar2
556 ,p_event_id in ota_events.event_id%type
557 ,p_prerequisite_course_id ota_activity_versions.activity_version_id%type
558 ) Return varchar2 is
559 --
560 --
561 Cursor c_course_prereq_status is
562 select
563 ota_cpr_utility.is_course_completed
564 (odb.delegate_person_id
565 ,odb.delegate_contact_id
566 ,nvl(odb.delegate_person_id,odb.delegate_contact_id)
567 ,decode(odb.delegate_person_id,null,'C', 'E')
568 ,p_prerequisite_course_id
569 ) prereq_met
570 from
571 ota_delegate_bookings odb,
572 ota_booking_status_types bst,
573 ota_events evb
574 where
575 odb.booking_status_type_id = bst.booking_status_type_id
576 and evb.event_id = odb.event_id
577 and evb.event_id = p_event_id;
578 --
579 l_prereq_count Number := 0;
580 --
581 Begin
582 --
583 For prereq_status in c_course_prereq_status Loop
584 --
585 If prereq_status.prereq_met = p_prereq_met Then
586 --
587 l_prereq_count := l_prereq_count + 1;
588 --
589 End If;
590 End Loop;
591 --
592 return to_char(l_prereq_count);
593 --
594 End Get_course_prereq_count;
595
596 Function Get_comp_prereq_count
597 (p_prereq_met varchar2
598 ,p_event_id in ota_events.event_id%type
599 ,p_comp_id in per_competence_elements.competence_id%type
600 ,p_level_id in per_competence_elements.proficiency_level_id%type
601 ) Return varchar2 is
602 --
603 --
604 Cursor c_comp_prereq_status is
605 select
606 ota_cpr_utility.is_competency_acheived
607 (odb.delegate_person_id
608 ,p_comp_id
609 ,p_level_id
610 ) prereq_met
611 from
612 ota_delegate_bookings odb,
613 ota_events evb
614 where
615 evb.event_id = odb.event_id
616 and evb.event_id = p_event_id;
617 --
618 l_prereq_count Number := 0;
619 --
620 Begin
621 --
622 For prereq_status in c_comp_prereq_status Loop
623 --
624 If prereq_status.prereq_met = p_prereq_met Then
625 --
626 l_prereq_count := l_prereq_count + 1;
627 --
628 End If;
629 End Loop;
630 --
631 return to_char(l_prereq_count);
632 --
633 End Get_comp_prereq_count;
634
635 FUNCTION get_prereq_met_count
636 (p_event_id in ota_events.event_id%type,
637 p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
638 p_comp_id in per_competence_elements.competence_id%type,
639 p_level_id in per_competence_elements.proficiency_level_id%type
640 ) RETURN varchar2 is
641 --
642 l_met_count Varchar2(15) := '0';
643 --
644 Begin
645 --
646 If p_prerequisite_course_id is not null then
647 --
648 l_met_count := Get_course_prereq_count
649 (p_prereq_met => 'Y'
650 ,p_event_id => p_event_id
651 ,p_prerequisite_course_id => p_prerequisite_course_id
652 );
653 --
654 Else
655 --
656 l_met_count := Get_comp_prereq_count
657 (p_prereq_met => 'Y'
658 ,p_event_id => p_event_id
659 ,p_comp_id => p_comp_id
660 ,p_level_id => p_level_id
661 );
662
663 --
664 End If;
665 --
666 Return l_met_count;
667 --
668 End get_prereq_met_count;
669
670
671 FUNCTION get_prereq_not_met_count
672 (p_event_id in ota_events.event_id%type,
673 p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
677
674 p_comp_id in per_competence_elements.competence_id%type,
675 p_level_id in per_competence_elements.proficiency_level_id%type
676 ) RETURN varchar2 is
678 l_not_met_cnt Number := 0;
679 --
680 Begin
681 --
682 If p_prerequisite_course_id is not null then
683 --
684 l_not_met_cnt := Get_course_prereq_count
685 (p_prereq_met => 'N'
686 ,p_event_id => p_event_id
687 ,p_prerequisite_course_id => p_prerequisite_course_id
688 );
689 --
690 Else
691 --
692 l_not_met_cnt := Get_comp_prereq_count
693 (p_prereq_met => 'N'
694 ,p_event_id => p_event_id
695 ,p_comp_id => p_comp_id
696 ,p_level_id => p_level_id
697 );
698 --
699 End If;
700 --
701 Return l_not_met_cnt;
702 --
703 end get_prereq_not_met_count;
704
705
706 -- Added for Bug#4485646
707 FUNCTION is_mand_comp_prereqs_comp_evt
708 (p_person_id in per_all_people_f.person_id%type,
709 p_event_id in ota_events.event_id%type
710 ) RETURN varchar2
711 IS
712 CURSOR csr_get_course_id IS
713 SELECT activity_version_id
714 FROM ota_events
715 WHERE event_id = p_event_id;
716
717 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
718
719 BEGIN
720 OPEN csr_get_course_id;
721 FETCH csr_get_course_id INTO l_activity_version_id;
722 CLOSE csr_get_course_id;
723 RETURN is_mand_comp_prereqs_completed(
724 p_person_id => p_person_id
725 ,p_act_ver_id => l_activity_version_id);
726 END is_mand_comp_prereqs_comp_evt;
727
728 FUNCTION is_mand_crs_prereqs_comp_evt
729 (p_person_id in per_all_people_f.person_id%type
730 ,p_delegate_contact_id in ota_delegate_bookings.delegate_contact_id%TYPE
731 ,p_user_id in number
732 ,p_user_type in varchar2
733 ,p_event_id in ota_events.event_id%type
734 ) RETURN varchar2
735 IS
736 CURSOR csr_get_course_id IS
737 SELECT activity_version_id
738 FROM ota_events
739 WHERE event_id = p_event_id;
740
741 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
742
743 BEGIN
744 OPEN csr_get_course_id;
745 FETCH csr_get_course_id INTO l_activity_version_id;
746 CLOSE csr_get_course_id;
747 RETURN is_mand_crs_prereqs_completed(
748 p_person_id => p_person_id
749 ,p_delegate_contact_id => p_delegate_contact_id
750 ,p_user_id => p_user_id
751 ,p_user_type => p_user_type
752 ,p_act_ver_id => l_activity_version_id);
753 END is_mand_crs_prereqs_comp_evt;
754
755 FUNCTION is_mandatory_prereqs_comp_evt
756 (p_person_id in per_all_people_f.person_id%type
757 ,p_delegate_contact_id in ota_delegate_bookings.delegate_contact_id%TYPE default NULL
758 ,p_user_id in number default NULL
759 ,p_user_type in varchar2 default 'E'
760 , p_event_id in ota_events.event_id%type
761 ) RETURN varchar2
762 IS
763 l_user_type varchar2(10):= p_user_type;
764 l_user_id number(15):=p_user_id;
765 BEGIN
766
767 If p_delegate_contact_id is not null then
768 l_user_type := 'C';
769 l_user_id := ota_utility.get_ext_lrnr_party_id(p_delegate_contact_id);
770
771 end if;
772 IF is_mand_crs_prereqs_comp_evt(p_person_id => p_person_id
773 ,p_delegate_contact_id => p_delegate_contact_id
774 ,p_user_id => l_user_id
775 ,p_user_type => l_user_type
776 ,p_event_id => p_event_id) = 'N' THEN
777 RETURN 'N';
778 ELSIF p_person_id IS NOT NULL THEN
779 RETURN is_mand_comp_prereqs_comp_evt(p_person_id => p_person_id
780 ,p_event_id => p_event_id);
781 END IF;
782
783 RETURN 'Y';
784 END is_mandatory_prereqs_comp_evt;
785
786 -- ----------------------------------------------------------------------------
787 -- |-------------------------< is_valid_classes_available >--------------------|
788 -- ----------------------------------------------------------------------------
789 -- PUBLIC
790 -- Description:
791 -- Validates whether prerequisite course contains valid classes or not.
792 -- Course should have associated offering and valid classes. Valid classes
793 -- include classes whose class type is SCHEDULED or SELFPACED and whose
794 -- class status is not Cancelled and which are not expired
795 --
796 FUNCTION is_valid_classes_available
797 (p_prerequisite_course_id in number
798 ) RETURN varchar2 is
799 --
800 cursor get_valid_classes is
801 select 'Y'
802 from OTA_EVENTS oev
803 where oev.ACTIVITY_VERSION_ID = p_prerequisite_course_id
804 and (oev.EVENT_TYPE = 'SCHEDULED' or oev.EVENT_TYPE = 'SELFPACED')
805 and oev.EVENT_STATUS <> 'A'
806 and nvl(trunc(oev.course_end_date), trunc(sysdate)) >= trunc(sysdate);
807
808 l_proc varchar2(72) := g_package||'is_valid_classes_available';
809 l_flag varchar2(1);
810 --
811 Begin
812 --
813 hr_utility.set_location('Entering:'|| l_proc, 5);
814 --
815 Open get_valid_classes;
816 fetch get_valid_classes into l_flag;
817
818 If ( get_valid_classes%notfound ) Then
819 l_flag := 'N';
820 else
821 l_flag := 'Y';
822 End If;
823
824 close get_valid_classes;
825 return l_flag;
829 -- ----------------------------------------------------------------------------
826 hr_utility.set_location(' Leaving:'|| l_proc, 10);
827 End is_valid_classes_available;
828 --
830 -- |-------------------------< get_all_dependent_courses >--------------------|
831 -- ----------------------------------------------------------------------------
832 -- PUBLIC
833 -- Description:
834 -- Fetch the list of courses that are dependant on the specified activity id
835 -- whose end date are greater than the given pre-req course.
836 --
837 -- Added for the bug 13035257
838 FUNCTION get_all_dependent_courses
839 (p_act_ver_id in ota_activity_versions.activity_version_id%type,
840 p_pre_req_crs_end_date varchar2) RETURN varchar2 is
841 --
842 cursor get_dependent_courses(p_act_ver_id in ota_attempts.user_type%type,
843 p_pre_req_crs_end_date date) is
844 select oavt.version_name Dependant_Course_Name,
845 ocp.prerequisite_type Prerequisite_Type,
846 ocp.enforcement_mode Enforcement_Mode,
847 ocp.activity_version_id Activity_Version_Id,
848 ocp.activity_version_id Dependant_Course_Id,
849 ocp.business_group_id Business_Group_Id
850 from ota_activity_versions oav,
851 ota_activity_versions_tl oavt,
852 ota_course_prerequisites ocp
853 where ocp.activity_version_id = oav.activity_version_id
854 and oav.activity_version_id = oavt.activity_version_id
855 and oavt.language = userenv('LANG')
856 and ocp.prerequisite_course_id = p_act_ver_id
857 and ( (oav.END_DATE is null ) or (trunc(oav.END_DATE) > trunc(to_date(p_pre_req_crs_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')))) )
858 Order By Dependant_Course_Name desc;
859 --
860 l_depend_course_names varchar2(4000);
861 Begin
862 l_depend_course_names := null;
863 --Getting the list of course names from the dependaent query
864 For a_depend_crs_rec In get_dependent_courses(p_act_ver_id,p_pre_req_crs_end_date) Loop
865 if l_depend_course_names is not null then
866 l_depend_course_names := l_depend_course_names ||', ' || a_depend_crs_rec.Dependant_Course_Name;
867 else
868 l_depend_course_names := a_depend_crs_rec.Dependant_Course_Name;
869 end if;
870 End Loop;
871 return l_depend_course_names;
872 End get_all_dependent_courses;
873 --
874 end ota_cpr_utility;