[Home] [Help]
PACKAGE BODY: APPS.OTA_OFFERING_SWI
Source
1 PACKAGE BODY OTA_OFFERING_SWI As
2 /* $Header: otoffswi.pkb 120.0.12000000.2 2007/02/06 15:23:37 vkkolla noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'ota_offering_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------------< create_offering >---------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_offering
12 (p_validate in number default hr_api.g_false_num
13 ,p_effective_date in date
14 ,p_business_group_id in number
15 ,p_name in varchar2
16 ,p_start_date in date
17 ,p_activity_version_id in number default null
18 ,p_end_date in date default null
19 ,p_owner_id in number default null
20 ,p_delivery_mode_id in number default null
21 ,p_language_id in number default null
22 ,p_duration in number default null
23 ,p_duration_units in varchar2 default null
24 ,p_learning_object_id in number default null
25 ,p_player_toolbar_flag in varchar2 default null
26 ,p_player_toolbar_bitset in number default null
27 ,p_player_new_window_flag in varchar2 default null
28 ,p_maximum_attendees in number default null
29 ,p_maximum_internal_attendees in number default null
30 ,p_minimum_attendees in number default null
31 ,p_actual_cost in number default null
32 ,p_budget_cost in number default null
33 ,p_budget_currency_code in varchar2 default null
34 ,p_price_basis in varchar2 default null
35 ,p_currency_code in varchar2 default null
36 ,p_standard_price in number default null
37 ,p_attribute_category in varchar2 default null
38 ,p_attribute1 in varchar2 default null
39 ,p_attribute2 in varchar2 default null
40 ,p_attribute3 in varchar2 default null
41 ,p_attribute4 in varchar2 default null
42 ,p_attribute5 in varchar2 default null
43 ,p_attribute6 in varchar2 default null
44 ,p_attribute7 in varchar2 default null
45 ,p_attribute8 in varchar2 default null
46 ,p_attribute9 in varchar2 default null
47 ,p_attribute10 in varchar2 default null
48 ,p_attribute11 in varchar2 default null
49 ,p_attribute12 in varchar2 default null
50 ,p_attribute13 in varchar2 default null
51 ,p_attribute14 in varchar2 default null
52 ,p_attribute15 in varchar2 default null
53 ,p_attribute16 in varchar2 default null
54 ,p_attribute17 in varchar2 default null
55 ,p_attribute18 in varchar2 default null
56 ,p_attribute19 in varchar2 default null
57 ,p_attribute20 in varchar2 default null
58 ,p_offering_id in number
59 ,p_object_version_number out nocopy number
60 ,p_return_status out nocopy varchar2
61 ,p_data_source in varchar2 default null
62 ,p_vendor_id in number default null
63 ,p_description in varchar2 default null
64 ,p_competency_update_level in varchar2 default null
65 ,p_language_code in varchar2 default null -- 2733966 enh natural_languages
66
67 ) is
68 --
69 -- Variables for API Boolean parameters
70 l_validate boolean;
71 --
72 -- Variables for IN/OUT parameters
73 --
74 -- Other variables
75 l_offering_id number;
76 l_proc varchar2(72) := g_package ||'create_offering';
77 Begin
78 hr_utility.set_location(' Entering:' || l_proc,10);
79 --
80 -- Issue a savepoint
81 --
82 savepoint create_offering_swi;
83 --
84 -- Initialise Multiple Message Detection
85 --
86 hr_multi_message.enable_message_list;
87 --
88 -- Remember IN OUT parameter IN values
89 --
90 --
91 -- Convert constant values to their corresponding boolean value
92 --
93 l_validate :=
94 hr_api.constant_to_boolean
95 (p_constant_value => p_validate);
96 --
97 -- Register Surrogate ID or user key values
98 --
99 ota_off_ins.set_base_key_value(p_offering_id => p_offering_id );
100 --
101 -- Call API
102 --
103 ota_offering_api.create_offering
104 (p_validate => l_validate
105 ,p_effective_date => p_effective_date
106 ,p_business_group_id => p_business_group_id
107 ,p_name => p_name
108 ,p_start_date => p_start_date
109 ,p_activity_version_id => p_activity_version_id
110 ,p_end_date => p_end_date
111 ,p_owner_id => p_owner_id
112 ,p_delivery_mode_id => p_delivery_mode_id
113 ,p_language_id => p_language_id
114 ,p_duration => p_duration
115 ,p_duration_units => p_duration_units
116 ,p_learning_object_id => p_learning_object_id
117 ,p_player_toolbar_flag => p_player_toolbar_flag
118 ,p_player_toolbar_bitset => p_player_toolbar_bitset
119 ,p_player_new_window_flag => p_player_new_window_flag
120 ,p_maximum_attendees => p_maximum_attendees
121 ,p_maximum_internal_attendees => p_maximum_internal_attendees
122 ,p_minimum_attendees => p_minimum_attendees
123 ,p_actual_cost => p_actual_cost
124 ,p_budget_cost => p_budget_cost
125 ,p_budget_currency_code => p_budget_currency_code
126 ,p_price_basis => p_price_basis
127 ,p_currency_code => p_currency_code
128 ,p_standard_price => p_standard_price
129 ,p_attribute_category => p_attribute_category
130 ,p_attribute1 => p_attribute1
131 ,p_attribute2 => p_attribute2
132 ,p_attribute3 => p_attribute3
133 ,p_attribute4 => p_attribute4
134 ,p_attribute5 => p_attribute5
135 ,p_attribute6 => p_attribute6
136 ,p_attribute7 => p_attribute7
137 ,p_attribute8 => p_attribute8
138 ,p_attribute9 => p_attribute9
139 ,p_attribute10 => p_attribute10
140 ,p_attribute11 => p_attribute11
141 ,p_attribute12 => p_attribute12
142 ,p_attribute13 => p_attribute13
143 ,p_attribute14 => p_attribute14
144 ,p_attribute15 => p_attribute15
145 ,p_attribute16 => p_attribute16
146 ,p_attribute17 => p_attribute17
147 ,p_attribute18 => p_attribute18
148 ,p_attribute19 => p_attribute19
149 ,p_attribute20 => p_attribute20
150 ,p_offering_id => l_offering_id
151 ,p_object_version_number => p_object_version_number
152 ,p_data_source => p_data_source
153 ,p_vendor_id => p_vendor_id
154 ,p_description => p_description
155 ,p_competency_update_level => p_competency_update_level
156 ,p_language_code => p_language_code -- 2733966
157 );
158 --
159 -- Convert API warning boolean parameter values to specific
160 -- messages and add them to Multiple Message List
161 --
162 --
163 -- Convert API non-warning boolean parameter values
164 --
165 --
166 -- Derive the API return status value based on whether
167 -- messages of any type exist in the Multiple Message List.
168 -- Also disable Multiple Message Detection.
169 --
170 p_return_status := hr_multi_message.get_return_status_disable;
171 hr_utility.set_location(' Leaving:' || l_proc,20);
172 --
173 exception
174 when hr_multi_message.error_message_exist then
175 --
176 -- Catch the Multiple Message List exception which
177 -- indicates API processing has been aborted because
178 -- at least one message exists in the list.
179 --
180 rollback to create_offering_swi;
181 --
182 -- Reset IN OUT parameters and set OUT parameters
183 --
184 p_object_version_number := null;
185 p_return_status := hr_multi_message.get_return_status_disable;
186 hr_utility.set_location(' Leaving:' || l_proc, 30);
187 when others then
188 --
189 -- When Multiple Message Detection is enabled catch
190 -- any Application specific or other unexpected
191 -- exceptions. Adding appropriate details to the
192 -- Multiple Message List. Otherwise re-raise the
193 -- error.
194 --
195 rollback to create_offering_swi;
196 if hr_multi_message.unexpected_error_add(l_proc) then
197 hr_utility.set_location(' Leaving:' || l_proc,40);
198 raise;
199 end if;
200 --
201 -- Reset IN OUT and set OUT parameters
202 --
203 p_object_version_number := null;
204 p_return_status := hr_multi_message.get_return_status_disable;
205 hr_utility.set_location(' Leaving:' || l_proc,50);
206 end create_offering;
207 -- ----------------------------------------------------------------------------
208 -- |----------------------------< delete_offering >---------------------------|
209 -- ----------------------------------------------------------------------------
210 PROCEDURE delete_offering
211 (p_validate in number default hr_api.g_false_num
212 ,p_offering_id in number
213 ,p_object_version_number in number
214 ,p_return_status out nocopy varchar2
215 ) is
216 --
217 -- Variables for API Boolean parameters
218 l_validate boolean;
219 --
220 -- Variables for IN/OUT parameters
221 --
222 -- Other variables
223 l_proc varchar2(72) := g_package ||'delete_offering';
224 Begin
225 hr_utility.set_location(' Entering:' || l_proc,10);
226 --
227 -- Issue a savepoint
228 --
229 savepoint delete_offering_swi;
230 --
231 -- Initialise Multiple Message Detection
232 --
233 hr_multi_message.enable_message_list;
234 --
235 -- Remember IN OUT parameter IN values
236 --
237 --
238 -- Convert constant values to their corresponding boolean value
239 --
240 l_validate :=
241 hr_api.constant_to_boolean
242 (p_constant_value => p_validate);
243 --
244 -- Register Surrogate ID or user key values
245 --
246 --
247 -- Call API
248 --
249 ota_offering_api.delete_offering
250 (p_validate => l_validate
251 ,p_offering_id => p_offering_id
252 ,p_object_version_number => p_object_version_number
253 );
254 --
255 -- Convert API warning boolean parameter values to specific
256 -- messages and add them to Multiple Message List
257 --
258 --
259 -- Convert API non-warning boolean parameter values
260 --
261 --
262 -- Derive the API return status value based on whether
263 -- messages of any type exist in the Multiple Message List.
264 -- Also disable Multiple Message Detection.
265 --
266 p_return_status := hr_multi_message.get_return_status_disable;
267 hr_utility.set_location(' Leaving:' || l_proc,20);
268 --
269 exception
270 when hr_multi_message.error_message_exist then
271 --
272 -- Catch the Multiple Message List exception which
273 -- indicates API processing has been aborted because
274 -- at least one message exists in the list.
275 --
276 rollback to delete_offering_swi;
277 --
278 -- Reset IN OUT parameters and set OUT parameters
279 --
280 p_return_status := hr_multi_message.get_return_status_disable;
281 hr_utility.set_location(' Leaving:' || l_proc, 30);
282 when others then
283 --
284 -- When Multiple Message Detection is enabled catch
285 -- any Application specific or other unexpected
286 -- exceptions. Adding appropriate details to the
287 -- Multiple Message List. Otherwise re-raise the
288 -- error.
289 --
290 rollback to delete_offering_swi;
291 if hr_multi_message.unexpected_error_add(l_proc) then
292 hr_utility.set_location(' Leaving:' || l_proc,40);
293 raise;
294 end if;
295 --
296 -- Reset IN OUT and set OUT parameters
297 --
298 p_return_status := hr_multi_message.get_return_status_disable;
299 hr_utility.set_location(' Leaving:' || l_proc,50);
300 end delete_offering;
301 -- ----------------------------------------------------------------------------
302 -- |----------------------------< update_offering >---------------------------|
303 -- ----------------------------------------------------------------------------
304 PROCEDURE update_offering
305 (p_validate in number default hr_api.g_false_num
306 ,p_effective_date in date
307 ,p_offering_id in number
308 ,p_object_version_number in out nocopy number
309 ,p_business_group_id in number default hr_api.g_number
310 ,p_name in varchar2 default hr_api.g_varchar2
311 ,p_start_date in date default hr_api.g_date
312 ,p_activity_version_id in number default hr_api.g_number
313 ,p_end_date in date default hr_api.g_date
314 ,p_owner_id in number default hr_api.g_number
315 ,p_delivery_mode_id in number default hr_api.g_number
316 ,p_language_id in number default hr_api.g_number
317 ,p_duration in number default hr_api.g_number
318 ,p_duration_units in varchar2 default hr_api.g_varchar2
319 ,p_learning_object_id in number default hr_api.g_number
320 ,p_player_toolbar_flag in varchar2 default hr_api.g_varchar2
321 ,p_player_toolbar_bitset in number default hr_api.g_number
322 ,p_player_new_window_flag in varchar2 default hr_api.g_varchar2
323 ,p_maximum_attendees in number default hr_api.g_number
324 ,p_maximum_internal_attendees in number default hr_api.g_number
325 ,p_minimum_attendees in number default hr_api.g_number
326 ,p_actual_cost in number default hr_api.g_number
327 ,p_budget_cost in number default hr_api.g_number
328 ,p_budget_currency_code in varchar2 default hr_api.g_varchar2
329 ,p_price_basis in varchar2 default hr_api.g_varchar2
330 ,p_currency_code in varchar2 default hr_api.g_varchar2
331 ,p_standard_price in number default hr_api.g_number
332 ,p_attribute_category in varchar2 default hr_api.g_varchar2
333 ,p_attribute1 in varchar2 default hr_api.g_varchar2
334 ,p_attribute2 in varchar2 default hr_api.g_varchar2
335 ,p_attribute3 in varchar2 default hr_api.g_varchar2
336 ,p_attribute4 in varchar2 default hr_api.g_varchar2
337 ,p_attribute5 in varchar2 default hr_api.g_varchar2
338 ,p_attribute6 in varchar2 default hr_api.g_varchar2
339 ,p_attribute7 in varchar2 default hr_api.g_varchar2
340 ,p_attribute8 in varchar2 default hr_api.g_varchar2
341 ,p_attribute9 in varchar2 default hr_api.g_varchar2
342 ,p_attribute10 in varchar2 default hr_api.g_varchar2
343 ,p_attribute11 in varchar2 default hr_api.g_varchar2
344 ,p_attribute12 in varchar2 default hr_api.g_varchar2
345 ,p_attribute13 in varchar2 default hr_api.g_varchar2
346 ,p_attribute14 in varchar2 default hr_api.g_varchar2
347 ,p_attribute15 in varchar2 default hr_api.g_varchar2
348 ,p_attribute16 in varchar2 default hr_api.g_varchar2
349 ,p_attribute17 in varchar2 default hr_api.g_varchar2
350 ,p_attribute18 in varchar2 default hr_api.g_varchar2
351 ,p_attribute19 in varchar2 default hr_api.g_varchar2
352 ,p_attribute20 in varchar2 default hr_api.g_varchar2
353 ,p_return_status out nocopy varchar2
354 ,p_data_source in varchar2 default hr_api.g_varchar2
355 ,p_vendor_id in number default hr_api.g_number
356 ,p_description in varchar2 default hr_api.g_varchar2
357 ,p_competency_update_level in varchar2 default hr_api.g_varchar2
358 ,p_language_code in varchar2 default hr_api.g_varchar2 -- 2733966
359
360 ) is
361 --
362 -- Variables for API Boolean parameters
363 l_validate boolean;
364 --
365 -- Variables for IN/OUT parameters
366 l_object_version_number number;
367 --
368 -- Other variables
369 l_proc varchar2(72) := g_package ||'update_offering';
370 Begin
371 hr_utility.set_location(' Entering:' || l_proc,10);
372 --
373 -- Issue a savepoint
374 --
375 savepoint update_offering_swi;
376 --
377 -- Initialise Multiple Message Detection
378 --
379 hr_multi_message.enable_message_list;
380 --
381 -- Remember IN OUT parameter IN values
382 --
383 l_object_version_number := p_object_version_number;
384 --
385 -- Convert constant values to their corresponding boolean value
386 --
387 l_validate :=
388 hr_api.constant_to_boolean
389 (p_constant_value => p_validate);
390 --
391 -- Register Surrogate ID or user key values
392 --
393 --
394 -- Call API
395 --
396 ota_offering_api.update_offering
397 (p_validate => l_validate
398 ,p_effective_date => p_effective_date
399 ,p_offering_id => p_offering_id
400 ,p_object_version_number => p_object_version_number
401 ,p_business_group_id => p_business_group_id
402 ,p_name => p_name
403 ,p_start_date => p_start_date
404 ,p_activity_version_id => p_activity_version_id
405 ,p_end_date => p_end_date
406 ,p_owner_id => p_owner_id
407 ,p_delivery_mode_id => p_delivery_mode_id
408 ,p_language_id => p_language_id
409 ,p_duration => p_duration
410 ,p_duration_units => p_duration_units
411 ,p_learning_object_id => p_learning_object_id
412 ,p_player_toolbar_flag => p_player_toolbar_flag
413 ,p_player_toolbar_bitset => p_player_toolbar_bitset
414 ,p_player_new_window_flag => p_player_new_window_flag
415 ,p_maximum_attendees => p_maximum_attendees
416 ,p_maximum_internal_attendees => p_maximum_internal_attendees
417 ,p_minimum_attendees => p_minimum_attendees
418 ,p_actual_cost => p_actual_cost
419 ,p_budget_cost => p_budget_cost
420 ,p_budget_currency_code => p_budget_currency_code
421 ,p_price_basis => p_price_basis
422 ,p_currency_code => p_currency_code
423 ,p_standard_price => p_standard_price
424 ,p_attribute_category => p_attribute_category
425 ,p_attribute1 => p_attribute1
426 ,p_attribute2 => p_attribute2
427 ,p_attribute3 => p_attribute3
428 ,p_attribute4 => p_attribute4
429 ,p_attribute5 => p_attribute5
430 ,p_attribute6 => p_attribute6
431 ,p_attribute7 => p_attribute7
432 ,p_attribute8 => p_attribute8
433 ,p_attribute9 => p_attribute9
434 ,p_attribute10 => p_attribute10
435 ,p_attribute11 => p_attribute11
436 ,p_attribute12 => p_attribute12
437 ,p_attribute13 => p_attribute13
438 ,p_attribute14 => p_attribute14
439 ,p_attribute15 => p_attribute15
440 ,p_attribute16 => p_attribute16
441 ,p_attribute17 => p_attribute17
442 ,p_attribute18 => p_attribute18
443 ,p_attribute19 => p_attribute19
444 ,p_attribute20 => p_attribute20
445 ,p_data_source => p_data_source
446 ,p_vendor_id => p_vendor_id
447 ,p_description => p_description
448 ,p_competency_update_level => p_competency_update_level
449 ,p_language_code => p_language_code -- 2733966
450 );
451 --
452 -- Convert API warning boolean parameter values to specific
453 -- messages and add them to Multiple Message List
454 --
455 --
456 -- Convert API non-warning boolean parameter values
457 --
458 --
459 -- Derive the API return status value based on whether
460 -- messages of any type exist in the Multiple Message List.
461 -- Also disable Multiple Message Detection.
462 --
463 p_return_status := hr_multi_message.get_return_status_disable;
464 hr_utility.set_location(' Leaving:' || l_proc,20);
465 --
466 exception
467 when hr_multi_message.error_message_exist then
468 --
469 -- Catch the Multiple Message List exception which
470 -- indicates API processing has been aborted because
471 -- at least one message exists in the list.
472 --
473 rollback to update_offering_swi;
474 --
475 -- Reset IN OUT parameters and set OUT parameters
476 --
477 p_object_version_number := l_object_version_number;
478 p_return_status := hr_multi_message.get_return_status_disable;
479 hr_utility.set_location(' Leaving:' || l_proc, 30);
480 when others then
481 --
482 -- When Multiple Message Detection is enabled catch
483 -- any Application specific or other unexpected
484 -- exceptions. Adding appropriate details to the
485 -- Multiple Message List. Otherwise re-raise the
486 -- error.
487 --
488 rollback to update_offering_swi;
489 if hr_multi_message.unexpected_error_add(l_proc) then
490 hr_utility.set_location(' Leaving:' || l_proc,40);
491 raise;
492 end if;
493 --
494 -- Reset IN OUT and set OUT parameters
495 --
496 p_object_version_number := l_object_version_number;
497 p_return_status := hr_multi_message.get_return_status_disable;
498 hr_utility.set_location(' Leaving:' || l_proc,50);
499 end update_offering;
500 end ota_offering_swi;