[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 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_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Current_Application_Id NUMBER DEFAULT NULL
) IS
--
-- 3889584: We need to derive the person names when updating the person record
-- This cursor allows the retrieval of all other name components that
-- are needed when calling the derive person names function
--
cursor csr_get_person_details is
select *
from per_all_people_f
where rowid = X_rowid;
UPDATE PER_PEOPLE_F
SET
person_id = X_Person_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,
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 = l_full_name, -- 3889584: 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,
-- 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,
order_name = l_order_name, -- 3889584
global_name = l_global_name,
local_name = l_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;
update_employee_rows(X_period_of_service_id
,X_person_id
,X_hire_date
,X_s_hire_date);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM PER_PEOPLE_F
WHERE rowid = X_Rowid;
END Delete_Row;
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 -- Perf Fix 3648655
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);
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,
X_Work_Telephone => 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_Suffix => Per_rec.suffix,
X_Pre_Name_Adjunct => Per_rec.pre_name_adjunct,
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;
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 person_id
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 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 = to_date('31-12-4712','DD-MM-YYYY')
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
where effective_start_date = X_s_termination_date + 1
and person_id = X_person_id;
update per_people_f
set effective_end_date = to_date ('31-12-4712','DD-MM-YYYY')
where effective_end_date = X_s_termination_date
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 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 -- Perf Fix 3648655
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_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
,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_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;