[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Delete_Validation.
JAlloun 08-OCT-96 70.10 366749 Added contact_only foreign key ch was picking up minimum
effective_start_date from per_people_f to set
the contacts effective_start_date !!!. Fixed the
problem by setting the constacts
effective_start_date to be the session date.
13-FEB-97 FSHOJAAS 70.12 436371 Tow parameter were added to insert and update
logic (X_suffix and X_PRE_NAME_ADJUNCT).
04-MAR-97 JAlloun 70.13 Changed all occurances of system.dual to
sys.dual for next release requirements.
18-JUL-97 RKAMIYAM 70.14 Added per_information1 to 30 and know_as cols
11-Sep-97 IHARDING 110.1 505202 Insert value 'N' for all relationship flags
into PER_CONTACT_RELATIONSHIPS for the
mirror contact.
04-DEC-97 IHARDING 110.2 568596 Set p_comments to null when creating
a mirror contact relationship row.
05-MAY-98 CCARTER 115.1 Changes to the following procedures resulting
from OAB changes: INSERT_ROW, LOCK_ROW,
UPDATE_ROW. Dependent_Flag and
Beneficiary_Flag left in, in case OAB
is pulled from 11.5.
15-DEC-98 CCARTER 115.3 Sequence_number parameter and validation
procedure added and called from
Insert_Contact and Update_Row
procedures for OAB.
16-FEB-99 ASahay 115.4 820655 Added and condition in delete_row
to_check for multiple contacts
25-OCT-99 JzyLi 115.6 963097 Replace get_full_name with
hr_person.derive_full_name
22-FEB-00 ASahay 115.7 1152185 Replace appid to 800 from 801 for
message PER_6996_REL_CURR_EXISTS
08-MAR-00 ASahay 115.8 1160369 Added procedure Update_Contact
08-MAR-00 pzwalker 115.9 1239046 Added call to time_validation from
Update_Contact
12-MAY-00 ASahay 115.10 1295442 Added and condition in delete_validation
to_check for multiple contacts
19-OCT-00 GPerry 115.11 1408379 Fixed WWBUG 1408379
Added hook call to OAB so life event
triggers work.
13-Mar-01 KSivagur 115.12 added parameter per_information_category.
20-Jun-01 GPerry 115.13 1833930 Fixed WWBUG 1833930.
Changed Check_For_Duplicate_Person
so that it uses exists and also
so that it uses the BG index.
21-Aug-01 adhunter 115.14 PTU changes to inserting person records
22-Aug-01 adhunter 115.15 added PTU changes to Update_Contact
30-Sep-01 wstallar 115.16 added party_id support
03-Oct-01 wstallar 115.17 added party_id support on update
10-Oct-01 adhunter 115.19 1766066 added call points to maintain_coverage
and DDF cols to all procedures.
Re-did delete routine.
19-Oct-01 wstallar 115.21 add support for TCA-mapped columns
24-Oct-01 adhunter 115.23 1931258 added ben_person_delete calls in delete_row
15-Nov-01 adhunter 115.24 2073795 added validation for X_Contact_Person_Id.
16-Nov-01 adhunter 115.25 added dbdrv line
14-Feb-02 hnarayan 115.27 1772037 added code to pass dff attributes of per_contact_relationships
table to ben_con_ler.ler_chk procedure
Makiko Iwamoto 2002/03/05 115.28 2225930 Added procedure chk_dates to validate
if new date_start is earlier than the
effective_start_date of child contact extra
information records and if new date_end is
later than the effective_end_date of child
contact extra information records before
update.
Modified procedure update_contact to handle
date_of_death.
Modified procedure delete_row to delete
child records in per_contact_extra_info_f.
26-Jul-02 irgonzal 115.29 2483186 added hr_security_internal.populate_new_person
call to ensure security list maintenance
gets properly updated.
06-Sep-02 vramanai 115.30 2533935 modified the cursor defination of check_person_changed
in update_contact procedure, to get the value of
person_type_id from per_person_type_usages
16-Sep-02 vramanai 115.31 2533935 moddified the earlier fix as there was some
problem with the fix.
05-Dec-02 pkakar 115.32 added nocopy to parameters
10-Dec-02 mbocutt 115.33 2690302 Added code to INSERT_ROW routine
to improve performance of duplicate
person checking(cursor
Check_For_Duplicate_Person)
by allowing CBO to pick the index on
LAST_NAME column.
07-Jan-03 vramanai 115.34 2618454 Added a hint in cursor Check_Person_Changed for
better performance.
04-Apr-04 TPAPIRED 115.35 2881631 TCA Unmerge changes
commented calls to create_tca_person
now this proc is called in PTUmaintain
24-Oct-03 ttagawa 115.36 3207660 chk_dates call commented out.
19-DEC-03 smparame 115.37 3326964 New procedure chk_date_of_birth added to
check whether date of birth is less than
or equal to to relationship start date.
Procedure update_contact modified. If
condition to check whether the date of
birth is less than relationship start
date is added.
15-APR-04 smparame 115.38 3546390 Call to chk_date_of_birth is removed.
This validation is moved to front end.
21-JUN-04 adudekul 115.39 3648765 Performance issues. In proc update_row
modified check_mirror_update.
19-jan-05 irgonzal 115.40 3889584 Added call to new routine to derive
person names.
21-FEB-05 smparame 115.41 4197342 Procedure delete_row modified to check whether
the person Irec candidate before deleting the
person record.
08-APR-05 pchowdav 115.42 4281500 update_contact modified to update the
relationship records date start when the
contact date of birth is updated with a later
date.
12-APR-05 abparekh 115.43 4295302 Called procedure ben_ppf_ler from Insert_Row
and Update_Contact to trigger life event
reasons for insert/update of PER_ALL_PEOPLE_F
records for a Contact
13-APR-05 abparekh 115.44 4295302 Fixed GSCC Error
15-JUN-05 bshukla 115.45 4432200 Fixed GSCC Error
06-OCT-05 irgonzal 115.46 Fixed GSCC error: GSCC Standard - File.Pkg.9
14-DEC-05 pchowdav 115.47 4867048 Modified cursors Check_Mirror_Update and
Check_Mirror to use nvl statement.
16-MAR-06 pchowdav 115.48 4763755 Modified procedure Update_Contact .
27-SEP-06 asgugupt 115.49 5415267 Modified procedure Delete_Validation .
27-NOV-06 risgupta 115.50 3988762 commented the call to cursor Check_For_Duplicate_Person
for duplicate check enhancement. This duplicate check is
redundent and will be taken care on contact form.
03-APR-09 pchowdav 115.54 8395666 Modified the procedure update row to call
Ben validation before updating the contact type.
======================================================================================*/
-----------------------------------------------
-- Local Procedure to return Mirror Contact Type
-----------------------------------------------
FUNCTION Get_Mirror_Contact_Type(p_contact_type VARCHAR2) RETURN VARCHAR2 IS
l_Contact_Type VARCHAR2(1);
SELECT 'Y'
FROM per_contact_relationships
WHERE person_id = p_person_id
AND primary_contact_flag = 'Y'
AND NVL(date_start, Hr_General.start_of_time) <=
NVL(p_date_end,Hr_General.end_of_time)
AND NVL(date_end, Hr_General.end_of_time) >=
NVL(p_date_start,Hr_General.start_of_time)
AND ((ROWID <> CHARTOROWID(p_rowid)
AND p_rowid IS NOT NULL)
OR (p_rowid IS NULL)
);
SELECT 'X'
FROM per_contact_relationships per
WHERE per.person_id = p_person_id
AND per.contact_person_id = p_contact_person_id
AND (per.contact_relationship_id <> p_contact_relationship_id
OR p_contact_relationship_id IS NULL)
AND per.contact_type = p_contact_type
AND NVL(p_date_start,l_start_of_time) <= NVL(date_end,l_end_of_time)
AND NVL(p_date_end,l_end_of_time) >= NVL(date_start,l_start_of_time);
SELECT 1
FROM per_contact_extra_info_f
WHERE contact_relationship_id = x_contact_relationship_id
AND effective_start_date < x_date_start;
SELECT 1
FROM per_contact_extra_info_f
WHERE contact_relationship_id = x_contact_relationship_id
AND effective_end_date > x_date_end;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Party_Id NUMBER ,
X_Contact_Relationship_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Person_Id IN OUT NOCOPY NUMBER,
X_Contact_Person_Id IN OUT NOCOPY NUMBER,
X_Contact_Type IN OUT NOCOPY VARCHAR2,
X_Comments VARCHAR2,
X_Bondholder_Flag VARCHAR2,
X_Third_Party_Pay_Flag VARCHAR2,
X_Primary_Contact_Flag VARCHAR2,
X_Cont_Attribute_Category VARCHAR2,
X_Cont_Attribute1 VARCHAR2,
X_Cont_Attribute2 VARCHAR2,
X_Cont_Attribute3 VARCHAR2,
X_Cont_Attribute4 VARCHAR2,
X_Cont_Attribute5 VARCHAR2,
X_Cont_Attribute6 VARCHAR2,
X_Cont_Attribute7 VARCHAR2,
X_Cont_Attribute8 VARCHAR2,
X_Cont_Attribute9 VARCHAR2,
X_Cont_Attribute10 VARCHAR2,
X_Cont_Attribute11 VARCHAR2,
X_Cont_Attribute12 VARCHAR2,
X_Cont_Attribute13 VARCHAR2,
X_Cont_Attribute14 VARCHAR2,
X_Cont_Attribute15 VARCHAR2,
X_Cont_Attribute16 VARCHAR2,
X_Cont_Attribute17 VARCHAR2,
X_Cont_Attribute18 VARCHAR2,
X_Cont_Attribute19 VARCHAR2,
X_Cont_Attribute20 VARCHAR2,
X_Cont_Information_Category VARCHAR2,
X_Cont_Information1 VARCHAR2,
X_Cont_Information2 VARCHAR2,
X_Cont_Information3 VARCHAR2,
X_Cont_Information4 VARCHAR2,
X_Cont_Information5 VARCHAR2,
X_Cont_Information6 VARCHAR2,
X_Cont_Information7 VARCHAR2,
X_Cont_Information8 VARCHAR2,
X_Cont_Information9 VARCHAR2,
X_Cont_Information10 VARCHAR2,
X_Cont_Information11 VARCHAR2,
X_Cont_Information12 VARCHAR2,
X_Cont_Information13 VARCHAR2,
X_Cont_Information14 VARCHAR2,
X_Cont_Information15 VARCHAR2,
X_Cont_Information16 VARCHAR2,
X_Cont_Information17 VARCHAR2,
X_Cont_Information18 VARCHAR2,
X_Cont_Information19 VARCHAR2,
X_Cont_Information20 VARCHAR2,
X_Session_Date DATE,
X_Person_Type_Id NUMBER,
X_Last_Name VARCHAR2,
X_Comment_Id NUMBER,
X_Date_Of_Birth DATE,
X_First_Name VARCHAR2,
X_Middle_Names VARCHAR2,
X_Sex VARCHAR2,
X_Title VARCHAR2,
X_PRE_NAME_ADJUNCT VARCHAR2,
X_SUFFIX VARCHAR2,
X_Title_Desc VARCHAR2,
X_national_identifier VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Attribute26 VARCHAR2,
X_Attribute27 VARCHAR2,
X_Attribute28 VARCHAR2,
X_Attribute29 VARCHAR2,
X_Attribute30 VARCHAR2,
X_Reentry_Flag IN OUT NOCOPY NUMBER,
X_Per_Information_Category VARCHAR2,
X_Per_Information1 VARCHAR2,
X_Per_Information2 VARCHAR2,
X_Per_Information3 VARCHAR2,
X_Per_Information4 VARCHAR2,
X_Per_Information5 VARCHAR2,
X_Per_Information6 VARCHAR2,
X_Per_Information7 VARCHAR2,
X_Per_Information8 VARCHAR2,
X_Per_Information9 VARCHAR2,
X_Per_Information10 VARCHAR2,
X_Per_Information11 VARCHAR2,
X_Per_Information12 VARCHAR2,
X_Per_Information13 VARCHAR2,
X_Per_Information14 VARCHAR2,
X_Per_Information15 VARCHAR2,
X_Per_Information16 VARCHAR2,
X_Per_Information17 VARCHAR2,
X_Per_Information18 VARCHAR2,
X_Per_Information19 VARCHAR2,
X_Per_Information20 VARCHAR2,
X_Per_Information21 VARCHAR2,
X_Per_Information22 VARCHAR2,
X_Per_Information23 VARCHAR2,
X_Per_Information24 VARCHAR2,
X_Per_Information25 VARCHAR2,
X_Per_Information26 VARCHAR2,
X_Per_Information27 VARCHAR2,
X_Per_Information28 VARCHAR2,
X_Per_Information29 VARCHAR2,
X_Per_Information30 VARCHAR2,
X_Known_As VARCHAR2,
X_Date_Start DATE,
X_Start_Life_Reason_Id VARCHAR2,
X_Date_End DATE,
X_End_Life_Reason_Id VARCHAR2,
X_Rltd_Per_Rsds_W_Dsgntr_Flag VARCHAR2,
X_Personal_Flag VARCHAR2,
X_Sequence_Number NUMBER,
X_Create_Mirror_Flag VARCHAR2,
X_Mirror_Type VARCHAR2,
X_Dependent_Flag VARCHAR2,
X_Beneficiary_Flag VARCHAR2,
X_marital_status VARCHAR2 ,
X_nationality VARCHAR2 ,
X_blood_type VARCHAR2 ,
X_correspondence_language VARCHAR2 ,
X_honors VARCHAR2 ,
X_rehire_authorizor VARCHAR2 ,
X_rehire_recommendation VARCHAR2 ,
X_resume_exists VARCHAR2 ,
X_resume_last_updated DATE ,
X_second_passport_exists VARCHAR2 ,
X_student_status VARCHAR2 ,
X_date_of_death DATE ,
X_uses_tobacco_flag VARCHAR2 ,
X_town_of_birth VARCHAR2 ,
X_region_of_birth VARCHAR2 ,
X_country_of_birth VARCHAR2 ,
X_fast_path_employee VARCHAR2 ,
X_email_address VARCHAR2 ,
X_fte_capacity VARCHAR2 ) IS
--
l_contact_type VARCHAR2(30);
SELECT 'Y'
FROM sys.dual
WHERE EXISTS (SELECT NULL
FROM per_all_people_f
WHERE UPPER(last_name) = UPPER(X_Last_Name)
AND ( last_name like l_ul_check
or last_name like l_lu_check
or last_name like l_uu_check
or last_name like l_ll_check)
AND (UPPER(first_name) = UPPER(X_first_name)
OR X_first_name IS NULL
OR first_name IS NULL)
AND (date_of_birth = X_date_of_birth
OR X_date_of_birth IS NULL
OR date_of_birth IS NULL)
AND business_group_id = X_business_group_id);
select 'Y'
from dual
where exists
(select 'Y'
from per_all_people_f
where person_id = X_Contact_Person_Id);
PROCEDURE Insert_Contact IS
CURSOR C IS
SELECT ROWID FROM PER_CONTACT_RELATIONSHIPS
WHERE contact_relationship_id = X_Contact_Relationship_Id;
SELECT per_contact_relationships_s.NEXTVAL FROM sys.dual;
INSERT INTO PER_CONTACT_RELATIONSHIPS(
contact_relationship_id,
business_group_id,
person_id,
contact_person_id,
contact_type,
comments,
Bondholder_Flag,
Third_Party_Pay_Flag,
primary_contact_flag,
cont_attribute_category,
cont_attribute1,
cont_attribute2,
cont_attribute3,
cont_attribute4,
cont_attribute5,
cont_attribute6,
cont_attribute7,
cont_attribute8,
cont_attribute9,
cont_attribute10,
cont_attribute11,
cont_attribute12,
cont_attribute13,
cont_attribute14,
cont_attribute15,
cont_attribute16,
cont_attribute17,
cont_attribute18,
cont_attribute19,
cont_attribute20,
cont_information_category,
cont_information1,
cont_information2,
cont_information3,
cont_information4,
cont_information5,
cont_information6,
cont_information7,
cont_information8,
cont_information9,
cont_information10,
cont_information11,
cont_information12,
cont_information13,
cont_information14,
cont_information15,
cont_information16,
cont_information17,
cont_information18,
cont_information19,
cont_information20,
date_start,
start_life_reason_id,
date_end,
end_life_reason_id,
rltd_per_rsds_w_dsgntr_flag,
personal_flag,
sequence_number,
dependent_flag,
beneficiary_flag
) VALUES (
X_Contact_Relationship_Id,
X_Business_Group_Id,
X_Person_Id,
X_Contact_Person_Id,
X_Contact_Type,
X_Comments,
X_Bondholder_Flag,
l_Third_Party_Pay_Flag,
l_Primary_Contact_Flag,
X_Cont_Attribute_Category,
X_Cont_Attribute1,
X_Cont_Attribute2,
X_Cont_Attribute3,
X_Cont_Attribute4,
X_Cont_Attribute5,
X_Cont_Attribute6,
X_Cont_Attribute7,
X_Cont_Attribute8,
X_Cont_Attribute9,
X_Cont_Attribute10,
X_Cont_Attribute11,
X_Cont_Attribute12,
X_Cont_Attribute13,
X_Cont_Attribute14,
X_Cont_Attribute15,
X_Cont_Attribute16,
X_Cont_Attribute17,
X_Cont_Attribute18,
X_Cont_Attribute19,
X_Cont_Attribute20,
X_Cont_Information_Category,
X_Cont_Information1,
X_Cont_Information2,
X_Cont_Information3,
X_Cont_Information4,
X_Cont_Information5,
X_Cont_Information6,
X_Cont_Information7,
X_Cont_Information8,
X_Cont_Information9,
X_Cont_Information10,
X_Cont_Information11,
X_Cont_Information12,
X_Cont_Information13,
X_Cont_Information14,
X_Cont_Information15,
X_Cont_Information16,
X_Cont_Information17,
X_Cont_Information18,
X_Cont_Information19,
X_Cont_Information20,
X_Date_Start,
X_Start_Life_Reason_Id,
X_Date_End,
X_End_Life_Reason_Id,
l_Rltd_Per_Rsds_W_Dsgntr_Flag,
X_personal_flag,
l_sequence_number,
X_dependent_flag,
X_beneficiary_flag
);
hr_utility.set_location('Insert_contact',1);
END Insert_Contact;
FUNCTION Insert_Person RETURN NUMBER IS
--
l_person per_all_people_f%ROWTYPE;
SELECT ROWID
FROM per_people_f
WHERE person_id = X_Person_Id;
SELECT per_people_s.NEXTVAL
FROM sys.dual;
SELECT *
FROM per_all_people_f
WHERE person_id = l_person_id
AND l_effective_start_date
BETWEEN effective_start_date
AND effective_end_date;
INSERT INTO PER_PEOPLE_F(
person_id,
effective_start_date,
effective_end_date,
business_group_id,
person_type_id,
last_name,
start_date,
comment_id,
current_applicant_flag,
current_emp_or_apl_flag,
current_employee_flag,
date_of_birth,
first_name,
full_name,
middle_names,
sex,
title,
pre_name_adjunct,
suffix,
national_identifier,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
Per_Information_category,
Per_Information1,
Per_Information2,
Per_Information3,
Per_Information4,
Per_Information5,
Per_Information6,
Per_Information7,
Per_Information8,
Per_Information9,
Per_Information10,
Per_Information11,
Per_Information12,
Per_Information13,
Per_Information14,
Per_Information15,
Per_Information16,
Per_Information17,
Per_Information18,
Per_Information19,
Per_Information20,
Per_Information21,
Per_Information22,
Per_Information23,
Per_Information24,
Per_Information25,
Per_Information26,
Per_Information27,
Per_Information28,
Per_Information29,
Per_Information30,
Known_As,
Party_Id,
marital_status,
nationality,
blood_type,
correspondence_language,
honors,
rehire_authorizor,
rehire_recommendation,
resume_exists,
resume_last_updated,
second_passport_exists,
student_status,
date_of_death,
uses_tobacco_flag,
town_of_birth,
region_of_birth,
country_of_birth,
fast_path_employee,
email_address,
fte_capacity,
global_name, -- #3889584
local_name,
order_name
) VALUES (
l_person_id,
l_effective_start_date,
l_effective_end_date,
X_Business_Group_Id,
hr_person_type_usage_info.get_default_person_type_id(X_Person_Type_Id),
-- X_Person_Type_Id,
X_Last_Name,
l_start_date,
X_Comment_Id,
NULL,
NULL,
NULL,
X_Date_Of_Birth,
X_First_Name,
l_full_name,
X_Middle_Names,
X_Sex,
X_Title,
X_PRE_NAME_ADJUNCT,
X_SUFFIX,
X_National_Identifier,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
X_Attribute21,
X_Attribute22,
X_Attribute23,
X_Attribute24,
X_Attribute25,
X_Attribute26,
X_Attribute27,
X_Attribute28,
X_Attribute29,
X_Attribute30,
X_Per_Information_category,
X_Per_Information1,
X_Per_Information2,
X_Per_Information3,
X_Per_Information4,
X_Per_Information5,
X_Per_Information6,
X_Per_Information7,
X_Per_Information8,
X_Per_Information9,
X_Per_Information10,
X_Per_Information11,
X_Per_Information12,
X_Per_Information13,
X_Per_Information14,
X_Per_Information15,
X_Per_Information16,
X_Per_Information17,
X_Per_Information18,
X_Per_Information19,
X_Per_Information20,
X_Per_Information21,
X_Per_Information22,
X_Per_Information23,
X_Per_Information24,
X_Per_Information25,
X_Per_Information26,
X_Per_Information27,
X_Per_Information28,
X_Per_Information29,
X_Per_Information30,
X_Known_As,
X_Party_Id,
X_marital_status,
X_nationality,
X_blood_type,
X_correspondence_language,
X_honors,
X_rehire_authorizor,
X_rehire_recommendation,
X_resume_exists,
X_resume_last_updated,
X_second_passport_exists,
X_student_status,
X_date_of_death,
X_uses_tobacco_flag,
X_town_of_birth,
X_region_of_birth,
X_country_of_birth,
X_fast_path_employee,
X_email_address,
X_fte_capacity,
l_global_name, -- #3889584
l_local_name,
l_order_name
);
hr_utility.set_location('pecon01t.insert_row',70);
hr_utility.set_location('pecon01t.insert_row',75);
UPDATE per_people_f
SET party_id = l_person.party_id
WHERE person_id = l_person_id;
hr_utility.set_location('Insert_person',2);
SELECT person_type_id INTO l_person_type_id
FROM per_person_types
WHERE person_type_id = X_Person_Type_Id
AND business_group_id = X_Business_Group_Id
AND active_flag = 'Y'
AND system_person_type = 'OTHER';
END Insert_Person;
X_Contact_Person_Id := Insert_Person;
Insert_Contact;
Insert_Contact;
END Insert_Row;
SELECT *
FROM PER_CONTACT_RELATIONSHIPS
WHERE ROWID = X_Rowid
FOR UPDATE OF Contact_Relationship_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Contact_Relationship_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Person_Id NUMBER,
X_Contact_Person_Id NUMBER,
X_Contact_Type VARCHAR2,
X_Comments VARCHAR2,
X_Bondholder_Flag VARCHAR2,
X_Third_Party_Pay_Flag VARCHAR2,
X_Primary_Contact_Flag VARCHAR2,
X_Cont_Attribute_Category VARCHAR2,
X_Cont_Attribute1 VARCHAR2,
X_Cont_Attribute2 VARCHAR2,
X_Cont_Attribute3 VARCHAR2,
X_Cont_Attribute4 VARCHAR2,
X_Cont_Attribute5 VARCHAR2,
X_Cont_Attribute6 VARCHAR2,
X_Cont_Attribute7 VARCHAR2,
X_Cont_Attribute8 VARCHAR2,
X_Cont_Attribute9 VARCHAR2,
X_Cont_Attribute10 VARCHAR2,
X_Cont_Attribute11 VARCHAR2,
X_Cont_Attribute12 VARCHAR2,
X_Cont_Attribute13 VARCHAR2,
X_Cont_Attribute14 VARCHAR2,
X_Cont_Attribute15 VARCHAR2,
X_Cont_Attribute16 VARCHAR2,
X_Cont_Attribute17 VARCHAR2,
X_Cont_Attribute18 VARCHAR2,
X_Cont_Attribute19 VARCHAR2,
X_Cont_Attribute20 VARCHAR2,
X_Cont_Information_Category VARCHAR2,
X_Cont_Information1 VARCHAR2,
X_Cont_Information2 VARCHAR2,
X_Cont_Information3 VARCHAR2,
X_Cont_Information4 VARCHAR2,
X_Cont_Information5 VARCHAR2,
X_Cont_Information6 VARCHAR2,
X_Cont_Information7 VARCHAR2,
X_Cont_Information8 VARCHAR2,
X_Cont_Information9 VARCHAR2,
X_Cont_Information10 VARCHAR2,
X_Cont_Information11 VARCHAR2,
X_Cont_Information12 VARCHAR2,
X_Cont_Information13 VARCHAR2,
X_Cont_Information14 VARCHAR2,
X_Cont_Information15 VARCHAR2,
X_Cont_Information16 VARCHAR2,
X_Cont_Information17 VARCHAR2,
X_Cont_Information18 VARCHAR2,
X_Cont_Information19 VARCHAR2,
X_Cont_Information20 VARCHAR2,
X_Session_Date DATE,
X_Date_Start DATE,
X_Start_Life_Reason_Id VARCHAR2,
X_Date_End DATE,
X_End_Life_Reason_Id VARCHAR2,
X_Rltd_Per_Rsds_W_Dsgntr_Flag VARCHAR2,
X_Personal_Flag VARCHAR2,
X_Sequence_Number NUMBER,
X_Dependent_Flag VARCHAR2,
X_Beneficiary_Flag VARCHAR2
) IS
---------------------------------------------
-- Local Declarations
--
l_exists VARCHAR2(1) := 'N';
l_mirror_update VARCHAR2(1) := 'N';
CURSOR Check_Mirror_Update IS
SELECT 'Y'
FROM per_contact_relationships
WHERE ROWID = CHARTOROWID(X_Rowid)
AND( nvl(date_start,hr_api.g_date) <> nvl(X_date_start,hr_api.g_date)
OR nvl(start_life_reason_id,hr_api.g_number) <> nvl(X_start_life_reason_Id,hr_api.g_number)
OR nvl(date_end,hr_api.g_date) <> nvl(X_date_end,hr_api.g_date)
OR nvl(end_life_reason_id,hr_api.g_number) <> nvl(X_end_life_reason_id,hr_api.g_number)
OR nvl(rltd_per_rsds_w_dsgntr_flag,hr_api.g_varchar2) <> nvl(X_rltd_per_rsds_w_dsgntr_flag,hr_api.g_varchar2)
OR nvl(personal_flag,hr_api.g_varchar2) <> nvl(X_personal_flag,hr_api.g_varchar2));
SELECT ROWIDTOCHAR(ROWID)
FROM per_contact_relationships
WHERE contact_person_id = X_Person_Id
AND person_id = X_Contact_Person_Id
AND contact_type = l_old_mirror_contact_type
AND nvl(date_start,hr_api.g_date) =nvl(l_date_start,hr_api.g_date)--fix for bug 4867048.
FOR UPDATE OF Contact_Type;
SELECT 'Y'
FROM per_contact_relationships
WHERE contact_relationship_id = X_Contact_Relationship_Id
AND contact_type <> X_Contact_Type
AND EXISTS
(SELECT NULL
FROM per_cobra_cov_enrollments
WHERE CONTACT_RELATIONSHIP_ID = X_Contact_Relationship_Id);
SELECT *
FROM per_contact_relationships
WHERE ROWID = CHARTOROWID(l_mirror_rowid);
SELECT *
FROM per_contact_relationships
WHERE ROWID = x_rowid;
SELECT date_start, contact_type
INTO l_date_start, l_contact_type
FROM per_contact_relationships
WHERE ROWID = CHARTOROWID(X_rowid);
OPEN Check_Mirror_Update;
FETCH Check_Mirror_Update INTO l_mirror_update;
CLOSE Check_Mirror_Update;
IF l_mirror_update = 'Y' THEN
OPEN Check_Mirror;
UPDATE per_contact_relationships
SET
date_start = X_date_start
,start_life_reason_id = X_start_life_reason_id
,date_end = X_date_end
,end_life_reason_id = X_end_life_reason_id
,rltd_per_rsds_w_dsgntr_flag = X_rltd_per_rsds_w_dsgntr_flag
,personal_flag = X_personal_flag
WHERE ROWID = CHARTOROWID(l_mirror_rowid);
UPDATE PER_CONTACT_RELATIONSHIPS
SET
contact_relationship_id = X_Contact_Relationship_Id,
business_group_id = X_Business_Group_Id,
person_id = X_Person_Id,
contact_person_id = X_Contact_Person_Id,
contact_type = X_Contact_Type,
comments = X_Comments,
Bondholder_Flag = X_Bondholder_Flag,
Third_Party_Pay_Flag = X_Third_Party_Pay_Flag,
primary_contact_flag = X_Primary_Contact_Flag,
cont_attribute_category = X_Cont_Attribute_Category,
cont_attribute1 = X_Cont_Attribute1,
cont_attribute2 = X_Cont_Attribute2,
cont_attribute3 = X_Cont_Attribute3,
cont_attribute4 = X_Cont_Attribute4,
cont_attribute5 = X_Cont_Attribute5,
cont_attribute6 = X_Cont_Attribute6,
cont_attribute7 = X_Cont_Attribute7,
cont_attribute8 = X_Cont_Attribute8,
cont_attribute9 = X_Cont_Attribute9,
cont_attribute10 = X_Cont_Attribute10,
cont_attribute11 = X_Cont_Attribute11,
cont_attribute12 = X_Cont_Attribute12,
cont_attribute13 = X_Cont_Attribute13,
cont_attribute14 = X_Cont_Attribute14,
cont_attribute15 = X_Cont_Attribute15,
cont_attribute16 = X_Cont_Attribute16,
cont_attribute17 = X_Cont_Attribute17,
cont_attribute18 = X_Cont_Attribute18,
cont_attribute19 = X_Cont_Attribute19,
cont_attribute20 = X_Cont_Attribute20,
cont_information_category = X_Cont_Information_Category,
cont_information1 = X_Cont_Information1,
cont_information2 = X_Cont_Information2,
cont_information3 = X_Cont_Information3,
cont_information4 = X_Cont_Information4,
cont_information5 = X_Cont_Information5,
cont_information6 = X_Cont_Information6,
cont_information7 = X_Cont_Information7,
cont_information8 = X_Cont_Information8,
cont_information9 = X_Cont_Information9,
cont_information10 = X_Cont_Information10,
cont_information11 = X_Cont_Information11,
cont_information12 = X_Cont_Information12,
cont_information13 = X_Cont_Information13,
cont_information14 = X_Cont_Information14,
cont_information15 = X_Cont_Information15,
cont_information16 = X_Cont_Information16,
cont_information17 = X_Cont_Information17,
cont_information18 = X_Cont_Information18,
cont_information19 = X_Cont_Information19,
cont_information20 = X_Cont_Information20,
date_start = X_date_start,
start_life_reason_id = X_start_life_reason_id,
date_end = X_date_end,
end_life_reason_id = X_end_life_reason_id,
rltd_per_rsds_w_dsgntr_flag = X_rltd_per_rsds_w_dsgntr_flag,
personal_flag = X_personal_flag,
sequence_number = X_sequence_number,
dependent_flag = X_dependent_flag,
beneficiary_flag = X_beneficiary_flag
WHERE ROWID = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2
,X_Person_Id NUMBER
,X_Contact_Person_Id NUMBER
,X_Contact_Relationship_Id NUMBER
,X_Date_Start DATE
,X_Contact_Type VARCHAR2) IS
l_mirror_type VARCHAR2(1);
DELETE FROM PER_CONTACT_RELATIONSHIPS
WHERE ROWID = X_Rowid;
DELETE FROM PER_CONTACT_RELATIONSHIPS
WHERE person_id = X_Contact_Person_Id
AND contact_person_id = X_Person_Id
AND contact_type = l_mirror_type
AND date_start = X_date_start;
Delete_Validation(X_Contact_Person_Id
,X_Contact_Relationship_Id);
DELETE FROM per_contact_extra_info_f
WHERE contact_relationship_id = x_contact_relationship_id;
ben_person_delete.perform_ri_check(X_Contact_Person_Id);
DELETE FROM hr_comments h
WHERE h.comment_id IN
(SELECT comment_id
FROM PER_ALL_PEOPLE_F paf
WHERE paf.person_id = X_Contact_Person_Id);
ben_person_delete.delete_ben_rows(X_Contact_Person_Id);
DELETE per_all_people_f
WHERE person_id = X_Contact_Person_Id;
END Delete_Row;
PROCEDURE Delete_Validation(X_Person_Id NUMBER
,X_Contact_Relationship_Id NUMBER) IS
l_address_exists VARCHAR2(1) := 'N';
SELECT 'Y'
FROM per_person_analyses
WHERE person_id = X_Person_Id;
SELECT 'Y'
FROM per_addresses
WHERE person_id = X_Person_Id;
SELECT 'Y'
FROM per_cobra_cov_enrollments
WHERE CONTACT_RELATIONSHIP_ID = X_Contact_Relationship_Id;
SELECT 'Y'
FROM BEN_BENEFICIARIES_X
WHERE source_id = X_Person_Id;
SELECT sequence_number
FROM per_contact_relationships con
WHERE con.person_id = p_person_id
AND con.contact_person_id = p_contact_person_id
AND con.sequence_number <> p_sequence_number;
SELECT sequence_number
FROM per_contact_relationships con
WHERE con.person_id = p_person_id
AND con.contact_person_id = p_contact_person_id
AND con.contact_relationship_id = p_contact_relationship_id;
SELECT sequence_number
FROM per_contact_relationships con
WHERE con.person_id = p_person_id
AND con.contact_person_id <> p_contact_person_id
AND con.sequence_number = p_sequence_number;
PROCEDURE Update_Contact(X_Business_Group_Id NUMBER,
X_Person_Id NUMBER,
X_Contact_Person_Id NUMBER,
X_Session_Date DATE,
X_Person_Type_Id NUMBER,
X_Last_Name VARCHAR2,
X_Comment_Id NUMBER,
X_Date_Of_Birth DATE,
x_date_of_death DATE,
X_First_Name VARCHAR2,
X_Middle_Names VARCHAR2,
X_Sex VARCHAR2,
X_Title VARCHAR2,
X_PRE_NAME_ADJUNCT VARCHAR2,
X_SUFFIX VARCHAR2,
X_Title_Desc VARCHAR2,
X_National_Identifier VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Attribute26 VARCHAR2,
X_Attribute27 VARCHAR2,
X_Attribute28 VARCHAR2,
X_Attribute29 VARCHAR2,
X_Attribute30 VARCHAR2,
X_Contact_Only VARCHAR2,
X_Reentry_flag IN OUT NOCOPY NUMBER,
X_Per_Information_category VARCHAR2,
X_Per_Information1 VARCHAR2,
X_Per_Information2 VARCHAR2,
X_Per_Information3 VARCHAR2,
X_Per_Information4 VARCHAR2,
X_Per_Information5 VARCHAR2,
X_Per_Information6 VARCHAR2,
X_Per_Information7 VARCHAR2,
X_Per_Information8 VARCHAR2,
X_Per_Information9 VARCHAR2,
X_Per_Information10 VARCHAR2,
X_Per_Information11 VARCHAR2,
X_Per_Information12 VARCHAR2,
X_Per_Information13 VARCHAR2,
X_Per_Information14 VARCHAR2,
X_Per_Information15 VARCHAR2,
X_Per_Information16 VARCHAR2,
X_Per_Information17 VARCHAR2,
X_Per_Information18 VARCHAR2,
X_Per_Information19 VARCHAR2,
X_Per_Information20 VARCHAR2,
X_Per_Information21 VARCHAR2,
X_Per_Information22 VARCHAR2,
X_Per_Information23 VARCHAR2,
X_Per_Information24 VARCHAR2,
X_Per_Information25 VARCHAR2,
X_Per_Information26 VARCHAR2,
X_Per_Information27 VARCHAR2,
X_Per_Information28 VARCHAR2,
X_Per_Information29 VARCHAR2,
X_Per_Information30 VARCHAR2,
X_Known_As VARCHAR2
) IS
---------------------------------------------
-- Local Declarations
--
l_exists VARCHAR2(1) := 'N';
SELECT /*+ NO_EXPAND */ 'Y'
FROM per_all_people_f ppf,per_person_type_usages ptu
WHERE ppf.person_id=X_Contact_Person_id
AND X_Session_Date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND ppf.last_name = X_Last_Name
AND ptu.person_id=ppf.person_id
AND ptu.person_type_id = X_Person_Type_Id
AND (( ppf.comment_id = X_Comment_Id)
OR (ppf.comment_id IS NULL AND X_Comment_Id IS NULL))
AND (( ppf.date_of_birth = X_Date_Of_Birth)
OR (ppf.date_of_birth IS NULL AND X_Date_Of_Birth IS NULL))
AND ((ppf.date_of_death = x_date_of_death)
OR (ppf.date_of_death IS NULL AND x_date_of_death IS NULL))
AND (( ppf.first_name = X_First_Name)
OR (ppf.first_name IS NULL AND X_First_Name IS NULL))
AND (( ppf.middle_names = X_Middle_Names)
OR (ppf.middle_names IS NULL AND X_Middle_Names IS NULL))
AND (( ppf.sex = X_Sex)
OR (ppf.sex IS NULL AND X_Sex IS NULL))
AND (( ppf.title = X_Title)
OR (ppf.title IS NULL AND X_Title IS NULL))
AND (( ppf.pre_name_adjunct = X_pre_name_adjunct)
OR (ppf.pre_name_adjunct IS NULL AND X_pre_name_adjunct IS NULL))
AND (( ppf.suffix = X_suffix)
OR (ppf.suffix IS NULL AND X_suffix IS NULL))
AND (( ppf.national_identifier = X_national_identifier)
OR (ppf.national_identifier IS NULL AND X_national_identifier IS NULL))
AND (( ppf.attribute_category = X_Attribute_Category)
OR (ppf.attribute_category IS NULL AND X_Attribute_Category IS NULL))
AND (( ppf.attribute1 = X_Attribute1)
OR (ppf.attribute1 IS NULL AND X_Attribute1 IS NULL))
AND (( ppf.attribute2 = X_Attribute2)
OR (ppf.attribute2 IS NULL AND X_Attribute2 IS NULL))
AND (( ppf.attribute3 = X_Attribute3)
OR (ppf.attribute3 IS NULL AND X_Attribute3 IS NULL))
AND (( ppf.attribute4 = X_Attribute4)
OR (ppf.attribute4 IS NULL AND X_Attribute4 IS NULL))
AND (( ppf.attribute5 = X_Attribute5)
OR (ppf.attribute5 IS NULL AND X_Attribute5 IS NULL))
AND (( ppf.attribute6 = X_Attribute6)
OR (ppf.attribute6 IS NULL AND X_Attribute6 IS NULL))
AND (( ppf.attribute7 = X_Attribute7)
OR (ppf.attribute7 IS NULL AND X_Attribute7 IS NULL))
AND (( ppf.attribute8 = X_Attribute8)
OR (ppf.attribute8 IS NULL AND X_Attribute8 IS NULL))
AND (( ppf.attribute9 = X_Attribute9)
OR (ppf.attribute9 IS NULL AND X_Attribute9 IS NULL))
AND (( ppf.attribute10 = X_Attribute10)
OR (ppf.attribute10 IS NULL AND X_Attribute10 IS NULL))
AND (( ppf.attribute11 = X_Attribute11)
OR (ppf.attribute11 IS NULL AND X_Attribute11 IS NULL))
AND (( ppf.attribute12 = X_Attribute12)
OR (ppf.attribute12 IS NULL AND X_Attribute12 IS NULL))
AND (( ppf.attribute13 = X_Attribute13)
OR (ppf.attribute13 IS NULL AND X_Attribute13 IS NULL))
AND (( ppf.attribute14 = X_Attribute14)
OR (ppf.attribute14 IS NULL AND X_Attribute14 IS NULL))
AND (( ppf.attribute15 = X_Attribute15)
OR (ppf.attribute15 IS NULL AND X_Attribute15 IS NULL))
AND (( ppf.attribute16 = X_Attribute16)
OR (ppf.attribute16 IS NULL AND X_Attribute16 IS NULL))
AND (( ppf.attribute17 = X_Attribute17)
OR (ppf.attribute17 IS NULL AND X_Attribute17 IS NULL))
AND (( ppf.attribute18 = X_Attribute18)
OR (ppf.attribute18 IS NULL AND X_Attribute18 IS NULL))
AND (( ppf.attribute19 = X_Attribute19)
OR (ppf.attribute19 IS NULL AND X_Attribute19 IS NULL))
AND (( ppf.attribute20 = X_Attribute20)
OR (ppf.attribute20 IS NULL AND X_Attribute20 IS NULL))
AND (( ppf.attribute21 = X_Attribute11)
OR (ppf.attribute21 IS NULL AND X_Attribute21 IS NULL))
AND (( ppf.attribute22 = X_Attribute22)
OR (ppf.attribute22 IS NULL AND X_Attribute22 IS NULL))
AND (( ppf.attribute23 = X_Attribute23)
OR (ppf.attribute23 IS NULL AND X_Attribute23 IS NULL))
AND (( ppf.attribute24 = X_Attribute24)
OR (ppf.attribute24 IS NULL AND X_Attribute24 IS NULL))
AND (( ppf.attribute25 = X_Attribute25)
OR (ppf.attribute25 IS NULL AND X_Attribute25 IS NULL))
AND (( ppf.attribute26 = X_Attribute26)
OR (ppf.attribute26 IS NULL AND X_Attribute26 IS NULL))
AND (( ppf.attribute27 = X_Attribute27)
OR (ppf.attribute27 IS NULL AND X_Attribute27 IS NULL))
AND (( ppf.attribute28 = X_Attribute28)
OR (ppf.attribute28 IS NULL AND X_Attribute28 IS NULL))
AND (( ppf.attribute29 = X_Attribute29)
OR (ppf.attribute29 IS NULL AND X_Attribute29 IS NULL))
AND (( ppf.attribute30 = X_Attribute30)
OR (ppf.attribute30 IS NULL AND X_Attribute30 IS NULL))
AND (( ppf.per_information_category = X_per_information_category)
OR (ppf.per_information_category IS NULL AND X_per_information_category IS NULL))
AND (( ppf.per_information1 = X_per_information1)
OR (ppf.per_information1 IS NULL AND X_per_information1 IS NULL))
AND (( ppf.per_information2 = X_per_information2)
OR (ppf.per_information2 IS NULL AND X_per_information2 IS NULL))
AND (( ppf.per_information3 = X_per_information3)
OR (ppf.per_information3 IS NULL AND X_per_information3 IS NULL))
AND (( ppf.per_information4 = X_per_information4)
OR (ppf.per_information4 IS NULL AND X_per_information4 IS NULL))
AND (( ppf.per_information5 = X_per_information5)
OR (ppf.per_information5 IS NULL AND X_per_information5 IS NULL))
AND (( ppf.per_information6 = X_per_information6)
OR (ppf.per_information6 IS NULL AND X_per_information6 IS NULL))
AND (( ppf.per_information7 = X_per_information7)
OR (ppf.per_information7 IS NULL AND X_per_information7 IS NULL))
AND (( ppf.per_information8 = X_per_information8)
OR (ppf.per_information8 IS NULL AND X_per_information8 IS NULL))
AND (( ppf.per_information9 = X_per_information9)
OR (ppf.per_information9 IS NULL AND X_per_information9 IS NULL))
AND (( ppf.per_information10 = X_per_information10)
OR (ppf.per_information10 IS NULL AND X_per_information10 IS NULL))
AND (( ppf.per_information11 = X_per_information11)
OR (ppf.per_information11 IS NULL AND X_per_information11 IS NULL))
AND (( ppf.per_information12 = X_per_information12)
OR (ppf.per_information12 IS NULL AND X_per_information12 IS NULL))
AND (( ppf.per_information13 = X_per_information13)
OR (ppf.per_information13 IS NULL AND X_per_information13 IS NULL))
AND (( ppf.per_information14 = X_per_information14)
OR (ppf.per_information14 IS NULL AND X_per_information14 IS NULL))
AND (( ppf.per_information15 = X_per_information15)
OR (ppf.per_information15 IS NULL AND X_per_information15 IS NULL))
AND (( ppf.per_information16 = X_per_information16)
OR (ppf.per_information16 IS NULL AND X_per_information16 IS NULL))
AND (( ppf.per_information17 = X_per_information17)
OR (ppf.per_information17 IS NULL AND X_per_information17 IS NULL))
AND (( ppf.per_information18 = X_per_information18)
OR (ppf.per_information18 IS NULL AND X_per_information18 IS NULL))
AND (( ppf.per_information19 = X_per_information19)
OR (ppf.per_information19 IS NULL AND X_per_information19 IS NULL))
AND (( ppf.per_information20 = X_per_information20)
OR (ppf.per_information20 IS NULL AND X_per_information20 IS NULL))
AND (( ppf.per_information21 = X_per_information21)
OR (ppf.per_information21 IS NULL AND X_per_information21 IS NULL))
AND (( ppf.per_information22 = X_per_information22)
OR (ppf.per_information22 IS NULL AND X_per_information22 IS NULL))
AND (( ppf.per_information23 = X_per_information23)
OR (ppf.per_information23 IS NULL AND X_per_information23 IS NULL))
AND (( ppf.per_information24 = X_per_information24)
OR (ppf.per_information24 IS NULL AND X_per_information24 IS NULL))
AND (( ppf.per_information25 = X_per_information25)
OR (ppf.per_information25 IS NULL AND X_per_information25 IS NULL))
AND (( ppf.per_information26 = X_per_information26)
OR (ppf.per_information26 IS NULL AND X_per_information26 IS NULL))
AND (( ppf.per_information27 = X_per_information27)
OR (ppf.per_information27 IS NULL AND X_per_information27 IS NULL))
AND (( ppf.per_information28 = X_per_information28)
OR (ppf.per_information28 IS NULL AND X_per_information28 IS NULL))
AND (( ppf.per_information29 = X_per_information29)
OR (ppf.per_information29 IS NULL AND X_per_information29 IS NULL))
AND (( ppf.per_information30 = X_per_information30)
OR (ppf.per_information30 IS NULL AND X_per_information30 IS NULL))
AND (( ppf.known_as = X_known_as)
OR (ppf.known_as IS NULL AND X_known_as IS NULL));
SELECT 'Y'
FROM per_all_people_f
WHERE person_id = X_Contact_Person_Id
HAVING COUNT(person_id) > 1;
select contact_relationship_id
from per_contact_relationships
where contact_person_id = X_contact_Person_Id
and date_end < x_date_of_birth;
select c.*,rowidtochar(rowid) row_id
from per_contact_relationships c
where c.contact_person_id = X_contact_Person_Id
and c.date_start < x_date_of_birth;
PROCEDURE Update_Person IS
--
l_full_name VARCHAR2(240);
SELECT ROWIDTOCHAR(ROWID)
, previous_last_name, email_address, employee_number -- #3889584
, applicant_number, npw_number
FROM per_all_people_f
WHERE person_id = X_Contact_Person_Id;
select *
from per_all_people_f
where ROWID =CHARTOROWID(l_rowid);
SELECT person_id, business_group_id, effective_start_date,
effective_end_date, date_of_birth, date_of_death,
on_military_service, marital_status, registered_disabled_flag,
sex, student_status, benefit_group_id, coord_ben_no_cvg_flag,
uses_tobacco_flag, coord_ben_med_pln_no, per_information10,
dpdnt_vlntry_svce_flag, receipt_of_death_cert_date, attribute1,
attribute2, attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14, attribute15, attribute16,
attribute17, attribute18, attribute19, attribute20, attribute21,
attribute22, attribute23, attribute24, attribute25, attribute26,
attribute27, attribute28, attribute29, attribute30, NULL
FROM per_all_people_f
WHERE x_session_date BETWEEN effective_start_date AND effective_end_date
AND person_id = cv_person_id;
hr_utility.set_location('update_contact.update_person',10);
UPDATE PER_ALL_PEOPLE_F
SET
person_type_id = -- X_Person_Type_Id,
hr_person_type_usage_info.get_default_person_type_id(X_Person_Type_Id),
last_name = X_Last_Name,
comment_id = X_Comment_Id,
date_of_birth = X_Date_Of_Birth,
date_of_death = x_date_of_death,
first_name = X_First_Name,
full_name = l_full_name,
middle_names = X_Middle_Names,
sex = X_Sex,
title = X_Title,
pre_name_adjunct = X_pre_name_adjunct,
suffix = X_SUFFIX,
national_identifier = X_National_Identifier,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20,
attribute21 = X_Attribute21,
attribute22 = X_Attribute22,
attribute23 = X_Attribute23,
attribute24 = X_Attribute24,
attribute25 = X_Attribute25,
attribute26 = X_Attribute26,
attribute27 = X_Attribute27,
attribute28 = X_Attribute28,
attribute29 = X_Attribute29,
attribute30 = X_Attribute30,
per_information_category = X_Per_Information_category,
per_information1 = X_per_information1,
per_information2 = X_per_information2,
per_information3 = X_per_information3,
per_information4 = X_per_information4,
per_information5 = X_per_information5,
per_information6 = X_per_information6,
per_information7 = X_per_information7,
per_information8 = X_per_information8,
per_information9 = X_per_information9,
per_information10 = X_per_information10,
per_information11 = X_per_information11,
per_information12 = X_per_information12,
per_information13 = X_per_information13,
per_information14 = X_per_information14,
per_information15 = X_per_information15,
per_information16 = X_per_information16,
per_information17 = X_per_information17,
per_information18 = X_per_information18,
per_information19 = X_per_information19,
per_information20 = X_per_information20,
per_information21 = X_per_information21,
per_information22 = X_per_information22,
per_information23 = X_per_information23,
per_information24 = X_per_information24,
per_information25 = X_per_information25,
per_information26 = X_per_information26,
per_information27 = X_per_information27,
per_information28 = X_per_information28,
per_information29 = X_per_information29,
per_information30 = X_per_information30,
known_as = X_known_as,
global_name = l_global_name,
local_name = l_local_name,
order_name = l_order_name
WHERE ROWID = CHARTOROWID(l_rowid);
hr_utility.set_location('update_contact.update_person',20);
hr_utility.set_location('update_contact.update_person',30);
,p_datetrack_update_mode => 'CORRECTION' --since the person record is only corrected too
);
hr_utility.set_location('update_contact.update_person',40);
hr_utility.set_location('update_person - before TCA update',100);
hr_utility.set_location('update_person - party_id='||l_person.party_id,200);
hr_utility.set_location('update_person - first_name='||l_person.first_name,200);
per_hrtca_merge.update_tca_person(p_Rec => l_person);
hr_utility.set_location('update_person - after TCA update',300);
END Update_Person;
hr_utility.set_location('per_contact_relationships_pkg.update_contact',1);
hr_utility.set_location('update_contact',2);
Update_Person;
PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
X_Rowid => rel_rec.Row_Id,
X_Contact_Relationship_Id=> rel_rec.Contact_Relationship_Id,
X_Business_Group_Id => rel_rec.Business_Group_Id,
X_Person_Id => rel_rec.Person_Id,
X_Contact_Person_Id => rel_rec.Contact_Person_Id,
X_Contact_Type => rel_rec.Contact_Type,
X_Comments => rel_rec.Comments,
X_Bondholder_Flag => rel_rec.Bondholder_Flag,
X_third_party_pay_Flag => rel_rec.third_party_pay_Flag,
X_Primary_Contact_Flag => rel_rec.Primary_Contact_Flag,
X_Cont_Attribute_Category=> rel_rec.Cont_Attribute_Category,
X_Cont_Attribute1 => rel_rec.Cont_Attribute1,
X_Cont_Attribute2 => rel_rec.Cont_Attribute2,
X_Cont_Attribute3 => rel_rec.Cont_Attribute3,
X_Cont_Attribute4 => rel_rec.Cont_Attribute4,
X_Cont_Attribute5 => rel_rec.Cont_Attribute5,
X_Cont_Attribute6 => rel_rec.Cont_Attribute6,
X_Cont_Attribute7 => rel_rec.Cont_Attribute7,
X_Cont_Attribute8 => rel_rec.Cont_Attribute8,
X_Cont_Attribute9 => rel_rec.Cont_Attribute9,
X_Cont_Attribute10 => rel_rec.Cont_Attribute10,
X_Cont_Attribute11 => rel_rec.Cont_Attribute11,
X_Cont_Attribute12 => rel_rec.Cont_Attribute12,
X_Cont_Attribute13 => rel_rec.Cont_Attribute13,
X_Cont_Attribute14 => rel_rec.Cont_Attribute14,
X_Cont_Attribute15 => rel_rec.Cont_Attribute15,
X_Cont_Attribute16 => rel_rec.Cont_Attribute16,
X_Cont_Attribute17 => rel_rec.Cont_Attribute17,
X_Cont_Attribute18 => rel_rec.Cont_Attribute18,
X_Cont_Attribute19 => rel_rec.Cont_Attribute19,
X_Cont_Attribute20 => rel_rec.Cont_Attribute20,
X_Cont_Information_Category => rel_rec.Cont_Information_Category,
X_Cont_Information1 => rel_rec.Cont_Information1,
X_Cont_Information2 => rel_rec.Cont_Information2,
X_Cont_Information3 => rel_rec.Cont_Information3,
X_Cont_Information4 => rel_rec.Cont_Information4,
X_Cont_Information5 => rel_rec.Cont_Information5,
X_Cont_Information6 => rel_rec.Cont_Information6,
X_Cont_Information7 => rel_rec.Cont_Information7,
X_Cont_Information8 => rel_rec.Cont_Information8,
X_Cont_Information9 => rel_rec.Cont_Information9,
X_Cont_Information10 => rel_rec.Cont_Information10,
X_Cont_Information11 => rel_rec.Cont_Information11,
X_Cont_Information12 => rel_rec.Cont_Information12,
X_Cont_Information13 => rel_rec.Cont_Information13,
X_Cont_Information14 => rel_rec.Cont_Information14,
X_Cont_Information15 => rel_rec.Cont_Information15,
X_Cont_Information16 => rel_rec.Cont_Information16,
X_Cont_Information17 => rel_rec.Cont_Information17,
X_Cont_Information18 => rel_rec.Cont_Information18,
X_Cont_Information19 => rel_rec.Cont_Information19,
X_Cont_Information20 => rel_rec.Cont_Information20,
X_Session_Date => X_Session_Date,
X_Date_Start => X_date_of_birth,
X_Start_Life_Reason_Id => rel_rec.start_life_reason_id,
X_Date_End => rel_rec.date_end,
X_End_Life_Reason_Id => rel_rec.end_life_reason_id,
X_Rltd_Per_Rsds_W_Dsgntr_Flag => rel_rec.rltd_per_rsds_w_dsgntr_flag,
X_Personal_Flag => rel_rec.personal_flag,
X_Sequence_Number => rel_rec.sequence_number,
X_Dependent_Flag => rel_rec.dependent_flag,
X_Beneficiary_Flag => rel_rec.beneficiary_flag
);
hr_utility.set_location('update_contact',3);
END Update_Contact;