[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select r.mx_dpnts_alwd_num,
r.no_mx_num_dfnd_flag,
r.dsgn_rqmt_id,
r.grp_rlshp_cd
from ben_dsgn_rqmt_f r
where ((r.pl_id = p_new_pl_id)
or
(r.oipl_id = p_new_oipl_id)
or
(r.opt_id = (select opt_id
from ben_oipl_f
where oipl_id = p_new_oipl_id
and p_effective_date between effective_start_date
and effective_end_date
and business_group_id = p_business_group_id)))
and r.dsgn_typ_cd = 'DPNT'
-- and r.grp_rlshp_cd is null
and r.business_group_id = p_business_group_id
and p_effective_date between r.effective_start_date
and r.effective_end_date;
select count(old.elig_cvrd_dpnt_id)
from ben_elig_cvrd_dpnt_f old,
ben_elig_dpnt new,
ben_per_in_ler pil,
ben_per_in_ler pil2
where old.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
and old.cvg_strt_dt is not null
and nvl(old.cvg_thru_dt, hr_api.g_eot) >= nvl(pil2.lf_evt_ocrd_dt - 1,
hr_api.g_eot)
and old.business_group_id = p_business_group_id
and p_effective_date between old.effective_start_date
and old.effective_end_date
and new.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
and new.business_group_id = p_business_group_id
and old.dpnt_person_id = new.dpnt_person_id
and pil.per_in_ler_id=old.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil2.per_in_ler_id=new.per_in_ler_id
and pil2.business_group_id=p_business_group_id
and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select old.elig_cvrd_dpnt_id old_dpnt,
new.elig_dpnt_id new_dpnt
from ben_elig_cvrd_dpnt_f old,
ben_elig_dpnt new,
per_contact_relationships pcr,
ben_per_in_ler pil,
ben_per_in_ler pil2
where old.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
and old.cvg_strt_dt is not null
and nvl(old.cvg_thru_dt, hr_api.g_eot) >= nvl(pil2.lf_evt_ocrd_dt - 1,
hr_api.g_eot)
and old.business_group_id = p_business_group_id
and p_effective_date between old.effective_start_date
and old.effective_end_date
and new.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
and new.business_group_id = p_business_group_id
and old.dpnt_person_id = new.dpnt_person_id
and new.dpnt_person_id = pcr.contact_person_id
and pcr.person_id = p_person_id
and pcr.personal_flag = 'Y' -- Bug 3137774
/*Bug 13408187 : To check whether the dependent is manually deenrolled:
Participant coverage continues, but dep coverage is end dated
and dependent is eligible to be covered for the electable choice that
he is previously enrolled.This will ensure end dated dependent coverages
will not be carry forwarded*/
and not exists(
select '1' from dual where
(
('Y' = (select 'Y' from dual where exists
(select '1' from ben_prtt_enrt_rslt_f pen1
where pen1.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
and pen1.prtt_enrt_rslt_stat_cd is NULL
and pen1.effective_end_date = hr_api.g_eot
and pen1.enrt_cvg_thru_dt = hr_api.g_eot)
)) --'Y'
and old.cvg_thru_dt <> hr_api.g_eot
and ('Y' = (select 'Y' from dual where exists
(select '1' from ben_elig_dpnt edp,
ben_elig_per_elctbl_chc epe
where edp.dpnt_person_id = old.dpnt_person_id
and edp.per_in_ler_id = new.per_in_ler_id
and epe.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
and epe.per_in_ler_id = new.per_in_ler_id
and epe.elig_per_elctbl_chc_id = edp.elig_per_elctbl_chc_id
)
)
)
)
) --not exists
/* Bug 13568008: If recycle dependents is called when enrollments are reinstated,
check whether there exists in the backup table for the deenrolled dependent coverage row
for the backedout per_in_ler_id and for the old prtt_enrt_rslt_id. If exists then do not carry forward
the dependent*/
/*Bug 14119948: When the Coverage code is 'Current can keep or Choose But Start New', prttt_enrt_rslt_id changes,
hence while reimstating the enrollments check which dependents should be carry forwarded and when defaults are applied during
reinstatement, skip the below cursor checks as above portion of the cursor takes care of which dependents should be carry forwarded*/
and ( (ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL and ben_lf_evt_clps_restore.g_dflt_during_reinstate = 'N'
and (
(l_old_prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
and ('1' = (select '1' from dual where
not exists
(select '1' from ben_le_clsn_n_rstr pdp_old
where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
and pdp_old.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
and pdp_old.business_group_id = p_business_group_id
and pdp_old.dpnt_person_id = old.dpnt_person_id
and pdp_old.cvg_thru_dt <> hr_api.g_eot
and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'))))
or (l_old_prtt_enrt_rslt_id <> p_new_prtt_enrt_rslt_id
and ('1' = (select '1' from dual where
exists
(select '1' from ben_le_clsn_n_rstr pdp_old
,ben_prtt_enrt_rslt_f pen5
where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
and pdp_old.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
and pdp_old.business_group_id = p_business_group_id
and pdp_old.dpnt_person_id = old.dpnt_person_id
and pdp_old.cvg_thru_dt = hr_api.g_eot
and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'
and pen5.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
and pen5.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
and pen5.business_group_id = p_business_group_id
and pen5.prtt_enrt_rslt_id = pdp_old.prtt_enrt_rslt_id
and pen5.pl_typ_id in (select pl_typ_id from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id)
union
select '1' from ben_elig_cvrd_dpnt_f pdp_old
,ben_prtt_enrt_rslt_f pen5
where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
and pdp_old.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
and pdp_old.business_group_id = p_business_group_id
and pdp_old.dpnt_person_id = old.dpnt_person_id
and pdp_old.cvg_thru_dt = hr_api.g_eot
and pen5.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
and pen5.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
and pen5.business_group_id = p_business_group_id
and pen5.prtt_enrt_rslt_id = pdp_old.prtt_enrt_rslt_id
and pen5.pl_typ_id in (select pl_typ_id from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id) )))
)
)
) or (ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL and ben_lf_evt_clps_restore.g_dflt_during_reinstate = 'Y')
or (ben_manage_life_events.g_bckdt_per_in_ler_id is NULL )
)
and
(pcr.contact_type in
(select c.rlshp_typ_cd
from ben_dsgn_rqmt_f p,
ben_dsgn_rqmt_rlshp_typ c
where p.dsgn_rqmt_id = c.dsgn_rqmt_id
and ((p.pl_id = p_new_pl_id)
or
(p.oipl_id = p_new_oipl_id)
or
(p.opt_id = (select opt_id
from ben_oipl_f
where oipl_id = p_new_oipl_id
and p_effective_date between effective_start_date
and effective_end_date
and business_group_id = p_business_group_id)))
and p.dsgn_typ_cd = 'DPNT'
and p.grp_rlshp_cd is not null
and p.business_group_id = p_business_group_id
and p_effective_date between p.effective_start_date
and p.effective_end_date
and nvl(p.mx_dpnts_alwd_num,999) >=
(select count('s')
from ben_elig_dpnt new2,
per_contact_relationships pcr2
where new2.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
and new2.business_group_id = p_business_group_id
and new2.dpnt_person_id in
-- Make sure that the dpnt being counted was covered before
(select dpnt_person_id
from ben_elig_cvrd_dpnt_f ecd
where prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
and cvg_strt_dt is not null
and nvl(cvg_thru_dt, hr_api.g_eot) >=
nvl(pil2.lf_evt_ocrd_dt - 1, hr_api.g_eot)
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date)
and new2.dpnt_person_id = pcr2.contact_person_id
and pcr2.person_id = p_person_id
and pcr2.contact_type in
(select rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ c2
where c2.dsgn_rqmt_id = p.dsgn_rqmt_id)))
or not exists
(select 's'
from ben_dsgn_rqmt_f p3
where p3.grp_rlshp_cd is not null
and ((p3.pl_id = p_new_pl_id)
or
(p3.oipl_id = p_new_oipl_id)
or
(p3.opt_id = (select opt_id
from ben_oipl_f
where oipl_id = p_new_oipl_id
and p_effective_date between effective_start_date
and effective_end_date
and business_group_id = p_business_group_id)))
and p3.dsgn_typ_cd = 'DPNT'
and p3.business_group_id = p_business_group_id
and p_effective_date between p3.effective_start_date
and p3.effective_end_date))
and pil.per_in_ler_id=old.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil2.per_in_ler_id=new.per_in_ler_id
and pil2.business_group_id=p_business_group_id
and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select ecd.elig_cvrd_dpnt_id,
ecd.dpnt_person_id,
ecd.elig_per_elctbl_chc_id,
ecd.object_version_number
from ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
where ecd.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and ecd.business_group_id = p_business_group_id
and p_effective_date between ecd.effective_start_date
and ecd.effective_end_date
and pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select pbn.*
from ben_pl_bnf_f pbn,
ben_per_in_ler pil
where pbn.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
and pbn.business_group_id = p_business_group_id
and p_effective_date between pbn.effective_start_date
and pbn.effective_end_date
and pil.per_in_ler_id=pbn.per_in_ler_id
and pil.business_group_id=pbn.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select count(old.elig_cvrd_dpnt_id)
from ben_elig_cvrd_dpnt_f old,
ben_elig_dpnt new,
ben_per_in_ler pil,
ben_per_in_ler pil2,
per_contact_relationships pcr
where old.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
and old.cvg_strt_dt is not null
and nvl(old.cvg_thru_dt, hr_api.g_eot) >= nvl(pil2.lf_evt_ocrd_dt - 1,
hr_api.g_eot)
and old.business_group_id = p_business_group_id
and p_effective_date between old.effective_start_date
and old.effective_end_date
and new.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
and new.business_group_id = p_business_group_id
and old.dpnt_person_id = new.dpnt_person_id
and pil.per_in_ler_id=old.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil2.per_in_ler_id=new.per_in_ler_id
and pil2.business_group_id=p_business_group_id
and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pcr.person_id = p_person_id
and pcr.contact_person_id =new.dpnt_person_id
and p_effective_date between nvl(pcr.date_start,p_effective_date)
and nvl(pcr.date_end,p_effective_date)
--- validate the no of dpnt for the grp
and ( pcr.contact_type in
( select drt.rlshp_typ_cd
from ben_dsgn_rqmt_f bdr ,
ben_dsgn_rqmt_rlshp_typ drt
where bdr.dsgn_rqmt_id = v_dsgn_rqmt_id
and drt.dsgn_rqmt_id = bdr.dsgn_rqmt_id
and ( bdr.grp_rlshp_cd = v_grp_rlshp_cd or
(bdr.grp_rlshp_cd is null and v_grp_rlshp_cd is null )
)
and p_effective_date between bdr.effective_start_date
and bdr.effective_end_date
)
--- if there is no relation typ defind take all
or
not exists
(select 'x' from ben_dsgn_rqmt_rlshp_typ drt
where drt.dsgn_rqmt_id = v_dsgn_rqmt_id
)
) ;
SELECT NULL
FROM ben_oipl_f oipl,
ben_opt_f opt
WHERE oipl.opt_id = opt.opt_id
AND oipl.oipl_id = p_new_oipl_id
AND p_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
AND p_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
AND NVL (opt.invk_wv_opt_flag, 'N') = 'Y'
UNION
SELECT NULL
FROM ben_pl_f pln
WHERE pln.pl_id = p_new_pl_id
AND p_effective_date BETWEEN pln.effective_start_date AND pln.effective_end_date
AND NVL (pln.invk_dcln_prtn_pl_flag, 'N') = 'Y';
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (c_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
order by pil.lf_evt_ocrd_dt desc;
SELECT old_pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f old_pen, ben_prtt_enrt_rslt_f new_pen
where old_pen.pgm_id = new_pen.pgm_id
-- and old_pen.pl_id = new_pen.pl_id
and old_pen.ptip_id = new_pen.ptip_id --Bug 14740884
-- and nvl(old_pen.oipl_id,1) = nvl(new_pen.oipl_id,1)
-- and old_pen.per_in_ler_id = new_pen.per_in_ler_id --Bug 14740884
and old_pen.per_in_ler_id = l_prev_pil_id --Bug 14740884
and new_pen.per_in_ler_id = p_per_in_ler_id
and new_pen.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
-- and old_pen.prtt_enrt_rslt_stat_cd = 'VOIDD' --Bug 14740884
and old_pen.prtt_enrt_rslt_stat_cd is null --Bug 14740884
and old_pen.sspndd_flag = 'N'
and new_pen.sspndd_flag = 'N' ;
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_validate => p_validate
,p_business_group_id => p_business_group_id
,p_elig_cvrd_dpnt_id => dpnt.elig_cvrd_dpnt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
,p_cvg_thru_dt => null
,p_per_in_ler_id => p_per_in_ler_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_request_id => fnd_global.conc_request_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate);
ben_plan_beneficiary_api.update_plan_beneficiary
(p_validate => p_validate
,p_pl_bnf_id => bnf.pl_bnf_id
,P_DSG_THRU_DT => null
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_per_in_ler_id => p_per_in_ler_id
,p_datetrack_mode => p_datetrack_mode
,p_request_id => fnd_global.conc_request_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate);
,p_program_update_date => sysdate
,p_object_version_number => l_bnf_object_version_number
,p_multi_row_actn => p_multi_row_actn
,p_effective_date => p_effective_date);
select dpnt_dsgn_lvl_cd
from ben_pgm_f
where pgm_id = p_pgm_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
select pgm.dpnt_dsgn_no_ctfn_rqd_flag
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date between pgm.effective_start_date
and pgm.effective_end_date;
select ptip.dpnt_cvg_no_ctfn_rqd_flag
from ben_ptip_f ptip
where ptip.ptip_id = (select ptip_id
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and business_group_id =p_business_group_id
and prtt_enrt_rslt_stat_cd is null
and p_effective_date between effective_start_date
and effective_end_date)
and ptip.business_group_id = p_business_group_id
and p_effective_date between
ptip.effective_start_date and ptip.effective_end_date;
select *
from ben_cvrd_dpnt_ctfn_prvdd_f
where elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
select pea.prtt_enrt_rslt_id,
pea.prtt_enrt_actn_id,
pea.actn_typ_id,
pea.object_version_number,
pea.effective_start_date,
pea.effective_end_date
from ben_prtt_enrt_actn_f pea,
ben_actn_typ eat
where pea.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
and pea.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
and pea.cmpltd_dt is not null
and eat.type_cd in ('DDCTFN')
and eat.actn_typ_id=pea.actn_typ_id
and p_effective_date
between pea.effective_start_date and pea.effective_end_date; -- bug 6793512
select ccp.cvrd_dpnt_ctfn_prvdd_id
,ccp.effective_start_date
,ccp.effective_end_date
,ccp.object_version_number
,ccp.prtt_enrt_actn_id
from ben_cvrd_dpnt_ctfn_prvdd_f ccp
where ccp.prtt_enrt_actn_id = ll_prtt_enrt_actn_id
and ccp.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
and ccp.business_group_id=p_business_group_id
and ccp.dpnt_dsgn_ctfn_recd_dt is not null
and p_effective_date between ccp.effective_start_date
and ccp.effective_end_date;
l_ccp_update_flag varchar2(30) := 'N'; --6613891
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
hr_utility.set_location('Update dpnt info'||l_proc, 20);
l_ccp_update_flag := 'N'; --6613891
hr_utility.set_location('In c_dpnt_pea. l_ccp_update_flag = '|| l_ccp_update_flag,8085);
l_ccp_update_flag := 'Y'; --6613891
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
l_datetrack_mode := hr_api.g_update_override;
elsif l_update then
l_datetrack_mode := hr_api.g_update;
ben_CVRD_DPNT_CTFN_PRVDD_api.update_CVRD_DPNT_CTFN_PRVDD
(
p_validate => FALSE
,p_cvrd_dpnt_ctfn_prvdd_id => l_ccp_rec.cvrd_dpnt_ctfn_prvdd_id
,p_effective_start_date => l_ccp_effective_start_date
,p_effective_end_date => l_ccp_effective_end_date
,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
,p_prtt_enrt_actn_id => l_ccp_rec.prtt_enrt_actn_id
,p_object_version_number => l_ccp_rec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
hr_utility.set_location('l_ccp_update_flag = ' || l_ccp_update_flag,8085);
if l_ccp_update_flag = 'Y' then
--
-- Bug 6793512
--
dt_api.find_dt_upd_modes
(p_effective_date => p_effective_date,
p_base_table_name => 'ben_prtt_enrt_actn_f',
p_base_key_column => 'prtt_enrt_actn_id',
p_base_key_value => l_dpnt_pea.prtt_enrt_actn_id,
p_correction => l_correction,
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
l_datetrack_mode := hr_api.g_update_override;
elsif l_update then
l_datetrack_mode := hr_api.g_update;
ben_PRTT_ENRT_ACTN_api.update_PRTT_ENRT_ACTN
(
p_validate => FALSE
,p_effective_start_date => l_pea_effective_start_date
,p_effective_end_date => l_pea_effective_end_date
,p_prtt_enrt_actn_id => l_dpnt_pea.prtt_enrt_actn_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
,p_object_version_number => l_dpnt_pea.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_rslt_object_version_number => l_pea_object_version_number
);
,p_program_update_date => sysdate);