The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO l_action_chk
FROM sys.dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act
WHERE act.assignment_id =
(select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and p_effective_date between
effective_start_date and effective_end_date
and primary_flag = 'Y'
and assignment_type = 'E')
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_type not in ('X','BEE') -- Bug 2898318. Exclude BEE
-- actions
AND pac.effective_date >= p_effective_date);
procedure update_row1(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 in 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 in 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
,p_work_schedule VARCHAR2
,p_correspondence_language VARCHAR2
,p_student_status VARCHAR2
,p_fte_capacity NUMBER
,p_on_military_service VARCHAR2
,p_second_passport_exists VARCHAR2
,p_background_check_status VARCHAR2
,p_background_date_check DATE
,p_blood_type VARCHAR2
,p_last_medical_test_date DATE
,p_last_medical_test_by VARCHAR2
,p_rehire_recommendation VARCHAR2
,p_rehire_reason VARCHAR2
,p_resume_exists VARCHAR2
,p_resume_last_updated DATE
,p_office_number VARCHAR2
,p_internal_location VARCHAR2
,p_mailstop VARCHAR2
,p_honors VARCHAR2
,p_pre_name_adjunct VARCHAR2
,p_hold_applicant_date_until DATE
,p_benefit_group_id NUMBER
,p_receipt_of_death_cert_date DATE
,p_coord_ben_med_pln_no VARCHAR2
,p_coord_ben_no_cvg_flag VARCHAR2
,p_uses_tobacco_flag VARCHAR2
,p_dpdnt_adoption_date DATE
,p_dpdnt_vlntry_svce_flag VARCHAR2
,p_date_of_death DATE
,p_original_date_of_hire DATE
,p_adjusted_svc_date DATE
,p_s_adjusted_svc_date DATE
,p_town_of_birth VARCHAR2
,p_region_of_birth VARCHAR2
,p_country_of_birth VARCHAR2
,p_global_person_id VARCHAR2
,p_npw_number IN OUT NOCOPY VARCHAR2
,p_current_npw_flag VARCHAR2
) is
l_sys_per_type varchar2(150);
update_row1(p_rowid
,p_person_id
,p_effective_start_date
,p_effective_end_date
,p_business_group_id
,p_person_type_id
,p_last_name
,p_start_date
,p_applicant_number
,p_comment_id
-- # 2264569:
,l_current_apl
--
,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
,p_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
,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_app_ass_status_type_id
,p_emp_ass_status_type_id
-- # 2264569
,l_sys_per_type
,p_s_system_person_type
,p_hire_date
,p_s_hire_date
,p_s_date_of_birth
,p_status
,p_new_primary_id
,p_update_primary
,p_legislation_code
,p_vacancy_id
,p_session_date
,p_end_of_time
,p_work_schedule
,p_correspondence_language
,p_student_status
,p_fte_capacity
,p_on_military_service
,p_second_passport_exists
,p_background_check_status
,p_background_date_check
,p_blood_type
,p_last_medical_test_date
,p_last_medical_test_by
,p_rehire_recommendation
,p_rehire_reason
,p_resume_exists
,p_resume_last_updated
,p_office_number
,p_internal_location
,p_mailstop
,p_honors
,p_pre_name_adjunct
,p_hold_applicant_date_until
,p_benefit_group_id
,p_receipt_of_death_cert_date
,p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag
,p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag
,p_date_of_death
,p_original_date_of_hire
,p_adjusted_svc_date
,p_s_adjusted_svc_date
,p_town_of_birth
,p_region_of_birth
,p_country_of_birth
,p_global_person_id
,p_npw_number
,p_current_npw_flag
-- #2264569
,l_table
,null
,null
,null);
end update_row1;
procedure update_row1(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
-- *** Start commented code for bug 2264569 *****
--,p_current_applicant_flag VARCHAR2
-- *** End commented code for bug 2264569 *******
--
-- *** Start new code for bug 2264569 ***********
-- should be in out parameter
,p_current_applicant_flag IN OUT NOCOPY VARCHAR2
-- *** End new code for bug 2264569 *************
,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
-- *** Start commented code for bug 2264569 ***
--,p_system_person_type VARCHAR2
-- *** End commented code for bug 2264569******
--
-- Start new code for bug 2264569 *************
-- should be in out parameter
,p_system_person_type IN OUT NOCOPY VARCHAR2
-- End new code for bug 2264569 **************
--
,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
,p_work_schedule VARCHAR2
,p_correspondence_language VARCHAR2
,p_student_status VARCHAR2
,p_fte_capacity NUMBER
,p_on_military_service VARCHAR2
,p_second_passport_exists VARCHAR2
,p_background_check_status VARCHAR2
,p_background_date_check DATE
,p_blood_type VARCHAR2
,p_last_medical_test_date DATE
,p_last_medical_test_by VARCHAR2
,p_rehire_recommendation VARCHAR2
,p_rehire_reason VARCHAR2
,p_resume_exists VARCHAR2
,p_resume_last_updated DATE
,p_office_number VARCHAR2
,p_internal_location VARCHAR2
,p_mailstop VARCHAR2
,p_honors VARCHAR2
,p_pre_name_adjunct VARCHAR2
,p_hold_applicant_date_until DATE
,p_benefit_group_id NUMBER
,p_receipt_of_death_cert_date DATE
,p_coord_ben_med_pln_no VARCHAR2
,p_coord_ben_no_cvg_flag VARCHAR2
,p_uses_tobacco_flag VARCHAR2
,p_dpdnt_adoption_date DATE
,p_dpdnt_vlntry_svce_flag VARCHAR2
,p_date_of_death DATE
,p_original_date_of_hire DATE
,p_adjusted_svc_date DATE
,p_s_adjusted_svc_date DATE
,p_town_of_birth VARCHAR2
,p_region_of_birth VARCHAR2
,p_country_of_birth VARCHAR2
,p_global_person_id VARCHAR2
,p_npw_number IN OUT NOCOPY VARCHAR2
,p_current_npw_flag VARCHAR2
-- Start new code for bug 2264569 ****************************
-- added pl/sql table
,p_tab IN OUT NOCOPY HR_EMPLOYEE_APPLICANT_API.t_ApplTable
-- End new code for bug 2264569 ******************************
,p_order_name IN VARCHAR2
,p_global_name IN VARCHAR2
,p_local_name IN VARCHAR2
) 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;
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;
select current_applicant_flag
from per_startup_person_types
where system_person_type = cp_person_type;
select email_address
from per_all_people_f
where rowid = p_rowid;
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',5);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',10);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',15);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',20);
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_location('PER_PEOPLE12_PKG.update_row1',25);
hr_utility.set_message_token('PROCEDURE','Update_row');
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',30);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',35);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',40);
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);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',55);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',60);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',65);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',70);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',75);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',80);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',95);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',100);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',101);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',102);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',105);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',110);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',115);
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 => p_adjusted_svc_date);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',120);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1201);
,p_datetrack_update_mode => 'CORRECTION'
);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1202);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',121);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',125);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',130);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',135);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',140);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',145);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',150);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',155);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',160);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',165);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',170);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',175);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',180);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',185);
hr_utility.set_location('update_row - b4 CHOOSE_VAC',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',240);
hr_utility.set_location('update_row - b4 HIRE',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',245);
if p_update_primary = 'Y'
and future_pactid_exists(p_person_id, p_effective_start_date)
then
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',246);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',250);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',255);
hr_utility.set_location('update_row - b4 hrhirapl',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',257);
hr_utility.set_location('update_row - b4 hrhirapl',2);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',259);
,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
,p_adjusted_svc_date
,p_session_date -- Bug 3564129
-- **** Start new code for bug 2264569 ******
,p_tab
-- **** End new code for bug 2264569 *******
);
hr_utility.set_location('update_row - after hrhirapl',2);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1211);
,p_datetrack_update_mode => 'CORRECTION'
);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1212);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',262);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',265);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',270);
hr_utility.set_location('update_row - b4 update',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_person_type_id,272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_s_system_person_type,272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_system_person_type,272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||hr_person_type_usage_info.get_default_person_type_id(
p_business_group_id,p_system_person_type),272);
update per_all_people_f ppf
-- Bug 6196362 Ends.
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.person_type_id =hr_person_type_usage_info.get_default_person_type_id( p_business_group_id ,p_system_person_type) bug 6848958
,ppf.person_type_id =decode( p_system_person_type,'CWK',
hr_person_type_usage_info.get_default_person_type_id(
p_business_group_id,'OTHER'),
hr_person_type_usage_info.get_default_person_type_id(
p_business_group_id,p_system_person_type)) -- fix for bug6848958
,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
,ppf.work_schedule = p_work_schedule
,ppf.correspondence_language = p_correspondence_language
,ppf.student_status = p_student_status
,ppf.fte_capacity = p_fte_capacity
,ppf.on_military_service = p_on_military_service
,ppf.second_passport_exists = p_second_passport_exists
,ppf.background_check_status = p_background_check_status
,ppf.background_date_check = p_background_date_check
,ppf.blood_type = p_blood_type
,ppf.last_medical_test_date = p_last_medical_test_date
,ppf.last_medical_test_by = p_last_medical_test_by
,ppf.rehire_recommendation = p_rehire_recommendation
,ppf.rehire_reason = p_rehire_reason
,ppf.resume_exists = p_resume_exists
,ppf.resume_last_updated = p_resume_last_updated
,ppf.office_number = p_office_number
,ppf.internal_location = p_internal_location
,ppf.mailstop = p_mailstop
,ppf.honors = p_honors
,ppf.pre_name_adjunct = p_pre_name_adjunct
,ppf.hold_applicant_date_until = p_hold_applicant_date_until
,ppf.benefit_group_id = p_benefit_group_id
,ppf.receipt_of_death_cert_date = p_receipt_of_death_cert_date
,ppf.coord_ben_med_pln_no = p_coord_ben_med_pln_no
,ppf.coord_ben_no_cvg_flag = p_coord_ben_no_cvg_flag
,ppf.uses_tobacco_flag = p_uses_tobacco_flag
,ppf.dpdnt_adoption_date = p_dpdnt_adoption_date
,ppf.dpdnt_vlntry_svce_flag = p_dpdnt_vlntry_svce_flag
,ppf.date_of_death = p_date_of_death
,ppf.original_date_of_hire = p_original_date_of_hire
,ppf.town_of_birth = p_town_of_birth
,ppf.region_of_birth = p_region_of_birth
,ppf.country_of_birth = p_country_of_birth
,ppf.global_person_id = p_global_person_id
,ppf.party_id = l_party_id
,ppf.npw_number = p_npw_number
,ppf.current_npw_flag = p_current_npw_flag
,ppf.order_name = p_order_name -- #3889584
,ppf.global_name = p_global_name
,ppf.local_name = p_local_name
where ppf.rowid = p_rowid;
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',275);
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('PER_PEOPLE12_PKG.update_row1',280);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',282);
,p_start_date => p_last_update_date
);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',285);
hr_utility.set_location('update_row - after update',2);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',290);
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
,p_adjusted_svc_date => p_adjusted_svc_date);
hr_utility.set_location('update_row - after update',3);
hr_utility.set_location('update_row - after update',4);
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('PER_PEOPLE12_PKG.update_row1',295);
hr_utility.set_location('update_row - after update',5);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',305);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',310);
p_action => 'UPDATE');
p_status := 'END'; -- Status required to end update loop on server
hr_utility.set_location('Leaving PER_PEOPLE12_PKG.update_row1',315);
end update_row1;
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.last_standard_process_date,
pps.final_process_date
--
-- 115.67 (END)
--
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 bus.legislation_code
FROM per_people_f per
,per_business_groups bus
WHERE per.person_id = p_person_id
AND per.business_group_id+0 = bus.business_group_id
AND p_start_date BETWEEN per.effective_start_date
AND per.effective_end_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 'Y'
into l_warn
from dual
where exists
(select null
from pay_element_entries_f ee,
pay_element_links_f el,
pay_element_types_f et
where ee.assignment_id in
(select assignment_id
from per_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date between l_earlier_date and l_later_date)
and ee.element_link_id = el.element_link_id
and el.element_type_id = et.element_type_id
and et.processing_type = 'R');
select *
from per_all_people_f
where person_id = c_person_id
order by effective_start_date asc;
select 'Y'
from dual
where exists(
select 1
from per_all_people_f per
where per.person_id = c_person_id
and c_effective_date between per.effective_start_date and per.effective_end_date
);
select ppt.system_person_type
from per_person_types ppt,
per_all_people_f ppf
where ppf.person_id = c_person_id
and ppf.person_type_id = ppt.person_type_id;
select address_id
from per_addresses
where person_id = c_person_id
and date_from = c_date_from;
select ppt.person_type_id
from per_person_types ppt
where ppt.business_group_id = c_business_group_id
and ppt.default_flag = 'Y'
and ppt.active_flag = 'Y'
and ppt.system_person_type = 'OTHER';
update per_addresses
set date_from = p_cov_date_start
where address_id = l_address_id.address_id;
update per_all_people_f
set effective_start_date = p_cov_date_start,
start_date = p_cov_date_start
where person_id = p_person_id
and effective_start_date = l_per_rec.effective_start_date;
update per_all_people_f
set start_date = p_cov_date_start
where person_id = p_person_id;
update per_person_type_usages_f ptu
set ptu.effective_start_date = p_cov_date_start
where ptu.person_id = p_person_id
and ptu.person_type_id in (select ppt.person_type_id
from per_person_types ppt
where ppt.system_person_type = 'OTHER');
insert into per_all_people_f(
person_id,
effective_start_date,
effective_end_date,
business_group_id,
person_type_id,
last_name,
start_date,
comment_id,
current_applicant_flag,
current_emp_or_apl_flag,
current_employee_flag,
date_of_birth,
first_name,
full_name,
middle_names,
sex,
title,
pre_name_adjunct,
suffix,
national_identifier,
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,
known_as
)
values(
l_per_rec.person_id,
l_per_rec.effective_start_date,
l_per_rec.effective_end_date,
l_per_rec.business_group_id,
l_per_rec.person_type_id,
l_per_rec.last_name,
l_per_rec.start_date,
l_per_rec.comment_id,
l_per_rec.current_applicant_flag,
l_per_rec.current_emp_or_apl_flag,
l_per_rec.current_employee_flag,
l_per_rec.date_of_birth,
l_per_rec.first_name,
l_per_rec.full_name,
l_per_rec.middle_names,
l_per_rec.sex,
l_per_rec.title,
l_per_rec.pre_name_adjunct,
l_per_rec.suffix,
l_per_rec.national_identifier,
l_per_rec.attribute_category,
l_per_rec.attribute1,
l_per_rec.attribute2,
l_per_rec.attribute3,
l_per_rec.attribute4,
l_per_rec.attribute5,
l_per_rec.attribute6,
l_per_rec.attribute7,
l_per_rec.attribute8,
l_per_rec.attribute9,
l_per_rec.attribute10,
l_per_rec.attribute11,
l_per_rec.attribute12,
l_per_rec.attribute13,
l_per_rec.attribute14,
l_per_rec.attribute15,
l_per_rec.attribute16,
l_per_rec.attribute17,
l_per_rec.attribute18,
l_per_rec.attribute19,
l_per_rec.attribute20,
l_per_rec.attribute21,
l_per_rec.attribute22,
l_per_rec.attribute23,
l_per_rec.attribute24,
l_per_rec.attribute25,
l_per_rec.attribute26,
l_per_rec.attribute27,
l_per_rec.attribute28,
l_per_rec.attribute29,
l_per_rec.attribute30,
l_per_rec.per_information_category,
l_per_rec.per_information1,
l_per_rec.per_information2,
l_per_rec.per_information3,
l_per_rec.per_information4,
l_per_rec.per_information5,
l_per_rec.per_information6,
l_per_rec.per_information7,
l_per_rec.per_information8,
l_per_rec.per_information9,
l_per_rec.per_information10,
l_per_rec.per_information11,
l_per_rec.per_information12,
l_per_rec.per_information13,
l_per_rec.per_information14,
l_per_rec.per_information15,
l_per_rec.per_information16,
l_per_rec.per_information17,
l_per_rec.per_information18,
l_per_rec.per_information19,
l_per_rec.per_information20,
l_per_rec.per_information21,
l_per_rec.per_information22,
l_per_rec.per_information23,
l_per_rec.per_information24,
l_per_rec.per_information25,
l_per_rec.per_information26,
l_per_rec.per_information27,
l_per_rec.per_information28,
l_per_rec.per_information29,
l_per_rec.per_information30,
l_per_rec.known_as
);
update per_all_people_f
set start_date = l_per_rec.start_date
where person_id = l_per_rec.person_id;
select per_person_type_usages_s.nextval into l_ptu_nextval
from sys.dual;
insert into per_person_type_usages_f(
PERSON_TYPE_USAGE_ID,
PERSON_ID,
PERSON_TYPE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
OBJECT_VERSION_NUMBER
)
values
(l_ptu_nextval,
l_per_rec.person_id,
l_per_rec.person_type_id,
l_per_rec.effective_start_date,
l_per_rec.effective_end_date,
l_object_version_number
);
select contact_person_id, min(nvl(date_start,hr_api.g_sot)) date_start
from per_contact_relationships
where person_id = c_person_id
group by contact_person_id;
select ctr.person_id, min(nvl(ctr.date_start,hr_api.g_sot)) date_start
from per_contact_relationships ctr
,per_person_type_usages_f ptu
,per_person_types ppt
where ctr.contact_person_id = c_contact_person_id
and ctr.person_id = ptu.person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EMP'
group by ctr.person_id;
select max(date_start)
from per_periods_of_service
where person_id = c_person_id;