[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select bpl.bnft_prvdd_ldgr_id, bpl.acty_base_rt_id, bpl.prtt_enrt_rslt_id,
bpl.business_group_id, bpl.effective_start_date, bpl.effective_end_date,
bpl.object_version_number, bpl.per_in_ler_id,
bpl.frftd_val, bpl.used_val, bpl.prvdd_val, bpl.cash_recd_val, bpl.rld_up_val
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil,
ben_prtt_enrt_rslt_f pen
where (bpl.acty_ref_perd_cd is null or
(bpl.used_val is not null and bpl.cmcd_used_val is null))
and bpl.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and bpl.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and bpl.effective_start_date between pen.effective_start_date
and pen.effective_end_date ;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_ldgr.bnft_prvdd_ldgr_id
,p_effective_start_date => l_ldgr.effective_start_date
,p_effective_end_date => l_ldgr.effective_end_date
,p_acty_ref_perd_cd => l_acty_ref_perd_cd
,p_cmcd_frftd_val => l_cmcd_frftd_val
,p_cmcd_prvdd_val => l_cmcd_prvdd_val
,p_cmcd_rld_up_val => l_cmcd_rld_up_val
,p_cmcd_used_val => l_cmcd_used_val
,p_cmcd_cash_recd_val => l_cmcd_cash_recd_val
,p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd
,p_ann_frftd_val => l_ann_frftd_val
,p_ann_prvdd_val => l_ann_prvdd_val
,p_ann_rld_up_val => l_ann_rld_up_val
,p_ann_used_val => l_ann_used_val
,p_ann_cash_recd_val => l_ann_cash_recd_val
,p_object_version_number => l_ldgr.object_version_number
,p_effective_date => l_ldgr.effective_start_date
,p_datetrack_mode => 'CORRECTION');
hr_utility.set_location('Updated Ledger. Id='||to_char(l_ldgr.bnft_prvdd_ldgr_id),24);
select bpl.acty_base_rt_id, bpl.prtt_enrt_rslt_id,
bpl.business_group_id, bpl.per_in_ler_id
from ben_bnft_prvdd_ldgr_f bpl
where bpl.bnft_prvdd_ldgr_id = c_bnft_prvdd_ldgr_id
and c_effective_date between
bpl.effective_start_date and bpl.effective_end_date;
select distinct pen.person_id, pen.pgm_id,
pgm.acty_ref_perd_cd, pgm.enrt_info_rt_freq_cd
from ben_prtt_enrt_rslt_f pen, ben_pgm_f pgm
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.pgm_id = pgm.pgm_id
and pen.prtt_enrt_rslt_stat_cd is null
and c_effective_date between
pen.effective_start_date and pen.effective_end_date
and c_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select distinct asg.payroll_id
from per_all_assignments_f asg
where asg.person_id = c_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and c_effective_date between
asg.effective_start_date and asg.effective_end_date;
select distinct ann_rt_val, cmcd_rt_val
from ben_prtt_rt_val prv, ben_prtt_enrt_rslt_f pen
where prv.acty_base_rt_id = c_acty_base_rt_id
-- make sure we're dealing with the exact same rate
and prv.rt_val = c_used_val
and prv.acty_ref_perd_cd = c_acty_ref_perd_cd
and prv.cmcd_ref_perd_cd = c_enrt_info_rt_freq_cd
-- make sure the rate is for our person
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.person_id = c_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and c_effective_date between
pen.effective_start_date and pen.effective_end_date;
select distinct ann_rt_val, cmcd_rt_val
from ben_prtt_rt_val prv
where prv.rt_val = c_prvdd_val
and prv.acty_ref_perd_cd = c_acty_ref_perd_cd
and prv.cmcd_ref_perd_cd = c_enrt_info_rt_freq_cd
and prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id;
select distinct ann_val, cmcd_val
from ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel
where ecr.acty_base_rt_id = c_acty_base_rt_id
-- make sure we're dealing with the exact same rate
and ecr.val = c_prvdd_val
and pel.acty_ref_perd_cd = c_acty_ref_perd_cd
and ecr.cmcd_acty_ref_perd_cd = c_enrt_info_rt_freq_cd
-- make sure the rate is for our person
and ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = c_per_in_ler_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.person_id = c_person_id;
select distinct epe.elig_per_elctbl_chc_id, ecr.enrt_rt_id, pil.lf_evt_ocrd_dt
from ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel, ben_enrt_bnft enb
where ecr.acty_base_rt_id = c_acty_base_rt_id
-- make sure we're dealing with the exact same rate
and pel.acty_ref_perd_cd = c_acty_ref_perd_cd
and ecr.cmcd_acty_ref_perd_cd = c_enrt_info_rt_freq_cd
-- make sure the rate is for our person
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and (epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id or
enb.enrt_bnft_id = ecr.enrt_bnft_id)
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = c_per_in_ler_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.person_id = c_person_id;
select ecr.enrt_rt_id, abr.acty_base_rt_id, abr.element_type_id,
epe.elig_per_elctbl_chc_id , pil.lf_evt_ocrd_dt
from ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
ben_acty_base_rt_f abr
where epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
and epe.pgm_id = c_pgm_id
and epe.comp_lvl_cd = 'PLANFC'
and pil.per_in_ler_id = epe.per_in_ler_id
and epe.per_in_ler_id = c_per_in_ler_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id;
is select rate_periodization_rl
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = cv_acty_base_rt_id
and p_effective_start_date between abr.effective_start_date
and abr.effective_end_date ;
l_update boolean := FALSE;
l_update_override boolean := FALSE;
l_update_change_insert boolean := FALSE;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override or l_update_change_insert then
p_mode := 'UPDATE_OVERRIDE';