[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select h.rowid
from hr_comments h
, per_assignments_f paf
where h.comment_id = paf.comment_id
and paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id
and paf.effective_start_date >= p_effective_date
for update of h.comment_id;
select rowid
from pay_personal_payment_methods ppm
where ppm.business_group_id = p_business_group_id
and exists (select 'exists'
from per_all_assignments_f paf
where paf.business_group_id +0= p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_id = ppm.assignment_id
--start bug 5987416
--and ppm.effective_start_date>= p_effective_date
--end bug 5987416
)
and ppm.effective_start_date >= p_effective_date
for update of ppm.assignment_id;
select pab.rowid
from per_assignment_budget_values_f pab
, per_assignments_f paf
where pab.business_group_id + 0 = paf.business_group_id + 0
and paf.business_group_id + 0 = p_business_group_id
and pab.assignment_id = paf.assignment_id
and paf.person_id = p_person_id
and paf.effective_end_date >=p_effective_date
and pab.effective_end_date >=p_effective_date
for update of pab.assignment_id;
select rowid
from per_assignments_f p
where p.recruiter_id = p_person_id
and (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
for update of assignment_id;
select pb.rowid
from per_events pe
, per_bookings pb
where pe.business_group_id = pb.business_group_id
and (pb.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pe.event_id = pb.event_id
and pe.event_or_interview in ('I','E')
and pb.person_id = p_person_id
for update of pb.event_id;
select rowid
from per_vacancies pv
where (pv.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pv.recruiter_id = p_person_id
and pv.date_from >= p_effective_date
for update of pv.vacancy_id;
select rowid
from per_requisitions pr
where (pr.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pr.person_id = p_person_id
for update of person_id;
select rowid
from per_assignments_f p
where (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and p.supervisor_id = p_person_id
for update of assignment_id;
select ppf.rowid
from per_people_f ppf
where ppf.person_id = p_person_id
for update of ppf.person_id;
select paf.rowid
from per_assignments_f paf
where paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_type = 'C'
for update of paf.assignment_id;
select pp.rowid
from per_periods_of_placement pp
where pp.person_id = p_person_id
for update of person_id;
select pgr.rowid
from pay_grade_rules_f pgr
where exists (select 'x'
from per_assignments_f paf
where pgr.grade_or_spinal_point_id = paf.assignment_id
and paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id)
for update of pgr.grade_or_spinal_point_id;
select spp.rowid
from per_spinal_point_placements_f spp
where spp.business_group_id = p_business_group_id and
exists (select 'x'
from per_assignments_f paf
where spp.assignment_id = paf.assignment_id
and paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id)
for update of spp.assignment_id;
select pca.rowid
from pay_cost_allocations_f pca
where exists (select 'x'
from per_assignments_f paf
where pca.assignment_id = paf.assignment_id
and paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id)
for update of pca.assignment_id;
select ppf.rowid
from per_people_f ppf
where ppf.person_id = p_person_id
for update of ppf.person_id;
select pps.rowid
from per_periods_of_service pps
where pps.person_id = p_person_id
for update of pps.person_id;
select pap.rowid
from per_applications pap
where pap.person_id = p_person_id
for update of pap.person_id;
select rowid
from per_assignments_f p
where (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and p.supervisor_id = p_person_id
for update of assignment_id;
select rowid
from per_assignments_f p
where p.recruiter_id = p_person_id
and (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
for update of assignment_id;
select pb.rowid
from per_events pe
, per_bookings pb
where pe.business_group_id = pb.business_group_id
and (pb.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pe.event_id = pb.event_id
and pe.event_or_interview = p_type
and pb.person_id = p_person_id
for update of pb.event_id;
select rowid
from per_vacancies pv
where (pv.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pv.recruiter_id = p_person_id
and pv.date_from >= p_primary_date
for update of pv.vacancy_id;
select rowid
from per_requisitions pr
where (pr.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pr.person_id = p_person_id
for update of person_id;
select rowid
from per_absence_attendances paa
where paa.business_group_id +0 = p_business_group_id
and paa.person_id = p_person_id
and paa.date_start >= p_primary_date
for update of person_id;
select rowid
from pay_personal_payment_methods ppm
where ppm.business_group_id = p_business_group_id
and exists (select 'exists'
from per_all_assignments_f paf
where paf.business_group_id +0= p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_id = ppm.assignment_id
and paf.period_of_service_id = p_primary_id
--start bug 5987416
--and ppm.effective_start_date>= p_primary_date
--end bug 5987416
)
and ppm.effective_start_date >= p_primary_date
for update of ppm.assignment_id;
select pab.rowid
from per_assignment_budget_values_f pab
, per_assignments_f paf
where pab.business_group_id + 0 = paf.business_group_id + 0
and paf.business_group_id + 0 = p_business_group_id
and pab.assignment_id = paf.assignment_id
and paf.person_id = p_person_id
and paf.effective_end_date >=p_primary_date
and pab.effective_end_date >=p_primary_date
for update of pab.assignment_id;
select p.rowid
from per_letter_request_lines p
, per_assignments_f paf
where p.assignment_id = paf.assignment_id
and paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id
and paf.application_id = p_primary_id
and paf.effective_end_date >=p_primary_date
for update of p.assignment_id;
select h.rowid
from hr_comments h
, per_assignments_f paf
where h.comment_id = paf.comment_id
and paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id
and paf.application_id = p_primary_id
and paf.effective_end_date >=p_primary_date
for update of h.comment_id;
select paf.rowid
from per_assignments_f paf
where paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_type = 'A'
and paf.application_id = p_primary_id
for update of paf.assignment_id;
select paf.rowid
from per_assignments_f paf
where paf.business_group_id + 0 = p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_type = 'E'
and paf.application_id = p_primary_id
for update of paf.assignment_id;
select 'Y'
from per_all_assignments_f a
where a.person_id = p_person_id
--
-- 115.51 (START)
--
AND a.period_of_service_id = p_primary_id
--
-- 115.51 (END)
--
AND ((a.effective_start_date = p_primary_date
and a.primary_flag <> 'Y'
and not exists ( select b.assignment_id
from per_all_assignments_f b
where nvl(b.effective_end_date,hr_api.g_eot)
= (p_primary_date-1)
and b.assignment_id = a.assignment_id) )
OR a.effective_start_date > p_primary_date );
select rowid
from per_assignments_f p
where (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and p.supervisor_id = p_person_id;
select rowid
from per_assignments_f p
where p.recruiter_id = p_person_id
and (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
select 'Events exist'
from per_events pe
, per_bookings pb
where pe.business_group_id = pb.business_group_id
and (pb.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pe.event_id = pb.event_id
and pe.event_or_interview = p_type
and pb.person_id = p_person_id;
select 'Interviews exist'
from per_events pe
where (pe.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pe.event_or_interview = 'I'
and pe.internal_contact_person_id = p_person_id;
select rowid
from per_vacancies pv
where (pv.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pv.recruiter_id = p_person_id
and pv.date_from >= p_primary_date;
select rowid
from per_requisitions pr
where (pr.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pr.person_id = p_person_id;
select rowid
from per_assignment_budget_values_f pab
where pab.business_group_id = p_business_group_id and
exists (select 'budget_values exist'
from per_all_assignments_f paf
where pab.business_group_id +0= paf.business_group_id + 0
and paf.business_group_id +0= p_business_group_id
and pab.assignment_id = paf.assignment_id
and paf.person_id = p_person_id
and paf.period_of_service_id = p_primary_id
and paf.effective_end_date >= p_primary_date
--start bug 5987416
--and pab.effective_end_date >= p_primary_date
)
AND pab.effective_end_date >= p_primary_date;
select rowid
from pay_personal_payment_methods ppm
where ppm.business_group_id = p_business_group_id
and exists (select 'exists'
from per_all_assignments_f paf
where paf.business_group_id +0= p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_id = ppm.assignment_id
and paf.period_of_service_id = p_primary_id
--start bug 5987416
--and ppm.effective_start_date>= p_primary_date
--end bug 5987416
)
and ppm.effective_start_date >= p_primary_date;
SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
--
-- 115.51 (START)
--
AND asg.period_of_service_id = p_primary_id
--
-- 115.51 (END)
--
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
--
--Start Bug 4724223
--
AND pac.action_type NOT IN ('X','BEE')
AND p_primary_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
--
--End Bug 4724223
--
AND pac.effective_date >= p_primary_date;
SELECT ptu.person_type_usage_id
FROM per_person_types pt,
per_person_type_usages_f ptu
WHERE pt.business_group_id = p_business_group_id
AND pt.person_type_id = ptu.person_type_id
AND p_primary_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id
AND pt.system_person_type = p_system_person_type;
/*Procedure to update the end date person type OTHER
when cancel placement is done.Added for the bug 6460093*/
procedure upd_person_type_usage_end_date
(
p_effective_date in date
,p_person_id in number
,p_system_person_type in varchar2
) is
cursor csr_upded_person_type_usages
(
p_effective_date in date
,p_person_id in number
,p_system_person_type in varchar2
) is
select ptu.person_type_usage_id
,ptu.object_version_number
from per_person_type_usages_f ptu
where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_id = p_person_id
and ptu.person_type_id in
(select ppt.person_type_id
from per_person_types ppt
where (( p_system_person_type = 'OTHER'
and ppt.system_person_type = 'OTHER' )));
update per_person_type_usages_f ptu
set effective_end_date = l_effective_end_date
where ptu.effective_end_date = p_effective_date
and ptu.person_id = p_person_id
and ptu.person_type_usage_id = l_csr_upd_per_type_usages.person_type_usage_id
and ptu.object_version_number = l_csr_upd_per_type_usages.object_version_number;
select rowid
from per_assignments_f p
where (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and p.supervisor_id = p_person_id;
select rowid
from per_assignments_f p
where p.recruiter_id = p_person_id
and (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
select rowid
from per_bookings pb
where (pb.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pb.person_id = p_person_id
and exists ( select 'row exists'
from per_events pe
where pe.business_group_id + 0 = p_business_group_id
and pe.event_id = pb.event_id
and pe.event_or_interview in ('I','E')
and pe.date_start >= p_date_start);
select rowid
from per_vacancies pv
where (pv.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pv.recruiter_id = p_person_id
and pv.date_from >= p_date_start;
select rowid
from per_requisitions pr
where (pr.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and pr.person_id = p_person_id;
select rowid
from per_absence_attendances paa
where paa.business_group_id +0 = p_business_group_id
and paa.person_id = p_person_id
and paa.date_start >= p_date_start;
select rowid
from per_assignment_budget_values_f pab
where pab.business_group_id = p_business_group_id and
exists (select 'budget_values exist'
from per_all_assignments_f paf
where pab.business_group_id +0= paf.business_group_id + 0
and paf.business_group_id +0= p_business_group_id
and pab.assignment_id = paf.assignment_id
and paf.person_id = p_person_id
and paf.period_of_service_id = l_period_of_service_id
and paf.effective_end_date >= p_date_start
--start bug 5987416
--and pab.effective_end_date >= p_date_start
)
and pab.effective_end_date >= p_date_start;
select rowid
from pay_personal_payment_methods ppm
where ppm.business_group_id = p_business_group_id
and exists (select 'exists'
from per_all_assignments_f paf
where paf.business_group_id +0= p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_id = ppm.assignment_id
and paf.period_of_service_id = l_period_of_service_id
--start bug 5987416
-- and ppm.effective_start_date>= p_date_start
--end bug 5987416
)
and ppm.effective_start_date >= p_date_start;
select rowid
from hr_comments h
where exists (select 'comments exist'
from per_all_assignments_f paf
where h.comment_id = paf.comment_id
and paf.business_group_id +0= p_business_group_id
and paf.person_id = p_person_id
and paf.period_of_service_id = l_period_of_service_id);
select rowid, assignment_id, assignment_status_type_id -- Bug 3564129
from per_all_assignments_f paf
where paf.business_group_id +0= p_business_group_id
and paf.person_id = p_person_id
and paf.period_of_service_id = l_period_of_service_id;
select iass.assignment_status_id, iass.object_version_number
from irc_assignment_statuses iass
where iass.assignment_status_id =
(select max(iass.assignment_status_id)
from irc_assignment_statuses iass
where iass.assignment_id = l_assignment_id
and iass.assignment_status_type_id = l_asg_status_type_id);
select assignment_id,effective_start_date,ROWID
from per_all_assignments_f
where person_id = p_person_id
and business_group_id +0 = p_business_group_id
and period_of_service_id is NULL
and assignment_type in ('E','A') -- 3194314
and effective_end_date = p_date_start - 1
for update of effective_end_date;
select paf.assignment_id, paf.effective_start_date
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id +0 = p_business_group_id
and assignment_type <> 'B'; --modified for bug #6449599
select rowid
from per_applications pap
where exists (select 'row exists'
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id +0 = p_business_group_id
and paf.period_of_service_id is NULL
and paf.effective_end_date = p_date_start - 1
and pap.application_id = paf.application_id);
select pps.rowid
from per_periods_of_service pps
where pps.person_id = p_person_id
and period_of_service_id = l_period_of_service_id;
select p.rowid, effective_end_date from per_people_f p
where p.person_id = p_person_id
and p.effective_start_date >= p_date_start;
select ptu.person_type_id
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and ptu.effective_start_date = p_date_start
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EMP';
select ptu.person_type_id
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and ptu.effective_end_date = p_date_start - 1
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'APL';
select pp.period_of_placement_id, pp.date_start
from per_periods_of_placement pp
where pp.person_id = p_person_id
and pp.actual_termination_date = cp_date_start - 1;
select p.rowid from per_people_f p
where p.person_id = p_person_id
and p.effective_end_date = p_date_start -1;
select ppp.pay_proposal_id, ppp.object_version_number
from per_pay_proposals ppp
where p_assignment_id = ppp.assignment_id;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_assignment_id
and creator_type = 'SP'
and p_start_date between
effective_start_date and effective_end_date;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_assignment_id
and creator_type = 'SP'
and (p_start_date - 1) between
effective_start_date and effective_end_date;
select pps.period_of_service_id
into v_dummy
from per_periods_of_service pps
where p_start_date = pps.date_start
and pps.person_id = p_person_id;
update per_all_assignments_f paf
set paf.supervisor_id = NULL
where paf.rowid = p_rowid;
update per_all_assignments_f paf
set paf.recruiter_id = NULL
where paf.rowid = p_rowid;
delete from per_bookings pb
where pb.rowid = p_rowid;
update per_all_vacancies pv
set pv.recruiter_id = NULL
where pv.rowid = p_rowid;
update per_requisitions pr
set pr.person_id = NULL
where pr.rowid = p_rowid;
delete from per_absence_attendances paa
where paa.rowid = p_rowid;
delete from per_assignment_budget_values_f pab
where pab.rowid = p_rowid;
delete from pay_personal_payment_methods ppm
where ppm.rowid = p_rowid;
delete from hr_comments h
where h.rowid = p_rowid;
delete from per_spinal_point_placements_f spp
where spp.assignment_id = l_assignment_id;
delete from pay_cost_allocations_f pca
where pca.assignment_id = l_assignment_id;
IRC_ASG_STATUS_API.delete_irc_asg_status
(p_assignment_status_id => l_asg_status_id
,p_object_version_number => l_asg_status_ovn);
hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id => l_pk_id
,p_business_group_id => l_business_group_id
,p_object_version_number => l_ovn
,p_validate => FALSE
,p_salary_warning => l_sal_warning);
hr_entry_api.delete_element_entry
('DELETE'
,p_start_date - 1
,l_element_entry_id);
hr_entry_api.delete_element_entry
('ZAP'
,p_start_date
,l_element_entry_id);
delete from per_all_assignments_f paf
where paf.rowid = p_rowid;
update per_applications pap
set pap.date_end = NULL
where pap.rowid = p_rowid;
update per_all_assignments_f paf
set paf.effective_end_date = p_end_of_time
where paf.rowid = p_rowid;
delete from per_periods_of_service
where rowid = p_rowid;
select person_type_id into l_person_type_id
from per_person_types
where business_group_id = p_business_group_id
and system_person_type = 'EX_EMP'
and default_flag = 'Y';
update per_people_f
set person_type_id = l_person_type_id,
effective_start_date = p_date_start, -- in case DT updates exist
current_employee_flag = null,
current_emp_or_apl_flag = l_apl_flag, -- 3848352 --null,
current_applicant_flag = l_apl_flag -- 3848352 --null
where rowid = p_rowid;
delete from per_people_f
where rowid = p_rowid;
select person_type_id into l_person_type_id
from per_person_types
where business_group_id = p_business_group_id
and system_person_type = 'APL'
and default_flag = 'Y';
select person_type_id into l_person_type_id
from per_person_types
where business_group_id = p_business_group_id
and system_person_type = 'OTHER' -- EX_CWK but this is not maintained
and default_flag = 'Y';
update per_people_f
set person_type_id = l_person_type_id,
current_employee_flag = null,
current_emp_or_apl_flag = l_apl_flag, -- 3848352 -- null,
current_applicant_flag = l_apl_flag, -- 3848352 --null,
per_information7 = null,
employee_number = null,
start_date = l_cwk_date_start,
effective_start_date = p_date_start, -- p_start_date, -- in case DT updates exist
original_date_of_hire = null
where rowid = p_rowid;
delete from per_people_f
where rowid = p_rowid;
delete from per_people_f
where rowid = p_rowid;
update per_people_f
set effective_end_date = p_end_of_time
where rowid = p_rowid;
,p_datetrack_delete_mode => 'ZAP'
);*/
,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
);
,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
);
per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
select distinct assignment_id
from per_assignments_f
where application_id = p_application_id;
select pe.event_id
from per_events pe
where pe.date_start >= p_date_received
and p_assignment_id = pe.assignment_id;
select pb.booking_id
from per_events pe,
per_bookings pb
where pb.event_id = pe.event_id
and pe.date_start >= p_date_received
and pb.person_id = p_person_id
and pe.emp_or_apl = 'A';
select pab.rowid
from per_assignment_budget_values_f pab
where pab.assignment_id = p_assignment_id;
select p.letter_request_line_id
from per_letter_request_lines p
where p.assignment_id = p_assignment_id;
select h.comment_id
from hr_comments h
, per_assignments_f paf
where h.comment_id = paf.comment_id
and paf.assignment_id = p_assignment_id;
select paf.rowid
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.application_id = p_application_id;
select rowid
from per_applications pap
where pap.date_received = p_date_received
and pap.person_id = p_person_id;
select rowid
from per_people_f
where person_id = p_person_id
and effective_start_date >= p_date_received;
select rowid
from per_people_f
where person_id = p_person_id
and effective_end_date = p_date_received - 1;
select sas.rowid
from per_secondary_ass_statuses sas
where sas.assignment_id = p_assignment_id;
select paei.assignment_extra_info_id, paei.object_version_number
from per_assignment_extra_info paei
where p_assignment_id = paei.assignment_id;
select ppp.pay_proposal_id, ppp.object_version_number
from per_pay_proposals ppp
where p_assignment_id = ppp.assignment_id;
select pwi.incident_id, pwi.object_version_number
from per_work_incidents pwi
where p_assignment_id = pwi.assignment_id;
select papa.rowid
from per_assign_proposal_answers papa
where p_assignment_id = papa.assignment_id;
select distinct h.comment_id --fix for bug 7157204.
from hr_comments h, per_people_f ppf
where ppf.business_group_id = p_business_group_id
and ppf.person_id = p_person_id
and ppf.effective_start_date >= p_date_received
and ppf.comment_id = h.comment_id;
select count(ptu.person_type_id)
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and ptu.effective_start_date = p_date_received
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type <> 'APL';
select rowid,effective_end_date,effective_start_date,current_applicant_flag
from per_people_f
where person_id = p_person_id
and effective_start_date = p_date_received;
select rowid,effective_end_date,effective_start_date,current_applicant_flag
from per_people_f
where person_id = p_person_id
and effective_start_date > p_date_received;
select person_type_id
from per_person_type_usages_f
where person_id = p_person_id
order by EFFECTIVE_END_DATE desc, EFFECTIVE_START_DATE desc;
select paf.assignment_id, paf.effective_start_date
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
delete from per_events pe
where pe.event_id = l_pk_id;
delete from per_assignment_budget_values_f pab
where pab.rowid = l_rowid;
delete from hr_comments h
where h.comment_id = l_pk_id;
delete from per_letter_request_lines plrl
where plrl.letter_request_line_id = l_pk_id;
per_secondary_ass_statuses_pkg.delete_row(l_rowid);
hr_assignment_extra_info_api.delete_assignment_extra_info(
p_validate => FALSE
,p_assignment_extra_info_id => l_pk_id
,p_object_version_number => l_ovn);
hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id => l_pk_id
,p_business_group_id => l_business_group_id
,p_object_version_number => l_ovn
,p_validate => FALSE
,p_salary_warning => l_sal_warning);
per_work_incident_api.delete_work_incident(
p_validate => FALSE
,p_incident_id => l_pk_id
,p_object_version_number => l_ovn);
delete from per_assign_proposal_answers papa
where papa.rowid = l_rowid;
delete from per_bookings pb
where pb.booking_id = l_pk_id;
delete from hr_comments h
where h.comment_id = l_pk_id;
delete from per_assignments_f paf
where paf.rowid = l_rowid;
delete from per_applications pap
where pap.rowid = l_rowid;
delete from per_people_f
where rowid = l_rowid;
update per_people_f
set effective_end_date = p_end_of_time
where rowid = l_rowid;
update per_people_f
set PERSON_TYPE_ID=l_person_type_id
where rowid = l_rowid;
delete from per_people_f
where rowid = l_rowid;
update per_people_f
set applicant_number = null,
current_applicant_flag=null,
current_emp_or_apl_flag=null,
PERSON_TYPE_ID=l_person_type_id
where rowid = l_rowid;
update per_people_f
set applicant_number = null,
current_applicant_flag=null,
current_emp_or_apl_flag=null,
PERSON_TYPE_ID=l_person_type_id,
effective_end_date=hr_general.end_of_time
where rowid = l_rowid;
hr_per_type_usage_internal.delete_person_type_usage
(p_validate => FALSE
,p_person_type_usage_id => l_pk_id
,p_effective_date => p_date_received
,p_datetrack_mode => 'ZAP'
,p_object_version_number => l_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date );
delete from per_person_type_usages_f ptu
where ptu.person_id = p_person_id
and ptu.effective_start_date = p_date_received
and ptu.person_type_id in (
select ppt.person_type_id
from per_person_types ppt
where ppt.system_person_type = 'APL');
per_cancel_hire_or_apl_pkg.update_person_list (p_person_id => p_person_id);
procedure update_person_list (p_person_id NUMBER) is
begin
--
-- Delete all rows from per_person_list
--
hr_security_internal.clear_from_person_list(p_person_id);
end update_person_list;
SELECT assignment_id
FROM per_assignments_f paf
WHERE (paf.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND paf.person_id = p_person_id;
SELECT per.person_id,
per.effective_start_date
FROM per_people_f per
WHERE per.person_id = p_person_id
AND per.current_npw_flag = 'Y'
AND per.effective_start_date >= p_date_start;
SELECT ROWID
FROM per_assignments_f p
WHERE (p.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND p.supervisor_id = p_person_id;
SELECT ROWID
FROM per_assignments_f p
WHERE p.recruiter_id = p_person_id
AND (p.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
SELECT 'Events exist'
FROM per_events pe,
per_bookings pb
WHERE pe.business_group_id = pb.business_group_id
AND (pb.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND pe.event_id = pb.event_id
AND pe.event_or_interview = p_type
AND pb.person_id = p_person_id;
SELECT 'Interviews exist'
FROM per_events pe
WHERE (pe.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND pe.event_or_interview = 'I'
AND pe.internal_contact_person_id = p_person_id;
SELECT ROWID
FROM per_vacancies pv
WHERE (pv.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND pv.recruiter_id = p_person_id
AND pv.date_from >= l_effective_date;
SELECT ROWID
FROM per_requisitions pr
WHERE (pr.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND pr.person_id = p_person_id;
SELECT ROWID
FROM per_assignment_budget_values_f pab
WHERE pab.business_group_id = p_business_group_id and
EXISTS (SELECT 'budget_values exist'
FROM per_all_assignments_f paf
WHERE pab.business_group_id +0= paf.business_group_id + 0
AND paf.business_group_id +0= p_business_group_id
AND pab.assignment_id = paf.assignment_id
AND paf.person_id = p_person_id
AND paf.period_of_placement_date_start = p_date_start
AND paf.effective_end_date >= l_effective_date
--START for 5987416
--AND pab.effective_end_date >= l_effective_date
)
AND pab.effective_end_date >= l_effective_date;
SELECT ROWID
FROM pay_personal_payment_methods ppm
WHERE ppm.business_group_id = p_business_group_id
AND EXISTS (SELECT 'exists'
FROM per_all_assignments_f paf
WHERE paf.business_group_id +0= p_business_group_id
AND paf.person_id = p_person_id
AND paf.assignment_id = ppm.assignment_id
AND paf.period_of_placement_date_start = p_date_start
--start bug 5987416
--AND ppm.effective_start_date>= l_effective_date
--end bug 5987416
)
AND ppm.effective_start_date >= l_effective_date;
SELECT ptu.person_type_usage_id
FROM per_person_types pt,
per_person_type_usages_f ptu
WHERE pt.business_group_id = p_business_group_id
AND pt.person_type_id = ptu.person_type_id
AND l_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id
AND pt.system_person_type = 'CWK';
SELECT ROWID
FROM per_bookings pb
WHERE (pb.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND pb.person_id = p_person_id
AND EXISTS (SELECT 'row exists'
FROM per_events pe
WHERE pe.business_group_id + 0 = p_business_group_id
AND pe.event_id = pb.event_id
AND pe.event_or_interview in ('I','E')
AND pe.date_start >= l_date_start);
SELECT ROWID
FROM per_requisitions pr
WHERE (pr.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND pr.person_id = p_person_id;
SELECT ROWID
FROM per_assignment_budget_values_f pab
WHERE pab.business_group_id = p_business_group_id and
EXISTS (SELECT 'budget_values exist'
FROM per_all_assignments_f paf
WHERE pab.business_group_id +0= paf.business_group_id + 0
AND paf.business_group_id +0= p_business_group_id
AND pab.assignment_id = paf.assignment_id
AND paf.person_id = p_person_id
AND paf.period_of_placement_date_start = p_date_start
AND paf.effective_end_date >= l_effective_date
--start for 5987416
--AND pab.effective_end_date >= l_effective_date
)
AND pab.effective_end_date >= l_effective_date;
SELECT ROWID
FROM pay_personal_payment_methods ppm
WHERE ppm.business_group_id = p_business_group_id
AND EXISTS (SELECT 'exists'
FROM per_all_assignments_f paf
WHERE paf.business_group_id +0= p_business_group_id
AND paf.person_id = p_person_id
AND paf.assignment_id = ppm.assignment_id
AND paf.period_of_placement_date_start = p_date_start
--start bug 5987416
--AND ppm.effective_start_date>= l_effective_date
--end bug 5987416
)
AND ppm.effective_start_date >= l_effective_date;
SELECT ROWID
FROM per_assignments_f p
WHERE (p.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND p.supervisor_id = p_person_id;
SELECT ROWID
FROM per_assignments_f p
WHERE p.recruiter_id = p_person_id
AND (p.business_group_id = p_business_group_id OR
NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
SELECT ROWID
FROM per_vacancies pv
WHERE (pv.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND pv.recruiter_id = p_person_id
AND pv.date_from >= p_date_start;
SELECT ROWID
FROM hr_comments h
WHERE EXISTS (SELECT 'comments exist'
FROM per_all_assignments_f paf
WHERE h.comment_id = paf.comment_id
AND paf.business_group_id +0 = p_business_group_id
AND paf.person_id = p_person_id
AND paf.period_of_placement_date_start = p_date_start);
SELECT ROWID,
assignment_id
FROM per_all_assignments_f paf
WHERE paf.business_group_id +0 = p_business_group_id
AND paf.person_id = p_person_id
AND paf.period_of_placement_date_start = p_date_start;
SELECT assignment_id,
effective_start_date,
ROWID
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND business_group_id +0 = p_business_group_id
AND effective_end_date = p_date_start - 1
AND assignment_type = 'C' -- 3194314
FOR UPDATE OF effective_end_date;
SELECT ROWID
FROM per_applications pap
WHERE EXISTS (SELECT 'row exists'
FROM per_all_assignments_f paf
WHERE paf.person_id = p_person_id
AND paf.business_group_id +0 = p_business_group_id
AND paf.effective_end_date = p_date_start - 1
AND pap.application_id = paf.application_id);
SELECT p.rowid,
effective_end_date
FROM per_people_f p
WHERE p.person_id = p_person_id
AND p.effective_start_date >= p_date_start;
SELECT p.rowid
FROM per_people_f p
WHERE p.person_id = p_person_id
AND p.effective_end_date = p_date_start -1;
SELECT pgr.rowid
FROM pay_grade_rules_f pgr
WHERE EXISTS (SELECT 'X'
FROM per_assignments_f paf
WHERE pgr.grade_or_spinal_point_id = paf.assignment_id
and paf.business_group_id + 0 = p_business_group_id
AND paf.person_id = p_person_id);
SELECT pp.rowid
FROM per_periods_of_placement pp
WHERE pp.person_id = p_person_id
AND pp.date_start = p_date_start;
SELECT pp.period_of_placement_id
FROM per_periods_of_placement pp
WHERE pp.person_id = p_person_id
AND pp.actual_termination_date = p_date_start -1;
SELECT ps.period_of_service_id
FROM per_periods_of_service ps
WHERE ps.person_id = p_person_id
AND ps.actual_termination_date = p_date_start -1;
SELECT pop.date_start
FROM per_periods_of_placement pop
WHERE pop.person_id = p_person_id
AND p_effective_date between pop.date_start and
nvl(pop.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));
SELECT person_type_id
FROM per_person_types
WHERE business_group_id = p_business_group_id
AND system_person_type = p_system_person_type
AND default_flag = 'Y';
SELECT person_type_id
FROM per_person_types
WHERE business_group_id = p_business_group_id
AND system_person_type = p_sys_person_type
AND default_flag = 'Y';
SELECT ptu.person_type_usage_id,
ptu.object_version_number
FROM per_person_type_usages_f ptu,
per_person_types pt
WHERE ptu.person_id = p_person_id
AND pt.person_type_id = ptu.person_type_Id
AND pt.system_person_type = 'CWK'
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date;
select paf.assignment_id, paf.effective_start_date
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id +0 = p_business_group_id
and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
UPDATE per_all_assignments_f paf
SET paf.supervisor_id = NULL
WHERE paf.rowid = supervisor_rec.rowid;
UPDATE per_all_assignments_f paf
SET paf.recruiter_id = NULL
WHERE paf.rowid = recruiter_rec.rowid;
UPDATE per_all_vacancies pv
SET pv.recruiter_id = NULL
WHERE pv.rowid = vacancies_rec.rowid;
DELETE from pay_grade_rules_f pgr
WHERE pgr.rowid = assignment_rate_rec.rowid;
DELETE FROM hr_comments h
WHERE h.rowid = comments_rec.rowid;
UPDATE per_requisitions pr
SET pr.person_id = NULL
WHERE pr.rowid = requisition_rec.rowid;
DELETE FROM per_bookings pb
WHERE pb.rowid = events_rec.rowid;
DELETE FROM per_assignment_budget_values_f pab
WHERE pab.rowid = budget_rec.rowid;
DELETE FROM pay_personal_payment_methods ppm
WHERE ppm.rowid = payment_rec.rowid;
DELETE FROM per_spinal_point_placements_f spp
WHERE spp.assignment_id = assignment1_rec.assignment_id;
DELETE FROM pay_cost_allocations_f pca
WHERE pca.assignment_id = assignment1_rec.assignment_id;
DELETE FROM per_all_assignments_f paf
WHERE paf.rowid = assignment1_rec.rowid;
UPDATE per_applications pap
SET pap.date_end = NULL
WHERE pap.rowid = applicantions_rec.rowid;
DELETE FROM per_periods_of_placement
WHERE rowid = l_rowid;
UPDATE per_all_assignments_f paf
SET paf.effective_end_date = l_end_of_time
WHERE paf.rowid = assignment2_rec.rowid;
UPDATE per_person_type_usages_f
SET person_type_id = l_person_type_id
WHERE person_id = p_person_id
AND person_type_id= l_person_type_id1
AND p_date_start BETWEEN effective_start_date
AND effective_end_date;
UPDATE per_people_f -- 3194314
SET current_npw_flag = null
,effective_start_date = p_date_start -- in case DT udpates exist
WHERE rowid = person_rec.rowid;
DELETE FROM per_people_f
WHERE rowid = person_rec.rowid;
UPDATE per_people_f
SET npw_number = null,
effective_start_date = p_date_start, -- in case DT updates exist
current_npw_flag = null,
per_information7 = 'INCL'
WHERE rowid = person_rec.rowid;
DELETE FROM per_people_f
WHERE rowid = person_rec.rowid;
DELETE FROM per_people_f
WHERE rowid = person_rec.rowid;
UPDATE per_people_f
SET effective_end_date = l_end_of_time
WHERE rowid = new_person_rec.rowid;
hr_per_type_usage_internal.delete_person_type_usage
(p_validate => FALSE
,p_person_type_usage_id => l_person_type_usage_id
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_zap
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
SELECT pbg.legislation_code
FROM per_business_groups pbg
, per_people_f per
WHERE per.person_id = p_person_id
AND pbg.business_group_id = per.business_group_id;