Product: | BEN - Advanced Benefits |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.BEN_PL_BNF_CTFN_PRVDD |
SELECT PBC.PL_BNF_CTFN_PRVDD_ID
, PBC.EFFECTIVE_START_DATE
, PBC.EFFECTIVE_END_DATE
, PBC.BNF_CTFN_TYP_CD
, PBC.BNF_CTFN_RECD_DT
, PBC.BNF_CTFN_RQD_FLAG
, PBC.PL_BNF_ID
, PBC.PRTT_ENRT_ACTN_ID
, PBC.BUSINESS_GROUP_ID
, PBC.PBC_ATTRIBUTE_CATEGORY
, PBC.PBC_ATTRIBUTE1
, PBC.PBC_ATTRIBUTE2
, PBC.PBC_ATTRIBUTE3
, PBC.PBC_ATTRIBUTE4
, PBC.PBC_ATTRIBUTE5
, PBC.PBC_ATTRIBUTE6
, PBC.PBC_ATTRIBUTE7
, PBC.PBC_ATTRIBUTE8
, PBC.PBC_ATTRIBUTE9
, PBC.PBC_ATTRIBUTE10
, PBC.PBC_ATTRIBUTE11
, PBC.PBC_ATTRIBUTE12
, PBC.PBC_ATTRIBUTE13
, PBC.PBC_ATTRIBUTE14
, PBC.PBC_ATTRIBUTE15
, PBC.PBC_ATTRIBUTE16
, PBC.PBC_ATTRIBUTE17
, PBC.PBC_ATTRIBUTE18
, PBC.PBC_ATTRIBUTE19
, PBC.PBC_ATTRIBUTE20
, PBC.PBC_ATTRIBUTE21
, PBC.PBC_ATTRIBUTE22
, PBC.PBC_ATTRIBUTE23
, PBC.PBC_ATTRIBUTE24
, PBC.PBC_ATTRIBUTE25
, PBC.PBC_ATTRIBUTE26
, PBC.PBC_ATTRIBUTE27
, PBC.PBC_ATTRIBUTE28
, PBC.PBC_ATTRIBUTE29
, PBC.PBC_ATTRIBUTE30
, PBC.LAST_UPDATE_DATE
, PBC.LAST_UPDATED_BY
, PBC.LAST_UPDATE_LOGIN
, PBC.CREATED_BY
, PBC.CREATION_DATE
, PBC.REQUEST_ID
, PBC.PROGRAM_APPLICATION_ID
, PBC.PROGRAM_ID
, PBC.PROGRAM_UPDATE_DATE
, PBC.OBJECT_VERSION_NUMBER
FROM BEN_PL_BNF_CTFN_PRVDD_F PBC
, BEN_PL_BNF_F PBN
, BEN_PER_IN_LER PIL
WHERE PBC.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PBC.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PBN.PL_BNF_ID=PBC.PL_BNF_ID
AND PBN.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PBN.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PBN.PL_BNF_ID=PBC.PL_BNF_ID
AND PBN.BUSINESS_GROUP_ID+0=PBC.BUSINESS_GROUP_ID
AND PBC.EFFECTIVE_START_DATE BETWEEN PBN.EFFECTIVE_START_DATE
AND PBN.EFFECTIVE_END_DATE
AND PIL.PER_IN_LER_ID(+)=PBN.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PBN.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )