DBA Data[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;