[Home] [Help]
PACKAGE BODY: APPS.GHR_SF52_UPDATE
Source
1 PACKAGE BODY GHR_SF52_UPDATE AS
2 /* $Header: ghsf52up.pkb 120.22.12010000.8 2008/12/01 05:56:46 vmididho ship $ */
3 g_effective_date date;
4
5 --- *****************************
6 --- procedure Populate_Remarks
7 ---- ****************************
8 /* This is a procedure introduced to auto populate the remarks 'P05' and
9 'p07' for GS Equivalent and special rate pay table or populate the remarks 'X37'
10 for pay rate determinants('A','B','E','F','U','V')*/
11 -- 6398933 Added parameter p_first_noa_code,p_second_noa_code to populate remark X37 for specific NOA codes
12 PROCEDURE Populate_Remarks(p_pa_request_id in number
13 ,p_to_pay_plan in varchar2
14 ,p_to_pay_table_identifier in number
15 ,p_person_id in number
16 ,p_pay_rate_determinant in varchar2
17 ,p_effective_date in date
18 ,p_first_noa_code in varchar2 -- 6398933
19 ,p_second_noa_code in varchar2 -- 6398933
20 ,p_leo_indicator in varchar2
21 ,p_fegli in varchar2
22 )
23 is
24 CURSOR c1(p_remark_code in varchar2) is
25 SELECT 1
26 FROM GHR_PA_REMARKS parem,
27 GHR_REMARKS grem
28 WHERE parem.remark_id = grem.remark_id
29 AND grem.code = p_remark_code
30 AND parem.pa_request_id = p_pa_request_id;
31
32 CURSOR C2(p_remark_code in varchar2) is
33 SELECT *
34 FROM GHR_REMARKS
35 WHERE code = p_remark_code
36 and p_effective_date between date_from and nvl(date_to,p_effective_date);
37
38 CURSOR C3 is
39 select 1
40 from ghr_pay_plans
41 where pay_plan = p_to_pay_plan
42 and equivalent_pay_plan = 'GS';
43
44 CURSOR C4 is
45 select SUBSTR(user_table_name,0,4)
46 from pay_user_tables
47 where user_table_id = p_to_pay_table_identifier;
48
49 -- Bug# 7269520 Added the below cursor to check pay plan
50 -- and valid grade combination for auto population of P11 remarks
51 CURSOR C5 is
52 select to_pay_plan, to_grade_or_level
53 from ghr_pa_requests
54 where pa_request_id = p_pa_request_id;
55
56 --Bug # 7573846 Added the below cursor to fetch lac codes
57 CURSOR get_pa_det
58 IS
59 select first_action_la_code1,first_action_la_code2
60 ,second_action_la_code1,second_action_la_code2,
61 first_noa_code,
62 second_noa_code
63 from ghr_pa_requests
64 where pa_request_id = p_pa_request_id;
65
66
67
68
69
70 l_proc varchar2(100) := 'Populate_Remarks';
71 l_found varchar2(1);
72 l_remark c2%rowtype;
73 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
74 l_object_version_number ghr_pa_remarks.object_version_number%type;
75 l_description ghr_remarks.description%type;
76 l_user_table_name pay_user_tables.user_table_name%type;
77 l_retained_grade_info ghr_pay_calc.retained_grade_rec_type;
78 l_retained_grade varchar2(10);
79 -- Bug # 7269520
80 l_to_pay_plan ghr_pa_requests.to_pay_plan%type;
81 l_to_grade_or_level ghr_pa_requests.to_grade_or_level%type;
82 -- Bug # 7269520
83
84 begin
85 hr_utility.set_location('Entering ' ||l_proc,5);
86 --Added Condition for the bug 6398933
87 If NOT (p_first_noa_code in ('840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889')
88 or (p_first_noa_code = '002' and
89 p_second_noa_code in ('840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889'))
90 ) Then
91 If p_pay_rate_determinant in ('A','B','E','F','U','V') then
92 If c1%isopen then
93 close c1;
94 End if;
95 open c1(p_remark_code => 'X37');
96 fetch c1 into l_found;
97 If c1%notfound then
98 If c2%isopen then
99 close c2;
100 End If;
101 open c2(p_remark_code => 'X37');
102 fetch c2 into l_remark;
103 if c2%found then
104
105
106 -- l_retained_grade_info := ghr_pc_basic_pay.get_retained_grade_details(p_person_id => p_person_id
107 -- ,p_effective_date => p_effective_date);
108
109 hr_utility.set_location('From Global varaible Fetchig the retained Grade details ' ||l_proc,20);
110
111 l_retained_grade_info := ghr_sf52_do_update.g_retained_grade_info;
112 -- l_retained_grade := l_retained_grade_info.grade_or_level||'-'||l_retained_grade_info.pay_plan;
113
114 --Bug# 6075814 vmididho to display PayPlan-Gradeorlevel
115 l_retained_grade := l_retained_grade_info.pay_plan||'-'||l_retained_grade_info.grade_or_level;
116
117 hr_utility.set_location('l_retained_grade ' || l_retained_grade, 30);
118
119 /*l_description := REPLACE(SUBSTR(l_remark.description,0,INSTR(l_remark.description,'_',1)-1)||l_retained_grade||
120 SUBSTR(l_remark.description,INSTR(l_remark.description,'_',1)+ LENGTH(l_retained_grade),
121 (INSTR(l_remark.description,'_',INSTR(l_remark.description,'_',1)+10))- (INSTR(l_remark.description,'_',1)+ LENGTH(l_retained_grade)))||
122 TO_CHAR(l_retained_grade_info.date_to,'DD-MON-RRRR'),'_',' ');*/
123
124 ghr_mass_actions_pkg.replace_insertion_values
125 (p_desc => l_remark.description,
126 p_information1 => l_retained_grade,
127 p_information2 => TO_CHAR(l_retained_grade_info.date_to,'DD-MON-RRRR'),
128 p_information3 => NULL,
129 p_information4 => NULL,
130 p_information5 => NULL,
131 p_desc_out => l_description
132 );
133
134 hr_utility.set_location('l_description ' || l_description, 40);
135
136 ghr_pa_remarks_api.create_pa_remarks
137 (p_validate => false
138 ,p_pa_request_id => p_pa_request_id
139 ,p_remark_id => l_remark.remark_id
140 ,p_description => l_description
141 ,p_remark_code_information1 => l_retained_grade
142 ,p_remark_code_information2 => TO_CHAR(l_retained_grade_info.date_to,'DD-MON-RRRR')
143 ,p_pa_remark_id => l_pa_remark_id
144 ,p_object_version_number => l_object_version_number
145 );
146 End if; -- cursor c2
147 close c2;
148 End If;
149 Close c1;
150 End IF;
151 If C3%isopen then
152 Close C3;
153 End If;
154
155 Open C3;
156 Fetch C3 into l_found;
157 -- If Equivalent code is 'GS'
158 If C3%found then
159 If C4%isopen then
160 Close C4;
161 End If;
162
163 Open C4;
164 Fetch C4 into l_user_table_name;
165 Close C4;
166 -- special pay rate table
167 If l_user_table_name not in ('0000','0491') THEN
168
169 If c1%isopen then
170 close c1;
171 End if;
172
173
174 open c1(p_remark_code => 'P05');
175 fetch c1 into l_found;
176 If c1%notfound then
177
178 If c2%isopen then
179 close c2;
180 End If;
181 open c2(p_remark_code => 'P05');
182 fetch c2 into l_remark;
183 if c2%found then
184
185 ghr_pa_remarks_api.create_pa_remarks
186 (p_validate => false
187 ,p_pa_request_id => p_pa_request_id
188 ,p_remark_id => l_remark.remark_id
189 ,p_description => l_remark.description
190 ,p_pa_remark_id => l_pa_remark_id
191 ,p_object_version_number => l_object_version_number
192 );
193 end if; -- cursor c2
194 close c2;
195 End If;
196 Close c1;
197
198 If c1%isopen then
199 close c1;
200 End if;
201 open c1(p_remark_code => 'P07');
202 fetch c1 into l_found;
203 If c1%notfound then
204
205 If c2%isopen then
206 close c2;
207 End If;
208 open c2(p_remark_code => 'P07');
209 fetch c2 into l_remark;
210 if c2%found then
211
212 --l_description := replace(l_remark.description,'_',' ')||l_user_table_name;
213
214 ghr_mass_actions_pkg.replace_insertion_values
215 (p_desc => l_remark.description,
216 p_information1 => l_user_table_name,
217 p_information2 => NULL,
218 p_information3 => NULL,
219 p_information4 => NULL,
220 p_information5 => NULL,
221 p_desc_out => l_description
222 );
223
224 ghr_pa_remarks_api.create_pa_remarks
225 (p_validate =>false
226 ,p_pa_request_id => p_pa_request_id
227 ,p_remark_id => l_remark.remark_id
228 ,p_description => l_description
229 ,p_remark_code_information1 => l_user_table_name
230 ,p_pa_remark_id => l_pa_remark_id
231 ,p_object_version_number => l_object_version_number
232 );
233 end if; -- cursor c2
234 close c2;
235 End If;
236 Close c1;
237 End If; --special pay rate table
238 End If; -- Equivalent GS
239 close c3;
240 end if; /* validation related to NOA Actions before populating X37,P05 and P07 remarks --6398933*/
241
242 -- Bug # 7269520 Added the validation of pay plan and Grade or level before populating p11 remarks
243 Open C5;
244 Fetch C5 into l_to_pay_plan, l_to_grade_or_level;
245 Close C5;
246 If l_to_pay_plan = 'GL' and l_to_grade_or_level in ('03','04','05','06','07','08','09','10') then
247 /*Bug #5919713 Auto population of remark code P11 for leo employees*/
248 -- it should be autopopulated other than Awards , LWOP and Non Pay Actions
249 If NOT (p_first_noa_code in ('840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889',
250 '430','450','452','460','471','472','473','480','773')
251 or (p_first_noa_code = '002' and
252 p_second_noa_code in ('840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889',
253 '430','450','452','460','471','472','473','480','773'))
254 ) Then
255
256 If p_leo_indicator <> '0' then
257 If c1%isopen then
258 close c1;
259 End if;
260
261 open c1(p_remark_code => 'P11');
262 fetch c1 into l_found;
263 If c1%notfound then
264
265 If c2%isopen then
266 close c2;
267 End If;
268 open c2(p_remark_code => 'P11');
269 fetch c2 into l_remark;
270 if c2%found then
271
272 ghr_pa_remarks_api.create_pa_remarks
273 (p_validate => false
274 ,p_pa_request_id => p_pa_request_id
275 ,p_remark_id => l_remark.remark_id
276 ,p_description => l_remark.description
277 ,p_pa_remark_id => l_pa_remark_id
278 ,p_object_version_number => l_object_version_number
279 );
280 end if; -- cursor c2
281 close c2;
282 End If;
283 Close c1;
284 End If;
285 End If;
286 /*End of Bug #5919713*/
287 End If; -- Checking pay plan and Grade
288
289 --Bug # 7573846
290
291 --Need to add remark code B76 for 473 and LAC code is Q3K or 353
292 -- Fegli is other than A0,A1,B0.
293
294 If (p_first_noa_code in ('353','473')) then
295
296 for get_pa_det_rec in get_pa_det
297 loop
298
299 if ((get_pa_det_rec.first_noa_code = '473') and
300 (get_pa_det_rec.first_action_la_code1 = 'Q3K' or get_pa_det_rec.first_action_la_code2 = 'Q3K') and
301 (p_fegli not in ('A0','A1','B0'))) OR
302 ((get_pa_det_rec.first_noa_code = '353') and
303 (p_fegli not in ('A0','A1','B0'))) OR
304 ((get_pa_det_rec.first_noa_code = '002' and get_pa_det_rec.second_noa_code = '473') and
305 (get_pa_det_rec.second_action_la_code1 = 'Q3K' or get_pa_det_rec.second_action_la_code2 = 'Q3K') and
306 (p_fegli not in ('A0','A1','B0'))) OR
307 ((get_pa_det_rec.first_noa_code = '002' and get_pa_det_rec.second_noa_code = '353') and
308 (p_fegli not in ('A0','A1','B0'))) then
309
310 open c1(p_remark_code => 'B76');
311 fetch c1 into l_found;
312 If c1%notfound then
313 If c2%isopen then
314 close c2;
315 End If;
316 open c2(p_remark_code => 'B76');
317 fetch c2 into l_remark;
318 if c2%found then
319
320 ghr_pa_remarks_api.create_pa_remarks
321 (p_validate => false
322 ,p_pa_request_id => p_pa_request_id
323 ,p_remark_id => l_remark.remark_id
324 ,p_description => l_remark.description
325 ,p_pa_remark_id => l_pa_remark_id
326 ,p_object_version_number=> l_object_version_number
327 );
328 end if; -- cursor c2
329 close c2;
330 End If;
331 Close c1;
332 end if;
333 end loop;
334 end if;
335 -- Bug # 7573846
336
337 hr_utility.set_location('Leaving ' || l_proc,100);
338 end Populate_Remarks;
339
340
341
342
343 --
344 -- ********************************
345 -- procedure Process_Immediate_Update
346 -- ********************************
347 --
348
349 procedure Process_Immediate_Update
350 ( p_imm_pa_request_rec in out nocopy ghr_pa_requests%rowtype,
351 p_imm_pa_request_ei_rec in ghr_pa_request_extra_info%rowtype,
352 p_imm_generic_ei_rec in ghr_pa_request_extra_info%rowtype,
353 p_capped_other_pay in out nocopy number
354 )
355 is
356
357 l_session ghr_history_api.g_session_var_type;
358 l_imm_pa_request_rec ghr_pa_requests%rowtype;
359 l_imm_pa_request_rec_in ghr_pa_requests%rowtype; /* Added for NOCOPY changes */
360 l_capped_other_pay_in number; /* Added for NOCOPY changes */
361 l_imm_asg_sf52 ghr_api.asg_sf52_type;
362 l_imm_asg_non_sf52 ghr_api.asg_non_sf52_type;
363 l_imm_asg_nte_dates ghr_api.asg_nte_dates_type;
364 l_imm_per_sf52 ghr_api.per_sf52_type;
365 l_imm_per_group1 ghr_api.per_group1_type;
366 l_imm_per_group2 ghr_api.per_group2_type;
367 l_imm_per_scd_info ghr_api.per_scd_info_type;
368 l_imm_per_retained_grade ghr_api.per_retained_grade_type;
369 l_imm_per_probations ghr_api.per_probations_type;
370 l_imm_per_sep_retire ghr_api.per_sep_retire_type;
371 l_imm_per_security ghr_api.per_security_type;
372 -- Bug#4486823 RRR changes
373 l_imm_per_service_oblig ghr_api.per_service_oblig_type;
374 l_imm_per_conversions ghr_api.per_conversions_type;
375 -- 4352589 BEN_EIT Changes
376 l_imm_per_benefit_info ghr_api.per_benefit_info_type;
377 l_imm_per_uniformed_services ghr_api.per_uniformed_services_type;
378 l_imm_pos_oblig ghr_api.pos_oblig_type;
379 l_imm_pos_grp2 ghr_api.pos_grp2_type;
380 l_imm_pos_grp1 ghr_api.pos_grp1_type;
381 l_imm_pos_valid_grade ghr_api.pos_valid_grade_type;
382 l_imm_pos_car_prog ghr_api.pos_car_prog_type;
383 l_imm_loc_info ghr_api.loc_info_type;
384 l_imm_wgi ghr_api.within_grade_increase_type;
385 l_imm_gov_awards ghr_api.government_awards_type;
386 l_imm_recruitment_bonus ghr_api.recruitment_bonus_type;
387 l_imm_relocation_bonus ghr_api.relocation_bonus_type;
388 l_imm_student_loan_repay ghr_api.student_loan_repay_type;
389
390 l_imm_extra_info_rec ghr_api.extra_info_rec_type ;
391 l_imm_sf52_from_data ghr_api.prior_sf52_data_type;
392 l_imm_personal_info ghr_api.personal_info_type;
393 l_imm_generic_extra_info_rec ghr_api.generic_extra_info_rec_type ;
394 l_imm_agency_sf52 ghr_api.agency_sf52_type;
395 l_agency_code varchar2(50);
396 l_imm_perf_appraisal ghr_api.performance_appraisal_type;
397 l_imm_conduct_performance ghr_api.conduct_performance_type;
398 l_imm_payroll_type ghr_api.government_payroll_type;
399 l_imm_par_term_retained_grade ghr_api.par_term_retained_grade_type;
400 l_imm_entitlement ghr_api.entitlement_type;
401 -- Bug#2759379 Added FEGLI Record
402 l_imm_fegli ghr_api.fegli_type;
403 l_imm_foreign_lang_prof_pay ghr_api.foreign_lang_prof_pay_type;
404 -- Bug#3385386
405 l_imm_fta ghr_api.fta_type;
406 l_imm_edp_pay ghr_api.edp_pay_type;
407 l_imm_hazard_pay ghr_api.hazard_pay_type;
408 l_imm_health_benefits ghr_api.health_benefits_type;
409 l_imm_danger_pay ghr_api.danger_pay_type;
410 l_imm_imminent_danger_pay ghr_api.imminent_danger_pay_type;
411 l_imm_living_quarters_allow ghr_api.living_quarters_allow_type;
412 l_imm_post_diff_amt ghr_api.post_diff_amt_type;
413 l_imm_post_diff_percent ghr_api.post_diff_percent_type;
414 l_imm_sep_maintenance_allow ghr_api.sep_maintenance_allow_type;
415 l_imm_supplemental_post_allow ghr_api.supplemental_post_allow_type;
416 l_imm_temp_lodge_allow ghr_api.temp_lodge_allow_type;
417 l_imm_premium_pay ghr_api.premium_pay_type;
418 l_imm_retirement_annuity ghr_api.retirement_annuity_type;
419 l_imm_severance_pay ghr_api.severance_pay_type;
420 l_imm_thrift_saving_plan ghr_api.thrift_saving_plan;
421 l_imm_retention_allow_review ghr_api.retention_allow_review_type;
422 l_imm_health_ben_pre_tax ghr_api.health_ben_pre_tax_type;
423 l_imm_mddds_special_pay ghr_api.mddds_special_pay_type;
424 l_imm_premium_pay_ind ghr_api.premium_pay_ind_type;
425 l_imm_per_race_ethnic_info ghr_api.per_race_ethnic_type; -- Race or National Origin changes
426 -- bug # 6312144 RPA EIT Benefits
427 l_imm_ipa_benefits_cont ghr_api.per_ipa_ben_cont_info_type;
428 l_imm_retirement_info ghr_api.per_retirement_info_type;
429
430
431 --
432 l_proc varchar2(70) := 'Process_Immediate_Update';
433 --
434 l_noa_family_name varchar2(150);
435 l_person_type varchar2(50);
436 l_hr_applicant_api_create_sec varchar2(1);
437 l_employee_api_update_criteria varchar2(1);
438 --
439 l_health_plan pay_element_entry_values.screen_entry_value%type;
440 l_error_flag boolean;
441 --
442 l_pa_request_id ghr_pa_requests.pa_request_id%type;
443 l_pay_calc_in_rec ghr_pay_calc.pay_calc_in_rec_type;
444 l_pay_calc_out_rec ghr_pay_calc.pay_calc_out_rec_type;
445 l_message_set boolean;
446 l_calculated boolean;
447 -- Benefits EIT Sundar
448 l_errbuf varchar2(2000);
449 l_retcode number;
450 l_amount_or_rate VARCHAR2(100);
451 l_opt_val number;
452 l_bg_id per_all_people_f.business_group_id%type;
453 l_temp_appt VARCHAR2(5);
454 l_warning boolean;
455 -- Cursors declaration
456 --
457 Cursor c_bg_id(c_person_id per_all_people_f.person_id%type,
458 c_effective_date per_all_people_f.effective_start_date%type) is
459 select business_group_id bg_id
460 from per_all_people_f
461 where person_id = c_person_id
462 and c_effective_date between effective_start_date and effective_end_date;
463
464 --Start of Bug # 5195518 added to check current enrollment
465 cursor get_current_enrollment (p_asg_id in NUMBER,
466 p_business_group_id in NUMBER,
467 p_effective_date in DATE)
468 is
469 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment,
470 eef.element_entry_id ,
471 eef.object_version_number
472 FROM pay_element_entries_f eef,
473 pay_element_types_f elt
474 WHERE assignment_id = p_asg_id
475 AND elt.element_type_id = eef.element_type_id
476 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
477 elt.effective_end_date
478 AND p_effective_date between eef.effective_start_date and eef.effective_end_date
479 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
480 p_business_group_id,
481 p_effective_date))
482 IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX');
483
484
485
486 l_object_version_number pay_element_entries_f.object_version_number%type;
487 l_effective_start_date date;
488 l_effective_end_date date;
489 l_delete_warning boolean;
490 l_exists BOOLEAN := FALSE;
491 l_pgm_id ben_pgm_f.pgm_id%type;
492
493 -- Cursor to get Program
494 CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type,
495 c_business_group_id ben_pgm_f.business_group_id%type,
496 c_effective_date ben_pgm_f.effective_start_date%type) is
497 SELECT pgm.pgm_id
498 FROM ben_pgm_f pgm
499 WHERE pgm.name = c_prog_name
500 AND pgm.business_group_id = c_business_group_id
501 AND c_effective_date between effective_start_date and effective_end_date;
502
503 CURSOR c_emp_in_ben(c_person_id ben_prtt_enrt_rslt_f.person_id%type,
504 c_pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
505 c_effective_date ben_pgm_f.effective_start_date%type) is
506 SELECT 1
507 FROM ben_prtt_enrt_rslt_f
508 WHERE person_id = c_person_id
509 AND pgm_id = c_pgm_id
510 AND prtt_enrt_rslt_stat_cd IS NULL
511 AND c_effective_date between effective_start_date and effective_end_date;
512
513 --end of Bug # 5195518
514 --Begin Bug# 4691288
515 l_grade_or_level ghr_pa_requests.to_grade_or_level%type;
516 l_pay_plan ghr_pa_requests.to_pay_plan%type;
517 l_record_found BOOLEAN := FALSE;
518
519
520 CURSOR cur_grd1 IS
521 SELECT gdf.segment1 pay_plan,
522 gdf.segment2 grade_or_level
523 FROM per_grade_definitions gdf,
524 per_grades grd
525 WHERE grd.grade_id = l_imm_pos_valid_grade.valid_grade
526 AND grd.grade_definition_id = gdf.grade_definition_id
527 AND grd.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
528
529 CURSOR cur_pay IS
530 SELECT cin.value basic_pay
531 FROM pay_user_column_instances_f cin
532 ,pay_user_rows_f urw
533 ,pay_user_columns col
534 WHERE col.user_table_id = l_imm_pos_valid_grade.pay_table_id
535 AND urw.user_table_id = col.user_table_id
536 AND urw.row_low_range_or_name = l_pay_plan||'-'||l_grade_or_level
537 AND NVL(p_imm_pa_request_rec.effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
538 AND cin.user_row_id = urw.user_row_id
539 AND cin.user_column_id = col.user_column_id
540 AND NVL(p_imm_pa_request_rec.effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;
541 --End Bug# 4691288
542 Begin
543 hr_utility.set_location('Entering ' ||l_proc,5);
544
545 l_imm_pa_request_rec_in := p_imm_pa_request_rec; /* Added for NOCOPY changes */
546 l_capped_other_pay_in := p_capped_other_pay; /* Added for NOCOPY changes */
547
548 hr_utility.set_location(l_proc,10);
549 ghr_history_api.get_g_session_var(l_session);
550 IF l_session.noa_id_correct is null and
551 p_imm_pa_request_rec.first_noa_code = '866' THEN
552 g_effective_date := p_imm_pa_request_rec.effective_date + 1;
553 l_session.date_effective := g_effective_date;
554 ghr_history_api.set_g_session_var( l_session);
555 ELSE
556 g_effective_date := p_imm_pa_request_rec.effective_date;
557 END IF;
558
559 -- Before performing validation, recalculate pay (don't calculate if first_noa_code / second_noa_code = 899)
560
561 ghr_process_sf52.redo_pay_calc
562 (p_sf52_rec => p_imm_pa_request_rec,
563 p_capped_other_pay => p_capped_other_pay
564 );
565
566 ghr_sf52_post_update.get_notification_details
567 (p_pa_request_id => p_imm_pa_request_rec.pa_request_id,
568 p_effective_date => p_imm_pa_request_rec.effective_date,
569 -- p_object_version_number => p_imm_pa_request_rec.object_version_number,
570 p_from_position_id => p_imm_pa_request_rec.from_position_id,
571 p_to_position_id => p_imm_pa_request_rec.to_position_id,
572 p_agency_code => p_imm_pa_request_rec.agency_code,
573 p_from_agency_code => p_imm_pa_request_rec.from_agency_code,
574 p_from_agency_desc => p_imm_pa_request_rec.from_agency_desc,
575 p_from_office_symbol => p_imm_pa_request_rec.from_office_symbol,
576 p_personnel_office_id => p_imm_pa_request_rec.personnel_office_id,
577 p_employee_dept_or_agency => p_imm_pa_request_rec.employee_dept_or_agency,
578 p_to_office_symbol => p_imm_pa_request_rec.to_office_symbol
579 );
580 --
581 -- Calling validation package to validate pa_request data
582 ghr_sf52_validn_pkg.perform_validn
583 (p_rec => P_imm_Pa_Request_Rec
584 );
585 --
586 --
587 l_imm_asg_sf52.asg_sf52_flag := 'N';
588 l_imm_asg_non_sf52.asg_non_sf52_flag := 'N';
589 l_imm_asg_nte_dates.asg_nte_dates_flag := 'N';
590 l_imm_per_sf52.per_sf52_flag := 'N';
591 l_imm_per_group1.per_group1_flag := 'N';
592 l_imm_per_group2.per_group2_flag := 'N';
593 l_imm_per_scd_info.per_scd_info_flag := 'N';
594 l_imm_per_retained_grade.per_retained_grade_flag := 'N';
595 l_imm_per_probations.per_probation_flag := 'N';
596 l_imm_per_sep_retire.per_sep_retire_flag := 'N';
597 l_imm_per_security.per_security_flag := 'N';
598 --Bug#4486823 RRR Changes
599 l_imm_per_service_oblig.per_service_oblig_flag := 'N';
600 l_imm_per_conversions.per_conversions_flag := 'N';
601 l_imm_per_uniformed_services.per_uniformed_services_flag := 'N';
602 l_imm_pos_oblig.pos_oblig_flag := 'N';
603 l_imm_pos_grp2.pos_grp2_flag := 'N';
604 l_imm_pos_grp1.pos_grp1_flag := 'N';
605 l_imm_pos_valid_grade.pos_valid_grade_flag := 'N';
606 l_imm_pos_car_prog.pos_car_prog_flag := 'N';
607 l_imm_loc_info.loc_info_flag := 'N';
608 l_imm_gov_awards.award_flag := 'N';
609 l_imm_conduct_performance.cond_perf_flag := 'N';
610 l_imm_par_term_retained_grade.par_term_retained_grade_flag := 'N';
611 l_imm_entitlement.entitlement_flag := 'N';
612 -- Bug#2759379
613 l_imm_fegli.fegli_flag := 'N';
614 l_imm_foreign_lang_prof_pay.for_lang_flag := 'N';
615 -- Bug#3385386
616 l_imm_fta.fta_flag := 'N';
617 l_imm_edp_pay.edp_flag := 'N';
618 l_imm_health_benefits.health_benefits_flag := 'N';
619 l_imm_danger_pay.danger_flag := 'N';
620 l_imm_imminent_danger_pay.imminent_danger_flag := 'N';
621 l_imm_living_quarters_allow.living_quarters_allow_flag := 'N';
622 l_imm_post_diff_amt.post_diff_amt_flag := 'N';
623 l_imm_post_diff_percent.post_diff_percent_flag := 'N';
624 l_imm_sep_maintenance_allow.sep_maint_allow_flag := 'N';
625 l_imm_supplemental_post_allow.sup_post_allow_flag := 'N';
626 l_imm_temp_lodge_allow.temp_lodge_allow_flag := 'N';
627 l_imm_premium_pay.premium_pay_flag := 'N';
628 l_imm_retirement_annuity.retirement_annuity_flag := 'N';
629 l_imm_severance_pay.severance_pay_flag := 'N';
630 l_imm_thrift_saving_plan.tsp_flag := 'N';
631 l_imm_retention_allow_review.retention_allow_review_flag := 'N';
632 l_imm_health_ben_pre_tax.health_ben_pre_tax_flag := 'N';
633 l_imm_per_race_ethnic_info.p_race_ethnic_info_flag := 'N'; -- Race and National origin changes
634
635 --
636 --
637
638 -- (Procedures reside in GHR_SF52_PRE_UPDATE package).
639
640 -- Populate all necessary record groups , for CPDF Valdiation,update/create extra infos, for agency specific edits
641 --
642
643 hr_utility.set_location(l_proc,15);
644
645 hr_utility.set_location('person id ' || to_char(p_imm_pa_request_rec.person_id),1);
646 ghr_sf52_pre_update.populate_record_groups
647 (
648 p_pa_request_rec => p_imm_pa_request_rec,
649 p_generic_ei_rec => p_imm_generic_ei_rec,
650 p_imm_asg_sf52 => l_imm_asg_sf52,
651 p_imm_asg_non_sf52 => l_imm_asg_non_sf52,
652 p_imm_asg_nte_dates => l_imm_asg_nte_dates,
653 p_imm_per_sf52 => l_imm_per_sf52,
654 p_imm_per_group1 => l_imm_per_group1,
655 p_imm_per_group2 => l_imm_per_group2,
656 p_imm_per_scd_info => l_imm_per_scd_info,
657 p_imm_per_retained_grade => l_imm_per_retained_grade,
658 p_imm_per_probations => l_imm_per_probations,
659 p_imm_per_sep_retire => l_imm_per_sep_retire,
660 p_imm_per_security => l_imm_per_security,
661 --Bug#4486823 RRR Changes
662 p_imm_per_service_oblig => l_imm_per_service_oblig,
663 p_imm_per_conversions => l_imm_per_conversions,
664 -- 4352589 BEN_EIT Changes
665 p_imm_per_benefit_info => l_imm_per_benefit_info,
666 p_imm_per_uniformed_services => l_imm_per_uniformed_services,
667 p_imm_pos_oblig => l_imm_pos_oblig,
668 p_imm_pos_grp2 => l_imm_pos_grp2,
669 p_imm_pos_grp1 => l_imm_pos_grp1,
670 p_imm_pos_valid_grade => l_imm_pos_valid_grade,
671 p_imm_pos_car_prog => l_imm_pos_car_prog,
672 p_imm_loc_info => l_imm_loc_info,
673 p_imm_wgi => l_imm_wgi,
674 p_imm_gov_awards => l_imm_gov_awards,
675 p_imm_recruitment_bonus => l_imm_recruitment_bonus,
676 p_imm_relocation_bonus => l_imm_relocation_bonus,
677 p_imm_student_loan_repay => l_imm_student_loan_repay,
678 --Pradeep
679 p_imm_per_race_ethnic_info => l_imm_per_race_ethnic_info, -- Race and National Origin changes
680 p_imm_mddds_special_pay => l_imm_mddds_special_pay,
681 p_imm_premium_pay_ind => l_imm_premium_pay_ind,
682
683 p_imm_perf_appraisal => l_imm_perf_appraisal,
684 p_imm_conduct_performance => l_imm_conduct_performance,
685 p_imm_payroll_type => l_imm_payroll_type,
686 p_imm_extra_info_rec => l_imm_extra_info_rec,
687 p_imm_sf52_from_data => l_imm_sf52_from_data,
688 p_imm_personal_info => l_imm_personal_info,
689 p_imm_generic_extra_info_rec => l_imm_generic_extra_info_rec,
690 p_imm_agency_sf52 => l_imm_agency_sf52,
691 p_agency_code => l_agency_code,
692 p_imm_par_term_retained_grade => l_imm_par_term_retained_grade,
693 p_imm_entitlement => l_imm_entitlement,
694 -- Bug#2759379 Added parameter p_imm_fegli
695 p_imm_fegli => l_imm_fegli,
696 p_imm_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
697 -- Bug#3385386 Added parameter p_imm_fta
698 p_imm_fta => l_imm_fta,
699 p_imm_edp_pay => l_imm_edp_pay,
700 p_imm_hazard_pay => l_imm_hazard_pay,
701 p_imm_health_benefits => l_imm_health_benefits,
702 p_imm_danger_pay => l_imm_danger_pay,
703 p_imm_imminent_danger_pay => l_imm_imminent_danger_pay,
704 p_imm_living_quarters_allow => l_imm_living_quarters_allow,
705 p_imm_post_diff_amt => l_imm_post_diff_amt,
706 p_imm_post_diff_percent => l_imm_post_diff_percent,
707 p_imm_sep_maintenance_allow => l_imm_sep_maintenance_allow,
708 p_imm_supplemental_post_allow => l_imm_supplemental_post_allow,
709 p_imm_temp_lodge_allow => l_imm_temp_lodge_allow,
710 p_imm_premium_pay => l_imm_premium_pay,
711 p_imm_retirement_annuity => l_imm_retirement_annuity,
712 p_imm_severance_pay => l_imm_severance_pay,
713 p_imm_thrift_saving_plan => l_imm_thrift_saving_plan,
714 p_imm_retention_allow_review => l_imm_retention_allow_review,
715 p_imm_health_ben_pre_tax => l_imm_health_ben_pre_tax,
716 -- Bug # 6312144 added new parameters to the procedure for RPA -- EIT Benefits
717 p_imm_ipa_benefits_cont => l_imm_ipa_benefits_cont,
718 p_imm_retirement_info => l_imm_retirement_info
719
720 );
721
722 hr_utility.set_location(l_proc,20);
723
724 -- FWFA Changes Bug#4444609
725 -- Bug#4701896
726 IF ghr_pay_calc.g_pay_table_upd_flag THEN
727 IF p_imm_pa_request_rec.input_pay_rate_determinant IN ('A','B','E','F','U','V')
728 AND l_imm_per_retained_grade.temp_step IS NULL THEN
729 --Begin Bug# 4691288
730 BEGIN
731 IF l_imm_per_retained_grade.retain_pay_table_id = l_imm_pos_valid_grade.pay_table_id
732 and l_imm_pos_valid_grade.pay_table_id <> p_imm_pa_request_rec.to_pay_table_identifier THEN
733 FOR p_cur_grd1 in cur_grd1 LOOP
734 l_grade_or_level := p_cur_grd1.grade_or_level;
735 l_pay_plan := p_cur_grd1.pay_plan;
736 END LOOP;
737 FOR cur_pay_rec IN cur_pay LOOP
738 l_record_found := TRUE;
739 EXIT;
740 END LOOP;
741 IF NOT l_record_found THEN
742 l_imm_pos_valid_grade.pay_table_id := p_imm_pa_request_rec.to_pay_table_identifier;
743 l_imm_pos_valid_grade.valid_grade := p_imm_pa_request_rec.to_grade_id;
744 l_imm_pos_valid_grade.pos_valid_grade_flag := 'Y';
745 END IF;
746 END IF;
747 EXCEPTION
748 WHEN OTHERS THEN
749 NULL;
750 END;
751 --end Bug# 4691288
752 l_imm_per_retained_grade.retain_pay_plan := nvl(ghr_pay_calc.g_out_to_pay_plan,l_imm_per_retained_grade.retain_pay_plan);
753 l_imm_per_retained_grade.retain_pay_table_id := p_imm_pa_request_rec.to_pay_table_identifier;
754 l_imm_per_retained_grade.per_retained_grade_flag := 'Y';
755 ELSE
756 l_imm_pos_valid_grade.pay_table_id := p_imm_pa_request_rec.to_pay_table_identifier;
757 l_imm_pos_valid_grade.valid_grade := p_imm_pa_request_rec.to_grade_id;
758 l_imm_pos_valid_grade.pos_valid_grade_flag := 'Y';
759 END IF;
760 END IF;
761 -- FWFA Changes
762 -- check that the required SF52 and the Non SF52 data is entered
763
764
765 ghr_upd_hr_validation.main_validation
766 (p_pa_requests_type => p_imm_pa_request_rec
767 ,p_asg_non_sf52_type => l_imm_asg_non_sf52
768 ,p_asg_nte_dates_type => l_imm_asg_nte_dates
769 ,p_per_group1_type => l_imm_per_group1
770 ,p_per_uniformed_services_type => l_imm_per_uniformed_services
771 ,p_per_retained_grade_type => l_imm_per_retained_grade
772 ,p_per_sep_retire_type => l_imm_per_sep_retire
773 ,p_per_probations_type => l_imm_per_probations
774 ,p_pos_grp1_type => l_imm_pos_grp1
775 ,p_pos_grp2_type => l_imm_pos_grp2
776 ,p_recruitment_bonus_type => l_imm_recruitment_bonus
777 ,p_relocation_bonus_type => l_imm_relocation_bonus
778 ,p_student_loan_repay_type => l_imm_student_loan_repay
779 --Pradeep
780 ,p_mddds_special_pay => l_imm_mddds_special_pay
781 ,p_premium_pay_ind => l_imm_premium_pay_ind
782
783 ,p_within_grade_increase_type => l_imm_wgi
784 ,p_government_awards_type => l_imm_gov_awards
785 ,p_government_payroll_type => l_imm_payroll_type
786 ,p_performance_appraisal_type => l_imm_perf_appraisal
787 ,p_per_conversions_type => l_imm_per_conversions
788 ,p_conduct_performance_type => l_imm_conduct_performance
789 -- Sun 4582970 - Added 3 parameters for benefits eit validation
790 ,p_thrift_savings_plan => l_imm_thrift_saving_plan
791 ,p_per_benefit_info => l_imm_per_benefit_info
792 ,p_per_scd_info_type => l_imm_per_scd_info
793 );
794
795
796 --Fetch Health Plan
797
798 hr_utility.set_location('Health Plan ' ,20);
799
800 ghr_api.retrieve_element_entry_value
801 ( p_element_name => 'Health Benefits'
802 ,p_input_value_name => 'Health Plan'
803 ,p_assignment_id => p_imm_pa_request_rec.employee_assignment_id
804 ,p_effective_date => trunc(p_imm_pa_request_rec.effective_date)
805 ,p_value => l_health_plan
806 ,p_multiple_error_flag => l_error_flag
807 );
808
809 -- Agency Check hook
810
811 GHR_AGENCY_CHECK.AGENCY_CHECK
812 (
813 p_pa_request_rec => p_imm_pa_request_rec,
814 p_asg_sf52 => l_imm_asg_sf52,
815 p_asg_non_sf52 => l_imm_asg_non_sf52,
816 p_asg_nte_dates => l_imm_asg_nte_dates,
817 p_per_sf52 => l_imm_per_sf52,
818 p_per_group1 => l_imm_per_group1,
819 p_per_group2 => l_imm_per_group2,
820 p_per_scd_info => l_imm_per_scd_info,
821 p_per_retained_grade => l_imm_per_retained_grade,
822 p_per_probations => l_imm_per_probations,
823 p_per_sep_Retire => l_imm_per_sep_retire,
824 p_per_security => l_imm_per_security,
825 p_per_conversions => l_imm_per_conversions,
826 p_per_uniformed_services => l_imm_per_uniformed_services,
827 p_pos_oblig => l_imm_pos_oblig,
828 p_pos_grp2 => l_imm_pos_grp2,
829 p_pos_grp1 => l_imm_pos_grp1,
830 p_pos_valid_grade => l_imm_pos_valid_grade,
831 p_pos_car_prog => l_imm_pos_car_prog,
832 p_loc_info => l_imm_loc_info,
833 p_wgi => l_imm_wgi,
834 p_recruitment_bonus => l_imm_recruitment_bonus,
835 p_relocation_bonus => l_imm_relocation_bonus ,
836 p_sf52_from_data => l_imm_sf52_from_data,
837 p_personal_info => l_imm_personal_info,
838 p_gov_awards_type => l_imm_gov_awards,
839 p_perf_appraisal_type => l_imm_perf_appraisal,
840 p_payroll_type => l_imm_payroll_type,
841 p_conduct_perf_type => l_imm_conduct_performance,
842 p_agency_code => l_agency_code,
843 p_agency_sf52 => l_imm_agency_sf52,
844 p_health_plan => l_health_plan,
845 p_entitlement => l_imm_entitlement,
846 p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
847 p_edp_pay => l_imm_edp_pay,
848 p_hazard_pay => l_imm_hazard_pay,
849 p_health_benefits => l_imm_health_benefits,
850 p_danger_pay => l_imm_danger_pay,
851 p_imminent_danger_pay => l_imm_imminent_danger_pay,
852 p_living_quarters_allow => l_imm_living_quarters_allow,
853 p_post_diff_amt => l_imm_post_diff_amt,
854 p_post_diff_percent => l_imm_post_diff_percent,
855 p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
856 p_supplemental_post_allow => l_imm_supplemental_post_allow,
857 p_temp_lodge_allow => l_imm_temp_lodge_allow,
858 p_premium_pay => l_imm_premium_pay,
859 p_retirement_annuity => l_imm_retirement_annuity,
860 p_severance_pay => l_imm_severance_pay,
861 p_thrift_saving_plan => l_imm_thrift_saving_plan,
862 p_retention_allow_review => l_imm_retention_allow_review,
863 p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
864 -- TAR 4646592.993
865 p_per_benefit_info => l_imm_per_benefit_info,
866 p_imm_retirement_info => l_imm_retirement_info --Bug# 7131104
867 );
868
869 -- GHR Validations
870
871 GHR_Validate_CHECK.Validate_CHECK
872 (
873 p_imm_pa_request_rec
874 ,l_imm_per_group1
875 ,l_imm_per_retained_grade
876 ,l_imm_per_sep_retire
877 ,l_imm_per_conversions
878 ,l_imm_per_uniformed_services
879 ,l_imm_pos_grp1
880 ,l_imm_pos_valid_grade
881 ,l_imm_loc_info
882 ,l_imm_sf52_from_data
883 ,l_imm_personal_info
884 ,l_agency_code
885 ,l_imm_gov_awards
886 ,l_imm_perf_appraisal
887 ,l_health_plan
888 ,l_imm_asg_non_sf52
889 ,l_imm_premium_pay
890 -- Bug#5036370
891 ,l_imm_per_service_oblig
892 ,l_imm_wgi --Bug 5527363
893 );
894
895 -- Call to CPDF Validation
896
897 hr_utility.set_location('Call to CPDF ' ,21);
898 hr_utility.set_location('Gov Award id ' || l_imm_gov_awards.group_award,1);
899 hr_utility.set_location('Prior Pay Plan ' || l_imm_sf52_from_data.pay_plan,1);
900 --hr_utility.set_location('Prior Pay Plan ' || l_imm_sf52_from_data.duty_station_id,1);
901
902 --Pradeep.
903 --Bypassing the edits for 850 and 855 actions.
904 If NOT ( p_imm_pa_request_rec.first_noa_code in ( '850', '855' ) or
905 ( p_imm_pa_request_rec.first_noa_code = '002' and
906 p_imm_pa_request_rec.second_noa_code in ( '850', '855' )
907 ) ) Then
908
909 ghr_ghrws52l.ghrws52l
910 ( p_pa_request_rec => p_imm_pa_request_rec
911 ,p_per_group1 => l_imm_per_group1
912 ,p_per_retained_grade => l_imm_per_retained_grade
913 ,p_per_sep_retire => l_imm_per_sep_retire
914 ,p_per_conversions => l_imm_per_conversions
915 ,p_per_uniformed_services => l_imm_per_uniformed_services
916 ,p_pos_grp1 => l_imm_pos_grp1
917 ,p_pos_valid_grade => l_imm_pos_valid_grade
918 ,p_loc_info => l_imm_loc_info
919 ,p_sf52_from_data => l_imm_sf52_from_data
920 ,p_personal_info => l_imm_personal_info
921 ,p_gov_awards_type => l_imm_gov_awards
922 ,p_perf_appraisal_type => l_imm_perf_appraisal
923 ,p_health_plan => l_health_plan
924 ,p_agency_code => l_agency_code
925 ,p_race_ethnic_info => l_imm_per_race_ethnic_info -- Bug 4724337 Race or National Origin changes
926 );
927
928 END IF;
929
930 -- Call position Rules
931
932 ghr_posn_rules.ghr_posn_drv
933 (
934 p_pos_grp1_type => l_imm_pos_grp1
935 ,p_pos_grp2_type => l_imm_pos_grp2
936 ,p_pos_oblig_type => l_imm_pos_oblig
937 ,p_pos_valid_grade_type => l_imm_pos_valid_grade
938 ,p_ghr_pa_requests => p_imm_pa_request_rec
939 ,p_asg_sf52_type => l_imm_asg_sf52
940 );
941
942
943 -- Calling creation of life events procedure
944 -- Apr 25,2005 Commented the call to create TSP Life events
945 /*
946 hr_utility.set_location(l_proc,120);
947 ghr_create_ptnl_life_events.create_ptnl_tsp_ler_for_per
948 (p_pa_request_rec => p_imm_pa_request_rec
949 );
950 */
951 hr_utility.set_location(l_proc,125);
952 ghr_create_ptnl_life_events.create_ptnl_ler_for_per
953 (p_pa_request_rec => p_imm_pa_request_rec
954 );
955 hr_utility.set_location(l_proc,125);
956
957 -- Do database update steps.
958 -- (procedures reside in GHR_SF52_DO_UPDATE package.
959
960 --
961 -- Calling Process family procedure
962
963 hr_utility.set_location(l_proc,70);
964
965 GHR_SF52_DO_UPDATE.Process_Family
966 (P_Pa_request_Rec => p_imm_pa_request_rec,
967 P_agency_code => l_agency_code );
968 hr_utility.set_location('assignmentid aft proc fam ' || to_char(p_imm_pa_request_rec.employee_assignment_id),2);
969 --
970 -- calling call_extra_info_api
971 --
972
973 hr_utility.set_location(l_proc,90);
974
975 --hr_utility.set_location('after populate rec gps ' ||to_char(l_imm_pos_gpr2.position_extra_info_id),1);
976 GHR_SF52_DO_UPDATE.call_extra_info_api
977 (P_PA_REQUEST_REC => p_imm_PA_REQUEST_REC
978 ,P_Asg_Sf52 => l_imm_Asg_Sf52
979 , P_Asg_non_Sf52 => l_imm_Asg_non_Sf52
980 , P_Asg_nte_dates => l_imm_Asg_nte_dates
981 , P_Per_Sf52 => l_imm_Per_Sf52
982 , P_Per_Group1 => l_imm_Per_Group1
983 , P_Per_Group2 => l_imm_Per_Group2
984 , P_Per_scd_info => l_imm_Per_scd_info
985 , P_Per_retained_grade => l_imm_Per_retained_grade
986 , P_Per_probations => l_imm_Per_probations
987 , P_Per_sep_retire => l_imm_Per_sep_retire
988 , P_Per_security => l_imm_Per_security
989 --Bug#4486823 RRR Changes
990 , P_Per_service_oblig => l_imm_Per_service_oblig
991 , P_Per_conversions => l_imm_Per_conversions
992 -- 4352589 BEN_EIT Changes
993 , p_per_benefit_info => l_imm_per_benefit_info
994 , P_Per_uniformed_services => l_imm_Per_uniformed_services
995 , P_Pos_oblig => l_imm_Pos_oblig
996 , P_Pos_Grp2 => l_imm_Pos_Grp2
997 , P_Pos_Grp1 => l_imm_Pos_Grp1
998 , P_Pos_valid_grade => l_imm_Pos_valid_grade
999 , P_Pos_car_prog => l_imm_Pos_car_prog
1000 , p_perf_appraisal => l_imm_perf_appraisal
1001 , p_conduct_performance => l_imm_conduct_performance
1002 , P_Loc_Info => l_imm_Loc_Info
1003 , P_generic_Extra_Info_Rec => l_imm_generic_Extra_Info_Rec
1004 , P_par_term_retained_grade => l_imm_par_term_retained_grade
1005 , p_per_race_ethnic_info => l_imm_per_race_ethnic_info -- Bug 4724337 Race or National Origin changes
1006 , p_ipa_benefits_cont => l_imm_ipa_benefits_cont --Bug # 6312144 RPA - EIT Benefits
1007 , p_retirement_info => l_imm_retirement_info
1008 );
1009
1010
1011
1012
1013
1014 --
1015 --
1016 -- Calling process_Salary_Info
1017 --
1018 --
1019 hr_utility.set_location(l_proc,100);
1020
1021 GHR_SF52_DO_UPDATE.Process_salary_Info
1022 (p_pa_request_rec => p_imm_pa_request_rec
1023 ,p_wgi => l_imm_wgi
1024 ,p_retention_allow_review => l_imm_retention_allow_review
1025 ,p_capped_other_pay => p_capped_other_pay );
1026
1027 --
1028 -- calling Process Non_salary info
1029 --
1030 -- Process_Non_Salary_Info
1031 hr_utility.set_location(l_proc,110);
1032 GHR_SF52_DO_UPDATE.Process_non_salary_Info
1033 (p_pa_request_rec => p_imm_pa_request_rec
1034 ,p_recruitment_bonus => l_imm_recruitment_bonus
1035 ,p_relocation_bonus => l_imm_relocation_bonus
1036 ,p_student_loan_repay => l_imm_student_loan_repay
1037 --Pradeep
1038 ,p_mddds_special_pay => l_imm_mddds_special_pay
1039 ,p_premium_pay_ind => l_imm_premium_pay_ind
1040
1041 ,p_gov_award => l_imm_gov_awards
1042 ,p_entitlement => l_imm_entitlement
1043 -- Bug#2759379 Added parameter p_fegli
1044 ,p_fegli => l_imm_fegli
1045 ,p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay
1046 -- Bug#3385386 Added parameter p_fta
1047 ,p_fta => l_imm_fta
1048 ,p_edp_pay => l_imm_edp_pay
1049 ,p_hazard_pay => l_imm_hazard_pay
1050 ,p_health_benefits => l_imm_health_benefits
1051 ,p_danger_pay => l_imm_danger_pay
1052 ,p_imminent_danger_pay => l_imm_imminent_danger_pay
1053 ,p_living_quarters_allow => l_imm_living_quarters_allow
1054 ,p_post_diff_amt => l_imm_post_diff_amt
1055 ,p_post_diff_percent => l_imm_post_diff_percent
1056 ,p_sep_maintenance_allow => l_imm_sep_maintenance_allow
1057 ,p_supplemental_post_allow => l_imm_supplemental_post_allow
1058 ,p_temp_lodge_allow => l_imm_temp_lodge_allow
1059 ,p_premium_pay => l_imm_premium_pay
1060 ,p_retirement_annuity => l_imm_retirement_annuity
1061 ,p_severance_pay => l_imm_severance_pay
1062 ,p_thrift_saving_plan => l_imm_thrift_saving_plan
1063 ,p_health_ben_pre_tax => l_imm_health_ben_pre_tax
1064 );
1065
1066
1067 -- Benefits processing
1068
1069 -- Get BG ID
1070 FOR l_cur_bg_id IN c_bg_id(p_imm_pa_request_rec.person_id, p_imm_pa_request_rec.effective_date) LOOP
1071 l_bg_id := l_cur_bg_id.bg_id;
1072 END LOOP;
1073
1074 hr_utility.set_location(l_proc,120);
1075 hr_utility.set_location(' noa_family_code ' || p_imm_pa_request_rec.noa_family_code,120);
1076 hr_utility.set_location(' Person ID ' || p_imm_pa_request_rec.person_id,120);
1077 hr_utility.set_location(' Eff. date ' || p_imm_pa_request_rec.effective_date,120);
1078 hr_utility.set_location(' p_business_group_id ' || l_bg_id,120);
1079 hr_utility.set_location(' p_pl_code ' || l_imm_health_benefits.health_plan,120);
1080 hr_utility.set_location(' p_opt_code ' || l_imm_health_benefits.enrollment,120);
1081 hr_utility.set_location(' p_pre_tax ' || l_imm_health_benefits.pre_tax_waiver,120);
1082 hr_utility.set_location(' pa_request_id ' || to_char(p_imm_pa_request_rec.pa_request_id),121);
1083
1084
1085 Populate_Remarks(p_pa_request_id => p_imm_pa_request_rec.pa_request_id
1086 ,p_to_pay_plan => p_imm_pa_request_rec.to_pay_plan
1087 ,p_to_pay_table_identifier => p_imm_pa_request_rec.to_pay_table_identifier
1088 ,p_person_id => p_imm_pa_request_rec.person_id
1089 ,p_pay_rate_determinant => p_imm_pa_request_rec.pay_rate_determinant
1090 ,p_effective_date => p_imm_pa_request_rec.effective_date
1091 ,p_first_noa_code => p_imm_pa_request_rec.first_noa_code
1092 ,p_second_noa_code => p_imm_pa_request_rec.second_noa_code
1093 ,p_leo_indicator => l_imm_Pos_Grp2.leo_position_indicator
1094 ,p_fegli => p_imm_pa_request_rec.fegli
1095 ); --6398933
1096
1097
1098
1099 --
1100 -- Agency Updates
1101 ghr_agency_update.ghr_agency_upd
1102 (p_pa_request_rec => p_imm_pa_request_rec,
1103 p_asg_sf52 => l_imm_asg_sf52,
1104 p_asg_non_sf52 => l_imm_asg_non_sf52,
1105 p_asg_nte_dates => l_imm_asg_nte_dates,
1106 p_per_sf52 => l_imm_per_sf52,
1107 p_per_group1 => l_imm_per_group1,
1108 p_per_group2 => l_imm_per_group2,
1109 p_per_scd_info => l_imm_per_scd_info,
1110 p_per_retained_grade => l_imm_per_retained_grade,
1111 p_per_probations => l_imm_per_probations,
1112 p_per_sep_Retire => l_imm_per_sep_retire,
1113 p_per_security => l_imm_per_security,
1114 p_per_conversions => l_imm_per_conversions,
1115 p_per_uniformed_services => l_imm_per_uniformed_services,
1116 p_pos_oblig => l_imm_pos_oblig,
1117 p_pos_grp2 => l_imm_pos_grp2,
1118 p_pos_grp1 => l_imm_pos_grp1,
1119 p_pos_valid_grade => l_imm_pos_valid_grade,
1120 p_pos_car_prog => l_imm_pos_car_prog,
1121 p_loc_info => l_imm_loc_info,
1122 p_wgi => l_imm_wgi,
1123 p_recruitment_bonus => l_imm_recruitment_bonus,
1124 p_relocation_bonus => l_imm_relocation_bonus ,
1125 p_sf52_from_data => l_imm_sf52_from_data,
1126 p_personal_info => l_imm_personal_info,
1127 p_gov_awards_type => l_imm_gov_awards,
1128 p_perf_appraisal_type => l_imm_perf_appraisal,
1129 p_payroll_type => l_imm_payroll_type,
1130 p_conduct_perf_type => l_imm_conduct_performance,
1131 p_agency_code => l_agency_code,
1132 p_agency_sf52 => l_imm_agency_sf52,
1133 p_entitlement => l_imm_entitlement,
1134 p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
1135 p_edp_pay => l_imm_edp_pay,
1136 p_hazard_pay => l_imm_hazard_pay,
1137 p_health_benefits => l_imm_health_benefits,
1138 p_danger_pay => l_imm_danger_pay,
1139 p_imminent_danger_pay => l_imm_imminent_danger_pay,
1140 p_living_quarters_allow => l_imm_living_quarters_allow,
1141 p_post_diff_amt => l_imm_post_diff_amt,
1142 p_post_diff_percent => l_imm_post_diff_percent,
1143 p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
1144 p_supplemental_post_allow => l_imm_supplemental_post_allow,
1145 p_temp_lodge_allow => l_imm_temp_lodge_allow,
1146 p_premium_pay => l_imm_premium_pay,
1147 p_retirement_annuity => l_imm_retirement_annuity,
1148 p_severance_pay => l_imm_severance_pay,
1149 p_thrift_saving_plan => l_imm_thrift_saving_plan,
1150 p_retention_allow_review => l_imm_retention_allow_review,
1151 p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
1152 p_per_benefit_info => l_imm_per_benefit_info,
1153 p_imm_retirement_info => l_imm_retirement_info --Bug# 7131104
1154 );
1155 --
1156 -- Start of Bug # 5195518 modified related to handling of Conversion to Appointment
1157 l_temp_appt := 'N';
1158 IF ghr_utility.is_ghr_ben_fehb = 'TRUE' and p_imm_pa_request_rec.noa_family_code IN ('CONV_APP') THEN
1159 IF l_imm_health_benefits.health_plan IS NOT NULL THEN
1160 IF l_imm_health_benefits.health_plan <> 'ZZ' AND l_imm_health_benefits.enrollment NOT IN ('W','X','Y','Z') THEN
1161
1162 for enr in get_current_enrollment (p_asg_id => p_imm_pa_request_rec.employee_assignment_id,
1163 p_business_group_id => l_bg_id ,
1164 p_effective_date => p_imm_pa_request_rec.effective_date)
1165 LOOP
1166 IF enr.enrollment = 'Z' then
1167 ghr_element_api.process_sf52_element
1168 (p_assignment_id => p_imm_pa_request_rec.employee_assignment_id
1169 ,p_element_name => 'Health Benefits'
1170 ,p_input_value_name1 => 'Enrollment'
1171 ,p_value1 => 'X'
1172 ,p_input_value_name2 => 'Health Plan'
1173 ,p_value2 => 'ZZ'
1174 ,p_effective_date => p_imm_pa_request_rec.effective_date
1175 ,p_process_warning => l_warning
1176 );
1177 l_temp_appt := 'Y';
1178 END IF;
1179 END LOOP;
1180 END IF;
1181 END IF;
1182 END IF;
1183
1184 -- Modified to move post_update_process after benefits processing as
1185 -- to maintain history
1186 -- Call Benefits processing Bug 4582970
1187 IF p_imm_pa_request_rec.noa_family_code in ('APP','CONV_APP','SEPARATION') THEN
1188 -- l_temp_appt := 'N';
1189 IF ghr_utility.is_ghr_ben_fehb = 'TRUE' THEN
1190 -- Will be uncommented later
1191 IF p_imm_pa_request_rec.noa_family_code in ('SEPARATION') THEN
1192 --Added this code to validate whether HB is already enrolled before processing separation life event
1193 -- Get Program ID
1194 FOR pgm_rec in c_get_pgm_id('Federal Employees Health Benefits', l_bg_id, p_imm_pa_request_rec.effective_date)
1195 LOOP -- Eff date and BG ID
1196 l_pgm_id := pgm_rec.pgm_id;
1197 EXIT;
1198 END LOOP;
1199
1200 IF l_pgm_id is not null THEN
1201 for emp_ben_rec in c_emp_in_ben(p_imm_pa_request_rec.person_id, l_pgm_id, p_imm_pa_request_rec.effective_date)
1202 LOOP
1203 l_exists := TRUE;
1204 exit;
1205 end loop;
1206 END IF;
1207 If l_exists then
1208 ghr_benefits_eit.ghr_benefits_fehb
1209 (errbuf => l_errbuf,
1210 retcode => l_retcode,
1211 p_person_id => p_imm_pa_request_rec.person_id,
1212 p_effective_date => fnd_date.date_to_canonical(p_imm_pa_request_rec.effective_date),
1213 p_business_group_id => l_bg_id,
1214 p_pl_code => NULL,
1215 p_opt_code => NULL,
1216 p_pre_tax => l_imm_health_benefits.pre_tax_waiver,
1217 p_assignment_id => p_imm_pa_request_rec.employee_assignment_id,
1218 p_temps_total_cost => l_imm_health_benefits.temps_total_cost,
1219 p_temp_appt => l_temp_appt
1220 );
1221 End If;
1222 ELSE
1223 IF l_imm_health_benefits.health_plan IS NOT NULL THEN
1224 IF l_imm_health_benefits.health_plan = 'ZZ' AND l_imm_health_benefits.enrollment IN ('W','X','Y','Z') THEN
1225 ghr_element_api.process_sf52_element
1226 (p_assignment_id => p_imm_pa_request_rec.employee_assignment_id
1227 ,p_element_name => 'Health Benefits'
1228 ,p_input_value_name1 => 'Enrollment'
1229 ,p_value1 => l_imm_health_benefits.enrollment
1230 ,p_input_value_name2 => 'Health Plan'
1231 ,p_value2 => l_imm_health_benefits.health_plan
1232 ,p_input_value_name3 => 'Temps Total Cost'
1233 ,p_value3 => l_imm_health_benefits.temps_total_cost
1234 ,p_input_value_name4 => 'Pre tax Waiver'
1235 ,p_value4 => l_imm_health_benefits.pre_tax_waiver
1236 ,p_effective_date => p_imm_pa_request_rec.effective_date
1237 ,p_process_warning => l_warning
1238 );
1239 ELSE
1240 ghr_benefits_eit.ghr_benefits_fehb
1241 (errbuf => l_errbuf,
1242 retcode => l_retcode,
1243 p_person_id => p_imm_pa_request_rec.person_id,
1244 p_effective_date => fnd_date.date_to_canonical(p_imm_pa_request_rec.effective_date),
1245 p_business_group_id => l_bg_id,
1246 p_pl_code => l_imm_health_benefits.health_plan,
1247 p_opt_code => l_imm_health_benefits.enrollment,
1248 p_pre_tax => l_imm_health_benefits.pre_tax_waiver,
1249 p_assignment_id => p_imm_pa_request_rec.employee_assignment_id,
1250 p_temps_total_cost => l_imm_health_benefits.temps_total_cost,
1251 p_temp_appt => l_temp_appt
1252 );
1253 END IF;
1254 END IF;
1255 END IF;
1256 END IF; -- IF ghr_utility.is_ghr_ben_fehb = 'TRUE
1257
1258 IF ghr_utility.is_ghr_ben_tsp = 'TRUE' THEN
1259 IF l_imm_thrift_saving_plan.amount > 0 THEN
1260 l_amount_or_rate := 'Amount';
1261 l_opt_val := l_imm_thrift_saving_plan.amount;
1262 ELSIF l_imm_thrift_saving_plan.rate > 0 THEN
1263 l_amount_or_rate := 'Percentage';
1264 l_opt_val := l_imm_thrift_saving_plan.rate;
1265 ELSE
1266 l_amount_or_rate := NULL;
1267 END IF;
1268 hr_utility.set_location(' tsp status ' || l_imm_thrift_saving_plan.status,120);
1269 hr_utility.set_location(' l_amount_or_rate ' || l_amount_or_rate,120);
1270 hr_utility.set_location(' l_opt_val ' || l_opt_val,120);
1271 IF l_amount_or_rate IS NOT NULL OR l_imm_thrift_saving_plan.status IN ('T','S') THEN
1272 ghr_benefits_eit.ghr_benefits_tsp(errbuf => l_errbuf,
1273 retcode => l_retcode,
1274 p_person_id => p_imm_pa_request_rec.person_id,
1275 p_effective_date => fnd_date.date_to_canonical(p_imm_pa_request_rec.effective_date),
1276 p_business_group_id => l_bg_id,
1277 p_tsp_status => l_imm_thrift_saving_plan.status,
1278 p_opt_name => l_amount_or_rate,
1279 p_opt_val => l_opt_val
1280 );
1281 END IF;
1282 END IF; -- IF ghr_utility.is_ghr_ben_tsp = 'TRUE' T
1283 END IF; -- IF p_imm_pa_request_rec.noa_family_code = 'APP'
1284 -- End of Bug # 5195518
1285
1286 ghr_history_api.post_update_process;
1287
1288 exception when others then
1289 --
1290 -- Reset IN OUT parameters and set OUT parameters
1291 -- /* Added for NOCOPY changes */
1292 p_imm_pa_request_rec := l_imm_pa_request_rec_in;
1293 p_capped_other_pay := l_capped_other_pay_in;
1294 raise;
1295
1296 End Process_Immediate_Update;
1297 --
1298
1299 -- Parameters for the foll.procedure
1300 -- p_pa_request_rec - To hold data of the current SF52 / in case of correction (correction + changes)
1301 -- p_pa_request_extra_info - pa_request_extra_info for the current pa_Request
1302 --
1303
1304 PROCEDURE MAIN
1305 (p_pa_request_rec in out nocopy ghr_pa_requests%rowtype,
1306 p_pa_request_ei_rec in ghr_pa_request_extra_info%rowtype,
1307 p_generic_ei_rec in ghr_pa_request_extra_info%rowtype,
1308 p_capped_other_pay in number default null
1309 )
1310 IS
1311
1312 l_proc varchar2(70) := 'Main';
1313 --l_rec ghr_pa_requests%rowtype ;
1314 l_notification_id ghr_pa_requests.pa_notification_id%type;
1315 l_person_type per_person_types.system_person_type%type;
1316 l_pa_request_rec ghr_pa_requests%rowtype;
1317 l_pa_request_rec_in ghr_pa_requests%rowtype; /* Added for NOCOPY changes */
1318 l_position_id per_positions.position_id%type;
1319 l_date_end per_positions.date_end%type;
1320
1321 Cursor c_sf50 is
1322 select pa_notification_id
1323 from ghr_pa_requests par
1324 where par.pa_request_id = p_pa_request_rec.pa_request_id;
1325
1326 Cursor c_pos_end_date is
1327 select date_end
1328 from hr_all_positions_f pos -- Venkat -- Position DT
1329 where pos.position_id = l_position_id
1330 and p_pa_request_rec.effective_date between
1331 pos.effective_start_date and pos.effective_end_date;
1332
1333
1334 l_capped_other_pay number;
1335
1336 Begin
1337 --
1338 hr_utility.set_location('Entering ' || l_proc,10);
1339 hr_utility.set_location(l_proc || p_pa_request_rec.to_position_number,12);
1340
1341
1342 l_pa_request_rec_in := p_pa_request_rec; /* Added for NOCOPY changes */
1343 g_effective_date := p_pa_request_rec.effective_date;
1344
1345
1346 l_capped_other_pay := p_capped_other_pay;
1347
1348 hr_utility.set_location(l_proc,20);
1349 l_pa_request_rec := p_pa_request_rec;
1350
1351 -- The foll. check ensures that the RPA is not processed
1352 -- if the TO_position(or From Position) is end dated
1353 If l_pa_request_rec.to_position_id is not null then
1354 l_position_id := l_pa_request_rec.to_position_id;
1355 Else
1356 l_position_id := l_pa_request_rec.from_position_id;
1357 End if;
1358 If l_position_id is not null then
1359 for c_pos_rec in c_pos_end_date loop
1360 l_date_end := c_pos_rec.date_end;
1361 end loop;
1362 if nvl(l_date_end,l_pa_request_rec.effective_date) < l_pa_request_rec.effective_date then
1363 -- raise error;
1364 hr_utility.set_message(8301,'GHR_38594_POSN_END_DATED');
1365 hr_utility.raise_error;
1366 end if;
1367 End if;
1368
1369 Process_Immediate_Update
1370 (p_imm_pa_request_rec => l_pa_request_rec,
1371 p_imm_pa_request_ei_rec => p_pa_request_ei_rec,
1372 p_imm_generic_ei_rec => p_generic_ei_rec,
1373 p_capped_other_pay => l_capped_other_pay
1374 );
1375 p_pa_request_rec := l_pa_request_rec;
1376 hr_utility.set_location('Assignment Id ' || to_char(p_pa_request_rec.employee_assignment_id),2);
1377
1378 --
1379 --
1380 hr_utility.set_location('Leaving ' ||l_proc,40);
1381
1382 exception when others then
1383 --
1384 -- Reset IN OUT parameters and set OUT parameters
1385 --
1386 p_pa_request_rec := l_pa_request_rec_in ; /* Added for NOCOPY changes */
1387 raise;
1388
1389 end MAIN;
1390
1391 end GHR_SF52_UPDATE;
1392