VIEW: APPS.BEN_WFREP_RATE_INFO_VW

Source

View Text - Preformatted

SELECT bft.benefit_action_id, bri.person_id, pgm.name pgm_name, pln.name pl_name, opt.name opt_name, hr_general.decode_lookup('BEN_BNFT_RT_TYP',bri.bnft_rt_typ_cd) bnft_rt_typ_cd_name, bri.dflt_flag, bri.val, hr_general.decode_lookup('BEN_TX_TYP',bri.tx_typ_cd) tx_typ_cd_name, hr_general.decode_lookup('BEN_ACTY_TYP',bri.acty_typ_cd) acty_typ_cd_name, bri.mn_elcn_val, bri.mx_elcn_val, bri.incrmt_elcn_val, bri.dflt_val, decode(pgm.name,null,null,pgm.name||' - ')||decode(pln.name,null,pln2.name||' - ',pln.name)||decode(opt.name,null,null,' - '||opt.name) rate_name from ben_benefit_actions bft, ben_pl_f pln, ben_pgm_f pgm, ben_oipl_f cop, ben_opt_f opt, ben_pl_f pln2, ben_batch_rate_info bri WHERE bft.benefit_action_id = bri.benefit_action_id and pgm.pgm_id(+) = bri.pgm_id and bft.process_date between nvl(pgm.effective_start_date,bft.process_date) and nvl(pgm.effective_end_date,bft.process_date) and pln.pl_id(+) = bri.pl_id and bft.process_date between nvl(pln.effective_start_date,bft.process_date) and nvl(pln.effective_end_date,bft.process_date) and cop.oipl_id(+) = bri.oipl_id and bft.process_date between nvl(cop.effective_start_date,bft.process_date) and nvl(cop.effective_end_date,bft.process_date) and pln2.pl_id(+) = cop.pl_id and bft.process_date between nvl(cop.effective_start_date,bft.process_date) and nvl(cop.effective_end_date,bft.process_date) and opt.opt_id(+) = cop.opt_id and bft.process_date between nvl(opt.effective_start_date,bft.process_date) and nvl(opt.effective_end_date,bft.process_date) /* order by pgm.name, pln2.name, pln.name, opt.name; */

View Text - HTML Formatted

SELECT BFT.BENEFIT_ACTION_ID
, BRI.PERSON_ID
, PGM.NAME PGM_NAME
, PLN.NAME PL_NAME
, OPT.NAME OPT_NAME
, HR_GENERAL.DECODE_LOOKUP('BEN_BNFT_RT_TYP'
, BRI.BNFT_RT_TYP_CD) BNFT_RT_TYP_CD_NAME
, BRI.DFLT_FLAG
, BRI.VAL
, HR_GENERAL.DECODE_LOOKUP('BEN_TX_TYP'
, BRI.TX_TYP_CD) TX_TYP_CD_NAME
, HR_GENERAL.DECODE_LOOKUP('BEN_ACTY_TYP'
, BRI.ACTY_TYP_CD) ACTY_TYP_CD_NAME
, BRI.MN_ELCN_VAL
, BRI.MX_ELCN_VAL
, BRI.INCRMT_ELCN_VAL
, BRI.DFLT_VAL
, DECODE(PGM.NAME
, NULL
, NULL
, PGM.NAME||' - ')||DECODE(PLN.NAME
, NULL
, PLN2.NAME||' - '
, PLN.NAME)||DECODE(OPT.NAME
, NULL
, NULL
, ' - '||OPT.NAME) RATE_NAME
FROM BEN_BENEFIT_ACTIONS BFT
, BEN_PL_F PLN
, BEN_PGM_F PGM
, BEN_OIPL_F COP
, BEN_OPT_F OPT
, BEN_PL_F PLN2
, BEN_BATCH_RATE_INFO BRI
WHERE BFT.BENEFIT_ACTION_ID = BRI.BENEFIT_ACTION_ID
AND PGM.PGM_ID(+) = BRI.PGM_ID
AND BFT.PROCESS_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND PLN.PL_ID(+) = BRI.PL_ID
AND BFT.PROCESS_DATE BETWEEN NVL(PLN.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(PLN.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND COP.OIPL_ID(+) = BRI.OIPL_ID
AND BFT.PROCESS_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(COP.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND PLN2.PL_ID(+) = COP.PL_ID
AND BFT.PROCESS_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(COP.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND OPT.OPT_ID(+) = COP.OPT_ID
AND BFT.PROCESS_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE) /* ORDER BY PGM.NAME
, PLN2.NAME
, PLN.NAME
, OPT.NAME; */