Product: | BEN - Advanced Benefits |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.BEN_PL_BNF |
SELECT PBN.PL_BNF_ID
, PBN.EFFECTIVE_START_DATE
, PBN.EFFECTIVE_END_DATE
, PBN.BUSINESS_GROUP_ID
, PBN.PRTT_ENRT_RSLT_ID
, PBN.BNF_PERSON_ID
, PBN.ORGANIZATION_ID
, PBN.TTEE_PERSON_ID
, PBN.PRMRY_CNTNGNT_CD
, PBN.PCT_DSGD_NUM
, PBN.AMT_DSGD_VAL
, PBN.AMT_DSGD_UOM
, PBN.ADDL_INSTRN_TXT
, PBN.DSGN_STRT_DT
, PBN.DSGN_THRU_DT
, PBN.PBN_ATTRIBUTE_CATEGORY
, PBN.PBN_ATTRIBUTE1
, PBN.PBN_ATTRIBUTE2
, PBN.PBN_ATTRIBUTE3
, PBN.PBN_ATTRIBUTE4
, PBN.PBN_ATTRIBUTE5
, PBN.PBN_ATTRIBUTE6
, PBN.PBN_ATTRIBUTE7
, PBN.PBN_ATTRIBUTE8
, PBN.PBN_ATTRIBUTE9
, PBN.PBN_ATTRIBUTE10
, PBN.PBN_ATTRIBUTE11
, PBN.PBN_ATTRIBUTE12
, PBN.PBN_ATTRIBUTE13
, PBN.PBN_ATTRIBUTE14
, PBN.PBN_ATTRIBUTE15
, PBN.PBN_ATTRIBUTE16
, PBN.PBN_ATTRIBUTE17
, PBN.PBN_ATTRIBUTE18
, PBN.PBN_ATTRIBUTE19
, PBN.PBN_ATTRIBUTE20
, PBN.PBN_ATTRIBUTE21
, PBN.PBN_ATTRIBUTE22
, PBN.PBN_ATTRIBUTE23
, PBN.PBN_ATTRIBUTE24
, PBN.PBN_ATTRIBUTE25
, PBN.PBN_ATTRIBUTE26
, PBN.PBN_ATTRIBUTE27
, PBN.PBN_ATTRIBUTE28
, PBN.PBN_ATTRIBUTE29
, PBN.PBN_ATTRIBUTE30
, PBN.LAST_UPDATE_DATE
, PBN.LAST_UPDATED_BY
, PBN.LAST_UPDATE_LOGIN
, PBN.CREATED_BY
, PBN.CREATION_DATE
, PBN.REQUEST_ID
, PBN.PROGRAM_APPLICATION_ID
, PBN.PROGRAM_ID
, PBN.PROGRAM_UPDATE_DATE
, PBN.OBJECT_VERSION_NUMBER
, PBN.PER_IN_LER_ID
FROM BEN_PL_BNF_F PBN
, BEN_PER_IN_LER PIL
WHERE 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 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 )