VIEW: APPS.BEN_VRBL_RT_PRFL_D

Source

View Text - Preformatted

SELECT vpf.vrbl_rt_prfl_id, vpf.effective_start_date, vpf.effective_end_date, hr_general.decode_lookup ('BEN_ACTY_TYP', vpf.acty_typ_cd), hr_general.decode_lookup ('BEN_RT_TYP', vpf.rt_typ_cd), hr_general.decode_lookup ('BEN_RT_TYP', vpf.bnft_rt_typ_cd), hr_general.decode_lookup ('BEN_TX_TYP', vpf.tx_typ_cd), hr_general.decode_lookup ('BEN_RT_TRTMT', vpf.vrbl_rt_trtmt_cd), hr_general.decode_lookup ('BEN_ACTY_REF_PERD', vpf.acty_ref_perd_cd), DECODE (vpf.vrbl_usg_cd, 'RT', hr_general.decode_lookup ('BEN_MLT', vpf.mlt_cd), 'CVG', hr_general.decode_lookup ('BEN_CVG_MLT', vpf.mlt_cd), 'ACP', hr_general.decode_lookup ('BEN_ACTL_PREM_MLT', vpf.mlt_cd ) ), vpf.incrmnt_elcn_val, vpf.dflt_elcn_val, vpf.mx_elcn_val, vpf.mn_elcn_val, vpf.val, vpf.NAME, fform1.formula_name, hr_general.decode_lookup ('BEN_STAT', vpf.vrbl_rt_prfl_stat_cd), vpf.last_update_date, fuser.user_name, hr_general.decode_lookup ('BEN_VRBL_USG', vpf.vrbl_usg_cd), hr_general.decode_lookup ('BEN_RNDG', vpf.rndg_cd), fform2.formula_name, vpf.ultmt_upr_lmt, vpf.ultmt_lwr_lmt, fform3.formula_name, fform4.formula_name, pt.NAME || ' - ' || opt.NAME pl_typ_opt_typ_name, pl.NAME plan_name FROM ben_vrbl_rt_prfl_f vpf, fnd_user fuser, ff_formulas_f fform1, ff_formulas_f fform2, ff_formulas_f fform3, ff_formulas_f fform4, ben_pl_typ_opt_typ_f pto, ben_pl_typ_f pt, ben_opt_f opt, ben_pl_f pl, ben_oipl_f oipl WHERE fuser.user_id(+) = vpf.last_updated_by AND fform1.formula_id(+) = vpf.val_calc_rl AND fform2.formula_id(+) = vpf.rndg_rl AND fform3.formula_id(+) = vpf.ultmt_upr_lmt_calc_rl AND fform4.formula_id(+) = vpf.ultmt_lwr_lmt_calc_rl AND vpf.effective_start_date BETWEEN fform1.effective_start_date(+) AND fform1.effective_end_date(+) AND vpf.effective_start_date BETWEEN fform2.effective_start_date(+) AND fform2.effective_end_date(+) AND vpf.effective_start_date BETWEEN fform3.effective_start_date(+) AND fform3.effective_end_date(+) AND vpf.effective_start_date BETWEEN fform4.effective_start_date(+) AND fform4.effective_end_date(+) AND vpf.pl_typ_opt_typ_id = pto.pl_typ_opt_typ_id(+) AND vpf.effective_start_date BETWEEN pto.effective_start_date(+) AND pto.effective_end_date(+) AND pto.pl_typ_id = pt.pl_typ_id(+) AND vpf.effective_start_date BETWEEN NVL (pt.effective_start_date, vpf.effective_start_date ) AND NVL (pt.effective_end_date, vpf.effective_end_date ) AND pto.opt_id = opt.opt_id(+) AND vpf.effective_start_date BETWEEN NVL (opt.effective_start_date, vpf.effective_start_date ) AND NVL (opt.effective_end_date, vpf.effective_end_date ) AND oipl.oipl_id(+) = vpf.oipl_id AND vpf.effective_start_date BETWEEN oipl.effective_start_date(+) AND oipl.effective_end_date(+) AND pl.pl_id(+) = oipl.pl_id AND vpf.effective_start_date BETWEEN NVL (pl.effective_start_date, vpf.effective_start_date ) AND NVL (pl.effective_end_date, vpf.effective_end_date )

View Text - HTML Formatted

SELECT VPF.VRBL_RT_PRFL_ID
, VPF.EFFECTIVE_START_DATE
, VPF.EFFECTIVE_END_DATE
, HR_GENERAL.DECODE_LOOKUP ('BEN_ACTY_TYP'
, VPF.ACTY_TYP_CD)
, HR_GENERAL.DECODE_LOOKUP ('BEN_RT_TYP'
, VPF.RT_TYP_CD)
, HR_GENERAL.DECODE_LOOKUP ('BEN_RT_TYP'
, VPF.BNFT_RT_TYP_CD)
, HR_GENERAL.DECODE_LOOKUP ('BEN_TX_TYP'
, VPF.TX_TYP_CD)
, HR_GENERAL.DECODE_LOOKUP ('BEN_RT_TRTMT'
, VPF.VRBL_RT_TRTMT_CD)
, HR_GENERAL.DECODE_LOOKUP ('BEN_ACTY_REF_PERD'
, VPF.ACTY_REF_PERD_CD)
, DECODE (VPF.VRBL_USG_CD
, 'RT'
, HR_GENERAL.DECODE_LOOKUP ('BEN_MLT'
, VPF.MLT_CD)
, 'CVG'
, HR_GENERAL.DECODE_LOOKUP ('BEN_CVG_MLT'
, VPF.MLT_CD)
, 'ACP'
, HR_GENERAL.DECODE_LOOKUP ('BEN_ACTL_PREM_MLT'
, VPF.MLT_CD ) )
, VPF.INCRMNT_ELCN_VAL
, VPF.DFLT_ELCN_VAL
, VPF.MX_ELCN_VAL
, VPF.MN_ELCN_VAL
, VPF.VAL
, VPF.NAME
, FFORM1.FORMULA_NAME
, HR_GENERAL.DECODE_LOOKUP ('BEN_STAT'
, VPF.VRBL_RT_PRFL_STAT_CD)
, VPF.LAST_UPDATE_DATE
, FUSER.USER_NAME
, HR_GENERAL.DECODE_LOOKUP ('BEN_VRBL_USG'
, VPF.VRBL_USG_CD)
, HR_GENERAL.DECODE_LOOKUP ('BEN_RNDG'
, VPF.RNDG_CD)
, FFORM2.FORMULA_NAME
, VPF.ULTMT_UPR_LMT
, VPF.ULTMT_LWR_LMT
, FFORM3.FORMULA_NAME
, FFORM4.FORMULA_NAME
, PT.NAME || ' - ' || OPT.NAME PL_TYP_OPT_TYP_NAME
, PL.NAME PLAN_NAME
FROM BEN_VRBL_RT_PRFL_F VPF
, FND_USER FUSER
, FF_FORMULAS_F FFORM1
, FF_FORMULAS_F FFORM2
, FF_FORMULAS_F FFORM3
, FF_FORMULAS_F FFORM4
, BEN_PL_TYP_OPT_TYP_F PTO
, BEN_PL_TYP_F PT
, BEN_OPT_F OPT
, BEN_PL_F PL
, BEN_OIPL_F OIPL
WHERE FUSER.USER_ID(+) = VPF.LAST_UPDATED_BY
AND FFORM1.FORMULA_ID(+) = VPF.VAL_CALC_RL
AND FFORM2.FORMULA_ID(+) = VPF.RNDG_RL
AND FFORM3.FORMULA_ID(+) = VPF.ULTMT_UPR_LMT_CALC_RL
AND FFORM4.FORMULA_ID(+) = VPF.ULTMT_LWR_LMT_CALC_RL
AND VPF.EFFECTIVE_START_DATE BETWEEN FFORM1.EFFECTIVE_START_DATE(+)
AND FFORM1.EFFECTIVE_END_DATE(+)
AND VPF.EFFECTIVE_START_DATE BETWEEN FFORM2.EFFECTIVE_START_DATE(+)
AND FFORM2.EFFECTIVE_END_DATE(+)
AND VPF.EFFECTIVE_START_DATE BETWEEN FFORM3.EFFECTIVE_START_DATE(+)
AND FFORM3.EFFECTIVE_END_DATE(+)
AND VPF.EFFECTIVE_START_DATE BETWEEN FFORM4.EFFECTIVE_START_DATE(+)
AND FFORM4.EFFECTIVE_END_DATE(+)
AND VPF.PL_TYP_OPT_TYP_ID = PTO.PL_TYP_OPT_TYP_ID(+)
AND VPF.EFFECTIVE_START_DATE BETWEEN PTO.EFFECTIVE_START_DATE(+)
AND PTO.EFFECTIVE_END_DATE(+)
AND PTO.PL_TYP_ID = PT.PL_TYP_ID(+)
AND VPF.EFFECTIVE_START_DATE BETWEEN NVL (PT.EFFECTIVE_START_DATE
, VPF.EFFECTIVE_START_DATE )
AND NVL (PT.EFFECTIVE_END_DATE
, VPF.EFFECTIVE_END_DATE )
AND PTO.OPT_ID = OPT.OPT_ID(+)
AND VPF.EFFECTIVE_START_DATE BETWEEN NVL (OPT.EFFECTIVE_START_DATE
, VPF.EFFECTIVE_START_DATE )
AND NVL (OPT.EFFECTIVE_END_DATE
, VPF.EFFECTIVE_END_DATE )
AND OIPL.OIPL_ID(+) = VPF.OIPL_ID
AND VPF.EFFECTIVE_START_DATE BETWEEN OIPL.EFFECTIVE_START_DATE(+)
AND OIPL.EFFECTIVE_END_DATE(+)
AND PL.PL_ID(+) = OIPL.PL_ID
AND VPF.EFFECTIVE_START_DATE BETWEEN NVL (PL.EFFECTIVE_START_DATE
, VPF.EFFECTIVE_START_DATE )
AND NVL (PL.EFFECTIVE_END_DATE
, VPF.EFFECTIVE_END_DATE )