DBA Data[Home] [Help]

PACKAGE BODY: APPS.OTA_CERT_MBR_ENROLLMENT_API

Source


1 Package Body OTA_CERT_MBR_ENROLLMENT_API as
2 /* $Header: otcmeapi.pkb 120.1 2005/08/10 15:26 asud noship $ */
3 --
4 -- Package Variables
5 --
6 g_package  varchar2(33) := '  OTA_CERT_MBR_ENROLLMENT_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_cert_mbr_enrollment    >-----------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_cert_mbr_enrollment
13 (
14   p_effective_date               in date,
15   p_validate                     in boolean          default false ,
16   p_cert_prd_enrollment_id       in number,
17   p_cert_member_id               in number,
18   p_member_status_code           in varchar2,
19   p_completion_date              in date             default null,
20   p_business_group_id            in number,
21   p_attribute_category           in varchar2         default null,
22   p_attribute1                   in varchar2         default null,
23   p_attribute2                   in varchar2         default null,
24   p_attribute3                   in varchar2         default null,
25   p_attribute4                   in varchar2         default null,
26   p_attribute5                   in varchar2         default null,
27   p_attribute6                   in varchar2         default null,
28   p_attribute7                   in varchar2         default null,
29   p_attribute8                   in varchar2         default null,
30   p_attribute9                   in varchar2         default null,
31   p_attribute10                  in varchar2         default null,
32   p_attribute11                  in varchar2         default null,
33   p_attribute12                  in varchar2         default null,
34   p_attribute13                  in varchar2         default null,
35   p_attribute14                  in varchar2         default null,
36   p_attribute15                  in varchar2         default null,
37   p_attribute16                  in varchar2         default null,
38   p_attribute17                  in varchar2         default null,
39   p_attribute18                  in varchar2         default null,
40   p_attribute19                  in varchar2         default null,
41   p_attribute20                  in varchar2         default null,
42   p_cert_mbr_enrollment_id       out nocopy number,
43   p_object_version_number        out nocopy number
44     ) is
45   --
46   -- Declare cursors and local variables
47   --
48   l_proc                    varchar2(72) := g_package||' create_cert_mbr_enrollment';
49   l_cert_mbr_enrollment_id number;
50   l_object_version_number   number;
51   l_effective_date date;
52 
53   l_member_status_code	ota_cert_mbr_enrollments.member_status_code%TYPE := p_member_status_code;
54   l_completion_date     ota_cert_mbr_enrollments.completion_date%TYPE := p_completion_date;
55   l_activity_version_id	ota_activity_versions.activity_version_id%TYPE;
56 
57   CURSOR csr_get_course_id IS
58   SELECT object_id
59     FROM ota_certification_members
60    WHERE certification_member_id = p_cert_member_id;
61 
62  l_mode varchar2(1);
63 
64 begin
65   hr_utility.set_location('Entering:'|| l_proc, 10);
66   --
67   -- Issue a savepoint
68   --
69   savepoint CREATE_cert_mbr_enrollment;
70   l_effective_date := trunc(p_effective_date);
71 
72   IF p_member_status_code = 'PLANNED' THEN
73      OPEN csr_get_course_id;
74     FETCH csr_get_course_id INTO l_activity_version_id;
75     CLOSE csr_get_course_id;
76     l_mode := 'C';
77      ota_cme_util.calculate_cme_status(p_activity_version_id 	=> l_activity_version_id,
78                                                  p_cert_prd_enrollment_id => p_cert_prd_enrollment_id,
79                                                  p_mode => l_mode,
80                                                  p_member_status_code	=> l_member_status_code,
81                                                  p_completion_date      => l_completion_date);
82  END IF;
83 
84   begin
85   OTA_CERT_MBR_ENROLLMENT_bk1.create_cert_mbr_enrollment_b
86   (  p_effective_date               => p_effective_date
87     ,p_cert_prd_enrollment_id       => p_cert_prd_enrollment_id
88     ,p_cert_member_id               => p_cert_member_id
89     ,p_member_status_code           => l_member_status_code
90     ,p_completion_date              => l_completion_date
91     ,p_business_group_id            => p_business_group_id
92     ,p_attribute_category           => p_attribute_category
93     ,p_attribute1                   => p_attribute1
94     ,p_attribute2                   => p_attribute2
95     ,p_attribute3                   => p_attribute3
96     ,p_attribute4                   => p_attribute4
97     ,p_attribute5                   => p_attribute5
98     ,p_attribute6                   => p_attribute6
99     ,p_attribute7                   => p_attribute7
100     ,p_attribute8                   => p_attribute8
101     ,p_attribute9                   => p_attribute9
102     ,p_attribute10                  => p_attribute10
103     ,p_attribute11                  => p_attribute11
104     ,p_attribute12                  => p_attribute12
105     ,p_attribute13                  => p_attribute13
106     ,p_attribute14                  => p_attribute14
107     ,p_attribute15                  => p_attribute15
108     ,p_attribute16                  => p_attribute16
109     ,p_attribute17                  => p_attribute17
110     ,p_attribute18                  => p_attribute18
111     ,p_attribute19                  => p_attribute19
112     ,p_attribute20                  => p_attribute20
113     );
114   exception
115     when hr_api.cannot_find_prog_unit then
116       hr_api.cannot_find_prog_unit_error
117         (p_module_name => 'CREATE_cert_mbr_enrollment'
118         ,p_hook_type   => 'BP'
119         );
120   end;
121 
122   --
123   -- Process Logic
124   --
125   ota_cme_ins.ins
126   (
127    p_effective_date                 =>   p_effective_date
128   ,p_cert_prd_enrollment_id         =>   p_cert_prd_enrollment_id
129   ,p_cert_member_id                 =>   p_cert_member_id
130   ,p_member_status_code             =>   l_member_status_code
131   ,p_completion_date                =>   l_completion_date
132   ,p_business_group_id              =>   p_business_group_id
133   ,p_attribute_category             =>   p_attribute_category
134   ,p_attribute1                     =>   p_attribute1
135   ,p_attribute2                     =>   p_attribute2
136   ,p_attribute3                     =>   p_attribute3
137   ,p_attribute4                     =>   p_attribute4
138   ,p_attribute5                     =>   p_attribute5
139   ,p_attribute6                     =>   p_attribute6
140   ,p_attribute7                     =>   p_attribute7
141   ,p_attribute8                     =>   p_attribute8
142   ,p_attribute9                     =>   p_attribute9
143   ,p_attribute10                    =>   p_attribute10
144   ,p_attribute11                    =>   p_attribute11
145   ,p_attribute12                    =>   p_attribute12
146   ,p_attribute13                    =>   p_attribute13
147   ,p_attribute14                    =>   p_attribute14
148   ,p_attribute15                    =>   p_attribute15
149   ,p_attribute16                    =>   p_attribute16
150   ,p_attribute17                    =>   p_attribute17
151   ,p_attribute18                    =>   p_attribute18
152   ,p_attribute19                    =>   p_attribute19
153   ,p_attribute20                    =>   p_attribute20
154   ,p_cert_mbr_enrollment_id         =>   l_cert_mbr_enrollment_id
155   ,p_object_version_number          =>   l_object_version_number
156   );
157   --
158   -- Set all output arguments
159   --
160   p_cert_mbr_enrollment_id  := l_cert_mbr_enrollment_id;
161   p_object_version_number   := l_object_version_number;
162 
163 
164 
165   begin
166   OTA_CERT_MBR_ENROLLMENT_bk1.create_cert_mbr_enrollment_a
167    ( p_effective_date               => p_effective_date
168     ,p_cert_mbr_enrollment_id       => p_cert_mbr_enrollment_id
169     ,p_cert_prd_enrollment_id       => p_cert_prd_enrollment_id
170     ,p_cert_member_id               => p_cert_member_id
171     ,p_member_status_code           => l_member_status_code
172     ,p_completion_date              => l_completion_date
173     ,p_business_group_id            => p_business_group_id
174     ,p_attribute_category           => p_attribute_category
175     ,p_attribute1                   => p_attribute1
176     ,p_attribute2                   => p_attribute2
177     ,p_attribute3                   => p_attribute3
178     ,p_attribute4                   => p_attribute4
179     ,p_attribute5                   => p_attribute5
180     ,p_attribute6                   => p_attribute6
181     ,p_attribute7                   => p_attribute7
182     ,p_attribute8                   => p_attribute8
183     ,p_attribute9                   => p_attribute9
184     ,p_attribute10                  => p_attribute10
185     ,p_attribute11                  => p_attribute11
186     ,p_attribute12                  => p_attribute12
187     ,p_attribute13                  => p_attribute13
188     ,p_attribute14                  => p_attribute14
189     ,p_attribute15                  => p_attribute15
190     ,p_attribute16                  => p_attribute16
191     ,p_attribute17                  => p_attribute17
192     ,p_attribute18                  => p_attribute18
193     ,p_attribute19                  => p_attribute19
194     ,p_attribute20                  => p_attribute20
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_mbr_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_mbr_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_mbr_enrollment_id  := null;
226     p_object_version_number   := null;
227     hr_utility.set_location(' Leaving:'||l_proc, 80);
228   when others then
229     --
233     p_cert_mbr_enrollment_id  := null;
230     -- A validation or unexpected error has occured
231     --
232     rollback to CREATE_cert_mbr_enrollment;
234     p_object_version_number   := null;
235     hr_utility.set_location(' Leaving:'||l_proc, 90);
236     raise;
237 end create_cert_mbr_enrollment;
238 -- ----------------------------------------------------------------------------
239 -- |-------------------------< UPDATE_cert_mbr_enrollment >-------------------------|
240 -- ----------------------------------------------------------------------------
241 --
242 procedure update_cert_mbr_enrollment
243   (p_effective_date               in     date
244   ,p_cert_mbr_enrollment_id       in     number
245   ,p_object_version_number        in out nocopy number
246   ,p_cert_prd_enrollment_id       in     number
247   ,p_cert_member_id               in     number
248   ,p_member_status_code           in     varchar2
249   ,p_completion_date              in     date      default hr_api.g_date
250   ,p_business_group_id            in     number    default hr_api.g_number
251   ,p_attribute_category           in     varchar2  default hr_api.g_varchar2
252   ,p_attribute1                   in     varchar2  default hr_api.g_varchar2
253   ,p_attribute2                   in     varchar2  default hr_api.g_varchar2
254   ,p_attribute3                   in     varchar2  default hr_api.g_varchar2
255   ,p_attribute4                   in     varchar2  default hr_api.g_varchar2
256   ,p_attribute5                   in     varchar2  default hr_api.g_varchar2
257   ,p_attribute6                   in     varchar2  default hr_api.g_varchar2
258   ,p_attribute7                   in     varchar2  default hr_api.g_varchar2
259   ,p_attribute8                   in     varchar2  default hr_api.g_varchar2
260   ,p_attribute9                   in     varchar2  default hr_api.g_varchar2
261   ,p_attribute10                  in     varchar2  default hr_api.g_varchar2
262   ,p_attribute11                  in     varchar2  default hr_api.g_varchar2
263   ,p_attribute12                  in     varchar2  default hr_api.g_varchar2
264   ,p_attribute13                  in     varchar2  default hr_api.g_varchar2
265   ,p_attribute14                  in     varchar2  default hr_api.g_varchar2
266   ,p_attribute15                  in     varchar2  default hr_api.g_varchar2
267   ,p_attribute16                  in     varchar2  default hr_api.g_varchar2
268   ,p_attribute17                  in     varchar2  default hr_api.g_varchar2
269   ,p_attribute18                  in     varchar2  default hr_api.g_varchar2
270   ,p_attribute19                  in     varchar2  default hr_api.g_varchar2
271   ,p_attribute20                  in     varchar2  default hr_api.g_varchar2
272   ,p_validate                     in     boolean          default false
273    ) is
274   --
275   -- Declare cursors and local variables
276   --
277   l_proc                    varchar2(72) := g_package||' Update Cert Enrollment';
281   l_member_status_code	    ota_cert_mbr_enrollments.member_status_code%TYPE := p_member_status_code;
278   l_object_version_number   number := p_object_version_number;
279   l_effective_date date;
280 
282   l_completion_date         ota_cert_mbr_enrollments.completion_date%TYPE := p_completion_date;
283   l_activity_version_id	ota_activity_versions.activity_version_id%TYPE;
284   l_cert_prd_enrollment_id    ota_cert_prd_enrollments.cert_enrollment_id%TYPE := p_cert_prd_enrollment_id;
285 
286   CURSOR csr_get_course_id IS
287   SELECT cmb.object_id,
288          cpe.cert_prd_enrollment_id
289     FROM ota_certification_members cmb,
290          ota_cert_mbr_enrollments cme,
291          ota_cert_prd_enrollments cpe
292    WHERE cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
293      AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
294      AND cme.cert_member_id = cmb.certification_member_id;
295 
296  l_mode varchar2(1);
297 
298 begin
299   hr_utility.set_location('Entering:'|| l_proc, 10);
300   --
301   -- Issue a savepoint
302   --
303   savepoint UPDATE_cert_mbr_enrollment;
304   l_effective_date := trunc(p_effective_date);
305 
306   IF p_member_status_code <> 'CANCELLED' THEN
307       FOR rec_course IN csr_get_course_id
308      LOOP
309           l_activity_version_id := rec_course.object_id;
310           l_cert_prd_enrollment_id    := rec_course.cert_prd_enrollment_id;
311      EXIT;
312       END LOOP;
313      l_mode := 'U';
314      ota_cme_util.calculate_cme_status(p_activity_version_id 	  => l_activity_version_id,
315                                                  p_cert_prd_enrollment_id => l_cert_prd_enrollment_id,
316                                                  p_mode => l_mode,
317                                                  p_member_status_code	  => l_member_status_code,
318                                                  p_completion_date        => l_completion_date);
319   END IF;
320 
321   begin
322   OTA_CERT_MBR_ENROLLMENT_bk2.update_cert_mbr_enrollment_b
323   (  p_effective_date               => p_effective_date
324     ,p_cert_mbr_enrollment_id       => p_cert_mbr_enrollment_id
325     ,p_object_version_number        => p_object_version_number
326     ,p_cert_prd_enrollment_id       => p_cert_prd_enrollment_id
327     ,p_cert_member_id               => p_cert_member_id
328     ,p_member_status_code           => l_member_status_code
329     ,p_completion_date              => l_completion_date
330     ,p_business_group_id            => p_business_group_id
331     ,p_attribute_category           => p_attribute_category
332     ,p_attribute1                   => p_attribute1
333     ,p_attribute2                   => p_attribute2
334     ,p_attribute3                   => p_attribute3
335     ,p_attribute4                   => p_attribute4
336     ,p_attribute5                   => p_attribute5
337     ,p_attribute6                   => p_attribute6
338     ,p_attribute7                   => p_attribute7
339     ,p_attribute8                   => p_attribute8
340     ,p_attribute9                   => p_attribute9
341     ,p_attribute10                  => p_attribute10
342     ,p_attribute11                  => p_attribute11
343     ,p_attribute12                  => p_attribute12
344     ,p_attribute13                  => p_attribute13
345     ,p_attribute14                  => p_attribute14
346     ,p_attribute15                  => p_attribute15
347     ,p_attribute16                  => p_attribute16
348     ,p_attribute17                  => p_attribute17
349     ,p_attribute18                  => p_attribute18
350     ,p_attribute19                  => p_attribute19
351     ,p_attribute20                  => p_attribute20
352     );
353 
354   exception
355     when hr_api.cannot_find_prog_unit then
356       hr_api.cannot_find_prog_unit_error
357         (p_module_name => 'UPDATE_cert_mbr_enrollment'
358         ,p_hook_type   => 'BP'
359         );
360   end;
361 
362 
363   --
364   -- Process Logic
365   --
366 
367  ota_cme_upd.upd
368   (
369    p_effective_date                 =>   p_effective_date
370   ,p_cert_mbr_enrollment_id         =>   p_cert_mbr_enrollment_id
371   ,p_object_version_number          =>   l_object_version_number
372   ,p_cert_prd_enrollment_id         =>   p_cert_prd_enrollment_id
373   ,p_cert_member_id                 =>   p_cert_member_id
374   ,p_member_status_code             =>   l_member_status_code
375   ,p_completion_date                =>   l_completion_date
376   ,p_business_group_id              =>   p_business_group_id
377   ,p_attribute_category             =>   p_attribute_category
378   ,p_attribute1                     =>   p_attribute1
379   ,p_attribute2                     =>   p_attribute2
380   ,p_attribute3                     =>   p_attribute3
381   ,p_attribute4                     =>   p_attribute4
382   ,p_attribute5                     =>   p_attribute5
383   ,p_attribute6                     =>   p_attribute6
384   ,p_attribute7                     =>   p_attribute7
385   ,p_attribute8                     =>   p_attribute8
386   ,p_attribute9                     =>   p_attribute9
387   ,p_attribute10                    =>   p_attribute10
388   ,p_attribute11                    =>   p_attribute11
389   ,p_attribute12                    =>   p_attribute12
390   ,p_attribute13                    =>   p_attribute13
391   ,p_attribute14                    =>   p_attribute14
392   ,p_attribute15                    =>   p_attribute15
393   ,p_attribute16                    =>   p_attribute16
394   ,p_attribute17                    =>   p_attribute17
395   ,p_attribute18                    =>   p_attribute18
396   ,p_attribute19                    =>   p_attribute19
397   ,p_attribute20                    =>   p_attribute20
398   );
399 
400 
401   begin
405     ,p_object_version_number        => p_object_version_number
402   OTA_CERT_MBR_ENROLLMENT_bk2.update_cert_mbr_enrollment_a
403   (  p_effective_date               => p_effective_date
404     ,p_cert_mbr_enrollment_id       => p_cert_mbr_enrollment_id
406     ,p_cert_prd_enrollment_id       => p_cert_prd_enrollment_id
407     ,p_cert_member_id               => p_cert_member_id
408     ,p_member_status_code           => l_member_status_code
409     ,p_completion_date              => l_completion_date
410     ,p_business_group_id            => p_business_group_id
411     ,p_attribute_category           => p_attribute_category
412     ,p_attribute1                   => p_attribute1
413     ,p_attribute2                   => p_attribute2
414     ,p_attribute3                   => p_attribute3
415     ,p_attribute4                   => p_attribute4
416     ,p_attribute5                   => p_attribute5
417     ,p_attribute6                   => p_attribute6
418     ,p_attribute7                   => p_attribute7
419     ,p_attribute8                   => p_attribute8
420     ,p_attribute9                   => p_attribute9
421     ,p_attribute10                  => p_attribute10
422     ,p_attribute11                  => p_attribute11
423     ,p_attribute12                  => p_attribute12
424     ,p_attribute13                  => p_attribute13
425     ,p_attribute14                  => p_attribute14
426     ,p_attribute15                  => p_attribute15
427     ,p_attribute16                  => p_attribute16
428     ,p_attribute17                  => p_attribute17
429     ,p_attribute18                  => p_attribute18
430     ,p_attribute19                  => p_attribute19
431     ,p_attribute20                  => p_attribute20
432     );
433 
434   exception
435     when hr_api.cannot_find_prog_unit then
436       hr_api.cannot_find_prog_unit_error
437         (p_module_name => 'UPDATE_CERT_MBR_ENROLLMENT'
438         ,p_hook_type   => 'AP'
439         );
440   end;
441 
442 
443   --
444   -- When in validation only mode raise the Validate_Enabled exception
448   end if;
445   --
446   if p_validate then
447     raise hr_api.validate_enabled;
449   --
450   -- Set all output arguments
451   --
452   p_object_version_number  := l_object_version_number;
453 
454   hr_utility.set_location(' Leaving:'||l_proc, 70);
455 exception
456   when hr_api.validate_enabled then
457     --
458     -- As the Validate_Enabled exception has been raised
459     -- we must rollback to the savepoint
460     --
461     rollback to UPDATE_cert_mbr_enrollment;
462     --
463     -- Only set output warning arguments
464     -- (Any key or derived arguments must be set to null
465     -- when validation only mode is being used.)
466     --
467     p_object_version_number  := null;
468     hr_utility.set_location(' Leaving:'||l_proc, 80);
469   when others then
470     --
471     -- A validation or unexpected error has occured
472     --
473     rollback to UPDATE_cert_mbr_enrollment;
474     p_object_version_number  := l_object_version_number;
475     hr_utility.set_location(' Leaving:'||l_proc, 90);
476     raise;
477 end update_cert_mbr_enrollment;
478 --
479 -- ----------------------------------------------------------------------------
480 -- |-------------------------< DELETE_cert_mbr_enrollment >-------------------|
481 -- ----------------------------------------------------------------------------
482 --
483 procedure delete_cert_mbr_enrollment
484   (p_cert_mbr_enrollment_id        in     number
485   ,p_object_version_number         in     number
486   ,p_validate                      in     boolean  default false
487 
488   ) is
489   --
490   -- Declare cursors and local variables
491   --
492   l_proc                    varchar2(72) := g_package||'DELETE_cert_mbr_enrollment';
493   --
494   --
495 begin
496   hr_utility.set_location('Entering:'|| l_proc, 10);
497   --
498   -- Issue a savepoint
499   --
500   savepoint DELETE_cert_mbr_enrollment;
501   --
502   -- Truncate the time portion from all IN date parameters
503   --
504   --
505 
506   begin
507   OTA_CERT_MBR_ENROLLMENT_bk3.delete_cert_mbr_enrollment_b
508   (p_cert_mbr_enrollment_id         => p_cert_mbr_enrollment_id
509     ,p_object_version_number        => p_object_version_number
510     );
511   exception
512     when hr_api.cannot_find_prog_unit then
513       hr_api.cannot_find_prog_unit_error
514         (p_module_name => 'DELETE_cert_mbr_enrollment'
515         ,p_hook_type   => 'BP'
516         );
517   end;
518 
519   --
520   -- Process Logic
521   --
522 
523   ota_cme_del.del
524   (
525   p_cert_mbr_enrollment_id   => p_cert_mbr_enrollment_id,
526   p_object_version_number    => p_object_version_number
527   );
528 
529 
530   begin
534     );
531   OTA_CERT_MBR_ENROLLMENT_bk3.delete_cert_mbr_enrollment_a
532   (p_cert_mbr_enrollment_id             => p_cert_mbr_enrollment_id
533     ,p_object_version_number        => p_object_version_number
535   exception
536     when hr_api.cannot_find_prog_unit then
537       hr_api.cannot_find_prog_unit_error
538         (p_module_name => 'DELETE_cert_mbr_enrollment'
539         ,p_hook_type   => 'AP'
540         );
541   end;
542 
543   --
544   -- When in validation only mode raise the Validate_Enabled exception
545   --
546   if p_validate then
547     raise hr_api.validate_enabled;
548   end if;
549   --
550   -- Set all output arguments
551   --
552   --
553   hr_utility.set_location(' Leaving:'||l_proc, 170);
554 exception
555   when hr_api.validate_enabled then
556     --
557     -- As the Validate_Enabled exception has been raised
558     -- we must rollback to the savepoint
559     --
560     rollback to DELETE_cert_mbr_enrollment;
561     --
562     -- Only set output warning arguments
563     -- (Any key or derived arguments must be set to null
564     -- when validation only mode is being used.)
565     --
566     hr_utility.set_location(' Leaving:'||l_proc, 180);
567   when others then
568     --
569     -- A validation or unexpected error has occured
570     --
571     rollback to DELETE_cert_mbr_enrollment;
572     hr_utility.set_location(' Leaving:'||l_proc, 190);
573     raise;
574 end delete_cert_mbr_enrollment;
575 --
576 end OTA_CERT_MBR_ENROLLMENT_api;