[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ler.typ_cd,
ler.ler_id,
ptn.lf_evt_ocrd_dt
from ben_ler_f ler,
ben_ptnl_ler_for_per ptn
where ptn.person_id = p_person_id
and ptn.ler_id = ler.ler_id
-- CWB Changes
-- ABSENCES - avoid collapsing absences life events.
and ler.typ_cd not in ('COMP', 'ABS','GSP', 'IREC')
and ptnl_ler_for_per_stat_cd in ('UNPROCD','DTCTD')
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
order by ptn.lf_evt_ocrd_dt asc;
select null
from ben_ler_f ler
where ler.ler_id = p_ler_id
and ler.typ_cd in ('DRVDAGE','DRVDCAL','DRVDCMP',
'DRVDHRW','DRVDLOS','DRVDTPF')
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date;
select paf.person_id
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.primary_flag = 'Y'
and l_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select ptn.lf_evt_ocrd_dt
from ben_ptnl_ler_for_per ptn,
ben_ler_f ler
where ptn.person_id = l_person_id
and ptn.ptnl_ler_for_per_stat_cd in ('UNPROCD','DTCTD')
and ptn.ler_id = p_ler_id
and ler.ler_id = ptn.ler_id
-- CWB Changes
-- ABSENCES - avoid collapsing absences life events.
and ler.typ_cd not in ('COMP', 'ABS','GSP', 'IREC')
and l_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ptn.rowid = (select min(ptn2.rowid)
from ben_ptnl_ler_for_per ptn2,
ben_ler_f ler2
where ptn2.person_id = ptn.person_id
and ptn2.ler_id = ler2.ler_id
and l_effective_date
between ler2.effective_start_date
and ler2.effective_end_date
and ler2.typ_cd not in ('COMP', 'ABS','GSP', 'IREC')
and ptn2.ptnl_ler_for_per_stat_cd in
('UNPROCD','DTCTD')
and ptn2.lf_evt_ocrd_dt =
(select min(ptn3.lf_evt_ocrd_dt)
from ben_ptnl_ler_for_per ptn3,
ben_ler_f ler3
where ptn3.person_id = ptn2.person_id
and ptn3.ler_id = ler3.ler_id
and l_effective_date
between ler3.effective_start_date
and ler3.effective_end_date
and ler3.typ_cd not in ( 'COMP','GSP', 'IREC', 'ABS')
and ptn3.ptnl_ler_for_per_stat_cd in
('UNPROCD','DTCTD')
));