[Home] [Help]
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_row2(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
and p_effective_start_date
between effective_start_date
and effective_end_date; /* Fix for Bug 7442246 */
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;
select business_group_id,per_information1
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 * from per_all_people_f
where person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date;
select 'Y' from per_all_people_f
where person_id = p_person_id
and effective_start_date > p_eff_start_date;
select date_received,date_end
from per_applications
where person_id= p_person_id and date_received > p_hire_date;
select 'Y' from per_applications
where person_id = p_person_id
and date_received = p_date_end +1;
select 'Y' from per_person_type_usages_f
where person_id = p_person_id
and person_type_id = hr_person_type_usage_info.get_default_person_type_id(p_business_group_id,'EX_APL')
and effective_start_date > p_hire_date;
select assignment_id, effective_start_date, effective_end_date
from per_all_assignments_f
where person_id = p_person_id
and assignment_id <> p_new_primary_id
and application_id = p_application_id
and effective_start_date > greatest(p_hire_date,p_session_date)
and assignment_type = 'A'
order by effective_start_date;
select application_id, date_end from per_applications
where person_id = p_person_id
and p_asg_start_date between date_received and nvl(date_end,hr_api.g_eot);
select distinct application_id
from per_all_assignments_f
where person_id = p_person_id
and assignment_id = p_new_primary_id;
fnd_message.set_token('PROCEDURE','PER_PEOPLE12_PKG.update_row2');
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',5);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',10);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',15);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',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_row2',25);
hr_utility.set_message_token('PROCEDURE','update_row2');
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',30);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',35);
-- a global version of session date to update the rows (not good)
--
-- VT 08/13/96
if p_session_date <> p_effective_start_date then
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',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);
--on PRE-UPDATE always
-- if p_current_employee_flag = 'Y' and
-- p_current_applicant_flag is null and
-- p_employee_number is not null then
-- null;
-- hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',45);
-- hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',50);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',55);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',60);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',65);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',70);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',75);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',80);
-- hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',85);
-- Insert the default applicant row and applicant
-- assignment.
--
-- VT 08/13/96
--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);
--hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',90);
--hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',92);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',95);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',100);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',101);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',102);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',105);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',110);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',115);
-- Insert the default period_of service and assignment
-- rows.
--
-- VT 08/13/96
per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => hr_api.g_eot -- ER FPT
,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_row2',120);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1201);
,p_datetrack_update_mode => 'CORRECTION'
);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1202);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',121);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',125);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',126);
-- there should not be any updates to the asgt on which it's hired >= hire date
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',127);
-- there should not be any updates to the asgt on which it's hired or the appln chosen to End >= hire date
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',128);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',130);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',135);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',140);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',145);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',150);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',155);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',160);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',165);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',170);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',175);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',180);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',185);
hr_utility.set_location('update_row2 - b4 CHOOSE_VAC',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',240);
hr_utility.set_location('update_row2 - b4 HIRE',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',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_row2',246);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',250);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',255);
hr_utility.set_location('update_row2 - b4 hrhirapl',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',257);
hr_utility.set_location('update_row2 - b4 hrhirapl',2);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',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 *******
);
select max(effective_end_date) into l_apl_end_date
from per_all_assignments_f
where person_id = p_person_id
and assignment_id = apl.assignment_id
and assignment_type = 'A';
SELECT per_applications_s.nextval into l_new_application_id FROM sys.dual;
INSERT INTO PER_APPLICATIONS(
application_id,
business_group_id,
person_id,
date_received,
date_end)
VALUES (l_new_application_id,p_business_group_id,p_person_id,apl.effective_start_date,l_apl_end_date);
update per_applications
set date_end = l_apl_end_date
where person_id = p_person_id
and application_id = l_cur_apl_id;
-- update the asgt with the application ID
update per_all_assignments_f
set application_id = l_cur_apl_id
where person_id = p_person_id
and assignment_id = apl.assignment_id
and effective_start_date = apl.effective_start_date
and effective_end_date = apl.effective_end_date;
hr_utility.set_location('update_row2 - after hrhirapl',2);
-- Update the system person type to EMP_APL if user is keeping active APPLS.
if hr_employee_applicant_api.retain_exists(p_tab) then
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',260);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1211);
,p_datetrack_update_mode => 'CORRECTION'
);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1212);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',260);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',262);
-- date track mode is set to UPDATE_CHANGE_INSERT when future EX_APL records exist
open chk_fut_ex_apl(greatest(p_hire_date,p_session_date));
l_datetrack_mode := hr_api.g_update_change_insert;
l_datetrack_mode := hr_api.g_update;
,p_datetrack_update_mode => l_datetrack_mode
);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 call enthn pkg',262);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',265);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',270);
hr_utility.set_location('update_row2 - b4 update',1);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||p_person_type_id,272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||p_s_system_person_type,272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||p_system_person_type,272);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||hr_person_type_usage_info.get_default_person_type_id(
p_business_group_id,p_system_person_type),272);
-- update per_people_f ppf
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_row2',275);
SELECT ppt.system_person_type
INTO l_next_person_type
FROM per_all_people_f per, per_person_types ppt
WHERE per.person_id = p_person_id
AND per.person_type_id = ppt.person_type_id
AND per.effective_start_date = p_effective_end_date + 1;
UPDATE per_all_people_f
SET effective_end_date = (
SELECT effective_end_date
FROM per_all_people_f
WHERE effective_start_date = p_effective_end_date + 1
AND person_id = p_person_id
)
WHERE rowid = p_rowid;
DELETE
FROM per_all_people_f
WHERE person_id = p_person_id
AND effective_start_date = p_effective_end_date + 1;
UPDATE per_all_people_f
SET person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EMP_APL')
,current_employee_flag = 'Y'
,employee_number = p_employee_number
,original_date_of_hire = p_original_date_of_hire
WHERE person_id = p_person_id
AND effective_start_date > p_effective_end_date
AND (person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'APL')
OR person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EX_EMP_APL'));
UPDATE per_all_people_f
SET person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EMP')
,current_employee_flag = 'Y'
,current_emp_or_apl_flag = 'Y'
,employee_number = p_employee_number
,original_date_of_hire = p_original_date_of_hire
WHERE person_id = p_person_id
AND effective_start_date > p_effective_end_date
AND (person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EX_APL')
OR person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EX_EMP'));
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',276);
update per_all_people_f
set person_type_id = hr_person_type_usage_info.get_default_person_type_id(p_business_group_id,'EMP')
,current_applicant_flag = null
where person_id = p_person_id
and effective_start_date between p_effective_end_date and l_first_apl_date;
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',277);
select object_version_number,effective_start_date into l_per_latest_ovn,l_effective_start_date
from per_all_people_f
where person_id = p_person_id
and apl.date_received between effective_start_date and effective_end_date;
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_mode := 'UPDATE';
,p_program_update_date => per_rec.program_update_date
,p_attribute_category => per_rec.attribute_category
,p_attribute1 => per_rec.attribute1
,p_attribute2 => per_rec.attribute2
,p_attribute3 => per_rec.attribute3
,p_attribute4 => per_rec.attribute4
,p_attribute5 => per_rec.attribute5
,p_attribute6 => per_rec.attribute6
,p_attribute7 => per_rec.attribute7
,p_attribute8 => per_rec.attribute8
,p_attribute9 => per_rec.attribute9
,p_attribute10 => per_rec.attribute10
,p_attribute11 => per_rec.attribute11
,p_attribute12 => per_rec.attribute12
,p_attribute13 => per_rec.attribute13
,p_attribute14 => per_rec.attribute14
,p_attribute15 => per_rec.attribute15
,p_attribute16 => per_rec.attribute16
,p_attribute17 => per_rec.attribute17
,p_attribute18 => per_rec.attribute18
,p_attribute19 => per_rec.attribute19
,p_attribute20 => per_rec.attribute20
,p_attribute21 => per_rec.attribute21
,p_attribute22 => per_rec.attribute22
,p_attribute23 => per_rec.attribute23
,p_attribute24 => per_rec.attribute24
,p_attribute25 => per_rec.attribute25
,p_attribute26 => per_rec.attribute26
,p_attribute27 => per_rec.attribute27
,p_attribute28 => per_rec.attribute28
,p_attribute29 => per_rec.attribute29
,p_attribute30 => per_rec.attribute30
,p_per_information_category => per_rec.per_information_category
,p_per_information1 => per_rec.per_information1
,p_per_information2 => per_rec.per_information2
,p_per_information3 => per_rec.per_information3
,p_per_information4 => per_rec.per_information4
,p_per_information5 => per_rec.per_information5
,p_per_information6 => per_rec.per_information6
,p_per_information7 => per_rec.per_information7
,P_per_information8 => per_rec.per_information8
,p_per_information9 => per_rec.per_information9
,p_per_information10 => per_rec.per_information10
,p_per_information11 => per_rec.per_information11
,p_per_information12 => per_rec.per_information12
,p_per_information13 => per_rec.per_information13
,p_per_information14 => per_rec.per_information14
,p_per_information15 => per_rec.per_information15
,p_per_information16 => per_rec.per_information16
,p_per_information17 => per_rec.per_information17
,p_per_information18 => per_rec.per_information18
,p_per_information19 => per_rec.per_information19
,p_per_information20 => per_rec.per_information20
,p_PER_INFORMATION21 => per_rec.per_information21
,p_PER_INFORMATION22 => per_rec.per_information22
,p_PER_INFORMATION23 => per_rec.per_information23
,p_PER_INFORMATION24 => per_rec.per_information24
,p_PER_INFORMATION25 => per_rec.per_information25
,p_PER_INFORMATION26 => per_rec.per_information26
,p_PER_INFORMATION27 => per_rec.per_information27
,p_PER_INFORMATION28 => per_rec.per_information28
,p_PER_INFORMATION29 => per_rec.per_information29
,p_PER_INFORMATION30 => per_rec.per_information30
,p_suffix => per_rec.suffix
,p_DATE_OF_DEATH => per_rec.DATE_OF_DEATH
,p_BACKGROUND_CHECK_STATUS => per_rec.BACKGROUND_CHECK_STATUS
,p_BACKGROUND_DATE_CHECK => per_rec.BACKGROUND_DATE_CHECK
,p_BLOOD_TYPE => per_rec.BLOOD_TYPE
,p_CORRESPONDENCE_LANGUAGE => per_rec.CORRESPONDENCE_LANGUAGE
,p_FTE_CAPACITY => per_rec.FTE_CAPACITY
,p_HOLD_APPLICANT_DATE_UNTIL => per_rec.HOLD_APPLICANT_DATE_UNTIL
,p_HONORS => per_rec.HONORS
,p_INTERNAL_LOCATION => per_rec.INTERNAL_LOCATION
,p_LAST_MEDICAL_TEST_BY => per_rec.LAST_MEDICAL_TEST_BY
,p_LAST_MEDICAL_TEST_DATE => per_rec.LAST_MEDICAL_TEST_DATE
,p_MAILSTOP => per_rec.MAILSTOP
,p_OFFICE_NUMBER => per_rec.OFFICE_NUMBER
,p_ON_MILITARY_SERVICE => per_rec.ON_MILITARY_SERVICE
,p_ORDER_NAME => per_rec.ORDER_NAME
,p_PRE_NAME_ADJUNCT => per_rec.PRE_NAME_ADJUNCT
,p_REHIRE_RECOMMENDATION => per_rec.REHIRE_RECOMMENDATION
,p_RESUME_EXISTS => per_rec.RESUME_EXISTS
,p_RESUME_LAST_UPDATED => per_rec.RESUME_LAST_UPDATED
,p_SECOND_PASSPORT_EXISTS => per_rec.SECOND_PASSPORT_EXISTS
,p_STUDENT_STATUS => per_rec.STUDENT_STATUS
,p_WORK_SCHEDULE => per_rec.WORK_SCHEDULE
,p_REHIRE_REASON => per_rec.REHIRE_REASON
,p_BENEFIT_GROUP_ID => per_rec.BENEFIT_GROUP_ID
,p_RECEIPT_OF_DEATH_CERT_DATE => per_rec.RECEIPT_OF_DEATH_CERT_DATE
,p_COORD_BEN_MED_PLN_NO => per_rec.COORD_BEN_MED_PLN_NO
,p_COORD_BEN_NO_CVG_FLAG => per_rec.COORD_BEN_NO_CVG_FLAG
,p_USES_TOBACCO_FLAG => per_rec.USES_TOBACCO_FLAG
,p_DPDNT_ADOPTION_DATE => per_rec.DPDNT_ADOPTION_DATE
,p_DPDNT_VLNTRY_SVCE_FLAG => per_rec.DPDNT_VLNTRY_SVCE_FLAG
,p_ORIGINAL_DATE_OF_HIRE => per_rec.ORIGINAL_DATE_OF_HIRE
,p_town_of_birth => per_rec.town_of_birth
,p_region_of_birth => per_rec.region_of_birth
,p_country_of_birth => per_rec.country_of_birth
,p_global_person_id => per_rec.global_person_id
,p_party_id => per_rec.party_id
,p_npw_number => per_rec.npw_number
,p_current_npw_flag => per_rec.current_npw_flag
,p_object_version_number => l_per_latest_ovn
,p_effective_date => apl.date_received
,p_datetrack_mode => l_datetrack_mode
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
select object_version_number,effective_start_date into l_per_latest_ovn,l_effective_start_date
from per_all_people_f
where person_id= p_person_id
and apl.date_end + 1 between effective_start_date and effective_end_date;
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_mode := 'UPDATE';
,p_program_update_date => per_rec.program_update_date
,p_attribute_category => per_rec.attribute_category
,p_attribute1 => per_rec.attribute1
,p_attribute2 => per_rec.attribute2
,p_attribute3 => per_rec.attribute3
,p_attribute4 => per_rec.attribute4
,p_attribute5 => per_rec.attribute5
,p_attribute6 => per_rec.attribute6
,p_attribute7 => per_rec.attribute7
,p_attribute8 => per_rec.attribute8
,p_attribute9 => per_rec.attribute9
,p_attribute10 => per_rec.attribute10
,p_attribute11 => per_rec.attribute11
,p_attribute12 => per_rec.attribute12
,p_attribute13 => per_rec.attribute13
,p_attribute14 => per_rec.attribute14
,p_attribute15 => per_rec.attribute15
,p_attribute16 => per_rec.attribute16
,p_attribute17 => per_rec.attribute17
,p_attribute18 => per_rec.attribute18
,p_attribute19 => per_rec.attribute19
,p_attribute20 => per_rec.attribute20
,p_attribute21 => per_rec.attribute21
,p_attribute22 => per_rec.attribute22
,p_attribute23 => per_rec.attribute23
,p_attribute24 => per_rec.attribute24
,p_attribute25 => per_rec.attribute25
,p_attribute26 => per_rec.attribute26
,p_attribute27 => per_rec.attribute27
,p_attribute28 => per_rec.attribute28
,p_attribute29 => per_rec.attribute29
,p_attribute30 => per_rec.attribute30
,p_per_information_category => per_rec.per_information_category
,p_per_information1 => per_rec.per_information1
,p_per_information2 => per_rec.per_information2
,p_per_information3 => per_rec.per_information3
,p_per_information4 => per_rec.per_information4
,p_per_information5 => per_rec.per_information5
,p_per_information6 => per_rec.per_information6
,p_per_information7 => per_rec.per_information7
,P_per_information8 => per_rec.per_information8
,p_per_information9 => per_rec.per_information9
,p_per_information10 => per_rec.per_information10
,p_per_information11 => per_rec.per_information11
,p_per_information12 => per_rec.per_information12
,p_per_information13 => per_rec.per_information13
,p_per_information14 => per_rec.per_information14
,p_per_information15 => per_rec.per_information15
,p_per_information16 => per_rec.per_information16
,p_per_information17 => per_rec.per_information17
,p_per_information18 => per_rec.per_information18
,p_per_information19 => per_rec.per_information19
,p_per_information20 => per_rec.per_information20
,p_PER_INFORMATION21 => per_rec.per_information21
,p_PER_INFORMATION22 => per_rec.per_information22
,p_PER_INFORMATION23 => per_rec.per_information23
,p_PER_INFORMATION24 => per_rec.per_information24
,p_PER_INFORMATION25 => per_rec.per_information25
,p_PER_INFORMATION26 => per_rec.per_information26
,p_PER_INFORMATION27 => per_rec.per_information27
,p_PER_INFORMATION28 => per_rec.per_information28
,p_PER_INFORMATION29 => per_rec.per_information29
,p_PER_INFORMATION30 => per_rec.per_information30
,p_suffix => per_rec.suffix
,p_DATE_OF_DEATH => per_rec.DATE_OF_DEATH
,p_BACKGROUND_CHECK_STATUS => per_rec.BACKGROUND_CHECK_STATUS
,p_BACKGROUND_DATE_CHECK => per_rec.BACKGROUND_DATE_CHECK
,p_BLOOD_TYPE => per_rec.BLOOD_TYPE
,p_CORRESPONDENCE_LANGUAGE => per_rec.CORRESPONDENCE_LANGUAGE
,p_FTE_CAPACITY => per_rec.FTE_CAPACITY
,p_HOLD_APPLICANT_DATE_UNTIL => per_rec.HOLD_APPLICANT_DATE_UNTIL
,p_HONORS => per_rec.HONORS
,p_INTERNAL_LOCATION => per_rec.INTERNAL_LOCATION
,p_LAST_MEDICAL_TEST_BY => per_rec.LAST_MEDICAL_TEST_BY
,p_LAST_MEDICAL_TEST_DATE => per_rec.LAST_MEDICAL_TEST_DATE
,p_MAILSTOP => per_rec.MAILSTOP
,p_OFFICE_NUMBER => per_rec.OFFICE_NUMBER
,p_ON_MILITARY_SERVICE => per_rec.ON_MILITARY_SERVICE
,p_ORDER_NAME => per_rec.ORDER_NAME
,p_PRE_NAME_ADJUNCT => per_rec.PRE_NAME_ADJUNCT
,p_REHIRE_RECOMMENDATION => per_rec.REHIRE_RECOMMENDATION
,p_RESUME_EXISTS => per_rec.RESUME_EXISTS
,p_RESUME_LAST_UPDATED => per_rec.RESUME_LAST_UPDATED
,p_SECOND_PASSPORT_EXISTS => per_rec.SECOND_PASSPORT_EXISTS
,p_STUDENT_STATUS => per_rec.STUDENT_STATUS
,p_WORK_SCHEDULE => per_rec.WORK_SCHEDULE
,p_REHIRE_REASON => per_rec.REHIRE_REASON
,p_BENEFIT_GROUP_ID => per_rec.BENEFIT_GROUP_ID
,p_RECEIPT_OF_DEATH_CERT_DATE => per_rec.RECEIPT_OF_DEATH_CERT_DATE
,p_COORD_BEN_MED_PLN_NO => per_rec.COORD_BEN_MED_PLN_NO
,p_COORD_BEN_NO_CVG_FLAG => per_rec.COORD_BEN_NO_CVG_FLAG
,p_USES_TOBACCO_FLAG => per_rec.USES_TOBACCO_FLAG
,p_DPDNT_ADOPTION_DATE => per_rec.DPDNT_ADOPTION_DATE
,p_DPDNT_VLNTRY_SVCE_FLAG => per_rec.DPDNT_VLNTRY_SVCE_FLAG
,p_ORIGINAL_DATE_OF_HIRE => per_rec.ORIGINAL_DATE_OF_HIRE
,p_town_of_birth => per_rec.town_of_birth
,p_region_of_birth => per_rec.region_of_birth
,p_country_of_birth => per_rec.country_of_birth
,p_global_person_id => per_rec.global_person_id
,p_party_id => per_rec.party_id
,p_npw_number => per_rec.npw_number
,p_current_npw_flag => per_rec.current_npw_flag
,p_object_version_number => l_per_latest_ovn
,p_effective_date => apl.date_end + 1
,p_datetrack_mode => l_datetrack_mode
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
UPDATE per_all_people_f
SET person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EMP')
,current_applicant_flag = NULL
WHERE person_id = p_person_id
AND effective_start_date > p_effective_end_date;
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',278);
-- Tests required post-update
--
/* BEGIN OF WWBUG 1975359 */
--
open c_person;
per_hrtca_merge.update_tca_person(p_Rec => l_person);
hr_utility.set_location('update_row2 - after update',1);
p_action => 'UPDATE');
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',280);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',282);
,p_start_date => p_last_update_date
);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',285);
hr_utility.set_location('update_row2 - after update',2);
-- Update the period of service for the employee
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',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_row2 - after update',3);
-- Update the hire records i.e
-- assignment etc.
--
--
/*--- If condition is added for the bug 5907880 */
if (((p_current_employee_flag = 'Y')
and (p_hire_date <> p_s_hire_date)
and (p_system_person_type = p_s_system_person_type))) then
/*--- End changes for the bug 5907880 */
hr_utility.set_location('update_row2 - 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_row2',295);
-- hr_change_start_date_api.update_pay_proposal (pehirapi.pkb)
-- open get_pay_proposal;
-- 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('PER_PEOPLE12_PKG.update_row2',300);
hr_utility.set_location('update_row2 - after update',5);
-- Update PTU records to reflect hire date change.
--
-- PTU changes: following has been added
--
-- hr_per_type_usage_internal.maintain_ptu(
-- p_action =>'HIRE DATE',
-- p_person_id => p_person_id,
-- p_date_start => p_hire_date,
-- p_old_date_start => p_s_hire_date);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',305);
hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',310);
p_action => 'UPDATE');
p_status := 'END'; -- Status required to end update loop on server
hr_utility.set_location('Leaving PER_PEOPLE12_PKG.update_row2',315);
end update_row2;
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
and p_effective_start_date
between effective_start_date
and effective_end_date; /* Fix for Bug 7442246 */
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;
select business_group_id,per_information1
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 assignment_id, application_id
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND primary_flag = 'Y'
AND assignment_type = 'E'
AND p_session_date BETWEEN effective_start_date AND effective_end_date;
select 'Y' from per_all_assignments_f
where person_id = p_person_id
and effective_start_date >= p_hire_date
and assignment_type = 'A';
select 'Y' from per_person_type_usages_f
where person_id = p_person_id
and effective_start_date >= p_hire_date;
-- If future person types or future asg updates exists move to proc update_row2
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y')
and ((l_chk_future_pt = 'Y' ) or (l_chk_future_asg = 'Y')) then
hr_utility.set_location('Future Person Type or Future asg changes exists',10);
update_row2(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:
,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
,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
,p_system_person_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
,p_tab
,null
,null
,null);
fnd_message.set_token('PROCEDURE','per_people12_pkg.update_row1');
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 call enthn pkg',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_person_type_usages_f ptu, per_person_types ppt
where ptu.person_id = p_person_id
and ptu.person_type_id = ppt.person_type_id
and ptu.effective_start_date > p_effective_start_date
and ppt.system_person_type in ('EMP','CWK');
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
open fpt_future_exists;
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;