[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.bnf_dsgn_cd
-- added for bug no. 1845251
, a.name
, a.bnf_cntngt_bnfs_alwd_flag
, a.bnf_may_dsgt_org_flag
, a.bnf_addl_instn_txt_alwd_flag
, a.bnf_pct_amt_alwd_cd
, a.bnf_mn_dsgntbl_amt
, a.bnf_incrmt_amt
, a.bnf_mn_dsgntbl_pct_val
, a.bnf_pct_incrmt_val
, b.oipl_id
from ben_pl_f a
, ben_prtt_enrt_rslt_f b
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and b.prtt_enrt_rslt_stat_cd is null
and nvl(l_lf_evt_ocrd_dt,p_effective_date) between b.effective_start_date
and b.effective_end_date
and a.pl_id = b.pl_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date) between a.effective_start_date
and a.effective_end_date
and a.business_group_id + 0 = p_business_group_id
;
SELECT
opt.NAME
FROM
ben_opt_f opt
,ben_oipl_f oipl
WHERE
oipl.oipl_id=l_bnf_enrt_oipl_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date) between oipl.effective_start_date and oipl.effective_end_date
and opt.opt_id=oipl.opt_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date) between opt.effective_start_date and opt.effective_end_date;
SELECT pil.lf_evt_ocrd_dt
FROM ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd is NULL
AND p_effective_date BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND pen.per_in_ler_id = pil.per_in_ler_id;
select sum(pct_dsgd_num)
from ben_pl_bnf_f b
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and b.business_group_id + 0 = p_business_group_id
and b.prmry_cntngnt_cd = p_prmry_cntngnt_cd
and b.pl_bnf_id <> nvl(p_pl_bnf_id, hr_api.g_number)
and p_validation_start_date <= b.effective_end_date
and p_validation_end_date >= b.effective_start_date
;
select bnft_amt
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 sum(amt_dsgd_val)
from ben_pl_bnf_f b
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and b.business_group_id + 0 = p_business_group_id
and b.prmry_cntngnt_cd = p_prmry_cntngnt_cd
and b.pl_bnf_id <> nvl(p_pl_bnf_id, hr_api.g_number)
and p_validation_start_date <=b.effective_end_date
and p_validation_end_date >= b.effective_start_date
;
select uom
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_effective_date between effective_start_date
and effective_end_date
;
select null
from ben_pl_bnf_f pbn,
ben_per_in_ler pil
where pbn.bnf_person_id = p_bnf_person_id
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')
or pil.per_in_ler_stat_cd is null )
and pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbn.pl_bnf_id <> nvl(p_pl_bnf_id, hr_api.g_number)
and pbn.business_group_id + 0 = p_business_group_id
and p_validation_start_date <= pbn.effective_end_date
and p_validation_end_date >= pbn.effective_start_date
;
select null
from per_all_people_f a
where a.person_id = p_bnf_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 per_all_people a, per_contact_relationships c
where a.person_id = p_ttee_person_id
and a.person_id = c.contact_person_id
and c.person_id = p_bnf_person_id
and p_validation_start_date <= nvl(c.date_end, p_validation_start_date)
and p_validation_end_date >= nvl(c.date_start, p_validation_start_date)
and a.business_group_id + 0 = p_business_group_id
and p_validation_start_date <= a.effective_end_date
and p_validation_end_date >= a.effective_start_date
;
select distinct drt.rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ drt
, ben_dsgn_rqmt_f drm
, ben_opt_f opt
, ben_oipl_f oipl
, ben_prtt_enrt_rslt_f pen
where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
and drt.business_group_id = p_business_group_id
and drm.dsgn_typ_cd = 'BNF'
and drm.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between drm.effective_start_date and drm.effective_end_date
and opt.opt_id = nvl(drm.opt_id, -1)
and opt.business_group_id = p_business_group_id
and p_effective_date between opt.effective_start_date and opt.effective_end_date
and oipl.opt_id = opt.opt_id
and oipl.business_group_id = p_business_group_id
and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
and pen.oipl_id = oipl.oipl_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ;
select distinct drt.rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ drt
, ben_dsgn_rqmt_f drm
, ben_oipl_f oipl
, ben_prtt_enrt_rslt_f pen
where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
and drt.business_group_id = p_business_group_id
and drm.dsgn_typ_cd = 'BNF'
and drm.business_group_id = p_business_group_id
and p_effective_date between drm.effective_start_date and drm.effective_end_date
and oipl.oipl_id = nvl(drm.oipl_id, -1)
and oipl.business_group_id = p_business_group_id
and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
and pen.oipl_id = oipl.oipl_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null;
select distinct drt.rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ drt
, ben_dsgn_rqmt_f drm
, ben_pl_f pln
, ben_prtt_enrt_rslt_f pen
where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
and drt.business_group_id = p_business_group_id
and drm.dsgn_typ_cd = 'BNF'
and drm.business_group_id = p_business_group_id
and p_effective_date between drm.effective_start_date and drm.effective_end_date
and pln.pl_id = nvl(drm.pl_id, -1)
and pln.business_group_id = p_business_group_id
and p_effective_date between pln.effective_start_date and pln.effective_end_date
and pen.pl_id = pln.pl_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
and pil.person_id = p_bnf_person_id ;
select pcr.contact_type,
hll.meaning
from per_contact_relationships pcr
, ben_per_in_ler pil
, hr_leg_lookups hll
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
and pcr.business_group_id = p_business_group_id
and pcr.person_id = pil.person_id
and pcr.contact_person_id = p_bnf_person_id
and pcr.contact_type = c_contact_type
and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
and hll.lookup_type = 'CONTACT'
and p_effective_date
between nvl(hll.start_date_active,p_effective_date) and nvl(hll.end_date_active,p_effective_date)
and pcr.contact_type = hll.lookup_code ;
select pl_id, oipl_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
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 p_effective_date between oipl.effective_start_date and oipl.effective_end_date ;
select name
from ben_pl_f
where pl_id=p_pl_id
and p_effective_date between effective_start_date and effective_end_date ;
select name
from ben_opt_f
where opt_id=p_opt_id
and p_effective_date between effective_start_date and effective_end_date ;
select null
from per_contact_relationships pcr
, ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
and pcr.business_group_id = p_business_group_id
and pcr.person_id = pil.person_id
and pcr.contact_person_id = p_bnf_person_id
and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
and pcr.contact_type in
(select distinct drt.rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ drt
, ben_dsgn_rqmt_f drm
, ben_opt_f opt
, ben_oipl_f oipl
, ben_prtt_enrt_rslt_f pen
where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
and drt.business_group_id = p_business_group_id
and drm.dsgn_typ_cd = 'BNF'
and drm.business_group_id = p_business_group_id
and p_effective_date between drm.effective_start_date and drm.effective_end_date
and opt.opt_id = nvl(drm.opt_id, -1)
and opt.business_group_id = p_business_group_id
and p_effective_date between opt.effective_start_date and opt.effective_end_date
and oipl.opt_id = opt.opt_id
and oipl.business_group_id = p_business_group_id
and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
and pen.oipl_id = oipl.oipl_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ) ;
select null
from per_contact_relationships pcr
, ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
and pcr.business_group_id = p_business_group_id
and pcr.person_id = pil.person_id
and pcr.contact_person_id = p_bnf_person_id
and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
and pcr.contact_type in
(select distinct drt.rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ drt
, ben_dsgn_rqmt_f drm
, ben_oipl_f oipl
, ben_prtt_enrt_rslt_f pen
where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
and drt.business_group_id = p_business_group_id
and drm.dsgn_typ_cd = 'BNF'
and drm.business_group_id = p_business_group_id
and p_effective_date between drm.effective_start_date and drm.effective_end_date
and oipl.oipl_id = nvl(drm.oipl_id, -1)
and oipl.business_group_id = p_business_group_id
and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
and pen.oipl_id = oipl.oipl_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ) ;
select null
from per_contact_relationships pcr
, ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
and pcr.business_group_id = p_business_group_id
and pcr.person_id = pil.person_id
and pcr.contact_person_id = p_bnf_person_id
and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
and pcr.contact_type in
(select distinct drt.rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ drt
, ben_dsgn_rqmt_f drm
, ben_pl_f pln
, ben_prtt_enrt_rslt_f pen
where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
and drt.business_group_id = p_business_group_id
and drm.dsgn_typ_cd = 'BNF'
and drm.business_group_id = p_business_group_id
and p_effective_date between drm.effective_start_date and drm.effective_end_date
and pln.pl_id = nvl(drm.pl_id, -1)
and pln.business_group_id = p_business_group_id
and p_effective_date between pln.effective_start_date and pln.effective_end_date
and pen.pl_id = pln.pl_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ) ;
select null
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
and pil.person_id = p_bnf_person_id ;
select hll.meaning
from per_contact_relationships pcr
, ben_per_in_ler pil
, hr_leg_lookups hll
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null )
and pcr.business_group_id = p_business_group_id
and pcr.person_id = pil.person_id
and pcr.contact_person_id = p_bnf_person_id
and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
and hll.lookup_type = 'CONTACT'
and p_effective_date
between nvl(hll.start_date_active,p_effective_date) and nvl(hll.end_date_active,p_effective_date)
and pcr.contact_type = hll.lookup_code ;
select pl_id, oipl_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
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 p_effective_date between oipl.effective_start_date and oipl.effective_end_date ;
select null
from hr_all_organization_units a
where a.organization_id = p_organization_id
and a.business_group_id + 0 = p_business_group_id
;
select null
from ben_pl_bnf_f pbn,
ben_per_in_ler pil
where (pbn.bnf_person_id = p_bnf_person_id
or pbn.organization_id = p_organization_id)
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')
or pil.per_in_ler_stat_cd is null )
and pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbn.pl_bnf_id <> nvl(p_pl_bnf_id,hr_api.g_number)
and pbn.business_group_id = p_business_group_id
and pbn.prmry_cntngnt_cd <> p_prmry_cntngnt_cd
and p_effective_date between pbn.effective_start_date and (pbn.effective_end_date -1);
Procedure dt_update_validate
(p_per_in_ler_id in number default hr_api.g_number,
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_pl_bnf_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_pbn_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';
End insert_validate;
Procedure update_validate
(p_rec in ben_pbn_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';
dt_update_validate
(p_per_in_ler_id => p_rec.per_in_ler_id,
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_pbn_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_pl_bnf_id => p_rec.pl_bnf_id);
End delete_validate;
select a.legislation_code
from per_business_groups a,
ben_pl_bnf_f b
where b.pl_bnf_id = p_pl_bnf_id
and a.business_group_id = b.business_group_id;