BEN_DIAG_LGE_LRE

Details

Schema: FUSION
Object owner: BEN
Object type: VIEW

Columns

Name
PGM PLTYP PL OPT LER ESD EED AUTO_ENRT_MTHD_RL CRNT_ENRT_PRCLDS_CHG_FLAG ENRT_CD ENRT_MTHD_CD ENRT_RL STL_ELIG_CANT_CHG_FLAG

Name

PGM

PLTYP

PL

OPT

LER

ESD

EED

AUTO_ENRT_MTHD_RL

CRNT_ENRT_PRCLDS_CHG_FLAG

ENRT_CD

ENRT_MTHD_CD

ENRT_RL

STL_ELIG_CANT_CHG_FLAG

Query

SQL_Statement
SELECT X.PGM, X.PLTYP, X.PL, X.OPT, X.LER ,TRUNC(X.EFFECTIVE_START_DATE) ESD ,TRUNC(X.EFFECTIVE_END_DATE) EED ,FF1.FORMULA_NAME AUTO_ENRT_MTHD_RL ,HL1.MEANING CRNT_ENRT_PRCLDS_CHG_FLAG ,HL2.MEANING ENRT_CD ,HL3.MEANING ENRT_MTHD_CD ,FF2.FORMULA_NAME ENRT_RL ,HL4.MEANING STL_ELIG_CANT_CHG_FLAG FROM ( SELECT LGE.LER_CHG_PGM_ENRT_ID, LGE.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID, LER.LER_ID ,PGM.NAME PGM, NULL PLTYP, NULL PL, NULL OPT, LER.NAME LER ,LGE.EFFECTIVE_START_DATE ,LGE.EFFECTIVE_END_DATE ,LGE.AUTO_ENRT_MTHD_RL ,LGE.CRNT_ENRT_PRCLDS_CHG_FLAG ,LGE.ENRT_CD ,LGE.ENRT_MTHD_CD ,LGE.ENRT_RL ,LGE.STL_ELIG_CANT_CHG_FLAG FROM BEN_LER_CHG_PGM_ENRT_F LGE ,BEN_PGM_F PGM ,BEN_LER_F LER WHERE LGE.PGM_ID = PGM.PGM_ID AND LGE.LER_ID = LER.LER_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LGE.EFFECTIVE_START_DATE AND LGE.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PGM.EFFECTIVE_START_DATE AND PGM.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE UNION ALL SELECT LCT.LER_CHG_PTIP_ENRT_ID, PGM.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID, LER.LER_ID ,PGM.NAME PGM, PTP.NAME PLTYP, NULL PL, NULL OPT, LER.NAME LER ,LCT.EFFECTIVE_START_DATE ,LCT.EFFECTIVE_END_DATE ,NULL AUTO_ENRT_MTHD_RL ,LCT.CRNT_ENRT_PRCLDS_CHG_FLAG ,LCT.ENRT_CD ,LCT.ENRT_MTHD_CD ,TO_NUMBER(LCT.ENRT_RL) ENRT_RL ,LCT.STL_ELIG_CANT_CHG_FLAG FROM BEN_LER_CHG_PTIP_ENRT_F LCT ,BEN_PGM_F PGM ,BEN_LER_F LER ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP WHERE CTP.PTIP_ID = LCT.PTIP_ID AND CTP.PL_TYP_ID = PTP.PL_TYP_ID AND PGM.PGM_ID = CTP.PGM_ID AND LCT.LER_ID = LER.LER_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LCT.EFFECTIVE_START_DATE AND LCT.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PGM.EFFECTIVE_START_DATE AND PGM.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN CTP.EFFECTIVE_START_DATE AND CTP.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE UNION ALL SELECT LPR.LER_CHG_PLIP_ENRT_ID, PGM.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID, LER.LER_ID ,PGM.NAME PGM, PTP.NAME PLTYP, NULL PL, NULL OPT, LER.NAME LER ,LPR.EFFECTIVE_START_DATE ,LPR.EFFECTIVE_END_DATE ,LPR.AUTO_ENRT_MTHD_RL ,LPR.CRNT_ENRT_PRCLDS_CHG_FLAG ,LPR.ENRT_CD ,LPR.ENRT_MTHD_CD ,LPR.ENRT_RL ,LPR.STL_ELIG_CANT_CHG_FLAG FROM BEN_LER_CHG_PLIP_ENRT_F LPR ,BEN_PGM_F PGM ,BEN_LER_F LER ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP ,BEN_PLIP_F CPP ,BEN_PL_F PL WHERE CPP.PLIP_ID = LPR.PLIP_ID AND PGM.PGM_ID = CPP.PGM_ID AND CTP.PGM_ID = PGM.PGM_ID AND CTP.PL_TYP_ID = PTP.PL_TYP_ID AND PTP.PL_TYP_ID = PL.PL_TYP_ID AND PL.PL_ID = CPP.PL_ID AND LPR.LER_ID = LER.LER_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LPR.EFFECTIVE_START_DATE AND LPR.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PGM.EFFECTIVE_START_DATE AND PGM.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN CTP.EFFECTIVE_START_DATE AND CTP.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN CPP.EFFECTIVE_START_DATE AND CPP.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE UNION ALL SELECT LPE.LER_CHG_PL_NIP_ENRT_ID, -1 PGM_ID, -1 PTIP_ID, -1 PLIP_ID, PL.PL_ID, -1 OIPL_ID, LER.LER_ID ,NULL PGM, PTP.NAME PLTYP, PL.NAME PL, NULL OPT, LER.NAME LER ,LPE.EFFECTIVE_START_DATE ,LPE.EFFECTIVE_END_DATE ,LPE.AUTO_ENRT_MTHD_RL ,LPE.CRNT_ENRT_PRCLDS_CHG_FLAG ,LPE.ENRT_CD ,LPE.ENRT_MTHD_CD ,LPE.ENRT_RL ,LPE.STL_ELIG_CANT_CHG_FLAG FROM BEN_LER_CHG_PL_NIP_ENRT_F LPE ,BEN_PL_F PL ,BEN_PL_TYP_F PTP ,BEN_LER_F LER WHERE PL.PL_ID = LPE.PL_ID AND LPE.LER_ID = LER.LER_ID AND PL.PL_TYP_ID = PTP.PL_TYP_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LPE.EFFECTIVE_START_DATE AND LPE.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE UNION ALL SELECT LOP.LER_CHG_OIPL_ENRT_ID, -1 PGM_ID, -1 PTIP_ID, -1 PLIP_ID, PL.PL_ID, COP.OIPL_ID, LER.LER_ID ,NULL PGM, PTP.NAME PLTYP, PL.NAME PL, OPT.NAME OPT, LER.NAME LER ,LOP.EFFECTIVE_START_DATE ,LOP.EFFECTIVE_END_DATE ,LOP.AUTO_ENRT_MTHD_RL ,LOP.CRNT_ENRT_PRCLDS_CHG_FLAG ,LOP.ENRT_CD ,NULL ENRT_MTHD_CD ,LOP.ENRT_RL ,LOP.STL_ELIG_CANT_CHG_FLAG FROM BEN_LER_CHG_OIPL_ENRT_F LOP ,BEN_PL_F PL ,BEN_PL_TYP_F PTP ,BEN_LER_F LER ,BEN_OIPL_F COP ,BEN_OPT_F OPT WHERE COP.OIPL_ID = LOP.OIPL_ID AND COP.PL_ID = PL.PL_ID AND COP.OPT_ID = OPT.OPT_ID AND LOP.LER_ID = LER.LER_ID AND PL.PL_TYP_ID = PTP.PL_TYP_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LOP.EFFECTIVE_START_DATE AND LOP.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN COP.EFFECTIVE_START_DATE AND COP.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN OPT.EFFECTIVE_START_DATE AND OPT.EFFECTIVE_END_DATE ) X ,HR_LOOKUPS HL1 ,HR_LOOKUPS HL2 ,HR_LOOKUPS HL3 ,HR_LOOKUPS HL4 ,FF_FORMULAS_F FF1 ,FF_FORMULAS_F FF2 WHERE HL1.LOOKUP_CODE(+) = X.CRNT_ENRT_PRCLDS_CHG_FLAG AND HL1.LOOKUP_TYPE(+) = 'YES_NO' AND HL2.LOOKUP_CODE(+) = X.ENRT_CD AND HL2.LOOKUP_TYPE(+) = 'BEN_ENRT' AND HL3.LOOKUP_CODE(+) = X.ENRT_MTHD_CD AND HL3.LOOKUP_TYPE(+) = 'BEN_ENRT_MTHD' AND HL4.LOOKUP_CODE(+) = X.STL_ELIG_CANT_CHG_FLAG AND HL4.LOOKUP_TYPE(+) = 'YES_NO' AND FF1.FORMULA_ID(+) = X.AUTO_ENRT_MTHD_RL AND FF2.FORMULA_ID(+) = X.ENRT_RL AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF1.EFFECTIVE_START_DATE(+) AND FF1.EFFECTIVE_END_DATE(+) AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF2.EFFECTIVE_START_DATE(+) AND FF2.EFFECTIVE_END_DATE(+) AND ((X.PGM_ID IN (select pgm_id from ben_pgm_f where name = FND_DIAG_SDK_API.GET_VARCHAR2_INPUT_PARAM('pgmName') AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ) ) OR (X.PGM_ID = -1 AND X.PL_ID IN (SELECT PL_ID FROM BEN_PLIP_F WHERE PGM_ID IN (select pgm_id from ben_pgm_f where name = FND_DIAG_SDK_API.GET_VARCHAR2_INPUT_PARAM('pgmName') AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ) ) ) ) AND (X.AUTO_ENRT_MTHD_RL IS NOT NULL OR X.CRNT_ENRT_PRCLDS_CHG_FLAG IS NOT NULL OR X.ENRT_CD IS NOT NULL OR X.ENRT_MTHD_CD IS NOT NULL OR X.ENRT_RL IS NOT NULL OR X.STL_ELIG_CANT_CHG_FLAG IS NOT NULL)

