Product: | BEN - Advanced Benefits |
---|---|
Description: | This view identifies the plans or options in plan a person may elect as the result of a life event. |
Implementation/DBA Data: | APPS.BENBV_ELIG_PER_ELCTBL_CHC_V |
SELECT EPE.PL_ORDR_NUM
, EPE.PLIP_ORDR_NUM
, EPE.PTIP_ORDR_NUM
, EPE.OIPL_ORDR_NUM
, EPE.PROGRAM_UPDATE_DATE
, EPE.ENRT_CVG_STRT_DT_RL
, EPE.DPNT_CVG_STRT_DT_RL
, EPE.ENRT_CVG_STRT_DT
, EPE.ERLST_DEENRT_DT
, EPE.PROCG_END_DT
, EPE.COMMENTS
, EPE.ELIG_OVRID_DT
, EPE.MGR_OVRID_DT
, EPE.FONM_CVG_STRT_DT
, /* DESCRIPTIVE FLEXFIELD */ '_DF:BEN:BEN_ELIG_PER_ELCTBL_CHC:EPE'
, /* CODES */ EPE.COMP_LVL_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_COMP_LVL'
, EPE.COMP_LVL_CD)
, EPE.ENRT_CVG_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_STRT'
, EPE.ENRT_CVG_STRT_DT_CD)
, EPE.DPNT_CVG_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_STRT'
, EPE.DPNT_CVG_STRT_DT_CD)
, EPE.DPNT_DSGN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_DSGN'
, EPE.DPNT_DSGN_CD)
, EPE.LER_CHG_DPNT_CVG_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_LER_CHG_DPNT_CVG'
, EPE.LER_CHG_DPNT_CVG_CD)
, EPE.CRYFWD_ELIG_DPNT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_CRYFWD_ELIG_DPNT'
, EPE.CRYFWD_ELIG_DPNT_CD)
, EPE.INELIG_RSN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_INELG_RSN'
, EPE.INELIG_RSN_CD)
, EPE.APPROVAL_STATUS_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_APPR_STAT'
, EPE.APPROVAL_STATUS_CD)
, EPE.ROLL_CRS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.ROLL_CRS_FLAG)
, EPE.CRNTLY_ENRD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.CRNTLY_ENRD_FLAG)
, EPE.DFLT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.DFLT_FLAG)
, EPE.ELCTBL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.ELCTBL_FLAG)
, EPE.MNDTRY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.MNDTRY_FLAG)
, EPE.ALWS_DPNT_DSGN_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.ALWS_DPNT_DSGN_FLAG)
, EPE.AUTO_ENRT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.AUTO_ENRT_FLAG)
, EPE.CTFN_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.CTFN_RQD_FLAG)
, EPE.IN_PNDG_WKFLOW_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.IN_PNDG_WKFLOW_FLAG)
, EPE.ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPE.ELIG_FLAG)
, /* ID COLUMNS */ EPE.ELIG_PER_ELCTBL_CHC_ID
, EPE.BUSINESS_GROUP_ID
, EPE.PER_IN_LER_ID
, EPE.YR_PERD_ID
, EPE.INTERIM_ELIG_PER_ELCTBL_CHC_ID
, EPE.PIL_ELCTBL_CHC_POPL_ID
, EPE.OIPLIP_ID
, EPE.REQUEST_ID
, EPE.PROGRAM_APPLICATION_ID
, EPE.PROGRAM_ID
, EPE.MUST_ENRL_ANTHR_PL_ID
, EPE.SPCL_RT_PL_ID
, EPE.SPCL_RT_OIPL_ID
, EPE.BNFT_PRVDR_POOL_ID
, EPE.CMBN_PTIP_ID
, EPE.CMBN_PTIP_OPT_ID
, EPE.CMBN_PLIP_ID
, EPE.OIPL_ID
, EPE.PGM_ID
, EPE.PL_ID
, EPE.PL_TYP_ID
, EPE.PLIP_ID
, EPE.PTIP_ID
, EPE.PRTT_ENRT_RSLT_ID
, EPE.ELIG_OVRID_PERSON_ID
, EPE.MGR_OVRID_PERSON_ID
, EPE.WS_MGR_ID
, EPE.ASSIGNMENT_ID
, /* WHO COLUMNS */ EPE.LAST_UPDATE_DATE
, EPE.LAST_UPDATED_BY
, EPE.CREATED_BY
, EPE.CREATION_DATE
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
WHERE EPE.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, EPE.BUSINESS_GROUP_ID) WITH READ ONLY