VIEW: APPS.BEN_ELIG_PER_D

Source

View Text - Preformatted

SELECT pep.ROWID ROW_ID , pep.ELIG_PER_ID ,pep.EFFECTIVE_START_DATE ,pep.EFFECTIVE_END_DATE ,LER.NAME ler_name ,DPNT_OTHR_PL_CVRD_RL.MEANING dpnt_othr_pl_cvrd_rl_meaning ,pep.PRTN_OVRIDN_THRU_DT ,PL_KEY_EE.MEANING pl_key_ee_meaning ,PL_HGHLY_COMPD.MEANING pl_hghly_compd_meaning ,ELIG.MEANING elig_meaning ,pep.COMP_REF_AMT ,pep.CMBN_AGE_N_LOS_VAL ,COMP_REF.name comp_ref_meaning ,pep.AGE_VAL ,pep.LOS_VAL ,pep.PRTN_END_DT ,pep.PRTN_STRT_DT ,WV_CTFN_TYP.MEANING wv_ctfn_typ_meaning ,pep.HRS_WKD_VAL ,HRS_WKD_BNDRY_PERD.MEANING hrs_wkd_bndry_perd_meaning ,PRTN_OVRIDN.MEANING prtn_ovridn_meaning ,NO_MX_PRTN_OVRID_THRU.MEANING no_mx_prtn_ovrid_thru_meaning ,PRTN_OVRIDN_RSN.MEANING prtn_ovridn_rsn_meaning ,AGE.MEANING age_meaning ,LOS.MEANING los_meaning ,pep.OVRID_SVC_DT ,FRZ_AGE.MEANING frz_age_meaning ,FRZ_CMP_LVL.MEANING frz_cmp_lvl_meaning ,FRZ_HRS_WKD.MEANING frz_hrs_wkd_meaning ,DSTR_RSTCN.MEANING dstr_rstcn_meaning ,pep.PCT_FL_TM_VAL ,WV_PRTN_RSN.MEANING wv_prtn_rsn_meaning ,PL_WVD.MEANING pl_wvd_meaning ,once_r_cntng.meaning hrs_wkd_qual_meaning ,fuser.user_name LAST_UPDATED_BY ,pep.LAST_UPDATE_DATE from BEN_ELIG_PER_F pep , ben_per_in_ler pil , fnd_user fuser ,BEN_LER_F LER ,PER_all_people_f PERSON ,hr_lookups WV_CTFN_TYP ,hr_lookups HRS_WKD_BNDRY_PERD ,hr_lookups PRTN_OVRIDN_RSN ,hr_lookups WV_PRTN_RSN ,fnd_currencies_tl COMP_REF ,hr_lookups AGE ,hr_lookups LOS ,hr_lookups PL_KEY_EE ,hr_lookups PL_HGHLY_COMPD ,hr_lookups ELIG ,hr_lookups PRTN_OVRIDN ,hr_lookups NO_MX_PRTN_OVRID_THRU ,hr_lookups FRZ_AGE ,hr_lookups FRZ_CMP_LVL ,hr_lookups FRZ_HRS_WKD ,hr_lookups DSTR_RSTCN ,hr_lookups PL_WVD ,hr_lookups DPNT_OTHR_PL_CVRD_RL ,hr_lookups once_r_cntng WHERE fuser.user_id (+)= pep.last_updated_by and LER.LER_ID (+)= pep.LER_ID and PERSON.PERSON_ID (+)= pep.PERSON_ID and WV_CTFN_TYP.lookup_code (+)= pep.WV_CTFN_TYP_CD and WV_CTFN_TYP.lookup_type (+)= 'BEN_WV_PRTN_CTFN_TYP' and HRS_WKD_BNDRY_PERD.lookup_code (+)= pep.HRS_WKD_BNDRY_PERD_CD and HRS_WKD_BNDRY_PERD.lookup_type (+)= 'BEN_BNDRY_PERD' and PRTN_OVRIDN_RSN.lookup_code (+)= pep.PRTN_OVRIDN_RSN_CD and PRTN_OVRIDN_RSN.lookup_type (+)= 'BEN_OVRID_RSN' and WV_PRTN_RSN.lookup_code (+)= pep.WV_PRTN_RSN_CD and WV_PRTN_RSN.lookup_type (+)= 'BEN_WV_PRTN_RSN' and comp_ref.currency_code(+) = pep.comp_ref_uom and AGE.lookup_code (+)= pep.AGE_UOM and AGE.lookup_type (+)= 'BEN_TM_UOM' and LOS.lookup_code (+)= pep.LOS_UOM and LOS.lookup_type (+)= 'BEN_TM_UOM' and PL_KEY_EE.lookup_code(+) = pep.PL_KEY_EE_FLAG and PL_KEY_EE.lookup_type(+) = 'YES_NO' and PL_HGHLY_COMPD.lookup_code(+) = pep.PL_HGHLY_COMPD_FLAG and PL_HGHLY_COMPD.lookup_type(+) = 'YES_NO' and ELIG.lookup_code(+) = pep.ELIG_FLAG and ELIG.lookup_type(+) = 'YES_NO' and PRTN_OVRIDN.lookup_code(+) = pep.PRTN_OVRIDN_FLAG and PRTN_OVRIDN.lookup_type(+) = 'YES_NO' and NO_MX_PRTN_OVRID_THRU.lookup_code(+) = pep.NO_MX_PRTN_OVRID_THRU_FLAG and NO_MX_PRTN_OVRID_THRU.lookup_type(+) = 'YES_NO' and FRZ_AGE.lookup_code(+) = pep.FRZ_AGE_FLAG and FRZ_AGE.lookup_type(+) = 'YES_NO' and FRZ_CMP_LVL.lookup_code(+) = pep.FRZ_CMP_LVL_FLAG and FRZ_CMP_LVL.lookup_type(+) = 'YES_NO' and FRZ_HRS_WKD.lookup_code(+) = pep.FRZ_HRS_WKD_FLAG and FRZ_HRS_WKD.lookup_type(+) = 'YES_NO' and DSTR_RSTCN.lookup_code(+) = pep.DSTR_RSTCN_FLAG and DSTR_RSTCN.lookup_type(+) = 'YES_NO' and PL_WVD.lookup_code(+) = pep.PL_WVD_FLAG and PL_WVD.lookup_type(+) = 'YES_NO' and DPNT_OTHR_PL_CVRD_RL.lookup_code(+) = pep.DPNT_OTHR_PL_CVRD_RL_FLAG and DPNT_OTHR_PL_CVRD_RL.lookup_type(+) = 'YES_NO' and once_r_cntng.lookup_code(+) = pep.once_r_cntug_cd and once_r_cntng.lookup_type(+) = 'BEN_ONCE_R_CNTNG' and pil.per_in_ler_id(+)=pep.per_in_ler_id and pil.business_group_id(+)=pep.business_group_id and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or pil.per_in_ler_stat_cd is null )

