[Home] [Help]
PACKAGE BODY: APPS.OTA_LEARNING_PATH_SWI
Source
1 Package Body ota_learning_path_swi As
2 /* $Header: otlpsswi.pkb 120.0 2005/05/29 07:24:23 appldev noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'ota_learning_path_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_learning_path >-------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_learning_path
12 (p_effective_date in date
13 ,p_validate in number default hr_api.g_false_num
14 ,p_path_name in varchar2 default null
15 ,p_business_group_id in number
16 ,p_duration in number default null
17 ,p_duration_units in varchar2 default null
18 ,p_start_date_active in date default null
19 ,p_end_date_active in date default null
20 ,p_description in varchar2 default null
21 ,p_objectives in varchar2 default null
22 ,p_keywords in varchar2 default null
23 ,p_purpose in varchar2 default null
24 ,p_attribute_category in varchar2 default null
25 ,p_attribute1 in varchar2 default null
26 ,p_attribute2 in varchar2 default null
27 ,p_attribute3 in varchar2 default null
28 ,p_attribute4 in varchar2 default null
29 ,p_attribute5 in varchar2 default null
30 ,p_attribute6 in varchar2 default null
31 ,p_attribute7 in varchar2 default null
32 ,p_attribute8 in varchar2 default null
33 ,p_attribute9 in varchar2 default null
34 ,p_attribute10 in varchar2 default null
35 ,p_attribute11 in varchar2 default null
36 ,p_attribute12 in varchar2 default null
37 ,p_attribute13 in varchar2 default null
38 ,p_attribute14 in varchar2 default null
39 ,p_attribute15 in varchar2 default null
40 ,p_attribute16 in varchar2 default null
41 ,p_attribute17 in varchar2 default null
42 ,p_attribute18 in varchar2 default null
43 ,p_attribute19 in varchar2 default null
44 ,p_attribute20 in varchar2 default null
45 ,p_path_source_code in varchar2 default null
46 ,p_source_function_code in varchar2 default null
47 ,p_assignment_id in number default null
48 ,p_source_id in number default null
49 ,p_notify_days_before_target in number default null
50 ,p_person_id in number default null
51 ,p_contact_id in number default null
52 ,p_display_to_learner_flag in varchar2 default null
53 ,p_public_flag in varchar2 default null
54 ,p_competency_update_level in varchar2 default null
55 ,p_learning_path_id in number
56 ,p_object_version_number out nocopy number
57 ,p_return_status out nocopy varchar2
58 ) is
59 --
60 -- Variables for API Boolean parameters
61 l_validate boolean;
62 --
63 -- Variables for IN/OUT parameters
64 --
65 -- Other variables
66 l_learning_path_id number;
67 l_path_name ota_learning_paths_tl.name%TYPE := p_path_name;
68
69 l_proc varchar2(72) := g_package ||'create_learning_path';
70
71 Begin
72 hr_utility.set_location(' Entering:' || l_proc,10);
73 --
74 -- Issue a savepoint
75 --
76 savepoint create_learning_path_swi;
77 --
78 -- Initialise Multiple Message Detection
79 --
80 hr_multi_message.enable_message_list;
81 --
82 -- Remember IN OUT parameter IN values
83 --
84 --
85 -- Convert constant values to their corresponding boolean value
86 --
87 l_validate :=
88 hr_api.constant_to_boolean
89 (p_constant_value => p_validate);
90 --
91 -- Register Surrogate ID or user key values
92 --
93 ota_lps_ins.set_base_key_value
94 (p_learning_path_id => p_learning_path_id
95 );
96
97 IF p_path_source_code = 'TALENT_MGMT' AND
98 p_path_name IS NULL THEN
99 l_path_name := ota_utility.Get_lookup_meaning
100 (p_lookup_type => 'OTA_PLAN_COMPONENT_SOURCE',
101 p_lookup_code => p_source_function_code,
102 p_application_id => 810);
103
104 IF p_source_function_code = 'APPRAISAL' THEN
105 -- Bug 4032657
106 -- l_path_name := substr(p_learning_path_id||l_path_name, 1, 80);
107 l_path_name := substr(l_path_name || p_learning_path_id, 1, 80);
108 END IF;
109
110 END IF;
111
112 check_duplicate_name
113 ( p_name => l_path_name
114 ,p_learning_path_id => p_learning_path_id
115 ,p_business_group_id=>p_business_group_id
116 ,p_person_id => p_person_id
117 ,p_contact_id => p_contact_id
118 ,p_path_source_code => p_path_source_code);
119
120 --
121 -- Call API
122 --
123 ota_learning_path_api.create_learning_path
124 (p_effective_date => p_effective_date
125 ,p_validate => l_validate
126 ,p_path_name => l_path_name
127 ,p_business_group_id => p_business_group_id
128 ,p_duration => p_duration
129 ,p_duration_units => p_duration_units
130 ,p_start_date_active => p_start_date_active
131 ,p_end_date_active => p_end_date_active
132 ,p_description => p_description
133 ,p_objectives => p_objectives
134 ,p_keywords => p_keywords
135 ,p_purpose => p_purpose
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_path_source_code => p_path_source_code
158 ,p_source_function_code => p_source_function_code
159 ,p_assignment_id => p_assignment_id
160 ,p_source_id => p_source_id
161 ,p_notify_days_before_target => p_notify_days_before_target
162 ,p_person_id => p_person_id
163 ,p_contact_id => p_contact_id
164 ,p_display_to_learner_flag => p_display_to_learner_flag
165 ,p_public_flag => p_public_flag
166 ,p_competency_update_level => p_competency_update_level
167 ,p_learning_path_id => l_learning_path_id
168 ,p_object_version_number => p_object_version_number
169 );
170 --
171 -- Convert API warning boolean parameter values to specific
172 -- messages and add them to Multiple Message List
173 --
174 --
175 -- Convert API non-warning boolean parameter values
176 --
177 --
178 -- Derive the API return status value based on whether
179 -- messages of any type exist in the Multiple Message List.
180 -- Also disable Multiple Message Detection.
181 --
182 p_return_status := hr_multi_message.get_return_status_disable;
183 hr_utility.set_location(' Leaving:' || l_proc,20);
184 --
185 exception
186 when hr_multi_message.error_message_exist then
187 --
188 -- Catch the Multiple Message List exception which
189 -- indicates API processing has been aborted because
190 -- at least one message exists in the list.
191 --
192 rollback to create_learning_path_swi;
193 --
194 -- Reset IN OUT parameters and set OUT parameters
195 --
196 p_object_version_number := null;
197 p_return_status := hr_multi_message.get_return_status_disable;
198 hr_utility.set_location(' Leaving:' || l_proc, 30);
199 when others then
200 --
201 -- When Multiple Message Detection is enabled catch
202 -- any Application specific or other unexpected
203 -- exceptions. Adding appropriate details to the
204 -- Multiple Message List. Otherwise re-raise the
205 -- error.
206 --
207 rollback to create_learning_path_swi;
208 if hr_multi_message.unexpected_error_add(l_proc) then
209 hr_utility.set_location(' Leaving:' || l_proc,40);
210 raise;
211 end if;
212 --
213 -- Reset IN OUT and set OUT parameters
214 --
215 p_object_version_number := null;
216 p_return_status := hr_multi_message.get_return_status_disable;
217 hr_utility.set_location(' Leaving:' || l_proc,50);
218 end create_learning_path;
219 -- ----------------------------------------------------------------------------
220 -- |-------------------------< delete_learning_path >-------------------------|
221 -- ----------------------------------------------------------------------------
222 PROCEDURE delete_learning_path
223 (p_learning_path_id in number
224 ,p_object_version_number in number
225 ,p_validate in number default hr_api.g_false_num
226 ,p_return_status out nocopy varchar2
227 ) is
228 --
229 -- Variables for API Boolean parameters
230 l_validate boolean;
231 --
232 -- Variables for IN/OUT parameters
233 --
234 -- Other variables
235 l_proc varchar2(72) := g_package ||'delete_learning_path';
236 Begin
237 hr_utility.set_location(' Entering:' || l_proc,10);
238 --
239 -- Issue a savepoint
240 --
241 savepoint delete_learning_path_swi;
242 --
243 -- Initialise Multiple Message Detection
244 --
245 hr_multi_message.enable_message_list;
246 --
247 -- Remember IN OUT parameter IN values
248 --
249 --
250 -- Convert constant values to their corresponding boolean value
251 --
252 l_validate :=
253 hr_api.constant_to_boolean
254 (p_constant_value => p_validate);
255 --
256 -- Register Surrogate ID or user key values
257 --
258 --
259 -- Call API
260 --
261 ota_learning_path_api.delete_learning_path
262 (p_learning_path_id => p_learning_path_id
263 ,p_object_version_number => p_object_version_number
264 ,p_validate => l_validate
265 );
266 --
267 -- Convert API warning boolean parameter values to specific
268 -- messages and add them to Multiple Message List
269 --
270 --
271 -- Convert API non-warning boolean parameter values
272 --
273 --
274 -- Derive the API return status value based on whether
275 -- messages of any type exist in the Multiple Message List.
276 -- Also disable Multiple Message Detection.
277 --
278 p_return_status := hr_multi_message.get_return_status_disable;
279 hr_utility.set_location(' Leaving:' || l_proc,20);
280 --
281 exception
282 when hr_multi_message.error_message_exist then
283 --
284 -- Catch the Multiple Message List exception which
285 -- indicates API processing has been aborted because
286 -- at least one message exists in the list.
287 --
288 rollback to delete_learning_path_swi;
289 --
290 -- Reset IN OUT parameters and set OUT parameters
291 --
292 p_return_status := hr_multi_message.get_return_status_disable;
293 hr_utility.set_location(' Leaving:' || l_proc, 30);
294 when others then
295 --
296 -- When Multiple Message Detection is enabled catch
297 -- any Application specific or other unexpected
298 -- exceptions. Adding appropriate details to the
299 -- Multiple Message List. Otherwise re-raise the
300 -- error.
301 --
302 rollback to delete_learning_path_swi;
303 if hr_multi_message.unexpected_error_add(l_proc) then
304 hr_utility.set_location(' Leaving:' || l_proc,40);
305 raise;
306 end if;
307 --
308 -- Reset IN OUT and set OUT parameters
309 --
310 p_return_status := hr_multi_message.get_return_status_disable;
311 hr_utility.set_location(' Leaving:' || l_proc,50);
312 end delete_learning_path;
313 -- ----------------------------------------------------------------------------
314 -- |-------------------------< update_learning_path >-------------------------|
315 -- ----------------------------------------------------------------------------
316 PROCEDURE update_learning_path
317 (p_effective_date in date
318 ,p_learning_path_id in number
319 ,p_object_version_number in out nocopy number
320 ,p_path_name in varchar2 default hr_api.g_varchar2
321 ,p_description in varchar2 default hr_api.g_varchar2
322 ,p_objectives in varchar2 default hr_api.g_varchar2
323 ,p_keywords in varchar2 default hr_api.g_varchar2
324 ,p_purpose in varchar2 default hr_api.g_varchar2
325 ,p_duration in number default hr_api.g_number
326 ,p_duration_units in varchar2 default hr_api.g_varchar2
327 ,p_start_date_active in date default hr_api.g_date
328 ,p_end_date_active in date default hr_api.g_date
329 ,p_attribute_category in varchar2 default hr_api.g_varchar2
330 ,p_attribute1 in varchar2 default hr_api.g_varchar2
331 ,p_attribute2 in varchar2 default hr_api.g_varchar2
332 ,p_attribute3 in varchar2 default hr_api.g_varchar2
333 ,p_attribute4 in varchar2 default hr_api.g_varchar2
334 ,p_attribute5 in varchar2 default hr_api.g_varchar2
335 ,p_attribute6 in varchar2 default hr_api.g_varchar2
336 ,p_attribute7 in varchar2 default hr_api.g_varchar2
337 ,p_attribute8 in varchar2 default hr_api.g_varchar2
338 ,p_attribute9 in varchar2 default hr_api.g_varchar2
339 ,p_attribute10 in varchar2 default hr_api.g_varchar2
340 ,p_attribute11 in varchar2 default hr_api.g_varchar2
341 ,p_attribute12 in varchar2 default hr_api.g_varchar2
342 ,p_attribute13 in varchar2 default hr_api.g_varchar2
343 ,p_attribute14 in varchar2 default hr_api.g_varchar2
344 ,p_attribute15 in varchar2 default hr_api.g_varchar2
345 ,p_attribute16 in varchar2 default hr_api.g_varchar2
346 ,p_attribute17 in varchar2 default hr_api.g_varchar2
347 ,p_attribute18 in varchar2 default hr_api.g_varchar2
348 ,p_attribute19 in varchar2 default hr_api.g_varchar2
349 ,p_attribute20 in varchar2 default hr_api.g_varchar2
350 ,p_path_source_code in varchar2 default hr_api.g_varchar2
351 ,p_source_function_code in varchar2 default hr_api.g_varchar2
352 ,p_assignment_id in number default hr_api.g_number
353 ,p_source_id in number default hr_api.g_number
354 ,p_notify_days_before_target in number default hr_api.g_number
355 ,p_person_id in number default hr_api.g_number
356 ,p_contact_id in number default hr_api.g_number
357 ,p_display_to_learner_flag in varchar2 default hr_api.g_varchar2
358 ,p_public_flag in varchar2 default hr_api.g_varchar2
359 ,p_business_group_id in number default hr_api.g_number
360 ,p_validate in number default hr_api.g_false_num
361 ,p_competency_update_level in varchar2 default hr_api.g_varchar2
362 ,p_return_status out nocopy varchar2
363 ) is
364 --
365 -- Variables for API Boolean parameters
366 l_validate boolean;
367 --
368 -- Variables for IN/OUT parameters
369 l_object_version_number number;
370 --
371 -- Other variables
372 l_proc varchar2(72) := g_package ||'update_learning_path';
373 Begin
374 hr_utility.set_location(' Entering:' || l_proc,10);
375 --
376 -- Issue a savepoint
377 --
378 savepoint update_learning_path_swi;
379 --
380 -- Initialise Multiple Message Detection
381 --
382 hr_multi_message.enable_message_list;
383 --
384 -- Remember IN OUT parameter IN values
385 --
386 l_object_version_number := p_object_version_number;
387 --
388 -- Convert constant values to their corresponding boolean value
389 --
390 l_validate :=
391 hr_api.constant_to_boolean
392 (p_constant_value => p_validate);
393 --
394 -- Register Surrogate ID or user key values
395 --
396
397 IF p_path_name <> hr_api.g_varchar2 THEN
398 check_duplicate_name
399 ( p_name => p_path_name
400 ,p_learning_path_id => p_learning_path_id
401 ,p_business_group_id=> p_business_group_id
402 ,p_person_id => p_person_id
403 ,p_contact_id => p_contact_id
404 ,p_path_source_code => p_path_source_code);
405 END IF;
406
407 --
408 -- Call API
409 --
410 ota_learning_path_api.update_learning_path
411 (p_effective_date => p_effective_date
412 ,p_learning_path_id => p_learning_path_id
413 ,p_object_version_number => p_object_version_number
414 ,p_path_name => p_path_name
415 ,p_description => p_description
416 ,p_objectives => p_objectives
417 ,p_keywords => p_keywords
418 ,p_purpose => p_purpose
419 ,p_duration => p_duration
420 ,p_duration_units => p_duration_units
421 ,p_start_date_active => p_start_date_active
422 ,p_end_date_active => p_end_date_active
423 ,p_attribute_category => p_attribute_category
424 ,p_attribute1 => p_attribute1
425 ,p_attribute2 => p_attribute2
426 ,p_attribute3 => p_attribute3
427 ,p_attribute4 => p_attribute4
428 ,p_attribute5 => p_attribute5
429 ,p_attribute6 => p_attribute6
430 ,p_attribute7 => p_attribute7
431 ,p_attribute8 => p_attribute8
432 ,p_attribute9 => p_attribute9
433 ,p_attribute10 => p_attribute10
434 ,p_attribute11 => p_attribute11
435 ,p_attribute12 => p_attribute12
436 ,p_attribute13 => p_attribute13
437 ,p_attribute14 => p_attribute14
438 ,p_attribute15 => p_attribute15
439 ,p_attribute16 => p_attribute16
440 ,p_attribute17 => p_attribute17
441 ,p_attribute18 => p_attribute18
442 ,p_attribute19 => p_attribute19
443 ,p_attribute20 => p_attribute20
444 ,p_business_group_id => p_business_group_id
445 ,p_validate => l_validate
446 ,p_path_source_code => p_path_source_code
447 ,p_source_function_code => p_source_function_code
448 ,p_assignment_id => p_assignment_id
449 ,p_source_id => p_source_id
450 ,p_notify_days_before_target => p_notify_days_before_target
451 ,p_person_id => p_person_id
452 ,p_contact_id => p_contact_id
453 ,p_display_to_learner_flag => p_display_to_learner_flag
454 ,p_public_flag => p_public_flag
455 ,p_competency_update_level => p_competency_update_level
456 );
457 --
458 -- Convert API warning boolean parameter values to specific
459 -- messages and add them to Multiple Message List
460 --
461 --
462 -- Convert API non-warning boolean parameter values
463 --
464 --
465 -- Derive the API return status value based on whether
466 -- messages of any type exist in the Multiple Message List.
467 -- Also disable Multiple Message Detection.
468 --
469 p_return_status := hr_multi_message.get_return_status_disable;
470 hr_utility.set_location(' Leaving:' || l_proc,20);
471 --
472 exception
473 when hr_multi_message.error_message_exist then
474 --
475 -- Catch the Multiple Message List exception which
476 -- indicates API processing has been aborted because
477 -- at least one message exists in the list.
478 --
479 rollback to update_learning_path_swi;
480 --
481 -- Reset IN OUT parameters and set OUT parameters
482 --
483 p_object_version_number := l_object_version_number;
484 p_return_status := hr_multi_message.get_return_status_disable;
485 hr_utility.set_location(' Leaving:' || l_proc, 30);
486 when others then
487 --
488 -- When Multiple Message Detection is enabled catch
489 -- any Application specific or other unexpected
490 -- exceptions. Adding appropriate details to the
491 -- Multiple Message List. Otherwise re-raise the
492 -- error.
493 --
494 rollback to update_learning_path_swi;
495 if hr_multi_message.unexpected_error_add(l_proc) then
496 hr_utility.set_location(' Leaving:' || l_proc,40);
497 raise;
498 end if;
499 --
500 -- Reset IN OUT and set OUT parameters
501 --
502 p_object_version_number := l_object_version_number;
503 p_return_status := hr_multi_message.get_return_status_disable;
504 hr_utility.set_location(' Leaving:' || l_proc,50);
505 end update_learning_path;
506
507 -- ----------------------------------------------------------------------------
508 -- |-------------------------< check_lp_enrollments_exist >------------------------|
509 -- ----------------------------------------------------------------------------
510 -- {Start of comments}
511 --
512 -- Description:
513 -- This function checks whether enrollments exist for the given Learning Path
514 --
515 --
516 -- Pre-requisites
517 -- All 'IN' parameters to this procedure have been appropriately derived.
518 --
519 -- Post Success:
520 -- p_return_status will return value indicating success.
521 --
522 -- Post Failure:
523 -- p_return_status will return value indication failure.
524 --
525 -- Access Status:
526 -- Internal Development use only.
527 --
528 -- {End of comments}
529 -- ----------------------------------------------------------------------------
530 FUNCTION check_lp_enrollments_exist
531 (p_learning_path_id in number
532 ) return varchar2 IS
533
534 CURSOR csr_chk_lpe_exist is
535 SELECT 1
536 FROM ota_lp_enrollments lpe
537 WHERE lpe.learning_path_id = p_learning_path_id;
538
539 l_update_allowed varchar2(1);
540 l_fetch number;
541 BEGIN
542
543 open csr_chk_lpe_exist;
544 fetch csr_chk_lpe_exist into l_fetch;
545 if csr_chk_lpe_exist%FOUND then
546 l_update_allowed := 'N';
547 else
548 l_update_allowed := 'Y';
549 end if;
550 close csr_chk_lpe_exist;
551
552 return l_update_allowed;
553 END check_lp_enrollments_exist;
554
555
556
557 PROCEDURE check_duplicate_name
558 ( p_name IN VARCHAR2
559 ,p_learning_path_id IN NUMBER
560 ,p_business_group_id IN NUMBER
561 ,p_person_id IN NUMBER
562 ,p_contact_id IN NUMBER
563 ,p_path_source_code IN VARCHAR2)
564 IS
565
566
567 l_business_group_id OTA_LEARNING_PATHS.business_group_id%TYPE;
568
569 CURSOR csr_ctg_name is
570 SELECT 1
571 FROM OTA_LEARNING_PATHS_VL LPS
572 WHERE rtrim(p_name) = rtrim(lps.name)
573 AND lps.business_group_id = l_business_group_id
574 AND path_source_code = 'CATALOG'
575 AND (p_learning_path_id IS NULL
576 OR ( p_learning_path_id IS NOT NULL
577 AND p_learning_path_id <> lps.learning_path_id)) ;
578
579 CURSOR csr_emp_name is
580 SELECT 1
581 FROM OTA_LEARNING_PATHS_VL LPS
582 WHERE rtrim(p_name) = rtrim(lps.name)
583 AND lps.business_group_id = l_business_group_id
584 AND person_id = p_person_id
585 AND (p_learning_path_id IS NULL
586 OR ( p_learning_path_id IS NOT NULL
587 AND p_learning_path_id <> lps.learning_path_id)) ;
588
589 CURSOR csr_ct_name is
590 SELECT 1
591 FROM OTA_LEARNING_PATHS_VL LPS
592 WHERE rtrim(p_name) = rtrim(lps.name)
593 AND lps.business_group_id = l_business_group_id
594 AND contact_id = p_contact_id
595 AND (p_learning_path_id IS NULL
596 OR ( p_learning_path_id IS NOT NULL
597 AND p_learning_path_id <> lps.learning_path_id)) ;
598
599 CURSOR csr_get_bg_id IS
600 SELECT lps.business_group_id
601 FROM OTA_LEARNING_PATHS lps
602 WHERE lps.learning_path_id = p_learning_path_id;
603
604 l_exists number;
605 l_proc varchar2(72) := g_package ||'check_duplicate_name';
606
607 BEGIN
608
609 IF p_business_group_id IS NULL THEN
610 OPEN csr_get_bg_id;
611 FETCH csr_get_bg_id INTO l_business_group_id;
612 CLOSE csr_get_bg_id;
613 ELSE
614 l_business_group_id := p_business_group_id;
615 END IF;
616
617 IF p_path_source_code = 'CATALOG' THEN
618
619 OPEN csr_ctg_name;
620 FETCH csr_ctg_name INTO l_exists;
621 IF csr_ctg_name%FOUND THEN
622 CLOSE csr_ctg_name;
623 hr_utility.set_location(' Step:'|| l_proc, 10);
624 fnd_message.set_name('OTA', 'OTA_13100_LPS_CTG_UNIQUE_NAME');
625 fnd_message.raise_error;
626 ELSE
627 CLOSE csr_ctg_name;
628 hr_utility.set_location(' Step:'|| l_proc, 20);
629 END IF;
630 ELSE
631 IF p_person_id IS NOT NULL THEN
632
633 OPEN csr_emp_name;
634 FETCH csr_emp_name INTO l_exists;
635 IF csr_emp_name%FOUND THEN
636 CLOSE csr_emp_name;
637 hr_utility.set_location(' Step:'|| l_proc, 30);
638 fnd_message.set_name('OTA', 'OTA_443386_LPS_UNIQUE_NAME');
639 fnd_message.raise_error;
640 ELSE
641 CLOSE csr_emp_name;
642 hr_utility.set_location(' Step:'|| l_proc, 40);
643 END IF;
644
645 ELSIF p_contact_id IS NOT NULL THEN
646
647 OPEN csr_ct_name;
648 FETCH csr_ct_name INTO l_exists;
649 IF csr_ct_name%FOUND THEN
650 CLOSE csr_ct_name;
651 hr_utility.set_location(' Step:'|| l_proc, 50);
652 fnd_message.set_name('OTA', 'OTA_443386_LPS_UNIQUE_NAME');
653 fnd_message.raise_error;
654 ELSE
655 CLOSE csr_ct_name;
656 hr_utility.set_location(' Step:'|| l_proc, 60);
657 END IF;
658
659 END IF;
660 END IF;
661
662
663 EXCEPTION
664
665 WHEN app_exception.application_exception THEN
666
667 IF hr_multi_message.exception_add
668 (p_associated_column1 => 'OTA_LEARNING_PATHS.NAME') THEN
669
670 hr_utility.set_location(' Leaving:'||l_proc, 92);
671 RAISE;
672
673 END IF;
674
675 hr_utility.set_location(' Leaving:'||l_proc, 94);
676
677 END check_duplicate_name;
678
679
680 FUNCTION is_Duration_Updateable
681 ( p_learning_path_id IN NUMBER
682 ) return VARCHAR2 IS
683
684 l_updateable VARCHAR2(30);
685 l_duration NUMBER;
686 BEGIN
687
688 l_updateable := ota_learning_path_swi.check_lp_enrollments_exist(p_learning_path_id);
689 return l_updateable;
690
691 END is_Duration_Updateable;
692
693 end ota_learning_path_swi;
694