[Home] [Help]
PACKAGE BODY: APPS.GHR_MTI_APP
Source
1 PACKAGE BODY GHR_MTI_APP AS
2 /* $Header: ghmtiapp.pkb 120.3 2010/04/09 05:22:31 vmididho ship $ */
3
4 g_package varchar2(32) := ' GHR_MTI_APP';
5 l_log_text varchar2(2000) := null;
6 l_mass_errbuf varchar2(2000) := null;
7
8 procedure populate_and_create_52(p_errbuf out nocopy varchar2,
9 p_retcode out nocopy number,
10 p_business_group_id in number,
11 p_mtin_name in varchar2,
12 p_mtin_id in number,
13 p_effective_date in date) is
14
15 cursor cur_people (l_effective_date date,l_person_id number) is
16 select ppf.first_name FIRST_NAME,
17 ppf.last_name LAST_NAME,
18 ppf.middle_names MIDDLE_NAMES,
19 ppf.full_name FULL_NAME,
20 ppf.date_of_birth DATE_OF_BIRTH,
21 ppf.national_identifier NATIONAL_IDENTIFIER,
22 --Bug# 9329643
23 ppf.employee_number EMPLOYEE_NUMBER
24 from per_people_f ppf
25 where ppf.person_id = l_person_id
26 and l_effective_date between ppf.effective_start_date
27 and nvl(ppf.effective_end_date,l_effective_date+1);
28
29 CURSOR ghr_mt_int (p_national_identifier varchar2,p_mt_name varchar2) is
30 SELECT RETIREMENT_PLAN,
31 RETENTION_ALLOWANCE,
32 TENURE,
33 ANNUITANT_INDICATOR,
34 FEGLI,
35 TO_POSITION_ID,
36 FROM_POSITION_TITLE,
37 FROM_POSITION_SEQ_NUM,
38 pay_rate_determinant,
39 from_step_or_rate,
40 APPOINTMENT_TYPE,
41 date_arrived_personnel_office,
42 wgi_date_due,
43 frozen_service,
44 -- Bug#2412656 Added FERS_Coverage
45 fers_coverage,
46 non_disclosure_agmt_status,
47 part_time_indicator,
48 position_working_title,
49 previous_retirement_coverage,
50 MT_STATUS,
51
52 FLSA_CATEGORY,
53 BARGAINING_UNIT_STATUS,
54 functional_class,
55 supervisory_status,
56 position_occupied,
57 appropriation_code1,
58 appropriation_code2,
59
60 TYPE_OF_EMPLOYMENT,
61 RACE_OR_NATIONAL_ORIGIN,
62 AGENCY_CODE_TRANSFER_FROM,
63 ORIG_APPOINTMENT_AUTH_CODE_1,
64 ORIG_APPT_AUTH_CODE_1_DESC,--Bug# 8724192
65 ORIG_APPOINTMENT_AUTH_CODE_2,
66 ORIG_APPT_AUTH_CODE_2_DESC,--Bug# 8724192
67 HANDICAP_CODE,
68
69 CITIZENSHIP,
70 VETERANS_PREFERENCE,
71 VETERANS_PREFERENCE_FOR_RIF,
72 VETERANS_STATUS,
73
74 WORK_SCHEDULE,
75 CREDITABLE_MILITARY_SERVICE,
76 -- # BUG # 711533
77 SERVICE_COMP_DATE,
78 EDUCATIONAL_LEVEL,
79 ACADEMIC_DISCIPLINE,
80 YEAR_DEGREE_ATTAINED,
81 -- Bug # 712305
82 PART_TIME_HOURS,
83 -- Bug 4093771
84 to_basic_salary_rate,
85 TO_ADJUSTED_BASIC_PAY,
86 TO_TOTAL_SALARY,
87 ASSIGNMENT_NTE_START_DATE,--Bug# 8724192
88 ASSIGNMENT_NTE--Bug# 8724192
89 FROM ghr_mt_interface_v
90 WHERE national_identifier = p_national_identifier
91 AND mt_name = p_mt_name;
92
93 CURSOR ghr_mt_int_count (p_mt_name varchar2) is
94 select count(*) COUNT
95 from ghr_mt_interface_v
96 where mt_name = p_mt_name
97 and mt_status = 'E';
98
99 CURSOR PER_EXTRA_CUR (p_mt_id number) is
100 SELECT PERSON_ID
101 , PERSON_EXTRA_INFO_ID
102 , OBJECT_VERSION_NUMBER
103 FROM PER_PEOPLE_EXTRA_INFO
104 WHERE PEI_INFORMATION6 = 'Y'
105 AND PEI_INFORMATION8 = to_char(p_mt_id)
106 AND INFORMATION_TYPE = 'GHR_US_PER_MASS_ACTIONS';
107
108 CURSOR PA_REQ_EXT_INFO_CUR (p_pa_request_id number) is
109 SELECT PA_REQUEST_EXTRA_INFO_ID,
110 OBJECT_VERSION_NUMBER
111 FROM GHR_PA_REQUEST_EXTRA_INFO
112 WHERE INFORMATION_TYPE = 'GHR_US_PAR_APPT_TRANSFER'
113 and pa_request_id = p_pa_request_id;
114
115 --Begin Bug# 8724192
116 CURSOR PA_REQ_NTE_EI_CUR (p_pa_request_id number) is
117 SELECT PA_REQUEST_EXTRA_INFO_ID,
118 OBJECT_VERSION_NUMBER
119 FROM GHR_PA_REQUEST_EXTRA_INFO
120 WHERE INFORMATION_TYPE = 'GHR_US_MASS_TRNSFR_NTE_DATES'
121 and pa_request_id = p_pa_request_id;
122
123 CURSOR PA_REQ_APP_AUTH_EI_CUR (p_pa_request_id number) is
124 SELECT PA_REQUEST_EXTRA_INFO_ID,
125 OBJECT_VERSION_NUMBER
126 FROM GHR_PA_REQUEST_EXTRA_INFO
127 WHERE INFORMATION_TYPE = 'GHR_US_TRANS_CURN_APP_AUTH'
128 and pa_request_id = p_pa_request_id;
129
130 --End Bug# 8724192
131
132 CURSOR GHR_MTI_CUR (p_mt_id number) is
133 SELECT PA_REQUEST_ID
134 FROM GHR_MASS_TRANSFERS
135 WHERE MASS_TRANSFER_ID = p_mt_id
136 and TRANSFER_TYPE = 'IN';
137
138 CURSOR c_asg_by_per_id_not_prim (p_per_id number, p_eff_date date) IS
139 SELECT asg.assignment_id
140 FROM per_assignments_f asg
141 WHERE asg.person_id = p_per_id
142 AND asg.assignment_type <> 'B'
143 AND trunc(p_eff_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
144 ORDER BY asg.assignment_id;
145
146 l_pa_request_id number;
147 l_lac_sf52_rec ghr_pa_requests%rowtype;
148
149 l_person_id number;
150 l_employee_assignment_id number;
151 l_position_id number;
152 l_RETIREMENT_PLAN VARCHAR2(30);
153 l_RETENTION_ALLOWANCE NUMBER;
154 l_TENURE VARCHAR2(30);
155 l_ANNUITANT_INDICATOR VARCHAR2(30);
156 l_FEGLI VARCHAR2(30);
157
158 l_flsa_category varchar2(100);
159 l_bargaining_unit_status varchar2(100);
160 l_functional_class varchar2(100);
161 l_supervisory_status varchar2(100);
162 l_position_occupied varchar2(100);
163 l_appropriation_code1 varchar2(100);
164 l_appropriation_code2 varchar2(100);
165
166 l_last_name per_people_f.last_name%type;
167 l_first_name per_people_f.first_name%type;
168 l_full_name per_people_f.full_name%type;
169 l_middle_names per_people_f.middle_names%type;
170 l_date_of_birth varchar2(20);
171 l_national_identifier per_people_f.NATIONAL_IDENTIFIER%type;
172
173 l_grade_id number;
174 l_job_id number;
175 l_organization_id number;
176
177 l_position_title varchar2(300);
178 l_position_number varchar2(20);
179 l_position_seq_no number(15);
180
181 l_pay_rate_determinant varchar2(35);
182 l_work_schedule varchar2(35);
183
184 l_part_time_hours varchar2(35);
185 l_pay_table_id number;
186 l_pay_plan varchar2(30);
187 l_occ_code varchar2(30);
188 l_grade_or_level varchar2(30);
189 l_step_or_rate varchar2(30);
190 l_pay_basis varchar2(30);
191 l_location_id number;
192 l_duty_station_id number;
193 l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
194 l_duty_station_code ghr_pa_requests.duty_station_code%type;
195 l_mt_status varchar2(2);
196
197 l_org1 varchar2(40);
198 l_org2 varchar2(40);
199 l_org3 varchar2(40);
200 l_org4 varchar2(40);
201 l_org5 varchar2(40);
202 l_org6 varchar2(40);
203 l_dummy varchar2(35);
204
205 l_personnel_office_id varchar2(300);
206 l_org_structure_id varchar2(300);
207
208 l_citizenship varchar2(32);
209 l_veterans_preference varchar2(32);
210 l_veterans_preference_for_RIF varchar2(32);
211 l_veterans_status varchar2(32);
212 l_serv_comp_date varchar2(32);
213
214
215 l_agency_code_transfer_from varchar2(32);
216 l_handicap_code varchar2(32);
217 l_orig_appointment_auth_code1 varchar2(32);
218 l_orig_appointment_auth_code2 varchar2(32);
219 l_race_or_national_origin varchar2(32);
220 l_type_of_employment varchar2(32);
221
222 l_creditable_military_service varchar2(32);
223
224 l_appointment_type varchar2(32);
225 l_previous_retirement_coverage varchar2(32);
226 -- Bug#2412656 Added l_fers_coverage
227 l_fers_coverage varchar2(32);
228 l_frozen_service varchar2(32);
229 l_date_arr_pers_office varchar2(20);
230 l_date_wgi_due varchar2(20);
231 l_non_disc_agmt_status varchar2(150);
232 l_position_working_title varchar2(150);
233 l_part_time_indicator varchar2(150);
234
235 l_sf52_rec ghr_pa_requests%rowtype;
236 l_errbuf varchar2(2000);
237
238 l_retcode number;
239
240 l_pos_ei_data per_position_extra_info%rowtype;
241 l_pos_grp1_rec per_position_extra_info%rowtype;
242 l_pos_grp2_rec per_position_extra_info%rowtype;
243 l_per_ei1_data per_people_extra_info%rowtype;
244 l_per_ei2_data per_people_extra_info%rowtype;
245 l_per_ei3_data per_people_extra_info%rowtype;
246 l_per_ei4_data per_people_extra_info%rowtype;
247 l_per_ei5_data per_people_extra_info%rowtype;
248
249 l_education_level ghr_pa_requests.education_level%type;
250 l_year_degree_attained ghr_pa_requests.year_degree_attained%type;
251 l_academic_discipline ghr_pa_requests.academic_discipline%type;
252 l_service_comp_date date;
253
254 -- Changes 4093771
255 l_to_basic_pay ghr_pa_requests.to_basic_pay%type;
256 l_to_adj_basic_pay ghr_pa_requests.to_adj_basic_pay%type;
257 l_to_total_salary ghr_pa_requests.to_total_salary%type;
258 -- End Changes 4093771
259 --Bug# 8724192
260 l_orig_appt_auth_code_1_desc varchar2(80);
261 l_orig_appt_auth_code_2_desc varchar2(80);
262 l_assignment_nte_start_date varchar2(255);
263 l_assignment_nte varchar2(255);
264 --Bug# 8724192
265
266 l_proc varchar2(72) := g_package || '.get_all_52_elements';
267
268 l_ms_cnt number := 0;
269 l_recs_failed number := 0;
270 l_mt_status_err_cnt number := 0;
271 l_capped_other_pay number := hr_api.g_number;
272
273 --Bug # 9329643
274 l_employee_number per_people_f.employee_number%type;
275 BEGIN
276 hr_utility.set_location('Entering ' || l_proc,5);
277 hr_utility.set_location('Inside populate and create 52' || l_proc, 15);
278 hr_utility.set_location('Mt in id '||to_char(p_mtin_id) ||' Mt in Name '||p_mtin_name ||
279 'p_business_grp id '||to_char(p_business_group_id) || l_proc, 25);
280 hr_utility.set_location('Eff date ' || to_char(p_effective_date) || l_proc, 35);
281
282 p_retcode := 0;
283
284 for ghr_mti_rec in ghr_mti_cur (p_mtin_id)
285 LOOP
286 l_pa_request_id := ghr_mti_rec.pa_request_id;
287 exit;
288 END LOOP;
289
290 if l_pa_request_id is null then
291 hr_utility.set_message(8301, 'GHR_99999_SELECT_LAC_REMARKS');
292 hr_utility.raise_error;
293 END IF;
294
295 ghr_msl_pkg.get_lac_dtls(l_pa_request_id,
296 l_lac_sf52_rec);
297
298 hr_utility.set_location('After get_lac_dtls ' || l_proc, 44);
299 for per_ext_rec in PER_EXTRA_CUR (p_mtin_id)
300 LOOP
301 BEGIN
302 savepoint EXECUTE_MRE_IN_SP;
303 l_ms_cnt := l_ms_cnt + 1;
304 l_person_id := per_ext_rec.person_id;
305
306 FOR per IN cur_people (p_effective_date,l_person_id) LOOP
307 l_last_name := per.last_name;
308 ---- l_date_of_birth := to_char(per.DATE_OF_BIRTH, 'DD-MON-YYYY');
309 l_date_of_birth := fnd_date.date_to_canonical(per.DATE_OF_BIRTH);
310 l_national_identifier := per.NATIONAL_IDENTIFIER;
311 l_first_name := per.first_name;
312 l_middle_names := per.middle_names;
313 l_full_name := per.full_name;
314 --Bug # 9329643
315 l_employee_number := per.employee_number;
316
317 exit;
318 END LOOP;
319
320 hr_utility.set_location('After fetch people ' || l_proc , 45);
321 hr_utility.set_location('Last Name ' || substr(l_last_name,1,40) , 45);
322 hr_utility.set_location('Date of Birth ' || l_date_of_birth , 45);
323 hr_utility.set_location('Bef fetch from intface ' || to_char(l_person_id) || p_mtin_name , 55);
324
325 for per_mt_int in ghr_mt_int (l_national_identifier,p_mtin_name) loop
326 l_RETIREMENT_PLAN := per_mt_int.RETIREMENT_PLAN;
327 l_RETENTION_ALLOWANCE := per_mt_int.RETENTION_ALLOWANCE;
328 l_TENURE := per_mt_int.TENURE;
329 l_ANNUITANT_INDICATOR := per_mt_int.ANNUITANT_INDICATOR;
330 l_FEGLI := per_mt_int.FEGLI;
331 l_appointment_type := per_mt_int.appointment_type;
332 l_position_id := per_mt_int.to_position_id;
333 l_pay_rate_determinant := per_mt_int.pay_rate_determinant;
334 l_step_or_rate := per_mt_int.from_step_or_rate;
335 -- l_date_arr_pers_office :=
336 -- to_char(per_mt_int.date_arrived_personnel_office, 'DD-MON-YYYY');
337 l_date_arr_pers_office :=
338 fnd_date.date_to_canonical(per_mt_int.date_arrived_personnel_office);
339 -- # Bug 711536
340 -- l_date_wgi_due := to_char( per_mt_int.wgi_date_due, 'DD-MON-YYYY');
341 l_date_wgi_due := fnd_date.date_to_canonical( per_mt_int.wgi_date_due);
342 l_non_disc_agmt_status := per_mt_int.non_disclosure_agmt_status;
343 l_previous_retirement_coverage := per_mt_int.previous_retirement_coverage;
344 l_position_working_title := per_mt_int.position_working_title;
345 l_part_time_indicator := per_mt_int.part_time_indicator;
346 l_mt_status := per_mt_int.mt_status;
347
348 l_flsa_category := per_mt_int.flsa_category;
349 l_bargaining_unit_status := per_mt_int.bargaining_unit_status;
350 l_functional_class := per_mt_int.functional_class;
351 l_supervisory_status := per_mt_int.supervisory_status;
352 l_position_occupied := per_mt_int.position_occupied;
353 l_appropriation_code1 := per_mt_int.appropriation_code1;
354 l_appropriation_code2 := per_mt_int.appropriation_code2;
355 -- # Bug # 711533
356 l_frozen_service := per_mt_int.frozen_service;
357 -- Bug # 712305
358 -- Bug#2412656 Added Fers Coverage
359 l_fers_coverage := per_mt_int.fers_coverage;
360
361 l_part_time_hours := per_mt_int.part_time_hours;
362
363 l_type_of_employment := per_mt_int.TYPE_OF_EMPLOYMENT;
364 l_race_or_national_origin := per_mt_int.RACE_OR_NATIONAL_ORIGIN;
365 l_orig_appointment_auth_code1 := per_mt_int.ORIG_APPOINTMENT_AUTH_CODE_1;
366 l_orig_appointment_auth_code2 := per_mt_int.ORIG_APPOINTMENT_AUTH_CODE_2;
367 l_handicap_code := per_mt_int.HANDICAP_CODE;
368 l_agency_code_transfer_from := per_mt_int.AGENCY_CODE_TRANSFER_FROM;
369
370 l_citizenship := per_mt_int.CITIZENSHIP;
371 l_veterans_preference := per_mt_int.VETERANS_PREFERENCE;
372 l_veterans_preference_for_RIF := per_mt_int.VETERANS_PREFERENCE_FOR_RIF;
373 l_veterans_status := per_mt_int.VETERANS_STATUS;
374
375 l_work_schedule := per_mt_int.work_schedule;
376
377 l_creditable_military_service := per_mt_int.creditable_military_service;
378 l_academic_discipline := per_mt_int.academic_discipline;
379 l_education_level := per_mt_int.educational_level;
380 l_year_degree_attained := per_mt_int.year_degree_attained;
381 l_service_comp_date := per_mt_int.service_comp_date;
382 -- Changes 4093771
383 l_to_basic_pay := per_mt_int.to_basic_salary_rate;
384 l_to_adj_basic_pay := per_mt_int.to_adjusted_basic_pay;
385 l_to_total_salary := per_mt_int.to_total_salary;
386 -- End Changes 4093771
387 --Begin Bug# 8724192
388 l_orig_appt_auth_code_1_desc := per_mt_int.orig_appt_auth_code_1_desc;
389 l_orig_appt_auth_code_2_desc := per_mt_int.orig_appt_auth_code_2_desc;
390 l_assignment_nte_start_date := fnd_date.date_to_canonical(per_mt_int.assignment_nte_start_date);
391 l_assignment_nte := fnd_date.date_to_canonical(per_mt_int.assignment_nte);
392 --End Bug# 8724192
393 exit;
394 end loop;
395
396 hr_utility.set_location('After fetch from int' || l_proc, 65);
397 hr_utility.set_location('Retirement plan ' || l_retirement_plan, 65);
398 hr_utility.set_location('Appointment Type ' || l_appointment_type, 65);
399 hr_utility.set_location('Position Id ' || to_char(l_position_id) ,65);
400
401 if nvl(l_mt_status,'U') = 'P' THEN
402
403 if check_eligibility(l_person_id,p_effective_date) then
404 ghr_pa_requests_pkg.get_SF52_to_data_elements
405 (p_position_id => l_position_id
406 ,p_effective_date => p_effective_date
407 ,p_prd => l_pay_rate_determinant
408 ,p_grade_id => l_grade_id
409 ,p_job_id => l_job_id
410 ,p_organization_id => l_organization_id
411 ,p_location_id => l_location_id
412 ,p_pay_plan => l_pay_plan
413 ,p_occ_code => l_occ_code
414 ,p_grade_or_level => l_grade_or_level
415 ,p_pay_basis => l_pay_basis
416 ,p_position_org_line1 => l_org1
417 ,p_position_org_line2 => l_org2
418 ,p_position_org_line3 => l_org3
419 ,p_position_org_line4 => l_org4
420 ,p_position_org_line5 => l_org5
421 ,p_position_org_line6 => l_org6
422 ,p_duty_station_id => l_duty_station_id);
423
424 hr_utility.set_location('after sf52 to data grade ' || l_proc, 75);
425 hr_utility.set_location('Grade ID ' || l_grade_id, 75);
426 hr_utility.set_location('Pay Plan ' || l_pay_plan, 75);
427 hr_utility.set_location('Occ ' || l_occ_code, 85);
428 hr_utility.set_location('Grade Or Level ' || l_grade_or_level, 85);
429 hr_utility.set_location('Pay Basis ' || l_pay_basis, 85);
430
431 begin
432 ghr_pa_requests_pkg.get_duty_station_details
433 (p_duty_station_id => l_duty_station_id
434 ,p_effective_date => p_effective_date
435 ,p_duty_station_code => l_duty_station_code
436 ,p_duty_station_desc => l_duty_station_desc);
437 exception
438 when others then
439 hr_utility.set_location('Err get duty station_det '||
440 ' Sql Err is '|| sqlerrm(sqlcode), 95);
441 -- Bug#3718167 Added full Name, SSN in l_mass_errbuf
442 --Bug # 9329643 Modified SSN to Emp No
443 l_mass_errbuf := 'Error in ghr_pa_req.get duty station_det for '||l_full_name||' Emp No: '||l_employee_number||
444 ' Sql Err is '|| sqlerrm(sqlcode);
445 raise mass_error;
446 end;
447
448 hr_utility.set_location('After duty stat ' || l_proc, 115);
449 hr_utility.set_location('Duty Station Code ' || l_duty_station_code, 115);
450 hr_utility.set_location('Duty Station Desc ' || l_duty_station_desc, 115);
451
452 begin
453 ghr_history_fetch.fetch_positionei
454 (p_position_id => l_position_id
455 ,p_information_type => 'GHR_US_POS_GRP1'
456 ,p_date_effective => p_effective_date
457 ,p_pos_ei_data => l_pos_grp1_rec);
458 exception
459 when others then
460 hr_utility.set_location('Err fetch_posnei-POS_GRP1'||
461 ' Sql Err is '|| sqlerrm(sqlcode) , 125);
462 -- Bug#3718167 Added full Name, SSN in l_mass_errbuf
463 --Bug # 9329643 Modified SSN to Emp No
464 l_mass_errbuf := 'Error in fetch_positionei -POS_GRP1 for '||l_full_name||' Emp No: '||l_employee_number||
465 ' Sql Err is '|| sqlerrm(sqlcode);
466 raise mass_error;
467 end;
468
469 --- those commented here will be fetched from interface table
470
471 l_personnel_office_id := l_pos_grp1_rec.poei_information3;
472 l_org_structure_id := l_pos_grp1_rec.poei_information5;
473 -- l_FLSA_category := l_pos_grp1_rec.poei_information7;
474 -- l_bargaining_unit_status := l_pos_grp1_rec.poei_information8;
475 -- l_work_schedule := l_pos_grp1_rec.poei_information10;
476 -- l_functional_class := l_pos_grp1_rec.poei_information11;
477 -- l_supervisory_status := l_pos_grp1_rec.poei_information16;
478
479 hr_utility.set_location('After pos grp1 poi ' || l_proc, 135);
480 hr_utility.set_location('Personnel Office id ' || l_personnel_office_id, 135);
481 hr_utility.set_location('work sch ' || l_work_schedule, 135);
482 hr_utility.set_location('bargain ' || l_bargaining_unit_status, 135);
483
484 l_position_title := ghr_api.get_position_title_pos
485 (p_position_id => l_position_id
486 ,p_business_group_id => p_business_group_id ) ;
487
488 l_position_number := ghr_api.get_position_desc_no_pos
489 (p_position_id => l_position_id
490 ,p_business_group_id => p_business_group_id);
491
492 l_position_seq_no := ghr_api.get_position_sequence_no_pos
493 (p_position_id => l_position_id,
494 p_business_group_id => p_business_group_id);
495
496
497 begin
498 ghr_history_fetch.fetch_peopleei
499 (p_person_id => l_person_id
500 ,p_information_type => 'GHR_US_PER_SCD_INFORMATON'
501 ,p_date_effective => p_effective_date
502 ,p_per_ei_data => l_per_ei2_data);
503 exception
504 when others then
505 hr_utility.set_location('Err fetch peopleei-SCDINFO'||
506 ' Sql Err is '|| sqlerrm(sqlcode) , 145);
507 -- Bug#3718167 Added full Name, SSN in l_mass_errbuf
508 --Bug # 9329643 Modified SSN to Emp No
509 l_mass_errbuf := 'Error in fetch peopleei - SCD INFO for '||l_full_name||' Emp No: '||l_employee_number||
510 ' Sql Err is '|| sqlerrm(sqlcode);
511 raise mass_error;
512 end;
513
514 l_serv_comp_date := l_per_ei2_data.pei_INFORMATION3;
515
516 hr_utility.set_location('Serv comp date ' || l_proc, 155);
517 hr_utility.set_location('Serv comp date ' || l_serv_comp_date, 155);
518 hr_utility.set_location('Pos Occ ' || l_position_occupied, 155);
519 hr_utility.set_location('appr 1 ' || l_appropriation_code1, 155);
520 hr_utility.set_location('appr 2 ' || l_appropriation_code2, 155);
521
522 hr_utility.set_location('us per separate retire ' || l_proc,175);
523 hr_utility.set_location('prev ret cov ' || l_previous_retirement_coverage, 175);
524 hr_utility.set_location('Frozen Service ' || l_frozen_service, 175);
525 hr_utility.set_location('FERS Coverage ' || l_fers_coverage, 175);
526
527 FOR c_asg_by_per_id_not_prim_rec in c_asg_by_per_id_not_prim
528 (l_person_id,p_effective_date)
529 LOOP
530
531 l_employee_assignment_id := c_asg_by_per_id_not_prim_rec.assignment_id;
532
533 END LOOP;
534
535 begin
536 assign_to_sf52_rec(
537 l_person_id,
538 l_position_id,
539 l_job_id,
540 l_employee_assignment_id,
541 l_last_name,
542 l_first_name,
543 l_middle_names,
544 l_national_identifier,
545 l_date_of_birth,
546 p_effective_date,
547 l_position_title,
548 l_position_number,
549 l_position_seq_no,
550 l_pay_plan,
551 l_occ_code,
552 l_organization_id,
553 l_grade_id,
554 l_grade_or_level,
555 l_pay_basis,
556 l_step_or_rate,
557 l_veterans_preference,
558 l_veterans_preference_for_RIF,
559 l_FEGLI,
560 l_tenure,
561 l_annuitant_indicator,
562 l_pay_rate_determinant,
563 l_retirement_plan,
564 l_serv_comp_date,
565 l_work_schedule,
566 l_position_occupied,
567 l_flsa_category,
568 l_appropriation_code1,
569 l_appropriation_code2,
570 l_bargaining_unit_status,
571 l_location_id,
572 l_duty_station_id,
573 l_duty_station_code,
574 l_duty_station_desc,
575 l_functional_class,
576 l_citizenship,
577 l_veterans_status,
578 l_supervisory_status,
579 l_type_of_employment,
580 l_race_or_national_origin,
581 l_orig_appointment_auth_code1,
582 l_handicap_code,
583 l_creditable_military_service,
584 l_previous_retirement_coverage,
585 l_frozen_service,
586 l_agency_code_transfer_from,
587 l_org1,
588 l_org2,
589 l_org3,
590 l_org4,
591 l_org5,
592 l_org6,
593 l_to_basic_pay,
594 l_to_adj_basic_pay,
595 l_to_total_salary,
596 l_lac_sf52_rec,
597 l_sf52_rec);
598
599 -- Bug # 711533 [Following values were not being populated from Mass Transfer in Form
600 l_sf52_rec.academic_discipline := l_academic_discipline;
601 l_sf52_rec.year_degree_attained := l_year_degree_attained;
602 l_sf52_rec.education_level := l_education_level;
603 l_sf52_rec.service_comp_date := l_service_comp_date;
604 -- Bug # 712305
605 l_sf52_rec.part_time_hours := l_part_time_hours;
606
607 exception
608 when others then
609 hr_utility.set_location('Others err in assign_sf52'||
610 ' Sql Err is '|| sqlerrm(sqlcode) , 185);
611 -- Bug#3718167 Added full Name, SSN in l_mass_errbuf
612 --Bug # 9329643 Modified SSN to Emp No
613 l_mass_errbuf := 'Others error in assign_sf52 for '||l_full_name||' Emp No: '||l_employee_number||
614 ' Sql Err is '|| sqlerrm(sqlcode);
615 raise mass_error;
616 end;
617
618 hr_utility.set_location('After assign to sf52 rec' || l_proc, 195);
619
620 begin
621 hr_utility.set_location('Calling Redo Pay Calc ' || l_proc, 196);
622 ghr_process_Sf52.redo_pay_calc(l_sf52_rec,l_capped_other_pay);
623 hr_utility.set_location('Calling create_sf52 ' || l_proc, 197);
624
625 -- Adding the following code to keep track of the RPA type and Mass action id
626 --
627 l_sf52_rec.rpa_type := 'MTI';
628 l_sf52_rec.mass_action_id := p_mtin_id;
629 --
630 ghr_mass_changes.create_sf52_for_mass_changes
631 (p_mass_action_type => 'MASS_TRANSFER_IN',
632 p_pa_request_rec => l_sf52_rec,
633 p_errbuf => l_errbuf,
634 p_retcode => l_retcode);
635 exception
636 when others then
637 hr_utility.set_location('Others error in ghr_mass_changes.create_sf52' ||
638 ' Sql Err is '|| sqlerrm(sqlcode) , 205);
639 -- Bug#3718167 Added full Name, SSN in l_mass_errbuf
640 --Bug # 9329643 Modified SSN to Emp No
641 l_mass_errbuf := 'Error in ghr_mass_changes.create_sf52 for '||l_full_name||' Emp No: '||l_employee_number||
642 ' PA Request ID ' || to_char(l_sf52_rec.pa_request_id) ||
643 ' Sql Err is '|| sqlerrm(sqlcode);
644 raise mass_error;
645 end;
646
647 if l_errbuf is not null then
648 hr_utility.set_location('Error in ghr_mass_changes.create_sf52 ' ||
649 l_errbuf , 215);
650 -- Bug#3718167 Added full Name, SSN in l_mass_errbuf
651 --Bug # 9329643 Modified SSN to Emp No
652 l_mass_errbuf := 'Error in ghr_mass_changes.create_sf52 for '||l_full_name||' Emp No: '||l_employee_number||
653 ' PA Request ID ' || to_char(l_sf52_rec.pa_request_id) ||
654 l_errbuf;
655
656 raise mass_error;
657 else
658
659 -- We do not Update the flag to NULL like in other Mass Actions
660 -- because we cannot create multiple previews for Mass transfer IN
661 -- as it is dependent on the interface dump.
662 --Bug # 9329643 Modified SSN to Emp No
663 ghr_mto_int.log_message(
664 p_procedure => 'Successful Completion',
665 p_message => 'Name: '||l_full_name ||
666 ' Emp No: '||l_employee_number ||
667 ' Mass Transfer IN : '||
668 p_mtin_name ||' SF52 Successfully completed');
669
670 ghr_msl_pkg.create_lac_remarks(l_pa_request_id,
671 l_sf52_rec.pa_request_id);
672
673 commit;
674 end if;
675
676 declare
677 l_PA_REQUEST_EXTRA_INFO_ID number;
678 l_pa_OBJECT_VERSION_NUMBER number;
679 --Bug# 8724192
680 l_APP_AUTH_PA_REQUEST_EI_ID number;
681 l_APP_AUTH_PA_OBJECT_VER_NUM number;
682 l_NTE_PA_REQUEST_EI_ID number;
683 l_NTE_PA_OBJECT_VER_NUM number;
684 --Bug# 8724192
685 begin
686 for pa_rec in PA_REQ_EXT_INFO_CUR (l_sf52_rec.pa_request_id)
687 loop
688 l_PA_REQUEST_EXTRA_INFO_ID := pa_rec.PA_REQUEST_EXTRA_INFO_ID;
689 l_pa_OBJECT_VERSION_NUMBER := pa_rec.OBJECT_VERSION_NUMBER;
690 exit;
691 end loop;
692 if l_pa_request_extra_info_id is null then
693 ghr_par_extra_info_api.create_pa_request_extra_info
694 (p_validate => false,
695 p_pa_request_id => l_sf52_rec.pa_request_id,
696 p_information_type => 'GHR_US_PAR_APPT_TRANSFER',
697 p_rei_information_category => 'GHR_US_PAR_APPT_TRANSFER',
698 p_rei_information3 => l_agency_code_transfer_from,
699 p_rei_information4 => l_appointment_type,
700 p_rei_information6 => l_creditable_military_service,
701 p_rei_information7 => l_date_arr_pers_office,
702 p_rei_information8 => l_date_wgi_due,
703 p_rei_information9 => l_frozen_service,
704 p_rei_information10 => l_handicap_code,
705 p_rei_information11 => l_non_disc_agmt_status,
706 ------ p_rei_information12 => l_orig_appointment_auth_code1,
707 ------ p_rei_information13 => l_orig_appointment_auth_code2,
708 p_rei_information14 => l_part_time_indicator,
709 p_rei_information15 => l_position_working_title,
710 p_rei_information16 => l_previous_retirement_coverage,
711 p_rei_information18 => l_race_or_national_origin,
712 p_rei_information19 => l_type_of_employment,
713 -- Bug#2412656 Added fers_coverage
714 p_rei_information21 => l_fers_coverage,
715 p_pa_request_extra_info_id => l_dummy,
716 p_object_version_number => l_dummy);
717
718 else
719 ghr_par_extra_info_api.update_pa_request_extra_info
720 (p_validate => false,
721 p_rei_information3 => l_agency_code_transfer_from,
722 p_rei_information4 => l_appointment_type,
723 p_rei_information6 => l_creditable_military_service,
724 p_rei_information7 => l_date_arr_pers_office,
725 p_rei_information8 => l_date_wgi_due,
726 p_rei_information9 => l_frozen_service,
727 p_rei_information10 => l_handicap_code,
728 p_rei_information11 => l_non_disc_agmt_status,
729 ------ p_rei_information12 => l_orig_appointment_auth_code1,
730 ------ p_rei_information13 => l_orig_appointment_auth_code2,
731 p_rei_information14 => l_part_time_indicator,
732 p_rei_information15 => l_position_working_title,
733 p_rei_information16 => l_previous_retirement_coverage,
734 p_rei_information18 => l_race_or_national_origin,
735 p_rei_information19 => l_type_of_employment,
736 -- Bug#2412656 Added fers_coverage
737 p_rei_information21 => l_fers_coverage,
738 p_pa_request_extra_info_id => l_PA_REQUEST_EXTRA_INFO_ID,
739 p_object_version_number => l_pa_OBJECT_VERSION_NUMBER);
740 end if;
741
742 --Begin Bug# 8724192
743 for pa_rec_auth in PA_REQ_APP_AUTH_EI_CUR (l_sf52_rec.pa_request_id)
744 loop
745 l_APP_AUTH_PA_REQUEST_EI_ID := pa_rec_auth.PA_REQUEST_EXTRA_INFO_ID;
746 l_APP_AUTH_PA_OBJECT_VER_NUM := pa_rec_auth.OBJECT_VERSION_NUMBER;
747 exit;
748 end loop;
749
750 if l_APP_AUTH_PA_REQUEST_EI_ID is null then
751 ghr_par_extra_info_api.create_pa_request_extra_info
752 (p_validate => false,
753 p_pa_request_id => l_sf52_rec.pa_request_id,
754 p_information_type => 'GHR_US_TRANS_CURN_APP_AUTH',
755 p_rei_information_category => 'GHR_US_TRANS_CURN_APP_AUTH',
756 p_rei_information1 => l_orig_appointment_auth_code1,
757 p_rei_information2 => l_orig_appt_auth_code_1_desc,
758 p_rei_information3 => l_orig_appointment_auth_code2,
759 p_rei_information4 => l_orig_appt_auth_code_2_desc,
760 p_pa_request_extra_info_id => l_dummy,
761 p_object_version_number => l_dummy);
762
763 else
764 ghr_par_extra_info_api.update_pa_request_extra_info
765 (p_validate => false,
766 p_rei_information1 => l_orig_appointment_auth_code1,
767 p_rei_information2 => l_orig_appt_auth_code_1_desc,
768 p_rei_information3 => l_orig_appointment_auth_code2,
769 p_rei_information4 => l_orig_appt_auth_code_2_desc,
770 p_pa_request_extra_info_id => l_APP_AUTH_PA_REQUEST_EI_ID,
771 p_object_version_number => l_APP_AUTH_PA_OBJECT_VER_NUM);
772
773 End if;
774
775 for pa_rec_nte in PA_REQ_NTE_EI_CUR (l_sf52_rec.pa_request_id)
776 loop
777 l_NTE_PA_REQUEST_EI_ID := pa_rec_nte.PA_REQUEST_EXTRA_INFO_ID;
778 l_NTE_PA_OBJECT_VER_NUM := pa_rec_nte.OBJECT_VERSION_NUMBER;
779 exit;
780 end loop;
781
782 if l_NTE_PA_REQUEST_EI_ID is null then
783 ghr_par_extra_info_api.create_pa_request_extra_info
784 (p_validate => false,
785 p_pa_request_id => l_sf52_rec.pa_request_id,
786 p_information_type => 'GHR_US_MASS_TRNSFR_NTE_DATES',
787 p_rei_information_category => 'GHR_US_MASS_TRNSFR_NTE_DATES',
788 p_rei_information10 => l_assignment_nte_start_date,
789 p_rei_information11 => l_assignment_nte,
790 p_pa_request_extra_info_id => l_dummy,
791 p_object_version_number => l_dummy);
792
793 else
794 ghr_par_extra_info_api.update_pa_request_extra_info
795 (p_validate => false,
796 p_rei_information10 => l_assignment_nte_start_date,
797 p_rei_information11 => l_assignment_nte,
798 p_pa_request_extra_info_id => l_NTE_PA_REQUEST_EI_ID,
799 p_object_version_number => l_NTE_PA_OBJECT_VER_NUM);
800 end if;
801 --end Bug# 8724192
802 exception
803 when others then
804 hr_utility.set_location('Error in ghr_par_extra info.create pa req'||
805 ' Sql Err is '|| sqlerrm(sqlcode) , 225);
806 -- Bug#3718167 Added full Name, SSN in l_mass_errbuf
807 --Bug # 9329643 Modified SSN to Emp No
808 l_mass_errbuf := 'Error in ghr_par_extra info.create pa req for '||l_full_name||' Emp No: '||l_employee_number||
809 ' Sql Err is '|| sqlerrm(sqlcode);
810 raise mass_error;
811 end;
812 hr_utility.set_location('After create pa_req_extra_info' || l_proc, 235);
813 else
814
815
816 -- We do not Update the flag to NULL like in other Mass Actions
817 -- because we cannot create multiple previews for Mass transfer IN
818 -- as it is dependent on the interface dump.
819
820 commit;
821 end if; ---- for check_eligibility...
822 else
823
824 l_mt_status_err_cnt := l_mt_status_err_cnt + 1;
825 end if; ---- for mt_status != 'P'
826
827 EXCEPTION
828 when mass_error then
829 hr_utility.set_location('Mass error raised ' || l_mass_errbuf , 245);
830 begin
831 ROLLBACK TO EXECUTE_MRE_IN_SP;
832 exception
833 when others then null;
834 end;
835
836 update ghr_mass_transfers
837 set status = 'E'
838 where mass_transfer_id = p_mtin_id;
839 commit;
840 l_recs_failed := l_recs_failed + 1;
841 p_errbuf := 'Error in '||l_proc || 'Details in GHR_PROCESS_LOG';
842 p_retcode := 1;
843 begin
844
845 ghr_mto_int.log_message(p_procedure => 'GHMTIAPP',
846 p_message => l_mass_errbuf);
847 commit;
848 exception
849 when others then
850 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
851 hr_utility.raise_error;
852 end;
853
854 WHEN OTHERS THEN
855 hr_utility.set_location('Err (Oth) Sql error '||sqlerrm(sqlcode),30);
856 l_mass_errbuf := 'Error in '||l_proc|| ' Sql Err is '||sqlerrm(sqlcode);
857 begin
858 ROLLBACK TO EXECUTE_MRE_IN_SP;
859 exception
860 when others then null;
861 end;
862
863 update ghr_mass_transfers
864 set status = 'E'
865 where mass_transfer_id = p_mtin_id;
866 commit;
867
868 l_recs_failed := l_recs_failed + 1;
869 p_errbuf := 'Error in '||l_proc || 'Details in GHR_PROCESS_LOG';
870 p_retcode := 1;
871 begin
872 ghr_mto_int.log_message(p_procedure => 'GHMTIAPP',
873 p_message => l_mass_errbuf);
874 commit;
875 exception
876 when others then
877 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
878 hr_utility.raise_error;
879 end;
880 END;
881 END LOOP;
882
883 for mt_int_rec in ghr_mt_int_count(p_mtin_name)
884 loop
885 l_mt_status_err_cnt := mt_int_rec.count;
886 exit;
887 end loop;
888
889 if (l_recs_failed = 0) then
890 begin
891 update ghr_mass_transfers
892 set status = decode(l_mt_status_err_cnt,0,'P','E')
893 where mass_transfer_id = p_mtin_id;
894 commit;
895 EXCEPTION
896 when others then
897 hr_utility.set_location('Err in Update mass_transfers Sql err '||sqlerrm(sqlcode),30);
898 hr_utility.set_message(8301, 'GHR_38476_UPD_GHR_MSL_FAILURE');
899 hr_utility.raise_error;
900 END;
901 else
902 update ghr_mass_transfers
903 set status = 'E'
904 where mass_transfer_id = p_mtin_id;
905 commit;
906
907 p_errbuf := 'Error in '||l_proc || 'Details in GHR_PROCESS_LOG';
908 p_retcode := 1;
909 end if;
910
911 EXCEPTION
912 when mass_error then
913 hr_utility.set_location('Mass error raised' || l_mass_errbuf , 245);
914 ROLLBACK TO EXECUTE_MRE_IN_SP;
915
916 update ghr_mass_transfers
917 set status = 'E'
918 where mass_transfer_id = p_mtin_id;
919 commit;
920 l_recs_failed := l_recs_failed + 1;
921 p_errbuf := 'Error in '||l_proc || 'Details in GHR_PROCESS_LOG';
922 p_retcode := 1;
923 begin
924 ghr_mto_int.log_message(p_procedure => 'GHMTIAPP',
925 p_message => l_mass_errbuf);
926 commit;
927 exception
928 when others then
929 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
930 hr_utility.raise_error;
931 end;
932 WHEN OTHERS THEN
933 hr_utility.set_location('Err (Others) occurred in Sql error '|| sqlerrm(sqlcode),30);
934 hr_utility.set_location('Error others '||' Err is '||sqlerrm(sqlcode),20);
935 l_mass_errbuf := 'Error in '||l_proc||
936 ' Sql Err is '||sqlerrm(sqlcode);
937 begin
938 ROLLBACK TO EXECUTE_MRE_IN_SP;
939 exception
940 when others then null;
941 end;
942
943 update ghr_mass_transfers
944 set status = 'E'
945 where mass_transfer_id = p_mtin_id;
946 commit;
947
948 l_recs_failed := l_recs_failed + 1;
949 p_errbuf := 'Error in '||l_proc || 'Details in GHR_PROCESS_LOG';
950 p_retcode := 1;
951 BEGIN
952 ghr_mto_int.log_message(p_procedure => 'GHMTIAPP',
953 p_message => l_mass_errbuf);
954 COMMIT;
955 EXCEPTION
956 WHEN OTHERS THEN
957 p_errbuf := NULL;
958 p_retcode := NULL;
959 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
960 hr_utility.raise_error;
961 END;
962 END populate_and_create_52;
963
964 --
965 --
966 --
967
968 function check_eligibility(p_person_id in number,
969 p_effective_date in date) return boolean is
970
971 l_proc varchar2(72) := g_package || '.check_eligibility';
972 begin
973 if GHR_MRE_PKG.person_in_pa_req_1noa
974 (p_person_id => p_person_id,
975 p_effective_date => p_effective_date,
976 p_first_noa_code => '132'
977 ) then
978 return false;
979 end if;
980 /********************
981 if GHR_MRE_PKG.person_in_pa_req_2noa
982 (p_person_id => p_person_id,
983 p_effective_date => p_effective_date,
984 p_second_noa_code => '132'
985 ) then
986 return false;
987 end if;
988 ****************/
989
990 return true;
991 end;
992
993 procedure assign_to_sf52_rec(
994 p_person_id in number
995 ,p_position_id in number
996 ,p_job_id in number
997 ,p_employee_assignment_id in number
998 ,p_last_name in varchar2
999 ,p_first_name in varchar2
1000 ,p_middle_names in varchar2
1001 ,p_national_identifier in varchar2
1002 ,p_date_of_birth in varchar2
1003 ,p_effective_date in varchar2
1004 ,p_position_title in varchar2
1005 ,p_position_number in varchar2
1006 ,p_position_seq_no in number
1007 ,p_pay_plan in varchar2
1008 ,p_occ_code in varchar2
1009 ,p_organization_id in number
1010 ,p_grade_id in number
1011 ,p_grade_or_level in varchar2
1012 ,p_pay_basis in varchar2
1013 ,p_step_or_rate in varchar2
1014 ,p_veterans_preference in varchar2
1015 ,p_vet_preference_for_RIF in varchar2
1016 ,p_FEGLI in varchar2
1017 ,p_tenure in varchar2
1018 ,p_annuitant_indicator in varchar2
1019 ,p_pay_rate_determinant in varchar2
1020 ,p_retirement_plan in varchar2
1021 ,p_service_comp_date in varchar2
1022 ,p_work_schedule in varchar2
1023 ,p_position_occupied in varchar2
1024 ,p_flsa_category in varchar2
1025 ,p_appropriation_code1 in varchar2
1026 ,p_appropriation_code2 in varchar2
1027 ,p_bargaining_unit_status in varchar2
1028 ,p_duty_station_location_id in number
1029 ,p_duty_station_id in number
1030 ,p_duty_station_code in varchar2
1031 ,p_duty_station_desc in varchar2
1032 ,p_functional_class in varchar2
1033 ,p_citizenship in varchar2
1034 ,p_veterans_status in varchar2
1035 ,p_supervisory_status in varchar2
1036 ,p_type_of_employment in varchar2
1037 ,p_race_or_national_origin in varchar2
1038 ,p_orig_appointment_auth_code1 in varchar2
1039 ,p_handicap_code in varchar2
1040 ,p_creditable_military_service in varchar2
1041 ,p_previous_retirement_coverage in varchar2
1042 ,p_frozen_service in varchar2
1043 ,p_agency_code_transfer_from in varchar2
1044 ,p_to_position_org_line1 IN varchar2
1045 ,p_to_position_org_line2 IN varchar2
1046 ,p_to_position_org_line3 IN varchar2
1047 ,p_to_position_org_line4 IN varchar2
1048 ,p_to_position_org_line5 IN varchar2
1049 ,p_to_position_org_line6 IN varchar2
1050 -- Changes 4093771
1051 , p_to_basic_pay IN number
1052 , p_to_adj_basic_pay IN number
1053 , p_to_total_salary IN number
1054 -- End Changes 4093771
1055 ,p_lac_sf52_rec in ghr_pa_requests%rowtype
1056 ,p_sf52_rec out nocopy ghr_pa_requests%rowtype)
1057 IS
1058
1059 l_proc varchar2(72)
1060 := g_package || '.assign_to_sf52_rec';
1061 begin
1062
1063 hr_utility.set_location('Entering ' || l_proc,5);
1064
1065 p_sf52_rec.pa_request_id := null;
1066 p_sf52_rec.pa_notification_id := null;
1067 p_sf52_rec.noa_family_code := 'APP';
1068 p_sf52_rec.routing_group_id := null;
1069 p_sf52_rec.academic_discipline := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1070 p_sf52_rec.additional_info_person_id := null;
1071 p_sf52_rec.additional_info_tel_number := null;
1072 p_sf52_rec.agency_code := null;
1073 p_sf52_rec.altered_pa_request_id := null;
1074 p_sf52_rec.annuitant_indicator := p_annuitant_indicator;
1075 p_sf52_rec.annuitant_indicator_desc := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1076 p_sf52_rec.appropriation_code1 := p_appropriation_code1;
1077 p_sf52_rec.appropriation_code2 := p_appropriation_code2;
1078 p_sf52_rec.approval_date := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1079 p_sf52_rec.approving_official_full_name := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1080 p_sf52_rec.approving_official_work_title := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1081 p_sf52_rec.authorized_by_person_id := null;
1082 p_sf52_rec.authorized_by_title := null;
1083 p_sf52_rec.award_amount := null;
1084 p_sf52_rec.award_uom := null;
1085 p_sf52_rec.bargaining_unit_status := p_bargaining_unit_status;
1086 p_sf52_rec.citizenship := p_citizenship;
1087 p_sf52_rec.concurrence_date := null;
1088 p_sf52_rec.custom_pay_calc_flag := null;
1089 p_sf52_rec.duty_station_code := p_duty_station_code;
1090 p_sf52_rec.duty_station_desc := p_duty_station_desc;
1091 p_sf52_rec.duty_station_id := p_duty_station_id;
1092 p_sf52_rec.duty_station_location_id := p_duty_station_location_id;
1093 p_sf52_rec.education_level := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1094 p_sf52_rec.effective_date := p_effective_date;
1095 p_sf52_rec.employee_assignment_id := p_employee_assignment_id;
1096 ---p_sf52_rec.employee_date_of_birth := to_date(p_date_of_birth, 'DD-MON-YYYY');
1097 p_sf52_rec.employee_date_of_birth := fnd_date.canonical_to_date(p_date_of_birth);
1098 p_sf52_rec.employee_dept_or_agency := null;
1099 p_sf52_rec.employee_first_name := p_first_name;
1100 p_sf52_rec.employee_last_name := p_last_name;
1101 p_sf52_rec.employee_middle_names := p_middle_names;
1102 p_sf52_rec.employee_national_identifier := p_national_identifier;
1103 p_sf52_rec.fegli := p_fegli;
1104 p_sf52_rec.fegli_desc := null;
1105 p_sf52_rec.first_action_la_code1 := p_lac_sf52_rec.first_action_la_code1;
1106 p_sf52_rec.first_action_la_code2 := p_lac_sf52_rec.first_action_la_code2;
1107 p_sf52_rec.first_action_la_desc1 := p_lac_sf52_rec.first_action_la_desc1;
1108 p_sf52_rec.first_action_la_desc2 := p_lac_sf52_rec.first_action_la_desc2;
1109 p_sf52_rec.first_noa_cancel_or_correct := null;
1110 p_sf52_rec.first_noa_code := '132';
1111 p_sf52_rec.first_noa_desc := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1112 p_sf52_rec.first_noa_id := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1113 p_sf52_rec.first_noa_pa_request_id := null;
1114 p_sf52_rec.flsa_category := p_flsa_category;
1115 p_sf52_rec.forwarding_address_line1 := null;
1116 p_sf52_rec.forwarding_address_line2 := null;
1117 p_sf52_rec.forwarding_address_line3 := null;
1118 p_sf52_rec.forwarding_country := null;
1119 p_sf52_rec.forwarding_country_short_name := null;
1120 p_sf52_rec.forwarding_postal_code := null;
1121 p_sf52_rec.forwarding_region_2 := null;
1122 p_sf52_rec.forwarding_town_or_city := null;
1123 p_sf52_rec.from_adj_basic_pay := null;
1124 p_sf52_rec.from_agency_code := null;
1125 p_sf52_rec.from_agency_desc := null;
1126 p_sf52_rec.from_basic_pay := null;
1127 p_sf52_rec.from_grade_or_level := null;
1128 p_sf52_rec.from_locality_adj := null;
1129 p_sf52_rec.from_occ_code := null;
1130 p_sf52_rec.from_office_symbol := null;
1131 p_sf52_rec.from_other_pay_amount := null;
1132 p_sf52_rec.from_pay_basis := null;
1133 p_sf52_rec.from_pay_plan := null;
1134 p_sf52_rec.from_position_id := null;
1135 p_sf52_rec.from_position_org_line1 := p_agency_code_transfer_from; --AVR
1136 p_sf52_rec.from_position_org_line2 := null;
1137 p_sf52_rec.from_position_org_line3 := null;
1138 p_sf52_rec.from_position_org_line4 := null;
1139 p_sf52_rec.from_position_org_line5 := null;
1140 p_sf52_rec.from_position_org_line6 := null;
1141 p_sf52_rec.from_position_number := null;
1142 p_sf52_rec.from_position_seq_no := null;
1143 p_sf52_rec.from_position_title := null;
1144 p_sf52_rec.from_step_or_rate := null;
1145 p_sf52_rec.from_total_salary := null;
1146 p_sf52_rec.functional_class := p_functional_class;
1147 p_sf52_rec.no_of_notification_printed := null;
1148 p_sf52_rec.notepad := null;
1149 p_sf52_rec.notification_printed_by := null;
1150 p_sf52_rec.part_time_hours := null;
1151 p_sf52_rec.pay_rate_determinant := p_pay_rate_determinant;
1152 p_sf52_rec.personnel_office_id := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1153 p_sf52_rec.person_id := p_person_id;
1154 p_sf52_rec.position_occupied := p_position_occupied;
1155 p_sf52_rec.proposed_effective_asap_flag := null;
1156 p_sf52_rec.proposed_effective_date := null;
1157 p_sf52_rec.requested_by_person_id := null;
1158 p_sf52_rec.requested_by_title := null;
1159 p_sf52_rec.requested_date := null;
1160 p_sf52_rec.requesting_office_remarks_desc := null;
1161 p_sf52_rec.requesting_office_remarks_flag := null;
1162 p_sf52_rec.request_number := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1163 p_sf52_rec.resign_and_retire_reason_desc := null;
1164 p_sf52_rec.retirement_plan := p_retirement_plan;
1165 p_sf52_rec.retirement_plan_desc := null;
1166 p_sf52_rec.second_action_la_code1 := null;
1167 p_sf52_rec.second_action_la_code2 := null;
1168 p_sf52_rec.second_action_la_desc1 := null;
1169 p_sf52_rec.second_action_la_desc2 := null;
1170 p_sf52_rec.second_noa_cancel_or_correct := null;
1171 p_sf52_rec.second_noa_code := null;
1172 p_sf52_rec.second_noa_desc := null;
1173 p_sf52_rec.second_noa_id := null;
1174 p_sf52_rec.second_noa_pa_request_id := null;
1175 p_sf52_rec.service_comp_date := p_service_comp_date;
1176 p_sf52_rec.sf50_approval_date := null;
1177 p_sf52_rec.sf50_approving_ofcl_full_name := null;
1178 p_sf52_rec.sf50_approving_ofcl_work_title := null;
1179 p_sf52_rec.status := null;
1180 p_sf52_rec.supervisory_status := p_supervisory_status;
1181 p_sf52_rec.tenure := p_tenure;
1182 p_sf52_rec.to_adj_basic_pay := null;
1183 p_sf52_rec.to_ap_premium_pay_indicator := null;
1184 p_sf52_rec.to_auo_premium_pay_indicator := null;
1185 p_sf52_rec.to_au_overtime := null;
1186 p_sf52_rec.to_availability_pay := null;
1187 p_sf52_rec.to_basic_pay := null;
1188 p_sf52_rec.to_grade_id := p_grade_id;
1189 p_sf52_rec.to_grade_or_level := p_grade_or_level;
1190 p_sf52_rec.to_job_id := p_job_id;
1191 p_sf52_rec.to_locality_adj := null;
1192 p_sf52_rec.to_occ_code := p_occ_code;
1193 p_sf52_rec.to_office_symbol := null;
1194 p_sf52_rec.to_organization_id := p_organization_id;
1195 p_sf52_rec.to_other_pay_amount := null;
1196 p_sf52_rec.to_pay_basis := p_pay_basis;
1197 p_sf52_rec.to_pay_plan := p_pay_plan;
1198 p_sf52_rec.to_position_id := p_position_id;
1199 p_sf52_rec.to_position_org_line1 := p_to_position_org_line1;
1200 p_sf52_rec.to_position_org_line2 := p_to_position_org_line2;
1201 p_sf52_rec.to_position_org_line3 := p_to_position_org_line3;
1202 p_sf52_rec.to_position_org_line4 := p_to_position_org_line4;
1203 p_sf52_rec.to_position_org_line5 := p_to_position_org_line5;
1204 p_sf52_rec.to_position_org_line6 := p_to_position_org_line6;
1205 p_sf52_rec.to_position_number := p_position_number;
1206 p_sf52_rec.to_position_seq_no := p_position_seq_no;
1207 p_sf52_rec.to_position_title := p_position_title;
1208 p_sf52_rec.to_retention_allowance := null;
1209 p_sf52_rec.to_staffing_differential := null;
1210 p_sf52_rec.to_step_or_rate := p_step_or_rate;
1211 p_sf52_rec.to_supervisory_differential := null;
1212 p_sf52_rec.to_total_salary := null;
1213 p_sf52_rec.veterans_preference := p_veterans_preference; -- Re-populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES?
1214 p_sf52_rec.veterans_pref_for_rif := p_vet_preference_for_rif; -- Re-populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES?
1215 p_sf52_rec.veterans_status := p_veterans_status; -- Re-populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES?
1216 p_sf52_rec.work_schedule := p_work_schedule;
1217 p_sf52_rec.work_schedule_desc := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1218 p_sf52_rec.year_degree_attained := null; -- Populated in GHR_MASS_CHANGES.CREATE_SF52_FOR_MASS_CHANGES
1219 p_sf52_rec.first_noa_information1 := null;
1220 p_sf52_rec.first_noa_information2 := null;
1221 p_sf52_rec.first_noa_information3 := null;
1222 p_sf52_rec.first_noa_information4 := null;
1223 p_sf52_rec.first_noa_information5 := null;
1224 p_sf52_rec.second_lac1_information1 := p_lac_sf52_rec.second_lac1_information1;
1225 p_sf52_rec.second_lac1_information2 := p_lac_sf52_rec.second_lac1_information2;
1226 p_sf52_rec.second_lac1_information3 := p_lac_sf52_rec.second_lac1_information3;
1227 p_sf52_rec.second_lac1_information4 := p_lac_sf52_rec.second_lac1_information4;
1228 p_sf52_rec.second_lac1_information5 := p_lac_sf52_rec.second_lac1_information5;
1229 p_sf52_rec.second_lac2_information1 := null;
1230 p_sf52_rec.second_lac2_information2 := null;
1231 p_sf52_rec.second_lac2_information3 := null;
1232 p_sf52_rec.second_lac2_information4 := null;
1233 p_sf52_rec.second_lac2_information5 := null;
1234 p_sf52_rec.second_noa_information1 := null;
1235 p_sf52_rec.second_noa_information2 := null;
1236 p_sf52_rec.second_noa_information3 := null;
1237 p_sf52_rec.second_noa_information4 := null;
1238 p_sf52_rec.second_noa_information5 := null;
1239 p_sf52_rec.first_lac1_information1 := p_lac_sf52_rec.first_lac1_information1;
1240 p_sf52_rec.first_lac1_information2 := p_lac_sf52_rec.first_lac1_information2;
1241 p_sf52_rec.first_lac1_information3 := p_lac_sf52_rec.first_lac1_information3;
1242 p_sf52_rec.first_lac1_information4 := p_lac_sf52_rec.first_lac1_information4;
1243 p_sf52_rec.first_lac1_information5 := p_lac_sf52_rec.first_lac1_information5;
1244 p_sf52_rec.first_lac2_information1 := null;
1245 p_sf52_rec.first_lac2_information2 := null;
1246 p_sf52_rec.first_lac2_information3 := null;
1247 p_sf52_rec.first_lac2_information4 := null;
1248 p_sf52_rec.first_lac2_information5 := null;
1249 p_sf52_rec.attribute_category := null;
1250 p_sf52_rec.attribute1 := null;
1251 p_sf52_rec.attribute2 := null;
1252 p_sf52_rec.attribute3 := null;
1253 p_sf52_rec.attribute4 := null;
1254 p_sf52_rec.attribute5 := null;
1255 p_sf52_rec.attribute6 := null;
1256 p_sf52_rec.attribute7 := null;
1257 p_sf52_rec.attribute8 := null;
1258 p_sf52_rec.attribute9 := null;
1259 p_sf52_rec.attribute10 := null;
1260 p_sf52_rec.attribute11 := null;
1261 p_sf52_rec.attribute12 := null;
1262 p_sf52_rec.attribute13 := null;
1263 p_sf52_rec.attribute14 := null;
1264 p_sf52_rec.attribute15 := null;
1265 p_sf52_rec.attribute16 := null;
1266 p_sf52_rec.attribute17 := null;
1267 p_sf52_rec.attribute18 := null;
1268 p_sf52_rec.attribute19 := null;
1269 p_sf52_rec.attribute20 := null;
1270 p_sf52_rec.created_by := null;
1271 p_sf52_rec.creation_date := null;
1272 p_sf52_rec.last_updated_by := null;
1273 p_sf52_rec.last_update_date := null;
1274 p_sf52_rec.last_update_login := null;
1275 p_sf52_rec.object_version_number := null;
1276
1277 -- Changes 4093771
1278 IF ghr_pay_calc.get_open_pay_range(p_position_id=>p_position_id,
1279 p_person_id=>p_person_id,
1280 p_prd=>p_pay_rate_determinant,
1281 p_pa_request_id => NULL,
1282 p_effective_date => NVL(p_effective_date,sysdate)) = TRUE THEN
1283 p_sf52_rec.to_basic_pay := p_to_basic_pay;
1284 p_sf52_rec.to_adj_basic_pay := p_to_adj_basic_pay;
1285 p_sf52_rec.to_total_salary := p_to_total_salary;
1286 END IF;
1287 -- End Changes 4093771
1288
1289 hr_utility.set_location('Exiting ' || l_proc,10);
1290
1291 EXCEPTION
1292 WHEN others THEN
1293 -- Reset IN OUT parameters and set OUT parameters
1294
1295 p_sf52_rec := NULL;
1296
1297 hr_utility.set_location('Leaving ' || l_proc,60);
1298 RAISE;
1299
1300 end assign_to_sf52_rec;
1301
1302 --
1303 --
1304 --
1305
1306 procedure pr (msg varchar2,par1 in varchar2 default null,
1307 par2 in varchar2 default null) is
1308 begin
1309 null;
1310 ---DBMS_OUTPUT.PUT_LINE(msg||'-'||par1||' -'||par2||'-');
1311 end;
1312
1313 END GHR_MTI_APP;