[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pil.per_in_ler_id, pil.per_in_ler_stat_cd
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id <> p_irec_per_in_ler_id
AND pil.assignment_id = p_assignment_id
ORDER BY pil.per_in_ler_id;
SELECT enb.enrt_bnft_id, enb.entr_val_at_enrt_flag, enb.dflt_val,
enb.val, enb.dflt_flag, enb.object_version_number,
enb.prtt_enrt_rslt_id, enb.cvg_mlt_cd, epe.pl_id, epe.pgm_id,
epe.oipl_id, enb.ordr_num
FROM ben_enrt_bnft enb, ben_elig_per_elctbl_chc epe
WHERE enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND epe.per_in_ler_id = cp_per_in_ler_id;
SELECT enb.enrt_bnft_id, enb.entr_val_at_enrt_flag, enb.dflt_val,
enb.val, enb.dflt_flag, enb.object_version_number,
enb.prtt_enrt_rslt_id, enb.cvg_mlt_cd, epe.pl_id, epe.pgm_id,
epe.oipl_id, enb.ordr_num
FROM ben_enrt_bnft enb, ben_elig_per_elctbl_chc epe
WHERE enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND epe.per_in_ler_id = cp_irec_per_in_ler_id
AND NVL (epe.pgm_id, -1) = NVL (cp_pgm_id, -1)
AND NVL (epe.pl_id, -1) = NVL (cp_pl_id, -1)
AND NVL (epe.oipl_id, -1) = NVL (cp_oipl_id, -1)
AND enb.ordr_num = cp_ordr_num;
SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
ecr.entr_val_at_enrt_flag, epe.pl_id, epe.pgm_id, epe.oipl_id,
ecr.acty_base_rt_id, ecr.ann_val
FROM ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe
WHERE epe.per_in_ler_id = cp_bckdt_per_in_ler_id
AND ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND ecr.entr_val_at_enrt_flag = 'Y'
AND ecr.spcl_rt_enrt_rt_id IS NULL
UNION
SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
ecr.entr_val_at_enrt_flag, epe.pl_id, epe.pgm_id, epe.oipl_id,
ecr.acty_base_rt_id, ecr.ann_val
FROM ben_enrt_rt ecr,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb
WHERE ecr.enrt_bnft_id = enb.enrt_bnft_id
AND epe.per_in_ler_id = cp_bckdt_per_in_ler_id
AND enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND ecr.entr_val_at_enrt_flag = 'Y'
AND ecr.spcl_rt_enrt_rt_id IS NULL;
SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id, ecr.ann_val,
ecr.object_version_number, ecr.entr_ann_val_flag
FROM ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe
WHERE epe.per_in_ler_id = cp_irec_per_in_ler_id
AND ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND ecr.entr_val_at_enrt_flag = 'Y'
AND ecr.spcl_rt_enrt_rt_id IS NULL
AND NVL (epe.pgm_id, -1) = NVL (cp_pgm_id, -1)
AND NVL (epe.pl_id, -1) = NVL (cp_pl_id, -1)
AND NVL (epe.oipl_id, -1) = NVL (cp_oipl_id, -1)
AND ecr.acty_base_rt_id = cp_acty_base_rt_id
UNION
SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id, ecr.ann_val,
ecr.object_version_number, ecr.entr_ann_val_flag
FROM ben_enrt_rt ecr,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb
WHERE ecr.enrt_bnft_id = enb.enrt_bnft_id
AND epe.per_in_ler_id = cp_irec_per_in_ler_id
AND enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND ecr.entr_val_at_enrt_flag = 'Y'
AND NVL (epe.pgm_id, -1) = NVL (cp_pgm_id, -1)
AND NVL (epe.pl_id, -1) = NVL (cp_pl_id, -1)
AND NVL (epe.oipl_id, -1) = NVL (cp_oipl_id, -1)
AND ecr.acty_base_rt_id = cp_acty_base_rt_id
AND ecr.spcl_rt_enrt_rt_id IS NULL;
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => l_active_bnft_rec.enrt_bnft_id,
p_val => l_bckdt_bnft_rec.val,
p_object_version_number => l_active_bnft_rec.object_version_number,
p_effective_date => p_effective_date
);
ben_enrollment_rate_api.update_enrollment_rate
(p_enrt_rt_id => l_active_rt_rec.enrt_rt_id,
p_val => l_bckdt_rt_rec.val,
p_ann_val => l_bckdt_rt_rec.ann_val,
p_object_version_number => l_active_rt_rec.object_version_number,
p_effective_date => p_effective_date
);
SELECT *
FROM ben_elig_per_elctbl_chc irec_epe
WHERE irec_epe.per_in_ler_id = p_irec_per_in_ler_id
AND irec_epe.approval_status_cd = 'IRC_BEN_A'; -- Bug 6079424
SELECT *
FROM ben_elig_per_elctbl_chc irec_epe
WHERE per_in_ler_id = p_unrstr_per_in_ler_id
AND NVL (pgm_id, -1) = NVL (p_pgm_id, -1)
AND pl_id = p_pl_id
AND NVL (oipl_id, -1) = NVL (p_oipl_id, -1);
SELECT enb.*
FROM ben_enrt_bnft enb
WHERE enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id
FROM ben_enrt_rt ecr
WHERE ecr.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
AND ecr.business_group_id = p_business_group_id
-- AND ecr.entr_val_at_enrt_flag = 'Y'
AND ecr.spcl_rt_enrt_rt_id IS NULL
UNION
SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id
FROM ben_enrt_rt ecr
WHERE ecr.enrt_bnft_id = cp_enrt_bnft_id
AND ecr.business_group_id = p_business_group_id
-- AND ecr.entr_val_at_enrt_flag = 'Y'
AND ecr.spcl_rt_enrt_rt_id IS NULL;