View: BEN_ENRT_OVERRIDE_NON_OAB_V

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

View Text

SELECT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, OPT.OPT_ID OPT_ID
, OPT.NAME OPT_NAME
, PGM.PGM_ID PGM_ID
, PGM.NAME PGM_NAME
, PL.PL_ID PL_ID
, PL.NAME PL_NAME
, TYP.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, DECODE(PGM.PGM_ID
, NULL
, PL.NIP_PL_UOM
, PGM.PGM_UOM) UOM
, F.EFFECTIVE_DATE ORGNL_ENRT_DT
, 'O' ENRT_MTHD_CD
, 'Y' ENRT_OVRIDN_FLAG
, F.EFFECTIVE_DATE ENRT_CVG_STRT_DT
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR') ENRT_CVG_THRU_DT
, PL.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM BEN_PL_F PL
, BEN_PLIP_F PLIP
, BEN_PGM_F PGM
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE PL.ELIG_APLS_FLAG = 'N'
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND PL.PL_ID = PLIP.PL_ID (+)
AND F.EFFECTIVE_DATE BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PLIP.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PLIP.PGM_ID = PGM.PGM_ID (+)
AND PGM.ELIG_APLS_FLAG (+) = 'N'
AND F.EFFECTIVE_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PL.PL_ID = OIPL.PL_ID (+)
AND OIPL.ELIG_APLS_FLAG (+) = 'N'
AND F.EFFECTIVE_DATE BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(OIPL.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND OIPL.OPT_ID = OPT.OPT_ID (+)
AND F.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PL.PL_TYP_ID = TYP.PL_TYP_ID
AND F.EFFECTIVE_DATE BETWEEN TYP.EFFECTIVE_START_DATE
AND TYP.EFFECTIVE_END_DATE
AND F.SESSION_ID = USERENV('SESSIONID')

Columns