[Home] [Help]
PACKAGE BODY: APPS.HR_PAY_SCALE_VALUE_API
Source
4 cursor csr_get_rate_type
1 Package Body hr_pay_scale_value_api as
2 /* $Header: pypsrapi.pkb 120.3 2010/03/17 07:46:21 sidsaxen ship $ */
3 --
5 (p_grade_rule_id number
6 ,p_effective_date date
7 ) is
8 select grr.rate_type
9 from pay_grade_rules_f grr
10 where grr.grade_rule_id = p_grade_rule_id
11 and p_effective_date between grr.effective_start_date
12 and grr.effective_end_date;
13 --
14 -- Package Variables
15 --
16 g_package varchar2(33) := ' hr_pay_scale_value_api.';
17 --
18 procedure check_rate_type
19 (p_grade_rule_id in number
20 ,p_effective_date in date
21 ) is
22 --
23 -- Declare cursors and local variables
24 --
25 l_rate_type pay_grade_rules_f.rate_type%TYPE;
26 l_proc varchar2(72) := g_package||'check_rate_type';
27 --
28 cursor csr_get_rate_type is
29 select grr.rate_type
30 from pay_grade_rules_f grr
31 where grr.grade_rule_id = p_grade_rule_id
32 and p_effective_date between grr.effective_start_date
33 and grr.effective_end_date;
34 --
35 begin
36 hr_utility.set_location('Entering:'|| l_proc, 5);
37 --
38 -- Check that the Grade Rule identified is for a Grade Rate.
39 --
40 open csr_get_rate_type;
41 fetch csr_get_rate_type
42 into l_rate_type;
43 if csr_get_rate_type%notfound then
44 --
45 close csr_get_rate_type;
46 --
47 hr_utility.set_location(l_proc, 7);
48 --
49 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
50 hr_utility.raise_error;
51 --
52 else
53 --
54 close csr_get_rate_type;
55 --
56 if l_rate_type <> 'SP' then
57 --
58 hr_utility.set_location(l_proc, 8);
59 --
60 hr_utility.set_message(801, 'HR_7855_GRR_INV_NOT_SP_RATE_TY');
61 hr_utility.raise_error;
62 --
63 end if;
64 end if;
65 --
66 hr_utility.set_location(' Leaving:'||l_proc, 11);
67 end;
68 --
69 -- ----------------------------------------------------------------------------
70 -- |-------------------------< create_pay_scale_value >-----------------------|
71 -- ----------------------------------------------------------------------------
72 --
73 procedure create_pay_scale_value
74 (p_validate in boolean default false
75 ,p_effective_date in date
76 ,p_rate_id in number
77 ,p_currency_code in varchar2
78 ,p_spinal_point_id in number
79 ,p_value in varchar2 default null
80 ,p_grade_rule_id out nocopy number
81 ,p_object_version_number out nocopy number
82 ,p_effective_start_date out nocopy date
83 ,p_effective_end_date out nocopy date
84 ) is
85 --
86 -- Declare cursors and local variables
87 --
88 l_proc varchar2(72) := g_package||'create_pay_scale_value';
89 l_business_group_id pay_grade_rules_f.business_group_id%TYPE;
90 l_sequence pay_grade_rules_f.sequence%TYPE;
91 l_effective_date date;
92 l_grade_rule_id number;
93 l_object_version_number number;
94 l_effective_start_date date;
95 l_effective_end_date date;
96 --
97 cursor csr_get_der_args is
98 select spo.business_group_id,
99 spo.sequence
100 from per_spinal_points spo
104 --
101 where spo.spinal_point_id = p_spinal_point_id;
102 --
103 begin
105 -- Set l_effective_date equal to truncated version of p_effective_date for
106 -- API work. Stops dates being passed to row handlers with time portion.
107 --
108 l_effective_date := trunc(p_effective_date);
109 hr_utility.set_location('Entering:'|| l_proc, 5);
110 --
111 -- Issue a savepoint.
112 --
113 savepoint create_pay_scale_value;
114 hr_utility.set_location(l_proc, 6);
115 --
116 -- Check that p_spinal_point_id is not null as it is used in the cursor.
117 --
118 hr_api.mandatory_arg_error
119 (p_api_name => l_proc,
120 p_argument => 'spinal_point_id',
121 p_argument_value => p_spinal_point_id);
122 --
123 -- Get business_group_id using person_id.
124 --
125 open csr_get_der_args;
126 fetch csr_get_der_args
127 into l_business_group_id,
128 l_sequence;
129 --
130 if csr_get_der_args%notfound then
131 close csr_get_der_args;
132 hr_utility.set_message(801, 'HR_7312_GRR_INVALID_SPNL_POINT');
133 hr_utility.raise_error;
134 end if;
135 --
136 close csr_get_der_args;
137 --
138 --
139 --
140 hr_utility.set_location(l_proc, 7);
141 --
142 -- Insert Progression Point Value.
143 --
144 hr_rate_values_api.create_rate_value(
145 p_validate => FALSE
146 ,p_effective_date => l_effective_date
147 ,p_business_group_id => l_business_group_id
148 ,p_rate_id => p_rate_id
149 ,p_grade_or_spinal_point_id => p_spinal_point_id
150 ,p_rate_type => 'SP'
151 ,p_currency_code => p_currency_code
152 ,p_value => p_value
153 ,p_grade_rule_id => l_grade_rule_id
154 ,p_object_version_number => l_object_version_number
155 ,p_effective_start_date => l_effective_start_date
156 ,p_effective_end_date => l_effective_end_date
157 );
158 --
159 hr_utility.set_location(l_proc, 8);
160 --
161 -- When in validation only mode raise the Validate_Enabled exception
162 --
163 if p_validate then
164 raise hr_api.validate_enabled;
165 end if;
166 --
167 -- Set out parms.
168 --
169 p_grade_rule_id := l_grade_rule_id;
170 p_object_version_number := l_object_version_number;
171 p_effective_start_date := l_effective_start_date;
172 p_effective_end_date := l_effective_end_date;
173 --
174 hr_utility.set_location(' Leaving:'||l_proc, 11);
175 exception
176 when hr_api.validate_enabled then
177 --
178 -- As the Validate_Enabled exception has been raised
179 -- we must rollback to the savepoint
180 --
181 ROLLBACK TO create_pay_scale_value;
182 --
183 -- Only set output warning arguments
184 -- (Any key or derived arguments must be set to null
185 -- when validation only mode is being used.)
186 --
187 p_grade_rule_id := null;
188 p_effective_start_date := null;
189 p_effective_end_date := null;
190 p_object_version_number := null;
191 --
192 when others then
193 --
194 -- A validation or unexpected error has occurred
195 --
196 -- Added as part of fix to bug 632479
197 --
198 ROLLBACK TO create_pay_scale_value;
199 --
200 -- Bugfix 2692195
201 -- Reset all OUT/IN OUT parameters
202 --
203 p_grade_rule_id := null;
204 p_object_version_number := null;
205 p_effective_start_date := null;
206 p_effective_end_date := null;
207 --
208 raise;
209 --
210 -- End of fix.
211 --
212 end create_pay_scale_value;
213
214 -- Start changes for bug 9328526
215 --
216 -- ----------------------------------------------------------------------------
217 -- |-------------------------< update_pay_scale_value >-----------------------|
218 -- ----------------------------------------------------------------------------
219 --
220 procedure update_pay_scale_value
221 (p_validate in boolean default false
222 ,p_effective_date in date
223 ,p_datetrack_update_mode in varchar2
224 ,p_grade_rule_id in number
225 ,p_object_version_number in out nocopy number
226 ,p_currency_code in varchar2
227 ,p_maximum in varchar2 default null
228 ,p_mid_value in varchar2 default null
229 ,p_minimum in varchar2 default null
230 ,p_value in varchar2 default null
231 ,p_effective_start_date out nocopy date
232 ,p_effective_end_date out nocopy date
233 ) is
234
235 l_upd_warning boolean;
236
237 begin
238 --
239 update_pay_scale_value
240 (p_validate => p_validate
241 ,p_effective_date => p_effective_date
242 ,p_datetrack_update_mode => p_datetrack_update_mode
243 ,p_grade_rule_id => p_grade_rule_id
244 ,p_object_version_number => p_object_version_number
245 ,p_currency_code => p_currency_code
246 ,p_maximum => p_maximum
247 ,p_mid_value => p_mid_value
248 ,p_minimum => p_minimum
249 ,p_value => p_value
250 ,p_effective_start_date => p_effective_start_date
251 ,p_effective_end_date => p_effective_end_date
252 ,p_called_from => 'API'
253 );
254 --
255 end update_pay_scale_value;
256 -- End changes for bug 9328526
257 --
258 -- ----------------------------------------------------------------------------
259 -- |-------------------------< update_pay_scale_value >-----------------------|
260 -- ----------------------------------------------------------------------------
261 --
262 procedure update_pay_scale_value
263 (p_validate in boolean default false
264 ,p_effective_date in date
265 ,p_datetrack_update_mode in varchar2
266 ,p_grade_rule_id in number
267 ,p_object_version_number in out nocopy number
268 ,p_currency_code in varchar2
269 ,p_maximum in varchar2 default null
270 ,p_mid_value in varchar2 default null
271 ,p_minimum in varchar2 default null
272 ,p_value in varchar2 default null
273 ,p_effective_start_date out nocopy date
274 ,p_effective_end_date out nocopy date
275 ,p_called_from in varchar2 --added for bug 9328526
276 ) is
277 --
278 -- Declare cursors and local variables
279 --
280 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
281 l_object_version_number_temp pay_grade_rules_f.object_version_number%TYPE;
282 l_proc varchar2(72) := g_package||'update_pay_scale_value';
283 l_effective_date date;
284 l_effective_start_date date;
285 l_effective_end_date date;
286
287 -- Start changes for bug 9328526
288 l_rate_id pay_rates.rate_id%type;
289
290 cursor c_get_rate_id is
291 select rate_id
292 from pay_grade_rules_f
293 where grade_rule_id = p_grade_rule_id;
294 -- End changes for bug 9328526
295
296 --
297 begin
298 --
299 -- Set l_effective_date equal to truncated version of p_effective_date for
300 -- API work. Stops dates being passed to row handlers with time portion.
301 --
302 l_effective_date := trunc(p_effective_date);
303 hr_utility.set_location('Entering:'|| l_proc, 5);
304 --
305 -- Issue a savepoint.
306 --
307 savepoint update_pay_scale_value;
308 --
309 hr_utility.set_location(l_proc, 6);
310 --
311 -- Check that the Grade Rule identified is for a Pay Scale.
312 --
313 --
314 check_rate_type
315 (p_grade_rule_id => p_grade_rule_id
316 ,p_effective_date => l_effective_date
317 );
318 --
319
320 -- Start changes for bug 9328526
321 hr_utility.set_location(l_proc, 7);
322
323 open c_get_rate_id;
324 fetch c_get_rate_id into l_rate_id;
325 if c_get_rate_id%notfound then
326 close c_get_rate_id;
327 hr_utility.set_message(800, 'HR_289683_INVALID_ASG_RATE');
328 hr_utility.raise_error;
329 else
330 close c_get_rate_id;
331 end if;
332
333 hr_utility.set_location(l_proc, 8);
334
335 if nvl(p_called_from,'API') <> 'GSPW' then
336 pqh_gsp_utility.chk_rate_for_upd_del(
337 p_rate_id => l_rate_id
338 ,p_entity => 'RATE_VALUE');
339 end if;
340
341 -- End changes for bug 9328526
342
343 hr_utility.set_location(l_proc, 9);
344 --
345 --
346 l_object_version_number_temp := p_object_version_number;
347 l_object_version_number := p_object_version_number;
348 --
349 -- Update Grade Rule details.
350 hr_rate_values_api.update_rate_value(
351 p_validate => FALSE
352 ,p_grade_rule_id => p_grade_rule_id
353 ,p_effective_date => l_effective_date
354 ,p_datetrack_mode => p_datetrack_update_mode
355 ,p_currency_code => p_currency_code
356 ,p_maximum => p_maximum
357 ,p_mid_value => p_mid_value
358 ,p_minimum => p_minimum
359 ,p_value => p_value
360 ,p_object_version_number => l_object_version_number
361 ,p_effective_start_date => l_effective_start_date
362 ,p_effective_end_date => l_effective_end_date
366 hr_utility.set_location(l_proc, 10);
363 );
364 --
365 --
367 --
368 -- When in validation only mode raise the Validate_Enabled exception
369 --
370 if p_validate then
371 raise hr_api.validate_enabled;
372 end if;
373 --
374 -- Set out parms
375 --
376 p_object_version_number := l_object_version_number;
377 p_effective_start_date := l_effective_start_date;
378 p_effective_end_date := l_effective_end_date;
379 --
380 hr_utility.set_location(' Leaving:'||l_proc, 11);
381 exception
382 when hr_api.validate_enabled then
383 --
384 -- As the Validate_Enabled exception has been raised
385 -- we must rollback to the savepoint
386 --
387 ROLLBACK TO update_pay_scale_value;
388 --
389 -- Only set output warning arguments
390 -- (Any key or derived arguments must be set to null
391 -- when validation only mode is being used.)
392 --
393 p_effective_start_date := null;
394 p_effective_end_date := null;
395 p_object_version_number := l_object_version_number_temp;
396 --
397 when others then
398 --
399 -- A validation or unexpected error has occurred
400 --
401 -- Added as part of fix to bug 632479
402 --
403 ROLLBACK TO update_pay_scale_value;
404 --
405 -- Bugfix 2692195
406 -- Reset all OUT/IN OUT parameters
407 --
408 p_object_version_number := l_object_version_number;
409 p_effective_start_date := null;
410 p_effective_end_date := null;
411 --
412 raise;
413 --
414 -- End of fix.
415 --
416 end update_pay_scale_value;
417
418 -- Start changes for bug 9328526
419 --
420 -- ----------------------------------------------------------------------------
421 -- |-------------------------< delete_pay_scale_value >-----------------------|
422 -- ----------------------------------------------------------------------------
423 --
424 procedure delete_pay_scale_value
425 (p_validate in boolean default false
426 ,p_effective_date in date
427 ,p_datetrack_delete_mode in varchar2
428 ,p_grade_rule_id in number
429 ,p_object_version_number in out nocopy number
430 ,p_effective_start_date out nocopy date
431 ,p_effective_end_date out nocopy date
432 ) is
433
434 l_del_warning boolean;
435
436 begin
437 --
438 delete_pay_scale_value
439 (p_validate => p_validate
440 ,p_effective_date => p_effective_date
441 ,p_datetrack_delete_mode => p_datetrack_delete_mode
442 ,p_grade_rule_id => p_grade_rule_id
443 ,p_object_version_number => p_object_version_number
444 ,p_effective_start_date => p_effective_start_date
445 ,p_effective_end_date => p_effective_end_date
446 ,p_called_from => 'API'
447 );
448 --
449 end;
450 -- End changes for 9328526
451
452 --
453 -- ----------------------------------------------------------------------------
454 -- |-------------------------< delete_pay_scale_value >-----------------------|
455 -- ----------------------------------------------------------------------------
456 --
457 procedure delete_pay_scale_value
458 (p_validate in boolean default false
459 ,p_effective_date in date
460 ,p_datetrack_delete_mode in varchar2
461 ,p_grade_rule_id in number
462 ,p_object_version_number in out nocopy number
463 ,p_effective_start_date out nocopy date
464 ,p_effective_end_date out nocopy date
465 ,p_called_from in varchar2 -- added for bug 9328526
466 ) is
467 --
468 -- Declare cursors and local variables
469 --
470 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
471 l_object_version_number_temp pay_grade_rules_f.object_version_number%TYPE;
472 l_proc varchar2(72) := g_package||'delete_pay_scale_value';
473 l_effective_date date;
474 l_effective_start_date date;
475 l_effective_end_date date;
476
477 -- Start changes for bug 9328526
478 l_rate_id pay_rates.rate_id%type;
479
480 cursor c_get_rate_id is
481 select rate_id
482 from pay_grade_rules_f
483 where grade_rule_id = p_grade_rule_id;
484 -- End changes for bug 9328526
485
486 --
487 begin
488 --
489 -- Set l_effective_date equal to truncated version of p_effective_date for
490 -- API work. Stops dates being passed to row handlers with time portion.
491 --
492 l_effective_date := trunc(p_effective_date);
493 hr_utility.set_location('Entering:'|| l_proc, 5);
494 --
495 -- Issue a savepoint.
496 --
497 savepoint delete_pay_scale_value;
498 --
499 hr_utility.set_location(l_proc, 6);
500 --
501 -- Check that the Grade Rule identified is for a Pay Scale.
502 --
503 --
504 check_rate_type
505 (p_grade_rule_id => p_grade_rule_id
506 ,p_effective_date => l_effective_date
507 );
508 --
509
510 -- Start changes for bug 9328526
511 hr_utility.set_location(l_proc, 7);
512
513 open c_get_rate_id;
514 fetch c_get_rate_id into l_rate_id;
515 if c_get_rate_id%notfound then
516 close c_get_rate_id;
517 hr_utility.set_message(800, 'HR_289683_INVALID_ASG_RATE');
518 hr_utility.raise_error;
519 else
520 close c_get_rate_id;
521 end if;
522
523 hr_utility.set_location(l_proc, 8);
524
525 if nvl(p_called_from,'API') <> 'GSPW' then
526 pqh_gsp_utility.chk_rate_for_upd_del(
527 p_rate_id => l_rate_id
528 ,p_entity => 'RATE_VALUE');
529 End if;
530 -- End changes for bug 9328526
531
532 hr_utility.set_location(l_proc, 9);
533
534 --
535 --
536 l_object_version_number_temp := p_object_version_number;
537 l_object_version_number := p_object_version_number;
538 --
539 -- Delete Grade Rule details.
540 hr_rate_values_api.delete_rate_value(
541 p_validate => FALSE
542 ,p_grade_rule_id => p_grade_rule_id
543 ,p_datetrack_mode => p_datetrack_delete_mode
544 ,p_effective_date => l_effective_date
545 ,p_object_version_number => l_object_version_number
546 ,p_effective_start_date => l_effective_start_date
547 ,p_effective_end_date => l_effective_end_date
548 );
549 --
550 hr_utility.set_location(l_proc, 10);
551 --
552 -- When in validation only mode raise the Validate_Enabled exception
553 --
554 if p_validate then
555 raise hr_api.validate_enabled;
556 end if;
557 --
558 p_object_version_number := l_object_version_number;
559 p_effective_start_date := l_effective_start_date;
560 p_effective_end_date := l_effective_end_date;
561 --
562 hr_utility.set_location(' Leaving:'||l_proc, 11);
563 exception
564 when hr_api.validate_enabled then
565 --
566 -- As the Validate_Enabled exception has been raised
567 -- we must rollback to the savepoint
568 --
569 ROLLBACK TO delete_pay_scale_value;
570 --
571 -- Only set output warning arguments
572 -- (Any key or derived arguments must be set to null
573 -- when validation only mode is being used.)
574 --
575 p_effective_start_date := null;
576 p_effective_end_date := null;
577 p_object_version_number := l_object_version_number_temp;
578 --
579 when others then
580 --
581 -- A validation or unexpected error has occurred
582 --
583 -- Added as part of fix to bug 632479
584 --
585 ROLLBACK TO delete_pay_scale_value;
586 --
587 -- Bugfix 2692195
588 -- Reset all OUT/IN OUT parameters
589 --
590 p_object_version_number := l_object_version_number;
591 p_effective_start_date := null;
592 p_effective_end_date := null;
593 --
594 raise;
595 --
596 -- End of fix.
597 --
598 end delete_pay_scale_value;
599 --
600 end hr_pay_scale_value_api;