View: BEN_WFREP_ELCTBL_CHC_INFO_VW

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

View Text

SELECT 1 ORD_NUM
, BFT.BENEFIT_ACTION_ID
, BEC.PERSON_ID
, PGM.NAME PGM_NAME
, PGM.NAME PL_NAME
, PGM.NAME OPT_NAME
, BEC.ENRT_CVG_STRT_DT
, BEC.ENRT_PERD_STRT_DT
, BEC.ENRT_PERD_END_DT
, BEC.ERLST_DEENRT_DT
, BEC.DFLT_ENRT_DT
, HR_GENERAL.DECODE_LOOKUP('BEN_ENRT_TYP_CYCL'
, BEC.ENRT_TYP_CYCL_CD) ENRT_TYP_CYCL_CD_NAME
, HR_GENERAL.DECODE_LOOKUP('BEN_COMP_LVL'
, BEC.COMP_LVL_CD) COMP_LVL_CD_NAME
, BEC.MNDTRY_FLAG
, BEC.DFLT_FLAG
, PGM.NAME ELECTABLE_CHOICE_NAME
FROM BEN_BENEFIT_ACTIONS BFT
, BEN_BATCH_ELCTBL_CHC_INFO BEC
, BEN_PGM_F PGM
WHERE BFT.BENEFIT_ACTION_ID = BEC.BENEFIT_ACTION_ID
AND BEC.PL_ID IS NULL
AND BEC.OIPL_ID IS NULL
AND BEC.PGM_ID IS NOT NULL
AND BEC.PGM_ID = PGM.PGM_ID
AND BFT.PROCESS_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE UNION SELECT 2 ORD_NUM
, BFT.BENEFIT_ACTION_ID
, BEC.PERSON_ID
, PGM.NAME PGM_NAME
, PLN.NAME PL_NAME
, PLN.NAME OPT_NAME
, BEC.ENRT_CVG_STRT_DT
, BEC.ENRT_PERD_STRT_DT
, BEC.ENRT_PERD_END_DT
, BEC.ERLST_DEENRT_DT
, BEC.DFLT_ENRT_DT
, HR_GENERAL.DECODE_LOOKUP('BEN_ENRT_TYP_CYCL'
, BEC.ENRT_TYP_CYCL_CD) ENRT_TYP_CYCL_CD_NAME
, HR_GENERAL.DECODE_LOOKUP('BEN_COMP_LVL'
, BEC.COMP_LVL_CD) COMP_LVL_CD_NAME
, BEC.MNDTRY_FLAG
, BEC.DFLT_FLAG
, DECODE(PGM.NAME
, NULL
, PLN.NAME
, PGM.NAME||' - '||PLN.NAME) ELECTABLE_CHOICE_NAME
FROM BEN_BENEFIT_ACTIONS BFT
, BEN_BATCH_ELCTBL_CHC_INFO BEC
, BEN_PGM_F PGM
, BEN_PL_F PLN
WHERE BFT.BENEFIT_ACTION_ID = BEC.BENEFIT_ACTION_ID
AND BEC.PL_ID IS NOT NULL
AND BEC.OIPL_ID IS NULL
AND BEC.PGM_ID = PGM.PGM_ID(+)
AND BFT.PROCESS_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND BEC.PL_ID = PLN.PL_ID
AND BFT.PROCESS_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE UNION SELECT 3 ORD_NUM
, BFT.BENEFIT_ACTION_ID
, BEC.PERSON_ID
, PGM.NAME PGM_NAME
, PLN.NAME PL_NAME
, OPT.NAME OPT_NAME
, BEC.ENRT_CVG_STRT_DT
, BEC.ENRT_PERD_STRT_DT
, BEC.ENRT_PERD_END_DT
, BEC.ERLST_DEENRT_DT
, BEC.DFLT_ENRT_DT
, HR_GENERAL.DECODE_LOOKUP('BEN_ENRT_TYP_CYCL'
, BEC.ENRT_TYP_CYCL_CD) ENRT_TYP_CYCL_CD_NAME
, HR_GENERAL.DECODE_LOOKUP('BEN_COMP_LVL'
, BEC.COMP_LVL_CD) COMP_LVL_CD_NAME
, BEC.MNDTRY_FLAG
, BEC.DFLT_FLAG
, DECODE(PGM.NAME
, NULL
, PLN.NAME||' - '||OPT.NAME
, PGM.NAME||' - '||PLN.NAME||' - '||OPT.NAME) ELECTABLE_CHOICE_NAME
FROM BEN_BENEFIT_ACTIONS BFT
, BEN_BATCH_ELCTBL_CHC_INFO BEC
, BEN_PGM_F PGM
, BEN_PL_F PLN
, BEN_OIPL_F COP
, BEN_OPT_F OPT
WHERE BFT.BENEFIT_ACTION_ID = BEC.BENEFIT_ACTION_ID
AND BEC.PL_ID IS NOT NULL
AND BEC.OIPL_ID IS NOT NULL
AND BEC.PGM_ID = PGM.PGM_ID(+)
AND BFT.PROCESS_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND BEC.PL_ID = PLN.PL_ID
AND BFT.PROCESS_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND BEC.OIPL_ID = COP.OIPL_ID
AND BFT.PROCESS_DATE BETWEEN COP.EFFECTIVE_START_DATE
AND COP.EFFECTIVE_END_DATE
AND OPT.OPT_ID = COP.OPT_ID
AND BFT.PROCESS_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE

Columns