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