[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 = p_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 = p_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
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 = p_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 = p_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 = p_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';
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);