[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pbg.legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
procedure update_person_deployment
(p_validate in boolean default false
,p_person_deployment_id in number
,p_object_version_number in out nocopy number
,p_to_person_id in number default hr_api.g_number
,p_person_type_id in number default hr_api.g_number
,p_start_date in date default hr_api.g_date
,p_end_date in date default hr_api.g_date
,p_deployment_reason in varchar2 default hr_api.g_varchar2
,p_employee_number in varchar2 default hr_api.g_varchar2
,p_leaving_reason in varchar2 default hr_api.g_varchar2
,p_leaving_person_type_id in number default hr_api.g_number
,p_status in varchar2 default hr_api.g_varchar2
,p_status_change_reason in varchar2 default hr_api.g_varchar2
,p_deplymt_policy_id in number default hr_api.g_number
,p_organization_id in number default hr_api.g_number
,p_location_id in number default hr_api.g_number
,p_job_id in number default hr_api.g_number
,p_position_id in number default hr_api.g_number
,p_grade_id in number default hr_api.g_number
,p_supervisor_id in number default hr_api.g_number
,p_supervisor_assignment_id in number default hr_api.g_number
,p_retain_direct_reports in varchar2 default hr_api.g_varchar2
,p_payroll_id in number default hr_api.g_number
,p_pay_basis_id in number default hr_api.g_number
,p_proposed_salary in varchar2 default hr_api.g_varchar2
,p_people_group_id in number default hr_api.g_number
,p_soft_coding_keyflex_id in number default hr_api.g_number
,p_assignment_status_type_id in number default hr_api.g_number
,p_ass_status_change_reason in varchar2 default hr_api.g_varchar2
,p_assignment_category in varchar2 default hr_api.g_varchar2
,p_per_information1 in varchar2 default hr_api.g_varchar2
,p_per_information2 in varchar2 default hr_api.g_varchar2
,p_per_information3 in varchar2 default hr_api.g_varchar2
,p_per_information4 in varchar2 default hr_api.g_varchar2
,p_per_information5 in varchar2 default hr_api.g_varchar2
,p_per_information6 in varchar2 default hr_api.g_varchar2
,p_per_information7 in varchar2 default hr_api.g_varchar2
,p_per_information8 in varchar2 default hr_api.g_varchar2
,p_per_information9 in varchar2 default hr_api.g_varchar2
,p_per_information10 in varchar2 default hr_api.g_varchar2
,p_per_information11 in varchar2 default hr_api.g_varchar2
,p_per_information12 in varchar2 default hr_api.g_varchar2
,p_per_information13 in varchar2 default hr_api.g_varchar2
,p_per_information14 in varchar2 default hr_api.g_varchar2
,p_per_information15 in varchar2 default hr_api.g_varchar2
,p_per_information16 in varchar2 default hr_api.g_varchar2
,p_per_information17 in varchar2 default hr_api.g_varchar2
,p_per_information18 in varchar2 default hr_api.g_varchar2
,p_per_information19 in varchar2 default hr_api.g_varchar2
,p_per_information20 in varchar2 default hr_api.g_varchar2
,p_per_information21 in varchar2 default hr_api.g_varchar2
,p_per_information22 in varchar2 default hr_api.g_varchar2
,p_per_information23 in varchar2 default hr_api.g_varchar2
,p_per_information24 in varchar2 default hr_api.g_varchar2
,p_per_information25 in varchar2 default hr_api.g_varchar2
,p_per_information26 in varchar2 default hr_api.g_varchar2
,p_per_information27 in varchar2 default hr_api.g_varchar2
,p_per_information28 in varchar2 default hr_api.g_varchar2
,p_per_information29 in varchar2 default hr_api.g_varchar2
,p_per_information30 in varchar2 default hr_api.g_varchar2
,p_policy_duration_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_object_version_number number;
l_proc varchar2(72) := g_package||'update_person_deployment';
savepoint update_person_deployment;
HR_PERSON_DEPLOYMENT_BK2.UPDATE_PERSON_DEPLOYMENT_B
(p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
,p_to_person_id => p_to_person_id
,p_person_type_id => p_person_type_id
,p_start_date => l_start_date
,p_status => p_status
,p_status_change_reason => p_status_change_reason
,p_end_date => l_end_date
,p_deployment_reason => p_deployment_reason
,p_employee_number => p_employee_number
,p_leaving_reason => p_leaving_reason
,p_leaving_person_type_id => p_leaving_person_type_id
,p_deplymt_policy_id => p_deplymt_policy_id
,p_organization_id => p_organization_id
,p_location_id => p_location_id
,p_job_id => p_job_id
,p_position_id => p_position_id
,p_grade_id => p_grade_id
,p_supervisor_id => p_supervisor_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_retain_direct_reports => p_retain_direct_reports
,p_payroll_id => p_payroll_id
,p_pay_basis_id => p_pay_basis_id
,p_proposed_salary => p_proposed_salary
,p_people_group_id => p_people_group_id
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_assignment_status_type_id => p_assignment_status_type_id
,p_ass_status_change_reason => p_ass_status_change_reason
,p_assignment_category => p_assignment_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_module_name => 'UPDATE_PERSON_DEPLOYMENT'
,p_hook_type => 'BP'
);
HR_PERSON_DEPLOYMENT_BK2.UPDATE_PERSON_DEPLOYMENT_A
(p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
,p_to_person_id => p_to_person_id
,p_person_type_id => p_person_type_id
,p_start_date => l_start_date
,p_status => p_status
,p_status_change_reason => p_status_change_reason
,p_end_date => l_end_date
,p_deployment_reason => p_deployment_reason
,p_employee_number => p_employee_number
,p_leaving_reason => p_leaving_reason
,p_leaving_person_type_id => p_leaving_person_type_id
,p_deplymt_policy_id => p_deplymt_policy_id
,p_organization_id => p_organization_id
,p_location_id => p_location_id
,p_job_id => p_job_id
,p_position_id => p_position_id
,p_grade_id => p_grade_id
,p_supervisor_id => p_supervisor_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_retain_direct_reports => p_retain_direct_reports
,p_payroll_id => p_payroll_id
,p_pay_basis_id => p_pay_basis_id
,p_proposed_salary => p_proposed_salary
,p_people_group_id => p_people_group_id
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_assignment_status_type_id => p_assignment_status_type_id
,p_ass_status_change_reason => p_ass_status_change_reason
,p_assignment_category => p_assignment_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_policy_duration_warning => l_policy_duration_warning
);
(p_module_name => 'UPDATE_PERSON_DEPLOYMENT'
,p_hook_type => 'AP'
);
rollback to update_person_deployment;
rollback to update_person_deployment;
end update_person_deployment;
procedure delete_person_deployment
(p_validate in boolean default false
,p_person_deployment_id in number
,p_object_version_number in number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_person_deployment';
savepoint delete_person_deployment;
HR_PERSON_DEPLOYMENT_BK3.DELETE_PERSON_DEPLOYMENT_B
(p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'DELETE_PERSON_DEPLOYMENT'
,p_hook_type => 'BP'
);
HR_PERSON_DEPLOYMENT_BK3.DELETE_PERSON_DEPLOYMENT_A
(p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'DELETE_PERSON_DEPLOYMENT'
,p_hook_type => 'AP'
);
rollback to delete_person_deployment;
rollback to delete_person_deployment;
end delete_person_deployment;
l_spp_delete_warning boolean;
l_last_update_date per_all_assignments_f.last_update_date%TYPE;
l_last_updated_by per_all_assignments_f.last_updated_by%TYPE;
l_last_update_login per_all_assignments_f.last_update_login%TYPE;
l_payroll_id_updated BOOLEAN;
l_datetrack_update_mode varchar2(30);
select null
from fnd_attached_documents
where PK1_VALUE =p_person_deployment_id
and ENTITY_NAME ='HR_PERSON_DEPLOYMENTS';
select *
from hr_person_deployments dpl
where dpl.person_deployment_id = p_person_deployment_id;
select 1
from hr_person_deployments pdt1
where pdt1.person_deployment_id = p_person_deployment_id
and exists (select 1
from hr_person_deployments pdt2
where pdt2.person_deployment_id <> pdt1.person_deployment_id
and pdt2.from_person_id = pdt1.from_person_id
and pdt2.status in ('ACTIVE','COMPLETE')
and pdt1.start_date <= nvl(pdt2.end_date,hr_api.g_eot));
select papf.last_name, papf.sex, papf.party_id, papf.original_date_of_hire,papf.first_name -- 8605683 , 8688303
from per_all_people_f papf
where papf.person_id = p_person_id
and l_dpl_rec.start_date between
papf.effective_start_date and papf.effective_end_date;
select papf.object_version_number
from per_all_people_f papf
where papf.person_id = p_person_id
and l_dpl_rec.start_date between
papf.effective_start_date and papf.effective_end_date;
select paaf.object_version_number
from per_all_assignments_f paaf
where paaf.assignment_id = p_assignment_id
and l_dpl_rec.start_date between
paaf.effective_start_date and paaf.effective_end_date;
select paaf.assignment_id, paaf.object_version_number
from per_all_assignments_f paaf,
per_assignment_status_types past
where paaf.person_id = p_person_id
and l_dpl_rec.start_date between
paaf.effective_start_date and paaf.effective_end_date
and paaf.assignment_type = 'E'
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN';
select pds.period_of_service_id, pds.object_version_number
from per_periods_of_service pds
where pds.person_id = p_person_id
and l_dpl_rec.start_date >= pds.date_start
and pds.actual_termination_date is null;
select *
from hr_person_deplymt_contacts pdc
where pdc.person_deployment_id = p_person_deployment_id;
select *
from per_contact_relationships ctr
where ctr.contact_relationship_id = p_contact_relationship_id;
select *
from per_all_people_f papf
where papf.person_id = p_person_id
and p_effective_date between
papf.effective_start_date and papf.effective_end_date;
select *
from hr_person_deplymt_eits pde
where pde.person_deployment_id = p_person_deployment_id;
select *
from per_people_extra_info pei
where pei.person_extra_info_id = p_person_extra_info_id;
select asg.assignment_id, asg.effective_start_date
from per_all_assignments_f asg
where asg.supervisor_id = p_person_id
and asg.effective_end_date > p_effective_date;*/
select *
from per_all_assignments_f asg
where asg.supervisor_id = p_person_id
and p_start_date between asg.effective_start_date and asg.effective_end_date;
select asg.assignment_id, asg.effective_start_date,asg.effective_end_date
from per_all_assignments_f asg
where asg.supervisor_id = p_person_id
and asg.effective_start_date > p_start_date;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
from pay_people_groups
where people_group_id = cur_p_people_group_id;
l_varray_d.delete;
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => l_home_asg_rec.assignment_id
,p_object_version_number => l_home_asg_rec.object_version_number
,p_effective_start_date => l_home_asg_esd
,p_effective_end_date => l_home_asg_eed
);
update per_all_assignments_f
set supervisor_id = l_host_person_id,
supervisor_assignment_id =
decode(supervisor_assignment_id,null,null,l_host_assignment_id),
object_version_number = object_version_number+1
where assignment_id = l_reports.assignment_id
and effective_start_date = l_reports.effective_start_date;
l_datetrack_update_mode:='CORRECTION';
Select count(effective_start_date)
into l_later_count
from per_all_assignments_f
where assignment_id = l_reports.assignment_id
and effective_start_date > (select effective_end_date
from per_all_assignments_f
where assignment_id = l_reports.assignment_id
and l_dpl_rec.start_date between effective_start_date and effective_end_date);
l_datetrack_update_mode:='UPDATE_CHANGE_INSERT';
l_datetrack_update_mode:='UPDATE';
l_datetrack_update_mode:='UPDATE';
if l_datetrack_update_mode = 'CORRECTION' then
update per_all_assignments_f
set supervisor_id = l_host_person_id,
supervisor_assignment_id =
decode(supervisor_assignment_id,null,null,l_host_assignment_id),
object_version_number = object_version_number+1
where assignment_id = l_reports.assignment_id
and effective_start_date = l_reports.effective_start_date;
elsif l_datetrack_update_mode ='UPDATE_CHANGE_INSERT' then
per_asg_upd.upd
(p_assignment_id => l_reports.assignment_id
,p_effective_start_date => l_effective_start_date --l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_business_group_id => l_business_group_id
,p_assignment_status_type_id => l_reports.assignment_status_type_id
,p_assignment_type => l_reports.assignment_type --modified
,p_supervisor_id => l_host_person_id --modified
,p_supervisor_assignment_id => l_host_assignment_id --modified
,p_primary_flag => l_reports.primary_flag
,p_period_of_service_id => l_reports.period_of_service_id
,p_comment_id => l_comment_id
,p_object_version_number => l_reports.object_version_number
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_effective_date => l_dpl_rec.start_date
,p_datetrack_mode => 'UPDATE_CHANGE_INSERT'
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
elsif l_datetrack_update_mode='UPDATE' then
per_asg_upd.upd
(p_assignment_id => l_reports.assignment_id
,p_effective_start_date => l_effective_start_date --l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_business_group_id => l_business_group_id
,p_assignment_status_type_id => l_reports.assignment_status_type_id
,p_assignment_type => l_reports.assignment_type --modified
,p_supervisor_id => l_host_person_id --modified
,p_supervisor_assignment_id => l_host_assignment_id --modified
,p_primary_flag => l_reports.primary_flag
,p_period_of_service_id => l_reports.period_of_service_id
,p_comment_id => l_comment_id
,p_object_version_number => l_reports.object_version_number
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_effective_date => l_dpl_rec.start_date
,p_datetrack_mode => 'UPDATE'
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
update per_all_assignments_f
set supervisor_id = l_host_person_id,
supervisor_assignment_id =
decode(supervisor_assignment_id,null,null,l_host_assignment_id),
object_version_number = object_version_number+1
where assignment_id = l_fut_dt_rows.assignment_id
and effective_start_date = l_fut_dt_rows.effective_start_date;
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => l_dpl_rec.start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_host_assignment_id
,p_object_version_number => l_host_asg_ovn
,p_supervisor_id => l_dpl_rec.supervisor_id
,p_assignment_number => l_host_asg_number
,p_change_reason => l_dpl_rec.ass_status_change_reason
-- ,p_assignment_status_type_id => l_dpl_rec.assignment_status_type_id
,p_assignment_status_type_id =>
nvl(l_dpl_rec.assignment_status_type_id,hr_api.g_number)
,p_comments => null
,p_date_probation_end => hr_api.g_date
,p_default_code_comb_id => hr_api.g_number
,p_frequency => hr_api.g_varchar2
,p_internal_address_line => hr_api.g_varchar2
,p_manager_flag => hr_api.g_varchar2
,p_normal_hours => hr_api.g_number
,p_perf_review_period => hr_api.g_number
,p_perf_review_period_frequency => hr_api.g_varchar2
,p_projected_assignment_end => l_dpl_rec.end_date
,p_probation_period => hr_api.g_number
,p_probation_unit => hr_api.g_varchar2
,p_sal_review_period => hr_api.g_number
,p_sal_review_period_frequency => hr_api.g_varchar2
,p_set_of_books_id => hr_api.g_number
,p_source_type => hr_api.g_varchar2
,p_time_normal_finish => hr_api.g_varchar2
,p_time_normal_start => hr_api.g_varchar2
,p_bargaining_unit_code => hr_api.g_varchar2
,p_labour_union_member_flag => hr_api.g_varchar2
,p_hourly_salaried_code => hr_api.g_varchar2
,p_ass_attribute_category => hr_api.g_varchar2
,p_ass_attribute1 => hr_api.g_varchar2
,p_ass_attribute2 => hr_api.g_varchar2
,p_ass_attribute3 => hr_api.g_varchar2
,p_ass_attribute4 => hr_api.g_varchar2
,p_ass_attribute5 => hr_api.g_varchar2
,p_ass_attribute6 => hr_api.g_varchar2
,p_ass_attribute7 => hr_api.g_varchar2
,p_ass_attribute8 => hr_api.g_varchar2
,p_ass_attribute9 => hr_api.g_varchar2
,p_ass_attribute10 => hr_api.g_varchar2
,p_ass_attribute11 => hr_api.g_varchar2
,p_ass_attribute12 => hr_api.g_varchar2
,p_ass_attribute13 => hr_api.g_varchar2
,p_ass_attribute14 => hr_api.g_varchar2
,p_ass_attribute15 => hr_api.g_varchar2
,p_ass_attribute16 => hr_api.g_varchar2
,p_ass_attribute17 => hr_api.g_varchar2
,p_ass_attribute18 => hr_api.g_varchar2
,p_ass_attribute19 => hr_api.g_varchar2
,p_ass_attribute20 => hr_api.g_varchar2
,p_ass_attribute21 => hr_api.g_varchar2
,p_ass_attribute22 => hr_api.g_varchar2
,p_ass_attribute23 => hr_api.g_varchar2
,p_ass_attribute24 => hr_api.g_varchar2
,p_ass_attribute25 => hr_api.g_varchar2
,p_ass_attribute26 => hr_api.g_varchar2
,p_ass_attribute27 => hr_api.g_varchar2
,p_ass_attribute28 => hr_api.g_varchar2
,p_ass_attribute29 => hr_api.g_varchar2
,p_ass_attribute30 => hr_api.g_varchar2
,p_title => hr_api.g_varchar2
,p_segment1 => hr_api.g_varchar2
,p_segment2 => hr_api.g_varchar2
,p_segment3 => hr_api.g_varchar2
,p_segment4 => hr_api.g_varchar2
,p_segment5 => hr_api.g_varchar2
,p_segment6 => hr_api.g_varchar2
,p_segment7 => hr_api.g_varchar2
,p_segment8 => hr_api.g_varchar2
,p_segment9 => hr_api.g_varchar2
,p_segment10 => hr_api.g_varchar2
,p_segment11 => hr_api.g_varchar2
,p_segment12 => hr_api.g_varchar2
,p_segment13 => hr_api.g_varchar2
,p_segment14 => hr_api.g_varchar2
,p_segment15 => hr_api.g_varchar2
,p_segment16 => hr_api.g_varchar2
,p_segment17 => hr_api.g_varchar2
,p_segment18 => hr_api.g_varchar2
,p_segment19 => hr_api.g_varchar2
,p_segment20 => hr_api.g_varchar2
,p_segment21 => hr_api.g_varchar2
,p_segment22 => hr_api.g_varchar2
,p_segment23 => hr_api.g_varchar2
,p_segment24 => hr_api.g_varchar2
,p_segment25 => hr_api.g_varchar2
,p_segment26 => hr_api.g_varchar2
,p_segment27 => hr_api.g_varchar2
,p_segment28 => hr_api.g_varchar2
,p_segment29 => hr_api.g_varchar2
,p_segment30 => hr_api.g_varchar2
,p_concat_segments => hr_api.g_varchar2
,p_contract_id => hr_api.g_number
,p_establishment_id => hr_api.g_number
,p_collective_agreement_id => hr_api.g_number
,p_cagr_id_flex_num => hr_api.g_number
,p_cag_segment1 => hr_api.g_varchar2
,p_cag_segment2 => hr_api.g_varchar2
,p_cag_segment3 => hr_api.g_varchar2
,p_cag_segment4 => hr_api.g_varchar2
,p_cag_segment5 => hr_api.g_varchar2
,p_cag_segment6 => hr_api.g_varchar2
,p_cag_segment7 => hr_api.g_varchar2
,p_cag_segment8 => hr_api.g_varchar2
,p_cag_segment9 => hr_api.g_varchar2
,p_cag_segment10 => hr_api.g_varchar2
,p_cag_segment11 => hr_api.g_varchar2
,p_cag_segment12 => hr_api.g_varchar2
,p_cag_segment13 => hr_api.g_varchar2
,p_cag_segment14 => hr_api.g_varchar2
,p_cag_segment15 => hr_api.g_varchar2
,p_cag_segment16 => hr_api.g_varchar2
,p_cag_segment17 => hr_api.g_varchar2
,p_cag_segment18 => hr_api.g_varchar2
,p_cag_segment19 => hr_api.g_varchar2
,p_cag_segment20 => hr_api.g_varchar2
,p_notice_period => hr_api.g_number
,p_notice_period_uom => hr_api.g_varchar2
,p_employee_category => hr_api.g_varchar2
,p_work_at_home => hr_api.g_varchar2
,p_job_post_source_name => hr_api.g_varchar2
,p_supervisor_assignment_id => l_dpl_rec.supervisor_assignment_id
,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 => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_host_asg_esd
,p_effective_end_date => l_host_asg_eed
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_gsp_post_process_warning => l_gsp_post_process_warning
);
hr_assignment_api.update_emp_asg_criteria
(p_validate => p_validate
,p_effective_date => l_dpl_rec.start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_host_assignment_id
,p_called_from_mass_update => false
,p_grade_id => l_dpl_rec.grade_id
,p_position_id => l_dpl_rec.position_id
,p_job_id => l_dpl_rec.job_id
,p_payroll_id => l_dpl_rec.payroll_id
,p_location_id => l_dpl_rec.location_id
,p_organization_id => l_dpl_rec.organization_id
,p_pay_basis_id => l_dpl_rec.pay_basis_id
,p_segment1 => l_pgp_segment1 --hr_api.g_varchar2
,p_segment2 => l_pgp_segment2 --hr_api.g_varchar2
,p_segment3 => l_pgp_segment3 --hr_api.g_varchar2
,p_segment4 => l_pgp_segment4 --hr_api.g_varchar2
,p_segment5 => l_pgp_segment5 --hr_api.g_varchar2
,p_segment6 => l_pgp_segment6 --hr_api.g_varchar2
,p_segment7 => l_pgp_segment7 --hr_api.g_varchar2
,p_segment8 => l_pgp_segment8 --hr_api.g_varchar2
,p_segment9 => l_pgp_segment9 --hr_api.g_varchar2
,p_segment10 => l_pgp_segment10 --hr_api.g_varchar2
,p_segment11 => l_pgp_segment11 --hr_api.g_varchar2
,p_segment12 => l_pgp_segment12 --hr_api.g_varchar2
,p_segment13 => l_pgp_segment13 --hr_api.g_varchar2
,p_segment14 => l_pgp_segment14 --hr_api.g_varchar2
,p_segment15 => l_pgp_segment15 --hr_api.g_varchar2
,p_segment16 => l_pgp_segment16 --hr_api.g_varchar2
,p_segment17 => l_pgp_segment17 --hr_api.g_varchar2
,p_segment18 => l_pgp_segment18 --hr_api.g_varchar2
,p_segment19 => l_pgp_segment19 --hr_api.g_varchar2
,p_segment20 => l_pgp_segment20 --hr_api.g_varchar2
,p_segment21 => l_pgp_segment21 --hr_api.g_varchar2
,p_segment22 => l_pgp_segment22 --hr_api.g_varchar2
,p_segment23 => l_pgp_segment23 --hr_api.g_varchar2
,p_segment24 => l_pgp_segment24 --hr_api.g_varchar2
,p_segment25 => l_pgp_segment25 --hr_api.g_varchar2
,p_segment26 => l_pgp_segment26 --hr_api.g_varchar2
,p_segment27 => l_pgp_segment27 --hr_api.g_varchar2
,p_segment28 => l_pgp_segment28 --hr_api.g_varchar2
,p_segment29 => l_pgp_segment29 --hr_api.g_varchar2
,p_segment30 => l_pgp_segment30 --hr_api.g_varchar2
,p_employment_category => l_dpl_rec.assignment_category
,p_concat_segments => hr_api.g_varchar2
,p_contract_id => hr_api.g_number
,p_establishment_id => hr_api.g_number
,p_scl_segment1 => hr_api.g_varchar2
,p_grade_ladder_pgm_id => hr_api.g_number
,p_supervisor_assignment_id => l_dpl_rec.supervisor_assignment_id
,p_object_version_number => l_host_asg_ovn
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_people_group_id => l_group_id --l_dpl_rec.people_group_id -- sturlapa
,p_soft_coding_keyflex_id => l_dpl_rec.soft_coding_keyflex_id
,p_group_name => l_group_name
,p_effective_start_date => l_host_asg_esd
,p_effective_end_date => l_host_asg_eed
,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
,p_concatenated_segments => l_concatenated_segments
,p_gsp_post_process_warning => l_gsp_post_process_warning
);
hr_maintain_proposal_api.insert_salary_proposal
(p_validate => p_validate
,p_pay_proposal_id => l_host_pyp_id
,p_assignment_id => l_host_assignment_id
,p_business_group_id => l_dpl_rec.to_business_group_id
,p_change_date => l_dpl_rec.start_date
,p_comments => null
,p_next_sal_review_date => null
,p_proposal_reason => null
,p_proposed_salary_n => l_dpl_rec.proposed_salary
,p_forced_ranking => null
,p_performance_review_id => null
,p_attribute_category => null
,p_attribute1 => null
,p_attribute2 => null
,p_attribute3 => null
,p_attribute4 => null
,p_attribute5 => null
,p_attribute6 => null
,p_attribute7 => null
,p_attribute8 => null
,p_attribute9 => null
,p_attribute10 => null
,p_attribute11 => null
,p_attribute12 => null
,p_attribute13 => null
,p_attribute14 => null
,p_attribute15 => null
,p_attribute16 => null
,p_attribute17 => null
,p_attribute18 => null
,p_attribute19 => null
,p_attribute20 => null
,p_object_version_number => l_host_pyp_ovn
,p_multiple_components => 'N'
,p_approved => 'Y'
,p_element_entry_id => l_pyp_element_entry_id
,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
,p_proposed_salary_warning => l_proposed_salary_warning
,p_approved_warning => l_approved_warning
,p_payroll_warning => l_payroll_warning
);
hr_person_deployment_api.update_person_deployment
(p_validate => p_validate
,p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
,p_to_person_id => l_host_person_id
,p_status => 'COMPLETE'
,p_policy_duration_warning => l_policy_duration_warning
);
hr_person_deployment_api.update_person_deployment
(p_validate => p_validate
,p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
,p_to_person_id => l_host_person_id
,p_status => 'ACTIVE'
,p_policy_duration_warning => l_policy_duration_warning
);
update fnd_attached_documents
set ENTITY_NAME='PER_PEOPLE_F' ,PK1_VALUE =l_host_person_id
WHERE PK1_VALUE=p_person_deployment_id
and ENTITY_NAME ='HR_PERSON_DEPLOYMENTS' ;
select *
from hr_person_deployments dpl
where dpl.person_deployment_id = p_person_deployment_id;
select paaf.assignment_id, paaf.effective_start_date
from per_all_assignments_f paaf,
per_assignment_status_types past
where paaf.person_id = L_DPL_REC.FROM_PERSON_ID
and paaf.effective_start_date = L_DPL_REC.START_DATE
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'SUSP_ASSIGN';
cursor csr_overlap_asg_update
(p_assignment_id number, p_old_date date, p_new_date date) is
select 1
from per_all_assignments_f paaf
where paaf.assignment_id = p_assignment_id
and paaf.effective_end_date = p_old_date-1
and paaf.effective_start_date >= p_new_date;
select asg.assignment_id,asg.object_version_number,asg.effective_start_date
from per_all_assignments_f asg,
hr_person_deployments pdt,
per_periods_of_service pds
where asg.person_id = pdt.to_person_id
and pdt.person_deployment_id = p_person_deployment_id
and asg.period_of_service_id = pds.period_of_service_id
and pds.date_start = p_start_date
and trunc(sysdate) between asg.effective_start_date and
asg.effective_end_date;
hr_change_start_date_api.update_start_date
(p_validate => p_validate
,p_person_id => l_dpl_rec.to_person_id
,p_old_start_date => l_dpl_rec.start_date
,p_new_start_date => l_start_date_in
,p_update_type => 'E'
,p_applicant_number => null
,p_warn_ee => l_warn_ee
);
open csr_overlap_asg_update
(l_asg.assignment_id,l_dpl_rec.start_date,l_start_date_in);
fetch csr_overlap_asg_update into l_dummy;
if csr_overlap_asg_update%notfound then
--
update per_all_assignments_f paaf
set paaf.effective_start_date = l_start_date_in
where paaf.assignment_id = l_asg.assignment_id
and paaf.effective_start_date = l_dpl_rec.start_date;
update per_all_assignments_f paaf
set paaf.effective_end_date = l_start_date_in-1
where paaf.assignment_id = l_asg.assignment_id
and paaf.effective_end_date = l_dpl_rec.start_date-1;
close csr_overlap_asg_update;
close csr_overlap_asg_update;
l_varray_d.delete;
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => l_host.assignment_id
,p_object_version_number => l_host.object_version_number
,p_projected_assignment_end => l_end_date_in
,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 => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_host_asg_esd
,p_effective_end_date => l_host_asg_eed
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_gsp_post_process_warning => l_gsp_post_process_warning
);
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_host.assignment_id
,p_object_version_number => l_host.object_version_number
,p_projected_assignment_end => l_end_date_in
,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 => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_host_asg_esd
,p_effective_end_date => l_host_asg_eed
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_gsp_post_process_warning => l_gsp_post_process_warning
);
hr_person_deployment_api.update_person_deployment
(p_validate => p_validate
,p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date_in
,p_end_date => l_end_date_in
,p_policy_duration_warning => l_policy_duration_warning
);
select *
from hr_person_deployments dpl
where dpl.person_deployment_id = p_person_deployment_id;
select pds.period_of_service_id, pds.object_version_number
from per_periods_of_service pds
where pds.person_id = p_person_id
and pds.date_start = L_DPL_REC.START_DATE;
select paaf.assignment_id, paaf.object_version_number
from per_all_assignments_f paaf,
per_assignment_status_types past
where paaf.person_id = p_person_id
and L_END_DATE_IN between
paaf.effective_start_date and paaf.effective_end_date
and paaf.assignment_type = 'E'
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'SUSP_ASSIGN'
and exists
(select 1
from per_all_assignments_f paaf1,
per_assignment_status_types past1
where paaf1.assignment_id = paaf.assignment_id
and paaf1.effective_start_date = L_DPL_REC.START_DATE
and paaf1.assignment_status_type_id = past1.assignment_status_type_id
and past1.per_system_status = 'SUSP_ASSIGN')
and exists
(select 1
from per_all_assignments_f paaf2,
per_assignment_status_types past2
where paaf2.assignment_id = paaf.assignment_id
and paaf2.effective_end_date = L_DPL_REC.START_DATE-1
and paaf2.assignment_status_type_id = past2.assignment_status_type_id
and past2.per_system_status = 'ACTIVE_ASSIGN');
hr_person_deployment_api.update_person_deployment
(p_validate => p_validate
,p_person_deployment_id => p_person_deployment_id
,p_object_version_number => p_object_version_number
,p_end_date => l_end_date_in
,p_status => 'COMPLETE'
,p_leaving_reason => l_leaving_reason
,p_leaving_person_type_id => l_leaving_person_type_id
,p_policy_duration_warning => l_policy_duration_warning
);
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => l_asg_rec.assignment_id
,p_change_reason => null
,p_object_version_number => l_asg_rec.object_version_number
,p_assignment_status_type_id => null --null causes it to be set to default
,p_effective_start_date => l_home_asg_esd
,p_effective_end_date => l_home_asg_eed
);