VIEW: APPS.BEN_CNTPT_V

Source

View Text - Preformatted

SELECT pen.person_id ,pln.name ,plt.name ,opt.name ,bnft_typ.meaning ,pen.bnft_amt ,uom.name ,nnmntry_uom.meaning ,pen.prtt_is_cvrd_flag ,prv.rt_strt_dt ,prv.rt_end_dt ,rt_typ.meaning ,prv.rt_val ,tx_typ.meaning ,acty_typ.meaning ,ee.screen_entry_value ,pen.pl_id ,abr.input_value_id ,abr.acty_base_rt_id ,pen.oipl_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 ,pay_element_entry_values_f ee ,ben_acty_base_rt_f abr ,ben_pl_f pln ,ben_pl_typ_f plt ,ben_oipl_f oipl ,ben_opt_f opt ,hr_lookups bnft_typ ,hr_lookups rt_typ ,hr_lookups tx_typ ,hr_lookups acty_typ ,hr_lookups nnmntry_uom ,fnd_currencies_vl 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(prv.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 prv.element_entry_value_id = ee.element_entry_value_id and se.effective_date between ee.effective_start_date and ee.effective_end_date 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.invk_flx_cr_pl_flag = 'N' and pen.pl_id = pln.pl_id and se.effective_date between pln.effective_start_date and pln.effective_end_date and pen.pl_typ_id = plt.pl_typ_id and se.effective_date between plt.effective_start_date and plt.effective_end_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.bnft_typ_cd = bnft_typ.lookup_code(+) and bnft_typ.lookup_type(+) = 'BEN_BNFT_TYP' and se.effective_date between nvl(bnft_typ.start_date_active, se.effective_date) and nvl(bnft_typ.end_date_active, se.effective_date) and pen.uom = uom.currency_code(+) 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 prv.rt_typ_cd = rt_typ.lookup_code(+) and rt_typ.lookup_type(+) = 'BEN_RT_TYP' and se.effective_date between nvl(rt_typ.start_date_active, se.effective_date) and nvl(rt_typ.end_date_active, se.effective_date) and prv.tx_typ_cd = tx_typ.lookup_code(+) and tx_typ.lookup_type(+) = 'BEN_TX_TYP' and se.effective_date between nvl(tx_typ.start_date_active, se.effective_date) and nvl(tx_typ.end_date_active, se.effective_date) and prv.acty_typ_cd = acty_typ.lookup_code(+) and acty_typ.lookup_type(+) = 'BEN_ACTY_TYP' and se.effective_date between nvl(acty_typ.start_date_active, se.effective_date) and nvl(acty_typ.end_date_active, se.effective_date)

View Text - HTML Formatted

SELECT PEN.PERSON_ID
, PLN.NAME
, PLT.NAME
, OPT.NAME
, BNFT_TYP.MEANING
, PEN.BNFT_AMT
, UOM.NAME
, NNMNTRY_UOM.MEANING
, PEN.PRTT_IS_CVRD_FLAG
, PRV.RT_STRT_DT
, PRV.RT_END_DT
, RT_TYP.MEANING
, PRV.RT_VAL
, TX_TYP.MEANING
, ACTY_TYP.MEANING
, EE.SCREEN_ENTRY_VALUE
, PEN.PL_ID
, ABR.INPUT_VALUE_ID
, ABR.ACTY_BASE_RT_ID
, PEN.OIPL_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
, PAY_ELEMENT_ENTRY_VALUES_F EE
, BEN_ACTY_BASE_RT_F ABR
, BEN_PL_F PLN
, BEN_PL_TYP_F PLT
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, HR_LOOKUPS BNFT_TYP
, HR_LOOKUPS RT_TYP
, HR_LOOKUPS TX_TYP
, HR_LOOKUPS ACTY_TYP
, HR_LOOKUPS NNMNTRY_UOM
, FND_CURRENCIES_VL 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(PRV.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 PRV.ELEMENT_ENTRY_VALUE_ID = EE.ELEMENT_ENTRY_VALUE_ID
AND SE.EFFECTIVE_DATE BETWEEN EE.EFFECTIVE_START_DATE
AND EE.EFFECTIVE_END_DATE
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.INVK_FLX_CR_PL_FLAG = 'N'
AND PEN.PL_ID = PLN.PL_ID
AND SE.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND PEN.PL_TYP_ID = PLT.PL_TYP_ID
AND SE.EFFECTIVE_DATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_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.BNFT_TYP_CD = BNFT_TYP.LOOKUP_CODE(+)
AND BNFT_TYP.LOOKUP_TYPE(+) = 'BEN_BNFT_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(BNFT_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(BNFT_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PEN.UOM = UOM.CURRENCY_CODE(+)
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 PRV.RT_TYP_CD = RT_TYP.LOOKUP_CODE(+)
AND RT_TYP.LOOKUP_TYPE(+) = 'BEN_RT_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(RT_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(RT_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PRV.TX_TYP_CD = TX_TYP.LOOKUP_CODE(+)
AND TX_TYP.LOOKUP_TYPE(+) = 'BEN_TX_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(TX_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(TX_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PRV.ACTY_TYP_CD = ACTY_TYP.LOOKUP_CODE(+)
AND ACTY_TYP.LOOKUP_TYPE(+) = 'BEN_ACTY_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(ACTY_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(ACTY_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)