[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(effective_end_date)
from per_all_assignments_f
where person_id = p_person_id
and application_id = p_application_id
and assignment_id <> p_assignment_id
and assignment_type = 'A' ;
select 1
from per_all_assignments_f a,
fnd_sessions f
where a.assignment_id = p_assignment_id
and f.session_id = userenv('sessionid')
and a.effective_start_date > f.effective_date ;
hr_utility.set_message ( 801, 'HR_6408_APPS_NO_UPDATE' ) ;
p_dt_update_mode in varchar2,
p_business_group_id in number ) is
--
--
procedure chk_assignment_current ( p_assignment_id in number ) is
l_asg_max_end_date date ;
select max(effective_end_date)
from per_all_assignments_f
where assignment_id = p_assignment_id ;
select f.effective_date
from per_people_f p,
fnd_sessions f
where p.person_id = p_person_id
and p.effective_start_date > f.effective_date
and f.session_id = userenv('sessionid') ;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select grade_structure,
people_group_structure,
job_structure,
position_structure
from per_business_groups
where business_group_id = p_business_group_id ;
p_dt_update_mode in varchar2,
p_business_group_id in number) is
begin
--
-- No changes are allowed if there are future assignment changes
chk_future_asg_changes ( p_assignment_id ) ;
p_dt_update_mode,
p_business_group_id) ;
procedure update_row ( p_rowid in varchar2,
p_application_id in number,
p_person_id in number,
p_assignment_id in number,
p_status_changed in boolean,
p_new_system_status in varchar2,
p_new_asg_status_type_id in number,
p_recruiter_id in number,
p_dt_update_mode in varchar2,
p_effective_date in date,
p_effective_start_date in date,
p_validation_start_date in date,
p_business_group_id in number ) is
--
l_end_row varchar2(5) := 'FALSE' ; -- Should the assignment row's
select nvl(max(a.effective_end_date),p_effective_date)
from per_all_assignments_f a
where a.person_id = p_person_id
and a.application_id = p_application_id
and a.assignment_id <> p_assignment_id
and a.assignment_type = 'A'
and p_effective_date between a.effective_start_date
and a.effective_end_date ;
delete from per_secondary_ass_statuses
where assignment_id = p_assignment_id
and start_date > p_effective_date ;
update per_secondary_ass_statuses
set end_date = p_effective_date
where assignment_id = p_assignment_id
and p_effective_date between start_date
and nvl(end_date,p_effective_date) ;
procedure delete_pending_letters ( p_assignment_id in number,
p_assignment_status_type_id in number,
p_business_group_id in number) is
begin
--
delete from per_letter_request_lines l
where l.assignment_id = p_assignment_id
and l.assignment_status_type_id <> p_assignment_status_type_id
and exists ( select 1
from per_letter_requests r
where r.letter_request_id = l.letter_request_id
and r.request_status = 'PENDING'
and r.auto_or_manual = 'AUTO' ) ;
message('DELETED '||to_char(sql%rowcount)||' ROWS FROM REQUEST LINES');
delete from per_letter_requests r
where r.business_group_id = p_business_group_id
and r.request_status = 'PENDING'
and r.auto_or_manual = 'AUTO'
and not exists ( select 1
from per_letter_request_lines l
where l.letter_request_id = r.letter_request_id
) ;
message('DELETED '||to_char(sql%rowcount)||' ROWS FROM LETTER REQUESTS');
end delete_pending_letters ;
procedure delete_events ( p_assignment_id in number,
p_effective_date in date ) is
begin
--
delete from per_bookings b
where b.event_id in ( select e.event_id
from per_events e
where e.assignment_id = p_assignment_id
and e.date_start > p_effective_date
) ;
delete from per_events e
where e.assignment_id = p_assignment_id
and e.date_start > p_effective_date ;
end delete_events ;
Select vacancy_id
From per_all_assignments_f
Where assignment_id = p_assignment_id
And p_effective_date between effective_start_date and effective_end_date;
delete_pending_letters ( p_assignment_id,
p_assignment_status_type_id,
p_business_group_id ) ;
p_dt_update_mode,
p_business_group_id);
update per_all_assignments_f
set assignment_status_type_id = decode( l_end_row,
'TRUE',
assignment_status_type_id,
p_new_asg_status_type_id ),
recruiter_id = p_recruiter_id,
effective_start_date = p_effective_start_date,
effective_end_date = decode(l_end_row,
'TRUE',
p_effective_date,
effective_end_date )
where rowid = p_rowid ;
update per_applications
set date_end = l_max_asg_end_date
where application_id = p_application_id ;
,p_datetrack_update_mode => 'UPDATE');
delete_events ( p_assignment_id,
p_effective_date ) ;
end update_row ;