[Home] [Help]
PACKAGE BODY: APPS.GHR_PAY_CAPS
Source
1 PACKAGE BODY ghr_pay_caps AS
2 /* $Header: ghpaycap.pkb 120.33.12020000.2 2012/07/05 14:44:49 amnaraya ship $ */
3 --
4 pay_cap_failed EXCEPTION;
5 --
6 -- This function returns true if the Pay Plan passed in is an 'FW' equivalent
7 FUNCTION pp_fw_equivalent (p_pay_plan IN VARCHAR2)
8 RETURN BOOLEAN IS
9 CURSOR cur_ppl IS
10 SELECT 1
11 FROM ghr_pay_plans ppl
12 WHERE ppl.pay_plan = p_pay_plan
13 AND ppl.equivalent_pay_plan = 'FW';
14 --
15 BEGIN
16 FOR cur_ppl_rec IN cur_ppl LOOP
17 RETURN(TRUE);
18 END LOOP;
19 --
20 RETURN(FALSE);
21 END pp_fw_equivalent;
22 --
23
24 -- This function returns true if the Pay Plan passed in is an 'GS' equivalent
25 FUNCTION pp_gs_equivalent (p_pay_plan IN VARCHAR2)
26 RETURN BOOLEAN IS
27 CURSOR cur_ppl IS
28 SELECT 1
29 FROM ghr_pay_plans ppl
30 WHERE ppl.pay_plan = p_pay_plan
31 AND ppl.equivalent_pay_plan = 'GS';
32 --
33 BEGIN
34 FOR cur_ppl_rec IN cur_ppl LOOP
35 RETURN(TRUE);
36 END LOOP;
37 --
38 RETURN(FALSE);
39 END pp_gs_equivalent;
40
41 --This function returns the date of the update 34 implemented for the retained grade employee
42 FUNCTION update34_implemented_date (p_person_id IN NUMBER)
43 RETURN DATE IS
44 CURSOR cur_update34_date is
45 SELECT fnd_date.canonical_to_date(pei_information3) update34_date
46 FROM per_people_extra_info
47 WHERE information_type = 'GHR_US_PER_UPDATE34'
48 AND person_id = p_person_id;
49
50 l_date DATE := NULL;
51 --
52 BEGIN
53 FOR cur_update34_date_rec IN cur_update34_date LOOP
54 l_date := cur_update34_date_rec.update34_date;
55 END LOOP;
56 --
57 RETURN(l_date);
58 END update34_implemented_date;
59
60 --Bug 5482191. Function added for AFHR
61 FUNCTION get_job_from_pos(p_effective_date IN DATE
62 ,p_position_id IN NUMBER)
63 RETURN VARCHAR2 IS
64 l_job_id hr_all_positions_f.job_id%TYPE;
65 l_job_name per_jobs.name%TYPE;
66 CURSOR cur_pos IS
67 SELECT job_id
68 FROM hr_all_positions_f pos
69 WHERE pos.position_id = p_position_id
70 AND p_effective_date BETWEEN pos.effective_start_date and pos.effective_end_date;
71 CURSOR cur_job IS
72 SELECT name
73 FROM per_jobs
74 WHERE job_id = l_job_id;
75
76 BEGIN
77 FOR cur_pos_rec IN cur_pos
78 LOOP
79 l_job_id := cur_pos_rec.job_id;
80 END LOOP;
81 FOR cur_job_rec IN cur_job
82 LOOP
83 l_job_name := NVL(cur_job_rec.name,' ');
84 END LOOP;
85 RETURN(l_job_name);
86 END get_job_from_pos;
87
88
89 PROCEDURE update34_implement (p_person_id IN NUMBER
90 ,P_date IN DATE)
91
92 IS
93
94 l_date date;
95 l_person_EXTRA_INFO_ID NUMBER;
96 l_OBJECT_VERSION_NUMBER NUMBER;
97 BEGIN
98
99 l_date := update34_implemented_date (p_person_id);
100
101 If l_date is null then
102 ghr_person_extra_info_api.create_person_extra_info
103 (p_person_id => p_person_id
104 ,p_information_type => 'GHR_US_PER_UPDATE34'
105 ,P_EFFECTIVE_DATE => p_date
106 ,P_PEI_information_category => 'GHR_US_PER_UPDATE34'
107 ,P_PEI_INFORMATION3 => fnd_date.date_to_canonical(p_date)
108 ,p_PERSON_EXTRA_INFO_ID => l_PERSON_EXTRA_INFO_ID
109 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
110 end if;
111 END update34_implement;
112 --
113
114 ----------------------------------------------------------------------------------------
115 --------------------------- <adj_basic_pay_cap> ----------------------------------------------
116 ----------------------------------------------------------------------------------------
117 PROCEDURE adj_basic_pay_cap (p_pay_cap IN NUMBER
118 ,p_basic_pay IN NUMBER
119 ,p_adj_basic_pay IN OUT NOCOPY NUMBER
120 ,p_locality_adj IN OUT NOCOPY NUMBER) IS
121
122 l_locality_diff NUMBER := 0;
123
124 BEGIN
125 IF p_adj_basic_pay > p_pay_cap THEN
126 IF p_pay_cap >= p_basic_pay THEN
127 l_locality_diff := p_pay_cap - p_basic_pay;
128 p_locality_adj := l_locality_diff;
129 p_adj_basic_pay := p_basic_pay + l_locality_diff;
130 ELSE
131 p_locality_adj := p_locality_adj;
132 p_adj_basic_pay := p_adj_basic_pay;
133 ghr_msl_pkg.g_ses_bp_capped := TRUE;
134 --9309268 added the global variable
135 ghr_msl_pkg.g_msl_bp_paycap := p_pay_cap;
136 hr_utility.set_message(8301,'GHR_38583_PAY_CAP3');
137 hr_utility.raise_error;
138 END IF;
139 END IF;
140
141 END adj_basic_pay_cap;
142
143 --
144 --
145 -- PERFORMANCE CERTIFICATION FUNCTION DECLARATIONS START HERE
146 --
147 FUNCTION perf_certified(p_agency_code IN ghr_pa_requests.from_Agency_code%TYPE,
148 p_org_id IN hr_positions_f.organization_id%TYPE,
149 p_pay_plan IN ghr_pa_Requests.from_pay_plan%TYPE,
150 p_effective_date IN ghr_pa_Requests.effective_date%TYPE)
151 RETURN BOOLEAN
152 IS
153 --
154 -- Bug 4063133
155 -- PERFORMANCE CERTIFICATION CURSOR DECLARATIONS START HERE
156 --
157 CURSOR cur_per_org_det (p_per_id per_all_assignments_f.person_id%TYPE,
158 p_eff_date per_all_assignments_f.effective_start_date%TYPE)
159 IS
160 SELECT asg.organization_id
161 FROM per_all_assignments_f asg
162 WHERE asg.person_id = p_per_id
163 AND asg.assignment_type <> 'B'
164 AND trunc(p_eff_date) between asg.effective_start_date
165 and asg.effective_end_date;
166 --
167 CURSOR cur_perf_cert_agency(p_eff_date date,
168 p_agency_code ghr_pa_Requests.agency_code%TYPE,
169 p_pay_plan ghr_pay_plans.pay_plan%TYPE,
170 p_business_group_id number)
171 IS
172 SELECT distinct cert_group
173 FROM ghr_perf_cert
174 WHERE cert_agency = substr(p_agency_code,1,2)
175 AND cert_agency_sub_code IS NULL
176 AND cert_organization IS NULL
177 AND p_eff_date BETWEEN cert_start_date AND nvl(cert_end_date,to_date('31/12/4712','DD/MM/YYYY') )
178 AND ( INSTR(cert_group,p_pay_plan) > 0 )
179 --Bug # 10272986 Added Business Group Id
180 AND NVL(business_group_id,p_business_group_id) = p_business_group_id;
181 --
182 CURSOR cur_perf_cert_agency_sub(p_eff_date date,
183 p_agency_sub_code ghr_pa_Requests.agency_code%TYPE,
184 p_pay_plan ghr_pay_plans.pay_plan%TYPE,
185 p_business_group_id number)
186 IS
187 SELECT cert_group
188 FROM ghr_perf_cert
189 WHERE cert_agency IS NULL
190 AND cert_agency_sub_code = p_agency_sub_code
191 AND cert_organization IS NULL
192 AND p_eff_date BETWEEN cert_start_date AND nvl(cert_end_date,to_date('31/12/4712','DD/MM/YYYY') )
193 AND ( INSTR(cert_group,p_pay_plan) > 0 )
194 --Bug # 10272986 Added Business Group Id
195 AND NVL(business_group_id,p_business_group_id) = p_business_group_id;
196 --
197
198 CURSOR cur_perf_cert_org(p_eff_date date,
199 p_org_id per_positions.organization_id%TYPE,
200 p_pay_plan ghr_pay_plans.pay_plan%TYPE,
201 p_business_group_id number)
202 IS
203 SELECT cert_group
204 FROM ghr_perf_cert
205 WHERE cert_agency IS NULL
206 AND cert_agency_sub_code IS NULL
207 AND cert_organization = p_org_id
208 AND p_eff_date BETWEEN cert_start_date AND nvl(cert_end_date,to_date('31/12/4712','DD/MM/YYYY') )
209 AND ( INSTR(cert_group,p_pay_plan) > 0 )
210 --Bug # 10272986 Added Business Group Id
211 AND NVL(business_group_id,p_business_group_id) = p_business_group_id;
212
213 --
214 --
215 CURSOR cur_ESSL_equivalent(p_pay_plan ghr_pay_plans.pay_plan%TYPE)
216 IS
217 SELECT equivalent_pay_plan
218 FROM ghr_pay_plans ppl
219 WHERE ppl.pay_plan = p_pay_plan;
220 --
221 ll_pay_plan ghr_pay_plans.pay_plan%TYPE;
222 l_from_Other_pay ghr_pa_requests.to_other_pay_amount%TYPE;
223 l_cert_group ghr_perf_cert.cert_group%type;
224 l_business_group_id per_positions.organization_id%TYPE;
225 --
226 -- PERFORMANCE CERTIFICATION CURSOR DECLARATIONS END HERE
227 --
228 --- New Variables End.
229
230 l_cert_agncy BOOLEAN;
231 l_cert_agncy_sub BOOLEAN;
232 l_cert_org BOOLEAN;
233 l_cert BOOLEAN;
234
235 BEGIN
236
237 l_cert := FALSE;
238 --
239 -- Performance Certification Changes
240
241 FOR cur_ESSL_equivalent_rec IN cur_ESSL_equivalent(p_pay_plan)
242 LOOP
243 ll_pay_plan := cur_ESSL_equivalent_rec.equivalent_pay_plan;
244 END LOOP;
245 hr_utility.set_location('INSIDE perf_cert loop'||p_org_id,123453);
246 hr_utility.set_location('INSIDE perf_cert loop'||p_effective_date,123453);
247 hr_utility.set_location('INSIDE perf_cert loop'||p_pay_plan,123453);
248 hr_utility.set_location('INSIDE perf_cert loop'||p_agency_code,123453);
249 --
250 -- 1st LEVEL CHECK
251 -- CHECK if Agency level certification exists
252 --
253 --Bug # 10272986 Added Business Group Id to perf cert table
254 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
255 FOR perf_cert IN cur_perf_cert_agency (p_effective_date,
256 p_agency_code,
257 ll_pay_plan,
258 l_business_group_id)
259 LOOP
260 l_cert := TRUE;
261 hr_utility.set_location('INSIDE perf_cert AGNCY loop'||p_org_id,123453);
262 END LOOP;
263
264 IF NOT l_cert THEN
265 --
266 -- 2nd LEVEL CHECK
267 -- CHECK if Agency SUBELEMENT CODE level certification exists
268 FOR perf_cert IN cur_perf_cert_agency_sub (p_effective_date,
269 p_agency_code,
270 ll_pay_plan,
271 l_business_group_id)
272 LOOP
273 l_cert := TRUE;
274 hr_utility.set_location('INSIDE perf_cert AGNCY SUB loop'||p_org_id,123453);
275 END LOOP;
276
277 IF NOT l_cert THEN
278 --
279 -- 3rd LEVEL CHECK
280 -- CHECK if ORGANIZATION level certification exists
281 FOR perf_cert IN cur_perf_cert_org (p_effective_date,
282 p_org_id,
283 ll_pay_plan,
284 l_business_group_id)
285 LOOP
286 l_cert := TRUE;
287 hr_utility.set_location('INSIDE perf_cert ORG loop'||p_org_id,123453);
288 END LOOP;
289 END IF;
290
291 END IF;
292 RETURN (l_cert);
293
294 END perf_certified;
295
296 --
297 --
298 -- PERFORMANCE CERTIFICATION FUNCTION DECLARATIONS END HERE
299 --
300 --
301
302 --Bug# 5132113
303
304 function pay_cap_chk_ttl_38(l_user_table_id IN pay_user_tables.user_table_id%TYPE,
305 l_user_clomun_name IN pay_user_columns.user_column_name%TYPE,
306 l_market_pay IN number,
307 p_effective_date IN ghr_pa_Requests.effective_date%TYPE)
308 RETURN BOOLEAN IS
309 CURSOR range_values is
310 SELECT udr.row_low_range_or_name,udr.row_high_range
311 FROM pay_user_columns udc,
312 pay_user_rows_f udr,
313 pay_user_tables udt,
314 pay_user_column_instances_f uci
315 WHERE udt.user_table_id = l_user_table_id
316 AND udr.user_table_id = udt.user_table_id
317 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN udr.effective_start_date AND udr.effective_end_date
318 AND udc.user_table_id = udt.user_table_id
319 AND uci.user_column_id = udc.user_column_id
320 AND udr.user_row_id = uci.user_row_id
321 AND upper(udc.user_column_name) = upper(l_user_clomun_name)
322 AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN uci.effective_start_date AND uci.effective_end_date;
323
324 l_low_range pay_user_rows_f.row_low_range_or_name%type;
325 l_high_range pay_user_rows_f.row_high_range%type;
326
327 BEGIN
328 for l_range_values in range_values
329 loop
330 l_low_range := l_range_values.row_low_range_or_name;
331 l_high_range := l_range_values.row_high_range;
332 EXIT;
333 END LOOP;
334 IF l_low_range <= l_market_pay AND
335 l_high_range >= l_market_pay then
336 RETURN(TRUE);
337 ELSE
338 RETURN(FALSE);
339 END IF;
340 END;
341
342 --Bug# 5132113
343
344
345 ----------------------------------------------------------------------------------------
346 --------------------------- <do_pay_caps> ----------------------------------------------
347 ----------------------------------------------------------------------------------------
348 PROCEDURE do_pay_caps_main ( p_pa_request_id IN NUMBER --NEW
349 ,p_effective_date IN DATE
350 ,p_duty_station_id IN NUMBER --Bug 13414643,13403289
351 ,p_pay_rate_determinant IN VARCHAR2
352 ,p_pay_plan IN VARCHAR2
353 ,p_to_position_id IN NUMBER
354 ,p_pay_basis IN VARCHAR2
355 ,p_person_id IN NUMBER
356 ,p_noa_code IN VARCHAR2 --New
357 ,p_basic_pay IN NUMBER
358 ,p_locality_adj IN OUT NOCOPY NUMBER
359 ,p_adj_basic_pay IN OUT NOCOPY NUMBER
360 ,p_total_salary IN OUT NOCOPY NUMBER
361 ,p_other_pay_amount IN OUT NOCOPY NUMBER
362 ,p_capped_other_pay IN OUT NOCOPY NUMBER --New
363 ,p_retention_allowance IN OUT NOCOPY NUMBER --New
364 ,p_retention_allow_percentage IN OUT NOCOPY NUMBER --New
365 ,p_supervisory_allowance IN NUMBER --New
366 ,p_staffing_differential IN NUMBER --New
367 ,p_au_overtime IN OUT NOCOPY NUMBER
368 ,p_availability_pay IN OUT NOCOPY NUMBER
369 ,p_adj_basic_message OUT NOCOPY BOOLEAN
370 ,p_pay_cap_message OUT NOCOPY BOOLEAN
371 ,p_pay_cap_adj OUT NOCOPY NUMBER
372 ,p_open_pay_fields OUT NOCOPY BOOLEAN
373 ,p_message_set OUT NOCOPY BOOLEAN
374 ,p_total_pay_check OUT NOCOPY VARCHAR2) IS
375
376 l_converted_basic_pay NUMBER;
377 l_converted_locality_adj NUMBER;
378 l_converted_adj_basic_pay NUMBER;
379 l_converted_total_salary NUMBER;
380
381 l_pay_cap_amount NUMBER;
382 l_pay_cap_in_data ghr_pay_caps.pay_cap_in_rec_type;
383 l_pay_cap_out_data ghr_pay_caps.pay_cap_out_rec_type;
384
385 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
386 l_pay_plan VARCHAR2(30);
387
388 ---- New Variables
389 l_pay_basis VARCHAR2(30);
390 l_update34_date DATE;
391 l_adjust_op_amt NUMBER;
392 l_difference NUMBER;
393 l_capped_other_pay NUMBER;
394 l_v_capped_other_pay NUMBER;
395 l_other_pay_amount NUMBER;
396 l_au_overtime NUMBER;
397 l_availability_pay NUMBER;
398 l_retention_allowance NUMBER;
399 l_retention_allow_percentage NUMBER;
400 l_ret_allow_from_ele NUMBER;
401 l_temp_ret_allowance NUMBER;
402 l_810_ra NUMBER;
403 l_supervisory_allowance NUMBER;
404 l_staffing_differential NUMBER;
405 l_adj_basic_mesg_flag NUMBER;
406
407 l_old_other_pay_amount NUMBER;
408 l_old_converted_total_salary NUMBER;
409 l_ra_diff NUMBER;
410 l_calc_retention_allowance NUMBER;
411
412 l_assignment_id per_assignments_f.assignment_id%type;
413 l_to_auo_premium_pay_indicator VARCHAR2(30);
414 l_to_ap_premium_pay_indicator VARCHAR2(30);
415 l_multi_error_flag BOOLEAN;
416 l_adj_basic_message BOOLEAN := FALSE;
417 l_pay_cap_message BOOLEAN := FALSE;
418 l_non_810_error BOOLEAN := FALSE;
419
420 l_pos_ei_grp2_data per_position_extra_info%rowtype;
421 l_leo_indicator BOOLEAN := FALSE;
422 l_current_basic_pay NUMBER;
423
424 l_temp_ret_allo_percentage NUMBER;
425 l_ele_supervisory NUMBER;
426 -- Bug 5482191 Start
427 l_pos_ei_valid_grade per_position_extra_info%ROWTYPE;
428 l_grade_or_level VARCHAR2(60);
429 l_grade_id NUMBER;
430 l_psi VARCHAR2(30);--Bug# 8324201
431
432 CURSOR cur_grd IS
433 SELECT gdf.segment1 pay_plan
434 ,gdf.segment2 grade_or_level
435 FROM per_grade_definitions gdf
436 ,per_grades grd
437 WHERE grd.grade_id = l_grade_id
438 AND grd.grade_definition_id = gdf.grade_definition_id;
439 -- Bug 5482191 End
440
441 cursor c_assignment_by_per_id (per_id number, eff_date date) is
442 select asg.assignment_id
443 from per_assignments_f asg
444 where asg.person_id = per_id
445 and trunc(eff_date) between asg.effective_start_date
446 and asg.effective_end_date
447 and asg.primary_flag = 'Y';
448
449 ---
450 CURSOR cur_get_pos_org(p_pos_id per_positions.position_id%TYPE,
451 p_eff_Date ghr_pa_requests.effective_date%TYPE)
452 IS
453 SELECT ORGANIZATION_ID FROM HR_POSITIONS_F
454 WHERE position_id=p_pos_id
455 AND p_eff_date between effective_start_Date and effective_end_date;
456 --
457 l_business_group_id per_positions.organization_id%TYPE;
458 l_agency_subele_code per_position_definitions.segment4%TYPE;
459 l_org_id per_positions.organization_id%TYPE;
460 --Bug 13414643,13403289
461 CURSOR get_locality_pay_area
462 IS
463 SELECT lc.locality_pay_area_code
464 FROM ghr_duty_stations_f ds, ghr_locality_pay_areas_f lc
465 WHERE ds.duty_station_id = p_duty_station_id
466 AND ds.locality_pay_area_id = lc.locality_pay_area_id
467 AND p_effective_date between ds.effective_start_date and ds.effective_end_date
468 AND p_effective_date between lc.effective_start_date and lc.effective_end_date;
469
470 l_locality_pay_area_code ghr_locality_pay_areas_f.locality_pay_area_code%type;
471 --Bug 13414643,13403289
472 FUNCTION convert_amount_to_PA(p_amount IN NUMBER
473 ,p_pay_basis IN VARCHAR2)
474 RETURN NUMBER IS
475 BEGIN
476 RETURN( ghr_pay_calc.convert_amount(p_amount
477 ,p_pay_basis
478 ,'PA') );
479 EXCEPTION
480 WHEN ghr_pay_calc.pay_calc_message THEN
481 RETURN(NULL);
482 END convert_amount_to_PA;
483 --
484 --
485 BEGIN
486 -- Bug#4758111 PRD 2 Processing. Skip Pay Cap calculation for the PRD 2.
487 --Bug# 8324201 added NSPS condition for PRD 2.
488 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_to_position_id, p_effective_date);
489 -- Bug# 13103038 added prd2_process condition
490 --Bug 13414643,13403289
491 for rec_locality_pay_area in get_locality_pay_area
492 loop
493 l_locality_pay_area_code := rec_locality_pay_area.locality_pay_area_code;
494 end loop;
495 --Bug 13414643,13403289
496 IF p_pay_rate_determinant = '2' AND l_psi = '00' AND NOT ghr_pay_calc.prd2_process THEN --Bug# 8324201
497 NULL;
498 ELSE
499 -- set up the in record structure
500 l_pay_cap_in_data.effective_date := p_effective_date;
501 l_pay_cap_in_data.pay_rate_determinant := p_pay_rate_determinant;
502 l_pay_cap_in_data.pay_plan := p_pay_plan;
503 l_pay_cap_in_data.to_position_id := p_to_position_id;
504 l_pay_cap_in_data.pay_basis := p_pay_basis;
505 l_pay_cap_in_data.person_id := p_person_id;
506 l_pay_cap_in_data.basic_pay := p_basic_pay;
507 l_pay_cap_in_data.locality_adj := p_locality_adj;
508 l_pay_cap_in_data.adj_basic_pay := p_adj_basic_pay;
509 l_pay_cap_in_data.total_salary := p_total_salary;
510 l_pay_cap_in_data.other_pay_amount := p_other_pay_amount;
511 l_pay_cap_in_data.au_overtime := p_au_overtime;
512 l_pay_cap_in_data.availability_pay := p_availability_pay;
513 l_pay_cap_in_data.noa_code := p_noa_code;
514 l_pay_cap_in_data.retention_allowance := p_retention_allowance;
515 l_pay_cap_in_data.retention_allow_percentage := p_retention_allow_percentage;
516 l_pay_cap_in_data.capped_other_pay := p_capped_other_pay;
517 l_pay_cap_in_data.supervisory_allowance := p_supervisory_allowance;
518 l_pay_cap_in_data.staffing_differential := p_staffing_differential;
519 l_pay_cap_in_data.pa_request_id := p_pa_request_id;
520
521 hr_utility.set_location('One ',1);
522 l_retention_allowance := p_retention_allowance;
523 l_retention_allow_percentage := p_retention_allow_percentage;
524 l_au_overtime := p_au_overtime;
525 l_availability_pay := p_availability_pay;
526 l_supervisory_allowance := p_supervisory_allowance;
527 l_staffing_differential := p_staffing_differential;
528
529 l_other_pay_amount := p_other_pay_amount;
530 l_capped_other_pay := p_capped_other_pay;
531 l_v_capped_other_pay := p_capped_other_pay;
532 l_adj_basic_mesg_flag := 0;
533
534 FOR c_assignment_by_per_id_rec in c_assignment_by_per_id (p_person_id, p_effective_date) LOOP
535 l_assignment_id := c_assignment_by_per_id_rec.assignment_id;
536 EXIT;
537 END LOOP;
538
539 --
540 -- Performance certification initializations
541 --
542 -- l_certified := FALSE;
543 -- l_cert_to_not_cert := FALSE;
544 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
545
546 --
547 -- Checking with the actual segment being used to store AGENCY CODE
548 -- as customer may have saved agency code in other segment
549 --
550 --
551 -- Performance certification initializations
552 --
553 l_agency_subele_code := ghr_api.get_position_agency_code_pos(
554 p_position_id => p_to_position_id,
555 p_business_group_id => l_business_group_id,
556 p_effective_date => p_effective_date);
557 -- This call only picks teh agency details during appointment action
558 FOR cur_get_pos_org_rec IN cur_get_pos_org (p_to_position_id, p_effective_date)
559 LOOP
560 l_org_id := cur_get_pos_org_rec.organization_id;
561 END LOOP;
562
563 /* ghr_api.retrieve_element_entry_value (p_element_name => 'Basic Salary Rate'
564 ,p_input_value_name => 'Rate'
565 ,p_assignment_id => l_assignment_id
566 ,p_effective_date => p_effective_date
567 ,p_value => l_from_basic_pay
568 ,p_multiple_error_flag => l_multi_error_flag);
569
570 ghr_api.retrieve_element_entry_value (p_element_name => 'Total Pay'
571 ,p_input_value_name => 'Amount'
572 ,p_assignment_id => l_assignment_id
573 ,p_effective_date => p_effective_date
574 ,p_value => l_from_total_pay
575 ,p_multiple_error_flag => l_multi_error_flag);*/
576
577 -- Need to check for the entered value and the from side value so that
578 -- if the emp's certification is suspended, terminated or emp moves from certified to
579 -- non-certified agency
580 --
581 -- END OF PERF CERT INITIALIZATIONS
582 --
583
584 ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
585 ,p_input_value_name => 'Premium Pay Ind'
586 ,p_assignment_id => l_assignment_id
587 ,p_effective_date => p_effective_date
588 ,p_value => l_to_auo_premium_pay_indicator
589 ,p_multiple_error_flag => l_multi_error_flag);
590
591 ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
592 ,p_input_value_name => 'Premium Pay Ind'
593 ,p_assignment_id => l_assignment_id
594 ,p_effective_date => p_effective_date
595 ,p_value => l_to_ap_premium_pay_indicator
596 ,p_multiple_error_flag => l_multi_error_flag);
597
598 hr_utility.set_location('Two ',1);
599 -- Bug 710431 If PRD is ABEFU or V then get retained grade details
600 --
601 IF l_pay_cap_in_data.pay_rate_determinant IN ('A','B','E','F','U','V') THEN
602 -- use retained details...
603 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (l_pay_cap_in_data.person_id
604 ,l_pay_cap_in_data.effective_date
605 ,p_pa_request_id);
606 -- NB: pa_request_id is now redundant!!
607 l_pay_plan := l_retained_grade.pay_plan;
608 l_update34_date := update34_implemented_date(p_person_id);
609 if l_update34_date is not null AND p_effective_date >= l_update34_date then
610 -----if the pay basis is null then raise a error message
611 l_pay_basis := l_retained_grade.pay_basis;
612 else
613 l_pay_basis := p_pay_basis;
614 end if;
615 l_grade_or_level := l_retained_grade.grade_or_level; -- 5482191
616 ELSE
617 l_pay_plan := l_pay_cap_in_data.pay_plan;
618 l_pay_basis := p_pay_basis;
619
620 -- Bug 5482191. Get valid grade data
621 ghr_history_fetch.fetch_positionei(
622 p_position_id => p_to_position_id,
623 p_information_type => 'GHR_US_POS_VALID_GRADE',
624 p_date_effective => p_effective_date,
625 p_pos_ei_data => l_pos_ei_valid_grade);
626 IF l_pos_ei_valid_grade.position_extra_info_id IS NOT NULL THEN
627 l_grade_id := l_pos_ei_valid_grade.poei_information4;
628 IF l_grade_id IS NOT NULL THEN
629 FOR cur_grd_rec IN cur_grd LOOP
630 l_grade_or_level := cur_grd_rec.grade_or_level;
631 END LOOP;
632 END IF;
633 END IF;
634 END IF;
635
636 ----Temp Promo changes regarding the pay basis.
637 IF l_pay_cap_in_data.pay_rate_determinant IN ('A','B','E','F')
638 AND l_retained_grade.temp_step IS NOT NULL THEN
639 l_pay_plan := l_pay_cap_in_data.pay_plan;
640 l_pay_basis := p_pay_basis;
641 END IF;
642 ----Temp Promo changes regarding the pay basis.
643
644 BEGIN
645 -- currently just basic_pay, adj_basic_pay and total salary are checked for
646 -- caps since they are compared with a PA table need to convert these to PA also
647 -- If we can not convert them just treat them as null, i.e. do not error
648 l_converted_basic_pay := convert_amount_to_PA(l_pay_cap_in_data.basic_pay
649 ,l_pay_basis);
650 l_converted_locality_adj := convert_amount_to_PA(l_pay_cap_in_data.locality_adj
651 ,l_pay_basis);
652 l_converted_adj_basic_pay := convert_amount_to_PA(l_pay_cap_in_data.adj_basic_pay
653 ,l_pay_basis);
654 l_converted_total_salary := convert_amount_to_PA(l_pay_cap_in_data.total_salary
655 ,l_pay_basis);
656
657 l_converted_locality_adj := l_converted_adj_basic_pay - l_converted_basic_pay;
658
659 --Fetch the LEO indicator
660 ghr_history_fetch.fetch_positionei(
661 p_position_id => p_to_position_id,
662 p_information_type => 'GHR_US_POS_GRP2',
663 p_date_effective => p_effective_date,
664 p_pos_ei_data => l_pos_ei_grp2_data);
665
666 IF l_pos_ei_grp2_data.position_extra_info_id IS NOT NULL
667 AND l_pos_ei_grp2_data.poei_information16 IN ('1','2') THEN
668 l_leo_indicator := TRUE;
669 END IF;
670
671 -- Bug 1978801
672 -- 3) If Pay Plan ES,IE,AL,SL,IP,ST,CA or with
673 -- any PRD Adjusted Basic Pay must not exceed EX-03
674 -- and Total Pay must not exceed EX-01
675 -- Bug3604377 EE is added to the list.
676 -- Bug 3969209 FE is added to the list
677
678 -- Performance Certification code checks
679 -- Check if the agency to which the emp belongs is certified, if not for Agency Subelement code
680 -- if not for Organization. If certified then check for Cert_status, Cert_group, start date and
681 -- end dates to apply the pay caps accordingly
682 -- Bug#4168256 Added Pay Plan EV
683 --Bug# 5132113
684 --Bug 6457107 Modified poei_information11 to poei_information12 and
685 -- poei_information12 to poei_information13
686 -- Bug# 7031385 Added prd 0 condition since paycap should not fire for prd T and 4
687 IF l_pay_plan in ( 'GP','GR') THEN
688 -- } 1
689 --Bug# 13815085 added following if condition since pay cap should trigger for GP , GR Pay plans
690 -- for all PRDs except T and 4
691 IF l_pay_cap_in_data.pay_rate_determinant NOT IN ('T','4') THEN --Bug# 13815085
692 ghr_history_fetch.fetch_positionei(
693 p_position_id => p_to_position_id,
694 p_information_type => 'GHR_US_POS_VALID_GRADE',
695 p_date_effective => p_effective_date,
696 p_pos_ei_data => l_pos_ei_valid_grade);
697 IF NOT (pay_cap_chk_ttl_38( l_pos_ei_valid_grade.poei_information12,
698 l_pos_ei_valid_grade.poei_information13,
699 l_converted_adj_basic_pay,
700 p_effective_date)) THEN
701 hr_utility.set_message(8301,'GHR_37448_PAY_CAP_TTL38');
702 hr_utility.raise_error;
703 END IF;
704 END IF;--Bug# 13815085
705
706 --Bug# 5132113
707 --Begin Bug# 7557159
708 ELSIF l_pay_plan in('IG') THEN
709 -- } 1
710 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
711 ,'02'
712 ,'00'
713 ,l_pay_cap_in_data.effective_date);
714 IF l_pay_cap_amount < l_converted_adj_basic_pay THEN
715 hr_utility.set_message(8301,'GHR_38186_IG_PAY_CAP1');
716 hr_utility.set_message_token('PAY_CAP_AMT',l_pay_cap_amount);
717 hr_utility.raise_error;
718 END IF;
719 --End Bug# 7557159
720 ELSIF l_pay_plan in ( 'ES','EP','EV','IE','AL','AA','SL','IP','ST','CA','EE', 'FE' ) THEN
721 -- } 1
722 IF l_pay_plan ='FE' THEN --Added for bug#5931199
723 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX','02','00'
724 ,l_pay_cap_in_data.effective_date);
725 ELSIF l_pay_plan in ( 'ES','EP','EV','IE','SL','IP','ST') THEN --Removed FE bug#5931199
726 -- { 2
727 IF ( perf_certified(l_agency_subele_code,l_org_id, l_pay_plan, p_effective_date) ) THEN
728 -- { 3 -- check for pay plans initially then check for certification
729 hr_utility.set_location('CERTIFIED',123455);
730
731 IF (l_pay_plan in ('ES','EP','EV','IE') ) THEN
732 --Begin Bug# 7633783
733 IF l_pay_cap_in_data.pay_rate_determinant = 'D' THEN
734 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VZ'
735 ,'00'
736 ,'00'
737 ,l_pay_cap_in_data.effective_date);
738
739 ELSE
740 --End Bug# 7633783
741 --Removed FE bug#5931199
742 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
743 ,'02'
744 ,'00'
745 ,l_pay_cap_in_data.effective_date);
746 hr_utility.set_location('INSIDE EX-02',123456);
747 hr_utility.set_location('pay cap amt :'||l_pay_cap_amount,1234567890);
748 END IF;
749 ELSIF (l_pay_plan in ('SL','ST','IP') ) THEN
750 --Begin Bug# 6807868, coded as per the Pay cap master chart
751 --Begin Bug# 7633783 added PRD S
752 --Begin Bug# 8320557
753 IF l_pay_cap_in_data.pay_rate_determinant IN ('R','S') THEN
754 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value
755 ('EX'
756 ,'02'
757 ,'00'
758 ,l_pay_cap_in_data.effective_date);
759 ELSE
760 --End Bug# 6807868
761 --Bug # 8515337 added date effectivity to consider EX-02 only after 12-Apr-2009
762 -- For SL ST IP and Certified Agency
763 IF l_pay_cap_in_data.effective_date >= to_date('2009/04/12','YYYY/MM/DD') THEN
764 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
765 ,'02'
766 ,'00'
767 ,l_pay_cap_in_data.effective_date);
768 ELSE
769 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
770 ,'03'
771 ,'00'
772 ,l_pay_cap_in_data.effective_date);
773 END IF;
774 END IF; -- Bug# 6807868
775 END IF;
776 ELSE -- IF NONE OF THE AGENCIES OR ORGANIZATIONS ARE CERTIFIED
777 -- Bug # 8320557 Modified for SL ST IP pay plans to consider EX-02 for PRD R
778 -- and EX-03 for Other than R PRDs and NON Certified
779 IF (l_pay_plan in ('SL','ST','IP') ) THEN
780 IF l_pay_cap_in_data.pay_rate_determinant IN ('R') THEN
781 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value
782 ('EX'
783 ,'02'
784 ,'00'
785 ,l_pay_cap_in_data.effective_date);
786 ELSE
787 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
788 ,'03'
789 ,'00'
790 ,l_pay_cap_in_data.effective_date);
791 END IF;
792 ELSE
793 --End Bug# 8320557
794 --Begin Bug# 7633783
795 IF l_pay_cap_in_data.pay_rate_determinant = 'D' THEN
796 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
797 ,'01'
798 ,'00'
799 ,l_pay_cap_in_data.effective_date);
800 ELSE
801 --End Bug# 7633783
802 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
803 ,'03'
804 ,'00'
805 ,l_pay_cap_in_data.effective_date);
806 END IF;
807 END IF; -- } 2
808 END IF;
809 -- } 3
810 ELSIF l_pay_plan in ('AL','AA','CA','EE') THEN
811 -- { 2
812 --Begin Bug# 6807868, coded as per the Pay cap master chart
813 IF l_pay_cap_in_data.pay_rate_determinant = 'R' AND l_pay_plan in ('AL','CA') THEN
814 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value
815 ('EX'
816 ,'02'
817 ,'00'
818 ,l_pay_cap_in_data.effective_date);
819 --Begin Bug# 9973337
820 ELSIF l_pay_plan in ('EE') THEN
821 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value
822 ('EX'
823 ,'02'
824 ,'00'
825 ,l_pay_cap_in_data.effective_date);
826 -- End Bug# 9973337
827 ELSE
828 --End Bug# 6807868
829 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
830 ,'03'
831 ,'00'
832 ,l_pay_cap_in_data.effective_date);
833 END IF; --Bug# 6807868
834 END IF;
835 -- } 2
836
837
838 -- END OF PERF CERT CHANGES FOR ADJ BASIC PAY
839 --
840 --check the adjusted basic pay
841 IF l_converted_adj_basic_pay > l_pay_cap_amount THEN
842 adj_basic_pay_cap (p_pay_cap => l_pay_cap_amount
843 ,p_basic_pay => l_converted_basic_pay
844 ,p_adj_basic_pay => l_converted_adj_basic_pay
845 ,p_locality_adj => l_converted_locality_adj );
846
847 if l_to_auo_premium_pay_indicator is not null then
848 l_au_overtime := ghr_pay_calc.get_ppi_amount(
849 l_to_auo_premium_pay_indicator
850 ,l_converted_adj_basic_pay
851 ,l_pay_basis);
852 end if;
853 if l_to_ap_premium_pay_indicator is not null then
854 l_availability_pay := ghr_pay_calc.get_ppi_amount(
855 l_to_ap_premium_pay_indicator
856 ,l_converted_adj_basic_pay
857 ,l_pay_basis);
858 end if;
859 l_other_pay_amount :=
860 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
861 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
862
863 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
864 ---Warning Message
865 hr_utility.set_message(8301,'GHR_38581_PAY_CAP1');
866 l_adj_basic_mesg_flag := 1;
867 END IF;
868
869 -- } 1 else cond
870
871 -- Bug 5482191 Start
872 ELSIF get_job_from_pos(l_pay_cap_in_data.effective_date, p_to_position_id) NOT IN ('0602','0680')
873 AND (l_pay_plan IN ('YA','YB','YC','YD','YE','YF','YH','YI','YK','YL','YM','YN','YP') OR
874 (l_pay_plan = 'YJ' AND l_grade_or_level IN ('01','02','03'))
875 ) THEN
876 -- } 1
877 IF l_pay_cap_in_data.pay_rate_determinant IN ('0','4','T') THEN
878 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
879 ,'04'
880 ,'00'
881 ,l_pay_cap_in_data.effective_date);
882 l_pay_cap_amount := FLOOR((l_pay_cap_amount * 105) / 100);
883 ELSIF l_pay_cap_in_data.pay_rate_determinant = 'R' THEN
884 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
885 ,'02'
886 ,'00'
887 ,l_pay_cap_in_data.effective_date);
888 --Begin Bug# 7633783
889 ELSIF l_pay_cap_in_data.pay_rate_determinant = 'S' THEN
890 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
891 ,'03'
892 ,'00'
893 ,l_pay_cap_in_data.effective_date);
894 --End Bug# 7633783
895 --Begin bug# 8324201
896 ELSIF l_pay_cap_in_data.pay_rate_determinant = '2' AND ( perf_certified(l_agency_subele_code,l_org_id, l_pay_plan, p_effective_date)) THEN
897 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
898 ,'02'
899 ,'00'
900 ,l_pay_cap_in_data.effective_date);
901 --End bug# 8324201
902 END IF;
903 IF l_pay_cap_amount < l_converted_adj_basic_pay THEN
904 --Begin bug# 8650322
905 adj_basic_pay_cap (p_pay_cap => l_pay_cap_amount
906 ,p_basic_pay => l_converted_basic_pay
907 ,p_adj_basic_pay => l_converted_adj_basic_pay
908 ,p_locality_adj => l_converted_locality_adj );
909
910 --End bug# 8650322
911 --Begin bug# 8324201
912 IF l_pay_cap_in_data.pay_rate_determinant = '2' AND ( perf_certified(l_agency_subele_code,l_org_id, l_pay_plan, p_effective_date)) THEN
913 hr_utility.set_message(8301,'GHR_38186_IG_PAY_CAP1');
914 hr_utility.set_message_token('PAY_CAP_AMT',l_pay_cap_amount);
915 hr_utility.raise_error;
916 ELSE
917 --End bug# 8324201
918
919 -- Bug 5663050 Start
920 if l_to_auo_premium_pay_indicator is not null then
921 l_au_overtime := ghr_pay_calc.get_ppi_amount(
922 l_to_auo_premium_pay_indicator
923 ,l_converted_adj_basic_pay
924 ,l_pay_basis);
925 end if;
926 if l_to_ap_premium_pay_indicator is not null then
927 l_availability_pay := ghr_pay_calc.get_ppi_amount(
928 l_to_ap_premium_pay_indicator
929 ,l_converted_adj_basic_pay
930 ,l_pay_basis);
931 end if;
932 l_other_pay_amount :=
933 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
934 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
935
936 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
937 -- Bug 5663050 End
938
939 hr_utility.set_message(8301,'GHR_38581_PAY_CAP1');
940 l_adj_basic_mesg_flag := 1;
941 END IF;
942 END IF;
943 ELSIF get_job_from_pos(l_pay_cap_in_data.effective_date, p_to_position_id) IN ('0602','0680')
944 AND (l_pay_plan = 'YG' OR
945 (l_pay_plan = 'YJ' AND l_grade_or_level = '04')
946 ) THEN
947 -- } 1
948 IF l_pay_cap_in_data.pay_rate_determinant IN ('0','4','T','R') THEN
949 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VW'
950 ,'00'
951 ,'00'
952 ,l_pay_cap_in_data.effective_date);
953 IF l_pay_cap_amount < l_converted_adj_basic_pay THEN
954 l_converted_adj_basic_pay := l_pay_cap_amount;
955 l_converted_locality_adj := l_converted_adj_basic_pay - l_converted_basic_pay;
956
957 -- Bug 5663050 Start
958 if l_to_auo_premium_pay_indicator is not null then
959 l_au_overtime := ghr_pay_calc.get_ppi_amount(
960 l_to_auo_premium_pay_indicator
961 ,l_converted_adj_basic_pay
962 ,l_pay_basis);
963 end if;
964 if l_to_ap_premium_pay_indicator is not null then
965 l_availability_pay := ghr_pay_calc.get_ppi_amount(
966 l_to_ap_premium_pay_indicator
967 ,l_converted_adj_basic_pay
968 ,l_pay_basis);
969 end if;
970 l_other_pay_amount :=
971 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
972 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
973
974 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
975 -- Bug 5663050 End
976
977 hr_utility.set_message(8301,'GHR_38581_PAY_CAP1');
978 l_adj_basic_mesg_flag := 1;
979 END IF;
980 END IF;
981 -- Bug 5482191 End
982
983 -- Bug #5948924
984 -- Commented leo indicator validations As per the pay cap chart - March 2007 leo indicator should not be considered.
985 -- ELSIF NOT l_leo_indicator THEN
986
987 --
988 -- 1) If Pay Plan GS and Equivalent or AD with (bug 2681620 Remove AD)
989 -- PRD 0,A,B,U,V Adjusted Basic Pay must not exceed EX-04
990 -- and Total Pay must not exceed EX-01
991 -- Bug#4168256 Added PRDs 3, J, K
992 -- Bug# 7633783 Added PRDs D and S
993 --Bug# 9255822,9156723 added PRD Y and Pay plan FP, FO
994 -- Bug# 13103038 added prd 2
995 -- Bug 13414643,13403289 Added Locality HI,AK,ON Condition
996 ELSIF (pp_gs_equivalent(l_pay_plan) OR l_pay_plan in ('FO','FP'))
997 AND (l_pay_cap_in_data.pay_rate_determinant in ('0','2','3','A','B','J','K','U','V','R','D','S','Y'))
998 AND NOT (l_pay_cap_in_data.pay_rate_determinant in ('3','J','K','U','V') AND NVL(l_locality_pay_area_code,'XX') in ('HI','AK','ON')) THEN
999 -- } 1
1000 ----Bug 2065033
1001 -- Bug#4168256 Removed Pay Plan EV. Added it with pay plans ES,EP,FE,IE
1002 IF l_pay_plan in ('EX') THEN
1003 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1004 ,'01'
1005 ,'00'
1006 ,l_pay_cap_in_data.effective_date);
1007 --BEGIN Bug# 6807868
1008 ELSIF l_pay_cap_in_data.pay_rate_determinant = 'R' THEN
1009 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1010 ,'02'
1011 ,'00'
1012 ,l_pay_cap_in_data.effective_date);
1013 --END Bug# 6807868
1014 --BEGIN Bug# 9255822
1015 ELSIF l_pay_cap_in_data.pay_rate_determinant = 'Y' THEN
1016 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1017 ,'04'
1018 ,'00'
1019 ,l_pay_cap_in_data.effective_date);
1020 l_pay_cap_amount := FLOOR((l_pay_cap_amount * 105) / 100);
1021 --END Bug# 9255822
1022 --Begin Bug# 7633783
1023 ELSIF l_pay_plan ='GS' AND l_pay_cap_in_data.pay_rate_determinant = 'D' THEN
1024 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1025 ,'01'
1026 ,'00'
1027 ,l_pay_cap_in_data.effective_date);
1028 ELSIF l_pay_cap_in_data.pay_rate_determinant = 'S' THEN
1029 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1030 ,'03'
1031 ,'00'
1032 ,l_pay_cap_in_data.effective_date);
1033 --End Bug# 7633783
1034 ELSE
1035 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1036 ,'04'
1037 ,'00'
1038 ,l_pay_cap_in_data.effective_date);
1039 END IF;
1040
1041 --check the adjusted basic pay
1042 IF l_converted_adj_basic_pay > l_pay_cap_amount THEN
1043 adj_basic_pay_cap (p_pay_cap => l_pay_cap_amount
1044 ,p_basic_pay => l_converted_basic_pay
1045 ,p_adj_basic_pay => l_converted_adj_basic_pay
1046 ,p_locality_adj => l_converted_locality_adj );
1047
1048 if l_to_auo_premium_pay_indicator is not null then
1049 l_au_overtime := ghr_pay_calc.get_ppi_amount(
1050 l_to_auo_premium_pay_indicator
1051 ,l_converted_adj_basic_pay
1052 ,l_pay_basis);
1053 end if;
1054 if l_to_ap_premium_pay_indicator is not null then
1055 l_availability_pay := ghr_pay_calc.get_ppi_amount(
1056 l_to_ap_premium_pay_indicator
1057 ,l_converted_adj_basic_pay
1058 ,l_pay_basis);
1059 end if;
1060 l_other_pay_amount :=
1061 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1062 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1063
1064 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
1065 ---Warning Message
1066 hr_utility.set_message(8301,'GHR_38581_PAY_CAP1');
1067 l_adj_basic_mesg_flag := 1;
1068 END IF;
1069 --END IF;
1070
1071 --
1072 -- 2) If Pay Plan GS and Equivalent or AD with (AD is removed from the requirements)
1073 -- PRD 5,6,7,E,F,M Adjusted Basic Pay must not exceed EX-05 (EX-05 -> EX-03)
1074 -- and Total Pay must not exceed EX-01
1075 -- Bug#4168256. After FWFA Changes(i.e. after 01-05-2005(dd-mm-yyyy)),
1076 -- If Pay Plan GS and Equivalent except AD with
1077 -- PRD 5,6,7,E,F,M Adjusted Basic Pay must not exceed EX-04
1078 -- and Total Pay must not exceed EX-01
1079 --Bug# 9156723 added Pay plan FP, FO
1080 ELSIF (pp_gs_equivalent(l_pay_plan)OR l_pay_plan in ('FO','FP'))
1081 AND (l_pay_cap_in_data.pay_rate_determinant in ('5','6','7','E','F','M')) THEN
1082 -- } 1
1083
1084 ----Bug 2065033
1085 -- Bug#4168256 Removed Pay Plan EV. Added it with pay plans ES,EP,FE,IE
1086 IF l_pay_plan in ('EX') THEN
1087 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1088 ,'01'
1089 ,'00'
1090 ,l_pay_cap_in_data.effective_date);
1091 ELSE
1092 --Bug#4168256 After FWFA, the adj pay cap changed to EX-04.
1093 -- Adj Pay Cap is EX-04 from the begining. Removing the effective date check.
1094 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1095 ,'04'
1096 ,'00'
1097 ,l_pay_cap_in_data.effective_date);
1098 END IF;
1099
1100 --check the adjusted basic pay
1101 IF l_converted_adj_basic_pay > l_pay_cap_amount THEN
1102 adj_basic_pay_cap (p_pay_cap => l_pay_cap_amount
1103 ,p_basic_pay => l_converted_basic_pay
1104 ,p_adj_basic_pay => l_converted_adj_basic_pay
1105 ,p_locality_adj => l_converted_locality_adj );
1106
1107 if l_to_auo_premium_pay_indicator is not null then
1108 l_au_overtime := ghr_pay_calc.get_ppi_amount(
1109 l_to_auo_premium_pay_indicator
1110 ,l_converted_adj_basic_pay
1111 ,l_pay_basis);
1112 end if;
1113 if l_to_ap_premium_pay_indicator is not null then
1114 l_availability_pay := ghr_pay_calc.get_ppi_amount(
1115 l_to_ap_premium_pay_indicator
1116 ,l_converted_adj_basic_pay
1117 ,l_pay_basis);
1118 end if;
1119 l_other_pay_amount :=
1120 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1121 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1122
1123 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
1124 ---Warning Message
1125 hr_utility.set_message(8301,'GHR_38581_PAY_CAP1');
1126 l_adj_basic_mesg_flag := 1;
1127 END IF;
1128 --END IF;
1129
1130 --
1131 -- 4) If Pay Plan 'FW Equivalent' and PRD anything then Total Salary must not exceed EX-01
1132 -- No ADj Basic pay check
1133 --
1134 --- END IF;
1135
1136 --
1137 -- 5) If LEO Position then
1138 -- If AP or AUO is not null then Adj basic pay must not exceed EX-05
1139 -- If not recivening ADj Basic Pay must not exceed EX-04
1140 -- And total Salary must not exceed EX-01.
1141 --
1142 -- Bug # 5948924 commented as no need of leo indicator validations as per
1143 -- March 2007 Pay cap chart
1144 /* ELSIF l_leo_indicator THEN
1145
1146 IF (l_au_overtime is not null ) OR (l_availability_pay is not null) THEN
1147 -- Bug# 4168256 Added the IF condition.
1148 IF (pp_gs_equivalent(l_pay_plan))
1149 AND (l_pay_cap_in_data.pay_rate_determinant in ('3','J','K','U','V')) THEN
1150 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1151 ,'04'
1152 ,'00'
1153 ,l_pay_cap_in_data.effective_date);
1154 ELSE
1155 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1156 ,'05'
1157 ,'00'
1158 ,l_pay_cap_in_data.effective_date);
1159 END IF;
1160 --check the adjusted basic pay
1161 IF l_converted_adj_basic_pay > l_pay_cap_amount THEN
1162 adj_basic_pay_cap (p_pay_cap => l_pay_cap_amount
1163 ,p_basic_pay => l_converted_basic_pay
1164 ,p_adj_basic_pay => l_converted_adj_basic_pay
1165 ,p_locality_adj => l_converted_locality_adj );
1166
1167 if l_to_auo_premium_pay_indicator is not null then
1168 l_au_overtime := ghr_pay_calc.get_ppi_amount(
1169 l_to_auo_premium_pay_indicator
1170 ,l_converted_adj_basic_pay
1171 ,l_pay_basis);
1172 end if;
1173 if l_to_ap_premium_pay_indicator is not null then
1174 l_availability_pay := ghr_pay_calc.get_ppi_amount(
1175 l_to_ap_premium_pay_indicator
1176 ,l_converted_adj_basic_pay
1177 ,l_pay_basis);
1178 end if;
1179 l_other_pay_amount :=
1180 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1181 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1182
1183 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
1184 ---Warning Message
1185 hr_utility.set_message(8301,'GHR_38581_PAY_CAP1');
1186 l_adj_basic_mesg_flag := 1;
1187 END IF;
1188 ELSIF ( nvl(l_au_overtime,0) = 0 ) AND (nvl(l_availability_pay,0) = 0) THEN
1189
1190 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1191 ,'04'
1192 ,'00'
1193 ,l_pay_cap_in_data.effective_date);
1194 --check the adjusted basic pay
1195 IF l_converted_adj_basic_pay > l_pay_cap_amount THEN
1196 adj_basic_pay_cap (p_pay_cap => l_pay_cap_amount
1197 ,p_basic_pay => l_converted_basic_pay
1198 ,p_adj_basic_pay => l_converted_adj_basic_pay
1199 ,p_locality_adj => l_converted_locality_adj );
1200
1201 if l_to_auo_premium_pay_indicator is not null then
1202 l_au_overtime := ghr_pay_calc.get_ppi_amount(
1203 l_to_auo_premium_pay_indicator
1204 ,l_converted_adj_basic_pay
1205 ,l_pay_basis);
1206 end if;
1207 if l_to_ap_premium_pay_indicator is not null then
1208 l_availability_pay := ghr_pay_calc.get_ppi_amount(
1209 l_to_ap_premium_pay_indicator
1210 ,l_converted_adj_basic_pay
1211 ,l_pay_basis);
1212 end if;
1213 l_other_pay_amount :=
1214 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1215 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1216
1217 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
1218 ---Warning Message
1219 hr_utility.set_message(8301,'GHR_38581_PAY_CAP1');
1220 l_adj_basic_mesg_flag := 1;
1221 END IF;
1222 END IF; */
1223 END IF;
1224 -- } 1
1225 hr_utility.set_location('Before entering TPC logic CTS'||l_converted_total_salary,1);
1226 hr_utility.set_location('p_noa_code is '||p_noa_code,1);
1227 --Begin Bug# 7557159
1228 IF l_pay_plan in('IG') THEN
1229 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1230 ,'01'
1231 ,'00'
1232 ,l_pay_cap_in_data.effective_date);
1233 IF l_pay_cap_amount < l_converted_total_salary THEN
1234 hr_utility.set_message(8301,'GHR_38187_IG_PAY_CAP2');
1235 hr_utility.set_message_token('PAY_CAP_AMT',l_pay_cap_amount);
1236 hr_utility.raise_error;
1237 END IF;
1238
1239 --End Bug# 7557159
1240 --Bug# 5132113
1241 -- Bug# 7034637
1242 ELSIF l_pay_plan in('GP','GR') THEN
1243 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VX',
1244 '00','00',l_pay_cap_in_data.effective_date);
1245 -- Begin Bug# 7034637
1246 IF l_pay_cap_amount < l_converted_total_salary THEN
1247 hr_utility.set_message(8301,'GHR_38586_PAY_CAP6');
1248 hr_utility.set_message_token('PAY_CAP_AMT',l_pay_cap_amount);
1249 hr_utility.raise_error;
1250 END IF;
1251 -- End Bug# 7034637
1252 --Bug# 5132113
1253 -- Bug#4168256 Added pay plan EV
1254 --Bug# 9156723 added Pay plan FP, FO
1255 ELSIF l_pay_plan in ( 'ES','EP','EV','IE','AL','AA','SL','IP','ST','CA','EE','FE','FP','FO') OR
1256 pp_gs_equivalent(l_pay_plan) OR
1257 pp_fw_equivalent(l_pay_plan) THEN
1258
1259 --check the total pay
1260 -- Pradeep added this if statement as for EE Pay Plan VZ-00 is the Total Pay Cap.
1261 IF l_pay_plan in ('EE','FE') THEN --Added pay plan FE for bug#5931199
1262
1263 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VZ'
1264 ,'00'
1265 ,'00'
1266 ,l_pay_cap_in_data.effective_date);
1267 -- Bug 4063133, 4065855
1268 -- Performance Certification changes
1269 -- Bug#4168256 Added pay plan EV
1270 ELSIF ( l_pay_plan in ( 'ES','EP','EV','IE') ) THEN --Removed FE bug#5931199
1271 -- Performance Certification Changes
1272 IF ( perf_certified(l_agency_subele_code,l_org_id, l_pay_plan, p_effective_date)
1273 ) THEN
1274 hr_utility.set_location('INSIDE VZ-02',12345);
1275 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VZ'
1276 ,'00'
1277 ,'00'
1278 ,l_pay_cap_in_data.effective_date);
1279
1280
1281 ELSE -- not certified then old pay cap limit
1282 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1283 ,'01'
1284 ,'00'
1285 ,l_pay_cap_in_data.effective_date);
1286 END IF;
1287 -- Performance Certification changes
1288 ELSIF ( l_pay_plan in ( 'SL','ST','IP') ) THEN
1289 -- Bug#5125166 For Certified employees on pay plans SL,ST,IP, pay cap is VZ-00, else EX-01.
1290 IF ( perf_certified(l_agency_subele_code,l_org_id, l_pay_plan, p_effective_date)
1291 ) THEN
1292 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VZ'
1293 ,'00'
1294 ,'00'
1295 ,l_pay_cap_in_data.effective_date);
1296
1297 ELSE -- not certified then old pay cap limit
1298 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1299 ,'01'
1300 ,'00'
1301 ,l_pay_cap_in_data.effective_date);
1302 END IF;
1303
1304 -- Bug 4063133, 4065855
1305 ELSE
1306 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1307 ,'01'
1308 ,'00'
1309 ,l_pay_cap_in_data.effective_date);
1310 END IF;
1311
1312 --
1313 hr_utility.set_location('CTS '||l_converted_total_salary,2);
1314 hr_utility.set_location('PC '||l_pay_cap_amount,3);
1315
1316 -- MADHURI add logic to check for from basic and to basic
1317 -- let not the user chaneg the basic if he is moved from cert to not cert
1318 --
1319 IF l_converted_total_salary > l_pay_cap_amount THEN
1320 IF p_noa_code <> '810' THEN
1321 --
1322 -- Modifying the input values from Salary to Rate for Payroll Integration
1323 --
1324 ghr_api.retrieve_element_entry_value (p_element_name => 'Basic Salary Rate'
1325 ,p_input_value_name => 'Rate'
1326 ,p_assignment_id => l_assignment_id
1327 ,p_effective_date => p_effective_date
1328 ,p_value => l_current_basic_pay
1329 ,p_multiple_error_flag => l_multi_error_flag);
1330 if nvl(p_basic_pay,0) >= nvl(l_current_basic_pay,0) then
1331 hr_utility.set_location('Inside the Retro calc -- l_converted_total_sal '||l_converted_total_salary,1);
1332
1333 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
1334 ,p_input_value_name => 'Amount'
1335 ,p_assignment_id => l_assignment_id
1336 ,p_effective_date => p_effective_date
1337 ,p_value => l_ret_allow_from_ele
1338 ,p_multiple_error_flag => l_multi_error_flag);
1339
1340 hr_utility.set_location('Inside the Retro calc RA'||l_ret_allow_from_ele,2);
1341 hr_utility.set_location('Inside the Retro calc l_RA'||l_retention_allowance,3);
1342
1343 ---Bug 2653521. Calculate the total salary with Old retention Allowance and Comapre with
1344 -------------- the pay cap amount. If the total salary is >= the pay cap amount then no
1345 -------------- need to increase the retention allowance. But if it is lesser then increase
1346 -------------- the retention allowance to that extent to meet the pay cap irrespective of
1347 -------------- the NOAC.
1348
1349 l_ra_diff := 0;
1350 l_old_other_pay_amount :=
1351 nvl(l_ret_allow_from_ele,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1352 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1353
1354 l_old_converted_total_salary := l_converted_adj_basic_pay + nvl(l_old_other_pay_amount,0);
1355 if l_old_converted_total_salary < l_pay_cap_amount then
1356 l_ra_diff := nvl(l_pay_cap_amount,0) - nvl(l_old_converted_total_salary,0);
1357 l_calc_retention_allowance := nvl(l_ret_allow_from_ele,0) + nvl(l_ra_diff,0);
1358 if l_calc_retention_allowance < l_retention_allowance then
1359 l_retention_allowance := l_calc_retention_allowance;
1360
1361 --Pradeep start of Bug 3306515.
1362 --l_retention_allow_percentage := NULL;
1363 --Bug 4744349 added trunc condition
1364 l_retention_allow_percentage := trunc((l_retention_allowance/l_current_basic_pay)*100,2);
1365 --Pradeep end of Bug 3306515.
1366
1367 else
1368 l_ra_diff := 0;
1369 end if;
1370 end if;
1371 ---Bug 2653521. fix end
1372
1373 IF nvl(l_retention_allowance,0) > nvl(l_ret_allow_from_ele,0) THEN
1374 l_retention_allowance := l_ret_allow_from_ele + l_ra_diff;
1375 l_other_pay_amount :=
1376 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1377 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1378
1379 l_converted_total_salary := l_converted_adj_basic_pay + nvl(l_other_pay_amount,0);
1380 END IF;
1381 End if;
1382 END IF;
1383 END IF;
1384
1385
1386
1387 hr_utility.set_location('l_converted_total_sal '||l_converted_total_salary,4);
1388
1389 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
1390 ,p_input_value_name => 'Amount'
1391 ,p_assignment_id => l_assignment_id
1392 ,p_effective_date => p_effective_date
1393 ,p_value => l_810_ra
1394 ,p_multiple_error_flag => l_multi_error_flag);
1395
1396 IF l_converted_total_salary > l_pay_cap_amount THEN
1397 hr_utility.set_location('Inside TP loop '||l_pay_cap_amount,4);
1398 l_adjust_op_amt := l_converted_total_salary - l_pay_cap_amount;
1399 if l_adjust_op_amt < 0 then
1400 l_adjust_op_amt := l_adjust_op_amt * -1;
1401 end if;
1402 l_adjust_op_amt := ghr_pay_calc.convert_amount(l_adjust_op_amt
1403 , 'PA'
1404 ,l_pay_basis);
1405
1406 l_temp_ret_allowance := l_retention_allowance;
1407 -- Bug#3228580 Added Call to convert the Amount into the corresponding pay basis.
1408 l_difference := ghr_pay_calc.convert_amount(l_converted_total_salary - l_pay_cap_amount
1409 , 'PA'
1410 ,l_pay_basis);
1411
1412 if l_difference > nvl(l_temp_ret_allowance,0) then
1413 l_temp_ret_allowance := 0;
1414 else
1415 l_temp_ret_allowance := nvl(l_temp_ret_allowance,0) - l_difference;
1416 end if;
1417
1418 --Pradeep for Bug 3306515.
1419
1420 l_temp_ret_allo_percentage := trunc((l_temp_ret_allowance/p_basic_pay)*100,2);
1421
1422 hr_utility.set_location('l_difference is '||l_difference,1);
1423 if p_noa_code <> '810' then
1424 if nvl(l_retention_allowance,0) > 0 then
1425 ----- Raise pay_cap_failed Raise Error Message
1426 --Pradeep Changed this error message for Bug 3306515.
1427 hr_utility.set_message(8301,'GHR_38893_PAY_CAP7');
1428 hr_utility.set_message_token('PAY_CAP_AMT',l_pay_cap_amount);
1429 hr_utility.set_message_token('CAL_OP_PERC' ,l_temp_ret_allo_percentage );
1430 l_non_810_error := TRUE;
1431 l_pay_cap_message := TRUE;
1432 raise pay_cap_failed;
1433 -------hr_utility.raise_error;
1434 end if;
1435 end if;
1436
1437 if l_810_ra is null AND nvl(l_retention_allowance,0) > 0 then
1438 l_capped_other_pay := NULL;
1439 else
1440 -- Bug # 4102958 changes begin
1441 -- If other pay is zero or null then capped other should be null.
1442 -- Capped other pay should be converted to original pays basis as it is calculated on PA pay basis.
1443 IF NVL(p_other_pay_amount,0) = 0 THEN
1444 l_capped_other_pay := NULL;
1445 ELSE
1446 l_capped_other_pay := ghr_pay_calc.convert_amount(l_pay_cap_amount - l_converted_adj_basic_pay
1447 , 'PA'
1448 , l_pay_basis);
1449 END IF;
1450 -- l_capped_other_pay := l_pay_cap_amount - l_converted_adj_basic_pay;
1451 -- Bug # 4102958 changes end
1452 end if;
1453
1454 hr_utility.set_location('l_capped_other_pay is '||l_capped_other_pay,1);
1455 -- Bug#3228580 Added Call to convert the Amount into the corresponding pay basis.
1456 l_difference := ghr_pay_calc.convert_amount(l_converted_total_salary - l_pay_cap_amount
1457 , 'PA'
1458 ,l_pay_basis);
1459 l_converted_total_salary := l_pay_cap_amount;
1460
1461 if l_difference > nvl(l_retention_allowance,0) then
1462 if l_retention_allowance is not null then
1463 l_retention_allowance := 0;
1464 l_retention_allow_percentage := NULL;
1465 end if;
1466 l_other_pay_amount :=
1467 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1468 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1469 else
1470 l_retention_allowance := nvl(l_retention_allowance,0) - l_difference;
1471
1472 --Pradeep for Bug 3306515.
1473 --l_retention_allow_percentage := NULL;
1474 --Bug 4744349 added trunc condition
1475 l_retention_allow_percentage := trunc((l_retention_allowance/p_basic_pay)*100,2);
1476
1477 l_other_pay_amount :=
1478 nvl(l_retention_allowance,0) + nvl(l_au_overtime,0) + nvl(l_availability_pay,0)
1479 + nvl(l_supervisory_allowance,0) + nvl(l_staffing_differential,0);
1480 end if;
1481 hr_utility.set_location('l_converted_total_salary is '||l_converted_total_salary,1);
1482 hr_utility.set_location('l_v_capped_other_pay is '||l_v_capped_other_pay,1);
1483 hr_utility.set_location('l_supervisory_allowance is '||l_supervisory_allowance,1);
1484 hr_utility.set_location('l_capped_other_pay is '||l_capped_other_pay,1);
1485
1486 /* for Bug 3306515 instead of Warning Message give an error message
1487 when supervisory allowance is present and give warning message
1488 and adjust the % when no supervisory is available.
1489 */
1490 -- Sundar. Comparing Supervisory element value with form value to find whether
1491 -- it has changed or not.
1492 ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
1493 ,p_input_value_name => 'Amount'
1494 ,p_assignment_id => l_assignment_id
1495 ,p_effective_date => p_effective_date
1496 ,p_value => l_ele_supervisory
1497 ,p_multiple_error_flag => l_multi_error_flag);
1498
1499 hr_utility.set_location('l_ele_supervisory is '||l_ele_supervisory,1);
1500
1501 if nvl(l_v_capped_other_pay,0) <> nvl(l_capped_other_pay,0) then
1502 --hr_utility.set_message(8301,'GHR_38585_PAY_CAP5');
1503 IF nvl(l_supervisory_allowance ,0) <> l_ele_supervisory THEN
1504 hr_utility.set_message(8301,'GHR_38893_PAY_CAP7');
1505 hr_utility.set_message_token('PAY_CAP_AMT',l_pay_cap_amount);
1506 hr_utility.set_message_token('CAL_OP_PERC',l_retention_allow_percentage);
1507 p_pay_cap_message := TRUE;
1508 raise pay_cap_failed;
1509 ELSE
1510 hr_utility.set_message(8301,'GHR_38585_PAY_CAP5');
1511 END IF;
1512
1513 end if;
1514
1515
1516 ELSIF l_converted_total_salary < l_pay_cap_amount THEN
1517 l_capped_other_pay := NULL;
1518 hr_utility.set_location('l_capped_other_pay is '||l_capped_other_pay,1);
1519 ELSIF l_converted_total_salary = l_pay_cap_amount THEN
1520
1521 if nvl(l_810_ra ,0) > nvl(l_retention_allowance,0) then
1522 ----Basically no change in the capped other pay
1523 l_capped_other_pay := p_capped_other_pay;
1524 elsif l_810_ra is null and l_retention_allowance is null then
1525 l_capped_other_pay := p_capped_other_pay;
1526 elsif nvl(l_capped_other_pay,0) = nvl(p_capped_other_pay,0) then
1527 l_capped_other_pay := p_capped_other_pay;
1528 else
1529 l_capped_other_pay := NULL;
1530 end if;
1531
1532 END IF;
1533 -- Bug 5482191 Start
1534 ELSIF get_job_from_pos(l_pay_cap_in_data.effective_date, p_to_position_id) NOT IN ('0602','0680')
1535 AND (l_pay_plan IN ('YA','YB','YC','YD','YE','YF','YH','YI','YK','YL','YM','YN','YP') OR
1536 (l_pay_plan = 'YJ' AND l_grade_or_level IN ('01','02','03'))
1537 ) THEN
1538 IF l_pay_cap_in_data.pay_rate_determinant IN ('0','4','T') THEN
1539 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1540 ,'01'
1541 ,'00'
1542 ,l_pay_cap_in_data.effective_date);
1543 ELSIF l_pay_cap_in_data.pay_rate_determinant = 'R' THEN
1544 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
1545 ,'02'
1546 ,'00'
1547 ,l_pay_cap_in_data.effective_date);
1548 --Begin bug# 8324201
1549 ELSIF l_pay_cap_in_data.pay_rate_determinant = '2' AND ( perf_certified(l_agency_subele_code,l_org_id, l_pay_plan, p_effective_date)) THEN
1550 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VZ'
1551 ,'00'
1552 ,'00'
1553 ,l_pay_cap_in_data.effective_date);
1554 --End bug# 8324201
1555 END IF;
1556 IF l_pay_cap_amount < l_converted_total_salary THEN
1557 --Begin bug# 8324201
1558 IF l_pay_cap_in_data.pay_rate_determinant = '2' AND ( perf_certified(l_agency_subele_code,l_org_id, l_pay_plan, p_effective_date)) THEN
1559 hr_utility.set_message(8301,'GHR_38187_IG_PAY_CAP2');
1560 hr_utility.set_message_token('PAY_CAP_AMT',l_pay_cap_amount);
1561 hr_utility.raise_error;
1562 ELSE
1563 --End bug# 8324201
1564 l_converted_total_salary := l_pay_cap_amount;
1565 l_capped_other_pay := l_converted_total_salary - l_converted_adj_basic_pay;
1566 hr_utility.set_message(8301,'GHR_38585_PAY_CAP5'); -- Bug 5663050
1567 END IF;
1568 END IF;
1569 -- Bug 5482191 End
1570 -- Bug 5663050 Start
1571 ELSIF get_job_from_pos(l_pay_cap_in_data.effective_date, p_to_position_id) IN ('0602','0680')
1572 AND (l_pay_plan = 'YG' OR
1573 (l_pay_plan = 'YJ' AND l_grade_or_level = '04')
1574 ) THEN
1575 IF l_pay_cap_in_data.pay_rate_determinant IN ('0','4','T','R') THEN
1576 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('VX'
1577 ,'00'
1578 ,'00'
1579 ,l_pay_cap_in_data.effective_date);
1580 IF l_pay_cap_amount < l_converted_total_salary THEN
1581 l_converted_total_salary := l_pay_cap_amount;
1582 l_capped_other_pay := l_converted_total_salary - l_converted_adj_basic_pay;
1583 hr_utility.set_message(8301,'GHR_38585_PAY_CAP5');
1584 END IF;
1585 END IF;
1586 -- Bug 5663050 End
1587 END IF;
1588
1589 if l_update34_date is not null AND p_effective_date >= l_update34_date then
1590 p_locality_adj := ghr_pay_calc.convert_amount(l_converted_locality_adj
1591 , 'PA'
1592 ,l_pay_basis);
1593 else
1594 p_locality_adj := l_converted_locality_adj;
1595 end if;
1596 p_adj_basic_pay := ghr_pay_calc.convert_amount(l_converted_adj_basic_pay
1597 , 'PA'
1598 ,l_pay_basis);
1599 p_total_salary := ghr_pay_calc.convert_amount(l_converted_total_salary
1600 , 'PA'
1601 ,l_pay_basis);
1602 if nvl(l_capped_other_pay,0) = nvl(l_other_pay_amount,0) then
1603 p_capped_other_pay := NULL;
1604 else
1605 p_capped_other_pay := l_capped_other_pay;
1606 end if;
1607 p_retention_allowance := l_retention_allowance;
1608 p_retention_allow_percentage := l_retention_allow_percentage;
1609
1610 if (p_other_pay_amount is null) AND (l_other_pay_amount = 0) then
1611 p_other_pay_amount := NULL;
1612 else
1613 p_other_pay_amount := l_other_pay_amount;
1614 end if;
1615 p_au_overtime := l_au_overtime;
1616 p_availability_pay := l_availability_pay;
1617
1618 l_pay_cap_in_data.locality_adj := p_locality_adj;
1619 l_pay_cap_in_data.adj_basic_pay := p_adj_basic_pay;
1620 l_pay_cap_in_data.total_salary := p_total_salary;
1621 l_pay_cap_in_data.other_pay_amount := p_other_pay_amount;
1622 l_pay_cap_in_data.au_overtime := p_au_overtime;
1623 l_pay_cap_in_data.availability_pay := p_availability_pay;
1624 l_pay_cap_in_data.retention_allowance := p_retention_allowance;
1625 l_pay_cap_in_data.retention_allow_percentage := p_retention_allow_percentage;
1626 l_pay_cap_in_data.capped_other_pay := p_capped_other_pay;
1627 l_pay_cap_in_data.pa_request_id := p_pa_request_id;
1628 --
1629 ghr_custom_pay_cap.custom_hook
1630 (l_pay_cap_in_data
1631 ,l_pay_cap_out_data);
1632 --
1633 --
1634 -- always set the out paramaters
1635 p_open_pay_fields := l_pay_cap_out_data.open_pay_fields;
1636 p_message_set := l_pay_cap_out_data.message_set;
1637
1638 --To support some of the countries pay cap like total pay = basic pay
1639 --Bug 2064497 CHANGES TO SUPPORT PAY CALC FOR STATE/ LOCAL NATIONALS
1640 IF p_basic_pay = l_pay_cap_out_data.total_salary then
1641
1642 p_locality_adj := 0;
1643 p_adj_basic_pay := p_basic_pay;
1644 p_total_salary := p_basic_pay;
1645 p_other_pay_amount := NULL;
1646 p_retention_allowance := NULL;
1647 p_retention_allow_percentage := NULL;
1648 p_au_overtime := NULL;
1649 p_availability_pay := NULL;
1650 p_capped_other_pay := NULL;
1651 p_total_pay_check := nvl(l_pay_cap_out_data.total_pay_check,'Y');
1652
1653 ELSE
1654 p_locality_adj := nvl(l_pay_cap_out_data.locality_adj,l_pay_cap_in_data.locality_adj);
1655 p_adj_basic_pay := nvl(l_pay_cap_out_data.adj_basic_pay,l_pay_cap_in_data.adj_basic_pay);
1656 p_total_salary := nvl(l_pay_cap_out_data.total_salary,l_pay_cap_in_data.total_salary);
1657 p_other_pay_amount := nvl(l_pay_cap_out_data.other_pay_amount,l_pay_cap_in_data.other_pay_amount);
1658 p_retention_allowance := nvl(l_pay_cap_out_data.retention_allowance,l_pay_cap_in_data.retention_allowance);
1659 p_retention_allow_percentage := l_retention_allow_percentage;
1660 p_au_overtime := nvl(l_pay_cap_out_data.au_overtime,l_pay_cap_in_data.au_overtime);
1661 p_availability_pay := nvl(l_pay_cap_out_data.availability_pay,l_pay_cap_in_data.availability_pay);
1662 p_capped_other_pay := nvl(l_pay_cap_out_data.capped_other_pay,l_pay_cap_in_data.capped_other_pay);
1663 p_total_pay_check := nvl(l_pay_cap_out_data.total_pay_check,'Y');
1664
1665 END IF;
1666
1667 p_pay_cap_message := nvl(l_pay_cap_out_data.pay_cap_message,l_pay_cap_message);
1668 p_pay_cap_adj := nvl(l_pay_cap_out_data.pay_cap_adj,l_temp_ret_allowance);
1669 --
1670 if nvl(l_pay_cap_out_data.adj_basic_mesg_flag,l_adj_basic_mesg_flag) = 1 then
1671 p_adj_basic_message := TRUE;
1672 raise pay_cap_failed;
1673 end if;
1674 --
1675 p_adj_basic_message := l_adj_basic_message;
1676
1677 EXCEPTION
1678 WHEN pay_cap_failed THEN
1679 -- bug 708295 do not open pay fields any more if cap exceeded
1680 l_pay_cap_out_data.open_pay_fields := FALSE;
1681 l_pay_cap_out_data.message_set := TRUE;
1682 if l_adj_basic_mesg_flag = 1 then
1683 l_adj_basic_message := TRUE;
1684 end if;
1685 p_pay_cap_adj :=nvl(l_pay_cap_out_data.pay_cap_adj,l_temp_ret_allowance);
1686 /* Commenting this call because there is no need to call this again as already there is
1687 call to custom_hook above. Also there is no change in pay component values between raising of
1688 exception and here.
1689 ghr_custom_pay_cap.custom_hook
1690 (l_pay_cap_in_data
1691 ,l_pay_cap_out_data);
1692 */
1693 --
1694 END;
1695 -- Initialization of out and in out parameters in case of exceptions raised by
1696 -- non 810 actions
1697 IF l_non_810_error THEN
1698 IF l_update34_date is not null AND p_effective_date >= l_update34_date THEN
1699 p_locality_adj := ghr_pay_calc.convert_amount(l_converted_locality_adj
1700 , 'PA'
1701 ,l_pay_basis);
1702 ELSE
1703 p_locality_adj := l_converted_locality_adj;
1704 END IF;
1705 p_adj_basic_pay := ghr_pay_calc.convert_amount(l_converted_adj_basic_pay
1706 , 'PA'
1707 ,l_pay_basis);
1708 p_total_salary := ghr_pay_calc.convert_amount(l_converted_total_salary
1709 , 'PA'
1710 ,l_pay_basis);
1711 if nvl(l_capped_other_pay,0) = nvl(l_other_pay_amount,0) then
1712 p_capped_other_pay := NULL;
1713 else
1714 p_capped_other_pay := l_capped_other_pay;
1715 end if;
1716 p_retention_allowance := l_retention_allowance;
1717 p_retention_allow_percentage := l_retention_allow_percentage;
1718
1719 if (p_other_pay_amount is null) AND (l_other_pay_amount = 0) then
1720 p_other_pay_amount := NULL;
1721 else
1722 p_other_pay_amount := l_other_pay_amount;
1723 end if;
1724 p_au_overtime := l_au_overtime;
1725 p_availability_pay := l_availability_pay;
1726 -- Out messages
1727 p_pay_cap_message := l_pay_cap_message;
1728 p_pay_cap_adj := l_temp_ret_allowance;
1729 --
1730 p_total_pay_check := nvl(l_pay_cap_out_data.total_pay_check,'Y');
1731 IF l_adj_basic_mesg_flag = 1 then
1732 p_adj_basic_message := TRUE;
1733 END IF;
1734 END IF;
1735 END IF;
1736 EXCEPTION
1737 WHEN ghr_pay_calc.pay_calc_message THEN
1738 null;
1739 END do_pay_caps_main;
1740 --
1741 --
1742 PROCEDURE do_pay_caps_sql ( p_pa_request_id IN NUMBER --NEW
1743 ,p_effective_date IN DATE
1744 ,p_pay_rate_determinant IN VARCHAR2
1745 ,p_pay_plan IN VARCHAR2
1746 ,p_to_position_id IN NUMBER
1747 ,p_pay_basis IN VARCHAR2
1748 ,p_person_id IN NUMBER
1749 ,p_noa_code IN VARCHAR2 --New
1750 ,p_basic_pay IN NUMBER
1751 ,p_locality_adj IN OUT NOCOPY NUMBER
1752 ,p_adj_basic_pay IN OUT NOCOPY NUMBER
1753 ,p_total_salary IN OUT NOCOPY NUMBER
1754 ,p_other_pay_amount IN OUT NOCOPY NUMBER
1755 ,p_capped_other_pay IN OUT NOCOPY NUMBER --New
1756 ,p_retention_allowance IN OUT NOCOPY NUMBER --New
1757 ,p_retention_allow_percentage IN OUT NOCOPY NUMBER --New
1758 ,p_supervisory_allowance IN NUMBER --New
1759 ,p_staffing_differential IN NUMBER --New
1760 ,p_au_overtime IN OUT NOCOPY NUMBER
1761 ,p_availability_pay IN OUT NOCOPY NUMBER
1762 ,p_adj_basic_message OUT NOCOPY BOOLEAN
1763 ,p_pay_cap_message OUT NOCOPY BOOLEAN
1764 ,p_pay_cap_adj OUT NOCOPY NUMBER
1765 ,p_open_pay_fields OUT NOCOPY BOOLEAN
1766 ,p_message_set IN OUT NOCOPY BOOLEAN
1767 ,p_total_pay_check OUT NOCOPY VARCHAR2) IS
1768 BEGIN
1769 do_pay_caps_main ( p_pa_request_id => p_pa_request_id
1770 ,p_effective_date => p_effective_date
1771 ,p_duty_station_id => NULL --bug# 13414643,13403289
1772 ,p_pay_rate_determinant => p_pay_rate_determinant
1773 ,p_pay_plan => p_pay_plan
1774 ,p_to_position_id => p_to_position_id
1775 ,p_pay_basis => p_pay_basis
1776 ,p_person_id => p_person_id
1777 ,p_noa_code => p_noa_code
1778 ,p_basic_pay => p_basic_pay
1779 ,p_locality_adj => p_locality_adj
1780 ,p_adj_basic_pay => p_adj_basic_pay
1781 ,p_total_salary => p_total_salary
1782 ,p_other_pay_amount => p_other_pay_amount
1783 ,p_capped_other_pay => p_capped_other_pay
1784 ,p_retention_allowance => p_retention_allowance
1785 ,p_retention_allow_percentage => p_retention_allow_percentage
1786 ,p_supervisory_allowance => p_supervisory_allowance
1787 ,p_staffing_differential => p_staffing_differential
1788 ,p_au_overtime => p_au_overtime
1789 ,p_availability_pay => p_availability_pay
1790 ,p_adj_basic_message => p_adj_basic_message
1791 ,p_pay_cap_message => p_pay_cap_message
1792 ,p_pay_cap_adj => p_pay_cap_adj
1793 ,p_open_pay_fields => p_open_pay_fields
1794 ,p_message_set => p_message_set
1795 ,p_total_pay_check => p_total_pay_check);
1796
1797 IF p_message_set THEN
1798 hr_utility.raise_error;
1799 END IF;
1800
1801 END do_pay_caps_sql;
1802
1803 END ghr_pay_caps;