VIEW: APPS.BENBV_PER_ELIGIBILITY_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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