[Home] [Help]
PACKAGE BODY: APPS.OTA_LP_ENROLLMENT_SWI
Source
1 Package Body ota_lp_enrollment_swi As
2 /* $Header: otlpeswi.pkb 120.4 2005/06/14 05:58 mcaruso noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'ota_lp_enrollment_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_lp_enrollment >-------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_lp_enrollment
12 (p_effective_date in date
13 ,p_validate in number default hr_api.g_false_num
14 ,p_learning_path_id in number
15 ,p_person_id in number default null
16 ,p_contact_id in number default null
17 ,p_path_status_code in varchar2
18 ,p_enrollment_source_code in varchar2
19 ,p_no_of_mandatory_courses in number default null
20 ,p_no_of_completed_courses in number default null
21 ,p_completion_target_date in date default null
22 ,p_completion_date in date default null
23 ,p_creator_person_id in number default null
24 ,p_business_group_id in number
25 ,p_attribute_category in varchar2 default null
26 ,p_attribute1 in varchar2 default null
27 ,p_attribute2 in varchar2 default null
28 ,p_attribute3 in varchar2 default null
29 ,p_attribute4 in varchar2 default null
30 ,p_attribute5 in varchar2 default null
31 ,p_attribute6 in varchar2 default null
32 ,p_attribute7 in varchar2 default null
33 ,p_attribute8 in varchar2 default null
34 ,p_attribute9 in varchar2 default null
35 ,p_attribute10 in varchar2 default null
36 ,p_attribute11 in varchar2 default null
37 ,p_attribute12 in varchar2 default null
38 ,p_attribute13 in varchar2 default null
39 ,p_attribute14 in varchar2 default null
40 ,p_attribute15 in varchar2 default null
41 ,p_attribute16 in varchar2 default null
42 ,p_attribute17 in varchar2 default null
43 ,p_attribute18 in varchar2 default null
44 ,p_attribute19 in varchar2 default null
45 ,p_attribute20 in varchar2 default null
46 ,p_attribute21 in varchar2 default null
47 ,p_attribute22 in varchar2 default null
48 ,p_attribute23 in varchar2 default null
49 ,p_attribute24 in varchar2 default null
50 ,p_attribute25 in varchar2 default null
51 ,p_attribute26 in varchar2 default null
52 ,p_attribute27 in varchar2 default null
53 ,p_attribute28 in varchar2 default null
54 ,p_attribute29 in varchar2 default null
55 ,p_attribute30 in varchar2 default null
56 ,p_is_history_flag in varchar2 default 'N'
57 ,p_lp_enrollment_id in number
58 ,p_object_version_number out nocopy number
59 ,p_return_status out nocopy varchar2
60 ) is
61 --
62 -- Variables for API Boolean parameters
63 l_validate boolean;
64 --
65 -- Variables for IN/OUT parameters
66 --
67 -- Other variables
68 l_lp_enrollment_id number;
69 l_proc varchar2(72) := g_package ||'create_lp_enrollment';
70 Begin
71 hr_utility.set_location(' Entering:' || l_proc,10);
72 --
73 -- Issue a savepoint
74 --
75 savepoint create_lp_enrollment_swi;
76 --
77 -- Initialise Multiple Message Detection
78 --
79 hr_multi_message.enable_message_list;
80 --
81 -- Remember IN OUT parameter IN values
82 --
83 --
84 -- Convert constant values to their corresponding boolean value
85 --
86 l_validate :=
87 hr_api.constant_to_boolean
88 (p_constant_value => p_validate);
89 --
90 -- Register Surrogate ID or user key values
91 --
92 ota_lpe_ins.set_base_key_value
93 (p_lp_enrollment_id => p_lp_enrollment_id
94 );
95 --
96 -- Call API
97 --
98 ota_lp_enrollment_api.create_lp_enrollment
99 (p_effective_date => p_effective_date
100 ,p_validate => l_validate
101 ,p_learning_path_id => p_learning_path_id
102 ,p_person_id => p_person_id
103 ,p_contact_id => p_contact_id
104 ,p_path_status_code => p_path_status_code
105 ,p_enrollment_source_code =>p_enrollment_source_code
106 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
107 ,p_no_of_completed_courses => p_no_of_completed_courses
108 ,p_completion_target_date => p_completion_target_date
109 ,p_completion_date => p_completion_date
110 ,p_creator_person_id => p_creator_person_id
111 ,p_business_group_id => p_business_group_id
112 ,p_attribute_category => p_attribute_category
113 ,p_attribute1 => p_attribute1
114 ,p_attribute2 => p_attribute2
115 ,p_attribute3 => p_attribute3
116 ,p_attribute4 => p_attribute4
117 ,p_attribute5 => p_attribute5
118 ,p_attribute6 => p_attribute6
119 ,p_attribute7 => p_attribute7
120 ,p_attribute8 => p_attribute8
121 ,p_attribute9 => p_attribute9
122 ,p_attribute10 => p_attribute10
123 ,p_attribute11 => p_attribute11
124 ,p_attribute12 => p_attribute12
125 ,p_attribute13 => p_attribute13
126 ,p_attribute14 => p_attribute14
127 ,p_attribute15 => p_attribute15
128 ,p_attribute16 => p_attribute16
129 ,p_attribute17 => p_attribute17
130 ,p_attribute18 => p_attribute18
131 ,p_attribute19 => p_attribute19
132 ,p_attribute20 => p_attribute20
133 ,p_attribute21 => p_attribute21
134 ,p_attribute22 => p_attribute22
135 ,p_attribute23 => p_attribute23
136 ,p_attribute24 => p_attribute24
137 ,p_attribute25 => p_attribute25
138 ,p_attribute26 => p_attribute26
139 ,p_attribute27 => p_attribute27
140 ,p_attribute28 => p_attribute28
141 ,p_attribute29 => p_attribute29
142 ,p_attribute30 => p_attribute30
143 ,p_is_history_flag => p_is_history_flag
144 ,p_lp_enrollment_id => l_lp_enrollment_id
145 ,p_object_version_number => p_object_version_number
146 );
147 --
148 -- Convert API warning boolean parameter values to specific
149 -- messages and add them to Multiple Message List
150 --
151 --
152 -- Convert API non-warning boolean parameter values
153 --
154 --
155 -- Derive the API return status value based on whether
156 -- messages of any type exist in the Multiple Message List.
157 -- Also disable Multiple Message Detection.
158 --
159 p_return_status := hr_multi_message.get_return_status_disable;
160 hr_utility.set_location(' Leaving:' || l_proc,20);
161 --
162 exception
163 when hr_multi_message.error_message_exist then
164 --
165 -- Catch the Multiple Message List exception which
166 -- indicates API processing has been aborted because
167 -- at least one message exists in the list.
168 --
169 rollback to create_lp_enrollment_swi;
170 --
171 -- Reset IN OUT parameters and set OUT parameters
172 --
173 p_object_version_number := null;
174 p_return_status := hr_multi_message.get_return_status_disable;
175 hr_utility.set_location(' Leaving:' || l_proc, 30);
176 when others then
177 --
178 -- When Multiple Message Detection is enabled catch
179 -- any Application specific or other unexpected
180 -- exceptions. Adding appropriate details to the
181 -- Multiple Message List. Otherwise re-raise the
182 -- error.
183 --
184 rollback to create_lp_enrollment_swi;
185 if hr_multi_message.unexpected_error_add(l_proc) then
186 hr_utility.set_location(' Leaving:' || l_proc,40);
187 raise;
188 end if;
189 --
190 -- Reset IN OUT and set OUT parameters
191 --
192 p_object_version_number := null;
193 p_return_status := hr_multi_message.get_return_status_disable;
194 hr_utility.set_location(' Leaving:' || l_proc,50);
195 end create_lp_enrollment;
196 -- ----------------------------------------------------------------------------
197 -- |-------------------------< update_lp_enrollment >-------------------------|
198 -- ----------------------------------------------------------------------------
199 PROCEDURE update_lp_enrollment
200 (p_effective_date in date
201 ,p_lp_enrollment_id in number
202 ,p_object_version_number in out nocopy number
203 ,p_person_id in number default hr_api.g_number
204 ,p_contact_id in number default hr_api.g_number
205 ,p_path_status_code in varchar2 default hr_api.g_varchar2
206 ,p_enrollment_source_code in varchar2 default hr_api.g_varchar2
207 ,p_no_of_mandatory_courses in number default hr_api.g_number
208 ,p_no_of_completed_courses in number default hr_api.g_number
209 ,p_completion_target_date in date default hr_api.g_date
210 ,p_completion_date in date default hr_api.g_date
211 ,p_creator_person_id in number default hr_api.g_number
212 ,p_attribute_category in varchar2 default hr_api.g_varchar2
213 ,p_attribute1 in varchar2 default hr_api.g_varchar2
214 ,p_attribute2 in varchar2 default hr_api.g_varchar2
215 ,p_attribute3 in varchar2 default hr_api.g_varchar2
216 ,p_attribute4 in varchar2 default hr_api.g_varchar2
217 ,p_attribute5 in varchar2 default hr_api.g_varchar2
218 ,p_attribute6 in varchar2 default hr_api.g_varchar2
219 ,p_attribute7 in varchar2 default hr_api.g_varchar2
220 ,p_attribute8 in varchar2 default hr_api.g_varchar2
221 ,p_attribute9 in varchar2 default hr_api.g_varchar2
222 ,p_attribute10 in varchar2 default hr_api.g_varchar2
223 ,p_attribute11 in varchar2 default hr_api.g_varchar2
224 ,p_attribute12 in varchar2 default hr_api.g_varchar2
225 ,p_attribute13 in varchar2 default hr_api.g_varchar2
226 ,p_attribute14 in varchar2 default hr_api.g_varchar2
227 ,p_attribute15 in varchar2 default hr_api.g_varchar2
228 ,p_attribute16 in varchar2 default hr_api.g_varchar2
229 ,p_attribute17 in varchar2 default hr_api.g_varchar2
230 ,p_attribute18 in varchar2 default hr_api.g_varchar2
231 ,p_attribute19 in varchar2 default hr_api.g_varchar2
232 ,p_attribute20 in varchar2 default hr_api.g_varchar2
233 ,p_attribute21 in varchar2 default hr_api.g_varchar2
234 ,p_attribute22 in varchar2 default hr_api.g_varchar2
235 ,p_attribute23 in varchar2 default hr_api.g_varchar2
236 ,p_attribute24 in varchar2 default hr_api.g_varchar2
237 ,p_attribute25 in varchar2 default hr_api.g_varchar2
238 ,p_attribute26 in varchar2 default hr_api.g_varchar2
239 ,p_attribute27 in varchar2 default hr_api.g_varchar2
240 ,p_attribute28 in varchar2 default hr_api.g_varchar2
241 ,p_attribute29 in varchar2 default hr_api.g_varchar2
242 ,p_attribute30 in varchar2 default hr_api.g_varchar2
243 ,p_is_history_flag in varchar2 default hr_api.g_varchar2
244 ,p_business_group_id in number default hr_api.g_number
245 ,p_validate in number default hr_api.g_false_num
246 ,p_return_status out nocopy varchar2
247 ) is
248 --
249 -- Variables for API Boolean parameters
250 l_validate boolean;
251 --
252 -- Variables for IN/OUT parameters
253 l_object_version_number number;
254 --
255 -- Other variables
256 l_proc varchar2(72) := g_package ||'update_lp_enrollment';
257 Begin
258 hr_utility.set_location(' Entering:' || l_proc,10);
259 --
260 -- Issue a savepoint
261 --
262 savepoint update_lp_enrollment_swi;
263 --
264 -- Initialise Multiple Message Detection
265 --
266 hr_multi_message.enable_message_list;
267 --
268 -- Remember IN OUT parameter IN values
269 --
270 l_object_version_number := p_object_version_number;
271 --
272 -- Convert constant values to their corresponding boolean value
273 --
274 l_validate :=
275 hr_api.constant_to_boolean
276 (p_constant_value => p_validate);
277 --
278 -- Register Surrogate ID or user key values
279 --
280 --
281 -- Call API
282 --
283 ota_lp_enrollment_api.update_lp_enrollment
284 (p_effective_date => p_effective_date
285 ,p_lp_enrollment_id => p_lp_enrollment_id
286 ,p_object_version_number => p_object_version_number
287 ,p_person_id => p_person_id
288 ,p_contact_id => p_contact_id
289 ,p_path_status_code => p_path_status_code
290 ,p_enrollment_source_code => p_enrollment_source_code
291 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
292 ,p_no_of_completed_courses => p_no_of_completed_courses
293 ,p_completion_target_date => p_completion_target_date
294 ,p_completion_date => p_completion_date
295 ,p_creator_person_id => p_creator_person_id
296 ,p_attribute_category => p_attribute_category
297 ,p_attribute1 => p_attribute1
298 ,p_attribute2 => p_attribute2
299 ,p_attribute3 => p_attribute3
300 ,p_attribute4 => p_attribute4
301 ,p_attribute5 => p_attribute5
302 ,p_attribute6 => p_attribute6
303 ,p_attribute7 => p_attribute7
304 ,p_attribute8 => p_attribute8
305 ,p_attribute9 => p_attribute9
306 ,p_attribute10 => p_attribute10
307 ,p_attribute11 => p_attribute11
308 ,p_attribute12 => p_attribute12
309 ,p_attribute13 => p_attribute13
310 ,p_attribute14 => p_attribute14
311 ,p_attribute15 => p_attribute15
312 ,p_attribute16 => p_attribute16
313 ,p_attribute17 => p_attribute17
314 ,p_attribute18 => p_attribute18
315 ,p_attribute19 => p_attribute19
316 ,p_attribute20 => p_attribute20
317 ,p_attribute21 => p_attribute21
318 ,p_attribute22 => p_attribute22
319 ,p_attribute23 => p_attribute23
320 ,p_attribute24 => p_attribute24
321 ,p_attribute25 => p_attribute25
322 ,p_attribute26 => p_attribute26
323 ,p_attribute27 => p_attribute27
324 ,p_attribute28 => p_attribute28
325 ,p_attribute29 => p_attribute29
326 ,p_attribute30 => p_attribute30
327 ,p_is_history_flag => p_is_history_flag
328 ,p_business_group_id => p_business_group_id
329 ,p_validate => l_validate
330 );
331 --
332 -- Convert API warning boolean parameter values to specific
333 -- messages and add them to Multiple Message List
334 --
335 --
336 -- Convert API non-warning boolean parameter values
337 --
338 --
339 -- Derive the API return status value based on whether
340 -- messages of any type exist in the Multiple Message List.
341 -- Also disable Multiple Message Detection.
342 --
343 p_return_status := hr_multi_message.get_return_status_disable;
344 hr_utility.set_location(' Leaving:' || l_proc,20);
345 --
346 exception
347 when hr_multi_message.error_message_exist then
348 --
349 -- Catch the Multiple Message List exception which
350 -- indicates API processing has been aborted because
351 -- at least one message exists in the list.
352 --
353 rollback to update_lp_enrollment_swi;
354 --
355 -- Reset IN OUT parameters and set OUT parameters
356 --
357 p_object_version_number := l_object_version_number;
358 p_return_status := hr_multi_message.get_return_status_disable;
359 hr_utility.set_location(' Leaving:' || l_proc, 30);
360 when others then
361 --
362 -- When Multiple Message Detection is enabled catch
363 -- any Application specific or other unexpected
364 -- exceptions. Adding appropriate details to the
365 -- Multiple Message List. Otherwise re-raise the
366 -- error.
367 --
368 rollback to update_lp_enrollment_swi;
369 if hr_multi_message.unexpected_error_add(l_proc) then
370 hr_utility.set_location(' Leaving:' || l_proc,40);
371 raise;
372 end if;
373 --
374 -- Reset IN OUT and set OUT parameters
375 --
376 p_object_version_number := l_object_version_number;
377 p_return_status := hr_multi_message.get_return_status_disable;
378 hr_utility.set_location(' Leaving:' || l_proc,50);
379 end update_lp_enrollment;
380 -- ----------------------------------------------------------------------------
381 -- |-------------------------< delete_lp_enrollment >-------------------------|
382 -- ----------------------------------------------------------------------------
383 PROCEDURE delete_lp_enrollment
384 (p_lp_enrollment_id in number
385 ,p_object_version_number in number
386 ,p_validate in number default hr_api.g_false_num
387 ,p_return_status out nocopy varchar2
388 ) is
389 --
390 -- Variables for API Boolean parameters
391 l_validate boolean;
392 --
393 -- Variables for IN/OUT parameters
394 --
395 -- Other variables
396 l_proc varchar2(72) := g_package ||'delete_lp_enrollment';
397 Begin
398 hr_utility.set_location(' Entering:' || l_proc,10);
399 --
400 -- Issue a savepoint
401 --
402 savepoint delete_lp_enrollment_swi;
403 --
404 -- Initialise Multiple Message Detection
405 --
406 hr_multi_message.enable_message_list;
407 --
408 -- Remember IN OUT parameter IN values
409 --
410 --
411 -- Convert constant values to their corresponding boolean value
412 --
413 l_validate :=
414 hr_api.constant_to_boolean
415 (p_constant_value => p_validate);
416 --
417 -- Register Surrogate ID or user key values
418 --
419 --
420 -- Call API
421 --
422 ota_lp_enrollment_api.delete_lp_enrollment
423 (p_lp_enrollment_id => p_lp_enrollment_id
424 ,p_object_version_number => p_object_version_number
425 ,p_validate => l_validate
426 );
427 --
428 -- Convert API warning boolean parameter values to specific
429 -- messages and add them to Multiple Message List
430 --
431 --
432 -- Convert API non-warning boolean parameter values
433 --
434 --
435 -- Derive the API return status value based on whether
436 -- messages of any type exist in the Multiple Message List.
437 -- Also disable Multiple Message Detection.
438 --
439 p_return_status := hr_multi_message.get_return_status_disable;
440 hr_utility.set_location(' Leaving:' || l_proc,20);
441 --
442 exception
443 when hr_multi_message.error_message_exist then
444 --
445 -- Catch the Multiple Message List exception which
446 -- indicates API processing has been aborted because
447 -- at least one message exists in the list.
448 --
449 rollback to delete_lp_enrollment_swi;
450 --
451 -- Reset IN OUT parameters and set OUT parameters
452 --
453 p_return_status := hr_multi_message.get_return_status_disable;
454 hr_utility.set_location(' Leaving:' || l_proc, 30);
455 when others then
456 --
457 -- When Multiple Message Detection is enabled catch
458 -- any Application specific or other unexpected
459 -- exceptions. Adding appropriate details to the
460 -- Multiple Message List. Otherwise re-raise the
461 -- error.
462 --
463 rollback to delete_lp_enrollment_swi;
464 if hr_multi_message.unexpected_error_add(l_proc) then
465 hr_utility.set_location(' Leaving:' || l_proc,40);
466 raise;
467 end if;
468 --
469 -- Reset IN OUT and set OUT parameters
470 --
471 p_return_status := hr_multi_message.get_return_status_disable;
472 hr_utility.set_location(' Leaving:' || l_proc,50);
473 end delete_lp_enrollment;
474 -- ----------------------------------------------------------------------------
475 -- |--------------------------< SUBSCRIBE_TO_LEARNING_PATH>-------------------------|
476 -- ----------------------------------------------------------------------------
477 procedure subscribe_to_learning_path
478 (p_validate in number default hr_api.g_false_num
479 ,p_learning_path_id IN NUMBER
480 ,p_person_id IN NUMBER default null
481 ,p_contact_id IN NUMBER default null
482 ,p_enrollment_source_code IN VARCHAR2
483 ,p_business_group_id IN NUMBER
484 ,p_creator_person_id IN NUMBER
485 ,p_lp_enrollment_id OUT NOCOPY number
486 ,p_path_status_code OUT NOCOPY VARCHAR2
487 ,p_return_status OUT NOCOPY VARCHAR2
488 ) IS
489
490 l_proc varchar2(72) := g_package || ' subscribe_to_learning_path';
491 l_validate boolean;
492
493 BEGIN
494
495 hr_multi_message.enable_message_list;
496 savepoint create_lp_subscription_swi;
497
498 l_validate :=
499 hr_api.constant_to_boolean
500 (p_constant_value => p_validate);
501
502 ota_lp_enrollment_api.subscribe_to_learning_path(
503 p_validate => l_validate
504 ,p_learning_path_id => p_learning_path_id
505 ,p_person_id => p_person_id
506 ,p_contact_id => p_contact_id
507 ,p_enrollment_source_code => p_enrollment_source_code
508 ,p_business_group_id => p_business_group_id
509 ,p_creator_person_id => p_creator_person_id
510 ,p_lp_enrollment_id => p_lp_enrollment_id
511 ,p_path_status_code => p_path_status_code);
512
513 --
514 -- Convert API warning boolean parameter values to specific
515 -- messages and add them to Multiple Message List
516 --
517 --
518 -- Convert API non-warning boolean parameter values
519 --
520 --
521 -- Derive the API return status value based on whether
522 -- messages of any type exist in the Multiple Message List.
523 -- Also disable Multiple Message Detection.
524 --
525 p_return_status := hr_multi_message.get_return_status_disable;
526 hr_utility.set_location(' Leaving:' || l_proc,20);
527
528 exception
529 when hr_multi_message.error_message_exist then
530 --
531 -- Catch the Multiple Message List exception which
532 -- indicates API processing has been aborted because
533 -- at least one message exists in the list.
534 --
535 rollback to create_lp_subscription_swi;
536 --
537 -- Reset IN OUT parameters and set OUT parameters
538 --
539 p_lp_enrollment_id := null;
540 p_path_status_code := null;
541 p_return_status := hr_multi_message.get_return_status_disable;
542 hr_utility.set_location(' Leaving:' || l_proc, 30);
543
544 when hr_api.validate_enabled then
545 --
546 -- As the Validate_Enabled exception has been raised
547 -- we must rollback to the savepoint
548 --
549 rollback to create_lp_subscription_swi;
550 --
551 -- Only set output warning arguments
552 -- (Any key or derived arguments must be set to null
553 -- when validation only mode is being used.)
554 --
555 p_lp_enrollment_id := null;
556 p_path_status_code := null;
557 hr_utility.set_location(' Leaving:'||l_proc, 80);
558 when others then
559 --
560 -- When Multiple Message Detection is enabled catch
561 -- any Application specific or other unexpected
562 -- exceptions. Adding appropriate details to the
563 -- Multiple Message List. Otherwise re-raise the
564 -- error.
565 --
566 rollback to create_lp_subscription_swi;
567 if hr_multi_message.unexpected_error_add(l_proc) then
568 hr_utility.set_location(' Leaving:' || l_proc,40);
569 raise;
570 end if;
571 --
572 -- Reset IN OUT and set OUT parameters
573 --
574 p_lp_enrollment_id := null;
575 p_path_status_code := null;
576 p_return_status := hr_multi_message.get_return_status_disable;
577 hr_utility.set_location(' Leaving:' || l_proc,50);
578 END subscribe_to_learning_path;
579 end ota_lp_enrollment_swi;