[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_sex in varchar2 default null
,p_student_status in varchar2 default null
,p_suffix in varchar2 default null
,p_title in varchar2 default null
,p_town_of_birth in varchar2 default null
,p_uses_tobacco_flag in varchar2 default null
,p_vendor_id in number default null
,p_work_schedule in varchar2 default null
,p_work_telephone in varchar2 default null
,p_exp_check_send_to_address in varchar2 default null
,p_hold_applicant_date_until in date default null
,p_date_employee_data_verified in date default null
,p_benefit_group_id in number default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default null
,p_original_date_of_hire in date default null
,p_attribute_category in varchar2 default null
,p_attribute1 in varchar2 default null
,p_attribute2 in varchar2 default null
,p_attribute3 in varchar2 default null
,p_attribute4 in varchar2 default null
,p_attribute5 in varchar2 default null
,p_attribute6 in varchar2 default null
,p_attribute7 in varchar2 default null
,p_attribute8 in varchar2 default null
,p_attribute9 in varchar2 default null
,p_attribute10 in varchar2 default null
,p_attribute11 in varchar2 default null
,p_attribute12 in varchar2 default null
,p_attribute13 in varchar2 default null
,p_attribute14 in varchar2 default null
,p_attribute15 in varchar2 default null
,p_attribute16 in varchar2 default null
,p_attribute17 in varchar2 default null
,p_attribute18 in varchar2 default null
,p_attribute19 in varchar2 default null
,p_attribute20 in varchar2 default null
,p_attribute21 in varchar2 default null
,p_attribute22 in varchar2 default null
,p_attribute23 in varchar2 default null
,p_attribute24 in varchar2 default null
,p_attribute25 in varchar2 default null
,p_attribute26 in varchar2 default null
,p_attribute27 in varchar2 default null
,p_attribute28 in varchar2 default null
,p_attribute29 in varchar2 default null
,p_attribute30 in varchar2 default null
,p_per_information_category in varchar2 default null
,p_per_information1 in varchar2 default null
,p_per_information2 in varchar2 default null
,p_per_information3 in varchar2 default null
,p_per_information4 in varchar2 default null
,p_per_information5 in varchar2 default null
,p_per_information6 in varchar2 default null
,p_per_information7 in varchar2 default null
,p_per_information8 in varchar2 default null
,p_per_information9 in varchar2 default null
,p_per_information10 in varchar2 default null
,p_per_information11 in varchar2 default null
,p_per_information12 in varchar2 default null
,p_per_information13 in varchar2 default null
,p_per_information14 in varchar2 default null
,p_per_information15 in varchar2 default null
,p_per_information16 in varchar2 default null
,p_per_information17 in varchar2 default null
,p_per_information18 in varchar2 default null
,p_per_information19 in varchar2 default null
,p_per_information20 in varchar2 default null
,p_per_information21 in varchar2 default null
,p_per_information22 in varchar2 default null
,p_per_information23 in varchar2 default null
,p_per_information24 in varchar2 default null
,p_per_information25 in varchar2 default null
,p_per_information26 in varchar2 default null
,p_per_information27 in varchar2 default null
,p_per_information28 in varchar2 default null
,p_per_information29 in varchar2 default null
,p_per_information30 in varchar2 default null
,p_person_id out nocopy number
,p_per_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_pdp_object_version_number out nocopy number
,p_full_name out nocopy varchar2
,p_comment_id out nocopy number
,p_assignment_id out nocopy number
,p_asg_object_version_number out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
) is
begin
create_cwk
(p_validate => p_validate
,p_start_date => p_start_date
,p_business_group_id => p_business_group_id
,p_last_name => p_last_name
,p_person_type_id => p_person_type_id
,p_npw_number => p_npw_number
,p_background_check_status => p_background_check_status
,p_background_date_check => p_background_date_check
,p_blood_type => p_blood_type
,p_comments => p_comments
,p_correspondence_language => p_correspondence_language
,p_country_of_birth => p_country_of_birth
,p_date_of_birth => p_date_of_birth
,p_date_of_death => p_date_of_death
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_email_address => p_email_address
,p_first_name => p_first_name
,p_fte_capacity => p_fte_capacity
,p_honors => p_honors
,p_internal_location => p_internal_location
,p_known_as => p_known_as
,p_last_medical_test_by => p_last_medical_test_by
,p_last_medical_test_date => p_last_medical_test_date
,p_mailstop => p_mailstop
,p_marital_status => p_marital_status
,p_middle_names => p_middle_names
,p_national_identifier => p_national_identifier
,p_nationality => p_nationality
,p_office_number => p_office_number
,p_on_military_service => p_on_military_service
,p_party_id => p_party_id
,p_pre_name_adjunct => p_pre_name_adjunct
,p_previous_last_name => p_previous_last_name
,p_projected_placement_end => p_projected_placement_end
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,p_region_of_birth => p_region_of_birth
,p_registered_disabled_flag => p_registered_disabled_flag
,p_resume_exists => p_resume_exists
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_sex => p_sex
,p_student_status => p_student_status
,p_suffix => p_suffix
,p_title => p_title
,p_town_of_birth => p_town_of_birth
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_vendor_id => p_vendor_id
,p_work_schedule => p_work_schedule
,p_work_telephone => p_work_telephone
,p_exp_check_send_to_address => p_exp_check_send_to_address
,p_hold_applicant_date_until => p_hold_applicant_date_until
,p_date_employee_data_verified => p_date_employee_data_verified
,p_benefit_group_id => p_benefit_group_id
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_per_information_category => p_per_information_category
,p_per_information1 => p_per_information1
,p_per_information2 => p_per_information2
,p_per_information3 => p_per_information3
,p_per_information4 => p_per_information4
,p_per_information5 => p_per_information5
,p_per_information6 => p_per_information6
,p_per_information7 => p_per_information7
,p_per_information8 => p_per_information8
,p_per_information9 => p_per_information9
,p_per_information10 => p_per_information10
,p_per_information11 => p_per_information11
,p_per_information12 => p_per_information12
,p_per_information13 => p_per_information13
,p_per_information14 => p_per_information14
,p_per_information15 => p_per_information15
,p_per_information16 => p_per_information16
,p_per_information17 => p_per_information17
,p_per_information18 => p_per_information18
,p_per_information19 => p_per_information19
,p_per_information20 => p_per_information20
,p_per_information21 => p_per_information21
,p_per_information22 => p_per_information22
,p_per_information23 => p_per_information23
,p_per_information24 => p_per_information24
,p_per_information25 => p_per_information25
,p_per_information26 => p_per_information26
,p_per_information27 => p_per_information27
,p_per_information28 => p_per_information28
,p_per_information29 => p_per_information29
,p_per_information30 => p_per_information30
-- Added for Bug 9367203
,p_rehire_recommendation => NULL
,p_rehire_reason => NULL
-- Fix 9367203 ends here
,p_person_id => p_person_id
,p_per_object_version_number => p_per_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_pdp_object_version_number => p_pdp_object_version_number
,p_full_name => p_full_name
,p_comment_id => p_comment_id
,p_assignment_id => p_assignment_id
,p_asg_object_version_number => p_asg_object_version_number
,p_assignment_sequence => p_assignment_sequence
,p_assignment_number => p_assignment_number
,p_name_combination_warning => p_name_combination_warning
);
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_sex in varchar2 default null
,p_student_status in varchar2 default null
,p_suffix in varchar2 default null
,p_title in varchar2 default null
,p_town_of_birth in varchar2 default null
,p_uses_tobacco_flag in varchar2 default null
,p_vendor_id in number default null
,p_work_schedule in varchar2 default null
,p_work_telephone in varchar2 default null
,p_exp_check_send_to_address in varchar2 default null
,p_hold_applicant_date_until in date default null
,p_date_employee_data_verified in date default null
,p_benefit_group_id in number default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default null
,p_original_date_of_hire in date default null
,p_attribute_category in varchar2 default null
,p_attribute1 in varchar2 default null
,p_attribute2 in varchar2 default null
,p_attribute3 in varchar2 default null
,p_attribute4 in varchar2 default null
,p_attribute5 in varchar2 default null
,p_attribute6 in varchar2 default null
,p_attribute7 in varchar2 default null
,p_attribute8 in varchar2 default null
,p_attribute9 in varchar2 default null
,p_attribute10 in varchar2 default null
,p_attribute11 in varchar2 default null
,p_attribute12 in varchar2 default null
,p_attribute13 in varchar2 default null
,p_attribute14 in varchar2 default null
,p_attribute15 in varchar2 default null
,p_attribute16 in varchar2 default null
,p_attribute17 in varchar2 default null
,p_attribute18 in varchar2 default null
,p_attribute19 in varchar2 default null
,p_attribute20 in varchar2 default null
,p_attribute21 in varchar2 default null
,p_attribute22 in varchar2 default null
,p_attribute23 in varchar2 default null
,p_attribute24 in varchar2 default null
,p_attribute25 in varchar2 default null
,p_attribute26 in varchar2 default null
,p_attribute27 in varchar2 default null
,p_attribute28 in varchar2 default null
,p_attribute29 in varchar2 default null
,p_attribute30 in varchar2 default null
,p_per_information_category in varchar2 default null
,p_per_information1 in varchar2 default null
,p_per_information2 in varchar2 default null
,p_per_information3 in varchar2 default null
,p_per_information4 in varchar2 default null
,p_per_information5 in varchar2 default null
,p_per_information6 in varchar2 default null
,p_per_information7 in varchar2 default null
,p_per_information8 in varchar2 default null
,p_per_information9 in varchar2 default null
,p_per_information10 in varchar2 default null
,p_per_information11 in varchar2 default null
,p_per_information12 in varchar2 default null
,p_per_information13 in varchar2 default null
,p_per_information14 in varchar2 default null
,p_per_information15 in varchar2 default null
,p_per_information16 in varchar2 default null
,p_per_information17 in varchar2 default null
,p_per_information18 in varchar2 default null
,p_per_information19 in varchar2 default null
,p_per_information20 in varchar2 default null
,p_per_information21 in varchar2 default null
,p_per_information22 in varchar2 default null
,p_per_information23 in varchar2 default null
,p_per_information24 in varchar2 default null
,p_per_information25 in varchar2 default null
,p_per_information26 in varchar2 default null
,p_per_information27 in varchar2 default null
,p_per_information28 in varchar2 default null
,p_per_information29 in varchar2 default null
,p_per_information30 in varchar2 default null
-- Fix for bug 9367203 starts
,p_rehire_recommendation in varchar2
,p_rehire_reason in varchar2
-- Fix for bug 9367203 ends
,p_person_id out nocopy number
,p_per_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_pdp_object_version_number out nocopy number
,p_full_name out nocopy varchar2
,p_comment_id out nocopy number
,p_assignment_id out nocopy number
,p_asg_object_version_number out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'create_cwk';
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_sex => p_sex
,p_student_status => p_student_status
,p_suffix => p_suffix
,p_title => p_title
,p_town_of_birth => p_town_of_birth
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_vendor_id => p_vendor_id
,p_work_schedule => p_work_schedule
,p_work_telephone => p_work_telephone
,p_exp_check_send_to_address => p_exp_check_send_to_address
,p_hold_applicant_date_until => p_hold_applicant_date_until
,p_date_employee_data_verified => p_date_employee_data_verified
,p_benefit_group_id => p_benefit_group_id
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_per_information_category => p_per_information_category
,p_per_information1 => p_per_information1
,p_per_information2 => p_per_information2
,p_per_information3 => p_per_information3
,p_per_information4 => p_per_information4
,p_per_information5 => p_per_information5
,p_per_information6 => p_per_information6
,p_per_information7 => p_per_information7
,p_per_information8 => p_per_information8
,p_per_information9 => p_per_information9
,p_per_information10 => p_per_information10
,p_per_information11 => p_per_information11
,p_per_information12 => p_per_information12
,p_per_information13 => p_per_information13
,p_per_information14 => p_per_information14
,p_per_information15 => p_per_information15
,p_per_information16 => p_per_information16
,p_per_information17 => p_per_information17
,p_per_information18 => p_per_information18
,p_per_information19 => p_per_information19
,p_per_information20 => p_per_information20
,p_per_information21 => p_per_information21
,p_per_information22 => p_per_information22
,p_per_information23 => p_per_information23
,p_per_information24 => p_per_information24
,p_per_information25 => p_per_information25
,p_per_information26 => p_per_information26
,p_per_information27 => p_per_information27
,p_per_information28 => p_per_information28
,p_per_information29 => p_per_information29
,p_per_information30 => p_per_information30
);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_sex => p_sex
,p_student_status => p_student_status
,p_suffix => p_suffix
,p_title => p_title
,p_town_of_birth => p_town_of_birth
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_work_schedule => p_work_schedule
,p_expense_check_send_to_addres => p_exp_check_send_to_address
,p_hold_applicant_date_until => p_hold_applicant_date_until
,p_date_employee_data_verified => p_date_employee_data_verified
,p_benefit_group_id => p_benefit_group_id
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_per_information_category => p_per_information_category
,p_per_information1 => p_per_information1
,p_per_information2 => p_per_information2
,p_per_information3 => p_per_information3
,p_per_information4 => p_per_information4
,p_per_information5 => p_per_information5
,p_per_information6 => p_per_information6
,p_per_information7 => p_per_information7
,p_per_information8 => p_per_information8
,p_per_information9 => p_per_information9
,p_per_information10 => p_per_information10
,p_per_information11 => p_per_information11
,p_per_information12 => p_per_information12
,p_per_information13 => p_per_information13
,p_per_information14 => p_per_information14
,p_per_information15 => p_per_information15
,p_per_information16 => p_per_information16
,p_per_information17 => p_per_information17
,p_per_information18 => p_per_information18
,p_per_information19 => p_per_information19
,p_per_information20 => p_per_information20
,p_per_information21 => p_per_information21
,p_per_information22 => p_per_information22
,p_per_information23 => p_per_information23
,p_per_information24 => p_per_information24
,p_per_information25 => p_per_information25
,p_per_information26 => p_per_information26
,p_per_information27 => p_per_information27
,p_per_information28 => p_per_information28
,p_per_information29 => p_per_information29
,p_per_information30 => p_per_information30
--
-- Fix for bug 9367203 starts
,p_rehire_recommendation => p_rehire_recommendation
,p_rehire_reason => p_rehire_reason
-- Fix for bug 9367203 ends
,p_applicant_number => l_applicant_number
,p_employee_number => l_employee_number
,p_npw_number => p_npw_number
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_employee_flag => l_current_employee_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_dob_null_warning
,p_orig_hire_warning => l_orig_hire_warning
);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_sex => p_sex
,p_student_status => p_student_status
,p_suffix => p_suffix
,p_title => p_title
,p_town_of_birth => p_town_of_birth
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_vendor_id => p_vendor_id
,p_work_schedule => p_work_schedule
,p_work_telephone => p_work_telephone
,p_exp_check_send_to_address => p_exp_check_send_to_address
,p_hold_applicant_date_until => p_hold_applicant_date_until
,p_date_employee_data_verified => p_date_employee_data_verified
,p_benefit_group_id => p_benefit_group_id
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_per_information_category => p_per_information_category
,p_per_information1 => p_per_information1
,p_per_information2 => p_per_information2
,p_per_information3 => p_per_information3
,p_per_information4 => p_per_information4
,p_per_information5 => p_per_information5
,p_per_information6 => p_per_information6
,p_per_information7 => p_per_information7
,p_per_information8 => p_per_information8
,p_per_information9 => p_per_information9
,p_per_information10 => p_per_information10
,p_per_information11 => p_per_information11
,p_per_information12 => p_per_information12
,p_per_information13 => p_per_information13
,p_per_information14 => p_per_information14
,p_per_information15 => p_per_information15
,p_per_information16 => p_per_information16
,p_per_information17 => p_per_information17
,p_per_information18 => p_per_information18
,p_per_information19 => p_per_information19
,p_per_information20 => p_per_information20
,p_per_information21 => p_per_information21
,p_per_information22 => p_per_information22
,p_per_information23 => p_per_information23
,p_per_information24 => p_per_information24
,p_per_information25 => p_per_information25
,p_per_information26 => p_per_information26
,p_per_information27 => p_per_information27
,p_per_information28 => p_per_information28
,p_per_information29 => p_per_information29
,p_per_information30 => p_per_information30
,p_person_id => l_person_id
,p_per_object_version_number => l_per_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_pdp_object_version_number => l_pdp_object_version_number
,p_full_name => l_full_name
,p_comment_id => l_comment_id
,p_assignment_id => l_assignment_id
,p_asg_object_version_number => l_asg_object_version_number
,p_assignment_sequence => l_assignment_sequence
,p_assignment_number => l_assignment_number
,p_name_combination_warning => l_name_combination_warning
);
SELECT object_version_number
INTO p_per_object_Version_number
FROM per_all_people_f
WHERE person_id = l_person_id
And effective_start_Date = l_per_effective_start_date
and effective_end_Date = l_per_effective_end_date;
,p_datetrack_update_mode in varchar2
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_pdp_object_version_number out nocopy number
,p_assignment_id out nocopy number
,p_asg_object_version_number out nocopy number
,p_assignment_sequence out nocopy number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'convert_to_cwk';
l_datetrack_update_mode varchar2(30);
l_ptu_update_mode varchar2(30);
SELECT ppf.business_group_id
,ppf.person_type_id
,ppt.system_person_type
,ppf.npw_number
,ppf.applicant_number
,ppf.employee_number
FROM per_all_people_f ppf
,per_person_types ppt
WHERE ppt.person_type_id = ppf.person_type_id
AND ppf.person_id = p_person_id
AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT ptu.person_type_id
,effective_start_date
,effective_end_date
FROM per_person_type_usages_f ptu
,per_person_types ppt
WHERE person_id = p_person_id
AND effective_start_date > l_effective_date
AND ptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type IN ('APL','EX_APL')
ORDER BY effective_start_date;
,p_datetrack_update_mode => p_datetrack_update_mode
);
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
if hr_person.fpt_check_ft_person_type(l_per_details_rec.system_person_type
,p_person_id
,l_per_details_rec.business_group_id
,'Y'
,p_effective_date) then
fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
l_datetrack_update_mode := 'CORRECTION';
l_ptu_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
l_ptu_update_mode := 'UPDATE';
if p_datetrack_update_mode = 'CORRECTION' then
l_datetrack_update_mode := p_datetrack_update_mode;
l_datetrack_update_mode := 'UPDATE';
l_ptu_update_mode := 'UPDATE';
,p_datetrack_mode => l_datetrack_update_mode
,p_person_type_id => l_person_type_id1
,p_applicant_number => l_per_details_rec.applicant_number
,p_employee_number => l_per_details_rec.employee_number
,p_npw_number => p_npw_number
,p_current_npw_flag => 'Y'
,p_object_version_number => p_object_version_number
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_dob_null_warning
,p_orig_hire_warning => l_orig_hire_warning
);
,p_datetrack_update_mode => l_ptu_update_mode
);
select object_version_number,effective_start_date,applicant_number
into l_ovn,l_effective_start_date,l_applicant_number
from per_all_people_f
where person_id = p_person_id
and ptu_rec.effective_start_date between effective_start_date and effective_end_date;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
,p_datetrack_mode => l_datetrack_update_mode
,p_person_type_id => l_person_type_id2
,p_applicant_number => l_applicant_number
,p_employee_number => l_per_details_rec.employee_number
,p_npw_number => p_npw_number
,p_current_npw_flag => 'Y'
,p_object_version_number => l_ovn
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_dob_null_warning
,p_orig_hire_warning => l_orig_hire_warning
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_pdp_object_version_number => l_pdp_object_version_number
,p_assignment_id => l_assignment_id
,p_asg_object_version_number => l_asg_object_version_number
,p_assignment_sequence => l_assignment_sequence
);
SELECT ppf.business_group_id
,ppf.person_type_id
,ppt.system_person_type
,ppf.npw_number
,ppf.applicant_number
,ppf.employee_number
FROM per_all_people_f ppf
,per_person_types ppt
WHERE ppt.person_type_id = ppf.person_type_id
AND ppf.person_id = p_person_id
AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
select 'Y'
from dual
where exists (select 'Y'
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and ppt.person_type_id = ptu.person_type_id
and ptu.effective_start_date >= p_effective_date
and ppt.system_person_type = 'APL');
,p_datetrack_mode => hr_api.g_update
,p_person_type_id => l_person_type_id1
,p_applicant_number => p_applicant_number
,p_employee_number => l_per_details_rec.employee_number
,p_npw_number => l_per_details_rec.npw_number
,p_current_npw_flag => 'Y'
,p_object_version_number => p_object_version_number
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_dob_null_warning
,p_orig_hire_warning => l_orig_hire_warning
);
Select 'X'
into v_dummy
from sys.dual
where exists (select 'Assignments Exist'
from per_all_assignments_f paf
where paf.supervisor_id = p_person_id
and paf.business_group_id = p_business_group_id
and p_session_date between paf.effective_start_date
and paf.effective_end_date);
select 'X'
into v_dummy
from sys.dual
where exists ( select 'Events exist'
from per_events pe
, per_bookings pb
where pe.business_group_id = pb.business_group_id
and (pb.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pe.event_id = pb.event_id
and pe.event_or_interview = 'E'
and pb.person_id = p_person_id
and pe.date_start > p_session_date
);
select 'X'
into v_dummy
from sys.dual
where exists(select 'Interview rows exist'
from per_events pe
where pe.business_group_id = p_business_group_id
and pe.event_or_interview = 'I'
and pe.internal_contact_person_id = p_person_id
and pe.date_start > p_session_date
)
OR
exists(select 'Interview rows exist'
from per_events pe
,per_bookings pb
where pe.business_group_id = pb.business_group_id
and (pb.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pe.event_id = pb.event_id
and pe.event_or_interview = 'I'
and pb.person_id = p_person_id
and pe.date_start > p_session_date
);
select 'X'
into v_dummy
from sys.dual
where exists ( select 'Perf Review rows exist'
from per_performance_reviews ppr
where ppr.person_id = p_person_id
and review_date > p_session_date
);
select 'X'
into v_dummy
from sys.dual
where exists (select 'Recruiter for vacancy'
from per_vacancies pv
where
-- Fix for bug 3446782. This condition exists in the view.
/*(pv.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and */
pv.recruiter_id = p_person_id
and nvl(pv.date_to, p_session_date) >= p_session_date);
select null
from per_all_people_f per
where per.person_id = l_person_id
and per.effective_start_date > l_actual_termination_date;
select asg.assignment_id
, asg.object_version_number
from per_all_assignments_f asg
where asg.person_id = l_person_id
and asg.period_of_placement_date_start = l_date_start
and l_actual_termination_date + 1 between asg.effective_start_date
and asg.effective_end_date
order by asg.primary_flag;
select bus.business_group_id
, bus.legislation_code
, per.person_id
, per.effective_start_date
, per.object_version_number
, per.npw_number
, per.applicant_number
from per_all_people_f per
, per_business_groups bus
, per_periods_of_placement pdp
where pdp.person_id = p_person_id
and pdp.date_start = p_date_start
and bus.business_group_id = pdp.business_group_id
and per.person_id = pdp.person_id
and l_actual_termination_date between per.effective_start_date
and per.effective_end_date;
select max(tpe.end_date)
from per_time_periods tpe
,per_all_assignments_f asg
where asg.person_id = l_person_id
and asg.period_of_placement_date_start = l_date_start
and l_actual_termination_date between asg.effective_start_date
and asg.effective_end_date
and asg.payroll_id is not null
and tpe.payroll_id = asg.payroll_id
and l_actual_termination_date between tpe.start_date
and tpe.end_date;
select date_of_death
from per_all_people_f
where person_id = l_person_id;
select null
from per_person_type_usages_f ptu, per_person_types ppt
where person_id = p_person_id
and effective_start_date > l_actual_termination_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP','CWK');
select min(effective_start_date)
from per_all_people_f
where person_id = p_person_id
and effective_start_date > l_actual_termination_date;
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
open csr_fpt_ptu_changes;
l_datetrack_mode := 'UPDATE';
select applicant_number into l_applicant_number from
per_all_people_f where person_id = p_person_id
and effective_start_date = l_start_date;
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
update per_all_people_f
set current_npw_flag = null
where person_id = p_person_id
and effective_start_date > l_actual_termination_date;
,p_datetrack_update_mode => 'UPDATE'
);
select bus.legislation_code
, pdp.actual_termination_date
, pdp.last_standard_process_date
, pdp.person_id
, pdp.object_version_number
from per_business_groups bus
, per_periods_of_placement pdp
where pdp.person_id = p_person_id
and pdp.date_start = p_date_start
and bus.business_group_id = pdp.business_group_id;
select asg.assignment_id
, asg.object_version_number
, asg.primary_flag
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.period_of_placement_date_start = p_date_start
and l_final_process_date between asg.effective_start_date
and asg.effective_end_date
order by asg.primary_flag;
select actual_termination_date
,final_process_date
from per_periods_of_placement
where person_id = p_person_id
and date_start = p_date_start;
select
role_id
, object_version_number
, end_date
from per_roles
where person_id = p_person_id
and p_actual_termination_date
between start_date
and nvl(end_date, hr_api.g_eot);
select role_id
from per_roles
where person_id = p_person_id
and EMP_RIGHTS_FLAG = 'Y'
and nvl(end_of_rights_date, hr_api.g_eot) > p_actual_termination_date;
** then don't call either termination API and just update the details.
*/
open csr_get_pdp_details;
hr_periods_of_placement_api.update_pdp_details
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_object_version_number => p_object_version_number
,p_person_id => p_person_id
,p_date_start => p_date_start
,p_termination_reason => p_termination_reason
,p_projected_termination_date => null
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_information3 => p_information3
,p_information4 => p_information4
,p_information5 => p_information5
,p_information6 => p_information6
,p_information7 => p_information7
,p_information8 => p_information8
,p_information9 => p_information9
,p_information10 => p_information10
,p_information11 => p_information11
,p_information12 => p_information12
,p_information13 => p_information13
,p_information14 => p_information14
,p_information15 => p_information15
,p_information16 => p_information16
,p_information17 => p_information17
,p_information18 => p_information18
,p_information19 => p_information19
,p_information20 => p_information20
,p_information21 => p_information21
,p_information22 => p_information22
,p_information23 => p_information23
,p_information24 => p_information24
,p_information25 => p_information25
,p_information26 => p_information26
,p_information27 => p_information27
,p_information28 => p_information28
,p_information29 => p_information29
,p_information30 => p_information30
);
per_supplementary_role_api.update_supplementary_role(
p_effective_date => p_effective_date
,p_role_id => roles_rec.role_id
,p_object_version_number => roles_rec.object_version_number
,p_end_date => p_actual_termination_date
,p_old_end_date => roles_rec.end_date
);
SELECT assignment_id
, assignment_status_type_id
, business_group_id
FROM per_all_assignments_f ass
WHERE ass.person_id = p_person_id
AND ass.effective_end_date = p_actual_termination_date
AND ass.assignment_type = 'C' -- Added for bug 12637271
FOR UPDATE;
SELECT pt.system_person_type
FROM per_person_type_usages_f ptu,
per_person_types pt
WHERE ptu.person_id = p_person_id
AND ptu.person_type_id = pt.person_type_id
AND ptu.effective_start_date > p_actual_termination_date;
select *
from per_periods_of_placement
where person_id = p_person_id
and actual_termination_date = p_actual_termination_date;
select role_id
,object_version_number
,old_end_date from
per_roles
where person_id = p_person_id
and end_date = p_actual_termination_date
for update nowait;
select *
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and effective_end_date = l_final_process_date;
select min(effective_start_date)
from per_all_people_f per, per_person_types ppt
where per.person_id = p_person_id
and effective_start_date > p_actual_termination_date
and per.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EX_APL','APL');
SELECT pdp.final_process_date
, pdp.last_standard_process_date
INTO l_final_process_date
, l_last_standard_process_date
FROM per_periods_of_placement pdp
WHERE pdp.person_id = p_person_id
AND pdp.actual_termination_date = p_actual_termination_date;
(fpt_rec.system_person_type in ('APL','EX_APL') and (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'N')) then
b_future_person_type_err := TRUE;
SELECT 'Y'
INTO l_action_chk
FROM dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_all_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_type not in ('X','BEE') -- Bug 889806,2711532
AND pac.effective_date > l_final_process_date);
SELECT 'W'
INTO l_action_chk
FROM sys.dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_all_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_status = 'C'
AND (pac.effective_date BETWEEN l_action_date AND l_final_process_date));
update per_all_people_f
set effective_end_date = l_eff_start_date - 1
where person_id = p_person_id
and p_actual_termination_date between effective_start_date and effective_end_date;
delete from per_all_people_f
where person_id = p_person_id
and effective_start_date > p_actual_termination_date
and effective_start_date < l_eff_start_date;
update per_all_people_f
set current_npw_flag = 'Y'
where person_id = p_person_id
and effective_start_date > p_actual_termination_date;
UPDATE per_people_f pp
SET pp.effective_end_date = hr_api.g_eot
WHERE pp.person_id = p_person_id
AND p_actual_termination_date
BETWEEN pp.effective_start_date
AND pp.effective_end_date;
DELETE per_people_f pp
WHERE pp.person_id = p_person_id
AND pp.effective_start_date > p_actual_termination_date;
UPDATE per_periods_of_placement pdp
SET pdp.actual_termination_date = null
, pdp.last_standard_process_date = null
, pdp.final_process_date = null
, pdp.termination_reason = null
, pdp.projected_termination_date = null
WHERE pdp.person_id = p_person_id
AND pdp.actual_termination_date = p_actual_termination_date;
UPDATE per_periods_of_placement pdp
SET pdp.actual_termination_date = null
, pdp.last_standard_process_date = null
, pdp.final_process_date = null
, pdp.termination_reason = null
, pdp.projected_termination_date = null
WHERE pdp.person_id = p_person_id
AND pdp.actual_termination_date = p_actual_termination_date;
SELECT per_system_status
INTO l_per_system_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = c_asg_rec.assignment_status_type_id;
SELECT assignment_status_type_id
INTO l_asg_status_type_id
FROM per_all_assignments_f
WHERE assignment_id = c_asg_rec.assignment_id
AND effective_end_date = p_actual_termination_date;
SELECT max(asg.effective_end_date)
INTO l_max_end_date
FROM per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_rec.assignment_id;
UPDATE per_all_assignments_f ass
SET ass.effective_end_date = l_effective_end_date
WHERE assignment_id = c_asg_rec.assignment_id
AND effective_end_date = l_max_end_date;
UPDATE per_all_assignments_f ass
SET ass.assignment_status_type_id = l_asg_status_type_id
WHERE assignment_id = c_asg_rec.assignment_id
AND effective_start_date >= p_actual_termination_date;
UPDATE per_secondary_ass_statuses sas
SET sas.end_date = null
WHERE sas.assignment_id = c_asg_rec.assignment_id
AND sas.end_date = l_final_process_date;
UPDATE pay_personal_payment_methods_f ppm
SET ppm.effective_end_date = l_effective_end_date
WHERE ppm.assignment_id = c_asg_rec.assignment_id
AND ppm.effective_end_date = l_final_process_date;
UPDATE pay_cost_allocations_f pca
SET pca.effective_end_date = l_effective_end_date
WHERE pca.assignment_id = c_asg_rec.assignment_id
AND pca.effective_end_date = l_final_process_date;
UPDATE per_spinal_point_placements_f spp
SET spp.effective_end_date = l_effective_end_date
WHERE spp.assignment_id = c_asg_rec.assignment_id
AND spp.effective_end_date = l_final_process_date;
UPDATE pay_grade_rules_f pgr
SET pgr.effective_end_date = l_effective_end_date
WHERE pgr.grade_or_spinal_point_Id = c_asg_rec.assignment_id
AND pgr.rate_type = 'A'
AND pgr.effective_end_date = l_final_process_date;
pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records
(c_asg_rec.assignment_id
,l_final_process_date);
update per_assignment_budget_values_f abv
set abv.effective_end_date = l_effective_end_date
where abv.assignment_id = c_asg_rec.assignment_id
and abv.assignment_budget_value_id = l_c2.assignment_budget_value_id
and abv.effective_end_date = l_final_process_date;
,'DELETE_NEXT_CHANGE'
,null
,null);
per_supplementary_role_api.update_supplementary_role(
p_effective_date => p_actual_termination_date+1
,p_role_id => roles_rec.role_id
,p_object_version_number => roles_rec.object_version_number
,p_end_date => roles_rec.old_end_date
);
select trunc(sum(months_between
(least
(nvl(ACTUAL_TERMINATION_DATE + 1, p_effective_date + 1),
p_effective_date + 1)
,DATE_START)) / 12, 0) total_years,
trunc(mod(sum(months_between
(least
(nvl(ACTUAL_TERMINATION_DATE + 1, p_effective_date + 1),
p_effective_date + 1)
,DATE_START)), 12), 0) total_months
from PER_PERIODS_OF_PLACEMENT
where PERSON_ID = p_person_id
and business_group_id = p_business_group_id
and DATE_START = p_date_start
and DATE_START <= p_effective_date;