View: BEN_ENRT_LOV_INFO_V

Product:	BEN - Advanced Benefits
Description:
Implementation/DBA Data:	APPS.BEN_ENRT_LOV_INFO_V

View Text

SELECT EPE.PTIP_ORDR_NUM
, EPE.PLIP_ORDR_NUM
, EPE.PL_ORDR_NUM
, EPE.OIPL_ORDR_NUM
, EPE.PL_TYP_ID PL_TYP_ID
, EPE.PL_ID PL_ID
, O.OPT_ID OPT_ID
, TYP.NAME PL_TYP_NAME
, P.NAME||' '||OPT.NAME PL_OPT_NAME
, EPE.OIPL_ID OIPL_ID
, EPE.PGM_ID
, EPE.PTIP_ID
, EPE.PLIP_ID
, B.NNMNTRY_UOM BNFT_NNMNTRY_UOM
, HL1.MEANING BNFT_NNMNTRY_UOM_MEANING
, NVL(B.VAL
, B.DFLT_VAL) BNFT_VAL
, B.MN_VAL MN_VAL
, B.MX_VAL MX_VAL
, B.MX_WOUT_CTFN_VAL
, B.INCRMT_VAL INCRMT_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, NVL( R1.ANN_VAL
, R1.ANN_DFLT_VAL)
, NVL(RB1.ANN_VAL
, RB1.ANN_DFLT_VAL)) ANN_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.ANN_MN_ELCN_VAL
, RB1.ANN_MN_ELCN_VAL) ANN_MN_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.ANN_MX_ELCN_VAL
, RB1.ANN_MX_ELCN_VAL) ANN_MX_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, NVL( R1.CMCD_VAL
, R1.CMCD_DFLT_VAL)
, NVL(RB1.CMCD_VAL
, RB1.CMCD_DFLT_VAL)) CMCD_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.CMCD_MN_ELCN_VAL
, RB1.CMCD_MN_ELCN_VAL) CMCD_MN_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.CMCD_MX_ELCN_VAL
, RB1.CMCD_MX_ELCN_VAL) CMCD_MX_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, HL3.MEANING
, HLB3.MEANING) CMCD_REF_PERD
, DECODE(B.ENRT_BNFT_ID
, NULL
, NVL(R1.VAL
, R1.DFLT_VAL)
, NVL(RB1.VAL
, RB1.DFLT_VAL)) PRE_TAX_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.TX_TYP_CD
, RB1.TX_TYP_CD) TX_TYP_CD
, DECODE(B.ENRT_BNFT_ID
, NULL
, HL2.MEANING
, HLB2.MEANING) TX_TYP
, EPE.ENRT_CVG_STRT_DT_CD
, HL4.MEANING ENRT_CVG_STRT_DT_MEANING
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.RT_STRT_DT
, RB1.RT_STRT_DT) RT_STRT_DT
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.NNMNTRY_UOM
, RB1.NNMNTRY_UOM) RT_NNMNTRY_UOM
, DECODE(B.ENRT_BNFT_ID
, NULL
, HL5.MEANING
, HLB5.MEANING) RT_NNMNTRY_UOM_MEANING
, NULL AFTR_TAX_VAL
, NULL NET_VAL
, DECODE(EPE.CRNTLY_ENRD_FLAG
, 'N'
, 'N'
, 'Y'
, DECODE(B.ENRT_BNFT_ID
, NULL
, 'Y'
, B.CRNTLY_ENRLD_FLAG)) CRNTLY_ENRD_FLAG
, DECODE(EPE.CRNTLY_ENRD_FLAG
, 'N'
, NULL
, 'Y'
, DECODE(B.ENRT_BNFT_ID
, NULL
, ' *'
, DECODE(B.CRNTLY_ENRLD_FLAG
, 'N'
, NULL
, 'Y'
, ' *'))) CRNTLY_ENRD_LOV
, EPE.BUSINESS_GROUP_ID
, EPE.MNDTRY_FLAG MNDTRY_FLAG
, DECODE(EPE.MNDTRY_FLAG
, 'Y'
, ' *'
, 'N'
, NULL) MNDTRY_LOV
, DECODE(EPE.DFLT_FLAG
, 'N'
, 'N'
, 'Y'
, DECODE(B.ENRT_BNFT_ID
, NULL
, 'Y'
, B.DFLT_FLAG)) DFLT_FLAG
, EPE.SPCL_RT_OIPL_ID
, EPE.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID
, EPE.ERLST_DEENRT_DT
, EPE.ENRT_CVG_STRT_DT
, EPE.PER_IN_LER_ID
, EPE.OBJECT_VERSION_NUMBER CHOICE_OVN
, EPE.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.ENRT_RT_ID
, RB1.ENRT_RT_ID) PRE_TAX_RT_ID
, NULL AFTR_TAX_RT_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.ACTY_BASE_RT_ID
, RB1.ACTY_BASE_RT_ID) PRE_TAX_BASE_RT_ID
, NULL AFTR_TAX_BASE_RT_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.DECR_BNFT_PRVDR_POOL_ID
, RB1.DECR_BNFT_PRVDR_POOL_ID) PRE_TAX_RT_POOL_ID
, NULL AFTR_TAX_RT_POOL_ID
, B.ENRT_BNFT_ID ENRT_BNFT_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.MN_ELCN_VAL
, RB1.MN_ELCN_VAL) MN_ELCN_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.MX_ELCN_VAL
, RB1.MX_ELCN_VAL) MX_ELCN_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.INCRMT_ELCN_VAL
, RB1.INCRMT_ELCN_VAL) INCRMT_ELCN_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, NVL(R1.ENTR_VAL_AT_ENRT_FLAG
, 'N')
, NVL(RB1.ENTR_VAL_AT_ENRT_FLAG
, 'N')) PRE_ENTR_VAL_FLAG
, NULL AFTR_ENTR_VAL_FLAG
, DECODE(B.ENRT_BNFT_ID
, NULL
, NVL(R1.ENTR_ANN_VAL_FLAG
, 'N')
, NVL(RB1.ENTR_ANN_VAL_FLAG
, 'N'))ENTR_ANN_VAL_FLAG
, NVL(R1.RL_CRS_ONLY_FLAG
, 'N') RT_ROLL_CRS_FLAG
, EPE.ROLL_CRS_FLAG CHC_ROLL_CRS_FLAG
, 'N' SSPNDD_FLAG
, 'N' CTFN_PRVDD_FLAG
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.RT_TYP_CD
, RB1.RT_TYP_CD) RT_TYP_CD
, DECODE(EPE.CTFN_RQD_FLAG
, 'N'
, DECODE(B.ENRT_BNFT_ID
, NULL
, 'N'
, DECODE(B.CTFN_RQD_FLAG
, 'N'
, 'N'
, 'Y'))
, 'Y') CTFN_RQD_FLAG
, DECODE(EPE.CTFN_RQD_FLAG
, 'N'
, DECODE(B.ENRT_BNFT_ID
, NULL
, NULL
, DECODE(B.CTFN_RQD_FLAG
, 'N'
, NULL
, '*'))
, '*') CTFN_RQD_LOV
, B.ENTR_VAL_AT_ENRT_FLAG
, DECODE(B.ENRT_BNFT_ID
, NULL
, HL6.MEANING
, HLB6.MEANING) ACTY_TYP_CD_MEANING
, DECODE(EPE.IN_PNDG_WKFLOW_FLAG
, 'Y'
, '*'
, NULL ) IN_PNDG_WKFLOW_FLAG_MEANING
, EPE.IN_PNDG_WKFLOW_FLAG
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_OIPL_F O
, BEN_OPT_F OPT
, BEN_PL_F P
, BEN_PL_TYP_F TYP
, BEN_ENRT_BNFT B
, BEN_ENRT_RT R1
, BEN_ENRT_RT RB1
, HR_LOOKUPS HL1
, HR_LOOKUPS HL2
, HR_LOOKUPS HLB2
, HR_LOOKUPS HL3
, HR_LOOKUPS HLB3
, HR_LOOKUPS HL4
, HR_LOOKUPS HL5
, HR_LOOKUPS HLB5
, HR_LOOKUPS HL6
, HR_LOOKUPS HLB6
, FND_SESSIONS F
, BEN_LER_F LER
WHERE EPE.OIPL_ID = O.OIPL_ID(+)
AND F.EFFECTIVE_DATE BETWEEN NVL(O.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(O.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND O.OIPL_STAT_CD(+) = 'A'
AND O.OPT_ID = OPT.OPT_ID(+)
AND F.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND EPE.PL_ID = P.PL_ID
AND F.EFFECTIVE_DATE BETWEEN NVL(P.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(P.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND EPE.PL_TYP_ID = TYP.PL_TYP_ID
AND F.EFFECTIVE_DATE BETWEEN NVL(TYP.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(TYP.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND TYP.PL_TYP_STAT_CD = 'A'
AND B.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND HL1.LOOKUP_TYPE(+) = 'BEN_NNMNTRY_UOM'
AND HL1.LOOKUP_CODE(+) = B.NNMNTRY_UOM
AND F.EFFECTIVE_DATE BETWEEN NVL(HL1.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HL1.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HL1.ENABLED_FLAG(+) = 'Y'
AND HL2.LOOKUP_TYPE(+) = 'BEN_TX_TYP'
AND HL2.LOOKUP_CODE(+) = R1.TX_TYP_CD
AND F.EFFECTIVE_DATE BETWEEN NVL(HL2.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HL2.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HL2.ENABLED_FLAG(+) = 'Y'
AND HL3.LOOKUP_TYPE(+) = 'BEN_CMCD_REF_PERD'
AND HL3.LOOKUP_CODE(+) = R1.CMCD_ACTY_REF_PERD_CD
AND F.EFFECTIVE_DATE BETWEEN NVL(HL3.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HL3.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HL3.ENABLED_FLAG(+) = 'Y'
AND HL4.LOOKUP_TYPE(+) = 'BEN_ENRT_CVG_STRT_DT'
AND HL4.LOOKUP_CODE(+) = EPE.ENRT_CVG_STRT_DT_CD
AND F.EFFECTIVE_DATE BETWEEN NVL(HL4.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HL4.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HL4.ENABLED_FLAG(+) = 'Y'
AND HLB3.LOOKUP_TYPE(+) = 'BEN_CMCD_REF_PERD'
AND HLB3.LOOKUP_CODE(+) = RB1.CMCD_ACTY_REF_PERD_CD
AND F.EFFECTIVE_DATE BETWEEN NVL(HLB3.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HLB3.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HLB3.ENABLED_FLAG(+) = 'Y'
AND HLB2.LOOKUP_TYPE(+) = 'BEN_TX_TYP'
AND HLB2.LOOKUP_CODE(+) = RB1.TX_TYP_CD
AND F.EFFECTIVE_DATE BETWEEN NVL(HLB2.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HLB2.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HLB2.ENABLED_FLAG(+) = 'Y'
AND HL5.LOOKUP_TYPE(+) = 'BEN_NNMNTRY_UOM'
AND HL5.LOOKUP_CODE(+) = R1.NNMNTRY_UOM
AND F.EFFECTIVE_DATE BETWEEN NVL(HL5.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HL5.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HL5.ENABLED_FLAG(+) = 'Y'
AND HLB5.LOOKUP_TYPE(+) = 'BEN_NNMNTRY_UOM'
AND HLB5.LOOKUP_CODE(+) = RB1.NNMNTRY_UOM
AND F.EFFECTIVE_DATE BETWEEN NVL(HLB5.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HLB5.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HLB5.ENABLED_FLAG(+) = 'Y'
AND HL6.LOOKUP_TYPE(+) = 'BEN_ACTY_TYP'
AND HL6.LOOKUP_CODE(+) = R1.ACTY_TYP_CD
AND F.EFFECTIVE_DATE BETWEEN NVL(HL6.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HL6.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HL6.ENABLED_FLAG(+) = 'Y'
AND HLB6.LOOKUP_TYPE(+) = 'BEN_ACTY_TYP'
AND HLB6.LOOKUP_CODE(+) = RB1.ACTY_TYP_CD
AND F.EFFECTIVE_DATE BETWEEN NVL(HLB6.START_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND NVL(HLB6.END_DATE_ACTIVE
, F.EFFECTIVE_DATE)
AND HLB6.ENABLED_FLAG(+) = 'Y'
AND R1.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND RB1.ENRT_BNFT_ID(+) = B.ENRT_BNFT_ID
AND R1.DSPLY_ON_ENRT_FLAG(+)='Y'
AND R1.ACTY_TYP_CD(+) <> 'SSDSPLY'
AND R1.SPCL_RT_ENRT_RT_ID IS NULL
AND RB1.DSPLY_ON_ENRT_FLAG(+)='Y'
AND RB1.ACTY_TYP_CD(+) <> 'SSDSPLY'
AND RB1.SPCL_RT_ENRT_RT_ID IS NULL
AND F.SESSION_ID = USERENV('SESSIONID')
AND P.INVK_FLX_CR_PL_FLAG(+) = 'N'
AND P.IMPTD_INCM_CALC_CD IS NULL
AND P.PL_STAT_CD = 'A'
AND EPE.ELCTBL_FLAG = 'Y'
AND B.MX_WO_CTFN_FLAG(+) = 'N'
AND PIL.PER_IN_LER_ID=EPE.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=EPE.BUSINESS_GROUP_ID
AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')
AND LER.LER_ID = PIL.LER_ID
AND LER.TYP_CD NOT IN ('COMP'
, 'IREC'
, 'GSP')
AND F.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE ORDER BY 1
, 2
, 3
, 4

Columns