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