[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.3 26-Jun-07 mkommuri bug6138732 updated cursor is at
ben_pep_cache2.write_pilepo_cache cursor
c_pilinstance
115.4 02-Jul-07 mkommuri same as 115.3. updated this hisotry
-----------------------------------------------------------------------------
*/
--
-- Globals.
--
g_package varchar2(50) := 'ben_pep_cache2.';
select pil.per_in_ler_id
from ben_per_in_ler pil
where pil.person_id = c_person_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
order by pil.per_in_ler_id desc;
select pep.per_in_ler_id
from ben_elig_per_f pep
where pep.per_in_ler_id = c_pil_id
and c_eff_date
between pep.effective_start_date and pep.effective_end_date
and rownum=1;
select epo.per_in_ler_id
from ben_elig_per_opt_f epo
where epo.per_in_ler_id = c_pil_id
and c_eff_date
between epo.effective_start_date and epo.effective_end_date
and rownum=1;
SELECT tab1.los_val,
tab1.age_val,
tab1.comp_ref_amt,
tab1.hrs_wkd_val,
tab1.pct_fl_tm_val,
tab1.cmbn_age_n_los_val,
tab1.age_uom,
tab1.los_uom,
tab1.comp_ref_uom,
tab1.hrs_wkd_bndry_perd_cd,
tab1.frz_los_flag,
tab1.frz_age_flag,
tab1.frz_hrs_wkd_flag,
tab1.frz_cmp_lvl_flag,
tab1.frz_pct_fl_tm_flag,
tab1.frz_comb_age_and_los_flag,
tab1.rt_los_val,
tab1.rt_age_val,
tab1.rt_comp_ref_amt,
tab1.rt_hrs_wkd_val,
tab1.rt_pct_fl_tm_val,
tab1.rt_cmbn_age_n_los_val,
tab1.rt_age_uom,
tab1.rt_los_uom,
tab1.rt_comp_ref_uom,
tab1.rt_hrs_wkd_bndry_perd_cd,
tab1.rt_frz_los_flag,
tab1.rt_frz_age_flag,
tab1.rt_frz_hrs_wkd_flag,
tab1.rt_frz_cmp_lvl_flag,
tab1.rt_frz_pct_fl_tm_flag,
tab1.rt_frz_comb_age_and_los_flag,
tab1.ovrid_svc_dt,
tab1.prtn_ovridn_flag,
tab1.prtn_ovridn_thru_dt,
tab1.once_r_cntug_cd,
tab1.elig_flag,
tab1.pgm_id,
tab1.ptip_id,
tab1.pl_id,
tab1.plip_id,
tab1.prtn_strt_dt,
tab1.prtn_end_dt,
tab1.object_version_number,
tab1.elig_per_id,
tab1.per_in_ler_id
from ben_elig_per_f tab1
where tab1.per_in_ler_id = c_pil_id
and c_effective_date
between tab1.effective_start_date and tab1.effective_end_date
order by tab1.pgm_id, tab1.pl_id, tab1.plip_id;
SELECT tab1.los_val,
tab1.age_val,
tab1.comp_ref_amt,
tab1.hrs_wkd_val,
tab1.pct_fl_tm_val,
tab1.cmbn_age_n_los_val,
tab1.age_uom,
tab1.los_uom,
tab1.comp_ref_uom,
tab1.hrs_wkd_bndry_perd_cd,
tab1.frz_los_flag,
tab1.frz_age_flag,
tab1.frz_hrs_wkd_flag,
tab1.frz_cmp_lvl_flag,
tab1.frz_pct_fl_tm_flag,
tab1.frz_comb_age_and_los_flag,
tab1.rt_los_val,
tab1.rt_age_val,
tab1.rt_comp_ref_amt,
tab1.rt_hrs_wkd_val,
tab1.rt_pct_fl_tm_val,
tab1.rt_cmbn_age_n_los_val,
tab1.rt_age_uom,
tab1.rt_los_uom,
tab1.rt_comp_ref_uom,
tab1.rt_hrs_wkd_bndry_perd_cd,
tab1.rt_frz_los_flag,
tab1.rt_frz_age_flag,
tab1.rt_frz_hrs_wkd_flag,
tab1.rt_frz_cmp_lvl_flag,
tab1.rt_frz_pct_fl_tm_flag,
tab1.rt_frz_comb_age_and_los_flag,
tab1.ovrid_svc_dt,
tab1.prtn_ovridn_flag,
tab1.prtn_ovridn_thru_dt,
tab1.once_r_cntug_cd,
tab1.elig_flag,
tab1.pgm_id,
tab1.ptip_id,
tab1.pl_id,
tab1.plip_id,
tab1.prtn_strt_dt,
tab1.prtn_end_dt,
tab1.object_version_number,
tab1.elig_per_id,
tab1.per_in_ler_id
from ben_elig_per_f tab1
where tab1.per_in_ler_id is null
and tab1.person_id = c_person_id
and c_effective_date
between tab1.effective_start_date and tab1.effective_end_date
order by tab1.pgm_id, tab1.pl_id, tab1.plip_id;
SELECT tab1.los_val
,tab1.age_val
,tab1.comp_ref_amt
,tab1.hrs_wkd_val
,tab1.pct_fl_tm_val
,tab1.cmbn_age_n_los_val
,tab1.age_uom
,tab1.los_uom
,tab1.comp_ref_uom
,tab1.hrs_wkd_bndry_perd_cd
,tab1.frz_los_flag
,tab1.frz_age_flag
,tab1.frz_hrs_wkd_flag
,tab1.frz_cmp_lvl_flag
,tab1.frz_pct_fl_tm_flag
,tab1.frz_comb_age_and_los_flag
,tab1.rt_los_val
,tab1.rt_age_val
,tab1.rt_comp_ref_amt
,tab1.rt_hrs_wkd_val
,tab1.rt_pct_fl_tm_val
,tab1.rt_cmbn_age_n_los_val
,tab1.rt_age_uom
,tab1.rt_los_uom
,tab1.rt_comp_ref_uom
,tab1.rt_hrs_wkd_bndry_perd_cd
,tab1.rt_frz_los_flag
,tab1.rt_frz_age_flag
,tab1.rt_frz_hrs_wkd_flag
,tab1.rt_frz_cmp_lvl_flag
,tab1.rt_frz_pct_fl_tm_flag
,tab1.rt_frz_comb_age_and_los_flag
,tab1.ovrid_svc_dt
,tab1.prtn_ovridn_flag
,tab1.prtn_ovridn_thru_dt
,tab1.once_r_cntug_cd
,tab1.elig_flag
,tab2.pgm_id
,tab2.ptip_id
,tab2.pl_id
,tab2.plip_id
,tab1.opt_id
,tab1.prtn_strt_dt
,tab1.prtn_end_dt
,tab1.elig_per_opt_id
,tab1.object_version_number
,tab2.elig_per_id
,tab2.per_in_ler_id
,tab2.prtn_strt_dt pep_psd
,tab2.prtn_end_dt pep_ped
,tab3.per_in_ler_stat_cd
from ben_elig_per_opt_f tab1,
ben_elig_per_f tab2,
ben_per_in_ler tab3
where tab1.opt_id is not null
and tab1.elig_per_id = tab2.elig_per_id
and tab3.per_in_ler_id = tab1.per_in_ler_id
and c_effective_date
between tab1.effective_start_date and tab1.effective_end_date
and c_effective_date
between tab2.effective_start_date and tab2.effective_end_date
and tab1.per_in_ler_id = c_pil_id
order by tab1.opt_id, tab2.pl_id, tab2.pgm_id;
SELECT tab1.los_val
,tab1.age_val
,tab1.comp_ref_amt
,tab1.hrs_wkd_val
,tab1.pct_fl_tm_val
,tab1.cmbn_age_n_los_val
,tab1.age_uom
,tab1.los_uom
,tab1.comp_ref_uom
,tab1.hrs_wkd_bndry_perd_cd
,tab1.frz_los_flag
,tab1.frz_age_flag
,tab1.frz_hrs_wkd_flag
,tab1.frz_cmp_lvl_flag
,tab1.frz_pct_fl_tm_flag
,tab1.frz_comb_age_and_los_flag
,tab1.rt_los_val
,tab1.rt_age_val
,tab1.rt_comp_ref_amt
,tab1.rt_hrs_wkd_val
,tab1.rt_pct_fl_tm_val
,tab1.rt_cmbn_age_n_los_val
,tab1.rt_age_uom
,tab1.rt_los_uom
,tab1.rt_comp_ref_uom
,tab1.rt_hrs_wkd_bndry_perd_cd
,tab1.rt_frz_los_flag
,tab1.rt_frz_age_flag
,tab1.rt_frz_hrs_wkd_flag
,tab1.rt_frz_cmp_lvl_flag
,tab1.rt_frz_pct_fl_tm_flag
,tab1.rt_frz_comb_age_and_los_flag
,tab1.ovrid_svc_dt
,tab1.prtn_ovridn_flag
,tab1.prtn_ovridn_thru_dt
,tab1.once_r_cntug_cd
,tab1.elig_flag
,tab2.pgm_id
,tab2.ptip_id
,tab2.pl_id
,tab2.plip_id
,tab1.opt_id
,tab1.prtn_strt_dt
,tab1.prtn_end_dt
,tab1.elig_per_opt_id
,tab1.object_version_number
,tab2.elig_per_id
,tab2.per_in_ler_id
,tab2.prtn_strt_dt pep_psd
,tab2.prtn_end_dt pep_ped
from ben_elig_per_opt_f tab1,
ben_elig_per_f tab2
where tab2.per_in_ler_id is null
and tab2.person_id = c_person_id
and tab1.opt_id is not null
and tab1.elig_per_id = tab2.elig_per_id
and c_effective_date
between tab1.effective_start_date and tab1.effective_end_date
and c_effective_date
between tab2.effective_start_date and tab2.effective_end_date
order by tab1.opt_id, tab2.pl_id, tab2.pgm_id;