[Home] [Help]
PACKAGE BODY: APPS.IRC_PARTY_PERSON_UTL
Source
1 package body irc_party_person_utl as
2 /* $Header: irptpeul.pkb 120.0 2005/07/26 15:16:00 mbocutt noship $ */
3 procedure update_party_records(p_mode varchar2) is
4
5 type t_number is table of number index by binary_integer;
6 l_party_ids t_number;
7 l_person_ids t_number;
8 l_old_row_count number;
9 l_new_row_count number;
10 l_rows_in_this_collect number;
11 l_business_group_id number;
12 l_person_type_id number;
13 l_ptu_person_type_id number;
14 l_new_person_id number;
15 l_object_version_number per_all_people_f.object_version_number%type;
16 l_effective_start_date per_all_people_f.effective_start_date%type;
17 l_effective_end_date per_all_people_f.effective_end_date%type;
18 l_full_name per_all_people_f.full_name%type;
19 l_comment_id per_all_people_f.comment_id%type;
20 l_name_combination_warning boolean;
21 l_orig_hire_warning boolean;
22 l_assign_payroll_warning boolean;
23 l_mode boolean;
24 l_employee_number varchar2(255);
25
26 cursor get_party_id1 is
27 select party_id,party_id person_id
28 from hz_parties
29 where orig_system_reference='PER:IRC'
30 union
31 select party_id,person_id
32 from irc_notification_preferences;
33
34 cursor get_party_id2 is
35 select party_id,party_id person_id
36 from hz_parties
37 where orig_system_reference='PER:IRC'
38 union
39 select party_id,person_id
40 from irc_notification_preferences
41 union
42 select party_id,person_id
43 from per_addresses
44 where party_id is not null
45 and person_id is null
46 union
47 select party_id,parent_id person_id
48 from per_phones
49 where party_id is not null
50 and parent_id is null
51 union
52 select party_id,person_id
53 from per_previous_employers
54 where party_id is not null
55 and person_id is null
56 union
57 select party_id,person_id
58 from per_qualifications
59 where party_id is not null
60 and person_id is null
61 union
62 select party_id,person_id
63 from per_establishment_attendances
64 where party_id is not null
65 and person_id is null
66 union
67 select party_id,person_id
68 from irc_documents
69 where party_id is not null
70 and person_id is null
71 union
72 select party_id,person_id
73 from per_competence_elements
74 where party_id is not null
75 and person_id is null
76 union
77 select party_id,person_id
78 from irc_job_basket_items
79 where party_id is not null
80 and person_id is null
81 union
82 select object_id party_id, object_id person_id
83 from irc_search_criteria
84 where object_type='WORK'
85 union
86 select party_id,person_id
87 from irc_vacancy_considerations
88 where party_id is not null
89 and person_id is null;
90
91 cursor get_party_rec(p_party_id number) is
92 select hzpp.person_first_name
93 ,hzpp.person_last_name
94 ,hzpp.date_of_birth
95 ,hzpp.person_title
96 ,hzpp.gender
97 ,hzpp.marital_status
98 ,hzpp.person_previous_last_name
99 ,hzpp.person_name_suffix
100 ,hzpp.person_middle_name
101 ,hzpp.known_as
102 ,hzpp.person_first_name_phonetic
103 ,hzpp.person_last_name_phonetic
104 ,hzp.creation_date
105 from hz_person_profiles hzpp
106 , hz_parties hzp
107 where hzpp.party_id=p_party_id
108 and hzp.party_id=hzpp.party_id
109 and sysdate between hzpp.effective_start_date and nvl(hzpp.effective_end_date,sysdate);
110
111 cursor get_email_rec(p_party_id number) is
112 select usr1.email_address,usr1.start_date
113 from fnd_user usr1
114 where usr1.customer_id=p_party_id
115 and usr1.start_date < sysdate
116 union
117 select usr1.email_address,usr1.start_date
118 from fnd_user usr1
119 ,per_all_people_f per1
120 where usr1.employee_id=per1.person_id
121 and usr1.start_date < sysdate
122 and per1.party_id=p_party_id
123 and trunc(sysdate) between per1.effective_start_date
124 and per1.effective_end_date
125 order by 2 desc;
126
127 l_email_address varchar2(240);
128 l_start_date_dummy date;
129
130 party_rec get_party_rec%rowtype;
131
132 cursor get_legislation_code is
133 select legislation_code
134 from per_business_groups
135 where business_group_id=l_business_group_id;
136
137 l_legislation_code varchar2(150);
138
139 cursor get_person_rec(p_party_id number) is
140 select person_id,effective_start_date
141 from per_all_people_f
142 where party_id=p_party_id
143 and business_group_id=l_business_group_id
144 order by effective_start_date asc;
145
146 cursor get_current_person_rec(p_person_id number) is
147 select email_address,object_version_number
148 from per_all_people_f
149 where person_id=p_person_id
150 and trunc(sysdate) between effective_start_date and effective_end_date;
151
152 cursor get_ptu_entry(p_person_id number) is
153 select 1
154 from per_person_type_usages_f ptu
155 , per_person_types ppt
156 where ptu.person_id=p_person_id
157 and ptu.person_type_id=ppt.person_type_id
158 and ppt.system_person_type='IRC_REG_USER';
159
160 cursor get_user_rec(p_party_id number) is
161 select user_name,employee_id
162 from fnd_user
163 where customer_id=p_party_id
164 and employee_id is null
165 and sysdate between start_date and nvl(end_date,sysdate);
166
167 l_data_migrator_mode varchar2(30);
168 l_limit number :=100;
169 l_b boolean;
170
171 cursor get_person_type_id is
172 select org_information8
173 from hr_organization_information
174 where organization_id=l_business_group_id
175 and ORG_INFORMATION_CONTEXT='BG Recruitment';
176
177 cursor get_work_prefs(p_person_id number) is
178 select 1 from irc_search_criteria
179 where object_id=p_person_id
180 and object_type='WPREF';
181
182 cursor get_notification_prefs(p_party_id number) is
183 select 1 from irc_notification_preferences
184 where party_id=p_party_id;
185
186 l_dummy number;
187 l_start_date date;
188
189
190 begin
191
192 if(p_mode='BASIC') then
193 l_mode:=true;
194 else
195 l_mode:=false;
196 end if;
197
198 l_business_group_id:=to_number(fnd_profile.value('IRC_REGISTRATION_BG_ID'));
199 if l_business_group_id is null then
200 fnd_message.set_name('PER','IRC_412155_REG_BG_NOT_SET');
201 fnd_message.raise_error;
202 end if;
203
204 open get_legislation_code;
205 fetch get_legislation_code into l_legislation_code;
206 close get_legislation_code;
207
208 open get_person_type_id;
209 fetch get_person_type_id into l_person_type_id;
210 if get_person_type_id%notfound then
211 close get_person_type_id;
212 fnd_message.set_name('PER','IRC_412156_PERS_TYPE_NOT_SET');
213 fnd_message.raise_error;
214 else
215 close get_person_type_id;
216 end if;
217 --
218 -- get the PTU person type for iRecruitment Candidate
219 --
220 l_ptu_person_type_id:=hr_person_type_usage_info.get_default_person_type_id
221 (l_business_group_id,
222 'IRC_REG_USER');
223 --
224 l_data_migrator_mode:=hr_general.g_data_migrator_mode;
225 hr_general.g_data_migrator_mode:='Y';
226 --
227 -- remap all employee data first
228 --
229 irc_global_remap_pkg.remap_employee(null,sysdate);
230 --
231 if(l_mode) then
232 open get_party_id1;
233 else
234 open get_party_id2;
235 end if;
236 loop
237
238 if(l_mode) then
239 fetch get_party_id1
240 bulk collect into
241 l_party_ids,l_person_ids
242 limit l_limit;
243 else
244 fetch get_party_id2
245 bulk collect into
246 l_party_ids,l_person_ids
247 limit l_limit;
248 end if;
249
250 l_old_row_count := l_new_row_count;
251 if(l_mode) then
252 l_new_row_count := get_party_id1%ROWCOUNT;
253 else
254 l_new_row_count := get_party_id2%ROWCOUNT;
255 end if;
256 l_rows_in_this_collect := l_new_row_count - l_old_row_count;
257
258 EXIT WHEN (l_rows_in_this_collect = 0);
259
260 for i in l_party_ids.first..l_party_ids.last loop
261 --
262 -- first of all look for an existing person_record
263 --
264 open get_person_rec(l_party_ids(i));
265 fetch get_person_rec into l_person_ids(i),l_start_date;
266 if get_person_rec%notfound then
267 close get_person_rec;
268 -- look for the e-mail address of the person
269 --
270 open get_email_rec(l_party_ids(i));
271 fetch get_email_rec into l_email_address,l_start_date_dummy;
272 close get_email_rec;
273 --
274 -- create a new person record in the default business group
275 --
276 open get_party_rec(l_party_ids(i));
277 fetch get_party_rec into party_rec;
278 close get_party_rec;
279 l_start_date:=trunc(party_rec.creation_date)-2;
280 if party_rec.gender='MALE' then
281 party_rec.gender:='M';
282 elsif party_rec.gender='FEMALE' then
283 party_rec.gender:='F';
284 else
285 party_rec.gender:=null;
286 end if;
287 if rtrim(party_rec.person_last_name) is null then
288 party_rec.person_last_name:=fnd_message.get_string('PER','IRC_412108_UNKNOWN_NAME');
289 end if;
290 if hr_api.not_exists_in_hr_lookups
291 (p_effective_date=>l_start_date
292 ,p_lookup_type=>'TITLE'
293 ,p_lookup_code=>party_rec.person_title) then
294 party_rec.person_title:=null;
295 end if;
296 if hr_api.not_exists_in_hr_lookups
297 (p_effective_date=>l_start_date
298 ,p_lookup_type=>'MAR_STATUS'
299 ,p_lookup_code=>party_rec.marital_status) then
300 party_rec.marital_status:=null;
301 end if;
302 if l_legislation_code='JP' then
303 hr_contact_api.create_person
304 (p_start_date => l_start_date
305 ,p_business_group_id => l_business_group_id
306 ,p_last_name => substrb(party_rec.person_last_name_phonetic,0,40)
307 ,p_first_name => substrb(party_rec.person_first_name_phonetic,0,20)
308 ,p_per_information_category => l_legislation_code
309 ,p_per_information18 => substrb(party_rec.person_last_name,0,150)
310 ,p_per_information19 => substrb(party_rec.person_first_name,0,150)
311 ,p_sex => party_rec.gender
312 ,p_title => party_rec.person_title
313 ,p_date_of_birth => party_rec.date_of_birth
314 ,p_known_as => substrb(party_rec.known_as,0,80)
315 ,p_previous_last_name => substrb(party_rec.person_previous_last_name,0,40)
316 ,p_marital_status => party_rec.marital_status
317 ,p_middle_names => substrb(party_rec.person_middle_name,0,60)
318 ,p_suffix => substrb(party_rec.person_name_suffix,0,30)
319 ,p_email_address => l_email_address
320 ,p_person_type_id => l_person_type_id
321 ,p_person_id => l_new_person_id
322 ,p_object_version_number => l_object_version_number
323 ,p_effective_start_date => l_effective_start_date
324 ,p_effective_end_date => l_effective_end_date
325 ,p_full_name => l_full_name
326 ,p_comment_id => l_comment_id
327 ,p_name_combination_warning => l_name_combination_warning
328 ,p_orig_hire_warning => l_orig_hire_warning
329 );
330 l_person_ids(i):=l_new_person_id;
331
332 elsif l_legislation_code='KR' then
333 hr_contact_api.create_person
334 (p_start_date => l_start_date
335 ,p_business_group_id => l_business_group_id
336 ,p_last_name => substrb(party_rec.person_last_name,0,40)
337 ,p_first_name => substrb(party_rec.person_first_name,0,20)
338 ,p_per_information_category => l_legislation_code
339 ,p_per_information1 => substrb(party_rec.person_last_name_phonetic,0,150)
340 ,p_per_information2 => substrb(party_rec.person_first_name_phonetic,0,150)
341 ,p_sex => party_rec.gender
342 ,p_title => party_rec.person_title
343 ,p_date_of_birth => party_rec.date_of_birth
344 ,p_known_as => substrb(party_rec.known_as,0,80)
345 ,p_previous_last_name => substrb(party_rec.person_previous_last_name,0,40)
346 ,p_marital_status => party_rec.marital_status
347 ,p_middle_names => substrb(party_rec.person_middle_name,0,60)
348 ,p_suffix => substrb(party_rec.person_name_suffix,0,30)
349 ,p_email_address => l_email_address
350 ,p_person_type_id => l_person_type_id
351 ,p_person_id => l_new_person_id
352 ,p_object_version_number => l_object_version_number
353 ,p_effective_start_date => l_effective_start_date
354 ,p_effective_end_date => l_effective_end_date
355 ,p_full_name => l_full_name
356 ,p_comment_id => l_comment_id
357 ,p_name_combination_warning => l_name_combination_warning
358 ,p_orig_hire_warning => l_orig_hire_warning
359 );
360 l_person_ids(i):=l_new_person_id;
361 else
362 hr_contact_api.create_person
363 (p_start_date => l_start_date
364 ,p_business_group_id => l_business_group_id
365 ,p_last_name => substrb(party_rec.person_last_name,0,40)
366 ,p_first_name => substrb(party_rec.person_first_name,0,20)
367 ,p_per_information_category => l_legislation_code
368 ,p_sex => party_rec.gender
369 ,p_title => party_rec.person_title
370 ,p_date_of_birth => party_rec.date_of_birth
371 ,p_known_as => substrb(party_rec.known_as,0,80)
372 ,p_previous_last_name => substrb(party_rec.person_previous_last_name,0,40)
373 ,p_marital_status => party_rec.marital_status
374 ,p_middle_names => substrb(party_rec.person_middle_name,0,60)
375 ,p_suffix => substrb(party_rec.person_name_suffix,0,30)
376 ,p_email_address => l_email_address
377 ,p_person_type_id => l_person_type_id
378 ,p_person_id => l_new_person_id
379 ,p_object_version_number => l_object_version_number
380 ,p_effective_start_date => l_effective_start_date
381 ,p_effective_end_date => l_effective_end_date
382 ,p_full_name => l_full_name
383 ,p_comment_id => l_comment_id
384 ,p_name_combination_warning => l_name_combination_warning
385 ,p_orig_hire_warning => l_orig_hire_warning
386 );
387 l_person_ids(i):=l_new_person_id;
388 end if;
389 --
390 --set the party_id on the record
391 --
392 l_employee_number:=null;
393 hr_person_api.update_person
394 (p_effective_date=>l_start_date
395 ,p_datetrack_update_mode=>'CORRECTION'
396 ,p_person_id=>l_person_ids(i)
397 ,p_object_version_number=>l_object_version_number
398 ,p_party_id =>l_party_ids(i)
399 ,p_employee_number=> l_employee_number
400 ,p_effective_start_date => l_effective_start_date
401 ,p_effective_end_date => l_effective_end_date
402 ,p_full_name => l_full_name
403 ,p_comment_id => l_comment_id
404 ,p_name_combination_warning => l_name_combination_warning
405 ,p_assign_payroll_warning => l_assign_payroll_warning
406 ,p_orig_hire_warning => l_orig_hire_warning);
407 --
408 -- create the extra PTU entry for iRecruitment Candidate
409 --
410 hr_per_type_usage_internal.maintain_person_type_usage
411 (p_effective_date => l_start_date
412 ,p_person_id => l_person_ids(i)
413 ,p_person_type_id => l_ptu_person_type_id
414 );
415 --
416 else
417 -- the person already exists, but may not have the correct PTU entry.
418 close get_person_rec;
419 open get_ptu_entry(l_person_ids(i));
420 fetch get_ptu_entry into l_dummy;
421 if get_ptu_entry%notfound then
422 close get_ptu_entry;
423 hr_per_type_usage_internal.maintain_person_type_usage
424 (p_effective_date => l_start_date
425 ,p_person_id => l_person_ids(i)
426 ,p_person_type_id => l_ptu_person_type_id
427 );
428 else
429 close get_ptu_entry;
430 end if;
431 -- update the e-mail address if it is not set
432 open get_current_person_rec(l_person_ids(i));
433 fetch get_current_person_rec into l_email_address,l_object_version_number;
434 if get_current_person_rec%found and l_email_address is null then
435 close get_current_person_rec;
436 l_employee_number:=hr_api.g_varchar2;
437 open get_email_rec(l_party_ids(i));
438 fetch get_email_rec into l_email_address,l_start_date_dummy;
439 close get_email_rec;
440 hr_person_api.update_person
441 (p_effective_date=>sysdate
442 ,p_datetrack_update_mode=>'CORRECTION'
443 ,p_person_id=>l_person_ids(i)
444 ,p_object_version_number=>l_object_version_number
445 ,p_employee_number=> l_employee_number
446 ,p_email_address=>l_email_address
447 ,p_effective_start_date => l_effective_start_date
448 ,p_effective_end_date => l_effective_end_date
449 ,p_full_name => l_full_name
450 ,p_comment_id => l_comment_id
451 ,p_name_combination_warning => l_name_combination_warning
452 ,p_assign_payroll_warning => l_assign_payroll_warning
453 ,p_orig_hire_warning => l_orig_hire_warning);
454 else
455 close get_current_person_rec;
456 end if;
457 end if;
458 --
459 -- get any user records
460 --
461 for usr_rec in get_user_rec(l_party_ids(i)) loop
462 fnd_user_pkg.updateUser
463 (x_user_name=>usr_rec.user_name
464 ,x_owner=>'CUST'
465 ,x_employee_id=>l_person_ids(i)
466 ,x_customer_id=>fnd_user_pkg.null_number);
467 end loop;
468 --
469 -- update the phone records directly for performance
470 --
471
472 update per_phones
473 set parent_id=l_person_ids(i)
474 ,parent_table='PER_ALL_PEOPLE_F'
475 where party_id=l_party_ids(i)
476 and parent_id is null;
477
478 --
479 -- update the address records directly for performance
480 --
481
482 update per_addresses
483 set person_id=l_person_ids(i)
484 ,business_group_id=l_business_group_id
485 where party_id=l_party_ids(i)
486 and person_id is null
487 and address_type='REC';
488 --
489 -- update the previous employers records directly for performance
490 --
491
492 update per_previous_employers
493 set person_id=l_person_ids(i)
494 ,business_group_id=l_business_group_id
495 where party_id=l_party_ids(i)
496 and person_id is null;
497 --
498 -- update the qualifications records directly for performance
499 --
500
501 update per_qualifications
502 set person_id=l_person_ids(i)
503 ,business_group_id=l_business_group_id
504 where party_id=l_party_ids(i)
505 and person_id is null;
506
507 --
508 -- update the establishment attendance records directly for performance
509 --
510
511 update per_establishment_attendances
512 set person_id=l_person_ids(i)
513 ,business_group_id=l_business_group_id
514 where party_id=l_party_ids(i)
515 and person_id is null;
516 --
517 -- update the competence records directly for performance
518 --
519
520 update per_competence_elements
521 set person_id=l_person_ids(i)
522 ,business_group_id=l_business_group_id
523 where party_id=l_party_ids(i)
524 and person_id is null;
525
526 --
527 -- update the documents records directly for performance
528 --
529
530 update irc_documents
531 set person_id=l_person_ids(i)
532 where party_id=l_party_ids(i)
533 and person_id is null;
534
535 --
536 -- update the job basket records directly for performance
537 --
538
539 update irc_job_basket_items
540 set person_id=l_person_ids(i)
541 where party_id=l_party_ids(i)
542 and person_id is null;
543
544 --
545 -- update the search criteria and work preferences
546 --
547 update irc_search_criteria
548 set object_id=l_person_ids(i)
549 ,object_type='PERSON'
550 where object_type = 'PARTY'
551 and object_id=l_party_ids(i);
552 --
553 -- update the search criteria and work preferences
554 --
555 update irc_search_criteria
556 set object_id=l_person_ids(i)
557 ,object_type='WPREF'
558 where object_type = 'WORK'
559 and object_id=l_party_ids(i);
560 --
561 -- check that the user has work preferences
562 --
563 open get_work_prefs(l_person_ids(i));
564 fetch get_work_prefs into l_dummy;
565 if get_work_prefs%found then
566 close get_work_prefs;
567 else
568 close get_work_prefs;
569 -- no work prefernces, so create some
570 insert into irc_search_criteria
571 (search_criteria_id
572 ,object_id
573 ,object_type
574 ,employee
575 ,contractor
576 ,employment_category
577 ,match_competence
578 ,match_qualification
579 ,salary_period
580 ,last_update_date
581 ,last_updated_by
582 ,last_update_login
583 ,created_by
584 ,creation_date
585 ,object_version_number)
586 values
587 (irc_search_criteria_s.nextval
588 ,l_person_ids(i)
589 ,'WPREF'
590 ,'Y'
591 ,'Y'
592 ,'FULLTIME'
593 ,'Y'
594 ,'Y'
595 ,'ANNUAL'
596 ,sysdate
597 ,1
598 ,1
599 ,1
600 ,sysdate
601 ,1);
602 end if;
603 --
604 -- update the vacancy consideration records
605 --
606
607 update irc_vacancy_considerations
608 set person_id=l_person_ids(i)
609 where party_id=l_party_ids(i)
610 and person_id is null;
611
612 --
613 -- update the notification preferences
614 --
615 open get_notification_prefs(l_party_ids(i));
616 fetch get_notification_prefs into l_dummy;
617 if get_notification_prefs%found then
618 close get_notification_prefs;
619 update irc_notification_preferences inp
620 set person_id=l_person_ids(i)
621 where party_id=l_party_ids(i)
622 and person_id is null;
623 else
624 close get_notification_prefs;
625 insert into irc_notification_preferences
626 (notification_preference_id
627 ,party_id
628 ,person_id
629 ,matching_jobs
630 ,matching_job_freq
631 ,receive_info_mail
632 ,allow_access
633 ,last_update_date
634 ,last_updated_by
635 ,last_update_login
636 ,created_by
637 ,creation_date
638 ,object_version_number)
639 values
640 (irc_notification_prefs_s.nextval
641 ,l_party_ids(i)
642 ,l_person_ids(i)
643 ,'N'
644 ,'1'
645 ,'N'
646 ,'N'
647 ,sysdate
648 ,1
649 ,1
650 ,1
651 ,sysdate
652 ,1);
653 end if;
654 end loop;
655
656 commit;
657 l_person_ids.delete;
658 l_party_ids.delete;
659
660 end loop;
661 if(l_mode) then
662 close get_party_id1;
663 else
664 close get_party_id2;
665 end if;
666 hr_general.g_data_migrator_mode:=l_data_migrator_mode;
667
668 end update_party_records;
669
670 procedure update_party_conc(errbuf out nocopy varchar2
671 ,retcode out nocopy varchar2) is
672 --
673 l_proc varchar2(72) := 'irc_party_person_utl.update_party_conc';
674 --
675 begin
676 hr_utility.set_location('Entering: '||l_proc, 10);
677 irc_party_person_utl.update_party_records;
678 commit;
679 retcode := 0;
680 hr_utility.set_location('Leaving: '||l_proc, 20);
681 exception
682 when others then
683 rollback;
684 --
685 -- Set the return parameters to indicate failure
686 --
687 errbuf := sqlerrm;
688 retcode := 2;
689 end update_party_conc;
690 end irc_party_person_utl;