VIEW: APPS.BEN_PRMRY_CARE_PRVDR_PEN_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)