[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from ben_elig_cvrd_dpnt_f ecd
,ben_per_in_ler pil
where ecd.dpnt_person_id = p_dpnt_person_id
and ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ecd.elig_cvrd_dpnt_id <> nvl(p_elig_cvrd_dpnt_id, hr_api.g_number)
and ecd.business_group_id = p_business_group_id
and p_cvg_strt_dt between ecd.cvg_strt_dt and ecd.cvg_thru_dt
and ecd.cvg_thru_dt <= ecd.effective_end_date
and ecd.per_in_ler_id = pil.per_in_ler_id(+)
and nvl(pil.per_in_ler_stat_cd, 'A') not in ('VOIDD', 'BCKDT')
--and p_validation_start_date <= effective_end_date
--and p_validation_end_date >= effective_start_date
;
select null
from per_all_people_f a
where a.person_id = p_dpnt_person_id
and a.business_group_id + 0 = p_business_group_id
and p_validation_start_date <= effective_end_date
and p_validation_end_date >= effective_start_date
;
select null
from ben_prtt_enrt_rslt_f a
where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and a.prtt_enrt_rslt_stat_cd is null
and a.business_group_id + 0 = p_business_group_id
and p_validation_start_date <= effective_end_date
and p_validation_end_date >= effective_start_date
;
select r.person_id
,r.pl_id
,r.oipl_id
,o.opt_id
from ben_prtt_enrt_rslt_f r ,
ben_oipl_f o
where r.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and r.prtt_enrt_rslt_stat_cd is null
and r.business_group_id + 0 = p_business_group_id
and p_effective_date between r.effective_start_date
and r.effective_end_date
and o.oipl_id(+) = r.oipl_id
and o.business_group_id(+)= p_business_group_id
and p_effective_date between o.effective_start_date(+)
and o.effective_end_date(+)
;
select c.contact_type
from per_contact_relationships c
where c.person_id = l_person_id
and c.contact_person_id = p_dpnt_person_id
-- bug 1762932 added personal_flag
and nvl(c.personal_flag,'N') = 'Y'
and c.business_group_id + 0 = p_business_group_id
and p_effective_date between nvl(c.date_start, p_effective_date)
and nvl(c.date_end, p_effective_date)
;
select mx_dpnts_alwd_num
,no_mx_num_dfnd_flag
,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
from ben_dsgn_rqmt_f
where
((nvl(pl_id, hr_api.g_number) = l_pl_id)
or (nvl(oipl_id, hr_api.g_number) = l_oipl_id)
or (nvl(opt_id, hr_api.g_number) = l_opt_id))
and dsgn_typ_cd = 'DPNT'
and grp_rlshp_cd is null
and business_group_id + 0 = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
order by heir
;
select 's'
from ben_dsgn_rqmt_f r
where ((nvl(pl_id, hr_api.g_number) = l_pl_id)
or (nvl(oipl_id, hr_api.g_number) = l_oipl_id)
or (nvl(opt_id, hr_api.g_number) = l_opt_id))
and r.dsgn_typ_cd = 'DPNT'
and r.business_group_id + 0 = p_business_group_id
and p_effective_date between nvl(r.effective_start_date, p_effective_date)
and nvl(r.effective_end_date, p_effective_date)
;
select r.mx_dpnts_alwd_num
,r.no_mx_num_dfnd_flag
,r.dsgn_rqmt_id
,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
from ben_dsgn_rqmt_f r,
ben_dsgn_rqmt_rlshp_typ dr
where ((nvl(pl_id, hr_api.g_number) = l_pl_id)
or (nvl(oipl_id, hr_api.g_number) = l_oipl_id)
or (nvl(opt_id, hr_api.g_number) = l_opt_id))
and r.dsgn_typ_cd = 'DPNT'
and r.business_group_id + 0 = p_business_group_id
and p_effective_date between nvl(r.effective_start_date, p_effective_date)
and nvl(r.effective_end_date, p_effective_date)
and dr.dsgn_rqmt_id = r.dsgn_rqmt_id
and dr.rlshp_typ_cd = l_contact_type
order by heir
;
select count(elig_cvrd_dpnt_id)
from ben_elig_cvrd_dpnt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and cvg_strt_dt is not null
and cvg_thru_dt = hr_api.g_eot
-- and cvrd_flag = 'Y'
and business_group_id + 0 = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
and p_cvg_strt_dt <= nvl(cvg_thru_dt, hr_api.g_date)
and nvl(p_cvg_thru_dt, hr_api.g_date) >= cvg_strt_dt
;
select count(*)
from per_contact_relationships c
, ben_elig_cvrd_dpnt_f d
where
c.person_id = l_person_id
and c.contact_person_id = d.dpnt_person_id
and d.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and d.cvg_strt_dt is not null
and d.cvg_thru_dt = hr_api.g_eot
and p_cvg_strt_dt <= nvl(d.cvg_thru_dt, hr_api.g_date)
and nvl(p_cvg_thru_dt, hr_api.g_date) >= d.cvg_strt_dt
and c.business_group_id + 0 = p_business_group_id
and p_effective_date between nvl(c.date_start, p_effective_date)
and nvl(c.date_end, p_effective_date)
and d.effective_end_date = hr_api.g_eot -- bug 1237204
and d.business_group_id + 0 = p_business_group_id
and c.contact_type in
(select rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ
where dsgn_rqmt_id = l_dsgn_rqmt_id)
;
select null
from ben_crt_ordr crt,
ben_crt_ordr_cvrd_per crc
where crc.person_id = p_dpnt_person_id
and crc.business_group_id = p_business_group_id
and crc.crt_ordr_id = crt.crt_ordr_id;
SELECT person_id, pgm_id, pl_id, ptip_id, pl_typ_id,
enrt_cvg_strt_dt, enrt_cvg_thru_dt, per_in_ler_id
FROM ben_prtt_enrt_rslt_f rslt
WHERE rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and rslt.prtt_enrt_rslt_stat_cd is null
AND rslt.business_group_id = p_business_group_id
AND p_effective_date BETWEEN rslt.effective_start_date
AND rslt.effective_end_date;
SELECT dpnt_dsgn_lvl_cd, dpnt_dsgn_cd, pgm_typ_cd
FROM ben_pgm_f pgm
WHERE pgm.pgm_id = rslt_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 pl.dpnt_dsgn_cd
FROM ben_pl_f pl
WHERE pl.pl_id = rslt_pl_id
AND pl.business_group_id = p_business_group_id
AND p_effective_date BETWEEN pl.effective_start_date
AND pl.effective_end_date;
SELECT ptip.dpnt_dsgn_cd
FROM ben_ptip_f ptip
WHERE ptip.ptip_id = rslt_ptip_id
AND ptip.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ptip.effective_start_date
AND ptip.effective_end_date;
SELECT pl.alws_qmcso_flag, pl.alws_qdro_flag, pl.pl_typ_id
FROM ben_pl_f pl
WHERE pl.pl_id = rslt_pl_id
AND pl.business_group_id = p_business_group_id
AND p_effective_date BETWEEN pl.effective_start_date
AND pl.effective_end_date;
SELECT lf_evt_ocrd_dt
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id = rslt_per_in_ler_id;
SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
cvr.person_id, bpl.NAME, crt.CRT_ORDR_TYP_CD
FROM ben_crt_ordr crt,
ben_crt_ordr_cvrd_per cvr,
per_all_people_f per,
per_contact_relationships con,
hr_lookups lkp,
ben_pl_f bpl
WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
AND crt.person_id = rslt_person_id
AND crt.pl_id = rslt_pl_id
AND crt.crt_ordr_id = cvr.crt_ordr_id
AND cvr.person_id = per.person_id
AND cvr.person_id = con.contact_person_id
AND con.contact_type = lkp.lookup_code
AND lkp.lookup_type = 'CONTACT'
AND p_effective_date BETWEEN NVL (lkp.start_date_active,
p_effective_date
)
AND NVL (lkp.end_date_active,
p_effective_date
)
AND GREATEST (l_lf_evt_ocrd_dt, rslt_enrt_cvg_strt_dt)
BETWEEN GREATEST
(NVL (apls_perd_strtg_dt,
p_effective_date
),
NVL (detd_qlfd_ordr_dt,
apls_perd_strtg_dt
)
)
AND NVL (apls_perd_endg_dt,
rslt_enrt_cvg_thru_dt
)
AND crt.business_group_id = p_business_group_id
AND cvr.business_group_id = p_business_group_id
AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
AND NVL (con.date_end, p_effective_date)
AND con.business_group_id = p_business_group_id
AND bpl.pl_id = rslt_pl_id
AND p_effective_date BETWEEN NVL (bpl.effective_start_date,
p_effective_date
)
AND NVL (bpl.effective_end_date,
p_effective_date
)
UNION
SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
cvr.person_id, bpt.NAME, crt.CRT_ORDR_TYP_CD
FROM ben_crt_ordr crt,
ben_crt_ordr_cvrd_per cvr,
per_all_people_f per,
per_contact_relationships con,
hr_lookups lkp,
ben_pl_typ_f bpt
WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
AND crt.person_id = rslt_person_id
AND crt.pl_typ_id = l_pl_typ_id_pl
AND crt.crt_ordr_id = cvr.crt_ordr_id
AND cvr.person_id = per.person_id
AND cvr.person_id = con.contact_person_id
AND con.contact_type = lkp.lookup_code
AND lkp.lookup_type = 'CONTACT'
AND p_effective_date BETWEEN NVL (lkp.start_date_active,
p_effective_date
)
AND NVL (lkp.end_date_active,
p_effective_date
)
AND GREATEST(l_lf_evt_ocrd_dt, rslt_enrt_cvg_strt_dt)
BETWEEN GREATEST
(NVL (apls_perd_strtg_dt,
p_effective_date
),
NVL (detd_qlfd_ordr_dt,
apls_perd_strtg_dt
)
)
AND NVL (apls_perd_endg_dt,
rslt_enrt_cvg_thru_dt
)
AND crt.business_group_id = p_business_group_id
AND cvr.business_group_id = p_business_group_id
AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
AND NVL (con.date_end, p_effective_date)
AND con.business_group_id = p_business_group_id
AND bpt.pl_typ_id = l_pl_typ_id_pl
AND p_effective_date BETWEEN NVL (bpt.effective_start_date,
p_effective_date
)
AND NVL (bpt.effective_end_date,
p_effective_date
);
SELECT NULL
FROM ben_elig_cvrd_dpnt_f pdp, ben_per_in_ler pil
WHERE pdp.dpnt_person_id = l_dpnt_id
AND pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND p_effective_date BETWEEN pdp.effective_start_date
AND pdp.effective_end_date
AND GREATEST(pil.lf_evt_ocrd_dt, rslt_enrt_cvg_strt_dt ) BETWEEN cvg_strt_dt
AND cvg_thru_dt
AND pdp.business_group_id = p_business_group_id
AND pil.business_group_id = p_business_group_id
AND pdp.per_in_ler_id = pil.per_in_ler_id;
select lf_evt_ocrd_dt
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id;
select 'Y'
from ben_crt_ordr crt,
ben_crt_ordr_cvrd_per cvr
where crt.crt_ordr_typ_cd in ('QMCSO','QDRO')
and crt.person_id = p_person_id
and (crt.pl_id = p_pl_id or crt.pl_typ_id = p_pl_typ_id)
and crt.crt_ordr_id = cvr.crt_ordr_id
and cvr.person_id = p_dpnt_person_id
and (greatest(p_enrt_cvg_strt_dt, p_lf_evt_ocrd_dt) between greatest(nvl(crt.apls_perd_strtg_dt,p_effective_date)
,nvl(crt.detd_qlfd_ordr_dt,crt.apls_perd_strtg_dt))
and nvl(crt.apls_perd_endg_dt,p_enrt_cvg_thru_dt))
and crt.business_group_id = p_business_group_id
and cvr.business_group_id = p_business_group_id;
select person_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
Procedure dt_update_validate
(p_prtt_enrt_rslt_id in number default hr_api.g_number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_elig_cvrd_dpnt_id in number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = 'DELETE' or
p_datetrack_mode = 'ZAP') then
--
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc,
p_argument => 'validation_start_date',
p_argument_value => p_validation_start_date);
End dt_delete_validate;
Procedure insert_validate
(p_rec in ben_pdp_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'insert_validate';
Bug 3756863 : Moved to POST_INSERT
--
crt_ordr_warning
(p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_business_group_id => p_rec.business_group_id);
End insert_validate;
Procedure update_validate
(p_rec in ben_pdp_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'update_validate';
Bug 3756863 : Moved to POST_UPDATE
crt_ordr_warning
(p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_business_group_id => p_rec.business_group_id);
dt_update_validate
(p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End update_validate;
Procedure delete_validate
(p_rec in ben_pdp_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'delete_validate';
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id);
Bug 3756863 : Moved to POST_DELETE
crt_ordr_warning
(p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_business_group_id => p_rec.business_group_id);
End delete_validate;
select a.legislation_code
from per_business_groups a,
ben_elig_cvrd_dpnt_f b
where b.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and a.business_group_id = b.business_group_id;