View: BEN_PLIP_D

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

View Text

SELECT DISTINCT CPP.PLIP_ID
, CPP.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_END_DATE
, PGM.NAME
, PL.NAME
, DFLT.MEANING
, PLIP_STAT.MEANING
, DFLT_ENRT.MEANING
, DFLT_ENRT_DET.FORMULA_NAME
, ALWS_UNRSTRCTD_ENRT.MEANING
, ENRT_CVG_STRT_DT.MEANING
, ENRT_CVG_STRT_DT2.FORMULA_NAME
, ENRT_CVG_END_DT.MEANING
, ENRT_CVG_END_DT2.FORMULA_NAME
, RT_STRT_DT.MEANING
, RT_STRT_DT2.FORMULA_NAME
, RT_END_DT.MEANING
, RT_END_DT2.FORMULA_NAME
, AUTO_ENRT_MTHD.FORMULA_NAME
, ENRT.MEANING
, ENRT_MTHD.MEANING
, ENRT2.FORMULA_NAME
, DRVBL_FCTR_APLS_RTS.MEANING
, DRVBL_FCTR_PRTN_ELIG.MEANING
, ELIG_APLS.MEANING
, PRTN_ELIG_OVRID_ALWD.MEANING
, TRK_INELIG_PER.MEANING
, POSTELCN_EDIT.FORMULA_NAME
, CPP.LAST_UPDATE_DATE
, FUSER.USER_NAME
, VRFY_FMLY_MMBR.MEANING
, VRFY_FMLY_MMBR2.FORMULA_NAME
, PER_CVRD.MEANING
, CPP.SHORT_NAME
, CPP.SHORT_CODE
FROM BEN_PLIP_F CPP
, FND_USER FUSER
, BEN_PGM_F PGM
, BEN_PL_F PL
, HR_LOOKUPS PLIP_STAT
, HR_LOOKUPS DFLT_ENRT
, HR_LOOKUPS DFLT
, HR_LOOKUPS ALWS_UNRSTRCTD_ENRT
, HR_LOOKUPS ENRT
, HR_LOOKUPS ENRT_MTHD
, HR_LOOKUPS ENRT_CVG_STRT_DT
, HR_LOOKUPS ENRT_CVG_END_DT
, HR_LOOKUPS RT_STRT_DT
, HR_LOOKUPS RT_END_DT
, HR_LOOKUPS DRVBL_FCTR_APLS_RTS
, HR_LOOKUPS DRVBL_FCTR_PRTN_ELIG
, HR_LOOKUPS ELIG_APLS
, HR_LOOKUPS PRTN_ELIG_OVRID_ALWD
, HR_LOOKUPS TRK_INELIG_PER
, FF_FORMULAS_F DFLT_ENRT_DET
, FF_FORMULAS_F AUTO_ENRT_MTHD
, FF_FORMULAS_F ENRT2
, FF_FORMULAS_F ENRT_CVG_STRT_DT2
, FF_FORMULAS_F ENRT_CVG_END_DT2
, FF_FORMULAS_F RT_STRT_DT2
, FF_FORMULAS_F RT_END_DT2
, FF_FORMULAS_F POSTELCN_EDIT
, HR_LOOKUPS PER_CVRD
, HR_LOOKUPS VRFY_FMLY_MMBR
, FF_FORMULAS_F VRFY_FMLY_MMBR2
WHERE FUSER.USER_ID (+)= CPP.LAST_UPDATED_BY
AND PGM.PGM_ID (+)= CPP.PGM_ID
AND PL.PL_ID (+)= CPP.PL_ID
AND PLIP_STAT.LOOKUP_CODE (+)= CPP.PLIP_STAT_CD
AND PLIP_STAT.LOOKUP_TYPE (+)= 'BEN_STAT'
AND DFLT_ENRT.LOOKUP_CODE (+)= CPP.DFLT_ENRT_CD
AND DFLT_ENRT.LOOKUP_TYPE (+)= 'BEN_DFLT_ENRT'
AND DFLT.LOOKUP_CODE (+)= CPP.DFLT_FLAG
AND DFLT.LOOKUP_TYPE (+)= 'YES_NO'
AND ALWS_UNRSTRCTD_ENRT.LOOKUP_CODE(+)= CPP.ALWS_UNRSTRCTD_ENRT_FLAG
AND ALWS_UNRSTRCTD_ENRT.LOOKUP_TYPE(+)='YES_NO'
AND DRVBL_FCTR_APLS_RTS.LOOKUP_CODE (+)= CPP.DRVBL_FCTR_APLS_RTS_FLAG
AND DRVBL_FCTR_APLS_RTS.LOOKUP_TYPE (+)= 'YES_NO'
AND DRVBL_FCTR_PRTN_ELIG.LOOKUP_CODE (+)= CPP.DRVBL_FCTR_PRTN_ELIG_FLAG
AND DRVBL_FCTR_PRTN_ELIG.LOOKUP_TYPE (+)= 'YES_NO'
AND ELIG_APLS.LOOKUP_CODE (+)= CPP.ELIG_APLS_FLAG
AND ELIG_APLS.LOOKUP_TYPE (+)= 'YES_NO'
AND PRTN_ELIG_OVRID_ALWD.LOOKUP_CODE (+)= CPP.PRTN_ELIG_OVRID_ALWD_FLAG
AND PRTN_ELIG_OVRID_ALWD.LOOKUP_TYPE (+)= 'YES_NO'
AND TRK_INELIG_PER.LOOKUP_CODE (+)= CPP.TRK_INELIG_PER_FLAG
AND TRK_INELIG_PER.LOOKUP_TYPE (+)= 'YES_NO'
AND ENRT.LOOKUP_CODE (+)= CPP.ENRT_CD
AND ENRT.LOOKUP_TYPE (+)= 'BEN_ENRT'
AND ENRT_MTHD.LOOKUP_CODE (+)= CPP.ENRT_MTHD_CD
AND ENRT_MTHD.LOOKUP_TYPE (+)= 'BEN_ENRT_MTHD'
AND ENRT_CVG_STRT_DT.LOOKUP_CODE (+)= CPP.ENRT_CVG_STRT_DT_CD
AND ENRT_CVG_STRT_DT.LOOKUP_TYPE (+)= 'BEN_ENRT_CVG_STRT'
AND ENRT_CVG_END_DT.LOOKUP_CODE (+)= CPP.ENRT_CVG_END_DT_CD
AND ENRT_CVG_END_DT.LOOKUP_TYPE (+)= 'BEN_ENRT_CVG_END'
AND RT_STRT_DT.LOOKUP_CODE (+)= CPP.RT_STRT_DT_CD
AND RT_STRT_DT.LOOKUP_TYPE (+)= 'BEN_RT_STRT'
AND RT_END_DT.LOOKUP_CODE (+)= CPP.RT_END_DT_CD
AND RT_END_DT.LOOKUP_TYPE (+)= 'BEN_RT_END'
AND DFLT_ENRT_DET.FORMULA_ID (+)= CPP.DFLT_ENRT_DET_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(DFLT_ENRT_DET.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(DFLT_ENRT_DET.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND AUTO_ENRT_MTHD.FORMULA_ID (+)= CPP.AUTO_ENRT_MTHD_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(AUTO_ENRT_MTHD.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(AUTO_ENRT_MTHD.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND ENRT2.FORMULA_ID (+)= CPP.ENRT_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(ENRT2.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(ENRT2.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND ENRT_CVG_STRT_DT2.FORMULA_ID (+)= CPP.ENRT_CVG_STRT_DT_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(ENRT_CVG_STRT_DT2.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(ENRT_CVG_STRT_DT2.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND ENRT_CVG_END_DT2.FORMULA_ID (+)= CPP.ENRT_CVG_END_DT_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(ENRT_CVG_END_DT2.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(ENRT_CVG_END_DT2.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND RT_STRT_DT2.FORMULA_ID (+)= CPP.RT_STRT_DT_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(RT_STRT_DT2.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(RT_STRT_DT2.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND RT_END_DT2.FORMULA_ID (+)= CPP.RT_END_DT_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(RT_STRT_DT2.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(RT_END_DT2.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND POSTELCN_EDIT.FORMULA_ID (+)= CPP.POSTELCN_EDIT_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(POSTELCN_EDIT.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(POSTELCN_EDIT.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)
AND VRFY_FMLY_MMBR.LOOKUP_CODE (+)= CPP.VRFY_FMLY_MMBR_CD
AND VRFY_FMLY_MMBR.LOOKUP_TYPE (+)= 'BEN_FMLY_MMBR'
AND PER_CVRD.LOOKUP_CODE (+)= CPP.PER_CVRD_CD
AND PER_CVRD.LOOKUP_TYPE (+)= 'BEN_PER_CVRD'
AND VRFY_FMLY_MMBR2.FORMULA_ID (+)= CPP.VRFY_FMLY_MMBR_RL
AND CPP.EFFECTIVE_START_DATE BETWEEN NVL(VRFY_FMLY_MMBR2.EFFECTIVE_START_DATE
, CPP.EFFECTIVE_START_DATE)
AND NVL(VRFY_FMLY_MMBR2.EFFECTIVE_END_DATE
, CPP.EFFECTIVE_START_DATE)

Columns