[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
AND asg.effective_start_date > csr_future_asgs.p_effective_date
AND (p_assignment_id is null OR
(p_assignment_id is not null AND
p_assignment_id = asg.assignment_id));
SELECT asg.assignment_id
,asg.object_version_number
FROM per_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.person_id = csr_nonaccepted_asgs.p_person_id
AND csr_nonaccepted_asgs.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.assignment_type = 'A'
AND ast.per_system_status <> 'ACCEPTED';
SELECT asg.assignment_id
,asg.object_version_number
,asg.vacancy_id
FROM per_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.person_id = csr_accepted_asgs.p_person_id
AND ( asg.assignment_id = csr_accepted_asgs.p_assignment_id
OR csr_accepted_asgs.p_assignment_id = hr_api.g_number)
AND csr_accepted_asgs.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.assignment_type = 'A'
AND ast.per_system_status = 'ACCEPTED';
SELECT asg.assignment_id
,asg.object_version_number
FROM per_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.person_id = csr_primary_asgs.p_person_id
AND csr_primary_asgs.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.assignment_type = 'E'
AND asg.primary_flag = 'Y'
AND ast.per_system_status = 'ACTIVE_ASSIGN';
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;
SELECT apl.application_id
,apl.object_version_number
FROM per_applications apl
WHERE apl.person_id = csr_apl_details.p_person_id
AND csr_apl_details.p_effective_date BETWEEN apl.date_received
AND NVL(apl.date_end,hr_api.g_eot);
SELECT pds.period_of_service_id
,pds.object_version_number
FROM per_periods_of_service pds
WHERE pds.person_id = csr_pds_details.p_person_id
AND csr_pds_details.p_effective_date BETWEEN pds.date_start
AND NVL(pds.actual_termination_date,hr_api.g_eot);
l_payroll_id_updated BOOLEAN;
select 1
from per_all_vacancies vac
where vac.vacancy_id=p_vacancy_id
and vac.number_of_openings <
(select count(distinct assignment_id)
from per_all_assignments_f asg
where asg.vacancy_id=p_vacancy_id
and asg.assignment_type='E');
select 1 from dual where exists
(select 1
from per_all_people_f
where person_id = p_person_id
and effective_start_date >= p_effective_date);
select application_id
from per_all_assignments_f
where assignment_id = lp_assignment_id
and p_hire_date between effective_start_date and effective_end_date;
PROCEDURE update_salary_proposal(p_assignment_id number
, p_effective_date date) IS
l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
select pay_proposal_id, object_version_number, change_date
,PROPOSED_SALARY_N
from per_pay_proposals
where assignment_id = p_assignment_id
order by change_date DESC;
END update_salary_proposal;
,p_datetrack_mode => hr_api.g_update
,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
);
select ptuf.person_type_id into l_person_type_id1
from per_person_type_usages_f ptuf, per_person_types ppt
where ptuf.person_id = p_person_id
and l_hire_date between ptuf.effective_start_date and ptuf.effective_end_date
and ppt.person_type_id = ptuf.person_type_id
and ppt.system_person_type = 'EMP';
IF (l_person_type_id1 is null) -- inserting
OR (l_person_type_id1 is not null -- updating with a change in PTU
AND nvl(l_person_type_id,l_person_type_id1) <> l_person_type_id1)
THEN
per_per_bus.chk_person_type
(p_person_type_id => l_person_type_id
,p_business_group_id => l_per_details_rec.business_group_id
,p_expected_sys_type => 'EMP'
);
,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_hire_date
,p_datetrack_mode => hr_api.g_update
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
update_salary_proposal(l_accepted_asgs_rec.assignment_id, l_hire_date);
l_payroll_id_updated boolean;
l_datetrack_update_mode varchar2(30);
select 'x'
from per_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date > p_hire_date;
select 'x'
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date >= p_hire_date
and asg.assignment_type = 'E';
select per.effective_start_date,
ppt.system_person_type,
per.business_group_id,
bus.legislation_code,
per.employee_number,
per.npw_number,
pap.application_id,
pap.object_version_number
from per_all_people_f per,
per_business_groups bus,
per_person_types ppt,
per_applications pap
where per.person_type_id = ppt.person_type_id
and per.business_group_id = bus.business_group_id
and per.person_id = pap.person_id
and per.person_id = p_person_id
and l_hire_date between per.effective_start_date
and per.effective_end_date
and l_hire_date between pap.date_received
and nvl(pap.date_end,hr_api.g_eot);
select count(asg.assignment_id)
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and pas.per_system_status = 'ACCEPTED'
and l_hire_date between asg.effective_start_date
and asg.effective_end_date;
select per.person_id,
pas.per_system_status
from per_all_people_f per,
per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and per.person_id = asg.person_id
and l_hire_date between per.effective_start_date
and per.effective_end_date
and asg.assignment_id = p_primary_assignment_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date;
select asg.assignment_id,
asg.object_version_number
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and asg.assignment_type='A'
and l_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status <> 'ACCEPTED'
order by asg.assignment_id;
select asg.assignment_id,
asg.object_version_number,
asg.effective_start_date,
asg.vacancy_id
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status = 'ACCEPTED'
order by asg.assignment_id;
select asg.assignment_id,
asg.object_version_number,
asg.effective_start_date,
asg.vacancy_id
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status = 'ACCEPTED'
order by decode(asg.assignment_id,p_primary_assignment_id,1,0) desc;
select assignment_id,object_version_number, period_of_service_id -- #2468916
from per_all_assignments_f
where person_id=p_person_id
and primary_flag='Y'
and l_hire_date between effective_start_date and effective_end_date
and assignment_type='E';
select *
from per_all_assignments_f asg
where asg.assignment_id=p_assignment_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date;
select *
from pay_people_groups
where people_group_id=p_people_group_id;
select *
from hr_soft_coding_keyflex
where soft_coding_keyflex_id=p_soft_coding_keyflex_id;
select *
from per_cagr_grades_def
where cagr_grade_def_id=p_cagr_grade_def_id;
select 1
from per_all_vacancies vac
where vac.vacancy_id=p_vacancy_id
and vac.number_of_openings <
(select count(distinct assignment_id)
from per_all_assignments_f asg
where asg.vacancy_id=p_vacancy_id
and asg.assignment_type='E');
select pay_proposal_id,object_version_number,proposed_salary_n, change_date, proposal_reason -- Added For Bug 5987409 --
from per_pay_proposals
where assignment_id=ass_id
and approved = 'N'
order by change_date desc;
select pay_proposal_id,object_version_number
from per_pay_proposals
where assignment_id=ass_id
and APPROVED='N';
select distinct PAAF.business_group_id
from per_all_assignments_f PAAF
where PAAF.assignment_id=p_asg_id;
select pay_proposal_id
from per_pay_proposals
where assignment_id=ass_id
and APPROVED='Y';
select PAAF.pay_basis_id
from per_all_assignments_f PAAF
where PAAF.assignment_id=p_asg_id;
select application_id
from per_all_assignments_f
where assignment_id = lp_assignment_id
and p_hire_date between effective_start_date and effective_end_date;
select 'Y' from per_all_assignments_f
where person_id = p_person_id
and assignment_id = p_assignment_id
and effective_start_date >= p_hire_date;
select date_received,date_end
from per_applications
where person_id= p_person_id and date_received > p_hire_date;
select 'Y' from per_applications
where person_id = p_person_id
and date_received = p_date_end +1;
select assignment_id, effective_start_date, effective_end_date
from per_all_assignments_f
where person_id = p_person_id
and assignment_type = 'A'
and effective_start_date > l_hire_date
and application_id = l_application_id
order by effective_start_date;
select application_id, date_end from per_applications
where person_id = p_person_id
and p_asg_start_date between date_received and nvl(date_end,hr_api.g_eot);
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
open fpt_future_asg_changes;
l_datetrack_update_mode:='CORRECTION';
l_datetrack_update_mode:='UPDATE';
,p_datetrack_mode => l_datetrack_update_mode
,p_person_id => p_person_id
,p_object_version_number => l_per_object_version_number
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
);
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
,p_datetrack_mode => l_datetrack_update_mode
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => p_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
,p_npw_number => l_npw_number
);
,p_datetrack_update_mode => l_datetrack_update_mode
);
,p_datetrack_update_mode => l_datetrack_update_mode
);
l_datetrack_update_mode:='CORRECTION';
l_datetrack_update_mode:='UPDATE';
p_datetrack_mode => l_datetrack_update_mode,
p_assignment_status_type_id => l_assignment_status_type_id,
p_assignment_type => 'E',
p_primary_flag => 'N',
p_period_of_service_id => l_period_of_service_id,
--
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => l_business_group_id,
p_comment_id => l_comment_id,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date,
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_hourly_salaried_warning => l_hourly_salaried_warning
);
p_datetrack_mode => l_datetrack_update_mode,
p_assignment_status_type_id => l_assignment_status_type_id,
p_assignment_type => 'E',
p_primary_flag => 'N',
p_period_of_service_id => l_period_of_service_id,
--
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => l_business_group_id,
p_comment_id => l_comment_id,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date,
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_hourly_salaried_warning => l_hourly_salaried_warning
);
/*hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_employment_category => l_asg_rec.employment_category
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_dummy
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
);*/
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_supervisor_assignment_id => l_asg_rec.supervisor_assignment_id -- 13595937
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_employment_category => l_asg_rec.employment_category
,p_people_group_id => l_dummy
,p_soft_coding_keyflex_id => l_asg_rec.soft_coding_keyflex_id
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
,p_concatenated_segments => l_concatenated_segments
,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
);
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_supervisor_id => l_asg_rec.supervisor_id
,p_assignment_number => l_asg_rec.assignment_number
,p_change_reason => l_asg_rec.change_reason
,p_date_probation_end => l_asg_rec.date_probation_end
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_frequency => l_asg_rec.frequency
,p_internal_address_line => l_asg_rec.internal_address_line
,p_manager_flag => l_asg_rec.manager_flag
,p_normal_hours => l_asg_rec.normal_hours
,p_perf_review_period => l_asg_rec.perf_review_period
,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
,p_probation_period => l_asg_rec.probation_period
,p_probation_unit => l_asg_rec.probation_unit
,p_sal_review_period => l_asg_rec.sal_review_period
,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_bargaining_unit_code => l_asg_rec.bargaining_unit_code
,p_labour_union_member_flag => l_asg_rec.labour_union_member_flag
,p_hourly_salaried_code => l_asg_rec.hourly_salaried_code
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => l_asg_rec.ass_attribute1
,p_ass_attribute2 => l_asg_rec.ass_attribute2
,p_ass_attribute3 => l_asg_rec.ass_attribute3
,p_ass_attribute4 => l_asg_rec.ass_attribute4
,p_ass_attribute5 => l_asg_rec.ass_attribute5
,p_ass_attribute6 => l_asg_rec.ass_attribute6
,p_ass_attribute7 => l_asg_rec.ass_attribute7
,p_ass_attribute8 => l_asg_rec.ass_attribute8
,p_ass_attribute9 => l_asg_rec.ass_attribute9
,p_ass_attribute10 => l_asg_rec.ass_attribute10
,p_ass_attribute11 => l_asg_rec.ass_attribute11
,p_ass_attribute12 => l_asg_rec.ass_attribute12
,p_ass_attribute13 => l_asg_rec.ass_attribute13
,p_ass_attribute14 => l_asg_rec.ass_attribute14
,p_ass_attribute15 => l_asg_rec.ass_attribute15
,p_ass_attribute16 => l_asg_rec.ass_attribute16
,p_ass_attribute17 => l_asg_rec.ass_attribute17
,p_ass_attribute18 => l_asg_rec.ass_attribute18
,p_ass_attribute19 => l_asg_rec.ass_attribute19
,p_ass_attribute20 => l_asg_rec.ass_attribute20
,p_ass_attribute21 => l_asg_rec.ass_attribute21
,p_ass_attribute22 => l_asg_rec.ass_attribute22
,p_ass_attribute23 => l_asg_rec.ass_attribute23
,p_ass_attribute24 => l_asg_rec.ass_attribute24
,p_ass_attribute25 => l_asg_rec.ass_attribute25
,p_ass_attribute26 => l_asg_rec.ass_attribute26
,p_ass_attribute27 => l_asg_rec.ass_attribute27
,p_ass_attribute28 => l_asg_rec.ass_attribute28
,p_ass_attribute29 => l_asg_rec.ass_attribute29
,p_ass_attribute30 => l_asg_rec.ass_attribute30
,p_segment1 => l_scl_rec.segment1
,p_segment2 => l_scl_rec.segment2
,p_segment3 => l_scl_rec.segment3
,p_segment4 => l_scl_rec.segment4
,p_segment5 => l_scl_rec.segment5
,p_segment6 => l_scl_rec.segment6
,p_segment7 => l_scl_rec.segment7
,p_segment8 => l_scl_rec.segment8
,p_segment9 => l_scl_rec.segment9
,p_segment10 => l_scl_rec.segment10
,p_segment11 => l_scl_rec.segment11
,p_segment12 => l_scl_rec.segment12
,p_segment13 => l_scl_rec.segment13
,p_segment14 => l_scl_rec.segment14
,p_segment15 => l_scl_rec.segment15
,p_segment16 => l_scl_rec.segment16
,p_segment17 => l_scl_rec.segment17
,p_segment18 => l_scl_rec.segment18
,p_segment19 => l_scl_rec.segment19
,p_segment20 => l_scl_rec.segment20
,p_segment21 => l_scl_rec.segment21
,p_segment22 => l_scl_rec.segment22
,p_segment23 => l_scl_rec.segment23
,p_segment24 => l_scl_rec.segment24
,p_segment25 => l_scl_rec.segment25
,p_segment26 => l_scl_rec.segment26
,p_segment27 => l_scl_rec.segment27
,p_segment28 => l_scl_rec.segment28
,p_segment29 => l_scl_rec.segment29
,p_segment30 => l_scl_rec.segment30
,p_contract_id => l_asg_rec.contract_id
,p_establishment_id => l_asg_rec.establishment_id
,p_collective_agreement_id => l_asg_rec.collective_agreement_id
,p_cagr_id_flex_num => l_asg_rec.cagr_id_flex_num
,p_cag_segment1 => l_cag_rec.segment1
,p_cag_segment2 => l_cag_rec.segment2
,p_cag_segment3 => l_cag_rec.segment3
,p_cag_segment4 => l_cag_rec.segment4
,p_cag_segment5 => l_cag_rec.segment5
,p_cag_segment6 => l_cag_rec.segment6
,p_cag_segment7 => l_cag_rec.segment7
,p_cag_segment8 => l_cag_rec.segment8
,p_cag_segment9 => l_cag_rec.segment9
,p_cag_segment10 => l_cag_rec.segment10
,p_cag_segment11 => l_cag_rec.segment11
,p_cag_segment12 => l_cag_rec.segment12
,p_cag_segment13 => l_cag_rec.segment13
,p_cag_segment14 => l_cag_rec.segment14
,p_cag_segment15 => l_cag_rec.segment15
,p_cag_segment16 => l_cag_rec.segment16
,p_cag_segment17 => l_cag_rec.segment17
,p_cag_segment18 => l_cag_rec.segment18
,p_cag_segment19 => l_cag_rec.segment19
,p_cag_segment20 => l_cag_rec.segment20
,p_notice_period => l_asg_rec.notice_period
,p_notice_period_uom => l_asg_rec.notice_period_uom
,p_employee_category => l_asg_rec.employee_category
,p_work_at_home => l_asg_rec.work_at_home
,p_job_post_source_name => l_asg_rec.job_post_source_name
,p_cagr_grade_def_id => l_dummynum1 -- Bug # 2788390 modified l_dummy to l_dummynum1.
,p_cagr_concatenated_segments => l_dummyv
,p_concatenated_segments => l_dummyv
,p_soft_coding_keyflex_id => l_dummy1
,p_comment_id => l_dummy2
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_hourly_salaried_warning => l_dummyb
);
UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
PAF.RECRUITER_ID =l_asg_rec.recruiter_id
WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
PAF.EFFECTIVE_END_DATE = l_effective_end_date;
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
/* hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_employment_category => l_asg_rec.employment_category
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_dummy
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
);*/
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_supervisor_assignment_id => nvl(l_asg_rec.supervisor_assignment_id
, l_primary_asg_rec.supervisor_assignment_id) -- 13595937
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_employment_category => l_asg_rec.employment_category
,p_people_group_id => l_dummy
,p_soft_coding_keyflex_id => l_asg_rec.soft_coding_keyflex_id
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
,p_concatenated_segments => l_concatenated_segments
,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
);
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_supervisor_id => l_asg_rec.supervisor_id
,p_assignment_number => l_asg_rec.assignment_number
,p_change_reason => NULL -- l_asg_rec.change_reason Bug 12946619
,p_date_probation_end => l_asg_rec.date_probation_end
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_frequency => l_asg_rec.frequency
,p_internal_address_line => l_asg_rec.internal_address_line
,p_manager_flag => l_asg_rec.manager_flag
,p_normal_hours => l_asg_rec.normal_hours
,p_perf_review_period => l_asg_rec.perf_review_period
,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
,p_probation_period => l_asg_rec.probation_period
,p_probation_unit => l_asg_rec.probation_unit
,p_sal_review_period => l_asg_rec.sal_review_period
,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_bargaining_unit_code => l_asg_rec.bargaining_unit_code
,p_labour_union_member_flag => l_asg_rec.labour_union_member_flag
,p_hourly_salaried_code => l_asg_rec.hourly_salaried_code
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => l_asg_rec.ass_attribute1
,p_ass_attribute2 => l_asg_rec.ass_attribute2
,p_ass_attribute3 => l_asg_rec.ass_attribute3
,p_ass_attribute4 => l_asg_rec.ass_attribute4
,p_ass_attribute5 => l_asg_rec.ass_attribute5
,p_ass_attribute6 => l_asg_rec.ass_attribute6
,p_ass_attribute7 => l_asg_rec.ass_attribute7
,p_ass_attribute8 => l_asg_rec.ass_attribute8
,p_ass_attribute9 => l_asg_rec.ass_attribute9
,p_ass_attribute10 => l_asg_rec.ass_attribute10
,p_ass_attribute11 => l_asg_rec.ass_attribute11
,p_ass_attribute12 => l_asg_rec.ass_attribute12
,p_ass_attribute13 => l_asg_rec.ass_attribute13
,p_ass_attribute14 => l_asg_rec.ass_attribute14
,p_ass_attribute15 => l_asg_rec.ass_attribute15
,p_ass_attribute16 => l_asg_rec.ass_attribute16
,p_ass_attribute17 => l_asg_rec.ass_attribute17
,p_ass_attribute18 => l_asg_rec.ass_attribute18
,p_ass_attribute19 => l_asg_rec.ass_attribute19
,p_ass_attribute20 => l_asg_rec.ass_attribute20
,p_ass_attribute21 => l_asg_rec.ass_attribute21
,p_ass_attribute22 => l_asg_rec.ass_attribute22
,p_ass_attribute23 => l_asg_rec.ass_attribute23
,p_ass_attribute24 => l_asg_rec.ass_attribute24
,p_ass_attribute25 => l_asg_rec.ass_attribute25
,p_ass_attribute26 => l_asg_rec.ass_attribute26
,p_ass_attribute27 => l_asg_rec.ass_attribute27
,p_ass_attribute28 => l_asg_rec.ass_attribute28
,p_ass_attribute29 => l_asg_rec.ass_attribute29
,p_ass_attribute30 => l_asg_rec.ass_attribute30
,p_segment1 => l_scl_rec.segment1
,p_segment2 => l_scl_rec.segment2
,p_segment3 => l_scl_rec.segment3
,p_segment4 => l_scl_rec.segment4
,p_segment5 => l_scl_rec.segment5
,p_segment6 => l_scl_rec.segment6
,p_segment7 => l_scl_rec.segment7
,p_segment8 => l_scl_rec.segment8
,p_segment9 => l_scl_rec.segment9
,p_segment10 => l_scl_rec.segment10
,p_segment11 => l_scl_rec.segment11
,p_segment12 => l_scl_rec.segment12
,p_segment13 => l_scl_rec.segment13
,p_segment14 => l_scl_rec.segment14
,p_segment15 => l_scl_rec.segment15
,p_segment16 => l_scl_rec.segment16
,p_segment17 => l_scl_rec.segment17
,p_segment18 => l_scl_rec.segment18
,p_segment19 => l_scl_rec.segment19
,p_segment20 => l_scl_rec.segment20
,p_segment21 => l_scl_rec.segment21
,p_segment22 => l_scl_rec.segment22
,p_segment23 => l_scl_rec.segment23
,p_segment24 => l_scl_rec.segment24
,p_segment25 => l_scl_rec.segment25
,p_segment26 => l_scl_rec.segment26
,p_segment27 => l_scl_rec.segment27
,p_segment28 => l_scl_rec.segment28
,p_segment29 => l_scl_rec.segment29
,p_segment30 => l_scl_rec.segment30
,p_contract_id => l_asg_rec.contract_id
,p_establishment_id => l_asg_rec.establishment_id
,p_collective_agreement_id => l_asg_rec.collective_agreement_id
,p_cagr_id_flex_num => l_asg_rec.cagr_id_flex_num
,p_cag_segment1 => l_cag_rec.segment1
,p_cag_segment2 => l_cag_rec.segment2
,p_cag_segment3 => l_cag_rec.segment3
,p_cag_segment4 => l_cag_rec.segment4
,p_cag_segment5 => l_cag_rec.segment5
,p_cag_segment6 => l_cag_rec.segment6
,p_cag_segment7 => l_cag_rec.segment7
,p_cag_segment8 => l_cag_rec.segment8
,p_cag_segment9 => l_cag_rec.segment9
,p_cag_segment10 => l_cag_rec.segment10
,p_cag_segment11 => l_cag_rec.segment11
,p_cag_segment12 => l_cag_rec.segment12
,p_cag_segment13 => l_cag_rec.segment13
,p_cag_segment14 => l_cag_rec.segment14
,p_cag_segment15 => l_cag_rec.segment15
,p_cag_segment16 => l_cag_rec.segment16
,p_cag_segment17 => l_cag_rec.segment17
,p_cag_segment18 => l_cag_rec.segment18
,p_cag_segment19 => l_cag_rec.segment19
,p_cag_segment20 => l_cag_rec.segment20
,p_notice_period => l_asg_rec.notice_period
,p_notice_period_uom => l_asg_rec.notice_period_uom
,p_employee_category => l_asg_rec.employee_category
,p_work_at_home => l_asg_rec.work_at_home
,p_job_post_source_name => l_asg_rec.job_post_source_name
,p_cagr_grade_def_id => l_dummynum1 -- Bug # 2788390 modified l_dummy to l_dummynum1.
,p_cagr_concatenated_segments => l_dummyv
,p_concatenated_segments => l_dummyv
,p_soft_coding_keyflex_id => l_dummy1
,p_comment_id => l_dummy2
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_hourly_salaried_warning => l_dummyb
);
UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
PAF.RECRUITER_ID =l_asg_rec.recruiter_id
WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
PAF.EFFECTIVE_END_DATE = l_effective_end_date;
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
p_dt_mode => 'UPDATE',
p_validation_start_date => l_effective_start_date,
p_validation_end_date => l_effective_end_date
);
-- retrieve the max end date of the ASG to update the APLN date_end
select max(effective_end_date) into l_apl_end_date
from per_all_assignments_f
where person_id = p_person_id
and assignment_id = apl.assignment_id
and assignment_type = 'A';
SELECT per_applications_s.nextval into l_new_application_id FROM sys.dual;
INSERT INTO PER_APPLICATIONS(
application_id,
business_group_id,
person_id,
date_received,
date_end)
VALUES (l_new_application_id,l_business_group_id,p_person_id,apl.effective_start_date,l_apl_end_date);
-- if APLN end date < ASG end date, update the APLN
if nvl(l_current_apl_end_date,hr_api.g_eot) < nvl(l_apl_end_date,hr_api.g_eot) then
update per_applications
set date_end = l_apl_end_date
where person_id = p_person_id
and application_id = l_cur_apl_id;
-- update the ASG with the correct APLN ID
update per_all_assignments_f
set application_id = l_cur_apl_id
where person_id = p_person_id
and assignment_id = apl.assignment_id
and effective_start_date = apl.effective_start_date
and effective_end_date = apl.effective_end_date;
select object_version_number into l_per_object_version_number
from per_all_people_f
where person_id = p_person_id
and apl.date_received between effective_start_date and effective_end_date;
,p_datetrack_mode => 'UPDATE'
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => p_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= p_person_id
and apl.date_end between effective_start_date and effective_end_date;
,p_datetrack_mode => 'UPDATE'
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => p_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
,p_npw_number => l_npw_number
);
l_payroll_id_updated boolean;
l_datetrack_update_mode varchar2(30);
select 'x'
from per_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date > p_hire_date;
select 'x'
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date >= p_hire_date
and asg.assignment_type = 'E';
select per.effective_start_date,
ppt.system_person_type,
per.business_group_id,
bus.legislation_code,
per.employee_number,
per.npw_number,
pap.application_id,
pap.object_version_number
from per_all_people_f per,
per_business_groups bus,
per_person_types ppt,
per_applications pap
where per.person_type_id = ppt.person_type_id
and per.business_group_id = bus.business_group_id
and per.person_id = pap.person_id
and per.person_id = p_person_id
and l_hire_date between per.effective_start_date
and per.effective_end_date
and l_hire_date between pap.date_received
and nvl(pap.date_end,hr_api.g_eot);
select count(asg.assignment_id)
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and pas.per_system_status = 'ACCEPTED'
and l_hire_date between asg.effective_start_date
and asg.effective_end_date;
select per.person_id,
pas.per_system_status
from per_all_people_f per,
per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and per.person_id = asg.person_id
and l_hire_date between per.effective_start_date
and per.effective_end_date
and asg.assignment_id = p_primary_assignment_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date;
select asg.assignment_id,
asg.object_version_number
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and asg.assignment_type='A'
and l_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status <> 'ACCEPTED'
order by asg.assignment_id;
select asg.assignment_id,
asg.object_version_number,
asg.effective_start_date,
asg.vacancy_id
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status = 'ACCEPTED'
order by asg.assignment_id;
this employee.applicant as per SSHR requirement. So added the extra selection criteria to
pick only the application assignment which we are hiring.
SSHR Enhancement(Bug # 8536819).
*/
cursor csr_get_accepted is
select asg.assignment_id,
asg.object_version_number,
asg.effective_start_date,
asg.vacancy_id,
asg.assignment_status_type_id -- 10376122
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status = 'ACCEPTED'
and asg.assignment_id = p_primary_assignment_id --- Added for SSHR Enhancement(Bug # 8536819).
order by decode(asg.assignment_id,p_primary_assignment_id,1,0) desc;
select assignment_id,object_version_number, period_of_service_id -- #2468916
from per_all_assignments_f
where person_id=p_person_id
and primary_flag='Y'
and l_hire_date between effective_start_date and effective_end_date
and assignment_type='E';
select *
from per_all_assignments_f asg
where asg.assignment_id=p_assignment_id
and l_hire_date between asg.effective_start_date
and asg.effective_end_date;
select *
from pay_people_groups
where people_group_id=p_people_group_id;
select *
from hr_soft_coding_keyflex
where soft_coding_keyflex_id=p_soft_coding_keyflex_id;
select *
from per_cagr_grades_def
where cagr_grade_def_id=p_cagr_grade_def_id;
select 1
from per_all_vacancies vac
where vac.vacancy_id=p_vacancy_id
and vac.number_of_openings <
(select count(distinct assignment_id)
from per_all_assignments_f asg
where asg.vacancy_id=p_vacancy_id
and asg.assignment_type='E');
select pay_proposal_id,object_version_number,proposed_salary_n, change_date, proposal_reason -- Added For Bug 5987409 --
from per_pay_proposals
where assignment_id=ass_id
and approved = 'N'
order by change_date desc;
select pay_proposal_id,object_version_number
from per_pay_proposals
where assignment_id=ass_id
and APPROVED='N';
select distinct PAAF.business_group_id
from per_all_assignments_f PAAF
where PAAF.assignment_id=p_asg_id;
select pay_proposal_id
from per_pay_proposals
where assignment_id=ass_id
and APPROVED='Y';
select PAAF.pay_basis_id
from per_all_assignments_f PAAF
where PAAF.assignment_id=p_asg_id;
select 'X' from dual where exists
( select *
from per_assignments_f asg,
per_assignment_status_types pas
where asg.assignment_status_type_id = pas.assignment_status_type_id
and asg.person_id = p_person_id
and asg.assignment_type='A'
and l_hire_date between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_id <> p_primary_assignment_id);
select soft_coding_keyflex_id,payroll_id
from per_all_assignments_f asg
where asg.assignment_id = crs_asg_id
-- and asg.primary_flag = 'Y'
and trunc(sysdate) between asg.effective_start_date
and asg.effective_end_date;
select soft_coding_keyflex_id
from hr_soft_coding_keyflex
where rownum=1;
select vacancy_id,RECRUITER_ID
from per_all_assignments_f
where assignment_id = p_asg_id
and l_hire_date between effective_start_date and effective_end_date;
select assignment_status_type_id
from per_all_assignments_f
where assignment_id = p_asg_id
and l_hire_date between effective_start_date and effective_end_date;
select 'Y' from per_all_assignments_f
where person_id = p_person_id
and assignment_id = p_assignment_id
and effective_start_date >= p_hire_date;
select date_received,date_end
from per_applications
where person_id= p_person_id and date_received > p_hire_date;
select 'Y' from per_applications
where person_id = p_person_id
and date_received = p_date_end +1;
select assignment_id, effective_start_date, effective_end_date
from per_all_assignments_f
where person_id = p_person_id
and assignment_type = 'A'
and effective_start_date > l_hire_date
and application_id = l_application_id
order by effective_start_date;
select application_id, date_end from per_applications
where person_id = p_person_id
and p_asg_start_date between date_received and nvl(date_end,hr_api.g_eot);
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
open fpt_future_asg_changes;
l_datetrack_update_mode:='CORRECTION';
l_datetrack_update_mode:='UPDATE';
,p_datetrack_mode => l_datetrack_update_mode
,p_person_id => p_person_id
,p_object_version_number => l_per_object_version_number
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
);
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
,p_datetrack_mode => l_datetrack_update_mode
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => p_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
,p_npw_number => l_npw_number
);
,p_datetrack_update_mode => l_datetrack_update_mode
);
,p_datetrack_update_mode => l_datetrack_update_mode
);
l_datetrack_update_mode:='CORRECTION';
l_datetrack_update_mode:='UPDATE';
p_datetrack_mode => l_datetrack_update_mode,
p_assignment_status_type_id => l_assignment_status_type_id,
p_assignment_type => 'E',
p_primary_flag => 'N',
p_period_of_service_id => l_period_of_service_id,
--
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => l_business_group_id,
p_comment_id => l_comment_id,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date,
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_hourly_salaried_warning => l_hourly_salaried_warning,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id --- Fix For Bug # 8844816
);
p_datetrack_mode => l_datetrack_update_mode,
p_assignment_status_type_id => l_assignment_status_type_id,
p_assignment_type => 'E',
p_primary_flag => 'N',
p_period_of_service_id => l_period_of_service_id,
--
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => l_business_group_id,
p_comment_id => l_comment_id,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date,
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_hourly_salaried_warning => l_hourly_salaried_warning,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id --- Fix For Bug # 8844816
);
/*hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_employment_category => l_asg_rec.employment_category
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_dummy
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
);*/
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_supervisor_assignment_id => l_asg_rec.supervisor_assignment_id -- 13595937
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_employment_category => l_asg_rec.employment_category
,p_people_group_id => l_dummy
,p_soft_coding_keyflex_id => l_asg_rec.soft_coding_keyflex_id
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
,p_concatenated_segments => l_concatenated_segments
,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
);
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_supervisor_id => l_asg_rec.supervisor_id
,p_assignment_number => l_asg_rec.assignment_number
,p_change_reason => l_asg_rec.change_reason
,p_date_probation_end => l_asg_rec.date_probation_end
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_frequency => l_asg_rec.frequency
,p_internal_address_line => l_asg_rec.internal_address_line
,p_manager_flag => l_asg_rec.manager_flag
,p_normal_hours => l_asg_rec.normal_hours
,p_perf_review_period => l_asg_rec.perf_review_period
,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
,p_probation_period => l_asg_rec.probation_period
,p_probation_unit => l_asg_rec.probation_unit
,p_sal_review_period => l_asg_rec.sal_review_period
,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_bargaining_unit_code => l_asg_rec.bargaining_unit_code
,p_labour_union_member_flag => l_asg_rec.labour_union_member_flag
,p_hourly_salaried_code => l_asg_rec.hourly_salaried_code
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => l_asg_rec.ass_attribute1
,p_ass_attribute2 => l_asg_rec.ass_attribute2
,p_ass_attribute3 => l_asg_rec.ass_attribute3
,p_ass_attribute4 => l_asg_rec.ass_attribute4
,p_ass_attribute5 => l_asg_rec.ass_attribute5
,p_ass_attribute6 => l_asg_rec.ass_attribute6
,p_ass_attribute7 => l_asg_rec.ass_attribute7
,p_ass_attribute8 => l_asg_rec.ass_attribute8
,p_ass_attribute9 => l_asg_rec.ass_attribute9
,p_ass_attribute10 => l_asg_rec.ass_attribute10
,p_ass_attribute11 => l_asg_rec.ass_attribute11
,p_ass_attribute12 => l_asg_rec.ass_attribute12
,p_ass_attribute13 => l_asg_rec.ass_attribute13
,p_ass_attribute14 => l_asg_rec.ass_attribute14
,p_ass_attribute15 => l_asg_rec.ass_attribute15
,p_ass_attribute16 => l_asg_rec.ass_attribute16
,p_ass_attribute17 => l_asg_rec.ass_attribute17
,p_ass_attribute18 => l_asg_rec.ass_attribute18
,p_ass_attribute19 => l_asg_rec.ass_attribute19
,p_ass_attribute20 => l_asg_rec.ass_attribute20
,p_ass_attribute21 => l_asg_rec.ass_attribute21
,p_ass_attribute22 => l_asg_rec.ass_attribute22
,p_ass_attribute23 => l_asg_rec.ass_attribute23
,p_ass_attribute24 => l_asg_rec.ass_attribute24
,p_ass_attribute25 => l_asg_rec.ass_attribute25
,p_ass_attribute26 => l_asg_rec.ass_attribute26
,p_ass_attribute27 => l_asg_rec.ass_attribute27
,p_ass_attribute28 => l_asg_rec.ass_attribute28
,p_ass_attribute29 => l_asg_rec.ass_attribute29
,p_ass_attribute30 => l_asg_rec.ass_attribute30
,p_segment1 => l_scl_rec.segment1
,p_segment2 => l_scl_rec.segment2
,p_segment3 => l_scl_rec.segment3
,p_segment4 => l_scl_rec.segment4
,p_segment5 => l_scl_rec.segment5
,p_segment6 => l_scl_rec.segment6
,p_segment7 => l_scl_rec.segment7
,p_segment8 => l_scl_rec.segment8
,p_segment9 => l_scl_rec.segment9
,p_segment10 => l_scl_rec.segment10
,p_segment11 => l_scl_rec.segment11
,p_segment12 => l_scl_rec.segment12
,p_segment13 => l_scl_rec.segment13
,p_segment14 => l_scl_rec.segment14
,p_segment15 => l_scl_rec.segment15
,p_segment16 => l_scl_rec.segment16
,p_segment17 => l_scl_rec.segment17
,p_segment18 => l_scl_rec.segment18
,p_segment19 => l_scl_rec.segment19
,p_segment20 => l_scl_rec.segment20
,p_segment21 => l_scl_rec.segment21
,p_segment22 => l_scl_rec.segment22
,p_segment23 => l_scl_rec.segment23
,p_segment24 => l_scl_rec.segment24
,p_segment25 => l_scl_rec.segment25
,p_segment26 => l_scl_rec.segment26
,p_segment27 => l_scl_rec.segment27
,p_segment28 => l_scl_rec.segment28
,p_segment29 => l_scl_rec.segment29
,p_segment30 => l_scl_rec.segment30
,p_contract_id => l_asg_rec.contract_id
,p_establishment_id => l_asg_rec.establishment_id
,p_collective_agreement_id => l_asg_rec.collective_agreement_id
,p_cagr_id_flex_num => l_asg_rec.cagr_id_flex_num
,p_cag_segment1 => l_cag_rec.segment1
,p_cag_segment2 => l_cag_rec.segment2
,p_cag_segment3 => l_cag_rec.segment3
,p_cag_segment4 => l_cag_rec.segment4
,p_cag_segment5 => l_cag_rec.segment5
,p_cag_segment6 => l_cag_rec.segment6
,p_cag_segment7 => l_cag_rec.segment7
,p_cag_segment8 => l_cag_rec.segment8
,p_cag_segment9 => l_cag_rec.segment9
,p_cag_segment10 => l_cag_rec.segment10
,p_cag_segment11 => l_cag_rec.segment11
,p_cag_segment12 => l_cag_rec.segment12
,p_cag_segment13 => l_cag_rec.segment13
,p_cag_segment14 => l_cag_rec.segment14
,p_cag_segment15 => l_cag_rec.segment15
,p_cag_segment16 => l_cag_rec.segment16
,p_cag_segment17 => l_cag_rec.segment17
,p_cag_segment18 => l_cag_rec.segment18
,p_cag_segment19 => l_cag_rec.segment19
,p_cag_segment20 => l_cag_rec.segment20
,p_notice_period => l_asg_rec.notice_period
,p_notice_period_uom => l_asg_rec.notice_period_uom
,p_employee_category => l_asg_rec.employee_category
,p_work_at_home => l_asg_rec.work_at_home
,p_job_post_source_name => l_asg_rec.job_post_source_name
,p_cagr_grade_def_id => l_dummynum1 -- Bug # 2788390 modified l_dummy to l_dummynum1.
,p_cagr_concatenated_segments => l_dummyv
,p_concatenated_segments => l_dummyv
,p_soft_coding_keyflex_id => l_dummy1
,p_comment_id => l_dummy2
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_hourly_salaried_warning => l_dummyb
);
UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
PAF.RECRUITER_ID =l_asg_rec.recruiter_id
WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
PAF.EFFECTIVE_END_DATE = l_effective_end_date;
UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_aplasg_vacancy_id ,
PAF.RECRUITER_ID = l_aplasg_RECRUITER_ID
WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
PAF.EFFECTIVE_END_DATE = l_effective_end_date;
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
update per_pay_proposals set assignment_id = l_primary_asg_id
where pay_proposal_id = l_pay_pspl_id;
/* hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_employment_category => l_asg_rec.employment_category
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_dummy
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
);*/
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_supervisor_assignment_id => nvl(l_asg_rec.supervisor_assignment_id
, l_primary_asg_rec.supervisor_assignment_id) -- 13595937
,p_segment1 => l_pgp_rec.segment1
,p_segment2 => l_pgp_rec.segment2
,p_segment3 => l_pgp_rec.segment3
,p_segment4 => l_pgp_rec.segment4
,p_segment5 => l_pgp_rec.segment5
,p_segment6 => l_pgp_rec.segment6
,p_segment7 => l_pgp_rec.segment7
,p_segment8 => l_pgp_rec.segment8
,p_segment9 => l_pgp_rec.segment9
,p_segment10 => l_pgp_rec.segment10
,p_segment11 => l_pgp_rec.segment11
,p_segment12 => l_pgp_rec.segment12
,p_segment13 => l_pgp_rec.segment13
,p_segment14 => l_pgp_rec.segment14
,p_segment15 => l_pgp_rec.segment15
,p_segment16 => l_pgp_rec.segment16
,p_segment17 => l_pgp_rec.segment17
,p_segment18 => l_pgp_rec.segment18
,p_segment19 => l_pgp_rec.segment19
,p_segment20 => l_pgp_rec.segment20
,p_segment21 => l_pgp_rec.segment21
,p_segment22 => l_pgp_rec.segment22
,p_segment23 => l_pgp_rec.segment23
,p_segment24 => l_pgp_rec.segment24
,p_segment25 => l_pgp_rec.segment25
,p_segment26 => l_pgp_rec.segment26
,p_segment27 => l_pgp_rec.segment27
,p_segment28 => l_pgp_rec.segment28
,p_segment29 => l_pgp_rec.segment29
,p_segment30 => l_pgp_rec.segment30
,p_employment_category => l_asg_rec.employment_category
,p_people_group_id => l_dummy
,p_soft_coding_keyflex_id => l_asg_rec.soft_coding_keyflex_id
,p_group_name => l_dummyv
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_spp_delete_warning => l_dummyb
,p_entries_changed_warning => l_dummyv
,p_tax_district_changed_warning => l_dummyb
,p_concatenated_segments => l_concatenated_segments
,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
);
hr_assignment_api.update_emp_asg
(p_validate => FALSE
,p_effective_date => l_hire_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_primary_asg_id
,p_object_version_number => l_primary_ovn
,p_supervisor_id => l_asg_rec.supervisor_id
,p_assignment_number => l_asg_rec.assignment_number
,p_change_reason => NULL -- l_asg_rec.change_reason Bug 12946619
,p_date_probation_end => l_asg_rec.date_probation_end
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_frequency => l_asg_rec.frequency
,p_internal_address_line => l_asg_rec.internal_address_line
,p_manager_flag => l_asg_rec.manager_flag
,p_normal_hours => l_asg_rec.normal_hours
,p_perf_review_period => l_asg_rec.perf_review_period
,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
,p_probation_period => l_asg_rec.probation_period
,p_probation_unit => l_asg_rec.probation_unit
,p_sal_review_period => l_asg_rec.sal_review_period
,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_bargaining_unit_code => l_asg_rec.bargaining_unit_code
,p_labour_union_member_flag => l_asg_rec.labour_union_member_flag
,p_hourly_salaried_code => l_asg_rec.hourly_salaried_code
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => l_asg_rec.ass_attribute1
,p_ass_attribute2 => l_asg_rec.ass_attribute2
,p_ass_attribute3 => l_asg_rec.ass_attribute3
,p_ass_attribute4 => l_asg_rec.ass_attribute4
,p_ass_attribute5 => l_asg_rec.ass_attribute5
,p_ass_attribute6 => l_asg_rec.ass_attribute6
,p_ass_attribute7 => l_asg_rec.ass_attribute7
,p_ass_attribute8 => l_asg_rec.ass_attribute8
,p_ass_attribute9 => l_asg_rec.ass_attribute9
,p_ass_attribute10 => l_asg_rec.ass_attribute10
,p_ass_attribute11 => l_asg_rec.ass_attribute11
,p_ass_attribute12 => l_asg_rec.ass_attribute12
,p_ass_attribute13 => l_asg_rec.ass_attribute13
,p_ass_attribute14 => l_asg_rec.ass_attribute14
,p_ass_attribute15 => l_asg_rec.ass_attribute15
,p_ass_attribute16 => l_asg_rec.ass_attribute16
,p_ass_attribute17 => l_asg_rec.ass_attribute17
,p_ass_attribute18 => l_asg_rec.ass_attribute18
,p_ass_attribute19 => l_asg_rec.ass_attribute19
,p_ass_attribute20 => l_asg_rec.ass_attribute20
,p_ass_attribute21 => l_asg_rec.ass_attribute21
,p_ass_attribute22 => l_asg_rec.ass_attribute22
,p_ass_attribute23 => l_asg_rec.ass_attribute23
,p_ass_attribute24 => l_asg_rec.ass_attribute24
,p_ass_attribute25 => l_asg_rec.ass_attribute25
,p_ass_attribute26 => l_asg_rec.ass_attribute26
,p_ass_attribute27 => l_asg_rec.ass_attribute27
,p_ass_attribute28 => l_asg_rec.ass_attribute28
,p_ass_attribute29 => l_asg_rec.ass_attribute29
,p_ass_attribute30 => l_asg_rec.ass_attribute30
,p_segment1 => l_scl_rec.segment1
,p_segment2 => l_scl_rec.segment2
,p_segment3 => l_scl_rec.segment3
,p_segment4 => l_scl_rec.segment4
,p_segment5 => l_scl_rec.segment5
,p_segment6 => l_scl_rec.segment6
,p_segment7 => l_scl_rec.segment7
,p_segment8 => l_scl_rec.segment8
,p_segment9 => l_scl_rec.segment9
,p_segment10 => l_scl_rec.segment10
,p_segment11 => l_scl_rec.segment11
,p_segment12 => l_scl_rec.segment12
,p_segment13 => l_scl_rec.segment13
,p_segment14 => l_scl_rec.segment14
,p_segment15 => l_scl_rec.segment15
,p_segment16 => l_scl_rec.segment16
,p_segment17 => l_scl_rec.segment17
,p_segment18 => l_scl_rec.segment18
,p_segment19 => l_scl_rec.segment19
,p_segment20 => l_scl_rec.segment20
,p_segment21 => l_scl_rec.segment21
,p_segment22 => l_scl_rec.segment22
,p_segment23 => l_scl_rec.segment23
,p_segment24 => l_scl_rec.segment24
,p_segment25 => l_scl_rec.segment25
,p_segment26 => l_scl_rec.segment26
,p_segment27 => l_scl_rec.segment27
,p_segment28 => l_scl_rec.segment28
,p_segment29 => l_scl_rec.segment29
,p_segment30 => l_scl_rec.segment30
,p_contract_id => l_asg_rec.contract_id
,p_establishment_id => l_asg_rec.establishment_id
,p_collective_agreement_id => l_asg_rec.collective_agreement_id
,p_cagr_id_flex_num => l_asg_rec.cagr_id_flex_num
,p_cag_segment1 => l_cag_rec.segment1
,p_cag_segment2 => l_cag_rec.segment2
,p_cag_segment3 => l_cag_rec.segment3
,p_cag_segment4 => l_cag_rec.segment4
,p_cag_segment5 => l_cag_rec.segment5
,p_cag_segment6 => l_cag_rec.segment6
,p_cag_segment7 => l_cag_rec.segment7
,p_cag_segment8 => l_cag_rec.segment8
,p_cag_segment9 => l_cag_rec.segment9
,p_cag_segment10 => l_cag_rec.segment10
,p_cag_segment11 => l_cag_rec.segment11
,p_cag_segment12 => l_cag_rec.segment12
,p_cag_segment13 => l_cag_rec.segment13
,p_cag_segment14 => l_cag_rec.segment14
,p_cag_segment15 => l_cag_rec.segment15
,p_cag_segment16 => l_cag_rec.segment16
,p_cag_segment17 => l_cag_rec.segment17
,p_cag_segment18 => l_cag_rec.segment18
,p_cag_segment19 => l_cag_rec.segment19
,p_cag_segment20 => l_cag_rec.segment20
,p_notice_period => l_asg_rec.notice_period
,p_notice_period_uom => l_asg_rec.notice_period_uom
,p_employee_category => l_asg_rec.employee_category
,p_work_at_home => l_asg_rec.work_at_home
,p_job_post_source_name => l_asg_rec.job_post_source_name
,p_cagr_grade_def_id => l_dummynum1 -- Bug # 2788390 modified l_dummy to l_dummynum1.
,p_cagr_concatenated_segments => l_dummyv
,p_concatenated_segments => l_dummyv
,p_soft_coding_keyflex_id => l_dummy1
,p_comment_id => l_dummy2
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_dummyb
,p_other_manager_warning => l_dummyb
,p_hourly_salaried_warning => l_dummyb
);
UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
PAF.RECRUITER_ID =l_asg_rec.recruiter_id
WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
PAF.EFFECTIVE_END_DATE = l_effective_end_date;
UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_aplasg_vacancy_id ,
PAF.RECRUITER_ID = l_aplasg_RECRUITER_ID
WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
PAF.EFFECTIVE_END_DATE = l_effective_end_date;
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
update per_pay_proposals set assignment_id = l_primary_asg_id
where pay_proposal_id = l_pay_pspl_id;
p_dt_mode => 'UPDATE',
p_validation_start_date => l_effective_start_date,
p_validation_end_date => l_effective_end_date
);
-- retrieve the max end date of the ASG to update the APLN date_end
select max(effective_end_date) into l_apl_end_date
from per_all_assignments_f
where person_id = p_person_id
and assignment_id = apl.assignment_id
and assignment_type = 'A';
SELECT per_applications_s.nextval into l_new_application_id FROM sys.dual;
INSERT INTO PER_APPLICATIONS(
application_id,
business_group_id,
person_id,
date_received,
date_end)
VALUES (l_new_application_id,l_business_group_id,p_person_id,apl.effective_start_date,l_apl_end_date);
-- if APLN end date < ASG end date, update the APLN
if nvl(l_current_apl_end_date,hr_api.g_eot) < nvl(l_apl_end_date,hr_api.g_eot) then
update per_applications
set date_end = l_apl_end_date
where person_id = p_person_id
and application_id = l_cur_apl_id;
-- update the ASG with the correct APLN ID
update per_all_assignments_f
set application_id = l_cur_apl_id
where person_id = p_person_id
and assignment_id = apl.assignment_id
and effective_start_date = apl.effective_start_date
and effective_end_date = apl.effective_end_date;
select object_version_number into l_per_object_version_number
from per_all_people_f
where person_id = p_person_id
and apl.date_received between effective_start_date and effective_end_date;
,p_datetrack_mode => 'UPDATE'
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => p_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= p_person_id
and apl.date_end between effective_start_date and effective_end_date;
,p_datetrack_mode => 'UPDATE'
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_comment_id => l_comment_id
,p_current_applicant_flag => l_current_applicant_flag
,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
,p_current_employee_flag => l_current_employee_flag
,p_full_name => l_full_name
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => p_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
,p_npw_number => l_npw_number
);