[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select se.effective_date
from fnd_sessions se
where se.session_id = USERENV('sessionid');
cursor sumselect
(c_per_id number
,c_eff_date date
)
is
SELECT /*+ ben_sum.sumselect */
pep.pgm_id order_num1,
-1 order_num2,
-1 order_num3,
pgm.name name,
pep.pgm_id id,
pep.business_group_id business_group_id,
pep.person_id person_id,
pep.elig_flag yn_lookcd,
'PGM' type
,
pgm.pgm_typ_cd object_type_cd
from ben_elig_per_f pep,
ben_per_in_ler pil,
ben_pgm_f pgm
WHERE pep.person_id = c_per_id
and pep.pgm_id = pgm.pgm_id
and pep.pl_id is null
/*
and pep.plip_id is null
and pep.ptip_id is null
*/
and c_eff_date
between pgm.effective_start_date and pgm.effective_end_date
and c_eff_date
between pep.effective_start_date and pep.effective_end_date
and pil.per_in_ler_id (+)= pep.per_in_ler_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
union
select pep.pgm_id order_num1,
pep.pl_id order_num2,
-1 order_num3,
' ' || pl.name name,
pep.pl_id id ,
pep.business_group_id business_group_id ,
pep.person_id person_id ,
pep.elig_flag yn_lookcd,
'PL' type
,
pl.svgs_pl_flag object_type_cd
from ben_elig_per_f pep ,
ben_per_in_ler pil,
ben_pl_f pl
WHERE pep.person_id = c_per_id
and pep.pl_id = pl.pl_id
and pep.pgm_id is not null
and pep.pl_id is not null
and c_eff_date
between pep.effective_start_date and pep.effective_end_date
and c_eff_date
between pl.effective_start_date and pl.effective_end_date
and exists
(select null
from ben_plip_f
where pl_id = pl.pl_id
)
and pil.per_in_ler_id (+)= pep.per_in_ler_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
union
select pep.pgm_id order_num1 ,
pep.pl_id order_num2 ,
pio.opt_id order_num3 ,
' ' || opt.name name ,
pio.opt_id id ,
pep.business_group_id business_group_id ,
pep.person_id person_id ,
pio.elig_flag yn_lookcd,
'OPT' type
,
null object_type_cd
from ben_elig_per_opt_f pio ,
ben_elig_per_f pep ,
ben_per_in_ler pil,
ben_pl_f pl ,
ben_opt_f opt
WHERE pep.person_id = c_per_id
and pio.elig_per_id = pep.elig_per_id
and pio.opt_id = opt.opt_id
and pep.pl_id = pl.pl_id
and pep.pgm_id is not null
and pep.pl_id is not null
and c_eff_date
between pep.effective_start_date and pep.effective_end_date
and c_eff_date
between pio.effective_start_date and pio.effective_end_date
and c_eff_date
between opt.effective_start_date and opt.effective_end_date
and c_eff_date
between pl.effective_start_date and pl.effective_end_date
and exists
(select null from ben_plip_f where pl_id = pl.pl_id)
and pil.per_in_ler_id (+)= pio.per_in_ler_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
union
select 9999999999999999999999999999999 order_num1 ,
pep.pl_id order_num2 ,
-1 order_num3 ,
pl.name name ,
pl.pl_id id ,
pep.business_group_id business_group_id ,
pep.person_id person_id ,
pep.elig_flag yn_lookcd,
'PL' type
,
pl.svgs_pl_flag object_type_cd
from ben_elig_per_f pep ,
ben_per_in_ler pil,
ben_pl_f pl
WHERE pep.person_id = c_per_id
and pep.pl_id = pl.pl_id
and pep.pgm_id is null
and c_eff_date
between pep.effective_start_date and pep.effective_end_date
and c_eff_date
between pl.effective_start_date and pl.effective_end_date
and not exists
(select null from ben_plip_f
where pl_id = pl.pl_id
)
and pil.per_in_ler_id (+)= pep.per_in_ler_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
union
select 9999999999999999999999999999999 order_num1 ,
pep.pl_id order_num2 ,
pio.opt_id order_num3 ,
' ' || opt.name name ,
pio.opt_id id ,
pep.business_group_id business_group_id ,
pep.person_id person_id ,
pio.elig_flag yn_lookcd,
'OPT' type
,
null object_type_cd
from ben_elig_per_opt_f pio ,
ben_elig_per_f pep ,
ben_per_in_ler pil,
ben_pl_f pl ,
ben_opt_f opt
WHERE pep.person_id = c_per_id
and pio.elig_per_id = pep.elig_per_id
and pio.opt_id = opt.opt_id
and pep.pl_id = pl.pl_id
and pep.pgm_id is null
and pep.pl_id is not null
and c_eff_date
between pep.effective_start_date and pep.effective_end_date
and c_eff_date
between pio.effective_start_date and pio.effective_end_date
and c_eff_date
between opt.effective_start_date and opt.effective_end_date
and c_eff_date
between pl.effective_start_date and pl.effective_end_date
and not exists
(select null from ben_plip_f
where pl_id = pl.pl_id
)
and pil.per_in_ler_id (+)= pio.per_in_ler_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
order by order_num1,
order_num2,
order_num3;
open sumselect
(c_per_id => p_person_id
,c_eff_date => l_eff_date
);
fetch sumselect BULK COLLECT INTO l_onum1_va,
l_onum2_va,
l_onum3_va,
l_name_va,
l_id_va,
l_bgpid_va,
l_perid_va,
l_yn_lookcd_va,
l_type_va,
l_otypecd_va;
close sumselect;