VIEW: APPS.BEN_AUTO_ENRT_TERM_V

Source

View Text - Preformatted

SELECT pen.ASSIGNMENT_ID, pen.BNFT_AMT, pen.BNFT_NNMNTRY_UOM, pen.BNFT_ORDR_NUM, pen.BNFT_TYP_CD, pen.BUSINESS_GROUP_ID, pen.COMP_LVL_CD, pen.CREATED_BY, pen.CREATION_DATE, pen.EFFECTIVE_END_DATE, pen.EFFECTIVE_START_DATE, pen.ENRT_CVG_STRT_DT, pen.ENRT_CVG_THRU_DT, pen.ENRT_MTHD_CD, pen.ENRT_OVRIDN_FLAG, pen.ENRT_OVRID_RSN_CD, pen.ENRT_OVRID_THRU_DT, pen.ERLST_DEENRT_DT, pen.LAST_UPDATED_BY, pen.LAST_UPDATE_DATE, pen.LAST_UPDATE_LOGIN, pen.LER_ID, pen.NO_LNGR_ELIG_FLAG, pen.OBJECT_VERSION_NUMBER, pen.OIPL_ID, pen.OIPL_ORDR_NUM, pen.ORGNL_ENRT_DT, pen.PEN_ATTRIBUTE1, pen.PEN_ATTRIBUTE10, pen.PEN_ATTRIBUTE11, pen.PEN_ATTRIBUTE12, pen.PEN_ATTRIBUTE13, pen.PEN_ATTRIBUTE14, pen.PEN_ATTRIBUTE15, pen.PEN_ATTRIBUTE16, pen.PEN_ATTRIBUTE17, pen.PEN_ATTRIBUTE18, pen.PEN_ATTRIBUTE19, pen.PEN_ATTRIBUTE2, pen.PEN_ATTRIBUTE20, pen.PEN_ATTRIBUTE21, pen.PEN_ATTRIBUTE22, pen.PEN_ATTRIBUTE23, pen.PEN_ATTRIBUTE24, pen.PEN_ATTRIBUTE25, pen.PEN_ATTRIBUTE26, pen.PEN_ATTRIBUTE27, pen.PEN_ATTRIBUTE28, pen.PEN_ATTRIBUTE29, pen.PEN_ATTRIBUTE3, pen.PEN_ATTRIBUTE30, pen.PEN_ATTRIBUTE4, pen.PEN_ATTRIBUTE5, pen.PEN_ATTRIBUTE6, pen.PEN_ATTRIBUTE7, pen.PEN_ATTRIBUTE8, pen.PEN_ATTRIBUTE9, pen.PEN_ATTRIBUTE_CATEGORY, pen.PERSON_ID, pen.PER_IN_LER_ID, pen.PGM_ID, pen.PLIP_ORDR_NUM, pen.PL_ID, pen.PL_ORDR_NUM, pen.PL_TYP_ID, pen.PROGRAM_APPLICATION_ID, pen.PROGRAM_ID, pen.PROGRAM_UPDATE_DATE, pen.PRTT_ENRT_RSLT_ID, pen.PRTT_ENRT_RSLT_STAT_CD, pen.PRTT_IS_CVRD_FLAG, pen.PTIP_ID, pen.PTIP_ORDR_NUM, pen.REQUEST_ID, pen.RPLCS_SSPNDD_RSLT_ID, pen.SSPNDD_FLAG, pen.UOM FROM ben_prtt_enrt_rslt_f pen WHERE (pen.oipl_id IS NULL AND EXISTS ( SELECT NULL FROM ben_elig_per_f pep WHERE pep.pl_id = pen.pl_id AND NVL(pep.pgm_id,-1) = NVL(pen.pgm_id,-1) AND pep.business_group_id = pen.business_group_id AND pep.person_id = pen.person_id AND pen.effective_start_date BETWEEN pep.effective_start_date AND pep.effective_end_date AND pep.elig_flag = 'N') ) OR (pen.oipl_id IS NOT NULL AND EXISTS ( SELECT NULL FROM ben_elig_per_f pep , ben_elig_per_opt_f epo , ben_oipl_f oipl WHERE pep.pl_id = pen.pl_id AND epo.elig_per_id = pep.elig_per_id AND pep.person_id = pen.person_id AND epo.opt_id = oipl.opt_id AND oipl.oipl_id = pen.oipl_id AND NVL(pep.pgm_id,-1) = NVL(pen.pgm_id,-1) AND pep.business_group_id = pen.business_group_id AND pep.business_group_id = epo.business_group_id AND oipl.business_group_id = epo.business_group_id AND pen.effective_start_date BETWEEN pen.effective_start_date AND pen.effective_end_date AND epo.effective_start_date BETWEEN pen.effective_start_date AND pen.effective_end_date AND pen.effective_start_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date AND epo.elig_flag = 'N') )

View Text - HTML Formatted

