[Home] [Help]
PACKAGE BODY: APPS.OTA_CPE_UTIL
Source
1 PACKAGE BODY OTA_CPE_UTIL as
2 /* $Header: otcpewrs.pkb 120.51.12020000.6 2013/01/18 09:30:53 jaysridh ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := 'OTA_CPE_UTIL.'; -- Global package name
9
10 -- ---------------------------------------------------------------------------
11 -- |----------------------< crt_comp_upd_succ_att >-----------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14
15 Procedure crt_comp_upd_succ_att(p_event_id in ota_events.event_id%type,
16 p_person_id in number
17 )
18 is
19
20 l_proc varchar2(72) := g_package||' crt_comp_upd_succ_att';
21
22 cursor get_crt_prd_enrollments is
23 select cpe.cert_prd_enrollment_id,
24 cpe.cert_period_start_date,
25 cpe.cert_period_end_date,
26 cre.certification_id
27 from
28 ota_activity_versions tav,
29 ota_cert_enrollments cre,
30 ota_cert_prd_enrollments cpe,
31 ota_cert_mbr_enrollments cme,
32 ota_certification_members cmb,
33 ota_events evt
34
35 where evt.event_id = p_event_id
36 and evt.activity_version_id = tav.activity_version_id
37 AND cmb.object_id = tav.activity_version_id
38 AND cmb.object_type = 'H'
39 AND cme.cert_member_id = cmb.certification_member_id
40 AND cme.member_status_code <> 'CANCELLED'
41 and cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
42 and cpe.period_status_code = 'COMPLETED'
43 and cpe.cert_enrollment_id = cre.cert_enrollment_id
44 and cre.person_id = p_person_id
45 AND ((evt.course_start_date >= cpe.cert_period_start_date
46 and nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= cpe.cert_period_end_date )
47 OR
48 (event_type ='SELFPACED' and cpe.cert_period_end_date >= course_start_date)
49 AND (evt.course_end_date is null or (evt.course_end_date IS NOT NULL AND
50 evt.course_end_date >= cpe.cert_period_start_date)) );
51
52 l_item_key wf_items.item_key%type;
53
54 begin
55
56 hr_utility.set_location('Entering:'|| l_proc, 10);
57
58 for rec in get_crt_prd_enrollments
59 Loop
60 if ('Y' = ota_cpe_util.is_cert_success_complete(p_cert_prd_enrollment_id => rec.cert_prd_enrollment_id,
61 p_cert_period_start_date => rec.cert_period_start_date
62 ,p_cert_period_end_date => rec.cert_period_end_date,
63 p_person_id => p_person_id)) then
64 ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
65 p_itemtype =>'HRSSA',
66 p_person_id => p_person_id,
67 p_eventid =>null,
68 p_learningpath_ids => null,
69 p_certification_id => rec.certification_id ,
70 p_itemkey =>l_item_key);
71
72 end if;
73
74 end loop;
75 hr_utility.set_location('Leaving:'|| l_proc, 10);
76 end crt_comp_upd_succ_att;
77
78 -- ---------------------------------------------------------------------------
79 -- |----------------------< is_cert_success_complete >-----------------------------|
80 -- ---------------------------------------------------------------------------
81 --
82 Function is_cert_success_complete(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
83 p_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
84 p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_start_date%type,
85 p_person_id in number)
86 return varchar2
87 IS
88
89
90 --get all the classes under one certification
91 Cursor Csr_Cert_mbr is
92 Select
93 tav.activity_version_id
94
95 FROM
96 ota_activity_versions tav,
97 -- ota_cert_enrollments cre,
98 ota_cert_prd_enrollments cpe,
99 ota_cert_mbr_enrollments cme,
100 ota_certification_members cmb
101
102 WHERE
103 cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
104 -- AND cre.cert_enrollment_id = cpe.cert_enrollment_id
105 AND cme.cert_member_id = cmb.certification_member_id
106 AND cme.member_status_code <> 'CANCELLED'
107 AND cmb.object_id = tav.activity_version_id
108 AND cmb.object_type = 'H'
109 -- AND tav.activity_version_id= evt.activity_version_id
110 AND cpe.cert_prd_enrollment_id =p_cert_prd_enrollment_id; -- 640, 350, 349
111
112
113 Cursor Csr_Crt_mbr_cls_status(p_act_version_id ota_activity_versions.activity_version_id%Type
114 ) is
115 Select tdb.successful_attendance_flag
116 From ota_events evt,ota_delegate_bookings tdb,ota_booking_status_types bst
117 Where
118 evt.activity_version_id = p_act_version_id
119 and tdb.event_id = evt.event_id
120 and tdb.booking_status_type_id = bst.booking_status_type_id
121 and bst.type = 'A'
122 AND tdb.delegate_person_id = p_person_id
123 --and tdb.event_id =p_event_id
124 AND ((evt.course_start_date >= p_cert_period_start_date
125 and nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= p_cert_period_end_date )
126 OR
127 (event_type ='SELFPACED' and p_cert_period_end_date >= course_start_date)
128 AND (evt.course_end_date is null or (evt.course_end_date IS NOT NULL AND
129 evt.course_end_date >= p_cert_period_start_date)) )
130 Order by Nvl(tdb.Successful_attendance_flag,'N') desc ;
131
132
133 l_act_version_id Number;
134 l_mbr_cls_succ_att_flag Varchar2(1) := 'N';
135 l_succ_att_flag varchar2(5);
136 l_cls_enr_exists varchar2(1) := 'N' ;
137 Begin
138 For I in Csr_Cert_mbr Loop
139 hr_utility.trace('Batra act_id' || I.Activity_version_id);
140
141 for rec in Csr_Crt_mbr_cls_status(I.Activity_version_id)
142 Loop
143 l_cls_enr_exists := 'Y' ;
144 l_succ_att_flag := rec.successful_attendance_flag;
145 hr_utility.trace('Batra l_succ_att_flag' || l_succ_att_flag);
146 /* If Csr_Crt_mbr_cls_status%Notfound then
147 hr_utility.trace('Batra in crs not found l_succ_att_flag' || I.Activity_version_id);
148 l_mbr_cls_succ_att_flag := 'N' ;
149 exit ;
150 Else*/
151 If Nvl(l_succ_att_flag,'N') = 'Y' then
152 hr_utility.trace('Batra l_succ_att_flag Y' || I.Activity_version_id);
153 l_mbr_cls_succ_att_flag := 'Y' ;
154 exit ;
155 Else
156 hr_utility.trace('Batra in else of l_succ_att_flag' || l_succ_att_flag);
157 l_mbr_cls_succ_att_flag := 'N' ;
158 exit ;
159 End If;
160 -- End If ;
161 End Loop ;
162 -- If either no class enrollment exists or found an class enrollment is not set to successful_attendannce
163 -- no further process required.
164 If l_cls_enr_exists = 'N' or l_mbr_cls_succ_att_flag = 'N' then
165 l_mbr_cls_succ_att_flag := 'N' ;
166 exit ;
167 End if ;
168 --close Csr_Crt_mbr_cls_status;
169
170 End Loop;
171 hr_utility.trace('Batra before return' || l_mbr_cls_succ_att_flag);
172 return l_mbr_cls_succ_att_flag;
173
174 end is_cert_success_complete;
175
176
177 -- ---------------------------------------------------------------------------
178 -- |----------------------< chk_cert_prd_compl >-----------------------------|
179 -- ---------------------------------------------------------------------------
180 --
181 Function chk_cert_prd_compl(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type)
182 return varchar2
183 IS
184
185 Cursor any_child is
186 Select cme.cert_mbr_enrollment_id
187 from ota_cert_mbr_enrollments cme
188 where member_status_code <> 'CANCELLED'
189 and member_status_code <> 'COMPLETED'
190 and cert_prd_enrollment_id = p_cert_prd_enrollment_id and rownum=1;
191
192 Cursor one_child_completed is
193 Select cme.cert_mbr_enrollment_id
194 from ota_cert_mbr_enrollments cme
195 where member_status_code = 'COMPLETED'
196 and cert_prd_enrollment_id = p_cert_prd_enrollment_id and rownum=1;
197
198 l_proc VARCHAR2(72) := g_package ||'chk_cert_prd_compl';
199 l_exists Number(9);
200 l_complete Number(9);
201 l_result varchar2(3) :='F';
202
203 Begin
204
205 hr_utility.set_location(' Entering:' || l_proc,10);
206
207 open any_child;
208 fetch any_child into l_exists;
209 if any_child%NOTFOUND then
210 open one_child_completed;
211 fetch one_child_completed into l_complete;
212 if one_child_completed%found then
213 l_result :='S';
214 end if;
215 close one_child_completed;
216 end if;
217 close any_child;
218
219 return l_result;
220
221 EXCEPTION
222 WHEN others THEN
223 hr_utility.set_location('Leaving :'||l_proc,15);
224 RETURN NULL;
225
226 end chk_cert_prd_compl;
227
228 procedure update_cpe_status(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type
229 ,p_certification_status_code OUT NOCOPY VARCHAR2
230 ,p_enroll_from in varchar2 default null
231 ,p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null
232 ,p_child_update_flag in varchar2 default 'Y'
233 ,p_completion_date in date default sysdate)
234 IS
235
236 Cursor csr_mbr_enrl is
237 Select cme.cert_mbr_enrollment_id
238 from ota_cert_mbr_enrollments cme
239 where member_status_code <> 'CANCELLED'
240 and cert_prd_enrollment_id = p_cert_prd_enrollment_id;
241
242 Cursor one_child_active IS
243 Select cme.cert_mbr_enrollment_id
244 from ota_cert_mbr_enrollments cme
245 where member_status_code in ('ACTIVE', 'PENDING', 'COMPLETED') --10164130
246 and cert_prd_enrollment_id = p_cert_prd_enrollment_id and rownum=1;
247
248 CURSOR csr_cert_enrl IS
249 select cre.certification_status_code, cre.object_version_number, cre.completion_date,
250 cre.certification_id,
251 cre.person_id,
252 cre.expiration_date, cre.unenrollment_date
253 FROM ota_cert_enrollments cre,
254 ota_cert_prd_enrollments cpe
255 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
256 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
257
258 CURSOR csr_prd_enrl IS
259 select period_status_code, object_version_number, completion_date, cert_enrollment_id, cert_period_end_date
260 FROM ota_cert_prd_enrollments
261 where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
262
263 Cursor get_mbr_completion_date is
264 Select min(cme.completion_date)
265 from ota_cert_mbr_enrollments cme
266 where member_status_code = 'COMPLETED'
267 and cert_prd_enrollment_id = p_cert_prd_enrollment_id;
268 l_proc varchar2(72) := g_package || ' update_cpe_status';
269
270
271 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
272 l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
273
274 l_complete_cert_prd_ok VARCHAR2(30);
275 l_period_status_code VARCHAR2(30);
276 n_period_status_code VARCHAR2(30) := 'ENROLLED';
277 l_certification_status_code ota_cert_enrollments.certification_status_code%type := 'ENROLLED';
278
279 l_object_version_number1 number;
280 l_object_version_number2 number;
281 l_object_version_number3 number;
282
283 l_completion_date DATE;
284 l_cert_prd_enrollment_id NUMBER;
285 l_cert_mbr_enrollment_id NUMBER;
286
287 l_period_start_date DATE;
288 l_period_end_date DATE;
289
290 --l_certification_status_code VARCHAR2(30);
291 l_expiration_date DATE;
292 l_unenrollment_date DATE;
293
294 Begin
295
296 hr_utility.set_location(' Entering:' || l_proc,10);
297
298 if p_child_update_flag = 'Y' then
299 for rec_mbr_enrl in csr_mbr_enrl
300 loop
301 ota_cme_util.update_cme_status(rec_mbr_enrl.cert_mbr_enrollment_id);
302 end loop;
303 end if;
304
305 --check for period completion
306 -- verify the period cert enrol eligibility for marking complete
307 l_complete_cert_prd_ok := ota_cpe_util.chk_cert_prd_compl(p_cert_prd_enrollment_id => p_cert_prd_enrollment_id);
308
309 IF l_complete_cert_prd_ok = 'S' THEN
310 -- The Plan can be completed
311 n_period_status_code := 'COMPLETED';
312 ELSIF l_complete_cert_prd_ok = 'F' THEN
313 --if atleast one child is in ACTIVE or COMPLETED, mark the parent cpe as ACTIVE
314 open one_child_active;
315 fetch one_child_active into l_cert_mbr_enrollment_id;
316 if one_child_active%found then
317 n_period_status_code := 'ACTIVE';
318 end if;
319 close one_child_active;
320 END IF;
321
322 OPEN csr_prd_enrl;
323 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
324 CLOSE csr_prd_enrl;
325
326
327 IF n_period_status_code <> l_prd_enrl_rec.period_status_code THEN
328
329 --update cpe and cre recs
330 if n_period_status_code = 'ACTIVE' THEN
331 l_certification_status_code := 'ENROLLED';
332 l_completion_date := null;
333 l_expiration_date := null;
334 elsif n_period_status_code = 'COMPLETED' THEN
335 --cert enrol overall status code
336 l_certification_status_code := 'CERTIFIED';
337 open get_mbr_completion_date;
338 fetch get_mbr_completion_date into l_completion_date;
339 close get_mbr_completion_date;
340
341 l_completion_date := trunc(nvl(l_completion_date, p_completion_date));
342 --l_completion_date := trunc(sysdate);
343 else
344 l_certification_status_code := 'ENROLLED';
345 l_completion_date := null;
346 l_expiration_date := null;
347 end if;
348
349
350
351 ota_cert_prd_enrollment_api.update_cert_prd_enrollment
352 (p_effective_date => trunc(sysdate)
353 ,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
354 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
355 ,p_object_version_number => l_prd_enrl_rec.object_version_number
356 ,p_period_status_code => n_period_status_code
357 ,p_completion_date => l_completion_date);
358
359
360 OPEN csr_cert_enrl;
361 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
362 CLOSE csr_cert_enrl;
363
364 if n_period_status_code = 'COMPLETED' then
365 --get the recent expiration_date updated within cpe.. update api
366 l_expiration_date := l_cert_enrl_rec.expiration_date;
367 end if;
368
369 if (l_certification_status_code = 'ENROLLED') then
370 l_unenrollment_date := null;
371 else
372 l_unenrollment_date := l_cert_enrl_rec.unenrollment_date;
373 end if;
374
375 ota_cert_enrollment_api.update_cert_enrollment
376 (p_effective_date => trunc(sysdate)
377 ,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
378 ,p_certification_id => l_cert_enrl_rec.certification_id
379 ,p_object_version_number => l_cert_enrl_rec.object_version_number
380 ,p_certification_status_code => l_certification_status_code
381 ,p_expiration_date => l_expiration_date
382 ,p_completion_date => l_completion_date
383 ,p_unenrollment_date => l_unenrollment_date);
384
385 end if; -- status code check
386
387 -- update prd end date if passed from admin i/f
388 OPEN csr_prd_enrl;
389 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
390 CLOSE csr_prd_enrl;
391
392 if (p_cert_period_end_date is not null and p_cert_period_end_date <> l_prd_enrl_rec.cert_period_end_date) then
393 ota_cert_prd_enrollment_api.update_cert_prd_enrollment
394 (p_effective_date => trunc(sysdate)
395 ,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
396 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
397 ,p_object_version_number => l_prd_enrl_rec.object_version_number
398 ,p_period_status_code => l_prd_enrl_rec.period_status_code
399 ,p_cert_period_end_date => p_cert_period_end_date);
400
401 end if;
402
403 --set out params
404 p_certification_status_code := l_certification_status_code;
405
406 if l_cert_enrl_rec.person_id is not null
407 and l_certification_status_code ='ENROLLED'
408 and p_enroll_from = 'ADMIN' then
409 OTA_LRNR_ENROLL_UNENROLL_WF.Cert_Enrollment(p_process => 'OTA_CERT_APPROVAL_JSP_PRC',
410 p_itemtype => 'HRSSA',
411 p_person_id => l_cert_enrl_rec.person_id,
412 p_certificationid => l_cert_enrl_rec.certification_id);
413 end if;
414
415 EXCEPTION
416 WHEN others THEN
417 --
418 -- Reset IN OUT and set OUT parameters
419 --
420 p_certification_status_code := null;
421 hr_utility.set_location(' Leaving:' || l_proc,50);
422 raise;
423 end update_cpe_status;
424
425
426 function is_period_renewable(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type)
427 return varchar2 is
428
429 CURSOR csr_crt IS
430 select
431 b.certification_id certification_id
432 , b.RENEWABLE_FLAG
433 , b.INITIAL_COMPLETION_DURATION
434 , cre.expiration_date
435 from ota_certifications_b b,
436 ota_cert_enrollments cre
437 where cre.certification_id = b.certification_id
438 and cre.cert_enrollment_id = p_cert_enrollment_id;
439
440 CURSOR csr_max_cpe_exp_dt IS
441 select
442 max(cpe.expiration_date)
443 from ota_cert_prd_enrollments cpe,
444 ota_cert_enrollments cre
445 where cpe.cert_enrollment_id = cre.cert_enrollment_id
446 and cre.cert_enrollment_id = p_cert_enrollment_id;
447
448 CURSOR csr_dupl_prd(csr_expiration_date ota_cert_enrollments.expiration_date%type) is
449 select 'Y' as dupl_prd_exists
450 from ota_cert_enrollments
451 where cert_enrollment_id = p_cert_enrollment_id
452 and expiration_date = csr_expiration_date;
453
454 l_proc VARCHAR2(72) := g_package||'is_period_renewable';
455
456 l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
457 l_expiration_date ota_cert_enrollments.expiration_date%type;
458 l_curr_dt date;
459 l_dupl_prd_exists varchar2(1) := 'N';
460
461 rec_crt csr_crt%rowtype;
462
463 l_return_val varchar2(1) := 'N';
464 l_max_expiration_date date;
465
466 Begin
467 hr_utility.set_location('Entering :'||l_proc,5);
468
469 --return N for non renewal certs
470 OPEN csr_crt;
471 FETCH csr_crt INTO rec_crt;
472 CLOSE csr_crt;
473
474 if (rec_crt.RENEWABLE_FLAG is null or rec_crt.RENEWABLE_FLAG = 'N') then
475 return 'N';
476 end if;
477
478 l_curr_dt := trunc(sysdate);
479
480 OPEN csr_max_cpe_exp_dt;
481 FETCH csr_max_cpe_exp_dt INTO l_max_expiration_date;
482 CLOSE csr_max_cpe_exp_dt;
483
484 /*
485 if l_curr_dt > l_max_expiration_date and rec_crt.INITIAL_COMPLETION_DURATION is not null then
486 --this is for expired init durn based certs, hence allow re-enroll beyond last day of reg earl enr day
487 l_return_val := 'Y';
488 return l_return_val;
489 end if;
490 */
491
492 calc_cre_dates(p_cert_enrollment_id, rec_crt.certification_id, 'V', l_earliest_enroll_date, l_expiration_date);
493
494 --check for dupl prd
495 /* open csr_dupl_prd(l_expiration_date);
496 fetch csr_dupl_prd into l_dupl_prd_exists;
497 if csr_dupl_prd%found then
498 return l_dupl_prd_exists;
499 end if;
500 close csr_dupl_prd; */
501
502 if l_earliest_enroll_date is not null and l_expiration_date is not null then
503 --based by dur can re-enroll after the ear_enr_dt since the restriction
504 --as applicable to based by date is not applicable ie., learners don't need
505 --to finish on same period due date.
506 if rec_crt.INITIAL_COMPLETION_DURATION is not null then
507 if l_curr_dt >= l_earliest_enroll_date then
508 l_return_val := 'Y';
509 else
510 l_return_val := 'N';
511 end if;
512 else
513 if l_curr_dt between l_earliest_enroll_date
514 and l_expiration_date then
515 l_return_val := 'Y';
516 else
517 l_return_val := 'N';
518 end if;
519 end if;
520 end if;
521
522 hr_utility.set_location('Leaving :'||l_proc,10);
523
524 return l_return_val;
525
526 EXCEPTION
527 WHEN others THEN
528 hr_utility.set_location('Leaving :'||l_proc,15);
529
530 RETURN null;
531
532 End is_period_renewable;
533
534 Function get_earl_enrl_dt(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
535 p_expiration_date in ota_cert_enrollments.expiration_date%type)
536 return date
537 IS
538
539 CURSOR csr_crt IS
540 select
541 b.certification_id certification_id
542 , b.INITIAL_COMPLETION_DATE
543 , b.INITIAL_COMPLETION_DURATION
544 , b.INITIAL_COMPL_DURATION_UNITS
545 , b.RENEWAL_DURATION
546 , b.RENEWAL_DURATION_UNITS
547 , b.NOTIFY_DAYS_BEFORE_EXPIRE
548 , b.VALIDITY_DURATION
549 , b.VALIDITY_DURATION_UNITS
550 , b.RENEWABLE_FLAG
551 , b.VALIDITY_START_TYPE
552 , b.PUBLIC_FLAG
553 , b.START_DATE_ACTIVE
554 , b.END_DATE_ACTIVE
555 from ota_certifications_b b,
556 ota_cert_enrollments cre
557 where cre.certification_id = b.certification_id
558 and cre.cert_enrollment_id = p_cert_enrollment_id;
559
560 l_proc VARCHAR2(72) := g_package||' get_earl_enrl_dt';
561 rec_crt csr_crt%rowtype;
562
563 l_next_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
564
565
566 BEGIN
567
568 hr_utility.set_location('Entering :'||l_proc,5);
569
570 OPEN csr_crt;
571 FETCH csr_crt INTO rec_crt;
572 CLOSE csr_crt;
573
574 --renewal duration logic as it may be null or not null
575 --regulatory fixed date, validity starts from target date
576 if (rec_crt.initial_completion_date is not null and rec_crt.VALIDITY_START_TYPE = 'T') then
577 if (rec_crt.renewal_duration is not null) then
578 l_next_earliest_enroll_date := p_expiration_date - rec_crt.renewal_duration;
579 end if;
580 /*
581 --professional fixed date, validity starts from actual completion date
582 --elsif(rec_crt.initial_completion_date is not null and rec_crt.VALIDITY_START_TYPE = 'A') then
583 --not supported
584 --init dur with validity starts from actual target durn
585 elsif (rec_crt.INITIAL_COMPLETION_DURATION is not null and rec_crt.VALIDITY_START_TYPE = 'T') then
586 if (rec_crt.renewal_duration is not null) then
587 l_next_earliest_enroll_date := p_expiration_date - rec_crt.renewal_duration;
588 else
589 --popl the values initially and update this with compl date on the day learner completes
590 l_next_earliest_enroll_date := p_expiration_date;
591 end if;
592 --regulatory init dur with validity starts from actual completion date
593 elsif (rec_crt.INITIAL_COMPLETION_DURATION is not null and rec_crt.VALIDITY_START_TYPE = 'A') then
594 --popl the values initially and recalculate this val as "lrnr_compl date + rec_crt.validity_duration"
595 -- also recalc the earliest enroll date when renewal_duration is null
596 if (rec_crt.renewal_duration is not null) then
597 l_next_earliest_enroll_date := p_expiration_date - rec_crt.renewal_duration;
598 else
599 l_next_earliest_enroll_date := p_expiration_date;
600 end if;
601 */
602 end if;
603
604 hr_utility.set_location('Leaving :'||l_proc,10);
605
606 return l_next_earliest_enroll_date;
607
608 EXCEPTION
609 WHEN others THEN
610 hr_utility.set_location('Leaving :'||l_proc,15);
611 RETURN NULL;
612
613 end get_earl_enrl_dt;
614
615 PROCEDURE calc_cre_dates(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
616 p_certification_id in ota_cert_enrollments.certification_id%type,
617 p_mode in varchar2,
618 p_earliest_enroll_date OUT nocopy ota_cert_enrollments.earliest_enroll_date%type,
619 p_expiration_date OUT nocopy ota_cert_enrollments.expiration_date%type,
620 p_cert_period_start_date in date default sysdate)
621 IS
622
623 CURSOR csr_crt IS
624 select
625 b.certification_id certification_id
626 , b.INITIAL_COMPLETION_DATE
627 , b.INITIAL_COMPLETION_DURATION
628 , b.INITIAL_COMPL_DURATION_UNITS
629 , b.RENEWAL_DURATION
630 , b.RENEWAL_DURATION_UNITS
631 , b.NOTIFY_DAYS_BEFORE_EXPIRE
632 , b.VALIDITY_DURATION
633 , b.VALIDITY_DURATION_UNITS
634 , b.RENEWABLE_FLAG
635 , b.VALIDITY_START_TYPE
636 , b.PUBLIC_FLAG
637 , b.START_DATE_ACTIVE
638 , b.END_DATE_ACTIVE
639 from ota_certifications_b b
640 where certification_id = p_certification_id;
641
642
643 CURSOR csr_cre IS
644 select
645 trunc(cre.earliest_enroll_date) earliest_enroll_date --bug#8775942
646 , trunc(cre.expiration_date) expiration_date
647 from ota_certifications_b b,
648 ota_cert_enrollments cre
649 where cre.certification_id = b.certification_id
650 and cre.cert_enrollment_id = p_cert_enrollment_id;
651
652
653 CURSOR csr_max_cpe_exp_dt IS
654 select
655 max(cpe.expiration_date)
656 from ota_cert_prd_enrollments cpe,
657 ota_cert_enrollments cre
658 where cpe.cert_enrollment_id = cre.cert_enrollment_id
659 and cre.cert_enrollment_id = p_cert_enrollment_id;
660
661 l_proc VARCHAR2(72) := g_package||'calc_cre_dates';
662 rec_crt csr_crt%rowtype;
663 rec_cre csr_cre%rowtype;
664
665 l_curr_dt date;
666
667 l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
668 l_expiration_date ota_cert_enrollments.expiration_date%type;
669 l_max_expiration_date date;
670
671 l_next_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
672 l_next_expiration_date ota_cert_enrollments.expiration_date%type;
673
674 BEGIN
675 hr_utility.set_location('Entering :'||l_proc,5);
676
677 l_curr_dt := trunc(nvl(p_cert_period_start_date, sysdate));
678
679 OPEN csr_crt;
680 FETCH csr_crt INTO rec_crt;
681 CLOSE csr_crt;
682
683 if (rec_crt.RENEWABLE_FLAG is null or rec_crt.RENEWABLE_FLAG = 'N') then
684 p_earliest_enroll_date := null;
685 p_expiration_date := null;
686 --return null;
687 return;
688 end if;
689
690 if p_mode = 'I' then
691 --initial mode
692 -- put the initial calc logic for earliest and exp dates
693 --regulatory fixed date, validity starts from target date
694 if (rec_crt.initial_completion_date is not null and rec_crt.VALIDITY_START_TYPE = 'T') then
695 --Bug 4940007 for late subscriptions
696 --exp date for on time subscr
697 if l_curr_dt <= trunc(rec_crt.initial_completion_date) then
698 l_expiration_date := rec_crt.initial_completion_date + rec_crt.validity_duration;
699 --always ren dur popl for date based
700 if (rec_crt.renewal_duration is not null) then
701 l_earliest_enroll_date := l_expiration_date - rec_crt.renewal_duration;
702 end if;
703 else
704 --elapsed exp date for late subscr
705 l_expiration_date := trunc(rec_crt.initial_completion_date);
706 LOOP
707 l_expiration_date := l_expiration_date + rec_crt.validity_duration;
708 EXIT WHEN l_curr_dt < trunc(l_expiration_date);
709 END LOOP;
710 if rec_crt.renewal_duration = rec_crt.validity_duration then
711 --this would be true if renewal is from target
712 --late subscr always have first perd exp as earliest enr dt
713 l_earliest_enroll_date := l_expiration_date;
714 else
715 --this would be true if renewal is n days before exp date
716 --late subscr have first perd exp minus renewal days before exp value
717 l_earliest_enroll_date := l_expiration_date - rec_crt.renewal_duration;
718 end if;
719 end if;
720 elsif (rec_crt.INITIAL_COMPLETION_DURATION is not null) then
721 --initialize as if the learner doesn't complete by due date,
722 --then based on when they compl and renewal/validity types recalc these dates
723 --l_expiration_date := trunc(sysdate) + rec_crt.initial_completion_duration;
724 --l_expiration_date := trunc(sysdate) + rec_crt.initial_completion_duration + rec_crt.validity_duration;
725 --l_earliest_enroll_date := trunc(sysdate) + rec_crt.initial_completion_duration;
726
727 l_expiration_date := trunc(nvl(p_cert_period_start_date, sysdate)) + rec_crt.initial_completion_duration + rec_crt.validity_duration;
728 l_earliest_enroll_date := trunc(nvl(p_cert_period_start_date, sysdate)) + rec_crt.initial_completion_duration;
729 end if;
730 p_earliest_enroll_date := l_earliest_enroll_date;
731 p_expiration_date := l_expiration_date;
732 elsif p_mode = 'V' then
733 --verification mode
734 --isrenewal chk requires logic to calc sysdate w/ expdate
735 --default to next immediate dates, used for Notifs
736 OPEN csr_cre;
737 FETCH csr_cre INTO rec_cre;
738 CLOSE csr_cre;
739
740 OPEN csr_max_cpe_exp_dt;
741 FETCH csr_max_cpe_exp_dt INTO l_max_expiration_date;
742 CLOSE csr_max_cpe_exp_dt;
743
744 --for based by dur we don't need re-enroll restriction as is the case
745 --with based by date certs which req adding validity_dur to exp dt
746 if rec_crt.initial_completion_duration is not null then
747 l_next_earliest_enroll_date := rec_cre.earliest_enroll_date;
748 l_next_expiration_date := l_max_expiration_date;
749 else
750 if l_curr_dt <= trunc(l_max_expiration_date) then
751 p_earliest_enroll_date := rec_cre.earliest_enroll_date;
752 p_expiration_date := l_max_expiration_date;
753 return;
754 else
755 l_next_expiration_date := l_max_expiration_date;
756
757 --loop next calc exp date and check for curr_dt
758
759 if l_next_expiration_date is not null then
760 LOOP
761 l_next_expiration_date := l_next_expiration_date + rec_crt.validity_duration;
762 EXIT WHEN l_curr_dt < trunc(l_next_expiration_date);
763 END LOOP;
764 l_next_earliest_enroll_date := get_earl_enrl_dt(p_cert_enrollment_id, l_next_expiration_date);
765 else
766 --return with null
767 p_earliest_enroll_date := null;
768 p_expiration_date := null;
769 return;
770 end if;
771
772 end if;
773 end if;
774
775 p_earliest_enroll_date := l_next_earliest_enroll_date;
776 p_expiration_date := l_next_expiration_date;
777 else
778 --renew mode
779 --default to next immediate dates, used for period renewal and notifs
780 OPEN csr_cre;
781 FETCH csr_cre INTO rec_cre;
782 CLOSE csr_cre;
783
784
785 --for based by dur we don't need re-enroll restriction as is the case
786 --with based by date certs which req adding validity_dur to exp dt
787 if rec_crt.initial_completion_duration is not null then
788 --initialize as if the learner doesn't complete by due date,
789 --then based on when they compl and renewal/validity types recalc these dates
790 --l_expiration_date := trunc(sysdate) + rec_crt.initial_completion_duration;
791 --l_next_expiration_date := trunc(sysdate) + rec_crt.initial_completion_duration + rec_crt.validity_duration;
792 --l_next_earliest_enroll_date := trunc(sysdate) + rec_crt.initial_completion_duration;
793
794 l_next_expiration_date := trunc(nvl(p_cert_period_start_date, sysdate)) + rec_crt.initial_completion_duration + rec_crt.validity_duration;
795 l_next_earliest_enroll_date := trunc(nvl(p_cert_period_start_date, sysdate)) + rec_crt.initial_completion_duration;
796 else
797 --loop next calc exp date and check for curr_dt
798 OPEN csr_max_cpe_exp_dt;
799 FETCH csr_max_cpe_exp_dt INTO l_max_expiration_date;
800 CLOSE csr_max_cpe_exp_dt;
801
802 l_next_expiration_date := l_max_expiration_date;
803
804 if l_next_expiration_date is not null then
805 LOOP
806 l_next_expiration_date := l_next_expiration_date + rec_crt.validity_duration;
807 EXIT WHEN l_curr_dt < trunc(l_next_expiration_date);
808 END LOOP;
809 l_next_earliest_enroll_date := get_earl_enrl_dt(p_cert_enrollment_id, l_next_expiration_date);
810 else
811 --return with null
812 p_earliest_enroll_date := null;
813 p_expiration_date := null;
814 return;
815 end if;
816 end if;
817
818 p_earliest_enroll_date := l_next_earliest_enroll_date;
819 p_expiration_date := l_next_expiration_date;
820 end if; --pmode check
821
822 hr_utility.set_location('Leaving :'||l_proc,10);
823
824 EXCEPTION
825 WHEN others THEN
826 hr_utility.set_location('LEAVING:'|| l_proc, 30);
827 p_earliest_enroll_date := null;
828 p_expiration_date := null;
829 RAISE;
830 END calc_cre_dates;
831
832 Function get_next_prd_dur_days(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
833 p_cert_period_start_date in date default sysdate )
834 return varchar2
835 IS
836
837 CURSOR csr_crt IS
838 select
839 b.certification_id certification_id
840 , b.INITIAL_COMPLETION_DATE
841 , b.INITIAL_COMPLETION_DURATION
842 , b.INITIAL_COMPL_DURATION_UNITS
843 , b.validity_duration
844 , b.START_DATE_ACTIVE
845 , b.END_DATE_ACTIVE
846 , cre.expiration_date
847 , b.renewable_flag --Bug 4545407
848 from ota_certifications_b b,
849 ota_cert_enrollments cre
850 where cre.certification_id = b.certification_id
851 and cre.cert_enrollment_id = p_cert_enrollment_id;
852
853
854 CURSOR csr_max_cpe_exp_dt IS
855 select
856 max(cpe.expiration_date)
857 from ota_cert_prd_enrollments cpe,
858 ota_cert_enrollments cre
859 where cpe.cert_enrollment_id = cre.cert_enrollment_id
860 and cre.cert_enrollment_id = p_cert_enrollment_id;
861
862 l_proc VARCHAR2(72) := g_package||' get_next_prd_dur_days';
863 rec_crt csr_crt%rowtype;
864
865 l_curr_dt date;
866 l_prd_durn varchar2(10);
867 l_next_expiration_date ota_cert_enrollments.expiration_date%type;
868 l_max_expiration_date date;
869 l_due_date date;
870 l_elapsed_due_date date;
871
872 BEGIN
873
874 hr_utility.set_location('Entering :'||l_proc,5);
875
876 OPEN csr_crt;
877 FETCH csr_crt INTO rec_crt;
878 CLOSE csr_crt;
879
880 l_curr_dt := trunc(nvl(p_cert_period_start_date, sysdate));
881
882 --calculate period end date
883 if (rec_crt.initial_completion_date is not null) then
884 if rec_crt.renewable_flag = 'Y' then -- For Bug 4545407
885 --for date based cert, always due date is derived from the last exp date + val dur
886 OPEN csr_max_cpe_exp_dt;
887 FETCH csr_max_cpe_exp_dt INTO l_max_expiration_date;
888 CLOSE csr_max_cpe_exp_dt;
889
890 if l_max_expiration_date is not null then
891 l_next_expiration_date := l_max_expiration_date;
892 end if;
893
894 --if the cpe rec is not yet created then l_next_expiration_date
895 --is null and due date is same as initial_completion_date
896 --whereas if l_next_expiration_date is not null then due date and exp date
897 --remains same
898
899 if l_next_expiration_date is not null then
900 LOOP
901 l_next_expiration_date := l_next_expiration_date + rec_crt.validity_duration;
902 EXIT WHEN l_curr_dt < l_next_expiration_date;
903 END LOOP;
904 l_prd_durn := trunc(l_next_expiration_date) - l_curr_dt;
905 else
906 --initial period rec
907 --Bug 4940007 for late subscriptions
908 --due date for on time subscr
909 if l_curr_dt <= trunc(rec_crt.initial_completion_date) then
910 l_prd_durn := trunc(rec_crt.initial_completion_date) - l_curr_dt;
911 else
912 --elapsed due date for late subscr
913 l_elapsed_due_date := trunc(rec_crt.initial_completion_date);
914 LOOP
915 l_elapsed_due_date := l_elapsed_due_date + rec_crt.validity_duration;
916 EXIT WHEN l_curr_dt < l_elapsed_due_date;
917 END LOOP;
918 l_prd_durn := trunc(l_elapsed_due_date) - l_curr_dt;
919 end if;
920 end if;
921 else --Bug 4545407
922 l_prd_durn := trunc(rec_crt.initial_completion_date) - l_curr_dt;
923 end if; -- Bug 4545407
924 elsif (rec_crt.initial_completion_duration is not null) then
925 l_prd_durn := rec_crt.initial_completion_duration;
926 end if;
927
928 l_prd_durn := '' || l_prd_durn;
929
930 hr_utility.set_location('Leaving :'||l_proc,10);
931
932 return l_prd_durn;
933
934 EXCEPTION
935 WHEN others THEN
936 hr_utility.set_location('Leaving :'||l_proc,15);
937 RETURN null;
938
939 end get_next_prd_dur_days;
940 --
941 FUNCTION get_cert_mbr_status (p_cert_mbr_id in ota_certification_members.certification_member_id%TYPE,
942 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
943 p_code in number default 1)
944 RETURN varchar2 is
945
946 Cursor get_cert_mbr_status is
947 Select member_status_code
948 ,ota_utility.get_lookup_meaning('OTA_CERT_MBR_ENROLL_STATUS', member_status_code, 810) member_status
949 From ota_cert_mbr_enrollments
950 where
951 cert_member_id = p_cert_mbr_id
952 and cert_prd_enrollment_id = p_cert_prd_enrollment_id;
953
954 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
955 l_member_status varchar2(240);
956
957 Begin
958 Open get_cert_mbr_status;
959 Fetch get_cert_mbr_status into l_member_status_code, l_member_status;
960 Close get_cert_mbr_status;
961
962 if ( p_code = 1 ) then
963 return l_member_status_code;
964 else
965 return l_member_status;
966 end if;
967 End get_cert_mbr_status;
968 --
969 FUNCTION get_cert_mbr_name (p_cert_mbr_id in ota_certification_members.certification_member_id%TYPE)
970 RETURN varchar2 is
971
972 Cursor get_cert_mbr_data is
973 Select object_id, object_type
974 From ota_certification_members
975 Where certification_member_id = p_cert_mbr_id;
976
977 Cursor get_course_name(p_act_ver_id in ota_activity_versions.activity_version_id%TYPE) is
978 Select version_name
979 From ota_activity_versions_vl
980 Where activity_version_id = p_act_ver_id;
981
982 l_object_id ota_certification_members.object_id%TYPE;
983 l_object_type ota_certification_members.object_type%TYPE;
984 l_member_name varchar2(240);
985 Begin
986 Open get_cert_mbr_data;
987 Fetch get_cert_mbr_data into l_object_id, l_object_type;
988 Close get_cert_mbr_data;
989
990 if (l_object_type = 'H') then --Course
991 Open get_course_name(l_object_id);
992 Fetch get_course_name into l_member_name;
993 Close get_course_name;
994 end if;
995
996 return l_member_name;
997 End get_cert_mbr_name;
998
999 FUNCTION get_cre_status (p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%TYPE,
1000 p_mode in varchar2 default 'm')
1001 RETURN varchar2 is
1002
1003 Cursor csr_cre_status is
1004 Select cre.certification_status_code,
1005 cre.completion_date,
1006 crt.validity_duration,
1007 ota_utility.get_lookup_meaning('OTA_CERT_ENROLL_STATUS', cre.certification_status_code, 810) cre_status,
1008 crt.renewable_flag,
1009 cre.expiration_date,
1010 nvl(crt.end_date_active,to_date('4712/12/31','YYYY/MM/DD')) end_date_active
1011 From ota_cert_enrollments cre,
1012 ota_certifications_b crt
1013 where cre.cert_enrollment_id = p_cert_enrollment_id
1014 and crt.certification_id = cre.certification_id;
1015
1016 l_proc VARCHAR2(72) := g_package||' get_cre_status';
1017 rec_cre_status csr_cre_status%rowtype;
1018
1019 l_cre_status varchar2(240);
1020 l_cre_status_code varchar2(30);
1021 l_old_exp_date date;
1022 l_curr_date date;
1023
1024 Begin
1025 open csr_cre_status;
1026 fetch csr_cre_status into rec_cre_status;
1027 close csr_cre_status;
1028
1029 l_curr_date := trunc(sysdate);
1030
1031 if (l_curr_date <= rec_cre_status.end_date_active) then
1032 if (rec_cre_status.renewable_flag = 'Y') then
1033 if (rec_cre_status.completion_date is not null and
1034 not (rec_cre_status.certification_status_code = 'EXPIRED'
1035 or rec_cre_status.certification_status_code = 'CANCELLED')) then
1036 --l_old_exp_date := rec_cre_status.completion_date + rec_cre_status.validity_duration;
1037 -- this could happen only for certs whose reenrl type is immed after compl.
1038 if (l_curr_date > rec_cre_status.expiration_date) then --
1039 l_cre_status := ota_utility.get_lookup_meaning('OTA_CERT_ENROLL_STATUS', 'EXPIRED', 810);
1040 l_cre_status_code := 'EXPIRED';
1041 --return l_cre_status;
1042 else
1043 --return rec_cre_status.cre_status;
1044 l_cre_status := rec_cre_status.cre_status;
1045 l_cre_status_code := rec_cre_status.certification_status_code;
1046 end if;
1047 else
1048 --return rec_cre_status.cre_status;
1049 l_cre_status := rec_cre_status.cre_status;
1050 l_cre_status_code := rec_cre_status.certification_status_code;
1051 end if;
1052 else
1053 --return rec_cre_status.cre_status;
1054 l_cre_status := rec_cre_status.cre_status;
1055 l_cre_status_code := rec_cre_status.certification_status_code;
1056 end if;
1057 else
1058 --concluded
1059 l_cre_status := ota_utility.get_lookup_meaning('OTA_CERT_ENROLL_STATUS', 'CONCLUDED', 810);
1060 l_cre_status_code := 'CONCLUDED';
1061
1062 end if;
1063
1064 if p_mode = 'm' then
1065 return l_cre_status;
1066 else
1067 return l_cre_status_code;
1068 end if;
1069
1070 EXCEPTION
1071 WHEN others THEN
1072 hr_utility.set_location('Leaving :'||l_proc,15);
1073 RETURN null;
1074
1075 End get_cre_status;
1076
1077 FUNCTION get_cpe_edit_enabled(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE)
1078 RETURN varchar2 is
1079
1080 Cursor csr_cpe is
1081 Select
1082 get_cre_status(cre.cert_enrollment_id, 'c') certification_status_code
1083 From ota_cert_enrollments cre,
1084 ota_cert_prd_enrollments cpe
1085 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
1086 and cre.cert_enrollment_id = cpe.cert_enrollment_id;
1087
1088 l_proc VARCHAR2(72) := g_package||' get_cpe_edit_enabled';
1089 rec_cpe csr_cpe%rowtype;
1090
1091 Begin
1092 open csr_cpe;
1093 fetch csr_cpe into rec_cpe;
1094 close csr_cpe;
1095
1096 if (rec_cpe.certification_status_code = 'CERTIFIED' or
1097 rec_cpe.certification_status_code = 'EXPIRED' or
1098 rec_cpe.certification_status_code = 'ENROLLED') then
1099 return 'Y';
1100 else
1101 return 'N';
1102 end if;
1103
1104
1105 EXCEPTION
1106 WHEN others THEN
1107 hr_utility.set_location('Leaving :'||l_proc,15);
1108 RETURN null;
1109
1110 End get_cpe_edit_enabled;
1111
1112 FUNCTION chk_prd_end_date(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
1113 p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,
1114 p_mass_update_flag in varchar2 default 'N') return varchar2 IS
1115
1116 CURSOR csr_cert_enrl IS
1117 select cre.certification_status_code,
1118 cre.completion_date,
1119 cre.certification_id,
1120 cpe.expiration_date,
1121 cpe.cert_period_end_date,
1122 cpe.period_status_code,
1123 crt.renewable_flag,
1124 crt.initial_completion_duration,
1125 crt.validity_duration,
1126 crt.validity_start_type
1127 FROM ota_cert_enrollments cre,
1128 ota_cert_prd_enrollments cpe,
1129 ota_certifications_b crt
1130 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
1131 and cre.certification_id = crt.certification_id
1132 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
1133
1134 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
1135
1136 --
1137 l_proc VARCHAR2(72) := g_package||'chk_prd_end_date';
1138 l_return_status varchar2(1):= 'S';
1139 l_bypass_flag varchar2(1) := 'N';
1140
1141 BEGIN
1142 hr_utility.set_location(' Leaving:'||l_proc, 10);
1143
1144 if p_mass_update_flag = 'N' then
1145 hr_multi_message.enable_message_list;
1146 end if;
1147
1148 OPEN csr_cert_enrl;
1149 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
1150 CLOSE csr_cert_enrl;
1151
1152 --bypass for cert whose start_type counts from compl, and validity_prd is less than initi compl durn , and compl_date is populated before
1153 if ( l_cert_enrl_rec.renewable_flag = 'Y' and
1154 l_cert_enrl_rec.validity_start_type = 'A' and
1155 (l_cert_enrl_rec.validity_duration < l_cert_enrl_rec.initial_completion_duration) and
1156 l_cert_enrl_rec.completion_date is not null) then
1157 l_bypass_flag := 'Y';
1158 end if;
1159
1160 if l_cert_enrl_rec.expiration_date is not null then
1161 if (l_cert_enrl_rec.period_status_code <> 'COMPLETED' and l_bypass_flag = 'N') then
1162 if (p_cert_period_end_date > l_cert_enrl_rec.expiration_date or
1163 p_cert_period_end_date < l_cert_enrl_rec.cert_period_end_date) then
1164 --throw invalid period end data error
1165 --l_result := 'E';
1166 if p_mass_update_flag = 'N' then
1167 fnd_message.set_name('OTA','OTA_443893_CPE_PRD_END_DT_ERR');
1168 fnd_message.set_token('DUE_DATE', l_cert_enrl_rec.cert_period_end_date);
1169 fnd_message.set_token('EXP_DATE', l_cert_enrl_rec.expiration_date);
1170 fnd_message.raise_error;
1171 else
1172 l_return_status := 'E';
1173 end if;
1174 end if;
1175 end if;
1176 end if;
1177
1178 hr_utility.set_location(' Leaving:'||l_proc, 20);
1179
1180 if p_mass_update_flag = 'N' then
1181 l_return_status := hr_multi_message.get_return_status_disable;
1182 end if;
1183
1184 return l_return_status;
1185
1186 EXCEPTION
1187
1188 WHEN app_exception.application_exception THEN
1189 hr_utility.set_location(' Leaving:'||l_proc, 25);
1190
1191 if p_mass_update_flag = 'N' then
1192 if hr_multi_message.exception_add(p_associated_column1 => 'OTA_CERT_PRD_ENROLLMENTS.CERT_PERIOD_END_DATE') then
1193 return hr_multi_message.get_return_status_disable;
1194 end if;
1195 l_return_status := hr_multi_message.get_return_status_disable;
1196 else
1197 l_return_status := 'E';
1198 end if;
1199
1200 return l_return_status;
1201
1202 WHEN others THEN
1203 hr_utility.set_location('Leaving :'||l_proc,30);
1204 if p_mass_update_flag = 'N' then
1205 l_return_status := hr_multi_message.get_return_status_disable;
1206 else
1207 l_return_status := 'E';
1208 end if;
1209
1210 return l_return_status;
1211
1212 END chk_prd_end_date;
1213
1214 procedure create_cpe_rec(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
1215 p_expiration_date in date,
1216 p_cert_period_start_date in date default sysdate,
1217 p_cert_prd_enrollment_id OUT NOCOPY ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1218 p_certification_status_code OUT NOCOPY VARCHAR2,
1219 p_is_recert in varchar2 default 'N')
1220 IS
1221 CURSOR csr_cert_info(p_certification_id in ota_cert_enrollments.certification_id%type) IS
1222 select
1223 b.certification_id certification_id
1224 , b.INITIAL_COMPLETION_DATE
1225 , b.INITIAL_COMPLETION_DURATION
1226 , b.INITIAL_COMPL_DURATION_UNITS
1227 , b.RENEWAL_DURATION
1228 , b.RENEWAL_DURATION_UNITS
1229 , b.NOTIFY_DAYS_BEFORE_EXPIRE
1230 , b.VALIDITY_DURATION
1231 , b.VALIDITY_DURATION_UNITS
1232 , b.RENEWABLE_FLAG
1233 , b.VALIDITY_START_TYPE
1234 , b.PUBLIC_FLAG
1235 , b.START_DATE_ACTIVE
1236 , b.END_DATE_ACTIVE
1237 from ota_certifications_b b
1238 where b.certification_id = p_certification_id;
1239
1240 CURSOR csr_cert_mbr(p_certification_id in ota_cert_enrollments.certification_id%type) IS
1241 select
1242 cmb.CERTIFICATION_MEMBER_ID
1243 , cmb.CERTIFICATION_ID
1244 , cmb.OBJECT_ID
1245 , cmb.OBJECT_TYPE
1246 , cmb.MEMBER_SEQUENCE
1247 , cmb.START_DATE_ACTIVE
1248 , cmb.END_DATE_ACTIVE
1249 from ota_certification_members cmb
1250 where cmb.certification_id = p_certification_id
1251 and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE) and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'));
1252
1253 CURSOR csr_cert_enrl IS
1254 select certification_id, cert_enrollment_id, business_group_id, certification_status_code,
1255 object_version_number, completion_date, expiration_date
1256 FROM ota_cert_enrollments
1257 where cert_enrollment_id = p_cert_enrollment_id;
1258
1259 CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
1260 select period_status_code, object_version_number, completion_date
1261 FROM ota_cert_prd_enrollments
1262 where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
1263
1264 Cursor one_child_active(l_cert_prd_enrollment_id in number) IS
1265 Select cme.cert_mbr_enrollment_id
1266 from ota_cert_mbr_enrollments cme
1267 where member_status_code in ('ACTIVE', 'PENDING', 'COMPLETED') --10164130
1268 and cert_prd_enrollment_id = l_cert_prd_enrollment_id and rownum=1;
1269
1270 CURSOR csr_prd_enrl_count IS
1271 select count(cert_prd_enrollment_id)
1272 FROM ota_cert_prd_enrollments
1273 where cert_enrollment_id = p_cert_enrollment_id;
1274
1275 Cursor get_mbr_completion_date(l_cert_prd_enrollment_id in number) is
1276 Select min(cme.completion_date)
1277 from ota_cert_mbr_enrollments cme
1278 where member_status_code = 'COMPLETED'
1279 and cert_prd_enrollment_id = l_cert_prd_enrollment_id;
1280 l_proc varchar2(72) := g_package || ' create_cpe_rec';
1281 l_cert_rec csr_cert_info%ROWTYPE;
1282 l_cert_mbr_rec csr_cert_mbr%ROWTYPE;
1283 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
1284 l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
1285
1286 l_complete_cert_prd_ok VARCHAR2(30);
1287
1288 l_period_status_code VARCHAR2(30);
1289 n_period_status_code VARCHAR2(30);
1290
1291 l_object_version_number1 number;
1292 l_object_version_number2 number;
1293
1294 l_completion_date DATE;
1295 l_prd_completion_date DATE;
1296 l_cert_prd_enrollment_id NUMBER;
1297 l_cert_mbr_enrollment_id NUMBER;
1298
1299 l_period_start_date DATE;
1300 l_period_end_date DATE;
1301
1302 l_expiration_date DATE;
1303 l_earliest_enroll_date DATE;
1304
1305 p_effective_date DATE;
1306 p_business_group_id DATE;
1307
1308 l_certification_status_code VARCHAR2(30);
1309 p_is_history_flag VARCHAR2(1);
1310
1311 l_attribute_category VARCHAR2(30) := NULL;
1312 l_attribute1 VARCHAR2(150) := NULL;
1313 l_attribute2 VARCHAR2(150) := NULL;
1314 l_attribute3 VARCHAR2(150) := NULL;
1315 l_attribute4 VARCHAR2(150) := NULL;
1316 l_attribute5 VARCHAR2(150) := NULL;
1317 l_attribute6 VARCHAR2(150) := NULL;
1318 l_attribute7 VARCHAR2(150) := NULL;
1319 l_attribute8 VARCHAR2(150) := NULL;
1320 l_attribute9 VARCHAR2(150) := NULL;
1321 l_attribute10 VARCHAR2(150) := NULL;
1322 l_attribute11 VARCHAR2(150) := NULL;
1323 l_attribute12 VARCHAR2(150) := NULL;
1324 l_attribute13 VARCHAR2(150) := NULL;
1325 l_attribute14 VARCHAR2(150) := NULL;
1326 l_attribute15 VARCHAR2(150) := NULL;
1327 l_attribute16 VARCHAR2(150) := NULL;
1328 l_attribute17 VARCHAR2(150) := NULL;
1329 l_attribute18 VARCHAR2(150) := NULL;
1330 l_attribute19 VARCHAR2(150) := NULL;
1331 l_attribute20 VARCHAR2(150) := NULL;
1332
1333 l_prd_enrl_count NUMBER;
1334
1335 BEGIN
1336
1337 OPEN csr_cert_enrl;
1338 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
1339 CLOSE csr_cert_enrl;
1340
1341 l_certification_status_code := l_cert_enrl_rec.certification_status_code;
1342 l_expiration_date := l_cert_enrl_rec.expiration_date;
1343
1344 OPEN csr_cert_info(l_cert_enrl_rec.certification_id);
1345 FETCH csr_cert_info INTO l_cert_rec;
1346 CLOSE csr_cert_info;
1347
1348 --initialize period status to ENROLLED
1349 l_period_status_code := 'ENROLLED';
1350
1351
1352 --create CPE and CME based on approval mode is off or enrl approval is granted success;
1353
1354
1355 --calculate period end date
1356 --l_period_end_date := trunc(sysdate) + get_next_prd_dur_days(p_cert_enrollment_id);
1357 l_period_end_date := trunc(p_cert_period_start_date) + get_next_prd_dur_days(p_cert_enrollment_id, p_cert_period_start_date);
1358
1359 /*
1360 if (l_cert_rec.initial_completion_date is not null) then
1361 l_period_end_date := trunc(l_cert_rec.initial_completion_date);
1362 elsif (l_cert_rec.initial_completion_duration is not null) then
1363 l_period_end_date := trunc(sysdate) + l_cert_rec.initial_completion_duration - 1;
1364 end if;
1365 */
1366 ota_utility.Get_Default_Value_Dff(
1367 appl_short_name => 'OTA'
1368 ,flex_field_name => 'OTA_CERT_PRD_ENROLLMENTS'
1369 ,p_attribute_category => l_attribute_category
1370 ,p_attribute1 => l_attribute1
1371 ,p_attribute2 => l_attribute2
1372 ,p_attribute3 => l_attribute3
1373 ,p_attribute4 => l_attribute4
1374 ,p_attribute5 => l_attribute5
1375 ,p_attribute6 => l_attribute6
1376 ,p_attribute7 => l_attribute7
1377 ,p_attribute8 => l_attribute8
1378 ,p_attribute9 => l_attribute9
1379 ,p_attribute10 => l_attribute10
1380 ,p_attribute11 => l_attribute11
1381 ,p_attribute12 => l_attribute12
1382 ,p_attribute13 => l_attribute13
1383 ,p_attribute14 => l_attribute14
1384 ,p_attribute15 => l_attribute15
1385 ,p_attribute16 => l_attribute16
1386 ,p_attribute17 => l_attribute17
1387 ,p_attribute18 => l_attribute18
1388 ,p_attribute19 => l_attribute19
1389 ,p_attribute20 => l_attribute20);
1390
1391 ota_cert_prd_enrollment_api.create_cert_prd_enrollment(
1392 p_effective_date => trunc(sysdate)
1393 ,p_cert_enrollment_id => p_cert_enrollment_id
1394 ,p_period_status_code => l_period_status_code
1395 ,p_cert_period_start_date => trunc(p_cert_period_start_date) --trunc(sysdate)
1396 ,p_cert_period_end_date => l_period_end_date
1397 ,p_business_group_id => l_cert_enrl_rec.business_group_id
1398 ,p_expiration_date => p_expiration_date
1399 ,p_cert_prd_enrollment_id => l_cert_prd_enrollment_id
1400 ,p_object_version_number => l_object_version_number1
1401 ,p_attribute_category => l_attribute_category
1402 ,p_attribute1 => l_attribute1
1403 ,p_attribute2 => l_attribute2
1404 ,p_attribute3 => l_attribute3
1405 ,p_attribute4 => l_attribute4
1406 ,p_attribute5 => l_attribute5
1407 ,p_attribute6 => l_attribute6
1408 ,p_attribute7 => l_attribute7
1409 ,p_attribute8 => l_attribute8
1410 ,p_attribute9 => l_attribute9
1411 ,p_attribute10 => l_attribute10
1412 ,p_attribute11 => l_attribute11
1413 ,p_attribute12 => l_attribute12
1414 ,p_attribute13 => l_attribute13
1415 ,p_attribute14 => l_attribute14
1416 ,p_attribute15 => l_attribute15
1417 ,p_attribute16 => l_attribute16
1418 ,p_attribute17 => l_attribute17
1419 ,p_attribute18 => l_attribute18
1420 ,p_attribute19 => l_attribute19
1421 ,p_attribute20 => l_attribute20
1422 );
1423
1424 l_attribute_category := NULL;
1425 l_attribute1 := NULL;
1426 l_attribute2 := NULL;
1427 l_attribute3 := NULL;
1428 l_attribute4 := NULL;
1429 l_attribute5 := NULL;
1430 l_attribute6 := NULL;
1431 l_attribute7 := NULL;
1432 l_attribute8 := NULL;
1433 l_attribute9 := NULL;
1434 l_attribute10 := NULL;
1435 l_attribute11 := NULL;
1436 l_attribute12 := NULL;
1437 l_attribute13 := NULL;
1438 l_attribute14 := NULL;
1439 l_attribute15 := NULL;
1440 l_attribute16 := NULL;
1441 l_attribute17 := NULL;
1442 l_attribute18 := NULL;
1443 l_attribute19 := NULL;
1444 l_attribute20 := NULL;
1445
1446 ota_utility.Get_Default_Value_Dff(
1447 appl_short_name => 'OTA'
1448 ,flex_field_name => 'OTA_CERT_MBR_ENROLLMENTS'
1449 ,p_attribute_category => l_attribute_category
1450 ,p_attribute1 => l_attribute1
1451 ,p_attribute2 => l_attribute2
1452 ,p_attribute3 => l_attribute3
1453 ,p_attribute4 => l_attribute4
1454 ,p_attribute5 => l_attribute5
1455 ,p_attribute6 => l_attribute6
1456 ,p_attribute7 => l_attribute7
1457 ,p_attribute8 => l_attribute8
1458 ,p_attribute9 => l_attribute9
1459 ,p_attribute10 => l_attribute10
1460 ,p_attribute11 => l_attribute11
1461 ,p_attribute12 => l_attribute12
1462 ,p_attribute13 => l_attribute13
1463 ,p_attribute14 => l_attribute14
1464 ,p_attribute15 => l_attribute15
1465 ,p_attribute16 => l_attribute16
1466 ,p_attribute17 => l_attribute17
1467 ,p_attribute18 => l_attribute18
1468 ,p_attribute19 => l_attribute19
1469 ,p_attribute20 => l_attribute20);
1470 FOR l_cert_mbr_rec IN csr_cert_mbr(l_cert_enrl_rec.certification_id) LOOP
1471 ota_cert_mbr_enrollment_api.create_cert_mbr_enrollment(
1472 p_effective_date => trunc(sysdate)
1473 ,p_cert_prd_enrollment_id => l_cert_prd_enrollment_id
1474 ,p_cert_member_id => l_cert_mbr_rec.certification_member_id
1475 ,p_member_status_code => 'PLANNED'
1476 ,p_business_group_id => l_cert_enrl_rec.business_group_id
1477 ,p_cert_mbr_enrollment_id => l_cert_mbr_enrollment_id
1478 ,p_object_version_number => l_object_version_number2
1479 ,p_attribute_category => l_attribute_category
1480 ,p_attribute1 => l_attribute1
1481 ,p_attribute2 => l_attribute2
1482 ,p_attribute3 => l_attribute3
1483 ,p_attribute4 => l_attribute4
1484 ,p_attribute5 => l_attribute5
1485 ,p_attribute6 => l_attribute6
1486 ,p_attribute7 => l_attribute7
1487 ,p_attribute8 => l_attribute8
1488 ,p_attribute9 => l_attribute9
1489 ,p_attribute10 => l_attribute10
1490 ,p_attribute11 => l_attribute11
1491 ,p_attribute12 => l_attribute12
1492 ,p_attribute13 => l_attribute13
1493 ,p_attribute14 => l_attribute14
1494 ,p_attribute15 => l_attribute15
1495 ,p_attribute16 => l_attribute16
1496 ,p_attribute17 => l_attribute17
1497 ,p_attribute18 => l_attribute18
1498 ,p_attribute19 => l_attribute19
1499 ,p_attribute20 => l_attribute20
1500 ,p_is_recert => p_is_recert --Bug 15938221
1501 );
1502 END LOOP;
1503
1504 -- verify the period cert enrol eligibility for marking complete
1505 l_complete_cert_prd_ok := chk_cert_prd_compl(p_cert_prd_enrollment_id => l_cert_prd_enrollment_id);
1506
1507 IF l_complete_cert_prd_ok = 'S' THEN
1508 -- The Plan can be completed
1509 n_period_status_code := 'COMPLETED';
1510 ELSIF l_complete_cert_prd_ok = 'F' THEN
1511 --if atleast one child is in ACTIVE or COMPLETED, mark the parent cpe as ACTIVE
1512 open one_child_active(l_cert_prd_enrollment_id);
1513 fetch one_child_active into l_cert_mbr_enrollment_id;
1514 if one_child_active%found then
1515 n_period_status_code := 'ACTIVE';
1516 end if;
1517 close one_child_active;
1518 END IF;
1519
1520 IF n_period_status_code <> l_period_status_code THEN
1521
1522 --update cpe and cre recs
1523 if n_period_status_code = 'ACTIVE' THEN
1524
1525 -- Bug#7303995
1526 open csr_prd_enrl_count;
1527 fetch csr_prd_enrl_count into l_prd_enrl_count;
1528 close csr_prd_enrl_count;
1529
1530 if(l_prd_enrl_count > 1 and p_is_recert = 'N') then
1531 l_certification_status_code := 'RENEWING';
1532 else
1533 l_certification_status_code := 'ENROLLED'; --Bug#7005319
1534 end if;
1535
1536 l_expiration_date := p_expiration_date;
1537 --donot reset the existing certification status code if its not completed
1538 --l_certification_status_code := l_cert_enrl_rec.certification_status_code;
1539 --dont reset to null on renewal cert prd
1540 l_prd_completion_date := null;
1541 l_completion_date := l_cert_enrl_rec.completion_date;
1542 elsif n_period_status_code = 'COMPLETED' THEN
1543 --cert enrol overall status code
1544 l_certification_status_code := 'CERTIFIED';
1545 open get_mbr_completion_date(l_cert_prd_enrollment_id);
1546 fetch get_mbr_completion_date into l_prd_completion_date;
1547 close get_mbr_completion_date;
1548
1549 l_prd_completion_date := trunc(nvl(l_prd_completion_date, sysdate));
1550 l_completion_date := l_prd_completion_date;
1551 end if;
1552
1553
1554 OPEN csr_prd_enrl(l_cert_prd_enrollment_id);
1555 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
1556 CLOSE csr_prd_enrl;
1557
1558 ota_cert_prd_enrollment_api.update_cert_prd_enrollment
1559 (p_effective_date => trunc(sysdate)
1560 ,p_cert_enrollment_id => p_cert_enrollment_id
1561 ,p_cert_prd_enrollment_id => l_cert_prd_enrollment_id
1562 ,p_object_version_number => l_prd_enrl_rec.object_version_number
1563 ,p_period_status_code => n_period_status_code
1564 ,p_completion_date => l_prd_completion_date);
1565
1566
1567 --update cre only if its already CERTIFIED on renew actn
1568 -- if l_certification_status_code = 'CERTIFIED' then
1569 ota_cert_enrollment_api.update_cert_enrollment
1570 (p_effective_date => trunc(sysdate)
1571 ,p_cert_enrollment_id => p_cert_enrollment_id
1572 ,p_certification_id => l_cert_enrl_rec.certification_id
1573 ,p_object_version_number => l_cert_enrl_rec.object_version_number
1574 ,p_certification_status_code => l_certification_status_code
1575 ,p_is_history_flag => 'N'
1576 ,p_completion_date => l_completion_date
1577 ,p_expiration_date => l_expiration_date);
1578 -- end if;
1579
1580 end if; -- status code check
1581
1582 --set output params
1583 p_cert_prd_enrollment_id := l_cert_prd_enrollment_id;
1584 p_certification_status_code := l_certification_status_code;
1585
1586 EXCEPTION
1587 WHEN others THEN
1588 --
1589 -- Reset IN OUT and set OUT parameters
1590 --
1591 p_cert_prd_enrollment_id := null;
1592 p_certification_status_code := null;
1593 hr_utility.set_location(' Leaving:' || l_proc,50);
1594 raise;
1595 end create_cpe_rec;
1596
1597 PROCEDURE delete_prd_cascade(p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1598 p_return_code OUT NOCOPY varchar2) IS
1599
1600 CURSOR csr_attempt IS
1601 SELECT attempt_id
1602 FROM ota_attempts
1603 WHERE cert_prd_enrollment_id = p_cert_prd_enrollment_id
1604 FOR UPDATE;
1605
1606 CURSOR csr_scorm_attempt(p_attempt_id ota_attempts.attempt_id%type) IS
1607 SELECT objective_id
1608 FROM OTA_SCORM_OBJ_ATTEMPTS
1609 WHERE attempt_id = p_attempt_id
1610 FOR UPDATE;
1611
1612 CURSOR csr_utest_ques(p_attempt_id ota_attempts.attempt_id%type) IS
1613 SELECT USER_TEST_QUESTION_ID
1614 FROM OTA_UTEST_QUESTIONS
1615 WHERE attempt_id = p_attempt_id
1616 FOR UPDATE;
1617
1618 l_return_code varchar2(1) := 'S';
1619
1620 BEGIN
1621 --Delete attempt dependent scorm attempts and scorm performances
1622 FOR attempt_rec IN csr_attempt
1623 LOOP
1624 --Delete scorm performances for the passed objective_id
1625 FOR scorm_attempt_rec IN csr_scorm_attempt(attempt_rec.attempt_id)
1626 LOOP
1627 DELETE FROM OTA_SCORM_OBJ_PERFS
1628 WHERE objective_id = scorm_attempt_rec.objective_id;
1629
1630 --Delete the fetched scorm attempt
1631 DELETE FROM OTA_SCORM_OBJ_ATTEMPTS
1632 WHERE CURRENT OF csr_scorm_attempt;
1633 END LOOP;
1634
1635 --Delete attempt specific test data, OTA_UTEST_QUESTIONS
1636 --OTA_UTEST_RESPONSES
1637 FOR utest_ques_rec IN csr_utest_ques(attempt_rec.attempt_id)
1638 LOOP
1639 --for each utest ques delete utest responses
1640 DELETE FROM OTA_UTEST_RESPONSES
1641 WHERE USER_TEST_QUESTION_ID = utest_ques_rec.USER_TEST_QUESTION_ID;
1642
1643 --Delete the fetched utest ques record
1644 DELETE FROM OTA_UTEST_QUESTIONS
1645 WHERE CURRENT OF csr_utest_ques;
1646
1647 END LOOP;
1648
1649 --Delete the fetched attempt record
1650 DELETE FROM OTA_ATTEMPTS
1651 WHERE CURRENT OF csr_attempt;
1652
1653 END LOOP;
1654
1655 --delete cert prd performance
1656 DELETE FROM ota_performances
1657 WHERE cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1658
1659 --delete cert mbr enrollments
1660 DELETE FROM ota_cert_mbr_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1661
1662 --delete cert prd enrollments
1663 DELETE FROM ota_cert_prd_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1664
1665 p_return_code := l_return_code;
1666 EXCEPTION
1667 WHEN others THEN
1668 --
1669 -- Reset IN OUT and set OUT parameters
1670 --
1671 p_return_code := 'E';
1672 END delete_prd_cascade;
1673
1674 procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
1675 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1676 p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1677 p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1678 p_return_status out NOCOPY VARCHAR2,
1679 p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate),
1680 p_cert_upd_comp in varchar2 default 'N') is
1681
1682 CURSOR csr_cert_enrl IS
1683 select cre.certification_status_code, cre.object_version_number, cre.completion_date,
1684 cre.certification_id,
1685 cre.person_id,
1686 cre.expiration_date
1687 FROM ota_cert_enrollments cre
1688 where cre.cert_enrollment_id = p_cert_enrollment_id;
1689
1690 CURSOR csr_prd_enrl IS
1691 select period_status_code, object_version_number, completion_date, cert_enrollment_id, cert_period_start_date, cert_period_end_date
1692 FROM ota_cert_prd_enrollments
1693 where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1694
1695 l_proc varchar2(72) := g_package || ' update_admin_changes';
1696 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
1697 l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
1698 l_return_status varchar2(1) := 'F';
1699 l_new_cre_status_code varchar2(30);
1700 l_new_cpe_status_code varchar2(30);
1701 l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%type;
1702 l_curr_date date;
1703 l_certification_status_code varchar2(30);
1704 l_is_period_update boolean := false;
1705 l_cert_period_completion_date ota_cert_prd_enrollments.completion_date%type;
1706 l_item_key wf_items.item_key%type;
1707 l_old_status_code ota_cert_enrollments.certification_status_code%type;
1708 l_cert_upd_comp varchar2(1);
1709 Begin
1710
1711 hr_utility.set_location(' Entering:' || l_proc,10);
1712 hr_utility.set_location(' p_certification_status_code is :' || l_proc || p_certification_status_code,100);
1713
1714 OPEN csr_prd_enrl;
1715 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
1716 CLOSE csr_prd_enrl;
1717
1718 if p_cert_prd_enrollment_id is not null then
1719 l_is_period_update := true;
1720 else
1721 l_is_period_update := false;
1722 end if;
1723
1724 if p_cert_period_end_date is not null and p_cert_period_end_date <> l_prd_enrl_rec.cert_period_end_date then
1725 l_cert_period_end_date := p_cert_period_end_date;
1726 else
1727 l_cert_period_end_date := l_prd_enrl_rec.cert_period_end_date;
1728 end if;
1729
1730 if (p_certification_status_code is not null and p_certification_status_code = 'EXPIRED') then
1731 --certSubscriptRowVO.setCertificationStatusCode(newCertStatus);
1732 --certSubscriptRowVO.setPeriodStatusCode(newCertStatus);
1733 l_new_cre_status_code := 'EXPIRED';
1734 l_new_cpe_status_code := 'EXPIRED';
1735
1736 OPEN csr_cert_enrl;
1737 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
1738 CLOSE csr_cert_enrl;
1739
1740 ota_cert_enrollment_api.update_cert_enrollment
1741 (p_effective_date => trunc(sysdate)
1742 ,p_cert_enrollment_id => p_cert_enrollment_id
1743 ,p_certification_id => l_cert_enrl_rec.certification_id
1744 ,p_object_version_number => l_cert_enrl_rec.object_version_number
1745 ,p_certification_status_code => l_new_cre_status_code);
1746
1747 if l_is_period_update then
1748 OPEN csr_prd_enrl;
1749 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
1750 CLOSE csr_prd_enrl;
1751
1752 ota_cert_prd_enrollment_api.update_cert_prd_enrollment
1753 (p_effective_date => trunc(sysdate)
1754 ,p_cert_enrollment_id => p_cert_enrollment_id
1755 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
1756 ,p_object_version_number => l_prd_enrl_rec.object_version_number
1757 ,p_period_status_code => l_new_cpe_status_code
1758 ,p_cert_period_end_date => l_cert_period_end_date);
1759 end if;
1760 elsif (p_certification_status_code is not null and p_certification_status_code = 'CERTIFIED') then
1761 --certSubscriptRowVO.setCertificationStatusCode(newCertStatus);
1762 --certSubscriptRowVO.setPeriodStatusCode("COMPLETED");
1763 --//reset the completion date
1764 --certSubscriptRowVO.setCpeCompletionDate(this.getOADBTransaction().getCurrentDBDate());
1765
1766 if(p_cert_period_completion_date is null) then
1767 l_cert_period_completion_date := trunc(sysdate);
1768 else
1769 l_cert_period_completion_date := p_cert_period_completion_date;
1770 end if;
1771 if (p_cert_period_completion_date < l_prd_enrl_rec.cert_period_start_date or trunc(p_cert_period_completion_date) > trunc(sysdate)) then
1772 p_return_status := 'OTA_467120_CPE_PRD_CMPL_DT_ERR';
1773 return;
1774 end if;
1775
1776 l_new_cre_status_code := 'CERTIFIED';
1777 l_new_cpe_status_code := 'COMPLETED';
1778 l_curr_date := trunc(sysdate);
1779
1780 OPEN csr_cert_enrl;
1781 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
1782 CLOSE csr_cert_enrl;
1783
1784 ota_cert_enrollment_api.update_cert_enrollment
1785 (p_effective_date => trunc(sysdate)
1786 ,p_cert_enrollment_id => p_cert_enrollment_id
1787 ,p_certification_id => l_cert_enrl_rec.certification_id
1788 ,p_object_version_number => l_cert_enrl_rec.object_version_number
1789 ,p_certification_status_code => l_new_cre_status_code
1790 ,p_completion_date => l_cert_period_completion_date);
1791
1792 if p_cert_upd_comp is null then
1793 l_cert_upd_comp := 'N';
1794 else
1795 l_cert_upd_comp := p_cert_upd_comp;
1796 end if;
1797
1798 if l_is_period_update then
1799 OPEN csr_prd_enrl;
1800 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
1801 CLOSE csr_prd_enrl;
1802
1803 ota_cert_prd_enrollment_api.update_cert_prd_enrollment
1804 (p_effective_date => trunc(sysdate)
1805 ,p_cert_enrollment_id => p_cert_enrollment_id
1806 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
1807 ,p_object_version_number => l_prd_enrl_rec.object_version_number
1808 ,p_period_status_code => l_new_cpe_status_code
1809 ,p_cert_period_end_date => l_cert_period_end_date
1810 ,p_completion_date => l_cert_period_completion_date);
1811 end if;
1812
1813 hr_utility.set_location(' l_cert_upd_comp is ' ||l_cert_upd_comp || l_proc,10);
1814
1815 if p_certification_status_code = 'CERTIFIED' and l_cert_upd_comp = 'Y' then
1816 hr_utility.set_location(' Updating competencies'||l_proc,10);
1817 ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
1818 p_itemtype =>'HRSSA',
1819 p_person_id => l_cert_enrl_rec.person_id,
1820 p_eventid =>null,
1821 p_learningpath_ids => null,
1822 p_certification_id => l_cert_enrl_rec.certification_id ,
1823 p_itemkey =>l_item_key);
1824 end if;
1825 elsif (p_certification_status_code is not null and p_certification_status_code = 'ENROLLED') then
1826 --//re-populate the prd status by calling ota_cpe_util.update_cpe_status
1827 --//which also sets the cre status
1828 --rePopulateCertStatuses(certPrdEnrollmentId);
1829 if l_is_period_update then
1830 update_cpe_status(p_cert_prd_enrollment_id, l_certification_status_code, null, p_cert_period_end_date);
1831 else
1832 l_new_cre_status_code := 'ENROLLED';
1833
1834 OPEN csr_cert_enrl;
1835 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
1836 CLOSE csr_cert_enrl;
1837
1838 ota_cert_enrollment_api.update_cert_enrollment
1839 (p_effective_date => trunc(sysdate)
1840 ,p_cert_enrollment_id => p_cert_enrollment_id
1841 ,p_certification_id => l_cert_enrl_rec.certification_id
1842 ,p_object_version_number => l_cert_enrl_rec.object_version_number
1843 ,p_certification_status_code => l_new_cre_status_code
1844 ,p_unenrollment_date => null);
1845 end if;
1846 elsif (p_certification_status_code is null and l_is_period_update) then
1847 --mass update scrn with just due date chg
1848 update_cpe_status(p_cert_prd_enrollment_id, l_certification_status_code, null, p_cert_period_end_date);
1849 end if;
1850
1851 l_return_status := 'S';
1852
1853 p_return_status := l_return_status;
1854
1855 hr_utility.set_location('Leaving :'||l_proc,20);
1856
1857 EXCEPTION
1858 WHEN others THEN
1859 hr_utility.set_location('Leaving :'||l_proc,30);
1860 p_return_status := null;
1861 End update_admin_changes;
1862 --
1863 --
1864 -- Bug 4701515
1865 Procedure update_cert_status_to_expired(
1866 ERRBUF OUT NOCOPY VARCHAR2,
1867 RETCODE OUT NOCOPY VARCHAR2) is
1868
1869 l_proc varchar2(72) := g_package || ' update_cert_status_to_expired';
1870
1871 --get all enrollment records in last period for expiry status updation
1872 cursor get_data_for_expiry_status_upd is
1873 Select ceb.certification_id
1874 ,ceb.name
1875 ,enr.cert_enrollment_id
1876 ,enr.person_id
1877 ,enr.contact_id
1878 ,prd.cert_prd_enrollment_id
1879 ,prd.period_status_code
1880 ,prd.cert_period_end_date
1881 ,prd.object_version_number
1882 From ota_certifications_vl ceb
1883 ,ota_cert_enrollments enr
1884 ,ota_cert_prd_enrollments prd
1885 Where
1886 ceb.certification_id = enr.certification_id
1887 and enr.cert_enrollment_id = prd.cert_enrollment_id
1888 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1889 nvl(trunc(ceb.end_date_active), trunc(sysdate))
1890 and prd.period_status_code in ('ACTIVE', 'ENROLLED', 'INCOMPLETE');
1891
1892 cursor get_data_for_expired_records(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
1893 Select ceb.certification_id
1894 ,ceb.name
1895 ,enr.cert_enrollment_id
1896 ,enr.person_id
1897 ,enr.contact_id
1898 ,prd.cert_prd_enrollment_id
1899 ,enr.certification_status_code
1900 ,enr.expiration_date
1901 ,prd.period_status_code
1902 ,prd.cert_period_end_date
1903 ,ceb.initial_completion_date
1904 ,enr.object_version_number
1905 From ota_certifications_vl ceb
1906 ,ota_cert_enrollments enr
1907 ,ota_cert_prd_enrollments prd
1908 Where
1909 ceb.certification_id = enr.certification_id
1910 and enr.cert_enrollment_id = prd.cert_enrollment_id
1911 and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate))
1912 and nvl(trunc(ceb.end_date_active), trunc(sysdate))
1913 and enr.certification_status_code in ('ENROLLED', 'CERTIFIED', 'RENEWING') -- Bug#7303995
1914 --check if this is max/latest prd then update CRE accordingly
1915 and prd.cert_prd_enrollment_id = (select max(cpex.cert_prd_enrollment_id)
1916 from ota_cert_prd_enrollments cpex
1917 where cpex.cert_enrollment_id = enr.cert_enrollment_id
1918 and trunc(cpex.cert_period_end_date) < trunc(sysdate)
1919 )
1920 and prd.cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1921
1922 l_log_message varchar2(500);
1923
1924 begin
1925 hr_utility.set_location('Entering:'||l_proc, 5);
1926
1927 for exp_sts_upd in get_data_for_expiry_status_upd
1928 Loop
1929 if ( trunc(exp_sts_upd.cert_period_end_date) < trunc(sysdate) ) then
1930 begin
1931 hr_utility.set_location('Entering:'||l_proc, 10);
1932 savepoint update_status_to_expired;
1933
1934 --Update Period CPE status to Expired
1935 ota_cert_prd_enrollment_api.update_cert_prd_enrollment(
1936 p_effective_date => trunc(sysdate),
1937 p_cert_prd_enrollment_id => exp_sts_upd.cert_prd_enrollment_id,
1938 p_object_version_number => exp_sts_upd.object_version_number,
1939 p_cert_enrollment_id => exp_sts_upd.cert_enrollment_id,
1940 p_period_status_code => 'EXPIRED');
1941
1942 l_log_message := 'Updating period status code to Expired for Certification ' ||
1943 exp_sts_upd.name || '(' || exp_sts_upd.certification_id || ')' ||
1944 ', subscription id ' || exp_sts_upd.cert_enrollment_id ||
1945 ', period subscription id ' || exp_sts_upd.cert_prd_enrollment_id ||
1946 ', period end date ' || exp_sts_upd.cert_period_end_date;
1947 if (exp_sts_upd.person_id is not null) then
1948 l_log_message := l_log_message || ' for person id ' || exp_sts_upd.person_id;
1949 else
1950 l_log_message := l_log_message || ' for contact id ' || exp_sts_upd.contact_id;
1951 end if;
1952
1953 FND_FILE.PUT_LINE(FND_FILE.LOG, l_log_message);
1954
1955 --Update Cert CRE status to Expired
1956 for exp_records in get_data_for_expired_records(exp_sts_upd.cert_prd_enrollment_id)
1957 Loop
1958 --if condition to consider bug 4642943
1959 if ( ( exp_records.expiration_date is not null and
1960 trunc(sysdate) > trunc(exp_records.expiration_date) )
1961 or exp_records.initial_completion_date is not null) then
1962
1963 --Update CRE status to Expired
1964 ota_cert_enrollment_api.update_cert_enrollment(
1965 p_effective_date => trunc(sysdate),
1966 p_cert_enrollment_id => exp_records.cert_enrollment_id,
1967 p_certification_id => exp_records.certification_id,
1968 p_object_version_number => exp_records.object_version_number,
1969 p_certification_status_code => 'EXPIRED');
1970
1971 l_log_message := 'Updating subscription status code to Expired for Certification ' ||
1972 exp_records.name || '(' || exp_records.certification_id || ')' ||
1973 ', subscription id ' || exp_records.cert_enrollment_id;
1974
1975 if (exp_records.person_id is not null) then
1976 l_log_message := l_log_message || ' for person id ' || exp_records.person_id;
1977 else
1978 l_log_message := l_log_message || ' for contact id ' || exp_records.contact_id;
1979 end if;
1980 FND_FILE.PUT_LINE(FND_FILE.LOG, l_log_message);
1981 end if;
1982 End Loop;
1983 EXCEPTION
1984 WHEN OTHERS then
1985 FND_FILE.PUT_LINE(FND_FILE.LOG, 'When Others Error occured in, '
1986 || 'Update to cpe cre calls ,' || 'Cert_Prd_Enrollment_Id=' || to_char(exp_sts_upd.cert_prd_enrollment_id)
1987 ||',' || 'Cert_Enrollment_Id=' || to_char(exp_sts_upd.cert_enrollment_id)
1988 || ',' || SUBSTR(SQLERRM, 1, 500));
1989
1990 ROLLBACK TO update_status_to_expired;
1991 end;
1992 end if;
1993 End Loop;
1994
1995 commit;
1996 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Data committed to database');
1997 hr_utility.set_location('Leaving:'||l_proc, 5);
1998 EXCEPTION
1999 when others then
2000 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
2001 ||','||SUBSTR(SQLERRM, 1, 500));
2002 end update_cert_status_to_expired;
2003 --
2004 Procedure sync_cert_status_to_class_enrl(
2005 ERRBUF OUT NOCOPY VARCHAR2,
2006 RETCODE OUT NOCOPY VARCHAR2) is
2007
2008 l_proc varchar2(72) := g_package || ' sync_cert_status_to_class_enrl';
2009
2010 cursor csr_cpe_status is
2011 select cpe.cert_prd_enrollment_id,
2012 cpe.period_status_code,
2013 cpe.cert_period_start_date
2014 from ota_cert_prd_enrollments cpe,
2015 ota_cert_enrollments cre
2016 where cpe.period_status_code not in ('COMPLETED', 'CANCELLED') and
2017 cpe.cert_enrollment_id = cre.cert_enrollment_id --bug#6338987
2018 order by cre.certification_id;
2019
2020 cursor csr_acty_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type)
2021
2022 is
2023 select cme.cert_mbr_enrollment_id,
2024 cmb.object_id
2025 from ota_cert_mbr_enrollments cme,
2026 ota_certification_members cmb
2027 where cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
2028 and cme.cert_member_id = cmb.certification_member_id
2029 and object_type = 'H';
2030
2031 l_booking_status_type ota_booking_status_types.type%TYPE;
2032 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
2033 do_update boolean := false;
2034 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
2035
2036 begin
2037
2038 hr_utility.set_location('Entering:'||l_proc, 5);
2039
2040 sync_late_subsc_to_class;
2041
2042 for rec_cpe_status in csr_cpe_status
2043 loop
2044 hr_utility.set_location('Step:'||l_proc, 10);
2045 for rec_acty_info in csr_acty_info(rec_cpe_status.cert_prd_enrollment_id)
2046 loop
2047 hr_utility.set_location('Step:'||l_proc, 10.1);
2048 -- get latest class enrollment status
2049 ota_cme_util.get_enrl_status_on_update(rec_acty_info.object_id,
2050 rec_cpe_status.cert_prd_enrollment_id,
2051 l_booking_status_type,
2052 l_date_status_changed);
2053
2054 -- if l_booking_status_type is 'A' then bypass update for EXPIRED periods for COMPLETED child status
2055 if l_booking_status_type = 'A' then
2056 if rec_cpe_status.period_status_code <> 'EXPIRED' then
2057 if trunc(l_date_status_changed) = trunc(rec_cpe_status.cert_period_start_date) then
2058 --scenarios 1 and 2
2059 -- for 'Attended' class enroll status perform status rollup for CURRENT CPE
2060 hr_utility.set_location('Step:'||l_proc, 10.2);
2061 do_update := true;
2062 end if;
2063 end if;
2064 else
2065 --scenarios 3 and 4
2066 -- for any class enroll status changes this would update child of EXPIRED CPE and status rollup for CURRENT CPE
2067 hr_utility.set_location('Step:'||l_proc, 10.3);
2068 do_update := true;
2069 end if;
2070 if do_update then
2071 ota_cme_util.update_cme_status(rec_acty_info.cert_mbr_enrollment_id);
2072 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updated the Certification member status for the cert_mbr_enrollment_id = ' || rec_acty_info.cert_mbr_enrollment_id);
2073 ota_cme_util.update_cpe_status(rec_acty_info.cert_mbr_enrollment_id, l_cert_prd_enrollment_id, trunc(l_date_status_changed));
2074 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updated the Certification period status for the cert_prd_enrollment_id = ' || l_cert_prd_enrollment_id);
2075 end if;
2076 do_update := false;
2077 end loop;
2078 end loop;
2079
2080 hr_utility.set_location('Entering:'||l_proc, 20);
2081
2082 commit;
2083
2084 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Data committed to database');
2085
2086 hr_utility.set_location('Leaving:'||l_proc, 30);
2087
2088 EXCEPTION
2089 when others then
2090 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
2091 ||','||SUBSTR(SQLERRM, 1, 500));
2092 end sync_cert_status_to_class_enrl;
2093 --
2094 FUNCTION get_latest_cpe_col(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%TYPE,
2095 p_col_name in varchar2 default 'Period_Status_Meaning')
2096 RETURN varchar2 is
2097
2098 Cursor csr_latest_cpe is
2099 Select cpe.period_status_code Period_Status_Code,
2100 ota_utility.get_lookup_meaning('OTA_CERT_PRD_ENROLL_STATUS', cpe.period_status_code, 810) Period_Status_Meaning,
2101 cpe.cert_prd_enrollment_id
2102 From ota_cert_prd_enrollments cpe
2103 where cpe.cert_enrollment_id = p_cert_enrollment_id
2104 -- and rownum = 1 --Bug#6356854
2105 order by cpe.cert_prd_enrollment_id desc;
2106
2107 l_proc VARCHAR2(72) := g_package||' get_latest_cpe_col';
2108 rec_latest_cpe csr_latest_cpe%rowtype;
2109
2110 Begin
2111 hr_utility.set_location('Entering:'||l_proc, 5);
2112
2113 open csr_latest_cpe;
2114 fetch csr_latest_cpe into rec_latest_cpe;
2115 close csr_latest_cpe;
2116
2117 hr_utility.set_location('Leaving:'||l_proc, 10);
2118
2119 if upper(p_col_name) = upper('Cert_Prd_Enrollment_Id') then
2120 return rec_latest_cpe.Cert_Prd_Enrollment_id;
2121 elsif upper(p_col_name) = upper('Period_Status_Code') then
2122 return rec_latest_cpe.Period_Status_Code;
2123 else
2124 return rec_latest_cpe.Period_Status_Meaning;
2125 end if;
2126
2127 EXCEPTION
2128 WHEN others THEN
2129 hr_utility.set_location('Leaving :'||l_proc,15);
2130 RETURN null;
2131
2132 End get_latest_cpe_col;
2133
2134 function get_elapsed_due_date(p_certification_id ota_certifications_b.certification_id%type) return date is
2135 CURSOR csr_crt IS
2136 select
2137 b.certification_id certification_id
2138 , b.INITIAL_COMPLETION_DATE
2139 , b.VALIDITY_DURATION
2140 , b.VALIDITY_DURATION_UNITS
2141 , b.renewable_flag
2142 from ota_certifications_b b
2143 where b.certification_id = p_certification_id;
2144
2145
2146 l_proc VARCHAR2(72) := g_package||' get_elapsed_due_date';
2147 rec_crt csr_crt%rowtype;
2148
2149 l_elapsed_due_date date;
2150 l_curr_dt date;
2151
2152 BEGIN
2153
2154 hr_utility.set_location('Entering :'||l_proc,5);
2155
2156 l_curr_dt := trunc(sysdate);
2157
2158 OPEN csr_crt;
2159 FETCH csr_crt INTO rec_crt;
2160 CLOSE csr_crt;
2161
2162 if (rec_crt.RENEWABLE_FLAG is null or rec_crt.RENEWABLE_FLAG = 'N') then
2163 return null;
2164 end if;
2165
2166 if trunc(rec_crt.initial_completion_date) < l_curr_dt then
2167 --elapsed due date for late subscr
2168 l_elapsed_due_date := trunc(rec_crt.initial_completion_date);
2169 LOOP
2170 l_elapsed_due_date := l_elapsed_due_date + rec_crt.validity_duration;
2171 EXIT WHEN l_curr_dt < l_elapsed_due_date;
2172 end loop;
2173 end if;
2174
2175 hr_utility.set_location('Leaving :'||l_proc,10);
2176
2177 return l_elapsed_due_date;
2178
2179 EXCEPTION
2180 WHEN others THEN
2181 hr_utility.set_location('Leaving :'||l_proc,15);
2182 RETURN NULL;
2183 end get_elapsed_due_date;
2184
2185 function check_active_periods(p_event_id ota_events.event_id%type) return varchar2
2186 is
2187 cursor csr_onl_sync_flag is
2188 select csu.online_flag,
2189 csu.synchronous_flag,
2190 ofr.offering_id
2191 from ota_category_usages csu,
2192 ota_offerings ofr,
2193 ota_events evt
2194 where evt.event_id = p_event_id
2195 and evt.parent_offering_id = ofr.offering_id
2196 and ofr.delivery_mode_id = csu.category_usage_id;
2197
2198 cursor csr_act_id(csr_offering_id number) is
2199 select act.activity_version_id
2200 from ota_activity_versions act,
2201 ota_offerings ofr
2202 where ofr.offering_id = csr_offering_id
2203 and ofr.activity_version_id = act.activity_version_id;
2204
2205 CURSOR csr_chk_prds(l_activity_version_id number) IS
2206 SELECT null
2207 FROM ota_certification_members cmb,
2208 ota_cert_mbr_enrollments cme,
2209 ota_cert_prd_enrollments cpe,
2210 ota_cert_enrollments cre
2211 WHERE
2212 cre.cert_enrollment_id = cpe.cert_enrollment_id
2213 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
2214 AND cme.cert_member_id = cmb.certification_member_id
2215 AND cmb.object_id = l_activity_version_id
2216 --pull only curr periods
2217 AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
2218 -- don't consider expired or canc prds
2219 AND cpe.period_status_code not in ('COMPLETED','EXPIRED','CANCELLED');
2220
2221
2222 l_proc varchar2(72) := g_package||'check_active_periods' ;
2223
2224 rec_onl_sync_flag csr_onl_sync_flag%rowtype;
2225
2226 is_evt_onl_async boolean := false;
2227 l_activity_version_id ota_activity_versions.activity_version_id%type;
2228
2229 l_return_value varchar2(1) := 'N';
2230
2231 l_dummy varchar2(10);
2232
2233 Begin
2234
2235 hr_utility.set_location('Entering:'||l_proc, 10);
2236
2237 open csr_onl_sync_flag;
2238 fetch csr_onl_sync_flag into rec_onl_sync_flag;
2239 close csr_onl_sync_flag;
2240
2241 --is event online
2242 if rec_onl_sync_flag.online_flag = 'Y' and rec_onl_sync_flag.synchronous_flag = 'N' then
2243 is_evt_onl_async := true;
2244 end if;
2245
2246 if is_evt_onl_async then
2247 -- check dependencies on current period components.
2248 open csr_act_id(rec_onl_sync_flag.offering_id);
2249 fetch csr_act_id into l_activity_version_id;
2250 close csr_act_id;
2251
2252 --for act check if any currently active periods exists with this course
2253 open csr_chk_prds(l_activity_version_id);
2254 fetch csr_chk_prds into l_dummy;
2255 if csr_chk_prds%found then
2256 l_return_value := 'Y';
2257 end if;
2258 close csr_chk_prds;
2259
2260 end if;
2261
2262 hr_utility.set_location('Leaving :'||l_proc, 30);
2263
2264 return l_return_value;
2265
2266 EXCEPTION
2267 WHEN others THEN
2268 hr_utility.set_location('Leaving :'||l_proc,40);
2269 return l_return_value;
2270 END check_active_periods;
2271
2272 /* Late Subscription or late renewal to certifications
2273 How this procedure works
2274 ------------------------
2275 1)Fetch all certifications which are
2276 a)renewable
2277 b)Active(sysdate between certification start and end date)
2278 2)For each certification fetched in step 1, get all the course
2279 component details which are in PLANNED status(This is
2280 because the certification period start and end date will
2281 not be matched with previously completed class)
2282 3)Iterate through all the courses fetched in step 2 and
2283 get all the classes in the course and
2284 get the class enrollment details for each class
2285 It will return result, if it staisfy the conditions
2286 a)If the class is scheduled class(online synchronous or
2287 offline synchronous),
2288 i) the latest certification (period start date - certification
2289 initial completion duration) should be less than
2290 or equal to class start date
2291 ii)the latest certification (period end date - certification
2292 initial completion duration) should be greater than
2293 or equal to class end date
2294 b)If the class is self paced class(online asynchronous or
2295 offline asynchronous),
2296 i) the class end date is null
2297 or
2298 the latest certification (period start date - certification
2299 initial completion duration) should be less than
2300 or equal to class end date
2301 ii)the latest certification (period end date - certification
2302 initial completion duration) should be greater than
2303 or equal to class start date
2304
2305 4)If the step 3 returns any result, it further should satisfy the conditions
2306 a)Learner should be completed the class(booking_status_type = 'A') and
2307 b)(class completion date + certification initial completion duration)
2308 should be greater than or equal to certification period start date and
2309 c)class completion date should be less than certification period end date and
2310 d)class completion date should be between certification start and end date
2311 5)If all the above condtions satisfied,
2312 take class completion date as new certification period start date
2313 and calculate certification period end date, expiry date, renewal date
2314 based on the certification period start date
2315 6)update the new calculated dates
2316
2317 NOTE:This procedure will correct late subscription or late renewal
2318 only for internal employees. Contacts will not be cosidered
2319 */
2320 procedure sync_late_subsc_to_class IS
2321
2322 l_proc varchar2(72) := g_package || ' sync_late_subsc_to_class';
2323
2324 CURSOR csr_cert_details IS
2325 SELECT ocb.certification_id,
2326 ocb.name,
2327 ocb.start_date_active,
2328 nvl(ocb.end_date_active, to_date('4712/12/31','YYYY/MM/DD')) end_date_active,
2329 oce.enrollment_date,
2330 nvl(ocb.initial_completion_duration, (ocb.initial_completion_date - oce.enrollment_date)) initial_completion_duration,
2331 ocb.renewable_flag,
2332 ocb.validity_duration,
2333 oce.cert_enrollment_id,
2334 oce.person_id,
2335 oce.contact_id,
2336 ocpe.cert_prd_enrollment_id,
2337 ocpe.cert_period_start_date,
2338 ocpe.cert_period_end_date,
2339 ocpe.expiration_date
2340 FROM ota_certifications_vl ocb, ota_cert_enrollments oce, ota_cert_prd_enrollments ocpe
2341 WHERE ocb.certification_id = oce.certification_id
2342 AND oce.cert_enrollment_id = ocpe.cert_enrollment_id
2343 AND trunc(SYSDATE) BETWEEN trunc(ocb.start_date_active) AND trunc(nvl(ocb.end_date_active, SYSDATE))
2344 AND ocb.renewable_flag = 'Y'
2345 AND oce.person_id is not null
2346 AND oce.certification_status_code <> 'CERTIFIED'
2347 AND ocpe.period_status_code not in ('COMPLETED', 'CANCELLED')
2348 AND ocpe.cert_prd_enrollment_id = (SELECT MAX(cert_prd_enrollment_id) FROM ota_cert_prd_enrollments ocpe1
2349 WHERE oce.cert_enrollment_id = ocpe1.cert_enrollment_id);
2350
2351 CURSOR csr_course_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
2352 SELECT cme.cert_mbr_enrollment_id,
2353 cmb.object_id
2354 FROM ota_cert_mbr_enrollments cme,
2355 ota_certification_members cmb
2356 WHERE cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
2357 AND cme.cert_member_id = cmb.certification_member_id
2358 AND object_type = 'H'
2359 AND member_status_code = 'PLANNED';
2360
2361 CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
2362 csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
2363 csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
2364 csr_person_id in ota_cert_enrollments.person_id%TYPE,
2365 csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
2366 SELECT DECODE(bst.type,'C','Z',bst.type) status,
2367 evt.event_type,
2368 tdb.DATE_STATUS_CHANGED,
2369 evt.COURSE_START_DATE,
2370 evt.COURSE_END_DATE
2371 FROM ota_events evt,
2372 ota_delegate_bookings tdb,
2373 ota_booking_status_types bst
2374 WHERE evt.event_id = tdb.event_id
2375 AND bst.booking_status_type_id = tdb.booking_status_type_id
2376 AND (
2377 --sync sched, online(conf) or offline(ILT)
2378 --sync always have an end date
2379 ( evt.event_type = 'SCHEDULED' and
2380 evt.course_start_date >= csr_cert_period_start_date and
2381 evt.course_end_date <= csr_cert_period_end_date )
2382 or
2383 --async selfpaced, online(selfp) or offline(CBT)
2384 --async have opt end date
2385 (event_type ='SELFPACED' and
2386 (csr_cert_period_end_date >= evt.course_start_date) AND
2387 ((evt.course_end_date is null) or
2388 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))))
2389 AND evt.activity_version_id = csr_activity_version_id
2390 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
2391 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
2392 )
2393 order by status;
2394
2395 CURSOR csr_cert_enrl(p_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type) IS
2396 SELECT certification_id,
2397 cert_enrollment_id,
2398 business_group_id,
2399 certification_status_code,
2400 object_version_number,
2401 completion_date,
2402 earliest_enroll_date
2403 FROM ota_cert_enrollments
2404 WHERE cert_enrollment_id = p_cert_enrollment_id;
2405
2406 CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
2407 select cert_enrollment_id, cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
2408 FROM ota_cert_prd_enrollments
2409 where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
2410
2411 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
2412 l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
2413 l_booking_status_type ota_booking_status_types.type%TYPE;
2414 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
2415
2416 l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;
2417 l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2418 l_cert_completion_date ota_cert_enrollments.completion_date%type;
2419 l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2420 l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
2421 l_expiration_date ota_cert_prd_enrollments.expiration_date%type;
2422
2423 begin
2424
2425 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sync Late Subscription to certification with classes');
2426
2427 FOR rec_cert_details in csr_cert_details loop
2428 FOR rec_course_info in csr_course_info(rec_cert_details.cert_prd_enrollment_id) loop
2429 FOR rec_enr IN get_enrl_status(rec_course_info.object_id,
2430 (rec_cert_details.cert_period_start_date - rec_cert_details.initial_completion_duration),
2431 (rec_cert_details.cert_period_end_date - rec_cert_details.initial_completion_duration),
2432 rec_cert_details.person_id,
2433 rec_cert_details.contact_id)LOOP
2434 l_booking_status_type := rec_enr.status ;
2435 l_date_status_changed := rec_enr.date_status_changed;
2436
2437 IF(l_booking_status_type = 'A' AND
2438 (trunc(l_date_status_changed) + rec_cert_details.initial_completion_duration) >= rec_cert_details.cert_period_start_date AND
2439 trunc(l_date_status_changed) < rec_cert_details.cert_period_end_date AND
2440 trunc(l_date_status_changed) BETWEEN rec_cert_details.start_date_active AND rec_cert_details.end_date_active) THEN
2441
2442 --calculate dates
2443 l_cert_period_start_date := trunc(l_date_status_changed);
2444 l_cert_period_end_date := l_cert_period_start_date + rec_cert_details.initial_completion_duration;
2445 l_cert_completion_date := trunc(l_date_status_changed);
2446 l_cert_prd_completion_date := trunc(l_date_status_changed);
2447
2448 ota_cpe_util.calc_cre_dates(rec_cert_details.cert_enrollment_id,
2449 rec_cert_details.certification_id,
2450 null,
2451 l_earliest_enroll_date,
2452 l_expiration_date,
2453 l_cert_period_start_date);
2454
2455 --UPDATE cert enrollment dates
2456 OPEN csr_cert_enrl(rec_cert_details.cert_enrollment_id);
2457 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
2458 CLOSE csr_cert_enrl;
2459
2460 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Certification: ' || rec_cert_details.name);
2461 FND_FILE.PUT_LINE(FND_FILE.LOG, '--------------');
2462
2463 FND_FILE.PUT_LINE(FND_FILE.LOG, 'cert_prd_enrollment_id = ' || rec_cert_details.cert_prd_enrollment_id);
2464 FND_FILE.PUT_LINE(FND_FILE.LOG, 'person_id = ' || rec_cert_details.person_id);
2465
2466 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Old Period Start Date = ' || rec_cert_details.cert_period_start_date);
2467 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Old Period End Date = ' || rec_cert_details.cert_period_end_date);
2468 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Old Compeletion Date = ' || l_cert_enrl_rec.completion_date);
2469 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Old Earliest Enroll Date = ' || l_cert_enrl_rec.earliest_enroll_date);
2470 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Old Expiration Date = ' || rec_cert_details.expiration_date);
2471
2472 FND_FILE.PUT_LINE(FND_FILE.LOG, 'New Period Start Date = ' || l_cert_period_start_date);
2473 FND_FILE.PUT_LINE(FND_FILE.LOG, 'New Period End Date = ' || l_cert_period_end_date);
2474 FND_FILE.PUT_LINE(FND_FILE.LOG, 'New Compeletion Date = ' || l_cert_completion_date);
2475 FND_FILE.PUT_LINE(FND_FILE.LOG, 'New Earliest Enroll Date = ' || l_earliest_enroll_date);
2476 FND_FILE.PUT_LINE(FND_FILE.LOG, 'New Expiration Date = ' || l_expiration_date);
2477 FND_FILE.PUT_LINE(FND_FILE.LOG, '--------------------------------------------------------');
2478
2479 ota_cert_enrollment_api.update_cert_enrollment
2480 (p_effective_date => sysdate
2481 ,p_cert_enrollment_id => l_cert_enrl_rec.cert_enrollment_id
2482 ,p_certification_id => l_cert_enrl_rec.certification_id
2483 ,p_object_version_number => l_cert_enrl_rec.object_version_number
2484 ,p_certification_status_code => l_cert_enrl_rec.certification_status_code
2485 ,p_is_history_flag => 'N'
2486 ,p_earliest_enroll_date => l_earliest_enroll_date
2487 ,p_completion_date => l_cert_completion_date
2488 );
2489
2490 --UPDATE cert period enrollment dates
2491 OPEN csr_prd_enrl(rec_cert_details.cert_prd_enrollment_id);
2492 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
2493 CLOSE csr_prd_enrl;
2494
2495 ota_cert_prd_enrollment_api.update_cert_prd_enrollment
2496 (p_effective_date => trunc(sysdate)
2497 ,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
2498 ,p_cert_prd_enrollment_id => l_prd_enrl_rec.cert_prd_enrollment_id
2499 ,p_object_version_number => l_prd_enrl_rec.object_version_number
2500 ,p_period_status_code => l_prd_enrl_rec.period_status_code
2501 ,p_completion_date => l_cert_prd_completion_date
2502 ,p_cert_period_start_date => l_cert_period_start_date
2503 ,p_cert_period_end_date => l_cert_period_end_date
2504 ,p_expiration_date => l_expiration_date
2505 );
2506
2507 COMMIT;
2508 EXIT;
2509 END IF;
2510 END LOOP;
2511 END LOOP;
2512 END LOOP;
2513
2514 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sync Late Subscription to certification with classes is completed.');
2515
2516 EXCEPTION when others then
2517 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc ||','||SUBSTR(SQLERRM, 1, 500));
2518 end sync_late_subsc_to_class;
2519 --
2520
2521 END OTA_CPE_UTIL;
2522