VIEW: APPS.BEN_WFREP_ELCTBL_CHC_INFO_VW

Source

View Text - Preformatted

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

View Text - HTML Formatted

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