Product: | BEN - Advanced Benefits |
---|---|
Description: | |
Implementation/DBA Data: | APPS.BEN_PRTT_ENRT_CTFN_INFO_V |
SELECT PEN.PRTT_ENRT_RSLT_ID
, PEN.OIPL_ID
, PEN.PERSON_ID
, PEN.PGM_ID
, PEN.PL_ID
, PEN.PTIP_ID
, PEN.PL_TYP_ID
, PEN.LER_ID
, PEN.PER_IN_LER_ID
, PEN.SSPNDD_FLAG
, PCS.PRTT_ENRT_CTFN_PRVDD_ID
, PCS.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_END_DATE
, PCS.ENRT_CTFN_RQD_FLAG
, PCS.ENRT_CTFN_TYP_CD
, PCS.ENRT_CTFN_RECD_DT
, PCS.ENRT_CTFN_DND_DT
, PCS.ENRT_R_BNFT_CTFN_CD
, PCS.PRTT_ENRT_ACTN_ID
, PCS.BUSINESS_GROUP_ID
, PCS.PCS_ATTRIBUTE_CATEGORY
, PCS.PCS_ATTRIBUTE1
, PCS.PCS_ATTRIBUTE2
, PCS.PCS_ATTRIBUTE3
, PCS.PCS_ATTRIBUTE4
, PCS.PCS_ATTRIBUTE5
, PCS.PCS_ATTRIBUTE6
, PCS.PCS_ATTRIBUTE7
, PCS.PCS_ATTRIBUTE8
, PCS.PCS_ATTRIBUTE9
, PCS.PCS_ATTRIBUTE10
, PCS.PCS_ATTRIBUTE11
, PCS.PCS_ATTRIBUTE12
, PCS.PCS_ATTRIBUTE13
, PCS.PCS_ATTRIBUTE14
, PCS.PCS_ATTRIBUTE15
, PCS.PCS_ATTRIBUTE16
, PCS.PCS_ATTRIBUTE17
, PCS.PCS_ATTRIBUTE18
, PCS.PCS_ATTRIBUTE19
, PCS.PCS_ATTRIBUTE20
, PCS.PCS_ATTRIBUTE21
, PCS.PCS_ATTRIBUTE22
, PCS.PCS_ATTRIBUTE23
, PCS.PCS_ATTRIBUTE24
, PCS.PCS_ATTRIBUTE25
, PCS.PCS_ATTRIBUTE26
, PCS.PCS_ATTRIBUTE27
, PCS.PCS_ATTRIBUTE28
, PCS.PCS_ATTRIBUTE29
, PCS.PCS_ATTRIBUTE30
, PCS.LAST_UPDATE_DATE
, PCS.LAST_UPDATED_BY
, PCS.LAST_UPDATE_LOGIN
, PCS.CREATED_BY
, PCS.CREATION_DATE
, PCS.REQUEST_ID
, PCS.PROGRAM_APPLICATION_ID
, PCS.PROGRAM_ID
, PCS.PROGRAM_UPDATE_DATE
, PCS.OBJECT_VERSION_NUMBER
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PRTT_ENRT_CTFN_PRVDD_F PCS
, BEN_PER_IN_LER PIL
, FND_SESSIONS SE
WHERE PEN.PRTT_ENRT_RSLT_ID = PCS.PRTT_ENRT_RSLT_ID
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND SE.SESSION_ID = USERENV('SESSIONID')
AND SE.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN PCS.EFFECTIVE_START_DATE
AND PCS.EFFECTIVE_END_DATE
AND PIL.PER_IN_LER_ID=PEN.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=PEN.BUSINESS_GROUP_ID
AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')