[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_row(p_rowid in out nocopy VARCHAR2
,p_person_id in out nocopy 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_create_defaults_for VARCHAR2
,p_party_id number default null
,p_blood_type VARCHAR2 default NULL
,p_correspondence_language VARCHAR2 default NULL
,p_honors VARCHAR2 default NULL
,p_pre_name_adjunct VARCHAR2 default NULL
,p_rehire_authorizor VARCHAR2 default NULL
,p_rehire_recommendation VARCHAR2 default NULL
,p_resume_exists VARCHAR2 default NULL
,p_resume_last_updated DATE default NULL
,p_second_passport_exists VARCHAR2 default NULL
,p_student_status VARCHAR2 default NULL
,p_date_of_death DATE default NULL
,p_uses_tobacco_flag VARCHAR2 default NULL
,p_town_of_birth VARCHAR2 default NULL
,p_region_of_birth VARCHAR2 default NULL
,p_country_of_birth VARCHAR2 default NULL
,p_fast_path_employee VARCHAR2 default NULL
,p_fte_capacity VARCHAR2 default NULL) is
--
-- Define Cursor.
--
cursor c1 is select per_people_s.nextval
from sys.dual;
cursor c2 is select rowid
from per_people_f
where effective_start_date = p_effective_start_date
and effective_end_date = p_effective_end_date
and person_id = p_person_id;
select *
from per_all_people_f
where person_id = p_person_id
and p_effective_start_date
between effective_start_date
and effective_end_date;
insert into PER_PEOPLE_F
(person_id
,party_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
,suffix
,vendor_id
-- ,work_telephone -- Now done by the create_phone business process call
,request_id
,program_application_id
,program_id
,program_update_date
,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
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,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
,blood_type
,correspondence_language
,honors
,pre_name_adjunct
,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
,fte_capacity
,order_name
,global_name
,local_name )
values
(p_person_id
,p_party_id
,p_effective_start_date
,p_effective_end_date
,p_business_group_id
-- ,p_person_type_id
,hr_person_type_usage_info.get_default_person_type_id(p_person_type_id)
,p_last_name
,p_start_date
,p_applicant_number
,p_comment_id
,p_current_applicant_flag
,p_current_emp_or_apl_flag
,p_current_employee_flag
,p_date_employee_data_verified
,p_date_of_birth
,p_email_address
,p_employee_number
,p_expense_check_send_to_addr
,p_first_name
,l_full_name
,p_known_as
,p_marital_status
,p_middle_names
,p_nationality
,p_national_identifier
,p_previous_last_name
,p_registered_disabled_flag
,p_sex
,p_title
,p_suffix
,p_vendor_id
-- ,p_work_telephone -- Now done by the create_phone business process call
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,p_a_cat
,p_a1
,p_a2
,p_a3
,p_a4
,p_a5
,p_a6
,p_a7
,p_a8
,p_a9
,p_a10
,p_a11
,p_a12
,p_a13
,p_a14
,p_a15
,p_a16
,p_a17
,p_a18
,p_a19
,p_a20
,p_a21
,p_a22
,p_a23
,p_a24
,p_a25
,p_a26
,p_a27
,p_a28
,p_a29
,p_a30
,p_last_update_date
,p_last_updated_by
,p_last_update_login
,p_created_by
,p_creation_date
,p_i_cat
,p_i1
,p_i2
,p_i3
,p_i4
,p_i5
,p_i6
,p_i7
,p_i8
,p_i9
,p_i10
,p_i11
,p_i12
,p_i13
,p_i14
,p_i15
,p_i16
,p_i17
,p_i18
,p_i19
,p_i20
,p_i21
,p_i22
,p_i23
,p_i24
,p_i25
,p_i26
,p_i27
,p_i28
,p_i29
,p_i30
,p_blood_type
,p_correspondence_language
,p_honors
,p_pre_name_adjunct
,p_rehire_authorizor
,p_rehire_recommendation
,p_resume_exists
,p_resume_last_updated
,p_second_passport_exists
,p_student_status
,p_date_of_death
,p_uses_tobacco_flag
,p_town_of_birth
,p_region_of_birth
,p_country_of_birth
,p_fast_path_employee
,p_fte_capacity
,l_order_name
,l_global_name
,l_local_name );
update per_people_f
set party_id = l_person.party_id
where person_id = p_person_id;
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);
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);
end insert_row;
procedure delete_row(p_rowid VARCHAR2) is
begin
--
delete from per_people_f
where rowid=chartorowid(p_rowid);
end delete_row;
cursor per is select *
from per_people_f
where rowid = chartorowid(p_rowid)
for update nowait;
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 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);
hr_utility.set_location('update_row - after update',4);
p_status := 'END'; -- Status required to end update loop on server
end update_row;