[Home] [Help]
647: select epe.elig_per_elctbl_chc_id,
648: epe.pl_id,
649: epe.pgm_id
650: from ben_prtt_enrt_rslt_f pen,
651: ben_elig_per_elctbl_chc epe,
652: ben_per_in_ler pil
653: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
654: and pen.prtt_enrt_rslt_stat_cd is null
655: and p_effective_date between
4625: ,p_rslt_object_version_number in out nocopy number
4626: ,p_dd_actn_item_open out nocopy boolean
4627: ,p_hack out nocopy varchar2) is
4628: -- this procedure determines if dependents are required to complete enrollment
4629: -- for an enrollment result check ben_elig_per_elctbl_chc.alws_dpnt_dsgn_flag
4630: -- if 'Y' and a new enrollment result write DD action item.
4631: -- A DD action item is only written when no dependents were designated.
4632: -- in additon if the dependent designation is 'R' required, suspend enrollment
4633: -- or if designation 'O' optional, do not suspend.
4754: --
4755: cursor c_elig_dpnts is
4756: SELECT 'S'
4757: from BEN_ELIG_DPNT egd,
4758: BEN_ELIG_PER_ELCTBL_CHC epe,
4759: BEN_PRTT_ENRT_RSLT_F pen
4760: where pen.PRTT_ENRT_RSLT_ID = p_prtt_enrt_rslt_id
4761: and pen.PRTT_ENRT_RSLT_ID = epe.PRTT_ENRT_RSLT_ID
4762: and egd.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
5415:
5416: cursor c_chc_flags is
5417: select epe.alws_dpnt_dsgn_flag, epe.dpnt_dsgn_cd
5418: from ben_prtt_enrt_rslt_f pen,
5419: ben_elig_per_elctbl_chc epe,
5420: ben_per_in_ler pil
5421: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5422: and pen.prtt_enrt_rslt_stat_cd is null
5423: and p_effective_date between
5699: and (
5700: ( pen2.per_in_ler_id = pen.per_in_ler_id and
5701: ( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
5702: ( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id and
5703: exists (select 'x' from ben_elig_per_elctbl_chc epe,
5704: ben_prtt_enrt_rslt_f pen3
5705: where epe.per_in_ler_id = pen.per_in_ler_id and
5706: epe.pl_id = pen.pl_id and
5707: ( epe.pgm_id = pen.pgm_id or pen.pgm_id is null) and
5745: and (
5746: ( pen2.per_in_ler_id = pen.per_in_ler_id and
5747: ( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
5748: ( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id or
5749: exists (select 'x' from ben_elig_per_elctbl_chc epe,
5750: ben_prtt_enrt_rslt_f pen3
5751: where epe.per_in_ler_id = pen2.per_in_ler_id and
5752: epe.pl_id = pen2.pl_id and
5753: ( epe.pgm_id = pen2.pgm_id or pen2.pgm_id is null) and
6164: epe.pl_id,
6165: nvl(pen.bnft_amt, 0) bnft_amt,
6166: pen.oipl_id
6167: from ben_prtt_enrt_rslt_f pen,
6168: ben_elig_per_elctbl_chc epe,
6169: ben_per_in_ler pil
6170: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6171: and pen.prtt_enrt_rslt_stat_cd is null
6172: and p_effective_date between