[Home] [Help]
PACKAGE BODY: APPS.GHR_MTI_INT
Source
1 PACKAGE BODY ghr_mti_int AS
2 /* $Header: ghmtiint.pkb 120.2 2010/04/09 05:28:59 vmididho noship $ */
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 --Bug # 9329643 Modified SSN to Emp No
797 l_message := 'Error in INT Address creation for ' || l_full_name||
798 ' Emp No: '||p_tran.employee_number|| 'Error is:'||sqlerrm(sqlcode);
799 ghr_mto_int.log_message(
800 p_procedure => l_proc_name,
801 p_message => l_message);
802 end;
803
804
805 ELSIF l_hr_user_type = 'PER' THEN
806
807 begin
808
809 hr_person_address_api.create_person_address
810 (p_effective_date => l_accept_date
811 ,p_person_id => l_person_id
812 ,p_primary_flag => l_address.primary_flag
813 ,p_style => 'US_GLB_FED' --Bug# 4725292
814 ,p_date_from => l_accept_date
815 ,p_address_line1 => l_address.address_line1
816 ,p_address_line2 => l_address.address_line2
817 ,p_address_line3 => l_address.address_line3
818 ,p_town_or_city => l_address.town_or_CITY
819 ,p_region_1 => l_address.region_1
820 ,p_region_2 => l_address.region_2
821 ,p_postal_code => l_address.postal_code
822 ,p_country => l_address.COUNTRY
823 ,p_address_id => l_ADDRESS_ID
824 ,p_object_version_number => l_per_object_VERSION_NUMBER
825 );
826
827 ghr_mto_int.log_message(
828 p_procedure => l_proc_name,
829 p_message => 'PER Address created Successfully');
830 exception when others then
831 hr_utility.set_location(' User type PER Error Address :'||l_proc, 56);
832 -- Bug#3718167 Added Person Name and SSN in the log text
833 --Bug # 9329643 Modified SSN to Emp No
834 l_message := 'Error in PER Address creation for ' || l_full_name||
835 ' Emp No: '||p_tran.employee_number|| 'Error is:'||sqlerrm(sqlcode);
836 ghr_mto_int.log_message(
837 p_procedure => l_proc_name,
838 p_message => l_message);
839 end;
840 END IF;
841
842 END IF;
843
844 close cur_address;
845
846 ghr_mto_int.put_line('* Successful');
847 hr_utility.set_location('Leaving:'||l_proc, 50);
848
849 ELSE
850 -- Employee exists with same SSN....
851 -- Bug#3718167 Added Person Full Name in the Log Message
852 --Bug # 9329643 Modified SSN to Emp No
853 ghr_mto_int.log_message(
854 p_procedure => l_proc_name,
855 p_message => 'Person exists with the same Emp No : ' || p_tran.employee_number ||
856 ' in the given business group with person Full Name as '||l_exists_full_name ||
857 ' and the person id is ' ||to_char(l_exists_person_id) );
858 END IF;
859
860 exception
861 when others then
862
863 rollback;
864 -- NOCOPY Changes Sundar 03Dec2003
865 p_person_id := NULL;
866 p_result := NULL;
867 -- End NOCOPY changes
868 ghr_mto_int.put_line(l_proc||' - raised exception');
869 ghr_mto_int.put_line(sqlerrm);
870
871 ghr_mto_int.log_message(
872 p_procedure => l_proc_name,
873 p_message => l_message);
874
875 hr_utility.raise_error;
876
877 end transfer_person_in;
878
879 --
880 -- For all rows in ghr_mt_people_f_v where mt_status = 'Y' and
881 -- name = ghr_mass_transfers.name do the following:
882 -- 1. create an applicant
883 -- 2. accept the applicant
884 -- 3. copy information from the row into person and person analysis
885 -- ddf's
886 --
887 -- Last name and person_id must exist in ghr_mt_people_f_v for each person
888 -- transfered in. Last name is required by create_us_applicant()
889 -- and person_id is necessary to differentiate between like named people
890 -- when setting the mt_status column to the transfer result (P or E)
891 --
892 -- All rows in the interface table pertaining to a person in a transfer
893 -- group (l_transfer_name) must contain the same mt_person_id. This
894 -- is used to query data from the interface table.
895 --
896 -- Ghr_mt_people_f_v.mt_status is set to 'P' for each successful
897 -- transfer and set to 'E' if an error was encountered. The
898 -- transfer name, last_name, and national_identifier (ssn) is
899 -- expected to be unique.
900 --
901 -- Parameters:
902 -- p_transfer_id: id of row in ghr_mass_transfers containing
903 -- transfer information
904 -- p_business_group_id: identifies the business group
905 --
906 procedure mass_transfer_in(p_errbuf out NOCOPY varchar2,
907 p_retcode out NOCOPY number,
908 p_transfer_id in number,
909 p_business_group_id in number) is
910
911 l_proc_name varchar2(30) := 'mass_transfer_in';
912 l_proc varchar2(72) := g_package||l_proc_name;
913
914 l_person_type_id per_person_types.person_type_id%type;
915 l_person_type_mti per_person_types.user_person_type%type;
916 l_person_type_mti_id per_person_types.person_type_id%type;
917 l_transfer_name ghr_mass_transfers.name%type;
918 l_effective_date ghr_mass_transfers.effective_date%type;
919 l_person_row ghr_mt_people_f_v%rowtype;
920 l_result varchar2(1);
921 l_person_id per_people_f.person_id%type;
922 l_inter_bg_transfer varchar2(1);
923
924 l_error_count number := 0;
925 l_transfer_count number := 0;
926 l_message varchar2(250);
927 l_errbuf varchar2(2000);
928 l_retcode number;
929
930 e_transfer_in_warning exception;
931
932 cursor cur_person_type is
933 select person_type_id
934 from per_person_types
935 where business_group_id = p_business_group_id
936 and user_person_type = 'Applicant';
937
938 cursor cur_person_type_mti is
939 select b.USER_PERSON_TYPE usertype,
940 b.person_type_id person_type_id
941 from per_all_people_f a,
942 per_person_types b
943 where a.BUSINESS_GROUP_ID = b.BUSINESS_GROUP_ID
944 and a.business_group_id = p_business_group_id
945 and a.PERSON_TYPE_ID = b.PERSON_TYPE_ID
946 and a.person_id = l_person_row.mt_person_id
947 and l_effective_date
948 between a.effective_start_date and a.effective_end_date;
949
950
951 -- Rows in ghr_mt_people_f_v with mt_status of 'Y' have been
952 -- selected for transfer in
953 cursor cur_people is
954 select *
955 from ghr_mt_people_f_v
956 where mt_name = l_transfer_name
957 and mt_status = 'Y';
958
959 begin
960
961 hr_utility.set_location('Entering:'||l_proc, 10);
962 ghr_mto_int.set_log_program_name('Mass Transfer In');
963
964 ghr_mto_int.get_transfer_parameters(
965 p_mass_transfer_id => p_transfer_id,
966 p_transfer_name => l_transfer_name,
967 p_effective_date => l_effective_date,
968 p_inter_bg_transfer => l_inter_bg_transfer);
969
970 if l_effective_date is null then
971 ghr_mto_int.log_message(
972 p_procedure => l_proc_name,
973 p_message => 'Failed to get Transfer In information from'||
974 'GHR_MASS_TRANSFERS for mass_transfer_id:'||
975 p_transfer_id);
976
977 hr_utility.set_message(8301, 'GHR_38534_MT_ID_ERR');
978 hr_utility.raise_error;
979 end if;
980
981 ghr_mto_int.put_line('Starting Transfer In for:'||l_transfer_name);
982
983 -- find the person type id for applicant in the business group
984 open cur_person_type;
985 fetch cur_person_type into l_person_type_id;
986 close cur_person_type;
987
988 if l_person_type_id is null then
989 ghr_mto_int.log_message(
990 p_procedure => l_proc_name,
991 p_message =>
992 'Failed to get the person_type_id for APPLICANT '||
993 'in the business group');
994 hr_utility.set_message(8301, 'GHR_38535_NO_MTI_PERSON_TYPE');
995 hr_utility.raise_error;
996 end if;
997
998 -- for all people selected for transfer in
999 for c_person in cur_people loop
1000
1001 l_person_row := c_person;
1002 l_result := 'P'; -- if we don't call transfer_person_in()
1003
1004 for cur_person_type_mti_rec in cur_person_type_mti
1005 loop
1006 l_person_type_mti := cur_person_type_mti_rec.usertype;
1007 l_person_type_mti_id := cur_person_type_mti_rec.person_type_id;
1008 exit;
1009 end loop;
1010
1011 if l_person_type_mti = 'Employee' then
1012 -- Bug#3718167 Added Person Name, SSN
1013 --Bug # 9329643 Modified SSN to Emp No
1014 ghr_mto_int.log_message(
1015 p_procedure => l_proc_name,
1016 p_message =>
1017 'Error: Selected person: '||c_person.full_name||'(Emp No: '||c_person.employee_number||
1018 ') contains an invalid Person Type as of the Effective ' ||
1019 'Date of the Transfer In action. Person Type may not be Employee. Please ' ||
1020 'make sure this person is not an Employee as of the Effective Date of the ' ||
1021 'Transfer In, and then resubmit the Transfer In action.');
1022 hr_utility.raise_error;
1023 end if;
1024
1025 -- A person is transfered in only if this is not an
1026 -- inter business group transfer
1027 if nvl(l_person_row.mt_inter_bg_transfer,'N') <> 'Y' then
1028 begin
1029 transfer_person_in(
1030 p_transfer_name => l_transfer_name,
1031 p_business_group_id => p_business_group_id,
1032 p_person_type_id => l_person_type_id,
1033 p_effective_date => l_effective_date,
1034 p_tran => l_person_row,
1035 p_person_id => l_person_id,
1036 p_result => l_result);
1037 exception
1038 when others then
1039 l_result := 'E';
1040 l_error_count := l_error_count + 1;
1041 -- Bug#3718167 Added Full Name, SSN in the log message
1042 --Bug # 9329643 Modified SSN to Emp No
1043 ghr_mto_int.log_message(
1044 p_procedure => l_proc_name,
1045 p_message =>
1046 'Mass Transfer In for person: '|| c_person.full_name||' Emp No: '||c_person.employee_number||
1047 ' Person ID: '||l_person_id||
1048 ' failed with error message: '|| sqlerrm(sqlcode));
1049
1050 end;
1051 else
1052 l_person_id := to_number(l_person_row.mt_person_id);
1053 end if;
1054
1055 set_transfer_status(
1056 p_status_code => l_result,
1057 p_transfer_name => l_transfer_name,
1058 p_last_name => l_person_row.last_name,
1059 p_person_id => l_person_row.mt_person_id);
1060
1061 if l_result = 'P' then
1062 update_mass_transfer_in_flag(
1063 p_person_id => l_person_id,
1064 p_transfer_id => p_transfer_id,
1065 p_effective_date => l_effective_date);
1066 end if;
1067
1068 commit;
1069 ghr_mto_int.put_line('Committed');
1070
1071 l_transfer_count := l_transfer_count + 1;
1072
1073 end loop;
1074
1075 -- always call sri's package to create sf52's
1076 begin
1077
1078 ghr_mti_app.populate_and_create_52(
1079 p_errbuf => l_errbuf,
1080 p_retcode => l_retcode,
1081 p_mtin_name => l_transfer_name,
1082 p_mtin_id => p_transfer_id,
1083 p_effective_date => l_effective_date,
1084 p_business_group_id => p_business_group_id);
1085
1086 exception
1087 when others then
1088
1089 ghr_mto_int.put_line('ghr_mti_app.populate_and_create_52'||
1090 ' - raised exception');
1091
1092 rollback; -- rollback changes made by the procedure
1093
1094 -- return errbuf and retcode returned from sri's procedure
1095 p_errbuf := l_errbuf;
1096 p_retcode := l_retcode;
1097
1098 ghr_mto_int.log_message(
1099 p_procedure => l_proc_name,
1100 p_message =>
1101 'Mass Transfer In for '||
1102 l_transfer_name ||
1103 'Sql err '||sqlerrm(sqlcode)||
1104 l_errbuf || ' from populate_and_create_52()');
1105 --- ' recieved error message: "'||
1106
1107 commit;
1108 hr_utility.raise_error;
1109 end;
1110
1111 -- notify user that at data from the interface table was not
1112 -- successfully transfered in by transfer_person_in()
1113 if l_error_count <> 0 then
1114 raise e_transfer_in_warning;
1115 end if;
1116
1117 p_errbuf := null;
1118 p_retcode := 0;
1119
1120 ghr_mto_int.log_message(
1121 p_procedure => l_proc_name,
1122 p_message =>
1123 'Mass Transfer In for '||
1124 l_transfer_name || ' transfered '||l_transfer_count ||
1125 ' employees.');
1126
1127 commit;
1128 hr_utility.set_location('Leaving:'||l_proc, 10);
1129
1130 exception
1131 when e_transfer_in_warning then
1132 l_message := 'Warning: Mass Transfer In for transfer: '||
1133 l_transfer_name ||' was unable to transfer '||
1134 l_error_count || ' people out of '|| l_transfer_count;
1135
1136 ghr_mto_int.log_message(
1137 p_procedure => l_proc_name,
1138 p_message => l_message);
1139
1140 p_errbuf := l_message;
1141 p_retcode := 1; -- signal warning condition to concurrent mgr
1142
1143 commit;
1144
1145 when others then
1146 ghr_mto_int.put_line(l_proc||' - raised exception');
1147
1148 if l_person_type_mti <> 'Employee' then
1149 ghr_mto_int.log_message(
1150 p_procedure => l_proc_name,
1151 p_message =>
1152 'Mass Transfer In for mass_transfer_id: '||
1153 p_transfer_id || ' failed with error message: '||
1154 sqlerrm(sqlcode));
1155 end if;
1156
1157 p_errbuf := sqlerrm(sqlcode);
1158 p_retcode := 2; -- signal error condition to concurrent mgr
1159
1160 commit;
1161
1162 end mass_transfer_in;
1163
1164 end ghr_mti_int;