View: BEN_ENRT_OVERRIDE_V

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

View Text

SELECT DISTINCT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, NVL(EPO.PER_IN_LER_ID
, PEP.PER_IN_LER_ID) PER_IN_LER_ID
, TO_CHAR(EPO.OPT_ID) OPT_ID
, DECODE(EPO.OPT_ID
, NULL
, NULL
, OPT.NAME) OPT_NAME
, TO_CHAR(PEP.PGM_ID) PGM_ID
, DECODE(PEP.PGM_ID
, NULL
, NULL
, PGM.NAME) PGM_NAME
, PEP.PL_ID PL_ID
, PL.NAME PL_NAME
, TYP.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, DECODE(PEP.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
, PEP.PERSON_ID PERSON_ID
, PEP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, EPO.OIPL_ORDR_NUM
FROM BEN_PL_F PL
, BEN_PGM_F PGM
, BEN_OPT_F OPT
, BEN_ELIG_PER_F PEP
, BEN_ELIG_PER_OPT_F EPO
, BEN_PER_IN_LER PIL
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE PEP.ELIG_FLAG = 'Y'
AND F.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PEP.PL_ID = PL.PL_ID
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND (PEP.PGM_ID IS NULL OR (PEP.PGM_ID = PGM.PGM_ID
AND F.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE))
AND PEP.ELIG_PER_ID = EPO.ELIG_PER_ID (+)
AND EPO.ELIG_FLAG (+) = 'Y'
AND F.EFFECTIVE_DATE BETWEEN NVL(EPO.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(EPO.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND (EPO.OPT_ID IS NULL OR (EPO.OPT_ID = OPT.OPT_ID
AND F.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_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')
AND PIL.PER_IN_LER_ID=NVL(EPO.PER_IN_LER_ID
, PEP.PER_IN_LER_ID)
AND PIL.BUSINESS_GROUP_ID=NVL(EPO.BUSINESS_GROUP_ID
, PEP.BUSINESS_GROUP_ID)+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND ( PL.SVGS_PL_FLAG = 'N' OR EXISTS ( SELECT 'X'
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE PEN.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND PEN.PL_ID = PL.PL_ID
AND PL.SVGS_PL_FLAG = 'Y'
AND PEN.COMP_LVL_CD = 'PLAN'
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE )) UNION SELECT DISTINCT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, PEP.PER_IN_LER_ID PER_IN_LER_ID
, NULL OPT_ID
, NULL OPT_NAME
, NULL PGM_ID
, NULL PGM_NAME
, PEP.PL_ID PL_ID
, PL.NAME PL_NAME
, TYP.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, PL.NIP_PL_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
, PEP.PERSON_ID PERSON_ID
, PEP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM BEN_PL_F PL
, BEN_ELIG_PER_F PEP
, BEN_PER_IN_LER PIL
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE PEP.ELIG_FLAG = 'Y'
AND F.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PEP.PL_ID = PL.PL_ID
AND PL.SVGS_PL_FLAG = 'Y'
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_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')
AND PIL.PER_IN_LER_ID=PEP.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=PEP.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ) MINUS SELECT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, PEN.PER_IN_LER_ID PER_IN_LER_ID
, TO_CHAR(OPT.OPT_ID) OPT_ID
, OPT.NAME OPT_NAME
, TO_CHAR(PEN.PGM_ID) PGM_ID
, PGM.NAME PGM_NAME
, PEN.PL_ID PL_ID
, PL.NAME PL_NAME
, PEN.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, PEN.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
, PEN.PERSON_ID PERSON_ID
, PEN.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PEN.OIPL_ORDR_NUM
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PL_F PL
, BEN_PGM_F PGM
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PER_IN_LER PIL
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.ENRT_CVG_THRU_DT = TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.PL_ID = PL.PL_ID
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND PEN.PGM_ID = PGM.PGM_ID(+)
AND F.EFFECTIVE_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PEN.OIPL_ID = OIPL.OIPL_ID (+)
AND F.EFFECTIVE_DATE BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(OIPL.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND NVL(OIPL.OPT_ID
, -1) = 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 PEN.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')
AND PIL.PER_IN_LER_ID=PEN.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=PEN.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )

Columns