VIEW: APPS.BEN_ACTL_PREM_D

Source

View Text - Preformatted

SELECT apr.actl_prem_id, apr.effective_start_date, apr.effective_end_date, apr.NAME, acty_ref.meaning, uom.NAME, apr.cr_lkbk_crnt_py_only_flag, apr.last_update_date, fuser.user_name, prtl_mth.meaning, prtl_mth2.formula_name, apr.wsh_rl_dy_mo_num, rt_typ.meaning, apr.val, calc_mthd.meaning, prdct.meaning, rndg.meaning, rndg2.formula_name, val_calc.formula_name, asnmt.meaning, asnmt_lvl.meaning, actl_prem.meaning, prem_pyr.meaning, apr.cr_lkbk_val, prspctv_r_rtspctv.meaning, apr.upr_lmt_val, upr_lmt.formula_name, apr.lwr_lmt_val, lwr_lmt.formula_name, opt.NAME, pln.NAME FROM ben_actl_prem_f apr, fnd_user_view fuser, ben_pl_f pln, ben_oipl_f oipl, ben_opt_f opt, hr_lookups prtl_mth, hr_lookups actl_prem, hr_lookups asnmt, hr_lookups asnmt_lvl, hr_lookups prem_pyr, hr_lookups prspctv_r_rtspctv, hr_lookups prdct, hr_lookups acty_ref, hr_lookups rndg, hr_lookups calc_mthd, hr_lookups rt_typ, ff_formulas_f prtl_mth2, ff_formulas_f rndg2, ff_formulas_f upr_lmt, ff_formulas_f lwr_lmt, ff_formulas_f val_calc, fnd_currencies_vl uom WHERE fuser.user_id(+) = apr.last_updated_by AND pln.pl_id(+) = apr.pl_id AND oipl.oipl_id(+) = apr.oipl_id AND opt.opt_id (+) = oipl.opt_id AND prtl_mth.lookup_type(+) = 'BEN_PRTL_MO_DET_MTHD' AND prtl_mth.lookup_code(+) = apr.prtl_mo_det_mthd_cd AND actl_prem.lookup_type(+) = 'BEN_PREM_TYP' AND actl_prem.lookup_code(+) = apr.actl_prem_typ_cd AND asnmt.lookup_type(+) = 'BEN_PREM_ASNMT' AND asnmt.lookup_code(+) = apr.prem_asnmt_cd AND asnmt_lvl.lookup_type(+) = 'BEN_PREM_ASNMT_LVL' AND asnmt_lvl.lookup_code(+) = apr.prem_asnmt_lvl_cd AND prem_pyr.lookup_type(+) = 'BEN_PREM_PYR' AND prem_pyr.lookup_code(+) = apr.prem_pyr_cd AND prspctv_r_rtspctv.lookup_type(+) = 'BEN_PRSPCTV_R_RTSPCTV' AND prspctv_r_rtspctv.lookup_code(+) = apr.prsptv_r_rtsptv_cd AND prdct.lookup_type(+) = 'BEN_PRDCT' AND prdct.lookup_code(+) = apr.prdct_cd AND acty_ref.lookup_type(+) = 'BEN_ACTY_REF_PERD' AND acty_ref.lookup_code(+) = apr.acty_ref_perd_cd AND rndg.lookup_type(+) = 'BEN_RNDG' AND rndg.lookup_code(+) = apr.rndg_cd AND calc_mthd.lookup_type(+) = 'BEN_ACTL_PREM_MLT' AND calc_mthd.lookup_code(+) = apr.mlt_cd AND rt_typ.lookup_type(+) = 'BEN_BNFT_RT_TYP' AND rt_typ.lookup_code(+) = apr.bnft_rt_typ_cd AND prtl_mth2.formula_id(+) = apr.prtl_mo_det_mthd_rl AND rndg2.formula_id(+) = apr.rndg_rl AND upr_lmt.formula_id(+) = apr.upr_lmt_calc_rl AND lwr_lmt.formula_id(+) = apr.lwr_lmt_calc_rl AND val_calc.formula_id(+) = apr.val_calc_rl AND uom.currency_code(+) = apr.uom AND apr.effective_start_date BETWEEN pln.effective_start_date(+) AND pln.effective_end_date(+) AND apr.effective_start_date BETWEEN oipl.effective_start_date(+) AND oipl.effective_end_date(+) AND oipl.effective_start_date BETWEEN opt.effective_start_date(+) AND opt.effective_end_date(+)

View Text - HTML Formatted

