1 package body GHR_Validate_CHECK AS
2 /* $Header: ghrvalid.pkb 120.31.12010000.3 2008/12/12 09:40:29 utokachi ship $ */
3
4 procedure Validate_CHECK(p_pa_request_rec IN ghr_pa_requests%ROWTYPE
5 ,p_per_group1 IN ghr_api.per_group1_type
6 ,p_per_retained_grade IN ghr_api.per_retained_grade_type
7 ,p_per_sep_retire in ghr_api.per_sep_retire_type
8 ,p_per_conversions in ghr_api.per_conversions_type
9 ,p_per_uniformed_services in ghr_api.per_uniformed_services_type
10 ,p_pos_grp1 in ghr_api.pos_grp1_type
11 ,p_pos_valid_grade in ghr_api.pos_valid_grade_type
12 ,p_loc_info in ghr_api.loc_info_type
13 ,p_sf52_from_data in ghr_api.prior_sf52_data_type
14 ,p_personal_info in ghr_api.personal_info_type
15 ,p_agency_code in varchar2
16 ,p_gov_awards_type in ghr_api.government_awards_type
17 ,p_perf_appraisal_type in ghr_api.performance_appraisal_type
18 ,p_health_plan in varchar2
19 ,p_asg_non_sf52 in ghr_api.asg_non_sf52_type
20 --Pradeep
21 ,p_premium_pay in ghr_api.premium_pay_type
22 --Bug#5036370
23 ,p_per_service_oblig in ghr_api.per_service_oblig_type
24 ,p_within_grade_incr in ghr_api.within_grade_increase_type --Bug 5527363
25 ) IS
26 l_assignment_found boolean := FALSE;
27 l_per_system_status VARCHAR2(30);
28
29 /* Get Person Type */
30 l_person_type_id per_people_f.person_type_id%type;
31 l_person_usr_type per_person_types.user_person_type%type;
32 l_person_sys_type per_person_types.system_person_type%type;
33 l_psn_status per_positions.status%type;
34
35 l_part_time_hr ghr_pa_requests.part_time_hours%type;
36 l_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
37
38 l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39 l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40 l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
41 l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42 l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43 l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
44
45 l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
46 l_from_step_or_rate ghr_pa_requests.from_step_or_rate%type;
47
48 l_Retention_Allowance ghr_pa_requests.TO_RETENTION_ALLOWANCE%type;
49 l_staffing_differential ghr_pa_requests.TO_STAFFING_DIFFERENTIAL%type;
50
51 l_effective_date ghr_pa_requests.EFFECTIVE_DATE%type;
52
53 l_AUO varchar2(30):=null;
54 l_Availablility varchar2(30):=null;
55 l_assignment_id ghr_pa_requests.EMPLOYEE_ASSIGNMENT_ID%type;
56 l_prem_pay varchar2(30);
57 l_amount number;
58 l_multiple_error_flag boolean;
59 l_session ghr_history_api.g_session_var_type;
60 l_message_set boolean;
61 l_open_pay_fields boolean;
62 l_for_810_count number;
63 l_op_810 number;
64 l_op_818 number;
65 l_op_819 number;
66 l_reta_amount number;
67 l_supv_amount number;
68 l_stad_amount number;
69 l_ap_amount number;
70 l_auo_amount number;
71 l_exists boolean;
72 l_noa_family_code ghr_families.noa_family_code%type;
73 l_to_agency_code per_people_extra_info.pei_information8%type;
74 l_to_supervisor_diff ghr_pa_requests.TO_SUPERVISORY_DIFFERENTIAL%type ;
75 l_award_salary ghr_pa_requests.from_basic_pay%type ; -- Bug 3376761
76
77 l_slr_recur_amount number;
78 l_slr_lumpsum number;
79
80 --Pradeep
81 l_mddds_special_pay_amount number;
82 l_mddds_specia_pay_nte_date date;
83 l_mddds_pay_amount_old number;
84 l_mddds_pay_nte_date_old ghr_pa_request_extra_info.rei_information12%TYPE;
85
86 l_premium_pay_ind VARCHAR2(30);
87 l_hz_ind VARCHAR2(30);
88 l_edp_ind VARCHAR2(30);
89
90 l_eff_start_date Date;
91 l_hz_eff_start_date Date;
92 l_edp_eff_start_date Date;
93 l_null_list varchar2(2000);
94 l_new_line VARCHAR2(1) := substr('
95 ',1,1);
96 -- Bug#5036370
97 l_serv_oblig_code per_people_extra_info.pei_information3%type;
98 l_serv_oblig_stdt DATE;
99 l_serv_oblig_enddt DATE;
100 --Open Pay Range variables.
101 l_row_high number;
102 l_row_low number;
103 l_user_table_id number;
104 l_pay_plan VARCHAR2(30);
105 l_grade_or_level VARCHAR2(60);
106 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
107
108 ------- Retrieve Position Status --
109 cursor c_get_psn_status(p_position_id number) is
110 select pps.status from hr_all_positions_f pps -- Venkat - Position DT
111 where pps.position_id = p_position_id
112 and p_pa_request_rec.effective_date between pps.effective_start_date
113 and pps.effective_end_date;
114 -------
115
116 ------- Retrieve Person Types (System/User) --
117 cursor c_get_person_types(p_person_id number, p_eff_date date) is
118 select ppt.system_person_type, ppt.user_person_type from PER_PERSON_TYPES ppt, PER_PEOPLE_F ppf
119 where ppf.person_id = p_person_id
120 and trunc(p_eff_date) between ppf.effective_start_date and ppf.effective_end_date
121 and ppt.person_type_id = ppf.person_type_id;
122 -------
123
124 -------
125 cursor c_asg_posn_check(p_To_Position_id number, p_person_id number, p_eff_date date) is
126 select asg1.assignment_id
127 from per_assignments_f asg1
128 where
129 asg1.person_id <> p_person_id
130 and asg1.position_id = p_to_position_id
131 and asg1.assignment_type NOT IN ('B','A')
132 and (asg1.effective_start_date >= p_eff_date
133 or p_eff_date
134 between asg1.effective_start_date and asg1.effective_end_date);
135
136 cursor c_asg_posn_check1(p_To_Position_id number, p_person_id number, p_eff_date date) is
137 select asg1.assignment_id
138 from per_assignments_f asg1
139 where asg1.person_id <> p_person_id
140 and asg1.position_id = p_to_position_id
141 and asg1.assignment_type NOT IN ('B','A')
142 and (p_eff_date
143 between asg1.effective_start_date and asg1.effective_end_date);
144
145 -------
146
147 ------- Retrieve Person Sytem Status --
148 cursor c_asg_stat_type (p_asg_id number, p_eff_date date, p_status char) is
149 Select per_system_status from per_assignment_status_types pst, per_assignments_f paf
150 where paf.assignment_status_type_id = pst.assignment_status_type_id
151 and paf.assignment_type <> 'B'
152 and paf.assignment_id = p_asg_id
153 and pst.per_system_status = p_status
154 and trunc(p_eff_date) between paf.effective_start_date and paf.effective_end_date;
155 -------
156 --Bug# 883594 -- Venkat
157 --Begin Bug# 7501214. added the SUBSTR condition for lac_lookup_code for all the 4 cursors
158 -- to avoid the error while checking the LAC codes which are duplicated. Like VWN and VWN1 etc.
159 cursor c_first_la_code1 is
160 select 1
161 from ghr_noac_las nla
162 where nla.nature_of_action_id = p_pa_request_rec.first_noa_id
163 and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.first_action_la_code1
164 and nla.valid_first_lac_flag = 'Y'
165 and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
166 between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
167 and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
168
169 cursor c_first_la_code2 is
170 select 1
171 from ghr_noac_las nla
172 where nla.nature_of_action_id = p_pa_request_rec.first_noa_id
173 and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.first_action_la_code2
174 and nla.valid_second_lac_flag = 'Y'
175 and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
176 between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
177 and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
178
179 cursor c_second_la_code1 is
180 select 1
181 from ghr_noac_las nla
182 where nla.nature_of_action_id = p_pa_request_rec.second_noa_id
183 and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.second_action_la_code1
184 and nla.valid_first_lac_flag = 'Y'
185 and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
186 between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
187 and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
188
189 cursor c_second_la_code2 is
190 select 1
191 from ghr_noac_las nla
192 where nla.nature_of_action_id = p_pa_request_rec.second_noa_id
193 and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.second_action_la_code2
194 and nla.valid_second_lac_flag = 'Y'
195 and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
196 between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
197 and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
198 --end Bug# 7501214
199 CURSOR cur_temp_step IS
200 SELECT rei_information3 temp_step
201 FROM ghr_pa_request_extra_info
202 WHERE pa_request_id = p_pa_request_rec.pa_request_id
203 AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
204 l_temp_step per_people_extra_info.pei_information9%type := hr_api.g_varchar2;
205
206 -- Family Code
207 -- Bug # 1145963
208 -- Bug#3941541 Added effective date condition.
209 Cursor c_noa_family_code(l_effective_date DATE) IS
210 Select fam.noa_family_code
211 from ghr_noa_families nfa,
212 ghr_families fam
213 where nfa.nature_of_action_id = p_pa_request_rec.first_noa_id
214 and nfa.noa_family_code = fam.noa_family_code
215 and fam.update_hr_flag = 'Y'
216 and l_effective_date between NVL(nfa.start_date_active,l_effective_date)
217 and NVL(nfa.end_date_active,l_effective_date);
218
219
220
221 CURSOR check_for_supervisory IS
222 select eev.screen_entry_value
223 from pay_element_types_f elt,
224 pay_input_values_f ipv,
225 pay_element_entries_f ele,
226 pay_element_entry_values_f eev
227 where trunc(p_pa_request_rec.effective_date) between elt.effective_start_date
228 and elt.effective_end_date
229 and trunc(p_pa_request_rec.effective_date) between ipv.effective_start_date
230 and ipv.effective_end_date
231 and trunc(p_pa_request_rec.effective_date) between ele.effective_start_date
232 and ele.effective_end_date
233 and trunc(p_pa_request_rec.effective_date) between eev.effective_start_date
234 and eev.effective_end_date
235 and elt.element_type_id = ipv.element_type_id
236 and ele.assignment_id = p_pa_request_rec.employee_assignment_id
237 and elt.element_name IN ('Supervisory Differential','AUO','Availability Pay')
238 and ipv.input_value_id = eev.input_value_id
239 and ele.element_entry_id + 0 = eev.element_entry_id ;
240
241 -- Sundar 3263109 To find out if any future actions exist.
242 CURSOR c_future_actions(c_person_id ghr_pa_requests.person_id%type, c_effective_date ghr_pa_requests.effective_date%type) IS
243 SELECT par.pa_request_id futr_rpa
244 FROM ghr_pa_routing_history prh
245 ,ghr_pa_requests par
246 WHERE prh.pa_request_id = par.pa_request_id
247 AND par.person_id = c_person_id
248 AND par.effective_date > c_effective_date
249 AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
250 FROM ghr_pa_routing_history prh2
251 WHERE prh2.pa_request_id = par.pa_request_id)
252 AND prh.action_taken IN ('FUTURE_ACTION','UPDATE_HR_COMPLETE')
253 AND par.NOA_FAMILY_CODE <> 'CANCEL'
254 AND ( ( par.second_noa_code IS NULL
255 AND NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
256 )
257 OR ( par.second_noa_code IS NOT NULL
258 AND par.NOA_FAMILY_CODE <> 'CORRECT'
259 AND ( NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
260 OR NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
261 )
262 )
263 OR ( par.second_noa_code IS NOT NULL
264 AND par.NOA_FAMILY_CODE = 'CORRECT'
265 AND NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
266 )
267 );
268
269 l_futr_actions ghr_pa_requests.pa_request_id%type;
270
271 --
272 -- Madhuri 3417859 Start fix of variables and cursor
273 --
274 l_user_tab_id pay_user_tables.user_table_id%type;
275
276 cursor c_pay_tab_essl
277 is
278 select 1 from pay_user_tables
279 where substr(user_table_name,1,4) = 'ESSL'
280 and user_table_id = l_user_tab_id;
281
282 l_essl_table BOOLEAN := FALSE;
283
284 --
285 -- Madhuri 3417859 End fix of variables and cursors
286 --
287 CURSOR cur_mddds_pay IS
288 SELECT NVL(rei_information3,0)+NVL(rei_information4,0)+NVL(rei_information5,0)+NVL(rei_information6,0)
289 +NVL(rei_information7,0)+NVL(rei_information8,0)+NVL(rei_information9,0)+NVL(rei_information10,0) amount,
290 rei_information12 nte_date,rei_information13 premium_pay_ind
291
292 FROM ghr_pa_request_extra_info
293 WHERE pa_request_id = p_pa_request_rec.pa_request_id
294 AND information_type = 'GHR_US_PAR_MD_DDS_PAY';
295
296 CURSOR cur_premium_pay IS
297 SELECT NVL(rei_information3,0) premium_pay_ind
298 FROM ghr_pa_request_extra_info
299 WHERE pa_request_id = p_pa_request_rec.pa_request_id
300 AND information_type = 'GHR_US_PAR_PREMIUM_PAY';
301
302
303 CURSOR cur_premium_pay_ind IS
304 SELECT NVL(rei_information3,0) premium_pay_ind
305 FROM ghr_pa_request_extra_info
306 WHERE pa_request_id = p_pa_request_rec.pa_request_id
307 AND information_type = 'GHR_US_PAR_PREMIUM_PAY_IND';
308
309 CURSOR cur_job_code is
310 SELECT from_occ_code
311 FROM ghr_pa_requests
312 WHERE pa_request_id = p_pa_request_rec.pa_request_id;
313
314 l_occ_code ghr_pa_requests.from_occ_code%TYPE;
315
316 --
317 --
318 CURSOR cur_repay_sch IS
319 SELECT rei_information8 repay_sch,
320 rei_information9 review_Date
321 FROM ghr_pa_request_extra_info
322 WHERE pa_request_id = p_pa_request_rec.pa_request_id
323 AND information_type = 'GHR_US_PAR_STUDENT_LOAN';
324
325 l_repay_sch varchar2(2);
326 l_review_date date;
327
328 CURSOR cur_studloan_ele_end_date(
329 p_ele_name pay_element_types_f.element_name%type,
330 p_asg_id ghr_pa_requests.employee_assignment_id%type)
331 IS
332 select ele.effective_end_date
333 from pay_element_types_f elt,
334 pay_element_links_f ell,
335 pay_element_entries_f ele
336 where p_pa_request_rec.effective_date between elt.effective_start_date and elt.effective_end_date
337 and p_pa_request_rec.effective_date between ell.effective_start_date and ell.effective_end_date
338 and p_pa_request_rec.effective_date between ele.effective_start_date and ele.effective_end_date
339 and elt.element_type_id = ell.element_type_id
340 and ell.element_link_id = ele.element_link_id
341 and ele.assignment_id = p_asg_id
342 and elt.element_name = p_ele_name;
343
344 /**** Commented because FP.F level DB is not having the element_type_id column
345 in pay_element_entries_f.
346
347 SELECT effective_end_date
348 FROM pay_element_entries_f
349 WHERE element_type_id = (SELECT element_type_id
350 FROM pay_element_types_f
351 WHERE element_name = p_ele_name
352 and p_pa_request_rec.effective_date between effective_start_Date and effective_end_date )
353 and assignment_id = p_asg_id;
354 ****/
355 -- 3562069
356
357 --Start of 3604377
358 l_pos_ei_grade_data per_position_extra_info%rowtype;
359
360 CURSOR cur_grd(p_grade_id per_grades.grade_id%TYPE) IS
361 SELECT gdf.segment1 pay_plan
362 ,gdf.segment2 grade_or_level
363 FROM per_grade_definitions gdf
364 ,per_grades grd
365 WHERE grd.grade_id = p_grade_id
366 AND grd.grade_definition_id = gdf.grade_definition_id;
367 --End of 3604377
368
369 l_appt_type PER_PEOPLE_EXTRA_INFO.pei_information3%type;
370 l_ele_name pay_element_types_f.element_name%type;
371 l_ele_end_date pay_element_entries_f.effective_end_date%type;
372
373 l_per_ei_data PER_PEOPLE_EXTRA_INFO%rowtype;
374 -- Bug#3928110
375 --l_percent NUMBER(8,2);
376
377 l_max_allowed_amount NUMBER;
378 l_min_allowed_amount NUMBER;
379 l_temp varchar2(100);
380
381
382 -- Start of bug 4016362
383
384 /***** Added Cursor BG_rec to get business group id given asg_id ****/
385
386 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
387 Select business_group_id bg
388 from per_assignments_f
389 where assignment_id = p_assignment_id
390 and p_eff_date between effective_start_date
391 and effective_end_date;
392
393 ll_bg_id per_all_assignments_f.business_group_id%type;
394
395 -- End of bug 4016362
396 -- Start of 3563491
397 l_asg_ei_data per_assignment_extra_info%rowtype;
398 l_nte_date_flg BOOLEAN:=FALSE;
399
400 --
401 CURSOR cur_nte_check(p_asg_id per_assignments_f.assignment_id%TYPE,
402 p_eff_date ghr_pa_requests.effective_date%TYPE)
403 IS
404 SELECT user_status
405 FROM per_assignment_status_types pst, per_assignments_f paf
406 WHERE paf.assignment_status_type_id = pst.assignment_status_type_id
407 AND paf.assignment_type <> 'B'
408 AND paf.assignment_id = p_asg_id
409 AND (p_eff_date) BETWEEN paf.effective_start_date AND paf.effective_end_date;
410
411 l_asg_status per_assignment_status_types.user_status%type;
412 --
413 --Begin Bug# 4748927
414 l_award_amount NUMBER;
415 --End Bug# 4748927
416
417 -- Bug#5039997 RRR Changes. Added variables, cursors.
418 -- Begin Bug# 5039100
419 l_inct_ctgy_pcnt NUMBER;
420 -- End Bug# 5039100
421 l_biweekly_end_date DATE;
422 l_futr_incentive_cnt NUMBER;
423 l_cnt NUMBER(2);
424 l_min_date DATE;
425 l_max_date DATE;
426 l_min_months NUMBER;
427 l_max_months NUMBER;
428
429 CURSOR c_incentives IS
430 SELECT pa_incentive_category_end_date,pa_incentive_category_percent
431 FROM ghr_pa_incentives
432 WHERE pa_request_id = p_pa_request_rec.pa_request_id
433 AND pa_incentive_category = 'Biweekly';
434
435 CURSOR c_incentive_cnt IS
436 SELECT count(*) cnt
437 FROM ghr_pa_incentives
438 WHERE pa_request_id = p_pa_request_rec.pa_request_id;
439
440 -- Bug#5041985
441 CURSOR c_futr_incentives(l_asg_id NUMBER, l_effective_date DATE) IS
442 SELECT count(*) cnt
443 FROM pay_element_entries_f ee, pay_element_types_f et
444 WHERE ee.assignment_id = l_asg_id
445 AND ee.element_type_id = et.element_type_id
446 AND et.element_name like '%Incentive%'
447 AND ee.effective_start_date > l_effective_date;
448
449 CURSOR cur_noa_id(l_noa_id NUMBER,l_noa_fam_code VARCHAR2, l_eff_date DATE) is
450 SELECT 1
451 FROM ghr_noa_families noa
452 WHERE noa.nature_of_action_id = l_noa_id
453 AND noa.noa_family_code = l_noa_fam_code
454 AND l_eff_date BETWEEN nvl(noa.start_date_active,l_eff_date)
455 AND nvl(noa.end_date_active,l_eff_date)
456 AND noa.enabled_flag = 'Y';
457 -- RRR Changes
458
459 --bug 5482191
460 l_psi VARCHAR2(10);
461
462
463 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
464 l_serv_oblg_type VARCHAR2(2);
465 l_serv_oblg_start_date VARCHAR2(22);
466 l_serv_oblg_end_date VARCHAR2(22);
467
468 CURSOR cur_service_oblg_ei IS
469 SELECT rei_information3 srvc_oblg_type,
470 rei_information4 srvc_oblg_st_date,
471 rei_information5 srvc_oblg_end_date
472 FROM ghr_pa_request_extra_info
473 WHERE pa_request_id = p_pa_request_rec.pa_request_id AND
474 information_type = 'GHR_US_PAR_SERVICE_OBLIGATION';
475 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
476
477 Begin
478
479 ghr_history_api.get_g_session_var(l_session);
480
481 /* Do not allow update to HR if Position_Status is Invalid */
482
483 FOR v_get_psn_status IN
484 c_get_psn_status(p_pa_request_rec.to_position_id) LOOP
485 l_psn_status := v_get_psn_status.status;
486 END LOOP;
487
488 If l_psn_status = 'INVALID' then
489 -- If position is invalid instead of erroring out straight away,
490 -- validate it and if valid, update the status, else give out the error
491 begin
492 ghr_validate_perwsdpo.validate_perwsdpo(p_pa_request_rec.to_position_id,p_pa_request_rec.effective_date);
493 ghr_validate_perwsdpo.update_posn_status(p_pa_request_rec.to_position_id,p_pa_request_rec.effective_date);
494 exception
495 when others then
496 hr_utility.set_message(8301,'GHR_38288_POSITION_INVALID');
497 hr_utility.raise_error;
498 end;
499 End If;
500 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_pa_request_rec.to_position_id,
501 p_pa_request_rec.effective_date);
502
503 /* Get Person Status and last of DDF retrivals.
504
505 /* Produce a warning if Person_User_Type is Invalid */
506 /* Best solution would offer a prompt of continue Y/N */
507 FOR v_get_person_types IN
508 c_get_person_types(p_pa_request_rec.person_id,p_pa_request_rec.effective_date) LOOP
509 l_person_sys_type := v_get_person_types.system_person_type;
510 l_person_usr_type := v_get_person_types.user_person_type;
511 END LOOP;
512 -- Bug 4377361 included EMP_APL for person type condition
513 If l_person_sys_type IN ('EMP','EMP_APL') and l_person_usr_type = 'Invalid Employee' then
514 hr_utility.set_message(8301,'GHR_38289_PERSON_INVALID');
515 --hr_utility.show_error;
516 End If;
517
518 FOR c_noa_family_code_rec in c_noa_family_code(p_pa_request_rec.effective_date) LOOP
519 l_noa_family_code := c_noa_family_code_rec.noa_family_code;
520 --exit;
521 END LOOP;
522
523 /*Bug 5909274 :-Throw an error message when NOAC is 890 , Legal Authority codes are QUM/QUA and the prior Pay Plan in block 8 is not 'GM' */
524
525 IF p_pa_request_rec.first_action_la_code1 = 'QUM' AND
526 p_pa_request_rec.first_action_la_code2 = 'QUA' AND
527 p_pa_request_rec.first_noa_code = '890' AND
528 p_pa_request_rec.from_pay_plan <> 'GM' THEN
529 hr_utility.set_message(8301, 'GHR_38553_GM_QUM_QUA');
530 hr_utility.raise_error;
531 END IF;
532
533 /* Do not allow creation of assignment if Position is Primary Assignment of another Person */
534 /* Cursor includes check to exclude from Position. */
535 /* only do this check if this is not a future action.*/
536 /* Bug 3454993: Check the following condition only if it is a cancellation action. (as a temp soln)*/
537 /* (Also we have to confirm that is it specific to separation cancellation action) */
538
539 --Start of Bug 3751864
540 --In case of Appointment only we need to check whether any Assginment is found in future.
541 IF (p_pa_request_rec.effective_date <= SYSDATE) THEN
542 IF ( l_noa_family_code IN (
543 'APP' , 'CONV_APP' , 'EXT_NTE' , 'GHR_SAL_CHG_LG',
544 'GHR_SAL_PROM', 'POS_ABOLISH' , 'POS_CHG' , 'POS_ESTABLISH',
545 'POS_REVIEW' , 'REASSIGNMENT' , 'RECRUIT_FILL' , 'RETURN_TO_DUTY')
546 -- Bug#5063298 Restrict this validation for Correction actions.
547 AND l_session.noa_id_correct is NULL
548 ) THEN
549 FOR v_asg_posn_check IN c_asg_posn_check(p_pa_request_rec.TO_Position_id,
550 p_pa_request_rec.person_id,
551 p_pa_request_rec.effective_date)
552 LOOP
553 l_assignment_found := TRUE;
554 END LOOP;
555
556 IF l_assignment_found THEN
557 hr_utility.set_message(8301,'GHR_38290_POSITION_ENCUMBERED');
558 hr_utility.raise_error;
559 END IF;
560
561 -- For other than Appt. we need to check whether any assignment is existing as on that date.
562
563 ELSE
564 FOR v_asg_posn_check1 IN c_asg_posn_check1(p_pa_request_rec.TO_Position_id,
565 p_pa_request_rec.person_id,
566 p_pa_request_rec.effective_date)
567 LOOP
568 l_assignment_found := TRUE;
569 END LOOP;
570
571 IF l_assignment_found THEN
572 hr_utility.set_message(8301,'GHR_38290_POSITION_ENCUMBERED');
573 hr_utility.raise_error;
574 END IF;
575 END IF;
576 END IF;
577 --End of Bug 3751864
578
579 /* If Family is appointment then employee_assignment_type must be ACCEPTED */
580 /* If Family is Return to Duty then employee_assignment_type must be SUSP_ASSIGN */
581 /* If Family is other than 2 above then employee_assignment_type must be ACTIVE_ASSIGN */
582 /* NOA_Family will not be coded as below */
583
584 l_assignment_found := FALSE;
585 If l_person_sys_type = 'APL' then
586 for c_asg_stat_type_rec in c_asg_stat_type (p_pa_request_rec.employee_assignment_id
587 ,p_pa_request_rec.effective_date
588 ,'ACCEPTED') loop
589 l_assignment_found := TRUE;
590 end loop;
591 if not l_assignment_found then
592 hr_utility.set_message(8301, 'GHR_38291_NOT_ACCEPTED');
593 hr_utility.raise_error;
594 end if;
595 -- Bug 4377361 included EMP_APL for person type condition
596 Elsif l_person_sys_type IN ('EMP','EMP_APL') then
597 -- If employees are added to APP family then may need to change this!!
598 -- Bug# 1145963 -- Fetching noa_family_code based on current first_noa_id since
599 -- p_pa_request_rec.noa_family_code always point to family code of original first_noa_id
600 hr_utility.set_location('GHRVALID-passed family code'||p_pa_request_rec.noa_family_code,1);
601 hr_utility.set_location('GHRVALID-current family code'||l_noa_family_code,2);
602 hr_utility.set_location('GHRVALID-noa code'||p_pa_request_rec.first_noa_code,3);
603
604 IF l_noa_family_code = 'RETURN_TO_DUTY' and l_session.noa_id_correct is null THEN
605 for c_asg_stat_type_rec in c_asg_stat_type (p_pa_request_rec.employee_assignment_id
606 ,p_pa_request_rec.effective_date
607 ,'SUSP_ASSIGN') LOOP
608 l_assignment_found := TRUE;
609 END LOOP;
610 IF not l_assignment_found THEN
611 hr_utility.set_message(8301, 'GHR_38292_NOT_SUSP_ASSIGN');
612 hr_utility.raise_error;
613 END IF;
614 /*
615 -- Note : Commenting the following 'Else part' as a temporary fix to bug 637083
616 -- Should be revisiting this code when we have the NOA specific Bus. rules in place
617 Else
618 for c_asg_stat_type_rec in c_asg_stat_type (p_pa_request_rec.employee_assignment_id
619 ,p_pa_request_rec.effective_date
620 ,'ACTIVE_ASSIGN') loop
621 l_assignment_found := TRUE;
622 end loop;
623 if not l_assignment_found then
624 hr_utility.set_message(8301, 'GHR_38293_NOT_ACTIVE_ASSIGN');
625 hr_utility.raise_error;
626 end if;
627 */
628 End If;
629 End If;
630
631 /* Sundar Bug 3263109/3263096 If doing separation, check if any future actions exist. If it exist
632 throw an error */
633 IF (l_noa_family_code = 'SEPARATION') THEN
634 FOR v_future_actions IN c_future_actions(p_pa_request_rec.person_id,p_pa_request_rec.effective_date) LOOP
635 l_futr_actions := v_future_actions.futr_rpa;
636 EXIT;
637 END LOOP;
638 IF (l_futr_actions IS NOT NULL) THEN
639 hr_utility.set_message(8301,'GHR_38847_NO_SEP_WITH_FUTR');
640 hr_utility.raise_error;
641 END IF;
642 END IF;
643 -- End Sundar Bug 3263109/3263096
644
645
646 --- Start fix Code 3417859
647 IF ( p_pa_request_rec.effective_date >= to_date('2004/01/11','YYYY/MM/DD') ) THEN
648 IF ( p_pa_request_rec.to_pay_plan in ('ES','EP','IE','FE') ) THEN
649
650 If p_pa_request_rec.first_noa_code ='893' THEN
651 hr_utility.set_message(8301, 'GHR_38889_SES_WGI_NO');
652 hr_utility.raise_error;
653 END IF;
654
655 l_user_tab_id := ghr_pay_calc.get_user_table_id(
656 p_position_id => p_pa_request_rec.to_position_id
657 ,p_effective_date => p_pa_request_rec.effective_date
658 );
659
660 l_essl_table := FALSE;
661 FOR essl_rec IN c_pay_tab_essl
662 LOOP
663 l_essl_table := TRUE;
664 END LOOP;
665
666 IF ( l_essl_table and p_pa_request_rec.to_step_or_rate <>'00' ) THEN
667 hr_utility.set_message(8301, 'GHR_38849_SES_TO_STEP_OR_RATE');
668 hr_utility.raise_error;
669 END IF;
670 END IF;
671 END IF;
672 --- End fix code 3417859
673 --Start of Bug#3604377
674 IF ( p_pa_request_rec.effective_date >= to_date('2004/01/11','YYYY/MM/DD') ) THEN
675 IF ( p_pa_request_rec.to_pay_plan in ('EE') ) THEN
676
677 IF p_pa_request_rec.first_noa_code ='893' THEN
678 hr_utility.set_message(8301, 'GHR_38897_EE_WGI_NO');
679 hr_utility.raise_error;
680 END IF;
681
682 l_user_tab_id := ghr_pay_calc.get_user_table_id(
683 p_position_id => p_pa_request_rec.to_position_id
684 ,p_effective_date => p_pa_request_rec.effective_date
685 );
686
687 l_essl_table := FALSE;
688 FOR essl_rec IN c_pay_tab_essl
689 LOOP
690 l_essl_table := TRUE;
691 END LOOP;
692
693 IF ( l_essl_table and p_pa_request_rec.to_step_or_rate <>'00' ) THEN
694 hr_utility.set_message(8301, 'GHR_38895_EE_TO_STEP_OR_RATE');
695 hr_utility.raise_error;
696 END IF;
697 END IF;
698 END IF;
699 --End of Bug#3604377
700 -- Bug# 1893483
701 /* Check whether agency code transferred to entered or not in case of 352 separation actions */
702 l_to_agency_code := p_per_sep_retire.agency_code_transfer_to;
703 If p_pa_request_rec.first_noa_code = '352' and
704 l_to_agency_code is NULL THEN
705 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
706 fnd_message.set_token('REQUIRED_LIST','Agency Code Transferred to');
707 hr_utility.raise_error;
708 End If;
709 -- Bug# 1893483
710
711 /* check part hours if work schedule is not F,G,B */
712 l_work_schedule_code:=p_pa_request_rec.WORK_SCHEDULE;
713 l_part_time_hr :=p_pa_request_rec.part_time_hours;
714 If l_work_schedule_code not in ('F','G','B','I','J') and
715 l_part_time_hr is null then
716 hr_utility.set_message(8301, 'GHR_38333_PART_TIME_HR_REQ');
717 hr_utility.raise_error;
718 end if;
719
720 -- Bug#5036370 RRR Changes
721 /* Check whether Service Obligation Information is entered for GHR_INCENTIVE Family or Not */
722 l_serv_oblig_code := p_per_service_oblig.service_oblig_type_code;
723 hr_utility.set_location('RRR 0'||p_per_service_oblig.service_oblig_start_date,10);
724 hr_utility.set_location('RRR 0'||p_per_service_oblig.service_oblig_end_date,10);
725 l_serv_oblig_stdt := fnd_date.canonical_to_date(p_per_service_oblig.service_oblig_start_date);
726 l_serv_oblig_enddt := fnd_date.canonical_to_date(p_per_service_oblig.service_oblig_end_date);
727
728 IF (l_noa_family_code = 'GHR_INCENTIVE' OR
729 (p_pa_request_rec.first_noa_code = '002' AND p_pa_request_rec.second_noa_code IN ('815','816','827'))
730 ) THEN
731 -- Bug#5040179
732 ghr_process_sf52.g_total_pay_check := 'N';
733 FOR incentive_rec IN c_incentive_cnt
734 LOOP
735 l_cnt := incentive_rec.cnt;
736 END LOOP;
737 IF l_cnt <= 0 THEN
738 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
739 fnd_message.set_token('REQUIRED_LIST','Incentive Category Details');
740 hr_utility.raise_error;
741 END IF;
742 IF NOT (p_pa_request_rec.first_noa_code <> '825' OR p_pa_request_rec.second_noa_code <> '825') THEN
743 IF NOT (p_pa_request_rec.first_noa_code = '827' AND p_pa_request_rec.pa_incentive_payment_option = 'B') THEN
744 hr_utility.set_location('first noa code '||p_pa_request_rec.first_noa_code,10);
745 IF ((l_serv_oblig_code IS NULL OR l_serv_oblig_stdt IS NULL) AND l_session.noa_id_correct IS NULL ) THEN
746 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
747 fnd_message.set_token('REQUIRED_LIST','Service Obligation Code, Service Obligation Start Date');
748 hr_utility.raise_error;
749 End If;
750 END IF;
751 IF l_serv_oblig_code IS NOT NULL AND l_serv_oblig_stdt IS NOT NULL THEN
752 /* Check the Service Obligation Period.*/
753 /*Bug # 6738306 "=" added in the below if condition and Token is set for the
754 error message*/
755 IF l_serv_oblig_stdt >= NVL(l_serv_oblig_enddt,to_date('4712/12/31','YYYY/MM/DD')) THEN
756 hr_utility.set_message(8301,'GHR_38999_STDT_GRTR_ENDDT');
757 FND_MESSAGE.SET_TOKEN('MESSAGE','Service Obligation End Date must be
758 greater than Service Obligation Start Date');
759 hr_utility.raise_error;
760 END IF;
761 IF p_pa_request_rec.first_noa_code = '815' THEN
762 l_min_months := 6;
763 l_max_months := 48;
764 ELSIF p_pa_request_rec.first_noa_code = '816' THEN
765 l_min_months := 0;
766 l_max_months := 48;
767 END IF;
768
769 l_min_date := ADD_MONTHS(l_serv_oblig_stdt,l_min_months);
770 l_max_date := ADD_MONTHS(l_serv_oblig_stdt,l_max_months);
771 IF NOT (NVL(l_serv_oblig_enddt,to_date('4712/12/31','YYYY/MM/DD')) BETWEEN l_min_date AND l_max_date) THEN
772 hr_utility.set_message(8301,'GHR_38998_INV_SERVOBL_PERIOD');
773 fnd_message.set_token('NOAC',p_pa_request_rec.first_noa_code);
774 fnd_message.set_token('MIN_PERIOD', to_char(l_min_months)||' Months');
775 fnd_message.set_token('MAX_PERIOD', ' 4 Years');
776 hr_utility.raise_error;
777 END IF;
778 END IF;
779 END IF;
780 END IF;
781 -- Bug#5039997
782 /*Check whether the Retention Incentive Review Date is entered for NOAC 827 where payment option "B" and
783 Effective End Date is NOT NULL */
784 IF p_pa_request_rec.first_noa_code = '827' AND p_pa_request_rec.pa_incentive_payment_option = 'B' THEN
785 l_cnt := 0;
786 For incentive_rec IN c_incentives
787 LOOP
788 l_biweekly_end_date := incentive_rec.pa_incentive_category_end_date;
789 l_inct_ctgy_pcnt := incentive_rec.pa_incentive_category_percent;
790 l_cnt := 1;
791 END LOOP;
792 -- Begin Bug# 5039100
793 IF l_inct_ctgy_pcnt <> 0 THEN
794 -- End Bug# 5039100
795 IF l_cnt = 1 AND l_biweekly_end_date IS NULL THEN
796 IF p_per_group1.retention_inc_review_date IS NULL THEN
797 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
798 fnd_message.set_token('REQUIRED_LIST','Retention Incentive Review Date');
799 hr_utility.raise_error;
800 END IF;
801 END IF;
802 END IF;
803 END IF;
804 /* Bug 5041985 -- If doing separation, check if any future Incentive elements exists.
805 If it exists, throw an error */
806 IF (l_noa_family_code = 'SEPARATION') THEN
807 FOR v_future_incentives IN c_futr_incentives(p_pa_request_rec.employee_assignment_id,
808 p_pa_request_rec.effective_date)
809 LOOP
810 l_futr_incentive_cnt := v_future_incentives.cnt;
811 EXIT;
812 END LOOP;
813 IF (l_futr_incentive_cnt > 0) THEN
814 hr_utility.set_message(8301,'GHR_38120_NO_SEP_FUTR_INCN_ELT');
815 hr_utility.raise_error;
816 END IF;
817 END IF;
818 /* Bug#5039691 Verify whether the NOA Code is Valid or not as on the effective date.
819 Verify this for all actions EXCEPT correction, cancellation. */
820 IF p_pa_request_rec.first_noa_code NOT IN ('001','002') THEN
821 l_exists := false;
822 FOR noa_id in cur_noa_id(p_pa_request_rec.first_noa_id,
823 l_noa_family_code,
824 p_pa_request_rec.effective_date)
825 LOOP
826 l_exists := true;
827 exit;
828 END LOOP;
829 -- to include logic to check if not valid as of the effective date
830 IF NOT l_exists THEN
831 hr_utility.set_message(8301, 'GHR_38167_INV_NAT_OF_ACT_FAM');
832 hr_utility.raise_error;
833 END IF;
834 END IF;
835 -- Bug#5036370 RRR Changes
836
837 /* check total pay when pay basis is 'PA' */
838 l_To_Pay_Basis:=p_pa_request_rec.TO_PAY_BASIS;
839 l_To_Locality_Adj:= p_pa_request_rec.TO_LOCALITY_ADJ;
840 l_To_Basic_Pay :=p_pa_request_rec.TO_BASIC_PAY;
841 l_To_adj_basic_pay := p_pa_request_rec.TO_ADJ_BASIC_PAY;
842 l_To_other_pay := p_pa_request_rec.TO_OTHER_PAY_AMOUNT;
843 l_To_total_pay := p_pa_request_rec.TO_TOTAL_SALARY;
844 l_to_supervisor_diff := p_pa_request_rec.TO_SUPERVISORY_DIFFERENTIAL ;
845
846 if l_To_Pay_Basis ='PA' and
847 nvl(l_To_adj_basic_pay,0) <> nvl(l_To_Basic_Pay,0) + nvl(l_To_Locality_Adj,0) then
848 hr_utility.set_message(8301, 'GHR_38334_ADJ_BASIC_PAY');
849 hr_utility.raise_error;
850 end if;
851 --bug 3584511
852 IF (p_pa_request_rec.noa_family_code <> 'GHR_STUDENT_LOAN' and ghr_process_sf52.g_total_pay_check = 'Y') THEN
853 if l_To_Pay_Basis ='PA' and
854 nvl(l_To_total_pay,0) <> nvl(l_To_adj_basic_pay,0) + nvl(l_To_other_pay,0) then
855 hr_utility.set_message(8301, 'GHR_38335_TOTAL_PAY');
856 hr_utility.raise_error;
857 end if;
858 END IF;
859 /* check staffing diff. */
860 l_staffing_differential:=p_pa_request_rec.TO_STAFFING_DIFFERENTIAL;
861 If l_staffing_differential is not null and
862 ghr_pay_calc.convert_amount(
863 l_staffing_differential,
864 'PA',
865 p_pa_request_rec.to_pay_basis)
866 > round((nvl(l_To_Basic_Pay,0) * 0.05)) then
867 hr_utility.set_message(8301, 'GHR_38344_INVALID_STAFF_DIFF');
868 hr_utility.raise_error;
869 end if;
870
871 /* check retention allowance */
872 l_Retention_Allowance:=p_pa_request_rec.TO_RETENTION_ALLOWANCE;
873 -- Code added for Student Loan
874 IF p_pa_request_rec.noa_family_code <> 'GHR_STUDENT_LOAN' then
875 if l_Retention_Allowance is not null and
876 l_Retention_Allowance > (nvl(l_To_Basic_Pay,0) * 0.25) then -- Bug 3067420 Removed 'ROUND'
877 hr_utility.set_message(8301, 'GHR_38345_INVALID_RET_ALLOW');
878 hr_utility.raise_error;
879 end if;
880 END IF;
881
882 -- Modified for FWS
883
884 IF p_pa_request_rec.to_pay_basis ='PH' AND
885 (p_pa_request_rec.first_noa_code IN ('818','819') or NVL(l_to_supervisor_diff,0) > 0) THEN
886 hr_utility.set_message(8301, 'GHR_38844_NOT_ENTITLED_OTH_PAY');
887 hr_utility.raise_error;
888 END IF;
889
890 IF p_pa_request_rec.from_pay_basis ='PA' and p_pa_request_rec.to_pay_basis ='PH' THEN
891 FOR chk_for_sup_rec IN check_for_supervisory LOOP
892 IF nvl(chk_for_sup_rec.screen_entry_value,0) > 0 THEN
893 hr_utility.set_message(8301, 'GHR_38844_NOT_ENTITLED_OTH_PAY');
894 hr_utility.raise_error;
895 END IF;
896 END LOOP;
897 END IF;
898
899 --Modified for FWS
900
901 /* commented it for bug 3218900 by Ashley
902 Added for bug 3067420 check supervisiry differential by Ashley
903 l_to_supervisor_diff := p_pa_request_rec.TO_SUPERVISORY_DIFFERENTIAL ;
904
905 if l_to_supervisor_diff is not null and
906 ghr_pay_calc.convert_amount(
907 l_to_supervisor_diff,
908 'PA',
909 p_pa_request_rec.to_pay_basis)
910 > (nvl(l_To_Basic_Pay,0) * 0.25) then
911 hr_utility.set_message(8301, 'GHR_SUP_DIFF_AMT_TOO_BIG');
912 hr_utility.raise_error;
913 end if;
914 */
915
916 /* check effective date */
917 /* Bug# 923276 - Remove 90-day limitation on effective date - Hence commented out - 24th July,1999
918 l_effective_date :=p_pa_request_rec.EFFECTIVE_DATE;
919 l_effective_date :=p_pa_request_rec.EFFECTIVE_DATE;
920 if (l_effective_date-sysdate) > 90 then
921 hr_utility.set_message(8301, 'GHR_38379_INV_EFFECT_DATE');
922 hr_utility.raise_error;
923 end if;
924 */
925
926
927 /* check that AUO and Availability Pay are mutually exclusive for a person */
928
929
930 If p_pa_request_rec.first_noa_code = '818' then -- if AUo
931 -- check if the person already gets an AP
932 ghr_api.retrieve_element_entry_value
933 (P_ELEMENT_NAME => 'Availability Pay',
934 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
935 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
936 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
937 P_VALUE => l_Prem_pay,
938 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
939 );
940
941 ghr_api.retrieve_element_entry_value
942 (P_ELEMENT_NAME => 'Availability Pay',
943 P_INPUT_VALUE_NAME => 'Amount',
944 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
945 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
946 P_VALUE => l_amount,
947 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
948 );
949
950 If l_prem_pay is not null or
951 l_amount is not null then
952 hr_utility.set_message(8301,'GHR_38387_AP_EXISTS');
953 hr_utility.raise_error;
954 End if;
955
956 Elsif p_pa_request_rec.first_noa_code = '819' then -- if AP
957 -- check if the person already gets an AUO
958 ghr_api.retrieve_element_entry_value
959 (P_ELEMENT_NAME => 'AUO',
960 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
961 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
962 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
963 P_VALUE => l_Prem_pay,
964 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
965 );
966
967 ghr_api.retrieve_element_entry_value
968 (P_ELEMENT_NAME => 'AUO',
969 P_INPUT_VALUE_NAME => 'Amount',
970 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
971 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
972 P_VALUE => l_amount,
973 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
974 );
975
976 If l_prem_pay is not null or
977 l_amount is not null then
978 hr_utility.set_message(8301,'GHR_38388_AUO_EXISTS');
979 hr_utility.raise_error;
980 End if;
981 End if;
982
983
984 /*---- Removed for Bug 708295 Check pay caps
985 ghr_pay_caps.do_pay_caps_sql
986 (p_effective_date => p_pa_request_rec.effective_date
987 ,p_pay_rate_determinant => p_pa_request_rec.pay_rate_determinant
988 ,p_pay_plan => p_pa_request_rec.to_pay_plan
989 ,p_pay_basis => p_pa_request_rec.to_pay_basis
990 ,p_to_position_id => p_pa_request_rec.to_position_id
991 ,p_basic_pay => p_pa_request_rec.to_basic_pay
992 ,p_locality_adj => p_pa_request_rec.to_locality_adj
993 ,p_adj_basic_pay => p_pa_request_rec.to_adj_basic_pay
994 ,p_total_salary => p_pa_request_rec.to_total_salary
995 ,p_other_pay_amount => p_pa_request_rec.to_other_pay_amount
996 ,p_au_overtime => p_pa_request_rec.to_au_overtime
997 ,p_availability_pay => p_pa_request_rec.to_availability_pay
998 ,p_open_pay_fields => l_open_pay_fields
999 ,p_message_set => l_message_set
1000 );
1001 */
1002
1003 --
1004 -- check that the UOM entered on a 872 is Hours.
1005 -- added for bug#705411
1006 --
1007 -- Start Bug 1379280
1008 if p_pa_request_rec.first_noa_code in ('846','847','872') and
1009 p_pa_request_rec.award_uom <> 'H' then
1010 hr_utility.set_message(8301,'GHR_38595_INVALID_AWARD_UOM');
1011 hr_utility.raise_error;
1012 end if;
1013 if p_pa_request_rec.first_noa_code in ('840','841','842','843','844','845','848',
1014 '849','815','816','825','878','879') and
1015 p_pa_request_rec.award_uom <> 'M' then
1016 hr_utility.set_message(8301,'GHR_38597_INVALID_AWARD_UOM');
1017 hr_utility.raise_error;
1018 end if;
1019 -- End Bug 1379280
1020
1021 --Pradeep for 3934195.
1022 IF p_pa_request_rec.noa_family_code = 'AWARD' THEN
1023 -- Begin Bug# 4748927
1024 -- Begin Bug# 5020754
1025 IF p_pa_request_rec.award_salary IS NULL THEN
1026 ghr_pay_calc.award_amount_calc (
1027 p_position_id => p_pa_request_rec.to_position_id
1028 ,p_pay_plan => p_pa_request_rec.from_pay_plan
1029 ,p_award_percentage => NULL
1030 ,p_user_table_id => p_pa_request_rec.from_pay_table_identifier
1031 ,p_grade_or_level => p_pa_request_rec.from_grade_or_level
1032 ,p_effective_date => p_pa_request_rec.effective_date
1033 ,p_basic_pay => p_pa_request_rec.from_basic_pay
1034 ,p_adj_basic_pay => p_pa_request_rec.from_adj_basic_pay
1035 ,p_duty_station_id => p_pa_request_rec.duty_station_id
1036 ,p_prd => p_pa_request_rec.pay_rate_determinant
1037 ,p_pay_basis => p_pa_request_rec.from_pay_basis
1038 ,p_person_id => p_pa_request_rec.person_id
1039 ,p_award_amount => l_award_amount
1040 ,p_award_salary => l_award_salary
1041 );
1042 ELSE
1043 l_award_salary := p_pa_request_rec.award_salary;
1044 END IF;
1045 -- End Bug# 5020754
1046 /*l_award_salary := ghr_pay_calc.convert_amount(p_pa_request_rec.from_basic_pay
1047 ,p_pa_request_rec.from_pay_basis,'PA');*/
1048 -- end Bug# 4748927
1049 --Use the same Message Name for All.
1050 hr_utility.set_message(8301,'GHR_38904_AWARD_AMT_TOO_BIG5');
1051
1052 --bug#5482191
1053
1054
1055 IF ( p_pa_request_rec.first_noa_code='844'
1056 OR p_pa_request_rec.second_noa_code='844' ) THEN
1057
1058 l_max_allowed_amount := 5*l_award_salary/100;
1059 hr_utility.set_message_token('ALLOWED','5%');
1060
1061 ELSIF ( p_pa_request_rec.first_noa_code IN ('840','841','879')
1062 OR p_pa_request_rec.second_noa_code IN ('840','841','879')
1063 OR p_pa_request_rec.first_noa_code IN ('885','886') AND l_psi = '00'
1064 OR p_pa_request_rec.second_noa_code IN ('885','886') AND l_psi = '00' ) THEN
1065
1066 l_max_allowed_amount := 25*l_award_salary/100;
1067 hr_utility.set_message_token('ALLOWED','25%');
1068
1069 ELSIF ( p_pa_request_rec.first_noa_code IN ('878')
1070 OR p_pa_request_rec.second_noa_code IN ('878') ) THEN
1071
1072 l_max_allowed_amount := 35*l_award_salary/100;
1073 hr_utility.set_message_token('ALLOWED','35%');
1074
1075 --bug#5482191
1076 ELSIF (( p_pa_request_rec.first_noa_code IN ('849')
1077 OR p_pa_request_rec.second_noa_code IN ('849')) and l_psi = '00' ) THEN
1078
1079 l_max_allowed_amount := 35*l_award_salary/100;
1080 hr_utility.set_message_token('ALLOWED','35%');
1081
1082
1083
1084 ELSIF ( p_pa_request_rec.first_noa_code IN ('825','842','843','848')
1085 OR p_pa_request_rec.second_noa_code IN ('825','842','843','848') ) THEN
1086
1087 l_max_allowed_amount := 25000;
1088 hr_utility.set_message(8301,'GHR_38905_AWARD_AMT_TOO_BIG6');
1089 hr_utility.set_message_token('ALLOWED','$25000');
1090
1091 END IF;
1092
1093
1094 IF ( p_pa_request_rec.first_noa_code='816'
1095 OR p_pa_request_rec.second_noa_code='816' ) THEN
1096
1097 IF p_pa_request_rec.from_pay_plan = 'EE' THEN
1098 IF (50*l_award_salary/100) > 50000 THEN
1099 l_max_allowed_amount := 50000;
1100 ELSE
1101 l_max_allowed_amount := 50*l_award_salary/100;
1102 END IF;
1103 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1104 hr_utility.set_message(8301, 'GHR_38898_AWARD_AMT_TOO_BIG3');
1105 hr_utility.raise_error;
1106 END IF;
1107 --3818297 Added NVL
1108 ELSIF ( ghr_pay_calc.LEO_position( p_prd => l_temp
1109 ,p_position_id => NVL(p_pa_request_rec.to_position_id,p_pa_request_rec.from_position_id)
1110 ,p_retained_user_table_id => l_temp
1111 ,p_duty_station_id => l_temp
1112 ,p_effective_date => p_pa_request_rec.effective_date
1113 )
1114 ) THEN
1115 l_max_allowed_amount := 25*l_award_salary/100;
1116 IF l_max_allowed_amount < 15000 THEN
1117 l_max_allowed_amount := 15000;
1118 END IF;
1119 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1120 hr_utility.set_message(8301, 'GHR_38896_AWARD_AMT_TOO_BIG2');
1121 hr_utility.raise_error;
1122 END IF;
1123 --3818297 Added NVL
1124 ELSIF (NOT ghr_pay_calc.LEO_position( p_prd => l_temp
1125 ,p_position_id => NVL(p_pa_request_rec.to_position_id,p_pa_request_rec.from_position_id)
1126 ,p_retained_user_table_id => l_temp
1127 ,p_duty_station_id => l_temp
1128 ,p_effective_date => p_pa_request_rec.effective_date
1129 ) )THEN
1130
1131 l_max_allowed_amount := 25*l_award_salary/100;
1132 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1133 hr_utility.set_message(8301, 'GHR_AWARD_AMT_TOO_BIG');
1134 hr_utility.raise_error;
1135 END IF;
1136 END IF;
1137
1138 ELSIF ( p_pa_request_rec.first_noa_code='815'
1139 OR p_pa_request_rec.second_noa_code='815' ) THEN
1140
1141 IF p_pa_request_rec.from_pay_plan = 'EE' THEN
1142
1143 IF (50*l_award_salary/100) > 50000 THEN
1144 l_max_allowed_amount := 50000;
1145 ELSE
1146 l_max_allowed_amount := 50*l_award_salary/100;
1147 END IF;
1148 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1149 hr_utility.set_message(8301, 'GHR_38898_AWARD_AMT_TOO_BIG3');
1150 hr_utility.raise_error;
1151 END IF;
1152 ELSE
1153 l_max_allowed_amount := 25*l_award_salary/100;
1154 hr_utility.set_message_token('ALLOWED','25%');
1155
1156 END IF;
1157 END IF;
1158
1159 -- Raise an Error if Award Amount is Greater than the Maximum Allowed Amount.
1160 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1161 --Name is already set.
1162 hr_utility.raise_error;
1163 ELSE
1164
1165 --If there is no error then clear the message stack.
1166 hr_utility.clear_message;
1167
1168 END IF;
1169
1170 --Check for Minimum Amount.
1171
1172 --Use the same Message Name for All.
1173 hr_utility.set_message(8301, 'GHR_38903_AWARD_AMT_TOO_LESS');
1174
1175 --Getting the Minimum Allowed Amount.
1176 IF ( p_pa_request_rec.first_noa_code='879'
1177 OR p_pa_request_rec.second_noa_code='879' ) THEN
1178
1179 l_min_allowed_amount := 5*l_award_salary/100;
1180 hr_utility.set_message_token('ALLOWED','5%');
1181 END IF;
1182
1183 --Raise an Error if Award Amount is Less than the Minimum Allowed Amount.
1184 IF p_pa_request_rec.award_amount < trunc(nvl(l_min_allowed_amount,0)) THEN
1185 hr_utility.raise_error;
1186 ELSE
1187 -- If there is no error then clear the message stack.
1188 hr_utility.clear_message;
1189 END IF;
1190
1191 END IF; -- End if of p_pa_request_rec.noa_family_code = 'AWARD'
1192 --Pradeep end of Bug 3934195
1193 --
1194 -- Wherever part-time indicator is enterable,
1195 -- if the Work Schedule is either B, F, G, I, or J,
1196 -- then the Part-Time Indicator must be null.
1197 --
1198 if p_pa_request_rec.noa_family_code in ('APP', 'CHG_HOURS', 'CHG_WORK_SCHED', 'CONV_APP'
1199 ,'REASSIGNMENT', 'RETURN_TO_DUTY') and
1200 p_pa_request_rec.work_schedule in ('B','F','G','I','J') and
1201 p_asg_non_sf52.parttime_indicator is not null then
1202 hr_utility.set_message(8301,'GHR_38621_PART_TIME_IND_NR');
1203 hr_utility.raise_error;
1204 end if;
1205 --
1206 -- START fix for 3563491 Madhuri
1207 --
1208 IF (p_pa_request_rec.noa_family_code = 'EXT_NTE') THEN
1209 ghr_history_fetch.fetch_asgei (
1210 p_assignment_id =>p_pa_request_rec.employee_assignment_id ,
1211 p_information_type => 'GHR_US_ASG_NTE_DATES' ,
1212 p_date_effective => nvl(p_pa_request_rec.effective_date,trunc(sysdate)) ,
1213 p_asg_ei_data => l_asg_ei_data
1214 );
1215 -- LWOP NTE
1216 IF p_pa_request_rec.FIRST_NOA_CODE ='773' THEN
1217 IF (l_asg_ei_data.aei_information5 is not null and l_asg_ei_data.aei_information6 is not null) Then
1218 l_nte_date_flg := TRUE;
1219 END IF;
1220 -- Suspension NTE
1221 IF (l_asg_ei_data.aei_information7 is not null and l_asg_ei_data.aei_information8 is not null) Then
1222 l_nte_date_flg := TRUE;
1223 END IF;
1224 -- END IF;
1225 --Furlough NTE
1226 ELSIF p_pa_request_rec.FIRST_NOA_CODE ='772' THEN
1227 IF (l_asg_ei_data.aei_information9 is not null and l_asg_ei_data.aei_information10 is not null) Then
1228 l_nte_date_flg := TRUE;
1229 END IF;
1230 --
1231 -- CONVERSION TO APPT, POSITION CHANGE NTE etc..
1232 --
1233 ELSIF p_pa_request_rec.FIRST_NOA_CODE in ('750','760','761','762','765','769','770') THEN
1234 IF (l_asg_ei_data.AEI_INFORMATION4 is not null) THEN
1235 l_nte_date_flg := TRUE;
1236 END IF;
1237 ELSE
1238 -- LWP NTE
1239 IF (l_asg_ei_data.aei_information11 is not null and l_asg_ei_data.aei_information12 is not null) Then
1240 l_nte_date_flg := TRUE;
1241 END IF;
1242 -- Sabbatical NTE
1243 IF (l_asg_ei_data.aei_information13 is not null and l_asg_ei_data.aei_information14 is not null) Then
1244 l_nte_date_flg := TRUE;
1245 END IF;
1246 END IF;
1247
1248 IF NOT l_nte_date_flg THEN
1249 FOR cur_nte_check_rec in cur_nte_check (p_pa_request_rec.employee_assignment_id,
1250 nvl(p_pa_request_rec.effective_date,trunc(sysdate)) )
1251 LOOP
1252 l_asg_status := cur_nte_check_rec.user_status;
1253 END LOOP;
1254 hr_utility.set_message(8301,'GHR_38920_NO_NTE_DATE');
1255 hr_utility.set_message_token('ASG_STATUS',l_asg_status);
1256 hr_utility.raise_error;
1257 END IF;
1258
1259 END IF;
1260 -- END of fix for 3563491
1261 --
1262
1263 -- New Termination of RG processing
1264 -- For 866 actions
1265 -- The To Position cannot be changed for this nature of action. Please
1266 -- process a separate action to change the To Position.
1267 IF p_pa_request_rec.first_noa_code = '866'
1268 AND p_pa_request_rec.from_position_id <> p_pa_request_rec.to_position_id
1269 THEN
1270 hr_utility.set_message(8301,'GHR_38693_NO_UPDATE_TO_POS');
1271 hr_utility.raise_error;
1272 END IF;
1273 --
1274 -- Added the following by skutteti on 08-Nov-99 for bug #983824
1275 --
1276 -- For change in work schedule and change in hours,
1277 -- if the work schedule is P,Q,S or T then the Parttime indicator must not be null
1278 --
1279 if p_pa_request_rec.noa_family_code in ('CHG_HOURS', 'CHG_WORK_SCHED') and
1280 p_pa_request_rec.work_schedule in ('P','Q','S','T') and
1281 p_asg_non_sf52.parttime_indicator is null then
1282 hr_utility.set_message(8301,'GHR_PART_TIME_IND_IS_NULL');
1283 hr_utility.raise_error;
1284 end if;
1285 -- Student Loan Repayment Code Changes start
1286 -- added for Student Loan Repayment Changes - 3494728 bug
1287 IF ( p_pa_request_rec.first_noa_code = '817' or p_pa_request_rec.second_noa_code = '817') THEN
1288
1289 --3562069
1290 ghr_history_fetch.fetch_peopleei
1291 (p_person_id => p_pa_request_rec.person_id,
1292 p_information_type => 'GHR_US_PER_GROUP1',
1293 p_date_effective => nvl(p_pa_request_rec.effective_date,trunc(sysdate)),
1294 p_per_ei_data => l_per_ei_data
1295 );
1296
1297
1298 l_appt_type := l_per_ei_data.pei_information3;
1299
1300 hr_utility.set_location('The Appointment Type is: '||l_appt_type,12345);
1301
1302 IF l_appt_type in ('34','44') THEN
1303 hr_utility.set_message(8301,'GHR_38878_APPT_TYPE_SCH_C');
1304 hr_utility.raise_error;
1305 END IF;
1306
1307 FOR cur_repay_sch_rec in cur_repay_sch
1308 LOOP
1309 l_repay_sch := cur_repay_sch_rec.repay_sch;
1310 l_review_date := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(cur_repay_sch_rec.review_date));
1311 END LOOP;
1312
1313 IF ( p_pa_request_rec.award_amount > 10000 ) then
1314 hr_utility.set_message(8301,'GHR_38862_AMT_EXCEEDS_LIMIT');
1315 hr_utility.raise_error;
1316 END IF;
1317
1318 IF l_repay_sch IS NULL THEN
1319 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
1320 hr_utility.set_message_token('REQUIRED_LIST','Repayment Schedule' );
1321 hr_utility.raise_error;
1322 END IF;
1323
1324 IF l_review_date is not null THEN
1325 IF (l_review_date < p_pa_request_rec.effective_date) THEN
1326 hr_utility.set_message(8301,'GHR_38863_REVIEW_DATE_LESS');
1327 hr_utility.raise_error;
1328 END IF;
1329 END IF;
1330
1331 IF l_repay_sch = 'L' THEN
1332 ghr_api.retrieve_element_entry_value (p_element_name => 'Student Loan Repayment'
1333 ,p_input_value_name => 'Amount'
1334 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1335 ,p_effective_date => p_pa_request_rec.effective_date
1336 ,p_value => l_slr_recur_amount
1337 ,p_multiple_error_flag => l_multiple_error_flag);
1338 if l_slr_recur_amount is not null then
1339 hr_utility.set_message(8301,'GHR_38864_ERR_CHG_REPAYMNT');
1340 hr_utility.raise_error;
1341 end if;
1342 else
1343 ghr_api.retrieve_element_entry_value (p_element_name => 'Student Loan Repayment LumpSum'
1344 ,p_input_value_name => 'Amount'
1345 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1346 ,p_effective_date => p_pa_request_rec.effective_date
1347 ,p_value => l_slr_lumpsum
1348 ,p_multiple_error_flag => l_multiple_error_flag);
1349
1350 l_ele_name := 'Student Loan Repayment LumpSum';
1351
1352 FOR cur_ele_end_date_rec IN cur_studloan_ele_end_date(l_ele_name,
1353 p_pa_request_rec.employee_assignment_id)
1354 LOOP
1355 l_ele_end_date := cur_ele_end_date_rec.effective_end_date;
1356 END LOOP;
1357
1358 if (l_slr_lumpsum is not null and l_session.noa_id_correct is NOT NULL) then
1359 hr_utility.set_message(8301,'GHR_38864_ERR_CHG_REPAYMNT');
1360 hr_utility.raise_error;
1361 elsif (p_pa_request_rec.effective_date <= l_ele_end_date) then
1362 hr_utility.set_message(8301,'GHR_38867_ERR_ELE_OVERLAPS');
1363 hr_utility.set_message_token('EFF_DATE',l_ele_end_date);
1364 hr_utility.raise_error;
1365 end if;
1366
1367 end if;
1368 end if;
1369 -- Student Loan Repayment Code Changes end here
1370 --- Start Bug 1551311
1371 --- check for other pay null for first time 810,818,819 actions
1372
1373 IF p_pa_request_rec.first_noa_code = '810'
1374 and l_session.noa_id_correct is NULL THEN
1375 ghr_api.retrieve_element_entry_value
1376 (p_element_name => 'Retention Allowance',
1377 p_input_value_name => 'Amount',
1378 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1379 p_effective_date => p_pa_request_rec.effective_date,
1380 p_value => l_reta_amount,
1381 p_multiple_error_flag => l_multiple_error_flag
1382 );
1383 ghr_api.retrieve_element_entry_value
1384 (p_element_name => 'Supervisory Differential',
1385 p_input_value_name => 'Amount',
1386 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1387 p_effective_date => p_pa_request_rec.effective_date,
1388 p_value => l_supv_amount,
1389 p_multiple_error_flag => l_multiple_error_flag
1390 );
1391 ghr_api.retrieve_element_entry_value
1392 (p_element_name => 'Staffing Differential',
1393 p_input_value_name => 'Amount',
1394 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1395 p_effective_date => p_pa_request_rec.effective_date,
1396 p_value => l_stad_amount,
1397 p_multiple_error_flag => l_multiple_error_flag
1398 );
1399 hr_utility.set_location('Ret Amount '||l_reta_amount,4);
1400 hr_utility.set_location('Supv Amount '||l_supv_amount,5);
1401 hr_utility.set_location('Stad Amount '||l_stad_amount,6);
1402 /* Pradeep commented this for 3306515.
1403
1404 IF (l_reta_amount is NULL and
1405 l_supv_amount is NULL and
1406 l_stad_amount is NULL )
1407 and
1408 (p_pa_request_rec.to_other_pay_amount is null or
1409 p_pa_request_rec.to_other_pay_amount = 0 ) THEN
1410 hr_utility.set_message(8301,'GHR_38589_NULL_OTHER_PAY');
1411 hr_utility.raise_error;
1412 END IF;
1413 */
1414 END IF;
1415
1416 --Pradeep.
1417 IF p_pa_request_rec.first_noa_code = '850'
1418 OR ( p_pa_request_rec.first_noa_code='002' and p_pa_request_rec.second_noa_code ='850') THEN
1419
1420 FOR cur_mddds_pay_rec in cur_mddds_pay
1421 LOOP
1422 l_mddds_special_pay_amount := cur_mddds_pay_rec.amount;
1423 l_mddds_specia_pay_nte_date := fnd_date.canonical_to_date(cur_mddds_pay_rec.nte_date);
1424 l_premium_pay_ind := cur_mddds_pay_rec.premium_pay_ind;
1425 END LOOP;
1426
1427 IF l_premium_pay_ind IS NULL THEN
1428
1429 ghr_api.retrieve_element_entry_value
1430 (p_element_name => 'Premium Pay',
1431 p_input_value_name => 'Premium Pay Ind',
1432 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1433 p_effective_date => p_pa_request_rec.effective_date,
1434 p_value => l_premium_pay_ind,
1435 p_multiple_error_flag => l_multiple_error_flag
1436 );
1437 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,4);
1438
1439 END IF;
1440
1441 /* IF l_premium_pay_ind IS NOT NULL and p_premium_pay.premium_pay_ind IS NOT NULL THEN
1442
1443 hr_utility.set_message(8301, 'GHR_38861_PREM_PAY_IND_ALREADY');
1444 hr_utility.raise_error;
1445
1446 END IF;
1447
1448 IF p_premium_pay.premium_pay_ind IS NOT NULL THEN
1449 l_premium_pay_ind := p_premium_pay.premium_pay_ind;
1450 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,5);
1451 END IF; --Premium Pay Ind is not coming from p_premium_pay.premium_pay_ind.
1452 */
1453 IF ( l_premium_pay_ind IS NULL ) THEN
1454 l_null_list := 'Premium Pay Indicator';
1455 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
1456 fnd_message.set_token('REQUIRED_LIST',l_null_list);
1457 hr_utility.raise_error;
1458 END IF;
1459
1460 --Pradeep. Bug# 3562063 New Business Rules for Title 38
1461 IF l_premium_pay_ind NOT IN ( 'K','X' ) THEN
1462 hr_utility.set_message(8301,'GHR_38871_850_PREM_PAY');
1463 hr_utility.raise_error;
1464 END IF;
1465
1466
1467 l_occ_code := p_pa_request_rec.from_occ_code;
1468 --Bug# 3562063
1469 --If Nature of Action is 850 and Occupation Series is 0610, then Premium Pay Indicator can only be K.
1470 If ( l_occ_code = '0610' )
1471 AND ( l_premium_pay_ind <> 'K' ) THEN
1472
1473 hr_utility.set_message(8301,'GHR_38875_850_OCC_SERIES1');
1474 hr_utility.raise_error;
1475
1476 End If;
1477
1478 --If Nature of Action is 850 and Occupation Series is 0660, then Premium Pay Indicator can not be K.
1479 If ( l_occ_code = '0660' )
1480 AND ( l_premium_pay_ind = 'K' ) THEN
1481
1482 hr_utility.set_message(8301,'GHR_38876_850_OCC_SERIES2');
1483 hr_utility.raise_error;
1484
1485 End If;
1486 ----Bug# 3562063
1487
1488 If l_premium_pay_ind='K' and l_occ_code NOT IN ('0602','0603','0610','0680','0681','0682','0683') THEN
1489
1490 hr_utility.set_message(8301,'GHR_38859_OCC_CD_PREM_PAY_IND');
1491 hr_utility.raise_error;
1492
1493 End If;
1494
1495 If l_premium_pay_ind='X' and l_occ_code NOT IN ('0180','0602','0620','0631','0633','0651','0660','0680') THEN
1496
1497 hr_utility.set_message(8301, 'GHR_38860_OCC_CD_PREM_PAY_IND');
1498 hr_utility.raise_error;
1499
1500 End If;
1501
1502 ghr_api.retrieve_element_entry_value
1503 (P_ELEMENT_NAME => 'Availability Pay',
1504 P_INPUT_VALUE_NAME => 'Amount',
1505 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1506 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1507 P_VALUE => l_ap_amount,
1508 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1509 );
1510 hr_utility.set_location('AP Amount '||l_ap_amount,7);
1511
1512 ghr_api.retrieve_element_entry_value
1513 (P_ELEMENT_NAME => 'AUO',
1514 P_INPUT_VALUE_NAME => 'Amount',
1515 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1516 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1517 P_VALUE => l_auo_amount,
1518 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1519 );
1520 hr_utility.set_location('AUO Amount '||l_auo_amount,7);
1521 -- Start of bug 4016362
1522 /********* Added Cursor BG_rec to get business group id
1523 to be passed to get_element_details procedure *************/
1524
1525 For BG_rec in Cur_BG(p_pa_request_rec.employee_assignment_id,p_pa_request_rec.effective_date)
1526 Loop
1527 ll_bg_id := BG_rec.bg;
1528 Exit;
1529 End Loop;
1530 -- End of Bug 4016362
1531
1532 ghr_per_sum.get_element_details
1533 (P_ELEMENT_NAME => 'Hazard Pay',
1534 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
1535 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1536 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1537 P_VALUE => l_hz_ind,
1538 P_EFFECTIVE_START_DATE => l_hz_eff_start_date,
1539 P_BUSINESS_GROUP_ID => ll_bg_id -- Bug 4016362
1540 );
1541
1542 ghr_per_sum.get_element_details
1543 (P_ELEMENT_NAME => 'EDP Pay',
1544 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
1545 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1546 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1547 P_VALUE => l_edp_ind,
1548 P_EFFECTIVE_START_DATE => l_edp_eff_start_date,
1549 P_BUSINESS_GROUP_ID => ll_bg_id -- Bug 4016362
1550 );
1551
1552
1553
1554
1555
1556 IF ( l_auo_amount is NOT NULL )
1557 or ( l_ap_amount IS NOT NULL )
1558 or ( l_edp_eff_start_date IS NOT NULL )
1559 or ( l_hz_eff_start_date IS NOT NULL ) THEN
1560 hr_utility.set_message(8301,'GHR_38858_PREM_PAY_OTH_PAY');
1561 hr_utility.raise_error;
1562 END IF;
1563
1564 --Check whether the elements AUO, AP, Hazard and EDP pay are existing in Future.
1565
1566 get_element_details_future(p_element_name => 'AUO'
1567 ,p_input_value_name => 'Amount'
1568 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1569 ,p_effective_date => p_pa_request_rec.effective_date
1570 ,p_value => l_auo_amount
1571 ,p_effective_start_date => l_eff_start_date);
1572
1573 hr_utility.set_location('AUO Amount '||l_auo_amount,8);
1574 get_element_details_future(p_element_name => 'Availability Pay'
1575 ,p_input_value_name => 'Amount'
1576 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1577 ,p_effective_date => p_pa_request_rec.effective_date
1578 ,p_value => l_ap_amount
1579 ,p_effective_start_date => l_eff_start_date);
1580
1581 hr_utility.set_location('AP Amount '||l_ap_amount,8);
1582 get_element_details_future(p_element_name => 'Hazard Pay'
1583 ,p_input_value_name => 'Premium Pay Ind'
1584 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1585 ,p_effective_date => p_pa_request_rec.effective_date
1586 ,p_value => l_hz_ind
1587 ,p_effective_start_date => l_hz_eff_start_date);
1588
1589 hr_utility.set_location('Hz Amount '||l_hz_ind,8);
1590 get_element_details_future(p_element_name => 'EDP Pay'
1591 ,p_input_value_name => 'Premium Pay Ind'
1592 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1593 ,p_effective_date => p_pa_request_rec.effective_date
1594 ,p_value => l_edp_ind
1595 ,p_effective_start_date => l_edp_eff_start_date);
1596 hr_utility.set_location('EDP Pay amount'||l_edp_ind,8);
1597
1598 IF ( l_auo_amount is NOT NULL )
1599 or ( l_ap_amount IS NOT NULL )
1600 or ( l_hz_eff_start_date IS NOT NULL )
1601 or ( l_edp_eff_start_date IS NOT NULL ) THEN
1602 hr_utility.set_message(8301,'GHR_38865_PREM_PAY_OTH_PAY_FUT');
1603 hr_utility.raise_error;
1604 END IF;
1605
1606
1607 --To check whether nre date and at least one special pay amount is entered or not
1608
1609 l_null_list := null;
1610
1611 If l_mddds_special_pay_amount is NULL or l_mddds_special_pay_amount = 0 THEN
1612
1613 ghr_api.retrieve_element_entry_value
1614 (p_element_name => 'MDDDS Special Pay',
1615 p_input_value_name => 'Amount',
1616 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1617 p_effective_date => p_pa_request_rec.effective_date,
1618 p_value => l_mddds_pay_amount_old,
1619 p_multiple_error_flag => l_multiple_error_flag
1620 );
1621 hr_utility.set_location('Amount l_mddds_pay_amount_old '|| l_mddds_pay_amount_old,5);
1622 IF l_mddds_pay_amount_old IS NULL THEN
1623 l_null_list := 'MD/DDS Special Pay Amount';
1624 END IF;
1625
1626 END IF;
1627
1628 If l_mddds_specia_pay_nte_date is NULL THEN
1629
1630 ghr_api.retrieve_element_entry_value
1631 (p_element_name => 'MDDDS Special Pay',
1632 p_input_value_name => 'MDDDS Special Pay NTE Date',
1633 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1634 p_effective_date => p_pa_request_rec.effective_date,
1635 p_value => l_mddds_pay_nte_date_old,
1636 p_multiple_error_flag => l_multiple_error_flag
1637 );
1638 hr_utility.set_location('MDDDS Special Pay NTE Date old '|| l_mddds_pay_nte_date_old,6);
1639
1640 IF l_mddds_pay_nte_date_old IS NULL THEN
1641 l_null_list := l_null_list||l_new_line||'MD/DDS Special Pay NTE Date';
1642 END IF;
1643
1644 END IF;
1645
1646 IF l_null_list IS NOT NULL THEN
1647
1648 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
1649 fnd_message.set_token('REQUIRED_LIST',l_null_list);
1650 hr_utility.raise_error;
1651
1652 End If;
1653
1654 IF l_mddds_specia_pay_nte_date IS NOT NULL
1655 and l_mddds_specia_pay_nte_date
1656 < p_pa_request_rec.effective_date THEN
1657 hr_utility.set_message(8301,'GHR_38853_NTE_DATE_<_EFF_DATE');
1658 hr_utility.raise_error;
1659 END IF;
1660
1661 END IF; -- End of If .. 850.
1662
1663 --Pradeep. New Business Rules for Title 38
1664 --Bug# 3562063
1665 IF p_pa_request_rec.first_noa_code = '855'
1666 OR ( p_pa_request_rec.first_noa_code='002' and p_pa_request_rec.second_noa_code ='855') THEN
1667
1668 FOR cur_premium_pay_ind_rec IN cur_premium_pay_ind
1669 LOOP
1670 l_premium_pay_ind := cur_premium_pay_ind_rec.premium_pay_ind;
1671 END LOOP;
1672
1673 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,3);
1674 IF l_premium_pay_ind IS NULL THEN
1675 ghr_api.retrieve_element_entry_value
1676 (p_element_name => 'Premium Pay',
1677 p_input_value_name => 'Premium Pay Ind',
1678 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1679 p_effective_date => p_pa_request_rec.effective_date,
1680 p_value => l_premium_pay_ind,
1681 p_multiple_error_flag => l_multiple_error_flag
1682 );
1683
1684 END IF;
1685
1686 IF ( l_premium_pay_ind ) IS NULL
1687 OR ( l_premium_pay_ind <> 'K' ) THEN
1688 hr_utility.set_message(8301,'GHR_38872_855_PREM_PAY');
1689 hr_utility.raise_error;
1690 END IF;
1691
1692 l_occ_code := p_pa_request_rec.from_occ_code;
1693
1694 If ( l_occ_code <> '0610' ) THEN
1695
1696 hr_utility.set_message(8301,'GHR_38873_855_OCC_SERIES');
1697 hr_utility.raise_error;
1698
1699 End If;
1700 --Bug# 3562063
1701
1702 END IF; --End of If ... 855
1703
1704 --Pradeep.
1705 -- Bug 3528461
1706 --Not only for 800 but also for any other noac, it should check the validation between Premium Pay and OCC Code.
1707 -- IF p_pa_request_rec.first_noa_code = '800' THEN
1708 --Bug#3579579
1709 FOR cur_premium_pay_rec IN cur_premium_pay
1710 LOOP
1711 l_premium_pay_ind := cur_premium_pay_rec.premium_pay_ind;
1712 END LOOP;
1713 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,3);
1714
1715 IF l_premium_pay_ind IS NULL THEN
1716 --Bug#3579579
1717 ghr_api.retrieve_element_entry_value
1718 (p_element_name => 'Premium Pay',
1719 p_input_value_name => 'Premium Pay Ind',
1720 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1721 p_effective_date => p_pa_request_rec.effective_date,
1722 p_value => l_premium_pay_ind,
1723 p_multiple_error_flag => l_multiple_error_flag
1724 );
1725 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,4);
1726 END IF;
1727
1728 l_occ_code := p_pa_request_rec.to_occ_code;
1729 hr_utility.set_location('Occ Code '|| l_occ_code,5);
1730 IF l_occ_code IS NULL THEN
1731 l_occ_code := p_pa_request_rec.from_occ_code;
1732 END IF;
1733
1734 If l_premium_pay_ind='K' and l_occ_code NOT IN ('0602','0603','0610','0680','0681','0682','0683') THEN
1735
1736 hr_utility.set_message(8301,'GHR_38859_OCC_CD_PREM_PAY_IND');
1737 hr_utility.raise_error;
1738
1739 End If;
1740
1741 If l_premium_pay_ind='X' and l_occ_code NOT IN ('0180','0602','0620','0631','0633','0651','0660','0680') THEN
1742
1743 hr_utility.set_message(8301, 'GHR_38860_OCC_CD_PREM_PAY_IND');
1744 hr_utility.raise_error;
1745
1746 End If;
1747
1748 get_element_details_future(p_element_name => 'Premium Pay'
1749 ,p_input_value_name => 'Premium Pay Ind'
1750 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1751 ,p_effective_date => p_pa_request_rec.effective_date
1752 ,p_value => l_premium_pay_ind
1753 ,p_effective_start_date => l_eff_start_date);
1754
1755 hr_utility.set_location('Premium Pay Ind future'|| l_premium_pay_ind,4);
1756
1757 If l_premium_pay_ind='K' and l_occ_code NOT IN ('0602','0603','0610','0680','0681','0682','0683') THEN
1758
1759 hr_utility.set_message(8301,'GHR_38859_OCC_CD_PREM_PAY_IND');
1760 hr_utility.raise_error;
1761
1762 End If;
1763
1764 If l_premium_pay_ind='X' and l_occ_code NOT IN ('0180','0602','0620','0631','0633','0651','0660','0680') THEN
1765
1766 hr_utility.set_message(8301, 'GHR_38860_OCC_CD_PREM_PAY_IND');
1767 hr_utility.raise_error;
1768
1769 End If;
1770
1771
1772 -- END IF;
1773
1774 IF p_pa_request_rec.first_noa_code = '819'
1775 and l_session.noa_id_correct is NULL THEN
1776 ghr_api.retrieve_element_entry_value
1777 (P_ELEMENT_NAME => 'Availability Pay',
1778 P_INPUT_VALUE_NAME => 'Amount',
1779 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1780 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1781 P_VALUE => l_ap_amount,
1782 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1783 );
1784 hr_utility.set_location('AP Amount '||l_ap_amount,7);
1785 IF l_ap_amount is NULL and
1786 (p_pa_request_rec.to_other_pay_amount is null or
1787 p_pa_request_rec.to_other_pay_amount = 0 ) THEN
1788 hr_utility.set_message(8301,'GHR_38589_NULL_OTHER_PAY');
1789 hr_utility.raise_error;
1790 END IF;
1791
1792 --Pradeep
1793 --Title 38 Employess should not get Avaialability Pay.
1794 ghr_api.retrieve_element_entry_value
1795 (p_element_name => 'Premium Pay',
1796 p_input_value_name => 'Premium Pay Ind',
1797 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1798 p_effective_date => p_pa_request_rec.effective_date,
1799 p_value => l_premium_pay_ind,
1800 p_multiple_error_flag => l_multiple_error_flag
1801 );
1802 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,7);
1803
1804 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
1805
1806 hr_utility.set_message(8301,'GHR_38857_OTH_PAY_PREM_PAY');
1807 hr_utility.raise_error;
1808
1809 END IF;
1810
1811 --To check if premium pay element is existing in the future
1812 get_element_details_future(p_element_name => 'Premium Pay'
1813 ,p_input_value_name => 'Premium Pay Ind'
1814 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1815 ,p_effective_date => p_pa_request_rec.effective_date
1816 ,p_value => l_premium_pay_ind
1817 ,p_effective_start_date => l_eff_start_date);
1818
1819 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,8);
1820
1821 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
1822
1823 hr_utility.set_message(8301,'GHR_38866_OTH_PAY_PREM_PAY_FUT');
1824 hr_utility.raise_error;
1825
1826 END IF;
1827
1828 END IF;
1829
1830 IF p_pa_request_rec.first_noa_code = '818'
1831 and l_session.noa_id_correct is NULL THEN
1832 ghr_api.retrieve_element_entry_value
1833 (P_ELEMENT_NAME => 'AUO',
1834 P_INPUT_VALUE_NAME => 'Amount',
1835 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1836 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1837 P_VALUE => l_auo_amount,
1838 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1839 );
1840 hr_utility.set_location('AUO Amount '||l_auo_amount,7);
1841 IF l_auo_amount is NULL and
1842 (p_pa_request_rec.to_other_pay_amount is null or
1843 p_pa_request_rec.to_other_pay_amount = 0 ) THEN
1844 hr_utility.set_message(8301,'GHR_38589_NULL_OTHER_PAY');
1845 hr_utility.raise_error;
1846 END IF;
1847
1848 --Pradeep
1849 --Title 38 Employess should not get AUO.
1850 ghr_api.retrieve_element_entry_value
1851 (p_element_name => 'Premium Pay',
1852 p_input_value_name => 'Premium Pay Ind',
1853 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1854 p_effective_date => p_pa_request_rec.effective_date,
1855 p_value => l_premium_pay_ind,
1856 p_multiple_error_flag => l_multiple_error_flag
1857 );
1858 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,7);
1859
1860 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
1861
1862 hr_utility.set_message(8301,'GHR_38857_OTH_PAY_PREM_PAY');
1863 hr_utility.raise_error;
1864
1865 END IF;
1866
1867 --To check if premium pay element is existing in the future
1868 get_element_details_future(p_element_name => 'Premium Pay'
1869 ,p_input_value_name => 'Premium Pay Ind'
1870 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1871 ,p_effective_date => p_pa_request_rec.effective_date
1872 ,p_value => l_premium_pay_ind
1873 ,p_effective_start_date => l_eff_start_date);
1874
1875 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,8);
1876
1877 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
1878
1879 hr_utility.set_message(8301,'GHR_38866_OTH_PAY_PREM_PAY_FUT');
1880 hr_utility.raise_error;
1881
1882 END IF;
1883
1884 END IF;
1885
1886 IF p_pa_request_rec.first_noa_code = '855'
1887 OR ( p_pa_request_rec.first_noa_code='002' and p_pa_request_rec.second_noa_code ='855') THEN
1888
1889 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F','U','V') THEN
1890 begin
1891 hr_utility.set_location('Check 855 Retained Grade ...get open pay range ' ,20);
1892
1893 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_pa_request_rec.person_id
1894 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)));
1895
1896 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F') THEN
1897 if l_retained_grade.temp_step is not null then
1898 l_to_step_or_rate := l_retained_grade.temp_step;
1899 else
1900 l_to_step_or_rate := l_retained_grade.step_or_rate;
1901 end if;
1902 ELSE
1903 l_to_step_or_rate := l_retained_grade.step_or_rate;
1904 END IF;
1905
1906 hr_utility.set_location(' check 855 Retained to step ' || l_to_step_or_rate,22);
1907
1908 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_pa_request_rec.person_id
1909 ,(p_pa_request_rec.effective_date - 1));
1910
1911 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F') THEN
1912 if l_retained_grade.temp_step is not null then
1913 l_from_step_or_rate := l_retained_grade.temp_step;
1914 else
1915 l_from_step_or_rate := l_retained_grade.step_or_rate;
1916 end if;
1917 ELSE
1918 l_from_step_or_rate := l_retained_grade.step_or_rate;
1919 END IF;
1920
1921 hr_utility.set_location(' check 855 Retained from step ' || l_from_step_or_rate,24);
1922
1923 exception
1924 when others then
1925 hr_utility.set_location('Retained Exception raised ' || sqlerrm(sqlcode),25);
1926 hr_utility.set_message(8301,'GHR_38255_MISSING_RETAINED_DET');
1927 hr_utility.raise_error;
1928 end;
1929
1930 IF ABS(l_to_step_or_rate - l_from_step_or_rate) > 2 THEN
1931 hr_utility.set_message(8301,'GHR_38852_TO_STEP_OR_RATE');
1932 hr_utility.raise_error;
1933 END IF;
1934
1935 END IF;
1936 END IF;
1937
1938
1939 --- End Bug 1551311
1940 --
1941 --
1942 -- check whether more than one other pay is changed for NOA 810
1943 --
1944 if p_pa_request_rec.first_noa_code = '810' then
1945 l_for_810_count := 0;
1946 ghr_api.retrieve_element_entry_value
1947 (p_element_name => 'Retention Allowance',
1948 p_input_value_name => 'Amount',
1949 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1950 p_effective_date => p_pa_request_rec.effective_date,
1951 p_value => l_amount,
1952 p_multiple_error_flag => l_multiple_error_flag
1953 );
1954 if NVL(p_pa_request_rec.to_retention_allowance,0) <> NVL(l_amount,0) then
1955 l_for_810_count := l_for_810_count + 1;
1956 end if;
1957 ghr_api.retrieve_element_entry_value
1958 (p_element_name => 'Supervisory Differential',
1959 p_input_value_name => 'Amount',
1960 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1961 p_effective_date => p_pa_request_rec.effective_date,
1962 p_value => l_amount,
1963 p_multiple_error_flag => l_multiple_error_flag
1964 );
1965 if NVL(p_pa_request_rec.to_supervisory_differential,0) <> NVL(l_amount,0) then
1966 l_for_810_count := l_for_810_count + 1;
1967 end if;
1968 ghr_api.retrieve_element_entry_value
1969 (p_element_name => 'Staffing Differential',
1970 p_input_value_name => 'Amount',
1971 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1972 p_effective_date => p_pa_request_rec.effective_date,
1973 p_value => l_amount,
1974 p_multiple_error_flag => l_multiple_error_flag
1975 );
1976 if NVL(p_pa_request_rec.to_staffing_differential,0) <> NVL(l_amount,0) then
1977 l_for_810_count := l_for_810_count + 1;
1978 end if;
1979 --
1980 if l_for_810_count > 1 then
1981 hr_utility.set_message(8301,'GHR_ONE_OP_UPDATE_ONLY');
1982 hr_utility.raise_error;
1983 end if;
1984 end if;
1985 hr_utility.set_location('GHRVALID-Before LAC Validation',3);
1986 -- Bug # 941255 --Venkat-- Check that LACs are Valid when Update HR
1987 -- in progress and give a Error message if NOAC/LAC combination is invalid
1988 --
1989 -- Check if first_action_la_code1 is valid
1990 --
1991 l_exists := false;
1992 If p_pa_request_rec.first_action_la_code1 is not null then
1993 for la_code in c_first_la_code1 loop
1994 l_exists := true;
1995 exit;
1996 end loop;
1997 --
1998 if not l_exists then
1999 hr_utility.set_message(8301, 'GHR_38105_INV_FIRST_LA_CODE1');
2000 hr_utility.raise_error;
2001 end if;
2002 end if;
2003
2004 l_exists := false;
2005 -- Check if first_action_la_code2 is valid
2006 --
2007 If p_pa_request_rec.first_action_la_code2 is not null then
2008 for la_code in c_first_la_code2 loop
2009 l_exists := true;
2010 exit;
2011 end loop;
2012 --
2013 if not l_exists then
2014 hr_utility.set_message(8301, 'GHR_38106_INV_FIRST_LA_CODE2');
2015 hr_utility.raise_error;
2016 end if;
2017 end if;
2018 l_exists := false;
2019 --
2020 -- Check if second_action_la_code1 is valid
2021 --
2022 If p_pa_request_rec.second_action_la_code1 is not null then
2023 for la_code in c_second_la_code1 loop
2024 l_exists := true;
2025 exit;
2026 end loop;
2027 --
2028 if not l_exists then
2029 hr_utility.set_message(8301, 'GHR_38107_INV_SECOND_LA_CODE1');
2030 hr_utility.raise_error;
2031 end if;
2032 end if;
2033 l_exists := false;
2034 --
2035 -- Check if second_action_la_code2 is valid
2036 --
2037 If p_pa_request_rec.second_action_la_code2 is not null then
2038 for la_code in c_second_la_code2 loop
2039 l_exists := true;
2040 exit;
2041 end loop;
2042 --
2043 if not l_exists then
2044 hr_utility.set_message(8301, 'GHR_38108_INV_SECOND_LA_CODE2');
2045 hr_utility.raise_error;
2046 end if;
2047 end if;
2048 hr_utility.set_location('GHRVALID-After LAC Validation',4);
2049 --
2050 --
2051 -- Validate if the open pay range basic pay is entered by user.
2052 --
2053 --
2054 IF p_pa_request_rec.noa_family_code not in ('AWARD'
2055 ,'GHR_INCENTIVE'
2056 ,'NON_PAY_DUTY_STATUS'
2057 ,'POS_ABOLISH'
2058 ,'POS_ESTABLISH'
2059 ,'POS_REVIEW'
2060 ,'RECRUIT_FILL'
2061 ,'SEPARATION'
2062 ,'GHR_STUDENT_LOAN') THEN
2063 IF p_pa_request_rec.to_position_id is not null THEN
2064 IF ghr_pay_calc.get_open_pay_range
2065 (p_pa_request_rec.to_position_id
2066 ,p_pa_request_rec.person_id
2067 ,p_pa_request_rec.pay_rate_determinant
2068 ,p_pa_request_rec.pa_request_id
2069 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) ) THEN
2070
2071 hr_utility.set_location('GHRVALID-Open Pay Range get table_id' ,5);
2072
2073
2074
2075 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F','U','V') THEN
2076 begin
2077 hr_utility.set_location('Retained Grade ...get open pay range ' ,20);
2078
2079 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_pa_request_rec.person_id
2080 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)));
2081 l_user_table_id := l_retained_grade.user_table_id;
2082 l_pay_plan := l_retained_grade.pay_plan;
2083 l_grade_or_level := l_retained_grade.grade_or_level;
2084 hr_utility.set_location(' Retained user table id ' || to_char(l_user_table_id),22);
2085 exception
2086 when others then
2087 hr_utility.set_location('Retained Exception raised ' || sqlerrm(sqlcode),25);
2088 hr_utility.set_message(8301,'GHR_38255_MISSING_RETAINED_DET');
2089 hr_utility.raise_error;
2090 end;
2091
2092 ELSE
2093
2094
2095 l_user_table_id := ghr_pay_calc.get_user_table_id (p_pa_request_rec.to_position_id
2096 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) );
2097 --Start of the Bug 3604377
2098 -- Retive the Grade info from the POI history table
2099 ghr_history_fetch.fetch_positionei(
2100 p_position_id => p_pa_request_rec.to_position_id,
2101 p_information_type => 'GHR_US_POS_VALID_GRADE',
2102 p_date_effective => NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)),
2103 p_pos_ei_data => l_pos_ei_grade_data);
2104 --Based on Grade id Get the Pay Plan.
2105 IF l_pos_ei_grade_data.poei_information3 IS NOT NULL THEN
2106 FOR cur_grd_rec IN cur_grd(l_pos_ei_grade_data.poei_information3) LOOP
2107 l_pay_plan := NVL(cur_grd_rec.pay_plan,l_pay_plan);
2108 l_grade_or_level := NVL(cur_grd_rec.grade_or_level,l_grade_or_level);
2109 END LOOP;
2110 END IF;
2111 --End of the Bug 3604377
2112 /*
2113 l_pay_plan := p_pa_request_rec.to_pay_plan;
2114 l_grade_or_level := p_pa_request_rec.to_grade_or_level;
2115 */
2116
2117 END IF;
2118
2119
2120
2121 --hr_utility.set_location('GHRVALID-l_user_table_id ' || to_char(l_user_table_id) ,5);
2122 --hr_utility.set_location('GHRVALID-l_pay_plan ' || to_char(l_pay_plan) ,5);
2123 --hr_utility.set_location('GHRVALID-l_grade_or_level ' || to_char(l_grade_or_level) ,5);
2124
2125 hr_utility.set_location('GHRVALID-l_user_table_id ' || to_char(l_user_table_id) ,5);
2126 hr_utility.set_location('GHRVALID-Open Pay Range get values' ,5);
2127
2128 ghr_pay_calc.get_open_pay_table_values
2129 (p_user_table_id => l_user_table_id
2130 ,p_pay_plan => l_pay_plan
2131 ,p_grade_or_level => l_grade_or_level
2132 ,p_effective_date => NVL(p_pa_request_rec.effective_date,TRUNC(sysdate))
2133 ,p_row_high => l_row_high
2134 ,p_row_low => l_row_low );
2135
2136
2137 hr_utility.set_location('GHRVALID-Open Pay Ranges found',5);
2138
2139 IF l_row_low is null and l_row_high is null then
2140 ---Raise Error
2141 hr_utility.set_message(8301, 'GHR_38715_OPEN_PAY_RANGE_NF');
2142 hr_utility.raise_error;
2143 END IF;
2144
2145 -- 5482191 Start
2146 --Bug 5658361 IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
2147 --Bug 5658361 p_pa_request_rec.first_noa_code in ('891','892','893','894','890','896','897') AND
2148 --Bug# 6073655 Brought out this code from the l_psi<>'00' condition
2149 IF p_pa_request_rec.first_noa_code = '897'
2150 AND p_pa_request_rec.to_basic_pay >= p_pa_request_rec.from_basic_pay THEN
2151 hr_utility.set_message(8301, 'GHR_38513_AFHR_VAL_CHK');
2152 hr_utility.raise_error;
2153 END IF;
2154 --Bug# 6073655
2155 IF l_psi <> '00' THEN
2156 IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('30/04/2006','DD/MM/YYYY') THEN --Bug 5658361
2157 IF p_pa_request_rec.pay_rate_determinant IN ('4','R') AND
2158 l_row_high >= p_pa_request_rec.to_basic_pay THEN
2159 hr_utility.set_message(8301, 'GHR_38506_AFHR_PRD_CHK');
2160 hr_utility.raise_error;
2161 ELSIF p_pa_request_rec.pay_rate_determinant = 'T' AND
2162 l_row_low <= p_pa_request_rec.to_basic_pay THEN
2163 hr_utility.set_message(8301, 'GHR_38508_AFHR_PRD_CHK');
2164 hr_utility.raise_error;
2165 ELSIF p_pa_request_rec.pay_rate_determinant = '0' AND
2166 (l_row_low > p_pa_request_rec.to_basic_pay OR l_row_high < p_pa_request_rec.to_basic_pay) THEN
2167 hr_utility.set_message(8301, 'GHR_38507_AFHR_PAY_CHK');
2168 hr_utility.raise_error;
2169 END IF;
2170 IF p_pa_request_rec.to_step_or_rate <> '00' THEN --Bug 5657572
2171 hr_utility.set_message(8301, 'GHR_38474_AFH_TO_STEP_OR_RATE');
2172 hr_utility.raise_error;
2173 END IF;
2174 END IF;
2175 ELSE
2176 --6489042 No validation is needed for PRD 2
2177 --Bug# 7557159, added PRD D, basic pay can enter more than the limit
2178 -- Pay cap is different for PRD D. Eg pay plan ES.
2179 IF p_pa_request_rec.pay_rate_determinant NOT IN ('2','D') then
2180 IF l_row_low <= p_pa_request_rec.to_basic_pay AND
2181 l_row_high >= p_pa_request_rec.to_basic_pay then
2182 null;
2183 ELSE
2184 ---Raise Error
2185 hr_utility.set_message(8301, 'GHR_38714_OPEN_PAY_RANGE_VAL');
2186 hr_utility.set_message_token('MIN',to_char(l_row_low));
2187 hr_utility.set_message_token('MAX',to_char(l_row_high));
2188 hr_utility.raise_error;
2189 END IF;
2190 END IF;
2191 END IF;
2192 -- 5482191 End
2193
2194 END IF;
2195
2196 -- Bug 5657572
2197 /**** As per GPPA update 46 req. for 890 any employee is fine
2198 IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
2199 p_pa_request_rec.first_noa_code = '890' THEN
2200 IF l_psi = '00' THEN
2201 hr_utility.set_message(8301, 'GHR_38462_AFHR_POS_SEL');
2202 hr_utility.raise_error;
2203 END IF;
2204 END IF;
2205 *******/
2206 END IF;
2207 END IF;
2208
2209 --Bug 5657733, 5662254
2210 --IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
2211 --Removed 894 NOAC for rating of record check as part of GPPA U46 Req.
2212 IF p_pa_request_rec.first_noa_code in ('891','892') AND l_psi <> '00' THEN
2213 IF p_pa_request_rec.first_noa_code = '891' AND
2214 nvl(p_perf_appraisal_type.rating_rec_level,'X') NOT IN ('3','4','5') THEN
2215 hr_utility.set_message(8301, 'GHR_38457_AFHR_NOA_CHK');
2216 hr_utility.raise_error;
2217 END IF;
2218 IF p_pa_request_rec.first_noa_code = '892' AND
2219 nvl(p_perf_appraisal_type.rating_rec_level,'X') IN ('1','2') THEN
2220 hr_utility.set_message(8301, 'GHR_38510_AFHR_NOA_CHK');
2221 hr_utility.raise_error;
2222 END IF;
2223 /****GPPA U46
2224 IF p_pa_request_rec.first_noa_code = '894' AND
2225 nvl(p_perf_appraisal_type.rating_rec_level,'X') = '1' THEN
2226 hr_utility.set_message(8301, 'GHR_38511_AFHR_NOA_CHK');
2227 hr_utility.raise_error;
2228 END IF;
2229 *****GPPA ****/
2230 END IF;
2231
2232 -- For 703 actions give error message if the Temporary Promotion Step value
2233 -- is null
2234 -- and Employee has a valid retained grade record
2235
2236 IF p_pa_request_rec.first_noa_code in ('703') THEN
2237 BEGIN
2238 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details
2239 (p_pa_request_rec.person_id
2240 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate))
2241 ,p_pa_request_rec.pa_request_id);
2242 EXCEPTION
2243 WHEN OTHERS THEN
2244 NULL;
2245 END;
2246 IF l_retained_grade.person_extra_info_id is not null and
2247 l_retained_grade.temp_step is null THEN
2248 hr_utility.set_message(8301,'GHR_38824_RG_TPS_REQUIRED');
2249 hr_utility.raise_error;
2250 END IF;
2251 END IF;
2252 --Bug 5527363
2253 IF p_pa_request_rec.noa_family_code = 'CONV_APP' AND p_within_grade_incr.p_date_wgi_due IS NOT NULL
2254 AND p_within_grade_incr.p_last_equi_incr IS NULL THEN
2255 hr_utility.set_message(8301,'GHR_37740_CONV_APP_NULL_DLEI');
2256 hr_utility.raise_error;
2257 END IF;
2258
2259 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
2260 IF p_pa_request_rec.first_noa_code IN ('817', '850', '480') OR
2261 (p_pa_request_rec.second_noa_code IN ('817', '850', '480') AND
2262 p_pa_request_rec.first_noa_code IN ('002')) THEN
2263 FOR l_cur_service_oblg_ei IN cur_service_oblg_ei LOOP
2264 l_serv_oblg_type := l_cur_service_oblg_ei.srvc_oblg_type;
2265 l_serv_oblg_start_date := l_cur_service_oblg_ei.srvc_oblg_st_date;
2266 l_serv_oblg_end_date := l_cur_service_oblg_ei.srvc_oblg_end_date;
2267 IF l_serv_oblg_type IS NOT NULL AND
2268 (l_serv_oblg_start_date IS NULL OR l_serv_oblg_end_date IS NULL) THEN
2269 hr_utility.set_message(8301,
2270 'GHR_38454_SRVC_OBLG_TYPE_CHK');
2271 hr_utility.raise_error;
2272 END IF;
2273
2274 IF l_serv_oblg_start_date IS NOT NULL AND
2275 (l_serv_oblg_end_date IS NULL OR l_serv_oblg_type IS NULL) THEN
2276 hr_utility.set_message(8301,
2277 'GHR_38455_SRVC_OBLG_ST_DT_CHK');
2278 hr_utility.raise_error;
2279 END IF;
2280 END LOOP;
2281 END IF;
2282 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
2283
2284 end Validate_CHECK;
2285
2286 PROCEDURE get_element_details_future (p_element_name IN VARCHAR2
2287 ,p_input_value_name IN VARCHAR2
2288 ,p_assignment_id IN NUMBER
2289 ,p_effective_date IN DATE
2290 ,p_value IN OUT NOCOPY VARCHAR2
2291 ,p_effective_start_date IN OUT NOCOPY DATE) IS
2292 --
2293 -- NOTE: The effective date we get is that of the individual input value not the effective
2294 -- date of the whole element as seen in the element screen.
2295 --
2296 CURSOR cur_ele(p_element_name IN VARCHAR2,
2297 p_bg_id IN NUMBER)
2298 IS
2299 SELECT eev.screen_entry_value
2300 ,eev.effective_start_date
2301 FROM pay_element_types_f elt
2302 ,pay_input_values_f ipv
2303 ,pay_element_entries_f ele
2304 ,pay_element_entry_values_f eev
2305 WHERE p_effective_date < eev.effective_start_date
2306 AND eev.effective_end_date IS NULL
2307 AND elt.element_type_id = ipv.element_type_id
2308 AND upper(elt.element_name)= upper(p_element_name)
2309 AND ipv.input_value_id = eev.input_value_id
2310 AND ele.assignment_id = p_assignment_id
2311 AND ele.element_entry_id+0 = eev.element_entry_id
2312 AND upper(ipv.name ) = upper(p_input_value_name)
2313 -- AND NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0)
2314 AND (elt.business_group_id is NULL or elt.business_group_id = p_bg_id);
2315 --
2316 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
2317 Select distinct business_group_id bg
2318 from per_assignments_f
2319 where assignment_id = p_assignment_id
2320 and p_eff_date between effective_start_date
2321 and effective_end_date;
2322 --
2323 ll_bg_id NUMBER;
2324 ll_pay_basis VARCHAR2(80);
2325 ll_effective_date DATE;
2326 l_new_element_name VARCHAR2(80);
2327 l_session ghr_history_api.g_session_var_type;
2328 --
2329 BEGIN
2330 --
2331 --
2332 -- Initialization
2333 -- Pick the business group id and also pay basis for later use
2334 ll_effective_date := p_effective_Date;
2335
2336 For BG_rec in Cur_BG(p_assignment_id,ll_effective_date)
2337 Loop
2338 ll_bg_id:=BG_rec.bg;
2339 End Loop;
2340
2341 ----
2342 ---- The New Changes after 08/22 patch
2343 ---- For all elements in HR User old function will fetch the same name.
2344 ---- because of is_script will be FALSE
2345 ----
2346 ---- For all elements (except BSR) in Payroll user old function.
2347 ---- for BSR a new function which will fetch from assignmnet id.
2348 ----
2349
2350 IF (p_element_name = 'Basic Salary Rate'
2351 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
2352 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- ', 1);
2353 l_new_element_name :=
2354 pqp_fedhr_uspay_int_utils.return_new_element_name(
2355 p_assignment_id => p_assignment_id,
2356 p_business_group_id => ll_bg_id,
2357 p_effective_date => ll_effective_date);
2358 ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
2359 or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN
2360 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- ', 1);
2361 l_new_element_name :=
2362 pqp_fedhr_uspay_int_utils.return_new_element_name(
2363 p_fedhr_element_name => p_element_name,
2364 p_business_group_id => ll_bg_id,
2365 p_effective_date => ll_effective_date,
2366 p_pay_basis => NULL);
2367
2368 END IF;
2369
2370 --
2371 --
2372 FOR cur_ele_rec IN cur_ele(l_new_element_name,ll_bg_id) LOOP
2373 p_value := cur_ele_rec.screen_entry_value;
2374 p_effective_start_date := cur_ele_rec.effective_start_date;
2375 END LOOP;
2376 --
2377 END get_element_details_future;
2378
2379 end GHR_Validate_CHECK;
[Home] [Help]