[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date
,p_called_from_form in varchar2 default 'N') is
--
-- Declare cursors and local variables
--
l_ptnl_ler_for_per_id ben_ptnl_ler_for_per.ptnl_ler_for_per_id%TYPE;
select '1' from dual where exists
(select obj.ELIG_OBJ_ID from ben_elig_obj_f obj ,
ben_elig_obj_elig_profl_f obe,
ben_ler_f ler
where c_effective_date between obj.effective_start_date
and obj.effective_end_date
and c_effective_date between obe.effective_start_date
and obe.effective_end_date
and obj.table_name = 'BEN_LER_F'
and obj.column_name = 'LER_ID'
and obj.column_value = p_ler_id
and obj.business_group_id = p_business_group_id
and obe.business_group_id = p_business_group_id
and obj.ELIG_OBJ_ID = obe.ELIG_OBJ_ID
and ler.ler_id = to_number(obj.column_value)
and ler.business_group_id = p_business_group_id
and c_effective_date between ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
);
,p_program_update_date => trunc(p_program_update_date)
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date));
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
/* Inserted for nocopy changes */
p_ptnl_ler_for_per_id := null;
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date) is
--
-- Declare cursors and local variables
--
l_ptnl_ler_for_per_id ben_ptnl_ler_for_per.ptnl_ler_for_per_id%TYPE;
,p_program_update_date => trunc(p_program_update_date)
,p_effective_date => trunc(p_effective_date));
select ben_ptnl_ler_for_per_s.nextval
into l_ptnl_ler_for_per_id
from sys.dual;
insert into ben_ptnl_ler_for_per
(ptnl_ler_for_per_id
,csd_by_ptnl_ler_for_per_id
,lf_evt_ocrd_dt
,trgr_table_pk_id
,ptnl_ler_for_per_stat_cd
,ptnl_ler_for_per_src_cd
,mnl_dt
,enrt_perd_id
,ler_id
,person_id
,business_group_id
,dtctd_dt
,procd_dt
,unprocd_dt
,voidd_dt
,mnlo_dt
,ntfn_dt
,request_id
,program_application_id
,program_id
,program_update_date
,object_version_number)
values
(l_ptnl_ler_for_per_id
,p_csd_by_ptnl_ler_for_per_id
,trunc(p_lf_evt_ocrd_dt)
,p_trgr_table_pk_id
,p_ptnl_ler_for_per_stat_cd
,p_ptnl_ler_for_per_src_cd
,trunc(p_mnl_dt)
,p_enrt_perd_id
,p_ler_id
,p_person_id
,p_business_group_id
,trunc(p_dtctd_dt)
,trunc(p_procd_dt)
,trunc(p_unprocd_dt)
,trunc(p_voidd_dt)
,trunc(p_mnlo_dt)
,trunc(p_ntfn_dt)
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,l_object_version_number);
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date));
/* Inserted for nocopy changes */
p_ptnl_ler_for_per_id := null;
procedure update_ptnl_ler_for_per
(p_validate in boolean default false
,p_ptnl_ler_for_per_id in number
,p_csd_by_ptnl_ler_for_per_id in number default hr_api.g_number
,p_lf_evt_ocrd_dt in date default hr_api.g_date
,p_trgr_table_pk_id in number default hr_api.g_number
,p_ptnl_ler_for_per_stat_cd in varchar2 default hr_api.g_varchar2
,p_ptnl_ler_for_per_src_cd in varchar2 default hr_api.g_varchar2
,p_mnl_dt in date default hr_api.g_date
,p_enrt_perd_id in number default hr_api.g_number
,p_ler_id in number default hr_api.g_number
,p_person_id in number default hr_api.g_number
,p_business_group_id in number default hr_api.g_number
,p_dtctd_dt in date default hr_api.g_date
,p_procd_dt in date default hr_api.g_date
,p_unprocd_dt in date default hr_api.g_date
,p_voidd_dt in date default hr_api.g_date
,p_mnlo_dt in date default hr_api.g_date
,p_ntfn_dt in date default hr_api.g_date
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_effective_date in date) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ;
l_proc := g_package||'update_ptnl_ler_for_per';
savepoint update_ptnl_ler_for_per;
ben_ptnl_ler_for_per_bk2.update_ptnl_ler_for_per_b
(p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
,p_csd_by_ptnl_ler_for_per_id => p_csd_by_ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
,p_trgr_table_pk_id => p_trgr_table_pk_id
,p_ptnl_ler_for_per_stat_cd => p_ptnl_ler_for_per_stat_cd
,p_ptnl_ler_for_per_src_cd => p_ptnl_ler_for_per_src_cd
,p_mnl_dt => trunc(p_mnl_dt)
,p_enrt_perd_id => p_enrt_perd_id
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_dtctd_dt => trunc(p_dtctd_dt)
,p_procd_dt => trunc(p_procd_dt)
,p_unprocd_dt => trunc(p_unprocd_dt)
,p_voidd_dt => trunc(p_voidd_dt)
,p_mnlo_dt => trunc(p_mnlo_dt)
,p_ntfn_dt => trunc(p_ntfn_dt)
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date));
(p_module_name => 'UPDATE_ptnl_ler_for_per'
,p_hook_type => 'BP');
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date));
ben_ptnl_ler_for_per_bk2.update_ptnl_ler_for_per_a
(p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
,p_csd_by_ptnl_ler_for_per_id => p_csd_by_ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
,p_trgr_table_pk_id => p_trgr_table_pk_id
,p_ptnl_ler_for_per_stat_cd => p_ptnl_ler_for_per_stat_cd
,p_ptnl_ler_for_per_src_cd => p_ptnl_ler_for_per_src_cd
,p_mnl_dt => trunc(p_mnl_dt)
,p_enrt_perd_id => p_enrt_perd_id
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_dtctd_dt => trunc(p_dtctd_dt)
,p_procd_dt => trunc(p_procd_dt)
,p_unprocd_dt => trunc(p_unprocd_dt)
,p_voidd_dt => trunc(p_voidd_dt)
,p_mnlo_dt => trunc(p_mnlo_dt)
,p_ntfn_dt => trunc(p_ntfn_dt)
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date));
(p_module_name => 'UPDATE_ptnl_ler_for_per'
,p_hook_type => 'AP');
ROLLBACK TO update_ptnl_ler_for_per;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end update_ptnl_ler_for_per;
procedure update_ptnl_ler_for_per_perf
(p_validate in boolean default false
,p_ptnl_ler_for_per_id in number
,p_csd_by_ptnl_ler_for_per_id in number default hr_api.g_number
,p_lf_evt_ocrd_dt in date default hr_api.g_date
,p_trgr_table_pk_id in number default hr_api.g_number
,p_ptnl_ler_for_per_stat_cd in varchar2 default hr_api.g_varchar2
,p_ptnl_ler_for_per_src_cd in varchar2 default hr_api.g_varchar2
,p_mnl_dt in date default hr_api.g_date
,p_enrt_perd_id in number default hr_api.g_number
,p_ler_id in number default hr_api.g_number
,p_person_id in number default hr_api.g_number
,p_business_group_id in number default hr_api.g_number
,p_dtctd_dt in date default hr_api.g_date
,p_procd_dt in date default hr_api.g_date
,p_unprocd_dt in date default hr_api.g_date
,p_voidd_dt in date default hr_api.g_date
,p_mnlo_dt in date default hr_api.g_date
,p_ntfn_dt in date default hr_api.g_date
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_effective_date in date) is
--
-- Declare cursors and local variables
--
-- l_proc varchar2(72) := g_package||'update_ptnl_ler_for_per';
l_program_update_date date;
savepoint update_ptnl_ler_for_per;
ben_ptnl_ler_for_per_bk2.update_ptnl_ler_for_per_b
(p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
,p_csd_by_ptnl_ler_for_per_id => p_csd_by_ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
,p_trgr_table_pk_id => p_trgr_table_pk_id
,p_ptnl_ler_for_per_stat_cd => p_ptnl_ler_for_per_stat_cd
,p_ptnl_ler_for_per_src_cd => p_ptnl_ler_for_per_src_cd
,p_mnl_dt => trunc(p_mnl_dt)
,p_enrt_perd_id => p_enrt_perd_id
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_dtctd_dt => p_dtctd_dt
,p_procd_dt => trunc(p_procd_dt)
,p_unprocd_dt => trunc(p_unprocd_dt)
,p_voidd_dt => trunc(p_voidd_dt)
,p_mnlo_dt => trunc(p_mnlo_dt)
,p_ntfn_dt => trunc(p_ntfn_dt)
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date));
(p_module_name => 'UPDATE_ptnl_ler_for_per'
,p_hook_type => 'BP');
l_program_update_date := trunc(p_program_update_date);
If (l_program_update_date = hr_api.g_date) then
l_program_update_date :=
ben_ppl_shd.g_old_rec.program_update_date;
update ben_ptnl_ler_for_per
set lf_evt_ocrd_dt = l_lf_evt_ocrd_dt,
trgr_table_pk_id = l_trgr_table_pk_id,
csd_by_ptnl_ler_for_per_id = l_csd_by_ptnl_ler_for_per_id,
ptnl_ler_for_per_stat_cd = l_ptnl_ler_for_per_stat_cd,
ptnl_ler_for_per_src_cd = l_ptnl_ler_for_per_src_cd,
mnl_dt = l_mnl_dt,
enrt_perd_id = l_enrt_perd_id,
ler_id = l_ler_id,
person_id = l_person_id,
business_group_id = l_business_group_id,
dtctd_dt = l_dtctd_dt,
procd_dt = l_procd_dt,
unprocd_dt = l_unprocd_dt,
voidd_dt = l_voidd_dt,
mnlo_dt = l_mnlo_dt,
ntfn_dt = l_ntfn_dt,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = l_program_update_date,
object_version_number = l_object_version_number
where ptnl_ler_for_per_id = p_ptnl_ler_for_per_id;
ben_ptnl_ler_for_per_bk2.update_ptnl_ler_for_per_a
(p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
,p_csd_by_ptnl_ler_for_per_id => p_csd_by_ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
,p_trgr_table_pk_id => p_trgr_table_pk_id
,p_ptnl_ler_for_per_stat_cd => p_ptnl_ler_for_per_stat_cd
,p_ptnl_ler_for_per_src_cd => p_ptnl_ler_for_per_src_cd
,p_mnl_dt => trunc(p_mnl_dt)
,p_enrt_perd_id => p_enrt_perd_id
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_dtctd_dt => trunc(p_dtctd_dt)
,p_procd_dt => trunc(p_procd_dt)
,p_unprocd_dt => trunc(p_unprocd_dt)
,p_voidd_dt => trunc(p_voidd_dt)
,p_mnlo_dt => trunc(p_mnlo_dt)
,p_ntfn_dt => trunc(p_ntfn_dt)
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => trunc(p_program_update_date)
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date));
(p_module_name => 'UPDATE_ptnl_ler_for_per'
,p_hook_type => 'AP');
ROLLBACK TO update_ptnl_ler_for_per;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end update_ptnl_ler_for_per_perf;
procedure delete_ptnl_ler_for_per
(p_validate in boolean default false
,p_ptnl_ler_for_per_id in number
,p_object_version_number in out nocopy number
,p_effective_date in date) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
l_proc := g_package||'update_ptnl_ler_for_per';
savepoint delete_ptnl_ler_for_per;
ben_ptnl_ler_for_per_bk3.delete_ptnl_ler_for_per_b
(p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date));
(p_module_name => 'DELETE_ptnl_ler_for_per'
,p_hook_type => 'BP');
ben_ptnl_ler_for_per_bk3.delete_ptnl_ler_for_per_a
(p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date));
(p_module_name => 'DELETE_ptnl_ler_for_per'
,p_hook_type => 'AP');
ROLLBACK TO delete_ptnl_ler_for_per;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end delete_ptnl_ler_for_per;