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