[Home] [Help]
PACKAGE BODY: APPS.HR_RATE_API
Source
1 PACKAGE BODY hr_rate_api AS
2 /* $Header: pypyrapi.pkb 115.5 2004/08/09 00:18:52 jpthomas noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_rate_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------------< create_rate >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 PROCEDURE create_rate
13 (p_validate IN BOOLEAN DEFAULT FALSE
14 ,p_effective_date IN DATE
15 ,p_business_group_id IN NUMBER
16 ,p_name IN VARCHAR2
17 ,p_rate_type IN VARCHAR2
18 ,p_rate_uom IN VARCHAR2
19 ,p_parent_spine_id IN NUMBER DEFAULT NULL
20 ,p_comments IN VARCHAR2 DEFAULT NULL
21 ,p_rate_basis IN VARCHAR2 DEFAULT NULL
22 ,p_asg_rate_type IN VARCHAR2 DEFAULT NULL
23 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
24 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
25 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
26 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
27 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
28 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
29 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
30 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
31 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
32 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
33 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
34 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
35 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
36 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
37 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
38 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
39 ,p_attribute16 IN VARCHAR2 DEFAULT NULL
40 ,p_attribute17 IN VARCHAR2 DEFAULT NULL
41 ,p_attribute18 IN VARCHAR2 DEFAULT NULL
42 ,p_attribute19 IN VARCHAR2 DEFAULT NULL
43 ,p_attribute20 IN VARCHAR2 DEFAULT NULL
44 ,p_object_version_number OUT NOCOPY NUMBER
45 ,p_rate_id OUT NOCOPY NUMBER) IS
46 --
47 -- Declare cursors and local variables
48 --
49 l_proc VARCHAR2(72) := g_package||'create_rate';
50 l_effective_date DATE;
51 l_object_version_number NUMBER;
52 l_rate_id pay_rates.rate_id%TYPE;
53 -- Bug 3795968 Starts Here
54 CURSOR csr_full_hr_installed IS
55 SELECT null
56 FROM fnd_product_installations i
57 WHERE i.status = 'I'
58 AND i.application_id = 800;
59 --
60 l_dummy varchar2(1);
61 -- Bug 3795968 Ends Here
62 --
63 BEGIN
64 --
65 hr_utility.set_location('Entering:'|| l_proc, 10);
66 --
67 -- Issue a savepoint if operating in validation only mode
68 --
69 SAVEPOINT create_rate;
70 --
71 hr_utility.set_location(l_proc, 20);
72 --
73 l_effective_date := TRUNC(p_effective_date);
74 --
75 -- Process Logic
76 --
77 BEGIN
78 --
79 -- Start of API User Hook for the before hook of create_rate
80 --
81 hr_rate_api_bk1.create_rate_b
82 (p_effective_date => l_effective_date
83 ,p_business_group_id => p_business_group_id
84 ,p_name => p_name
85 ,p_rate_type => p_rate_type
86 ,p_rate_uom => p_rate_uom
87 ,p_parent_spine_id => p_parent_spine_id
88 ,p_comments => p_comments
89 ,p_rate_basis => p_rate_basis
90 ,p_asg_rate_type => p_asg_rate_type
91 ,p_attribute_category => p_attribute_category
92 ,p_attribute1 => p_attribute1
93 ,p_attribute2 => p_attribute2
94 ,p_attribute3 => p_attribute3
95 ,p_attribute4 => p_attribute4
96 ,p_attribute5 => p_attribute5
97 ,p_attribute6 => p_attribute6
98 ,p_attribute7 => p_attribute7
99 ,p_attribute8 => p_attribute8
100 ,p_attribute9 => p_attribute9
101 ,p_attribute10 => p_attribute11
102 ,p_attribute11 => p_attribute12
103 ,p_attribute12 => p_attribute13
104 ,p_attribute13 => p_attribute14
105 ,p_attribute14 => p_attribute15
106 ,p_attribute15 => p_attribute16
107 ,p_attribute16 => p_attribute17
108 ,p_attribute17 => p_attribute18
109 ,p_attribute18 => p_attribute19
110 ,p_attribute19 => p_attribute19
111 ,p_attribute20 => p_attribute20);
112 --
113 EXCEPTION
114 WHEN hr_api.cannot_find_prog_unit THEN
115 hr_api.cannot_find_prog_unit_error
116 (p_module_name => 'create_rate'
117 ,p_hook_type => 'BP');
118 --
119 -- End of API User Hook for the before hook of create_rate
120 --
121 END;
122 --
123 hr_utility.set_location(l_proc, 30);
124 --
125 pay_pyr_ins.ins
126 (p_effective_date => l_effective_date
127 ,p_business_group_id => p_business_group_id
128 ,p_name => p_name
129 ,p_rate_type => p_rate_type
130 ,p_rate_uom => p_rate_uom
131 ,p_parent_spine_id => p_parent_spine_id
132 ,p_comments => p_comments
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_rate_basis => p_rate_basis
155 ,p_asg_rate_type => p_asg_rate_type
156 ,p_rate_id => l_rate_id
157 ,p_object_version_number => l_object_version_number);
158 --
159 hr_utility.set_location(l_proc, 40);
160 --
161 --
162 -- Start of fix 3273216
163 -- calling database package to insert database item
164 -- Bug 3795968 Starts Here
165 OPEN csr_full_hr_installed;
166 FETCH csr_full_hr_installed INTO l_dummy;
167 IF csr_full_hr_installed%FOUND THEN
168 hrdyndbi.create_grade_spine_dict(p_rate_id => l_rate_id);
169 END IF;
170 CLOSE csr_full_hr_installed;
171 -- Bug 3795968 Ends Here
172 --
173 hr_utility.set_location(l_proc, 45);
174 --
175 -- End of 3273216
176 BEGIN
177 --
178 -- Start of API User Hook for the after hook of create_rate
179 --
180 hr_rate_api_bk1.create_rate_a
181 (p_effective_date => l_effective_date
182 ,p_rate_id => l_rate_id
183 ,p_object_version_number => l_object_version_number
184 ,p_business_group_id => p_business_group_id
185 ,p_name => p_name
186 ,p_rate_type => p_rate_type
187 ,p_rate_uom => p_rate_uom
188 ,p_parent_spine_id => p_parent_spine_id
189 ,p_comments => p_comments
190 ,p_rate_basis => p_rate_basis
191 ,p_asg_rate_type => p_asg_rate_type
192 ,p_attribute_category => p_attribute_category
193 ,p_attribute1 => p_attribute1
194 ,p_attribute2 => p_attribute2
195 ,p_attribute3 => p_attribute3
196 ,p_attribute4 => p_attribute4
197 ,p_attribute5 => p_attribute5
198 ,p_attribute6 => p_attribute6
199 ,p_attribute7 => p_attribute7
200 ,p_attribute8 => p_attribute8
201 ,p_attribute9 => p_attribute9
202 ,p_attribute10 => p_attribute11
203 ,p_attribute11 => p_attribute12
204 ,p_attribute12 => p_attribute13
205 ,p_attribute13 => p_attribute14
206 ,p_attribute14 => p_attribute15
207 ,p_attribute15 => p_attribute16
208 ,p_attribute16 => p_attribute17
209 ,p_attribute17 => p_attribute18
210 ,p_attribute18 => p_attribute19
211 ,p_attribute19 => p_attribute19
212 ,p_attribute20 => p_attribute20);
213 --
214 EXCEPTION
215 WHEN hr_api.cannot_find_prog_unit THEN
216 hr_api.cannot_find_prog_unit_error
217 (p_module_name => 'create_rate'
218 ,p_hook_type => 'AP');
219 --
220 -- End of API User Hook for the after hook of create_rate
221 --
222 END;
223 --
224 hr_utility.set_location(l_proc, 50);
225 --
226 -- When in validation only mode raise the Validate_Enabled exception
227 --
228 IF p_validate THEN
229 --
230 RAISE hr_api.validate_enabled;
231 --
232 END IF;
233 --
234 -- Set all output arguments
235 --
236 p_object_version_number := l_object_version_number;
237 p_rate_id := l_rate_id;
238 --
239 hr_utility.set_location(' Leaving:'||l_proc, 999);
240 --
241 EXCEPTION
242 --
243 WHEN hr_api.validate_enabled THEN
244 --
245 -- As the Validate_Enabled exception has been raised
246 -- we must rollback to the savepoint
247 --
248 ROLLBACK TO create_rate;
249 --
250 -- Only set output warning arguments
251 -- (Any key or derived arguments must be set to null
252 -- when validation only mode is being used.)
253 --
254 p_object_version_number := NULL;
255 --
256 hr_utility.set_location(' Leaving:'||l_proc, 998);
257 --
258 WHEN others THEN
259 --
260 -- A validation or unexpected error has occured
261 --
262 ROLLBACK TO create_rate;
263 --
264 -- Bugfix 2692195
265 -- Reset all OUT/IN OUT parameters
266 --
267 p_object_version_number := null;
268 p_rate_id := null;
269 --
270 RAISE;
271 --
272 END create_rate;
273 --
274 -- ----------------------------------------------------------------------------
275 -- |--------------------------------< update_rate >---------------------------|
276 -- ----------------------------------------------------------------------------
277 --
278 PROCEDURE update_rate
279 (p_validate IN BOOLEAN DEFAULT FALSE
280 ,p_rate_id IN NUMBER
281 ,p_object_version_number IN OUT NOCOPY NUMBER
282 ,p_effective_date IN DATE
283 ,p_name IN VARCHAR2 DEFAULT hr_api.g_varchar2
284 ,p_rate_uom IN VARCHAR2 DEFAULT hr_api.g_varchar2
285 ,p_parent_spine_id IN NUMBER DEFAULT hr_api.g_number
286 ,p_comments IN VARCHAR2 DEFAULT hr_api.g_varchar2
287 ,p_rate_basis IN VARCHAR2 DEFAULT hr_api.g_varchar2
288 ,p_asg_rate_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
289 ,p_attribute_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
290 ,p_attribute1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
291 ,p_attribute2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
292 ,p_attribute3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
293 ,p_attribute4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
294 ,p_attribute5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
295 ,p_attribute6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
296 ,p_attribute7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
297 ,p_attribute8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
298 ,p_attribute9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
299 ,p_attribute10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
300 ,p_attribute11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
301 ,p_attribute12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
302 ,p_attribute13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
303 ,p_attribute14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
304 ,p_attribute15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
305 ,p_attribute16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
306 ,p_attribute17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
307 ,p_attribute18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
308 ,p_attribute19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
309 ,p_attribute20 IN VARCHAR2 DEFAULT hr_api.g_varchar2) IS
310 --
311 -- Declare cursors and local variables
312 --
313 l_proc VARCHAR2(72) := g_package||'update_rate';
314 l_object_version_number NUMBER;
315 l_effective_date DATE;
316 -- Bug 3795968 Starts Here
317 CURSOR csr_full_hr_installed IS
318 SELECT null
319 FROM fnd_product_installations i
320 WHERE i.status = 'I'
321 AND i.application_id = 800;
322 --
323 l_dummy varchar2(1);
324 -- Bug 3795968 Ends Here
325 --
326 BEGIN
327 --
328 hr_utility.set_location('Entering:'|| l_proc, 10);
329 --
330 -- Issue a savepoint if operating in validation only mode
331 --
332 SAVEPOINT update_rate;
333 --
334 l_effective_date := TRUNC(p_effective_date);
335 --
336 -- Process Logic
337 --
338 l_object_version_number := p_object_version_number;
339 --
340 hr_utility.set_location(l_proc, 20);
341 --
342 BEGIN
343 --
344 -- Start of API User Hook for the before hook of update_rate
345 --
346 hr_rate_api_bk2.update_rate_a
347 (p_effective_date => l_effective_date
348 ,p_rate_id => p_rate_id
349 ,p_object_version_number => l_object_version_number
350 ,p_name => p_name
351 ,p_rate_uom => p_rate_uom
352 ,p_parent_spine_id => p_parent_spine_id
353 ,p_comments => p_comments
354 ,p_rate_basis => p_rate_basis
355 ,p_asg_rate_type => p_asg_rate_type
356 ,p_attribute_category => p_attribute_category
357 ,p_attribute1 => p_attribute1
358 ,p_attribute2 => p_attribute2
359 ,p_attribute3 => p_attribute3
360 ,p_attribute4 => p_attribute4
361 ,p_attribute5 => p_attribute5
362 ,p_attribute6 => p_attribute6
363 ,p_attribute7 => p_attribute7
364 ,p_attribute8 => p_attribute8
365 ,p_attribute9 => p_attribute9
366 ,p_attribute10 => p_attribute11
367 ,p_attribute11 => p_attribute12
368 ,p_attribute12 => p_attribute13
369 ,p_attribute13 => p_attribute14
370 ,p_attribute14 => p_attribute15
371 ,p_attribute15 => p_attribute16
372 ,p_attribute16 => p_attribute17
373 ,p_attribute17 => p_attribute18
374 ,p_attribute18 => p_attribute19
375 ,p_attribute19 => p_attribute19
376 ,p_attribute20 => p_attribute20);
377 --
378 EXCEPTION
379 WHEN hr_api.cannot_find_prog_unit THEN
380 hr_api.cannot_find_prog_unit_error
381 (p_module_name => 'update_rate'
382 ,p_hook_type => 'BP');
383 --
384 -- End of API User Hook for the before hook of update_rate
385 --
386 END;
387 --
388 hr_utility.set_location(l_proc, 30);
389 --
390 pay_pyr_upd.upd
391 (p_effective_date => l_effective_date
392 ,p_rate_id => p_rate_id
393 ,p_object_version_number => l_object_version_number
394 ,p_name => p_name
395 ,p_rate_uom => p_rate_uom
396 ,p_parent_spine_id => p_parent_spine_id
397 ,p_comments => p_comments
398 ,p_rate_basis => p_rate_basis
399 ,p_asg_rate_type => p_asg_rate_type
400 ,p_attribute_category => p_attribute_category
401 ,p_attribute1 => p_attribute1
402 ,p_attribute2 => p_attribute2
403 ,p_attribute3 => p_attribute3
404 ,p_attribute4 => p_attribute4
405 ,p_attribute5 => p_attribute5
406 ,p_attribute6 => p_attribute6
407 ,p_attribute7 => p_attribute7
408 ,p_attribute8 => p_attribute8
409 ,p_attribute9 => p_attribute9
410 ,p_attribute10 => p_attribute10
411 ,p_attribute11 => p_attribute11
412 ,p_attribute12 => p_attribute12
413 ,p_attribute13 => p_attribute13
414 ,p_attribute14 => p_attribute14
415 ,p_attribute15 => p_attribute15
416 ,p_attribute16 => p_attribute16
417 ,p_attribute17 => p_attribute17
418 ,p_attribute18 => p_attribute18
419 ,p_attribute19 => p_attribute19
420 ,p_attribute20 => p_attribute20);
421 --
422 hr_utility.set_location(l_proc, 40);
423 --
424 -- Start of fix 3273216
425 -- calling database package to first delete the existing database item
426 -- and then call the database package to insert the updated database item
427 -- Bug 3795968 Starts Here
428 OPEN csr_full_hr_installed;
432 hrdyndbi.create_grade_spine_dict(p_rate_id => p_rate_id);
429 FETCH csr_full_hr_installed INTO l_dummy;
430 IF csr_full_hr_installed%FOUND THEN
431 hrdyndbi.delete_grade_spine_dict(p_rate_id => p_rate_id);
433 END IF;
434 CLOSE csr_full_hr_installed;
435 -- Bug 3795968 Ends Here
436 --
437 hr_utility.set_location(l_proc, 45);
438 --
439 -- End of 3273216
440 BEGIN
441 --
442 -- Start of API User Hook for the after hook of update_rate
443 --
444 hr_rate_api_bk2.update_rate_a
445 (p_effective_date => l_effective_date
446 ,p_rate_id => p_rate_id
447 ,p_object_version_number => l_object_version_number
448 ,p_name => p_name
449 ,p_rate_uom => p_rate_uom
450 ,p_parent_spine_id => p_parent_spine_id
451 ,p_comments => p_comments
452 ,p_rate_basis => p_rate_basis
453 ,p_asg_rate_type => p_asg_rate_type
454 ,p_attribute_category => p_attribute_category
455 ,p_attribute1 => p_attribute1
456 ,p_attribute2 => p_attribute2
457 ,p_attribute3 => p_attribute3
458 ,p_attribute4 => p_attribute4
459 ,p_attribute5 => p_attribute5
460 ,p_attribute6 => p_attribute6
461 ,p_attribute7 => p_attribute7
462 ,p_attribute8 => p_attribute8
463 ,p_attribute9 => p_attribute9
464 ,p_attribute10 => p_attribute11
465 ,p_attribute11 => p_attribute12
466 ,p_attribute12 => p_attribute13
467 ,p_attribute13 => p_attribute14
468 ,p_attribute14 => p_attribute15
469 ,p_attribute15 => p_attribute16
470 ,p_attribute16 => p_attribute17
471 ,p_attribute17 => p_attribute18
472 ,p_attribute18 => p_attribute19
473 ,p_attribute19 => p_attribute19
474 ,p_attribute20 => p_attribute20);
475 --
476 EXCEPTION
477 WHEN hr_api.cannot_find_prog_unit THEN
478 hr_api.cannot_find_prog_unit_error
479 (p_module_name => 'update_rate'
480 ,p_hook_type => 'AP');
481 --
482 -- End of API User Hook for the after hook of update_rate
483 --
484 END;
485 --
486 hr_utility.set_location(l_proc, 50);
487 --
488 -- When in validation only mode raise the Validate_Enabled exception
489 --
490 IF p_validate THEN
491 --
492 RAISE hr_api.validate_enabled;
493 --
494 END IF;
495 --
496 -- Set all output arguments
497 --
498 p_object_version_number := l_object_version_number;
499 --
500 hr_utility.set_location(' Leaving:'||l_proc, 999);
501 --
502 EXCEPTION
503 --
504 WHEN hr_api.validate_enabled THEN
505 --
506 -- As the Validate_Enabled exception has been raised
507 -- we must rollback to the savepoint
508 --
509 ROLLBACK TO update_rate;
510 --
511 -- Only set output warning arguments
512 -- (Any key or derived arguments must be set to null
513 -- when validation only mode is being used.)
514 --
515 hr_utility.set_location(' Leaving:'||l_proc, 998);
516 --
517 WHEN others THEN
518 --
519 -- A validation or unexpected error has occured
520 --
521 ROLLBACK TO update_rate;
522 --
523 -- Bugfix 2692195
524 -- Reset all OUT/IN OUT parameters
525 --
526 p_object_version_number := l_object_version_number;
527 --
528 RAISE;
529 --
530 END update_rate;
531 --
532 -- ----------------------------------------------------------------------------
533 -- |------------------------------< delete_rate >-----------------------------|
534 -- ----------------------------------------------------------------------------
535 --
536 PROCEDURE delete_rate
537 (p_validate IN BOOLEAN DEFAULT FALSE
538 ,p_effective_date IN DATE
539 ,p_rate_id IN NUMBER
540 ,p_rate_type IN VARCHAR2
541 ,p_object_version_number IN OUT NOCOPY NUMBER) IS
542 --
543 -- Declare cursors and local variables
544 --
545 l_proc VARCHAR2(72) := g_package||'delete_rate';
546 l_object_version_number NUMBER;
547 l_effective_date DATE;
548 -- Bug 3795968 Starts Here
549 CURSOR csr_full_hr_installed IS
550 SELECT null
551 FROM fnd_product_installations i
552 WHERE i.status = 'I'
553 AND i.application_id = 800;
554 --
555 l_dummy varchar2(1);
556 -- Bug 3795968 Ends Here
557 --
558 BEGIN
559 --
560 hr_utility.set_location('Entering:'|| l_proc, 10);
561 --
562 -- Issue a savepoint if operating in validation only mode
563 --
564 SAVEPOINT delete_rate;
565 --
566 hr_utility.set_location(l_proc, 20);
567 --
568 l_effective_date := TRUNC(p_effective_date);
572 l_object_version_number := p_object_version_number;
569 --
570 -- Process Logic
571 --
573 --
574 --
575 BEGIN
576 --
577 -- Start of API User Hook for the before hook of delete_rate
578 --
579 hr_rate_api_bk3.delete_rate_b
580 (p_rate_id => p_rate_id
581 ,p_rate_type => p_rate_type
582 ,p_object_version_number => l_object_version_number
583 ,p_effective_date => l_effective_date);
584 --
585 EXCEPTION
586 WHEN hr_api.cannot_find_prog_unit THEN
587 --
588 hr_api.cannot_find_prog_unit_error
589 (p_module_name => 'delete_rate'
590 ,p_hook_type => 'BP');
591 --
592 -- End of API User Hook for the before hook of delete_rate
593 --
594 END;
595 --
596 hr_utility.set_location( l_proc, 30);
597 --
598 --
599 -- Process Logic
600 -- =============
601 --
602 -- Need to lock main table to maintain the locking ladder order
603 --
604 pay_pyr_shd.lck(p_rate_id => p_rate_id
605 ,p_object_version_number => p_object_version_number);
606 --
607 hr_utility.set_location( l_proc, 40);
608 --
609 pay_pyr_del.del(p_rate_id => p_rate_id
610 ,p_rate_type => p_rate_type
611 ,p_object_version_number => p_object_version_number);
612 --
613 hr_utility.set_location( l_proc, 50);
614 --
615 -- Start of fix 3273216
616 -- calling database package to delete database item
617 -- Bug 3795968 Starts Here
618 OPEN csr_full_hr_installed;
619 FETCH csr_full_hr_installed INTO l_dummy;
620 IF csr_full_hr_installed%FOUND THEN
621 hrdyndbi.delete_grade_spine_dict(p_rate_id => p_rate_id);
622 END IF;
623 CLOSE csr_full_hr_installed;
624 -- Bug 3795968 Ends Here
625 --
626 hr_utility.set_location( l_proc, 55);
627 --
628 -- End of 3273216
629 --
630 BEGIN
631 --
632 -- Start of API User Hook for the after hook of delete_rate
633 --
634 hr_rate_api_bk3.delete_rate_a
635 (p_rate_id => p_rate_id
636 ,p_rate_type => p_rate_type
637 ,p_effective_date => l_effective_date
638 ,p_object_version_number => l_object_version_number);
639 --
640 EXCEPTION
641 --
642 WHEN hr_api.cannot_find_prog_unit THEN
643 --
644 hr_api.cannot_find_prog_unit_error
645 (p_module_name => 'delete_rate'
646 ,p_hook_type => 'AP');
647 --
648 -- End of API User Hook for the after hook of delete_rate
649 --
650 END;
651 --
652 hr_utility.set_location(l_proc, 60);
653 --
654 -- When in validation only mode raise the Validate_Enabled exception
655 --
656 IF p_validate THEN
657 --
658 RAISE hr_api.validate_enabled;
659 --
660 END IF;
661 --
662 hr_utility.set_location(' Leaving:'||l_proc, 999);
663 --
664 EXCEPTION
665 --
666 WHEN hr_api.validate_enabled THEN
667 --
668 -- As the Validate_Enabled exception has been raised
669 -- we must rollback to the savepoint
670 --
671 ROLLBACK TO delete_rate;
672 --
673 -- Only set output warning arguments
674 -- (Any key or derived arguments must be set to null
675 -- when validation only mode is being used.)
676 --
677 --
678 WHEN others THEN
679 --
680 -- A validation or unexpected error has occured
681 --
682 ROLLBACK TO delete_rate;
683 --
684 -- Bugfix 2692195
685 -- Reset all OUT/IN OUT parameters
686 --
687 p_object_version_number := l_object_version_number;
688 --
689 RAISE;
690 --
691 END delete_rate;
692 --
693 PROCEDURE create_assignment_rate
694 (p_validate IN BOOLEAN DEFAULT FALSE
695 ,p_effective_date IN DATE
696 ,p_business_group_id IN NUMBER
697 ,p_name IN VARCHAR2
698 ,p_rate_basis IN VARCHAR2
699 ,p_asg_rate_type IN VARCHAR2 DEFAULT NULL
700 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
701 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
702 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
703 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
704 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
705 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
706 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
707 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
708 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
709 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
710 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
711 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
712 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
716 ,p_attribute16 IN VARCHAR2 DEFAULT NULL
713 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
714 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
715 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
717 ,p_attribute17 IN VARCHAR2 DEFAULT NULL
718 ,p_attribute18 IN VARCHAR2 DEFAULT NULL
719 ,p_attribute19 IN VARCHAR2 DEFAULT NULL
720 ,p_attribute20 IN VARCHAR2 DEFAULT NULL
721 ,p_object_version_number OUT NOCOPY NUMBER
722 ,p_rate_id OUT NOCOPY NUMBER) IS
723 --
724 -- Declare cursors and local variables
725 --
726 l_proc VARCHAR2(72) := g_package||'create_assignment_rate';
727 l_effective_date DATE;
728 l_object_version_number NUMBER;
729 l_rate_id pay_rates.rate_id%TYPE;
730 l_default_rate_type pay_rates.rate_type%TYPE;
731 l_default_rate_uom pay_rates.rate_uom%TYPE;
732 --
733 BEGIN
734 --
735 hr_utility.set_location('Entering:'|| l_proc, 10);
736 --
737 -- Issue a savepoint if operating in validation only mode
738 --
739 SAVEPOINT create_assignment_rate;
740 --
741 hr_utility.set_location(l_proc, 20);
742 --
743 l_effective_date := TRUNC(p_effective_date);
744 --
745 -- Set the rate type to be Assignment
746 --
747 l_default_rate_type := 'A';
748 --
749 -- Set the rate unit of measure to be Money
750 --
751 l_default_rate_uom := 'M';
752 --
753 -- Process Logic
754 --
755 BEGIN
756 --
757 -- Start of API User Hook for the before hook of create_assignment_rate
758 --
759 hr_rate_api_bk4.create_assignment_rate_b
760 (p_effective_date => l_effective_date
761 ,p_business_group_id => p_business_group_id
762 ,p_name => p_name
763 ,p_rate_type => l_default_rate_type
764 ,p_rate_uom => l_default_rate_uom
765 ,p_rate_basis => p_rate_basis
766 ,p_asg_rate_type => p_asg_rate_type
767 ,p_attribute_category => p_attribute_category
768 ,p_attribute1 => p_attribute1
769 ,p_attribute2 => p_attribute2
770 ,p_attribute3 => p_attribute3
771 ,p_attribute4 => p_attribute4
772 ,p_attribute5 => p_attribute5
773 ,p_attribute6 => p_attribute6
774 ,p_attribute7 => p_attribute7
775 ,p_attribute8 => p_attribute8
776 ,p_attribute9 => p_attribute9
777 ,p_attribute10 => p_attribute11
778 ,p_attribute11 => p_attribute12
779 ,p_attribute12 => p_attribute13
780 ,p_attribute13 => p_attribute14
781 ,p_attribute14 => p_attribute15
782 ,p_attribute15 => p_attribute16
783 ,p_attribute16 => p_attribute17
784 ,p_attribute17 => p_attribute18
785 ,p_attribute18 => p_attribute19
786 ,p_attribute19 => p_attribute19
787 ,p_attribute20 => p_attribute20);
788 --
789 EXCEPTION
790 WHEN hr_api.cannot_find_prog_unit THEN
791 hr_api.cannot_find_prog_unit_error
792 (p_module_name => 'create_assignment_rate'
793 ,p_hook_type => 'BP');
794 --
795 -- End of API User Hook for the before hook of create_assignment_rate
796 --
797 END;
798 --
799 hr_utility.set_location(l_proc, 30);
800 --
801 hr_rate_api.create_rate
802 (p_effective_date => l_effective_date
803 ,p_business_group_id => p_business_group_id
804 ,p_name => p_name
805 ,p_rate_type => l_default_rate_type
806 ,p_rate_uom => l_default_rate_uom
807 ,p_attribute_category => p_attribute_category
808 ,p_attribute1 => p_attribute1
809 ,p_attribute2 => p_attribute2
810 ,p_attribute3 => p_attribute3
811 ,p_attribute4 => p_attribute4
812 ,p_attribute5 => p_attribute5
813 ,p_attribute6 => p_attribute6
814 ,p_attribute7 => p_attribute7
815 ,p_attribute8 => p_attribute8
816 ,p_attribute9 => p_attribute9
817 ,p_attribute10 => p_attribute10
818 ,p_attribute11 => p_attribute11
819 ,p_attribute12 => p_attribute12
820 ,p_attribute13 => p_attribute13
821 ,p_attribute14 => p_attribute14
822 ,p_attribute15 => p_attribute15
823 ,p_attribute16 => p_attribute16
824 ,p_attribute17 => p_attribute17
825 ,p_attribute18 => p_attribute18
826 ,p_attribute19 => p_attribute19
827 ,p_attribute20 => p_attribute20
828 ,p_rate_basis => p_rate_basis
829 ,p_asg_rate_type => p_asg_rate_type
830 ,p_rate_id => l_rate_id
831 ,p_object_version_number => l_object_version_number);
832 --
833 hr_utility.set_location(l_proc, 40);
834 --
835 BEGIN
836 --
840 (p_effective_date => l_effective_date
837 -- Start of API User Hook for the after hook of create_assignment_rate
838 --
839 hr_rate_api_bk4.create_assignment_rate_a
841 ,p_rate_id => l_rate_id
842 ,p_object_version_number => l_object_version_number
843 ,p_business_group_id => p_business_group_id
844 ,p_name => p_name
845 ,p_rate_type => l_default_rate_type
846 ,p_rate_uom => l_default_rate_uom
847 ,p_rate_basis => p_rate_basis
848 ,p_asg_rate_type => p_asg_rate_type
849 ,p_attribute_category => p_attribute_category
850 ,p_attribute1 => p_attribute1
851 ,p_attribute2 => p_attribute2
852 ,p_attribute3 => p_attribute3
853 ,p_attribute4 => p_attribute4
854 ,p_attribute5 => p_attribute5
855 ,p_attribute6 => p_attribute6
856 ,p_attribute7 => p_attribute7
857 ,p_attribute8 => p_attribute8
858 ,p_attribute9 => p_attribute9
859 ,p_attribute10 => p_attribute11
860 ,p_attribute11 => p_attribute12
861 ,p_attribute12 => p_attribute13
862 ,p_attribute13 => p_attribute14
863 ,p_attribute14 => p_attribute15
864 ,p_attribute15 => p_attribute16
865 ,p_attribute16 => p_attribute17
866 ,p_attribute17 => p_attribute18
867 ,p_attribute18 => p_attribute19
868 ,p_attribute19 => p_attribute19
869 ,p_attribute20 => p_attribute20);
870 --
871 EXCEPTION
872 WHEN hr_api.cannot_find_prog_unit THEN
873 hr_api.cannot_find_prog_unit_error
874 (p_module_name => 'create_assignment_rate'
875 ,p_hook_type => 'AP');
876 --
877 -- End of API User Hook for the after hook of create_assignment_rate
878 --
879 END;
880 --
881 hr_utility.set_location(l_proc, 50);
882 --
883 -- When in validation only mode raise the Validate_Enabled exception
884 --
885 IF p_validate THEN
886 --
887 RAISE hr_api.validate_enabled;
888 --
889 END IF;
890 --
891 -- Set all output arguments
892 --
893 p_object_version_number := l_object_version_number;
894 p_rate_id := l_rate_id;
895 --
896 hr_utility.set_location(' Leaving:'||l_proc, 999);
897 --
898 EXCEPTION
899 --
900 WHEN hr_api.validate_enabled THEN
901 --
902 -- As the Validate_Enabled exception has been raised
903 -- we must rollback to the savepoint
904 --
905 ROLLBACK TO create_assignment_rate;
906 --
907 -- Only set output warning arguments
908 -- (Any key or derived arguments must be set to null
909 -- when validation only mode is being used.)
910 --
911 p_object_version_number := NULL;
912 --
913 hr_utility.set_location(' Leaving:'||l_proc, 998);
914 --
915 WHEN others THEN
916 --
917 -- A validation or unexpected error has occured
918 --
919 ROLLBACK TO create_assignment_rate;
920 --
921 -- Bugfix 2692195
922 -- Reset all OUT/IN OUT parameters
923 --
924 p_object_version_number := null;
925 p_rate_id := null;
926 --
927 RAISE;
928 --
929 END create_assignment_rate;
930 --
931 -- ----------------------------------------------------------------------------
932 -- |-------------------------< update_assignment_rate >-----------------------|
933 -- ----------------------------------------------------------------------------
934 --
935 PROCEDURE update_assignment_rate
936 (p_validate IN BOOLEAN DEFAULT FALSE
937 ,p_rate_id IN NUMBER
938 ,p_object_version_number IN OUT NOCOPY NUMBER
939 ,p_effective_date IN DATE
940 ,p_name IN VARCHAR2 DEFAULT hr_api.g_varchar2
941 ,p_rate_basis IN VARCHAR2 DEFAULT hr_api.g_varchar2
942 ,p_asg_rate_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
943 ,p_attribute_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
944 ,p_attribute1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
945 ,p_attribute2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
946 ,p_attribute3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
947 ,p_attribute4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
948 ,p_attribute5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
949 ,p_attribute6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
950 ,p_attribute7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
951 ,p_attribute8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
952 ,p_attribute9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
953 ,p_attribute10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
954 ,p_attribute11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
955 ,p_attribute12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
956 ,p_attribute13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
960 ,p_attribute17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
957 ,p_attribute14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
958 ,p_attribute15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
959 ,p_attribute16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
961 ,p_attribute18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
962 ,p_attribute19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
963 ,p_attribute20 IN VARCHAR2 DEFAULT hr_api.g_varchar2) IS
964 --
965 -- Declare cursors and local variables
966 --
967 l_proc VARCHAR2(72) := g_package||'update_assignment_rate';
968 l_object_version_number NUMBER;
969 l_effective_date DATE;
970 --
971 BEGIN
972 --
973 hr_utility.set_location('Entering:'|| l_proc, 10);
974 --
975 -- Issue a savepoint if operating in validation only mode
976 --
977 SAVEPOINT update_assignment_rate;
978 --
979 l_effective_date := TRUNC(p_effective_date);
980 --
981 -- Process Logic
982 --
983 l_object_version_number := p_object_version_number;
984 --
985 hr_utility.set_location(l_proc, 20);
986 --
987 BEGIN
988 --
989 -- Start of API User Hook for the before hook of update_assignment_rate
990 --
991 hr_rate_api_bk5.update_assignment_rate_a
992 (p_effective_date => l_effective_date
993 ,p_rate_id => p_rate_id
994 ,p_object_version_number => l_object_version_number
995 ,p_name => p_name
996 ,p_rate_basis => p_rate_basis
997 ,p_asg_rate_type => p_asg_rate_type
998 ,p_attribute_category => p_attribute_category
999 ,p_attribute1 => p_attribute1
1000 ,p_attribute2 => p_attribute2
1001 ,p_attribute3 => p_attribute3
1002 ,p_attribute4 => p_attribute4
1003 ,p_attribute5 => p_attribute5
1004 ,p_attribute6 => p_attribute6
1005 ,p_attribute7 => p_attribute7
1006 ,p_attribute8 => p_attribute8
1007 ,p_attribute9 => p_attribute9
1008 ,p_attribute10 => p_attribute11
1009 ,p_attribute11 => p_attribute12
1010 ,p_attribute12 => p_attribute13
1011 ,p_attribute13 => p_attribute14
1012 ,p_attribute14 => p_attribute15
1013 ,p_attribute15 => p_attribute16
1014 ,p_attribute16 => p_attribute17
1015 ,p_attribute17 => p_attribute18
1016 ,p_attribute18 => p_attribute19
1017 ,p_attribute19 => p_attribute19
1018 ,p_attribute20 => p_attribute20);
1019 --
1020 EXCEPTION
1021 WHEN hr_api.cannot_find_prog_unit THEN
1022 hr_api.cannot_find_prog_unit_error
1023 (p_module_name => 'update_assignment_rate'
1024 ,p_hook_type => 'BP');
1025 --
1026 -- End of API User Hook for the before hook of update_assignment_rate
1027 --
1028 END;
1029 --
1030 hr_utility.set_location(l_proc, 30);
1031 --
1032 hr_rate_api.update_rate
1033 (p_effective_date => l_effective_date
1034 ,p_rate_id => p_rate_id
1035 ,p_object_version_number => l_object_version_number
1036 ,p_name => p_name
1037 ,p_rate_basis => p_rate_basis
1038 ,p_asg_rate_type => p_asg_rate_type
1039 ,p_attribute_category => p_attribute_category
1040 ,p_attribute1 => p_attribute1
1041 ,p_attribute2 => p_attribute2
1042 ,p_attribute3 => p_attribute3
1043 ,p_attribute4 => p_attribute4
1044 ,p_attribute5 => p_attribute5
1045 ,p_attribute6 => p_attribute6
1046 ,p_attribute7 => p_attribute7
1047 ,p_attribute8 => p_attribute8
1048 ,p_attribute9 => p_attribute9
1049 ,p_attribute10 => p_attribute10
1050 ,p_attribute11 => p_attribute11
1051 ,p_attribute12 => p_attribute12
1052 ,p_attribute13 => p_attribute13
1053 ,p_attribute14 => p_attribute14
1054 ,p_attribute15 => p_attribute15
1055 ,p_attribute16 => p_attribute16
1056 ,p_attribute17 => p_attribute17
1057 ,p_attribute18 => p_attribute18
1058 ,p_attribute19 => p_attribute19
1059 ,p_attribute20 => p_attribute20);
1060 --
1061 hr_utility.set_location(l_proc, 40);
1062 --
1063 BEGIN
1064 --
1065 -- Start of API User Hook for the after hook of update_assignment_rate
1066 --
1067 hr_rate_api_bk5.update_assignment_rate_a
1068 (p_effective_date => l_effective_date
1069 ,p_rate_id => p_rate_id
1070 ,p_object_version_number => l_object_version_number
1071 ,p_name => p_name
1072 ,p_rate_basis => p_rate_basis
1073 ,p_asg_rate_type => p_asg_rate_type
1077 ,p_attribute3 => p_attribute3
1074 ,p_attribute_category => p_attribute_category
1075 ,p_attribute1 => p_attribute1
1076 ,p_attribute2 => p_attribute2
1078 ,p_attribute4 => p_attribute4
1079 ,p_attribute5 => p_attribute5
1080 ,p_attribute6 => p_attribute6
1081 ,p_attribute7 => p_attribute7
1082 ,p_attribute8 => p_attribute8
1083 ,p_attribute9 => p_attribute9
1084 ,p_attribute10 => p_attribute11
1085 ,p_attribute11 => p_attribute12
1086 ,p_attribute12 => p_attribute13
1087 ,p_attribute13 => p_attribute14
1088 ,p_attribute14 => p_attribute15
1089 ,p_attribute15 => p_attribute16
1090 ,p_attribute16 => p_attribute17
1091 ,p_attribute17 => p_attribute18
1092 ,p_attribute18 => p_attribute19
1093 ,p_attribute19 => p_attribute19
1094 ,p_attribute20 => p_attribute20);
1095 --
1096 EXCEPTION
1097 WHEN hr_api.cannot_find_prog_unit THEN
1098 hr_api.cannot_find_prog_unit_error
1099 (p_module_name => 'update_assignment_rate'
1100 ,p_hook_type => 'AP');
1101 --
1102 -- End of API User Hook for the after hook of update_assignment_rate
1103 --
1104 END;
1105 --
1106 hr_utility.set_location(l_proc, 50);
1107 --
1108 -- When in validation only mode raise the Validate_Enabled exception
1109 --
1110 IF p_validate THEN
1111 --
1112 RAISE hr_api.validate_enabled;
1113 --
1114 END IF;
1115 --
1116 -- Set all output arguments
1117 --
1118 p_object_version_number := l_object_version_number;
1119 --
1120 hr_utility.set_location(' Leaving:'||l_proc, 999);
1121 --
1122 EXCEPTION
1123 --
1124 WHEN hr_api.validate_enabled THEN
1125 --
1126 -- As the Validate_Enabled exception has been raised
1127 -- we must rollback to the savepoint
1128 --
1129 ROLLBACK TO update_assignment_rate;
1130 --
1131 -- Only set output warning arguments
1132 -- (Any key or derived arguments must be set to null
1133 -- when validation only mode is being used.)
1134 --
1135 hr_utility.set_location(' Leaving:'||l_proc, 998);
1136 --
1137 WHEN others THEN
1138 --
1139 -- A validation or unexpected error has occured
1140 --
1141 ROLLBACK TO update_assignment_rate;
1142 --
1143 -- Bugfix 2692195
1144 -- Reset all OUT/IN OUT parameters
1145 --
1146 p_object_version_number := l_object_version_number;
1147 --
1148 RAISE;
1149 --
1150 END update_assignment_rate;
1151 --
1152 END hr_rate_api;