[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id';
p_sqlstr := p_sqlstr || ' (SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt';
p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id ';
p_sqlstr := p_sqlstr || ' AND EXISTS (SELECT 1 ';
p_sqlstr := p_sqlstr || ' SELECT DISTINCT peaq.person_id object_id ';
SELECT per_in_ler_id, prtt_enrt_rslt_id, person_id, actn_typ_id,
event_date, lf_evt_ocrd_dt, due_dt, actn_typ_cd, rqd_flag,
cmpltd_dt, prtt_enrt_actn_id
FROM hri_eq_ben_enrlactn_evts peaq
WHERE per_in_ler_id = p_per_in_ler_id
AND event_cd = 'COMPLETED'
AND cmpltd_dt IS NOT NULL;
SELECT per_in_ler_id, prtt_enrt_rslt_id, person_id, actn_typ_id,
event_date, lf_evt_ocrd_dt, due_dt, actn_typ_cd, rqd_flag,
cmpltd_dt, prtt_enrt_actn_id
FROM hri_eq_ben_enrlactn_evts peaq
WHERE per_in_ler_id = p_per_in_ler_id AND event_cd = 'INSERTED';
SELECT peaq.lf_evt_ocrd_dt, peaq.person_id,
peaq.per_in_ler_id, peaq.PRTT_ENRT_ACTN_ID
FROM hri_eq_ben_enrlactn_evts peaq
WHERE peaq.per_in_ler_id = p_per_in_ler_id
AND peaq.person_id = p_person_id
AND peaq.event_cd IN ('ZAP');
INSERT INTO hri_mb_ben_enrlactn_ct
( sspnd_ind,
actn_item_ind,
interim_ind,
change_date,
effective_start_date,
effective_end_date,
person_id,
asnd_lf_evt_dt,
actn_typ_cd,
compobj_sk_pk,
enrt_perd_id,
actn_typ_id,
prtt_enrt_rslt_id,
prtt_enrt_actn_id,
per_in_ler_id,
interim_enrt_rslt_id,
interim_compobj_sk_pk, due_dt
)
( SELECT (CASE WHEN (l_rqd_flag_tab (i) = 'Y'
AND l_cmpltd_dt_tab (i) IS NULL)
THEN 1
ELSE 0 END) sspnd_ind,
(CASE WHEN (l_cmpltd_dt_tab (i) IS NULL) -- 4541338
THEN 1
ELSE 0 END ) actn_item_ind,
DECODE (pen.rplcs_sspndd_rslt_id, NULL, 0, 1),
l_event_date_tab (i),
--l_event_date_tab (i),
pen.effective_start_Date,
hr_api.g_eot,
l_person_id_tab (i),
l_lf_evt_ocrd_dt_tab (i),
l_actn_typ_cd_tab (i),
copd.compobj_sk_pk compobj_sk_pk,
enpd.enrt_perd_id,
l_actn_typ_id_tab (i),
pen.prtt_enrt_rslt_id,
l_prtt_enrt_actn_id_tab (i),
l_per_in_ler_id_tab (i),
pen.rplcs_sspndd_rslt_id,
copd_int.compobj_sk_pk interim_compobj_sk_pk,
l_due_dt_tab (i)
FROM ben_prtt_enrt_rslt_f pen,
hri_cs_time_benrl_prd_ct enpd,
ben_prtt_enrt_rslt_f pen_int,
hri_cs_compobj_ct copd_int,
hri_cs_compobj_ct copd,
ben_opt_f opt,
ben_pl_f pln
WHERE pen.per_in_ler_id = l_per_in_ler_id_tab (i)
AND pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id_tab (i)
AND enpd.pgm_id = pen.pgm_id
AND l_lf_evt_ocrd_dt_tab (i) = enpd.asnd_lf_evt_dt
AND ( copd.oipl_id = pen.oipl_id
OR (pen.oipl_id IS NULL AND copd.oipl_id = -1)
)
AND copd.pl_id = pen.pl_id
AND copd.pgm_id = pen.pgm_id
AND opt.opt_id(+) = copd.opt_id
AND pln.pl_id = copd.pl_id
AND l_lf_evt_ocrd_dt_tab (i) BETWEEN opt.effective_start_date(+)
AND opt.effective_end_date(+)
AND l_lf_evt_ocrd_dt_tab (i) BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND pen.rplcs_sspndd_rslt_id = pen_int.prtt_enrt_rslt_id(+)
/* AND copd_int.oipl_id(+) = NVL (pen_int.oipl_id, -1) */
AND ( copd_int.oipl_id = pen_int.oipl_id
OR (pen_int.oipl_id IS NULL AND NVL(copd_int.oipl_id,-1) = -1)
)
AND copd_int.pgm_id(+) = pen_int.pgm_id
AND copd_int.pl_id(+) = pen_int.pl_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
/* Bug 4562628 */
and ( pen_int.effective_start_date is null
or (pen.effective_Start_date between pen_int.effective_Start_date
and pen_int.effective_end_date)
)
/* Bug 4562628 */
);
UPDATE hri_mb_ben_enrlactn_ct peac
SET peac.effective_end_date = l_event_date_tab (i) - 1
WHERE peac.per_in_ler_id = l_per_in_ler_id_tab (i)
AND peac.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id_tab (i)
AND peac.prtt_enrt_actn_id = l_prtt_enrt_actn_id_tab (i)
AND peac.actn_typ_id = l_actn_typ_id_tab (i)
AND peac.asnd_lf_evt_dt = l_lf_evt_ocrd_dt_tab (i);
DELETE FROM hri_mb_ben_enrlactn_ct peac
WHERE peac.asnd_lf_evt_dt = l_lf_evt_ocrd_dt_tab (i)
and peac.person_id = l_person_id_tab(i)
and peac.per_in_ler_id = l_per_in_ler_id_tab (i)
/* See Case(i) and Case(ii) above for clause below */
and peac.PRTT_ENRT_ACTN_ID = nvl(l_prtt_enrt_actn_id_tab (i),peac.PRTT_ENRT_ACTN_ID ) ;
dbg ('Deleted Zapped Records');
DELETE from hri_mb_ben_enrlactn_ct
WHERE person_id = p_pil_rec.person_id
AND asnd_lf_evt_dt = p_pil_rec.lf_evt_ocrd_dt
AND per_in_ler_id = p_pil_rec.per_in_ler_id;
INSERT INTO hri_mb_ben_enrlactn_ct
(
sspnd_ind,
actn_item_ind,
interim_ind,
change_date,
effective_start_date,
effective_end_date,
person_id,
asnd_lf_evt_dt,
actn_typ_cd,
compobj_sk_pk,
enrt_perd_id,
actn_typ_id,
prtt_enrt_rslt_id,
prtt_enrt_actn_id,
per_in_ler_id,
interim_enrt_rslt_id,
interim_compobj_sk_pk,
due_dt
)
(
SELECT (CASE WHEN (pea.rqd_flag = 'Y'
AND pea.cmpltd_dt IS NULL)
THEN 1
ELSE 0 END) sspnd_ind,
(CASE WHEN (pea.cmpltd_dt IS NULL) -- 4541338
THEN 1
ELSE 0 END ) actn_item_ind,
DECODE (pen.rplcs_sspndd_rslt_id, NULL, 0, 1),
pea.effective_start_date,
pea.effective_start_date,
pea.effective_end_date,
p_pil_rec.person_id,
p_pil_rec.lf_evt_ocrd_dt,
act.type_cd,
copd.compobj_sk_pk compobj_sk_pk,
enpd.enrt_perd_id,
pea.actn_typ_id,
pen.prtt_enrt_rslt_id,
pea.prtt_enrt_actn_id,
p_pil_rec.per_in_ler_id,
pen.rplcs_sspndd_rslt_id,
copd_int.compobj_sk_pk interim_compobj_sk_pk, pea.due_dt
FROM ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea,
hri_cs_time_benrl_prd_ct enpd,
ben_actn_typ act,
ben_prtt_enrt_rslt_f pen_int,
hri_cs_compobj_ct copd_int,
hri_cs_compobj_ct copd,
ben_opt_f opt,
ben_pl_f pln
WHERE pen.per_in_ler_id = p_pil_rec.per_in_ler_id
AND pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
AND pea.actn_typ_id = act.actn_typ_id
AND enpd.pgm_id = pen.pgm_id
AND p_pil_rec.lf_evt_ocrd_dt = enpd.asnd_lf_evt_dt
AND ( copd.oipl_id = pen.oipl_id
OR (pen.oipl_id IS NULL AND copd.oipl_id = -1)
)
AND copd.pl_id = pen.pl_id
AND copd.pgm_id = pen.pgm_id
AND opt.opt_id(+) = copd.opt_id
AND pln.pl_id = copd.pl_id
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date(+)
AND opt.effective_end_date(+)
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND pen.rplcs_sspndd_rslt_id = pen_int.prtt_enrt_rslt_id(+)
/* AND copd_int.oipl_id(+) = NVL(pen_int.oipl_id, -1) */
AND ( copd_int.oipl_id = pen_int.oipl_id
OR (pen_int.oipl_id IS NULL AND NVL(copd_int.oipl_id,-1) = -1)
)
AND copd_int.pgm_id(+) = pen_int.pgm_id
AND copd_int.pl_id(+) = pen_int.pl_id
-- AND pen.effective_end_date = hr_api.g_eot /* Bug 4562628 */
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.prtt_enrt_rslt_stat_cd IS NULL
/* Bug 4562628 */
and pea.effective_Start_date between pen.effective_Start_date and pen.effective_end_date
and ( pen_int.effective_start_date is null
or (pen.effective_Start_date between pen_int.effective_Start_date
and pen_int.effective_end_date)
)
/* Bug 4562628 */
);
SELECT pil.per_in_ler_id,
pil.person_id,
pil.lf_evt_ocrd_dt,
pil.per_in_ler_stat_cd,
pil.business_group_id
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.ler_id = ler.ler_id
AND ler.typ_cd = 'SCHEDDO'
AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
AND pil.lf_evt_ocrd_dt >= g_global_start_date
AND pil.person_id BETWEEN p_start_object_id
AND p_end_object_id
ORDER BY pil.person_id;
SELECT pil.per_in_ler_id,
pil.person_id,
pil.lf_evt_ocrd_dt,
pil.per_in_ler_stat_cd,
pil.business_group_id
FROM ben_per_in_ler pil,
(SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt
, pil.business_group_id
, pil.ler_id
FROM ben_per_in_ler pil,
ben_ler_f ler
WHERE pil.ler_id = ler.ler_id
AND ler.typ_cd = 'SCHEDDO'
AND pil.per_in_ler_stat_cd = 'STRTD'
AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date
AND ler.effective_end_date
GROUP BY pil.business_group_id, pil.ler_id ) pil1
WHERE pil.ler_id = pil1.ler_id
AND pil.business_group_id = pil1.business_group_id
AND pil.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
AND pil.lf_evt_ocrd_dt >= g_global_start_date
AND pil.person_id BETWEEN p_start_object_id AND p_end_object_id
ORDER BY pil.person_id;
SELECT DISTINCT penq.person_id, penq.per_in_ler_id
FROM hri_eq_ben_enrlactn_evts penq
WHERE penq.person_id BETWEEN p_start_object_id
AND p_end_object_id
ORDER BY penq.person_id;
l_person_tbl.DELETE;
l_pil_tbl.DELETE;