[Home] [Help]
PACKAGE BODY: APPS.GHR_PAY_CALC
Source
1 PACKAGE BODY ghr_pay_calc AS
2 /* $Header: ghpaycal.pkb 120.72.12010000.10 2008/12/03 11:57:00 utokachi 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
1051
1052 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1053 -- Bug 3021003
1054 l_retained_grade.pay_plan := p_retained_grade.pay_plan;
1055 l_retained_grade.grade_or_level := p_retained_grade.grade_or_level;
1056 l_retained_grade.step_or_rate := p_retained_grade.step_or_rate;
1057 l_retained_grade.temp_step := p_retained_grade.temp_step;
1058 -- to cater for PRD 'M' which may or may not have a retained grade record.
1059 -- if there is a retained grade record use that, otherwise use the position's
1060 ghr_history_api.get_g_session_var(l_session);
1061
1062 IF p_retained_grade.pay_plan IS NOT NULL THEN
1063 l_pay_plan := p_retained_grade.pay_plan;
1064 l_user_table_id := p_retained_grade.user_table_id;
1065 if p_retained_grade.temp_step is not null then
1066 l_pay_plan := p_pay_calc_data.pay_plan;
1067 l_user_table_id := p_pay_calc_data.user_table_id;
1068 end if;
1069 ELSE
1070 l_pay_plan := p_pay_calc_data.pay_plan;
1071 l_user_table_id := p_pay_calc_data.user_table_id;
1072 END IF;
1073
1074 for c_pay_tab_rec in c_pay_tab loop
1075 l_itpay_table := TRUE;
1076 exit;
1077 end loop;
1078
1079 for c_pay_tab_essl_rec in c_pay_tab_essl loop
1080 l_essl_table := TRUE;
1081 exit;
1082 end loop;
1083
1084 --AVR
1085 IF (l_pay_plan in ('GS','GL','GM') AND p_pay_calc_data.noa_code = '894'
1086 AND p_pay_calc_data.pay_rate_determinant = 'M') THEN
1087 IF p_retained_grade.grade_or_level IS NOT NULL THEN
1088 l_grade_or_level := p_retained_grade.grade_or_level;
1089 l_step_or_rate := p_retained_grade.step_or_rate;
1090 if p_retained_grade.temp_step is not null then
1091 l_grade_or_level := p_pay_calc_data.grade_or_level;
1092 l_step_or_rate := p_retained_grade.temp_step;
1093 end if;
1094 ELSE
1095 l_grade_or_level := p_pay_calc_data.grade_or_level;
1096 l_step_or_rate := p_pay_calc_data.step_or_rate;
1097 END IF;
1098 END IF;
1099 --AVR
1100
1101 -- Pradeep added EE to the if list for the Bug 3604377.
1102 --Bug# 6342011 added GR
1103 --Bug# 7557159 added IG
1104 IF l_pay_plan IN ('AD','AL','ES','EP','GG','GH','GM','GS','GL','IP','IE',
1105 'FB','FG','FJ','FM','FX','CA','AA','SL','ST','EE','GR','IG') THEN
1106 --
1107 --AVR
1108 IF (l_pay_plan IN ( 'GS','GL','GM') AND p_pay_calc_data.noa_code = '894'
1109 AND p_pay_calc_data.pay_rate_determinant = 'M') THEN
1110 IF l_pay_plan = 'GS' THEN
1111
1112 hr_utility.set_location('In Entering ...'|| l_proc,5);
1113 hr_utility.set_location('In User_table_id ...'|| to_char(p_pay_calc_data.user_table_id),5);
1114 hr_utility.set_location('In p_pay_plan ...'|| l_pay_plan,5);
1115 hr_utility.set_location('In p_grade_or_level ...'|| l_grade_or_level,5);
1116 hr_utility.set_location('In step_or_rate ...'|| l_step_or_rate,5);
1117 hr_utility.set_location('In effective_date ...'|| to_char(p_pay_calc_data.effective_date,'DD-MON-YYYY'),5);
1118 hr_utility.set_location('In new current adj basic pay ...'|| to_char(p_pay_calc_data.current_adj_basic_pay),5);
1119 hr_utility.set_location('In new basic pay ...'|| to_char(p_pay_calc_out_data.basic_pay),5);
1120 get_locality_adj_894_PRDM_GS
1121 (p_user_table_id => p_pay_calc_data.user_table_id
1122 ,p_pay_plan => l_pay_plan
1123 ,p_grade_or_level => l_grade_or_level
1124 ,p_step_or_rate => l_step_or_rate
1125 ,p_effective_date => p_pay_calc_data.effective_date
1126 ,p_cur_adj_basic_pay => p_pay_calc_data.current_adj_basic_pay
1127 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1128 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1129 ,p_new_locality_adj => l_locality_adj );
1130
1131 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
1132 p_pay_calc_out_data.locality_adj := l_locality_adj;
1133
1134
1135 ELSIF l_pay_plan = 'GM' THEN
1136
1137 get_locality_adj_894_PRDM_GM
1138 (p_pay_calc_data => p_pay_calc_data
1139 ,p_retained_grade => p_retained_grade
1140 ,p_new_std_relative_rate => l_new_std_relative_rate
1141 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1142 ,p_new_locality_adj => l_locality_adj);
1143
1144 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
1145 p_pay_calc_out_data.locality_adj := l_locality_adj;
1146
1147 END IF;
1148
1149 ELSIF l_pay_plan IN ('GM','GH') AND l_itpay_table THEN
1150 IF p_pay_calc_data.noa_code = '894' AND p_pay_calc_data.pay_rate_determinant = '6' THEN
1151 get_locality_894_itpay
1152 (p_pay_calc_data => p_pay_calc_data
1153 ,p_retained_grade => p_retained_grade
1154 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1155 ,p_GM_unadjusted_rate => l_GM_unadjusted_rate
1156 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1157 ,p_new_locality_adj => l_locality_adj );
1158
1159 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
1160 p_pay_calc_out_data.locality_adj := l_locality_adj;
1161
1162 ELSIF p_pay_calc_data.noa_code in ('892','891') AND p_pay_calc_data.pay_rate_determinant = '6' THEN
1163 get_locality_892_itpay
1164 (p_pay_calc_data => p_pay_calc_data
1165 ,p_retained_grade => p_retained_grade
1166 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1167 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1168 ,p_new_locality_adj => l_locality_adj );
1169
1170 --Begin Bug 5661441 AFHR change
1171 ELSIF p_pay_calc_data.noa_code in ('893') AND p_pay_calc_data.pay_rate_determinant = '6' THEN
1172 IF p_pay_calc_data.effective_date >= to_date('2007/01/07','YYYY/MM/DD')THEN
1173 get_locality_892_itpay
1174 (p_pay_calc_data => p_pay_calc_data
1175 ,p_retained_grade => p_retained_grade
1176 ,p_new_basic_pay => p_pay_calc_out_data.basic_pay
1177 ,p_new_adj_basic_pay => l_new_adj_basic_pay
1178 ,p_new_locality_adj => l_locality_adj );
1179 END IF;
1180 --End Bug 5661441 AFHR change
1181
1182 END IF;
1183
1184 ELSIF l_pay_plan IN ('ES','EP','FE','IE') AND l_essl_table THEN
1185
1186 l_locality_adj := 0;
1187 --Bug# 7557159 added IG
1188 ELSIF l_pay_plan IN ('IG') THEN
1189 l_locality_adj := 0;
1190 --Bug 4740036 Start
1191 --Bug 4740036 Start
1192
1193 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)
1194 AND l_pay_plan in ('GG','GS')
1195 AND p_pay_calc_data.pay_rate_determinant IN ('6','E','F')
1196 AND ghr_pay_calc.LEO_position (l_dummy
1197 ,p_pay_calc_data.position_id
1198 ,l_dummy
1199 ,l_dummy
1200 ,p_pay_calc_data.effective_date) THEN
1201 hr_utility.set_location('AB p_pay_calc_data.pay_rate_determinant ' || p_pay_calc_data.pay_rate_determinant,99);
1202 FOR c_rec IN c_user_table_id(ghr_pay_calc.l_standard_table_name) LOOP
1203 l_std_user_table_id := c_rec.user_table_id;
1204 END LOOP;
1205 hr_utility.set_location('AB l_std_user_table_id ' || l_std_user_table_id,99);
1206 IF p_pay_calc_data.pay_rate_determinant = '6' THEN
1207 hr_utility.set_location('AB p_pay_calc_data.grade_or_level ' || p_pay_calc_data.grade_or_level,99);
1208 l_new_step_or_rate := NVL(p_pay_calc_out_data.out_step_or_rate, p_pay_calc_data.step_or_rate);
1209 hr_utility.set_location('AB l_new_step_or_rate ' || l_new_step_or_rate,99);
1210 get_pay_table_value(l_std_user_table_id
1211 ,l_pay_plan
1212 ,p_pay_calc_data.grade_or_level
1213 ,l_new_step_or_rate
1214 ,p_pay_calc_data.effective_date
1215 ,l_std_basic_pay
1216 ,l_dummy_date
1217 ,l_dummy_date);
1218 ELSE
1219 hr_utility.set_location('AB l_retained_grade.grade_or_level ' || l_retained_grade.grade_or_level,99);
1220 hr_utility.set_location('AB l_retained_grade.step_or_rate ' || l_retained_grade.step_or_rate,99);
1221 IF p_pay_calc_data.noa_code IN ('867','892','893') THEN
1222 IF l_session.noa_id_correct is not null then
1223 IF l_retained_grade.temp_step is not null then
1224 is_retained_ia(p_pay_calc_data.person_id,
1225 p_pay_calc_data.effective_date,
1226 l_retained_grade.pay_plan,
1227 l_retained_grade.grade_or_level,
1228 l_retained_grade.step_or_rate,
1229 l_retained_grade.temp_step,
1230 l_ret_flag);
1231 IF l_ret_flag = TRUE THEN
1232 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1233 ,l_retained_grade.temp_step);
1234 ELSE
1235 l_new_step_or_rate := p_retained_grade.temp_step;
1236 END IF;
1237 ELSE
1238 is_retained_ia(p_pay_calc_data.person_id,
1239 p_pay_calc_data.effective_date,
1240 l_retained_grade.pay_plan,
1241 l_retained_grade.grade_or_level,
1242 l_retained_grade.step_or_rate,
1243 l_retained_grade.temp_step,
1244 l_ret_flag);
1245 IF l_ret_flag = TRUE THEN
1246 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1247 ,l_retained_grade.step_or_rate);
1248 ELSE
1249 l_new_step_or_rate := l_retained_grade.step_or_rate;
1250 END IF;
1251 END IF;
1252 ELSE
1253 IF p_retained_grade.temp_step is not null THEN
1254 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1255 ,p_retained_grade.temp_step);
1256 ELSE
1257 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_retained_grade.pay_plan
1258 ,p_retained_grade.step_or_rate);
1259 END IF;
1260 END IF;
1261 hr_utility.set_location('AB l_new_step_or_rate ' || l_new_step_or_rate,99);
1262 ELSE
1263 IF p_retained_grade.temp_step is not null THEN
1264 l_new_step_or_rate := p_retained_grade.temp_step;
1265 ELSE
1266 l_new_step_or_rate := p_retained_grade.step_or_rate;
1267 END IF;
1268 END IF;
1269 get_pay_table_value(l_std_user_table_id
1270 ,l_pay_plan
1271 ,l_retained_grade.grade_or_level
1272 ,l_new_step_or_rate
1273 ,p_pay_calc_data.effective_date
1274 ,l_std_basic_pay
1275 ,l_dummy_date
1276 ,l_dummy_date);
1277 END IF;
1278 hr_utility.set_location('AB l_std_basic_pay ' || l_std_basic_pay,99);
1279 l_adjustment_percentage := ghr_pay_calc.get_leo_lpa_percentage
1280 (p_pay_calc_data.duty_station_id
1281 ,p_pay_calc_data.effective_date);
1282 hr_utility.set_location('AB l_adjustment_percentage ' || l_adjustment_percentage,99);
1283 l_std_locality_adj := ROUND(l_std_basic_pay * (NVL(l_adjustment_percentage,0)/100),0);
1284 l_loc_rate := l_std_basic_pay + l_std_locality_adj;
1285 hr_utility.set_location('AB l_std_locality_adj ' || l_std_locality_adj,99);
1286 hr_utility.set_location('AB l_loc_rate ' || l_loc_rate,99);
1287 IF p_pay_calc_out_data.basic_pay > l_loc_rate THEN
1288 l_locality_adj := 0;
1289 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_out_data.basic_pay;
1290 ELSE
1291 p_pay_calc_out_data.adj_basic_pay := l_loc_rate;
1292 l_locality_adj := l_loc_rate - p_pay_calc_out_data.basic_pay;
1293 END IF;
1294 p_pay_calc_out_data.locality_adj := l_locality_adj;
1295 hr_utility.set_location('AB p_pay_calc_out_data.locality_adj ' || p_pay_calc_out_data.locality_adj,99);
1296 hr_utility.set_location('AB p_pay_calc_out_data.adj_basic_pay ' || p_pay_calc_out_data.adj_basic_pay,99);
1297
1298 --Bug 4740036 End
1299
1300 ELSE
1301
1302 l_basic_pay_pa := convert_amount(p_pay_calc_out_data.basic_pay
1303 ,p_pay_calc_data.pay_basis
1304 ,'PA');
1305 --
1306 IF LEO_position (p_pay_calc_data.pay_rate_determinant
1307 ,p_pay_calc_data.position_id
1308 ,p_retained_grade.user_table_id
1309 ,p_pay_calc_data.duty_station_id
1310 ,p_pay_calc_data.effective_date) OR
1311 l_pay_plan = 'GL' THEN
1312 -- as for PRD 0 below except use get_leo_lpa_percentage
1313 l_adjustment_percentage := NVL(get_leo_lpa_percentage(p_pay_calc_data.duty_station_id
1314 ,p_pay_calc_data.effective_date),0);
1315 --
1316 l_locality_adj := ROUND(l_basic_pay_pa * (NVL(l_adjustment_percentage,0)/100),0);
1317
1318 ELSE
1319 --
1320 --Bug# 6342011 added GR condition. For GR return copy from locality.
1321 IF l_pay_plan ='GR' THEN
1322 l_locality_adj :=p_pay_calc_data.open_out_locality_adj;
1323 ELSIF p_pay_calc_data.pay_rate_determinant IN ('0','7', 'A','B', '2','4',
1324 'J','K','R','S','3','U','V','D') THEN --Biug# 7557159
1325 -- This one is really easy just multiply the basic_pay (converted to pa) by the locality%age
1326 l_adjustment_percentage := NVL(get_lpa_percentage(p_pay_calc_data.duty_station_id
1327 ,p_pay_calc_data.effective_date),0);
1328 --
1329 l_locality_adj := ROUND(l_basic_pay_pa * (NVL(l_adjustment_percentage,0)/100),0);
1330 --
1331 ELSIF p_pay_calc_data.pay_rate_determinant IN ('5','6','E','F') THEN
1332 --
1333 IF p_pay_calc_data.pay_rate_determinant IN ('5','6') THEN
1334 IF l_pay_plan NOT IN ('GM','GH') THEN
1335 --
1336 -- Sundar 3294560 If Pay plan is 'GG' take from 'GG' else take from GS.
1337 -- Pay Plan should be either GG or GS, if not in 'GM', 'GH'. If not, then assign
1338 -- the standard table 'GS'
1339 IF l_pay_plan NOT IN ('GG','GS') THEN
1340 l_pay_plan := 'GS';
1341 END IF; -- IF l_pay_plan NOT IN ('GG','GS')
1342
1343 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1344 ,p_pay_calc_data.grade_or_level
1345 ,NVL(p_pay_calc_out_data.out_step_or_rate,p_pay_calc_data.step_or_rate)
1346 ,p_pay_calc_data.effective_date);
1347 -- End Bug 3294560
1348 ELSE -- GM,GH
1349 l_default_basic_pay := get_GM_GH_def_basic_pay(p_pay_calc_data
1350 ,p_retained_grade);
1351 --
1352 END IF; -- PAY PLAN NOT IN GM,GH
1353 ELSIF p_pay_calc_data.pay_rate_determinant IN ('E','F') THEN
1354 --
1355 IF l_pay_plan NOT IN ('GM','GH') THEN
1356 --
1357 -- Sundar 3294560 If Pay plan is 'GG' take from 'GG' else take from GS.
1358 -- Pay Plan should be either GG or GS, if not in 'GM', 'GH'. If not, then -- the standard table 'GS'
1359 IF l_pay_plan NOT IN ('GG','GS','GL') THEN
1360 l_pay_plan := 'GS';
1361 END IF; -- IF l_pay_plan NOT IN ('GG','GS','GL')
1362
1363 --- Bug 1953725 Start
1364
1365 IF p_pay_calc_data.noa_code IN ('867','892','893') THEN
1366 --Bug 2596425
1367 if l_session.noa_id_correct is not null then
1368 if l_retained_grade.temp_step is not null then
1369 -- Bug 3021003 Start
1370 is_retained_ia(p_pay_calc_data.person_id,
1371 p_pay_calc_data.effective_date,
1372 l_retained_grade.pay_plan,
1373 l_retained_grade.grade_or_level,
1374 l_retained_grade.step_or_rate,
1375 l_retained_grade.temp_step,
1376 l_ret_flag);
1377 IF l_ret_flag = TRUE then
1378 hr_utility.set_location('NAR ret step ' ||l_retained_grade.temp_step,10);
1379 hr_utility.set_location('NAR pay plan '||p_pay_calc_data.pay_plan,20);
1380 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1381 ,l_retained_grade.temp_step);
1382 hr_utility.set_location('NAR new step after getting the step ' ||l_new_step_or_rate,10);
1383 -- End Bug 3021003
1384 ELSE
1385 l_new_step_or_rate := p_retained_grade.temp_step;
1386 END IF;
1387 else
1388 -- Bug 3021003
1389 is_retained_ia(p_pay_calc_data.person_id,
1390 p_pay_calc_data.effective_date,
1391 l_retained_grade.pay_plan,
1392 l_retained_grade.grade_or_level,
1393 l_retained_grade.step_or_rate,
1394 l_retained_grade.temp_step,
1395 l_ret_flag);
1396 IF l_ret_flag = TRUE then
1397 hr_utility.set_location('NAR ret step ' ||l_retained_grade.step_or_rate,10);
1398 hr_utility.set_location('NAR pay plan '||p_pay_calc_data.pay_plan,20);
1399 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1400 ,l_retained_grade.step_or_rate);
1401 hr_utility.set_location('NAR new step after getting the step ' ||l_new_step_or_rate,10);
1402 ELSE
1403 l_new_step_or_rate := l_retained_grade.step_or_rate;
1404 END IF; -- IF is_retained_ia End Bug 3021003
1405 END IF; -- if p_retained_grade.temp_step is not null
1406 else
1407 if p_retained_grade.temp_step is not null then
1408
1409 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_calc_data.pay_plan
1410 ,p_retained_grade.temp_step);
1411 else
1412 l_new_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_retained_grade.pay_plan
1413 ,p_retained_grade.step_or_rate);
1414 end if;
1415 end if;
1416
1417
1418 -- Bug 3294560 Hard coding of 'GS' replaced with l_pay_plan
1419 IF p_retained_grade.temp_step IS NOT NULL THEN
1420
1421 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1422 ,p_pay_calc_data.grade_or_level
1423 ,l_new_step_or_rate
1424 ,p_pay_calc_data.effective_date);
1425 ELSE
1426 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1427 ,p_retained_grade.grade_or_level
1428 ,l_new_step_or_rate
1429 ,p_pay_calc_data.effective_date);
1430 END IF;
1431 ELSE
1432 --- Bug 1953725 End
1433 if p_retained_grade.temp_step is not null then
1434 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1435 ,p_pay_calc_data.grade_or_level
1436 ,p_retained_grade.temp_step
1437 ,p_pay_calc_data.effective_date);
1438 else
1439 l_default_basic_pay := get_standard_pay_table_value (l_pay_plan
1440 ,p_retained_grade.grade_or_level
1441 ,p_retained_grade.step_or_rate
1442 ,p_pay_calc_data.effective_date);
1443 end if;
1444 END IF;
1445 ELSE
1446 --
1447 l_default_basic_pay := get_GM_GH_def_basic_pay(p_pay_calc_data
1448 ,p_retained_grade);
1449 --
1450 END IF;
1451
1452 END IF;
1453
1454 l_adjustment_percentage := get_lpa_percentage(p_pay_calc_data.duty_station_id
1455 ,p_pay_calc_data.effective_date);
1456 -- Now do the comparison!
1457 l_default_adj_basic_pay := NVL(l_default_basic_pay,0)
1458 + ROUND(l_default_basic_pay *
1459 (NVL(l_adjustment_percentage,0)/100),0);
1460 --
1461 IF l_default_adj_basic_pay > l_basic_pay_pa THEN
1462 l_locality_adj := l_default_adj_basic_pay - l_basic_pay_pa;
1463 ELSE
1464 l_locality_adj := 0;
1465 END IF;
1466 --
1467 ELSIF p_pay_calc_data.pay_rate_determinant = 'M' THEN
1468 IF p_pay_calc_out_data.basic_pay >= p_pay_calc_data.current_adj_basic_pay THEN
1469 l_locality_adj := 0;
1470 --
1471 ELSE
1472 l_locality_adj := ghr_pay_calc.convert_amount
1473 (p_pay_calc_data.current_adj_basic_pay - p_pay_calc_out_data.basic_pay
1474 ,p_pay_calc_data.pay_basis
1475 ,'PA');
1476 --
1477 END IF;
1478
1479 ELSE
1480 -- should never have really got here as the basic pay should have stopped us
1481 -- calculating if we didn't know how to
1482 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
1483 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
1484 raise ghr_pay_calc.unable_to_calculate;
1485 END IF; -- End of PRD checks
1486 --
1487 END IF; -- End of LEO checks
1488 --
1489 END IF; -- End of PRD=M and 894
1490 ELSE
1491 -- We do not calculate locality adjustment for other pay plans
1492 l_locality_adj := 0;
1493 --
1494 END IF;
1495 --
1496 -- The above should just set l_locality_adj now set the real out parameter
1497 --
1498 p_pay_calc_out_data.locality_adj := l_locality_adj;
1499
1500 EXCEPTION
1501 WHEN others THEN
1502 -- Reset IN OUT parameters and set OUT parameters
1503 p_pay_calc_out_data := l_pay_calc_out_data;
1504 RAISE;
1505 --
1506 END get_locality_adj;
1507 --
1508 --------------------------- <get_adj_basic_pay> ----------------------------------
1509 PROCEDURE get_adj_basic_pay (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1510 ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1511
1512 --
1513 l_locality_adj_conv NUMBER;
1514 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1515
1516 BEGIN
1517
1518 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1519 --
1520 IF p_pay_calc_data.pay_rate_determinant = 'M' THEN
1521 --
1522 IF (p_pay_calc_data.pay_plan IN ('GS','GM') AND p_pay_calc_data.noa_code = '894') THEN
1523 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_out_data.basic_pay +
1524 p_pay_calc_out_data.locality_adj;
1525
1526 -- Basically M's keep the same adjusted basic pay unless basic pay is higher!
1527 ELSIF p_pay_calc_out_data.basic_pay >= p_pay_calc_data.current_adj_basic_pay THEN
1528 p_pay_calc_out_data.locality_adj := 0;
1529 --
1530 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_out_data.basic_pay;
1531 --
1532 ELSE
1533 p_pay_calc_out_data.adj_basic_pay := p_pay_calc_data.current_adj_basic_pay;
1534 --
1535 END IF;
1536 --
1537 ELSE
1538 -- convert the locality_adj (which is always 'PA') to the pay_basis of the
1539 -- basic pay so they can then be added together
1540 --
1541 l_locality_adj_conv := ghr_pay_calc.convert_amount(p_pay_calc_out_data.locality_adj
1542 ,'PA'
1543 ,p_pay_calc_data.pay_basis);
1544 --
1545 p_pay_calc_out_data.adj_basic_pay := NVL(p_pay_calc_out_data.basic_pay,0)
1546 + NVL(l_locality_adj_conv,0);
1547 END IF;
1548
1549 EXCEPTION
1550 WHEN others THEN
1551 -- Reset IN OUT parameters and set OUT parameters
1552 p_pay_calc_out_data := l_pay_calc_out_data;
1553 RAISE;
1554
1555 END get_adj_basic_pay;
1556
1557 --------------------------- <get_ppi_amount> --------------------------------------
1558 FUNCTION get_ppi_amount (p_ppi_code IN VARCHAR2
1559 ,p_amount IN NUMBER
1560 ,p_pay_basis IN VARCHAR2)
1561
1562 RETURN NUMBER IS
1563 -- bug 710122 need to convert amount to PA before doing calc
1564 CURSOR cur_ppi IS
1565 SELECT ppi.ppi_percentage
1566 FROM ghr_premium_pay_indicators ppi
1567 WHERE code = p_ppi_code;
1568
1569 l_amount_pa NUMBER;
1570 l_ppi_percentage NUMBER;
1571 l_ret_val NUMBER;
1572 BEGIN
1573 IF p_ppi_code IS NOT NULL THEN
1574 FOR cur_ppi_rec IN cur_ppi LOOP
1575 l_ppi_percentage := cur_ppi_rec.ppi_percentage;
1576 END LOOP;
1577
1578 -- rounding??
1579 --
1580 l_amount_pa := convert_amount(p_amount,p_pay_basis ,'PA');
1581 l_ret_val := TRUNC(l_amount_pa * NVL(l_ppi_percentage,0) / 100 , 0); --Bug 3067420 changed ROUND -> TRUNC
1582
1583 ELSE
1584 l_ret_val := NULL;
1585 END IF;
1586
1587 RETURN(l_ret_val);
1588
1589 END get_ppi_amount;
1590
1591 --------------------------- <get_other_pay_amount> --------------------------------
1592 PROCEDURE get_other_pay_amount (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
1593 ,p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type) IS
1594
1595 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1596
1597 BEGIN
1598
1599 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1600
1601 -- should not need to worry about the pay basis as they are all in the same basis
1602 -- If ALL the values are null then return null as opposed to 0:
1603 -- NOTE: these should all be per annum amounts
1604 IF p_pay_calc_out_data.au_overtime IS NULL
1605 AND p_pay_calc_out_data.availability_pay IS NULL
1606 AND p_pay_calc_out_data.retention_allowance IS NULL
1607 AND p_pay_calc_data.supervisory_differential IS NULL
1608 AND p_pay_calc_data.staffing_differential IS NULL THEN
1609 p_pay_calc_out_data.other_pay_amount := NULL;
1610 ELSE
1611 p_pay_calc_out_data.other_pay_amount := NVL(p_pay_calc_out_data.au_overtime,0)
1612 + NVL(p_pay_calc_out_data.availability_pay,0)
1613 + NVL(p_pay_calc_out_data.retention_allowance,0)
1614 + NVL(p_pay_calc_data.supervisory_differential,0)
1615 + NVL(p_pay_calc_data.staffing_differential,0);
1616 END IF;
1617 --
1618
1619 EXCEPTION
1620 WHEN others THEN
1621 -- Reset IN OUT parameters and set OUT parameters
1622 p_pay_calc_out_data := l_pay_calc_out_data ;
1623 RAISE;
1624 END get_other_pay_amount;
1625
1626 --------------------------- <get_total_salary> ------------------------------------
1627 PROCEDURE get_total_salary (p_pay_calc_out_data IN OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
1628 ,p_pay_basis IN VARCHAR2) IS
1629
1630 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1631 BEGIN
1632
1633 l_pay_calc_out_data := p_pay_calc_out_data ; --NOCOPY Changes
1634
1635 -- 24-Nov-2003 For Other Pay conversion is not required due to FWS Retention Calc. Changes
1636 p_pay_calc_out_data.total_salary := NVL(p_pay_calc_out_data.adj_basic_pay,0)
1637 + NVL(p_pay_calc_out_data.other_pay_amount,0);
1638
1639 EXCEPTION
1640 WHEN others THEN
1641 -- Reset IN OUT parameters and set OUT parameters
1642 p_pay_calc_out_data := l_pay_calc_out_data ;
1643 RAISE;
1644
1645 END get_total_salary;
1646
1647 PROCEDURE main_pay_calc (p_person_id IN per_people_f.person_id%TYPE
1648 ,p_position_id IN hr_all_positions_f.position_id%TYPE
1649 ,p_noa_family_code IN ghr_families.noa_family_code%TYPE
1650 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
1651 ,p_second_noa_code IN ghr_nature_of_actions.code%TYPE
1652 ,p_first_action_la_code1 IN ghr_pa_requests.first_action_la_code1%TYPE
1653 ,p_effective_date IN DATE
1654 ,p_pay_rate_determinant IN VARCHAR2
1655 ,p_pay_plan IN VARCHAR2
1656 ,p_grade_or_level IN VARCHAR2
1657 ,p_step_or_rate IN VARCHAR2
1658 ,p_pay_basis IN VARCHAR2
1659 ,p_user_table_id IN NUMBER
1660 ,p_duty_station_id IN NUMBER
1661 ,p_auo_premium_pay_indicator IN VARCHAR2
1662 ,p_ap_premium_pay_indicator IN VARCHAR2
1663 ,p_retention_allowance IN NUMBER
1664 ,p_to_ret_allow_percentage IN NUMBER
1665 ,p_supervisory_differential IN NUMBER
1666 ,p_staffing_differential IN NUMBER
1667 ,p_current_basic_pay IN NUMBER
1668 ,p_current_adj_basic_pay IN NUMBER
1669 ,p_current_step_or_rate IN VARCHAR2
1670 ,p_pa_request_id IN NUMBER
1671 ,p_open_range_out_basic_pay IN NUMBER DEFAULT NULL
1672 ,p_open_out_locality_adj IN NUMBER DEFAULT NULL
1673 ,p_basic_pay OUT NOCOPY NUMBER
1674 ,p_locality_adj OUT NOCOPY NUMBER
1675 ,p_adj_basic_pay OUT NOCOPY NUMBER
1676 ,p_total_salary OUT NOCOPY NUMBER
1677 ,p_other_pay_amount OUT NOCOPY NUMBER
1678 ,p_to_retention_allowance OUT NOCOPY NUMBER
1679 ,p_ret_allow_perc_out OUT NOCOPY NUMBER
1680 ,p_au_overtime OUT NOCOPY NUMBER
1681 ,p_availability_pay OUT NOCOPY NUMBER
1682 -- FWFA Changes
1683 ,p_calc_pay_table_id OUT NOCOPY NUMBER
1684 ,p_pay_table_id OUT NOCOPY NUMBER
1685 -- FWFA Changes
1686 ,p_out_step_or_rate OUT NOCOPY VARCHAR2
1687 ,p_out_pay_rate_determinant OUT NOCOPY VARCHAR2
1688 ,p_out_to_grade_id OUT NOCOPY NUMBER
1689 ,p_out_to_pay_plan OUT NOCOPY VARCHAR2
1690 ,p_out_to_grade_or_level OUT NOCOPY VARCHAR2
1691 ,p_PT_eff_start_date OUT NOCOPY DATE
1692 ,p_open_basicpay_field OUT NOCOPY BOOLEAN
1693 ,p_open_pay_fields OUT NOCOPY BOOLEAN
1694 ,p_message_set OUT NOCOPY BOOLEAN
1695 ,p_calculated OUT NOCOPY BOOLEAN
1696 ,p_open_localityadj_field OUT NOCOPY BOOLEAN
1697 ) IS
1698 --
1699 l_ghr_pa_request_rec ghr_pa_requests%ROWTYPE;
1700 --
1701 l_effective_date DATE;
1702 --
1703 l_position_id hr_all_positions_f.position_id%TYPE;
1704 l_noa_family_code ghr_families.noa_family_code%TYPE;
1705 l_noa_code ghr_nature_of_actions.code%TYPE;
1706 l_second_noa_code ghr_nature_of_actions.code%TYPE;
1707 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
1708 l_pay_rate_determinant VARCHAR2(30);
1709 l_pay_plan VARCHAR2(30);
1710 l_grade_or_level VARCHAR2(60);
1711 l_step_or_rate VARCHAR2(30);
1712 l_pay_basis VARCHAR2(30);
1713 l_duty_station_id NUMBER;
1714 l_auo_premium_pay_indicator VARCHAR2(30);
1715 l_ap_premium_pay_indicator VARCHAR2(30);
1716 l_retention_allowance NUMBER;
1717 l_dummy NUMBER;
1718 l_to_retention_allowance NUMBER;
1719 l_to_ret_allow_percentage NUMBER(15,2);
1720 l_ret_calc_perc NUMBER(15,2);
1721 l_supervisory_differential NUMBER;
1722 l_staffing_differential NUMBER;
1723 l_current_basic_pay NUMBER;
1724 l_current_adj_basic_pay NUMBER;
1725 l_current_step_or_rate VARCHAR2(30);
1726 -- Bug#5120116
1727 l_capped_other_pay NUMBER;
1728 l_prd_d_pay_amount NUMBER; --Bug# 7557159
1729 --
1730 l_second_noa_family_code ghr_families.noa_family_code%TYPE;
1731 l_run_pay_calc_again BOOLEAN := FALSE;
1732 --
1733 l_pay_calc_data ghr_pay_calc.pay_calc_in_rec_type; -- This is the main IN record structure
1734 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type; -- This is the main OUT record structure
1735 l_retained_grade ghr_pay_calc.retained_grade_rec_type; -- This contains retained grade info if needed
1736 --
1737 l_message_set BOOLEAN := FALSE;
1738 l_calculated BOOLEAN := TRUE;
1739 --
1740 l_user_table_id pay_user_tables.user_table_id%TYPE;
1741 --
1742
1743 l_proc VARCHAR2(30) := 'main_pay_calc';
1744
1745 cursor cur_ex_emp is
1746 select 1
1747 from per_person_types pet1,
1748 per_people_f per1
1749 where pet1.person_type_id = per1.person_type_id
1750 and per1.person_id = p_person_id
1751 and nvl(p_effective_date,trunc(sysdate))
1752 between per1.effective_start_date and per1.effective_end_date
1753 and pet1.system_person_type = 'EX_EMP';
1754
1755 l_ex_emp BOOLEAN;
1756 l_session ghr_history_api.g_session_var_type;
1757 v_session ghr_history_api.g_session_var_type;
1758
1759 ----IA Correction Logic.
1760 cursor c_pa_req1 is
1761 select pa_notification_id
1762 from ghr_pa_requests
1763 where pa_request_id = l_ghr_pa_request_rec.altered_pa_request_id;
1764
1765 l_ia_flag varchar2(30);
1766 l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1767 l_retro_first_noa ghr_nature_of_actions.code%type;
1768 l_retro_second_noa ghr_nature_of_actions.code%type;
1769
1770 l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1771 l_ia_effective_date ghr_pa_requests.effective_date%type;
1772 l_ia_retro_first_noa ghr_nature_of_actions.code%type;
1773 l_ia_retro_second_noa ghr_nature_of_actions.code%type;
1774 l_old_effective_date date;
1775 l_old_ret_allow NUMBER;
1776 l_value VARCHAR2(60);
1777 l_multi_error_flag BOOLEAN;
1778 l_from_basic_pay NUMBER;
1779 l_from_retention_allowance NUMBER;
1780
1781 -- Bug 3248061
1782 l_altered_pa_req_id ghr_pa_requests.altered_pa_request_id%type;
1783 -- Bug 3245692
1784 l_from_pay_basis ghr_pa_requests.from_pay_basis%type;
1785
1786 CURSOR c_get_from_pay_basis(c_pa_request_id IN ghr_pa_requests.pa_request_id%type) IS
1787 SELECT from_pay_basis
1788 FROM ghr_pa_requests
1789 WHERE pa_request_id = c_pa_request_id;
1790
1791 CURSOR c_get_alt_pareq_id(c_pa_request_id IN ghr_pa_requests.pa_request_id%type) IS
1792 SELECT altered_pa_request_id
1793 FROM ghr_pa_requests
1794 WHERE pa_request_id = c_pa_request_id;
1795
1796 CURSOR c_get_notification_id(c_alt_pa_request_id IN ghr_pa_requests.pa_request_id%type) IS
1797 SELECT pa_notification_id
1798 FROM ghr_pa_requests
1799 WHERE pa_request_id = c_alt_pa_request_id;
1800 -- End Bug 3248061
1801
1802 -- Bug#5120116 created the cursor to get assignment id.
1803 CURSOR c_asg_rec(c_person_id NUMBER, c_effective_date DATE) IS
1804 SELECT assignment_id
1805 FROM per_all_assignments_f
1806 WHERE person_id = c_person_id
1807 AND assignment_type = 'E'
1808 AND c_effective_date between effective_start_date and effective_end_date;
1809
1810 -- Bug#6154261
1811 cursor espayplan
1812 is
1813 select 1
1814 from ghr_pay_plans
1815 where equivalent_pay_plan = 'ES'
1816 and pay_plan = l_pay_plan;
1817
1818 esplan_flag varchar2(1);
1819 --Bug #6154261
1820 l_assignment_id NUMBER;
1821
1822 l_max_RA NUMBER;
1823
1824
1825 ------ GPPA Update 46 - GM Pay plan will change to GS pay plan for 890 NOAC
1826 l_business_group_id per_positions.organization_id%TYPE;
1827
1828 CURSOR GET_GRADE_ID (v_pay_plan varchar2, v_grade_or_level varchar2) IS
1829 SELECT grd.grade_id grade_id
1830 FROM per_grades grd,
1831 per_grade_definitions gdf
1832 WHERE gdf.segment1 = v_pay_plan
1833 AND gdf.segment2 = v_grade_or_level
1834 AND grd.grade_definition_id = gdf.grade_definition_id
1835 AND grd.business_group_id = l_business_group_id;
1836
1837 l_grade_id NUMBER;
1838 l_pay_plan_changed BOOLEAN;
1839 l_default_pay_plan VARCHAR2(5);
1840 ------ GPPA Update 46 end
1841
1842 BEGIN
1843
1844 ------ GPPA Update 46 Start
1845 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
1846 l_pay_plan_changed := FALSE;
1847 ------ GPPA Update 46 End
1848
1849 l_ex_emp := FALSE;
1850 for cur_ex_emp_rec in cur_ex_emp
1851 LOOP
1852 l_ex_emp := TRUE;
1853 END LOOP;
1854
1855 hr_utility.set_location('Entering main pay calc...'|| l_proc,5);
1856 -----Bug 3288419 Initialize the global variables.
1857 g_gm_unadjd_basic_pay := NULL;
1858 gm_unadjusted_pay_flg := NULL;
1859 -- FWFA Changes Bug#4444609
1860 g_pay_table_upd_flag := FALSE;
1861 g_fwfa_pay_calc_flag := FALSE;
1862 g_gl_upd_flag := FALSE;
1863 g_fw_equiv_pay_plan := FALSE; --Bug 5218445
1864 g_out_to_pay_plan := NULL;
1865 -- FWFA Changes
1866 l_pay_calc_out_data.open_pay_fields := FALSE;
1867 l_pay_calc_out_data.open_basicpay_field := FALSE;
1868 -- Bug#5132113
1869 l_pay_calc_out_data.open_localityadj_field := FALSE;
1870
1871 BEGIN
1872 -- This is the main starting point of pay calc.
1873 -- Basically the user gives us the IN parameters needed to calculate
1874 -- pay and if we can we do it if not
1875 -- we pass the same parameters on to a custome defined procedure
1876 --
1877 -- Bug For 866 use effective date + 1
1878 --
1879 -- Bug 705022 Now overrides the previous bug!! :- 866 also uses simply effective_date
1880 IF NVL(p_noa_code,'@@!!##') = '866' OR NVL(p_second_noa_code,'@@!!##') = '866' THEN
1881 l_effective_date := p_effective_date + 1;
1882 ELSE
1883 l_effective_date := p_effective_date;
1884 END IF;
1885 --
1886 IF ( p_noa_code = '702' OR p_second_noa_code = '702' )
1887 AND ( p_pay_rate_determinant in ('A','B','E','F','U','V') ) THEN
1888 hr_utility.set_message(8301,'GHR_38697_RG_PROMOTE');
1889 raise pay_calc_message;
1890 END IF;
1891
1892 -- For corrections we need to go and get alot of the IN parameters
1893 -- as the form does not populate them:
1894 IF p_noa_family_code = 'CORRECT' THEN
1895 -- Must have a pa_request_id for a CORRECT family
1896 ghr_pc_basic_pay.g_noa_family_code := 'CORRECT';
1897 IF p_pa_request_id IS NULL THEN
1898 hr_utility.set_message(8301,'GHR_38398_PAY_CALC_NO_PAR_ID');
1899 raise pay_calc_message;
1900 END IF;
1901 ghr_corr_canc_sf52.build_corrected_sf52
1902 (p_pa_request_id => p_pa_request_id
1903 ,p_noa_code_correct => p_second_noa_code
1904 ,p_sf52_data_result => l_ghr_pa_request_rec
1905 ,p_called_from => 'FROM_PAYCAL');
1906
1907 l_position_id := NVL(p_position_id,l_ghr_pa_request_rec.to_position_id);
1908 l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family(p_second_noa_code);
1909 l_noa_code := p_second_noa_code;
1910 l_second_noa_code := NULL;
1911 l_first_action_la_code1 := NVL(p_first_action_la_code1 ,l_ghr_pa_request_rec.first_action_la_code1);
1912 l_pay_rate_determinant := NVL(p_pay_rate_determinant ,l_ghr_pa_request_rec.pay_rate_determinant);
1913 l_pay_plan := NVL(p_pay_plan ,l_ghr_pa_request_rec.to_pay_plan);
1914 l_grade_or_level := NVL(p_grade_or_level ,l_ghr_pa_request_rec.to_grade_or_level);
1915 l_step_or_rate := NVL(p_step_or_rate ,l_ghr_pa_request_rec.to_step_or_rate);
1916 l_pay_basis := NVL(p_pay_basis ,l_ghr_pa_request_rec.to_pay_basis);
1917 l_duty_station_id := NVL(p_duty_station_id ,l_ghr_pa_request_rec.duty_station_id);
1918 l_auo_premium_pay_indicator := NVL(p_auo_premium_pay_indicator,l_ghr_pa_request_rec.to_auo_premium_pay_indicator);
1919 l_ap_premium_pay_indicator := NVL(p_ap_premium_pay_indicator ,l_ghr_pa_request_rec.to_ap_premium_pay_indicator);
1920 l_retention_allowance := NVL(p_retention_allowance ,l_ghr_pa_request_rec.to_retention_allowance);
1921 l_to_ret_allow_percentage := NVL(p_to_ret_allow_percentage ,l_ghr_pa_request_rec.to_retention_allow_percentage);
1922 l_supervisory_differential := NVL(p_supervisory_differential ,l_ghr_pa_request_rec.to_supervisory_differential);
1923 l_staffing_differential := NVL(p_staffing_differential ,l_ghr_pa_request_rec.to_staffing_differential);
1924 l_current_basic_pay := NVL(p_current_basic_pay ,l_ghr_pa_request_rec.from_basic_pay);
1925 l_current_adj_basic_pay := NVL(p_current_adj_basic_pay ,l_ghr_pa_request_rec.from_adj_basic_pay);
1926 l_current_step_or_rate := NVL(p_current_step_or_rate ,l_ghr_pa_request_rec.from_step_or_rate);
1927
1928 -- For correction actions take pay basis from ghr_pa_requests table itself.
1929 FOR ctr_from_pay_basis IN c_get_from_pay_basis(p_pa_request_id) LOOP
1930 l_from_pay_basis := ctr_from_pay_basis.from_pay_basis;
1931 END LOOP;
1932
1933 ELSE
1934 ghr_pc_basic_pay.g_noa_family_code := NULL;
1935 l_position_id := p_position_id;
1936 l_noa_family_code := p_noa_family_code;
1937 l_noa_code := p_noa_code;
1938 l_second_noa_code := p_second_noa_code;
1939 l_first_action_la_code1 := p_first_action_la_code1;
1940 l_pay_rate_determinant := p_pay_rate_determinant;
1941 l_pay_plan := p_pay_plan;
1942 l_grade_or_level := p_grade_or_level;
1943 l_step_or_rate := p_step_or_rate;
1944 l_pay_basis := p_pay_basis;
1945 l_duty_station_id := p_duty_station_id;
1946 l_auo_premium_pay_indicator := p_auo_premium_pay_indicator;
1947 l_ap_premium_pay_indicator := p_ap_premium_pay_indicator;
1948 l_retention_allowance := p_retention_allowance;
1949 l_to_ret_allow_percentage := p_to_ret_allow_percentage;
1950 l_supervisory_differential := p_supervisory_differential;
1951 l_staffing_differential := p_staffing_differential;
1952 l_current_basic_pay := p_current_basic_pay;
1953 l_current_adj_basic_pay := p_current_adj_basic_pay;
1954 l_current_step_or_rate := p_current_step_or_rate;
1955
1956 -- 3245692 Get From pay basis from the function get_pay_basis
1957 l_from_pay_basis := get_pay_basis(
1958 p_effective_date => p_effective_date,
1959 p_pa_request_id => p_pa_request_id,
1960 p_person_id => p_person_id);
1961 IF (l_from_pay_basis = '-1') THEN
1962 hr_utility.set_message(8301,'GHR_38020_PAY_CALC_NO_PAY_BAS');
1963 raise pay_calc_message;
1964 END IF;
1965 -- End 3245692
1966
1967 END IF;
1968
1969 hr_utility.set_location(' Passed Noa Fam Code = ' || p_noa_family_code,5);
1970 hr_utility.set_location(' Passed l_retention_allowance = ' || to_char(l_retention_allowance),5);
1971 hr_utility.set_location(' Passed l_to_ret_allow_percentage = ' || to_char(l_to_ret_allow_percentage),5);
1972
1973 ghr_history_api.get_g_session_var(l_session);
1974 hr_utility.set_location( 'Get Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
1975 hr_utility.set_location( 'Get Ses ASGID is ' || to_char(l_session.assignment_id), 15);
1976 -- set values of session variables IA fetch sake.
1977 v_session.pa_request_id := l_session.pa_request_id;
1978 v_session.noa_id := l_session.noa_id;
1979 v_session.fire_trigger := l_session.fire_trigger;
1980 v_session.date_Effective := l_session.date_Effective;
1981 v_session.person_id := l_session.person_id;
1982 v_session.program_name := l_session.program_name;
1983 v_session.assignment_id := l_session.assignment_id;
1984 v_session.altered_pa_request_id := l_session.altered_pa_request_id;
1985 v_session.noa_id_correct := l_session.noa_id_correct;
1986
1987
1988 IF l_session.noa_id_correct is not null then
1989 ghr_pc_basic_pay.g_noa_family_code := 'CORRECT';
1990 -- First determine presence of retro active actions
1991 l_ia_flag := 'N';
1992 -- Bug 3248061 Sundar
1993 -- l_ghr_pa_request_rec.pa_request_id becomes null for correction record. So in that case
1994 -- Use p_pa_request_id which is passed instead of the value from the record l_ghr_pa_request_rec
1995 IF (l_ghr_pa_request_rec.pa_request_id IS NULL) THEN
1996 -- Get the Original Id of this correction action
1997 FOR ctr_alt_pareq_id IN c_get_alt_pareq_id(p_pa_request_id) LOOP
1998 l_altered_pa_req_id := ctr_alt_pareq_id.altered_pa_request_id;
1999 END LOOP;
2000 -- Get Notification ID for this Original pa_request_id
2001 FOR ctr_get_notify_id IN c_get_notification_id(l_altered_pa_req_id) LOOP
2002 l_pa_notification_id := ctr_get_notify_id.pa_notification_id;
2003 END LOOP;
2004 ELSE
2005 FOR c_pa_rec1 IN c_pa_req1 LOOP
2006 l_pa_notification_id := c_pa_rec1.pa_notification_id;
2007 END LOOP;
2008 END IF;
2009 -- End Bug 3248061
2010 -- Get the pa_notification_id from the original action
2011
2012 --BUG #7216635 added the parameter p_noa_id_correct
2013 GHR_APPROVED_PA_REQUESTS.determine_ia(
2014 p_pa_request_id => l_ghr_pa_request_rec.altered_pa_request_id,
2015 p_pa_notification_id => l_pa_notification_id,
2016 p_person_id => p_person_id,
2017 p_effective_date => l_effective_date,
2018 p_noa_id_correct => l_session.noa_id_correct,
2019 p_retro_pa_request_id => l_retro_pa_request_id,
2020 p_retro_eff_date => l_ia_effective_date,
2021 p_retro_first_noa => l_ia_retro_first_noa,
2022 p_retro_second_noa => l_ia_retro_second_noa);
2023 if l_ia_effective_date is NOT NULL THEN
2024 l_ia_flag := 'Y';
2025 IF l_retro_first_noa = '866' then
2026 l_ia_effective_date := l_ia_effective_date + 1;
2027 END IF;
2028 end if;
2029
2030 hr_utility.set_location( 'l_ia_effective_date ' || to_char(l_ia_effective_date,'YYYY/MM/DD'),15);
2031
2032 hr_utility.set_location( 'Ses NOAIDCORR is ' || to_char(v_session.noa_id_correct), 15);
2033 hr_utility.set_location( 'Ses ASGID is ' || to_char(v_session.assignment_id), 15);
2034
2035 ghr_history_api.reinit_g_session_var;
2036 l_session.noa_id_correct := NULL;
2037 hr_utility.set_location( 'Set Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
2038 ghr_history_api.set_g_session_var(l_session);
2039
2040 if l_ia_effective_date is not null then
2041 l_old_effective_date := l_ia_effective_date - 1;
2042 else
2043 l_old_effective_date := l_effective_date - 1;
2044 end if;
2045
2046 hr_utility.set_location( 'l_old_effective_date ' || to_char(l_old_effective_date,'YYYY/MM/DD'),15);
2047 hr_utility.set_location( 'Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
2048 hr_utility.set_location( 'Ses ASGID is ' || to_char(l_session.assignment_id), 15);
2049 hr_utility.set_location( 'Fetch old Retention Allowance' || l_proc,15);
2050
2051 -------Assignment id is different then need to be checked the following logic.
2052
2053 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2054 ,p_input_value_name => 'Amount'
2055 ,p_assignment_id => l_session.assignment_id
2056 ,p_effective_date => l_old_effective_date
2057 ,p_value => l_value
2058 ,p_multiple_error_flag => l_multi_error_flag);
2059 l_old_ret_allow := to_number(l_value);
2060 hr_utility.set_location( 'l_old_ret_allow is ' || to_char(l_old_ret_allow), 15);
2061
2062 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2063 ,p_input_value_name => 'Amount'
2064 ,p_assignment_id => l_session.assignment_id
2065 ,p_effective_date => nvl(l_ia_effective_date,l_effective_date)
2066 ,p_value => l_value
2067 ,p_multiple_error_flag => l_multi_error_flag);
2068 l_from_retention_allowance := to_number(l_value);
2069 hr_utility.set_location( 'l_from_retention_allowance is ' || to_char(l_from_retention_allowance), 15);
2070 --
2071 -- Modifying the input values from percent to percentage for Payroll Integration
2072 --
2073 ghr_api.retrieve_element_entry_value (p_element_name => 'Basic Salary Rate'
2074 ,p_input_value_name => 'Rate'
2075 ,p_assignment_id => l_session.assignment_id
2076 ,p_effective_date => nvl(l_ia_effective_date,l_effective_date)
2077 ,p_value => l_value
2078 ,p_multiple_error_flag => l_multi_error_flag);
2079 l_from_basic_pay := to_number(l_value);
2080 hr_utility.set_location( 'l_from_basic_pay is ' || to_char(l_from_basic_pay), 15);
2081
2082 if l_ia_effective_date is not null then
2083 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2084 ,p_input_value_name => 'Amount'
2085 ,p_assignment_id => l_session.assignment_id
2086 ,p_effective_date => l_ia_effective_date
2087 ,p_value => l_value
2088 ,p_multiple_error_flag => l_multi_error_flag);
2089
2090 l_ghr_pa_request_rec.to_retention_allowance := to_number(l_value);
2091 hr_utility.set_location( 'RA is ' || to_char(l_ghr_pa_request_rec.to_retention_allowance), 15);
2092 --
2093 -- Modifying the input values from percent to percentage for Payroll Integration
2094 --
2095 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2096 ,p_input_value_name => 'Percentage'
2097 ,p_assignment_id => l_session.assignment_id
2098 ,p_effective_date => l_ia_effective_date
2099 ,p_value => l_value
2100 ,p_multiple_error_flag => l_multi_error_flag);
2101
2102 l_ghr_pa_request_rec.to_retention_allow_percentage := to_number(l_value);
2103 hr_utility.set_location( 'RA % is ' || to_char(l_ghr_pa_request_rec.to_retention_allow_percentage),15);
2104
2105 ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
2106 ,p_input_value_name => 'Amount'
2107 ,p_assignment_id => l_session.assignment_id
2108 ,p_effective_date => l_ia_effective_date
2109 ,p_value => l_value
2110 ,p_multiple_error_flag => l_multi_error_flag);
2111
2112 l_ghr_pa_request_rec.to_supervisory_differential := to_number(l_value);
2113 hr_utility.set_location( 'SD is ' || to_char(l_ghr_pa_request_rec.to_supervisory_differential), 15);
2114
2115
2116 ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
2117 ,p_input_value_name => 'Premium Pay Ind'
2118 ,p_assignment_id => l_session.assignment_id
2119 ,p_effective_date => l_ia_effective_date
2120 ,p_value => l_value
2121 ,p_multiple_error_flag => l_multi_error_flag);
2122
2123 l_ghr_pa_request_rec.to_auo_premium_pay_indicator := l_value;
2124 hr_utility.set_location( 'AUO is ' || (l_ghr_pa_request_rec.to_auo_premium_pay_indicator), 15);
2125
2126
2127 ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
2128 ,p_input_value_name => 'Premium Pay Ind'
2129 ,p_assignment_id => l_session.assignment_id
2130 ,p_effective_date => l_ia_effective_date
2131 ,p_value => l_value
2132 ,p_multiple_error_flag => l_multi_error_flag);
2133
2134 l_ghr_pa_request_rec.to_ap_premium_pay_indicator := l_value;
2135 hr_utility.set_location( 'AP is ' || (l_ghr_pa_request_rec.to_ap_premium_pay_indicator), 15);
2136 l_auo_premium_pay_indicator := NVL(p_auo_premium_pay_indicator,l_ghr_pa_request_rec.to_auo_premium_pay_indicator);
2137 l_ap_premium_pay_indicator := NVL(p_ap_premium_pay_indicator ,l_ghr_pa_request_rec.to_ap_premium_pay_indicator);
2138 l_retention_allowance := NVL(p_retention_allowance ,l_ghr_pa_request_rec.to_retention_allowance);
2139 l_to_ret_allow_percentage := NVL(p_to_ret_allow_percentage ,l_ghr_pa_request_rec.to_retention_allow_percentage);
2140 l_supervisory_differential := NVL(p_supervisory_differential ,l_ghr_pa_request_rec.to_supervisory_differential);
2141 end if;
2142 ELSE
2143 ghr_pc_basic_pay.g_noa_family_code := NULL;
2144 END IF;
2145 ghr_history_api.reinit_g_session_var;
2146 -- Reset values of session variables IA fetch sake.
2147 l_session.pa_request_id := v_session.pa_request_id;
2148 l_session.noa_id := v_session.noa_id;
2149 l_session.fire_trigger := v_session.fire_trigger;
2150 l_session.date_Effective := v_session.date_Effective;
2151 l_session.person_id := v_session.person_id;
2152 l_session.program_name := v_session.program_name;
2153 l_session.assignment_id := v_session.assignment_id;
2154 l_session.altered_pa_request_id := v_session.altered_pa_request_id;
2155 l_session.noa_id_correct := v_session.noa_id_correct;
2156 ghr_history_api.set_g_session_var(l_session);
2157
2158 hr_utility.set_location( 'Ses NOAIDCORR is ' || to_char(l_session.noa_id_correct), 15);
2159 hr_utility.set_location( 'Ses ASGID is ' || to_char(l_session.assignment_id), 15);
2160
2161
2162 -- If we didn't get the user_table_id passed in then try and get it from the position_id
2163 -- This is specifically for the Form as I would expect any body else who uses this to get ALL
2164 -- details associated with position at the same time i.e pay_plan, grade_or_level, step_or_rate,
2165 -- pay_basis and at the same time get user_table_id. The reason the form doesn't have it is because
2166 -- it is not displayed on the SF52!
2167 IF p_user_table_id IS NULL THEN
2168 l_user_table_id := get_user_table_id (l_position_id
2169 ,NVL(p_effective_date,TRUNC(sysdate)) );
2170 ELSE
2171 l_user_table_id := p_user_table_id;
2172 END IF;
2173 --
2174 -- Dual Actions yuk!!!
2175 -- Only do this when we have a second NOA
2176 -- We basically have the following situations;
2177 -- First Noa Second NOA Solution
2178 -- MAIN No Pay Calc Use First NOA
2179 -- MAIN MAIN Use First NOA
2180 -- MAIN 892/893 Use First NOA
2181
2182 -- No Pay Calc No Pay Calc Use First NOA
2183 -- No Pay Calc MAIN Use Second NOA
2184 -- No Pay Calc 892/893 Not a valid option
2185
2186 -- 892/893 No Pay Calc Use First NOA
2187 -- 892/893 MAIN Use Second NOA
2188 -- 892/893 892/893 Run Pac Calc twice!!
2189 --
2190 -- Firstly if no second NOA then nothing to worry about!!
2191 IF l_second_noa_code IS NOT NULL THEN
2192 l_second_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family(p_second_noa_code);
2193 --
2194 IF l_second_noa_family_code IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
2195 ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY')
2196 AND (l_noa_code IN ('892','893')
2197 OR l_second_noa_family_code NOT IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
2198 ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY')
2199 ) THEN
2200 l_noa_family_code := l_second_noa_family_code;
2201 l_noa_code := l_second_noa_code;
2202 l_second_noa_code := NULL;
2203 ELSE
2204 IF l_noa_code IN ('892','893') AND l_second_noa_code IN ('892','893') THEN
2205 l_run_pay_calc_again := TRUE;
2206 END IF;
2207 -- keep the first one and blank the second
2208 l_second_noa_code := NULL;
2209 --
2210 END IF;
2211 --
2212 END IF;
2213 --
2214 -- populate a general record group that includes all the IN parameters and pass that around
2215 --
2216 l_pay_calc_data := populate_in_rec_structure
2217 (p_person_id
2218 ,l_position_id
2219 ,l_noa_family_code
2220 ,l_noa_code
2221 ,l_second_noa_code
2222 ,l_first_action_la_code1
2223 ,l_effective_date
2224 ,l_pay_rate_determinant
2225 ,l_pay_plan
2226 ,l_grade_or_level
2227 ,l_step_or_rate
2228 ,l_pay_basis
2229 ,l_user_table_id
2230 ,l_duty_station_id
2231 ,l_auo_premium_pay_indicator
2232 ,l_ap_premium_pay_indicator
2233 ,l_retention_allowance
2234 ,l_to_ret_allow_percentage
2235 ,l_supervisory_differential
2236 ,l_staffing_differential
2237 ,l_current_basic_pay
2238 ,l_current_adj_basic_pay
2239 ,l_current_step_or_rate
2240 ,p_pa_request_id
2241 ,p_open_range_out_basic_pay
2242 --Bug #5132113 added new parameter
2243 ,p_open_out_locality_adj
2244 );
2245 --
2246 -- Next do any validation
2247 --
2248 validate_in_data(l_pay_calc_data);
2249 --
2250 -- Bug#5482191 Fetch the PSI Value
2251 l_pay_calc_data.personnel_system_indicator := ghr_pa_requests_pkg.get_personnel_system_indicator
2252 (l_pay_calc_data.position_id,l_pay_calc_data.effective_date); -- MSL Percentage Changes Madhuri
2253 -- Bug#4758111 PRD 2 Processing.
2254 --- if nvl(ghr_msl_pkg.g_ses_msl_process,'N') <> 'Y' then
2255 if p_open_range_out_basic_pay is null and l_noa_code <> '890' then
2256 IF l_noa_code = '892' AND
2257 l_effective_date < to_date('2007/01/07','YYYY/MM/DD') THEN
2258 IF espayplan%isopen THEN
2259 CLOSE espayplan;
2260 END IF;
2261
2262 OPEN espayplan;
2263 FETCH espayplan into esplan_flag;
2264 IF espayplan%FOUND THEN
2265 CLOSE espayplan;
2266 hr_utility.set_message(8301, 'GHR_38223_INV_PAY_PLAN_892');
2267 hr_utility.raise_error;
2268 END IF;
2269 CLOSE espayplan;
2270 END IF;
2271 ---Bug 7557159 Start
2272 IF p_pay_rate_determinant = 'D' THEN
2273 hr_utility.set_message(8301, 'GHR_38520_PRD_D');
2274 raise ghr_pay_calc.open_pay_range_mesg;
2275 END IF;
2276 ---Bug 7557159 End
2277 if get_open_pay_range ( l_position_id
2278 , p_person_id
2279 , l_pay_rate_determinant
2280 , p_pa_request_id
2281 , NVL(p_effective_date,TRUNC(sysdate)) ) then
2282 --6489042 during appointment of ESSL employees of PRD 2 enabling basic pay for user entry
2283 if p_pay_rate_determinant <> '2' then
2284 hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2285 raise ghr_pay_calc.open_pay_range_mesg;
2286 elsif p_pay_rate_determinant = '2' and l_noa_family_code IN ('APP') then
2287 hr_utility.set_message(8301, 'GHR_38713_OPEN_PAY_RANGE');
2288 raise ghr_pay_calc.open_pay_range_mesg;
2289 end if;
2290 end if;
2291 end if;
2292 --- end if;
2293 --
2294
2295 -- FWFA Modification: Moved the get_retained_grade_details call from get_basic_pay procedure
2296 -- to this position.
2297 IF l_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V','M') THEN
2298 BEGIN
2299 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (l_pay_calc_data.person_id
2300 ,l_pay_calc_data.effective_date
2301 ,l_pay_calc_data.pa_request_id);
2302 EXCEPTION
2303 WHEN OTHERS THEN
2304 RAISE;
2305 END;
2306 END IF;
2307 -- FWFA Modification
2308
2309 -- FWFA Changes.
2310 --6489042 during appointment of ESSL employees of PRD 2 not to handle the existing functionality of
2311 -- copying the already existing values
2312 IF p_pay_rate_determinant = '2' and l_noa_family_code NOT IN ('APP') THEN
2313
2314 l_pay_calc_out_data.basic_pay := l_pay_calc_data.current_basic_pay;
2315 l_pay_calc_out_data.adj_basic_pay := l_pay_calc_data.current_adj_basic_pay;
2316 l_pay_calc_out_data.locality_adj := l_pay_calc_out_data.adj_basic_pay - l_pay_calc_out_data.basic_pay;
2317 -- Bug#5349609 Added the other pay components values to the out variables
2318 l_pay_calc_out_data.retention_allowance := l_pay_calc_data.retention_allowance;
2319 l_pay_calc_out_data.au_overtime := get_ppi_amount (l_pay_calc_data.auo_premium_pay_indicator
2320 ,l_pay_calc_out_data.adj_basic_pay
2321 ,l_pay_calc_data.pay_basis);
2322 l_pay_calc_out_data.availability_pay := get_ppi_amount (l_pay_calc_data.ap_premium_pay_indicator
2323 ,l_pay_calc_out_data.adj_basic_pay
2324 ,l_pay_calc_data.pay_basis);
2325 hr_utility.set_location('test asg id: '||l_session.assignment_id,10);
2326 hr_utility.set_location(' test eff dt: '||l_pay_calc_data.effective_date,10);
2327 FOR asg_rec IN c_asg_rec(l_pay_calc_data.person_id, l_pay_calc_data.effective_date)
2328 LOOP
2329 l_assignment_id := asg_rec.assignment_id;
2330 exit;
2331 END LOOP;
2332 l_capped_other_pay := ghr_pa_requests_pkg2.get_cop(l_assignment_id,
2333 l_pay_calc_data.effective_date);
2334
2335 -- Bug#5349609 If capped other pay is null, get the other pay value.
2336 IF l_capped_other_pay is NULL THEN
2337 get_other_pay_amount (l_pay_calc_data, l_pay_calc_out_data);
2338 get_total_salary (l_pay_calc_out_data,l_pay_calc_data.pay_basis);
2339 ELSE
2340 l_pay_calc_out_data.total_salary := NVL(l_pay_calc_out_data.adj_basic_pay,0) +
2341 NVL(l_capped_other_pay,0);
2342
2343 END IF;
2344 ELSE
2345 -- Bug#4758111
2346 IF fwfa_pay_calc(l_pay_calc_data,l_retained_grade) THEN
2347 special_rate_pay_calc (p_pay_calc_data => l_pay_calc_data
2348 ,p_pay_calc_out_data => l_pay_calc_out_data
2349 ,p_retained_grade => l_retained_grade
2350 );
2351 --Begin Bug 7046241
2352 IF l_pay_plan = 'GP' then
2353 --Setting process methods for GP pay plan
2354 l_pay_calc_out_data.open_localityadj_field := TRUE;
2355 If (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2356 and (l_first_action_la_code1 <> 'ZLM') then
2357 l_pay_calc_out_data.open_localityadj_field := FALSE;
2358 End If;
2359 If (l_pay_calc_data.noa_code = '893' OR l_pay_calc_data.second_noa_code = '893') then
2360 l_pay_calc_out_data.open_localityadj_field := FALSE;
2361 End If;
2362 --Restoring Market pay since market pay should not change because of special_rate_pay_calc
2363 if p_open_out_locality_adj is not null then
2364 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2365 l_pay_calc_out_data.adj_basic_pay := l_pay_calc_out_data.locality_adj +
2366 l_pay_calc_out_data.basic_pay;
2367 end if;
2368 ELSIF l_pay_plan = 'GR' then --IF l_pay_plan = 'GP'
2369 hr_utility.set_location('Setting process methods for GP pay plan...',555);
2370 get_locality_adj (l_pay_calc_data, l_retained_grade, l_pay_calc_out_data);
2371 If (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2372 and (l_first_action_la_code1 = 'ZLM') then
2373 l_pay_calc_out_data.open_localityadj_field := TRUE;
2374 END IF;
2375 --Restoring Market pay since market pay should not change because of special_rate_pay_calc
2376 if p_open_out_locality_adj is not null then
2377 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2378 l_pay_calc_out_data.adj_basic_pay := l_pay_calc_out_data.locality_adj +
2379 l_pay_calc_out_data.basic_pay;
2380 end if;
2381 END IF; --IF l_pay_plan = 'GP'
2382 --End Bug 7046241
2383 ELSE
2384
2385 ------GPPA Update 46 start
2386 IF (l_pay_calc_data.noa_code = '890' OR l_pay_calc_data.second_noa_code = '890') AND
2387 l_pay_calc_data.pay_plan = 'GM' then
2388 l_pay_calc_data.pay_plan := 'GS';
2389 l_default_pay_plan := 'GS';
2390 l_grade_id := NULL;
2391 FOR get_grade_id_rec IN get_grade_id(l_default_pay_plan, l_pay_calc_data.grade_or_level)
2392 LOOP
2393 l_grade_id := get_grade_id_rec.grade_id;
2394 l_pay_plan_changed := TRUE;
2395 g_pay_table_upd_flag := TRUE;
2396 exit;
2397 END LOOP;
2398 END IF;
2399 ------------------------------ 1) Get basic_pay --------------------------------------------------------
2400 --
2401 hr_utility.set_location('Calling main basic pay...'|| l_proc,5);
2402 -- MSL percentage Changes Madhuri 3843306
2403
2404 -- if nvl(ghr_msl_pkg.g_ses_msl_process,'N') = 'Y' then
2405 -- l_pay_calc_out_data.basic_pay := nvl(l_pay_calc_data.current_adj_basic_pay,0);
2406 --else
2407 ghr_pc_basic_pay.get_basic_pay (l_pay_calc_data, l_pay_calc_out_data, l_retained_grade);
2408 --end if;
2409 -- FWFA Changes. Bug#4444609 Setting the Calculation Pay Table in case of non-fwfa calculations.
2410 IF l_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V','M') AND
2411 l_retained_grade.temp_step IS NULL THEN
2412 l_pay_calc_out_data.calculation_pay_table_id := l_retained_grade.user_table_id;
2413 l_pay_calc_out_data.pay_table_id := l_retained_grade.user_table_id;
2414 l_pay_plan := l_retained_grade.pay_plan;
2415 ELSE
2416 l_pay_calc_out_data.calculation_pay_table_id := l_pay_calc_data.user_table_id;
2417 l_pay_calc_out_data.pay_table_id := l_pay_calc_data.user_table_id;
2418 l_pay_plan := l_pay_calc_data.pay_plan;
2419 -- Bug#4748583 Added the following IF Condition to set the PRD to '6' for 0491 table.
2420 -- Bug#5089732 GL Pay Plan
2421 IF get_user_table_name(l_pay_calc_out_data.pay_table_id) = ghr_pay_calc.l_spl491_table_name AND
2422 (l_pay_calc_data.pay_plan <> 'GL' OR
2423 (l_pay_plan = 'GG' and
2424 l_pay_calc_data.effective_date < to_date('2006/01/08','YYYY/MM/DD')
2425 )
2426 ) AND
2427 l_pay_calc_data.pay_rate_determinant = '0' THEN
2428 l_pay_calc_out_data.out_pay_rate_determinant := '6';
2429 END IF;
2430 -- Bug#4748583
2431 END IF;
2432 --Bug#5089732 Set the Pay Rate Determinant for GL,GG Pay Plans
2433 IF get_user_table_name(l_pay_calc_out_data.calculation_pay_table_id) = ghr_pay_calc.l_spl491_table_name AND
2434 (l_pay_plan = 'GL' OR
2435 (l_pay_plan = 'GG' and
2436 l_pay_calc_data.effective_date >= to_date('2006/01/08','YYYY/MM/DD')
2437 )
2438 )AND
2439 l_pay_calc_data.pay_rate_determinant IN ('6','E','F') THEN
2440 IF l_pay_calc_data.pay_rate_determinant = '6' THEN
2441 l_pay_calc_out_data.out_pay_rate_determinant := '0';
2442 ELSIF l_pay_calc_data.pay_rate_determinant = 'E' THEN
2443 l_pay_calc_out_data.out_pay_rate_determinant := 'A';
2444 ELSE
2445 l_pay_calc_out_data.out_pay_rate_determinant := 'B';
2446 END IF;
2447 --Bug#5435217 Set this flag to true to avoid unnecessary creation of RG record.
2448 g_gl_upd_flag := TRUE;
2449 END IF;
2450 -- FWFA Changes
2451 ------------------------------ 2) Get locality_adj --------------------------------------------------------
2452 --
2453 -- Start of Bug #5132113
2454 IF l_pay_plan = 'GP' then
2455 l_pay_calc_out_data.open_localityadj_field := TRUE;
2456
2457 --Bug #6344900 locality adjustment should not be opened for 894 and lac codes other than ZLM
2458 -- and 893 actions
2459 If (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2460 and (l_first_action_la_code1 <> 'ZLM') then
2461 l_pay_calc_out_data.open_localityadj_field := FALSE;
2462 End If;
2463 If (l_pay_calc_data.noa_code = '893' OR l_pay_calc_data.second_noa_code = '893') then
2464 l_pay_calc_out_data.open_localityadj_field := FALSE;
2465 End If;
2466
2467
2468 if p_open_out_locality_adj is not null then
2469 l_pay_calc_out_data.locality_adj := p_open_out_locality_adj;
2470 end if;
2471 ELSE
2472 hr_utility.set_location('Calling main locality...'|| l_proc,5);
2473 get_locality_adj (l_pay_calc_data, l_retained_grade, l_pay_calc_out_data);
2474 --Bug#5132113 for GR payplan and 894 open locality pay for user modification
2475 If (l_pay_plan = 'GR') AND
2476 (l_pay_calc_data.noa_code = '894' OR l_pay_calc_data.second_noa_code = '894')
2477 and (l_first_action_la_code1 = 'ZLM') then
2478 l_pay_calc_out_data.open_localityadj_field := TRUE;
2479 END IF;
2480 --Bug#5132113
2481
2482 hr_utility.set_location('Calling main locality..main locality pay .'||
2483 to_char(l_pay_calc_out_data.locality_adj),5);
2484 END IF;
2485 --
2486 ------------------------------ 3) Get adj_basic_pay -----------------------------------------------------
2487 --
2488 hr_utility.set_location('Calling main adj basic...'|| l_proc,5);
2489 get_adj_basic_pay (l_pay_calc_data, l_pay_calc_out_data);
2490 hr_utility.set_location('Calling main adj basic..adj basic Pay.'||
2491 to_char(l_pay_calc_out_data.adj_basic_pay),5);
2492 END IF;
2493
2494 IF g_pay_table_upd_flag THEN
2495 hr_utility.set_location('After Basic/loc/adj basic calc. Pay Table UPD Flag TRUE',101);
2496 ELSE
2497 hr_utility.set_location('After Basic/loc/adj basic Calc. Pay Table UPD Flag FALSE',102);
2498 END IF;
2499 --
2500
2501 ------------------------------ 4.0) Get retention_allowance----------------------------------------------
2502 --
2503 ---- Calcultion of Retention Allowance.
2504 ---
2505 if p_noa_family_code = 'CONV_APP' and l_ex_emp THEN
2506 l_retention_allowance := null;
2507 l_to_ret_allow_percentage := null;
2508 end if;
2509
2510 if l_retention_allowance is not null then
2511 if nvl(l_pay_calc_data.current_basic_pay,0) <> nvl(l_pay_calc_out_data.basic_pay,0) then
2512
2513 if l_to_ret_allow_percentage is null then
2514 if (l_session.noa_id_correct is not null ) or (p_noa_family_code = 'CORRECT') then
2515 hr_utility.set_location(' perc null Inside correct ' || l_proc,5);
2516 hr_utility.set_location(' current_basic pay = ' || to_char(l_pay_calc_data.current_basic_pay),5);
2517 if nvl(l_old_ret_allow,0) = nvl(l_from_retention_allowance,0) then
2518 if nvl(l_from_basic_pay,0) <> nvl(l_pay_calc_data.current_basic_pay,0) then
2519 l_from_basic_pay := l_pay_calc_data.current_basic_pay;
2520 end if;
2521 end if;
2522 hr_utility.set_location('From retention' || l_from_retention_allowance,2000);
2523 hr_utility.set_location('From l_from_basic_pay' || l_from_basic_pay,2000);
2524 hr_utility.set_location('From l_from_pay_basis' || l_from_pay_basis,2000);
2525
2526 ---- Changed for FWS
2527
2528 l_ret_calc_perc := (l_from_retention_allowance / l_from_basic_pay )* 100;
2529
2530 -- Bug 3245692 . Replaced l_pay_basis with l_from_pay_basis in the above statement
2531 hr_utility.set_location(' Ret Percentage = ' || to_char(l_ret_calc_perc),5);
2532 else
2533 hr_utility.set_location(' perc null else correct ' || l_proc,5);
2534 ----
2535 ---- Bug 3218346 --
2536 ----
2537
2538 l_ret_calc_perc := nvl((l_pay_calc_data.retention_allowance /
2539 l_pay_calc_data.current_basic_pay) * 100,0);
2540 -- Bug 3245692
2541 hr_utility.set_location(' else Ret Percentage = ' || to_char(l_ret_calc_perc),5);
2542 end if;
2543
2544 if nvl(l_ret_calc_perc,0) > 25 then
2545 l_ret_calc_perc := 25;
2546 end if;
2547 else -- l_to_ret_allow_percentage is not null
2548 l_ret_calc_perc := l_to_ret_allow_percentage;
2549 hr_utility.set_location(' Passed Percentage l_ret_calc_perc = ' || to_char(l_ret_calc_perc),5);
2550 end if; -- if nvl(l_ret_calc_perc,0) > 2
2551
2552
2553 /*IF l_pay_basis ='PH' THEN
2554 l_to_retention_allowance := TRUNC(l_pay_calc_out_data.basic_pay * l_ret_calc_perc / 100 ,2);
2555
2556 ELSE
2557 l_to_retention_allowance := TRUNC(l_pay_calc_out_data.basic_pay * l_ret_calc_perc / 100 ,0);
2558
2559 END IF;
2560 --Changed for FWS*/
2561
2562 -- Rounding of RETENTION ALLOWANCE STARTS
2563 ---3843316
2564 --
2565 -- BUG# 4689374 For FWFA if % is not entered, then take only the amount
2566 IF p_pay_rate_determinant IN ('3','4','J','K','U','V') AND
2567 p_effective_date >= to_date('01/05/2005','dd/mm/yyyy') AND
2568 l_to_ret_allow_percentage IS NULL THEN
2569 l_to_retention_allowance := l_retention_allowance;
2570 ELSE
2571 IF l_pay_basis ='PH' THEN
2572 l_to_retention_allowance := round(l_pay_calc_out_data.basic_pay *(l_ret_calc_perc/100),2);
2573 IF ( l_ret_calc_perc between 24 and 25) THEN
2574 l_max_RA:= trunc((l_pay_calc_out_data.basic_pay*0.25),2);
2575 IF (l_to_retention_allowance > l_max_ra) THEN
2576 l_to_retention_allowance:= l_max_ra;
2577 END IF;
2578 END IF;
2579 ELSIF l_pay_basis ='PA' THEN
2580 l_max_RA:= trunc((l_pay_calc_out_data.basic_pay*0.25),0);
2581 l_to_retention_allowance := round(l_pay_calc_out_data.basic_pay*(l_ret_calc_perc/100),0);
2582 if l_to_retention_allowance > l_max_RA then
2583 l_to_retention_allowance := l_max_RA;
2584 end if;
2585 ELSE
2586 l_to_retention_allowance := TRUNC(l_pay_calc_out_data.basic_pay * l_ret_calc_perc / 100 ,0);
2587 END IF;
2588 END IF;
2589
2590 --- 3843316
2591 -- Rounding of RETENTION ALLOWANCE ENDS
2592
2593 hr_utility.set_location(' Calc retention_allowance = ' || to_char(l_to_retention_allowance),5);
2594 hr_utility.set_location(' Calc-Pass ret_allow_percentage = ' || to_char(l_ret_calc_perc),5);
2595 hr_utility.set_location(' Supervisory Differentail = ' || to_char(l_supervisory_differential),5);
2596 else
2597 hr_utility.set_location(' Basic Pays are same ' || l_proc,5);
2598 if ( l_session.noa_id_correct is not null ) or ( p_noa_family_code = 'CORRECT' ) then
2599 hr_utility.set_location(' Basic Pays are same - Inside Correct ' || l_proc,5);
2600 if l_from_basic_pay = nvl(l_pay_calc_data.current_basic_pay,0) then
2601 l_to_retention_allowance := l_pay_calc_data.retention_allowance;
2602 else
2603 l_to_retention_allowance := l_old_ret_allow;
2604 end if;
2605 hr_utility.set_location(' BPS correct retention_allowance = ' || to_char(l_to_retention_allowance),5);
2606 else
2607 l_to_retention_allowance := l_pay_calc_data.retention_allowance;
2608 hr_utility.set_location(' BPS else retention_allowance = ' || to_char(l_to_retention_allowance),5);
2609 end if;
2610 end if;
2611 end if;
2612 l_pay_calc_out_data.retention_allowance := l_to_retention_allowance;
2613
2614 ------------------------------ 4.1) Get au_overtime------------------------------------------------------
2615 --
2616 l_pay_calc_out_data.au_overtime := get_ppi_amount (l_pay_calc_data.auo_premium_pay_indicator
2617 ,l_pay_calc_out_data.adj_basic_pay
2618 ,l_pay_calc_data.pay_basis);
2619 --
2620 ------------------------------ 4.2) Get avalabilty_pay ------------------------------------------------------
2621 --
2622 l_pay_calc_out_data.availability_pay := get_ppi_amount (l_pay_calc_data.ap_premium_pay_indicator
2623 ,l_pay_calc_out_data.adj_basic_pay
2624 ,l_pay_calc_data.pay_basis);
2625 --
2626 ------------------------------ 5) Get other_pay_amount ------------------------------------------------------
2627 --
2628 get_other_pay_amount (l_pay_calc_data, l_pay_calc_out_data);
2629 --
2630 ------------------------------ 6) Get_total_salary ------------------------------------------------------
2631 --
2632 get_total_salary (l_pay_calc_out_data,l_pay_calc_data.pay_basis);
2633 --
2634 -------------------------------------------------------------------------------------------------------
2635 --
2636 END IF; -- End of PRD 2 Processing IF Condition.
2637 ----GPPA Update 46
2638 IF l_pay_plan_changed THEN
2639 IF nvl(l_pay_calc_out_data.out_pay_rate_determinant,'X') in ('A','B','E','F','U','V') AND
2640 l_retained_grade.temp_step IS NULL THEN
2641 g_out_to_pay_plan := l_default_pay_plan;
2642 ELSIF l_grade_id is not null then
2643 l_pay_calc_out_data.out_to_grade_id := l_grade_id;
2644 l_pay_calc_out_data.out_to_pay_plan := l_default_pay_plan;
2645 l_pay_calc_out_data.out_to_grade_or_level := l_pay_calc_data.grade_or_level;
2646 END IF;
2647 END IF;
2648 -- If we got here we haven't set any messages and we think we calculated it!!
2649 -- Even though we calculated everything pass it on to the cutstom call incase they want to
2650 -- overwrite it!!
2651 ---Bug 7423379 Start (Fix was revoked - AVR 10/24)
2652 ----IF l_pay_plan = 'AD' THEN
2653 ---- l_pay_calc_out_data.open_pay_fields := TRUE;
2654 ----END IF;
2655 ---Bug 7423379 End
2656 ghr_custom_pay_calc.custom_pay_calc
2657 (l_pay_calc_data
2658 ,l_pay_calc_out_data
2659 ,l_message_set
2660 ,l_calculated);
2661 EXCEPTION
2662 WHEN open_pay_range_mesg THEN
2663 -- set calculated to false and let the customer attempt to calculate it!
2664 -- if the user enters the validated basic pay then pay will be calculated.
2665 l_message_set := TRUE;
2666 l_calculated := FALSE;
2667 l_pay_calc_out_data.open_basicpay_field := TRUE;
2668 -- Bug#4758111 Removed PRD 2 from the following list.
2669 if p_pay_rate_determinant
2670 in ( '3','4','C','J','K','M','P','R','S','U','V') then
2671 l_pay_calc_out_data.open_pay_fields := TRUE;
2672 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
2673 hr_utility.set_message_token('PRD',p_pay_rate_determinant);
2674 end if;
2675 ---Bug 7423379 Start (Fix was revoked - AVR 10/24)
2676 ----IF l_pay_plan = 'AD' THEN
2677 ---- l_pay_calc_out_data.open_pay_fields := TRUE;
2678 ----END IF;
2679 ---Bug 7423379 End
2680 ghr_custom_pay_calc.custom_pay_calc
2681 (l_pay_calc_data
2682 ,l_pay_calc_out_data
2683 ,l_message_set
2684 ,l_calculated);
2685 WHEN unable_to_calculate THEN
2686 -- set calculated to false and let the customer attempt to calculate it!
2687 -- if the user calculates it they need to make sure the pass back out p_calculated = TRUE
2688 -- otherwise we will assume they didn't do it either!!
2689 -- FWFA Changes. Bug#4444609 Setting the Calculation Pay Table in case of non-fwfa calculations.
2690 IF NOT (g_fwfa_pay_calc_flag) THEN
2691 IF l_pay_calc_data.pay_rate_determinant IN ('U','V') AND
2692 l_retained_grade.temp_step IS NULL THEN
2693 l_pay_calc_out_data.calculation_pay_table_id := l_retained_grade.user_table_id;
2694 l_pay_calc_out_data.pay_table_id := l_retained_grade.user_table_id;
2695 ELSIF l_pay_calc_data.pay_rate_determinant IN ('3','4','J','K') THEN
2696 l_pay_calc_out_data.calculation_pay_table_id := l_pay_calc_data.user_table_id;
2697 l_pay_calc_out_data.pay_table_id := l_pay_calc_data.user_table_id;
2698 END IF;
2699 END IF;
2700 l_message_set := TRUE;
2701 l_calculated := FALSE;
2702 l_pay_calc_out_data.open_pay_fields := TRUE;
2703 ghr_custom_pay_calc.custom_pay_calc
2704 (l_pay_calc_data
2705 ,l_pay_calc_out_data
2706 ,l_message_set
2707 ,l_calculated);
2708 WHEN ghr_pay_calc.pay_calc_message THEN
2709 l_message_set := TRUE;
2710 l_calculated := TRUE;
2711 ---Bug 7423379 Start (Fix was revoked - AVR 10/24)
2712 ----IF l_pay_plan = 'AD' THEN
2713 ---- l_pay_calc_out_data.open_pay_fields := TRUE;
2714 ----END IF;
2715 ---Bug 7423379 End
2716 ghr_custom_pay_calc.custom_pay_calc
2717 (l_pay_calc_data
2718 ,l_pay_calc_out_data
2719 ,l_message_set
2720 ,l_calculated);
2721
2722 END;
2723 -- In one circumstance we may need to run pay calc again! i.e. Dual Action: 893 / 892
2724 IF l_run_pay_calc_again THEN
2725 main_pay_calc (p_person_id
2726 ,p_position_id
2727 ,l_second_noa_family_code
2728 ,p_second_noa_code
2729 ,p_first_action_la_code1
2730 ,null
2731 ,p_effective_date
2732 ,p_pay_rate_determinant
2733 ,p_pay_plan
2734 ,p_grade_or_level
2735 ,p_step_or_rate
2736 ,p_pay_basis
2737 ,p_user_table_id
2738 ,p_duty_station_id
2739 ,p_auo_premium_pay_indicator
2740 ,p_ap_premium_pay_indicator
2741 ,p_retention_allowance
2742 ,p_to_ret_allow_percentage
2743 ,p_supervisory_differential
2744 ,p_staffing_differential
2745 ,p_current_basic_pay
2746 ,p_current_adj_basic_pay
2747 ,l_pay_calc_out_data.out_step_or_rate
2748 ,p_pa_request_id
2749 ,p_open_range_out_basic_pay
2750 ,p_open_out_locality_adj
2751 ,l_pay_calc_out_data.basic_pay
2752 ,l_pay_calc_out_data.locality_adj
2753 ,l_pay_calc_out_data.adj_basic_pay
2754 ,l_pay_calc_out_data.total_salary
2755 ,l_pay_calc_out_data.other_pay_amount
2756 ,l_pay_calc_out_data.retention_allowance
2757 ,l_pay_calc_out_data.ret_allow_perc_out
2758 ,l_pay_calc_out_data.au_overtime
2759 ,l_pay_calc_out_data.availability_pay
2760 -- FWFA Changes
2761 ,l_pay_calc_out_data.calculation_pay_table_id
2762 ,l_pay_calc_out_data.pay_table_id
2763 -- FWFA Changes
2764 ,l_pay_calc_out_data.out_step_or_rate
2765 ,l_pay_calc_out_data.out_pay_rate_determinant
2766 ,l_pay_calc_out_data.out_to_grade_id
2767 ,l_pay_calc_out_data.out_to_pay_plan
2768 ,l_pay_calc_out_data.out_to_grade_or_level
2769 ,l_pay_calc_out_data.PT_eff_start_date
2770 ,l_pay_calc_out_data.open_basicpay_field
2771 ,l_pay_calc_out_data.open_pay_fields
2772 ,l_message_set
2773 ,l_calculated
2774 ,l_pay_calc_out_data.open_localityadj_field);
2775 END IF;
2776
2777 if get_open_pay_range ( l_position_id
2778 , p_person_id
2779 , l_pay_rate_determinant
2780 , p_pa_request_id
2781 , NVL(p_effective_date,TRUNC(sysdate)) ) then
2782 --6489042 during appointment of ESSL employees of PRD 2 enabling basic pay for user entry
2783 if (p_pay_rate_determinant <> '2') or (p_pay_rate_determinant = '2' and l_noa_family_code IN ('APP')) then
2784 l_pay_calc_out_data.open_basicpay_field := TRUE;
2785 end if;
2786 end if;
2787
2788 --bug#5132113
2789 /* if p_open_out_locality_adj is null then
2790 l_pay_calc_out_data.open_localityadj_field := TRUE;
2791 end if;*/
2792 --Begin Bug# 7557159
2793 IF l_pay_plan='GS' AND l_pay_rate_determinant='D' THEN
2794 l_prd_d_pay_amount := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => l_pay_plan
2795 ,p_grade_or_level => l_grade_or_level
2796 ,p_step_or_rate => '10'
2797 ,p_effective_date => p_effective_date);
2798
2799 IF l_prd_d_pay_amount < l_pay_calc_out_data.basic_pay THEN
2800 l_pay_calc_out_data.out_step_or_rate := '00';
2801 l_pay_calc_out_data.open_basicpay_field := TRUE;
2802 END IF;
2803 END IF;
2804 --End Bug# 7557159
2805 -- always set the out parameters
2806 l_pay_calc_out_data.ret_allow_perc_out := l_to_ret_allow_percentage;
2807
2808 p_basic_pay := l_pay_calc_out_data.basic_pay;
2809 p_locality_adj := l_pay_calc_out_data.locality_adj;
2810 p_adj_basic_pay := l_pay_calc_out_data.adj_basic_pay;
2811 p_total_salary := l_pay_calc_out_data.total_salary;
2812 p_other_pay_amount := l_pay_calc_out_data.other_pay_amount;
2813 p_to_retention_allowance := l_pay_calc_out_data.retention_allowance;
2814 p_ret_allow_perc_out := l_pay_calc_out_data.ret_allow_perc_out;
2815 p_au_overtime := l_pay_calc_out_data.au_overtime;
2816 p_availability_pay := l_pay_calc_out_data.availability_pay;
2817 -- FWFA Changes
2818 p_calc_pay_table_id := l_pay_calc_out_data.calculation_pay_table_id;
2819 p_pay_table_id := l_pay_calc_out_data.pay_table_id;
2820 p_out_to_grade_id := l_pay_calc_out_data.out_to_grade_id;
2821 p_out_to_pay_plan := l_pay_calc_out_data.out_to_pay_plan;
2822 p_out_to_grade_or_level := l_pay_calc_out_data.out_to_grade_or_level;
2823 -- FWFA Changes
2824 p_out_step_or_rate := l_pay_calc_out_data.out_step_or_rate;
2825 p_out_pay_rate_determinant := l_pay_calc_out_data.out_pay_rate_determinant;
2826 p_PT_eff_start_date := l_pay_calc_out_data.PT_eff_start_date;
2827 p_open_basicpay_field := l_pay_calc_out_data.open_basicpay_field;
2828 p_open_pay_fields := l_pay_calc_out_data.open_pay_fields;
2829 p_message_set := l_message_set;
2830 p_calculated := l_calculated;
2831
2832 --Bug#5132113
2833 p_open_localityadj_field := l_pay_calc_out_data.open_localityadj_field;
2834
2835
2836 EXCEPTION
2837 WHEN others THEN
2838 -- Reset IN OUT parameters and set OUT parameters
2839 p_basic_pay := NULL;
2840 p_locality_adj := NULL;
2841 p_adj_basic_pay := NULL;
2842 p_total_salary := NULL;
2843 p_other_pay_amount := NULL;
2844 p_to_retention_allowance := NULL;
2845 p_ret_allow_perc_out := NULL;
2846 p_au_overtime := NULL;
2847 p_availability_pay := NULL;
2848 -- FWFA Changes
2849 p_calc_pay_table_id := NULL;
2850 p_pay_table_id := NULL;
2851 -- FWFA Changes
2852 p_out_step_or_rate := NULL;
2853 p_out_pay_rate_determinant := NULL;
2854 p_out_to_grade_id := NULL;
2855 p_out_to_pay_plan := NULL;
2856 p_out_to_grade_or_level := NULL;
2857 p_PT_eff_start_date := NULL;
2858 p_open_basicpay_field := NULL;
2859 p_open_pay_fields := NULL;
2860 p_message_set := NULL;
2861 p_calculated := NULL;
2862 p_open_localityadj_field := NULL;
2863 RAISE;
2864 END main_pay_calc;
2865
2866
2867 PROCEDURE sql_main_pay_calc (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
2868 ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
2869 ,p_message_set OUT NOCOPY BOOLEAN
2870 ,p_calculated OUT NOCOPY BOOLEAN
2871 ) IS
2872 l_message_set BOOLEAN;
2873 l_calculated BOOLEAN;
2874 BEGIN
2875
2876 main_pay_calc (p_pay_calc_data.person_id
2877 ,p_pay_calc_data.position_id
2878 ,p_pay_calc_data.noa_family_code
2879 ,p_pay_calc_data.noa_code
2880 ,p_pay_calc_data.second_noa_code
2881 ,p_pay_calc_data.first_action_la_code1
2882 ,p_pay_calc_data.effective_date
2883 ,p_pay_calc_data.pay_rate_determinant
2884 ,p_pay_calc_data.pay_plan
2885 ,p_pay_calc_data.grade_or_level
2886 ,p_pay_calc_data.step_or_rate
2887 ,p_pay_calc_data.pay_basis
2888 ,p_pay_calc_data.user_table_id
2889 ,p_pay_calc_data.duty_station_id
2890 ,p_pay_calc_data.auo_premium_pay_indicator
2891 ,p_pay_calc_data.ap_premium_pay_indicator
2892 ,p_pay_calc_data.retention_allowance
2893 ,p_pay_calc_data.to_ret_allow_percentage
2894 ,p_pay_calc_data.supervisory_differential
2895 ,p_pay_calc_data.staffing_differential
2896 ,p_pay_calc_data.current_basic_pay
2897 ,p_pay_calc_data.current_adj_basic_pay
2898 ,p_pay_calc_data.current_step_or_rate
2899 ,p_pay_calc_data.pa_request_id
2900 ,p_pay_calc_data.open_range_out_basic_pay
2901 --Bug5132113
2902 ,p_pay_calc_data.open_out_locality_adj
2903 ,p_pay_calc_out_data.basic_pay
2904 ,p_pay_calc_out_data.locality_adj
2905 ,p_pay_calc_out_data.adj_basic_pay
2906 ,p_pay_calc_out_data.total_salary
2907 ,p_pay_calc_out_data.other_pay_amount
2908 ,p_pay_calc_out_data.retention_allowance
2909 ,p_pay_calc_out_data.ret_allow_perc_out
2910 ,p_pay_calc_out_data.au_overtime
2911 ,p_pay_calc_out_data.availability_pay
2912 -- FWFA Changes
2913 ,p_pay_calc_out_data.Calculation_pay_table_id
2914 ,p_pay_calc_out_data.pay_table_id
2915 -- FWFA Changes
2916 ,p_pay_calc_out_data.out_step_or_rate
2917 ,p_pay_calc_out_data.out_pay_rate_determinant
2918 ,p_pay_calc_out_data.out_to_grade_id
2919 ,p_pay_calc_out_data.out_to_pay_plan
2920 ,p_pay_calc_out_data.out_to_grade_or_level
2921 ,p_pay_calc_out_data.PT_eff_start_date
2922 ,p_pay_calc_out_data.open_basicpay_field
2923 ,p_pay_calc_out_data.open_pay_fields
2924 ,l_message_set
2925 ,l_calculated
2926 ,p_pay_calc_out_data.open_localityadj_field);
2927
2928 p_message_set := l_message_set;
2929 p_calculated := l_calculated;
2930
2931
2932 IF l_message_set and l_calculated THEN
2933 hr_utility.raise_error;
2934 END IF;
2935
2936 EXCEPTION
2937 WHEN others THEN
2938 -- Reset IN OUT parameters and set OUT parameters
2939 p_message_set := NULL;
2940 p_calculated := NULL;
2941 p_pay_calc_out_data := NULL;
2942 RAISE;
2943 END sql_main_pay_calc;
2944
2945 --
2946 FUNCTION get_pos_pay_basis (p_position_id IN per_positions.position_id%TYPE
2947 ,p_effective_date IN date)
2948 RETURN VARCHAR2 IS
2949 --
2950 -- Since Position Extra Info now has history use the history packages written to get
2951 -- the user_table_id
2952 l_pos_ei_data per_position_extra_info%ROWTYPE;
2953 --
2954 --
2955 BEGIN
2956 ghr_history_fetch.fetch_positionei(
2957 p_position_id => p_position_id
2958 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
2959 ,p_date_effective => p_effective_date
2960 ,p_pos_ei_data => l_pos_ei_data);
2961 --
2962 RETURN(l_pos_ei_data.poei_information6);
2963 --
2964 END get_pos_pay_basis;
2965 --
2966
2967 --AVR
2968 PROCEDURE get_locality_adj_894_PRDM_GS (p_user_table_id IN NUMBER
2969 ,p_pay_plan IN VARCHAR2
2970 ,p_grade_or_level IN VARCHAR2
2971 ,p_step_or_rate IN VARCHAR2
2972 ,p_effective_date IN DATE
2973 ,p_cur_adj_basic_pay IN NUMBER
2974 ,p_new_basic_pay IN NUMBER
2975 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
2976 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
2977 --
2978 -- Local variables
2979 --
2980
2981 l_PT_value NUMBER;
2982 l_PT_eff_start_date DATE;
2983 l_PT_eff_end_date DATE;
2984 l_PT_o_value NUMBER;
2985 l_PT_o_eff_start_date DATE;
2986 l_PT_o_eff_end_date DATE;
2987 l_SPT_value NUMBER;
2988 l_SPT_o_value NUMBER;
2989 l_A NUMBER;
2990 l_B NUMBER;
2991 lesser_amt NUMBER;
2992 new_adj_basic_pay NUMBER;
2993 new_locality_adj NUMBER;
2994
2995 l_proc VARCHAR2(30) := '894_PRDM_GS';
2996 begin
2997 hr_utility.set_location('Entering ...'|| l_proc,5);
2998 hr_utility.set_location('User_table_id ...'|| p_user_table_id,5);
2999 hr_utility.set_location('p_pay_plan ...'|| p_pay_plan,5);
3000 hr_utility.set_location('p_grade_or_level ...'|| p_grade_or_level,5);
3001 hr_utility.set_location('step_or_rate ...'|| p_step_or_rate,5);
3002 hr_utility.set_location('effective_date ...'|| to_char(p_effective_date,'DD-MON-YYYY'),5);
3003 hr_utility.set_location('In new current adj basic pay ...'|| to_char(p_cur_adj_basic_pay),5);
3004 hr_utility.set_location('In new basic pay ...'|| to_char(p_new_basic_pay),5);
3005
3006 ghr_pay_calc.get_pay_table_value (p_user_table_id => p_user_table_id
3007 ,p_pay_plan => p_pay_plan
3008 ,p_grade_or_level => p_grade_or_level
3009 ,p_step_or_rate => p_step_or_rate
3010 ,p_effective_date => p_effective_date
3011 ,p_PT_value => l_PT_value
3012 ,p_PT_eff_start_date => l_PT_eff_start_date
3013 ,p_PT_eff_end_date => l_PT_eff_end_date);
3014
3015 hr_utility.set_location('l_PT_value ...'|| to_char(l_PT_value),5);
3016 ghr_pay_calc.get_pay_table_value (p_user_table_id => p_user_table_id
3017 ,p_pay_plan => p_pay_plan
3018 ,p_grade_or_level => p_grade_or_level
3019 ,p_step_or_rate => p_step_or_rate
3020 ,p_effective_date => (l_PT_eff_start_date - 1)
3021 ,p_PT_value => l_PT_o_value
3022 ,p_PT_eff_start_date => l_PT_o_eff_start_date
3023 ,p_PT_eff_end_date => l_PT_o_eff_end_date);
3024
3025 hr_utility.set_location('l_PT_o_value ...'|| to_char(l_PT_o_value),5);
3026 l_SPT_value := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => p_pay_plan
3027 ,p_grade_or_level => p_grade_or_level
3028 ,p_step_or_rate => p_step_or_rate
3029 ,p_effective_date => p_effective_date);
3030
3031 hr_utility.set_location('l_SPT_value ...'|| to_char(l_SPT_value),5);
3032 l_SPT_o_value := ghr_pay_calc.get_standard_pay_table_value (p_pay_plan => p_pay_plan
3033 ,p_grade_or_level => p_grade_or_level
3034 ,p_step_or_rate => p_step_or_rate
3035 ,p_effective_date => (l_PT_eff_start_date -1) );
3036
3037 hr_utility.set_location('l_SPT_o_value ...'|| to_char(l_SPT_o_value),5);
3038
3039 l_A := l_PT_value - l_PT_o_value;
3040 hr_utility.set_location('l_A...'|| to_char(l_A),5);
3041 l_B := l_SPT_value - l_SPT_o_value;
3042 hr_utility.set_location('l_B...'|| to_char(l_B),5);
3043
3044 if l_A = l_B then
3045 lesser_amt := l_A;
3046 elsif l_A > l_B then
3047 lesser_amt := l_B;
3048 else
3049 lesser_amt := l_A;
3050 end if;
3051 hr_utility.set_location('lesser_amt...'|| to_char(lesser_amt),5);
3052
3053 new_adj_basic_pay := p_cur_adj_basic_pay + lesser_amt;
3054 new_locality_adj := new_adj_basic_pay - p_new_basic_pay;
3055
3056 p_new_adj_basic_pay := new_adj_basic_pay;
3057 p_new_locality_adj := new_locality_adj;
3058
3059 hr_utility.set_location('new_adj_basic_pay...'|| to_char(new_adj_basic_pay),5);
3060 hr_utility.set_location('new_locality_adj...'|| to_char(new_locality_adj),5);
3061
3062 hr_utility.set_location('Leaving ...'|| l_proc,5);
3063
3064 EXCEPTION
3065 WHEN others THEN
3066 -- Reset IN OUT parameters and set OUT parameters
3067 p_new_adj_basic_pay := NULL;
3068 p_new_locality_adj := NULL;
3069 RAISE;
3070
3071 end get_locality_adj_894_PRDM_GS;
3072
3073 PROCEDURE get_locality_adj_894_PRDM_GM
3074 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3075 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3076 ,p_new_std_relative_rate OUT NOCOPY NUMBER
3077 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3078 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3079
3080 l_std_user_table_id NUMBER;
3081 l_user_table_id NUMBER;
3082 l_user_table_name pay_user_tables.user_table_name%type;
3083 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
3084 l_new_std_relative_rate NUMBER;
3085
3086
3087 l_grade VARCHAR2(30);
3088 l_PT_eff_start_date DATE;
3089 l_7dp NUMBER;
3090
3091 l_std_min NUMBER;
3092 l_std_max NUMBER;
3093
3094 l_dummy_step VARCHAR2(30);
3095 l_dummy_date DATE;
3096 l_new_basic_pay NUMBER;
3097
3098 l_new_ret_basic_pay NUMBER;
3099 l_old_ret_basic_pay NUMBER;
3100 l_A NUMBER;
3101 l_B NUMBER;
3102 lesser_amt NUMBER;
3103 new_adj_basic_pay NUMBER;
3104 new_locality_adj NUMBER;
3105
3106 l_proc VARCHAR2(30) := '894_PRDM_GM';
3107
3108 CURSOR get_std_user_table_id IS
3109 SELECT utb.user_table_id
3110 FROM pay_user_tables utb
3111 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3112
3113 BEGIN
3114 hr_utility.set_location('Entering ...'|| l_proc,5);
3115 -- First get the id of standard pay table for later use
3116 FOR c_rec IN get_std_user_table_id LOOP
3117 l_std_user_table_id := c_rec.user_table_id;
3118 END LOOP;
3119
3120 IF p_retained_grade.grade_or_level IS NULL THEN
3121 l_grade := p_pay_calc_data.grade_or_level;
3122 l_user_table_id := p_pay_calc_data.user_table_id;
3123 ELSE
3124 l_grade := p_retained_grade.grade_or_level;
3125 l_user_table_id := p_retained_grade.user_table_id;
3126 END IF;
3127
3128 l_user_table_name := get_user_table_name(l_user_table_id);
3129 l_adjustment_percentage := get_lpa_percentage
3130 (p_pay_calc_data.duty_station_id
3131 ,p_pay_calc_data.effective_date);
3132
3133
3134 -- Get the 7 dp figure as calculated in the 6 step rule!
3135 ghr_pc_basic_pay.get_basic_pay_SAL894_6step(p_pay_calc_data
3136 ,p_retained_grade
3137 ,'POSITION'
3138 ,l_new_basic_pay
3139 ,l_PT_eff_start_date
3140 ,l_7dp);
3141 --
3142 l_A := l_new_basic_pay - p_pay_calc_data.current_basic_pay;
3143
3144 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3145 ,'GS'
3146 ,l_grade
3147 ,l_PT_eff_start_date
3148 ,l_dummy_step
3149 ,l_std_min
3150 ,l_dummy_date
3151 ,l_dummy_date);
3152 --
3153 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3154 ,'GS'
3155 ,l_grade
3156 ,l_PT_eff_start_date
3157 ,l_dummy_step
3158 ,l_std_max
3159 ,l_dummy_date
3160 ,l_dummy_date);
3161
3162 l_new_ret_basic_pay := l_std_min + ROUND((l_std_max - l_std_min) * l_7dp );
3163 l_new_std_relative_rate := l_new_ret_basic_pay;
3164 p_new_std_relative_rate := l_new_ret_basic_pay;
3165 --
3166 --
3167 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3168 ,'GS'
3169 ,l_grade
3170 ,(l_PT_eff_start_date - 1)
3171 ,l_dummy_step
3172 ,l_std_min
3173 ,l_dummy_date
3174 ,l_dummy_date);
3175 --
3176 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3177 ,'GS'
3178 ,l_grade
3179 ,(l_PT_eff_start_date - 1)
3180 ,l_dummy_step
3181 ,l_std_max
3182 ,l_dummy_date
3183 ,l_dummy_date);
3184
3185 l_old_ret_basic_pay := l_std_min + ROUND((l_std_max - l_std_min) * l_7dp );
3186
3187 l_B := l_new_ret_basic_pay - l_old_ret_basic_pay;
3188
3189 if l_A = l_B then
3190 lesser_amt := l_A;
3191 elsif l_A > l_B then
3192 lesser_amt := l_B;
3193 else
3194 lesser_amt := l_A;
3195 end if;
3196
3197 new_adj_basic_pay := p_pay_calc_data.current_adj_basic_pay + lesser_amt;
3198 new_locality_adj := new_adj_basic_pay - l_new_basic_pay;
3199
3200 l_new_std_relative_rate := l_new_std_relative_rate +
3201 ROUND(l_new_std_relative_rate *
3202 (NVL(l_adjustment_percentage,0)/100),0);
3203
3204 IF l_new_std_relative_rate > new_adj_basic_pay THEN
3205 new_adj_basic_pay := l_new_std_relative_rate;
3206 new_locality_adj := new_adj_basic_pay - l_new_basic_pay;
3207 ELSIF (l_new_basic_pay > new_adj_basic_pay) AND
3208 (l_user_table_name <> ghr_pay_calc.l_standard_table_name) THEN
3209 new_adj_basic_pay := l_new_basic_pay;
3210 new_locality_adj := 0;
3211 END IF;
3212
3213 p_new_adj_basic_pay := new_adj_basic_pay;
3214 p_new_locality_adj := new_locality_adj;
3215 hr_utility.set_location('Leaving ...'|| l_proc,5);
3216
3217 EXCEPTION
3218 WHEN others THEN
3219 -- Reset IN OUT parameters and set OUT parameters
3220 p_new_adj_basic_pay := NULL;
3221 p_new_locality_adj := NULL;
3222 RAISE;
3223 END;
3224
3225 --AVR
3226 --------------------------- <get_open_pay_table_values> ------------------------------------------
3227 PROCEDURE get_open_pay_table_values (p_user_table_id IN NUMBER
3228 ,p_pay_plan IN VARCHAR2
3229 ,p_grade_or_level IN VARCHAR2
3230 ,p_effective_date IN DATE
3231 ,p_row_high OUT NOCOPY NUMBER
3232 ,p_row_low OUT NOCOPY NUMBER) IS
3233 --
3234 l_proc varchar2(50) := 'get_open_pay_table_values';
3235 l_row_high NUMBER;
3236 l_row_low NUMBER;
3237 l_record_found BOOLEAN := FALSE;
3238 --
3239 -- Go and get the basic pay from the given pay table at the given grade or level
3240 -- and step
3241 -- NOTE:
3242 -- column => Pay Plan ||'-'|| Grade or Level
3243 --
3244 CURSOR cur_pay IS
3245 SELECT max(urw.ROW_HIGH_RANGE) ROW_HIGH_RANGE
3246 ,min(urw.ROW_LOW_RANGE_OR_NAME) ROW_LOW_RANGE_OR_NAME
3247 FROM pay_user_column_instances_f cin
3248 ,pay_user_rows_f urw
3249 ,pay_user_columns col
3250 WHERE col.user_table_id = p_user_table_id
3251 AND col.user_column_name = p_pay_plan||'-'||p_grade_or_level
3252 AND urw.user_table_id = p_user_table_id
3253 AND cin.user_row_id = urw.user_row_id
3254 AND cin.user_column_id = col.user_column_id
3255 AND NVL(p_effective_date,TRUNC(SYSDATE))
3256 BETWEEN urw.effective_start_date AND urw.effective_end_date
3257 AND NVL(p_effective_date,TRUNC(SYSDATE))
3258 BETWEEN cin.effective_start_date AND cin.effective_end_date;
3259 BEGIN
3260 hr_utility.set_location('Entering ...'|| l_proc,5);
3261 FOR cur_pay_rec IN cur_pay LOOP
3262 l_row_high := ROUND(cur_pay_rec.ROW_HIGH_RANGE,2);
3263 l_row_low := ROUND(cur_pay_rec.ROW_LOW_RANGE_OR_NAME,2);
3264 l_record_found := TRUE;
3265 --
3266 hr_utility.set_location('Record Found ...'|| l_proc,10);
3267 --
3268 END LOOP;
3269 --
3270 IF NOT l_record_found THEN
3271 p_row_high := null;
3272 p_row_low := null;
3273 ELSE
3274 p_row_high := l_row_high;
3275 p_row_low := l_row_low;
3276 END IF;
3277 hr_utility.set_location('Leaving ...'|| l_proc,20);
3278 --
3279
3280 EXCEPTION
3281 WHEN others THEN
3282 -- Reset IN OUT parameters and set OUT parameters
3283 p_row_high := NULL;
3284 p_row_low := NULL;
3285 RAISE;
3286 END get_open_pay_table_values;
3287
3288 FUNCTION get_pay_basis(
3289 p_effective_date IN ghr_pa_requests.effective_date%type,
3290 p_pa_request_id IN ghr_pa_requests.pa_request_id%type,
3291 p_person_id IN ghr_pa_requests.person_id%type
3292 ) RETURN ghr_pa_requests.from_pay_basis%type
3293 IS
3294 l_asg_ei_data per_assignment_extra_info%rowtype;
3295 l_prd per_assignment_extra_info.aei_information6%type;
3296 l_dummy VARCHAR2(30);
3297 l_assignment_id per_assignments_f.assignment_id%type;
3298 l_from_position_id ghr_pa_requests.from_position_id%type;
3299 l_from_pay_basis ghr_pa_requests.from_pay_basis%type;
3300 l_pos_ei_grade_data per_position_extra_info%rowtype;
3301
3302 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
3303 SELECT paf.assignment_id, paf.position_id
3304 FROM per_assignments_f paf
3305 WHERE paf.person_id = c_person_id
3306 AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
3307 AND paf.primary_flag = 'Y'
3308 AND paf.assignment_type <> 'B';
3309
3310 BEGIN
3311 -- Get From assignment id and position id using person_id and effective date
3312 FOR ctr_get_asgn_pos IN get_asgn_pos(p_person_id,p_effective_date) LOOP
3313 l_assignment_id := ctr_get_asgn_pos.assignment_id;
3314 l_from_position_id := ctr_get_asgn_pos.position_id;
3315 END LOOP;
3316
3317 -- Get Assignment extra info records
3318 IF l_assignment_id IS NOT NULL THEN
3319 ghr_pa_requests_pkg.get_SF52_asg_ddf_details
3320 (p_assignment_id => l_assignment_id
3321 ,p_date_effective => p_effective_date
3322 ,p_tenure => l_dummy
3323 ,p_annuitant_indicator => l_dummy
3324 ,p_pay_rate_determinant => l_prd
3325 ,p_work_schedule => l_dummy
3326 ,p_part_time_hours => l_dummy);
3327 END IF;
3328
3329 -- If PRD In 'A','B','E','F','U','V'
3330 IF l_prd IN ('A','B','E','F','U','V') THEN
3331 -- If PRD in 'A','B','E','F' and having temporary promotion step, get from position extra info
3332 IF l_prd IN ('A','B','E','F') AND
3333 ghr_pa_requests_pkg.temp_step_true(p_pa_request_id) THEN
3334 ghr_history_fetch.fetch_positionei(
3335 p_position_id => l_from_position_id,
3336 p_information_type => 'GHR_US_POS_VALID_GRADE',
3337 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3338 p_pos_ei_data => l_pos_ei_grade_data);
3339 l_from_pay_basis := l_pos_ei_grade_data.poei_information6;
3340 ELSE
3341 l_from_pay_basis := ghr_pa_requests_pkg.get_upd34_pay_basis
3342 (p_person_id => p_person_id
3343 ,p_position_id => l_from_position_id
3344 ,p_prd => l_prd
3345 ,p_noa_code => null
3346 ,p_pa_request_id => null
3347 ,p_effective_date => nvl(p_effective_date,trunc(sysdate)));
3348 END IF;
3349 -- If PRD not in 'A','B','E','F','U','V', get from position extra info
3350 ELSE
3351 ghr_history_fetch.fetch_positionei(
3352 p_position_id => l_from_position_id,
3353 p_information_type => 'GHR_US_POS_VALID_GRADE',
3354 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3355 p_pos_ei_data => l_pos_ei_grade_data);
3356 l_from_pay_basis := l_pos_ei_grade_data.poei_information6;
3357 END IF;
3358 return l_from_pay_basis;
3359 EXCEPTION
3360 WHEN OTHERS THEN
3361 return '-1';
3362 END get_pay_basis;
3363
3364 --------------------------- <get_locality_894_itpay> ------------------------------------------
3365 PROCEDURE get_locality_894_itpay
3366 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3367 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3368 ,p_new_basic_pay IN NUMBER
3369 ,p_GM_unadjusted_rate OUT NOCOPY NUMBER
3370 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3371 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3372
3373 l_std_user_table_id NUMBER;
3374 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
3375
3376 l_grade VARCHAR2(30);
3377
3378 l_std_min NUMBER;
3379 l_std_max NUMBER;
3380 l_std_min_old NUMBER;
3381 l_std_max_old NUMBER;
3382
3383 l_dummy_step VARCHAR2(30);
3384 l_dummy_date DATE;
3385 l_effective_start_date DATE;
3386 l_effective_end_date DATE;
3387
3388 new_adj_basic_pay NUMBER;
3389 new_locality_adj NUMBER;
3390
3391 l_B1 NUMBER;
3392 l_B2 NUMBER;
3393 l_B3 NUMBER;
3394 l_B4 NUMBER;
3395 l_B5 NUMBER;
3396 l_B6 NUMBER;
3397 l_B7 NUMBER;
3398 l_B8 NUMBER;
3399
3400 l_proc VARCHAR2(30) := 'GMIT_894';
3401
3402 CURSOR get_std_user_table_id IS
3403 SELECT utb.user_table_id
3404 FROM pay_user_tables utb
3405 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3406
3407 ----
3408 l_assignment_id per_assignments_f.assignment_id%type;
3409 l_value varchar2(60);
3410 l_multi_error_flag BOOLEAN;
3411 l_gm_unadjd_basic_pay Number;
3412
3413 CURSOR get_asgn_pos (c_person_id IN ghr_pa_requests.person_id%type,
3414 c_effective_date IN ghr_pa_requests.effective_date%type)
3415 IS
3416 SELECT paf.assignment_id
3417 FROM per_assignments_f paf
3418 WHERE paf.person_id = c_person_id
3419 AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
3420 AND paf.primary_flag = 'Y'
3421 AND paf.assignment_type <> 'B';
3422 ----
3423 BEGIN
3424 hr_utility.set_location('Entering ...'|| l_proc,5);
3425 g_gm_unadjd_basic_pay := NULL;
3426 gm_unadjusted_pay_flg := NULL;
3427 -- First get the id of standard pay table for later use
3428 FOR c_rec IN get_std_user_table_id LOOP
3429 l_std_user_table_id := c_rec.user_table_id;
3430 END LOOP;
3431
3432 IF p_retained_grade.grade_or_level IS NULL THEN
3433 l_grade := p_pay_calc_data.grade_or_level;
3434 ELSE
3435 l_grade := p_retained_grade.grade_or_level;
3436 END IF;
3437
3438 l_adjustment_percentage := get_lpa_percentage
3439 (p_pay_calc_data.duty_station_id
3440 ,p_pay_calc_data.effective_date);
3441
3442 -- Get From assignment id and position id using person_id and effective date
3443 FOR ctr_get_asgn_pos IN get_asgn_pos(p_pay_calc_data.person_id,(p_pay_calc_data.effective_date - 1)) LOOP
3444 l_assignment_id := ctr_get_asgn_pos.assignment_id;
3445 END LOOP;
3446
3447 --
3448 -- Fetch the value Unadjusted Basic pay. The very first time user will enter it.
3449 --
3450 begin
3451
3452 ghr_api.retrieve_element_entry_value (p_element_name => 'Unadjusted Basic Pay'
3453 ,p_input_value_name => 'Amount'
3454 ,p_assignment_id => l_assignment_id
3455 ,p_effective_date => (p_pay_calc_data.effective_date - 1)
3456 ,p_value => l_value
3457 ,p_multiple_error_flag => l_multi_error_flag);
3458 l_gm_unadjd_basic_pay := to_number(l_value);
3459 if l_gm_unadjd_basic_pay is null or l_gm_unadjd_basic_pay = 0 then
3460 gm_unadjusted_pay_flg := 'Y';
3461 hr_utility.set_location('Unadjusted Basic Pay is zero or null ' || sqlerrm(sqlcode),25);
3462 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3463 hr_utility.raise_error;
3464 end if;
3465 exception
3466 when others then
3467 gm_unadjusted_pay_flg := 'Y';
3468 hr_utility.set_location('Error in fetching of Unadjusted Basic Pay ' || sqlerrm(sqlcode),25);
3469 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3470 hr_utility.raise_error;
3471 end;
3472
3473 --
3474 -- Present Year values.
3475 --
3476 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3477 ,'GS'
3478 ,l_grade
3479 ,p_pay_calc_data.effective_date
3480 ,l_dummy_step
3481 ,l_std_min
3482 ,l_effective_start_date
3483 ,l_effective_end_date);
3484 --
3485 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3486 ,'GS'
3487 ,l_grade
3488 ,p_pay_calc_data.effective_date
3489 ,l_dummy_step
3490 ,l_std_max
3491 ,l_effective_start_date
3492 ,l_effective_end_date);
3493
3494 -- Previous Year values.
3495 --
3496 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3497 ,'GS'
3498 ,l_grade
3499 ,(l_effective_start_date - 1)
3500 ,l_dummy_step
3501 ,l_std_min_old
3502 ,l_dummy_date
3503 ,l_dummy_date);
3504 --
3505 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3506 ,'GS'
3507 ,l_grade
3508 ,(l_effective_start_date - 1)
3509 ,l_dummy_step
3510 ,l_std_max_old
3511 ,l_dummy_date
3512 ,l_dummy_date);
3513
3514 l_B1 := l_gm_unadjd_basic_pay - l_std_min_old;
3515 l_B2 := l_std_max_old - l_std_min_old;
3516 l_B3 := TRUNC( (l_B1/l_B2) ,7);
3517 l_B4 := l_std_max - l_std_min;
3518 l_B5 := CEIL((l_B3 * l_B4));
3519 l_B6 := l_B5 + l_std_min;
3520 l_B7 := ROUND(l_B6 * (NVL(l_adjustment_percentage,0)/100),0);
3521 l_B8 := l_B6 + l_B7;
3522 new_locality_adj := l_B8 - p_new_basic_pay;
3523 new_adj_basic_pay := p_new_basic_pay + new_locality_adj;
3524
3525 p_GM_unadjusted_rate := l_B6;
3526 g_gm_unadjd_basic_pay := l_B6;
3527 p_new_adj_basic_pay := new_adj_basic_pay;
3528 p_new_locality_adj := new_locality_adj;
3529 hr_utility.set_location('Leaving ...'|| l_proc,5);
3530
3531 EXCEPTION
3532 WHEN others THEN
3533 -- Reset IN OUT parameters and set OUT parameters
3534 p_GM_unadjusted_rate :=NULL;
3535 p_new_adj_basic_pay :=NULL;
3536 p_new_locality_adj :=NULL;
3537 hr_utility.set_location('Leaving.... ' || l_proc,6);
3538 RAISE;
3539 END get_locality_894_itpay;
3540
3541 --------------------------- <get_locality_892_itpay> ------------------------------------------
3542 PROCEDURE get_locality_892_itpay
3543 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3544 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
3545 ,p_new_basic_pay IN NUMBER
3546 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
3547 ,p_new_locality_adj OUT NOCOPY NUMBER) IS
3548
3549 l_std_user_table_id NUMBER;
3550 l_user_table_id NUMBER;
3551 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
3552
3553 l_grade VARCHAR2(30);
3554
3555 l_std_min NUMBER;
3556 l_std_max NUMBER;
3557 l_it_min NUMBER;
3558 l_it_max NUMBER;
3559
3560 l_dummy_step VARCHAR2(30);
3561 l_dummy_date DATE;
3562 l_effective_start_date DATE;
3563 l_effective_end_date DATE;
3564
3565 new_adj_basic_pay NUMBER;
3566 new_locality_adj NUMBER;
3567
3568 l_C1 NUMBER;
3569 l_C2 NUMBER;
3570 l_C3 NUMBER;
3571 l_C4 NUMBER;
3572 l_C5 NUMBER;
3573 l_C6 NUMBER;
3574 l_dummy_number NUMBER;
3575
3576 l_GM_unadjusted_rate NUMBER;
3577 l_proc VARCHAR2(30) := 'GMIT_892';
3578
3579 CURSOR get_std_user_table_id IS
3580 SELECT utb.user_table_id
3581 FROM pay_user_tables utb
3582 WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;
3583
3584 ----
3585 l_assignment_id per_assignments_f.assignment_id%type;
3586 l_value varchar2(60);
3587 l_multi_error_flag BOOLEAN;
3588 l_gm_unadjd_basic_pay Number;
3589
3590 CURSOR get_asgn_pos (c_person_id IN ghr_pa_requests.person_id%type,
3591 c_effective_date IN ghr_pa_requests.effective_date%type)
3592 IS
3593 SELECT paf.assignment_id
3594 FROM per_assignments_f paf
3595 WHERE paf.person_id = c_person_id
3596 AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
3597 AND paf.primary_flag = 'Y'
3598 AND paf.assignment_type <> 'B';
3599 ----
3600 BEGIN
3601 hr_utility.set_location('Entering ...'|| l_proc,5);
3602 g_gm_unadjd_basic_pay := NULL;
3603 gm_unadjusted_pay_flg := NULL;
3604 -- First get the id of standard pay table for later use
3605 FOR c_rec IN get_std_user_table_id LOOP
3606 l_std_user_table_id := c_rec.user_table_id;
3607 END LOOP;
3608
3609 IF p_retained_grade.grade_or_level IS NULL THEN
3610 l_grade := p_pay_calc_data.grade_or_level;
3611 l_user_table_id := p_pay_calc_data.user_table_id;
3612 ELSE
3613 l_grade := p_retained_grade.grade_or_level;
3614 l_user_table_id := p_retained_grade.user_table_id;
3615 END IF;
3616
3617 l_adjustment_percentage := get_lpa_percentage
3618 (p_pay_calc_data.duty_station_id
3619 ,p_pay_calc_data.effective_date);
3620
3621 -- Get From assignment id and position id using person_id and effective date
3622 FOR ctr_get_asgn_pos IN get_asgn_pos(p_pay_calc_data.person_id,(p_pay_calc_data.effective_date - 1)) LOOP
3623 l_assignment_id := ctr_get_asgn_pos.assignment_id;
3624 END LOOP;
3625
3626 --
3627 -- Fetch the value Unadjusted Basic pay. The very first time user will enter it.
3628 --
3629 begin
3630
3631 ghr_api.retrieve_element_entry_value (p_element_name => 'Unadjusted Basic Pay'
3632 ,p_input_value_name => 'Amount'
3633 ,p_assignment_id => l_assignment_id
3634 ,p_effective_date => (p_pay_calc_data.effective_date - 1)
3635 ,p_value => l_value
3636 ,p_multiple_error_flag => l_multi_error_flag);
3637 l_gm_unadjd_basic_pay := to_number(l_value);
3638 if l_gm_unadjd_basic_pay is null or l_gm_unadjd_basic_pay = 0 then
3639 gm_unadjusted_pay_flg := 'Y';
3640 hr_utility.set_location('Unadjusted Basic Pay is zero or null ' || sqlerrm(sqlcode),25);
3641 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3642 hr_utility.raise_error;
3643 end if;
3644 exception
3645 when others then
3646 gm_unadjusted_pay_flg := 'Y';
3647 hr_utility.set_location('Error in fetching of Unadjusted Basic Pay ' || sqlerrm(sqlcode),25);
3648 hr_utility.set_message(8301,'GHR_38843_NO_GM_UNADJUST');
3649 hr_utility.raise_error;
3650 end;
3651 --
3652 -- Present Year values.
3653 --
3654 ghr_pc_basic_pay.get_min_pay_table_value(l_std_user_table_id
3655 ,'GS'
3656 ,l_grade
3657 ,p_pay_calc_data.effective_date
3658 ,l_dummy_step
3659 ,l_std_min
3660 ,l_effective_start_date
3661 ,l_effective_end_date);
3662 --
3663 ghr_pc_basic_pay.get_max_pay_table_value(l_std_user_table_id
3664 ,'GS'
3665 ,l_grade
3666 ,p_pay_calc_data.effective_date
3667 ,l_dummy_step
3668 ,l_std_max
3669 ,l_effective_start_date
3670 ,l_effective_end_date);
3671
3672 -- Present Year values.
3673 --
3674 ghr_pc_basic_pay.get_min_pay_table_value(l_user_table_id
3675 ,'GS'
3676 ,l_grade
3677 ,p_pay_calc_data.effective_date
3678 ,l_dummy_step
3679 ,l_it_min
3680 ,l_dummy_date
3681 ,l_dummy_date);
3682 --
3683 ghr_pc_basic_pay.get_max_pay_table_value(l_user_table_id
3684 ,'GS'
3685 ,l_grade
3686 ,p_pay_calc_data.effective_date
3687 ,l_dummy_step
3688 ,l_it_max
3689 ,l_dummy_date
3690 ,l_dummy_date);
3691
3692 l_C1 := CEIL((l_it_max - l_it_min) / 9);
3693 l_C2 := p_pay_calc_data.current_basic_pay + l_C1;
3694 l_C3 := CEIL((l_std_max - l_std_min) / 9);
3695 l_C4 := l_gm_unadjd_basic_pay + l_C3;
3696 l_C5 := l_C4 + ROUND(l_C4 * (NVL(l_adjustment_percentage,0)/100),0);
3697 l_C6 := l_C5 - l_C2;
3698 new_locality_adj := l_C6;
3699 new_adj_basic_pay := l_C5;
3700
3701 g_gm_unadjd_basic_pay := l_C4;
3702
3703 p_new_adj_basic_pay := new_adj_basic_pay;
3704 p_new_locality_adj := new_locality_adj;
3705 hr_utility.set_location('Leaving ...'|| l_proc,5);
3706
3707 EXCEPTION
3708 WHEN others THEN
3709 -- Reset IN OUT parameters and set OUT parameters
3710 p_new_adj_basic_pay := NULL;
3711 p_new_locality_adj := NULL;
3712 hr_utility.set_location('Leaving.... ' || l_proc,6);
3713 RAISE;
3714 END get_locality_892_itpay;
3715
3716 -- FWFA Changes. Created procedures get_special_pay_table_value, special_rate_pay_calc
3717 -- Create function fwfa_pay_calc
3718 FUNCTION fwfa_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3719 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
3720 RETURN BOOLEAN IS
3721 CURSOR cur_ppl(p_pay_plan VARCHAR2) IS
3722 SELECT 1
3723 FROM ghr_pay_plans ppl
3724 WHERE ppl.pay_plan = p_pay_plan
3725 AND ppl.equivalent_pay_plan = 'GS';
3726
3727 CURSOR cur_ppl_fw(p_pay_plan VARCHAR2) IS --5218445
3728 SELECT 1
3729 FROM ghr_pay_plans ppl
3730 WHERE ppl.pay_plan = p_pay_plan
3731 AND ppl.equivalent_pay_plan = 'FW';
3732
3733 l_pay_plan VARCHAR2(30);
3734 l_user_table_id pay_user_tables.user_table_id%type;
3735 l_user_table_name pay_user_tables.user_table_name%type;
3736 -- Bug 4695312
3737 l_gs_equivalent BOOLEAN;
3738 BEGIN
3739 l_gs_equivalent := FALSE;
3740 -- Removed the PRD comparison. Modified it with Pay Table Comparison
3741 IF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V') THEN -- 5218445
3742 IF p_retained_grade.temp_step is not null AND
3743 p_pay_calc_data.noa_code <> '740' then
3744 l_user_table_id := p_pay_calc_data.user_table_id;
3745 l_pay_plan := p_pay_calc_data.pay_plan;
3746 ELSE
3747 l_user_table_id := p_retained_grade.user_table_id;
3748 l_pay_plan := p_retained_grade.pay_plan;
3749 END IF;
3750 ELSE
3751 l_user_table_id := p_pay_calc_data.user_table_id;
3752 l_pay_plan := p_pay_calc_data.pay_plan;
3753 END IF;
3754
3755 FOR cur_ppl_fw_rec IN cur_ppl_fw(l_pay_plan)
3756 LOOP
3757 IF p_pay_calc_data.pay_rate_determinant IN ('6','E','F') THEN
3758 g_fwfa_pay_calc_flag := TRUE;
3759 g_fw_equiv_pay_plan := TRUE;
3760 RETURN TRUE;
3761 END IF;
3762 END LOOP;
3763
3764
3765 IF p_pay_calc_data.effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
3766 l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
3767 --
3768 FOR cur_ppl_rec IN cur_ppl(l_pay_plan)
3769 LOOP
3770 l_gs_equivalent := TRUE;
3771 END LOOP;
3772 -- Bug#4695312 For Retained Pay PRDs don't verify position pay table.
3773 -- IF the pay plan is GS Equivalent, Return TRUE.
3774 IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R') THEN --bug#4999237
3775 IF l_gs_equivalent THEN
3776 g_fwfa_pay_calc_flag := TRUE;
3777 RETURN TRUE;
3778 ELSE
3779 g_fwfa_pay_calc_flag := FALSE;
3780 RETURN FALSE;
3781 END IF;
3782 ELSE
3783 IF l_user_table_name NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) AND
3784 l_gs_equivalent THEN
3785 g_fwfa_pay_calc_flag := TRUE;
3786 RETURN TRUE;
3787 ELSE
3788 g_fwfa_pay_calc_flag := FALSE;
3789 RETURN FALSE;
3790 END IF;
3791 END IF;
3792 ELSE
3793 g_fwfa_pay_calc_flag := FALSE;
3794 RETURN FALSE;
3795 END IF;
3796 END fwfa_pay_calc;
3797
3798 PROCEDURE get_special_pay_table_value (p_pay_plan IN VARCHAR2
3799 ,p_grade_or_level IN VARCHAR2
3800 ,p_step_or_rate IN VARCHAR2
3801 ,p_user_table_id IN NUMBER
3802 ,p_effective_date IN DATE
3803 ,p_pt_value OUT NOCOPY NUMBER
3804 ,p_PT_eff_start_date OUT NOCOPY DATE
3805 ,p_PT_eff_end_date OUT NOCOPY DATE
3806 ,p_pp_grd_exists OUT NOCOPY BOOLEAN) IS
3807 --
3808 l_PT_value NUMBER;
3809 l_PT_eff_start_date DATE;
3810 l_PT_eff_end_date DATE;
3811 l_record_found BOOLEAN := FALSE;
3812
3813 --
3814 -- Go and get the basic pay from the given pay table at the given grade or level
3815 -- and step
3816 -- NOTE: column => Step or Rate
3817 -- row => Pay Plan ||'-'|| Grade or Level
3818 --
3819 CURSOR cur_pay IS
3820 SELECT cin.value basic_pay
3821 ,cin.effective_start_date
3822 ,cin.effective_end_date
3823 ,col.user_column_name step
3824 FROM pay_user_column_instances_f cin
3825 ,pay_user_rows_f urw
3826 ,pay_user_columns col
3827 WHERE col.user_table_id = p_user_table_id
3828 -- AND col.user_column_name = p_step_or_rate
3829 AND urw.user_table_id = p_user_table_id
3830 --Bug# 7046241 added decode for pay plan GP
3831 AND urw.row_low_range_or_name = decode(p_pay_plan,'GP','GS',p_pay_plan)||'-'||p_grade_or_level
3832 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
3833 AND cin.user_row_id = urw.user_row_id
3834 AND cin.user_column_id = col.user_column_id
3835 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;
3836 BEGIN
3837 FOR cur_pay_rec IN cur_pay LOOP
3838 l_record_found := TRUE;
3839 IF cur_pay_rec.step = p_step_or_rate THEN
3840 l_PT_value := cur_pay_rec.basic_pay;
3841 l_PT_eff_start_date := cur_pay_rec.effective_start_date;
3842 l_PT_eff_end_date := cur_pay_rec.effective_end_date;
3843 --
3844 EXIT;
3845 END IF;
3846 END LOOP;
3847 --
3848 IF NOT l_record_found THEN
3849 p_pp_grd_exists := FALSE;
3850 p_pt_value := NULL;
3851 p_pt_eff_start_date := NULL;
3852 p_pt_eff_end_date := NULL;
3853 ELSE
3854 p_PT_value := l_PT_value;
3855 p_PT_eff_start_date := l_PT_eff_start_date;
3856 p_PT_eff_end_date := l_PT_eff_end_date;
3857 p_pp_grd_exists := TRUE;
3858 END IF;
3859
3860 EXCEPTION
3861 WHEN others THEN
3862 -- Reset IN OUT parameters and set OUT parameters
3863 p_PT_value := NULL;
3864 p_PT_eff_start_date := NULL;
3865 p_PT_eff_end_date := NULL;
3866 p_pp_grd_exists := NULL;
3867 RAISE;
3868 --
3869 END get_special_pay_table_value;
3870
3871 PROCEDURE special_rate_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
3872 ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
3873 ,p_retained_grade IN OUT NOCOPY ghr_pay_calc.retained_grade_rec_type) IS
3874
3875 l_new_prd VARCHAR2(30);
3876 l_new_step VARCHAR2(30);
3877 l_term_ret_pay_prd BOOLEAN;
3878 l_pay_plan VARCHAR2(5);
3879 l_default_pay_plan VARCHAR2(5);
3880 l_step_or_rate VARCHAR2(5);
3881 l_curr_step_or_rate VARCHAR2(5);
3882 l_grade_or_level VARCHAR2(5);
3883 l_pay_calc_process VARCHAR2(1);
3884 l_dummy VARCHAR2(1);
3885 l_pt_value NUMBER;
3886 l_locality_adj NUMBER;
3887 l_basic_pay NUMBER;
3888 l_adj_basic_pay NUMBER;
3889 l_new_basic_pay NUMBER;
3890 l_new_locality_adj NUMBER;
3891 l_new_adj_basic_pay NUMBER;
3892 l_increment NUMBER;
3893 l_old_basic_pay NUMBER;
3894 l_old_locality_adj NUMBER;
3895 l_old_adj_basic_pay NUMBER;
3896 l_adj_perc_factor NUMBER(10,6);
3897 l_old_adj_perc_factor NUMBER(10,6);
3898 l_new_special_rate NUMBER; -- AC for Bug#5114467
3899 l_dummy_date DATE;
3900 l_pt_eff_start_date DATE;
3901 l_pt_eff_end_date DATE;
3902 l_pp_grd_exists BOOLEAN;
3903 l_rg_flag BOOLEAN;
3904 l_leo_flag BOOLEAN;
3905 l_ret_flag BOOLEAN ;
3906 l_loc_rate_less_spl_rate BOOLEAN;
3907 l_user_table_name pay_user_tables.user_table_name%type;
3908 l_default_pay_table pay_user_tables.user_table_name%type;
3909 l_new_position_pay_table pay_user_tables.user_table_name%type;
3910 l_calculation_pay_table pay_user_tables.user_table_name%type;
3911 l_position_pay_table pay_user_tables.user_table_name%type;
3912 l_retained_pay_table pay_user_tables.user_table_name%type;
3913 l_user_table_id pay_user_tables.user_table_id%type;
3914 l_default_table_id pay_user_tables.user_table_id%type;
3915 l_calculation_pay_table_id pay_user_tables.user_table_id%type;
3916 l_position_pay_table_id pay_user_tables.user_table_id%type;
3917
3918 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%type;
3919
3920 l_old_special_rate NUMBER;
3921 l_special_rate NUMBER; --for bug 4999237
3922 l_gm_pay_plan BOOLEAN;
3923 --Begin Bug 7046241
3924 l_gr_pay_plan BOOLEAN;
3925 --End Bug 7046241
3926
3927 CURSOR c_get_table_id(p_user_table_name VARCHAR2) IS
3928 SELECT utb.user_table_id user_table_id, utb.user_table_name user_table_name
3929 FROM pay_user_tables utb
3930 WHERE utb.user_table_name = p_user_table_name;
3931
3932 ------ Bug#5741977 start
3933 l_business_group_id per_positions.organization_id%TYPE;
3934
3935 CURSOR GET_GRADE_ID (v_pay_plan varchar2, v_grade_or_level varchar2) IS
3936 SELECT grd.grade_id grade_id
3937 FROM per_grades grd,
3938 per_grade_definitions gdf
3939 WHERE gdf.segment1 = v_pay_plan
3940 AND gdf.segment2 = v_grade_or_level
3941 AND grd.grade_definition_id = gdf.grade_definition_id
3942 AND grd.business_group_id = l_business_group_id;
3943
3944 l_grade_id NUMBER;
3945 l_fwfa_pay_plan_changed BOOLEAN;
3946 ------ Bug#5741977 end
3947
3948 -- 7046241
3949 l_pos_ei_valid_grade per_position_extra_info%ROWTYPE;
3950 -- 7046241
3951
3952 PROCEDURE set_cpt_ppt_prd(p_leo_flag IN BOOLEAN
3953 ,p_rg_flag IN BOOLEAN
3954 ,p_pp_grd_exists IN BOOLEAN
3955 ,p_loc_rate_less_spl_rate IN BOOLEAN
3956 ,p_prd IN VARCHAR2
3957 ,p_pt_value IN NUMBER
3958 ,p_special_rate_table IN VARCHAR2
3959 ,p_calculation_pay_table OUT NOCOPY VARCHAR2
3960 ,p_position_pay_table OUT NOCOPY VARCHAR2
3961 ,p_retained_pay_table OUT NOCOPY VARCHAR2
3962 ,p_new_prd OUT NOCOPY VARCHAR2
3963 ) IS
3964 BEGIN
3965 hr_utility.set_location('Inside set_cpt_ppt_prd',0);
3966
3967 IF p_leo_flag THEN
3968 --Bug# 5635023 added PRD 7
3969 IF p_prd IN ('5', '6', '7') THEN
3970 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
3971 IF p_loc_rate_less_spl_rate THEN
3972 p_position_pay_table := p_special_rate_table;
3973 p_new_prd := '6';
3974 p_calculation_pay_table := p_special_rate_table;
3975 g_pay_table_upd_flag := FALSE;
3976 ELSE
3977 p_position_pay_table := p_special_rate_table;
3978 p_new_prd := '6';
3979 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3980 g_pay_table_upd_flag := FALSE;
3981 END IF;
3982 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
3983 p_position_pay_table := p_special_rate_table;
3984 p_new_prd := '6';
3985 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3986 g_pay_table_upd_flag := FALSE;
3987 ELSE
3988 p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
3989 p_new_prd := '6';
3990 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
3991 g_pay_table_upd_flag := TRUE;
3992 END IF;
3993 ELSIF p_prd IN ('E','F') THEN
3994 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
3995 IF p_loc_rate_less_spl_rate THEN
3996 p_retained_pay_table := p_special_rate_table;
3997 p_new_prd := p_prd;
3998 p_calculation_pay_table := p_special_rate_table;
3999 g_pay_table_upd_flag := FALSE;
4000 ELSE
4001 p_retained_pay_table := p_special_rate_table;
4002 p_new_prd := p_prd;
4003 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4004 g_pay_table_upd_flag := FALSE;
4005 END IF;
4006 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4007 p_retained_pay_table := p_special_rate_table;
4008 p_new_prd := p_prd;
4009 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4010 g_pay_table_upd_flag := FALSE;
4011 ELSE
4012 p_retained_pay_table := ghr_pay_calc.l_spl491_table_name;
4013 p_new_prd := p_prd;
4014 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4015 g_pay_table_upd_flag := TRUE;
4016 END IF;
4017 ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN --bug 4999237
4018 IF NOT(p_pp_grd_exists) THEN
4019 p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4020 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4021 g_pay_table_upd_flag := TRUE;
4022 ELSE
4023 p_position_pay_table := p_special_rate_table;
4024 g_pay_table_upd_flag := FALSE;
4025 p_calculation_pay_table := p_special_rate_table;
4026 END IF;
4027 IF p_pay_calc_data.pay_rate_determinant = '4' THEN
4028 p_new_prd := 'J';
4029 ELSE
4030 p_new_prd := p_prd;
4031 END IF;
4032 ELSIF p_prd IN ('0','A','B') THEN
4033 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4034 IF p_loc_rate_less_spl_rate THEN
4035 p_position_pay_table := p_special_rate_table;
4036 p_calculation_pay_table := p_special_rate_table;
4037 g_pay_table_upd_flag := FALSE;
4038 ELSE
4039 p_position_pay_table := p_special_rate_table;
4040 p_new_prd := p_prd;
4041 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4042 g_pay_table_upd_flag := FALSE;
4043 END IF;
4044 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4045 p_position_pay_table := p_special_rate_table;
4046 p_new_prd := p_prd;
4047 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4048 g_pay_table_upd_flag := FALSE;
4049 ELSE
4050 p_position_pay_table := ghr_pay_calc.l_spl491_table_name;
4051 p_new_prd := p_prd;
4052 p_calculation_pay_table := ghr_pay_calc.l_spl491_table_name;
4053 g_pay_table_upd_flag := TRUE;
4054 END IF;
4055 IF p_prd = '0' THEN
4056 p_new_prd := '6';
4057 ELSIF p_prd = 'A' THEN
4058 p_new_prd := 'E';
4059 ELSIF p_prd = 'B' THEN
4060 p_new_prd := 'F';
4061 END IF;
4062 END IF;
4063 ELSE -- NON LEO
4064 --Bug# 5635023 added PRD 7
4065 IF p_prd IN ('5', '6', '7') THEN
4066 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4067 IF p_loc_rate_less_spl_rate THEN
4068 p_position_pay_table := p_special_rate_table;
4069 p_new_prd := '6';
4070 p_calculation_pay_table := p_special_rate_table;
4071 g_pay_table_upd_flag := FALSE;
4072 ELSE
4073 p_position_pay_table := p_special_rate_table;
4074 p_new_prd := '0';
4075 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4076 g_pay_table_upd_flag := FALSE;
4077 END IF;
4078 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4079 p_position_pay_table := p_special_rate_table;
4080 p_new_prd := '0';
4081 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4082 g_pay_table_upd_flag := FALSE;
4083 ELSE
4084 p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4085 p_new_prd := '0';
4086 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4087 g_pay_table_upd_flag := TRUE;
4088 END IF;
4089 ELSIF p_prd IN ('E','F') THEN
4090 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4091 IF p_loc_rate_less_spl_rate THEN
4092 p_retained_pay_table := p_special_rate_table;
4093 p_new_prd := p_prd;
4094 p_calculation_pay_table := p_special_rate_table;
4095 g_pay_table_upd_flag := FALSE;
4096 ELSE
4097 p_retained_pay_table := p_special_rate_table;
4098 IF p_prd = 'E' THEN
4099 p_new_prd := 'A';
4100 ELSIF p_prd = 'F' THEN
4101 p_new_prd := 'B';
4102 END IF;
4103 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4104 g_pay_table_upd_flag := FALSE;
4105 END IF;
4106 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4107 p_retained_pay_table := p_special_rate_table;
4108 IF p_prd = 'E' THEN
4109 p_new_prd := 'A';
4110 ELSIF p_prd = 'F' THEN
4111 p_new_prd := 'B';
4112 END IF;
4113 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4114 g_pay_table_upd_flag := FALSE;
4115 ELSE
4116 p_retained_pay_table := ghr_pay_calc.l_standard_table_name;
4117 IF p_prd = 'E' THEN
4118 p_new_prd := 'A';
4119 ELSIF p_prd = 'F' THEN
4120 p_new_prd := 'B';
4121 END IF;
4122 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4123 g_pay_table_upd_flag := TRUE;
4124 END IF;
4125 ELSIF p_prd IN ('3','4','J','K','U','V','R') THEN
4126 IF NOT(p_pp_grd_exists) THEN
4127 p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4128 g_pay_table_upd_flag := TRUE;
4129 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4130 ELSE
4131 p_position_pay_table := p_special_rate_table;
4132 g_pay_table_upd_flag := FALSE;
4133 p_calculation_pay_table := p_special_rate_table;
4134 END IF;
4135 IF p_pay_calc_data.pay_rate_determinant = '4' THEN
4136 p_new_prd := 'J';
4137 ELSE
4138 p_new_prd := p_prd;
4139 END IF;
4140 -- FWFA new change. Added the following condition to handle PRDs 0,A,B.
4141 ELSIF p_prd IN ('0','A','B') THEN
4142 IF p_pp_grd_exists AND p_pt_value IS NOT NULL THEN
4143 IF p_loc_rate_less_spl_rate THEN
4144 p_position_pay_table := p_special_rate_table;
4145 IF p_prd = '0' THEN
4146 p_new_prd := '6';
4147 ELSIF p_prd = 'A' THEN
4148 p_new_prd := 'E';
4149 ELSIF p_prd = 'B' THEN
4150 p_new_prd := 'F';
4151 END IF;
4152 p_calculation_pay_table := p_special_rate_table;
4153 g_pay_table_upd_flag := FALSE;
4154 ELSE
4155 p_position_pay_table := p_special_rate_table;
4156 p_new_prd := p_prd;
4157 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4158 g_pay_table_upd_flag := FALSE;
4159 END IF;
4160 ELSIF p_pp_grd_exists AND p_pt_value IS NULL THEN
4161 p_position_pay_table := p_special_rate_table;
4162 p_new_prd := p_prd;
4163 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4164 g_pay_table_upd_flag := FALSE;
4165 ELSE
4166 p_position_pay_table := ghr_pay_calc.l_standard_table_name;
4167 p_new_prd := p_prd;
4168 p_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4169 g_pay_table_upd_flag := TRUE;
4170 END IF;
4171 END IF;
4172 END IF;
4173 hr_utility.set_location('Leaving CPT PRD',10);
4174 END set_cpt_ppt_prd;
4175
4176 BEGIN
4177
4178 ------ Bug#5741977 start
4179 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
4180 l_fwfa_pay_plan_changed := FALSE;
4181 ------ Bug#5741977 end
4182 --
4183
4184
4185
4186 if p_pay_calc_data.noa_family_code = 'APP' then
4187 if p_pay_calc_data.pay_rate_determinant
4188 in ('2','C','M','P','R','S') then
4189 p_pay_calc_out_data.open_pay_fields := TRUE;
4190 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
4191 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
4192 raise ghr_pay_calc.unable_to_calculate;
4193 end if;
4194 end if;
4195 ---
4196
4197
4198 -- 1. Set the Pay related values depending on the Retained/Non-Retained Grade.
4199 hr_utility.set_location('PRD: '||p_pay_calc_data.pay_rate_determinant,20);
4200 IF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F','U','V') and
4201 p_retained_grade.temp_step is NULL THEN
4202 l_user_table_id := p_retained_grade.user_table_id;
4203 l_pay_plan := p_retained_grade.pay_plan;
4204 l_grade_or_level:= p_retained_grade.grade_or_level;
4205 l_step_or_rate := p_retained_grade.step_or_rate;
4206 l_rg_flag := TRUE;
4207 ELSE
4208 l_user_table_id := p_pay_calc_data.user_table_id;
4209 l_pay_plan := p_pay_calc_data.pay_plan;
4210 l_grade_or_level:= p_pay_calc_data.grade_or_level;
4211 -- Added NOA Code 867 for the Bug#5621814
4212 IF p_pay_calc_data.noa_code IN ('867', '892','893') OR
4213 p_pay_calc_data.second_noa_code IN ('867', '892','893') THEN
4214 l_step_or_rate := p_pay_calc_data.current_step_or_rate;
4215 ELSE
4216 l_step_or_rate := p_pay_calc_data.step_or_rate;
4217 END IF;
4218 -- Bug#4701896
4219 IF p_retained_grade.temp_step IS NOT NULL THEN
4220 l_step_or_rate := p_retained_grade.temp_step;
4221 END IF;
4222 l_rg_flag := FALSE;
4223 END IF;
4224
4225 l_gm_pay_plan := FALSE;
4226 --Begin Bug 7046241
4227 l_gr_pay_plan := FALSE;
4228 --End Bug 7046241
4229 -- 2. Get the user table name into local variable
4230 l_user_table_name := ghr_pay_calc.get_user_table_name(l_user_table_id);
4231 hr_utility.set_location('user table name:' ||l_user_table_name,30);
4232 -- 3. Get the Locality Percentage Value
4233 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_pay_calc_data.duty_station_id
4234 ,p_pay_calc_data.effective_date),0);
4235 hr_utility.set_location('l_adjustment_percentage ..' || to_char(l_adjustment_percentage),31);
4236 l_adj_perc_factor := l_adjustment_percentage/100;
4237 hr_utility.set_location('l_adj_perc_factor ..' || to_char(l_adj_perc_factor),32);
4238
4239
4240
4241 -- Check LEO Posiiton or NOT
4242 -- Bug#4709111 Modified p_pay_calc_data.grade_or_level to l_grade_or_level.
4243 -- Bug#5089732 Treat all the positions with pay plan GL as LEO Positions.
4244 IF (ghr_pay_calc.LEO_position (l_dummy
4245 ,p_pay_calc_data.position_id
4246 ,l_dummy
4247 ,l_dummy
4248 ,p_pay_calc_data.effective_date)
4249 AND l_grade_or_level between 03 and 10) OR
4250 l_pay_plan = 'GL' THEN
4251 l_leo_flag := TRUE;
4252 l_default_pay_table := ghr_pay_calc.l_spl491_table_name;
4253 ELSE
4254 l_leo_flag := FALSE;
4255 l_default_pay_table := ghr_pay_calc.l_standard_table_name;
4256 END IF;
4257
4258
4259 --Bug #5919700 For 890 action and GM Pay plan GS should be considered
4260 IF (p_pay_calc_data.noa_code = '890' OR p_pay_calc_data.second_noa_code = '890') AND
4261 p_pay_calc_data.pay_plan = 'GM' then
4262 l_default_pay_plan := 'GS';
4263 l_pay_plan := 'GS';
4264 l_grade_id := NULL;
4265 FOR get_grade_id_rec IN get_grade_id(l_default_pay_plan, l_grade_or_level)
4266 LOOP
4267 l_grade_id := get_grade_id_rec.grade_id;
4268 l_fwfa_pay_plan_changed := TRUE;
4269 g_pay_table_upd_flag := TRUE;
4270 exit;
4271 END LOOP;
4272 END IF;
4273
4274
4275 -- Bug#5741977 - Set the default Pay Plan as GL as GS, GG are end dated in 0491 table.
4276 IF l_default_pay_table = ghr_pay_calc.l_spl491_table_name AND
4277 l_pay_plan IN ('GS','GG') AND
4278 p_pay_calc_data.effective_date >= to_date('2007/01/07','YYYY/MM/DD') THEN
4279 l_default_pay_plan := 'GL';
4280 ------ Bug#5741977 start
4281 l_grade_id := null;
4282 FOR get_grade_id_rec IN get_grade_id(l_default_pay_plan, l_grade_or_level)
4283 LOOP
4284 l_grade_id := get_grade_id_rec.grade_id;
4285 l_fwfa_pay_plan_changed := TRUE;
4286 exit;
4287 END LOOP;
4288 ------ Bug#5741977 End
4289 ELSE
4290 l_default_pay_plan := l_pay_plan;
4291 END IF;
4292
4293
4294
4295
4296
4297 FOR table_id_rec in c_get_table_id(l_default_pay_table)
4298 LOOP
4299 l_default_table_id := table_id_rec.user_table_id;
4300 END LOOP;
4301
4302 -- 4. Processing Employees On Retained Pay
4303 IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R')
4304 AND NOT(l_pay_plan IN ('GR') AND p_pay_calc_data.pay_rate_determinant IN ('4')) THEN --bug 4999237
4305 --Bug# 7518210,to skip this calc and moving to else part(i.e GM calc)added pay plan GR and PRD 4 condition
4306 -----Bug 4665119 ..Other than appointment and 894 for all other actions pay fields are to be open
4307 ----- Appointment already checked in the fwfa_pay_cal function.
4308 ----- Modified by AVR 17-OCT-2005
4309 -- Bug#5368848 Open the pay fields if the NOA Code is not 894.
4310 -- GPPA Update46. Added 890 in the NOT IN list.
4311 IF p_pay_calc_data.noa_code NOT IN ('894','890') then
4312 p_pay_calc_out_data.open_pay_fields := TRUE;
4313 hr_utility.set_message(8301, 'GHR_38254_NO_CALC_PRD');
4314 hr_utility.set_message_token('PRD',p_pay_calc_data.pay_rate_determinant);
4315 raise ghr_pay_calc.unable_to_calculate;
4316 END IF;
4317 -- Bug# 4866952 Added the new pay calculation method for Pay Adjustment actions
4318 -- Processed after 01-MAY-2005
4319 IF p_pay_calc_data.effective_date = TO_DATE('2005/05/01','YYYY/MM/DD') THEN
4320 l_new_basic_pay := p_pay_calc_data.current_adj_basic_pay;
4321 l_new_locality_adj := 0;
4322 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4323 get_special_pay_table_value (p_pay_plan => l_pay_plan
4324 ,p_grade_or_level => l_grade_or_level
4325 ,p_step_or_rate => l_step_or_rate
4326 ,p_user_table_id => l_user_table_id
4327 ,p_effective_date => p_pay_calc_data.effective_date
4328 ,p_pt_value => l_pt_value
4329 ,p_PT_eff_start_date => l_pt_eff_start_date
4330 ,p_PT_eff_end_date => l_pt_eff_end_date
4331 ,p_pp_grd_exists => l_pp_grd_exists );
4332 set_cpt_ppt_prd ( p_leo_flag => l_leo_flag
4333 ,p_rg_flag => l_rg_flag
4334 ,p_pp_grd_exists => l_pp_grd_exists
4335 ,p_loc_rate_less_spl_rate => NULL
4336 ,p_prd => p_pay_calc_data.pay_rate_determinant
4337 ,p_pt_value => l_pt_value
4338 ,p_special_rate_table => l_user_table_name
4339 ,p_calculation_pay_table => l_calculation_pay_table
4340 ,p_position_pay_table => l_position_pay_table
4341 ,p_retained_pay_table => l_retained_pay_table
4342 ,p_new_prd => l_new_prd
4343 );
4344 ELSE--IF p_pay_calc_data.effective_date
4345 l_term_ret_pay_prd := FALSE;
4346 hr_utility.set_location('PAY ADJUSTMENT AS ON 08-JAN',7788);
4347 --CODE CHANGES DONE TO ATLER PAY CALC FOR BUG# 4999237...
4348 IF l_pay_plan = 'GM' THEN
4349 l_pay_plan := 'GS';
4350 --- Bug 5908487 changes...
4351 l_default_pay_plan := 'GS';
4352 l_gm_pay_plan := TRUE;
4353 --Begin Bug 7046241
4354 ELSIF l_pay_plan = 'GR' THEN
4355 l_pay_plan := 'GS';
4356 l_default_pay_plan := 'GS';
4357 l_gr_pay_plan := TRUE;
4358 --End Bug 7046241
4359 END IF;
4360 -- Get Current Years' Special Pay Table Value at step 10
4361
4362 hr_utility.set_location('Get Current Years Special Pay Table Value at step 10',7788);
4363
4364 get_special_pay_table_value (p_pay_plan => l_pay_plan
4365 ,p_grade_or_level => l_grade_or_level
4366 ,p_step_or_rate => '10'
4367 ,p_user_table_id => l_user_table_id
4368 ,p_effective_date => p_pay_calc_data.effective_date
4369 ,p_pt_value => l_basic_pay
4370 ,p_PT_eff_start_date => l_pt_eff_start_date
4371 ,p_PT_eff_end_date => l_pt_eff_end_date
4372 ,p_pp_grd_exists => l_pp_grd_exists );
4373
4374
4375
4376
4377 l_special_rate := l_basic_pay;
4378
4379 hr_utility.set_location('GOt Current Years Special Pay Table Value at step 10'|| l_special_rate,7788);
4380 --Now Get current Years's default pay table value.for max rate....BUG# 4999237.
4381 hr_utility.set_location('Now Get current Years default pay table value.for max rate.',7788);
4382
4383 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4384 ,p_pay_plan =>l_default_pay_plan
4385 ,p_grade_or_level =>l_grade_or_level
4386 ,p_step_or_rate =>'10'
4387 ,p_effective_date =>p_pay_calc_data.effective_date
4388 ,p_PT_value => l_basic_pay
4389 ,p_PT_eff_start_date => l_PT_eff_start_date
4390 ,p_PT_eff_end_date => l_dummy_date);
4391
4392
4393 hr_utility.set_location('Now GOt current Years default pay table value.for max rate.'||l_basic_pay ,7788);
4394
4395
4396
4397 -----APPLY LOCALITY % TO NEW DEFAULT TABLE VALUE.....l_adj_perc_factor is already calculated
4398 hr_utility.set_location('APPLY LOCALITY % TO NEW DEFAULT TABLE VALUE' ,7788);
4399
4400 l_locality_adj := ROUND(l_basic_pay * l_adj_perc_factor,0);
4401 hr_utility.set_location('GETTING LOCALITY ADJUSTMENT'||l_locality_adj ,7788);
4402 l_adj_basic_pay := l_basic_pay + l_locality_adj;
4403 hr_utility.set_location('GETTING ADJUSTED BASIC PAY'||l_adj_basic_pay ,7788);
4404 ----Now compare special rate with basic pay......BUG# 4999237.
4405 hr_utility.set_location('COMPARING SPL RATE WITH ADJ BASIC PAY',7788);
4406 IF NVL(l_special_rate,0) > l_adj_basic_pay THEN
4407
4408 hr_utility.set_location(' SPL RATE IS > ADJ BASIC PAY',7788);
4409 --Pick the special rate ......BUG# 4999237.
4410 --From Now on Calculation is as usual...BUG# 4999237.
4411 --get previous year's special rate value.at step 10 ....BUG# 4999237.
4412 hr_utility.set_location('get previous years special rate value.at step 10',7788);
4413
4414 get_special_pay_table_value(p_pay_plan => l_pay_plan
4415 ,p_grade_or_level => l_grade_or_level
4416 ,p_step_or_rate => '10'
4417 ,p_user_table_id => l_user_table_id
4418 ,p_effective_date => p_pay_calc_data.effective_date - 1
4419 ,p_pt_value => l_old_basic_pay
4420 ,p_PT_eff_start_date => l_pt_eff_start_date
4421 ,p_PT_eff_end_date => l_pt_eff_end_date
4422 ,p_pp_grd_exists => l_pp_grd_exists );
4423
4424 l_old_special_rate := l_old_basic_pay; --for bug#4999237...
4425
4426 hr_utility.set_location('gOt previous years special rate value.at step 10'||l_old_special_rate,7788);
4427
4428
4429 hr_utility.set_location('::: 5010491 BP::: '||l_basic_pay,10);
4430 hr_utility.set_location('::: 5010491 ABP::: '||l_old_basic_pay,20);
4431 hr_utility.set_location('::: 5010491 UTN::: '||l_user_table_name,30);
4432 -- If both current and previous year's special rate step 10 values are NOT NULL
4433 -- Then new basic = current basic + (new special rate step10 value - old special rate step 10 value/2)
4434
4435 IF l_old_basic_pay IS NOT NULL AND l_special_rate IS NOT NULL AND
4436
4437 l_user_table_name NOT IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) THEN
4438
4439 l_increment := Round((l_special_rate - l_old_basic_pay)/2,0);
4440 hr_utility.set_location('INCREMENTED HALF VALUE'||l_increment,7788);
4441 l_new_basic_pay := p_pay_calc_data.current_basic_pay + l_increment;
4442 hr_utility.set_location('NEW BASIC PAY'||l_new_basic_pay,7788);
4443 l_new_locality_adj := 0;
4444 hr_utility.set_location('LOCALITY ADJUSTMENT SET TO ZERO',7788);
4445 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4446 l_new_prd := p_pay_calc_data.pay_rate_determinant;
4447 hr_utility.set_location('NEW PRD SET'||l_new_prd ,7788);
4448 l_calculation_pay_table := l_user_table_name;
4449 --6814842 modified to update position pay table and pay_table_upd_flag
4450 l_position_pay_table := l_user_table_name;
4451 g_pay_table_upd_flag := FALSE;
4452
4453
4454 hr_utility.set_location('COMPARING NEW BASIC PAY AND NEW SPECIAL RATE VALUE',7788);
4455
4456 IF l_new_basic_pay <= l_special_rate THEN
4457 l_new_adj_basic_pay := l_special_rate;
4458 hr_utility.set_location('SPECIAL RATE IS GREATER'||l_special_rate,7788);
4459 hr_utility.set_location('get current value for step10 in default table',7788);
4460 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4461 ,p_pay_plan =>l_default_pay_plan
4462 ,p_grade_or_level =>l_grade_or_level
4463 ,p_step_or_rate => '10'
4464 ,p_effective_date =>p_pay_calc_data.effective_date
4465 ,p_PT_value => l_new_basic_pay
4466 ,p_PT_eff_start_date => l_PT_eff_start_date
4467 ,p_PT_eff_end_date => l_dummy_date);
4468
4469 hr_utility.set_location('gOt current value for step10 in default table'||l_new_basic_pay,7788);
4470 hr_utility.set_location('l_new_basic_pay: '|| l_new_basic_pay,95);
4471 l_new_locality_adj := l_new_adj_basic_pay - l_new_basic_pay;
4472 hr_utility.set_location('SETTING NEW STEP'||l_new_step,7788);
4473 l_new_step := '10';
4474 hr_utility.set_location('terminate PAY RETENTION',7788);
4475 l_term_ret_pay_prd := TRUE;
4476 hr_utility.set_location('l_new_locality_adj: '|| l_new_locality_adj,95);
4477 END IF;
4478 END IF;--IF l_old_basic_pay IS NOT
4479
4480
4481 ELSIF (l_adj_basic_pay >= nvl(l_special_rate,0)) OR (l_old_special_rate IS NULL) then--IF NVL(l_special_rate,0)
4482
4483 hr_utility.set_location('special rate is lesser or old special rate is not available',7788);
4484
4485 hr_utility.set_location('::: 50101491 :::Inside Else Condition',40);
4486 l_old_adj_perc_factor := (NVL(ghr_pay_calc.get_lpa_percentage(p_pay_calc_data.duty_station_id
4487 ,p_pay_calc_data.effective_date - 1
4488 ),0))/100;
4489
4490
4491
4492 hr_utility.set_location('l_old_adj_perc_factor ..' || to_char(l_old_adj_perc_factor),32);
4493 hr_utility.set_location('l_default_table_id: '||l_default_table_id,95);
4494 hr_utility.set_location(' l_pay_plan: '||l_pay_plan,95);
4495 hr_utility.set_location(' l_default_pay_plan: '||l_default_pay_plan,95);
4496 hr_utility.set_location(' l_grade_or_level: '||l_grade_or_level,95);
4497 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4498 ,p_pay_plan =>l_default_pay_plan
4499 ,p_grade_or_level =>l_grade_or_level
4500 ,p_step_or_rate => '10'
4501 ,p_effective_date =>p_pay_calc_data.effective_date - 1
4502 ,p_PT_value => l_old_basic_pay
4503 ,p_PT_eff_start_date => l_PT_eff_start_date
4504 ,p_PT_eff_end_date => l_dummy_date);
4505
4506
4507 hr_utility.set_location('old default table value '||l_old_basic_pay ,7788);
4508 hr_utility.set_location('Old basic at step 10: '||to_char(l_old_BASIC_PAY),90);
4509 l_old_locality_adj := ROUND(l_old_basic_pay * l_old_adj_perc_factor,0);
4510 l_old_adj_basic_pay := l_old_basic_pay + l_old_locality_adj;
4511 hr_utility.set_location('Old adj basic at step 10: '||to_char(l_old_adj_basic_pay),95);
4512 hr_utility.set_location('Old locality at step 10: '||to_char(l_old_locality_adj),95);
4513 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4514 ,p_pay_plan =>l_default_pay_plan
4515 ,p_grade_or_level =>l_grade_or_level
4516 ,p_step_or_rate =>'10'
4517 ,p_effective_date =>p_pay_calc_data.effective_date
4518 ,p_PT_value => l_basic_pay
4519 ,p_PT_eff_start_date => l_PT_eff_start_date
4520 ,p_PT_eff_end_date => l_dummy_date);
4521
4522
4523 hr_utility.set_location('New default table value '||l_basic_pay ,7788);
4524 l_locality_adj := ROUND(l_basic_pay * l_adj_perc_factor,0);
4525 l_adj_basic_pay := l_basic_pay + l_locality_adj;
4526 l_increment := Round((l_adj_basic_pay - l_old_adj_basic_pay)/2,0);
4527 l_new_basic_pay := p_pay_calc_data.current_basic_pay + l_increment;
4528 l_new_locality_adj := 0;
4529 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4530 l_new_prd := p_pay_calc_data.pay_rate_determinant;
4531 hr_utility.set_location(' adj basic at step 10: '||to_char(l_adj_basic_pay),95);
4532 hr_utility.set_location(' locality at step 10: '||to_char(l_locality_adj),95);
4533 l_calculation_pay_table := l_default_pay_table;
4534 --6814842 modified to update position pay table and pay_table_upd_flag
4535 l_position_pay_table := l_default_pay_table;
4536 g_pay_table_upd_flag := TRUE;
4537 IF l_new_adj_basic_pay <= l_adj_basic_pay THEN
4538 hr_utility.set_location('new adjusted basic pay is less than adjusted basic pay',7788);
4539 l_new_basic_pay := l_basic_pay;
4540
4541 hr_utility.set_location('new basic pay '||l_new_basic_pay,7788);
4542
4543 l_new_locality_adj := l_locality_adj;
4544 hr_utility.set_location('l_new_locality_adj'||l_locality_adj,7788);
4545 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4546 hr_utility.set_location('l_new_adj_basic_pay'||l_new_adj_basic_pay,7788);
4547 --Bug 7046241 added l_gr_pay_plan condition
4548 IF l_gm_pay_plan OR l_gr_pay_plan THEN
4549 l_new_step := '00';
4550 ELSE
4551 l_new_step := '10';
4552 END IF;
4553 hr_utility.set_location('l_new_step'||l_new_step,7788);
4554 l_term_ret_pay_prd := TRUE;
4555 hr_utility.set_location('terminate PRD',7788);
4556 END IF;
4557 END IF; --IF NVL(l_special_rate,0)
4558 -- Set the PRD if Pay Retention is terminated.
4559 -- 7046241
4560 IF l_term_ret_pay_prd and l_gr_pay_plan and l_calculation_pay_table = ghr_pay_calc.l_standard_table_name then
4561 ghr_history_fetch.fetch_positionei(
4562 p_position_id => p_pay_calc_data.position_id,
4563 p_information_type => 'GHR_US_POS_VALID_GRADE',
4564 p_date_effective => p_pay_calc_data.effective_date,
4565 p_pos_ei_data => l_pos_ei_valid_grade);
4566 IF NOT (ghr_pay_caps.pay_cap_chk_ttl_38( l_pos_ei_valid_grade.poei_information12,
4567 l_pos_ei_valid_grade.poei_information13,
4568 p_pay_calc_data.current_adj_basic_pay,
4569 p_pay_calc_data.effective_date)) THEN
4570 l_term_ret_pay_prd := FALSE;
4571
4572 END IF;
4573 END IF;
4574 -- 7046241
4575 IF l_term_ret_pay_prd THEN
4576 IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','R') THEN
4577 IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4578 l_new_prd := '0';
4579 ELSE
4580 l_new_prd := '6';
4581 END IF;
4582 ELSIF p_pay_calc_data.pay_rate_determinant = 'U' THEN
4583 IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4584 l_new_prd := 'B';
4585 ELSE
4586 l_new_prd := 'F';
4587 END IF;
4588 ELSIF p_pay_calc_data.pay_rate_determinant = 'V' THEN
4589 IF l_calculation_pay_table = ghr_pay_calc.l_standard_table_name THEN
4590 l_new_prd := 'A';
4591 ELSE
4592 l_new_prd := 'E';
4593 END IF;
4594 END IF;
4595 END IF;
4596
4597 END IF;--IF p_pay_calc_data.effective_date
4598 -- Get the Calculation Pay Table ID
4599 FOR table_rec IN c_get_table_id(l_calculation_pay_table)
4600 LOOP
4601 l_calculation_pay_table_id := table_rec.user_table_id;
4602 END LOOP;
4603 p_pay_calc_out_data.calculation_pay_table_id := l_calculation_pay_table_id;
4604 hr_utility.set_location(':: 5010491 :: leaving the pay retention',100);
4605 IF l_gm_pay_plan THEN
4606 l_pay_plan := 'GM';
4607 --- Bug 5908487 Changes.
4608 l_default_pay_plan := 'GM';
4609 l_gm_pay_plan := FALSE;
4610 --Begin Bug 7046241
4611 ELSIF l_gr_pay_plan THEN
4612 l_pay_plan := 'GR';
4613 l_default_pay_plan := 'GR';
4614 l_gr_pay_plan := FALSE;
4615 --End Bug 7046241
4616 END IF;
4617
4618 ELSE ---- IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R')
4619 -- 5. Processing Employees on GM Pay Plan.
4620 --Bug# 7518210 added GR
4621 IF l_pay_plan IN ('GM','GH','GR') THEN
4622 -- AC added the IF condition for opening teh pay fields in few actions Bug#5114467
4623 IF p_pay_calc_data.noa_family_code IN ('APP','CHG_DUTY_STATION','CONV_APP','EXT_NTE','POS_CHG'
4624 ,'REALIGNMENT','REASSIGNMENT', 'RETURN_TO_DUTY') THEN
4625 hr_utility.set_message(8301, 'GHR_38260_NO_CALC_PAY_PLAN');
4626 hr_utility.set_message_token('PAY_PLAN',l_pay_plan);
4627 raise ghr_pay_calc.unable_to_calculate;
4628 END IF;
4629 -- Added this IF condition Bug#5114467
4630 IF l_pay_plan NOT IN ('GM','GR') THEN
4631 --Bug# 7518210 added GR
4632 l_new_Position_Pay_Table := ghr_pay_calc.l_standard_table_name;
4633 l_calculation_pay_table := ghr_pay_calc.l_standard_table_name;
4634 g_pay_table_upd_flag := TRUE;
4635 l_new_prd := '0';
4636 l_new_basic_pay := ROUND(p_pay_calc_data.current_adj_basic_pay / (1 + l_adj_perc_factor),0);
4637 l_new_locality_adj := ROUND(l_new_basic_pay * l_adj_perc_factor);
4638 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4639 ELSE
4640 IF p_pay_calc_data.noa_code= 894 THEN
4641 --AC -- Code for 894 Special Emp.
4642 hr_utility.set_location('AC Grade' || l_grade_or_level,1000);
4643 hr_utility.set_location('AC User Table id' || l_user_table_id,1000);
4644 ghr_pc_basic_pay.get_894_GM_sp_basic_pay (p_grade_or_level => l_grade_or_level
4645 ,p_effective_date => p_pay_calc_data.effective_date
4646 ,p_user_table_id => l_user_table_id
4647 ,p_default_table_id => l_default_table_id
4648 ,p_curr_basic_pay => p_pay_calc_data.current_basic_pay
4649 ,p_duty_station_id => p_pay_calc_data.duty_station_id
4650 ,p_new_basic_pay => l_new_basic_pay
4651 ,p_new_adj_basic_pay => l_new_adj_basic_pay
4652 ,p_new_locality_adj => l_new_locality_adj
4653 ,p_new_special_rate => l_new_special_rate
4654 );
4655 ELSE
4656 --AC -- Code for WGI Special Emp.
4657 ghr_pc_basic_pay.get_wgi_GM_sp_basic_pay (p_grade_or_level => l_grade_or_level
4658 ,p_effective_date => p_pay_calc_data.effective_date
4659 ,p_user_table_id => l_user_table_id
4660 ,p_default_table_id => l_default_table_id
4661 ,p_curr_basic_pay => p_pay_calc_data.current_basic_pay
4662 ,p_duty_station_id => p_pay_calc_data.duty_station_id
4663 ,p_new_basic_pay => l_new_basic_pay
4664 ,p_new_adj_basic_pay => l_new_adj_basic_pay
4665 ,p_new_locality_adj => l_new_locality_adj
4666 );
4667 END IF;
4668 END IF;
4669
4670 /*ELSIF g_fw_equiv_pay_plan AND p_pay_calc_data.pay_rate_determinant IN ('6','E','F') THEN -- Bug 5218445
4671 get_special_pay_table_value (p_pay_plan => l_pay_plan
4672 ,p_grade_or_level => l_grade_or_level
4673 ,p_step_or_rate => l_step_or_rate
4674 ,p_user_table_id => l_user_table_id
4675 ,p_effective_date => p_pay_calc_data.effective_date
4676 ,p_pt_value => l_pt_value
4677 ,p_PT_eff_start_date => l_pt_eff_start_date
4678 ,p_PT_eff_end_date => l_pt_eff_end_date
4679 ,p_pp_grd_exists => l_pp_grd_exists );
4680 l_new_basic_pay := l_pt_value;
4681 l_new_locality_adj := 0;
4682 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4683 l_calculation_pay_table := get_user_table_name(l_user_table_id);
4684 */
4685 ELSE----IF l_pay_plan IN ('GM'
4686 -- 6. Processing Employees on pay plans other than GM.
4687
4688
4689 -- Processing for InterimWGI/WGI/QSI.
4690 -- Bug#5566896Added NOA Code 867.
4691 IF p_pay_calc_data.noa_code IN ('867','892','893') THEN
4692 l_ret_flag := FALSE;
4693 l_curr_step_or_rate := l_step_or_rate;
4694 -- FWFA Changes NEW
4695 IF p_pay_calc_data.pay_rate_determinant IN ('A','B','E','F') THEN
4696 if ghr_pc_basic_pay.g_noa_family_code ='CORRECT' then
4697 if p_retained_grade.temp_step is not null then
4698 -- Bug 3021003 Start
4699 is_retained_ia(p_pay_calc_data.person_id,
4700 p_pay_calc_data.effective_date,
4701 l_pay_plan,
4702 l_grade_or_level,
4703 l_step_or_rate,
4704 p_retained_grade.temp_step,
4705 l_ret_flag);
4706 IF l_ret_flag = TRUE then
4707 hr_utility.set_location('NAR ret step ' ||p_retained_grade.temp_step,10);
4708 hr_utility.set_location('NAR pay plan '||l_pay_plan,20);
4709 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
4710 ,p_retained_grade.temp_step);
4711 hr_utility.set_location('NAR new step after getting the step ' ||l_step_or_rate,10);
4712 -- End Bug 3021003
4713 ELSE
4714 l_step_or_rate := p_retained_grade.temp_step;
4715 END IF;
4716 else
4717 -- Bug 3021003
4718 is_retained_ia(p_pay_calc_data.person_id,
4719 p_pay_calc_data.effective_date,
4720 l_pay_plan,
4721 l_grade_or_level,
4722 l_step_or_rate,
4723 p_retained_grade.temp_step,
4724 l_ret_flag);
4725 IF l_ret_flag = TRUE then
4726 hr_utility.set_location('NAR ret step ' ||l_step_or_rate,10);
4727 hr_utility.set_location('NAR pay plan '||l_pay_plan,20);
4728 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
4729 ,l_step_or_rate);
4730 hr_utility.set_location('NAR new step after getting the step ' ||l_step_or_rate,10);
4731 ELSE
4732 l_step_or_rate := l_step_or_rate;
4733 END IF; -- IF is_retained_ia End Bug 3021003
4734 END IF; -- if p_retained_grade.temp_step is not null
4735 else--family_code ='CORRECT'
4736 if p_retained_grade.temp_step is not null then
4737
4738 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
4739 ,p_retained_grade.temp_step);
4740 else
4741 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (l_pay_plan
4742 ,p_retained_grade.step_or_rate);
4743 end if;
4744 end if;--family_code ='CORRECT'
4745 ELSE --('A','B','E','F') THEN
4746 --if ghr_pc_basic_pay.g_noa_family_code ='CORRECT' then
4747 -- l_step_or_rate := l_curr_step_or_rate;
4748 --ELSE
4749 l_step_or_rate := ghr_pc_basic_pay.get_next_WGI_step (p_pay_plan => l_pay_plan
4750 ,p_current_step => l_curr_step_or_rate);
4751 --END IF;
4752 END IF; --('A','B','E','F') THEN
4753 -- FWFA Changes NEW
4754 hr_utility.set_location('l_step_or_rate: '||l_step_or_rate,11111111);
4755 -- Bug#5444558 Modified the assignment of step value. Assigned the step into local
4756 -- variable l_new_step here. Assign this value to p_pay_calc_out_data.out_step_or_rate
4757 -- at the end of the pay calc.
4758 IF p_pay_calc_data.pay_rate_determinant IN ('E','F','A','B') THEN
4759 l_new_step := '00';
4760 ELSE
4761 l_new_step := l_step_or_rate;
4762 END IF;
4763 END IF;-- ('867','892','893') THEN
4764
4765
4766 IF g_fw_equiv_pay_plan THEN -- Begin Bug 5608741
4767 get_special_pay_table_value (p_pay_plan => l_pay_plan
4768 ,p_grade_or_level => l_grade_or_level
4769 ,p_step_or_rate => l_step_or_rate
4770 ,p_user_table_id => l_user_table_id
4771 ,p_effective_date => p_pay_calc_data.effective_date
4772 ,p_pt_value => l_pt_value
4773 ,p_PT_eff_start_date => l_pt_eff_start_date
4774 ,p_PT_eff_end_date => l_pt_eff_end_date
4775 ,p_pp_grd_exists => l_pp_grd_exists );
4776 l_new_basic_pay := l_pt_value;
4777 l_new_locality_adj := 0;
4778 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4779 l_calculation_pay_table := get_user_table_name(l_user_table_id);
4780 ELSE --End Bug# 5608741
4781 -- Processing of NOACs other than WGI/QSI
4782 IF l_user_table_name IN (ghr_pay_calc.l_standard_table_name,ghr_pay_calc.l_spl491_table_name) THEN
4783 l_pay_calc_process := 'L';
4784 ELSE
4785 hr_utility.set_location('user tbale id: '||to_char(l_user_table_id),40);
4786 hr_utility.set_location('pay Plan: '||l_pay_plan,50);
4787 hr_utility.set_location('grade: '||l_grade_or_level,60);
4788 hr_utility.set_location('step : '||l_step_or_rate,70);
4789 get_special_pay_table_value (p_pay_plan => l_pay_plan
4790 ,p_grade_or_level => l_grade_or_level
4791 ,p_step_or_rate => l_step_or_rate
4792 ,p_user_table_id => l_user_table_id
4793 ,p_effective_date => p_pay_calc_data.effective_date
4794 ,p_pt_value => l_pt_value
4795 ,p_PT_eff_start_date => l_pt_eff_start_date
4796 ,p_PT_eff_end_date => l_pt_eff_end_date
4797 ,p_pp_grd_exists => l_pp_grd_exists );
4798 hr_utility.set_location('value '||to_char(l_pt_value),80);
4799 ghr_pay_calc.get_pay_table_value(p_user_table_id =>l_default_table_id
4800 ,p_pay_plan =>l_default_pay_plan
4801 ,p_grade_or_level =>l_grade_or_level
4802 ,p_step_or_rate =>l_step_or_rate
4803 ,p_effective_date =>p_pay_calc_data.effective_date
4804 ,p_PT_value => l_basic_pay
4805 ,p_PT_eff_start_date => l_PT_eff_start_date
4806 ,p_PT_eff_end_date => l_dummy_date);
4807 hr_utility.set_location('std basic: '||to_char(l_BASIC_PAY),90);
4808
4809 l_locality_adj := ROUND(l_basic_pay * (NVL(l_adjustment_percentage,0)/100),0);
4810 l_adj_basic_pay := l_basic_pay + l_locality_adj;
4811
4812 -- IF Pay Plan Grade Exists and Step not exists
4813 IF l_pp_grd_exists THEN
4814 IF l_pt_value is NULL THEN
4815 l_pay_calc_process := 'L';
4816 l_loc_rate_less_spl_rate := NULL;
4817 ELSE
4818 IF l_pt_value > l_adj_basic_pay THEN
4819 l_pay_calc_process := 'S';
4820 l_calculation_pay_table := l_user_table_name;
4821 l_loc_rate_less_spl_rate := TRUE;
4822 ELSE
4823 l_pay_calc_process := 'L';
4824 l_loc_rate_less_spl_rate := FALSE;
4825 END IF;
4826 END IF;
4827 ELSE
4828 l_pay_calc_process := 'L';
4829 l_loc_rate_less_spl_rate := NULL;
4830 END IF;
4831 END IF; --l_user_table_name IN
4832 hr_utility.set_location('process :'||l_pay_calc_process,100);
4833 IF g_pay_table_upd_flag THEN
4834 hr_utility.set_location('Before SET_CPT Pay Table UPD Flag TRUE',101);
4835 ELSE
4836 hr_utility.set_location('Before SET_CPT Pay Table UPD Flag FALSE',102);
4837 END IF;
4838 set_cpt_ppt_prd ( p_leo_flag => l_leo_flag
4839 ,p_rg_flag => l_rg_flag
4840 ,p_pp_grd_exists => l_pp_grd_exists
4841 ,p_loc_rate_less_spl_rate => l_loc_rate_less_spl_rate
4842 ,p_prd => p_pay_calc_data.pay_rate_determinant
4843 ,p_pt_value => l_pt_value
4844 ,p_special_rate_table => l_user_table_name
4845 ,p_calculation_pay_table => l_calculation_pay_table
4846 ,p_position_pay_table => l_position_pay_table
4847 ,p_retained_pay_table => l_retained_pay_table
4848 ,p_new_prd => l_new_prd
4849 );
4850 IF g_pay_table_upd_flag THEN
4851 hr_utility.set_location('After SET_CPT Pay Table UPD Flag TRUE',101);
4852 ELSE
4853 hr_utility.set_location('After SET_CPT Pay Table UPD Flag FALSE',102);
4854 END IF;
4855 hr_utility.set_location('calc table: '||l_calculation_pay_table,110);
4856 hr_utility.set_location('posn table: '||L_position_pay_table,120);
4857 hr_utility.set_location('retd table: '||l_retained_pay_table,130);
4858 hr_utility.set_location('new prd : '||l_new_prd,140);
4859
4860
4861 -- get_basic_pay
4862 hr_utility.set_location('2. l_step_or_rate: '||l_step_or_rate,999999);
4863 ghr_pay_calc.get_pay_table_value(p_user_table_id => l_default_table_id
4864 ,p_pay_plan => l_default_pay_plan
4865 ,p_grade_or_level => l_grade_or_level
4866 ,p_step_or_rate => l_step_or_rate
4867 ,p_effective_date => p_pay_calc_data.effective_date
4868 ,p_PT_value => l_basic_pay
4869 ,p_PT_eff_start_date => l_PT_eff_start_date
4870 ,p_PT_eff_end_date => l_dummy_date);
4871 l_new_basic_pay := l_basic_pay;
4872 hr_utility.set_location('std basic: '||to_char(l_new_BASIC_PAY),10);
4873 hr_utility.set_location('ADJ Perc : '||to_char(l_adj_perc_factor),20);
4874 -- get_locality_or_supplement/ Adj Basic Pay
4875 IF l_pay_calc_process = 'L' THEN
4876 hr_utility.set_location('Inside Locality Calculation',30);
4877 l_new_locality_adj := ROUND(l_new_basic_pay * l_adj_perc_factor);
4878 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4879 ELSIF l_pay_calc_process = 'S' THEN
4880 hr_utility.set_location('Inside Special Rate Calculation',40);
4881 l_new_locality_adj := l_pt_value - l_new_basic_pay;
4882 l_new_adj_basic_pay := l_new_basic_pay + l_new_locality_adj;
4883 END IF;
4884 END IF;--End of if Bug 5608741
4885 END IF;----IF l_pay_plan IN ('GM'
4886 END IF; ---- IF p_pay_calc_data.pay_rate_determinant IN ('3','4','J','K','U','V','R')
4887 FOR table_rec IN c_get_table_id(l_calculation_pay_table)
4888 LOOP
4889 l_calculation_pay_table_id := table_rec.user_table_id;
4890 END LOOP;
4891
4892 hr_utility.set_location('new Basic : '||to_char(l_new_basic_pay),10);
4893 hr_utility.set_location('new LOC : '||to_char(l_new_locality_adj),20);
4894 hr_utility.set_location('new ADJ BA : '||to_char(l_new_adj_basic_pay),30);
4895 hr_utility.set_location('new PRD : '||l_new_prd,40);
4896 hr_utility.set_location('new Step : '||l_new_step,50);
4897 --Bug#5089732 Set the Pay Rate Determinant for GL,GG Pay Plans
4898 IF l_calculation_pay_table = ghr_pay_calc.l_spl491_table_name AND
4899 l_pay_plan IN ('GL','GG') AND
4900 l_new_prd IN ('6','E','F') THEN
4901 IF l_new_prd = '6' THEN
4902 l_new_prd := '0';
4903 ELSIF l_new_prd = 'E' THEN
4904 l_new_prd := 'A';
4905 ELSE
4906 l_new_prd := 'B';
4907 END IF;
4908 --Bug#5435217 Set this flag to true to avoid unnecessary creation of RG record.
4909 g_gl_upd_flag := TRUE;
4910 END IF;
4911
4912 -- AC : Bug#5114467 Added codition for For Regular Emp. in GM pay table and 894 action
4913 -- And Code for determining Calculation pay table For Regular Emp. in GM pay table and 894 action
4914 IF l_pay_plan = 'GM' AND p_pay_calc_data.noa_code= 894 THEN
4915 -- AC : Bug#5725928 - Added the condition so that the pay adjustment process should be
4916 -- doing the comparison of locality verses special rate only if the pay table is not 0000
4917 IF l_calculation_pay_table <> ghr_pay_calc.l_standard_table_name THEN
4918 IF l_new_locality_adj > l_new_special_rate THEN
4919 l_new_prd := '0';
4920 ELSE
4921 l_new_prd := '6';
4922 END IF;
4923 END IF;
4924 IF l_new_prd IN ('0','A','B') THEN
4925 l_calculation_pay_table_id := l_default_table_id;
4926 ELSIF l_new_prd IN ('6','E','F') THEN
4927 l_calculation_pay_table_id := l_user_table_id;
4928 END IF;
4929 END IF;
4930
4931 -- Bug#4699932 Added the OR clause to IF condition.
4932 -- Set the out_pay_rate_determinant as NULL if there is no change in PRD.
4933 IF p_pay_calc_data.pay_rate_determinant = l_new_prd OR
4934 (p_pay_calc_data.noa_family_code IN ('APP','CONV_APP') AND
4935 p_pay_calc_data.pay_rate_determinant IN ('5','7')
4936 ) THEN
4937 l_new_prd := NULL;
4938 END IF;
4939
4940 -- SET OUT PARAMETERS
4941 hr_utility.set_location('new PRD : '||l_new_prd,40);
4942 hr_utility.set_location('new Step : '||l_new_step,50);
4943 hr_utility.set_location('new l_calculation_pay_table_id : '|| l_calculation_pay_table_id,50);
4944 p_pay_calc_out_data.basic_pay := l_new_basic_pay;
4945 p_pay_calc_out_data.locality_adj := l_new_locality_adj;
4946 p_pay_calc_out_data.adj_basic_pay := l_new_adj_basic_pay;
4947 p_pay_calc_out_data.out_pay_rate_determinant := l_new_prd;
4948 p_pay_calc_out_data.calculation_pay_table_id := l_calculation_pay_table_id;
4949 p_pay_calc_out_data.out_step_or_rate := l_new_step;
4950 -- Use the Pay Table ID as the input table for Pay Calculation.
4951 p_pay_calc_out_data.pay_table_id := l_user_table_id;
4952
4953 ------ Bug#5741977 start
4954 IF l_fwfa_pay_plan_changed THEN
4955 IF nvl(l_new_prd,'X') in ('A','B','E','F','U','V') AND
4956 p_retained_grade.temp_step IS NULL THEN
4957 g_out_to_pay_plan := l_default_pay_plan;
4958 ELSIF l_grade_id is not null then
4959 p_pay_calc_out_data.out_to_grade_id := l_grade_id;
4960 p_pay_calc_out_data.out_to_pay_plan := l_default_pay_plan;
4961 p_pay_calc_out_data.out_to_grade_or_level := l_grade_or_level;
4962 END IF;
4963 END IF;
4964 ------ Bug#5741977 End
4965 EXCEPTION
4966 WHEN OTHERS THEN
4967 RAISE;
4968 END special_rate_pay_calc;
4969
4970 -- Bug# 4748927 Begin
4971 PROCEDURE award_amount_calc (
4972 p_position_id IN NUMBER
4973 ,p_pay_plan IN VARCHAR2
4974 ,p_award_percentage IN NUMBER
4975 ,p_user_table_id IN NUMBER
4976 ,p_grade_or_level IN VARCHAR2
4977 ,p_effective_date IN DATE
4978 ,p_basic_pay IN NUMBER
4979 ,p_adj_basic_pay IN NUMBER
4980 ,p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
4981 ,p_prd IN ghr_pa_requests.pay_rate_determinant%type
4982 ,p_pay_basis IN VARCHAR2
4983 ,p_person_id IN per_people_f.person_id%TYPE
4984 ,p_award_amount OUT NOCOPY NUMBER
4985 ,p_award_salary OUT NOCOPY NUMBER
4986 ) IS
4987 l_pay_table VARCHAR2(4);
4988 l_PT_value NUMBER;
4989 l_PT_eff_start_date DATE;
4990 l_PT_eff_end_date DATE;
4991 l_equivalent_pay_plan ghr_pay_plans.equivalent_pay_plan%type;
4992 l_adjustment_percentage ghr_locality_pay_areas_f.adjustment_percentage%type;
4993 l_max_step ghr_pay_plans.maximum_step%type;
4994 l_locality_adj NUMBER;
4995 l_leo_flag BOOLEAN;
4996 l_std_user_table_id NUMBER;
4997 l_std_user_table_name varchar2(80);
4998 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
4999 l_pay_plan VARCHAR2(10);
5000 l_pay_basis VARCHAR2(10);
5001 l_grade_or_level VARCHAR2(60);
5002 l_user_table_id NUMBER;
5003 l_proc VARCHAR2(30) := 'award_amount_calc';
5004 l_special_rate NUMBER;
5005 l_pp_grd_exists BOOLEAN;
5006 --bug #5482191
5007 l_psi VARCHAR2(30);
5008 cursor c_gs_eq(pc_pay_plan VARCHAR2) is
5009 SELECT equivalent_pay_plan,maximum_step
5010 FROM ghr_pay_plans
5011 WHERE pay_plan = pc_pay_plan;
5012
5013 CURSOR get_user_table_id(p_user_table_name VARCHAR2) IS
5014 SELECT utb.user_table_id
5015 FROM pay_user_tables utb
5016 WHERE utb.user_table_name = p_user_table_name;
5017
5018 BEGIN
5019 hr_utility.set_location('Entering ...'|| l_proc,100);
5020 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_position_id,p_effective_date);
5021
5022 hr_utility.set_location('personnel system ...'|| l_psi,101);
5023
5024 IF l_psi <> '00' THEN --for NSPS
5025
5026 IF p_pay_basis = 'PA' THEN
5027 p_award_salary := ROUND(p_adj_basic_pay);
5028 ELSIF p_pay_basis = 'PH' THEN
5029 p_award_salary := ROUND(p_adj_basic_pay * 2087);
5030 ELSIF p_pay_basis = 'PM' THEN
5031 p_award_salary := ROUND(p_adj_basic_pay * 12);
5032 END IF;
5033
5034 ELSE --for non-NSPS
5035
5036 IF NVL(p_prd,'X') NOT IN ('A','B','E','F','U','V') THEN
5037 l_pay_plan := p_pay_plan;
5038 l_grade_or_level := p_grade_or_level;
5039 l_user_table_id := p_user_table_id;
5040 -- Bug#5237399 Added Pay basis
5041 l_pay_basis := p_pay_basis;
5042 ELSE
5043 BEGIN
5044 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (p_person_id,p_effective_date,NULL);
5045 --Mani Bug #6655566 Award Amount calculated on the Position pay basis if temp step is not null
5046 --otherwise on retained grade
5047 If NVL(p_prd,'X') IN ('A','B','E','F') AND
5048 l_retained_grade_rec.temp_step is not null then
5049 l_pay_basis := p_pay_basis;
5050 l_pay_plan := p_pay_plan;
5051 l_grade_or_level := p_grade_or_level;
5052 l_user_table_id := p_user_table_id;
5053 else
5054 l_pay_plan := l_retained_grade_rec.pay_plan;
5055 l_grade_or_level := l_retained_grade_rec.grade_or_level;
5056 l_user_table_id := l_retained_grade_rec.user_table_id;
5057 -- Bug#5237399 Added Pay basis
5058 l_pay_basis := l_retained_grade_rec.pay_basis;
5059
5060 END IF;
5061
5062 exception
5063 when others then
5064 hr_utility.set_location('Retained Exception raised ' || sqlerrm(sqlcode),25);
5065 END;
5066 END IF;
5067 for c1 in c_gs_eq(l_pay_plan) loop
5068 l_equivalent_pay_plan := c1.equivalent_pay_plan;
5069 l_max_step := c1.maximum_step;
5070 end loop;
5071
5072 IF p_prd not in('3','4','J','K','U','V','R') THEN
5073 IF (l_equivalent_pay_plan = 'FW' OR
5074 (l_pay_plan = 'AD' and l_pay_basis = 'PH') ) THEN
5075 p_award_salary := ROUND(p_adj_basic_pay * 2087);
5076 ELSE
5077 p_award_salary := ROUND(p_adj_basic_pay);
5078 END IF;
5079 ELSE
5080 l_pay_table := SUBSTR(ghr_pay_calc.get_user_table_name(l_user_table_id),1,4);
5081
5082
5083 IF (l_equivalent_pay_plan = 'GS') THEN
5084
5085 --bug #5529825
5086 if l_pay_plan = 'GM' then
5087 l_pay_plan := 'GS';
5088 l_max_step := '10';
5089 end if;
5090 --end bug#5529825
5091
5092
5093
5094 --code changes for bug 4999237
5095 ---CODE FOR GETTING SPECIAL TABLE RATE VALUES..........
5096 IF l_pay_table NOT IN('0491','0000') THEN
5097 get_special_pay_table_value (p_pay_plan => l_pay_plan
5098 ,p_grade_or_level => l_grade_or_level
5099 ,p_step_or_rate => l_max_step
5100 ,p_user_table_id => l_user_table_id
5101 ,p_effective_date => p_effective_date
5102 ,p_pt_value => l_pt_value
5103 ,p_PT_eff_start_date => l_pt_eff_start_date
5104 ,p_PT_eff_end_date => l_pt_eff_end_date
5105 ,p_pp_grd_exists => l_pp_grd_exists );
5106
5107
5108
5109 l_special_rate := l_pt_value;
5110 hr_utility.set_location('special rate '||l_special_rate,8877);
5111
5112 --code for getting default table values......
5113
5114
5115
5116 IF ghr_pay_calc.LEO_position (p_prd
5117 ,p_position_id
5118 ,l_std_user_table_id
5119 ,p_duty_station_id
5120 ,p_effective_date)
5121 AND p_grade_or_level between 03 and 10 THEN
5122 l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5123
5124 ELSE
5125 l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5126
5127 END IF;
5128 FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5129 l_std_user_table_id := c_rec.user_table_id;
5130 END LOOP;
5131
5132 ghr_pay_calc.get_pay_table_value (p_user_table_id => l_std_user_table_id
5133 ,p_pay_plan => l_pay_plan
5134 ,p_grade_or_level => l_grade_or_level
5135 ,p_step_or_rate => l_max_step
5136 ,p_effective_date => p_effective_date
5137 ,p_PT_value => l_PT_value
5138 ,p_PT_eff_start_date => l_PT_eff_start_date
5139 ,p_PT_eff_end_date => l_PT_eff_end_date
5140 );
5141 hr_utility.set_location('default rate '||l_PT_value,8877);
5142 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id,p_effective_date),0);
5143 hr_utility.set_location('l_adjustment_percentage '||l_adjustment_percentage,8877);
5144 l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5145
5146 l_pt_value := l_pt_value + l_locality_adj;
5147 hr_utility.set_location('final default rate '||l_pt_value,8877);
5148 --compare srt and locality rate..........
5149 if nvl(l_special_rate,0) < l_pt_value then
5150
5151 p_award_salary := ROUND(l_pt_value);
5152
5153 else
5154
5155 p_award_salary := ROUND(l_special_rate);
5156
5157 end if;
5158 ELSIF l_pay_table IN('0491','0000') THEN
5159
5160 BEGIN
5161 ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_user_table_id
5162 ,p_pay_plan => l_pay_plan
5163 ,p_grade_or_level => l_grade_or_level
5164 ,p_step_or_rate => l_max_step
5165 ,p_effective_date => p_effective_date
5166 ,p_PT_value => l_PT_value
5167 ,p_PT_eff_start_date => l_PT_eff_start_date
5168 ,p_PT_eff_end_date => l_PT_eff_end_date);
5169
5170 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id
5171 ,p_effective_date),0);
5172
5173 l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5174
5175 p_award_salary := ROUND(l_PT_value+l_locality_adj);
5176
5177
5178 EXCEPTION
5179 WHEN ghr_pay_calc.pay_calc_message THEN
5180 BEGIN
5181 IF ghr_pay_calc.LEO_position (p_prd
5182 ,p_position_id
5183 ,l_std_user_table_id
5184 ,p_duty_station_id
5185 ,p_effective_date)
5186 AND p_grade_or_level between 03 and 10 THEN
5187 l_std_user_table_name := Ghr_pay_calc.l_spl491_table_name;
5188
5189 ELSE
5190 l_std_user_table_name := Ghr_pay_calc.l_standard_table_name;
5191
5192 END IF;
5193 FOR c_rec IN get_user_table_id(l_std_user_table_name) LOOP
5194 l_std_user_table_id := c_rec.user_table_id;
5195 END LOOP;
5196 ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_std_user_table_id
5197 ,p_pay_plan => l_pay_plan
5198 ,p_grade_or_level => l_grade_or_level
5199 ,p_step_or_rate => l_max_step
5200 ,p_effective_date => p_effective_date
5201 ,p_PT_value => l_PT_value
5202 ,p_PT_eff_start_date => l_PT_eff_start_date
5203 ,p_PT_eff_end_date => l_PT_eff_end_date);
5204
5205 l_adjustment_percentage := NVL(ghr_pay_calc.get_lpa_percentage(p_duty_station_id
5206 ,p_effective_date),0);
5207 l_locality_adj := l_PT_value * (NVL(l_adjustment_percentage,0)/100);
5208 p_award_salary := ROUND(l_PT_value+l_locality_adj);
5209 END;
5210 END;
5211 --END IF; --l_pay_table IN('0491','0000')
5212
5213 END IF;
5214
5215
5216 --END IF;
5217 --end code changes for bug 4999237
5218
5219 -- Bug#5237399 Added the AD Pay Plan Condition.
5220 ELSIF (l_equivalent_pay_plan = 'FW' OR
5221 (l_pay_plan = 'AD' and l_pay_basis = 'PH')) THEN
5222 hr_utility.set_location('USER TABLE '||to_char(l_user_table_id),10);
5223 hr_utility.set_location('EFF DT '||to_char(p_effective_date),20);
5224 hr_utility.set_location('MAX STEP '||l_max_step,30);
5225 hr_utility.set_location('PAY PLAN '||l_pay_plan,40);
5226 hr_utility.set_location('GRADE '||l_grade_or_level,50);
5227 ghr_pay_calc.get_pay_table_value ( p_user_table_id => l_user_table_id
5228 ,p_pay_plan => l_pay_plan
5229 ,p_grade_or_level => l_grade_or_level
5230 ,p_step_or_rate => l_max_step
5231 ,p_effective_date => p_effective_date
5232 ,p_PT_value => l_PT_value
5233 ,p_PT_eff_start_date => l_PT_eff_start_date
5234 ,p_PT_eff_end_date => l_PT_eff_end_date);
5235 hr_utility.set_location('VALUE '||to_char(l_pt_value),60);
5236 hr_utility.set_location('Pay Basis '||p_pay_basis,70);
5237 p_award_salary := ROUND(ghr_pay_calc.convert_amount(l_PT_value,p_pay_basis,'PA'));
5238
5239 END IF; --(p_pay_plan = l_equivalent_pay_plan)
5240 END IF; --p_prd not in('3','4','J','K','U','V'
5241 --Begin Bug# 5039156
5242
5243
5244 END IF;
5245
5246 -- Call the user hook
5247 ghr_custom_award.custom_award_salary(p_position_id
5248 ,p_person_id
5249 ,p_prd
5250 ,p_pay_basis
5251 ,p_pay_plan
5252 ,p_user_table_id
5253 ,p_grade_or_level
5254 ,p_effective_date
5255 ,p_basic_pay
5256 ,p_adj_basic_pay
5257 ,p_duty_station_id
5258 ,p_award_salary
5259 );
5260
5261
5262
5263 p_award_amount := FLOOR(p_award_salary *( nvl(p_award_percentage,0) / 100));
5264 --End Bug# 5039156
5265 hr_utility.set_location('Leaving Award Amount sal' || p_award_salary,10);
5266 EXCEPTION
5267 WHEN OTHERS THEN
5268 p_award_amount := NULL;
5269 p_award_salary := NULL;
5270 hr_utility.set_location('Leaving ' || l_proc,10);
5271 END award_amount_calc;
5272 -- Bug# 4748927 End
5273 END ghr_pay_calc;