VIEW: APPS.BEN_PER_CM_PRVDD_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )