VIEW: APPS.BEN_PRMRY_CARE_PRVDR_D

Source

View Text - Preformatted

SELECT distinct PPR.ROWID ROW_ID, PPR.PRMRY_CARE_PRVDR_ID ,PPR.EFFECTIVE_START_DATE , PPR.EFFECTIVE_END_DATE ,PPR.NAME ,PPR.EXT_IDENT ,PRMRY_CARE_PRVDR_TYP.MEANING , decode(ppr.prtt_enrt_rslt_id, null, dpnt_PL.NAME, pl.name) ,PPR.LAST_UPDATE_DATE , fuser.user_name from BEN_PRMRY_CARE_PRVDR_F PPR, fnd_user_view fuser , BEN_PRTT_ENRT_RSLT_F PRTT_ENRT_RSLT ,BEN_ELIG_CVRD_DPNT_F ELIG_CVRD_DPNT , BEN_PRTT_ENRT_RSLT_F DPNT_RSLT ,BEN_PL_F PL ,BEN_PL_F DPNT_PL , hr_lookups PRMRY_CARE_PRVDR_TYP WHERE fuser.user_id(+) = ppr.last_updated_by AND prtt_enrt_rslt.prtt_enrt_rslt_id(+) = ppr.prtt_enrt_rslt_id AND prtt_enrt_rslt.pl_id = pl.pl_id(+) AND elig_cvrd_dpnt.elig_cvrd_dpnt_id(+) = ppr.elig_cvrd_dpnt_id AND elig_cvrd_dpnt.prtt_enrt_rslt_id = dpnt_rslt.prtt_enrt_rslt_id(+) AND dpnt_rslt.pl_id = dpnt_pl.pl_id(+) AND prmry_care_prvdr_typ.lookup_code(+) = ppr.prmry_care_prvdr_typ_cd AND prmry_care_prvdr_typ.lookup_type(+) = 'BEN_PRMRY_CARE_PRVDR_TYP' AND ppr.effective_start_date BETWEEN NVL (prtt_enrt_rslt.effective_start_date, ppr.effective_start_date ) AND NVL (prtt_enrt_rslt.effective_end_date, ppr.effective_end_date ) AND ppr.effective_start_date BETWEEN NVL (elig_cvrd_dpnt.effective_start_date, ppr.effective_start_date ) AND NVL (elig_cvrd_dpnt.effective_end_date, ppr.effective_end_date ) AND ppr.effective_start_date BETWEEN NVL (dpnt_rslt.effective_start_date, ppr.effective_start_date ) AND NVL (dpnt_rslt.effective_end_date, ppr.effective_end_date ) AND ppr.effective_start_date BETWEEN NVL (pl.effective_start_date, ppr.effective_start_date ) AND NVL (pl.effective_end_date, ppr.effective_end_date ) AND ppr.effective_start_date BETWEEN NVL (dpnt_pl.effective_start_date, ppr.effective_start_date ) AND NVL (dpnt_pl.effective_end_date, ppr.effective_end_date )

View Text - HTML Formatted

SELECT DISTINCT PPR.ROWID ROW_ID
, PPR.PRMRY_CARE_PRVDR_ID
, PPR.EFFECTIVE_START_DATE
, PPR.EFFECTIVE_END_DATE
, PPR.NAME
, PPR.EXT_IDENT
, PRMRY_CARE_PRVDR_TYP.MEANING
, DECODE(PPR.PRTT_ENRT_RSLT_ID
, NULL
, DPNT_PL.NAME
, PL.NAME)
, PPR.LAST_UPDATE_DATE
, FUSER.USER_NAME
FROM BEN_PRMRY_CARE_PRVDR_F PPR
, FND_USER_VIEW FUSER
, BEN_PRTT_ENRT_RSLT_F PRTT_ENRT_RSLT
, BEN_ELIG_CVRD_DPNT_F ELIG_CVRD_DPNT
, BEN_PRTT_ENRT_RSLT_F DPNT_RSLT
, BEN_PL_F PL
, BEN_PL_F DPNT_PL
, HR_LOOKUPS PRMRY_CARE_PRVDR_TYP
WHERE FUSER.USER_ID(+) = PPR.LAST_UPDATED_BY
AND PRTT_ENRT_RSLT.PRTT_ENRT_RSLT_ID(+) = PPR.PRTT_ENRT_RSLT_ID
AND PRTT_ENRT_RSLT.PL_ID = PL.PL_ID(+)
AND ELIG_CVRD_DPNT.ELIG_CVRD_DPNT_ID(+) = PPR.ELIG_CVRD_DPNT_ID
AND ELIG_CVRD_DPNT.PRTT_ENRT_RSLT_ID = DPNT_RSLT.PRTT_ENRT_RSLT_ID(+)
AND DPNT_RSLT.PL_ID = DPNT_PL.PL_ID(+)
AND PRMRY_CARE_PRVDR_TYP.LOOKUP_CODE(+) = PPR.PRMRY_CARE_PRVDR_TYP_CD
AND PRMRY_CARE_PRVDR_TYP.LOOKUP_TYPE(+) = 'BEN_PRMRY_CARE_PRVDR_TYP'
AND PPR.EFFECTIVE_START_DATE BETWEEN NVL (PRTT_ENRT_RSLT.EFFECTIVE_START_DATE
, PPR.EFFECTIVE_START_DATE )
AND NVL (PRTT_ENRT_RSLT.EFFECTIVE_END_DATE
, PPR.EFFECTIVE_END_DATE )
AND PPR.EFFECTIVE_START_DATE BETWEEN NVL (ELIG_CVRD_DPNT.EFFECTIVE_START_DATE
, PPR.EFFECTIVE_START_DATE )
AND NVL (ELIG_CVRD_DPNT.EFFECTIVE_END_DATE
, PPR.EFFECTIVE_END_DATE )
AND PPR.EFFECTIVE_START_DATE BETWEEN NVL (DPNT_RSLT.EFFECTIVE_START_DATE
, PPR.EFFECTIVE_START_DATE )
AND NVL (DPNT_RSLT.EFFECTIVE_END_DATE
, PPR.EFFECTIVE_END_DATE )
AND PPR.EFFECTIVE_START_DATE BETWEEN NVL (PL.EFFECTIVE_START_DATE
, PPR.EFFECTIVE_START_DATE )
AND NVL (PL.EFFECTIVE_END_DATE
, PPR.EFFECTIVE_END_DATE )
AND PPR.EFFECTIVE_START_DATE BETWEEN NVL (DPNT_PL.EFFECTIVE_START_DATE
, PPR.EFFECTIVE_START_DATE )
AND NVL (DPNT_PL.EFFECTIVE_END_DATE
, PPR.EFFECTIVE_END_DATE )