[Home] [Help]
PACKAGE BODY: APPS.OTA_CERTIFICATION_SWI
Source
1 Package Body ota_certification_swi As
2 /* $Header: otcrtswi.pkb 120.1 2005/06/14 15:13 estreacy noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'ota_certification_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_certification >-------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_certification
12 (p_effective_date in date
13 ,p_validate in number default hr_api.g_false_num
14 ,p_name in varchar2
15 ,p_business_group_id in number
16 ,p_public_flag in varchar2 default null
17 ,p_initial_completion_date in date default null
18 ,p_initial_completion_duration in number default null
19 ,p_initial_compl_duration_units in varchar2 default null
20 ,p_renewal_duration in number default null
21 ,p_renewal_duration_units in varchar2 default null
22 ,p_notify_days_before_expire in number default null
23 ,p_start_date_active in date default null
24 ,p_end_date_active in date default null
25 ,p_description in varchar2 default null
26 ,p_objectives in varchar2 default null
27 ,p_purpose in varchar2 default null
28 ,p_keywords in varchar2 default null
29 ,p_end_date_comments in varchar2 default null
30 ,p_initial_period_comments in varchar2 default null
31 ,p_renewal_period_comments in varchar2 default null
32 ,p_attribute_category in varchar2 default null
33 ,p_attribute1 in varchar2 default null
34 ,p_attribute2 in varchar2 default null
35 ,p_attribute3 in varchar2 default null
36 ,p_attribute4 in varchar2 default null
37 ,p_attribute5 in varchar2 default null
38 ,p_attribute6 in varchar2 default null
39 ,p_attribute7 in varchar2 default null
40 ,p_attribute8 in varchar2 default null
41 ,p_attribute9 in varchar2 default null
42 ,p_attribute10 in varchar2 default null
43 ,p_attribute11 in varchar2 default null
44 ,p_attribute12 in varchar2 default null
45 ,p_attribute13 in varchar2 default null
46 ,p_attribute14 in varchar2 default null
47 ,p_attribute15 in varchar2 default null
48 ,p_attribute16 in varchar2 default null
49 ,p_attribute17 in varchar2 default null
50 ,p_attribute18 in varchar2 default null
51 ,p_attribute19 in varchar2 default null
52 ,p_attribute20 in varchar2 default null
53 ,p_VALIDITY_DURATION in NUMBER default null
54 ,p_VALIDITY_DURATION_UNITS in VARCHAR2 default null
55 ,p_RENEWABLE_FLAG in VARCHAR2 default null
56 ,p_VALIDITY_START_TYPE in VARCHAR2 default null
57 ,p_COMPETENCY_UPDATE_LEVEL in VARCHAR2 default null
58 ,p_certification_id in number
59 ,p_object_version_number out nocopy number
60 ,p_return_status out nocopy varchar2
61 ) is
62 --
63 -- Variables for API Boolean parameters
64 l_validate boolean;
65 --
66 -- Variables for IN/OUT parameters
67 --
68 -- Other variables
69 l_certification_id number;
70 l_proc varchar2(72) := g_package ||'create_certification';
71 Begin
72 hr_utility.set_location(' Entering:' || l_proc,10);
73 --
74 -- Issue a savepoint
75 --
76 savepoint create_certification_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_crt_ins.set_base_key_value
94 (p_certification_id => p_certification_id
95 );
96
97 check_duplicate_name
98 ( p_name => p_name
99 ,p_certification_id => p_certification_id
100 ,p_business_group_id=>p_business_group_id
101 );
102 --
103 -- Call API
104 --
105 ota_certification_api.create_certification
106 (p_effective_date => p_effective_date
107 ,p_validate => l_validate
108 ,p_name => p_name
109 ,p_business_group_id => p_business_group_id
110 ,p_public_flag => p_public_flag
111 ,p_initial_completion_date => p_initial_completion_date
112 ,p_initial_completion_duration => p_initial_completion_duration
113 ,p_initial_compl_duration_units => p_initial_compl_duration_units
114 ,p_renewal_duration => p_renewal_duration
115 ,p_renewal_duration_units => p_renewal_duration_units
116 ,p_notify_days_before_expire => p_notify_days_before_expire
117 ,p_start_date_active => p_start_date_active
118 ,p_end_date_active => p_end_date_active
119 ,p_description => p_description
120 ,p_objectives => p_objectives
121 ,p_purpose => p_purpose
122 ,p_keywords => p_keywords
123 ,p_end_date_comments => p_end_date_comments
124 ,p_initial_period_comments => p_initial_period_comments
125 ,p_renewal_period_comments => p_renewal_period_comments
126 ,p_attribute_category => p_attribute_category
127 ,p_attribute1 => p_attribute1
128 ,p_attribute2 => p_attribute2
129 ,p_attribute3 => p_attribute3
130 ,p_attribute4 => p_attribute4
131 ,p_attribute5 => p_attribute5
132 ,p_attribute6 => p_attribute6
133 ,p_attribute7 => p_attribute7
134 ,p_attribute8 => p_attribute8
135 ,p_attribute9 => p_attribute9
136 ,p_attribute10 => p_attribute10
137 ,p_attribute11 => p_attribute11
138 ,p_attribute12 => p_attribute12
139 ,p_attribute13 => p_attribute13
140 ,p_attribute14 => p_attribute14
141 ,p_attribute15 => p_attribute15
142 ,p_attribute16 => p_attribute16
143 ,p_attribute17 => p_attribute17
144 ,p_attribute18 => p_attribute18
145 ,p_attribute19 => p_attribute19
146 ,p_attribute20 => p_attribute20
147 ,p_VALIDITY_DURATION => p_VALIDITY_DURATION
148 ,p_VALIDITY_DURATION_UNITS => p_VALIDITY_DURATION_UNITS
149 ,p_RENEWABLE_FLAG => p_RENEWABLE_FLAG
150 ,p_VALIDITY_START_TYPE => p_VALIDITY_START_TYPE
151 ,p_COMPETENCY_UPDATE_LEVEL => p_COMPETENCY_UPDATE_LEVEL
152 ,p_certification_id => l_certification_id
153 ,p_object_version_number => p_object_version_number
154 );
155 --
156 -- Convert API warning boolean parameter values to specific
157 -- messages and add them to Multiple Message List
158 --
159 --
160 -- Convert API non-warning boolean parameter values
161 --
162 --
163 -- Derive the API return status value based on whether
164 -- messages of any type exist in the Multiple Message List.
165 -- Also disable Multiple Message Detection.
166 --
167 p_return_status := hr_multi_message.get_return_status_disable;
168 hr_utility.set_location(' Leaving:' || l_proc,20);
169 --
170 exception
171 when hr_multi_message.error_message_exist then
172 --
173 -- Catch the Multiple Message List exception which
174 -- indicates API processing has been aborted because
175 -- at least one message exists in the list.
176 --
177 rollback to create_certification_swi;
178 --
179 -- Reset IN OUT parameters and set OUT parameters
180 --
181 p_object_version_number := null;
182 p_return_status := hr_multi_message.get_return_status_disable;
183 hr_utility.set_location(' Leaving:' || l_proc, 30);
184 when others then
185 --
186 -- When Multiple Message Detection is enabled catch
187 -- any Application specific or other unexpected
188 -- exceptions. Adding appropriate details to the
189 -- Multiple Message List. Otherwise re-raise the
190 -- error.
191 --
192 rollback to create_certification_swi;
193 if hr_multi_message.unexpected_error_add(l_proc) then
194 hr_utility.set_location(' Leaving:' || l_proc,40);
195 raise;
196 end if;
197 --
198 -- Reset IN OUT and set OUT parameters
199 --
200 p_object_version_number := null;
201 p_return_status := hr_multi_message.get_return_status_disable;
202 hr_utility.set_location(' Leaving:' || l_proc,50);
203 end create_certification;
204 -- ----------------------------------------------------------------------------
205 -- |-------------------------< update_certification >-------------------------|
206 -- ----------------------------------------------------------------------------
207 PROCEDURE update_certification
208 (p_effective_date in date
209 ,p_certification_id in number
210 ,p_object_version_number in out nocopy number
211 ,p_name in varchar2 default hr_api.g_varchar2
212 ,p_public_flag in varchar2 default hr_api.g_varchar2
213 ,p_initial_completion_date in date default hr_api.g_date
214 ,p_initial_completion_duration in number default hr_api.g_number
215 ,p_initial_compl_duration_units in varchar2 default hr_api.g_varchar2
216 ,p_renewal_duration in number default hr_api.g_number
217 ,p_renewal_duration_units in varchar2 default hr_api.g_varchar2
218 ,p_notify_days_before_expire in number default hr_api.g_number
219 ,p_start_date_active in date default hr_api.g_date
220 ,p_end_date_active in date default hr_api.g_date
221 ,p_description in varchar2 default hr_api.g_varchar2
222 ,p_objectives in varchar2 default hr_api.g_varchar2
223 ,p_purpose in varchar2 default hr_api.g_varchar2
224 ,p_keywords in varchar2 default hr_api.g_varchar2
225 ,p_end_date_comments in varchar2 default hr_api.g_varchar2
226 ,p_initial_period_comments in varchar2 default hr_api.g_varchar2
227 ,p_renewal_period_comments in varchar2 default hr_api.g_varchar2
228 ,p_attribute_category in varchar2 default hr_api.g_varchar2
229 ,p_attribute1 in varchar2 default hr_api.g_varchar2
230 ,p_attribute2 in varchar2 default hr_api.g_varchar2
231 ,p_attribute3 in varchar2 default hr_api.g_varchar2
232 ,p_attribute4 in varchar2 default hr_api.g_varchar2
233 ,p_attribute5 in varchar2 default hr_api.g_varchar2
234 ,p_attribute6 in varchar2 default hr_api.g_varchar2
235 ,p_attribute7 in varchar2 default hr_api.g_varchar2
236 ,p_attribute8 in varchar2 default hr_api.g_varchar2
237 ,p_attribute9 in varchar2 default hr_api.g_varchar2
238 ,p_attribute10 in varchar2 default hr_api.g_varchar2
239 ,p_attribute11 in varchar2 default hr_api.g_varchar2
240 ,p_attribute12 in varchar2 default hr_api.g_varchar2
241 ,p_attribute13 in varchar2 default hr_api.g_varchar2
242 ,p_attribute14 in varchar2 default hr_api.g_varchar2
243 ,p_attribute15 in varchar2 default hr_api.g_varchar2
244 ,p_attribute16 in varchar2 default hr_api.g_varchar2
245 ,p_attribute17 in varchar2 default hr_api.g_varchar2
246 ,p_attribute18 in varchar2 default hr_api.g_varchar2
247 ,p_attribute19 in varchar2 default hr_api.g_varchar2
248 ,p_attribute20 in varchar2 default hr_api.g_varchar2
249 ,p_business_group_id in number default hr_api.g_number
250 ,p_VALIDITY_DURATION in NUMBER default hr_api.g_number
251 ,p_VALIDITY_DURATION_UNITS in VARCHAR2 default hr_api.g_varchar2
252 ,p_RENEWABLE_FLAG in VARCHAR2 default hr_api.g_varchar2
253 ,p_VALIDITY_START_TYPE in VARCHAR2 default hr_api.g_varchar2
254 ,p_COMPETENCY_UPDATE_LEVEL in VARCHAR2 default hr_api.g_varchar2
255 ,p_validate in number default hr_api.g_false_num
256 ,p_return_status out nocopy varchar2
257 ) is
258 --
259 -- Variables for API Boolean parameters
260 l_validate boolean;
261 --
262 -- Variables for IN/OUT parameters
263 l_object_version_number number;
264 --
265 -- Other variables
266 l_proc varchar2(72) := g_package ||'update_certification';
267 Begin
268 hr_utility.set_location(' Entering:' || l_proc,10);
269 --
270 -- Issue a savepoint
271 --
272 savepoint update_certification_swi;
273 --
274 -- Initialise Multiple Message Detection
275 --
276 hr_multi_message.enable_message_list;
277 --
278 -- Remember IN OUT parameter IN values
279 --
280 l_object_version_number := p_object_version_number;
281 --
282 -- Convert constant values to their corresponding boolean value
283 --
284 l_validate :=
285 hr_api.constant_to_boolean
286 (p_constant_value => p_validate);
287 --
288 -- Register Surrogate ID or user key values
289 --
290
291 IF p_name <> hr_api.g_varchar2 THEN
292 check_duplicate_name
293 ( p_name => p_name
294 ,p_certification_id => p_certification_id
295 ,p_business_group_id=> p_business_group_id
296 );
297 END IF;
298 --
299 -- Call API
300 --
301 ota_certification_api.update_certification
302 (p_effective_date => p_effective_date
303 ,p_certification_id => p_certification_id
304 ,p_object_version_number => p_object_version_number
305 ,p_name => p_name
306 ,p_public_flag => p_public_flag
307 ,p_initial_completion_date => p_initial_completion_date
308 ,p_initial_completion_duration => p_initial_completion_duration
309 ,p_initial_compl_duration_units => p_initial_compl_duration_units
310 ,p_renewal_duration => p_renewal_duration
311 ,p_renewal_duration_units => p_renewal_duration_units
312 ,p_notify_days_before_expire => p_notify_days_before_expire
313 ,p_start_date_active => p_start_date_active
314 ,p_end_date_active => p_end_date_active
315 ,p_description => p_description
316 ,p_objectives => p_objectives
317 ,p_purpose => p_purpose
318 ,p_keywords => p_keywords
319 ,p_end_date_comments => p_end_date_comments
320 ,p_initial_period_comments => p_initial_period_comments
321 ,p_renewal_period_comments => p_renewal_period_comments
322 ,p_attribute_category => p_attribute_category
323 ,p_attribute1 => p_attribute1
324 ,p_attribute2 => p_attribute2
325 ,p_attribute3 => p_attribute3
326 ,p_attribute4 => p_attribute4
327 ,p_attribute5 => p_attribute5
328 ,p_attribute6 => p_attribute6
329 ,p_attribute7 => p_attribute7
330 ,p_attribute8 => p_attribute8
331 ,p_attribute9 => p_attribute9
332 ,p_attribute10 => p_attribute10
333 ,p_attribute11 => p_attribute11
334 ,p_attribute12 => p_attribute12
335 ,p_attribute13 => p_attribute13
336 ,p_attribute14 => p_attribute14
337 ,p_attribute15 => p_attribute15
338 ,p_attribute16 => p_attribute16
339 ,p_attribute17 => p_attribute17
340 ,p_attribute18 => p_attribute18
341 ,p_attribute19 => p_attribute19
342 ,p_attribute20 => p_attribute20
343 ,p_business_group_id => p_business_group_id
344 ,p_VALIDITY_DURATION => p_VALIDITY_DURATION
345 ,p_VALIDITY_DURATION_UNITS => p_VALIDITY_DURATION_UNITS
346 ,p_RENEWABLE_FLAG => p_RENEWABLE_FLAG
347 ,p_VALIDITY_START_TYPE => p_VALIDITY_START_TYPE
348 ,p_COMPETENCY_UPDATE_LEVEL => p_COMPETENCY_UPDATE_LEVEL
349 ,p_validate => l_validate
350 );
351 --
352 -- Convert API warning boolean parameter values to specific
353 -- messages and add them to Multiple Message List
354 --
355 --
356 -- Convert API non-warning boolean parameter values
357 --
358 --
359 -- Derive the API return status value based on whether
360 -- messages of any type exist in the Multiple Message List.
361 -- Also disable Multiple Message Detection.
362 --
363 p_return_status := hr_multi_message.get_return_status_disable;
364 hr_utility.set_location(' Leaving:' || l_proc,20);
365 --
366 exception
367 when hr_multi_message.error_message_exist then
368 --
369 -- Catch the Multiple Message List exception which
370 -- indicates API processing has been aborted because
371 -- at least one message exists in the list.
372 --
373 rollback to update_certification_swi;
374 --
375 -- Reset IN OUT parameters and set OUT parameters
376 --
377 p_object_version_number := l_object_version_number;
378 p_return_status := hr_multi_message.get_return_status_disable;
379 hr_utility.set_location(' Leaving:' || l_proc, 30);
380 when others then
381 --
382 -- When Multiple Message Detection is enabled catch
383 -- any Application specific or other unexpected
384 -- exceptions. Adding appropriate details to the
385 -- Multiple Message List. Otherwise re-raise the
386 -- error.
387 --
388 rollback to update_certification_swi;
389 if hr_multi_message.unexpected_error_add(l_proc) then
390 hr_utility.set_location(' Leaving:' || l_proc,40);
391 raise;
392 end if;
393 --
394 -- Reset IN OUT and set OUT parameters
395 --
396 p_object_version_number := l_object_version_number;
397 p_return_status := hr_multi_message.get_return_status_disable;
398 hr_utility.set_location(' Leaving:' || l_proc,50);
399 end update_certification;
400 -- ----------------------------------------------------------------------------
401 -- |-------------------------< delete_certification >-------------------------|
402 -- ----------------------------------------------------------------------------
403 PROCEDURE delete_certification
404 (p_certification_id in number
405 ,p_object_version_number in number
406 ,p_validate in number default hr_api.g_false_num
407 ,p_return_status out nocopy varchar2
408 ) is
409 --
410 -- Variables for API Boolean parameters
411 l_validate boolean;
412 --
413 -- Variables for IN/OUT parameters
414 --
415 -- Other variables
416 l_proc varchar2(72) := g_package ||'delete_certification';
417 Begin
418 hr_utility.set_location(' Entering:' || l_proc,10);
419 --
420 -- Issue a savepoint
421 --
422 savepoint delete_certification_swi;
423 --
424 -- Initialise Multiple Message Detection
425 --
426 hr_multi_message.enable_message_list;
427 --
428 -- Remember IN OUT parameter IN values
429 --
430 --
431 -- Convert constant values to their corresponding boolean value
432 --
433 l_validate :=
434 hr_api.constant_to_boolean
435 (p_constant_value => p_validate);
436 --
437 -- Register Surrogate ID or user key values
438 --
439 --
440 -- Call API
441 --
442 ota_certification_api.delete_certification
443 (p_certification_id => p_certification_id
444 ,p_object_version_number => p_object_version_number
445 ,p_validate => l_validate
446 );
447 --
448 -- Convert API warning boolean parameter values to specific
449 -- messages and add them to Multiple Message List
450 --
451 --
452 -- Convert API non-warning boolean parameter values
453 --
454 --
455 -- Derive the API return status value based on whether
456 -- messages of any type exist in the Multiple Message List.
457 -- Also disable Multiple Message Detection.
458 --
459 p_return_status := hr_multi_message.get_return_status_disable;
460 hr_utility.set_location(' Leaving:' || l_proc,20);
461 --
462 exception
463 when hr_multi_message.error_message_exist then
464 --
465 -- Catch the Multiple Message List exception which
466 -- indicates API processing has been aborted because
467 -- at least one message exists in the list.
468 --
469 rollback to delete_certification_swi;
470 --
471 -- Reset IN OUT parameters and set OUT parameters
472 --
473 p_return_status := hr_multi_message.get_return_status_disable;
474 hr_utility.set_location(' Leaving:' || l_proc, 30);
475 when others then
476 --
477 -- When Multiple Message Detection is enabled catch
478 -- any Application specific or other unexpected
479 -- exceptions. Adding appropriate details to the
480 -- Multiple Message List. Otherwise re-raise the
481 -- error.
482 --
483 rollback to delete_certification_swi;
484 if hr_multi_message.unexpected_error_add(l_proc) then
485 hr_utility.set_location(' Leaving:' || l_proc,40);
486 raise;
487 end if;
488 --
489 -- Reset IN OUT and set OUT parameters
490 --
491 p_return_status := hr_multi_message.get_return_status_disable;
492 hr_utility.set_location(' Leaving:' || l_proc,50);
493 end delete_certification;
494
495 -- ----------------------------------------------------------------------------
496 -- |-------------------------< check_crt_enrollments_exist >------------------------|
497 -- ----------------------------------------------------------------------------
498 -- {Start of comments}
499 --
500 -- Description:
501 -- This function checks whether enrollments exist for the given certification
502 --
503 --
504 -- Pre-requisites
505 -- All 'IN' parameters to this procedure have been appropriately derived.
506 --
507 -- Post Success:
508 -- p_return_status will return value indicating success.
509 --
510 -- Post Failure:
511 -- p_return_status will return value indication failure.
512 --
513 -- Access Status:
514 -- Internal Development use only.
515 --
516 -- {End of comments}
517 -- ----------------------------------------------------------------------------
518 FUNCTION check_crt_enrollments_exist
519 (p_certification_id in number
520 ) return varchar2 IS
521
522 CURSOR csr_chk_ce_exist is
523 SELECT 1
524 FROM ota_cert_enrollments ce
525 WHERE ce.certification_id = p_certification_id;
526
527 l_update_allowed varchar2(1);
528 l_fetch number;
529 BEGIN
530
531 open csr_chk_ce_exist;
532 fetch csr_chk_ce_exist into l_fetch;
533 if csr_chk_ce_exist%FOUND then
534 l_update_allowed := 'N';
535 else
536 l_update_allowed := 'Y';
537 end if;
538 close csr_chk_ce_exist;
539
540 return l_update_allowed;
541 END check_crt_enrollments_exist;
542
543
544
545 PROCEDURE check_duplicate_name
546 ( p_name IN VARCHAR2
547 ,p_certification_id IN NUMBER
548 ,p_business_group_id IN NUMBER)
549 IS
550
551
552 l_business_group_id OTA_LEARNING_PATHS.business_group_id%TYPE;
553
554 CURSOR csr_name is
555 SELECT 1
556 FROM OTA_CERTIFICATIONS_VL cert
557 WHERE rtrim(p_name) = rtrim(cert.name)
558 AND cert.business_group_id = l_business_group_id
559 AND (p_certification_id IS NULL
560 OR ( p_certification_id IS NOT NULL
561 AND p_certification_id <> cert.certification_id)) ;
562
563 CURSOR csr_get_bg_id IS
564 SELECT cert.business_group_id
565 FROM OTA_CERTIFICATIONS_B cert
566 WHERE cert.certification_id = p_certification_id;
567
568 l_exists number;
569 l_proc varchar2(72) := g_package ||'check_duplicate_name';
570
571 BEGIN
572
573 IF p_business_group_id IS NULL THEN
574 OPEN csr_get_bg_id;
575 FETCH csr_get_bg_id INTO l_business_group_id;
576 CLOSE csr_get_bg_id;
577 ELSE
578 l_business_group_id := p_business_group_id;
579 END IF;
580
581
582
583 OPEN csr_name;
584 FETCH csr_name INTO l_exists;
585 IF csr_name%FOUND THEN
586 CLOSE csr_name;
587 hr_utility.set_location(' Step:'|| l_proc, 10);
588 fnd_message.set_name('OTA', 'OTA_443810_CERT_UNIQUE_NAME');
589 fnd_message.raise_error;
590 ELSE
591 CLOSE csr_name;
592 hr_utility.set_location(' Step:'|| l_proc, 20);
593 END IF;
594
595
596
597 EXCEPTION
598
599 WHEN app_exception.application_exception THEN
600
601 IF hr_multi_message.exception_add
602 (p_associated_column1 => 'OTA_CERTIFICATIONS_TL.NAME') THEN
603
604 hr_utility.set_location(' Leaving:'||l_proc, 92);
605 RAISE;
606
607 END IF;
608
609 hr_utility.set_location(' Leaving:'||l_proc, 94);
610
611 END check_duplicate_name;
612
613
614
615 end ota_certification_swi;