[Home] [Help]
PACKAGE BODY: APPS.OTA_CERT_PRD_ENROLLMENT_API
Source
1 Package Body OTA_CERT_PRD_ENROLLMENT_API as
2 /* $Header: otcpeapi.pkb 120.13.12010000.3 2008/09/22 10:52:35 pekasi ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_CERT_PRD_ENROLLMENT_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_cert_prd_enrollment >-----------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_cert_prd_enrollment
13 (
14 p_effective_date in date,
15 p_validate in boolean default false ,
16 p_cert_enrollment_id in number,
17 p_period_status_code in varchar2,
18 p_completion_date in date default null,
19 p_cert_period_start_date in date default null,
20 p_cert_period_end_date in date default null,
21 p_business_group_id in number,
22 p_attribute_category in varchar2 default null,
23 p_attribute1 in varchar2 default null,
24 p_attribute2 in varchar2 default null,
25 p_attribute3 in varchar2 default null,
26 p_attribute4 in varchar2 default null,
27 p_attribute5 in varchar2 default null,
28 p_attribute6 in varchar2 default null,
29 p_attribute7 in varchar2 default null,
30 p_attribute8 in varchar2 default null,
31 p_attribute9 in varchar2 default null,
32 p_attribute10 in varchar2 default null,
33 p_attribute11 in varchar2 default null,
34 p_attribute12 in varchar2 default null,
35 p_attribute13 in varchar2 default null,
36 p_attribute14 in varchar2 default null,
37 p_attribute15 in varchar2 default null,
38 p_attribute16 in varchar2 default null,
39 p_attribute17 in varchar2 default null,
40 p_attribute18 in varchar2 default null,
41 p_attribute19 in varchar2 default null,
42 p_attribute20 in varchar2 default null,
43 p_expiration_date in date default null,
44 p_cert_prd_enrollment_id out nocopy number,
45 p_object_version_number out nocopy number
46 ) is
47 --
48 -- Declare cursors and local variables
49 --
50 l_proc varchar2(72) := g_package||' create_cert_prd_enrollment';
51 l_cert_prd_enrollment_id number;
52 l_object_version_number number;
53 l_effective_date date;
54
55 begin
56 hr_utility.set_location('Entering:'|| l_proc, 10);
57 --
58 -- Issue a savepoint
59 --
60 savepoint CREATE_cert_prd_enrollment;
61 l_effective_date := trunc(p_effective_date);
62
63
64 begin
65 OTA_CERT_PRD_ENROLLMENT_bk1.create_cert_prd_enrollment_b
66 ( p_effective_date => p_effective_date
67 ,p_cert_enrollment_id => p_cert_enrollment_id
68 ,p_period_status_code => p_period_status_code
69 ,p_completion_date => p_completion_date
70 ,p_cert_period_start_date => p_cert_period_start_date
71 ,p_cert_period_end_date => p_cert_period_end_date
72 ,p_business_group_id => p_business_group_id
73 ,p_attribute_category => p_attribute_category
74 ,p_attribute1 => p_attribute1
75 ,p_attribute2 => p_attribute2
76 ,p_attribute3 => p_attribute3
77 ,p_attribute4 => p_attribute4
78 ,p_attribute5 => p_attribute5
79 ,p_attribute6 => p_attribute6
80 ,p_attribute7 => p_attribute7
81 ,p_attribute8 => p_attribute8
82 ,p_attribute9 => p_attribute9
83 ,p_attribute10 => p_attribute10
84 ,p_attribute11 => p_attribute11
85 ,p_attribute12 => p_attribute12
86 ,p_attribute13 => p_attribute13
87 ,p_attribute14 => p_attribute14
88 ,p_attribute15 => p_attribute15
89 ,p_attribute16 => p_attribute16
90 ,p_attribute17 => p_attribute17
91 ,p_attribute18 => p_attribute18
92 ,p_attribute19 => p_attribute19
93 ,p_attribute20 => p_attribute20
94 ,p_expiration_date => p_expiration_date
95 );
96 exception
97 when hr_api.cannot_find_prog_unit then
98 hr_api.cannot_find_prog_unit_error
99 (p_module_name => 'CREATE_cert_prd_enrollment'
100 ,p_hook_type => 'BP'
101 );
102 end;
103
104 --
105 -- Process Logic
106 --
107 ota_cpe_ins.ins
108 (
109 p_effective_date => p_effective_date
110 ,p_cert_enrollment_id => p_cert_enrollment_id
111 ,p_period_status_code => p_period_status_code
112 ,p_cert_period_start_date => p_cert_period_start_date
113 ,p_cert_period_end_date => p_cert_period_end_date
114 ,p_completion_date => p_completion_date
115 ,p_business_group_id => p_business_group_id
116 ,p_attribute_category => p_attribute_category
117 ,p_attribute1 => p_attribute1
118 ,p_attribute2 => p_attribute2
119 ,p_attribute3 => p_attribute3
120 ,p_attribute4 => p_attribute4
121 ,p_attribute5 => p_attribute5
122 ,p_attribute6 => p_attribute6
123 ,p_attribute7 => p_attribute7
124 ,p_attribute8 => p_attribute8
125 ,p_attribute9 => p_attribute9
126 ,p_attribute10 => p_attribute10
127 ,p_attribute11 => p_attribute11
128 ,p_attribute12 => p_attribute12
129 ,p_attribute13 => p_attribute13
130 ,p_attribute14 => p_attribute14
131 ,p_attribute15 => p_attribute15
132 ,p_attribute16 => p_attribute16
133 ,p_attribute17 => p_attribute17
134 ,p_attribute18 => p_attribute18
135 ,p_attribute19 => p_attribute19
136 ,p_attribute20 => p_attribute20
137 ,p_expiration_date => p_expiration_date
138 ,p_cert_prd_enrollment_id => l_cert_prd_enrollment_id
139 ,p_object_version_number => l_object_version_number
140 );
141 --
142 -- Set all output arguments
143 --
144 p_cert_prd_enrollment_id := l_cert_prd_enrollment_id;
145 p_object_version_number := l_object_version_number;
146
147
148
149 begin
150 OTA_CERT_PRD_ENROLLMENT_bk1.create_cert_prd_enrollment_a
151 ( p_effective_date => p_effective_date
152 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
153 ,p_cert_enrollment_id => p_cert_enrollment_id
154 ,p_period_status_code => p_period_status_code
155 ,p_completion_date => p_completion_date
156 ,p_cert_period_start_date => p_cert_period_start_date
157 ,p_cert_period_end_date => p_cert_period_end_date
158 ,p_business_group_id => p_business_group_id
159 ,p_attribute_category => p_attribute_category
160 ,p_attribute1 => p_attribute1
161 ,p_attribute2 => p_attribute2
162 ,p_attribute3 => p_attribute3
163 ,p_attribute4 => p_attribute4
164 ,p_attribute5 => p_attribute5
165 ,p_attribute6 => p_attribute6
166 ,p_attribute7 => p_attribute7
167 ,p_attribute8 => p_attribute8
168 ,p_attribute9 => p_attribute9
169 ,p_attribute10 => p_attribute10
170 ,p_attribute11 => p_attribute11
171 ,p_attribute12 => p_attribute12
172 ,p_attribute13 => p_attribute13
173 ,p_attribute14 => p_attribute14
174 ,p_attribute15 => p_attribute15
175 ,p_attribute16 => p_attribute16
176 ,p_attribute17 => p_attribute17
177 ,p_attribute18 => p_attribute18
178 ,p_attribute19 => p_attribute19
179 ,p_attribute20 => p_attribute20
180 ,p_expiration_date => p_expiration_date
181 );
182
183 exception
184 when hr_api.cannot_find_prog_unit then
185 hr_api.cannot_find_prog_unit_error
186 (p_module_name => 'CREATE_cert_prd_enrollment'
187 ,p_hook_type => 'AP'
188 );
189 end;
190
191 --
192 -- When in validation only mode raise the Validate_Enabled exception
193 --
194 if p_validate then
195 raise hr_api.validate_enabled;
196 end if;
197
198 hr_utility.set_location(' Leaving:'||l_proc, 70);
199 exception
200 when hr_api.validate_enabled then
201 --
202 -- As the Validate_Enabled exception has been raised
203 -- we must rollback to the savepoint
204 --
205 rollback to CREATE_cert_prd_enrollment;
206 --
207 -- Only set output warning arguments
208 -- (Any key or derived arguments must be set to null
209 -- when validation only mode is being used.)
210 --
211 p_cert_prd_enrollment_id := null;
212 p_object_version_number := null;
213 hr_utility.set_location(' Leaving:'||l_proc, 80);
214 when others then
215 --
216 -- A validation or unexpected error has occured
217 --
218 rollback to CREATE_cert_prd_enrollment;
219 p_cert_prd_enrollment_id := null;
220 p_object_version_number := null;
221 hr_utility.set_location(' Leaving:'||l_proc, 90);
222 raise;
223 end create_cert_prd_enrollment;
224 -- ----------------------------------------------------------------------------
225 -- |-------------------------< UPDATE_cert_prd_enrollment >-------------------------|
226 -- ----------------------------------------------------------------------------
227 --
228 procedure update_cert_prd_enrollment
229 (p_effective_date in date
230 ,p_cert_prd_enrollment_id in number
231 ,p_object_version_number in out nocopy number
232 ,p_cert_enrollment_id in number
233 ,p_period_status_code in varchar2
234 ,p_completion_date in date default hr_api.g_date
235 ,p_cert_period_start_date in date default hr_api.g_date
236 ,p_cert_period_end_date in date default hr_api.g_date
237 ,p_business_group_id in number default hr_api.g_number
238 ,p_attribute_category in varchar2 default hr_api.g_varchar2
239 ,p_attribute1 in varchar2 default hr_api.g_varchar2
240 ,p_attribute2 in varchar2 default hr_api.g_varchar2
241 ,p_attribute3 in varchar2 default hr_api.g_varchar2
242 ,p_attribute4 in varchar2 default hr_api.g_varchar2
243 ,p_attribute5 in varchar2 default hr_api.g_varchar2
244 ,p_attribute6 in varchar2 default hr_api.g_varchar2
245 ,p_attribute7 in varchar2 default hr_api.g_varchar2
246 ,p_attribute8 in varchar2 default hr_api.g_varchar2
247 ,p_attribute9 in varchar2 default hr_api.g_varchar2
248 ,p_attribute10 in varchar2 default hr_api.g_varchar2
249 ,p_attribute11 in varchar2 default hr_api.g_varchar2
250 ,p_attribute12 in varchar2 default hr_api.g_varchar2
251 ,p_attribute13 in varchar2 default hr_api.g_varchar2
252 ,p_attribute14 in varchar2 default hr_api.g_varchar2
253 ,p_attribute15 in varchar2 default hr_api.g_varchar2
254 ,p_attribute16 in varchar2 default hr_api.g_varchar2
255 ,p_attribute17 in varchar2 default hr_api.g_varchar2
256 ,p_attribute18 in varchar2 default hr_api.g_varchar2
257 ,p_attribute19 in varchar2 default hr_api.g_varchar2
258 ,p_attribute20 in varchar2 default hr_api.g_varchar2
259 ,p_expiration_date in date default hr_api.g_date
260 ,p_validate in boolean default false
261 ) is
262 --
263 -- Declare cursors and local variables
264 --
265 CURSOR csr_crt IS
266 select
267 b.certification_id certification_id
268 , b.INITIAL_COMPLETION_DATE
269 , b.INITIAL_COMPLETION_DURATION
270 , b.INITIAL_COMPL_DURATION_UNITS
271 , b.RENEWAL_DURATION
272 , b.RENEWAL_DURATION_UNITS
273 , b.NOTIFY_DAYS_BEFORE_EXPIRE
274 , b.VALIDITY_DURATION
275 , b.VALIDITY_DURATION_UNITS
276 , b.RENEWABLE_FLAG
277 , b.VALIDITY_START_TYPE
278 , b.PUBLIC_FLAG
279 , b.START_DATE_ACTIVE
280 , b.END_DATE_ACTIVE
281 , cre.earliest_enroll_date
282 , cre.expiration_date
283 from ota_certifications_b b,
284 ota_cert_enrollments cre
285 where b.certification_id = cre.certification_id
286 and cre.cert_enrollment_id = p_cert_enrollment_id;
287
288 CURSOR csr_cert_enrl IS
289 select cert_enrollment_id,
290 certification_id,
291 certification_status_code,
292 object_version_number,
293 completion_date,
294 is_history_flag,
295 person_id,
296 earliest_enroll_date
297 FROM ota_cert_enrollments
298 where cert_enrollment_id = p_cert_enrollment_id;
299
300 CURSOR csr_max_cpe_exp_dt IS
301 select
302 max(cpe.expiration_date)
303 from ota_cert_prd_enrollments cpe,
304 ota_cert_enrollments cre
305 where cpe.cert_enrollment_id = cre.cert_enrollment_id
306 and cre.cert_enrollment_id = p_cert_enrollment_id;
307
308 CURSOR csr_old_cpe_exp_dt IS
309 select
310 cpe.expiration_date
311 from ota_cert_prd_enrollments cpe
312 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id;
313
314 l_proc varchar2(72) := g_package||' update_cert_prd_enrollment';
315 l_object_version_number number := p_object_version_number;
316 l_effective_date date;
317 l_item_key wf_items.item_key%type;
318
319 rec_crt csr_crt%rowtype;
320 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
321
322 l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
323 l_expiration_date ota_cert_enrollments.expiration_date%type;
324 l_max_expiration_date date;
325 l_update_cre_dates_flag varchar2(1) := 'N';
326 l_cert_period_start_date date :=p_cert_period_start_date;
327 l_cert_period_end_date date :=p_cert_period_end_date;
328
329
330 begin
331 hr_utility.set_location('Entering:'|| l_proc, 10);
332 --
333 -- Issue a savepoint
334 --
335 savepoint UPDATE_cert_prd_enrollment;
336 l_effective_date := trunc(p_effective_date);
337
338 --initialize l_expiration_date with passed p_expiration_date
339 if p_expiration_date is not null then
340 l_expiration_date := p_expiration_date;
341 else
342 --get old value into l_expiration_date
343 open csr_old_cpe_exp_dt;
344 fetch csr_old_cpe_exp_dt into l_expiration_date;
345 close csr_old_cpe_exp_dt;
346 end if;
347
348 OPEN csr_crt;
349 FETCH csr_crt INTO rec_crt;
350 CLOSE csr_crt;
351
352 if (rec_crt.RENEWABLE_FLAG = 'Y' and p_period_status_code = 'COMPLETED') then
353 --update cre dates
354 --recalc exp date, and earliest enroll dates for next period
355 OPEN csr_max_cpe_exp_dt;
356 FETCH csr_max_cpe_exp_dt INTO l_max_expiration_date;
357 CLOSE csr_max_cpe_exp_dt;
358
359 OPEN csr_cert_enrl;
360 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
361 CLOSE csr_cert_enrl;
362
363 if rec_crt.INITIAL_COMPLETION_DURATION is not null then
364 --populate exp date
365 if rec_crt.VALIDITY_START_TYPE = 'T' then
366 --get the max exp date for cre
367 l_expiration_date := l_max_expiration_date;
368 elsif (rec_crt.VALIDITY_START_TYPE = 'A') then
369 if(p_completion_date is not null) then
370 l_expiration_date := p_completion_date + rec_crt.validity_duration;
371 else
372 l_expiration_date := trunc(sysdate) + rec_crt.validity_duration;
373 end if;
374 end if;
375
376 /*
377 validity start type = T
378 - renewal_duration null means, renewal from actual compl
379 - renewal_duration same as validity_duration means, renewal from due date
380
381 validity start type = A
382 - renewal_duration null means, renewal from actual compl
383 - renewal_duration same as validity_duration means, renewal from due date
384 */
385
386 --populate earliest_enrollment_date
387 if rec_crt.renewal_duration is not null then
388 if (rec_crt.validity_duration = rec_crt.renewal_duration) then
389 --renew from due date
390 --get the existing earl date and upd same
391 l_earliest_enroll_date := l_cert_enrl_rec.earliest_enroll_date;
392 else
393 l_earliest_enroll_date := l_expiration_date - rec_crt.renewal_duration;
394 end if;
395 else
396 --earl enr dt imm after compl
397 if(p_completion_date is not null) then
398 l_earliest_enroll_date := p_completion_date;
399 else
400 l_earliest_enroll_date := trunc(sysdate);
401 end if;
402 end if;
403
404 l_update_cre_dates_flag := 'Y';
405
406 elsif rec_crt.INITIAL_COMPLETION_DATE is not null then
407 --populate exp date at cre
408 if rec_crt.VALIDITY_START_TYPE = 'T' then
409 --get the max exp date for cre
410 l_expiration_date := l_max_expiration_date;
411 end if;
412
413 --get the existing earl date and upd same
414 l_earliest_enroll_date := l_cert_enrl_rec.earliest_enroll_date;
415 l_update_cre_dates_flag := 'Y';
416 end if;
417
418
419 --update cre rec for any modified dates
420 if (l_update_cre_dates_flag = 'Y') then
421 ota_cert_enrollment_api.update_cert_enrollment
422 (p_effective_date => sysdate
423 ,p_cert_enrollment_id => p_cert_enrollment_id
424 ,p_certification_id => rec_crt.certification_id
425 ,p_object_version_number => l_cert_enrl_rec.object_version_number
426 ,p_certification_status_code => l_cert_enrl_rec.certification_status_code
427 ,p_is_history_flag => l_cert_enrl_rec.is_history_flag
428 ,p_completion_date => p_completion_date
429 ,p_expiration_date => l_expiration_date
430 ,p_earliest_enroll_date => l_earliest_enroll_date
431 );
432 end if;
433 end if; --end of RENEWAL COMPLETE
434
435
436 begin
437 OTA_CERT_PRD_ENROLLMENT_bk2.update_cert_prd_enrollment_b
438 ( p_effective_date => p_effective_date
439 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
440 ,p_object_version_number => p_object_version_number
441 ,p_cert_enrollment_id => p_cert_enrollment_id
442 ,p_period_status_code => p_period_status_code
443 ,p_completion_date => p_completion_date
444 ,p_cert_period_start_date => p_cert_period_start_date
445 ,p_cert_period_end_date => p_cert_period_end_date
446 ,p_business_group_id => p_business_group_id
447 ,p_attribute_category => p_attribute_category
448 ,p_attribute1 => p_attribute1
449 ,p_attribute2 => p_attribute2
450 ,p_attribute3 => p_attribute3
451 ,p_attribute4 => p_attribute4
452 ,p_attribute5 => p_attribute5
453 ,p_attribute6 => p_attribute6
454 ,p_attribute7 => p_attribute7
455 ,p_attribute8 => p_attribute8
456 ,p_attribute9 => p_attribute9
457 ,p_attribute10 => p_attribute10
458 ,p_attribute11 => p_attribute11
459 ,p_attribute12 => p_attribute12
460 ,p_attribute13 => p_attribute13
461 ,p_attribute14 => p_attribute14
462 ,p_attribute15 => p_attribute15
463 ,p_attribute16 => p_attribute16
464 ,p_attribute17 => p_attribute17
465 ,p_attribute18 => p_attribute18
466 ,p_attribute19 => p_attribute19
467 ,p_attribute20 => p_attribute20
468 ,p_expiration_date => p_expiration_date
469 );
470
471 exception
472 when hr_api.cannot_find_prog_unit then
473 hr_api.cannot_find_prog_unit_error
474 (p_module_name => 'UPDATE_cert_prd_enrollment'
475 ,p_hook_type => 'BP'
476 );
477 end;
478
479
480 --
481 -- Process Logic
482 --
483
484 ota_cpe_upd.upd
485 (
486 p_effective_date => p_effective_date
487 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
488 ,p_object_version_number => l_object_version_number
489 ,p_cert_enrollment_id => p_cert_enrollment_id
490 ,p_period_status_code => p_period_status_code
491 ,p_cert_period_start_date => p_cert_period_start_date
492 ,p_cert_period_end_date => p_cert_period_end_date
493 ,p_completion_date => p_completion_date
494 ,p_business_group_id => p_business_group_id
495 ,p_attribute_category => p_attribute_category
496 ,p_attribute1 => p_attribute1
497 ,p_attribute2 => p_attribute2
498 ,p_attribute3 => p_attribute3
499 ,p_attribute4 => p_attribute4
500 ,p_attribute5 => p_attribute5
501 ,p_attribute6 => p_attribute6
502 ,p_attribute7 => p_attribute7
503 ,p_attribute8 => p_attribute8
504 ,p_attribute9 => p_attribute9
505 ,p_attribute10 => p_attribute10
506 ,p_attribute11 => p_attribute11
507 ,p_attribute12 => p_attribute12
508 ,p_attribute13 => p_attribute13
509 ,p_attribute14 => p_attribute14
510 ,p_attribute15 => p_attribute15
511 ,p_attribute16 => p_attribute16
512 ,p_attribute17 => p_attribute17
513 ,p_attribute18 => p_attribute18
514 ,p_attribute19 => p_attribute19
515 ,p_attribute20 => p_attribute20
516 --expiration_date would be re-calculated for COMPL status
517 ,p_expiration_date => l_expiration_date
518 );
519
520 begin
521 OTA_CERT_PRD_ENROLLMENT_bk2.update_cert_prd_enrollment_a
522 ( p_effective_date => p_effective_date
523 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
524 ,p_object_version_number => p_object_version_number
525 ,p_cert_enrollment_id => p_cert_enrollment_id
526 ,p_period_status_code => p_period_status_code
527 ,p_completion_date => p_completion_date
528 ,p_cert_period_start_date => p_cert_period_start_date
529 ,p_cert_period_end_date => p_cert_period_end_date
530 ,p_business_group_id => p_business_group_id
531 ,p_attribute_category => p_attribute_category
532 ,p_attribute1 => p_attribute1
533 ,p_attribute2 => p_attribute2
534 ,p_attribute3 => p_attribute3
535 ,p_attribute4 => p_attribute4
536 ,p_attribute5 => p_attribute5
537 ,p_attribute6 => p_attribute6
538 ,p_attribute7 => p_attribute7
539 ,p_attribute8 => p_attribute8
540 ,p_attribute9 => p_attribute9
541 ,p_attribute10 => p_attribute10
542 ,p_attribute11 => p_attribute11
543 ,p_attribute12 => p_attribute12
544 ,p_attribute13 => p_attribute13
545 ,p_attribute14 => p_attribute14
546 ,p_attribute15 => p_attribute15
547 ,p_attribute16 => p_attribute16
548 ,p_attribute17 => p_attribute17
549 ,p_attribute18 => p_attribute18
550 ,p_attribute19 => p_attribute19
551 ,p_attribute20 => p_attribute20
552 ,p_expiration_date => p_expiration_date
553 );
554
555 exception
556 when hr_api.cannot_find_prog_unit then
557 hr_api.cannot_find_prog_unit_error
558 (p_module_name => 'UPDATE_cert_prd_enrollment'
559 ,p_hook_type => 'AP'
560 );
561 end;
562
563
564 --
565 -- When in validation only mode raise the Validate_Enabled exception
566 --
567 if p_validate then
568 raise hr_api.validate_enabled;
569 end if;
570
571 --fire competency update/CERT completion notifications
572 OPEN csr_cert_enrl;
573 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
574 CLOSE csr_cert_enrl;
575
576 if p_period_status_code = 'COMPLETED' and l_cert_enrl_rec.person_id is not null then
577
578 OTA_INITIALIZATION_WF.initialize_cert_ntf_wf(p_item_type => 'OTWF',
579 p_person_id => l_cert_enrl_rec.person_id,
580 p_certification_id => l_cert_enrl_rec.certification_id,
581 p_cert_prd_enrollment_id => p_cert_prd_enrollment_id,
582 p_cert_ntf_type => 'CERT_COMPLETION');
583
584 If (p_cert_period_start_date = hr_api.g_date) then
585 l_cert_period_start_date :=
586 ota_cpe_shd.g_old_rec.cert_period_start_date;
587 End If;
588 If (p_cert_period_end_date = hr_api.g_date) then
589 l_cert_period_end_date :=
590 ota_cpe_shd.g_old_rec.cert_period_end_date;
591 End If;
592
593 if ('Y' = ota_cpe_util.is_cert_success_complete(p_cert_prd_enrollment_id => p_cert_prd_enrollment_id,
594 p_cert_period_start_date => l_cert_period_start_date
595 ,p_cert_period_end_date => l_cert_period_end_date,
596 p_person_id => l_cert_enrl_rec.person_id)) then
597 ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
598 p_itemtype =>'HRSSA',
599 p_person_id => l_cert_enrl_rec.person_id,
600 p_eventid =>null,
601 p_learningpath_ids => null,
602 p_certification_id => l_cert_enrl_rec.certification_id ,
603 p_itemkey =>l_item_key);
604
605 end if;
606
607 end if;
608
609 if p_period_status_code = 'CANCELLED' and l_cert_enrl_rec.person_id is not null then
610
611 OTA_INITIALIZATION_WF.initialize_cert_ntf_wf(p_item_type => 'OTWF',
612 p_person_id => l_cert_enrl_rec.person_id ,
613 p_certification_id => l_cert_enrl_rec.certification_id,
614 p_cert_prd_enrollment_id => p_cert_prd_enrollment_id,
615 p_cert_ntf_type => 'CERT_UNENROLL');
616
617
618 end if;
619
620 --
621 -- Set all output arguments
622 --
623 p_object_version_number := l_object_version_number;
624
625 hr_utility.set_location(' Leaving:'||l_proc, 70);
626 exception
627 when hr_api.validate_enabled then
628 --
629 -- As the Validate_Enabled exception has been raised
630 -- we must rollback to the savepoint
631 --
632 rollback to UPDATE_cert_prd_enrollment;
633 --
634 -- Only set output warning arguments
635 -- (Any key or derived arguments must be set to null
636 -- when validation only mode is being used.)
637 --
638 p_object_version_number := null;
639 hr_utility.set_location(' Leaving:'||l_proc, 80);
640 when others then
641 --
642 -- A validation or unexpected error has occured
643 --
644 rollback to UPDATE_cert_prd_enrollment;
645 p_object_version_number := l_object_version_number;
646 hr_utility.set_location(' Leaving:'||l_proc, 90);
647 raise;
648 end update_cert_prd_enrollment;
649 --
650 -- ----------------------------------------------------------------------------
651 -- |-------------------------< DELETE_cert_prd_enrollment >-------------------|
652 -- ----------------------------------------------------------------------------
653 --
654 procedure delete_cert_prd_enrollment
655 (p_cert_prd_enrollment_id in number
656 ,p_object_version_number in number
657 ,p_validate in boolean default false
658
659 ) is
660 --
661 -- Declare cursors and local variables
662 --
663 l_proc varchar2(72) := g_package||'DELETE_cert_prd_enrollment';
664 --
665 --
666 begin
667 hr_utility.set_location('Entering:'|| l_proc, 10);
668 --
669 -- Issue a savepoint
670 --
671 savepoint DELETE_cert_prd_enrollment;
672 --
673 -- Truncate the time portion from all IN date parameters
674 --
675 --
676
677 begin
678 OTA_CERT_PRD_ENROLLMENT_bk3.delete_cert_prd_enrollment_b
679 (p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
680 ,p_object_version_number => p_object_version_number
681 );
682 exception
683 when hr_api.cannot_find_prog_unit then
684 hr_api.cannot_find_prog_unit_error
685 (p_module_name => 'DELETE_cert_prd_enrollment'
686 ,p_hook_type => 'BP'
687 );
688 end;
689
690 --
691 -- Process Logic
692 --
693
694 ota_cpe_del.del
695 (
696 p_cert_prd_enrollment_id => p_cert_prd_enrollment_id ,
697 p_object_version_number => p_object_version_number
698 );
699
700
701 begin
702 OTA_CERT_PRD_ENROLLMENT_bk3.delete_cert_prd_enrollment_a
703 (p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
704 ,p_object_version_number => p_object_version_number
705 );
706 exception
707 when hr_api.cannot_find_prog_unit then
708 hr_api.cannot_find_prog_unit_error
709 (p_module_name => 'DELETE_cert_prd_enrollment'
710 ,p_hook_type => 'AP'
711 );
712 end;
713
714 --
715 -- When in validation only mode raise the Validate_Enabled exception
716 --
717 if p_validate then
718 raise hr_api.validate_enabled;
719 end if;
720 --
721 -- Set all output arguments
722 --
723 --
724 hr_utility.set_location(' Leaving:'||l_proc, 170);
725 exception
726 when hr_api.validate_enabled then
727 --
728 -- As the Validate_Enabled exception has been raised
729 -- we must rollback to the savepoint
730 --
731 rollback to DELETE_cert_prd_enrollment;
732 --
733 -- Only set output warning arguments
734 -- (Any key or derived arguments must be set to null
735 -- when validation only mode is being used.)
736 --
737 hr_utility.set_location(' Leaving:'||l_proc, 180);
738 when others then
739 --
740 -- A validation or unexpected error has occured
741 --
742 rollback to DELETE_cert_prd_enrollment;
743 hr_utility.set_location(' Leaving:'||l_proc, 190);
744 raise;
745 end delete_cert_prd_enrollment;
746
747 -- ----------------------------------------------------------------------------
748 -- |-------------------------< renew_cert_prd_enrollment >-------------------|
749 -- ----------------------------------------------------------------------------
750 procedure renew_cert_prd_enrollment(p_validate in boolean default false
751 ,p_cert_enrollment_id in number
752 ,p_cert_period_start_date in date default sysdate
753 ,p_cert_prd_enrollment_id OUT NOCOPY number
754 ,p_certification_status_code OUT NOCOPY VARCHAR2)
755 is
756
757 CURSOR csr_crt IS
758 select
759 b.certification_id certification_id
760 , b.INITIAL_COMPLETION_DATE
761 , b.INITIAL_COMPLETION_DURATION
762 , b.INITIAL_COMPL_DURATION_UNITS
763 , b.RENEWAL_DURATION
764 , b.RENEWAL_DURATION_UNITS
765 , b.NOTIFY_DAYS_BEFORE_EXPIRE
766 , b.VALIDITY_DURATION
767 , b.VALIDITY_DURATION_UNITS
768 , b.RENEWABLE_FLAG
769 , b.VALIDITY_START_TYPE
770 , b.PUBLIC_FLAG
771 , b.START_DATE_ACTIVE
772 , b.END_DATE_ACTIVE
773 from ota_certifications_b b,
774 ota_cert_enrollments cre
775 where cre.certification_id = b.certification_id
776 and cre.cert_enrollment_id = p_cert_enrollment_id;
777
778
779 CURSOR csr_cert_enrl IS
780 select certification_id,
781 cert_enrollment_id,
782 business_group_id,
783 certification_status_code,
784 object_version_number,
785 completion_date
786 FROM ota_cert_enrollments
787 where cert_enrollment_id = p_cert_enrollment_id;
788
789 l_proc varchar2(72) := g_package || ' renew_cert_prd_enrollment';
790
791 rec_crt csr_crt%rowtype;
792 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
793
794 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type;
795 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
796
797 p_effective_date DATE;
798 p_business_group_id DATE;
799
800 l_certification_status_code VARCHAR2(30);
801
802 l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
803 l_expiration_date ota_cert_enrollments.expiration_date%type;
804
805 p_expiration_date date;
806
807
808 BEGIN
809
810 hr_multi_message.enable_message_list;
811 savepoint renew_cert_prd_enrollment_api;
812
813 OPEN csr_crt;
814 FETCH csr_crt INTO rec_crt;
815 CLOSE csr_crt;
816
817 if (rec_crt.renewable_flag = 'Y') then
818 ota_cpe_util.calc_cre_dates(p_cert_enrollment_id, rec_crt.certification_id, null, l_earliest_enroll_date, l_expiration_date, p_cert_period_start_date);
819 end if; --end renewal flag
820
821 ota_cpe_util.create_cpe_rec(p_cert_enrollment_id => p_cert_enrollment_id,
822 p_expiration_date => l_expiration_date,
823 p_cert_period_start_date => p_cert_period_start_date,
824 p_cert_prd_enrollment_id => l_cert_prd_enrollment_id,
825 p_certification_status_code => l_certification_status_code);
826
827
828 OPEN csr_cert_enrl;
829 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
830 CLOSE csr_cert_enrl;
831
832 ota_cert_enrollment_api.update_cert_enrollment
833 (p_effective_date => trunc(sysdate)
834 ,p_cert_enrollment_id => p_cert_enrollment_id
835 ,p_certification_id => l_cert_enrl_rec.certification_id
836 ,p_object_version_number => l_cert_enrl_rec.object_version_number
837 ,p_certification_status_code => l_cert_enrl_rec.certification_status_code
838 ,p_is_history_flag => 'N'
839 ,p_earliest_enroll_date => l_earliest_enroll_date
840 );
841
842 --set output params
843 p_cert_prd_enrollment_id := l_cert_prd_enrollment_id;
844 p_certification_status_code := l_certification_status_code;
845
846
847 if p_validate then
848 raise hr_api.validate_enabled;
849 end if;
850
851 exception
852 when hr_api.validate_enabled then
853 --
854 --
855 -- Catch the Multiple Message List exception which
856 -- indicates API processing has been aborted because
857 -- at least one message exists in the list.
858 --
859 rollback to renew_cert_prd_enrollment_api;
860 --
861 -- Reset IN OUT parameters and set OUT parameters
862 --
863 p_cert_prd_enrollment_id := null;
864 p_certification_status_code := null;
865 hr_utility.set_location(' Leaving:' || l_proc, 30);
866
867 when others then
868 --
869 -- A validation or unexpected error has occured
870 --
871 rollback to renew_cert_prd_enrollment_api;
872 --
873 -- Reset IN OUT and set OUT parameters
874 --
875 p_cert_prd_enrollment_id := null;
876 p_certification_status_code := null;
877
878 hr_utility.set_location(' Leaving:' || l_proc,50);
879 raise;
880 END renew_cert_prd_enrollment;
881 --
882 end OTA_CERT_PRD_ENROLLMENT_api;