[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asg.assignment_id
,asg.object_version_number
FROM per_assignments_f asg
WHERE asg.person_id = csr_future_asgs.p_person_id
--
-- 115.58 (START)
--
AND asg.assignment_type <> 'B'
--
-- 115.58 (START)
--
AND asg.effective_start_date >= csr_future_asgs.p_effective_date;
SELECT 'x'
FROM per_assignments_f asg1
,per_assignments_f asg2
,per_assignment_status_types pas1
,per_assignment_status_types pas2
,per_periods_of_service pds
WHERE pds.person_id = p_person_id
AND pds.person_id = asg1.person_id
AND pds.person_id = asg2.person_id
AND asg1.assignment_status_type_id = pas1.assignment_status_type_id
AND asg2.assignment_status_type_id = pas2.assignment_status_type_id
AND pds.final_process_date > pds.actual_termination_date
AND pds.actual_termination_date+1 = p_effective_date
AND asg1.effective_start_date = p_effective_date
AND pas1.per_system_status = 'TERM_ASSIGN'
AND asg2.effective_end_date+1 = p_effective_date
AND pas2.per_system_status = 'ACTIVE_ASSIGN';
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_work_schedule in varchar2 default null
,p_suffix in varchar2 default null
,p_benefit_group_id in number default null
,p_receipt_of_death_cert_date in date default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default 'N'
,p_coord_ben_med_ext_er in varchar2 default null
,p_coord_ben_med_pl_name in varchar2 default null
,p_coord_ben_med_insr_crr_name in varchar2 default null
,p_coord_ben_med_insr_crr_ident in varchar2 default null
,p_coord_ben_med_cvg_strt_dt in date default null
,p_coord_ben_med_cvg_end_dt in date default null
,p_uses_tobacco_flag in varchar2 default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
,p_original_date_of_hire in date default null
,p_adjusted_svc_date in date default null
,p_town_of_birth in varchar2 default null
,p_region_of_birth in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_global_person_id in varchar2 default null
,p_party_id in number default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
,p_assign_payroll_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => l_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => l_original_date_of_hire
,p_adjusted_svc_date => l_adjusted_svc_date
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => l_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => l_original_date_of_hire
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_validate => false
--
,p_applicant_number => l_applicant_number
,p_employee_number => p_employee_number
,p_person_id => l_person_id
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_per_comment_id
,p_current_applicant_flag => l_discard_varchar2
,p_current_emp_or_apl_flag => l_discard_varchar2
,p_current_employee_flag => l_discard_varchar2
,p_full_name => l_full_name
,p_object_version_number => l_per_object_version_number
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
,p_npw_number => l_npw_number
);
SELECT object_version_number
INTO l_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;
hr_assignment.update_assgn_context_value (p_business_group_id,
l_person_id,
l_assignment_id,
p_hire_date);
SELECT object_version_number
INTO l_asg_object_Version_number
FROM per_all_assignments_f
WHERE business_group_id = p_business_group_id
and person_id = l_person_id
and assignment_id = l_assignment_id
and effective_start_Date = p_hire_date;
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => l_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => l_original_date_of_hire
,p_adjusted_svc_date => l_adjusted_svc_date
,p_person_id => l_person_id
,p_assignment_id => l_assignment_id
,p_per_object_version_number => l_per_object_version_number
,p_asg_object_version_number => l_asg_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_per_comment_id => l_per_comment_id
,p_assignment_sequence => l_assignment_sequence
,p_assignment_number => l_assignment_number
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_work_schedule in varchar2 default null
,p_suffix in varchar2 default null
,p_benefit_group_id in number default null
,p_receipt_of_death_cert_date in date default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default 'N'
,p_coord_ben_med_ext_er in varchar2 default null
,p_coord_ben_med_pl_name in varchar2 default null
,p_coord_ben_med_insr_crr_name in varchar2 default null
,p_coord_ben_med_insr_crr_ident in varchar2 default null
,p_coord_ben_med_cvg_strt_dt in date default null
,p_coord_ben_med_cvg_end_dt in date default null
,p_uses_tobacco_flag in varchar2 default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
,p_original_date_of_hire in date default null
,p_adjusted_svc_date in date default null
,p_town_of_birth in varchar2 default null
,p_region_of_birth in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_global_person_id in varchar2 default null
,p_party_id in number default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
,p_assign_payroll_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_assignment_number => p_assignment_number
,p_name_combination_warning => p_name_combination_warning
,p_assign_payroll_warning => p_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_work_schedule in varchar2 default null
,p_suffix in varchar2 default null
,p_benefit_group_id in number default null
,p_receipt_of_death_cert_date in date default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default 'N'
,p_coord_ben_med_ext_er in varchar2 default null
,p_coord_ben_med_pl_name in varchar2 default null
,p_coord_ben_med_insr_crr_name in varchar2 default null
,p_coord_ben_med_insr_crr_ident in varchar2 default null
,p_coord_ben_med_cvg_strt_dt in date default null
,p_coord_ben_med_cvg_end_dt in date default null
,p_uses_tobacco_flag in varchar2 default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
,p_original_date_of_hire in date default null
,p_adjusted_svc_date in date default null
,p_town_of_birth in varchar2 default null
,p_region_of_birth in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_global_person_id in varchar2 default null
,p_party_id in number default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
,p_assign_payroll_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
select legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select 1 into l_session_c from fnd_sessions
where session_id=userenv('sessionid');
insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'),p_hire_date);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => l_original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
--
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_assignment_number => p_assignment_number
,p_name_combination_warning => p_name_combination_warning
,p_assign_payroll_warning => p_assign_payroll_warning
,p_orig_hire_warning => p_orig_hire_warning
);
delete from fnd_sessions where session_id=userenv('sessionid');
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_work_schedule in varchar2 default null
,p_suffix in varchar2 default null
,p_benefit_group_id in number default null
,p_receipt_of_death_cert_date in date default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default 'N'
,p_coord_ben_med_ext_er in varchar2 default null
,p_coord_ben_med_pl_name in varchar2 default null
,p_coord_ben_med_insr_crr_name in varchar2 default null
,p_coord_ben_med_insr_crr_ident in varchar2 default null
,p_coord_ben_med_cvg_strt_dt in date default null
,p_coord_ben_med_cvg_end_dt in date default null
,p_uses_tobacco_flag in varchar2 default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
,p_original_date_of_hire in date default null
,p_adjusted_svc_date in date default null
,p_town_of_birth in varchar2 default null
,p_region_of_birth in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_global_person_id in varchar2 default null
,p_party_id in number default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
,p_assign_payroll_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_assignment_number => p_assignment_number
,p_name_combination_warning => p_name_combination_warning
,p_assign_payroll_warning => p_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_work_schedule in varchar2 default null
,p_suffix in varchar2 default null
,p_benefit_group_id in number default null
,p_receipt_of_death_cert_date in date default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default 'N'
,p_coord_ben_med_ext_er in varchar2 default null
,p_coord_ben_med_pl_name in varchar2 default null
,p_coord_ben_med_insr_crr_name in varchar2 default null
,p_coord_ben_med_insr_crr_ident in varchar2 default null
,p_coord_ben_med_cvg_strt_dt in date default null
,p_coord_ben_med_cvg_end_dt in date default null
,p_uses_tobacco_flag in varchar2 default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
,p_original_date_of_hire in date default null
,p_adjusted_svc_date in date default null
,p_town_of_birth in varchar2 default null
,p_region_of_birth in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_global_person_id in varchar2 default null
,p_party_id in number default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
,p_assign_payroll_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ;
select legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
--
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_assignment_number => p_assignment_number
,p_name_combination_warning => p_name_combination_warning
,p_assign_payroll_warning => p_assign_payroll_warning
,p_orig_hire_warning => p_orig_hire_warning
);
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_work_schedule in varchar2 default null
,p_suffix in varchar2 default null
,p_benefit_group_id in number default null
,p_receipt_of_death_cert_date in date default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default 'N'
,p_coord_ben_med_ext_er in varchar2 default null
,p_coord_ben_med_pl_name in varchar2 default null
,p_coord_ben_med_insr_crr_name in varchar2 default null
,p_coord_ben_med_insr_crr_ident in varchar2 default null
,p_coord_ben_med_cvg_strt_dt in date default null
,p_coord_ben_med_cvg_end_dt in date default null
,p_uses_tobacco_flag in varchar2 default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
,p_original_date_of_hire in date default null
,p_adjusted_svc_date in date default null
,p_town_of_birth in varchar2 default null
,p_region_of_birth in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_global_person_id in varchar2 default null
,p_party_id in number default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
,p_assign_payroll_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ;
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,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_coord_ben_med_ext_er => p_coord_ben_med_ext_er
,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_assignment_number => p_assignment_number
,p_name_combination_warning => p_name_combination_warning
,p_assign_payroll_warning => p_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
select effective_start_date
from per_person_type_usages_f ptu,per_person_types ppt
where ptu.person_type_id = ppt.person_type_id
and ptu.person_id = p_person_id
and p_hire_date between ptu.effective_start_date and ptu.effective_end_date
and ppt.system_person_type='EX_EMP';
/* select per.business_group_id,
per.employee_number,
per.effective_start_date,
pet.system_person_type
from per_person_types pet,
per_people_f per
where per.person_id = p_person_id
and pet.person_type_id = per.person_type_id
and pet.business_group_id + 0 = per.business_group_id
and p_effective_date
between per.effective_start_date
and per.effective_end_date;*/
select per.business_group_id,
per.employee_number,
per.effective_start_date,
pet.system_person_type
from per_person_types pet,
per_people_f per,
per_person_type_usages_f ptu
where per.person_id = p_person_id
and pet.person_type_id = ptu.person_type_id
and ptu.person_id = per.person_id
-- added this condition for 5601538
and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
/*and ptu.effective_start_date = per.effective_start_date
and ptu.effective_end_date = per.effective_end_date commented for bug 5601538*/
and pet.business_group_id + 0 = per.business_group_id
and p_effective_date
between per.effective_start_date
and per.effective_end_date;
select pos.actual_termination_date,
pos.last_standard_process_date,
pos.final_process_date
--
-- 115.57 (END)
--
from per_periods_of_service pos
where pos.person_id = p_person_id
order by pos.date_start desc;
SELECT bus.legislation_code
FROM per_people_f per
,per_business_groups bus
WHERE per.person_id = csr_per_legislation.p_person_id
AND per.business_group_id+0 = bus.business_group_id
AND csr_per_legislation.p_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date;
l_datetrack_mode := 'UPDATE';
l_ptu_datetrack_mode := 'UPDATE';
,p_datetrack_update_mode => l_ptu_datetrack_mode -- #3611984 l_datetrack_mode
);
select ppf.business_group_id
,ppf.employee_number
,ppf.npw_number
,ppt.system_person_type
from per_person_types ppt
,per_people_f ppf
where ppf.person_id = p_person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.business_group_id + 0 = ppf.business_group_id
and l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
select person_type_id
from per_person_types
where business_group_id = l_business_group_id
and active_flag = 'Y'
and default_flag = 'Y'
and system_person_type = 'EMP_APL';
select organization_id
,legislation_code
,default_start_time
,default_end_time
,fnd_number.canonical_to_number(working_hours)
,frequency
from per_business_groups
where business_group_id = l_business_group_id;
select recruiter_id
,grade_id
,position_id
,job_id
,location_id
,people_group_id
,organization_id -- added org id to cursor. thayden 7/10.
,business_group_id -- added business_group_id to cursor lma 7/11
from per_vacancies
where vacancy_id = p_vacancy_id;
,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
,p_person_type_id IN NUMBER DEFAULT NULL
,p_national_identifier IN VARCHAR2 DEFAULT NULL
,p_per_information7 IN VARCHAR2 DEFAULT NULL -- 3414724
,p_effective_start_date OUT NOCOPY DATE
,p_effective_end_date OUT NOCOPY DATE
,p_assign_payroll_warning OUT NOCOPY BOOLEAN
,p_orig_hire_warning OUT NOCOPY BOOLEAN
)
IS
--
-- Local variables
--
l_proc VARCHAR2(72) := g_package||'hire_into_job';
,p_datetrack_update_mode => p_datetrack_update_mode
,p_person_type_id => p_person_type_id
,p_national_identifier => p_national_identifier
,p_per_information7 => p_per_information7
,p_assignment_id => l_assignment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_assign_payroll_warning => p_assign_payroll_warning
,p_orig_hire_warning => p_orig_hire_warning
);
,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
,p_person_type_id IN NUMBER DEFAULT NULL
,p_national_identifier IN VARCHAR2 DEFAULT NULL
,p_per_information7 IN VARCHAR2 DEFAULT NULL -- 3414724
,p_assignment_id OUT NOCOPY NUMBER --Bug#3919096
,p_effective_start_date OUT NOCOPY DATE
,p_effective_end_date OUT NOCOPY DATE
,p_assign_payroll_warning OUT NOCOPY BOOLEAN
,p_orig_hire_warning OUT NOCOPY BOOLEAN
)
IS
--
-- Local variables
--
l_proc VARCHAR2(72) := g_package||'hire_into_job';
l_datetrack_update_mode VARCHAR2(30) := p_datetrack_update_mode;
select pos.actual_termination_date,
pos.last_standard_process_date,
pos.final_process_date
--
-- 115.57 (END)
--
from per_periods_of_service pos
where pos.person_id = p_person_id
order by pos.date_start desc;
SELECT pet.person_type_id
,pet.system_person_type
,per.effective_start_date
,per.effective_end_date
,per.applicant_number
,per.employee_number
,per.npw_number
,bus.business_group_id
,bus.legislation_code
FROM per_people_f per
,per_business_groups bus
,per_person_types pet
WHERE per.person_type_id = pet.person_type_id
AND per.business_group_id+0 = bus.business_group_id
AND per.person_id = csr_per_details.p_person_id
AND csr_per_details.p_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date;
,p_datetrack_update_mode => p_datetrack_update_mode
,p_employee_number => p_employee_number
,p_person_type_id => p_person_type_id
,p_national_identifier => p_national_identifier
,p_per_information7 => p_per_information7 --3414274
);
IF (l_datetrack_update_mode IS NULL)
THEN
if l_effective_date = l_per_details_rec.effective_start_date then -- 3194314
l_datetrack_update_mode := hr_api.g_correction;
l_datetrack_update_mode := hr_api.g_update;
IF (l_datetrack_update_mode NOT IN (hr_api.g_update,hr_api.g_correction))
THEN
hr_utility.set_message(800,'HR_7203_DT_UPD_MODE_INVALID');
l_datetrack_update_mode := hr_api.g_update;
,p_datetrack_mode => l_datetrack_update_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
,p_npw_number => l_per_details_rec.npw_number
,p_per_information7 => p_per_information7 --3414274
);
,p_datetrack_update_mode => l_datetrack_update_mode -- Bug 3230389
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_employee_number => p_employee_number
,p_person_type_id => p_person_type_id
,p_national_identifier => p_national_identifier
,p_per_information7 => p_per_information7 -- 3414274
,p_assignment_id => l_assignment_id --Bug#3919096
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
PROCEDURE update_rehire_primary_asgs
(p_person_id IN NUMBER
,p_rehire_date IN DATE
,p_cancel IN VARCHAR2
) IS
--
l_proc VARCHAR2(80) := g_package||'update_rehire_primary_asgs';
SELECT assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,title
,object_version_number
,contract_id
,cagr_id_flex_num
,cagr_grade_def_id
,establishment_id
,collective_agreement_id
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,period_of_placement_date_start
,vendor_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,grade_ladder_pgm_id
,supervisor_assignment_id
FROM per_assignments_f
WHERE person_id = c_prim_asg.p_person_id
AND primary_flag = 'Y'
AND assignment_type <> 'B' -- 115.59
AND c_prim_asg.p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SAVEPOINT update_rehire_primary_asgs;
UPDATE per_assignments_f
SET primary_flag = 'Y'
,object_version_number = object_version_number + 1
WHERE assignment_id = lr_prim_asg.assignment_id
AND assignment_type <> 'B' -- 115.59
AND effective_start_date >= p_rehire_date;
UPDATE per_assignments_f
SET primary_flag = 'N'
,object_version_number = object_version_number + 1
WHERE assignment_id = lr_prim_asg.assignment_id
AND assignment_type <> 'B' -- 115.59
AND effective_start_date = p_rehire_date;
UPDATE per_assignments_f
SET effective_end_date = (p_rehire_date - 1)
,object_version_number = object_version_number + 1
WHERE assignment_id = lr_prim_asg.assignment_id
AND assignment_type <> 'B' -- 115.59
AND effective_end_date = p_rehire_date;
INSERT INTO per_all_assignments_f
(assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,title
,object_version_number
,contract_id
,cagr_id_flex_num
,cagr_grade_def_id
,establishment_id
,collective_agreement_id
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,period_of_placement_date_start
,vendor_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,grade_ladder_pgm_id
,supervisor_assignment_id
)
VALUES
(lr_prim_asg.assignment_id
,p_rehire_date -- New ESD
,p_rehire_date -- New EED
,lr_prim_asg.business_group_id
,lr_prim_asg.recruiter_id
,lr_prim_asg.grade_id
,lr_prim_asg.position_id
,lr_prim_asg.job_id
,lr_prim_asg.assignment_status_type_id
,lr_prim_asg.payroll_id
,lr_prim_asg.location_id
,lr_prim_asg.person_referred_by_id
,lr_prim_asg.supervisor_id
,lr_prim_asg.special_ceiling_step_id
,lr_prim_asg.person_id
,lr_prim_asg.recruitment_activity_id
,lr_prim_asg.source_organization_id
,lr_prim_asg.organization_id
,lr_prim_asg.people_group_id
,lr_prim_asg.soft_coding_keyflex_id
,lr_prim_asg.vacancy_id
,lr_prim_asg.pay_basis_id
,lr_prim_asg.assignment_sequence
,lr_prim_asg.assignment_type
,'N' -- New Primary Flag
,lr_prim_asg.application_id
,lr_prim_asg.assignment_number
,lr_prim_asg.change_reason
,lr_prim_asg.comment_id
,lr_prim_asg.date_probation_end
,lr_prim_asg.default_code_comb_id
,lr_prim_asg.employment_category
,lr_prim_asg.frequency
,lr_prim_asg.internal_address_line
,lr_prim_asg.manager_flag
,lr_prim_asg.normal_hours
,lr_prim_asg.perf_review_period
,lr_prim_asg.perf_review_period_frequency
,lr_prim_asg.period_of_service_id
,lr_prim_asg.probation_period
,lr_prim_asg.probation_unit
,lr_prim_asg.sal_review_period
,lr_prim_asg.sal_review_period_frequency
,lr_prim_asg.set_of_books_id
,lr_prim_asg.source_type
,lr_prim_asg.time_normal_finish
,lr_prim_asg.time_normal_start
,lr_prim_asg.bargaining_unit_code
,lr_prim_asg.labour_union_member_flag
,lr_prim_asg.hourly_salaried_code
,lr_prim_asg.request_id
,lr_prim_asg.program_application_id
,lr_prim_asg.program_id
,lr_prim_asg.program_update_date
,lr_prim_asg.ass_attribute_category
,lr_prim_asg.ass_attribute1
,lr_prim_asg.ass_attribute2
,lr_prim_asg.ass_attribute3
,lr_prim_asg.ass_attribute4
,lr_prim_asg.ass_attribute5
,lr_prim_asg.ass_attribute6
,lr_prim_asg.ass_attribute7
,lr_prim_asg.ass_attribute8
,lr_prim_asg.ass_attribute9
,lr_prim_asg.ass_attribute10
,lr_prim_asg.ass_attribute11
,lr_prim_asg.ass_attribute12
,lr_prim_asg.ass_attribute13
,lr_prim_asg.ass_attribute14
,lr_prim_asg.ass_attribute15
,lr_prim_asg.ass_attribute16
,lr_prim_asg.ass_attribute17
,lr_prim_asg.ass_attribute18
,lr_prim_asg.ass_attribute19
,lr_prim_asg.ass_attribute20
,lr_prim_asg.ass_attribute21
,lr_prim_asg.ass_attribute22
,lr_prim_asg.ass_attribute23
,lr_prim_asg.ass_attribute24
,lr_prim_asg.ass_attribute25
,lr_prim_asg.ass_attribute26
,lr_prim_asg.ass_attribute27
,lr_prim_asg.ass_attribute28
,lr_prim_asg.ass_attribute29
,lr_prim_asg.ass_attribute30
,TRUNC(SYSDATE) -- New Last Update Date
,-1 -- New Updated By
,-1 -- New Update Login
,-1 -- New Created By
,TRUNC(SYSDATE) -- New Creation Date
,lr_prim_asg.title
,1 -- New OVN
,lr_prim_asg.contract_id
,lr_prim_asg.cagr_id_flex_num
,lr_prim_asg.cagr_grade_def_id
,lr_prim_asg.establishment_id
,lr_prim_asg.collective_agreement_id
,lr_prim_asg.notice_period
,lr_prim_asg.notice_period_uom
,lr_prim_asg.employee_category
,lr_prim_asg.work_at_home
,lr_prim_asg.job_post_source_name
,lr_prim_asg.period_of_placement_date_start
,lr_prim_asg.vendor_id
,lr_prim_asg.vendor_site_id
,lr_prim_asg.po_header_id
,lr_prim_asg.po_line_id
,lr_prim_asg.projected_assignment_end
,lr_prim_asg.vendor_employee_number
,lr_prim_asg.vendor_assignment_number
,lr_prim_asg.assignment_category
,lr_prim_asg.project_title
,lr_prim_asg.grade_ladder_pgm_id
,lr_prim_asg.supervisor_assignment_id
);
UPDATE per_assignments_f
SET effective_end_date = (p_rehire_date - 1)
,object_version_number = object_version_number + 1
WHERE assignment_id = lr_prim_asg.assignment_id
AND assignment_type <> 'B' -- 115.59
AND effective_start_date = lr_prim_asg.effective_start_date;
INSERT INTO per_all_assignments_f
(assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,title
,object_version_number
,contract_id
,cagr_id_flex_num
,cagr_grade_def_id
,establishment_id
,collective_agreement_id
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,period_of_placement_date_start
,vendor_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,grade_ladder_pgm_id
,supervisor_assignment_id
)
VALUES
(lr_prim_asg.assignment_id
,p_rehire_date -- New ESD
,lr_prim_asg.effective_end_date
,lr_prim_asg.business_group_id
,lr_prim_asg.recruiter_id
,lr_prim_asg.grade_id
,lr_prim_asg.position_id
,lr_prim_asg.job_id
,lr_prim_asg.assignment_status_type_id
,lr_prim_asg.payroll_id
,lr_prim_asg.location_id
,lr_prim_asg.person_referred_by_id
,lr_prim_asg.supervisor_id
,lr_prim_asg.special_ceiling_step_id
,lr_prim_asg.person_id
,lr_prim_asg.recruitment_activity_id
,lr_prim_asg.source_organization_id
,lr_prim_asg.organization_id
,lr_prim_asg.people_group_id
,lr_prim_asg.soft_coding_keyflex_id
,lr_prim_asg.vacancy_id
,lr_prim_asg.pay_basis_id
,lr_prim_asg.assignment_sequence
,lr_prim_asg.assignment_type
,'N' -- New Primary Flag
,lr_prim_asg.application_id
,lr_prim_asg.assignment_number
,lr_prim_asg.change_reason
,lr_prim_asg.comment_id
,lr_prim_asg.date_probation_end
,lr_prim_asg.default_code_comb_id
,lr_prim_asg.employment_category
,lr_prim_asg.frequency
,lr_prim_asg.internal_address_line
,lr_prim_asg.manager_flag
,lr_prim_asg.normal_hours
,lr_prim_asg.perf_review_period
,lr_prim_asg.perf_review_period_frequency
,lr_prim_asg.period_of_service_id
,lr_prim_asg.probation_period
,lr_prim_asg.probation_unit
,lr_prim_asg.sal_review_period
,lr_prim_asg.sal_review_period_frequency
,lr_prim_asg.set_of_books_id
,lr_prim_asg.source_type
,lr_prim_asg.time_normal_finish
,lr_prim_asg.time_normal_start
,lr_prim_asg.bargaining_unit_code
,lr_prim_asg.labour_union_member_flag
,lr_prim_asg.hourly_salaried_code
,lr_prim_asg.request_id
,lr_prim_asg.program_application_id
,lr_prim_asg.program_id
,lr_prim_asg.program_update_date
,lr_prim_asg.ass_attribute_category
,lr_prim_asg.ass_attribute1
,lr_prim_asg.ass_attribute2
,lr_prim_asg.ass_attribute3
,lr_prim_asg.ass_attribute4
,lr_prim_asg.ass_attribute5
,lr_prim_asg.ass_attribute6
,lr_prim_asg.ass_attribute7
,lr_prim_asg.ass_attribute8
,lr_prim_asg.ass_attribute9
,lr_prim_asg.ass_attribute10
,lr_prim_asg.ass_attribute11
,lr_prim_asg.ass_attribute12
,lr_prim_asg.ass_attribute13
,lr_prim_asg.ass_attribute14
,lr_prim_asg.ass_attribute15
,lr_prim_asg.ass_attribute16
,lr_prim_asg.ass_attribute17
,lr_prim_asg.ass_attribute18
,lr_prim_asg.ass_attribute19
,lr_prim_asg.ass_attribute20
,lr_prim_asg.ass_attribute21
,lr_prim_asg.ass_attribute22
,lr_prim_asg.ass_attribute23
,lr_prim_asg.ass_attribute24
,lr_prim_asg.ass_attribute25
,lr_prim_asg.ass_attribute26
,lr_prim_asg.ass_attribute27
,lr_prim_asg.ass_attribute28
,lr_prim_asg.ass_attribute29
,lr_prim_asg.ass_attribute30
,TRUNC(SYSDATE) -- New Last Update Date
,-1 -- New Updated By
,-1 -- New Update Login
,-1 -- New Created By
,TRUNC(SYSDATE) -- New Creation Date
,lr_prim_asg.title
,1 -- New OVN
,lr_prim_asg.contract_id
,lr_prim_asg.cagr_id_flex_num
,lr_prim_asg.cagr_grade_def_id
,lr_prim_asg.establishment_id
,lr_prim_asg.collective_agreement_id
,lr_prim_asg.notice_period
,lr_prim_asg.notice_period_uom
,lr_prim_asg.employee_category
,lr_prim_asg.work_at_home
,lr_prim_asg.job_post_source_name
,lr_prim_asg.period_of_placement_date_start
,lr_prim_asg.vendor_id
,lr_prim_asg.vendor_site_id
,lr_prim_asg.po_header_id
,lr_prim_asg.po_line_id
,lr_prim_asg.projected_assignment_end
,lr_prim_asg.vendor_employee_number
,lr_prim_asg.vendor_assignment_number
,lr_prim_asg.assignment_category
,lr_prim_asg.project_title
,lr_prim_asg.grade_ladder_pgm_id
,lr_prim_asg.supervisor_assignment_id
);
UPDATE per_assignments_f
SET primary_flag = 'N'
,object_version_number = object_version_number + 1
WHERE assignment_id = lr_prim_asg.assignment_id
AND effective_start_date > p_rehire_date;
ROLLBACK TO update_rehire_primary_asgs;
END update_rehire_primary_asgs;
SELECT pds.period_of_service_id
,pds.date_start
,pds.actual_termination_date
,pds.last_standard_process_date
,pds.final_process_date
FROM per_periods_of_service pds
WHERE pds.person_id = p_person_id
AND pds.date_start < p_rehire_date
ORDER BY pds.date_start DESC;
SELECT asg.assignment_id
,asg.effective_start_date
,asg.effective_end_date
FROM per_assignments_f asg
WHERE asg.period_of_service_id = csr_prim_asg.p_period_of_service_id
AND asg.person_id = csr_prim_asg.p_person_id
AND csr_prim_asg.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.primary_flag = 'Y';
update_rehire_primary_asgs
(p_person_id => p_person_id
,p_rehire_date => p_rehire_date
,p_cancel => p_cancel
);