[Home] [Help]
View: BEN_WFREP_RATE_INFO_VW
View Text
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; */
Columns
Name |
BENEFIT_ACTION_ID |
PERSON_ID |
PGM_NAME |
PL_NAME |
OPT_NAME |
BNFT_RT_TYP_CD_NAME |
DFLT_FLAG |
VAL |
TX_TYP_CD_NAME |
ACTY_TYP_CD_NAME |
MN_ELCN_VAL |
MX_ELCN_VAL |
INCRMT_ELCN_VAL |
DFLT_VAL |
RATE_NAME |