[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Person_Id NUMBER,
X_Effective_Start_Date IN OUT NOCOPY DATE,
X_Effective_End_Date IN OUT NOCOPY DATE,
X_Business_Group_Id NUMBER,
X_Person_Type_Id NUMBER,
X_Last_Name VARCHAR2,
X_Start_Date IN OUT NOCOPY DATE,
X_Hire_date DATE,
X_S_Hire_Date DATE,
X_Period_of_service_id NUMBER,
X_Termination_Date DATE,
X_S_Termination_Date DATE,
X_Applicant_Number VARCHAR2,
X_Comment_Id NUMBER,
X_Current_Applicant_Flag VARCHAR2,
X_Current_Emp_Or_Apl_Flag VARCHAR2,
X_Current_Employee_Flag VARCHAR2,
X_Date_Employee_Data_Verified DATE,
X_Date_Of_Birth DATE,
X_Email_Address VARCHAR2,
X_Employee_Number VARCHAR2,
X_Expense_Check_To_Address VARCHAR2,
X_First_Name VARCHAR2,
X_Full_Name VARCHAR2,
X_Known_As VARCHAR2,
X_Marital_Status VARCHAR2,
X_Middle_Names VARCHAR2,
X_Nationality VARCHAR2,
X_National_Identifier VARCHAR2,
X_Previous_Last_Name VARCHAR2,
X_Registered_Disabled_Flag VARCHAR2,
X_Sex VARCHAR2,
X_Title VARCHAR2,
X_Vendor_Id NUMBER,
X_Work_Telephone 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_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_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Current_Application_Id NUMBER, --DEFAULT NULL
X_Order_Name VARCHAR2,
X_Global_Name VARCHAR2,
X_Local_Name VARCHAR2
) IS
--
l_old_work_telephone per_phones.phone_number%TYPE;
select phone_number, phone_id, phn.object_version_number
from per_phones phn
--
-- Removed fnd_sessions_f table in From Clause
-- becuase it is not used in the data retrieval at all.
-- fnd_sessions f
--
where phn.parent_id = x_person_id
and phn.parent_table = 'PER_ALL_PEOPLE_F'
and phn.phone_type = 'W1'
and x_start_date between phn.date_from and
nvl(phn.date_to,x_start_date);
select party_id
from per_all_people_f
where person_id = x_person_id;
select *
from per_all_people_f
where person_id = x_person_id
and x_effective_start_date
between effective_start_date
and effective_end_date;
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',10);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',20);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',30);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',40);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',50);
UPDATE PER_PEOPLE_F
SET
person_id = X_Person_Id,
party_id = l_Party_Id,
effective_start_date = X_Effective_Start_Date,
effective_end_date = X_Effective_End_Date,
business_group_id = X_Business_Group_Id,
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,
start_date = X_Start_Date,
applicant_number = X_Applicant_Number,
comment_id = X_Comment_Id,
current_applicant_flag = X_Current_Applicant_Flag,
current_emp_or_apl_flag = X_Current_Emp_Or_Apl_Flag,
current_employee_flag = X_Current_Employee_Flag,
date_employee_data_verified = X_Date_Employee_Data_Verified,
date_of_birth = X_Date_Of_Birth,
email_address = X_Email_Address,
employee_number = X_Employee_Number,
expense_check_send_to_address = X_Expense_Check_To_Address,
first_name = X_First_Name,
full_name = X_Full_Name,
known_as = X_Known_As,
marital_status = X_Marital_Status,
middle_names = X_Middle_Names,
nationality = X_Nationality,
national_identifier = X_National_Identifier,
previous_last_name = X_Previous_Last_Name,
registered_disabled_flag = X_Registered_Disabled_Flag,
sex = X_Sex,
title = X_Title,
vendor_id = X_Vendor_Id,
-- The update of the work_telephone data is now done on the PER_PHONES table.
-- work_telephone = X_Work_Telephone,
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,
order_name = X_Order_Name, -- #3889584
global_name = X_Global_Name,
local_name = X_Local_Name,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login
WHERE rowid = X_rowid;
hr_utility.set_location('per_people_v7_pkg.update_row',55);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',60);
per_hrtca_merge.update_tca_person(p_Rec => l_person);
hr_utility.set_location('update_row - after update',1);
p_action => 'UPDATE');
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',70);
hr_phone_api.delete_phone(FALSE, l_phone_id, l_phn_object_version_number);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',80);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',90);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',95);
hr_phone_api.update_phone
(p_phone_id => l_phone_id,
p_phone_number => x_work_telephone,
p_date_to => null, -- 1020074 x_termination_date,
p_object_version_number => l_phn_object_version_number,
p_effective_date => x_effective_start_date); -- 1020074 x_start_date
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',100);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',110);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',120);
update_employee_rows(X_period_of_service_id
,X_person_id
,X_hire_date
,X_s_hire_date);
hr_utility.set_location('Entering : per_people_v7_pkg.update_row',130);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM PER_PEOPLE_F
WHERE rowid = X_Rowid;
END Delete_Row;
select ppt.person_type_id
, pst.current_applicant_flag
, pst.current_emp_or_apl_flag
, pst.current_employee_flag
from per_person_types ppt
, per_startup_person_types pst
where ppt.business_group_id = p_business_group_id -- Bug fix 3645987
and pst.system_person_type = ppt.system_person_type
and ppt.active_flag = 'Y'
and ppt.default_flag = 'Y'
and pst.system_person_type = 'EX_EMP';
INSERT INTO PER_ALL_PEOPLE_F(
person_id,
effective_start_date,
effective_end_date,
business_group_id,
person_type_id,
last_name,
start_date,
applicant_number,
comment_id,
current_applicant_flag,
current_emp_or_apl_flag,
current_employee_flag,
date_employee_data_verified,
date_of_birth,
email_address,
employee_number,
expense_check_send_to_address,
first_name,
full_name,
known_as,
marital_status,
middle_names,
nationality,
national_identifier,
previous_last_name,
registered_disabled_flag,
sex,
title,
vendor_id,
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,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
town_of_birth,
region_of_birth,
country_of_birth,
global_person_id,
party_id,
blood_type,
correspondence_language,
honors,
pre_name_adjunct,
rehire_authorizor,
rehire_recommendation,
resume_exists,
resume_last_updated,
second_passport_exists,
student_status,
suffix,
date_of_death,
uses_tobacco_flag,
fast_path_employee,
fte_capacity,
order_name,
global_name,
local_name
) VALUES (
l_Person_Id,
p_start_date,
p_end_date,
p_person_rec.Business_Group_Id,
hr_person_type_usage_info.get_default_person_type_id(l_person_type_id),
p_person_rec.Last_Name,
p_person_rec.Start_Date,
p_person_rec.Applicant_Number,
p_person_rec.Comment_Id,
l_Current_Applicant_Flag,
l_Current_Emp_Or_Apl_Flag,
l_Current_Employee_Flag,
p_person_rec.Date_Employee_Data_Verified,
p_person_rec.Date_Of_Birth,
p_person_rec.Email_Address,
p_person_rec.Employee_Number,
p_person_rec.expense_check_send_to_address,
p_person_rec.First_Name,
p_person_rec.Full_Name,
p_person_rec.Known_As,
p_person_rec.Marital_Status,
p_person_rec.Middle_Names,
p_person_rec.Nationality,
p_person_rec.National_Identifier,
p_person_rec.Previous_Last_Name,
p_person_rec.Registered_Disabled_Flag,
p_person_rec.Sex,
p_person_rec.Title,
p_person_rec.Vendor_Id,
p_person_rec.Attribute_Category,
p_person_rec.Attribute1,
p_person_rec.Attribute2,
p_person_rec.Attribute3,
p_person_rec.Attribute4,
p_person_rec.Attribute5,
p_person_rec.Attribute6,
p_person_rec.Attribute7,
p_person_rec.Attribute8,
p_person_rec.Attribute9,
p_person_rec.Attribute10,
p_person_rec.Attribute11,
p_person_rec.Attribute12,
p_person_rec.Attribute13,
p_person_rec.Attribute14,
p_person_rec.Attribute15,
p_person_rec.Attribute16,
p_person_rec.Attribute17,
p_person_rec.Attribute18,
p_person_rec.Attribute19,
p_person_rec.Attribute20,
p_person_rec.Attribute21,
p_person_rec.Attribute22,
p_person_rec.Attribute23,
p_person_rec.Attribute24,
p_person_rec.Attribute25,
p_person_rec.Attribute26,
p_person_rec.Attribute27,
p_person_rec.Attribute28,
p_person_rec.Attribute29,
p_person_rec.Attribute30,
p_person_rec.per_information_category,
p_person_rec.per_information1,
p_person_rec.per_information2,
p_person_rec.per_information3,
p_person_rec.per_information4,
p_person_rec.per_information5,
p_person_rec.per_information6,
p_person_rec.per_information7,
p_person_rec.per_information8,
p_person_rec.per_information9,
p_person_rec.per_information10,
p_person_rec.per_information11,
p_person_rec.per_information12,
p_person_rec.per_information13,
p_person_rec.per_information14,
p_person_rec.per_information15,
p_person_rec.per_information16,
p_person_rec.per_information17,
p_person_rec.per_information18,
p_person_rec.per_information19,
p_person_rec.per_information20,
p_person_rec.per_information21,
p_person_rec.per_information22,
p_person_rec.per_information23,
p_person_rec.per_information24,
p_person_rec.per_information25,
p_person_rec.per_information26,
p_person_rec.per_information27,
p_person_rec.per_information28,
p_person_rec.per_information29,
p_person_rec.per_information30,
p_person_rec.Last_Update_Date,
p_person_rec.Last_Updated_By,
p_person_rec.Last_Update_Login,
p_person_rec.Created_By,
p_person_rec.Creation_Date,
p_person_rec.town_of_birth,
p_person_rec.region_of_birth,
p_person_rec.country_of_birth,
p_person_rec.global_person_id,
p_person_rec.party_id,
p_person_rec.blood_type,
p_person_rec.correspondence_language,
p_person_rec.honors,
p_person_rec.pre_name_adjunct,
p_person_rec.rehire_authorizor,
p_person_rec.rehire_recommendation,
p_person_rec.resume_exists,
p_person_rec.resume_last_updated,
p_person_rec.second_passport_exists,
p_person_rec.student_status,
p_person_rec.suffix,
p_person_rec.date_of_death,
p_person_rec.uses_tobacco_flag,
p_person_rec.fast_path_employee,
p_person_rec.fte_capacity,
p_person_rec.order_name,
p_person_rec.global_name,
p_person_rec.local_name);
select actual_termination_date
from per_periods_of_service
where period_of_service_id = X_period_of_service_id
for update of actual_termination_date;
select effective_end_date
from per_assignments_f
where period_of_service_id = X_period_of_service_id
and effective_end_date = to_date('31-12-4712','DD-MM-YYYY')
for update of effective_end_date;
select ppt.person_type_id
, pst.current_applicant_flag
, pst.current_emp_or_apl_flag
, pst.current_employee_flag
from per_person_types ppt
, per_startup_person_types pst
where ppt.business_group_id = X_business_group_id --bug fix 3645987
and pst.system_person_type = ppt.system_person_type
and ppt.active_flag = 'Y'
and ppt.default_flag = 'Y'
and pst.system_person_type = 'EX_EMP';
select *
from per_people_f
where rowid = chartorowid(X_Rowid);
select *
from per_periods_of_service
where period_of_service_id = x_period_of_service_id;
update per_periods_of_service
set actual_termination_date = X_termination_date
, final_process_date = X_termination_date
, last_standard_process_date = X_termination_date
where period_of_service_id = X_period_of_service_id;
update per_assignments_f
set effective_end_date = X_termination_date
where period_of_service_id = X_period_of_service_id
and effective_end_date = to_date('31-12-4712','DD-MM-YYYY');
PER_PEOPLE_V14_PKG.Insert_Row(
X_Rowid => l_rowid,
X_Person_Id => Per_rec.Person_Id,
X_Party_Id => Per_rec.Party_Id,
X_Effective_Start_Date => X_termination_date + 1,
X_Effective_End_Date => to_date('31-12-4712','DD-MM-YYYY'),
X_Business_Group_Id => Per_rec.Business_Group_Id,
X_Person_Type_Id => l_person_type_id,
X_Last_Name => Per_rec.Last_Name,
X_Start_Date => Per_rec.Start_Date,
X_Applicant_Number => Per_rec.Applicant_Number,
X_Comment_Id => Per_rec.Comment_Id,
X_Current_Applicant_Flag=> l_current_applicant_flag,
X_Current_Emp_Or_Apl_Flag=> l_current_emp_or_apl_flag,
X_Current_Employee_Flag=> l_current_employee_flag,
X_Date_Employee_Data_Verified=> Per_rec.Date_Employee_Data_Verified,
X_Date_Of_Birth => Per_rec.Date_Of_Birth,
X_Email_Address => Per_rec.Email_Address,
X_Employee_Number => Per_rec.Employee_Number,
X_Expense_Check_To_Address=> Per_rec.expense_check_send_to_address,
X_First_Name => Per_rec.First_Name,
X_Full_Name => Per_rec.Full_Name,
X_Known_As => Per_rec.Known_As,
X_Marital_Status => Per_rec.Marital_Status,
X_Middle_Names => Per_rec.Middle_Names,
X_Nationality => Per_rec.Nationality,
X_National_Identifier => Per_rec.National_Identifier,
X_Previous_Last_Name => Per_rec.Previous_Last_Name,
X_Registered_Disabled_Flag=> Per_rec.Registered_Disabled_Flag,
X_Sex => Per_rec.Sex,
X_Title => Per_rec.Title,
X_Vendor_Id => Per_rec.Vendor_Id,
--
-- 115.22 : We need to ensure the per_phones table does not create rows.
-- Phone changes are handled by the "update_phone" api
--
X_Work_Telephone => null, -- 1020074 Per_rec.Work_Telephone,
X_Attribute_Category => Per_rec.Attribute_Category,
X_Attribute1 => Per_rec.Attribute1,
X_Attribute2 => Per_rec.Attribute2,
X_Attribute3 => Per_rec.Attribute3,
X_Attribute4 => Per_rec.Attribute4,
X_Attribute5 => Per_rec.Attribute5,
X_Attribute6 => Per_rec.Attribute6,
X_Attribute7 => Per_rec.Attribute7,
X_Attribute8 => Per_rec.Attribute8,
X_Attribute9 => Per_rec.Attribute9,
X_Attribute10 => Per_rec.Attribute10,
X_Attribute11 => Per_rec.Attribute11,
X_Attribute12 => Per_rec.Attribute12,
X_Attribute13 => Per_rec.Attribute13,
X_Attribute14 => Per_rec.Attribute14,
X_Attribute15 => Per_rec.Attribute15,
X_Attribute16 => Per_rec.Attribute16,
X_Attribute17 => Per_rec.Attribute17,
X_Attribute18 => Per_rec.Attribute18,
X_Attribute19 => Per_rec.Attribute19,
X_Attribute20 => Per_rec.Attribute20,
X_Attribute21 => Per_rec.Attribute21,
X_Attribute22 => Per_rec.Attribute22,
X_Attribute23 => Per_rec.Attribute23,
X_Attribute24 => Per_rec.Attribute24,
X_Attribute25 => Per_rec.Attribute25,
X_Attribute26 => Per_rec.Attribute26,
X_Attribute27 => Per_rec.Attribute27,
X_Attribute28 => Per_rec.Attribute28,
X_Attribute29 => Per_rec.Attribute29,
X_Attribute30 => Per_rec.Attribute30,
X_Per_Information_Category => Per_rec.Per_Information_Category,
X_Per_Information1 => Per_rec.Per_Information1,
X_Per_Information2 => Per_rec.Per_Information2,
X_Per_Information3 => Per_rec.Per_Information3,
X_Per_Information4 => Per_rec.Per_Information4,
X_Per_Information5 => Per_rec.Per_Information5,
X_Per_Information6 => Per_rec.Per_Information6,
X_Per_Information7 => Per_rec.Per_Information7,
X_Per_Information8 => Per_rec.Per_Information8,
X_Per_Information9 => Per_rec.Per_Information9,
X_Per_Information10 => Per_rec.Per_Information10,
X_Per_Information11 => Per_rec.Per_Information11,
X_Per_Information12 => Per_rec.Per_Information12,
X_Per_Information13 => Per_rec.Per_Information13,
X_Per_Information14 => Per_rec.Per_Information14,
X_Per_Information15 => Per_rec.Per_Information15,
X_Per_Information16 => Per_rec.Per_Information16,
X_Per_Information17 => Per_rec.Per_Information17,
X_Per_Information18 => Per_rec.Per_Information18,
X_Per_Information19 => Per_rec.Per_Information19,
X_Per_Information20 => Per_rec.Per_Information20,
X_Per_Information21 => Per_rec.Per_Information21,
X_Per_Information22 => Per_rec.Per_Information22,
X_Per_Information23 => Per_rec.Per_Information23,
X_Per_Information24 => Per_rec.Per_Information24,
X_Per_Information25 => Per_rec.Per_Information25,
X_Per_Information26 => Per_rec.Per_Information26,
X_Per_Information27 => Per_rec.Per_Information27,
X_Per_Information28 => Per_rec.Per_Information28,
X_Per_Information29 => Per_rec.Per_Information29,
X_Per_Information30 => Per_rec.Per_Information30,
X_Last_Update_Date => Per_rec.Last_Update_Date,
X_Last_Updated_By => Per_rec.Last_Updated_By,
X_Last_Update_Login => Per_rec.Last_Update_Login,
X_Created_By => Per_rec.Created_By,
X_Creation_Date => Per_rec.Creation_Date,
X_Period_Of_Service_Id => l_period_of_service_id,
X_town_of_birth => Per_rec.town_of_birth,
X_region_of_birth => Per_rec.region_of_birth,
X_country_of_birth => Per_rec.country_of_birth,
X_global_person_id => Per_rec.global_person_id,
X_Order_Name => Per_rec.order_Name, -- #3889584
X_Global_Name => Per_rec.global_name,
X_Local_Name => Per_rec.local_name);
procedure update_employee_rows(X_period_of_service_id NUMBER
,X_person_id NUMBER
,X_hire_date DATE
,X_s_hire_date DATE) IS
--
cursor period is
select date_start
from per_periods_of_service
where period_of_service_id = X_period_of_service_id
for update of date_start;
select effective_start_date
from per_assignments_f
where period_of_service_id = X_period_of_service_id
and effective_start_date = X_s_hire_date
and person_id = X_person_id
for update of effective_start_date;
select date_from
from per_addresses pa
where person_id = X_person_id
and date_from = X_s_hire_Date
for update of date_from;
select *
from per_periods_of_service
where period_of_service_id = x_period_of_service_id
and date_start = x_s_hire_date;
select *
from per_addresses
where person_id = X_person_id
and date_from = X_S_hire_date;
l_proc varchar2(20) := 'update_employee_rows';
update per_periods_of_service
set date_start = X_hire_date
where period_of_service_id = X_period_of_service_id
and date_start = X_s_hire_date;
update per_assignments_f
set effective_start_date = X_hire_date
where period_of_service_id = X_period_of_service_id
and effective_start_date = X_s_hire_date;
update per_addresses
set date_from = X_hire_date
where person_id = X_person_id
and date_from = X_S_hire_date;
END update_employee_rows;
is select Effective_End_Date
from per_people_f
where person_id = X_Person_Id
and nvl(Effective_End_Date,X_S_Hire_Date) = (X_S_Hire_Date - 1)
and nvl(Current_Employee_Flag,'N') <> 'Y'
for update of Effective_End_Date;
is select person_id
from per_people_f
where person_id = X_Person_Id
and effective_start_date = X_S_Hire_Date
for update of Person_Id;
select effective_start_date
from per_assignments_f
where period_of_service_id = X_period_of_service_id
and effective_start_date = X_s_hire_date
and person_id = X_person_id
for update of effective_start_date;
select date_start, business_group_id
from per_periods_of_service
where Period_of_service_Id = X_Period_of_service_Id
and date_start = X_S_Hire_Date
and actual_termination_date is null
for update of date_start;
select address_id,date_from
from per_addresses pa
where person_id = X_person_id
and date_from = X_s_hire_Date
and primary_flag = 'Y'
for update of date_from;
select date_from
from per_addresses pa
where person_id = X_Person_Id
and address_id <> lc_addr_id
and primary_flag = 'Y'
and lc_date_from between date_from and nvl(date_to,hr_api.g_eot);
select phone_id,phone_number,object_version_number
from per_phones
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = X_Person_Id
and date_from = X_S_Hire_Date
and phone_type = 'W1';
select max(actual_termination_date)
from per_periods_of_service
where date_start < cp_date_start
and person_id = X_person_id;
select * from per_all_people_f
where business_group_id = cp_bg_id
and person_id = X_person_id
and nvl(Effective_End_Date,cp_date) = cp_date;
update per_people_f
set Effective_End_Date = X_Hire_Date - 1
where Effective_End_Date = l_dummy_date
and person_id = X_Person_Id
and nvl(Current_Employee_Flag,'N') <> 'Y';
update per_people_f
set effective_start_date = X_Hire_Date,
start_date = X_hire_Date
where effective_start_date = X_S_Hire_Date
and person_id = X_person_id;
update per_assignments_f
set effective_start_date = X_hire_date
where period_of_service_id = X_period_of_service_id
and effective_start_date = X_s_hire_date;
update per_addresses
set date_from = X_Hire_Date
where person_id = X_Person_Id
and date_from = X_S_Hire_Date
and address_id = l_dummy_num;
,p_datetrack_update_mode => 'UPDATE_CHANGE_INSERT'
,p_person_type_id => hr_person_type_usage_info.get_default_person_type_id
(l_business_group_id
,'EX_EMP')
);
update per_periods_of_service
set date_start = X_Hire_Date
where Period_of_service_Id = X_Period_of_service_Id
and date_start = X_S_Hire_Date
and actual_termination_date is null;
hr_phone_api.update_phone
(p_phone_id => l_Phone_Id,
p_phone_number => l_work_telephone,
p_date_to => null,
p_object_version_number => l_Phone_OVN,
p_date_from => X_Hire_date,
p_effective_date => X_Hire_Date);
is select *
--changes for bug 5857638 here
from per_people_f
where person_id = X_Person_Id
for update of Person_Id;
select effective_end_date
from per_assignments_f
where person_id = X_person_id
and effective_end_date = X_s_termination_date
for update of effective_end_date;
select *
from per_periods_of_service
where person_id = x_person_id
and rownum = 1;
select actual_termination_date
from per_periods_of_service
where period_of_service_id = X_period_of_service_id
for update of actual_termination_date;
update per_assignments_f
set effective_end_date = hr_general.end_of_time
where current of assignment;
update per_periods_of_service
set actual_termination_date = null
, last_standard_process_date = null
, final_process_date = null
where current of period;
delete from per_people_f
--changes for bug 5857638 here
-- where effective_start_date = X_s_termination_date + 1
where effective_end_date = X_s_termination_date
--changes for bug 5857638 here
and person_id = X_person_id;
update per_people_f
-- set effective_end_date = hr_general.end_of_time
--changes for bug 5857638 here
set EFFECTIVE_START_DATE =l_EFFECTIVE_START_DATE,
CURRENT_EMP_OR_APL_FLAG =l_CURRENT_EMP_OR_APL_FLAG,
CURRENT_EMPLOYEE_FLAG =l_CURRENT_EMPLOYEE_FLAG,
PERSON_TYPE_ID =l_PERSON_TYPE_ID
-- where effective_end_date = X_s_termination_date
where effective_start_date = X_s_termination_date+1
--changes for bug 5857638 here
and person_id = X_person_id;
is select *
from per_people_f
where person_id = X_Person_Id
and X_Hire_date between
effective_start_Date and effective_end_date
for update of effective_end_date;
select *
from per_all_people_f
where person_id = x_person_id
and x_hire_date
between effective_start_date
and effective_end_date;
select *
from per_periods_of_service
where person_id = x_person_id
and rownum = 1;
select ppt.person_type_id
, pst.current_employee_flag
, pst.current_applicant_flag
, pst.current_emp_or_apl_flag
, to_date('31/12/4712','DD/MM/YYYY') effective_end_date
from per_person_types ppt
, per_startup_person_types pst
where ppt.business_group_id =X_Business_group_id --Bug fix 3645987
and ppt.system_person_type = pst.system_person_type
and ppt.system_person_type = 'EMP'
and ppt.default_flag = 'Y'
and pst.default_flag = 'Y';
select past.assignment_status_type_id
from per_assignment_status_types past
, per_ass_status_type_amends pasa
, per_business_groups pbg
where pasa.assignment_status_type_id(+) = past.assignment_status_type_id
and pasa.business_group_id(+) = X_Business_Group_id
and pbg.business_group_id = X_Business_Group_id
and nvl(past.business_group_id,X_Business_Group_id) = X_Business_Group_id
and nvl(past.legislation_code, pbg.legislation_code) = pbg.legislation_code
and nvl(pasa.active_flag,past.active_flag) = 'Y'
and nvl(pasa.default_flag,past.default_flag) = 'Y'
and nvl(pasa.per_system_status,past.per_system_status) = 'ACTIVE_ASSIGN';
UPDATE PER_ALL_PEOPLE_F
SET effective_start_date=X_hire_date,
effective_end_date= type_rec.effective_end_date,
business_group_id= per_rec.Business_Group_Id,
person_type_id= hr_person_type_usage_info.get_default_person_type_id(type_rec.Person_Type_Id),
last_name= per_rec.Last_Name,
start_date= per_rec.Start_Date,
applicant_number= per_rec.Applicant_Number,
comment_id= per_rec.Comment_Id,
current_applicant_flag= type_rec.Current_Applicant_Flag,
current_emp_or_apl_flag= type_rec.Current_Emp_Or_Apl_Flag,
current_employee_flag= type_rec.Current_Employee_Flag,
date_employee_data_verified= per_rec.Date_Employee_Data_Verified,
date_of_birth= per_rec.Date_Of_Birth,
email_address= per_rec.Email_Address,
employee_number= per_rec.Employee_Number,
expense_check_send_to_address= per_rec.expense_check_send_to_address,
first_name= per_rec.First_Name,
full_name= per_rec.Full_Name,
known_as= per_rec.Known_As,
marital_status= per_rec.Marital_Status,
middle_names= per_rec.Middle_Names,
nationality= per_rec.Nationality,
national_identifier= per_rec.National_Identifier,
previous_last_name= per_rec.Previous_Last_Name,
registered_disabled_flag= per_rec.Registered_Disabled_Flag,
sex= per_rec.Sex,
title= per_rec.Title,
vendor_id= per_rec.Vendor_Id,
attribute_category= per_rec.Attribute_Category,
attribute1= per_rec.Attribute1,
attribute2= per_rec.Attribute2,
attribute3= per_rec.Attribute3,
attribute4= per_rec.Attribute4,
attribute5= per_rec.Attribute5,
attribute6= per_rec.Attribute6,
attribute7= per_rec.Attribute7,
attribute8= per_rec.Attribute8,
attribute9= per_rec.Attribute9,
attribute10= per_rec.Attribute10,
attribute11= per_rec.Attribute11,
attribute12= per_rec.Attribute12,
attribute13= per_rec.Attribute13,
attribute14= per_rec.Attribute14,
attribute15= per_rec.Attribute15,
attribute16= per_rec.Attribute16,
attribute17= per_rec.Attribute17,
attribute18= per_rec.Attribute18,
attribute19= per_rec.Attribute19,
attribute20= per_rec.Attribute20,
attribute21= per_rec.Attribute21,
attribute22= per_rec.Attribute22,
attribute23= per_rec.Attribute23,
attribute24= per_rec.Attribute24,
attribute25= per_rec.Attribute25,
attribute26= per_rec.Attribute26,
attribute27= per_rec.Attribute27,
attribute28= per_rec.Attribute28,
attribute29= per_rec.Attribute29,
attribute30= per_rec.Attribute30,
per_information_category= per_rec.per_information_category,
per_information1= per_rec.per_information1,
per_information2= per_rec.per_information2,
per_information3= per_rec.per_information3,
per_information4= per_rec.per_information4,
per_information5= per_rec.per_information5,
per_information6= per_rec.per_information6,
per_information7= per_rec.per_information7,
per_information8= per_rec.per_information8,
per_information9= per_rec.per_information9,
per_information10= per_rec.per_information10,
per_information11= per_rec.per_information11,
per_information12= per_rec.per_information12,
per_information13= per_rec.per_information13,
per_information14= per_rec.per_information14,
per_information15= per_rec.per_information15,
per_information16= per_rec.per_information16,
per_information17= per_rec.per_information17,
per_information18= per_rec.per_information18,
per_information19= per_rec.per_information19,
per_information20= per_rec.per_information20,
per_information21= per_rec.per_information21,
per_information22= per_rec.per_information22,
per_information23= per_rec.per_information23,
per_information24= per_rec.per_information24,
per_information25= per_rec.per_information25,
per_information26= per_rec.per_information26,
per_information27= per_rec.per_information27,
per_information28= per_rec.per_information28,
per_information29= per_rec.per_information29,
per_information30= per_rec.per_information30,
last_update_date= per_rec.Last_Update_Date,
last_updated_by= per_rec.Last_Updated_By,
last_update_login= per_rec.Last_Update_Login,
created_by= per_rec.Created_By,
creation_date= per_rec.Creation_Date,
town_of_birth= per_rec.town_of_birth,
region_of_birth= per_rec.region_of_birth,
country_of_birth= per_rec.country_of_birth,
global_person_id= per_rec.global_person_id,
party_id= per_rec.party_id,
blood_type= per_rec.blood_type,
correspondence_language= per_rec.correspondence_language,
honors= per_rec.honors,
pre_name_adjunct= per_rec.pre_name_adjunct,
rehire_authorizor= per_rec.rehire_authorizor,
rehire_recommendation= per_rec.rehire_recommendation,
resume_exists= per_rec.resume_exists,
resume_last_updated= per_rec.resume_last_updated,
second_passport_exists= per_rec.second_passport_exists,
student_status= per_rec.student_status,
suffix= per_rec.suffix,
date_of_death= per_rec.date_of_death,
uses_tobacco_flag= per_rec.uses_tobacco_flag,
fast_path_employee= per_rec.fast_path_employee,
fte_capacity = per_rec.fte_capacity,
order_name = per_rec.order_name, -- #3889584
global_name = per_rec.global_name,
local_name = per_rec.local_name
where person_id = per_rec.person_id
and X_HIRE_DATE between effective_start_date
and effective_end_date;
Per_People_V14_Pkg.insert_period_of_service(X_Person_id => X_Person_Id
,X_Business_Group_Id => X_Business_Group_id
,X_Date_Start => X_HIRE_DATE
,X_Period_Of_Service_Id => l_period_of_service_id
);
,p_datetrack_update_mode =>'CORRECTION'
);
update per_people_f
set effective_end_date = X_Hire_date - 1
where current of person;
per_people_v14_pkg.insert_row(l_Rowid
,per_rec.Person_Id
,per_rec.Party_Id
,X_Hire_date
,type_rec.effective_end_date
,per_rec.Business_Group_Id
,type_rec.Person_Type_Id
,per_rec.Last_Name
,per_rec.Start_Date
,per_rec.Applicant_Number
,per_rec.Comment_Id
,type_rec.Current_Applicant_Flag
,type_rec.Current_Emp_Or_Apl_Flag
,type_rec.Current_Employee_Flag
,per_rec.Date_Employee_Data_Verified
,per_rec.Date_Of_Birth
,per_rec.Email_Address
,per_rec.Employee_Number
,per_rec.expense_check_send_to_address
,per_rec.First_Name
,per_rec.Full_Name
,per_rec.Known_As
,per_rec.Marital_Status
,per_rec.Middle_Names
,per_rec.Nationality
,per_rec.National_Identifier
,per_rec.Previous_Last_Name
,per_rec.Registered_Disabled_Flag
,per_rec.Sex
,per_rec.Title
,per_rec.Vendor_Id
--
-- 115.22: We need to ensure no rows get created in per_phones
-- table.
--
,null -- #1020074 per_rec.Work_Telephone
,per_rec.Attribute_Category
,per_rec.Attribute1
,per_rec.Attribute2
,per_rec.Attribute3
,per_rec.Attribute4
,per_rec.Attribute5
,per_rec.Attribute6
,per_rec.Attribute7
,per_rec.Attribute8
,per_rec.Attribute9
,per_rec.Attribute10
,per_rec.Attribute11
,per_rec.Attribute12
,per_rec.Attribute13
,per_rec.Attribute14
,per_rec.Attribute15
,per_rec.Attribute16
,per_rec.Attribute17
,per_rec.Attribute18
,per_rec.Attribute19
,per_rec.Attribute20
,per_rec.Attribute21
,per_rec.Attribute22
,per_rec.Attribute23
,per_rec.Attribute24
,per_rec.Attribute25
,per_rec.Attribute26
,per_rec.Attribute27
,per_rec.Attribute28
,per_rec.Attribute29
,per_rec.Attribute30
,per_rec.Per_Information_Category
,per_rec.Per_Information1
,per_rec.Per_Information2
,per_rec.Per_Information3
,per_rec.Per_Information4
,per_rec.Per_Information5
,per_rec.Per_Information6
,per_rec.Per_Information7
,per_rec.Per_Information8
,per_rec.Per_Information9
,per_rec.Per_Information10
,per_rec.Per_Information11
,per_rec.Per_Information12
,per_rec.Per_Information13
,per_rec.Per_Information14
,per_rec.Per_Information15
,per_rec.Per_Information16
,per_rec.Per_Information17
,per_rec.Per_Information18
,per_rec.Per_Information19
,per_rec.Per_Information20
,per_rec.Per_Information21
,per_rec.Per_Information22
,per_rec.Per_Information23
,per_rec.Per_Information24
,per_rec.Per_Information25
,per_rec.Per_Information26
,per_rec.Per_Information27
,per_rec.Per_Information28
,per_rec.Per_Information29
,per_rec.Per_Information30
,per_rec.Last_Update_Date
,per_rec.Last_Updated_By
,per_rec.Last_Update_Login
,per_rec.Created_By
,per_rec.Creation_Date
,per_rec.order_name -- #3889584
,per_rec.global_name
,per_rec.local_name
,l_period_of_service_id
,per_rec.town_of_birth
,per_rec.region_of_birth
,per_rec.country_of_birth
,per_rec.global_person_id);
PER_ASSIGNMENTS_V7_PKG.insert_row(X_Rowid =>l_rowid
,X_Assignment_Id =>l_ass_id
,X_Effective_Start_Date =>X_Hire_Date
,X_Effective_End_Date =>
type_rec.effective_end_date
,X_Business_Group_Id => X_business_group_id
,X_Recruiter_Id =>NULL
,X_Grade_Id =>NULL
,X_Position_Id =>NULL
,X_Job_Id =>NULL
,X_Assignment_Status_Type_Id =>
ass_rec.Assignment_Status_Type_Id
,X_Payroll_Id =>NULL
,X_Location_Id =>NULL
,X_Person_Referred_By_Id =>NULL
,X_Supervisor_Id =>NULL
,X_Special_Ceiling_Step_Id =>NULL
,X_Person_Id => X_person_id
,X_Employee_Number => per_rec.employee_number
,X_Recruitment_Activity_Id =>NULL
,X_Source_Organization_Id =>NULL
,X_Organization_Id => X_business_group_id
,X_People_Group_Id =>NULL
,X_Soft_Coding_Keyflex_Id =>NULL
,X_Vacancy_Id =>NULL
,X_Pay_Basis_Id =>NULL
,X_Assignment_Sequence =>l_ass_seq
,X_Assignment_Type => 'E'
,X_Primary_Flag => l_primary_flag
,X_Application_Id =>NULL
,X_Assignment_Number => l_ass_number
,X_Change_Reason =>NULL
,X_Comment_Id =>NULL
,X_Date_Probation_End =>NULL
,X_Default_Code_Comb_Id =>NULL
,X_Employment_Category =>NULL
,X_Frequency =>NULL
,X_Internal_Address_Line =>NULL
,X_Manager_Flag =>NULL
,X_Normal_Hours =>NULL
,X_Perf_Review_Period =>NULL
,X_Perf_Review_Period_Frequency =>NULL
,X_Period_Of_Service_Id =>l_period_of_service_id
,X_Probation_Period =>NULL
,X_Probation_Unit =>NULL
,X_Sal_Review_Period =>NULL
,X_Sal_Review_Period_Frequency =>NULL
,X_set_of_books_id =>X_Set_Of_Books_Id
,X_Source_Type =>NULL
,X_Time_Normal_Finish =>NULL
,X_Time_Normal_Start =>NULL
,X_Ass_Attribute_Category =>NULL
,X_Ass_Attribute1 =>NULL
,X_Ass_Attribute2 =>NULL
,X_Ass_Attribute3 =>NULL
,X_Ass_Attribute4 =>NULL
,X_Ass_Attribute5 =>NULL
,X_Ass_Attribute6 =>NULL
,X_Ass_Attribute7 =>NULL
,X_Ass_Attribute8 =>NULL
,X_Ass_Attribute9 =>NULL
,X_Ass_Attribute10 =>NULL
,X_Ass_Attribute11 =>NULL
,X_Ass_Attribute12 =>NULL
,X_Ass_Attribute13 =>NULL
,X_Ass_Attribute14 =>NULL
,X_Ass_Attribute15 =>NULL
,X_Ass_Attribute16 =>NULL
,X_Ass_Attribute17 =>NULL
,X_Ass_Attribute18 =>NULL
,X_Ass_Attribute19 =>NULL
,X_Ass_Attribute20 =>NULL
,X_Ass_Attribute21 =>NULL
,X_Ass_Attribute22 =>NULL
,X_Ass_Attribute23 =>NULL
,X_Ass_Attribute24 =>NULL
,X_Ass_Attribute25 =>NULL
,X_Ass_Attribute26 =>NULL
,X_Ass_Attribute27 =>NULL
,X_Ass_Attribute28 =>NULL
,X_Ass_Attribute29 =>NULL
,X_Ass_Attribute30 =>NULL
,X_Last_Update_Date =>NULL
,X_Last_Updated_By =>NULL
,X_Last_Update_Login =>NULL
,X_Created_By =>NULL
,X_Creation_Date =>NULL
,X_Title =>NULL
);
select 'Y'
from per_people_f ppf
,per_person_types ppt
,per_startup_person_types pst
where ppf.person_type_id = ppt.person_type_id
and ppf.person_id = p_person_id
and ppf.business_group_id +0 = ppt.business_group_id+0
and ppf.business_group_id +0 = p_business_group_id
and p_effective_start_date < ppf.effective_start_date
and ppt.system_person_type <> pst.system_person_type
and pst.system_person_type = p_system_person_type;
select 'Y'
from per_people_f p
where p.effective_start_date > p_hire_date
and p.person_id = p_person_id;
select 'Y'
from per_people_f p
where p.effective_start_date > p_hire_date - 1
and p.effective_start_date <> p_s_hire_date
and p.person_id = p_person_id;
select assignment_id
from per_assignments_f paf
, per_periods_of_service pos
where paf.person_id = pos.person_id
and paf.period_of_service_id = pos.period_of_service_id
and pos.date_start = p_s_hire_date
and pos.person_id = p_person_id;