[Home] [Help]
PACKAGE BODY: APPS.PER_CONTACT_RELATIONSHIPS_PKG
Source
1 PACKAGE BODY PER_CONTACT_RELATIONSHIPS_PKG AS
2 /* $Header: pecon01t.pkb 120.8.12010000.1 2008/07/28 04:24:03 appldev ship $ */
3 /*---------------------------------------------------------------------------
4 --
5 Change List
6 -----------
7 --
8 Name Date Versn Bug Text
9 --------------+-----------+-------+-------+-------------------------------------------
10 JRhodes 09-May-94 80.0 Created Initial Version
11 JRhodes 25-Jan-95 70.4 Added ATTRIBUTE21-30 for people
12 Removed AOL WHO Columns
13 JRhodes 05-Apr-95 70.5 Performance fix (273826)
14 TMathers 05-Jul-95 70.6 Added BENEFICIARY_FLAG,BONDHOLDER_FLAG and
15 THIRD_PARTY_PAY_FLAG + NATIONAL_IDENTIFIER.
16 SSDesai 05-Aug-95 70.8 Added Dependent and Beneficiary to
17 Delete_Validation.
18 JAlloun 08-OCT-96 70.10 366749 Added contact_only foreign key ch was picking up minimum
19 effective_start_date from per_people_f to set
20 the contacts effective_start_date !!!. Fixed the
21 problem by setting the constacts
22 effective_start_date to be the session date.
23 13-FEB-97 FSHOJAAS 70.12 436371 Tow parameter were added to insert and update
24 logic (X_suffix and X_PRE_NAME_ADJUNCT).
25 04-MAR-97 JAlloun 70.13 Changed all occurances of system.dual to
26 sys.dual for next release requirements.
27 18-JUL-97 RKAMIYAM 70.14 Added per_information1 to 30 and know_as cols
28 11-Sep-97 IHARDING 110.1 505202 Insert value 'N' for all relationship flags
29 into PER_CONTACT_RELATIONSHIPS for the
30 mirror contact.
31 04-DEC-97 IHARDING 110.2 568596 Set p_comments to null when creating
32 a mirror contact relationship row.
33 05-MAY-98 CCARTER 115.1 Changes to the following procedures resulting
34 from OAB changes: INSERT_ROW, LOCK_ROW,
35 UPDATE_ROW. Dependent_Flag and
36 Beneficiary_Flag left in, in case OAB
37 is pulled from 11.5.
38 15-DEC-98 CCARTER 115.3 Sequence_number parameter and validation
39 procedure added and called from
40 Insert_Contact and Update_Row
41 procedures for OAB.
42 16-FEB-99 ASahay 115.4 820655 Added and condition in delete_row
43 to_check for multiple contacts
44 25-OCT-99 JzyLi 115.6 963097 Replace get_full_name with
45 hr_person.derive_full_name
46 22-FEB-00 ASahay 115.7 1152185 Replace appid to 800 from 801 for
47 message PER_6996_REL_CURR_EXISTS
48 08-MAR-00 ASahay 115.8 1160369 Added procedure Update_Contact
49
50 08-MAR-00 pzwalker 115.9 1239046 Added call to time_validation from
51 Update_Contact
52 12-MAY-00 ASahay 115.10 1295442 Added and condition in delete_validation
53 to_check for multiple contacts
54 19-OCT-00 GPerry 115.11 1408379 Fixed WWBUG 1408379
55 Added hook call to OAB so life event
56 triggers work.
57 13-Mar-01 KSivagur 115.12 added parameter per_information_category.
58 20-Jun-01 GPerry 115.13 1833930 Fixed WWBUG 1833930.
59 Changed Check_For_Duplicate_Person
60 so that it uses exists and also
61 so that it uses the BG index.
62 21-Aug-01 adhunter 115.14 PTU changes to inserting person records
63 22-Aug-01 adhunter 115.15 added PTU changes to Update_Contact
64 30-Sep-01 wstallar 115.16 added party_id support
65 03-Oct-01 wstallar 115.17 added party_id support on update
66 10-Oct-01 adhunter 115.19 1766066 added call points to maintain_coverage
67 and DDF cols to all procedures.
68 Re-did delete routine.
69 19-Oct-01 wstallar 115.21 add support for TCA-mapped columns
70 24-Oct-01 adhunter 115.23 1931258 added ben_person_delete calls in delete_row
71 15-Nov-01 adhunter 115.24 2073795 added validation for X_Contact_Person_Id.
72 16-Nov-01 adhunter 115.25 added dbdrv line
73 14-Feb-02 hnarayan 115.27 1772037 added code to pass dff attributes of per_contact_relationships
74 table to ben_con_ler.ler_chk procedure
75 Makiko Iwamoto 2002/03/05 115.28 2225930 Added procedure chk_dates to validate
76 if new date_start is earlier than the
77 effective_start_date of child contact extra
78 information records and if new date_end is
79 later than the effective_end_date of child
80 contact extra information records before
81 update.
82 Modified procedure update_contact to handle
83 date_of_death.
84 Modified procedure delete_row to delete
85 child records in per_contact_extra_info_f.
86 26-Jul-02 irgonzal 115.29 2483186 added hr_security_internal.populate_new_person
87 call to ensure security list maintenance
88 gets properly updated.
89 06-Sep-02 vramanai 115.30 2533935 modified the cursor defination of check_person_changed
90 in update_contact procedure, to get the value of
91 person_type_id from per_person_type_usages
92 16-Sep-02 vramanai 115.31 2533935 moddified the earlier fix as there was some
93 problem with the fix.
94 05-Dec-02 pkakar 115.32 added nocopy to parameters
95 10-Dec-02 mbocutt 115.33 2690302 Added code to INSERT_ROW routine
96 to improve performance of duplicate
97 person checking(cursor
98 Check_For_Duplicate_Person)
99 by allowing CBO to pick the index on
100 LAST_NAME column.
101 07-Jan-03 vramanai 115.34 2618454 Added a hint in cursor Check_Person_Changed for
102 better performance.
103 04-Apr-04 TPAPIRED 115.35 2881631 TCA Unmerge changes
104 commented calls to create_tca_person
105 now this proc is called in PTUmaintain
106 24-Oct-03 ttagawa 115.36 3207660 chk_dates call commented out.
107 19-DEC-03 smparame 115.37 3326964 New procedure chk_date_of_birth added to
108 check whether date of birth is less than
109 or equal to to relationship start date.
110 Procedure update_contact modified. If
111 condition to check whether the date of
112 birth is less than relationship start
113 date is added.
114 15-APR-04 smparame 115.38 3546390 Call to chk_date_of_birth is removed.
115 This validation is moved to front end.
116 21-JUN-04 adudekul 115.39 3648765 Performance issues. In proc update_row
117 modified check_mirror_update.
118 19-jan-05 irgonzal 115.40 3889584 Added call to new routine to derive
119 person names.
120 21-FEB-05 smparame 115.41 4197342 Procedure delete_row modified to check whether
121 the person Irec candidate before deleting the
122 person record.
123 08-APR-05 pchowdav 115.42 4281500 update_contact modified to update the
124 relationship records date start when the
125 contact date of birth is updated with a later
126 date.
127 12-APR-05 abparekh 115.43 4295302 Called procedure ben_ppf_ler from Insert_Row
128 and Update_Contact to trigger life event
129 reasons for insert/update of PER_ALL_PEOPLE_F
130 records for a Contact
131 13-APR-05 abparekh 115.44 4295302 Fixed GSCC Error
132 15-JUN-05 bshukla 115.45 4432200 Fixed GSCC Error
133 06-OCT-05 irgonzal 115.46 Fixed GSCC error: GSCC Standard - File.Pkg.9
134 14-DEC-05 pchowdav 115.47 4867048 Modified cursors Check_Mirror_Update and
135 Check_Mirror to use nvl statement.
136 16-MAR-06 pchowdav 115.48 4763755 Modified procedure Update_Contact .
137 27-SEP-06 asgugupt 115.49 5415267 Modified procedure Delete_Validation .
138 27-NOV-06 risgupta 115.50 3988762 commented the call to cursor Check_For_Duplicate_Person
139 for duplicate check enhancement. This duplicate check is
140 redundent and will be taken care on contact form.
141 02-may-2007 pdkundu 115.51 6026636 Modified the cursor csr_seq_others.
142 ======================================================================================*/
143 -----------------------------------------------
144 -- Local Procedure to return Mirror Contact Type
145 -----------------------------------------------
146 FUNCTION Get_Mirror_Contact_Type(p_contact_type VARCHAR2) RETURN VARCHAR2 IS
147 l_Contact_Type VARCHAR2(1);
148 BEGIN
149 IF p_Contact_Type = 'C' THEN
150 l_Contact_Type := 'P';
151 ELSIF p_Contact_Type = 'P' THEN
152 l_Contact_Type := 'C';
153 ELSIF p_Contact_Type IN ('S','F') THEN
154 l_Contact_Type := p_Contact_Type;
155 ELSE
156 l_Contact_Type := 'M';
157 END IF;
158 RETURN(l_Contact_Type);
159 END;
160 --
161 FUNCTION check_primary_contact(p_rowid VARCHAR2
162 ,p_person_id NUMBER
163 ,p_date_start DATE
164 ,p_date_end DATE
165 ) RETURN BOOLEAN IS
166 CURSOR c IS
167 SELECT 'Y'
168 FROM per_contact_relationships
169 WHERE person_id = p_person_id
170 AND primary_contact_flag = 'Y'
171 AND NVL(date_start, Hr_General.start_of_time) <=
172 NVL(p_date_end,Hr_General.end_of_time)
173 AND NVL(date_end, Hr_General.end_of_time) >=
174 NVL(p_date_start,Hr_General.start_of_time)
175 AND ((ROWID <> CHARTOROWID(p_rowid)
176 AND p_rowid IS NOT NULL)
177 OR (p_rowid IS NULL)
178 );
179 --
180 l_exists VARCHAR2(1) := 'N';
181 --
182 BEGIN
183 OPEN c;
184 FETCH c INTO l_exists;
185 CLOSE c;
186 --
187 RETURN(l_exists = 'Y');
188 END check_primary_contact;
189 ---------------------------------------------------------------------
190 -- Local procedure time validation
191 -- Check to ensure that only one relationship of the same type exists
192 -- between the same two people at the same time
193 ---------------------------------------------------------------------
194 FUNCTION time_validation (p_contact_type VARCHAR2,
195 p_person_id NUMBER,
196 p_contact_person_id NUMBER,
197 p_contact_relationship_id NUMBER,
198 p_date_start DATE,
199 p_date_end DATE ) RETURN BOOLEAN IS
200 l_records VARCHAR2(1);
201 l_start_of_time DATE := Hr_General.start_of_time;
202 l_end_of_time DATE := Hr_General.end_of_time;
203 CURSOR c IS
204 SELECT 'X'
205 FROM per_contact_relationships per
206 WHERE per.person_id = p_person_id
207 AND per.contact_person_id = p_contact_person_id
208 AND (per.contact_relationship_id <> p_contact_relationship_id
209 OR p_contact_relationship_id IS NULL)
210 AND per.contact_type = p_contact_type
211 AND NVL(p_date_start,l_start_of_time) <= NVL(date_end,l_end_of_time)
212 AND NVL(p_date_end,l_end_of_time) >= NVL(date_start,l_start_of_time);
213 --
214 BEGIN
215 OPEN c;
216 FETCH c INTO l_records;
217 CLOSE c;
218
219 RETURN (l_records = 'X');
220 END time_validation;
221 ---------------------------------------------------------------------
222 -- ---------------------------------------------------------------------------
223 -- |-------------------------------< chk_dates >-----------------------------|
224 -- ---------------------------------------------------------------------------
228 -- Accepts contact_relationship_id, date_start, and date_end, and check if
225 -- {Start Of Comments}
226 --
227 -- Description:
229 -- neither child record with effective_start_date earlier than the given
230 -- date_start exists nor child records with effective_end_date later than
231 -- the given date_end in per_contact_extra_info_f.
232 --
233 -- Prerequisites:
234 -- None.
235 --
236 -- In Parameters:
237 -- Name Reqd Type Description
238 -- x_contact_relationship_id Yes NUMBER Contact Relationship ID.
239 -- x_date_start No DATE Start Date of the
240 -- relationship.
241 -- x_date_end No DATE End Date of the
242 -- relationship.
243 --
244 -- Out Parameters:
245 -- None.
246 --
247 -- Post Success:
248 -- The process succeeds.
249 --
250 -- Post Failure:
251 -- The process will be terminated.
252 --
253 -- Developer Implementation Notes:
254 -- None.
255 --
256 -- Access Status:
257 -- Public.
258 --
259 -- {End Of Comments}
260 -- ---------------------------------------------------------------------------
261 PROCEDURE chk_dates(
262 x_contact_relationship_id IN per_contact_relationships.contact_relationship_id%TYPE,
263 x_date_start IN per_contact_relationships.date_start%TYPE,
264 x_date_end IN per_contact_relationships.date_end%TYPE) IS
265 --
266 CURSOR cel_earlier_child_exists IS
267 SELECT 1
268 FROM per_contact_extra_info_f
269 WHERE contact_relationship_id = x_contact_relationship_id
270 AND effective_start_date < x_date_start;
271 --
272 CURSOR cel_later_child_exists IS
273 SELECT 1
274 FROM per_contact_extra_info_f
275 WHERE contact_relationship_id = x_contact_relationship_id
276 AND effective_end_date > x_date_end;
277 --
278 l_dummy VARCHAR2(1);
279 BEGIN
280
281 -- = Start date validation is executed only when x_date_start is not null.
282 IF x_date_start IS NOT NULL THEN
283 OPEN cel_earlier_child_exists;
284 FETCH cel_earlier_child_exists INTO l_dummy;
285
286 -- == Raise error if child record with earlier effective_start_date than the
287 -- == given date_start exists.
288 IF cel_earlier_child_exists%FOUND THEN
289 CLOSE cel_earlier_child_exists;
290 --
291 hr_utility.set_message(
292 applid => 800,
293 l_message_name => 'PER_6549_INVALD_REL_START_DATE');
294 --
295 hr_utility.raise_error;
296 END IF;
297 -- ==
298
299 CLOSE cel_earlier_child_exists;
300 END IF;
301 -- =
302
303 -- = End date validation is executed only when x_date_end is not null.
304 IF x_date_end IS NOT NULL THEN
305 OPEN cel_later_child_exists;
306 FETCH cel_later_child_exists INTO l_dummy;
307
308 -- == Raise error if child record with later effective_end_date than the
309 -- == given date_end exists.
310 IF cel_later_child_exists%FOUND THEN
311 CLOSE cel_later_child_exists;
312 --
313 hr_utility.set_message(
314 applid => 800,
315 l_message_name => 'PER_50044_INVALID_REL_END_DATE');
316 --
317 hr_utility.raise_error;
318 END IF;
319 -- ==
320
321 CLOSE cel_later_child_exists;
322 END IF;
323 -- =
324
325 END chk_dates;
326 --
327 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
328 X_Party_Id NUMBER ,
329 X_Contact_Relationship_Id IN OUT NOCOPY NUMBER,
330 X_Business_Group_Id NUMBER,
331 X_Person_Id IN OUT NOCOPY NUMBER,
332 X_Contact_Person_Id IN OUT NOCOPY NUMBER,
333 X_Contact_Type IN OUT NOCOPY VARCHAR2,
334 X_Comments VARCHAR2,
335 X_Bondholder_Flag VARCHAR2,
336 X_Third_Party_Pay_Flag VARCHAR2,
337 X_Primary_Contact_Flag VARCHAR2,
338 X_Cont_Attribute_Category VARCHAR2,
339 X_Cont_Attribute1 VARCHAR2,
340 X_Cont_Attribute2 VARCHAR2,
341 X_Cont_Attribute3 VARCHAR2,
345 X_Cont_Attribute7 VARCHAR2,
342 X_Cont_Attribute4 VARCHAR2,
343 X_Cont_Attribute5 VARCHAR2,
344 X_Cont_Attribute6 VARCHAR2,
346 X_Cont_Attribute8 VARCHAR2,
347 X_Cont_Attribute9 VARCHAR2,
348 X_Cont_Attribute10 VARCHAR2,
349 X_Cont_Attribute11 VARCHAR2,
350 X_Cont_Attribute12 VARCHAR2,
351 X_Cont_Attribute13 VARCHAR2,
352 X_Cont_Attribute14 VARCHAR2,
353 X_Cont_Attribute15 VARCHAR2,
354 X_Cont_Attribute16 VARCHAR2,
355 X_Cont_Attribute17 VARCHAR2,
356 X_Cont_Attribute18 VARCHAR2,
357 X_Cont_Attribute19 VARCHAR2,
358 X_Cont_Attribute20 VARCHAR2,
359 X_Cont_Information_Category VARCHAR2,
360 X_Cont_Information1 VARCHAR2,
361 X_Cont_Information2 VARCHAR2,
362 X_Cont_Information3 VARCHAR2,
363 X_Cont_Information4 VARCHAR2,
364 X_Cont_Information5 VARCHAR2,
365 X_Cont_Information6 VARCHAR2,
366 X_Cont_Information7 VARCHAR2,
367 X_Cont_Information8 VARCHAR2,
368 X_Cont_Information9 VARCHAR2,
369 X_Cont_Information10 VARCHAR2,
370 X_Cont_Information11 VARCHAR2,
371 X_Cont_Information12 VARCHAR2,
372 X_Cont_Information13 VARCHAR2,
373 X_Cont_Information14 VARCHAR2,
374 X_Cont_Information15 VARCHAR2,
375 X_Cont_Information16 VARCHAR2,
376 X_Cont_Information17 VARCHAR2,
377 X_Cont_Information18 VARCHAR2,
378 X_Cont_Information19 VARCHAR2,
379 X_Cont_Information20 VARCHAR2,
380 X_Session_Date DATE,
381 X_Person_Type_Id NUMBER,
382 X_Last_Name VARCHAR2,
383 X_Comment_Id NUMBER,
384 X_Date_Of_Birth DATE,
385 X_First_Name VARCHAR2,
386 X_Middle_Names VARCHAR2,
387 X_Sex VARCHAR2,
388 X_Title VARCHAR2,
389 X_PRE_NAME_ADJUNCT VARCHAR2,
390 X_SUFFIX VARCHAR2,
391 X_Title_Desc VARCHAR2,
392 X_national_identifier VARCHAR2,
393 X_Attribute_Category VARCHAR2,
394 X_Attribute1 VARCHAR2,
395 X_Attribute2 VARCHAR2,
396 X_Attribute3 VARCHAR2,
397 X_Attribute4 VARCHAR2,
398 X_Attribute5 VARCHAR2,
399 X_Attribute6 VARCHAR2,
400 X_Attribute7 VARCHAR2,
401 X_Attribute8 VARCHAR2,
402 X_Attribute9 VARCHAR2,
403 X_Attribute10 VARCHAR2,
404 X_Attribute11 VARCHAR2,
405 X_Attribute12 VARCHAR2,
406 X_Attribute13 VARCHAR2,
407 X_Attribute14 VARCHAR2,
408 X_Attribute15 VARCHAR2,
409 X_Attribute16 VARCHAR2,
410 X_Attribute17 VARCHAR2,
411 X_Attribute18 VARCHAR2,
412 X_Attribute19 VARCHAR2,
413 X_Attribute20 VARCHAR2,
414 X_Attribute21 VARCHAR2,
415 X_Attribute22 VARCHAR2,
416 X_Attribute23 VARCHAR2,
417 X_Attribute24 VARCHAR2,
418 X_Attribute25 VARCHAR2,
419 X_Attribute26 VARCHAR2,
420 X_Attribute27 VARCHAR2,
424 X_Reentry_Flag IN OUT NOCOPY NUMBER,
421 X_Attribute28 VARCHAR2,
422 X_Attribute29 VARCHAR2,
423 X_Attribute30 VARCHAR2,
425 X_Per_Information_Category VARCHAR2,
426 X_Per_Information1 VARCHAR2,
427 X_Per_Information2 VARCHAR2,
428 X_Per_Information3 VARCHAR2,
429 X_Per_Information4 VARCHAR2,
430 X_Per_Information5 VARCHAR2,
431 X_Per_Information6 VARCHAR2,
432 X_Per_Information7 VARCHAR2,
433 X_Per_Information8 VARCHAR2,
434 X_Per_Information9 VARCHAR2,
435 X_Per_Information10 VARCHAR2,
436 X_Per_Information11 VARCHAR2,
437 X_Per_Information12 VARCHAR2,
438 X_Per_Information13 VARCHAR2,
439 X_Per_Information14 VARCHAR2,
440 X_Per_Information15 VARCHAR2,
441 X_Per_Information16 VARCHAR2,
442 X_Per_Information17 VARCHAR2,
443 X_Per_Information18 VARCHAR2,
444 X_Per_Information19 VARCHAR2,
445 X_Per_Information20 VARCHAR2,
446 X_Per_Information21 VARCHAR2,
447 X_Per_Information22 VARCHAR2,
448 X_Per_Information23 VARCHAR2,
449 X_Per_Information24 VARCHAR2,
450 X_Per_Information25 VARCHAR2,
451 X_Per_Information26 VARCHAR2,
452 X_Per_Information27 VARCHAR2,
453 X_Per_Information28 VARCHAR2,
454 X_Per_Information29 VARCHAR2,
455 X_Per_Information30 VARCHAR2,
456 X_Known_As VARCHAR2,
457 X_Date_Start DATE,
458 X_Start_Life_Reason_Id VARCHAR2,
459 X_Date_End DATE,
460 X_End_Life_Reason_Id VARCHAR2,
461 X_Rltd_Per_Rsds_W_Dsgntr_Flag VARCHAR2,
462 X_Personal_Flag VARCHAR2,
463 X_Sequence_Number NUMBER,
464 X_Create_Mirror_Flag VARCHAR2,
465 X_Mirror_Type VARCHAR2,
466 X_Dependent_Flag VARCHAR2,
467 X_Beneficiary_Flag VARCHAR2,
468 X_marital_status VARCHAR2 ,
469 X_nationality VARCHAR2 ,
470 X_blood_type VARCHAR2 ,
471 X_correspondence_language VARCHAR2 ,
472 X_honors VARCHAR2 ,
473 X_rehire_authorizor VARCHAR2 ,
474 X_rehire_recommendation VARCHAR2 ,
475 X_resume_exists VARCHAR2 ,
476 X_resume_last_updated DATE ,
477 X_second_passport_exists VARCHAR2 ,
478 X_student_status VARCHAR2 ,
479 X_date_of_death DATE ,
480 X_uses_tobacco_flag VARCHAR2 ,
481 X_town_of_birth VARCHAR2 ,
482 X_region_of_birth VARCHAR2 ,
483 X_country_of_birth VARCHAR2 ,
484 X_fast_path_employee VARCHAR2 ,
485 X_email_address VARCHAR2 ,
486 X_fte_capacity VARCHAR2 ) IS
487
488 --
489 l_contact_type VARCHAR2(30);
490 l_person_id NUMBER;
491 l_contact_person_id NUMBER;
492 l_duplicate_exists VARCHAR2(1) := 'N';
493 l_object_version_number NUMBER;
494 --Local var flags for bug 505202 IJH.
495 l_Third_Party_Pay_Flag VARCHAR2(1) := 'N';
496 l_Primary_Contact_Flag VARCHAR2(1) := 'N';
497 l_rltd_per_rsds_w_dsgntr_flag VARCHAR2(1) := 'N';
498 l_personal_flag VARCHAR2(1) := 'N';
499 l_dependent_flag VARCHAR2(1) := 'N';
500 l_beneficiary_flag VARCHAR2(1) := 'N';
501 l_sequence_number NUMBER;
502 --
503 l_first_char VARCHAR2(5) := substr( x_last_name , 1 , 1 ) ;
504 l_second_char VARCHAR2(5) := substr( x_last_name , 2 , 1 ) ;
505 l_ul_check VARCHAR2(15) := upper(l_first_char)||lower(l_second_char)||'%';
506 l_lu_check VARCHAR2(15) := lower(l_first_char)||upper(l_second_char)||'%';
507 l_uu_check VARCHAR2(15) := upper(l_first_char)||upper(l_second_char)||'%';
508 l_ll_check VARCHAR2(15) := lower(l_first_char)||lower(l_second_char)||'%';
509
510 --
511 -- WWBUG 1833930.
515 CURSOR Check_For_Duplicate_Person IS
512 -- Changed cursor to use the business group index and to use an exists
513 -- statement rather than the full table scan.
514 --
516 SELECT 'Y'
517 FROM sys.dual
518 WHERE EXISTS (SELECT NULL
519 FROM per_all_people_f
520 WHERE UPPER(last_name) = UPPER(X_Last_Name)
521 AND ( last_name like l_ul_check
522 or last_name like l_lu_check
523 or last_name like l_uu_check
524 or last_name like l_ll_check)
525 AND (UPPER(first_name) = UPPER(X_first_name)
526 OR X_first_name IS NULL
527 OR first_name IS NULL)
528 AND (date_of_birth = X_date_of_birth
529 OR X_date_of_birth IS NULL
530 OR date_of_birth IS NULL)
531 AND business_group_id = X_business_group_id);
532 -- fix for bug 2073795
533 --
534 CURSOR csr_per_exists is
535 select 'Y'
536 from dual
537 where exists
538 (select 'Y'
539 from per_all_people_f
540 where person_id = X_Contact_Person_Id);
541 --
542 l_dummy varchar2(10);
543 --
544 ------------------------------------------------------------
545 -- Local procedure to insert individual contact row
546 --
547 PROCEDURE Insert_Contact IS
548 CURSOR C IS
549 SELECT ROWID FROM PER_CONTACT_RELATIONSHIPS
550 WHERE contact_relationship_id = X_Contact_Relationship_Id;
551 --
552 CURSOR C2 IS
553 SELECT per_contact_relationships_s.NEXTVAL FROM sys.dual;
554 --
555 -- Start of Fix for WWBUG 1408379
556 --
557 l_old ben_con_ler.g_con_ler_rec;
558 l_new ben_con_ler.g_con_ler_rec;
559 --
560 -- End of Fix for WWBUG 1408379
561 --
562 BEGIN
563 IF (X_Contact_Relationship_Id IS NULL) THEN
564 OPEN C2;
565 FETCH C2 INTO X_Contact_Relationship_Id;
566 CLOSE C2;
567 END IF;
568 IF X_contact_relationship_id IS NOT NULL THEN
569 NULL;
570 END IF;
571 --
572 -- Call to sequence number validation
573 --
574 chk_sequence_number(p_contact_relationship_id => X_contact_relationship_id
575 ,p_sequence_number => X_sequence_number
576 ,p_contact_person_id => X_contact_person_id
577 ,p_person_id => X_person_id
578 );
579 --
580 -- Check to ensure that only one relationship of the same type exists
581 -- between the same two people at the same time
582 --
583 IF time_validation(p_contact_type => X_contact_type
584 ,p_person_id => X_person_id
585 ,p_contact_person_id => X_contact_person_id
586 ,p_contact_relationship_id => X_contact_relationship_id
587 ,p_date_start => X_date_start
588 ,p_date_end => X_date_end
589 ) THEN
590 hr_utility.set_message(800,'PER_6996_REL_CURR_EXISTS');
591 hr_utility.raise_error;
592 END IF;
593 --
594 INSERT INTO PER_CONTACT_RELATIONSHIPS(
595 contact_relationship_id,
596 business_group_id,
597 person_id,
598 contact_person_id,
599 contact_type,
600 comments,
601 Bondholder_Flag,
602 Third_Party_Pay_Flag,
603 primary_contact_flag,
604 cont_attribute_category,
605 cont_attribute1,
606 cont_attribute2,
607 cont_attribute3,
608 cont_attribute4,
609 cont_attribute5,
610 cont_attribute6,
611 cont_attribute7,
612 cont_attribute8,
613 cont_attribute9,
614 cont_attribute10,
615 cont_attribute11,
616 cont_attribute12,
617 cont_attribute13,
618 cont_attribute14,
619 cont_attribute15,
620 cont_attribute16,
621 cont_attribute17,
622 cont_attribute18,
623 cont_attribute19,
624 cont_attribute20,
625 cont_information_category,
626 cont_information1,
627 cont_information2,
628 cont_information3,
629 cont_information4,
630 cont_information5,
631 cont_information6,
632 cont_information7,
633 cont_information8,
634 cont_information9,
635 cont_information10,
636 cont_information11,
637 cont_information12,
638 cont_information13,
639 cont_information14,
640 cont_information15,
641 cont_information16,
642 cont_information17,
643 cont_information18,
644 cont_information19,
645 cont_information20,
646 date_start,
647 start_life_reason_id,
648 date_end,
649 end_life_reason_id,
650 rltd_per_rsds_w_dsgntr_flag,
651 personal_flag,
652 sequence_number,
653 dependent_flag,
654 beneficiary_flag
655
656 ) VALUES (
657 X_Contact_Relationship_Id,
658 X_Business_Group_Id,
659 X_Person_Id,
660 X_Contact_Person_Id,
661 X_Contact_Type,
662 X_Comments,
663 X_Bondholder_Flag,
667 X_Cont_Attribute1,
664 l_Third_Party_Pay_Flag,
665 l_Primary_Contact_Flag,
666 X_Cont_Attribute_Category,
668 X_Cont_Attribute2,
669 X_Cont_Attribute3,
670 X_Cont_Attribute4,
671 X_Cont_Attribute5,
672 X_Cont_Attribute6,
673 X_Cont_Attribute7,
674 X_Cont_Attribute8,
675 X_Cont_Attribute9,
676 X_Cont_Attribute10,
677 X_Cont_Attribute11,
678 X_Cont_Attribute12,
679 X_Cont_Attribute13,
680 X_Cont_Attribute14,
681 X_Cont_Attribute15,
682 X_Cont_Attribute16,
683 X_Cont_Attribute17,
684 X_Cont_Attribute18,
685 X_Cont_Attribute19,
686 X_Cont_Attribute20,
687 X_Cont_Information_Category,
688 X_Cont_Information1,
689 X_Cont_Information2,
690 X_Cont_Information3,
691 X_Cont_Information4,
692 X_Cont_Information5,
693 X_Cont_Information6,
694 X_Cont_Information7,
695 X_Cont_Information8,
696 X_Cont_Information9,
697 X_Cont_Information10,
698 X_Cont_Information11,
699 X_Cont_Information12,
700 X_Cont_Information13,
701 X_Cont_Information14,
702 X_Cont_Information15,
703 X_Cont_Information16,
704 X_Cont_Information17,
705 X_Cont_Information18,
706 X_Cont_Information19,
707 X_Cont_Information20,
708 X_Date_Start,
709 X_Start_Life_Reason_Id,
710 X_Date_End,
711 X_End_Life_Reason_Id,
712 l_Rltd_Per_Rsds_W_Dsgntr_Flag,
713 X_personal_flag,
714 l_sequence_number,
715 X_dependent_flag,
716 X_beneficiary_flag
717 );
718 --
719 --
720 -- Start of Fix for 1408379
721 --
722 l_new.person_id := x_person_id;
723 l_new.contact_person_id := x_contact_person_id;
724 l_new.business_group_id := x_business_group_id;
725 l_new.date_start := x_date_start;
726 l_new.date_end := x_date_end;
727 l_new.contact_type := x_contact_type;
728 l_new.personal_flag := x_personal_flag;
729 l_new.start_life_reason_id := x_start_life_reason_id;
730 l_new.end_life_reason_id := x_end_life_reason_id;
731 l_new.rltd_per_rsds_w_dsgntr_flag := l_rltd_per_rsds_w_dsgntr_flag;
732 l_new.contact_relationship_id := x_contact_relationship_id;
733 --
734 -- Bug 1772037 fix
735 --
736 l_new.cont_attribute1 := x_cont_attribute1 ;
737 l_new.cont_attribute2 := x_cont_attribute2 ;
738 l_new.cont_attribute3 := x_cont_attribute3 ;
739 l_new.cont_attribute4 := x_cont_attribute4 ;
740 l_new.cont_attribute5 := x_cont_attribute5 ;
741 l_new.cont_attribute6 := x_cont_attribute6 ;
742 l_new.cont_attribute7 := x_cont_attribute7 ;
743 l_new.cont_attribute8 := x_cont_attribute8 ;
744 l_new.cont_attribute9 := x_cont_attribute9 ;
745 l_new.cont_attribute10 := x_cont_attribute10 ;
746 l_new.cont_attribute11 := x_cont_attribute11 ;
747 l_new.cont_attribute12 := x_cont_attribute12 ;
748 l_new.cont_attribute13 := x_cont_attribute13 ;
749 l_new.cont_attribute14 := x_cont_attribute14 ;
750 l_new.cont_attribute15 := x_cont_attribute15 ;
751 l_new.cont_attribute16 := x_cont_attribute16 ;
752 l_new.cont_attribute17 := x_cont_attribute17 ;
753 l_new.cont_attribute18 := x_cont_attribute18 ;
754 l_new.cont_attribute19 := x_cont_attribute19 ;
755 l_new.cont_attribute20 := x_cont_attribute20 ;
756 --
757 -- End fix 1772037
758 --
759 ben_con_ler.ler_chk(p_old => l_old,
760 p_new => l_new,
761 p_effective_date => NVL(x_date_start,SYSDATE));
762 --
763 -- End of Fix for 1408379
764 --
765 hr_utility.set_location('Insert_contact',1);
766 OPEN C;
767 FETCH C INTO X_Rowid;
768 IF (C%NOTFOUND) THEN
769 CLOSE C;
770 RAISE NO_DATA_FOUND;
771 END IF;
772 CLOSE C;
773 END Insert_Contact;
774 ------------------------------------------------------------
775 -- Local procedure to insert individual person row
776 --
777
778 FUNCTION Insert_Person RETURN NUMBER IS
779 --
780 l_person per_all_people_f%ROWTYPE;
781 --
782 l_person_id NUMBER;
783 l_person_type_id NUMBER;
784 l_rowid VARCHAR2(30);
785 l_full_name VARCHAR2(240);
786 l_order_name varchar2(240);
787 l_global_name varchar2(240);
788 l_local_name varchar2(240);
789
790 l_effective_start_date DATE;
791 l_effective_end_date DATE;
792 l_start_date DATE;
793 l_dup_name VARCHAR2(1);
794 --
795 CURSOR C IS
796 SELECT ROWID
797 FROM per_people_f
798 WHERE person_id = X_Person_Id;
799 --
800 CURSOR C2 IS
801 SELECT per_people_s.NEXTVAL
802 FROM sys.dual;
803 --
804 CURSOR c_person IS
805 SELECT *
806 FROM per_all_people_f
807 WHERE person_id = l_person_id
808 AND l_effective_start_date
809 BETWEEN effective_start_date
810 AND effective_end_date;
811 --
812 -- Bug 4295302
813 --
814 l_ppf_ler_new_rec ben_ppf_ler.g_ppf_ler_rec;
818 --
815 l_ppf_ler_old_rec ben_ppf_ler.g_ppf_ler_rec;
816 --
817 BEGIN
819 --hr_person.derive_full_name (x_first_name, x_middle_names,
820 -- x_last_name, x_known_as, x_title, x_suffix, x_pre_name_adjunct,
821 -- x_date_of_birth, NULL, x_business_group_id, l_full_name,
822 -- l_dup_name);
823 hr_person_name.derive_person_names
824 (p_format_name => NULL, -- generate all names
825 p_business_group_id => x_business_group_id,
826 p_person_id => NULL,
827 p_first_name => x_first_name,
828 p_middle_names => x_middle_names,
829 p_last_name => x_last_name,
830 p_known_as => x_known_as,
831 p_title => x_title,
832 p_suffix => x_suffix,
833 p_pre_name_adjunct => x_pre_name_adjunct,
834 p_date_of_birth => x_date_of_birth,
835 p_previous_last_name => NULL,
836 p_email_address => x_email_address ,
837 p_employee_number => NULL,
838 p_applicant_number => NULL,
839 p_npw_number => NULL,
840 p_per_information1 => x_per_information1 ,
841 p_per_information2 => x_per_information2 ,
842 p_per_information3 => x_per_information3 ,
843 p_per_information4 => x_per_information4 ,
844 p_per_information5 => x_per_information5 ,
845 p_per_information6 => x_per_information6 ,
846 p_per_information7 => x_per_information7 ,
847 p_per_information8 => x_per_information8 ,
848 p_per_information9 => x_per_information9 ,
849 p_per_information10 => x_per_information10 ,
850 p_per_information11 => x_per_information11 ,
851 p_per_information12 => x_per_information12 ,
852 p_per_information13 => x_per_information13 ,
853 p_per_information14 => x_per_information14 ,
854 p_per_information15 => x_per_information15 ,
855 p_per_information16 => x_per_information16 ,
856 p_per_information17 => x_per_information17 ,
857 p_per_information18 => x_per_information18 ,
858 p_per_information19 => x_per_information19 ,
859 p_per_information20 => x_per_information20 ,
860 p_per_information21 => x_per_information21 ,
861 p_per_information22 => x_per_information22 ,
862 p_per_information23 => x_per_information23 ,
863 p_per_information24 => x_per_information24 ,
864 p_per_information25 => x_per_information25 ,
865 p_per_information26 => x_per_information26 ,
866 p_per_information27 => x_per_information27 ,
867 p_per_information28 => x_per_information28 ,
868 p_per_information29 => x_per_information29 ,
869 p_per_information30 => x_per_information30 ,
870 p_attribute1 => x_attribute1 ,
871 p_attribute2 => x_attribute2 ,
872 p_attribute3 => x_attribute3 ,
873 p_attribute4 => x_attribute4 ,
874 p_attribute5 => x_attribute5 ,
875 p_attribute6 => x_attribute6 ,
876 p_attribute7 => x_attribute7 ,
877 p_attribute8 => x_attribute8 ,
878 p_attribute9 => x_attribute9 ,
879 p_attribute10 => x_attribute10 ,
880 p_attribute11 => x_attribute11 ,
881 p_attribute12 => x_attribute12 ,
882 p_attribute13 => x_attribute13 ,
883 p_attribute14 => x_attribute14 ,
884 p_attribute15 => x_attribute15 ,
885 p_attribute16 => x_attribute16 ,
886 p_attribute17 => x_attribute17 ,
887 p_attribute18 => x_attribute18 ,
888 p_attribute19 => x_attribute19 ,
889 p_attribute20 => x_attribute20 ,
890 p_attribute21 => x_attribute21 ,
891 p_attribute22 => x_attribute22 ,
892 p_attribute23 => x_attribute23,
893 p_attribute24 => x_attribute24,
894 p_attribute25 => x_attribute25,
895 p_attribute26 => x_attribute26,
896 p_attribute27 => x_attribute27,
897 p_attribute28 => x_attribute28,
898 p_attribute29 => x_attribute29,
899 p_attribute30 => x_attribute30,
900 p_full_name => l_full_name,
901 p_order_name => l_order_name,
902 p_global_name => l_global_name,
903 p_local_name => l_local_name,
904 p_duplicate_flag => l_dup_name
905 );
906 --
907 l_effective_end_date := Hr_General.end_of_time;
908 l_effective_start_date := x_session_date;
909 l_start_date := x_session_date;
910 --
911 OPEN C2;
912 FETCH C2 INTO l_person_id;
913 CLOSE C2;
914 --
915 -- Verify party id, if one is passed in
916 IF x_party_id IS NOT NULL THEN
917 --
918 per_per_bus.chk_party_id
919 (p_person_id => l_person_id
920 ,p_party_id => x_party_id
921 ,p_effective_date => l_effective_start_date
922 ,p_object_version_number => NULL);
923 END IF;
924
925 INSERT INTO PER_PEOPLE_F(
926 person_id,
927 effective_start_date,
928 effective_end_date,
929 business_group_id,
933 comment_id,
930 person_type_id,
931 last_name,
932 start_date,
934 current_applicant_flag,
935 current_emp_or_apl_flag,
936 current_employee_flag,
937 date_of_birth,
938 first_name,
939 full_name,
940 middle_names,
941 sex,
942 title,
943 pre_name_adjunct,
944 suffix,
945 national_identifier,
946 attribute_category,
947 attribute1,
948 attribute2,
949 attribute3,
950 attribute4,
951 attribute5,
952 attribute6,
953 attribute7,
954 attribute8,
955 attribute9,
956 attribute10,
957 attribute11,
958 attribute12,
959 attribute13,
960 attribute14,
961 attribute15,
962 attribute16,
963 attribute17,
964 attribute18,
965 attribute19,
966 attribute20,
967 attribute21,
968 attribute22,
969 attribute23,
970 attribute24,
971 attribute25,
972 attribute26,
973 attribute27,
974 attribute28,
975 attribute29,
976 attribute30,
977 Per_Information_category,
978 Per_Information1,
979 Per_Information2,
980 Per_Information3,
981 Per_Information4,
982 Per_Information5,
983 Per_Information6,
984 Per_Information7,
985 Per_Information8,
986 Per_Information9,
987 Per_Information10,
988 Per_Information11,
989 Per_Information12,
990 Per_Information13,
991 Per_Information14,
992 Per_Information15,
993 Per_Information16,
994 Per_Information17,
995 Per_Information18,
996 Per_Information19,
997 Per_Information20,
998 Per_Information21,
999 Per_Information22,
1000 Per_Information23,
1001 Per_Information24,
1002 Per_Information25,
1003 Per_Information26,
1004 Per_Information27,
1005 Per_Information28,
1006 Per_Information29,
1007 Per_Information30,
1008 Known_As,
1009 Party_Id,
1010 marital_status,
1011 nationality,
1012 blood_type,
1013 correspondence_language,
1014 honors,
1015 rehire_authorizor,
1016 rehire_recommendation,
1017 resume_exists,
1018 resume_last_updated,
1019 second_passport_exists,
1020 student_status,
1021 date_of_death,
1022 uses_tobacco_flag,
1023 town_of_birth,
1024 region_of_birth,
1025 country_of_birth,
1026 fast_path_employee,
1027 email_address,
1028 fte_capacity,
1029 global_name, -- #3889584
1030 local_name,
1031 order_name
1032 ) VALUES (
1033 l_person_id,
1034 l_effective_start_date,
1035 l_effective_end_date,
1036 X_Business_Group_Id,
1037 hr_person_type_usage_info.get_default_person_type_id(X_Person_Type_Id),
1038 -- X_Person_Type_Id,
1039 X_Last_Name,
1040 l_start_date,
1041 X_Comment_Id,
1042 NULL,
1043 NULL,
1044 NULL,
1045 X_Date_Of_Birth,
1046 X_First_Name,
1047 l_full_name,
1048 X_Middle_Names,
1049 X_Sex,
1050 X_Title,
1051 X_PRE_NAME_ADJUNCT,
1052 X_SUFFIX,
1053 X_National_Identifier,
1054 X_Attribute_Category,
1055 X_Attribute1,
1056 X_Attribute2,
1057 X_Attribute3,
1058 X_Attribute4,
1059 X_Attribute5,
1060 X_Attribute6,
1061 X_Attribute7,
1062 X_Attribute8,
1063 X_Attribute9,
1064 X_Attribute10,
1065 X_Attribute11,
1066 X_Attribute12,
1067 X_Attribute13,
1068 X_Attribute14,
1069 X_Attribute15,
1070 X_Attribute16,
1071 X_Attribute17,
1072 X_Attribute18,
1073 X_Attribute19,
1074 X_Attribute20,
1075 X_Attribute21,
1076 X_Attribute22,
1077 X_Attribute23,
1078 X_Attribute24,
1079 X_Attribute25,
1080 X_Attribute26,
1081 X_Attribute27,
1082 X_Attribute28,
1083 X_Attribute29,
1084 X_Attribute30,
1085 X_Per_Information_category,
1086 X_Per_Information1,
1087 X_Per_Information2,
1088 X_Per_Information3,
1089 X_Per_Information4,
1090 X_Per_Information5,
1091 X_Per_Information6,
1095 X_Per_Information10,
1092 X_Per_Information7,
1093 X_Per_Information8,
1094 X_Per_Information9,
1096 X_Per_Information11,
1097 X_Per_Information12,
1098 X_Per_Information13,
1099 X_Per_Information14,
1100 X_Per_Information15,
1101 X_Per_Information16,
1102 X_Per_Information17,
1103 X_Per_Information18,
1104 X_Per_Information19,
1105 X_Per_Information20,
1106 X_Per_Information21,
1107 X_Per_Information22,
1108 X_Per_Information23,
1109 X_Per_Information24,
1110 X_Per_Information25,
1111 X_Per_Information26,
1112 X_Per_Information27,
1113 X_Per_Information28,
1114 X_Per_Information29,
1115 X_Per_Information30,
1116 X_Known_As,
1117 X_Party_Id,
1118 X_marital_status,
1119 X_nationality,
1120 X_blood_type,
1121 X_correspondence_language,
1122 X_honors,
1123 X_rehire_authorizor,
1124 X_rehire_recommendation,
1125 X_resume_exists,
1126 X_resume_last_updated,
1127 X_second_passport_exists,
1128 X_student_status,
1129 X_date_of_death,
1130 X_uses_tobacco_flag,
1131 X_town_of_birth,
1132 X_region_of_birth,
1133 X_country_of_birth,
1134 X_fast_path_employee,
1135 X_email_address,
1136 X_fte_capacity,
1137 l_global_name, -- #3889584
1138 l_local_name,
1139 l_order_name
1140 );
1141 --2483186: now we are securing by contacts,
1142 -- the security list maintenance must be done for all
1143 -- inserts (note CWK are done in their own API anyway, not here)
1144 --
1145
1146 hr_utility.set_location('pecon01t.insert_row',70);
1147 --
1148 -- Bug 4295302
1149 --
1150 l_ppf_ler_new_rec.business_group_id := x_business_group_id;
1151 l_ppf_ler_new_rec.person_id := l_person_id;
1152 l_ppf_ler_new_rec.effective_start_date := l_effective_start_date;
1153 l_ppf_ler_new_rec.effective_end_date := l_effective_end_date;
1154 l_ppf_ler_new_rec.date_of_birth := x_date_of_birth;
1155 l_ppf_ler_new_rec.date_of_death := x_date_of_death;
1156 l_ppf_ler_new_rec.marital_status := x_marital_status;
1157 l_ppf_ler_new_rec.sex := x_sex;
1158 l_ppf_ler_new_rec.student_status := x_student_status;
1159 l_ppf_ler_new_rec.uses_tobacco_flag := x_uses_tobacco_flag;
1160 /*
1161 l_ppf_ler_new_rec.benefit_group_id := l_ppf_ler_old_rec.benefit_group_id;
1162 l_ppf_ler_new_rec.DPDNT_VLNTRY_SVCE_FLAG := l_ppf_ler_old_rec.dpdnt_vlntry_svce_flag;
1163 l_ppf_ler_new_rec.RECEIPT_OF_DEATH_CERT_DATE := l_ppf_ler_old_rec.receipt_of_death_cert_date;
1164 l_ppf_ler_new_rec.on_military_service := l_ppf_ler_old_rec.on_military_service;
1165 l_ppf_ler_new_rec.registered_disabled_flag := l_ppf_ler_old_rec.registered_disabled_flag;
1166 l_ppf_ler_new_rec.coord_ben_med_pln_no := l_ppf_ler_old_rec.coord_ben_med_pln_no;
1167 l_ppf_ler_new_rec.coord_ben_no_cvg_flag := l_ppf_ler_old_rec.coord_ben_no_cvg_flag;
1168 */
1169 l_ppf_ler_new_rec.per_information10 := x_per_information10;
1170 l_ppf_ler_new_rec.attribute1 := x_attribute1;
1171 l_ppf_ler_new_rec.attribute2 := x_attribute2;
1172 l_ppf_ler_new_rec.attribute3 := x_attribute3;
1173 l_ppf_ler_new_rec.attribute4 := x_attribute4;
1174 l_ppf_ler_new_rec.attribute5 := x_attribute5;
1175 l_ppf_ler_new_rec.attribute6 := x_attribute6;
1176 l_ppf_ler_new_rec.attribute7 := x_attribute7;
1177 l_ppf_ler_new_rec.attribute8 := x_attribute8;
1178 l_ppf_ler_new_rec.attribute9 := x_attribute9;
1179 l_ppf_ler_new_rec.attribute10 := x_attribute10;
1180 l_ppf_ler_new_rec.attribute11 := x_attribute11;
1181 l_ppf_ler_new_rec.attribute12 := x_attribute12;
1182 l_ppf_ler_new_rec.attribute13 := x_attribute13;
1183 l_ppf_ler_new_rec.attribute14 := x_attribute14;
1184 l_ppf_ler_new_rec.attribute15 := x_attribute15;
1185 l_ppf_ler_new_rec.attribute16 := x_attribute16;
1186 l_ppf_ler_new_rec.attribute17 := x_attribute17;
1187 l_ppf_ler_new_rec.attribute18 := x_attribute18;
1188 l_ppf_ler_new_rec.attribute19 := x_attribute19;
1189 l_ppf_ler_new_rec.attribute20 := x_attribute20;
1190 l_ppf_ler_new_rec.attribute21 := x_attribute21;
1191 l_ppf_ler_new_rec.attribute22 := x_attribute22;
1192 l_ppf_ler_new_rec.attribute23 := x_attribute23;
1193 l_ppf_ler_new_rec.attribute24 := x_attribute24;
1194 l_ppf_ler_new_rec.attribute25 := x_attribute25;
1195 l_ppf_ler_new_rec.attribute26 := x_attribute26;
1196 l_ppf_ler_new_rec.attribute27 := x_attribute27;
1197 l_ppf_ler_new_rec.attribute28 := x_attribute28;
1198 l_ppf_ler_new_rec.attribute29 := x_attribute29;
1199 l_ppf_ler_new_rec.attribute30 := x_attribute30;
1200 --
1201 -- This procedure is will create potential life event reasons if the Person Change
1205 ,p_new => l_ppf_ler_new_rec
1202 -- criteria is met (a part of Oracle Advanced Benefits functionality)
1203 --
1204 ben_ppf_ler.ler_chk( p_old => l_ppf_ler_old_rec
1206 ,p_effective_date => x_session_date );
1207
1208 --
1209
1210 --
1211 -- Bug 4295302
1212 --
1213
1214 --
1215 hr_security_internal.populate_new_person
1216 (p_business_group_id=>x_business_group_id
1217 ,p_person_id =>l_person_id);
1218 --
1219 hr_utility.set_location('pecon01t.insert_row',75);
1220 --
1221 --
1222 /* BEGIN OF PARTY_ID WORK */
1223 /* This is being commented out as part of TCA party unmerge. This part of the code
1224 is being called from per_person_type_usage_internal.maintain_person_type_usage
1225 -- tpapired
1226 --
1227 OPEN c_person;
1228 --
1229 FETCH c_person INTO l_person;
1230 --
1231 CLOSE c_person;
1232 --
1233 per_hrtca_merge.create_tca_person(p_rec => l_person);
1234 --
1235 hr_utility.set_location('UPDATING party id',10);
1236 --
1237 -- Now assign the resulting party id back to the record.
1238 --
1239 IF x_party_id IS NULL THEN
1240 UPDATE per_people_f
1241 SET party_id = l_person.party_id
1242 WHERE person_id = l_person_id;
1243 END IF;
1244 --
1245 */
1246 /* END OF PARTY ID WORK */
1247 hr_utility.set_location('Insert_person',2);
1248 OPEN C;
1249 FETCH C INTO l_Rowid;
1250 IF (C%NOTFOUND) THEN
1251 CLOSE C;
1252 RAISE NO_DATA_FOUND;
1253 END IF;
1254 CLOSE C;
1255 --
1256 -- added for PTU
1257 --
1258 IF X_Person_Type_Id IS NOT NULL AND X_Person_Type_Id <> hr_api.g_number THEN
1259 BEGIN
1260 SELECT person_type_id INTO l_person_type_id
1261 FROM per_person_types
1262 WHERE person_type_id = X_Person_Type_Id
1263 AND business_group_id = X_Business_Group_Id
1264 AND active_flag = 'Y'
1265 AND system_person_type = 'OTHER';
1266 EXCEPTION
1267 WHEN NO_DATA_FOUND THEN
1268 hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
1269 hr_utility.raise_error;
1270 END;
1271 l_person_type_id := X_Person_Type_Id;
1272 ELSE
1273 l_person_type_id := hr_person_type_usage_info.get_default_person_type_id
1274 (X_Business_Group_Id,
1275 'OTHER');
1276 END IF;
1277 --
1278 hr_per_type_usage_internal.maintain_person_type_usage
1279 (p_effective_date => x_session_date
1280 ,p_person_id => l_person_id
1281 ,p_person_type_id => l_person_type_id
1282 );
1283 --
1284 -- end of PTU changes
1285 --
1286 RETURN(l_person_id);
1287 END Insert_Person;
1288
1289 ----------------------------------------------
1290 -- Main procedure
1291 --
1292 BEGIN
1293 -- Check to ensure that there is only one primary contact for the person
1294 --
1295 IF X_Primary_Contact_Flag = 'Y' AND
1296 check_primary_contact(p_rowid => X_Rowid
1297 ,p_person_id => X_Person_Id
1298 ,p_date_start => X_date_start
1299 ,p_date_end => X_date_end
1300 ) THEN
1301 hr_utility.set_message(801,'PER_7125_EMP_CON_PRIMARY');
1302 hr_utility.raise_error;
1303 END IF;
1304 --
1305 -- 2073795: added validation for X_Contact_Person_Id. If the form has previously failed a multi
1306 -- record insert, it still passes id values and person is not created. This is a problem.
1307 --
1308 if X_Contact_Person_Id is not null then
1309 open csr_per_exists;
1310 fetch csr_per_exists into l_dummy;
1311 if csr_per_exists%notfound then
1312 X_Contact_Person_Id := null;
1313 else
1314 null;
1315 end if;
1316 close csr_per_exists;
1317 end if;
1318 --
1319 -- If the Contact Person is null then this is a new person and a row must
1320 -- be created in PER_PEOPLE_F
1321 --
1322 IF X_Contact_Person_Id IS NULL THEN
1323 -- Need re-entrant code to be able to warn user that a potentially duplicate
1324 -- person may be created
1325 --
1326 -- START commented by risgupta for duplicate check enhancement bug 3988762
1327 -- This duplicate check will be taken care on contact form
1328 /*IF X_Reentry_Flag = 1 THEN
1329
1330 OPEN Check_For_Duplicate_Person;
1331 FETCH Check_For_Duplicate_Person INTO l_duplicate_exists;
1332 CLOSE Check_For_Duplicate_Person;
1333 IF l_duplicate_exists = 'Y' THEN
1334 X_Reentry_Flag := 2;
1335 RETURN;
1336 END IF;
1337 END IF;*/
1338 -- END 3988762
1339
1340 X_Contact_Person_Id := Insert_Person;
1341 END IF;
1342 --
1343 -- Prepare variables prior to creating Contact Relationship and its mirror
1344 --
1345 l_contact_type := X_Contact_Type;
1346 l_person_id := X_Person_Id;
1347 l_contact_person_id := X_Contact_Person_Id;
1348 hr_utility.set_location('X_cont_type : '||X_Contact_Type,10);
1349 hr_utility.set_location('X_Mirr_type : '||X_Mirror_Type,20);
1350 hr_utility.set_location('X_Cont_per_id : '||TO_CHAR(X_Contact_Person_Id),30);
1351
1352 --
1353 -- Insert the Mirror Relationship
1354 --
1358 X_Person_Id := l_contact_person_id;
1355 IF X_Create_Mirror_Flag = 'Y' THEN
1356 X_Contact_Type := X_Mirror_Type;
1357 --
1359 X_Contact_Person_Id := l_person_id;
1360 l_Third_Party_Pay_Flag := 'N';
1361 l_Primary_Contact_Flag := 'N';
1362 l_dependent_flag := 'N';
1363 l_beneficiary_flag := 'N';
1364 l_Rltd_Per_Rsds_W_Dsgntr_Flag := X_Rltd_Per_Rsds_W_Dsgntr_Flag;
1365 l_personal_flag := X_personal_flag;
1366 l_sequence_number := NULL;
1367 --
1368 Insert_Contact;
1369 END IF;
1370 --
1371 -- Insert the Real Relationship
1372 --
1373 X_Contact_Type := l_contact_type;
1374 X_Person_Id := l_person_id;
1375 X_Contact_Person_Id := l_contact_person_id;
1376 X_Rowid := '';
1377 X_Contact_Relationship_Id := '';
1378 l_Third_Party_Pay_Flag := X_Third_Party_Pay_Flag;
1379 l_Primary_Contact_Flag := X_Primary_Contact_Flag;
1380 l_Rltd_Per_Rsds_W_Dsgntr_Flag := X_Rltd_Per_Rsds_W_Dsgntr_Flag;
1381 l_personal_flag := X_personal_flag;
1382 l_sequence_number := X_sequence_number;
1383 --
1384 Insert_Contact;
1385 --
1386 -- 1766066: added call for contact start date enh.
1387 --
1388 per_people12_pkg.maintain_coverage(p_person_id => X_Contact_Person_Id
1389 ,p_type => 'CONT'
1390 );
1391 -- 1766066 end.
1392 END Insert_Row;
1393
1394 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
1395 X_Contact_Relationship_Id NUMBER,
1396 X_Business_Group_Id NUMBER,
1397 X_Person_Id NUMBER,
1398 X_Contact_Person_Id NUMBER,
1399 X_Contact_Type VARCHAR2,
1400 X_Comments VARCHAR2,
1401 X_Bondholder_Flag VARCHAR2,
1402 X_Third_Party_Pay_Flag VARCHAR2,
1403 X_Primary_Contact_Flag VARCHAR2,
1404 X_Cont_Attribute_Category VARCHAR2,
1405 X_Cont_Attribute1 VARCHAR2,
1406 X_Cont_Attribute2 VARCHAR2,
1407 X_Cont_Attribute3 VARCHAR2,
1408 X_Cont_Attribute4 VARCHAR2,
1409 X_Cont_Attribute5 VARCHAR2,
1410 X_Cont_Attribute6 VARCHAR2,
1411 X_Cont_Attribute7 VARCHAR2,
1412 X_Cont_Attribute8 VARCHAR2,
1413 X_Cont_Attribute9 VARCHAR2,
1414 X_Cont_Attribute10 VARCHAR2,
1415 X_Cont_Attribute11 VARCHAR2,
1416 X_Cont_Attribute12 VARCHAR2,
1417 X_Cont_Attribute13 VARCHAR2,
1418 X_Cont_Attribute14 VARCHAR2,
1419 X_Cont_Attribute15 VARCHAR2,
1420 X_Cont_Attribute16 VARCHAR2,
1421 X_Cont_Attribute17 VARCHAR2,
1422 X_Cont_Attribute18 VARCHAR2,
1423 X_Cont_Attribute19 VARCHAR2,
1424 X_Cont_Attribute20 VARCHAR2,
1425 X_Cont_Information_Category VARCHAR2,
1426 X_Cont_Information1 VARCHAR2,
1427 X_Cont_Information2 VARCHAR2,
1428 X_Cont_Information3 VARCHAR2,
1429 X_Cont_Information4 VARCHAR2,
1430 X_Cont_Information5 VARCHAR2,
1431 X_Cont_Information6 VARCHAR2,
1432 X_Cont_Information7 VARCHAR2,
1433 X_Cont_Information8 VARCHAR2,
1434 X_Cont_Information9 VARCHAR2,
1435 X_Cont_Information10 VARCHAR2,
1436 X_Cont_Information11 VARCHAR2,
1437 X_Cont_Information12 VARCHAR2,
1438 X_Cont_Information13 VARCHAR2,
1439 X_Cont_Information14 VARCHAR2,
1440 X_Cont_Information15 VARCHAR2,
1441 X_Cont_Information16 VARCHAR2,
1442 X_Cont_Information17 VARCHAR2,
1443 X_Cont_Information18 VARCHAR2,
1444 X_Cont_Information19 VARCHAR2,
1445 X_Cont_Information20 VARCHAR2,
1446 X_Per_Information1 VARCHAR2,
1447 X_Per_Information2 VARCHAR2,
1448 X_Per_Information3 VARCHAR2,
1449 X_Per_Information4 VARCHAR2,
1450 X_Per_Information5 VARCHAR2,
1451 X_Per_Information6 VARCHAR2,
1452 X_Per_Information7 VARCHAR2,
1453 X_Per_Information8 VARCHAR2,
1454 X_Per_Information9 VARCHAR2,
1455 X_Per_Information10 VARCHAR2,
1456 X_Per_Information11 VARCHAR2,
1457 X_Per_Information12 VARCHAR2,
1458 X_Per_Information13 VARCHAR2,
1459 X_Per_Information14 VARCHAR2,
1460 X_Per_Information15 VARCHAR2,
1461 X_Per_Information16 VARCHAR2,
1462 X_Per_Information17 VARCHAR2,
1463 X_Per_Information18 VARCHAR2,
1464 X_Per_Information19 VARCHAR2,
1465 X_Per_Information20 VARCHAR2,
1466 X_Per_Information21 VARCHAR2,
1467 X_Per_Information22 VARCHAR2,
1468 X_Per_Information23 VARCHAR2,
1469 X_Per_Information24 VARCHAR2,
1470 X_Per_Information25 VARCHAR2,
1471 X_Per_Information26 VARCHAR2,
1472 X_Per_Information27 VARCHAR2,
1473 X_Per_Information28 VARCHAR2,
1474 X_Per_Information29 VARCHAR2,
1475 X_Per_Information30 VARCHAR2,
1476 X_Known_As VARCHAR2,
1477 X_Date_Start DATE,
1478 X_Start_Life_Reason_Id VARCHAR2,
1479 X_Date_End DATE,
1480 X_End_Life_Reason_Id VARCHAR2,
1481 X_Rltd_Per_Rsds_W_Dsgntr_Flag VARCHAR2,
1482 X_Personal_Flag VARCHAR2,
1483 X_Sequence_Number NUMBER,
1484 X_Dependent_Flag VARCHAR2,
1485 X_Beneficiary_Flag VARCHAR2
1486
1487 ) IS
1488 CURSOR C IS
1489 SELECT *
1490 FROM PER_CONTACT_RELATIONSHIPS
1491 WHERE ROWID = X_Rowid
1492 FOR UPDATE OF Contact_Relationship_Id NOWAIT;
1493 Recinfo C%ROWTYPE;
1494 BEGIN
1495 OPEN C;
1496 FETCH C INTO Recinfo;
1497 IF (C%NOTFOUND) THEN
1498 CLOSE C;
1499 RAISE NO_DATA_FOUND;
1500 END IF;
1501 CLOSE C;
1502 --
1503 Recinfo.contact_type := RTRIM(Recinfo.contact_type);
1504 Recinfo.comments := RTRIM(Recinfo.comments);
1505 Recinfo.Bondholder_Flag:=RTRIM(Recinfo.Bondholder_Flag);
1506 Recinfo.Third_Party_Pay_Flag :=RTRIM(Recinfo.Third_Party_Pay_Flag);
1507 Recinfo.primary_contact_flag := RTRIM(Recinfo.primary_contact_flag);
1508 Recinfo.cont_attribute_category := RTRIM(Recinfo.cont_attribute_category);
1509 Recinfo.cont_attribute1 := RTRIM(Recinfo.cont_attribute1);
1510 Recinfo.cont_attribute2 := RTRIM(Recinfo.cont_attribute2);
1511 Recinfo.cont_attribute3 := RTRIM(Recinfo.cont_attribute3);
1512 Recinfo.cont_attribute4 := RTRIM(Recinfo.cont_attribute4);
1513 Recinfo.cont_attribute5 := RTRIM(Recinfo.cont_attribute5);
1514 Recinfo.cont_attribute6 := RTRIM(Recinfo.cont_attribute6);
1515 Recinfo.cont_attribute7 := RTRIM(Recinfo.cont_attribute7);
1516 Recinfo.cont_attribute8 := RTRIM(Recinfo.cont_attribute8);
1517 Recinfo.cont_attribute9 := RTRIM(Recinfo.cont_attribute9);
1518 Recinfo.cont_attribute10 := RTRIM(Recinfo.cont_attribute10);
1519 Recinfo.cont_attribute11 := RTRIM(Recinfo.cont_attribute11);
1520 Recinfo.cont_attribute12 := RTRIM(Recinfo.cont_attribute12);
1521 Recinfo.cont_attribute13 := RTRIM(Recinfo.cont_attribute13);
1522 Recinfo.cont_attribute14 := RTRIM(Recinfo.cont_attribute14);
1523 Recinfo.cont_attribute15 := RTRIM(Recinfo.cont_attribute15);
1524 Recinfo.cont_attribute16 := RTRIM(Recinfo.cont_attribute16);
1525 Recinfo.cont_attribute17 := RTRIM(Recinfo.cont_attribute17);
1526 Recinfo.cont_attribute18 := RTRIM(Recinfo.cont_attribute18);
1530 Recinfo.cont_information1 := RTRIM(Recinfo.cont_information1);
1527 Recinfo.cont_attribute19 := RTRIM(Recinfo.cont_attribute19);
1528 Recinfo.cont_attribute20 := RTRIM(Recinfo.cont_attribute20);
1529 Recinfo.cont_information_category := RTRIM(Recinfo.cont_information_category);
1531 Recinfo.cont_information2 := RTRIM(Recinfo.cont_information2);
1532 Recinfo.cont_information3 := RTRIM(Recinfo.cont_information3);
1533 Recinfo.cont_information4 := RTRIM(Recinfo.cont_information4);
1534 Recinfo.cont_information5 := RTRIM(Recinfo.cont_information5);
1535 Recinfo.cont_information6 := RTRIM(Recinfo.cont_information6);
1536 Recinfo.cont_information7 := RTRIM(Recinfo.cont_information7);
1537 Recinfo.cont_information8 := RTRIM(Recinfo.cont_information8);
1538 Recinfo.cont_information9 := RTRIM(Recinfo.cont_information9);
1539 Recinfo.cont_information10 := RTRIM(Recinfo.cont_information10);
1540 Recinfo.cont_information11 := RTRIM(Recinfo.cont_information11);
1541 Recinfo.cont_information12 := RTRIM(Recinfo.cont_information12);
1542 Recinfo.cont_information13 := RTRIM(Recinfo.cont_information13);
1543 Recinfo.cont_information14 := RTRIM(Recinfo.cont_information14);
1544 Recinfo.cont_information15 := RTRIM(Recinfo.cont_information15);
1545 Recinfo.cont_information16 := RTRIM(Recinfo.cont_information16);
1546 Recinfo.cont_information17 := RTRIM(Recinfo.cont_information17);
1547 Recinfo.cont_information18 := RTRIM(Recinfo.cont_information18);
1548 Recinfo.cont_information19 := RTRIM(Recinfo.cont_information19);
1549 Recinfo.cont_information20 := RTRIM(Recinfo.cont_information20);
1550 Recinfo.date_start := RTRIM(Recinfo.date_start);
1551 Recinfo.start_life_reason_id := RTRIM(Recinfo.start_life_reason_id);
1552 Recinfo.date_end := RTRIM(Recinfo.date_end);
1553 Recinfo.end_life_reason_id :=RTRIM(Recinfo.end_life_reason_id);
1554 Recinfo.rltd_per_rsds_w_dsgntr_flag :=RTRIM(Recinfo.rltd_per_rsds_w_dsgntr_flag);
1555 Recinfo.personal_flag := RTRIM(Recinfo.personal_flag);
1556 Recinfo.sequence_number := RTRIM(Recinfo.sequence_number);
1557 Recinfo.dependent_flag := RTRIM(Recinfo.dependent_flag);
1558 Recinfo.beneficiary_flag := RTRIM(Recinfo.beneficiary_flag);
1559
1560 --
1561 RETURN;
1562 IF (
1563 ( (Recinfo.contact_relationship_id = X_Contact_Relationship_Id)
1564 OR ( (Recinfo.contact_relationship_id IS NULL)
1565 AND (X_Contact_Relationship_Id IS NULL)))
1566 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
1567 OR ( (Recinfo.business_group_id IS NULL)
1568 AND (X_Business_Group_Id IS NULL)))
1569 AND ( (Recinfo.person_id = X_Person_Id)
1570 OR ( (Recinfo.person_id IS NULL)
1571 AND (X_Person_Id IS NULL)))
1572 AND ( (Recinfo.contact_person_id = X_Contact_Person_Id)
1573 OR ( (Recinfo.contact_person_id IS NULL)
1574 AND (X_Contact_Person_Id IS NULL)))
1575 AND ( (Recinfo.contact_type = X_Contact_Type)
1576 OR ( (Recinfo.contact_type IS NULL)
1577 AND (X_Contact_Type IS NULL)))
1578 AND ( (Recinfo.comments = X_Comments)
1579 OR ( (Recinfo.comments IS NULL)
1580 AND (X_Comments IS NULL)))
1581 AND ( (Recinfo.primary_contact_flag = X_Primary_Contact_Flag)
1582 OR ( (Recinfo.primary_contact_flag IS NULL)
1583 AND (X_Primary_Contact_Flag IS NULL)))
1584 AND ( (Recinfo.cont_attribute_category = X_Cont_Attribute_Category)
1585 OR ( (Recinfo.cont_attribute_category IS NULL)
1586 AND (X_Cont_Attribute_Category IS NULL)))
1587 AND ( (Recinfo.cont_attribute1 = X_Cont_Attribute1)
1588 OR ( (Recinfo.cont_attribute1 IS NULL)
1589 AND (X_Cont_Attribute1 IS NULL)))
1590 AND ( (Recinfo.cont_attribute2 = X_Cont_Attribute2)
1591 OR ( (Recinfo.cont_attribute2 IS NULL)
1592 AND (X_Cont_Attribute2 IS NULL)))
1593 AND ( (Recinfo.cont_attribute3 = X_Cont_Attribute3)
1594 OR ( (Recinfo.cont_attribute3 IS NULL)
1595 AND (X_Cont_Attribute3 IS NULL)))
1596 AND ( (Recinfo.cont_attribute4 = X_Cont_Attribute4)
1597 OR ( (Recinfo.cont_attribute4 IS NULL)
1598 AND (X_Cont_Attribute4 IS NULL)))
1599 AND ( (Recinfo.cont_attribute5 = X_Cont_Attribute5)
1600 OR ( (Recinfo.cont_attribute5 IS NULL)
1601 AND (X_Cont_Attribute5 IS NULL)))
1602 AND ( (Recinfo.cont_attribute6 = X_Cont_Attribute6)
1603 OR ( (Recinfo.cont_attribute6 IS NULL)
1604 AND (X_Cont_Attribute6 IS NULL)))
1605 AND ( (Recinfo.cont_attribute7 = X_Cont_Attribute7)
1606 OR ( (Recinfo.cont_attribute7 IS NULL)
1607 AND (X_Cont_Attribute7 IS NULL)))
1608 AND ( (Recinfo.cont_attribute8 = X_Cont_Attribute8)
1609 OR ( (Recinfo.cont_attribute8 IS NULL)
1610 AND (X_Cont_Attribute8 IS NULL)))
1611 AND ( (Recinfo.cont_attribute9 = X_Cont_Attribute9)
1612 OR ( (Recinfo.cont_attribute9 IS NULL)
1613 AND (X_Cont_Attribute9 IS NULL)))
1614 AND ( (Recinfo.cont_attribute10 = X_Cont_Attribute10)
1615 OR ( (Recinfo.cont_attribute10 IS NULL)
1616 AND (X_Cont_Attribute10 IS NULL)))
1617 AND ( (Recinfo.cont_attribute11 = X_Cont_Attribute11)
1618 OR ( (Recinfo.cont_attribute11 IS NULL)
1619 AND (X_Cont_Attribute11 IS NULL)))
1620 AND ( (Recinfo.cont_attribute12 = X_Cont_Attribute12)
1621 OR ( (Recinfo.cont_attribute12 IS NULL)
1622 AND (X_Cont_Attribute12 IS NULL)))
1623 AND ( (Recinfo.cont_attribute13 = X_Cont_Attribute13)
1627 OR ( (Recinfo.cont_attribute14 IS NULL)
1624 OR ( (Recinfo.cont_attribute13 IS NULL)
1625 AND (X_Cont_Attribute13 IS NULL)))
1626 AND ( (Recinfo.cont_attribute14 = X_Cont_Attribute14)
1628 AND (X_Cont_Attribute14 IS NULL)))
1629 AND ( (Recinfo.cont_attribute15 = X_Cont_Attribute15)
1630 OR ( (Recinfo.cont_attribute15 IS NULL)
1631 AND (X_Cont_Attribute15 IS NULL)))
1632 AND ( (Recinfo.cont_attribute16 = X_Cont_Attribute16)
1633 OR ( (Recinfo.cont_attribute16 IS NULL)
1634 AND (X_Cont_Attribute16 IS NULL)))
1635 AND ( (Recinfo.cont_attribute17 = X_Cont_Attribute17)
1636 OR ( (Recinfo.cont_attribute17 IS NULL)
1637 AND (X_Cont_Attribute17 IS NULL)))
1638 AND ( (Recinfo.cont_attribute18 = X_Cont_Attribute18)
1639 OR ( (Recinfo.cont_attribute18 IS NULL)
1640 AND (X_Cont_Attribute18 IS NULL)))
1641 AND ( (Recinfo.cont_attribute19 = X_Cont_Attribute19)
1642 OR ( (Recinfo.cont_attribute19 IS NULL)
1643 AND (X_Cont_Attribute19 IS NULL)))
1644 AND ( (Recinfo.cont_attribute20 = X_Cont_Attribute20)
1645 OR ( (Recinfo.cont_attribute20 IS NULL)
1646 AND (X_Cont_Attribute20 IS NULL)))
1647 AND ( (Recinfo.cont_information_category = X_Cont_Information_Category)
1648 OR ( (Recinfo.cont_information_category IS NULL)
1649 AND (X_Cont_Information_Category IS NULL)))
1650 AND ( (Recinfo.cont_information1 = X_Cont_Information1)
1651 OR ( (Recinfo.cont_information1 IS NULL)
1652 AND (X_Cont_Information1 IS NULL)))
1653 AND ( (Recinfo.cont_information2 = X_Cont_Information2)
1654 OR ( (Recinfo.cont_information2 IS NULL)
1655 AND (X_Cont_Information2 IS NULL)))
1656 AND ( (Recinfo.cont_information3 = X_Cont_Information3)
1657 OR ( (Recinfo.cont_information3 IS NULL)
1658 AND (X_Cont_Information3 IS NULL)))
1659 AND ( (Recinfo.cont_information4 = X_Cont_Information4)
1660 OR ( (Recinfo.cont_information4 IS NULL)
1661 AND (X_Cont_Information4 IS NULL)))
1662 AND ( (Recinfo.cont_information5 = X_Cont_Information5)
1663 OR ( (Recinfo.cont_information5 IS NULL)
1664 AND (X_Cont_Information5 IS NULL)))
1665 AND ( (Recinfo.cont_information6 = X_Cont_Information6)
1666 OR ( (Recinfo.cont_information6 IS NULL)
1667 AND (X_Cont_Information6 IS NULL)))
1668 AND ( (Recinfo.cont_information7 = X_Cont_Information7)
1669 OR ( (Recinfo.cont_information7 IS NULL)
1670 AND (X_Cont_Information7 IS NULL)))
1671 AND ( (Recinfo.cont_information8 = X_Cont_Information8)
1672 OR ( (Recinfo.cont_information8 IS NULL)
1673 AND (X_Cont_Information8 IS NULL)))
1674 AND ( (Recinfo.cont_information9 = X_Cont_Information9)
1675 OR ( (Recinfo.cont_information9 IS NULL)
1676 AND (X_Cont_Information9 IS NULL)))
1677 AND ( (Recinfo.cont_information10 = X_Cont_Information10)
1678 OR ( (Recinfo.cont_information10 IS NULL)
1679 AND (X_Cont_Information10 IS NULL)))
1680 AND ( (Recinfo.cont_information11 = X_Cont_Information11)
1681 OR ( (Recinfo.cont_information11 IS NULL)
1682 AND (X_Cont_Information11 IS NULL)))
1683 AND ( (Recinfo.cont_information12 = X_Cont_Information12)
1684 OR ( (Recinfo.cont_information12 IS NULL)
1685 AND (X_Cont_Information12 IS NULL)))
1686 AND ( (Recinfo.cont_information13 = X_Cont_Information13)
1687 OR ( (Recinfo.cont_information13 IS NULL)
1688 AND (X_Cont_Information13 IS NULL)))
1689 AND ( (Recinfo.cont_information14 = X_Cont_Information14)
1690 OR ( (Recinfo.cont_information14 IS NULL)
1691 AND (X_Cont_Information14 IS NULL)))
1692 AND ( (Recinfo.cont_information15 = X_Cont_Information15)
1693 OR ( (Recinfo.cont_information15 IS NULL)
1694 AND (X_Cont_Information15 IS NULL)))
1695 AND ( (Recinfo.cont_information16 = X_Cont_Information16)
1696 OR ( (Recinfo.cont_information16 IS NULL)
1697 AND (X_Cont_Information16 IS NULL)))
1698 AND ( (Recinfo.cont_information17 = X_Cont_Information17)
1699 OR ( (Recinfo.cont_information17 IS NULL)
1700 AND (X_Cont_Information17 IS NULL)))
1701 AND ( (Recinfo.cont_information18 = X_Cont_Information18)
1702 OR ( (Recinfo.cont_information18 IS NULL)
1703 AND (X_Cont_Information18 IS NULL)))
1704 AND ( (Recinfo.cont_information19 = X_Cont_Information19)
1705 OR ( (Recinfo.cont_information19 IS NULL)
1706 AND (X_Cont_Information19 IS NULL)))
1707 AND ( (Recinfo.cont_information20 = X_Cont_Information20)
1708 OR ( (Recinfo.cont_information20 IS NULL)
1709 AND (X_Cont_Information20 IS NULL)))
1710
1711 ) THEN
1712 IF (
1713 ( (Recinfo.Bondholder_Flag = X_Bondholder_Flag)
1714 OR ( (Recinfo.Bondholder_Flag IS NULL)
1715 AND (X_Bondholder_Flag IS NULL)))
1716 AND ( (Recinfo.Third_Party_Pay_Flag = X_Third_Party_Pay_Flag)
1717 OR ( (Recinfo.Third_Party_Pay_Flag IS NULL)
1718 AND (X_Third_Party_Pay_Flag IS NULL)))
1719 AND ( (Recinfo.Date_Start = X_Date_Start)
1720 OR ( (Recinfo.Date_Start IS NULL)
1721 AND (X_Date_Start IS NULL)))
1722 AND ( (Recinfo.Start_Life_Reason_Id =X_Start_life_reason_id)
1723 OR ( (Recinfo.Start_life_reason_id IS NULL)
1724 AND (X_Start_Life_Reason_Id IS NULL)))
1725 AND ( (Recinfo.Date_End = X_date_end)
1726 OR ( (Recinfo.Date_End IS NULL)
1727 AND (X_Date_end IS NULL)))
1728 AND ((Recinfo.End_Life_reason_id = x_end_life_reason_id)
1729 OR ( (Recinfo.end_life_reason_id IS NULL)
1730 AND (X_end_life_reason_id IS NULL)))
1731 AND ( (Recinfo.Rltd_per_rsds_w_dsgntr_flag = X_rltd_per_rsds_w_dsgntr_flag)
1732 OR ( (Recinfo.rltd_per_rsds_w_dsgntr_flag IS NULL)
1733 AND (x_rltd_per_rsds_w_dsgntr_flag IS NULL)))
1734 AND ( (Recinfo.Personal_flag = X_Personal_flag)
1735 OR ( (Recinfo.personal_flag IS NULL)
1736 AND (x_personal_flag IS NULL)))
1737 AND ( (Recinfo.Sequence_number = X_Sequence_number)
1738 OR ( (Recinfo.sequence_number IS NULL)
1739 AND (x_sequence_number IS NULL)))
1740 AND ( (Recinfo.Dependent_flag = X_Dependent_flag)
1741 OR ( (Recinfo.dependent_flag IS NULL)
1742 AND (x_dependent_flag IS NULL)))
1743 AND ( (Recinfo.Beneficiary_flag = X_Beneficiary_flag)
1744 OR ( (Recinfo.beneficiary_flag IS NULL)
1745 AND (x_beneficiary_flag IS NULL)))
1746
1747
1748 )
1749 THEN
1750 RETURN;
1751 END IF;
1752 ELSE
1753 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1754 APP_EXCEPTION.RAISE_EXCEPTION;
1755 END IF;
1756 END Lock_Row;
1757
1758 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1759 X_Contact_Relationship_Id NUMBER,
1760 X_Business_Group_Id NUMBER,
1761 X_Person_Id NUMBER,
1762 X_Contact_Person_Id NUMBER,
1763 X_Contact_Type VARCHAR2,
1764 X_Comments VARCHAR2,
1765 X_Bondholder_Flag VARCHAR2,
1766 X_Third_Party_Pay_Flag VARCHAR2,
1767 X_Primary_Contact_Flag VARCHAR2,
1768 X_Cont_Attribute_Category VARCHAR2,
1769 X_Cont_Attribute1 VARCHAR2,
1770 X_Cont_Attribute2 VARCHAR2,
1771 X_Cont_Attribute3 VARCHAR2,
1772 X_Cont_Attribute4 VARCHAR2,
1773 X_Cont_Attribute5 VARCHAR2,
1774 X_Cont_Attribute6 VARCHAR2,
1775 X_Cont_Attribute7 VARCHAR2,
1776 X_Cont_Attribute8 VARCHAR2,
1777 X_Cont_Attribute9 VARCHAR2,
1778 X_Cont_Attribute10 VARCHAR2,
1779 X_Cont_Attribute11 VARCHAR2,
1780 X_Cont_Attribute12 VARCHAR2,
1781 X_Cont_Attribute13 VARCHAR2,
1782 X_Cont_Attribute14 VARCHAR2,
1783 X_Cont_Attribute15 VARCHAR2,
1784 X_Cont_Attribute16 VARCHAR2,
1785 X_Cont_Attribute17 VARCHAR2,
1786 X_Cont_Attribute18 VARCHAR2,
1787 X_Cont_Attribute19 VARCHAR2,
1788 X_Cont_Attribute20 VARCHAR2,
1789 X_Cont_Information_Category VARCHAR2,
1790 X_Cont_Information1 VARCHAR2,
1791 X_Cont_Information2 VARCHAR2,
1792 X_Cont_Information3 VARCHAR2,
1793 X_Cont_Information4 VARCHAR2,
1794 X_Cont_Information5 VARCHAR2,
1795 X_Cont_Information6 VARCHAR2,
1796 X_Cont_Information7 VARCHAR2,
1797 X_Cont_Information8 VARCHAR2,
1798 X_Cont_Information9 VARCHAR2,
1799 X_Cont_Information10 VARCHAR2,
1800 X_Cont_Information11 VARCHAR2,
1801 X_Cont_Information12 VARCHAR2,
1802 X_Cont_Information13 VARCHAR2,
1803 X_Cont_Information14 VARCHAR2,
1804 X_Cont_Information15 VARCHAR2,
1805 X_Cont_Information16 VARCHAR2,
1806 X_Cont_Information17 VARCHAR2,
1807 X_Cont_Information18 VARCHAR2,
1808 X_Cont_Information19 VARCHAR2,
1809 X_Cont_Information20 VARCHAR2,
1810 X_Session_Date DATE,
1811 X_Date_Start DATE,
1812 X_Start_Life_Reason_Id VARCHAR2,
1813 X_Date_End DATE,
1814 X_End_Life_Reason_Id VARCHAR2,
1815 X_Rltd_Per_Rsds_W_Dsgntr_Flag VARCHAR2,
1816 X_Personal_Flag VARCHAR2,
1817 X_Sequence_Number NUMBER,
1818 X_Dependent_Flag VARCHAR2,
1819 X_Beneficiary_Flag VARCHAR2
1820
1821 ) IS
1822 ---------------------------------------------
1823 -- Local Declarations
1824 --
1825 l_exists VARCHAR2(1) := 'N';
1826 l_mirror_update VARCHAR2(1) := 'N';
1827 l_mirror_rowid VARCHAR2(30);
1828 l_mirror_contact_type VARCHAR2(1);
1829 l_others_exist VARCHAR2(1) := 'N';
1830 l_rel_changed_cobra_exists VARCHAR2(1) := 'N';
1831 l_old_mirror_contact_type VARCHAR2(30);
1832 l_contact_type VARCHAR2(30);
1833 l_date_start DATE;
1834 --
1835 --fix for bug 4867048.
1836 CURSOR Check_Mirror_Update IS
1837 SELECT 'Y'
1838 FROM per_contact_relationships
1839 WHERE ROWID = CHARTOROWID(X_Rowid)
1840 AND( nvl(date_start,hr_api.g_date) <> nvl(X_date_start,hr_api.g_date)
1841 OR nvl(start_life_reason_id,hr_api.g_number) <> nvl(X_start_life_reason_Id,hr_api.g_number)
1842 OR nvl(date_end,hr_api.g_date) <> nvl(X_date_end,hr_api.g_date)
1843 OR nvl(end_life_reason_id,hr_api.g_number) <> nvl(X_end_life_reason_id,hr_api.g_number)
1844 OR nvl(rltd_per_rsds_w_dsgntr_flag,hr_api.g_varchar2) <> nvl(X_rltd_per_rsds_w_dsgntr_flag,hr_api.g_varchar2)
1845 OR nvl(personal_flag,hr_api.g_varchar2) <> nvl(X_personal_flag,hr_api.g_varchar2));
1846 --
1847 CURSOR Check_Mirror IS
1848 SELECT ROWIDTOCHAR(ROWID)
1849 FROM per_contact_relationships
1850 WHERE contact_person_id = X_Person_Id
1851 AND person_id = X_Contact_Person_Id
1852 AND contact_type = l_old_mirror_contact_type
1853 AND nvl(date_start,hr_api.g_date) =nvl(l_date_start,hr_api.g_date)--fix for bug 4867048.
1854 FOR UPDATE OF Contact_Type;
1855 --
1856 CURSOR Check_Relationship_Changed IS
1857 SELECT 'Y'
1858 FROM per_contact_relationships
1859 WHERE contact_relationship_id = X_Contact_Relationship_Id
1860 AND contact_type <> X_Contact_Type
1861 AND EXISTS
1862 (SELECT NULL
1863 FROM per_cobra_cov_enrollments
1864 WHERE CONTACT_RELATIONSHIP_ID = X_Contact_Relationship_Id);
1865 --
1866 --
1867 -- Start of Fix for WWBUG 1408379
1868 --
1869 CURSOR c1 IS
1870 SELECT *
1871 FROM per_contact_relationships
1872 WHERE ROWID = CHARTOROWID(l_mirror_rowid);
1873 --
1874 CURSOR c2 IS
1875 SELECT *
1876 FROM per_contact_relationships
1877 WHERE ROWID = x_rowid;
1878 --
1879 l_c1 c1%ROWTYPE;
1880 l_rows_found BOOLEAN := FALSE;
1881 l_old ben_con_ler.g_con_ler_rec;
1882 l_new ben_con_ler.g_con_ler_rec;
1883 --
1884 -- End of Fix for WWBUG 1408379
1885 --
1886 ---------------------------------------------
1887 -- Main Procedure
1888 --
1889 BEGIN
1890 --
1891 SELECT date_start, contact_type
1892 INTO l_date_start, l_contact_type
1893 FROM per_contact_relationships
1894 WHERE ROWID = CHARTOROWID(X_rowid);
1895 --
1896 -- Find the mirror_contact_type before update to locate
1897 -- the correct mirror relationship for update.
1898 --
1899 l_old_mirror_contact_type := Get_Mirror_Contact_Type(l_contact_type);
1900 --
1901 -- Check to ensure that there is only one primary contact for the person
1902 --
1903 IF X_Primary_Contact_Flag = 'Y' AND
1904 check_primary_contact(p_rowid => X_Rowid
1905 ,p_person_id => X_Person_Id
1909 hr_utility.raise_error;
1906 ,p_date_start => X_date_start
1907 ,p_date_end => X_date_end) THEN
1908 hr_utility.set_message(801,'PER_7125_EMP_CON_PRIMARY');
1910 END IF;
1911 --
1912 -- Call to sequence number validation
1913 --
1914 chk_sequence_number(p_contact_relationship_id => X_contact_relationship_id
1915 ,p_sequence_number => X_sequence_number
1916 ,p_contact_person_id => X_contact_person_id
1917 ,p_person_id => X_person_id
1918 );
1919 --
1920 -- Check to ensure that only one relationship of the same type exists
1921 -- between the same two people at the same time (Bug 1239406)
1922 --
1923 IF time_validation(p_contact_type => X_contact_type
1924 ,p_person_id => X_person_id
1925 ,p_contact_person_id => X_contact_person_id
1926 ,p_contact_relationship_id => X_contact_relationship_id
1927 ,p_date_start => X_date_start
1928 ,p_date_end => X_date_end
1929 ) THEN
1930 hr_utility.set_message(800,'PER_6996_REL_CURR_EXISTS');
1931 hr_utility.raise_error;
1932 END IF;
1933 --
1934 /*
1935 -- Bug.3207660
1936 -- This validation guarantees all PER_CONTACT_EXTRA_INFO_F records
1937 -- are within CTR.DATE_START and CTR.DATE_END date range.
1938 -- Now the validation is relaxed(removed).
1939 -- PER_CONTACT_EXTRA_INFO_F records can stay outside the range of CTR.
1940 -- Note the validation that PER_CONTACT_EXTRA_INFO_F records must be
1941 -- within the date range of PER_ALL_PEOPLE_F is still alive.
1942 chk_dates(
1943 x_contact_relationship_id => x_contact_relationship_id,
1944 x_date_start => x_date_start,
1945 x_date_end => x_date_end);
1946 */
1947 --
1948 --
1949 -- If the Contact_Type has change then do not attempt to update the
1950 -- mirror image. If contact_type is unchanged then update the mirror.
1951 --
1952 OPEN Check_Mirror_Update;
1953 FETCH Check_Mirror_Update INTO l_mirror_update;
1954 CLOSE Check_Mirror_Update;
1955 --
1956 IF l_mirror_update = 'Y' THEN
1957 OPEN Check_Mirror;
1958 FETCH Check_Mirror INTO l_mirror_rowid;
1959 IF Check_Mirror%FOUND THEN
1960 --
1961 -- Start of Fix for WWBUG 1408379
1962 --
1963 OPEN c1;
1964 --
1965 FETCH c1 INTO l_c1;
1966 IF c1%FOUND THEN
1967 --
1968 l_rows_found := TRUE;
1969 --
1970 END IF;
1971 --
1972 CLOSE c1;
1973 --
1974 UPDATE per_contact_relationships
1975 SET
1976 date_start = X_date_start
1977 ,start_life_reason_id = X_start_life_reason_id
1978 ,date_end = X_date_end
1979 ,end_life_reason_id = X_end_life_reason_id
1980 ,rltd_per_rsds_w_dsgntr_flag = X_rltd_per_rsds_w_dsgntr_flag
1981 ,personal_flag = X_personal_flag
1982 WHERE ROWID = CHARTOROWID(l_mirror_rowid);
1983 --
1984 IF l_rows_found THEN
1985 --
1986 l_old.person_id := l_c1.person_id;
1987 l_old.contact_person_id := l_c1.contact_person_id;
1988 l_old.business_group_id := l_c1.business_group_id;
1989 l_old.date_start := l_c1.date_start;
1990 l_old.date_end := l_c1.date_end;
1991 l_old.contact_type := l_c1.contact_type;
1992 l_old.personal_flag := l_c1.personal_flag;
1993 l_old.start_life_reason_id := l_c1.start_life_reason_id;
1994 l_old.end_life_reason_id := l_c1.end_life_reason_id;
1995 l_old.rltd_per_rsds_w_dsgntr_flag := l_c1.rltd_per_rsds_w_dsgntr_flag;
1996 l_old.contact_relationship_id := l_c1.contact_relationship_id;
1997 -- Bug 1772037 fix
1998 l_old.cont_attribute1 := l_c1.cont_attribute1 ;
1999 l_old.cont_attribute2 := l_c1.cont_attribute2 ;
2000 l_old.cont_attribute3 := l_c1.cont_attribute3 ;
2001 l_old.cont_attribute4 := l_c1.cont_attribute4 ;
2002 l_old.cont_attribute5 := l_c1.cont_attribute5 ;
2003 l_old.cont_attribute6 := l_c1.cont_attribute6 ;
2004 l_old.cont_attribute7 := l_c1.cont_attribute7 ;
2005 l_old.cont_attribute8 := l_c1.cont_attribute8 ;
2006 l_old.cont_attribute9 := l_c1.cont_attribute9 ;
2007 l_old.cont_attribute10 := l_c1.cont_attribute10 ;
2008 l_old.cont_attribute11 := l_c1.cont_attribute11 ;
2009 l_old.cont_attribute12 := l_c1.cont_attribute12 ;
2010 l_old.cont_attribute13 := l_c1.cont_attribute13 ;
2011 l_old.cont_attribute14 := l_c1.cont_attribute14 ;
2012 l_old.cont_attribute15 := l_c1.cont_attribute15 ;
2013 l_old.cont_attribute16 := l_c1.cont_attribute16 ;
2014 l_old.cont_attribute17 := l_c1.cont_attribute17 ;
2015 l_old.cont_attribute18 := l_c1.cont_attribute18 ;
2016 l_old.cont_attribute19 := l_c1.cont_attribute19 ;
2017 l_old.cont_attribute20 := l_c1.cont_attribute20 ;
2018 -- End fix 1772037
2019 l_new.person_id := l_c1.person_id;
2020 l_new.contact_person_id := l_c1.contact_person_id;
2021 l_new.business_group_id := l_c1.business_group_id;
2022 l_new.date_start := x_date_start;
2023 l_new.date_end := x_date_end;
2027 l_new.end_life_reason_id := x_end_life_reason_id;
2024 l_new.contact_type := l_c1.contact_type;
2025 l_new.personal_flag := x_personal_flag;
2026 l_new.start_life_reason_id := x_start_life_reason_id;
2028 l_new.rltd_per_rsds_w_dsgntr_flag := x_rltd_per_rsds_w_dsgntr_flag;
2029 l_new.contact_relationship_id := l_c1.contact_relationship_id;
2030 -- Bug 1772037 fix
2031 l_new.cont_attribute1 := x_cont_attribute1 ;
2032 l_new.cont_attribute2 := x_cont_attribute2 ;
2033 l_new.cont_attribute3 := x_cont_attribute3 ;
2034 l_new.cont_attribute4 := x_cont_attribute4 ;
2035 l_new.cont_attribute5 := x_cont_attribute5 ;
2036 l_new.cont_attribute6 := x_cont_attribute6 ;
2037 l_new.cont_attribute7 := x_cont_attribute7 ;
2038 l_new.cont_attribute8 := x_cont_attribute8 ;
2039 l_new.cont_attribute9 := x_cont_attribute9 ;
2040 l_new.cont_attribute10 := x_cont_attribute10 ;
2041 l_new.cont_attribute11 := x_cont_attribute11 ;
2042 l_new.cont_attribute12 := x_cont_attribute12 ;
2043 l_new.cont_attribute13 := x_cont_attribute13 ;
2044 l_new.cont_attribute14 := x_cont_attribute14 ;
2045 l_new.cont_attribute15 := x_cont_attribute15 ;
2046 l_new.cont_attribute16 := x_cont_attribute16 ;
2047 l_new.cont_attribute17 := x_cont_attribute17 ;
2048 l_new.cont_attribute18 := x_cont_attribute18 ;
2049 l_new.cont_attribute19 := x_cont_attribute19 ;
2050 l_new.cont_attribute20 := x_cont_attribute20 ;
2051 -- End fix 1772037
2052 --
2053 ben_con_ler.ler_chk(p_old => l_old,
2054 p_new => l_new,
2055 p_effective_date => NVL(x_date_start,SYSDATE));
2056 --
2057 END IF;
2058 --
2059 END IF;
2060 CLOSE Check_Mirror;
2061 END IF;
2062 --
2063 -- Update the base Contact_Relationship record
2064 --
2065 --
2066 -- Start of Fix for WWBUG 1408379
2067 --
2068 OPEN c2;
2069 --
2070 FETCH c2 INTO l_c1;
2071 --
2072 IF c2%FOUND THEN
2073 --
2074 l_rows_found := TRUE;
2075 --
2076 ELSE
2077 --
2078 l_rows_found := FALSE;
2079 --
2080 END IF;
2081 --
2082 CLOSE c2;
2083 --
2084 UPDATE PER_CONTACT_RELATIONSHIPS
2085 SET
2086 contact_relationship_id = X_Contact_Relationship_Id,
2087 business_group_id = X_Business_Group_Id,
2088 person_id = X_Person_Id,
2089 contact_person_id = X_Contact_Person_Id,
2090 contact_type = X_Contact_Type,
2091 comments = X_Comments,
2092 Bondholder_Flag = X_Bondholder_Flag,
2093 Third_Party_Pay_Flag = X_Third_Party_Pay_Flag,
2094 primary_contact_flag = X_Primary_Contact_Flag,
2095 cont_attribute_category = X_Cont_Attribute_Category,
2096 cont_attribute1 = X_Cont_Attribute1,
2097 cont_attribute2 = X_Cont_Attribute2,
2098 cont_attribute3 = X_Cont_Attribute3,
2099 cont_attribute4 = X_Cont_Attribute4,
2100 cont_attribute5 = X_Cont_Attribute5,
2101 cont_attribute6 = X_Cont_Attribute6,
2102 cont_attribute7 = X_Cont_Attribute7,
2103 cont_attribute8 = X_Cont_Attribute8,
2104 cont_attribute9 = X_Cont_Attribute9,
2105 cont_attribute10 = X_Cont_Attribute10,
2106 cont_attribute11 = X_Cont_Attribute11,
2107 cont_attribute12 = X_Cont_Attribute12,
2108 cont_attribute13 = X_Cont_Attribute13,
2109 cont_attribute14 = X_Cont_Attribute14,
2110 cont_attribute15 = X_Cont_Attribute15,
2111 cont_attribute16 = X_Cont_Attribute16,
2112 cont_attribute17 = X_Cont_Attribute17,
2113 cont_attribute18 = X_Cont_Attribute18,
2114 cont_attribute19 = X_Cont_Attribute19,
2115 cont_attribute20 = X_Cont_Attribute20,
2116 cont_information_category = X_Cont_Information_Category,
2117 cont_information1 = X_Cont_Information1,
2118 cont_information2 = X_Cont_Information2,
2119 cont_information3 = X_Cont_Information3,
2120 cont_information4 = X_Cont_Information4,
2121 cont_information5 = X_Cont_Information5,
2122 cont_information6 = X_Cont_Information6,
2123 cont_information7 = X_Cont_Information7,
2124 cont_information8 = X_Cont_Information8,
2125 cont_information9 = X_Cont_Information9,
2129 cont_information13 = X_Cont_Information13,
2126 cont_information10 = X_Cont_Information10,
2127 cont_information11 = X_Cont_Information11,
2128 cont_information12 = X_Cont_Information12,
2130 cont_information14 = X_Cont_Information14,
2131 cont_information15 = X_Cont_Information15,
2132 cont_information16 = X_Cont_Information16,
2133 cont_information17 = X_Cont_Information17,
2134 cont_information18 = X_Cont_Information18,
2135 cont_information19 = X_Cont_Information19,
2136 cont_information20 = X_Cont_Information20,
2137 date_start = X_date_start,
2138 start_life_reason_id = X_start_life_reason_id,
2139 date_end = X_date_end,
2140 end_life_reason_id = X_end_life_reason_id,
2141 rltd_per_rsds_w_dsgntr_flag = X_rltd_per_rsds_w_dsgntr_flag,
2142 personal_flag = X_personal_flag,
2143 sequence_number = X_sequence_number,
2144 dependent_flag = X_dependent_flag,
2145 beneficiary_flag = X_beneficiary_flag
2146
2147 WHERE ROWID = X_rowid;
2148
2149 IF (SQL%NOTFOUND) THEN
2150 RAISE NO_DATA_FOUND;
2151 END IF;
2152 --
2153 IF l_rows_found THEN
2154 --
2155 l_old.person_id := l_c1.person_id;
2156 l_old.contact_person_id := l_c1.contact_person_id;
2157 l_old.business_group_id := l_c1.business_group_id;
2158 l_old.date_start := l_c1.date_start;
2159 l_old.date_end := l_c1.date_end;
2160 l_old.contact_type := l_c1.contact_type;
2161 l_old.personal_flag := l_c1.personal_flag;
2162 l_old.start_life_reason_id := l_c1.start_life_reason_id;
2163 l_old.end_life_reason_id := l_c1.end_life_reason_id;
2164 l_old.rltd_per_rsds_w_dsgntr_flag := l_c1.rltd_per_rsds_w_dsgntr_flag;
2165 l_old.contact_relationship_id := l_c1.contact_relationship_id;
2166 -- Bug 1772037 fix
2167 l_old.cont_attribute1 := l_c1.cont_attribute1 ;
2168 l_old.cont_attribute2 := l_c1.cont_attribute2 ;
2169 l_old.cont_attribute3 := l_c1.cont_attribute3 ;
2170 l_old.cont_attribute4 := l_c1.cont_attribute4 ;
2171 l_old.cont_attribute5 := l_c1.cont_attribute5 ;
2172 l_old.cont_attribute6 := l_c1.cont_attribute6 ;
2173 l_old.cont_attribute7 := l_c1.cont_attribute7 ;
2174 l_old.cont_attribute8 := l_c1.cont_attribute8 ;
2175 l_old.cont_attribute9 := l_c1.cont_attribute9 ;
2176 l_old.cont_attribute10 := l_c1.cont_attribute10 ;
2177 l_old.cont_attribute11 := l_c1.cont_attribute11 ;
2178 l_old.cont_attribute12 := l_c1.cont_attribute12 ;
2179 l_old.cont_attribute13 := l_c1.cont_attribute13 ;
2180 l_old.cont_attribute14 := l_c1.cont_attribute14 ;
2181 l_old.cont_attribute15 := l_c1.cont_attribute15 ;
2182 l_old.cont_attribute16 := l_c1.cont_attribute16 ;
2183 l_old.cont_attribute17 := l_c1.cont_attribute17 ;
2184 l_old.cont_attribute18 := l_c1.cont_attribute18 ;
2185 l_old.cont_attribute19 := l_c1.cont_attribute19 ;
2186 l_old.cont_attribute20 := l_c1.cont_attribute20 ;
2187 -- End fix 1772037
2188 l_new.person_id := x_person_id;
2189 l_new.contact_person_id := x_contact_person_id;
2190 l_new.business_group_id := x_business_group_id;
2191 l_new.date_start := x_date_start;
2192 l_new.date_end := x_date_end;
2193 l_new.contact_type := x_contact_type;
2194 l_new.personal_flag := x_personal_flag;
2195 l_new.start_life_reason_id := x_start_life_reason_id;
2196 l_new.end_life_reason_id := x_end_life_reason_id;
2197 l_new.rltd_per_rsds_w_dsgntr_flag := x_rltd_per_rsds_w_dsgntr_flag;
2198 l_new.contact_relationship_id := x_contact_relationship_id;
2199 -- Bug 1772037 fix
2200 l_new.cont_attribute1 := x_cont_attribute1 ;
2201 l_new.cont_attribute2 := x_cont_attribute2 ;
2202 l_new.cont_attribute3 := x_cont_attribute3 ;
2203 l_new.cont_attribute4 := x_cont_attribute4 ;
2204 l_new.cont_attribute5 := x_cont_attribute5 ;
2205 l_new.cont_attribute6 := x_cont_attribute6 ;
2206 l_new.cont_attribute7 := x_cont_attribute7 ;
2207 l_new.cont_attribute8 := x_cont_attribute8 ;
2208 l_new.cont_attribute9 := x_cont_attribute9 ;
2209 l_new.cont_attribute10 := x_cont_attribute10 ;
2210 l_new.cont_attribute11 := x_cont_attribute11 ;
2211 l_new.cont_attribute12 := x_cont_attribute12 ;
2212 l_new.cont_attribute13 := x_cont_attribute13 ;
2213 l_new.cont_attribute14 := x_cont_attribute14 ;
2214 l_new.cont_attribute15 := x_cont_attribute15 ;
2215 l_new.cont_attribute16 := x_cont_attribute16 ;
2216 l_new.cont_attribute17 := x_cont_attribute17 ;
2217 l_new.cont_attribute18 := x_cont_attribute18 ;
2218 l_new.cont_attribute19 := x_cont_attribute19 ;
2219 l_new.cont_attribute20 := x_cont_attribute20 ;
2220 -- End fix 1772037
2221 --
2222 ben_con_ler.ler_chk(p_old => l_old,
2223 p_new => l_new,
2224 p_effective_date => NVL(x_date_start,SYSDATE));
2225 --
2226 END IF;
2227 --
2228 -- 1766066: added call for contact start date enh.
2229 --
2230 if (X_Date_Start is not null
2231 and X_Date_Start < l_date_start) then
2235 end if;
2232 per_people12_pkg.maintain_coverage(p_person_id => X_Contact_Person_Id
2233 ,p_type => 'CONT'
2234 );
2236 -- 1766066 end.
2237 --
2238 END Update_Row;
2239
2240 PROCEDURE Delete_Row(X_Rowid VARCHAR2
2241 ,X_Person_Id NUMBER
2242 ,X_Contact_Person_Id NUMBER
2243 ,X_Contact_Relationship_Id NUMBER
2244 ,X_Date_Start DATE
2245 ,X_Contact_Type VARCHAR2) IS
2246 l_mirror_type VARCHAR2(1);
2247 --
2248 BEGIN
2249 -- Bug 2017198: have switched the order of deletes alongside change in peper-2t.pkb
2250 -- for function multiple_contacts
2251
2252 -- Delete the base contact relationship
2253 --
2254 DELETE FROM PER_CONTACT_RELATIONSHIPS
2255 WHERE ROWID = X_Rowid;
2256
2257 IF (SQL%NOTFOUND) THEN
2258 RAISE NO_DATA_FOUND;
2259 END IF;
2260
2261 -- Delete the Mirror Relationship
2262 --
2263 IF X_contact_type IN ('C','P','S') THEN
2264 l_mirror_type := Get_Mirror_Contact_Type(p_contact_type =>X_Contact_Type);
2265 --
2266 DELETE FROM PER_CONTACT_RELATIONSHIPS
2267 WHERE person_id = X_Contact_Person_Id
2268 AND contact_person_id = X_Person_Id
2269 AND contact_type = l_mirror_type
2270 AND date_start = X_date_start;
2271
2272 ELSE NULL;
2273 END IF;
2274
2275 -- if the person is a Contact Only and
2276 -- there exists Person Analyses Records and
2277 -- their Person Type is 'OTHER' then
2278 -- delete any comments for the person
2279 -- delete the person
2280 --
2281 -- bug fix 4197342
2282 -- condition added to check whether the person is an irec candidate.
2283
2284 IF hr_contact_relationships.contact_only(X_Contact_Person_Id) = 'Y'
2285 AND hr_contact_relationships.multiple_contacts(X_Contact_Person_Id) = 'N'
2286 AND NOT irc_candidate_test.is_person_a_candidate(X_Contact_Person_Id)
2287 THEN
2288 --
2289 -- Check foreign key tables
2290 --
2291 Delete_Validation(X_Contact_Person_Id
2292 ,X_Contact_Relationship_Id);
2293 --
2294 DELETE FROM per_contact_extra_info_f
2295 WHERE contact_relationship_id = x_contact_relationship_id;
2296 --
2297 -- added for 1931258
2298 ben_person_delete.perform_ri_check(X_Contact_Person_Id);
2299
2300 DELETE FROM hr_comments h
2301 WHERE h.comment_id IN
2302 (SELECT comment_id
2303 FROM PER_ALL_PEOPLE_F paf
2304 WHERE paf.person_id = X_Contact_Person_Id);
2305
2306 -- added for 1931258
2307 ben_person_delete.delete_ben_rows(X_Contact_Person_Id);
2308
2309 DELETE per_all_people_f
2310 WHERE person_id = X_Contact_Person_Id;
2311
2312 END IF;
2313 END Delete_Row;
2314
2315 PROCEDURE Delete_Validation(X_Person_Id NUMBER
2316 ,X_Contact_Relationship_Id NUMBER) IS
2317
2318 l_address_exists VARCHAR2(1) := 'N';
2319 l_cobra_exists VARCHAR2(1) := 'N';
2320 l_dependent_exists VARCHAR2(1) := 'N';
2321 l_beneficiary_exists VARCHAR2(1) := 'N';
2322 l_pa_exists VARCHAR2(1) := 'N';
2323 --
2324 CURSOR Personal_Analyses IS
2325 SELECT 'Y'
2326 FROM per_person_analyses
2327 WHERE person_id = X_Person_Id;
2328 --
2329 CURSOR Address IS
2330 SELECT 'Y'
2331 FROM per_addresses
2332 WHERE person_id = X_Person_Id;
2333 --
2334 CURSOR COBRA IS
2335 SELECT 'Y'
2336 FROM per_cobra_cov_enrollments
2337 WHERE CONTACT_RELATIONSHIP_ID = X_Contact_Relationship_Id;
2338 --
2339 --Changes for Bug no 5415267 starts here
2340 CURSOR Benificiary IS
2341 SELECT 'Y'
2342 FROM BEN_BENEFICIARIES_X
2343 WHERE source_id = X_Person_Id;
2344 --Changes for Bug no 5415267 ends here
2345 BEGIN
2346 --
2347 --Changes for Bug no 5415267 starts here
2348
2349 OPEN Benificiary;
2350 FETCH Benificiary INTO l_beneficiary_exists;
2351 CLOSE Benificiary;
2352 --
2353 IF l_beneficiary_exists = 'Y' THEN
2354 hr_utility.set_message(801,'HR_7993_CONTACT_BENEFIT_EXISTS');
2355 hr_utility.raise_error;
2356 END IF;
2357 --Changes for Bug no 5415267 ends here
2358
2359 -- if the person is a Contact Only then
2360 -- do foreign key checks ie. address
2361 --
2362 -- Bug 1295442 Added check for multiple contacts
2363 --
2364 IF hr_contact_relationships.contact_only(X_Person_Id) = 'Y'
2365 AND hr_contact_relationships.multiple_contacts(X_Person_Id) = 'N'
2366 THEN
2367
2368 OPEN Personal_Analyses;
2369 FETCH Personal_Analyses INTO l_pa_exists;
2370 CLOSE Personal_Analyses;
2371 --
2372 IF l_pa_exists = 'Y' THEN
2373 hr_utility.set_message(801,'HR_51600_EMP_SIT_EXIST');
2374 hr_utility.raise_error;
2375 END IF;
2376 --
2377 OPEN Address;
2378 FETCH Address INTO l_address_exists;
2379 CLOSE Address;
2380 --
2381 IF l_address_exists = 'Y' THEN
2382 hr_utility.set_message(801,'PER_7101_EMP_ENTER_DEL_ADDR');
2383 hr_utility.raise_error;
2384 END IF;
2385 --
2386
2387 OPEN COBRA;
2391 IF l_cobra_exists = 'Y' THEN
2388 FETCH COBRA INTO l_cobra_exists;
2389 CLOSE COBRA;
2390 --
2392 hr_utility.set_message(801,'HR_6975_CONTACT_COBRA_EXISTS');
2393 hr_utility.raise_error;
2394 END IF;
2395 --
2396 -- Check to see if Personal Payment Method exists
2397 -- where contact is the Payee.
2398 --
2399 hr_contact_relationships.check_ppm(p_contact_id => X_Person_Id
2400 ,p_contact_relationship_id => X_Contact_Relationship_Id
2401 ,p_mode => 'D');
2402 END IF;
2403 END;
2404 --
2405 PROCEDURE chk_sequence_number(p_contact_relationship_id IN NUMBER,
2406 p_sequence_number IN NUMBER,
2407 p_contact_person_id IN NUMBER,
2408 p_person_id IN NUMBER) IS
2409 --
2410 l_sequence_number NUMBER;
2411 l_sequence_other NUMBER;
2412 l_old_seq_number NUMBER;
2413 --
2414 CURSOR csr_seq IS
2415 SELECT sequence_number
2416 FROM per_contact_relationships con
2417 WHERE con.person_id = p_person_id
2418 AND con.contact_person_id = p_contact_person_id
2419 AND con.sequence_number <> p_sequence_number;
2420 --
2421 CURSOR csr_old_seq IS
2422 SELECT sequence_number
2423 FROM per_contact_relationships con
2424 WHERE con.person_id = p_person_id
2425 AND con.contact_person_id = p_contact_person_id
2426 AND con.contact_relationship_id = p_contact_relationship_id;
2427 --
2428 --modified the cursor to add additional conditions for checking for end_date
2429 CURSOR csr_seq_others IS
2430 SELECT sequence_number
2431 FROM per_contact_relationships con
2432 WHERE con.person_id = p_person_id
2433 AND con.contact_person_id <> p_contact_person_id
2434 AND con.sequence_number = p_sequence_number
2435 AND con.date_end <=to_date(SYSDATE,'dd/mm/yyyy');
2436 --
2437 BEGIN
2438 --
2439 IF (p_contact_relationship_id IS NOT NULL) THEN
2440 OPEN csr_old_seq;
2441 FETCH csr_old_seq INTO l_old_seq_number;
2442 CLOSE csr_old_seq;
2443 END IF;
2444
2445 IF p_sequence_number IS NOT NULL AND
2446 p_contact_person_id IS NOT NULL THEN
2447 IF l_old_seq_number IS NULL THEN
2448 OPEN csr_seq;
2449 FETCH csr_seq INTO l_sequence_number;
2450 IF csr_seq%FOUND THEN
2451 CLOSE csr_seq;
2452 hr_utility.set_message('800','PER_52509_USE_SEQ_NO');
2453 hr_utility.raise_error;
2454 ELSE
2455 OPEN csr_seq_others;
2456 FETCH csr_seq_others INTO l_sequence_other;
2457 IF csr_seq_others%FOUND THEN
2458 CLOSE csr_seq_others;
2459 hr_utility.set_message('800','PER_52510_DIFF_SEQ_NO');
2460 hr_utility.raise_error;
2461 END IF;
2462 END IF;
2463 ELSIF p_sequence_number <> l_old_seq_number THEN
2464 hr_utility.set_message('800','PER_52511_SEQ_NO_UPD');
2465 hr_utility.raise_error;
2466 END IF;
2467 ELSIF l_old_seq_number IS NOT NULL THEN
2468 hr_utility.set_message('800','PER_52511_SEQ_NO_UPD');
2469 hr_utility.raise_error;
2470 END IF;
2471 END chk_sequence_number;
2472 --
2473 -- Update Contact Person details
2474 --
2475 PROCEDURE Update_Contact(X_Business_Group_Id NUMBER,
2476 X_Person_Id NUMBER,
2477 X_Contact_Person_Id NUMBER,
2478 X_Session_Date DATE,
2479 X_Person_Type_Id NUMBER,
2480 X_Last_Name VARCHAR2,
2481 X_Comment_Id NUMBER,
2482 X_Date_Of_Birth DATE,
2483 x_date_of_death DATE,
2484 X_First_Name VARCHAR2,
2485 X_Middle_Names VARCHAR2,
2486 X_Sex VARCHAR2,
2487 X_Title VARCHAR2,
2488 X_PRE_NAME_ADJUNCT VARCHAR2,
2489 X_SUFFIX VARCHAR2,
2490 X_Title_Desc VARCHAR2,
2491 X_National_Identifier VARCHAR2,
2492 X_Attribute_Category VARCHAR2,
2493 X_Attribute1 VARCHAR2,
2494 X_Attribute2 VARCHAR2,
2495 X_Attribute3 VARCHAR2,
2496 X_Attribute4 VARCHAR2,
2497 X_Attribute5 VARCHAR2,
2498 X_Attribute6 VARCHAR2,
2499 X_Attribute7 VARCHAR2,
2500 X_Attribute8 VARCHAR2,
2501 X_Attribute9 VARCHAR2,
2502 X_Attribute10 VARCHAR2,
2503 X_Attribute11 VARCHAR2,
2504 X_Attribute12 VARCHAR2,
2505 X_Attribute13 VARCHAR2,
2506 X_Attribute14 VARCHAR2,
2507 X_Attribute15 VARCHAR2,
2508 X_Attribute16 VARCHAR2,
2509 X_Attribute17 VARCHAR2,
2510 X_Attribute18 VARCHAR2,
2511 X_Attribute19 VARCHAR2,
2512 X_Attribute20 VARCHAR2,
2513 X_Attribute21 VARCHAR2,
2514 X_Attribute22 VARCHAR2,
2515 X_Attribute23 VARCHAR2,
2516 X_Attribute24 VARCHAR2,
2517 X_Attribute25 VARCHAR2,
2518 X_Attribute26 VARCHAR2,
2519 X_Attribute27 VARCHAR2,
2520 X_Attribute28 VARCHAR2,
2521 X_Attribute29 VARCHAR2,
2522 X_Attribute30 VARCHAR2,
2523 X_Contact_Only VARCHAR2,
2524 X_Reentry_flag IN OUT NOCOPY NUMBER,
2525 X_Per_Information_category VARCHAR2,
2526 X_Per_Information1 VARCHAR2,
2527 X_Per_Information2 VARCHAR2,
2528 X_Per_Information3 VARCHAR2,
2529 X_Per_Information4 VARCHAR2,
2530 X_Per_Information5 VARCHAR2,
2531 X_Per_Information6 VARCHAR2,
2532 X_Per_Information7 VARCHAR2,
2533 X_Per_Information8 VARCHAR2,
2534 X_Per_Information9 VARCHAR2,
2535 X_Per_Information10 VARCHAR2,
2536 X_Per_Information11 VARCHAR2,
2537 X_Per_Information12 VARCHAR2,
2538 X_Per_Information13 VARCHAR2,
2539 X_Per_Information14 VARCHAR2,
2540 X_Per_Information15 VARCHAR2,
2541 X_Per_Information16 VARCHAR2,
2542 X_Per_Information17 VARCHAR2,
2543 X_Per_Information18 VARCHAR2,
2544 X_Per_Information19 VARCHAR2,
2545 X_Per_Information20 VARCHAR2,
2546 X_Per_Information21 VARCHAR2,
2547 X_Per_Information22 VARCHAR2,
2548 X_Per_Information23 VARCHAR2,
2549 X_Per_Information24 VARCHAR2,
2550 X_Per_Information25 VARCHAR2,
2551 X_Per_Information26 VARCHAR2,
2552 X_Per_Information27 VARCHAR2,
2553 X_Per_Information28 VARCHAR2,
2554 X_Per_Information29 VARCHAR2,
2555 X_Per_Information30 VARCHAR2,
2556 X_Known_As VARCHAR2
2557 ) IS
2558 ---------------------------------------------
2559 -- Local Declarations
2560 --
2561 l_exists VARCHAR2(1) := 'N';
2562 l_ctype_changed VARCHAR2(1) := 'N';
2563 l_others_exist VARCHAR2(1) := 'N';
2564 --
2565 --
2566 CURSOR Check_Person_Changed IS
2567 SELECT /*+ NO_EXPAND */ 'Y'
2568 FROM per_all_people_f ppf,per_person_type_usages ptu
2569 WHERE ppf.person_id=X_Contact_Person_id
2570 AND X_Session_Date BETWEEN
2571 ppf.effective_start_date AND ppf.effective_end_date
2572 AND ppf.last_name = X_Last_Name
2573 AND ptu.person_id=ppf.person_id
2574 AND ptu.person_type_id = X_Person_Type_Id
2575 AND (( ppf.comment_id = X_Comment_Id)
2576 OR (ppf.comment_id IS NULL AND X_Comment_Id IS NULL))
2577 AND (( ppf.date_of_birth = X_Date_Of_Birth)
2578 OR (ppf.date_of_birth IS NULL AND X_Date_Of_Birth IS NULL))
2579 AND ((ppf.date_of_death = x_date_of_death)
2580 OR (ppf.date_of_death IS NULL AND x_date_of_death IS NULL))
2581 AND (( ppf.first_name = X_First_Name)
2582 OR (ppf.first_name IS NULL AND X_First_Name IS NULL))
2583 AND (( ppf.middle_names = X_Middle_Names)
2584 OR (ppf.middle_names IS NULL AND X_Middle_Names IS NULL))
2585 AND (( ppf.sex = X_Sex)
2586 OR (ppf.sex IS NULL AND X_Sex IS NULL))
2587 AND (( ppf.title = X_Title)
2588 OR (ppf.title IS NULL AND X_Title IS NULL))
2589 AND (( ppf.pre_name_adjunct = X_pre_name_adjunct)
2590 OR (ppf.pre_name_adjunct IS NULL AND X_pre_name_adjunct IS NULL))
2591 AND (( ppf.suffix = X_suffix)
2592 OR (ppf.suffix IS NULL AND X_suffix IS NULL))
2593 AND (( ppf.national_identifier = X_national_identifier)
2594 OR (ppf.national_identifier IS NULL AND X_national_identifier IS NULL))
2595 AND (( ppf.attribute_category = X_Attribute_Category)
2596 OR (ppf.attribute_category IS NULL AND X_Attribute_Category IS NULL))
2597 AND (( ppf.attribute1 = X_Attribute1)
2598 OR (ppf.attribute1 IS NULL AND X_Attribute1 IS NULL))
2599 AND (( ppf.attribute2 = X_Attribute2)
2600 OR (ppf.attribute2 IS NULL AND X_Attribute2 IS NULL))
2601 AND (( ppf.attribute3 = X_Attribute3)
2602 OR (ppf.attribute3 IS NULL AND X_Attribute3 IS NULL))
2603 AND (( ppf.attribute4 = X_Attribute4)
2604 OR (ppf.attribute4 IS NULL AND X_Attribute4 IS NULL))
2605 AND (( ppf.attribute5 = X_Attribute5)
2606 OR (ppf.attribute5 IS NULL AND X_Attribute5 IS NULL))
2607 AND (( ppf.attribute6 = X_Attribute6)
2608 OR (ppf.attribute6 IS NULL AND X_Attribute6 IS NULL))
2609 AND (( ppf.attribute7 = X_Attribute7)
2610 OR (ppf.attribute7 IS NULL AND X_Attribute7 IS NULL))
2611 AND (( ppf.attribute8 = X_Attribute8)
2612 OR (ppf.attribute8 IS NULL AND X_Attribute8 IS NULL))
2613 AND (( ppf.attribute9 = X_Attribute9)
2614 OR (ppf.attribute9 IS NULL AND X_Attribute9 IS NULL))
2615 AND (( ppf.attribute10 = X_Attribute10)
2616 OR (ppf.attribute10 IS NULL AND X_Attribute10 IS NULL))
2617 AND (( ppf.attribute11 = X_Attribute11)
2618 OR (ppf.attribute11 IS NULL AND X_Attribute11 IS NULL))
2619 AND (( ppf.attribute12 = X_Attribute12)
2620 OR (ppf.attribute12 IS NULL AND X_Attribute12 IS NULL))
2621 AND (( ppf.attribute13 = X_Attribute13)
2622 OR (ppf.attribute13 IS NULL AND X_Attribute13 IS NULL))
2623 AND (( ppf.attribute14 = X_Attribute14)
2624 OR (ppf.attribute14 IS NULL AND X_Attribute14 IS NULL))
2625 AND (( ppf.attribute15 = X_Attribute15)
2626 OR (ppf.attribute15 IS NULL AND X_Attribute15 IS NULL))
2627 AND (( ppf.attribute16 = X_Attribute16)
2628 OR (ppf.attribute16 IS NULL AND X_Attribute16 IS NULL))
2629 AND (( ppf.attribute17 = X_Attribute17)
2630 OR (ppf.attribute17 IS NULL AND X_Attribute17 IS NULL))
2631 AND (( ppf.attribute18 = X_Attribute18)
2632 OR (ppf.attribute18 IS NULL AND X_Attribute18 IS NULL))
2633 AND (( ppf.attribute19 = X_Attribute19)
2634 OR (ppf.attribute19 IS NULL AND X_Attribute19 IS NULL))
2635 AND (( ppf.attribute20 = X_Attribute20)
2636 OR (ppf.attribute20 IS NULL AND X_Attribute20 IS NULL))
2637 AND (( ppf.attribute21 = X_Attribute11)
2638 OR (ppf.attribute21 IS NULL AND X_Attribute21 IS NULL))
2639 AND (( ppf.attribute22 = X_Attribute22)
2640 OR (ppf.attribute22 IS NULL AND X_Attribute22 IS NULL))
2641 AND (( ppf.attribute23 = X_Attribute23)
2642 OR (ppf.attribute23 IS NULL AND X_Attribute23 IS NULL))
2643 AND (( ppf.attribute24 = X_Attribute24)
2644 OR (ppf.attribute24 IS NULL AND X_Attribute24 IS NULL))
2645 AND (( ppf.attribute25 = X_Attribute25)
2646 OR (ppf.attribute25 IS NULL AND X_Attribute25 IS NULL))
2647 AND (( ppf.attribute26 = X_Attribute26)
2648 OR (ppf.attribute26 IS NULL AND X_Attribute26 IS NULL))
2652 OR (ppf.attribute28 IS NULL AND X_Attribute28 IS NULL))
2649 AND (( ppf.attribute27 = X_Attribute27)
2650 OR (ppf.attribute27 IS NULL AND X_Attribute27 IS NULL))
2651 AND (( ppf.attribute28 = X_Attribute28)
2653 AND (( ppf.attribute29 = X_Attribute29)
2654 OR (ppf.attribute29 IS NULL AND X_Attribute29 IS NULL))
2655 AND (( ppf.attribute30 = X_Attribute30)
2656 OR (ppf.attribute30 IS NULL AND X_Attribute30 IS NULL))
2657 AND (( ppf.per_information_category = X_per_information_category)
2658 OR (ppf.per_information_category IS NULL AND X_per_information_category IS NULL))
2659 AND (( ppf.per_information1 = X_per_information1)
2660 OR (ppf.per_information1 IS NULL AND X_per_information1 IS NULL))
2661 AND (( ppf.per_information2 = X_per_information2)
2662 OR (ppf.per_information2 IS NULL AND X_per_information2 IS NULL))
2663 AND (( ppf.per_information3 = X_per_information3)
2664 OR (ppf.per_information3 IS NULL AND X_per_information3 IS NULL))
2665 AND (( ppf.per_information4 = X_per_information4)
2666 OR (ppf.per_information4 IS NULL AND X_per_information4 IS NULL))
2667 AND (( ppf.per_information5 = X_per_information5)
2668 OR (ppf.per_information5 IS NULL AND X_per_information5 IS NULL))
2669 AND (( ppf.per_information6 = X_per_information6)
2670 OR (ppf.per_information6 IS NULL AND X_per_information6 IS NULL))
2671 AND (( ppf.per_information7 = X_per_information7)
2672 OR (ppf.per_information7 IS NULL AND X_per_information7 IS NULL))
2673 AND (( ppf.per_information8 = X_per_information8)
2674 OR (ppf.per_information8 IS NULL AND X_per_information8 IS NULL))
2675 AND (( ppf.per_information9 = X_per_information9)
2676 OR (ppf.per_information9 IS NULL AND X_per_information9 IS NULL))
2677 AND (( ppf.per_information10 = X_per_information10)
2678 OR (ppf.per_information10 IS NULL AND X_per_information10 IS NULL))
2679 AND (( ppf.per_information11 = X_per_information11)
2680 OR (ppf.per_information11 IS NULL AND X_per_information11 IS NULL))
2681 AND (( ppf.per_information12 = X_per_information12)
2682 OR (ppf.per_information12 IS NULL AND X_per_information12 IS NULL))
2683 AND (( ppf.per_information13 = X_per_information13)
2684 OR (ppf.per_information13 IS NULL AND X_per_information13 IS NULL))
2685 AND (( ppf.per_information14 = X_per_information14)
2686 OR (ppf.per_information14 IS NULL AND X_per_information14 IS NULL))
2687 AND (( ppf.per_information15 = X_per_information15)
2688 OR (ppf.per_information15 IS NULL AND X_per_information15 IS NULL))
2689 AND (( ppf.per_information16 = X_per_information16)
2690 OR (ppf.per_information16 IS NULL AND X_per_information16 IS NULL))
2691 AND (( ppf.per_information17 = X_per_information17)
2692 OR (ppf.per_information17 IS NULL AND X_per_information17 IS NULL))
2693 AND (( ppf.per_information18 = X_per_information18)
2694 OR (ppf.per_information18 IS NULL AND X_per_information18 IS NULL))
2695 AND (( ppf.per_information19 = X_per_information19)
2696 OR (ppf.per_information19 IS NULL AND X_per_information19 IS NULL))
2697 AND (( ppf.per_information20 = X_per_information20)
2698 OR (ppf.per_information20 IS NULL AND X_per_information20 IS NULL))
2699 AND (( ppf.per_information21 = X_per_information21)
2700 OR (ppf.per_information21 IS NULL AND X_per_information21 IS NULL))
2701 AND (( ppf.per_information22 = X_per_information22)
2702 OR (ppf.per_information22 IS NULL AND X_per_information22 IS NULL))
2703 AND (( ppf.per_information23 = X_per_information23)
2704 OR (ppf.per_information23 IS NULL AND X_per_information23 IS NULL))
2705 AND (( ppf.per_information24 = X_per_information24)
2706 OR (ppf.per_information24 IS NULL AND X_per_information24 IS NULL))
2707 AND (( ppf.per_information25 = X_per_information25)
2708 OR (ppf.per_information25 IS NULL AND X_per_information25 IS NULL))
2709 AND (( ppf.per_information26 = X_per_information26)
2710 OR (ppf.per_information26 IS NULL AND X_per_information26 IS NULL))
2711 AND (( ppf.per_information27 = X_per_information27)
2712 OR (ppf.per_information27 IS NULL AND X_per_information27 IS NULL))
2713 AND (( ppf.per_information28 = X_per_information28)
2714 OR (ppf.per_information28 IS NULL AND X_per_information28 IS NULL))
2715 AND (( ppf.per_information29 = X_per_information29)
2716 OR (ppf.per_information29 IS NULL AND X_per_information29 IS NULL))
2717 AND (( ppf.per_information30 = X_per_information30)
2718 OR (ppf.per_information30 IS NULL AND X_per_information30 IS NULL))
2719 AND (( ppf.known_as = X_known_as)
2720 OR (ppf.known_as IS NULL AND X_known_as IS NULL));
2721 --
2722 CURSOR Check_For_Person_Rows IS
2723 SELECT 'Y'
2724 FROM per_all_people_f
2725 WHERE person_id = X_Contact_Person_Id
2726 HAVING COUNT(person_id) > 1;
2727 --
2728 -- bug fix 4281500 start here --
2729 -- Cursor to check whether
2730 -- relationship with an end date
2731 -- earlier than date of brith exists.
2732
2733 cursor csr_chk_rel is
2734 select contact_relationship_id
2735 from per_contact_relationships
2736 where contact_person_id = X_contact_Person_Id
2737 and date_end < x_date_of_birth;
2738
2739 l_con_rel_id per_contact_relationships.contact_relationship_id%type;
2740
2741 -- cursor to fetch contact relationship details
2742 -- of the records to be updated.
2743
2744 cursor csr_rel is
2745 select c.*,rowidtochar(rowid) row_id
2746 from per_contact_relationships c
2747 where c.contact_person_id = X_contact_Person_Id
2748 and c.date_start < x_date_of_birth;
2749
2750 -- bug fix 4281500 ends here -----
2751
2752 ---------------------------------------------------------
2756 PROCEDURE Update_Person IS
2753 -- Local Procedure to Update Person
2754 --
2755 --
2757 --
2758 l_full_name VARCHAR2(240);
2759 l_global_name VARCHAR2(240);
2760 l_local_name VARCHAR2(240);
2761 l_order_name VARCHAR2(240);
2762 l_person_id NUMBER;
2763 l_rowid VARCHAR2(30):=null;
2764 l_dup_name VARCHAR2(1);
2765 l_previous_last_name per_all_people_f.previous_last_name%TYPE;
2766 l_email_address per_all_people_f.email_address%TYPE;
2767 l_employee_number per_all_people_f.employee_number%TYPE;
2768 l_applicant_number per_all_people_f.applicant_number%TYPE;
2769 l_npw_number per_all_people_f.npw_number%TYPE;
2770 --
2771 CURSOR Lock_Person_Row IS
2772 SELECT ROWIDTOCHAR(ROWID)
2773 , previous_last_name, email_address, employee_number -- #3889584
2774 , applicant_number, npw_number
2775 FROM per_all_people_f
2776 WHERE person_id = X_Contact_Person_Id;
2777 --Locking of row is cauising ORA-1002, if any statements present in
2778 --cursor for loop. Have logged bug 2030142 for this, for now will
2779 --remove the lock.
2780 -- FOR UPDATE OF person_id;
2781 --
2782 -- Changes for HR/TCA Merge
2783 cursor c_person is
2784 select *
2785 from per_all_people_f
2786 where ROWID =CHARTOROWID(l_rowid);
2787 --
2788 l_person per_all_people_f%rowtype;
2789 --
2790 -- Bug 4295302
2791 --
2792 CURSOR get_old_ppf (cv_person_id IN NUMBER)
2793 IS
2794 SELECT person_id, business_group_id, effective_start_date,
2795 effective_end_date, date_of_birth, date_of_death,
2796 on_military_service, marital_status, registered_disabled_flag,
2797 sex, student_status, benefit_group_id, coord_ben_no_cvg_flag,
2798 uses_tobacco_flag, coord_ben_med_pln_no, per_information10,
2799 dpdnt_vlntry_svce_flag, receipt_of_death_cert_date, attribute1,
2800 attribute2, attribute3, attribute4, attribute5, attribute6,
2801 attribute7, attribute8, attribute9, attribute10, attribute11,
2802 attribute12, attribute13, attribute14, attribute15, attribute16,
2803 attribute17, attribute18, attribute19, attribute20, attribute21,
2804 attribute22, attribute23, attribute24, attribute25, attribute26,
2805 attribute27, attribute28, attribute29, attribute30, NULL
2806 FROM per_all_people_f
2807 WHERE x_session_date BETWEEN effective_start_date AND effective_end_date
2808 AND person_id = cv_person_id;
2809 --
2810 l_ppf_ler_new_rec ben_ppf_ler.g_ppf_ler_rec;
2811 l_ppf_ler_old_rec ben_ppf_ler.g_ppf_ler_rec;
2812 --
2813 -- Bug 4295302
2814 --
2815 --
2816 BEGIN
2817 -- Get the New Full Name
2818 --hr_person.derive_full_name (x_first_name, x_middle_names,
2819 -- x_last_name, x_known_as, x_title, x_suffix, x_pre_name_adjunct,
2820 -- x_date_of_birth, x_person_type_id, x_business_group_id, l_full_name,
2821 -- l_dup_name);
2822 --
2823 -- Select for update
2824 --
2825 --
2826 -- Bug 4295302
2827 --
2828 open get_old_ppf(cv_person_id => x_contact_person_id);
2829 --
2830 fetch get_old_ppf into l_ppf_ler_old_rec;
2831 --
2832 close get_old_ppf;
2833 --
2834 l_ppf_ler_new_rec.business_group_id := x_business_group_id;
2835 l_ppf_ler_new_rec.person_id := X_Contact_Person_Id;
2836 l_ppf_ler_new_rec.effective_start_date := x_session_date;
2837 l_ppf_ler_new_rec.effective_end_date := l_ppf_ler_old_rec.effective_end_date;
2838 l_ppf_ler_new_rec.date_of_birth := x_date_of_birth;
2839 l_ppf_ler_new_rec.date_of_death := x_date_of_death;
2840 l_ppf_ler_new_rec.marital_status := l_ppf_ler_old_rec.marital_status;
2841 l_ppf_ler_new_rec.on_military_service := l_ppf_ler_old_rec.on_military_service;
2842 l_ppf_ler_new_rec.registered_disabled_flag := l_ppf_ler_old_rec.registered_disabled_flag;
2843 l_ppf_ler_new_rec.sex := x_sex;
2844 l_ppf_ler_new_rec.student_status := l_ppf_ler_old_rec.student_status;
2845 l_ppf_ler_new_rec.coord_ben_med_pln_no := l_ppf_ler_old_rec.coord_ben_med_pln_no;
2846 l_ppf_ler_new_rec.coord_ben_no_cvg_flag := l_ppf_ler_old_rec.coord_ben_no_cvg_flag;
2847 l_ppf_ler_new_rec.uses_tobacco_flag := l_ppf_ler_old_rec.uses_tobacco_flag; --
2848 l_ppf_ler_new_rec.benefit_group_id := l_ppf_ler_old_rec.benefit_group_id; --
2849 l_ppf_ler_new_rec.DPDNT_VLNTRY_SVCE_FLAG := l_ppf_ler_old_rec.dpdnt_vlntry_svce_flag; --
2850 l_ppf_ler_new_rec.RECEIPT_OF_DEATH_CERT_DATE := l_ppf_ler_old_rec.receipt_of_death_cert_date; --
2851 l_ppf_ler_new_rec.per_information10 := x_per_information10;
2852 l_ppf_ler_new_rec.attribute1 := x_attribute1;
2853 l_ppf_ler_new_rec.attribute2 := x_attribute2;
2854 l_ppf_ler_new_rec.attribute3 := x_attribute3;
2855 l_ppf_ler_new_rec.attribute4 := x_attribute4;
2856 l_ppf_ler_new_rec.attribute5 := x_attribute5;
2857 l_ppf_ler_new_rec.attribute6 := x_attribute6;
2858 l_ppf_ler_new_rec.attribute7 := x_attribute7;
2859 l_ppf_ler_new_rec.attribute8 := x_attribute8;
2860 l_ppf_ler_new_rec.attribute9 := x_attribute9;
2861 l_ppf_ler_new_rec.attribute10 := x_attribute10;
2862 l_ppf_ler_new_rec.attribute11 := x_attribute11;
2863 l_ppf_ler_new_rec.attribute12 := x_attribute12;
2864 l_ppf_ler_new_rec.attribute13 := x_attribute13;
2868 l_ppf_ler_new_rec.attribute17 := x_attribute17;
2865 l_ppf_ler_new_rec.attribute14 := x_attribute14;
2866 l_ppf_ler_new_rec.attribute15 := x_attribute15;
2867 l_ppf_ler_new_rec.attribute16 := x_attribute16;
2869 l_ppf_ler_new_rec.attribute18 := x_attribute18;
2870 l_ppf_ler_new_rec.attribute19 := x_attribute19;
2871 l_ppf_ler_new_rec.attribute20 := x_attribute20;
2872 l_ppf_ler_new_rec.attribute21 := x_attribute21;
2873 l_ppf_ler_new_rec.attribute22 := x_attribute22;
2874 l_ppf_ler_new_rec.attribute23 := x_attribute23;
2875 l_ppf_ler_new_rec.attribute24 := x_attribute24;
2876 l_ppf_ler_new_rec.attribute25 := x_attribute25;
2877 l_ppf_ler_new_rec.attribute26 := x_attribute26;
2878 l_ppf_ler_new_rec.attribute27 := x_attribute27;
2879 l_ppf_ler_new_rec.attribute28 := x_attribute28;
2880 l_ppf_ler_new_rec.attribute29 := x_attribute29;
2881 l_ppf_ler_new_rec.attribute30 := x_attribute30;
2882 --
2883 -- Bug 4295302
2884 --
2885 OPEN Lock_Person_Row;
2886 LOOP
2887 FETCH Lock_Person_Row
2888 INTO l_rowid
2889 , l_previous_last_name, l_email_address, l_employee_number
2890 , l_applicant_number, l_npw_number;
2891 EXIT WHEN Lock_Person_Row%NOTFOUND;
2892
2893 hr_utility.set_location('update_contact.update_person',10);
2894
2895 hr_person_name.derive_person_names -- #3889584
2896 (p_format_name => NULL, -- generate all names
2897 p_business_group_id => x_business_group_id,
2898 p_person_id => NULL, -- X_Contact_Person_Id
2899 p_first_name => x_first_name,
2900 p_middle_names => x_middle_names,
2901 p_last_name => x_last_name,
2902 p_known_as => x_known_as,
2903 p_title => x_title,
2904 p_suffix => x_suffix,
2905 p_pre_name_adjunct => x_pre_name_adjunct,
2906 p_date_of_birth => x_date_of_birth,
2907 p_previous_last_name => l_previous_last_name ,
2908 p_email_address => l_email_address ,
2909 p_employee_number => l_employee_number ,
2910 p_applicant_number => l_applicant_number ,
2911 p_npw_number => l_npw_number ,
2912 p_per_information1 => x_per_information1 ,
2913 p_per_information2 => x_per_information2 ,
2914 p_per_information3 => x_per_information3 ,
2915 p_per_information4 => x_per_information4 ,
2916 p_per_information5 => x_per_information5 ,
2917 p_per_information6 => x_per_information6 ,
2918 p_per_information7 => x_per_information7 ,
2919 p_per_information8 => x_per_information8 ,
2920 p_per_information9 => x_per_information9 ,
2921 p_per_information10 => x_per_information10 ,
2922 p_per_information11 => x_per_information11 ,
2923 p_per_information12 => x_per_information12 ,
2924 p_per_information13 => x_per_information13 ,
2925 p_per_information14 => x_per_information14 ,
2926 p_per_information15 => x_per_information15 ,
2927 p_per_information16 => x_per_information16 ,
2928 p_per_information17 => x_per_information17 ,
2929 p_per_information18 => x_per_information18 ,
2930 p_per_information19 => x_per_information19 ,
2931 p_per_information20 => x_per_information20 ,
2932 p_per_information21 => x_per_information21 ,
2933 p_per_information22 => x_per_information22 ,
2934 p_per_information23 => x_per_information23 ,
2935 p_per_information24 => x_per_information24 ,
2936 p_per_information25 => x_per_information25 ,
2937 p_per_information26 => x_per_information26 ,
2938 p_per_information27 => x_per_information27 ,
2939 p_per_information28 => x_per_information28 ,
2940 p_per_information29 => x_per_information29 ,
2941 p_per_information30 => x_per_information30 ,
2942 p_attribute1 => x_attribute1 ,
2943 p_attribute2 => x_attribute2 ,
2944 p_attribute3 => x_attribute3 ,
2945 p_attribute4 => x_attribute4 ,
2946 p_attribute5 => x_attribute5 ,
2947 p_attribute6 => x_attribute6 ,
2948 p_attribute7 => x_attribute7 ,
2949 p_attribute8 => x_attribute8 ,
2950 p_attribute9 => x_attribute9 ,
2951 p_attribute10 => x_attribute10 ,
2952 p_attribute11 => x_attribute11 ,
2953 p_attribute12 => x_attribute12 ,
2954 p_attribute13 => x_attribute13 ,
2955 p_attribute14 => x_attribute14 ,
2956 p_attribute15 => x_attribute15 ,
2957 p_attribute16 => x_attribute16 ,
2958 p_attribute17 => x_attribute17 ,
2959 p_attribute18 => x_attribute18 ,
2960 p_attribute19 => x_attribute19 ,
2961 p_attribute20 => x_attribute20 ,
2962 p_attribute21 => x_attribute21 ,
2963 p_attribute22 => x_attribute22 ,
2964 p_attribute23 => x_attribute23,
2965 p_attribute24 => x_attribute24,
2966 p_attribute25 => x_attribute25,
2967 p_attribute26 => x_attribute26,
2968 p_attribute27 => x_attribute27,
2969 p_attribute28 => x_attribute28,
2970 p_attribute29 => x_attribute29,
2971 p_attribute30 => x_attribute30,
2972 p_full_name => l_full_name,
2976 p_duplicate_flag => l_dup_name
2973 p_order_name => l_order_name,
2974 p_global_name => l_global_name,
2975 p_local_name => l_local_name,
2977 );
2978 UPDATE PER_ALL_PEOPLE_F
2979 SET
2980 person_type_id = -- X_Person_Type_Id,
2981 hr_person_type_usage_info.get_default_person_type_id(X_Person_Type_Id),
2982 last_name = X_Last_Name,
2983 comment_id = X_Comment_Id,
2984 date_of_birth = X_Date_Of_Birth,
2985 date_of_death = x_date_of_death,
2986 first_name = X_First_Name,
2987 full_name = l_full_name,
2988 middle_names = X_Middle_Names,
2989 sex = X_Sex,
2990 title = X_Title,
2991 pre_name_adjunct = X_pre_name_adjunct,
2992 suffix = X_SUFFIX,
2993 national_identifier = X_National_Identifier,
2994 attribute_category = X_Attribute_Category,
2995 attribute1 = X_Attribute1,
2996 attribute2 = X_Attribute2,
2997 attribute3 = X_Attribute3,
2998 attribute4 = X_Attribute4,
2999 attribute5 = X_Attribute5,
3000 attribute6 = X_Attribute6,
3001 attribute7 = X_Attribute7,
3002 attribute8 = X_Attribute8,
3003 attribute9 = X_Attribute9,
3004 attribute10 = X_Attribute10,
3005 attribute11 = X_Attribute11,
3006 attribute12 = X_Attribute12,
3007 attribute13 = X_Attribute13,
3008 attribute14 = X_Attribute14,
3009 attribute15 = X_Attribute15,
3010 attribute16 = X_Attribute16,
3011 attribute17 = X_Attribute17,
3012 attribute18 = X_Attribute18,
3013 attribute19 = X_Attribute19,
3014 attribute20 = X_Attribute20,
3015 attribute21 = X_Attribute21,
3016 attribute22 = X_Attribute22,
3017 attribute23 = X_Attribute23,
3018 attribute24 = X_Attribute24,
3019 attribute25 = X_Attribute25,
3020 attribute26 = X_Attribute26,
3021 attribute27 = X_Attribute27,
3022 attribute28 = X_Attribute28,
3023 attribute29 = X_Attribute29,
3024 attribute30 = X_Attribute30,
3025 per_information_category = X_Per_Information_category,
3026 per_information1 = X_per_information1,
3027 per_information2 = X_per_information2,
3028 per_information3 = X_per_information3,
3029 per_information4 = X_per_information4,
3030 per_information5 = X_per_information5,
3031 per_information6 = X_per_information6,
3032 per_information7 = X_per_information7,
3033 per_information8 = X_per_information8,
3034 per_information9 = X_per_information9,
3035 per_information10 = X_per_information10,
3036 per_information11 = X_per_information11,
3037 per_information12 = X_per_information12,
3038 per_information13 = X_per_information13,
3039 per_information14 = X_per_information14,
3040 per_information15 = X_per_information15,
3041 per_information16 = X_per_information16,
3042 per_information17 = X_per_information17,
3043 per_information18 = X_per_information18,
3044 per_information19 = X_per_information19,
3045 per_information20 = X_per_information20,
3046 per_information21 = X_per_information21,
3047 per_information22 = X_per_information22,
3048 per_information23 = X_per_information23,
3049 per_information24 = X_per_information24,
3050 per_information25 = X_per_information25,
3051 per_information26 = X_per_information26,
3052 per_information27 = X_per_information27,
3053 per_information28 = X_per_information28,
3054 per_information29 = X_per_information29,
3055 per_information30 = X_per_information30,
3056 known_as = X_known_as,
3060
3057 global_name = l_global_name,
3058 local_name = l_local_name,
3059 order_name = l_order_name
3061 WHERE ROWID = CHARTOROWID(l_rowid);
3062 hr_utility.set_location('update_contact.update_person',20);
3063
3064
3065 IF (SQL%NOTFOUND) THEN
3066 RAISE NO_DATA_FOUND;
3067 END IF;
3068 --
3069 -- PTU changes
3070 hr_utility.set_location('update_contact.update_person',30);
3071
3072 --
3073 hr_per_type_usage_internal.maintain_person_type_usage
3074 (p_effective_date => X_Session_Date
3075 ,p_person_id => X_Contact_Person_Id
3076 ,p_person_type_id => X_Person_Type_Id
3077 ,p_datetrack_update_mode => 'CORRECTION' --since the person record is only corrected too
3078 );
3079 --
3080 hr_utility.set_location('update_contact.update_person',40);
3081
3082 -- end of PTU changes
3083 --
3084
3085 END LOOP;
3086 --
3087 -- Bug 4295302
3088 --
3089 -- This procedure is will create potential life event reasons if the Person Change
3090 -- criteria is met (a part of Oracle Advanced Benefits functionality)
3091 --
3092 ben_ppf_ler.ler_chk( p_old => l_ppf_ler_old_rec
3093 ,p_new => l_ppf_ler_new_rec
3094 ,p_effective_date => x_session_date );
3095
3096 --
3097 -- Bug 4295302
3098 --
3099 --HR/TCA merge changes
3100 if l_rowid is not null then
3101 open c_person;
3102 --
3103 fetch c_person into l_person;
3104 --
3105 close c_person;
3106 hr_utility.set_location('update_person - before TCA update',100);
3107 hr_utility.set_location('update_person - party_id='||l_person.party_id,200);
3108 hr_utility.set_location('update_person - first_name='||l_person.first_name,200);
3109 --
3110 per_hrtca_merge.update_tca_person(p_Rec => l_person);
3111 --
3112 hr_utility.set_location('update_person - after TCA update',300);
3113 end if;
3114 CLOSE Lock_Person_Row;
3115 END Update_Person;
3116 ---------------------------------------------
3117 -- Main Procedure
3118 --
3119 BEGIN
3120 --
3121 -- Determine whether the person has been updated
3122 -- i.e. check whether the Contact Only flag = 'Y' and if it is then
3123 -- check that the actual values have changed on the person record
3124 -- for the current person record
3125 --
3126 hr_utility.set_location('per_contact_relationships_pkg.update_contact',1);
3127 IF X_Contact_Only = 'Y' THEN
3128 -- bug fix 4281500 starts here --
3129 -- check if there exist a reslationship record with end date
3130 -- earlier than new DOB.
3131
3132 if x_date_of_birth is not null then
3133 open csr_chk_rel;
3134 fetch csr_chk_rel into l_con_rel_id;
3135 if csr_chk_rel%found then
3136 close csr_chk_rel;
3137 hr_utility.set_message(
3138 applid => 800,
3139 l_message_name => 'PER_449600_INVALD_DOB');
3140 --
3141 hr_utility.raise_error;
3142 end if;
3143 close csr_chk_rel;
3144 end if;
3145
3146 -- bug fix 4281500 ends here --
3147 OPEN Check_Person_Changed;
3148 FETCH Check_Person_Changed INTO l_exists;
3149 CLOSE Check_Person_Changed;
3150 --
3151 IF l_exists = 'N' THEN
3152 --
3153 -- If the person has details in the future or past then we must ask
3154 -- whether these should be overridden. The first time this code is
3155 -- called REENTRY_FLAG = 1 and the check is performed. If we need to
3156 -- ask the question set REENTRY_FLAG = 2 and exit.
3157 -- The second time in will happen if the update is to proceed.
3158 --
3159 hr_utility.trace('Calling Check_For_Person_Rows');
3160 IF X_Reentry_Flag = 1 THEN
3161 OPEN Check_For_Person_Rows;
3162 FETCH Check_For_Person_Rows INTO l_others_exist;
3163 CLOSE Check_For_Person_Rows;
3164 --
3165 IF l_others_exist = 'Y' THEN
3166 X_Reentry_Flag := 2;
3167 RETURN;
3168 END IF;
3169 END IF;
3170 hr_utility.set_location('update_contact',2);
3171 Update_Person;
3172 -- bug fix 4281500 starts here --
3173 -- call to update the relationship records
3174 -- having start date earlier than date of birth
3175 if x_date_of_birth is not null then
3176 for rel_rec in csr_rel
3177 loop
3178 --
3179 PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
3180 X_Rowid => rel_rec.Row_Id,
3181 X_Contact_Relationship_Id=> rel_rec.Contact_Relationship_Id,
3182 X_Business_Group_Id => rel_rec.Business_Group_Id,
3183 X_Person_Id => rel_rec.Person_Id,
3184 X_Contact_Person_Id => rel_rec.Contact_Person_Id,
3185 X_Contact_Type => rel_rec.Contact_Type,
3186 X_Comments => rel_rec.Comments,
3187 X_Bondholder_Flag => rel_rec.Bondholder_Flag,
3188 X_third_party_pay_Flag => rel_rec.third_party_pay_Flag,
3189 X_Primary_Contact_Flag => rel_rec.Primary_Contact_Flag,
3190 X_Cont_Attribute_Category=> rel_rec.Cont_Attribute_Category,
3191 X_Cont_Attribute1 => rel_rec.Cont_Attribute1,
3192 X_Cont_Attribute2 => rel_rec.Cont_Attribute2,
3193 X_Cont_Attribute3 => rel_rec.Cont_Attribute3,
3194 X_Cont_Attribute4 => rel_rec.Cont_Attribute4,
3195 X_Cont_Attribute5 => rel_rec.Cont_Attribute5,
3196 X_Cont_Attribute6 => rel_rec.Cont_Attribute6,
3197 X_Cont_Attribute7 => rel_rec.Cont_Attribute7,
3198 X_Cont_Attribute8 => rel_rec.Cont_Attribute8,
3199 X_Cont_Attribute9 => rel_rec.Cont_Attribute9,
3200 X_Cont_Attribute10 => rel_rec.Cont_Attribute10,
3201 X_Cont_Attribute11 => rel_rec.Cont_Attribute11,
3202 X_Cont_Attribute12 => rel_rec.Cont_Attribute12,
3203 X_Cont_Attribute13 => rel_rec.Cont_Attribute13,
3204 X_Cont_Attribute14 => rel_rec.Cont_Attribute14,
3205 X_Cont_Attribute15 => rel_rec.Cont_Attribute15,
3206 X_Cont_Attribute16 => rel_rec.Cont_Attribute16,
3207 X_Cont_Attribute17 => rel_rec.Cont_Attribute17,
3208 X_Cont_Attribute18 => rel_rec.Cont_Attribute18,
3209 X_Cont_Attribute19 => rel_rec.Cont_Attribute19,
3210 X_Cont_Attribute20 => rel_rec.Cont_Attribute20,
3211 X_Cont_Information_Category => rel_rec.Cont_Information_Category,
3212 X_Cont_Information1 => rel_rec.Cont_Information1,
3213 X_Cont_Information2 => rel_rec.Cont_Information2,
3214 X_Cont_Information3 => rel_rec.Cont_Information3,
3215 X_Cont_Information4 => rel_rec.Cont_Information4,
3216 X_Cont_Information5 => rel_rec.Cont_Information5,
3217 X_Cont_Information6 => rel_rec.Cont_Information6,
3218 X_Cont_Information7 => rel_rec.Cont_Information7,
3219 X_Cont_Information8 => rel_rec.Cont_Information8,
3220 X_Cont_Information9 => rel_rec.Cont_Information9,
3221 X_Cont_Information10 => rel_rec.Cont_Information10,
3222 X_Cont_Information11 => rel_rec.Cont_Information11,
3223 X_Cont_Information12 => rel_rec.Cont_Information12,
3224 X_Cont_Information13 => rel_rec.Cont_Information13,
3225 X_Cont_Information14 => rel_rec.Cont_Information14,
3226 X_Cont_Information15 => rel_rec.Cont_Information15,
3227 X_Cont_Information16 => rel_rec.Cont_Information16,
3228 X_Cont_Information17 => rel_rec.Cont_Information17,
3229 X_Cont_Information18 => rel_rec.Cont_Information18,
3230 X_Cont_Information19 => rel_rec.Cont_Information19,
3231 X_Cont_Information20 => rel_rec.Cont_Information20,
3232 X_Session_Date => X_Session_Date,
3233 X_Date_Start => X_date_of_birth,
3234 X_Start_Life_Reason_Id => rel_rec.start_life_reason_id,
3235 X_Date_End => rel_rec.date_end,
3236 X_End_Life_Reason_Id => rel_rec.end_life_reason_id,
3237 X_Rltd_Per_Rsds_W_Dsgntr_Flag => rel_rec.rltd_per_rsds_w_dsgntr_flag,
3238 X_Personal_Flag => rel_rec.personal_flag,
3239 X_Sequence_Number => rel_rec.sequence_number,
3240 X_Dependent_Flag => rel_rec.dependent_flag,
3241 X_Beneficiary_Flag => rel_rec.beneficiary_flag
3242 );
3243
3244 --
3245 end loop;
3246 end if;
3247 -- bug fix 4281500 ends here --
3248 END IF;
3249 END IF;
3250 --
3251 --
3252 hr_utility.set_location('update_contact',3);
3253
3254 /*IF (SQL%NOTFOUND) THEN
3255 RAISE NO_DATA_FOUND;
3256 END IF;*/-- fix for bug 4763755.
3257
3258 END Update_Contact;
3259
3260 END PER_CONTACT_RELATIONSHIPS_PKG;