Product: | BEN - Advanced Benefits |
---|---|
Description: | |
Implementation/DBA Data: | APPS.BEN_ELIG_PER |
SELECT PEP.ELIG_PER_ID
, PEP.EFFECTIVE_START_DATE
, PEP.EFFECTIVE_END_DATE
, PEP.BUSINESS_GROUP_ID
, PEP.PL_ID
, PEP.PGM_ID
, PEP.LER_ID
, PEP.PERSON_ID
, PEP.PER_IN_LER_ID
, PEP.DPNT_OTHR_PL_CVRD_RL_FLAG
, PEP.PRTN_OVRIDN_THRU_DT
, PEP.PL_KEY_EE_FLAG
, PEP.PL_HGHLY_COMPD_FLAG
, PEP.ELIG_FLAG
, PEP.COMP_REF_AMT
, PEP.CMBN_AGE_N_LOS_VAL
, PEP.COMP_REF_UOM
, PEP.AGE_VAL
, PEP.LOS_VAL
, PEP.PRTN_END_DT
, PEP.PRTN_STRT_DT
, PEP.WV_CTFN_TYP_CD
, PEP.HRS_WKD_VAL
, PEP.HRS_WKD_BNDRY_PERD_CD
, PEP.PRTN_OVRIDN_FLAG
, PEP.NO_MX_PRTN_OVRID_THRU_FLAG
, PEP.PRTN_OVRIDN_RSN_CD
, PEP.AGE_UOM
, PEP.LOS_UOM
, PEP.OVRID_SVC_DT
, PEP.FRZ_LOS_FLAG
, PEP.FRZ_AGE_FLAG
, PEP.FRZ_CMP_LVL_FLAG
, PEP.FRZ_PCT_FL_TM_FLAG
, PEP.FRZ_HRS_WKD_FLAG
, PEP.FRZ_COMB_AGE_AND_LOS_FLAG
, PEP.DSTR_RSTCN_FLAG
, PEP.PCT_FL_TM_VAL
, PEP.WV_PRTN_RSN_CD
, PEP.PL_WVD_FLAG
, PEP.WAIT_PERD_CMPLTN_DT
, PEP.WAIT_PERD_STRT_DT
, PEP.PEP_ATTRIBUTE_CATEGORY
, PEP.PEP_ATTRIBUTE1
, PEP.PEP_ATTRIBUTE2
, PEP.PEP_ATTRIBUTE3
, PEP.PEP_ATTRIBUTE4
, PEP.PEP_ATTRIBUTE5
, PEP.PEP_ATTRIBUTE6
, PEP.PEP_ATTRIBUTE7
, PEP.PEP_ATTRIBUTE8
, PEP.PEP_ATTRIBUTE9
, PEP.PEP_ATTRIBUTE10
, PEP.PEP_ATTRIBUTE11
, PEP.PEP_ATTRIBUTE12
, PEP.PEP_ATTRIBUTE13
, PEP.PEP_ATTRIBUTE14
, PEP.PEP_ATTRIBUTE15
, PEP.PEP_ATTRIBUTE16
, PEP.PEP_ATTRIBUTE17
, PEP.PEP_ATTRIBUTE18
, PEP.PEP_ATTRIBUTE19
, PEP.PEP_ATTRIBUTE20
, PEP.PEP_ATTRIBUTE21
, PEP.PEP_ATTRIBUTE22
, PEP.PEP_ATTRIBUTE23
, PEP.PEP_ATTRIBUTE24
, PEP.PEP_ATTRIBUTE25
, PEP.PEP_ATTRIBUTE26
, PEP.PEP_ATTRIBUTE27
, PEP.PEP_ATTRIBUTE28
, PEP.PEP_ATTRIBUTE29
, PEP.PEP_ATTRIBUTE30
, PEP.LAST_UPDATE_DATE
, PEP.LAST_UPDATED_BY
, PEP.LAST_UPDATE_LOGIN
, PEP.CREATED_BY
, PEP.CREATION_DATE
, PEP.REQUEST_ID
, PEP.PROGRAM_APPLICATION_ID
, PEP.PROGRAM_ID
, PEP.PROGRAM_UPDATE_DATE
, PEP.OBJECT_VERSION_NUMBER
, PEP.MUST_ENRL_ANTHR_PL_ID
, PEP.RT_COMP_REF_AMT
, PEP.RT_CMBN_AGE_N_LOS_VAL
, PEP.RT_COMP_REF_UOM
, PEP.RT_AGE_VAL
, PEP.RT_LOS_VAL
, PEP.RT_HRS_WKD_VAL
, PEP.RT_HRS_WKD_BNDRY_PERD_CD
, PEP.RT_AGE_UOM
, PEP.RT_LOS_UOM
, PEP.RT_PCT_FL_TM_VAL
, PEP.RT_FRZ_LOS_FLAG
, PEP.RT_FRZ_AGE_FLAG
, PEP.RT_FRZ_CMP_LVL_FLAG
, PEP.RT_FRZ_PCT_FL_TM_FLAG
, PEP.RT_FRZ_HRS_WKD_FLAG
, PEP.RT_FRZ_COMB_AGE_AND_LOS_FLAG
, PEP.INELG_RSN_CD
, PEP.ONCE_R_CNTUG_CD
, PEP.PLIP_ID
, PEP.PTIP_ID
FROM BEN_ELIG_PER_F PEP
, BEN_PER_IN_LER PIL
WHERE PEP.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEP.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PIL.PER_IN_LER_ID(+)=PEP.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PEP.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )