[Home] [Help]
PACKAGE BODY: APPS.OTA_CERT_ENROLLMENT_API
Source
1 Package Body OTA_CERT_ENROLLMENT_API as
2 /* $Header: otcreapi.pkb 120.17.12010000.2 2008/09/22 11:23:12 pekasi ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_CERT_ENROLLMENT_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_cert_enrollment >------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_cert_enrollment
13 (
14 p_effective_date in date,
15 p_validate in boolean default false ,
16 p_certification_id in number,
17 p_person_id in number default null,
18 p_contact_id in number default null,
19 p_certification_status_code in varchar2,
20 p_completion_date in date default null,
21 p_UNENROLLMENT_DATE in date default null,
22 p_EXPIRATION_DATE in date default null,
23 p_EARLIEST_ENROLL_DATE in date default null,
24 p_IS_HISTORY_FLAG in varchar2 default 'N',
25 p_business_group_id in number,
26 p_attribute_category in varchar2 default null,
27 p_attribute1 in varchar2 default null,
28 p_attribute2 in varchar2 default null,
29 p_attribute3 in varchar2 default null,
30 p_attribute4 in varchar2 default null,
31 p_attribute5 in varchar2 default null,
32 p_attribute6 in varchar2 default null,
33 p_attribute7 in varchar2 default null,
34 p_attribute8 in varchar2 default null,
35 p_attribute9 in varchar2 default null,
36 p_attribute10 in varchar2 default null,
37 p_attribute11 in varchar2 default null,
38 p_attribute12 in varchar2 default null,
39 p_attribute13 in varchar2 default null,
40 p_attribute14 in varchar2 default null,
41 p_attribute15 in varchar2 default null,
42 p_attribute16 in varchar2 default null,
43 p_attribute17 in varchar2 default null,
44 p_attribute18 in varchar2 default null,
45 p_attribute19 in varchar2 default null,
46 p_attribute20 in varchar2 default null,
47 p_enrollment_date in date default null,
48 p_cert_enrollment_id out nocopy number,
49 p_object_version_number out nocopy number
50 ) is
51 --
52 -- Declare cursors and local variables
53 --
54 l_proc varchar2(72) := g_package||' create_cert_enrollment';
55 l_cert_enrollment_id number;
56 l_object_version_number number;
57 l_effective_date date;
58
59 begin
60 hr_utility.set_location('Entering:'|| l_proc, 10);
61 --
62 -- Issue a savepoint
63 --
64 savepoint CREATE_cert_enrollment;
65 l_effective_date := trunc(p_effective_date);
66
67
68 begin
69 OTA_CERT_ENROLLMENT_bk1.create_cert_enrollment_b
70 ( p_effective_date => p_effective_date
71 ,p_certification_id => p_certification_id
72 ,p_person_id => p_person_id
73 ,p_contact_id => p_contact_id
74 ,p_certification_status_code => p_certification_status_code
75 ,p_completion_date => p_completion_date
76 ,p_UNENROLLMENT_DATE => p_UNENROLLMENT_DATE
77 ,p_EXPIRATION_DATE => p_EXPIRATION_DATE
78 ,p_EARLIEST_ENROLL_DATE => p_EARLIEST_ENROLL_DATE
79 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
80 ,p_business_group_id => p_business_group_id
81 ,p_attribute_category => p_attribute_category
82 ,p_attribute1 => p_attribute1
83 ,p_attribute2 => p_attribute2
84 ,p_attribute3 => p_attribute3
85 ,p_attribute4 => p_attribute4
86 ,p_attribute5 => p_attribute5
87 ,p_attribute6 => p_attribute6
88 ,p_attribute7 => p_attribute7
89 ,p_attribute8 => p_attribute8
90 ,p_attribute9 => p_attribute9
91 ,p_attribute10 => p_attribute10
92 ,p_attribute11 => p_attribute11
93 ,p_attribute12 => p_attribute12
94 ,p_attribute13 => p_attribute13
95 ,p_attribute14 => p_attribute14
96 ,p_attribute15 => p_attribute15
97 ,p_attribute16 => p_attribute16
98 ,p_attribute17 => p_attribute17
99 ,p_attribute18 => p_attribute18
100 ,p_attribute19 => p_attribute19
101 ,p_attribute20 => p_attribute20
102 ,p_enrollment_date => p_enrollment_date
103 );
104 exception
105 when hr_api.cannot_find_prog_unit then
106 hr_api.cannot_find_prog_unit_error
107 (p_module_name => 'CREATE_cert_enrollment'
108 ,p_hook_type => 'BP'
109 );
110 end;
111
112 --
113 -- Process Logic
114 --
115 ota_cre_ins.ins
116 (
117 p_effective_date => p_effective_date
118 ,p_certification_id => p_certification_id
119 ,p_certification_status_code => p_certification_status_code
120 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
121 ,p_person_id => p_person_id
122 ,p_contact_id => p_contact_id
123 ,p_completion_date => p_completion_date
124 ,p_business_group_id => p_business_group_id
125 ,p_UNENROLLMENT_DATE => p_UNENROLLMENT_DATE
126 ,p_EXPIRATION_DATE => p_EXPIRATION_DATE
127 ,p_EARLIEST_ENROLL_DATE => p_EARLIEST_ENROLL_DATE
128 ,p_attribute_category => p_attribute_category
129 ,p_attribute1 => p_attribute1
130 ,p_attribute2 => p_attribute2
131 ,p_attribute3 => p_attribute3
132 ,p_attribute4 => p_attribute4
133 ,p_attribute5 => p_attribute5
134 ,p_attribute6 => p_attribute6
135 ,p_attribute7 => p_attribute7
136 ,p_attribute8 => p_attribute8
137 ,p_attribute9 => p_attribute9
138 ,p_attribute10 => p_attribute10
139 ,p_attribute11 => p_attribute11
140 ,p_attribute12 => p_attribute12
141 ,p_attribute13 => p_attribute13
142 ,p_attribute14 => p_attribute14
143 ,p_attribute15 => p_attribute15
144 ,p_attribute16 => p_attribute16
145 ,p_attribute17 => p_attribute17
146 ,p_attribute18 => p_attribute18
147 ,p_attribute19 => p_attribute19
148 ,p_attribute20 => p_attribute20
149 ,p_enrollment_date => p_enrollment_date
150 ,p_cert_enrollment_id => l_cert_enrollment_id
151 ,p_object_version_number => l_object_version_number
152 );
153 --
154 -- Set all output arguments
155 --
156 p_cert_enrollment_id := l_cert_enrollment_id;
157 p_object_version_number := l_object_version_number;
158
159 begin
160 OTA_CERT_ENROLLMENT_bk1.create_cert_enrollment_a
161 ( p_effective_date => p_effective_date
162 ,p_cert_enrollment_id => p_cert_enrollment_id
163 ,p_certification_id => p_certification_id
164 ,p_person_id => p_person_id
165 ,p_contact_id => p_contact_id
166 ,p_certification_status_code => p_certification_status_code
167 ,p_completion_date => p_completion_date
168 ,p_UNENROLLMENT_DATE => p_UNENROLLMENT_DATE
169 ,p_EXPIRATION_DATE => p_EXPIRATION_DATE
170 ,p_EARLIEST_ENROLL_DATE => p_EARLIEST_ENROLL_DATE
171 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
172 ,p_business_group_id => p_business_group_id
173 ,p_attribute_category => p_attribute_category
174 ,p_attribute1 => p_attribute1
175 ,p_attribute2 => p_attribute2
176 ,p_attribute3 => p_attribute3
177 ,p_attribute4 => p_attribute4
178 ,p_attribute5 => p_attribute5
179 ,p_attribute6 => p_attribute6
180 ,p_attribute7 => p_attribute7
181 ,p_attribute8 => p_attribute8
182 ,p_attribute9 => p_attribute9
183 ,p_attribute10 => p_attribute10
184 ,p_attribute11 => p_attribute11
185 ,p_attribute12 => p_attribute12
186 ,p_attribute13 => p_attribute13
187 ,p_attribute14 => p_attribute14
188 ,p_attribute15 => p_attribute15
189 ,p_attribute16 => p_attribute16
190 ,p_attribute17 => p_attribute17
191 ,p_attribute18 => p_attribute18
192 ,p_attribute19 => p_attribute19
193 ,p_attribute20 => p_attribute20
194 ,p_enrollment_date => p_enrollment_date
195 );
196
197 exception
198 when hr_api.cannot_find_prog_unit then
199 hr_api.cannot_find_prog_unit_error
200 (p_module_name => 'CREATE_cert_enrollment'
201 ,p_hook_type => 'AP'
202 );
203 end;
204
205 --
206 -- When in validation only mode raise the Validate_Enabled exception
207 --
208 if p_validate then
209 raise hr_api.validate_enabled;
210 end if;
211
212 hr_utility.set_location(' Leaving:'||l_proc, 70);
213 exception
214 when hr_api.validate_enabled then
215 --
216 -- As the Validate_Enabled exception has been raised
217 -- we must rollback to the savepoint
218 --
219 rollback to CREATE_cert_enrollment;
220 --
221 -- Only set output warning arguments
222 -- (Any key or derived arguments must be set to null
223 -- when validation only mode is being used.)
224 --
225 p_cert_enrollment_id := null;
226 p_object_version_number := null;
227 hr_utility.set_location(' Leaving:'||l_proc, 80);
228 when others then
229 --
230 -- A validation or unexpected error has occured
231 --
232 rollback to CREATE_cert_enrollment;
233 p_cert_enrollment_id := null;
234 p_object_version_number := null;
235 hr_utility.set_location(' Leaving:'||l_proc, 90);
236 raise;
237 end create_cert_enrollment;
238 -- ----------------------------------------------------------------------------
239 -- |-------------------------< UPDATE_cert_enrollment >-------------------------|
240 -- ----------------------------------------------------------------------------
241 --
242 procedure update_cert_enrollment
243 (p_effective_date in date
244 ,p_cert_enrollment_id in number
245 ,p_object_version_number in out nocopy number
246 ,p_certification_id in number
247 ,p_person_id in number default hr_api.g_number
248 ,p_contact_id in number default hr_api.g_number
249 ,p_certification_status_code in varchar2 default hr_api.g_varchar2
250 ,p_completion_date in date default hr_api.g_date
251 ,p_unenrollment_date in date default hr_api.g_date
252 ,p_expiration_date in date default hr_api.g_date
256 ,p_attribute_category in varchar2 default hr_api.g_varchar2
253 ,p_earliest_enroll_date in date default hr_api.g_date
254 ,p_is_history_flag in varchar2 default hr_api.g_varchar2
255 ,p_business_group_id in number default hr_api.g_number
257 ,p_attribute1 in varchar2 default hr_api.g_varchar2
258 ,p_attribute2 in varchar2 default hr_api.g_varchar2
259 ,p_attribute3 in varchar2 default hr_api.g_varchar2
260 ,p_attribute4 in varchar2 default hr_api.g_varchar2
261 ,p_attribute5 in varchar2 default hr_api.g_varchar2
262 ,p_attribute6 in varchar2 default hr_api.g_varchar2
263 ,p_attribute7 in varchar2 default hr_api.g_varchar2
264 ,p_attribute8 in varchar2 default hr_api.g_varchar2
265 ,p_attribute9 in varchar2 default hr_api.g_varchar2
266 ,p_attribute10 in varchar2 default hr_api.g_varchar2
267 ,p_attribute11 in varchar2 default hr_api.g_varchar2
268 ,p_attribute12 in varchar2 default hr_api.g_varchar2
269 ,p_attribute13 in varchar2 default hr_api.g_varchar2
270 ,p_attribute14 in varchar2 default hr_api.g_varchar2
271 ,p_attribute15 in varchar2 default hr_api.g_varchar2
272 ,p_attribute16 in varchar2 default hr_api.g_varchar2
273 ,p_attribute17 in varchar2 default hr_api.g_varchar2
274 ,p_attribute18 in varchar2 default hr_api.g_varchar2
275 ,p_attribute19 in varchar2 default hr_api.g_varchar2
276 ,p_attribute20 in varchar2 default hr_api.g_varchar2
277 ,p_enrollment_date in date default hr_api.g_date
278 ,p_validate in boolean default false
279 ) is
280 --
281 -- Declare cursors and local variables
282 --
283 l_proc varchar2(72) := g_package||' Update Cert Enrollment';
284 l_object_version_number number := p_object_version_number;
285 l_effective_date date;
286
287 begin
288 hr_utility.set_location('Entering:'|| l_proc, 10);
289 --
290 -- Issue a savepoint
291 --
292 savepoint UPDATE_cert_enrollment;
293 l_effective_date := trunc(p_effective_date);
294
295 begin
296 OTA_CERT_ENROLLMENT_bk2.update_cert_enrollment_b
297 ( p_effective_date => p_effective_date
298 ,p_cert_enrollment_id => p_cert_enrollment_id
299 ,p_object_version_number => p_object_version_number
300 ,p_certification_id => p_certification_id
301 ,p_person_id => p_person_id
302 ,p_contact_id => p_contact_id
303 ,p_certification_status_code => p_certification_status_code
304 ,p_completion_date => p_completion_date
305 ,p_UNENROLLMENT_DATE => p_UNENROLLMENT_DATE
306 ,p_EXPIRATION_DATE => p_EXPIRATION_DATE
307 ,p_EARLIEST_ENROLL_DATE => p_EARLIEST_ENROLL_DATE
308 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
309 ,p_business_group_id => p_business_group_id
310 ,p_attribute_category => p_attribute_category
311 ,p_attribute1 => p_attribute1
312 ,p_attribute2 => p_attribute2
313 ,p_attribute3 => p_attribute3
314 ,p_attribute4 => p_attribute4
315 ,p_attribute5 => p_attribute5
316 ,p_attribute6 => p_attribute6
317 ,p_attribute7 => p_attribute7
318 ,p_attribute8 => p_attribute8
319 ,p_attribute9 => p_attribute9
320 ,p_attribute10 => p_attribute10
321 ,p_attribute11 => p_attribute11
322 ,p_attribute12 => p_attribute12
323 ,p_attribute13 => p_attribute13
327 ,p_attribute17 => p_attribute17
324 ,p_attribute14 => p_attribute14
325 ,p_attribute15 => p_attribute15
326 ,p_attribute16 => p_attribute16
328 ,p_attribute18 => p_attribute18
329 ,p_attribute19 => p_attribute19
330 ,p_attribute20 => p_attribute20
331 ,p_enrollment_date => p_enrollment_date
332 );
333
334 exception
335 when hr_api.cannot_find_prog_unit then
336 hr_api.cannot_find_prog_unit_error
337 (p_module_name => 'UPDATE_cert_enrollment'
338 ,p_hook_type => 'BP'
339 );
340 end;
341
342
343 --
344 -- Process Logic
345 --
346
347 ota_cre_upd.upd
348 (
349 p_effective_date => p_effective_date
350 ,p_cert_enrollment_id => p_cert_enrollment_id
351 ,p_object_version_number => l_object_version_number
352 ,p_certification_id => p_certification_id
353 ,p_certification_status_code => p_certification_status_code
354 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
355 ,p_completion_date => p_completion_date
356 ,p_business_group_id => p_business_group_id
357 ,p_person_id => p_person_id
358 ,p_contact_id => p_contact_id
359 ,p_UNENROLLMENT_DATE => p_UNENROLLMENT_DATE
360 ,p_EXPIRATION_DATE => p_EXPIRATION_DATE
361 ,p_EARLIEST_ENROLL_DATE => p_EARLIEST_ENROLL_DATE
362 ,p_attribute_category => p_attribute_category
363 ,p_attribute1 => p_attribute1
364 ,p_attribute2 => p_attribute2
365 ,p_attribute3 => p_attribute3
366 ,p_attribute4 => p_attribute4
367 ,p_attribute5 => p_attribute5
368 ,p_attribute6 => p_attribute6
369 ,p_attribute7 => p_attribute7
370 ,p_attribute8 => p_attribute8
371 ,p_attribute9 => p_attribute9
372 ,p_attribute10 => p_attribute10
373 ,p_attribute11 => p_attribute11
374 ,p_attribute12 => p_attribute12
375 ,p_attribute13 => p_attribute13
376 ,p_attribute14 => p_attribute14
377 ,p_attribute15 => p_attribute15
378 ,p_attribute16 => p_attribute16
379 ,p_attribute17 => p_attribute17
380 ,p_attribute18 => p_attribute18
381 ,p_attribute19 => p_attribute19
382 ,p_attribute20 => p_attribute20
383 ,p_enrollment_date => p_enrollment_date
384 );
385
386
387 begin
388 OTA_CERT_ENROLLMENT_bk2.update_cert_enrollment_a
389 ( p_effective_date => p_effective_date
390 ,p_cert_enrollment_id => p_cert_enrollment_id
391 ,p_object_version_number => p_object_version_number
392 ,p_certification_id => p_certification_id
393 ,p_person_id => p_person_id
394 ,p_contact_id => p_contact_id
395 ,p_certification_status_code => p_certification_status_code
396 ,p_completion_date => p_completion_date
397 ,p_UNENROLLMENT_DATE => p_UNENROLLMENT_DATE
398 ,p_EXPIRATION_DATE => p_EXPIRATION_DATE
399 ,p_EARLIEST_ENROLL_DATE => p_EARLIEST_ENROLL_DATE
400 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
401 ,p_business_group_id => p_business_group_id
402 ,p_attribute_category => p_attribute_category
403 ,p_attribute1 => p_attribute1
404 ,p_attribute2 => p_attribute2
405 ,p_attribute3 => p_attribute3
406 ,p_attribute4 => p_attribute4
407 ,p_attribute5 => p_attribute5
408 ,p_attribute6 => p_attribute6
409 ,p_attribute7 => p_attribute7
410 ,p_attribute8 => p_attribute8
411 ,p_attribute9 => p_attribute9
412 ,p_attribute10 => p_attribute10
413 ,p_attribute11 => p_attribute11
414 ,p_attribute12 => p_attribute12
415 ,p_attribute13 => p_attribute13
416 ,p_attribute14 => p_attribute14
417 ,p_attribute15 => p_attribute15
418 ,p_attribute16 => p_attribute16
419 ,p_attribute17 => p_attribute17
420 ,p_attribute18 => p_attribute18
421 ,p_attribute19 => p_attribute19
422 ,p_attribute20 => p_attribute20
423 ,p_enrollment_date => p_enrollment_date
424 );
425
426 exception
427 when hr_api.cannot_find_prog_unit then
428 hr_api.cannot_find_prog_unit_error
429 (p_module_name => 'UPDATE_cert_enrollment'
430 ,p_hook_type => 'AP'
431 );
432 end;
433
434
435 --
436 -- When in validation only mode raise the Validate_Enabled exception
437 --
438 if p_validate then
442 -- Set all output arguments
439 raise hr_api.validate_enabled;
440 end if;
441 --
443 --
444 p_object_version_number := l_object_version_number;
445
446 hr_utility.set_location(' Leaving:'||l_proc, 70);
447 exception
448 when hr_api.validate_enabled then
449 --
450 -- As the Validate_Enabled exception has been raised
451 -- we must rollback to the savepoint
452 --
453 rollback to UPDATE_cert_enrollment;
454 --
455 -- Only set output warning arguments
456 -- (Any key or derived arguments must be set to null
457 -- when validation only mode is being used.)
458 --
459 p_object_version_number := null;
460 hr_utility.set_location(' Leaving:'||l_proc, 80);
461 when others then
462 --
463 -- A validation or unexpected error has occured
464 --
465 rollback to UPDATE_cert_enrollment;
466 p_object_version_number := l_object_version_number;
467 hr_utility.set_location(' Leaving:'||l_proc, 90);
468 raise;
469 end update_cert_enrollment;
470 --
471 -- ----------------------------------------------------------------------------
472 -- |-------------------------< DELETE_cert_enrollment >-------------------|
473 -- ----------------------------------------------------------------------------
474 --
475 procedure delete_cert_enrollment
476 (p_cert_enrollment_id in number
477 ,p_object_version_number in number
478 ,p_validate in boolean default false
479
480 ) is
481 --
482 -- Declare cursors and local variables
483 --
484 l_proc varchar2(72) := g_package||'DELETE_cert_enrollment';
485 --
486 --
487 begin
488 hr_utility.set_location('Entering:'|| l_proc, 10);
489 --
490 -- Issue a savepoint
491 --
492 savepoint DELETE_cert_enrollment;
493 --
494 -- Truncate the time portion from all IN date parameters
495 --
496 --
497
498 begin
499 OTA_CERT_ENROLLMENT_bk3.delete_cert_enrollment_b
500 (p_cert_enrollment_id => p_cert_enrollment_id
501 ,p_object_version_number => p_object_version_number
502 );
503 exception
504 when hr_api.cannot_find_prog_unit then
505 hr_api.cannot_find_prog_unit_error
506 (p_module_name => 'DELETE_cert_enrollment'
507 ,p_hook_type => 'BP'
508 );
509 end;
510
511 --
512 -- Process Logic
513 --
514
515 ota_cre_del.del
516 (
517 p_cert_enrollment_id => p_cert_enrollment_id,
518 p_object_version_number => p_object_version_number
519 );
520
521
522 begin
523 OTA_CERT_ENROLLMENT_bk3.delete_cert_enrollment_a
524 (p_cert_enrollment_id => p_cert_enrollment_id
525 ,p_object_version_number => p_object_version_number
526 );
527 exception
528 when hr_api.cannot_find_prog_unit then
529 hr_api.cannot_find_prog_unit_error
533 end;
530 (p_module_name => 'DELETE_cert_enrollment'
531 ,p_hook_type => 'AP'
532 );
534
535 --
536 -- When in validation only mode raise the Validate_Enabled exception
537 --
538 if p_validate then
539 raise hr_api.validate_enabled;
540 end if;
541 --
542 -- Set all output arguments
543 --
544 --
545 hr_utility.set_location(' Leaving:'||l_proc, 170);
546 exception
547 when hr_api.validate_enabled then
548 --
549 -- As the Validate_Enabled exception has been raised
550 -- we must rollback to the savepoint
551 --
552 rollback to DELETE_cert_enrollment;
553 --
554 -- Only set output warning arguments
555 -- (Any key or derived arguments must be set to null
556 -- when validation only mode is being used.)
557 --
558 hr_utility.set_location(' Leaving:'||l_proc, 180);
559 when others then
560 --
561 -- A validation or unexpected error has occured
562 --
563 rollback to DELETE_cert_enrollment;
564 hr_utility.set_location(' Leaving:'||l_proc, 190);
565 raise;
566 end delete_cert_enrollment;
567
568 -- ----------------------------------------------------------------------------
569 -- |--------------------------< SUBSCRIBE_TO_CERTIFICATION>-------------------|
570 -- ----------------------------------------------------------------------------
571 procedure subscribe_to_certification
572 (p_validate in boolean default false
573 ,p_certification_id IN NUMBER
574 ,p_person_id IN NUMBER default null
575 ,p_contact_id IN NUMBER default null
576 ,p_business_group_id IN NUMBER
577 ,p_approval_flag IN VARCHAR2
578 ,p_completion_date in date default null
579 ,p_unenrollment_date in date default null
580 ,p_expiration_date in date default null
581 ,p_earliest_enroll_date in date default null
582 ,p_is_history_flag in varchar2
583 ,p_attribute_category in varchar2 default null
584 ,p_attribute1 in varchar2 default null
585 ,p_attribute2 in varchar2 default null
586 ,p_attribute3 in varchar2 default null
587 ,p_attribute4 in varchar2 default null
588 ,p_attribute5 in varchar2 default null
589 ,p_attribute6 in varchar2 default null
590 ,p_attribute7 in varchar2 default null
591 ,p_attribute8 in varchar2 default null
592 ,p_attribute9 in varchar2 default null
593 ,p_attribute10 in varchar2 default null
594 ,p_attribute11 in varchar2 default null
595 ,p_attribute12 in varchar2 default null
596 ,p_attribute13 in varchar2 default null
597 ,p_attribute14 in varchar2 default null
598 ,p_attribute15 in varchar2 default null
599 ,p_attribute16 in varchar2 default null
600 ,p_attribute17 in varchar2 default null
601 ,p_attribute18 in varchar2 default null
602 ,p_attribute19 in varchar2 default null
603 ,p_attribute20 in varchar2 default null
604 ,p_enrollment_date in date default null
605 ,p_cert_enrollment_id OUT NOCOPY NUMBER
606 ,p_certification_status_code OUT NOCOPY VARCHAR2
607 ,p_enroll_from in varchar2 default null
608 ) IS
609
610 CURSOR csr_cert_info IS
611 select
612 b.certification_id certification_id
613 , b.INITIAL_COMPLETION_DATE
614 , b.INITIAL_COMPLETION_DURATION
615 , b.INITIAL_COMPL_DURATION_UNITS
616 , b.RENEWAL_DURATION
617 , b.RENEWAL_DURATION_UNITS
618 , b.NOTIFY_DAYS_BEFORE_EXPIRE
619 , b.VALIDITY_DURATION
620 , b.VALIDITY_DURATION_UNITS
621 , b.RENEWABLE_FLAG
622 , b.VALIDITY_START_TYPE
623 , b.PUBLIC_FLAG
624 , b.START_DATE_ACTIVE
625 , b.END_DATE_ACTIVE
626 from ota_certifications_b b
627 where b.certification_id = p_certification_id;
628
629 CURSOR csr_recert IS
630 select
631 cre.cert_enrollment_id,
632 cre.certification_id,
633 cre.object_version_number,
634 cre.certification_status_code
635 from ota_cert_enrollments cre
636 where cre.certification_id = p_certification_id
637 and (cre.person_id = p_person_id or cre.contact_id = p_contact_id);
638
639 CURSOR csr_cert_enrl(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type) IS
640 select cert_enrollment_id, certification_id, certification_status_code, object_version_number, completion_date
641 FROM ota_cert_enrollments
642 where cert_enrollment_id = p_cert_enrollment_id;
643
644 CURSOR csr_prd_enrl(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type) IS
645 select cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
646 FROM ota_cert_prd_enrollments
647 where cert_enrollment_id = p_cert_enrollment_id
648 and trunc(sysdate) between trunc(cert_period_start_date) and nvl(trunc(cert_period_end_date),trunc(sysdate))
649 order by cert_prd_enrollment_id desc;
650
654 where cert_enrollment_id = p_cert_enrollment_id;
651 CURSOR csr_cpe_exist(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type) IS
652 select cert_prd_enrollment_id
653 FROM ota_cert_prd_enrollments
655
656
657 l_proc varchar2(72) := g_package || ' subscribe_to_certification';
658 l_cert_rec csr_cert_info%ROWTYPE;
659 l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
660 l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
661 l_recert_rec csr_recert%ROWTYPE;
662
663 l_object_version_number1 number;
664 l_object_version_number2 number;
665
666 l_period_status_code VARCHAR2(30);
667 l_certification_status_code ota_cert_enrollments.certification_status_code%type;
668 l_cert_enrollment_id NUMBER;
669 l_cert_prd_enrollment_id NUMBER;
670 l_cert_mbr_enrollment_id NUMBER;
671
672 l_expiration_date DATE;
673 l_earliest_enroll_date DATE;
674
675 l_is_recert VARCHAR2(1);
676 l_is_appr_recert VARCHAR2(1) := 'N';
677
678 l_attribute_category VARCHAR2(30) := p_attribute_category;
679 l_attribute1 VARCHAR2(150) := p_attribute1 ;
680 l_attribute2 VARCHAR2(150) := p_attribute2 ;
681 l_attribute3 VARCHAR2(150) := p_attribute3 ;
682 l_attribute4 VARCHAR2(150) := p_attribute4 ;
683 l_attribute5 VARCHAR2(150) := p_attribute5 ;
684 l_attribute6 VARCHAR2(150) := p_attribute6 ;
685 l_attribute7 VARCHAR2(150) := p_attribute7 ;
686 l_attribute8 VARCHAR2(150) := p_attribute8 ;
687 l_attribute9 VARCHAR2(150) := p_attribute9 ;
688 l_attribute10 VARCHAR2(150) := p_attribute10 ;
689 l_attribute11 VARCHAR2(150) := p_attribute11 ;
690 l_attribute12 VARCHAR2(150) := p_attribute12 ;
691 l_attribute13 VARCHAR2(150) := p_attribute13 ;
692 l_attribute14 VARCHAR2(150) := p_attribute14 ;
693 l_attribute15 VARCHAR2(150) := p_attribute15 ;
694 l_attribute16 VARCHAR2(150) := p_attribute16 ;
695 l_attribute17 VARCHAR2(150) := p_attribute17 ;
696 l_attribute18 VARCHAR2(150) := p_attribute18 ;
697 l_attribute19 VARCHAR2(150) := p_attribute19 ;
698 l_attribute20 VARCHAR2(150) := p_attribute20 ;
699
700 l_dummy number;
701 l_o_cert_enroll_id number;
702 l_o_cert_enroll_status varchar2(100);
703
704 BEGIN
705 hr_utility.set_location('Entering:'|| l_proc, 10);
706
707 OPEN csr_cert_info;
708 FETCH csr_cert_info INTO l_cert_rec;
709 CLOSE csr_cert_info;
710
711 hr_multi_message.enable_message_list;
712 savepoint create_cert_subscription;
713
714 if (p_approval_flag = 'N' or p_approval_flag = 'S') then
715 l_certification_status_code := 'ENROLLED';
716 l_period_status_code := 'ENROLLED';
717 else
718 l_certification_status_code := 'AWAITING_APPROVAL';
719 end if;
720
721 hr_utility.set_location(' Step:'|| l_proc, 20);
722
723 --check for initial subscription or resubscription to unsubscribed cert
724 --re-cert is applicable if approvals is off and certification_status_code in 'CANCELLED'
725 --or if approvals on and certification_status_code in 'AWAITING_APPROVAL' but has atleast one period.
726
727 OPEN csr_recert;
728 FETCH csr_recert INTO l_recert_rec;
729 CLOSE csr_recert;
730
731 -- needed for firing ntf from admin side
732 l_o_cert_enroll_id := l_recert_rec.cert_enrollment_id;
733 l_o_cert_enroll_status :=l_recert_rec.certification_status_code;
734
735
736 hr_utility.set_location(' Step:'|| l_proc, 30);
737
738 --check for approvals re-cert
739 if (l_recert_rec.certification_status_code = 'AWAITING_APPROVAL') then
740 open csr_cpe_exist(l_recert_rec.cert_enrollment_id);
741 fetch csr_cpe_exist into l_dummy;
742 if csr_cpe_exist%found then
743 l_is_appr_recert := 'Y';
744 end if;
745 close csr_cpe_exist;
746 end if;
747
748 if (l_recert_rec.certification_status_code = 'CANCELLED' or l_recert_rec.certification_status_code = 'REJECTED' or l_is_appr_recert = 'Y') then
749 l_is_recert := 'Y';
750 else
751 l_is_recert := 'N';
752 end if;
753
754 l_cert_enrollment_id := l_recert_rec.cert_enrollment_id;
755
756 hr_utility.set_location(' Step:'|| l_proc, 40);
757
758 --calculate exp and earliest enrol dates
759 --for onetime certs there's no expiration
760 --for renewable certs calc based on initial_completion_date, INITIAL_COMPLETION_DURATION,
761 --validity_start_type and validity_duration
762
763 if (l_cert_rec.renewable_flag = 'Y') then
764 ota_cpe_util.calc_cre_dates(null, l_cert_rec.certification_id, 'I', l_earliest_enroll_date, l_expiration_date, p_enrollment_date);
765 end if; --end renewal flag
766
767 hr_utility.set_location(' Step:'|| l_proc, 50);
768
769 --create or update CRE based on recert flag
770 if (l_is_recert = 'N') then
771 -- initial subscription
772 --create cert enrollment only when the approval is off or enrl is sent for approval
773
774 if (p_approval_flag = 'N' or p_approval_flag = 'A') then
775
776 hr_utility.set_location(' Step:'|| l_proc, 60);
777 ota_utility.Get_Default_Value_Dff(
778 appl_short_name => 'OTA'
779 ,flex_field_name => 'OTA_CERT_ENROLLMENTS'
780 ,p_attribute_category => l_attribute_category
784 ,p_attribute4 => l_attribute4
781 ,p_attribute1 => l_attribute1
782 ,p_attribute2 => l_attribute2
783 ,p_attribute3 => l_attribute3
785 ,p_attribute5 => l_attribute5
786 ,p_attribute6 => l_attribute6
787 ,p_attribute7 => l_attribute7
788 ,p_attribute8 => l_attribute8
789 ,p_attribute9 => l_attribute9
790 ,p_attribute10 => l_attribute10
791 ,p_attribute11 => l_attribute11
792 ,p_attribute12 => l_attribute12
793 ,p_attribute13 => l_attribute13
794 ,p_attribute14 => l_attribute14
795 ,p_attribute15 => l_attribute15
796 ,p_attribute16 => l_attribute16
797 ,p_attribute17 => l_attribute17
798 ,p_attribute18 => l_attribute18
799 ,p_attribute19 => l_attribute19
800 ,p_attribute20 => l_attribute20);
801
802 ota_cert_enrollment_api.create_cert_enrollment(
803 p_effective_date => trunc(sysdate)
804 ,p_validate => p_validate
805 ,p_certification_id => p_certification_id
806 ,p_person_id => p_person_id
807 ,p_contact_id => p_contact_id
808 ,p_certification_status_code => l_certification_status_code
809 ,p_business_group_id => p_business_group_id
810 ,p_completion_date => p_completion_date
811 ,p_unenrollment_date => p_unenrollment_date
812 ,p_earliest_enroll_date => l_earliest_enroll_date
813 ,p_is_history_flag => p_is_history_flag
814 ,p_attribute_category => l_attribute_category
815 ,p_attribute1 => l_attribute1
816 ,p_attribute2 => l_attribute2
817 ,p_attribute3 => l_attribute3
818 ,p_attribute4 => l_attribute4
819 ,p_attribute5 => l_attribute5
820 ,p_attribute6 => l_attribute6
821 ,p_attribute7 => l_attribute7
822 ,p_attribute8 => l_attribute8
823 ,p_attribute9 => l_attribute9
824 ,p_attribute10 => l_attribute10
825 ,p_attribute11 => l_attribute11
826 ,p_attribute12 => l_attribute12
827 ,p_attribute13 => l_attribute13
828 ,p_attribute14 => l_attribute14
829 ,p_attribute15 => l_attribute15
830 ,p_attribute16 => l_attribute16
831 ,p_attribute17 => l_attribute17
832 ,p_attribute18 => l_attribute18
833 ,p_attribute19 => l_attribute19
834 ,p_attribute20 => l_attribute20
835 ,p_enrollment_date => trunc(nvl(p_enrollment_date, sysdate))
836 ,p_cert_enrollment_id => l_cert_enrollment_id
837 ,p_object_version_number => l_object_version_number1
838 );
839 end if; --end create CRE
840
841 hr_utility.set_location(' Step:'|| l_proc, 70);
842
843 if (p_approval_flag = 'N' or p_approval_flag = 'S') then
844
845 --update cre status on approval confirmed
846 if (p_approval_flag = 'S') then
847 OPEN csr_cert_enrl(l_cert_enrollment_id);
848 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
849 CLOSE csr_cert_enrl;
850
851 hr_utility.set_location(' Step:'|| l_proc, 75);
852
853 ota_cert_enrollment_api.update_cert_enrollment
854 (p_effective_date => sysdate
855 ,p_cert_enrollment_id => l_cert_enrollment_id
856 ,p_certification_id => p_certification_id
857 ,p_object_version_number => l_cert_enrl_rec.object_version_number
858 ,p_certification_status_code => l_certification_status_code
859 );
860 end if;
861
862 --call create CPE and CME util proc
863 ota_cpe_util.create_cpe_rec(p_cert_enrollment_id => l_cert_enrollment_id,
864 p_expiration_date => l_expiration_date,
865 p_cert_period_start_date => trunc(nvl(p_enrollment_date, sysdate)),
866 p_cert_prd_enrollment_id => l_cert_prd_enrollment_id,
867 p_certification_status_code => l_certification_status_code);
868 end if;--create CPE CMEs
869
870 hr_utility.set_location(' Step:'|| l_proc, 80);
871
872 else -- start of recert
873 -- resubscription to unsubscribed cert
874 -- set the status and flip the unenrollment date to null
875
876 hr_utility.set_location(' Step:'|| l_proc, 90);
877
878 --update cert enrollment
879 OPEN csr_cert_enrl(l_recert_rec.cert_enrollment_id);
880 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
881 CLOSE csr_cert_enrl;
882
883 hr_utility.set_location(' Step:'|| l_proc, 100);
884
885 ota_cert_enrollment_api.update_cert_enrollment
886 (p_effective_date => sysdate
890 ,p_certification_status_code => l_certification_status_code
887 ,p_cert_enrollment_id => l_cert_enrollment_id
888 ,p_certification_id => l_cert_enrl_rec.certification_id
889 ,p_object_version_number => l_cert_enrl_rec.object_version_number
891 ,p_is_history_flag => p_is_history_flag
892 ,p_unenrollment_date => null
893 ,p_attribute_category => p_attribute_category
894 ,p_attribute1 => p_attribute1
895 ,p_attribute2 => p_attribute2
896 ,p_attribute3 => p_attribute3
897 ,p_attribute4 => p_attribute4
898 ,p_attribute5 => p_attribute5
899 ,p_attribute6 => p_attribute6
900 ,p_attribute7 => p_attribute7
901 ,p_attribute8 => p_attribute8
902 ,p_attribute9 => p_attribute9
903 ,p_attribute10 => p_attribute10
904 ,p_attribute11 => p_attribute11
905 ,p_attribute12 => p_attribute12
906 ,p_attribute13 => p_attribute13
907 ,p_attribute14 => p_attribute14
908 ,p_attribute15 => p_attribute15
909 ,p_attribute16 => p_attribute16
910 ,p_attribute17 => p_attribute17
911 ,p_attribute18 => p_attribute18
912 ,p_attribute19 => p_attribute19
913 ,p_attribute20 => p_attribute20
914 ,p_enrollment_date => trunc(nvl(p_enrollment_date, sysdate)));
915
916 --udpate prd enrol and mbr enrol only if approval mode is off or enrl approval is granted success;
917 if (p_approval_flag = 'N' or p_approval_flag = 'S') then
918 -- check active period and update if found, otherwise create CPE and CME
919 OPEN csr_prd_enrl(l_recert_rec.cert_enrollment_id);
920 FETCH csr_prd_enrl INTO l_prd_enrl_rec;
921 if (csr_prd_enrl%FOUND) then
922 l_cert_prd_enrollment_id := l_prd_enrl_rec.cert_prd_enrollment_id;
923 end if;
924 CLOSE csr_prd_enrl;
925
926 hr_utility.set_location(' Step:'|| l_proc, 110);
927
928 if (l_cert_prd_enrollment_id is not null) then
929 --Bug 4565761
930 ota_cme_util.refresh_cme(l_cert_prd_enrollment_id);
931
932 --calculate period and mbr enroll status for existing data and update accordingly
933 ota_cpe_util.update_cpe_status(l_cert_prd_enrollment_id,
934 p_certification_status_code => l_certification_status_code);
935
936 hr_utility.set_location(' Step:'|| l_proc, 120);
937
941 --update cre for exp date and earliest enroll date when creating new period
938 else
939 --create prd and mbr enrols
940
942 --calculate exp and earliest enrol dates
943 --for onetime certs there's no expiration
944 --for renewable certs calc based on initial_completion_date, INITIAL_COMPLETION_DURATION,
945 --validity_start_type and validity_duration
946 OPEN csr_cert_enrl(l_recert_rec.cert_enrollment_id);
947 FETCH csr_cert_enrl INTO l_cert_enrl_rec;
948 CLOSE csr_cert_enrl;
949
950 hr_utility.set_location(' Step:'|| l_proc, 130);
951
952 ota_cert_enrollment_api.update_cert_enrollment
953 (p_effective_date => sysdate
954 ,p_cert_enrollment_id => l_cert_enrollment_id
955 ,p_certification_id => p_certification_id
956 ,p_object_version_number => l_cert_enrl_rec.object_version_number
957 ,p_certification_status_code => l_certification_status_code
958 ,p_is_history_flag => p_is_history_flag
959 ,p_earliest_enroll_date => l_earliest_enroll_date
960 );
961
962 hr_utility.set_location(' Step:'|| l_proc, 140);
963
964 --call create CPE and CME util proc
965 ota_cpe_util.create_cpe_rec(p_cert_enrollment_id => l_cert_enrollment_id,
966 p_expiration_date => l_expiration_date,
967 p_cert_period_start_date => trunc(nvl(p_enrollment_date, sysdate)),
968 p_cert_prd_enrollment_id => l_cert_prd_enrollment_id,
969 p_certification_status_code => l_certification_status_code);
970 end if;
971 end if; --for approval N or S
972 end if; --end recert
973
974 hr_utility.set_location(' Step:'|| l_proc, 150);
975
976 --set output param
977 p_cert_enrollment_id := l_cert_enrollment_id;
978 p_certification_status_code := l_certification_status_code;
979
980 --fire ntf for enrollment from admin side
981
982 if (((l_o_cert_enroll_id is null and l_certification_status_code ='ENROLLED')
983 or (l_o_cert_enroll_id is not null and l_o_cert_enroll_status <> 'ENROLLED'
984 and l_certification_status_code ='ENROLLED'))
985 and p_person_id is not null
986 and nvl(p_enroll_from , '-1')<> 'LRNR')then
987 OTA_LRNR_ENROLL_UNENROLL_WF.Cert_Enrollment(p_process => 'OTA_CERT_APPROVAL_JSP_PRC',
988 p_itemtype => 'HRSSA',
989 p_person_id => p_person_id,
990 p_certificationid => p_certification_id);
991
992 end if;
993
994
995
996
997 if p_validate then
998 raise hr_api.validate_enabled;
999 end if;
1000
1001 exception
1002 when hr_api.validate_enabled then
1003 --
1004 --
1005 -- Catch the Multiple Message List exception which
1006 -- indicates API processing has been aborted because
1007 -- at least one message exists in the list.
1008 --
1009 rollback to create_cert_subscription;
1010 --
1011 -- Reset IN OUT parameters and set OUT parameters
1012 --
1013 p_cert_enrollment_id := null;
1014 p_certification_status_code := null;
1015 hr_utility.set_location(' Leaving:' || l_proc, 160);
1016
1017 when others then
1018 --
1019 -- A validation or unexpected error has occured
1020 --
1021 rollback to create_cert_subscription;
1022 --
1023 -- Reset IN OUT and set OUT parameters
1024 --
1025 p_cert_enrollment_id := null;
1026 p_certification_status_code := null;
1027
1028 hr_utility.set_location(' Leaving:' || l_proc,170);
1029 raise;
1030 END subscribe_to_certification;
1031 --
1032 end OTA_CERT_ENROLLMENT_api;