VIEW: APPS.BEN_ENRT_LOV_INFO_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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