[Home] [Help]
PACKAGE BODY: APPS.HR_RATING_SCALES_API
Source
1 Package Body hr_rating_scales_api as
2 /* $Header: perscapi.pkb 120.0 2005/05/31 19:41:40 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_rating_scales_api.';
7 --
8 -- ---------------------------------------------------------------------------
9 -- |-----------------------< <create_rating_scale> >--------------------------|
10 -- ---------------------------------------------------------------------------
11 -- ngundura made changes as per pa requirements
12 -- {made p_business_group_id made optional }
13 -- Validate the language parameter. l_language_code should be passed
14 -- instead of p_language_code from now on, to allow an IN OUT parameter to
15 -- be passed through.
16 --
17
18 procedure create_rating_scale
19 (p_language_code in varchar2 default hr_api.userenv_lang,
20 p_validate in boolean default false,
21 p_effective_date in date,
22 p_name in varchar2,
23 p_type in varchar2,
24 p_default_flag in varchar2 default 'N',
25 p_business_group_id in number default null,
26 p_description in varchar2 default null,
27 p_attribute_category in varchar2 default null,
28 p_attribute1 in varchar2 default null,
29 p_attribute2 in varchar2 default null,
30 p_attribute3 in varchar2 default null,
31 p_attribute4 in varchar2 default null,
32 p_attribute5 in varchar2 default null,
33 p_attribute6 in varchar2 default null,
34 p_attribute7 in varchar2 default null,
35 p_attribute8 in varchar2 default null,
36 p_attribute9 in varchar2 default null,
37 p_attribute10 in varchar2 default null,
38 p_attribute11 in varchar2 default null,
39 p_attribute12 in varchar2 default null,
40 p_attribute13 in varchar2 default null,
41 p_attribute14 in varchar2 default null,
42 p_attribute15 in varchar2 default null,
43 p_attribute16 in varchar2 default null,
44 p_attribute17 in varchar2 default null,
45 p_attribute18 in varchar2 default null,
46 p_attribute19 in varchar2 default null,
47 p_attribute20 in varchar2 default null,
48 p_rating_scale_id out NOCOPY number,
49 p_object_version_number out NOCOPY number
50 ) is
51 --
52 -- Declare cursors and local variables
53 --
54 --
55 l_proc varchar2(72) := g_package||'create_rating_scale';
56 l_rating_scale_id per_rating_scales.rating_scale_id%TYPE;
57 l_object_version_number per_rating_scales.object_version_number%TYPE;
58 l_language_code per_rating_scales_tl.language%TYPE;
59 begin
60 hr_utility.set_location('Entering:'|| l_proc, 5);
61 --
62 -- Issue a savepoint.
63 --
64 savepoint create_rating_scale;
65 hr_utility.set_location(l_proc, 6);
66 l_language_code := p_language_code;
67 hr_api.validate_language_code(p_language_code => l_language_code);
68 --
69 -- ngundura global rating scales cannot be created if Cross business
70 -- group profile is set to N
71 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP')='N' and p_business_group_id is null then
72 fnd_message.set_name('PER','HR_52692_NO_GLOB_RAT_SCAL');
73 fnd_message.raise_error;
74 end if;
75
76 -- Call Before Process User Hook
77 --
78 begin
79 hr_rating_scales_bk1.create_rating_scale_b (
80 p_language_code => l_language_code,
81 p_effective_date => p_effective_date,
82 p_name => p_name,
83 p_type => p_type,
84 p_business_group_id => p_business_group_id,
85 p_description => p_description,
86 p_default_flag => p_default_flag,
87 p_attribute_category => p_attribute_category,
88 p_attribute1 => p_attribute1,
89 p_attribute2 => p_attribute2,
90 p_attribute3 => p_attribute3,
91 p_attribute4 => p_attribute4,
92 p_attribute5 => p_attribute5,
93 p_attribute6 => p_attribute6,
94 p_attribute7 => p_attribute7,
95 p_attribute8 => p_attribute8,
96 p_attribute9 => p_attribute9,
97 p_attribute10 => p_attribute10,
98 p_attribute11 => p_attribute11,
99 p_attribute12 => p_attribute12,
100 p_attribute13 => p_attribute13,
101 p_attribute14 => p_attribute14,
102 p_attribute15 => p_attribute15,
103 p_attribute16 => p_attribute16,
104 p_attribute17 => p_attribute17,
105 p_attribute18 => p_attribute18,
106 p_attribute19 => p_attribute19,
107 p_attribute20 => p_attribute20
108 );
109 exception
110 when hr_api.cannot_find_prog_unit then
111 hr_api.cannot_find_prog_unit_error
112 (p_module_name => 'create_rating_scale',
113 p_hook_type => 'BP'
114 );
115 end;
116 --
117 -- End of Before Process User Hook call
118 --
119 -- Validation in addition to Table Handlers
120 --
121 hr_utility.set_location(l_proc, 7);
122 --
123 -- Process Logic
124 --
125 per_rsc_ins.ins
126 (p_validate => p_validate,
127 p_effective_date => p_effective_date,
128 p_name => p_name,
129 p_type => p_type,
130 p_business_group_id => p_business_group_id,
131 p_description => p_description,
132 p_default_flag => p_default_flag,
133 p_attribute_category => p_attribute_category,
134 p_attribute1 => p_attribute1,
135 p_attribute2 => p_attribute2,
136 p_attribute3 => p_attribute3,
137 p_attribute4 => p_attribute4,
138 p_attribute5 => p_attribute5,
139 p_attribute6 => p_attribute6,
140 p_attribute7 => p_attribute7,
141 p_attribute8 => p_attribute8,
142 p_attribute9 => p_attribute9,
143 p_attribute10 => p_attribute10,
144 p_attribute11 => p_attribute11,
145 p_attribute12 => p_attribute12,
146 p_attribute13 => p_attribute13,
147 p_attribute14 => p_attribute14,
148 p_attribute15 => p_attribute15,
149 p_attribute16 => p_attribute16,
150 p_attribute17 => p_attribute17,
151 p_attribute18 => p_attribute18,
152 p_attribute19 => p_attribute19,
153 p_attribute20 => p_attribute20,
154 p_rating_scale_id => l_rating_scale_id,
155 p_object_version_number => l_object_version_number
156 );
157 --
158
159
160
161 per_rsl_ins.ins_tl
162 (p_language_code => l_language_code
163 ,p_rating_scale_id => l_rating_scale_id
164 ,p_name => p_name
165 ,p_description => p_description
166 );
167 hr_utility.set_location(l_proc, 8);
168 --
169 -- Call After Process User Hook
170 --
171 begin
172 hr_rating_scales_bk1.create_rating_scale_a (
173 p_language_code => l_language_code,
174 p_rating_scale_id => l_rating_scale_id,
175 p_object_version_number => l_object_version_number,
176 p_effective_date => p_effective_date,
177 p_name => p_name,
178 p_type => p_type,
179 p_business_group_id => p_business_group_id,
180 p_description => p_description,
181 p_default_flag => p_default_flag,
182 p_attribute_category => p_attribute_category,
183 p_attribute1 => p_attribute1,
184 p_attribute2 => p_attribute2,
185 p_attribute3 => p_attribute3,
186 p_attribute4 => p_attribute4,
187 p_attribute5 => p_attribute5,
188 p_attribute6 => p_attribute6,
189 p_attribute7 => p_attribute7,
190 p_attribute8 => p_attribute8,
191 p_attribute9 => p_attribute9,
192 p_attribute10 => p_attribute10,
193 p_attribute11 => p_attribute11,
194 p_attribute12 => p_attribute12,
195 p_attribute13 => p_attribute13,
196 p_attribute14 => p_attribute14,
197 p_attribute15 => p_attribute15,
198 p_attribute16 => p_attribute16,
199 p_attribute17 => p_attribute17,
200 p_attribute18 => p_attribute18,
201 p_attribute19 => p_attribute19,
202 p_attribute20 => p_attribute20
203 );
204 exception
205 when hr_api.cannot_find_prog_unit then
206 hr_api.cannot_find_prog_unit_error
207 (p_module_name => 'create_rating_scale',
208 p_hook_type => 'AP'
209 );
210 end;
211 --
212 -- End of After process user hook
213 --
214 -- When in validation only mode raise the Validate_Enabled exception
215 --
216 if p_validate then
217 raise hr_api.validate_enabled;
218 end if;
219 --
220 -- Set all output arguments
221 --
222 p_rating_scale_id := l_rating_scale_id;
223 p_object_version_number := l_object_version_number;
224 --
225 hr_utility.set_location(' Leaving:'||l_proc, 11);
226 exception
227 when hr_api.validate_enabled then
228 --
229 -- As the Validate_Enabled exception has been raised
230 -- we must rollback to the savepoint
231 --
232 ROLLBACK TO create_rating_scale;
233 --
234 -- Only set output warning arguments
235 -- (Any key or derived arguments must be set to null
236 -- when validation only mode is being used.)
237 --
238 p_rating_scale_id := null;
239 p_object_version_number := null;
240 --
241 when others then
242 --
243 -- A validation or unexpected error has occurred
244 --
245 -- Added as part of fix to bug 632482
246 --
247 p_rating_scale_id := null;
248 p_object_version_number := null;
249 ROLLBACK TO create_rating_scale;
250 raise;
251 --
252 -- End of fix.
253 --
254 hr_utility.set_location(' Leaving:'||l_proc, 12);
255 end create_rating_scale;
256 --
257 --
258 --
259 -- ---------------------------------------------------------------------------
260 -- |-----------------------< <update_rating_scale> >--------------------------|
261 -- ---------------------------------------------------------------------------
262 -- Validate the language parameter. l_language_code should be passed
263 -- instead of p_language_code from now on, to allow an IN OUT parameter to
264 -- be passed through.
265 --
266 procedure update_rating_scale
267 (p_language_code in varchar2 default hr_api.userenv_lang,
268 p_validate in boolean default false,
269 p_effective_date in date,
270 p_rating_scale_id in number,
271 p_object_version_number in out NOCOPY number,
272 p_name in varchar2 default hr_api.g_varchar2,
273 p_description in varchar2 default hr_api.g_varchar2,
274 p_default_flag in varchar2 default hr_api.g_varchar2,
275 p_attribute_category in varchar2 default hr_api.g_varchar2,
276 p_attribute1 in varchar2 default hr_api.g_varchar2,
277 p_attribute2 in varchar2 default hr_api.g_varchar2,
278 p_attribute3 in varchar2 default hr_api.g_varchar2,
279 p_attribute4 in varchar2 default hr_api.g_varchar2,
280 p_attribute5 in varchar2 default hr_api.g_varchar2,
281 p_attribute6 in varchar2 default hr_api.g_varchar2,
282 p_attribute7 in varchar2 default hr_api.g_varchar2,
283 p_attribute8 in varchar2 default hr_api.g_varchar2,
284 p_attribute9 in varchar2 default hr_api.g_varchar2,
285 p_attribute10 in varchar2 default hr_api.g_varchar2,
286 p_attribute11 in varchar2 default hr_api.g_varchar2,
287 p_attribute12 in varchar2 default hr_api.g_varchar2,
288 p_attribute13 in varchar2 default hr_api.g_varchar2,
289 p_attribute14 in varchar2 default hr_api.g_varchar2,
290 p_attribute15 in varchar2 default hr_api.g_varchar2,
291 p_attribute16 in varchar2 default hr_api.g_varchar2,
292 p_attribute17 in varchar2 default hr_api.g_varchar2,
293 p_attribute18 in varchar2 default hr_api.g_varchar2,
294 p_attribute19 in varchar2 default hr_api.g_varchar2,
295 p_attribute20 in varchar2 default hr_api.g_varchar2
296 ) is
297 --
298 -- Declare cursors and local variables
299 --
300
301 l_proc varchar2(72) := g_package||'update_rating_scale';
302 l_object_version_number per_rating_scales.object_version_number%TYPE;
303 l_language_code per_rating_scales_tl.language%TYPE;
304 l_temp_ovn number := p_object_version_number;
305 begin
306 hr_utility.set_location('Entering:'|| l_proc, 5);
307 --
308 -- Issue a savepoint.
309 --
310 savepoint update_rating_scale;
311 l_language_code := p_language_code;
312 hr_api.validate_language_code(p_language_code => l_language_code);
313 hr_utility.set_location(l_proc, 6);
314 --
315 -- Call Before Process User Hook
316 --
317 begin
318 hr_rating_scales_bk2.update_rating_scale_b (
319 p_language_code => l_language_code,
320 p_effective_date => p_effective_date,
321 p_rating_scale_id => p_rating_scale_id,
322 p_object_version_number => p_object_version_number,
323 p_name => p_name,
324 p_description => p_description,
325 p_default_flag => p_default_flag,
326 p_attribute_category => p_attribute_category,
327 p_attribute1 => p_attribute1,
328 p_attribute2 => p_attribute2,
329 p_attribute3 => p_attribute3,
330 p_attribute4 => p_attribute4,
331 p_attribute5 => p_attribute5,
332 p_attribute6 => p_attribute6,
333 p_attribute7 => p_attribute7,
334 p_attribute8 => p_attribute8,
335 p_attribute9 => p_attribute9,
336 p_attribute10 => p_attribute10,
337 p_attribute11 => p_attribute11,
338 p_attribute12 => p_attribute12,
339 p_attribute13 => p_attribute13,
340 p_attribute14 => p_attribute14,
341 p_attribute15 => p_attribute15,
342 p_attribute16 => p_attribute16,
343 p_attribute17 => p_attribute17,
344 p_attribute18 => p_attribute18,
345 p_attribute19 => p_attribute19,
346 p_attribute20 => p_attribute20
347 );
348 exception
349 when hr_api.cannot_find_prog_unit then
350 hr_api.cannot_find_prog_unit_error
351 (p_module_name => 'update_rating_scale',
352 p_hook_type => 'BP'
353 );
354 end;
355 --
356 -- End of Before Process User Hook call
357 --
358 -- Validation in addition to Table Handlers
359 --
360 hr_utility.set_location(l_proc, 7);
361 --
362 -- Process Logic
363 --
364 --
365 l_object_version_number := p_object_version_number;
366 per_rsc_upd.upd
367 (p_validate => p_validate,
368 p_effective_date => p_effective_date,
369 p_rating_scale_id => p_rating_scale_id,
370 p_object_version_number => l_object_version_number,
371 p_name => p_name,
372 p_description => p_description,
373 p_default_flag => p_default_flag,
374 p_attribute_category => p_attribute_category,
375 p_attribute1 => p_attribute1,
376 p_attribute2 => p_attribute2,
377 p_attribute3 => p_attribute3,
378 p_attribute4 => p_attribute4,
379 p_attribute5 => p_attribute5,
380 p_attribute6 => p_attribute6,
381 p_attribute7 => p_attribute7,
382 p_attribute8 => p_attribute8,
383 p_attribute9 => p_attribute9,
384 p_attribute10 => p_attribute10,
385 p_attribute11 => p_attribute11,
386 p_attribute12 => p_attribute12,
387 p_attribute13 => p_attribute13,
388 p_attribute14 => p_attribute14,
389 p_attribute15 => p_attribute15,
390 p_attribute16 => p_attribute16,
391 p_attribute17 => p_attribute17,
392 p_attribute18 => p_attribute18,
393 p_attribute19 => p_attribute19,
394 p_attribute20 => p_attribute20
395 );
396 --
397
398
399 per_rsl_upd.upd_tl
400 (p_language_code => l_language_code
401 ,p_rating_scale_id => p_rating_scale_id
402 ,p_name => p_name
403 ,p_description => p_description
404 );
405 hr_utility.set_location(l_proc, 8);
406 --
407 -- Call After Process User Hook
408 --
409 begin
410 hr_rating_scales_bk2.update_rating_scale_a (
411 p_language_code => l_language_code,
412 p_effective_date => p_effective_date,
413 p_rating_scale_id => p_rating_scale_id,
414 p_object_version_number => l_object_version_number,
415 p_name => p_name,
416 p_description => p_description,
417 p_default_flag => p_default_flag,
418 p_attribute_category => p_attribute_category,
419 p_attribute1 => p_attribute1,
420 p_attribute2 => p_attribute2,
421 p_attribute3 => p_attribute3,
422 p_attribute4 => p_attribute4,
423 p_attribute5 => p_attribute5,
424 p_attribute6 => p_attribute6,
425 p_attribute7 => p_attribute7,
426 p_attribute8 => p_attribute8,
427 p_attribute9 => p_attribute9,
428 p_attribute10 => p_attribute10,
429 p_attribute11 => p_attribute11,
430 p_attribute12 => p_attribute12,
431 p_attribute13 => p_attribute13,
432 p_attribute14 => p_attribute14,
433 p_attribute15 => p_attribute15,
434 p_attribute16 => p_attribute16,
435 p_attribute17 => p_attribute17,
436 p_attribute18 => p_attribute18,
437 p_attribute19 => p_attribute19,
438 p_attribute20 => p_attribute20
439 );
440 exception
441 when hr_api.cannot_find_prog_unit then
442 hr_api.cannot_find_prog_unit_error
443 (p_module_name => 'update_rating_scale',
444 p_hook_type => 'AP'
445 );
446 end;
447 --
448 -- End After Process User Hook
449 --
450 -- When in validation only mode raise the Validate_Enabled exception
451 --
452 if p_validate then
453 raise hr_api.validate_enabled;
454 end if;
455 --
456 -- Set all output arguments. l_object_version_number now has the new
457 -- object version number as the update was successful
458 --
459 --
460 p_object_version_number := l_object_version_number;
461 --
462 hr_utility.set_location(' Leaving:'||l_proc, 11);
463 exception
464 when hr_api.validate_enabled then
465 --
466 -- As the Validate_Enabled exception has been raised
467 -- we must rollback to the savepoint
468 --
469 ROLLBACK TO update_rating_scale;
470 --
471 -- Only set output warning arguments and in out arguments back
472 -- to their IN value
473 -- (Any key or derived arguments must be set to null
474 -- when validation only mode is being used.)
475 --
476 p_object_version_number := l_object_version_number;
477 --
478 when others then
479 --
480 -- A validation or unexpected error has occurred
481 --
482 -- Added as part of fix to bug 632482
483 --
484 p_object_version_number := l_temp_ovn;
485 ROLLBACK TO update_rating_scale;
486 raise;
487 --
488 -- End of fix.
489 --
490 hr_utility.set_location(' Leaving:'||l_proc, 12);
491 --
492 end update_rating_scale;
493 --
494 --
495 -- ---------------------------------------------------------------------------
496 -- |-----------------------< <delete_rating_scale> >--------------------------|
497 -- ---------------------------------------------------------------------------
498 --
499 procedure delete_rating_scale
500 (p_validate in boolean default false,
501 p_rating_scale_id in number,
502 p_object_version_number in number
503 ) is
504 --
505 -- Declare cursors and local variables
506 --
507 cursor csr_get_rat_levels(c_rating_scale_id per_rating_scales.rating_scale_id%TYPE)
508 is
509 select rating_level_id,
510 object_version_number
511 from per_rating_levels
512 where rating_scale_id = c_rating_scale_id;
513 --
514 --
515 l_proc varchar2(72) := g_package||'delete_rating_scale';
516 begin
517 hr_utility.set_location('Entering:'|| l_proc, 5);
518 --
519 -- Issue a savepoint.
520 --
521 savepoint delete_rating_scale;
522 hr_utility.set_location(l_proc, 6);
523 --
524 -- Call Before Process User Hook
525 --
526 begin
527 hr_rating_scales_bk3.delete_rating_scale_b
528 (
529 p_rating_scale_id => p_rating_scale_id,
530 p_object_version_number => p_object_version_number
531 );
532 exception
533 when hr_api.cannot_find_prog_unit then
534 hr_api.cannot_find_prog_unit_error
535 (p_module_name => 'delete_rating_scale',
536 p_hook_type => 'BP'
537 );
538 end;
539 --
540 -- End of Before Process User hook
541 --
542 -- Validation in addition to Table Handlers
543 --
544 hr_utility.set_location(l_proc, 7);
545 --
546 -- Process Logic
547 --
548 --
549 -- check if rating scale has any rating levels
550 -- if yes, delete these rows.
551 --
552 for c_rat_rec in csr_get_rat_levels(p_rating_scale_id) loop
553 per_rtl_del.del
554 (p_validate => FALSE
555 ,p_rating_level_id => c_rat_rec.rating_level_id
556 ,p_object_version_number => c_rat_rec.object_version_number
557 );
558 end loop;
559 --
560 -- now delete the rating scale itself
561 --
562 per_rsl_del.del_tl
563 (p_rating_scale_id => p_rating_scale_id
564 );
565
566 per_rsc_del.del
567 (p_validate => FALSE
568 ,p_rating_scale_id => p_rating_scale_id
569 ,p_object_version_number => p_object_version_number
570 );
571 --
572 hr_utility.set_location(l_proc, 8);
573 --
574 -- Call After Process User Hook
575 --
576 begin
577 hr_rating_scales_bk3.delete_rating_scale_a (
578 p_rating_scale_id => p_rating_scale_id,
579 p_object_version_number => p_object_version_number
580 );
581 exception
582 when hr_api.cannot_find_prog_unit then
583 hr_api.cannot_find_prog_unit_error
584 (p_module_name => 'delete_rating_scale',
585 p_hook_type => 'AP'
586 );
587 end;
588 --
589 -- End of After Process User hook
590 --
591 -- When in validation only mode raise the Validate_Enabled exception
592 --
593 if p_validate then
594 raise hr_api.validate_enabled;
595 end if;
596 --
597 hr_utility.set_location(' Leaving:'||l_proc, 11);
598 exception
599 when hr_api.validate_enabled then
600 --
601 -- As the Validate_Enabled exception has been raised
602 -- we must rollback to the savepoint
603 --
604 ROLLBACK TO delete_rating_scale;
605 --
606 --
607 when others then
608 --
609 -- A validation or unexpected error has occurred
610 --
611 -- Added as part of fix to bug 632482
612 --
613 ROLLBACK TO delete_rating_scale;
614 raise;
615 --
616 -- End of fix.
617 --
618 hr_utility.set_location(' Leaving:'||l_proc, 12);
619 end delete_rating_scale;
620 --
621 -- ----------------------------------------------------------------------------
622 -- |-------------------< <create_or_update_rating_scale> >---------------------|
623 -- ----------------------------------------------------------------------------
624 procedure create_or_update_rating_scale
625 (p_language_code in varchar2 default hr_api.userenv_lang
626 ,p_validate in boolean default false
627 ,p_effective_date in date default trunc(sysdate)
628 ,p_name in varchar2 default null
629 ,p_type in varchar2 default null
630 ,p_description in varchar2 default null
631 ,p_default_flag in varchar2 default null
632 ,p_translated_language in varchar2 default null
633 ,p_source_rating_scale_name in varchar2 default null
634 ) is
635
636 --
637 -- Declare cursor and local variables
638 --
639 l_proc varchar2(72) := g_package||'create_or_update_rating_scale';
640 l_rating_scale_id per_rating_scales.rating_scale_id%TYPE;
641 l_source_rating_scale_id per_rating_scales.rating_scale_id%TYPE;
642 l_ovn per_rating_scales.object_version_number%TYPE;
643 l_source_ovn per_rating_scales.object_version_number%TYPE;
644 l_type per_rating_scales.type%TYPE;
645 l_default_flag per_rating_scales.default_flag%TYPE;
646 l_effective_date date;
647 l_language_code varchar2(20);
648 l_translated_language varchar2(20);
649
650 cursor csr_rs(p_rating_scale_name in varchar2) is
651 select rating_scale_id , object_version_number
652 from per_rating_scales_v
653 where business_group_id is null
654 and name = p_rating_scale_name;
655
656 --
657 -- Declare local modules
658 --
659 function return_lookup_code
660 (p_meaning in fnd_lookup_values.meaning%TYPE default null
661 ,p_lookup_type in fnd_lookup_values.lookup_code%TYPE
662 ,p_language_code in fnd_lookup_values.language%TYPE
663 )
664 Return fnd_lookup_values.lookup_code%TYPE Is
665 --
666 l_lookup_code fnd_lookup_values.lookup_code%TYPE := null;
667 --
668 Cursor Sel_Id Is
669 select flv.lookup_code
670 from fnd_lookup_values flv
671 where flv.lookup_type = p_lookup_type
672 and flv.meaning = p_meaning
673 and flv.language = p_language_code
674 and flv.enabled_flag = 'Y';
675 --
676 begin
677 open Sel_Id;
678 fetch Sel_Id Into l_lookup_code;
679 if Sel_Id%notfound then
680 close Sel_Id;
681 hr_utility.set_message(800, 'HR_449156_LOOK_MEANING_INVALID');
682 fnd_message.set_token('MEANING',p_meaning);
683 hr_utility.raise_error;
684 end if;
685 close Sel_Id;
686 --
687 return (l_lookup_code);
688 end;
689 begin
690
691 hr_utility.set_location('Entering... ' || l_proc,10);
692 --
693 -- Issue a savepoint.
694 --
695 savepoint create_or_update_rating_scale;
696
697 hr_rating_scales_api.g_ignore_df := 'Y';
698
699 if (p_language_code is NULL) then
700 l_language_code := hr_api.userenv_lang;
701 else
702 l_language_code := p_language_code;
703 -- BUG3668368
704 hr_api.validate_language_code(p_language_code => l_language_code);
705 end if;
706
707 if (p_effective_date is NULL) then
708 l_effective_date := trunc(sysdate);
709 else
710 l_effective_date := trunc(p_effective_date);
711 end if;
712 hr_utility.trace('p_effective_date : ' || p_effective_date);
713
714 IF ( p_translated_language IS NULL AND p_source_rating_scale_name IS NULL )
715 THEN
716 hr_utility.set_location(l_proc,20);
717
718 open csr_rs(p_name);
719 fetch csr_rs into l_rating_scale_id,l_ovn;
720 if csr_rs%NOTFOUND then
721 close csr_rs;
722 hr_utility.set_location(l_proc,30);
723 l_rating_scale_id := NULL;
724 l_ovn := NULL;
725 else
726 close csr_rs;
727 end if;
728 hr_utility.trace('l_reating_scale_id : ' || l_rating_scale_id);
729 hr_utility.trace('l_ovn : ' || l_ovn);
730
731 --
732 if (p_type is not null) then
733 l_type := return_lookup_code
734 (p_meaning => p_type
735 ,p_lookup_type => 'RATING_SCALE_TYPE'
736 ,p_language_code => l_language_code
737 );
738 else
739 l_type := 'PROFICIENCY';
740 end if;
741 hr_utility.trace('l_type : ' || l_type);
742 --
743 if (p_default_flag is not null) then
744 l_default_flag := return_lookup_code
745 (p_meaning => p_default_flag
746 ,p_lookup_type => 'YES_NO'
747 ,p_language_code => l_language_code
748 );
749 else
750 l_default_flag := 'N';
751 end if;
752 hr_utility.trace('l_default_flag : ' || l_default_flag);
753
754 --
755 if (l_rating_scale_id is null and l_ovn is null) then
756 hr_utility.set_location(l_proc,40);
757
758 create_rating_scale
759 (p_language_code => l_language_code
760 ,p_validate => p_validate
761 ,p_effective_date => l_effective_date
762 ,p_name => p_name
763 ,p_type => l_type
764 ,p_default_flag => l_default_flag
765 ,p_description => p_description
766 ,p_rating_scale_id => l_rating_scale_id
767 ,p_object_version_number => l_ovn
768 );
769 hr_utility.trace('l_rating_scale_id : ' || l_rating_scale_id);
770 else
771 hr_utility.set_location(l_proc,50);
772
773 update_rating_scale
774 (p_language_code => l_language_code
775 ,p_validate => p_validate
776 ,p_effective_date => l_effective_date
777 ,p_rating_scale_id => l_rating_scale_id
778 ,p_object_version_number => l_ovn
779 ,p_name => p_name
780 ,p_default_flag => l_default_flag
781 ,p_description => p_description
782 );
783 end if;
784 ELSE
785
786 hr_utility.set_location(l_proc,60);
787 open csr_rs(p_source_rating_scale_name);
788 fetch csr_rs into l_source_rating_scale_id,l_source_ovn;
789 if csr_rs%NOTFOUND then
790 close csr_rs;
791 hr_utility.set_location(l_proc,70);
792 hr_utility.set_message(800, 'HR_449190_SOURCE_RSC_INVALID');
793 hr_utility.raise_error;
794 else
795 close csr_rs;
796 end if;
797 hr_utility.trace('l_source_reating_scale_id : ' || l_source_rating_scale_id);
798 hr_utility.trace('l_source_ovn : ' || l_source_ovn);
799
800 l_translated_language := p_translated_language;
801 hr_api.validate_language_code(p_language_code => l_translated_language);
802
803 --
804 -- MLS update
805 --
806 hr_utility.set_location(l_proc,80);
807 per_rsl_upd.upd_tl
808 (p_language_code => l_translated_language
809 ,p_rating_scale_id => l_source_rating_scale_id
810 ,p_name => p_name
811 ,p_description => p_description
812 );
813 hr_utility.set_location(l_proc, 90);
814 --
815 -- Call After Process User Hook
816 --
817 begin
818 hr_rating_scales_bk2.update_rating_scale_a (
819 p_language_code => l_translated_language,
820 p_effective_date => l_effective_date,
821 p_rating_scale_id => l_source_rating_scale_id,
822 p_object_version_number => l_source_ovn,
823 p_name => p_source_rating_scale_name,
824 p_description => p_description,
825 p_default_flag => p_default_flag,
826 p_attribute_category => NULL,
827 p_attribute1 => NULL,
828 p_attribute2 => NULL,
829 p_attribute3 => NULL,
830 p_attribute4 => NULL,
831 p_attribute5 => NULL,
832 p_attribute6 => NULL,
833 p_attribute7 => NULL,
834 p_attribute8 => NULL,
835 p_attribute9 => NULL,
836 p_attribute10 => NULL,
837 p_attribute11 => NULL,
838 p_attribute12 => NULL,
839 p_attribute13 => NULL,
840 p_attribute14 => NULL,
841 p_attribute15 => NULL,
842 p_attribute16 => NULL,
843 p_attribute17 => NULL,
844 p_attribute18 => NULL,
845 p_attribute19 => NULL,
846 p_attribute20 => NULL
847 );
848 exception
849 when hr_api.cannot_find_prog_unit then
850 hr_rating_scales_api.g_ignore_df := 'N';
851 hr_api.cannot_find_prog_unit_error
852 (p_module_name => 'update_rating_scale',
853 p_hook_type => 'AP'
854 );
855 end;
856 --
857 -- End After Process User Hook
858 --
859 hr_utility.set_location(l_proc,100);
860 END IF;
861
862 hr_rating_scales_api.g_ignore_df := 'N';
863 hr_utility.set_location('Leaving ... ' || l_proc,110);
864 exception
865 when hr_api.validate_enabled then
866 --
867 -- As the Validate_Enabled exception has been raised
868 -- we must rollback to the savepoint
869 --
870 ROLLBACK TO create_or_update_rating_scale;
871 hr_rating_scales_api.g_ignore_df := 'N';
872 --
873 --
874 when others then
875 --
876 -- A validation or unexpected error has occurred
877 --
878 --
879 ROLLBACK TO create_or_update_rating_scale;
880 hr_rating_scales_api.g_ignore_df := 'N';
881 raise;
882 --
883 hr_utility.set_location(' Leaving:'||l_proc, 120);
884 --
885 end create_or_update_rating_scale;
886 --
887 end hr_rating_scales_api;