VIEW: APPS.BEN_PRTT_ENRT_RSLT_D

Source

View Text - Preformatted

SELECT DISTINCT PEN.ROWID ROW_ID , PEN.PRTT_ENRT_RSLT_ID , PEN.EFFECTIVE_START_DATE , PEN.EFFECTIVE_END_DATE , PERSON.FULL_NAME , PGM.NAME , PL.NAME , PTIP.PTIP_STAT_CD , PL_TYP.NAME , LER.NAME , SSPNDD.MEANING , PRTT_IS_CVRD.MEANING , PEN.BNFT_AMT , BNFT.NAME , PEN.ORGNL_ENRT_DT , ENRT_MTHD.MEANING , ENRT_OVRIDN.MEANING , ENRT_OVRID_RSN.MEANING , PEN.ERLST_DEENRT_DT , PEN.ENRT_CVG_STRT_DT , PEN.ENRT_CVG_THRU_DT , PEN.ENRT_OVRID_THRU_DT , PEN.LAST_UPDATE_DATE , FUSER.USER_NAME , BEN_PER_IN_LER_STAT.MEANING , BNFT_TYP.MEANING , BNFT_NNMNTRY.MEANING , comp_lvl.meaning , NO_LNGR_ELIG.MEANING , PEN.BNFT_ORDR_NUM , PRTT_ENRT_RSLT_STAT.MEANING FROM BEN_PRTT_ENRT_RSLT_F PEN, FND_USER FUSER , PER_ALL_PEOPLE_F PERSON, BEN_PGM_F PGM , BEN_PL_F PL , BEN_PTIP_F PTIP , BEN_PL_TYP_F PL_TYP , BEN_LER_F LER , HR_LOOKUPS ENRT_MTHD , HR_LOOKUPS ENRT_OVRID_RSN , FND_CURRENCIES_VL BNFT , HR_LOOKUPS SSPNDD , HR_LOOKUPS PRTT_IS_CVRD , HR_LOOKUPS ENRT_OVRIDN , BEN_PER_IN_LER PER_IN_LER , HR_LOOKUPS BNFT_TYP , HR_LOOKUPS BNFT_NNMNTRY , hr_lookups comp_lvl , HR_LOOKUPS NO_LNGR_ELIG , HR_LOOKUPS PRTT_ENRT_RSLT_STAT, HR_LOOKUPS BEN_PER_IN_LER_STAT WHERE FUSER.USER_ID (+)= PEN.LAST_UPDATED_BY AND PERSON.PERSON_ID (+)= PEN.PERSON_ID AND PGM.PGM_ID (+)= PEN.PGM_ID AND PL.PL_ID (+)= PEN.PL_ID AND PTIP.PTIP_ID (+)= PEN.PTIP_ID AND PL_TYP.PL_TYP_ID (+)= PEN.PL_TYP_ID AND LER.LER_ID (+)= PEN.LER_ID AND ENRT_MTHD.LOOKUP_CODE (+)= PEN.ENRT_MTHD_CD AND ENRT_MTHD.LOOKUP_TYPE (+)= 'BEN_ENRT_RSLT_MTHD' AND ENRT_OVRID_RSN.LOOKUP_CODE (+)= PEN.ENRT_OVRID_RSN_CD AND ENRT_OVRID_RSN.LOOKUP_TYPE (+)= 'BEN_OVERID_RSN' AND BNFT.CURRENCY_CODE (+)= PEN.UOM AND SSPNDD.LOOKUP_CODE (+)= PEN.SSPNDD_FLAG AND SSPNDD.LOOKUP_TYPE (+)='YES_NO' AND PRTT_IS_CVRD.LOOKUP_CODE (+)= PEN.PRTT_IS_CVRD_FLAG AND PRTT_IS_CVRD.LOOKUP_TYPE (+)= 'YES_NO' AND ENRT_OVRIDN.LOOKUP_CODE (+)= PEN.ENRT_OVRIDN_FLAG AND ENRT_OVRIDN.LOOKUP_TYPE (+)= 'YES_NO' AND PER_IN_LER.PER_IN_LER_ID (+)= PEN.PER_IN_LER_ID AND BNFT_TYP.LOOKUP_TYPE (+)= 'BEN_BNFT_TYP' AND BNFT_TYP.LOOKUP_CODE (+)= PEN.BNFT_TYP_CD AND BNFT_NNMNTRY.LOOKUP_CODE (+)= PEN.BNFT_NNMNTRY_UOM AND BNFT_NNMNTRY.LOOKUP_TYPE (+)= 'BEN_BNFT_NNMNTRY_UOM' AND comp_lvl.LOOKUP_CODE (+)= PEN.comp_lvl_cd AND comp_lvl.LOOKUP_TYPE (+)= 'BEN_COMP_LVL' AND NO_LNGR_ELIG.LOOKUP_CODE (+)= PEN.NO_LNGR_ELIG_FLAG AND NO_LNGR_ELIG.LOOKUP_TYPE (+)= 'YES_NO' AND PRTT_ENRT_RSLT_STAT.LOOKUP_TYPE (+)= 'BEN_PRTT_ENRT_RSLT_STAT' AND PRTT_ENRT_RSLT_STAT.LOOKUP_CODE (+)= PEN.PRTT_ENRT_RSLT_STAT_CD AND BEN_PER_IN_LER_STAT.LOOKUP_TYPE (+)= 'BEN_PER_IN_LER_STAT' AND BEN_PER_IN_LER_STAT.LOOKUP_CODE (+)= PER_IN_LER.PER_IN_LER_STAT_CD AND pen.effective_start_date between ler.effective_start_date(+) and ler.effective_end_date(+) AND pen.effective_start_date between pl_typ.effective_start_date(+) and pl_typ.effective_end_date(+) AND pen.effective_start_date between ptip.effective_start_date(+) and ptip.effective_end_date(+) AND pen.effective_start_date between pl.effective_start_date(+) and pl.effective_end_date(+) AND pen.effective_start_date between pgm.effective_start_date(+) and pgm.effective_end_date(+) AND pen.effective_start_date between person.effective_start_date(+) and person.effective_end_date(+)

