Product: | BEN - Advanced Benefits |
---|---|
Description: | This view identifies all of the possible life event reasons that may cause a change to a person's eligibility, electability, coverage, rates or premiums. |
Implementation/DBA Data: | APPS.BENBV_LER_V |
SELECT LER.EFFECTIVE_START_DATE
, LER.EFFECTIVE_END_DATE
, LER.NAME
, LER.LER_EVAL_RL
, LER.DESC_TXT
, LER.TMLNS_PERD_RL
, LER.TMLNS_DYS_NUM
, LER.SHORT_CODE
, LER.SHORT_NAME
, /* DESCRIPTIVE FLEXFIELD */ '_DF:BEN:BEN_LER_F:LER'
, /* CODES */ LER.TYP_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_LER_TYP'
, LER.TYP_CD)
, LER.PTNL_LER_TRTMT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_PTNL_LER_TRTMT'
, LER.PTNL_LER_TRTMT_CD)
, LER.WHN_TO_PRCS_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_WHN_TO_PRCS_LER'
, LER.WHN_TO_PRCS_CD)
, LER.TMLNS_PERD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_LER_TMLNS_PERD'
, LER.TMLNS_PERD_CD)
, LER.TMLNS_EVAL_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_LER_TMLNS_EVAL'
, LER.TMLNS_EVAL_CD)
, LER.OCRD_DT_DET_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_OCRD_DT_DET'
, LER.OCRD_DT_DET_CD)
, LER.SLCTBL_SLF_SVC_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_SLCTBL_SLF_SVC_CD'
, LER.SLCTBL_SLF_SVC_CD)
, LER.LER_STAT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_STAT'
, LER.LER_STAT_CD)
, LER.LF_EVT_OPER_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_LF_EVT_OPER'
, LER.LF_EVT_OPER_CD)
, LER.CK_RLTD_PER_ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, LER.CK_RLTD_PER_ELIG_FLAG)
, LER.CM_APLY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, LER.CM_APLY_FLAG)
, LER.OVRIDG_LE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, LER.OVRIDG_LE_FLAG)
, LER.QUALG_EVT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, LER.QUALG_EVT_FLAG)
, /* ID COLUMNS */ LER.LER_ID
, LER.BUSINESS_GROUP_ID
, /* WHO COLUMNS */ LER.LAST_UPDATE_DATE
, LER.LAST_UPDATED_BY
, LER.CREATED_BY
, LER.CREATION_DATE
FROM BEN_LER_F LER
WHERE LER.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, LER.BUSINESS_GROUP_ID) WITH READ ONLY