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