1 PACKAGE BODY ghr_pc_basic_pay AS
2 /* $Header: ghbasicp.pkb 120.20.12010000.3 2008/12/03 11:59:06 utokachi ship $ */
3 --
4 --
5
6 FUNCTION get_retained_grade_details (p_person_id IN NUMBER
7 ,p_effective_date IN DATE
8 ,p_pa_request_id IN NUMBER DEFAULT NULL)
9 RETURN ghr_pay_calc.retained_grade_rec_type IS
10 --
11 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
12 l_last_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
13 --
14 l_last_pay_table_value NUMBER;
15 l_last_pay_table_value_conv NUMBER;
16 l_cur_pay_table_value NUMBER;
17 --
18 l_record_found BOOLEAN :=FALSE;
19 --
20 l_dummy_date DATE;
21
22 l_noa_code ghr_nature_of_actions.code%type;
23
24 CURSOR cur_par IS
25 SELECT first_noa_code,second_noa_code
26 FROM ghr_pa_requests
27 WHERE pa_request_id = p_pa_request_id;
28
29 CURSOR cur_temp_step IS
30 SELECT rei_information3 temp_step
31 FROM ghr_pa_request_extra_info
32 WHERE pa_request_id = p_pa_request_id
33 AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
34
35 --
36 CURSOR cur_pei IS
37 SELECT pei.person_extra_info_id
38 -- Bug#4423679 Added date_from,date_to columns.
39 ,fnd_date.canonical_to_date(pei.pei_information1) date_from
40 ,fnd_date.canonical_to_date(pei.pei_information2) date_to
41 -- Bug#4423679
42 ,pei.pei_information3 retained_grade
43 ,pei.pei_information4 retained_step_or_rate
44 ,pei.pei_information5 retained_pay_plan
45 ,pei.pei_information6 retained_user_table_id
46 ---- ,pei.pei_information7 retained_locality_percent
47 ,pei.pei_information8 retained_pay_basis
48 ,pei.pei_information9 retained_temp_step
49 FROM per_people_extra_info pei
50 WHERE pei.person_id = p_person_id
51 AND pei.information_type = 'GHR_US_RETAINED_GRADE'
52 AND p_effective_date BETWEEN NVL(fnd_date.canonical_to_date(pei.pei_information1),p_effective_date)
53 AND NVL(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date)
54 AND fnd_date.canonical_to_date(pei.pei_information1) =
55 (SELECT MIN (NVL(fnd_date.canonical_to_date(pei2.pei_information1),p_effective_date) )
56 FROM per_people_extra_info pei2
57 WHERE pei2.person_id = p_person_id
58 AND pei2.information_type = 'GHR_US_RETAINED_GRADE'
59 AND p_effective_date
60 BETWEEN NVL(fnd_date.canonical_to_date(pei2.pei_information1),p_effective_date)
61 AND NVL(fnd_date.canonical_to_date(pei2.pei_information2),p_effective_date)
62 AND pei2.person_extra_info_id NOT IN (SELECT rei_information3
63 FROM ghr_pa_request_extra_info
64 WHERE pa_request_id = p_pa_request_id
65 AND (rei_information5 is null OR rei_information5 = 'Y')
66 AND information_type in ('GHR_US_PAR_TERM_RET_GRADE',
67 'GHR_US_PAR_TERM_RG_PROMO',
68 'GHR_US_PAR_TERM_RG_POSN_CHG')
69 )
70 )
71 AND pei.person_extra_info_id NOT IN (SELECT rei_information3
72 FROM ghr_pa_request_extra_info
73 WHERE pa_request_id = p_pa_request_id
74 AND information_type in ( 'GHR_US_PAR_TERM_RET_GRADE',
75 'GHR_US_PAR_TERM_RG_PROMO',
76 'GHR_US_PAR_TERM_RG_POSN_CHG')
77 AND (rei_information5 is null OR rei_information5 = 'Y'));
78
79
80 BEGIN
81 -- Just in case there is more than one retained grade with the same earliest start
82 -- date we have to return the one with the highest plan_table_value!!
83 -- I'm sure this is very very unlikely to happen!!
84 hr_utility.set_location(' get_retained_grade_details',1);
85 FOR cur_pei_rec IN cur_pei LOOP
86 hr_utility.set_location(' get_retained_grade_details',2);
87 IF l_record_found THEN
88 -- If we have already been here once store all the last details before we get the new ones
89 -- the main record group will always keep the last highest value!
90 l_last_retained_grade_rec := l_retained_grade_rec;
91 END IF;
92 hr_utility.set_location(' get_retained_grade_details person_extra_info_id' ||l_retained_grade_rec.person_extra_info_id ,3);
93 l_retained_grade_rec.person_extra_info_id := cur_pei_rec.person_extra_info_id;
94 -- Bug#4423679 Added date_from, date_to columns in the retained grade record.
95 l_retained_grade_rec.date_from := cur_pei_rec.date_from;
96 l_retained_grade_rec.date_to := cur_pei_rec.date_to;
97 -- Bug#4423679
98 l_retained_grade_rec.grade_or_level := cur_pei_rec.retained_grade;
99 l_retained_grade_rec.step_or_rate := cur_pei_rec.retained_step_or_rate;
100 l_retained_grade_rec.pay_plan := cur_pei_rec.retained_pay_plan;
101 l_retained_grade_rec.user_table_id := cur_pei_rec.retained_user_table_id;
102 l_retained_grade_rec.pay_basis := cur_pei_rec.retained_pay_basis;
103 l_retained_grade_rec.temp_step := cur_pei_rec.retained_temp_step;
104
105 IF l_retained_grade_rec.grade_or_level IS NULL
106 OR l_retained_grade_rec.step_or_rate IS NULL
107 OR l_retained_grade_rec.pay_plan IS NULL
108 OR l_retained_grade_rec.user_table_id IS NULL
109 OR l_retained_grade_rec.pay_basis IS NULL THEN
110 hr_utility.set_message(8301, 'GHR_38255_MISSING_RETAINED_DET');
111 raise ghr_pay_calc.pay_calc_message;
112 END IF;
113 --
114 IF l_record_found THEN
115 hr_utility.set_location(' get_retained_grade_details ' ,5);
116 -- only if we have previously found a retained record with the same start date do we bother
117 -- getting the values to compare
118 ghr_pay_calc.get_pay_table_value (l_retained_grade_rec.user_table_id
119 ,l_retained_grade_rec.pay_plan
120 ,l_retained_grade_rec.grade_or_level
121 ,l_retained_grade_rec.step_or_rate
122 ,p_effective_date
123 ,l_cur_pay_table_value
124 ,l_dummy_date
125 ,l_dummy_date);
126
127 ghr_pay_calc.get_pay_table_value (l_last_retained_grade_rec.user_table_id
128 ,l_last_retained_grade_rec.pay_plan
129 ,l_last_retained_grade_rec.grade_or_level
130 ,l_last_retained_grade_rec.step_or_rate
131 ,p_effective_date
132 ,l_last_pay_table_value
133 ,l_dummy_date
134 ,l_dummy_date);
135
136 -- if they are different pay basis Convert the last retained ggrade to the pay basis
137 -- of the current
138 IF l_last_retained_grade_rec.pay_basis <> l_retained_grade_rec.pay_basis THEN
139 l_last_pay_table_value_conv := ghr_pay_calc.convert_amount
140 (l_last_pay_table_value
141 ,l_last_retained_grade_rec.pay_basis
142 ,l_retained_grade_rec.pay_basis);
143 ELSE
144 l_last_pay_table_value_conv := l_last_pay_table_value;
145 END IF;
146
147 -- now compare the two and set the l_retained_grade_rec to the one with the highest value
148 IF NVL(l_last_pay_table_value_conv,-9) > NVL(l_cur_pay_table_value,-9) THEN
149 l_retained_grade_rec := l_last_retained_grade_rec;
150 END IF;
151
152 END IF;
153 hr_utility.set_location(' get_retained_grade_details ' ,6);
154 l_record_found := TRUE;
155
156 END LOOP;
157
158 ------- Start Temp Promotion Code changes for 703 and 866 NOACs.
159 l_noa_code := null;
160 IF p_pa_request_id is not null THEN
161 FOR cur_par_rec IN cur_par LOOP
162 if cur_par_rec.first_noa_code = '002' then
163 l_noa_code := cur_par_rec.second_noa_code;
164 else
165 l_noa_code := cur_par_rec.first_noa_code;
166 end if;
167 EXIT;
168 END LOOP;
169 IF l_noa_code in ('703','866') THEN
170 l_retained_grade_rec.temp_step := null;
171 -- Bug 3221361 In case if TPS record is deleted, it shd return NULL as the value.
172 FOR cur_temp_step_rec IN cur_temp_step LOOP
173 l_retained_grade_rec.temp_step := cur_temp_step_rec.temp_step;
174 END LOOP;
175 END IF;
176 END IF;
177 IF l_noa_code = '740' THEN
178 l_retained_grade_rec.temp_step := NULL;
179 END IF;
180 -------End Temp Promotion Code changes for 703 and 866 NOACs.
181 hr_utility.set_location(' get_retained_grade_details ' ,7);
182 IF l_record_found THEN
183 hr_utility.set_location(' get_retained_grade_details ' ,7);
184 RETURN (l_retained_grade_rec);
185 ELSE
186 hr_utility.set_message(8301, 'GHR_38256_NO_RETAINED_GRADE');
187 raise ghr_pay_calc.pay_calc_message;
188 END IF;
189
190 END get_retained_grade_details ;
191 --
192 -- Bug#4016384 Created the following function to get the RG record available
193 -- before the MSL effective date.
194 FUNCTION get_expired_rg_details (p_person_id IN NUMBER
195 ,p_effective_date IN DATE
196 ,p_pa_request_id IN NUMBER DEFAULT NULL)
197 RETURN ghr_pay_calc.retained_grade_rec_type IS
198 --
199 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
200 l_last_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
201 --
202 l_last_pay_table_value NUMBER;
203 l_last_pay_table_value_conv NUMBER;
204 l_cur_pay_table_value NUMBER;
205 --
206 l_record_found BOOLEAN :=FALSE;
207 --
208 l_dummy_date DATE;
209
210 --
211 CURSOR cur_pei IS
212 SELECT pei.person_extra_info_id
213 -- Bug#4423679 Added date_from,date_to columns.
214 ,fnd_date.canonical_to_date(pei.pei_information1) date_from
215 ,fnd_date.canonical_to_date(pei.pei_information2) date_to
216 -- Bug#4423679
217 ,pei.pei_information3 retained_grade
218 ,pei.pei_information4 retained_step_or_rate
219 ,pei.pei_information5 retained_pay_plan
220 ,pei.pei_information6 retained_user_table_id
221 ---- ,pei.pei_information7 retained_locality_percent
222 ,pei.pei_information8 retained_pay_basis
223 ,pei.pei_information9 retained_temp_step
224 FROM per_people_extra_info pei
225 WHERE pei.person_id = p_person_id
226 AND pei.information_type = 'GHR_US_RETAINED_GRADE'
227 AND NVL(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date) < p_effective_date
228 AND fnd_date.canonical_to_date(pei.pei_information1) =
229 (SELECT MIN (NVL(fnd_date.canonical_to_date(pei2.pei_information1),p_effective_date) )
230 FROM per_people_extra_info pei2
231 WHERE pei2.person_id = p_person_id
232 AND pei2.information_type = 'GHR_US_RETAINED_GRADE'
233 AND NVL(fnd_date.canonical_to_date(pei2.pei_information2),p_effective_date) < p_effective_date
234 AND pei2.person_extra_info_id NOT IN (SELECT rei_information3
235 FROM ghr_pa_request_extra_info
236 WHERE pa_request_id = p_pa_request_id
237 AND (rei_information5 is null OR rei_information5 = 'Y')
238 AND information_type in ('GHR_US_PAR_TERM_RET_GRADE',
239 'GHR_US_PAR_TERM_RG_PROMO',
240 'GHR_US_PAR_TERM_RG_POSN_CHG')
241 )
242 )
243 AND pei.person_extra_info_id NOT IN (SELECT rei_information3
244 FROM ghr_pa_request_extra_info
245 WHERE pa_request_id = p_pa_request_id
246 AND information_type in ( 'GHR_US_PAR_TERM_RET_GRADE',
247 'GHR_US_PAR_TERM_RG_PROMO',
248 'GHR_US_PAR_TERM_RG_POSN_CHG')
249 AND (rei_information5 is null OR rei_information5 = 'Y'));
250
251
252 BEGIN
253 -- Just in case there is more than one retained grade with the same earliest start
254 -- date we have to return the one with the highest plan_table_value!!
255 -- I'm sure this is very very unlikely to happen!!
256 hr_utility.set_location(' get_expired_rg_details',1);
257 FOR cur_pei_rec IN cur_pei LOOP
258 hr_utility.set_location(' get_expired_rg_details',2);
259 IF l_record_found THEN
260 -- If we have already been here once store all the last details before we get the new ones
261 -- the main record group will always keep the last highest value!
262 l_last_retained_grade_rec := l_retained_grade_rec;
263 END IF;
264 hr_utility.set_location(' get_expired_rg_details person_extra_info_id' ||l_retained_grade_rec.person_extra_info_id ,3);
265 l_retained_grade_rec.person_extra_info_id := cur_pei_rec.person_extra_info_id;
266 -- Bug#4423679 Added date_from, date_to columns in the retained grade record.
267 l_retained_grade_rec.date_from := cur_pei_rec.date_from;
268 l_retained_grade_rec.date_to := cur_pei_rec.date_to;
269 -- Bug#4423679
270 l_retained_grade_rec.grade_or_level := cur_pei_rec.retained_grade;
271 l_retained_grade_rec.step_or_rate := cur_pei_rec.retained_step_or_rate;
272 l_retained_grade_rec.pay_plan := cur_pei_rec.retained_pay_plan;
273 l_retained_grade_rec.user_table_id := cur_pei_rec.retained_user_table_id;
274 l_retained_grade_rec.pay_basis := cur_pei_rec.retained_pay_basis;
275 l_retained_grade_rec.temp_step := cur_pei_rec.retained_temp_step;
276
277 IF l_retained_grade_rec.grade_or_level IS NULL
278 OR l_retained_grade_rec.step_or_rate IS NULL
279 OR l_retained_grade_rec.pay_plan IS NULL
280 OR l_retained_grade_rec.user_table_id IS NULL
281 OR l_retained_grade_rec.pay_basis IS NULL THEN
282 hr_utility.set_message(8301, 'GHR_38255_MISSING_RETAINED_DET');
283 raise ghr_pay_calc.pay_calc_message;
284 END IF;
285 --
286 IF l_record_found THEN
287 hr_utility.set_location(' get_expired_rg_details ' ,5);
288 -- only if we have previously found a retained record with the same start date do we bother
289 -- getting the values to compare
290 ghr_pay_calc.get_pay_table_value (l_retained_grade_rec.user_table_id
291 ,l_retained_grade_rec.pay_plan
292 ,l_retained_grade_rec.grade_or_level
293 ,l_retained_grade_rec.step_or_rate
294 ,p_effective_date
295 ,l_cur_pay_table_value
296 ,l_dummy_date
297 ,l_dummy_date);
298
299 ghr_pay_calc.get_pay_table_value (l_last_retained_grade_rec.user_table_id
300 ,l_last_retained_grade_rec.pay_plan
301 ,l_last_retained_grade_rec.grade_or_level
302 ,l_last_retained_grade_rec.step_or_rate
303 ,p_effective_date
304 ,l_last_pay_table_value
305 ,l_dummy_date
306 ,l_dummy_date);
307
308 -- if they are different pay basis Convert the last retained ggrade to the pay basis
309 -- of the current
310 IF l_last_retained_grade_rec.pay_basis <> l_retained_grade_rec.pay_basis THEN
311 l_last_pay_table_value_conv := ghr_pay_calc.convert_amount
312 (l_last_pay_table_value
313 ,l_last_retained_grade_rec.pay_basis
314 ,l_retained_grade_rec.pay_basis);
315 ELSE
316 l_last_pay_table_value_conv := l_last_pay_table_value;
317 END IF;
318
319 -- now compare the two and set the l_retained_grade_rec to the one with the highest value
320 IF NVL(l_last_pay_table_value_conv,-9) > NVL(l_cur_pay_table_value,-9) THEN
321 l_retained_grade_rec := l_last_retained_grade_rec;
322 END IF;
323
324 END IF;
325 hr_utility.set_location(' get_expired_rg_details ' ,6);
326 l_record_found := TRUE;
327
328 END LOOP;
329
330 IF l_record_found THEN
331 hr_utility.set_location(' get_expired_rg_details ' ,7);
332 RETURN (l_retained_grade_rec);
333 ELSE
334 hr_utility.set_message(8301, 'GHR_38256_NO_RETAINED_GRADE');
335 raise ghr_pay_calc.pay_calc_message;
336 END IF;
337
338 END get_expired_rg_details ;
339 --
340
341 PROCEDURE get_basic_pay_MAIN_per (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
342 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
343 ,p_basic_pay OUT NOCOPY NUMBER
344 ,p_PT_eff_start_date OUT NOCOPY DATE) IS
345 l_basic_pay NUMBER;
346 l_dummy_date DATE;
347 BEGIN
348 IF p_retained_grade.temp_step is not null then
349 ghr_pay_calc.get_pay_table_value(p_pay_calc_data.user_table_id
350 ,p_pay_calc_data.pay_plan
351 ,p_pay_calc_data.grade_or_level
352 ,p_retained_grade.temp_step
353 ,p_pay_calc_data.effective_date
354 ,l_basic_pay
355 ,p_PT_eff_start_date
356 ,l_dummy_date);
357 p_basic_pay := l_basic_pay;
358 ELSE
359 ghr_pay_calc.get_pay_table_value(p_retained_grade.user_table_id
360 ,p_retained_grade.pay_plan
361 ,p_retained_grade.grade_or_level
362 ,p_retained_grade.step_or_rate
363 ,p_pay_calc_data.effective_date
364 ,l_basic_pay
365 ,p_PT_eff_start_date
366 ,l_dummy_date);
367 --
368 -- need to convert to whatever the displayed value is
369 p_basic_pay := ghr_pay_calc.convert_amount(l_basic_pay
370 ,p_retained_grade.pay_basis
371 ,p_pay_calc_data.pay_basis);
372 END IF;
373
374
375 EXCEPTION
376 WHEN others THEN
377 -- Reset IN OUT parameters and set OUT parameters
378
379 p_basic_pay := NULL;
380 p_PT_eff_start_date := NULL;
381
382 RAISE;
383
384 END get_basic_pay_MAIN_per;
385 --
386 PROCEDURE get_min_pay_table_value (p_user_table_id IN NUMBER
387 ,p_pay_plan IN VARCHAR2
388 ,p_grade_or_level IN VARCHAR2
389 ,p_effective_date IN DATE
390 ,p_step_or_rate OUT NOCOPY VARCHAR2
391 ,p_PT_value OUT NOCOPY NUMBER
392 ,p_PT_eff_start_date OUT NOCOPY DATE
393 ,p_PT_eff_end_date OUT NOCOPY DATE) IS
394
395 -- for a given pay_plan and grade this returns the minimum value and step
396 l_PT_value NUMBER;
397 l_record_found BOOLEAN := FALSE;
398 --
399 CURSOR cur_pay IS
400 SELECT cin.value basic_pay
401 ,col.user_column_name step_or_rate
402 ,cin.effective_start_date
403 ,cin.effective_end_date
404 FROM pay_user_column_instances_f cin
405 ,pay_user_rows_f urw
406 ,pay_user_columns col
407 WHERE col.user_table_id = p_user_table_id
408 AND urw.user_table_id = p_user_table_id
409 AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
410 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
411 AND cin.user_row_id = urw.user_row_id
412 AND cin.user_column_id = col.user_column_id
413 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
414 ORDER BY TO_NUMBER(cin.value) ASC;
415 --
416 -- The order by means we will get the lowest value first
417 BEGIN
418 FOR cur_pay_rec IN cur_pay LOOP
419 p_step_or_rate := cur_pay_rec.step_or_rate;
420 l_PT_value := ROUND(cur_pay_rec.basic_pay,2);
421 p_PT_value := l_PT_value;
422 p_PT_eff_start_date := cur_pay_rec.effective_start_date;
423 p_PT_eff_end_date := cur_pay_rec.effective_end_date;
424 l_record_found := TRUE;
425 IF l_PT_value IS NULL THEN
426 -- Set tokens to give name of pay table, pay plan, grade, step and rate
427 hr_utility.set_message(8301,'GHR_38252_NULL_PAY_PLAN_VALUE');
428 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.get_user_table_name(p_user_table_id) );
429 hr_utility.set_message_token('STEP',cur_pay_rec.step_or_rate);
430 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
431 hr_utility.set_message_token('GRADE',p_grade_or_level);
432 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
433 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date) );
434 raise ghr_pay_calc.pay_calc_message;
435 END IF;
436 EXIT;
437 END LOOP;
438 --
439 IF NOT l_record_found THEN
440 -- Set tokens to give name of pay table, pay plan, grade, step and rate
441 -- Note: the is no step!
442 hr_utility.set_message(8301,'GHR_38257_NO_MIN_PAY_PLAN_VAL');
443 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.get_user_table_name(p_user_table_id) );
444 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
445 hr_utility.set_message_token('GRADE',p_grade_or_level);
446 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
447 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date) );
448 raise ghr_pay_calc.pay_calc_message;
449 END IF;
450 --
451
452 EXCEPTION
453 WHEN others THEN
454 -- Reset IN OUT parameters and set OUT parameters
455
456 p_step_or_rate := NULL;
457 p_PT_value := NULL;
458 p_PT_eff_start_date := NULL;
459 p_PT_eff_end_date := NULL;
460
461 RAISE;
462
463 END get_min_pay_table_value;
464 --
465 PROCEDURE get_max_pay_table_value (p_user_table_id IN NUMBER
466 ,p_pay_plan IN VARCHAR2
467 ,p_grade_or_level IN VARCHAR2
468 ,p_effective_date IN DATE
469 ,p_step_or_rate OUT NOCOPY VARCHAR2
470 ,p_PT_value OUT NOCOPY NUMBER
471 ,p_PT_eff_start_date OUT NOCOPY DATE
472 ,p_PT_eff_end_date OUT NOCOPY DATE) IS
473 --
474 -- for a given pay_plan and grade this returns the minimum value and step
475 l_PT_value NUMBER;
476 l_record_found BOOLEAN := FALSE;
477 --
478 CURSOR cur_pay IS
479 SELECT cin.value basic_pay
480 ,col.user_column_name step_or_rate
481 ,cin.effective_start_date
482 ,cin.effective_end_date
483 FROM pay_user_column_instances_f cin
484 ,pay_user_rows_f urw
485 ,pay_user_columns col
486 WHERE col.user_table_id = p_user_table_id
487 AND urw.user_table_id = p_user_table_id
488 AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
489 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
490 AND cin.user_row_id = urw.user_row_id
491 AND cin.user_column_id = col.user_column_id
492 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
493 ORDER BY TO_NUMBER(cin.value) DESC;
494 --
495 -- The order by means we will get the HIGHEST value first
496 BEGIN
497 FOR cur_pay_rec IN cur_pay LOOP
498 p_step_or_rate := cur_pay_rec.step_or_rate;
499 l_PT_value := ROUND(cur_pay_rec.basic_pay,2);
500 p_PT_value := l_PT_value;
501 p_PT_eff_start_date := cur_pay_rec.effective_start_date;
502 p_PT_eff_end_date := cur_pay_rec.effective_end_date;
503 l_record_found := TRUE;
504
505 IF l_PT_value IS NULL THEN
506 -- Set tokens to give name of pay table, pay plan, grade, step and rate
507 hr_utility.set_message(8301,'GHR_38252_NULL_PAY_PLAN_VALUE');
508 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.get_user_table_name(p_user_table_id) );
509 hr_utility.set_message_token('STEP',cur_pay_rec.step_or_rate);
510 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
511 hr_utility.set_message_token('GRADE',p_grade_or_level);
512 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
513 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date) );
514 raise ghr_pay_calc.pay_calc_message;
515 END IF;
516 EXIT;
517 END LOOP;
518 --
519 IF NOT l_record_found THEN
520 -- Set tokens to give name of pay table, pay plan, grade, step and rate
521 hr_utility.set_message(8301,'GHR_38258_NO_MAX_PAY_PLAN_VAL');
522 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.get_user_table_name(p_user_table_id) );
523 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
524 hr_utility.set_message_token('GRADE',p_grade_or_level);
525 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
526 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date) );
527 raise ghr_pay_calc.pay_calc_message;
528 END IF;
529 --
530
531 EXCEPTION
532 WHEN others THEN
533 -- Reset IN OUT parameters and set OUT parameters
534
535 p_step_or_rate := NULL;
536 p_PT_value := NULL;
537 p_PT_eff_start_date := NULL;
538 p_PT_eff_end_date := NULL;
539
540 RAISE;
541
542 END get_max_pay_table_value;
543 ---
544 ---
545 --
546 PROCEDURE get_890_pay_table_value (p_user_table_id IN NUMBER
547 ,p_pay_plan IN VARCHAR2
548 ,p_grade_or_level IN VARCHAR2
549 ,p_effective_date IN DATE
550 ,p_current_val IN NUMBER
551 ,p_in_step_or_rate IN VARCHAR2
552 ,p_step_or_rate OUT NOCOPY VARCHAR2
553 ,p_PT_value OUT NOCOPY NUMBER
554 ,p_PT_eff_start_date OUT NOCOPY DATE
555 ,p_PT_eff_end_date OUT NOCOPY DATE) IS
556 --
557 -- for a given pay_plan and grade this returns the minimum value and step
558 l_PT_value NUMBER;
559 l_record_found BOOLEAN := FALSE;
560 ---BUG 6211029
561 l_in_PT_value NUMBER;
562 l_in_PT_eff_start_date DATE;
563 l_in_PT_eff_end_date DATE;
564 -- BUG 6211029
565 --
566 CURSOR cur_pay IS
567 SELECT cin.value basic_pay
568 ,col.user_column_name step_or_rate
569 ,cin.effective_start_date
570 ,cin.effective_end_date
571 FROM pay_user_column_instances_f cin
572 ,pay_user_rows_f urw
573 ,pay_user_columns col
574 WHERE col.user_table_id = p_user_table_id
575 AND urw.user_table_id = p_user_table_id
576 AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
577 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
578 AND cin.user_row_id = urw.user_row_id
579 AND cin.user_column_id = col.user_column_id
580 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
581 ORDER BY TO_NUMBER(cin.value) ASC;
582 --
583 -- The order by means we will get the LOWEST value first
584 BEGIN
585 --BUG# 6211029 Added p_in_step_or_rate entered by the user initially after changing position
586 -- this will be passed as '01' if the value of User entered step or rate is greater than the
587 -- basic pay then need to consider the same otherwise need to be autopopulated with
588 -- the Minimum step or rate having value greater than the adjusted basic pay
589 IF p_in_step_or_rate is not null THEN
590 ghr_pay_calc.get_pay_table_value (p_user_table_id => p_user_table_id
591 ,p_pay_plan => p_pay_plan
592 ,p_grade_or_level => p_grade_or_level
593 ,p_step_or_rate => p_in_step_or_rate
594 ,p_effective_date => p_effective_date
595 ,p_PT_value => l_in_PT_value
596 ,p_PT_eff_start_date => l_in_PT_eff_start_date
597 ,p_PT_eff_end_date => l_in_PT_eff_end_date);
598
599 END IF;
600 IF NVL(l_in_PT_value,0) >= p_current_val AND p_in_step_or_rate is not null then
601 p_step_or_rate := p_in_step_or_rate;
602 p_PT_value := l_in_PT_value;
603 p_PT_eff_start_date := l_in_PT_eff_start_date;
604 p_PT_eff_end_date := l_in_PT_eff_end_date;
605 ELSE
606 --End of BUG 6211029
607 FOR cur_pay_rec IN cur_pay LOOP
608 IF cur_pay_rec.basic_pay >= p_current_val then
609 p_step_or_rate := cur_pay_rec.step_or_rate;
610 l_PT_value := ROUND(cur_pay_rec.basic_pay,2);
611 p_PT_value := l_PT_value;
612 p_PT_eff_start_date := cur_pay_rec.effective_start_date;
613 p_PT_eff_end_date := cur_pay_rec.effective_end_date;
614 l_record_found := TRUE;
615
616 IF l_PT_value IS NULL THEN
617 -- Set tokens to give name of pay table, pay plan, grade, step and rate
618 hr_utility.set_message(8301,'GHR_38252_NULL_PAY_PLAN_VALUE');
619 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.get_user_table_name(p_user_table_id) );
620 hr_utility.set_message_token('STEP',cur_pay_rec.step_or_rate);
621 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
622 hr_utility.set_message_token('GRADE',p_grade_or_level);
623 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
624 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date) );
625 raise ghr_pay_calc.pay_calc_message;
626 END IF;
627 EXIT;
628 END IF;
629 END LOOP;
630
631 --
632 IF NOT l_record_found THEN
633 -- Set tokens to give name of pay table, pay plan, grade, step and rate
634 hr_utility.set_message(8301,'GHR_38258_NO_MAX_PAY_PLAN_VAL');
635 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.get_user_table_name(p_user_table_id) );
636 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
637 hr_utility.set_message_token('GRADE',p_grade_or_level);
638 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
639 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date) );
640 raise ghr_pay_calc.pay_calc_message;
641 END IF;
642 --
643 END IF; -- p_in_step_or_rate Comparison
644
645 EXCEPTION
646 WHEN others THEN
647 -- Reset IN OUT parameters and set OUT parameters
648
649 p_step_or_rate := NULL;
650 p_PT_value := NULL;
651 p_PT_eff_start_date := NULL;
652 p_PT_eff_end_date := NULL;
653
654 RAISE;
655
656 END get_890_pay_table_value;
657 --
658 --
659 --
660 -- This procedure gets the minimum pay table value that is greater than a given value (X)
661 -- and returns the step associated with it
662 -- will return null if there is not one as opposed to error
663 PROCEDURE get_min_pay_table_value_GT_X (p_user_table_id IN NUMBER
664 ,p_pay_plan IN VARCHAR2
665 ,p_grade_or_level IN VARCHAR2
666 ,p_effective_date IN DATE
667 ,p_x IN NUMBER
668 ,p_step_or_rate OUT NOCOPY VARCHAR2
669 ,p_PT_value OUT NOCOPY NUMBER) IS
670
671 -- for a given pay_plan and grade this returns the minimum value and step
672 l_PT_value NUMBER;
673 l_record_found BOOLEAN := FALSE;
674 --
675 CURSOR cur_pay IS
676 SELECT cin.value basic_pay
677 ,col.user_column_name step_or_rate
678 ,cin.effective_start_date
679 ,cin.effective_end_date
680 FROM pay_user_column_instances_f cin
681 ,pay_user_rows_f urw
682 ,pay_user_columns col
683 WHERE col.user_table_id = p_user_table_id
684 AND urw.user_table_id = p_user_table_id
685 AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
686 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
687 AND cin.user_row_id = urw.user_row_id
688 AND cin.user_column_id = col.user_column_id
689 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
690 AND cin.value >= p_x
691 ORDER BY TO_NUMBER(cin.value) ASC;
692 --
693 -- The order by means we will get the lowest value first
694 BEGIN
695 FOR cur_pay_rec IN cur_pay LOOP
696 p_step_or_rate := cur_pay_rec.step_or_rate;
697 p_PT_value := ROUND(cur_pay_rec.basic_pay,2);
698 l_record_found := TRUE;
699 EXIT;
700 END LOOP;
701 --
702 IF NOT l_record_found THEN
703 p_step_or_rate := null;
704 p_PT_value := null;
705 END IF;
706 --
707
708 EXCEPTION
709 WHEN others THEN
710 -- Reset IN OUT parameters and set OUT parameters
711
712 p_step_or_rate := NULL;
713 p_PT_value := NULL;
714
715 RAISE;
716 END get_min_pay_table_value_GT_X;
717 --
718 --
719 PROCEDURE get_basic_pay_SAL891_pos (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
720 ,p_basic_pay OUT NOCOPY NUMBER
721 ,p_step_or_rate OUT NOCOPY VARCHAR2) IS
722 --
723 l_min_basic_pay NUMBER;
724 l_min_step VARCHAR2(30);
725 l_max_basic_pay NUMBER;
726 l_max_step VARCHAR2(30);
727 l_step_diff INTEGER;
728 l_basic_pay NUMBER;
729 l_dummy_date DATE;
730 BEGIN
731 --
732 get_min_pay_table_value(p_pay_calc_data.user_table_id
733 ,'GS'
734 ,p_pay_calc_data.grade_or_level
735 ,p_pay_calc_data.effective_date
736 ,l_min_step
737 ,l_min_basic_pay
738 ,l_dummy_date
739 ,l_dummy_date);
740 --
741 get_max_pay_table_value(p_pay_calc_data.user_table_id
742 ,'GS'
743 ,p_pay_calc_data.grade_or_level
744 ,p_pay_calc_data.effective_date
745 ,l_max_step
746 ,l_max_basic_pay
747 ,l_dummy_date
748 ,l_dummy_date);
749
750 --
751 -- May have to be careful using to_number since what we call the step is actually stored
752 -- as a varchar2 there ful it actually has the possibility of having characters in it
753 -- ORA-01722: invalid number will occur in this case
754 -- Also be careful if we got 0?
755 BEGIN
756 l_step_diff := TO_NUMBER(l_max_step) - TO_NUMBER(l_min_step);
757 END;
758 ----Basic Pay Calc issue in GMIT Pay --- Basically Matching with locality C2 step.
759 l_basic_pay := p_pay_calc_data.current_basic_pay + CEIL(( (l_max_basic_pay - l_min_basic_pay)/l_step_diff));
760 IF l_basic_pay > l_max_basic_pay THEN
761 p_basic_pay := l_max_basic_pay;
762 p_step_or_rate := '00'; ----------l_max_step;
763 ELSE
764 p_basic_pay := l_basic_pay;
765 p_step_or_rate := '00';
766 END IF;
767 --
768
769 EXCEPTION
770 WHEN others THEN
771 -- Reset IN OUT parameters and set OUT parameters
772
773 p_step_or_rate := NULL;
774 p_basic_pay := NULL;
775
776 RAISE;
777 END get_basic_pay_SAL891_pos;
778 --
779 PROCEDURE get_basic_pay_SAL891_per (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
780 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
781 ,p_basic_pay OUT NOCOPY NUMBER
782 ,p_step_or_rate OUT NOCOPY VARCHAR2) IS
783 --
784 -- This one always uses the retained grade details no matter what
785 --
786 l_min_basic_pay NUMBER;
787 l_min_step VARCHAR2(30);
788 l_max_basic_pay NUMBER;
789 l_max_step VARCHAR2(30);
790 l_step_diff INTEGER;
791 l_basic_pay NUMBER;
792 l_dummy_date DATE;
793 --
794 BEGIN
795 --
796 get_min_pay_table_value(p_retained_grade.user_table_id
797 ,'GS'
798 ,p_retained_grade.grade_or_level
799 ,p_pay_calc_data.effective_date
800 ,l_min_step
801 ,l_min_basic_pay
802 ,l_dummy_date
803 ,l_dummy_date);
804 --
805 get_max_pay_table_value(p_retained_grade.user_table_id
806 ,'GS'
807 ,p_retained_grade.grade_or_level
808 ,p_pay_calc_data.effective_date
809 ,l_max_step
810 ,l_max_basic_pay
811 ,l_dummy_date
812 ,l_dummy_date);
813
814 --
815 -- May have to be careful using to_number since what we call the step is actually stored
816 -- as a varchar2 there ful it actually has the possibility of having characters in it
817 -- ORA-01722: invalid number will occur in this case
818 -- Also be careful if we got 0?
819 BEGIN
820 l_step_diff := TO_NUMBER(l_max_step) - TO_NUMBER(l_min_step);
821 END;
822 -----l_basic_pay := ROUND(p_pay_calc_data.current_basic_pay + ( (l_max_basic_pay - l_min_basic_pay)/l_step_diff) ,0);
823 l_basic_pay := p_pay_calc_data.current_basic_pay + CEIL(( (l_max_basic_pay - l_min_basic_pay)/l_step_diff));
824 IF l_basic_pay > l_max_basic_pay THEN
825 p_basic_pay := l_max_basic_pay;
826 p_step_or_rate := '00'; ----------l_max_step;
827 ELSE
828 p_basic_pay := l_basic_pay;
829 p_step_or_rate := '00';
830 END IF;
831 --
832
833 EXCEPTION
834 WHEN others THEN
835 -- Reset IN OUT parameters and set OUT parameters
836
837 p_step_or_rate := NULL;
838 p_basic_pay := NULL;
839
840 RAISE;
841
842 END get_basic_pay_SAL891_per;
843 --
844 PROCEDURE check_current_PT (p_PT_date IN DATE
845 ,p_eff_start_date IN DATE) IS
846 BEGIN
847 IF p_PT_date <> p_eff_start_date THEN
848 hr_utility.set_message(8301,'GHR_38395_NOT_CURRENT_PT');
849 -- hr_utility.set_message_token('PAY_TABLE_NAME',get_user_table_name(p_user_table_id) );
850 raise ghr_pay_calc.pay_calc_message;
851 END IF;
852 END check_current_PT;
853 --
854 PROCEDURE check_old_PT (p_PT_date IN DATE
855 ,p_eff_end_date IN DATE) IS
856 BEGIN
857 /* This procedure is no more required as per Bug 3837402 .
858 IF p_PT_date -1 <> p_eff_end_date THEN
859 hr_utility.set_message(8301,'GHR_38396_NOT_OLD_PT');
860 -- hr_utility.set_message_token('PAY_TABLE_NAME',get_user_table_name(p_user_table_id) );
861 raise ghr_pay_calc.pay_calc_message;
862 END IF;
863 */
864 null;
865
866 END check_old_PT;
867 --
868 PROCEDURE get_basic_pay_SAL894_6step(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
869 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
870 ,p_pay_table_data IN VARCHAR2
871 ,p_basic_pay OUT NOCOPY NUMBER
872 ,p_PT_eff_start_date OUT NOCOPY DATE
873 ,p_7dp OUT NOCOPY NUMBER) IS
874 --
875 l_user_table_id NUMBER;
876 l_pay_plan VARCHAR2(30);
877 l_grade_or_level VARCHAR2(60);
878 l_step_or_rate VARCHAR2(30);
879 l_pay_basis VARCHAR2(30);
880 --
881 l_PT_eff_start_date DATE;
882 l_eff_start_date DATE;
883 l_eff_end_date DATE;
884 --
885 l_dummy_step VARCHAR2(30);
886 --
887 l_old_basic_pay NUMBER;
888 l_min_old_basic_pay NUMBER;
889 l_max_old_basic_pay NUMBER;
890 --
891 l_cur_basic_pay NUMBER;
892 l_min_cur_basic_pay NUMBER;
893 l_max_cur_basic_pay NUMBER;
894 --
895 l_A NUMBER;
896 l_B NUMBER;
897 l_C NUMBER;
898 l_D NUMBER;
899 l_E NUMBER;
900 l_basic_pay NUMBER;
901 --
902 BEGIN
903 -- First work out what pay table data to use
904 --
905 -- bug 710171 Always use GS as the Pay plan
906 l_pay_plan := 'GS';
907 IF p_pay_table_data = 'POSITION' THEN
908 l_user_table_id := p_pay_calc_data.user_table_id;
909 l_grade_or_level := p_pay_calc_data.grade_or_level;
910 l_pay_basis := p_pay_calc_data.pay_basis;
911 --
912 ELSE
913 l_user_table_id := p_retained_grade.user_table_id;
914 l_grade_or_level := p_retained_grade.grade_or_level;
915 l_pay_basis := p_retained_grade.pay_basis;
916 --
917 END IF;
918 --AVR
919 IF p_pay_calc_data.noa_code = '894' AND p_pay_calc_data.pay_rate_determinant = 'M' THEN
920 IF p_retained_grade.grade_or_level IS NOT NULL THEN
921 l_grade_or_level := p_retained_grade.grade_or_level;
922 l_step_or_rate := p_retained_grade.step_or_rate;
923 ELSE
924 l_grade_or_level := p_pay_calc_data.grade_or_level;
925 l_step_or_rate := p_pay_calc_data.step_or_rate;
926 END IF;
927 END IF;
928 --AVR
929 -- Get current value just to get the Pay Table effective date
930 ghr_pay_calc.get_pay_table_value(l_user_table_id
931 ,l_pay_plan
932 ,l_grade_or_level
933 ,'01'
934 ,p_pay_calc_data.effective_date
935 ,l_cur_basic_pay
936 ,l_eff_start_date
937 ,l_eff_end_date);
938 --
939 l_PT_eff_start_date := l_eff_start_date;
940 --
941 -- Step 1
942 ---------
943 get_min_pay_table_value(l_user_table_id
944 ,l_pay_plan
945 ,l_grade_or_level
946 ,l_PT_eff_start_date - 1
947 ,l_dummy_step
948 ,l_min_old_basic_pay
949 ,l_eff_start_date
950 ,l_eff_end_date);
951 --
952 -- Check we used an old Pay Table
953 -- This vaidation is no more required as per bug 3837402.
954 -- check_old_PT (l_PT_eff_start_date, l_eff_end_date);
955 --
956 -- bug 710171 Use Current basic Pay as the starting point
957 l_A := p_pay_calc_data.current_basic_pay - l_min_old_basic_pay;
958 --
959 -- Step 2
960 ---------
961 get_max_pay_table_value(l_user_table_id
962 ,l_pay_plan
963 ,l_grade_or_level
964 ,l_PT_eff_start_date - 1
965 ,l_dummy_step
966 ,l_max_old_basic_pay
967 ,l_eff_start_date
968 ,l_eff_end_date);
969 --
970 -- Check we used an old Pay Table
971 -- This vaidation is no more required as per bug 3837402.
972 -- check_old_PT (l_PT_eff_start_date, l_eff_end_date);
973 --
974 l_B := l_max_old_basic_pay - l_min_old_basic_pay;
975 --
976 -- Step 3 -- Otherwise refered to as the 7d.p. which is also used in the
977 -- locality adj calc
978 ---------
979 l_C := TRUNC( (l_A/l_B) ,7);
980 --
981 -- Step 4
982 ---------
983 --
984 get_min_pay_table_value(l_user_table_id
985 ,l_pay_plan
986 ,l_grade_or_level
987 ,l_PT_eff_start_date
988 ,l_dummy_step
989 ,l_min_cur_basic_pay
990 ,l_eff_start_date
991 ,l_eff_end_date);
992 --
993 -- Check we used a current Pay Table
994 check_current_PT (l_PT_eff_start_date, l_eff_start_date);
995 --
996 get_max_pay_table_value(l_user_table_id
997 ,l_pay_plan
998 ,l_grade_or_level
999 ,l_PT_eff_start_date
1000 ,l_dummy_step
1001 ,l_max_cur_basic_pay
1002 ,l_eff_start_date
1003 ,l_eff_end_date);
1004 --
1005 -- Check we used a current Pay Table
1006 check_current_PT (l_PT_eff_start_date, l_eff_start_date);
1007 --
1008 l_D := l_max_cur_basic_pay - l_min_cur_basic_pay;
1009 --
1010 -- Step 5
1011 ---------
1012 l_E := l_C * l_D;
1013 --
1014 -- Step 6
1015 ---------
1016 ---l_basic_pay := ROUND(l_E + l_min_cur_basic_pay); --Bug#6603789 added round
1017
1018 --BUG# 6680463 5 USC 531.205 --Basic rate should be rounded to the next whole dollar amount
1019
1020 l_basic_pay := CEIL(l_E + l_min_cur_basic_pay);
1021
1022 --
1023 p_basic_pay := ghr_pay_calc.convert_amount(l_basic_pay
1024 ,l_pay_basis
1025 ,p_pay_calc_data.pay_basis);
1026 --
1027 p_PT_eff_start_date := l_PT_eff_start_date;
1028 p_7dp := l_C;
1029 --
1030
1031 EXCEPTION
1032 WHEN others THEN
1033 -- Reset IN OUT parameters and set OUT parameters
1034
1035 p_7dp := NULL;
1036 p_basic_pay := NULL;
1037 p_PT_eff_start_date := NULL;
1038
1039 RAISE;
1040 END get_basic_pay_SAL894_6step;
1041 --
1042 --
1043 PROCEDURE get_basic_pay_SAL894_50 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1044 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
1045 ,p_pay_table_data IN VARCHAR2
1046 ,p_basic_pay OUT NOCOPY NUMBER
1047 ,p_step OUT NOCOPY VARCHAR2
1048 ,p_prd OUT NOCOPY VARCHAR2
1049 ,p_PT_eff_start_date OUT NOCOPY DATE) IS
1050 --
1051 l_user_table_id NUMBER;
1052 l_pay_plan VARCHAR2(30);
1053 l_pc_pay_plan VARCHAR2(30);
1054 l_grade_or_level VARCHAR2(60);
1055 l_step_or_rate VARCHAR2(30);
1056 l_pay_basis VARCHAR2(30);
1057
1058 l_dummy_step VARCHAR2(30);
1059 l_max_cur_basic_pay NUMBER;
1060 l_max_old_basic_pay NUMBER;
1061 l_ret_basic_pay NUMBER;
1062 l_pos_basic_pay NUMBER;
1063 l_pos_step VARCHAR2(30);
1064 --
1065 l_PT_eff_start_date DATE;
1066 l_eff_start_date DATE;
1067 l_eff_end_date DATE;
1068 --
1069 l_cur_pos_basic_pay NUMBER;
1070 --
1071 l_converted_increase NUMBER;
1072 --
1073 l_user_table_name pay_user_tables.user_table_name%type;
1074
1075 --Bug 3180991
1076 l_old_user_table_id NUMBER;
1077 l_asg_ei_data per_assignment_extra_info%rowtype;
1078 l_prd_effective_date date;
1079 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
1080 l_position_id per_assignments_f.position_id%type;
1081 l_assignment_id per_assignments_f.assignment_id%type;
1082 l_temp_step per_people_extra_info.pei_information6%type;
1083 l_effective_date date;
1084
1085 --Cursor to get the position id.
1086 CURSOR cur_per_pos(p_effective_date date) is
1087 SELECT asg.position_id, asg.assignment_id
1088 FROM per_assignments_f asg
1089 WHERE asg.person_id = p_pay_calc_data.person_id
1090 AND trunc(nvl(p_effective_date,sysdate))
1091 between asg.effective_start_date and asg.effective_end_date
1092 AND asg.assignment_type <> 'B'
1093 AND asg.primary_flag = 'Y';
1094
1095 CURSOR cur_per_pos_2 is
1096 SELECT asg.effective_start_date,asg.position_id, asg.assignment_id
1097 FROM per_assignments_f asg
1098 WHERE asg.person_id = p_pay_calc_data.person_id
1099 AND asg.position_id is not null
1100 AND asg.assignment_type <> 'B'
1101 AND asg.primary_flag = 'Y'
1102 ORDER BY asg.effective_start_date;
1103
1104
1105 CURSOR cur_pei (p_effective_date date) IS
1106 SELECT pei.person_extra_info_id
1107 ,pei.pei_information6 retained_user_table_id
1108 ,pei.pei_information9 retained_temp_step
1109 FROM per_people_extra_info pei
1110 WHERE pei.person_id = p_pay_calc_data.person_id
1111 AND pei.information_type = 'GHR_US_RETAINED_GRADE'
1112 AND p_effective_date BETWEEN NVL(fnd_date.canonical_to_date(pei.pei_information1),p_effective_date)
1113 AND NVL(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date);
1114 --Bug 3180991
1115
1116 BEGIN
1117
1118 -- First work out what pay table data to use
1119
1120 IF p_pay_table_data = 'POSITION' THEN
1121 l_user_table_id := p_pay_calc_data.user_table_id;
1122 l_pay_plan := p_pay_calc_data.pay_plan;
1123 l_grade_or_level := p_pay_calc_data.grade_or_level;
1124 l_step_or_rate := p_pay_calc_data.step_or_rate;
1125 l_pay_basis := p_pay_calc_data.pay_basis;
1126 --
1127 ELSE
1128 l_user_table_id := p_retained_grade.user_table_id;
1129 l_pay_plan := p_retained_grade.pay_plan;
1130 l_grade_or_level := p_retained_grade.grade_or_level;
1131 l_step_or_rate := p_retained_grade.step_or_rate;
1132 l_pay_basis := p_retained_grade.pay_basis;
1133 --
1134 END IF;
1135 IF l_pay_plan IN ('GM','GH') THEN
1136 l_pay_plan := 'GS';
1137 END IF;
1138 --
1139
1140 get_max_pay_table_value(l_user_table_id
1141 ,l_pay_plan
1142 ,l_grade_or_level
1143 ,p_pay_calc_data.effective_date
1144 ,l_dummy_step
1145 ,l_max_cur_basic_pay
1146 ,l_eff_start_date
1147 ,l_eff_end_date);
1148
1149 hr_utility.set_location(' get_basic_pay_SAL894_50 After first max pay' ||l_eff_start_date,12);
1150
1151 -- set the Pay Table efective date as this is the first lookup we have done
1152 l_PT_eff_start_date := l_eff_start_date;
1153
1154 --Bug# 3180991
1155 l_effective_date := p_pay_calc_data.effective_date ;
1156 l_prd_effective_date := l_effective_date - 1;
1157
1158 -- get the positin id and assignment id as on l_PT_eff_start_date-1 using the cursor.
1159 FOR per_pos_id in cur_per_pos(l_PT_eff_start_date-1)
1160 LOOP
1161 l_position_id := per_pos_id.position_id;
1162 l_assignment_id := per_pos_id.assignment_id;
1163 hr_utility.set_location(' get_basic_pay_SAL894_50 Position id ' ||l_position_id,12);
1164 END LOOP;
1165
1166 IF l_assignment_id is null THEN
1167 FOR per_pos_id_2 in cur_per_pos_2
1168 LOOP
1169 l_prd_effective_date := per_pos_id_2.effective_start_date;
1170 l_position_id := per_pos_id_2.position_id;
1171 l_assignment_id := per_pos_id_2.assignment_id;
1172 hr_utility.set_location(' get_basic_pay_SAL894_50 Position id ' ||l_position_id,12);
1173 exit;
1174 END LOOP;
1175 END IF;
1176
1177 IF l_assignment_id is not null THEN
1178 -- This is used to get the prd.
1179 hr_utility.set_location(' get_basic_pay_SAL894_50 l_assignment_id' ||l_assignment_id,10);
1180 hr_utility.set_location(' get_basic_pay_SAL894_50 l_prd_effective_date' ||l_prd_effective_date,10);
1181
1182 ghr_history_fetch.fetch_asgei( p_assignment_id => l_assignment_id
1183 ,p_information_type => 'GHR_US_ASG_SF52'
1184 ,p_date_effective => l_prd_effective_date
1185 ,p_asg_ei_data => l_asg_ei_data
1186 );
1187
1188 hr_utility.set_location(' get_basic_pay_SAL894_50 l_asg_ei_data.aei_information6 ' ||l_asg_ei_data.aei_information6 ,11);
1189
1190 IF l_asg_ei_data.aei_information6 NOT IN ('A','B','E','F','U','V') THEN
1191
1192 l_old_user_table_id := ghr_pay_calc.get_user_table_id( l_position_id , l_prd_effective_date );
1193 hr_utility.set_location(' get_basic_pay_SAL894_50 l_old_user_table_id ' ||l_old_user_table_id ,13);
1194
1195 ELSE
1196
1197 --Get the retain grade info as on l_PT_eff_start_date-1
1198 hr_utility.set_location(' get_basic_pay_SAL894_50 p_pay_calc_data.person_id' ||p_pay_calc_data.person_id,14);
1199 hr_utility.set_location(' get_basic_pay_SAL894_50 l_PT_eff_start_date-1' ||l_PT_eff_start_date,14);
1200 hr_utility.set_location(' get_basic_pay_SAL894_50 p_pay_calc_data.pa_request_id' ||p_pay_calc_data.pa_request_id,14);
1201
1202 hr_utility.set_location(' get_basic_pay_SAL894_50 l_old_user_table_id ' ||l_old_user_table_id ,14);
1203
1204 /* We cannot use this procedure as pa_request_id is not available as of now.
1205 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
1206 (p_person_id => p_pay_calc_data.person_id
1207 ,p_effective_date => l_prd_effective_date
1208 ,p_pa_request_id => p_pay_calc_data.pa_request_id);
1209
1210 */
1211 FOR cur_pei_rec IN cur_pei(l_prd_effective_date)
1212 LOOP
1213 l_old_user_table_id := cur_pei_rec.retained_user_table_id;
1214 l_temp_step := cur_pei_rec.retained_temp_step;
1215 END LOOP;
1216
1217 hr_utility.set_location(' get_basic_pay_SAL894_50 temp step after loop' ||l_temp_step ,14);
1218 hr_utility.set_location(' get_basic_pay_SAL894_50 l_old_user_table_id after loop' ||l_old_user_table_id ,14);
1219
1220 --check for temp promotion and temp step is not null then use ghr_pay_calc.get_user_table_id.
1221 IF l_temp_step IS NOT NULL THEN
1222 l_old_user_table_id := ghr_pay_calc.get_user_table_id( l_position_id , l_prd_effective_date );
1223 hr_utility.set_location(' get_basic_pay_SAL894_50 if l_old_user_table_id ' ||l_old_user_table_id ,15);
1224 END IF;
1225
1226 END IF;
1227
1228 ELSE
1229 l_old_user_table_id := l_user_table_id;
1230 END IF;
1231
1232 --Bug# 3180991
1233
1234 get_max_pay_table_value(l_old_user_table_id -- changed for 3180991
1235 ,l_pay_plan
1236 ,l_grade_or_level
1237 ,l_effective_date - 1
1238 ,l_dummy_step
1239 ,l_max_old_basic_pay
1240 ,l_eff_start_date
1241 ,l_eff_end_date);
1242
1243
1244 --Bug# 3180991 Added If Statement
1245 -- This vaidation is no more required as per bug 3837402.
1246 /* IF l_old_user_table_id = l_user_table_id THEN
1247 --
1248 -- Check we used an old Pay Table
1249
1250 check_old_PT (l_PT_eff_start_date, l_eff_end_date);
1251 --
1252 END IF;
1253 */
1254 l_converted_increase := ghr_pay_calc.convert_amount( (l_max_cur_basic_pay - l_max_old_basic_pay)/2
1255 ,l_pay_basis
1256 ,p_pay_calc_data.pay_basis);
1257 --
1258
1259 --- Bug 1579674
1260 if l_pay_basis = 'PH' then
1261 l_ret_basic_pay := ROUND(p_pay_calc_data.current_basic_pay + l_converted_increase,2);
1262 else
1263 l_ret_basic_pay := ROUND(p_pay_calc_data.current_basic_pay + l_converted_increase,0);
1264 end if;
1265 --- Bug 1579674
1266
1267
1268 p_PT_eff_start_date := l_PT_eff_start_date;
1269 --
1270 IF p_pay_calc_data.pay_plan IN ('GM','GH') THEN
1271 l_pc_pay_plan := 'GS';
1272 ELSE
1273 l_pc_pay_plan := p_pay_calc_data.pay_plan;
1274 END IF;
1275
1276
1277 get_min_pay_table_value_GT_X (l_user_table_id
1278 ,l_pay_plan
1279 ,l_grade_or_level
1280 ,p_pay_calc_data.effective_date
1281 ,l_ret_basic_pay
1282 ,l_pos_step
1283 ,l_pos_basic_pay);
1284
1285
1286 IF l_pos_basic_pay IS NULL THEN
1287 -- For pay plan CA need to check it hasn't exceeded EX-04 (table 0000 step 00)
1288 IF l_pay_plan = 'CA' THEN
1289 IF l_ret_basic_pay > ghr_pay_calc.get_standard_pay_table_value('EX'
1290 ,'04'
1291 ,'00'
1292 ,p_pay_calc_data.effective_date) THEN
1293 hr_utility.set_message(8301, 'GHR_38587_NO_CALC_EXCEED_EX_IV');
1294 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
1295 raise ghr_pay_calc.unable_to_calculate;
1296 ELSE
1297 p_basic_pay := l_ret_basic_pay;
1298 p_step := '00';
1299 p_prd := NULL;
1300 END IF;
1301 ELSE
1302 p_basic_pay := l_ret_basic_pay;
1303 p_step := '00';
1304 p_prd := NULL;
1305 END IF;
1306 ELSE -- (pay retention is being terminated)
1307 -- Do not know what to do if pay plan is ES or IE and pay retention is terminated!
1308 IF l_pay_plan = 'CA' THEN
1309 hr_utility.set_message(8301, 'GHR_38588_NO_CALC_PAY_RET_END');
1310 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
1311 raise ghr_pay_calc.unable_to_calculate;
1312 ELSE
1313 p_basic_pay := l_pos_basic_pay;
1314 p_step := l_pos_step;
1315 l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
1316 IF p_pay_calc_data.pay_rate_determinant IN ('J','K','R','S','3') THEN
1317 IF l_pay_basis = 'PH' THEN
1318 p_prd := 0;
1319 ELSIF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
1320 p_prd := '0';
1321 ELSE
1322 p_prd := '6';
1323 END IF;
1324 ELSIF p_pay_calc_data.pay_rate_determinant = 'U' THEN
1325 IF l_pay_basis = 'PH' THEN
1326 p_prd := 'B';
1327 ELSIF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
1328 p_prd := 'B';
1329 ELSE
1330 p_prd := 'F';
1331 END IF;
1332 ELSIF p_pay_calc_data.pay_rate_determinant = 'V' THEN
1333 IF l_pay_basis = 'PH' THEN
1334 p_prd := 'A';
1335 ELSIF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
1336 p_prd := 'A';
1337 ELSE
1338 p_prd := 'E';
1339 END IF;
1340 END IF; -- end of PRD check inside Pay rentention terminated
1341 --
1342 END IF;
1343 END IF;
1344
1345 --
1346 EXCEPTION
1347 WHEN others THEN
1348 -- Reset IN OUT parameters and set OUT parameters
1349
1350 p_step := NULL;
1351 p_prd := NULL;
1352 p_basic_pay := NULL;
1353 p_PT_eff_start_date := NULL;
1354
1355 RAISE;
1356
1357 END get_basic_pay_SAL894_50;
1358 --
1359 --
1360 PROCEDURE get_basic_pay_SAL894_100 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1361 ,p_basic_pay OUT NOCOPY NUMBER
1362 ,p_PT_eff_start_date OUT NOCOPY DATE) IS
1363 --
1364 l_dummy_step VARCHAR2(30);
1365 l_pay_plan VARCHAR2(30);
1366 l_max_cur_basic_pay NUMBER;
1367 --
1368 l_max_old_basic_pay NUMBER;
1369 --
1370 l_PT_eff_start_date DATE;
1371 l_eff_start_date DATE;
1372 l_eff_end_date DATE;
1373 --For Bug 3180991
1374 l_old_user_table_id NUMBER;
1375 l_effective_date DATE;
1376 --
1377 BEGIN
1378 IF l_pay_plan IN ('GM','GH') THEN
1379 l_pay_plan := 'GS';
1380 ELSE
1381 l_pay_plan := p_pay_calc_data.pay_plan;
1382 END IF;
1383 --
1384 get_max_pay_table_value(p_pay_calc_data.user_table_id
1385 ,l_pay_plan
1386 ,p_pay_calc_data.grade_or_level
1387 ,p_pay_calc_data.effective_date
1388 ,l_dummy_step
1389 ,l_max_cur_basic_pay
1390 ,l_eff_start_date
1391 ,l_eff_end_date);
1392 --
1393 -- Set eff_start date of the Pay Table
1394 l_PT_eff_start_date := l_eff_start_date;
1395
1396 l_effective_date := p_pay_calc_data.effective_date;
1397 --
1398 --Bug# 3180991
1399 l_old_user_table_id := ghr_pay_calc.get_user_table_id( p_pay_calc_data.position_id, l_PT_eff_start_date-1 );
1400 --Bug# 3180991
1401
1402 get_max_pay_table_value(l_old_user_table_id
1403 ,l_pay_plan
1404 ,p_pay_calc_data.grade_or_level
1405 ,l_effective_date - 1
1406 ,l_dummy_step
1407 ,l_max_old_basic_pay
1408 ,l_eff_start_date
1409 ,l_eff_end_date);
1410
1411 --Bug# 3180991 Added If Statement
1412 -- This vaidation is no more required as per bug 3837402.
1413 /* IF l_old_user_table_id = p_pay_calc_data.user_table_id THEN
1414 --
1415 -- Check we used an old Pay Table
1416 -- This vaidation is no more required.
1417 check_old_PT (l_PT_eff_start_date, l_eff_end_date);
1418 --
1419 END IF;
1420 */
1421 p_basic_pay := ROUND(p_pay_calc_data.current_basic_pay + (l_max_cur_basic_pay - l_max_old_basic_pay) ,0);
1422 --
1423 p_PT_eff_start_date := l_PT_eff_start_date;
1424 --
1425
1426 EXCEPTION
1427 WHEN others THEN
1428 -- Reset IN OUT parameters and set OUT parameters
1429
1430 p_basic_pay := NULL;
1431 p_PT_eff_start_date := NULL;
1432
1433 RAISE;
1434 END get_basic_pay_SAL894_100;
1435 --
1436 --
1437 FUNCTION get_next_WGI_step (p_pay_plan IN VARCHAR2
1438 ,p_current_step IN VARCHAR2)
1439 RETURN VARCHAR2 IS
1440 --
1441 -- I assume there can only be one record for a given pay_plan and user table_id
1442 CURSOR cur_ppw IS
1443 SELECT ppw.to_step
1444 ,ppl.maximum_step
1445 FROM ghr_pay_plan_waiting_periods ppw
1446 ,ghr_pay_plans ppl
1447 WHERE ppl.pay_plan = p_pay_plan
1448 AND ppl.equivalent_pay_plan = ppw.pay_plan
1449 AND ppw.from_step = p_current_step;
1450 --
1451 l_new_step VARCHAR2(30);
1452 BEGIN
1453 FOR cur_ppw_rec IN cur_ppw LOOP
1454 l_new_step := cur_ppw_rec.to_step;
1455 --
1456 -- If the new step or rate is greater then the max then use the max
1457 IF l_new_step > cur_ppw_rec.maximum_step THEN
1458 l_new_step := cur_ppw_rec.maximum_step;
1459 END IF;
1460 --
1461 RETURN(l_new_step);
1462 END LOOP;
1463 --
1464 -- If we got here no record was returned
1465 -- set tokens to say the user table name and pay_plan that was used
1466 hr_utility.set_message(8301, 'GHR_38259_NO_WGI_STEP');
1467 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
1468 raise ghr_pay_calc.pay_calc_message;
1469 --
1470 END get_next_WGI_step;
1471 --
1472 PROCEDURE get_basic_pay_SALWGI_pos (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1473 ,p_basic_pay OUT NOCOPY NUMBER
1474 ,p_new_step_or_rate OUT NOCOPY VARCHAR2) IS
1475 --
1476 l_new_step_or_rate VARCHAR2(30);
1477 l_dummy_date DATE;
1478 BEGIN
1479 -- This is the calcualation of a salary Change (SALARY_CHG) noac codes 867 - Interim Within Grade Increase,
1480 -- 892 - Quality Increase, 893 - Within-Grade Increase, PRD of 0 or 6:
1481 -- Basically you just get the next step by adding the wgi_step_or_rate on to the current step to get
1482 -- a new one you then use that to look up on the pay tables
1483 --
1484 --
1485 l_new_step_or_rate := get_next_WGI_step (p_pay_calc_data.pay_plan
1486 ,p_pay_calc_data.current_step_or_rate);
1487
1488 ghr_pay_calc.get_pay_table_value(p_pay_calc_data.user_table_id
1489 ,p_pay_calc_data.pay_plan
1490 ,p_pay_calc_data.grade_or_level
1491 ,l_new_step_or_rate
1492 ,p_pay_calc_data.effective_date
1493 ,p_basic_pay
1494 ,l_dummy_date
1495 ,l_dummy_date);
1496 --
1497 p_new_step_or_rate := l_new_step_or_rate;
1498 --
1499
1500 EXCEPTION
1501 WHEN others THEN
1502 -- Reset IN OUT parameters and set OUT parameters
1503
1504 p_basic_pay := NULL;
1505 p_new_step_or_rate := NULL;
1506
1507 RAISE;
1508 END get_basic_pay_SALWGI_pos;
1509 --
1510 PROCEDURE get_basic_pay_SALWGI_per (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1511 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
1512 ,p_basic_pay OUT NOCOPY NUMBER
1513 ,p_new_step_or_rate OUT NOCOPY VARCHAR2) IS
1514 --
1515 -- This one always uses the retained grade details no matter what
1516 --
1517 l_basic_pay NUMBER;
1518 l_new_step_or_rate VARCHAR2(30);
1519 --
1520 l_dummy_date DATE;
1521 l_step_or_rate VARCHAR2(30);
1522 l_pay_plan VARCHAR2(30);
1523 l_grade_or_level VARCHAR2(60);
1524 l_user_table_id NUMBER;
1525 l_pay_basis VARCHAR2(30);
1526 --Bug 3021003
1527 l_ret_flag BOOLEAN;
1528 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
1529 l_temp_step VARCHAR2(30);
1530
1531 BEGIN
1532 -- This is the calcualation of a salary Change (SALARY_CHG) noac codes 867 - Interim Within Grade Increase,
1533 -- 892 - Quality Increase, 893 - Within-Grade Increase, PRD of A , B, E or F:
1534 -- As for SAL1_WGI above except you use the retained grade details to do the look up and return 00 as the
1535 -- step
1536 -- Basically you just get the next step by adding the wgi_step_or_rate on to the current step to get
1537 -- a new one you then use that to look up on the pay tables
1538 --
1539 -- Bug 3021003
1540 l_retained_grade.pay_plan := p_retained_grade.pay_plan;
1541 l_retained_grade.grade_or_level := p_retained_grade.grade_or_level;
1542 l_retained_grade.step_or_rate := p_retained_grade.step_or_rate;
1543 l_retained_grade.temp_step := p_retained_grade.temp_step;
1544
1545 hr_utility.set_location('NAR inside wgi_per',0);
1546 if p_retained_grade.temp_step is not null then
1547 l_step_or_rate := p_retained_grade.temp_step;
1548 l_user_table_id := p_pay_calc_data.user_table_id;
1549 l_pay_plan := p_pay_calc_data.pay_plan;
1550 l_grade_or_level := p_pay_calc_data.grade_or_level;
1551 l_pay_basis := p_pay_calc_data.pay_basis;
1552 else
1553 l_step_or_rate := p_retained_grade.step_or_rate;
1554 l_user_table_id := p_retained_grade.user_table_id;
1555 l_pay_plan := p_retained_grade.pay_plan;
1556 l_grade_or_level := p_retained_grade.grade_or_level;
1557 l_pay_basis := p_retained_grade.pay_basis;
1558 end if;
1559
1560 IF nvl(g_noa_family_code,'XXX') = 'CORRECT' then
1561 -- Bug 3021003
1562 hr_utility.set_location('NAR inside noa_fam code = CORRECT ',5);
1563 ghr_pay_calc.is_retained_ia(p_pay_calc_data.person_id,
1564 p_pay_calc_data.effective_date,
1565 l_retained_grade.pay_plan,
1566 l_retained_grade.grade_or_level,
1567 l_retained_grade.step_or_rate,
1568 l_retained_grade.temp_step,
1569 l_ret_flag);
1570 IF l_ret_flag = TRUE THEN
1571 hr_utility.set_location('NAR ret step ' ||l_retained_grade.step_or_rate,10);
1572 hr_utility.set_location('NAR pay plan '||p_pay_calc_data.pay_plan,20);
1573 -- Check for Temp step
1574 IF p_retained_grade.temp_step is not null then
1575 l_new_step_or_rate := get_next_WGI_step (l_retained_grade.pay_plan,l_step_or_rate);
1576 ELSE
1577 l_new_step_or_rate := get_next_WGI_step (l_retained_grade.pay_plan,l_retained_grade.step_or_rate);
1578 END IF;
1579 hr_utility.set_location('NAR new step after getting the step ' ||l_new_step_or_rate,30);
1580 ELSE
1581 l_new_step_or_rate := l_step_or_rate;
1582 END IF;
1583 hr_utility.set_location('NAR new step after getting the step ' ||l_new_step_or_rate,40);
1584 ELSE
1585 l_new_step_or_rate := get_next_WGI_step (l_pay_plan
1586 ,l_step_or_rate);
1587 END IF;
1588
1589 ghr_pay_calc.get_pay_table_value(l_user_table_id
1590 ,l_pay_plan
1591 ,l_grade_or_level
1592 ,l_new_step_or_rate
1593 ,p_pay_calc_data.effective_date
1594 ,l_basic_pay
1595 ,l_dummy_date
1596 ,l_dummy_date );
1597 --
1598 p_basic_pay := ghr_pay_calc.convert_amount(l_basic_pay
1599 ,l_pay_basis
1600 ,p_pay_calc_data.pay_basis);
1601 --
1602 p_new_step_or_rate := '00';
1603 --
1604 EXCEPTION
1605 WHEN others THEN
1606 -- Reset IN OUT parameters and set OUT parameters
1607
1608 p_basic_pay := NULL;
1609 p_new_step_or_rate := NULL;
1610
1611 RAISE;
1612 END get_basic_pay_SALWGI_per;
1613
1614
1615 PROCEDURE get_basic_pay_SAL894_PRDM (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1616 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
1617 ,p_basic_pay OUT NOCOPY NUMBER
1618 ,p_prd OUT NOCOPY VARCHAR2
1619 ,p_PT_eff_start_date OUT NOCOPY DATE) IS
1620 --
1621 l_user_table_id NUMBER;
1622 l_pay_plan VARCHAR2(30);
1623 l_pc_pay_plan VARCHAR2(30);
1624 l_grade_or_level VARCHAR2(60);
1625 l_step_or_rate VARCHAR2(30);
1626 l_pay_basis VARCHAR2(30);
1627
1628 l_dummy_step VARCHAR2(30);
1629 l_dummy_date DATE;
1630 l_dummy_number NUMBER;
1631 l_max_cur_basic_pay NUMBER;
1632 l_max_old_basic_pay NUMBER;
1633 l_ret_basic_pay NUMBER;
1634 l_pos_basic_pay NUMBER;
1635 l_pos_step VARCHAR2(30);
1636 --
1637 l_PT_eff_start_date DATE;
1638 l_eff_start_date DATE;
1639 l_eff_end_date DATE;
1640
1641 l_basic_pay NUMBER;
1642 l_new_adj_basic_pay NUMBER;
1643 l_locality_adj NUMBER;
1644 --
1645 ----l_cur_pos_basic_pay NUMBER;
1646 --
1647 ----l_converted_increase NUMBER;
1648 --
1649 l_user_table_name pay_user_tables.user_table_name%type;
1650 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
1651 l_spl_basic_pay NUMBER;
1652 l_spl_adj_basic_pay NUMBER;
1653 l_spl_locality_adj NUMBER;
1654 l_A NUMBER;
1655 l_B NUMBER;
1656
1657 l_proc varchar2(30) := 'SAL894_PRDM';
1658
1659 l_new_std_relative_rate NUMBER;
1660
1661 BEGIN
1662 hr_utility.set_location('Entering ' || l_proc,5);
1663 -- First work out what pay table data to use
1664 --
1665 IF p_retained_grade.grade_or_level is NULL THEN
1666 hr_utility.set_location('Entering ..No Retained Grade Info.. ' || l_proc,10);
1667 l_user_table_id := p_pay_calc_data.user_table_id;
1668 l_pay_plan := p_pay_calc_data.pay_plan;
1669 l_grade_or_level := p_pay_calc_data.grade_or_level;
1670 l_step_or_rate := p_pay_calc_data.step_or_rate;
1671 l_pay_basis := p_pay_calc_data.pay_basis;
1672 --
1673 ELSE
1674 hr_utility.set_location('Entering ..Retained Grade Info.. ' || l_proc,10);
1675 l_user_table_id := p_retained_grade.user_table_id;
1676 l_pay_plan := p_retained_grade.pay_plan;
1677 l_grade_or_level := p_retained_grade.grade_or_level;
1678 l_step_or_rate := p_retained_grade.step_or_rate;
1679 l_pay_basis := p_retained_grade.pay_basis;
1680 --
1681 END IF;
1682 ---------Pay Plan should be always 'GS'
1683 ---------l_pay_plan := 'GS';
1684 l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
1685 l_adjustment_percentage := ghr_pay_calc.get_lpa_percentage
1686 (p_pay_calc_data.duty_station_id
1687 ,p_pay_calc_data.effective_date);
1688 --
1689 IF l_pay_plan = 'GS' THEN
1690
1691 hr_utility.set_location('Calculating for GS Plan ..Basic Pay ' || l_proc,15);
1692 hr_utility.set_location('user_table_id..' || to_char(l_user_table_id) ,15);
1693
1694 ghr_pay_calc.get_pay_table_value(l_user_table_id
1695 ,l_pay_plan
1696 ,l_grade_or_level
1697 ,l_step_or_rate
1698 ,p_pay_calc_data.effective_date
1699 ,l_basic_pay
1700 ,l_PT_eff_start_date
1701 ,l_dummy_date);
1702
1703 hr_utility.set_location('Calculating for GS Plan ..Locality Pay ' || l_proc,20);
1704
1705 ghr_pay_calc.get_locality_adj_894_PRDM_GS
1706 (p_user_table_id => l_user_table_id
1707 ,p_pay_plan => l_pay_plan
1708 ,p_grade_or_level => l_grade_or_level
1709 ,p_step_or_rate => l_step_or_rate
1710 ,p_effective_date => p_pay_calc_data.effective_date
1711 ,p_cur_adj_basic_pay => p_pay_calc_data.current_adj_basic_pay
1712 ,p_new_basic_pay => l_basic_pay
1713 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1714 ,p_new_locality_adj => l_locality_adj );
1715
1716 p_basic_pay := nvl(l_basic_pay,0);
1717 p_PT_eff_start_date := l_PT_eff_start_date;
1718
1719 IF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
1720 l_spl_basic_pay := ghr_pay_calc.get_standard_pay_table_value
1721 (l_pay_plan
1722 ,l_grade_or_level
1723 ,l_step_or_rate
1724 ,p_pay_calc_data.effective_date);
1725 l_spl_locality_adj := ROUND(l_spl_basic_pay * (NVL(l_adjustment_percentage,0)/100),0);
1726 l_spl_adj_basic_pay := (l_spl_basic_pay + l_spl_locality_adj);
1727 ELSE
1728 l_spl_basic_pay := nvl(l_basic_pay,0);
1729 l_A := ghr_pay_calc.get_standard_pay_table_value
1730 ('GS'
1731 ,l_grade_or_level
1732 ,l_step_or_rate
1733 ,p_pay_calc_data.effective_date);
1734 l_B := NVL(l_A,0) + ROUND(l_A * (NVL(l_adjustment_percentage,0)/100),0);
1735 IF (l_spl_basic_pay > l_B ) OR (l_spl_basic_pay = l_B ) THEN
1736 l_spl_adj_basic_pay := l_spl_basic_pay;
1737 ELSE
1738 l_spl_adj_basic_pay := l_spl_basic_pay + (l_B - l_spl_basic_pay);
1739 END IF;
1740 END IF;
1741
1742 -- Now do the comparison!
1743
1744 IF (l_basic_pay > l_new_adj_basic_pay) OR
1745 (l_spl_adj_basic_pay > l_new_adj_basic_pay) THEN
1746 IF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
1747 p_prd := 0;
1748 ELSE
1749 p_prd := 6;
1750 END IF;
1751 END IF;
1752
1753 ELSIF l_pay_plan = 'GM' THEN
1754
1755 hr_utility.set_location('Calculating for GM Plan ..Basic Pay ' || l_proc,25);
1756
1757 get_basic_pay_SAL894_6step(p_pay_calc_data
1758 ,p_retained_grade
1759 ,'POSITION'
1760 ,l_basic_pay
1761 ,l_PT_eff_start_date
1762 ,l_dummy_number);
1763
1764 hr_utility.set_location('Calculating for GM Plan ..Locality Pay ' || l_proc,20);
1765
1766 ghr_pay_calc.get_locality_adj_894_PRDM_GM
1767 (p_pay_calc_data => p_pay_calc_data
1768 ,p_retained_grade => p_retained_grade
1769 ,p_new_std_relative_rate => l_new_std_relative_rate
1770 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1771 ,p_new_locality_adj => l_locality_adj);
1772
1773
1774 l_new_std_relative_rate := l_new_std_relative_rate +
1775 ROUND(l_new_std_relative_rate *
1776 (NVL(l_adjustment_percentage,0)/100),0);
1777
1778 IF l_new_std_relative_rate > l_new_adj_basic_pay THEN
1779 p_prd := 0;
1780 l_new_adj_basic_pay := l_new_std_relative_rate;
1781 --------- l_locality_adj := l_new_adj_basic_pay - l_basic_pay;
1782 ELSIF (l_basic_pay > l_new_adj_basic_pay) AND
1783 (l_user_table_name <> ghr_pay_calc.l_standard_table_name) THEN
1784 p_prd := 6;
1785 l_new_adj_basic_pay := l_basic_pay;
1786 ------------- l_locality_adj := 0;
1787 END IF;
1788 p_basic_pay := nvl(l_basic_pay,0);
1789 p_PT_eff_start_date := l_PT_eff_start_date;
1790
1791 END IF;
1792 hr_utility.set_location('Leaving .. ' || l_proc,5);
1793
1794 EXCEPTION
1795 WHEN others THEN
1796 -- Reset IN OUT parameters and set OUT parameters
1797
1798 p_basic_pay := NULL;
1799 p_prd := NULL;
1800 p_PT_eff_start_date := NULL;
1801
1802 hr_utility.set_location('Leaving .. ' || l_proc,6);
1803 RAISE;
1804 END get_basic_pay_SAL894_PRDM;
1805
1806 ----------------------------------------------------------------------------------------
1807 -- --
1808 --------------------------- <get_basic_pay> --------------------------------------------
1809 -- --
1810 ----------------------------------------------------------------------------------------
1811 PROCEDURE get_basic_pay (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1812 ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
1813 ,p_retained_grade IN OUT NOCOPY ghr_pay_calc.retained_grade_rec_type) IS
1814 --
1815 -- This is the main bit of all the pay calc -- how we get the basic pay , everything else kinda
1816 -- falls out from that.
1817 -- Basically if we can calulate it we will otherwise raise ...
1818 -- Please note the return value will be in the given pay basis
1819 l_dummy_date DATE;
1820 l_dummy_number NUMBER;
1821 l_pay_plan VARCHAR2(30);
1822 l_pay_basis VARCHAR2(30);
1823 l_proc VARCHAR2(20) := 'get_basic_pay';
1824
1825 --1360547 Fix start
1826 cursor cfws is
1827 select 1 from ghr_pay_plans
1828 where EQUIVALENT_PAY_PLAN = 'FW'
1829 and PAY_PLAN = l_pay_plan;
1830
1831 --5470182
1832 cursor ces is
1833 select 1 from ghr_pay_plans
1834 where EQUIVALENT_PAY_PLAN = 'ES'
1835 and PAY_PLAN = l_pay_plan;
1836
1837 l_fws_flag VARCHAR2(5);
1838 l_es_flag VARCHAR2(5);
1839 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
1840 l_ig_pay_amount NUMBER;--Bug# 7557159
1841 --1360547 Fix
1842 l_open_range_basic_pay NUMBER;
1843
1844 l_890_current_adj_basic_pay NUMBER;
1845 BEGIN
1846
1847 l_retained_grade := p_retained_grade ;
1848 l_fws_flag := 'FALSE'; ---Bug 1360547
1849 hr_utility.set_location('Entering ' || l_proc,5);
1850 -- get retained grade record if there is one, there MUST be one for 'A','B','E','F','U','V'
1851 -- and maybe one for 'M'
1852 IF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V','M') THEN
1853 p_retained_grade := get_retained_grade_details (p_pay_calc_data.person_id
1854 ,p_pay_calc_data.effective_date
1855 ,p_pay_calc_data.pa_request_id);
1856 l_pay_plan := p_retained_grade.pay_plan;
1857 l_pay_basis := p_retained_grade.pay_basis;
1858 if p_pay_calc_data.noa_code = '740' then
1859 p_retained_grade.temp_step := NULL;
1860 end if;
1861 if p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') AND
1862 p_retained_grade.temp_step is not null AND
1863 p_pay_calc_data.noa_code <> '740' then
1864 l_pay_plan := p_pay_calc_data.pay_plan;
1865 l_pay_basis := p_pay_calc_data.pay_basis;
1866 end if;
1867 ELSE
1868 l_pay_plan := p_pay_calc_data.pay_plan;
1869 l_pay_basis := p_pay_calc_data.pay_basis;
1870 END IF;
1871
1872 ---Open Pay Range Basic pay assignment from the in to out record.
1873 if p_pay_calc_data.open_range_out_basic_pay is not null then
1874 l_open_range_basic_pay := p_pay_calc_data.open_range_out_basic_pay;
1875 p_pay_calc_out_data.basic_pay := l_open_range_basic_pay;
1876 end if;
1877 ---Open Pay Range Code changes.
1878 --
1879 --1360547 Fix start
1880 --
1881 for cfws_rec in cfws
1882 loop
1883 l_fws_flag := 'TRUE';
1884 exit;
1885 end loop;
1886 --1360547 Fix
1887
1888 --5470182 Fix start
1889 --
1890 l_es_flag := 'FALSE';
1891 for ces_rec in ces
1892 loop
1893 l_es_flag := 'TRUE';
1894 exit;
1895 end loop;
1896 --5470182 Fix
1897
1898
1899
1900 -- Can not do pay calcs for dual actions -- 17/DEC/97 Can now the main pay calc
1901 -- routine should have set the the second noa to null to pass through this!
1902 --
1903
1904 IF p_pay_calc_data.open_range_out_basic_pay IS NULL THEN
1905 IF p_pay_calc_data.second_noa_code IS NULL THEN
1906 -- Must have all the data to be here as the validation has checked it
1907 IF p_pay_calc_data.pay_basis IN ('PA','PH','BW')
1908 AND l_pay_basis IN ('PA','PH','BW') THEN
1909 --
1910 IF l_pay_plan NOT IN ('SL','ST', 'SR')
1911 AND SUBSTR(l_pay_plan,1,1) <> 'D' THEN
1912 --
1913 IF p_pay_calc_data.noa_family_code IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
1914 ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY') THEN
1915 --
1916 --Bug# 5132113 added pay plan GR
1917 IF l_pay_plan NOT IN ('GM','GH','FM','GR') THEN
1918 --
1919 IF p_pay_calc_data.pay_rate_determinant IN ('0','5','6','7') THEN
1920 -- This is the easy one! refered to as MAIN_pos in the design doc
1921 -- all you have to do is a striaght look up on the user table given, using step,pay_plan,and
1922 -- grade given at the effective date also given
1923 -- Note: need for any conversion since it must already be in the given pay basis
1924 ghr_pay_calc.get_pay_table_value(p_pay_calc_data.user_table_id
1925 ,p_pay_calc_data.pay_plan
1926 ,p_pay_calc_data.grade_or_level
1927 ,p_pay_calc_data.step_or_rate
1928 ,p_pay_calc_data.effective_date
1929 ,p_pay_calc_out_data.basic_pay
1930 ,l_dummy_date
1931 ,l_dummy_date);
1932 ELSIF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
1933 get_basic_pay_MAIN_per(p_pay_calc_data
1934 ,p_retained_grade
1935 ,p_pay_calc_out_data.basic_pay
1936 ,l_dummy_date);
1937 --Begin Bug# 7557159
1938 ELSIF p_pay_calc_data.pay_rate_determinant IN ('S') AND l_pay_plan IN ('IG') THEN
1939 l_ig_pay_amount := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => l_pay_plan
1940 ,p_grade_or_level => '00'
1941 ,p_step_or_rate => '00'
1942 ,p_effective_date => p_pay_calc_data.effective_date);
1943 IF l_ig_pay_amount > p_pay_calc_data.current_basic_pay THEN
1944 p_pay_calc_out_data.out_pay_rate_determinant := '0';
1945 p_pay_calc_out_data.basic_pay := l_ig_pay_amount;
1946 ELSE
1947 p_pay_calc_out_data.basic_pay := p_pay_calc_data.current_basic_pay;
1948 END IF;
1949 --Begin Bug# 7557159
1950 ELSE
1951 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
1952 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
1953 raise ghr_pay_calc.unable_to_calculate;
1954 END IF;
1955 --
1956 ELSE
1957 hr_utility.set_message(8301, 'GHR_38260_NO_CALC_PAY_PLAN');
1958 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
1959 raise ghr_pay_calc.unable_to_calculate;
1960 END IF;
1961 ELSIF SUBSTR(p_pay_calc_data.noa_family_code ,1,8) = 'GHR_SAL_' THEN
1962 -- For salary change family we need to further investigate the noac to determine
1963 -- how to do pay
1964 IF p_pay_calc_data.noa_code = '894' THEN
1965 IF (p_pay_calc_data.effective_date >= to_date('2007/01/07','YYYY/MM/DD') AND
1966 nvl(p_pay_calc_data.first_action_la_code1,'XXX') <> 'VGR') OR
1967 p_pay_calc_data.effective_date < to_date('2007/01/07','YYYY/MM/DD') THEN
1968 -- Bug! 658164 Since we were not able to calculate pay for PRD's 2,3,4,J,K,M,R,3,U,V
1969 -- in Appointment, don't attempt to do it in 894!!
1970 -- How to calculate for this NOAC basically depends on Pay Plan AND PRD:
1971 --Bug# 5132113 added pay plan GP
1972 IF ( l_pay_plan IN ('GS','GL','GG','IE','GP')
1973 AND p_pay_calc_data.pay_rate_determinant IN ('0','6') )
1974 OR ( (l_pay_plan IN ('EX') or l_fws_flag = 'TRUE')
1975 AND p_pay_calc_data.pay_rate_determinant IN ('0') )
1976 OR ( l_pay_plan IN ('ES','EP','CA','FO','FP','FE','AL','AA')
1977 AND p_pay_calc_data.pay_rate_determinant IN ('0','6') )
1978 OR l_pay_plan IN ('IG') THEN --Bug# 7557159
1979 --
1980 -- This is what we refer to as MAIN_pos
1981 --
1982 ghr_pay_calc.get_pay_table_value(p_pay_calc_data.user_table_id
1983 ,p_pay_calc_data.pay_plan
1984 ,p_pay_calc_data.grade_or_level
1985 ,p_pay_calc_data.step_or_rate
1986 ,p_pay_calc_data.effective_date
1987 ,p_pay_calc_out_data.basic_pay
1988 ,p_pay_calc_out_data.PT_eff_start_date
1989 ,l_dummy_date);
1990 --
1991 ELSIF ( (l_pay_plan IN ('GS','GL','GG','ES','EP','CA','FO','FP','FE','AL','AA','IE')
1992 or l_fws_flag = 'TRUE')
1993 AND p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') ) THEN
1994 --
1995 get_basic_pay_MAIN_per(p_pay_calc_data
1996 ,p_retained_grade
1997 ,p_pay_calc_out_data.basic_pay
1998 ,p_pay_calc_out_data.PT_eff_start_date);
1999 --
2000 ELSIF ( l_pay_plan IN ('GM','GH','GR')
2001 AND p_pay_calc_data.pay_rate_determinant IN ('0','6') ) THEN
2002 --
2003 get_basic_pay_SAL894_6step(p_pay_calc_data
2004 ,p_retained_grade
2005 ,'POSITION'
2006 ,p_pay_calc_out_data.basic_pay
2007 ,p_pay_calc_out_data.PT_eff_start_date
2008 ,l_dummy_number);
2009 --
2010 ELSIF ( l_pay_plan IN ('GM','GH')
2011 AND p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') ) THEN
2012 get_basic_pay_SAL894_6step(p_pay_calc_data
2013 ,p_retained_grade
2014 ,'PERSON'
2015 ,p_pay_calc_out_data.basic_pay
2016 ,p_pay_calc_out_data.PT_eff_start_date
2017 ,l_dummy_number);
2018 --
2019 ELSIF ( (l_pay_plan IN ('GS','GL','GM','GG','GH','ES','EP','FO','FP','FE','IE','AL','AA','CA')
2020 or l_fws_flag = 'TRUE')
2021 AND p_pay_calc_data.pay_rate_determinant IN ('J','K','R','3','S') ) THEN
2022 get_basic_pay_SAL894_50(p_pay_calc_data
2023 ,p_retained_grade
2024 ,'POSITION'
2025 ,p_pay_calc_out_data.basic_pay
2026 ,p_pay_calc_out_data.out_step_or_rate
2027 ,p_pay_calc_out_data.out_pay_rate_determinant
2028 ,p_pay_calc_out_data.PT_eff_start_date);
2029 --
2030 ELSIF ( (l_pay_plan IN ('GS','GL','GM','GG','GH','ES','EP','FO','FP','FE','IE','AL','AA','CA')
2031 or l_fws_flag = 'TRUE')
2032 AND p_pay_calc_data.pay_rate_determinant IN ('U','V') ) THEN
2033 get_basic_pay_SAL894_50(p_pay_calc_data
2034 ,p_retained_grade
2035 ,'PERSON'
2036 ,p_pay_calc_out_data.basic_pay
2037 ,p_pay_calc_out_data.out_step_or_rate
2038 ,p_pay_calc_out_data.out_pay_rate_determinant
2039 ,p_pay_calc_out_data.PT_eff_start_date);
2040
2041 ELSIF ( l_pay_plan IN ('GS','GL','GM','GG','GH')
2042 AND p_pay_calc_data.pay_rate_determinant IN ('2','4') ) THEN
2043 get_basic_pay_SAL894_100(p_pay_calc_data
2044 ,p_pay_calc_out_data.basic_pay
2045 ,p_pay_calc_out_data.PT_eff_start_date);
2046 --AVR
2047 ELSIF ( l_pay_plan IN ('GS','GL','GM')
2048 AND p_pay_calc_data.pay_rate_determinant = 'M' ) THEN
2049
2050 hr_utility.set_location('Calling ..SAL894_PRDM.. ' || l_proc,15);
2051 get_basic_pay_SAL894_PRDM (p_pay_calc_data
2052 ,p_retained_grade
2053 ,p_pay_calc_out_data.basic_pay
2054 ,p_pay_calc_out_data.out_pay_rate_determinant
2055 ,p_pay_calc_out_data.PT_eff_start_date );
2056 hr_utility.set_location('Called ..SAL894_PRDM.. ' || l_proc,25);
2057
2058 --AVR
2059
2060
2061 --Begin Bug# 7557159
2062 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2063 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2064 raise ghr_pay_calc.open_pay_range_mesg;
2065 --End Bug# 7557159
2066 ELSE
2067 hr_utility.set_message(8301, 'GHR_38391_NO_CALC_PAY_PLAN_PRD');
2068 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
2069 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
2070 raise ghr_pay_calc.unable_to_calculate;
2071 END IF;
2072 --
2073 ELSIF (p_pay_calc_data.effective_date >= to_date('2007/01/07','YYYY/MM/DD') AND
2074 p_pay_calc_data.first_action_la_code1 = 'VGR' ) THEN
2075 ---GPPA Update 46 894 NOAC will behave like 895 with VGR after 7-JAN-2007 onwards.
2076 p_pay_calc_out_data.basic_pay := p_pay_calc_data.current_basic_pay;
2077 END IF;
2078 ELSIF p_pay_calc_data.noa_code = '895' THEN
2079 -- Easy this one I like this, no change in basic pay!!
2080 p_pay_calc_out_data.basic_pay := p_pay_calc_data.current_basic_pay;
2081 --
2082
2083 --Bug# 5132113 added pay plan GR
2084 ELSIF l_pay_plan NOT IN ('GM','GH','GR') THEN
2085 IF p_pay_calc_data.noa_code = '891' AND
2086 p_pay_calc_data.effective_date < to_date('2007/01/07','YYYY/MM/DD')THEN --Bug# 5482191
2087 hr_utility.set_message(8301, 'GHR_38248_INV_PAY_PLAN_891');
2088 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
2089 raise ghr_pay_calc.pay_calc_message;
2090 --
2091 ELSIF p_pay_calc_data.noa_code IN ('867','892','893') THEN
2092 --
2093 IF p_pay_calc_data.pay_rate_determinant IN ('0','6','M') THEN
2094 get_basic_pay_SALWGI_pos(p_pay_calc_data
2095 ,p_pay_calc_out_data.basic_pay
2096 ,p_pay_calc_out_data.out_step_or_rate);
2097 ELSIF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
2098 get_basic_pay_SALWGI_per(p_pay_calc_data
2099 ,p_retained_grade
2100 ,p_pay_calc_out_data.basic_pay
2101 ,p_pay_calc_out_data.out_step_or_rate);
2102 --Begin Bug# 7557159
2103 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2104 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2105 raise ghr_pay_calc.open_pay_range_mesg;
2106 --End Bug# 7557159
2107 ELSE
2108 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2109 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
2110 raise ghr_pay_calc.unable_to_calculate;
2111 END IF;
2112
2113 ELSIF (p_pay_calc_data.noa_code IN ('890') AND (l_fws_flag = 'TRUE' or l_es_flag = 'TRUE')) THEN
2114 --
2115 IF p_pay_calc_data.pay_rate_determinant IN ('0','6') THEN
2116 --5470182 added NOA code '890' mass salary actions for 6step calculation
2117 IF l_es_flag = 'TRUE' THEN
2118
2119 get_basic_pay_SAL890_6step(p_pay_calc_data =>p_pay_calc_data
2120 ,p_retained_grade =>p_retained_grade
2121 ,p_pay_table_data =>'POSITION'
2122 ,p_basic_pay =>p_pay_calc_out_data.basic_pay
2123 );
2124
2125 ELSE
2126 ---- Bug 5913318
2127 ---- At this stage when a mixed pay basis condition araises no variable for from pay basis to convert the
2128 ---- current_basic_pay. So evolving a hard coded logic below - Not correct but due to time constraint...
2129
2130 --BUG 6211029 Modified the basic pay to Adjusted Basic Pay and added p_pay_calc_data.step_or_rate to the
2131 -- call of get_890_pay_table_value
2132
2133 --BUG 6211029 removed the below call to get_890_pay_table_value as no need of defaulting the step or rate
2134 -- need to be calculated based on the entered step or rate
2135
2136 /* IF p_pay_calc_data.pay_basis = 'PH' AND p_pay_calc_data.current_adj_basic_pay > 100 THEN
2137 l_890_current_adj_basic_pay := ghr_pay_calc.convert_amount(p_pay_calc_data.current_adj_basic_pay ,'PA','PH');
2138 ELSE
2139 l_890_current_adj_basic_pay := p_pay_calc_data.current_adj_basic_pay;
2140 END IF;
2141
2142
2143 get_890_pay_table_value(p_pay_calc_data.user_table_id
2144 ,p_pay_calc_data.pay_plan
2145 ,p_pay_calc_data.grade_or_level
2146 ,p_pay_calc_data.effective_date
2147 ,nvl(l_890_current_adj_basic_pay, p_pay_calc_data.current_adj_basic_pay)
2148 ,p_pay_calc_data.step_or_rate
2149 ,p_pay_calc_out_data.out_step_or_rate
2150 ,p_pay_calc_out_data.basic_pay
2151 ,l_dummy_date
2152 ,l_dummy_date); */
2153
2154 ghr_pay_calc.get_pay_table_value(p_pay_calc_data.user_table_id
2155 ,p_pay_calc_data.pay_plan
2156 ,p_pay_calc_data.grade_or_level
2157 ,p_pay_calc_data.step_or_rate
2158 ,p_pay_calc_data.effective_date
2159 ,p_pay_calc_out_data.basic_pay
2160 ,l_dummy_date
2161 ,l_dummy_date);
2162
2163
2164 END IF;
2165 ELSIF p_pay_calc_data.pay_rate_determinant IN ('J','K','R','3','S') THEN
2166 ghr_pay_calc.get_pay_table_value (p_user_table_id => p_pay_calc_data.user_table_id
2167 ,p_pay_plan => p_pay_calc_data.pay_plan
2168 ,p_grade_or_level => p_pay_calc_data.grade_or_level
2169 ,p_step_or_rate => '05'
2170 ,p_effective_date => p_pay_calc_data.effective_date
2171 ,p_PT_value => p_pay_calc_out_data.basic_pay
2172 ,p_PT_eff_start_date => l_dummy_date
2173 ,p_PT_eff_end_date => l_dummy_date);
2174 p_pay_calc_out_data.out_step_or_rate := '05';
2175 p_pay_calc_out_data.out_pay_rate_determinant := '0';
2176 ELSIF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
2177 get_basic_pay_MAIN_per(p_pay_calc_data
2178 ,p_retained_grade
2179 ,p_pay_calc_out_data.basic_pay
2180 ,l_dummy_date);
2181 --
2182 --Begin Bug# 7557159
2183 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2184 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2185 raise ghr_pay_calc.open_pay_range_mesg;
2186 --End Bug# 7557159
2187 ELSE
2188 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2189 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
2190 raise ghr_pay_calc.unable_to_calculate;
2191 END IF;
2192
2193 ELSE -- All other NoaC's for the salary change family (not GM,GH)
2194
2195 IF p_pay_calc_data.pay_rate_determinant IN ('0','6') THEN
2196 -- This is the easy one! refered to as MAIN_pos in the design doc
2197 -- all you have to do is a striaght look up on the user table given, using step,pay_plan,and
2198 -- grade given at the effective date also given
2199 -- Note: need for any conversion since it must already be in the given pay basis
2200 ghr_pay_calc.get_pay_table_value(p_pay_calc_data.user_table_id
2201 ,p_pay_calc_data.pay_plan
2202 ,p_pay_calc_data.grade_or_level
2203 ,p_pay_calc_data.step_or_rate
2204 ,p_pay_calc_data.effective_date
2205 ,p_pay_calc_out_data.basic_pay
2206 ,l_dummy_date
2207 ,l_dummy_date);
2208 ELSIF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
2209 get_basic_pay_MAIN_per(p_pay_calc_data
2210 ,p_retained_grade
2211 ,p_pay_calc_out_data.basic_pay
2212 ,l_dummy_date);
2213 --
2214 --Begin Bug# 7557159
2215 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2216 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2217 raise ghr_pay_calc.open_pay_range_mesg;
2218 --End Bug# 7557159
2219 ELSE
2220 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2221 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
2222 raise ghr_pay_calc.unable_to_calculate;
2223 END IF;
2224
2225 --Pradeep commented for Title 38 Changes
2226 -- IF NOAC = 850 or 855 open up pay fields as well as doing the calc:
2227 /*
2228 IF p_pay_calc_data.noa_code IN ('850','855') THEN
2229 p_pay_calc_out_data.open_pay_fields := TRUE;
2230 END IF;
2231 */
2232 --
2233 --
2234 END IF; -- end of noac check inside salary change family
2235 --
2236 ELSE -- Not 894, not 895 and must be GM, GH pay plans
2237 --
2238 --Bug# 5132113 added pay plan GR condition
2239 /*IF l_pay_plan in('GR') THEN
2240 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2241 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
2242 raise ghr_pay_calc.unable_to_calculate;
2243 ELS*/ --Bug# 6342011 Commented
2244 IF p_pay_calc_data.noa_code IN ('891','892') THEN
2245 IF p_pay_calc_data.pay_rate_determinant IN ('0','6') THEN
2246 get_basic_pay_SAL891_pos(p_pay_calc_data
2247 ,p_pay_calc_out_data.basic_pay
2248 ,p_pay_calc_out_data.out_step_or_rate);
2249 ELSIF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
2250 get_basic_pay_SAL891_per(p_pay_calc_data
2251 ,p_retained_grade
2252 ,p_pay_calc_out_data.basic_pay
2253 ,p_pay_calc_out_data.out_step_or_rate);
2254 --Begin Bug# 7557159
2255 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2256 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2257 raise ghr_pay_calc.open_pay_range_mesg;
2258 --End Bug# 7557159
2259 ELSE
2260 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2261 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
2262 raise ghr_pay_calc.unable_to_calculate;
2263 END IF;
2264 --Begin Bug 5661441 AFHR change
2265
2266 ELSIF p_pay_calc_data.noa_code IN ('893') THEN
2267 IF p_pay_calc_data.effective_date < to_date('2007/01/07','YYYY/MM/DD')THEN
2268 hr_utility.set_message(8301, 'GHR_INV_PAY_PLAN_893');
2269 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
2270 hr_utility.set_message_token('NOAC',p_pay_calc_data.noa_code);
2271 raise ghr_pay_calc.pay_calc_message;
2272 ELSE
2273 IF p_pay_calc_data.pay_rate_determinant IN ('0','6') THEN
2274 get_basic_pay_SAL891_pos(p_pay_calc_data
2275 ,p_pay_calc_out_data.basic_pay
2276 ,p_pay_calc_out_data.out_step_or_rate);
2277 ELSIF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
2278 get_basic_pay_SAL891_per(p_pay_calc_data
2279 ,p_retained_grade
2280 ,p_pay_calc_out_data.basic_pay
2281 ,p_pay_calc_out_data.out_step_or_rate);
2282 --Begin Bug# 7557159
2283 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2284 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2285 raise ghr_pay_calc.open_pay_range_mesg;
2286 --End Bug# 7557159
2287 ELSE
2288 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2289 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
2290 raise ghr_pay_calc.unable_to_calculate;
2291 END IF;
2292 END IF;
2293
2294 --end Bug 5661441 AFHR change
2295 ELSIF p_pay_calc_data.noa_code IN ('867') THEN --AFHR change 893 removed
2296 hr_utility.set_message(8301, 'GHR_INV_PAY_PLAN_893');
2297 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
2298 hr_utility.set_message_token('NOAC',p_pay_calc_data.noa_code);
2299 raise ghr_pay_calc.pay_calc_message;
2300 --
2301 --Begin Bug# 7557159
2302 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2303 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2304 raise ghr_pay_calc.open_pay_range_mesg;
2305 --End Bug# 7557159
2306 ELSE
2307 hr_utility.set_message(8301, 'GHR_38260_NO_CALC_PAY_PLAN');
2308 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
2309 raise ghr_pay_calc.unable_to_calculate;
2310 END IF;
2311 --
2312 END IF;
2313 --Begin Bug# 7557159
2314 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2315 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2316 raise ghr_pay_calc.open_pay_range_mesg;
2317 --End Bug# 7557159
2318 ELSE
2319 hr_utility.set_message(8301, 'GHR_38261_NO_CALC_FAMILY');
2320 hr_utility.set_message_token('FAMILY',p_pay_calc_data.noa_family_code);
2321 raise ghr_pay_calc.unable_to_calculate;
2322 END IF;
2323 --
2324 --Begin Bug# 7557159
2325 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2326 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2327 raise ghr_pay_calc.open_pay_range_mesg;
2328 --End Bug# 7557159
2329 ELSE
2330 hr_utility.set_message(8301, 'GHR_38260_NO_CALC_PAY_PLAN');
2331 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
2332 raise ghr_pay_calc.unable_to_calculate;
2333 END IF;
2334 --Begin Bug# 7557159
2335 ELSIF p_pay_calc_data.pay_rate_determinant = 'D' THEN
2336 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2337 raise ghr_pay_calc.open_pay_range_mesg;
2338 --End Bug# 7557159
2339 ELSE
2340 hr_utility.set_message(8301, 'GHR_38262_NO_CALC_PAY_BASIS');
2341 -- It could be either the position pay basis or the retained pay basis as to why we couldn't
2342 -- calculate
2343 IF p_pay_calc_data.pay_basis NOT IN ('PA','PH','BW') THEN
2344 hr_utility.set_message_token('PAY_BASIS',
2345 ghr_pa_requests_pkg.get_lookup_meaning(800,'GHR_US_PAY_BASIS'
2346 ,p_pay_calc_data.pay_basis));
2347 ELSE
2348 hr_utility.set_message_token('PAY_BASIS',
2349 ghr_pa_requests_pkg.get_lookup_meaning(800,'GHR_US_PAY_BASIS'
2350 ,l_pay_basis));
2351 END IF;
2352 raise ghr_pay_calc.unable_to_calculate;
2353 END IF;
2354 ELSE
2355 hr_utility.set_message(8301, 'GHR_38263_NO_CALC_DUAL_ACTION');
2356 raise ghr_pay_calc.unable_to_calculate;
2357 END IF;
2358 END IF;
2359
2360 EXCEPTION
2361 WHEN others THEN
2362 -- Reset IN OUT parameters and set OUT parameters
2363
2364 p_pay_calc_out_data := NULL;
2365 p_retained_grade := l_retained_grade;
2366
2367 RAISE;
2368 hr_utility.set_location('Leaving ' || l_proc,8);
2369 END get_basic_pay;
2370
2371 -- Bug#5114467 Calling proc for Calculating basic pay, locality rate and
2372 -- adjusted basic pay for employee in 'GM' pay plan and NOA 894 AC
2373
2374 PROCEDURE get_894_GM_sp_basic_pay(p_grade_or_level IN VARCHAR2
2375 ,p_effective_date IN DATE
2376 ,p_user_table_id IN pay_user_tables.user_table_id%TYPE
2377 ,p_default_table_id IN NUMBER
2378 ,p_curr_basic_pay IN NUMBER
2379 ,p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
2380 ,p_new_basic_pay OUT NOCOPY NUMBER
2381 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
2382 ,p_new_locality_adj OUT NOCOPY NUMBER
2383 ,p_new_special_rate OUT NOCOPY NUMBER
2384 ) IS
2385
2386 l_pay_plan VARCHAR2(30);
2387 l_grade_or_level VARCHAR2(60);
2388 l_PT_eff_start_date DATE;
2389 l_eff_start_date DATE;
2390 l_eff_end_date DATE;
2391 --
2392 l_old_basic_pay NUMBER;
2393 l_min_old_basic_pay NUMBER;
2394 l_max_old_basic_pay NUMBER;
2395 --
2396 l_new_basic_pay NUMBER;
2397 l_cur_basic_pay NUMBER;
2398 l_min_cur_basic_pay NUMBER;
2399 l_max_cur_basic_pay NUMBER;
2400 l_min_sr_basic_pay NUMBER;
2401 l_max_sr_basic_pay NUMBER;
2402
2403 l_new_locality_adj NUMBER;
2404 l_new_adj_basic_pay NUMBER;
2405
2406 l_temp_basic_pay NUMBER;
2407 l_temp2_basic_pay NUMBER;
2408 l_new_special_rate NUMBER;
2409
2410 l_new_locality_rate NUMBER;
2411 l_loc_amnt_or_supp_rate NUMBER;
2412
2413 l_new_loc_perc_factor NUMBER;
2414 l_user_table_id NUMBER;
2415 l_dummy_step NUMBER;
2416 l_grade NUMBER;
2417
2418 l_default_table_id NUMBER;
2419 l_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE;
2420
2421 BEGIN
2422 l_grade_or_level := p_grade_or_level;
2423 l_PT_eff_start_date := p_effective_date;
2424 l_pay_plan := 'GS';
2425 l_default_table_id := p_default_table_id;
2426 l_user_table_id := p_user_table_id;
2427 l_old_basic_pay := p_curr_basic_pay;
2428 l_duty_station_id := p_duty_station_id;
2429
2430 -- Start ->> Calculation Of New Basic Pay
2431 get_min_pay_table_value(l_default_table_id
2432 ,l_pay_plan
2433 ,l_grade_or_level
2434 ,l_PT_eff_start_date - 1
2435 ,l_dummy_step
2436 ,l_min_old_basic_pay
2437 ,l_eff_start_date
2438 ,l_eff_end_date);
2439
2440 get_max_pay_table_value(l_default_table_id
2441 ,l_pay_plan
2442 ,l_grade_or_level
2443 ,l_PT_eff_start_date - 1
2444 ,l_dummy_step
2445 ,l_max_old_basic_pay
2446 ,l_eff_start_date
2447 ,l_eff_end_date);
2448
2449 l_temp_basic_pay := l_old_basic_pay - l_min_old_basic_pay ;
2450 l_temp2_basic_pay := TRUNC( (l_temp_basic_pay/(l_max_old_basic_pay - l_min_old_basic_pay)),7);
2451
2452 get_min_pay_table_value(l_default_table_id
2453 ,l_pay_plan
2454 ,l_grade_or_level
2455 ,l_PT_eff_start_date
2456 ,l_dummy_step
2457 ,l_min_cur_basic_pay
2458 ,l_eff_start_date
2459 ,l_eff_end_date);
2460
2461 get_max_pay_table_value(l_default_table_id
2462 ,l_pay_plan
2463 ,l_grade_or_level
2464 ,l_PT_eff_start_date
2465 ,l_dummy_step
2466 ,l_max_cur_basic_pay
2467 ,l_eff_start_date
2468 ,l_eff_end_date);
2469
2470 l_new_basic_pay := l_min_cur_basic_pay + ROUND((l_temp2_basic_pay * (l_max_cur_basic_pay - l_min_cur_basic_pay)),0);
2471 -- End ->> Calculation Of New Basic Pay
2472
2473 -- Start ->> Calculation of Adjusted Basic Pay
2474 l_new_loc_perc_factor := (NVL(ghr_pay_calc.get_lpa_percentage(l_duty_station_id
2475 ,l_PT_eff_start_date
2476 )
2477 ,0
2478 )
2479 )/100;
2480 l_new_locality_adj := ROUND((l_new_basic_pay * l_new_loc_perc_factor),0);
2481 l_new_locality_rate := l_new_basic_pay + l_new_locality_adj;
2482 -- End ->> Calculation of Adjusted Basic Pay
2483
2484 -- Start ->> Calculation of special Rate amount
2485
2486 get_min_pay_table_value(l_user_table_id
2487 ,l_pay_plan
2488 ,l_grade_or_level
2489 ,l_PT_eff_start_date
2490 ,l_dummy_step
2491 ,l_min_sr_basic_pay
2492 ,l_eff_start_date
2493 ,l_eff_end_date);
2494
2495 get_max_pay_table_value(l_user_table_id
2496 ,l_pay_plan
2497 ,l_grade_or_level
2498 ,l_PT_eff_start_date
2499 ,l_dummy_step
2500 ,l_max_sr_basic_pay
2501 ,l_eff_start_date
2502 ,l_eff_end_date);
2503 l_new_special_rate := l_min_sr_basic_pay + ROUND((l_temp2_basic_pay*(l_max_sr_basic_pay - l_min_sr_basic_pay)),0);
2504 -- End ->> Calculation of special Rate amount
2505
2506 -- Start ->> Determining greater of locality rate and Special rate
2507 IF l_new_locality_rate > l_new_special_rate THEN
2508 l_new_adj_basic_pay := l_new_locality_rate;
2509 ELSE
2510 l_new_adj_basic_pay := l_new_special_rate;
2511 END IF;
2512
2513 l_loc_amnt_or_supp_rate := l_new_adj_basic_pay - l_new_basic_pay;
2514 -- End ->> Determining greater of locality rate and Special rate
2515
2516 -- Assigning the OUT parameters
2517 p_new_basic_pay := l_new_basic_pay;
2518 p_new_adj_basic_pay := l_new_adj_basic_pay;
2519 p_new_locality_adj := l_loc_amnt_or_supp_rate;
2520 p_new_special_rate := l_new_special_rate;
2521
2522
2523 END get_894_GM_sp_basic_pay;
2524
2525
2526 -- Bug#5114467 Calling proc for Calculating basic pay, locality rate and
2527 -- adjusted basic pay for WGI employee in 'GM' pay plan AC
2528
2529 PROCEDURE get_wgi_GM_sp_basic_pay(p_grade_or_level IN VARCHAR2
2530 ,p_effective_date IN DATE
2531 ,p_user_table_id IN pay_user_tables.user_table_id%TYPE
2532 ,p_default_table_id IN NUMBER
2533 ,p_curr_basic_pay IN NUMBER
2534 ,p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
2535 ,p_new_basic_pay OUT NOCOPY NUMBER
2536 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
2537 ,p_new_locality_adj OUT NOCOPY NUMBER
2538 ) IS
2539
2540 l_pay_plan VARCHAR2(30);
2541 l_grade_or_level VARCHAR2(60);
2542 l_PT_eff_start_date DATE;
2543 l_eff_start_date DATE;
2544 l_eff_end_date DATE;
2545 --
2546 l_new_basic_pay NUMBER;
2547 l_old_basic_pay NUMBER;
2548 l_min_old_basic_pay NUMBER;
2549 l_max_old_basic_pay NUMBER;
2550 --
2551 l_cur_basic_pay NUMBER;
2552 l_min_sp_basic_pay NUMBER;
2553 l_max_sp_basic_pay NUMBER;
2554
2555 l_new_locality_adj NUMBER;
2556 l_new_adj_basic_pay NUMBER;
2557
2558 l_temp_basic_pay NUMBER;
2559 l_temp2_basic_pay NUMBER;
2560
2561 l_new_locality_rate NUMBER;
2562 l_loc_amnt_or_supp_rate NUMBER;
2563 l_new_special_rate NUMBER;
2564
2565 l_new_loc_perc_factor NUMBER;
2566 l_user_table_id NUMBER;
2567 l_dummy_step NUMBER;
2568 l_grade NUMBER;
2569
2570 l_default_table_id NUMBER;
2571 l_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE;
2572
2573 BEGIN
2574 --5919700 assigning p_grade_or_level to l_grade_or_level
2575 l_grade_or_level := p_grade_or_level;
2576 l_PT_eff_start_date := p_effective_date;
2577 l_pay_plan := 'GS';
2578 l_default_table_id := p_default_table_id;
2579 l_user_table_id := p_user_table_id;
2580 l_old_basic_pay := p_curr_basic_pay;
2581 l_duty_station_id := p_duty_station_id;
2582
2583 -- Start ->> Calculation Of New Basic Pay
2584 get_min_pay_table_value(l_default_table_id
2585 ,'GS'
2586 ,l_grade_or_level
2587 ,l_PT_eff_start_date
2588 ,l_dummy_step
2589 ,l_min_old_basic_pay
2590 ,l_eff_start_date
2591 ,l_eff_end_date);
2592
2593 get_max_pay_table_value(l_default_table_id
2594 ,'GS'
2595 ,l_grade_or_level
2596 ,l_PT_eff_start_date
2597 ,l_dummy_step
2598 ,l_max_old_basic_pay
2599 ,l_eff_start_date
2600 ,l_eff_end_date);
2601
2602 l_new_basic_pay := l_old_basic_pay + (l_max_old_basic_pay - l_min_old_basic_pay)/9;
2603 -- End ->> Calculation Of New Basic Pay
2604
2605 -- Start ->> Calculation of special Rate amount
2606 l_new_special_rate := TRUNC(((l_new_basic_pay - l_min_old_basic_pay)/
2607 (l_max_old_basic_pay - l_min_old_basic_pay)
2608 )
2609 , 7
2610 );
2611 -- End ->> Calculation of special Rate amount
2612
2613 -- Start -->> Calculate relative rate in range for the special rate
2614 get_min_pay_table_value( l_user_table_id
2615 ,'GS'
2616 ,l_grade_or_level
2617 ,l_PT_eff_start_date
2618 ,l_dummy_step
2619 ,l_min_sp_basic_pay
2620 ,l_eff_start_date
2621 ,l_eff_end_date);
2622
2623 get_max_pay_table_value(l_user_table_id
2624 ,'GS'
2625 ,l_grade_or_level
2626 ,l_PT_eff_start_date
2627 ,l_dummy_step
2628 ,l_max_sp_basic_pay
2629 ,l_eff_start_date
2630 ,l_eff_end_date);
2631
2632 l_new_special_rate := l_min_sp_basic_pay + ROUND (((l_max_sp_basic_pay - l_min_sp_basic_pay) * l_new_special_rate),0);
2633 -- End -->> Calculate relative rate in range for the special rate
2634
2635 -- Start ->> Calculation of Locality Rate
2636 l_new_loc_perc_factor := (NVL(ghr_pay_calc.get_lpa_percentage(l_duty_station_id
2637 ,l_PT_eff_start_date
2638 )
2639 ,0
2640 )
2641 )/100;
2642 l_new_locality_adj := ROUND((l_new_basic_pay * l_new_loc_perc_factor),0);
2643 l_new_locality_rate := l_new_basic_pay + l_new_locality_adj;
2644 -- End ->> Calculation of Locality Rate
2645
2646 -- Start ->> Calculation of Adjusted Basic Pay
2647 IF l_new_special_rate > l_new_locality_rate THEN
2648 l_new_adj_basic_pay := l_new_special_rate;
2649 ELSE
2650 l_new_adj_basic_pay := l_new_locality_rate;
2651 END IF;
2652 l_loc_amnt_or_supp_rate := l_new_adj_basic_pay - l_new_basic_pay;
2653 -- End ->> Calculation of Adjusted Basic Pay
2654
2655 -- Assigning the OUT parameters
2656 p_new_basic_pay := l_new_basic_pay;
2657 p_new_adj_basic_pay := l_new_adj_basic_pay;
2658 p_new_locality_adj := l_loc_amnt_or_supp_rate;
2659
2660 END get_wgi_GM_sp_basic_pay;
2661 --
2662
2663 PROCEDURE get_basic_pay_SAL890_6step(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
2664 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
2665 ,p_pay_table_data IN VARCHAR2
2666 ,p_basic_pay OUT NOCOPY NUMBER
2667 ) IS
2668 l_user_table_id NUMBER;
2669 l_pay_plan VARCHAR2(30);
2670 l_grade_or_level VARCHAR2(60);
2671 l_step_or_rate VARCHAR2(30);
2672 l_pay_basis VARCHAR2(30);
2673 l_effective_date DATE;
2674
2675 l_curr_basic_pay NUMBER;
2676 l_old_rangeval_min NUMBER;
2677 l_old_rangeval_max NUMBER;
2678 l_calc_basic_pay NUMBER;
2679 l_new_rangeval_min NUMBER;
2680 l_new_rangeval_max NUMBER;
2681
2682
2683 stp_1 NUMBER;
2684 stp_2 NUMBER;
2685 stp_3 NUMBER;
2686 stp_4 NUMBER;
2687 stp_5 NUMBER;
2688 l_basic_pay NUMBER;
2689
2690 BEGIN
2691 -- First work out what pay table data to use
2692 --
2693
2694 IF p_pay_table_data = 'POSITION' THEN
2695 l_pay_plan := p_pay_calc_data.pay_plan;
2696 l_user_table_id := p_pay_calc_data.user_table_id;
2697 l_grade_or_level := p_pay_calc_data.grade_or_level;
2698 l_pay_basis := p_pay_calc_data.pay_basis;
2699 ELSE
2700 l_pay_plan := p_retained_grade.pay_plan;
2701 l_user_table_id := p_retained_grade.user_table_id;
2702 l_grade_or_level := p_retained_grade.grade_or_level;
2703 l_pay_basis := p_retained_grade.pay_basis;
2704 END IF;
2705
2706 l_curr_basic_pay := p_pay_calc_data.current_basic_pay;
2707 l_effective_date := NVL(p_pay_calc_data.effective_date,TRUNC(sysdate));
2708
2709 --fetching min and max range values on the preceding day of pay adjustment
2710 ghr_pay_calc.get_open_pay_table_values(p_user_table_id => l_user_table_id
2711 ,p_pay_plan => l_pay_plan
2712 ,p_grade_or_level => l_grade_or_level
2713 ,p_effective_date => l_effective_date-1
2714 ,p_row_high => l_old_rangeval_max
2715 ,p_row_low => l_old_rangeval_min);
2716 --fetching current min and max range values
2717 ghr_pay_calc.get_open_pay_table_values(p_user_table_id => l_user_table_id
2718 ,p_pay_plan => l_pay_plan
2719 ,p_grade_or_level => l_grade_or_level
2720 ,p_effective_date => l_effective_date
2721 ,p_row_high => l_new_rangeval_max
2722 ,p_row_low => l_new_rangeval_min);
2723 --Step 1
2724 stp_1 := l_curr_basic_pay - l_old_rangeval_min;
2725
2726 --Step 2
2727 stp_2 := l_old_rangeval_max - l_old_rangeval_min;
2728
2729 --Step 3
2730 stp_3 := TRUNC(stp_1/stp_2,7);
2731
2732 -- Step 4
2733 stp_4 := l_new_rangeval_max - l_new_rangeval_min;
2734
2735 --Step 5
2736 --stp_5 := CEIL(stp_3 * stp_4);
2737 stp_5 := ROUND(stp_3 * stp_4);
2738
2739 --Step 6
2740 l_calc_basic_pay := stp_5 + l_new_rangeval_min;
2741
2742 p_basic_pay := l_calc_basic_pay;
2743
2744 EXCEPTION
2745 WHEN others THEN
2746 -- Reset IN OUT parameters and set OUT parameters
2747 p_basic_pay := NULL;
2748
2749 RAISE;
2750
2751 END get_basic_pay_SAL890_6step;
2752
2753 END ghr_pc_basic_pay;
[Home] [Help]