SELECT APR.ACTL_PREM_ID
, APR.EFFECTIVE_START_DATE
, APR.EFFECTIVE_END_DATE
, APR.NAME
, ACTY_REF.MEANING
, UOM.NAME
, APR.CR_LKBK_CRNT_PY_ONLY_FLAG
, APR.LAST_UPDATE_DATE
, FUSER.USER_NAME
, PRTL_MTH.MEANING
, PRTL_MTH2.FORMULA_NAME
, APR.WSH_RL_DY_MO_NUM
, RT_TYP.MEANING
, APR.VAL
, CALC_MTHD.MEANING
, PRDCT.MEANING
, RNDG.MEANING
, RNDG2.FORMULA_NAME
, VAL_CALC.FORMULA_NAME
, ASNMT.MEANING
, ASNMT_LVL.MEANING
, ACTL_PREM.MEANING
, PREM_PYR.MEANING
, APR.CR_LKBK_VAL
, PRSPCTV_R_RTSPCTV.MEANING
, APR.UPR_LMT_VAL
, UPR_LMT.FORMULA_NAME
, APR.LWR_LMT_VAL
, LWR_LMT.FORMULA_NAME
, OPT.NAME
, PLN.NAME
FROM BEN_ACTL_PREM_F APR
, FND_USER_VIEW FUSER
, BEN_PL_F PLN
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, HR_LOOKUPS PRTL_MTH
, HR_LOOKUPS ACTL_PREM
, HR_LOOKUPS ASNMT
, HR_LOOKUPS ASNMT_LVL
, HR_LOOKUPS PREM_PYR
, HR_LOOKUPS PRSPCTV_R_RTSPCTV
, HR_LOOKUPS PRDCT
, HR_LOOKUPS ACTY_REF
, HR_LOOKUPS RNDG
, HR_LOOKUPS CALC_MTHD
, HR_LOOKUPS RT_TYP
, FF_FORMULAS_F PRTL_MTH2
, FF_FORMULAS_F RNDG2
, FF_FORMULAS_F UPR_LMT
, FF_FORMULAS_F LWR_LMT
, FF_FORMULAS_F VAL_CALC
, FND_CURRENCIES_VL UOM
WHERE FUSER.USER_ID(+) = APR.LAST_UPDATED_BY
AND PLN.PL_ID(+) = APR.PL_ID
AND OIPL.OIPL_ID(+) = APR.OIPL_ID
AND OPT.OPT_ID (+) = OIPL.OPT_ID
AND PRTL_MTH.LOOKUP_TYPE(+) = 'BEN_PRTL_MO_DET_MTHD'
AND PRTL_MTH.LOOKUP_CODE(+) = APR.PRTL_MO_DET_MTHD_CD
AND ACTL_PREM.LOOKUP_TYPE(+) = 'BEN_PREM_TYP'
AND ACTL_PREM.LOOKUP_CODE(+) = APR.ACTL_PREM_TYP_CD
AND ASNMT.LOOKUP_TYPE(+) = 'BEN_PREM_ASNMT'
AND ASNMT.LOOKUP_CODE(+) = APR.PREM_ASNMT_CD
AND ASNMT_LVL.LOOKUP_TYPE(+) = 'BEN_PREM_ASNMT_LVL'
AND ASNMT_LVL.LOOKUP_CODE(+) = APR.PREM_ASNMT_LVL_CD
AND PREM_PYR.LOOKUP_TYPE(+) = 'BEN_PREM_PYR'
AND PREM_PYR.LOOKUP_CODE(+) = APR.PREM_PYR_CD
AND PRSPCTV_R_RTSPCTV.LOOKUP_TYPE(+) = 'BEN_PRSPCTV_R_RTSPCTV'
AND PRSPCTV_R_RTSPCTV.LOOKUP_CODE(+) = APR.PRSPTV_R_RTSPTV_CD
AND PRDCT.LOOKUP_TYPE(+) = 'BEN_PRDCT'
AND PRDCT.LOOKUP_CODE(+) = APR.PRDCT_CD
AND ACTY_REF.LOOKUP_TYPE(+) = 'BEN_ACTY_REF_PERD'
AND ACTY_REF.LOOKUP_CODE(+) = APR.ACTY_REF_PERD_CD
AND RNDG.LOOKUP_TYPE(+) = 'BEN_RNDG'
AND RNDG.LOOKUP_CODE(+) = APR.RNDG_CD
AND CALC_MTHD.LOOKUP_TYPE(+) = 'BEN_ACTL_PREM_MLT'
AND CALC_MTHD.LOOKUP_CODE(+) = APR.MLT_CD
AND RT_TYP.LOOKUP_TYPE(+) = 'BEN_BNFT_RT_TYP'
AND RT_TYP.LOOKUP_CODE(+) = APR.BNFT_RT_TYP_CD
AND PRTL_MTH2.FORMULA_ID(+) = APR.PRTL_MO_DET_MTHD_RL
AND RNDG2.FORMULA_ID(+) = APR.RNDG_RL
AND UPR_LMT.FORMULA_ID(+) = APR.UPR_LMT_CALC_RL
AND LWR_LMT.FORMULA_ID(+) = APR.LWR_LMT_CALC_RL
AND VAL_CALC.FORMULA_ID(+) = APR.VAL_CALC_RL
AND UOM.CURRENCY_CODE(+) = APR.UOM
AND APR.EFFECTIVE_START_DATE BETWEEN PLN.EFFECTIVE_START_DATE(+)
AND PLN.EFFECTIVE_END_DATE(+)
AND APR.EFFECTIVE_START_DATE BETWEEN OIPL.EFFECTIVE_START_DATE(+)
AND OIPL.EFFECTIVE_END_DATE(+)
AND OIPL.EFFECTIVE_START_DATE BETWEEN OPT.EFFECTIVE_START_DATE(+)
AND OPT.EFFECTIVE_END_DATE(+)