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