VIEW: APPS.BEN_RCD_CNTR_AMT_V

Source

View Text - Preformatted

SELECT pen.person_id ,pgm.name ,pln.name ,plt.name ,opt.name ,uom.name ,nnmntry_uom.meaning ,abr.name ,abr.acty_base_rt_id ,pen.pl_id ,pen.oipl_id ,plip.plip_id ,pen.prtt_enrt_rslt_id ,pen.effective_start_date ,pen.effective_end_date ,pen.object_version_number from ben_prtt_enrt_rslt_f pen , ben_prtt_rt_val prv ,ben_acty_base_rt_f abr ,ben_pgm_f pgm ,ben_pl_f pln ,ben_pl_typ_f plt ,ben_oipl_f oipl ,ben_opt_f opt ,ben_plip_f plip ,fnd_currencies_vl uom ,hr_lookups nnmntry_uom ,fnd_sessions se WHERE se.session_id = userenv('sessionid') and pen.sspndd_flag <> 'Y' and prv.acty_base_rt_id = abr.acty_base_rt_id and substr(nvl(abr.acty_typ_cd, 'xxx'),1,3) <> 'PBC' and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id and se.effective_date between prv.rt_strt_dt and prv.rt_end_dt and se.effective_date between abr.effective_start_date and abr.effective_end_date and se.effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt and pen.enrt_cvg_thru_dt <= pen.effective_end_date and pln.imptd_incm_calc_cd is null and pen.pgm_id = pgm.pgm_id(+) and se.effective_date between nvl( pgm.effective_start_date, se.effective_date) and nvl(pgm.effective_end_date , se.effective_date) and pen.pl_id = pln.pl_id(+) and se.effective_date between nvl(pln.effective_start_date , se.effective_date) and nvl(pln.effective_end_date , se.effective_date) and pen.pl_typ_id = plt.pl_typ_id(+) and se.effective_date between nvl( plt.effective_start_date, se.effective_date) and nvl(plt.effective_end_date , se.effective_date) and pen.oipl_id = oipl.oipl_id(+) and se.effective_date between nvl(oipl.effective_start_date , se.effective_date) and nvl(oipl.effective_end_date , se.effective_date) and oipl.opt_id = opt.opt_id(+) and se.effective_date between nvl(opt.effective_start_date , se.effective_date) and nvl(opt.effective_end_date , se.effective_date) and pen.uom = uom.currency_code(+) and se.effective_date between nvl(uom.start_date_active, se.effective_date) and nvl(uom.end_date_active, se.effective_date) and uom.enabled_flag = 'Y' and pen.bnft_nnmntry_uom = nnmntry_uom.lookup_code(+) and nnmntry_uom.lookup_type(+) = 'BEN_NNMNTRY_UOM' and se.effective_date between nvl(nnmntry_uom.start_date_active, se.effective_date) and nvl(nnmntry_uom.end_date_active, se.effective_date) and pen.pgm_id = plip.pgm_id(+) and pen.pl_id = plip.pl_id(+) and se.effective_date between nvl(plip.effective_start_date,se.effective_date) and nvl(plip.effective_end_date,se.effective_date)

View Text - HTML Formatted

SELECT PEN.PERSON_ID
, PGM.NAME
, PLN.NAME
, PLT.NAME
, OPT.NAME
, UOM.NAME
, NNMNTRY_UOM.MEANING
, ABR.NAME
, ABR.ACTY_BASE_RT_ID
, PEN.PL_ID
, PEN.OIPL_ID
, PLIP.PLIP_ID
, PEN.PRTT_ENRT_RSLT_ID
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE
, PEN.OBJECT_VERSION_NUMBER
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PRTT_RT_VAL PRV
, BEN_ACTY_BASE_RT_F ABR
, BEN_PGM_F PGM
, BEN_PL_F PLN
, BEN_PL_TYP_F PLT
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PLIP_F PLIP
, FND_CURRENCIES_VL UOM
, HR_LOOKUPS NNMNTRY_UOM
, FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID')
AND PEN.SSPNDD_FLAG <> 'Y'
AND PRV.ACTY_BASE_RT_ID = ABR.ACTY_BASE_RT_ID
AND SUBSTR(NVL(ABR.ACTY_TYP_CD
, 'XXX')
, 1
, 3) <> 'PBC'
AND PEN.PRTT_ENRT_RSLT_ID = PRV.PRTT_ENRT_RSLT_ID
AND SE.EFFECTIVE_DATE BETWEEN PRV.RT_STRT_DT
AND PRV.RT_END_DT
AND SE.EFFECTIVE_DATE BETWEEN ABR.EFFECTIVE_START_DATE
AND ABR.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN PEN.ENRT_CVG_STRT_DT
AND PEN.ENRT_CVG_THRU_DT
AND PEN.ENRT_CVG_THRU_DT <= PEN.EFFECTIVE_END_DATE
AND PLN.IMPTD_INCM_CALC_CD IS NULL
AND PEN.PGM_ID = PGM.PGM_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL( PGM.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.PL_ID = PLN.PL_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(PLN.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PLN.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.PL_TYP_ID = PLT.PL_TYP_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL( PLT.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PLT.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.OIPL_ID = OIPL.OIPL_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(OIPL.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.UOM = UOM.CURRENCY_CODE(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(UOM.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(UOM.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND UOM.ENABLED_FLAG = 'Y'
AND PEN.BNFT_NNMNTRY_UOM = NNMNTRY_UOM.LOOKUP_CODE(+)
AND NNMNTRY_UOM.LOOKUP_TYPE(+) = 'BEN_NNMNTRY_UOM'
AND SE.EFFECTIVE_DATE BETWEEN NVL(NNMNTRY_UOM.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(NNMNTRY_UOM.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PEN.PGM_ID = PLIP.PGM_ID(+)
AND PEN.PL_ID = PLIP.PL_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PLIP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)