[Home] [Help]
PACKAGE BODY: APPS.GHR_MTI_INT_INSERT
Source
1 PACKAGE BODY GHR_MTI_INT_INSERT AS
2 /* $Header: ghrmtins.pkb 120.1.12010000.2 2009/08/07 09:42:44 utokachi ship $ */
3
4 procedure main_convert(
5 p_transfer_name IN varchar2,
6 p_process_date IN date,
7 p_effective_date IN date,
8 p_source IN varchar2,
9 p_status IN varchar2,
10 p_person_id IN OUT NOCOPY number,
11 p_inter_bg_transfer IN varchar2,
12 p_date_of_birth IN date,
13 p_effective_end_date IN date,
14 p_effective_start_date IN date,
15 p_first_name IN varchar2,
16 p_full_name IN varchar2,
17 p_last_name IN varchar2,
18 p_marital_status IN varchar2,
19 p_middle_names IN varchar2,
20 p_national_identifier IN varchar2,
21 p_nationality IN varchar2,
22 p_rehire_reason IN varchar2,
23 p_sex IN varchar2,
24 p_start_date IN date,
25 p_title IN varchar2,
26 p_work_telephone IN varchar2,
27 p_citizenship IN varchar2,
28 p_veterans_preference IN varchar2,
29 p_veterans_preference_for_RIF IN varchar2,
30 p_veterans_status IN varchar2,
31 p_appointment_type IN varchar2,
32 p_type_of_employment IN varchar2,
33 p_race_or_national_origin IN varchar2,
34 p_agency_code_transfer_from IN varchar2,
35 p_orig_appointment_auth_code_1 IN varchar2,
36 p_orig_appt_auth_code_1_desc IN varchar2,--Bug# 8724192
37 p_orig_appointment_auth_code_2 IN varchar2,
38 p_orig_appt_auth_code_2_desc IN varchar2,--Bug# 8724192
39 p_handicap_code IN varchar2,
40 p_service_comp_date IN date,
41 -- Bug 2412656 Added FERS Coverage
42 p_fers_coverage IN VARCHAR2,
43 p_previous_retirement_coverage IN varchar2,
44 p_frozen_service IN varchar2,
45 p_Creditable_Military_Service IN varchar2,
46 p_flsa_category IN varchar2,
47 p_bargaining_unit_status IN varchar2,
48 p_functional_class IN varchar2,
49 p_position_working_title IN varchar2,
50 p_supervisory_status IN varchar2,
51 p_position_occupied IN varchar2,
52 p_appropriation_code1 IN varchar2,
53 p_appropriation_code2 IN varchar2,
54 p_total_salary IN number,
55 p_basic_salary_rate IN number,
56 p_locality_adjustment IN number,
57 p_adjusted_basic_pay IN number,
58 p_other_pay IN number,
59 p_fegli IN varchar2,
60 p_retirement_plan IN varchar2,
61 p_retention_allowance IN number,
62 p_staffing_differential IN number,
63 p_supervisory_differential IN number,
64 p_wgi_date_due IN date,
65 p_fegli_desc IN varchar2,
66 p_retirement_plan_desc IN varchar2,
67 p_au_overtime IN number,
68 p_availability_pay IN number,
69 p_auo_premium_pay_indicator IN varchar2,
70 p_ap_premium_pay_indicator IN varchar2,
71 p_to_position_id IN number,
72 p_from_grade_or_level IN varchar2,
73 p_from_pay_plan IN varchar2,
74 p_from_position_title IN varchar2,
75 p_from_position_seq_num IN number,
76 p_duty_station_code IN varchar2,
77 p_duty_station_desc IN varchar2,
78 p_from_step_or_rate IN varchar2,
79 p_tenure IN varchar2,
80 p_annuitant_indicator IN varchar2,
81 p_pay_rate_determinant IN varchar2,
82 p_work_schedule IN varchar2,
83 p_part_time_hours IN number,
84 p_date_arrivd_personnel_office IN date,
85 p_non_disclosure_agmt_status IN varchar2,
86 p_part_time_indicator IN varchar2,
87 p_qualif_standards_waiver IN varchar2,
88 p_education_level IN varchar2,
89 p_academic_discipline IN varchar2,
90 p_year_degree_attained IN varchar2,
91 -- Changes 4093771
92 p_to_total_salary IN number,
93 p_to_basic_salary_rate IN number,
94 p_to_adjusted_basic_pay IN number,
95 -- End Changes 4093771
96 --Begin Bug# 8724192
97 p_assignment_nte_start_date IN date,
98 p_assignment_nte IN date
99 --end Bug# 8724192
100 ) is
101
102 cursor cur_pid is
103 select ghr_interface_2_s.nextval nvalue from dual;
104
105 l_person_id number;
106 l_assignment_id number;
107 l_position_id number;
108 l_action varchar2(30);
109 l_person_id_nc number; --For NOCOPY Changes
110
111 begin
112 l_person_id_nc := p_person_id ; --NOCOPY Changes
113 if p_person_id is null then
114 l_action := 'INSERT';
115 for cur_pid_rec in cur_pid
116 loop
117 l_person_id := cur_pid_rec.nvalue;
118 l_assignment_id := l_person_id;
119 l_position_id := l_person_id;
120 exit;
121 end loop;
122 else
123 l_action := 'UPDATE';
124 l_person_id := p_person_id;
125 end if;
126
127 p_person_id := l_person_id;
128
129 if l_action = 'INSERT' then
130 map_mtv_to_assign_f(
131 p_transfer_name => p_transfer_name,
132 p_effective_date => p_effective_date,
133 p_assignment_id => l_assignment_id );
134
135 map_mtv_to_position(
136 p_transfer_name => p_transfer_name,
137 p_person_id => l_person_id,
138 p_effective_date => p_effective_date,
139 p_position_id => l_position_id );
140 end if;
141
142 map_mtv_to_people_f(
143 p_transfer_name => p_transfer_name,
144 p_inter_bg_transfer => p_inter_bg_transfer,
145 p_effective_date => p_effective_date,
146 p_person_id => l_person_id,
147 p_date_of_birth => p_date_of_birth,
148 p_effective_end_date => p_effective_end_date,
149 p_effective_start_date => p_effective_start_date,
150 p_first_name => p_first_name,
151 p_full_name => p_full_name,
152 p_last_name => p_last_name,
153 p_marital_status => p_marital_status,
154 p_middle_names => p_middle_names,
155 p_national_identifier => p_national_identifier,
156 p_nationality => p_nationality,
157 p_rehire_reason => p_rehire_reason,
158 p_sex => p_sex,
159 p_start_date => p_start_date,
160 p_title => p_title,
161 p_work_telephone => p_work_telephone,
162 p_action => l_action );
163
164 map_mtv_to_people_ei1(
165 p_transfer_name => p_transfer_name,
166 p_effective_date => p_effective_date,
167 p_person_id => l_person_id,
168 p_citizenship => p_citizenship,
169 p_veterans_preference => p_veterans_preference,
170 p_veterans_preference_for_RIF => p_veterans_preference_for_RIF,
171 p_veterans_status => p_veterans_status,
172 p_action => l_action );
173
174 map_mtv_to_people_ei2(
175 p_transfer_name => p_transfer_name,
176 p_effective_date => p_effective_date,
177 p_person_id => l_person_id,
178 p_appointment_type => p_appointment_type,
179 p_type_of_employment => p_type_of_employment,
180 p_race_or_national_origin => p_race_or_national_origin,
181 -- p_agency_code_transfer_from => p_agency_code_transfer_from,
182 p_orig_appointment_auth_code_1 => p_orig_appointment_auth_code_1,
183 p_orig_appt_auth_code_1_desc => p_orig_appt_auth_code_1_desc, --Bug# 8724192
184 p_orig_appointment_auth_code_2 => p_orig_appointment_auth_code_2,
185 p_orig_appt_auth_code_2_desc => p_orig_appt_auth_code_2_desc, --Bug# 8724192
186 p_handicap_code => p_handicap_code,
187 p_action => l_action );
188
189 map_mtv_to_people_ei3(
190 p_transfer_name => p_transfer_name,
191 p_effective_date => p_effective_date,
192 p_person_id => l_person_id,
193 p_service_comp_date => p_service_comp_date,
194 p_action => l_action );
195
196 -- Bug 2412656 Added FERS Coverage
197 map_mtv_to_people_ei4(
198 p_transfer_name => p_transfer_name,
199 p_effective_date => p_effective_date,
200 p_person_id => l_person_id,
201 p_fers_coverage => p_fers_coverage,
202 p_previous_retirement_coverage => p_previous_retirement_coverage,
203 p_frozen_service => p_frozen_service,
204 p_action => l_action );
205
206 map_mtv_to_people_ei5(
207 p_transfer_name => p_transfer_name,
208 p_effective_date => p_effective_date,
209 p_person_id => l_person_id,
210 p_creditable_military_service => p_creditable_military_service,
211 p_action => l_action );
212
213 map_mtv_to_position_ei1(
214 p_transfer_name => p_transfer_name,
215 p_person_id => l_person_id,
216 p_effective_date => p_effective_date,
217 p_flsa_category => p_flsa_category,
218 p_bargaining_unit_status => p_bargaining_unit_status,
219 p_functional_class => p_functional_class,
220 p_position_working_title => p_position_working_title,
221 p_supervisory_status => p_supervisory_status,
222 p_action => l_action );
223
224 map_mtv_to_position_ei2(
225 p_transfer_name => p_transfer_name,
226 p_person_id => l_person_id,
227 p_effective_date => p_effective_date,
228 p_position_occupied => p_position_occupied,
229 p_appropriation_code1 => p_appropriation_code1,
230 p_appropriation_code2 => p_appropriation_code2,
231 p_action => l_action );
232
233 map_mtv_to_element_entries(
234 p_transfer_name => p_transfer_name,
235 p_person_id => l_person_id,
236 p_effective_date => p_effective_date,
237 p_total_salary => p_total_salary,
238 p_basic_salary_rate => p_basic_salary_rate,
239 p_locality_adjustment => p_locality_adjustment,
240 p_adjusted_basic_pay => p_adjusted_basic_pay,
241 p_other_pay => p_other_pay,
242 p_fegli => p_fegli,
243 p_retirement_plan => p_retirement_plan,
244 p_retention_allowance => p_retention_allowance,
245 p_staffing_differential => p_staffing_differential,
246 p_supervisory_differential => p_supervisory_differential,
247 p_wgi_date_due => p_wgi_date_due,
248 p_fegli_desc => p_fegli_desc,
249 p_retirement_plan_desc => p_retirement_plan_desc,
250 p_au_overtime => p_au_overtime,
251 p_availability_pay => p_availability_pay,
252 p_auo_premium_pay_indicator => p_auo_premium_pay_indicator,
253 p_ap_premium_pay_indicator => p_ap_premium_pay_indicator,
254 -- Changes 4093771
255 p_to_total_salary => p_to_total_salary,
256 p_to_basic_salary_rate => p_to_basic_salary_rate ,
257 p_to_adjusted_basic_pay => p_to_adjusted_basic_pay ,
258 -- End Changes 4093771
259 p_action => l_action );
260
261 map_mtv_to_misc(
262 p_transfer_name => p_transfer_name,
263 p_person_id => l_person_id,
264 p_effective_date => p_effective_date,
265 p_to_position_id => p_to_position_id,
266 p_from_grade_or_level => p_from_grade_or_level,
267 p_from_pay_plan => p_from_pay_plan,
268 p_from_position_title => p_from_position_title,
269 p_from_position_seq_num => p_from_position_seq_num ,
270 p_duty_station_code => p_duty_station_code,
271 p_duty_station_desc => p_duty_station_desc,
272 --VSM
273 p_from_agency_code => p_agency_code_transfer_from,
274 p_action => l_action );
275
276 map_mtv_to_assign_ei1(
277 p_transfer_name => p_transfer_name,
278 p_person_id => l_person_id,
279 p_effective_date => p_effective_date,
280 p_from_step_or_rate => p_from_step_or_rate,
281 p_tenure => p_tenure,
282 p_annuitant_indicator => p_annuitant_indicator,
283 p_pay_rate_determinant => p_pay_rate_determinant,
284 p_work_schedule => p_work_schedule,
285 p_part_time_hours => p_part_time_hours,
286 p_action => l_action );
287
288 map_mtv_to_assign_ei2(
289 p_transfer_name => p_transfer_name,
290 p_person_id => l_person_id,
291 p_effective_date => p_effective_date,
292 p_date_arrivd_personnel_office => p_date_arrivd_personnel_office,
293 p_non_disclosure_agmt_status => p_non_disclosure_agmt_status,
294 p_part_time_indicator => p_part_time_indicator,
295 p_qualif_standards_waiver => p_qualif_standards_waiver,
296 p_action => l_action );
297
298 --Begin Bug# 8724192
299 map_mtv_to_assign_ei3(
300 p_transfer_name => p_transfer_name,
301 p_person_id => l_person_id,
302 p_effective_date => p_effective_date,
303 p_assignment_nte_start_date => p_assignment_nte_start_date,
304 p_assignment_nte => p_assignment_nte,
305 p_action => l_action );
306 --End Bug# 8724192
307
308 map_mtv_to_special_info(
309 p_transfer_name => p_transfer_name,
310 p_effective_date => p_effective_date,
311 p_person_id => l_person_id,
312 p_education_level => p_education_level,
313 p_academic_discipline => p_academic_discipline,
314 p_year_degree_attained => p_year_degree_attained,
315 p_action => l_action );
316
317 EXCEPTION
318 WHEN others THEN
319 -- Reset IN OUT parameters and set OUT parameters
320
321
322 p_person_id :=l_person_id_nc;
323 RAISE;
324
325 end;
326
327 procedure map_mtv_to_people_f(
328 p_transfer_name IN varchar2,
329 p_inter_bg_transfer IN varchar2,
330 p_effective_date IN date,
331 p_person_id IN number,
332 p_date_of_birth IN date,
333 p_effective_end_date IN date,
334 p_effective_start_date IN date,
335 p_first_name IN varchar2,
336 p_full_name IN varchar2,
337 p_last_name IN varchar2,
338 p_marital_status IN varchar2,
339 p_middle_names IN varchar2,
340 p_national_identifier IN varchar2,
341 p_nationality IN varchar2,
342 p_rehire_reason IN varchar2,
343 p_sex IN varchar2,
344 p_start_date IN date,
345 p_title IN varchar2,
346 p_work_telephone IN varchar2,
347 p_action IN varchar2 ) is
348
349 l_people_f_rec per_all_people_f%ROWTYPE;
350
351 begin
352 l_people_f_rec.PERSON_ID := p_person_id;
353 l_people_f_rec.EFFECTIVE_START_DATE := p_effective_start_date;
354 l_people_f_rec.EFFECTIVE_END_DATE := p_effective_end_date;
355 l_people_f_rec.LAST_NAME := p_last_name;
356 l_people_f_rec.START_DATE := p_start_date;
357 l_people_f_rec.DATE_OF_BIRTH := p_date_of_birth;
358 l_people_f_rec.FIRST_NAME := p_first_name;
359 l_people_f_rec.FULL_NAME := p_full_name;
360 l_people_f_rec.MARITAL_STATUS := p_marital_status;
361 l_people_f_rec.MIDDLE_NAMES := p_middle_names;
362 l_people_f_rec.NATIONALITY := p_nationality;
363 l_people_f_rec.NATIONAL_IDENTIFIER := p_national_identifier;
364 l_people_f_rec.SEX := p_sex;
365 l_people_f_rec.TITLE := p_title;
366 l_people_f_rec.WORK_TELEPHONE := p_work_telephone;
367 l_people_f_rec.REHIRE_REASON := p_rehire_reason;
368
369 if p_action = 'INSERT' then
370 ghr_mto_int.insert_people_f
371 ( p_transfer_name => p_transfer_name,
372 p_inter_bg_transfer => p_inter_bg_transfer,
373 p_effective_date => p_effective_date,
374 ppf => l_people_f_rec);
375 else
376 ghr_mto_int.update_people_f
377 ( p_transfer_name => p_transfer_name,
378 p_inter_bg_transfer => p_inter_bg_transfer,
379 p_effective_date => p_effective_date,
380 ppf => l_people_f_rec);
381 end if;
382 end;
383
384 procedure map_mtv_to_people_ei1(
385 p_transfer_name IN varchar2,
386 p_effective_date IN date,
387 p_person_id IN number,
388 p_citizenship IN varchar2,
389 p_veterans_preference IN varchar2,
390 p_veterans_preference_for_RIF IN varchar2,
391 p_veterans_status IN varchar2,
392 p_action IN varchar2 ) is
393
394 l_people_ei1_rec per_people_extra_info%ROWTYPE;
395
396 begin
397 l_people_ei1_rec.person_id := p_person_id;
398 l_people_ei1_rec.INFORMATION_TYPE := 'GHR_US_PER_SF52';
399 l_people_ei1_rec.PEI_INFORMATION_CATEGORY := 'GHR_US_PER_SF52';
400 l_people_ei1_rec.PEI_INFORMATION3 := p_citizenship;
401 l_people_ei1_rec.PEI_INFORMATION4 := p_veterans_preference;
402 l_people_ei1_rec.PEI_INFORMATION5 := p_veterans_preference_for_RIF;
403 l_people_ei1_rec.PEI_INFORMATION6 := p_veterans_status;
404
405 if p_action = 'INSERT' then
406 ghr_mto_int.insert_people_ei
407 ( p_transfer_name => p_transfer_name,
408 p_effective_date => p_effective_date,
409 pp_ei => l_people_ei1_rec);
410 else
411 ghr_mto_int.update_people_ei
412 ( p_transfer_name => p_transfer_name,
413 p_effective_date => p_effective_date,
414 pp_ei => l_people_ei1_rec);
415 end if;
416 end;
417
418 procedure map_mtv_to_people_ei2(
419 p_transfer_name IN varchar2,
420 p_effective_date IN date,
421 p_person_id IN number,
422 p_appointment_type IN varchar2,
423 p_type_of_employment IN varchar2,
424 p_race_or_national_origin IN varchar2,
425 -- p_agency_code_transfer_from IN varchar2,
426 p_orig_appointment_auth_code_1 IN varchar2,
427 p_orig_appt_auth_code_1_desc IN varchar2, --Bug# 8724192
428 p_orig_appointment_auth_code_2 IN varchar2,
429 p_orig_appt_auth_code_2_desc IN varchar2, --Bug# 8724192
430 p_handicap_code IN varchar2,
431 p_action IN varchar2 ) is
432
433 l_people_ei2_rec per_people_extra_info%ROWTYPE;
434
435 begin
436 l_people_ei2_rec.person_id := p_person_id;
437 l_people_ei2_rec.INFORMATION_TYPE := 'GHR_US_PER_GROUP1';
438 l_people_ei2_rec.PEI_INFORMATION_CATEGORY := 'GHR_US_PER_GROUP1';
439 l_people_ei2_rec.PEI_INFORMATION3 := p_appointment_type;
440 l_people_ei2_rec.PEI_INFORMATION4 := p_type_of_employment;
441 l_people_ei2_rec.PEI_INFORMATION5 := p_race_or_national_origin;
442 -- l_people_ei2_rec.PEI_INFORMATION7 := p_agency_code_transfer_from;
443 l_people_ei2_rec.PEI_INFORMATION8 := p_orig_appointment_auth_code_1;
444 l_people_ei2_rec.PEI_INFORMATION9 := p_orig_appointment_auth_code_2;
445 l_people_ei2_rec.PEI_INFORMATION11 := p_handicap_code;
446 l_people_ei2_rec.PEI_INFORMATION22 := p_orig_appt_auth_code_1_desc;--Bug# 8724192
447 l_people_ei2_rec.PEI_INFORMATION23 := p_orig_appt_auth_code_2_desc;--Bug# 8724192
448
449 if p_action = 'INSERT' then
450 ghr_mto_int.insert_people_ei
451 ( p_transfer_name => p_transfer_name,
452 p_effective_date => p_effective_date,
453 pp_ei => l_people_ei2_rec);
454 else
455 ghr_mto_int.update_people_ei
456 ( p_transfer_name => p_transfer_name,
457 p_effective_date => p_effective_date,
458 pp_ei => l_people_ei2_rec);
459 end if;
460 end;
461
462 procedure map_mtv_to_people_ei3(
463 p_transfer_name IN varchar2,
464 p_effective_date IN date,
465 p_person_id IN number,
466 p_service_comp_date IN date,
467 p_action IN varchar2 ) is
468
469 l_people_ei3_rec per_people_extra_info%ROWTYPE;
470
471 begin
472 l_people_ei3_rec.person_id := p_person_id;
473 l_people_ei3_rec.INFORMATION_TYPE := 'GHR_US_PER_SCD_INFORMATION';
474 l_people_ei3_rec.PEI_INFORMATION_CATEGORY := 'GHR_US_PER_SCD_INFORMATION';
475 ----l_people_ei3_rec.PEI_INFORMATION3 := to_char(p_service_comp_date, 'DD-MON-YYYY');
476 l_people_ei3_rec.PEI_INFORMATION3 := fnd_date.date_to_canonical(p_service_comp_date);
477
478 if p_action = 'INSERT' then
479 ghr_mto_int.insert_people_ei
480 ( p_transfer_name => p_transfer_name,
481 p_effective_date => p_effective_date,
482 pp_ei => l_people_ei3_rec);
483 else
484 ghr_mto_int.update_people_ei
485 ( p_transfer_name => p_transfer_name,
486 p_effective_date => p_effective_date,
487 pp_ei => l_people_ei3_rec);
488 end if;
489 end;
490
491 procedure map_mtv_to_people_ei4(
492 p_transfer_name IN varchar2,
493 p_effective_date IN date,
494 p_person_id IN number,
495 p_fers_coverage IN VARCHAR2,
496 p_Previous_Retirement_Coverage IN varchar2,
497 p_Frozen_Service IN varchar2,
498 p_action IN varchar2 ) is
499
500 l_people_ei4_rec per_people_extra_info%ROWTYPE;
501
502 begin
503 l_people_ei4_rec.person_id := p_person_id;
504 l_people_ei4_rec.INFORMATION_TYPE := 'GHR_US_PER_SEPARATE_RETIRE';
505 l_people_ei4_rec.PEI_INFORMATION_CATEGORY := 'GHR_US_PER_SEPARATE_RETIRE';
506 -- Bug 2412656 Added FERS Coverage
507 l_people_ei4_rec.PEI_INFORMATION3 := p_fers_coverage;
508 l_people_ei4_rec.PEI_INFORMATION4 := p_Previous_Retirement_Coverage;
509 l_people_ei4_rec.PEI_INFORMATION5 := p_Frozen_Service;
510
511 if p_action = 'INSERT' then
512 ghr_mto_int.insert_people_ei
513 ( p_transfer_name => p_transfer_name,
514 p_effective_date => p_effective_date,
515 pp_ei => l_people_ei4_rec);
516 else
517 ghr_mto_int.update_people_ei
518 ( p_transfer_name => p_transfer_name,
519 p_effective_date => p_effective_date,
520 pp_ei => l_people_ei4_rec);
521 end if;
522 end;
523
524 procedure map_mtv_to_people_ei5(
525 p_transfer_name IN varchar2,
526 p_effective_date IN date,
527 p_person_id IN number,
528 p_Creditable_Military_Service IN varchar2,
529 p_action IN varchar2 ) is
530
531 l_people_ei5_rec per_people_extra_info%ROWTYPE;
532
533 begin
534 l_people_ei5_rec.person_id := p_person_id;
535 l_people_ei5_rec.INFORMATION_TYPE := 'GHR_US_PER_UNIFORMED_SERVICES';
536 l_people_ei5_rec.PEI_INFORMATION_CATEGORY := 'GHR_US_PER_UNIFORMED_SERVICES';
537 l_people_ei5_rec.PEI_INFORMATION5 := p_Creditable_Military_Service;
538
539 if p_action = 'INSERT' then
540 ghr_mto_int.insert_people_ei
541 ( p_transfer_name => p_transfer_name,
542 p_effective_date => p_effective_date,
543 pp_ei => l_people_ei5_rec);
544 else
545 ghr_mto_int.update_people_ei
546 ( p_transfer_name => p_transfer_name,
547 p_effective_date => p_effective_date,
548 pp_ei => l_people_ei5_rec);
549 end if;
550 end;
551
552 procedure map_mtv_to_position_ei1(
553 p_transfer_name IN varchar2,
554 p_person_id IN number,
555 p_effective_date IN date,
556 p_flsa_category IN varchar2,
557 p_bargaining_unit_status IN varchar2,
558 p_functional_class IN varchar2,
559 p_position_working_title IN varchar2,
560 p_supervisory_status IN varchar2,
561 p_action IN varchar2 ) is
562
563 l_position_ei1_rec per_position_extra_info%ROWTYPE;
564
565 begin
566 l_position_ei1_rec.INFORMATION_TYPE := 'GHR_US_POS_GRP1';
567 l_position_ei1_rec.POEI_INFORMATION_CATEGORY:= 'GHR_US_POS_GRP1';
568 l_position_ei1_rec.POEI_INFORMATION7 := p_flsa_category;
569 l_position_ei1_rec.POEI_INFORMATION8 := p_bargaining_unit_status;
570 l_position_ei1_rec.POEI_INFORMATION11 := p_functional_class;
571 l_position_ei1_rec.POEI_INFORMATION12 := p_position_working_title;
572 l_position_ei1_rec.POEI_INFORMATION16 := p_supervisory_status;
573
574 if p_action = 'INSERT' then
575 l_position_ei1_rec.POSITION_ID := p_person_id;
576 ghr_mto_int.insert_position_ei
577 ( p_transfer_name => p_transfer_name,
578 p_person_id => p_person_id,
579 p_effective_date => p_effective_date,
580 p_pos_ei => l_position_ei1_rec);
581 else
582 ghr_mto_int.update_position_ei
583 ( p_transfer_name => p_transfer_name,
584 p_person_id => p_person_id,
585 p_effective_date => p_effective_date,
586 p_pos_ei => l_position_ei1_rec);
587 end if;
588 end;
589
590 procedure map_mtv_to_position_ei2(
591 p_transfer_name IN varchar2,
592 p_person_id IN number,
593 p_effective_date IN date,
594 p_position_occupied IN varchar2,
595 p_appropriation_code1 IN varchar2,
596 p_appropriation_code2 IN varchar2,
597 p_action IN varchar2 ) is
598
599 l_position_ei2_rec per_position_extra_info%ROWTYPE;
600
601 begin
602 l_position_ei2_rec.INFORMATION_TYPE := 'GHR_US_POS_GRP2';
603 l_position_ei2_rec.POEI_INFORMATION_CATEGORY:= 'GHR_US_POS_GRP2';
604 l_position_ei2_rec.POEI_INFORMATION3 := p_position_occupied;
605 l_position_ei2_rec.POEI_INFORMATION13 := p_appropriation_code1;
606 l_position_ei2_rec.POEI_INFORMATION14 := p_appropriation_code2;
607
608 if p_action = 'INSERT' then
609 l_position_ei2_rec.POSITION_ID := p_person_id;
610 ghr_mto_int.insert_position_ei
611 ( p_transfer_name => p_transfer_name,
612 p_person_id => p_person_id,
613 p_effective_date => p_effective_date,
614 p_pos_ei => l_position_ei2_rec);
615 else
616 ghr_mto_int.update_position_ei
617 ( p_transfer_name => p_transfer_name,
618 p_person_id => p_person_id,
619 p_effective_date => p_effective_date,
620 p_pos_ei => l_position_ei2_rec);
621 end if;
622 end;
623
624 procedure map_mtv_to_element_entries(
625 p_transfer_name IN varchar2,
626 p_person_id IN number,
627 p_effective_date IN date,
628 p_total_salary IN number,
629 p_basic_salary_rate IN number,
630 p_locality_adjustment IN number,
631 p_adjusted_basic_pay IN number,
632 p_other_pay IN number,
633 p_fegli IN varchar2,
634 p_retirement_plan IN varchar2,
635 p_retention_allowance IN number,
636 p_staffing_differential IN number,
637 p_supervisory_differential IN number,
638 p_wgi_date_due IN date,
639 p_fegli_desc IN varchar2,
640 p_retirement_plan_desc IN varchar2,
641 p_au_overtime IN number,
642 p_availability_pay IN number,
643 p_auo_premium_pay_indicator IN varchar2,
644 p_ap_premium_pay_indicator IN varchar2,
645 p_to_total_salary IN number,
646 p_to_basic_salary_rate IN number,
647 p_to_adjusted_basic_pay IN number,
648 p_action IN varchar2 ) is
649
650 l_ghr_mt_ele_rec ghr_mt_element_entries_v%ROWTYPE;
651
652 begin
653 l_ghr_mt_ele_rec.total_salary := p_total_salary;
654 l_ghr_mt_ele_rec.salary := p_basic_salary_rate;
655 l_ghr_mt_ele_rec.locality_adjustment := p_locality_adjustment;
656 l_ghr_mt_ele_rec.adjusted_pay := p_adjusted_basic_pay;
657 l_ghr_mt_ele_rec.other_pay := p_other_pay;
658 l_ghr_mt_ele_rec.fegli := p_fegli;
659 l_ghr_mt_ele_rec.retirement_plan := p_retirement_plan;
660 l_ghr_mt_ele_rec.retention_allowance := p_retention_allowance;
661 l_ghr_mt_ele_rec.staffing_differential := p_staffing_differential;
662 l_ghr_mt_ele_rec.supervisory_differential := p_supervisory_differential;
663 -- l_ghr_mt_ele_rec.wgi_date_due := to_char(p_wgi_date_due, 'DD-MON-YYYY');
664 l_ghr_mt_ele_rec.wgi_date_due := fnd_date.date_to_canonical(p_wgi_date_due);
665 l_ghr_mt_ele_rec.fegli_meaning := p_fegli_desc;
666 l_ghr_mt_ele_rec.retirement_plan_meaning := p_retirement_plan_desc;
667 l_ghr_mt_ele_rec.auo_amount := p_au_overtime;
668 -- Bug#4151183 Assigned wrong values to AUO Prem. Pay Ind and AP Amount. Assigned Correct values now.
669 l_ghr_mt_ele_rec.auo_premium_pay_ind := p_auo_premium_pay_indicator;
670 l_ghr_mt_ele_rec.ap_amount := p_availability_pay;
671 -- Bug#4151183 End of Fix
672 l_ghr_mt_ele_rec.ap_premium_pay_ind := p_ap_premium_pay_indicator;
673
674 -- Changes 4093771
675 l_ghr_mt_ele_rec.to_basic_salary_rate := p_to_basic_salary_rate;
676 l_ghr_mt_ele_rec.to_total_salary := p_to_total_salary;
677 l_ghr_mt_ele_rec.to_adjusted_basic_pay := p_to_adjusted_basic_pay;
678 -- End changes 4093771
679
680 if p_action = 'INSERT' then
681 ghr_mto_int.insert_element_entries
682 ( p_transfer_name => p_transfer_name,
683 p_person_id => p_person_id,
684 p_effective_date => p_effective_date,
685 p_element => l_ghr_mt_ele_rec);
686 else
687 ghr_mto_int.update_element_entries
688 ( p_transfer_name => p_transfer_name,
689 p_person_id => p_person_id,
690 p_effective_date => p_effective_date,
691 p_element => l_ghr_mt_ele_rec);
692 end if;
693 end;
694
695 procedure map_mtv_to_misc(
696 p_transfer_name IN varchar2,
697 p_person_id IN number,
698 p_effective_date IN date,
699 p_to_position_id IN number,
700 p_from_grade_or_level IN varchar2,
701 p_from_pay_plan IN varchar2,
702 p_from_position_title IN varchar2,
703 p_from_position_seq_num IN number,
704 p_duty_station_code IN varchar2,
705 p_duty_station_desc IN varchar2,
706 p_from_agency_code IN varchar2,
707 p_action IN varchar2 ) is
708
709 l_ghr_mt_misc_rec ghr_mt_misc_v%ROWTYPE;
710
711 begin
712
713 l_ghr_mt_misc_rec.to_position_id := p_to_position_id;
714 l_ghr_mt_misc_rec.from_grade_or_level := p_from_grade_or_level;
715 l_ghr_mt_misc_rec.from_pay_plan := p_from_pay_plan;
716 l_ghr_mt_misc_rec.from_position_title := p_from_position_title;
717 l_ghr_mt_misc_rec.from_position_seq_num := p_from_position_seq_num;
718 l_ghr_mt_misc_rec.duty_station_code := p_duty_station_code;
719 l_ghr_mt_misc_rec.duty_station_desc := p_duty_station_desc;
720 l_ghr_mt_misc_rec.from_agency_code := p_from_agency_code;
721
722 if p_action = 'INSERT' then
723 ghr_mto_int.insert_misc
724 ( p_transfer_name => p_transfer_name,
725 p_person_id => p_person_id,
726 p_effective_date => p_effective_date,
727 p_misc => l_ghr_mt_misc_rec);
728 else
729 ghr_mto_int.update_misc
730 ( p_transfer_name => p_transfer_name,
731 p_person_id => p_person_id,
732 p_effective_date => p_effective_date,
733 p_misc => l_ghr_mt_misc_rec);
734 end if;
735 end;
736
737
738 procedure map_mtv_to_assign_ei1(
739 p_transfer_name IN varchar2,
740 p_person_id IN number,
741 p_effective_date IN date,
742 p_from_step_or_rate IN varchar2,
743 p_tenure IN varchar2,
744 p_annuitant_indicator IN varchar2,
745 p_pay_rate_determinant IN varchar2,
746 p_work_schedule IN varchar2,
747 p_part_time_hours IN number,
748 p_action IN varchar2 ) is
749
750 l_assign_ei1_rec per_assignment_extra_info%ROWTYPE;
751
752 begin
753 l_assign_ei1_rec.INFORMATION_TYPE := 'GHR_US_ASG_SF52';
754 l_assign_ei1_rec.AEI_INFORMATION_CATEGORY := 'GHR_US_ASG_SF52';
755 l_assign_ei1_rec.AEI_INFORMATION3 := p_from_step_or_rate;
756 l_assign_ei1_rec.AEI_INFORMATION4 := p_tenure;
757 l_assign_ei1_rec.AEI_INFORMATION5 := p_annuitant_indicator;
758 l_assign_ei1_rec.AEI_INFORMATION6 := p_pay_rate_determinant;
759 l_assign_ei1_rec.AEI_INFORMATION7 := p_work_schedule;
760 l_assign_ei1_rec.AEI_INFORMATION8 := p_part_time_hours;
761
762 if p_action = 'INSERT' then
763 l_assign_ei1_rec.ASSIGNMENT_ID := p_person_id;
764 ghr_mto_int.insert_assignment_ei
765 ( p_transfer_name => p_transfer_name,
766 p_person_id => p_person_id,
767 p_effective_date => p_effective_date,
768 p_a_ei => l_assign_ei1_rec);
769 else
770 ghr_mto_int.update_assignment_ei
771 ( p_transfer_name => p_transfer_name,
772 p_person_id => p_person_id,
773 p_effective_date => p_effective_date,
774 p_a_ei => l_assign_ei1_rec);
775 end if;
776 end;
777
778 procedure map_mtv_to_assign_ei2(
779 p_transfer_name IN varchar2,
780 p_person_id IN number,
781 p_effective_date IN date,
782 p_date_arrivd_personnel_office IN date,
783 p_non_disclosure_agmt_status IN varchar2,
784 p_part_time_indicator IN varchar2,
785 p_qualif_standards_waiver IN varchar2,
786 p_action IN varchar2 ) is
787
788 l_assign_ei2_rec per_assignment_extra_info%ROWTYPE;
789
790 begin
791 l_assign_ei2_rec.INFORMATION_TYPE := 'GHR_US_ASG_NON_SF52';
792 l_assign_ei2_rec.AEI_INFORMATION_CATEGORY := 'GHR_US_ASG_NON_SF52';
793 -- l_assign_ei2_rec.AEI_INFORMATION3 := to_char(p_date_arrivd_personnel_office, 'DD-MON-YYYY');
794 l_assign_ei2_rec.AEI_INFORMATION3 := fnd_date.date_to_canonical(p_date_arrivd_personnel_office);
795 l_assign_ei2_rec.AEI_INFORMATION6 := p_non_disclosure_agmt_status;
796 l_assign_ei2_rec.AEI_INFORMATION8 := p_part_time_indicator;
797 l_assign_ei2_rec.AEI_INFORMATION9 := p_qualif_standards_waiver;
798
799 if p_action = 'INSERT' then
800 l_assign_ei2_rec.ASSIGNMENT_ID := p_person_id;
801 ghr_mto_int.insert_assignment_ei
802 ( p_transfer_name => p_transfer_name,
803 p_person_id => p_person_id,
804 p_effective_date => p_effective_date,
805 p_a_ei => l_assign_ei2_rec);
806 else
807 ghr_mto_int.update_assignment_ei
808 ( p_transfer_name => p_transfer_name,
809 p_person_id => p_person_id,
810 p_effective_date => p_effective_date,
811 p_a_ei => l_assign_ei2_rec);
812 end if;
813 end;
814
815 --Begin Bug# 8724192
816 procedure map_mtv_to_assign_ei3(
817 p_transfer_name IN varchar2,
818 p_person_id IN number,
819 p_effective_date IN date,
820 p_assignment_nte_start_date IN date,
821 p_assignment_nte IN date,
822 p_action IN varchar2 ) is
823
824 l_assign_ei3_rec per_assignment_extra_info%ROWTYPE;
825
826 begin
827 l_assign_ei3_rec.INFORMATION_TYPE := 'GHR_US_ASG_NTE_DATES';
828 l_assign_ei3_rec.AEI_INFORMATION_CATEGORY := 'GHR_US_ASG_NTE_DATES';
829 l_assign_ei3_rec.AEI_INFORMATION3 := fnd_date.date_to_canonical(p_assignment_nte_start_date);
830 l_assign_ei3_rec.AEI_INFORMATION4 := fnd_date.date_to_canonical(p_assignment_nte);
831
832 if p_action = 'INSERT' then
833 l_assign_ei3_rec.ASSIGNMENT_ID := p_person_id;
834 ghr_mto_int.insert_assignment_ei
835 ( p_transfer_name => p_transfer_name,
836 p_person_id => p_person_id,
837 p_effective_date => p_effective_date,
838 p_a_ei => l_assign_ei3_rec);
839 else
840 ghr_mto_int.update_assignment_ei
841 ( p_transfer_name => p_transfer_name,
842 p_person_id => p_person_id,
843 p_effective_date => p_effective_date,
844 p_a_ei => l_assign_ei3_rec);
845 end if;
846 end;
847 --End Bug# 8724192
848 procedure map_mtv_to_special_info(
849 p_transfer_name IN varchar2,
850 p_effective_date IN date,
851 p_person_id IN number,
852 p_education_level IN varchar2,
853 p_academic_discipline IN varchar2,
854 p_year_degree_attained IN varchar2,
855 p_action IN varchar2 ) is
856
857 l_flex_name varchar2(150) := 'US Fed Education';
858 l_si ghr_api.special_information_type;
859
860 begin
861
862 l_si.SEGMENT1 := p_education_level;
863 l_si.SEGMENT2 := p_academic_discipline;
864 l_si.SEGMENT3 := p_year_degree_attained;
865
866 if p_action = 'INSERT' then
867 ghr_mto_int.insert_special_info(
868 p_transfer_name => p_transfer_name,
869 p_effective_date => p_effective_date,
870 p_person_id => p_person_id,
871 p_flex_name => l_flex_name,
872 p_si => l_si );
873 end if;
874 if p_education_level is not null or
875 p_academic_discipline is not null or
876 p_year_degree_attained is not null then
877 ghr_mto_int.update_special_info(
878 p_transfer_name => p_transfer_name,
879 p_effective_date => p_effective_date,
880 p_person_id => p_person_id,
881 p_flex_name => l_flex_name,
882 p_si => l_si );
883
884 end if;
885
886 end;
887
888 procedure map_mtv_to_assign_f(
889 p_transfer_name IN varchar2,
890 p_effective_date IN date,
891 p_assignment_id IN number) is
892
893 l_assign_f_rec per_all_assignments_f%ROWTYPE;
894
895 begin
896 l_assign_f_rec.ASSIGNMENT_ID := p_assignment_id;
897 l_assign_f_rec.PERSON_ID := p_assignment_id;
898
899 ghr_mto_int.insert_assignment_f(
900 p_transfer_name => p_transfer_name,
901 p_effective_date => p_effective_date,
902 p_a => l_assign_f_rec );
903 end;
904
905 procedure map_mtv_to_position(
906 p_transfer_name IN varchar2,
907 p_person_id IN number,
908 p_effective_date IN date,
909 p_position_id IN number) is
910
911 l_position_rec hr_all_positions_f%ROWTYPE;
912
913 begin
914 l_position_rec.position_id := p_position_id;
915
916 ghr_mto_int.insert_position(
917 p_transfer_name => p_transfer_name,
918 p_person_id => p_person_id,
919 p_effective_date => p_effective_date,
920 p_pos => l_position_rec);
921 end;
922
923 Procedure Update_Process_Flag(
924 p_transfer_name in varchar2,
925 p_include_error in varchar2,
926 p_override_prev_selection in varchar2,
927 p_value in varchar2) is
928
929 Begin
930 update ghr_mt_people_f_v
931 set mt_status = p_value
932 where mt_name = p_transfer_name
933 and nvl(mt_Status, ' ') in (' ', decode(p_include_error, 'Y', 'E', ' '),
934 decode(p_override_prev_selection, 'Y', decode(p_value, 'Y', 'N', 'Y'), ' '));
935 commit;
936 End;
937
938 function Submit_MTI_Request (
939 P_DESCRIPTION IN VARCHAR2,
940 P_ARGUMENT1 IN VARCHAR2,
941 P_ARGUMENT2 IN VARCHAR2)
942 RETURN NUMBER IS
943 BEGIN
944 return (fnd_request.submit_request(
945 APPLICATION => 'GHR'
946 ,PROGRAM => 'SUBMIT_MASS_TRANSFER_IN'
947 ,DESCRIPTION => p_description
948 ,START_TIME => SYSDATE
949 ,SUB_REQUEST => FALSE
950 ,ARGUMENT1 => p_argument1
951 ,ARGUMENT2 => p_argument2
952 ));
953
954 end;
955
956 FUNCTION get_lookup_meaning(
957 p_lookup_type hr_lookups.lookup_type%TYPE
958 ,p_lookup_code hr_lookups.lookup_code%TYPE)
959 RETURN VARCHAR2 IS
960
961 l_ret_val hr_lookups.meaning%TYPE := NULL;
962
963 CURSOR cur_loc IS
964 SELECT loc.meaning
965 FROM hr_lookups loc
966 WHERE
967 loc.lookup_type = p_lookup_type
968 AND loc.lookup_code = p_lookup_code;
969
970 BEGIN
971 FOR cur_loc_rec IN cur_loc LOOP
972 l_ret_val := cur_loc_rec.meaning;
973 END LOOP;
974
975 RETURN(l_ret_val);
976
977 end;
978
979 Procedure set_process_flag(
980 p_mt_name in varchar2,
981 p_mt_person_id in number,
982 p_mt_status in varchar2) is
983 Begin
984
985 update ghr_mt_people_f_v
986 set mt_status = p_mt_status
987 where mt_name = p_mt_name and
988 mt_person_id = p_mt_person_id;
989
990 end set_process_flag;
991
992 end ghr_mti_int_insert;