[Home] [Help]
PACKAGE BODY: APPS.OTA_CME_UTIL
Source
1 PACKAGE BODY OTA_CME_UTIL as
2 /* $Header: otcmewrs.pkb 120.14.12010000.2 2008/11/07 09:35:18 pekasi ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' OTA_CME_UTIL.'; -- Global package name
9 --
10 CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
11 csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
12 csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
13 csr_person_id in ota_cert_enrollments.person_id%TYPE,
14 csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
15 SELECT DECODE(bst.type,'C','Z',bst.type) status,
16 evt.event_type,
17 tdb.DATE_STATUS_CHANGED,
18 evt.COURSE_START_DATE,
19 evt.COURSE_END_DATE
20 FROM ota_events evt,
21 ota_delegate_bookings tdb,
22 ota_booking_status_types bst
23 WHERE evt.event_id = tdb.event_id
24 AND bst.booking_status_type_id = tdb.booking_status_type_id
25 and (
26 --sync sched, online(conf) or offline(ILT)
27 --sync always have an end date
28 ( evt.event_type = 'SCHEDULED' and
29 evt.course_start_date >= csr_cert_period_start_date and
30 evt.course_end_date <= csr_cert_period_end_date )
31 or
32 --async selfpaced, online(selfp) or offline(CBT)
33 --async have opt end date
34 (event_type ='SELFPACED' and
35 (csr_cert_period_end_date >= evt.course_start_date) AND
36 ((evt.course_end_date is null) or
37 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))))
38 AND evt.activity_version_id = csr_activity_version_id
39 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
40 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
41 )
42 order by status;
43 --
44
45 PROCEDURE get_enrl_status_on_update(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
46 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
47 p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
48 p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE)
49 IS
50
51 CURSOR csr_cert_enrl IS
52 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
53 FROM ota_cert_enrollments cre,
54 ota_cert_prd_enrollments cpe
55 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
56 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
57
58 l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';
59
60 l_person_id ota_cert_enrollments.person_id%TYPE;
61 l_contact_id ota_cert_enrollments.contact_id%TYPE;
62 l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
63 l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
64
65 l_enroll_status VARCHAR2(30);
66 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
67
68 BEGIN
69
70 hr_utility.set_location(' Step:'|| l_proc, 10);
71
72 OPEN csr_cert_enrl;
73 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
74 CLOSE csr_cert_enrl;
75
76 FOR rec_enr IN get_enrl_status(p_activity_version_id,
77 l_cert_period_start_date,
78 l_cert_period_end_date,
79 l_person_id,
80 l_contact_id)
81 LOOP
82 l_enroll_status := rec_enr.status ;
83 l_date_status_changed := rec_enr.date_status_changed;
84 EXIT;
85 END LOOP;
86
87
88 p_booking_status_type := l_enroll_status;
89 p_date_status_changed := l_date_status_changed;
90
91 hr_utility.set_location(' Step:'|| l_proc, 20);
92 --
93 EXCEPTION
94 WHEN others THEN
95 hr_utility.set_location('Leaving :'||l_proc,30);
96 p_booking_status_type := null;
97 p_date_status_changed := null;
98
99 END get_enrl_status_on_update;
100
101 -- ---------------------------------------------------------------------------
102 -- |----------------------< calculate_cme_status >-----------------------------|
103 -- ---------------------------------------------------------------------------
104 PROCEDURE calculate_cme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
105 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
106 p_mode in varchar2,
107 p_member_status_code OUT nocopy VARCHAR2,
108 p_completion_date OUT nocopy DATE)
109 IS
110
111 CURSOR csr_cert_enrl IS
112 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
113 FROM ota_cert_enrollments cre,
114 ota_cert_prd_enrollments cpe
115 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
116 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
117
118 l_proc VARCHAR2(72) := g_package|| 'calculate_cme_status';
119
120 l_person_id ota_cert_enrollments.person_id%TYPE;
121 l_contact_id ota_cert_enrollments.contact_id%TYPE;
122 l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
123 l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
124
125 l_enroll_status VARCHAR2(30);
126 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
127 l_event_type ota_events.event_type%type;
128 l_course_start_date ota_events.course_start_date%type;
129 l_course_end_date ota_events.course_end_date%type;
130
131 BEGIN
132
133 hr_utility.set_location('Entering:'|| l_proc, 10);
134
135 OPEN csr_cert_enrl;
136 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
137 CLOSE csr_cert_enrl;
138
139 hr_utility.set_location('Entering:'|| l_proc, 20);
140
141 FOR rec_enr IN get_enrl_status(p_activity_version_id,
142 l_cert_period_start_date,
143 l_cert_period_end_date,
144 l_person_id,
145 l_contact_id)
146 LOOP
147 l_enroll_status := rec_enr.status ;
148 l_date_status_changed := rec_enr.date_status_changed;
149 l_event_type := rec_enr.event_type;
150 l_course_start_date := rec_enr.course_start_date;
151 l_course_end_date := rec_enr.course_end_date;
152 EXIT;
153 END LOOP;
154
155 hr_utility.set_location('Entering:'|| l_proc, 30);
156
157 if p_mode = 'C' then
158
159 --don't consider past attended enrls during cert mbr enrl create
160 -- enable selfpaced event enrl compl in the past as Active for next cert prd comps
161 if l_enroll_status = 'A' and
162 -- Bug 4515924 --rec_enr.event_type = 'SELFPACED' then
163 (l_event_type = 'SELFPACED' AND
164 ((l_cert_period_end_date >= l_course_start_date) AND
165 ((l_course_end_date is null) or
166 (l_course_end_date IS NOT NULL AND l_course_end_date >= l_cert_period_start_date))
167 )) THEN
168 p_member_status_code := 'ACTIVE';-- intreprit cme status as Active
169 p_completion_date := null;
170 ELSIF ( l_enroll_status='P'
171 OR l_enroll_status='W'
172 OR l_enroll_status ='R') THEN
173 p_member_status_code := 'ACTIVE';
174 p_completion_date := null;
175 ELSE
176 p_member_status_code := 'PLANNED';
177 p_completion_date := null;
178 END IF;
179
180 elsif p_mode = 'U' then
181 --consider attended enrls only during the cert prd start and end dates.
182 IF ( l_enroll_status='A' ) THEN
183 p_member_status_code := 'COMPLETED';
184 p_completion_date := l_date_status_changed;
185 ELSIF ( l_enroll_status='P'
186 OR l_enroll_status='W'
187 OR l_enroll_status ='R') THEN
188 p_member_status_code := 'ACTIVE';
189 p_completion_date := null;
190 ELSE
191 p_member_status_code := 'PLANNED';
192 p_completion_date := null;
193 END IF;
194 end if;
195
196 hr_utility.set_location('Step:'|| l_proc, 40);
197
198 EXCEPTION
199 WHEN others THEN
200 hr_utility.set_location('LEAVING:'|| l_proc, 50);
201 p_member_status_code := 'PLANNED';
202 p_completion_date := null;
203 RAISE;
204
205 END calculate_cme_status;
206
207
208 -- ---------------------------------------------------------------------------
209 -- |----------------------< update_cme_status >-------------------|
210 -- ---------------------------------------------------------------------------
211 PROCEDURE update_cme_status (p_event_id IN ota_events.event_id%TYPE,
212 p_person_id IN ota_cert_enrollments.person_id%TYPE,
213 p_contact_id IN ota_cert_enrollments.contact_id%TYPE,
214 p_cert_prd_enrollment_ids OUT NOCOPY varchar2)
215 IS
216
217 l_proc VARCHAR2(72) := g_package|| 'update_cme_status';
218
219 CURSOR evt_det IS
220 SELECT evt.activity_version_id,
221 ocu.online_flag
222 FROM ota_events evt,
223 ota_offerings ofr,
224 ota_category_usages ocu
225 WHERE evt.event_id = p_event_id
226 AND evt.parent_offering_id = ofr.offering_id
227 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
228
229
230 CURSOR csr_cme_info(csr_activity_version_id number) IS
231 SELECT cme.cert_mbr_enrollment_id,
232 cpe.cert_prd_enrollment_id,
233 cme.object_version_number,
234 cmb.certification_member_id,
235 cme.member_status_code
236 FROM ota_certification_members cmb,
237 ota_cert_mbr_enrollments cme,
238 ota_cert_prd_enrollments cpe,
239 ota_cert_enrollments cre
240 WHERE
241 cre.cert_enrollment_id = cpe.cert_enrollment_id
242 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
243 AND cme.cert_member_id = cmb.certification_member_id
244 AND cmb.object_id = csr_activity_version_id
245 AND (( p_person_id IS NOT NULL AND cre.person_id = p_person_id)
246 OR (p_contact_id IS NOT NULL AND cre.contact_id = p_contact_id))
247 AND cme.member_status_code <> 'CANCELLED'
248 --pull only curr periods
249 AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
250 -- don't consider expired prds
251 AND cpe.period_status_code <> 'EXPIRED';
252
253 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
254 l_online_flag ota_category_usages.online_flag%type;
255 l_enroll_type ota_booking_status_types.type%TYPE;
256 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
257 l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
258 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
259
260 --variables to store old values
261 l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
262
263 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
264 l_cert_prd_enrollment_ids varchar2(4000) := '';
265
266 BEGIN
267
268 hr_utility.set_location(' Step:'||l_proc,10);
269
270 OPEN evt_det;
271 FETCH evt_det
272 INTO l_activity_version_id, l_online_flag;
273 CLOSE evt_det;
274
275 hr_utility.set_location(' Step:'|| l_proc, 20);
276
277
278 FOR rec_cme_info IN csr_cme_info(l_activity_version_id)
279
280 LOOP
281
282 get_enrl_status_on_update(p_activity_version_id => l_activity_version_id,
283 p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id,
284 p_booking_status_type => l_enroll_type,
285 p_date_status_changed => l_date_status_changed);
286 l_completion_date := null;
287
288 IF l_enroll_type = 'A' THEN
289 if l_online_flag = 'Y' then
290 --skip updating cme rollup, since player would update appr cme
291 exit;
292 end if;
293 l_member_status_code := 'COMPLETED';
294 l_completion_date := l_date_status_changed;
295 ELSIF ( l_enroll_type = 'P'
296 OR l_enroll_type = 'W'
297 OR l_enroll_type = 'R') THEN
298 l_member_status_code := 'ACTIVE';
299 ELSE l_member_status_code := 'PLANNED';
300 END IF;
301
302 l_old_member_status := rec_cme_info.member_status_code;
303
304 IF l_old_member_status <> l_member_status_code THEN
305 --call upd cme api after lck
306 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
307 (p_effective_date => sysdate
308 ,p_object_version_number => rec_cme_info.object_version_number
309 ,p_cert_member_id => rec_cme_info.certification_member_id
310 ,p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id
311 ,p_cert_mbr_enrollment_id => rec_cme_info.cert_mbr_enrollment_id
312 ,p_member_status_code => l_member_status_code
313 ,p_completion_date => l_completion_date);
314
315
316 Update_cpe_status(rec_cme_info.cert_mbr_enrollment_id, l_cert_prd_enrollment_id);
317
318 --populate OUT cert_prd_enrollment_ids params
319 IF l_cert_prd_enrollment_id IS NOT NULL THEN
320 if l_cert_prd_enrollment_ids = '' or l_cert_prd_enrollment_ids is null then
321 l_cert_prd_enrollment_ids := l_cert_prd_enrollment_id;
322 else
323 l_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids || '^' || l_cert_prd_enrollment_id;
324 end if;
325 END IF;
326
327 END IF;
328
329
330
331
332 END LOOP;
333
334 p_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids;
335 hr_utility.set_location(' Step:'||l_proc,30);
336
337 EXCEPTION
338 WHEN others THEN
339 hr_utility.set_location('Leaving :'||l_proc,40);
340 p_cert_prd_enrollment_ids := null;
341 --MULTI MESSAGE SUPPORT
342
343 END update_cme_status;
344
345 -- ---------------------------------------------------------------------------
346 -- |----------------------< Update_cpe_status >--------------------------|
347 -- ---------------------------------------------------------------------------
348 --
349 -- This procedure will get called when a cme is Updated or Cancelled
350 Procedure Update_cpe_status( p_cert_mbr_enrollment_id IN ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE
351 ,p_cert_prd_enrollment_id OUT NOCOPY varchar2
352 ,p_completion_date in date default sysdate)
353 is
354
355 CURSOR csr_cpe_cme
356 IS
357 SELECT cre.certification_id,
358 cpe.cert_enrollment_id,
359 cpe.cert_prd_enrollment_id,
360 cpe.period_status_code
361 FROM ota_cert_enrollments cre,
362 ota_cert_prd_enrollments cpe,
363 ota_cert_mbr_enrollments cme
364 WHERE cre.cert_enrollment_id = cpe.cert_enrollment_id
365 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
366 AND cpe.period_status_code not in ('CANCELLED', 'EXPIRED')
367 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
368 AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date);
369
370 CURSOR csr_cpe_status(csr_cert_prd_enrollment_id number)
371 IS
372 SELECT cpe.period_status_code
373 FROM ota_cert_prd_enrollments cpe
374 WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
375
376 /*
377 CURSOR csr_cpe_update(csr_cert_prd_enrollment_id number)
378 IS
379 SELECT cpe.object_version_number
380 FROM ota_cert_prd_enrollments cpe
381 WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
382
383 CURSOR csr_cre_update(csr_cert_enrollment_id number)
384 IS
385 SELECT cre.object_version_number
386 FROM ota_cert_enrollments cre
387 where cre.cert_enrollment_id = csr_cert_enrollment_id;
388
389
390 -- l_exists ota_cert_prd_enrollments.cert_mbr_enrollment_id%TYPE;
391 cre_object_version_number ota_cert_enrollments.object_version_number%type;
392 cpe_object_version_number ota_cert_prd_enrollments.object_version_number%type;
393 l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;
394 l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;
395 l_chk_cert_prd_compl varchar2(1);
396 l_completion_date date;
397 */
398 rec_cpe_status csr_cpe_status%rowtype;
399 l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;
400 l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;
401
402 l_proc VARCHAR2(72) := g_package|| 'Update_cpe_status';
403
404 l_child_update_flag varchar2(1) := 'N';
405 BEGIN
406 hr_utility.set_location(' Step:'|| l_proc, 10);
407
408 FOR rec_cpe_cme in csr_cpe_cme LOOP
409 l_period_status_code := rec_cpe_cme.period_status_code;
410 --update cpe rec
411 ota_cpe_util.update_cpe_status(rec_cpe_cme.cert_prd_enrollment_id, l_certification_status_code, null, null, l_child_update_flag, p_completion_date);
412
413 open csr_cpe_status(rec_cpe_cme.cert_prd_enrollment_id);
414 fetch csr_cpe_status into rec_cpe_status;
415 close csr_cpe_status;
416
417 --check for status change and populate param out cert_prd_enrollment_id
421
418 IF l_period_status_code <> rec_cpe_status.period_status_code THEN
419 p_cert_prd_enrollment_id := rec_cpe_cme.cert_prd_enrollment_id;
420 END IF;
422 END LOOP;
423
424 hr_utility.set_location(' Step:'|| l_proc, 20);
425 EXCEPTION
426 WHEN others THEN
427 hr_utility.set_location('Leaving :'||l_proc,30);
428 p_cert_prd_enrollment_id := null;
429 --MULTI MESSAGE SUPPORT
430
431 END Update_cpe_status;
432
433 -- ---------------------------------------------------------------------------
434 -- |----------------------< update_cme_status >------------------------------|
435 -- ---------------------------------------------------------------------------
436 PROCEDURE update_cme_status (p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
437 IS
438
439 l_proc VARCHAR2(72) := g_package|| 'update_cme_status';
440
441
442 CURSOR csr_cme_info IS
443 SELECT cme.cert_mbr_enrollment_id,
444 cme.cert_prd_enrollment_id,
445 cme.object_version_number,
446 cmb.object_id,
447 cmb.certification_member_id,
448 cme.member_status_code
449 FROM ota_certification_members cmb,
450 ota_cert_mbr_enrollments cme
451 WHERE cme.cert_member_id = cmb.certification_member_id
452 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
453 --AND cme.member_status_code <> 'CANCELLED';
454
455 l_enroll_type ota_booking_status_types.type%TYPE;
456 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
457 l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
458 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
459
460 --variables to store old values
461 l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
462
463 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
464 l_cert_prd_enrollment_ids varchar2(4000) := '';
465
466 rec_cme_info csr_cme_info%ROWTYPE;
467
468 BEGIN
469
470 hr_utility.set_location(' Step:'||l_proc,10);
471
472 open csr_cme_info;
473 fetch csr_cme_info into rec_cme_info;
474 close csr_cme_info;
475
476 get_enrl_status_on_update(p_activity_version_id => rec_cme_info.object_id,
477 p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id,
478 p_booking_status_type => l_enroll_type,
479 p_date_status_changed => l_date_status_changed);
480 l_completion_date := null;
481
482 IF l_enroll_type = 'A' THEN
483 l_member_status_code := 'COMPLETED';
484 l_completion_date := l_date_status_changed;
485 ELSIF ( l_enroll_type = 'P'
486 OR l_enroll_type = 'W'
487 OR l_enroll_type = 'R') THEN
488 l_member_status_code := 'ACTIVE';
489 ELSE l_member_status_code := 'PLANNED';
490 END IF;
491
492 l_old_member_status := rec_cme_info.member_status_code;
493
494 IF l_old_member_status <> l_member_status_code THEN
495 --call upd cme api after lck
496 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
497 (p_effective_date => sysdate
498 ,p_object_version_number => rec_cme_info.object_version_number
499 ,p_cert_member_id => rec_cme_info.certification_member_id
500 ,p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id
501 ,p_cert_mbr_enrollment_id => rec_cme_info.cert_mbr_enrollment_id
502 ,p_member_status_code => l_member_status_code
503 ,p_completion_date => l_completion_date);
504 END IF;
505
506 hr_utility.set_location(' Step:'||l_proc,30);
507
508 EXCEPTION
509 WHEN others THEN
510 hr_utility.set_location('Leaving :'||l_proc,40);
511 --MULTI MESSAGE SUPPORT
512
513 END update_cme_status;
514
515 -- ----------------------------------------------------------------------------
516 -- |-----------------------< chk_if_cme_exists >------------------------------|
517 -- ----------------------------------------------------------------------------
518 --
519 PROCEDURE chk_if_cme_exists
520 (p_cmb_id IN ota_certification_members.certification_member_id%TYPE
521 , p_return_status OUT NOCOPY VARCHAR2)
522 IS
523 --
524 --
525 v_exists varchar2(1);
526 v_proc varchar2(72) := g_package||'chk_if_cme_exists';
527 --
528 cursor sel_cme_exists is
529 select 'Y'
530 from ota_cert_mbr_enrollments cme
531 where cme.cert_member_id = p_cmb_id;
532 --
533 Begin
534 --
535 hr_utility.set_location('Entering:'|| v_proc, 5);
536 --
537
538 p_return_status := 'S';
539
540 Open sel_cme_exists;
541 fetch sel_cme_exists into v_exists;
542 --
543 if sel_cme_exists%found then
544 --
545 close sel_cme_exists;
546 --
547 p_return_status := 'E';
548 --
549 else
553 --
550 close sel_cme_exists;
551
552 end if;
554
555 hr_utility.set_location(' Step:'|| v_proc, 30);
556
557 END chk_if_cme_exists;
558
559 -- ----------------------------------------------------------------------------
560 -- |-----------------------< refresh_cme >------------------------------|
561 -- ----------------------------------------------------------------------------
562 --
563 procedure refresh_cme(p_cert_prd_enrollment_id in ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) IS
564
565 --cpe csr
566 CURSOR csr_cpe IS
567 select
568 cre.certification_id,
569 cpe.business_group_id
570 FROM ota_cert_enrollments cre,
571 ota_cert_prd_enrollments cpe,
572 ota_certifications_b crt
573 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
574 and cre.certification_id = crt.certification_id
575 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
576
577 --csr for new courses since last unsubscribe
578 CURSOR csr_new_crs(p_certification_id in number) IS
579 select
580 cmb.CERTIFICATION_MEMBER_ID
581 , cmb.CERTIFICATION_ID
582 , cmb.OBJECT_ID
583 , cmb.OBJECT_TYPE
584 , cmb.MEMBER_SEQUENCE
585 , cmb.START_DATE_ACTIVE
586 , cmb.END_DATE_ACTIVE
587 from ota_certification_members cmb
588 where cmb.certification_id = p_certification_id
589 and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
590 and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'))
591 and cmb.OBJECT_TYPE = 'H'
592 and not exists (select
593 null
594 from ota_cert_mbr_enrollments cme2,
595 ota_certification_members cmb2
596 where cme2.cert_member_id = cmb2.certification_member_id
597 and cme2.cert_prd_enrollment_id = p_cert_prd_enrollment_id
598 and cmb2.object_id = cmb.object_id
599 and cmb2.OBJECT_TYPE = 'H');
600
601 --end dated courses since last unsubscribe
602 CURSOR csr_end_crs IS
603 SELECT cme.cert_mbr_enrollment_id,
604 cme.object_version_number,
605 cmb.certification_member_id,
606 cme.member_status_code,
607 cmb.object_id
608 FROM ota_certification_members cmb,
609 ota_cert_mbr_enrollments cme
610 WHERE
611 cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
612 AND cme.cert_member_id = cmb.certification_member_id
613 AND cme.member_status_code <> 'CANCELLED'
614 AND cmb.object_type = 'H'
615 and not exists (select
616 null
617 from ota_certification_members cmb2
618 where cmb2.OBJECT_ID = cmb.object_id
619 and cmb2.OBJECT_type = 'H'
620 and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
621 and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD')));
622
623 l_proc varchar2(72) := g_package||'refresh_cme';
624
625 l_attribute_category VARCHAR2(30) := NULL;
626 l_attribute1 VARCHAR2(150) := NULL;
627 l_attribute2 VARCHAR2(150) := NULL;
628 l_attribute3 VARCHAR2(150) := NULL;
629 l_attribute4 VARCHAR2(150) := NULL;
630 l_attribute5 VARCHAR2(150) := NULL;
631 l_attribute6 VARCHAR2(150) := NULL;
632 l_attribute7 VARCHAR2(150) := NULL;
633 l_attribute8 VARCHAR2(150) := NULL;
634 l_attribute9 VARCHAR2(150) := NULL;
635 l_attribute10 VARCHAR2(150) := NULL;
636 l_attribute11 VARCHAR2(150) := NULL;
637 l_attribute12 VARCHAR2(150) := NULL;
638 l_attribute13 VARCHAR2(150) := NULL;
639 l_attribute14 VARCHAR2(150) := NULL;
640 l_attribute15 VARCHAR2(150) := NULL;
641 l_attribute16 VARCHAR2(150) := NULL;
642 l_attribute17 VARCHAR2(150) := NULL;
643 l_attribute18 VARCHAR2(150) := NULL;
644 l_attribute19 VARCHAR2(150) := NULL;
645 l_attribute20 VARCHAR2(150) := NULL;
646
647 p_attribute_category VARCHAR2(30) := NULL;
648 p_attribute1 VARCHAR2(150) := NULL;
649 p_attribute2 VARCHAR2(150) := NULL;
650 p_attribute3 VARCHAR2(150) := NULL;
651 p_attribute4 VARCHAR2(150) := NULL;
652 p_attribute5 VARCHAR2(150) := NULL;
653 p_attribute6 VARCHAR2(150) := NULL;
654 p_attribute7 VARCHAR2(150) := NULL;
655 p_attribute8 VARCHAR2(150) := NULL;
656 p_attribute9 VARCHAR2(150) := NULL;
657 p_attribute10 VARCHAR2(150) := NULL;
658 p_attribute11 VARCHAR2(150) := NULL;
659 p_attribute12 VARCHAR2(150) := NULL;
660 p_attribute13 VARCHAR2(150) := NULL;
661 p_attribute14 VARCHAR2(150) := NULL;
662 p_attribute15 VARCHAR2(150) := NULL;
663 p_attribute16 VARCHAR2(150) := NULL;
664 p_attribute17 VARCHAR2(150) := NULL;
665 p_attribute18 VARCHAR2(150) := NULL;
666 p_attribute19 VARCHAR2(150) := NULL;
667 p_attribute20 VARCHAR2(150) := NULL;
668
669 rec_cpe csr_cpe%rowtype;
670 l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
671 l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;
672
673 Begin
674 hr_utility.set_location(' Entering:'||l_proc,10);
675
676 open csr_cpe;
677 fetch csr_cpe into rec_cpe;
678 close csr_cpe;
679
680 --check for new courses since last unsubscribe
681 for new_crs in csr_new_crs(rec_cpe.certification_id)
682 loop
683 --create cme record
684
685 hr_utility.set_location(' Step:'||l_proc,20);
686
687 ota_utility.Get_Default_Value_Dff(
688 appl_short_name => 'OTA'
689 ,flex_field_name => 'OTA_CERT_MBR_ENROLLMENTS'
690 ,p_attribute_category => l_attribute_category
691 ,p_attribute1 => l_attribute1
692 ,p_attribute2 => l_attribute2
693 ,p_attribute3 => l_attribute3
694 ,p_attribute4 => l_attribute4
695 ,p_attribute5 => l_attribute5
696 ,p_attribute6 => l_attribute6
697 ,p_attribute7 => l_attribute7
698 ,p_attribute8 => l_attribute8
699 ,p_attribute9 => l_attribute9
700 ,p_attribute10 => l_attribute10
701 ,p_attribute11 => l_attribute11
702 ,p_attribute12 => l_attribute12
703 ,p_attribute13 => l_attribute13
704 ,p_attribute14 => l_attribute14
705 ,p_attribute15 => l_attribute15
706 ,p_attribute16 => l_attribute16
707 ,p_attribute17 => l_attribute17
708 ,p_attribute18 => l_attribute18
709 ,p_attribute19 => l_attribute19
710 ,p_attribute20 => l_attribute20);
711
712 hr_utility.set_location(' Step:'||l_proc,30);
713
714 ota_cert_mbr_enrollment_api.create_cert_mbr_enrollment(
715 p_effective_date => trunc(sysdate)
716 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
717 ,p_cert_member_id => new_crs.certification_member_id
718 ,p_member_status_code => 'PLANNED'
719 ,p_business_group_id => rec_cpe.business_group_id
720 ,p_cert_mbr_enrollment_id => l_cert_mbr_enrollment_id
721 ,p_object_version_number => l_object_version_number
722 ,p_attribute_category => l_attribute_category
723 ,p_attribute1 => l_attribute1
724 ,p_attribute2 => l_attribute2
725 ,p_attribute3 => l_attribute3
726 ,p_attribute4 => l_attribute4
727 ,p_attribute5 => l_attribute5
728 ,p_attribute6 => l_attribute6
729 ,p_attribute7 => l_attribute7
730 ,p_attribute8 => l_attribute8
731 ,p_attribute9 => l_attribute9
732 ,p_attribute10 => l_attribute10
733 ,p_attribute11 => l_attribute11
734 ,p_attribute12 => l_attribute12
735 ,p_attribute13 => l_attribute13
736 ,p_attribute14 => l_attribute14
737 ,p_attribute15 => l_attribute15
738 ,p_attribute16 => l_attribute16
739 ,p_attribute17 => l_attribute17
740 ,p_attribute18 => l_attribute18
741 ,p_attribute19 => l_attribute19
742 ,p_attribute20 => l_attribute20
743 );
744
745 end loop;
746
747 hr_utility.set_location(' Step:'||l_proc,40);
748
749 --update cme_record to CANCELLED status for activities which are ended during re-cert
750 for end_crs in csr_end_crs
751 loop
752 --update cme to CANCELLED
753 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
754 (p_effective_date => sysdate
755 ,p_object_version_number => end_crs.object_version_number
756 ,p_cert_member_id => end_crs.certification_member_id
757 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
758 ,p_cert_mbr_enrollment_id => end_crs.cert_mbr_enrollment_id
759 ,p_member_status_code => 'CANCELLED');
760 end loop;
761
762 hr_utility.set_location(' Step:'||l_proc,50);
763
764 EXCEPTION
765 WHEN others THEN
766 hr_utility.set_location('Leaving :'||l_proc,60);
767 END refresh_cme;
768
769 Function chk_active_cme_enrl(p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
770 return varchar2
771 IS
772
773 cursor csr_active_enrl is
774 SELECT
775 s.type Enrollment_Status_Type
776 FROM ota_events e,
777 ota_events_tl et,
778 ota_activity_versions a,
779 ota_delegate_bookings b,
780 ota_booking_status_types_VL s,
781 ota_cert_enrollments cre,
782 ota_cert_prd_enrollments cpe,
783 ota_cert_mbr_enrollments cme,
784 ota_certification_members cmb,
785 ota_offerings ofr,
786 ota_category_usages c
787 WHERE e.event_id = b.event_id
788 AND cre.cert_enrollment_id = cpe.cert_enrollment_id
789 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
790 AND e.event_id= et.event_id
791 AND s.type <> 'C'
792 AND et.language = USERENV('LANG')
793 AND cme.cert_member_id = cmb.certification_member_id
794 AND cmb.object_id = a.activity_version_id
795 AND cmb.object_type = 'H'
796 AND e.parent_offering_id = ofr.offering_id
797 AND e.activity_version_id = a.activity_version_id
798 AND b.booking_status_type_id = s.booking_status_type_id
799 AND ((cre.person_id IS NOT NULL AND b.delegate_person_id = cre.person_id) OR (cre.CONTACT_ID IS NOT NULL AND b.delegate_contact_id = cre.contact_id))
800 AND E.PARENT_OFFERING_ID=OFR.OFFERING_ID
801 AND OFR.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
802 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
803 AND ((cre.person_id IS NOT NULL AND b.delegate_person_id = cre.person_id) OR (cre.CONTACT_ID IS NOT NULL AND b.delegate_contact_id = cre.contact_id))
804 AND ( ( e.course_start_date >= cert_period_start_date
805 and nvl(e.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= cert_period_end_date )
806 or (event_type ='SELFPACED' and ((cert_period_end_date >= e.course_start_date)
807 AND ((e.course_end_date is null) or (e.course_end_date IS NOT NULL AND e.course_end_date >= cert_period_start_date)))));
808
809 l_proc VARCHAR2(72) := g_package ||'chk_active_cme_enrl';
810
811
812 l_enrollment_Status_Type ota_booking_status_types.Type%type;
813 l_return_flag varchar2(1) := 'F';
814
815 begin
816
817 hr_utility.set_location(' Entering:' || l_proc,10);
818
819 FOR rec IN csr_active_enrl
820 LOOP
821 l_enrollment_Status_Type := rec.enrollment_Status_Type;
822 l_return_flag := 'T';
823 exit;
824 END LOOP;
825
826 return l_return_flag;
827 EXCEPTION
828 WHEN others THEN
829 hr_utility.set_location('Leaving :'||l_proc,15);
830 RETURN l_return_flag;
831 end chk_active_cme_enrl;
832
833
834 END OTA_CME_UTIL;
835