DBA Data[Home] [Help]

PACKAGE BODY: APPS.PER_CONTACT_RELATIONSHIPS_PKG

Source


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