View: BEN_PRMRY_CARE_PRVDR_PE

Product:	BEN - Advanced Benefits
Description:
Implementation/DBA Data:	APPS.BEN_PRMRY_CARE_PRVDR_PE

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.BUSINESS_GROUP_ID
, R.OBJECT_VERSION_NUMBER
, R.EFFECTIVE_START_DATE
, R.EFFECTIVE_END_DATE
, R.LER_ID
, R.PTIP_ID
FROM BEN_PRTT_ENRT_RSLT_F R
, FND_SESSIONS FND
WHERE 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.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
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.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.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND P.ELIG_CVRD_DPNT_ID = D.ELIG_CVRD_DPNT_ID)

Columns