[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;
,p_resume_last_updated in date --default null
,p_student_status in varchar2 --default null
,p_work_schedule in varchar2 --default null
,p_suffix in varchar2 --default null
,p_date_of_death in date --default null
,p_benefit_group_id in number --default null
,p_receipt_of_death_cert_date in date --default null
,p_coord_ben_med_pln_no in varchar2 --default null
,p_coord_ben_no_cvg_flag in varchar2 --default 'N'
,p_uses_tobacco_flag in varchar2 --default null
,p_dpdnt_adoption_date in date --default null
,p_dpdnt_vlntry_svce_flag in varchar2 --default 'N'
,p_original_date_of_hire in date --default null
,p_town_of_birth in varchar2 --default null
,p_region_of_birth in varchar2 --default null
,p_country_of_birth in varchar2 --default null
,p_global_person_id in varchar2 --default null
,p_party_id in number --default null
,p_vacancy_id in number -- Added for bug 3680947.
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_application_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_apl_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_name_combination_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'create_applicant';
l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
l_resume_last_updated := trunc(p_resume_last_updated);
,p_resume_last_updated => l_resume_last_updated
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_date_of_death => l_date_of_death
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => l_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => l_original_date_of_hire
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_vacancy_id => p_vacancy_id
);
,p_resume_last_updated => l_resume_last_updated
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_date_of_death => l_date_of_death
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => l_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_npw_number => l_npw_number
,p_object_version_number => p_per_object_version_number
,p_effective_date => l_date_received
,p_name_combination_warning => l_name_combination_warning
,p_dob_null_warning => l_dob_null_warning
,p_orig_hire_warning => l_orig_hire_warning
);
,p_resume_last_updated => l_resume_last_updated
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_date_of_death => l_date_of_death
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => l_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => l_original_date_of_hire
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
,p_vacancy_id => p_vacancy_id
,p_person_id => l_person_id
,p_assignment_id => l_assignment_id
,p_application_id => l_application_id
,p_per_object_version_number => l_per_object_version_number
,p_asg_object_version_number => l_asg_object_version_number
,p_apl_object_version_number => l_apl_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_per_comment_id => l_per_comment_id
,p_assignment_sequence => l_assignment_sequence
,p_name_combination_warning => l_name_combination_warning
,p_orig_hire_warning => l_orig_hire_warning
);
SELECT object_version_number
INTO p_per_object_Version_number
FROM per_all_people_f
WHERE person_id = l_person_id
And effective_start_Date = l_per_effective_start_date
and effective_end_Date = l_per_effective_end_date;
,p_resume_last_updated in date --default null
,p_student_status in varchar2 --default null
,p_work_schedule in varchar2 --default null
,p_suffix in varchar2 --default null
,p_date_of_death in date --default null
,p_benefit_group_id in number --default null
,p_receipt_of_death_cert_date in date --default null
,p_coord_ben_med_pln_no in varchar2 --default null
,p_coord_ben_no_cvg_flag in varchar2 --default 'N'
,p_uses_tobacco_flag in varchar2 --default null
,p_dpdnt_adoption_date in date --default null
,p_dpdnt_vlntry_svce_flag in varchar2 --default 'N'
,p_original_date_of_hire in date --default null
,p_town_of_birth in varchar2 --default null
,p_region_of_birth in varchar2 --default null
,p_country_of_birth in varchar2 --default null
,p_global_person_id in varchar2 --default null
,p_party_id in number --default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_application_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_apl_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_name_combination_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'create_gb_applicant';
select legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
,p_resume_last_updated => p_resume_last_updated
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_date_of_death => p_date_of_death
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
--
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_application_id => p_application_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_apl_object_version_number => p_apl_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_name_combination_warning => p_name_combination_warning
,p_orig_hire_warning => p_orig_hire_warning
);
,p_resume_last_updated in date --default null
,p_student_status in varchar2 --default null
,p_work_schedule in varchar2 --default null
,p_suffix in varchar2 --default null
,p_date_of_death in date --default null
,p_benefit_group_id in number --default null
,p_receipt_of_death_cert_date in date --default null
,p_coord_ben_med_pln_no in varchar2 --default null
,p_coord_ben_no_cvg_flag in varchar2 --default 'N'
,p_uses_tobacco_flag in varchar2 --default null
,p_dpdnt_adoption_date in date --default null
,p_dpdnt_vlntry_svce_flag in varchar2 --default 'N'
,p_original_date_of_hire in date --default null
,p_town_of_birth in varchar2 --default null
,p_region_of_birth in varchar2 --default null
,p_country_of_birth in varchar2 --default null
,p_global_person_id in varchar2 --default null
,p_party_id in number --default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_application_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_apl_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_name_combination_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean
)
is
l_vets100A varchar2(100);
select legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
,p_resume_last_updated => p_resume_last_updated
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_date_of_death => p_date_of_death
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
--
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_application_id => p_application_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_apl_object_version_number => p_apl_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_name_combination_warning => p_name_combination_warning
,p_orig_hire_warning => p_orig_hire_warning
);
,p_resume_last_updated in date --default null
,p_student_status in varchar2 --default null
,p_work_schedule in varchar2 --default null
,p_suffix in varchar2 --default null
,p_date_of_death in date --default null
,p_benefit_group_id in number --default null
,p_receipt_of_death_cert_date in date --default null
,p_coord_ben_med_pln_no in varchar2 --default null
,p_coord_ben_no_cvg_flag in varchar2 --default 'N'
,p_uses_tobacco_flag in varchar2 --default null
,p_dpdnt_adoption_date in date --default null
,p_dpdnt_vlntry_svce_flag in varchar2 --default 'N'
,p_original_date_of_hire in date --default null
,p_town_of_birth in varchar2 --default null
,p_region_of_birth in varchar2 --default null
,p_country_of_birth in varchar2 --default null
,p_global_person_id in varchar2 --default null
,p_party_id in number --default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_application_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_apl_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_name_combination_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'create_us_applicant';
select legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
,p_resume_last_updated => p_resume_last_updated
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_suffix => p_suffix
,p_date_of_death => p_date_of_death
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_party_id => p_party_id
--
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_application_id => p_application_id
,p_per_object_version_number => p_per_object_version_number
,p_asg_object_version_number => p_asg_object_version_number
,p_apl_object_version_number => p_apl_object_version_number
,p_per_effective_start_date => p_per_effective_start_date
,p_per_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_per_comment_id => p_per_comment_id
,p_assignment_sequence => p_assignment_sequence
,p_name_combination_warning => p_name_combination_warning
,p_orig_hire_warning => p_orig_hire_warning
);
l_payroll_id_updated boolean;
select date_received,nvl(date_end ,hr_api.g_eot)
from per_applications
where person_id = p_person_id and date_received > l_hire_date;
select 'Y' from per_applications
where person_id = p_person_id
and date_received = l_apln_date_to +1;
select assignment_id, effective_start_date, effective_end_date
from per_all_assignments_f
where person_id= p_person_id
and effective_start_date > p_hire_date
and assignment_type = 'A'
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);
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;
l_datetrack_mode varchar2(10):='UPDATE';
select final_process_date
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.actual_termination_date = p_hire_date-1;
select final_process_date
from per_periods_of_placement pps
where pps.person_id = p_person_id
and pps.actual_termination_date = p_hire_date-1;
select 'Y'
from per_all_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 - 1 between asg.effective_start_date and asg.effective_end_date
and exists (select null from per_all_people_f where person_id = p_person_id and effective_start_date = l_hire_date);
select date_of_birth
from per_all_people_f ppf
where ppf.person_id = p_person_id
and l_business_group_id = ppf.business_group_id
and p_hire_date between effective_start_date
and nvl(effective_end_date,p_hire_date);
select hoi1.org_information12, hoi1.org_information13
from hr_organization_information hoi1
where l_business_group_id +0 = hoi1.organization_id
and hoi1.org_information_context = 'Business Group Information';
select 'x'
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date > p_hire_date;--bug no 5105005
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 ppt.system_person_type,
per.business_group_id,
bus.legislation_code,
per.applicant_number,
pap.application_id,
pap.object_version_number,
per.npw_number,
per.original_date_of_hire -- #2978566
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_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 l_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status <> 'ACCEPTED'
and asg.assignment_type = 'A' --Fix for bug 2881076
order by asg.assignment_id;
select asg.assignment_id,
asg.object_version_number,
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'
and asg.assignment_type = 'A' --changed for bug 6501961
order by asg.assignment_id;
select asg.assignment_id,
asg.object_version_number,
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'
and asg.assignment_id=p_assignment_id;
select asg.assignment_id,
asg.object_version_number,
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'
and asg.assignment_id <> p_assignment_id
and asg.assignment_type = 'A' --changed for bug 6501961
order by asg.assignment_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 address_id, object_version_number
from per_addresses
where party_id=p_party_id
and person_id is null;
select phone_id, object_version_number
from per_phones
where party_id=p_party_id
and parent_id is null;
select previous_employer_id, object_version_number
from per_previous_employers
where party_id=p_party_id
and person_id is null;
select qualification_id, object_version_number
from per_qualifications
where party_id=p_party_id
and person_id is null;
select attendance_id, object_version_number
from per_establishment_attendances
where party_id=p_party_id
and person_id is null;
select final_process_date
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.actual_termination_date is not null
and pps.date_start < p_hire_date
and nvl(pps.final_process_date,p_hire_date) >= p_hire_date;
select legislation_code
from per_business_groups_perf pbg
,per_all_people_f ppf
where ppf.person_id = p_person_id
and pbg.business_group_id+0 = ppf.business_group_id;
SELECT per_information7
FROM per_all_people_f
WHERE person_id = p_person_id
AND effective_start_date =
(select max(effective_start_date)
from per_all_people_f
where person_id = p_person_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 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 p_hire_date between asg.effective_start_date
and asg.effective_end_date
and pas.per_system_status = 'ACCEPTED'
and asg.assignment_type = 'A' ;
select distinct PAAF.business_group_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;
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;
select projected_hire_date
from per_applications
where application_id =l_appl_id;
select probation_period,probation_unit,DATE_PROBATION_END from per_all_assignments_f
where assignment_id = p_assignment_id and
p_hire_date between effective_start_date and effective_end_date;
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
-- Checking for future 'E' asg changes when the profile option is set
open fpt_future_asg_changes;
-- 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;
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
select CHANGE_REASON into l_change_reason
from per_all_assignments_f where assignment_id= l_assignment_id
and l_hire_date -1 between effective_start_date and effective_end_date ;
select nvl(src_apl_asg_id,-1) into l_irc_link_exists
from PER_VAC_LINKED_ASSIGNMENTS
where tgt_apl_asg_id = l_assignment_id
and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate) ;
select assignment_status_type_id into l_irc_spl_status
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
,p_datetrack_mode => l_datetrack_mode -- 'UPDATE' 12320120 fix
--,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
,p_original_date_of_hire => l_original_date_of_hire
);
l_datetrack_mode := 'UPDATE';
,p_datetrack_update_mode=> l_datetrack_mode
--bug no 5105005
);
select object_version_number into l_per_latest_ovn
from per_all_people_f
where person_id= p_person_id
and l_apln_date_start 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
,p_original_date_of_hire => l_original_date_of_hire
,p_per_information7 => l_new_hire_code
);
select object_version_number into l_per_latest_ovn
from per_all_people_f
where person_id= p_person_id
and l_hire_date 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
,p_original_date_of_hire => l_original_date_of_hire
,p_per_information7 => l_new_hire_code
);
p_datetrack_mode => 'UPDATE',
p_assignment_status_type_id => l_assignment_status_type_id,
p_assignment_type => 'E',
p_primary_flag => l_primary_flag,
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 5749371
,p_date_probation_end => get_probation_end_date
(p_appl_id => l_application_id,
p_assignment_id => l_assignment_id,
p_hire_date => l_hire_date) ----- Fix For Bug # 10648201
);
select nvl(src_apl_asg_id,-1) into l_irc_link_exists
from PER_VAC_LINKED_ASSIGNMENTS
where tgt_apl_asg_id = l_assignment_id
and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate);
select assignment_status_type_id into l_irc_spl_status
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
p_dt_mode => 'UPDATE',
p_validation_start_date => l_per_effective_start_date,--p_per_effective_start_date, /* Fix For Bug # 10072348 */
p_validation_end_date => l_per_effective_end_date--p_per_effective_end_date /* Fix For Bug # 10072348 */
);
update_salary_proposal(l_assignment_id, l_hire_date);
p_datetrack_mode => 'UPDATE',
p_assignment_status_type_id => l_assignment_status_type_id,
p_assignment_type => 'E',
p_primary_flag => l_primary_flag,
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 5749371
,p_date_probation_end => get_probation_end_date
(p_appl_id => l_application_id,
p_assignment_id => l_assignment_id,
p_hire_date => l_hire_date) ----- Fix For Bug # 10648201
);
select nvl(src_apl_asg_id,-1) into l_irc_link_exists
from PER_VAC_LINKED_ASSIGNMENTS
where tgt_apl_asg_id = l_assignment_id
and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate);
select assignment_status_type_id into l_irc_spl_status
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
update_salary_proposal(l_assignment_id, l_hire_date);
p_datetrack_mode => 'UPDATE',
p_assignment_status_type_id => l_assignment_status_type_id,
p_assignment_type => 'E',
p_primary_flag => l_primary_flag,
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_date_probation_end => get_probation_end_date
(p_appl_id => l_application_id,
p_assignment_id => l_assignment_id,
p_hire_date => l_hire_date) ----- Fix For Bug # 10648201
);
select nvl(src_apl_asg_id,-1) into l_irc_link_exists
from PER_VAC_LINKED_ASSIGNMENTS
where tgt_apl_asg_id = l_assignment_id
and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate);
select assignment_status_type_id into l_irc_spl_status
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
update_salary_proposal(l_assignment_id, l_hire_date);
hr_assignment.update_assgn_context_value (l_business_group_id,
p_person_id,
l_assignment_id,
p_hire_date);
SELECT object_version_number
INTO l_asg_object_Version_number
FROM per_all_assignments_f
WHERE business_group_id = l_business_group_id
and person_id = p_person_id
and assignment_id = l_assignment_id
and effective_start_Date = p_hire_date;
Select vacancy_id
From per_all_assignments_f
Where assignment_id = l_assg_id
And p_effective_date between effective_start_date and effective_end_date;
SELECT apl.application_id
,apl.object_version_number
,per.business_group_id
,per.applicant_number
,per.employee_number
,pet.system_person_type
,per.npw_number
FROM per_applications apl
,per_person_types pet
,per_people_f per
WHERE p_effective_date BETWEEN apl.date_received
AND NVL(apl.date_end,p_effective_date)
AND apl.person_id = per.person_id
AND pet.person_type_id = per.person_type_id
AND p_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.person_id = p_person_id;
SELECT asg.assignment_id
,asg.object_version_number
,asg.effective_end_date -- 3652025
,asg.assignment_status_type_id --7229710
FROM per_all_assignments_f asg
WHERE asg.person_id = p_person_id
AND p_effective_date+1 BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.assignment_type = 'A';
SELECT as2.assignment_id, as2.effective_start_date, as2.object_version_number
FROM per_all_assignments_f as2
WHERE as2.person_id = cp_person_id
AND as2.application_id = cp_application_id
AND as2.assignment_type = 'A'
AND as2.effective_start_date > cp_effective_date
AND not exists
(select 'N'
from per_all_assignments_f as1
where as1.assignment_id = as2.assignment_id
and as1.effective_start_date < as2.effective_start_date)
ORDER BY as2.effective_start_date, as2.assignment_id ASC;
SELECT null
FROM per_all_people_f
WHERE person_id = cp_person_id
AND (effective_start_date > cp_termination_date
OR
cp_termination_date between effective_start_date
and effective_end_date)
for update nowait;
SELECT null
FROM per_person_type_usages_f ptu
,per_person_types ppt
WHERE person_id = cp_person_id
AND (effective_start_date > cp_termination_date
OR
cp_termination_date between effective_start_date
and effective_end_date)
AND ptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('APL','EX_APL')
-- for update nowait; for bug 6433245
for update of ptu.person_id nowait;
,p_datetrack_mode => hr_api.g_update
,p_person_id => p_person_id
,p_object_version_number => p_object_version_number
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
);
,p_datetrack_mode => hr_api.g_delete
,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_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
,p_datetrack_mode => hr_api.g_delete
,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_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
delete from per_letter_request_lines plrl
where plrl.assignment_id = l_assignment.assignment_id
and plrl.assignment_status_type_id = l_assignment.assignment_status_type_id
and exists
(select null
from per_letter_requests plr
where plr.letter_request_id = plrl.letter_request_id
and plr.request_status = 'PENDING'
and plr.auto_or_manual = 'AUTO');
hr_applicant_internal.Update_PER_PTU_To_EX_APL
(p_business_group_id => l_application.business_group_id
,p_person_id => p_person_id
,p_effective_date => l_effective_date+1 -- when becomes EX_APL
,p_person_type_id => l_person_type_id
,p_per_effective_start_date => l_effective_start_date
,p_per_effective_end_date => l_effective_end_date
);
UPDATE per_applications
set date_end = l_effective_date
,termination_reason = p_termination_reason
WHERE application_id = l_application.application_id;
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 application_id
from per_applications
where person_id = p_person_id
and date_received > p_effective_date
order by date_received asc;
select application_id
from per_applications
where person_id = p_person_id
and date_received < p_effective_date
and nvl(date_end,hr_api.g_eot) >= p_effective_date;
select application_id
from per_applications
where person_id = p_person_id
and date_end = p_effective_date-1;
select ppf.business_group_id
,ppf.employee_number
,ppf.npw_number
,ppt.system_person_type
from per_person_types ppt
,per_people_f ppf
where ppf.person_id = p_person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.business_group_id + 0 = ppf.business_group_id
and (l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date or ppf.effective_start_date > l_effective_date);