Product: | BEN - Advanced Benefits |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.BEN_ENRT_ACTN_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
, PIL.LER_ID
, PEA.RQD_FLAG
, PEN.OBJECT_VERSION_NUMBER RSLT_OBJECT_VERSION_NUMBER
, PEN.PER_IN_LER_ID
, PEA.PRTT_ENRT_ACTN_ID
, PEA.EFFECTIVE_START_DATE
, PEA.EFFECTIVE_END_DATE
, PEA.CMPLTD_DT
, PEA.DUE_DT
, PEA.ACTN_TYP_ID
, PEA.BUSINESS_GROUP_ID
, PEA.PEA_ATTRIBUTE_CATEGORY
, PEA.PEA_ATTRIBUTE1
, PEA.PEA_ATTRIBUTE2
, PEA.PEA_ATTRIBUTE3
, PEA.PEA_ATTRIBUTE4
, PEA.PEA_ATTRIBUTE5
, PEA.PEA_ATTRIBUTE6
, PEA.PEA_ATTRIBUTE7
, PEA.PEA_ATTRIBUTE8
, PEA.PEA_ATTRIBUTE9
, PEA.PEA_ATTRIBUTE10
, PEA.PEA_ATTRIBUTE11
, PEA.PEA_ATTRIBUTE12
, PEA.PEA_ATTRIBUTE13
, PEA.PEA_ATTRIBUTE14
, PEA.PEA_ATTRIBUTE15
, PEA.PEA_ATTRIBUTE16
, PEA.PEA_ATTRIBUTE17
, PEA.PEA_ATTRIBUTE18
, PEA.PEA_ATTRIBUTE19
, PEA.PEA_ATTRIBUTE20
, PEA.PEA_ATTRIBUTE21
, PEA.PEA_ATTRIBUTE22
, PEA.PEA_ATTRIBUTE23
, PEA.PEA_ATTRIBUTE24
, PEA.PEA_ATTRIBUTE25
, PEA.PEA_ATTRIBUTE26
, PEA.PEA_ATTRIBUTE27
, PEA.PEA_ATTRIBUTE28
, PEA.PEA_ATTRIBUTE29
, PEA.PEA_ATTRIBUTE30
, PEA.OBJECT_VERSION_NUMBER
, PEA.PL_BNF_ID
, PEA.ELIG_CVRD_DPNT_ID
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PRTT_ENRT_ACTN_F PEA
, BEN_PER_IN_LER PIL
, FND_SESSIONS SE
WHERE PEN.PRTT_ENRT_RSLT_ID = PEA.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 PEA.EFFECTIVE_START_DATE
AND PEA.EFFECTIVE_END_DATE
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 )