VIEW: APPS.BEN_PER_CM_N_CM_PRVDD_V

Source

View Text - Preformatted

SELECT 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.request_id ,pcd.program_application_id ,pcd.program_id ,pcd.program_update_date ,pcd.OBJECT_VERSION_NUMBER ,pcm.EFFECTIVE_START_DATE pcm_EFFECTIVE_START_DATE ,pcm.EFFECTIVE_END_DATE pcm_EFFECTIVE_END_DATE ,pcm.LF_EVT_OCRD_DT ,pcm.LER_ID ,pcm.PRTT_ENRT_ACTN_ID ,pcm.PERSON_ID ,pcm.BNF_PERSON_ID ,pcm.DPNT_PERSON_ID ,pcm.CM_TYP_ID ,pcm.RQSTBL_UNTL_DT ,pcm.PCM_ATTRIBUTE_CATEGORY ,pcm.PCM_ATTRIBUTE1 ,pcm.PCM_ATTRIBUTE2 ,pcm.PCM_ATTRIBUTE3 ,pcm.PCM_ATTRIBUTE4 ,pcm.PCM_ATTRIBUTE5 ,pcm.PCM_ATTRIBUTE6 ,pcm.PCM_ATTRIBUTE7 ,pcm.PCM_ATTRIBUTE8 ,pcm.PCM_ATTRIBUTE9 ,pcm.PCM_ATTRIBUTE10 ,pcm.PCM_ATTRIBUTE11 ,pcm.PCM_ATTRIBUTE12 ,pcm.PCM_ATTRIBUTE13 ,pcm.PCM_ATTRIBUTE14 ,pcm.PCM_ATTRIBUTE15 ,pcm.PCM_ATTRIBUTE16 ,pcm.PCM_ATTRIBUTE17 ,pcm.PCM_ATTRIBUTE18 ,pcm.PCM_ATTRIBUTE19 ,pcm.PCM_ATTRIBUTE20 ,pcm.PCM_ATTRIBUTE21 ,pcm.PCM_ATTRIBUTE22 ,pcm.PCM_ATTRIBUTE23 ,pcm.PCM_ATTRIBUTE24 ,pcm.PCM_ATTRIBUTE25 ,pcm.PCM_ATTRIBUTE26 ,pcm.PCM_ATTRIBUTE27 ,pcm.PCM_ATTRIBUTE28 ,pcm.PCM_ATTRIBUTE29 ,pcm.PCM_ATTRIBUTE30 , pcm.request_id pcm_request_id , pcm.program_application_id pcm_program_application_id , pcm.program_id pcm_program_id ,pcm.per_in_ler_id , pcm.program_update_date pcm_program_update_date , pcm.object_version_number pcm_object_version_number from BEN_PER_CM_PRVDD_F pcd , ben_per_cm_f pcm , fnd_sessions fnd ,ben_per_in_ler pil WHERE fnd.session_id = userenv('sessionid') and pcd.per_cm_id = pcm.per_cm_id and fnd.effective_date between pcm.effective_start_date and pcm.effective_end_date and fnd.effective_date between pcd.effective_start_date and pcd.effective_end_date and pcm.business_group_id=pcd.business_group_id and pcm.per_in_ler_id = pil.per_in_ler_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) UNION SELECT TO_NUMBER (NULL), pcm.effective_start_date, pcm.effective_end_date, NULL, NULL, NULL, NULL, TO_DATE (NULL), TO_NUMBER (NULL), TO_DATE (NULL), NULL, cct.inspn_rqd_flag, NULL, NULL, PCM.PER_CM_ID, TO_NUMBER (NULL), pcm.business_group_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TO_NUMBER (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_DATE (NULL), TO_NUMBER (NULL), pcm.effective_start_date pcm_effective_start_date, pcm.effective_end_date pcm_effective_end_date, pcm.lf_evt_ocrd_dt, pcm.ler_id, pcm.prtt_enrt_actn_id, pcm.person_id, pcm.bnf_person_id, pcm.dpnt_person_id, pcm.cm_typ_id, pcm.rqstbl_untl_dt, pcm.pcm_attribute_category, pcm.pcm_attribute1, pcm.pcm_attribute2, pcm.pcm_attribute3, pcm.pcm_attribute4, pcm.pcm_attribute5, pcm.pcm_attribute6, pcm.pcm_attribute7, pcm.pcm_attribute8, pcm.pcm_attribute9, pcm.pcm_attribute10, pcm.pcm_attribute11, pcm.pcm_attribute12, pcm.pcm_attribute13, pcm.pcm_attribute14, pcm.pcm_attribute15, pcm.pcm_attribute16, pcm.pcm_attribute17, pcm.pcm_attribute18, pcm.pcm_attribute19, pcm.pcm_attribute20, pcm.pcm_attribute21, pcm.pcm_attribute22, pcm.pcm_attribute23, pcm.pcm_attribute24, pcm.pcm_attribute25, pcm.pcm_attribute26, pcm.pcm_attribute27, pcm.pcm_attribute28, pcm.pcm_attribute29, pcm.pcm_attribute30, pcm.request_id pcm_request_id, pcm.program_application_id pcm_program_application_id, pcm.program_id pcm_program_id, pcm.per_in_ler_id, pcm.program_update_date pcm_program_update_date, pcm.object_version_number pcm_object_version_number FROM ben_per_cm_f pcm , fnd_sessions fnd , ben_per_in_ler pil , ben_cm_typ_f cct WHERE fnd.session_id = userenv('sessionid') and fnd.effective_date between pcm.effective_start_date and pcm.effective_end_date and pcm.per_in_ler_id = pil.per_in_ler_id(+) and (pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') or pil.per_in_ler_stat_cd is null ) and pcm.CM_TYP_ID = cct.CM_TYP_ID and cct.shrt_name like 'LEG%'

View Text - HTML Formatted

SELECT 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.REQUEST_ID
, PCD.PROGRAM_APPLICATION_ID
, PCD.PROGRAM_ID
, PCD.PROGRAM_UPDATE_DATE
, PCD.OBJECT_VERSION_NUMBER
, PCM.EFFECTIVE_START_DATE PCM_EFFECTIVE_START_DATE
, PCM.EFFECTIVE_END_DATE PCM_EFFECTIVE_END_DATE
, PCM.LF_EVT_OCRD_DT
, PCM.LER_ID
, PCM.PRTT_ENRT_ACTN_ID
, PCM.PERSON_ID
, PCM.BNF_PERSON_ID
, PCM.DPNT_PERSON_ID
, PCM.CM_TYP_ID
, PCM.RQSTBL_UNTL_DT
, PCM.PCM_ATTRIBUTE_CATEGORY
, PCM.PCM_ATTRIBUTE1
, PCM.PCM_ATTRIBUTE2
, PCM.PCM_ATTRIBUTE3
, PCM.PCM_ATTRIBUTE4
, PCM.PCM_ATTRIBUTE5
, PCM.PCM_ATTRIBUTE6
, PCM.PCM_ATTRIBUTE7
, PCM.PCM_ATTRIBUTE8
, PCM.PCM_ATTRIBUTE9
, PCM.PCM_ATTRIBUTE10
, PCM.PCM_ATTRIBUTE11
, PCM.PCM_ATTRIBUTE12
, PCM.PCM_ATTRIBUTE13
, PCM.PCM_ATTRIBUTE14
, PCM.PCM_ATTRIBUTE15
, PCM.PCM_ATTRIBUTE16
, PCM.PCM_ATTRIBUTE17
, PCM.PCM_ATTRIBUTE18
, PCM.PCM_ATTRIBUTE19
, PCM.PCM_ATTRIBUTE20
, PCM.PCM_ATTRIBUTE21
, PCM.PCM_ATTRIBUTE22
, PCM.PCM_ATTRIBUTE23
, PCM.PCM_ATTRIBUTE24
, PCM.PCM_ATTRIBUTE25
, PCM.PCM_ATTRIBUTE26
, PCM.PCM_ATTRIBUTE27
, PCM.PCM_ATTRIBUTE28
, PCM.PCM_ATTRIBUTE29
, PCM.PCM_ATTRIBUTE30
, PCM.REQUEST_ID PCM_REQUEST_ID
, PCM.PROGRAM_APPLICATION_ID PCM_PROGRAM_APPLICATION_ID
, PCM.PROGRAM_ID PCM_PROGRAM_ID
, PCM.PER_IN_LER_ID
, PCM.PROGRAM_UPDATE_DATE PCM_PROGRAM_UPDATE_DATE
, PCM.OBJECT_VERSION_NUMBER PCM_OBJECT_VERSION_NUMBER
FROM BEN_PER_CM_PRVDD_F PCD
, BEN_PER_CM_F PCM
, FND_SESSIONS FND
, BEN_PER_IN_LER PIL
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND PCD.PER_CM_ID = PCM.PER_CM_ID
AND FND.EFFECTIVE_DATE BETWEEN PCM.EFFECTIVE_START_DATE
AND PCM.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN PCD.EFFECTIVE_START_DATE
AND PCD.EFFECTIVE_END_DATE
AND PCM.BUSINESS_GROUP_ID=PCD.BUSINESS_GROUP_ID
AND PCM.PER_IN_LER_ID = PIL.PER_IN_LER_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) UNION SELECT TO_NUMBER (NULL)
, PCM.EFFECTIVE_START_DATE
, PCM.EFFECTIVE_END_DATE
, NULL
, NULL
, NULL
, NULL
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_DATE (NULL)
, NULL
, CCT.INSPN_RQD_FLAG
, NULL
, NULL
, PCM.PER_CM_ID
, TO_NUMBER (NULL)
, PCM.BUSINESS_GROUP_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, PCM.EFFECTIVE_START_DATE PCM_EFFECTIVE_START_DATE
, PCM.EFFECTIVE_END_DATE PCM_EFFECTIVE_END_DATE
, PCM.LF_EVT_OCRD_DT
, PCM.LER_ID
, PCM.PRTT_ENRT_ACTN_ID
, PCM.PERSON_ID
, PCM.BNF_PERSON_ID
, PCM.DPNT_PERSON_ID
, PCM.CM_TYP_ID
, PCM.RQSTBL_UNTL_DT
, PCM.PCM_ATTRIBUTE_CATEGORY
, PCM.PCM_ATTRIBUTE1
, PCM.PCM_ATTRIBUTE2
, PCM.PCM_ATTRIBUTE3
, PCM.PCM_ATTRIBUTE4
, PCM.PCM_ATTRIBUTE5
, PCM.PCM_ATTRIBUTE6
, PCM.PCM_ATTRIBUTE7
, PCM.PCM_ATTRIBUTE8
, PCM.PCM_ATTRIBUTE9
, PCM.PCM_ATTRIBUTE10
, PCM.PCM_ATTRIBUTE11
, PCM.PCM_ATTRIBUTE12
, PCM.PCM_ATTRIBUTE13
, PCM.PCM_ATTRIBUTE14
, PCM.PCM_ATTRIBUTE15
, PCM.PCM_ATTRIBUTE16
, PCM.PCM_ATTRIBUTE17
, PCM.PCM_ATTRIBUTE18
, PCM.PCM_ATTRIBUTE19
, PCM.PCM_ATTRIBUTE20
, PCM.PCM_ATTRIBUTE21
, PCM.PCM_ATTRIBUTE22
, PCM.PCM_ATTRIBUTE23
, PCM.PCM_ATTRIBUTE24
, PCM.PCM_ATTRIBUTE25
, PCM.PCM_ATTRIBUTE26
, PCM.PCM_ATTRIBUTE27
, PCM.PCM_ATTRIBUTE28
, PCM.PCM_ATTRIBUTE29
, PCM.PCM_ATTRIBUTE30
, PCM.REQUEST_ID PCM_REQUEST_ID
, PCM.PROGRAM_APPLICATION_ID PCM_PROGRAM_APPLICATION_ID
, PCM.PROGRAM_ID PCM_PROGRAM_ID
, PCM.PER_IN_LER_ID
, PCM.PROGRAM_UPDATE_DATE PCM_PROGRAM_UPDATE_DATE
, PCM.OBJECT_VERSION_NUMBER PCM_OBJECT_VERSION_NUMBER
FROM BEN_PER_CM_F PCM
, FND_SESSIONS FND
, BEN_PER_IN_LER PIL
, BEN_CM_TYP_F CCT
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PCM.EFFECTIVE_START_DATE
AND PCM.EFFECTIVE_END_DATE
AND PCM.PER_IN_LER_ID = PIL.PER_IN_LER_ID(+)
AND (PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND PCM.CM_TYP_ID = CCT.CM_TYP_ID
AND CCT.SHRT_NAME LIKE 'LEG%'