[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ppg.people_group_id , ppg.segment1 ,ppg.segment2 ,ppg.segment3 ,ppg.segment4 ,
ppg.segment5 , ppg.segment6 ,ppg.segment7 ,ppg.segment8 ,ppg.segment9 , ppg.segment10 ,
ppg.segment11 , ppg.segment12 ,ppg.segment13 ,ppg.segment14 ,ppg.segment15 ,
ppg.segment16 , ppg.segment17 ,ppg.segment18 ,ppg.segment19 ,ppg.segment20 ,
ppg.segment21 , ppg.segment22 ,ppg.segment23 ,ppg.segment24 ,ppg.segment25 ,
ppg.segment26 , ppg.segment27 ,ppg.segment28 ,ppg.segment29 ,ppg.segment30
from pay_people_groups ppg
where ppg.people_group_id = p_people_group_id ;
select bep.rt_pct_fl_tm_val
from ben_elig_per_f bep,
ben_per_in_ler pil
where bep.person_id = p_person_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and p_effective_date
between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id+0
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
);
select epo.rt_pct_fl_tm_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and p_effective_date between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select epo.rt_pct_fl_tm_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.plip_id,-1) = nvl(p_plip_id,-1)
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and p_effective_date between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select bep.rt_hrs_wkd_val
from ben_elig_per_f bep,
ben_per_in_ler pil
where bep.person_id = p_person_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and p_effective_date between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select epo.rt_hrs_wkd_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and p_effective_date between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select epo.rt_hrs_wkd_val
from ben_elig_per_f bep, ben_elig_per_opt_f epo,
ben_per_in_ler pil
where bep.person_id = p_person_id
and bep.elig_per_id = epo.elig_per_id
and epo.opt_id = p_opt_id
and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(bep.plip_id,-1) = nvl(p_plip_id,-1)
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and p_effective_date between bep.effective_start_date
and bep.effective_end_date
and pil.per_in_ler_id(+)=bep.per_in_ler_id
and pil.business_group_id(+)=bep.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null); -- outer join condition
select null
from per_absence_attendances abs
where abs.person_id = p_person_id
and abs.absence_attendance_type_id = p_absence_attendance_type_id
and nvl(abs.abs_attendance_reason_id,-1) =
nvl(p_abs_attendance_reason_id,nvl(abs.abs_attendance_reason_id,-1))
and l_effective_date
between nvl(abs.date_start,l_effective_date) and
nvl(abs.date_end,l_effective_date)
and abs.business_group_id = p_business_group_id;
SELECT epz.pstl_zip_rng_id,epz.excld_flag
FROM ben_pstl_zip_rt_f epz
WHERE epz.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
AND cv_effective_date -- FONM NVL(p_lf_evt_ocrd_dt, p_effective_date)
BETWEEN epz.effective_start_date
AND epz.effective_end_date;
SELECT zip.from_value, zip.to_value
FROM ben_pstl_zip_rng_f zip
WHERE zip.pstl_zip_rng_id = p_pstl_zip_rng_id
AND LENGTH(p_postal_code) >= LENGTH(zip.from_value)
AND (SUBSTR( nvl(p_postal_code,'-1'),1,LENGTH(zip.from_value))
BETWEEN zip.from_value AND NVL(zip.to_value,p_postal_code)
OR NVL(p_postal_code,'-1') = zip.from_value
OR nvl(p_postal_code,'-1') = zip.to_value)
AND cv_effective_date
BETWEEN zip.effective_start_date
AND zip.effective_end_date;
select sum(normal_hours)
from per_all_assignments_f
where person_id = c_person_id
and c_effective_date between effective_start_date and effective_end_date
and frequency = c_freq_cd
and business_group_id = c_business_group_id;
select normal_hours, frequency
from per_all_assignments_f
where assignment_id = c_assignment_id
and c_effective_date between effective_start_date and effective_end_date
and business_group_id = c_business_group_id;
SELECT sar.svc_area_id, sar.excld_flag
FROM ben_svc_area_rt_f sar
WHERE sar.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
AND cv_effective_date -- FONM NVL(p_lf_evt_ocrd_dt, p_effective_date)
BETWEEN sar.effective_start_date
AND sar.effective_end_date;
SELECT zip.from_value, zip.to_value
FROM ben_pstl_zip_rng_f zip
WHERE zip.pstl_zip_rng_id IN (
SELECT pstl_zip_rng_id
FROM ben_svc_area_pstl_zip_rng_f rng
WHERE rng.SVC_AREA_ID = p_svc_area_id
AND cv_effective_date BETWEEN rng.effective_start_date
AND rng.effective_end_date)
AND LENGTH(p_postal_code) >= LENGTH(zip.from_value)
AND (SUBSTR( NVL(p_postal_code,'-1'),1,LENGTH(zip.from_value))
BETWEEN zip.from_value and NVL(zip.to_value,p_postal_code)
OR NVL(p_postal_code,'-1') = zip.from_value
OR NVL(p_postal_code,'-1') = zip.to_value)
AND cv_effective_date BETWEEN zip.effective_start_date
AND zip.effective_end_date;
select epe.pl_id, epe.pgm_id, epe.oipl_id, epe.per_in_ler_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select epe.pl_id, epe.pgm_id, epe.oipl_id, epe.per_in_ler_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select epe.pl_id, epe.pgm_id, epe.oipl_id, epe.per_in_ler_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select epe.pl_id, epe.pgm_id, epe.oipl_id, epe.per_in_ler_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select cqb.*, crp.per_in_ler_id
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.quald_bnf_flag = 'Y'
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cqb.cbr_elig_perd_strt_dt and
cqb.cbr_elig_perd_end_dt
and cqb.business_group_id = p_business_group_id
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and cqb.pgm_id = nvl(p_pgm_id,cqb.pgm_id)
and nvl(cqb.pl_typ_id,nvl(p_pl_typ_id,-1)) = nvl(p_pl_typ_id,-1)
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and pil.business_group_id = crp.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
and cqb.quald_bnf_flag = 'Y'
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cqb.cbr_elig_perd_strt_dt and
cqb.cbr_elig_perd_end_dt
and cqb.business_group_id = p_business_group_id
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and cqb.pgm_id = nvl(p_pgm_id,cqb.pgm_id)
and nvl(cqb.pl_typ_id,nvl(p_pl_typ_id,-1)) = nvl(p_pl_typ_id,-1)
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and pil.business_group_id = crp.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select null
from ben_cbr_per_in_ler crp
, ben_ler_f ler
, ben_per_in_ler pil
where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and crp.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'DSBLTY'
and ler.qualg_evt_flag = 'Y'
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between ler.effective_start_date and
ler.effective_end_date
and ler.business_group_id = p_business_group_id
and ler.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and crp.cnt_num = (select max(cnt_num)
from ben_cbr_per_in_ler crp2
,ben_per_in_ler pil2
where crp2.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and crp2.per_in_ler_id = pil2.per_in_ler_id
and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and crp2.business_group_id = p_business_group_id
and crp2.business_group_id = pil2.business_group_id);
select null
from ben_ler_f ler
, ben_per_in_ler pil
where ler.ler_id = pil.ler_id
and pil.person_id = p_person_id
and ler.name = 'Assistance Eligible Individual'
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and pil.lf_evt_ocrd_dt
between p_cbr_elig_perd_strt_dt
and p_cbr_elig_perd_end_dt
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between ler.effective_start_date
and ler.effective_end_date
and ler.business_group_id = p_business_group_id
and pil.business_group_id = ler.business_group_id;
select ler.*
from
ben_ler_f ler
where
ler.ler_id = p_ler_id
and ler.QUALG_EVT_FLAG = 'Y'
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.business_group_id = p_business_group_id ;
select cqb.quald_bnf_flag
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.pgm_id = nvl(p_pgm_id,cqb.pgm_id)
and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
and p_lf_evt_ocrd_dt
between nvl(cqb.cbr_elig_perd_strt_dt , p_lf_evt_ocrd_dt)
and nvl(cqb.cbr_elig_perd_end_dt , p_lf_evt_ocrd_dt)
and cqb.business_group_id = p_business_group_id
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.pl_id = p_pl_id
and pdp.dpnt_person_id = p_person_id
and l_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pdp.business_group_id = pen.business_group_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and l_date_to_use
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select null
from ben_prtt_enrt_rslt_f pen
,ben_elig_cvrd_dpnt_f pdp
,ben_plip_f cpp
where pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.dpnt_person_id = p_person_id
and pen.pgm_id = cpp.pgm_id
and pen.pl_id = cpp.pl_id
and cpp.plip_id = p_plip_id
and l_date_to_use
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.business_group_id = pen.business_group_id
and l_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.business_group_id = pen.business_group_id
and l_date_to_use
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select pen.pl_id
from ben_prtt_enrt_rslt_f pen
,ben_elig_cvrd_dpnt_f pdp
where pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.dpnt_person_id = p_person_id
and pen.ptip_id = p_ptip_id
and l_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.business_group_id = pen.business_group_id
and l_date_to_use
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select null
from ben_pl_regn_f prg
,ben_regn_f reg
where prg.pl_id = p_pl_id
and prg.regn_id = reg.regn_id
and reg.sttry_citn_name = 'COBRA'
and prg.business_group_id = p_business_group_id
and l_date_to_use
between prg.effective_start_date
and prg.effective_end_date
and prg.business_group_id = reg.business_group_id
and l_date_to_use
between reg.effective_start_date
and reg.effective_end_date;
select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.pgm_id = p_pgm_id
and pdp.dpnt_person_id = p_person_id
and l_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pdp.business_group_id = pen.business_group_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and l_date_to_use
between pdp.cvg_strt_dt
and pdp.cvg_thru_dt
and pdp.effective_end_date = hr_api.g_eot;
select null
from ben_elig_per_f epo,
ben_per_in_ler pil
where epo.person_id = p_person_id
and epo.pl_id = p_pl_id
and p_effective_date
between epo.effective_start_date
and epo.effective_end_date
and epo.elig_flag = 'Y' -- Bug 13636427
and epo.business_group_id = p_business_group_id
and pil.per_in_ler_id(+)=epo.per_in_ler_id
-- and pil.business_group_id(+)=epo.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
);
select /*+ bendtlep.check_othr_ptip.pilc1 */
null
from ben_pl_f pln,
ben_plip_f cpp,
ben_ptip_f ctp,
ben_elig_per_f epo
where pln.pl_id = cpp.pl_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date
and c_effective_date
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.pgm_id = ctp.pgm_id
and pln.pl_typ_id = ctp.pl_typ_id
and ctp.ptip_id = c_ptip_id
and c_effective_date
between ctp.effective_start_date
and ctp.effective_end_date
and epo.per_in_ler_id = c_per_in_ler_id
and epo.pgm_id = ctp.pgm_id
and epo.pl_id = pln.pl_id
and c_effective_date
between epo.effective_start_date
and epo.effective_end_date
and epo.elig_flag = 'Y'
and ( c_only_pls_subj_cobra_flag = 'N'
or exists (select null
from ben_pl_regn_f prg,
ben_regn_f reg
where prg.pl_id = pln.pl_id
and c_effective_date between prg.effective_start_date
and prg.effective_end_date
and reg.regn_id = prg.regn_id
and c_effective_date between reg.effective_start_date
and reg.effective_end_date
and reg.sttry_citn_name = 'COBRA') ); -- 2443719
select /*+ bendtlep.check_othr_ptip.c1 */
null
from ben_pl_f pln,
ben_plip_f cpp,
ben_ptip_f ctp,
ben_pl_regn_f prg,
ben_regn_f reg,
ben_elig_per_f epo,
ben_per_in_ler pil
where pln.pl_id = cpp.pl_id
and pln.business_group_id = c_business_group_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date
and cpp.business_group_id = pln.business_group_id
and c_effective_date
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.pgm_id = ctp.pgm_id
and pln.pl_typ_id = ctp.pl_typ_id
and ctp.ptip_id = c_ptip_id
and ctp.business_group_id = pln.business_group_id
and c_effective_date
between ctp.effective_start_date
and ctp.effective_end_date
and epo.person_id = c_person_id
and epo.pgm_id = ctp.pgm_id
and epo.pl_id = pln.pl_id
and epo.business_group_id = c_business_group_id
and p_effective_date
between epo.effective_start_date
and epo.effective_end_date
and epo.elig_flag = 'Y'
and ( c_only_pls_subj_cobra_flag = 'N' -- cobra regulation needed only when the flag is set
or exists (select null
from ben_pl_regn_f prg,
ben_regn_f reg
where prg.pl_id = pln.pl_id
and c_effective_date between prg.effective_start_date
and prg.effective_end_date
and prg.business_group_id = pln.business_group_id
and reg.regn_id = prg.regn_id
and c_effective_date between reg.effective_start_date
and reg.effective_end_date
and reg.sttry_citn_name = 'COBRA') ) -- 2443719
and pil.per_in_ler_id(+)=epo.per_in_ler_id
--and pil.business_group_id(+)=epo.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
) ;
select null
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.oipl_id = p_oipl_id
and pen.person_id = p_person_id
and l_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_pl_f pln,
ben_pl_regn_f prg,
ben_regn_f reg
where pln.pl_id = p_pl_id
and pln.business_group_id = p_business_group_id
and l_date_to_use
between pln.effective_start_date
and pln.effective_end_date
and pln.pl_id = prg.pl_id
and prg.business_group_id = pln.business_group_id
and l_date_to_use
between prg.effective_start_date
and prg.effective_end_date
and prg.regn_id = reg.regn_id
and reg.business_group_id = prg.business_group_id
and l_date_to_use
between reg.effective_start_date
and reg.effective_end_date
and reg.sttry_citn_name = 'COBRA';
select null
from ben_prtt_enrt_rslt_f pen
,ben_plip_f cpp
where pen.business_group_id = p_business_group_id
and pen.pgm_id = cpp.pgm_id
and pen.pl_id = cpp.pl_id
and cpp.plip_id = p_plip_id
and l_date_to_use
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.business_group_id = pen.business_group_id
and pen.person_id = p_person_id
and l_date_to_use
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_pl_f pln,
ben_plip_f cpp,
ben_ptip_f ctp,
ben_elig_per_f epo,
ben_elig_dpnt edp,
ben_per_in_ler pil
where pln.pl_id = cpp.pl_id
and pln.business_group_id = c_bgp_id
and c_eff_date
between pln.effective_start_date and pln.effective_end_date
and cpp.business_group_id = pln.business_group_id
and c_eff_date
between cpp.effective_start_date and cpp.effective_end_date
and cpp.pgm_id = ctp.pgm_id
and pln.pl_typ_id = ctp.pl_typ_id
and ctp.ptip_id = c_ptip_id
and ctp.business_group_id = pln.business_group_id
and c_eff_date
between ctp.effective_start_date and ctp.effective_end_date
and edp.dpnt_person_id = c_dpnt_person_id
and epo.pgm_id = ctp.pgm_id
and epo.pl_id = pln.pl_id
and epo.business_group_id = c_bgp_id
and c_eff_date
between epo.effective_start_date and epo.effective_end_date
and epo.elig_flag = 'Y'
and edp.dpnt_inelig_flag = 'N'
and edp.create_dt = (select max(edp2.create_dt)
from ben_elig_dpnt edp2
,ben_per_in_ler pil2
where edp2.dpnt_person_id = edp.dpnt_person_id
and edp2.elig_per_id = epo.elig_per_id
and pil2.per_in_ler_id(+)=edp2.per_in_ler_id
and pil2.business_group_id(+)=edp2.business_group_id
and (pil2.per_in_ler_stat_cd
not in ('VOIDD','BCKDT')
or pil2.per_in_ler_stat_cd is null))
and epo.elig_per_id = edp.elig_per_id
and pil.per_in_ler_id(+)=edp.per_in_ler_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null
)
and epo.per_in_ler_id = edp.per_in_ler_id
and c_eff_date
between epo.effective_start_date and epo.effective_end_date;
select null
from per_competence_elements pce
where pce.person_id = p_person_id
and type = 'PERSONAL'
and pce.competence_id = p_competence_id
and pce.proficiency_level_id = p_rating_level_id
and p_lf_evt_ocrd_dt between nvl(pce.effective_date_from, p_lf_evt_ocrd_dt)
and nvl(pce.effective_date_to, p_lf_evt_ocrd_dt)
and pce.business_group_id = p_business_group_id ;
select null
from per_qualifications pqt
where pqt.person_id = p_person_id
and pqt.qualification_type_id = p_qualification_type_id
and nvl(pqt.title,'NULL' ) = NVL(p_title , 'NULL')
and p_lf_evt_ocrd_dt between nvl(pqt.start_date , p_lf_evt_ocrd_dt)
and nvl(pqt.end_date ,p_lf_evt_ocrd_dt)
and pqt.business_group_id = p_business_group_id ;
select (maximum * grade_annualization_factor) maximum ,
(minimum * grade_annualization_factor) minimum
from pay_grade_rules_f pgr,
per_pay_bases ppb -- 2594204
where ppb.pay_basis_id = p_pay_basis_id
and ppb.business_group_id = p_business_group_id
and pgr.rate_id = ppb.rate_id
and pgr.business_group_id = p_business_group_id
and pgr.grade_or_spinal_point_id = p_grade_id
and p_lf_evt_ocrd_dt between nvl(pgr.effective_start_date, p_lf_evt_ocrd_dt)
and nvl(pgr.effective_end_date, p_lf_evt_ocrd_dt);
select ppp.proposed_salary_n * ppb.pay_annualization_factor annual_salary
from per_pay_bases ppb,
per_pay_proposals ppp
where ppb.pay_basis_id = p_pay_basis_id
and ppb.business_group_id = p_business_group_id
and ppp.assignment_id = p_assignment_id
and ppp.change_date <= p_lf_evt_ocrd_dt
order by ppp.change_date desc ;
select ppp.proposed_salary_n
from per_pay_proposals ppp
where ppp.assignment_id = p_assignment_id
and ppp.business_group_id = p_business_group_id
and ppp.approved = 'Y'
and ppp.change_date <= p_lf_evt_ocrd_dt
order by ppp.change_date desc;
select ppb.pay_annualization_factor
from per_pay_bases ppb
where ppb.pay_basis_id = p_pay_basis_id
and ppb.business_group_id = ppb.business_group_id;
SELECT ppr.performance_rating
FROM per_performance_reviews ppr, per_events pev
WHERE NVL(pev.assignment_id,p_assignment_id) = p_assignment_id /* Bug 9679606: added nvl condition */
AND pev.TYPE = p_event_type
AND NVL(pev.business_group_id,p_business_group_id) = p_business_group_id /* Bug 9679606: added nvl condition */
AND p_lf_evt_ocrd_dt BETWEEN NVL (date_start, p_lf_evt_ocrd_dt)
AND NVL (date_end, p_lf_evt_ocrd_dt)
AND ppr.event_id = pev.event_id
AND ppr.person_id = p_person_id
-- AND ppr.performance_rating = p_performance_rating
ORDER BY pev.date_start desc, ppr.review_date desc;
* If ELPRO criteria does not specify Performance Type then we would select
* only those performance reviews which do have Performance (Interview) Type
* as NULL i.e PPR.EVENT_ID IS NULL
*/
CURSOR c2_without_events (
p_person_id NUMBER,
p_effective_date DATE
)
IS
SELECT ppr.performance_rating
FROM per_performance_reviews ppr
WHERE ppr.person_id = p_person_id
-- AND ppr.performance_rating = p_performance_rating
AND ppr.review_date <= p_effective_date
AND ppr.event_id IS NULL
ORDER BY ppr.review_date desc;
ben_evaluate_rate_profiles.g_all_prfls.delete;
ben_evaluate_rate_profiles.g_use_prfls.delete;
l_insert_record varchar2(1);
l_insert_record := 'Y';
l_insert_record := 'N';
if l_insert_record = 'Y' then
-- We didn't find a match in the table, add a row to table.
-- increase the total number of profiles matched
g_all_prfls(p_ordr_num).vrbl_rt_prfl_id :=
p_vrbl_rt_prfl_id;
l_insert_record := 'Y';
l_insert_record := 'N';
if l_insert_record = 'Y' then
-- We didn't find a match in the table, add a row to table.
-- increase the total number of profiles matched
g_num_of_prfls_used := g_num_of_prfls_used + 1;
select sck.segment1 gre_id
from hr_soft_coding_keyflex sck
where sck.soft_coding_keyflex_id = c_soft_coding_keyflex_id;
select epe.pgm_id,
epe.pl_id,
epe.pl_typ_id,
epe.oipl_id,
epe.business_group_id,
epe.plip_id,
epe.ptip_id,
epe.oiplip_id,
pil.ler_id,
pil.per_in_ler_id
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id;