[Home] [Help]
PACKAGE BODY: APPS.GHR_SS_RPA_CREATION
Source
1 PACKAGE BODY GHR_SS_RPA_CREATION as
2 /* $Header: ghrssrpa.pkb 120.3 2011/04/26 10:51:54 vmididho noship $*/
3
4 g_package varchar2(32) := 'GHR_SS_RPA_CREATION.';
5
6 PROCEDURE CREATE_SF52_FEGLI(p_person_id in number,
7 p_assignment_id in number,
8 p_fegli_code in varchar2,
9 p_fegli_event_code in varchar2,
10 p_effective_date in date) IS
11
12 CURSOR chk_fegli_exists
13 IS
14 SELECT fegli,
15 pa_request_id,
16 object_version_number,
17 employee_first_name,
18 employee_last_name,
19 employee_middle_names
20 FROM GHR_PA_REQUESTS
21 WHERE person_id = p_person_id
22 and employee_assignment_id = p_assignment_id
23 and effective_date = p_effective_date
24 and first_noa_code = '881'
25 and pa_notification_id is null;
26
27 CURSOR get_rpa_det(p_pa_request_id in number)
28 IS
29 SELECT work_schedule
30 FROM GHR_PA_REQUESTS
31 WHERE pa_request_id = p_pa_request_id;
32
33 CURSOR del_exists_rem(p_pa_request_id in number)
34 IS
35 SELECT parem.pa_remark_id,
36 parem.object_version_number
37 FROM GHR_PA_REMARKS parem,GHR_REMARKS rem
38 WHERE parem.pa_request_id = p_pa_request_id
39 AND rem.remark_id = parem.remark_id
40 AND rem.code = 'B51';
41
42 CURSOR c_get_fegli_event_code
43 IS
44 SELECT pen_attribute1 fegli_event_code
45 FROM ben_prtt_enrt_rslt_f
46 WHERE person_id = p_person_id
47 -- AND pgm_id = p_pgm_id
48 AND p_effective_date between effective_start_date and effective_end_date
49 AND pen_attribute_category = 'GHR_FEGLI_EXTRA_INFO'
50 AND NVL(prtt_enrt_rslt_stat_cd,'@@') NOT IN ('BCKDT','VOIDD');
51
52 CURSOR c_get_ppl_data
53 IS
54 SELECT full_name, employee_number
55 FROM per_all_people_f
56 WHERE person_id = p_person_id
57 AND p_effective_date between effective_start_date
58 and effective_end_date;
59
60 l_curr_fegli ghr_pa_requests.fegli%type;
61 l_pa_request_id ghr_pa_requests.pa_request_id%type;
62 l_object_version_number ghr_pa_requests.object_version_number%type;
63 l_proc varchar2(72) := g_package || 'create_sf52_fegli';
64 l_log_text varchar2(2000);
65 l_full_name VARCHAR2(380);
66 l_employee_number per_people_f.employee_number%type;
67 l_employee_first_name per_people_f.first_name%type;
68 l_employee_last_name per_people_f.last_name%type;
69 l_employee_middle_names per_people_f.middle_names%type;
70 l_wrk_sch ghr_pa_requests.work_schedule%type;
71 l_fegli_element_value ghr_pa_requests.fegli%type;
72 l_elig_exp varchar2(30);
73 l_event_code varchar2(30);
74 l_multiple_error_flag boolean;
75 fegli_null_error exception;
76 fegli_upd_error exception;
77 fegli_rem_error exception;
78 fegli_ei_error exception;
79
80 BEGIN
81
82 ghr_mto_int.set_log_program_name('GHR_SS_FEGLI_RPA');
83
84 if p_fegli_code is NULL then
85 l_log_text := 'Error in create_sf52_fegli Fegli Code is Null';
86 raise fegli_null_error;
87 end if;
88
89 open chk_fegli_exists;
90 fetch chk_fegli_exists into l_curr_fegli,l_pa_request_id,l_object_version_number,
91 l_employee_last_name,l_employee_first_name,l_employee_middle_names;
92 l_full_name := l_employee_last_name||','|| l_employee_first_name||' '|| l_employee_middle_names;
93 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
94 (p_person_id => p_person_id,
95 p_effective_date => p_effective_date);
96 if chk_fegli_exists%notfound then
97
98 --Bug # 12397367 This has been added to avoid FEGLI RPA not to be created during Appointment
99 -- as 881 RPA is not required if existing element entry value and modified FEGLI code are same
100 ghr_api.retrieve_element_entry_value
101 (p_element_name => 'FEGLI',
102 p_input_value_name => 'FEGLI',
103 p_assignment_id => p_assignment_id,
104 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
105 p_value => l_fegli_element_value,
106 p_multiple_error_flag => l_multiple_error_flag
107 );
108
109 IF NVL(l_fegli_element_value,'@@') <> p_fegli_code THEN
110 FOR get_ppl_data in c_get_ppl_data
111 LOOP
112 l_full_name := get_ppl_data.full_name;
113 l_employee_number := get_ppl_data.employee_number;
114 END LOOP;
115
116 CREATE_SF52_FEGLI_RPA(p_person_id => p_person_id,
117 p_assignment_id => p_assignment_id,
118 p_fegli_code => p_fegli_code,
119 p_effective_date => p_effective_date,
120 p_pa_request_id => l_pa_request_id);
121 END IF;
122
123 else
124 if NVL(p_fegli_code,'@@') <> NVL(l_curr_fegli,'@@') then
125
126 BEGIN
127 UPDATE_SF52_FEGLI_RPA(p_pa_request_id => l_pa_request_id,
128 p_person_id => p_person_id,
129 p_assignment_id => p_assignment_id,
130 p_fegli_code => p_fegli_code,
131 p_effective_date => p_effective_date);
132 EXCEPTION
133 WHEN OTHERS THEN
134 l_log_text := 'Error in ghr_par_upd.upd for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
135 || '; Error: '||sqlerrm(sqlcode);
136 raise fegli_upd_error;
137 END;
138 end if;
139 end if;
140 close chk_fegli_exists;
141
142 open get_rpa_det(p_pa_request_id => l_pa_request_id);
143 fetch get_rpa_det into l_wrk_sch;
144 close get_rpa_det;
145
146 BEGIN
147 --Creating Remarks
148 IF (instr(p_fegli_code,'A') < 2 OR instr(p_fegli_code,'B') < 2) AND
149 l_wrk_sch = 'P' THEN
150
151 CREATE_PA_REMARKS(p_pa_request_id => l_pa_request_id,
152 p_effective_date => p_effective_date,
153 p_remark_code => 'B51');
154 ELSE
155 FOR cur_pa_rem IN del_exists_rem(p_pa_request_id => l_pa_request_id)
156 LOOP
157 ghr_pa_remarks_api.delete_pa_remarks
158 (p_pa_remark_id => cur_pa_rem.pa_remark_id
159 ,p_object_version_number => cur_pa_rem.object_version_number);
160 END LOOP;
161 END IF;
162 EXCEPTION
163 WHEN OTHERS THEN
164 l_log_text := 'Error in creating remarks for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
165 || '; Error: '||sqlerrm(sqlcode);
166 raise fegli_rem_error;
167 END;
168
169 --Eligibility Expiration
170 BEGIN
171 ghr_api.retrieve_element_entry_value
172 (p_element_name => 'FEGLI',
173 p_input_value_name => 'Eligibility Expiration',
174 p_assignment_id => p_assignment_id,
175 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
176 p_value => l_elig_exp,
177 p_multiple_error_flag => l_multiple_error_flag
178 );
179
180 populate_pa_request_extra_info(p_pa_request_id => l_pa_request_id,
181 p_info_type => 'GHR_US_PAR_FEGLI',
182 p_attribute1 => l_elig_exp,
183 p_attribute2 => p_fegli_event_code
184 );
185 EXCEPTION
186 WHEN OTHERS then
187 l_log_text := 'Error in creating PA Request Extra Information for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
188 || '; Error: '||sqlerrm(sqlcode);
189 raise fegli_ei_error;
190 END;
191
192 EXCEPTION
193 WHEN fegli_null_error then
194 hr_utility.set_location('Error occured in ' || l_proc , 1);
195 IF l_log_text is NULL THEN
196 l_log_text := 'Cannot Create/Update SS Fegli RPA';
197 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
198 END IF;
199
200 l_log_text := substr(l_log_text,1,2000);
201
202 hr_utility.set_location('before creating entry in log file',10);
203 ghr_mto_int.log_message( p_procedure => 'Create/Upd SS Fegli RPA'
204 ,p_message => l_log_text);
205
206 hr_utility.set_location('created entry in log file',20);
207
208 WHEN fegli_upd_error then
209 hr_utility.set_location('Error occured in ' || l_proc , 1);
210 IF l_log_text is NULL THEN
211 l_log_text := 'Error while Updating the PA Request Rec. ';
212 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
213 END IF;
214
215 l_log_text := substr(l_log_text,1,2000);
216
217 hr_utility.set_location('before creating entry in log file',10);
218 ghr_mto_int.log_message( p_procedure => 'While Updating FEGLI RPA'
219 ,p_message => l_log_text);
220
221 WHEN fegli_ei_error then
222 hr_utility.set_location('Error occured in ' || l_proc , 1);
223 IF l_log_text is NULL THEN
224 l_log_text := 'Error while Creating PA Request Extra Information ';
225 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
226 END IF;
227
228 l_log_text := substr(l_log_text,1,2000);
229
230 hr_utility.set_location('before creating entry in log file',10);
231 ghr_mto_int.log_message( p_procedure => 'While Creating Extra Info'
232 ,p_message => l_log_text);
233
234 hr_utility.set_location('created entry in log file',20);
235
236
237
238 END CREATE_SF52_FEGLI;
239
240 PROCEDURE CREATE_SF52_FEGLI_RPA(p_person_id in number,
241 p_assignment_id in number,
242 p_fegli_code in varchar2,
243 p_effective_date in date,
244 p_pa_request_id out NOCOPY number) IS
245 CURSOR get_person_dtls
246 IS
247 SELECT first_name,
248 last_name,
249 middle_names,
250 national_identifier,
251 date_of_birth,
252 employee_number
253 FROM PER_PEOPLE_F
254 WHERE person_id = p_person_id
255 AND p_effective_date between effective_start_date and effective_end_date;
256
257 CURSOR get_assignment_dtls
258 IS
259 SELECT position_id,
260 assignment_id,
261 location_id
262 FROM per_assignments_f
263 WHERE person_id = p_person_id
264 AND p_effective_date between effective_start_date and effective_end_date;
265 CURSOR c_routing_history(p_pa_request_id in number)
266 IS
267 SELECT prh.pa_routing_history_id,
268 prh.object_version_number
269 FROM ghr_pa_routing_history prh
270 WHERE prh.pa_request_id = p_pa_request_id
271 ORDER BY 1 desc;
272
273 l_sf52_rec ghr_pa_requests%rowtype;
274 l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
275 l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
276 l_2_prh_object_version_number ghr_pa_requests.object_version_number%type;
277 l_2_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
278 l_object_version_number ghr_pa_remarks.object_version_number%type;
279 l_full_name VARCHAR2(380);
280 l_employee_number per_people_f.employee_number%type;
281 l_routing_group_id ghr_pa_requests.routing_group_id%type;
282 l_old_fegli ghr_pa_requests.fegli%type;
283 fegli_error exception;
284 l_log_text varchar2(2000);
285 l_dummy_char varchar2(150);
286 l_personnel_officer_name per_people_f.full_name%type;
287 l_groupbox_id ghr_groupboxes.groupbox_id%type;
288 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
289 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
290 l_scd_leave varchar2(50);
291 l_multiple_error_flag boolean;
292 l_proc varchar2(72) := g_package || 'create_sf52_fegli_rpa';
293
294 BEGIN
295
296 l_sf52_rec := NULL;
297
298 l_sf52_rec.person_id := p_person_id;
299 l_sf52_rec.employee_assignment_id := p_assignment_id;
300 l_sf52_rec.effective_date := p_effective_date;
301
302 l_sf52_rec.first_noa_code := '881';
303 l_sf52_rec.noa_family_code := 'CHG_FEGLI';
304 l_sf52_rec.fegli :=p_fegli_code;
305
306
307
308
309
310 FOR c_person_dtls IN get_person_dtls
311 LOOP
312 l_sf52_rec.employee_first_name := c_person_dtls.first_name;
313 l_sf52_rec.employee_last_name := c_person_dtls.last_name;
314 l_sf52_rec.employee_middle_names := c_person_dtls.middle_names;
315 l_sf52_rec.employee_national_identifier := c_person_dtls.national_identifier;
316 l_sf52_rec.employee_date_of_birth := c_person_dtls.date_of_birth;
317 EXIT;
318 END LOOP;
319
320 l_full_name := l_sf52_rec.employee_last_name||','|| l_sf52_rec.employee_first_name||' '|| l_sf52_rec.employee_middle_names;
321 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
322 (p_person_id => l_sf52_rec.person_id,
323 p_effective_date => l_sf52_rec.effective_date);
324
325
326 BEGIN
327 ghr_pa_requests_pkg.get_sf52_asg_ddf_details(l_sf52_rec.employee_assignment_id,
328 p_effective_date,
329 l_sf52_rec.tenure,
330 l_sf52_rec.annuitant_indicator,
331 l_sf52_rec.pay_rate_determinant,
332 l_sf52_rec.work_schedule,
333 l_sf52_rec.part_time_hours);
334 EXCEPTION
335 WHEN OTHERS THEN
336 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details'||
337 'Err is '||sqlerrm(sqlcode),20);
338 l_log_text := 'Error in get_sf52_asgddf_details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
339 || '; SQL Error: '|| sqlerrm(sqlcode);
340 raise fegli_error;
341 END;
342
343 FOR c_assg_dtls IN get_assignment_dtls
344 LOOP
345 l_sf52_rec.from_position_id := c_assg_dtls.position_id;
346 l_sf52_rec.duty_station_location_id := c_assg_dtls.location_id;
347 -- l_sf52_rec.business_group_id := c_assg_dtls.business_group_id;
348 -- l_sf52_rec.assignment_status_type_id := c_assg_dtls.assignment_status_type_id;
349 EXIT;
350 END LOOP;
351
352
353 BEGIN
354 ghr_pa_requests_pkg.get_SF52_pos_ddf_details
355 (p_position_id => l_sf52_rec.from_position_id
356 ,p_date_Effective => l_sf52_rec.effective_date
357 ,p_flsa_category => l_sf52_rec.flsa_category
358 ,p_bargaining_unit_status => l_sf52_rec.bargaining_unit_status
359 ,p_work_schedule => l_sf52_rec.work_schedule
360 ,p_functional_class => l_sf52_rec.functional_class
361 ,p_supervisory_status => l_sf52_rec.supervisory_status
362 ,p_position_occupied => l_sf52_rec.position_occupied
363 ,p_appropriation_code1 => l_sf52_rec.appropriation_code1
364 ,p_appropriation_code2 => l_sf52_rec.appropriation_code2
365 ,p_personnel_office_id => l_sf52_rec.personnel_office_id
366 ,p_office_symbol => l_sf52_rec.from_office_symbol
367 ,p_part_time_hours => l_sf52_rec.part_time_hours);
368 EXCEPTION
369 WHEN OTHERS THEN
370 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_SF52_pos_ddf_details'
371 || 'Err is' ||sqlerrm(sqlcode),20);
372 l_log_text := 'Error in get_SF52_pos_ddf_details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
373 || '; SQL Error: '|| sqlerrm(sqlcode);
374 raise fegli_error;
375 END;
376
377
378
379 BEGIN
380 ghr_api.sf52_from_data_elements
381 (p_person_id => l_sf52_rec.person_id
382 ,p_assignment_id => l_sf52_rec.employee_assignment_id
383 ,p_effective_date => l_sf52_rec.effective_date
384 ,p_altered_pa_request_id => null
385 ,p_noa_id_corrected => null
386 ,p_pa_history_id => null
387 ,p_position_title => l_sf52_rec.from_position_title
388 ,p_position_number => l_sf52_rec.from_position_number
389 ,p_position_seq_no => l_sf52_rec.from_position_seq_no
390 ,p_pay_plan => l_sf52_rec.from_pay_plan
391 ,p_job_id => l_sf52_rec.to_job_id
392 ,p_occ_code => l_sf52_rec.from_occ_code
393 ,p_grade_id => l_sf52_rec.to_grade_id
394 ,p_grade_or_level => l_sf52_rec.from_grade_or_level
395 ,p_step_or_rate => l_sf52_rec.from_step_or_rate
396 ,p_total_salary => l_sf52_rec.from_total_salary
397 ,p_pay_basis => l_sf52_rec.from_pay_basis
398 ,p_pay_table_identifier => l_sf52_rec.from_pay_table_identifier
399 ,p_basic_pay => l_sf52_rec.from_basic_pay
400 ,p_locality_adj => l_sf52_rec.from_locality_adj
401 ,p_adj_basic_pay => l_sf52_rec.from_adj_basic_pay
402 ,p_other_pay => l_sf52_rec.from_other_pay_amount
403 ,p_au_overtime => l_sf52_rec.to_au_overtime
404 ,p_auo_premium_pay_indicator => l_sf52_rec.to_auo_premium_pay_indicator
405 ,p_availability_pay => l_sf52_rec.to_availability_pay
406 ,p_ap_premium_pay_indicator => l_sf52_rec.to_ap_premium_pay_indicator
407 ,p_retention_allowance => l_sf52_rec.to_retention_allowance
408 ,p_retention_allow_percentage=> l_sf52_rec.to_retention_allow_percentage
409 ,p_supervisory_differential => l_sf52_rec.to_supervisory_differential
410 ,p_supervisory_diff_percentage => l_sf52_rec.to_supervisory_diff_percentage
411 ,p_staffing_differential => l_sf52_rec.to_staffing_differential
412 ,p_staffing_diff_percentage => l_sf52_rec.to_staffing_diff_percentage
413 ,p_organization_id => l_sf52_rec.to_organization_id
414 ,p_position_org_line1 => l_sf52_rec.from_position_org_line1
415 ,p_position_org_line2 => l_sf52_rec.from_position_org_line2
416 ,p_position_org_line3 => l_sf52_rec.from_position_org_line3
417 ,p_position_org_line4 => l_sf52_rec.from_position_org_line4
418 ,p_position_org_line5 => l_sf52_rec.from_position_org_line5
419 ,p_position_org_line6 => l_sf52_rec.from_position_org_line6
420 ,p_position_id => l_sf52_rec.from_position_id
421 ,p_duty_station_location_id => l_dummy_char
422 ,p_pay_rate_determinant => l_dummy_char
423 ,p_work_schedule => l_dummy_char
424 );
425 EXCEPTION
426 WHEN OTHERS THEN
427 hr_utility.set_location('Error in ghr_api.sf52_from_data_elements'||
428 'Err is'||sqlerrm(sqlcode),20);
429 l_log_text := 'Error in sf52_from_data_elements for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
430 || '; SQL Error: '|| sqlerrm(sqlcode);
431 raise fegli_error;
432 END;
433
434 l_sf52_rec.to_position_title := l_sf52_rec.from_position_title;
435 l_sf52_rec.to_position_number := l_sf52_rec.from_position_number;
436 l_sf52_rec.to_position_seq_no := l_sf52_rec.from_position_seq_no;
437 l_sf52_rec.to_pay_plan := l_sf52_rec.from_pay_plan;
438 l_sf52_rec.to_occ_code := l_sf52_rec.from_occ_code;
439 l_sf52_rec.to_grade_or_level := l_sf52_rec.from_grade_or_level;
440 l_sf52_rec.to_step_or_rate := l_sf52_rec.from_step_or_rate;
441 l_sf52_rec.to_total_salary := l_sf52_rec.from_total_salary;
442 l_sf52_rec.to_pay_basis := l_sf52_rec.from_pay_basis;
443 l_sf52_rec.to_pay_table_identifier := l_sf52_rec.from_pay_table_identifier;
444 l_sf52_rec.to_basic_pay := l_sf52_rec.from_basic_pay;
445 l_sf52_rec.to_locality_adj := l_sf52_rec.from_locality_adj;
446 l_sf52_rec.to_adj_basic_pay := l_sf52_rec.from_adj_basic_pay;
447 l_sf52_rec.to_other_pay_amount := l_sf52_rec.from_other_pay_amount;
448 l_sf52_rec.to_position_org_line1 := l_sf52_rec.from_position_org_line1;
449 l_sf52_rec.to_position_org_line2 := l_sf52_rec.from_position_org_line2;
450 l_sf52_rec.to_position_org_line3 := l_sf52_rec.from_position_org_line3;
451 l_sf52_rec.to_position_org_line4 := l_sf52_rec.from_position_org_line4;
452 l_sf52_rec.to_position_org_line5 := l_sf52_rec.from_position_org_line5;
453 l_sf52_rec.to_position_org_line6 := l_sf52_rec.from_position_org_line6;
454 l_sf52_rec.to_position_id := l_sf52_rec.from_position_id;
455
456 BEGIN
457 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
458 (p_location_id => l_sf52_rec.duty_station_location_id
459 ,p_duty_station_id => l_sf52_rec.duty_station_id);
460 EXCEPTION
461 WHEN OTHERS THEN
462 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_SF52_loc_ddf_details'||
463 'Err is'||sqlerrm(sqlcode),20);
464 l_log_text := 'Error in get_SF52_loc_ddf_details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
465 || '; SQL Error: '||sqlerrm(sqlcode);
466 raise fegli_error;
467 END;
468
469 BEGIN
470 ghr_pa_requests_pkg.get_duty_station_details
471 (p_duty_station_id => l_sf52_rec.duty_station_id
472 ,p_effective_date => l_sf52_rec.effective_date
473 ,p_duty_station_code => l_sf52_rec.duty_station_code
474 ,p_duty_station_desc => l_sf52_rec.duty_station_desc);
475 EXCEPTION
476 WHEN OTHERS THEN
477 hr_utility.set_location('Error in ghr_pa_requests_pkg.get_duty_station_details'||
478 'Err is '||sqlerrm(sqlcode),20);
479 l_log_text := 'Error in get_duty_station_details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
480 || '; SQL Error: '|| sqlerrm(sqlcode);
481 raise fegli_error;
482 END;
483
484 BEGIN
485 ghr_api.return_education_Details
486 (p_person_id => l_sf52_rec.person_id,
487 p_effective_date => l_sf52_rec.effective_date,
488 p_education_level => l_sf52_rec.education_level,
489 p_academic_discipline => l_sf52_rec.academic_discipline,
490 p_year_degree_attained => l_sf52_rec.year_degree_attained
491 );
492 EXCEPTION
493 WHEN OTHERS THEN
494 hr_utility.set_location('Error in ghr_api.return_education_Details'||
495 'Err is '||sqlerrm(sqlcode),20);
496 l_log_text := 'Error in return_education_Details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
497 || '; SQL Error:'|| sqlerrm(sqlcode);
498 raise fegli_error;
499 END;
500
501 BEGIN
502 ghr_pa_requests_pkg.get_SF52_person_ddf_details
503 (p_person_id => l_sf52_rec.person_id,
504 p_date_effective => l_sf52_rec.effective_date,
505 p_citizenship => l_sf52_rec.citizenship,
506 p_veterans_preference => l_sf52_rec.veterans_preference,
507 p_veterans_pref_for_rif => l_sf52_rec.veterans_pref_for_rif,
508 p_veterans_status => l_sf52_rec.veterans_status,
509 p_scd_leave => l_scd_leave
510 );
511 EXCEPTION
512 WHEN OTHERS THEN
513 hr_utility.set_location('Error in ghr_pa_requests_pkg.get_SF52_person_ddf_details'||
514 'Err is '||sqlerrm(sqlcode),20);
515 l_log_text := 'Error in get_SF52_person_ddf_details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
516 || '; SQL Error:'|| sqlerrm(sqlcode);
517 raise fegli_error;
518 END;
519
520 -- Annuitant_indicator
521 l_sf52_rec.annuitant_indicator_desc := ghr_pa_requests_pkg.get_lookup_meaning
522 (800
523 ,'GHR_US_ANNUITANT_INDICATOR'
524 ,l_sf52_rec.annuitant_indicator
525 );
526
527 --WORK_SCHEDULE
528 l_sf52_rec.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning
529 (800
530 ,'GHR_US_WORK_SCHEDULE'
531 ,l_sf52_rec.work_schedule
532 );
533
534 l_sf52_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
535 (800
536 ,'GHR_US_FEGLI'
537 ,l_sf52_rec.fegli
538 );
539
540 ghr_api.retrieve_element_entry_value
541 (p_element_name => 'Retirement Plan'
542 ,p_input_value_name => 'Plan'
543 ,p_assignment_id => l_sf52_rec.employee_assignment_id
544 ,p_effective_date => l_sf52_rec.effective_date
545 ,p_value => l_sf52_rec.retirement_plan
546 ,p_multiple_error_flag => l_multiple_error_flag
547 );
548
549
550 l_sf52_rec.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning
551 (800
552 ,'GHR_US_RETIREMENT_PLAN'
553 ,l_sf52_rec.retirement_plan
554 );
555 BEGIN
556 ghr_mass_actions_pkg.get_personnel_officer_name
557 (p_personnel_office_id => l_sf52_rec.personnel_office_id,
558 p_person_full_name => l_personnel_officer_name,
559 p_approving_off_work_title => l_approving_off_work_title);
560 EXCEPTION
561 WHEN OTHERS THEN
562 hr_utility.set_location('Error in ghr_mass_actions_pkg.get_personnel_officer_name'||
563 'Err is '||sqlerrm(sqlcode),20);
564 l_log_text := 'Error in get_personnel_officer_name for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
565 || '; SQL Error:'|| sqlerrm(sqlcode);
566 raise fegli_error;
567 END;
568
569
570
571 l_sf52_rec.additional_info_person_id := NULL;
572 l_sf52_rec.additional_info_tel_number := NULL;
573 l_sf52_rec.Proposed_Effective_Date := NULL;
574 l_sf52_rec.Proposed_Effective_ASAP_flag := 'N';
575 l_sf52_rec.requested_by_person_id := NULL;
576 l_sf52_rec.requested_by_title := NULL;
577 l_sf52_rec.requested_date := NULL;
578 l_sf52_rec.authorized_by_person_id := NULL;
579 l_sf52_rec.authorized_by_title := NULL;
580 l_sf52_rec.concurrence_Date := NULL;
581 l_sf52_rec.forwarding_address_line1 := NULL;
582 l_sf52_rec.forwarding_address_line2 := NULL;
583 l_sf52_rec.forwarding_address_line3 := NULL;
584 l_sf52_rec.forwarding_country := NULL;
585 l_sf52_rec.forwarding_country_short_name := NULL;
586 l_sf52_rec.forwarding_postal_code := NULL;
587 l_sf52_rec.forwarding_region_2 := NULL;
588 l_sf52_rec.forwarding_town_or_city := NULL;
589 l_sf52_rec.altered_pa_request_id := NULL;
590
591 l_sf52_rec.service_comp_date := fnd_date.canonical_to_date(l_scd_leave);
592
593 ---LAC Details
594
595 l_sf52_rec.first_action_la_code1 := 'DPM';
596 l_sf52_rec.first_action_la_desc1 := ghr_pa_requests_pkg.get_lookup_description(800,'GHR_US_LEGAL_AUTHORITY',l_sf52_rec.first_action_la_code1);
597
598 ---NOA Desc
599 BEGIN
600 ghr_mass_actions_pkg.get_noa_id_desc
601 (p_noa_code => l_sf52_rec.first_noa_code,
602 p_effective_date => l_sf52_rec.effective_date,
603 p_noa_id => l_sf52_rec.first_noa_id,
604 p_noa_desc => l_sf52_rec.first_noa_desc
605 );
606 EXCEPTION
607 WHEN OTHERS THEN
608 hr_utility.set_location('Error in ghr_mass_actions_pkg.get_noa_id_desc'||
609 'Err is '||sqlerrm(sqlcode),20);
610 l_log_text := 'Error in get_noa_id_desc for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
611 || '; SQL Error:'|| sqlerrm(sqlcode);
612 raise fegli_error;
613 END;
614
615 l_sf52_rec.custom_pay_calc_flag := 'N';
616
617
618
619 BEGIN
620 ghr_sf52_api.create_sf52(
621 p_noa_family_code => l_sf52_rec.noa_family_code,
622 p_proposed_effective_asap_flag => l_sf52_rec.proposed_effective_asap_flag,
623 p_academic_discipline => l_sf52_rec.academic_discipline,
624 p_additional_info_person_id => l_sf52_rec.additional_info_person_id,
625 p_additional_info_tel_number => l_sf52_rec.additional_info_tel_number,
626 p_annuitant_indicator => l_sf52_rec.annuitant_indicator,
627 p_annuitant_indicator_desc => l_sf52_rec.annuitant_indicator_desc,
628 p_appropriation_code1 => l_sf52_rec.appropriation_code1,
629 p_appropriation_code2 => l_sf52_rec.appropriation_code2,
630 p_authorized_by_person_id => l_sf52_rec.authorized_by_person_id,
631 p_authorized_by_title => l_sf52_rec.authorized_by_title,
632 p_award_amount => l_sf52_rec.award_amount,
633 p_award_uom => l_sf52_rec.award_uom,
634 p_bargaining_unit_status => l_sf52_rec.bargaining_unit_status,
635 p_citizenship => l_sf52_rec.citizenship,
636 p_concurrence_date => l_sf52_rec.concurrence_date,
637 p_custom_pay_calc_flag => l_sf52_rec.custom_pay_calc_flag,
638 p_duty_station_code => l_sf52_rec.duty_station_code,
639 p_duty_station_desc => l_sf52_rec.duty_station_desc,
640 p_duty_station_id => l_sf52_rec.duty_station_id,
641 p_duty_station_location_id => l_sf52_rec.duty_station_location_id,
642 p_education_level => l_sf52_rec.education_level,
643 p_effective_date => l_sf52_rec.effective_date,
644 p_employee_assignment_id => l_sf52_rec.employee_assignment_id,
645 p_employee_date_of_birth => l_sf52_rec.employee_date_of_birth,
646 p_employee_first_name => l_sf52_rec.employee_first_name,
647 p_employee_last_name => l_sf52_rec.employee_last_name,
648 p_employee_middle_names => l_sf52_rec.employee_middle_names,
649 p_employee_national_identifier => l_sf52_rec.employee_national_identifier,
650 p_fegli => l_sf52_rec.fegli,
651 p_fegli_desc => l_sf52_rec.fegli_desc,
652 p_first_action_la_code1 => l_sf52_rec.first_action_la_code1,
653 p_first_action_la_code2 => l_sf52_rec.first_action_la_code2,
654 p_first_action_la_desc1 => l_sf52_rec.first_action_la_desc1,
655 p_first_action_la_desc2 => l_sf52_rec.first_action_la_desc2,
656 p_first_noa_code => l_sf52_rec.first_noa_code,
657 p_first_noa_desc => l_sf52_rec.first_noa_desc,
658 p_first_noa_id => l_sf52_rec.first_noa_id,
659 p_first_noa_information1 => l_sf52_rec.first_noa_information1,
660 p_first_noa_pa_request_id => l_sf52_rec.first_noa_pa_request_id,
661 p_flsa_category => l_sf52_rec.flsa_category,
662 p_forwarding_address_line1 => l_sf52_rec.forwarding_address_line1,
663 p_forwarding_address_line2 => l_sf52_rec.forwarding_address_line2,
664 p_forwarding_address_line3 => l_sf52_rec.forwarding_address_line3,
665 p_forwarding_country => l_sf52_rec.forwarding_country,
666 p_forwarding_country_short_nam => l_sf52_rec.forwarding_country_short_name,
667 p_forwarding_postal_code => l_sf52_rec.forwarding_postal_code,
668 p_forwarding_region_2 => l_sf52_rec.forwarding_region_2,
669 p_forwarding_town_or_city => l_sf52_rec.forwarding_town_or_city,
670 p_from_adj_basic_pay => l_sf52_rec.from_adj_basic_pay,
671 p_from_basic_pay => l_sf52_rec.from_basic_pay,
672 p_from_grade_or_level => l_sf52_rec.from_grade_or_level,
673 p_from_locality_adj => l_sf52_rec.from_locality_adj,
674 p_from_occ_code => l_sf52_rec.from_occ_code,
675 p_from_other_pay_amount => l_sf52_rec.from_other_pay_amount,
676 p_from_pay_basis => l_sf52_rec.from_pay_basis,
677 p_from_pay_plan => l_sf52_rec.from_pay_plan,
678 p_from_position_id => l_sf52_rec.from_position_id,
679 p_from_position_org_line1 => l_sf52_rec.from_position_org_line1,
680 p_from_position_org_line2 => l_sf52_rec.from_position_org_line2,
681 p_from_position_org_line3 => l_sf52_rec.from_position_org_line3,
682 p_from_position_org_line4 => l_sf52_rec.from_position_org_line4,
683 p_from_position_org_line5 => l_sf52_rec.from_position_org_line5,
684 p_from_position_org_line6 => l_sf52_rec.from_position_org_line6,
685 p_from_position_number => l_sf52_rec.from_position_number,
686 p_from_position_seq_no => l_sf52_rec.from_position_seq_no,
687 p_from_position_title => l_sf52_rec.from_position_title,
688 p_from_step_or_rate => l_sf52_rec.from_step_or_rate,
689 p_from_total_salary => l_sf52_rec.from_total_salary,
690 p_functional_class => l_sf52_rec.functional_class,
691 p_notepad => l_sf52_rec.notepad,
692 p_part_time_hours => l_sf52_rec.part_time_hours,
693 p_pay_rate_determinant => l_sf52_rec.pay_rate_determinant,
694 p_person_id => l_sf52_rec.person_id,
695 p_position_occupied => l_sf52_rec.position_occupied,
696 p_proposed_effective_date => l_sf52_rec.proposed_effective_date,
697 p_requested_by_person_id => l_sf52_rec.requested_by_person_id,
698 p_requested_by_title => l_sf52_rec.requested_by_title,
699 p_requested_date => l_sf52_rec.requested_date,
700 p_requesting_office_remarks_de => l_sf52_rec.requesting_office_remarks_desc,
701 p_requesting_office_remarks_fl => l_sf52_rec.requesting_office_remarks_flag,
702 p_resign_and_retire_reason_des => l_sf52_rec.resign_and_retire_reason_desc,
703 p_retirement_plan => l_sf52_rec.retirement_plan,
704 p_retirement_plan_desc => l_sf52_rec.retirement_plan_desc,
705 p_service_comp_date => l_sf52_rec.service_comp_date,
706 p_supervisory_status => l_sf52_rec.supervisory_status,
707 p_tenure => l_sf52_rec.tenure,
708 p_to_adj_basic_pay => l_sf52_rec.to_adj_basic_pay,
709 p_to_basic_pay => l_sf52_rec.to_basic_pay,
710 p_to_grade_id => l_sf52_rec.to_grade_id,
711 p_to_grade_or_level => l_sf52_rec.to_grade_or_level,
712 p_to_job_id => l_sf52_rec.to_job_id,
713 p_to_locality_adj => l_sf52_rec.to_locality_adj,
714 p_to_occ_code => l_sf52_rec.to_occ_code,
715 p_to_organization_id => l_sf52_rec.to_organization_id,
716 p_to_other_pay_amount => l_sf52_rec.to_other_pay_amount,
717 p_to_au_overtime => l_sf52_rec.to_au_overtime,
718 p_to_auo_premium_pay_indicator => l_sf52_rec.to_auo_premium_pay_indicator,
719 p_to_availability_pay => l_sf52_rec.to_availability_pay,
720 p_to_ap_premium_pay_indicator => l_sf52_rec.to_ap_premium_pay_indicator,
721 p_to_retention_allowance => l_sf52_rec.to_retention_allowance,
722 p_to_supervisory_differential => l_sf52_rec.to_supervisory_differential,
723 p_to_staffing_differential => l_sf52_rec.to_staffing_differential,
724 p_to_pay_basis => l_sf52_rec.to_pay_basis,
725 p_to_pay_plan => l_sf52_rec.to_pay_plan,
726 p_to_position_id => l_sf52_rec.to_position_id,
727 p_to_position_org_line1 => l_sf52_rec.to_position_org_line1,
728 p_to_position_org_line2 => l_sf52_rec.to_position_org_line2,
729 p_to_position_org_line3 => l_sf52_rec.to_position_org_line3,
730 p_to_position_org_line4 => l_sf52_rec.to_position_org_line4,
731 p_to_position_org_line5 => l_sf52_rec.to_position_org_line5,
732 p_to_position_org_line6 => l_sf52_rec.to_position_org_line6,
733 p_to_position_number => l_sf52_rec.to_position_number,
734 p_to_position_seq_no => l_sf52_rec.to_position_seq_no,
735 p_to_position_title => l_sf52_rec.to_position_title,
736 p_to_step_or_rate => l_sf52_rec.to_step_or_rate,
737 p_to_total_salary => l_sf52_rec.to_total_salary,
738 p_veterans_preference => l_sf52_rec.veterans_preference,
739 p_veterans_pref_for_rif => l_sf52_rec.veterans_pref_for_rif,
740 p_veterans_status => l_sf52_rec.veterans_status,
741 p_work_schedule => l_sf52_rec.work_schedule,
742 p_work_schedule_desc => l_sf52_rec.work_schedule_desc,
743 p_year_degree_attained => l_sf52_rec.year_degree_attained,
744 p_first_lac1_information1 => l_sf52_rec.first_lac1_information1,
745 p_first_lac1_information2 => l_sf52_rec.first_lac1_information2,
746 p_first_lac1_information3 => l_sf52_rec.first_lac1_information3,
747 p_first_lac1_information4 => l_sf52_rec.first_lac1_information4,
748 p_first_lac1_information5 => l_sf52_rec.first_lac1_information5,
749 p_first_lac2_information1 => l_sf52_rec.first_lac2_information1,
750 p_first_lac2_information2 => l_sf52_rec.first_lac2_information2,
751 p_first_lac2_information3 => l_sf52_rec.first_lac2_information3,
752 p_first_lac2_information4 => l_sf52_rec.first_lac2_information4,
753 p_first_lac2_information5 => l_sf52_rec.first_lac2_information5,
754 p_second_lac1_information1 => l_sf52_rec.second_lac1_information1,
755 p_second_lac1_information2 => l_sf52_rec.second_lac1_information1,
756 p_second_lac1_information3 => l_sf52_rec.second_lac1_information1,
757 p_second_lac1_information4 => l_sf52_rec.second_lac1_information1,
758 p_second_lac1_information5 => l_sf52_rec.second_lac1_information1,
759 p_print_sf50_flag => 'N', -- true for all ??
760 p_printer_name => Null,
761 p_1_attachment_modified_flag => 'N',
762 p_1_approved_flag => 'N',
763 p_1_user_name_acted_on => Null,
764 p_1_action_taken => 'NOT_ROUTED',
765 p_2_user_name_routed_to => Null,
766 p_2_groupbox_id => Null,
767 p_2_routing_list_id => Null,
768 p_2_routing_seq_number => Null,
769 p_to_retention_allow_percentag => l_sf52_rec.to_retention_allow_percentage,
770 p_to_supervisory_diff_percenta => l_sf52_rec.to_supervisory_diff_percentage,
771 p_to_staffing_diff_percentage => l_sf52_rec.to_staffing_diff_percentage ,
772 p_pa_request_id => l_sf52_rec.pa_request_id,
773 p_par_object_version_number => l_sf52_rec.object_version_number,
774 p_1_pa_routing_history_id => l_1_pa_routing_history_id,
775 p_1_prh_object_version_number => l_1_prh_object_version_number,
776 p_2_pa_routing_history_id => l_2_pa_routing_history_id,
777 p_2_prh_object_version_number => l_2_prh_object_version_number,
778 p_approving_official_full_name => l_personnel_officer_name,
779 p_approval_date => sysdate,
780 p_approving_official_work_titl => l_approving_off_work_title,
781 p_1_approval_status => 'APPROVE',
782 p_rpa_type => 'FEGLI SS',
783 p_mass_action_id => l_sf52_rec.mass_action_id,
784 p_from_pay_table_identifier => l_sf52_rec.from_pay_table_identifier,
785 p_to_pay_table_identifier => l_sf52_rec.to_pay_table_identifier,
786 p_input_pay_rate_determinant => l_sf52_rec.input_pay_rate_determinant
787 );
788 EXCEPTION
789 WHEN OTHERS THEN
790 hr_utility.set_location('Error in ghr_sf52_api.create_sf52'||
791 'Err is '||sqlerrm(sqlcode),20);
792 l_log_text := 'Error in create sf52 for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
793 || '; SQL Error: '|| sqlerrm(sqlcode);
794 raise fegli_error;
795 END;
796
797 p_pa_request_id := l_sf52_rec.pa_request_id;
798
799 hr_utility.set_location('p_pa_request_id'||p_pa_request_id,1000);
800
801
802
803
804 BEGIN
805 ghr_mass_actions_pkg.get_personnel_off_groupbox(l_sf52_rec.to_position_id,
806 l_sf52_rec.effective_date,
807 l_groupbox_id,
808 l_routing_group_id);
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 l_log_text := 'Groupbox error for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
813 || '; Error: '||sqlerrm(sqlcode);
814 RAISE fegli_error;
815 END;
816
817 ghr_prh_upd.upd(
818 p_pa_routing_history_id => l_1_pa_routing_history_id,
819 p_groupbox_id => l_groupbox_id,
820 p_object_version_number => l_1_prh_object_version_number);
821
822 ghr_par_upd.upd(
823 p_pa_request_id => l_sf52_rec.pa_request_id,
824 p_routing_group_id => l_routing_group_id,
825 p_object_version_number => l_sf52_rec.object_version_number);
826
827 hr_utility.set_location('l_sf52_rec.fegli'||l_sf52_rec.fegli,1000);
828 BEGIN
829 --Fetching the actual Fegli code from Element Entry
830 ghr_api.retrieve_element_entry_value
831 (p_element_name => 'FEGLI',
832 p_input_value_name => 'FEGLI',
833 p_assignment_id => p_assignment_id,
834 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
835 p_value => l_old_fegli,
836 p_multiple_error_flag => l_multiple_error_flag
837 );
838 l_sf52_rec.fegli := l_old_fegli;
839 l_sf52_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
840 (800
841 ,'GHR_US_FEGLI'
842 ,l_sf52_rec.fegli
843 );
844 ghr_process_sf52.create_shadow_row (p_sf52_data => l_sf52_rec);
845
846 UPDATE ghr_pa_request_shadow
847 SET employee_national_identifier = l_sf52_rec.employee_national_identifier,
848 employee_date_of_birth = l_sf52_rec.employee_date_of_birth,
849 employee_first_name = l_sf52_rec.employee_first_name,
850 employee_last_name = l_sf52_rec.employee_last_name,
851 employee_middle_names = l_sf52_rec.employee_middle_names
852 WHERE pa_request_id = l_sf52_rec.pa_request_id;
853
854 EXCEPTION
855 WHEN OTHERS THEN
856 l_log_text := 'Error in creating shadow row for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
857 || '; Error: '||sqlerrm(sqlcode);
858 RAISE fegli_error;
859 END;
860
861 BEGIN
862 ghr_sf52_post_update.get_notification_details(
863 p_pa_request_id => l_sf52_rec.pa_request_id,
864 p_effective_date => l_sf52_rec.effective_date,
865 p_from_position_id => l_sf52_rec.from_position_id,
866 p_to_position_id => l_sf52_rec.to_position_id,
867 p_agency_code => l_sf52_rec.agency_code,
868 p_from_agency_code => l_sf52_rec.from_agency_code,
869 p_from_agency_desc => l_sf52_rec.from_agency_desc,
870 p_from_office_symbol => l_sf52_rec.from_office_symbol,
871 p_personnel_office_id => l_sf52_rec.personnel_office_id,
872 p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
873 p_to_office_symbol => l_sf52_rec.to_office_symbol);
874 EXCEPTION
875 WHEN OTHERS THEN
876 l_log_text := 'Error in get_notification_details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
877 || '; Error: '||sqlerrm(sqlcode);
878 RAISE fegli_error;
879 END;
880
881 ghr_par_upd.upd
882 (p_pa_request_id => l_sf52_rec.pa_request_id,
883 p_object_version_number => l_sf52_rec.object_version_number,
884 p_agency_code => l_sf52_rec.agency_code,
885 p_from_agency_code => l_sf52_rec.from_agency_code,
886 p_from_agency_desc => l_sf52_rec.from_agency_desc,
887 p_from_office_symbol => l_sf52_rec.from_office_symbol,
888 p_personnel_office_id => l_sf52_rec.personnel_office_id,
889 p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
890 p_to_office_symbol => l_sf52_rec.to_office_symbol,
891 p_request_number => 'FEGLI'|| to_char(l_sf52_rec.pa_request_id),
892 p_sf50_approving_ofcl_full_nam => l_personnel_officer_name,
893 p_sf50_approval_date => sysdate ,
894 p_sf50_approving_ofcl_work_tit => l_approving_off_work_title
895 );
896
897 -- Since we are not calling the process_sf52 by passing
898 -- 'FUTURE_ACTION' as the action_taken in the
899 -- above procedure, updating it on the routing history table
900 -- by using the row handler, just in
901 -- case .
902
903 FOR routing_history_id in c_routing_history(p_pa_request_id => l_sf52_rec.pa_request_id)
904 LOOP
905 l_pa_routing_history_id := routing_history_id.pa_routing_history_id;
906 l_object_version_number := routing_history_id.object_version_number;
907 EXIT;
908 END LOOP;
909
910 ghr_prh_upd.upd
911 (p_pa_routing_history_id => l_pa_routing_history_id,
912 p_object_version_number => l_object_version_number,
913 p_action_taken => 'FUTURE_ACTION'
914 );
915
916 EXCEPTION
917 When fegli_error then
918 hr_utility.set_location('Error occured in ' || l_proc , 1);
919 IF l_log_text is NULL THEN
920 l_log_text := 'Error while create / Update the PA Request Rec. ';
921 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
922 END IF;
923
924 l_log_text := substr(l_log_text,1,2000);
925
926 hr_utility.set_location('before creating entry in log file',10);
927 ghr_mto_int.log_message( p_procedure => 'While Creating FEGLI RPA'
928 ,p_message => l_log_text);
929
930 hr_utility.set_location('created entry in log file',20);
931 WHEN others THEN
932
933 IF l_log_text is NULL THEN
934 l_log_text := 'Error while create / Update the PA Request Rec. ';
935 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
936 END IF;
937
938 hr_utility.set_location('before creating entry in log file',10);
939
940 ghr_mto_int.log_message(p_procedure => 'While Creating FEGLI RPA'
941 ,p_message => l_log_text);
942 hr_utility.set_location('created entry in log file',20);
943
944 END CREATE_SF52_FEGLI_RPA;
945
946
947 PROCEDURE UPDATE_SF52_FEGLI_RPA(p_pa_request_id in number,
948 p_person_id in number,
949 p_assignment_id in number,
950 p_fegli_code in varchar2,
951 p_effective_date in date) IS
952 CURSOR get_rpa_det
953 IS
954 SELECT *
955 FROM GHR_PA_REQUESTS
956 WHERE pa_request_id = p_pa_request_id;
957
958 CURSOR c_routing_history(p_pa_request_id in number)
959 IS
960 SELECT prh.pa_routing_history_id,
961 prh.object_version_number
962 FROM ghr_pa_routing_history prh
963 WHERE prh.pa_request_id = p_pa_request_id
964 ORDER BY 1 desc;
965
966 l_uprh_object_version_number ghr_pa_routing_history.object_version_number%type;
967 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
968 l_iprh_object_version_number ghr_pa_routing_history.object_version_number%type;
969 l_sf52_rec ghr_pa_requests%rowtype;
970 l_full_name VARCHAR2(380);
971 l_employee_number per_people_f.employee_number%type;
972 l_routing_group_id ghr_pa_requests.routing_group_id%type;
973 fegli_upd_error exception;
974 fegli_shadow_upd_error exception;
975 l_log_text varchar2(2000);
976 l_personnel_officer_name per_people_f.full_name%type;
977 l_groupbox_id ghr_groupboxes.groupbox_id%type;
978 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
979 l_object_version_number number;
980 l_result BOOLEAN;
981 l_fegli_desc ghr_pa_requests.fegli_desc%type;
982 l_old_fegli ghr_pa_requests.fegli%type;
983 l_multiple_error_flag boolean;
984
985 l_proc varchar2(72) := g_package || 'update_sf52_fegli_rpa';
986 BEGIN
987
988 open get_rpa_det;
989 fetch get_rpa_det into l_sf52_rec;
990 close get_rpa_det;
991
992
993 l_fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
994 (800
995 ,'GHR_US_FEGLI'
996 ,p_fegli_code
997 );
998
999 ghr_sf52_api.update_sf52
1000 (p_pa_request_id =>l_sf52_rec.pa_request_id,
1001 p_noa_family_code =>l_sf52_rec.noa_family_code,
1002 p_routing_group_id =>l_sf52_rec.routing_group_id,
1003 p_par_object_version_number =>l_sf52_rec.object_version_number,
1004 p_proposed_effective_asap_flag =>l_sf52_rec.proposed_effective_asap_flag,
1005 p_academic_discipline =>l_sf52_rec.academic_discipline,
1006 p_additional_info_person_id =>l_sf52_rec.additional_info_person_id,
1007 p_additional_info_tel_number =>l_sf52_rec.additional_info_tel_number,
1008 p_altered_pa_request_id =>l_sf52_rec.altered_pa_request_id,
1009 p_annuitant_indicator =>l_sf52_rec.annuitant_indicator,
1010 p_annuitant_indicator_desc =>l_sf52_rec.annuitant_indicator_desc,
1011 p_appropriation_code1 =>l_sf52_rec.appropriation_code1,
1012 p_appropriation_code2 =>l_sf52_rec.appropriation_code2,
1013 p_approval_date =>l_sf52_rec.approval_date,
1014 p_approving_official_full_name =>l_sf52_rec.approving_official_full_name,
1015 p_approving_official_work_titl =>l_sf52_rec.approving_official_work_title,
1016 p_authorized_by_person_id =>l_sf52_rec.authorized_by_person_id,
1017 p_authorized_by_title =>l_sf52_rec.authorized_by_title,
1018 p_award_amount =>l_sf52_rec.award_amount,
1019 p_award_uom =>l_sf52_rec.award_uom,
1020 p_bargaining_unit_status =>l_sf52_rec.bargaining_unit_status,
1021 p_citizenship =>l_sf52_rec.citizenship,
1022 p_concurrence_date =>l_sf52_rec.concurrence_date,
1023 p_custom_pay_calc_flag =>l_sf52_rec.custom_pay_calc_flag,
1024 p_duty_station_code =>l_sf52_rec.duty_station_code,
1025 p_duty_station_desc =>l_sf52_rec.duty_station_desc,
1026 p_duty_station_id =>l_sf52_rec.duty_station_id,
1027 p_duty_station_location_id =>l_sf52_rec.duty_station_location_id,
1028 p_education_level =>l_sf52_rec.education_level,
1029 p_effective_date =>p_effective_date,
1030 p_employee_assignment_id =>l_sf52_rec.employee_assignment_id,
1031 p_employee_date_of_birth =>l_sf52_rec.employee_date_of_birth,
1032 p_employee_first_name =>l_sf52_rec.employee_first_name,
1033 p_employee_last_name =>l_sf52_rec.employee_last_name,
1034 p_employee_middle_names =>l_sf52_rec.employee_middle_names,
1035 p_employee_national_identifier =>l_sf52_rec.employee_national_identifier,
1036 p_fegli =>p_fegli_code,
1037 p_fegli_desc =>l_fegli_desc,
1038 p_first_action_la_code1 =>l_sf52_rec.first_action_la_code1,
1039 p_first_action_la_code2 =>l_sf52_rec.first_action_la_code2,
1040 p_first_action_la_desc1 =>l_sf52_rec.first_action_la_desc1,
1041 p_first_action_la_desc2 =>l_sf52_rec.first_action_la_desc2,
1042 p_first_noa_cancel_or_correct =>l_sf52_rec.first_noa_cancel_or_correct,
1043 p_first_noa_code =>l_sf52_rec.first_noa_code,
1044 p_first_noa_desc =>l_sf52_rec.first_noa_desc,
1045 p_first_noa_id =>l_sf52_rec.first_noa_id,
1046 p_first_noa_pa_request_id =>l_sf52_rec.first_noa_pa_request_id,
1047 p_flsa_category =>l_sf52_rec.flsa_category,
1048 p_forwarding_address_line1 =>l_sf52_rec.forwarding_address_line1,
1049 p_forwarding_address_line2 =>l_sf52_rec.forwarding_address_line2,
1050 p_forwarding_address_line3 =>l_sf52_rec.forwarding_address_line3,
1051 p_forwarding_country =>l_sf52_rec.forwarding_country,
1052 p_forwarding_country_short_nam =>l_sf52_rec.forwarding_country_short_name,
1053 p_forwarding_postal_code =>l_sf52_rec.forwarding_postal_code,
1054 p_forwarding_region_2 =>l_sf52_rec.forwarding_region_2,
1055 p_forwarding_town_or_city =>l_sf52_rec.forwarding_town_or_city,
1056 p_from_adj_basic_pay =>l_sf52_rec.from_adj_basic_pay,
1057 p_from_basic_pay =>l_sf52_rec.from_basic_pay,
1058 p_from_grade_or_level =>l_sf52_rec.from_grade_or_level,
1059 p_from_locality_adj =>l_sf52_rec.from_locality_adj,
1060 p_from_occ_code =>l_sf52_rec.from_occ_code,
1061 p_from_other_pay_amount =>l_sf52_rec.from_other_pay_amount,
1062 p_from_pay_basis =>l_sf52_rec.from_pay_basis,
1063 p_from_pay_plan =>l_sf52_rec.from_pay_plan,
1064 p_input_pay_rate_determinant =>l_sf52_rec.input_pay_rate_determinant,
1065 p_from_pay_table_identifier =>l_sf52_rec.from_pay_table_identifier,
1066 p_from_position_id =>l_sf52_rec.from_position_id,
1067 p_from_position_org_line1 =>l_sf52_rec.from_position_org_line1,
1068 p_from_position_org_line2 =>l_sf52_rec.from_position_org_line2,
1069 p_from_position_org_line3 =>l_sf52_rec.from_position_org_line3,
1070 p_from_position_org_line4 =>l_sf52_rec.from_position_org_line4,
1071 p_from_position_org_line5 =>l_sf52_rec.from_position_org_line5,
1072 p_from_position_org_line6 =>l_sf52_rec.from_position_org_line6,
1073 p_from_position_number =>l_sf52_rec.from_position_number,
1074 p_from_position_seq_no =>l_sf52_rec.from_position_seq_no,
1075 p_from_position_title =>l_sf52_rec.from_position_title,
1076 p_from_step_or_rate =>l_sf52_rec.from_step_or_rate,
1077 p_from_total_salary =>l_sf52_rec.from_total_salary,
1078 p_functional_class =>l_sf52_rec.functional_class,
1079 p_notepad =>l_sf52_rec.notepad,
1080 p_part_time_hours =>l_sf52_rec.part_time_hours,
1081 p_pay_rate_determinant =>l_sf52_rec.pay_rate_determinant,
1082 p_person_id =>l_sf52_rec.person_id,
1083 p_position_occupied =>l_sf52_rec.position_occupied,
1084 p_proposed_effective_date =>l_sf52_rec.proposed_effective_date,
1085 p_requested_by_person_id =>l_sf52_rec.requested_by_person_id,
1086 p_requested_by_title =>l_sf52_rec.requested_by_title,
1087 p_requested_date =>l_sf52_rec.requested_date,
1088 p_requesting_office_remarks_de =>l_sf52_rec.requesting_office_remarks_desc,
1089 p_requesting_office_remarks_fl =>l_sf52_rec.REQUESTING_OFFICE_REMARKS_FLAG,
1090 p_request_number =>l_sf52_rec.request_number,
1091 p_resign_and_retire_reason_des =>l_sf52_rec.resign_and_retire_reason_desc,
1092 p_retirement_plan =>l_sf52_rec.retirement_plan,
1093 p_retirement_plan_desc =>l_sf52_rec.retirement_plan_desc,
1094 p_second_action_la_code1 =>l_sf52_rec.second_action_la_code1,
1095 p_second_action_la_code2 =>l_sf52_rec.second_action_la_code2,
1096 p_second_action_la_desc1 =>l_sf52_rec.second_action_la_desc1,
1097 p_second_action_la_desc2 =>l_sf52_rec.second_action_la_desc2,
1098 p_second_noa_cancel_or_correct =>l_sf52_rec.second_noa_cancel_or_correct,
1099 p_second_noa_code =>l_sf52_rec.second_noa_code,
1100 p_second_noa_desc =>l_sf52_rec.second_noa_desc,
1101 p_second_noa_id =>l_sf52_rec.second_noa_id,
1102 p_second_noa_pa_request_id =>l_sf52_rec.second_noa_pa_request_id,
1103 p_service_comp_date =>l_sf52_rec.service_comp_date,
1104 p_supervisory_status =>l_sf52_rec.supervisory_status,
1105 p_tenure =>l_sf52_rec.tenure,
1106 p_to_adj_basic_pay =>l_sf52_rec.to_adj_basic_pay,
1107 p_to_basic_pay =>l_sf52_rec.to_basic_pay,
1108 p_to_grade_id =>l_sf52_rec.to_grade_id,
1109 p_to_grade_or_level =>l_sf52_rec.to_grade_or_level,
1110 p_to_job_id =>l_sf52_rec.to_job_id,
1111 p_to_locality_adj =>l_sf52_rec.to_locality_adj,
1112 p_to_occ_code =>l_sf52_rec.to_occ_code,
1113 p_to_organization_id =>l_sf52_rec.to_organization_id,
1114 p_to_other_pay_amount =>l_sf52_rec.to_other_pay_amount,
1115 p_to_au_overtime =>l_sf52_rec.to_au_overtime,
1116 p_to_auo_premium_pay_indicator =>l_sf52_rec.to_auo_premium_pay_indicator,
1117 p_to_availability_pay =>l_sf52_rec.to_availability_pay,
1118 p_to_ap_premium_pay_indicator =>l_sf52_rec.to_ap_premium_pay_indicator,
1119 p_to_retention_allowance =>l_sf52_rec.to_retention_allowance,
1120 p_to_supervisory_differential =>l_sf52_rec.to_supervisory_differential,
1121 p_to_staffing_differential =>l_sf52_rec.to_staffing_differential,
1122 p_to_pay_basis =>l_sf52_rec.to_pay_basis,
1123 p_to_pay_plan =>l_sf52_rec.to_pay_plan,
1124 p_to_pay_table_identifier =>l_sf52_rec.to_pay_table_identifier,
1125 p_to_position_id =>l_sf52_rec.to_position_id,
1126 p_to_position_org_line1 =>l_sf52_rec.to_position_org_line1,
1127 p_to_position_org_line2 =>l_sf52_rec.to_position_org_line2,
1128 p_to_position_org_line3 =>l_sf52_rec.to_position_org_line3,
1129 p_to_position_org_line4 =>l_sf52_rec.to_position_org_line4,
1130 p_to_position_org_line5 =>l_sf52_rec.to_position_org_line5,
1131 p_to_position_org_line6 =>l_sf52_rec.to_position_org_line6,
1132 p_to_position_number =>l_sf52_rec.to_position_number,
1133 p_to_position_seq_no =>l_sf52_rec.to_position_seq_no,
1134 p_to_position_title =>l_sf52_rec.to_position_title,
1135 p_to_step_or_rate =>l_sf52_rec.to_step_or_rate,
1136 p_to_total_salary =>l_sf52_rec.to_total_salary,
1137 p_veterans_preference =>l_sf52_rec.veterans_preference,
1138 p_veterans_pref_for_rif =>l_sf52_rec.veterans_pref_for_rif,
1139 p_veterans_status =>l_sf52_rec.veterans_status,
1140 p_work_schedule =>l_sf52_rec.work_schedule,
1141 p_work_schedule_desc =>l_sf52_rec.work_schedule_desc,
1142 p_year_degree_attained =>l_sf52_rec.year_degree_attained,
1143 p_first_noa_information1 =>l_sf52_rec.first_noa_information1,
1144 p_first_noa_information2 =>l_sf52_rec.first_noa_information2,
1145 p_first_noa_information3 =>l_sf52_rec.first_noa_information3,
1146 p_first_noa_information4 =>l_sf52_rec.first_noa_information4,
1147 p_first_noa_information5 =>l_sf52_rec.first_noa_information5,
1148 p_second_lac1_information1 =>l_sf52_rec.second_lac1_information1,
1149 p_second_lac1_information2 =>l_sf52_rec.second_lac1_information2,
1150 p_second_lac1_information3 =>l_sf52_rec.second_lac1_information3,
1151 p_second_lac1_information4 =>l_sf52_rec.second_lac1_information4,
1152 p_second_lac1_information5 =>l_sf52_rec.second_lac1_information5,
1153 p_second_lac2_information1 =>l_sf52_rec.second_lac2_information1,
1154 p_second_lac2_information2 =>l_sf52_rec.second_lac2_information2,
1155 p_second_lac2_information3 =>l_sf52_rec.second_lac2_information3,
1156 p_second_lac2_information4 =>l_sf52_rec.second_lac2_information4,
1157 p_second_lac2_information5 =>l_sf52_rec.second_lac2_information5 ,
1158 p_second_noa_information1 =>l_sf52_rec.second_noa_information1,
1159 p_second_noa_information2 =>l_sf52_rec.second_noa_information2,
1160 p_second_noa_information3 =>l_sf52_rec.second_noa_information3,
1161 p_second_noa_information4 =>l_sf52_rec.second_noa_information4,
1162 p_second_noa_information5 =>l_sf52_rec.second_noa_information5,
1163 p_first_lac1_information1 =>l_sf52_rec.first_lac1_information1,
1164 p_first_lac1_information2 =>l_sf52_rec.first_lac1_information2,
1165 p_first_lac1_information3 =>l_sf52_rec.first_lac1_information3,
1166 p_first_lac1_information4 =>l_sf52_rec.first_lac1_information4,
1167 p_first_lac1_information5 =>l_sf52_rec.first_lac1_information5,
1168 p_first_lac2_information1 =>l_sf52_rec.first_lac2_information1,
1169 p_first_lac2_information2 =>l_sf52_rec.first_lac2_information2,
1170 p_first_lac2_information3 =>l_sf52_rec.first_lac2_information3,
1171 p_first_lac2_information4 =>l_sf52_rec.first_lac2_information4,
1172 p_first_lac2_information5 =>l_sf52_rec.first_lac2_information5,
1173 p_attribute_category =>l_sf52_rec.attribute_category,
1174 p_attribute1 =>l_sf52_rec.attribute1,
1175 p_attribute2 =>l_sf52_rec.attribute2,
1176 p_attribute3 =>l_sf52_rec.attribute3,
1177 p_attribute4 =>l_sf52_rec.attribute4,
1178 p_attribute5 =>l_sf52_rec.attribute5,
1179 p_attribute6 =>l_sf52_rec.attribute6,
1180 p_attribute7 =>l_sf52_rec.attribute7,
1181 p_attribute8 =>l_sf52_rec.attribute8,
1182 p_attribute9 =>l_sf52_rec.attribute9,
1183 p_attribute10 =>l_sf52_rec.attribute10,
1184 p_attribute11 =>l_sf52_rec.attribute11,
1185 p_attribute12 =>l_sf52_rec.attribute12,
1186 p_attribute13 =>l_sf52_rec.attribute13,
1187 p_attribute14 =>l_sf52_rec.attribute14,
1188 p_attribute15 =>l_sf52_rec.attribute15,
1189 p_attribute16 =>l_sf52_rec.attribute16,
1190 p_attribute17 =>l_sf52_rec.attribute17,
1191 p_attribute18 =>l_sf52_rec.attribute18,
1192 p_attribute19 =>l_sf52_rec.attribute19,
1193 p_attribute20 =>l_sf52_rec.attribute20,
1194 p_u_approved_flag =>'N',
1195 p_u_user_name_acted_on =>Null,
1196 p_u_action_taken =>'NOT_ROUTED',
1197 p_u_approval_status =>'APPROVE',
1198 p_i_user_name_routed_to =>Null,
1199 p_i_groupbox_id =>Null,
1200 p_i_routing_list_id =>Null,
1201 p_i_routing_seq_number =>Null,
1202 p_to_retention_allow_percentag =>l_sf52_rec.to_retention_allow_percentage,
1203 p_to_supervisory_diff_percenta =>l_sf52_rec.to_supervisory_diff_percentage,
1204 p_to_staffing_diff_percentage =>l_sf52_rec.to_staffing_diff_percentage,
1205 p_award_percentage =>l_sf52_rec.award_percentage,
1206 p_u_prh_object_version_number => l_uprh_object_version_number,
1207 p_i_pa_routing_history_id => l_pa_routing_history_id,
1208 p_i_prh_object_version_number => l_iprh_object_version_number
1209 );
1210
1211 hr_utility.set_location('after updation',10);
1212 hr_utility.set_location('to_position_id'||l_sf52_rec.to_position_id,10);
1213 hr_utility.set_location('l_sf52_rec.effective_date'||l_sf52_rec.effective_date,10);
1214
1215
1216 BEGIN
1217 ghr_mass_actions_pkg.get_personnel_off_groupbox(l_sf52_rec.to_position_id,
1218 l_sf52_rec.effective_date,
1219 l_groupbox_id,
1220 l_routing_group_id);
1221
1222 EXCEPTION
1223 WHEN OTHERS THEN
1224 l_log_text := 'Groupbox error for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
1225 || '; Error: '||sqlerrm(sqlcode);
1226 RAISE fegli_upd_error;
1227 END;
1228
1229 --hr_utility.trace_on(null,'mani');
1230 FOR routing_history_id in c_routing_history(p_pa_request_id => l_sf52_rec.pa_request_id)
1231 LOOP
1232 l_pa_routing_history_id := routing_history_id.pa_routing_history_id;
1233 l_object_version_number := routing_history_id.object_version_number;
1234 -- EXIT;
1235 hr_utility.set_location('before updating routing'||l_pa_routing_history_id,1000);
1236 hr_utility.set_location('before updating routing'||l_groupbox_id,1002);
1237 END LOOP;
1238
1239 ghr_prh_upd.upd(
1240 p_pa_routing_history_id => l_pa_routing_history_id,
1241 p_groupbox_id => l_groupbox_id,
1242 p_object_version_number => l_object_version_number,
1243 p_action_taken => 'FUTURE_ACTION');
1244 hr_utility.set_location('before updating par'||l_routing_group_id,1005);
1245 hr_utility.set_location('before updating par'||l_sf52_rec.object_version_number,1006);
1246 --hr_utility.trace_off;
1247 ghr_par_upd.upd(
1248 p_pa_request_id => l_sf52_rec.pa_request_id,
1249 p_routing_group_id => l_routing_group_id,
1250 p_object_version_number => l_sf52_rec.object_version_number);
1251
1252 BEGIN
1253 --Fetching the actual Fegli code from Element Entry
1254 ghr_api.retrieve_element_entry_value
1255 (p_element_name => 'FEGLI',
1256 p_input_value_name => 'FEGLI',
1257 p_assignment_id => p_assignment_id,
1258 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1259 p_value => l_old_fegli,
1260 p_multiple_error_flag => l_multiple_error_flag
1261 );
1262 l_sf52_rec.fegli := l_old_fegli;
1263 l_sf52_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
1264 (800
1265 ,'GHR_US_FEGLI'
1266 ,l_sf52_rec.fegli
1267 );
1268 update_shadow_row (p_sf52_rec => l_sf52_rec, p_result => l_result);
1269 if l_result = FALSE OR l_result = NULL then
1270 raise fegli_shadow_upd_error;
1271 end if;
1272 EXCEPTION
1273 WHEN fegli_shadow_upd_error THEN
1274 l_log_text := 'Error in Updating the shadow row for Emp No: '||l_employee_number||'; Name:'||l_full_name
1275 || '; Error: '||sqlerrm(sqlcode);
1276 RAISE fegli_upd_error;
1277 WHEN OTHERS THEN
1278 l_log_text := 'Error in Updating the shadow row for Emp No: '||l_employee_number||'; Name:'||l_full_name
1279 || '; Error: '||sqlerrm(sqlcode);
1280 RAISE fegli_upd_error;
1281 END;
1282
1283 BEGIN
1284 ghr_sf52_post_update.get_notification_details(
1285 p_pa_request_id => l_sf52_rec.pa_request_id,
1286 p_effective_date => l_sf52_rec.effective_date,
1287 p_from_position_id => l_sf52_rec.from_position_id,
1288 p_to_position_id => l_sf52_rec.to_position_id,
1289 p_agency_code => l_sf52_rec.agency_code,
1290 p_from_agency_code => l_sf52_rec.from_agency_code,
1291 p_from_agency_desc => l_sf52_rec.from_agency_desc,
1292 p_from_office_symbol => l_sf52_rec.from_office_symbol,
1293 p_personnel_office_id => l_sf52_rec.personnel_office_id,
1294 p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
1295 p_to_office_symbol => l_sf52_rec.to_office_symbol);
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 l_log_text := 'Error in get_notification_details for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
1299 || '; Error: '||sqlerrm(sqlcode);
1300 RAISE fegli_upd_error;
1301 END;
1302
1303 ghr_par_upd.upd
1304 (p_pa_request_id => l_sf52_rec.pa_request_id,
1305 p_object_version_number => l_sf52_rec.object_version_number,
1306 p_agency_code => l_sf52_rec.agency_code,
1307 p_from_agency_code => l_sf52_rec.from_agency_code,
1308 p_from_agency_desc => l_sf52_rec.from_agency_desc,
1309 p_from_office_symbol => l_sf52_rec.from_office_symbol,
1310 p_personnel_office_id => l_sf52_rec.personnel_office_id,
1311 p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
1312 p_to_office_symbol => l_sf52_rec.to_office_symbol,
1313 p_request_number => 'FEGLI'|| to_char(l_sf52_rec.pa_request_id),
1314 p_sf50_approving_ofcl_full_nam => l_personnel_officer_name,
1315 p_sf50_approval_date => sysdate ,
1316 p_sf50_approving_ofcl_work_tit => l_approving_off_work_title
1317 );
1318
1319 -- Since we are not calling the process_sf52 by passing
1320 -- 'FUTURE_ACTION' as the action_taken in the
1321 -- above procedure, updating it on the routing history table
1322 -- by using the row handler, just in
1323 -- case .
1324
1325
1326
1327
1328 EXCEPTION
1329 When fegli_upd_error then
1330 hr_utility.set_location('Error occured in ' || l_proc , 1);
1331 IF l_log_text is NULL THEN
1332 l_log_text := 'Error while Update the PA Request Rec. ';
1333 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
1334 END IF;
1335
1336 l_log_text := substr(l_log_text,1,2000);
1337
1338 hr_utility.set_location('before creating entry in log file',10);
1339 ghr_mto_int.log_message( p_procedure => 'While Updating FEGLI RPA'
1340 ,p_message => l_log_text);
1341
1342 hr_utility.set_location('created entry in log file',20);
1343 WHEN others THEN
1344
1345 IF l_log_text is NULL THEN
1346 l_log_text := 'Error while Update the PA Request Rec. ';
1347 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
1348 END IF;
1349
1350 hr_utility.set_location('before creating entry in log file',10);
1351
1352 ghr_mto_int.log_message(p_procedure => 'While Updating FEGLI RPA'
1353 ,p_message => l_log_text);
1354 hr_utility.set_location('created entry in log file',20);
1355
1356
1357
1358 END UPDATE_SF52_FEGLI_RPA;
1359
1360
1361 PROCEDURE CREATE_PA_REMARKS (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
1362 p_effective_date in date,
1363 p_remark_code in ghr_remarks.code%type) IS
1364
1365 cursor chk_rem_exists(p_remark_id in NUMBER)
1366 is
1367 SELECT 1
1368 FROM GHR_PA_REMARKS
1369 WHERE pa_request_id = p_pa_request_id
1370 AND remark_id = p_remark_id;
1371
1372 l_proc varchar2(72) := g_package || 'create_remarks';
1373 l_remark_id ghr_remarks.remark_id%type;
1374 l_description ghr_pa_remarks.description%type;
1375 l_remark_code_information1 ghr_pa_remarks.remark_code_information1%type;
1376 l_remark_code_information2 ghr_pa_remarks.remark_code_information2%type;
1377 l_remark_code_information3 ghr_pa_remarks.remark_code_information3%type;
1378 l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
1379 l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
1380 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
1381 l_object_version_number ghr_pa_remarks.object_version_number%type;
1382 rem_exists number;
1383
1384
1385
1386 begin
1387
1388 -- get remark_id remark_desc, remark_code_information1, ...
1389 hr_utility.set_location('Entering ' || l_proc,5);
1390
1391 ghr_mass_actions_pkg.get_remark_id_desc
1392 (p_remark_code => p_remark_code,
1393 p_effective_date => trunc(nvl(p_effective_date,sysdate)),
1394 p_remark_id => l_remark_id,
1395 p_remark_desc => l_description
1396 );
1397
1398 open chk_rem_exists(p_remark_id => l_remark_id);
1399 fetch chk_rem_exists into rem_exists;
1400 if chk_rem_exists%notfound then
1401
1402 l_remark_code_information1 := Null;
1403 l_remark_code_information2 := Null;
1404 l_remark_code_information3 := Null;
1405 l_remark_code_information4 := Null;
1406 l_remark_code_information5 := Null;
1407
1408 ghr_pa_remarks_api.create_pa_remarks
1409 (
1410 P_PA_REQUEST_ID => p_pa_request_id,
1411 P_REMARK_ID => l_remark_id,
1412 P_DESCRIPTION => l_description,
1413 P_REMARK_CODE_INFORMATION1 => l_remark_code_information1,
1414 P_REMARK_CODE_INFORMATION2 => l_remark_code_information2,
1415 P_REMARK_CODE_INFORMATION3 => l_remark_code_information3,
1416 P_REMARK_CODE_INFORMATION4 => l_remark_code_information4,
1417 P_REMARK_CODE_INFORMATION5 => l_remark_code_information5,
1418 P_PA_REMARK_ID => l_pa_remark_id,
1419 p_OBJECT_VERSION_NUMBER => l_object_version_number
1420 );
1421 end if;
1422
1423 End create_pa_remarks;
1424
1425
1426 PROCEDURE POPULATE_PA_REQUEST_EXTRA_INFO(p_pa_request_id in NUMBER,
1427 p_info_type in VARCHAR2,
1428 p_attribute1 in VARCHAR2 default NULL,
1429 p_attribute2 in VARCHAR2 default NULL,
1430 p_attribute3 in VARCHAR2 default NULL,
1431 p_attribute4 in VARCHAR2 default NULL,
1432 p_attribute5 in VARCHAR2 default NULL,
1433 p_attribute6 in VARCHAR2 default NULL,
1434 p_attribute7 in VARCHAR2 default NULL,
1435 p_attribute8 in VARCHAR2 default NULL,
1436 p_attribute9 in VARCHAR2 default NULL,
1437 p_attribute10 in VARCHAR2 default NULL,
1438 p_attribute11 in VARCHAR2 default NULL,
1439 p_attribute12 in VARCHAR2 default NULL,
1440 p_attribute13 in VARCHAR2 default NULL,
1441 p_attribute14 in VARCHAR2 default NULL,
1442 p_attribute15 in VARCHAR2 default NULL,
1443 p_attribute16 in VARCHAR2 default NULL,
1444 p_attribute17 in VARCHAR2 default NULL,
1445 p_attribute18 in VARCHAR2 default NULL,
1446 p_attribute19 in VARCHAR2 default NULL,
1447 p_attribute20 in VARCHAR2 default NULL,
1448 p_attribute21 in VARCHAR2 default NULL,
1449 p_attribute22 in VARCHAR2 default NULL,
1450 p_attribute23 in VARCHAR2 default NULL,
1451 p_attribute24 in VARCHAR2 default NULL,
1452 p_attribute25 in VARCHAR2 default NULL,
1453 p_attribute26 in VARCHAR2 default NULL,
1454 p_attribute27 in VARCHAR2 default NULL,
1455 p_attribute28 in VARCHAR2 default NULL,
1456 p_attribute29 in VARCHAR2 default NULL,
1457 p_attribute30 in VARCHAR2 default NULL
1458 ) IS
1459
1460 CURSOR c_get_rpa_ei_exist
1461 IS
1462 SELECT pa_request_extra_info_id,
1463 object_version_number
1464 FROM GHR_PA_REQUEST_EXTRA_INFO
1465 WHERE pa_request_id = p_pa_request_id
1466 AND information_type = 'GHR_US_PAR_FEGLI';
1467
1468 l_pa_request_extra_info_id NUMBER;
1469 l_object_version_number NUMBER;
1470 l_ins_upd VARCHAR2(1);
1471
1472 BEGIN
1473
1474 open c_get_rpa_ei_exist;
1475 fetch c_get_rpa_ei_exist into l_pa_request_extra_info_id,l_object_version_number;
1476 if c_get_rpa_ei_exist%found then
1477 l_ins_upd := 'U';
1478 else
1479 l_ins_upd := 'I';
1480 end if;
1481
1482 IF l_ins_upd = 'I' THEN
1483 ghr_par_extra_info_api.create_pa_request_extra_info
1484 (p_pa_request_id => p_pa_request_id,
1485 p_information_type => p_info_type,
1486 p_rei_information_category => p_info_type,
1487 p_rei_information1 => p_attribute1,
1488 p_rei_information2 => p_attribute2,
1489 p_rei_information3 => p_attribute3,
1490 p_rei_information4 => p_attribute4,
1491 p_rei_information5 => p_attribute5,
1492 p_rei_information6 => p_attribute6,
1493 p_rei_information7 => p_attribute7,
1494 p_rei_information8 => p_attribute8,
1495 p_rei_information9 => p_attribute9,
1496 p_rei_information10 => p_attribute10,
1497 p_rei_information11 => p_attribute11,
1498 p_rei_information12 => p_attribute12,
1499 p_rei_information13 => p_attribute13,
1500 p_rei_information14 => p_attribute14,
1501 p_rei_information15 => p_attribute15,
1502 p_rei_information16 => p_attribute16,
1503 p_rei_information17 => p_attribute17,
1504 p_rei_information18 => p_attribute18,
1505 p_rei_information19 => p_attribute19,
1506 p_rei_information20 => p_attribute20,
1507 p_rei_information21 => p_attribute21,
1508 p_rei_information22 => p_attribute22,
1509 p_rei_information23 => p_attribute23,
1510 p_rei_information24 => p_attribute24,
1511 p_rei_information25 => p_attribute25,
1512 p_rei_information26 => p_attribute26,
1513 p_rei_information27 => p_attribute27,
1514 p_rei_information28 => p_attribute28,
1515 p_rei_information29 => p_attribute29,
1516 p_rei_information30 => p_attribute30,
1517 P_PA_REQUEST_EXTRA_INFO_ID => l_pa_request_extra_info_id,
1518 P_OBJECT_VERSION_NUMBER => l_object_version_number
1519 );
1520
1521 insert into ghr_pa_request_ei_shadow
1522 (
1523 pa_request_extra_info_id ,
1524 pa_request_id ,
1525 information_type,
1526 rei_information1 ,
1527 rei_information2 ,
1528 rei_information3 ,
1529 rei_information4 ,
1530 rei_information5 ,
1531 rei_information6 ,
1532 rei_information7 ,
1533 rei_information8 ,
1534 rei_information9 ,
1535 rei_information10 ,
1536 rei_information11 ,
1537 rei_information12 ,
1538 rei_information13 ,
1539 rei_information14 ,
1540 rei_information15 ,
1541 rei_information16 ,
1542 rei_information17 ,
1543 rei_information18 ,
1544 rei_information19 ,
1545 rei_information20 ,
1546 rei_information21 ,
1547 rei_information22 ,
1548 rei_information23 ,
1549 rei_information24 ,
1550 rei_information25 ,
1551 rei_information26 ,
1552 rei_information27 ,
1553 rei_information28 ,
1554 rei_information29 ,
1555 rei_information30
1556 ) values
1557 (l_pa_request_extra_info_id,
1558 p_pa_request_id,
1559 p_info_type,
1560 p_attribute1,
1561 p_attribute2,
1562 p_attribute3,
1563 p_attribute4,
1564 p_attribute5,
1565 p_attribute6,
1566 p_attribute7,
1567 p_attribute8,
1568 p_attribute9,
1569 p_attribute10,
1570 p_attribute11,
1571 p_attribute12,
1572 p_attribute13,
1573 p_attribute14,
1574 p_attribute15,
1575 p_attribute16,
1576 p_attribute17,
1577 p_attribute18,
1578 p_attribute19,
1579 p_attribute20,
1580 p_attribute21,
1581 p_attribute22,
1582 p_attribute23,
1583 p_attribute24,
1584 p_attribute25,
1585 p_attribute26,
1586 p_attribute27,
1587 p_attribute28,
1588 p_attribute29,
1589 p_attribute30
1590 );
1591 ELSIF l_ins_upd = 'U' THEN
1592 ghr_par_extra_info_api.update_pa_request_extra_info
1593 (P_PA_REQUEST_EXTRA_INFO_ID => l_pa_request_extra_info_id,
1594 P_OBJECT_VERSION_NUMBER => l_object_version_number ,
1595 p_rei_information1 => p_attribute1,
1596 p_rei_information2 => p_attribute2,
1597 p_rei_information3 => p_attribute3,
1598 p_rei_information4 => p_attribute4,
1599 p_rei_information5 => p_attribute5,
1600 p_rei_information6 => p_attribute6,
1601 p_rei_information7 => p_attribute7,
1602 p_rei_information8 => p_attribute8,
1603 p_rei_information9 => p_attribute9,
1604 p_rei_information10 => p_attribute10,
1605 p_rei_information11 => p_attribute11,
1606 p_rei_information12 => p_attribute12,
1607 p_rei_information13 => p_attribute13,
1608 p_rei_information14 => p_attribute14,
1609 p_rei_information15 => p_attribute15,
1610 p_rei_information16 => p_attribute16,
1611 p_rei_information17 => p_attribute17,
1612 p_rei_information18 => p_attribute18,
1613 p_rei_information19 => p_attribute19,
1614 p_rei_information20 => p_attribute20,
1615 p_rei_information21 => p_attribute21,
1616 p_rei_information22 => p_attribute22,
1617 p_rei_information23 => p_attribute23,
1618 p_rei_information24 => p_attribute24,
1619 p_rei_information25 => p_attribute25,
1620 p_rei_information26 => p_attribute26,
1621 p_rei_information27 => p_attribute27,
1622 p_rei_information28 => p_attribute28,
1623 p_rei_information29 => p_attribute29,
1624 p_rei_information30 => p_attribute30
1625 );
1626 hr_utility.set_location('bef upd of shad ' || 'ovn ' || l_object_version_number,2);
1627
1628 update ghr_pa_request_ei_shadow set
1629 rei_information1 = p_attribute1,
1630 rei_information2 = p_attribute2,
1631 rei_information3 = p_attribute3,
1632 rei_information4 = p_attribute4,
1633 rei_information5 = p_attribute5,
1634 rei_information6 = p_attribute6,
1635 rei_information7 = p_attribute7,
1636 rei_information8 = p_attribute8,
1637 rei_information9 = p_attribute9,
1638 rei_information10 = p_attribute10,
1639 rei_information11 = p_attribute11,
1640 rei_information12 = p_attribute12,
1641 rei_information13 = p_attribute13,
1642 rei_information14 = p_attribute14,
1643 rei_information15 = p_attribute15,
1644 rei_information16 = p_attribute16,
1645 rei_information17 = p_attribute17,
1646 rei_information18 = p_attribute18,
1647 rei_information19 = p_attribute19,
1648 rei_information20 = p_attribute20,
1649 rei_information21 = p_attribute21,
1650 rei_information22 = p_attribute22,
1651 rei_information23 = p_attribute23,
1652 rei_information24 = p_attribute24,
1653 rei_information25 = p_attribute25,
1654 rei_information26 = p_attribute26,
1655 rei_information27 = p_attribute27,
1656 rei_information28 = p_attribute28,
1657 rei_information29 = p_attribute29,
1658 rei_information30 = p_attribute30
1659 where pa_request_extra_info_id = l_pa_request_extra_info_id;
1660 END IF;
1661 END POPULATE_PA_REQUEST_EXTRA_INFO;
1662
1663 PROCEDURE UPDATE_SHADOW_ROW(p_sf52_rec in ghr_pa_requests%rowtype,
1664 p_result out nocopy BOOLEAN) IS
1665 l_sf52_shadow_rec ghr_pa_request_shadow%ROWTYPE;
1666 BEGIN
1667
1668 UPDATE GHR_PA_REQUEST_SHADOW
1669 SET
1670 pa_request_id = p_sf52_rec.pa_request_id
1671 ,academic_discipline = p_sf52_rec.academic_discipline
1672 ,annuitant_indicator = p_sf52_rec.annuitant_indicator
1673 ,appropriation_code1 = p_sf52_rec.appropriation_code1
1674 ,appropriation_code2 = p_sf52_rec.appropriation_code2
1675 ,bargaining_unit_status = p_sf52_rec.bargaining_unit_status
1676 ,citizenship = p_sf52_rec.citizenship
1677 ,duty_station_id = p_sf52_rec.duty_station_id
1678 ,duty_station_location_id = p_sf52_rec.duty_station_location_id
1679 ,education_level = p_sf52_rec.education_level
1680 ,fegli = p_sf52_rec.fegli
1681 ,flsa_category = p_sf52_rec.flsa_category
1682 ,forwarding_address_line1 = p_sf52_rec.forwarding_address_line1
1683 ,forwarding_address_line2 = p_sf52_rec.forwarding_address_line2
1684 ,forwarding_address_line3 = p_sf52_rec.forwarding_address_line3
1685 ,forwarding_country_short_name = p_sf52_rec.forwarding_country_short_name
1686 ,forwarding_postal_code = p_sf52_rec.forwarding_postal_code
1687 ,forwarding_region_2 = p_sf52_rec.forwarding_region_2
1688 ,forwarding_town_or_city = p_sf52_rec.forwarding_town_or_city
1689 ,functional_class = p_sf52_rec.functional_class
1690 ,part_time_hours = p_sf52_rec.part_time_hours
1691 ,pay_rate_determinant = p_sf52_rec.pay_rate_determinant
1692 ,position_occupied = p_sf52_rec.position_occupied
1693 ,retirement_plan = p_sf52_rec.retirement_plan
1694 ,service_comp_date = p_sf52_rec.service_comp_date
1695 ,supervisory_status = p_sf52_rec.supervisory_status
1696 ,tenure = p_sf52_rec.tenure
1697 ,to_ap_premium_pay_indicator = p_sf52_rec.to_ap_premium_pay_indicator
1698 ,to_auo_premium_pay_indicator = p_sf52_rec.to_auo_premium_pay_indicator
1699 ,to_occ_code = p_sf52_rec.to_occ_code
1700 ,to_position_id = p_sf52_rec.to_position_id
1701 ,to_retention_allowance = p_sf52_rec.to_retention_allowance
1702 ,to_retention_allow_percentage = p_sf52_rec.to_retention_allow_percentage
1703 ,to_staffing_differential = p_sf52_rec.to_staffing_differential
1704 ,to_staffing_diff_percentage = p_sf52_rec.to_staffing_diff_percentage
1705 ,to_step_or_rate = p_sf52_rec.to_step_or_rate
1706 ,to_supervisory_differential = p_sf52_rec.to_supervisory_differential
1707 ,to_supervisory_diff_percentage = p_sf52_rec.to_supervisory_diff_percentage
1708 ,veterans_preference = p_sf52_rec.veterans_preference
1709 ,veterans_pref_for_rif = p_sf52_rec.veterans_pref_for_rif
1710 ,veterans_status = p_sf52_rec.veterans_status
1711 ,work_schedule = p_sf52_rec.work_schedule
1712 ,year_degree_attained = p_sf52_rec.year_degree_attained
1713 WHERE pa_request_id = p_sf52_rec.pa_request_id;
1714
1715
1716 if sql%notfound then
1717 p_result := FALSE;
1718 else
1719 p_result := TRUE;
1720 end if;
1721
1722 EXCEPTION
1723 WHEN others THEN
1724 p_result := NULL;
1725 RAISE;
1726
1727 END UPDATE_SHADOW_ROW;
1728
1729 END;