[Home] [Help]
PACKAGE BODY: APPS.GHR_MTO_INT
Source
1 PACKAGE BODY ghr_mto_int AS
2 /* $Header: ghmtoint.pkb 120.8 2011/06/13 09:16:13 utokachi ship $ */
3
4 g_datefmt constant varchar2(12) := 'DD-MON-YYYY';
5 g_name constant varchar2(12) := 'GHR_MTO_INT';
6 g_log_name varchar2(30);
7 g_message varchar2(128); -- 128 max for hr_utility.set_location
8
9 -- Created g_new_line to use instead of CHR(10)
10 g_new_line varchar2(1) := substr('
11 ',1,1);
12
13 --
14 -- Directs logging messages to hr_utility.set_location unless the global
15 -- variable, g_dbms_output, is TRUE, then output goes to dbms_output
16 --
17 -- g_dbms_output is FALSE by default
18 --
19 procedure put_line(p_message varchar2 default null) is
20
21 begin
22 if g_dbms_output = TRUE then
23 null;
24 --dbms_output.put_line(g_message||p_message);
25 else
26 hr_utility.set_location(substr(g_message||p_message,128),33);
27 end if;
28
29 g_message := null;
30
31 exception
32 when others then
33 null;
34 end;
35
36 procedure put(p_message varchar2) is
37 begin
38 g_message := substr(g_message||p_message,128);
39
40 exception
41 when others then
42 null;
43 end;
44
45 function row_exists (p_transfer_name varchar2,
46 p_person_id number,
47 p_table_name varchar2,
48 p_info_type varchar2 default null,
49 p_contact_name varchar2 default null)
50 return boolean is
51
52 l_count number;
53
54 -- if p_contact_name is null then it should not influence the
55 -- query result, otherwise it should identify a contact address
56 -- row regardless of the contact name. We want to have only one
57 -- contact address.
58 cursor cur_ck_unique is
59 select count(*)
60 from ghr_interface
61 where information4 = p_table_name -- mt_table_name
62 and information1 = p_transfer_name -- mt_name
63 and information3 = to_char(p_person_id) -- mt_person_id
64 and nvl(information5,'null') = nvl(p_info_type,'null')
65 and decode(information7,null,'null','notnull') =
66 decode(nvl(p_contact_name,information7),null,'null','notnull');
67
68 begin
69
70 open cur_ck_unique;
71 fetch cur_ck_unique into l_count;
72 close cur_ck_unique;
73
74 if l_count = 0 then
75 return FALSE;
76 else
77 return TRUE;
78 end if;
79
80 end;
81
82 --
83 -- set program name used by log_message
84 --
85 -- The concurrent program request id is appended to the
86 -- name passed and if the result is > 29 the name is truncated
87 --
88 -- If the procedure is not called by the concurrent manager then
89 -- the value ghr_process_log_s.nextval is used in place of the
90 -- request id
91 --
92 procedure set_log_program_name(p_log_program_name varchar2) is
93 l_prog_name varchar2(30);
94 l_id_len number;
95 l_name_len number;
96 l_proc varchar2(72) := g_package||'set_log_program_name';
97
98 cursor cur_get_seq is
99 select to_char(ghr_process_log_s.nextval)
100 from sys.dual;
101
102 begin
103 hr_utility.set_location('Entering:'||l_proc, 10);
104
105 l_prog_name := Fnd_profile.value('CONC_REQUEST_ID');
106
107 hr_utility.set_location('l_prog_name conc_request_id :' || l_prog_name ,11);
108 if l_prog_name = '-1' then
109 l_prog_name := NULL;
110 end if;
111
112 if l_prog_name is null then -- we're not called by concurrent mgr
113 open cur_get_seq;
114 fetch cur_get_seq into l_prog_name;
115 close cur_get_seq;
116 hr_utility.set_location('l_prog_name in if condn :' || l_prog_name ,12);
117 end if;
118
119
120 l_id_len := length(l_prog_name);
121 l_name_len := length(p_log_program_name);
122
123 if l_id_len + l_name_len + 1 > 30 then
124 l_prog_name := substr(p_log_program_name,1,30 - l_id_len - 1)||'_'||
125 l_prog_name;
126 else
127 l_prog_name := p_log_program_name||'_'||l_prog_name;
128 end if;
129
130 put_line('Log Program Name: '|| l_prog_name);
131
132 hr_utility.set_location('l_prog_name Final :' || l_prog_name ,13);
133
134 g_log_name := l_prog_name;
135
136 hr_utility.set_location('Leaving:'||l_proc, 20);
137
138 end;
139
140 procedure log_message(p_procedure varchar2, p_message varchar2)
141 is
142
143 l_proc varchar2(72) := g_package||'log_message';
144
145 begin
146 hr_utility.set_location('Entering:'||l_proc, 10);
147
148 if g_log_enabled = FALSE then
149 return;
150 end if;
151
152 -- call set_log_program_name to set g_log_name before first call
153 if g_log_name is null then
154 hr_utility.set_message(8301, 'GHR_38546_LOG_NAME_NOT_SET');
155 hr_utility.raise_error;
156 end if;
157
158 -- p_procedure must contain 30 or fewer characters
159 if length(p_procedure) > 30 then
160 hr_utility.set_message(8301, 'GHR_38547_NAME_TOO_LONG');
161 hr_utility.raise_error;
162 end if;
163 hr_utility.set_location('g_log_name :'||g_log_name, 11);
164 hr_utility.set_location('p_procedure :'||p_procedure, 11);
165
166 ghr_wgi_pkg.create_ghr_errorlog(
167 p_program_name => g_log_name,
168 p_message_name => p_procedure,
169 p_log_text => p_message,
170 p_log_date => sysdate
171 );
172
173 hr_utility.set_location('Leaving:'||l_proc, 10);
174 exception
175 when others then
176 put_line(l_proc||' - raised exception');
177 raise;
178 end;
179
180 procedure who_called_me(p_name out nocopy varchar2)
181 as
182 call_stack varchar2(4096) default dbms_utility.format_call_stack;
183 n number;
184 found_stack BOOLEAN default FALSE;
185 line varchar2(255);
186 cnt number := 0;
187
188 l_owner varchar2(30);
189 l_lineno number;
190 l_caller_t varchar2(30);
191
192 begin
193
194 loop
195 n := instr( call_stack, g_new_line );
196 exit when ( cnt = 3 or n is NULL or n = 0 );
197
198 line := substr( call_stack, 1, n-1 );
199 call_stack := substr( call_stack, n+1 );
200
201 if ( NOT found_stack ) then
202 if ( line like '%handle%number%name%' ) then
203 found_stack := TRUE;
204 end if;
205 else
206 cnt := cnt + 1;
207 -- cnt = 1 is this procedure, ME
208 -- cnt = 2 is MY Caller
209 -- cnt = 3 is Their Caller
210 if ( cnt = 3 ) then
211 -- Bug#4086677 Commented the following line.
212 -- l_lineno := to_number(substr( line, 13, 6 ));
213 line := substr( line, 21 );
214 if ( line like 'pr%' ) then
215 n := length( 'procedure ' );
216 elsif ( line like 'fun%' ) then
217 n := length( 'function ' );
218 elsif ( line like 'package body%' ) then
219 n := length( 'package body ' );
220 elsif ( line like 'pack%' ) then
221 n := length( 'package ' );
222 else
223 n := length( 'anonymous block ' );
224 end if;
225 l_caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
226 line := substr( line, n );
227 n := instr( line, '.' );
228 l_owner := ltrim(rtrim(substr( line, 1, n-1 )));
229 p_name := ltrim(rtrim(substr( line, n+1 )));
230 end if;
231 end if;
232 end loop;
233
234 end;
235
236 procedure get_transfer_parameters(p_mass_transfer_id in number,
237 p_transfer_name out nocopy varchar2,
238 p_effective_date out nocopy date,
239 p_inter_bg_transfer out nocopy varchar2) is
240
241 cursor cur_eff_date is
242 select effective_date, name, within_bg_transfer
243 from ghr_mass_transfers
244 where mass_transfer_id = p_mass_transfer_id;
245
246 l_effdate date;
247 l_name ghr_mass_transfers.name%type;
248 l_bg_tran ghr_mass_transfers.within_bg_transfer%type;
249 l_proc varchar2(72) := g_package||'get_transfer_parameters';
250
251 begin
252
253 open cur_eff_date;
254 fetch cur_eff_date into l_effdate, l_name, l_bg_tran;
255 close cur_eff_date;
256
257 p_effective_date := l_effdate;
258 p_transfer_name := l_name;
259 p_inter_bg_transfer := l_bg_tran;
260
261 exception
262 when others then
263 put_line(l_proc||' - raised exception');
264 raise;
265
266 end get_transfer_parameters;
267
268 procedure update_agency_transfer_from(p_person_id number,
269 p_effective_date date,
270 p_value varchar2) is
271
272 cursor cur_pei_id is
273 select person_extra_info_id, object_version_number
274 from per_people_extra_info
275 where person_id = p_person_id
276 and information_type = 'GHR_US_PER_GROUP1';
277
278 l_person_extra_info_id per_people_extra_info.person_extra_info_id%type;
279 l_object_version_number per_people_extra_info.object_version_number%type;
280
281 l_proc varchar2(72) := g_package||'update_agency_transfer_from';
282
283 begin
284
285 hr_utility.set_location('Entering:'||l_proc, 10);
286
287 open cur_pei_id;
288 fetch cur_pei_id into l_person_extra_info_id, l_object_version_number;
289
290 if cur_pei_id%notfound then -- create info type
291
292 ghr_person_extra_info_api.create_person_extra_info (
293 p_person_id => p_person_id
294 ,p_effective_date => p_effective_date
295 ,p_information_type => 'GHR_US_PER_GROUP1'
296 ,p_pei_information_category => 'GHR_US_PER_GROUP1'
297 ,p_pei_information7 => p_value
298 ,p_person_extra_info_id => l_person_extra_info_id
299 ,p_object_version_number => l_object_version_number
300 );
301
302 else
303
304 ghr_person_extra_info_api.update_person_extra_info
305 (p_person_extra_info_id => l_person_extra_info_id
306 ,p_object_version_number => l_object_version_number
307 ,p_effective_date => p_effective_date
308 ,p_pei_information7 => p_value);
309
310 end if;
311
312 -- update_person_extra_info() invalidates the person, so we need
313 -- to re-validate
314 --- Commented the following two lines to remove Validation functionality on Person.
315 -- ghr_validate_perwsepi.validate_perwsepi(p_person_id);
316 -- ghr_validate_perwsepi.update_person_user_type(p_person_id);
317
318 close cur_pei_id;
319 hr_utility.set_location('Leaving:'||l_proc, 10);
320
321 exception
322 when others then
323 put_line(l_proc||' - raised exception');
324 raise;
325
326 end;
327
328 --
329 -- all insert_% procedures defined in this package are public and
330 -- will enforce uniqueness of transfer_name and ppf.person_id
331 -- by raising an error when the row already exists.
332 --
333 -- When called by mass_transfer_out the uniqueness condition does not
334 -- need to be checked since mass_transfer_out deletes all rows in
335 -- the interface table for a person_id and transfer_name which already
336 -- exists
337 --
338 procedure insert_people_f(p_transfer_name varchar2,
339 p_inter_bg_transfer varchar2,
340 p_effective_date date,
341 ppf per_all_people_f%rowtype)
342 is
343
344 l_proc varchar2(72) := g_package||'insert_people_f';
345 l_table_name constant varchar2(30) := 'PER_PEOPLE_F';
346
347 l_owner varchar2(30);
348 l_name varchar2(30);
349 l_lineno number;
350 l_caller varchar2(30);
351
352 begin
353
354 put('insert people_f');
355
356 who_called_me(p_name => l_name);
357
358 -- if this package body did NOT call this procedure then ckeck if
359 -- the row to be inserted already exists, if so raise an error
360 if nvl(l_name,'unknown') <> g_name then
361 put_line(' - called by '|| l_name);
362 if row_exists(
363 p_transfer_name => p_transfer_name
364 ,p_person_id => ppf.person_id
365 ,p_table_name => l_table_name) then
366
367 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
368 hr_utility.raise_error;
369 end if;
370 else
371 put_line(' - called locally');
372 end if;
373
374 insert into ghr_mt_people_f_v (
375 ghr_interface_id,
376 MT_PROCESS_DATE, MT_SOURCE, MT_EFFECTIVE_DATE,
377 MT_NAME, MT_STATUS, MT_PERSON_ID,
378 MT_TABLE_NAME, MT_INFORMATION_TYPE,
379 MT_INTER_BG_TRANSFER, PERSON_ID,
380 EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID,
381 PERSON_TYPE_ID, LAST_NAME, START_DATE,
382 APPLICANT_NUMBER, BACKGROUND_CHECK_STATUS, BACKGROUND_DATE_CHECK,
383 BLOOD_TYPE, COMMENT_ID, CORRESPONDENCE_LANGUAGE,
384 CURRENT_APPLICANT_FLAG, CURRENT_EMP_OR_APL_FLAG,
385 CURRENT_EMPLOYEE_FLAG, DATE_EMPLOYEE_DATA_VERIFIED,
386 DATE_OF_BIRTH, EMAIL_ADDRESS, EMPLOYEE_NUMBER,
387 EXPENSE_CHECK_SEND_TO_ADDRESS, FAST_PATH_EMPLOYEE,
388 FIRST_NAME, FTE_CAPACITY, FULL_NAME,
389 HOLD_APPLICANT_DATE_UNTIL, HONORS, INTERNAL_LOCATION,
390 KNOWN_AS, LAST_MEDICAL_TEST_BY, LAST_MEDICAL_TEST_DATE,
391 MAILSTOP, MARITAL_STATUS, MIDDLE_NAMES, NATIONALITY,
392 NATIONAL_IDENTIFIER, OFFICE_NUMBER, ON_MILITARY_SERVICE,
393 ORDER_NAME, PRE_NAME_ADJUNCT, PREVIOUS_LAST_NAME,
394 PROJECTED_START_DATE, REHIRE_AUTHORIZOR, REHIRE_RECOMMENDATION,
395 RESUME_EXISTS, RESUME_LAST_UPDATED, REGISTERED_DISABLED_FLAG,
396 SECOND_PASSPORT_EXISTS, SEX, STUDENT_STATUS, SUFFIX,
397 TITLE, VENDOR_ID, WORK_SCHEDULE, WORK_TELEPHONE, REQUEST_ID,
398 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
399 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
400 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
401 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
402 ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
403 ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22,
404 ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26,
405 ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30,
406 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY,
407 CREATION_DATE, PER_INFORMATION_CATEGORY, PER_INFORMATION1,
408 PER_INFORMATION2, PER_INFORMATION3, PER_INFORMATION4,
409 PER_INFORMATION5, PER_INFORMATION6, PER_INFORMATION7,
410 PER_INFORMATION8, PER_INFORMATION9, PER_INFORMATION10,
411 PER_INFORMATION11, PER_INFORMATION12, PER_INFORMATION13,
412 PER_INFORMATION14, PER_INFORMATION15, PER_INFORMATION16,
413 PER_INFORMATION17, PER_INFORMATION18, PER_INFORMATION19,
414 PER_INFORMATION20, PER_INFORMATION21, PER_INFORMATION22,
415 PER_INFORMATION23, PER_INFORMATION24, PER_INFORMATION25,
416 PER_INFORMATION26, PER_INFORMATION27, PER_INFORMATION28,
417 PER_INFORMATION29, PER_INFORMATION30, OBJECT_VERSION_NUMBER,
418 DATE_OF_DEATH, REHIRE_REASON
419 )
420 values (
421 ghr_interface_s.nextval,
422 sysdate,
423 l_name,
424 p_effective_date,
425 p_transfer_name,
426 null, -- MT_STATUS,
427 ppf.person_id, -- MT_PERSON_ID,
428 l_table_name, -- MT_TABLE_NAME,
429 null, -- MT_INFORMATION_TYPE,
430 p_inter_bg_transfer, -- MT_INTER_BG_TRANSFER,
431 ppf.PERSON_ID,
432 to_char(ppf.EFFECTIVE_START_DATE,g_datefmt),
433 to_char(ppf.EFFECTIVE_END_DATE,g_datefmt),
434 ppf.BUSINESS_GROUP_ID,
435 ppf.PERSON_TYPE_ID,
436 ppf.LAST_NAME,
437 to_char(ppf.START_DATE,g_datefmt),
438 ppf.APPLICANT_NUMBER,
439 ppf.BACKGROUND_CHECK_STATUS,
440 to_char(ppf.BACKGROUND_DATE_CHECK,g_datefmt),
441 ppf.BLOOD_TYPE,
442 ppf.COMMENT_ID,
443 ppf.CORRESPONDENCE_LANGUAGE,
444 ppf.CURRENT_APPLICANT_FLAG,
445 ppf.CURRENT_EMP_OR_APL_FLAG,
446 ppf.CURRENT_EMPLOYEE_FLAG,
447 to_char(ppf.DATE_EMPLOYEE_DATA_VERIFIED,g_datefmt),
448 to_char(ppf.DATE_OF_BIRTH,g_datefmt),
449 ppf.EMAIL_ADDRESS,
450 ppf.EMPLOYEE_NUMBER,
451 ppf.EXPENSE_CHECK_SEND_TO_ADDRESS,
452 ppf.FAST_PATH_EMPLOYEE,
453 ppf.FIRST_NAME,
454 ppf.FTE_CAPACITY,
455 ppf.FULL_NAME,
456 to_char(ppf.HOLD_APPLICANT_DATE_UNTIL,g_datefmt),
457 ppf.HONORS,
458 ppf.INTERNAL_LOCATION,
459 ppf.KNOWN_AS,
460 ppf.LAST_MEDICAL_TEST_BY,
461 to_char(ppf.LAST_MEDICAL_TEST_DATE,g_datefmt),
462 ppf.MAILSTOP,
463 ppf.MARITAL_STATUS,
464 ppf.MIDDLE_NAMES,
465 ppf.NATIONALITY,
466 ppf.NATIONAL_IDENTIFIER,
467 ppf.OFFICE_NUMBER,
468 ppf.ON_MILITARY_SERVICE,
469 ppf.ORDER_NAME,
470 ppf.PRE_NAME_ADJUNCT,
471 ppf.PREVIOUS_LAST_NAME,
472 to_char(ppf.PROJECTED_START_DATE,g_datefmt),
473 ppf.REHIRE_AUTHORIZOR,
474 ppf.REHIRE_RECOMMENDATION,
475 ppf.RESUME_EXISTS,
476 to_char(ppf.RESUME_LAST_UPDATED,g_datefmt),
477 ppf.REGISTERED_DISABLED_FLAG,
478 ppf.SECOND_PASSPORT_EXISTS,
479 ppf.SEX,
480 ppf.STUDENT_STATUS,
481 ppf.SUFFIX,
482 ppf.TITLE,
483 ppf.VENDOR_ID,
484 ppf.WORK_SCHEDULE,
485 ppf.WORK_TELEPHONE,
486 ppf.REQUEST_ID,
487 ppf.PROGRAM_APPLICATION_ID,
488 ppf.PROGRAM_ID,
489 to_char(ppf.PROGRAM_UPDATE_DATE,g_datefmt),
490 ppf.ATTRIBUTE_CATEGORY,
491 ppf.ATTRIBUTE1,
492 ppf.ATTRIBUTE2,
493 ppf.ATTRIBUTE3,
494 ppf.ATTRIBUTE4,
495 ppf.ATTRIBUTE5,
496 ppf.ATTRIBUTE6,
497 ppf.ATTRIBUTE7,
498 ppf.ATTRIBUTE8,
499 ppf.ATTRIBUTE9,
500 ppf.ATTRIBUTE10,
501 ppf.ATTRIBUTE11,
502 ppf.ATTRIBUTE12,
503 ppf.ATTRIBUTE13,
504 ppf.ATTRIBUTE14,
505 ppf.ATTRIBUTE15,
506 ppf.ATTRIBUTE16,
507 ppf.ATTRIBUTE17,
508 ppf.ATTRIBUTE18,
509 ppf.ATTRIBUTE19,
510 ppf.ATTRIBUTE20,
511 ppf.ATTRIBUTE21,
512 ppf.ATTRIBUTE22,
513 ppf.ATTRIBUTE23,
514 ppf.ATTRIBUTE24,
515 ppf.ATTRIBUTE25,
516 ppf.ATTRIBUTE26,
517 ppf.ATTRIBUTE27,
518 ppf.ATTRIBUTE28,
519 ppf.ATTRIBUTE29,
520 ppf.ATTRIBUTE30,
521 to_char(ppf.LAST_UPDATE_DATE,g_datefmt),
522 ppf.LAST_UPDATED_BY,
523 ppf.LAST_UPDATE_LOGIN,
524 ppf.CREATED_BY,
525 to_char(ppf.CREATION_DATE,g_datefmt),
526 ppf.PER_INFORMATION_CATEGORY,
527 ppf.PER_INFORMATION1,
528 ppf.PER_INFORMATION2,
529 ppf.PER_INFORMATION3,
530 ppf.PER_INFORMATION4,
531 ppf.PER_INFORMATION5,
532 ppf.PER_INFORMATION6,
533 ppf.PER_INFORMATION7,
534 ppf.PER_INFORMATION8,
535 ppf.PER_INFORMATION9,
536 ppf.PER_INFORMATION10,
537 ppf.PER_INFORMATION11,
538 ppf.PER_INFORMATION12,
539 ppf.PER_INFORMATION13,
540 ppf.PER_INFORMATION14,
541 ppf.PER_INFORMATION15,
542 ppf.PER_INFORMATION16,
543 ppf.PER_INFORMATION17,
544 ppf.PER_INFORMATION18,
545 ppf.PER_INFORMATION19,
546 ppf.PER_INFORMATION20,
547 ppf.PER_INFORMATION21,
548 ppf.PER_INFORMATION22,
549 ppf.PER_INFORMATION23,
550 ppf.PER_INFORMATION24,
551 ppf.PER_INFORMATION25,
552 ppf.PER_INFORMATION26,
553 ppf.PER_INFORMATION27,
554 ppf.PER_INFORMATION28,
555 ppf.PER_INFORMATION29,
556 ppf.PER_INFORMATION30,
557 ppf.OBJECT_VERSION_NUMBER,
558 to_char(ppf.DATE_OF_DEATH,g_datefmt),
559 ppf.REHIRE_REASON
560 );
561
562 exception
563 when others then
564 put_line(l_proc||' - raised exception');
565 raise;
566
567 end;
568
569 procedure update_people_f(p_transfer_name varchar2,
570 p_inter_bg_transfer varchar2,
571 p_effective_date date,
572 ppf per_all_people_f%rowtype)
573 is
574
575 l_proc varchar2(72) := g_package||'update_people_f';
576 l_table_name constant varchar2(30) := 'PER_PEOPLE_F';
577 l_name varchar2(30);
578 l_x varchar2(1);
579
580 cursor cur_get_person is
581 select 'x'
582 from ghr_mt_people_f_v
583 where mt_name = p_transfer_name
584 and mt_person_id = to_char(ppf.person_id)
585 for update;
586
587 begin
588
589 put_line('update people_f');
590
591 open cur_get_person;
592 fetch cur_get_person into l_x;
593
594 if cur_get_person%notfound then
595 close cur_get_person;
596 hr_utility.set_message(8301, 'GHR_38549_UPDATE_NOT_FOUND');
597 hr_utility.raise_error;
598 end if;
599
600 update ghr_mt_people_f_v
601 set
602 MT_EFFECTIVE_DATE = p_effective_date,
603 MT_INTER_BG_TRANSFER = p_inter_bg_transfer,
604 BUSINESS_GROUP_ID = nvl(ppf.BUSINESS_GROUP_ID,BUSINESS_GROUP_ID),
605 PERSON_TYPE_ID = nvl(ppf.PERSON_TYPE_ID,PERSON_TYPE_ID),
606 LAST_NAME = nvl(ppf.LAST_NAME, LAST_NAME),
607 START_DATE = nvl(to_char(ppf.START_DATE,g_datefmt),START_DATE),
608 APPLICANT_NUMBER = nvl(ppf.APPLICANT_NUMBER, APPLICANT_NUMBER),
609 BACKGROUND_CHECK_STATUS =
610 nvl(ppf.BACKGROUND_CHECK_STATUS, BACKGROUND_CHECK_STATUS),
611 BACKGROUND_DATE_CHECK =
612 nvl(to_char(ppf.BACKGROUND_DATE_CHECK,g_datefmt),
613 BACKGROUND_DATE_CHECK),
614 BLOOD_TYPE = nvl(ppf.BLOOD_TYPE, BLOOD_TYPE),
615 COMMENT_ID = nvl(ppf.COMMENT_ID, COMMENT_ID),
616 CORRESPONDENCE_LANGUAGE =
617 nvl(ppf.CORRESPONDENCE_LANGUAGE,CORRESPONDENCE_LANGUAGE),
618 CURRENT_APPLICANT_FLAG =
619 nvl(ppf.CURRENT_APPLICANT_FLAG, CURRENT_APPLICANT_FLAG),
620 CURRENT_EMP_OR_APL_FLAG =
621 nvl(ppf.CURRENT_EMP_OR_APL_FLAG,CURRENT_EMP_OR_APL_FLAG),
622 CURRENT_EMPLOYEE_FLAG =
623 nvl(ppf.CURRENT_EMPLOYEE_FLAG, CURRENT_EMPLOYEE_FLAG),
624 DATE_EMPLOYEE_DATA_VERIFIED =
625 nvl(to_char(ppf.DATE_EMPLOYEE_DATA_VERIFIED,g_datefmt),
626 DATE_EMPLOYEE_DATA_VERIFIED),
627 DATE_OF_BIRTH =
628 nvl(to_char(ppf.DATE_OF_BIRTH,g_datefmt), DATE_OF_BIRTH),
629 EMAIL_ADDRESS = nvl(ppf.EMAIL_ADDRESS, EMAIL_ADDRESS),
630 EMPLOYEE_NUMBER = nvl(ppf.EMPLOYEE_NUMBER,EMPLOYEE_NUMBER),
631 EXPENSE_CHECK_SEND_TO_ADDRESS =
632 nvl(ppf.EXPENSE_CHECK_SEND_TO_ADDRESS,
633 EXPENSE_CHECK_SEND_TO_ADDRESS),
634 FAST_PATH_EMPLOYEE = nvl(ppf.FAST_PATH_EMPLOYEE,FAST_PATH_EMPLOYEE),
635 FIRST_NAME = nvl(ppf.FIRST_NAME, FIRST_NAME),
636 FTE_CAPACITY = nvl(ppf.FTE_CAPACITY, FTE_CAPACITY),
637 FULL_NAME = nvl(ppf.FULL_NAME,FULL_NAME),
638 HOLD_APPLICANT_DATE_UNTIL =
639 nvl(to_char(ppf.HOLD_APPLICANT_DATE_UNTIL,g_datefmt),
640 HOLD_APPLICANT_DATE_UNTIL),
641 HONORS = nvl(ppf.HONORS, HONORS),
642 INTERNAL_LOCATION = nvl(ppf.INTERNAL_LOCATION,INTERNAL_LOCATION),
643 KNOWN_AS = nvl(ppf.KNOWN_AS, KNOWN_AS),
644 LAST_MEDICAL_TEST_BY =
645 nvl(ppf.LAST_MEDICAL_TEST_BY, LAST_MEDICAL_TEST_BY),
646 LAST_MEDICAL_TEST_DATE =
647 nvl(to_char(ppf.LAST_MEDICAL_TEST_DATE,g_datefmt),
648 LAST_MEDICAL_TEST_DATE),
649 MAILSTOP = nvl(ppf.MAILSTOP, MAILSTOP),
650 MARITAL_STATUS = nvl(ppf.MARITAL_STATUS, MARITAL_STATUS),
651 MIDDLE_NAMES = nvl(ppf.MIDDLE_NAMES, MIDDLE_NAMES),
652 NATIONALITY = nvl(ppf.NATIONALITY,NATIONALITY),
653 NATIONAL_IDENTIFIER =
654 nvl(ppf.NATIONAL_IDENTIFIER, NATIONAL_IDENTIFIER),
655 OFFICE_NUMBER = nvl(ppf.OFFICE_NUMBER, OFFICE_NUMBER),
656 ON_MILITARY_SERVICE = nvl(ppf.ON_MILITARY_SERVICE,ON_MILITARY_SERVICE),
657 ORDER_NAME = nvl(ppf.ORDER_NAME, ORDER_NAME),
658 PRE_NAME_ADJUNCT = nvl(ppf.PRE_NAME_ADJUNCT, PRE_NAME_ADJUNCT),
659 PREVIOUS_LAST_NAME = nvl(ppf.PREVIOUS_LAST_NAME,PREVIOUS_LAST_NAME),
660 PROJECTED_START_DATE =
661 nvl(to_char(ppf.PROJECTED_START_DATE,g_datefmt),
662 PROJECTED_START_DATE),
663 REHIRE_AUTHORIZOR = nvl(ppf.REHIRE_AUTHORIZOR, REHIRE_AUTHORIZOR),
664 REHIRE_RECOMMENDATION =
665 nvl(ppf.REHIRE_RECOMMENDATION,REHIRE_RECOMMENDATION),
666 RESUME_EXISTS = nvl(ppf.RESUME_EXISTS, RESUME_EXISTS),
667 RESUME_LAST_UPDATED =
668 nvl(to_char(ppf.RESUME_LAST_UPDATED,g_datefmt),
669 RESUME_LAST_UPDATED),
670 REGISTERED_DISABLED_FLAG =
671 nvl(ppf.REGISTERED_DISABLED_FLAG,REGISTERED_DISABLED_FLAG),
672 SECOND_PASSPORT_EXISTS =
673 nvl(ppf.SECOND_PASSPORT_EXISTS, SECOND_PASSPORT_EXISTS),
674 SEX = nvl(ppf.SEX, SEX),
675 STUDENT_STATUS = nvl(ppf.STUDENT_STATUS, STUDENT_STATUS),
676 SUFFIX = nvl(ppf.SUFFIX,SUFFIX),
677 TITLE = nvl(ppf.TITLE, TITLE),
678 VENDOR_ID = nvl(ppf.VENDOR_ID, VENDOR_ID),
679 WORK_SCHEDULE = nvl(ppf.WORK_SCHEDULE, WORK_SCHEDULE),
680 WORK_TELEPHONE = nvl(ppf.WORK_TELEPHONE,WORK_TELEPHONE),
681 REQUEST_ID = nvl(ppf.REQUEST_ID,REQUEST_ID),
682 PROGRAM_APPLICATION_ID =
683 nvl(ppf.PROGRAM_APPLICATION_ID,PROGRAM_APPLICATION_ID),
684 PROGRAM_ID = nvl(ppf.PROGRAM_ID,PROGRAM_ID),
685 ATTRIBUTE_CATEGORY = nvl(ppf.ATTRIBUTE_CATEGORY,ATTRIBUTE_CATEGORY),
686 ATTRIBUTE1 = nvl(ppf.ATTRIBUTE1,ATTRIBUTE1),
687 ATTRIBUTE2 = nvl(ppf.ATTRIBUTE2,ATTRIBUTE2),
688 ATTRIBUTE3 = nvl(ppf.ATTRIBUTE3,ATTRIBUTE3),
689 ATTRIBUTE4 = nvl(ppf.ATTRIBUTE4,ATTRIBUTE4),
690 ATTRIBUTE5 = nvl(ppf.ATTRIBUTE5,ATTRIBUTE5),
691 ATTRIBUTE6 = nvl(ppf.ATTRIBUTE6,ATTRIBUTE6),
692 ATTRIBUTE7 = nvl(ppf.ATTRIBUTE7,ATTRIBUTE7),
693 ATTRIBUTE8 = nvl(ppf.ATTRIBUTE8,ATTRIBUTE8),
694 ATTRIBUTE9 = nvl(ppf.ATTRIBUTE9,ATTRIBUTE9),
695 ATTRIBUTE10 = nvl(ppf.ATTRIBUTE10,ATTRIBUTE10),
696 ATTRIBUTE11 = nvl(ppf.ATTRIBUTE11,ATTRIBUTE11),
697 ATTRIBUTE12 = nvl(ppf.ATTRIBUTE12,ATTRIBUTE12),
698 ATTRIBUTE13 = nvl(ppf.ATTRIBUTE13,ATTRIBUTE13),
699 ATTRIBUTE14 = nvl(ppf.ATTRIBUTE14,ATTRIBUTE14),
700 ATTRIBUTE15 = nvl(ppf.ATTRIBUTE15,ATTRIBUTE15),
701 ATTRIBUTE16 = nvl(ppf.ATTRIBUTE16,ATTRIBUTE16),
702 ATTRIBUTE17 = nvl(ppf.ATTRIBUTE17,ATTRIBUTE17),
703 ATTRIBUTE18 = nvl(ppf.ATTRIBUTE18,ATTRIBUTE18),
704 ATTRIBUTE19 = nvl(ppf.ATTRIBUTE19,ATTRIBUTE19),
705 ATTRIBUTE20 = nvl(ppf.ATTRIBUTE20,ATTRIBUTE20),
706 ATTRIBUTE21 = nvl(ppf.ATTRIBUTE21,ATTRIBUTE21),
707 ATTRIBUTE22 = nvl(ppf.ATTRIBUTE22,ATTRIBUTE22),
708 ATTRIBUTE23 = nvl(ppf.ATTRIBUTE23,ATTRIBUTE23),
709 ATTRIBUTE24 = nvl(ppf.ATTRIBUTE24,ATTRIBUTE24),
710 ATTRIBUTE25 = nvl(ppf.ATTRIBUTE25,ATTRIBUTE25),
711 ATTRIBUTE26 = nvl(ppf.ATTRIBUTE26,ATTRIBUTE26),
712 ATTRIBUTE27 = nvl(ppf.ATTRIBUTE27,ATTRIBUTE27),
713 ATTRIBUTE28 = nvl(ppf.ATTRIBUTE28,ATTRIBUTE28),
714 ATTRIBUTE29 = nvl(ppf.ATTRIBUTE29,ATTRIBUTE29),
715 ATTRIBUTE30 = nvl(ppf.ATTRIBUTE30,ATTRIBUTE30),
716 PER_INFORMATION_CATEGORY =
717 nvl(ppf.PER_INFORMATION_CATEGORY,PER_INFORMATION_CATEGORY),
718 PER_INFORMATION1 = nvl(ppf.PER_INFORMATION1,PER_INFORMATION1),
719 PER_INFORMATION2 = nvl(ppf.PER_INFORMATION2,PER_INFORMATION2),
720 PER_INFORMATION3 = nvl(ppf.PER_INFORMATION3,PER_INFORMATION3),
721 PER_INFORMATION4 = nvl(ppf.PER_INFORMATION4,PER_INFORMATION4),
722 PER_INFORMATION5 = nvl(ppf.PER_INFORMATION5,PER_INFORMATION5),
723 PER_INFORMATION6 = nvl(ppf.PER_INFORMATION6,PER_INFORMATION6),
724 PER_INFORMATION7 = nvl(ppf.PER_INFORMATION7,PER_INFORMATION7),
725 PER_INFORMATION8 = nvl(ppf.PER_INFORMATION8,PER_INFORMATION8),
726 PER_INFORMATION9 = nvl(ppf.PER_INFORMATION9,PER_INFORMATION9),
727 PER_INFORMATION10 = nvl(ppf.PER_INFORMATION10,PER_INFORMATION10),
728 PER_INFORMATION11 = nvl(ppf.PER_INFORMATION11,PER_INFORMATION11),
729 PER_INFORMATION12 = nvl(ppf.PER_INFORMATION12,PER_INFORMATION12),
730 PER_INFORMATION13 = nvl(ppf.PER_INFORMATION13,PER_INFORMATION13),
731 PER_INFORMATION14 = nvl(ppf.PER_INFORMATION14,PER_INFORMATION14),
732 PER_INFORMATION15 = nvl(ppf.PER_INFORMATION15,PER_INFORMATION15),
733 PER_INFORMATION16 = nvl(ppf.PER_INFORMATION16,PER_INFORMATION16),
734 PER_INFORMATION17 = nvl(ppf.PER_INFORMATION17,PER_INFORMATION17),
735 PER_INFORMATION18 = nvl(ppf.PER_INFORMATION18,PER_INFORMATION18),
736 PER_INFORMATION19 = nvl(ppf.PER_INFORMATION19,PER_INFORMATION19),
737 PER_INFORMATION20 = nvl(ppf.PER_INFORMATION20,PER_INFORMATION20),
738 PER_INFORMATION21 = nvl(ppf.PER_INFORMATION21,PER_INFORMATION21),
739 PER_INFORMATION22 = nvl(ppf.PER_INFORMATION22,PER_INFORMATION22),
740 PER_INFORMATION23 = nvl(ppf.PER_INFORMATION23,PER_INFORMATION23),
741 PER_INFORMATION24 = nvl(ppf.PER_INFORMATION24,PER_INFORMATION24),
742 PER_INFORMATION25 = nvl(ppf.PER_INFORMATION25,PER_INFORMATION25),
743 PER_INFORMATION26 = nvl(ppf.PER_INFORMATION26,PER_INFORMATION26),
744 PER_INFORMATION27 = nvl(ppf.PER_INFORMATION27,PER_INFORMATION27),
745 PER_INFORMATION28 = nvl(ppf.PER_INFORMATION28,PER_INFORMATION28),
746 PER_INFORMATION29 = nvl(ppf.PER_INFORMATION29,PER_INFORMATION29),
747 PER_INFORMATION30 = nvl(ppf.PER_INFORMATION30,PER_INFORMATION30),
748 DATE_OF_DEATH = nvl(to_char(ppf.DATE_OF_DEATH,g_datefmt),
749 DATE_OF_DEATH),
750 REHIRE_REASON = nvl(ppf.REHIRE_REASON,REHIRE_REASON)
751 where current of cur_get_person;
752
753 close cur_get_person;
754
755 exception
756 when others then
757 put_line(l_proc||' - raised exception');
758 raise;
759
760 end;
761
762 function get_code_meaning(p_lookup_type varchar2,
763 p_lookup_code varchar2)
764 return varchar2 is
765
766 l_proc varchar2(72) := g_package||'get_code_meaning';
767 l_description hr_lookups.description%type := null;
768
769 begin
770
771 if p_lookup_code is null then
772 return null;
773 end if;
774 --Bug# 11934207 added GHR_US_HANDICAP_CODE to get the description
775 --Bug# 11934989 added GHR_US_ACADEMIC_DISCIPLINE to get the description
776 if p_lookup_type IN ('GHR_US_LEGAL_AUTHORITY','GHR_US_HANDICAP_CODE','GHR_US_ACADEMIC_DISCIPLINE') then
777 l_description := ghr_pa_requests_pkg.get_lookup_description (
778 p_application_id => 800,
779 p_lookup_type => p_lookup_type,
780 p_lookup_code => p_lookup_code);
781 else
782 l_description := ghr_pa_requests_pkg.get_lookup_meaning (
783 p_application_id => 800,
784 p_lookup_type => p_lookup_type,
785 p_lookup_code => p_lookup_code);
786 end if;
787
788 return l_description;
789
790 exception
791 when others then
792 put_line(l_proc||' - raised exception');
793 raise;
794
795 end;
796
797 procedure insert_people_ei(p_transfer_name varchar2,
798 p_effective_date date,
799 pp_ei per_people_extra_info%rowtype)
800 is
801
802 l_info3_meaning varchar2(80) := null;
803 l_info4_meaning varchar2(80) := null;
804 l_info5_meaning varchar2(80) := null;
805 l_info6_meaning varchar2(80) := null;
806 l_info7_meaning varchar2(80) := null;
807 l_info8_meaning varchar2(80) := null;
808 l_info9_meaning varchar2(80) := null;
809 l_info10_meaning varchar2(80) := null;
810 l_info11_meaning varchar2(240) := null; --Bug# 12644378
811
812 l_proc varchar2(72) := g_package||'insert_people_ei';
813 l_table_name constant varchar2(30) := 'PER_PEOPLE_EXTRA_INFO';
814 l_name varchar2(30);
815
816 begin
817
818 put('insert people_ei');
819
820 -- check if pp_ei contains data, if not return
821 if pp_ei.information_type is null then
822 put_line(' - no data');
823 return;
824 end if;
825 put_line(' - ok, info_type: '||pp_ei.information_type);
826
827 who_called_me(p_name => l_name);
828
829 -- if this package body did NOT call this procedure then ckeck if
830 -- the row to be inserted already exists, if so raise an error
831 if nvl(l_name,'unknown') <> g_name then
832 put_line('Called by '|| l_name);
833 if row_exists(
834 p_transfer_name => p_transfer_name
835 ,p_person_id => pp_ei.person_id
836 ,p_table_name => l_table_name
837 ,p_info_type => pp_ei.information_type) then
838
839 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
840 hr_utility.raise_error;
841 end if;
842 else
843 put_line('Called locally');
844 end if;
845
846 if pp_ei.information_type = 'GHR_US_PER_SF52' then
847 l_info3_meaning := get_code_meaning (
848 p_lookup_type => 'GHR_US_CITIZENSHIP',
849 p_lookup_code => pp_ei.pei_information3);
850
851 l_info4_meaning := get_code_meaning (
852 p_lookup_type => 'GHR_US_VETERANS_PREF',
853 p_lookup_code => pp_ei.pei_information4);
854
855 l_info5_meaning := get_code_meaning (
856 p_lookup_type => 'GHR_US_VETERANS_PREF_FOR_RIF',
857 p_lookup_code => pp_ei.pei_information5);
858
859 l_info6_meaning := get_code_meaning (
860 p_lookup_type => 'GHR_US_VET_STATUS',
861 p_lookup_code => pp_ei.pei_information6);
862
863 elsif pp_ei.information_type = 'GHR_US_PER_GROUP1' then
864 l_info3_meaning := get_code_meaning (
865 p_lookup_type => 'GHR_US_APPOINTMENT_TYPE',
866 p_lookup_code => pp_ei.pei_information3);
867
868 l_info4_meaning := get_code_meaning (
869 p_lookup_type => 'GHR_US_TYPE_EMPLOYMENT',
870 p_lookup_code => pp_ei.pei_information4);
871
872 l_info5_meaning := get_code_meaning (
873 p_lookup_type => 'GHR_US_RACE_NATIONAL_ORIGIN',
874 p_lookup_code => pp_ei.pei_information5);
875
876 l_info7_meaning := get_code_meaning (
877 p_lookup_type => 'GHR_US_AGENCY_CODE',
878 p_lookup_code => pp_ei.pei_information7);
879
880 l_info8_meaning := get_code_meaning (
881 p_lookup_type => 'GHR_US_LEGAL_AUTHORITY',
882 p_lookup_code => pp_ei.pei_information8);
883
884 l_info9_meaning := get_code_meaning (
885 p_lookup_type => 'GHR_US_LEGAL_AUTHORITY',
886 p_lookup_code => pp_ei.pei_information9);
887
888 l_info11_meaning := get_code_meaning (
889 p_lookup_type => 'GHR_US_HANDICAP_CODE',
890 p_lookup_code => pp_ei.pei_information11);
891
892 elsif pp_ei.information_type = 'GHR_US_PER_SEPARATE_RETIRE' then
893 l_info3_meaning := get_code_meaning (
894 p_lookup_type => 'GHR_US_FERS_COVERAGE',
895 p_lookup_code => pp_ei.pei_information3);
896
897 l_info4_meaning := get_code_meaning (
898 p_lookup_type => 'GHR_US_PREV_RETIRE_COVER',
899 p_lookup_code => pp_ei.pei_information4);
900
901 elsif pp_ei.information_type = 'GHR_US_PER_UNIFORMED_SERVICES' then
902 l_info5_meaning := get_code_meaning (
903 p_lookup_type => 'GHR_US_CREDIT_MIL_SVC',
904 p_lookup_code => pp_ei.pei_information5);
905
906 end if;
907
908 insert into ghr_mt_people_ei_v (
909 GHR_INTERFACE_ID,
910 MT_PROCESS_DATE,
911 MT_SOURCE,
912 MT_EFFECTIVE_DATE,
913 MT_NAME,
914 MT_STATUS,
915 MT_PERSON_ID,
916 MT_TABLE_NAME,
917 MT_INFORMATION_TYPE,
918 MT_INTER_BG_TRANSFER,
919 PERSON_EXTRA_INFO_ID,
920 PERSON_ID,
921 INFORMATION_TYPE,
922 REQUEST_ID,
923 PROGRAM_APPLICATION_ID,
924 PROGRAM_ID,
925 PROGRAM_UPDATE_DATE,
926 PEI_ATTRIBUTE_CATEGORY,
927 PEI_ATTRIBUTE1,
928 PEI_ATTRIBUTE2,
929 PEI_ATTRIBUTE3,
930 PEI_ATTRIBUTE4,
931 PEI_ATTRIBUTE5,
932 PEI_ATTRIBUTE6,
933 PEI_ATTRIBUTE7,
934 PEI_ATTRIBUTE8,
935 PEI_ATTRIBUTE9,
936 PEI_ATTRIBUTE10,
937 PEI_ATTRIBUTE11,
938 PEI_ATTRIBUTE12,
939 PEI_ATTRIBUTE13,
940 PEI_ATTRIBUTE14,
941 PEI_ATTRIBUTE15,
942 PEI_ATTRIBUTE16,
943 PEI_ATTRIBUTE17,
944 PEI_ATTRIBUTE18,
945 PEI_ATTRIBUTE19,
946 PEI_ATTRIBUTE20,
947 PEI_INFORMATION_CATEGORY,
948 PEI_INFORMATION1,
949 PEI_INFORMATION2,
950 PEI_INFORMATION3,
951 PEI_INFORMATION4,
952 PEI_INFORMATION5,
953 PEI_INFORMATION6,
954 PEI_INFORMATION7,
955 PEI_INFORMATION8,
956 PEI_INFORMATION9,
957 PEI_INFORMATION10,
958 PEI_INFORMATION11,
959 PEI_INFORMATION12,
960 PEI_INFORMATION13,
961 PEI_INFORMATION14,
962 PEI_INFORMATION15,
963 PEI_INFORMATION16,
964 PEI_INFORMATION17,
965 PEI_INFORMATION18,
966 PEI_INFORMATION19,
967 PEI_INFORMATION20,
968 PEI_INFORMATION21,
969 PEI_INFORMATION22,
970 PEI_INFORMATION23,
971 PEI_INFORMATION24,
972 PEI_INFORMATION25,
973 PEI_INFORMATION26,
974 PEI_INFORMATION27,
975 PEI_INFORMATION28,
976 PEI_INFORMATION29,
977 PEI_INFORMATION30,
978 OBJECT_VERSION_NUMBER,
979 LAST_UPDATE_DATE,
980 LAST_UPDATED_BY,
981 LAST_UPDATE_LOGIN,
982 CREATED_BY,
983 CREATION_DATE,
984 info3_meaning,
985 info4_meaning,
986 info5_meaning,
987 info6_meaning,
988 info7_meaning,
989 info8_meaning,
990 info9_meaning,
991 info10_meaning,
992 info11_meaning
993 )
994 values (
995 ghr_interface_s.nextval,
996 sysdate,
997 l_name,
998 p_effective_date,
999 p_transfer_name,
1000 null, -- MT_STATUS,
1001 pp_ei.person_id, -- MT_PERSON_ID,
1002 l_table_name, -- MT_TABLE_NAME,
1003 pp_ei.information_type, -- MT_INFORMATION_TYPE,
1004 null,
1005 pp_ei.PERSON_EXTRA_INFO_ID,
1006 pp_ei.PERSON_ID,
1007 pp_ei.INFORMATION_TYPE,
1008 pp_ei.REQUEST_ID,
1009 pp_ei.PROGRAM_APPLICATION_ID,
1010 pp_ei.PROGRAM_ID,
1011 to_char(pp_ei.PROGRAM_UPDATE_DATE,g_datefmt),
1012 pp_ei.PEI_ATTRIBUTE_CATEGORY,
1013 pp_ei.PEI_ATTRIBUTE1,
1014 pp_ei.PEI_ATTRIBUTE2,
1015 pp_ei.PEI_ATTRIBUTE3,
1016 pp_ei.PEI_ATTRIBUTE4,
1017 pp_ei.PEI_ATTRIBUTE5,
1018 pp_ei.PEI_ATTRIBUTE6,
1019 pp_ei.PEI_ATTRIBUTE7,
1020 pp_ei.PEI_ATTRIBUTE8,
1021 pp_ei.PEI_ATTRIBUTE9,
1022 pp_ei.PEI_ATTRIBUTE10,
1023 pp_ei.PEI_ATTRIBUTE11,
1024 pp_ei.PEI_ATTRIBUTE12,
1025 pp_ei.PEI_ATTRIBUTE13,
1026 pp_ei.PEI_ATTRIBUTE14,
1027 pp_ei.PEI_ATTRIBUTE15,
1028 pp_ei.PEI_ATTRIBUTE16,
1029 pp_ei.PEI_ATTRIBUTE17,
1030 pp_ei.PEI_ATTRIBUTE18,
1031 pp_ei.PEI_ATTRIBUTE19,
1032 pp_ei.PEI_ATTRIBUTE20,
1033 pp_ei.PEI_INFORMATION_CATEGORY,
1034 pp_ei.PEI_INFORMATION1,
1035 pp_ei.PEI_INFORMATION2,
1036 pp_ei.PEI_INFORMATION3,
1037 pp_ei.PEI_INFORMATION4,
1038 pp_ei.PEI_INFORMATION5,
1039 pp_ei.PEI_INFORMATION6,
1040 pp_ei.PEI_INFORMATION7,
1041 pp_ei.PEI_INFORMATION8,
1042 pp_ei.PEI_INFORMATION9,
1043 pp_ei.PEI_INFORMATION10,
1044 pp_ei.PEI_INFORMATION11,
1045 pp_ei.PEI_INFORMATION12,
1046 pp_ei.PEI_INFORMATION13,
1047 pp_ei.PEI_INFORMATION14,
1048 pp_ei.PEI_INFORMATION15,
1049 pp_ei.PEI_INFORMATION16,
1050 pp_ei.PEI_INFORMATION17,
1051 pp_ei.PEI_INFORMATION18,
1052 pp_ei.PEI_INFORMATION19,
1053 pp_ei.PEI_INFORMATION20,
1054 pp_ei.PEI_INFORMATION21,
1055 pp_ei.PEI_INFORMATION22,
1056 pp_ei.PEI_INFORMATION23,
1057 pp_ei.PEI_INFORMATION24,
1058 pp_ei.PEI_INFORMATION25,
1059 pp_ei.PEI_INFORMATION26,
1060 pp_ei.PEI_INFORMATION27,
1061 pp_ei.PEI_INFORMATION28,
1062 pp_ei.PEI_INFORMATION29,
1063 pp_ei.PEI_INFORMATION30,
1064 pp_ei.OBJECT_VERSION_NUMBER,
1065 to_char(pp_ei.LAST_UPDATE_DATE,g_datefmt),
1066 pp_ei.LAST_UPDATED_BY,
1067 pp_ei.LAST_UPDATE_LOGIN,
1068 pp_ei.CREATED_BY,
1069 to_char(pp_ei.CREATION_DATE,g_datefmt),
1070 l_info3_meaning,
1071 l_info4_meaning,
1072 l_info5_meaning,
1073 l_info6_meaning,
1074 l_info7_meaning,
1075 l_info8_meaning,
1076 l_info9_meaning,
1077 l_info10_meaning,
1078 l_info11_meaning
1079 );
1080
1081 exception
1082 when others then
1083 put_line(l_proc||' - raised exception');
1084 raise;
1085 end;
1086
1087 procedure update_people_ei(p_transfer_name varchar2,
1088 p_effective_date date,
1089 pp_ei per_people_extra_info%rowtype)
1090 is
1091
1092 l_info3_meaning varchar2(80) := null;
1093 l_info4_meaning varchar2(80) := null;
1094 l_info5_meaning varchar2(80) := null;
1095 l_info6_meaning varchar2(80) := null;
1096 l_info7_meaning varchar2(80) := null;
1097 l_info8_meaning varchar2(80) := null;
1098 l_info9_meaning varchar2(80) := null;
1099 l_info10_meaning varchar2(80) := null;
1100 l_info11_meaning varchar2(240) := null;--Bug# 12644378
1101
1102 l_proc varchar2(72) := g_package||'update_people_ei';
1103 l_x varchar2(1);
1104
1105 cursor cur_get_person_ei is
1106 select 'x' -- mt_person_id
1107 from ghr_mt_people_ei_v
1108 where mt_name = p_transfer_name
1109 and mt_person_id = to_char(pp_ei.person_id)
1110 and mt_information_type = pp_ei.information_type
1111 for update;
1112
1113 begin
1114
1115 put_line('update_people_ei');
1116
1117 open cur_get_person_ei;
1118 fetch cur_get_person_ei into l_x;
1119
1120 -- if select fails to return a row then call insert
1121 if cur_get_person_ei%notfound then
1122
1123 put_line('update row not found - calling insert');
1124 close cur_get_person_ei;
1125
1126 insert_people_ei(
1127 p_transfer_name => p_transfer_name,
1128 p_effective_date => p_effective_date,
1129 pp_ei => pp_ei
1130 );
1131
1132 return;
1133
1134 end if;
1135
1136 if pp_ei.information_type = 'GHR_US_PER_SF52' then
1137 l_info3_meaning := get_code_meaning (
1138 p_lookup_type => 'GHR_US_CITIZENSHIP',
1139 p_lookup_code => pp_ei.pei_information3);
1140
1141 l_info4_meaning := get_code_meaning (
1142 p_lookup_type => 'GHR_US_VETERANS_PREF',
1143 p_lookup_code => pp_ei.pei_information4);
1144
1145 l_info5_meaning := get_code_meaning (
1146 p_lookup_type => 'GHR_US_VETERANS_PREF_FOR_RIF',
1147 p_lookup_code => pp_ei.pei_information5);
1148
1149 l_info6_meaning := get_code_meaning (
1150 p_lookup_type => 'GHR_US_VET_STATUS',
1151 p_lookup_code => pp_ei.pei_information6);
1152
1153 elsif pp_ei.information_type = 'GHR_US_PER_GROUP1' then
1154 l_info3_meaning := get_code_meaning (
1155 p_lookup_type => 'GHR_US_APPOINTMENT_TYPE',
1156 p_lookup_code => pp_ei.pei_information3);
1157
1158 l_info4_meaning := get_code_meaning (
1159 p_lookup_type => 'GHR_US_TYPE_EMPLOYMENT',
1160 p_lookup_code => pp_ei.pei_information4);
1161
1162 l_info5_meaning := get_code_meaning (
1163 p_lookup_type => 'GHR_US_RACE_NATIONAL_ORIGIN',
1164 p_lookup_code => pp_ei.pei_information5);
1165
1166 l_info7_meaning := get_code_meaning (
1167 p_lookup_type => 'GHR_US_AGENCY_CODE',
1168 p_lookup_code => pp_ei.pei_information7);
1169
1170 l_info8_meaning := get_code_meaning (
1171 p_lookup_type => 'GHR_US_LEGAL_AUTHORITY',
1172 p_lookup_code => pp_ei.pei_information8);
1173
1174 l_info9_meaning := get_code_meaning (
1175 p_lookup_type => 'GHR_US_LEGAL_AUTHORITY',
1176 p_lookup_code => pp_ei.pei_information9);
1177
1178 l_info11_meaning := get_code_meaning (
1179 p_lookup_type => 'GHR_US_HANDICAP_CODE',
1180 p_lookup_code => pp_ei.pei_information11);
1181
1182 elsif pp_ei.information_type = 'GHR_US_PER_SEPARATE_RETIRE' then
1183 l_info3_meaning := get_code_meaning (
1184 p_lookup_type => 'GHR_US_FERS_COVERAGE',
1185 p_lookup_code => pp_ei.pei_information3);
1186
1187 l_info4_meaning := get_code_meaning (
1188 p_lookup_type => 'GHR_US_PREV_RETIRE_COVER',
1189 p_lookup_code => pp_ei.pei_information4);
1190
1191 elsif pp_ei.information_type = 'GHR_US_PER_UNIFORMED_SERVICES' then
1192 l_info5_meaning := get_code_meaning (
1193 p_lookup_type => 'GHR_US_CREDIT_MIL_SVC',
1194 p_lookup_code => pp_ei.pei_information5);
1195
1196 end if;
1197
1198 update ghr_mt_people_ei_v
1199 set
1200 MT_EFFECTIVE_DATE = nvl(p_EFFECTIVE_DATE,MT_EFFECTIVE_DATE),
1201 PERSON_EXTRA_INFO_ID =
1202 nvl(pp_ei.PERSON_EXTRA_INFO_ID,PERSON_EXTRA_INFO_ID),
1203 INFORMATION_TYPE = nvl(pp_ei.INFORMATION_TYPE,INFORMATION_TYPE),
1204 REQUEST_ID = nvl(pp_ei.REQUEST_ID,REQUEST_ID),
1205 PROGRAM_APPLICATION_ID =
1206 nvl(pp_ei.PROGRAM_APPLICATION_ID,PROGRAM_APPLICATION_ID),
1207 PROGRAM_ID = nvl(pp_ei.PROGRAM_ID,PROGRAM_ID),
1208 PROGRAM_UPDATE_DATE =
1209 nvl(to_char(pp_ei.PROGRAM_UPDATE_DATE,g_datefmt),
1210 PROGRAM_UPDATE_DATE),
1211 PEI_ATTRIBUTE_CATEGORY =
1212 nvl(pp_ei.PEI_ATTRIBUTE_CATEGORY,PEI_ATTRIBUTE_CATEGORY),
1213 PEI_ATTRIBUTE1 = nvl(pp_ei.PEI_ATTRIBUTE1,PEI_ATTRIBUTE1),
1214 PEI_ATTRIBUTE2 = nvl(pp_ei.PEI_ATTRIBUTE2,PEI_ATTRIBUTE2),
1215 PEI_ATTRIBUTE3 = nvl(pp_ei.PEI_ATTRIBUTE3,PEI_ATTRIBUTE3),
1216 PEI_ATTRIBUTE4 = nvl(pp_ei.PEI_ATTRIBUTE4,PEI_ATTRIBUTE4),
1217 PEI_ATTRIBUTE5 = nvl(pp_ei.PEI_ATTRIBUTE5,PEI_ATTRIBUTE5),
1218 PEI_ATTRIBUTE6 = nvl(pp_ei.PEI_ATTRIBUTE6,PEI_ATTRIBUTE6),
1219 PEI_ATTRIBUTE7 = nvl(pp_ei.PEI_ATTRIBUTE7,PEI_ATTRIBUTE7),
1220 PEI_ATTRIBUTE8 = nvl(pp_ei.PEI_ATTRIBUTE8,PEI_ATTRIBUTE8),
1221 PEI_ATTRIBUTE9 = nvl(pp_ei.PEI_ATTRIBUTE9,PEI_ATTRIBUTE9),
1222 PEI_ATTRIBUTE10 = nvl(pp_ei.PEI_ATTRIBUTE10,PEI_ATTRIBUTE10),
1223 PEI_ATTRIBUTE11 = nvl(pp_ei.PEI_ATTRIBUTE11,PEI_ATTRIBUTE11),
1224 PEI_ATTRIBUTE12 = nvl(pp_ei.PEI_ATTRIBUTE12,PEI_ATTRIBUTE12),
1225 PEI_ATTRIBUTE13 = nvl(pp_ei.PEI_ATTRIBUTE13,PEI_ATTRIBUTE13),
1226 PEI_ATTRIBUTE14 = nvl(pp_ei.PEI_ATTRIBUTE14,PEI_ATTRIBUTE14),
1227 PEI_ATTRIBUTE15 = nvl(pp_ei.PEI_ATTRIBUTE15,PEI_ATTRIBUTE15),
1228 PEI_ATTRIBUTE16 = nvl(pp_ei.PEI_ATTRIBUTE16,PEI_ATTRIBUTE16),
1229 PEI_ATTRIBUTE17 = nvl(pp_ei.PEI_ATTRIBUTE17,PEI_ATTRIBUTE17),
1230 PEI_ATTRIBUTE18 = nvl(pp_ei.PEI_ATTRIBUTE18,PEI_ATTRIBUTE18),
1231 PEI_ATTRIBUTE19 = nvl(pp_ei.PEI_ATTRIBUTE19,PEI_ATTRIBUTE19),
1232 PEI_ATTRIBUTE20 = nvl(pp_ei.PEI_ATTRIBUTE20,PEI_ATTRIBUTE20),
1233 PEI_INFORMATION_CATEGORY =
1234 nvl(pp_ei.PEI_INFORMATION_CATEGORY,PEI_INFORMATION_CATEGORY),
1235 PEI_INFORMATION1 = nvl(pp_ei.PEI_INFORMATION1,PEI_INFORMATION1),
1236 PEI_INFORMATION2 = nvl(pp_ei.PEI_INFORMATION2,PEI_INFORMATION2),
1237 --PEI_INFORMATION3 = nvl(pp_ei.PEI_INFORMATION3,PEI_INFORMATION3),
1238 ---Bug 2412656 FERS Coverage
1239 PEI_INFORMATION3 = decode(pp_ei.information_type,'GHR_US_PER_SEPARATE_RETIRE',
1240 pp_ei.PEI_INFORMATION3, nvl(pp_ei.PEI_INFORMATION3,PEI_INFORMATION3)),
1241 PEI_INFORMATION4 = nvl(pp_ei.PEI_INFORMATION4,PEI_INFORMATION4),
1242 --PEI_INFORMATION5 = nvl(pp_ei.PEI_INFORMATION5,PEI_INFORMATION5),
1243 ---Bug 2412656 Frozen Service
1244 PEI_INFORMATION5 = decode(pp_ei.information_type,'GHR_US_PER_SEPARATE_RETIRE',
1245 pp_ei.PEI_INFORMATION5, nvl(pp_ei.PEI_INFORMATION5,PEI_INFORMATION5)),
1246 PEI_INFORMATION6 = nvl(pp_ei.PEI_INFORMATION6,PEI_INFORMATION6),
1247 PEI_INFORMATION7 = nvl(pp_ei.PEI_INFORMATION7,PEI_INFORMATION7),
1248 PEI_INFORMATION8 = nvl(pp_ei.PEI_INFORMATION8,PEI_INFORMATION8),
1249 PEI_INFORMATION9 = nvl(pp_ei.PEI_INFORMATION9,PEI_INFORMATION9),
1250 PEI_INFORMATION10 = nvl(pp_ei.PEI_INFORMATION10,PEI_INFORMATION10),
1251 PEI_INFORMATION11 = nvl(pp_ei.PEI_INFORMATION11,PEI_INFORMATION11),
1252 PEI_INFORMATION12 = nvl(pp_ei.PEI_INFORMATION12,PEI_INFORMATION12),
1253 PEI_INFORMATION13 = nvl(pp_ei.PEI_INFORMATION13,PEI_INFORMATION13),
1254 PEI_INFORMATION14 = nvl(pp_ei.PEI_INFORMATION14,PEI_INFORMATION14),
1255 PEI_INFORMATION15 = nvl(pp_ei.PEI_INFORMATION15,PEI_INFORMATION15),
1256 PEI_INFORMATION16 = nvl(pp_ei.PEI_INFORMATION16,PEI_INFORMATION16),
1257 PEI_INFORMATION17 = nvl(pp_ei.PEI_INFORMATION17,PEI_INFORMATION17),
1258 PEI_INFORMATION18 = nvl(pp_ei.PEI_INFORMATION18,PEI_INFORMATION18),
1259 PEI_INFORMATION19 = nvl(pp_ei.PEI_INFORMATION19,PEI_INFORMATION19),
1260 PEI_INFORMATION20 = nvl(pp_ei.PEI_INFORMATION20,PEI_INFORMATION20),
1261 PEI_INFORMATION21 = nvl(pp_ei.PEI_INFORMATION21,PEI_INFORMATION21),
1262 PEI_INFORMATION22 = nvl(pp_ei.PEI_INFORMATION22,PEI_INFORMATION22),
1263 PEI_INFORMATION23 = nvl(pp_ei.PEI_INFORMATION23,PEI_INFORMATION23),
1264 PEI_INFORMATION24 = nvl(pp_ei.PEI_INFORMATION24,PEI_INFORMATION24),
1265 PEI_INFORMATION25 = nvl(pp_ei.PEI_INFORMATION25,PEI_INFORMATION25),
1266 PEI_INFORMATION26 = nvl(pp_ei.PEI_INFORMATION26,PEI_INFORMATION26),
1267 PEI_INFORMATION27 = nvl(pp_ei.PEI_INFORMATION27,PEI_INFORMATION27),
1268 PEI_INFORMATION28 = nvl(pp_ei.PEI_INFORMATION28,PEI_INFORMATION28),
1269 PEI_INFORMATION29 = nvl(pp_ei.PEI_INFORMATION29,PEI_INFORMATION29),
1270 PEI_INFORMATION30 = nvl(pp_ei.PEI_INFORMATION30,PEI_INFORMATION30),
1271 --info3_meaning = nvl(l_info3_meaning,info3_meaning),
1272 -----Bug 2412656 FERS Coverage
1273 info3_meaning = decode(pp_ei.information_type,'GHR_US_PER_SEPARATE_RETIRE',
1274 l_info3_meaning, nvl(l_info3_meaning,info3_meaning)),
1275 info4_meaning = nvl(l_info4_meaning,info4_meaning),
1276 info5_meaning = nvl(l_info5_meaning,info5_meaning),
1277 info6_meaning = nvl(l_info6_meaning,info6_meaning),
1278 info7_meaning = nvl(l_info7_meaning,info7_meaning),
1279 info8_meaning = nvl(l_info8_meaning,info8_meaning),
1280 info9_meaning = nvl(l_info9_meaning,info9_meaning),
1281 info10_meaning = nvl(l_info10_meaning,info10_meaning),
1282 info11_meaning = nvl(l_info11_meaning,info11_meaning)
1283 where current of cur_get_person_ei;
1284
1285 close cur_get_person_ei;
1286
1287 exception
1288 when others then
1289 put_line(l_proc||' - raised exception');
1290 raise;
1291 end;
1292
1293
1294 --
1295 -- insert data only if there is at least one value in p_si which is
1296 -- not null
1297 --
1298 procedure insert_special_info(
1299 p_transfer_name varchar2,
1300 p_effective_date date,
1301 p_person_id number,
1302 p_flex_name varchar2,
1303 p_si ghr_api.special_information_type)
1304 is
1305
1306 l_concat varchar2(2000);
1307 l_segment1_meaning varchar2(80);
1308 l_segment2_meaning varchar2(240);--Bug# 12644378
1309 l_proc varchar2(72) := g_package||'insert_special_info';
1310 l_table_name constant varchar2(30) := 'PER_ANALYSIS_CRITERIA';
1311 l_name varchar2(30);
1312
1313 begin
1314
1315 put('insert special info');
1316
1317 l_concat := p_si.segment1 ||
1318 p_si.segment2 ||
1319 p_si.SEGMENT3 ||
1320 p_si.SEGMENT4 ||
1321 p_si.SEGMENT5 ||
1322 p_si.SEGMENT6 ||
1323 p_si.SEGMENT7 ||
1324 p_si.SEGMENT8 ||
1325 p_si.SEGMENT9 ||
1326 p_si.SEGMENT10 ||
1327 p_si.SEGMENT11 ||
1328 p_si.SEGMENT12 ||
1329 p_si.SEGMENT13 ||
1330 p_si.SEGMENT14 ||
1331 p_si.SEGMENT15 ||
1332 p_si.SEGMENT16 ||
1333 p_si.SEGMENT17 ||
1334 p_si.SEGMENT18 ||
1335 p_si.SEGMENT19 ||
1336 p_si.SEGMENT20;
1337
1338 if l_concat is null then
1339 put_line(' - no data');
1340 return;
1341 end if;
1342 put_line(' - ok, flex_name: '||p_flex_name);
1343
1344 who_called_me(p_name => l_name);
1345
1346 -- if this package body did NOT call this procedure then ckeck if
1347 -- the row to be inserted already exists, if so raise an error
1348 if nvl(l_name,'unknown') <> g_name then
1349 put_line('Called by '|| l_name);
1350 if row_exists(
1351 p_transfer_name => p_transfer_name
1352 ,p_person_id => p_person_id
1353 ,p_table_name => l_table_name
1354 ,p_info_type => p_flex_name) then
1355
1356 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
1357 hr_utility.raise_error;
1358 end if;
1359 else
1360 put_line('Called locally');
1361 end if;
1362
1363 if p_flex_name = 'US Fed Education' then
1364 l_segment1_meaning := get_code_meaning (
1365 p_lookup_type => 'GHR_US_EDUCATIONAL_LEVEL',
1366 p_lookup_code => p_si.segment1);
1367
1368 l_segment2_meaning := get_code_meaning (
1369 p_lookup_type => 'GHR_US_ACADEMIC_DISCIPLINE',
1370 p_lookup_code => p_si.segment2);
1371
1372 end if;
1373
1374
1375 insert into ghr_mt_analysis_criteria_v (
1376 GHR_INTERFACE_ID,
1377 MT_PROCESS_DATE,
1378 MT_SOURCE,
1379 MT_EFFECTIVE_DATE,
1380 MT_NAME,
1381 MT_STATUS,
1382 MT_PERSON_ID,
1383 MT_TABLE_NAME,
1384 MT_INFORMATION_TYPE,
1385 MT_INTER_BG_TRANSFER,
1386 SEGMENT1,
1387 SEGMENT2,
1388 SEGMENT3,
1389 SEGMENT4,
1390 SEGMENT5,
1391 SEGMENT6,
1392 SEGMENT7,
1393 SEGMENT8,
1394 SEGMENT9,
1395 SEGMENT10,
1396 SEGMENT11,
1397 SEGMENT12,
1398 SEGMENT13,
1399 SEGMENT14,
1400 SEGMENT15,
1401 SEGMENT16,
1402 SEGMENT17,
1403 SEGMENT18,
1404 SEGMENT19,
1405 SEGMENT20,
1406 segment1_meaning,
1407 segment2_meaning
1408 )
1409 values (
1410 ghr_interface_s.nextval,
1411 sysdate,
1412 l_name,
1413 p_effective_date,
1414 p_transfer_name,
1415 null, -- MT_STATUS,
1416 p_person_id, -- MT_PERSON_ID,
1417 l_table_name, -- MT_TABLE_NAME,
1418 p_flex_name, -- MT_INFORMATION_TYPE,
1419 null,
1420 p_si.SEGMENT1,
1421 p_si.SEGMENT2,
1422 p_si.SEGMENT3,
1423 p_si.SEGMENT4,
1424 p_si.SEGMENT5,
1425 p_si.SEGMENT6,
1426 p_si.SEGMENT7,
1427 p_si.SEGMENT8,
1428 p_si.SEGMENT9,
1429 p_si.SEGMENT10,
1430 p_si.SEGMENT11,
1431 p_si.SEGMENT12,
1432 p_si.SEGMENT13,
1433 p_si.SEGMENT14,
1434 p_si.SEGMENT15,
1435 p_si.SEGMENT16,
1436 p_si.SEGMENT17,
1437 p_si.SEGMENT18,
1438 p_si.SEGMENT19,
1439 p_si.SEGMENT20,
1440 l_segment1_meaning,
1441 l_segment2_meaning
1442 );
1443
1444 exception
1445 when others then
1446 put_line(l_proc||' - raised exception');
1447 raise;
1448
1449 end;
1450
1451 procedure update_special_info(
1452 p_transfer_name varchar2,
1453 p_effective_date date,
1454 p_person_id number,
1455 p_flex_name varchar2,
1456 p_si ghr_api.special_information_type)
1457 is
1458
1459 l_segment1_meaning varchar2(80);
1460 l_segment2_meaning varchar2(240);--Bug 12644378
1461 l_proc varchar2(72) := g_package||'update_special_info';
1462 l_x varchar2(1);
1463
1464 cursor cur_get_special_info is
1465 select 'x'
1466 from ghr_mt_analysis_criteria_v
1467 where mt_name = p_transfer_name
1468 and mt_person_id = to_char(p_person_id)
1469 and mt_information_type = p_flex_name
1470 for update;
1471
1472 begin
1473
1474 put_line('update special info');
1475
1476 open cur_get_special_info;
1477 fetch cur_get_special_info into l_x;
1478
1479 if cur_get_special_info%notfound then
1480 put_line('update row not found - calling insert');
1481 close cur_get_special_info;
1482
1483 insert_special_info(
1484 p_transfer_name => p_transfer_name,
1485 p_effective_date => p_effective_date,
1486 p_person_id => p_person_id,
1487 p_flex_name => p_flex_name,
1488 p_si => p_si
1489 );
1490
1491 return;
1492 end if;
1493
1494 if p_flex_name = 'US Fed Education' then
1495 l_segment1_meaning := get_code_meaning (
1496 p_lookup_type => 'GHR_US_EDUCATIONAL_LEVEL',
1497 p_lookup_code => p_si.segment1);
1498
1499 l_segment2_meaning := get_code_meaning (
1500 p_lookup_type => 'GHR_US_ACADEMIC_DISCIPLINE',
1501 p_lookup_code => p_si.segment2);
1502
1503 end if;
1504
1505 update ghr_mt_analysis_criteria_v
1506 set
1507 MT_EFFECTIVE_DATE = p_effective_date,
1508 SEGMENT1 = nvl(p_si.SEGMENT1,SEGMENT1),
1509 --SEGMENT2 = nvl(p_si.SEGMENT2,SEGMENT2),
1510 --SEGMENT3 = nvl(p_si.SEGMENT3,SEGMENT3),
1511 SEGMENT2 = p_si.SEGMENT2,
1512 SEGMENT3 = p_si.SEGMENT3,
1513 SEGMENT4 = nvl(p_si.SEGMENT4,SEGMENT4),
1514 SEGMENT5 = nvl(p_si.SEGMENT5,SEGMENT5),
1515 SEGMENT6 = nvl(p_si.SEGMENT6,SEGMENT6),
1516 SEGMENT7 = nvl(p_si.SEGMENT7,SEGMENT7),
1517 SEGMENT8 = nvl(p_si.SEGMENT8,SEGMENT8),
1518 SEGMENT9 = nvl(p_si.SEGMENT9,SEGMENT9),
1519 SEGMENT10 = nvl(p_si.SEGMENT10,SEGMENT10),
1520 SEGMENT11 = nvl(p_si.SEGMENT11,SEGMENT11),
1521 SEGMENT12 = nvl(p_si.SEGMENT12,SEGMENT12),
1522 SEGMENT13 = nvl(p_si.SEGMENT13,SEGMENT13),
1523 SEGMENT14 = nvl(p_si.SEGMENT14,SEGMENT14),
1524 SEGMENT15 = nvl(p_si.SEGMENT15,SEGMENT15),
1525 SEGMENT16 = nvl(p_si.SEGMENT16,SEGMENT16),
1526 SEGMENT17 = nvl(p_si.SEGMENT17,SEGMENT17),
1527 SEGMENT18 = nvl(p_si.SEGMENT18,SEGMENT18),
1528 SEGMENT19 = nvl(p_si.SEGMENT19,SEGMENT19),
1529 SEGMENT20 = nvl(p_si.SEGMENT20,SEGMENT20),
1530 segment1_meaning = nvl(l_segment1_meaning,segment1_meaning),
1531 --segment2_meaning = nvl(l_segment2_meaning,segment2_meaning)
1532 ---bug 2412656 SIT
1533 segment2_meaning = l_segment2_meaning
1534 where current of cur_get_special_info;
1535
1536 close cur_get_special_info;
1537
1538 exception
1539 when others then
1540 put_line(l_proc||' - raised exception');
1541 raise;
1542
1543 end;
1544
1545
1546 /*
1547 * The parameters, p_contact_name, and p_contact_type should contain values
1548 * when the address inserted is that of the contact.
1549 */
1550 procedure insert_address(p_transfer_name in varchar2,
1551 p_effective_date in date,
1552 p_a in per_addresses%rowtype,
1553 p_contact_name in varchar2 default null,
1554 p_contact_type in varchar2 default null)
1555 is
1556
1557 l_proc varchar2(72) := g_package||'insert_address';
1558 l_name varchar2(30);
1559 l_table_name constant varchar2(30) := 'PER_ADDRESSES';
1560
1561 begin
1562
1563 if p_contact_name is null then
1564 put('insert address');
1565 else
1566 put('insert contact address');
1567 end if;
1568
1569 who_called_me(p_name => l_name);
1570
1571 -- if this package body did NOT call this procedure then ckeck if
1572 -- the row to be inserted already exists, if so raise an error
1573 if nvl(l_name,'unknown') <> g_name then
1574 put_line(' - called by '|| l_name);
1575 if row_exists(
1576 p_transfer_name => p_transfer_name
1577 ,p_person_id => p_a.person_id
1578 ,p_table_name => l_table_name
1579 ,p_contact_name => p_contact_name) then
1580
1581 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
1582 hr_utility.raise_error;
1583 end if;
1584 else
1585 put_line(' - called locally');
1586 end if;
1587
1588 insert into ghr_mt_addresses_v (
1589 GHR_INTERFACE_ID,
1590 MT_PROCESS_DATE,
1591 MT_SOURCE,
1592 MT_EFFECTIVE_DATE,
1593 MT_NAME,
1594 MT_STATUS,
1595 MT_PERSON_ID,
1596 MT_TABLE_NAME,
1597 MT_INFORMATION_TYPE,
1598 MT_INTER_BG_TRANSFER,
1599 MT_CONTACT_NAME,
1600 MT_CONTACT_TYPE,
1601 ADDRESS_ID,
1602 BUSINESS_GROUP_ID,
1603 PERSON_ID,
1604 DATE_FROM,
1605 PRIMARY_FLAG,
1606 STYLE,
1607 ADDRESS_LINE1,
1608 ADDRESS_LINE2,
1609 ADDRESS_LINE3,
1610 ADDRESS_TYPE,
1611 COMMENTS,
1612 COUNTRY,
1613 DATE_TO,
1614 POSTAL_CODE,
1615 REGION_1,
1616 REGION_2,
1617 REGION_3,
1618 TELEPHONE_NUMBER_1,
1619 TELEPHONE_NUMBER_2,
1620 TELEPHONE_NUMBER_3,
1621 TOWN_OR_CITY,
1622 REQUEST_ID,
1623 PROGRAM_APPLICATION_ID,
1624 PROGRAM_ID,
1625 PROGRAM_UPDATE_DATE,
1626 ADDR_ATTRIBUTE_CATEGORY,
1627 ADDR_ATTRIBUTE1,
1628 ADDR_ATTRIBUTE2,
1629 ADDR_ATTRIBUTE3,
1630 ADDR_ATTRIBUTE4,
1631 ADDR_ATTRIBUTE5,
1632 ADDR_ATTRIBUTE6,
1633 ADDR_ATTRIBUTE7,
1634 ADDR_ATTRIBUTE8,
1635 ADDR_ATTRIBUTE9,
1636 ADDR_ATTRIBUTE10,
1637 ADDR_ATTRIBUTE11,
1638 ADDR_ATTRIBUTE12,
1639 ADDR_ATTRIBUTE13,
1640 ADDR_ATTRIBUTE14,
1641 ADDR_ATTRIBUTE15,
1642 ADDR_ATTRIBUTE16,
1643 ADDR_ATTRIBUTE17,
1644 ADDR_ATTRIBUTE18,
1645 ADDR_ATTRIBUTE19,
1646 ADDR_ATTRIBUTE20
1647 )
1648 values (
1649 ghr_interface_s.nextval,
1650 sysdate,
1651 l_name,
1652 p_effective_date,
1653 p_transfer_name,
1654 null, -- MT_STATUS,
1655 p_a.person_id, -- MT_PERSON_ID,
1656 l_table_name, -- MT_TABLE_NAME,
1657 null, -- MT_INFORMATION_TYPE,
1658 null,
1659 p_contact_name, -- MT_CONTACT_NAME
1660 p_contact_type, -- MT_CONTACT_TYPE
1661 p_a.ADDRESS_ID,
1662 p_a.BUSINESS_GROUP_ID,
1663 p_a.PERSON_ID,
1664 to_char(p_a.DATE_FROM,g_datefmt),
1665 p_a.PRIMARY_FLAG,
1666 p_a.STYLE,
1667 p_a.ADDRESS_LINE1,
1668 p_a.ADDRESS_LINE2,
1669 p_a.ADDRESS_LINE3,
1670 p_a.ADDRESS_TYPE,
1671 p_a.COMMENTS,
1672 p_a.COUNTRY,
1673 to_char(p_a.DATE_TO,g_datefmt),
1674 p_a.POSTAL_CODE,
1675 p_a.REGION_1,
1676 p_a.REGION_2,
1677 p_a.REGION_3,
1678 p_a.TELEPHONE_NUMBER_1,
1679 p_a.TELEPHONE_NUMBER_2,
1680 p_a.TELEPHONE_NUMBER_3,
1681 p_a.TOWN_OR_CITY,
1682 p_a.REQUEST_ID,
1683 p_a.PROGRAM_APPLICATION_ID,
1684 p_a.PROGRAM_ID,
1685 p_a.PROGRAM_UPDATE_DATE,
1686 p_a.ADDR_ATTRIBUTE_CATEGORY,
1687 p_a.ADDR_ATTRIBUTE1,
1688 p_a.ADDR_ATTRIBUTE2,
1689 p_a.ADDR_ATTRIBUTE3,
1690 p_a.ADDR_ATTRIBUTE4,
1691 p_a.ADDR_ATTRIBUTE5,
1692 p_a.ADDR_ATTRIBUTE6,
1693 p_a.ADDR_ATTRIBUTE7,
1694 p_a.ADDR_ATTRIBUTE8,
1695 p_a.ADDR_ATTRIBUTE9,
1696 p_a.ADDR_ATTRIBUTE10,
1697 p_a.ADDR_ATTRIBUTE11,
1698 p_a.ADDR_ATTRIBUTE12,
1699 p_a.ADDR_ATTRIBUTE13,
1700 p_a.ADDR_ATTRIBUTE14,
1701 p_a.ADDR_ATTRIBUTE15,
1702 p_a.ADDR_ATTRIBUTE16,
1703 p_a.ADDR_ATTRIBUTE17,
1704 p_a.ADDR_ATTRIBUTE18,
1705 p_a.ADDR_ATTRIBUTE19,
1706 p_a.ADDR_ATTRIBUTE20
1707 );
1708
1709 exception
1710 when others then
1711 put_line(l_proc||' - raised exception');
1712 raise;
1713
1714 end;
1715
1716 procedure update_address(p_transfer_name in varchar2,
1717 p_effective_date in date,
1718 p_a in per_addresses%rowtype,
1719 p_contact_name in varchar2 default null,
1720 p_contact_type in varchar2 default null)
1721 is
1722
1723 l_proc varchar2(72) := g_package||'update_address';
1724 l_x varchar2(1);
1725
1726 cursor cur_address is
1727 select 'x'
1728 from ghr_mt_addresses_v
1729 where mt_name = p_transfer_name
1730 and mt_person_id = to_char(p_a.person_id)
1731 and nvl(mt_contact_name,'null') = nvl(p_contact_name,'null')
1732 for update;
1733
1734 begin
1735
1736 if p_contact_name is null then
1737 put_line('update address');
1738 else
1739 put_line('update contact address');
1740 end if;
1741
1742 open cur_address;
1743 fetch cur_address into l_x;
1744
1745 if cur_address%notfound then
1746 close cur_address;
1747 hr_utility.set_message(8301, 'GHR_38549_UPDATE_NOT_FOUND');
1748 hr_utility.raise_error;
1749 end if;
1750
1751 update ghr_mt_addresses_v
1752 set
1753 MT_EFFECTIVE_DATE = p_effective_date,
1754 ADDRESS_ID = nvl(p_a.ADDRESS_ID,ADDRESS_ID),
1755 BUSINESS_GROUP_ID = nvl(p_a.BUSINESS_GROUP_ID,BUSINESS_GROUP_ID),
1756 PERSON_ID = nvl(p_a.PERSON_ID,PERSON_ID),
1757 DATE_FROM = nvl(to_char(p_a.DATE_FROM,g_datefmt),DATE_FROM),
1758 PRIMARY_FLAG = nvl(p_a.PRIMARY_FLAG,PRIMARY_FLAG),
1759 STYLE = nvl(p_a.STYLE,STYLE),
1760 ADDRESS_LINE1 = nvl(p_a.ADDRESS_LINE1,ADDRESS_LINE1),
1761 ADDRESS_LINE2 = nvl(p_a.ADDRESS_LINE2,ADDRESS_LINE2),
1762 ADDRESS_LINE3 = nvl(p_a.ADDRESS_LINE3,ADDRESS_LINE3),
1763 ADDRESS_TYPE = nvl(p_a.ADDRESS_TYPE,ADDRESS_TYPE),
1764 COMMENTS = nvl(p_a.COMMENTS,COMMENTS),
1765 COUNTRY = nvl(p_a.COUNTRY,COUNTRY),
1766 DATE_TO = nvl(to_char(p_a.DATE_TO,g_datefmt),DATE_TO),
1767 POSTAL_CODE = nvl(p_a.POSTAL_CODE,POSTAL_CODE),
1768 REGION_1 = nvl(p_a.REGION_1,REGION_1),
1769 REGION_2 = nvl(p_a.REGION_2,REGION_2),
1770 REGION_3 = nvl(p_a.REGION_3,REGION_3),
1771 TELEPHONE_NUMBER_1 = nvl(p_a.TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_1),
1772 TELEPHONE_NUMBER_2 = nvl(p_a.TELEPHONE_NUMBER_2,TELEPHONE_NUMBER_2),
1773 TELEPHONE_NUMBER_3 = nvl(p_a.TELEPHONE_NUMBER_3,TELEPHONE_NUMBER_3),
1774 TOWN_OR_CITY = nvl(p_a.TOWN_OR_CITY,TOWN_OR_CITY),
1775 REQUEST_ID = nvl(p_a.REQUEST_ID,REQUEST_ID),
1776 PROGRAM_APPLICATION_ID =
1777 nvl(p_a.PROGRAM_APPLICATION_ID,PROGRAM_APPLICATION_ID),
1778 PROGRAM_ID = nvl(p_a.PROGRAM_ID,PROGRAM_ID),
1779 ADDR_ATTRIBUTE_CATEGORY =
1780 nvl(p_a.ADDR_ATTRIBUTE_CATEGORY,ADDR_ATTRIBUTE_CATEGORY),
1781 ADDR_ATTRIBUTE1 = nvl(p_a.ADDR_ATTRIBUTE1,ADDR_ATTRIBUTE1),
1782 ADDR_ATTRIBUTE2 = nvl(p_a.ADDR_ATTRIBUTE2,ADDR_ATTRIBUTE2),
1783 ADDR_ATTRIBUTE3 = nvl(p_a.ADDR_ATTRIBUTE3,ADDR_ATTRIBUTE3),
1784 ADDR_ATTRIBUTE4 = nvl(p_a.ADDR_ATTRIBUTE4,ADDR_ATTRIBUTE4),
1785 ADDR_ATTRIBUTE5 = nvl(p_a.ADDR_ATTRIBUTE5,ADDR_ATTRIBUTE5),
1786 ADDR_ATTRIBUTE6 = nvl(p_a.ADDR_ATTRIBUTE6,ADDR_ATTRIBUTE6),
1787 ADDR_ATTRIBUTE7 = nvl(p_a.ADDR_ATTRIBUTE7,ADDR_ATTRIBUTE7),
1788 ADDR_ATTRIBUTE8 = nvl(p_a.ADDR_ATTRIBUTE8,ADDR_ATTRIBUTE8),
1789 ADDR_ATTRIBUTE9 = nvl(p_a.ADDR_ATTRIBUTE9,ADDR_ATTRIBUTE9),
1790 ADDR_ATTRIBUTE10 = nvl(p_a.ADDR_ATTRIBUTE10,ADDR_ATTRIBUTE10),
1791 ADDR_ATTRIBUTE11 = nvl(p_a.ADDR_ATTRIBUTE11,ADDR_ATTRIBUTE11),
1792 ADDR_ATTRIBUTE12 = nvl(p_a.ADDR_ATTRIBUTE12,ADDR_ATTRIBUTE12),
1793 ADDR_ATTRIBUTE13 = nvl(p_a.ADDR_ATTRIBUTE13,ADDR_ATTRIBUTE13),
1794 ADDR_ATTRIBUTE14 = nvl(p_a.ADDR_ATTRIBUTE14,ADDR_ATTRIBUTE14),
1795 ADDR_ATTRIBUTE15 = nvl(p_a.ADDR_ATTRIBUTE15,ADDR_ATTRIBUTE15),
1796 ADDR_ATTRIBUTE16 = nvl(p_a.ADDR_ATTRIBUTE16,ADDR_ATTRIBUTE16),
1797 ADDR_ATTRIBUTE17 = nvl(p_a.ADDR_ATTRIBUTE17,ADDR_ATTRIBUTE17),
1798 ADDR_ATTRIBUTE18 = nvl(p_a.ADDR_ATTRIBUTE18,ADDR_ATTRIBUTE18),
1799 ADDR_ATTRIBUTE19 = nvl(p_a.ADDR_ATTRIBUTE19,ADDR_ATTRIBUTE19),
1800 ADDR_ATTRIBUTE20 = nvl(p_a.ADDR_ATTRIBUTE20,ADDR_ATTRIBUTE20)
1801 where current of cur_address;
1802
1803 close cur_address;
1804
1805 exception
1806 when others then
1807 put_line(l_proc||' - raised exception');
1808 raise;
1809
1810 end;
1811
1812 procedure insert_assignment_f(p_transfer_name varchar2,
1813 p_effective_date date,
1814 p_a per_all_assignments_f%rowtype)
1815 is
1816
1817 l_proc varchar2(72) := g_package||'insert_assignment';
1818 l_table_name constant varchar2(30) := 'PER_ASSIGNMENTS_F';
1819 l_name varchar2(30);
1820
1821 begin
1822
1823 put('insert assignment');
1824
1825 who_called_me(p_name => l_name);
1826
1827 -- if this package body did NOT call this procedure then ckeck if
1828 -- the row to be inserted already exists, if so raise an error
1829 if nvl(l_name,'unknown') <> g_name then
1830 put_line(' - called by '|| l_name);
1831 if row_exists(
1832 p_transfer_name => p_transfer_name
1833 ,p_person_id => p_a.person_id
1834 ,p_table_name => l_table_name) then
1835
1836 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
1837 hr_utility.raise_error;
1838 end if;
1839 else
1840 put_line(' - called locally');
1841 end if;
1842
1843
1844 insert into ghr_mt_assignments_f_v (
1845 GHR_INTERFACE_ID,
1846 MT_PROCESS_DATE,
1847 MT_SOURCE,
1848 MT_EFFECTIVE_DATE,
1849 MT_NAME,
1850 MT_STATUS,
1851 MT_PERSON_ID,
1852 MT_TABLE_NAME,
1853 MT_INFORMATION_TYPE,
1854 MT_INTER_BG_TRANSFER,
1855 ASSIGNMENT_ID,
1856 EFFECTIVE_START_DATE,
1857 EFFECTIVE_END_DATE,
1858 BUSINESS_GROUP_ID,
1859 RECRUITER_ID,
1860 GRADE_ID,
1861 POSITION_ID,
1862 JOB_ID,
1863 ASSIGNMENT_STATUS_TYPE_ID,
1864 PAYROLL_ID,
1865 LOCATION_ID,
1866 PERSON_REFERRED_BY_ID,
1867 SUPERVISOR_ID,
1868 SPECIAL_CEILING_STEP_ID,
1869 PERSON_ID,
1870 RECRUITMENT_ACTIVITY_ID,
1871 SOURCE_ORGANIZATION_ID,
1872 ORGANIZATION_ID,
1873 PEOPLE_GROUP_ID,
1874 SOFT_CODING_KEYFLEX_ID,
1875 VACANCY_ID,
1876 PAY_BASIS_ID,
1877 ASSIGNMENT_SEQUENCE,
1878 ASSIGNMENT_TYPE,
1879 PRIMARY_FLAG,
1880 APPLICATION_ID,
1881 ASSIGNMENT_NUMBER,
1882 CHANGE_REASON,
1883 COMMENT_ID,
1884 DATE_PROBATION_END,
1885 DEFAULT_CODE_COMB_ID,
1886 EMPLOYMENT_CATEGORY,
1887 FREQUENCY,
1888 INTERNAL_ADDRESS_LINE,
1889 MANAGER_FLAG,
1890 NORMAL_HOURS,
1891 PERF_REVIEW_PERIOD,
1892 PERF_REVIEW_PERIOD_FREQUENCY,
1893 PERIOD_OF_SERVICE_ID,
1894 PROBATION_PERIOD,
1895 PROBATION_UNIT,
1896 SAL_REVIEW_PERIOD,
1897 SAL_REVIEW_PERIOD_FREQUENCY,
1898 SET_OF_BOOKS_ID,
1899 SOURCE_TYPE,
1900 TIME_NORMAL_FINISH,
1901 TIME_NORMAL_START,
1902 REQUEST_ID,
1903 PROGRAM_APPLICATION_ID,
1904 PROGRAM_ID,
1905 PROGRAM_UPDATE_DATE,
1906 ASS_ATTRIBUTE_CATEGORY,
1907 ASS_ATTRIBUTE1,
1908 ASS_ATTRIBUTE2,
1909 ASS_ATTRIBUTE3,
1910 ASS_ATTRIBUTE4,
1911 ASS_ATTRIBUTE5,
1912 ASS_ATTRIBUTE6,
1913 ASS_ATTRIBUTE7,
1914 ASS_ATTRIBUTE8,
1915 ASS_ATTRIBUTE9,
1916 ASS_ATTRIBUTE10,
1917 ASS_ATTRIBUTE11,
1918 ASS_ATTRIBUTE12,
1919 ASS_ATTRIBUTE13,
1920 ASS_ATTRIBUTE14,
1921 ASS_ATTRIBUTE15,
1922 ASS_ATTRIBUTE16,
1923 ASS_ATTRIBUTE17,
1924 ASS_ATTRIBUTE18,
1925 ASS_ATTRIBUTE19,
1926 ASS_ATTRIBUTE20,
1927 ASS_ATTRIBUTE21,
1928 ASS_ATTRIBUTE22,
1929 ASS_ATTRIBUTE23,
1930 ASS_ATTRIBUTE24,
1931 ASS_ATTRIBUTE25,
1932 ASS_ATTRIBUTE26,
1933 ASS_ATTRIBUTE27,
1934 ASS_ATTRIBUTE28,
1935 ASS_ATTRIBUTE29,
1936 ASS_ATTRIBUTE30,
1937 LAST_UPDATE_DATE,
1938 LAST_UPDATED_BY,
1939 LAST_UPDATE_LOGIN,
1940 CREATED_BY,
1941 CREATION_DATE,
1942 TITLE,
1943 OBJECT_VERSION_NUMBER
1944 )
1945 values (
1946 ghr_interface_s.nextval,
1947 sysdate,
1948 l_name,
1949 p_effective_date,
1950 p_transfer_name,
1951 null, -- MT_STATUS,
1952 p_a.person_id, -- MT_PERSON_ID,
1953 l_table_name, -- MT_TABLE_NAME,
1954 null, -- MT_INFORMATION_TYPE,
1955 null,
1956 p_a.ASSIGNMENT_ID,
1957 to_char(p_a.EFFECTIVE_START_DATE,g_datefmt),
1958 to_char(p_a.EFFECTIVE_END_DATE,g_datefmt),
1959 p_a.BUSINESS_GROUP_ID,
1960 p_a.RECRUITER_ID,
1961 p_a.GRADE_ID,
1962 p_a.POSITION_ID,
1963 p_a.JOB_ID,
1964 p_a.ASSIGNMENT_STATUS_TYPE_ID,
1965 p_a.PAYROLL_ID,
1966 p_a.LOCATION_ID,
1967 p_a.PERSON_REFERRED_BY_ID,
1968 p_a.SUPERVISOR_ID,
1969 p_a.SPECIAL_CEILING_STEP_ID,
1970 p_a.PERSON_ID,
1971 p_a.RECRUITMENT_ACTIVITY_ID,
1972 p_a.SOURCE_ORGANIZATION_ID,
1973 p_a.ORGANIZATION_ID,
1974 p_a.PEOPLE_GROUP_ID,
1975 p_a.SOFT_CODING_KEYFLEX_ID,
1976 p_a.VACANCY_ID,
1977 p_a.PAY_BASIS_ID,
1978 p_a.ASSIGNMENT_SEQUENCE,
1979 p_a.ASSIGNMENT_TYPE,
1980 p_a.PRIMARY_FLAG,
1981 p_a.APPLICATION_ID,
1982 p_a.ASSIGNMENT_NUMBER,
1983 p_a.CHANGE_REASON,
1984 p_a.COMMENT_ID,
1985 to_char(p_a.DATE_PROBATION_END,g_datefmt),
1986 p_a.DEFAULT_CODE_COMB_ID,
1987 p_a.EMPLOYMENT_CATEGORY,
1988 p_a.FREQUENCY,
1989 p_a.INTERNAL_ADDRESS_LINE,
1990 p_a.MANAGER_FLAG,
1991 p_a.NORMAL_HOURS,
1992 p_a.PERF_REVIEW_PERIOD,
1993 p_a.PERF_REVIEW_PERIOD_FREQUENCY,
1994 p_a.PERIOD_OF_SERVICE_ID,
1995 p_a.PROBATION_PERIOD,
1996 p_a.PROBATION_UNIT,
1997 p_a.SAL_REVIEW_PERIOD,
1998 p_a.SAL_REVIEW_PERIOD_FREQUENCY,
1999 p_a.SET_OF_BOOKS_ID,
2000 p_a.SOURCE_TYPE,
2001 p_a.TIME_NORMAL_FINISH,
2002 p_a.TIME_NORMAL_START,
2003 p_a.REQUEST_ID,
2004 p_a.PROGRAM_APPLICATION_ID,
2005 p_a.PROGRAM_ID,
2006 to_char(p_a.PROGRAM_UPDATE_DATE,g_datefmt),
2007 p_a.ASS_ATTRIBUTE_CATEGORY,
2008 p_a.ASS_ATTRIBUTE1,
2009 p_a.ASS_ATTRIBUTE2,
2010 p_a.ASS_ATTRIBUTE3,
2011 p_a.ASS_ATTRIBUTE4,
2012 p_a.ASS_ATTRIBUTE5,
2013 p_a.ASS_ATTRIBUTE6,
2014 p_a.ASS_ATTRIBUTE7,
2015 p_a.ASS_ATTRIBUTE8,
2016 p_a.ASS_ATTRIBUTE9,
2017 p_a.ASS_ATTRIBUTE10,
2018 p_a.ASS_ATTRIBUTE11,
2019 p_a.ASS_ATTRIBUTE12,
2020 p_a.ASS_ATTRIBUTE13,
2021 p_a.ASS_ATTRIBUTE14,
2022 p_a.ASS_ATTRIBUTE15,
2023 p_a.ASS_ATTRIBUTE16,
2024 p_a.ASS_ATTRIBUTE17,
2025 p_a.ASS_ATTRIBUTE18,
2026 p_a.ASS_ATTRIBUTE19,
2027 p_a.ASS_ATTRIBUTE20,
2028 p_a.ASS_ATTRIBUTE21,
2029 p_a.ASS_ATTRIBUTE22,
2030 p_a.ASS_ATTRIBUTE23,
2031 p_a.ASS_ATTRIBUTE24,
2032 p_a.ASS_ATTRIBUTE25,
2033 p_a.ASS_ATTRIBUTE26,
2034 p_a.ASS_ATTRIBUTE27,
2035 p_a.ASS_ATTRIBUTE28,
2036 p_a.ASS_ATTRIBUTE29,
2037 p_a.ASS_ATTRIBUTE30,
2038 to_char(p_a.LAST_UPDATE_DATE,g_datefmt),
2039 p_a.LAST_UPDATED_BY,
2040 p_a.LAST_UPDATE_LOGIN,
2041 p_a.CREATED_BY,
2042 to_char(p_a.CREATION_DATE,g_datefmt),
2043 p_a.TITLE,
2044 p_a.OBJECT_VERSION_NUMBER
2045 );
2046
2047 exception
2048 when others then
2049 put_line(l_proc||' - raised exception');
2050 raise;
2051
2052 end;
2053
2054 procedure update_assignment_f(p_transfer_name varchar2,
2055 p_effective_date date,
2056 p_a per_all_assignments_f%rowtype)
2057 is
2058
2059 l_proc varchar2(72) := g_package||'insert_assignment';
2060 l_x varchar2(1);
2061
2062 cursor cur_assignment is
2063 select 'x'
2064 from ghr_mt_assignments_f_v
2065 where mt_name = p_transfer_name
2066 and mt_person_id = to_char(p_a.person_id)
2067 for update;
2068
2069 begin
2070
2071 put_line('update assignment');
2072
2073 open cur_assignment;
2074 fetch cur_assignment into l_x;
2075
2076 if cur_assignment%notfound then
2077 close cur_assignment;
2078 hr_utility.set_message(8301, 'GHR_38549_UPDATE_NOT_FOUND');
2079 hr_utility.raise_error;
2080 end if;
2081
2082 update ghr_mt_assignments_f_v
2083 set
2084 MT_EFFECTIVE_DATE = p_effective_date,
2085 ASSIGNMENT_ID = nvl(p_a.ASSIGNMENT_ID,ASSIGNMENT_ID),
2086 EFFECTIVE_START_DATE =
2087 nvl(to_char(p_a.EFFECTIVE_START_DATE,g_datefmt),
2088 EFFECTIVE_START_DATE),
2089 EFFECTIVE_END_DATE = nvl(to_char(p_a.EFFECTIVE_END_DATE,g_datefmt),
2090 EFFECTIVE_END_DATE),
2091 BUSINESS_GROUP_ID = nvl(p_a.BUSINESS_GROUP_ID,BUSINESS_GROUP_ID),
2092 RECRUITER_ID = nvl(p_a.RECRUITER_ID,RECRUITER_ID),
2093 GRADE_ID = nvl(p_a.GRADE_ID,GRADE_ID),
2094 POSITION_ID = nvl(p_a.POSITION_ID,POSITION_ID),
2095 JOB_ID = nvl(p_a.JOB_ID,JOB_ID),
2096 ASSIGNMENT_STATUS_TYPE_ID =
2097 nvl(p_a.ASSIGNMENT_STATUS_TYPE_ID,ASSIGNMENT_STATUS_TYPE_ID),
2098 PAYROLL_ID = nvl(p_a.PAYROLL_ID,PAYROLL_ID),
2099 LOCATION_ID = nvl(p_a.LOCATION_ID,LOCATION_ID),
2100 PERSON_REFERRED_BY_ID =
2101 nvl(p_a.PERSON_REFERRED_BY_ID,PERSON_REFERRED_BY_ID),
2102 SUPERVISOR_ID = nvl(p_a.SUPERVISOR_ID,SUPERVISOR_ID),
2103 SPECIAL_CEILING_STEP_ID =
2104 nvl(p_a.SPECIAL_CEILING_STEP_ID,SPECIAL_CEILING_STEP_ID),
2105 PERSON_ID = nvl(p_a.PERSON_ID,PERSON_ID),
2106 RECRUITMENT_ACTIVITY_ID =
2107 nvl(p_a.RECRUITMENT_ACTIVITY_ID,RECRUITMENT_ACTIVITY_ID),
2108 SOURCE_ORGANIZATION_ID =
2109 nvl(p_a.SOURCE_ORGANIZATION_ID,SOURCE_ORGANIZATION_ID),
2110 ORGANIZATION_ID = nvl(p_a.ORGANIZATION_ID,ORGANIZATION_ID),
2111 PEOPLE_GROUP_ID = nvl(p_a.PEOPLE_GROUP_ID,PEOPLE_GROUP_ID),
2112 SOFT_CODING_KEYFLEX_ID =
2113 nvl(p_a.SOFT_CODING_KEYFLEX_ID,SOFT_CODING_KEYFLEX_ID),
2114 VACANCY_ID = nvl(p_a.VACANCY_ID,VACANCY_ID),
2115 PAY_BASIS_ID = nvl(p_a.PAY_BASIS_ID,PAY_BASIS_ID),
2116 ASSIGNMENT_SEQUENCE = nvl(p_a.ASSIGNMENT_SEQUENCE,ASSIGNMENT_SEQUENCE),
2117 ASSIGNMENT_TYPE = nvl(p_a.ASSIGNMENT_TYPE,ASSIGNMENT_TYPE),
2118 PRIMARY_FLAG = nvl(p_a.PRIMARY_FLAG,PRIMARY_FLAG),
2119 APPLICATION_ID = nvl(p_a.APPLICATION_ID,APPLICATION_ID),
2120 ASSIGNMENT_NUMBER = nvl(p_a.ASSIGNMENT_NUMBER,ASSIGNMENT_NUMBER),
2121 CHANGE_REASON = nvl(p_a.CHANGE_REASON,CHANGE_REASON),
2122 COMMENT_ID = nvl(p_a.COMMENT_ID,COMMENT_ID),
2123 DATE_PROBATION_END =
2124 nvl(to_char(p_a.DATE_PROBATION_END,g_datefmt),DATE_PROBATION_END),
2125 DEFAULT_CODE_COMB_ID = nvl(p_a.DEFAULT_CODE_COMB_ID,DEFAULT_CODE_COMB_ID),
2126 EMPLOYMENT_CATEGORY = nvl(p_a.EMPLOYMENT_CATEGORY,EMPLOYMENT_CATEGORY),
2127 FREQUENCY = nvl(p_a.FREQUENCY,FREQUENCY),
2128 INTERNAL_ADDRESS_LINE =
2129 nvl(p_a.INTERNAL_ADDRESS_LINE,INTERNAL_ADDRESS_LINE),
2130 MANAGER_FLAG = nvl(p_a.MANAGER_FLAG,MANAGER_FLAG),
2131 NORMAL_HOURS = nvl(p_a.NORMAL_HOURS,NORMAL_HOURS),
2132 PERF_REVIEW_PERIOD = nvl(p_a.PERF_REVIEW_PERIOD,PERF_REVIEW_PERIOD),
2133 PERF_REVIEW_PERIOD_FREQUENCY =
2134 nvl(p_a.PERF_REVIEW_PERIOD_FREQUENCY,PERF_REVIEW_PERIOD_FREQUENCY),
2135 PERIOD_OF_SERVICE_ID = nvl(p_a.PERIOD_OF_SERVICE_ID,PERIOD_OF_SERVICE_ID),
2136 PROBATION_PERIOD = nvl(p_a.PROBATION_PERIOD,PROBATION_PERIOD),
2137 PROBATION_UNIT = nvl(p_a.PROBATION_UNIT,PROBATION_UNIT),
2138 SAL_REVIEW_PERIOD = nvl(p_a.SAL_REVIEW_PERIOD,SAL_REVIEW_PERIOD),
2139 SAL_REVIEW_PERIOD_FREQUENCY =
2140 nvl(p_a.SAL_REVIEW_PERIOD_FREQUENCY,SAL_REVIEW_PERIOD_FREQUENCY),
2141 SET_OF_BOOKS_ID = nvl(p_a.SET_OF_BOOKS_ID,SET_OF_BOOKS_ID),
2142 SOURCE_TYPE = nvl(p_a.SOURCE_TYPE,SOURCE_TYPE),
2143 TIME_NORMAL_FINISH = nvl(p_a.TIME_NORMAL_FINISH,TIME_NORMAL_FINISH),
2144 TIME_NORMAL_START = nvl(p_a.TIME_NORMAL_START,TIME_NORMAL_START),
2145 REQUEST_ID = nvl(p_a.REQUEST_ID,REQUEST_ID),
2146 PROGRAM_APPLICATION_ID =
2147 nvl(p_a.PROGRAM_APPLICATION_ID,PROGRAM_APPLICATION_ID),
2148 PROGRAM_ID = nvl(p_a.PROGRAM_ID,PROGRAM_ID),
2149 PROGRAM_UPDATE_DATE = nvl(p_a.PROGRAM_UPDATE_DATE,PROGRAM_UPDATE_DATE),
2150 ASS_ATTRIBUTE_CATEGORY =
2151 nvl(p_a.ASS_ATTRIBUTE_CATEGORY,ASS_ATTRIBUTE_CATEGORY),
2152 ASS_ATTRIBUTE1 = nvl(p_a.ASS_ATTRIBUTE1,ASS_ATTRIBUTE1),
2153 ASS_ATTRIBUTE2 = nvl(p_a.ASS_ATTRIBUTE2,ASS_ATTRIBUTE2),
2154 ASS_ATTRIBUTE3 = nvl(p_a.ASS_ATTRIBUTE3,ASS_ATTRIBUTE3),
2155 ASS_ATTRIBUTE4 = nvl(p_a.ASS_ATTRIBUTE4,ASS_ATTRIBUTE4),
2156 ASS_ATTRIBUTE5 = nvl(p_a.ASS_ATTRIBUTE5,ASS_ATTRIBUTE5),
2157 ASS_ATTRIBUTE6 = nvl(p_a.ASS_ATTRIBUTE6,ASS_ATTRIBUTE6),
2158 ASS_ATTRIBUTE7 = nvl(p_a.ASS_ATTRIBUTE7,ASS_ATTRIBUTE7),
2159 ASS_ATTRIBUTE8 = nvl(p_a.ASS_ATTRIBUTE8,ASS_ATTRIBUTE8),
2160 ASS_ATTRIBUTE9 = nvl(p_a.ASS_ATTRIBUTE9,ASS_ATTRIBUTE9),
2161 ASS_ATTRIBUTE10 = nvl(p_a.ASS_ATTRIBUTE10,ASS_ATTRIBUTE10),
2162 ASS_ATTRIBUTE11 = nvl(p_a.ASS_ATTRIBUTE11,ASS_ATTRIBUTE11),
2163 ASS_ATTRIBUTE12 = nvl(p_a.ASS_ATTRIBUTE12,ASS_ATTRIBUTE12),
2164 ASS_ATTRIBUTE13 = nvl(p_a.ASS_ATTRIBUTE13,ASS_ATTRIBUTE13),
2165 ASS_ATTRIBUTE14 = nvl(p_a.ASS_ATTRIBUTE14,ASS_ATTRIBUTE14),
2166 ASS_ATTRIBUTE15 = nvl(p_a.ASS_ATTRIBUTE15,ASS_ATTRIBUTE15),
2167 ASS_ATTRIBUTE16 = nvl(p_a.ASS_ATTRIBUTE16,ASS_ATTRIBUTE16),
2168 ASS_ATTRIBUTE17 = nvl(p_a.ASS_ATTRIBUTE17,ASS_ATTRIBUTE17),
2169 ASS_ATTRIBUTE18 = nvl(p_a.ASS_ATTRIBUTE18,ASS_ATTRIBUTE18),
2170 ASS_ATTRIBUTE19 = nvl(p_a.ASS_ATTRIBUTE19,ASS_ATTRIBUTE19),
2171 ASS_ATTRIBUTE20 = nvl(p_a.ASS_ATTRIBUTE20,ASS_ATTRIBUTE20),
2172 ASS_ATTRIBUTE21 = nvl(p_a.ASS_ATTRIBUTE21,ASS_ATTRIBUTE21),
2173 ASS_ATTRIBUTE22 = nvl(p_a.ASS_ATTRIBUTE22,ASS_ATTRIBUTE22),
2174 ASS_ATTRIBUTE23 = nvl(p_a.ASS_ATTRIBUTE23,ASS_ATTRIBUTE23),
2175 ASS_ATTRIBUTE24 = nvl(p_a.ASS_ATTRIBUTE24,ASS_ATTRIBUTE24),
2176 ASS_ATTRIBUTE25 = nvl(p_a.ASS_ATTRIBUTE25,ASS_ATTRIBUTE25),
2177 ASS_ATTRIBUTE26 = nvl(p_a.ASS_ATTRIBUTE26,ASS_ATTRIBUTE26),
2178 ASS_ATTRIBUTE27 = nvl(p_a.ASS_ATTRIBUTE27,ASS_ATTRIBUTE27),
2179 ASS_ATTRIBUTE28 = nvl(p_a.ASS_ATTRIBUTE28,ASS_ATTRIBUTE28),
2180 ASS_ATTRIBUTE29 = nvl(p_a.ASS_ATTRIBUTE29,ASS_ATTRIBUTE29),
2181 ASS_ATTRIBUTE30 = nvl(p_a.ASS_ATTRIBUTE30,ASS_ATTRIBUTE30),
2182 TITLE = nvl(p_a.TITLE,TITLE)
2183 where current of cur_assignment;
2184
2185 close cur_assignment;
2186
2187 exception
2188 when others then
2189 put_line(l_proc||' - raised exception');
2190 raise;
2191
2192 end;
2193
2194 procedure insert_assignment_ei(p_transfer_name varchar2,
2195 p_person_id number,
2196 p_effective_date date,
2197 p_a_ei per_assignment_extra_info%rowtype)
2198 is
2199
2200 l_proc varchar2(72) := g_package||'insert_assignment_ei';
2201 l_table_name constant varchar2(30) := 'PER_ASSIGNMENT_EXTRA_INFO';
2202 l_name varchar2(30);
2203
2204 l_info4_meaning varchar2(80) := null;
2205 l_info5_meaning varchar2(80) := null;
2206 l_info6_meaning varchar2(80) := null;
2207 l_info7_meaning varchar2(80) := null;
2208 l_info8_meaning varchar2(80) := null;
2209
2210 begin
2211
2212 put('insert assignment_ei');
2213
2214 -- check if p_a_ei contains data, if not return
2215 if p_a_ei.information_type is null then
2216 put_line(' - no data');
2217 return;
2218 end if;
2219 put_line(' - ok, info_type: '||p_a_ei.information_type);
2220
2221 who_called_me(p_name => l_name);
2222
2223 -- if this package body did NOT call this procedure then ckeck if
2224 -- the row to be inserted already exists, if so raise an error
2225 if nvl(l_name,'unknown') <> g_name then
2226 put_line('Called by '|| l_name);
2227 if row_exists(
2228 p_transfer_name => p_transfer_name
2229 ,p_person_id => p_person_id
2230 ,p_table_name => l_table_name
2231 ,p_info_type => p_a_ei.information_type) then
2232
2233 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
2234 hr_utility.raise_error;
2235 end if;
2236 else
2237 put_line('Called locally');
2238 end if;
2239
2240 if p_a_ei.information_type = 'GHR_US_ASG_SF52' then
2241 l_info4_meaning := get_code_meaning (
2242 p_lookup_type => 'GHR_US_TENURE',
2243 p_lookup_code => p_a_ei.aei_information4);
2244
2245 l_info5_meaning := get_code_meaning (
2246 p_lookup_type => 'GHR_US_ANNUITANT_INDICATOR',
2247 p_lookup_code => p_a_ei.aei_information5);
2248
2249 l_info6_meaning := get_code_meaning (
2250 p_lookup_type => 'GHR_US_PAY_RATE_DETERMINANT',
2251 p_lookup_code => p_a_ei.aei_information6);
2252
2253 l_info7_meaning := get_code_meaning (
2254 p_lookup_type => 'GHR_US_WORK_SCHEDULE',
2255 p_lookup_code => p_a_ei.aei_information7);
2256
2257 elsif p_a_ei.information_type = 'GHR_US_ASG_NON_SF52' then
2258 l_info6_meaning := get_code_meaning (
2259 p_lookup_type => 'GHR_US_NON_DISCLOSURE_AGREE',
2260 p_lookup_code => p_a_ei.aei_information6);
2261
2262 l_info8_meaning := get_code_meaning (
2263 p_lookup_type => 'GHR_US_PART_TIME_INDICATOR',
2264 p_lookup_code => p_a_ei.aei_information8);
2265
2266 end if;
2267
2268
2269 insert into ghr_mt_assignment_ei_v (
2270 GHR_INTERFACE_ID,
2271 MT_PROCESS_DATE,
2272 MT_SOURCE,
2273 MT_EFFECTIVE_DATE,
2274 MT_NAME,
2275 MT_STATUS,
2276 MT_PERSON_ID,
2277 MT_TABLE_NAME,
2278 MT_INFORMATION_TYPE,
2279 MT_INTER_BG_TRANSFER,
2280 ASSIGNMENT_EXTRA_INFO_ID,
2281 ASSIGNMENT_ID,
2282 INFORMATION_TYPE,
2283 REQUEST_ID,
2284 PROGRAM_APPLICATION_ID,
2285 PROGRAM_ID,
2286 PROGRAM_UPDATE_DATE,
2287 AEI_ATTRIBUTE_CATEGORY,
2288 AEI_ATTRIBUTE1,
2289 AEI_ATTRIBUTE2,
2290 AEI_ATTRIBUTE3,
2291 AEI_ATTRIBUTE4,
2292 AEI_ATTRIBUTE5,
2293 AEI_ATTRIBUTE6,
2294 AEI_ATTRIBUTE7,
2295 AEI_ATTRIBUTE8,
2296 AEI_ATTRIBUTE9,
2297 AEI_ATTRIBUTE10,
2298 AEI_ATTRIBUTE11,
2299 AEI_ATTRIBUTE12,
2300 AEI_ATTRIBUTE13,
2301 AEI_ATTRIBUTE14,
2302 AEI_ATTRIBUTE15,
2303 AEI_ATTRIBUTE16,
2304 AEI_ATTRIBUTE17,
2305 AEI_ATTRIBUTE18,
2306 AEI_ATTRIBUTE19,
2307 AEI_ATTRIBUTE20,
2308 LAST_UPDATE_DATE,
2309 LAST_UPDATED_BY,
2310 LAST_UPDATE_LOGIN,
2311 CREATED_BY,
2312 CREATION_DATE,
2313 AEI_INFORMATION_CATEGORY,
2314 AEI_INFORMATION1,
2315 AEI_INFORMATION2,
2316 AEI_INFORMATION3,
2317 AEI_INFORMATION4,
2318 AEI_INFORMATION5,
2319 AEI_INFORMATION6,
2320 AEI_INFORMATION7,
2321 AEI_INFORMATION8,
2322 AEI_INFORMATION9,
2323 AEI_INFORMATION10,
2324 AEI_INFORMATION11,
2325 AEI_INFORMATION12,
2326 AEI_INFORMATION13,
2327 AEI_INFORMATION14,
2328 AEI_INFORMATION15,
2329 AEI_INFORMATION16,
2330 AEI_INFORMATION17,
2331 AEI_INFORMATION18,
2332 AEI_INFORMATION19,
2333 AEI_INFORMATION20,
2334 AEI_INFORMATION21,
2335 AEI_INFORMATION22,
2336 AEI_INFORMATION23,
2337 AEI_INFORMATION24,
2338 AEI_INFORMATION25,
2339 AEI_INFORMATION26,
2340 AEI_INFORMATION27,
2341 AEI_INFORMATION28,
2342 AEI_INFORMATION29,
2343 AEI_INFORMATION30,
2344 OBJECT_VERSION_NUMBER,
2345 info4_meaning,
2346 info5_meaning,
2347 info6_meaning,
2348 info7_meaning,
2349 info8_meaning
2350 )
2351 values (
2352 ghr_interface_s.nextval,
2353 sysdate,
2354 l_name,
2355 p_effective_date,
2356 p_transfer_name,
2357 null, -- MT_STATUS,
2358 p_person_id, -- MT_PERSON_ID,
2359 l_table_name, -- MT_TABLE_NAME,
2360 p_a_ei.information_type, -- MT_INFORMATION_TYPE,
2361 null, -- MT_INTER_BG_TRANSFER
2362 p_a_ei.ASSIGNMENT_EXTRA_INFO_ID,
2363 p_a_ei.ASSIGNMENT_ID,
2364 p_a_ei.INFORMATION_TYPE,
2365 p_a_ei.REQUEST_ID,
2366 p_a_ei.PROGRAM_APPLICATION_ID,
2367 p_a_ei.PROGRAM_ID,
2368 to_char(p_a_ei.PROGRAM_UPDATE_DATE,g_datefmt),
2369 p_a_ei.AEI_ATTRIBUTE_CATEGORY,
2370 p_a_ei.AEI_ATTRIBUTE1,
2371 p_a_ei.AEI_ATTRIBUTE2,
2372 p_a_ei.AEI_ATTRIBUTE3,
2373 p_a_ei.AEI_ATTRIBUTE4,
2374 p_a_ei.AEI_ATTRIBUTE5,
2375 p_a_ei.AEI_ATTRIBUTE6,
2376 p_a_ei.AEI_ATTRIBUTE7,
2377 p_a_ei.AEI_ATTRIBUTE8,
2378 p_a_ei.AEI_ATTRIBUTE9,
2379 p_a_ei.AEI_ATTRIBUTE10,
2380 p_a_ei.AEI_ATTRIBUTE11,
2381 p_a_ei.AEI_ATTRIBUTE12,
2382 p_a_ei.AEI_ATTRIBUTE13,
2383 p_a_ei.AEI_ATTRIBUTE14,
2384 p_a_ei.AEI_ATTRIBUTE15,
2385 p_a_ei.AEI_ATTRIBUTE16,
2386 p_a_ei.AEI_ATTRIBUTE17,
2387 p_a_ei.AEI_ATTRIBUTE18,
2388 p_a_ei.AEI_ATTRIBUTE19,
2389 p_a_ei.AEI_ATTRIBUTE20,
2390 to_char(p_a_ei.LAST_UPDATE_DATE,g_datefmt),
2391 p_a_ei.LAST_UPDATED_BY,
2392 p_a_ei.LAST_UPDATE_LOGIN,
2393 p_a_ei.CREATED_BY,
2394 to_char(p_a_ei.CREATION_DATE,g_datefmt),
2395 p_a_ei.AEI_INFORMATION_CATEGORY,
2396 p_a_ei.AEI_INFORMATION1,
2397 p_a_ei.AEI_INFORMATION2,
2398 p_a_ei.AEI_INFORMATION3,
2399 p_a_ei.AEI_INFORMATION4,
2400 p_a_ei.AEI_INFORMATION5,
2401 p_a_ei.AEI_INFORMATION6,
2402 p_a_ei.AEI_INFORMATION7,
2403 p_a_ei.AEI_INFORMATION8,
2404 p_a_ei.AEI_INFORMATION9,
2405 p_a_ei.AEI_INFORMATION10,
2406 p_a_ei.AEI_INFORMATION11,
2407 p_a_ei.AEI_INFORMATION12,
2408 p_a_ei.AEI_INFORMATION13,
2409 p_a_ei.AEI_INFORMATION14,
2410 p_a_ei.AEI_INFORMATION15,
2411 p_a_ei.AEI_INFORMATION16,
2412 p_a_ei.AEI_INFORMATION17,
2413 p_a_ei.AEI_INFORMATION18,
2414 p_a_ei.AEI_INFORMATION19,
2415 p_a_ei.AEI_INFORMATION20,
2416 p_a_ei.AEI_INFORMATION21,
2417 p_a_ei.AEI_INFORMATION22,
2418 p_a_ei.AEI_INFORMATION23,
2419 p_a_ei.AEI_INFORMATION24,
2420 p_a_ei.AEI_INFORMATION25,
2421 p_a_ei.AEI_INFORMATION26,
2422 p_a_ei.AEI_INFORMATION27,
2423 p_a_ei.AEI_INFORMATION28,
2424 p_a_ei.AEI_INFORMATION29,
2425 p_a_ei.AEI_INFORMATION30,
2426 p_a_ei.OBJECT_VERSION_NUMBER,
2427 l_info4_meaning,
2428 l_info5_meaning,
2429 l_info6_meaning,
2430 l_info7_meaning,
2431 l_info8_meaning
2432 );
2433
2434 exception
2435 when others then
2436 put_line(l_proc||' - raised exception');
2437 raise;
2438
2439 end;
2440
2441 procedure update_assignment_ei(p_transfer_name varchar2,
2442 p_person_id number,
2443 p_effective_date date,
2444 p_a_ei per_assignment_extra_info%rowtype)
2445 is
2446
2447 l_proc varchar2(72) := g_package||'update_assignment_ei';
2448 l_x varchar2(1);
2449
2450 l_info4_meaning varchar2(80) := null;
2451 l_info5_meaning varchar2(80) := null;
2452 l_info6_meaning varchar2(80) := null;
2453 l_info7_meaning varchar2(80) := null;
2454 l_info8_meaning varchar2(80) := null;
2455
2456 cursor cur_assignment_ei is
2457 select 'x'
2458 from ghr_mt_assignment_ei_v
2459 where mt_name = p_transfer_name
2460 and mt_person_id = to_char(p_person_id)
2461 and mt_information_type = p_a_ei.information_type
2462 for update;
2463
2464 begin
2465
2466 put_line('update assignment_ei');
2467
2468 open cur_assignment_ei;
2469 fetch cur_assignment_ei into l_x;
2470
2471 if cur_assignment_ei%notfound then
2472 put_line('update row not found - calling insert');
2473 close cur_assignment_ei;
2474
2475 insert_assignment_ei(
2476 p_transfer_name => p_transfer_name,
2477 p_person_id => p_person_id,
2478 p_effective_date => p_effective_date,
2479 p_a_ei => p_a_ei
2480 );
2481
2482 return;
2483 end if;
2484
2485 if p_a_ei.information_type = 'GHR_US_ASG_SF52' then
2486 l_info4_meaning := get_code_meaning (
2487 p_lookup_type => 'GHR_US_TENURE',
2488 p_lookup_code => p_a_ei.aei_information4);
2489
2490 l_info5_meaning := get_code_meaning (
2491 p_lookup_type => 'GHR_US_ANNUITANT_INDICATOR',
2492 p_lookup_code => p_a_ei.aei_information5);
2493
2494 l_info6_meaning := get_code_meaning (
2495 p_lookup_type => 'GHR_US_PAY_RATE_DETERMINANT',
2496 p_lookup_code => p_a_ei.aei_information6);
2497
2498 l_info7_meaning := get_code_meaning (
2499 p_lookup_type => 'GHR_US_WORK_SCHEDULE',
2500 p_lookup_code => p_a_ei.aei_information7);
2501
2502 elsif p_a_ei.information_type = 'GHR_US_ASG_NON_SF52' then
2503 l_info6_meaning := get_code_meaning (
2504 p_lookup_type => 'GHR_US_NON_DISCLOSURE_AGREE',
2505 p_lookup_code => p_a_ei.aei_information6);
2506
2507 l_info8_meaning := get_code_meaning (
2508 p_lookup_type => 'GHR_US_PART_TIME_INDICATOR',
2509 p_lookup_code => p_a_ei.aei_information8);
2510
2511 end if;
2512
2513 update ghr_mt_assignment_ei_v
2514 set
2515 MT_EFFECTIVE_DATE = p_effective_date,
2516 ASSIGNMENT_EXTRA_INFO_ID =
2517 nvl(p_a_ei.ASSIGNMENT_EXTRA_INFO_ID,ASSIGNMENT_EXTRA_INFO_ID),
2518 ASSIGNMENT_ID = nvl(p_a_ei.ASSIGNMENT_ID,ASSIGNMENT_ID),
2519 INFORMATION_TYPE = nvl(p_a_ei.INFORMATION_TYPE,INFORMATION_TYPE),
2520 REQUEST_ID = nvl(p_a_ei.REQUEST_ID,REQUEST_ID),
2521 PROGRAM_APPLICATION_ID =
2522 nvl(p_a_ei.PROGRAM_APPLICATION_ID,PROGRAM_APPLICATION_ID),
2523 PROGRAM_ID = nvl(p_a_ei.PROGRAM_ID,PROGRAM_ID),
2524 PROGRAM_UPDATE_DATE =
2525 nvl(to_char(p_a_ei.PROGRAM_UPDATE_DATE,g_datefmt),
2526 PROGRAM_UPDATE_DATE),
2527 AEI_ATTRIBUTE_CATEGORY =
2528 nvl(p_a_ei.AEI_ATTRIBUTE_CATEGORY,AEI_ATTRIBUTE_CATEGORY),
2529 AEI_ATTRIBUTE1 = nvl(p_a_ei.AEI_ATTRIBUTE1,AEI_ATTRIBUTE1),
2530 AEI_ATTRIBUTE2 = nvl(p_a_ei.AEI_ATTRIBUTE2,AEI_ATTRIBUTE2),
2531 AEI_ATTRIBUTE3 = nvl(p_a_ei.AEI_ATTRIBUTE3,AEI_ATTRIBUTE3),
2532 AEI_ATTRIBUTE4 = nvl(p_a_ei.AEI_ATTRIBUTE4,AEI_ATTRIBUTE4),
2533 AEI_ATTRIBUTE5 = nvl(p_a_ei.AEI_ATTRIBUTE5,AEI_ATTRIBUTE5),
2534 AEI_ATTRIBUTE6 = nvl(p_a_ei.AEI_ATTRIBUTE6,AEI_ATTRIBUTE6),
2535 AEI_ATTRIBUTE7 = nvl(p_a_ei.AEI_ATTRIBUTE7,AEI_ATTRIBUTE7),
2536 AEI_ATTRIBUTE8 = nvl(p_a_ei.AEI_ATTRIBUTE8,AEI_ATTRIBUTE8),
2537 AEI_ATTRIBUTE9 = nvl(p_a_ei.AEI_ATTRIBUTE9,AEI_ATTRIBUTE9),
2538 AEI_ATTRIBUTE10 = nvl(p_a_ei.AEI_ATTRIBUTE10,AEI_ATTRIBUTE10),
2539 AEI_ATTRIBUTE11 = nvl(p_a_ei.AEI_ATTRIBUTE11,AEI_ATTRIBUTE11),
2540 AEI_ATTRIBUTE12 = nvl(p_a_ei.AEI_ATTRIBUTE12,AEI_ATTRIBUTE12),
2541 AEI_ATTRIBUTE13 = nvl(p_a_ei.AEI_ATTRIBUTE13,AEI_ATTRIBUTE13),
2542 AEI_ATTRIBUTE14 = nvl(p_a_ei.AEI_ATTRIBUTE14,AEI_ATTRIBUTE14),
2543 AEI_ATTRIBUTE15 = nvl(p_a_ei.AEI_ATTRIBUTE15,AEI_ATTRIBUTE15),
2544 AEI_ATTRIBUTE16 = nvl(p_a_ei.AEI_ATTRIBUTE16,AEI_ATTRIBUTE16),
2545 AEI_ATTRIBUTE17 = nvl(p_a_ei.AEI_ATTRIBUTE17,AEI_ATTRIBUTE17),
2546 AEI_ATTRIBUTE18 = nvl(p_a_ei.AEI_ATTRIBUTE18,AEI_ATTRIBUTE18),
2547 AEI_ATTRIBUTE19 = nvl(p_a_ei.AEI_ATTRIBUTE19,AEI_ATTRIBUTE19),
2548 AEI_ATTRIBUTE20 = nvl(p_a_ei.AEI_ATTRIBUTE20,AEI_ATTRIBUTE20),
2549 AEI_INFORMATION_CATEGORY =
2550 nvl(p_a_ei.AEI_INFORMATION_CATEGORY,AEI_INFORMATION_CATEGORY),
2551 AEI_INFORMATION1 = nvl(p_a_ei.AEI_INFORMATION1,AEI_INFORMATION1),
2552 AEI_INFORMATION2 = nvl(p_a_ei.AEI_INFORMATION2,AEI_INFORMATION2),
2553 --AEI_INFORMATION3 = nvl(p_a_ei.AEI_INFORMATION3,AEI_INFORMATION3),
2554 -----bug 2412656 Date Arrived Per
2555 AEI_INFORMATION3 = decode(p_a_ei.information_type,'GHR_US_ASG_NON_SF52',
2556 p_a_ei.AEI_INFORMATION3, nvl(p_a_ei.AEI_INFORMATION3,AEI_INFORMATION3)),
2557 AEI_INFORMATION4 = nvl(p_a_ei.AEI_INFORMATION4,AEI_INFORMATION4),
2558 AEI_INFORMATION5 = nvl(p_a_ei.AEI_INFORMATION5,AEI_INFORMATION5),
2559 --AEI_INFORMATION6 = nvl(p_a_ei.AEI_INFORMATION6,AEI_INFORMATION6),
2560 -----bug 2412656 Non Disclosure Agr.
2561 AEI_INFORMATION6 = decode(p_a_ei.information_type,'GHR_US_ASG_NON_SF52',
2562 p_a_ei.AEI_INFORMATION6, nvl(p_a_ei.AEI_INFORMATION6,AEI_INFORMATION6)),
2563 AEI_INFORMATION7 = nvl(p_a_ei.AEI_INFORMATION7,AEI_INFORMATION7),
2564 --AEI_INFORMATION8 = nvl(p_a_ei.AEI_INFORMATION8,AEI_INFORMATION8),
2565 -----bug 2412656 Part time Indicator and Part Time Hrs
2566 AEI_INFORMATION8 = decode(p_a_ei.information_type,'GHR_US_ASG_NON_SF52',
2567 p_a_ei.AEI_INFORMATION8,'GHR_US_ASG_SF52',
2568 p_a_ei.AEI_INFORMATION8, nvl(p_a_ei.AEI_INFORMATION8,AEI_INFORMATION8)),
2569 AEI_INFORMATION9 = nvl(p_a_ei.AEI_INFORMATION9,AEI_INFORMATION9),
2570 AEI_INFORMATION10 = nvl(p_a_ei.AEI_INFORMATION10,AEI_INFORMATION10),
2571 AEI_INFORMATION11 = nvl(p_a_ei.AEI_INFORMATION11,AEI_INFORMATION11),
2572 AEI_INFORMATION12 = nvl(p_a_ei.AEI_INFORMATION12,AEI_INFORMATION12),
2573 AEI_INFORMATION13 = nvl(p_a_ei.AEI_INFORMATION13,AEI_INFORMATION13),
2574 AEI_INFORMATION14 = nvl(p_a_ei.AEI_INFORMATION14,AEI_INFORMATION14),
2575 AEI_INFORMATION15 = nvl(p_a_ei.AEI_INFORMATION15,AEI_INFORMATION15),
2576 AEI_INFORMATION16 = nvl(p_a_ei.AEI_INFORMATION16,AEI_INFORMATION16),
2577 AEI_INFORMATION17 = nvl(p_a_ei.AEI_INFORMATION17,AEI_INFORMATION17),
2578 AEI_INFORMATION18 = nvl(p_a_ei.AEI_INFORMATION18,AEI_INFORMATION18),
2579 AEI_INFORMATION19 = nvl(p_a_ei.AEI_INFORMATION19,AEI_INFORMATION19),
2580 AEI_INFORMATION20 = nvl(p_a_ei.AEI_INFORMATION20,AEI_INFORMATION20),
2581 AEI_INFORMATION21 = nvl(p_a_ei.AEI_INFORMATION21,AEI_INFORMATION21),
2582 AEI_INFORMATION22 = nvl(p_a_ei.AEI_INFORMATION22,AEI_INFORMATION22),
2583 AEI_INFORMATION23 = nvl(p_a_ei.AEI_INFORMATION23,AEI_INFORMATION23),
2584 AEI_INFORMATION24 = nvl(p_a_ei.AEI_INFORMATION24,AEI_INFORMATION24),
2585 AEI_INFORMATION25 = nvl(p_a_ei.AEI_INFORMATION25,AEI_INFORMATION25),
2586 AEI_INFORMATION26 = nvl(p_a_ei.AEI_INFORMATION26,AEI_INFORMATION26),
2587 AEI_INFORMATION27 = nvl(p_a_ei.AEI_INFORMATION27,AEI_INFORMATION27),
2588 AEI_INFORMATION28 = nvl(p_a_ei.AEI_INFORMATION28,AEI_INFORMATION28),
2589 AEI_INFORMATION29 = nvl(p_a_ei.AEI_INFORMATION29,AEI_INFORMATION29),
2590 AEI_INFORMATION30 = nvl(p_a_ei.AEI_INFORMATION30,AEI_INFORMATION30),
2591 info4_meaning = nvl(l_info4_meaning,info4_meaning),
2592 info5_meaning = nvl(l_info5_meaning,info5_meaning),
2593 --info6_meaning = nvl(l_info6_meaning,info6_meaning),
2594 -----bug 2412656 Non Disclosure Agr.
2595 info6_meaning = decode(p_a_ei.information_type,'GHR_US_ASG_NON_SF52',
2596 l_info6_meaning, nvl(l_info6_meaning,info6_meaning)),
2597 info7_meaning = nvl(l_info7_meaning,info7_meaning),
2598 --info8_meaning = nvl(l_info8_meaning,info8_meaning)
2599 -----bug 2412656 Part time Indicator
2600 info8_meaning = decode(p_a_ei.information_type,'GHR_US_ASG_NON_SF52',
2601 l_info8_meaning, nvl(l_info8_meaning,info8_meaning))
2602 where current of cur_assignment_ei;
2603
2604 close cur_assignment_ei;
2605
2606 exception
2607 when others then
2608 put_line(l_proc||' - raised exception');
2609 raise;
2610
2611 end;
2612
2613 procedure insert_position(p_transfer_name varchar2,
2614 p_person_id number,
2615 p_effective_date date,
2616 p_pos hr_all_positions_f%rowtype)
2617 is
2618
2619 l_proc varchar2(72) := g_package||'insert_position';
2620 l_table_name constant varchar2(30) := 'PER_POSITIONS';
2621 l_name varchar2(30);
2622
2623 begin
2624
2625 put('insert position');
2626
2627 who_called_me(p_name => l_name);
2628
2629 -- if this package body did NOT call this procedure then ckeck if
2630 -- the row to be inserted already exists, if so raise an error
2631 if nvl(l_name,'unknown') <> g_name then
2632 put_line(' - called by '|| l_name);
2633 if row_exists(
2634 p_transfer_name => p_transfer_name
2635 ,p_person_id => p_person_id
2636 ,p_table_name => l_table_name) then
2637
2638 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
2639 hr_utility.raise_error;
2640 end if;
2641 else
2642 put_line(' - called locally');
2643 end if;
2644
2645 insert into ghr_mt_positions_v (
2646 GHR_INTERFACE_ID,
2647 MT_PROCESS_DATE,
2648 MT_SOURCE,
2649 MT_EFFECTIVE_DATE,
2650 MT_NAME,
2651 MT_STATUS,
2652 MT_PERSON_ID,
2653 MT_TABLE_NAME,
2654 MT_INFORMATION_TYPE,
2655 MT_INTER_BG_TRANSFER,
2656 POSITION_ID,
2657 BUSINESS_GROUP_ID,
2658 JOB_ID,
2659 ORGANIZATION_ID,
2660 SUCCESSOR_POSITION_ID,
2661 RELIEF_POSITION_ID,
2662 LOCATION_ID,
2663 POSITION_DEFINITION_ID,
2664 DATE_EFFECTIVE,
2665 COMMENTS,
2666 DATE_END,
2667 FREQUENCY,
2668 NAME,
2669 PROBATION_PERIOD,
2670 PROBATION_PERIOD_UNITS,
2671 REPLACEMENT_REQUIRED_FLAG,
2672 TIME_NORMAL_FINISH,
2673 TIME_NORMAL_START,
2674 WORKING_HOURS,
2675 REQUEST_ID,
2676 PROGRAM_APPLICATION_ID,
2677 PROGRAM_ID,
2678 PROGRAM_UPDATE_DATE,
2679 ATTRIBUTE_CATEGORY,
2680 ATTRIBUTE1,
2681 ATTRIBUTE2,
2682 ATTRIBUTE3,
2683 ATTRIBUTE4,
2684 ATTRIBUTE5,
2685 ATTRIBUTE6,
2686 ATTRIBUTE7,
2687 ATTRIBUTE8,
2688 ATTRIBUTE9,
2689 ATTRIBUTE10,
2690 ATTRIBUTE11,
2691 ATTRIBUTE12,
2692 ATTRIBUTE13,
2693 ATTRIBUTE14,
2694 ATTRIBUTE15,
2695 ATTRIBUTE16,
2696 ATTRIBUTE17,
2697 ATTRIBUTE18,
2698 ATTRIBUTE19,
2699 ATTRIBUTE20,
2700 OBJECT_VERSION_NUMBER,
2701 LAST_UPDATE_DATE,
2702 LAST_UPDATED_BY,
2703 LAST_UPDATE_LOGIN,
2704 CREATED_BY,
2705 CREATION_DATE,
2706 STATUS
2707 )
2708 values (
2709 ghr_interface_s.nextval,
2710 sysdate,
2711 l_name,
2712 p_effective_date,
2713 p_transfer_name,
2714 null, -- MT_STATUS,
2715 p_person_id, -- MT_PERSON_ID,
2716 l_table_name, -- MT_TABLE_NAME,
2717 null, -- MT_INFORMATION_TYPE,
2718 null, -- MT_INTER_BG_TRANSFER
2719 p_pos.POSITION_ID,
2720 p_pos.BUSINESS_GROUP_ID,
2721 p_pos.JOB_ID,
2722 p_pos.ORGANIZATION_ID,
2723 p_pos.SUCCESSOR_POSITION_ID,
2724 p_pos.RELIEF_POSITION_ID,
2725 p_pos.LOCATION_ID,
2726 p_pos.POSITION_DEFINITION_ID,
2727 to_char(p_pos.DATE_EFFECTIVE,g_datefmt),
2728 p_pos.COMMENTS,
2729 to_char(p_pos.DATE_END,g_datefmt),
2730 p_pos.FREQUENCY,
2731 p_pos.NAME,
2732 p_pos.PROBATION_PERIOD,
2733 p_pos.PROBATION_PERIOD_UNIT_CD,
2734 p_pos.REPLACEMENT_REQUIRED_FLAG,
2735 p_pos.TIME_NORMAL_FINISH,
2736 p_pos.TIME_NORMAL_START,
2737 p_pos.WORKING_HOURS,
2738 p_pos.REQUEST_ID,
2739 p_pos.PROGRAM_APPLICATION_ID,
2740 p_pos.PROGRAM_ID,
2741 to_char(p_pos.PROGRAM_UPDATE_DATE,g_datefmt),
2742 p_pos.ATTRIBUTE_CATEGORY,
2743 p_pos.ATTRIBUTE1,
2744 p_pos.ATTRIBUTE2,
2745 p_pos.ATTRIBUTE3,
2746 p_pos.ATTRIBUTE4,
2747 p_pos.ATTRIBUTE5,
2748 p_pos.ATTRIBUTE6,
2749 p_pos.ATTRIBUTE7,
2750 p_pos.ATTRIBUTE8,
2751 p_pos.ATTRIBUTE9,
2752 p_pos.ATTRIBUTE10,
2753 p_pos.ATTRIBUTE11,
2754 p_pos.ATTRIBUTE12,
2755 p_pos.ATTRIBUTE13,
2756 p_pos.ATTRIBUTE14,
2757 p_pos.ATTRIBUTE15,
2758 p_pos.ATTRIBUTE16,
2759 p_pos.ATTRIBUTE17,
2760 p_pos.ATTRIBUTE18,
2761 p_pos.ATTRIBUTE19,
2762 p_pos.ATTRIBUTE20,
2763 p_pos.OBJECT_VERSION_NUMBER,
2764 to_char(p_pos.LAST_UPDATE_DATE,g_datefmt),
2765 p_pos.LAST_UPDATED_BY,
2766 p_pos.LAST_UPDATE_LOGIN,
2767 p_pos.CREATED_BY,
2768 to_char(p_pos.CREATION_DATE,g_datefmt),
2769 p_pos.STATUS
2770 );
2771
2772 exception
2773 when others then
2774 put_line(l_proc||' - raised exception');
2775 raise;
2776
2777 end;
2778
2779 procedure update_position(p_transfer_name varchar2,
2780 p_person_id number,
2781 p_effective_date date,
2782 p_pos hr_all_positions_f%rowtype)
2783 is
2784
2785 l_proc varchar2(72) := g_package||'update_position';
2786 l_x varchar2(1);
2787
2788 cursor cur_position is
2789 select 'x'
2790 from ghr_mt_positions_v
2791 where mt_name = p_transfer_name
2792 and mt_person_id = to_char(p_person_id)
2793 for update;
2794
2795 begin
2796
2797 put_line('update position');
2798
2799 open cur_position;
2800 fetch cur_position into l_x;
2801
2802 if cur_position%notfound then
2803 close cur_position;
2804 hr_utility.set_message(8301, 'GHR_38549_UPDATE_NOT_FOUND');
2805 hr_utility.raise_error;
2806 end if;
2807
2808 update ghr_mt_positions_v
2809 set
2810 MT_EFFECTIVE_DATE = p_effective_date,
2811 POSITION_ID = nvl(p_pos.POSITION_ID,POSITION_ID),
2812 BUSINESS_GROUP_ID = nvl(p_pos.BUSINESS_GROUP_ID,BUSINESS_GROUP_ID),
2813 JOB_ID = nvl(p_pos.JOB_ID,JOB_ID),
2814 ORGANIZATION_ID = nvl(p_pos.ORGANIZATION_ID,ORGANIZATION_ID),
2815 SUCCESSOR_POSITION_ID =
2816 nvl(p_pos.SUCCESSOR_POSITION_ID,SUCCESSOR_POSITION_ID),
2817 RELIEF_POSITION_ID = nvl(p_pos.RELIEF_POSITION_ID,RELIEF_POSITION_ID),
2818 LOCATION_ID = nvl(p_pos.LOCATION_ID,LOCATION_ID),
2819 POSITION_DEFINITION_ID =
2820 nvl(p_pos.POSITION_DEFINITION_ID,POSITION_DEFINITION_ID),
2821 DATE_EFFECTIVE = nvl(to_char(p_pos.DATE_EFFECTIVE,g_datefmt),
2822 DATE_EFFECTIVE),
2823 COMMENTS = nvl(p_pos.COMMENTS,COMMENTS),
2824 DATE_END = nvl(to_char(p_pos.DATE_END,g_datefmt),DATE_END),
2825 FREQUENCY = nvl(p_pos.FREQUENCY,FREQUENCY),
2826 NAME = nvl(p_pos.NAME,NAME),
2827 PROBATION_PERIOD = nvl(p_pos.PROBATION_PERIOD,PROBATION_PERIOD),
2828 PROBATION_PERIOD_UNITS =
2829 nvl(p_pos.PROBATION_PERIOD_UNIT_CD,PROBATION_PERIOD_UNITS),
2830 REPLACEMENT_REQUIRED_FLAG =
2831 nvl(p_pos.REPLACEMENT_REQUIRED_FLAG,REPLACEMENT_REQUIRED_FLAG),
2832 TIME_NORMAL_FINISH = nvl(p_pos.TIME_NORMAL_FINISH,TIME_NORMAL_FINISH),
2833 TIME_NORMAL_START = nvl(p_pos.TIME_NORMAL_START,TIME_NORMAL_START),
2834 WORKING_HOURS = nvl(p_pos.WORKING_HOURS,WORKING_HOURS),
2835 REQUEST_ID = nvl(p_pos.REQUEST_ID,REQUEST_ID),
2836 PROGRAM_APPLICATION_ID =
2837 nvl(p_pos.PROGRAM_APPLICATION_ID,PROGRAM_APPLICATION_ID),
2838 PROGRAM_ID = nvl(p_pos.PROGRAM_ID,PROGRAM_ID),
2839 PROGRAM_UPDATE_DATE =
2840 nvl(to_char(p_pos.PROGRAM_UPDATE_DATE,g_datefmt),
2841 PROGRAM_UPDATE_DATE),
2842 ATTRIBUTE_CATEGORY = nvl(p_pos.ATTRIBUTE_CATEGORY,ATTRIBUTE_CATEGORY),
2843 ATTRIBUTE1 = nvl(p_pos.ATTRIBUTE1,ATTRIBUTE1),
2844 ATTRIBUTE2 = nvl(p_pos.ATTRIBUTE2,ATTRIBUTE2),
2845 ATTRIBUTE3 = nvl(p_pos.ATTRIBUTE3,ATTRIBUTE3),
2846 ATTRIBUTE4 = nvl(p_pos.ATTRIBUTE4,ATTRIBUTE4),
2847 ATTRIBUTE5 = nvl(p_pos.ATTRIBUTE5,ATTRIBUTE5),
2848 ATTRIBUTE6 = nvl(p_pos.ATTRIBUTE6,ATTRIBUTE6),
2849 ATTRIBUTE7 = nvl(p_pos.ATTRIBUTE7,ATTRIBUTE7),
2850 ATTRIBUTE8 = nvl(p_pos.ATTRIBUTE8,ATTRIBUTE8),
2851 ATTRIBUTE9 = nvl(p_pos.ATTRIBUTE9,ATTRIBUTE9),
2852 ATTRIBUTE10 = nvl(p_pos.ATTRIBUTE10,ATTRIBUTE10),
2853 ATTRIBUTE11 = nvl(p_pos.ATTRIBUTE11,ATTRIBUTE11),
2854 ATTRIBUTE12 = nvl(p_pos.ATTRIBUTE12,ATTRIBUTE12),
2855 ATTRIBUTE13 = nvl(p_pos.ATTRIBUTE13,ATTRIBUTE13),
2856 ATTRIBUTE14 = nvl(p_pos.ATTRIBUTE14,ATTRIBUTE14),
2857 ATTRIBUTE15 = nvl(p_pos.ATTRIBUTE15,ATTRIBUTE15),
2858 ATTRIBUTE16 = nvl(p_pos.ATTRIBUTE16,ATTRIBUTE16),
2859 ATTRIBUTE17 = nvl(p_pos.ATTRIBUTE17,ATTRIBUTE17),
2860 ATTRIBUTE18 = nvl(p_pos.ATTRIBUTE18,ATTRIBUTE18),
2861 ATTRIBUTE19 = nvl(p_pos.ATTRIBUTE19,ATTRIBUTE19),
2862 ATTRIBUTE20 = nvl(p_pos.ATTRIBUTE20,ATTRIBUTE20),
2863 STATUS = nvl(p_pos.STATUS,STATUS)
2864 where current of cur_position;
2865
2866 close cur_position;
2867
2868 exception
2869 when others then
2870 put_line(l_proc||' - raised exception');
2871 raise;
2872
2873 end;
2874
2875 procedure insert_position_ei(p_transfer_name varchar2,
2876 p_person_id number,
2877 p_effective_date date,
2878 p_pos_ei per_position_extra_info%rowtype)
2879 is
2880
2881 l_proc varchar2(72) := g_package||'insert_position_ei';
2882
2883 l_info3_meaning varchar2(80) := null;
2884 l_info7_meaning varchar2(80) := null;
2885 l_info8_meaning varchar2(80) := null;
2886 l_info11_meaning varchar2(80) := null;
2887 l_info12_meaning varchar2(80) := null;
2888 l_info13_meaning varchar2(80) := null;
2889 l_info14_meaning varchar2(80) := null;
2890 l_info16_meaning varchar2(80) := null;
2891
2892 l_table_name constant varchar2(30) := 'PER_POSITION_EXTRA_INFO';
2893 l_name varchar2(30);
2894
2895 begin
2896
2897 put('insert position_ei');
2898
2899 --
2900 -- check if p_pos_ei contains data, if not return
2901 --
2902 if p_pos_ei.information_type is null then
2903 put_line(' - no data');
2904 return;
2905 end if;
2906 put_line(' - ok, info_type: '||p_pos_ei.information_type);
2907
2908 who_called_me(p_name => l_name);
2909
2910 -- if this package body did NOT call this procedure then ckeck if
2911 -- the row to be inserted already exists, if so raise an error
2912 if nvl(l_name,'unknown') <> g_name then
2913 put_line('Called by '|| l_name);
2914 if row_exists(
2915 p_transfer_name => p_transfer_name
2916 ,p_person_id => p_person_id
2917 ,p_table_name => l_table_name
2918 ,p_info_type => p_pos_ei.information_type) then
2919
2920 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
2921 hr_utility.raise_error;
2922 end if;
2923 else
2924 put_line('Called locally');
2925 end if;
2926
2927
2928 if p_pos_ei.information_type = 'GHR_US_POS_GRP1' then
2929 l_info7_meaning := get_code_meaning (
2930 p_lookup_type => 'GHR_US_FLSA_CATEGORY',
2931 p_lookup_code => p_pos_ei.poei_information7);
2932
2933 l_info8_meaning := get_code_meaning (
2934 p_lookup_type => 'GHR_US_BARG_UNIT_STATUS',
2935 p_lookup_code => p_pos_ei.poei_information8);
2936
2937 l_info11_meaning := get_code_meaning (
2938 p_lookup_type => 'GHR_US_FUNCTIONAL_CLASS',
2939 p_lookup_code => p_pos_ei.poei_information11);
2940
2941 l_info12_meaning := p_pos_ei.poei_information12;
2942
2943 l_info16_meaning := get_code_meaning (
2944 p_lookup_type => 'GHR_US_SUPERVISORY_STATUS',
2945 p_lookup_code => p_pos_ei.poei_information16);
2946
2947 elsif p_pos_ei.information_type = 'GHR_US_POS_GRP2' then
2948 l_info3_meaning := get_code_meaning (
2949 p_lookup_type => 'GHR_US_POSITION_OCCUPIED',
2950 p_lookup_code => p_pos_ei.poei_information3);
2951
2952 l_info13_meaning := get_code_meaning (
2953 p_lookup_type => 'GHR_US_APPROPRIATION_CODE1',
2954 p_lookup_code => p_pos_ei.poei_information13);
2955
2956 l_info14_meaning := get_code_meaning (
2957 p_lookup_type => 'GHR_US_APPROPRIATION_CODE2',
2958 p_lookup_code => p_pos_ei.poei_information14);
2959
2960
2961 end if;
2962
2963
2964 insert into ghr_mt_position_ei_v (
2965 GHR_INTERFACE_ID,
2966 MT_PROCESS_DATE,
2967 MT_SOURCE,
2968 MT_EFFECTIVE_DATE,
2969 MT_NAME,
2970 MT_STATUS,
2971 MT_PERSON_ID,
2972 MT_TABLE_NAME,
2973 MT_INFORMATION_TYPE,
2974 MT_INTER_BG_TRANSFER,
2975 POSITION_EXTRA_INFO_ID,
2976 POSITION_ID,
2977 INFORMATION_TYPE,
2978 REQUEST_ID,
2979 PROGRAM_APPLICATION_ID,
2980 PROGRAM_ID,
2981 PROGRAM_UPDATE_DATE,
2982 POEI_ATTRIBUTE_CATEGORY,
2983 POEI_ATTRIBUTE1,
2984 POEI_ATTRIBUTE2,
2985 POEI_ATTRIBUTE3,
2986 POEI_ATTRIBUTE4,
2987 POEI_ATTRIBUTE5,
2988 POEI_ATTRIBUTE6,
2989 POEI_ATTRIBUTE7,
2990 POEI_ATTRIBUTE8,
2991 POEI_ATTRIBUTE9,
2992 POEI_ATTRIBUTE10,
2993 POEI_ATTRIBUTE11,
2994 POEI_ATTRIBUTE12,
2995 POEI_ATTRIBUTE13,
2996 POEI_ATTRIBUTE14,
2997 POEI_ATTRIBUTE15,
2998 POEI_ATTRIBUTE16,
2999 POEI_ATTRIBUTE17,
3000 POEI_ATTRIBUTE18,
3001 POEI_ATTRIBUTE19,
3002 POEI_ATTRIBUTE20,
3003 POEI_INFORMATION_CATEGORY,
3004 POEI_INFORMATION1,
3005 POEI_INFORMATION2,
3006 POEI_INFORMATION3,
3007 POEI_INFORMATION4,
3008 POEI_INFORMATION5,
3009 POEI_INFORMATION6,
3010 POEI_INFORMATION7,
3011 POEI_INFORMATION8,
3012 POEI_INFORMATION9,
3013 POEI_INFORMATION10,
3014 POEI_INFORMATION11,
3015 POEI_INFORMATION12,
3016 POEI_INFORMATION13,
3017 POEI_INFORMATION14,
3018 POEI_INFORMATION15,
3019 POEI_INFORMATION16,
3020 POEI_INFORMATION17,
3021 POEI_INFORMATION18,
3022 POEI_INFORMATION19,
3023 POEI_INFORMATION20,
3024 POEI_INFORMATION21,
3025 POEI_INFORMATION22,
3026 POEI_INFORMATION23,
3027 POEI_INFORMATION24,
3028 POEI_INFORMATION25,
3029 POEI_INFORMATION26,
3030 POEI_INFORMATION27,
3031 POEI_INFORMATION28,
3032 POEI_INFORMATION29,
3033 POEI_INFORMATION30,
3034 OBJECT_VERSION_NUMBER,
3035 LAST_UPDATE_DATE,
3036 LAST_UPDATED_BY,
3037 LAST_UPDATE_LOGIN,
3038 CREATED_BY,
3039 CREATION_DATE,
3040 info3_meaning,
3041 info7_meaning,
3042 info8_meaning,
3043 info11_meaning,
3044 info12_meaning,
3045 info13_meaning,
3046 info14_meaning,
3047 info16_meaning
3048 )
3049 values (
3050 ghr_interface_s.nextval,
3051 sysdate,
3052 l_name,
3053 p_effective_date,
3054 p_transfer_name,
3055 null, -- MT_STATUS,
3056 p_person_id, -- MT_PERSON_ID,
3057 l_table_name, -- MT_TABLE_NAME,
3058 p_pos_ei.information_type, -- MT_INFORMATION_TYPE,
3059 null, -- MT_INTER_BG_TRANSFER
3060 p_pos_ei.POSITION_EXTRA_INFO_ID,
3061 p_pos_ei.POSITION_ID,
3062 p_pos_ei.INFORMATION_TYPE,
3063 p_pos_ei.REQUEST_ID,
3064 p_pos_ei.PROGRAM_APPLICATION_ID,
3065 p_pos_ei.PROGRAM_ID,
3066 to_char(p_pos_ei.PROGRAM_UPDATE_DATE,g_datefmt),
3067 p_pos_ei.POEI_ATTRIBUTE_CATEGORY,
3068 p_pos_ei.POEI_ATTRIBUTE1,
3069 p_pos_ei.POEI_ATTRIBUTE2,
3070 p_pos_ei.POEI_ATTRIBUTE3,
3071 p_pos_ei.POEI_ATTRIBUTE4,
3072 p_pos_ei.POEI_ATTRIBUTE5,
3073 p_pos_ei.POEI_ATTRIBUTE6,
3074 p_pos_ei.POEI_ATTRIBUTE7,
3075 p_pos_ei.POEI_ATTRIBUTE8,
3076 p_pos_ei.POEI_ATTRIBUTE9,
3077 p_pos_ei.POEI_ATTRIBUTE10,
3078 p_pos_ei.POEI_ATTRIBUTE11,
3079 p_pos_ei.POEI_ATTRIBUTE12,
3080 p_pos_ei.POEI_ATTRIBUTE13,
3081 p_pos_ei.POEI_ATTRIBUTE14,
3082 p_pos_ei.POEI_ATTRIBUTE15,
3083 p_pos_ei.POEI_ATTRIBUTE16,
3084 p_pos_ei.POEI_ATTRIBUTE17,
3085 p_pos_ei.POEI_ATTRIBUTE18,
3086 p_pos_ei.POEI_ATTRIBUTE19,
3087 p_pos_ei.POEI_ATTRIBUTE20,
3088 p_pos_ei.POEI_INFORMATION_CATEGORY,
3089 p_pos_ei.POEI_INFORMATION1,
3090 p_pos_ei.POEI_INFORMATION2,
3091 p_pos_ei.POEI_INFORMATION3,
3092 p_pos_ei.POEI_INFORMATION4,
3093 p_pos_ei.POEI_INFORMATION5,
3094 p_pos_ei.POEI_INFORMATION6,
3095 p_pos_ei.POEI_INFORMATION7,
3096 p_pos_ei.POEI_INFORMATION8,
3097 p_pos_ei.POEI_INFORMATION9,
3098 p_pos_ei.POEI_INFORMATION10,
3099 p_pos_ei.POEI_INFORMATION11,
3100 p_pos_ei.POEI_INFORMATION12,
3101 p_pos_ei.POEI_INFORMATION13,
3102 p_pos_ei.POEI_INFORMATION14,
3103 p_pos_ei.POEI_INFORMATION15,
3104 p_pos_ei.POEI_INFORMATION16,
3105 p_pos_ei.POEI_INFORMATION17,
3106 p_pos_ei.POEI_INFORMATION18,
3107 p_pos_ei.POEI_INFORMATION19,
3108 p_pos_ei.POEI_INFORMATION20,
3109 p_pos_ei.POEI_INFORMATION21,
3110 p_pos_ei.POEI_INFORMATION22,
3111 p_pos_ei.POEI_INFORMATION23,
3112 p_pos_ei.POEI_INFORMATION24,
3113 p_pos_ei.POEI_INFORMATION25,
3114 p_pos_ei.POEI_INFORMATION26,
3115 p_pos_ei.POEI_INFORMATION27,
3116 p_pos_ei.POEI_INFORMATION28,
3117 p_pos_ei.POEI_INFORMATION29,
3118 p_pos_ei.POEI_INFORMATION30,
3119 p_pos_ei.OBJECT_VERSION_NUMBER,
3120 to_char(p_pos_ei.LAST_UPDATE_DATE,g_datefmt),
3121 p_pos_ei.LAST_UPDATED_BY,
3122 p_pos_ei.LAST_UPDATE_LOGIN,
3123 p_pos_ei.CREATED_BY,
3124 to_char(p_pos_ei.CREATION_DATE,g_datefmt),
3125 l_info3_meaning,
3126 l_info7_meaning,
3127 l_info8_meaning,
3128 l_info11_meaning,
3129 l_info12_meaning,
3130 l_info13_meaning,
3131 l_info14_meaning,
3132 l_info16_meaning
3133 );
3134
3135 exception
3136 when others then
3137 put_line(l_proc||' - raised exception');
3138 raise;
3139
3140 end;
3141
3142 procedure update_position_ei(p_transfer_name varchar2,
3143 p_person_id number,
3144 p_effective_date date,
3145 p_pos_ei per_position_extra_info%rowtype)
3146 is
3147
3148 l_proc varchar2(72) := g_package||'update_position_ei';
3149 l_x varchar2(1);
3150
3151 l_info3_meaning varchar2(80) := null;
3152 l_info7_meaning varchar2(80) := null;
3153 l_info8_meaning varchar2(80) := null;
3154 l_info11_meaning varchar2(80) := null;
3155 l_info12_meaning varchar2(80) := null;
3156 l_info13_meaning varchar2(80) := null;
3157 l_info14_meaning varchar2(80) := null;
3158 l_info16_meaning varchar2(80) := null;
3159
3160 cursor cur_position_ei is
3161 select 'x'
3162 from ghr_mt_position_ei_v
3163 where mt_name = p_transfer_name
3164 and mt_person_id = to_char(p_person_id)
3165 and mt_information_type = p_pos_ei.information_type
3166 for update;
3167
3168 begin
3169
3170 put_line('update position_ei');
3171
3172 open cur_position_ei;
3173 fetch cur_position_ei into l_x;
3174
3175 if cur_position_ei%notfound then
3176 put_line('update row not found - calling insert');
3177 close cur_position_ei;
3178
3179 insert_position_ei(
3180 p_transfer_name => p_transfer_name,
3181 p_person_id => p_person_id,
3182 p_effective_date => p_effective_date,
3183 p_pos_ei => p_pos_ei
3184 );
3185
3186 return;
3187 end if;
3188
3189 if p_pos_ei.information_type = 'GHR_US_POS_GRP1' then
3190 l_info7_meaning := get_code_meaning (
3191 p_lookup_type => 'GHR_US_FLSA_CATEGORY',
3192 p_lookup_code => p_pos_ei.poei_information7);
3193
3194 l_info8_meaning := get_code_meaning (
3195 p_lookup_type => 'GHR_US_BARG_UNIT_STATUS',
3196 p_lookup_code => p_pos_ei.poei_information8);
3197
3198 l_info11_meaning := get_code_meaning (
3199 p_lookup_type => 'GHR_US_FUNCTIONAL_CLASS',
3200 p_lookup_code => p_pos_ei.poei_information11);
3201
3202 l_info12_meaning := p_pos_ei.poei_information12;
3203
3204 l_info16_meaning := get_code_meaning (
3205 p_lookup_type => 'GHR_US_SUPERVISORY_STATUS',
3206 p_lookup_code => p_pos_ei.poei_information16);
3207
3208 elsif p_pos_ei.information_type = 'GHR_US_POS_GRP2' then
3209 l_info3_meaning := get_code_meaning (
3210 p_lookup_type => 'GHR_US_POSITION_OCCUPIED',
3211 p_lookup_code => p_pos_ei.poei_information3);
3212
3213 l_info13_meaning := get_code_meaning (
3214 p_lookup_type => 'GHR_US_APPROPRIATION_CODE1',
3215 p_lookup_code => p_pos_ei.poei_information13);
3216
3217 l_info14_meaning := get_code_meaning (
3218 p_lookup_type => 'GHR_US_APPROPRIATION_CODE2',
3219 p_lookup_code => p_pos_ei.poei_information14);
3220
3221
3222 end if;
3223
3224 update ghr_mt_position_ei_v
3225 set
3226 MT_EFFECTIVE_DATE = p_effective_date,
3227 POSITION_EXTRA_INFO_ID =
3228 nvl(p_pos_ei.POSITION_EXTRA_INFO_ID,POSITION_EXTRA_INFO_ID),
3229 POSITION_ID = nvl(p_pos_ei.POSITION_ID,POSITION_ID),
3230 INFORMATION_TYPE = nvl(p_pos_ei.INFORMATION_TYPE,INFORMATION_TYPE),
3231 REQUEST_ID = nvl(p_pos_ei.REQUEST_ID,REQUEST_ID),
3232 PROGRAM_APPLICATION_ID =
3233 nvl(p_pos_ei.PROGRAM_APPLICATION_ID,PROGRAM_APPLICATION_ID),
3234 PROGRAM_ID = nvl(p_pos_ei.PROGRAM_ID,PROGRAM_ID),
3235 PROGRAM_UPDATE_DATE =
3236 nvl(to_char(p_pos_ei.PROGRAM_UPDATE_DATE,g_datefmt),
3237 PROGRAM_UPDATE_DATE),
3238 POEI_ATTRIBUTE_CATEGORY =
3239 nvl(p_pos_ei.POEI_ATTRIBUTE_CATEGORY,POEI_ATTRIBUTE_CATEGORY),
3240 POEI_ATTRIBUTE1 = nvl(p_pos_ei.POEI_ATTRIBUTE1,POEI_ATTRIBUTE1),
3241 POEI_ATTRIBUTE2 = nvl(p_pos_ei.POEI_ATTRIBUTE2,POEI_ATTRIBUTE2),
3242 POEI_ATTRIBUTE3 = nvl(p_pos_ei.POEI_ATTRIBUTE3,POEI_ATTRIBUTE3),
3243 POEI_ATTRIBUTE4 = nvl(p_pos_ei.POEI_ATTRIBUTE4,POEI_ATTRIBUTE4),
3244 POEI_ATTRIBUTE5 = nvl(p_pos_ei.POEI_ATTRIBUTE5,POEI_ATTRIBUTE5),
3245 POEI_ATTRIBUTE6 = nvl(p_pos_ei.POEI_ATTRIBUTE6,POEI_ATTRIBUTE6),
3246 POEI_ATTRIBUTE7 = nvl(p_pos_ei.POEI_ATTRIBUTE7,POEI_ATTRIBUTE7),
3247 POEI_ATTRIBUTE8 = nvl(p_pos_ei.POEI_ATTRIBUTE8,POEI_ATTRIBUTE8),
3248 POEI_ATTRIBUTE9 = nvl(p_pos_ei.POEI_ATTRIBUTE9,POEI_ATTRIBUTE9),
3249 POEI_ATTRIBUTE10 = nvl(p_pos_ei.POEI_ATTRIBUTE10,POEI_ATTRIBUTE10),
3250 POEI_ATTRIBUTE11 = nvl(p_pos_ei.POEI_ATTRIBUTE11,POEI_ATTRIBUTE11),
3251 POEI_ATTRIBUTE12 = nvl(p_pos_ei.POEI_ATTRIBUTE12,POEI_ATTRIBUTE12),
3252 POEI_ATTRIBUTE13 = nvl(p_pos_ei.POEI_ATTRIBUTE13,POEI_ATTRIBUTE13),
3253 POEI_ATTRIBUTE14 = nvl(p_pos_ei.POEI_ATTRIBUTE14,POEI_ATTRIBUTE14),
3254 POEI_ATTRIBUTE15 = nvl(p_pos_ei.POEI_ATTRIBUTE15,POEI_ATTRIBUTE15),
3255 POEI_ATTRIBUTE16 = nvl(p_pos_ei.POEI_ATTRIBUTE16,POEI_ATTRIBUTE16),
3256 POEI_ATTRIBUTE17 = nvl(p_pos_ei.POEI_ATTRIBUTE17,POEI_ATTRIBUTE17),
3257 POEI_ATTRIBUTE18 = nvl(p_pos_ei.POEI_ATTRIBUTE18,POEI_ATTRIBUTE18),
3258 POEI_ATTRIBUTE19 = nvl(p_pos_ei.POEI_ATTRIBUTE19,POEI_ATTRIBUTE19),
3259 POEI_ATTRIBUTE20 = nvl(p_pos_ei.POEI_ATTRIBUTE20,POEI_ATTRIBUTE20),
3260 POEI_INFORMATION_CATEGORY =
3261 nvl(p_pos_ei.POEI_INFORMATION_CATEGORY,POEI_INFORMATION_CATEGORY),
3262 POEI_INFORMATION1 = nvl(p_pos_ei.POEI_INFORMATION1,POEI_INFORMATION1),
3263 POEI_INFORMATION2 = nvl(p_pos_ei.POEI_INFORMATION2,POEI_INFORMATION2),
3264 POEI_INFORMATION3 = nvl(p_pos_ei.POEI_INFORMATION3,POEI_INFORMATION3),
3265 POEI_INFORMATION4 = nvl(p_pos_ei.POEI_INFORMATION4,POEI_INFORMATION4),
3266 POEI_INFORMATION5 = nvl(p_pos_ei.POEI_INFORMATION5,POEI_INFORMATION5),
3267 POEI_INFORMATION6 = nvl(p_pos_ei.POEI_INFORMATION6,POEI_INFORMATION6),
3268 POEI_INFORMATION7 = nvl(p_pos_ei.POEI_INFORMATION7,POEI_INFORMATION7),
3269 POEI_INFORMATION8 = nvl(p_pos_ei.POEI_INFORMATION8,POEI_INFORMATION8),
3270 POEI_INFORMATION9 = nvl(p_pos_ei.POEI_INFORMATION9,POEI_INFORMATION9),
3271 POEI_INFORMATION10 = nvl(p_pos_ei.POEI_INFORMATION10,POEI_INFORMATION10),
3272 POEI_INFORMATION11 = nvl(p_pos_ei.POEI_INFORMATION11,POEI_INFORMATION11),
3273 --POEI_INFORMATION12 = nvl(p_pos_ei.POEI_INFORMATION12,POEI_INFORMATION12),
3274 -----bug 2412656 Position Title
3275 POEI_INFORMATION12 = decode(p_pos_ei.information_type,'GHR_US_POS_GRP1',
3276 p_pos_ei.POEI_INFORMATION12, nvl(p_pos_ei.POEI_INFORMATION12,POEI_INFORMATION12)),
3277 POEI_INFORMATION13 = nvl(p_pos_ei.POEI_INFORMATION13,POEI_INFORMATION13),
3278 POEI_INFORMATION14 = nvl(p_pos_ei.POEI_INFORMATION14,POEI_INFORMATION14),
3279 POEI_INFORMATION15 = nvl(p_pos_ei.POEI_INFORMATION15,POEI_INFORMATION15),
3280 POEI_INFORMATION16 = nvl(p_pos_ei.POEI_INFORMATION16,POEI_INFORMATION16),
3281 POEI_INFORMATION17 = nvl(p_pos_ei.POEI_INFORMATION17,POEI_INFORMATION17),
3282 POEI_INFORMATION18 = nvl(p_pos_ei.POEI_INFORMATION18,POEI_INFORMATION18),
3283 POEI_INFORMATION19 = nvl(p_pos_ei.POEI_INFORMATION19,POEI_INFORMATION19),
3284 POEI_INFORMATION20 = nvl(p_pos_ei.POEI_INFORMATION20,POEI_INFORMATION20),
3285 POEI_INFORMATION21 = nvl(p_pos_ei.POEI_INFORMATION21,POEI_INFORMATION21),
3286 POEI_INFORMATION22 = nvl(p_pos_ei.POEI_INFORMATION22,POEI_INFORMATION22),
3287 --POEI_INFORMATION23 = nvl(p_pos_ei.POEI_INFORMATION23,POEI_INFORMATION23),
3288 --Part Time Hours
3289 POEI_INFORMATION23 = decode(p_pos_ei.information_type,'GHR_US_POS_GRP1',
3290 p_pos_ei.POEI_INFORMATION23, nvl(p_pos_ei.POEI_INFORMATION23,POEI_INFORMATION23)),
3291 POEI_INFORMATION24 = nvl(p_pos_ei.POEI_INFORMATION24,POEI_INFORMATION24),
3292 POEI_INFORMATION25 = nvl(p_pos_ei.POEI_INFORMATION25,POEI_INFORMATION25),
3293 POEI_INFORMATION26 = nvl(p_pos_ei.POEI_INFORMATION26,POEI_INFORMATION26),
3294 POEI_INFORMATION27 = nvl(p_pos_ei.POEI_INFORMATION27,POEI_INFORMATION27),
3295 POEI_INFORMATION28 = nvl(p_pos_ei.POEI_INFORMATION28,POEI_INFORMATION28),
3296 POEI_INFORMATION29 = nvl(p_pos_ei.POEI_INFORMATION29,POEI_INFORMATION29),
3297 POEI_INFORMATION30 = nvl(p_pos_ei.POEI_INFORMATION30,POEI_INFORMATION30),
3298 info3_meaning = nvl(l_info3_meaning,info3_meaning),
3299 info7_meaning = nvl(l_info7_meaning,info7_meaning),
3300 info8_meaning = nvl(l_info8_meaning,info8_meaning),
3301 info11_meaning = nvl(l_info11_meaning,info11_meaning),
3302 --info12_meaning = nvl(l_info12_meaning,info12_meaning),
3303 -----bug 2412656 Position Title
3304 info12_meaning = decode(p_pos_ei.information_type,'GHR_US_POS_GRP1',
3305 l_info12_meaning, nvl(l_info12_meaning,info12_meaning)),
3306 info13_meaning = nvl(l_info13_meaning,info13_meaning),
3307 info14_meaning = nvl(l_info14_meaning,info14_meaning),
3308 info16_meaning = nvl(l_info16_meaning,info16_meaning)
3309 where current of cur_position_ei;
3310
3311 close cur_position_ei;
3312
3313 exception
3314 when others then
3315 put_line(l_proc||' - raised exception');
3316 raise;
3317
3318 end;
3319
3320
3321 procedure insert_position_defs(p_transfer_name varchar2,
3322 p_effective_date date,
3323 p_person_id number,
3324 p_flex_name varchar2,
3325 p_pos_defs per_position_definitions%rowtype)
3326 is
3327
3328 l_proc varchar2(72) := g_package||'insert_position_defs';
3329 l_table_name constant varchar2(30) := 'PER_POSITION_DEFINITIONS';
3330 l_name varchar2(30);
3331
3332 begin
3333
3334 put_line('insert position_defs -'||p_flex_name);
3335
3336 who_called_me(p_name => l_name);
3337
3338 -- if this package body did NOT call this procedure then ckeck if
3339 -- the row to be inserted already exists, if so raise an error
3340 if nvl(l_name,'unknown') <> g_name then
3341 put_line('Called by '|| l_name);
3342 if row_exists(
3343 p_transfer_name => p_transfer_name
3344 ,p_person_id => p_person_id
3345 ,p_table_name => l_table_name
3346 ,p_info_type => p_flex_name) then
3347
3348 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
3349 hr_utility.raise_error;
3350 end if;
3351 else
3352 put_line('Called locally');
3353 end if;
3354
3355 insert into ghr_mt_position_definitions_v (
3356 GHR_INTERFACE_ID,
3357 MT_PROCESS_DATE,
3358 MT_SOURCE,
3359 MT_EFFECTIVE_DATE,
3360 MT_NAME,
3361 MT_STATUS,
3362 MT_PERSON_ID,
3363 MT_TABLE_NAME,
3364 MT_INFORMATION_TYPE,
3365 MT_INTER_BG_TRANSFER,
3366 POSITION_DEFINITION_ID,
3367 ID_FLEX_NUM,
3368 SUMMARY_FLAG,
3369 ENABLED_FLAG,
3370 START_DATE_ACTIVE,
3371 END_DATE_ACTIVE,
3372 SEGMENT1,
3373 SEGMENT2,
3374 SEGMENT3,
3375 SEGMENT4,
3376 SEGMENT5,
3377 SEGMENT6,
3378 SEGMENT7,
3379 SEGMENT8,
3380 SEGMENT9,
3381 SEGMENT10,
3382 SEGMENT11,
3383 SEGMENT12,
3384 SEGMENT13,
3385 SEGMENT14,
3386 SEGMENT15,
3387 SEGMENT16,
3388 SEGMENT17,
3389 SEGMENT18,
3390 SEGMENT19,
3391 SEGMENT20,
3392 SEGMENT21,
3393 SEGMENT22,
3394 SEGMENT23,
3395 SEGMENT24,
3396 SEGMENT25,
3397 SEGMENT26,
3398 SEGMENT27,
3399 SEGMENT28,
3400 SEGMENT29,
3401 SEGMENT30,
3402 LAST_UPDATE_DATE,
3403 LAST_UPDATED_BY,
3404 LAST_UPDATE_LOGIN,
3405 CREATED_BY,
3406 CREATION_DATE
3407 )
3408 values (
3409 ghr_interface_s.nextval,
3410 sysdate,
3411 l_name,
3412 p_effective_date,
3413 p_transfer_name,
3414 null, -- MT_STATUS,
3415 p_person_id, -- MT_PERSON_ID,
3416 l_table_name, -- MT_TABLE_NAME,
3417 p_flex_name, -- MT_INFORMATION_TYPE,
3418 null, -- MT_INTER_BG_TRANSFER
3419 p_pos_defs.POSITION_DEFINITION_ID,
3420 p_pos_defs.ID_FLEX_NUM,
3421 p_pos_defs.SUMMARY_FLAG,
3422 p_pos_defs.ENABLED_FLAG,
3423 to_char(p_pos_defs.START_DATE_ACTIVE,g_datefmt),
3424 to_char(p_pos_defs.END_DATE_ACTIVE,g_datefmt),
3425 p_pos_defs.SEGMENT1,
3426 p_pos_defs.SEGMENT2,
3427 p_pos_defs.SEGMENT3,
3428 p_pos_defs.SEGMENT4,
3429 p_pos_defs.SEGMENT5,
3430 p_pos_defs.SEGMENT6,
3431 p_pos_defs.SEGMENT7,
3432 p_pos_defs.SEGMENT8,
3433 p_pos_defs.SEGMENT9,
3434 p_pos_defs.SEGMENT10,
3435 p_pos_defs.SEGMENT11,
3436 p_pos_defs.SEGMENT12,
3437 p_pos_defs.SEGMENT13,
3438 p_pos_defs.SEGMENT14,
3439 p_pos_defs.SEGMENT15,
3440 p_pos_defs.SEGMENT16,
3441 p_pos_defs.SEGMENT17,
3442 p_pos_defs.SEGMENT18,
3443 p_pos_defs.SEGMENT19,
3444 p_pos_defs.SEGMENT20,
3445 p_pos_defs.SEGMENT21,
3446 p_pos_defs.SEGMENT22,
3447 p_pos_defs.SEGMENT23,
3448 p_pos_defs.SEGMENT24,
3449 p_pos_defs.SEGMENT25,
3450 p_pos_defs.SEGMENT26,
3451 p_pos_defs.SEGMENT27,
3452 p_pos_defs.SEGMENT28,
3453 p_pos_defs.SEGMENT29,
3454 p_pos_defs.SEGMENT30,
3455 to_char(p_pos_defs.LAST_UPDATE_DATE,g_datefmt),
3456 p_pos_defs.LAST_UPDATED_BY,
3457 p_pos_defs.LAST_UPDATE_LOGIN,
3458 p_pos_defs.CREATED_BY,
3459 to_char(p_pos_defs.CREATION_DATE,g_datefmt)
3460 );
3461
3462 exception
3463 when others then
3464 put_line(l_proc||' - raised exception');
3465 raise;
3466
3467 end;
3468
3469 procedure update_position_defs(p_transfer_name varchar2,
3470 p_effective_date date,
3471 p_person_id number,
3472 p_flex_name varchar2,
3473 p_pos_defs per_position_definitions%rowtype)
3474 is
3475
3476 l_proc varchar2(72) := g_package||'update_position_defs';
3477 l_x varchar2(1);
3478
3479 cursor cur_pos_defs is
3480 select 'x'
3481 from ghr_mt_position_definitions_v
3482 where mt_name = p_transfer_name
3483 and mt_person_id = to_char(p_person_id)
3484 and mt_information_type = p_flex_name
3485 for update;
3486
3487 begin
3488
3489 put_line('update position_defs -'||p_flex_name);
3490
3491 open cur_pos_defs;
3492 fetch cur_pos_defs into l_x;
3493
3494 if cur_pos_defs%notfound then
3495 put_line('update row not found - calling insert');
3496 close cur_pos_defs;
3497
3498 insert_position_defs(
3499 p_transfer_name => p_transfer_name,
3500 p_effective_date => p_effective_date,
3501 p_person_id => p_person_id,
3502 p_flex_name => p_flex_name,
3503 p_pos_defs => p_pos_defs
3504 );
3505
3506 return;
3507 end if;
3508
3509 update ghr_mt_position_definitions_v
3510 set
3511 MT_EFFECTIVE_DATE = p_effective_date,
3512 POSITION_DEFINITION_ID =
3513 nvl(p_pos_defs.POSITION_DEFINITION_ID,POSITION_DEFINITION_ID),
3514 ID_FLEX_NUM = nvl(p_pos_defs.ID_FLEX_NUM,ID_FLEX_NUM),
3515 SUMMARY_FLAG = nvl(p_pos_defs.SUMMARY_FLAG,SUMMARY_FLAG),
3516 ENABLED_FLAG = nvl(p_pos_defs.ENABLED_FLAG,ENABLED_FLAG),
3517 START_DATE_ACTIVE =
3518 nvl(to_char(p_pos_defs.START_DATE_ACTIVE,g_datefmt),
3519 START_DATE_ACTIVE),
3520 END_DATE_ACTIVE =
3521 nvl(to_char(p_pos_defs.END_DATE_ACTIVE,g_datefmt),END_DATE_ACTIVE),
3522 SEGMENT1 = nvl(p_pos_defs.SEGMENT1,SEGMENT1),
3523 SEGMENT2 = nvl(p_pos_defs.SEGMENT2,SEGMENT2),
3524 SEGMENT3 = nvl(p_pos_defs.SEGMENT3,SEGMENT3),
3525 SEGMENT4 = nvl(p_pos_defs.SEGMENT4,SEGMENT4),
3526 SEGMENT5 = nvl(p_pos_defs.SEGMENT5,SEGMENT5),
3527 SEGMENT6 = nvl(p_pos_defs.SEGMENT6,SEGMENT6),
3528 SEGMENT7 = nvl(p_pos_defs.SEGMENT7,SEGMENT7),
3529 SEGMENT8 = nvl(p_pos_defs.SEGMENT8,SEGMENT8),
3530 SEGMENT9 = nvl(p_pos_defs.SEGMENT9,SEGMENT9),
3531 SEGMENT10 = nvl(p_pos_defs.SEGMENT10,SEGMENT10),
3532 SEGMENT11 = nvl(p_pos_defs.SEGMENT11,SEGMENT11),
3533 SEGMENT12 = nvl(p_pos_defs.SEGMENT12,SEGMENT12),
3534 SEGMENT13 = nvl(p_pos_defs.SEGMENT13,SEGMENT13),
3535 SEGMENT14 = nvl(p_pos_defs.SEGMENT14,SEGMENT14),
3536 SEGMENT15 = nvl(p_pos_defs.SEGMENT15,SEGMENT15),
3537 SEGMENT16 = nvl(p_pos_defs.SEGMENT16,SEGMENT16),
3538 SEGMENT17 = nvl(p_pos_defs.SEGMENT17,SEGMENT17),
3539 SEGMENT18 = nvl(p_pos_defs.SEGMENT18,SEGMENT18),
3540 SEGMENT19 = nvl(p_pos_defs.SEGMENT19,SEGMENT19),
3541 SEGMENT20 = nvl(p_pos_defs.SEGMENT20,SEGMENT20),
3542 SEGMENT21 = nvl(p_pos_defs.SEGMENT21,SEGMENT21),
3543 SEGMENT22 = nvl(p_pos_defs.SEGMENT22,SEGMENT22),
3544 SEGMENT23 = nvl(p_pos_defs.SEGMENT23,SEGMENT23),
3545 SEGMENT24 = nvl(p_pos_defs.SEGMENT24,SEGMENT24),
3546 SEGMENT25 = nvl(p_pos_defs.SEGMENT25,SEGMENT25),
3547 SEGMENT26 = nvl(p_pos_defs.SEGMENT26,SEGMENT26),
3548 SEGMENT27 = nvl(p_pos_defs.SEGMENT27,SEGMENT27),
3549 SEGMENT28 = nvl(p_pos_defs.SEGMENT28,SEGMENT28),
3550 SEGMENT29 = nvl(p_pos_defs.SEGMENT29,SEGMENT29),
3551 SEGMENT30 = nvl(p_pos_defs.SEGMENT30,SEGMENT30)
3552 where current of cur_pos_defs;
3553
3554 close cur_pos_defs;
3555
3556 exception
3557 when others then
3558 put_line(l_proc||' - raised exception');
3559 raise;
3560
3561 end;
3562
3563 procedure insert_element_entries(
3564 p_transfer_name varchar2,
3565 p_person_id number,
3566 p_effective_date date,
3567 p_element in out nocopy ghr_mt_element_entries_v%rowtype)
3568 is
3569
3570 l_proc varchar2(72) := g_package||'insert_element_entries';
3571 l_fegli_meaning hr_lookups.meaning%type;
3572 l_retirement_plan_meaning hr_lookups.meaning%type;
3573 l_table_name constant varchar2(30) := 'PER_ELEMENT_ENTRIES';
3574 l_name varchar2(30);
3575
3576 begin
3577
3578 put('insert element entries');
3579
3580 who_called_me(p_name => l_name);
3581
3582 -- if this package body did NOT call this procedure then ckeck if
3583 -- the row to be inserted already exists, if so raise an error
3584 if nvl(l_name,'unknown') <> g_name then
3585 put_line(' - called by '|| l_name);
3586 if row_exists(
3587 p_transfer_name => p_transfer_name
3588 ,p_person_id => p_person_id
3589 ,p_table_name => l_table_name) then
3590
3591 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
3592 hr_utility.raise_error;
3593 end if;
3594 else
3595 put_line(' - called locally');
3596 end if;
3597
3598 l_fegli_meaning := get_code_meaning (
3599 p_lookup_type => 'GHR_US_FEGLI',
3600 p_lookup_code => p_element.fegli);
3601
3602 l_retirement_plan_meaning := get_code_meaning (
3603 p_lookup_type => 'GHR_US_RETIREMENT_PLAN',
3604 p_lookup_code => p_element.retirement_plan);
3605
3606 insert into ghr_mt_element_entries_v (
3607 GHR_INTERFACE_ID,
3608 MT_PROCESS_DATE,
3609 MT_SOURCE,
3610 MT_EFFECTIVE_DATE,
3611 MT_NAME,
3612 MT_STATUS,
3613 MT_PERSON_ID,
3614 MT_TABLE_NAME,
3615 MT_INFORMATION_TYPE,
3616 MT_INTER_BG_TRANSFER,
3617 TOTAL_SALARY,
3618 SALARY,
3619 LOCALITY_ADJUSTMENT,
3620 ADJUSTED_PAY,
3621 OTHER_PAY,
3622 FEGLI,
3623 ELIGIBILITY_EXPIRATION,
3624 RETIREMENT_PLAN,
3625 RETENTION_ALLOWANCE,
3626 TO_RETENTION_ALLOW_PERCENTAGE,
3627 STAFFING_DIFFERENTIAL,
3628 TO_STAFFING_DIFF_PERCENTAGE,
3629 SUPERVISORY_DIFFERENTIAL,
3630 TO_SUPERVISORY_DIFF_PERCENTAGE,
3631 AUO_AMOUNT,
3632 AUO_PREMIUM_PAY_IND,
3633 AP_AMOUNT,
3634 AP_PREMIUM_PAY_IND,
3635 ENROLLMENT,
3636 HEALTH_PLAN,
3637 TEMPS_TOTAL_COST,
3638 TSP_AMOUNT,
3639 TSP_RATE,
3640 TSP_G_FUND,
3641 TSP_F_FUND,
3642 TSP_C_FUND,
3643 TSP_STATUS,
3644 TSP_STATUS_DATE,
3645 TSP_ELIGIBILITY_DATE,
3646 WGI_STATUS,
3647 WGI_DATE_DUE,
3648 WGI_LAST_INCREASE_DATE,
3649 WGI_POSTPONMT_EFFECTIVE,
3650 WGI_POSTPONMT_DETERM_DUE,
3651 fegli_meaning,
3652 retirement_plan_meaning,
3653 to_basic_salary_rate,
3654 TO_ADJUSTED_BASIC_PAY,
3655 TO_TOTAL_SALARY
3656 )
3657 values (
3658 ghr_interface_s.nextval,
3659 sysdate,
3660 l_name,
3661 p_effective_date,
3662 p_transfer_name,
3663 null, -- MT_STATUS,
3664 p_person_id, -- MT_PERSON_ID,
3665 l_table_name, -- MT_TABLE_NAME,
3666 null, -- MT_INFORMATION_TYPE,
3667 null, -- MT_INTER_BG_TRANSFER
3668 p_element.TOTAL_SALARY,
3669 p_element.SALARY,
3670 p_element.LOCALITY_ADJUSTMENT,
3671 p_element.ADJUSTED_PAY,
3672 p_element.OTHER_PAY,
3673 p_element.FEGLI,
3674 p_element.ELIGIBILITY_EXPIRATION,
3675 p_element.RETIREMENT_PLAN,
3676 p_element.RETENTION_ALLOWANCE,
3677 p_element.TO_RETENTION_ALLOW_PERCENTAGE,
3678 p_element.STAFFING_DIFFERENTIAL,
3679 p_element.TO_STAFFING_DIFF_PERCENTAGE,
3680 p_element.SUPERVISORY_DIFFERENTIAL,
3681 p_element.TO_SUPERVISORY_DIFF_PERCENTAGE,
3682 p_element.AUO_AMOUNT,
3683 p_element.AUO_PREMIUM_PAY_IND,
3684 p_element.AP_AMOUNT,
3685 p_element.AP_PREMIUM_PAY_IND,
3686 p_element.ENROLLMENT,
3687 p_element.HEALTH_PLAN,
3688 p_element.TEMPS_TOTAL_COST,
3689 p_element.TSP_AMOUNT,
3690 p_element.TSP_RATE,
3691 p_element.TSP_G_FUND,
3692 p_element.TSP_F_FUND,
3693 p_element.TSP_C_FUND,
3694 p_element.TSP_STATUS,
3695 p_element.TSP_STATUS_DATE,
3696 p_element.TSP_ELIGIBILITY_DATE,
3697 p_element.WGI_STATUS,
3698 p_element.WGI_DATE_DUE,
3699 p_element.WGI_LAST_INCREASE_DATE,
3700 p_element.WGI_POSTPONMT_EFFECTIVE,
3701 p_element.WGI_POSTPONMT_DETERM_DUE,
3702 l_fegli_meaning,
3703 l_retirement_plan_meaning,
3704 -- Changes 4093771
3705 p_element.TO_BASIC_SALARY_RATE,
3706 p_element.TO_ADJUSTED_BASIC_PAY,
3707 p_element.TO_TOTAL_SALARY
3708 -- End changes 4093771
3709 );
3710
3711 exception
3712 when others then
3713 put_line(l_proc||' - raised exception');
3714 raise;
3715
3716 end;
3717
3718 procedure update_element_entries(
3719 p_transfer_name varchar2,
3720 p_person_id number,
3721 p_effective_date date,
3722 p_element in out nocopy ghr_mt_element_entries_v%rowtype)
3723 is
3724
3725 l_proc varchar2(72) := g_package||'update_element_entries';
3726 l_fegli_meaning hr_lookups.meaning%type;
3727 l_retirement_plan_meaning hr_lookups.meaning%type;
3728 l_x varchar2(1);
3729
3730 cursor cur_elements is
3731 select 'x'
3732 from ghr_mt_element_entries_v
3733 where mt_name = p_transfer_name
3734 and mt_person_id = to_char(p_person_id)
3735 for update;
3736
3737 begin
3738
3739 put_line('update element entries');
3740
3741 put_line('update element entries - basic' || p_element.TO_BASIC_SALARY_RATE);
3742 put_line('update element entries - TO_TOTAL_SALARY' || p_element.TO_TOTAL_SALARY);
3743 put_line('update element entries - TO_ADJUSTED_BASIC_PAY' || p_element.TO_ADJUSTED_BASIC_PAY);
3744
3745 open cur_elements;
3746 fetch cur_elements into l_x;
3747
3748 if cur_elements%notfound then
3749 put_line('update row not found - calling insert');
3750 close cur_elements;
3751
3752 insert_element_entries(
3753 p_transfer_name => p_transfer_name,
3754 p_person_id => p_person_id,
3755 p_effective_date => p_effective_date,
3756 p_element => p_element
3757 );
3758
3759 return;
3760 end if;
3761
3762 l_fegli_meaning := get_code_meaning (
3763 p_lookup_type => 'GHR_US_FEGLI',
3764 p_lookup_code => p_element.fegli);
3765
3766 l_retirement_plan_meaning := get_code_meaning (
3767 p_lookup_type => 'GHR_US_RETIREMENT_PLAN',
3768 p_lookup_code => p_element.retirement_plan);
3769
3770 update ghr_mt_element_entries_v
3771 set
3772 MT_EFFECTIVE_DATE = p_effective_date,
3773 TOTAL_SALARY = nvl(p_element.TOTAL_SALARY,TOTAL_SALARY),
3774 SALARY = nvl(p_element.SALARY,SALARY),
3775 LOCALITY_ADJUSTMENT =
3776 nvl(p_element.LOCALITY_ADJUSTMENT,LOCALITY_ADJUSTMENT),
3777 ADJUSTED_PAY = nvl(p_element.ADJUSTED_PAY,ADJUSTED_PAY),
3778 OTHER_PAY = nvl(p_element.OTHER_PAY,OTHER_PAY),
3779 FEGLI = nvl(p_element.FEGLI,FEGLI),
3780 ELIGIBILITY_EXPIRATION =
3781 nvl(p_element.ELIGIBILITY_EXPIRATION,ELIGIBILITY_EXPIRATION),
3782 RETIREMENT_PLAN = nvl(p_element.RETIREMENT_PLAN,RETIREMENT_PLAN),
3783 RETENTION_ALLOWANCE =
3784 nvl(p_element.RETENTION_ALLOWANCE,RETENTION_ALLOWANCE),
3785 TO_RETENTION_ALLOW_PERCENTAGE =
3786 nvl(p_element.TO_RETENTION_ALLOW_PERCENTAGE,TO_RETENTION_ALLOW_PERCENTAGE),
3787 STAFFING_DIFFERENTIAL =
3788 nvl(p_element.STAFFING_DIFFERENTIAL,STAFFING_DIFFERENTIAL),
3789 TO_STAFFING_DIFF_PERCENTAGE =
3790 nvl(p_element.TO_STAFFING_DIFF_PERCENTAGE,TO_STAFFING_DIFF_PERCENTAGE),
3791 SUPERVISORY_DIFFERENTIAL =
3792 nvl(p_element.SUPERVISORY_DIFFERENTIAL,SUPERVISORY_DIFFERENTIAL),
3793 TO_SUPERVISORY_DIFF_PERCENTAGE =
3794 nvl(p_element.TO_SUPERVISORY_DIFF_PERCENTAGE,TO_SUPERVISORY_DIFF_PERCENTAGE),
3795 AUO_AMOUNT = nvl(p_element.AUO_AMOUNT,AUO_AMOUNT),
3796 AUO_PREMIUM_PAY_IND = nvl(p_element.AUO_PREMIUM_PAY_IND,AUO_PREMIUM_PAY_IND),
3797 AP_AMOUNT = nvl(p_element.AP_AMOUNT,AP_AMOUNT),
3798 AP_PREMIUM_PAY_IND = nvl(p_element.AP_PREMIUM_PAY_IND,AP_PREMIUM_PAY_IND),
3799 ENROLLMENT = nvl(p_element.ENROLLMENT,ENROLLMENT),
3800 HEALTH_PLAN = nvl(p_element.HEALTH_PLAN,HEALTH_PLAN),
3801 TEMPS_TOTAL_COST = nvl(p_element.TEMPS_TOTAL_COST,TEMPS_TOTAL_COST),
3802 TSP_AMOUNT = nvl(p_element.TSP_AMOUNT,TSP_AMOUNT),
3803 TSP_RATE = nvl(p_element.TSP_RATE,TSP_RATE),
3804 TSP_G_FUND = nvl(p_element.TSP_G_FUND,TSP_G_FUND),
3805 TSP_F_FUND = nvl(p_element.TSP_F_FUND,TSP_F_FUND),
3806 TSP_C_FUND = nvl(p_element.TSP_C_FUND,TSP_C_FUND),
3807 TSP_STATUS = nvl(p_element.TSP_STATUS,TSP_STATUS),
3808 TSP_STATUS_DATE =
3809 nvl(p_element.TSP_STATUS_DATE,TSP_STATUS_DATE),
3810 TSP_ELIGIBILITY_DATE =
3811 nvl(p_element.TSP_ELIGIBILITY_DATE,TSP_ELIGIBILITY_DATE),
3812 WGI_STATUS = nvl(p_element.WGI_STATUS,WGI_STATUS),
3813 /*WGI_DATE_DUE =
3814 nvl(p_element.WGI_DATE_DUE,WGI_DATE_DUE),*/
3815 ---Bug 2412656 WGI element
3816 WGI_DATE_DUE = p_element.WGI_DATE_DUE,
3817 WGI_LAST_INCREASE_DATE =
3818 nvl(p_element.WGI_LAST_INCREASE_DATE, WGI_LAST_INCREASE_DATE),
3819 WGI_POSTPONMT_EFFECTIVE =
3820 nvl(p_element.WGI_POSTPONMT_EFFECTIVE,WGI_POSTPONMT_EFFECTIVE),
3821 WGI_POSTPONMT_DETERM_DUE =
3822 nvl(p_element.WGI_POSTPONMT_DETERM_DUE,WGI_POSTPONMT_DETERM_DUE),
3823 fegli_meaning = nvl(l_fegli_meaning,fegli_meaning),
3824 retirement_plan_meaning =
3825 nvl(l_retirement_plan_meaning,retirement_plan_meaning),
3826 TO_BASIC_SALARY_RATE = p_element.TO_BASIC_SALARY_RATE,
3827 TO_ADJUSTED_BASIC_PAY = p_element.TO_ADJUSTED_BASIC_PAY,
3828 TO_TOTAL_SALARY = p_element.TO_TOTAL_SALARY
3829 where current of cur_elements;
3830
3831 close cur_elements;
3832
3833 exception
3834 when others then
3835 put_line(l_proc||' - raised exception');
3836 raise;
3837
3838 end;
3839
3840 procedure insert_misc(p_transfer_name varchar2,
3841 p_person_id number,
3842 p_effective_date date,
3843 p_misc ghr_mt_misc_v%rowtype)
3844 is
3845
3846 l_proc varchar2(72) := g_package||'insert_misc';
3847 l_table_name constant varchar2(30) := 'MISCELANEOUS';
3848 l_name varchar2(30);
3849
3850 l_from_agency_code_desc varchar2(80);
3851
3852 begin
3853
3854 put('insert misc');
3855
3856 who_called_me(p_name => l_name);
3857
3858 -- if this package body did NOT call this procedure then ckeck if
3859 -- the row to be inserted already exists, if so raise an error
3860 if nvl(l_name,'unknown') <> g_name then
3861 put_line(' - called by '|| l_name);
3862 if row_exists(
3863 p_transfer_name => p_transfer_name
3864 ,p_person_id => p_person_id
3865 ,p_table_name => l_table_name) then
3866
3867 hr_utility.set_message(8301, 'GHR_38548_UNIQUE_CONSTRAINT');
3868 hr_utility.raise_error;
3869 end if;
3870 else
3871 put_line(' - called locally');
3872 end if;
3873
3874 l_from_agency_code_desc := get_code_meaning (
3875 p_lookup_type => 'GHR_US_AGENCY_CODE',
3876 p_lookup_code => p_misc.from_agency_code);
3877
3878 insert into ghr_mt_misc_v (
3879 GHR_INTERFACE_ID,
3880 MT_PROCESS_DATE,
3881 MT_SOURCE,
3882 MT_EFFECTIVE_DATE,
3883 MT_NAME,
3884 MT_STATUS,
3885 MT_PERSON_ID,
3886 MT_TABLE_NAME,
3887 MT_INFORMATION_TYPE,
3888 MT_INTER_BG_TRANSFER,
3889 FROM_GRADE_OR_LEVEL,
3890 FROM_PAY_PLAN,
3891 FROM_POSITION_TITLE,
3892 FROM_POSITION_SEQ_NUM,
3893 FROM_POSITION_DESC_NUM,
3894 FROM_POSITION_ID,
3895 FROM_AGENCY_CODE,
3896 OCC_SERIES,
3897 TO_POSITION_ID,
3898 DUTY_STATION_DESC,
3899 DUTY_STATION_CODE,
3900 FROM_AGENCY_CODE_DESC
3901 )
3902 values (
3903 ghr_interface_s.nextval,
3904 sysdate,
3905 l_name,
3906 p_effective_date,
3907 p_transfer_name,
3908 null, -- MT_STATUS,
3909 p_person_id, -- MT_PERSON_ID,
3910 l_table_name, -- MT_TABLE_NAME,
3911 null, -- MT_INFORMATION_TYPE,
3912 null, -- MT_INTER_BG_TRANSFER
3913 p_misc.FROM_GRADE_OR_LEVEL,
3914 p_misc.FROM_PAY_PLAN,
3915 p_misc.FROM_POSITION_TITLE,
3916 p_misc.FROM_POSITION_SEQ_NUM,
3917 p_misc.FROM_POSITION_DESC_NUM,
3918 p_misc.FROM_POSITION_ID,
3919 p_misc.FROM_AGENCY_CODE,
3920 p_misc.OCC_SERIES,
3921 p_misc.TO_POSITION_ID,
3922 p_misc.DUTY_STATION_DESC,
3923 p_misc.DUTY_STATION_CODE,
3924 l_from_agency_code_desc
3925 );
3926
3927 exception
3928 when others then
3929 put_line(l_proc||' - raised exception');
3930 raise;
3931
3932 end;
3933
3934 procedure update_misc(p_transfer_name varchar2,
3935 p_person_id number,
3936 p_effective_date date,
3937 p_misc ghr_mt_misc_v%rowtype)
3938 is
3939
3940 l_proc varchar2(72) := g_package||'update_misc';
3941 l_x varchar2(1);
3942 l_from_agency_code_desc varchar2(80);
3943
3944 cursor cur_misc is
3945 select 'x'
3946 from ghr_mt_misc_v
3947 where mt_name = p_transfer_name
3948 and mt_person_id = to_char(p_person_id)
3949 for update;
3950
3951 begin
3952
3953 put_line('update misc');
3954
3955 open cur_misc;
3956 fetch cur_misc into l_x;
3957
3958 if cur_misc%notfound then
3959 close cur_misc;
3960 hr_utility.set_message(8301, 'GHR_38549_UPDATE_NOT_FOUND');
3961 hr_utility.raise_error;
3962 end if;
3963
3964 l_from_agency_code_desc := get_code_meaning (
3965 p_lookup_type => 'GHR_US_AGENCY_CODE',
3966 p_lookup_code => p_misc.from_agency_code);
3967
3968 update ghr_mt_misc_v
3969 set
3970 MT_EFFECTIVE_DATE = p_effective_date,
3971 FROM_GRADE_OR_LEVEL = nvl(p_misc.FROM_GRADE_OR_LEVEL,FROM_GRADE_OR_LEVEL),
3972 FROM_PAY_PLAN = nvl(p_misc.FROM_PAY_PLAN,FROM_PAY_PLAN),
3973 FROM_POSITION_TITLE = nvl(p_misc.FROM_POSITION_TITLE,FROM_POSITION_TITLE),
3974 FROM_POSITION_SEQ_NUM =
3975 nvl(p_misc.FROM_POSITION_SEQ_NUM,FROM_POSITION_SEQ_NUM),
3976 FROM_POSITION_DESC_NUM =
3977 nvl(p_misc.FROM_POSITION_DESC_NUM,FROM_POSITION_DESC_NUM),
3978 FROM_POSITION_ID = nvl(p_misc.FROM_POSITION_ID,FROM_POSITION_ID),
3979 FROM_AGENCY_CODE = nvl(p_misc.FROM_AGENCY_CODE,FROM_AGENCY_CODE),
3980 OCC_SERIES = nvl(p_misc.OCC_SERIES,OCC_SERIES),
3981 TO_POSITION_ID = nvl(p_misc.TO_POSITION_ID,TO_POSITION_ID),
3982 DUTY_STATION_DESC = nvl(p_misc.DUTY_STATION_DESC,DUTY_STATION_DESC),
3983 DUTY_STATION_CODE = nvl(p_misc.DUTY_STATION_CODE,DUTY_STATION_CODE),
3984 from_agency_code_desc =
3985 nvl(l_from_agency_code_desc,from_agency_code_desc)
3986 where current of cur_misc;
3987
3988 close cur_misc;
3989
3990 exception
3991 when others then
3992 put_line(l_proc||' - raised exception');
3993 raise;
3994
3995 end;
3996
3997
3998 --
3999 -- Copy information for the person identified by p_person_id to
4000 -- ghr_mass_interface_v. The parameter p_transfer_id is used to get the
4001 -- transfer effective date and transfer name used for the transfer of
4002 -- the person.
4003 --
4004 -- If a person already exists in the interface table
4005 -- for the transfer name, the data is deleted before continuing with
4006 -- the transfer out.
4007 --
4008 -- This procedure does not commit
4009 --
4010 -- Note: this procedure or procedures that it calls, calls log_message
4011 -- to write messages to ghr_process_log. The log_message procedure
4012 -- will raise an exception if it is called before set_log_program_name()
4013 -- is called. The procedure below expects that its caller has called
4014 -- set_log_program_name() first.
4015 --
4016 procedure mass_transfer_out(p_errbuf out nocopy varchar2,
4017 p_retcode out nocopy number,
4018 p_transfer_id in number,
4019 p_person_id in per_all_people_f.person_id%type)
4020 is
4021 l_proc_name varchar2(30) := 'mass_transfer_out';
4022 l_proc varchar2(72) := g_package||l_proc_name;
4023
4024 l_effdate date;
4025 l_transfer_name ghr_mass_transfers.name%type;
4026 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
4027
4028 l_position_id per_assignments_f.position_id%type;
4029 l_location_id per_assignments_f.location_id%type;
4030 l_assignment_id per_assignments_f.assignment_id%type;
4031 l_grade_id per_assignments_f.grade_id%type;
4032 l_business_group_id per_assignments_f.business_group_id%type;
4033
4034 l_pos_ei_data per_position_extra_info%rowtype;
4035 l_per_ei_data per_people_extra_info%rowtype;
4036 l_asg_ei_data per_assignment_extra_info%rowtype;
4037 l_special_info ghr_api.special_information_type;
4038
4039 l_adj_basic_pay number;
4040 l_multi_error_flag boolean;
4041
4042 l_position_status hr_all_positions_f.status%type;
4043 l_rc varchar2(30);
4044 l_inter_bg_transfer varchar2(1);
4045
4046 ---------------
4047 l_people_row per_all_people_f%rowtype;
4048 l_position_row hr_all_positions_f%rowtype;
4049 l_people_ei_row per_people_extra_info%rowtype;
4050 l_element ghr_mt_element_entries_v%rowtype;
4051 l_misc ghr_mt_misc_v%rowtype;
4052
4053 l_pos_defs per_position_definitions%rowtype;
4054 l_position_rec hr_all_positions_f%rowtype;
4055 l_result_code varchar2(10);
4056 /*
4057 cursor cur_pos_definitions(p_position_id number) is
4058 select pd.*
4059 from per_position_definitions pd,
4060 hr_all_positions_f p
4061 where p.position_id = p_position_id
4062 and p.position_definition_id = pd.position_definition_id
4063 and trunc(l_effdate) between p.effective_start_date and
4064 nvl(effective_end_date,l_effdate+1);
4065 */
4066 cursor cur_pos_definitions(p_position_definition_id number) is
4067 select pd.*
4068 from per_position_definitions pd
4069 where pd.position_definition_id = p_position_definition_id;
4070 --
4071 l_address_row per_addresses%rowtype;
4072 cursor cur_addr(p_person_id number) is
4073 select *
4074 from per_addresses
4075 where person_id = p_person_id
4076 and primary_flag = 'Y'
4077 and l_effdate
4078 between nvl(date_from,l_effdate)
4079 and nvl(date_to,l_effdate);
4080
4081 l_assignments_row per_all_assignments_f%rowtype;
4082 l_assignment_ei_row per_assignment_extra_info%rowtype;
4083 cursor cur_assignments_f(p_person_id number) is
4084 select assignment_id, position_id, business_group_id, grade_id, location_id
4085 from per_all_assignments_f
4086 where primary_flag = 'Y'
4087 and assignment_type <> 'B'
4088 and person_id = p_person_id
4089 and trunc(l_effdate) between effective_start_date
4090 and effective_end_date;
4091
4092 l_positions_row hr_all_positions_f%rowtype;
4093 l_position_ei_row per_position_extra_info%rowtype;
4094
4095 l_contact_type varchar2(30);
4096 l_contact_name varchar2(240);
4097 l_contact_person_id per_people_f.person_id%type;
4098 cursor cur_contact_info(p_person_id number) is
4099 select cr.contact_type, p.full_name, cr.contact_person_id
4100 from per_contact_relationships cr, per_all_people_f p
4101 where cr.person_id = p_person_id
4102 and cr.primary_contact_flag = 'Y'
4103 and cr.contact_person_id = p.person_id
4104 and trunc(l_effdate) between p.effective_start_date
4105 and p.effective_end_date;
4106
4107 cursor cur_lei(p_location_id number) is
4108 select ds.duty_station_desc, ds.duty_station_code
4109 FROM hr_location_extra_info lei, ghr_duty_stations_v ds
4110 WHERE lei.location_id = p_location_id
4111 AND lei.information_type = 'GHR_US_LOC_INFORMATION'
4112 and to_number(lei.lei_information3) = ds.duty_station_id
4113 and trunc(l_effdate) between ds.effective_start_date
4114 and ds.effective_end_date;
4115
4116 -- from ghapiapi.pkb (can't find api)
4117 cursor cur_grd(p_grade_id number) is
4118 SELECT gdf.segment1 pay_plan
4119 ,gdf.segment2 grade_or_level
4120 FROM per_grade_definitions gdf
4121 ,per_grades grd
4122 WHERE grd.grade_id = p_grade_id
4123 AND grd.grade_definition_id = gdf.grade_definition_id;
4124
4125 begin
4126
4127 hr_utility.set_location('Entering:'||l_proc, 10);
4128 put_line(l_proc||' transfer_id: '||p_transfer_id ||
4129 ', person_id: '||p_person_id);
4130
4131 get_transfer_parameters(p_transfer_id,
4132 p_effective_date => l_effdate,
4133 p_transfer_name => l_transfer_name,
4134 p_inter_bg_transfer => l_inter_bg_transfer);
4135
4136 if l_effdate is null then
4137
4138 log_message(
4139 p_procedure => l_proc_name,
4140 p_message => 'Failed to get Transfer Out information from'||
4141 'GHR_MASS_TRANSFERS for mass_transfer_id:'||
4142 p_transfer_id);
4143 commit;
4144 hr_utility.set_message(8301, 'GHR_38534_MT_ID_ERR');
4145 hr_utility.raise_error;
4146
4147 end if;
4148
4149 ghr_history_fetch.fetch_people (
4150 p_person_id => p_person_id,
4151 p_date_effective => l_effdate,
4152 p_people_data => l_people_row,
4153 p_result_code => l_rc
4154 );
4155
4156 if l_rc = 'not_found' then
4157
4158 log_message(
4159 p_procedure => l_proc_name,
4160 p_message =>
4161 'Could not query data from PER_PEOPLE_F for person_id:'||
4162 p_person_id);
4163
4164 commit;
4165 hr_utility.set_message(8301, 'GHR_38536_MTO_NO_PERSON');
4166 hr_utility.raise_error;
4167 end if;
4168
4169 -- if person and p_transfer_name exits in the interface table
4170 -- refresh the information by deleting the current data before
4171 -- inserting new data
4172 if row_exists(
4173 p_transfer_name => l_transfer_name
4174 ,p_person_id => p_person_id
4175 ,p_table_name => 'PER_PEOPLE_F') then
4176
4177 put_line('------------------------------------------');
4178 put_line('Person '||p_person_id||', found in table');
4179 put_line('------------------------------------------');
4180
4181 delete ghr_interface
4182 where information1 = l_transfer_name
4183 and information3 = to_char(p_person_id);
4184 end if;
4185
4186 savepoint mto;
4187
4188 insert_people_f(p_transfer_name => l_transfer_name,
4189 p_inter_bg_transfer => l_inter_bg_transfer,
4190 p_effective_date => l_effdate,
4191 ppf => l_people_row);
4192
4193 hr_utility.set_location('Gathering Data:'||l_proc, 20);
4194
4195 -- procedure insert_people_ei tests if informaton_type is null and
4196 -- if it is the row in not inserted into the interface table
4197
4198 l_people_ei_row.information_type := null;
4199 ghr_history_fetch.fetch_peopleei
4200 (p_person_id => p_person_id
4201 ,p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES'
4202 ,p_date_effective => l_effdate
4203 ,p_per_ei_data => l_people_ei_row
4204 );
4205
4206 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4207
4208 l_people_ei_row.information_type := null;
4209 ghr_history_fetch.fetch_peopleei
4210 (p_person_id => p_person_id
4211 ,p_information_type => 'GHR_US_PER_SF52'
4212 ,p_date_effective => l_effdate
4213 ,p_per_ei_data => l_people_ei_row
4214 );
4215
4216 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4217
4218 l_people_ei_row.information_type := null;
4219 ghr_history_fetch.fetch_peopleei
4220 (p_person_id => p_person_id
4221 ,p_information_type => 'GHR_US_PER_GROUP1'
4222 ,p_date_effective => l_effdate
4223 ,p_per_ei_data => l_people_ei_row
4224 );
4225
4226 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4227
4228 l_people_ei_row.information_type := null;
4229 ghr_history_fetch.fetch_peopleei
4230 (p_person_id => p_person_id
4231 ,p_information_type => 'GHR_US_PER_GROUP2'
4232 ,p_date_effective => l_effdate
4233 ,p_per_ei_data => l_people_ei_row
4234 );
4235
4236 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4237
4238
4239 l_people_ei_row.information_type := null;
4240 ghr_history_fetch.fetch_peopleei
4241 (p_person_id => p_person_id
4242 ,p_information_type => 'GHR_US_PER_SCD_INFORMATION'
4243 ,p_date_effective => l_effdate
4244 ,p_per_ei_data => l_people_ei_row
4245 );
4246
4247 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4248
4249 l_people_ei_row.information_type := null;
4250 ghr_history_fetch.fetch_peopleei
4251 (p_person_id => p_person_id
4252 ,p_information_type => 'GHR_US_PER_PROBATIONS'
4253 ,p_date_effective => l_effdate
4254 ,p_per_ei_data => l_people_ei_row
4255 );
4256
4257 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4258
4259 l_people_ei_row.information_type := null;
4260 ghr_history_fetch.fetch_peopleei
4261 (p_person_id => p_person_id
4262 ,p_information_type => 'GHR_US_PER_RETAINED_GRADE'
4263 ,p_date_effective => l_effdate
4264 ,p_per_ei_data => l_people_ei_row
4265 );
4266
4267 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4268
4269 l_people_ei_row.information_type := null;
4270 ghr_history_fetch.fetch_peopleei
4271 (p_person_id => p_person_id
4272 ,p_information_type => 'GHR_US_PER_SEPARATE_RETIRE'
4273 ,p_date_effective => l_effdate
4274 ,p_per_ei_data => l_people_ei_row
4275 );
4276
4277 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4278
4279 l_people_ei_row.information_type := null;
4280 ghr_history_fetch.fetch_peopleei
4281 (p_person_id => p_person_id
4282 ,p_information_type => 'GHR_US_PER_SECURITY'
4283 ,p_date_effective => l_effdate
4284 ,p_per_ei_data => l_people_ei_row
4285 );
4286
4287 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4288
4289 l_people_ei_row.information_type := null;
4290 ghr_history_fetch.fetch_peopleei
4291 (p_person_id => p_person_id
4292 ,p_information_type => 'GHR_US_PER_CONVERSIONS'
4293 ,p_date_effective => l_effdate
4294 ,p_per_ei_data => l_people_ei_row
4295 );
4296
4297 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4298
4299 l_people_ei_row.information_type := null;
4300 ghr_history_fetch.fetch_peopleei
4301 (p_person_id => p_person_id
4302 ,p_information_type => 'GHR_US_PER_USER_INFO'
4303 ,p_date_effective => l_effdate
4304 ,p_per_ei_data => l_people_ei_row
4305 );
4306
4307 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4308
4309 l_people_ei_row.information_type := null;
4310 ghr_history_fetch.fetch_peopleei
4311 (p_person_id => p_person_id
4312 ,p_information_type => 'GHR_US_PER_WF_ROUTING_GROUPS'
4313 ,p_date_effective => l_effdate
4314 ,p_per_ei_data => l_people_ei_row
4315 );
4316
4317 insert_people_ei(l_transfer_name, l_effdate, l_people_ei_row);
4318
4319 -- get person special information
4320 ghr_api.return_special_information
4321 (p_person_id => p_person_id
4322 ,p_structure_name => 'US Fed Education'
4323 ,p_effective_date => l_effdate
4324 ,p_special_info => l_special_info
4325 );
4326
4327 insert_special_info(l_transfer_name, l_effdate, p_person_id,
4328 'US Fed Education', l_special_info);
4329
4330 ghr_api.return_special_information
4331 (p_person_id => p_person_id
4332 ,p_structure_name => 'US Fed Cndtn of Emplmt'
4333 ,p_effective_date => l_effdate
4334 ,p_special_info => l_special_info
4335 );
4336
4337 insert_special_info(l_transfer_name, l_effdate, p_person_id,
4338 'US Fed Cndtn of Emplmt', l_special_info);
4339
4340 ghr_api.return_special_information
4341 (p_person_id => p_person_id
4342 ,p_structure_name => 'US Fed Conduct Perf'
4343 ,p_effective_date => l_effdate
4344 ,p_special_info => l_special_info
4345 );
4346
4347 insert_special_info(l_transfer_name, l_effdate, p_person_id,
4348 'US Fed Conduct Perf', l_special_info);
4349
4350 ghr_api.return_special_information
4351 (p_person_id => p_person_id
4352 ,p_structure_name => 'US Fed Language'
4353 ,p_effective_date => l_effdate
4354 ,p_special_info => l_special_info
4355 );
4356
4357 insert_special_info(l_transfer_name, l_effdate, p_person_id,
4358 'US Fed Language', l_special_info);
4359
4360 ghr_api.return_special_information
4361 (p_person_id => p_person_id
4362 ,p_structure_name => 'US Fed Spcl Consdrtn'
4363 ,p_effective_date => l_effdate
4364 ,p_special_info => l_special_info
4365 );
4366
4367 insert_special_info(l_transfer_name, l_effdate, p_person_id,
4368 'US Fed Spcl Consdrtn', l_special_info);
4369
4370 ghr_api.return_special_information
4371 (p_person_id => p_person_id
4372 ,p_structure_name => 'US Fed Perf Appraisal'
4373 ,p_effective_date => l_effdate
4374 ,p_special_info => l_special_info
4375 );
4376
4377 insert_special_info(l_transfer_name, l_effdate, p_person_id,
4378 'US Fed Perf Appraisal', l_special_info);
4379
4380 -- get address info
4381 open cur_addr(p_person_id);
4382 fetch cur_addr into l_address_row;
4383
4384 if cur_addr%notfound then
4385 put_line('No primary address info found');
4386 else
4387 insert_address(p_transfer_name => l_transfer_name,
4388 p_effective_date => l_effdate,
4389 p_a => l_address_row);
4390 end if;
4391 close cur_addr;
4392
4393 -- get person contact info
4394 open cur_contact_info(p_person_id);
4395 fetch cur_contact_info into l_contact_type, l_contact_name,
4396 l_contact_person_id;
4397
4398 if cur_contact_info%notfound then
4399 put_line('No primary contact found');
4400 else
4401
4402 open cur_addr(l_contact_person_id);
4403 fetch cur_addr into l_address_row;
4404
4405 if cur_addr%notfound then
4406 put_line('Primary contact address into not found');
4407 else
4408 -- Replace the person_id on the contact address record with
4409 -- the person_id of the employee being transfered out
4410 -- If this is not done, the mt_person_id column in the interface
4411 -- table will be that of the contact not the employee
4412 l_address_row.person_id := p_person_id;
4413 insert_address(
4414 p_transfer_name => l_transfer_name,
4415 p_effective_date => l_effdate,
4416 p_a => l_address_row,
4417 p_contact_name => l_contact_name,
4418 p_contact_type => l_contact_type);
4419 end if;
4420 close cur_addr;
4421 end if;
4422 close cur_contact_info;
4423
4424 -- get persons primary assignment
4425 open cur_assignments_f(p_person_id);
4426 fetch cur_assignments_f into l_assignment_id, l_position_id, l_business_group_id, l_grade_id, l_location_id;
4427
4428 if cur_assignments_f%notfound then
4429 put_line('No assignment found');
4430 close cur_assignments_f;
4431 -- Bug 3718167 Added Person,SSN in the message
4432 --Bug # 9329643 Modified SSN to Emp No
4433 log_message(
4434 p_procedure => l_proc_name,
4435 p_message =>
4436 'Could not get assignment data '||
4437 'for Employee: '|| l_people_row.full_name ||
4438 ' Emp No: '|| l_people_row.employee_number ||
4439 'person_id:'|| p_person_id);
4440
4441 hr_utility.set_message(8301, 'GHR_38537_MTO_NO_ASG');
4442 hr_utility.raise_error;
4443 end if;
4444 close cur_assignments_f;
4445
4446 ghr_history_fetch.fetch_assignment (
4447 p_assignment_id => l_assignment_id,
4448 p_date_effective => l_effdate,
4449 p_assignment_data => l_assignments_row,
4450 p_result_code => l_rc);
4451
4452 if l_rc = 'not_found' then
4453 --Bug#3718167 Added Name, SSN in the message.
4454 --Bug # 9329643 Modified SSN to Emp No
4455 log_message(
4456 p_procedure => l_proc_name,
4457 p_message =>
4458 'Could not get assignment data '||
4459 'for Employee: '|| l_people_row.full_name ||
4460 ' Emp No: '|| l_people_row.employee_number ||
4461 'person_id:'|| p_person_id);
4462 hr_utility.set_message(8301, 'GHR_38537_MTO_NO_ASG');
4463 hr_utility.raise_error;
4464 end if;
4465
4466 insert_assignment_f(p_transfer_name => l_transfer_name,
4467 p_effective_date => l_effdate,
4468 p_a => l_assignments_row);
4469
4470 -- get assignment extra info
4471 l_assignment_ei_row.information_type := null;
4472 ghr_history_fetch.fetch_asgei(
4473 p_assignment_id => l_assignment_id,
4474 p_information_type => 'GHR_US_ASG_SF52',
4475 p_date_effective => l_effdate,
4476 p_asg_ei_data => l_assignment_ei_row
4477 );
4478
4479 insert_assignment_ei(p_transfer_name => l_transfer_name,
4480 p_person_id => p_person_id,
4481 p_effective_date => l_effdate,
4482 p_a_ei => l_assignment_ei_row
4483 );
4484
4485 l_assignment_ei_row.information_type := null;
4486 ghr_history_fetch.fetch_asgei(
4487 p_assignment_id => l_assignment_id,
4488 p_information_type => 'GHR_US_ASG_NON_SF52',
4489 p_date_effective => l_effdate,
4490 p_asg_ei_data => l_assignment_ei_row
4491 );
4492
4493 insert_assignment_ei(p_transfer_name => l_transfer_name,
4494 p_person_id => p_person_id,
4495 p_effective_date => l_effdate,
4496 p_a_ei => l_assignment_ei_row
4497 );
4498
4499 l_assignment_ei_row.information_type := null;
4500 ghr_history_fetch.fetch_asgei(
4501 p_assignment_id => l_assignment_id,
4502 p_information_type => 'GHR_US_ASG_NTE_DATES',
4503 p_date_effective => l_effdate,
4504 p_asg_ei_data => l_assignment_ei_row
4505 );
4506
4507 insert_assignment_ei(p_transfer_name => l_transfer_name,
4508 p_person_id => p_person_id,
4509 p_effective_date => l_effdate,
4510 p_a_ei => l_assignment_ei_row
4511 );
4512
4513 -- get position info
4514 ghr_history_fetch.fetch_position(
4515 p_position_id => l_position_id,
4516 p_date_effective => l_effdate,
4517 p_position_data => l_position_row,
4518 p_result_code => l_rc
4519 );
4520
4521 insert_position(p_transfer_name => l_transfer_name,
4522 p_person_id => p_person_id,
4523 p_effective_date => l_effdate,
4524 p_pos => l_position_row);
4525
4526 -- get position extra info
4527 l_position_ei_row.information_type := null;
4528 ghr_history_fetch.fetch_positionei
4529 (p_position_id => l_position_id
4530 ,p_information_type => 'GHR_US_POS_GRP1'
4531 ,p_date_effective => l_effdate
4532 ,p_pos_ei_data => l_position_ei_row
4533 );
4534
4535 insert_position_ei(p_transfer_name => l_transfer_name,
4536 p_person_id => p_person_id,
4537 p_effective_date => l_effdate,
4538 p_pos_ei => l_position_ei_row);
4539
4540 l_position_ei_row.information_type := null;
4541 ghr_history_fetch.fetch_positionei
4542 (p_position_id => l_position_id
4543 ,p_information_type => 'GHR_US_POS_GRP2'
4544 ,p_date_effective => l_effdate
4545 ,p_pos_ei_data => l_position_ei_row
4546 );
4547
4548 insert_position_ei(p_transfer_name => l_transfer_name,
4549 p_person_id => p_person_id,
4550 p_effective_date => l_effdate,
4551 p_pos_ei => l_position_ei_row);
4552
4553 l_position_ei_row.information_type := null;
4554 ghr_history_fetch.fetch_positionei
4555 (p_position_id => l_position_id
4556 ,p_information_type => 'GHR_US_POSITION_DESCRIPTION'
4557 ,p_date_effective => l_effdate
4558 ,p_pos_ei_data => l_position_ei_row
4559 );
4560
4561 insert_position_ei(p_transfer_name => l_transfer_name,
4562 p_person_id => p_person_id,
4563 p_effective_date => l_effdate,
4564 p_pos_ei => l_position_ei_row);
4565
4566 l_position_ei_row.information_type := null;
4567 ghr_history_fetch.fetch_positionei
4568 (p_position_id => l_position_id
4569 ,p_information_type => 'GHR_US_POS_OBLIG'
4570 ,p_date_effective => l_effdate
4571 ,p_pos_ei_data => l_position_ei_row
4572 );
4573
4574 insert_position_ei(p_transfer_name => l_transfer_name,
4575 p_person_id => p_person_id,
4576 p_effective_date => l_effdate,
4577 p_pos_ei => l_position_ei_row);
4578
4579 l_position_ei_row.information_type := null;
4580 ghr_history_fetch.fetch_positionei
4581 (p_position_id => l_position_id
4582 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
4583 ,p_date_effective => l_effdate
4584 ,p_pos_ei_data => l_position_ei_row
4585 );
4586
4587 insert_position_ei(p_transfer_name => l_transfer_name,
4588 p_person_id => p_person_id,
4589 p_effective_date => l_effdate,
4590 p_pos_ei => l_position_ei_row);
4591 GHR_HISTORY_FETCH.fetch_position (
4592 p_position_id => l_position_id,
4593 p_date_effective => l_effdate,
4594 p_position_data => l_position_rec,
4595 p_result_code => l_result_code );
4596
4597 open cur_pos_definitions(l_position_rec.position_definition_id);
4598 fetch cur_pos_definitions into l_pos_defs;
4599
4600 if cur_pos_definitions%found then
4601 put_line('Position Definition data found');
4602 insert_position_defs(
4603 p_transfer_name => l_transfer_name,
4604 p_effective_date => l_effdate,
4605 p_person_id => p_person_id,
4606 p_flex_name => 'US Federal Position',
4607 p_pos_defs => l_pos_defs
4608 );
4609 end if;
4610 close cur_pos_definitions;
4611
4612 -- Processing Total Pay and Adjusted Basic Pay
4613 -- NAME DATE BUG COMMENTS
4614 -- Ashley 17-JUL-03 Payroll Intg Modified the Input Value name
4615 -- Changes from Total Salary -> Amount
4616 -- Adjusted Pay -> Amount
4617
4618
4619 ghr_api.retrieve_element_entry_value (
4620 p_element_name => 'Total Pay'
4621 ,p_input_value_name => 'Amount'
4622 ,p_assignment_id => l_assignment_id
4623 ,p_effective_date => l_effdate
4624 ,p_value => l_element.total_salary
4625 ,p_multiple_error_flag => l_multi_error_flag);
4626
4627 ghr_api.retrieve_element_entry_value (
4628 p_element_name => 'Basic Salary Rate'
4629 ,p_input_value_name => 'Rate'
4630 ,p_assignment_id => l_assignment_id
4631 ,p_effective_date => l_effdate
4632 ,p_value => l_element.salary
4633 ,p_multiple_error_flag => l_multi_error_flag);
4634
4635 ghr_api.retrieve_element_entry_value (
4636 -- FWFA Changes Bug#4444609
4637 p_element_name => 'Locality Pay or SR Supplement'
4638 -- FWFA Changes Modify 'Locality Pay' to 'Locality Pay or SR Supplement'
4639 ,p_input_value_name => 'Rate'
4640 -- Changed 'Amount' input value to 'Rate' by Ashu Gupta.
4641 ,p_assignment_id => l_assignment_id
4642 ,p_effective_date => l_effdate
4643 ,p_value => l_element.locality_adjustment
4644 ,p_multiple_error_flag => l_multi_error_flag);
4645
4646 ghr_api.retrieve_element_entry_value (
4647 p_element_name => 'Adjusted Basic Pay'
4648 ,p_input_value_name => 'Amount'
4649 ,p_assignment_id => l_assignment_id
4650 ,p_effective_date => l_effdate
4651 ,p_value => l_element.adjusted_pay
4652 ,p_multiple_error_flag => l_multi_error_flag);
4653
4654 ghr_api.retrieve_element_entry_value (
4655 p_element_name => 'Other Pay'
4656 ,p_input_value_name => 'Amount'
4657 ,p_assignment_id => l_assignment_id
4658 ,p_effective_date => l_effdate
4659 ,p_value => l_element.other_pay
4660 ,p_multiple_error_flag => l_multi_error_flag);
4661
4662 ghr_api.retrieve_element_entry_value (
4663 p_element_name => 'Health Benefits'
4664 ,p_input_value_name => 'Enrollment'
4665 ,p_assignment_id => l_assignment_id
4666 ,p_effective_date => l_effdate
4667 ,p_value => l_element.enrollment
4668 ,p_multiple_error_flag => l_multi_error_flag);
4669
4670 ghr_api.retrieve_element_entry_value (
4671 p_element_name => 'Health Benefits'
4672 ,p_input_value_name => 'Health Plan'
4673 ,p_assignment_id => l_assignment_id
4674 ,p_effective_date => l_effdate
4675 ,p_value => l_element.health_plan
4676 ,p_multiple_error_flag => l_multi_error_flag);
4677
4678 ghr_api.retrieve_element_entry_value (
4679 p_element_name => 'Health Benefits'
4680 ,p_input_value_name => 'Temps Total Cost'
4681 ,p_assignment_id => l_assignment_id
4682 ,p_effective_date => l_effdate
4683 ,p_value => l_element.temps_total_cost
4684 ,p_multiple_error_flag => l_multi_error_flag);
4685
4686 ghr_api.retrieve_element_entry_value (
4687 p_element_name => 'TSP'
4688 ,p_input_value_name => 'Amount'
4689 ,p_assignment_id => l_assignment_id
4690 ,p_effective_date => l_effdate
4691 ,p_value => l_element.tsp_amount
4692 ,p_multiple_error_flag => l_multi_error_flag);
4693
4694 ghr_api.retrieve_element_entry_value (
4695 p_element_name => 'TSP'
4696 ,p_input_value_name => 'Rate'
4697 ,p_assignment_id => l_assignment_id
4698 ,p_effective_date => l_effdate
4699 ,p_value => l_element.tsp_rate
4700 ,p_multiple_error_flag => l_multi_error_flag);
4701
4702 ghr_api.retrieve_element_entry_value (
4703 p_element_name => 'TSP'
4704 ,p_input_value_name => 'G Fund'
4705 ,p_assignment_id => l_assignment_id
4706 ,p_effective_date => l_effdate
4707 ,p_value => l_element.tsp_g_fund
4708 ,p_multiple_error_flag => l_multi_error_flag);
4709
4710 ghr_api.retrieve_element_entry_value (
4711 p_element_name => 'TSP'
4712 ,p_input_value_name => 'F Fund'
4713 ,p_assignment_id => l_assignment_id
4714 ,p_effective_date => l_effdate
4715 ,p_value => l_element.tsp_f_fund
4716 ,p_multiple_error_flag => l_multi_error_flag);
4717
4718 ghr_api.retrieve_element_entry_value (
4719 p_element_name => 'TSP'
4720 ,p_input_value_name => 'C Fund'
4721 ,p_assignment_id => l_assignment_id
4722 ,p_effective_date => l_effdate
4723 ,p_value => l_element.tsp_c_fund
4724 ,p_multiple_error_flag => l_multi_error_flag);
4725
4726 ghr_api.retrieve_element_entry_value (
4727 p_element_name => 'TSP'
4728 ,p_input_value_name => 'Status'
4729 ,p_assignment_id => l_assignment_id
4730 ,p_effective_date => l_effdate
4731 ,p_value => l_element.tsp_status
4732 ,p_multiple_error_flag => l_multi_error_flag);
4733
4734 ghr_api.retrieve_element_entry_value (
4735 p_element_name => 'TSP'
4736 ,p_input_value_name => 'Status Date'
4737 ,p_assignment_id => l_assignment_id
4738 ,p_effective_date => l_effdate
4739 ,p_value => l_element.tsp_status_date
4740 ,p_multiple_error_flag => l_multi_error_flag);
4741
4742 ghr_api.retrieve_element_entry_value (
4743 p_element_name => 'TSP'
4744 ,p_input_value_name => 'Eligibility Date'
4745 ,p_assignment_id => l_assignment_id
4746 ,p_effective_date => l_effdate
4747 ,p_value => l_element.tsp_eligibility_date
4748 ,p_multiple_error_flag => l_multi_error_flag);
4749
4750 ghr_api.retrieve_element_entry_value (
4751 p_element_name => 'Within Grade Increase'
4752 ,p_input_value_name => 'Status'
4753 ,p_assignment_id => l_assignment_id
4754 ,p_effective_date => l_effdate
4755 ,p_value => l_element.wgi_status
4756 ,p_multiple_error_flag => l_multi_error_flag);
4757
4758 ghr_api.retrieve_element_entry_value (
4759 p_element_name => 'Within Grade Increase'
4760 ,p_input_value_name => 'Date Due'
4761 ,p_assignment_id => l_assignment_id
4762 ,p_effective_date => l_effdate
4763 ,p_value => l_element.wgi_date_due
4764 ,p_multiple_error_flag => l_multi_error_flag);
4765
4766 ghr_api.retrieve_element_entry_value (
4767 p_element_name => 'Within Grade Increase'
4768 ,p_input_value_name => 'Pay Date'
4769 ,p_assignment_id => l_assignment_id
4770 ,p_effective_date => l_effdate
4771 ,p_value => l_element.wgi_pay_date
4772 ,p_multiple_error_flag => l_multi_error_flag);
4773
4774 ghr_api.retrieve_element_entry_value (
4775 p_element_name => 'Within Grade Increase'
4776 ,p_input_value_name => 'Last Increase Date'
4777 ,p_assignment_id => l_assignment_id
4778 ,p_effective_date => l_effdate
4779 ,p_value => l_element.wgi_last_increase_date
4780 ,p_multiple_error_flag => l_multi_error_flag);
4781
4782 ghr_api.retrieve_element_entry_value (
4783 p_element_name => 'Within Grade Increase'
4784 ,p_input_value_name => 'Postponmt Effective'
4785 ,p_assignment_id => l_assignment_id
4786 ,p_effective_date => l_effdate
4787 ,p_value => l_element.wgi_postponmt_effective
4788 ,p_multiple_error_flag => l_multi_error_flag);
4789
4790 ghr_api.retrieve_element_entry_value (
4791 p_element_name => 'Within Grade Increase'
4792 ,p_input_value_name => 'Postponmt Determ Due'
4793 ,p_assignment_id => l_assignment_id
4794 ,p_effective_date => l_effdate
4795 ,p_value => l_element.wgi_postponmt_determ_due
4796 ,p_multiple_error_flag => l_multi_error_flag);
4797
4798 ghr_api.retrieve_element_entry_value (
4799 p_element_name => 'FEGLI'
4800 ,p_input_value_name => 'FEGLI'
4801 ,p_assignment_id => l_assignment_id
4802 ,p_effective_date => l_effdate
4803 ,p_value => l_element.fegli
4804 ,p_multiple_error_flag => l_multi_error_flag);
4805
4806 ghr_api.retrieve_element_entry_value (
4807 p_element_name => 'FEGLI'
4808 ,p_input_value_name => 'Eligibility Expiration'
4809 ,p_assignment_id => l_assignment_id
4810 ,p_effective_date => l_effdate
4811 ,p_value => l_element.eligibility_expiration
4812 ,p_multiple_error_flag => l_multi_error_flag);
4813
4814 ghr_api.retrieve_element_entry_value (
4815 p_element_name => 'Retirement Plan'
4816 ,p_input_value_name => 'Plan'
4817 ,p_assignment_id => l_assignment_id
4818 ,p_effective_date => l_effdate
4819 ,p_value => l_element.retirement_plan
4820 ,p_multiple_error_flag => l_multi_error_flag);
4821
4822 ghr_api.retrieve_element_entry_value (
4823 p_element_name => 'Retention Allowance'
4824 ,p_input_value_name => 'Amount'
4825 ,p_assignment_id => l_assignment_id
4826 ,p_effective_date => l_effdate
4827 ,p_value => l_element.retention_allowance
4828 ,p_multiple_error_flag => l_multi_error_flag);
4829
4830 ghr_api.retrieve_element_entry_value (
4831 p_element_name => 'Retention Allowance'
4832 ,p_input_value_name => 'Percentage'
4833 ,p_assignment_id => l_assignment_id
4834 ,p_effective_date => l_effdate
4835 ,p_value => l_element.to_retention_allow_percentage
4836 ,p_multiple_error_flag => l_multi_error_flag);
4837
4838 ghr_api.retrieve_element_entry_value (
4839 p_element_name => 'Staffing Differential'
4840 ,p_input_value_name => 'Amount'
4841 ,p_assignment_id => l_assignment_id
4842 ,p_effective_date => l_effdate
4843 ,p_value => l_element.staffing_differential
4844 ,p_multiple_error_flag => l_multi_error_flag);
4845
4846 ghr_api.retrieve_element_entry_value (
4847 p_element_name => 'Staffing Differential'
4848 ,p_input_value_name => 'Percent'
4849 ,p_assignment_id => l_assignment_id
4850 ,p_effective_date => l_effdate
4851 ,p_value => l_element.to_staffing_diff_percentage
4852 ,p_multiple_error_flag => l_multi_error_flag);
4853
4854 ghr_api.retrieve_element_entry_value (
4855 p_element_name => 'Supervisory Differential'
4856 ,p_input_value_name => 'Amount'
4857 ,p_assignment_id => l_assignment_id
4858 ,p_effective_date => l_effdate
4859 ,p_value => l_element.supervisory_differential
4860 ,p_multiple_error_flag => l_multi_error_flag);
4861
4862 ghr_api.retrieve_element_entry_value (
4863 p_element_name => 'Supervisory Differential'
4864 ,p_input_value_name => 'Percentage'
4865 ,p_assignment_id => l_assignment_id
4866 ,p_effective_date => l_effdate
4867 ,p_value => l_element.to_supervisory_diff_percentage
4868 ,p_multiple_error_flag => l_multi_error_flag);
4869
4870 ghr_api.retrieve_element_entry_value (
4871 p_element_name => 'AUO'
4872 ,p_input_value_name => 'Amount'
4873 ,p_assignment_id => l_assignment_id
4874 ,p_effective_date => l_effdate
4875 ,p_value => l_element.auo_amount
4876 ,p_multiple_error_flag => l_multi_error_flag);
4877
4878 ghr_api.retrieve_element_entry_value (
4879 p_element_name => 'AUO'
4880 ,p_input_value_name => 'Premium Pay Ind'
4881 ,p_assignment_id => l_assignment_id
4882 ,p_effective_date => l_effdate
4883 ,p_value => l_element.auo_premium_pay_ind
4884 ,p_multiple_error_flag => l_multi_error_flag);
4885
4886 ghr_api.retrieve_element_entry_value (
4887 p_element_name => 'Availability Pay'
4888 ,p_input_value_name => 'Amount'
4889 ,p_assignment_id => l_assignment_id
4890 ,p_effective_date => l_effdate
4891 ,p_value => l_element.ap_amount
4892 ,p_multiple_error_flag => l_multi_error_flag);
4893
4894 ghr_api.retrieve_element_entry_value (
4895 p_element_name => 'Availability Pay'
4896 ,p_input_value_name => 'Premium Pay Ind'
4897 ,p_assignment_id => l_assignment_id
4898 ,p_effective_date => l_effdate
4899 ,p_value => l_element.ap_premium_pay_ind
4900 ,p_multiple_error_flag => l_multi_error_flag);
4901
4902 insert_element_entries(p_transfer_name => l_transfer_name,
4903 p_person_id => p_person_id,
4904 p_effective_date => l_effdate,
4905 p_element => l_element);
4906
4907 ghr_msl_pkg.get_sub_element_code_pos_title(
4908 l_position_id,
4909 p_person_id,
4910 l_business_group_id,
4911 l_assignment_id,
4912 l_effdate,
4913 l_misc.from_agency_code,
4914 l_misc.from_position_title,
4915 l_misc.from_position_desc_num,
4916 l_misc.from_position_seq_num);
4917
4918 open cur_grd(l_grade_id);
4919 fetch cur_grd into l_misc.from_pay_plan, l_misc.from_grade_or_level;
4920 close cur_grd;
4921
4922 l_misc.occ_series := ghr_api.get_job_occupational_series(
4923 p_person_id => p_person_id
4924 ,p_assignment_id => l_assignment_id
4925 ,p_effective_date => l_effdate);
4926
4927 -- per jmacgoy 3/5/98,
4928 -- assignment_location is duty_station_code
4929 open cur_lei(l_location_id);
4930 fetch cur_lei into l_misc.duty_station_desc, l_misc.duty_station_code;
4931 close cur_lei;
4932
4933 insert_misc(p_transfer_name => l_transfer_name,
4934 p_person_id => p_person_id,
4935 p_effective_date => l_effdate,
4936 p_misc => l_misc);
4937
4938 /*
4939 -- copy from_agency_code to segment Agency Code Transfer From (pei_info7)
4940 update_agency_transfer_from(
4941 p_person_id => p_person_id,
4942 p_effective_date => l_effdate,
4943 p_value => l_misc.from_agency_code);
4944 */
4945
4946 hr_utility.set_location('Leaving:'||l_proc, 60);
4947 -- Bug#4183516 Modified the Message Text.
4948 --Bug # 9329643 Modified SSN to Emp No
4949 log_message(
4950 p_procedure => l_proc_name,
4951 p_message => 'Interface table updated Successfully for ' || l_people_row.full_name ||
4952 ' Emp No: '|| l_people_row.employee_number);
4953 --||' successful');
4954
4955 p_retcode := 0;
4956 p_errbuf := null;
4957
4958 exception
4959 when others then
4960
4961 rollback to mto;
4962 --Bug # 9329643 Modified SSN to Emp No
4963 log_message(
4964 p_procedure => l_proc_name,
4965 p_message => 'Mass Transfer Out for ' || l_people_row.full_name ||
4966 ' Emp No: '|| l_people_row.employee_number ||
4967 ' failed with error message: '||sqlerrm(sqlcode));
4968
4969 commit;
4970 put_line(l_proc||' - raised exception');
4971
4972 p_errbuf := 'MTO: '|| sqlerrm(sqlcode);
4973 p_retcode := 2;
4974
4975 end mass_transfer_out;
4976
4977 begin
4978 g_dbms_output := false;
4979 g_message := null;
4980 g_log_name := null;
4981 end ghr_mto_int;