View Text - HTML Formatted

SELECT DISTINCT PEN.ROWID ROW_ID
, PEN.PRTT_ENRT_RSLT_ID
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE
, PERSON.FULL_NAME
, PGM.NAME
, PL.NAME
, PTIP.PTIP_STAT_CD
, PL_TYP.NAME
, LER.NAME
, SSPNDD.MEANING
, PRTT_IS_CVRD.MEANING
, PEN.BNFT_AMT
, BNFT.NAME
, PEN.ORGNL_ENRT_DT
, ENRT_MTHD.MEANING
, ENRT_OVRIDN.MEANING
, ENRT_OVRID_RSN.MEANING
, PEN.ERLST_DEENRT_DT
, PEN.ENRT_CVG_STRT_DT
, PEN.ENRT_CVG_THRU_DT
, PEN.ENRT_OVRID_THRU_DT
, PEN.LAST_UPDATE_DATE
, FUSER.USER_NAME
, BEN_PER_IN_LER_STAT.MEANING
, BNFT_TYP.MEANING
, BNFT_NNMNTRY.MEANING
, COMP_LVL.MEANING
, NO_LNGR_ELIG.MEANING
, PEN.BNFT_ORDR_NUM
, PRTT_ENRT_RSLT_STAT.MEANING
FROM BEN_PRTT_ENRT_RSLT_F PEN
, FND_USER FUSER
, PER_ALL_PEOPLE_F PERSON
, BEN_PGM_F PGM
, BEN_PL_F PL
, BEN_PTIP_F PTIP
, BEN_PL_TYP_F PL_TYP
, BEN_LER_F LER
, HR_LOOKUPS ENRT_MTHD
, HR_LOOKUPS ENRT_OVRID_RSN
, FND_CURRENCIES_VL BNFT
, HR_LOOKUPS SSPNDD
, HR_LOOKUPS PRTT_IS_CVRD
, HR_LOOKUPS ENRT_OVRIDN
, BEN_PER_IN_LER PER_IN_LER
, HR_LOOKUPS BNFT_TYP
, HR_LOOKUPS BNFT_NNMNTRY
, HR_LOOKUPS COMP_LVL
, HR_LOOKUPS NO_LNGR_ELIG
, HR_LOOKUPS PRTT_ENRT_RSLT_STAT
, HR_LOOKUPS BEN_PER_IN_LER_STAT
WHERE FUSER.USER_ID (+)= PEN.LAST_UPDATED_BY
AND PERSON.PERSON_ID (+)= PEN.PERSON_ID
AND PGM.PGM_ID (+)= PEN.PGM_ID
AND PL.PL_ID (+)= PEN.PL_ID
AND PTIP.PTIP_ID (+)= PEN.PTIP_ID
AND PL_TYP.PL_TYP_ID (+)= PEN.PL_TYP_ID
AND LER.LER_ID (+)= PEN.LER_ID
AND ENRT_MTHD.LOOKUP_CODE (+)= PEN.ENRT_MTHD_CD
AND ENRT_MTHD.LOOKUP_TYPE (+)= 'BEN_ENRT_RSLT_MTHD'
AND ENRT_OVRID_RSN.LOOKUP_CODE (+)= PEN.ENRT_OVRID_RSN_CD
AND ENRT_OVRID_RSN.LOOKUP_TYPE (+)= 'BEN_OVERID_RSN'
AND BNFT.CURRENCY_CODE (+)= PEN.UOM
AND SSPNDD.LOOKUP_CODE (+)= PEN.SSPNDD_FLAG
AND SSPNDD.LOOKUP_TYPE (+)='YES_NO'
AND PRTT_IS_CVRD.LOOKUP_CODE (+)= PEN.PRTT_IS_CVRD_FLAG
AND PRTT_IS_CVRD.LOOKUP_TYPE (+)= 'YES_NO'
AND ENRT_OVRIDN.LOOKUP_CODE (+)= PEN.ENRT_OVRIDN_FLAG
AND ENRT_OVRIDN.LOOKUP_TYPE (+)= 'YES_NO'
AND PER_IN_LER.PER_IN_LER_ID (+)= PEN.PER_IN_LER_ID
AND BNFT_TYP.LOOKUP_TYPE (+)= 'BEN_BNFT_TYP'
AND BNFT_TYP.LOOKUP_CODE (+)= PEN.BNFT_TYP_CD
AND BNFT_NNMNTRY.LOOKUP_CODE (+)= PEN.BNFT_NNMNTRY_UOM
AND BNFT_NNMNTRY.LOOKUP_TYPE (+)= 'BEN_BNFT_NNMNTRY_UOM'
AND COMP_LVL.LOOKUP_CODE (+)= PEN.COMP_LVL_CD
AND COMP_LVL.LOOKUP_TYPE (+)= 'BEN_COMP_LVL'
AND NO_LNGR_ELIG.LOOKUP_CODE (+)= PEN.NO_LNGR_ELIG_FLAG
AND NO_LNGR_ELIG.LOOKUP_TYPE (+)= 'YES_NO'
AND PRTT_ENRT_RSLT_STAT.LOOKUP_TYPE (+)= 'BEN_PRTT_ENRT_RSLT_STAT'
AND PRTT_ENRT_RSLT_STAT.LOOKUP_CODE (+)= PEN.PRTT_ENRT_RSLT_STAT_CD
AND BEN_PER_IN_LER_STAT.LOOKUP_TYPE (+)= 'BEN_PER_IN_LER_STAT'
AND BEN_PER_IN_LER_STAT.LOOKUP_CODE (+)= PER_IN_LER.PER_IN_LER_STAT_CD
AND PEN.EFFECTIVE_START_DATE BETWEEN LER.EFFECTIVE_START_DATE(+)
AND LER.EFFECTIVE_END_DATE(+)
AND PEN.EFFECTIVE_START_DATE BETWEEN PL_TYP.EFFECTIVE_START_DATE(+)
AND PL_TYP.EFFECTIVE_END_DATE(+)
AND PEN.EFFECTIVE_START_DATE BETWEEN PTIP.EFFECTIVE_START_DATE(+)
AND PTIP.EFFECTIVE_END_DATE(+)
AND PEN.EFFECTIVE_START_DATE BETWEEN PL.EFFECTIVE_START_DATE(+)
AND PL.EFFECTIVE_END_DATE(+)
AND PEN.EFFECTIVE_START_DATE BETWEEN PGM.EFFECTIVE_START_DATE(+)
AND PGM.EFFECTIVE_END_DATE(+)
AND PEN.EFFECTIVE_START_DATE BETWEEN PERSON.EFFECTIVE_START_DATE(+)
AND PERSON.EFFECTIVE_END_DATE(+)