SELECT PEN.ASSIGNMENT_ID
, PEN.BNFT_AMT
, PEN.BNFT_NNMNTRY_UOM
, PEN.BNFT_ORDR_NUM
, PEN.BNFT_TYP_CD
, PEN.BUSINESS_GROUP_ID
, PEN.COMP_LVL_CD
, PEN.CREATED_BY
, PEN.CREATION_DATE
, PEN.EFFECTIVE_END_DATE
, PEN.EFFECTIVE_START_DATE
, PEN.ENRT_CVG_STRT_DT
, PEN.ENRT_CVG_THRU_DT
, PEN.ENRT_MTHD_CD
, PEN.ENRT_OVRIDN_FLAG
, PEN.ENRT_OVRID_RSN_CD
, PEN.ENRT_OVRID_THRU_DT
, PEN.ERLST_DEENRT_DT
, PEN.LAST_UPDATED_BY
, PEN.LAST_UPDATE_DATE
, PEN.LAST_UPDATE_LOGIN
, PEN.LER_ID
, PEN.NO_LNGR_ELIG_FLAG
, PEN.OBJECT_VERSION_NUMBER
, PEN.OIPL_ID
, PEN.OIPL_ORDR_NUM
, PEN.ORGNL_ENRT_DT
, PEN.PEN_ATTRIBUTE1
, PEN.PEN_ATTRIBUTE10
, PEN.PEN_ATTRIBUTE11
, PEN.PEN_ATTRIBUTE12
, PEN.PEN_ATTRIBUTE13
, PEN.PEN_ATTRIBUTE14
, PEN.PEN_ATTRIBUTE15
, PEN.PEN_ATTRIBUTE16
, PEN.PEN_ATTRIBUTE17
, PEN.PEN_ATTRIBUTE18
, PEN.PEN_ATTRIBUTE19
, PEN.PEN_ATTRIBUTE2
, PEN.PEN_ATTRIBUTE20
, PEN.PEN_ATTRIBUTE21
, PEN.PEN_ATTRIBUTE22
, PEN.PEN_ATTRIBUTE23
, PEN.PEN_ATTRIBUTE24
, PEN.PEN_ATTRIBUTE25
, PEN.PEN_ATTRIBUTE26
, PEN.PEN_ATTRIBUTE27
, PEN.PEN_ATTRIBUTE28
, PEN.PEN_ATTRIBUTE29
, PEN.PEN_ATTRIBUTE3
, PEN.PEN_ATTRIBUTE30
, PEN.PEN_ATTRIBUTE4
, PEN.PEN_ATTRIBUTE5
, PEN.PEN_ATTRIBUTE6
, PEN.PEN_ATTRIBUTE7
, PEN.PEN_ATTRIBUTE8
, PEN.PEN_ATTRIBUTE9
, PEN.PEN_ATTRIBUTE_CATEGORY
, PEN.PERSON_ID
, PEN.PER_IN_LER_ID
, PEN.PGM_ID
, PEN.PLIP_ORDR_NUM
, PEN.PL_ID
, PEN.PL_ORDR_NUM
, PEN.PL_TYP_ID
, PEN.PROGRAM_APPLICATION_ID
, PEN.PROGRAM_ID
, PEN.PROGRAM_UPDATE_DATE
, PEN.PRTT_ENRT_RSLT_ID
, PEN.PRTT_ENRT_RSLT_STAT_CD
, PEN.PRTT_IS_CVRD_FLAG
, PEN.PTIP_ID
, PEN.PTIP_ORDR_NUM
, PEN.REQUEST_ID
, PEN.RPLCS_SSPNDD_RSLT_ID
, PEN.SSPNDD_FLAG
, PEN.UOM
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE (PEN.OIPL_ID IS NULL
AND EXISTS ( SELECT NULL
FROM BEN_ELIG_PER_F PEP
WHERE PEP.PL_ID = PEN.PL_ID
AND NVL(PEP.PGM_ID
, -1) = NVL(PEN.PGM_ID
, -1)
AND PEP.BUSINESS_GROUP_ID = PEN.BUSINESS_GROUP_ID
AND PEP.PERSON_ID = PEN.PERSON_ID
AND PEN.EFFECTIVE_START_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PEP.ELIG_FLAG = 'N') ) OR (PEN.OIPL_ID IS NOT NULL
AND EXISTS ( SELECT NULL
FROM BEN_ELIG_PER_F PEP
, BEN_ELIG_PER_OPT_F EPO
, BEN_OIPL_F OIPL
WHERE PEP.PL_ID = PEN.PL_ID
AND EPO.ELIG_PER_ID = PEP.ELIG_PER_ID
AND PEP.PERSON_ID = PEN.PERSON_ID
AND EPO.OPT_ID = OIPL.OPT_ID
AND OIPL.OIPL_ID = PEN.OIPL_ID
AND NVL(PEP.PGM_ID
, -1) = NVL(PEN.PGM_ID
, -1)
AND PEP.BUSINESS_GROUP_ID = PEN.BUSINESS_GROUP_ID
AND PEP.BUSINESS_GROUP_ID = EPO.BUSINESS_GROUP_ID
AND OIPL.BUSINESS_GROUP_ID = EPO.BUSINESS_GROUP_ID
AND PEN.EFFECTIVE_START_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND EPO.EFFECTIVE_START_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.EFFECTIVE_START_DATE BETWEEN OIPL.EFFECTIVE_START_DATE
AND OIPL.EFFECTIVE_END_DATE
AND EPO.ELIG_FLAG = 'N') )