[Home] [Help]
PACKAGE BODY: APPS.HR_GRADE_RATE_VALUE_API
Source
1 Package Body hr_grade_rate_value_api as
2 /* $Header: pygrrapi.pkb 120.0 2005/05/29 05:34:11 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_grade_rate_value_api.';
7 --
8 procedure check_rate_type
9 (p_grade_rule_id in number
10 ,p_effective_date in date
11 ) is
12 --
13 -- Declare cursors and local variables
14 --
15 l_rate_type pay_grade_rules_f.rate_type%TYPE;
16 l_proc varchar2(72) := g_package||'check_rate_type';
17 --
18 cursor csr_get_rate_type is
19 select grr.rate_type
20 from pay_grade_rules_f grr
21 where grr.grade_rule_id = p_grade_rule_id
22 and p_effective_date between grr.effective_start_date
23 and grr.effective_end_date;
24 --
25 begin
26 hr_utility.set_location('Entering:'|| l_proc, 5);
27 --
28 -- Check that the Grade Rule identified is for a Grade Rate.
29 --
30 open csr_get_rate_type;
31 fetch csr_get_rate_type
32 into l_rate_type;
33 if csr_get_rate_type%notfound then
34 --
35 close csr_get_rate_type;
36 --
37 hr_utility.set_location(l_proc, 7);
38 --
39 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
40 hr_utility.raise_error;
41 --
42 else
43 --
44 close csr_get_rate_type;
45 --
46 if l_rate_type <> 'G' then
47 --
48 hr_utility.set_location(l_proc, 8);
49 --
50 hr_utility.set_message(801, 'HR_7854_GRR_INV_NOT_G_RATE_TYP');
51 hr_utility.raise_error;
52 --
53 end if;
54 end if;
55 --
56 hr_utility.set_location(' Leaving:'||l_proc, 11);
57 end;
58 --
59 -- ----------------------------------------------------------------------------
60 -- |------------------------< create_grade_rate_value >----------------------|
61 -- ----------------------------------------------------------------------------
62 --
63 procedure create_grade_rate_value
64 (p_validate in boolean default false
65 ,p_effective_date in date
66 ,p_rate_id in number
67 ,p_grade_id in number
68 ,p_currency_code in varchar2 default null
69 ,p_maximum in varchar2 default null
70 ,p_mid_value in varchar2 default null
71 ,p_minimum in varchar2 default null
72 ,p_value in varchar2 default null
73 ,p_sequence in number default null
74 ,p_grade_rule_id out nocopy number
75 ,p_object_version_number out nocopy number
76 ,p_effective_start_date out nocopy date
77 ,p_effective_end_date out nocopy date
78 ) is
79 --
80 -- Declare cursors and local variables
81 --
82 l_proc varchar2(72) := g_package||'create_grade_rate_value';
83 l_business_group_id pay_grade_rules_f.business_group_id%TYPE;
84 l_effective_start_date pay_grade_rules_f.effective_start_date%TYPE;
85 l_effective_end_date pay_grade_rules_f.effective_end_date%TYPE;
86 l_sequence pay_grade_rules_f.sequence%TYPE;
87 l_effective_date date;
88 l_grade_rule_id number;
89 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
90 --
91 cursor csr_get_der_args is
92 select rat.business_group_id,
93 rat.sequence
94 from per_grades rat
95 where rat.grade_id = p_grade_id;
96 --
97 begin
98 --
99 -- Set l_effective_date equal to truncated version of p_effective date for
100 -- API work. Stops dates being passed to row handlers with a time portion.
101 --
102 l_effective_date := trunc(p_effective_date);
103 hr_utility.set_location('Entering:'|| l_proc, 5);
104 --
105 -- Issue a savepoint.
106 --
107 savepoint create_grade_rate_value;
108 hr_utility.set_location(l_proc, 6);
109 --
110 -- Check that p_grade_id is not null as it is used in the cursor.
111 --
112 hr_api.mandatory_arg_error
113 (p_api_name => l_proc,
114 p_argument => 'grade_id',
115 p_argument_value => p_grade_id);
116 --
117 -- Get business_group_id using person_id.
118 --
119 open csr_get_der_args;
120 fetch csr_get_der_args
121 into l_business_group_id,
122 l_sequence;
123 --
124 if csr_get_der_args%notfound then
125 close csr_get_der_args;
126 hr_utility.set_message(801, 'HR_7311_GRR_INVALID_GRADE');
127 hr_utility.raise_error;
128 end if;
129 --
130 close csr_get_der_args;
131 --
132 hr_utility.set_location(l_proc, 7);
133 --
134 -- Insert Grade Rule details.
135 hr_rate_values_api.create_rate_value(
136 p_validate => FALSE
137 ,p_effective_date => l_effective_date
138 ,p_business_group_id => l_business_group_id
139 ,p_rate_id => p_rate_id
140 ,p_grade_or_spinal_point_id => p_grade_id
141 ,p_rate_type => 'G'
142 ,p_currency_code => p_currency_code
143 ,p_maximum => p_maximum
144 ,p_mid_value => p_mid_value
145 ,p_minimum => p_minimum
146 ,p_value => p_value
147 ,p_sequence => p_sequence
148 ,p_grade_rule_id => l_grade_rule_id
149 ,p_object_version_number => l_object_version_number
150 ,p_effective_start_date => l_effective_start_date
151 ,p_effective_end_date => l_effective_end_date
152 );
153 --
154 hr_utility.set_location(l_proc, 8);
155 --
156 -- When in validation only mode raise the Validate_Enabled exception
157 --
158 if p_validate then
159 raise hr_api.validate_enabled;
160 end if;
161 --
162 -- Set out parms.
163 --
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(' Leaving:'||l_proc, 11);
170 exception
171 when hr_api.validate_enabled then
172 --
173 -- As the Validate_Enabled exception has been raised
174 -- we must rollback to the savepoint
175 --
176 ROLLBACK TO create_grade_rate_value;
177 --
178 -- Only set output warning arguments
179 -- (Any key or derived arguments must be set to null
180 -- when validation only mode is being used.)
181 --
182 p_grade_rule_id := null;
183 p_effective_start_date := null;
184 p_effective_end_date := null;
185 p_object_version_number := null;
186 --
187 when others then
188 --
189 -- A validation or unexpected error has occurred
190 --
191 -- Added as part of fix to bug 632479
192 --
193 ROLLBACK TO create_grade_rate_value;
194 --
195 -- set in out parameters and set out parameters
196 --
197 p_grade_rule_id := null;
198 p_effective_start_date := null;
199 p_effective_end_date := null;
200 p_object_version_number := null;
201 raise;
202 --
203 -- End of fix.
204 --
205 end create_grade_rate_value;
206 --
207 -- ----------------------------------------------------------------------------
208 -- |------------------------< update_grade_rate_value >----------------------|
209 -- ----------------------------------------------------------------------------
210 --
211 procedure update_grade_rate_value
212 (p_validate in boolean default false
213 ,p_effective_date in date
214 ,p_datetrack_update_mode in varchar2
215 ,p_grade_rule_id in number
216 ,p_currency_code in varchar2 default hr_api.g_varchar2
217 ,p_object_version_number in out nocopy number
218 ,p_maximum in varchar2 default hr_api.g_varchar2
219 ,p_mid_value in varchar2 default hr_api.g_varchar2
220 ,p_minimum in varchar2 default hr_api.g_varchar2
221 ,p_value in varchar2 default hr_api.g_varchar2
222 ,p_sequence in number default hr_api.g_number
223 ,p_effective_start_date out nocopy date
224 ,p_effective_end_date out nocopy date
225 ) is
226 --
227 -- Declare cursors and local variables
228 --
229 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
230 l_object_version_number_temp pay_grade_rules_f.object_version_number%TYPE;
231 l_proc varchar2(72) := g_package||'update_grade_rate_value';
232 l_effective_date date;
233 l_effective_start_date date;
234 l_effective_end_date date;
235 --
236 begin
237 --
238 -- Set l_effective_date equal to truncated version of p_effective_date for
239 -- API work. Stops dates being passed to row handlers with time portion.
240 --
241 l_effective_date := trunc(p_effective_date);
242 hr_utility.set_location('Entering:'|| l_proc, 5);
243 --
244 -- Issue a savepoint.
245 --
246 savepoint update_grade_rate_value;
247 --
248 hr_utility.set_location(l_proc, 6);
249 --
250 check_rate_type
251 (p_grade_rule_id => p_grade_rule_id
252 ,p_effective_date => l_effective_date
253 );
254 --
255 hr_utility.set_location(l_proc, 9);
256 --
257 --
258 l_object_version_number_temp := p_object_version_number;
259 l_object_version_number := p_object_version_number;
260 --
261 -- Update Grade Rule details.
262 hr_rate_values_api.update_rate_value(
263 p_validate => FALSE
264 ,p_grade_rule_id => p_grade_rule_id
265 ,p_effective_date => l_effective_date
266 ,p_datetrack_mode => p_datetrack_update_mode
267 ,p_currency_code => p_currency_code
268 ,p_maximum => p_maximum
269 ,p_mid_value => p_mid_value
270 ,p_minimum => p_minimum
271 ,p_value => p_value
272 ,p_sequence => p_sequence
273 ,p_object_version_number => l_object_version_number
274 ,p_effective_start_date => l_effective_start_date
275 ,p_effective_end_date => l_effective_end_date
276 );
277 --
278 hr_utility.set_location(l_proc, 8);
279 --
280 -- When in validation only mode raise the Validate_Enabled exception
281 --
282 if p_validate then
283 raise hr_api.validate_enabled;
284 end if;
285 --
286 -- Set all output arguments.
287 --
288 p_object_version_number := l_object_version_number;
289 p_effective_start_date := l_effective_start_date;
290 p_effective_end_date := l_effective_end_date;
291 --
292 hr_utility.set_location(' Leaving:'||l_proc, 11);
293 exception
294 when hr_api.validate_enabled then
295 --
296 -- As the Validate_Enabled exception has been raised
297 -- we must rollback to the savepoint
298 --
299 ROLLBACK TO update_grade_rate_value;
300 --
301 -- Only set output warning arguments
302 -- (Any key or derived arguments must be set to null
303 -- when validation only mode is being used.)
304 --
305 p_effective_start_date := null;
306 p_effective_end_date := null;
307 p_object_version_number := l_object_version_number_temp;
308 --
309 when others then
310 --
311 -- A validation or unexpected error has occurred
312 --
313 -- Added as part of fix to bug 632479
314 --
315 ROLLBACK TO update_grade_rate_value;
316 --
317 -- set in out parameters and set out parameters
318 --
319 p_effective_start_date := null;
320 p_effective_end_date := null;
321 p_object_version_number := l_object_version_number_temp;
322
323 raise;
324 --
325 -- End of fix.
326 --
327 end update_grade_rate_value;
328 --
329 -- ----------------------------------------------------------------------------
330 -- |------------------------< delete_grade_rate_value >----------------------|
331 -- ----------------------------------------------------------------------------
332 --
333 procedure delete_grade_rate_value
334 (p_validate in boolean default false
335 ,p_effective_date in date
336 ,p_datetrack_delete_mode in varchar2
337 ,p_grade_rule_id in number
338 ,p_object_version_number in out nocopy number
339 ,p_effective_start_date out nocopy date
340 ,p_effective_end_date out nocopy date
341 ) is
342 --
343 -- Declare cursors and local variables
344 --
345 l_object_version_number pay_grade_rules_f.object_version_number%TYPE;
346 l_object_version_number_temp pay_grade_rules_f.object_version_number%TYPE;
347 l_proc varchar2(72) := g_package||'delete_grade_rate_value';
348 l_effective_date date;
349 l_effective_start_date date;
350 l_effective_end_date date;
351 --
352 begin
353 --
354 -- Set l_effective_date to truncated version of p_effective_date for API work
355 -- Stops dates being passed to row handlers with time portion.
356 --
357 l_effective_date := trunc(p_effective_date);
358 hr_utility.set_location('Entering:'|| l_proc, 5);
359 --
360 -- Issue a savepoint.
361 --
362 savepoint delete_grade_rate_value;
363 --
364 hr_utility.set_location(l_proc, 6);
365 --
366 -- Check that the Grade Rule identified is for a Grade Rate.
367 --
368 check_rate_type
369 (p_grade_rule_id => p_grade_rule_id
370 ,p_effective_date => l_effective_date
371 );
372 --
373 --
374 l_object_version_number_temp := p_object_version_number;
375 l_object_version_number := p_object_version_number;
376 --
377 -- Delete Grade Rule details.
378 --
379 hr_rate_values_api.delete_rate_value(
380 p_validate => FALSE
381 ,p_grade_rule_id => p_grade_rule_id
382 ,p_datetrack_mode => p_datetrack_delete_mode
383 ,p_effective_date => l_effective_date
384 ,p_object_version_number => l_object_version_number
385 ,p_effective_start_date => l_effective_start_date
386 ,p_effective_end_date => l_effective_end_date
387 );
388 --
389 hr_utility.set_location(l_proc, 8);
390 --
391 -- When in validation only mode raise the Validate_Enabled exception
392 --
393 if p_validate then
394 raise hr_api.validate_enabled;
395 end if;
396 --
397 --
398 -- Set out parms.
399 --
400 p_object_version_number := l_object_version_number;
401 p_effective_start_date := l_effective_start_date;
402 p_effective_end_date := l_effective_end_date;
403 --
404 hr_utility.set_location(' Leaving:'||l_proc, 11);
405 exception
406 when hr_api.validate_enabled then
407 --
408 -- As the Validate_Enabled exception has been raised
409 -- we must rollback to the savepoint
410 --
411 ROLLBACK TO delete_grade_rate_value;
412 --
413 -- Only set output warning arguments
414 -- (Any key or derived arguments must be set to null
415 -- when validation only mode is being used.)
416 --
417 p_effective_start_date := null;
418 p_effective_end_date := null;
419 p_object_version_number := l_object_version_number_temp;
420 --
421 when others then
422 --
423 -- A validation or unexpected error has occurred
424 --
425 -- Added as part of fix to bug 632479
426 --
427 ROLLBACK TO delete_grade_rate_value;
428 --
429 -- set in out parameters and set out parameters
430 --
431 p_effective_start_date := null;
432 p_effective_end_date := null;
433 p_object_version_number := l_object_version_number_temp;
434 raise;
435 --
436 -- End of fix.
437 --
438 end delete_grade_rate_value;
439 --
440 end hr_grade_rate_value_api;