[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_primary_asg_affectation
(
p_validate in boolean default false,
p_assignment_id in number,
p_effective_date in date,
p_primary_affectation in varchar2,
p_organization_id in number,
p_job_id in number,
p_position_id in number,
p_datetrack_update_mode in varchar2,
p_object_version_number in number,
p_person_id in number,
p_supervisor_id in number default null
)
is
l_validate boolean;
l_spp_delete_warning boolean;
Select effective_start_date from
per_all_assignments_f
where primary_flag = 'Y'
and person_id = p_person_id
and assignment_id = p_assignment_id
and effective_start_date > p_effective_date;
hr_assignment_api.update_emp_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => l_ovn -- In OUT
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
update per_all_assignments_f
set supervisor_id = p_supervisor_id
where
person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date
and assignment_id = p_assignment_id
and primary_flag = 'Y';
hr_assignment_api.update_cwk_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => l_ovn -- In OUT
,p_people_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
update per_all_assignments_f
set supervisor_id = p_supervisor_id
where
person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date
and assignment_id = p_assignment_id
and primary_flag = 'Y';
update per_all_assignments_f
set position_id = p_position_id,
job_id = p_job_id,
organization_id = p_organization_id,
supervisor_id = p_supervisor_id
where
person_id = p_person_id
and effective_start_date = l_fut_pri_start_date
and assignment_id = p_assignment_id
and primary_flag = 'Y';
end update_primary_asg_affectation;
Select people_group_id, establishment_id , scl.segment10 FrEmpCategory,
assignment_id,object_version_number,normal_hours,frequency,business_group_id
from per_all_assignments_f asg, hr_soft_coding_keyflex scl
where person_id = p_person_id
and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and p_effective_date between effective_start_date and effective_end_date
and primary_flag ='Y';
update_primary_asg_affectation
(
p_validate => p_validate,
p_assignment_id => l_admin_career_id,
p_effective_date => p_effective_date,
p_primary_affectation => p_primary_affectation,
p_organization_id => p_organization_id,
p_job_id => p_job_id,
p_position_id => p_position_id,
p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
p_object_version_number => l_p_asg_ovn,
p_person_id => p_person_id,
p_supervisor_id => p_supervisor_id
) ;
procedure update_affectation
(p_validate in boolean default false
,p_datetrack_update_mode in varchar2
,p_effective_date in date
,p_organization_id in number
,p_position_id in number
,p_person_id in number
,p_job_id in number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_assignment_status_type_id in number
,p_identifier in varchar2
,p_affectation_type in varchar2
,p_percent_effected in varchar2
,p_primary_affectation in varchar2
,p_group_name out nocopy varchar2
,p_scl_concat_segments in varchar2
,p_assignment_id in number
,p_soft_coding_keyflex_id out nocopy number
,p_object_version_number in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_assignment_sequence out nocopy number
) IS
l_validate boolean;
l_spp_delete_warning boolean;
l_proc varchar2(72) := g_package ||'update_affecations';
Select people_group_id, establishment_id , scl.segment10 FrEmpCategory,assignment_id
,object_version_number,normal_hours
from per_all_assignments_f asg, hr_soft_coding_keyflex scl
where person_id = p_person_id
and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and p_effective_date between effective_start_date and effective_end_date
and primary_flag ='Y';
Select segment27 PrimayAffectation
from per_all_assignments_f asg, hr_soft_coding_keyflex scl
where assignment_id = p_assignment_id
and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and p_effective_date between effective_start_date and effective_end_date;
Y Y 1. Update Primary Assignment with Position, Job , org
2. Update Affecation with Remaining Details
N N 1. Update Affecation with complete details
N Y 1. Check are there any Primary affecations in the System, If so Throw an error
Saying effective affectation is already exist, Else Similar to first case
Y N 1. Update the Primary assignment by removing Job and Position
2. Update Affecation with Other details + Position + Job
Earlier Value: Retrieve from assignments table for the affection id
Current Value: Value which is passed from UI
*/
If (l_old_primary_affectation is null) then
-- Creating Affectation for the first time, <=> there are no affecations earlier
--
l_old_primary_affectation := 'N';
hr_assignment_api.update_cwk_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => null
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_people_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_cwk_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_normal_hours => l_normal_hours
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
-- ,p_assignment_status_type_id => p_assignment_status_type_id
,p_establishment_id => l_establishment_id
,p_scl_segment23 => p_identifier
,p_scl_segment24 => p_affectation_type
,p_scl_segment25 => p_percent_effected
,p_scl_segment26 => l_admin_career_id
,p_scl_segment27 => p_primary_affectation
,p_scl_segment2 => 'CIVIL'
,p_scl_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2
,p_org_now_no_manager_warning => l_other_manager_warning2
,p_hourly_salaried_warning => l_other_manager_warning2 );
hr_assignment_api.update_emp_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => null
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_emp_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_normal_hours => l_normal_hours
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_establishment_id => l_establishment_id
,p_segment23 => p_identifier
,p_segment24 => p_affectation_type
,p_segment25 => p_percent_effected
,p_segment26 => l_admin_career_id
,p_segment27 => p_primary_affectation
,p_segment2 => 'CIVIL'
,p_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2 );
update_primary_asg_affectation
(
p_validate => p_validate,
p_assignment_id => l_admin_career_id,
p_effective_date => p_effective_date,
p_primary_affectation => p_primary_affectation,
p_organization_id => p_organization_id,
p_job_id => p_job_id,
p_position_id => p_position_id,
p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
p_object_version_number => l_p_asg_ovn,
p_person_id => p_person_id,
p_supervisor_id => p_supervisor_id
) ;
hr_assignment_api.update_emp_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_emp_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_normal_hours => l_normal_hours
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_establishment_id => l_establishment_id
,p_segment23 => p_identifier
,p_segment24 => p_affectation_type
,p_segment25 => p_percent_effected
,p_segment26 => l_admin_career_id
,p_segment27 => p_primary_affectation
,p_segment2 => 'CIVIL'
,p_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2 );
hr_assignment_api.update_cwk_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_people_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_cwk_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_normal_hours => l_normal_hours
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
-- ,p_assignment_status_type_id => p_assignment_status_type_id
,p_establishment_id => l_establishment_id
,p_scl_segment23 => p_identifier
,p_scl_segment24 => p_affectation_type
,p_scl_segment25 => p_percent_effected
,p_scl_segment26 => l_admin_career_id
,p_scl_segment27 => p_primary_affectation
,p_scl_segment2 => 'CIVIL'
,p_scl_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2
,p_org_now_no_manager_warning => l_other_manager_warning2
,p_hourly_salaried_warning => l_other_manager_warning2 );
hr_assignment_api.update_emp_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => null
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_emp_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_normal_hours => l_normal_hours
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_establishment_id => l_establishment_id
,p_segment23 => p_identifier
,p_segment24 => p_affectation_type
,p_segment25 => p_percent_effected
,p_segment26 => l_admin_career_id
,p_segment27 => p_primary_affectation
,p_segment2 => 'CIVIL'
,p_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2 );
hr_assignment_api.update_cwk_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => null
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_people_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_cwk_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_establishment_id => l_establishment_id
,p_normal_hours => l_normal_hours
,p_scl_segment23 => p_identifier
,p_scl_segment24 => p_affectation_type
,p_scl_segment25 => p_percent_effected
,p_scl_segment26 => l_admin_career_id
,p_scl_segment27 => p_primary_affectation
,p_scl_segment2 => 'CIVIL'
,p_scl_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2
,p_org_now_no_manager_warning => l_other_manager_warning2
,p_hourly_salaried_warning => l_other_manager_warning2 );
update_primary_asg_affectation
(
p_validate => p_validate,
p_assignment_id => l_admin_career_id,
p_effective_date => p_effective_date,
p_primary_affectation => p_primary_affectation,
p_organization_id => p_organization_id,
p_job_id => p_job_id,
p_position_id => p_position_id,
p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
p_object_version_number => l_p_asg_ovn,
p_person_id => p_person_id,
p_supervisor_id => p_supervisor_id
) ;
update_primary_asg_affectation
(
p_validate => p_validate,
p_assignment_id => l_admin_career_id,
p_effective_date => p_effective_date,
p_primary_affectation => l_old_primary_affectation,
p_organization_id => p_organization_id,
p_job_id => p_job_id,
p_position_id => null,
p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
p_object_version_number => l_p_asg_ovn,
p_person_id => p_person_id,
p_supervisor_id => p_supervisor_id
) ;
hr_assignment_api.update_emp_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_emp_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_normal_hours => l_normal_hours
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_establishment_id => l_establishment_id
,p_segment23 => p_identifier
,p_segment24 => p_affectation_type
,p_segment25 => p_percent_effected
,p_segment26 => l_admin_career_id
,p_segment27 => p_primary_affectation
,p_segment2 => 'CIVIL'
,p_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2 );
hr_assignment_api.update_cwk_asg_criteria
(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_job_id => p_job_id
-- Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_people_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_cwk_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_normal_hours => l_normal_hours
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_establishment_id => l_establishment_id
,p_scl_segment23 => p_identifier
,p_scl_segment24 => p_affectation_type
,p_scl_segment25 => p_percent_effected
,p_scl_segment26 => l_admin_career_id
,p_scl_segment27 => p_primary_affectation
,p_scl_segment2 => 'CIVIL'
,p_scl_segment10 => l_fr_emp_category
-- Following are Out Parameters
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2
,p_org_now_no_manager_warning => l_other_manager_warning2
,p_hourly_salaried_warning => l_other_manager_warning2 );
end update_affectation;
PROCEDURE update_employment_terms
(p_validate IN BOOLEAN DEFAULT FALSE
,p_datetrack_update_mode IN VARCHAR2
,p_effective_date IN DATE
,p_assignment_id IN NUMBER
,p_establishment_id IN NUMBER
,p_comments IN VARCHAR2
,p_assignment_category IN VARCHAR2
,p_reason_for_parttime IN VARCHAR2
,p_working_hours_share IN VARCHAR2
,p_contract_id IN NUMBER
,p_change_reason IN VARCHAR2
,p_normal_hours IN NUMBER
,p_frequency IN VARCHAR2
,p_soft_coding_keyflex_id OUT NOCOPY NUMBER
,p_object_version_number IN OUT NOCOPY NUMBER
,p_effective_start_date OUT NOCOPY DATE
,p_effective_end_date OUT NOCOPY DATE
,p_assignment_sequence OUT NOCOPY NUMBER
)
IS
--
--Cursor to fetch Current Assignment
CURSOR csr_asg_information IS
SELECT asg.assignment_type, ast.per_system_status
FROM per_all_assignments_f asg,
per_assignment_status_types ast
WHERE asg.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id;
CURSOR csr_update_admin_career(p_effective_end_date DATE
,p_employment_category VARCHAR2
,p_contract_id NUMBER
,p_reason_for_parttime VARCHAR2
,p_comments VARCHAR2) IS
SELECT asg.assignment_id,
asg.object_version_number,
asg.soft_coding_keyflex_id,
asg.grade_ladder_pgm_id,
asg.grade_id,
scl.segment10 "Employee Category",
asg.effective_start_date,
asg.effective_end_date,
'ET',
asg.employment_category,
asg.contract_id,
asg.establishment_id,
scl.segment9 "Working Hours Share",
scl.segment19 "Reason For Part",
scl.segment20 "Comments"
FROM per_all_assignments_f asg,
hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
AND asg.effective_start_date >= p_effective_end_date
AND NVL(asg.employment_category,p_employment_category) = p_employment_category
AND NVL(asg.contract_id,p_contract_id) = p_contract_id
AND NVL(asg.establishment_id,p_establishment_id) = p_establishment_id
AND NVL(scl.segment9,p_working_hours_share) = p_working_hours_share
AND NVL(scl.segment19,p_reason_for_parttime) = p_reason_for_parttime
AND NVL(scl.segment20,p_comments) = p_comments;
SELECT normal_hours hours, normal_hours_frequency frequency
FROM pqh_corps_definitions
WHERE ben_pgm_id = (SELECT grade_ladder_pgm_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date)
UNION
SELECT fnd_number.canonical_to_number (org_information4) hours, 'M' frequency
FROM hr_organization_information_v
WHERE org_information_context = 'FR_ESTAB_INFO'
AND organization_id = p_establishment_id;
l_spp_delete_warning BOOLEAN;
update_emp_asg_criteria
-p_employment_category -> p_assignment_category
update_emp_asg
-p_establishment_id
-p_comments - scl.20
-p_reason_for_parttime - scl.19
-p_working_hours_share - scl.9
-p_contract_id
-p_change_reason
*/
--
IF l_asg_type = 'E' THEN
--Employee Assignment
hr_assignment_api.update_emp_asg_criteria
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_employment_category => p_assignment_category
--Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- IN OUT
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_segment17 => p_change_reason
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_segment20 => p_comments
,p_segment19 => p_reason_for_parttime
,p_segment9 => p_working_hours_share
,p_normal_hours => l_wrk_hours
,p_frequency => l_frequency
,p_segment2 => 'CIVIL'
--Following are Out Parameters
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2 );
If Employment Terms record is updated will have to keep the Career Information
record in Sync with Employment Terms record: Steps as follows
1. Find the all Assignment records whose effective start date > the p_effective_end_date
2. For all those assignment records compare career details with current rec career detial
If It matches update Employment Terms Information for those records
*/
hr_utility.set_location(' Before Loop ' , 10);
FOR l_asg_rec in csr_update_admin_career(p_effective_end_date,p_assignment_category,-1
,NVL(p_reason_for_parttime,-1),NVL(p_comments,-1))
LOOP
--
l_ovn := l_asg_rec.object_version_number;
hr_assignment_api.update_emp_asg_criteria
(p_validate => p_validate
,p_effective_date => l_asg_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_asg_rec.assignment_id
,p_employment_category => p_assignment_category
--Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => l_ovn -- In OUT
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => l_asg_rec.effective_start_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => l_asg_rec.assignment_id
,p_object_version_number => l_ovn
,p_segment17 => p_change_reason
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_segment20 => p_comments
,p_segment19 => p_reason_for_parttime
,p_segment9 => p_working_hours_share
,p_normal_hours => l_wrk_hours
,p_frequency => l_frequency
,p_segment2 => 'CIVIL'
--Out Parameters
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2);
hr_assignment_api.update_cwk_asg_criteria
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
--,p_employment_category => p_assignment_category
--Out Variables
,p_people_group_id => l_people_group_id
,p_object_version_number => p_object_version_number -- In OUT
,p_people_group_name => l_group_name
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
hr_assignment_api.update_cwk_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_change_reason => p_change_reason
--,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_scl_segment20 => p_comments
,p_scl_segment19 => p_reason_for_parttime
,p_scl_segment9 => p_working_hours_share
,p_normal_hours => l_wrk_hours
,p_frequency => l_frequency
,p_scl_segment2 => 'CIVIL'
--Out Parameters
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_no_managers_warning => l_other_manager_warning
,p_other_manager_warning => l_other_manager_warning2
,p_org_now_no_manager_warning => l_other_manager_warning2
,p_hourly_salaried_warning => l_other_manager_warning2);
END update_employment_terms;
PROCEDURE update_administrative_career (
p_validate IN BOOLEAN DEFAULT FALSE,
p_datetrack_update_mode IN VARCHAR2,
p_effective_date IN DATE,
p_assignment_id IN NUMBER,
p_corps_id IN NUMBER,
p_grade_id IN NUMBER,
p_step_id IN NUMBER,
p_progression_speed IN VARCHAR2,
p_personal_gross_index IN VARCHAR2,
p_employee_category IN VARCHAR2,
p_soft_coding_keyflex_id OUT NOCOPY NUMBER,
p_object_version_number IN OUT NOCOPY NUMBER,
p_effective_start_date OUT NOCOPY DATE,
p_effective_end_date OUT NOCOPY DATE,
p_assignment_sequence OUT NOCOPY NUMBER
)
IS
l_validate BOOLEAN;
l_spp_delete_warning BOOLEAN;
SELECT placement_id, object_version_number ovn, information3,
information4, step_id
FROM per_spinal_point_placements_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT assignment_type
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT normal_hours, normal_hours_frequency
FROM pqh_corps_definitions
WHERE ben_pgm_id = p_corps_id
AND p_effective_date BETWEEN date_from
AND NVL (date_to, hr_general.end_of_time);
SELECT grade_ladder_pgm_id, grade_id, normal_hours, frequency,
segment10 employee_category
FROM per_all_assignments_f, hr_soft_coding_keyflex
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
AND per_all_assignments_f.soft_coding_keyflex_id = hr_soft_coding_keyflex.soft_coding_keyflex_id(+);
SELECT nvl(scl.segment9,100) work_hour_share,
nvl(employment_category,'CF') employment_category,
normal_hours,
frequency
FROM per_all_assignments_f asg,
hr_soft_coding_keyflex scl
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND assignment_id = p_assignment_id
AND p_effective_date between effective_start_date and effective_end_date
AND primary_flag = 'Y';
hr_assignment_api.update_emp_asg_criteria
(p_validate => p_validate,
p_effective_date => p_effective_date,
p_datetrack_update_mode => p_datetrack_update_mode,
p_assignment_id => p_assignment_id,
p_grade_ladder_pgm_id => p_corps_id,
p_grade_id => p_grade_id
-- Out Variables
,
p_people_group_id => l_people_group_id,
p_object_version_number => p_object_version_number
-- In OUT
,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
);
hr_assignment_api.update_emp_asg
(p_validate => p_validate,
p_effective_date => p_effective_date,
p_datetrack_update_mode => l_datetrack_mode,
p_assignment_id => p_assignment_id,
p_object_version_number => p_object_version_number,
p_segment10 => p_employee_category,
p_segment2 => 'CIVIL',
p_normal_hours => l_normal_hours,
p_frequency => l_frequency
-- Following are Out Parameters
,
p_cagr_grade_def_id => l_cagr_grade_def_id,
p_cagr_concatenated_segments => l_cagr_concatenated_segments,
p_concatenated_segments => l_concatenated_segments,
p_soft_coding_keyflex_id => p_soft_coding_keyflex_id,
p_comment_id => l_comment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_no_managers_warning => l_other_manager_warning,
p_other_manager_warning => l_other_manager_warning2
);
hr_assignment_api.update_cwk_asg
(p_validate => p_validate,
p_effective_date => p_effective_date,
p_datetrack_update_mode => p_datetrack_update_mode,
p_assignment_id => p_assignment_id,
p_object_version_number => p_object_version_number,
p_scl_segment10 => p_employee_category,
p_scl_segment2 => 'CIVIL',
p_establishment_id => l_establishment_id
-- Following are Out Parameters
,
p_concatenated_segments => l_concatenated_segments,
p_soft_coding_keyflex_id => p_soft_coding_keyflex_id,
p_comment_id => l_comment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_no_managers_warning => l_other_manager_warning,
p_other_manager_warning => l_other_manager_warning2,
p_org_now_no_manager_warning => l_other_manager_warning2,
p_hourly_salaried_warning => l_other_manager_warning2
);
hr_assignment_api.update_cwk_asg_criteria
(p_validate => p_validate,
p_effective_date => p_effective_date,
p_datetrack_update_mode => p_datetrack_update_mode,
p_assignment_id => p_assignment_id
-- Out Variables
,
p_people_group_id => l_people_group_id,
p_object_version_number => p_object_version_number
-- In OUT
,
p_people_group_name => l_group_name,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
);
hr_sp_placement_api.update_spp(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_object_version_number => p_object_version_number
,p_placement_id => p_object_version_number
,p_information2 => p_personal_gross_index
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date);
hr_sp_placement_api.update_spp
(p_effective_date => p_effective_date,
p_placement_id => placement_record.placement_id,
p_object_version_number => placement_record.ovn,
p_datetrack_mode => l_datetrack_mode,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_validate => p_validate,
p_information3 => p_progression_speed,
p_information4 => p_personal_gross_index,
p_step_id => p_step_id
);
END update_administrative_career;
Select person_id, scl.segment26 admin_career_id, object_version_number
from per_all_assignments_f asg, hr_soft_coding_keyflex scl
where assignment_id = p_assignment_id
and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and p_effective_date between effective_start_date and effective_end_date;
Select people_group_id, establishment_id , scl.segment10 FrEmpCategory,
assignment_id,object_version_number,normal_hours,frequency,business_group_id,
organization_id, job_id, position_id
from per_all_assignments_f asg, hr_soft_coding_keyflex scl
where person_id = p_person_id
and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and p_effective_date between effective_start_date and effective_end_date
and primary_flag ='Y';
update_primary_asg_affectation
(
p_validate => p_validate,
p_assignment_id => l_admin_career_id,
p_effective_date => p_effective_date,
p_primary_affectation => p_primary_affectation,
p_organization_id => l_organization_id,
p_job_id => l_job_id,
p_position_id => NULL,
p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
p_object_version_number => l_p_asg_ovn,
p_person_id => l_person_id
) ;
Select person_id, scl.segment26 admin_career_id, object_version_number
from per_all_assignments_f asg, hr_soft_coding_keyflex scl
where assignment_id = p_assignment_id
and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and p_effective_date between effective_start_date and effective_end_date;
,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
,p_base_key_column => 'ASSIGNMENT_ID'
,p_base_key_value => p_assignment_id)
-- Following are Out Parameters
,p_object_version_number => l_obj_ver_no
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
,p_base_key_column => 'ASSIGNMENT_ID'
,p_base_key_value => p_assignment_id)
-- Following are Out Parameters
,p_object_version_number => l_obj_ver_no
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
Select person_id, scl.segment26 admin_career_id, object_version_number
from per_all_assignments_f asg, hr_soft_coding_keyflex scl
where assignment_id = p_assignment_id
and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and p_effective_date between effective_start_date and effective_end_date;
Select nvl(scl.segment25,0) Percenteffected
From per_all_assignments_f assign,
hr_soft_coding_keyflex scl
Where person_id = l_person_id
And assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
And assign.primary_flag = 'N'
And p_effective_date Between effective_start_date And effective_end_date
And assign.assignment_status_type_id = 2
And assign.assignment_id = p_assignment_id;
,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
,p_base_key_column => 'ASSIGNMENT_ID'
,p_base_key_value => p_assignment_id)
-- Following are Out Parameters
,p_object_version_number => l_obj_ver_no
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
,p_base_key_column => 'ASSIGNMENT_ID'
,p_base_key_value => p_assignment_id)
-- Following are Out Parameters
,p_object_version_number => l_obj_ver_no
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);