[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
and paf.business_group_id + 0 = p_business_group_id
and p_session_date <= paf.effective_end_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 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;
l_datetrack_mode := 'UPDATE';
,p_datetrack_mode => 'UPDATE'
,p_date_of_death => l_date_of_death
,p_validate => p_validate
,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_npw_number => l_npw_number
);
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'
);
,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))));