DBA Data[Home] [Help]

PACKAGE BODY: APPS.OTA_CERT_CATEGORY_SWI

Source


1 Package Body ota_cert_category_swi As
2 /* $Header: otcciswi.pkb 120.1 2005/07/21 15:16 estreacy noship $ */
3 --
4 -- Package variables
5 --
6 g_package  varchar2(33) := 'ota_cert_category_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------< create_cert_cat_inclusion >----------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_cert_cat_inclusion
12   (p_validate                     in     number    default hr_api.g_false_num
13   ,p_effective_date               in     date
14   ,p_certification_id             in     number
15   ,p_category_usage_id            in     number
16   ,p_object_version_number           out nocopy number
17   ,p_start_date_active            in     date      default null
18   ,p_end_date_active              in     date      default null
19   ,p_primary_flag                 in     varchar2  default null
20   ,p_attribute_category           in     varchar2  default null
21   ,p_attribute1                   in     varchar2  default null
22   ,p_attribute2                   in     varchar2  default null
23   ,p_attribute3                   in     varchar2  default null
24   ,p_attribute4                   in     varchar2  default null
25   ,p_attribute5                   in     varchar2  default null
26   ,p_attribute6                   in     varchar2  default null
27   ,p_attribute7                   in     varchar2  default null
28   ,p_attribute8                   in     varchar2  default null
29   ,p_attribute9                   in     varchar2  default null
30   ,p_attribute10                  in     varchar2  default null
31   ,p_attribute11                  in     varchar2  default null
32   ,p_attribute12                  in     varchar2  default null
33   ,p_attribute13                  in     varchar2  default null
34   ,p_attribute14                  in     varchar2  default null
35   ,p_attribute15                  in     varchar2  default null
36   ,p_attribute16                  in     varchar2  default null
37   ,p_attribute17                  in     varchar2  default null
38   ,p_attribute18                  in     varchar2  default null
39   ,p_attribute19                  in     varchar2  default null
40   ,p_attribute20                  in     varchar2  default null
41   ,p_return_status                   out nocopy varchar2
42   ) is
43   --
44   -- Variables for API Boolean parameters
45   l_validate                      boolean;
46   --
47   -- Variables for IN/OUT parameters
48   --
49   -- Other variables
50   l_certification_id             number;
51   l_category_usage_id            number;
52   l_proc    varchar2(72) := g_package ||'create_cert_cat_inclusion';
53 Begin
54   hr_utility.set_location(' Entering:' || l_proc,10);
55   --
56   -- Issue a savepoint
57   --
58   savepoint create_cert_cat_inclusion_swi;
59   --
60   -- Initialise Multiple Message Detection
61   --
62   hr_multi_message.enable_message_list;
63   --
64   -- Remember IN OUT parameter IN values
65   --
66   --
67   -- Convert constant values to their corresponding boolean value
68   --
69   l_validate :=
70     hr_api.constant_to_boolean
71       (p_constant_value => p_validate);
72   --
73   -- Register Surrogate ID or user key values
74   --
75   ota_cci_ins.set_base_key_value
76     (p_category_usage_id => p_category_usage_id
77     ,p_certification_id => p_certification_id
78     );
79   --
80   -- Call API
81   --
82   ota_cert_category_api.create_cert_cat_inclusion
83     (p_validate                     => l_validate
84     ,p_effective_date               => p_effective_date
85     ,p_certification_id             => p_certification_id
86     ,p_category_usage_id            => p_category_usage_id
87     ,p_object_version_number        => p_object_version_number
88     ,p_start_date_active            => p_start_date_active
89     ,p_end_date_active              => p_end_date_active
90     ,p_primary_flag                 => p_primary_flag
91     ,p_attribute_category           => p_attribute_category
92     ,p_attribute1                   => p_attribute1
93     ,p_attribute2                   => p_attribute2
94     ,p_attribute3                   => p_attribute3
95     ,p_attribute4                   => p_attribute4
96     ,p_attribute5                   => p_attribute5
97     ,p_attribute6                   => p_attribute6
98     ,p_attribute7                   => p_attribute7
99     ,p_attribute8                   => p_attribute8
100     ,p_attribute9                   => p_attribute9
101     ,p_attribute10                  => p_attribute10
102     ,p_attribute11                  => p_attribute11
103     ,p_attribute12                  => p_attribute12
104     ,p_attribute13                  => p_attribute13
105     ,p_attribute14                  => p_attribute14
106     ,p_attribute15                  => p_attribute15
107     ,p_attribute16                  => p_attribute16
108     ,p_attribute17                  => p_attribute17
109     ,p_attribute18                  => p_attribute18
110     ,p_attribute19                  => p_attribute19
111     ,p_attribute20                  => p_attribute20
112     );
113   --
114   -- Convert API warning boolean parameter values to specific
115   -- messages and add them to Multiple Message List
116   --
117   --
118   -- Convert API non-warning boolean parameter values
119   --
120   --
121   -- Derive the API return status value based on whether
122   -- messages of any type exist in the Multiple Message List.
123   -- Also disable Multiple Message Detection.
124   --
125   p_return_status := hr_multi_message.get_return_status_disable;
126   hr_utility.set_location(' Leaving:' || l_proc,20);
127   --
128 exception
129   when hr_multi_message.error_message_exist then
130     --
131     -- Catch the Multiple Message List exception which
132     -- indicates API processing has been aborted because
133     -- at least one message exists in the list.
134     --
135     rollback to create_cert_cat_inclusion_swi;
136     --
137     -- Reset IN OUT parameters and set OUT parameters
138     --
139     p_object_version_number        := null;
140     p_return_status := hr_multi_message.get_return_status_disable;
141     hr_utility.set_location(' Leaving:' || l_proc, 30);
142   when others then
143     --
144     -- When Multiple Message Detection is enabled catch
145     -- any Application specific or other unexpected
146     -- exceptions.  Adding appropriate details to the
147     -- Multiple Message List.  Otherwise re-raise the
148     -- error.
149     --
150     rollback to create_cert_cat_inclusion_swi;
151     if hr_multi_message.unexpected_error_add(l_proc) then
152        hr_utility.set_location(' Leaving:' || l_proc,40);
153        raise;
154     end if;
155     --
156     -- Reset IN OUT and set OUT parameters
157     --
158     p_object_version_number        := null;
159     p_return_status := hr_multi_message.get_return_status_disable;
160     hr_utility.set_location(' Leaving:' || l_proc,50);
161 end create_cert_cat_inclusion;
162 -- ----------------------------------------------------------------------------
163 -- |-----------------------< update_cert_cat_inclusion >----------------------|
164 -- ----------------------------------------------------------------------------
165 PROCEDURE update_cert_cat_inclusion
166   (p_validate                     in     number    default hr_api.g_false_num
167   ,p_effective_date               in     date
168   ,p_certification_id             in     number
169   ,p_object_version_number        in out nocopy number
170   ,p_attribute_category           in     varchar2  default hr_api.g_varchar2
171   ,p_attribute1                   in     varchar2  default hr_api.g_varchar2
172   ,p_attribute2                   in     varchar2  default hr_api.g_varchar2
173   ,p_attribute3                   in     varchar2  default hr_api.g_varchar2
174   ,p_attribute4                   in     varchar2  default hr_api.g_varchar2
175   ,p_attribute5                   in     varchar2  default hr_api.g_varchar2
176   ,p_attribute6                   in     varchar2  default hr_api.g_varchar2
177   ,p_attribute7                   in     varchar2  default hr_api.g_varchar2
178   ,p_attribute8                   in     varchar2  default hr_api.g_varchar2
179   ,p_attribute9                   in     varchar2  default hr_api.g_varchar2
180   ,p_attribute10                  in     varchar2  default hr_api.g_varchar2
181   ,p_attribute11                  in     varchar2  default hr_api.g_varchar2
182   ,p_attribute12                  in     varchar2  default hr_api.g_varchar2
183   ,p_attribute13                  in     varchar2  default hr_api.g_varchar2
184   ,p_attribute14                  in     varchar2  default hr_api.g_varchar2
185   ,p_attribute15                  in     varchar2  default hr_api.g_varchar2
186   ,p_attribute16                  in     varchar2  default hr_api.g_varchar2
187   ,p_attribute17                  in     varchar2  default hr_api.g_varchar2
188   ,p_attribute18                  in     varchar2  default hr_api.g_varchar2
189   ,p_attribute19                  in     varchar2  default hr_api.g_varchar2
190   ,p_attribute20                  in     varchar2  default hr_api.g_varchar2
191   ,p_start_date_active            in     date      default hr_api.g_date
192   ,p_end_date_active              in     date      default hr_api.g_date
193   ,p_primary_flag                 in     varchar2  default hr_api.g_varchar2
194   ,p_category_usage_id            in     number
195   ,p_return_status                   out nocopy varchar2
196   ) is
197   --
198   -- Variables for API Boolean parameters
199   l_validate                      boolean;
200   --
201   -- Variables for IN/OUT parameters
202   l_object_version_number         number;
203   --
204   -- Other variables
205   l_proc    varchar2(72) := g_package ||'update_cert_cat_inclusion';
206 Begin
207   hr_utility.set_location(' Entering:' || l_proc,10);
208   --
209   -- Issue a savepoint
210   --
211   savepoint update_cert_cat_inclusion_swi;
212   --
213   -- Initialise Multiple Message Detection
214   --
215   hr_multi_message.enable_message_list;
216   --
217   -- Remember IN OUT parameter IN values
218   --
219   l_object_version_number         := p_object_version_number;
220   --
221   -- Convert constant values to their corresponding boolean value
222   --
223   l_validate :=
224     hr_api.constant_to_boolean
225       (p_constant_value => p_validate);
226   --
227   -- Register Surrogate ID or user key values
228   --
229   --
230   -- Call API
231   --
232   ota_cert_category_api.update_cert_cat_inclusion
233     (p_validate                     => l_validate
234     ,p_effective_date               => p_effective_date
235     ,p_certification_id             => p_certification_id
236     ,p_object_version_number        => p_object_version_number
237     ,p_attribute_category           => p_attribute_category
238     ,p_attribute1                   => p_attribute1
239     ,p_attribute2                   => p_attribute2
240     ,p_attribute3                   => p_attribute3
241     ,p_attribute4                   => p_attribute4
242     ,p_attribute5                   => p_attribute5
243     ,p_attribute6                   => p_attribute6
244     ,p_attribute7                   => p_attribute7
245     ,p_attribute8                   => p_attribute8
246     ,p_attribute9                   => p_attribute9
247     ,p_attribute10                  => p_attribute10
248     ,p_attribute11                  => p_attribute11
249     ,p_attribute12                  => p_attribute12
250     ,p_attribute13                  => p_attribute13
251     ,p_attribute14                  => p_attribute14
252     ,p_attribute15                  => p_attribute15
253     ,p_attribute16                  => p_attribute16
254     ,p_attribute17                  => p_attribute17
255     ,p_attribute18                  => p_attribute18
256     ,p_attribute19                  => p_attribute19
257     ,p_attribute20                  => p_attribute20
258     ,p_start_date_active            => p_start_date_active
259     ,p_end_date_active              => p_end_date_active
260     ,p_primary_flag                 => p_primary_flag
261     ,p_category_usage_id            => p_category_usage_id
262     );
263   --
264   -- Convert API warning boolean parameter values to specific
265   -- messages and add them to Multiple Message List
266   --
267   --
268   -- Convert API non-warning boolean parameter values
269   --
270   --
271   -- Derive the API return status value based on whether
272   -- messages of any type exist in the Multiple Message List.
273   -- Also disable Multiple Message Detection.
274   --
275   p_return_status := hr_multi_message.get_return_status_disable;
276   hr_utility.set_location(' Leaving:' || l_proc,20);
277   --
278 exception
279   when hr_multi_message.error_message_exist then
280     --
281     -- Catch the Multiple Message List exception which
282     -- indicates API processing has been aborted because
283     -- at least one message exists in the list.
284     --
285     rollback to update_cert_cat_inclusion_swi;
286     --
287     -- Reset IN OUT parameters and set OUT parameters
288     --
289     p_object_version_number        := l_object_version_number;
290     p_return_status := hr_multi_message.get_return_status_disable;
291     hr_utility.set_location(' Leaving:' || l_proc, 30);
292   when others then
293     --
294     -- When Multiple Message Detection is enabled catch
295     -- any Application specific or other unexpected
296     -- exceptions.  Adding appropriate details to the
297     -- Multiple Message List.  Otherwise re-raise the
298     -- error.
299     --
300     rollback to update_cert_cat_inclusion_swi;
301     if hr_multi_message.unexpected_error_add(l_proc) then
302        hr_utility.set_location(' Leaving:' || l_proc,40);
303        raise;
304     end if;
305     --
306     -- Reset IN OUT and set OUT parameters
307     --
308     p_object_version_number        := l_object_version_number;
309     p_return_status := hr_multi_message.get_return_status_disable;
310     hr_utility.set_location(' Leaving:' || l_proc,50);
311 end update_cert_cat_inclusion;
312 -- ----------------------------------------------------------------------------
313 -- |-----------------------< delete_cert_cat_inclusion >----------------------|
314 -- ----------------------------------------------------------------------------
315 PROCEDURE delete_cert_cat_inclusion
316   (p_certification_id             in     number
317   ,p_category_usage_id            in     varchar2
318   ,p_object_version_number        in     number
319   ,p_validate                     in     number    default hr_api.g_false_num
320   ,p_return_status                   out nocopy varchar2
321   ) is
322   --
323   -- Variables for API Boolean parameters
324   l_validate                      boolean;
325   --
326   -- Variables for IN/OUT parameters
327   --
328   -- Other variables
329   l_proc    varchar2(72) := g_package ||'delete_cert_cat_inclusion';
330 Begin
331   hr_utility.set_location(' Entering:' || l_proc,10);
332   --
333   -- Issue a savepoint
334   --
335   savepoint delete_cert_cat_inclusion_swi;
336   --
337   -- Initialise Multiple Message Detection
338   --
339   hr_multi_message.enable_message_list;
340   --
341   -- Remember IN OUT parameter IN values
342   --
343   --
344   -- Convert constant values to their corresponding boolean value
345   --
346   l_validate :=
347     hr_api.constant_to_boolean
348       (p_constant_value => p_validate);
349   --
350   -- Register Surrogate ID or user key values
351   --
352   --
353   -- Call API
354   --
355   ota_cert_category_api.delete_cert_cat_inclusion
356     (p_certification_id             => p_certification_id
357     ,p_category_usage_id            => p_category_usage_id
358     ,p_object_version_number        => p_object_version_number
359     ,p_validate                     => l_validate
360     );
361   --
362   -- Convert API warning boolean parameter values to specific
363   -- messages and add them to Multiple Message List
364   --
365   --
366   -- Convert API non-warning boolean parameter values
367   --
368   --
369   -- Derive the API return status value based on whether
370   -- messages of any type exist in the Multiple Message List.
371   -- Also disable Multiple Message Detection.
372   --
373   p_return_status := hr_multi_message.get_return_status_disable;
374   hr_utility.set_location(' Leaving:' || l_proc,20);
375   --
376 exception
377   when hr_multi_message.error_message_exist then
378     --
379     -- Catch the Multiple Message List exception which
380     -- indicates API processing has been aborted because
381     -- at least one message exists in the list.
382     --
383     rollback to delete_cert_cat_inclusion_swi;
384     --
385     -- Reset IN OUT parameters and set OUT parameters
386     --
387     p_return_status := hr_multi_message.get_return_status_disable;
388     hr_utility.set_location(' Leaving:' || l_proc, 30);
389   when others then
390     --
391     -- When Multiple Message Detection is enabled catch
392     -- any Application specific or other unexpected
393     -- exceptions.  Adding appropriate details to the
394     -- Multiple Message List.  Otherwise re-raise the
395     -- error.
396     --
397     rollback to delete_cert_cat_inclusion_swi;
398     if hr_multi_message.unexpected_error_add(l_proc) then
399        hr_utility.set_location(' Leaving:' || l_proc,40);
400        raise;
401     end if;
402     --
403     -- Reset IN OUT and set OUT parameters
404     --
405     p_return_status := hr_multi_message.get_return_status_disable;
406     hr_utility.set_location(' Leaving:' || l_proc,50);
407 end delete_cert_cat_inclusion;
408 end ota_cert_category_swi;