[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Assignment_Id IN OUT NOCOPY NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Recruiter_Id NUMBER,
X_Grade_Id NUMBER,
X_Position_Id NUMBER,
X_Job_Id NUMBER,
X_Assignment_Status_Type_Id IN OUT NOCOPY NUMBER,
X_Payroll_Id NUMBER,
X_Location_Id NUMBER,
X_Person_Referred_By_Id NUMBER,
X_Supervisor_Id NUMBER,
X_Special_Ceiling_Step_Id NUMBER,
X_Person_Id NUMBER,
X_Employee_Number VARCHAR2,
X_Recruitment_Activity_Id NUMBER,
X_Source_Organization_Id NUMBER,
X_Organization_Id NUMBER,
X_People_Group_Id NUMBER,
X_Soft_Coding_Keyflex_Id NUMBER,
X_Vacancy_Id NUMBER,
X_Pay_Basis_Id NUMBER,
X_Assignment_Sequence IN OUT NOCOPY NUMBER,
X_Assignment_Type VARCHAR2,
X_Primary_Flag IN OUT NOCOPY VARCHAR2,
X_Application_Id NUMBER,
X_Assignment_Number IN OUT NOCOPY VARCHAR2,
X_Change_Reason VARCHAR2,
X_Comment_Id NUMBER,
X_Date_Probation_End DATE,
X_Default_Code_Comb_Id NUMBER,
X_Employment_Category VARCHAR2,
X_Frequency VARCHAR2,
X_Internal_Address_Line VARCHAR2,
X_Manager_Flag VARCHAR2,
X_Normal_Hours NUMBER,
X_Perf_Review_Period NUMBER,
X_Perf_Review_Period_Frequency VARCHAR2,
X_Period_Of_Service_Id NUMBER,
X_Probation_Period NUMBER,
X_Probation_Unit VARCHAR2,
X_Sal_Review_Period NUMBER,
X_Sal_Review_Period_Frequency VARCHAR2,
X_Set_Of_Books_Id NUMBER,
X_Source_Type VARCHAR2,
X_Time_Normal_Finish VARCHAR2,
X_Time_Normal_Start VARCHAR2,
X_Ass_Attribute_Category VARCHAR2,
X_Ass_Attribute1 VARCHAR2,
X_Ass_Attribute2 VARCHAR2,
X_Ass_Attribute3 VARCHAR2,
X_Ass_Attribute4 VARCHAR2,
X_Ass_Attribute5 VARCHAR2,
X_Ass_Attribute6 VARCHAR2,
X_Ass_Attribute7 VARCHAR2,
X_Ass_Attribute8 VARCHAR2,
X_Ass_Attribute9 VARCHAR2,
X_Ass_Attribute10 VARCHAR2,
X_Ass_Attribute11 VARCHAR2,
X_Ass_Attribute12 VARCHAR2,
X_Ass_Attribute13 VARCHAR2,
X_Ass_Attribute14 VARCHAR2,
X_Ass_Attribute15 VARCHAR2,
X_Ass_Attribute16 VARCHAR2,
X_Ass_Attribute17 VARCHAR2,
X_Ass_Attribute18 VARCHAR2,
X_Ass_Attribute19 VARCHAR2,
X_Ass_Attribute20 VARCHAR2,
X_Ass_Attribute21 VARCHAR2,
X_Ass_Attribute22 VARCHAR2,
X_Ass_Attribute23 VARCHAR2,
X_Ass_Attribute24 VARCHAR2,
X_Ass_Attribute25 VARCHAR2,
X_Ass_Attribute26 VARCHAR2,
X_Ass_Attribute27 VARCHAR2,
X_Ass_Attribute28 VARCHAR2,
X_Ass_Attribute29 VARCHAR2,
X_Ass_Attribute30 VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE,
X_Title VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM PER_ASSIGNMENTS_F
WHERE assignment_id = X_Assignment_Id;
CURSOR C2 IS SELECT per_assignments_s.nextval FROM sys.dual;
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';
hr_utility.set_location('per_assignments_v7_pkg.insert_row',10);
hr_utility.set_location('per_assignments_v7_pkg.insert_row',20);
hr_utility.set_location('per_assignments_v7_pkg.insert_row',30);
hr_utility.set_location('per_assignments_v7_pkg.insert_row',40);
hr_utility.set_location('per_assignments_v7_pkg.insert_row',50);
hr_utility.set_location('per_assignments_v7_pkg.insert_row',60);
INSERT INTO PER_ASSIGNMENTS_F(
assignment_id,
effective_start_date,
effective_end_date,
business_group_id,
recruiter_id,
grade_id,
position_id,
job_id,
assignment_status_type_id,
payroll_id,
location_id,
person_referred_by_id,
supervisor_id,
special_ceiling_step_id,
person_id,
recruitment_activity_id,
source_organization_id,
organization_id,
people_group_id,
soft_coding_keyflex_id,
vacancy_id,
pay_basis_id,
assignment_sequence,
assignment_type,
primary_flag,
application_id,
assignment_number,
change_reason,
comment_id,
date_probation_end,
default_code_comb_id,
employment_category,
frequency,
internal_address_line,
manager_flag,
normal_hours,
perf_review_period,
perf_review_period_frequency,
period_of_service_id,
probation_period,
probation_unit,
sal_review_period,
sal_review_period_frequency,
set_of_books_id,
source_type,
time_normal_finish,
time_normal_start,
ass_attribute_category,
ass_attribute1,
ass_attribute2,
ass_attribute3,
ass_attribute4,
ass_attribute5,
ass_attribute6,
ass_attribute7,
ass_attribute8,
ass_attribute9,
ass_attribute10,
ass_attribute11,
ass_attribute12,
ass_attribute13,
ass_attribute14,
ass_attribute15,
ass_attribute16,
ass_attribute17,
ass_attribute18,
ass_attribute19,
ass_attribute20,
ass_attribute21,
ass_attribute22,
ass_attribute23,
ass_attribute24,
ass_attribute25,
ass_attribute26,
ass_attribute27,
ass_attribute28,
ass_attribute29,
ass_attribute30,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
title
) VALUES (
X_Assignment_Id,
X_Effective_Start_Date,
X_Effective_End_Date,
X_Business_Group_Id,
X_Recruiter_Id,
X_Grade_Id,
X_Position_Id,
X_Job_Id,
X_Assignment_Status_Type_Id,
X_Payroll_Id,
X_Location_Id,
X_Person_Referred_By_Id,
X_Supervisor_Id,
X_Special_Ceiling_Step_Id,
X_Person_Id,
X_Recruitment_Activity_Id,
X_Source_Organization_Id,
X_Organization_Id,
X_People_Group_Id,
X_Soft_Coding_Keyflex_Id,
X_Vacancy_Id,
X_Pay_Basis_Id,
X_Assignment_Sequence,
X_Assignment_Type,
X_Primary_Flag,
X_Application_Id,
X_Assignment_Number,
X_Change_Reason,
X_Comment_Id,
X_Date_Probation_End,
X_Default_Code_Comb_Id,
X_Employment_Category,
X_Frequency,
X_Internal_Address_Line,
X_Manager_Flag,
X_Normal_Hours,
X_Perf_Review_Period,
X_Perf_Review_Period_Frequency,
X_Period_Of_Service_Id,
X_Probation_Period,
X_Probation_Unit,
X_Sal_Review_Period,
X_Sal_Review_Period_Frequency,
X_Set_Of_Books_Id,
X_Source_Type,
X_Time_Normal_Finish,
X_Time_Normal_Start,
X_Ass_Attribute_Category,
X_Ass_Attribute1,
X_Ass_Attribute2,
X_Ass_Attribute3,
X_Ass_Attribute4,
X_Ass_Attribute5,
X_Ass_Attribute6,
X_Ass_Attribute7,
X_Ass_Attribute8,
X_Ass_Attribute9,
X_Ass_Attribute10,
X_Ass_Attribute11,
X_Ass_Attribute12,
X_Ass_Attribute13,
X_Ass_Attribute14,
X_Ass_Attribute15,
X_Ass_Attribute16,
X_Ass_Attribute17,
X_Ass_Attribute18,
X_Ass_Attribute19,
X_Ass_Attribute20,
X_Ass_Attribute21,
X_Ass_Attribute22,
X_Ass_Attribute23,
X_Ass_Attribute24,
X_Ass_Attribute25,
X_Ass_Attribute26,
X_Ass_Attribute27,
X_Ass_Attribute28,
X_Ass_Attribute29,
X_Ass_Attribute30,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Created_By,
X_Creation_Date,
X_Title
);
hr_utility.set_location('per_assignments_v7_pkg.insert_row',70);
hr_utility.set_location('per_assignments_v7_pkg.insert_row',80);
END Insert_Row;
SELECT *
FROM PER_ASSIGNMENTS_F
WHERE rowid = X_Rowid
FOR UPDATE of Assignment_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Assignment_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Recruiter_Id NUMBER,
X_Grade_Id NUMBER,
X_Position_Id NUMBER,
X_Job_Id NUMBER,
X_Assignment_Status_Type_Id NUMBER,
X_Payroll_Id NUMBER,
X_Location_Id NUMBER,
X_Person_Referred_By_Id NUMBER,
X_Supervisor_Id NUMBER,
X_Special_Ceiling_Step_Id NUMBER,
X_Person_Id NUMBER,
X_Recruitment_Activity_Id NUMBER,
X_Source_Organization_Id NUMBER,
X_Organization_Id NUMBER,
X_People_Group_Id NUMBER,
X_Soft_Coding_Keyflex_Id NUMBER,
X_Vacancy_Id NUMBER,
X_Pay_Basis_Id NUMBER,
X_Assignment_Sequence NUMBER,
X_Assignment_Type VARCHAR2,
X_Primary_Flag VARCHAR2,
X_Application_Id NUMBER,
X_Assignment_Number VARCHAR2,
X_Change_Reason VARCHAR2,
X_Comment_Id NUMBER,
X_Date_Probation_End DATE,
X_Default_Code_Comb_Id NUMBER,
X_Employment_Category VARCHAR2,
X_Frequency VARCHAR2,
X_Internal_Address_Line VARCHAR2,
X_Manager_Flag VARCHAR2,
X_Normal_Hours NUMBER,
X_Perf_Review_Period NUMBER,
X_Perf_Review_Period_Frequency VARCHAR2,
X_Period_Of_Service_Id NUMBER,
X_Probation_Period NUMBER,
X_Probation_Unit VARCHAR2,
X_Sal_Review_Period NUMBER,
X_Sal_Review_Period_Frequency VARCHAR2,
X_Set_Of_Books_Id NUMBER,
X_Source_Type VARCHAR2,
X_Time_Normal_Finish VARCHAR2,
X_Time_Normal_Start VARCHAR2,
X_Ass_Attribute_Category VARCHAR2,
X_Ass_Attribute1 VARCHAR2,
X_Ass_Attribute2 VARCHAR2,
X_Ass_Attribute3 VARCHAR2,
X_Ass_Attribute4 VARCHAR2,
X_Ass_Attribute5 VARCHAR2,
X_Ass_Attribute6 VARCHAR2,
X_Ass_Attribute7 VARCHAR2,
X_Ass_Attribute8 VARCHAR2,
X_Ass_Attribute9 VARCHAR2,
X_Ass_Attribute10 VARCHAR2,
X_Ass_Attribute11 VARCHAR2,
X_Ass_Attribute12 VARCHAR2,
X_Ass_Attribute13 VARCHAR2,
X_Ass_Attribute14 VARCHAR2,
X_Ass_Attribute15 VARCHAR2,
X_Ass_Attribute16 VARCHAR2,
X_Ass_Attribute17 VARCHAR2,
X_Ass_Attribute18 VARCHAR2,
X_Ass_Attribute19 VARCHAR2,
X_Ass_Attribute20 VARCHAR2,
X_Ass_Attribute21 VARCHAR2,
X_Ass_Attribute22 VARCHAR2,
X_Ass_Attribute23 VARCHAR2,
X_Ass_Attribute24 VARCHAR2,
X_Ass_Attribute25 VARCHAR2,
X_Ass_Attribute26 VARCHAR2,
X_Ass_Attribute27 VARCHAR2,
X_Ass_Attribute28 VARCHAR2,
X_Ass_Attribute29 VARCHAR2,
X_Ass_Attribute30 VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Title VARCHAR2,
X_Current_application_id NUMBER DEFAULT NULL
) IS
--
cursor org is
select organization_id
from per_all_assignments_f
where assignment_id=X_Assignment_Id
and X_Effective_Start_Date between effective_start_date
and effective_end_date;
hr_utility.set_location('per_assignments_v7_pkg.update_row',10);
hr_utility.set_location('per_assignments_v7_pkg.update_row',20);
hr_utility.set_location('per_assignments_v7_pkg.update_row',30);
hr_utility.set_location('per_assignments_v7_pkg.update_row',40);
hr_utility.set_location('per_assignments_v7_pkg.update_row',50);
UPDATE PER_ASSIGNMENTS_F
SET
assignment_id = X_Assignment_Id,
effective_start_date = X_Effective_Start_Date,
effective_end_date = X_Effective_End_Date,
business_group_id = X_Business_Group_Id,
recruiter_id = X_Recruiter_Id,
grade_id = X_Grade_Id,
position_id = X_Position_Id,
job_id = X_Job_Id,
assignment_status_type_id = X_Assignment_Status_Type_Id,
payroll_id = X_Payroll_Id,
location_id = X_Location_Id,
person_referred_by_id = X_Person_Referred_By_Id,
supervisor_id = X_Supervisor_Id,
special_ceiling_step_id = X_Special_Ceiling_Step_Id,
person_id = X_Person_Id,
recruitment_activity_id = X_Recruitment_Activity_Id,
source_organization_id = X_Source_Organization_Id,
organization_id = X_Organization_Id,
people_group_id = X_People_Group_Id,
soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id,
vacancy_id = X_Vacancy_Id,
pay_basis_id = X_Pay_Basis_Id,
assignment_sequence = X_Assignment_Sequence,
assignment_type = X_Assignment_Type,
primary_flag = X_Primary_Flag,
application_id = X_Application_Id,
assignment_number = X_Assignment_Number,
change_reason = X_Change_Reason,
comment_id = X_Comment_Id,
date_probation_end = X_Date_Probation_End,
default_code_comb_id = X_Default_Code_Comb_Id,
employment_category = X_Employment_Category,
frequency = X_Frequency,
internal_address_line = X_Internal_Address_Line,
manager_flag = X_Manager_Flag,
normal_hours = X_Normal_Hours,
perf_review_period = X_Perf_Review_Period,
perf_review_period_frequency = X_Perf_Review_Period_Frequency,
period_of_service_id = X_Period_Of_Service_Id,
probation_period = X_Probation_Period,
probation_unit = X_Probation_Unit,
sal_review_period = X_Sal_Review_Period,
sal_review_period_frequency = X_Sal_Review_Period_Frequency
,
set_of_books_id = X_Set_Of_Books_Id,
source_type = X_Source_Type,
time_normal_finish = X_Time_Normal_Finish,
time_normal_start = X_Time_Normal_Start,
ass_attribute_category = X_Ass_Attribute_Category,
ass_attribute1 = X_Ass_Attribute1,
ass_attribute2 = X_Ass_Attribute2,
ass_attribute3 = X_Ass_Attribute3,
ass_attribute4 = X_Ass_Attribute4,
ass_attribute5 = X_Ass_Attribute5,
ass_attribute6 = X_Ass_Attribute6,
ass_attribute7 = X_Ass_Attribute7,
ass_attribute8 = X_Ass_Attribute8,
ass_attribute9 = X_Ass_Attribute9,
ass_attribute10 = X_Ass_Attribute10,
ass_attribute11 = X_Ass_Attribute11,
ass_attribute12 = X_Ass_Attribute12,
ass_attribute13 = X_Ass_Attribute13,
ass_attribute14 = X_Ass_Attribute14,
ass_attribute15 = X_Ass_Attribute15,
ass_attribute16 = X_Ass_Attribute16,
ass_attribute17 = X_Ass_Attribute17,
ass_attribute18 = X_Ass_Attribute18,
ass_attribute19 = X_Ass_Attribute19,
ass_attribute20 = X_Ass_Attribute20,
ass_attribute21 = X_Ass_Attribute21,
ass_attribute22 = X_Ass_Attribute22,
ass_attribute23 = X_Ass_Attribute23,
ass_attribute24 = X_Ass_Attribute24,
ass_attribute25 = X_Ass_Attribute25,
ass_attribute26 = X_Ass_Attribute26,
ass_attribute27 = X_Ass_Attribute27,
ass_attribute28 = X_Ass_Attribute28,
ass_attribute29 = X_Ass_Attribute29,
ass_attribute30 = X_Ass_Attribute30,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
title = X_Title
WHERE rowid = X_rowid;
hr_utility.set_location('per_assignments_v7_pkg.update_row',60);
hr_utility.set_location('per_assignments_v7_pkg.update_row',70);
hr_utility.set_location('per_assignments_v7_pkg.update_row',80);
hr_utility.set_location('per_assignments_v7_pkg.update_row',90);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
hr_utility.set_location('per_assignments_v7_pkg.delete_row',10);
DELETE FROM PER_ASSIGNMENTS_F
WHERE rowid = X_Rowid;
hr_utility.set_location('per_assignments_v7_pkg.delete_row',20);
END Delete_Row;
select Address_Line_1
,Address_Line_2
,Address_Line_3
,bill_to_site_flag
,Country
,Description
,Designated_receiver_id
,In_organization_flag
,Inactive_date
,Inventory_organization_id
,Office_site_flag
,Postal_Code
,Receiving_site_flag
,Region_1
,Region_2
,Region_3
,Ship_to_location_id
,Ship_to_site_flag
,Style
,Tax_name
,Telephone_number_1
,Telephone_number_2
,Telephone_number_3
,Town_or_city
,Attribute_category
,Attribute1
,Attribute2
,Attribute3
,Attribute4
,Attribute5
,Attribute6
,Attribute7
,Attribute8
,Attribute9
,Attribute10
,Attribute11
,Attribute12
,Attribute13
,Attribute14
,Attribute15
,Attribute16
,Attribute17
,Attribute18
,Attribute19
,Attribute20
from hr_locations
where location_id = X_location_id;
select 'Y'
from sys.dual
where exists (select 1 from
per_assignments_f
where person_id =X_person_id
and X_eff_dt between effective_start_date
and effective_end_date);
select 'Y'
from per_assignments_f p
where p.effective_start_date < x_effective_start_date
and p.effective_end_date <> hr_general.end_of_time
and p.assignment_id = x_assignment_id
and p.effective_start_date = (select max(p1.effective_start_date)
from per_assignments_f p1
where p1.assignment_id = p.assignment_id);
select 'Y'
from per_assignments_f p
where trunc(p.effective_start_date) > trunc(x_effective_start_date) -- #1862029
and p.assignment_id = x_assignment_id;