[Home] [Help]
PACKAGE BODY: APPS.GHR_PAY_CALC
Source
1 PACKAGE BODY ghr_pay_calc AS
2 /* $Header: ghpaycal.pkb 120.108.12020000.4 2012/08/01 10:03:28 vmididho ship $ */
3
4
5 FUNCTION get_form_item_name
6 RETURN VARCHAR2 IS
7 BEGIN
8 -- Because forms can not directly read this global variable we need
9 -- to write just a little function on the server to return the value!!
10 RETURN(ghr_pay_calc.form_item_name);
11 --
12 END get_form_item_name;
13 --
14 PROCEDURE set_form_item_name(p_value IN VARCHAR2) IS
15 BEGIN
16 ghr_pay_calc.form_item_name := p_value;
17 --
18 END set_form_item_name;
19
20 --
21
22 FUNCTION get_open_pay_range (p_position_id IN hr_all_positions_f.position_id%TYPE
23 ,p_person_id IN per_all_people_f.person_id%type
24 ,p_prd IN ghr_pa_requests.pay_rate_determinant%type
25 ,p_pa_request_id IN ghr_pa_requests.pa_request_id%type
26 ,p_effective_date IN date)
27 RETURN BOOLEAN IS
28 --
29 l_pos_ei_data per_position_extra_info%ROWTYPE;
30 l_range_or_match pay_user_tables.range_or_match%type;
31 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
32 l_user_table_id pay_user_tables.user_table_id%type;
33 l_proc varchar2(30) := 'get_open_pay_range';
34
35 cursor c_pay_type is
36 select range_or_match
37 from pay_user_tables
38 where user_table_id = l_user_table_id;
39 --
40 --
41 BEGIN
42
43 hr_utility.set_location('Entering ...' || l_proc,10);
44
45 IF p_prd IN ('A','B','E','F','U','V') THEN
46 begin
47 hr_utility.set_location('Retained Grade ...' || l_proc,20);
48
49 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_person_id
50 ,p_effective_date
51 ,p_pa_request_id);
52 IF p_prd IN ('A','B','E','F') AND l_retained_grade.temp_step is not null THEN
53 ghr_history_fetch.fetch_positionei(
54 p_position_id => p_position_id
55 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
56 ,p_date_effective => p_effective_date
57 ,p_pos_ei_data => l_pos_ei_data);
58
59 l_user_table_id := l_pos_ei_data.poei_information5;
60 ELSE
61 l_user_table_id := l_retained_grade.user_table_id;
62 hr_utility.set_location(' Retained user table id ' || to_char(l_user_table_id),22);
63 END IF;
64 exception
65 when others then
66 hr_utility.set_location('Retained Exception raised ' || sqlerrm(sqlcode),25);
67 hr_utility.set_message(8301,'GHR_38255_MISSING_RETAINED_DET');
68 hr_utility.raise_error;
69 end;
70
71 ELSE
72 hr_utility.set_location('Non Retained Grade ...' || l_proc,30);
73 ghr_history_fetch.fetch_positionei(
74 p_position_id => p_position_id
75 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
76 ,p_date_effective => p_effective_date
77 ,p_pos_ei_data => l_pos_ei_data);
78
79 l_user_table_id := l_pos_ei_data.poei_information5;
80 hr_utility.set_location('Non Retained user table id ' || to_char(l_user_table_id),32);
81
82 END IF;
83 --
84 /***
85 if l_pos_ei_data.poei_information8 = 'Y' THEN
86 RETURN(TRUE);
87 else
88 RETURN(FALSE);
89 end if;
90 --
91 ***/
92
93 for c_pay_type_rec in c_pay_type
94 loop
95 l_range_or_match := c_pay_type_rec.range_or_match;
96 exit;
97 end loop;
98
99 if l_range_or_match = 'R' THEN
100 RETURN(TRUE);
101 else
102 RETURN(FALSE);
103 end if;
104
105 END get_open_pay_range;
106
107
108 FUNCTION get_user_table_id (p_position_id IN hr_all_positions_f.position_id%TYPE
109 ,p_effective_date IN date)
110 RETURN NUMBER IS
111 --
112 -- Since Position Extra Info now has history use the history packages written to get
113 -- the user_table_id
114 l_pos_ei_data per_position_extra_info%ROWTYPE;
115 --
116 --
117 BEGIN
118 ghr_history_fetch.fetch_positionei(
119 p_position_id => p_position_id
120 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
121 ,p_date_effective => p_effective_date
122 ,p_pos_ei_data => l_pos_ei_data);
123 --
124 RETURN(l_pos_ei_data.poei_information5);
125 --
126 END get_user_table_id;
127 --
128 -- Depenending on the user table associated with the position this function will return
129 -- 0 if it is the 'standard default' table otherwise 6
130 FUNCTION get_default_prd (p_position_id IN NUMBER
131 ,p_effective_date IN DATE)
132 RETURN VARCHAR2 IS
133 l_user_table_id pay_user_tables.user_table_id%TYPE;
134 l_user_table_name pay_user_tables.user_table_name%TYPE;
135 BEGIN
136 -- First use the routine already written to go and get the user_table_id for the given position
137 l_user_table_id := get_user_table_id (p_position_id
138 ,NVL(p_effective_date,TRUNC(sysdate)) );
139 --
140 -- Next get the name of the retrieved user_table_id
141 IF l_user_table_id IS NOT NULL THEN
142 --
143 l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
144 -- Note: Must have a table name if we have a table_id
145 IF l_user_table_name = ghr_pay_calc.l_standard_table_name THEN
146 RETURN('0');
147 ELSE
148 RETURN('6');
149 END IF;
150 ELSE
151 --
152 RETURN(null);
153 --
154 END IF;
155 --
156 END get_default_prd;
157 --
158 -- This function returns TRUE if Pay Calc is going to set the step so the form knows to
159 -- grey it out, this is especially hard to work out after it has been routed!!
160 FUNCTION pay_calc_sets_step(p_first_noa_code IN VARCHAR2
161 ,p_second_noa_code IN VARCHAR2
162 ,p_pay_plan IN VARCHAR2
163 ,p_prd IN VARCHAR2
164 ,p_pa_request_id IN NUMBER)
165 RETURN BOOLEAN IS
166 l_first_noa_code ghr_nature_of_actions.code%TYPE;
167 l_second_noa_code ghr_nature_of_actions.code%TYPE;
168 l_pay_plan VARCHAR2(30);
169 l_prd VARCHAR2(30);
170 --
171 l_ghr_pa_request_rec ghr_pa_requests%ROWTYPE;
172 --
173 BEGIN
174 -- If 'Correction' then use second NOA
175 IF p_first_noa_code = '002' THEN
176 IF p_pa_request_id IS NULL THEN
177 hr_utility.set_message(8301,'GHR_38398_PAY_CALC_NO_PAR_ID');
178 raise pay_calc_message;
179 END IF;
180 ghr_corr_canc_sf52.build_corrected_sf52
181 (p_pa_request_id => p_pa_request_id
182 ,p_noa_code_correct => p_second_noa_code
183 ,p_sf52_data_result => l_ghr_pa_request_rec);
184
185 l_first_noa_code := p_second_noa_code;
186 l_second_noa_code := null;
187 l_pay_plan := NVL(p_pay_plan ,l_ghr_pa_request_rec.to_pay_plan);
188 l_prd := NVL(p_prd ,l_ghr_pa_request_rec.pay_rate_determinant);
189
190 ELSE
191 l_first_noa_code := p_first_noa_code;
192 l_second_noa_code := p_second_noa_code;
193 l_pay_plan := p_pay_plan;
194 l_prd := p_prd;
195
196 END IF;
197
198 --Pradeep added 850 for Title 38 changes.
199 IF (l_first_noa_code IN ('891','867','892','893','850')
200 AND l_second_noa_code IS NULL)
201 OR (l_first_noa_code IN ('892','893')
202 AND l_second_noa_code IN ('892','893') ) THEN
203 /**** -- don't even bother for 894?
204 OR (l_first_noa_code = '894'
205 AND l_pay_plan IN ('GS','GM','GG','GH','ES','FO','FP','FE')
206 AND l_prd IN ('J','K','R','3','U','V','S') ) THEN
207 ***/
208 --
209 RETURN(TRUE);
210 ELSE
211 RETURN(FALSE);
212 END IF;
213 END;
214 --
215
216 --Bug#5132113 added new parameter p_open_range_out_basic_pay
217
218 FUNCTION populate_in_rec_structure
219 (p_person_id IN per_people_f.person_id%TYPE
220 ,p_position_id IN hr_all_positions_f.position_id%TYPE
221 ,p_noa_family_code IN ghr_families.noa_family_code%TYPE
222 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
223 ,p_second_noa_code IN ghr_nature_of_actions.code%TYPE
224 ,p_first_action_la_code1 IN ghr_pa_requests.first_action_la_code1%TYPE
225 ,p_effective_date IN DATE
226 ,p_pay_rate_determinant IN VARCHAR2
227 ,p_pay_plan IN VARCHAR2
228 ,p_grade_or_level IN VARCHAR2
229 ,p_step_or_rate IN VARCHAR2
230 ,p_pay_basis IN VARCHAR2
231 ,p_user_table_id IN NUMBER
232 ,p_duty_station_id IN NUMBER
233 ,p_auo_premium_pay_indicator IN VARCHAR2
234 ,p_ap_premium_pay_indicator IN VARCHAR2
235 ,p_retention_allowance IN NUMBER
236 ,p_to_ret_allow_percentage IN NUMBER
237 ,p_supervisory_differential IN NUMBER
238 ,p_staffing_differential IN NUMBER
239 ,p_current_basic_pay IN NUMBER
240 ,p_current_adj_basic_pay IN NUMBER
241 ,p_current_step_or_rate IN VARCHAR2
242 ,p_pa_request_id IN NUMBER
243 ,p_open_range_out_basic_pay IN NUMBER
244 ,p_open_out_locality_adj IN NUMBER
245 )
246 RETURN ghr_pay_calc.pay_calc_in_rec_type IS
247
248 l_populated_rec ghr_pay_calc.pay_calc_in_rec_type;
249 --
250 BEGIN
251 --
252 l_populated_rec.person_id := p_person_id;
253 l_populated_rec.position_id := p_position_id;
254 l_populated_rec.noa_family_code := p_noa_family_code;
255 l_populated_rec.noa_code := p_noa_code;
256 l_populated_rec.second_noa_code := p_second_noa_code;
257 l_populated_rec.first_action_la_code1 := p_first_action_la_code1;
258 IF p_effective_date IS NULL THEN
259 l_populated_rec.effective_date := TRUNC(sysdate);
260 ELSE
261 l_populated_rec.effective_date := p_effective_date;
262 END IF;
263 l_populated_rec.pay_rate_determinant := p_pay_rate_determinant;
264 l_populated_rec.pay_plan := p_pay_plan;
265 l_populated_rec.grade_or_level := p_grade_or_level;
266 l_populated_rec.step_or_rate := p_step_or_rate;
267 l_populated_rec.pay_basis := p_pay_basis;
268 l_populated_rec.user_table_id := p_user_table_id;
269 l_populated_rec.duty_station_id := p_duty_station_id;
270 l_populated_rec.auo_premium_pay_indicator := p_auo_premium_pay_indicator;
271 l_populated_rec.ap_premium_pay_indicator := p_ap_premium_pay_indicator;
272 l_populated_rec.retention_allowance := p_retention_allowance;
273 l_populated_rec.to_ret_allow_percentage := p_to_ret_allow_percentage;
274 l_populated_rec.supervisory_differential := p_supervisory_differential;
275 l_populated_rec.staffing_differential := p_staffing_differential;
276 l_populated_rec.current_basic_pay := p_current_basic_pay;
277 l_populated_rec.current_adj_basic_pay := p_current_adj_basic_pay;
278 l_populated_rec.current_step_or_rate := p_current_step_or_rate;
279 l_populated_rec.pa_request_id := p_pa_request_id;
280 l_populated_rec.open_range_out_basic_pay := p_open_range_out_basic_pay;
281 -- Bug#5132113 assigning locality adjustment
282 l_populated_rec.open_out_locality_adj := p_open_out_locality_adj;
283
284 RETURN l_populated_rec;
285
286 END;
287 --
288 PROCEDURE validate_in_data(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type) IS
289 --
290 CURSOR c_chk_pos IS
291 SELECT 1
292 FROM hr_all_positions_f pos
293 WHERE p_pay_calc_data.position_id = pos.position_id
294 AND p_pay_calc_data.effective_date BETWEEN pos.effective_start_date
295 and pos.effective_end_date;
296 l_ret_val BOOLEAN := FALSE;
297 BEGIN
298 -- Must have a person
299 IF p_pay_calc_data.person_id IS NULL THEN
300 ghr_pay_calc.form_item_name := 'PAR.EMPLOYEE_LAST_NAME';
301 hr_utility.set_message(8301,'GHR_38244_PAY_CALC_NO_PER');
302 raise pay_calc_message;
303 END IF;
304
305 -- Must have a position -- well that is kinda obvious!!!
306 IF p_pay_calc_data.position_id IS NULL THEN
307 ghr_pay_calc.form_item_name := 'PAR.TO_POSITION_TITLE';
308 hr_utility.set_message(8301,'GHR_38016_PAY_CALC_NO_POS');
309 raise pay_calc_message;
310 END IF;
311
312 -- 774633 Now we have a position id check it is valid at given date
313 FOR c_chk_pos_rec IN c_chk_pos LOOP
314 l_ret_val := TRUE;
315 END LOOP;
316 IF NOT l_ret_val THEN
317 hr_utility.set_message(8301,'GHR_38640_PAY_CALC_INV_POS');
318 raise pay_calc_message;
319 END IF;
320
321 -- Must have a family (e.g 'APP', 'SALARY_CHG')
322 IF p_pay_calc_data.noa_family_code IS NULL THEN
323 hr_utility.set_message(8301,'GHR_38245_PAY_CALC_NO_FAM');
324 raise pay_calc_message;
325 END IF;
326
327 -- Must have a noa if the family is 'SALARY_CHG'
328 IF SUBSTR(p_pay_calc_data.noa_family_code ,1,8) = 'GHR_SAL_'
329 -- IF p_pay_calc_data.noa_family_code = 'SALARY_CHG'
330 AND p_pay_calc_data.noa_code IS NULL THEN
331 ghr_pay_calc.form_item_name := 'PAR.FIRST_NOA_CODE';
332 hr_utility.set_message(8301,'GHR_38246_PAY_CALC_NO_NOA');
333 raise pay_calc_message;
334 END IF;
335
336 -- No need to check for effective date since it will already have been
337 -- defaulted to sysdate
338
339 -- Must have a pay rate determinant (e.g 0,5,6,7)
340 IF p_pay_calc_data.pay_rate_determinant IS NULL THEN
341 ghr_pay_calc.form_item_name := 'PAR.PAY_RATE_DETERMINANT';
342 hr_utility.set_message(8301,'GHR_38021_PAY_CALC_NO_PRD');
343 raise pay_calc_message;
344 END IF;
345
346 -- Must have a pay plan (e.g GG,GS) which is associated to the position
347 IF p_pay_calc_data.pay_plan IS NULL THEN
348 hr_utility.set_message(8301,'GHR_38017_PAY_CALC_NO_PAY_PLAN');
349 raise pay_calc_message;
350 END IF;
351
352 -- Must have a grade_or_level (e.g 01,02) which is associated to the position
353 IF p_pay_calc_data.grade_or_level IS NULL THEN
354 hr_utility.set_message(8301,'GHR_38018_PAY_CALC_NO_GRADE');
355 raise pay_calc_message;
356 END IF;
357
358 -- Must have a step_or_rate (e.g 01,02) which is associated to the position
359 IF p_pay_calc_data.step_or_rate IS NULL THEN
360 ghr_pay_calc.form_item_name := 'PAR.TO_STEP_OR_RATE';
361 hr_utility.set_message(8301,'GHR_38019_PAY_CALC_NO_STEP');
362 raise pay_calc_message;
363 END IF;
364
365 -- Must have a pay_basis (e.g PA,PH) which is associated to the position
366 IF p_pay_calc_data.pay_basis IS NULL THEN
367 hr_utility.set_message(8301,'GHR_38020_PAY_CALC_NO_PAY_BAS');
368 raise pay_calc_message;
369 END IF;
370
371 -- Must have a pay table id (e.g 0000,0013) which is associated to the position
372 -- we actually store the unique id of the plan table id which is the user_table_id
373 IF p_pay_calc_data.user_table_id IS NULL THEN
374 hr_utility.set_message(8301, 'GHR_38175_NO_POS_PLAN_TABLE');
375 raise pay_calc_message;
376 END IF;
377
378 -- IF NOA CODE = 891
379 -- 1) must have a current_basic_pay
380 -- 2) must be for a pay plan of GM
381 IF p_pay_calc_data.noa_code = '891' THEN
382 IF p_pay_calc_data.current_basic_pay IS NULL THEN
383 hr_utility.set_message(8301, 'GHR_38247_NO_CUR_BASIC_PAY_891');
384 raise pay_calc_message;
385 END IF;
386 END IF;
387
388 -- For Salary change If NOA CODE = 867, 892, 893
389 -- Must have current step or rate for PRD's of 0,6 and M
390 -- For PRD of M must also have current_adj_basic_pay
391 IF p_pay_calc_data.noa_code IN ('867', '892', '893' ) THEN
392 IF p_pay_calc_data.pay_rate_determinant IN ('0','6','M')
393 AND p_pay_calc_data.current_step_or_rate IS NULL THEN
394 hr_utility.set_message(8301, 'GHR_38249_NO_CUR_STEP');
395 raise pay_calc_message;
396 END IF;
397 IF p_pay_calc_data.pay_rate_determinant = 'M'
398 AND p_pay_calc_data.current_adj_basic_pay IS NULL THEN
399 hr_utility.set_message(8301, 'GHR_38250_NO_CUR_ADJ_PAY_WGI');
400 raise pay_calc_message;
401 END IF;
402 END IF;
403
404 -- IF NOA CODE = 866 - must have a pa_request_id
405 IF p_pay_calc_data.noa_code = '866' THEN
406 IF p_pay_calc_data.pa_request_id IS NULL THEN
407 hr_utility.set_message(8301, 'GHR_38482_866_NO_PAR_ID');
408 raise pay_calc_message;
409 END IF;
410 END IF;
411
412 END validate_in_data;
413 --
414 --------------------------- convert_amount> ------------------------------------
415 --
416 FUNCTION convert_amount (p_amount IN NUMBER
417 ,p_in_pay_basis IN VARCHAR2
418 ,p_out_pay_basis IN VARCHAR2)
419 RETURN NUMBER IS
420 --
421 -- This function converts a given amount from one pay_basis to another.
422 -- Currently we only know how to convert the following
423 -- FROM TO
424 -- PA PH /2087 - standard rounding to 2dp
425 -- PA BW /2087 *80 - standard rounding to 2dp
426 -- PH PA *2087 - standard rounding to whole number
427 -- BW PA /80 *2087 - standard rounding to whole number
428 -- Added for TSP
429 -- PA PD /261 - standard rounding to 2dp
430 -- PA PM /12 - standard rounding to 2dp
431 -- PD PA *261 - standard rounding to whole number
432 -- PM PA *12 - standard rounding to whole number
433 l_conv_amount NUMBER;
434 BEGIN
435 IF p_in_pay_basis = p_out_pay_basis THEN
436 RETURN(p_amount);
437 --ELSIF p_out_pay_basis = 'PD' THEN
438 --RETURN(p_amount);
439 --ELSIF p_in_pay_basis = 'PD' THEN
440 --RETURN(p_amount);
441 ELSIF p_in_pay_basis = 'PA' THEN
442 IF p_out_pay_basis = 'PH' THEN
443 l_conv_amount := ROUND(p_amount/2087, 2);
444 RETURN(l_conv_amount);
445 ELSIF p_out_pay_basis = 'BW' THEN
446 l_conv_amount := ROUND((p_amount/2087) * 80, 2);
447 RETURN(l_conv_amount);
448 ELSIF p_out_pay_basis = 'PD' THEN
449 l_conv_amount := ROUND(p_amount/261 , 2);
450 RETURN(l_conv_amount);
451 ELSIF p_out_pay_basis = 'PM' THEN
452 l_conv_amount := ROUND(p_amount/12 , 2);
453 RETURN(l_conv_amount);
454 END IF;
455 ELSIF p_in_pay_basis = 'PH' THEN
456 IF p_out_pay_basis = 'PA' THEN
457 l_conv_amount := ROUND(p_amount*2087, 0);
458 RETURN(l_conv_amount);
459 END IF;
460 ELSIF p_in_pay_basis = 'BW' THEN
461 IF p_out_pay_basis = 'PA' THEN
462 l_conv_amount := ROUND(p_amount/80 * 2087, 0);
463 RETURN(l_conv_amount);
464 END IF;
465 ELSIF p_in_pay_basis = 'PD' THEN
466 IF p_out_pay_basis = 'PA' THEN
467 l_conv_amount := ROUND(p_amount * 261, 0);
468 RETURN(l_conv_amount);
469 END IF;
470 ELSIF p_in_pay_basis = 'PM' THEN
471 IF p_out_pay_basis = 'PA' THEN
472 l_conv_amount := ROUND(p_amount * 12, 0);
473 RETURN(l_conv_amount);
474 END IF;
475 END IF;
476 --
477 -- If we got here sorry do not know how to convert this amount!!
478 --
479 hr_utility.set_message(8301, 'GHR_38251_NO_CONVERSION');
480 hr_utility.set_message_token('FROM_PAY_BASIS',p_in_pay_basis);
481 hr_utility.set_message_token('TO_PAY_BASIS',p_out_pay_basis);
482 raise ghr_pay_calc.pay_calc_message;
483 END;
484 --
485 --------------------------- <get_lpa_percentage> ------------------------------------
486 --
487 FUNCTION get_lpa_percentage (p_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE
488 ,p_effective_date DATE)
489 RETURN NUMBER IS
490
491 l_ret_val ghr_locality_pay_areas_f.adjustment_percentage%TYPE := NULL;
492 --
493 CURSOR cur_lpa IS
494 SELECT lpa.adjustment_percentage
495 FROM ghr_locality_pay_areas_f lpa
496 ,ghr_duty_stations_f dst
497 WHERE dst.duty_station_id = p_duty_station_id
498 AND NVL(p_effective_date,TRUNC(sysdate)) between dst.effective_start_date and dst.effective_end_date
499 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
500 AND NVL(p_effective_date,TRUNC(sysdate)) between lpa.effective_start_date and lpa.effective_end_date;
501 --
502 BEGIN
503 FOR cur_lpa_rec IN cur_lpa LOOP
504 l_ret_val := cur_lpa_rec.adjustment_percentage;
505 END LOOP;
506 --
507 RETURN(l_ret_val);
508 --
509 END get_lpa_percentage;
510 --
511 --
512 --------------------------- <get_leo_lpa_percentage> ------------------------------------
513 --
514 FUNCTION get_leo_lpa_percentage (p_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE
515 ,p_effective_date DATE)
516 RETURN NUMBER IS
517
518 l_ret_val NUMBER := NULL;
519 --
520 CURSOR cur_lpa IS
521 SELECT NVL(lpa.leo_adjustment_percentage, lpa.adjustment_percentage) adj_percentage
522 FROM ghr_locality_pay_areas_f lpa
523 ,ghr_duty_stations_f dst
524 WHERE dst.duty_station_id = p_duty_station_id
525 AND NVL(p_effective_date,TRUNC(sysdate)) between dst.effective_start_date and dst.effective_end_date
526 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
527 AND NVL(p_effective_date,TRUNC(sysdate)) between lpa.effective_start_date and lpa.effective_end_date;
528 --
529 CURSOR cur_lpa_geo_null IS
530 SELECT lpa.adjustment_percentage adj_percentage
531 FROM ghr_locality_pay_areas_f lpa
532 ,ghr_duty_stations_f dst
533 WHERE dst.duty_station_id = p_duty_station_id
534 AND NVL(p_effective_date,TRUNC(sysdate)) between dst.effective_start_date and dst.effective_end_date
535 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
536 AND NVL(p_effective_date,TRUNC(sysdate)) between lpa.effective_start_date and lpa.effective_end_date;
537 --
538
539 CURSOR cur_leo_code IS
540 SELECT leo_pay_area_code
541 FROM ghr_duty_stations_f
542 WHERE duty_station_id = p_duty_station_id
543 AND NVL(p_effective_date,TRUNC(sysdate))
544 between effective_start_date and effective_end_date;
545
546 l_leo_pay_area_code ghr_duty_stations_f.leo_pay_area_code%type;
547
548 BEGIN
549 l_leo_pay_area_code := NULL;
550 FOR cur_leo_code_rec IN cur_leo_code LOOP
551 l_leo_pay_area_code := cur_leo_code_rec.leo_pay_area_code;
552 EXIT;
553 END LOOP;
554
555 if l_leo_pay_area_code is not null then
556 FOR cur_lpa_rec IN cur_lpa LOOP
557 l_ret_val := cur_lpa_rec.adj_percentage;
558 END LOOP;
559 else
560 FOR cur_lpa_geo_null_rec IN cur_lpa_geo_null LOOP
561 l_ret_val := cur_lpa_geo_null_rec.adj_percentage;
562 END LOOP;
563 end if;
564 --
565 RETURN(l_ret_val);
566 --
567 END get_leo_lpa_percentage;
568 --
569 --
570 -- This is just a little function that given a user table_id returns the name.
571 -- It was originally writen when there was an error on the table it could be used as
572 -- a token in the error meesage
573 FUNCTION get_user_table_name (p_user_table_id IN NUMBER)
574 RETURN VARCHAR2 IS
575 --
576 CURSOR cur_uta IS
577 SELECT uta.user_table_name
578 FROM pay_user_tables uta
579 WHERE uta.user_table_id = p_user_table_id;
580 --
581 BEGIN
582 FOR cur_uta_rec IN cur_uta LOOP
583 RETURN(cur_uta_rec.user_table_name );
584 END LOOP;
585
586 RETURN('UNKNOWN'); -- Probably need to translate this but we should never
587 -- really be given a user table id that doesn't exist
588 -- Assuming referential integrity has been enforced!!!
589 END get_user_table_name;
590 --
591 PROCEDURE is_retained_ia(
592 p_person_id IN NUMBER,
593 p_effective_date IN DATE,
594 p_retained_pay_plan IN OUT NOCOPY VARCHAR2,
595 p_retained_grade IN OUT NOCOPY VARCHAR2,
596 p_retained_step_or_rate IN OUT NOCOPY VARCHAR2,
597 p_temp_step IN OUT NOCOPY VARCHAR2,
598 p_return_flag OUT NOCOPY BOOLEAN)
599 IS
600
601 CURSOR get_prev_ret_grade(c_person_id NUMBER,c_effective_date DATE) is
602 SELECT pei_information3 grade,
603 pei_information4 step,
604 pei_information5 pay_plan,
605 pei_information9 temp_step
606 FROM per_people_extra_info
607 where person_id = c_person_id
608 and information_type ='GHR_US_RETAINED_GRADE'
609 and pei_information_category = 'GHR_US_RETAINED_GRADE'
610 and c_effective_date BETWEEN NVL(fnd_date.canonical_to_date(pei_information1),c_effective_date)
611 AND NVL(fnd_date.canonical_to_date(pei_information2),c_effective_date);
612
613 CURSOR c_get_rpa_details(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
614 SELECT pa_notification_id, to_step_or_rate
615 FROM ghr_pa_requests
616 WHERE pa_request_id = c_pa_request_id;
617
618 l_true_or_false BOOLEAN := FALSE;
619 l_effective_date DATE;
620
621 -- Bug 3021003
622 l_session ghr_history_api.g_session_var_type;
623 l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
624 l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
625 l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
626 l_ia_effective_date ghr_pa_requests.effective_date%type;
627 l_ia_retro_first_noa ghr_pa_requests.first_noa_code%type;
628 l_ia_retro_second_noa ghr_pa_requests.second_noa_code%type;
629
630 l_retained_pay_plan VARCHAR2(30);
631 l_retained_grade VARCHAR2(30);
632 l_retained_step_or_rate VARCHAR2(30);
633 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
634 l_temp_step VARCHAR2(30);
635 BEGIN
636
637 l_retained_pay_plan := p_retained_pay_plan;
638 l_retained_grade := p_retained_grade;
639 l_retained_step_or_rate := p_retained_step_or_rate;
640 l_temp_step := p_temp_step;
641
642 ghr_history_api.get_g_session_var(l_session); -- Bug 3021003
643 -----------If an Intervening cancellation has been done, Need to take previous Step or rate
644 IF l_session.noa_id_correct IS NOT NULL THEN
645 -- Get notification id and step from RPA
646 hr_utility.set_location('Sun l_session.altered_pa_request_id' || l_session.altered_pa_request_id,10);
647 FOR l_get_rpa_details IN c_get_rpa_details(l_session.altered_pa_request_id) LOOP
648 l_pa_notification_id := l_get_rpa_details.pa_notification_id;
649 l_to_step_or_rate := l_get_rpa_details.to_step_or_rate;
650 END LOOP;
651
652 IF l_to_step_or_rate = '00' THEN
653 --BUG #7216635 added the parameter p_noa_id_correct
654 GHR_APPROVED_PA_REQUESTS.determine_ia(
655 p_pa_request_id => l_session.altered_pa_request_id,
656 p_pa_notification_id => l_pa_notification_id,
657 p_person_id => p_person_id,
658 p_effective_date => p_effective_date,
659 p_noa_id_correct => l_session.noa_id_correct,
660 p_retro_pa_request_id => l_retro_pa_request_id,
661 p_retro_eff_date => l_ia_effective_date,
662 p_retro_first_noa => l_ia_retro_first_noa,
663 p_retro_second_noa => l_ia_retro_second_noa);
664 hr_utility.set_location('Sun l_ia_retro_first_noa' || l_ia_retro_first_noa,10);
665 hr_utility.set_location('Sun l_ia_retro_second_noa' || l_ia_retro_second_noa,10);
666 IF l_ia_retro_first_noa = '001' AND l_ia_retro_second_noa IN ('867','892','893','894') THEN
667 l_effective_date := p_effective_date - 1;
668 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
669 (p_person_id => p_person_id
670 ,p_effective_date => l_effective_date);
671 IF p_temp_step IS NULL THEN
672 p_retained_pay_plan := l_retained_grade_rec.pay_plan;
673 p_retained_grade := l_retained_grade_rec.grade_or_level;
674 p_retained_step_or_rate := l_retained_grade_rec.step_or_rate;
675 l_true_or_false := TRUE;
676 ELSE
677 p_retained_pay_plan := l_retained_grade_rec.pay_plan;
678 p_retained_grade := l_retained_grade_rec.grade_or_level;
679 p_retained_step_or_rate := l_retained_grade_rec.step_or_rate;
680 p_temp_step := l_retained_grade_rec.temp_step;
681 l_true_or_false := TRUE;
682 END IF;
683 END IF; -- IF l_ia_retro_first_noa = '001'
684 END IF; -- IF l_to_step_or_rate = '00' THEN
685 END IF; -- IF l_session.noa_id_correct IS NOT NULL
686
687 IF l_true_or_false = FALSE THEN
688 l_effective_date := p_effective_date - 1;
689 -- If temp step include temp step also in the condition
690 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
691 (p_person_id => p_person_id
692 ,p_effective_date => l_effective_date);
693 IF p_temp_step IS NULL THEN
694 IF p_retained_pay_plan = l_retained_grade_rec.pay_plan AND
695 p_retained_grade = l_retained_grade_rec.grade_or_level AND
696 p_retained_step_or_rate = l_retained_grade_rec.step_or_rate THEN
697 hr_utility.set_location('NAR Inside true',100);
698 l_true_or_false := TRUE;
699 -- EXIT;
700 ELSE
701 l_true_or_false := FALSE;
702 hr_utility.set_location('NAR Inside false',110);
703 END IF;
704 ELSE
705 IF p_retained_pay_plan = l_retained_grade_rec.pay_plan AND
706 p_retained_grade = l_retained_grade_rec.grade_or_level AND
707 p_retained_step_or_rate = l_retained_grade_rec.step_or_rate AND
708 p_temp_step = l_retained_grade_rec.temp_step THEN
709 hr_utility.set_location('NAR Inside true',100);
710 l_true_or_false := TRUE;
711 -- EXIT;
712 ELSE
713 l_true_or_false := FALSE;
714 hr_utility.set_location('NAR Inside false',110);
715 END IF;
716 END IF;
717 END IF; -- IF l_true_or_false = FALSE THEN
718 p_return_flag := l_true_or_false;
719 EXCEPTION
720 WHEN OTHERS THEN
721 hr_utility.set_location('Error '|| sqlerrm,60);
722 p_retained_pay_plan := l_retained_pay_plan;
723 p_retained_grade := l_retained_grade;
724 p_retained_step_or_rate := l_retained_step_or_rate;
725 p_temp_step := l_temp_step;
726 p_return_flag := FALSE;
727 END is_retained_ia;
728 ----------------------------- <get_pay_table_value> ------------------------------------------
729 PROCEDURE get_pay_table_value (p_user_table_id IN NUMBER
730 ,p_pay_plan IN VARCHAR2
731 ,p_grade_or_level IN VARCHAR2
732 ,p_step_or_rate IN VARCHAR2
733 ,p_effective_date IN DATE
734 ,p_PT_value OUT NOCOPY NUMBER
735 ,p_PT_eff_start_date OUT NOCOPY DATE
736 ,p_PT_eff_end_date OUT NOCOPY DATE) IS
737 --
738 l_PT_value NUMBER;
739 l_PT_eff_start_date DATE;
740 l_PT_eff_end_date DATE;
741 l_record_found BOOLEAN := FALSE;
742 --
743 -- Go and get the basic pay from the given pay table at the given grade or level
744 -- and step
745 -- NOTE: column => Step or Rate
746 -- row => Pay Plan ||'-'|| Grade or Level
747 --
748 CURSOR cur_pay IS
749 SELECT cin.value basic_pay
750 ,cin.effective_start_date
751 ,cin.effective_end_date
752 FROM pay_user_column_instances_f cin
753 ,pay_user_rows_f urw
754 ,pay_user_columns col
755 WHERE col.user_table_id = p_user_table_id
756 AND col.user_column_name = p_step_or_rate
757 AND urw.user_table_id = p_user_table_id
758 -- Bug# 5132113 getting the value of GS when pay plan is GP
759 AND urw.row_low_range_or_name = decode(p_pay_plan,'GP','GS',p_pay_plan)||'-'||p_grade_or_level
760 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
761 AND cin.user_row_id = urw.user_row_id
762 AND cin.user_column_id = col.user_column_id
763 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;
764 BEGIN
765 FOR cur_pay_rec IN cur_pay LOOP
766 l_PT_value := ROUND(cur_pay_rec.basic_pay,2);
767 l_PT_eff_start_date := cur_pay_rec.effective_start_date;
768 l_PT_eff_end_date := cur_pay_rec.effective_end_date;
769 l_record_found := TRUE;
770 --
771 IF l_PT_value IS NULL THEN
772 -- Sets to give name of pay table, pay plan, grade, step and date
773 hr_utility.set_message(8301,'GHR_38252_NULL_PAY_PLAN_VALUE');
774 hr_utility.set_message_token('PAY_TABLE_NAME',get_user_table_name(p_user_table_id) );
775 hr_utility.set_message_token('STEP',p_step_or_rate);
776 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
777 hr_utility.set_message_token('GRADE',p_grade_or_level);
778 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
779 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date));
780 raise pay_calc_message;
781 END IF;
782 --
783 END LOOP;
784 --
785 IF NOT l_record_found THEN
786 -- Set tokens to give name of pay table, pay plan, grade, step and date
787 hr_utility.set_message(8301,'GHR_38253_NO_PAY_PLAN_VALUE');
788 hr_utility.set_message_token('PAY_TABLE_NAME',get_user_table_name(p_user_table_id));
789 hr_utility.set_message_token('STEP',p_step_or_rate);
790 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
791 hr_utility.set_message_token('GRADE',p_grade_or_level);
792 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
793 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date));
794 raise ghr_pay_calc.pay_calc_message;
795 ELSE
796 p_PT_value := l_PT_value;
797 p_PT_eff_start_date := l_PT_eff_start_date;
798 p_PT_eff_end_date := l_PT_eff_end_date;
799 END IF;
800
801 EXCEPTION
802 WHEN others THEN
803 -- Reset IN OUT parameters and set OUT parameters
804 p_PT_value := NULL;
805 p_PT_eff_start_date := NULL;
806 p_PT_eff_end_date := NULL;
807 RAISE;
808 --
809 END get_pay_table_value;
810 --
811 --------------------------- <get_standard_pay_table_value> ----------------------------------
812 FUNCTION get_standard_pay_table_value (p_pay_plan IN VARCHAR2
813 ,p_grade_or_level IN VARCHAR2
814 ,p_step_or_rate IN VARCHAR2
815 ,p_effective_date IN DATE)
816 RETURN NUMBER IS
817 --
818 l_pay_plan_value NUMBER;
819 l_record_found BOOLEAN := FALSE;
820 --
821 CURSOR cur_default_pay IS
822 SELECT cin.value basic_pay
823 FROM pay_user_column_instances_f cin
824 ,pay_user_rows_f urw
825 ,pay_user_columns col
826 ,pay_user_tables utb
827 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name
828 AND col.user_table_id = utb.user_table_id
829 AND col.user_column_name = p_step_or_rate
830 AND urw.user_table_id = utb.user_table_id
831 AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
832 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
833 AND cin.user_row_id = urw.user_row_id
834 AND cin.user_column_id = col.user_column_id
835 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;
836 BEGIN
837 FOR cur_default_pay_rec IN cur_default_pay LOOP
838 l_pay_plan_value := ROUND(cur_default_pay_rec.basic_pay,2);
839 l_record_found := TRUE;
840 IF l_pay_plan_value IS NULL THEN
841 -- Set tokens to give name of pay table(standard), pay plan, grade, step and date
842 hr_utility.set_message(8301, 'GHR_38252_NULL_PAY_PLAN_VALUE');
843 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.l_standard_table_name );
844 hr_utility.set_message_token('STEP',p_step_or_rate);
845 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
846 hr_utility.set_message_token('GRADE',p_grade_or_level);
847 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
848 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date));
849 raise pay_calc_message;
850 END IF;
851 END LOOP;
852 --
853 IF NOT l_record_found THEN
854 -- Set tokens to give name of pay table(standard), pay plan, grade, step and date
855 hr_utility.set_message(8301, 'GHR_38253_NO_PAY_PLAN_VALUE');
856 hr_utility.set_message_token('PAY_TABLE_NAME',ghr_pay_calc.l_standard_table_name);
857 hr_utility.set_message_token('STEP',p_step_or_rate);
858 hr_utility.set_message_token('PAY_PLAN',p_pay_plan);
859 hr_utility.set_message_token('GRADE',p_grade_or_level);
860 -- hr_utility.set_message_token('EFF_DATE',TO_CHAR(p_effective_date,'DD-MON-YYYY') );
861 hr_utility.set_message_token('EFF_DATE',fnd_date.date_to_chardate(p_effective_date));
862 raise pay_calc_message;
863 ELSE
864 RETURN(l_pay_plan_value);
865 END IF;
866 --
867 END get_standard_pay_table_value;
868 --
869 -- This function is used to determine if the given position is a 'LEO'
870 -- The definition of a LEO is the 'LEO Position Indicator' on information type 'GHR_US_POS_GRP2'
871 -- is 1 or 2
872 -- Returns TRUE if it is a LEO Position
873 FUNCTION LEO_position (p_prd IN VARCHAR2
874 ,p_position_id IN NUMBER
875 ,p_retained_user_table_id IN NUMBER
876 ,p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
877 ,p_effective_date IN DATE)
878 RETURN BOOLEAN IS
879
880 l_pay_table varchar2(4);
881 l_pos_ei_grp2_data per_position_extra_info%rowtype;
882
883 BEGIN
884 /***** New LEO Locality Pay calculation.
885 -- bug 709492 for retained grade PRD's check the retained user table id
886 -- otherwise check the position occupied indicator as before
887 IF p_prd IN ('A','B','E','F','U','V') THEN
888 -- in the future we should have a leo indicator on the retained grade DDF
889 l_pay_table := SUBSTR(get_user_table_name(p_retained_user_table_id),1,4);
890 IF l_pay_table = '0491' THEN
891 RETURN(TRUE);
892 END IF;
893 ELSE
894 ******/
895
896 ghr_history_fetch.fetch_positionei(
897 p_position_id => p_position_id,
898 p_information_type => 'GHR_US_POS_GRP2',
899 p_date_effective => p_effective_date,
900 p_pos_ei_data => l_pos_ei_grp2_data);
901
902 IF l_pos_ei_grp2_data.position_extra_info_id IS NOT NULL
903 AND l_pos_ei_grp2_data.poei_information16 IN ('1','2') THEN
904 RETURN(TRUE);
905 END IF;
906
907 RETURN (FALSE);
908
909 END LEO_position;
910 --
911 -- This local procedure gets the default basic pay to do the comparison in the locality pay
912 FUNCTION get_GM_GH_def_basic_pay(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
913 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
914 RETURN NUMBER IS
915
916 l_std_user_table_id NUMBER;
917
918 l_grade VARCHAR2(30);
919 l_PT_eff_start_date DATE;
920 l_7dp NUMBER;
921
922 l_std_min NUMBER;
923 l_std_max NUMBER;
924
925 l_dummy_step VARCHAR2(30);
926 l_dummy_date DATE;
927 l_dummy_number NUMBER;
928
929 l_ret_basic_pay NUMBER;
930
931 CURSOR get_std_user_table_id IS
932 SELECT utb.user_table_id
933 FROM pay_user_tables utb
934 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
935
936 BEGIN
937 -- First get the id of standard pay table for later use
938 FOR c_rec IN get_std_user_table_id LOOP
939 l_std_user_table_id := c_rec.user_table_id;
940 END LOOP;
941 --
942 -- Get the 7 dp figure as calculated in the 6 step rule!
943 IF p_retained_grade.pay_plan IS NULL THEN
944 ghr_pc_basic_pay.get_basic_pay_SAL894_6step(p_pay_calc_data
945 ,p_retained_grade
946 ,'POSITION'
947 ,l_dummy_number
948 ,l_PT_eff_start_date
949 ,l_7dp);
950 l_grade := p_pay_calc_data.grade_or_level;
951 ELSE
952 ghr_pc_basic_pay.get_basic_pay_SAL894_6step(p_pay_calc_data
953 ,p_retained_grade
954 ,'PERSON'
955 ,l_dummy_number
956 ,l_PT_eff_start_date
957 ,l_7dp);
958 l_grade := p_retained_grade.grade_or_level;
959 END IF;
960 --
961 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
962 ,'GS'
963 ,l_grade
964 ,l_PT_eff_start_date
965 ,l_dummy_step
966 ,l_std_min
967 ,l_dummy_date
968 ,l_dummy_date);
969
970 -- Bug No 711506 for Loaclity Adjustment
971 -- l_std_max is being fetched from get_min_pay_table_value. Corrected the same by fetcing
972 -- from get_max_pay_table_value
973 --
974 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
975 ,'GS'
976 ,l_grade
977 ,l_PT_eff_start_date
978 ,l_dummy_step
979 ,l_std_max
980 ,l_dummy_date
981 ,l_dummy_date);
982 --
983 l_ret_basic_pay := ROUND ( ((l_std_max - l_std_min) * l_7dp ) + l_std_min );
984 --
985 RETURN(l_ret_basic_pay);
986 END;
987
988
989 --------------------------- <get_locality_adj> ----------------------------------
990 --------------------------- <get_locality_adj> ----------------------------------
991 PROCEDURE get_locality_adj (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
992 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
993 ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
994 --
995 l_basic_pay_pa NUMBER; -- This is the basis_pay based in as a PA format
996 l_basic_pay_pos NUMBER;
997 l_basic_pay_pos_pa NUMBER;
998 l_basic_pay_std NUMBER;
999 l_dummy_date DATE;
1000 l_locality_adj NUMBER;
1001 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
1002 l_default_basic_pay NUMBER;
1003 l_default_adj_basic_pay NUMBER;
1004 l_new_adj_basic_pay NUMBER; --AVR
1005 l_grade_or_level VARCHAR2(60); --AVR
1006 l_step_or_rate VARCHAR2(30); --AVR
1007 l_new_std_relative_rate NUMBER; --AVR
1008 --
1009 l_pay_plan VARCHAR2(30);
1010 --
1011 l_proc VARCHAR2(30) := 'get_locality_adj';
1012 l_new_step_or_rate VARCHAR2(30); --AVR
1013 l_session ghr_history_api.g_session_var_type;
1014 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1015
1016
1017 l_user_table_id pay_user_tables.user_table_id%type;
1018 l_GM_unadjusted_rate NUMBER; --AVR
1019
1020 -- Bug 4740036 Start
1021 l_std_user_table_id NUMBER;
1022 l_dummy VARCHAR2(1);
1023 l_std_basic_pay NUMBER;
1024 l_std_locality_adj NUMBER;
1025 l_loc_rate NUMBER;
1026 CURSOR c_user_table_id(p_user_table_name VARCHAR2) IS
1027 SELECT utb.user_table_id
1028 FROM pay_user_tables utb
1029 WHERE utb.user_table_name = p_user_table_name;
1030 -- Bug 4740036 End
1031
1032 cursor c_pay_tab is
1033 select 1 from pay_user_tables
1034 where substr(user_table_name,1,4) in ('999B','999C','999D','999E','999F')
1035 and user_table_id = l_user_table_id;
1036
1037 l_itpay_table BOOLEAN := FALSE;
1038
1039 cursor c_pay_tab_essl is
1040 select 1 from pay_user_tables
1041 where substr(user_table_name,1,4) = 'ESSL'
1042 and user_table_id = l_user_table_id;
1043
1044 l_essl_table BOOLEAN := FALSE;
1045
1046 -- Bug 3021003
1047 l_ret_flag BOOLEAN := FALSE;
1048 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
1049
1050 --Begin Bug# 9156723
1051 l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
1052
1053 CURSOR c_get_wcd_loc IS
1054 select adjustment_percentage from ghr_locality_pay_areas_f
1055 where locality_pay_area_code = 'WA'
1056 AND NVL(p_pay_calc_data.effective_date,TRUNC(sysdate)) between effective_start_date and effective_end_date;
1057
1058 cursor c_fp_fo_duty_stn(p_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE, p_effective_date DATE) is
1059 select duty_station_code from ghr_duty_stations_f
1060 where substr(duty_station_code,1,2) between 'AA' and 'ZZ'
1061 and substr(duty_station_code,2,1)<> 'Q'
1062 and duty_station_id = p_duty_station_id
1063 AND NVL(p_effective_date,TRUNC(sysdate)) between effective_start_date and effective_end_date;
1064 --End Bug# 9156723
1065
1066 BEGIN
1067
1068 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1069 -- Bug 3021003
1070 l_retained_grade.pay_plan := p_retained_grade.pay_plan;
1071 l_retained_grade.grade_or_level := p_retained_grade.grade_or_level;
1072 l_retained_grade.step_or_rate := p_retained_grade.step_or_rate;
1073 l_retained_grade.temp_step := p_retained_grade.temp_step;
1074 -- to cater for PRD 'M' which may or may not have a retained grade record.
1075 -- if there is a retained grade record use that, otherwise use the position's
1076 ghr_history_api.get_g_session_var(l_session);
1077
1078 IF p_retained_grade.pay_plan IS NOT NULL THEN
1079 l_pay_plan := p_retained_grade.pay_plan;
1080 l_user_table_id := p_retained_grade.user_table_id;
1081 if p_retained_grade.temp_step is not null then
1082 l_pay_plan := p_pay_calc_data.pay_plan;
1083 l_user_table_id := p_pay_calc_data.user_table_id;
1084 end if;
1085 ELSE
1086 l_pay_plan := p_pay_calc_data.pay_plan;
1087 l_user_table_id := p_pay_calc_data.user_table_id;
1088 END IF;
1089
1090 for c_pay_tab_rec in c_pay_tab loop
1091 l_itpay_table := TRUE;
1092 exit;
1093 end loop;
1094
1095 for c_pay_tab_essl_rec in c_pay_tab_essl loop
1096 l_essl_table := TRUE;
1097 exit;
1098 end loop;
1099
1100 --AVR
1101 IF (l_pay_plan in ('GS','GL','GM') AND p_pay_calc_data.noa_code = '894'
1102 AND p_pay_calc_data.pay_rate_determinant = 'M') THEN
1103 IF p_retained_grade.grade_or_level IS NOT NULL THEN
1104 l_grade_or_level := p_retained_grade.grade_or_level;
1105 l_step_or_rate := p_retained_grade.step_or_rate;
1106 if p_retained_grade.temp_step is not null then
1107 l_grade_or_level := p_pay_calc_data.grade_or_level;
1108 l_step_or_rate := p_retained_grade.temp_step;
1109 end if;
1110 ELSE
1111 l_grade_or_level := p_pay_calc_data.grade_or_level;
1112 l_step_or_rate := p_pay_calc_data.step_or_rate;
1113 END IF;
1114 END IF;
1115 --AVR
1116
1117 -- Pradeep added EE to the if list for the Bug 3604377.
1118 --Bug# 6342011 added GR
1119 --Bug# 7557159 added IG
1120 --Bug# 9156723 added FP,FO
1121 IF l_pay_plan IN ('AD','AL','ES','EP','GG','GH','GM','GS','GL','IP','IE',
1122 'FB','FG','FJ','FM','FX','CA','AA','SL','ST','EE','GR','IG','FP','FO') THEN
1123 --
1124 --AVR
1125 IF (l_pay_plan IN ( 'GS','GL','GM') AND p_pay_calc_data.noa_code = '894'
1126 AND p_pay_calc_data.pay_rate_determinant = 'M') THEN
1127 IF l_pay_plan = 'GS' THEN
1128
1129 hr_utility.set_location('In Entering ...'|| l_proc,5);
1130 hr_utility.set_location('In User_table_id ...'|| to_char(p_pay_calc_data.user_table_id),5);
1131 hr_utility.set_location('In p_pay_plan ...'|| l_pay_plan,5);
1132 hr_utility.set_location('In p_grade_or_level ...'|| l_grade_or_level,5);
1133 hr_utility.set_location('In step_or_rate ...'|| l_step_or_rate,5);
1134 hr_utility.set_location('In effective_date ...'|| to_char(p_pay_calc_data.effective_date,'DD-MON-YYYY'),5);
1135 hr_utility.set_location('In new current adj basic pay ...'|| to_char(p_pay_calc_data.current_adj_basic_pay),5);
1136 hr_utility.set_location('In new basic pay ...'|| to_char(p_pay_calc_out_data.basic_pay),5);
1137 get_locality_adj_894_PRDM_GS
1138 (p_user_table_id => p_pay_calc_data.user_table_id
1139 ,p_pay_plan => l_pay_plan
1140 ,p_grade_or_level => l_grade_or_level
1141 ,p_step_or_rate => l_step_or_rate
1142 ,p_effective_date => p_pay_calc_data.effective_date
1143 ,p_cur_adj_basic_pay => p_pay_calc_data.current_adj_basic_pay
1144 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1145 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1146 ,p_new_locality_adj => l_locality_adj );
1147
1148 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
1149 p_pay_calc_out_data.locality_adj := l_locality_adj;
1150
1151
1152 ELSIF l_pay_plan = 'GM' THEN
1153
1154 get_locality_adj_894_PRDM_GM
1155 (p_pay_calc_data => p_pay_calc_data
1156 ,p_retained_grade => p_retained_grade
1157 ,p_new_std_relative_rate => l_new_std_relative_rate
1158 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1159 ,p_new_locality_adj => l_locality_adj);
1160
1161 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
1162 p_pay_calc_out_data.locality_adj := l_locality_adj;
1163
1164 END IF;
1165
1166 ELSIF l_pay_plan IN ('GM','GH') AND l_itpay_table THEN
1167 IF p_pay_calc_data.noa_code = '894' AND p_pay_calc_data.pay_rate_determinant = '6' THEN
1168 get_locality_894_itpay
1169 (p_pay_calc_data => p_pay_calc_data
1170 ,p_retained_grade => p_retained_grade
1171 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1172 ,p_GM_unadjusted_rate => l_GM_unadjusted_rate
1173 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1174 ,p_new_locality_adj => l_locality_adj );
1175
1176 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
1177 p_pay_calc_out_data.locality_adj := l_locality_adj;
1178
1179 ELSIF p_pay_calc_data.noa_code in ('892','891') AND p_pay_calc_data.pay_rate_determinant = '6' THEN
1180 get_locality_892_itpay
1181 (p_pay_calc_data => p_pay_calc_data
1182 ,p_retained_grade => p_retained_grade
1183 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1184 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1185 ,p_new_locality_adj => l_locality_adj );
1186
1187 --Begin Bug 5661441 AFHR change
1188 ELSIF p_pay_calc_data.noa_code in ('893') AND p_pay_calc_data.pay_rate_determinant = '6' THEN
1189 IF p_pay_calc_data.effective_date >= to_date('2007/01/07','YYYY/MM/DD')THEN
1190 get_locality_892_itpay
1191 (p_pay_calc_data => p_pay_calc_data
1192 ,p_retained_grade => p_retained_grade
1193 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1194 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1195 ,p_new_locality_adj => l_locality_adj );
1196 END IF;
1197 --End Bug 5661441 AFHR change
1198
1199 END IF;
1200
1201 ELSIF l_pay_plan IN ('ES','EP','FE','IE') AND l_essl_table THEN
1202
1203 l_locality_adj := 0;
1204 --Begin Bug# 9156723
1205 ELSIF l_pay_plan IN ('FP','FO') THEN
1206 open c_fp_fo_duty_stn(p_pay_calc_data.duty_station_id,p_pay_calc_data.effective_date);
1207 fetch c_fp_fo_duty_stn into l_duty_station_code;
1208 IF c_fp_fo_duty_stn%FOUND THEN
1209 IF p_pay_calc_data.effective_date BETWEEN TO_DATE('2009/08/16','YYYY/MM/DD')
1210 AND TO_DATE('2010/01/02','YYYY/MM/DD') THEN
1211 l_locality_adj := ROUND( p_pay_calc_out_data.basic_pay * (7.7/100),0);
1212 ELSIF p_pay_calc_data.effective_date BETWEEN TO_DATE('2010/01/03','YYYY/MM/DD')
1213 AND TO_DATE('2010/08/14','YYYY/MM/DD') THEN
1214 l_locality_adj := ROUND( p_pay_calc_out_data.basic_pay * (8.82/100),0);
1215 ELSIF p_pay_calc_data.effective_date BETWEEN TO_DATE('2010/08/15','YYYY/MM/DD')
1216 AND TO_DATE('2011/08/13','YYYY/MM/DD') THEN
1217 l_locality_adj := ROUND( p_pay_calc_out_data.basic_pay * (16.52/100),0);
1218 ELSIF p_pay_calc_data.effective_date BETWEEN TO_DATE('2011/08/14','YYYY/MM/DD')
1219 AND TO_DATE('2011/12/31','YYYY/MM/DD') THEN
1220 l_locality_adj := ROUND( p_pay_calc_out_data.basic_pay * (16.52/100),0);--Bug# 12917539
1221 ELSIF p_pay_calc_data.effective_date >= to_date('2012/01/01','YYYY/MM/DD') THEN
1222 /*FOR l_get_wcd_loc IN c_get_wcd_loc LOOP
1223 l_adjustment_percentage:= l_get_wcd_loc.adjustment_percentage;
1224 END LOOP;
1225 l_locality_adj := ROUND( p_pay_calc_out_data.basic_pay * (nvl(l_adjustment_percentage,0)/100),0);*/
1226 l_locality_adj := ROUND( p_pay_calc_out_data.basic_pay * (16.52/100),0);--bug# 12917539
1227 ELSE
1228 l_locality_adj := 0;
1229 END IF;
1230 ELSE
1231 --Begin Bug# 9380555
1232 l_adjustment_percentage := NVL(get_leo_lpa_percentage(p_pay_calc_data.duty_station_id
1233 ,p_pay_calc_data.effective_date),0);
1234 l_locality_adj := ROUND( p_pay_calc_out_data.basic_pay * (nvl(l_adjustment_percentage,0)/100),0);
1235 --End Bug# 9380555
1236 END IF;
1237 CLOSE c_fp_fo_duty_stn;
1238 --End Bug# 9156723
1239 --Bug# 7557159 added IG
1240 --Bug# 12404398 added AD, locality should be 0 for pay plan AD
1241 ELSIF l_pay_plan IN ('IG','AD') THEN
1242 l_locality_adj := 0;
1243 --Bug 4740036 Start
1244 --8320557 From 12 -Apr-2009 locality pay will be 0 for SL ST IP pay plan
1245 ELSIF l_pay_plan IN ('SL','ST','IP') AND p_pay_calc_data.effective_date >= to_date('2009/04/12','YYYY/MM/DD') THEN
1246 l_locality_adj := 0;
1247 ELSIF ghr_pay_calc.get_user_table_name(l_user_table_id) NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name)
1248 AND l_pay_plan in ('GG','GS')
1249 AND p_pay_calc_data.pay_rate_determinant IN ('6','E','F')
1250 AND ghr_pay_calc.LEO_position (l_dummy
1251 ,p_pay_calc_data.position_id
1252 ,l_dummy
1253 ,l_dummy
1254 ,p_pay_calc_data.effective_date) THEN
1255 hr_utility.set_location('AB p_pay_calc_data.pay_rate_determinant ' || p_pay_calc_data.pay_rate_determinant,99);
1256 FOR c_rec IN c_user_table_id(ghr_pay_calc.l_standard_table_name) LOOP
1257 l_std_user_table_id := c_rec.user_table_id;
1258 END LOOP;
1259 hr_utility.set_location('AB l_std_user_table_id ' || l_std_user_table_id,99);
1260 IF p_pay_calc_data.pay_rate_determinant = '6' THEN
1261 hr_utility.set_location('AB p_pay_calc_data.grade_or_level ' || p_pay_calc_data.grade_or_level,99);
1262 l_new_step_or_rate := NVL(p_pay_calc_out_data.out_step_or_rate, p_pay_calc_data.step_or_rate);
1263 hr_utility.set_location('AB l_new_step_or_rate ' || l_new_step_or_rate,99);
1264 get_pay_table_value(l_std_user_table_id
1265 ,l_pay_plan
1266 ,p_pay_calc_data.grade_or_level
1267 ,l_new_step_or_rate
1268 ,p_pay_calc_data.effective_date
1269 ,l_std_basic_pay
1270 ,l_dummy_date
1271 ,l_dummy_date);
1272 ELSE
1273 hr_utility.set_location('AB l_retained_grade.grade_or_level ' || l_retained_grade.grade_or_level,99);
1274 hr_utility.set_location('AB l_retained_grade.step_or_rate ' || l_retained_grade.step_or_rate,99);
1275 IF p_pay_calc_data.noa_code IN ('867','892','893') THEN
1276 IF l_session.noa_id_correct is not null then
1277 IF l_retained_grade.temp_step is not null then
1278 is_retained_ia(p_pay_calc_data.person_id,
1279 p_pay_calc_data.effective_date,
1280 l_retained_grade.pay_plan,
1281 l_retained_grade.grade_or_level,
1282 l_retained_grade.step_or_rate,
1283 l_retained_grade.temp_step,
1284 l_ret_flag);
1285 IF l_ret_flag = TRUE THEN
1286 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1287 ,l_retained_grade.temp_step);
1288 ELSE
1289 l_new_step_or_rate := p_retained_grade.temp_step;
1290 END IF;
1291 ELSE
1292 is_retained_ia(p_pay_calc_data.person_id,
1293 p_pay_calc_data.effective_date,
1294 l_retained_grade.pay_plan,
1295 l_retained_grade.grade_or_level,
1296 l_retained_grade.step_or_rate,
1297 l_retained_grade.temp_step,
1298 l_ret_flag);
1299 IF l_ret_flag = TRUE THEN
1300 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1301 ,l_retained_grade.step_or_rate);
1302 ELSE
1303 l_new_step_or_rate := l_retained_grade.step_or_rate;
1304 END IF;
1305 END IF;
1306 ELSE
1307 IF p_retained_grade.temp_step is not null THEN
1308 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1309 ,p_retained_grade.temp_step);
1310 ELSE
1311 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_retained_grade.pay_plan
1312 ,p_retained_grade.step_or_rate);
1313 END IF;
1314 END IF;
1315 hr_utility.set_location('AB l_new_step_or_rate ' || l_new_step_or_rate,99);
1316 ELSE
1317 IF p_retained_grade.temp_step is not null THEN
1318 l_new_step_or_rate := p_retained_grade.temp_step;
1319 ELSE
1320 l_new_step_or_rate := p_retained_grade.step_or_rate;
1321 END IF;
1322 END IF;
1323 get_pay_table_value(l_std_user_table_id
1324 ,l_pay_plan
1325 ,l_retained_grade.grade_or_level
1326 ,l_new_step_or_rate
1327 ,p_pay_calc_data.effective_date
1328 ,l_std_basic_pay
1329 ,l_dummy_date
1330 ,l_dummy_date);
1331 END IF;
1332 hr_utility.set_location('AB l_std_basic_pay ' || l_std_basic_pay,99);
1333 l_adjustment_percentage := ghr_pay_calc.get_leo_lpa_percentage
1334 (p_pay_calc_data.duty_station_id
1335 ,p_pay_calc_data.effective_date);
1336 hr_utility.set_location('AB l_adjustment_percentage ' || l_adjustment_percentage,99);
1337 l_std_locality_adj := ROUND(l_std_basic_pay * (NVL(l_adjustment_percentage,0)/100),0);
1338 l_loc_rate := l_std_basic_pay + l_std_locality_adj;
1339 hr_utility.set_location('AB l_std_locality_adj ' || l_std_locality_adj,99);
1340 hr_utility.set_location('AB l_loc_rate ' || l_loc_rate,99);
1341 IF p_pay_calc_out_data.basic_pay > l_loc_rate THEN
1342 l_locality_adj := 0;
1343 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_out_data.basic_pay;
1344 ELSE
1345 p_pay_calc_out_data.adj_basic_pay := l_loc_rate;
1346 l_locality_adj := l_loc_rate - p_pay_calc_out_data.basic_pay;
1347 END IF;
1348 p_pay_calc_out_data.locality_adj := l_locality_adj;
1349 hr_utility.set_location('AB p_pay_calc_out_data.locality_adj ' || p_pay_calc_out_data.locality_adj,99);
1350 hr_utility.set_location('AB p_pay_calc_out_data.adj_basic_pay ' || p_pay_calc_out_data.adj_basic_pay,99);
1351
1352 --Bug 4740036 End
1353
1354 ELSE
1355
1356 l_basic_pay_pa := convert_amount(p_pay_calc_out_data.basic_pay
1357 ,p_pay_calc_data.pay_basis
1358 ,'PA');
1359 --
1360 IF LEO_position (p_pay_calc_data.pay_rate_determinant
1361 ,p_pay_calc_data.position_id
1362 ,p_retained_grade.user_table_id
1363 ,p_pay_calc_data.duty_station_id
1364 ,p_pay_calc_data.effective_date) OR
1365 l_pay_plan = 'GL' THEN
1366 -- as for PRD 0 below except use get_leo_lpa_percentage
1367 l_adjustment_percentage := NVL(get_leo_lpa_percentage(p_pay_calc_data.duty_station_id
1368 ,p_pay_calc_data.effective_date),0);
1369 --
1370 l_locality_adj := ROUND(l_basic_pay_pa * (NVL(l_adjustment_percentage,0)/100),0);
1371
1372 ELSE
1373 --
1374 --Bug# 6342011 added GR condition. For GR return copy from locality.
1375 IF l_pay_plan ='GR' THEN
1376 l_locality_adj :=p_pay_calc_data.open_out_locality_adj;
1377 ELSIF p_pay_calc_data.pay_rate_determinant IN ('0','7', 'A','B', '2','4',
1378 'J','K','R','S','3','U','V','D') THEN --Biug# 7557159
1379 -- This one is really easy just multiply the basic_pay (converted to pa) by the locality%age
1380 l_adjustment_percentage := NVL(get_lpa_percentage(p_pay_calc_data.duty_station_id
1381 ,p_pay_calc_data.effective_date),0);
1382 --
1383 l_locality_adj := ROUND(l_basic_pay_pa * (NVL(l_adjustment_percentage,0)/100),0);
1384 --
1385 ELSIF p_pay_calc_data.pay_rate_determinant IN ('5','6','E','F') THEN
1386 --
1387 IF p_pay_calc_data.pay_rate_determinant IN ('5','6') THEN
1388 IF l_pay_plan NOT IN ('GM','GH') THEN
1389 --
1390 -- Sundar 3294560 If Pay plan is 'GG' take from 'GG' else take from GS.
1391 -- Pay Plan should be either GG or GS, if not in 'GM', 'GH'. If not, then assign
1392 -- the standard table 'GS'
1393 IF l_pay_plan NOT IN ('GG','GS') THEN
1394 l_pay_plan := 'GS';
1395 END IF; -- IF l_pay_plan NOT IN ('GG','GS')
1396
1397 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1398 ,p_pay_calc_data.grade_or_level
1399 ,NVL(p_pay_calc_out_data.out_step_or_rate,p_pay_calc_data.step_or_rate)
1400 ,p_pay_calc_data.effective_date);
1401 -- End Bug 3294560
1402 ELSE -- GM,GH
1403 l_default_basic_pay := get_GM_GH_def_basic_pay(p_pay_calc_data
1404 ,p_retained_grade);
1405 --
1406 END IF; -- PAY PLAN NOT IN GM,GH
1407 ELSIF p_pay_calc_data.pay_rate_determinant IN ('E','F') THEN
1408 --
1409 IF l_pay_plan NOT IN ('GM','GH') THEN
1410 --
1411 -- Sundar 3294560 If Pay plan is 'GG' take from 'GG' else take from GS.
1412 -- Pay Plan should be either GG or GS, if not in 'GM', 'GH'. If not, then -- the standard table 'GS'
1413 IF l_pay_plan NOT IN ('GG','GS','GL') THEN
1414 l_pay_plan := 'GS';
1415 END IF; -- IF l_pay_plan NOT IN ('GG','GS','GL')
1416
1417 --- Bug 1953725 Start
1418
1419 IF p_pay_calc_data.noa_code IN ('867','892','893') THEN
1420 --Bug 2596425
1421 if l_session.noa_id_correct is not null then
1422 if l_retained_grade.temp_step is not null then
1423 -- Bug 3021003 Start
1424 is_retained_ia(p_pay_calc_data.person_id,
1425 p_pay_calc_data.effective_date,
1426 l_retained_grade.pay_plan,
1427 l_retained_grade.grade_or_level,
1428 l_retained_grade.step_or_rate,
1429 l_retained_grade.temp_step,
1430 l_ret_flag);
1431 IF l_ret_flag = TRUE then
1432 hr_utility.set_location('NAR ret step ' ||l_retained_grade.temp_step,10);
1433 hr_utility.set_location('NAR pay plan '||p_pay_calc_data.pay_plan,20);
1434 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1435 ,l_retained_grade.temp_step);
1436 hr_utility.set_location('NAR new step after getting the step ' ||l_new_step_or_rate,10);
1437 -- End Bug 3021003
1438 ELSE
1439 l_new_step_or_rate := p_retained_grade.temp_step;
1440 END IF;
1441 else
1442 -- Bug 3021003
1443 is_retained_ia(p_pay_calc_data.person_id,
1444 p_pay_calc_data.effective_date,
1445 l_retained_grade.pay_plan,
1446 l_retained_grade.grade_or_level,
1447 l_retained_grade.step_or_rate,
1448 l_retained_grade.temp_step,
1449 l_ret_flag);
1450 IF l_ret_flag = TRUE then
1451 hr_utility.set_location('NAR ret step ' ||l_retained_grade.step_or_rate,10);
1452 hr_utility.set_location('NAR pay plan '||p_pay_calc_data.pay_plan,20);
1453 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1454 ,l_retained_grade.step_or_rate);
1455 hr_utility.set_location('NAR new step after getting the step ' ||l_new_step_or_rate,10);
1456 ELSE
1457 l_new_step_or_rate := l_retained_grade.step_or_rate;
1458 END IF; -- IF is_retained_ia End Bug 3021003
1459 END IF; -- if p_retained_grade.temp_step is not null
1460 else
1461 if p_retained_grade.temp_step is not null then
1462
1463 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1464 ,p_retained_grade.temp_step);
1465 else
1466 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_retained_grade.pay_plan
1467 ,p_retained_grade.step_or_rate);
1468 end if;
1469 end if;
1470
1471
1472 -- Bug 3294560 Hard coding of 'GS' replaced with l_pay_plan
1473 IF p_retained_grade.temp_step IS NOT NULL THEN
1474
1475 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1476 ,p_pay_calc_data.grade_or_level
1477 ,l_new_step_or_rate
1478 ,p_pay_calc_data.effective_date);
1479 ELSE
1480 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1481 ,p_retained_grade.grade_or_level
1482 ,l_new_step_or_rate
1483 ,p_pay_calc_data.effective_date);
1484 END IF;
1485 ELSE
1486 --- Bug 1953725 End
1487 if p_retained_grade.temp_step is not null then
1488 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1489 ,p_pay_calc_data.grade_or_level
1490 ,p_retained_grade.temp_step
1491 ,p_pay_calc_data.effective_date);
1492 else
1493 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1494 ,p_retained_grade.grade_or_level
1495 ,p_retained_grade.step_or_rate
1496 ,p_pay_calc_data.effective_date);
1497 end if;
1498 END IF;
1499 ELSE
1500 --
1501 l_default_basic_pay := get_GM_GH_def_basic_pay(p_pay_calc_data
1502 ,p_retained_grade);
1503 --
1504 END IF;
1505
1506 END IF;
1507
1508 l_adjustment_percentage := get_lpa_percentage(p_pay_calc_data.duty_station_id
1509 ,p_pay_calc_data.effective_date);
1510 -- Now do the comparison!
1511 l_default_adj_basic_pay := NVL(l_default_basic_pay,0)
1512 + ROUND(l_default_basic_pay *
1513 (NVL(l_adjustment_percentage,0)/100),0);
1514 --
1515 IF l_default_adj_basic_pay > l_basic_pay_pa THEN
1516 l_locality_adj := l_default_adj_basic_pay - l_basic_pay_pa;
1517 ELSE
1518 l_locality_adj := 0;
1519 END IF;
1520 --
1521 ELSIF p_pay_calc_data.pay_rate_determinant = 'M' THEN
1522 IF p_pay_calc_out_data.basic_pay >= p_pay_calc_data.current_adj_basic_pay THEN
1523 l_locality_adj := 0;
1524 --
1525 ELSE
1526 l_locality_adj := ghr_pay_calc.convert_amount
1527 (p_pay_calc_data.current_adj_basic_pay - p_pay_calc_out_data.basic_pay
1528 ,p_pay_calc_data.pay_basis
1529 ,'PA');
1530 --
1531 END IF;
1532
1533 ELSE
1534 -- should never have really got here as the basic pay should have stopped us
1535 -- calculating if we didn't know how to
1536 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
1537 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
1538 raise ghr_pay_calc.unable_to_calculate;
1539 END IF; -- End of PRD checks
1540 --
1541 END IF; -- End of LEO checks
1542 --
1543 END IF; -- End of PRD=M and 894
1544 ELSE
1545 -- We do not calculate locality adjustment for other pay plans
1546 l_locality_adj := 0;
1547 --
1548 END IF;
1549 --
1550 -- The above should just set l_locality_adj now set the real out parameter
1551 --
1552 p_pay_calc_out_data.locality_adj := l_locality_adj;
1553
1554 EXCEPTION
1555 WHEN others THEN
1556 -- Reset IN OUT parameters and set OUT parameters
1557 p_pay_calc_out_data := l_pay_calc_out_data;
1558 RAISE;
1559 --
1560 END get_locality_adj;
1561 --
1562 --------------------------- <get_adj_basic_pay> ----------------------------------
1563 PROCEDURE get_adj_basic_pay (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1564 ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1565
1566 --
1567 l_locality_adj_conv NUMBER;
1568 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1569
1570 BEGIN
1571
1572 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1573 --
1574 IF p_pay_calc_data.pay_rate_determinant = 'M' THEN
1575 --
1576 IF (p_pay_calc_data.pay_plan IN ('GS','GM') AND p_pay_calc_data.noa_code = '894') THEN
1577 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_out_data.basic_pay +
1578 p_pay_calc_out_data.locality_adj;
1579
1580 -- Basically M's keep the same adjusted basic pay unless basic pay is higher!
1581 ELSIF p_pay_calc_out_data.basic_pay >= p_pay_calc_data.current_adj_basic_pay THEN
1582 p_pay_calc_out_data.locality_adj := 0;
1583 --
1584 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_out_data.basic_pay;
1585 --
1586 ELSE
1587 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_data.current_adj_basic_pay;
1588 --
1589 END IF;
1590 --
1591 ELSE
1592 -- convert the locality_adj (which is always 'PA') to the pay_basis of the
1593 -- basic pay so they can then be added together
1594 --
1595 l_locality_adj_conv := ghr_pay_calc.convert_amount(p_pay_calc_out_data.locality_adj
1596 ,'PA'
1597 ,p_pay_calc_data.pay_basis);
1598 --
1599 p_pay_calc_out_data.adj_basic_pay := NVL(p_pay_calc_out_data.basic_pay,0)
1600 + NVL(l_locality_adj_conv,0);
1601 END IF;
1602
1603 EXCEPTION
1604 WHEN others THEN
1605 -- Reset IN OUT parameters and set OUT parameters
1606 p_pay_calc_out_data := l_pay_calc_out_data;
1607 RAISE;
1608
1609 END get_adj_basic_pay;
1610
1611 --------------------------- <get_ppi_amount> --------------------------------------
1612 FUNCTION get_ppi_amount (p_ppi_code IN VARCHAR2
1613 ,p_amount IN NUMBER
1614 ,p_pay_basis IN VARCHAR2)
1615
1616 RETURN NUMBER IS
1617 -- bug 710122 need to convert amount to PA before doing calc
1618 CURSOR cur_ppi IS
1619 SELECT ppi.ppi_percentage
1620 FROM ghr_premium_pay_indicators ppi
1621 WHERE code = p_ppi_code;
1622
1623 l_amount_pa NUMBER;
1624 l_ppi_percentage NUMBER;
1625 l_ret_val NUMBER;
1626 BEGIN
1627 IF p_ppi_code IS NOT NULL THEN
1628 FOR cur_ppi_rec IN cur_ppi LOOP
1629 l_ppi_percentage := cur_ppi_rec.ppi_percentage;
1630 END LOOP;
1631
1632 -- rounding??
1633 --
1634 l_amount_pa := convert_amount(p_amount,p_pay_basis ,'PA');
1635 l_ret_val := TRUNC(l_amount_pa * NVL(l_ppi_percentage,0) / 100 , 0); --Bug 3067420 changed ROUND -> TRUNC
1636
1637 ELSE
1638 l_ret_val := NULL;
1639 END IF;
1640
1641 RETURN(l_ret_val);
1642
1643 END get_ppi_amount;
1644
1645 --------------------------- <get_other_pay_amount> --------------------------------
1646 PROCEDURE get_other_pay_amount (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1647 ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1648
1649 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1650
1651 BEGIN
1652
1653 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1654
1655 -- should not need to worry about the pay basis as they are all in the same basis
1656 -- If ALL the values are null then return null as opposed to 0:
1657 -- NOTE: these should all be per annum amounts
1658 IF p_pay_calc_out_data.au_overtime IS NULL
1659 AND p_pay_calc_out_data.availability_pay IS NULL
1660 AND p_pay_calc_out_data.retention_allowance IS NULL
1661 AND p_pay_calc_data.supervisory_differential IS NULL
1662 AND p_pay_calc_data.staffing_differential IS NULL THEN
1663 p_pay_calc_out_data.other_pay_amount := NULL;
1664 ELSE
1665 p_pay_calc_out_data.other_pay_amount := NVL(p_pay_calc_out_data.au_overtime,0)
1666 + NVL(p_pay_calc_out_data.availability_pay,0)
1667 + NVL(p_pay_calc_out_data.retention_allowance,0)
1668 + NVL(p_pay_calc_data.supervisory_differential,0)
1669 + NVL(p_pay_calc_data.staffing_differential,0);
1670 END IF;
1671 --
1672
1673 EXCEPTION
1674 WHEN others THEN
1675 -- Reset IN OUT parameters and set OUT parameters
1676 p_pay_calc_out_data := l_pay_calc_out_data ;
1677 RAISE;
1678 END get_other_pay_amount;
1679
1680 --------------------------- <get_total_salary> ------------------------------------
1681 PROCEDURE get_total_salary (p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
1682 ,p_pay_basis IN VARCHAR2) IS
1683
1684 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1685 BEGIN
1686
1687 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1688
1689 -- 24-Nov-2003 For Other Pay conversion is not required due to FWS Retention Calc. Changes
1690 p_pay_calc_out_data.total_salary := NVL(p_pay_calc_out_data.adj_basic_pay,0)
1691 + NVL(p_pay_calc_out_data.other_pay_amount,0);
1692
1693 EXCEPTION
1694 WHEN others THEN
1695 -- Reset IN OUT parameters and set OUT parameters
1696 p_pay_calc_out_data := l_pay_calc_out_data ;
1697 RAISE;
1698
1699 END get_total_salary;
1700
1701 PROCEDURE main_pay_calc (p_person_id IN per_people_f.person_id%TYPE
1702 ,p_position_id IN hr_all_positions_f.position_id%TYPE
1703 ,p_noa_family_code IN ghr_families.noa_family_code%TYPE
1704 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
1705 ,p_second_noa_code IN ghr_nature_of_actions.code%TYPE
1706 ,p_first_action_la_code1 IN ghr_pa_requests.first_action_la_code1%TYPE
1707 ,p_effective_date IN DATE
1708 ,p_pay_rate_determinant IN VARCHAR2
1709 ,p_pay_plan IN VARCHAR2
1710 ,p_grade_or_level IN VARCHAR2
1711 ,p_step_or_rate IN VARCHAR2
1712 ,p_pay_basis IN VARCHAR2
1713 ,p_user_table_id IN NUMBER
1714 ,p_duty_station_id IN NUMBER
1715 ,p_auo_premium_pay_indicator IN VARCHAR2
1716 ,p_ap_premium_pay_indicator IN VARCHAR2
1717 ,p_retention_allowance IN NUMBER
1718 ,p_to_ret_allow_percentage IN NUMBER
1719 ,p_supervisory_differential IN NUMBER
1720 ,p_staffing_differential IN NUMBER
1721 ,p_current_basic_pay IN NUMBER
1722 ,p_current_adj_basic_pay IN NUMBER
1723 ,p_current_step_or_rate IN VARCHAR2
1724 ,p_pa_request_id IN NUMBER
1725 ,p_open_range_out_basic_pay IN NUMBER DEFAULT NULL
1726 ,p_open_out_locality_adj IN NUMBER DEFAULT NULL
1727 ,p_basic_pay OUT NOCOPY NUMBER
1728 ,p_locality_adj OUT NOCOPY NUMBER
1729 ,p_adj_basic_pay OUT NOCOPY NUMBER
1730 ,p_total_salary OUT NOCOPY NUMBER
1731 ,p_other_pay_amount OUT NOCOPY NUMBER
1732 ,p_to_retention_allowance OUT NOCOPY NUMBER
1733 ,p_ret_allow_perc_out OUT NOCOPY NUMBER
1734 ,p_au_overtime OUT NOCOPY NUMBER
1735 ,p_availability_pay OUT NOCOPY NUMBER
1736 -- FWFA Changes
1737 ,p_calc_pay_table_id OUT NOCOPY NUMBER
1738 ,p_pay_table_id OUT NOCOPY NUMBER
1739 -- FWFA Changes
1740 ,p_out_step_or_rate OUT NOCOPY VARCHAR2
1741 ,p_out_pay_rate_determinant OUT NOCOPY VARCHAR2
1742 ,p_out_to_grade_id OUT NOCOPY NUMBER
1743 ,p_out_to_pay_plan OUT NOCOPY VARCHAR2
1744 ,p_out_to_grade_or_level OUT NOCOPY VARCHAR2
1745 ,p_PT_eff_start_date OUT NOCOPY DATE
1746 ,p_open_basicpay_field OUT NOCOPY BOOLEAN
1747 ,p_open_pay_fields OUT NOCOPY BOOLEAN
1748 ,p_message_set OUT NOCOPY BOOLEAN
1749 ,p_calculated OUT NOCOPY BOOLEAN
1750 ,p_open_localityadj_field OUT NOCOPY BOOLEAN
1751 ) IS
1752 --
1753 l_ghr_pa_request_rec ghr_pa_requests%ROWTYPE;
1754 --
1755 l_effective_date DATE;
1756 --
1757 l_position_id hr_all_positions_f.position_id%TYPE;
1758 l_noa_family_code ghr_families.noa_family_code%TYPE;
1759 l_noa_code ghr_nature_of_actions.code%TYPE;
1760 l_second_noa_code ghr_nature_of_actions.code%TYPE;
1761 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
1762 l_pay_rate_determinant VARCHAR2(30);
1763 l_pay_plan VARCHAR2(30);
1764 l_grade_or_level VARCHAR2(60);
1765 l_step_or_rate VARCHAR2(30);
1766 l_pay_basis VARCHAR2(30);
1767 l_duty_station_id NUMBER;
1768 l_auo_premium_pay_indicator VARCHAR2(30);
1769 l_ap_premium_pay_indicator VARCHAR2(30);
1770 l_retention_allowance NUMBER;
1771 l_dummy NUMBER;
1772 l_to_retention_allowance NUMBER;
1773 l_to_ret_allow_percentage NUMBER(15,2);
1774 l_ret_calc_perc NUMBER(15,2);
1775 l_supervisory_differential NUMBER;
1776 l_staffing_differential NUMBER;
1777 l_current_basic_pay NUMBER;
1778 l_current_adj_basic_pay NUMBER;
1779 l_current_step_or_rate VARCHAR2(30);
1780 -- Bug#5120116
1781 l_capped_other_pay NUMBER;
1782 l_prd_d_pay_amount NUMBER; --Bug# 7557159
1783 --
1784 l_second_noa_family_code ghr_families.noa_family_code%TYPE;
1785 l_run_pay_calc_again BOOLEAN := FALSE;
1786 --
1787 l_pay_calc_data ghr_pay_calc.pay_calc_in_rec_type; -- This is the main IN record structure
1788 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type; -- This is the main OUT record structure
1789 l_retained_grade ghr_pay_calc.retained_grade_rec_type; -- This contains retained grade info if needed
1790 --
1791 l_message_set BOOLEAN := FALSE;
1792 l_calculated BOOLEAN := TRUE;
1793 --
1794 l_user_table_id pay_user_tables.user_table_id%TYPE;
1795 --
1796
1797 l_proc VARCHAR2(30) := 'main_pay_calc';
1798
1799 cursor cur_ex_emp is
1800 select 1
1801 from per_person_types pet1,
1802 per_people_f per1
1803 where pet1.person_type_id = per1.person_type_id
1804 and per1.person_id = p_person_id
1805 and nvl(p_effective_date,trunc(sysdate))
1806 between per1.effective_start_date and per1.effective_end_date
1807 and pet1.system_person_type = 'EX_EMP';
1808
1809 l_ex_emp BOOLEAN;
1810 l_session ghr_history_api.g_session_var_type;
1811 v_session ghr_history_api.g_session_var_type;
1812
1813 ----IA Correction Logic.
1814 cursor c_pa_req1 is
1815 select pa_notification_id
1816 from ghr_pa_requests
1817 where pa_request_id = l_ghr_pa_request_rec.altered_pa_request_id;
1818
1819 l_ia_flag varchar2(30);
1820 l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1821 l_retro_first_noa ghr_nature_of_actions.code%type;
1822 l_retro_second_noa ghr_nature_of_actions.code%type;
1823
1824 l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1825 l_ia_effective_date ghr_pa_requests.effective_date%type;
1826 l_ia_retro_first_noa ghr_nature_of_actions.code%type;
1827 l_ia_retro_second_noa ghr_nature_of_actions.code%type;
1828 l_old_effective_date date;
1829 l_old_ret_allow NUMBER;
1830 l_value VARCHAR2(60);
1831 l_multi_error_flag BOOLEAN;
1832 l_from_basic_pay NUMBER;
1833 l_from_retention_allowance NUMBER;
1834
1835 -- Bug 3248061
1836 l_altered_pa_req_id ghr_pa_requests.altered_pa_request_id%type;
1837 -- Bug 3245692
1838 l_from_pay_basis ghr_pa_requests.from_pay_basis%type;
1839
1840 CURSOR c_get_from_pay_basis(c_pa_request_id IN ghr_pa_requests.pa_request_id%type) IS
1841 SELECT from_pay_basis
1842 FROM ghr_pa_requests
1843 WHERE pa_request_id = c_pa_request_id;
1844
1845 CURSOR c_get_alt_pareq_id(c_pa_request_id IN ghr_pa_requests.pa_request_id%type) IS
1846 SELECT altered_pa_request_id
1847 FROM ghr_pa_requests
1848 WHERE pa_request_id = c_pa_request_id;
1849
1850 CURSOR c_get_notification_id(c_alt_pa_request_id IN ghr_pa_requests.pa_request_id%type) IS
1851 SELECT pa_notification_id
1852 FROM ghr_pa_requests
1853 WHERE pa_request_id = c_alt_pa_request_id;
1854 -- End Bug 3248061
1855
1856 -- Bug#5120116 created the cursor to get assignment id.
1857 CURSOR c_asg_rec(c_person_id NUMBER, c_effective_date DATE) IS
1858 SELECT assignment_id
1859 FROM per_all_assignments_f
1860 WHERE person_id = c_person_id
1861 AND assignment_type = 'E'
1862 AND c_effective_date between effective_start_date and effective_end_date;
1863
1864 -- Bug#6154261
1865 cursor espayplan
1866 is
1867 select 'Y'
1868 from ghr_pay_plans
1869 where equivalent_pay_plan = 'ES'
1870 and pay_plan = l_pay_plan;
1871
1872 esplan_flag varchar2(1);
1873
1874 --Bug # 9156680
1875
1876 cursor c_pay_tab_essl is
1877 select 1 from pay_user_tables
1878 where substr(user_table_name,1,4) = 'ESSL'
1879 and user_table_id = l_user_table_id;
1880
1881 l_essl_table BOOLEAN :=FALSE;
1882
1883 --Bug #6154261
1884 l_assignment_id NUMBER;
1885
1886 l_max_RA NUMBER;
1887
1888
1889 ------ GPPA Update 46 - GM Pay plan will change to GS pay plan for 890 NOAC
1890 l_business_group_id per_positions.organization_id%TYPE;
1891
1892 CURSOR GET_GRADE_ID (v_pay_plan varchar2, v_grade_or_level varchar2) IS
1893 SELECT grd.grade_id grade_id
1894 FROM per_grades grd,
1895 per_grade_definitions gdf
1896 WHERE gdf.segment1 = v_pay_plan
1897 AND gdf.segment2 = v_grade_or_level
1898 AND grd.grade_definition_id = gdf.grade_definition_id
1899 AND grd.business_group_id = l_business_group_id;
1900
1901 l_grade_id NUMBER;
1902 l_pay_plan_changed BOOLEAN;
1903 l_default_pay_plan VARCHAR2(5);
1904 ------ GPPA Update 46 end
1905
1906 --Begin Bug# 8453042
1907 sl_eql_pay_plan ghr_pay_plans.equivalent_pay_plan%type;
1908
1909 cursor c_sl_eql_pp is
1910 select equivalent_pay_plan from ghr_pay_plans
1911 where PAY_PLAN = l_pay_plan;
1912 --End Bug# 8453042
1913
1914 --Bug # 9513518
1915 CURSOR get_locality_pay_area
1916 IS
1917 SELECT lc.locality_pay_area_code
1918 FROM ghr_duty_stations_f ds, ghr_locality_pay_areas_f lc
1919 WHERE ds.duty_station_id = NVL(p_duty_station_id,l_ghr_pa_request_rec.duty_station_id)
1920 AND ds.locality_pay_area_id = lc.locality_pay_area_id
1921 AND p_effective_date between ds.effective_start_date and ds.effective_end_date
1922 AND p_effective_date between lc.effective_start_date and lc.effective_end_date;
1923
1924 l_locality_pay_area_code ghr_locality_pay_areas_f.locality_pay_area_code%type;
1925 --End Bug # 9513518
1926
1927
1928 BEGIN
1929
1930 ------ GPPA Update 46 Start
1931 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
1932 l_pay_plan_changed := FALSE;
1933 ------ GPPA Update 46 End
1934
1935 l_ex_emp := FALSE;
1936 for cur_ex_emp_rec in cur_ex_emp
1937 LOOP
1938 l_ex_emp := TRUE;
1939 END LOOP;
1940
1941 hr_utility.set_location('Entering main pay calc...'|| l_proc,5);
1942 -----Bug 3288419 Initialize the global variables.
1943 g_gm_unadjd_basic_pay := NULL;
1944 gm_unadjusted_pay_flg := NULL;
1945 -- FWFA Changes Bug#4444609
1946 g_pay_table_upd_flag := FALSE;
1947 g_fwfa_pay_calc_flag := FALSE;
1948 g_gl_upd_flag := FALSE;
1949 g_fw_equiv_pay_plan := FALSE; --Bug 5218445
1950 g_out_to_pay_plan := NULL;
1951 -- FWFA Changes
1952 l_pay_calc_out_data.open_pay_fields := FALSE;
1953 l_pay_calc_out_data.open_basicpay_field := FALSE;
1954 -- bug# 13103038 PRD2 Process
1955 ghr_pay_calc.prd2_process := FALSE;
1956 -- Bug#5132113
1957 l_pay_calc_out_data.open_localityadj_field := FALSE;
1958
1959 BEGIN
1960 -- This is the main starting point of pay calc.
1961 -- Basically the user gives us the IN parameters needed to calculate
1962 -- pay and if we can we do it if not
1963 -- we pass the same parameters on to a custome defined procedure
1964 --
1965 -- Bug For 866 use effective date + 1
1966 --
1967 -- Bug 705022 Now overrides the previous bug!! :- 866 also uses simply effective_date
1968 IF NVL(p_noa_code,'@@!!##') = '866' OR NVL(p_second_noa_code,'@@!!##') = '866' THEN
1969 l_effective_date := p_effective_date + 1;
1970 ELSE
1971 l_effective_date := p_effective_date;
1972 END IF;
1973 --
1974 IF ( p_noa_code = '702' OR p_second_noa_code = '702' )
1975 AND ( p_pay_rate_determinant in ('A','B','E','F','U','V') ) THEN
1976 hr_utility.set_message(8301,'GHR_38697_RG_PROMOTE');
1977 raise pay_calc_message;
1978 END IF;
1979
1980 -- For corrections we need to go and get alot of the IN parameters
1981 -- as the form does not populate them:
1982 IF p_noa_family_code = 'CORRECT' THEN
1983 -- Must have a pa_request_id for a CORRECT family
1984 ghr_pc_basic_pay.g_noa_family_code := 'CORRECT';
1985 IF p_pa_request_id IS NULL THEN
1986 hr_utility.set_message(8301,'GHR_38398_PAY_CALC_NO_PAR_ID');
1987 raise pay_calc_message;
1988 END IF;
1989 ghr_corr_canc_sf52.build_corrected_sf52
1990 (p_pa_request_id => p_pa_request_id
1991 ,p_noa_code_correct => p_second_noa_code
1992 ,p_sf52_data_result => l_ghr_pa_request_rec
1993 ,p_called_from => 'FROM_PAYCAL');
1994
1995 l_position_id := NVL(p_position_id,l_ghr_pa_request_rec.to_position_id);
1996 l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family(p_second_noa_code);
1997 l_noa_code := p_second_noa_code;
1998 l_second_noa_code := NULL;
1999 l_first_action_la_code1 := NVL(p_first_action_la_code1 ,l_ghr_pa_request_rec.first_action_la_code1);
2000 l_pay_rate_determinant := NVL(p_pay_rate_determinant ,l_ghr_pa_request_rec.pay_rate_determinant);
2001 l_pay_plan := NVL(p_pay_plan ,l_ghr_pa_request_rec.to_pay_plan);
2002 l_grade_or_level := NVL(p_grade_or_level ,l_ghr_pa_request_rec.to_grade_or_level);
2003 l_step_or_rate := NVL(p_step_or_rate ,l_ghr_pa_request_rec.to_step_or_rate);
2004 l_pay_basis := NVL(p_pay_basis ,l_ghr_pa_request_rec.to_pay_basis);
2005 l_duty_station_id := NVL(p_duty_station_id ,l_ghr_pa_request_rec.duty_station_id);
2006 l_auo_premium_pay_indicator := NVL(p_auo_premium_pay_indicator,l_ghr_pa_request_rec.to_auo_premium_pay_indicator);
2007 l_ap_premium_pay_indicator := NVL(p_ap_premium_pay_indicator ,l_ghr_pa_request_rec.to_ap_premium_pay_indicator);
2008 l_retention_allowance := NVL(p_retention_allowance ,l_ghr_pa_request_rec.to_retention_allowance);
2009 l_to_ret_allow_percentage := NVL(p_to_ret_allow_percentage ,l_ghr_pa_request_rec.to_retention_allow_percentage);
2010 l_supervisory_differential := NVL(p_supervisory_differential ,l_ghr_pa_request_rec.to_supervisory_differential);
2011 l_staffing_differential := NVL(p_staffing_differential ,l_ghr_pa_request_rec.to_staffing_differential);
2012 l_current_basic_pay := NVL(p_current_basic_pay ,l_ghr_pa_request_rec.from_basic_pay);
2013 l_current_adj_basic_pay := NVL(p_current_adj_basic_pay ,l_ghr_pa_request_rec.from_adj_basic_pay);
2014 l_current_step_or_rate := NVL(p_current_step_or_rate ,l_ghr_pa_request_rec.from_step_or_rate);
2015
2016 -- For correction actions take pay basis from ghr_pa_requests table itself.
2017 FOR ctr_from_pay_basis IN c_get_from_pay_basis(p_pa_request_id) LOOP
2018 l_from_pay_basis := ctr_from_pay_basis.from_pay_basis;
2019 END LOOP;
2020
2021 ELSE
2022 ghr_pc_basic_pay.g_noa_family_code := NULL;
2023 l_position_id := p_position_id;
2024 l_noa_family_code := p_noa_family_code;
2025 l_noa_code := p_noa_code;
2026 l_second_noa_code := p_second_noa_code;
2027 l_first_action_la_code1 := p_first_action_la_code1;
2028 l_pay_rate_determinant := p_pay_rate_determinant;
2029 l_pay_plan := p_pay_plan;
2030 l_grade_or_level := p_grade_or_level;
2031 l_step_or_rate := p_step_or_rate;
2032 l_pay_basis := p_pay_basis;
2033 l_duty_station_id := p_duty_station_id;
2034 l_auo_premium_pay_indicator := p_auo_premium_pay_indicator;
2035 l_ap_premium_pay_indicator := p_ap_premium_pay_indicator;
2036 l_retention_allowance := p_retention_allowance;
2037 l_to_ret_allow_percentage := p_to_ret_allow_percentage;
2038 l_supervisory_differential := p_supervisory_differential;
2039 l_staffing_differential := p_staffing_differential;
2040 l_current_basic_pay := p_current_basic_pay;
2041 l_current_adj_basic_pay := p_current_adj_basic_pay;
2042 l_current_step_or_rate := p_current_step_or_rate;
2043
2044 -- 3245692 Get From pay basis from the function get_pay_basis
2045 l_from_pay_basis := get_pay_basis(
2046 p_effective_date => p_effective_date,
2047 p_pa_request_id => p_pa_request_id,
2048 p_person_id => p_person_id);
2049 IF (l_from_pay_basis = '-1') THEN
2050 hr_utility.set_message(8301,'GHR_38020_PAY_CALC_NO_PAY_BAS');
2051 raise pay_calc_message;
2052 END IF;
2053 -- End 3245692
2054
2055 END IF;
2056
2057 hr_utility.set_location(' Passed Noa Fam Code = ' || p_noa_family_code,5);
2058 hr_utility.set_location(' Passed l_retention_allowance = ' || to_char(l_retention_allowance),5);
2059 hr_utility.set_location(' Passed l_to_ret_allow_percentage = ' || to_char(l_to_ret_allow_percentage),5);
2060
2061 ghr_history_api.get_g_session_var(l_session);
2062 hr_utility.set_location( 'Get Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
2063 hr_utility.set_location( 'Get Ses ASGID is ' || to_char(l_session.assignment_id), 15);
2064 -- set values of session variables IA fetch sake.
2065 v_session.pa_request_id := l_session.pa_request_id;
2066 v_session.noa_id := l_session.noa_id;
2067 v_session.fire_trigger := l_session.fire_trigger;
2068 v_session.date_Effective := l_session.date_Effective;
2069 v_session.person_id := l_session.person_id;
2070 v_session.program_name := l_session.program_name;
2071 v_session.assignment_id := l_session.assignment_id;
2072 v_session.altered_pa_request_id := l_session.altered_pa_request_id;
2073 v_session.noa_id_correct := l_session.noa_id_correct;
2074
2075
2076 IF l_session.noa_id_correct is not null then
2077 ghr_pc_basic_pay.g_noa_family_code := 'CORRECT';
2078 -- First determine presence of retro active actions
2079 l_ia_flag := 'N';
2080 -- Bug 3248061 Sundar
2081 -- l_ghr_pa_request_rec.pa_request_id becomes null for correction record. So in that case
2082 -- Use p_pa_request_id which is passed instead of the value from the record l_ghr_pa_request_rec
2083 IF (l_ghr_pa_request_rec.pa_request_id IS NULL) THEN
2084 -- Get the Original Id of this correction action
2085 FOR ctr_alt_pareq_id IN c_get_alt_pareq_id(p_pa_request_id) LOOP
2086 l_altered_pa_req_id := ctr_alt_pareq_id.altered_pa_request_id;
2087 END LOOP;
2088 -- Get Notification ID for this Original pa_request_id
2089 FOR ctr_get_notify_id IN c_get_notification_id(l_altered_pa_req_id) LOOP
2090 l_pa_notification_id := ctr_get_notify_id.pa_notification_id;
2091 END LOOP;
2092 ELSE
2093 FOR c_pa_rec1 IN c_pa_req1 LOOP
2094 l_pa_notification_id := c_pa_rec1.pa_notification_id;
2095 END LOOP;
2096 END IF;
2097 -- End Bug 3248061
2098 -- Get the pa_notification_id from the original action
2099
2100 --BUG #7216635 added the parameter p_noa_id_correct
2101 GHR_APPROVED_PA_REQUESTS.determine_ia(
2102 p_pa_request_id => l_ghr_pa_request_rec.altered_pa_request_id,
2103 p_pa_notification_id => l_pa_notification_id,
2104 p_person_id => p_person_id,
2105 p_effective_date => l_effective_date,
2106 p_noa_id_correct => l_session.noa_id_correct,
2107 p_retro_pa_request_id => l_retro_pa_request_id,
2108 p_retro_eff_date => l_ia_effective_date,
2109 p_retro_first_noa => l_ia_retro_first_noa,
2110 p_retro_second_noa => l_ia_retro_second_noa);
2111 if l_ia_effective_date is NOT NULL THEN
2112 l_ia_flag := 'Y';
2113 IF l_retro_first_noa = '866' then
2114 l_ia_effective_date := l_ia_effective_date + 1;
2115 END IF;
2116 end if;
2117
2118 hr_utility.set_location( 'l_ia_effective_date ' || to_char(l_ia_effective_date,'YYYY/MM/DD'),15);
2119
2120 hr_utility.set_location( 'Ses NOAIDCORR is ' || to_char(v_session.noa_id_correct), 15);
2121 hr_utility.set_location( 'Ses ASGID is ' || to_char(v_session.assignment_id), 15);
2122
2123 ghr_history_api.reinit_g_session_var;
2124 l_session.noa_id_correct := NULL;
2125 hr_utility.set_location( 'Set Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
2126 ghr_history_api.set_g_session_var(l_session);
2127
2128 if l_ia_effective_date is not null then
2129 l_old_effective_date := l_ia_effective_date - 1;
2130 else
2131 l_old_effective_date := l_effective_date - 1;
2132 end if;
2133
2134 hr_utility.set_location( 'l_old_effective_date ' || to_char(l_old_effective_date,'YYYY/MM/DD'),15);
2135 hr_utility.set_location( 'Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
2136 hr_utility.set_location( 'Ses ASGID is ' || to_char(l_session.assignment_id), 15);
2137 hr_utility.set_location( 'Fetch old Retention Allowance' || l_proc,15);
2138
2139 -------Assignment id is different then need to be checked the following logic.
2140
2141 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2142 ,p_input_value_name => 'Amount'
2143 ,p_assignment_id => l_session.assignment_id
2144 ,p_effective_date => l_old_effective_date
2145 ,p_value => l_value
2146 ,p_multiple_error_flag => l_multi_error_flag);
2147 l_old_ret_allow := to_number(l_value);
2148 hr_utility.set_location( 'l_old_ret_allow is ' || to_char(l_old_ret_allow), 15);
2149
2150 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2151 ,p_input_value_name => 'Amount'
2152 ,p_assignment_id => l_session.assignment_id
2153 ,p_effective_date => nvl(l_ia_effective_date,l_effective_date)
2154 ,p_value => l_value
2155 ,p_multiple_error_flag => l_multi_error_flag);
2156 l_from_retention_allowance := to_number(l_value);
2157 hr_utility.set_location( 'l_from_retention_allowance is ' || to_char(l_from_retention_allowance), 15);
2158 --
2159 -- Modifying the input values from percent to percentage for Payroll Integration
2160 --
2161 ghr_api.retrieve_element_entry_value (p_element_name => 'Basic Salary Rate'
2162 ,p_input_value_name => 'Rate'
2163 ,p_assignment_id => l_session.assignment_id
2164 ,p_effective_date => nvl(l_ia_effective_date,l_effective_date)
2165 ,p_value => l_value
2166 ,p_multiple_error_flag => l_multi_error_flag);
2167 l_from_basic_pay := to_number(l_value);
2168 hr_utility.set_location( 'l_from_basic_pay is ' || to_char(l_from_basic_pay), 15);
2169
2170 if l_ia_effective_date is not null then
2171 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2172 ,p_input_value_name => 'Amount'
2173 ,p_assignment_id => l_session.assignment_id
2174 ,p_effective_date => l_ia_effective_date
2175 ,p_value => l_value
2176 ,p_multiple_error_flag => l_multi_error_flag);
2177
2178 l_ghr_pa_request_rec.to_retention_allowance := to_number(l_value);
2179 hr_utility.set_location( 'RA is ' || to_char(l_ghr_pa_request_rec.to_retention_allowance), 15);
2180 --
2181 -- Modifying the input values from percent to percentage for Payroll Integration
2182 --
2183 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2184 ,p_input_value_name => 'Percentage'
2185 ,p_assignment_id => l_session.assignment_id
2186 ,p_effective_date => l_ia_effective_date
2187 ,p_value => l_value
2188 ,p_multiple_error_flag => l_multi_error_flag);
2189
2190 l_ghr_pa_request_rec.to_retention_allow_percentage := to_number(l_value);
2191 hr_utility.set_location( 'RA % is ' || to_char(l_ghr_pa_request_rec.to_retention_allow_percentage),15);
2192
2193 ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
2194 ,p_input_value_name => 'Amount'
2195 ,p_assignment_id => l_session.assignment_id
2196 ,p_effective_date => l_ia_effective_date
2197 ,p_value => l_value
2198 ,p_multiple_error_flag => l_multi_error_flag);
2199
2200 l_ghr_pa_request_rec.to_supervisory_differential := to_number(l_value);
2201 hr_utility.set_location( 'SD is ' || to_char(l_ghr_pa_request_rec.to_supervisory_differential), 15);
2202
2203
2204 ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
2205 ,p_input_value_name => 'Premium Pay Ind'
2206 ,p_assignment_id => l_session.assignment_id
2207 ,p_effective_date => l_ia_effective_date
2208 ,p_value => l_value
2209 ,p_multiple_error_flag => l_multi_error_flag);
2210
2211 l_ghr_pa_request_rec.to_auo_premium_pay_indicator := l_value;
2212 hr_utility.set_location( 'AUO is ' || (l_ghr_pa_request_rec.to_auo_premium_pay_indicator), 15);
2213
2214
2215 ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
2216 ,p_input_value_name => 'Premium Pay Ind'
2217 ,p_assignment_id => l_session.assignment_id
2218 ,p_effective_date => l_ia_effective_date
2219 ,p_value => l_value
2220 ,p_multiple_error_flag => l_multi_error_flag);
2221
2222 l_ghr_pa_request_rec.to_ap_premium_pay_indicator := l_value;
2223 hr_utility.set_location( 'AP is ' || (l_ghr_pa_request_rec.to_ap_premium_pay_indicator), 15);
2224 l_auo_premium_pay_indicator := NVL(p_auo_premium_pay_indicator,l_ghr_pa_request_rec.to_auo_premium_pay_indicator);
2225 l_ap_premium_pay_indicator := NVL(p_ap_premium_pay_indicator ,l_ghr_pa_request_rec.to_ap_premium_pay_indicator);
2226 l_retention_allowance := NVL(p_retention_allowance ,l_ghr_pa_request_rec.to_retention_allowance);
2227 l_to_ret_allow_percentage := NVL(p_to_ret_allow_percentage ,l_ghr_pa_request_rec.to_retention_allow_percentage);
2228 l_supervisory_differential := NVL(p_supervisory_differential ,l_ghr_pa_request_rec.to_supervisory_differential);
2229 end if;
2230 ELSE
2231 ghr_pc_basic_pay.g_noa_family_code := NULL;
2232 END IF;
2233 ghr_history_api.reinit_g_session_var;
2234 -- Reset values of session variables IA fetch sake.
2235 l_session.pa_request_id := v_session.pa_request_id;
2236 l_session.noa_id := v_session.noa_id;
2237 l_session.fire_trigger := v_session.fire_trigger;
2238 l_session.date_Effective := v_session.date_Effective;
2239 l_session.person_id := v_session.person_id;
2240 l_session.program_name := v_session.program_name;
2241 l_session.assignment_id := v_session.assignment_id;
2242 l_session.altered_pa_request_id := v_session.altered_pa_request_id;
2243 l_session.noa_id_correct := v_session.noa_id_correct;
2244 ghr_history_api.set_g_session_var(l_session);
2245
2246 hr_utility.set_location( 'Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
2247 hr_utility.set_location( 'Ses ASGID is ' || to_char(l_session.assignment_id), 15);
2248
2249
2250 -- If we didn't get the user_table_id passed in then try and get it from the position_id
2251 -- This is specifically for the Form as I would expect any body else who uses this to get ALL
2252 -- details associated with position at the same time i.e pay_plan, grade_or_level, step_or_rate,
2253 -- pay_basis and at the same time get user_table_id. The reason the form doesn't have it is because
2254 -- it is not displayed on the SF52!
2255 IF p_user_table_id IS NULL THEN
2256 l_user_table_id := get_user_table_id (l_position_id
2257 ,NVL(p_effective_date,TRUNC(sysdate)) );
2258 ELSE
2259 l_user_table_id := p_user_table_id;
2260 END IF;
2261 --
2262 -- Dual Actions yuk!!!
2263 -- Only do this when we have a second NOA
2264 -- We basically have the following situations;
2265 -- First Noa Second NOA Solution
2266 -- MAIN No Pay Calc Use First NOA
2267 -- MAIN MAIN Use First NOA
2268 -- MAIN 892/893 Use First NOA
2269
2270 -- No Pay Calc No Pay Calc Use First NOA
2271 -- No Pay Calc MAIN Use Second NOA
2272 -- No Pay Calc 892/893 Not a valid option
2273
2274 -- 892/893 No Pay Calc Use First NOA
2275 -- 892/893 MAIN Use Second NOA
2276 -- 892/893 892/893 Run Pac Calc twice!!
2277 --
2278 -- Firstly if no second NOA then nothing to worry about!!
2279 IF l_second_noa_code IS NOT NULL THEN
2280 l_second_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family(p_second_noa_code);
2281 --
2282 IF l_second_noa_family_code IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
2283 ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY')
2284 AND (l_noa_code IN ('892','893')
2285 OR l_second_noa_family_code NOT IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
2286 ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY')
2287 ) THEN
2288 l_noa_family_code := l_second_noa_family_code;
2289 l_noa_code := l_second_noa_code;
2290 l_second_noa_code := NULL;
2291 ELSE
2292 IF l_noa_code IN ('892','893') AND l_second_noa_code IN ('892','893') THEN
2293 l_run_pay_calc_again := TRUE;
2294 END IF;
2295 -- keep the first one and blank the second
2296 l_second_noa_code := NULL;
2297 --
2298 END IF;
2299 --
2300 END IF;
2301
2302 --Bug # 9513518 Moved the cursor from Inner Loop
2303
2304 --ES Equivalent Pay Plan
2305 IF espayplan%isopen THEN
2306 CLOSE espayplan;
2307 END IF;
2308
2309 OPEN espayplan;
2310 FETCH espayplan into esplan_flag;
2311 CLOSE espayplan;
2312
2313 --
2314 -- populate a general record group that includes all the IN parameters and pass that around
2315 --
2316 l_pay_calc_data := populate_in_rec_structure
2317 (p_person_id
2318 ,l_position_id
2319 ,l_noa_family_code
2320 ,l_noa_code
2321 ,l_second_noa_code
2322 ,l_first_action_la_code1
2323 ,l_effective_date
2324 ,l_pay_rate_determinant
2325 ,l_pay_plan
2326 ,l_grade_or_level
2327 ,l_step_or_rate
2328 ,l_pay_basis
2329 ,l_user_table_id
2330 ,l_duty_station_id
2331 ,l_auo_premium_pay_indicator
2332 ,l_ap_premium_pay_indicator
2333 ,l_retention_allowance
2334 ,l_to_ret_allow_percentage
2335 ,l_supervisory_differential
2336 ,l_staffing_differential
2337 ,l_current_basic_pay
2338 ,l_current_adj_basic_pay
2339 ,l_current_step_or_rate
2340 ,p_pa_request_id
2341 ,p_open_range_out_basic_pay
2342 --Bug #5132113 added new parameter
2343 ,p_open_out_locality_adj
2344 );
2345 --
2346 -- Next do any validation
2347 --
2348 validate_in_data(l_pay_calc_data);
2349 --
2350 -- Bug#5482191 Fetch the PSI Value
2351 l_pay_calc_data.personnel_system_indicator := ghr_pa_requests_pkg.get_personnel_system_indicator
2352 (l_pay_calc_data.position_id,l_pay_calc_data.effective_date); -- MSL Percentage Changes Madhuri
2353 -- Bug#4758111 PRD 2 Processing.
2354 --- if nvl(ghr_msl_pkg.g_ses_msl_process,'N') <> 'Y' then
2355 if p_open_range_out_basic_pay is null then
2356
2357 -- Bug # 9156680 Modified for 890 to consider ESSL pay table validation
2358 for c_pay_tab_essl_rec in c_pay_tab_essl loop
2359 l_essl_table := TRUE;
2360 exit;
2361 end loop;
2362
2363 IF NOT(l_essl_table and l_noa_code = '890') THEN
2364 IF l_noa_code = '892' AND
2365 l_effective_date < to_date('2007/01/07','YYYY/MM/DD') THEN
2366
2367 IF NVL(esplan_flag,'N') = 'Y' THEN
2368 hr_utility.set_message(8301, 'GHR_38223_INV_PAY_PLAN_892');
2369 hr_utility.raise_error;
2370 END IF;
2371 END IF;
2372 ---Bug 7557159 Start
2373 IF p_pay_rate_determinant = 'D' THEN
2374 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2375 raise ghr_pay_calc.open_pay_range_mesg;
2376 END IF;
2377 ---Bug 7557159 End
2378
2379 ---Bug 13103038 Start
2380 IF l_pay_rate_determinant = '2' AND l_noa_code = '894'
2381 AND (l_session.noa_id_correct is not null OR l_first_action_la_code1 = 'VGR') THEN
2382 for rec_locality_pay_area in get_locality_pay_area
2383 loop
2384 l_locality_pay_area_code := rec_locality_pay_area.locality_pay_area_code;
2385 end loop;
2386 END IF;
2387 IF l_pay_rate_determinant = '2' AND l_noa_code = '894' AND l_locality_pay_area_code in ('HI','AK','ON')
2388 AND (l_session.noa_id_correct is not null OR l_first_action_la_code1 = 'VGR') THEN
2389 ghr_pay_calc.prd2_process := TRUE;
2390 hr_utility.set_message(8301, 'GHR_37461_PRD_2');
2391 raise ghr_pay_calc.open_pay_range_mesg;
2392 ELSE
2393 ghr_pay_calc.prd2_process := FALSE;
2394 END IF;
2395 ---Bug 13103038 end
2396
2397 if get_open_pay_range ( l_position_id
2398 , p_person_id
2399 , l_pay_rate_determinant
2400 , p_pa_request_id
2401 , NVL(p_effective_date,TRUNC(sysdate)) ) then
2402 --6489042 during appointment of ESSL employees of PRD 2 enabling basic pay for user entry
2403 if p_pay_rate_determinant <> '2' then
2404 hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2405 raise ghr_pay_calc.open_pay_range_mesg;
2406 elsif p_pay_rate_determinant = '2' and l_noa_family_code IN ('APP') then
2407 hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2408 raise ghr_pay_calc.open_pay_range_mesg;
2409 end if;
2410 end if;
2411 END IF;
2412 end if;
2413 --- end if;
2414 --
2415
2416 -- FWFA Modification: Moved the get_retained_grade_details call from get_basic_pay procedure
2417 -- to this position.
2418 IF l_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V','M') THEN
2419 BEGIN
2420 -- Begin Bug# 13619633, as per bug 1929382 for 866 we are passing eff date+1 this is casuing the issue.
2421 --- So passing eff date -1 for noa 866.
2422 IF NVL(p_noa_code,hr_api.g_varchar2) = '866' OR NVL(p_second_noa_code,hr_api.g_varchar2) = '866' THEN
2423 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (l_pay_calc_data.person_id
2424 ,l_pay_calc_data.effective_date-1
2425 ,l_pay_calc_data.pa_request_id);
2426 ELSE
2427 -- End Bug# 13619633
2428 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (l_pay_calc_data.person_id
2429 ,l_pay_calc_data.effective_date
2430 ,l_pay_calc_data.pa_request_id);
2431 END IF;-- Bug# 13619633
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434 RAISE;
2435 END;
2436 END IF;
2437 -- FWFA Modification
2438
2439 -- FWFA Changes.
2440 --6489042 during appointment of ESSL employees of PRD 2 not to handle the existing functionality of
2441 -- copying the already existing values
2442 IF (p_pay_rate_determinant = '2' and l_noa_family_code NOT IN ('APP')) THEN
2443 ---Bug 13103038 Start
2444
2445 IF l_pay_rate_determinant = '2' AND l_noa_code = '894'
2446 AND (l_session.noa_id_correct is not null OR l_first_action_la_code1 = 'VGR') THEN
2447 for rec_locality_pay_area in get_locality_pay_area
2448 loop
2449 l_locality_pay_area_code := rec_locality_pay_area.locality_pay_area_code;
2450 end loop;
2451 END IF;
2452 IF l_pay_rate_determinant = '2' AND l_noa_code = '894' AND (l_session.noa_id_correct is not null OR l_first_action_la_code1 = 'VGR')
2453 AND l_locality_pay_area_code in ('HI','AK','ON') THEN
2454 l_pay_calc_out_data.basic_pay := p_open_range_out_basic_pay;
2455 l_pay_calc_out_data.adj_basic_pay := p_open_range_out_basic_pay;
2456 l_pay_calc_out_data.open_basicpay_field := TRUE;
2457 ghr_pay_calc.prd2_process := TRUE;
2458 ELSE
2459 ghr_pay_calc.prd2_process := FALSE;
2460 l_pay_calc_out_data.basic_pay := l_pay_calc_data.current_basic_pay;
2461 l_pay_calc_out_data.adj_basic_pay := l_pay_calc_data.current_adj_basic_pay;
2462 END IF;
2463 ---Bug 13103038 end
2464 l_pay_calc_out_data.locality_adj := l_pay_calc_out_data.adj_basic_pay - l_pay_calc_out_data.basic_pay;
2465 -- Bug#5349609 Added the other pay components values to the out variables
2466 l_pay_calc_out_data.retention_allowance := l_pay_calc_data.retention_allowance;
2467 l_pay_calc_out_data.au_overtime := get_ppi_amount (l_pay_calc_data.auo_premium_pay_indicator
2468 ,l_pay_calc_out_data.adj_basic_pay
2469 ,l_pay_calc_data.pay_basis);
2470 l_pay_calc_out_data.availability_pay := get_ppi_amount (l_pay_calc_data.ap_premium_pay_indicator
2471 ,l_pay_calc_out_data.adj_basic_pay
2472 ,l_pay_calc_data.pay_basis);
2473 hr_utility.set_location('test asg id: '||l_session.assignment_id,10);
2474 hr_utility.set_location(' test eff dt: '||l_pay_calc_data.effective_date,10);
2475 FOR asg_rec IN c_asg_rec(l_pay_calc_data.person_id, l_pay_calc_data.effective_date)
2476 LOOP
2477 l_assignment_id := asg_rec.assignment_id;
2478 exit;
2479 END LOOP;
2480 l_capped_other_pay := ghr_pa_requests_pkg2.get_cop(l_assignment_id,
2481 l_pay_calc_data.effective_date);
2482
2483 -- Bug#5349609 If capped other pay is null, get the other pay value.
2484 IF l_capped_other_pay is NULL THEN
2485 get_other_pay_amount (l_pay_calc_data, l_pay_calc_out_data);
2486 get_total_salary (l_pay_calc_out_data,l_pay_calc_data.pay_basis);
2487 ELSE
2488 l_pay_calc_out_data.total_salary := NVL(l_pay_calc_out_data.adj_basic_pay,0) +
2489 NVL(l_capped_other_pay,0);
2490
2491 END IF;
2492 ELSE
2493 -- Bug#4758111
2494 IF fwfa_pay_calc(l_pay_calc_data,l_retained_grade) THEN
2495 special_rate_pay_calc (p_pay_calc_data => l_pay_calc_data
2496 ,p_pay_calc_out_data => l_pay_calc_out_data
2497 ,p_retained_grade => l_retained_grade
2498 );
2499 --Begin Bug 7046241
2500 IF l_pay_plan = 'GP' then
2501 --Setting process methods for GP pay plan
2502 l_pay_calc_out_data.open_localityadj_field := TRUE;
2503 If (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2504 and (l_first_action_la_code1 <> 'ZLM') then
2505 l_pay_calc_out_data.open_localityadj_field := FALSE;
2506 End If;
2507 If (l_pay_calc_data.noa_code = '893' OR l_pay_calc_data.second_noa_code = '893') then
2508 l_pay_calc_out_data.open_localityadj_field := FALSE;
2509 End If;
2510 --Restoring Market pay since market pay should not change because of special_rate_pay_calc
2511 if p_open_out_locality_adj is not null then
2512 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2513 l_pay_calc_out_data.adj_basic_pay := l_pay_calc_out_data.locality_adj +
2514 l_pay_calc_out_data.basic_pay;
2515 end if;
2516 ELSIF l_pay_plan = 'GR' then --IF l_pay_plan = 'GP'
2517 hr_utility.set_location('Setting process methods for GP pay plan...',555);
2518 get_locality_adj (l_pay_calc_data, l_retained_grade, l_pay_calc_out_data);
2519 If (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2520 and (l_first_action_la_code1 = 'ZLM') then
2521 l_pay_calc_out_data.open_localityadj_field := TRUE;
2522 END IF;
2523 --Restoring Market pay since market pay should not change because of special_rate_pay_calc
2524 if p_open_out_locality_adj is not null then
2525 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2526 l_pay_calc_out_data.adj_basic_pay := l_pay_calc_out_data.locality_adj +
2527 l_pay_calc_out_data.basic_pay;
2528 end if;
2529 END IF; --IF l_pay_plan = 'GP'
2530 --End Bug 7046241
2531 ELSE
2532
2533 ------GPPA Update 46 start
2534 IF (l_pay_calc_data.noa_code = '890' OR l_pay_calc_data.second_noa_code = '890') AND
2535 l_pay_calc_data.pay_plan = 'GM' then
2536 l_pay_calc_data.pay_plan := 'GS';
2537 l_default_pay_plan := 'GS';
2538 l_grade_id := NULL;
2539 FOR get_grade_id_rec IN get_grade_id(l_default_pay_plan, l_pay_calc_data.grade_or_level)
2540 LOOP
2541 l_grade_id := get_grade_id_rec.grade_id;
2542 l_pay_plan_changed := TRUE;
2543 g_pay_table_upd_flag := TRUE;
2544 exit;
2545 END LOOP;
2546 END IF;
2547 ------------------------------ 1) Get basic_pay --------------------------------------------------------
2548 --
2549 hr_utility.set_location('Calling main basic pay...'|| l_proc,5);
2550 -- MSL percentage Changes Madhuri 3843306
2551
2552 -- if nvl(ghr_msl_pkg.g_ses_msl_process,'N') = 'Y' then
2553 -- l_pay_calc_out_data.basic_pay := nvl(l_pay_calc_data.current_adj_basic_pay,0);
2554 --else
2555 ghr_pc_basic_pay.get_basic_pay (l_pay_calc_data, l_pay_calc_out_data, l_retained_grade);
2556 --end if;
2557 -- FWFA Changes. Bug#4444609 Setting the Calculation Pay Table in case of non-fwfa calculations.
2558 IF l_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V','M') AND
2559 l_retained_grade.temp_step IS NULL THEN
2560 l_pay_calc_out_data.calculation_pay_table_id := l_retained_grade.user_table_id;
2561 l_pay_calc_out_data.pay_table_id := l_retained_grade.user_table_id;
2562 l_pay_plan := l_retained_grade.pay_plan;
2563 ELSE
2564 l_pay_calc_out_data.calculation_pay_table_id := l_pay_calc_data.user_table_id;
2565 l_pay_calc_out_data.pay_table_id := l_pay_calc_data.user_table_id;
2566 l_pay_plan := l_pay_calc_data.pay_plan;
2567 -- Bug#4748583 Added the following IF Condition to set the PRD to '6' for 0491 table.
2568 -- Bug#5089732 GL Pay Plan
2569 IF get_user_table_name(l_pay_calc_out_data.pay_table_id) = ghr_pay_calc.l_spl491_table_name AND
2570 (l_pay_calc_data.pay_plan <> 'GL' OR
2571 (l_pay_plan = 'GG' and
2572 l_pay_calc_data.effective_date < to_date('2006/01/08','YYYY/MM/DD')
2573 )
2574 ) AND
2575 l_pay_calc_data.pay_rate_determinant = '0' THEN
2576 l_pay_calc_out_data.out_pay_rate_determinant := '6';
2577 END IF;
2578 -- Bug#4748583
2579 END IF;
2580 --Bug#5089732 Set the Pay Rate Determinant for GL,GG Pay Plans
2581 IF get_user_table_name(l_pay_calc_out_data.calculation_pay_table_id) = ghr_pay_calc.l_spl491_table_name AND
2582 (l_pay_plan = 'GL' OR
2583 (l_pay_plan = 'GG' and
2584 l_pay_calc_data.effective_date >= to_date('2006/01/08','YYYY/MM/DD')
2585 )
2586 )AND
2587 l_pay_calc_data.pay_rate_determinant IN ('6','E','F') THEN
2588 IF l_pay_calc_data.pay_rate_determinant = '6' THEN
2589 l_pay_calc_out_data.out_pay_rate_determinant := '0';
2590 ELSIF l_pay_calc_data.pay_rate_determinant = 'E' THEN
2591 l_pay_calc_out_data.out_pay_rate_determinant := 'A';
2592 ELSE
2593 l_pay_calc_out_data.out_pay_rate_determinant := 'B';
2594 END IF;
2595 --Bug#5435217 Set this flag to true to avoid unnecessary creation of RG record.
2596 g_gl_upd_flag := TRUE;
2597 END IF;
2598
2599 --BUG # 13341602 Added to set back the PRD to 0 If previously it was set to 6 by
2600 -- Special Rate and now it is changed to 0000 table
2601 IF l_pay_calc_data.pay_rate_determinant = '6' AND
2602 get_user_table_name(l_pay_calc_data.user_table_id) in (ghr_pay_calc.l_spl491_table_name,ghr_pay_calc.l_standard_table_name) THEN
2603 l_pay_calc_out_data.out_pay_rate_determinant := '0';
2604 END IF;
2605
2606 -- FWFA Changes
2607 ------------------------------ 2) Get locality_adj --------------------------------------------------------
2608 --
2609 --Bug # 9513518
2610 for rec_locality_pay_area in get_locality_pay_area
2611 loop
2612 l_locality_pay_area_code := rec_locality_pay_area.locality_pay_area_code;
2613 end loop;
2614 -- Start of Bug #5132113
2615 IF l_pay_plan = 'GP' then
2616 l_pay_calc_out_data.open_localityadj_field := TRUE;
2617
2618 --Bug #6344900 locality adjustment should not be opened for 894 and lac codes other than ZLM
2619 -- and 893 actions
2620 If (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2621 and (l_first_action_la_code1 <> 'ZLM') then
2622 l_pay_calc_out_data.open_localityadj_field := FALSE;
2623 End If;
2624 If (l_pay_calc_data.noa_code = '893' OR l_pay_calc_data.second_noa_code = '893') then
2625 l_pay_calc_out_data.open_localityadj_field := FALSE;
2626 End If;
2627
2628
2629 if p_open_out_locality_adj is not null then
2630 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2631 end if;
2632 --Bug # 9513518 Added If Condition For ES Equivalent and 890 Locality should open
2633 -- Bug # 9977834 made to open for all NOAC removed validation on 890 and
2634 -- non foreign locality areas
2635 --Begin Bug 10244679
2636 ELSIF l_pay_calc_data.noa_family_code = 'REALIGNMENT' and
2637 l_pay_plan IN ('AD','EF') and
2638 l_pay_calc_data.grade_or_level = '00' and
2639 l_pay_calc_data.step_or_rate = '00' and
2640 l_pay_calc_data.pay_rate_determinant = '0'
2641 and SUBSTR(ghr_pay_calc.get_user_table_name(l_user_table_id),1,4) = '0000' THEN
2642 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2643 --End Bug# 10244679
2644 ELSIF NVL(esplan_flag,'N') = 'Y' and l_locality_pay_area_code in ('HI','AK','ON') then
2645 l_pay_calc_out_data.open_localityadj_field := TRUE;
2646 if p_open_out_locality_adj is not null then
2647 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2648 end if;
2649 ELSE
2650 hr_utility.set_location('Calling main locality...'|| l_proc,5);
2651 get_locality_adj (l_pay_calc_data, l_retained_grade, l_pay_calc_out_data);
2652 --Bug#5132113 for GR payplan and 894 open locality pay for user modification
2653 If (l_pay_plan = 'GR') AND
2654 (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2655 and (l_first_action_la_code1 = 'ZLM') then
2656 l_pay_calc_out_data.open_localityadj_field := TRUE;
2657 END IF;
2658 --Bug#5132113
2659
2660 hr_utility.set_location('Calling main locality..main locality pay .'||
2661 to_char(l_pay_calc_out_data.locality_adj),5);
2662 END IF;
2663 --
2664 ------------------------------ 3) Get adj_basic_pay -----------------------------------------------------
2665 --
2666 hr_utility.set_location('Calling main adj basic...'|| l_proc,5);
2667 get_adj_basic_pay (l_pay_calc_data, l_pay_calc_out_data);
2668 hr_utility.set_location('Calling main adj basic..adj basic Pay.'||
2669 to_char(l_pay_calc_out_data.adj_basic_pay),5);
2670 END IF;
2671
2672 IF g_pay_table_upd_flag THEN
2673 hr_utility.set_location('After Basic/loc/adj basic calc. Pay Table UPD Flag TRUE',101);
2674 ELSE
2675 hr_utility.set_location('After Basic/loc/adj basic Calc. Pay Table UPD Flag FALSE',102);
2676 END IF;
2677 --
2678
2679 ------------------------------ 4.0) Get retention_allowance----------------------------------------------
2680 --
2681 ---- Calcultion of Retention Allowance.
2682 ---
2683 if p_noa_family_code = 'CONV_APP' and l_ex_emp THEN
2684 l_retention_allowance := null;
2685 l_to_ret_allow_percentage := null;
2686 end if;
2687
2688 if l_retention_allowance is not null then
2689 if nvl(l_pay_calc_data.current_basic_pay,0) <> nvl(l_pay_calc_out_data.basic_pay,0) then
2690
2691 if l_to_ret_allow_percentage is null then
2692 if (l_session.noa_id_correct is not null ) or (p_noa_family_code = 'CORRECT') then
2693 hr_utility.set_location(' perc null Inside correct ' || l_proc,5);
2694 hr_utility.set_location(' current_basic pay = ' || to_char(l_pay_calc_data.current_basic_pay),5);
2695 if nvl(l_old_ret_allow,0) = nvl(l_from_retention_allowance,0) then
2696 if nvl(l_from_basic_pay,0) <> nvl(l_pay_calc_data.current_basic_pay,0) then
2697 l_from_basic_pay := l_pay_calc_data.current_basic_pay;
2698 end if;
2699 end if;
2700 hr_utility.set_location('From retention' || l_from_retention_allowance,2000);
2701 hr_utility.set_location('From l_from_basic_pay' || l_from_basic_pay,2000);
2702 hr_utility.set_location('From l_from_pay_basis' || l_from_pay_basis,2000);
2703
2704 ---- Changed for FWS
2705
2706 l_ret_calc_perc := (l_from_retention_allowance / l_from_basic_pay )* 100;
2707
2708 -- Bug 3245692 . Replaced l_pay_basis with l_from_pay_basis in the above statement
2709 hr_utility.set_location(' Ret Percentage = ' || to_char(l_ret_calc_perc),5);
2710 else
2711 hr_utility.set_location(' perc null else correct ' || l_proc,5);
2712 ----
2713 ---- Bug 3218346 --
2714 ----
2715
2716 l_ret_calc_perc := nvl((l_pay_calc_data.retention_allowance /
2717 l_pay_calc_data.current_basic_pay) * 100,0);
2718 -- Bug 3245692
2719 hr_utility.set_location(' else Ret Percentage = ' || to_char(l_ret_calc_perc),5);
2720 end if;
2721
2722 if nvl(l_ret_calc_perc,0) > 25 then
2723 l_ret_calc_perc := 25;
2724 end if;
2725 else -- l_to_ret_allow_percentage is not null
2726 l_ret_calc_perc := l_to_ret_allow_percentage;
2727 hr_utility.set_location(' Passed Percentage l_ret_calc_perc = ' || to_char(l_ret_calc_perc),5);
2728 end if; -- if nvl(l_ret_calc_perc,0) > 2
2729
2730
2731 /*IF l_pay_basis ='PH' THEN
2732 l_to_retention_allowance := TRUNC(l_pay_calc_out_data.basic_pay * l_ret_calc_perc / 100 ,2);
2733
2734 ELSE
2735 l_to_retention_allowance := TRUNC(l_pay_calc_out_data.basic_pay * l_ret_calc_perc / 100 ,0);
2736
2737 END IF;
2738 --Changed for FWS*/
2739
2740 -- Rounding of RETENTION ALLOWANCE STARTS
2741 ---3843316
2742 --
2743 -- BUG# 4689374 For FWFA if % is not entered, then take only the amount
2744 IF p_pay_rate_determinant IN ('3','4','J','K','U','V') AND
2745 p_effective_date >= to_date('01/05/2005','dd/mm/yyyy') AND
2746 l_to_ret_allow_percentage IS NULL THEN
2747 l_to_retention_allowance := l_retention_allowance;
2748 ELSE
2749 IF l_pay_basis ='PH' THEN
2750 l_to_retention_allowance := round(l_pay_calc_out_data.basic_pay *(l_ret_calc_perc/100),2);
2751 IF ( l_ret_calc_perc between 24 and 25) THEN
2752 l_max_RA:= trunc((l_pay_calc_out_data.basic_pay*0.25),2);
2753 IF (l_to_retention_allowance > l_max_ra) THEN
2754 l_to_retention_allowance:= l_max_ra;
2755 END IF;
2756 END IF;
2757 ELSIF l_pay_basis ='PA' THEN
2758 l_max_RA:= trunc((l_pay_calc_out_data.basic_pay*0.25),0);
2759 l_to_retention_allowance := round(l_pay_calc_out_data.basic_pay*(l_ret_calc_perc/100),0);
2760 if l_to_retention_allowance > l_max_RA then
2761 l_to_retention_allowance := l_max_RA;
2762 end if;
2763 ELSE
2764 l_to_retention_allowance := TRUNC(l_pay_calc_out_data.basic_pay * l_ret_calc_perc / 100 ,0);
2765 END IF;
2766 END IF;
2767
2768 --- 3843316
2769 -- Rounding of RETENTION ALLOWANCE ENDS
2770
2771 hr_utility.set_location(' Calc retention_allowance = ' || to_char(l_to_retention_allowance),5);
2772 hr_utility.set_location(' Calc-Pass ret_allow_percentage = ' || to_char(l_ret_calc_perc),5);
2773 hr_utility.set_location(' Supervisory Differentail = ' || to_char(l_supervisory_differential),5);
2774 else
2775 hr_utility.set_location(' Basic Pays are same ' || l_proc,5);
2776 if ( l_session.noa_id_correct is not null ) or ( p_noa_family_code = 'CORRECT' ) then
2777 hr_utility.set_location(' Basic Pays are same - Inside Correct ' || l_proc,5);
2778 if l_from_basic_pay = nvl(l_pay_calc_data.current_basic_pay,0) then
2779 l_to_retention_allowance := l_pay_calc_data.retention_allowance;
2780 else
2781 l_to_retention_allowance := l_old_ret_allow;
2782 end if;
2783 hr_utility.set_location(' BPS correct retention_allowance = ' || to_char(l_to_retention_allowance),5);
2784 else
2785 l_to_retention_allowance := l_pay_calc_data.retention_allowance;
2786 hr_utility.set_location(' BPS else retention_allowance = ' || to_char(l_to_retention_allowance),5);
2787 end if;
2788 end if;
2789 end if;
2790 l_pay_calc_out_data.retention_allowance := l_to_retention_allowance;
2791
2792 ------------------------------ 4.1) Get au_overtime------------------------------------------------------
2793 --
2794 l_pay_calc_out_data.au_overtime := get_ppi_amount (l_pay_calc_data.auo_premium_pay_indicator
2795 ,l_pay_calc_out_data.adj_basic_pay
2796 ,l_pay_calc_data.pay_basis);
2797 --
2798 ------------------------------ 4.2) Get avalabilty_pay ------------------------------------------------------
2799 --
2800 l_pay_calc_out_data.availability_pay := get_ppi_amount (l_pay_calc_data.ap_premium_pay_indicator
2801 ,l_pay_calc_out_data.adj_basic_pay
2802 ,l_pay_calc_data.pay_basis);
2803 --
2804 ------------------------------ 5) Get other_pay_amount ------------------------------------------------------
2805 --
2806 get_other_pay_amount (l_pay_calc_data, l_pay_calc_out_data);
2807 --
2808 ------------------------------ 6) Get_total_salary ------------------------------------------------------
2809 --
2810 get_total_salary (l_pay_calc_out_data,l_pay_calc_data.pay_basis);
2811 --
2812 -------------------------------------------------------------------------------------------------------
2813 --
2814 END IF; -- End of PRD 2 Processing IF Condition.
2815 ----GPPA Update 46
2816 IF l_pay_plan_changed THEN
2817 IF nvl(l_pay_calc_out_data.out_pay_rate_determinant,'X') in ('A','B','E','F','U','V') AND
2818 l_retained_grade.temp_step IS NULL THEN
2819 g_out_to_pay_plan := l_default_pay_plan;
2820 ELSIF l_grade_id is not null then
2821 l_pay_calc_out_data.out_to_grade_id := l_grade_id;
2822 l_pay_calc_out_data.out_to_pay_plan := l_default_pay_plan;
2823 l_pay_calc_out_data.out_to_grade_or_level := l_pay_calc_data.grade_or_level;
2824 END IF;
2825 END IF;
2826 -- If we got here we haven't set any messages and we think we calculated it!!
2827 -- Even though we calculated everything pass it on to the cutstom call incase they want to
2828 -- overwrite it!!
2829 ---Bug 7423379 Start (Fix was revoked - AVR 10/24)
2830 ----IF l_pay_plan = 'AD' THEN
2831 ---- l_pay_calc_out_data.open_pay_fields := TRUE;
2832 ----END IF;
2833 ---Bug 7423379 End
2834 ghr_custom_pay_calc.custom_pay_calc
2835 (l_pay_calc_data
2836 ,l_pay_calc_out_data
2837 ,l_message_set
2838 ,l_calculated);
2839 EXCEPTION
2840 WHEN open_pay_range_mesg THEN
2841 -- set calculated to false and let the customer attempt to calculate it!
2842 -- if the user enters the validated basic pay then pay will be calculated.
2843 l_message_set := TRUE;
2844 l_calculated := FALSE;
2845 l_pay_calc_out_data.open_basicpay_field := TRUE;
2846
2847 --Bug # 9513518
2848 for rec_locality_pay_area in get_locality_pay_area
2849 loop
2850 l_locality_pay_area_code := rec_locality_pay_area.locality_pay_area_code;
2851 end loop;
2852 --ES Equivalent Pay Plan
2853 IF espayplan%isopen THEN
2854 CLOSE espayplan;
2855 END IF;
2856
2857 OPEN espayplan;
2858 FETCH espayplan into esplan_flag;
2859 CLOSE espayplan;
2860 IF NVL(esplan_flag,'N') = 'Y' and l_locality_pay_area_code in ('HI','AK','ON') then
2861 l_pay_calc_out_data.open_localityadj_field := TRUE;
2862 END IF;
2863 --Bug # 9513518
2864
2865
2866 -- Bug#4758111 Removed PRD 2 from the following list.
2867 if p_pay_rate_determinant
2868 in ( '3','4','C','J','K','M','P','R','S','U','V') then
2869 l_pay_calc_out_data.open_pay_fields := TRUE;
2870 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2871 hr_utility.set_message_token('PRD',p_pay_rate_determinant);
2872 end if;
2873 ---Bug 7423379 Start (Fix was revoked - AVR 10/24)
2874 ----IF l_pay_plan = 'AD' THEN
2875 ---- l_pay_calc_out_data.open_pay_fields := TRUE;
2876 ----END IF;
2877 ---Bug 7423379 End
2878 ghr_custom_pay_calc.custom_pay_calc
2879 (l_pay_calc_data
2880 ,l_pay_calc_out_data
2881 ,l_message_set
2882 ,l_calculated);
2883 WHEN unable_to_calculate THEN
2884 -- set calculated to false and let the customer attempt to calculate it!
2885 -- if the user calculates it they need to make sure the pass back out p_calculated = TRUE
2886 -- otherwise we will assume they didn't do it either!!
2887 -- FWFA Changes. Bug#4444609 Setting the Calculation Pay Table in case of non-fwfa calculations.
2888 IF NOT (g_fwfa_pay_calc_flag) THEN
2889 --Begin Bug# 8453042
2890 FOR l_sl_eql_pp IN c_sl_eql_pp LOOP
2891 sl_eql_pay_plan := l_sl_eql_pp.equivalent_pay_plan;
2892 END LOOP;
2893 --End Bug# 8453042
2894 IF l_pay_calc_data.pay_rate_determinant IN ('U','V') AND
2895 l_retained_grade.temp_step IS NULL THEN
2896 l_pay_calc_out_data.calculation_pay_table_id := l_retained_grade.user_table_id;
2897 l_pay_calc_out_data.pay_table_id := l_retained_grade.user_table_id;
2898 ELSIF l_pay_calc_data.pay_rate_determinant IN ('3','4','J','K') OR (NVL(sl_eql_pay_plan,'$$')='SL') THEN--Bug# 8453042
2899 l_pay_calc_out_data.calculation_pay_table_id := l_pay_calc_data.user_table_id;
2900 l_pay_calc_out_data.pay_table_id := l_pay_calc_data.user_table_id;
2901 END IF;
2902 END IF;
2903 l_message_set := TRUE;
2904 l_calculated := FALSE;
2905 l_pay_calc_out_data.open_pay_fields := TRUE;
2906 ghr_custom_pay_calc.custom_pay_calc
2907 (l_pay_calc_data
2908 ,l_pay_calc_out_data
2909 ,l_message_set
2910 ,l_calculated);
2911 WHEN ghr_pay_calc.pay_calc_message THEN
2912 l_message_set := TRUE;
2913 l_calculated := TRUE;
2914 ---Bug 7423379 Start (Fix was revoked - AVR 10/24)
2915 -- Begin Bug 9977338 removed the comments done for bug 7423379
2916 IF l_pay_plan = 'AD' THEN
2917 l_message_set := TRUE;
2918 l_calculated := FALSE;
2919 l_pay_calc_out_data.open_pay_fields := TRUE;
2920 END IF;
2921 ---Bug 9977338 End
2922 ghr_custom_pay_calc.custom_pay_calc
2923 (l_pay_calc_data
2924 ,l_pay_calc_out_data
2925 ,l_message_set
2926 ,l_calculated);
2927
2928 END;
2929 -- In one circumstance we may need to run pay calc again! i.e. Dual Action: 893 / 892
2930 IF l_run_pay_calc_again THEN
2931 main_pay_calc (p_person_id
2932 ,p_position_id
2933 ,l_second_noa_family_code
2934 ,p_second_noa_code
2935 ,p_first_action_la_code1
2936 ,null
2937 ,p_effective_date
2938 ,p_pay_rate_determinant
2939 ,p_pay_plan
2940 ,p_grade_or_level
2941 ,p_step_or_rate
2942 ,p_pay_basis
2943 ,p_user_table_id
2944 ,p_duty_station_id
2945 ,p_auo_premium_pay_indicator
2946 ,p_ap_premium_pay_indicator
2947 ,p_retention_allowance
2948 ,p_to_ret_allow_percentage
2949 ,p_supervisory_differential
2950 ,p_staffing_differential
2951 ,p_current_basic_pay
2952 ,p_current_adj_basic_pay
2953 ,l_pay_calc_out_data.out_step_or_rate
2954 ,p_pa_request_id
2955 ,p_open_range_out_basic_pay
2956 ,p_open_out_locality_adj
2957 ,l_pay_calc_out_data.basic_pay
2958 ,l_pay_calc_out_data.locality_adj
2959 ,l_pay_calc_out_data.adj_basic_pay
2960 ,l_pay_calc_out_data.total_salary
2961 ,l_pay_calc_out_data.other_pay_amount
2962 ,l_pay_calc_out_data.retention_allowance
2963 ,l_pay_calc_out_data.ret_allow_perc_out
2964 ,l_pay_calc_out_data.au_overtime
2965 ,l_pay_calc_out_data.availability_pay
2966 -- FWFA Changes
2967 ,l_pay_calc_out_data.calculation_pay_table_id
2968 ,l_pay_calc_out_data.pay_table_id
2969 -- FWFA Changes
2970 ,l_pay_calc_out_data.out_step_or_rate
2971 ,l_pay_calc_out_data.out_pay_rate_determinant
2972 ,l_pay_calc_out_data.out_to_grade_id
2973 ,l_pay_calc_out_data.out_to_pay_plan
2974 ,l_pay_calc_out_data.out_to_grade_or_level
2975 ,l_pay_calc_out_data.PT_eff_start_date
2976 ,l_pay_calc_out_data.open_basicpay_field
2977 ,l_pay_calc_out_data.open_pay_fields
2978 ,l_message_set
2979 ,l_calculated
2980 ,l_pay_calc_out_data.open_localityadj_field);
2981 END IF;
2982
2983 if get_open_pay_range ( l_position_id
2984 , p_person_id
2985 , l_pay_rate_determinant
2986 , p_pa_request_id
2987 , NVL(p_effective_date,TRUNC(sysdate)) ) then
2988 --6489042 during appointment of ESSL employees of PRD 2 enabling basic pay for user entry
2989 if (p_pay_rate_determinant <> '2') or (p_pay_rate_determinant = '2' and l_noa_family_code IN ('APP')) then
2990 l_pay_calc_out_data.open_basicpay_field := TRUE;
2991 end if;
2992 --Bug# 13103038
2993 IF p_pay_rate_determinant = '2'
2994 AND l_noa_code = '894'
2995 AND (l_session.noa_id_correct is not null OR l_first_action_la_code1 = 'VGR')
2996 ANd l_locality_pay_area_code in ('HI','AK','ON') THEN
2997 l_pay_calc_out_data.open_basicpay_field := TRUE;
2998 END IF;
2999 --Bug# 13103038
3000 end if;
3001
3002 --bug#5132113
3003 /* if p_open_out_locality_adj is null then
3004 l_pay_calc_out_data.open_localityadj_field := TRUE;
3005 end if;*/
3006 --Begin Bug# 7557159
3007 IF l_pay_plan='GS' AND l_pay_rate_determinant='D' THEN
3008 l_prd_d_pay_amount := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => l_pay_plan
3009 ,p_grade_or_level => l_grade_or_level
3010 ,p_step_or_rate => '10'
3011 ,p_effective_date => p_effective_date);
3012
3013 IF l_prd_d_pay_amount < l_pay_calc_out_data.basic_pay THEN
3014 l_pay_calc_out_data.out_step_or_rate := '00';
3015 l_pay_calc_out_data.open_basicpay_field := TRUE;
3016 END IF;
3017 END IF;
3018 --End Bug# 7557159
3019 -- always set the out parameters
3020 l_pay_calc_out_data.ret_allow_perc_out := l_to_ret_allow_percentage;
3021
3022 p_basic_pay := l_pay_calc_out_data.basic_pay;
3023 p_locality_adj := l_pay_calc_out_data.locality_adj;
3024 p_adj_basic_pay := l_pay_calc_out_data.adj_basic_pay;
3025 p_total_salary := l_pay_calc_out_data.total_salary;
3026 p_other_pay_amount := l_pay_calc_out_data.other_pay_amount;
3027 p_to_retention_allowance := l_pay_calc_out_data.retention_allowance;
3028 p_ret_allow_perc_out := l_pay_calc_out_data.ret_allow_perc_out;
3029 p_au_overtime := l_pay_calc_out_data.au_overtime;
3030 p_availability_pay := l_pay_calc_out_data.availability_pay;
3031 -- FWFA Changes
3032 p_calc_pay_table_id := l_pay_calc_out_data.calculation_pay_table_id;
3033 p_pay_table_id := l_pay_calc_out_data.pay_table_id;
3034 p_out_to_grade_id := l_pay_calc_out_data.out_to_grade_id;
3035 p_out_to_pay_plan := l_pay_calc_out_data.out_to_pay_plan;
3036 p_out_to_grade_or_level := l_pay_calc_out_data.out_to_grade_or_level;
3037 -- FWFA Changes
3038 p_out_step_or_rate := l_pay_calc_out_data.out_step_or_rate;
3039 p_out_pay_rate_determinant := l_pay_calc_out_data.out_pay_rate_determinant;
3040 p_PT_eff_start_date := l_pay_calc_out_data.PT_eff_start_date;
3041 p_open_basicpay_field := l_pay_calc_out_data.open_basicpay_field;
3042 p_open_pay_fields := l_pay_calc_out_data.open_pay_fields;
3043 p_message_set := l_message_set;
3044 p_calculated := l_calculated;
3045
3046 --Bug#5132113
3047 p_open_localityadj_field := l_pay_calc_out_data.open_localityadj_field;
3048
3049
3050 EXCEPTION
3051 WHEN others THEN
3052 -- Reset IN OUT parameters and set OUT parameters
3053 p_basic_pay := NULL;
3054 p_locality_adj := NULL;
3055 p_adj_basic_pay := NULL;
3056 p_total_salary := NULL;
3057 p_other_pay_amount := NULL;
3058 p_to_retention_allowance := NULL;
3059 p_ret_allow_perc_out := NULL;
3060 p_au_overtime := NULL;
3061 p_availability_pay := NULL;
3062 -- FWFA Changes
3063 p_calc_pay_table_id := NULL;
3064 p_pay_table_id := NULL;
3065 -- FWFA Changes
3066 p_out_step_or_rate := NULL;
3067 p_out_pay_rate_determinant := NULL;
3068 p_out_to_grade_id := NULL;
3069 p_out_to_pay_plan := NULL;
3070 p_out_to_grade_or_level := NULL;
3071 p_PT_eff_start_date := NULL;
3072 p_open_basicpay_field := NULL;
3073 p_open_pay_fields := NULL;
3074 p_message_set := NULL;
3075 p_calculated := NULL;
3076 p_open_localityadj_field := NULL;
3077 RAISE;
3078 END main_pay_calc;
3079
3080
3081 PROCEDURE sql_main_pay_calc (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3082 ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
3083 ,p_message_set OUT NOCOPY BOOLEAN
3084 ,p_calculated OUT NOCOPY BOOLEAN
3085 ) IS
3086 l_message_set BOOLEAN;
3087 l_calculated BOOLEAN;
3088 BEGIN
3089
3090 main_pay_calc (p_pay_calc_data.person_id
3091 ,p_pay_calc_data.position_id
3092 ,p_pay_calc_data.noa_family_code
3093 ,p_pay_calc_data.noa_code
3094 ,p_pay_calc_data.second_noa_code
3095 ,p_pay_calc_data.first_action_la_code1
3096 ,p_pay_calc_data.effective_date
3097 ,p_pay_calc_data.pay_rate_determinant
3098 ,p_pay_calc_data.pay_plan
3099 ,p_pay_calc_data.grade_or_level
3100 ,p_pay_calc_data.step_or_rate
3101 ,p_pay_calc_data.pay_basis
3102 ,p_pay_calc_data.user_table_id
3103 ,p_pay_calc_data.duty_station_id
3104 ,p_pay_calc_data.auo_premium_pay_indicator
3105 ,p_pay_calc_data.ap_premium_pay_indicator
3106 ,p_pay_calc_data.retention_allowance
3107 ,p_pay_calc_data.to_ret_allow_percentage
3108 ,p_pay_calc_data.supervisory_differential
3109 ,p_pay_calc_data.staffing_differential
3110 ,p_pay_calc_data.current_basic_pay
3111 ,p_pay_calc_data.current_adj_basic_pay
3112 ,p_pay_calc_data.current_step_or_rate
3113 ,p_pay_calc_data.pa_request_id
3114 ,p_pay_calc_data.open_range_out_basic_pay
3115 --Bug5132113
3116 ,p_pay_calc_data.open_out_locality_adj
3117 ,p_pay_calc_out_data.basic_pay
3118 ,p_pay_calc_out_data.locality_adj
3119 ,p_pay_calc_out_data.adj_basic_pay
3120 ,p_pay_calc_out_data.total_salary
3121 ,p_pay_calc_out_data.other_pay_amount
3122 ,p_pay_calc_out_data.retention_allowance
3123 ,p_pay_calc_out_data.ret_allow_perc_out
3124 ,p_pay_calc_out_data.au_overtime
3125 ,p_pay_calc_out_data.availability_pay
3126 -- FWFA Changes
3127 ,p_pay_calc_out_data.Calculation_pay_table_id
3128 ,p_pay_calc_out_data.pay_table_id
3129 -- FWFA Changes
3130 ,p_pay_calc_out_data.out_step_or_rate
3131 ,p_pay_calc_out_data.out_pay_rate_determinant
3132 ,p_pay_calc_out_data.out_to_grade_id
3133 ,p_pay_calc_out_data.out_to_pay_plan
3134 ,p_pay_calc_out_data.out_to_grade_or_level
3135 ,p_pay_calc_out_data.PT_eff_start_date
3136 ,p_pay_calc_out_data.open_basicpay_field
3137 ,p_pay_calc_out_data.open_pay_fields
3138 ,l_message_set
3139 ,l_calculated
3140 ,p_pay_calc_out_data.open_localityadj_field);
3141
3142 p_message_set := l_message_set;
3143 p_calculated := l_calculated;
3144
3145
3146 IF l_message_set and l_calculated THEN
3147 hr_utility.raise_error;
3148 END IF;
3149
3150 EXCEPTION
3151 WHEN others THEN
3152 -- Reset IN OUT parameters and set OUT parameters
3153 p_message_set := NULL;
3154 p_calculated := NULL;
3155 p_pay_calc_out_data := NULL;
3156 RAISE;
3157 END sql_main_pay_calc;
3158
3159 --
3160 FUNCTION get_pos_pay_basis (p_position_id IN per_positions.position_id%TYPE
3161 ,p_effective_date IN date)
3162 RETURN VARCHAR2 IS
3163 --
3164 -- Since Position Extra Info now has history use the history packages written to get
3165 -- the user_table_id
3166 l_pos_ei_data per_position_extra_info%ROWTYPE;
3167 --
3168 --
3169 BEGIN
3170 ghr_history_fetch.fetch_positionei(
3171 p_position_id => p_position_id
3172 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
3173 ,p_date_effective => p_effective_date
3174 ,p_pos_ei_data => l_pos_ei_data);
3175 --
3176 RETURN(l_pos_ei_data.poei_information6);
3177 --
3178 END get_pos_pay_basis;
3179 --
3180
3181 --AVR
3182 PROCEDURE get_locality_adj_894_PRDM_GS (p_user_table_id IN NUMBER
3183 ,p_pay_plan IN VARCHAR2
3184 ,p_grade_or_level IN VARCHAR2
3185 ,p_step_or_rate IN VARCHAR2
3186 ,p_effective_date IN DATE
3187 ,p_cur_adj_basic_pay IN NUMBER
3188 ,p_new_basic_pay IN NUMBER
3189 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3190 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3191 --
3192 -- Local variables
3193 --
3194
3195 l_PT_value NUMBER;
3196 l_PT_eff_start_date DATE;
3197 l_PT_eff_end_date DATE;
3198 l_PT_o_value NUMBER;
3199 l_PT_o_eff_start_date DATE;
3200 l_PT_o_eff_end_date DATE;
3201 l_SPT_value NUMBER;
3202 l_SPT_o_value NUMBER;
3203 l_A NUMBER;
3204 l_B NUMBER;
3205 lesser_amt NUMBER;
3206 new_adj_basic_pay NUMBER;
3207 new_locality_adj NUMBER;
3208
3209 l_proc VARCHAR2(30) := '894_PRDM_GS';
3210 begin
3211 hr_utility.set_location('Entering ...'|| l_proc,5);
3212 hr_utility.set_location('User_table_id ...'|| p_user_table_id,5);
3213 hr_utility.set_location('p_pay_plan ...'|| p_pay_plan,5);
3214 hr_utility.set_location('p_grade_or_level ...'|| p_grade_or_level,5);
3215 hr_utility.set_location('step_or_rate ...'|| p_step_or_rate,5);
3216 hr_utility.set_location('effective_date ...'|| to_char(p_effective_date,'DD-MON-YYYY'),5);
3217 hr_utility.set_location('In new current adj basic pay ...'|| to_char(p_cur_adj_basic_pay),5);
3218 hr_utility.set_location('In new basic pay ...'|| to_char(p_new_basic_pay),5);
3219
3220 ghr_pay_calc.get_pay_table_value (p_user_table_id => p_user_table_id
3221 ,p_pay_plan => p_pay_plan
3222 ,p_grade_or_level => p_grade_or_level
3223 ,p_step_or_rate => p_step_or_rate
3224 ,p_effective_date => p_effective_date
3225 ,p_PT_value => l_PT_value
3226 ,p_PT_eff_start_date => l_PT_eff_start_date
3227 ,p_PT_eff_end_date => l_PT_eff_end_date);
3228
3229 hr_utility.set_location('l_PT_value ...'|| to_char(l_PT_value),5);
3230 ghr_pay_calc.get_pay_table_value (p_user_table_id => p_user_table_id
3231 ,p_pay_plan => p_pay_plan
3232 ,p_grade_or_level => p_grade_or_level
3233 ,p_step_or_rate => p_step_or_rate
3234 ,p_effective_date => (l_PT_eff_start_date - 1)
3235 ,p_PT_value => l_PT_o_value
3236 ,p_PT_eff_start_date => l_PT_o_eff_start_date
3237 ,p_PT_eff_end_date => l_PT_o_eff_end_date);
3238
3239 hr_utility.set_location('l_PT_o_value ...'|| to_char(l_PT_o_value),5);
3240 l_SPT_value := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => p_pay_plan
3241 ,p_grade_or_level => p_grade_or_level
3242 ,p_step_or_rate => p_step_or_rate
3243 ,p_effective_date => p_effective_date);
3244
3245 hr_utility.set_location('l_SPT_value ...'|| to_char(l_SPT_value),5);
3246 l_SPT_o_value := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => p_pay_plan
3247 ,p_grade_or_level => p_grade_or_level
3248 ,p_step_or_rate => p_step_or_rate
3249 ,p_effective_date => (l_PT_eff_start_date -1) );
3250
3251 hr_utility.set_location('l_SPT_o_value ...'|| to_char(l_SPT_o_value),5);
3252
3253 l_A := l_PT_value - l_PT_o_value;
3254 hr_utility.set_location('l_A...'|| to_char(l_A),5);
3255 l_B := l_SPT_value - l_SPT_o_value;
3256 hr_utility.set_location('l_B...'|| to_char(l_B),5);
3257
3258 if l_A = l_B then
3259 lesser_amt := l_A;
3260 elsif l_A > l_B then
3261 lesser_amt := l_B;
3262 else
3263 lesser_amt := l_A;
3264 end if;
3265 hr_utility.set_location('lesser_amt...'|| to_char(lesser_amt),5);
3266
3267 new_adj_basic_pay := p_cur_adj_basic_pay + lesser_amt;
3268 new_locality_adj := new_adj_basic_pay - p_new_basic_pay;
3269
3270 p_new_adj_basic_pay := new_adj_basic_pay;
3271 p_new_locality_adj := new_locality_adj;
3272
3273 hr_utility.set_location('new_adj_basic_pay...'|| to_char(new_adj_basic_pay),5);
3274 hr_utility.set_location('new_locality_adj...'|| to_char(new_locality_adj),5);
3275
3276 hr_utility.set_location('Leaving ...'|| l_proc,5);
3277
3278 EXCEPTION
3279 WHEN others THEN
3280 -- Reset IN OUT parameters and set OUT parameters
3281 p_new_adj_basic_pay := NULL;
3282 p_new_locality_adj := NULL;
3283 RAISE;
3284
3285 end get_locality_adj_894_PRDM_GS;
3286
3287 PROCEDURE get_locality_adj_894_PRDM_GM
3288 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3289 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3290 ,p_new_std_relative_rate OUT NOCOPY NUMBER
3291 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3292 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3293
3294 l_std_user_table_id NUMBER;
3295 l_user_table_id NUMBER;
3296 l_user_table_name pay_user_tables.user_table_name%type;
3297 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
3298 l_new_std_relative_rate NUMBER;
3299
3300
3301 l_grade VARCHAR2(30);
3302 l_PT_eff_start_date DATE;
3303 l_7dp NUMBER;
3304
3305 l_std_min NUMBER;
3306 l_std_max NUMBER;
3307
3308 l_dummy_step VARCHAR2(30);
3309 l_dummy_date DATE;
3310 l_new_basic_pay NUMBER;
3311
3312 l_new_ret_basic_pay NUMBER;
3313 l_old_ret_basic_pay NUMBER;
3314 l_A NUMBER;
3315 l_B NUMBER;
3316 lesser_amt NUMBER;
3317 new_adj_basic_pay NUMBER;
3318 new_locality_adj NUMBER;
3319
3320 l_proc VARCHAR2(30) := '894_PRDM_GM';
3321
3322 CURSOR get_std_user_table_id IS
3323 SELECT utb.user_table_id
3324 FROM pay_user_tables utb
3325 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3326
3327 BEGIN
3328 hr_utility.set_location('Entering ...'|| l_proc,5);
3329 -- First get the id of standard pay table for later use
3330 FOR c_rec IN get_std_user_table_id LOOP
3331 l_std_user_table_id := c_rec.user_table_id;
3332 END LOOP;
3333
3334 IF p_retained_grade.grade_or_level IS NULL THEN
3335 l_grade := p_pay_calc_data.grade_or_level;
3336 l_user_table_id := p_pay_calc_data.user_table_id;
3337 ELSE
3338 l_grade := p_retained_grade.grade_or_level;
3339 l_user_table_id := p_retained_grade.user_table_id;
3340 END IF;
3341
3342 l_user_table_name := get_user_table_name(l_user_table_id);
3343 l_adjustment_percentage := get_lpa_percentage
3344 (p_pay_calc_data.duty_station_id
3345 ,p_pay_calc_data.effective_date);
3346
3347
3348 -- Get the 7 dp figure as calculated in the 6 step rule!
3349 ghr_pc_basic_pay.get_basic_pay_SAL894_6step(p_pay_calc_data
3350 ,p_retained_grade
3351 ,'POSITION'
3352 ,l_new_basic_pay
3353 ,l_PT_eff_start_date
3354 ,l_7dp);
3355 --
3356 l_A := l_new_basic_pay - p_pay_calc_data.current_basic_pay;
3357
3358 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3359 ,'GS'
3360 ,l_grade
3361 ,l_PT_eff_start_date
3362 ,l_dummy_step
3363 ,l_std_min
3364 ,l_dummy_date
3365 ,l_dummy_date);
3366 --
3367 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3368 ,'GS'
3369 ,l_grade
3370 ,l_PT_eff_start_date
3371 ,l_dummy_step
3372 ,l_std_max
3373 ,l_dummy_date
3374 ,l_dummy_date);
3375
3376 l_new_ret_basic_pay := l_std_min + ROUND((l_std_max - l_std_min) * l_7dp );
3377 l_new_std_relative_rate := l_new_ret_basic_pay;
3378 p_new_std_relative_rate := l_new_ret_basic_pay;
3379 --
3380 --
3381 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3382 ,'GS'
3383 ,l_grade
3384 ,(l_PT_eff_start_date - 1)
3385 ,l_dummy_step
3386 ,l_std_min
3387 ,l_dummy_date
3388 ,l_dummy_date);
3389 --
3390 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3391 ,'GS'
3392 ,l_grade
3393 ,(l_PT_eff_start_date - 1)
3394 ,l_dummy_step
3395 ,l_std_max
3396 ,l_dummy_date
3397 ,l_dummy_date);
3398
3399 l_old_ret_basic_pay := l_std_min + ROUND((l_std_max - l_std_min) * l_7dp );
3400
3401 l_B := l_new_ret_basic_pay - l_old_ret_basic_pay;
3402
3403 if l_A = l_B then
3404 lesser_amt := l_A;
3405 elsif l_A > l_B then
3406 lesser_amt := l_B;
3407 else
3408 lesser_amt := l_A;
3409 end if;
3410
3411 new_adj_basic_pay := p_pay_calc_data.current_adj_basic_pay + lesser_amt;
3412 new_locality_adj := new_adj_basic_pay - l_new_basic_pay;
3413
3414 l_new_std_relative_rate := l_new_std_relative_rate +
3415 ROUND(l_new_std_relative_rate *
3416 (NVL(l_adjustment_percentage,0)/100),0);
3417
3418 IF l_new_std_relative_rate > new_adj_basic_pay THEN
3419 new_adj_basic_pay := l_new_std_relative_rate;
3420 new_locality_adj := new_adj_basic_pay - l_new_basic_pay;
3421 ELSIF (l_new_basic_pay > new_adj_basic_pay) AND
3422 (l_user_table_name <> ghr_pay_calc.l_standard_table_name) THEN
3423 new_adj_basic_pay := l_new_basic_pay;
3424 new_locality_adj := 0;
3425 END IF;
3426
3427 p_new_adj_basic_pay := new_adj_basic_pay;
3428 p_new_locality_adj := new_locality_adj;
3429 hr_utility.set_location('Leaving ...'|| l_proc,5);
3430
3431 EXCEPTION
3432 WHEN others THEN
3433 -- Reset IN OUT parameters and set OUT parameters
3434 p_new_adj_basic_pay := NULL;
3435 p_new_locality_adj := NULL;
3436 RAISE;
3437 END;
3438
3439 --AVR
3440 --------------------------- <get_open_pay_table_values> ------------------------------------------
3441 PROCEDURE get_open_pay_table_values (p_user_table_id IN NUMBER
3442 ,p_pay_plan IN VARCHAR2
3443 ,p_grade_or_level IN VARCHAR2
3444 ,p_effective_date IN DATE
3445 ,p_row_high OUT NOCOPY NUMBER
3446 ,p_row_low OUT NOCOPY NUMBER) IS
3447 --
3448 l_proc varchar2(50) := 'get_open_pay_table_values';
3449 l_row_high NUMBER;
3450 l_row_low NUMBER;
3451 l_record_found BOOLEAN := FALSE;
3452 --
3453 -- Go and get the basic pay from the given pay table at the given grade or level
3454 -- and step
3455 -- NOTE:
3456 -- column => Pay Plan ||'-'|| Grade or Level
3457 --
3458 CURSOR cur_pay IS
3459 SELECT max(urw.ROW_HIGH_RANGE) ROW_HIGH_RANGE
3460 ,min(urw.ROW_LOW_RANGE_OR_NAME) ROW_LOW_RANGE_OR_NAME
3461 FROM pay_user_column_instances_f cin
3462 ,pay_user_rows_f urw
3463 ,pay_user_columns col
3464 WHERE col.user_table_id = p_user_table_id
3465 AND col.user_column_name = p_pay_plan||'-'||p_grade_or_level
3466 AND urw.user_table_id = p_user_table_id
3467 AND cin.user_row_id = urw.user_row_id
3468 AND cin.user_column_id = col.user_column_id
3469 AND NVL(p_effective_date,TRUNC(SYSDATE))
3470 BETWEEN urw.effective_start_date AND urw.effective_end_date
3471 AND NVL(p_effective_date,TRUNC(SYSDATE))
3472 BETWEEN cin.effective_start_date AND cin.effective_end_date;
3473 BEGIN
3474 hr_utility.set_location('Entering ...'|| l_proc,5);
3475 FOR cur_pay_rec IN cur_pay LOOP
3476 l_row_high := ROUND(cur_pay_rec.ROW_HIGH_RANGE,2);
3477 l_row_low := ROUND(cur_pay_rec.ROW_LOW_RANGE_OR_NAME,2);
3478 l_record_found := TRUE;
3479 --
3480 hr_utility.set_location('Record Found ...'|| l_proc,10);
3481 --
3482 END LOOP;
3483 --
3484 IF NOT l_record_found THEN
3485 p_row_high := null;
3486 p_row_low := null;
3487 ELSE
3488 p_row_high := l_row_high;
3489 p_row_low := l_row_low;
3490 END IF;
3491 hr_utility.set_location('Leaving ...'|| l_proc,20);
3492 --
3493
3494 EXCEPTION
3495 WHEN others THEN
3496 -- Reset IN OUT parameters and set OUT parameters
3497 p_row_high := NULL;
3498 p_row_low := NULL;
3499 RAISE;
3500 END get_open_pay_table_values;
3501
3502 FUNCTION get_pay_basis(
3503 p_effective_date IN ghr_pa_requests.effective_date%type,
3504 p_pa_request_id IN ghr_pa_requests.pa_request_id%type,
3505 p_person_id IN ghr_pa_requests.person_id%type
3506 ) RETURN ghr_pa_requests.from_pay_basis%type
3507 IS
3508 l_asg_ei_data per_assignment_extra_info%rowtype;
3509 l_prd per_assignment_extra_info.aei_information6%type;
3510 l_dummy VARCHAR2(30);
3511 l_assignment_id per_assignments_f.assignment_id%type;
3512 l_from_position_id ghr_pa_requests.from_position_id%type;
3513 l_from_pay_basis ghr_pa_requests.from_pay_basis%type;
3514 l_pos_ei_grade_data per_position_extra_info%rowtype;
3515
3516 CURSOR get_asgn_pos(c_person_id IN ghr_pa_requests.person_id%type, c_effective_date IN ghr_pa_requests.effective_date%type) IS
3517 SELECT paf.assignment_id, paf.position_id
3518 FROM per_assignments_f paf
3519 WHERE paf.person_id = c_person_id
3520 AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
3521 AND paf.primary_flag = 'Y'
3522 AND paf.assignment_type <> 'B';
3523
3524 BEGIN
3525 -- Get From assignment id and position id using person_id and effective date
3526 FOR ctr_get_asgn_pos IN get_asgn_pos(p_person_id,p_effective_date) LOOP
3527 l_assignment_id := ctr_get_asgn_pos.assignment_id;
3528 l_from_position_id := ctr_get_asgn_pos.position_id;
3529 END LOOP;
3530
3531 -- Get Assignment extra info records
3532 IF l_assignment_id IS NOT NULL THEN
3533 ghr_pa_requests_pkg.get_SF52_asg_ddf_details
3534 (p_assignment_id => l_assignment_id
3535 ,p_date_effective => p_effective_date
3536 ,p_tenure => l_dummy
3537 ,p_annuitant_indicator => l_dummy
3538 ,p_pay_rate_determinant => l_prd
3539 ,p_work_schedule => l_dummy
3540 ,p_part_time_hours => l_dummy);
3541 END IF;
3542
3543 -- If PRD In 'A','B','E','F','U','V'
3544 IF l_prd IN ('A','B','E','F','U','V') THEN
3545 -- If PRD in 'A','B','E','F' and having temporary promotion step, get from position extra info
3546 IF l_prd IN ('A','B','E','F') AND
3547 ghr_pa_requests_pkg.temp_step_true(p_pa_request_id) THEN
3548 ghr_history_fetch.fetch_positionei(
3549 p_position_id => l_from_position_id,
3550 p_information_type => 'GHR_US_POS_VALID_GRADE',
3551 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3552 p_pos_ei_data => l_pos_ei_grade_data);
3553 l_from_pay_basis := l_pos_ei_grade_data.poei_information6;
3554 ELSE
3555 l_from_pay_basis := ghr_pa_requests_pkg.get_upd34_pay_basis
3556 (p_person_id => p_person_id
3557 ,p_position_id => l_from_position_id
3558 ,p_prd => l_prd
3559 ,p_noa_code => null
3560 ,p_pa_request_id => null
3561 ,p_effective_date => nvl(p_effective_date,trunc(sysdate)));
3562 END IF;
3563 -- If PRD not in 'A','B','E','F','U','V', get from position extra info
3564 ELSE
3565 ghr_history_fetch.fetch_positionei(
3566 p_position_id => l_from_position_id,
3567 p_information_type => 'GHR_US_POS_VALID_GRADE',
3568 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3569 p_pos_ei_data => l_pos_ei_grade_data);
3570 l_from_pay_basis := l_pos_ei_grade_data.poei_information6;
3571 END IF;
3572 return l_from_pay_basis;
3573 EXCEPTION
3574 WHEN OTHERS THEN
3575 return '-1';
3576 END get_pay_basis;
3577
3578 --------------------------- <get_locality_894_itpay> ------------------------------------------
3579 PROCEDURE get_locality_894_itpay
3580 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3581 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3582 ,p_new_basic_pay IN NUMBER
3583 ,p_GM_unadjusted_rate OUT NOCOPY NUMBER
3584 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3585 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3586
3587 l_std_user_table_id NUMBER;
3588 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
3589
3590 l_grade VARCHAR2(30);
3591
3592 l_std_min NUMBER;
3593 l_std_max NUMBER;
3594 l_std_min_old NUMBER;
3595 l_std_max_old NUMBER;
3596
3597 l_dummy_step VARCHAR2(30);
3598 l_dummy_date DATE;
3599 l_effective_start_date DATE;
3600 l_effective_end_date DATE;
3601
3602 new_adj_basic_pay NUMBER;
3603 new_locality_adj NUMBER;
3604
3605 l_B1 NUMBER;
3606 l_B2 NUMBER;
3607 l_B3 NUMBER;
3608 l_B4 NUMBER;
3609 l_B5 NUMBER;
3610 l_B6 NUMBER;
3611 l_B7 NUMBER;
3612 l_B8 NUMBER;
3613
3614 l_proc VARCHAR2(30) := 'GMIT_894';
3615
3616 CURSOR get_std_user_table_id IS
3617 SELECT utb.user_table_id
3618 FROM pay_user_tables utb
3619 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3620
3621 ----
3622 l_assignment_id per_assignments_f.assignment_id%type;
3623 l_value varchar2(60);
3624 l_multi_error_flag BOOLEAN;
3625 l_gm_unadjd_basic_pay Number;
3626
3627 CURSOR get_asgn_pos (c_person_id IN ghr_pa_requests.person_id%type,
3628 c_effective_date IN ghr_pa_requests.effective_date%type)
3629 IS
3630 SELECT paf.assignment_id
3631 FROM per_assignments_f paf
3632 WHERE paf.person_id = c_person_id
3633 AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
3634 AND paf.primary_flag = 'Y'
3635 AND paf.assignment_type <> 'B';
3636 ----
3637 BEGIN
3638 hr_utility.set_location('Entering ...'|| l_proc,5);
3639 g_gm_unadjd_basic_pay := NULL;
3640 gm_unadjusted_pay_flg := NULL;
3641 -- First get the id of standard pay table for later use
3642 FOR c_rec IN get_std_user_table_id LOOP
3643 l_std_user_table_id := c_rec.user_table_id;
3644 END LOOP;
3645
3646 IF p_retained_grade.grade_or_level IS NULL THEN
3647 l_grade := p_pay_calc_data.grade_or_level;
3648 ELSE
3649 l_grade := p_retained_grade.grade_or_level;
3650 END IF;
3651
3652 l_adjustment_percentage := get_lpa_percentage
3653 (p_pay_calc_data.duty_station_id
3654 ,p_pay_calc_data.effective_date);
3655
3656 -- Get From assignment id and position id using person_id and effective date
3657 FOR ctr_get_asgn_pos IN get_asgn_pos(p_pay_calc_data.person_id,(p_pay_calc_data.effective_date - 1)) LOOP
3658 l_assignment_id := ctr_get_asgn_pos.assignment_id;
3659 END LOOP;
3660
3661 --
3662 -- Fetch the value Unadjusted Basic pay. The very first time user will enter it.
3663 --
3664 begin
3665
3666 ghr_api.retrieve_element_entry_value (p_element_name => 'Unadjusted Basic Pay'
3667 ,p_input_value_name => 'Amount'
3668 ,p_assignment_id => l_assignment_id
3669 ,p_effective_date => (p_pay_calc_data.effective_date - 1)
3670 ,p_value => l_value
3671 ,p_multiple_error_flag => l_multi_error_flag);
3672 l_gm_unadjd_basic_pay := to_number(l_value);
3673 if l_gm_unadjd_basic_pay is null or l_gm_unadjd_basic_pay = 0 then
3674 gm_unadjusted_pay_flg := 'Y';
3675 hr_utility.set_location('Unadjusted Basic Pay is zero or null ' || sqlerrm(sqlcode),25);
3676 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3677 hr_utility.raise_error;
3678 end if;
3679 exception
3680 when others then
3681 gm_unadjusted_pay_flg := 'Y';
3682 hr_utility.set_location('Error in fetching of Unadjusted Basic Pay ' || sqlerrm(sqlcode),25);
3683 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3684 hr_utility.raise_error;
3685 end;
3686
3687 --
3688 -- Present Year values.
3689 --
3690 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3691 ,'GS'
3692 ,l_grade
3693 ,p_pay_calc_data.effective_date
3694 ,l_dummy_step
3695 ,l_std_min
3696 ,l_effective_start_date
3697 ,l_effective_end_date);
3698 --
3699 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3700 ,'GS'
3701 ,l_grade
3702 ,p_pay_calc_data.effective_date
3703 ,l_dummy_step
3704 ,l_std_max
3705 ,l_effective_start_date
3706 ,l_effective_end_date);
3707
3708 -- Previous Year values.
3709 --
3710 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3711 ,'GS'
3712 ,l_grade
3713 ,(l_effective_start_date - 1)
3714 ,l_dummy_step
3715 ,l_std_min_old
3716 ,l_dummy_date
3717 ,l_dummy_date);
3718 --
3719 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3720 ,'GS'
3721 ,l_grade
3722 ,(l_effective_start_date - 1)
3723 ,l_dummy_step
3724 ,l_std_max_old
3725 ,l_dummy_date
3726 ,l_dummy_date);
3727
3728 l_B1 := l_gm_unadjd_basic_pay - l_std_min_old;
3729 l_B2 := l_std_max_old - l_std_min_old;
3730 l_B3 := TRUNC( (l_B1/l_B2) ,7);
3731 l_B4 := l_std_max - l_std_min;
3732 l_B5 := CEIL((l_B3 * l_B4));
3733 l_B6 := l_B5 + l_std_min;
3734 l_B7 := ROUND(l_B6 * (NVL(l_adjustment_percentage,0)/100),0);
3735 l_B8 := l_B6 + l_B7;
3736 new_locality_adj := l_B8 - p_new_basic_pay;
3737 new_adj_basic_pay := p_new_basic_pay + new_locality_adj;
3738
3739 p_GM_unadjusted_rate := l_B6;
3740 g_gm_unadjd_basic_pay := l_B6;
3741 p_new_adj_basic_pay := new_adj_basic_pay;
3742 p_new_locality_adj := new_locality_adj;
3743 hr_utility.set_location('Leaving ...'|| l_proc,5);
3744
3745 EXCEPTION
3746 WHEN others THEN
3747 -- Reset IN OUT parameters and set OUT parameters
3748 p_GM_unadjusted_rate :=NULL;
3749 p_new_adj_basic_pay :=NULL;
3750 p_new_locality_adj :=NULL;
3751 hr_utility.set_location('Leaving.... ' || l_proc,6);
3752 RAISE;
3753 END get_locality_894_itpay;
3754
3755 --------------------------- <get_locality_892_itpay> ------------------------------------------
3756 PROCEDURE get_locality_892_itpay
3757 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3758 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3759 ,p_new_basic_pay IN NUMBER
3760 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3761 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3762
3763 l_std_user_table_id NUMBER;
3764 l_user_table_id NUMBER;
3765 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
3766
3767 l_grade VARCHAR2(30);
3768
3769 l_std_min NUMBER;
3770 l_std_max NUMBER;
3771 l_it_min NUMBER;
3772 l_it_max NUMBER;
3773
3774 l_dummy_step VARCHAR2(30);
3775 l_dummy_date DATE;
3776 l_effective_start_date DATE;
3777 l_effective_end_date DATE;
3778
3779 new_adj_basic_pay NUMBER;
3780 new_locality_adj NUMBER;
3781
3782 l_C1 NUMBER;
3783 l_C2 NUMBER;
3784 l_C3 NUMBER;
3785 l_C4 NUMBER;
3786 l_C5 NUMBER;
3787 l_C6 NUMBER;
3788 l_dummy_number NUMBER;
3789
3790 l_GM_unadjusted_rate NUMBER;
3791 l_proc VARCHAR2(30) := 'GMIT_892';
3792
3793 CURSOR get_std_user_table_id IS
3794 SELECT utb.user_table_id
3795 FROM pay_user_tables utb
3796 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3797
3798 ----
3799 l_assignment_id per_assignments_f.assignment_id%type;
3800 l_value varchar2(60);
3801 l_multi_error_flag BOOLEAN;
3802 l_gm_unadjd_basic_pay Number;
3803
3804 CURSOR get_asgn_pos (c_person_id IN ghr_pa_requests.person_id%type,
3805 c_effective_date IN ghr_pa_requests.effective_date%type)
3806 IS
3807 SELECT paf.assignment_id
3808 FROM per_assignments_f paf
3809 WHERE paf.person_id = c_person_id
3810 AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
3811 AND paf.primary_flag = 'Y'
3812 AND paf.assignment_type <> 'B';
3813 ----
3814 BEGIN
3815 hr_utility.set_location('Entering ...'|| l_proc,5);
3816 g_gm_unadjd_basic_pay := NULL;
3817 gm_unadjusted_pay_flg := NULL;
3818 -- First get the id of standard pay table for later use
3819 FOR c_rec IN get_std_user_table_id LOOP
3820 l_std_user_table_id := c_rec.user_table_id;
3821 END LOOP;
3822
3823 IF p_retained_grade.grade_or_level IS NULL THEN
3824 l_grade := p_pay_calc_data.grade_or_level;
3825 l_user_table_id := p_pay_calc_data.user_table_id;
3826 ELSE
3827 l_grade := p_retained_grade.grade_or_level;
3828 l_user_table_id := p_retained_grade.user_table_id;
3829 END IF;
3830
3831 l_adjustment_percentage := get_lpa_percentage
3832 (p_pay_calc_data.duty_station_id
3833 ,p_pay_calc_data.effective_date);
3834
3835 -- Get From assignment id and position id using person_id and effective date
3836 FOR ctr_get_asgn_pos IN get_asgn_pos(p_pay_calc_data.person_id,(p_pay_calc_data.effective_date - 1)) LOOP
3837 l_assignment_id := ctr_get_asgn_pos.assignment_id;
3838 END LOOP;
3839
3840 --
3841 -- Fetch the value Unadjusted Basic pay. The very first time user will enter it.
3842 --
3843 begin
3844
3845 ghr_api.retrieve_element_entry_value (p_element_name => 'Unadjusted Basic Pay'
3846 ,p_input_value_name => 'Amount'
3847 ,p_assignment_id => l_assignment_id
3848 ,p_effective_date => (p_pay_calc_data.effective_date - 1)
3849 ,p_value => l_value
3850 ,p_multiple_error_flag => l_multi_error_flag);
3851 l_gm_unadjd_basic_pay := to_number(l_value);
3852 if l_gm_unadjd_basic_pay is null or l_gm_unadjd_basic_pay = 0 then
3853 gm_unadjusted_pay_flg := 'Y';
3854 hr_utility.set_location('Unadjusted Basic Pay is zero or null ' || sqlerrm(sqlcode),25);
3855 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3856 hr_utility.raise_error;
3857 end if;
3858 exception
3859 when others then
3860 gm_unadjusted_pay_flg := 'Y';
3861 hr_utility.set_location('Error in fetching of Unadjusted Basic Pay ' || sqlerrm(sqlcode),25);
3862 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3863 hr_utility.raise_error;
3864 end;
3865 --
3866 -- Present Year values.
3867 --
3868 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3869 ,'GS'
3870 ,l_grade
3871 ,p_pay_calc_data.effective_date
3872 ,l_dummy_step
3873 ,l_std_min
3874 ,l_effective_start_date
3875 ,l_effective_end_date);
3876 --
3877 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3878 ,'GS'
3879 ,l_grade
3880 ,p_pay_calc_data.effective_date
3881 ,l_dummy_step
3882 ,l_std_max
3883 ,l_effective_start_date
3884 ,l_effective_end_date);
3885
3886 -- Present Year values.
3887 --
3888 ghr_pc_basic_pay.get_min_pay_table_value(l_user_table_id
3889 ,'GS'
3890 ,l_grade
3891 ,p_pay_calc_data.effective_date
3892 ,l_dummy_step
3893 ,l_it_min
3894 ,l_dummy_date
3895 ,l_dummy_date);
3896 --
3897 ghr_pc_basic_pay.get_max_pay_table_value(l_user_table_id
3898 ,'GS'
3899 ,l_grade
3900 ,p_pay_calc_data.effective_date
3901 ,l_dummy_step
3902 ,l_it_max
3903 ,l_dummy_date
3904 ,l_dummy_date);
3905
3906 l_C1 := CEIL((l_it_max - l_it_min) / 9);
3907 l_C2 := p_pay_calc_data.current_basic_pay + l_C1;
3908 l_C3 := CEIL((l_std_max - l_std_min) / 9);
3909 l_C4 := l_gm_unadjd_basic_pay + l_C3;
3910 l_C5 := l_C4 + ROUND(l_C4 * (NVL(l_adjustment_percentage,0)/100),0);
3911 l_C6 := l_C5 - l_C2;
3912 new_locality_adj := l_C6;
3913 new_adj_basic_pay := l_C5;
3914
3915 g_gm_unadjd_basic_pay := l_C4;
3916
3917 p_new_adj_basic_pay := new_adj_basic_pay;
3918 p_new_locality_adj := new_locality_adj;
3919 hr_utility.set_location('Leaving ...'|| l_proc,5);
3920
3921 EXCEPTION
3922 WHEN others THEN
3923 -- Reset IN OUT parameters and set OUT parameters
3924 p_new_adj_basic_pay := NULL;
3925 p_new_locality_adj := NULL;
3926 hr_utility.set_location('Leaving.... ' || l_proc,6);
3927 RAISE;
3928 END get_locality_892_itpay;
3929
3930 -- FWFA Changes. Created procedures get_special_pay_table_value, special_rate_pay_calc
3931 -- Create function fwfa_pay_calc
3932 FUNCTION fwfa_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3933 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
3934 RETURN BOOLEAN IS
3935 CURSOR cur_ppl(p_pay_plan VARCHAR2) IS
3936 SELECT 1
3937 FROM ghr_pay_plans ppl
3938 WHERE ppl.pay_plan = p_pay_plan
3939 AND ppl.equivalent_pay_plan = 'GS';
3940
3941 CURSOR cur_ppl_fw(p_pay_plan VARCHAR2) IS --5218445
3942 SELECT 1
3943 FROM ghr_pay_plans ppl
3944 WHERE ppl.pay_plan = p_pay_plan
3945 AND ppl.equivalent_pay_plan = 'FW';
3946
3947 l_pay_plan VARCHAR2(30);
3948 l_user_table_id pay_user_tables.user_table_id%type;
3949 l_user_table_name pay_user_tables.user_table_name%type;
3950 -- Bug 4695312
3951 l_gs_equivalent BOOLEAN;
3952 BEGIN
3953 l_gs_equivalent := FALSE;
3954 -- Removed the PRD comparison. Modified it with Pay Table Comparison
3955 IF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V') THEN -- 5218445
3956 IF p_retained_grade.temp_step is not null AND
3957 p_pay_calc_data.noa_code <> '740' then
3958 l_user_table_id := p_pay_calc_data.user_table_id;
3959 l_pay_plan := p_pay_calc_data.pay_plan;
3960 ELSE
3961 l_user_table_id := p_retained_grade.user_table_id;
3962 l_pay_plan := p_retained_grade.pay_plan;
3963 END IF;
3964 ELSE
3965 l_user_table_id := p_pay_calc_data.user_table_id;
3966 l_pay_plan := p_pay_calc_data.pay_plan;
3967 END IF;
3968
3969 FOR cur_ppl_fw_rec IN cur_ppl_fw(l_pay_plan)
3970 LOOP
3971 IF p_pay_calc_data.pay_rate_determinant IN ('6','E','F') THEN
3972 g_fwfa_pay_calc_flag := TRUE;
3973 g_fw_equiv_pay_plan := TRUE;
3974 RETURN TRUE;
3975 END IF;
3976 END LOOP;
3977
3978
3979 IF p_pay_calc_data.effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
3980 l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
3981 --
3982 FOR cur_ppl_rec IN cur_ppl(l_pay_plan)
3983 LOOP
3984 l_gs_equivalent := TRUE;
3985 END LOOP;
3986 -- Bug#4695312 For Retained Pay PRDs don't verify position pay table.
3987 -- IF the pay plan is GS Equivalent, Return TRUE.
3988 --Bug# 9255822 added PRD Y
3989 IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R','Y') THEN --bug#4999237
3990 IF l_gs_equivalent THEN
3991 g_fwfa_pay_calc_flag := TRUE;
3992 RETURN TRUE;
3993 ELSE
3994 g_fwfa_pay_calc_flag := FALSE;
3995 RETURN FALSE;
3996 END IF;
3997 ELSE
3998 IF l_user_table_name NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) AND
3999 l_gs_equivalent THEN
4000 g_fwfa_pay_calc_flag := TRUE;
4001 RETURN TRUE;
4002 ELSE
4003 g_fwfa_pay_calc_flag := FALSE;
4004 RETURN FALSE;
4005 END IF;
4006 END IF;
4007 ELSE
4008 g_fwfa_pay_calc_flag := FALSE;
4009 RETURN FALSE;
4010 END IF;
4011 END fwfa_pay_calc;
4012
4013 PROCEDURE get_special_pay_table_value (p_pay_plan IN VARCHAR2
4014 ,p_grade_or_level IN VARCHAR2
4015 ,p_step_or_rate IN VARCHAR2
4016 ,p_user_table_id IN NUMBER
4017 ,p_effective_date IN DATE
4018 ,p_pt_value OUT NOCOPY NUMBER
4019 ,p_PT_eff_start_date OUT NOCOPY DATE
4020 ,p_PT_eff_end_date OUT NOCOPY DATE
4021 ,p_pp_grd_exists OUT NOCOPY BOOLEAN) IS
4022 --
4023 l_PT_value NUMBER;
4024 l_PT_eff_start_date DATE;
4025 l_PT_eff_end_date DATE;
4026 l_record_found BOOLEAN := FALSE;
4027
4028 --
4029 -- Go and get the basic pay from the given pay table at the given grade or level
4030 -- and step
4031 -- NOTE: column => Step or Rate
4032 -- row => Pay Plan ||'-'|| Grade or Level
4033 --
4034 CURSOR cur_pay IS
4035 SELECT cin.value basic_pay
4036 ,cin.effective_start_date
4037 ,cin.effective_end_date
4038 ,col.user_column_name step
4039 FROM pay_user_column_instances_f cin
4040 ,pay_user_rows_f urw
4041 ,pay_user_columns col
4042 WHERE col.user_table_id = p_user_table_id
4043 -- AND col.user_column_name = p_step_or_rate
4044 AND urw.user_table_id = p_user_table_id
4045 --Bug# 7046241 added decode for pay plan GP
4046 AND urw.row_low_range_or_name = decode(p_pay_plan,'GP','GS',p_pay_plan)||'-'||p_grade_or_level
4047 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
4048 AND cin.user_row_id = urw.user_row_id
4049 AND cin.user_column_id = col.user_column_id
4050 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;
4051 BEGIN
4052 FOR cur_pay_rec IN cur_pay LOOP
4053 l_record_found := TRUE;
4054 IF cur_pay_rec.step = p_step_or_rate THEN
4055 l_PT_value := cur_pay_rec.basic_pay;
4056 l_PT_eff_start_date := cur_pay_rec.effective_start_date;
4057 l_PT_eff_end_date := cur_pay_rec.effective_end_date;
4058 --
4059 EXIT;
4060 END IF;
4061 END LOOP;
4062 --
4063 IF NOT l_record_found THEN
4064 p_pp_grd_exists := FALSE;
4065 p_pt_value := NULL;
4066 p_pt_eff_start_date := NULL;
4067 p_pt_eff_end_date := NULL;
4068 ELSE
4069 p_PT_value := l_PT_value;
4070 p_PT_eff_start_date := l_PT_eff_start_date;
4071 p_PT_eff_end_date := l_PT_eff_end_date;
4072 p_pp_grd_exists := TRUE;
4073 END IF;
4074
4075 EXCEPTION
4076 WHEN others THEN
4077 -- Reset IN OUT parameters and set OUT parameters
4078 p_PT_value := NULL;
4079 p_PT_eff_start_date := NULL;
4080 p_PT_eff_end_date := NULL;
4081 p_pp_grd_exists := NULL;
4082 RAISE;
4083 --
4084 END get_special_pay_table_value;
4085
4086 PROCEDURE special_rate_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
4087 ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
4088 ,p_retained_grade IN OUT NOCOPY ghr_pay_calc.retained_grade_rec_type) IS
4089
4090 l_new_prd VARCHAR2(30);
4091 l_new_step VARCHAR2(30);
4092 l_term_ret_pay_prd BOOLEAN;
4093 l_pay_plan VARCHAR2(5);
4094 l_default_pay_plan VARCHAR2(5);
4095 l_step_or_rate VARCHAR2(5);
4096 l_curr_step_or_rate VARCHAR2(5);
4097 l_grade_or_level VARCHAR2(5);
4098 l_pay_calc_process VARCHAR2(1);
4099 l_dummy VARCHAR2(1);
4100 l_pt_value NUMBER;
4101 l_locality_adj NUMBER;
4102 l_basic_pay NUMBER;
4103 l_adj_basic_pay NUMBER;
4104 l_new_basic_pay NUMBER;
4105 l_new_locality_adj NUMBER;
4106 l_new_adj_basic_pay NUMBER;
4107 l_increment NUMBER;
4108 l_old_basic_pay NUMBER;
4109 l_old_locality_adj NUMBER;
4110 l_old_adj_basic_pay NUMBER;
4111 l_adj_perc_factor NUMBER(10,6);
4112 l_old_adj_perc_factor NUMBER(10,6);
4113 l_new_special_rate NUMBER; -- AC for Bug#5114467
4114 l_dummy_date DATE;
4115 l_pt_eff_start_date DATE;
4116 l_pt_eff_end_date DATE;
4117 l_pp_grd_exists BOOLEAN;
4118 l_rg_flag BOOLEAN;
4119 l_leo_flag BOOLEAN;
4120 l_ret_flag BOOLEAN ;
4121 l_loc_rate_less_spl_rate BOOLEAN;
4122 l_user_table_name pay_user_tables.user_table_name%type;
4123 l_default_pay_table pay_user_tables.user_table_name%type;
4124 l_new_position_pay_table pay_user_tables.user_table_name%type;
4125 l_calculation_pay_table pay_user_tables.user_table_name%type;
4126 l_position_pay_table pay_user_tables.user_table_name%type;
4127 l_retained_pay_table pay_user_tables.user_table_name%type;
4128 l_user_table_id pay_user_tables.user_table_id%type;
4129 l_default_table_id pay_user_tables.user_table_id%type;
4130 l_calculation_pay_table_id pay_user_tables.user_table_id%type;
4131 l_position_pay_table_id pay_user_tables.user_table_id%type;
4132
4133 --Bug # 10043212
4134 l_old_pp_grd_exists BOOLEAN;
4135
4136 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%type;
4137
4138 l_old_special_rate NUMBER;
4139 l_special_rate NUMBER; --for bug 4999237
4140 l_gm_pay_plan BOOLEAN;
4141 --Begin Bug 7046241
4142 l_gr_pay_plan BOOLEAN;
4143 --End Bug 7046241
4144
4145 CURSOR c_get_table_id(p_user_table_name VARCHAR2) IS
4146 SELECT utb.user_table_id user_table_id, utb.user_table_name user_table_name
4147 FROM pay_user_tables utb
4148 WHERE utb.user_table_name = p_user_table_name;
4149
4150 ------ Bug#5741977 start
4151 l_business_group_id per_positions.organization_id%TYPE;
4152
4153 CURSOR GET_GRADE_ID (v_pay_plan varchar2, v_grade_or_level varchar2) IS
4154 SELECT grd.grade_id grade_id
4155 FROM per_grades grd,
4156 per_grade_definitions gdf
4157 WHERE gdf.segment1 = v_pay_plan
4158 AND gdf.segment2 = v_grade_or_level
4159 AND grd.grade_definition_id = gdf.grade_definition_id
4160 AND grd.business_group_id = l_business_group_id;
4161
4162 l_grade_id NUMBER;
4163 l_fwfa_pay_plan_changed BOOLEAN;
4164 ------ Bug#5741977 end
4165
4166 -- 7046241
4167 l_pos_ei_valid_grade per_position_extra_info%ROWTYPE;
4168 -- 7046241
4169
4170 --Bug# 9258929
4171 l_from_position_id ghr_pa_requests.from_position_id%type;
4172 --Bug # 10368444
4173 l_from_pay_plan ghr_pa_requests.from_pay_plan%type;
4174 l_from_grade_id NUMBER;
4175 l_from_pos_ind VARCHAR2(30);
4176 l_to_pos_ind VARCHAR2(30);
4177 l_equivalent_pay_plan ghr_pay_plans.equivalent_pay_plan%type;
4178 l_session ghr_history_api.g_session_var_type;
4179
4180
4181 cursor get_equi_pay_plan(p_pay_plan in varchar2)
4182 is
4183 select equivalent_pay_plan
4184 from ghr_pay_plans
4185 where pay_plan = p_pay_plan;
4186
4187 cursor get_asgn_pos(c_person_id IN ghr_pa_requests.person_id%type,
4188 c_effective_date IN ghr_pa_requests.effective_date%type)
4189 is
4190 select paf.position_id,
4191 paf.grade_id
4192 from per_all_assignments_f paf
4193 where paf.person_id = c_person_id
4194 and trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
4195 and paf.primary_flag = 'Y'
4196 and paf.assignment_type <> 'B';
4197
4198 cursor get_pa_req_det(p_pa_request_id in number)
4199 is
4200 select from_position_id,
4201 from_pay_plan --Added for Bug # 10368444
4202 from ghr_pa_requests
4203 where pa_request_id = p_pa_request_id;
4204
4205 cursor c_grade_kff (grd_id number) is
4206 select gdf.segment1,
4207 gdf.segment2
4208 from per_grades grd,
4209 per_grade_definitions gdf
4210 where grd.grade_id = grd_id
4211 and grd.grade_definition_id = gdf.grade_definition_id;
4212
4213 -- Bug # 9258929
4214
4215 PROCEDURE set_cpt_ppt_prd(p_leo_flag IN BOOLEAN
4216 ,p_rg_flag IN BOOLEAN
4217 ,p_pp_grd_exists IN BOOLEAN
4218 ,p_loc_rate_less_spl_rate IN BOOLEAN
4219 ,p_prd IN VARCHAR2
4220 ,p_pt_value IN NUMBER
4221 ,p_special_rate_table IN VARCHAR2
4222 ,p_calculation_pay_table OUT NOCOPY VARCHAR2
4223 ,p_position_pay_table OUT NOCOPY VARCHAR2
4224 ,p_retained_pay_table OUT NOCOPY VARCHAR2
4225 ,p_new_prd OUT NOCOPY VARCHAR2
4226 ) IS
4227 BEGIN
4228 hr_utility.set_location('Inside set_cpt_ppt_prd',0);
4229
4230 IF p_leo_flag THEN
4231 --Bug# 5635023 added PRD 7
4232 IF p_prd IN ('5', '6', '7') THEN
4233 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4234 IF p_loc_rate_less_spl_rate THEN
4235 p_position_pay_table := p_special_rate_table;
4236 p_new_prd := '6';
4237 p_calculation_pay_table := p_special_rate_table;
4238 g_pay_table_upd_flag := FALSE;
4239 ELSE
4240 p_position_pay_table := p_special_rate_table;
4241 p_new_prd := '6';
4242 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4243 g_pay_table_upd_flag := FALSE;
4244 END IF;
4245 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4246 p_position_pay_table := p_special_rate_table;
4247 p_new_prd := '6';
4248 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4249 g_pay_table_upd_flag := FALSE;
4250 ELSE
4251 p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4252 p_new_prd := '6';
4253 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4254 g_pay_table_upd_flag := TRUE;
4255 END IF;
4256 ELSIF p_prd IN ('E','F') THEN
4257 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4258 IF p_loc_rate_less_spl_rate THEN
4259 p_retained_pay_table := p_special_rate_table;
4260 p_new_prd := p_prd;
4261 p_calculation_pay_table := p_special_rate_table;
4262 g_pay_table_upd_flag := FALSE;
4263 ELSE
4264 p_retained_pay_table := p_special_rate_table;
4265 p_new_prd := p_prd;
4266 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4267 g_pay_table_upd_flag := FALSE;
4268 END IF;
4269 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4270 p_retained_pay_table := p_special_rate_table;
4271 p_new_prd := p_prd;
4272 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4273 g_pay_table_upd_flag := FALSE;
4274 ELSE
4275 p_retained_pay_table := ghr_pay_calc.l_spl491_table_name;
4276 p_new_prd := p_prd;
4277 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4278 g_pay_table_upd_flag := TRUE;
4279 END IF;
4280 ELSIF p_prd IN ('3','4','J','K','U','V','R','Y') THEN --bug 4999237 -- Bug# 9255822 added PRD Y
4281 IF NOT(p_pp_grd_exists) THEN
4282 p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4283 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4284 g_pay_table_upd_flag := TRUE;
4285 ELSE
4286 p_position_pay_table := p_special_rate_table;
4287 g_pay_table_upd_flag := FALSE;
4288 p_calculation_pay_table := p_special_rate_table;
4289 END IF;
4290 IF p_pay_calc_data.pay_rate_determinant = '4' THEN
4291 p_new_prd := 'J';
4292 ELSE
4293 p_new_prd := p_prd;
4294 END IF;
4295 ELSIF p_prd IN ('0','A','B') THEN
4296 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4297 IF p_loc_rate_less_spl_rate THEN
4298 p_position_pay_table := p_special_rate_table;
4299 p_calculation_pay_table := p_special_rate_table;
4300 g_pay_table_upd_flag := FALSE;
4301 ELSE
4302 p_position_pay_table := p_special_rate_table;
4303 p_new_prd := p_prd;
4304 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4305 g_pay_table_upd_flag := FALSE;
4306 END IF;
4307 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4308 p_position_pay_table := p_special_rate_table;
4309 p_new_prd := p_prd;
4310 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4311 g_pay_table_upd_flag := FALSE;
4312 ELSE
4313 p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4314 p_new_prd := p_prd;
4315 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4316 g_pay_table_upd_flag := TRUE;
4317 END IF;
4318 IF p_prd = '0' THEN
4319 p_new_prd := '6';
4320 ELSIF p_prd = 'A' THEN
4321 p_new_prd := 'E';
4322 ELSIF p_prd = 'B' THEN
4323 p_new_prd := 'F';
4324 END IF;
4325 END IF;
4326 ELSE -- NON LEO
4327 --Bug# 5635023 added PRD 7
4328 IF p_prd IN ('5', '6', '7') THEN
4329 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4330 IF p_loc_rate_less_spl_rate THEN
4331 p_position_pay_table := p_special_rate_table;
4332 p_new_prd := '6';
4333 p_calculation_pay_table := p_special_rate_table;
4334 g_pay_table_upd_flag := FALSE;
4335 ELSE
4336 p_position_pay_table := p_special_rate_table;
4337 p_new_prd := '0';
4338 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4339 g_pay_table_upd_flag := FALSE;
4340 END IF;
4341 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4342 p_position_pay_table := p_special_rate_table;
4343 p_new_prd := '0';
4344 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4345 g_pay_table_upd_flag := FALSE;
4346 ELSE
4347 p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4348 p_new_prd := '0';
4349 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4350 g_pay_table_upd_flag := TRUE;
4351 END IF;
4352 ELSIF p_prd IN ('E','F') THEN
4353 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4354 IF p_loc_rate_less_spl_rate THEN
4355 p_retained_pay_table := p_special_rate_table;
4356 p_new_prd := p_prd;
4357 p_calculation_pay_table := p_special_rate_table;
4358 g_pay_table_upd_flag := FALSE;
4359 ELSE
4360 p_retained_pay_table := p_special_rate_table;
4361 IF p_prd = 'E' THEN
4362 p_new_prd := 'A';
4363 ELSIF p_prd = 'F' THEN
4364 p_new_prd := 'B';
4365 END IF;
4366 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4367 g_pay_table_upd_flag := FALSE;
4368 END IF;
4369 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4370 p_retained_pay_table := p_special_rate_table;
4371 IF p_prd = 'E' THEN
4372 p_new_prd := 'A';
4373 ELSIF p_prd = 'F' THEN
4374 p_new_prd := 'B';
4375 END IF;
4376 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4377 g_pay_table_upd_flag := FALSE;
4378 ELSE
4379 p_retained_pay_table := ghr_pay_calc.l_standard_table_name;
4380 IF p_prd = 'E' THEN
4381 p_new_prd := 'A';
4382 ELSIF p_prd = 'F' THEN
4383 p_new_prd := 'B';
4384 END IF;
4385 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4386 g_pay_table_upd_flag := TRUE;
4387 END IF;
4388 ELSIF p_prd IN ('3','4','J','K','U','V','R','Y') THEN -- Bug# 9255822 added PRD Y
4389 IF NOT(p_pp_grd_exists) THEN
4390 p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4391 g_pay_table_upd_flag := TRUE;
4392 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4393 ELSE
4394 p_position_pay_table := p_special_rate_table;
4395 g_pay_table_upd_flag := FALSE;
4396 p_calculation_pay_table := p_special_rate_table;
4397 END IF;
4398 IF p_pay_calc_data.pay_rate_determinant = '4' THEN
4399 p_new_prd := 'J';
4400 ELSE
4401 p_new_prd := p_prd;
4402 END IF;
4403 -- FWFA new change. Added the following condition to handle PRDs 0,A,B.
4404 ELSIF p_prd IN ('0','A','B') THEN
4405 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4406 IF p_loc_rate_less_spl_rate THEN
4407 p_position_pay_table := p_special_rate_table;
4408 IF p_prd = '0' THEN
4409 p_new_prd := '6';
4410 ELSIF p_prd = 'A' THEN
4411 p_new_prd := 'E';
4412 ELSIF p_prd = 'B' THEN
4413 p_new_prd := 'F';
4414 END IF;
4415 p_calculation_pay_table := p_special_rate_table;
4416 g_pay_table_upd_flag := FALSE;
4417 ELSE
4418 p_position_pay_table := p_special_rate_table;
4419 p_new_prd := p_prd;
4420 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4421 g_pay_table_upd_flag := FALSE;
4422 END IF;
4423 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4424 p_position_pay_table := p_special_rate_table;
4425 p_new_prd := p_prd;
4426 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4427 g_pay_table_upd_flag := FALSE;
4428 ELSE
4429 p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4430 p_new_prd := p_prd;
4431 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4432 g_pay_table_upd_flag := TRUE;
4433 END IF;
4434 END IF;
4435 END IF;
4436 hr_utility.set_location('Leaving CPT PRD',10);
4437 END set_cpt_ppt_prd;
4438
4439 BEGIN
4440
4441 ------ Bug#5741977 start
4442 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
4443 l_fwfa_pay_plan_changed := FALSE;
4444 ------ Bug#5741977 end
4445 --
4446
4447
4448
4449 if p_pay_calc_data.noa_family_code = 'APP' then
4450 if p_pay_calc_data.pay_rate_determinant
4451 in ('2','C','M','P','R','S') then
4452 p_pay_calc_out_data.open_pay_fields := TRUE;
4453 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
4454 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
4455 raise ghr_pay_calc.unable_to_calculate;
4456 end if;
4457 end if;
4458 ---
4459
4460
4461 -- 1. Set the Pay related values depending on the Retained/Non-Retained Grade.
4462 hr_utility.set_location('PRD: '||p_pay_calc_data.pay_rate_determinant,20);
4463 IF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V') and
4464 p_retained_grade.temp_step is NULL THEN
4465 l_user_table_id := p_retained_grade.user_table_id;
4466 l_pay_plan := p_retained_grade.pay_plan;
4467 l_grade_or_level:= p_retained_grade.grade_or_level;
4468 l_step_or_rate := p_retained_grade.step_or_rate;
4469 l_rg_flag := TRUE;
4470 ELSE
4471 l_user_table_id := p_pay_calc_data.user_table_id;
4472 l_pay_plan := p_pay_calc_data.pay_plan;
4473 l_grade_or_level:= p_pay_calc_data.grade_or_level;
4474 -- Added NOA Code 867 for the Bug#5621814
4475 IF p_pay_calc_data.noa_code IN ('867', '892','893') OR
4476 p_pay_calc_data.second_noa_code IN ('867', '892','893') THEN
4477 l_step_or_rate := p_pay_calc_data.current_step_or_rate;
4478 ELSE
4479 l_step_or_rate := p_pay_calc_data.step_or_rate;
4480 END IF;
4481 -- Bug#4701896
4482 IF p_retained_grade.temp_step IS NOT NULL THEN
4483 l_step_or_rate := p_retained_grade.temp_step;
4484 END IF;
4485 l_rg_flag := FALSE;
4486 END IF;
4487
4488 l_gm_pay_plan := FALSE;
4489 --Begin Bug 7046241
4490 l_gr_pay_plan := FALSE;
4491 --End Bug 7046241
4492 -- 2. Get the user table name into local variable
4493 l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
4494 hr_utility.set_location('user table name:' ||l_user_table_name,30);
4495 -- 3. Get the Locality Percentage Value
4496 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_pay_calc_data.duty_station_id
4497 ,p_pay_calc_data.effective_date),0);
4498 hr_utility.set_location('l_adjustment_percentage ..' || to_char(l_adjustment_percentage),31);
4499 l_adj_perc_factor := l_adjustment_percentage/100;
4500 hr_utility.set_location('l_adj_perc_factor ..' || to_char(l_adj_perc_factor),32);
4501
4502
4503
4504 -- Check LEO Posiiton or NOT
4505 -- Bug#4709111 Modified p_pay_calc_data.grade_or_level to l_grade_or_level.
4506 -- Bug#5089732 Treat all the positions with pay plan GL as LEO Positions.
4507 IF (ghr_pay_calc.LEO_position (l_dummy
4508 ,p_pay_calc_data.position_id
4509 ,l_dummy
4510 ,l_dummy
4511 ,p_pay_calc_data.effective_date)
4512 AND l_grade_or_level between 03 and 10) OR
4513 l_pay_plan = 'GL' THEN
4514 l_leo_flag := TRUE;
4515 l_default_pay_table := ghr_pay_calc.l_spl491_table_name;
4516 ELSE
4517 l_leo_flag := FALSE;
4518 l_default_pay_table := ghr_pay_calc.l_standard_table_name;
4519 END IF;
4520
4521
4522 --Bug #5919700 For 890 action and GM Pay plan GS should be considered
4523 IF (p_pay_calc_data.noa_code = '890' OR p_pay_calc_data.second_noa_code = '890') AND
4524 p_pay_calc_data.pay_plan = 'GM' then
4525 l_default_pay_plan := 'GS';
4526 l_pay_plan := 'GS';
4527 l_grade_id := NULL;
4528 FOR get_grade_id_rec IN get_grade_id(l_default_pay_plan, l_grade_or_level)
4529 LOOP
4530 l_grade_id := get_grade_id_rec.grade_id;
4531 l_fwfa_pay_plan_changed := TRUE;
4532 g_pay_table_upd_flag := TRUE;
4533 exit;
4534 END LOOP;
4535 END IF;
4536
4537
4538 -- Bug#5741977 - Set the default Pay Plan as GL as GS, GG are end dated in 0491 table.
4539 IF l_default_pay_table = ghr_pay_calc.l_spl491_table_name AND
4540 l_pay_plan IN ('GS','GG') AND
4541 p_pay_calc_data.effective_date >= to_date('2007/01/07','YYYY/MM/DD') THEN
4542 l_default_pay_plan := 'GL';
4543 ------ Bug#5741977 start
4544 l_grade_id := null;
4545 FOR get_grade_id_rec IN get_grade_id(l_default_pay_plan, l_grade_or_level)
4546 LOOP
4547 l_grade_id := get_grade_id_rec.grade_id;
4548 --BUG # 13549637 modified to not to change pay plan
4549 -- GL will be considered only in calculation
4550 --l_fwfa_pay_plan_changed := TRUE;
4551 exit;
4552 END LOOP;
4553 ------ Bug#5741977 End
4554 ELSE
4555 l_default_pay_plan := l_pay_plan;
4556 END IF;
4557
4558
4559
4560
4561
4562 FOR table_id_rec in c_get_table_id(l_default_pay_table)
4563 LOOP
4564 l_default_table_id := table_id_rec.user_table_id;
4565 END LOOP;
4566
4567 -- 4. Processing Employees On Retained Pay
4568 -- Bug# 9255822 added PRD Y
4569 IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R','Y')
4570 AND NOT(l_pay_plan IN ('GR') AND p_pay_calc_data.pay_rate_determinant IN ('4')) THEN --bug 4999237
4571 --Bug# 7518210,to skip this calc and moving to else part(i.e GM calc)added pay plan GR and PRD 4 condition
4572 -----Bug 4665119 ..Other than appointment and 894 for all other actions pay fields are to be open
4573 ----- Appointment already checked in the fwfa_pay_cal function.
4574 ----- Modified by AVR 17-OCT-2005
4575 -- Bug#5368848 Open the pay fields if the NOA Code is not 894.
4576 -- GPPA Update46. Added 890 in the NOT IN list.
4577 IF p_pay_calc_data.noa_code NOT IN ('894','890') then
4578 p_pay_calc_out_data.open_pay_fields := TRUE;
4579 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
4580 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
4581 raise ghr_pay_calc.unable_to_calculate;
4582 END IF;
4583 --Bug# 9258929
4584 -- Get From assignment id and position id using person_id and effective date
4585 ghr_history_api.get_g_session_var(l_session);
4586 IF l_session.noa_id_correct is null then
4587 FOR ctr_get_asgn_pos IN get_asgn_pos(p_pay_calc_data.person_id,p_pay_calc_data.effective_date) LOOP
4588 l_from_position_id := ctr_get_asgn_pos.position_id;
4589 l_from_grade_id := ctr_get_asgn_pos.grade_id;
4590 END LOOP;
4591 --Bug # 10368444
4592 FOR c_grade_kff_rec in c_grade_kff (l_from_grade_id) LOOP
4593 l_from_pay_plan := c_grade_kff_rec.segment1;
4594 exit;
4595 END LOOP;
4596 --Bug # 10368444
4597
4598 ELSE
4599 FOR c_get_pa_req_det IN get_pa_req_det(p_pa_request_id => p_pay_calc_data.pa_request_id)
4600 LOOP
4601 l_from_position_id :=c_get_pa_req_det.from_position_id;
4602 --Bug # 10368444
4603 l_from_pay_plan :=c_get_pa_req_det.from_pay_plan;
4604 END LOOP;
4605 END IF;
4606
4607
4608
4609
4610
4611 for c_equi_pay_plan in get_equi_pay_plan(p_pay_plan => l_pay_plan)
4612 loop
4613 l_equivalent_pay_plan := c_equi_pay_plan.equivalent_pay_plan;
4614 end loop;
4615
4616 l_from_pos_ind := ghr_pa_requests_pkg.get_personnel_system_indicator(l_from_position_id,p_pay_calc_data.effective_date);
4617
4618 hr_utility.set_location('l_from_pos_ind'||l_from_pos_ind,1000);
4619
4620 hr_utility.set_location('l_from_pay_plan'||l_from_pay_plan,1000);
4621 hr_utility.set_location('l_from_position_id'||l_from_position_id,1000);
4622
4623 hr_utility.set_location('l_pay_plan'||l_pay_plan,1000);
4624 hr_utility.set_location('p_pay_calc_data.pay_rate_determinant'||p_pay_calc_data.pay_rate_determinant,1000);
4625
4626 l_to_pos_ind := ghr_pa_requests_pkg.get_personnel_system_indicator(nvl(p_pay_calc_data.position_id,l_from_position_id),p_pay_calc_data.effective_date);
4627 --Bug # 9258929
4628
4629 -- Bug# 4866952 Added the new pay calculation method for Pay Adjustment actions
4630 -- Processed after 01-MAY-2005
4631 IF p_pay_calc_data.effective_date = TO_DATE('2005/05/01','YYYY/MM/DD') THEN
4632 l_new_basic_pay := p_pay_calc_data.current_adj_basic_pay;
4633 l_new_locality_adj := 0;
4634 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4635 get_special_pay_table_value (p_pay_plan => l_pay_plan
4636 ,p_grade_or_level => l_grade_or_level
4637 ,p_step_or_rate => l_step_or_rate
4638 ,p_user_table_id => l_user_table_id
4639 ,p_effective_date => p_pay_calc_data.effective_date
4640 ,p_pt_value => l_pt_value
4641 ,p_PT_eff_start_date => l_pt_eff_start_date
4642 ,p_PT_eff_end_date => l_pt_eff_end_date
4643 ,p_pp_grd_exists => l_pp_grd_exists );
4644 set_cpt_ppt_prd ( p_leo_flag => l_leo_flag
4645 ,p_rg_flag => l_rg_flag
4646 ,p_pp_grd_exists => l_pp_grd_exists
4647 ,p_loc_rate_less_spl_rate => NULL
4648 ,p_prd => p_pay_calc_data.pay_rate_determinant
4649 ,p_pt_value => l_pt_value
4650 ,p_special_rate_table => l_user_table_name
4651 ,p_calculation_pay_table => l_calculation_pay_table
4652 ,p_position_pay_table => l_position_pay_table
4653 ,p_retained_pay_table => l_retained_pay_table
4654 ,p_new_prd => l_new_prd
4655 );
4656 --Bug# 9258929
4657 ELSIF p_pay_calc_data.noa_code = '890' and ( (NVL(l_from_pos_ind,'00') <> '00'
4658 and NVL(l_to_pos_ind,'00') = '00' and l_equivalent_pay_plan = 'GS' and NVL(l_step_or_rate,'@@') = '00') OR --Bug # 10368444
4659 (l_from_pay_plan <> l_pay_plan and p_pay_calc_data.pay_rate_determinant IN ('J','K','Y')) )
4660 THEN
4661 l_new_basic_pay := p_pay_calc_data.current_adj_basic_pay;
4662 l_new_locality_adj := 0;
4663 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4664 get_special_pay_table_value (p_pay_plan => l_pay_plan
4665 ,p_grade_or_level => l_grade_or_level
4666 ,p_step_or_rate => l_step_or_rate
4667 ,p_user_table_id => l_user_table_id
4668 ,p_effective_date => p_pay_calc_data.effective_date
4669 ,p_pt_value => l_pt_value
4670 ,p_PT_eff_start_date => l_pt_eff_start_date
4671 ,p_PT_eff_end_date => l_pt_eff_end_date
4672 ,p_pp_grd_exists => l_pp_grd_exists );
4673 set_cpt_ppt_prd ( p_leo_flag => l_leo_flag
4674 ,p_rg_flag => l_rg_flag
4675 ,p_pp_grd_exists => l_pp_grd_exists
4676 ,p_loc_rate_less_spl_rate => NULL
4677 ,p_prd => p_pay_calc_data.pay_rate_determinant
4678 ,p_pt_value => l_pt_value
4679 ,p_special_rate_table => l_user_table_name
4680 ,p_calculation_pay_table => l_calculation_pay_table
4681 ,p_position_pay_table => l_position_pay_table
4682 ,p_retained_pay_table => l_retained_pay_table
4683 ,p_new_prd => l_new_prd
4684 );
4685 --Bug # 9073576 modified as per the requirement
4686 ELSE--IF p_pay_calc_data.effective_date
4687 l_term_ret_pay_prd := FALSE;
4688 hr_utility.set_location('PAY ADJUSTMENT AS ON 08-JAN',7788);
4689 --CODE CHANGES DONE TO ATLER PAY CALC FOR BUG# 4999237...
4690 IF l_pay_plan = 'GM' THEN
4691 l_pay_plan := 'GS';
4692 --- Bug 5908487 changes...
4693 l_default_pay_plan := 'GS';
4694 l_gm_pay_plan := TRUE;
4695 --Begin Bug 7046241
4696 ELSIF l_pay_plan = 'GR' THEN
4697 l_pay_plan := 'GS';
4698 l_default_pay_plan := 'GS';
4699 l_gr_pay_plan := TRUE;
4700 --End Bug 7046241
4701 END IF;
4702 -- Get Current Years' Special Pay Table Value at step 10
4703
4704 hr_utility.set_location('Get Current Years Special Pay Table Value at step 10',7788);
4705
4706 get_special_pay_table_value (p_pay_plan => l_pay_plan
4707 ,p_grade_or_level => l_grade_or_level
4708 ,p_step_or_rate => '10'
4709 ,p_user_table_id => l_user_table_id
4710 ,p_effective_date => p_pay_calc_data.effective_date
4711 ,p_pt_value => l_basic_pay
4712 ,p_PT_eff_start_date => l_pt_eff_start_date
4713 ,p_PT_eff_end_date => l_pt_eff_end_date
4714 ,p_pp_grd_exists => l_pp_grd_exists );
4715
4716 l_special_rate := l_basic_pay;
4717 hr_utility.set_location('GOt Current Years Special Pay Table Value at step 10'|| l_special_rate,7788);
4718 --Now Get current Years's default pay table value.for max rate....BUG# 4999237.
4719 hr_utility.set_location('Now Get current Years default pay table value.for max rate.',7788);
4720 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4721 ,p_pay_plan =>l_default_pay_plan
4722 ,p_grade_or_level =>l_grade_or_level
4723 ,p_step_or_rate =>'10'
4724 ,p_effective_date =>p_pay_calc_data.effective_date
4725 ,p_PT_value => l_basic_pay
4726 ,p_PT_eff_start_date => l_PT_eff_start_date
4727 ,p_PT_eff_end_date => l_dummy_date);
4728 hr_utility.set_location('Now GOt current Years default pay table value.for max rate.'||l_basic_pay ,7788);
4729
4730 --Bug# 7708264 modified to move getting previous years special rate calculation
4731 -- before if condition as it is being used in the else condition also
4732
4733 --get previous year's special rate value.at step 10 ....BUG# 4999237.
4734 hr_utility.set_location('get previous years special rate value.at step 10',7788);
4735 get_special_pay_table_value(p_pay_plan => l_pay_plan
4736 ,p_grade_or_level => l_grade_or_level
4737 ,p_step_or_rate => '10'
4738 ,p_user_table_id => l_user_table_id
4739 ,p_effective_date => p_pay_calc_data.effective_date - 1
4740 ,p_pt_value => l_old_basic_pay
4741 ,p_PT_eff_start_date => l_pt_eff_start_date
4742 ,p_PT_eff_end_date => l_pt_eff_end_date
4743 ,p_pp_grd_exists => l_old_pp_grd_exists );
4744
4745 l_old_special_rate := l_old_basic_pay; --for bug#4999237...
4746 hr_utility.set_location('gOt previous years special rate value.at step 10'||l_old_special_rate,7788);
4747
4748 -----APPLY LOCALITY % TO NEW DEFAULT TABLE VALUE.....l_adj_perc_factor is already calculated
4749 hr_utility.set_location('APPLY LOCALITY % TO NEW DEFAULT TABLE VALUE' ,7788);
4750 l_locality_adj := ROUND(l_basic_pay * l_adj_perc_factor,0);
4751 hr_utility.set_location('GETTING LOCALITY ADJUSTMENT'||l_locality_adj ,7788);
4752 l_adj_basic_pay := l_basic_pay + l_locality_adj;
4753 hr_utility.set_location('GETTING ADJUSTED BASIC PAY'||l_adj_basic_pay ,7788);
4754
4755 --Bug # 9073576 Calculation of Old special Rate and Old Locality Rate
4756 -- has been moved to before if condition as it is used in both if and else condition
4757 --Pick the special rate ......BUG# 4999237.
4758 --From Now on Calculation is as usual...BUG# 4999237.
4759 --get previous year's special rate value.at step 10 ....BUG# 4999237.
4760 l_old_adj_perc_factor := (NVL(ghr_pay_calc.get_lpa_percentage(p_pay_calc_data.duty_station_id
4761 ,p_pay_calc_data.effective_date - 1
4762 ),0))/100;
4763
4764
4765
4766 hr_utility.set_location('l_old_adj_perc_factor ..' || to_char(l_old_adj_perc_factor),32);
4767 hr_utility.set_location('l_default_table_id: '||l_default_table_id,95);
4768 hr_utility.set_location(' l_pay_plan: '||l_pay_plan,95);
4769 hr_utility.set_location(' l_default_pay_plan: '||l_default_pay_plan,95);
4770 hr_utility.set_location(' l_grade_or_level: '||l_grade_or_level,95);
4771 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4772 ,p_pay_plan =>l_default_pay_plan
4773 ,p_grade_or_level =>l_grade_or_level
4774 ,p_step_or_rate => '10'
4775 ,p_effective_date =>p_pay_calc_data.effective_date - 1
4776 ,p_PT_value => l_old_basic_pay
4777 ,p_PT_eff_start_date => l_PT_eff_start_date
4778 ,p_PT_eff_end_date => l_dummy_date);
4779
4780
4781 hr_utility.set_location('old default table value '||l_old_basic_pay ,7788);
4782 hr_utility.set_location('Old basic at step 10: '||to_char(l_old_BASIC_PAY),90);
4783 l_old_locality_adj := ROUND(l_old_basic_pay * l_old_adj_perc_factor,0);
4784 l_old_adj_basic_pay := l_old_basic_pay + l_old_locality_adj;
4785 hr_utility.set_location('Old adj basic at step 10: '||to_char(l_old_adj_basic_pay),95);
4786 hr_utility.set_location('Old locality at step 10: '||to_char(l_old_locality_adj),95);
4787
4788
4789 ----Now compare special rate with basic pay......BUG# 4999237.
4790 hr_utility.set_location('COMPARING SPL RATE WITH ADJ BASIC PAY',7788);
4791
4792 IF NVL(l_special_rate,0) > l_adj_basic_pay THEN
4793 hr_utility.set_location(' SPL RATE IS > ADJ BASIC PAY',7788);
4794
4795 hr_utility.set_location('::: 5010491 BP::: '||l_basic_pay,10);
4796 hr_utility.set_location('::: 5010491 ABP::: '||l_old_basic_pay,20);
4797 hr_utility.set_location('::: 5010491 UTN::: '||l_user_table_name,30);
4798 -- If both current and previous year's special rate step 10 values are NOT NULL
4799 -- Then new basic = current basic + (new special rate step10 value - old special rate step 10 value/2)
4800
4801 -- Bug # 9073576 as per the new requirement specified in the bug
4802 IF l_old_adj_basic_pay > NVL(l_old_special_rate,0) THEN
4803 l_old_special_rate := l_old_adj_basic_pay;
4804 END IF;
4805
4806 IF l_old_special_rate IS NOT NULL AND l_special_rate IS NOT NULL AND
4807 l_user_table_name NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) THEN
4808
4809 l_increment := Round((l_special_rate - l_old_special_rate)/2,0);
4810 hr_utility.set_location('INCREMENTED HALF VALUE'||l_increment,7788);
4811 l_new_basic_pay := p_pay_calc_data.current_basic_pay + l_increment;
4812 hr_utility.set_location('NEW BASIC PAY'||l_new_basic_pay,7788);
4813 l_new_locality_adj := 0;
4814 hr_utility.set_location('LOCALITY ADJUSTMENT SET TO ZERO',7788);
4815 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4816 l_new_prd := p_pay_calc_data.pay_rate_determinant;
4817 hr_utility.set_location('NEW PRD SET'||l_new_prd ,7788);
4818 l_calculation_pay_table := l_user_table_name;
4819 --6814842 modified to update position pay table and pay_table_upd_flag
4820 --Bug# 10043212 added to set the position pay table only if the grade does not exist
4821 IF NOT(l_pp_grd_exists) THEN
4822 l_position_pay_table := l_default_pay_table;
4823 g_pay_table_upd_flag := TRUE;
4824 ELSE
4825 l_position_pay_table := l_user_table_name;
4826 g_pay_table_upd_flag := FALSE;
4827 END IF;
4828
4829 hr_utility.set_location('COMPARING NEW BASIC PAY AND NEW SPECIAL RATE VALUE',7788);
4830
4831 IF l_new_basic_pay <= l_special_rate THEN
4832 l_new_adj_basic_pay := l_special_rate;
4833 hr_utility.set_location('SPECIAL RATE IS GREATER'||l_special_rate,7788);
4834 hr_utility.set_location('get current value for step10 in default table',7788);
4835 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4836 ,p_pay_plan =>l_default_pay_plan
4837 ,p_grade_or_level =>l_grade_or_level
4838 ,p_step_or_rate => '10'
4839 ,p_effective_date =>p_pay_calc_data.effective_date
4840 ,p_PT_value => l_new_basic_pay
4841 ,p_PT_eff_start_date => l_PT_eff_start_date
4842 ,p_PT_eff_end_date => l_dummy_date);
4843
4844 hr_utility.set_location('gOt current value for step10 in default table'||l_new_basic_pay,7788);
4845 hr_utility.set_location('l_new_basic_pay: '|| l_new_basic_pay,95);
4846 l_new_locality_adj := l_new_adj_basic_pay - l_new_basic_pay;
4847 hr_utility.set_location('SETTING NEW STEP'||l_new_step,7788);
4848 l_new_step := '10';
4849 hr_utility.set_location('terminate PAY RETENTION',7788);
4850 l_term_ret_pay_prd := TRUE;
4851 hr_utility.set_location('l_new_locality_adj: '|| l_new_locality_adj,95);
4852 END IF;
4853 END IF;--IF l_old_basic_pay IS NOT
4854
4855 ELSIF (l_adj_basic_pay >= nvl(l_special_rate,0)) OR (l_old_special_rate IS NULL) then--IF NVL(l_special_rate,0)
4856
4857 hr_utility.set_location('special rate is lesser or old special rate is not available',7788);
4858 hr_utility.set_location('::: 50101491 :::Inside Else Condition',40);
4859
4860 IF NVL(l_old_special_rate ,0) > l_old_adj_basic_pay THEN
4861 l_increment := Round((l_adj_basic_pay - l_old_special_rate)/2,0);
4862 ELSE
4863 l_increment := Round((l_adj_basic_pay - l_old_adj_basic_pay)/2,0);
4864 END IF;
4865
4866 l_new_basic_pay := p_pay_calc_data.current_basic_pay + l_increment;
4867 l_new_locality_adj := 0;
4868 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4869 l_new_prd := p_pay_calc_data.pay_rate_determinant;
4870 hr_utility.set_location(' adj basic at step 10: '||to_char(l_adj_basic_pay),95);
4871 hr_utility.set_location(' locality at step 10: '||to_char(l_locality_adj),95);
4872 l_calculation_pay_table := l_default_pay_table;
4873
4874 --Bug# 6814842 modified to update position pay table and pay_table_upd_flag
4875 --Bug# 10043212 added to set the position pay table only if the grade does not exist
4876 -- If we call set_cpt_ppt_prd it will override the calculation pay table updation
4877 -- as it need to be updated based on locality pay or special pay which ever is higher
4878 IF NOT(l_pp_grd_exists) THEN
4879 l_position_pay_table := l_default_pay_table;
4880 g_pay_table_upd_flag := TRUE;
4881 ELSE
4882 l_position_pay_table := l_user_table_name;
4883 g_pay_table_upd_flag := FALSE;
4884 END IF;
4885
4886
4887 IF l_new_adj_basic_pay <= l_adj_basic_pay THEN
4888 hr_utility.set_location('new adjusted basic pay is less than adjusted basic pay',7788);
4889 l_new_basic_pay := l_basic_pay;
4890 hr_utility.set_location('new basic pay '||l_new_basic_pay,7788);
4891 l_new_locality_adj := l_locality_adj;
4892 hr_utility.set_location('l_new_locality_adj'||l_locality_adj,7788);
4893 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4894 hr_utility.set_location('l_new_adj_basic_pay'||l_new_adj_basic_pay,7788);
4895 --Bug 7046241 added l_gr_pay_plan condition
4896 IF l_gm_pay_plan OR l_gr_pay_plan THEN
4897 l_new_step := '00';
4898 ELSE
4899 l_new_step := '10';
4900 END IF;
4901 hr_utility.set_location('l_new_step'||l_new_step,7788);
4902 l_term_ret_pay_prd := TRUE;
4903 hr_utility.set_location('terminate PRD',7788);
4904 END IF;
4905 END IF; --IF NVL(l_special_rate,0)
4906
4907 -- Set the PRD if Pay Retention is terminated.
4908 -- 7046241
4909 IF l_term_ret_pay_prd and l_gr_pay_plan and l_calculation_pay_table = ghr_pay_calc.l_standard_table_name then
4910 ghr_history_fetch.fetch_positionei(
4911 p_position_id => p_pay_calc_data.position_id,
4912 p_information_type => 'GHR_US_POS_VALID_GRADE',
4913 p_date_effective => p_pay_calc_data.effective_date,
4914 p_pos_ei_data => l_pos_ei_valid_grade);
4915 IF NOT (ghr_pay_caps.pay_cap_chk_ttl_38( l_pos_ei_valid_grade.poei_information12,
4916 l_pos_ei_valid_grade.poei_information13,
4917 p_pay_calc_data.current_adj_basic_pay,
4918 p_pay_calc_data.effective_date)) THEN
4919 l_term_ret_pay_prd := FALSE;
4920
4921 END IF;
4922 END IF;
4923 -- 7046241
4924 IF l_term_ret_pay_prd THEN
4925 IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','R','Y') THEN -- Bug# 9255822 added PRD Y
4926 IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4927 l_new_prd := '0';
4928 ELSE
4929 l_new_prd := '6';
4930 END IF;
4931 ELSIF p_pay_calc_data.pay_rate_determinant = 'U' THEN
4932 IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4933 l_new_prd := 'B';
4934 ELSE
4935 l_new_prd := 'F';
4936 END IF;
4937 ELSIF p_pay_calc_data.pay_rate_determinant = 'V' THEN
4938 IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4939 l_new_prd := 'A';
4940 ELSE
4941 l_new_prd := 'E';
4942 END IF;
4943 END IF;
4944 END IF;
4945
4946 END IF;--IF p_pay_calc_data.effective_date
4947 -- Get the Calculation Pay Table ID
4948 FOR table_rec IN c_get_table_id(l_calculation_pay_table)
4949 LOOP
4950 l_calculation_pay_table_id := table_rec.user_table_id;
4951 END LOOP;
4952 p_pay_calc_out_data.calculation_pay_table_id := l_calculation_pay_table_id;
4953 hr_utility.set_location(':: 5010491 :: leaving the pay retention',100);
4954 IF l_gm_pay_plan THEN
4955 l_pay_plan := 'GM';
4956 --- Bug 5908487 Changes.
4957 l_default_pay_plan := 'GM';
4958 l_gm_pay_plan := FALSE;
4959 --Begin Bug 7046241
4960 ELSIF l_gr_pay_plan THEN
4961 l_pay_plan := 'GR';
4962 l_default_pay_plan := 'GR';
4963 l_gr_pay_plan := FALSE;
4964 --End Bug 7046241
4965 END IF;
4966
4967 ELSE ---- IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R','Y')
4968 -- 5. Processing Employees on GM Pay Plan.
4969 --Bug# 7518210 added GR
4970 IF l_pay_plan IN ('GM','GH','GR') THEN
4971 -- AC added the IF condition for opening teh pay fields in few actions Bug#5114467
4972 IF p_pay_calc_data.noa_family_code IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
4973 ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY') THEN
4974 hr_utility.set_message(8301, 'GHR_38260_NO_CALC_PAY_PLAN');
4975 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
4976 raise ghr_pay_calc.unable_to_calculate;
4977 END IF;
4978 -- Added this IF condition Bug#5114467
4979 IF l_pay_plan NOT IN ('GM','GR') THEN
4980 --Bug# 7518210 added GR
4981 l_new_Position_Pay_Table := ghr_pay_calc.l_standard_table_name;
4982 l_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4983 g_pay_table_upd_flag := TRUE;
4984 l_new_prd := '0';
4985 l_new_basic_pay := ROUND(p_pay_calc_data.current_adj_basic_pay / (1 + l_adj_perc_factor),0);
4986 l_new_locality_adj := ROUND(l_new_basic_pay * l_adj_perc_factor);
4987 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4988 ELSE
4989 IF p_pay_calc_data.noa_code= 894 THEN
4990 --AC -- Code for 894 Special Emp.
4991 hr_utility.set_location('AC Grade' || l_grade_or_level,1000);
4992 hr_utility.set_location('AC User Table id' || l_user_table_id,1000);
4993 ghr_pc_basic_pay.get_894_GM_sp_basic_pay (p_grade_or_level => l_grade_or_level
4994 ,p_effective_date => p_pay_calc_data.effective_date
4995 ,p_user_table_id => l_user_table_id
4996 ,p_default_table_id => l_default_table_id
4997 ,p_curr_basic_pay => p_pay_calc_data.current_basic_pay
4998 ,p_duty_station_id => p_pay_calc_data.duty_station_id
4999 ,p_new_basic_pay => l_new_basic_pay
5000 ,p_new_adj_basic_pay => l_new_adj_basic_pay
5001 ,p_new_locality_adj => l_new_locality_adj
5002 ,p_new_special_rate => l_new_special_rate
5003 );
5004 ELSE
5005 --AC -- Code for WGI Special Emp.
5006 ghr_pc_basic_pay.get_wgi_GM_sp_basic_pay (p_grade_or_level => l_grade_or_level
5007 ,p_effective_date => p_pay_calc_data.effective_date
5008 ,p_user_table_id => l_user_table_id
5009 ,p_default_table_id => l_default_table_id
5010 ,p_curr_basic_pay => p_pay_calc_data.current_basic_pay
5011 ,p_duty_station_id => p_pay_calc_data.duty_station_id
5012 ,p_new_basic_pay => l_new_basic_pay
5013 ,p_new_adj_basic_pay => l_new_adj_basic_pay
5014 ,p_new_locality_adj => l_new_locality_adj
5015 );
5016 END IF;
5017 END IF;
5018
5019 /*ELSIF g_fw_equiv_pay_plan AND p_pay_calc_data.pay_rate_determinant IN ('6','E','F') THEN -- Bug 5218445
5020 get_special_pay_table_value (p_pay_plan => l_pay_plan
5021 ,p_grade_or_level => l_grade_or_level
5022 ,p_step_or_rate => l_step_or_rate
5023 ,p_user_table_id => l_user_table_id
5024 ,p_effective_date => p_pay_calc_data.effective_date
5025 ,p_pt_value => l_pt_value
5026 ,p_PT_eff_start_date => l_pt_eff_start_date
5027 ,p_PT_eff_end_date => l_pt_eff_end_date
5028 ,p_pp_grd_exists => l_pp_grd_exists );
5029 l_new_basic_pay := l_pt_value;
5030 l_new_locality_adj := 0;
5031 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
5032 l_calculation_pay_table := get_user_table_name(l_user_table_id);
5033 */
5034 ELSE----IF l_pay_plan IN ('GM'
5035 -- 6. Processing Employees on pay plans other than GM.
5036
5037
5038 -- Processing for InterimWGI/WGI/QSI.
5039 -- Bug#5566896Added NOA Code 867.
5040 IF p_pay_calc_data.noa_code IN ('867','892','893') THEN
5041 l_ret_flag := FALSE;
5042 l_curr_step_or_rate := l_step_or_rate;
5043 -- FWFA Changes NEW
5044 IF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
5045 if ghr_pc_basic_pay.g_noa_family_code ='CORRECT' then
5046 if p_retained_grade.temp_step is not null then
5047 -- Bug 3021003 Start
5048 is_retained_ia(p_pay_calc_data.person_id,
5049 p_pay_calc_data.effective_date,
5050 l_pay_plan,
5051 l_grade_or_level,
5052 l_step_or_rate,
5053 p_retained_grade.temp_step,
5054 l_ret_flag);
5055 IF l_ret_flag = TRUE then
5056 hr_utility.set_location('NAR ret step ' ||p_retained_grade.temp_step,10);
5057 hr_utility.set_location('NAR pay plan '||l_pay_plan,20);
5058 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
5059 ,p_retained_grade.temp_step);
5060 hr_utility.set_location('NAR new step after getting the step ' ||l_step_or_rate,10);
5061 -- End Bug 3021003
5062 ELSE
5063 l_step_or_rate := p_retained_grade.temp_step;
5064 END IF;
5065 else
5066 -- Bug 3021003
5067 is_retained_ia(p_pay_calc_data.person_id,
5068 p_pay_calc_data.effective_date,
5069 l_pay_plan,
5070 l_grade_or_level,
5071 l_step_or_rate,
5072 p_retained_grade.temp_step,
5073 l_ret_flag);
5074 IF l_ret_flag = TRUE then
5075 hr_utility.set_location('NAR ret step ' ||l_step_or_rate,10);
5076 hr_utility.set_location('NAR pay plan '||l_pay_plan,20);
5077 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
5078 ,l_step_or_rate);
5079 hr_utility.set_location('NAR new step after getting the step ' ||l_step_or_rate,10);
5080 ELSE
5081 l_step_or_rate := l_step_or_rate;
5082 END IF; -- IF is_retained_ia End Bug 3021003
5083 END IF; -- if p_retained_grade.temp_step is not null
5084 else--family_code ='CORRECT'
5085 if p_retained_grade.temp_step is not null then
5086
5087 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
5088 ,p_retained_grade.temp_step);
5089 else
5090 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
5091 ,p_retained_grade.step_or_rate);
5092 end if;
5093 end if;--family_code ='CORRECT'
5094 ELSE --('A','B','E','F') THEN
5095 --if ghr_pc_basic_pay.g_noa_family_code ='CORRECT' then
5096 -- l_step_or_rate := l_curr_step_or_rate;
5097 --ELSE
5098 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_plan => l_pay_plan
5099 ,p_current_step => l_curr_step_or_rate);
5100 --END IF;
5101 END IF; --('A','B','E','F') THEN
5102 -- FWFA Changes NEW
5103 hr_utility.set_location('l_step_or_rate: '||l_step_or_rate,11111111);
5104 -- Bug#5444558 Modified the assignment of step value. Assigned the step into local
5105 -- variable l_new_step here. Assign this value to p_pay_calc_out_data.out_step_or_rate
5106 -- at the end of the pay calc.
5107 IF p_pay_calc_data.pay_rate_determinant IN ('E','F','A','B') THEN
5108 l_new_step := '00';
5109 ELSE
5110 l_new_step := l_step_or_rate;
5111 END IF;
5112 END IF;-- ('867','892','893') THEN
5113
5114
5115 IF g_fw_equiv_pay_plan THEN -- Begin Bug 5608741
5116 get_special_pay_table_value (p_pay_plan => l_pay_plan
5117 ,p_grade_or_level => l_grade_or_level
5118 ,p_step_or_rate => l_step_or_rate
5119 ,p_user_table_id => l_user_table_id
5120 ,p_effective_date => p_pay_calc_data.effective_date
5121 ,p_pt_value => l_pt_value
5122 ,p_PT_eff_start_date => l_pt_eff_start_date
5123 ,p_PT_eff_end_date => l_pt_eff_end_date
5124 ,p_pp_grd_exists => l_pp_grd_exists );
5125 l_new_basic_pay := l_pt_value;
5126 l_new_locality_adj := 0;
5127 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
5128 l_calculation_pay_table := get_user_table_name(l_user_table_id);
5129 ELSE --End Bug# 5608741
5130 -- Processing of NOACs other than WGI/QSI
5131 IF l_user_table_name IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) THEN
5132 l_pay_calc_process := 'L';
5133 ELSE
5134 hr_utility.set_location('user tbale id: '||to_char(l_user_table_id),40);
5135 hr_utility.set_location('pay Plan: '||l_pay_plan,50);
5136 hr_utility.set_location('grade: '||l_grade_or_level,60);
5137 hr_utility.set_location('step : '||l_step_or_rate,70);
5138 get_special_pay_table_value (p_pay_plan => l_pay_plan
5139 ,p_grade_or_level => l_grade_or_level
5140 ,p_step_or_rate => l_step_or_rate
5141 ,p_user_table_id => l_user_table_id
5142 ,p_effective_date => p_pay_calc_data.effective_date
5143 ,p_pt_value => l_pt_value
5144 ,p_PT_eff_start_date => l_pt_eff_start_date
5145 ,p_PT_eff_end_date => l_pt_eff_end_date
5146 ,p_pp_grd_exists => l_pp_grd_exists );
5147 hr_utility.set_location('value '||to_char(l_pt_value),80);
5148 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
5149 ,p_pay_plan =>l_default_pay_plan
5150 ,p_grade_or_level =>l_grade_or_level
5151 ,p_step_or_rate =>l_step_or_rate
5152 ,p_effective_date =>p_pay_calc_data.effective_date
5153 ,p_PT_value => l_basic_pay
5154 ,p_PT_eff_start_date => l_PT_eff_start_date
5155 ,p_PT_eff_end_date => l_dummy_date);
5156 hr_utility.set_location('std basic: '||to_char(l_BASIC_PAY),90);
5157
5158 l_locality_adj := ROUND(l_basic_pay * (NVL(l_adjustment_percentage,0)/100),0);
5159 l_adj_basic_pay := l_basic_pay + l_locality_adj;
5160
5161 -- IF Pay Plan Grade Exists and Step not exists
5162 IF l_pp_grd_exists THEN
5163 IF l_pt_value is NULL THEN
5164 l_pay_calc_process := 'L';
5165 l_loc_rate_less_spl_rate := NULL;
5166 ELSE
5167 IF l_pt_value > l_adj_basic_pay THEN
5168 l_pay_calc_process := 'S';
5169 l_calculation_pay_table := l_user_table_name;
5170 l_loc_rate_less_spl_rate := TRUE;
5171 ELSE
5172 l_pay_calc_process := 'L';
5173 l_loc_rate_less_spl_rate := FALSE;
5174 END IF;
5175 END IF;
5176 ELSE
5177 l_pay_calc_process := 'L';
5178 l_loc_rate_less_spl_rate := NULL;
5179 END IF;
5180 END IF; --l_user_table_name IN
5181 hr_utility.set_location('process :'||l_pay_calc_process,100);
5182 IF g_pay_table_upd_flag THEN
5183 hr_utility.set_location('Before SET_CPT Pay Table UPD Flag TRUE',101);
5184 ELSE
5185 hr_utility.set_location('Before SET_CPT Pay Table UPD Flag FALSE',102);
5186 END IF;
5187 set_cpt_ppt_prd ( p_leo_flag => l_leo_flag
5188 ,p_rg_flag => l_rg_flag
5189 ,p_pp_grd_exists => l_pp_grd_exists
5190 ,p_loc_rate_less_spl_rate => l_loc_rate_less_spl_rate
5191 ,p_prd => p_pay_calc_data.pay_rate_determinant
5192 ,p_pt_value => l_pt_value
5193 ,p_special_rate_table => l_user_table_name
5194 ,p_calculation_pay_table => l_calculation_pay_table
5195 ,p_position_pay_table => l_position_pay_table
5196 ,p_retained_pay_table => l_retained_pay_table
5197 ,p_new_prd => l_new_prd
5198 );
5199 IF g_pay_table_upd_flag THEN
5200 hr_utility.set_location('After SET_CPT Pay Table UPD Flag TRUE',101);
5201 ELSE
5202 hr_utility.set_location('After SET_CPT Pay Table UPD Flag FALSE',102);
5203 END IF;
5204 hr_utility.set_location('calc table: '||l_calculation_pay_table,110);
5205 hr_utility.set_location('posn table: '||L_position_pay_table,120);
5206 hr_utility.set_location('retd table: '||l_retained_pay_table,130);
5207 hr_utility.set_location('new prd : '||l_new_prd,140);
5208
5209
5210 -- get_basic_pay
5211 hr_utility.set_location('2. l_step_or_rate: '||l_step_or_rate,999999);
5212 ghr_pay_calc.get_pay_table_value(p_user_table_id => l_default_table_id
5213 ,p_pay_plan => l_default_pay_plan
5214 ,p_grade_or_level => l_grade_or_level
5215 ,p_step_or_rate => l_step_or_rate
5216 ,p_effective_date => p_pay_calc_data.effective_date
5217 ,p_PT_value => l_basic_pay
5218 ,p_PT_eff_start_date => l_PT_eff_start_date
5219 ,p_PT_eff_end_date => l_dummy_date);
5220 l_new_basic_pay := l_basic_pay;
5221 hr_utility.set_location('std basic: '||to_char(l_new_BASIC_PAY),10);
5222 hr_utility.set_location('ADJ Perc : '||to_char(l_adj_perc_factor),20);
5223 -- get_locality_or_supplement/ Adj Basic Pay
5224 IF l_pay_calc_process = 'L' THEN
5225 hr_utility.set_location('Inside Locality Calculation',30);
5226 l_new_locality_adj := ROUND(l_new_basic_pay * l_adj_perc_factor);
5227 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
5228 ELSIF l_pay_calc_process = 'S' THEN
5229 hr_utility.set_location('Inside Special Rate Calculation',40);
5230 l_new_locality_adj := l_pt_value - l_new_basic_pay;
5231 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
5232 END IF;
5233 END IF;--End of if Bug 5608741
5234 END IF;----IF l_pay_plan IN ('GM'
5235 END IF; ---- IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R','Y')
5236 FOR table_rec IN c_get_table_id(l_calculation_pay_table)
5237 LOOP
5238 l_calculation_pay_table_id := table_rec.user_table_id;
5239 END LOOP;
5240
5241 hr_utility.set_location('new Basic : '||to_char(l_new_basic_pay),10);
5242 hr_utility.set_location('new LOC : '||to_char(l_new_locality_adj),20);
5243 hr_utility.set_location('new ADJ BA : '||to_char(l_new_adj_basic_pay),30);
5244 hr_utility.set_location('new PRD : '||l_new_prd,40);
5245 hr_utility.set_location('new Step : '||l_new_step,50);
5246 --Bug#5089732 Set the Pay Rate Determinant for GL,GG Pay Plans
5247 IF l_calculation_pay_table = ghr_pay_calc.l_spl491_table_name AND
5248 l_pay_plan IN ('GL','GG') AND
5249 l_new_prd IN ('6','E','F') THEN
5250 IF l_new_prd = '6' THEN
5251 l_new_prd := '0';
5252 ELSIF l_new_prd = 'E' THEN
5253 l_new_prd := 'A';
5254 ELSE
5255 l_new_prd := 'B';
5256 END IF;
5257 --Bug#5435217 Set this flag to true to avoid unnecessary creation of RG record.
5258 g_gl_upd_flag := TRUE;
5259 END IF;
5260
5261 -- AC : Bug#5114467 Added codition for For Regular Emp. in GM pay table and 894 action
5262 -- And Code for determining Calculation pay table For Regular Emp. in GM pay table and 894 action
5263 IF l_pay_plan = 'GM' AND p_pay_calc_data.noa_code= 894 THEN
5264 -- AC : Bug#5725928 - Added the condition so that the pay adjustment process should be
5265 -- doing the comparison of locality verses special rate only if the pay table is not 0000
5266 IF l_calculation_pay_table <> ghr_pay_calc.l_standard_table_name THEN
5267 IF l_new_locality_adj > l_new_special_rate THEN
5268 l_new_prd := '0';
5269 ELSE
5270 l_new_prd := '6';
5271 END IF;
5272 END IF;
5273 IF l_new_prd IN ('0','A','B') THEN
5274 l_calculation_pay_table_id := l_default_table_id;
5275 ELSIF l_new_prd IN ('6','E','F') THEN
5276 l_calculation_pay_table_id := l_user_table_id;
5277 END IF;
5278 END IF;
5279
5280 -- Bug#4699932 Added the OR clause to IF condition.
5281 -- Set the out_pay_rate_determinant as NULL if there is no change in PRD.
5282 IF p_pay_calc_data.pay_rate_determinant = l_new_prd OR
5283 (p_pay_calc_data.noa_family_code IN ('APP','CONV_APP') AND
5284 p_pay_calc_data.pay_rate_determinant IN ('5','7')
5285 ) THEN
5286 l_new_prd := NULL;
5287 END IF;
5288
5289 -- SET OUT PARAMETERS
5290 hr_utility.set_location('new PRD : '||l_new_prd,40);
5291 hr_utility.set_location('new Step : '||l_new_step,50);
5292 hr_utility.set_location('new l_calculation_pay_table_id : '|| l_calculation_pay_table_id,50);
5293 p_pay_calc_out_data.basic_pay := l_new_basic_pay;
5294 p_pay_calc_out_data.locality_adj := l_new_locality_adj;
5295 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
5296 p_pay_calc_out_data.out_pay_rate_determinant := l_new_prd;
5297 p_pay_calc_out_data.calculation_pay_table_id := l_calculation_pay_table_id;
5298 p_pay_calc_out_data.out_step_or_rate := l_new_step;
5299 -- Use the Pay Table ID as the input table for Pay Calculation.
5300 p_pay_calc_out_data.pay_table_id := l_user_table_id;
5301
5302 ------ Bug#5741977 start
5303 IF l_fwfa_pay_plan_changed THEN
5304 IF nvl(l_new_prd,'X') in ('A','B','E','F','U','V') AND
5305 p_retained_grade.temp_step IS NULL THEN
5306 g_out_to_pay_plan := l_default_pay_plan;
5307 ELSIF l_grade_id is not null then
5308 p_pay_calc_out_data.out_to_grade_id := l_grade_id;
5309 p_pay_calc_out_data.out_to_pay_plan := l_default_pay_plan;
5310 p_pay_calc_out_data.out_to_grade_or_level := l_grade_or_level;
5311 END IF;
5312 END IF;
5313 ------ Bug#5741977 End
5314 EXCEPTION
5315 WHEN OTHERS THEN
5316 RAISE;
5317 END special_rate_pay_calc;
5318
5319 -- Bug# 4748927 Begin
5320 PROCEDURE award_amount_calc (
5321 p_position_id IN NUMBER
5322 ,p_pay_plan IN VARCHAR2
5323 ,p_award_percentage IN NUMBER
5324 ,p_user_table_id IN NUMBER
5325 ,p_grade_or_level IN VARCHAR2
5326 ,p_effective_date IN DATE
5327 ,p_basic_pay IN NUMBER
5328 ,p_adj_basic_pay IN NUMBER
5329 ,p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
5330 ,p_prd IN ghr_pa_requests.pay_rate_determinant%type
5331 ,p_pay_basis IN VARCHAR2
5332 ,p_person_id IN per_people_f.person_id%TYPE
5333 ,p_award_amount OUT NOCOPY NUMBER
5334 ,p_award_salary OUT NOCOPY NUMBER
5335 ) IS
5336 l_pay_table VARCHAR2(4);
5337 l_PT_value NUMBER;
5338 l_PT_eff_start_date DATE;
5339 l_PT_eff_end_date DATE;
5340 l_equivalent_pay_plan ghr_pay_plans.equivalent_pay_plan%type;
5341 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%type;
5342 l_max_step ghr_pay_plans.maximum_step%type;
5343 l_locality_adj NUMBER;
5344 l_leo_flag BOOLEAN;
5345 l_std_user_table_id NUMBER;
5346 l_std_user_table_name varchar2(80);
5347 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
5348 l_pay_plan VARCHAR2(10);
5349 l_pay_basis VARCHAR2(10);
5350 l_grade_or_level VARCHAR2(60);
5351 l_user_table_id NUMBER;
5352 l_proc VARCHAR2(30) := 'award_amount_calc';
5353 l_special_rate NUMBER;
5354 l_pp_grd_exists BOOLEAN;
5355 --bug #5482191
5356 l_psi VARCHAR2(30);
5357 cursor c_gs_eq(pc_pay_plan VARCHAR2) is
5358 SELECT equivalent_pay_plan,maximum_step
5359 FROM ghr_pay_plans
5360 WHERE pay_plan = pc_pay_plan;
5361
5362 CURSOR get_user_table_id(p_user_table_name VARCHAR2) IS
5363 SELECT utb.user_table_id
5364 FROM pay_user_tables utb
5365 WHERE utb.user_table_name = p_user_table_name;
5366
5367 BEGIN
5368 hr_utility.set_location('Entering ...'|| l_proc,100);
5369 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_position_id,p_effective_date);
5370
5371 hr_utility.set_location('personnel system ...'|| l_psi,101);
5372
5373 IF l_psi <> '00' THEN --for NSPS
5374
5375 IF p_pay_basis = 'PA' THEN
5376 p_award_salary := ROUND(p_adj_basic_pay);
5377 ELSIF p_pay_basis = 'PH' THEN
5378 p_award_salary := ROUND(p_adj_basic_pay * 2087);
5379 ELSIF p_pay_basis = 'PM' THEN
5380 p_award_salary := ROUND(p_adj_basic_pay * 12);
5381 END IF;
5382
5383 ELSE --for non-NSPS
5384
5385 IF NVL(p_prd,'X') NOT IN ('A','B','E','F','U','V') THEN
5386 l_pay_plan := p_pay_plan;
5387 l_grade_or_level := p_grade_or_level;
5388 l_user_table_id := p_user_table_id;
5389 -- Bug#5237399 Added Pay basis
5390 l_pay_basis := p_pay_basis;
5391 ELSE --IF NVL(p_prd,'X')
5392 BEGIN
5393 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (p_person_id,p_effective_date,NULL);
5394 --Mani Bug #6655566 Award Amount calculated on the Position pay basis if temp step is not null
5395 --otherwise on retained grade
5396 If NVL(p_prd,'X') IN ('A','B','E','F') AND
5397 l_retained_grade_rec.temp_step is not null then
5398 l_pay_basis := p_pay_basis;
5399 l_pay_plan := p_pay_plan;
5400 l_grade_or_level := p_grade_or_level;
5401 l_user_table_id := p_user_table_id;
5402 else
5403 l_pay_plan := l_retained_grade_rec.pay_plan;
5404 l_grade_or_level := l_retained_grade_rec.grade_or_level;
5405 l_user_table_id := l_retained_grade_rec.user_table_id;
5406 -- Bug#5237399 Added Pay basis
5407 l_pay_basis := l_retained_grade_rec.pay_basis;
5408
5409 END IF;
5410
5411 exception
5412 when others then
5413 hr_utility.set_location('Retained Exception raised ' || sqlerrm(sqlcode),25);
5414 END;
5415 END IF;--IF NVL(p_prd,'X')
5416 for c1 in c_gs_eq(l_pay_plan) loop
5417 l_equivalent_pay_plan := c1.equivalent_pay_plan;
5418 l_max_step := c1.maximum_step;
5419 end loop;
5420
5421 IF p_prd not in('3','4','J','K','U','V','R') THEN
5422 IF (l_equivalent_pay_plan = 'FW' OR
5423 (l_pay_plan in('AD','ED') and l_pay_basis = 'PH') ) THEN --Bug# 10154618 added pay plan ED
5424 p_award_salary := ROUND(p_adj_basic_pay * 2087);
5425 ELSE
5426 p_award_salary := ROUND(p_adj_basic_pay);
5427 END IF;
5428 ELSE ---IF p_prd not in('3','4'
5429 l_pay_table := SUBSTR(ghr_pay_calc.get_user_table_name(l_user_table_id),1,4);
5430
5431 IF (l_equivalent_pay_plan = 'GS') THEN
5432
5433 --bug #5529825
5434 if l_pay_plan = 'GM' then
5435 l_pay_plan := 'GS';
5436 l_max_step := '10';
5437 end if;
5438 --end bug#5529825
5439
5440 --code changes for bug 4999237
5441 ---CODE FOR GETTING SPECIAL TABLE RATE VALUES..........
5442 IF l_pay_table NOT IN('0491','0000') THEN
5443 get_special_pay_table_value (p_pay_plan => l_pay_plan
5444 ,p_grade_or_level => l_grade_or_level
5445 ,p_step_or_rate => l_max_step
5446 ,p_user_table_id => l_user_table_id
5447 ,p_effective_date => p_effective_date
5448 ,p_pt_value => l_pt_value
5449 ,p_PT_eff_start_date => l_pt_eff_start_date
5450 ,p_PT_eff_end_date => l_pt_eff_end_date
5451 ,p_pp_grd_exists => l_pp_grd_exists );
5452
5453 l_special_rate := l_pt_value;
5454 hr_utility.set_location('special rate '||l_special_rate,8877);
5455
5456 --code for getting default table values......
5457
5458
5459
5460 IF ghr_pay_calc.LEO_position (p_prd
5461 ,p_position_id
5462 ,l_std_user_table_id
5463 ,p_duty_station_id
5464 ,p_effective_date)
5465 AND p_grade_or_level between 03 and 10 THEN
5466 l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5467
5468 ELSE
5469 l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5470
5471 END IF;
5472 FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5473 l_std_user_table_id := c_rec.user_table_id;
5474 END LOOP;
5475
5476 ghr_pay_calc.get_pay_table_value (p_user_table_id => l_std_user_table_id
5477 ,p_pay_plan => l_pay_plan
5478 ,p_grade_or_level => l_grade_or_level
5479 ,p_step_or_rate => l_max_step
5480 ,p_effective_date => p_effective_date
5481 ,p_PT_value => l_PT_value
5482 ,p_PT_eff_start_date => l_PT_eff_start_date
5483 ,p_PT_eff_end_date => l_PT_eff_end_date
5484 );
5485 hr_utility.set_location('default rate '||l_PT_value,8877);
5486 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id,p_effective_date),0);
5487 hr_utility.set_location('l_adjustment_percentage '||l_adjustment_percentage,8877);
5488 l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5489
5490 l_pt_value := l_pt_value + l_locality_adj;
5491 hr_utility.set_location('final default rate '||l_pt_value,8877);
5492 --compare srt and locality rate..........
5493 if nvl(l_special_rate,0) < l_pt_value then
5494 p_award_salary := ROUND(l_pt_value);
5495 else
5496 p_award_salary := ROUND(l_special_rate);
5497 end if;
5498 ELSIF l_pay_table IN('0491','0000') THEN
5499
5500 BEGIN
5501 ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_user_table_id
5502 ,p_pay_plan => l_pay_plan
5503 ,p_grade_or_level => l_grade_or_level
5504 ,p_step_or_rate => l_max_step
5505 ,p_effective_date => p_effective_date
5506 ,p_PT_value => l_PT_value
5507 ,p_PT_eff_start_date => l_PT_eff_start_date
5508 ,p_PT_eff_end_date => l_PT_eff_end_date);
5509
5510 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id
5511 ,p_effective_date),0);
5512
5513 l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5514
5515 p_award_salary := ROUND(l_PT_value+l_locality_adj);
5516
5517 EXCEPTION
5518 WHEN ghr_pay_calc.pay_calc_message THEN
5519 BEGIN
5520 IF ghr_pay_calc.LEO_position (p_prd
5521 ,p_position_id
5522 ,l_std_user_table_id
5523 ,p_duty_station_id
5524 ,p_effective_date)
5525 AND p_grade_or_level between 03 and 10 THEN
5526 l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5527
5528 ELSE
5529 l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5530
5531 END IF;
5532 FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5533 l_std_user_table_id := c_rec.user_table_id;
5534 END LOOP;
5535 ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_std_user_table_id
5536 ,p_pay_plan => l_pay_plan
5537 ,p_grade_or_level => l_grade_or_level
5538 ,p_step_or_rate => l_max_step
5539 ,p_effective_date => p_effective_date
5540 ,p_PT_value => l_PT_value
5541 ,p_PT_eff_start_date => l_PT_eff_start_date
5542 ,p_PT_eff_end_date => l_PT_eff_end_date);
5543
5544 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id
5545 ,p_effective_date),0);
5546 l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5547 p_award_salary := ROUND(l_PT_value+l_locality_adj);
5548 END;
5549 END;
5550 END IF; --l_pay_table IN('0491','0000')
5551 --end code changes for bug 4999237
5552
5553 -- Bug#5237399 Added the AD Pay Plan Condition.
5554 ELSIF (l_equivalent_pay_plan = 'FW' OR
5555 (l_pay_plan IN ('AD','ED') and l_pay_basis = 'PH')) THEN --Bug# 10154618 added pay plan ED
5556 hr_utility.set_location('USER TABLE '||to_char(l_user_table_id),10);
5557 hr_utility.set_location('EFF DT '||to_char(p_effective_date),20);
5558 hr_utility.set_location('MAX STEP '||l_max_step,30);
5559 hr_utility.set_location('PAY PLAN '||l_pay_plan,40);
5560 hr_utility.set_location('GRADE '||l_grade_or_level,50);
5561 ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_user_table_id
5562 ,p_pay_plan => l_pay_plan
5563 ,p_grade_or_level => l_grade_or_level
5564 ,p_step_or_rate => l_max_step
5565 ,p_effective_date => p_effective_date
5566 ,p_PT_value => l_PT_value
5567 ,p_PT_eff_start_date => l_PT_eff_start_date
5568 ,p_PT_eff_end_date => l_PT_eff_end_date);
5569 hr_utility.set_location('VALUE '||to_char(l_pt_value),60);
5570 hr_utility.set_location('Pay Basis '||p_pay_basis,70);
5571 p_award_salary := ROUND(ghr_pay_calc.convert_amount(l_PT_value,p_pay_basis,'PA'));
5572
5573 END IF; --(p_pay_plan = l_equivalent_pay_plan)
5574 END IF; -----IF p_prd not in('3','4','J','K','U','V'
5575 --Begin Bug# 5039156
5576 END IF;--for non-NSPS
5577
5578 -- Call the user hook
5579 ghr_custom_award.custom_award_salary(p_position_id
5580 ,p_person_id
5581 ,p_prd
5582 ,p_pay_basis
5583 ,p_pay_plan
5584 ,p_user_table_id
5585 ,p_grade_or_level
5586 ,p_effective_date
5587 ,p_basic_pay
5588 ,p_adj_basic_pay
5589 ,p_duty_station_id
5590 ,p_award_salary
5591 );
5592
5593 p_award_amount := FLOOR(p_award_salary *( nvl(p_award_percentage,0) / 100));
5594 --End Bug# 5039156
5595 hr_utility.set_location('Leaving Award Amount sal' || p_award_salary,10);
5596 EXCEPTION
5597 WHEN OTHERS THEN
5598 p_award_amount := NULL;
5599 p_award_salary := NULL;
5600 hr_utility.set_location('Leaving ' || l_proc,10);
5601 END award_amount_calc;
5602 -- Bug# 4748927 End
5603 END ghr_pay_calc;