[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-Jun-02 pabodla 115.46 Do not select the contingent worker
assignment when assignment data is
fetched.
10-Oct-03 kmahendr 115.49 Performance fix - check_age_elig moved
down and check_contact_elig moved up.
13-aug-04 tjesumic 115.50 fonm changes
07-Feb-05 kmahendr 115.51 Bug#4157836 - the ineligible date on account
of contact type based on person table reversed
01-Apr-05 swjain 115.52 Bug 4271143 -- Added check to calculate apld_dpnt_cvg_elig_rl
since if Coverage Eligibility Rule is specified, then it should also
evaluate to true alongwith eligibility profile. Also added one more cursor c_ade1
in procedure main.
09-May-05 kmahendr 115.53 Bug#4318031 - added cursor contact2 and added
codes to check_contact_elig
07-Jun-05 kmahendr 115.54 Bug#4399894-added c_previous_per to check
previous value
13-jun-05 kmahendr 115.55 Fixes for other eligibility viz military,
student and marital
19-aug-05 ssarkar 115.56 bug 4546890 : set_elig_change_dt is called for rule evaluation of eligibility.
and modified proc set_elig_change_dt for FONM case.
24-Oct-07 swjain 115.57 Bug 6520270: Made changes in set_elig_change_dt
14-Nov-07 swjain 115.58 Bug 6615978: Updated procedure check_age_elig. Ineligibility date is calculated
as per the profile value set now.
14-jan-08 rtagarra 115.59 Bug 6738429: Added prtt_enrt_rslt_stat_cd clause in
Cursor c_get_elig_cvrd_dpnt for the issue in contact person
covered in other plan.
06-mar-08 bachakra 115.61 Bug 6870564: Corrected the prtt_enrt_rslt_stat_cd clause in cursor
c_get_prtt_enrt_rslt in the procedure
check_dsgntr_enrld_cvg_elig
22-APR-08 stee 115.62 Bug 6956648: Change cursor c_contact in check_contact_elig
to only check contact for the person.
*/
-----------------------------------------------------------------------
--
-- Global to track eligibility change date
--
g_elig_change_dt date;
select dpnt_dsgn_lvl_cd,
dpnt_dsgn_cd,
dpnt_cvg_strt_dt_cd,
dpnt_cvg_strt_dt_rl,
dpnt_cvg_end_dt_cd,
dpnt_cvg_end_dt_rl
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and c_effective_date
between pgm.effective_start_date
and pgm.effective_end_date;
select contact_person_id,
contact_relationship_id,
contact_type,
personal_flag,
rltd_per_rsds_w_dsgntr_flag,
person_id,
date_end
from per_contact_relationships ctr
where ctr.contact_relationship_id = p_contact_relationship_id
and c_effective_date >=
nvl(date_start,c_effective_date);
select contact_type
from per_contact_relationships ctr
where ctr.contact_relationship_id <> p_contact_relationship_id
and ctr.contact_person_id = p_contact_person_id
and ctr.personal_flag = 'Y'
and p_effective_date between nvl(ctr.date_start,p_effective_date)
and nvl(ctr.date_end,p_effective_date);
select null
from ben_dsgn_rqmt_f ddr
where (ddr.oipl_id = p_oipl_id
or ddr.pl_id = p_pl_id
or ddr.opt_id = (select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and oipl.business_group_id =
p_business_group_id
and c_effective_date
between oipl.effective_start_date
and oipl.effective_end_date))
and ddr.dsgn_typ_cd = 'DPNT'
and ddr.business_group_id = p_business_group_id
and c_effective_date
between ddr.effective_start_date
and ddr.effective_end_date;
select null
from ben_dsgn_rqmt_f ddr, ben_dsgn_rqmt_rlshp_typ rl
where ddr.dsgn_rqmt_id = rl.dsgn_rqmt_id(+)
and rl.rlshp_typ_cd = p_contact_type
and (ddr.oipl_id = p_oipl_id
or ddr.pl_id = p_pl_id
or ddr.opt_id = (select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and oipl.business_group_id =
p_business_group_id
and c_effective_date
between oipl.effective_start_date
and oipl.effective_end_date))
and ddr.dsgn_typ_cd = 'DPNT'
-- Commented and added new clause for Bug fix - 1859111
-- and (nvl(ddr.mn_dpnts_rqd_num,-1) <> 0
-- and nvl(ddr.mx_dpnts_alwd_num,-1) <> 0)
and ( (nvl(ddr.mn_dpnts_rqd_num,-1) = -1 and ddr.no_mn_num_dfnd_flag = 'Y')
or ( ddr.mn_dpnts_rqd_num is not null and ddr.no_mn_num_dfnd_flag = 'N') )
and ( (nvl(ddr.mx_dpnts_alwd_num,-1) = -1 and ddr.no_mx_num_dfnd_flag = 'Y')
or ( ddr.mx_dpnts_alwd_num <> 0 and ddr.no_mx_num_dfnd_flag = 'N') )
-- End of Bug fix - 1859111
and ddr.business_group_id = p_business_group_id
and c_effective_date
between ddr.effective_start_date
and ddr.effective_end_date;
select null
from ben_dsgn_rqmt_f ddr
where ddr.grp_rlshp_cd is null
and (ddr.oipl_id = p_oipl_id
or ddr.pl_id = p_pl_id
or ddr.opt_id = (select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and oipl.business_group_id =
p_business_group_id
and c_effective_date
between oipl.effective_start_date
and oipl.effective_end_date))
and ddr.dsgn_typ_cd = 'DPNT'
-- Commented and added new clause for Bug fix - 1859111
-- and (nvl(ddr.mn_dpnts_rqd_num,-1) <> 0
-- and nvl(ddr.mx_dpnts_alwd_num,-1) <> 0)
and ( (nvl(ddr.mn_dpnts_rqd_num,-1) = -1 and ddr.no_mn_num_dfnd_flag = 'Y')
or ( ddr.mn_dpnts_rqd_num is not null and ddr.no_mn_num_dfnd_flag = 'N') )
and ( (nvl(ddr.mx_dpnts_alwd_num,-1) = -1 and ddr.no_mx_num_dfnd_flag = 'Y')
or ( ddr.mx_dpnts_alwd_num <> 0 and ddr.no_mx_num_dfnd_flag = 'N') )
-- End of Bug fix - 1859111
and ddr.business_group_id = p_business_group_id
and c_effective_date
between ddr.effective_start_date
and ddr.effective_end_date;
select null
from ben_dsgn_rqmt_f ddr,
ben_dsgn_rqmt_rlshp_typ rl
where ddr.dsgn_rqmt_id = rl.dsgn_rqmt_id(+)
and ddr.grp_rlshp_cd is not null
and rl.rlshp_typ_cd <> l_contact.contact_type
and (ddr.oipl_id = p_oipl_id
or ddr.pl_id = p_pl_id
or ddr.opt_id = (select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and oipl.business_group_id =
p_business_group_id
and c_effective_date
between oipl.effective_start_date
and oipl.effective_end_date))
and ddr.dsgn_typ_cd = 'DPNT'
and ddr.business_group_id = p_business_group_id
and c_effective_date
between ddr.effective_start_date
and ddr.effective_end_date;
select ptip.ptip_id
from ben_ptip_f ptip, ben_pl_f pl
where ptip.pl_typ_id = pl.pl_typ_id
and ptip.pgm_id = p_pgm_id
and pl.pl_id = p_pl_id
and c_effective_date
between ptip.effective_start_date
and ptip.effective_end_date
and c_effective_date
between pl.effective_start_date
and pl.effective_end_date;
select ade.dpnt_cvg_eligy_prfl_id,
ade.mndtry_flag,
ade.apld_dpnt_cvg_elig_rl, -- Bug No 4271143
dce.dpnt_cvg_elig_det_rl,
dce.dpnt_rlshp_flag,
dce.dpnt_age_flag,
dce.dpnt_stud_flag,
dce.dpnt_dsbld_flag,
dce.dpnt_mrtl_flag,
dce.dpnt_mltry_flag,
dce.dpnt_pstl_flag,
dce.dpnt_cvrd_in_anthr_pl_flag,
dce.dpnt_dsgnt_crntly_enrld_flag
from ben_apld_dpnt_cvg_elig_prfl_f ade,
ben_dpnt_cvg_eligy_prfl_f dce
where decode(c_lvl,
'PL',c_pl_id,
'PTIP',c_ptip_id,
'PGM', c_pgm_id) =
decode(c_lvl,
'PL',ade.pl_id,
'PTIP',ade.ptip_id,
'PGM', ade.pgm_id)
and c_effective_date
between ade.effective_start_date
and ade.effective_end_date
and dce.dpnt_cvg_eligy_prfl_id = ade.dpnt_cvg_eligy_prfl_id
and dce.dpnt_cvg_eligy_prfl_stat_cd = 'A'
and c_effective_date
between dce.effective_start_date
and dce.effective_end_date
order by decode(ade.mndtry_flag,'Y',1,2);
select ade.dpnt_cvg_eligy_prfl_id,
ade.mndtry_flag,
ade.apld_dpnt_cvg_elig_rl
from ben_apld_dpnt_cvg_elig_prfl_f ade
where decode(c_lvl,
'PL',c_pl_id,
'PTIP',c_ptip_id,
'PGM', c_pgm_id) =
decode(c_lvl,
'PL',ade.pl_id,
'PTIP',ade.ptip_id,
'PGM', ade.pgm_id)
and c_effective_date
between ade.effective_start_date
and ade.effective_end_date
and ade.dpnt_cvg_eligy_prfl_id is null
and ade.apld_dpnt_cvg_elig_rl is not null
order by decode(ade.mndtry_flag,'Y',1,2);
select marital_status,
on_military_service,
student_status,
registered_disabled_flag,
effective_start_date
from per_all_people_f per
where per.person_id = p_contact_person_id
and c_effective_date
between per.effective_start_date
and per.effective_end_date;
select marital_status,
on_military_service,
student_status,
registered_disabled_flag
from per_all_people_f per
where per.person_id = p_contact_person_id
and p_effective_date
between per.effective_start_date
and per.effective_end_date;
select addr.postal_code,
addr.date_from
from per_addresses addr
where addr.person_id = p_contact_person_id
and addr.primary_flag = 'Y'
and (c_effective_date >= addr.date_from
or addr.date_from is null)
and (c_effective_date <= addr.date_to
or addr.date_to is null);
select addr.postal_code,
addr.date_from
from per_addresses addr
where addr.person_id = l_contact.person_id
and addr.primary_flag = 'Y'
and (c_effective_date >= addr.date_from
or addr.date_from is null)
and (c_effective_date <= addr.date_to
or addr.date_to is null);
select pl.pl_typ_id
from ben_pl_f pl
where pl.pl_id = p_pl_id
and c_effective_date
between pl.effective_start_date
and pl.effective_end_date;
select pil.ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id;
select asg.assignment_id,asg.organization_id,loc.region_2
from per_all_assignments_f asg,hr_locations_all loc
where asg.person_id = l_contact.person_id
and asg.assignment_type <> 'C'
and asg.location_id = loc.location_id(+)
and asg.primary_flag='Y'
and c_effective_date
between asg.effective_start_date
and asg.effective_end_date;
select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and c_effective_date
between oipl.effective_start_date
and oipl.effective_end_date;
select date_of_birth
from per_all_people_f per
where per.person_id = p_person_id
and per.business_group_id = p_business_group_id
and l_effective_date
between per.effective_start_date
and per.effective_end_date;
select agf.mx_age_num,
agf.mn_age_num,
agf.age_uom,
agf.no_mn_age_flag,
agf.no_mx_age_flag,
eac.excld_flag,
eac.age_fctr_id
from ben_age_fctr agf,
ben_elig_age_cvg_f eac
where agf.age_fctr_id = eac.age_fctr_id
and agf.business_group_id = p_business_group_id
and eac.dpnt_cvg_eligy_prfl_id = p_eligy_prfl_id
and eac.business_group_id = p_business_group_id
and l_effective_date
between eac.effective_start_date
and eac.effective_end_date;
select mrtl_stat_cd
from ben_elig_mrtl_stat_cvg_f ems
where ems.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
and ems.business_group_id = l_bg_id
and l_effective_date
between ems.effective_start_date
and ems.effective_end_date;
select mltry_stat_cd
from ben_elig_mltry_stat_cvg_f emc
where emc.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
and emc.business_group_id = l_bg_id
and l_effective_date
between emc.effective_start_date
and emc.effective_end_date;
select stdnt_stat_cd
from ben_elig_stdnt_stat_cvg_f esc
where esc.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
and esc.business_group_id = l_bg_id
and l_effective_date
between esc.effective_start_date
and esc.effective_end_date;
select per_relshp_typ_cd
from ben_dpnt_cvg_rqd_rlshp_f dcr
where dcr.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
and dcr.business_group_id = l_bg_id
and l_effective_date
between dcr.effective_start_date
and dcr.effective_end_date;
select null
from per_contact_relationships ctr
where ctr.contact_type = p_contact_type
and ctr.contact_person_id = p_person_id
and ctr.person_id = p_contact_person_id -- Bug 6956648
and ctr.personal_flag = 'Y'
and p_effective_date between nvl(ctr.date_start,p_effective_date)
and nvl(ctr.date_end,p_effective_date);
select dsbld_cd
from ben_elig_dsbld_stat_cvg_f edc
where edc.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
and edc.business_group_id = l_bg_id
and l_effective_date
between edc.effective_start_date
and edc.effective_end_date;
select rzr.from_value,
rzr.to_value,
epl.excld_flag
from ben_pstl_zip_rng_f rzr,
ben_elig_pstl_cd_r_rng_cvg_f epl
where rzr.pstl_zip_rng_id = epl.pstl_zip_rng_id
and epl.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
and epl.business_group_id = l_bg_id
and rzr.business_group_id = l_bg_id
and l_effective_date
between epl.effective_start_date
and epl.effective_end_date
and l_effective_date
between rzr.effective_start_date
and rzr.effective_end_date
order by epl.ordr_num;
select dpc.pl_id,dpc.cvg_det_dt_cd, dpc.excld_flag
from ben_dpnt_cvrd_anthr_pl_cvg_f dpc
where dpc.dpnt_cvg_eligy_prfl_id = p_eligy_prfl_id
and dpc.business_group_id = p_business_group_id
and l_effective_date
between dpc.effective_start_date
and dpc.effective_end_date;
select null
from ben_elig_cvrd_dpnt_f pdp
,ben_prtt_enrt_rslt_f pen
where pdp.dpnt_person_id = p_person_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pl_id = p_pl_id
and l_cvg_det_dt
between pdp.cvg_strt_dt
and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
and pdp.cvg_strt_dt is not null
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pdp.effective_start_date
and pdp.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pen.effective_start_date
and pen.effective_end_date
;
select dec.dsgntr_crntly_enrld_flag
from ben_dsgntr_enrld_cvg_f dec
where dec.dpnt_cvg_eligy_prfl_id = p_eligy_prfl_id
and dec.business_group_id = p_business_group_id
and l_effective_date
between dec.effective_start_date
and dec.effective_end_date;
select null
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_dsgntr_id
and pen.pgm_id = p_pgm_id
--and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_stat_cd is null
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date;