View Text - HTML Formatted

SELECT PEP.ROWID ROW_ID
, PEP.ELIG_PER_ID
, PEP.EFFECTIVE_START_DATE
, PEP.EFFECTIVE_END_DATE
, LER.NAME LER_NAME
, DPNT_OTHR_PL_CVRD_RL.MEANING DPNT_OTHR_PL_CVRD_RL_MEANING
, PEP.PRTN_OVRIDN_THRU_DT
, PL_KEY_EE.MEANING PL_KEY_EE_MEANING
, PL_HGHLY_COMPD.MEANING PL_HGHLY_COMPD_MEANING
, ELIG.MEANING ELIG_MEANING
, PEP.COMP_REF_AMT
, PEP.CMBN_AGE_N_LOS_VAL
, COMP_REF.NAME COMP_REF_MEANING
, PEP.AGE_VAL
, PEP.LOS_VAL
, PEP.PRTN_END_DT
, PEP.PRTN_STRT_DT
, WV_CTFN_TYP.MEANING WV_CTFN_TYP_MEANING
, PEP.HRS_WKD_VAL
, HRS_WKD_BNDRY_PERD.MEANING HRS_WKD_BNDRY_PERD_MEANING
, PRTN_OVRIDN.MEANING PRTN_OVRIDN_MEANING
, NO_MX_PRTN_OVRID_THRU.MEANING NO_MX_PRTN_OVRID_THRU_MEANING
, PRTN_OVRIDN_RSN.MEANING PRTN_OVRIDN_RSN_MEANING
, AGE.MEANING AGE_MEANING
, LOS.MEANING LOS_MEANING
, PEP.OVRID_SVC_DT
, FRZ_AGE.MEANING FRZ_AGE_MEANING
, FRZ_CMP_LVL.MEANING FRZ_CMP_LVL_MEANING
, FRZ_HRS_WKD.MEANING FRZ_HRS_WKD_MEANING
, DSTR_RSTCN.MEANING DSTR_RSTCN_MEANING
, PEP.PCT_FL_TM_VAL
, WV_PRTN_RSN.MEANING WV_PRTN_RSN_MEANING
, PL_WVD.MEANING PL_WVD_MEANING
, ONCE_R_CNTNG.MEANING HRS_WKD_QUAL_MEANING
, FUSER.USER_NAME LAST_UPDATED_BY
, PEP.LAST_UPDATE_DATE
FROM BEN_ELIG_PER_F PEP
, BEN_PER_IN_LER PIL
, FND_USER FUSER
, BEN_LER_F LER
, PER_ALL_PEOPLE_F PERSON
, HR_LOOKUPS WV_CTFN_TYP
, HR_LOOKUPS HRS_WKD_BNDRY_PERD
, HR_LOOKUPS PRTN_OVRIDN_RSN
, HR_LOOKUPS WV_PRTN_RSN
, FND_CURRENCIES_TL COMP_REF
, HR_LOOKUPS AGE
, HR_LOOKUPS LOS
, HR_LOOKUPS PL_KEY_EE
, HR_LOOKUPS PL_HGHLY_COMPD
, HR_LOOKUPS ELIG
, HR_LOOKUPS PRTN_OVRIDN
, HR_LOOKUPS NO_MX_PRTN_OVRID_THRU
, HR_LOOKUPS FRZ_AGE
, HR_LOOKUPS FRZ_CMP_LVL
, HR_LOOKUPS FRZ_HRS_WKD
, HR_LOOKUPS DSTR_RSTCN
, HR_LOOKUPS PL_WVD
, HR_LOOKUPS DPNT_OTHR_PL_CVRD_RL
, HR_LOOKUPS ONCE_R_CNTNG
WHERE FUSER.USER_ID (+)= PEP.LAST_UPDATED_BY
AND LER.LER_ID (+)= PEP.LER_ID
AND PERSON.PERSON_ID (+)= PEP.PERSON_ID
AND WV_CTFN_TYP.LOOKUP_CODE (+)= PEP.WV_CTFN_TYP_CD
AND WV_CTFN_TYP.LOOKUP_TYPE (+)= 'BEN_WV_PRTN_CTFN_TYP'
AND HRS_WKD_BNDRY_PERD.LOOKUP_CODE (+)= PEP.HRS_WKD_BNDRY_PERD_CD
AND HRS_WKD_BNDRY_PERD.LOOKUP_TYPE (+)= 'BEN_BNDRY_PERD'
AND PRTN_OVRIDN_RSN.LOOKUP_CODE (+)= PEP.PRTN_OVRIDN_RSN_CD
AND PRTN_OVRIDN_RSN.LOOKUP_TYPE (+)= 'BEN_OVRID_RSN'
AND WV_PRTN_RSN.LOOKUP_CODE (+)= PEP.WV_PRTN_RSN_CD
AND WV_PRTN_RSN.LOOKUP_TYPE (+)= 'BEN_WV_PRTN_RSN'
AND COMP_REF.CURRENCY_CODE(+) = PEP.COMP_REF_UOM
AND AGE.LOOKUP_CODE (+)= PEP.AGE_UOM
AND AGE.LOOKUP_TYPE (+)= 'BEN_TM_UOM'
AND LOS.LOOKUP_CODE (+)= PEP.LOS_UOM
AND LOS.LOOKUP_TYPE (+)= 'BEN_TM_UOM'
AND PL_KEY_EE.LOOKUP_CODE(+) = PEP.PL_KEY_EE_FLAG
AND PL_KEY_EE.LOOKUP_TYPE(+) = 'YES_NO'
AND PL_HGHLY_COMPD.LOOKUP_CODE(+) = PEP.PL_HGHLY_COMPD_FLAG
AND PL_HGHLY_COMPD.LOOKUP_TYPE(+) = 'YES_NO'
AND ELIG.LOOKUP_CODE(+) = PEP.ELIG_FLAG
AND ELIG.LOOKUP_TYPE(+) = 'YES_NO'
AND PRTN_OVRIDN.LOOKUP_CODE(+) = PEP.PRTN_OVRIDN_FLAG
AND PRTN_OVRIDN.LOOKUP_TYPE(+) = 'YES_NO'
AND NO_MX_PRTN_OVRID_THRU.LOOKUP_CODE(+) = PEP.NO_MX_PRTN_OVRID_THRU_FLAG
AND NO_MX_PRTN_OVRID_THRU.LOOKUP_TYPE(+) = 'YES_NO'
AND FRZ_AGE.LOOKUP_CODE(+) = PEP.FRZ_AGE_FLAG
AND FRZ_AGE.LOOKUP_TYPE(+) = 'YES_NO'
AND FRZ_CMP_LVL.LOOKUP_CODE(+) = PEP.FRZ_CMP_LVL_FLAG
AND FRZ_CMP_LVL.LOOKUP_TYPE(+) = 'YES_NO'
AND FRZ_HRS_WKD.LOOKUP_CODE(+) = PEP.FRZ_HRS_WKD_FLAG
AND FRZ_HRS_WKD.LOOKUP_TYPE(+) = 'YES_NO'
AND DSTR_RSTCN.LOOKUP_CODE(+) = PEP.DSTR_RSTCN_FLAG
AND DSTR_RSTCN.LOOKUP_TYPE(+) = 'YES_NO'
AND PL_WVD.LOOKUP_CODE(+) = PEP.PL_WVD_FLAG
AND PL_WVD.LOOKUP_TYPE(+) = 'YES_NO'
AND DPNT_OTHR_PL_CVRD_RL.LOOKUP_CODE(+) = PEP.DPNT_OTHR_PL_CVRD_RL_FLAG
AND DPNT_OTHR_PL_CVRD_RL.LOOKUP_TYPE(+) = 'YES_NO'
AND ONCE_R_CNTNG.LOOKUP_CODE(+) = PEP.ONCE_R_CNTUG_CD
AND ONCE_R_CNTNG.LOOKUP_TYPE(+) = 'BEN_ONCE_R_CNTNG'
AND PIL.PER_IN_LER_ID(+)=PEP.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PEP.BUSINESS_GROUP_ID
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )