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