[Home] [Help]
PACKAGE BODY: APPS.GHR_MTI_INT
Source
1 PACKAGE BODY ghr_mti_int AS
2 /* $Header: ghmtiint.pkb 120.0.12010000.2 2008/08/05 15:07:34 ubhat ship $ */
3
4 g_datefmt constant varchar2(30) := 'DD-MON-YYYY';
5
6 function report_state(p_bool boolean, p_message varchar2)
7 return varchar2 is
8
9 l_proc varchar2(72) := g_package||'report_state';
10 l_message varchar2(100);
11
12 begin
13
14 hr_utility.set_location('Entering:'||l_proc, 10);
15
16 if p_bool = TRUE then
17 l_message := p_message|| 'TRUE';
18 else
19 l_message := p_message|| 'FALSE';
20 end if;
21
22 hr_utility.set_location('Leaving:'||l_proc, 10);
23 return l_message;
24
25 end report_state;
26
27 function ck_parameter_list(p_person_row in ghr_mt_people_f_v%rowtype)
28 return boolean is
29 l_proc varchar2(72) := g_package||'ck_parameter_list';
30
31 begin
32
33 hr_utility.set_location('Entering:'||l_proc, 10);
34
35 if p_person_row.last_name is null then
36 -- a persons last name is required to create an applicant
37 return FALSE;
38 elsif p_person_row.mt_person_id is null then
39 -- person_id required to differentiate between like named people
40 -- for update of transfer status, and to find all data in the
41 -- interface table for ther person
42 return FALSE;
43 end if;
44
45 hr_utility.set_location('Leaving:'||l_proc, 10);
46 return TRUE;
47
48 end ck_parameter_list;
49
50 procedure set_transfer_status(p_status_code varchar2,
51 p_transfer_name varchar2,
52 p_last_name varchar2,
53 p_person_id varchar2) is
54
55 l_proc varchar2(72) := g_package||'set_transfer_status';
56 begin
57
58 hr_utility.set_location('Entering:'||l_proc, 10);
59
60 -- update interface table to indicate result of transfer
61 update ghr_mt_people_f_v
62 set mt_status = p_status_code
63 where mt_name = p_transfer_name
64 and last_name = p_last_name
65 and mt_person_id = p_person_id;
66
67 hr_utility.set_location('Leaving:'||l_proc, 10);
68
69 exception
70 when others then
71 ghr_mto_int.put_line(l_proc||' - raised exception');
72 hr_utility.raise_error;
73
74 end set_transfer_status;
75
76 procedure update_mass_transfer_in_flag(p_person_id number,
77 p_transfer_id varchar2,
78 p_effective_date date) is
79
80 cursor cur_pei_id is
81 select person_extra_info_id, object_version_number
82 from per_people_extra_info
83 where person_id = p_person_id
84 and information_type = 'GHR_US_PER_MASS_ACTIONS';
85
86 l_person_extra_info_id per_people_extra_info.person_extra_info_id%type;
87 l_object_version_number per_people_extra_info.object_version_number%type;
88
89 l_proc varchar2(72) := g_package||'update_mass_transfer_in_flag';
90
91 begin
92
93 hr_utility.set_location('Entering:'||l_proc, 10);
94
95 open cur_pei_id;
96 fetch cur_pei_id into l_person_extra_info_id, l_object_version_number;
97
98 if cur_pei_id%notfound then -- create info type
99
100 ghr_person_extra_info_api.create_person_extra_info (
101 p_person_id => p_person_id
102 ,p_effective_date => p_effective_date
103 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
104 ,p_pei_information_category => 'GHR_US_PER_MASS_ACTIONS'
105 ,p_pei_information6 => 'Y'
106 ,p_pei_information8 => p_transfer_id
107 ,p_person_extra_info_id => l_person_extra_info_id
108 ,p_object_version_number => l_object_version_number
109 );
110
111 else
112
113 ghr_person_extra_info_api.update_person_extra_info (
114 p_person_extra_info_id => l_person_extra_info_id
115 ,p_object_version_number => l_object_version_number
116 ,p_effective_date => p_effective_date
117 ,p_pei_information6 => 'Y'
118 ,p_pei_information8 => p_transfer_id
119 );
120
121 end if;
122
123 close cur_pei_id;
124 hr_utility.set_location('Leaving:'||l_proc, 10);
125
126 exception
127 when others then
128 ghr_mto_int.put_line(l_proc||' - raised exception');
129 hr_utility.raise_error;
130
131 end;
132
133 --
134 -- If all information parameters are null then this procedure does nothing
135 -- otherwise it calls hr_person_extra_info_api.create_person_extra_info.
136 --
137 procedure create_person_extra_info_maybe (
138 p_person_id number
139 ,p_effective_date date
140 ,p_information_type varchar2
141 ,p_pei_information3 varchar2 default null
142 ,p_pei_information4 varchar2 default null
143 ,p_pei_information5 varchar2 default null
144 ,p_pei_information6 varchar2 default null
145 ,p_pei_information7 varchar2 default null
146 ,p_pei_information8 varchar2 default null
147 ,p_pei_information9 varchar2 default null
148 ,p_pei_information10 varchar2 default null
149 ,p_pei_information11 varchar2 default null
150 ,p_pei_information12 varchar2 default null
151 ,p_pei_information13 varchar2 default null
152 ,p_pei_information14 varchar2 default null
153 ,p_pei_information15 varchar2 default null
154 ,p_pei_information16 varchar2 default null
155 ,p_pei_information17 varchar2 default null) is
156
157 l_proc varchar2(72) := g_package||'create_person_extra_info_maybe';
158 l_concat varchar2(2000);
159 l_info3 varchar2(150) := p_pei_information3;
160
161 l_person_extra_info_id number(15);
162 l_object_version_number number(15);
163
164 begin
165
166 hr_utility.set_location('Entering:'||l_proc, 10);
167
168 ghr_mto_int.put('Person Extra Info: '|| p_information_type);
169
170 -- don't add info3 if DDF segment is GHR_US_PER_GROUP1
171 if p_information_type = 'GHR_US_PER_GROUP1' then
172 l_info3 := null;
173 end if;
174
175 -- if l_concat is null then there is no extra information, just
176 -- exit procedure doing nothing
177
178 l_concat := l_info3 ||
179 p_pei_information4 ||
180 p_pei_information5 ||
181 p_pei_information6 ||
182 p_pei_information7 ||
183 p_pei_information8 ||
184 p_pei_information9 ||
185 p_pei_information10 ||
186 p_pei_information11 ||
187 p_pei_information12 ||
188 p_pei_information13 ||
189 p_pei_information14 ||
190 p_pei_information15 ||
191 p_pei_information16 ||
192 p_pei_information17;
193
194 if l_concat is null then
195 ghr_mto_int.put_line(' > not created.');
196 return;
197 end if;
198
199 ghr_person_extra_info_api.create_person_extra_info (
200 p_validate => g_validate
201 ,p_person_id => p_person_id
202 ,p_effective_date => p_effective_date
203 ,p_information_type => p_information_type
204 ,p_pei_information_category => p_information_type
205 ,p_pei_information3 => l_info3
206 ,p_pei_information4 => p_pei_information4
207 ,p_pei_information5 => p_pei_information5
208 ,p_pei_information6 => p_pei_information6
209 ,p_pei_information7 => p_pei_information7
210 ,p_pei_information8 => p_pei_information8
211 ,p_pei_information9 => p_pei_information9
212 ,p_pei_information10 => p_pei_information10
213 ,p_pei_information11 => p_pei_information11
214 ,p_pei_information12 => p_pei_information12
215 ,p_pei_information13 => p_pei_information13
216 ,p_pei_information14 => p_pei_information14
217 ,p_pei_information15 => p_pei_information15
218 ,p_pei_information16 => p_pei_information16
219 ,p_pei_information17 => p_pei_information17
220 ,p_person_extra_info_id => l_person_extra_info_id
221 ,p_object_version_number => l_object_version_number
222 );
223
224 ghr_mto_int.put_line(' > ok, pei_id: '|| l_person_extra_info_id);
225 hr_utility.set_location('Leaving:'||l_proc, 30);
226
227 exception
228 when others then
229 ghr_mto_int.put_line(l_proc||' - raised exception');
230 hr_utility.raise_error;
231
232 end;
233
234 procedure create_sit_maybe (
235 p_person_id in number
236 ,p_business_group_id in number
237 ,p_id_flex_name in varchar2
238 ,p_effective_date in date
239 ,p_segment1 in varchar2 default null
240 ,p_segment2 in varchar2 default null
241 ,p_segment3 in varchar2 default null
242 ,p_segment4 in varchar2 default null
243 ,p_segment5 in varchar2 default null
244 ,p_segment6 in varchar2 default null
245 ,p_segment7 in varchar2 default null
246 ,p_segment8 in varchar2 default null
247 ,p_segment9 in varchar2 default null
248 ,p_segment10 in varchar2 default null
249 ,p_segment11 in varchar2 default null
250 ,p_segment12 in varchar2 default null
251 ,p_segment13 in varchar2 default null
252 ,p_segment14 in varchar2 default null) is
253
254 l_analysis_criteria_id number;
255 l_person_analysis_id number;
256 l_pea_object_version_number number;
257
258 l_id_flex_num number(15);
259
260 l_proc varchar2(72) := g_package||'create_sit_maybe';
261 l_concat varchar2(2000);
262
263 cursor cur_flex_num is
264 select id_flex_num
265 from fnd_id_flex_structures_tl
266 where id_flex_structure_name = p_id_flex_name
267 and language = 'US';
268
269 begin
270
271 hr_utility.set_location('Entering:'||l_proc, 10);
272
273 ghr_mto_int.put('Person Spcl Info: '|| p_id_flex_name);
274
275 l_concat := p_segment1 ||
276 p_segment2 ||
277 p_segment3 ||
278 p_segment4 ||
279 p_segment5 ||
280 p_segment6 ||
281 p_segment7 ||
282 p_segment8 ||
283 p_segment9 ||
284 p_segment10 ||
285 p_segment11 ||
286 p_segment12 ||
287 p_segment13 ||
288 p_segment14;
289
290 if l_concat is null then
291 ghr_mto_int.put_line(' > not created.');
292 return;
293 end if;
294
295 open cur_flex_num;
296 fetch cur_flex_num into l_id_flex_num;
297
298 if cur_flex_num%notfound then
299 close cur_flex_num;
300 hr_utility.set_message(8301, 'GHR_38532_MT_SIT_STRUCT');
301 hr_utility.raise_error;
302 end if;
303 close cur_flex_num;
304
305 ghr_sit_api.create_sit (
306 p_validate => g_validate
307 ,p_person_id => p_person_id
308 ,p_business_group_id => p_business_group_id
309 ,p_id_flex_num => l_id_flex_num
310 ,p_effective_date => p_effective_date
311 ,p_date_from => p_effective_date
312 ,p_segment1 => p_segment1
313 ,p_segment2 => p_segment2
314 ,p_segment3 => p_segment3
315 ,p_segment4 => p_segment4
316 ,p_segment5 => p_segment5
317 ,p_segment6 => p_segment6
318 ,p_segment7 => p_segment7
319 ,p_segment8 => p_segment8
320 ,p_segment9 => p_segment9
321 ,p_segment10 => p_segment10
322 ,p_segment11 => p_segment11
323 ,p_segment12 => p_segment12
324 ,p_segment13 => p_segment13
325 ,p_segment14 => p_segment14
326 ,p_analysis_criteria_id => l_analysis_criteria_id
327 ,p_person_analysis_id => l_person_analysis_id
328 ,p_pea_object_version_number => l_pea_object_version_number
329 );
330
331 ghr_mto_int.put_line(' > ok, criteria_id: '||
332 l_analysis_criteria_id||', analysis_id: '||
333 l_person_analysis_id);
334 hr_utility.set_location('Leaving:'||l_proc, 30);
335
336 exception
337 when others then
338 ghr_mto_int.put_line(l_proc||' - raised exception');
339 hr_utility.raise_error;
340
341 end;
342
343 procedure transfer_person_in(
344 p_transfer_name varchar2,
345 p_business_group_id number,
346 p_person_type_id number,
347 p_effective_date date,
348 p_tran ghr_mt_people_f_v%rowtype,
349 p_person_id out NOCOPY per_people_f.person_id%type,
350 p_result out NOCOPY varchar2) is
351
352 l_proc_name varchar2(30) := 'transfer_person_in';
353 l_proc varchar2(72) := g_package||l_proc_name;
354 l_applicant_date date;
355 l_accept_date date;
356 l_message varchar2(1000);
357
358 -- out parameters for create_us_applicant
359 l_applicant_number per_people_f.applicant_number%type;
360 l_person_id per_people_f.person_id%type;
361 -- Bug#3718167 Added variable l_exists_full_name
362 l_exists_full_name per_people_f.full_name%type;
363 l_exists_person_id per_people_f.person_id%type;
364 l_assignment_id number;
365 l_assignment_status_type_id number; ---AVR
366 l_address_id number;
367 l_application_id number;
368 l_per_object_version_number number;
369 l_asg_object_version_number number;
370 l_apl_object_version_number number;
371 l_per_effective_start_date date;
372 l_per_effective_end_date date;
373 l_full_name per_people_f.full_name%type;
374 l_per_comment_id number;
375 l_assignment_sequence number;
376 l_name_combination_warning boolean;
377 l_orig_hire_warning boolean;
378
379 l_pei ghr_mt_people_ei_v%rowtype;
380 l_sit ghr_mt_analysis_criteria_v%rowtype;
381 l_hr_user_type varchar2(20);
382 l_address ghr_mt_addresses_v%rowtype;
383
384 --
385 -- select all people extra info rows by transfer name and
386 -- person_id, The person_id must uniquely identify all information
387 -- in the interface table for a person by transfer_name.
388 --
389 cursor cur_people_ei is
390 select *
391 from ghr_mt_people_ei_v
392 where mt_name = p_transfer_name
393 and mt_person_id = p_tran.mt_person_id;
394
395 cursor cur_sit is
396 select *
397 from ghr_mt_analysis_criteria_v
398 where mt_name = p_transfer_name
399 and mt_person_id = p_tran.mt_person_id;
400
401 cursor cur_address is
402 select *
403 from ghr_mt_addresses_v
404 where mt_name = p_transfer_name
405 and mt_person_id = p_tran.mt_person_id
406 and mt_contact_name is null;
407
408 /*
409 cursor cur_contact_address is
410 select *
411 from ghr_mt_addresses_v
412 where mt_name = p_transfer_name
413 and mt_person_id = p_tran.mt_person_id
414 and mt_contact_name is not null;
415 */
416
417 ----Added for Assignment_status_type_id --AVR
418
419 cursor cur_asg_type is
420 select ASSIGNMENT_STATUS_TYPE_ID
421 from PER_ASSIGNMENT_STATUS_TYPES
422 where ASSIGNMENT_STATUS_TYPE_ID < 10
423 and user_status like '%Accepted%'
424 and business_group_id is null;
425
426 -- Bug#3718167 Added full_name in the selection
427 cursor cur_person_exists is
428 select person_id, full_name
429 from per_people_f
430 where national_identifier = p_tran.national_identifier
431 and business_group_id = p_business_group_id
432 and p_effective_date
433 between effective_start_date and effective_end_date;
434 begin
435
436 hr_utility.set_location('Entering:'||l_proc, 10);
437 p_result := 'P';
438
439 ghr_mto_int.put_line('Transfering: '||p_tran.last_name);
440
441 if ck_parameter_list(p_tran) = FALSE then
442 ghr_mto_int.put_line('Missing required data for transfer in');
443 ghr_mto_int.log_message(
444 p_procedure => l_proc_name,
445 p_message => 'Missing last_name and/or '||
446 'mt_person_id from ghr_mt_people_f_v');
447
448 -- so message gets into the log, since the caller rollsback on error
449 commit;
450
451 hr_utility.set_message(8301, 'GHR_38533_MTI_MISS_REQ');
452 hr_utility.raise_error;
453 end if;
454
455
456 -------------------------- changed the applicant from p_effective_date-2 to p_effective_date-1 for bug# 2488778
457
458 l_applicant_date := trunc(p_effective_date) - 1;
459 l_accept_date := trunc(p_effective_date) - 1;
460
461 ghr_mto_int.put_line(report_state(g_validate, 'Validate Flag:'));
462 hr_utility.set_location('Create Applicant:'||l_proc, 20);
463
464 for cur_person_exists_rec in cur_person_exists
465 loop
466 l_exists_person_id := cur_person_exists_rec.person_id;
467 l_exists_full_name := cur_person_exists_rec.full_name;
468 exit;
469 end loop;
470 --
471 -- on transfer in:
472 -- p_tran.comment_id contains comments
473 IF l_exists_person_id is NULL then
474 ghr_applicant_api.create_applicant (
475 p_validate => g_validate
476 ,p_date_received => l_applicant_date
477 ,p_business_group_id => p_business_group_id
478 ,p_last_name => p_tran.last_name
479 ,p_sex => p_tran.sex
480 ,p_person_type_id => p_person_type_id
481 ,p_applicant_number => l_applicant_number
482 ,P_per_COMMENTS => p_tran.comment_id
483 ,p_date_of_birth =>
484 to_date(p_tran.date_of_birth,g_datefmt)
485 ,P_EMAIL_ADDRESS => p_tran.email_address
486 ,P_EXPENSE_CHECK_SEND_TO_ADDRES =>
487 p_tran.expense_check_send_to_address
488 ,p_first_name => p_tran.first_name
489 ,P_KNOWN_AS => p_tran.known_as
490 ,P_MARITAL_STATUS => p_tran.MARITAL_STATUS
491 ,P_MIDDLE_NAMES => p_tran.MIDDLE_NAMES
492 ,P_NATIONALITY => p_tran.NATIONALITY
493 ,p_national_identifier => p_tran.national_identifier
494 ,P_PREVIOUS_LAST_NAME => p_tran.PREVIOUS_LAST_NAME
495 ,P_REGISTERED_DISABLED_FLAG => p_tran.REGISTERED_DISABLED_FLAG
496 ,P_TITLE => p_tran.TITLE
497 ,P_WORK_TELEPHONE => p_tran.WORK_TELEPHONE
498 ------------,P_ATTRIBUTE_CATEGORY => p_tran.ATTRIBUTE_CATEGORY
499 ,P_ATTRIBUTE1 => p_tran.attribute1
500 ,P_ATTRIBUTE2 => p_tran.ATTRIBUTE2
501 ,P_ATTRIBUTE3 => p_tran.ATTRIBUTE3
502 ,P_ATTRIBUTE4 => p_tran.ATTRIBUTE4
503 ,P_ATTRIBUTE5 => p_tran.ATTRIBUTE5
504 ,P_ATTRIBUTE6 => p_tran.ATTRIBUTE6
505 ,P_ATTRIBUTE7 => p_tran.ATTRIBUTE7
506 ,P_ATTRIBUTE8 => p_tran.ATTRIBUTE8
507 ,P_ATTRIBUTE9 => p_tran.ATTRIBUTE9
508 ,P_ATTRIBUTE10 => p_tran.ATTRIBUTE10
509 ,P_ATTRIBUTE11 => p_tran.ATTRIBUTE11
510 ,P_ATTRIBUTE12 => p_tran.ATTRIBUTE12
511 ,P_ATTRIBUTE13 => p_tran.ATTRIBUTE13
512 ,P_ATTRIBUTE14 => p_tran.ATTRIBUTE14
513 ,P_ATTRIBUTE15 => p_tran.ATTRIBUTE15
514 ,P_ATTRIBUTE16 => p_tran.ATTRIBUTE16
515 ,P_ATTRIBUTE17 => p_tran.ATTRIBUTE17
516 ,P_ATTRIBUTE18 => p_tran.ATTRIBUTE18
517 ,P_ATTRIBUTE19 => p_tran.ATTRIBUTE19
518 ,P_ATTRIBUTE20 => p_tran.ATTRIBUTE20
519 ,P_ATTRIBUTE21 => p_tran.ATTRIBUTE21
520 ,P_ATTRIBUTE22 => p_tran.ATTRIBUTE22
521 ,P_ATTRIBUTE23 => p_tran.ATTRIBUTE23
522 ,P_ATTRIBUTE24 => p_tran.ATTRIBUTE24
523 ,P_ATTRIBUTE25 => p_tran.ATTRIBUTE25
524 ,P_ATTRIBUTE26 => p_tran.ATTRIBUTE26
525 ,P_ATTRIBUTE27 => p_tran.ATTRIBUTE27
526 ,P_ATTRIBUTE28 => p_tran.ATTRIBUTE28
527 ,P_ATTRIBUTE29 => p_tran.ATTRIBUTE29
528 ,P_ATTRIBUTE30 => p_tran.ATTRIBUTE30
529 ,P_SUFFIX => p_tran.SUFFIX
530 ,P_PER_INFORMATION_CATEGORY => p_tran.PER_INFORMATION_CATEGORY
531 ,P_PER_INFORMATION1 => p_tran.PER_INFORMATION1
532 ,P_PER_INFORMATION2 => p_tran.PER_INFORMATION2
533 ,P_PER_INFORMATION3 => p_tran.PER_INFORMATION3
534 ,P_PER_INFORMATION4 => p_tran.PER_INFORMATION4
535 ,P_PER_INFORMATION5 => p_tran.PER_INFORMATION5
536 ,P_PER_INFORMATION6 => p_tran.PER_INFORMATION6
537 ,P_PER_INFORMATION7 => p_tran.PER_INFORMATION7
538 ,P_PER_INFORMATION8 => p_tran.PER_INFORMATION8
539 ,P_PER_INFORMATION9 => p_tran.PER_INFORMATION9
540 ,P_PER_INFORMATION10 => p_tran.PER_INFORMATION10
541 ,P_PER_INFORMATION11 => p_tran.PER_INFORMATION11
542 ,P_PER_INFORMATION12 => p_tran.PER_INFORMATION12
543 ,P_PER_INFORMATION13 => p_tran.PER_INFORMATION13
544 ,P_PER_INFORMATION14 => p_tran.PER_INFORMATION14
545 ,P_PER_INFORMATION15 => p_tran.PER_INFORMATION15
546 ,P_PER_INFORMATION16 => p_tran.PER_INFORMATION16
547 ,P_PER_INFORMATION17 => p_tran.PER_INFORMATION17
548 ,P_PER_INFORMATION18 => p_tran.PER_INFORMATION18
549 ,P_PER_INFORMATION19 => p_tran.PER_INFORMATION19
550 ,P_PER_INFORMATION20 => p_tran.PER_INFORMATION20
551 ,P_PER_INFORMATION21 => p_tran.PER_INFORMATION21
552 ,P_PER_INFORMATION22 => p_tran.PER_INFORMATION22
553 ,P_PER_INFORMATION23 => p_tran.PER_INFORMATION23
554 ,P_PER_INFORMATION24 => p_tran.PER_INFORMATION24
555 ,P_PER_INFORMATION25 => p_tran.PER_INFORMATION25
556 ,P_PER_INFORMATION26 => p_tran.PER_INFORMATION26
557 ,P_PER_INFORMATION27 => p_tran.PER_INFORMATION27
558 ,P_PER_INFORMATION28 => p_tran.PER_INFORMATION28
559 ,P_PER_INFORMATION29 => p_tran.PER_INFORMATION29
560 ,P_PER_INFORMATION30 => p_tran.PER_INFORMATION30
561 ,P_BACKGROUND_CHECK_STATUS => p_tran.BACKGROUND_CHECK_STATUS
562 ,P_BACKGROUND_DATE_CHECK =>
563 to_date(p_tran.BACKGROUND_DATE_CHECK,g_datefmt)
564 ,P_CORRESPONDENCE_LANGUAGE => p_tran.CORRESPONDENCE_LANGUAGE
565 ,P_FTE_CAPACITY => p_tran.FTE_CAPACITY
566 ,P_HOLD_APPLICANT_DATE_UNTIL =>
567 to_date(p_tran.HOLD_APPLICANT_DATE_UNTIL,g_datefmt)
568 ,P_HONORS => p_tran.HONORS
569 ,P_MAILSTOP => p_tran.MAILSTOP
570 ,P_OFFICE_NUMBER => p_tran.OFFICE_NUMBER
571 ,P_PRE_NAME_ADJUNCT => p_tran.PRE_NAME_ADJUNCT
572 ,P_PROJECTED_START_DATE =>
573 to_date(p_tran.PROJECTED_START_DATE,g_datefmt)
574 ,P_RESUME_EXISTS => p_tran.RESUME_EXISTS
575 ,P_RESUME_LAST_UPDATED =>
576 to_date(p_tran.RESUME_LAST_UPDATED,g_datefmt)
577 ,P_WORK_SCHEDULE => p_tran.WORK_SCHEDULE
578 ,p_person_id => l_person_id
579 ,p_assignment_id => l_assignment_id
580 ,p_application_id => l_application_id
581 ,p_per_object_version_number => l_per_object_version_number
582 ,p_asg_object_version_number => l_asg_object_version_number
583 ,p_apl_object_version_number => l_apl_object_version_number
584 ,p_per_effective_start_date => l_per_effective_start_date
585 ,p_per_effective_end_date => l_per_effective_end_date
586 ,p_full_name => l_full_name
587 ,p_per_comment_id => l_per_comment_id
588 ,p_assignment_sequence => l_assignment_sequence
589 ,p_name_combination_warning => l_name_combination_warning
590 ,p_orig_hire_warning => l_orig_hire_warning
591 );
592
593 -- assign out parameter p_person_id
594 p_person_id := l_person_id;
595
596 if g_validate = FALSE then
597 ghr_mto_int.put_line('===== Create Applicant =============');
598 ghr_mto_int.put_line('Person ID:'||l_person_id);
599 ghr_mto_int.put_line('Assignment ID:'||l_assignment_id);
600 ghr_mto_int.put_line('Application ID:'||l_application_id);
601 ghr_mto_int.put_line(
602 report_state(l_name_combination_warning,'Name Warning:'));
603 end if;
604
605 -------AVR bug 2467099
606 for cur_asg_type_rec in cur_asg_type
607 loop
608 l_assignment_status_type_id := cur_asg_type_rec.assignment_status_type_id;
609 end loop;
610
611
612 hr_utility.set_location('l_assignment_id : '||to_char(l_assignment_id), 29);
613 hr_utility.set_location('l_person_id : '||to_char(l_person_id) , 29);
614 hr_utility.set_location('l_asg_sts_tpe_id :' ||to_char(l_assignment_status_type_id), 29);
615
616 hr_utility.set_location('Accept Applicant:'||l_proc, 30);
617
618 if g_validate = FALSE then
619
620 ---------------------changed the p_datetrack_update_mode to CORRECTION for Bug#2488778
621
622 hr_assignment_internal.update_status_type_apl_asg (
623 p_effective_date => l_accept_date
624 ,p_datetrack_update_mode => 'CORRECTION'
625 ,p_assignment_id => l_assignment_id
626 -- ,p_object_version_number => l_per_object_version_number
627 -- Sundar Commented the above line and added the below one. Asg OVN shd be passed instead of per ovn. Bug 2944589
628 ,p_object_version_number => l_asg_object_version_number
629 ,p_expected_system_status => 'ACCEPTED'
630 ,p_assignment_status_type_id => l_assignment_status_type_id
631 ,p_effective_start_date => l_per_effective_start_date
632 ,p_effective_end_date => l_per_effective_end_date
633 );
634
635 ghr_mto_int.put_line('------- Accept Applicant -------------');
636 ghr_mto_int.put_line('Start Date: '||
637 to_char(l_per_effective_start_date,g_datefmt));
638 ghr_mto_int.put_line('End Date: '||
639 to_char(l_per_effective_end_date,g_datefmt));
640 ghr_mto_int.put_line('------------------------------------');
641 end if;
642
643 hr_utility.set_location('Person Extra Info:'||l_proc, 40);
644
645 for c_people_ei in cur_people_ei loop
646
647 l_pei := c_people_ei;
648
649 create_person_extra_info_maybe (
650 p_person_id => l_person_id
651 ,p_effective_date => l_accept_date
652 ,p_information_type => l_pei.information_type
653 ,p_pei_information3 => l_pei.pei_information3
654 ,p_pei_information4 => l_pei.pei_information4
655 ,p_pei_information5 => l_pei.pei_information5
656 ,p_pei_information6 => l_pei.pei_information6
657 ,p_pei_information7 => l_pei.pei_information7
658 ,p_pei_information8 => l_pei.pei_information8
659 ,p_pei_information9 => l_pei.pei_information9
660 ,p_pei_information10 => l_pei.pei_information10
661 ,p_pei_information11 => l_pei.pei_information11
662 ,p_pei_information12 => l_pei.pei_information12
663 ,p_pei_information13 => l_pei.pei_information13
664 ,p_pei_information14 => l_pei.pei_information14
665 ,p_pei_information15 => l_pei.pei_information15
666 ,p_pei_information16 => l_pei.pei_information16
667 ,p_pei_information17 => l_pei.pei_information17
668 );
669
670 end loop;
671
672 hr_utility.set_location('Person Spcl Info:'||l_proc, 50);
673
674 for c_sit in cur_sit loop
675
676 l_sit := c_sit;
677
678 create_sit_maybe (
679 p_person_id => l_person_id
680 ,p_business_group_id => p_business_group_id
681 ,p_id_flex_name => l_sit.mt_information_type
682 ,p_effective_date => l_accept_date
683 ,p_segment1 => l_sit.segment1
684 ,p_segment2 => l_sit.segment2
685 ,p_segment3 => l_sit.segment3
686 ,p_segment4 => l_sit.segment4
687 ,p_segment5 => l_sit.segment5
688 ,p_segment6 => l_sit.segment6
689 ,p_segment7 => l_sit.segment7
690 ,p_segment8 => l_sit.segment8
691 ,p_segment9 => l_sit.segment9
692 ,p_segment10 => l_sit.segment10
693 ,p_segment11 => l_sit.segment11
694 ,p_segment12 => l_sit.segment12
695 ,p_segment13 => l_sit.segment13
696 ,p_segment14 => l_sit.segment14
697 );
698
699 end loop;
700 commit;
701 -- get persons address info
702 open cur_address;
703 fetch cur_address into l_address;
704
705 IF cur_address%found then
706
707 -- Note: ghr_mt_address_v.region_1=county,
708 -- ghr_mt_address_v.region_2=state
709 --
710 -- This creates US style address and assumes region_1
711 -- is the county and region_2 is the state
712 hr_utility.set_location(' Address :'||l_proc, 55);
713 --
714 /***** Old code for address Bug 2467099
715 begin
716 hr_person_address_api.create_us_person_address (
717 P_EFFECTIVE_DATE => l_accept_date,
718 P_PERSON_ID => l_person_id,
719 P_PRIMARY_FLAG => l_address.primary_flag,
720 P_DATE_FROM =>
721 to_date(l_address.date_from,g_datefmt),
722 P_DATE_TO =>
723 to_date(l_address.date_to,g_datefmt),
724 P_COMMENTS => l_address.comments,
725 P_ADDRESS_TYPE => l_address.address_type,
726 P_ADDRESS_LINE1 => l_address.address_line1,
727 P_ADDRESS_LINE2 => l_address.address_line2,
728 P_ADDRESS_LINE3 => l_address.address_line3,
729 P_CITY => l_address.town_or_CITY,
730 P_STATE => l_address.region_2,
731 P_ZIP_CODE => l_address.postal_code,
732 P_COUNTY => l_address.region_1,
733 P_COUNTRY => l_address.COUNTRY,
734 P_TELEPHONE_NUMBER_1 => l_address.TELEPHONE_NUMBER_1,
735 P_TELEPHONE_NUMBER_2 => l_address.TELEPHONE_NUMBER_2,
736 ---------------P_ADDR_ATTRIBUTE_CATEGORY => l_address.ADDR_ATTRIBUTE_CATEGORY,
737 P_ADDR_ATTRIBUTE1 => l_address.ADDR_ATTRIBUTE1,
738 P_ADDR_ATTRIBUTE2 => l_address.ADDR_ATTRIBUTE2,
739 P_ADDR_ATTRIBUTE3 => l_address.ADDR_ATTRIBUTE3,
740 P_ADDR_ATTRIBUTE4 => l_address.ADDR_ATTRIBUTE4,
741 P_ADDR_ATTRIBUTE5 => l_address.ADDR_ATTRIBUTE5,
742 P_ADDR_ATTRIBUTE6 => l_address.ADDR_ATTRIBUTE6,
743 P_ADDR_ATTRIBUTE7 => l_address.ADDR_ATTRIBUTE7,
744 P_ADDR_ATTRIBUTE8 => l_address.ADDR_ATTRIBUTE8,
745 P_ADDR_ATTRIBUTE9 => l_address.ADDR_ATTRIBUTE9,
746 P_ADDR_ATTRIBUTE10 => l_address.ADDR_ATTRIBUTE10,
747 P_ADDR_ATTRIBUTE11 => l_address.ADDR_ATTRIBUTE11,
748 P_ADDR_ATTRIBUTE12 => l_address.ADDR_ATTRIBUTE12,
749 P_ADDR_ATTRIBUTE13 => l_address.ADDR_ATTRIBUTE13,
750 P_ADDR_ATTRIBUTE14 => l_address.ADDR_ATTRIBUTE14,
751 P_ADDR_ATTRIBUTE15 => l_address.ADDR_ATTRIBUTE15,
752 P_ADDR_ATTRIBUTE16 => l_address.ADDR_ATTRIBUTE16,
753 P_ADDR_ATTRIBUTE17 => l_address.ADDR_ATTRIBUTE17,
754 P_ADDR_ATTRIBUTE18 => l_address.ADDR_ATTRIBUTE18,
755 P_ADDR_ATTRIBUTE19 => l_address.ADDR_ATTRIBUTE19,
756 P_ADDR_ATTRIBUTE20 => l_address.ADDR_ATTRIBUTE20,
757 P_ADDRESS_ID => l_ADDRESS_ID,
758 P_OBJECT_VERSION_NUMBER => l_per_object_VERSION_NUMBER
759 );
760 exception when others then
761 hr_utility.set_location(' Error Address :'||l_proc, 56);
762 l_message := 'Error in Address creation ' || sqlerrm(sqlcode);
763 end;
764 ************/
765
766 l_hr_user_type := fnd_profile.value('HR_USER_TYPE');
767
768 IF l_hr_user_type = 'INT' THEN
769
770 begin
771
772 hr_person_address_api.create_us_person_address
773 (p_effective_date => l_accept_date
774 ,p_person_id => l_person_id
775 ,p_primary_flag => l_address.primary_flag
776 ,p_date_from => l_accept_date
777 ,p_address_line1 => l_address.address_line1
778 ,p_address_line2 => l_address.address_line2
779 ,p_address_line3 => l_address.address_line3
780 ,p_city => l_address.town_or_CITY
781 ,p_state => l_address.region_2
782 ,p_county => l_address.region_1
783 ,p_zip_code => l_address.postal_code
784 ,p_country => l_address.COUNTRY
785 ,p_address_id => l_ADDRESS_ID
786 ,p_object_version_number => l_per_object_VERSION_NUMBER
787 );
788 ghr_mto_int.log_message(
789 p_procedure => l_proc_name,
790 p_message => 'Address created Successfully');
791
792 exception
793 when others then
794 hr_utility.set_location(' User type INT Error Address :'||l_proc, 56);
795 -- Bug#3718167 Added Person Name and SSN in the log text
796 l_message := 'Error in INT Address creation for ' || l_full_name||
797 ' SSN: '||p_tran.national_identifier|| 'Error is:'||sqlerrm(sqlcode);
798 ghr_mto_int.log_message(
799 p_procedure => l_proc_name,
800 p_message => l_message);
801 end;
802
803
804 ELSIF l_hr_user_type = 'PER' THEN
805
806 begin
807
808 hr_person_address_api.create_person_address
809 (p_effective_date => l_accept_date
810 ,p_person_id => l_person_id
811 ,p_primary_flag => l_address.primary_flag
812 ,p_style => 'US_GLB_FED' --Bug# 4725292
813 ,p_date_from => l_accept_date
814 ,p_address_line1 => l_address.address_line1
815 ,p_address_line2 => l_address.address_line2
816 ,p_address_line3 => l_address.address_line3
817 ,p_town_or_city => l_address.town_or_CITY
818 ,p_region_1 => l_address.region_1
819 ,p_region_2 => l_address.region_2
820 ,p_postal_code => l_address.postal_code
821 ,p_country => l_address.COUNTRY
822 ,p_address_id => l_ADDRESS_ID
823 ,p_object_version_number => l_per_object_VERSION_NUMBER
824 );
825
826 ghr_mto_int.log_message(
827 p_procedure => l_proc_name,
828 p_message => 'PER Address created Successfully');
829 exception when others then
830 hr_utility.set_location(' User type PER Error Address :'||l_proc, 56);
831 -- Bug#3718167 Added Person Name and SSN in the log text
832 l_message := 'Error in PER Address creation for ' || l_full_name||
833 ' SSN: '||p_tran.national_identifier|| 'Error is:'||sqlerrm(sqlcode);
834 ghr_mto_int.log_message(
835 p_procedure => l_proc_name,
836 p_message => l_message);
837 end;
838 END IF;
839
840 END IF;
841
842 close cur_address;
843
844 ghr_mto_int.put_line('* Successful');
845 hr_utility.set_location('Leaving:'||l_proc, 50);
846
847 ELSE
848 -- Employee exists with same SSN....
849 -- Bug#3718167 Added Person Full Name in the Log Message
850 ghr_mto_int.log_message(
851 p_procedure => l_proc_name,
852 p_message => 'Person exists with the same SSN : ' || p_tran.national_identifier ||
853 ' in the given business group with person Full Name as '||l_exists_full_name ||
854 ' and the person id is ' ||to_char(l_exists_person_id) );
855 END IF;
856
857 exception
858 when others then
859
860 rollback;
861 -- NOCOPY Changes Sundar 03Dec2003
862 p_person_id := NULL;
863 p_result := NULL;
864 -- End NOCOPY changes
865 ghr_mto_int.put_line(l_proc||' - raised exception');
866 ghr_mto_int.put_line(sqlerrm);
867
868 ghr_mto_int.log_message(
869 p_procedure => l_proc_name,
870 p_message => l_message);
871
872 hr_utility.raise_error;
873
874 end transfer_person_in;
875
876 --
877 -- For all rows in ghr_mt_people_f_v where mt_status = 'Y' and
878 -- name = ghr_mass_transfers.name do the following:
879 -- 1. create an applicant
880 -- 2. accept the applicant
881 -- 3. copy information from the row into person and person analysis
882 -- ddf's
883 --
884 -- Last name and person_id must exist in ghr_mt_people_f_v for each person
885 -- transfered in. Last name is required by create_us_applicant()
886 -- and person_id is necessary to differentiate between like named people
887 -- when setting the mt_status column to the transfer result (P or E)
888 --
889 -- All rows in the interface table pertaining to a person in a transfer
890 -- group (l_transfer_name) must contain the same mt_person_id. This
891 -- is used to query data from the interface table.
892 --
893 -- Ghr_mt_people_f_v.mt_status is set to 'P' for each successful
894 -- transfer and set to 'E' if an error was encountered. The
895 -- transfer name, last_name, and national_identifier (ssn) is
896 -- expected to be unique.
897 --
898 -- Parameters:
899 -- p_transfer_id: id of row in ghr_mass_transfers containing
900 -- transfer information
901 -- p_business_group_id: identifies the business group
902 --
903 procedure mass_transfer_in(p_errbuf out NOCOPY varchar2,
904 p_retcode out NOCOPY number,
905 p_transfer_id in number,
906 p_business_group_id in number) is
907
908 l_proc_name varchar2(30) := 'mass_transfer_in';
909 l_proc varchar2(72) := g_package||l_proc_name;
910
911 l_person_type_id per_person_types.person_type_id%type;
912 l_person_type_mti per_person_types.user_person_type%type;
913 l_person_type_mti_id per_person_types.person_type_id%type;
914 l_transfer_name ghr_mass_transfers.name%type;
915 l_effective_date ghr_mass_transfers.effective_date%type;
916 l_person_row ghr_mt_people_f_v%rowtype;
917 l_result varchar2(1);
918 l_person_id per_people_f.person_id%type;
919 l_inter_bg_transfer varchar2(1);
920
921 l_error_count number := 0;
922 l_transfer_count number := 0;
923 l_message varchar2(250);
924 l_errbuf varchar2(2000);
925 l_retcode number;
926
927 e_transfer_in_warning exception;
928
929 cursor cur_person_type is
930 select person_type_id
931 from per_person_types
932 where business_group_id = p_business_group_id
933 and user_person_type = 'Applicant';
934
935 cursor cur_person_type_mti is
936 select b.USER_PERSON_TYPE usertype,
937 b.person_type_id person_type_id
938 from per_all_people_f a,
939 per_person_types b
940 where a.BUSINESS_GROUP_ID = b.BUSINESS_GROUP_ID
941 and a.business_group_id = p_business_group_id
942 and a.PERSON_TYPE_ID = b.PERSON_TYPE_ID
943 and a.person_id = l_person_row.mt_person_id
944 and l_effective_date
945 between a.effective_start_date and a.effective_end_date;
946
947
948 -- Rows in ghr_mt_people_f_v with mt_status of 'Y' have been
949 -- selected for transfer in
950 cursor cur_people is
951 select *
952 from ghr_mt_people_f_v
953 where mt_name = l_transfer_name
954 and mt_status = 'Y';
955
956 begin
957
958 hr_utility.set_location('Entering:'||l_proc, 10);
959 ghr_mto_int.set_log_program_name('Mass Transfer In');
960
961 ghr_mto_int.get_transfer_parameters(
962 p_mass_transfer_id => p_transfer_id,
963 p_transfer_name => l_transfer_name,
964 p_effective_date => l_effective_date,
965 p_inter_bg_transfer => l_inter_bg_transfer);
966
967 if l_effective_date is null then
968 ghr_mto_int.log_message(
969 p_procedure => l_proc_name,
970 p_message => 'Failed to get Transfer In information from'||
971 'GHR_MASS_TRANSFERS for mass_transfer_id:'||
972 p_transfer_id);
973
974 hr_utility.set_message(8301, 'GHR_38534_MT_ID_ERR');
975 hr_utility.raise_error;
976 end if;
977
978 ghr_mto_int.put_line('Starting Transfer In for:'||l_transfer_name);
979
980 -- find the person type id for applicant in the business group
981 open cur_person_type;
982 fetch cur_person_type into l_person_type_id;
983 close cur_person_type;
984
985 if l_person_type_id is null then
986 ghr_mto_int.log_message(
987 p_procedure => l_proc_name,
988 p_message =>
989 'Failed to get the person_type_id for APPLICANT '||
990 'in the business group');
991 hr_utility.set_message(8301, 'GHR_38535_NO_MTI_PERSON_TYPE');
992 hr_utility.raise_error;
993 end if;
994
995 -- for all people selected for transfer in
996 for c_person in cur_people loop
997
998 l_person_row := c_person;
999 l_result := 'P'; -- if we don't call transfer_person_in()
1000
1001 for cur_person_type_mti_rec in cur_person_type_mti
1002 loop
1003 l_person_type_mti := cur_person_type_mti_rec.usertype;
1004 l_person_type_mti_id := cur_person_type_mti_rec.person_type_id;
1005 exit;
1006 end loop;
1007
1008 if l_person_type_mti = 'Employee' then
1009 -- Bug#3718167 Added Person Name, SSN
1010 ghr_mto_int.log_message(
1011 p_procedure => l_proc_name,
1012 p_message =>
1013 'Error: Selected person: '||c_person.full_name||'(SSN: '||c_person.national_identifier||
1014 ') contains an invalid Person Type as of the Effective ' ||
1015 'Date of the Transfer In action. Person Type may not be Employee. Please ' ||
1016 'make sure this person is not an Employee as of the Effective Date of the ' ||
1017 'Transfer In, and then resubmit the Transfer In action.');
1018 hr_utility.raise_error;
1019 end if;
1020
1021 -- A person is transfered in only if this is not an
1022 -- inter business group transfer
1023 if nvl(l_person_row.mt_inter_bg_transfer,'N') <> 'Y' then
1024 begin
1025 transfer_person_in(
1026 p_transfer_name => l_transfer_name,
1027 p_business_group_id => p_business_group_id,
1028 p_person_type_id => l_person_type_id,
1029 p_effective_date => l_effective_date,
1030 p_tran => l_person_row,
1031 p_person_id => l_person_id,
1032 p_result => l_result);
1033 exception
1034 when others then
1035 l_result := 'E';
1036 l_error_count := l_error_count + 1;
1037 -- Bug#3718167 Added Full Name, SSN in the log message
1038 ghr_mto_int.log_message(
1039 p_procedure => l_proc_name,
1040 p_message =>
1041 'Mass Transfer In for person: '|| c_person.full_name||' SSN: '||c_person.national_identifier||
1042 ' Person ID: '||l_person_id||
1043 ' failed with error message: '|| sqlerrm(sqlcode));
1044
1045 end;
1046 else
1047 l_person_id := to_number(l_person_row.mt_person_id);
1048 end if;
1049
1050 set_transfer_status(
1051 p_status_code => l_result,
1052 p_transfer_name => l_transfer_name,
1053 p_last_name => l_person_row.last_name,
1054 p_person_id => l_person_row.mt_person_id);
1055
1056 if l_result = 'P' then
1057 update_mass_transfer_in_flag(
1058 p_person_id => l_person_id,
1059 p_transfer_id => p_transfer_id,
1060 p_effective_date => l_effective_date);
1061 end if;
1062
1063 commit;
1064 ghr_mto_int.put_line('Committed');
1065
1066 l_transfer_count := l_transfer_count + 1;
1067
1068 end loop;
1069
1070 -- always call sri's package to create sf52's
1071 begin
1072
1073 ghr_mti_app.populate_and_create_52(
1074 p_errbuf => l_errbuf,
1075 p_retcode => l_retcode,
1076 p_mtin_name => l_transfer_name,
1077 p_mtin_id => p_transfer_id,
1078 p_effective_date => l_effective_date,
1079 p_business_group_id => p_business_group_id);
1080
1081 exception
1082 when others then
1083
1084 ghr_mto_int.put_line('ghr_mti_app.populate_and_create_52'||
1085 ' - raised exception');
1086
1087 rollback; -- rollback changes made by the procedure
1088
1089 -- return errbuf and retcode returned from sri's procedure
1090 p_errbuf := l_errbuf;
1091 p_retcode := l_retcode;
1092
1093 ghr_mto_int.log_message(
1094 p_procedure => l_proc_name,
1095 p_message =>
1096 'Mass Transfer In for '||
1097 l_transfer_name ||
1098 'Sql err '||sqlerrm(sqlcode)||
1099 l_errbuf || ' from populate_and_create_52()');
1100 --- ' recieved error message: "'||
1101
1102 commit;
1103 hr_utility.raise_error;
1104 end;
1105
1106 -- notify user that at data from the interface table was not
1107 -- successfully transfered in by transfer_person_in()
1108 if l_error_count <> 0 then
1109 raise e_transfer_in_warning;
1110 end if;
1111
1112 p_errbuf := null;
1113 p_retcode := 0;
1114
1115 ghr_mto_int.log_message(
1116 p_procedure => l_proc_name,
1117 p_message =>
1118 'Mass Transfer In for '||
1119 l_transfer_name || ' transfered '||l_transfer_count ||
1120 ' employees.');
1121
1122 commit;
1123 hr_utility.set_location('Leaving:'||l_proc, 10);
1124
1125 exception
1126 when e_transfer_in_warning then
1127 l_message := 'Warning: Mass Transfer In for transfer: '||
1128 l_transfer_name ||' was unable to transfer '||
1129 l_error_count || ' people out of '|| l_transfer_count;
1130
1131 ghr_mto_int.log_message(
1132 p_procedure => l_proc_name,
1133 p_message => l_message);
1134
1135 p_errbuf := l_message;
1136 p_retcode := 1; -- signal warning condition to concurrent mgr
1137
1138 commit;
1139
1140 when others then
1141 ghr_mto_int.put_line(l_proc||' - raised exception');
1142
1143 if l_person_type_mti <> 'Employee' then
1144 ghr_mto_int.log_message(
1145 p_procedure => l_proc_name,
1146 p_message =>
1147 'Mass Transfer In for mass_transfer_id: '||
1148 p_transfer_id || ' failed with error message: '||
1149 sqlerrm(sqlcode));
1150 end if;
1151
1152 p_errbuf := sqlerrm(sqlcode);
1153 p_retcode := 2; -- signal error condition to concurrent mgr
1154
1155 commit;
1156
1157 end mass_transfer_in;
1158
1159 end ghr_mti_int;