[Home] [Help]
PACKAGE BODY: APPS.HR_PAY_SCALE_VALUE_API
Source
1 Package Body hr_pay_scale_value_api as
2 /* $Header: pypsrapi.pkb 115.6 2003/06/24 19:35:36 ynegoro ship $ */
3 --
4 cursor csr_get_rate_type
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
101 where spo.spinal_point_id = p_spinal_point_id;
102 --
103 begin
104 --
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 -- ----------------------------------------------------------------------------
215 -- |-------------------------< update_pay_scale_value >-----------------------|
216 -- ----------------------------------------------------------------------------
217 --
218 procedure update_pay_scale_value
219 (p_validate in boolean default false
220 ,p_effective_date in date
221 ,p_datetrack_update_mode in varchar2
222 ,p_grade_rule_id in number
223 ,p_object_version_number in out nocopy number
224 ,p_currency_code in varchar2
225 ,p_maximum in varchar2 default null
226 ,p_mid_value in varchar2 default null
227 ,p_minimum in varchar2 default null
228 ,p_value in varchar2 default null
229 ,p_effective_start_date out nocopy date
230 ,p_effective_end_date out nocopy date
231 ) is
232 --
233 -- Declare cursors and local variables
234 --
235 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
236 l_object_version_number_temp pay_grade_rules_f.object_version_number%TYPE;
237 l_proc varchar2(72) := g_package||'update_pay_scale_value';
238 l_effective_date date;
239 l_effective_start_date date;
240 l_effective_end_date date;
241 --
242 begin
243 --
244 -- Set l_effective_date equal to truncated version of p_effective_date for
245 -- API work. Stops dates being passed to row handlers with time portion.
246 --
247 l_effective_date := trunc(p_effective_date);
248 hr_utility.set_location('Entering:'|| l_proc, 5);
249 --
250 -- Issue a savepoint.
251 --
252 savepoint update_pay_scale_value;
253 --
254 hr_utility.set_location(l_proc, 6);
255 --
256 -- Check that the Grade Rule identified is for a Pay Scale.
257 --
258 --
259 check_rate_type
260 (p_grade_rule_id => p_grade_rule_id
261 ,p_effective_date => l_effective_date
262 );
263 --
264 hr_utility.set_location(l_proc, 9);
265 --
266 --
267 l_object_version_number_temp := p_object_version_number;
268 l_object_version_number := p_object_version_number;
269 --
270 -- Update Grade Rule details.
271 hr_rate_values_api.update_rate_value(
272 p_validate => FALSE
273 ,p_grade_rule_id => p_grade_rule_id
274 ,p_effective_date => l_effective_date
275 ,p_datetrack_mode => p_datetrack_update_mode
276 ,p_currency_code => p_currency_code
277 ,p_maximum => p_maximum
278 ,p_mid_value => p_mid_value
279 ,p_minimum => p_minimum
280 ,p_value => p_value
281 ,p_object_version_number => l_object_version_number
282 ,p_effective_start_date => l_effective_start_date
283 ,p_effective_end_date => l_effective_end_date
284 );
285 --
286 --
287 hr_utility.set_location(l_proc, 10);
288 --
289 -- When in validation only mode raise the Validate_Enabled exception
290 --
291 if p_validate then
292 raise hr_api.validate_enabled;
293 end if;
294 --
295 -- Set out parms
296 --
297 p_object_version_number := l_object_version_number;
298 p_effective_start_date := l_effective_start_date;
299 p_effective_end_date := l_effective_end_date;
300 --
301 hr_utility.set_location(' Leaving:'||l_proc, 11);
302 exception
303 when hr_api.validate_enabled then
304 --
305 -- As the Validate_Enabled exception has been raised
306 -- we must rollback to the savepoint
307 --
308 ROLLBACK TO update_pay_scale_value;
309 --
310 -- Only set output warning arguments
311 -- (Any key or derived arguments must be set to null
312 -- when validation only mode is being used.)
313 --
314 p_effective_start_date := null;
315 p_effective_end_date := null;
316 p_object_version_number := l_object_version_number_temp;
317 --
318 when others then
319 --
320 -- A validation or unexpected error has occurred
321 --
322 -- Added as part of fix to bug 632479
323 --
324 ROLLBACK TO update_pay_scale_value;
325 --
326 -- Bugfix 2692195
330 p_effective_start_date := null;
327 -- Reset all OUT/IN OUT parameters
328 --
329 p_object_version_number := l_object_version_number;
331 p_effective_end_date := null;
332 --
333 raise;
334 --
335 -- End of fix.
336 --
337 end update_pay_scale_value;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |-------------------------< delete_pay_scale_value >-----------------------|
341 -- ----------------------------------------------------------------------------
342 --
343 procedure delete_pay_scale_value
344 (p_validate in boolean default false
345 ,p_effective_date in date
346 ,p_datetrack_delete_mode in varchar2
347 ,p_grade_rule_id in number
348 ,p_object_version_number in out nocopy number
349 ,p_effective_start_date out nocopy date
350 ,p_effective_end_date out nocopy date
351 ) is
352 --
353 -- Declare cursors and local variables
354 --
355 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
356 l_object_version_number_temp pay_grade_rules_f.object_version_number%TYPE;
357 l_proc varchar2(72) := g_package||'delete_pay_scale_value';
358 l_effective_date date;
359 l_effective_start_date date;
360 l_effective_end_date date;
361 --
362 begin
363 --
364 -- Set l_effective_date equal to truncated version of p_effective_date for
365 -- API work. Stops dates being passed to row handlers with time portion.
366 --
367 l_effective_date := trunc(p_effective_date);
368 hr_utility.set_location('Entering:'|| l_proc, 5);
369 --
370 -- Issue a savepoint.
371 --
372 savepoint delete_pay_scale_value;
373 --
374 hr_utility.set_location(l_proc, 6);
375 --
376 -- Check that the Grade Rule identified is for a Pay Scale.
377 --
378 --
379 check_rate_type
380 (p_grade_rule_id => p_grade_rule_id
381 ,p_effective_date => l_effective_date
382 );
383 --
384 hr_utility.set_location(l_proc, 9);
385 --
386 --
387 l_object_version_number_temp := p_object_version_number;
388 l_object_version_number := p_object_version_number;
389 --
390 -- Delete Grade Rule details.
391 hr_rate_values_api.delete_rate_value(
392 p_validate => FALSE
393 ,p_grade_rule_id => p_grade_rule_id
394 ,p_datetrack_mode => p_datetrack_delete_mode
395 ,p_effective_date => l_effective_date
396 ,p_object_version_number => l_object_version_number
397 ,p_effective_start_date => l_effective_start_date
398 ,p_effective_end_date => l_effective_end_date
399 );
400 --
401 hr_utility.set_location(l_proc, 10);
402 --
403 -- When in validation only mode raise the Validate_Enabled exception
404 --
405 if p_validate then
406 raise hr_api.validate_enabled;
407 end if;
408 --
409 p_object_version_number := l_object_version_number;
410 p_effective_start_date := l_effective_start_date;
411 p_effective_end_date := l_effective_end_date;
412 --
413 hr_utility.set_location(' Leaving:'||l_proc, 11);
414 exception
415 when hr_api.validate_enabled then
416 --
417 -- As the Validate_Enabled exception has been raised
418 -- we must rollback to the savepoint
419 --
420 ROLLBACK TO delete_pay_scale_value;
421 --
422 -- Only set output warning arguments
423 -- (Any key or derived arguments must be set to null
424 -- when validation only mode is being used.)
425 --
426 p_effective_start_date := null;
427 p_effective_end_date := null;
428 p_object_version_number := l_object_version_number_temp;
429 --
430 when others then
431 --
432 -- A validation or unexpected error has occurred
433 --
434 -- Added as part of fix to bug 632479
435 --
436 ROLLBACK TO delete_pay_scale_value;
437 --
438 -- Bugfix 2692195
439 -- Reset all OUT/IN OUT parameters
440 --
441 p_object_version_number := l_object_version_number;
442 p_effective_start_date := null;
443 p_effective_end_date := null;
444 --
445 raise;
446 --
447 -- End of fix.
448 --
449 end delete_pay_scale_value;
450 --
451 end hr_pay_scale_value_api;