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.OIPL_ID , R.BUSINESS_GROUP_ID , PLT.NAME||': '||PL.NAME||':'||OPT.NAME NAME FROM BEN_PRTT_ENRT_RSLT_F R , BEN_PL_F PL , BEN_PL_TYP_F PLT , BEN_OIPL_F COP , BEN_OPT_F OPT , FND_SESSIONS FND WHERE FND.SESSION_ID = USERENV('SESSIONID') AND R.PL_ID = PL.PL_ID AND R.PL_TYP_ID = PLT.PL_TYP_ID AND R.OIPL_ID = COP.OIPL_ID(+) AND COP.OPT_ID = OPT.OPT_ID(+) AND PL.INVK_FLX_CR_PL_FLAG = 'N' AND (PL.IMPTD_INCM_CALC_CD IS NULL OR PL.IMPTD_INCM_CALC_CD <> 'PRTT') AND R.PRTT_ENRT_RSLT_STAT_CD IS NULL AND FND.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE AND FND.EFFECTIVE_DATE BETWEEN PLT.EFFECTIVE_START_DATE AND PLT.EFFECTIVE_END_DATE AND FND.EFFECTIVE_DATE BETWEEN R.EFFECTIVE_START_DATE AND R.EFFECTIVE_END_DATE AND FND.EFFECTIVE_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE , FND.EFFECTIVE_DATE) AND NVL(COP.EFFECTIVE_END_DATE , FND.EFFECTIVE_DATE) AND FND.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE , FND.EFFECTIVE_DATE) AND NVL(OPT.EFFECTIVE_END_DATE , FND.EFFECTIVE_DATE) AND NOT EXISTS (SELECT 'X' FROM BEN_PRMRY_CARE_PRVDR_F P WHERE 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.OIPL_ID , R.BUSINESS_GROUP_ID , PLT.NAME||': '||PL.NAME||':'||OPT.NAME NAME FROM BEN_ELIG_CVRD_DPNT_F D , BEN_PRTT_ENRT_RSLT_F R , BEN_PL_F PL , BEN_PL_TYP_F PLT , BEN_OIPL_F COP , BEN_OPT_F OPT , FND_SESSIONS FND WHERE FND.SESSION_ID = USERENV('SESSIONID') AND R.PRTT_ENRT_RSLT_ID = D.PRTT_ENRT_RSLT_ID AND R.PL_ID = PL.PL_ID AND R.PL_TYP_ID = PLT.PL_TYP_ID AND R.OIPL_ID = COP.OIPL_ID(+) AND COP.OPT_ID = OPT.OPT_ID(+) AND PL.INVK_FLX_CR_PL_FLAG = 'N' AND (PL.IMPTD_INCM_CALC_CD IS NULL OR PL.IMPTD_INCM_CALC_CD <> 'PRTT') AND FND.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE AND FND.EFFECTIVE_DATE BETWEEN PLT.EFFECTIVE_START_DATE AND PLT.EFFECTIVE_END_DATE 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 FND.EFFECTIVE_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE , FND.EFFECTIVE_DATE) AND NVL(COP.EFFECTIVE_END_DATE , FND.EFFECTIVE_DATE) AND FND.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE , FND.EFFECTIVE_DATE) AND NVL(OPT.EFFECTIVE_END_DATE , FND.EFFECTIVE_DATE) AND NOT EXISTS (SELECT 'X' FROM BEN_PRMRY_CARE_PRVDR_F P WHERE FND.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND P.ELIG_CVRD_DPNT_ID = D.ELIG_CVRD_DPNT_ID)