View: BEN_PRMRY_CARE_PRVDR_LOV_LE_V

Product:	BEN - Advanced Benefits
Description:	A supplementary view used to simplify forms coding.
Implementation/DBA Data:	APPS.BEN_PRMRY_CARE_PRVDR_LOV_LE_V

View Text

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
, BEN_PER_IN_LER PIL
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 R.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND PIL.LF_EVT_OCRD_DT BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT 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 PIL.LF_EVT_OCRD_DT BETWEEN NVL(COP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(COP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
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
, BEN_PER_IN_LER PIL
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 R.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND PIL.LF_EVT_OCRD_DT BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT 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 PIL.LF_EVT_OCRD_DT BETWEEN NVL(COP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(COP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
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)

Columns