[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asg.assignment_id,
asg.organization_id
FROM per_all_assignments_f asg
WHERE person_id = c_per_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = decode(c_run_md, 'I',asg.primary_flag, 'Y')
AND c_effdt
BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT pil.per_in_ler_id,
ler.typ_cd,
ler.name,
pil.lf_evt_ocrd_dt,
ler.effective_start_date,
ler.effective_end_date
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = c_per_id
AND pil.business_group_id = c_bgp_id
AND pil.ler_id = c_ler_id
AND pil.per_in_ler_stat_cd = 'STRTD'
AND ler.business_group_id = c_bgp_id
AND pil.ler_id = ler.ler_id
AND c_effdt BETWEEN ler.effective_start_date
AND ler.effective_end_date
and nvl(pil.assignment_id, -9999) = decode (c_run_md,
'I',
c_irecasg_id,
nvl(pil.assignment_id, -9999) );
select max(rslt.effective_start_date)
from ben_prtt_enrt_rslt_f rslt,ben_ler_f ler
where rslt.person_id = c_per_id
and ler.ler_id=rslt.ler_id
-- and rslt.prtt_enrt_rslt_stat_cd NOT IN ('BCKDT', 'VOIDD')
and rslt.prtt_enrt_rslt_stat_cd is null
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU' )
and rslt.business_group_id = c_bgp_id
and rslt.enrt_cvg_thru_dt = hr_api.g_eot; -- Bug 4388226 - End-dated suspended enrl shudn't be picked up.
SELECT MAX(pil.bckt_dt)
FROM ben_per_in_ler pil
-- CWB changes
,ben_ler_f ler
,ben_ptnl_ler_for_per plr
WHERE pil.person_id = c_per_id
AND pil.ler_id = ler.ler_id
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
and c_effdt
between ler.effective_start_date and ler.effective_end_date
AND pil.business_group_id = c_bgp_id
AND pil.ler_id = c_ler_id
AND pil.lf_evt_ocrd_dt = c_leodt
AND pil.bckt_dt IS NOT NULL
and pil.per_in_ler_stat_cd = 'BCKDT'
and pil.ptnl_ler_for_per_id = plr.ptnl_ler_for_per_id
and plr.ptnl_ler_for_per_stat_cd <> 'VOIDD';
SELECT pen.pl_id,
pen.oipl_id,
plip.plip_id
FROM ben_prtt_enrt_rslt_f pen, ben_plip_f plip
WHERE pen.person_id = c_per_id
AND pen.business_group_id = c_bgp_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
--AND pen.sspndd_flag = 'N'
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.effective_end_date = hr_api.g_eot
AND c_cvgthrudt <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND c_ptip_id = pen.ptip_id
AND plip.pgm_id = pen.pgm_id
AND plip.pl_id = pen.pl_id
AND c_effdt BETWEEN plip.effective_start_date
AND plip.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_plip_enrt_f lep
WHERE c_plip_id = lep.plip_id
AND c_ler_id = lep.ler_id
AND c_effdt BETWEEN lep.effective_start_date
AND lep.effective_end_date;