[Home] [Help]
634: select epe.elig_per_elctbl_chc_id,
635: epe.pl_id,
636: epe.pgm_id
637: from ben_prtt_enrt_rslt_f pen,
638: ben_elig_per_elctbl_chc epe,
639: ben_per_in_ler pil
640: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
641: and pen.prtt_enrt_rslt_stat_cd is null
642: and p_effective_date between
4612: ,p_rslt_object_version_number in out nocopy number
4613: ,p_dd_actn_item_open out nocopy boolean
4614: ,p_hack out nocopy varchar2) is
4615: -- this procedure determines if dependents are required to complete enrollment
4616: -- for an enrollment result check ben_elig_per_elctbl_chc.alws_dpnt_dsgn_flag
4617: -- if 'Y' and a new enrollment result write DD action item.
4618: -- A DD action item is only written when no dependents were designated.
4619: -- in additon if the dependent designation is 'R' required, suspend enrollment
4620: -- or if designation 'O' optional, do not suspend.
4741: --
4742: cursor c_elig_dpnts is
4743: SELECT 'S'
4744: from BEN_ELIG_DPNT egd,
4745: BEN_ELIG_PER_ELCTBL_CHC epe,
4746: BEN_PRTT_ENRT_RSLT_F pen
4747: where pen.PRTT_ENRT_RSLT_ID = p_prtt_enrt_rslt_id
4748: and pen.PRTT_ENRT_RSLT_ID = epe.PRTT_ENRT_RSLT_ID
4749: and egd.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
5337:
5338: cursor c_chc_flags is
5339: select epe.alws_dpnt_dsgn_flag, epe.dpnt_dsgn_cd
5340: from ben_prtt_enrt_rslt_f pen,
5341: ben_elig_per_elctbl_chc epe,
5342: ben_per_in_ler pil
5343: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5344: and pen.prtt_enrt_rslt_stat_cd is null
5345: and p_effective_date between
5621: and (
5622: ( pen2.per_in_ler_id = pen.per_in_ler_id and
5623: ( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
5624: ( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id and
5625: exists (select 'x' from ben_elig_per_elctbl_chc epe,
5626: ben_prtt_enrt_rslt_f pen3
5627: where epe.per_in_ler_id = pen.per_in_ler_id and
5628: epe.pl_id = pen.pl_id and
5629: ( epe.pgm_id = pen.pgm_id or pen.pgm_id is null) and
5667: and (
5668: ( pen2.per_in_ler_id = pen.per_in_ler_id and
5669: ( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
5670: ( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id or
5671: exists (select 'x' from ben_elig_per_elctbl_chc epe,
5672: ben_prtt_enrt_rslt_f pen3
5673: where epe.per_in_ler_id = pen2.per_in_ler_id and
5674: epe.pl_id = pen2.pl_id and
5675: ( epe.pgm_id = pen2.pgm_id or pen2.pgm_id is null) and
6086: epe.pl_id,
6087: nvl(pen.bnft_amt, 0) bnft_amt,
6088: pen.oipl_id
6089: from ben_prtt_enrt_rslt_f pen,
6090: ben_elig_per_elctbl_chc epe,
6091: ben_per_in_ler pil
6092: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6093: and pen.prtt_enrt_rslt_stat_cd is null
6094: and p_effective_date between