[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'X'
into v_dummy
from sys.dual
where exists (select 'Assignments Exist'
from per_assignments_f paf
where paf.supervisor_id = p_person_id
-- Code changes for Bug#13869378 Starts here
and (paf.business_group_id + 0 = p_business_group_id
OR NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and paf.assignment_type in ('E','C')
and p_session_date between paf.effective_start_date and paf.effective_end_date
and not exists (select 1
from per_assignment_status_types past
where past.assignment_status_type_id = paf.assignment_status_type_id
and past.per_system_status = 'TERM_ASSIGN'
and (past.business_group_id = paf.business_group_id
OR past.business_group_id is NULL)));
and exists (select null from
per_periods_of_service pds where pds.PERIOD_OF_SERVICE_ID =paf.PERIOD_OF_SERVICE_ID
and (pds.ACTUAL_TERMINATION_DATE is not null and pds.ACTUAL_TERMINATION_DATE >= p_session_date)));*/
select 'X'
into v_dummy
from sys.dual
where exists ( 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 = 'E'
and pb.person_id = p_person_id
and pe.date_start > p_session_date
);
select 'X'
into v_dummy
from sys.dual
where exists(select 'Interview rows exist'
from per_events pe
where pe.business_group_id + 0 = p_business_group_id
and pe.event_or_interview = 'I'
and pe.internal_contact_person_id = p_person_id
and pe.date_start > p_session_date
)
OR
exists(select 'Interview rows 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 = 'I'
and pb.person_id = p_person_id
and pe.date_start > p_session_date
);
select 'X'
into v_dummy
from sys.dual
where exists ( select 'Perf Review rows exist'
from per_performance_reviews ppr
where ppr.person_id = p_person_id
and review_date > p_session_date
);
select 'X'
into v_dummy
from sys.dual
where exists (select 'Recruiter for vacancy'
from per_vacancies pv
where
-- Fix for Bug 3446782 starts here. this condition is taken
-- care in view definition.
/*(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 nvl(pv.date_to, p_session_date) >= p_session_date);
select null
from per_all_people_f per
where per.person_id = l_person_id
and per.effective_start_date > l_actual_termination_date;
select null
from per_person_type_usages_f ptu, per_person_types ppt
where ptu.person_id = l_person_id
and ptu.effective_start_date > l_actual_termination_date
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type in ('EMP','CWK');
select per.person_type_id, per.object_version_number, per.effective_start_date
,per.applicant_number, per.employee_number, per.npw_number
from per_all_people_f per, per_person_types ppt
where per.person_id = l_person_id
and per.effective_start_date > l_actual_termination_date + 1
and per.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP','EMP_APL');
select effective_start_date, person_type_id
from per_all_people_f
where person_id = l_person_id
and effective_start_date =
(select min(effective_start_date)
from per_all_people_f
where person_id = l_person_id
and effective_start_date > l_actual_termination_date);
select asg.assignment_id
, asg.object_version_number
from per_assignments_f asg
where asg.period_of_service_id = p_period_of_service_id
and l_actual_termination_date + 1 between asg.effective_start_date
and asg.effective_end_date
order by asg.primary_flag;
select bus.business_group_id
, bus.legislation_code
, per.person_id
, per.employee_number
, per.effective_start_date
, per.object_version_number
, pet.system_person_type
, per.npw_number
, pds.actual_termination_date
, pds.last_standard_process_date
from per_all_people_f per
, per_business_groups bus
, per_periods_of_service pds
, per_person_types pet
where pds.period_of_service_id = p_period_of_service_id
and bus.business_group_id = pds.business_group_id
and per.person_id = pds.person_id
and l_actual_termination_date between per.effective_start_date
and per.effective_end_date
and pet.person_type_id = per.person_type_id;
select max(tpe.end_date)
from per_time_periods tpe
,per_assignments_f asg
where asg.period_of_service_id = p_period_of_service_id
and l_actual_termination_date between asg.effective_start_date
and asg.effective_end_date
and asg.payroll_id is not null
and tpe.payroll_id = asg.payroll_id
and l_actual_termination_date between tpe.start_date
and tpe.end_date;
select date_of_death
from per_all_people_f
where person_id = l_person_id;
select *
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
select contract_id
,reference
,type
,object_version_number
from per_contracts_f
where person_id = l_person_id;*/
select * from hr_person_deployments where
FROM_BUSINESS_GROUP_ID = p_src_bg_id and
FROM_PERSON_ID = p_gold_src_person_id;
select * from hr_person_deployments where
TO_BUSINESS_GROUP_ID = p_dest_bg_id and
TO_PERSON_ID = p_gold_dest_person_id;
select person_id from per_periods_of_service
where PERIOD_OF_SERVICE_ID = p_period_of_service_id;
PROCEDURE delete_events(p_person_id IN NUMBER,p_pds_id number ,p_term_date date) IS
---- this procedure is written after discussing with Talent Mgmt team
cursor csr_del_events is
select event_id,object_version_number
from per_events
where assignment_id in ( select distinct (assignment_id )
from per_all_assignments_f where person_id=p_person_id and period_of_service_id =p_pds_id
and assignment_type='E')
AND date_start > p_term_date;
DELETE from per_bookings
WHERE event_id = csr_events.event_id;
per_events_api.delete_event(
p_event_id => csr_events.event_id ,
p_object_version_number => csr_events.object_version_number);
PROCEDURE delete_perf(p_person_id IN NUMBER,p_term_date date) IS
cursor csr_perfs(p_person_id NUMBER, p_term_date1 DATE) is
select pe.event_id,pe.object_version_number pe_ovn, pr.performance_review_id, pr.object_version_number
FROM per_events pe, per_performance_reviews pr
WHERE pr.person_id = p_person_id
AND pr.event_id = pe.event_id
AND pr.review_date > p_term_date1
ORDER BY performance_review_id,event_id;
hr_perf_review_api.delete_perf_review(
p_performance_review_id => csr_perf.performance_review_id,
p_object_version_number => l_perf_obj_number);
DELETE from per_bookings
WHERE event_id = l_prev_event;
per_events_api.delete_event(
p_event_id =>l_prev_event,
p_object_version_number =>l_prev_ovn);
DELETE from per_bookings
WHERE event_id = l_prev_event;
per_events_api.delete_event(
p_event_id =>l_prev_event,
p_object_version_number =>l_prev_ovn);
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
hr_utility.set_location(l_proc, 170);
-- to CORRECTION or UPDATE_CHANGE_INSERT
-- based on the effective_start_date of the first future record
if l_future_per_rows = 'Y' then
hr_utility.set_location(l_proc, 282);
l_datetrack_mode := hr_api.g_update_change_insert;
l_datetrack_mode := 'UPDATE';
-- All updates are done in correction mode on the start date of the row
-- EMP records are updated to EX_EMP
-- EMP_APL records are updated to EX_EMP_APL
for per in fut_person_rec
loop
hr_utility.set_location('Updating PER table data starting - '||per.effective_start_date,333);
select hrl.lookup_code into l_ctc_status
from hr_lookups hrl
where hrl.lookup_type = 'CONTRACT_STATUS'
and hrl.lookup_code = 'T-TERMINATION'
and hrl.application_id = 800 AND hrl.enabled_flag = 'Y';
,p_datetrack_mode => 'UPDATE'
);
delete_perf(l_person_id ,l_actual_termination_date );
delete_events(l_person_id,p_period_of_service_id,l_actual_termination_date);
,p_datetrack_update_mode => 'UPDATE'
);
select bus.legislation_code
, pds.actual_termination_date
, pds.last_standard_process_date
, pds.person_id
, pds.object_version_number
from per_business_groups bus
, per_periods_of_service pds
where pds.period_of_service_id = p_period_of_service_id
and bus.business_group_id = pds.business_group_id;
select asg.assignment_id
, asg.object_version_number
, asg.primary_flag
from per_all_assignments_f asg
where asg.period_of_service_id = p_period_of_service_id
and l_final_process_date between asg.effective_start_date
and asg.effective_end_date
and exists (
select 'X'
from per_all_assignments_f a1
where asg.assignment_id = a1.assignment_id
and l_final_process_date+1 between a1.effective_start_date
and a1.effective_end_date)
order by asg.primary_flag;
select null
from per_all_people_f per
, per_person_type_usages_f ptu
, per_person_types pet
where per.person_id = l_person_id
and l_exemppet_eff_date between per.effective_start_date
and per.effective_end_date
and per.person_id = ptu.person_id
and l_exemppet_eff_date between ptu.effective_start_date
and ptu.effective_end_date
and pet.person_type_id = ptu.person_type_id
and pet.system_person_type = 'EX_EMP';
select *
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
procedure update_term_details_emp
(p_validate in boolean default false
,p_effective_date in date
,p_period_of_service_id in number
,p_object_version_number in out nocopy number
,p_termination_accepted_person in number default hr_api.g_number
,p_accepted_termination_date in date default hr_api.g_date
,p_comments in varchar2 default hr_api.g_varchar2
,p_leaving_reason in varchar2 default hr_api.g_varchar2
,p_notified_termination_date in date default hr_api.g_date
,p_projected_termination_date in date default hr_api.g_date
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_term_details_emp';
savepoint update_term_details_emp;
hr_ex_employee_bk3.update_term_details_emp_b
(p_effective_date => p_effective_date
,p_period_of_service_id => p_period_of_service_id
,p_object_version_number => p_object_version_number
,p_termination_accepted_person => p_termination_accepted_person
,p_accepted_termination_date => p_accepted_termination_date
,p_comments => p_comments
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
);
(p_module_name => 'UPDATE_TERM_DETAILS_EMP',
p_hook_type => 'BP'
);
hr_ex_employee_bk3.update_term_details_emp_a
(p_effective_date => p_effective_date
,p_period_of_service_id => p_period_of_service_id
,p_object_version_number => p_object_version_number
,p_termination_accepted_person => p_termination_accepted_person
,p_accepted_termination_date => p_accepted_termination_date
,p_comments => p_comments
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
);
(p_module_name => 'UPDATE_TERM_DETAILS_EMP',
p_hook_type => 'AP'
);
ROLLBACK TO update_term_details_emp;
ROLLBACK TO update_term_details_emp;
end update_term_details_emp;
SELECT 'Y'
INTO l_action_chk
FROM dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_type NOT IN ('X','BEE')
AND pac.effective_date > nvl(p_fpr_date,hr_api.g_eot));
SELECT 'W'
INTO l_action_chk
FROM dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_type <> 'BEE'
AND pac.action_status = 'C'
AND ( (p_fpr_date is null
AND pac.effective_date >= l_action_date)
OR (p_fpr_date is not null
AND (pac.effective_date >= l_action_date
AND pac.effective_date <= p_fpr_date))));