[Home] [Help]
PACKAGE BODY: APPS.GHR_MASS_CON_PROGRAM
Source
1 PACKAGE BODY GHR_MASS_CON_PROGRAM AS
2 /* $Header: ghmascon.pkb 120.6 2011/04/09 15:15:59 utokachi noship $ */
3
4
5 g_package varchar2(32) := 'GHR_MASS_CON_PROGRAM.';
6 l_information_type varchar2(40);
7 g_log_name varchar2(30);
8 g_log_text varchar2(2000);
9 g_furlo_effective_date ghr_pa_requests.effective_date%type;
10
11 PROCEDURE get_pa_request_id_ovn (
12 p_pa_request_id in ghr_pa_requests.pa_request_id%TYPE,
13 p_effective_date in date,
14 p_person_id in per_people_f.person_id%TYPE,
15 p_pa_notification_id out nocopy ghr_pa_requests.pa_notification_id%TYPE,
16 p_rpa_type out nocopy ghr_pa_requests.rpa_type%TYPE,
17 p_object_version_number out nocopy ghr_pa_requests.object_version_number%TYPE)
18
19 IS
20
21 l_proc varchar2(72) := g_package || 'get_pa_request_id_ovn';
22 CURSOR cur_rpa IS
23 SELECT object_version_number,
24 pa_notification_id,rpa_type
25 FROM ghr_pa_requests par
26 WHERE pa_request_id =p_pa_request_id;
27
28 BEGIN
29
30 hr_utility.set_location('Entering ' || l_proc,5);
31 p_object_version_number := Null;
32 p_pa_notification_id := Null;
33
34 for cur_rpa_rec in cur_rpa loop
35 hr_utility.set_location( l_proc,10);
36 p_object_version_number := cur_rpa_rec.object_version_number;
37 p_pa_notification_id := cur_rpa_rec.pa_notification_id;
38 p_rpa_type := cur_rpa_rec.rpa_type;
39 end loop;
40 hr_utility.set_location('pa_requst_id value ' || to_char(p_pa_request_id) ,15);
41 hr_utility.set_location('pa_notifn_id value ' || to_char(p_pa_notification_id),15);
42 hr_utility.set_location('pa_rpa_type value ' || p_rpa_type,15);
43 hr_utility.set_location('pa_ovn value ' || to_char(p_object_version_number),15);
44 hr_utility.set_location('Leaving ' || l_proc,15);
45
46 EXCEPTION
47 WHEN others THEN
48 -- Reset IN OUT parameters and set OUT parameters
49
50 p_pa_notification_id :=NULL;
51 p_object_version_number :=NULL;
52 hr_utility.set_location('Leaving ' || l_proc,20);
53 RAISE;
54
55 END get_pa_request_id_ovn;
56
57 PROCEDURE refresh_ei_details
58 (
59 p_noa_action in ghr_pa_requests.first_noa_code%TYPE,
60 p_effective_date in date,
61 p_person_id in per_people_f.person_id%TYPE,
62 p_pa_request_id in ghr_pa_requests.pa_request_id%type
63 )
64
65 IS
66
67 l_proc varchar2(72) := g_package || 'refresh_ei_details';
68
69 l_pa_request_extra_info_id number;
70 l_pa_request_extra_info_sh_id number;
71 l_pa_object_version_number number;
72 l_dummy varchar2(30);
73 l_information_type varchar2(40);
74 l_employment_type varchar2(5);
75 l_pei_extra_info per_people_extra_info%rowtype;
76
77
78 CURSOR cur_rpa_ei (p_pa_request_id number) is
79 SELECT pa_request_extra_info_id,
80 object_version_number
81 FROM ghr_pa_request_extra_info
82 WHERE information_type = l_information_type
83 AND pa_request_id = p_pa_request_id;
84
85 CURSOR cur_rpa_ei_shadow (p_pa_request_id number) is
86 SELECT pa_request_extra_info_id
87 FROM ghr_pa_request_ei_shadow
88 WHERE information_type = l_information_type
89 AND pa_request_id = p_pa_request_id;
90 l_pa_rei_id number;
91 BEGIN
92 hr_utility.set_location('Entering ' || l_proc,5);
93 IF p_noa_action ='472' THEN
94 l_information_type := 'GHR_US_PAR_LWOP_INFO';
95 l_employment_type := 'F';
96 ELSIF p_noa_action ='292' THEN
97 l_information_type := 'GHR_US_PAR_RETURN_TO_DUTY';
98 ghr_history_fetch.fetch_peopleei(p_person_id => p_person_id
99 ,p_information_type => 'GHR_US_PER_GROUP1'
100 ,p_date_effective => g_furlo_effective_date - 1
101 ,p_per_ei_data => l_pei_extra_info);
102 l_employment_type := l_pei_extra_info.pei_information4;
103 END IF;
104 FOR cur_rpa_ei_rec in cur_rpa_ei(p_pa_request_id)
105 LOOP
106 hr_utility.set_location( l_proc,10);
107 l_pa_request_extra_info_id := cur_rpa_ei_rec.pa_request_extra_info_id;
108 l_pa_object_version_number := cur_rpa_ei_rec.object_version_number;
109 exit;
110 END LOOP;
111
112 FOR cur_rpa_ei_shadow_rec in cur_rpa_ei_shadow(p_pa_request_id)
113 LOOP
114 hr_utility.set_location( l_proc,11);
115 l_pa_request_extra_info_sh_id := cur_rpa_ei_shadow_rec.pa_request_extra_info_id;
116 exit;
117 END LOOP;
118
119 hr_utility.set_location(l_proc,40);
120 IF l_pa_request_extra_info_id is null then
121 hr_utility.set_location(l_proc,45);
122 IF p_noa_action ='472' THEN
123 hr_utility.set_location( l_proc,12);
124 ghr_par_extra_info_api.create_pa_request_extra_info
125 (p_validate => false,
126 p_pa_request_id => p_pa_request_id,
127 p_information_type => l_information_type,
128 p_rei_information_category => l_information_type,
129 p_rei_information3 => l_Employment_type,
130 p_pa_request_extra_info_id => l_pa_rei_id,
131 p_object_version_number => l_dummy
132 );
133 ELSIF p_noa_action ='292' THEN
134 hr_utility.set_location( l_proc,13);
135 ghr_par_extra_info_api.create_pa_request_extra_info
136 (p_validate => false,
137 p_pa_request_id => p_pa_request_id,
138 p_information_type => l_information_type,
139 p_rei_information_category => l_information_type,
140 p_rei_information8 => l_Employment_type,
141 p_pa_request_extra_info_id => l_dummy,
142 p_object_version_number => l_dummy
143 );
144 END IF;
145 ELSE
146 hr_utility.set_location(l_proc,14);
147 IF p_noa_action ='472' THEN
148 ghr_par_extra_info_api.update_pa_request_extra_info
149 (p_validate => false,
150 p_rei_information3 => l_Employment_type,
151 p_pa_request_extra_info_id => l_pa_request_extra_info_id,
152 p_object_version_number => l_pa_object_version_number);
153 ELSIF p_noa_action ='292' THEN
154 ghr_par_extra_info_api.update_pa_request_extra_info
155 (p_validate => false,
156 p_rei_information8 => l_employment_type,
157 p_pa_request_extra_info_id => l_pa_request_extra_info_id,
158 p_object_version_number => l_pa_object_version_number);
159 END IF;
160 END IF;
161 IF l_pa_request_extra_info_sh_id is null then
162 hr_utility.set_location( l_proc,15);
163 IF p_noa_action ='472' THEN
164 insert into ghr_pa_request_ei_shadow
165 (pa_request_extra_info_id,
166 pa_request_id,
167 information_type,
168 rei_information3)
169 values (ghr_pa_request_extra_info_s.nextval,
170 p_pa_request_id,
171 l_information_type,
172 l_employment_type);
173 ELSIF p_noa_action ='292' THEN
174 insert into ghr_pa_request_ei_shadow
175 (pa_request_extra_info_id,
176 pa_request_id,
177 information_type,
178 rei_information8)
179 values (ghr_pa_request_extra_info_s.nextval,
180 p_pa_request_id,
181 l_information_type,
182 l_employment_type);
183 END IF;
184
185 ELSE
186 hr_utility.set_location( l_proc,16);
187 IF p_noa_action ='472' THEN
188 update ghr_pa_request_ei_shadow
189 set rei_information3 = l_employment_type
190 where pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
191 ELSIF p_noa_action ='292' THEN
192 update ghr_pa_request_ei_shadow
193 set rei_information8 = l_employment_type
194 where pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
195 END IF;
196 END IF;
197
198 EXCEPTION
199
200 when others then
201 hr_utility.set_location('Error in ghr_par_extra info.create pa req'||
202 ' Sql Err is '|| sqlerrm(sqlcode) || l_proc, 60);
203
204 END refresh_ei_details;
205
206 Procedure create_shadow_row ( p_rpa_data in ghr_pa_requests%rowtype) is
207 Begin
208 insert into ghr_pa_request_shadow(
209 pa_request_id
210 ,academic_discipline
211 ,annuitant_indicator
212 ,appropriation_code1
213 ,appropriation_code2
214 ,bargaining_unit_status
215 ,citizenship
216 ,duty_station_id
217 ,duty_station_location_id
218 ,education_level
219 ,employee_date_of_birth
220 ,employee_first_name
221 ,employee_last_name
222 ,employee_middle_names
223 ,employee_national_identifier
224 ,fegli
225 ,flsa_category
226 ,forwarding_address_line1
227 ,forwarding_address_line2
228 ,forwarding_address_line3
229 ,forwarding_country_short_name
230 ,forwarding_postal_code
231 ,forwarding_region_2
232 ,forwarding_town_or_city
233 ,functional_class
234 ,part_time_hours
235 ,pay_rate_determinant
236 ,position_occupied
237 ,retirement_plan
238 ,service_comp_date
239 ,supervisory_status
240 ,tenure
241 ,to_ap_premium_pay_indicator
242 ,to_auo_premium_pay_indicator
243 ,to_occ_code
244 ,to_position_id
245 ,to_retention_allowance
246 ,to_staffing_differential
247 ,to_step_or_ratE
248 ,to_supervisory_differential
249 ,veterans_preference
250 ,veterans_pref_for_riF
251 ,veterans_status
252 ,work_schedule
253 ,year_degree_attained
254 ,to_retention_allow_percentage
255 ,to_supervisory_diff_percentage
256 ,to_staffing_diff_percentage
257 ,first_action_la_code1
258 ,award_amount
259 ,award_uom
260 ,award_percentage )
261 values (
262 p_rpa_data.pa_request_id
263 ,p_rpa_data.academic_discipline
264 ,p_rpa_data.annuitant_indicator
265 ,p_rpa_data.appropriation_code1
266 ,p_rpa_data.appropriation_code2
267 ,p_rpa_data.bargaining_unit_status
268 ,p_rpa_data.citizenship
269 ,p_rpa_data.duty_station_id
270 ,p_rpa_data.duty_station_location_id
271 ,p_rpa_data.education_level
272 ,p_rpa_data.employee_date_of_birth
273 ,p_rpa_data.employee_first_name
274 ,p_rpa_data.employee_last_name
275 ,p_rpa_data.employee_middle_names
276 ,p_rpa_data.employee_national_identifier
277 ,p_rpa_data.fegli
278 ,p_rpa_data.flsa_category
279 ,p_rpa_data.forwarding_address_line1
280 ,p_rpa_data.forwarding_address_line2
281 ,p_rpa_data.forwarding_address_line3
282 ,p_rpa_data.forwarding_country_short_name
283 ,p_rpa_data.forwarding_postal_code
284 ,p_rpa_data.forwarding_region_2
285 ,p_rpa_data.forwarding_town_or_city
286 ,p_rpa_data.functional_class
287 ,p_rpa_data.part_time_hours
288 ,p_rpa_data.pay_rate_determinant
289 ,p_rpa_data.position_occupied
290 ,p_rpa_data.retirement_plan
291 ,p_rpa_data.service_comp_date
292 ,p_rpa_data.supervisory_status
293 ,p_rpa_data.tenure
294 ,p_rpa_data.to_ap_premium_pay_indicator
295 ,p_rpa_data.to_auo_premium_pay_indicator
296 ,p_rpa_data.to_occ_code
297 ,p_rpa_data.to_position_id
298 ,p_rpa_data.to_retention_allowance
299 ,p_rpa_data.to_staffing_differential
300 ,p_rpa_data.to_step_or_ratE
301 ,p_rpa_data.to_supervisory_differential
302 ,p_rpa_data.veterans_preference
303 ,p_rpa_data.veterans_pref_for_riF
304 ,p_rpa_data.veterans_status
305 ,p_rpa_data.work_schedule
306 ,p_rpa_data.year_degree_attained
307 ,p_rpa_data.to_retention_allow_percentage
308 ,p_rpa_data.to_supervisory_diff_percentage
309 ,p_rpa_data.to_staffing_diff_percentage
310 ,p_rpa_data.first_action_la_code1
311 ,p_rpa_data.award_amount
312 ,p_rpa_data.award_uom
313 ,p_rpa_data.award_percentage );
314 end create_shadow_row;
315
316 Procedure update_shadow_row ( p_rpa_data in ghr_pa_requests%rowtype,
317 p_result out nocopy Boolean ) is
318 Begin
319 update ghr_pa_request_shadow
320 set
321 academic_discipline = p_rpa_data.academic_discipline
322 ,annuitant_indicator = p_rpa_data.annuitant_indicator
323 ,appropriation_code1 = p_rpa_data.appropriation_code1
324 ,appropriation_code2 = p_rpa_data.appropriation_code2
325 ,bargaining_unit_status = p_rpa_data.bargaining_unit_status
326 ,citizenship = p_rpa_data.citizenship
327 ,duty_station_id = p_rpa_data.duty_station_id
328 ,duty_station_location_id = p_rpa_data.duty_station_location_id
329 ,education_level = p_rpa_data.education_level
330 ,employee_date_of_birth = p_rpa_data.employee_date_of_birth
331 ,employee_first_name = p_rpa_data.employee_first_name
332 ,employee_last_name = p_rpa_data.employee_last_name
333 ,employee_middle_names = p_rpa_data.employee_middle_names
334 ,employee_national_identifier = p_rpa_data.employee_national_identifier
335 ,fegli = p_rpa_data.fegli
336 ,flsa_category = p_rpa_data.flsa_category
337 ,forwarding_address_line1 = p_rpa_data.forwarding_address_line1
338 ,forwarding_address_line2 = p_rpa_data.forwarding_address_line2
339 ,forwarding_address_line3 = p_rpa_data.forwarding_address_line3
340 ,forwarding_country_short_name = p_rpa_data.forwarding_country_short_name
341 ,forwarding_postal_code = p_rpa_data.forwarding_postal_code
342 ,forwarding_region_2 = p_rpa_data.forwarding_region_2
343 ,forwarding_town_or_city = p_rpa_data.forwarding_town_or_city
344 ,functional_class = p_rpa_data.functional_class
345 ,part_time_hours = p_rpa_data.part_time_hours
346 ,pay_rate_determinant = p_rpa_data.pay_rate_determinant
347 ,position_occupied = p_rpa_data.position_occupied
348 ,retirement_plan = p_rpa_data.retirement_plan
349 ,service_comp_date = p_rpa_data.service_comp_date
350 ,supervisory_status = p_rpa_data.supervisory_status
351 ,tenure = p_rpa_data.tenure
352 ,to_ap_premium_pay_indicator = p_rpa_data.to_ap_premium_pay_indicator
353 ,to_auo_premium_pay_indicator = p_rpa_data.to_auo_premium_pay_indicator
354 ,to_occ_code = p_rpa_data.to_occ_code
355 ,to_position_id = p_rpa_data.to_position_id
356 ,to_retention_allowance = p_rpa_data.to_retention_allowance
357 ,to_staffing_differential = p_rpa_data.to_staffing_differential
358 ,to_step_or_ratE = p_rpa_data.to_step_or_ratE
359 ,to_supervisory_differential = p_rpa_data.to_supervisory_differential
360 ,veterans_preference = p_rpa_data.veterans_preference
361 ,veterans_pref_for_riF = p_rpa_data.veterans_pref_for_riF
362 ,veterans_status = p_rpa_data.veterans_status
363 ,work_schedule = p_rpa_data.work_schedule
364 ,year_degree_attained = p_rpa_data.year_degree_attained
365 ,to_retention_allow_percentage = p_rpa_data.to_retention_allow_percentage
366 ,to_supervisory_diff_percentage = p_rpa_data.to_supervisory_diff_percentage
367 ,to_staffing_diff_percentage = p_rpa_data.to_staffing_diff_percentage
368 ,first_action_la_code1 = p_rpa_data.first_action_la_code1
369 where pa_request_id = p_rpa_data.pa_request_id;
370
371 if sql%notfound then
372 p_result := FALSE;
373 else
374 p_result := TRUE;
375 end if;
376
377 EXCEPTION
378 WHEN others THEN
379 p_result := NULL;
380 RAISE;
381 end update_shadow_row;
382
383 PROCEDURE get_furlough_rtd_lac
384 ( p_noa_action in VARCHAR2,
385 p_pay_plan in VARCHAR2,
386 p_effective_date in date,
387 p_first_lac1_record out nocopy first_lac1_record,
388 p_first_lac2_record out nocopy first_lac2_record
389 )
390 IS
391
392 l_proc varchar2(72) := g_package || 'get_furlough_rtd_lac';
393 g_equivalent_pay_plan ghr_pay_plans.equivalent_pay_plan%TYPE;
394 cursor c_eql_pay_plan is
395 SELECT equivalent_pay_plan
396 INTO g_equivalent_pay_plan
397 FROM ghr_pay_plans
398 WHERE pay_plan = p_pay_plan;
399
400 BEGIN
401
402 hr_utility.set_location('Entering ' || l_proc,5);
403 p_first_lac1_record := Null;
404 p_first_lac2_record := Null;
405
406 IF p_noa_action IN ('472','772') THEN
407 FOR l_eql_pay_plan in c_eql_pay_plan LOOP
408 g_equivalent_pay_plan := l_eql_pay_plan.equivalent_pay_plan;
409 END LOOP;
410 IF g_equivalent_pay_plan ='ES' THEN
411 p_first_lac1_record.first_action_la_code1 := 'VDR';
412 p_first_lac1_record.first_action_la_desc1 := ghr_pa_requests_pkg.get_lookup_description(800,'GHR_US_LEGAL_AUTHORITY','VDR');
413 ELSE
414 p_first_lac1_record.first_action_la_code1 := 'VAJ';
415 p_first_lac1_record.first_action_la_desc1 := ghr_pa_requests_pkg.get_lookup_description(800,'GHR_US_LEGAL_AUTHORITY','VAJ');
416 END IF;
417 ELSIF p_noa_action ='292' THEN
418 p_first_lac1_record.first_action_la_code1 := 'CGM';
419 p_first_lac1_record.first_action_la_desc1 := ghr_pa_requests_pkg.get_lookup_description(800,'GHR_US_LEGAL_AUTHORITY','CGM');
420
421 END IF;
422 hr_utility.set_location('Leaving ' || l_proc,20);
423
424 EXCEPTION
425 WHEN others THEN
426 p_first_lac1_record := Null;
427 p_first_lac2_record := Null;
428 hr_utility.set_location('Leaving ' || l_proc,25);
429 RAISE;
430 END get_furlough_rtd_lac;
431
432 Procedure create_remarks
433 (p_noa_action IN VARCHAR2,
434 p_effective_date IN date,
435 p_person_id IN number,
436 p_pa_request_id IN ghr_pa_requests.pa_request_id%type,
437 p_work_schedule IN ghr_pa_requests.work_schedule%type,
438 p_fegli IN VARCHAR2,
439 p_fehb_plan IN VARCHAR2,
440 p_prd IN VARCHAR2
441 )
442 is
443 l_proc varchar2(72) := g_package || 'create_remarks';
444 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
445 l_remark_id ghr_pa_remarks.remark_id%type;
446 l_description ghr_pa_remarks.description%type;
447 l_desc_ins_val ghr_pa_remarks.description%type;
448 l_remark_code_information1 ghr_pa_remarks.remark_code_information1%type;
449 l_remark_code_information2 ghr_pa_remarks.remark_code_information2%type;
450 l_remark_code_information3 ghr_pa_remarks.remark_code_information3%type;
451 l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
452 l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
453 l_p_pa_remark_id ghr_pa_remarks.remark_id%type;
454 l_p_object_version_number ghr_pa_remarks.object_version_number%type;
455
456 begin
457 hr_utility.set_location('Entering ' || l_proc,5);
458 IF p_noa_action IN ('472','772') THEN
459 hr_utility.set_location('Remarks creation for 472, 772 '||l_proc,111);
460
461 IF p_noa_action IN ('472') THEN
462 ghr_mass_actions_pkg.get_remark_id_desc
463 (p_remark_code => 'M72',
464 p_effective_date => p_effective_date,
465 p_remark_id => l_remark_id,
466 p_remark_desc => l_description);
467 ghr_mass_actions_pkg.replace_insertion_values
468 (p_desc => l_description,
469 p_information1 => 'Lapse in appropriations',
470 p_desc_out => l_desc_ins_val
471 );
472 ghr_pa_remarks_api.create_pa_remarks
473 (
474 p_pa_request_id => p_pa_request_id
475 ,p_remark_id => l_remark_id
476 ,p_description => l_desc_ins_val
477 ,p_remark_code_information1 => 'Lapse in appropriations'
478 ,p_remark_code_information2 => l_remark_code_information2
479 ,p_remark_code_information3 => l_remark_code_information3
480 ,p_remark_code_information4 => l_remark_code_information4
481 ,p_remark_code_information5 => l_remark_code_information5
482 ,p_pa_remark_id => l_p_pa_remark_id
483 ,p_object_version_number => l_p_object_version_number
484 );
485 END IF;
486
487 IF p_work_schedule IN('B','F','G','P','Q','S','T') THEN
488 ghr_mass_actions_pkg.get_remark_id_desc
489 (p_remark_code => 'G33',
490 p_effective_date => p_effective_date,
491 p_remark_id => l_remark_id,
492 p_remark_desc => l_description);
493 ghr_pa_remarks_api.create_pa_remarks
494 (
495 p_pa_request_id => p_pa_request_id
496 ,p_remark_id => l_remark_id
497 ,p_description => l_description
498 ,p_remark_code_information1 => l_remark_code_information1
499 ,p_remark_code_information2 => l_remark_code_information2
500 ,p_remark_code_information3 => l_remark_code_information3
501 ,p_remark_code_information4 => l_remark_code_information4
502 ,p_remark_code_information5 => l_remark_code_information5
503 ,p_pa_remark_id => l_p_pa_remark_id
504 ,p_object_version_number => l_p_object_version_number
505 );
506 END IF;
507 IF p_fegli NOT IN ('A0','A1','B0') THEN
508 ghr_mass_actions_pkg.get_remark_id_desc
509 (p_remark_code => 'B72',
510 p_effective_date => p_effective_date,
511 p_remark_id => l_remark_id,
512 p_remark_desc => l_description);
513 ghr_pa_remarks_api.create_pa_remarks
514 (
515 p_pa_request_id => p_pa_request_id
516 ,p_remark_id => l_remark_id
517 ,p_description => l_description
518 ,p_remark_code_information1 => l_remark_code_information1
519 ,p_remark_code_information2 => l_remark_code_information2
520 ,p_remark_code_information3 => l_remark_code_information3
521 ,p_remark_code_information4 => l_remark_code_information4
522 ,p_remark_code_information5 => l_remark_code_information5
523 ,p_pa_remark_id => l_p_pa_remark_id
524 ,p_object_version_number => l_p_object_version_number
525 );
526 END IF;
527 IF p_fehb_plan <>'ZZ' THEN
528 ghr_mass_actions_pkg.get_remark_id_desc
529 (p_remark_code => 'B71',
530 p_effective_date => p_effective_date,
531 p_remark_id => l_remark_id,
532 p_remark_desc => l_description);
533 ghr_pa_remarks_api.create_pa_remarks
534 (
535 p_pa_request_id => p_pa_request_id
536 ,p_remark_id => l_remark_id
537 ,p_description => l_description
538 ,p_remark_code_information1 => l_remark_code_information1
539 ,p_remark_code_information2 => l_remark_code_information2
540 ,p_remark_code_information3 => l_remark_code_information3
541 ,p_remark_code_information4 => l_remark_code_information4
542 ,p_remark_code_information5 => l_remark_code_information5
543 ,p_pa_remark_id => l_p_pa_remark_id
544 ,p_object_version_number => l_p_object_version_number
545 );
546
547 END IF;
548 ELSIF p_noa_action ='292' THEN
549 hr_utility.set_location('Remarks creation for 292'||l_proc,111);
550 IF p_prd IN ('A','B','E','F','U','V') THEN
551 ghr_mass_actions_pkg.get_remark_id_desc
552 (p_remark_code => 'X45',
553 p_effective_date => p_effective_date,
554 p_remark_id => l_remark_id,
555 p_remark_desc => l_description);
556 ghr_pa_remarks_api.create_pa_remarks
557 (
558 p_pa_request_id => p_pa_request_id
559 ,p_remark_id => l_remark_id
560 ,p_description => l_description
561 ,p_remark_code_information1 => l_remark_code_information1
562 ,p_remark_code_information2 => l_remark_code_information2
563 ,p_remark_code_information3 => l_remark_code_information3
564 ,p_remark_code_information4 => l_remark_code_information4
565 ,p_remark_code_information5 => l_remark_code_information5
566 ,p_pa_remark_id => l_p_pa_remark_id
567 ,p_object_version_number => l_p_object_version_number
568 );
569 ghr_mass_actions_pkg.get_remark_id_desc
570 (p_remark_code => 'X61',
571 p_effective_date => p_effective_date,
572 p_remark_id => l_remark_id,
573 p_remark_desc => l_description);
574 ghr_pa_remarks_api.create_pa_remarks
575 (
576 p_pa_request_id => p_pa_request_id
577 ,p_remark_id => l_remark_id
578 ,p_description => l_description
579 ,p_remark_code_information1 => l_remark_code_information1
580 ,p_remark_code_information2 => l_remark_code_information2
581 ,p_remark_code_information3 => l_remark_code_information3
582 ,p_remark_code_information4 => l_remark_code_information4
583 ,p_remark_code_information5 => l_remark_code_information5
584 ,p_pa_remark_id => l_p_pa_remark_id
585 ,p_object_version_number => l_p_object_version_number
586 );
587 END IF;
588 END IF;
589 hr_utility.set_location('Leaving ' || l_proc,5);
590 end create_remarks;
591
592 procedure marpa_process
593 (
594 p_noa_action in VARCHAR2,
595 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
596 p_effective_date in date,
597 p_person_id in per_people_f.person_id%TYPE,
598 p_pa_request_rec in out nocopy ghr_pa_requests%rowtype ,
599 p_log_text out nocopy varchar2
600 )
601 IS
602
603 l_proc varchar2(72) := g_package || 'marpa_process';
604 l_pa_request_rec ghr_pa_requests%rowtype;
605 l_log_text varchar2(2000);
606 l_result boolean;
607 l_dummy varchar2(30);
608 l_dummy_number number;
609 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
610 l_pa_notification_id ghr_pa_requests.pa_notification_id%TYPE;
611 l_rpa_type ghr_pa_requests.rpa_type%TYPE;
612 l_object_version_number ghr_pa_requests.object_version_number%type;
613 l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
614 l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
615 l_2_prh_object_version_number ghr_pa_requests.object_version_number%type;
616 l_2_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
617 l_approval_date date;
618 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
619 l_1_approval_status varchar2(10);
620 l_u_prh_object_version_number number;
621 l_i_pa_routing_history_id number;
622 l_i_prh_object_version_number number;
623 ma_rpaerror exception;
624 l_error_flag BOOLEAN;
625
626 BEGIN
627 l_pa_request_rec := p_pa_request_rec;
628
629 begin --Begin 2
630
631 l_approval_date := sysdate;
632 l_1_approval_status := 'APPROVE';
633
634 hr_utility.set_location('Entering ...' || l_proc,120);
635 hr_utility.set_location('Noa_family_code value ' || l_pa_request_rec.noa_family_code,120);
636 get_pa_request_id_ovn
637 ( p_pa_request_id => l_pa_request_rec.pa_request_id,
638 p_effective_date => p_effective_date,
639 p_person_id => p_person_id,
640 p_pa_notification_id => l_pa_notification_id,
641 p_rpa_type => l_rpa_type,
642 p_object_version_number => l_object_version_number);
643
644
645 IF l_pa_notification_id is null then
646 IF l_pa_request_rec.pa_request_id is null THEN
647 hr_utility.set_location(l_proc,130);
648 begin
649 l_log_text := p_noa_action || '-Error while creating the PA Request Rec. ';
650 g_log_text := p_noa_action || '-Error while creating the PA Request Rec. ';
651 ghr_sf52_api.create_sf52
652 ( p_noa_family_code => l_pa_request_rec.noa_family_code,
653 p_proposed_effective_asap_flag => l_pa_request_rec.proposed_effective_asap_flag,
654 p_academic_discipline => l_pa_request_rec.academic_discipline,
655 p_additional_info_person_id => l_pa_request_rec.additional_info_person_id,
656 p_additional_info_tel_number => l_pa_request_rec.additional_info_tel_number,
657 p_annuitant_indicator => l_pa_request_rec.annuitant_indicator,
658 p_annuitant_indicator_desc => l_pa_request_rec.annuitant_indicator_desc,
659 p_appropriation_code1 => l_pa_request_rec.appropriation_code1,
660 p_appropriation_code2 => l_pa_request_rec.appropriation_code2,
661 p_authorized_by_person_id => l_pa_request_rec.authorized_by_person_id,
662 p_authorized_by_title => l_pa_request_rec.authorized_by_title,
663 p_award_amount => l_pa_request_rec.award_amount,
664 p_award_uom => l_pa_request_rec.award_uom,
665 p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status,
666 p_citizenship => l_pa_request_rec.citizenship,
667 p_concurrence_date => l_pa_request_rec.concurrence_date,
668 p_custom_pay_calc_flag => l_pa_request_rec.custom_pay_calc_flag,
669 p_duty_station_code => l_pa_request_rec.duty_station_code,
670 p_duty_station_desc => l_pa_request_rec.duty_station_desc,
671 p_duty_station_id => l_pa_request_rec.duty_station_id,
672 p_duty_station_location_id => l_pa_request_rec.duty_station_location_id,
673 p_education_level => l_pa_request_rec.education_level,
674 p_effective_date => l_pa_request_rec.effective_date,
675 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
676 p_employee_date_of_birth => l_pa_request_rec.employee_date_of_birth,
677 p_employee_first_name => l_pa_request_rec.employee_first_name,
678 p_employee_last_name => l_pa_request_rec.employee_last_name,
679 p_employee_middle_names => l_pa_request_rec.employee_middle_names,
680 p_employee_national_identifier => l_pa_request_rec.employee_national_identifier,
681 p_fegli => l_pa_request_rec.fegli,
682 p_fegli_desc => l_pa_request_rec.fegli_desc,
683 p_first_action_la_code1 => l_pa_request_rec.first_action_la_code1,
684 p_first_action_la_code2 => l_pa_request_rec.first_action_la_code2,
685 p_first_action_la_desc1 => l_pa_request_rec.first_action_la_desc1,
686 p_first_action_la_desc2 => l_pa_request_rec.first_action_la_desc2,
687 p_first_noa_code => l_pa_request_rec.first_noa_code,
688 p_first_noa_desc => l_pa_request_rec.first_noa_desc,
689 p_first_noa_id => l_pa_request_rec.first_noa_id,
690 p_first_noa_information1 => l_pa_request_rec.first_noa_information1,
691 p_first_noa_pa_request_id => l_pa_request_rec.first_noa_pa_request_id,
692 p_flsa_category => l_pa_request_rec.flsa_category,
693 p_from_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay,
694 p_from_basic_pay => l_pa_request_rec.from_basic_pay,
695 p_from_grade_or_level => l_pa_request_rec.from_grade_or_level,
696 p_from_locality_adj => l_pa_request_rec.from_locality_adj,
697 p_from_occ_code => l_pa_request_rec.from_occ_code,
698 p_from_other_pay_amount => l_pa_request_rec.from_other_pay_amount,
699 p_from_pay_basis => l_pa_request_rec.from_pay_basis,
700 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
701 p_input_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
702 p_from_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier,
703 p_from_position_id => l_pa_request_rec.from_position_id,
704 p_from_position_org_line1 => l_pa_request_rec.from_position_org_line1,
705 p_from_position_org_line2 => l_pa_request_rec.from_position_org_line2,
706 p_from_position_org_line3 => l_pa_request_rec.from_position_org_line3,
707 p_from_position_org_line4 => l_pa_request_rec.from_position_org_line4,
708 p_from_position_org_line5 => l_pa_request_rec.from_position_org_line5,
709 p_from_position_org_line6 => l_pa_request_rec.from_position_org_line6,
710 p_from_position_number => l_pa_request_rec.from_position_number,
711 p_from_position_seq_no => l_pa_request_rec.from_position_seq_no,
712 p_from_position_title => l_pa_request_rec.from_position_title,
713 p_from_step_or_rate => l_pa_request_rec.from_step_or_rate,
714 p_from_total_salary => l_pa_request_rec.from_total_salary,
715 p_functional_class => l_pa_request_rec.functional_class,
716 p_notepad => l_pa_request_rec.notepad,
717 p_part_time_hours => l_pa_request_rec.part_time_hours,
718 p_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
719 p_person_id => l_pa_request_rec.person_id,
720 p_position_occupied => l_pa_request_rec.position_occupied,
721 p_proposed_effective_date => l_pa_request_rec.proposed_effective_date,
722 p_requested_by_person_id => l_pa_request_rec.requested_by_person_id,
723 p_requested_by_title => l_pa_request_rec.requested_by_title,
724 p_requested_date => l_pa_request_rec.requested_date,
725 p_requesting_office_remarks_de => l_pa_request_rec.requesting_office_remarks_desc,
726 p_requesting_office_remarks_fl => l_pa_request_rec.requesting_office_remarks_flag,
727 p_resign_and_retire_reason_des => l_pa_request_rec.resign_and_retire_reason_desc,
728 p_retirement_plan => l_pa_request_rec.retirement_plan,
729 p_retirement_plan_desc => l_pa_request_rec.retirement_plan_desc,
730 p_service_comp_date => l_pa_request_rec.service_comp_date,
731 p_supervisory_status => l_pa_request_rec.supervisory_status,
732 p_tenure => l_pa_request_rec.tenure,
733 p_to_adj_basic_pay => l_pa_request_rec.to_adj_basic_pay,
734 p_to_basic_pay => l_pa_request_rec.to_basic_pay,
735 p_to_grade_id => l_pa_request_rec.to_grade_id,
736 p_to_grade_or_level => l_pa_request_rec.to_grade_or_level,
737 p_to_job_id => l_pa_request_rec.to_job_id,
738 p_to_locality_adj => l_pa_request_rec.to_locality_adj,
739 p_to_occ_code => l_pa_request_rec.to_occ_code,
740 p_to_organization_id => l_pa_request_rec.to_organization_id,
741 p_to_other_pay_amount => l_pa_request_rec.to_other_pay_amount,
742 p_to_au_overtime => l_pa_request_rec.to_au_overtime,
743 p_to_auo_premium_pay_indicator => l_pa_request_rec.to_auo_premium_pay_indicator,
744 p_to_availability_pay => l_pa_request_rec.to_availability_pay,
745 p_to_ap_premium_pay_indicator => l_pa_request_rec.to_ap_premium_pay_indicator,
746 p_to_retention_allowance => l_pa_request_rec.to_retention_allowance,
747 p_to_supervisory_differential => l_pa_request_rec.to_supervisory_differential,
748 p_to_staffing_differential => l_pa_request_rec.to_staffing_differential,
749 p_to_pay_basis => l_pa_request_rec.to_pay_basis,
750 p_to_pay_plan => l_pa_request_rec.to_pay_plan,
751 p_to_position_id => l_pa_request_rec.to_position_id,
752 p_to_position_org_line1 => l_pa_request_rec.to_position_org_line1,
753 p_to_position_org_line2 => l_pa_request_rec.to_position_org_line2,
754 p_to_position_org_line3 => l_pa_request_rec.to_position_org_line3,
755 p_to_position_org_line4 => l_pa_request_rec.to_position_org_line4,
756 p_to_position_org_line5 => l_pa_request_rec.to_position_org_line5,
757 p_to_position_org_line6 => l_pa_request_rec.to_position_org_line6,
758 p_to_position_number => l_pa_request_rec.to_position_number,
759 p_to_position_seq_no => l_pa_request_rec.to_position_seq_no,
760 p_to_position_title => l_pa_request_rec.to_position_title,
761 p_to_step_or_rate => l_pa_request_rec.to_step_or_rate,
762 p_to_total_salary => l_pa_request_rec.to_total_salary,
763 p_veterans_preference => l_pa_request_rec.veterans_preference,
764 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
765 p_veterans_status => l_pa_request_rec.veterans_status,
766 p_work_schedule => l_pa_request_rec.work_schedule,
767 p_work_schedule_desc => l_pa_request_rec.work_schedule_desc,
768 p_year_degree_attained => l_pa_request_rec.year_degree_attained,
769 p_first_lac1_information1 => l_pa_request_rec.first_lac1_information1,
770 p_first_lac1_information2 => l_pa_request_rec.first_lac1_information2,
771 p_first_lac1_information3 => l_pa_request_rec.first_lac1_information3,
772 p_first_lac1_information4 => l_pa_request_rec.first_lac1_information4,
773 p_first_lac1_information5 => l_pa_request_rec.first_lac1_information5,
774 p_first_lac2_information1 => l_pa_request_rec.first_lac2_information1,
775 p_first_lac2_information2 => l_pa_request_rec.first_lac2_information2,
776 p_first_lac2_information3 => l_pa_request_rec.first_lac2_information3,
777 p_first_lac2_information4 => l_pa_request_rec.first_lac2_information4,
778 p_first_lac2_information5 => l_pa_request_rec.first_lac2_information5,
779 p_second_lac1_information1 => l_pa_request_rec.second_lac1_information1,
780 p_second_lac1_information2 => l_pa_request_rec.second_lac1_information1,
781 p_second_lac1_information3 => l_pa_request_rec.second_lac1_information1,
782 p_second_lac1_information4 => l_pa_request_rec.second_lac1_information1,
783 p_second_lac1_information5 => l_pa_request_rec.second_lac1_information1,
784 p_print_sf50_flag => 'N', -- true for all ??
785 p_printer_name => Null,
786 p_1_attachment_modified_flag => 'N',
787 p_1_approved_flag => 'N',
788 p_1_user_name_acted_on => Null,
789 p_1_action_taken => 'NOT_ROUTED',
790 p_2_user_name_routed_to => Null,
791 p_2_groupbox_id => Null,
792 p_2_routing_list_id => Null,
793 p_2_routing_seq_number => Null,
794 p_to_retention_allow_percentag => l_pa_request_rec.to_retention_allow_percentage,
795 p_to_supervisory_diff_percenta => l_pa_request_rec.to_supervisory_diff_percentage,
796 p_to_staffing_diff_percentage => l_pa_request_rec.to_staffing_diff_percentage ,
797 p_pa_request_id => l_pa_request_rec.pa_request_id,
798 p_par_object_version_number => l_pa_request_rec.object_version_number,
799 p_1_pa_routing_history_id => l_1_pa_routing_history_id,
800 p_1_prh_object_version_number => l_1_prh_object_version_number,
801 p_2_pa_routing_history_id => l_2_pa_routing_history_id,
802 p_2_prh_object_version_number => l_2_prh_object_version_number,
803 p_rpa_type => p_rpa_type,
804 p_mass_action_id => null,
805 p_approving_official_full_name => l_pa_request_rec.approving_official_full_name
806 ,p_approval_date => l_approval_date
807 ,p_approving_official_work_titl => l_pa_request_rec.approving_official_work_title
808 ,p_1_approval_status => l_1_approval_status
809 );
810 hr_utility.set_location( l_proc,131);
811 exception
812 WHEN OTHERS THEN
813 hr_utility.set_location( ' create_sf52 Sql error : '||sqlerrm(sqlcode) ,132);
814 hr_utility.set_location(l_proc,132);
815 l_log_text := substr(l_log_text || ' Sql error : '|| sqlerrm(sqlcode),1,2000);
816 g_log_text := substr(l_log_text || ' Sql error : '|| sqlerrm(sqlcode),1,2000);
817 raise ma_rpaerror;
818 end;
819
820 hr_utility.set_location(l_proc,135);
821 begin
822 l_log_text := p_noa_action || '-Error After creating the RPA before upd ';
823 g_log_text := p_noa_action || '-Error After creating the RPA before upd ';
824 ghr_par_upd.upd(
825 p_pa_request_id => l_pa_request_rec.pa_request_id,
826 p_object_version_number => l_pa_request_rec.object_version_number,
827 p_agency_code => l_pa_request_rec.agency_code,
828 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
829 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
830 p_from_office_symbol => l_pa_request_rec.from_office_symbol);
831 exception
832 WHEN OTHERS THEN
833 hr_utility.set_location(l_proc,136);
834 l_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
835 g_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
836 raise ma_rpaerror;
837 end;
838
839 hr_utility.set_location(l_proc,138);
840 create_shadow_row (p_rpa_data => l_pa_request_rec);
841 hr_utility.set_location(' Shadow created ...'|| l_proc,138);
842
843 IF p_noa_action IN ('472','292') THEN
844 hr_utility.set_location('Calling refresh_ei_details '||l_proc,140);
845 refresh_ei_details
846 (
847 p_noa_action => p_noa_action,
848 p_effective_date => p_effective_date,
849 p_person_id => p_person_id,
850 p_pa_request_id => l_pa_request_rec.pa_request_id );
851 END IF;
852 ELSE -- IF l_pa_request_id is null
853 hr_utility.set_location(l_proc,141);
854
855 hr_utility.set_location(l_proc || 'pa_request_id ' || to_char(l_pa_request_id),150);
856 hr_utility.set_location(l_proc || 'Object version ' || to_char(l_object_version_number),151);
857
858 begin
859 l_log_text := 'Error while Updating the PA Request Rec. ';
860 g_log_text := 'Error while Updating the PA Request Rec. ';
861 hr_utility.set_location( l_proc,132);
862 ghr_sf52_api.update_sf52
863 (
864 p_pa_request_id => l_pa_request_id,
865 p_noa_family_code => l_pa_request_rec.noa_family_code,
866 p_routing_group_id => l_pa_request_rec.routing_group_id,
867 p_par_object_version_number => l_object_version_number,
868 p_proposed_effective_asap_flag => l_pa_request_rec.proposed_effective_asap_flag,
869 p_academic_discipline => l_pa_request_rec.academic_discipline,
870 p_additional_info_person_id => l_pa_request_rec.additional_info_person_id,
871 p_additional_info_tel_number => l_pa_request_rec.additional_info_tel_number ,
872 p_annuitant_indicator => l_pa_request_rec.annuitant_indicator,
873 p_annuitant_indicator_desc => l_pa_request_rec.annuitant_indicator_desc,
874 p_appropriation_code1 => l_pa_request_rec.appropriation_code1,
875 p_appropriation_code2 => l_pa_request_rec.appropriation_code2,
876 p_approval_date => l_approval_date ,
877 p_approving_official_full_name => l_pa_request_rec.approving_official_full_name,
878 p_approving_official_work_titl => l_pa_request_rec.approving_official_work_title,
879 p_authorized_by_person_id => l_pa_request_rec.authorized_by_person_id ,
880 p_authorized_by_title => l_pa_request_rec.authorized_by_title,
881 p_award_amount => l_pa_request_rec.award_amount,
882 p_award_uom => l_pa_request_rec.award_uom,
883 p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status,
884 p_citizenship => l_pa_request_rec.citizenship,
885 p_concurrence_date => l_pa_request_rec.concurrence_date,
886 p_custom_pay_calc_flag => l_pa_request_rec.custom_pay_calc_flag,
887 p_duty_station_code => l_pa_request_rec.duty_station_code,
888 p_duty_station_desc => l_pa_request_rec.duty_station_desc,
889 p_duty_station_id => l_pa_request_rec.duty_station_id,
890 p_duty_station_location_id => l_pa_request_rec.duty_station_location_id,
891 p_education_level => l_pa_request_rec.education_level,
892 p_effective_date => l_pa_request_rec.effective_date,
893 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
894 p_employee_date_of_birth => l_pa_request_rec.employee_date_of_birth,
895 p_employee_first_name => l_pa_request_rec.employee_first_name,
896 p_employee_last_name => l_pa_request_rec.employee_last_name,
897 p_employee_middle_names => l_pa_request_rec.employee_middle_names,
898 p_employee_national_identifier => l_pa_request_rec.employee_national_identifier,
899 p_fegli => l_pa_request_rec.fegli,
900 p_fegli_desc => l_pa_request_rec.fegli_desc,
901 p_first_action_la_code1 => l_pa_request_rec.first_action_la_code1,
902 p_first_action_la_code2 => l_pa_request_rec.first_action_la_code2,
903 p_first_action_la_desc1 => l_pa_request_rec.first_action_la_desc1,
904 p_first_action_la_desc2 => l_pa_request_rec.first_action_la_desc2,
905 p_first_noa_cancel_or_correct => l_pa_request_rec.first_noa_cancel_or_correct,
906 p_first_noa_code => l_pa_request_rec.first_noa_code,
907 p_first_noa_desc => l_pa_request_rec.first_noa_desc,
908 p_first_noa_id => l_pa_request_rec.first_noa_id,
909 p_first_noa_pa_request_id => l_pa_request_rec.first_noa_pa_request_id,
910 p_flsa_category => l_pa_request_rec.flsa_category,
911 p_forwarding_address_line1 => l_pa_request_rec.forwarding_address_line1,
912 p_forwarding_address_line2 => l_pa_request_rec.forwarding_address_line2,
913 p_forwarding_address_line3 => l_pa_request_rec.forwarding_address_line3,
914 p_forwarding_country => l_pa_request_rec.forwarding_country,
915 p_forwarding_country_short_nam => l_pa_request_rec.forwarding_country_short_name,
916 p_forwarding_postal_code => l_pa_request_rec.forwarding_postal_code,
917 p_forwarding_region_2 => l_pa_request_rec.forwarding_region_2,
918 p_forwarding_town_or_city => l_pa_request_rec.forwarding_town_or_city,
919 p_from_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay,
920 p_from_basic_pay => l_pa_request_rec.from_basic_pay,
921 p_from_grade_or_level => l_pa_request_rec.from_grade_or_level,
922 p_from_locality_adj => l_pa_request_rec.from_locality_adj,
923 p_from_occ_code => l_pa_request_rec.from_occ_code,
924 p_from_other_pay_amount => l_pa_request_rec.from_other_pay_amount,
925 p_from_pay_basis => l_pa_request_rec.from_pay_basis,
926 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
927 p_input_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
928 p_from_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier,
929 p_from_position_id => l_pa_request_rec.from_position_id,
930 p_from_position_org_line1 => l_pa_request_rec.from_position_org_line1,
931 p_from_position_org_line2 => l_pa_request_rec.from_position_org_line2,
932 p_from_position_org_line3 => l_pa_request_rec.from_position_org_line3,
933 p_from_position_org_line4 => l_pa_request_rec.from_position_org_line4,
934 p_from_position_org_line5 => l_pa_request_rec.from_position_org_line5,
935 p_from_position_org_line6 => l_pa_request_rec.from_position_org_line6,
936 p_from_position_number => l_pa_request_rec.from_position_number,
937 p_from_position_seq_no => l_pa_request_rec.from_position_seq_no,
938 p_from_position_title => l_pa_request_rec.from_position_title,
939 p_from_step_or_rate => l_pa_request_rec.from_step_or_rate,
940 p_from_total_salary => l_pa_request_rec.from_total_salary,
941 p_functional_class => l_pa_request_rec.functional_class,
942 p_notepad => l_pa_request_rec.notepad,
943 p_part_time_hours => l_pa_request_rec.part_time_hours,
944 p_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
945 p_person_id => l_pa_request_rec.person_id,
946 p_position_occupied => l_pa_request_rec.position_occupied,
947 p_proposed_effective_date => l_pa_request_rec.proposed_effective_date,
948 p_requested_by_person_id => l_pa_request_rec.requested_by_person_id,
949 p_requested_by_title => l_pa_request_rec.requested_by_title,
950 p_requested_date => l_pa_request_rec.requested_date,
951 p_requesting_office_remarks_de => l_pa_request_rec.requesting_office_remarks_desc,
952 p_requesting_office_remarks_fl => l_pa_request_rec.requesting_office_remarks_flag,
953 p_request_number => l_pa_request_rec.request_number,
954 p_resign_and_retire_reason_des => l_pa_request_rec.resign_and_retire_reason_desc,
955 p_retirement_plan => l_pa_request_rec.retirement_plan,
956 p_retirement_plan_desc => l_pa_request_rec.retirement_plan_desc,
957 p_second_action_la_code1 => l_pa_request_rec.second_action_la_code1,
958 p_second_action_la_code2 => l_pa_request_rec.second_action_la_code2,
959 p_second_action_la_desc1 => l_pa_request_rec.second_action_la_desc1,
960 p_second_action_la_desc2 => l_pa_request_rec.second_action_la_desc2,
961 p_second_noa_cancel_or_correct => l_pa_request_rec.second_noa_cancel_or_correct,
962 p_second_noa_code => l_pa_request_rec.second_noa_code,
963 p_second_noa_desc => l_pa_request_rec.second_noa_desc,
964 p_second_noa_id => l_pa_request_rec.second_noa_id,
965 p_second_noa_pa_request_id => l_pa_request_rec.second_noa_pa_request_id,
966 p_service_comp_date => l_pa_request_rec.service_comp_date,
967 p_supervisory_status => l_pa_request_rec.supervisory_status,
968 p_tenure => l_pa_request_rec.tenure,
969 p_to_adj_basic_pay => l_pa_request_rec.to_adj_basic_pay,
970 p_to_basic_pay => l_pa_request_rec.to_basic_pay,
971 p_to_grade_id => l_pa_request_rec.to_grade_id,
972 p_to_grade_or_level => l_pa_request_rec.to_grade_or_level,
973 p_to_job_id => l_pa_request_rec.to_job_id,
974 p_to_locality_adj => l_pa_request_rec.to_locality_adj,
975 p_to_occ_code => l_pa_request_rec.to_occ_code,
976 p_to_organization_id => l_pa_request_rec.to_organization_id,
977 p_to_other_pay_amount => l_pa_request_rec.to_other_pay_amount,
978 p_to_au_overtime => l_pa_request_rec.to_au_overtime,
979 p_to_auo_premium_pay_indicator => l_pa_request_rec.to_auo_premium_pay_indicator,
980 p_to_availability_pay => l_pa_request_rec.to_availability_pay,
981 p_to_ap_premium_pay_indicator => l_pa_request_rec.to_ap_premium_pay_indicator,
982 p_to_retention_allowance => l_pa_request_rec.to_retention_allowance,
983 p_to_supervisory_differential => l_pa_request_rec.to_supervisory_differential,
984 p_to_staffing_differential => l_pa_request_rec.to_staffing_differential,
985 p_to_pay_basis => l_pa_request_rec.to_pay_basis,
986 p_to_pay_plan => l_pa_request_rec.to_pay_plan,
987 p_to_pay_table_identifier => l_pa_request_rec.to_pay_table_identifier,
988 p_to_position_id => l_pa_request_rec.to_position_id,
989 p_to_position_org_line1 => l_pa_request_rec.to_position_org_line1,
990 p_to_position_org_line2 => l_pa_request_rec.to_position_org_line2,
991 p_to_position_org_line3 => l_pa_request_rec.to_position_org_line3,
992 p_to_position_org_line4 => l_pa_request_rec.to_position_org_line4,
993 p_to_position_org_line5 => l_pa_request_rec.to_position_org_line5,
994 p_to_position_org_line6 => l_pa_request_rec.to_position_org_line6,
995 p_to_position_number => l_pa_request_rec.to_position_number,
996 p_to_position_seq_no => l_pa_request_rec.to_position_seq_no,
997 p_to_position_title => l_pa_request_rec.to_position_title,
998 p_to_step_or_rate => l_pa_request_rec.to_step_or_rate,
999 p_to_total_salary => l_pa_request_rec.to_total_salary,
1000 p_veterans_preference => l_pa_request_rec.veterans_preference,
1001 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
1002 p_veterans_status => l_pa_request_rec.veterans_status,
1003 p_work_schedule => l_pa_request_rec.work_schedule,
1004 p_work_schedule_desc => l_pa_request_rec.work_schedule_desc,
1005 p_year_degree_attained => l_pa_request_rec.year_degree_attained,
1006 p_first_noa_information1 => l_pa_request_rec.first_noa_information1,
1007 p_first_noa_information2 => l_pa_request_rec.first_noa_information2,
1008 p_first_noa_information3 => l_pa_request_rec.first_noa_information3,
1009 p_first_noa_information4 => l_pa_request_rec.first_noa_information4,
1010 p_first_noa_information5 => l_pa_request_rec.first_noa_information5,
1011 p_second_lac1_information1 => l_pa_request_rec.second_lac1_information1,
1012 p_second_lac1_information2 => l_pa_request_rec.second_lac1_information2,
1013 p_second_lac1_information3 => l_pa_request_rec.second_lac1_information3,
1014 p_second_lac1_information4 => l_pa_request_rec.second_lac1_information4,
1015 p_second_lac1_information5 => l_pa_request_rec.second_lac1_information5,
1016 p_second_lac2_information1 => l_pa_request_rec.second_lac2_information1,
1017 p_second_lac2_information2 => l_pa_request_rec.second_lac2_information2,
1018 p_second_lac2_information3 => l_pa_request_rec.second_lac2_information3,
1019 p_second_lac2_information4 => l_pa_request_rec.second_lac2_information4,
1020 p_second_lac2_information5 => l_pa_request_rec.second_lac2_information5,
1021 p_second_noa_information1 => l_pa_request_rec.second_noa_information1,
1022 p_second_noa_information2 => l_pa_request_rec.second_noa_information2,
1023 p_second_noa_information3 => l_pa_request_rec.second_noa_information3,
1024 p_second_noa_information4 => l_pa_request_rec.second_noa_information4,
1025 p_second_noa_information5 => l_pa_request_rec.second_noa_information5,
1026 p_first_lac1_information1 => l_pa_request_rec.first_lac1_information1,
1027 p_first_lac1_information2 => l_pa_request_rec.first_lac1_information2,
1028 p_first_lac1_information3 => l_pa_request_rec.first_lac1_information3,
1029 p_first_lac1_information4 => l_pa_request_rec.first_lac1_information4,
1030 p_first_lac1_information5 => l_pa_request_rec.first_lac1_information5,
1031 p_first_lac2_information1 => l_pa_request_rec.first_lac2_information1,
1032 p_first_lac2_information2 => l_pa_request_rec.first_lac2_information2,
1033 p_first_lac2_information3 => l_pa_request_rec.first_lac2_information3,
1034 p_first_lac2_information4 => l_pa_request_rec.first_lac2_information4,
1035 p_first_lac2_information5 => l_pa_request_rec.first_lac2_information5,
1036 p_u_action_taken => 'NOT_ROUTED',
1037 p_u_approval_status => l_1_approval_status,
1038 p_to_retention_allow_percentag => l_pa_request_rec.to_retention_allow_percentage,
1039 p_to_supervisory_diff_percenta => l_pa_request_rec.to_supervisory_diff_percentage,
1040 p_to_staffing_diff_percentage => l_pa_request_rec.to_staffing_diff_percentage,
1041 p_award_percentage => l_pa_request_rec.award_percentage,
1042 p_rpa_type => p_rpa_type,
1043 p_mass_action_id => null,
1044 p_u_prh_object_version_number => l_u_prh_object_version_number ,
1045 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
1046 p_i_prh_object_version_number => l_i_prh_object_version_number,
1047 p_award_salary => l_pa_request_rec.award_salary
1048 );
1049 hr_utility.set_location( l_proc,133);
1050 exception
1051 WHEN OTHERS THEN
1052 hr_utility.set_location( l_proc,134);
1053 hr_utility.set_location(l_proc,153);
1054 l_log_text := l_log_text || ' update_sf52 Sql error : '||sqlerrm(sqlcode);
1055 g_log_text := l_log_text || ' update_sf52 Sql error : '||sqlerrm(sqlcode);
1056 raise ma_rpaerror;
1057 end;
1058
1059 hr_utility.set_location('u ovn ' || to_char(l_u_prh_object_version_number),154);
1060 hr_utility.set_location('prh id ' || to_char(l_i_pa_routing_history_id),154);
1061 hr_utility.set_location('prh ovn ' || to_char(l_i_prh_object_version_number),154);
1062 hr_utility.set_location('par upd ' || to_char(l_object_version_number),154);
1063 hr_utility.set_location('par upd ' || to_char(l_pa_request_id),155);
1064 hr_utility.set_location(l_proc,156);
1065 begin
1066 l_log_text := p_noa_action || '-Error After updating the RPA before upd ';
1067 g_log_text := p_noa_action || '-Error After updating the RPA before upd ';
1068 ghr_par_upd.upd(
1069 p_pa_request_id => l_pa_request_id,
1070 p_object_version_number => l_object_version_number,
1071 p_agency_code => l_pa_request_rec.agency_code,
1072 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
1073 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
1074 p_from_office_symbol => l_pa_request_rec.from_office_symbol);
1075 exception
1076 WHEN OTHERS THEN
1077 hr_utility.set_location(l_proc,153);
1078 l_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1079 g_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1080 raise ma_rpaerror;
1081 end;
1082
1083 hr_utility.set_location(l_proc,158);
1084
1085 l_pa_request_rec.pa_request_id := l_pa_request_id;
1086 l_pa_request_rec.object_version_number := l_object_version_number;
1087 update_shadow_row (p_rpa_data => l_pa_request_rec,
1088 p_result => l_result);
1089 if l_result then
1090 hr_utility.set_location('Shadow updated ..'|| l_proc,158);
1091 else
1092 hr_utility.set_location('Shadow not found..'|| l_proc,158);
1093 end if;
1094
1095 hr_utility.set_location(l_proc,160);
1096 IF p_noa_action IN ('472','292') THEN
1097 refresh_ei_details
1098 (
1099 p_noa_action => p_noa_action,
1100 p_effective_date => p_effective_date,
1101 p_person_id => p_person_id,
1102 p_pa_request_id => l_pa_request_rec.pa_request_id );
1103 END IF;
1104 p_pa_request_rec := l_pa_request_rec;
1105 p_log_text := l_log_text;
1106
1107 hr_utility.set_location('Leaving ...' || l_proc,161);
1108
1109 END IF; --IF l_pa_request_id is null
1110 END IF; -- IF l_pa_notification_id is null then
1111 exception when ma_rpaerror then
1112 p_pa_request_rec := l_pa_request_rec;
1113 p_log_text := l_log_text;
1114 raise;
1115 when others then
1116 p_pa_request_rec := l_pa_request_rec;
1117 p_log_text := l_log_text;
1118 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),162);
1119 l_log_text := p_noa_action || '-Error raised in marpa process others ';
1120 l_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1121 raise ma_rpaerror;
1122 end; --Begin 2
1123 p_pa_request_rec := l_pa_request_rec;
1124 p_log_text := l_log_text;
1125 END marpa_process;
1126
1127 Procedure build_rpa
1128 ( p_noa_action in VARCHAR2,
1129 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
1130 p_effective_date in date,
1131 p_nte_date in varchar2,
1132 p_person_id in per_people_f.person_id%TYPE,
1133 p_assignment_id in per_assignments_f.assignment_id%TYPE,
1134 p_position_id in hr_positions_f.position_id%TYPE,
1135 p_location_id in hr_locations.location_id%TYPE,
1136 p_business_group_id in number,
1137 p_errbuf out nocopy varchar2, --\ error log
1138 p_status out nocopy varchar2, --||
1139 p_retcode out nocopy number
1140 )
1141 IS
1142
1143 l_proc varchar2(172) := g_package || 'build_rpa';
1144 l_dummy varchar2(30);
1145 l_dummy_number number;
1146 l_pa_request_rec ghr_pa_requests%rowtype;
1147 l_personnel_officer_name per_people_f.full_name%type;
1148 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
1149 l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
1150 l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
1151 l_multiple_error_flag boolean;
1152 l_scd_leave varchar2(30);
1153 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
1154 l_pa_notification_id ghr_pa_requests.pa_notification_id%TYPE;
1155 l_rpa_type ghr_pa_requests.rpa_type%TYPE;
1156 l_object_version_number ghr_pa_requests.object_version_number%type;
1157 l_log_text varchar2(2000);
1158 l_routing_group_id ghr_pa_requests.routing_group_id%type;
1159 l_groupbox_id ghr_groupboxes.groupbox_id%type;
1160 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
1161 l_prh_object_version_number ghr_pa_routing_history.object_version_number%type;
1162 l_duty_station_location_id hr_locations.location_id%TYPE;
1163 l_u_prh_object_version_number number;
1164 l_i_pa_routing_history_id number;
1165 l_i_prh_object_version_number number;
1166 l_lac1 first_lac1_record;
1167 l_lac2 first_lac2_record;
1168 l_rpa_action varchar2(40);
1169 l_health_plan pay_element_entry_values_f.screen_entry_value%type;
1170 l_first_noa_desc ghr_pa_requests.first_noa_desc%type;
1171 l_savepoint varchar2(1);
1172 groupboxerr exception;
1173 rpaerror exception;
1174 ma_rpaerror exception;
1175 l_pa_request_num_prefix varchar2(50) := 'Mass_Furl_Rtd';
1176 l_comments varchar2(2000);
1177 l_employee_number varchar2(30);
1178
1179 CURSOR c_routing_history is
1180 SELECT prh.pa_routing_history_id,
1181 prh.object_version_number
1182 FROM ghr_pa_routing_history prh
1183 WHERE prh.pa_request_id = l_pa_request_rec.pa_request_id
1184 ORDER by 1 desc;
1185
1186 Begin
1187 hr_utility.set_location('Entering '||l_proc ,5);
1188 p_errbuf := Null;
1189 p_status := Null;
1190 p_retcode := Null;
1191
1192 hr_utility.set_location('Entering ' || l_proc,5);
1193 l_savepoint := 'P';
1194 savepoint build_rpa_save_preview;
1195 hr_utility.set_location('savepoint build_rpa_save_preview ' || l_proc,5);
1196 -- The following would be null while creating rpa.
1197
1198 l_pa_request_rec.additional_info_person_id := NULL;
1199 l_pa_request_rec.additional_info_tel_number := NULL;
1200 l_pa_request_rec.Proposed_Effective_Date := NULL;
1201 l_pa_request_rec.Proposed_Effective_ASAP_flag := 'N';
1202 l_pa_request_rec.requested_by_person_id := NULL;
1203 l_pa_request_rec.requested_by_title := NULL;
1204 l_pa_request_rec.requested_date := NULL;
1205 l_pa_request_rec.authorized_by_person_id := NULL;
1206 l_pa_request_rec.authorized_by_title := NULL;
1207 l_pa_request_rec.concurrence_Date := NULL;
1208 l_pa_request_rec.award_amount := NULL;
1209 l_pa_request_rec.award_uom := NULL;
1210 l_pa_request_rec.award_percentage := NULL;
1211 l_pa_request_rec.mass_action_id := NULL;
1212 l_pa_request_rec.rpa_type := p_rpa_type;
1213 l_pa_request_rec.effective_date := p_effective_date;
1214 l_pa_request_rec.person_id := p_person_id;
1215 l_pa_request_rec.employee_assignment_id := p_assignment_id;
1216 l_pa_request_rec.from_position_id := p_position_id;
1217 l_pa_request_rec.duty_station_location_id := p_location_id;
1218 l_pa_request_rec.first_noa_code := p_noa_action;
1219 l_pa_request_rec.first_noa_information1 := p_nte_date;
1220
1221 hr_utility.set_location(l_proc,15);
1222 ghr_mass_actions_pkg.get_noa_id_desc
1223 (p_noa_code => l_pa_request_rec.first_noa_code,
1224 p_effective_date => l_pa_request_rec.effective_date,
1225 p_noa_id => l_pa_request_rec.first_noa_id,
1226 p_noa_desc => l_pa_request_rec.first_noa_desc
1227 );
1228 l_pa_request_rec.noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
1229 (p_nature_of_action_id => l_pa_request_rec.first_noa_id);
1230
1231 hr_utility.set_location(l_proc,20);
1232 ghr_pa_requests_pkg.get_person_details
1233 (p_person_id => p_person_id
1234 ,p_effective_date => p_effective_date
1235 ,p_national_identifier => l_pa_request_rec.employee_national_identifier
1236 ,p_date_of_birth => l_pa_request_rec.employee_date_of_birth
1237 ,p_last_name => l_pa_request_rec.employee_last_name
1238 ,p_first_name => l_pa_request_rec.employee_first_name
1239 ,p_middle_names => l_pa_request_rec.employee_middle_names
1240 );
1241
1242 If p_person_id is not null then
1243 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
1244 (p_person_id => p_person_id,
1245 p_effective_date => p_effective_date);
1246 End if;
1247
1248 hr_utility.set_location(l_proc,25);
1249 ghr_api.sf52_from_data_elements
1250 (p_person_id => p_person_id
1251 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
1252 ,p_effective_date => p_effective_date
1253 ,p_altered_pa_request_id => null
1254 ,p_noa_id_corrected => null
1255 ,p_pa_history_id => null
1256 ,p_position_title => l_pa_request_rec.from_position_title
1257 ,p_position_number => l_pa_request_rec.from_position_number
1258 ,p_position_seq_no => l_pa_request_rec.from_position_seq_no
1259 ,p_pay_plan => l_pa_request_rec.from_pay_plan
1260 ,p_job_id => l_pa_request_rec.to_job_id
1261 ,p_occ_code => l_pa_request_rec.from_occ_code
1262 ,p_grade_id => l_pa_request_rec.to_grade_id
1263 ,p_grade_or_level => l_pa_request_rec.from_grade_or_level
1264 ,p_step_or_rate => l_pa_request_rec.from_step_or_rate
1265 ,p_total_salary => l_pa_request_rec.from_total_salary
1266 ,p_pay_basis => l_pa_request_rec.from_pay_basis
1267 ,p_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier
1268 ,p_basic_pay => l_pa_request_rec.from_basic_pay
1269 ,p_locality_adj => l_pa_request_rec.from_locality_adj
1270 ,p_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay
1271 ,p_other_pay => l_pa_request_rec.from_other_pay_amount
1272 ,p_au_overtime => l_dummy_number
1273 ,p_auo_premium_pay_indicator => l_dummy
1274 ,p_availability_pay => l_dummy_number
1275 ,p_ap_premium_pay_indicator => l_dummy
1276 ,p_retention_allowance => l_dummy_number
1277 ,p_retention_allow_percentage => l_dummy_number
1278 ,p_supervisory_differential => l_dummy_number
1279 ,p_supervisory_diff_percentage => l_dummy_number
1280 ,p_staffing_differential => l_dummy_number
1281 ,p_staffing_diff_percentage => l_dummy_number
1282 ,p_organization_id => l_pa_request_rec.to_organization_id
1283 ,p_position_org_line1 => l_pa_request_rec.from_position_org_line1
1284 ,p_position_org_line2 => l_pa_request_rec.from_position_org_line2
1285 ,p_position_org_line3 => l_pa_request_rec.from_position_org_line3
1286 ,p_position_org_line4 => l_pa_request_rec.from_position_org_line4
1287 ,p_position_org_line5 => l_pa_request_rec.from_position_org_line5
1288 ,p_position_org_line6 => l_pa_request_rec.from_position_org_line6
1289 ,p_position_id => l_pa_request_rec.from_position_id
1290 ,p_duty_station_location_id => l_duty_station_location_id
1291 ,p_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant
1292 ,p_work_schedule => l_pa_request_rec.work_schedule
1293 );
1294
1295 hr_utility.set_location(l_proc,30);
1296 l_pa_request_rec.duty_station_location_id := l_duty_station_location_id;
1297
1298 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1299 (p_location_id => l_duty_station_location_id
1300 ,p_duty_station_id => l_pa_request_rec.duty_station_id);
1301
1302 ghr_pa_requests_pkg.get_duty_station_details
1303 (p_duty_station_id => l_pa_request_rec.duty_station_id
1304 ,p_effective_date => l_pa_request_rec.effective_date
1305 ,p_duty_station_code => l_pa_request_rec.duty_station_code
1306 ,p_duty_station_desc => l_pa_request_rec.duty_station_desc);
1307
1308 IF p_noa_action IN ('472') THEN
1309 l_pa_request_rec.to_position_title := NULL;
1310 l_pa_request_rec.to_position_number := NULL;
1311 l_pa_request_rec.to_position_seq_no := NULL;
1312 l_pa_request_rec.to_pay_plan := NULL;
1313 l_pa_request_rec.to_occ_code := NULL;
1314 l_pa_request_rec.to_grade_id := NULL;
1315 l_pa_request_rec.to_job_id := NULL;
1316 l_pa_request_rec.to_organization_id := NULL;
1317 l_pa_request_rec.to_grade_or_level := NULL;
1318 l_pa_request_rec.to_pay_basis := NULL;
1319 l_pa_request_rec.to_pay_table_identifier := NULL;
1320 l_pa_request_rec.to_position_id := NULL;
1321 l_pa_request_rec.to_step_or_rate := NULL;
1322 l_pa_request_rec.to_adj_basic_pay := NULL;
1323 l_pa_request_rec.to_basic_pay := NULL;
1324 l_pa_request_rec.to_total_salary := NULL;
1325 l_pa_request_rec.to_other_pay_amount := NULL;
1326 l_pa_request_rec.to_au_overtime := NULL;
1327 l_pa_request_rec.to_auo_premium_pay_indicator := NULL;
1328 l_pa_request_rec.to_availability_pay := NULL;
1329 l_pa_request_rec.to_ap_premium_pay_indicator := NULL;
1330 l_pa_request_rec.to_retention_allowance := NULL;
1331 l_pa_request_rec.to_supervisory_differential := NULL;
1332 l_pa_request_rec.to_staffing_differential := NULL;
1333 l_pa_request_rec.to_locality_adj := NULL;
1334 l_pa_request_rec.to_retention_allow_percentage := NULL;
1335 l_pa_request_rec.to_supervisory_diff_percentage := NULL;
1336 l_pa_request_rec.to_staffing_diff_percentage := NULL;
1337 ELSIF p_noa_action in ('292','772') THEN
1338 l_pa_request_rec.to_position_id := l_pa_request_rec.from_position_id;
1339 l_pa_request_rec.to_position_title := l_pa_request_rec.from_position_title;
1340 l_pa_request_rec.to_position_number := l_pa_request_rec.from_position_number;
1341 l_pa_request_rec.to_position_seq_no := l_pa_request_rec.from_position_seq_no;
1342 l_pa_request_rec.to_position_org_line1 := l_pa_request_rec.from_position_org_line1;
1343 l_pa_request_rec.to_position_org_line2 := l_pa_request_rec.from_position_org_line2;
1344 l_pa_request_rec.to_position_org_line3 := l_pa_request_rec.from_position_org_line3;
1345 l_pa_request_rec.to_position_org_line4 := l_pa_request_rec.from_position_org_line4;
1346 l_pa_request_rec.to_position_org_line5 := l_pa_request_rec.from_position_org_line5;
1347 l_pa_request_rec.to_position_org_line6 := l_pa_request_rec.from_position_org_line6;
1348 l_pa_request_rec.to_step_or_rate := l_pa_request_rec.from_step_or_rate;
1349 l_pa_request_rec.to_adj_basic_pay := l_pa_request_rec.from_adj_basic_pay;
1350 l_pa_request_rec.to_basic_pay := l_pa_request_rec.from_basic_pay ;
1351 l_pa_request_rec.to_total_salary := l_pa_request_rec.from_total_salary;
1352 l_pa_request_rec.to_other_pay_amount := l_pa_request_rec.from_other_pay_amount;
1353 l_pa_request_rec.to_locality_adj := l_pa_request_rec.from_locality_adj;
1354 l_pa_request_rec.to_grade_or_level := l_pa_request_rec.from_grade_or_level;
1355 l_pa_request_rec.to_occ_code := l_pa_request_rec.from_occ_code;
1356 l_pa_request_rec.to_pay_basis := l_pa_request_rec.from_pay_basis;
1357 l_pa_request_rec.to_pay_plan := l_pa_request_rec.from_pay_plan;
1358 END IF;
1359
1360 hr_utility.set_location(l_proc,40);
1361 ghr_pa_requests_pkg.get_sf52_pos_ddf_details
1362 (p_position_id => p_position_id
1363 ,p_date_Effective => l_pa_request_rec.effective_date
1364 ,p_flsa_category => l_pa_request_rec.flsa_category
1365 ,p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status
1366 ,p_work_schedule => l_dummy
1367 ,p_functional_class => l_pa_request_rec.functional_class
1368 ,p_supervisory_status => l_pa_request_rec.supervisory_status
1369 ,p_position_occupied => l_pa_request_rec.position_occupied
1370 ,p_appropriation_code1 => l_pa_request_rec.appropriation_code1
1371 ,p_appropriation_code2 => l_pa_request_rec.appropriation_code2
1372 ,p_personnel_office_id => l_pa_request_rec.personnel_office_id
1373 ,p_office_symbol => l_pa_request_rec.from_office_symbol
1374 ,p_part_time_hours => l_dummy
1375 );
1376
1377 hr_utility.set_location('POI ID ' || l_pa_request_rec.personnel_office_id ,42);
1378 hr_utility.set_location('From Pos ID ' || to_char(l_pa_request_rec.from_position_id) ,42);
1379 hr_utility.set_location('To Pos ID ' || to_char(l_pa_request_rec.to_position_id) ,42);
1380 hr_utility.set_location('Office Symbol ' || l_pa_request_rec.from_office_symbol ,42);
1381
1382 hr_utility.set_location(l_proc,45);
1383 hr_utility.set_location(l_proc,50);
1384 l_pa_request_rec.agency_code := ghr_api.get_position_agency_code_pos
1385 (p_position_id => p_position_id
1386 ,p_business_group_id => p_business_group_id
1387 ,p_effective_date => p_effective_date);
1388
1389 hr_utility.set_location('Agency Code ' || l_pa_request_rec.agency_code ,52);
1390
1391 l_pa_request_rec.employee_dept_or_agency := ghr_pa_requests_pkg.get_lookup_meaning
1392 (800
1393 ,'GHR_US_AGENCY_CODE'
1394 ,l_pa_request_rec.agency_code
1395 );
1396
1397 ghr_pa_requests_pkg.get_SF52_person_ddf_details
1398 (p_person_id => l_pa_request_rec.person_id,
1399 p_date_effective => l_pa_request_rec.effective_date,
1400 p_citizenship => l_pa_request_rec.citizenship,
1401 p_veterans_preference => l_pa_request_rec.veterans_preference,
1402 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
1403 p_veterans_status => l_pa_request_rec.veterans_status,
1404 p_scd_leave => l_scd_leave
1405 );
1406
1407 hr_utility.set_location(l_proc,60);
1408 l_pa_request_rec.service_comp_date := fnd_date.canonical_to_date(l_scd_leave);
1409
1410 -- get education details
1411 hr_utility.set_location(l_proc,70);
1412 ghr_api.return_education_Details
1413 (p_person_id => l_pa_request_rec.person_id,
1414 p_effective_date => l_pa_request_rec.effective_date,
1415 p_education_level => l_pa_request_rec.education_level,
1416 p_academic_discipline => l_pa_request_rec.academic_discipline,
1417 p_year_degree_attained => l_pa_request_rec.year_degree_attained
1418 );
1419
1420 hr_utility.set_location(l_proc,80);
1421 ghr_pa_requests_pkg.get_SF52_asg_ddf_details
1422 (p_assignment_id => l_pa_request_rec.employee_assignment_id
1423 ,p_date_effective => l_pa_request_rec.effective_date
1424 ,p_tenure => l_pa_request_rec.tenure
1425 ,p_annuitant_indicator => l_pa_request_rec.annuitant_indicator
1426 ,p_pay_rate_determinant => l_dummy
1427 ,p_work_schedule => l_pa_request_rec.work_schedule
1428 ,p_part_time_hours => l_pa_request_rec.part_time_hours
1429 );
1430
1431 -- Annuitant_indicator
1432 l_pa_request_rec.annuitant_indicator_desc := ghr_pa_requests_pkg.get_lookup_meaning
1433 (800
1434 ,'GHR_US_ANNUITANT_INDICATOR'
1435 ,l_pa_request_rec.annuitant_indicator
1436 );
1437
1438 --WORK_SCHEDULE
1439 l_pa_request_rec.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning
1440 (800
1441 ,'GHR_US_WORK_SCHEDULE'
1442 ,l_pa_request_rec.work_schedule
1443 );
1444 -- get fegli,retirement_plan
1445
1446 hr_utility.set_location(l_proc,901);
1447 ghr_api.retrieve_element_entry_value
1448 (p_element_name => 'FEGLI'
1449 ,p_input_value_name => 'FEGLI'
1450 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
1451 ,p_effective_date => l_pa_request_rec.effective_date
1452 ,p_value => l_pa_request_rec.fegli
1453 ,p_multiple_error_flag => l_multiple_error_flag
1454 );
1455 l_pa_request_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
1456 (800
1457 ,'GHR_US_FEGLI'
1458 ,l_pa_request_rec.fegli
1459 );
1460
1461 --retirement_plan
1462 hr_utility.set_location(l_proc,100);
1463 ghr_api.retrieve_element_entry_value
1464 (p_element_name => 'Retirement Plan'
1465 ,p_input_value_name => 'Plan'
1466 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
1467 ,p_effective_date => l_pa_request_rec.effective_date
1468 ,p_value => l_pa_request_rec.retirement_plan
1469 ,p_multiple_error_flag => l_multiple_error_flag
1470 );
1471
1472 l_pa_request_rec.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning
1473 (800
1474 ,'GHR_US_RETIREMENT_PLAN'
1475 ,l_pa_request_rec.retirement_plan
1476 );
1477 -- Get FEHB Plan
1478 ghr_api.retrieve_element_entry_value
1479 ( p_element_name => 'Health Benefits'
1480 ,p_input_value_name => 'Health Plan'
1481 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
1482 ,p_effective_date => trunc(l_pa_request_rec.effective_date)
1483 ,p_value => l_health_plan
1484 ,p_multiple_error_flag => l_multiple_error_flag
1485 );
1486 -- Descriptions for the codes passed in
1487 hr_utility.set_location(l_proc,110);
1488 ghr_mass_actions_pkg.get_personnel_officer_name
1489 (p_personnel_office_id => l_pa_request_rec.personnel_office_id,
1490 p_person_full_name => l_personnel_officer_name,
1491 p_approving_off_work_title => l_approving_off_work_title);
1492
1493 l_pa_request_rec.approving_official_full_name := l_personnel_officer_name;
1494 l_pa_request_rec.approving_official_work_title := l_approving_off_work_title;
1495
1496 l_pa_request_rec.request_number := l_pa_request_num_prefix ||to_char(l_pa_request_rec.pa_request_id);
1497
1498 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,110);
1499
1500 l_pa_request_rec.sf50_approving_ofcl_full_name := l_personnel_officer_name;
1501 l_pa_request_rec.sf50_approval_date := sysdate;
1502 l_pa_request_rec.sf50_approving_ofcl_work_title := l_approving_off_work_title;
1503
1504 hr_utility.set_location('Noa_family_code value ' || l_pa_request_rec.noa_family_code,52);
1505 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,52);
1506 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),52);
1507
1508 get_furlough_rtd_lac
1509 (p_noa_action => p_noa_action,
1510 p_pay_plan => l_pa_request_rec.from_pay_plan,
1511 p_effective_date => p_effective_date,
1512 p_first_lac1_record => l_lac1,
1513 p_first_lac2_record => l_lac2
1514 );
1515 l_pa_request_rec.first_action_la_code1 := l_lac1.first_action_la_code1;
1516 l_pa_request_rec.first_action_la_desc1 := l_lac1.first_action_la_desc1;
1517 l_pa_request_rec.first_lac1_information1 := l_lac1.first_lac1_information1;
1518 l_pa_request_rec.first_lac1_information2 := l_lac1.first_lac1_information2;
1519 l_pa_request_rec.first_lac1_information3 := l_lac1.first_lac1_information3;
1520 l_pa_request_rec.first_lac1_information4 := l_lac1.first_lac1_information4;
1521 l_pa_request_rec.first_lac1_information5 := l_lac1.first_lac1_information5;
1522
1523 l_pa_request_rec.first_action_la_code2 := l_lac2.first_action_la_code2;
1524 l_pa_request_rec.first_action_la_desc2 := l_lac2.first_action_la_desc2;
1525 l_pa_request_rec.first_lac2_information1 := l_lac2.first_lac2_information1;
1526 l_pa_request_rec.first_lac2_information2 := l_lac2.first_lac2_information2;
1527 l_pa_request_rec.first_lac2_information3 := l_lac2.first_lac2_information3;
1528 l_pa_request_rec.first_lac2_information4 := l_lac2.first_lac2_information4;
1529 l_pa_request_rec.first_lac2_information5 := l_lac2.first_lac2_information5;
1530 ghr_mass_actions_pkg.replace_insertion_values
1531 (p_desc => l_pa_request_rec.first_noa_desc,
1532 p_information1 => to_char(fnd_date.canonical_to_date(p_nte_date),'DD-MON-RRRR'),
1533 p_desc_out => l_first_noa_desc
1534 );
1535
1536 l_pa_request_rec.first_noa_desc := l_first_noa_desc;
1537
1538 begin
1539 hr_utility.set_location(' marpa_process calling ' || l_proc,52);
1540 marpa_process
1541 (p_noa_action => p_noa_action
1542 ,p_rpa_type => p_rpa_type
1543 ,p_effective_date => p_effective_date
1544 ,p_person_id => p_person_id
1545 ,p_pa_request_rec => l_pa_request_rec
1546 ,p_log_text => l_log_text
1547 );
1548 l_savepoint := 'P';
1549 savepoint build_rpa_save_preview;
1550 hr_utility.set_location('savepoint build_rpa_save_preview - After Preview ' ,162);
1551 exception
1552 when ma_rpaerror then
1553 raise;
1554 when others then
1555 raise ma_rpaerror;
1556 end;
1557
1558 hr_utility.set_location(l_proc,163);
1559
1560 get_pa_request_id_ovn
1561 ( p_pa_request_id => l_pa_request_rec.pa_request_id,
1562 p_effective_date => p_effective_date,
1563 p_person_id => p_person_id,
1564 p_pa_notification_id => l_pa_notification_id,
1565 p_rpa_type => l_rpa_type,
1566 p_object_version_number => l_object_version_number);
1567
1568 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,165);
1569 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),165);
1570
1571 IF l_pa_notification_id is null THEN
1572
1573 if l_1_pa_routing_history_id is null then
1574 l_1_pa_routing_history_id := l_i_pa_routing_history_id;
1575 end if;
1576 if l_1_prh_object_version_number is null then
1577 l_1_prh_object_version_number := l_i_prh_object_version_number;
1578 end if;
1579
1580 begin
1581 hr_utility.set_location('Getting Group Box ' || l_proc,170);
1582
1583 ghr_mass_actions_pkg.get_personnel_off_groupbox
1584 (p_position_id => p_position_id
1585 ,p_effective_date => p_effective_date
1586 ,p_groupbox_id => l_groupbox_id
1587 ,p_routing_group_id => l_routing_group_id );
1588 hr_utility.set_location('Getting Group Box Success ' || l_proc,170);
1589 hr_utility.set_location('Routing Group Id ' || to_char(l_routing_group_id),170);
1590 hr_utility.set_location('Group Box Id' || to_char(l_groupbox_id),170);
1591
1592 exception
1593 WHEN OTHERS THEN
1594 hr_utility.set_location('Getting Group Box Failure ' || l_proc,170);
1595 l_log_text := 'Error in POI groupbox ' ||' Sql error : '||sqlerrm(sqlcode);
1596
1597 raise groupboxerr;
1598 end;
1599
1600 for routing_history_id in c_routing_history loop
1601 l_pa_routing_history_id := routing_history_id.pa_routing_history_id;
1602 l_prh_object_version_number := routing_history_id.object_version_number;
1603 exit;
1604 end loop;
1605
1606 hr_utility.set_location('Updating prh ' || l_proc,175);
1607 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,175);
1608 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),175);
1609 hr_utility.set_location('RHVN ' || to_char(l_prh_object_version_number),176);
1610 hr_utility.set_location('RHID ' || to_char(l_pa_routing_history_id),177);
1611
1612 ghr_prh_upd.upd(
1613 p_pa_routing_history_id => l_pa_routing_history_id,
1614 p_groupbox_id => l_groupbox_id,
1615 p_object_version_number => l_prh_object_version_number);
1616
1617 hr_utility.set_location('Updating par ' || l_proc,180);
1618
1619 -- Call ghr_par_upd.upd here
1620 ghr_par_upd.upd(
1621 p_pa_request_id => l_pa_request_rec.pa_request_id,
1622 p_routing_group_id => l_routing_group_id,
1623 p_object_version_number => l_pa_request_rec.object_version_number);
1624
1625 commit;
1626 hr_utility.set_location('commit(GB) build_rpa_save_preview ' || l_proc,52);
1627 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,52);
1628 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),52);
1629
1630 l_savepoint := 'F';
1631 savepoint build_rpa_save_final;
1632 hr_utility.set_location('savepoint build_rpa_save_final ' || l_proc,52);
1633 hr_utility.set_location(l_proc,52);
1634
1635 hr_utility.set_location('Creating Remarks ' || l_proc,165);
1636 create_remarks
1637 (p_noa_action => p_noa_action
1638 ,p_effective_date => p_effective_date
1639 ,p_person_id => p_person_id
1640 ,p_pa_request_id => l_pa_request_rec.pa_request_id
1641 ,p_work_schedule => l_pa_request_rec.work_schedule
1642 ,p_fegli => l_pa_request_rec.fegli
1643 ,p_fehb_plan => l_health_plan
1644 ,p_prd => l_pa_request_rec.pay_rate_determinant);--Bug# 12334118
1645 hr_utility.set_location('Creating Remarks Over ' || l_proc,165);
1646
1647
1648 if p_effective_date > sysdate then
1649 l_rpa_action := 'FUTURE_ACTION';
1650 else
1651 l_rpa_action := 'UPDATE_HR';
1652 end if;
1653
1654 hr_utility.set_location('Action before last ghr_par_upd.upd ' || l_rpa_action || ' ' || l_proc,175);
1655
1656 ghr_par_upd.upd
1657 (p_pa_request_id => l_pa_request_rec.pa_request_id,
1658 p_object_version_number => l_pa_request_rec.object_version_number,
1659 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
1660 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
1661 p_to_office_symbol => l_pa_request_rec.to_office_symbol,
1662 p_sf50_approving_ofcl_full_nam => l_personnel_officer_name,
1663 p_sf50_approval_date => sysdate ,
1664 p_sf50_approving_ofcl_work_tit => l_approving_off_work_title
1665 );
1666 hr_utility.set_location('Action after last ghr_par_upd.upd ' || l_rpa_action || ' ' || l_proc,175);
1667
1668 hr_utility.set_location('Action before ' || l_rpa_action || ' ' || l_proc,185);
1669 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,185);
1670 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),185);
1671
1672 begin
1673 ghr_sf52_api.update_sf52
1674 (
1675 p_pa_request_id => l_pa_request_rec.pa_request_id,
1676 p_par_object_version_number => l_pa_request_rec.object_version_number,
1677 p_effective_date => l_pa_request_rec.effective_date,
1678 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
1679 p_noa_family_code => l_pa_request_rec.noa_family_code,
1680 p_routing_group_id => l_routing_group_id,
1681 p_u_action_taken => l_rpa_action,
1682 p_u_prh_object_version_number => l_u_prh_object_version_number ,
1683 p_first_noa_id => l_pa_request_rec.first_noa_id,
1684 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
1685 p_i_prh_object_version_number => l_i_prh_object_version_number);
1686 exception
1687 WHEN OTHERS THEN
1688 hr_utility.set_location(sqlerrm(sqlcode),1);
1689 hr_utility.set_location('Update sf52 Failure ' || l_proc,190);
1690 l_log_text := 'Error in Update sf52 ' ||' Sql error : '||sqlerrm(sqlcode);
1691 raise rpaerror;
1692 end;
1693
1694 hr_utility.set_location('Action after ' || l_rpa_action || ' ' || l_proc,200);
1695 p_status := 'SUCCESS';
1696 p_retcode := 0;
1697 l_log_text := 'Last Name: '|| l_pa_request_rec.employee_last_name ||
1698 ' Emp No: '|| l_employee_number ||
1699 ' Mass Furlough and RTD: '|| to_char(p_noa_action)||
1700 ' SF52 Successfully completed';
1701 p_errbuf := l_log_text;
1702
1703 ghr_wgi_pkg.create_ghr_errorlog(
1704 p_program_name => g_log_name,
1705 p_message_name => substr('Mass Furlo or RTD Success',1,30),
1706 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
1707 p_log_date => sysdate);
1708
1709 ELSE --IF l_pa_notification_id is null
1710
1711 hr_utility.set_location('Already Processed ' || l_proc,200);
1712 p_status := 'PROCESSED';
1713 p_retcode := 0;
1714 END IF;
1715
1716 Exception
1717 When groupboxerr then
1718 hr_utility.set_location('Entering Exception groupboxerr ' || l_proc , 1);
1719 rollback to build_rpa_save_preview;
1720 hr_utility.set_location('rollback grp build_rpa_save_preview ' || l_proc,1);
1721 IF l_log_text is NULL THEN
1722 l_log_text := 'Error in POI groupbox ';
1723 END IF;
1724
1725 p_retcode := 1;
1726 p_status := 'GROUPBOX';
1727 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
1728
1729 hr_utility.set_location('Exception groupboxerr in build_rpa before creat log ',2);
1730 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
1731 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
1732 '; Emp No: '||l_employee_number||
1733 ') ' || l_log_text,1,2000);
1734
1735 ghr_wgi_pkg.create_ghr_errorlog(
1736 p_program_name => g_log_name,
1737 p_message_name => substr('Mass Furlo or RTD' || '-' || 'GB Err',1,30),
1738 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
1739 p_log_date => sysdate);
1740
1741 hr_utility.set_location('Leaving build_rpa Exception groupboxerr ',2);
1742 commit;
1743
1744 When rpaerror then
1745 if l_savepoint = 'F' THEN
1746 rollback to build_rpa_save_final;
1747 hr_utility.set_location('rollback rpa build_rpa_save_final ' || l_proc,1);
1748 else
1749 rollback to build_rpa_save_preview;
1750 hr_utility.set_location('rollback rpa build_rpa_save_preview ' || l_proc,1);
1751 end if;
1752
1753 hr_utility.set_location('Error occured Update Sf52 Exception rpaerror ' || l_proc , 1);
1754 IF l_log_text is NULL THEN
1755 l_log_text := 'Update sf52 Failure ';
1756 END IF;
1757
1758 p_retcode := 1;
1759 p_status := 'FAILURE';
1760 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
1761
1762 hr_utility.set_location('Exception rpaerror before creating entry in log file',2);
1763 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
1764 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
1765 '; Emp No: '||l_employee_number||
1766 ') ' || l_log_text ,1,2000);
1767 ghr_wgi_pkg.create_ghr_errorlog(
1768 p_program_name => g_log_name,
1769 p_message_name => substr('Mass Furlo or RTD' || '-' || 'RPA Err',1,30),
1770 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
1771 p_log_date => sysdate);
1772
1773 hr_utility.set_location('Leaving build_rpa Exception rpaerror ',2);
1774 commit;
1775 When ma_rpaerror then
1776 if l_savepoint = 'F' THEN
1777 rollback to build_rpa_save_final;
1778 hr_utility.set_location('rollback marpa build_rpa_save_final ' || l_proc,1);
1779 else
1780 rollback to build_rpa_save_preview;
1781 hr_utility.set_location('rollback marpa build_rpa_save_preview ' || l_proc,1);
1782 end if;
1783
1784 hr_utility.set_location('Error occured in ' || l_proc , 1);
1785 IF g_log_text is NOT NULL then
1786 l_log_text := g_log_text;
1787 END IF;
1788 IF l_log_text is NULL THEN
1789 l_log_text := 'Error while creating / Updating the PA Request Rec.';
1790 END IF;
1791
1792 p_retcode := 1;
1793 p_status := 'FAILURE';
1794 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
1795
1796 hr_utility.set_location('Exception ma_rpaerror before creating entry in log file',2);
1797 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
1798 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
1799 '; Emp No: '||l_employee_number||
1800 ') ' || l_log_text,1,2000);
1801 ghr_wgi_pkg.create_ghr_errorlog(
1802 p_program_name => g_log_name,
1803 p_message_name => substr('Mass Furlo or RTD' || '-' || 'RPA MA',1,30) ,
1804 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
1805 p_log_date => sysdate);
1806
1807 hr_utility.set_location('Leaving build_rpa Exception ma_rpaerror ',2);
1808
1809 commit;
1810 When others then
1811
1812 if l_savepoint = 'F' THEN
1813 rollback to build_rpa_save_final;
1814 hr_utility.set_location('rollback others build_rpa_save_final ' || l_proc,1);
1815 else
1816 rollback to build_rpa_save_preview;
1817 hr_utility.set_location('rollback others build_rpa_save_preview ' || l_proc,1);
1818 end if;
1819
1820 hr_utility.set_location('Error occured in ' || l_proc , 1);
1821 IF l_log_text is NULL THEN
1822 l_log_text := 'Error while creating / Updating the PA Request Rec.';
1823 END IF;
1824
1825 p_retcode := 1;
1826 p_status := 'FAILURE';
1827 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
1828
1829 hr_utility.set_location('Exception build_rpa others2 before creating entry in log file',2);
1830 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
1831 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
1832 '; Emp No: '||l_employee_number||
1833 ') ' || l_log_text,1,2000);
1834 ghr_wgi_pkg.create_ghr_errorlog(
1835 p_program_name => g_log_name,
1836 p_message_name => substr('Mass Furlo or RTD' || '-' || 'RPA Oth',1,30) ,
1837 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
1838 p_log_date => sysdate);
1839
1840 hr_utility.set_location('Leaving build_rpa Exception Others ',2);
1841
1842 commit;
1843
1844 end build_rpa;
1845
1846 PROCEDURE main_concurrent_program
1847 ( p_errbuf OUT NOCOPY VARCHAR2,
1848 p_retcode OUT NOCOPY NUMBER,
1849 p_effective_date in varchar2,
1850 p_noa_action in ghr_nature_of_actions.code%type,
1851 p_nte_date in varchar2,
1852 p_business_group_id in Number,
1853 p_agency in varchar2
1854 ) is
1855 furlo_main_error exception;
1856 furlo_param_error exception;
1857 l_errbuf varchar2(2000);
1858 l_status varchar2(10);
1859 l_log_text varchar2(2000);
1860 l_retcode number;
1861 l_maxcheck number;
1862 l_agency varchar2(50);
1863 l_noa_action ghr_nature_of_actions.code%type;
1864 l_proc varchar2(172) := g_package || 'main_concurrent_program';
1865 l_effective_date date;
1866 l_rpa_type ghr_pa_requests.rpa_type%type;
1867 l_prog_name varchar2(30);
1868 l_asg_extra_info_rec per_assignment_extra_info%rowtype;
1869 l_elig_flag BOOLEAN := FALSE;
1870 l_emergency_ess_emp VARCHAR2(20);
1871 l_full_name VARCHAR2(380);
1872 l_employee_number per_people_f.employee_number%type;
1873
1874 CURSOR cur_people (l_eff_date date) IS
1875 SELECT ppf.person_id PERSON_ID,
1876 ppf.first_name FIRST_NAME,
1877 ppf.last_name LAST_NAME,
1878 ppf.middle_names MIDDLE_NAMES,
1879 ppf.full_name FULL_NAME,
1880 ppf.date_of_birth DATE_OF_BIRTH,
1881 ppf.national_identifier NATIONAL_IDENTIFIER,
1882 ppf.employee_number EMPLOYEE_NUMBER,
1883 paf.position_id POSITION_ID,
1884 paf.assignment_id ASSIGNMENT_ID,
1885 paf.location_id LOCATION_ID,
1886 paf.organization_id ORGANIZATION_ID,
1887 paf.business_group_id BUSINESS_GROUP_ID,
1888 paf.assignment_status_type_id ASSIGNMENT_STATUS_TYPE_ID
1889 FROM per_assignments_f paf,
1890 per_people_f ppf,
1891 per_person_types ppt,
1892 hr_organization_units hou
1893 WHERE ppf.person_id = paf.person_id
1894 AND l_eff_date between ppf.effective_start_date and ppf.effective_end_date
1895 AND l_eff_date between paf.effective_start_date and paf.effective_end_date
1896 AND paf.primary_flag = 'Y'
1897 AND paf.assignment_type <> 'B'
1898 AND ppf.person_type_id = ppt.person_type_id
1899 AND ppt.system_person_type IN ('EMP','EMP_APL')
1900 AND paf.organization_id = hou.organization_id
1901 AND paf.position_id is not null
1902 and paf.business_group_id = nvl(p_business_group_id,paf.business_group_id)
1903 AND substr(ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id,l_eff_date),1,2) = nvl(l_agency,substr(ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id,l_eff_date),1,2))
1904 ORDER BY ppf.person_id;
1905 --
1906 -- Check assignment_status_type
1907 --
1908
1909 CURSOR cur_472_ast (asg_status_type_id number) IS
1910 SELECT user_status from per_assignment_status_types
1911 WHERE assignment_status_type_id = asg_status_type_id
1912 AND upper(user_status) not in (
1913 'TERMINATE ASSIGNMENT', /* 3 */
1914 'ACTIVE APPLICATION', /* 4 */
1915 'OFFER', /* 5 */
1916 'ACCEPTED', /* 6 */
1917 'TERMINATE APPLICATION', /* 7 */
1918 'END', /* 8 */
1919 'FURLOUGH', /* 28 */
1920 'FURLOUGH NTE', /* 28 */
1921 'TERMINATE APPOINTMENT', /* 126 */
1922 'SEPARATED'); /* 132 */
1923
1924 CURSOR cur_292_772_people (l_eff_date date) IS
1925 SELECT ppf.person_id PERSON_ID,
1926 ppf.first_name FIRST_NAME,
1927 ppf.last_name LAST_NAME,
1928 ppf.middle_names MIDDLE_NAMES,
1929 ppf.full_name FULL_NAME,
1930 ppf.date_of_birth DATE_OF_BIRTH,
1931 ppf.national_identifier NATIONAL_IDENTIFIER,
1932 ppf.employee_number EMPLOYEE_NUMBER,
1933 paf.position_id POSITION_ID,
1934 paf.assignment_id ASSIGNMENT_ID,
1935 paf.location_id LOCATION_ID,
1936 paf.organization_id ORGANIZATION_ID,
1937 paf.business_group_id BUSINESS_GROUP_ID,
1938 paf.assignment_status_type_id ASSIGNMENT_STATUS_TYPE_ID
1939 FROM per_assignments_f paf,
1940 per_people_f ppf,
1941 per_person_types ppt,
1942 hr_organization_units hou,
1943 per_assignment_status_types past
1944 WHERE ppf.person_id = paf.person_id
1945 AND l_eff_date between ppf.effective_start_date and ppf.effective_end_date
1946 AND l_eff_date between paf.effective_start_date and paf.effective_end_date
1947 AND paf.primary_flag = 'Y'
1948 AND paf.assignment_type <> 'B'
1949 AND ppf.person_type_id = ppt.person_type_id
1950 AND ppt.system_person_type IN ('EMP','EMP_APL')
1951 AND paf.assignment_status_type_id = past.assignment_status_type_id
1952 AND upper(past.user_status) in ('FURLOUGH','FURLOUGH NTE')
1953 AND paf.organization_id = hou.organization_id
1954 AND paf.position_id is not null
1955 and paf.business_group_id = nvl(p_business_group_id,paf.business_group_id)
1956 AND substr(ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id,l_eff_date),1,2) = nvl(l_agency,substr(ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id,l_eff_date),1,2))
1957 ORDER BY ppf.person_id;
1958
1959 CURSOR get_rpa_type (p_person_id number,p_assignment_id number) IS
1960 SELECT par.rpa_type,par.effective_date from ghr_pa_requests par
1961 where par.person_id = p_person_id
1962 AND par.employee_assignment_id = p_assignment_id
1963 AND par.effective_date < l_effective_date
1964 AND par.rpa_type='MFL'
1965 AND par.first_noa_code IN ('472')
1966 AND par.pa_notification_id IS NOT NULL
1967 AND not exists (select 1
1968 from ghr_pa_requests b
1969 where person_id = p_person_id
1970 and altered_pa_request_id = par.pa_request_id
1971 and first_noa_code = '001'
1972 and second_noa_code = par.first_noa_code)
1973 ORDER BY par.pa_request_id desc;
1974
1975 BEGIN
1976 hr_utility.set_location('Entering ' || l_proc,5);
1977 l_errbuf := NULL;
1978 l_retcode := NULL;
1979 l_status := NULL;
1980 l_maxcheck := NULL;
1981 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1982 l_agency := p_agency;
1983 l_noa_action := p_noa_action;
1984 ghr_session.set_fnd_session_date(l_effective_date);
1985 l_prog_name := fnd_profile.VALUE ('CONC_REQUEST_ID');
1986 l_prog_name := 'GHR_CON_PGM-' || l_prog_name;
1987 g_log_name := l_prog_name;
1988 hr_utility.set_location( l_proc,20);
1989 BEGIN
1990 hr_utility.set_location( 'Checking mandatory fields '|| p_nte_date,20);
1991 IF l_noa_action IN ('772','472') AND p_nte_date IS NULL THEN
1992 hr_utility.set_location('Mandatory NTE Date not passed ',20);
1993 l_log_text := 'NTE Date is Mandatory for NOA 472 and 772 ';
1994 raise furlo_param_error;
1995 END IF;
1996 END;
1997 IF l_noa_action ='472' THEN
1998 l_rpa_type := 'MFL';
1999 FOR per IN cur_people (l_effective_date)
2000 LOOP
2001 l_errbuf := NULL;
2002 FOR ast IN cur_472_ast (per.assignment_status_type_id)
2003 LOOP
2004 hr_utility.set_location(' before SSN: '||per.national_identifier,2001);
2005 l_elig_flag := FALSE;
2006 hr_utility.set_location('SSN: '||per.national_identifier,2000);
2007 hr_utility.set_location('Position Id: '||per.position_id,2000);
2008 hr_utility.set_location('BG Id: '||per.business_group_id,2000);
2009 l_full_name := per.last_name||','|| per.first_name||' '||per.middle_names;
2010 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
2011 (p_person_id => per.person_id,
2012 p_effective_date => l_effective_date);
2013
2014 ghr_history_fetch.fetch_asgei
2015 (p_assignment_id => per.ASSIGNMENT_ID,
2016 p_information_type => 'GHR_US_ASG_NON_SF52',
2017 p_date_effective => l_effective_date,
2018 p_asg_ei_data => l_asg_extra_info_rec
2019 );
2020 l_emergency_ess_emp := l_asg_extra_info_rec.aei_information5;
2021
2022 IF NVL(l_emergency_ess_emp,'XX') NOT IN('6','7') THEN
2023 l_elig_flag := TRUE;
2024 ELSE
2025 l_elig_flag := FALSE;
2026 END IF;
2027 IF l_elig_flag THEN
2028 BEGIN
2029 hr_utility.set_location( 'Calling build_rpa '|| l_rpa_type,20);
2030 build_rpa
2031 ( p_noa_action => l_noa_action,
2032 p_rpa_type => l_rpa_type,
2033 p_effective_date => l_effective_date,
2034 p_nte_date => p_nte_date,
2035 p_person_id => per.person_id,
2036 p_assignment_id => per.assignment_id,
2037 p_position_id => per.position_id,
2038 p_location_id => per.location_id,
2039 p_business_group_id => per.business_group_id,
2040 p_errbuf => l_errbuf,
2041 p_status => l_status,
2042 p_retcode => l_retcode
2043 );
2044 EXCEPTION
2045 WHEN others THEN
2046 hr_utility.set_location('Error in build_rpa call '|| 'Err is '||sqlerrm(sqlcode),20);
2047 l_log_text := 'Error in build_rpa for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
2048 || '; SQL Error: '|| sqlerrm(sqlcode);
2049 raise furlo_main_error;
2050 END;
2051 END IF;
2052 END LOOP;
2053 END LOOP;
2054 ELSIF l_noa_action IN ('772','292') THEN
2055 hr_utility.set_location( 'Calling 772 or 292 '|| l_rpa_type,20);
2056 IF l_noa_action IN ('772') THEN
2057 l_rpa_type := 'MFL';
2058 ELSIF l_noa_action IN ('292') THEN
2059 l_rpa_type := 'MRTD';
2060 END IF;
2061 FOR per IN cur_292_772_people(l_effective_date)
2062 LOOP
2063 l_errbuf := NULL;
2064 l_elig_flag := FALSE;
2065 g_furlo_effective_date := NULL;
2066 hr_utility.set_location('SSN: '||per.national_identifier,2000);
2067 hr_utility.set_location('person Id: '|| per.person_id,2000);
2068 hr_utility.set_location('assignment Id: '||per.assignment_id,2000);
2069 BEGIN
2070 FOR l_get_rpa_type IN get_rpa_type(per.person_id,per.assignment_id) LOOP
2071 l_elig_flag := TRUE;
2072 hr_utility.set_location('292 eff date: '||l_get_rpa_type.effective_date,2000);
2073 g_furlo_effective_date := l_get_rpa_type.effective_date;
2074 EXIT;
2075 END LOOP;
2076 EXCEPTION
2077 when others THEN
2078 hr_utility.set_location('292 eff date: exception ',2000);
2079 END;
2080 IF l_elig_flag THEN
2081 BEGIN
2082 hr_utility.set_location( 'Calling build_rpa '|| l_rpa_type,20);
2083 build_rpa
2084 ( p_noa_action => l_noa_action,
2085 p_rpa_type => l_rpa_type,
2086 p_effective_date => l_effective_date,
2087 p_nte_date => p_nte_date,
2088 p_person_id => per.person_id,
2089 p_assignment_id => per.assignment_id,
2090 p_position_id => per.position_id,
2091 p_location_id => per.location_id,
2092 p_business_group_id => per.business_group_id,
2093 p_errbuf => l_errbuf,
2094 p_status => l_status,
2095 p_retcode => l_retcode
2096 );
2097 EXCEPTION
2098 WHEN others THEN
2099 hr_utility.set_location('Error in build_rpa call '|| 'Err is '||sqlerrm(sqlcode),20);
2100 l_log_text := 'Error in build_rpa for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
2101 || '; SQL Error: '|| sqlerrm(sqlcode);
2102 raise furlo_main_error;
2103 END;
2104 END IF;
2105 END LOOP;
2106 END IF;
2107 p_errbuf := l_errbuf;
2108 p_retcode := l_retcode;
2109 EXCEPTION
2110 WHEN furlo_param_error THEN
2111 l_log_text := substr(l_log_text,1,2000);
2112 hr_utility.set_location(' Exception furlo_param_error creating entry in log file',10);
2113 p_retcode := 2;
2114 p_errbuf := substr(l_log_text ,1,2000);
2115 When furlo_main_error then
2116 hr_utility.set_location('Error occured in ' || l_proc , 1);
2117 IF l_log_text is NULL THEN
2118 l_log_text := 'Error while create / Update the PA Request Rec. ';
2119 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
2120 END IF;
2121
2122 l_log_text := substr(l_log_text,1,2000);
2123
2124 hr_utility.set_location(' Exception furlo_main_error creating entry in log file',10);
2125 ghr_mto_int.log_message( p_procedure => 'While Creating Furlo RPA'
2126 ,p_message => l_log_text);
2127
2128 hr_utility.set_location('created entry in log file',20);
2129 WHEN others THEN
2130
2131 IF l_log_text is NULL THEN
2132 l_log_text := 'Error while create / Update the PA Request Rec. ';
2133 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
2134 END IF;
2135 l_log_text := substr(l_log_text,1,2000);
2136 hr_utility.set_location('Exception main_concurrent_program others create entry in log file',10);
2137
2138 ghr_mto_int.log_message(p_procedure => 'While Creating Furlo RPA'
2139 ,p_message => l_log_text);
2140 hr_utility.set_location('created entry in log file',20);
2141 END main_concurrent_program;
2142
2143 END GHR_MASS_CON_PROGRAM;