[Home] [Help]
664: ,pen.sspndd_flag
665: ,pen.object_version_number
666: ,pen.per_in_ler_id
667: ,'N' skip
668: from ben_prtt_enrt_rslt_f pen
669: where pen.person_id = p_person_id
670: and pen.business_group_id = p_business_group_id
671: and pen.prtt_enrt_rslt_stat_cd is null
672: and exists (select null
678: and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
679: and p_effective_date between pen.effective_start_date
680: and pen.effective_end_date -1
681: and ( pen.effective_end_date = hr_api.g_eot or --Bug 4398840
682: not exists (select 'x' from ben_prtt_enrt_rslt_f pen1 -- to exclude the ended result
683: where pen1.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
684: and pen1.effective_end_date = hr_api.g_eot
685: and pen1.enrt_cvg_thru_dt <> hr_api.g_eot
686: )
712: and pil.per_in_ler_id=b.per_in_ler_id
713: and pil.business_group_id=b.business_group_id
714: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
715: AND EXISTS (SELECT NULL ------Bug 8620516
716: FROM ben_prtt_enrt_rslt_f pen
717: WHERE pen.prtt_enrt_rslt_id = b.prtt_enrt_rslt_id
718: AND pen.per_in_ler_id = pil.per_in_ler_id
719: AND pen.business_group_id = pil.business_group_id
720: AND pen.prtt_enrt_rslt_stat_cd IS NULL)
759: is
760: select pgm_id,
761: pl_id,
762: oipl_id
763: from ben_prtt_enrt_rslt_f
764: where prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
765: and business_group_id = p_business_group_id
766: and p_effective_date between effective_start_date
767: and effective_end_date
1867: --
1868: cursor c_pen
1869: is
1870: select distinct pen.person_id
1871: from ben_prtt_enrt_rslt_f pen
1872: ,ben_per_in_ler pil
1873: ,ben_prtt_enrt_actn_f actn
1874: where pen.business_group_id = p_business_group_id
1875: and pen.business_group_id = actn.business_group_id