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