Product: | BEN - Advanced Benefits |
---|---|
Description: | |
Implementation/DBA Data: | APPS.BEN_PER_CM_PRVDD_V |
SELECT PCD.ROWID ROW_ID
, PCD.PER_CM_PRVDD_ID
, PCD.EFFECTIVE_START_DATE
, PCD.EFFECTIVE_END_DATE
, PCD.RQSTD_FLAG
, PCD.PER_CM_PRVDD_STAT_CD
, PCD.CM_DLVRY_MED_CD
, PCD.CM_DLVRY_MTHD_CD
, PCD.SENT_DT
, PCD.INSTNC_NUM
, PCD.TO_BE_SENT_DT
, PCD.DLVRY_INSTN_TXT
, PCD.INSPN_RQD_FLAG
, PCD.RESND_RSN_CD
, PCD.RESND_CMNT_TXT
, PCD.PER_CM_ID
, PCD.ADDRESS_ID
, PCD.BUSINESS_GROUP_ID
, PCD.PCD_ATTRIBUTE_CATEGORY
, PCD.PCD_ATTRIBUTE1
, PCD.PCD_ATTRIBUTE2
, PCD.PCD_ATTRIBUTE3
, PCD.PCD_ATTRIBUTE4
, PCD.PCD_ATTRIBUTE5
, PCD.PCD_ATTRIBUTE6
, PCD.PCD_ATTRIBUTE7
, PCD.PCD_ATTRIBUTE8
, PCD.PCD_ATTRIBUTE9
, PCD.PCD_ATTRIBUTE10
, PCD.PCD_ATTRIBUTE11
, PCD.PCD_ATTRIBUTE12
, PCD.PCD_ATTRIBUTE13
, PCD.PCD_ATTRIBUTE14
, PCD.PCD_ATTRIBUTE15
, PCD.PCD_ATTRIBUTE16
, PCD.PCD_ATTRIBUTE17
, PCD.PCD_ATTRIBUTE18
, PCD.PCD_ATTRIBUTE19
, PCD.PCD_ATTRIBUTE20
, PCD.PCD_ATTRIBUTE21
, PCD.PCD_ATTRIBUTE22
, PCD.PCD_ATTRIBUTE23
, PCD.PCD_ATTRIBUTE24
, PCD.PCD_ATTRIBUTE25
, PCD.PCD_ATTRIBUTE26
, PCD.PCD_ATTRIBUTE27
, PCD.PCD_ATTRIBUTE28
, PCD.PCD_ATTRIBUTE29
, PCD.PCD_ATTRIBUTE30
, PCD.LAST_UPDATE_DATE
, PCD.LAST_UPDATED_BY
, PCD.LAST_UPDATE_LOGIN
, PCD.CREATED_BY
, PCD.CREATION_DATE
, PCD.REQUEST_ID
, PCD.PROGRAM_APPLICATION_ID
, PCD.PROGRAM_ID
, PCD.PROGRAM_UPDATE_DATE
, PCD.OBJECT_VERSION_NUMBER
FROM BEN_PER_CM_PRVDD_F PCD
, BEN_PER_CM_F PCM
, BEN_PER_IN_LER PIL
, FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID')
AND SE.EFFECTIVE_DATE BETWEEN PCD.EFFECTIVE_START_DATE
AND PCD.EFFECTIVE_END_DATE
AND PCM.PER_CM_ID=PCD.PER_CM_ID
AND PCM.BUSINESS_GROUP_ID=PCD.BUSINESS_GROUP_ID
AND PCD.EFFECTIVE_START_DATE BETWEEN PCM.EFFECTIVE_START_DATE
AND PCM.EFFECTIVE_END_DATE
AND PIL.PER_IN_LER_ID(+)=PCM.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PCM.BUSINESS_GROUP_ID
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL OR PCD.SENT_DT IS NOT NULL )