VIEW: APPS.BEN_PRMRY_CARE_PRVDR_PE

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 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)

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
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)