View: BENBV_PER_ELIGIBILITY_V

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

View Text

SELECT EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
, PRTN_STRT_DT
, PRTN_END_DT
, PL_ORDR_NUM
, PLIP_ORDR_NUM
, PTIP_ORDR_NUM
, TO_NUMBER(NULL)
, PROGRAM_UPDATE_DATE
, OVRID_SVC_DT
, PRTN_OVRIDN_THRU_DT
, AGE_VAL
, AGE_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, AGE_UOM)
, LOS_VAL
, LOS_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, LOS_UOM)
, RT_AGE_VAL
, RT_AGE_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, RT_AGE_UOM)
, RT_LOS_VAL
, RT_LOS_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, RT_LOS_UOM)
, COMP_REF_UOM
, HRS_WKD_VAL
, RT_COMP_REF_AMT
, CMBN_AGE_N_LOS_VAL
, COMP_REF_AMT
, PCT_FL_TM_VAL
, RT_CMBN_AGE_N_LOS_VAL
, RT_COMP_REF_UOM
, RT_HRS_WKD_VAL
, RT_PCT_FL_TM_VAL
, WAIT_PERD_STRT_DT
, TO_CHAR(WAIT_PERD_CMPLTN_DT)
, /* CODES */ HRS_WKD_BNDRY_PERD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_BNDRY_PERD'
, HRS_WKD_BNDRY_PERD_CD)
, WV_PRTN_RSN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_WV_PRTN_RSN'
, WV_PRTN_RSN_CD)
, PRTN_OVRIDN_RSN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_PRTN_OVRRIDN_RSN'
, PRTN_OVRIDN_RSN_CD)
, WV_CTFN_TYP_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_WV_PRTN_CTFN_TYP'
, WV_CTFN_TYP_CD)
, RT_HRS_WKD_BNDRY_PERD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_BNDRY_PERD'
, RT_HRS_WKD_BNDRY_PERD_CD)
, INELG_RSN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_INELG_RSN'
, INELG_RSN_CD)
, ONCE_R_CNTUG_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ONCE_R_CNTNG'
, ONCE_R_CNTUG_CD)
, DSTR_RSTCN_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DSTR_RSTCN_FLAG)
, DPNT_OTHR_PL_CVRD_RL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DPNT_OTHR_PL_CVRD_RL_FLAG)
, PL_HGHLY_COMPD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PL_HGHLY_COMPD_FLAG)
, PL_KEY_EE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PL_KEY_EE_FLAG)
, ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ELIG_FLAG)
, PL_WVD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PL_WVD_FLAG)
, PRTN_OVRIDN_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRTN_OVRIDN_FLAG)
, NO_MX_PRTN_OVRID_THRU_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NO_MX_PRTN_OVRID_THRU_FLAG)
, FRZ_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, FRZ_LOS_FLAG)
, FRZ_AGE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, FRZ_AGE_FLAG)
, FRZ_CMP_LVL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, FRZ_CMP_LVL_FLAG)
, FRZ_PCT_FL_TM_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, FRZ_PCT_FL_TM_FLAG)
, FRZ_HRS_WKD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, FRZ_HRS_WKD_FLAG)
, FRZ_COMB_AGE_AND_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, FRZ_COMB_AGE_AND_LOS_FLAG)
, RT_FRZ_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RT_FRZ_LOS_FLAG)
, RT_FRZ_AGE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RT_FRZ_AGE_FLAG)
, RT_FRZ_CMP_LVL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RT_FRZ_CMP_LVL_FLAG)
, RT_FRZ_PCT_FL_TM_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RT_FRZ_PCT_FL_TM_FLAG)
, RT_FRZ_HRS_WKD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RT_FRZ_HRS_WKD_FLAG)
, RT_FRZ_COMB_AGE_AND_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, RT_FRZ_COMB_AGE_AND_LOS_FLAG)
, /* ID COLUMNS */ PERSON_ID
, ELIG_PER_ID
, TO_NUMBER(NULL)
, BUSINESS_GROUP_ID
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PER_IN_LER_ID
, PL_ID
, PLIP_ID
, PGM_ID
, PTIP_ID
, TO_NUMBER(NULL)
, LER_ID
, MUST_ENRL_ANTHR_PL_ID
, /* WHO COLUMNS */ CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
FROM BEN_ELIG_PER_F PEP
WHERE PEP.PLIP_ID IS NULL
AND PEP.PTIP_ID IS NULL
AND PEP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEP.BUSINESS_GROUP_ID) UNION SELECT EPO.EFFECTIVE_START_DATE
, EPO.EFFECTIVE_END_DATE
, EPO.PRTN_STRT_DT
, EPO.PRTN_END_DT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, EPO.OIPL_ORDR_NUM
, EPO.PROGRAM_UPDATE_DATE
, EPO.OVRID_SVC_DT
, EPO.PRTN_OVRIDN_THRU_DT
, EPO.AGE_VAL
, EPO.AGE_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, EPO.AGE_UOM)
, EPO.LOS_VAL
, EPO.LOS_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, EPO.LOS_UOM)
, EPO.RT_AGE_VAL
, EPO.RT_AGE_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, EPO.RT_AGE_UOM)
, EPO.RT_LOS_VAL
, EPO.RT_LOS_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM'
, EPO.RT_LOS_UOM)
, EPO.COMP_REF_UOM
, EPO.HRS_WKD_VAL
, EPO.RT_COMP_REF_AMT
, EPO.CMBN_AGE_N_LOS_VAL
, EPO.COMP_REF_AMT
, EPO.PCT_FL_TM_VAL
, EPO.RT_CMBN_AGE_N_LOS_VAL
, EPO.RT_COMP_REF_UOM
, EPO.RT_HRS_WKD_VAL
, EPO.RT_PCT_FL_TM_VAL
, EPO.WAIT_PERD_STRT_DT
, EPO.WAIT_PERD_CMPLTN_DT
, /* CODES */ EPO.HRS_WKD_BNDRY_PERD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_BNDRY_PERD'
, EPO.HRS_WKD_BNDRY_PERD_CD)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, EPO.PRTN_OVRIDN_RSN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_PRTN_OVRRIDN_RSN'
, EPO.PRTN_OVRIDN_RSN_CD)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, EPO.RT_HRS_WKD_BNDRY_PERD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_BNDRY_PERD'
, EPO.RT_HRS_WKD_BNDRY_PERD_CD)
, EPO.INELG_RSN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_INELG_RSN'
, EPO.INELG_RSN_CD)
, EPO.ONCE_R_CNTUG_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ONCE_R_CNTNG'
, EPO.ONCE_R_CNTUG_CD)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, EPO.ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.ELIG_FLAG)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, EPO.PRTN_OVRIDN_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.PRTN_OVRIDN_FLAG)
, EPO.NO_MX_PRTN_OVRID_THRU_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.NO_MX_PRTN_OVRID_THRU_FLAG)
, EPO.FRZ_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.FRZ_LOS_FLAG)
, EPO.FRZ_AGE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.FRZ_AGE_FLAG)
, EPO.FRZ_CMP_LVL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.FRZ_CMP_LVL_FLAG)
, EPO.FRZ_PCT_FL_TM_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.FRZ_PCT_FL_TM_FLAG)
, EPO.FRZ_HRS_WKD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.FRZ_HRS_WKD_FLAG)
, EPO.FRZ_COMB_AGE_AND_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.FRZ_COMB_AGE_AND_LOS_FLAG)
, EPO.RT_FRZ_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.RT_FRZ_LOS_FLAG)
, EPO.RT_FRZ_AGE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.RT_FRZ_AGE_FLAG)
, EPO.RT_FRZ_CMP_LVL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.RT_FRZ_CMP_LVL_FLAG)
, EPO.RT_FRZ_PCT_FL_TM_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.RT_FRZ_PCT_FL_TM_FLAG)
, EPO.RT_FRZ_HRS_WKD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.RT_FRZ_HRS_WKD_FLAG)
, EPO.RT_FRZ_COMB_AGE_AND_LOS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EPO.RT_FRZ_COMB_AGE_AND_LOS_FLAG)
, /* ID COLUMNS */ PEP.PERSON_ID
, EPO.ELIG_PER_ID
, EPO.ELIG_PER_OPT_ID
, EPO.BUSINESS_GROUP_ID
, EPO.REQUEST_ID
, EPO.PROGRAM_APPLICATION_ID
, EPO.PROGRAM_ID
, EPO.PER_IN_LER_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, EPO.OPT_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, /* WHO COLUMNS */ EPO.CREATION_DATE
, EPO.CREATED_BY
, EPO.LAST_UPDATE_DATE
, EPO.LAST_UPDATED_BY
FROM BEN_ELIG_PER_OPT_F EPO
, BEN_ELIG_PER_F PEP
WHERE EPO.ELIG_PER_ID = PEP.ELIG_PER_ID
AND EPO.PER_IN_LER_ID = PEP.PER_IN_LER_ID
AND PEP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEP.BUSINESS_GROUP_ID)
AND EPO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, EPO.BUSINESS_GROUP_ID)
AND PEP.PL_ID IS NOT NULL WITH READ ONLY

Columns