[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_row(p_rowid VARCHAR2
,p_person_id NUMBER
,p_effective_start_date DATE
,p_effective_end_date DATE
,p_business_group_id NUMBER
,p_person_type_id NUMBER
,p_last_name VARCHAR2
,p_start_date DATE
,p_applicant_number IN OUT NOCOPY VARCHAR2
,p_comment_id NUMBER
,p_current_applicant_flag VARCHAR2
,p_current_emp_or_apl_flag VARCHAR2
,p_current_employee_flag VARCHAR2
,p_date_employee_data_verified DATE
,p_date_of_birth DATE
,p_email_address VARCHAR2
,p_employee_number IN OUT NOCOPY VARCHAR2
,p_expense_check_send_to_addr VARCHAR2
,p_first_name VARCHAR2
,p_full_name VARCHAR2
,p_known_as VARCHAR2
,p_marital_status VARCHAR2
,p_middle_names VARCHAR2
,p_nationality VARCHAR2
,p_national_identifier VARCHAR2
,p_previous_last_name VARCHAR2
,p_registered_disabled_flag VARCHAR2
,p_sex VARCHAR2
,p_title VARCHAR2
,p_suffix VARCHAR2
,p_vendor_id NUMBER
,p_work_telephone VARCHAR2
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_a_cat VARCHAR2
,p_a1 VARCHAR2
,p_a2 VARCHAR2
,p_a3 VARCHAR2
,p_a4 VARCHAR2
,p_a5 VARCHAR2
,p_a6 VARCHAR2
,p_a7 VARCHAR2
,p_a8 VARCHAR2
,p_a9 VARCHAR2
,p_a10 VARCHAR2
,p_a11 VARCHAR2
,p_a12 VARCHAR2
,p_a13 VARCHAR2
,p_a14 VARCHAR2
,p_a15 VARCHAR2
,p_a16 VARCHAR2
,p_a17 VARCHAR2
,p_a18 VARCHAR2
,p_a19 VARCHAR2
,p_a20 VARCHAR2
,p_a21 VARCHAR2
,p_a22 VARCHAR2
,p_a23 VARCHAR2
,p_a24 VARCHAR2
,p_a25 VARCHAR2
,p_a26 VARCHAR2
,p_a27 VARCHAR2
,p_a28 VARCHAR2
,p_a29 VARCHAR2
,p_a30 VARCHAR2
,p_last_update_date DATE
,p_last_updated_by NUMBER
,p_last_update_login NUMBER
,p_created_by NUMBER
,p_creation_date DATE
,p_i_cat VARCHAR2
,p_i1 VARCHAR2
,p_i2 VARCHAR2
,p_i3 VARCHAR2
,p_i4 VARCHAR2
,p_i5 VARCHAR2
,p_i6 VARCHAR2
,p_i7 VARCHAR2
,p_i8 VARCHAR2
,p_i9 VARCHAR2
,p_i10 VARCHAR2
,p_i11 VARCHAR2
,p_i12 VARCHAR2
,p_i13 VARCHAR2
,p_i14 VARCHAR2
,p_i15 VARCHAR2
,p_i16 VARCHAR2
,p_i17 VARCHAR2
,p_i18 VARCHAR2
,p_i19 VARCHAR2
,p_i20 VARCHAR2
,p_i21 VARCHAR2
,p_i22 VARCHAR2
,p_i23 VARCHAR2
,p_i24 VARCHAR2
,p_i25 VARCHAR2
,p_i26 VARCHAR2
,p_i27 VARCHAR2
,p_i28 VARCHAR2
,p_i29 VARCHAR2
,p_i30 VARCHAR2
,p_app_ass_status_type_id NUMBER
,p_emp_ass_status_type_id NUMBER
,p_system_person_type VARCHAR2
,p_s_system_person_type VARCHAR2
,p_hire_date DATE
,p_s_hire_date DATE
,p_s_date_of_birth DATE
,p_status in out nocopy VARCHAR2
,p_new_primary_id in out nocopy NUMBER
,p_update_primary in out nocopy VARCHAR2
,p_legislation_code VARCHAR2
,p_vacancy_id IN OUT NOCOPY NUMBER
,p_session_date date
,p_end_of_time date) is
--
l_period_of_service_id number; -- Period of Service id.
select PAY_PROPOSAL_ID
from per_pay_proposals
where change_date = p_s_hire_date
and assignment_id = (select assignment_id
from per_assignments_f
where person_id = p_person_id
and primary_flag = 'Y'
and effective_start_date = p_hire_date
and assignment_type = 'E'
);
select party_id
from per_all_people_f
where person_id = p_person_id
and p_session_date between effective_start_date
and effective_end_date;
select pps.period_of_service_id
into l_period_of_service_id
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.date_start = p_s_hire_date;
hr_utility.set_message_token('PROCEDURE','Update_row');
per_people9_pkg.update_old_person_row(p_person_id =>p_person_id
,p_session_date => p_session_date
,p_effective_start_date=>p_effective_start_date);
per_people9_pkg.insert_applicant_rows(p_person_id => p_person_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_business_group_id =>p_business_group_id
,p_app_ass_status_type_id => p_app_ass_status_type_id
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_last_update_date => p_last_update_date
,p_last_updated_by => p_last_updated_by
,p_last_update_login => p_last_update_login
,p_created_by => p_created_by
,p_creation_date => p_creation_date);
per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_business_group_id =>p_business_group_id
,p_emp_ass_status_type_id => p_emp_ass_status_type_id
,p_employee_number => p_employee_number
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_last_update_date => p_last_update_date
,p_last_updated_by => p_last_updated_by
,p_last_update_login => p_last_update_login
,p_created_by => p_created_by
,p_creation_date => p_creation_date
,p_adjusted_svc_date => NULL);
hr_utility.set_location('update_row - b4 MULTIPLE_CONTRACTS',1);
hr_utility.set_location('update_row - b4 CHOOSE_VAC',1);
hr_utility.set_location('update_row - b4 HIRE',1);
hr_utility.set_location('update_row - b4 hrhirapl',1);
,p_last_updated_by
,p_last_update_login
,p_effective_start_date
,p_end_of_time
,p_last_update_date
,p_update_primary
,p_employee_number
,l_set_of_books_id
,l_employ_emp_apl
,NULL
,p_session_date); -- Bug 3564129
hr_utility.set_location('update_row - after hrhirapl',2);
hr_utility.set_location('update_row - b4 update',1);
update per_people_f ppf
set ppf.person_id = p_person_id
,ppf.effective_start_date = p_effective_start_date
,ppf.effective_end_date = p_effective_end_date
,ppf.business_group_id = p_business_group_id
,ppf.person_type_id = p_person_type_id
,ppf.last_name = p_last_name
,ppf.start_date = p_start_date
,ppf.applicant_number = p_applicant_number
,ppf.comment_id = p_comment_id
,ppf.current_applicant_flag = p_current_applicant_flag
,ppf.current_emp_or_apl_flag = p_current_emp_or_apl_flag
,ppf.current_employee_flag = p_current_employee_flag
,ppf.date_employee_data_verified = p_date_employee_data_verified
,ppf.date_of_birth = p_date_of_birth
,ppf.email_address = p_email_address
,ppf.employee_number = p_employee_number
,ppf.expense_check_send_to_address = p_expense_check_send_to_addr
,ppf.first_name = p_first_name
,ppf.full_name = p_full_name
,ppf.known_as = p_known_as
,ppf.marital_status = p_marital_status
,ppf.middle_names = p_middle_names
,ppf.nationality = p_nationality
,ppf.national_identifier = p_national_identifier
,ppf.previous_last_name = p_previous_last_name
,ppf.registered_disabled_flag = p_registered_disabled_flag
,ppf.sex = p_sex
,ppf.title = p_title
,ppf.suffix = p_suffix
,ppf.vendor_id = p_vendor_id
-- ,ppf.work_telephone = p_work_telephone
,ppf.request_id = p_request_id
,ppf.program_application_id = p_program_application_id
,ppf.program_id = p_program_id
,ppf.program_update_date = p_program_update_date
,ppf.attribute_category = p_a_cat
,ppf.attribute1 = p_a1
,ppf.attribute2 = p_a2
,ppf.attribute3 = p_a3
,ppf.attribute4 = p_a4
,ppf.attribute5 = p_a5
,ppf.attribute6 = p_a6
,ppf.attribute7 = p_a7
,ppf.attribute8 = p_a8
,ppf.attribute9 = p_a9
,ppf.attribute10 = p_a10
,ppf.attribute11 = p_a11
,ppf.attribute12 = p_a12
,ppf.attribute13 = p_a13
,ppf.attribute14 = p_a14
,ppf.attribute15 = p_a15
,ppf.attribute16 = p_a16
,ppf.attribute17 = p_a17
,ppf.attribute18 = p_a18
,ppf.attribute19 = p_a19
,ppf.attribute20 = p_a20
,ppf.attribute21 = p_a21
,ppf.attribute22 = p_a22
,ppf.attribute23 = p_a23
,ppf.attribute24 = p_a24
,ppf.attribute25 = p_a25
,ppf.attribute26 = p_a26
,ppf.attribute27 = p_a27
,ppf.attribute28 = p_a28
,ppf.attribute29 = p_a29
,ppf.attribute30 = p_a30
,ppf.last_update_date = p_last_update_date
,ppf.last_updated_by = p_last_updated_by
,ppf.last_update_login = p_last_update_login
,ppf.created_by = p_created_by
,ppf.creation_date = p_creation_date
,ppf.per_information_category = p_i_cat
,ppf.per_information1 = p_i1
,ppf.per_information2 = p_i2
,ppf.per_information3 = p_i3
,ppf.per_information4 = p_i4
,ppf.per_information5 = p_i5
,ppf.per_information6 = p_i6
,ppf.per_information7 = p_i7
,ppf.per_information8 = p_i8
,ppf.per_information9 = p_i9
,ppf.per_information10 = p_i10
,ppf.per_information11 = p_i11
,ppf.per_information12 = p_i12
,ppf.per_information13 = p_i13
,ppf.per_information14 = p_i14
,ppf.per_information15 = p_i15
,ppf.per_information16 = p_i16
,ppf.per_information17 = p_i17
,ppf.per_information18 = p_i18
,ppf.per_information19 = p_i19
,ppf.per_information20 = p_i20
,ppf.per_information21 = p_i21
,ppf.per_information22 = p_i22
,ppf.per_information23 = p_i23
,ppf.per_information24 = p_i24
,ppf.per_information25 = p_i25
,ppf.per_information26 = p_i26
,ppf.per_information27 = p_i27
,ppf.per_information28 = p_i28
,ppf.per_information29 = p_i29
,ppf.per_information30 = p_i30
where ppf.rowid = p_rowid;
hr_utility.set_location('update_row - after update',1);
,p_start_date => p_last_update_date
);
hr_utility.set_location('update_row - after update',2);
per_people3_pkg.update_period(p_person_id =>p_person_id
,p_hire_date => p_s_hire_date
,p_new_hire_date =>p_hire_date);
hr_utility.set_location('update_row - after update',3);
hr_date_chk.update_hire_records(p_person_id
,p_applicant_number
,p_hire_date
,p_s_hire_date
,p_last_updated_by
,p_last_update_login);
update per_pay_proposals
set change_date = p_hire_date
where change_date = p_s_hire_date
and assignment_id = (select assignment_id
from per_assignments_f
where person_id = p_person_id
and primary_flag = 'Y'
and effective_start_date = p_hire_date
and assignment_type = 'E'
);
hr_utility.set_message_token('PROCEDURE','Update_row');
hr_utility.set_location('update_row - after update',5);
p_status := 'END'; -- Status required to end update loop on server
end update_row;
select '1'
from sys.dual
where exists (
select 'future assignment exists'
from per_people_f ppf
where ppf.person_id = p_person_id
and ppf.effective_start_date > p_effective_start_date
);
select 'Y'
from per_assignments_f paf
where paf.assignment_type = 'E'
and paf.supervisor_id = p_person_id
and p_new_hire_date > paf.effective_start_date
and paf.effective_end_date >= p_old_hire_date;
select 1
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.actual_termination_date is not null;
select pps.final_process_date
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pps1
where pps1.person_id = pps.person_id
and pps1.date_start = p_start_date;
select asg.assignment_id
from per_assignments_f asg
where asg.person_id = p_person_id
and asg.payroll_id is not null;
select date_start, period_of_service_id,actual_termination_date
from per_periods_of_service
where p_person_id = person_id
order by date_start desc ;-- fix for bug 4672540.
select effective_date
into l_session_date
from fnd_sessions
where session_id=userenv('SESSIONID');