SELECT R.PERSON_ID PERSON_ID , R.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID , 0 ELIG_CVRD_DPNT_ID , R.PL_TYP_ID PL_TYP_ID , R.PL_ID PL_ID , R.BUSINESS_GROUP_ID , R.OBJECT_VERSION_NUMBER , R.EFFECTIVE_START_DATE , R.EFFECTIVE_END_DATE , R.LER_ID , R.PTIP_ID , R.OIPL_ID FROM BEN_PRTT_ENRT_RSLT_F R , FND_SESSIONS FND WHERE FND.SESSION_ID = USERENV('SESSIONID') AND FND.EFFECTIVE_DATE BETWEEN R.EFFECTIVE_START_DATE AND R.EFFECTIVE_END_DATE AND PRTT_ENRT_RSLT_STAT_CD IS NULL AND EXISTS (SELECT 'X' FROM BEN_PRMRY_CARE_PRVDR_F P WHERE FND.SESSION_ID = USERENV('SESSIONID') AND FND.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND P.PRTT_ENRT_RSLT_ID = R.PRTT_ENRT_RSLT_ID) UNION SELECT D.DPNT_PERSON_ID PERSON_ID , 0 PRTT_ENRT_RSLT_ID , D.ELIG_CVRD_DPNT_ID ELIG_CVRD_DPNT_ID , R.PL_TYP_ID PL_TYP_ID , R.PL_ID PL_ID , R.BUSINESS_GROUP_ID , R.OBJECT_VERSION_NUMBER , R.EFFECTIVE_START_DATE , R.EFFECTIVE_END_DATE , R.LER_ID , R.PTIP_ID , R.OIPL_ID FROM BEN_ELIG_CVRD_DPNT_F D , BEN_PRTT_ENRT_RSLT_F R , FND_SESSIONS FND WHERE R.PRTT_ENRT_RSLT_ID = D.PRTT_ENRT_RSLT_ID AND NOT EXISTS ( SELECT NULL FROM BEN_PER_IN_LER PIL WHERE PIL.PER_IN_LER_ID=D.PER_IN_LER_ID AND PIL.PER_IN_LER_STAT_CD IN ('VOIDD' , 'BCKDT')) AND FND.SESSION_ID = USERENV('SESSIONID') AND FND.EFFECTIVE_DATE BETWEEN R.EFFECTIVE_START_DATE AND R.EFFECTIVE_END_DATE AND FND.EFFECTIVE_DATE BETWEEN D.EFFECTIVE_START_DATE AND D.EFFECTIVE_END_DATE AND R.PRTT_ENRT_RSLT_STAT_CD IS NULL AND EXISTS (SELECT 'X' FROM BEN_PRMRY_CARE_PRVDR_F P WHERE FND.SESSION_ID = USERENV('SESSIONID') AND FND.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND P.ELIG_CVRD_DPNT_ID = D.ELIG_CVRD_DPNT_ID)