SQL_Statement

SELECT X.PGM, X.PLTYP, X.PL, X.OPT, X.LER

,TRUNC(X.EFFECTIVE_START_DATE) ESD

,TRUNC(X.EFFECTIVE_END_DATE) EED

,FF1.FORMULA_NAME AUTO_ENRT_MTHD_RL

,HL1.MEANING CRNT_ENRT_PRCLDS_CHG_FLAG

,HL2.MEANING ENRT_CD

,HL3.MEANING ENRT_MTHD_CD

,FF2.FORMULA_NAME ENRT_RL

,HL4.MEANING STL_ELIG_CANT_CHG_FLAG

FROM (

SELECT LGE.LER_CHG_PGM_ENRT_ID, LGE.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID, LER.LER_ID

,PGM.NAME PGM, NULL PLTYP, NULL PL, NULL OPT, LER.NAME LER

,LGE.EFFECTIVE_START_DATE

,LGE.EFFECTIVE_END_DATE

,LGE.AUTO_ENRT_MTHD_RL

,LGE.CRNT_ENRT_PRCLDS_CHG_FLAG

,LGE.ENRT_CD

,LGE.ENRT_MTHD_CD

,LGE.ENRT_RL

,LGE.STL_ELIG_CANT_CHG_FLAG

FROM BEN_LER_CHG_PGM_ENRT_F LGE

,BEN_PGM_F PGM

,BEN_LER_F LER

WHERE LGE.PGM_ID = PGM.PGM_ID

AND LGE.LER_ID = LER.LER_ID

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LGE.EFFECTIVE_START_DATE AND LGE.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PGM.EFFECTIVE_START_DATE AND PGM.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE

UNION ALL

SELECT LCT.LER_CHG_PTIP_ENRT_ID, PGM.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID, LER.LER_ID

,PGM.NAME PGM, PTP.NAME PLTYP, NULL PL, NULL OPT, LER.NAME LER

,LCT.EFFECTIVE_START_DATE

,LCT.EFFECTIVE_END_DATE

,NULL AUTO_ENRT_MTHD_RL

,LCT.CRNT_ENRT_PRCLDS_CHG_FLAG

,LCT.ENRT_CD

,LCT.ENRT_MTHD_CD

,TO_NUMBER(LCT.ENRT_RL) ENRT_RL

,LCT.STL_ELIG_CANT_CHG_FLAG

FROM BEN_LER_CHG_PTIP_ENRT_F LCT

,BEN_PGM_F PGM

,BEN_LER_F LER

,BEN_PTIP_F CTP

,BEN_PL_TYP_F PTP

WHERE CTP.PTIP_ID = LCT.PTIP_ID

AND CTP.PL_TYP_ID = PTP.PL_TYP_ID

AND PGM.PGM_ID = CTP.PGM_ID

AND LCT.LER_ID = LER.LER_ID

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LCT.EFFECTIVE_START_DATE AND LCT.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PGM.EFFECTIVE_START_DATE AND PGM.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN CTP.EFFECTIVE_START_DATE AND CTP.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE

UNION ALL

SELECT LPR.LER_CHG_PLIP_ENRT_ID, PGM.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID, LER.LER_ID

,PGM.NAME PGM, PTP.NAME PLTYP, NULL PL, NULL OPT, LER.NAME LER

,LPR.EFFECTIVE_START_DATE

,LPR.EFFECTIVE_END_DATE

,LPR.AUTO_ENRT_MTHD_RL

,LPR.CRNT_ENRT_PRCLDS_CHG_FLAG

,LPR.ENRT_CD

,LPR.ENRT_MTHD_CD

,LPR.ENRT_RL

,LPR.STL_ELIG_CANT_CHG_FLAG

FROM BEN_LER_CHG_PLIP_ENRT_F LPR

,BEN_PGM_F PGM

,BEN_LER_F LER

,BEN_PTIP_F CTP

,BEN_PL_TYP_F PTP

,BEN_PLIP_F CPP

,BEN_PL_F PL

WHERE CPP.PLIP_ID = LPR.PLIP_ID

AND PGM.PGM_ID = CPP.PGM_ID

AND CTP.PGM_ID = PGM.PGM_ID

AND CTP.PL_TYP_ID = PTP.PL_TYP_ID

AND PTP.PL_TYP_ID = PL.PL_TYP_ID

AND PL.PL_ID = CPP.PL_ID

AND LPR.LER_ID = LER.LER_ID

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LPR.EFFECTIVE_START_DATE AND LPR.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PGM.EFFECTIVE_START_DATE AND PGM.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN CTP.EFFECTIVE_START_DATE AND CTP.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN CPP.EFFECTIVE_START_DATE AND CPP.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE

UNION ALL

SELECT LPE.LER_CHG_PL_NIP_ENRT_ID, -1 PGM_ID, -1 PTIP_ID, -1 PLIP_ID, PL.PL_ID, -1 OIPL_ID, LER.LER_ID

,NULL PGM, PTP.NAME PLTYP, PL.NAME PL, NULL OPT, LER.NAME LER

,LPE.EFFECTIVE_START_DATE

,LPE.EFFECTIVE_END_DATE

,LPE.AUTO_ENRT_MTHD_RL

,LPE.CRNT_ENRT_PRCLDS_CHG_FLAG

,LPE.ENRT_CD

,LPE.ENRT_MTHD_CD

,LPE.ENRT_RL

,LPE.STL_ELIG_CANT_CHG_FLAG

FROM BEN_LER_CHG_PL_NIP_ENRT_F LPE

,BEN_PL_F PL

,BEN_PL_TYP_F PTP

,BEN_LER_F LER

WHERE PL.PL_ID = LPE.PL_ID

AND LPE.LER_ID = LER.LER_ID

AND PL.PL_TYP_ID = PTP.PL_TYP_ID

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LPE.EFFECTIVE_START_DATE AND LPE.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE

UNION ALL

SELECT LOP.LER_CHG_OIPL_ENRT_ID, -1 PGM_ID, -1 PTIP_ID, -1 PLIP_ID, PL.PL_ID, COP.OIPL_ID, LER.LER_ID

,NULL PGM, PTP.NAME PLTYP, PL.NAME PL, OPT.NAME OPT, LER.NAME LER

,LOP.EFFECTIVE_START_DATE

,LOP.EFFECTIVE_END_DATE

,LOP.AUTO_ENRT_MTHD_RL

,LOP.CRNT_ENRT_PRCLDS_CHG_FLAG

,LOP.ENRT_CD

,NULL ENRT_MTHD_CD

,LOP.ENRT_RL

,LOP.STL_ELIG_CANT_CHG_FLAG

FROM BEN_LER_CHG_OIPL_ENRT_F LOP

,BEN_PL_F PL

,BEN_PL_TYP_F PTP

,BEN_LER_F LER

,BEN_OIPL_F COP

,BEN_OPT_F OPT

WHERE COP.OIPL_ID = LOP.OIPL_ID

AND COP.PL_ID = PL.PL_ID

AND COP.OPT_ID = OPT.OPT_ID

AND LOP.LER_ID = LER.LER_ID

AND PL.PL_TYP_ID = PTP.PL_TYP_ID

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LOP.EFFECTIVE_START_DATE AND LOP.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN COP.EFFECTIVE_START_DATE AND COP.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN OPT.EFFECTIVE_START_DATE AND OPT.EFFECTIVE_END_DATE

) X

