[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select papf.person_id,papf.object_version_number,papf.business_group_id,paaf.object_version_number
from per_all_people_f papf, per_all_assignments_f paaf
where paaf.assignment_id = l_apl_asg_id
and l_effective_date between nvl(paaf.effective_start_date,l_effective_date) and nvl(paaf.effective_end_date,l_effective_date)
and l_effective_date between nvl(papf.effective_start_date,l_effective_date) and nvl(papf.effective_end_date,l_effective_date)
and papf.person_id = paaf.person_id;
select assignment_status_type_id
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
select assignment_status_type_id
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'TERM_APL'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y'
and (business_group_id is null or
business_group_id = bg_id);
select 1
from per_all_assignments_f
where person_id = l_person_id
and l_effective_date +1 between nvl(effective_start_date,l_effective_date)
and nvl(effective_end_date,l_effective_date)
and assignment_id <> l_apl_asg_id
and assignment_type = 'A';
select max(effective_end_date)
from per_all_assignments_f
where person_id = l_person_id
and assignment_id <> l_apl_asg_id
and assignment_type = 'A';
select src_apl_asg_id
from per_vac_linked_assignments
where tgt_apl_asg_id = l_tgt_assignment_id
and l_effective_date between trunc(nvl(start_date,l_effective_date)) and trunc(nvl(end_date,l_effective_date)) ;
select tgt_apl_asg_id
from per_vac_linked_assignments
where src_apl_asg_id = l_src_assignment_id
and l_effective_date between trunc(nvl(start_date,l_effective_date)) and trunc(nvl(end_date,l_effective_date))
and tgt_apl_asg_id <> apl_assignment_id;
select src_apl_asg_id
from per_vac_linked_assignments
where tgt_apl_asg_id = l_tgt_assignment_id;
select SRC_APL_ASG_ID
from PER_VAC_LINKED_ASSIGNMENTS
where TGT_APL_ASG_ID = apl_assignment_id;
select TGT_APL_ASG_ID
from PER_VAC_LINKED_ASSIGNMENTS
where SRC_APL_ASG_ID = apl_assignment_id;
select papf.person_id, papf.application_id, papf.business_group_id, papf.effective_end_date
from per_all_assignments_f papf
where papf.assignment_id = apl_assignment_id;
select ptu.effective_start_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = l_person_id
and ptu.effective_start_date > l_date_received
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EX_APL'
order by ptu.effective_start_date;
select assignment_status_type_id
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y'
and (business_group_id is null or
business_group_id = bg_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 = apl_assignment_id
and iass.assignment_status_type_id = l_asg_status_type_id);
select 1
from per_all_assignments_f
where person_id = l_person_id
and l_effective_date + 1 between nvl(effective_start_date,l_effective_date)
and nvl(effective_end_date,l_effective_date) -- added 1 to the date for bug#12593632 fix
and assignment_id <> l_apl_asg_id
and assignment_type = 'A'
and business_group_id =l_apl_bg_id;-- added new
select distinct business_group_id into l_apl_bg_id
from per_all_assignments_f
where assignment_id = apl_assignment_id;
select max(effective_end_date) into l_asg_end_date
from per_all_assignments_f
where assignment_id = apl_assignment_id;
per_app_asg_pkg.pre_delete_validation
( p_business_group_id => l_business_group_id,
p_assignment_id => apl_assignment_id,
p_application_id => l_application_id,
p_person_id => l_person_id,
p_session_date => l_date_end - 1 , --this should be close date - 1
p_validation_start_date => l_date_end + 1, --This would be close date + 1
p_validation_end_date => l_end_of_time, --This is end of time
p_delete_mode => 'DELETE_NEXT_CHANGE',
p_new_end_date => l_new_end_date ) ; -- This parameter need to check once
delete_row1
(p_assignment_id => apl_assignment_id,
p_new_end_date => null,
p_effective_end_date => l_date_end , -- this is close date
p_validation_end_date => l_end_of_time, -- this is end of time
p_session_date => l_date_end - 1, -- this should be close date - 1
p_delete_mode => 'DELETE_NEXT_CHANGE') ;
select legislation_code into l_legislation_code
from per_business_groups
where business_group_id in
(select business_group_id
from per_all_assignments_f
where assignment_id = apl_assignment_id);
P_last_updated_by => fnd_global.user_id(),
P_last_update_login => fnd_global.login_id(),
P_end_of_time => l_end_of_time);
p_last_updated_by => fnd_global.user_id(),
p_last_update_login => fnd_global.login_id(),
p_person_id => l_person_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);
per_applications_pkg.cancel_update_assigns_obg(
p_person_id => l_person_id,
p_business_group_id => l_business_group_id,
P_date_end => l_date_end,
P_application_id => l_application_id,
p_legislation_code => l_legislation_code,
P_end_of_time => l_end_of_time,
P_last_updated_by => fnd_global.user_id(),
p_last_update_login => fnd_global.login_id(),
p_assignment_id => apl_assignment_id);
UPDATE PER_APPLICATIONS
SET date_end = null
where application_id = l_application_id;
select date_received into l_date_received
from per_applications
where application_id = l_application_id;
update per_assignments_f a
set a.effective_end_date = p_new_end_date
where a.assignment_id = p_assignment_id
and a.effective_end_date = (
select max(a2.effective_end_date)
from per_assignments_f a2
where a2.assignment_id = a.assignment_id);
procedure delete_child ( p_assignment_id in number,
p_delete_mode in varchar2) is
p_del_flag VARCHAR2(1) := 'N';
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and p_delete_mode = 'ZAP');
p_delete_mode = 'ZAP' THEN
Delete per_assignment_budget_values_f
where assignment_id = p_assignment_id;
END delete_child;
procedure delete_row1(p_assignment_id number,
p_new_end_date date,
p_effective_end_date date,
p_validation_end_date date,
p_session_date date,
p_delete_mode varchar2 ) is
l_cost_warning boolean;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row1' , 5 ) ;
hr_utility.set_location('p_delete_mode :'|| p_delete_mode, 200);
delete_child ( p_assignment_id ,p_delete_mode);
delete from per_assignments_f a
where assignment_id = p_assignment_id;
update per_all_assignments_f
set effective_end_date = hr_api.g_eot -- to_date('31-Dec-4712','DD-MON-YYYY')
where assignment_id = p_assignment_id;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row1' , 15 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row1' , 20 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 25 ) ;
end delete_row1;