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