,HR_LOOKUPS HL1

,HR_LOOKUPS HL2

,HR_LOOKUPS HL3

,HR_LOOKUPS HL4

,FF_FORMULAS_F FF1

,FF_FORMULAS_F FF2

WHERE HL1.LOOKUP_CODE(+) = X.CRNT_ENRT_PRCLDS_CHG_FLAG

AND HL1.LOOKUP_TYPE(+) = 'YES_NO'

AND HL2.LOOKUP_CODE(+) = X.ENRT_CD

AND HL2.LOOKUP_TYPE(+) = 'BEN_ENRT'

AND HL3.LOOKUP_CODE(+) = X.ENRT_MTHD_CD

AND HL3.LOOKUP_TYPE(+) = 'BEN_ENRT_MTHD'

AND HL4.LOOKUP_CODE(+) = X.STL_ELIG_CANT_CHG_FLAG

AND HL4.LOOKUP_TYPE(+) = 'YES_NO'

AND FF1.FORMULA_ID(+) = X.AUTO_ENRT_MTHD_RL

AND FF2.FORMULA_ID(+) = X.ENRT_RL

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF1.EFFECTIVE_START_DATE(+) AND FF1.EFFECTIVE_END_DATE(+)

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF2.EFFECTIVE_START_DATE(+) AND FF2.EFFECTIVE_END_DATE(+)

AND ((X.PGM_ID IN (select pgm_id

from ben_pgm_f

where name = FND_DIAG_SDK_API.GET_VARCHAR2_INPUT_PARAM('pgmName')

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

)

OR (X.PGM_ID = -1 AND X.PL_ID IN (SELECT PL_ID FROM BEN_PLIP_F WHERE PGM_ID IN (select pgm_id

from ben_pgm_f

where name = FND_DIAG_SDK_API.GET_VARCHAR2_INPUT_PARAM('pgmName')

AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

)

AND (X.AUTO_ENRT_MTHD_RL IS NOT NULL

OR X.CRNT_ENRT_PRCLDS_CHG_FLAG IS NOT NULL

OR X.ENRT_CD IS NOT NULL

OR X.ENRT_MTHD_CD IS NOT NULL

OR X.ENRT_RL IS NOT NULL

OR X.STL_ELIG_CANT_CHG_FLAG IS NOT NULL)