View: BEN_PER_CM_N_CM_PRVDD_V

Product:	BEN - Advanced Benefits
Description:
Implementation/DBA Data:	APPS.BEN_PER_CM_N_CM_PRVDD_V

View Text

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%'

Columns