VIEW: APPS.BEN_PRMRY_CARE_PRVDR_LOV_LE_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.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.prtt_enrt_rslt_stat_cd IS NULL /* Bug 4686468 */ 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)

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.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.PRTT_ENRT_RSLT_STAT_CD IS NULL /* BUG 4686468 */
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)