View: BEN_PRTT_ENRT_CTFN_PRVDD_D

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

View Text

SELECT PCS.ROWID ROW_ID
, PCS.PRTT_ENRT_CTFN_PRVDD_ID
, PCS.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_END_DATE
, ENRT_CTFN_TYP.MEANING ENRT_CTFN_TYP_MEANING
, DECODE(PEN.SSPNDD_FLAG
, 'Y'
, 'YES'
, 'NO') SSPNDD_FLAG
, PEA.DUE_DT
, ENRT_CTFN_RQD.MEANING ENRT_CTFN_RQD_MEANING
, ENRT_R_BNFT_CTFN.MEANING ENRT_R_BNFT_CTFN_MEANING
, DECODE(PCS.ENRT_CTFN_RECD_DT
, TO_DATE(NULL)
, 'NO'
, 'YES') RECVD_FLAG
, PCS.ENRT_CTFN_RECD_DT
, DECODE(PCS.ENRT_CTFN_DND_DT
, TO_DATE(NULL)
, 'NO'
, 'YES') DND_FLAG
, PCS.ENRT_CTFN_DND_DT
, PLAN.NAME PLN_NAME
, PLTYP.NAME PL_TYP_NAME
, PROGRAM.NAME PGM_NAME
, OPT.NAME OPT_NAME
, LER.NAME LER_NAME
, PCS.LAST_UPDATE_DATE
, FUSER.USER_NAME LAST_UPDATED_BY
FROM BEN_PRTT_ENRT_CTFN_PRVDD_F PCS
, BEN_PRTT_ENRT_ACTN_F PEA
, BEN_PER_IN_LER PIL
, FND_USER FUSER
, HR_LOOKUPS ENRT_CTFN_TYP
, HR_LOOKUPS ENRT_R_BNFT_CTFN
, HR_LOOKUPS ENRT_CTFN_RQD
, BEN_PRTT_ENRT_RSLT_F PEN
, BEN_OPT_F OPT
, BEN_OIPL_F OPT2
, BEN_PGM_F PROGRAM
, BEN_PL_F PLAN
, BEN_LER_F LER
, BEN_PL_TYP_F PLTYP
WHERE FUSER.USER_ID (+)= PCS.LAST_UPDATED_BY
AND ENRT_CTFN_TYP.LOOKUP_CODE (+)= PCS.ENRT_CTFN_TYP_CD
AND ENRT_CTFN_TYP.LOOKUP_TYPE (+)= 'ENRT_CTFN_TYP'
AND ENRT_R_BNFT_CTFN.LOOKUP_CODE (+)= PCS.ENRT_R_BNFT_CTFN_CD
AND ENRT_R_BNFT_CTFN.LOOKUP_TYPE (+)= 'ENRT_R_BNFT_CTFN'
AND ENRT_CTFN_RQD.LOOKUP_CODE (+)= PCS.ENRT_CTFN_RQD_FLAG
AND ENRT_CTFN_RQD.LOOKUP_TYPE (+)= 'YES_NO'
AND PEA.PRTT_ENRT_ACTN_ID=PCS.PRTT_ENRT_ACTN_ID
AND PEA.BUSINESS_GROUP_ID=PCS.BUSINESS_GROUP_ID
AND PIL.PER_IN_LER_ID(+)=PEA.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PEA.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND PEN.PRTT_ENRT_RSLT_ID = PCS.PRTT_ENRT_RSLT_ID
AND OPT2.OIPL_ID (+) = PEN.OIPL_ID
AND OPT.OPT_ID (+) = OPT2.OPT_ID
AND PROGRAM.PGM_ID (+) = PEN.PGM_ID
AND PLAN.PL_ID (+) = PEN.PL_ID
AND PLTYP.PL_TYP_ID (+) = PEN.PL_TYP_ID
AND LER.LER_ID (+) = PEN.LER_ID
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PIL.PER_IN_LER_ID=PEN.PER_IN_LER_ID
AND PCS.EFFECTIVE_START_DATE BETWEEN PEA.EFFECTIVE_START_DATE
AND PEA.EFFECTIVE_END_DATE
AND PCS.EFFECTIVE_START_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( OPT.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( OPT2.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(OPT2.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL(PROGRAM.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(PROGRAM.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( PLAN.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(PLAN.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL(PLTYP.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(PLTYP.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( LER.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(LER.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)

Columns