[Home] [Help]
PACKAGE BODY: APPS.OTA_CME_UTIL
Source
1 PACKAGE BODY OTA_CME_UTIL as
2 /* $Header: otcmewrs.pkb 120.19.12020000.2 2012/12/03 11:20:01 jaysridh 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 tdb.successful_attendance_flag,
21 tdb.sign_eval_status ---added for sign in cert.10164130
22 FROM ota_events evt,
23 ota_delegate_bookings tdb,
24 ota_booking_status_types bst
25 WHERE evt.event_id = tdb.event_id
26 AND bst.booking_status_type_id = tdb.booking_status_type_id
27 and (
28 --sync sched, online(conf) or offline(ILT)
29 --sync always have an end date
30 ( evt.event_type = 'SCHEDULED' and
31 evt.course_start_date >= csr_cert_period_start_date and
32 evt.course_end_date <= csr_cert_period_end_date )
33 or
34 --async selfpaced, online(selfp) or offline(CBT)
35 --async have opt end date
36 (event_type ='SELFPACED' and
37 (csr_cert_period_end_date >= evt.course_start_date) AND
38 ((evt.course_end_date is null) or
39 (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))))
40 AND evt.activity_version_id = csr_activity_version_id
41 AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
42 OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
43 )
44 order by status;
45 --
46
47 PROCEDURE get_enrl_status_on_update(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
48 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
49 p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
50 p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE)
51 IS
52
53 CURSOR csr_cert_enrl IS
54 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
55 FROM ota_cert_enrollments cre,
56 ota_cert_prd_enrollments cpe
57 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
58 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
59
60 l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';
61
62 l_person_id ota_cert_enrollments.person_id%TYPE;
63 l_contact_id ota_cert_enrollments.contact_id%TYPE;
64 l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
65 l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
66
67 l_enroll_status VARCHAR2(30);
68 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
69
70 BEGIN
71
72 hr_utility.set_location(' Step:'|| l_proc, 10);
73
74 OPEN csr_cert_enrl;
75 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
76 CLOSE csr_cert_enrl;
77
78 FOR rec_enr IN get_enrl_status(p_activity_version_id,
79 l_cert_period_start_date,
80 l_cert_period_end_date,
81 l_person_id,
82 l_contact_id)
83 LOOP
84 l_enroll_status := rec_enr.status ;
85 l_date_status_changed := rec_enr.date_status_changed;
86 EXIT;
87 END LOOP;
88
89
90 p_booking_status_type := l_enroll_status;
91 p_date_status_changed := l_date_status_changed;
92
93 hr_utility.set_location(' Step:'|| l_proc, 20);
94 --
95 EXCEPTION
96 WHEN others THEN
97 hr_utility.set_location('Leaving :'||l_proc,30);
98 p_booking_status_type := null;
99 p_date_status_changed := null;
100
101 END get_enrl_status_on_update;
102
103 PROCEDURE get_enrl_status_on_update(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
104 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
105 p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
106 p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE,
107 p_successful_attendance_flag OUT NOCOPY ota_delegate_bookings.successful_attendance_flag%TYPE)
108 IS
109
110 CURSOR csr_cert_enrl IS
111 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
112 FROM ota_cert_enrollments cre,
113 ota_cert_prd_enrollments cpe
114 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
115 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
116
117 l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';
118
119 l_person_id ota_cert_enrollments.person_id%TYPE;
120 l_contact_id ota_cert_enrollments.contact_id%TYPE;
121 l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
122 l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
123
124 l_enroll_status VARCHAR2(30);
125 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
126 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
127
128 BEGIN
129
130 hr_utility.set_location(' Step:'|| l_proc, 10);
131
132 OPEN csr_cert_enrl;
133 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
134 CLOSE csr_cert_enrl;
135
136 FOR rec_enr IN get_enrl_status(p_activity_version_id,
137 l_cert_period_start_date,
138 l_cert_period_end_date,
139 l_person_id,
140 l_contact_id)
141 LOOP
142 l_enroll_status := rec_enr.status ;
143 l_date_status_changed := rec_enr.date_status_changed;
144 l_successful_attendance_flag := rec_enr.successful_attendance_flag;
145 EXIT;
146 END LOOP;
147
148
149 p_booking_status_type := l_enroll_status;
150 p_date_status_changed := l_date_status_changed;
151 p_successful_attendance_flag := l_successful_attendance_flag;
152
153 hr_utility.set_location(' Step:'|| l_proc, 20);
154 --
155 EXCEPTION
156 WHEN others THEN
157 hr_utility.set_location('Leaving :'||l_proc,30);
158 p_booking_status_type := null;
159 p_date_status_changed := null;
160 p_successful_attendance_flag := null;
161
162 END get_enrl_status_on_update;
163
164 -- ---------------------------------------------------------------------------
165 -- |----------------------< calculate_cme_status >-----------------------------|
166 -- ---------------------------------------------------------------------------
167 PROCEDURE calculate_cme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
168 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
169 p_mode in varchar2,
170 p_member_status_code OUT nocopy VARCHAR2,
171 p_completion_date OUT nocopy DATE,
172 p_is_recert IN VARCHAR2 default 'N') --need to be passed only when p_mode is 'C'
173 IS
174
175 CURSOR csr_cert_enrl IS
176 SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
177 FROM ota_cert_enrollments cre,
178 ota_cert_prd_enrollments cpe
179 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
180 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
181
182 CURSOR csr_cert_prd_count IS
183 select count(cert_prd_enrollment_id)
184 from ota_cert_prd_enrollments
185 where CERT_ENROLLMENT_ID = (select CERT_ENROLLMENT_ID
186 from ota_cert_prd_enrollments
187 where CERT_PRD_ENROLLMENT_ID = p_cert_prd_enrollment_id); --Bug 15938221
188
189 l_proc VARCHAR2(72) := g_package|| 'calculate_cme_status';
190
191 l_person_id ota_cert_enrollments.person_id%TYPE;
192 l_contact_id ota_cert_enrollments.contact_id%TYPE;
193 l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
194 l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
195
196 l_enroll_status VARCHAR2(30);
197 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
198 l_event_type ota_events.event_type%type;
199 l_course_start_date ota_events.course_start_date%type;
200 l_course_end_date ota_events.course_end_date%type;
201 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
202 l_sign_eval_status ota_delegate_bookings.sign_eval_status%type; --10164130
203 l_cert_prd_count number := 0; --Bug 15938221
204
205 BEGIN
206
207 hr_utility.set_location('Entering:'|| l_proc, 10);
208
209 OPEN csr_cert_enrl;
210 FETCH csr_cert_enrl into l_person_id, l_contact_id, l_cert_period_start_date, l_cert_period_end_date;
211 CLOSE csr_cert_enrl;
212
213 hr_utility.set_location('Entering:'|| l_proc, 20);
214
215 FOR rec_enr IN get_enrl_status(p_activity_version_id,
216 l_cert_period_start_date,
217 l_cert_period_end_date,
218 l_person_id,
219 l_contact_id)
220 LOOP
221 l_enroll_status := rec_enr.status ;
222 l_date_status_changed := rec_enr.date_status_changed;
223 l_event_type := rec_enr.event_type;
224 l_course_start_date := rec_enr.course_start_date;
225 l_course_end_date := rec_enr.course_end_date;
226 l_successful_attendance_flag := rec_enr.successful_attendance_flag;
227 l_sign_eval_status := rec_enr.sign_eval_status; --10164130
228 EXIT;
229 END LOOP;
230
231 hr_utility.set_location('Entering:'|| l_proc, 30);
232
233 if p_mode = 'C' then
234
235 --Bug 15938221
236 OPEN csr_cert_prd_count;
237 FETCH csr_cert_prd_count into l_cert_prd_count;
238 CLOSE csr_cert_prd_count;
239
240 --don't consider past attended enrls during cert mbr enrl create
241 -- enable selfpaced event enrl compl in the past as Active for next cert prd comps
242 if l_enroll_status = 'A' and
243 -- Bug 4515924 --rec_enr.event_type = 'SELFPACED' then
244 (l_event_type = 'SELFPACED' AND
245 ((l_cert_period_end_date >= l_course_start_date) AND
246 ((l_course_end_date is null) or
247 (l_course_end_date IS NOT NULL AND l_course_end_date >= l_cert_period_start_date))
248 ) AND p_is_recert = 'N' AND l_cert_prd_count > 1) THEN --Bug 15938221
249 p_member_status_code := 'ACTIVE';-- intreprit cme status as Active
250 p_completion_date := null;
251 ELSIF ( l_enroll_status='P'
252 OR l_enroll_status='W'
253 OR l_enroll_status ='R') THEN
254 p_member_status_code := 'ACTIVE';
255 p_completion_date := null;
256 ELSIF l_enroll_status='E' THEN ---added for sign in cert.10164130
257 p_member_status_code := 'PENDING';
258 p_completion_date := null; ---added for sign in cert.
259 ELSE
260 p_member_status_code := 'PLANNED';
261 p_completion_date := null;
262 END IF;
263
264 elsif p_mode = 'U' then
265 --consider attended enrls only during the cert prd start and end dates.
266 IF ( l_enroll_status='A' ) THEN
267 if(l_sign_eval_status='CE') then --- new sign_eval_status for enabling sign
268 p_member_status_code := 'PENDING'; --- when learner has played the course again
269 p_completion_date := null; --- after renewal of the cert.10164130
270 elsif(l_successful_attendance_flag = 'Y') then
271 p_member_status_code := 'COMPLETED';
272 p_completion_date := l_date_status_changed;
273 else
274 p_member_status_code := 'ACTIVE';
275 p_completion_date := null;
276 end if;
277 ELSIF ( l_enroll_status='P'
278 OR l_enroll_status='W'
279 OR l_enroll_status ='R') THEN
280 p_member_status_code := 'ACTIVE';
281 p_completion_date := null;
282 ELSIF l_enroll_status='E' THEN ---added for sign in cert.10164130
283 p_member_status_code := 'PENDING';
284 p_completion_date := null;
285 ELSE
286 p_member_status_code := 'PLANNED';
287 p_completion_date := null;
288 END IF;
289 end if;
290
291 hr_utility.set_location('Step:'|| l_proc, 40);
292
293 EXCEPTION
294 WHEN others THEN
295 hr_utility.set_location('LEAVING:'|| l_proc, 50);
296 p_member_status_code := 'PLANNED';
297 p_completion_date := null;
298 RAISE;
299
300 END calculate_cme_status;
301
302
303 -- ---------------------------------------------------------------------------
304 -- |----------------------< update_cme_status >-------------------|
305 -- ---------------------------------------------------------------------------
306 PROCEDURE update_cme_status (p_event_id IN ota_events.event_id%TYPE,
307 p_person_id IN ota_cert_enrollments.person_id%TYPE,
308 p_contact_id IN ota_cert_enrollments.contact_id%TYPE,
309 p_cert_prd_enrollment_ids OUT NOCOPY varchar2)
310 IS
311
312 l_proc VARCHAR2(72) := g_package|| 'update_cme_status';
313
314 CURSOR evt_det IS
315 SELECT evt.activity_version_id,
316 ocu.online_flag
317 FROM ota_events evt,
318 ota_offerings ofr,
319 ota_category_usages ocu
320 WHERE evt.event_id = p_event_id
321 AND evt.parent_offering_id = ofr.offering_id
322 AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
323
324
325 CURSOR csr_cme_info(csr_activity_version_id number) IS
326 SELECT cme.cert_mbr_enrollment_id,
327 cpe.cert_prd_enrollment_id,
328 cme.object_version_number,
329 cmb.certification_member_id,
330 cme.member_status_code
331 FROM ota_certification_members cmb,
332 ota_cert_mbr_enrollments cme,
333 ota_cert_prd_enrollments cpe,
334 ota_cert_enrollments cre
335 WHERE
336 cre.cert_enrollment_id = cpe.cert_enrollment_id
337 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
338 AND cme.cert_member_id = cmb.certification_member_id
339 AND cmb.object_id = csr_activity_version_id
340 AND (( p_person_id IS NOT NULL AND cre.person_id = p_person_id)
341 OR (p_contact_id IS NOT NULL AND cre.contact_id = p_contact_id))
342 AND cme.member_status_code <> 'CANCELLED'
343 --pull only curr periods
344 AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
345 -- don't consider expired prds
346 AND cpe.period_status_code <> 'EXPIRED';
347
348 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
349 l_online_flag ota_category_usages.online_flag%type;
350 l_enroll_type ota_booking_status_types.type%TYPE;
351 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
352 l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
353 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
354
355 --variables to store old values
356 l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
357
358 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
359 l_cert_prd_enrollment_ids varchar2(4000) := '';
360 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
361
362 BEGIN
363
364 hr_utility.set_location(' Step:'||l_proc,10);
365
366 OPEN evt_det;
367 FETCH evt_det
368 INTO l_activity_version_id, l_online_flag;
369 CLOSE evt_det;
370
371 hr_utility.set_location(' Step:'|| l_proc, 20);
372
373
374 FOR rec_cme_info IN csr_cme_info(l_activity_version_id)
375
376 LOOP
377
378 get_enrl_status_on_update(p_activity_version_id => l_activity_version_id,
379 p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id,
380 p_booking_status_type => l_enroll_type,
381 p_date_status_changed => l_date_status_changed,
382 p_successful_attendance_flag => l_successful_attendance_flag);
383 l_completion_date := null;
384
385 IF l_enroll_type = 'A' THEN
386 if l_online_flag = 'Y' then
387 --skip updating cme rollup, since player would update appr cme
388 exit;
389 end if;
390 if(l_successful_attendance_flag = 'Y') then
391 l_member_status_code := 'COMPLETED';
392 l_completion_date := l_date_status_changed;
393 else
394 l_member_status_code := rec_cme_info.member_status_code;
395 end if;
396 ELSIF ( l_enroll_type = 'P'
397 OR l_enroll_type = 'W'
398 OR l_enroll_type = 'R') THEN
399 l_member_status_code := 'ACTIVE';
400 ELSIF l_enroll_type = 'E' THEN ---added for sign in cert.10164130
401 l_member_status_code := 'PENDING';
402 ELSE l_member_status_code := 'PLANNED';
403 END IF;
404
405 l_old_member_status := rec_cme_info.member_status_code;
406
407 IF l_old_member_status <> l_member_status_code THEN
408 --call upd cme api after lck
409 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
410 (p_effective_date => sysdate
411 ,p_object_version_number => rec_cme_info.object_version_number
412 ,p_cert_member_id => rec_cme_info.certification_member_id
413 ,p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id
414 ,p_cert_mbr_enrollment_id => rec_cme_info.cert_mbr_enrollment_id
415 ,p_member_status_code => l_member_status_code
416 ,p_completion_date => l_completion_date);
417
418
419 Update_cpe_status(rec_cme_info.cert_mbr_enrollment_id, l_cert_prd_enrollment_id);
420
421 --populate OUT cert_prd_enrollment_ids params
422 IF l_cert_prd_enrollment_id IS NOT NULL THEN
423 if l_cert_prd_enrollment_ids = '' or l_cert_prd_enrollment_ids is null then
424 l_cert_prd_enrollment_ids := l_cert_prd_enrollment_id;
425 else
426 l_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids || '^' || l_cert_prd_enrollment_id;
427 end if;
428 END IF;
429
430 END IF;
431
432
433
434
435 END LOOP;
436
437 p_cert_prd_enrollment_ids := l_cert_prd_enrollment_ids;
438 hr_utility.set_location(' Step:'||l_proc,30);
439
440 EXCEPTION
441 WHEN others THEN
442 hr_utility.set_location('Leaving :'||l_proc,40);
443 p_cert_prd_enrollment_ids := null;
444 --MULTI MESSAGE SUPPORT
445
446 END update_cme_status;
447
448 -- ---------------------------------------------------------------------------
449 -- |----------------------< Update_cpe_status >--------------------------|
450 -- ---------------------------------------------------------------------------
451 --
452 -- This procedure will get called when a cme is Updated or Cancelled
453 Procedure Update_cpe_status( p_cert_mbr_enrollment_id IN ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE
454 ,p_cert_prd_enrollment_id OUT NOCOPY varchar2
455 ,p_completion_date in date default sysdate)
456 is
457
458 CURSOR csr_cpe_cme
459 IS
460 SELECT cre.certification_id,
461 cpe.cert_enrollment_id,
462 cpe.cert_prd_enrollment_id,
463 cpe.period_status_code
464 FROM ota_cert_enrollments cre,
465 ota_cert_prd_enrollments cpe,
466 ota_cert_mbr_enrollments cme
467 WHERE cre.cert_enrollment_id = cpe.cert_enrollment_id
468 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
469 AND cpe.period_status_code not in ('CANCELLED', 'EXPIRED')
470 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
471 AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date);
472
473 CURSOR csr_cpe_status(csr_cert_prd_enrollment_id number)
474 IS
475 SELECT cpe.period_status_code
476 FROM ota_cert_prd_enrollments cpe
477 WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
478
479 /*
480 CURSOR csr_cpe_update(csr_cert_prd_enrollment_id number)
481 IS
482 SELECT cpe.object_version_number
483 FROM ota_cert_prd_enrollments cpe
484 WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
485
486 CURSOR csr_cre_update(csr_cert_enrollment_id number)
487 IS
488 SELECT cre.object_version_number
489 FROM ota_cert_enrollments cre
490 where cre.cert_enrollment_id = csr_cert_enrollment_id;
491
492
493 -- l_exists ota_cert_prd_enrollments.cert_mbr_enrollment_id%TYPE;
494 cre_object_version_number ota_cert_enrollments.object_version_number%type;
495 cpe_object_version_number ota_cert_prd_enrollments.object_version_number%type;
496 l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;
497 l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;
498 l_chk_cert_prd_compl varchar2(1);
499 l_completion_date date;
500 */
501 rec_cpe_status csr_cpe_status%rowtype;
502 l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;
503 l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;
504
505 l_proc VARCHAR2(72) := g_package|| 'Update_cpe_status';
506
507 l_child_update_flag varchar2(1) := 'N';
508 BEGIN
509 hr_utility.set_location(' Step:'|| l_proc, 10);
510
511 FOR rec_cpe_cme in csr_cpe_cme LOOP
512 l_period_status_code := rec_cpe_cme.period_status_code;
513 --update cpe rec
514 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);
515
516 open csr_cpe_status(rec_cpe_cme.cert_prd_enrollment_id);
517 fetch csr_cpe_status into rec_cpe_status;
518 close csr_cpe_status;
519
520 --check for status change and populate param out cert_prd_enrollment_id
521 IF l_period_status_code <> rec_cpe_status.period_status_code THEN
522 p_cert_prd_enrollment_id := rec_cpe_cme.cert_prd_enrollment_id;
523 END IF;
524
525 END LOOP;
526
527 hr_utility.set_location(' Step:'|| l_proc, 20);
528 EXCEPTION
529 WHEN others THEN
530 hr_utility.set_location('Leaving :'||l_proc,30);
531 p_cert_prd_enrollment_id := null;
532 --MULTI MESSAGE SUPPORT
533
534 END Update_cpe_status;
535
536 -- ---------------------------------------------------------------------------
537 -- |----------------------< update_cme_status >------------------------------|
538 -- ---------------------------------------------------------------------------
539 PROCEDURE update_cme_status (p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
540 IS
541
542 l_proc VARCHAR2(72) := g_package|| 'update_cme_status';
543
544
545 CURSOR csr_cme_info IS
546 SELECT cme.cert_mbr_enrollment_id,
547 cme.cert_prd_enrollment_id,
548 cme.object_version_number,
549 cmb.object_id,
550 cmb.certification_member_id,
551 cme.member_status_code
552 FROM ota_certification_members cmb,
553 ota_cert_mbr_enrollments cme
554 WHERE cme.cert_member_id = cmb.certification_member_id
555 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
556 --AND cme.member_status_code <> 'CANCELLED';
557
558 l_enroll_type ota_booking_status_types.type%TYPE;
559 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
560 l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
561 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
562
563 --variables to store old values
564 l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
565
566 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
567 l_cert_prd_enrollment_ids varchar2(4000) := '';
568
569 rec_cme_info csr_cme_info%ROWTYPE;
570 l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
571
572 BEGIN
573
574 hr_utility.set_location(' Step:'||l_proc,10);
575
576 open csr_cme_info;
577 fetch csr_cme_info into rec_cme_info;
578 close csr_cme_info;
579
580 get_enrl_status_on_update(p_activity_version_id => rec_cme_info.object_id,
581 p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id,
582 p_booking_status_type => l_enroll_type,
583 p_date_status_changed => l_date_status_changed,
584 p_successful_attendance_flag => l_successful_attendance_flag);
585 l_completion_date := null;
586
587 IF l_enroll_type = 'A' THEN
588 if(l_successful_attendance_flag = 'Y') then
589 l_member_status_code := 'COMPLETED';
590 l_completion_date := l_date_status_changed;
591 else
592 l_member_status_code := rec_cme_info.member_status_code;
593 end if;
594 ELSIF ( l_enroll_type = 'P'
595 OR l_enroll_type = 'W'
596 OR l_enroll_type = 'R') THEN
597 l_member_status_code := 'ACTIVE';
598 ELSIF l_enroll_type = 'E' THEN ---added for sign in cert.10164130
599 l_member_status_code := 'PENDING';
600 ELSE l_member_status_code := 'PLANNED';
601 END IF;
602
603 l_old_member_status := rec_cme_info.member_status_code;
604
605 IF l_old_member_status <> l_member_status_code THEN
606 --call upd cme api after lck
607 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
608 (p_effective_date => sysdate
609 ,p_object_version_number => rec_cme_info.object_version_number
610 ,p_cert_member_id => rec_cme_info.certification_member_id
611 ,p_cert_prd_enrollment_id => rec_cme_info.cert_prd_enrollment_id
612 ,p_cert_mbr_enrollment_id => rec_cme_info.cert_mbr_enrollment_id
613 ,p_member_status_code => l_member_status_code
614 ,p_completion_date => l_completion_date);
615 END IF;
616
617 hr_utility.set_location(' Step:'||l_proc,30);
618
619 EXCEPTION
620 WHEN others THEN
621 hr_utility.set_location('Leaving :'||l_proc,40);
622 --MULTI MESSAGE SUPPORT
623
624 END update_cme_status;
625
626 -- ----------------------------------------------------------------------------
627 -- |-----------------------< chk_if_cme_exists >------------------------------|
628 -- ----------------------------------------------------------------------------
629 --
630 PROCEDURE chk_if_cme_exists
631 (p_cmb_id IN ota_certification_members.certification_member_id%TYPE
632 , p_return_status OUT NOCOPY VARCHAR2)
633 IS
634 --
635 --
636 v_exists varchar2(1);
637 v_proc varchar2(72) := g_package||'chk_if_cme_exists';
638 --
639 cursor sel_cme_exists is
640 select 'Y'
641 from ota_cert_mbr_enrollments cme
642 where cme.cert_member_id = p_cmb_id;
643 --
644 Begin
645 --
646 hr_utility.set_location('Entering:'|| v_proc, 5);
647 --
648
649 p_return_status := 'S';
650
651 Open sel_cme_exists;
652 fetch sel_cme_exists into v_exists;
653 --
654 if sel_cme_exists%found then
655 --
656 close sel_cme_exists;
657 --
658 p_return_status := 'E';
659 --
660 else
661 close sel_cme_exists;
662
663 end if;
664 --
665
666 hr_utility.set_location(' Step:'|| v_proc, 30);
667
668 END chk_if_cme_exists;
669
670 -- ----------------------------------------------------------------------------
671 -- |-----------------------< refresh_cme >------------------------------|
672 -- ----------------------------------------------------------------------------
673 --
674 procedure refresh_cme(p_cert_prd_enrollment_id in ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) IS
675
676 --cpe csr
677 CURSOR csr_cpe IS
678 select
679 cre.certification_id,
680 cpe.business_group_id
681 FROM ota_cert_enrollments cre,
682 ota_cert_prd_enrollments cpe,
683 ota_certifications_b crt
684 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
685 and cre.certification_id = crt.certification_id
686 and cpe.cert_enrollment_id = cre.cert_enrollment_id;
687
688 --csr for new courses since last unsubscribe
689 CURSOR csr_new_crs(p_certification_id in number) IS
690 select
691 cmb.CERTIFICATION_MEMBER_ID
692 , cmb.CERTIFICATION_ID
693 , cmb.OBJECT_ID
694 , cmb.OBJECT_TYPE
695 , cmb.MEMBER_SEQUENCE
696 , cmb.START_DATE_ACTIVE
697 , cmb.END_DATE_ACTIVE
698 from ota_certification_members cmb
699 where cmb.certification_id = p_certification_id
700 and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
701 and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'))
702 and cmb.OBJECT_TYPE = 'H'
703 and not exists (select
704 null
705 from ota_cert_mbr_enrollments cme2,
706 ota_certification_members cmb2
707 where cme2.cert_member_id = cmb2.certification_member_id
708 and cme2.cert_prd_enrollment_id = p_cert_prd_enrollment_id
709 and cmb2.object_id = cmb.object_id
710 and cmb2.OBJECT_TYPE = 'H');
711
712 --end dated courses since last unsubscribe
713 CURSOR csr_end_crs IS
714 SELECT cme.cert_mbr_enrollment_id,
715 cme.object_version_number,
716 cmb.certification_member_id,
717 cme.member_status_code,
718 cmb.object_id
719 FROM ota_certification_members cmb,
720 ota_cert_mbr_enrollments cme
721 WHERE
722 cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
723 AND cme.cert_member_id = cmb.certification_member_id
724 AND cme.member_status_code <> 'CANCELLED'
725 AND cmb.object_type = 'H'
726 and not exists (select
727 null
728 from ota_certification_members cmb2
729 where cmb2.OBJECT_ID = cmb.object_id
730 and cmb2.OBJECT_type = 'H'
731 and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
732 and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD')));
733
734 l_proc varchar2(72) := g_package||'refresh_cme';
735
736 l_attribute_category VARCHAR2(30) := NULL;
737 l_attribute1 VARCHAR2(150) := NULL;
738 l_attribute2 VARCHAR2(150) := NULL;
739 l_attribute3 VARCHAR2(150) := NULL;
740 l_attribute4 VARCHAR2(150) := NULL;
741 l_attribute5 VARCHAR2(150) := NULL;
742 l_attribute6 VARCHAR2(150) := NULL;
743 l_attribute7 VARCHAR2(150) := NULL;
744 l_attribute8 VARCHAR2(150) := NULL;
745 l_attribute9 VARCHAR2(150) := NULL;
746 l_attribute10 VARCHAR2(150) := NULL;
747 l_attribute11 VARCHAR2(150) := NULL;
748 l_attribute12 VARCHAR2(150) := NULL;
749 l_attribute13 VARCHAR2(150) := NULL;
750 l_attribute14 VARCHAR2(150) := NULL;
751 l_attribute15 VARCHAR2(150) := NULL;
752 l_attribute16 VARCHAR2(150) := NULL;
753 l_attribute17 VARCHAR2(150) := NULL;
754 l_attribute18 VARCHAR2(150) := NULL;
755 l_attribute19 VARCHAR2(150) := NULL;
756 l_attribute20 VARCHAR2(150) := NULL;
757
758 p_attribute_category VARCHAR2(30) := NULL;
759 p_attribute1 VARCHAR2(150) := NULL;
760 p_attribute2 VARCHAR2(150) := NULL;
761 p_attribute3 VARCHAR2(150) := NULL;
762 p_attribute4 VARCHAR2(150) := NULL;
763 p_attribute5 VARCHAR2(150) := NULL;
764 p_attribute6 VARCHAR2(150) := NULL;
765 p_attribute7 VARCHAR2(150) := NULL;
766 p_attribute8 VARCHAR2(150) := NULL;
767 p_attribute9 VARCHAR2(150) := NULL;
768 p_attribute10 VARCHAR2(150) := NULL;
769 p_attribute11 VARCHAR2(150) := NULL;
770 p_attribute12 VARCHAR2(150) := NULL;
771 p_attribute13 VARCHAR2(150) := NULL;
772 p_attribute14 VARCHAR2(150) := NULL;
773 p_attribute15 VARCHAR2(150) := NULL;
774 p_attribute16 VARCHAR2(150) := NULL;
775 p_attribute17 VARCHAR2(150) := NULL;
776 p_attribute18 VARCHAR2(150) := NULL;
777 p_attribute19 VARCHAR2(150) := NULL;
778 p_attribute20 VARCHAR2(150) := NULL;
779
780 rec_cpe csr_cpe%rowtype;
781 l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
782 l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;
783
784 Begin
785 hr_utility.set_location(' Entering:'||l_proc,10);
786
787 open csr_cpe;
788 fetch csr_cpe into rec_cpe;
789 close csr_cpe;
790
791 --check for new courses since last unsubscribe
792 for new_crs in csr_new_crs(rec_cpe.certification_id)
793 loop
794 --create cme record
795
796 hr_utility.set_location(' Step:'||l_proc,20);
797
798 ota_utility.Get_Default_Value_Dff(
799 appl_short_name => 'OTA'
800 ,flex_field_name => 'OTA_CERT_MBR_ENROLLMENTS'
801 ,p_attribute_category => l_attribute_category
802 ,p_attribute1 => l_attribute1
803 ,p_attribute2 => l_attribute2
804 ,p_attribute3 => l_attribute3
805 ,p_attribute4 => l_attribute4
806 ,p_attribute5 => l_attribute5
807 ,p_attribute6 => l_attribute6
808 ,p_attribute7 => l_attribute7
809 ,p_attribute8 => l_attribute8
810 ,p_attribute9 => l_attribute9
811 ,p_attribute10 => l_attribute10
812 ,p_attribute11 => l_attribute11
813 ,p_attribute12 => l_attribute12
814 ,p_attribute13 => l_attribute13
815 ,p_attribute14 => l_attribute14
816 ,p_attribute15 => l_attribute15
817 ,p_attribute16 => l_attribute16
818 ,p_attribute17 => l_attribute17
819 ,p_attribute18 => l_attribute18
820 ,p_attribute19 => l_attribute19
821 ,p_attribute20 => l_attribute20);
822
823 hr_utility.set_location(' Step:'||l_proc,30);
824
825 ota_cert_mbr_enrollment_api.create_cert_mbr_enrollment(
826 p_effective_date => trunc(sysdate)
827 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
828 ,p_cert_member_id => new_crs.certification_member_id
829 ,p_member_status_code => 'PLANNED'
830 ,p_business_group_id => rec_cpe.business_group_id
831 ,p_cert_mbr_enrollment_id => l_cert_mbr_enrollment_id
832 ,p_object_version_number => l_object_version_number
833 ,p_attribute_category => l_attribute_category
834 ,p_attribute1 => l_attribute1
835 ,p_attribute2 => l_attribute2
836 ,p_attribute3 => l_attribute3
837 ,p_attribute4 => l_attribute4
838 ,p_attribute5 => l_attribute5
839 ,p_attribute6 => l_attribute6
840 ,p_attribute7 => l_attribute7
841 ,p_attribute8 => l_attribute8
842 ,p_attribute9 => l_attribute9
843 ,p_attribute10 => l_attribute10
844 ,p_attribute11 => l_attribute11
845 ,p_attribute12 => l_attribute12
846 ,p_attribute13 => l_attribute13
847 ,p_attribute14 => l_attribute14
848 ,p_attribute15 => l_attribute15
849 ,p_attribute16 => l_attribute16
850 ,p_attribute17 => l_attribute17
851 ,p_attribute18 => l_attribute18
852 ,p_attribute19 => l_attribute19
853 ,p_attribute20 => l_attribute20
854 ,p_is_recert => 'Y' --Since refresh is recert passing 'Y'
855 );
856
857 end loop;
858
859 hr_utility.set_location(' Step:'||l_proc,40);
860
861 --update cme_record to CANCELLED status for activities which are ended during re-cert
862 for end_crs in csr_end_crs
863 loop
864 --update cme to CANCELLED
865 ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
866 (p_effective_date => sysdate
867 ,p_object_version_number => end_crs.object_version_number
868 ,p_cert_member_id => end_crs.certification_member_id
869 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
870 ,p_cert_mbr_enrollment_id => end_crs.cert_mbr_enrollment_id
871 ,p_member_status_code => 'CANCELLED');
872 end loop;
873
874 hr_utility.set_location(' Step:'||l_proc,50);
875
876 EXCEPTION
877 WHEN others THEN
878 hr_utility.set_location('Leaving :'||l_proc,60);
879 END refresh_cme;
880
881 Function chk_active_cme_enrl(p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
882 return varchar2
883 IS
884
885 cursor csr_active_enrl is
886 SELECT
887 s.type Enrollment_Status_Type
888 FROM ota_events e,
889 ota_events_tl et,
890 ota_activity_versions a,
891 ota_delegate_bookings b,
892 ota_booking_status_types_VL s,
893 ota_cert_enrollments cre,
894 ota_cert_prd_enrollments cpe,
895 ota_cert_mbr_enrollments cme,
896 ota_certification_members cmb,
897 ota_offerings ofr,
898 ota_category_usages c
899 WHERE e.event_id = b.event_id
900 AND cre.cert_enrollment_id = cpe.cert_enrollment_id
901 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
902 AND e.event_id= et.event_id
903 AND s.type <> 'C'
904 AND et.language = USERENV('LANG')
905 AND cme.cert_member_id = cmb.certification_member_id
906 AND cmb.object_id = a.activity_version_id
907 AND cmb.object_type = 'H'
908 AND e.parent_offering_id = ofr.offering_id
909 AND e.activity_version_id = a.activity_version_id
910 AND b.booking_status_type_id = s.booking_status_type_id
911 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))
912 AND E.PARENT_OFFERING_ID=OFR.OFFERING_ID
913 AND OFR.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
914 AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
915 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))
916 AND ( ( e.course_start_date >= cert_period_start_date
917 and nvl(e.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= cert_period_end_date )
918 or (event_type ='SELFPACED' and ((cert_period_end_date >= e.course_start_date)
919 AND ((e.course_end_date is null) or (e.course_end_date IS NOT NULL AND e.course_end_date >= cert_period_start_date)))));
920
921 l_proc VARCHAR2(72) := g_package ||'chk_active_cme_enrl';
922
923
924 l_enrollment_Status_Type ota_booking_status_types.Type%type;
925 l_return_flag varchar2(1) := 'F';
926
927 begin
928
929 hr_utility.set_location(' Entering:' || l_proc,10);
930
931 FOR rec IN csr_active_enrl
932 LOOP
933 l_enrollment_Status_Type := rec.enrollment_Status_Type;
934 l_return_flag := 'T';
935 exit;
936 END LOOP;
937
938 return l_return_flag;
939 EXCEPTION
940 WHEN others THEN
941 hr_utility.set_location('Leaving :'||l_proc,15);
942 RETURN l_return_flag;
943 end chk_active_cme_enrl;
944
945 function get_cert_mbr_enroll_id(p_event_id IN ota_events.event_id%type,
946 p_person_id IN ota_cert_enrollments.person_id%type,
947 p_contact_id ota_cert_enrollments.contact_id%type)
948 return ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type IS
949
950 CURSOR csr_cert_mbr_enrl_id_person IS
951 SELECT max(cme.cert_mbr_enrollment_id) cert_mbr_enrollment_id
952 FROM OTA_CERT_ENROLLMENTS cre,
953 OTA_CERT_PRD_ENROLLMENTS cpe,
954 OTA_CERT_MBR_ENROLLMENTS cme,
955 OTA_CERTIFICATION_MEMBERS cmb,
956 OTA_EVENTS evt
957 WHERE cre.person_id = p_person_id
958 AND cre.cert_enrollment_id = cpe.cert_enrollment_id
959 AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
960 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
961 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
962 AND evt.event_id = p_event_id
963 AND cmb.OBJECT_ID = evt.activity_version_id
964 AND cmb.OBJECT_TYPE = 'H'
965 AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
966 AND cme.cert_member_id = cmb.certification_member_id;
967
968 CURSOR csr_cert_mbr_enrl_id_contact IS
969 SELECT max(cme.cert_mbr_enrollment_id) cert_mbr_enrollment_id
970 FROM OTA_CERT_ENROLLMENTS cre,
971 OTA_CERT_PRD_ENROLLMENTS cpe,
972 OTA_CERT_MBR_ENROLLMENTS cme,
973 OTA_CERTIFICATION_MEMBERS cmb,
974 OTA_EVENTS evt
975 WHERE cre.contact_id = p_contact_id
976 AND cre.cert_enrollment_id = cpe.cert_enrollment_id
977 AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
978 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
979 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
980 AND evt.event_id = p_event_id
981 AND cmb.OBJECT_ID = evt.activity_version_id
982 AND cmb.OBJECT_TYPE = 'H'
983 AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
984 AND cme.cert_member_id = cmb.certification_member_id;
985
986 l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
987
988 BEGIN
989
990 if p_person_id is not null then
991 OPEN csr_cert_mbr_enrl_id_person;
992 FETCH csr_cert_mbr_enrl_id_person into l_cert_mbr_enrollment_id;
993 CLOSE csr_cert_mbr_enrl_id_person;
994
995 else
996 OPEN csr_cert_mbr_enrl_id_contact;
997 FETCH csr_cert_mbr_enrl_id_contact into l_cert_mbr_enrollment_id;
998 CLOSE csr_cert_mbr_enrl_id_contact;
999 end if;
1000
1001 return l_cert_mbr_enrollment_id;
1002
1003 END get_cert_mbr_enroll_id;
1004
1005 function get_cert_prd_enroll_id(p_event_id IN ota_events.event_id%type,
1006 p_person_id IN ota_cert_enrollments.person_id%type,
1007 p_contact_id ota_cert_enrollments.contact_id%type)
1008 return ota_cert_prd_enrollments.cert_prd_enrollment_id%type IS
1009
1010 CURSOR csr_cert_prd_enrl_id_person IS
1011 SELECT max(cme.cert_prd_enrollment_id) cert_prd_enrollment_id
1012 FROM OTA_CERT_ENROLLMENTS cre,
1013 OTA_CERT_PRD_ENROLLMENTS cpe,
1014 OTA_CERT_MBR_ENROLLMENTS cme,
1015 OTA_CERTIFICATION_MEMBERS cmb,
1016 OTA_EVENTS evt
1017 WHERE cre.person_id = p_person_id
1018 AND cre.cert_enrollment_id = cpe.cert_enrollment_id
1019 AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
1020 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
1021 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
1022 AND evt.event_id = p_event_id
1023 AND cmb.OBJECT_ID = evt.activity_version_id
1024 AND cmb.OBJECT_TYPE = 'H'
1025 AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
1026 AND cme.cert_member_id = cmb.certification_member_id;
1027
1028 CURSOR csr_cert_prd_enrl_id_contact IS
1029 SELECT max(cme.cert_prd_enrollment_id) cert_prd_enrollment_id
1030 FROM OTA_CERT_ENROLLMENTS cre,
1031 OTA_CERT_PRD_ENROLLMENTS cpe,
1032 OTA_CERT_MBR_ENROLLMENTS cme,
1033 OTA_CERTIFICATION_MEMBERS cmb,
1034 OTA_EVENTS evt
1035 WHERE cre.contact_id = p_contact_id
1036 AND cre.cert_enrollment_id = cpe.cert_enrollment_id
1037 AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
1038 AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
1039 AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
1040 AND evt.event_id = p_event_id
1041 AND cmb.OBJECT_ID = evt.activity_version_id
1042 AND cmb.OBJECT_TYPE = 'H'
1043 AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
1044 AND cme.cert_member_id = cmb.certification_member_id;
1045
1046 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
1047
1048 BEGIN
1049
1050 if p_person_id is not null then
1051 OPEN csr_cert_prd_enrl_id_person;
1052 FETCH csr_cert_prd_enrl_id_person into l_cert_prd_enrollment_id;
1053 CLOSE csr_cert_prd_enrl_id_person;
1054
1055 else
1056 OPEN csr_cert_prd_enrl_id_contact;
1057 FETCH csr_cert_prd_enrl_id_contact into l_cert_prd_enrollment_id;
1058 CLOSE csr_cert_prd_enrl_id_contact;
1059 end if;
1060
1061 return l_cert_prd_enrollment_id;
1062
1063 END get_cert_prd_enroll_id;
1064
1065 END OTA_CME_UTIL;
1066