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