[Home] [Help]
PACKAGE BODY: APPS.HR_RATE_VALUES_API
Source
1 PACKAGE BODY hr_rate_values_api AS
2 /* $Header: pypgrapi.pkb 115.7 2003/10/27 03:09:25 lsilveir noship $ */
3 --
4 -- Package Variables
5 --
6 g_package VARCHAR2(33) := ' hr_rate_values_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< create_rate_value >--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 PROCEDURE create_rate_value
13 (p_validate IN BOOLEAN DEFAULT FALSE
14 ,p_effective_date IN DATE
15 ,p_business_group_id IN NUMBER
16 ,p_rate_id IN NUMBER
17 ,p_grade_or_spinal_point_id IN NUMBER
18 ,p_rate_type IN VARCHAR2
19 ,p_currency_code IN VARCHAR2 DEFAULT NULL
20 ,p_maximum IN VARCHAR2 DEFAULT NULL
21 ,p_mid_value IN VARCHAR2 DEFAULT NULL
22 ,p_minimum IN VARCHAR2 DEFAULT NULL
23 ,p_sequence IN NUMBER DEFAULT NULL
24 ,p_value IN VARCHAR2 DEFAULT NULL
25 ,p_grade_rule_id OUT NOCOPY NUMBER
26 ,p_object_version_number OUT NOCOPY NUMBER
27 ,p_effective_start_date OUT NOCOPY DATE
28 ,p_effective_end_date OUT NOCOPY DATE) IS
29 --
30 -- Declare cursors and local variables
31 --
32 l_grade_rule_id pay_grade_rules_f.grade_rule_id%TYPE;
33 l_effective_start_date pay_grade_rules_f.effective_start_date%TYPE;
34 l_effective_end_date pay_grade_rules_f.effective_end_date%TYPE;
35 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
36 l_effective_date DATE;
37 l_proc VARCHAR2(72) := g_package||'create_rate_value';
38 --
39 l_rate_uom pay_rates.rate_uom%TYPE;
40 l_currency_code pay_grade_rules_f.currency_code%TYPE;
41 --
42 cursor csr_get_rate_uom is
43 select rate_uom
44 from pay_rates
45 where rate_id = p_rate_id;
46 --
47 cursor csr_get_curr_code is
48 select currency_code
49 from per_business_groups_perf
50 where business_group_id = p_business_group_id;
51 --
52 BEGIN
53 --
54 hr_utility.set_location('Entering:'|| l_proc, 10);
55 --
56 -- Issue a SAVEPOINT IF operating in validation only mode
57 --
58 SAVEPOINT create_rate_value;
59 --
60 -- Truncate date paramters
61 --
62 l_effective_date := TRUNC(p_effective_date);
63 --
64 -- Default the currency code if required.
65 --
66 IF p_currency_code IS NULL THEN
67 --
68 hr_utility.set_location(l_proc, 12);
69 --
70 -- Fetch the Rate Unit Of Measure
71 --
72 OPEN csr_get_rate_uom;
73 --
74 FETCH csr_get_rate_uom INTO l_rate_uom;
75 IF csr_get_rate_uom%NOTFOUND THEN
76 CLOSE csr_get_rate_uom;
77 --
78 hr_utility.set_location(l_proc, 13);
79 --
80 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
81 hr_utility.raise_error;
82 END IF;
83 --
84 CLOSE csr_get_rate_uom;
85 --
86 hr_utility.set_location(l_proc, 14);
87 --
88 IF l_rate_uom = 'M' THEN
89 --
90 -- Get default currency code from business group.
91 --
92 hr_utility.set_location(l_proc, 15);
93 --
94 OPEN csr_get_curr_code;
95 --
96 FETCH csr_get_curr_code INTO l_currency_code;
97 IF csr_get_curr_code%NOTFOUND THEN
98 CLOSE csr_get_curr_code;
99 --
100 hr_utility.set_location(l_proc, 16);
101 --
102 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
103 hr_utility.raise_error;
104 END IF;
105 --
106 CLOSE csr_get_curr_code;
107 --
108 ELSE
109 l_currency_code := p_currency_code;
110 END IF;
111 ELSE
112 l_currency_code := p_currency_code;
113 END IF;
114 --
115 -- Process Logic
116 --
117 BEGIN
118 --
119 -- Start of API User Hook for the before hook of create_rate_value
120 --
121 hr_utility.set_location(l_proc, 20);
122 --
123 hr_rate_value_bk1.create_rate_value_b
124 (p_effective_date => l_effective_date
125 ,p_business_group_id => p_business_group_Id
126 ,p_rate_id => p_rate_id
127 ,p_grade_or_spinal_point_id => p_grade_or_spinal_point_id
128 ,p_rate_type => p_rate_type
129 ,p_currency_code => l_currency_code
130 ,p_maximum => p_maximum
131 ,p_mid_value => p_mid_value
132 ,p_minimum => p_minimum
133 ,p_sequence => p_sequence
134 ,p_value => p_value);
135 --
136 hr_utility.set_location(l_proc, 30);
137 --
138 EXCEPTION
139 --
140 WHEN hr_api.cannot_find_prog_unit THEN
141 --
142 hr_api.cannot_find_prog_unit_error
143 (p_module_name => 'create_rate_value'
144 ,p_hook_type => 'BP');
145 --
146 -- End of API User Hook for the before hook of create_rate_value
147 --
148 END;
149 --
150 hr_utility.set_location(l_proc, 40);
151 --
152 pay_pgr_ins.ins
153 (p_effective_date => l_effective_date
154 ,p_business_group_id => p_business_group_id
155 ,p_rate_id => p_rate_id
156 ,p_grade_or_spinal_point_id => p_grade_or_spinal_point_id
157 ,p_rate_type => p_rate_type
158 ,p_maximum => p_maximum
159 ,p_mid_value => p_mid_value
160 ,p_minimum => p_minimum
161 ,p_sequence => p_sequence
162 ,p_value => p_value
163 ,p_currency_code => l_currency_code
164 ,p_grade_rule_id => l_grade_rule_id
165 ,p_object_version_number => l_object_version_number
166 ,p_effective_start_date => l_effective_start_date
167 ,p_effective_end_date => l_effective_end_Date);
168 --
169 hr_utility.set_location(l_proc, 50);
170 --
171 BEGIN
172 --
173 -- Start of API User Hook for the after hook of create_rate_value
174 --
175 hr_utility.set_location(l_proc, 60);
176 --
177 hr_rate_value_bk1.create_rate_value_a
178 (p_effective_date => l_effective_date
179 ,p_grade_rule_Id => l_grade_rule_id
180 ,p_object_version_number => l_object_version_number
181 ,p_effective_start_date => l_effective_start_date
182 ,p_effective_end_date => l_effective_end_date
183 ,p_business_group_id => p_business_group_id
184 ,p_rate_id => p_rate_id
185 ,p_grade_or_spinal_point_id => p_grade_or_spinal_point_id
186 ,p_rate_type => p_rate_type
187 ,p_currency_code => l_currency_code
188 ,p_maximum => p_maximum
189 ,p_mid_value => p_mid_value
190 ,p_minimum => p_minimum
191 ,p_sequence => p_sequence
192 ,p_value => p_value);
193 --
194 hr_utility.set_location(l_proc, 70);
195 --
196 EXCEPTION
197 ---
198 WHEN hr_api.cannot_find_prog_unit THEN
199 --
200 hr_api.cannot_find_prog_unit_error
201 (p_module_name => 'create_rate_value'
202 ,p_hook_type => 'AP');
203 --
204 -- End of API User Hook for the after hook of create_rate_value
205 --
206 END;
207 --
208 hr_utility.set_location(l_proc, 80);
209 --
210 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
211 --
212 IF p_validate THEN
213 --
214 RAISE hr_api.validate_enabled;
215 --
216 END IF;
217 --
218 -- Set all output arguments
219 --
220 p_grade_rule_id := l_grade_rule_id;
221 p_effective_start_date := l_effective_start_date;
222 p_effective_end_date := l_effective_end_date;
223 p_object_version_number := l_object_version_number;
224 --
225 hr_utility.set_location(' Leaving:'||l_proc, 999);
226 --
227 EXCEPTION
228 --
229 WHEN hr_api.validate_enabled THEN
230 --
231 -- As the Validate_Enabled EXCEPTION has been RAISEd
232 -- we must rollback to the SAVEPOINT
233 --
234 ROLLBACK TO create_rate_value;
235 --
236 -- Only set output warning arguments
237 -- (Any key or derived arguments must be set to null
238 -- WHEN validation only mode is being used.)
239 --
240 p_grade_rule_id := null;
241 p_effective_start_date := null;
242 p_effective_end_date := null;
243 p_object_version_number := null;
244 --
245 hr_utility.set_location(' Leaving:'||l_proc, 998);
246 --
247 WHEN others THEN
248 --
249 -- A validation or unexpected error has occured
250 --
251 ROLLBACK TO create_rate_value;
252 p_grade_rule_id := null;
253 p_effective_start_date := null;
254 p_effective_end_date := null;
255 p_object_version_number := null;
256 RAISE;
257 --
258 END create_rate_value;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |----------------------------< update_rate_value >--------------------------|
262 -- ----------------------------------------------------------------------------
263 --
264 PROCEDURE update_rate_value
265 (p_validate IN BOOLEAN DEFAULT FALSE
266 ,p_grade_rule_id IN NUMBER
267 ,p_effective_date IN DATE
268 ,p_datetrack_mode IN VARCHAR2
269 ,p_currency_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
270 ,p_maximum IN VARCHAR2 DEFAULT hr_api.g_varchar2
271 ,p_mid_value IN VARCHAR2 DEFAULT hr_api.g_varchar2
272 ,p_minimum IN VARCHAR2 DEFAULT hr_api.g_varchar2
273 ,p_sequence IN NUMBER DEFAULT hr_api.g_number
274 ,p_value IN VARCHAR2 DEFAULT hr_api.g_varchar2
275 ,p_object_version_number IN OUT NOCOPY NUMBER
276 ,p_effective_start_date OUT NOCOPY DATE
277 ,p_effective_end_date OUT NOCOPY DATE) IS
278 --
279 -- Declare cursors and local variables
280 --
281 l_proc VARCHAR2(72) := g_package||'update_rate_value';
282 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
283 l_effective_start_date pay_grade_rules_f.effective_start_date%TYPE;
284 l_effective_end_date pay_grade_rules_f.effective_END_date%TYPE;
285 l_effective_date DATE;
286 l_temp_ovn number := p_object_version_number;
287 --
288
289 BEGIN
290 --
291 hr_utility.set_location('Entering:'|| l_proc, 10);
292 --
293 l_effective_date := TRUNC(p_effective_date);
294 --
295 -- Issue a SAVEPOINT IF operating in validation only mode
296 --
297 SAVEPOINT update_rate_value;
298 --
299 hr_utility.set_location(l_proc, 20);
300 --
301 -- Process Logic
302 --
303 l_object_version_number := p_object_version_number;
304 --
305 BEGIN
306 --
307 -- Start of API User Hook for the before hook of update_rate_value
308 --
309 hr_rate_value_bk2.update_rate_value_b
310 (p_grade_rule_id => p_grade_rule_id
311 ,p_effective_date => p_effective_date
312 ,p_currency_code => p_currency_code
313 ,p_maximum => p_maximum
314 ,p_mid_value => p_mid_value
315 ,p_minimum => p_minimum
316 ,p_sequence => p_sequence
317 ,p_value => p_value
318 ,p_object_version_number => l_object_version_number);
319 --
320 EXCEPTION
321 --
322 WHEN hr_api.cannot_find_prog_unit THEN
323 --
324 hr_api.cannot_find_prog_unit_error
325 (p_module_name => 'update_rate_value'
326 ,p_hook_type => 'BP'
327 );
328 --
329 -- End of API User Hook for the before hook of update_rate_value
330 --
331 END;
332 --
333 hr_utility.set_location(l_proc, 30);
334 --
335 pay_pgr_upd.upd
336 (p_effective_date => l_effective_date
337 ,p_datetrack_mode => p_datetrack_mode
338 ,p_grade_rule_id => p_grade_rule_id
339 ,p_object_version_number => l_object_version_number
340 ,p_maximum => p_maximum
341 ,p_mid_value => p_mid_value
342 ,p_minimum => p_minimum
343 ,p_sequence => p_sequence
344 ,p_value => p_value
345 ,p_currency_code => p_currency_code
346 ,p_effective_start_date => l_effective_start_date
347 ,p_effective_end_date => l_effective_end_date);
348 --
349 BEGIN
350 --
351 -- Start of API User Hook for the after hook of update_rate_value
352 --
353 hr_rate_value_bk2.update_rate_value_a
354 (p_grade_rule_id => p_grade_rule_id
355 ,p_effective_date => p_effective_date
356 ,p_currency_code => p_currency_code
357 ,p_maximum => p_maximum
358 ,p_mid_value => p_mid_value
359 ,p_minimum => p_minimum
360 ,p_sequence => p_sequence
361 ,p_value => p_value
362 ,p_object_version_number => l_object_version_number
363 ,p_effective_start_date => l_effective_start_date
364 ,p_effective_end_date => l_effective_end_date);
365 --
366 EXCEPTION
367 --
368 WHEN hr_api.cannot_find_prog_unit THEN
369 --
370 hr_api.cannot_find_prog_unit_error
371 (p_module_name => 'update_rate_value'
372 ,p_hook_type => 'AP'
373 );
374 --
375 -- End of API User Hook for the after hook of update_rate_value
376 --
377 END;
378 --
379 hr_utility.set_location(l_proc, 40);
380 --
381 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
382 --
383 IF p_validate THEN
384 --
385 RAISE hr_api.validate_enabled;
386 --
387 END IF;
388 --
389 -- Set all output arguments
390 --
391 p_object_version_number := l_object_version_number;
392 p_effective_start_date := l_effective_start_date;
393 p_effective_end_date := l_effective_end_date;
394 --
395 hr_utility.set_location(' Leaving:'||l_proc, 999);
396 --
397 EXCEPTION
398 --
399 WHEN hr_api.validate_enabled THEN
400 --
401 -- As the Validate_Enabled EXCEPTION has been raised
402 -- we must rollback to the SAVEPOINT
403 --
404 ROLLBACK TO update_rate_value;
405 --
406 -- Only set output warning arguments
407 -- (Any key or derived arguments must be set to null
408 -- WHEN validation only mode is being used.)
409 --
410 p_effective_start_date := l_effective_start_date;
411 p_effective_end_date := l_effective_end_date;
412
413 p_object_version_number := l_temp_ovn;
414 --
415 hr_utility.set_location(' Leaving:'||l_proc, 998);
416 --
417 WHEN others THEN
418 --
419 -- A validation or unexpected error has occured
420 --
421 ROLLBACK TO update_rate_value;
422 -- Reset IN OUT parameters and set OUT parameters.
423 p_object_version_number := l_temp_ovn;
424 p_effective_start_date := null;
425 p_effective_end_date := null;
426
427 RAISE;
428 --
429 END update_rate_value;
430 -- ----------------------------------------------------------------------------
431 -- |----------------------------< delete_rate_value >-------------------------|
432 -- ----------------------------------------------------------------------------
433 --
434 PROCEDURE delete_rate_value
435 (p_validate IN BOOLEAN DEFAULT FALSE
436 ,p_grade_rule_id IN NUMBER
437 ,p_datetrack_mode IN VARCHAR2
438 ,p_effective_date IN DATE
439 ,p_object_version_number IN OUT NOCOPY NUMBER
440 ,p_effective_start_date OUT NOCOPY DATE
441 ,p_effective_end_date OUT NOCOPY DATE) IS
442 --
443 -- Declare cursors and local variables
444 --
445 l_proc VARCHAR2(72) := g_package||'delete_rate_value';
446 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
447 l_effective_start_date pay_grade_rules_f.effective_start_date%TYPE;
448 l_effective_end_date pay_grade_rules_f.effective_end_date%TYPE;
449 l_effective_date DATE;
450 l_temp_ovn number := p_object_version_number;
451 --
452 BEGIN
453 --
454 hr_utility.set_location('Entering:'|| l_proc, 10);
455 --
456 -- Issue a SAVEPOINT IF operating in validation only mode
457 --
458 SAVEPOINT delete_rate_value;
459 --
460 l_effective_date := TRUNC(p_effective_date);
461 --
462 hr_utility.set_location(l_proc, 20);
463 --
464 -- Process Logic
465 --
466 l_object_version_number := p_object_version_number;
467 --
468 BEGIN
469 --
470 -- Start of API User Hook for the before hook of delete_rate_value
471 --
472 hr_rate_value_bk3.delete_rate_value_b
473 (p_grade_rule_id => p_grade_rule_id
474 ,p_object_version_number => l_object_version_number
475 ,p_effective_date => l_effective_date);
476 --
477 EXCEPTION
478 --
479 WHEN hr_api.cannot_find_prog_unit THEN
480 --
481 hr_api.cannot_find_prog_unit_error
482 (p_module_name => 'delete_rate_value'
483 ,p_hook_type => 'BP');
484 --
485 -- End of API User Hook for the before hook of delete_rate_value
486 --
487 END;
488 --
489 hr_utility.set_location(l_proc, 30);
490 --
491 pay_pgr_del.del
492 (p_effective_date => p_effective_date
493 ,p_datetrack_mode => p_datetrack_mode
494 ,p_grade_rule_id => p_grade_rule_id
495 ,p_object_version_number => l_object_version_number
496 ,p_effective_start_date => l_effective_start_date
497 ,p_effective_end_date => l_effective_END_date);
498 --
499 BEGIN
500 --
501 -- Start of API User Hook for the after hook of delete_rate_value
502 --
503 hr_rate_value_bk3.delete_rate_value_a
504 (p_grade_rule_id => p_grade_rule_id
505 ,p_effective_start_date => l_effective_start_date
506 ,p_effective_end_date => l_effective_end_date
507 ,p_object_version_number => l_object_version_number
508 ,p_effective_date => l_effective_date);
509 --
510 EXCEPTION
511 --
512 WHEN hr_api.cannot_find_prog_unit THEN
513 --
514 hr_api.cannot_find_prog_unit_error
515 (p_module_name => 'delete_rate_value'
516 ,p_hook_type => 'AP'
517 );
518 --
519 -- End of API User Hook for the after hook of delete_entitlement_line
520 --
521 END;
522 --
523 hr_utility.set_location(l_proc, 40);
524 --
525 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
526 --
527 IF p_validate THEN
528 --
529 RAISE hr_api.validate_enabled;
530 --
531 END IF;
532 --
533 -- Set all output arguments
534 --
535 p_object_version_number := l_object_version_number;
536 p_effective_start_date := l_effective_start_date;
537 p_effective_end_date := l_effective_end_date;
538 --
539 hr_utility.set_location(' Leaving:'||l_proc, 999);
540 --
541 EXCEPTION
542 --
543 WHEN hr_api.validate_enabled THEN
544 --
545 -- As the Validate_Enabled EXCEPTION has been RAISEd
546 -- we must rollback to the SAVEPOINT
547 --
548 ROLLBACK TO delete_rate_value;
549 --
550 -- Only set output warning arguments
551 -- (Any key or derived arguments must be set to null
552 -- WHEN validation only mode is being used.)
553 --
554 p_effective_start_date := null;
555 p_effective_end_date := null;
556
557 p_object_version_number := l_temp_ovn;
558 --
559 hr_utility.set_location(' Leaving:'||l_proc, 998);
560 --
561 WHEN others THEN
562 --
563 -- A validation or unexpected error has occured
564 --
565 ROLLBACK TO delete_rate_value;
566 -- Reset IN OUT parameters and set OUT parameters.
567 p_object_version_number := l_temp_ovn;
568 p_effective_start_date := null;
569 p_effective_end_date := null;
570 RAISE;
571 --
572 END delete_rate_value;
573 --
574 -- ----------------------------------------------------------------------------
575 -- |----------------------< create_assignment_rate_value >--------------------|
576 -- ----------------------------------------------------------------------------
577 --
578 PROCEDURE create_assignment_rate_value
579 (p_validate IN BOOLEAN DEFAULT FALSE
580 ,p_effective_date IN DATE
581 ,p_business_group_id IN NUMBER
582 ,p_rate_id IN NUMBER
583 ,p_assignment_id IN NUMBER
584 ,p_rate_type IN VARCHAR2
585 ,p_currency_code IN VARCHAR2 DEFAULT NULL
586 ,p_value IN VARCHAR2
587 ,p_grade_rule_id OUT NOCOPY NUMBER
588 ,p_object_version_number OUT NOCOPY NUMBER
589 ,p_effective_start_date OUT NOCOPY DATE
590 ,p_effective_end_date OUT NOCOPY DATE) IS
591 --
592 -- Declare cursors and local variables
593 --
594 l_grade_rule_id pay_grade_rules_f.grade_rule_id%TYPE;
595 l_effective_start_date pay_grade_rules_f.effective_start_date%TYPE;
596 l_effective_end_date pay_grade_rules_f.effective_end_date%TYPE;
597 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
598 l_effective_date DATE;
599 l_default_rate_type pay_grade_rules_f.rate_type%TYPE;
600 l_proc VARCHAR2(72) := g_package||'create_assignment_rate_value';
601 --
602 l_rate_uom pay_rates.rate_uom%TYPE;
603 l_currency_code pay_grade_rules_f.currency_code%TYPE;
604 --
605 cursor csr_get_rate_uom is
606 select rate_uom
607 from pay_rates
608 where rate_id = p_rate_id;
609 --
610 cursor csr_get_curr_code is
611 select currency_code
612 from per_business_groups_perf
613 where business_group_id = p_business_group_id;
614 --
615 BEGIN
616 --
617 hr_utility.set_location('Entering:'|| l_proc, 10);
618 --
619 -- Issue a SAVEPOINT IF operating in validation only mode
620 --
621 SAVEPOINT create_assignment_rate_value;
622 --
623 -- Truncate date paramters
624 --
625 l_effective_date := TRUNC(p_effective_date);
626 --
627 -- Default the currency code if required.
628 --
629 IF p_currency_code IS NULL THEN
630 --
631 hr_utility.set_location(l_proc, 20);
632 --
633 -- Fetch the Rate Unit Of Measure
634 --
635 OPEN csr_get_rate_uom;
636 --
637 FETCH csr_get_rate_uom INTO l_rate_uom;
638 IF csr_get_rate_uom%NOTFOUND THEN
639 CLOSE csr_get_rate_uom;
640 --
641 hr_utility.set_location(l_proc, 21);
642 --
643 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
644 hr_utility.raise_error;
645 END IF;
646 --
647 CLOSE csr_get_rate_uom;
648 --
649 hr_utility.set_location(l_proc, 22);
650 --
651 IF l_rate_uom = 'M' THEN
652 --
653 -- Get default currency code from business group.
654 --
655 hr_utility.set_location(l_proc, 23);
656 --
657 OPEN csr_get_curr_code;
658 --
659 FETCH csr_get_curr_code INTO l_currency_code;
660 IF csr_get_curr_code%NOTFOUND THEN
661 CLOSE csr_get_curr_code;
662 --
663 hr_utility.set_location(l_proc, 24);
664 --
665 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
666 hr_utility.raise_error;
667 END IF;
668 --
669 CLOSE csr_get_curr_code;
670 --
671 ELSE
672 l_currency_code := p_currency_code;
673 END IF;
674 ELSE
675 l_currency_code := p_currency_code;
676 END IF;
677 --
678 -- Process Logic
679 --
680 --
681 -- Set the default rate type to be Assignment Rate
682 --
683 l_default_rate_type := 'A';
684 --
685 hr_utility.set_location(l_proc, 40);
686 --
687 hr_rate_values_api.create_rate_value
688 (p_validate => p_validate
689 ,p_effective_date => l_effective_date
690 ,p_business_group_id => p_business_group_id
691 ,p_rate_id => p_rate_id
692 ,p_grade_or_spinal_point_id => p_assignment_id
693 ,p_rate_type => l_default_rate_type
694 ,p_currency_code => l_currency_code
695 ,p_value => p_value
696 ,p_grade_rule_id => l_grade_rule_id
697 ,p_object_version_number => l_object_version_number
698 ,p_effective_start_date => l_effective_start_date
699 ,p_effective_end_date => l_effective_end_date);
700
701 --
702 hr_utility.set_location(l_proc, 50);
703 --
704 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
705 --
706 IF p_validate THEN
707 --
708 RAISE hr_api.validate_enabled;
709 --
710 END IF;
711 --
712 -- Set all output arguments
713 --
714 p_grade_rule_id := l_grade_rule_id;
715 p_effective_start_date := l_effective_start_date;
716 p_effective_end_date := l_effective_END_date;
717 p_object_version_number := l_object_version_number;
718 --
719 hr_utility.set_location(' Leaving:'||l_proc, 999);
720 --
721 EXCEPTION
722 --
723 WHEN hr_api.validate_enabled THEN
724 --
725 -- As the Validate_Enabled EXCEPTION has been RAISEd
726 -- we must rollback to the SAVEPOINT
727 --
728 ROLLBACK TO create_assignment_rate_value;
729 --
730 -- Only set output warning arguments
731 -- (Any key or derived arguments must be set to null
732 -- WHEN validation only mode is being used.)
733 --
734 p_grade_rule_id := null;
735 p_effective_start_date := null;
736 p_effective_end_date := null;
737 p_object_version_number := null;
738 --
739 hr_utility.set_location(' Leaving:'||l_proc, 998);
740 --
741 WHEN others THEN
742 --
743 -- A validation or unexpected error has occured
744 --
745 ROLLBACK TO create_assignment_rate_value;
746 -- set OUT parameters.
747 p_grade_rule_id := null;
748 p_object_version_number := null;
749 p_effective_start_date := null;
750 p_effective_end_date := null;
751
752 RAISE;
753 --
754 END create_assignment_rate_value;
755 --
756 -- ----------------------------------------------------------------------------
757 -- |----------------------< update_assignment_rate_value >--------------------|
758 -- ----------------------------------------------------------------------------
759 --
760 PROCEDURE update_assignment_rate_value
761 (p_validate IN BOOLEAN DEFAULT FALSE
762 ,p_grade_rule_id IN NUMBER
763 ,p_effective_date IN DATE
764 ,p_datetrack_mode IN VARCHAR2
765 ,p_currency_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
766 ,p_value IN VARCHAR2 DEFAULT hr_api.g_varchar2
767 ,p_object_version_number IN OUT NOCOPY NUMBER
768 ,p_effective_start_date OUT NOCOPY DATE
769 ,p_effective_end_date OUT NOCOPY DATE) IS
770 --
771 -- Declare cursors and local variables
772 --
773 l_proc VARCHAR2(72) := g_package||'update_assignment_rate_value';
774 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
775 l_effective_start_date pay_grade_rules_f.effective_start_date%TYPE;
776 l_effective_end_date pay_grade_rules_f.effective_END_date%TYPE;
777 l_effective_date DATE;
778 l_temp_ovn number := p_object_version_number;
779 --
780 BEGIN
781 --
782 hr_utility.set_location('Entering:'|| l_proc, 10);
783 --
784 l_effective_date := TRUNC(p_effective_date);
785 --
786 -- Issue a SAVEPOINT IF operating in validation only mode
787 --
788 SAVEPOINT update_assignment_rate_value;
789 --
790 hr_utility.set_location(l_proc, 20);
791 --
792 -- Process Logic
793 --
794 l_object_version_number := p_object_version_number;
795 --
796 hr_utility.set_location(l_proc, 30);
797 --
798 hr_rate_values_api.update_rate_value
799 (p_validate => p_validate
800 ,p_grade_rule_id => p_grade_rule_id
801 ,p_effective_date => l_effective_date
802 ,p_datetrack_mode => p_datetrack_mode
803 ,p_currency_code => p_currency_code
804 ,p_value => p_value
805 ,p_object_version_number => l_object_version_number
806 ,p_effective_start_date => l_effective_start_date
807 ,p_effective_end_date => l_effective_end_date);
808 --
809 hr_utility.set_location(l_proc, 40);
810 --
811 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
812 --
813 IF p_validate THEN
814 --
815 RAISE hr_api.validate_enabled;
816 --
817 END IF;
818 --
819 -- Set all output arguments
820 --
821 p_object_version_number := l_object_version_number;
822 p_effective_start_date := l_effective_start_date;
823 p_effective_end_date := l_effective_end_date;
824 --
825 hr_utility.set_location(' Leaving:'||l_proc, 999);
826 --
827 EXCEPTION
828 --
829 WHEN hr_api.validate_enabled THEN
830 --
831 -- As the Validate_Enabled EXCEPTION has been raised
832 -- we must rollback to the SAVEPOINT
833 --
834 ROLLBACK TO update_assignment_rate_value;
835 --
836 -- Only set output warning arguments
837 -- (Any key or derived arguments must be set to null
838 -- WHEN validation only mode is being used.)
839 --
840 p_effective_start_date := l_effective_start_date;
841 p_effective_end_date := l_effective_end_date;
842
843 p_object_version_number := l_temp_ovn;
844 --
845 hr_utility.set_location(' Leaving:'||l_proc, 998);
846 --
847 WHEN others THEN
848 --
849 -- A validation or unexpected error has occured
850 --
851 ROLLBACK TO update_assignment_rate_value;
852 p_object_version_number := l_temp_ovn;
853 p_effective_start_date := null;
854 p_effective_end_date := null;
855
856 RAISE;
857 --
858 END update_assignment_rate_value;
859 --
860 -- ----------------------------------------------------------------------------
861 -- |-------------------------------< lck >------------------------------------|
862 -- ----------------------------------------------------------------------------
863 --
864 PROCEDURE lck
865 (p_grade_rule_id IN NUMBER
866 ,p_object_version_number IN NUMBER
867 ,p_effective_date IN DATE
868 ,p_datetrack_mode IN VARCHAR2
869 ,p_validation_start_date OUT NOCOPY DATE
870 ,p_validation_end_date OUT NOCOPY DATE ) IS
871 --
872 -- Declare cursors and local variables
873 --
874 l_proc VARCHAR2(72) := g_package||'lck';
875 l_validation_start_date DATE;
876 l_validation_end_date DATE;
877 --
878 BEGIN
879 --
880 hr_utility.set_location('Entering:'|| l_proc, 10);
881 --
882 /*
883 per_dar_shd.lck
884 (p_grade_rule_id => p_grade_rule_id
885 ,p_datetrack_mode => p_datetrack_mode
886 ,p_object_version_number => p_object_version_number
887 ,p_effective_date => p_effective_date
888 ,p_validation_start_date => l_validation_start_date
889 ,p_validation_end_date => l_validation_end_date);
890 --
891 */
892 hr_utility.set_location(' Leaving:'||l_proc, 70);
893 --
894 exception
895 when others then
896 p_validation_start_date := null;
897 p_validation_end_date := null;
898 raise;
899
900 END lck;
901 --
902 END hr_rate_values_api;