1 package body GHR_Validate_CHECK AS
2 /* $Header: ghrvalid.pkb 120.46.12020000.11 2013/02/15 09:06:23 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 --Begin Bug# 8653508
317 l_detail_type ghr_pa_request_extra_info.rei_information3%type;
318 l_unclassified_duty_ind ghr_pa_request_extra_info.rei_information4%type;
319 l_detail_position_id ghr_pa_request_extra_info.rei_information5%type;--bug# 11781477
320
321 CURSOR cur_detail_info IS
322 SELECT rei_information3 ,
323 rei_information4 ,
324 rei_information5 --bug# 11781477
325 FROM ghr_pa_request_extra_info
326 WHERE pa_request_id = p_pa_request_rec.pa_request_id
327 AND information_type = 'GHR_US_PAR_DET_INFO';
328
329 CURSOR cur_detail_exists(p_asg_id per_assignments_f.assignment_id%TYPE,
330 p_eff_date ghr_pa_requests.effective_date%TYPE) IS
331 SELECT user_status
332 FROM per_assignment_status_types pst, per_assignments_f paf
333 WHERE paf.assignment_status_type_id = pst.assignment_status_type_id
334 AND paf.assignment_type <> 'B'
335 AND paf.assignment_id = p_asg_id
336 AND user_status ='Detail NTE'
337 and paf.effective_end_date = to_date('31/12/4712','DD/MM/RRRR');
338
339 --End Bug# 8653508
340 --Begin Bug# 9899054
341 l_detail_nte_date ghr_pa_requests.first_noa_information1%type;
342 CURSOR c_detail_first_nte_date is
343 SELECT first_noa_information1
344 FROM ghr_pa_requests
345 WHERE pa_request_id = p_pa_request_rec.pa_request_id;
346
347 CURSOR c_detail_second_nte_date is
348 SELECT second_noa_information1
349 FROM ghr_pa_requests
350 WHERE pa_request_id = p_pa_request_rec.pa_request_id;
351 --end Bug# 9899054
352
353 l_asg_extra_info_rec per_assignment_extra_info%rowtype;--Bug# 11776559
354 --Begin Bug# 9898968
355 Cursor c_detail_chk(g_effective_date DATE) is
356 select assignment_extra_info_id
357 from per_assignment_extra_info
358 where assignment_id = p_pa_request_rec.employee_assignment_id
359 and information_type='GHR_US_ASG_DET_INFO'
360 and g_effective_date
361 between fnd_date.canonical_to_date(AEI_INFORMATION1)
362 and fnd_date.canonical_to_date(AEI_INFORMATION2);
363 --End Bug# 9898968
364 --Begin Bug# 11781477
365 CURSOR c_get_pos_avl_status (p_position_id number,g_effective_date DATE) IS
366 SELECT HR_GENERAL.DECODE_AVAILABILITY_STATUS(hap.availability_status_id) status
367 FROM hr_all_positions_f hap
368 WHERE g_effective_date BETWEEN hap.effective_start_date
369 AND hap.effective_end_date
370 AND hap.position_id=p_position_id;
371 --End Bug# 11781477
372 --
373 --
374 CURSOR cur_repay_sch IS
375 SELECT rei_information8 repay_sch,
376 rei_information9 review_Date
377 FROM ghr_pa_request_extra_info
378 WHERE pa_request_id = p_pa_request_rec.pa_request_id
379 AND information_type = 'GHR_US_PAR_STUDENT_LOAN';
380
381 l_repay_sch varchar2(2);
382 l_review_date date;
383
384 CURSOR cur_studloan_ele_end_date(
385 p_ele_name pay_element_types_f.element_name%type,
386 p_asg_id ghr_pa_requests.employee_assignment_id%type)
387 IS
388 select ele.effective_end_date
389 from pay_element_types_f elt,
390 pay_element_links_f ell,
391 pay_element_entries_f ele
392 where p_pa_request_rec.effective_date between elt.effective_start_date and elt.effective_end_date
393 and p_pa_request_rec.effective_date between ell.effective_start_date and ell.effective_end_date
394 and p_pa_request_rec.effective_date between ele.effective_start_date and ele.effective_end_date
395 and elt.element_type_id = ell.element_type_id
396 and ell.element_link_id = ele.element_link_id
397 and ele.assignment_id = p_asg_id
398 and elt.element_name = p_ele_name;
399
400 /**** Commented because FP.F level DB is not having the element_type_id column
401 in pay_element_entries_f.
402
403 SELECT effective_end_date
404 FROM pay_element_entries_f
405 WHERE element_type_id = (SELECT element_type_id
406 FROM pay_element_types_f
407 WHERE element_name = p_ele_name
408 and p_pa_request_rec.effective_date between effective_start_Date and effective_end_date )
409 and assignment_id = p_asg_id;
410 ****/
411 -- 3562069
412
413 --Start of 3604377
414 l_pos_ei_grade_data per_position_extra_info%rowtype;
415
416 CURSOR cur_grd(p_grade_id per_grades.grade_id%TYPE) IS
417 SELECT gdf.segment1 pay_plan
418 ,gdf.segment2 grade_or_level
419 FROM per_grade_definitions gdf
420 ,per_grades grd
421 WHERE grd.grade_id = p_grade_id
422 AND grd.grade_definition_id = gdf.grade_definition_id;
423 --End of 3604377
424
425 l_appt_type PER_PEOPLE_EXTRA_INFO.pei_information3%type;
426 l_ele_name pay_element_types_f.element_name%type;
427 l_ele_end_date pay_element_entries_f.effective_end_date%type;
428
429 l_per_ei_data PER_PEOPLE_EXTRA_INFO%rowtype;
430 -- Bug#3928110
431 --l_percent NUMBER(8,2);
432
433 l_max_allowed_amount NUMBER;
434 l_min_allowed_amount NUMBER;
435 l_temp varchar2(100);
436
437
438 -- Start of bug 4016362
439
440 /***** Added Cursor BG_rec to get business group id given asg_id ****/
441
442 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
443 Select business_group_id bg
444 from per_assignments_f
445 where assignment_id = p_assignment_id
446 and p_eff_date between effective_start_date
447 and effective_end_date;
448
449 ll_bg_id per_all_assignments_f.business_group_id%type;
450
451 -- End of bug 4016362
452 -- Start of 3563491
453 l_asg_ei_data per_assignment_extra_info%rowtype;
454 l_nte_date_flg BOOLEAN:=FALSE;
455
456 --
457 CURSOR cur_nte_check(p_asg_id per_assignments_f.assignment_id%TYPE,
458 p_eff_date ghr_pa_requests.effective_date%TYPE)
459 IS
460 SELECT user_status
461 FROM per_assignment_status_types pst, per_assignments_f paf
462 WHERE paf.assignment_status_type_id = pst.assignment_status_type_id
463 AND paf.assignment_type <> 'B'
464 AND paf.assignment_id = p_asg_id
465 AND (p_eff_date) BETWEEN paf.effective_start_date AND paf.effective_end_date;
466
467 l_asg_status per_assignment_status_types.user_status%type;
468 --
469 --Begin Bug# 4748927
470 l_award_amount NUMBER;
471 --End Bug# 4748927
472
473 -- Bug#5039997 RRR Changes. Added variables, cursors.
474 -- Begin Bug# 5039100
475 l_inct_ctgy_pcnt NUMBER;
476 -- End Bug# 5039100
477 l_biweekly_end_date DATE;
478 l_futr_incentive_cnt NUMBER;
479 l_cnt NUMBER(2);
480 l_min_date DATE;
481 l_max_date DATE;
482 l_min_months NUMBER;
483 l_max_months NUMBER;
484
485 CURSOR c_incentives IS
486 SELECT pa_incentive_category_end_date,pa_incentive_category_percent
487 FROM ghr_pa_incentives
488 WHERE pa_request_id = p_pa_request_rec.pa_request_id
489 AND pa_incentive_category = 'Biweekly';
490
491 CURSOR c_incentive_cnt IS
492 SELECT count(*) cnt
493 FROM ghr_pa_incentives
494 WHERE pa_request_id = p_pa_request_rec.pa_request_id;
495
496 -- Bug#5041985
497 CURSOR c_futr_incentives(l_asg_id NUMBER, l_effective_date DATE) IS
498 SELECT count(*) cnt
499 FROM pay_element_entries_f ee, pay_element_types_f et
500 WHERE ee.assignment_id = l_asg_id
501 AND ee.element_type_id = et.element_type_id
502 AND et.element_name like '%Incentive%'
503 AND ee.effective_start_date > l_effective_date;
504
505 CURSOR cur_noa_id(l_noa_id NUMBER,l_noa_fam_code VARCHAR2, l_eff_date DATE) is
506 SELECT 1
507 FROM ghr_noa_families noa
508 WHERE noa.nature_of_action_id = l_noa_id
509 AND noa.noa_family_code = l_noa_fam_code
510 AND l_eff_date BETWEEN nvl(noa.start_date_active,l_eff_date)
511 AND nvl(noa.end_date_active,l_eff_date)
512 AND noa.enabled_flag = 'Y';
513 -- RRR Changes
514
515 --bug 5482191
516 l_psi VARCHAR2(10);
517
518
519 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
520 l_serv_oblg_type VARCHAR2(2);
521 l_serv_oblg_start_date VARCHAR2(22);
522 l_serv_oblg_end_date VARCHAR2(22);
523
524 CURSOR cur_service_oblg_ei IS
525 SELECT rei_information3 srvc_oblg_type,
526 rei_information4 srvc_oblg_st_date,
527 rei_information5 srvc_oblg_end_date
528 FROM ghr_pa_request_extra_info
529 WHERE pa_request_id = p_pa_request_rec.pa_request_id AND
530 information_type = 'GHR_US_PAR_SERVICE_OBLIGATION';
531 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
532
533 --8528195
534 l_adj_basic_pay_amt NUMBER;
535 l_special_info_type ghr_api.special_information_type;
536 l_value VARCHAR2(60);
537 l_multi_error_flag BOOLEAN;
538 --8528195
539 --Begin Bug# 12557463
540 CURSOR c_get_tsp_scd IS
541 SELECT rei_information6 tsp_scd
542 FROM ghr_pa_request_extra_info
543 WHERE pa_request_id = p_pa_request_rec.pa_request_id
544 AND information_type = 'GHR_US_PAR_SCD_INFO';
545 l_tsp_scd ghr_pa_request_extra_info.rei_information6%type;
546 --End Bug# 12557463
547 --Begin Bug# 14769681
548 CURSOR c_get_tsp_scd_803 IS
549 SELECT rei_information7 tsp_scd
550 FROM ghr_pa_request_extra_info
551 WHERE pa_request_id = p_pa_request_rec.pa_request_id
552 AND information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN';
553 --End Bug# 14769681
554
555 --Begin Bug# 12637350
556 l_fehb_elig_exp_date ghr_pa_request_extra_info.rei_information4%type;
557 l_noa_nte_date date;
558 CURSOR cur_fehb_elg_exp_date IS
559 SELECT rei_information3
560 FROM ghr_pa_request_extra_info
561 WHERE pa_request_id = p_pa_request_rec.pa_request_id
562 AND information_type = 'GHR_US_PAR_BENEFITS';
563
564 cursor cur_asg_prior_date is
565 Select asg.effective_start_date
566 from per_all_assignments_f asg
567 Where asg.assignment_id = p_pa_request_rec.employee_assignment_id
568 and asg.primary_flag = 'Y'
569 and asg.assignment_type <> 'B'
570 order by asg.effective_start_date;
571
572 l_asg_start_date date;
573 --End Bug# 12637350
574 Begin
575
576 ghr_history_api.get_g_session_var(l_session);
577
578 /* Do not allow update to HR if Position_Status is Invalid */
579
580 FOR v_get_psn_status IN
581 c_get_psn_status(p_pa_request_rec.to_position_id) LOOP
582 l_psn_status := v_get_psn_status.status;
583 END LOOP;
584
585 If l_psn_status = 'INVALID' then
586 -- If position is invalid instead of erroring out straight away,
587 -- validate it and if valid, update the status, else give out the error
588 begin
589 ghr_validate_perwsdpo.validate_perwsdpo(p_pa_request_rec.to_position_id,p_pa_request_rec.effective_date);
590 ghr_validate_perwsdpo.update_posn_status(p_pa_request_rec.to_position_id,p_pa_request_rec.effective_date);
591 exception
592 when others then
593 hr_utility.set_message(8301,'GHR_38288_POSITION_INVALID');
594 hr_utility.raise_error;
595 end;
596 End If;
597 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_pa_request_rec.to_position_id,
598 p_pa_request_rec.effective_date);
599
600 /* Get Person Status and last of DDF retrivals.
601
602 /* Produce a warning if Person_User_Type is Invalid */
603 /* Best solution would offer a prompt of continue Y/N */
604 FOR v_get_person_types IN
605 c_get_person_types(p_pa_request_rec.person_id,p_pa_request_rec.effective_date) LOOP
606 l_person_sys_type := v_get_person_types.system_person_type;
607 l_person_usr_type := v_get_person_types.user_person_type;
608 END LOOP;
609 -- Bug 4377361 included EMP_APL for person type condition
610 If l_person_sys_type IN ('EMP','EMP_APL') and l_person_usr_type = 'Invalid Employee' then
611 hr_utility.set_message(8301,'GHR_38289_PERSON_INVALID');
612 --hr_utility.show_error;
613 End If;
614
615 FOR c_noa_family_code_rec in c_noa_family_code(p_pa_request_rec.effective_date) LOOP
616 l_noa_family_code := c_noa_family_code_rec.noa_family_code;
617 --exit;
618 END LOOP;
619
620 /*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' */
621
622 IF p_pa_request_rec.first_action_la_code1 = 'QUM' AND
623 p_pa_request_rec.first_action_la_code2 = 'QUA' AND
624 p_pa_request_rec.first_noa_code = '890' AND
625 p_pa_request_rec.from_pay_plan <> 'GM' THEN
626 hr_utility.set_message(8301, 'GHR_38553_GM_QUM_QUA');
627 hr_utility.raise_error;
628 END IF;
629
630 /* Do not allow creation of assignment if Position is Primary Assignment of another Person */
631 /* Cursor includes check to exclude from Position. */
632 /* only do this check if this is not a future action.*/
633 /* Bug 3454993: Check the following condition only if it is a cancellation action. (as a temp soln)*/
634 /* (Also we have to confirm that is it specific to separation cancellation action) */
635
636 --Start of Bug 3751864
637 --In case of Appointment only we need to check whether any Assginment is found in future.
638 IF (p_pa_request_rec.effective_date <= SYSDATE) THEN
639 IF ( l_noa_family_code IN (
640 'APP' , 'CONV_APP' , 'EXT_NTE' , 'GHR_SAL_CHG_LG',
641 'GHR_SAL_PROM', 'POS_ABOLISH' , 'POS_CHG' , 'POS_ESTABLISH',
642 'POS_REVIEW' , 'REASSIGNMENT' , 'RECRUIT_FILL' , 'RETURN_TO_DUTY')
643 -- Bug#5063298 Restrict this validation for Correction actions.
644 AND l_session.noa_id_correct is NULL
645 ) THEN
646 FOR v_asg_posn_check IN c_asg_posn_check(p_pa_request_rec.TO_Position_id,
647 p_pa_request_rec.person_id,
648 p_pa_request_rec.effective_date)
649 LOOP
650 l_assignment_found := TRUE;
651 END LOOP;
652
653 IF l_assignment_found THEN
654 hr_utility.set_message(8301,'GHR_38290_POSITION_ENCUMBERED');
655 hr_utility.raise_error;
656 END IF;
657
658 -- For other than Appt. we need to check whether any assignment is existing as on that date.
659
660 ELSE
661 FOR v_asg_posn_check1 IN c_asg_posn_check1(p_pa_request_rec.TO_Position_id,
662 p_pa_request_rec.person_id,
663 p_pa_request_rec.effective_date)
664 LOOP
665 l_assignment_found := TRUE;
666 END LOOP;
667
668 IF l_assignment_found THEN
669 hr_utility.set_message(8301,'GHR_38290_POSITION_ENCUMBERED');
670 hr_utility.raise_error;
671 END IF;
672 END IF;
673 END IF;
674 --End of Bug 3751864
675
676 /* If Family is appointment then employee_assignment_type must be ACCEPTED */
677 /* If Family is Return to Duty then employee_assignment_type must be SUSP_ASSIGN */
678 /* If Family is other than 2 above then employee_assignment_type must be ACTIVE_ASSIGN */
679 /* NOA_Family will not be coded as below */
680
681 l_assignment_found := FALSE;
682 If l_person_sys_type = 'APL' then
683 for c_asg_stat_type_rec in c_asg_stat_type (p_pa_request_rec.employee_assignment_id
684 ,p_pa_request_rec.effective_date
685 ,'ACCEPTED') loop
686 l_assignment_found := TRUE;
687 end loop;
688 if not l_assignment_found then
689 hr_utility.set_message(8301, 'GHR_38291_NOT_ACCEPTED');
690 hr_utility.raise_error;
691 end if;
692 -- Bug 4377361 included EMP_APL for person type condition
693 Elsif l_person_sys_type IN ('EMP','EMP_APL') then
694 -- If employees are added to APP family then may need to change this!!
695 -- Bug# 1145963 -- Fetching noa_family_code based on current first_noa_id since
696 -- p_pa_request_rec.noa_family_code always point to family code of original first_noa_id
697 hr_utility.set_location('GHRVALID-passed family code'||p_pa_request_rec.noa_family_code,1);
698 hr_utility.set_location('GHRVALID-current family code'||l_noa_family_code,2);
699 hr_utility.set_location('GHRVALID-noa code'||p_pa_request_rec.first_noa_code,3);
700
701 IF l_noa_family_code = 'RETURN_TO_DUTY' and l_session.noa_id_correct is null THEN
702 for c_asg_stat_type_rec in c_asg_stat_type (p_pa_request_rec.employee_assignment_id
703 ,p_pa_request_rec.effective_date
704 ,'SUSP_ASSIGN') LOOP
705 l_assignment_found := TRUE;
706 END LOOP;
707 IF not l_assignment_found THEN
708 hr_utility.set_message(8301, 'GHR_38292_NOT_SUSP_ASSIGN');
709 hr_utility.raise_error;
710 END IF;
711 /*
712 -- Note : Commenting the following 'Else part' as a temporary fix to bug 637083
713 -- Should be revisiting this code when we have the NOA specific Bus. rules in place
714 Else
715 for c_asg_stat_type_rec in c_asg_stat_type (p_pa_request_rec.employee_assignment_id
716 ,p_pa_request_rec.effective_date
717 ,'ACTIVE_ASSIGN') loop
718 l_assignment_found := TRUE;
719 end loop;
720 if not l_assignment_found then
721 hr_utility.set_message(8301, 'GHR_38293_NOT_ACTIVE_ASSIGN');
722 hr_utility.raise_error;
723 end if;
724 */
725 End If;
726 End If;
727
728 /* Sundar Bug 3263109/3263096 If doing separation, check if any future actions exist. If it exist
729 throw an error */
730 IF (l_noa_family_code = 'SEPARATION') THEN
731 FOR v_future_actions IN c_future_actions(p_pa_request_rec.person_id,p_pa_request_rec.effective_date) LOOP
732 l_futr_actions := v_future_actions.futr_rpa;
733 EXIT;
734 END LOOP;
735 IF (l_futr_actions IS NOT NULL) THEN
736 hr_utility.set_message(8301,'GHR_38847_NO_SEP_WITH_FUTR');
737 hr_utility.raise_error;
738 END IF;
739 END IF;
740 -- End Sundar Bug 3263109/3263096
741
742
743 --- Start fix Code 3417859
744 IF ( p_pa_request_rec.effective_date >= to_date('2004/01/11','YYYY/MM/DD') ) THEN
745 IF ( p_pa_request_rec.to_pay_plan in ('ES','EP','IE','FE') ) THEN
746
747 If p_pa_request_rec.first_noa_code ='893' THEN
748 hr_utility.set_message(8301, 'GHR_38889_SES_WGI_NO');
749 hr_utility.raise_error;
750 END IF;
751
752 l_user_tab_id := ghr_pay_calc.get_user_table_id(
753 p_position_id => p_pa_request_rec.to_position_id
754 ,p_effective_date => p_pa_request_rec.effective_date
755 );
756
757 l_essl_table := FALSE;
758 FOR essl_rec IN c_pay_tab_essl
759 LOOP
760 l_essl_table := TRUE;
761 END LOOP;
762
763 IF ( l_essl_table and p_pa_request_rec.to_step_or_rate <>'00' ) THEN
764 hr_utility.set_message(8301, 'GHR_38849_SES_TO_STEP_OR_RATE');
765 hr_utility.raise_error;
766 END IF;
767 END IF;
768 END IF;
769 --- End fix code 3417859
770 --Start of Bug#3604377
771 IF ( p_pa_request_rec.effective_date >= to_date('2004/01/11','YYYY/MM/DD') ) THEN
772 IF ( p_pa_request_rec.to_pay_plan in ('EE') ) THEN
773
774 IF p_pa_request_rec.first_noa_code ='893' THEN
775 hr_utility.set_message(8301, 'GHR_38897_EE_WGI_NO');
776 hr_utility.raise_error;
777 END IF;
778
779 l_user_tab_id := ghr_pay_calc.get_user_table_id(
780 p_position_id => p_pa_request_rec.to_position_id
781 ,p_effective_date => p_pa_request_rec.effective_date
782 );
783
784 l_essl_table := FALSE;
785 FOR essl_rec IN c_pay_tab_essl
786 LOOP
787 l_essl_table := TRUE;
788 END LOOP;
789
790 IF ( l_essl_table and p_pa_request_rec.to_step_or_rate <>'00' ) THEN
791 hr_utility.set_message(8301, 'GHR_38895_EE_TO_STEP_OR_RATE');
792 hr_utility.raise_error;
793 END IF;
794 END IF;
795 END IF;
796 --End of Bug#3604377
797 -- Bug# 1893483
798 /* Check whether agency code transferred to entered or not in case of 352 separation actions */
799 l_to_agency_code := p_per_sep_retire.agency_code_transfer_to;
800 If p_pa_request_rec.first_noa_code = '352' and
801 l_to_agency_code is NULL THEN
802 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
803 fnd_message.set_token('REQUIRED_LIST','Agency Code Transferred to');
804 hr_utility.raise_error;
805 End If;
806 -- Bug# 1893483
807
808 /* check part hours if work schedule is not F,G,B */
809 l_work_schedule_code:=p_pa_request_rec.WORK_SCHEDULE;
810 l_part_time_hr :=p_pa_request_rec.part_time_hours;
811 If l_work_schedule_code not in ('F','G','B','I','J') and
812 l_part_time_hr is null then
813 hr_utility.set_message(8301, 'GHR_38333_PART_TIME_HR_REQ');
814 hr_utility.raise_error;
815 end if;
816
817 -- Bug#5036370 RRR Changes
818 /* Check whether Service Obligation Information is entered for GHR_INCENTIVE Family or Not */
819 l_serv_oblig_code := p_per_service_oblig.service_oblig_type_code;
820 hr_utility.set_location('RRR 0'||p_per_service_oblig.service_oblig_start_date,10);
821 hr_utility.set_location('RRR 0'||p_per_service_oblig.service_oblig_end_date,10);
822 l_serv_oblig_stdt := fnd_date.canonical_to_date(p_per_service_oblig.service_oblig_start_date);
823 l_serv_oblig_enddt := fnd_date.canonical_to_date(p_per_service_oblig.service_oblig_end_date);
824
825 IF (l_noa_family_code = 'GHR_INCENTIVE' OR
826 (p_pa_request_rec.first_noa_code = '002' AND p_pa_request_rec.second_noa_code IN ('815','816','827'))
827 ) THEN
828 -- Bug#5040179
829 ghr_process_sf52.g_total_pay_check := 'N';
830 FOR incentive_rec IN c_incentive_cnt
831 LOOP
832 l_cnt := incentive_rec.cnt;
833 END LOOP;
834 IF l_cnt <= 0 THEN
835 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
836 fnd_message.set_token('REQUIRED_LIST','Incentive Category Details');
837 hr_utility.raise_error;
838 END IF;
839 IF (p_pa_request_rec.first_noa_code <> '825' OR p_pa_request_rec.second_noa_code <> '825') THEN --bug# 5983639 removed the NOT
840 IF NOT (p_pa_request_rec.first_noa_code = '827' AND p_pa_request_rec.pa_incentive_payment_option = 'B') THEN
841 hr_utility.set_location('first noa code '||p_pa_request_rec.first_noa_code,10);
842 IF ((l_serv_oblig_code IS NULL OR l_serv_oblig_stdt IS NULL) AND l_session.noa_id_correct IS NULL ) THEN
843 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
844 fnd_message.set_token('REQUIRED_LIST','Service Obligation Code, Service Obligation Start Date');
845 hr_utility.raise_error;
846 End If;
847 END IF;
848 IF l_serv_oblig_code IS NOT NULL AND l_serv_oblig_stdt IS NOT NULL THEN
849 /* Check the Service Obligation Period.*/
850 /*Bug # 6738306 "=" added in the below if condition and Token is set for the
851 error message*/
852 IF l_serv_oblig_stdt >= NVL(l_serv_oblig_enddt,to_date('4712/12/31','YYYY/MM/DD')) THEN
853 hr_utility.set_message(8301,'GHR_38999_STDT_GRTR_ENDDT');
854 FND_MESSAGE.SET_TOKEN('MESSAGE','Service Obligation End Date must be
855 greater than Service Obligation Start Date');
856 --Bug 14276354 Added Token 14276354
857 FND_MESSAGE.SET_TOKEN('EIT','US Fed Service Obligation Information');
858 hr_utility.raise_error;
859 END IF;
860 IF p_pa_request_rec.first_noa_code = '815' THEN
861 l_min_months := 6;
862 l_max_months := 48;
863 ELSIF p_pa_request_rec.first_noa_code = '816' THEN
864 l_min_months := 0;
865 l_max_months := 48;
866 END IF;
867
868 l_min_date := ADD_MONTHS(l_serv_oblig_stdt,l_min_months);
869 l_max_date := ADD_MONTHS(l_serv_oblig_stdt,l_max_months);
870 IF NOT (NVL(l_serv_oblig_enddt,to_date('4712/12/31','YYYY/MM/DD')) BETWEEN l_min_date AND l_max_date) THEN
871 hr_utility.set_message(8301,'GHR_38998_INV_SERVOBL_PERIOD');
872 fnd_message.set_token('NOAC',p_pa_request_rec.first_noa_code);
873 fnd_message.set_token('MIN_PERIOD', to_char(l_min_months)||' Months');
874 fnd_message.set_token('MAX_PERIOD', ' 4 Years');
875 hr_utility.raise_error;
876 END IF;
877 END IF;
878 END IF;
879 END IF;
880 -- Bug#5039997
881 /*Check whether the Retention Incentive Review Date is entered for NOAC 827 where payment option "B" and
882 Effective End Date is NOT NULL */
883 IF p_pa_request_rec.first_noa_code = '827' AND p_pa_request_rec.pa_incentive_payment_option = 'B' THEN
884 l_cnt := 0;
885 For incentive_rec IN c_incentives
886 LOOP
887 l_biweekly_end_date := incentive_rec.pa_incentive_category_end_date;
888 l_inct_ctgy_pcnt := incentive_rec.pa_incentive_category_percent;
889 l_cnt := 1;
890 END LOOP;
891 -- Begin Bug# 5039100
892 IF l_inct_ctgy_pcnt <> 0 THEN
893 -- End Bug# 5039100
894 IF l_cnt = 1 AND l_biweekly_end_date IS NULL THEN
895 IF p_per_group1.retention_inc_review_date IS NULL THEN
896 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
897 fnd_message.set_token('REQUIRED_LIST','Retention Incentive Review Date');
898 hr_utility.raise_error;
899 END IF;
900 END IF;
901 END IF;
902 END IF;
903 /* Bug 5041985 -- If doing separation, check if any future Incentive elements exists.
904 If it exists, throw an error */
905 IF (l_noa_family_code = 'SEPARATION') THEN
906 FOR v_future_incentives IN c_futr_incentives(p_pa_request_rec.employee_assignment_id,
907 p_pa_request_rec.effective_date)
908 LOOP
909 l_futr_incentive_cnt := v_future_incentives.cnt;
910 EXIT;
911 END LOOP;
912 IF (l_futr_incentive_cnt > 0) THEN
913 hr_utility.set_message(8301,'GHR_38120_NO_SEP_FUTR_INCN_ELT');
914 hr_utility.raise_error;
915 END IF;
916 END IF;
917 /* Bug#5039691 Verify whether the NOA Code is Valid or not as on the effective date.
918 Verify this for all actions EXCEPT correction, cancellation. */
919 IF p_pa_request_rec.first_noa_code NOT IN ('001','002') THEN
920 l_exists := false;
921 FOR noa_id in cur_noa_id(p_pa_request_rec.first_noa_id,
922 l_noa_family_code,
923 p_pa_request_rec.effective_date)
924 LOOP
925 l_exists := true;
926 exit;
927 END LOOP;
928 -- to include logic to check if not valid as of the effective date
929 IF NOT l_exists THEN
930 hr_utility.set_message(8301, 'GHR_38167_INV_NAT_OF_ACT_FAM');
931 hr_utility.raise_error;
932 END IF;
933 END IF;
934 -- Bug#5036370 RRR Changes
935
936 /* check total pay when pay basis is 'PA' */
937 l_To_Pay_Basis:=p_pa_request_rec.TO_PAY_BASIS;
938 l_To_Locality_Adj:= p_pa_request_rec.TO_LOCALITY_ADJ;
939 l_To_Basic_Pay :=p_pa_request_rec.TO_BASIC_PAY;
940 l_To_adj_basic_pay := p_pa_request_rec.TO_ADJ_BASIC_PAY;
941 l_To_other_pay := p_pa_request_rec.TO_OTHER_PAY_AMOUNT;
942 l_To_total_pay := p_pa_request_rec.TO_TOTAL_SALARY;
943 l_to_supervisor_diff := p_pa_request_rec.TO_SUPERVISORY_DIFFERENTIAL ;
944
945 if l_To_Pay_Basis ='PA' and
946 nvl(l_To_adj_basic_pay,0) <> nvl(l_To_Basic_Pay,0) + nvl(l_To_Locality_Adj,0) then
947 hr_utility.set_message(8301, 'GHR_38334_ADJ_BASIC_PAY');
948 hr_utility.raise_error;
949 end if;
950 --bug 3584511
951 IF (p_pa_request_rec.noa_family_code <> 'GHR_STUDENT_LOAN' and ghr_process_sf52.g_total_pay_check = 'Y') THEN
952 if l_To_Pay_Basis ='PA' and
953 nvl(l_To_total_pay,0) <> nvl(l_To_adj_basic_pay,0) + nvl(l_To_other_pay,0) then
954 hr_utility.set_message(8301, 'GHR_38335_TOTAL_PAY');
955 hr_utility.raise_error;
956 end if;
957 END IF;
958 /* check staffing diff. */
959 l_staffing_differential:=p_pa_request_rec.TO_STAFFING_DIFFERENTIAL;
960 If l_staffing_differential is not null and
961 ghr_pay_calc.convert_amount(
962 l_staffing_differential,
963 'PA',
964 p_pa_request_rec.to_pay_basis)
965 > round((nvl(l_To_Basic_Pay,0) * 0.05)) then
966 hr_utility.set_message(8301, 'GHR_38344_INVALID_STAFF_DIFF');
967 hr_utility.raise_error;
968 end if;
969
970 /* check retention allowance */
971 l_Retention_Allowance:=p_pa_request_rec.TO_RETENTION_ALLOWANCE;
972 -- Code added for Student Loan
973 IF p_pa_request_rec.noa_family_code <> 'GHR_STUDENT_LOAN' then
974 if l_Retention_Allowance is not null and
975 l_Retention_Allowance > (nvl(l_To_Basic_Pay,0) * 0.25) then -- Bug 3067420 Removed 'ROUND'
976 hr_utility.set_message(8301, 'GHR_38345_INVALID_RET_ALLOW');
977 hr_utility.raise_error;
978 end if;
979 END IF;
980
981 -- Modified for FWS
982
983 IF p_pa_request_rec.to_pay_basis ='PH' AND
984 (p_pa_request_rec.first_noa_code IN ('818','819') or NVL(l_to_supervisor_diff,0) > 0) THEN
985 hr_utility.set_message(8301, 'GHR_38844_NOT_ENTITLED_OTH_PAY');
986 hr_utility.raise_error;
987 END IF;
988
989 IF p_pa_request_rec.from_pay_basis ='PA' and p_pa_request_rec.to_pay_basis ='PH' THEN
990 FOR chk_for_sup_rec IN check_for_supervisory LOOP
991 IF nvl(chk_for_sup_rec.screen_entry_value,0) > 0 THEN
992 hr_utility.set_message(8301, 'GHR_38844_NOT_ENTITLED_OTH_PAY');
993 hr_utility.raise_error;
994 END IF;
995 END LOOP;
996 END IF;
997
998 --Modified for FWS
999
1000 /* commented it for bug 3218900 by Ashley
1001 Added for bug 3067420 check supervisiry differential by Ashley
1002 l_to_supervisor_diff := p_pa_request_rec.TO_SUPERVISORY_DIFFERENTIAL ;
1003
1004 if l_to_supervisor_diff is not null and
1005 ghr_pay_calc.convert_amount(
1006 l_to_supervisor_diff,
1007 'PA',
1008 p_pa_request_rec.to_pay_basis)
1009 > (nvl(l_To_Basic_Pay,0) * 0.25) then
1010 hr_utility.set_message(8301, 'GHR_SUP_DIFF_AMT_TOO_BIG');
1011 hr_utility.raise_error;
1012 end if;
1013 */
1014
1015 /* check effective date */
1016 /* Bug# 923276 - Remove 90-day limitation on effective date - Hence commented out - 24th July,1999
1017 l_effective_date :=p_pa_request_rec.EFFECTIVE_DATE;
1018 l_effective_date :=p_pa_request_rec.EFFECTIVE_DATE;
1019 if (l_effective_date-sysdate) > 90 then
1020 hr_utility.set_message(8301, 'GHR_38379_INV_EFFECT_DATE');
1021 hr_utility.raise_error;
1022 end if;
1023 */
1024
1025
1026 /* check that AUO and Availability Pay are mutually exclusive for a person */
1027
1028
1029 If p_pa_request_rec.first_noa_code = '818' then -- if AUo
1030 -- check if the person already gets an AP
1031 ghr_api.retrieve_element_entry_value
1032 (P_ELEMENT_NAME => 'Availability Pay',
1033 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
1034 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1035 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1036 P_VALUE => l_Prem_pay,
1037 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1038 );
1039
1040 ghr_api.retrieve_element_entry_value
1041 (P_ELEMENT_NAME => 'Availability Pay',
1042 P_INPUT_VALUE_NAME => 'Amount',
1043 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1044 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1045 P_VALUE => l_amount,
1046 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1047 );
1048
1049 If l_prem_pay is not null or
1050 l_amount is not null then
1051 hr_utility.set_message(8301,'GHR_38387_AP_EXISTS');
1052 hr_utility.raise_error;
1053 End if;
1054
1055 Elsif p_pa_request_rec.first_noa_code = '819' then -- if AP
1056 -- check if the person already gets an AUO
1057 ghr_api.retrieve_element_entry_value
1058 (P_ELEMENT_NAME => 'AUO',
1059 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
1060 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1061 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1062 P_VALUE => l_Prem_pay,
1063 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1064 );
1065
1066 ghr_api.retrieve_element_entry_value
1067 (P_ELEMENT_NAME => 'AUO',
1068 P_INPUT_VALUE_NAME => 'Amount',
1069 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1070 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1071 P_VALUE => l_amount,
1072 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1073 );
1074
1075 If l_prem_pay is not null or
1076 l_amount is not null then
1077 hr_utility.set_message(8301,'GHR_38388_AUO_EXISTS');
1078 hr_utility.raise_error;
1079 End if;
1080 End if;
1081
1082
1083 /*---- Removed for Bug 708295 Check pay caps
1084 ghr_pay_caps.do_pay_caps_sql
1085 (p_effective_date => p_pa_request_rec.effective_date
1086 ,p_pay_rate_determinant => p_pa_request_rec.pay_rate_determinant
1087 ,p_pay_plan => p_pa_request_rec.to_pay_plan
1088 ,p_pay_basis => p_pa_request_rec.to_pay_basis
1089 ,p_to_position_id => p_pa_request_rec.to_position_id
1090 ,p_basic_pay => p_pa_request_rec.to_basic_pay
1091 ,p_locality_adj => p_pa_request_rec.to_locality_adj
1092 ,p_adj_basic_pay => p_pa_request_rec.to_adj_basic_pay
1093 ,p_total_salary => p_pa_request_rec.to_total_salary
1094 ,p_other_pay_amount => p_pa_request_rec.to_other_pay_amount
1095 ,p_au_overtime => p_pa_request_rec.to_au_overtime
1096 ,p_availability_pay => p_pa_request_rec.to_availability_pay
1097 ,p_open_pay_fields => l_open_pay_fields
1098 ,p_message_set => l_message_set
1099 );
1100 */
1101
1102 --
1103 -- check that the UOM entered on a 872 is Hours.
1104 -- added for bug#705411
1105 --
1106 -- Start Bug 1379280
1107 if p_pa_request_rec.first_noa_code in ('846','847','872') and
1108 p_pa_request_rec.award_uom <> 'H' then
1109 hr_utility.set_message(8301,'GHR_38595_INVALID_AWARD_UOM');
1110 hr_utility.raise_error;
1111 end if;
1112 if p_pa_request_rec.first_noa_code in ('840','841','842','843','844','845','848',
1113 '849','815','816','825','878','879') and
1114 p_pa_request_rec.award_uom <> 'M' then
1115 hr_utility.set_message(8301,'GHR_38597_INVALID_AWARD_UOM');
1116 hr_utility.raise_error;
1117 end if;
1118 -- End Bug 1379280
1119
1120 --Pradeep for 3934195.
1121 IF p_pa_request_rec.noa_family_code = 'AWARD' THEN
1122 -- Begin Bug# 4748927
1123 -- Begin Bug# 5020754
1124 IF p_pa_request_rec.award_salary IS NULL THEN
1125
1126 --8528195
1127 l_adj_basic_pay_amt := p_pa_request_rec.from_adj_basic_pay;
1128 IF ((p_pa_request_rec.first_noa_code = '879') OR (p_pa_request_rec.first_noa_code = '002' and
1129 p_pa_request_rec.second_noa_code = '879')) THEN
1130
1131 ghr_history_fetch.return_special_information
1132 (p_person_id => p_pa_request_rec.person_id,
1133 p_structure_name => 'US Fed Perf Appraisal',
1134 p_effective_date => p_pa_request_rec.effective_date,
1135 p_special_info => l_special_info_type
1136 );
1137 if l_special_info_type.segment6 is not null then
1138 ghr_api.retrieve_element_entry_value (p_element_name => 'Adjusted Basic Pay'
1139 ,p_input_value_name => 'Amount'
1140 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1141 ,p_effective_date => fnd_date.canonical_to_date(l_special_info_type.segment6)
1142 ,p_value => l_value
1143 ,p_multiple_error_flag=> l_multi_error_flag);
1144 l_adj_basic_pay_amt := l_value;
1145 end if;
1146 END IF;
1147 --8528195
1148
1149 ghr_pay_calc.award_amount_calc (
1150 p_position_id => p_pa_request_rec.to_position_id
1151 ,p_pay_plan => p_pa_request_rec.from_pay_plan
1152 ,p_award_percentage => NULL
1153 ,p_user_table_id => p_pa_request_rec.from_pay_table_identifier
1154 ,p_grade_or_level => p_pa_request_rec.from_grade_or_level
1155 ,p_effective_date => p_pa_request_rec.effective_date
1156 ,p_basic_pay => p_pa_request_rec.from_basic_pay
1157 ,p_adj_basic_pay => l_adj_basic_pay_amt
1158 ,p_duty_station_id => p_pa_request_rec.duty_station_id
1159 ,p_prd => p_pa_request_rec.pay_rate_determinant
1160 ,p_pay_basis => p_pa_request_rec.from_pay_basis
1161 ,p_person_id => p_pa_request_rec.person_id
1162 ,p_award_amount => l_award_amount
1163 ,p_award_salary => l_award_salary
1164 );
1165 ELSE
1166 l_award_salary := p_pa_request_rec.award_salary;
1167 END IF;
1168
1169 -- End Bug# 5020754
1170 /*l_award_salary := ghr_pay_calc.convert_amount(p_pa_request_rec.from_basic_pay
1171 ,p_pa_request_rec.from_pay_basis,'PA');*/
1172 -- end Bug# 4748927
1173 --Use the same Message Name for All.
1174 hr_utility.set_message(8301,'GHR_38904_AWARD_AMT_TOO_BIG5');
1175
1176 --bug#5482191
1177
1178
1179 IF ( p_pa_request_rec.first_noa_code='844'
1180 OR p_pa_request_rec.second_noa_code='844' ) THEN
1181
1182 l_max_allowed_amount := 5*l_award_salary/100;
1183 hr_utility.set_message_token('ALLOWED','5%');
1184
1185 ELSIF ( p_pa_request_rec.first_noa_code IN ('840','841')
1186 OR p_pa_request_rec.second_noa_code IN ('840','841')
1187 OR p_pa_request_rec.first_noa_code IN ('885','886') AND l_psi = '00'
1188 OR p_pa_request_rec.second_noa_code IN ('885','886') AND l_psi = '00' ) THEN
1189
1190 l_max_allowed_amount := 25*l_award_salary/100;
1191 hr_utility.set_message_token('ALLOWED','25%');
1192 --8528195
1193 ELSIF (p_pa_request_rec.first_noa_code='879'
1194 OR p_pa_request_rec.second_noa_code='879') THEN
1195
1196 l_max_allowed_amount := 20*l_award_salary/100;
1197 hr_utility.set_message_token('ALLOWED','20%');
1198 --8528195
1199 ELSIF ( p_pa_request_rec.first_noa_code IN ('878')
1200 OR p_pa_request_rec.second_noa_code IN ('878') ) THEN
1201
1202 l_max_allowed_amount := 35*l_award_salary/100;
1203 hr_utility.set_message_token('ALLOWED','35%');
1204
1205 --bug#5482191
1206 ELSIF (( p_pa_request_rec.first_noa_code IN ('849')
1207 OR p_pa_request_rec.second_noa_code IN ('849')) and l_psi = '00' ) THEN
1208
1209 l_max_allowed_amount := 35*l_award_salary/100;
1210 hr_utility.set_message_token('ALLOWED','35%');
1211
1212
1213
1214 ELSIF ( p_pa_request_rec.first_noa_code IN ('825','842','843','848')
1215 OR p_pa_request_rec.second_noa_code IN ('825','842','843','848') ) THEN
1216
1217 l_max_allowed_amount := 25000;
1218 hr_utility.set_message(8301,'GHR_38905_AWARD_AMT_TOO_BIG6');
1219 hr_utility.set_message_token('ALLOWED','$25000');
1220
1221 END IF;
1222
1223
1224 IF ( p_pa_request_rec.first_noa_code='816'
1225 OR p_pa_request_rec.second_noa_code='816' ) THEN
1226
1227 IF p_pa_request_rec.from_pay_plan = 'EE' THEN
1228 IF (50*l_award_salary/100) > 50000 THEN
1229 l_max_allowed_amount := 50000;
1230 ELSE
1231 l_max_allowed_amount := 50*l_award_salary/100;
1232 END IF;
1233 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1234 hr_utility.set_message(8301, 'GHR_38898_AWARD_AMT_TOO_BIG3');
1235 hr_utility.raise_error;
1236 END IF;
1237 --3818297 Added NVL
1238 ELSIF ( ghr_pay_calc.LEO_position( p_prd => l_temp
1239 ,p_position_id => NVL(p_pa_request_rec.to_position_id,p_pa_request_rec.from_position_id)
1240 ,p_retained_user_table_id => l_temp
1241 ,p_duty_station_id => l_temp
1242 ,p_effective_date => p_pa_request_rec.effective_date
1243 )
1244 ) THEN
1245 l_max_allowed_amount := 25*l_award_salary/100;
1246 IF l_max_allowed_amount < 15000 THEN
1247 l_max_allowed_amount := 15000;
1248 END IF;
1249 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1250 hr_utility.set_message(8301, 'GHR_38896_AWARD_AMT_TOO_BIG2');
1251 hr_utility.raise_error;
1252 END IF;
1253 --3818297 Added NVL
1254 ELSIF (NOT ghr_pay_calc.LEO_position( p_prd => l_temp
1255 ,p_position_id => NVL(p_pa_request_rec.to_position_id,p_pa_request_rec.from_position_id)
1256 ,p_retained_user_table_id => l_temp
1257 ,p_duty_station_id => l_temp
1258 ,p_effective_date => p_pa_request_rec.effective_date
1259 ) )THEN
1260
1261 l_max_allowed_amount := 25*l_award_salary/100;
1262 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1263 hr_utility.set_message(8301, 'GHR_AWARD_AMT_TOO_BIG');
1264 hr_utility.raise_error;
1265 END IF;
1266 END IF;
1267
1268 ELSIF ( p_pa_request_rec.first_noa_code='815'
1269 OR p_pa_request_rec.second_noa_code='815' ) THEN
1270
1271 IF p_pa_request_rec.from_pay_plan = 'EE' THEN
1272
1273 IF (50*l_award_salary/100) > 50000 THEN
1274 l_max_allowed_amount := 50000;
1275 ELSE
1276 l_max_allowed_amount := 50*l_award_salary/100;
1277 END IF;
1278 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1279 hr_utility.set_message(8301, 'GHR_38898_AWARD_AMT_TOO_BIG3');
1280 hr_utility.raise_error;
1281 END IF;
1282 ELSE
1283 l_max_allowed_amount := 25*l_award_salary/100;
1284 hr_utility.set_message_token('ALLOWED','25%');
1285
1286 END IF;
1287 END IF;
1288
1289 -- Raise an Error if Award Amount is Greater than the Maximum Allowed Amount.
1290 IF p_pa_request_rec.award_amount > round(l_max_allowed_amount) THEN
1291 --Name is already set.
1292 hr_utility.raise_error;
1293 ELSE
1294
1295 --If there is no error then clear the message stack.
1296 hr_utility.clear_message;
1297
1298 END IF;
1299
1300 --Check for Minimum Amount.
1301
1302 --Use the same Message Name for All.
1303 hr_utility.set_message(8301, 'GHR_38903_AWARD_AMT_TOO_LESS');
1304
1305 --Getting the Minimum Allowed Amount.
1306 IF ( p_pa_request_rec.first_noa_code='879'
1307 OR p_pa_request_rec.second_noa_code='879' ) THEN
1308
1309 l_min_allowed_amount := 5*l_award_salary/100;
1310 hr_utility.set_message_token('ALLOWED','5%');
1311 END IF;
1312
1313 --Raise an Error if Award Amount is Less than the Minimum Allowed Amount.
1314 IF p_pa_request_rec.award_amount < trunc(nvl(l_min_allowed_amount,0)) THEN
1315 hr_utility.raise_error;
1316 ELSE
1317 -- If there is no error then clear the message stack.
1318 hr_utility.clear_message;
1319 END IF;
1320
1321 END IF; -- End if of p_pa_request_rec.noa_family_code = 'AWARD'
1322 --Pradeep end of Bug 3934195
1323 --
1324 -- Wherever part-time indicator is enterable,
1325 -- if the Work Schedule is either B, F, G, I, or J,
1326 -- then the Part-Time Indicator must be null.
1327 --
1328 if p_pa_request_rec.noa_family_code in ('APP', 'CHG_HOURS', 'CHG_WORK_SCHED', 'CONV_APP'
1329 ,'REASSIGNMENT', 'RETURN_TO_DUTY') and
1330 p_pa_request_rec.work_schedule in ('B','F','G','I','J') and
1331 p_asg_non_sf52.parttime_indicator is not null then
1332 hr_utility.set_message(8301,'GHR_38621_PART_TIME_IND_NR');
1333 hr_utility.raise_error;
1334 end if;
1335 --
1336 -- START fix for 3563491 Madhuri
1337 --
1338 IF (p_pa_request_rec.noa_family_code = 'EXT_NTE') THEN
1339 ghr_history_fetch.fetch_asgei (
1340 p_assignment_id =>p_pa_request_rec.employee_assignment_id ,
1341 p_information_type => 'GHR_US_ASG_NTE_DATES' ,
1342 p_date_effective => nvl(p_pa_request_rec.effective_date,trunc(sysdate)) ,
1343 p_asg_ei_data => l_asg_ei_data
1344 );
1345 -- LWOP NTE
1346 IF p_pa_request_rec.FIRST_NOA_CODE ='773' THEN
1347 IF (l_asg_ei_data.aei_information5 is not null and l_asg_ei_data.aei_information6 is not null) Then
1348 l_nte_date_flg := TRUE;
1349 END IF;
1350 -- Suspension NTE
1351 IF (l_asg_ei_data.aei_information7 is not null and l_asg_ei_data.aei_information8 is not null) Then
1352 l_nte_date_flg := TRUE;
1353 END IF;
1354 -- END IF;
1355 --Furlough NTE
1356 ELSIF p_pa_request_rec.FIRST_NOA_CODE ='772' THEN
1357 IF (l_asg_ei_data.aei_information9 is not null and l_asg_ei_data.aei_information10 is not null) Then
1358 l_nte_date_flg := TRUE;
1359 END IF;
1360 --
1361 -- CONVERSION TO APPT, POSITION CHANGE NTE etc..
1362 --
1363 ELSIF p_pa_request_rec.FIRST_NOA_CODE in ('750','760','761','762','765','769','770') THEN
1364 IF (l_asg_ei_data.AEI_INFORMATION4 is not null) THEN
1365 l_nte_date_flg := TRUE;
1366 END IF;
1367 ELSE
1368 -- LWP NTE
1369 IF (l_asg_ei_data.aei_information11 is not null and l_asg_ei_data.aei_information12 is not null) Then
1370 l_nte_date_flg := TRUE;
1371 END IF;
1372 -- Sabbatical NTE
1373 IF (l_asg_ei_data.aei_information13 is not null and l_asg_ei_data.aei_information14 is not null) Then
1374 l_nte_date_flg := TRUE;
1375 END IF;
1376 END IF;
1377
1378 IF NOT l_nte_date_flg THEN
1379 FOR cur_nte_check_rec in cur_nte_check (p_pa_request_rec.employee_assignment_id,
1380 nvl(p_pa_request_rec.effective_date,trunc(sysdate)) )
1381 LOOP
1382 l_asg_status := cur_nte_check_rec.user_status;
1383 END LOOP;
1384 hr_utility.set_message(8301,'GHR_38920_NO_NTE_DATE');
1385 hr_utility.set_message_token('ASG_STATUS',l_asg_status);
1386 hr_utility.raise_error;
1387 END IF;
1388
1389 END IF;
1390 -- END of fix for 3563491
1391 --
1392
1393 -- New Termination of RG processing
1394 -- For 866 actions
1395 -- The To Position cannot be changed for this nature of action. Please
1396 -- process a separate action to change the To Position.
1397 IF p_pa_request_rec.first_noa_code = '866'
1398 AND p_pa_request_rec.from_position_id <> p_pa_request_rec.to_position_id
1399 THEN
1400 hr_utility.set_message(8301,'GHR_38693_NO_UPDATE_TO_POS');
1401 hr_utility.raise_error;
1402 END IF;
1403 --
1404 -- Added the following by skutteti on 08-Nov-99 for bug #983824
1405 --
1406 -- For change in work schedule and change in hours,
1407 -- if the work schedule is P,Q,S or T then the Parttime indicator must not be null
1408 --
1409 if p_pa_request_rec.noa_family_code in ('CHG_HOURS', 'CHG_WORK_SCHED') and
1410 p_pa_request_rec.work_schedule in ('P','Q','S','T') and
1411 p_asg_non_sf52.parttime_indicator is null then
1412 hr_utility.set_message(8301,'GHR_PART_TIME_IND_IS_NULL');
1413 hr_utility.raise_error;
1414 end if;
1415 -- Student Loan Repayment Code Changes start
1416 -- added for Student Loan Repayment Changes - 3494728 bug
1417 IF ( p_pa_request_rec.first_noa_code = '817' or p_pa_request_rec.second_noa_code = '817') THEN
1418
1419 --3562069
1420 ghr_history_fetch.fetch_peopleei
1421 (p_person_id => p_pa_request_rec.person_id,
1422 p_information_type => 'GHR_US_PER_GROUP1',
1423 p_date_effective => nvl(p_pa_request_rec.effective_date,trunc(sysdate)),
1424 p_per_ei_data => l_per_ei_data
1425 );
1426
1427
1428 l_appt_type := l_per_ei_data.pei_information3;
1429
1430 hr_utility.set_location('The Appointment Type is: '||l_appt_type,12345);
1431
1432 IF l_appt_type in ('34','44') THEN
1433 hr_utility.set_message(8301,'GHR_38878_APPT_TYPE_SCH_C');
1434 hr_utility.raise_error;
1435 END IF;
1436
1437 FOR cur_repay_sch_rec in cur_repay_sch
1438 LOOP
1439 l_repay_sch := cur_repay_sch_rec.repay_sch;
1440 l_review_date := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(cur_repay_sch_rec.review_date));
1441 END LOOP;
1442
1443 IF ( p_pa_request_rec.award_amount > 10000 ) then
1444 hr_utility.set_message(8301,'GHR_38862_AMT_EXCEEDS_LIMIT');
1445 hr_utility.raise_error;
1446 END IF;
1447
1448 IF l_repay_sch IS NULL THEN
1449 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
1450 hr_utility.set_message_token('REQUIRED_LIST','Repayment Schedule' );
1451 hr_utility.raise_error;
1452 END IF;
1453
1454 IF l_review_date is not null THEN
1455 IF (l_review_date < p_pa_request_rec.effective_date) THEN
1456 hr_utility.set_message(8301,'GHR_38863_REVIEW_DATE_LESS');
1457 hr_utility.raise_error;
1458 END IF;
1459 END IF;
1460
1461 IF l_repay_sch = 'L' THEN
1462 ghr_api.retrieve_element_entry_value (p_element_name => 'Student Loan Repayment'
1463 ,p_input_value_name => 'Amount'
1464 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1465 ,p_effective_date => p_pa_request_rec.effective_date
1466 ,p_value => l_slr_recur_amount
1467 ,p_multiple_error_flag => l_multiple_error_flag);
1468 if l_slr_recur_amount is not null then
1469 hr_utility.set_message(8301,'GHR_38864_ERR_CHG_REPAYMNT');
1470 hr_utility.raise_error;
1471 end if;
1472 else
1473 ghr_api.retrieve_element_entry_value (p_element_name => 'Student Loan Repayment LumpSum'
1474 ,p_input_value_name => 'Amount'
1475 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1476 ,p_effective_date => p_pa_request_rec.effective_date
1477 ,p_value => l_slr_lumpsum
1478 ,p_multiple_error_flag => l_multiple_error_flag);
1479
1480 l_ele_name := 'Student Loan Repayment LumpSum';
1481
1482 FOR cur_ele_end_date_rec IN cur_studloan_ele_end_date(l_ele_name,
1483 p_pa_request_rec.employee_assignment_id)
1484 LOOP
1485 l_ele_end_date := cur_ele_end_date_rec.effective_end_date;
1486 END LOOP;
1487
1488 if (l_slr_lumpsum is not null and l_session.noa_id_correct is NOT NULL) then
1489 hr_utility.set_message(8301,'GHR_38864_ERR_CHG_REPAYMNT');
1490 hr_utility.raise_error;
1491 elsif (p_pa_request_rec.effective_date <= l_ele_end_date) then
1492 hr_utility.set_message(8301,'GHR_38867_ERR_ELE_OVERLAPS');
1493 hr_utility.set_message_token('EFF_DATE',l_ele_end_date);
1494 hr_utility.raise_error;
1495 end if;
1496
1497 end if;
1498 end if;
1499 -- Student Loan Repayment Code Changes end here
1500 --- Start Bug 1551311
1501 --- check for other pay null for first time 810,818,819 actions
1502
1503 IF p_pa_request_rec.first_noa_code = '810'
1504 and l_session.noa_id_correct is NULL THEN
1505 ghr_api.retrieve_element_entry_value
1506 (p_element_name => 'Retention Allowance',
1507 p_input_value_name => 'Amount',
1508 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1509 p_effective_date => p_pa_request_rec.effective_date,
1510 p_value => l_reta_amount,
1511 p_multiple_error_flag => l_multiple_error_flag
1512 );
1513 ghr_api.retrieve_element_entry_value
1514 (p_element_name => 'Supervisory Differential',
1515 p_input_value_name => 'Amount',
1516 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1517 p_effective_date => p_pa_request_rec.effective_date,
1518 p_value => l_supv_amount,
1519 p_multiple_error_flag => l_multiple_error_flag
1520 );
1521 ghr_api.retrieve_element_entry_value
1522 (p_element_name => 'Staffing Differential',
1523 p_input_value_name => 'Amount',
1524 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1525 p_effective_date => p_pa_request_rec.effective_date,
1526 p_value => l_stad_amount,
1527 p_multiple_error_flag => l_multiple_error_flag
1528 );
1529 hr_utility.set_location('Ret Amount '||l_reta_amount,4);
1530 hr_utility.set_location('Supv Amount '||l_supv_amount,5);
1531 hr_utility.set_location('Stad Amount '||l_stad_amount,6);
1532 /* Pradeep commented this for 3306515.
1533
1534 IF (l_reta_amount is NULL and
1535 l_supv_amount is NULL and
1536 l_stad_amount is NULL )
1537 and
1538 (p_pa_request_rec.to_other_pay_amount is null or
1539 p_pa_request_rec.to_other_pay_amount = 0 ) THEN
1540 hr_utility.set_message(8301,'GHR_38589_NULL_OTHER_PAY');
1541 hr_utility.raise_error;
1542 END IF;
1543 */
1544 END IF;
1545
1546 --Pradeep.
1547 IF p_pa_request_rec.first_noa_code = '850'
1548 OR ( p_pa_request_rec.first_noa_code='002' and p_pa_request_rec.second_noa_code ='850') THEN
1549
1550 FOR cur_mddds_pay_rec in cur_mddds_pay
1551 LOOP
1552 l_mddds_special_pay_amount := cur_mddds_pay_rec.amount;
1553 l_mddds_specia_pay_nte_date := fnd_date.canonical_to_date(cur_mddds_pay_rec.nte_date);
1554 l_premium_pay_ind := cur_mddds_pay_rec.premium_pay_ind;
1555 END LOOP;
1556
1557 IF l_premium_pay_ind IS NULL THEN
1558
1559 ghr_api.retrieve_element_entry_value
1560 (p_element_name => 'Premium Pay',
1561 p_input_value_name => 'Premium Pay Ind',
1562 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1563 p_effective_date => p_pa_request_rec.effective_date,
1564 p_value => l_premium_pay_ind,
1565 p_multiple_error_flag => l_multiple_error_flag
1566 );
1567 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,4);
1568
1569 END IF;
1570
1571 /* IF l_premium_pay_ind IS NOT NULL and p_premium_pay.premium_pay_ind IS NOT NULL THEN
1572
1573 hr_utility.set_message(8301, 'GHR_38861_PREM_PAY_IND_ALREADY');
1574 hr_utility.raise_error;
1575
1576 END IF;
1577
1578 IF p_premium_pay.premium_pay_ind IS NOT NULL THEN
1579 l_premium_pay_ind := p_premium_pay.premium_pay_ind;
1580 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,5);
1581 END IF; --Premium Pay Ind is not coming from p_premium_pay.premium_pay_ind.
1582 */
1583 IF ( l_premium_pay_ind IS NULL ) THEN
1584 l_null_list := 'Premium Pay Indicator';
1585 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
1586 fnd_message.set_token('REQUIRED_LIST',l_null_list);
1587 hr_utility.raise_error;
1588 END IF;
1589
1590 --Pradeep. Bug# 3562063 New Business Rules for Title 38
1591 IF l_premium_pay_ind NOT IN ( 'K','X' ) THEN
1592 hr_utility.set_message(8301,'GHR_38871_850_PREM_PAY');
1593 hr_utility.raise_error;
1594 END IF;
1595
1596
1597 l_occ_code := p_pa_request_rec.from_occ_code;
1598 --Bug# 3562063
1599 --If Nature of Action is 850 and Occupation Series is 0610, then Premium Pay Indicator can only be K.
1600 If ( l_occ_code = '0610' )
1601 AND ( l_premium_pay_ind <> 'K' ) THEN
1602
1603 hr_utility.set_message(8301,'GHR_38875_850_OCC_SERIES1');
1604 hr_utility.raise_error;
1605
1606 End If;
1607
1608 --If Nature of Action is 850 and Occupation Series is 0660, then Premium Pay Indicator can not be K.
1609 If ( l_occ_code = '0660' )
1610 AND ( l_premium_pay_ind = 'K' ) THEN
1611
1612 hr_utility.set_message(8301,'GHR_38876_850_OCC_SERIES2');
1613 hr_utility.raise_error;
1614
1615 End If;
1616 ----Bug# 3562063
1617
1618 If l_premium_pay_ind='K' and l_occ_code NOT IN ('0602','0603','0610','0680','0681','0682','0683') THEN
1619
1620 hr_utility.set_message(8301,'GHR_38859_OCC_CD_PREM_PAY_IND');
1621 hr_utility.raise_error;
1622
1623 End If;
1624
1625 If l_premium_pay_ind='X' and l_occ_code NOT IN ('0180','0602','0620','0631','0633','0651','0660','0680') THEN
1626
1627 hr_utility.set_message(8301, 'GHR_38860_OCC_CD_PREM_PAY_IND');
1628 hr_utility.raise_error;
1629
1630 End If;
1631
1632 ghr_api.retrieve_element_entry_value
1633 (P_ELEMENT_NAME => 'Availability Pay',
1634 P_INPUT_VALUE_NAME => 'Amount',
1635 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1636 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1637 P_VALUE => l_ap_amount,
1638 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1639 );
1640 hr_utility.set_location('AP Amount '||l_ap_amount,7);
1641
1642 ghr_api.retrieve_element_entry_value
1643 (P_ELEMENT_NAME => 'AUO',
1644 P_INPUT_VALUE_NAME => 'Amount',
1645 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1646 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1647 P_VALUE => l_auo_amount,
1648 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
1649 );
1650 hr_utility.set_location('AUO Amount '||l_auo_amount,7);
1651 -- Start of bug 4016362
1652 /********* Added Cursor BG_rec to get business group id
1653 to be passed to get_element_details procedure *************/
1654
1655 For BG_rec in Cur_BG(p_pa_request_rec.employee_assignment_id,p_pa_request_rec.effective_date)
1656 Loop
1657 ll_bg_id := BG_rec.bg;
1658 Exit;
1659 End Loop;
1660 -- End of Bug 4016362
1661
1662 ghr_per_sum.get_element_details
1663 (P_ELEMENT_NAME => 'Hazard Pay',
1664 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
1665 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1666 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1667 P_VALUE => l_hz_ind,
1668 P_EFFECTIVE_START_DATE => l_hz_eff_start_date,
1669 P_BUSINESS_GROUP_ID => ll_bg_id -- Bug 4016362
1670 );
1671
1672 ghr_per_sum.get_element_details
1673 (P_ELEMENT_NAME => 'EDP Pay',
1674 P_INPUT_VALUE_NAME => 'Premium Pay Ind',
1675 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
1676 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
1677 P_VALUE => l_edp_ind,
1678 P_EFFECTIVE_START_DATE => l_edp_eff_start_date,
1679 P_BUSINESS_GROUP_ID => ll_bg_id -- Bug 4016362
1680 );
1681
1682
1683
1684
1685
1686 IF ( l_auo_amount is NOT NULL )
1687 or ( l_ap_amount IS NOT NULL )
1688 or ( l_edp_eff_start_date IS NOT NULL )
1689 or ( l_hz_eff_start_date IS NOT NULL ) THEN
1690 hr_utility.set_message(8301,'GHR_38858_PREM_PAY_OTH_PAY');
1691 hr_utility.raise_error;
1692 END IF;
1693
1694 --Check whether the elements AUO, AP, Hazard and EDP pay are existing in Future.
1695
1696 get_element_details_future(p_element_name => 'AUO'
1697 ,p_input_value_name => 'Amount'
1698 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1699 ,p_effective_date => p_pa_request_rec.effective_date
1700 ,p_value => l_auo_amount
1701 ,p_effective_start_date => l_eff_start_date);
1702
1703 hr_utility.set_location('AUO Amount '||l_auo_amount,8);
1704 get_element_details_future(p_element_name => 'Availability Pay'
1705 ,p_input_value_name => 'Amount'
1706 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1707 ,p_effective_date => p_pa_request_rec.effective_date
1708 ,p_value => l_ap_amount
1709 ,p_effective_start_date => l_eff_start_date);
1710
1711 hr_utility.set_location('AP Amount '||l_ap_amount,8);
1712 get_element_details_future(p_element_name => 'Hazard Pay'
1713 ,p_input_value_name => 'Premium Pay Ind'
1714 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1715 ,p_effective_date => p_pa_request_rec.effective_date
1716 ,p_value => l_hz_ind
1717 ,p_effective_start_date => l_hz_eff_start_date);
1718
1719 hr_utility.set_location('Hz Amount '||l_hz_ind,8);
1720 get_element_details_future(p_element_name => 'EDP Pay'
1721 ,p_input_value_name => 'Premium Pay Ind'
1722 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1723 ,p_effective_date => p_pa_request_rec.effective_date
1724 ,p_value => l_edp_ind
1725 ,p_effective_start_date => l_edp_eff_start_date);
1726 hr_utility.set_location('EDP Pay amount'||l_edp_ind,8);
1727
1728 IF ( l_auo_amount is NOT NULL )
1729 or ( l_ap_amount IS NOT NULL )
1730 or ( l_hz_eff_start_date IS NOT NULL )
1731 or ( l_edp_eff_start_date IS NOT NULL ) THEN
1732 hr_utility.set_message(8301,'GHR_38865_PREM_PAY_OTH_PAY_FUT');
1733 hr_utility.raise_error;
1734 END IF;
1735
1736
1737 --To check whether nre date and at least one special pay amount is entered or not
1738
1739 l_null_list := null;
1740
1741 If l_mddds_special_pay_amount is NULL or l_mddds_special_pay_amount = 0 THEN
1742
1743 ghr_api.retrieve_element_entry_value
1744 (p_element_name => 'MDDDS Special Pay',
1745 p_input_value_name => 'Amount',
1746 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1747 p_effective_date => p_pa_request_rec.effective_date,
1748 p_value => l_mddds_pay_amount_old,
1749 p_multiple_error_flag => l_multiple_error_flag
1750 );
1751 hr_utility.set_location('Amount l_mddds_pay_amount_old '|| l_mddds_pay_amount_old,5);
1752 IF l_mddds_pay_amount_old IS NULL THEN
1753 l_null_list := 'MD/DDS Special Pay Amount';
1754 END IF;
1755
1756 END IF;
1757
1758 If l_mddds_specia_pay_nte_date is NULL THEN
1759
1760 ghr_api.retrieve_element_entry_value
1761 (p_element_name => 'MDDDS Special Pay',
1762 p_input_value_name => 'MDDDS Special Pay NTE Date',
1763 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1764 p_effective_date => p_pa_request_rec.effective_date,
1765 p_value => l_mddds_pay_nte_date_old,
1766 p_multiple_error_flag => l_multiple_error_flag
1767 );
1768 hr_utility.set_location('MDDDS Special Pay NTE Date old '|| l_mddds_pay_nte_date_old,6);
1769
1770 IF l_mddds_pay_nte_date_old IS NULL THEN
1771 l_null_list := l_null_list||l_new_line||'MD/DDS Special Pay NTE Date';
1772 END IF;
1773
1774 END IF;
1775
1776 IF l_null_list IS NOT NULL THEN
1777
1778 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
1779 fnd_message.set_token('REQUIRED_LIST',l_null_list);
1780 hr_utility.raise_error;
1781
1782 End If;
1783
1784 IF l_mddds_specia_pay_nte_date IS NOT NULL
1785 and l_mddds_specia_pay_nte_date
1786 < p_pa_request_rec.effective_date THEN
1787 hr_utility.set_message(8301,'GHR_38853_NTE_DATE_EFF_DATE');
1788 fnd_message.set_token('PROC','MD/DDS Special Pay ');--bug# 14728585
1789 hr_utility.raise_error;
1790 END IF;
1791
1792 END IF; -- End of If .. 850.
1793 --Begin Bug# 8653508
1794
1795 IF (p_pa_request_rec.noa_family_code = 'GHR_DETAIL') THEN
1796 hr_utility.set_location(' Detail Action Validations ',101);
1797 FOR cur_detail_info_rec IN cur_detail_info LOOP
1798 l_detail_type := cur_detail_info_rec.rei_information3;
1799 l_unclassified_duty_ind := cur_detail_info_rec.rei_information4;
1800 l_detail_position_id := cur_detail_info_rec.rei_information5; --Bug# 11781477
1801 END LOOP;
1802 IF l_detail_type IS NULL OR l_unclassified_duty_ind IS NULL THEN
1803 l_null_list := null;
1804 IF l_detail_type IS NULL THEN
1805 l_null_list := l_null_list||l_new_line||'Detail Type';
1806 END IF;
1807 IF l_unclassified_duty_ind IS NULL THEN
1808 l_null_list := l_null_list||l_new_line||'Unclassified Duties Indicator';--Bug# 9578885
1809 END IF;
1810 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
1811 fnd_message.set_token('REQUIRED_LIST',l_null_list);
1812 hr_utility.raise_error;
1813 END IF;
1814 --Begin Bug# 11781477
1815 FOR p_get_pos_avl_status IN c_get_pos_avl_status(l_detail_position_id,p_pa_request_rec.effective_date) LOOP
1816 IF p_get_pos_avl_status.status <> 'Active' THEN
1817 hr_utility.set_message(8301,'GHR_38193_DETAIL_POSAVL_CHK');
1818 FND_MESSAGE.SET_TOKEN('HIRING_STATUS',p_get_pos_avl_status.status);
1819 hr_utility.raise_error;
1820 END IF;
1821 END LOOP;
1822 --End Bug# 11781477
1823 --Begin Bug# 9899054, 9898968
1824 IF p_pa_request_rec.first_noa_code IN ('730','930','731','931') THEN
1825 IF p_pa_request_rec.first_noa_code IN ('730','930') THEN
1826 --Begin Bug# 9898968
1827 IF l_session.noa_id_correct IS NULL THEN
1828 FOR l_detail_chk IN c_detail_chk(p_pa_request_rec.effective_date) LOOP
1829 hr_utility.set_message(8301,'GHR_38286_DETAIL_ASG_CHK');
1830 hr_utility.raise_error;
1831 END LOOP;
1832 END IF;
1833 --End Bug# 9898968
1834 END IF;
1835 IF l_session.noa_id_correct IS NOT NULL THEN
1836 FOR c_detail_second_nte_date_rec in c_detail_second_nte_date LOOP
1837 l_detail_nte_date := fnd_date.canonical_to_date(c_detail_second_nte_date_rec.second_noa_information1);
1838 END LOOP;
1839
1840 ELSE
1841 FOR c_detail_first_nte_date_rec in c_detail_first_nte_date LOOP
1842 l_detail_nte_date := fnd_date.canonical_to_date(c_detail_first_nte_date_rec.first_noa_information1);
1843 END LOOP;
1844 END IF;
1845 IF l_detail_nte_date <= p_pa_request_rec.effective_date THEN
1846 hr_utility.set_message(8301,'GHR_37754_DETAIL_POS_CHK');
1847 FND_MESSAGE.SET_TOKEN('DETAIL_GEN_MSG','Detail NTE Date must not be earlier than the Detail Start Date');--Bug# 9899054
1848 hr_utility.raise_error;
1849 END IF;
1850 --Begin Bug# 11776559
1851 IF l_detail_nte_date IS NOT NULL THEN
1852 ghr_history_fetch.fetch_asgei
1853 (p_assignment_id => p_pa_request_rec.employee_assignment_id,
1854 p_information_type => 'GHR_US_ASG_DET_INFO',
1855 p_date_effective => p_pa_request_rec.effective_date,
1856 p_asg_ei_data => l_asg_extra_info_rec
1857 );
1858 FOR l_detail_chk IN c_detail_chk(l_detail_nte_date) LOOP
1859 IF NVL(l_detail_chk.assignment_extra_info_id,9999999) <> nvl(l_asg_extra_info_rec.assignment_extra_info_id,9999999) THEN
1860 hr_utility.set_message(8301,'GHR_38287_DETAIL_NTE_CHK');
1861 hr_utility.raise_error;
1862 END IF;
1863 END LOOP;
1864 END IF;
1865 --End Bug# 11776559
1866 END IF;
1867 --end Bug# 9899054 , 9898968
1868 IF p_pa_request_rec.first_noa_code IN ('930','931','932') AND l_detail_type between 'G' and 'P' THEN
1869 hr_utility.set_message(8301,'GHR_37754_DETAIL_POS_CHK');
1870 fnd_message.set_token('DETAIL_GEN_MSG','IF NOA is 9xx then Detail Type must be between A and F');
1871 hr_utility.raise_error;
1872 ELSIF p_pa_request_rec.first_noa_code IN ('730','731','732') AND l_detail_type between 'A' and 'F' THEN
1873 hr_utility.set_message(8301,'GHR_37754_DETAIL_POS_CHK');
1874 fnd_message.set_token('DETAIL_GEN_MSG','IF NOA is 7xx then Detail Type must be between G and P');
1875 hr_utility.raise_error;
1876 END IF;
1877 --check is there any open Detail Assignment record exists
1878 FOR cur_detail_exists_rec IN cur_detail_exists(p_pa_request_rec.employee_assignment_id,
1879 nvl(p_pa_request_rec.effective_date,trunc(sysdate))) LOOP
1880 --Bug# 9625781 added correct and 730, 930 condition
1881 IF cur_detail_exists_rec.user_status IS NOT NULL AND
1882 p_pa_request_rec.first_noa_code IN ('730','930') AND l_session.noa_id_correct IS NULL THEN
1883 hr_utility.set_message(8301,'GHR_37753_DETAIL_INFO_CHK');
1884 hr_utility.raise_error;
1885 END IF;
1886 END LOOP;
1887 END IF;
1888 --End Bug# 8653508
1889
1890 --Pradeep. New Business Rules for Title 38
1891 --Bug# 3562063
1892 IF p_pa_request_rec.first_noa_code = '855'
1893 OR ( p_pa_request_rec.first_noa_code='002' and p_pa_request_rec.second_noa_code ='855') THEN
1894
1895 FOR cur_premium_pay_ind_rec IN cur_premium_pay_ind
1896 LOOP
1897 l_premium_pay_ind := cur_premium_pay_ind_rec.premium_pay_ind;
1898 END LOOP;
1899
1900 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,3);
1901 IF l_premium_pay_ind IS NULL THEN
1902 ghr_api.retrieve_element_entry_value
1903 (p_element_name => 'Premium Pay',
1904 p_input_value_name => 'Premium Pay Ind',
1905 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1906 p_effective_date => p_pa_request_rec.effective_date,
1907 p_value => l_premium_pay_ind,
1908 p_multiple_error_flag => l_multiple_error_flag
1909 );
1910
1911 END IF;
1912
1913 IF ( l_premium_pay_ind ) IS NULL
1914 OR ( l_premium_pay_ind <> 'K' ) THEN
1915 hr_utility.set_message(8301,'GHR_38872_855_PREM_PAY');
1916 hr_utility.raise_error;
1917 END IF;
1918
1919 l_occ_code := p_pa_request_rec.from_occ_code;
1920
1921 If ( l_occ_code <> '0610' ) THEN
1922
1923 hr_utility.set_message(8301,'GHR_38873_855_OCC_SERIES');
1924 hr_utility.raise_error;
1925
1926 End If;
1927 --Bug# 3562063
1928
1929 END IF; --End of If ... 855
1930
1931 --Pradeep.
1932 -- Bug 3528461
1933 --Not only for 800 but also for any other noac, it should check the validation between Premium Pay and OCC Code.
1934 -- IF p_pa_request_rec.first_noa_code = '800' THEN
1935 --Bug#3579579
1936 FOR cur_premium_pay_rec IN cur_premium_pay
1937 LOOP
1938 l_premium_pay_ind := cur_premium_pay_rec.premium_pay_ind;
1939 END LOOP;
1940 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,3);
1941
1942 IF l_premium_pay_ind IS NULL THEN
1943 --Bug#3579579
1944 ghr_api.retrieve_element_entry_value
1945 (p_element_name => 'Premium Pay',
1946 p_input_value_name => 'Premium Pay Ind',
1947 p_assignment_id => p_pa_request_rec.employee_assignment_id,
1948 p_effective_date => p_pa_request_rec.effective_date,
1949 p_value => l_premium_pay_ind,
1950 p_multiple_error_flag => l_multiple_error_flag
1951 );
1952 hr_utility.set_location('Premium Pay Ind '|| l_premium_pay_ind,4);
1953 END IF;
1954
1955 l_occ_code := p_pa_request_rec.to_occ_code;
1956 hr_utility.set_location('Occ Code '|| l_occ_code,5);
1957 IF l_occ_code IS NULL THEN
1958 l_occ_code := p_pa_request_rec.from_occ_code;
1959 END IF;
1960
1961 If l_premium_pay_ind='K' and l_occ_code NOT IN ('0602','0603','0610','0680','0681','0682','0683') THEN
1962
1963 hr_utility.set_message(8301,'GHR_38859_OCC_CD_PREM_PAY_IND');
1964 hr_utility.raise_error;
1965
1966 End If;
1967
1968 If l_premium_pay_ind='X' and l_occ_code NOT IN ('0180','0602','0620','0631','0633','0651','0660','0680') THEN
1969
1970 hr_utility.set_message(8301, 'GHR_38860_OCC_CD_PREM_PAY_IND');
1971 hr_utility.raise_error;
1972
1973 End If;
1974
1975 get_element_details_future(p_element_name => 'Premium Pay'
1976 ,p_input_value_name => 'Premium Pay Ind'
1977 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1978 ,p_effective_date => p_pa_request_rec.effective_date
1979 ,p_value => l_premium_pay_ind
1980 ,p_effective_start_date => l_eff_start_date);
1981
1982 hr_utility.set_location('Premium Pay Ind future'|| l_premium_pay_ind,4);
1983
1984 If l_premium_pay_ind='K' and l_occ_code NOT IN ('0602','0603','0610','0680','0681','0682','0683') THEN
1985
1986 hr_utility.set_message(8301,'GHR_38859_OCC_CD_PREM_PAY_IND');
1987 hr_utility.raise_error;
1988
1989 End If;
1990
1991 If l_premium_pay_ind='X' and l_occ_code NOT IN ('0180','0602','0620','0631','0633','0651','0660','0680') THEN
1992
1993 hr_utility.set_message(8301, 'GHR_38860_OCC_CD_PREM_PAY_IND');
1994 hr_utility.raise_error;
1995
1996 End If;
1997
1998
1999 -- END IF;
2000
2001 IF p_pa_request_rec.first_noa_code = '819'
2002 and l_session.noa_id_correct is NULL THEN
2003 ghr_api.retrieve_element_entry_value
2004 (P_ELEMENT_NAME => 'Availability Pay',
2005 P_INPUT_VALUE_NAME => 'Amount',
2006 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
2007 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
2008 P_VALUE => l_ap_amount,
2009 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
2010 );
2011 hr_utility.set_location('AP Amount '||l_ap_amount,7);
2012 IF l_ap_amount is NULL and
2013 (p_pa_request_rec.to_other_pay_amount is null or
2014 p_pa_request_rec.to_other_pay_amount = 0 ) THEN
2015 hr_utility.set_message(8301,'GHR_38589_NULL_OTHER_PAY');
2016 hr_utility.raise_error;
2017 END IF;
2018
2019 --Pradeep
2020 --Title 38 Employess should not get Avaialability Pay.
2021 ghr_api.retrieve_element_entry_value
2022 (p_element_name => 'Premium Pay',
2023 p_input_value_name => 'Premium Pay Ind',
2024 p_assignment_id => p_pa_request_rec.employee_assignment_id,
2025 p_effective_date => p_pa_request_rec.effective_date,
2026 p_value => l_premium_pay_ind,
2027 p_multiple_error_flag => l_multiple_error_flag
2028 );
2029 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,7);
2030
2031 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
2032
2033 hr_utility.set_message(8301,'GHR_38857_OTH_PAY_PREM_PAY');
2034 hr_utility.raise_error;
2035
2036 END IF;
2037
2038 --To check if premium pay element is existing in the future
2039 get_element_details_future(p_element_name => 'Premium Pay'
2040 ,p_input_value_name => 'Premium Pay Ind'
2041 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
2042 ,p_effective_date => p_pa_request_rec.effective_date
2043 ,p_value => l_premium_pay_ind
2044 ,p_effective_start_date => l_eff_start_date);
2045
2046 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,8);
2047
2048 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
2049
2050 hr_utility.set_message(8301,'GHR_38866_OTH_PAY_PREM_PAY_FUT');
2051 hr_utility.raise_error;
2052
2053 END IF;
2054
2055 END IF;
2056
2057 IF p_pa_request_rec.first_noa_code = '818'
2058 and l_session.noa_id_correct is NULL THEN
2059 ghr_api.retrieve_element_entry_value
2060 (P_ELEMENT_NAME => 'AUO',
2061 P_INPUT_VALUE_NAME => 'Amount',
2062 P_ASSIGNMENT_ID => p_pa_request_rec.employee_assignment_id,
2063 P_EFFECTIVE_DATE => p_pa_request_rec.effective_date,
2064 P_VALUE => l_auo_amount,
2065 P_MULTIPLE_ERROR_FLAG => l_multiple_error_flag
2066 );
2067 hr_utility.set_location('AUO Amount '||l_auo_amount,7);
2068 IF l_auo_amount is NULL and
2069 (p_pa_request_rec.to_other_pay_amount is null or
2070 p_pa_request_rec.to_other_pay_amount = 0 ) THEN
2071 hr_utility.set_message(8301,'GHR_38589_NULL_OTHER_PAY');
2072 hr_utility.raise_error;
2073 END IF;
2074
2075 --Pradeep
2076 --Title 38 Employess should not get AUO.
2077 ghr_api.retrieve_element_entry_value
2078 (p_element_name => 'Premium Pay',
2079 p_input_value_name => 'Premium Pay Ind',
2080 p_assignment_id => p_pa_request_rec.employee_assignment_id,
2081 p_effective_date => p_pa_request_rec.effective_date,
2082 p_value => l_premium_pay_ind,
2083 p_multiple_error_flag => l_multiple_error_flag
2084 );
2085 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,7);
2086
2087 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
2088
2089 hr_utility.set_message(8301,'GHR_38857_OTH_PAY_PREM_PAY');
2090 hr_utility.raise_error;
2091
2092 END IF;
2093
2094 --To check if premium pay element is existing in the future
2095 get_element_details_future(p_element_name => 'Premium Pay'
2096 ,p_input_value_name => 'Premium Pay Ind'
2097 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
2098 ,p_effective_date => p_pa_request_rec.effective_date
2099 ,p_value => l_premium_pay_ind
2100 ,p_effective_start_date => l_eff_start_date);
2101
2102 hr_utility.set_location('Premium Pay Ind '|| l_prem_pay,8);
2103
2104 IF l_premium_pay_ind IN ( 'K','L','M','X' ) THEN
2105
2106 hr_utility.set_message(8301,'GHR_38866_OTH_PAY_PREM_PAY_FUT');
2107 hr_utility.raise_error;
2108
2109 END IF;
2110
2111 END IF;
2112
2113 IF p_pa_request_rec.first_noa_code = '855'
2114 OR ( p_pa_request_rec.first_noa_code='002' and p_pa_request_rec.second_noa_code ='855') THEN
2115
2116 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F','U','V') THEN
2117 begin
2118 hr_utility.set_location('Check 855 Retained Grade ...get open pay range ' ,20);
2119
2120 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_pa_request_rec.person_id
2121 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)));
2122
2123 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F') THEN
2124 if l_retained_grade.temp_step is not null then
2125 l_to_step_or_rate := l_retained_grade.temp_step;
2126 else
2127 l_to_step_or_rate := l_retained_grade.step_or_rate;
2128 end if;
2129 ELSE
2130 l_to_step_or_rate := l_retained_grade.step_or_rate;
2131 END IF;
2132
2133 hr_utility.set_location(' check 855 Retained to step ' || l_to_step_or_rate,22);
2134
2135 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_pa_request_rec.person_id
2136 ,(p_pa_request_rec.effective_date - 1));
2137
2138 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F') THEN
2139 if l_retained_grade.temp_step is not null then
2140 l_from_step_or_rate := l_retained_grade.temp_step;
2141 else
2142 l_from_step_or_rate := l_retained_grade.step_or_rate;
2143 end if;
2144 ELSE
2145 l_from_step_or_rate := l_retained_grade.step_or_rate;
2146 END IF;
2147
2148 hr_utility.set_location(' check 855 Retained from step ' || l_from_step_or_rate,24);
2149
2150 exception
2151 when others then
2152 hr_utility.set_location('Retained Exception raised ' || sqlerrm(sqlcode),25);
2153 hr_utility.set_message(8301,'GHR_38255_MISSING_RETAINED_DET');
2154 hr_utility.raise_error;
2155 end;
2156
2157 IF ABS(l_to_step_or_rate - l_from_step_or_rate) > 2 THEN
2158 hr_utility.set_message(8301,'GHR_38852_TO_STEP_OR_RATE');
2159 hr_utility.raise_error;
2160 END IF;
2161
2162 END IF;
2163 END IF;
2164
2165
2166 --- End Bug 1551311
2167 --
2168 --
2169 -- check whether more than one other pay is changed for NOA 810
2170 --
2171 if p_pa_request_rec.first_noa_code = '810' then
2172 l_for_810_count := 0;
2173 ghr_api.retrieve_element_entry_value
2174 (p_element_name => 'Retention Allowance',
2175 p_input_value_name => 'Amount',
2176 p_assignment_id => p_pa_request_rec.employee_assignment_id,
2177 p_effective_date => p_pa_request_rec.effective_date,
2178 p_value => l_amount,
2179 p_multiple_error_flag => l_multiple_error_flag
2180 );
2181 if NVL(p_pa_request_rec.to_retention_allowance,0) <> NVL(l_amount,0) then
2182 l_for_810_count := l_for_810_count + 1;
2183 end if;
2184 ghr_api.retrieve_element_entry_value
2185 (p_element_name => 'Supervisory Differential',
2186 p_input_value_name => 'Amount',
2187 p_assignment_id => p_pa_request_rec.employee_assignment_id,
2188 p_effective_date => p_pa_request_rec.effective_date,
2189 p_value => l_amount,
2190 p_multiple_error_flag => l_multiple_error_flag
2191 );
2192 if NVL(p_pa_request_rec.to_supervisory_differential,0) <> NVL(l_amount,0) then
2193 l_for_810_count := l_for_810_count + 1;
2194 end if;
2195 ghr_api.retrieve_element_entry_value
2196 (p_element_name => 'Staffing Differential',
2197 p_input_value_name => 'Amount',
2198 p_assignment_id => p_pa_request_rec.employee_assignment_id,
2199 p_effective_date => p_pa_request_rec.effective_date,
2200 p_value => l_amount,
2201 p_multiple_error_flag => l_multiple_error_flag
2202 );
2203 if NVL(p_pa_request_rec.to_staffing_differential,0) <> NVL(l_amount,0) then
2204 l_for_810_count := l_for_810_count + 1;
2205 end if;
2206 --
2207 if l_for_810_count > 1 then
2208 hr_utility.set_message(8301,'GHR_ONE_OP_UPDATE_ONLY');
2209 hr_utility.raise_error;
2210 end if;
2211 end if;
2212 hr_utility.set_location('GHRVALID-Before LAC Validation',3);
2213 -- Bug # 941255 --Venkat-- Check that LACs are Valid when Update HR
2214 -- in progress and give a Error message if NOAC/LAC combination is invalid
2215 --
2216 -- Check if first_action_la_code1 is valid
2217 --
2218 l_exists := false;
2219 If p_pa_request_rec.first_action_la_code1 is not null then
2220 for la_code in c_first_la_code1 loop
2221 l_exists := true;
2222 exit;
2223 end loop;
2224 --
2225 if not l_exists then
2226 hr_utility.set_message(8301, 'GHR_38105_INV_FIRST_LA_CODE1');
2227 hr_utility.raise_error;
2228 end if;
2229 end if;
2230
2231 l_exists := false;
2232 -- Check if first_action_la_code2 is valid
2233 --
2234 If p_pa_request_rec.first_action_la_code2 is not null then
2235 for la_code in c_first_la_code2 loop
2236 l_exists := true;
2237 exit;
2238 end loop;
2239 --
2240 if not l_exists then
2241 hr_utility.set_message(8301, 'GHR_38106_INV_FIRST_LA_CODE2');
2242 hr_utility.raise_error;
2243 end if;
2244 end if;
2245 l_exists := false;
2246 --
2247 -- Check if second_action_la_code1 is valid
2248 --
2249 If p_pa_request_rec.second_action_la_code1 is not null then
2250 for la_code in c_second_la_code1 loop
2251 l_exists := true;
2252 exit;
2253 end loop;
2254 --
2255 if not l_exists then
2256 hr_utility.set_message(8301, 'GHR_38107_INV_SECOND_LA_CODE1');
2257 hr_utility.raise_error;
2258 end if;
2259 end if;
2260 l_exists := false;
2261 --
2262 -- Check if second_action_la_code2 is valid
2263 --
2264 If p_pa_request_rec.second_action_la_code2 is not null then
2265 for la_code in c_second_la_code2 loop
2266 l_exists := true;
2267 exit;
2268 end loop;
2269 --
2270 if not l_exists then
2271 hr_utility.set_message(8301, 'GHR_38108_INV_SECOND_LA_CODE2');
2272 hr_utility.raise_error;
2273 end if;
2274 end if;
2275 hr_utility.set_location('GHRVALID-After LAC Validation',4);
2276 --
2277 --
2278 -- Validate if the open pay range basic pay is entered by user.
2279 --
2280 --
2281 IF p_pa_request_rec.noa_family_code not in ('AWARD'
2282 ,'GHR_INCENTIVE'
2283 ,'NON_PAY_DUTY_STATUS'
2284 ,'POS_ABOLISH'
2285 ,'POS_ESTABLISH'
2286 ,'POS_REVIEW'
2287 ,'RECRUIT_FILL'
2288 ,'SEPARATION'
2289 ,'GHR_STUDENT_LOAN') THEN
2290 IF p_pa_request_rec.to_position_id is not null THEN
2291 IF ghr_pay_calc.get_open_pay_range
2292 (p_pa_request_rec.to_position_id
2293 ,p_pa_request_rec.person_id
2294 ,p_pa_request_rec.pay_rate_determinant
2295 ,p_pa_request_rec.pa_request_id
2296 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) ) THEN
2297
2298 hr_utility.set_location('GHRVALID-Open Pay Range get table_id' ,5);
2299
2300
2301
2302 IF p_pa_request_rec.pay_rate_determinant IN ('A','B','E','F','U','V') THEN
2303 begin
2304 hr_utility.set_location('Retained Grade ...get open pay range ' ,20);
2305
2306 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_pa_request_rec.person_id
2307 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)));
2308 l_user_table_id := l_retained_grade.user_table_id;
2309 l_pay_plan := l_retained_grade.pay_plan;
2310 l_grade_or_level := l_retained_grade.grade_or_level;
2311 hr_utility.set_location(' Retained user table id ' || to_char(l_user_table_id),22);
2312 exception
2313 when others then
2314 hr_utility.set_location('Retained Exception raised ' || sqlerrm(sqlcode),25);
2315 hr_utility.set_message(8301,'GHR_38255_MISSING_RETAINED_DET');
2316 hr_utility.raise_error;
2317 end;
2318
2319 ELSE
2320
2321
2322 l_user_table_id := ghr_pay_calc.get_user_table_id (p_pa_request_rec.to_position_id
2323 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) );
2324 --Start of the Bug 3604377
2325 -- Retive the Grade info from the POI history table
2326 ghr_history_fetch.fetch_positionei(
2327 p_position_id => p_pa_request_rec.to_position_id,
2328 p_information_type => 'GHR_US_POS_VALID_GRADE',
2329 p_date_effective => NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)),
2330 p_pos_ei_data => l_pos_ei_grade_data);
2331 --Based on Grade id Get the Pay Plan.
2332 IF l_pos_ei_grade_data.poei_information3 IS NOT NULL THEN
2333 FOR cur_grd_rec IN cur_grd(l_pos_ei_grade_data.poei_information3) LOOP
2334 l_pay_plan := NVL(cur_grd_rec.pay_plan,l_pay_plan);
2335 l_grade_or_level := NVL(cur_grd_rec.grade_or_level,l_grade_or_level);
2336 END LOOP;
2337 END IF;
2338 --End of the Bug 3604377
2339 /*
2340 l_pay_plan := p_pa_request_rec.to_pay_plan;
2341 l_grade_or_level := p_pa_request_rec.to_grade_or_level;
2342 */
2343
2344 END IF;
2345
2346
2347
2348 --hr_utility.set_location('GHRVALID-l_user_table_id ' || to_char(l_user_table_id) ,5);
2349 --hr_utility.set_location('GHRVALID-l_pay_plan ' || to_char(l_pay_plan) ,5);
2350 --hr_utility.set_location('GHRVALID-l_grade_or_level ' || to_char(l_grade_or_level) ,5);
2351
2352 hr_utility.set_location('GHRVALID-l_user_table_id ' || to_char(l_user_table_id) ,5);
2353 hr_utility.set_location('GHRVALID-Open Pay Range get values' ,5);
2354
2355 ghr_pay_calc.get_open_pay_table_values
2356 (p_user_table_id => l_user_table_id
2357 ,p_pay_plan => l_pay_plan
2358 ,p_grade_or_level => l_grade_or_level
2359 ,p_effective_date => NVL(p_pa_request_rec.effective_date,TRUNC(sysdate))
2360 ,p_row_high => l_row_high
2361 ,p_row_low => l_row_low );
2362
2363
2364 hr_utility.set_location('GHRVALID-Open Pay Ranges found',5);
2365
2366 IF l_row_low is null and l_row_high is null then
2367 ---Raise Error
2368 hr_utility.set_message(8301, 'GHR_38715_OPEN_PAY_RANGE_NF');
2369 hr_utility.raise_error;
2370 END IF;
2371
2372 -- 5482191 Start
2373 --Bug 5658361 IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
2374 --Bug 5658361 p_pa_request_rec.first_noa_code in ('891','892','893','894','890','896','897') AND
2375 --Bug# 6073655 Brought out this code from the l_psi<>'00' condition
2376 IF p_pa_request_rec.first_noa_code = '897'
2377 AND p_pa_request_rec.to_basic_pay >= p_pa_request_rec.from_basic_pay THEN
2378 hr_utility.set_message(8301, 'GHR_38513_AFHR_VAL_CHK');
2379 hr_utility.raise_error;
2380 END IF;
2381 --Bug# 6073655
2382 IF l_psi <> '00' THEN
2383 IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('30/04/2006','DD/MM/YYYY') THEN --Bug 5658361
2384 IF p_pa_request_rec.pay_rate_determinant IN ('4','R') AND
2385 l_row_high >= p_pa_request_rec.to_basic_pay THEN
2386 hr_utility.set_message(8301, 'GHR_38506_AFHR_PRD_CHK');
2387 hr_utility.raise_error;
2388 ELSIF p_pa_request_rec.pay_rate_determinant = 'T' AND
2389 l_row_low <= p_pa_request_rec.to_basic_pay THEN
2390 hr_utility.set_message(8301, 'GHR_38508_AFHR_PRD_CHK');
2391 hr_utility.raise_error;
2392 ELSIF p_pa_request_rec.pay_rate_determinant = '0' AND
2393 (l_row_low > p_pa_request_rec.to_basic_pay OR l_row_high < p_pa_request_rec.to_basic_pay) THEN
2394 hr_utility.set_message(8301, 'GHR_38507_AFHR_PAY_CHK');
2395 hr_utility.raise_error;
2396 END IF;
2397 IF p_pa_request_rec.to_step_or_rate <> '00' THEN --Bug 5657572
2398 hr_utility.set_message(8301, 'GHR_38474_AFH_TO_STEP_OR_RATE');
2399 hr_utility.raise_error;
2400 END IF;
2401 END IF;
2402 ELSE
2403 --6489042 No validation is needed for PRD 2
2404 --Bug# 7557159, added PRD D, basic pay can enter more than the limit
2405 -- Pay cap is different for PRD D. Eg pay plan ES.
2406 IF p_pa_request_rec.pay_rate_determinant NOT IN ('2','D') then
2407 IF l_row_low <= p_pa_request_rec.to_basic_pay AND
2408 l_row_high >= p_pa_request_rec.to_basic_pay then
2409 null;
2410 ELSE
2411 ---Raise Error
2412 hr_utility.set_message(8301, 'GHR_38714_OPEN_PAY_RANGE_VAL');
2413 hr_utility.set_message_token('MIN',to_char(l_row_low));
2414 hr_utility.set_message_token('MAX',to_char(l_row_high));
2415 hr_utility.raise_error;
2416 END IF;
2417 END IF;
2418 END IF;
2419 -- 5482191 End
2420
2421 END IF;
2422
2423 -- Bug 5657572
2424 /**** As per GPPA update 46 req. for 890 any employee is fine
2425 IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
2426 p_pa_request_rec.first_noa_code = '890' THEN
2427 IF l_psi = '00' THEN
2428 hr_utility.set_message(8301, 'GHR_38462_AFHR_POS_SEL');
2429 hr_utility.raise_error;
2430 END IF;
2431 END IF;
2432 *******/
2433 END IF;
2434 END IF;
2435
2436 --Bug 5657733, 5662254
2437 --IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
2438 --Removed 894 NOAC for rating of record check as part of GPPA U46 Req.
2439 IF p_pa_request_rec.first_noa_code in ('891','892') AND l_psi <> '00' THEN
2440 IF p_pa_request_rec.first_noa_code = '891' AND
2441 nvl(p_perf_appraisal_type.rating_rec_level,'X') NOT IN ('3','4','5') THEN
2442 hr_utility.set_message(8301, 'GHR_38457_AFHR_NOA_CHK');
2443 hr_utility.raise_error;
2444 END IF;
2445 IF p_pa_request_rec.first_noa_code = '892' AND
2446 nvl(p_perf_appraisal_type.rating_rec_level,'X') IN ('1','2') THEN
2447 hr_utility.set_message(8301, 'GHR_38510_AFHR_NOA_CHK');
2448 hr_utility.raise_error;
2449 END IF;
2450 /****GPPA U46
2451 IF p_pa_request_rec.first_noa_code = '894' AND
2452 nvl(p_perf_appraisal_type.rating_rec_level,'X') = '1' THEN
2453 hr_utility.set_message(8301, 'GHR_38511_AFHR_NOA_CHK');
2454 hr_utility.raise_error;
2455 END IF;
2456 *****GPPA ****/
2457 END IF;
2458
2459 -- For 703 actions give error message if the Temporary Promotion Step value
2460 -- is null
2461 -- and Employee has a valid retained grade record
2462
2463 IF p_pa_request_rec.first_noa_code in ('703') THEN
2464 BEGIN
2465 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details
2466 (p_pa_request_rec.person_id
2467 ,NVL(p_pa_request_rec.effective_date,TRUNC(sysdate))
2468 ,p_pa_request_rec.pa_request_id);
2469 EXCEPTION
2470 WHEN OTHERS THEN
2471 NULL;
2472 END;
2473 IF l_retained_grade.person_extra_info_id is not null and
2474 l_retained_grade.temp_step is null THEN
2475 hr_utility.set_message(8301,'GHR_38824_RG_TPS_REQUIRED');
2476 hr_utility.raise_error;
2477 END IF;
2478 END IF;
2479 --Bug 5527363
2480 IF p_pa_request_rec.noa_family_code = 'CONV_APP' AND p_within_grade_incr.p_date_wgi_due IS NOT NULL
2481 AND p_within_grade_incr.p_last_equi_incr IS NULL THEN
2482 hr_utility.set_message(8301,'GHR_37740_CONV_APP_NULL_DLEI');
2483 hr_utility.raise_error;
2484 END IF;
2485
2486 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
2487 IF p_pa_request_rec.first_noa_code IN ('817', '850', '480') OR
2488 (p_pa_request_rec.second_noa_code IN ('817', '850', '480') AND
2489 p_pa_request_rec.first_noa_code IN ('002')) THEN
2490 FOR l_cur_service_oblg_ei IN cur_service_oblg_ei LOOP
2491 l_serv_oblg_type := l_cur_service_oblg_ei.srvc_oblg_type;
2492 l_serv_oblg_start_date := l_cur_service_oblg_ei.srvc_oblg_st_date;
2493 l_serv_oblg_end_date := l_cur_service_oblg_ei.srvc_oblg_end_date;
2494 IF l_serv_oblg_type IS NOT NULL AND
2495 (l_serv_oblg_start_date IS NULL OR l_serv_oblg_end_date IS NULL) THEN
2496 hr_utility.set_message(8301,
2497 'GHR_38454_SRVC_OBLG_TYPE_CHK');
2498 hr_utility.raise_error;
2499 END IF;
2500
2501 IF l_serv_oblg_start_date IS NOT NULL AND
2502 (l_serv_oblg_end_date IS NULL OR l_serv_oblg_type IS NULL) THEN
2503 hr_utility.set_message(8301,
2504 'GHR_38455_SRVC_OBLG_ST_DT_CHK');
2505 hr_utility.raise_error;
2506 END IF;
2507 END LOOP;
2508 END IF;
2509 /* Bug#5132121 Service Obligation for Student Loan and MD/DDS */
2510 --Begin Bug# 12557463
2511 --Bug# 14368933 removed NOAs 280,292,293,781,882
2512 IF (p_pa_request_rec.first_noa_code IN ('280','292','293','781','882') OR SUBSTR(p_pa_request_rec.first_noa_code,1,1) IN ('1','5')) OR
2513 ((p_pa_request_rec.second_noa_code IN ('280','292','293','781','882') OR SUBSTR(p_pa_request_rec.second_noa_code,1,1) IN ('1','5')) AND
2514 p_pa_request_rec.first_noa_code IN ('002')) THEN
2515 hr_utility.set_location('Tsp SCD check '||p_pa_request_rec.first_noa_code , 11);
2516 FOR l_get_tsp_scd IN c_get_tsp_scd LOOP
2517 l_tsp_scd := l_get_tsp_scd.tsp_scd;
2518 END LOOP;
2519 --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR
2520 IF nvl(p_pa_request_rec.retirement_plan,'@') IN ('D','K','L','M','N','P','DR','KR','LR','MR','NR','PR') THEN
2521 IF l_tsp_scd IS NULL THEN
2522 hr_utility.set_message(8301,'GHR_38957_TSP_FERS');
2523 hr_utility.raise_error;
2524 END IF;
2525 END IF;
2526 --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR
2527 IF p_pa_request_rec.retirement_plan IS NOT NULL AND p_pa_request_rec.retirement_plan NOT IN ('D','K','L','M','N','P','DR','KR','LR','MR','NR','PR')
2528 AND l_tsp_scd IS NOT NULL THEN
2529 IF NOT(p_pa_request_rec.retirement_plan = '4' AND NVL(p_pa_request_rec.annuitant_indicator,hr_api.g_varchar2) NOT IN ('2','3','9')) THEN --Bug# 11808857
2530 hr_utility.set_message(8301,'GHR_38392_NON_TSP_FERS');
2531 hr_utility.raise_error;
2532 END IF;
2533 END IF;
2534 END IF;
2535 --End Bug# 12557463
2536 --Begin Bug# 14769681
2537 IF p_pa_request_rec.first_noa_code IN ('803') OR
2538 (p_pa_request_rec.second_noa_code IN ('803') AND p_pa_request_rec.first_noa_code IN ('002')) THEN
2539 hr_utility.set_location('Tsp SCD check for NOA '||p_pa_request_rec.first_noa_code , 11);
2540 FOR l_get_tsp_scd IN c_get_tsp_scd_803 LOOP
2541 l_tsp_scd := l_get_tsp_scd.tsp_scd;
2542 END LOOP;
2543 --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR
2544 IF nvl(p_pa_request_rec.retirement_plan,'@') IN ('D','K','L','M','N','P','DR','KR','LR','MR','NR','PR') THEN
2545 IF l_tsp_scd IS NULL THEN
2546 hr_utility.set_message(8301,'GHR_38957_TSP_FERS');
2547 hr_utility.raise_error;
2548 END IF;
2549 END IF;
2550 --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR
2551 IF p_pa_request_rec.retirement_plan IS NOT NULL AND p_pa_request_rec.retirement_plan NOT IN ('D','K','L','M','N','P','DR','KR','LR','MR','NR','PR')
2552 AND l_tsp_scd IS NOT NULL THEN
2553 IF NOT(p_pa_request_rec.retirement_plan = '4' AND NVL(p_pa_request_rec.annuitant_indicator,hr_api.g_varchar2) NOT IN ('2','3','9')) THEN
2554 hr_utility.set_message(8301,'GHR_38392_NON_TSP_FERS');
2555 hr_utility.raise_error;
2556 END IF;
2557 END IF;
2558 END IF;
2559 --End Bug# 14769681
2560 --Begin Bug 12637350
2561 IF p_pa_request_rec.noa_family_code IN ('APP','CONV_APP','EXT_NTE') THEN
2562 hr_utility.set_location('Fehb Elig Exp Date '||p_pa_request_rec.first_noa_code , 11);
2563 FOR c_detail_first_nte_date_rec in c_detail_first_nte_date LOOP
2564 l_noa_nte_date := fnd_date.canonical_to_date(c_detail_first_nte_date_rec.first_noa_information1);
2565 END LOOP;
2566 FOR c_cur_fehb_elg_exp_date in cur_fehb_elg_exp_date LOOP
2567 l_fehb_elig_exp_date := fnd_date.canonical_to_date(c_cur_fehb_elg_exp_date.rei_information3);
2568 END LOOP;
2569 IF p_pa_request_rec.noa_family_code IN ('APP') THEN
2570 l_asg_start_date := p_pa_request_rec.effective_date+365;
2571 ELSIF p_pa_request_rec.noa_family_code IN ('CONV_APP','EXT_NTE') THEN
2572 for asg_prior_date in cur_asg_prior_date loop
2573 l_asg_start_date := asg_prior_date.effective_start_date+365;
2574 exit;
2575 end loop;
2576 END IF;
2577 --Bug# 14686129 removed equal condition not to trigger error for 365 days
2578 IF l_noa_nte_date < l_asg_start_date AND l_fehb_elig_exp_date IS NOT NULL THEN
2579 hr_utility.set_message(8301,'GHR_38952_FEHB_ELIG_FOR_TMP');
2580 hr_utility.raise_error;
2581 END IF;
2582 END IF;
2583 --End Bug 12637350
2584 end Validate_CHECK;
2585
2586 PROCEDURE get_element_details_future (p_element_name IN VARCHAR2
2587 ,p_input_value_name IN VARCHAR2
2588 ,p_assignment_id IN NUMBER
2589 ,p_effective_date IN DATE
2590 ,p_value IN OUT NOCOPY VARCHAR2
2591 ,p_effective_start_date IN OUT NOCOPY DATE) IS
2592 --
2593 -- NOTE: The effective date we get is that of the individual input value not the effective
2594 -- date of the whole element as seen in the element screen.
2595 --
2596 CURSOR cur_ele(p_element_name IN VARCHAR2,
2597 p_bg_id IN NUMBER)
2598 IS
2599 SELECT eev.screen_entry_value
2600 ,eev.effective_start_date
2601 FROM pay_element_types_f elt
2602 ,pay_input_values_f ipv
2603 ,pay_element_entries_f ele
2604 ,pay_element_entry_values_f eev
2605 WHERE p_effective_date < eev.effective_start_date
2606 AND eev.effective_end_date IS NULL
2607 AND elt.element_type_id = ipv.element_type_id
2608 AND upper(elt.element_name)= upper(p_element_name)
2609 AND ipv.input_value_id = eev.input_value_id
2610 AND ele.assignment_id = p_assignment_id
2611 AND ele.element_entry_id+0 = eev.element_entry_id
2612 AND upper(ipv.name ) = upper(p_input_value_name)
2613 -- AND NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0)
2614 AND (elt.business_group_id is NULL or elt.business_group_id = p_bg_id);
2615 --
2616 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
2617 Select distinct business_group_id bg
2618 from per_assignments_f
2619 where assignment_id = p_assignment_id
2620 and p_eff_date between effective_start_date
2621 and effective_end_date;
2622 --
2623 ll_bg_id NUMBER;
2624 ll_pay_basis VARCHAR2(80);
2625 ll_effective_date DATE;
2626 l_new_element_name VARCHAR2(80);
2627 l_session ghr_history_api.g_session_var_type;
2628 --
2629 BEGIN
2630 --
2631 --
2632 -- Initialization
2633 -- Pick the business group id and also pay basis for later use
2634 ll_effective_date := p_effective_Date;
2635
2636 For BG_rec in Cur_BG(p_assignment_id,ll_effective_date)
2637 Loop
2638 ll_bg_id:=BG_rec.bg;
2639 End Loop;
2640
2641 ----
2642 ---- The New Changes after 08/22 patch
2643 ---- For all elements in HR User old function will fetch the same name.
2644 ---- because of is_script will be FALSE
2645 ----
2646 ---- For all elements (except BSR) in Payroll user old function.
2647 ---- for BSR a new function which will fetch from assignmnet id.
2648 ----
2649
2650 IF (p_element_name = 'Basic Salary Rate'
2651 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
2652 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- ', 1);
2653 l_new_element_name :=
2654 pqp_fedhr_uspay_int_utils.return_new_element_name(
2655 p_assignment_id => p_assignment_id,
2656 p_business_group_id => ll_bg_id,
2657 p_effective_date => ll_effective_date);
2658 ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
2659 or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN
2660 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- ', 1);
2661 l_new_element_name :=
2662 pqp_fedhr_uspay_int_utils.return_new_element_name(
2663 p_fedhr_element_name => p_element_name,
2664 p_business_group_id => ll_bg_id,
2665 p_effective_date => ll_effective_date,
2666 p_pay_basis => NULL);
2667
2668 END IF;
2669
2670 --
2671 --
2672 FOR cur_ele_rec IN cur_ele(l_new_element_name,ll_bg_id) LOOP
2673 p_value := cur_ele_rec.screen_entry_value;
2674 p_effective_start_date := cur_ele_rec.effective_start_date;
2675 END LOOP;
2676 --
2677 END get_element_details_future;
2678
2679 end GHR_Validate_CHECK;
[Home] [Help]