BEN_DIAG_ENP_LEN_V

Details

• Schema: FUSION

• Object owner: BEN

• Object type: VIEW

Columns

Name
PGM PL LER ENRT_PERD_STRT_DT_CD ENRT_PERD_STRT_DT_RL ENRT_PERD_END_DT_CD ENRT_PERD_END_DT_RL ENRT_CVG_STRT_DT_CD ENRT_CVG_STRT_DT_RL ENRT_CVG_END_DT_CD ENRT_CVG_END_DT_RL RT_STRT_DT_CD RT_STRT_DT_RL RT_END_DT_CD RT_END_DT_RL LER_ID

Query

SQL_Statement

SELECT X.PGM, X.PL, X.LER LER ,HL1.MEANING ENRT_PERD_STRT_DT_CD ,FF1.FORMULA_NAME ENRT_PERD_STRT_DT_RL ,HL2.MEANING ENRT_PERD_END_DT_CD ,FF2.FORMULA_NAME ENRT_PERD_END_DT_RL ,HL3.MEANING ENRT_CVG_STRT_DT_CD ,FF3.FORMULA_NAME ENRT_CVG_STRT_DT_RL ,HL4.MEANING ENRT_CVG_END_DT_CD ,FF4.FORMULA_NAME ENRT_CVG_END_DT_RL ,HL5.MEANING RT_STRT_DT_CD ,FF5.FORMULA_NAME RT_STRT_DT_RL ,HL6.MEANING RT_END_DT_CD ,FF6.FORMULA_NAME RT_END_DT_RL ,X.LER_ID FROM ( SELECT LEN.LEE_RSN_ID, PGM.PGM_ID, -1 PLIP_ID, -1 PL_ID, LER.LER_ID, PGM.NAME PGM, NULL PL, LER.NAME LER ,LEN.ENRT_PERD_STRT_DT_CD ,LEN.ENRT_PERD_STRT_DT_RL ,LEN.ENRT_PERD_END_DT_CD ,LEN.ENRT_PERD_END_DT_RL ,LEN.ENRT_CVG_STRT_DT_CD ,LEN.ENRT_CVG_STRT_DT_RL ,LEN.ENRT_CVG_END_DT_CD ,LEN.ENRT_CVG_END_DT_RL ,LEN.RT_STRT_DT_CD ,LEN.RT_STRT_DT_RL ,LEN.RT_END_DT_CD ,LEN.RT_END_DT_RL FROM BEN_LEE_RSN_F LEN ,BEN_PGM_F PGM ,BEN_POPL_ENRT_TYP_CYCL PET ,BEN_LER_F LER WHERE PET.PGM_ID = PGM.PGM_ID AND LEN.LER_ID = LER.LER_ID AND PET.POPL_ENRT_TYP_CYCL_ID = LEN.POPL_ENRT_TYP_CYCL_ID AND PET.ENRT_TYP_CYCL_CD = 'L' 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 LEN.EFFECTIVE_START_DATE AND LEN.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 ENP.ENRT_PERD_ID, PGM.PGM_ID, -1 PLIP_ID, -1 PL_ID, ENP.YR_PERD_ID LER_ID, PGM.NAME PGM, NULL PL, DECODE(PET.ENRT_TYP_CYCL_CD,'O','Open','A','Admin','UO','Unrestricted Open') || ' (' || ENP.STRT_DT || ' To ' || ENP.END_DT || ')' LER ,NULL ENRT_PERD_STRT_DT_CD ,NULL ENRT_PERD_STRT_DT_RL ,NULL ENRT_PERD_END_DT_CD ,NULL ENRT_PERD_END_DT_RL ,ENP.ENRT_CVG_STRT_DT_CD ,ENP.ENRT_CVG_STRT_DT_RL ,ENP.ENRT_CVG_END_DT_CD ,ENP.ENRT_CVG_END_DT_RL ,ENP.RT_STRT_DT_CD ,ENP.RT_STRT_DT_RL ,ENP.RT_END_DT_CD ,ENP.RT_END_DT_RL FROM BEN_ENRT_PERD ENP ,BEN_PGM_F PGM ,BEN_POPL_ENRT_TYP_CYCL PET WHERE PET.PGM_ID = PGM.PGM_ID AND PET.POPL_ENRT_TYP_CYCL_ID = ENP.POPL_ENRT_TYP_CYCL_ID AND PET.ENRT_TYP_CYCL_CD IN ('O','A','UO') 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 UNION ALL SELECT ERP.ENRT_PERD_FOR_PL_ID, PGM.PGM_ID, -1 PLIP_ID, PL.PL_ID, LER.LER_ID, PGM.NAME PGM, PL.NAME PL, LER.NAME LER ,NULL ENRT_PERD_STRT_DT_CD ,NULL ENRT_PERD_STRT_DT_RL ,NULL ENRT_PERD_END_DT_CD ,NULL ENRT_PERD_END_DT_RL ,ERP.ENRT_CVG_STRT_DT_CD ,ERP.ENRT_CVG_STRT_DT_RL ,ERP.ENRT_CVG_END_DT_CD ,ERP.ENRT_CVG_END_DT_RL ,ERP.RT_STRT_DT_CD ,ERP.RT_STRT_DT_RL ,ERP.RT_END_DT_CD ,ERP.RT_END_DT_RL FROM BEN_LEE_RSN_F LEN ,BEN_ENRT_PERD_FOR_PL_F ERP ,BEN_PGM_F PGM ,BEN_POPL_ENRT_TYP_CYCL PET ,BEN_PL_F PL ,BEN_LER_F LER WHERE PET.PGM_ID = PGM.PGM_ID AND LEN.LEE_RSN_ID = ERP.LEE_RSN_ID AND ERP.PL_ID = PL.PL_ID AND LEN.LER_ID = LER.LER_ID AND PET.POPL_ENRT_TYP_CYCL_ID = LEN.POPL_ENRT_TYP_CYCL_ID AND PET.ENRT_TYP_CYCL_CD = 'L' 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 LEN.EFFECTIVE_START_DATE AND LEN.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 ERP.EFFECTIVE_START_DATE AND ERP.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 ERP.ENRT_PERD_FOR_PL_ID, PGM.PGM_ID, -1 PLIP_ID, PL.PL_ID, ENP.YR_PERD_ID LER_ID, PGM.NAME PGM, PL.NAME PL, DECODE(PET.ENRT_TYP_CYCL_CD,'O','Open','A','Administrative') || ' ( ' || ENP.STRT_DT || ' To ' || ENP.END_DT || ' ) ' LER ,NULL ENRT_PERD_STRT_DT_CD ,NULL ENRT_PERD_STRT_DT_RL ,NULL ENRT_PERD_END_DT_CD ,NULL ENRT_PERD_END_DT_RL ,ERP.ENRT_CVG_STRT_DT_CD ,ERP.ENRT_CVG_STRT_DT_RL ,ERP.ENRT_CVG_END_DT_CD ,ERP.ENRT_CVG_END_DT_RL ,ERP.RT_STRT_DT_CD ,ERP.RT_STRT_DT_RL ,ERP.RT_END_DT_CD ,ERP.RT_END_DT_RL FROM BEN_ENRT_PERD ENP ,BEN_ENRT_PERD_FOR_PL_F ERP ,BEN_PGM_F PGM ,BEN_PL_F PL ,BEN_POPL_ENRT_TYP_CYCL PET WHERE PET.PGM_ID = PGM.PGM_ID AND ENP.ENRT_PERD_ID = ERP.ENRT_PERD_ID AND ERP.PL_ID = PL.PL_ID AND PET.POPL_ENRT_TYP_CYCL_ID = ENP.POPL_ENRT_TYP_CYCL_ID AND PET.ENRT_TYP_CYCL_CD IN ('O','A') 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 ERP.EFFECTIVE_START_DATE AND ERP.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 LEN.LEE_RSN_ID, -1 PGM_ID, -1 PLIP_ID, PL.PL_ID, LER.LER_ID, NULL PGM, PL.NAME PL, LER.NAME LER ,LEN.ENRT_PERD_STRT_DT_CD ,LEN.ENRT_PERD_STRT_DT_RL ,LEN.ENRT_PERD_END_DT_CD ,LEN.ENRT_PERD_END_DT_RL ,LEN.ENRT_CVG_STRT_DT_CD ,LEN.ENRT_CVG_STRT_DT_RL ,LEN.ENRT_CVG_END_DT_CD ,LEN.ENRT_CVG_END_DT_RL ,LEN.RT_STRT_DT_CD ,LEN.RT_STRT_DT_RL ,LEN.RT_END_DT_CD ,LEN.RT_END_DT_RL FROM BEN_LEE_RSN_F LEN ,BEN_PL_F PL ,BEN_POPL_ENRT_TYP_CYCL PET ,BEN_LER_F LER WHERE PET.PGM_ID = PL.PL_ID AND LEN.LER_ID = LER.LER_ID AND PET.POPL_ENRT_TYP_CYCL_ID = LEN.POPL_ENRT_TYP_CYCL_ID AND PET.ENRT_TYP_CYCL_CD = 'L' 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 LEN.EFFECTIVE_START_DATE AND LEN.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 ENP.ENRT_PERD_ID, -1 PGM_ID, -1 PLIP_ID, PL.PL_ID, ENP.YR_PERD_ID LER_ID, NULL PGM, PL.NAME PL, DECODE(PET.ENRT_TYP_CYCL_CD,'O','Open','A','Administrative') || ' ( ' || ENP.STRT_DT || ' To ' || ENP.END_DT || ' ) ' LER ,NULL ENRT_PERD_STRT_DT_CD ,NULL ENRT_PERD_STRT_DT_RL ,NULL ENRT_PERD_END_DT_CD ,NULL ENRT_PERD_END_DT_RL ,ENP.ENRT_CVG_STRT_DT_CD ,ENP.ENRT_CVG_STRT_DT_RL ,ENP.ENRT_CVG_END_DT_CD ,ENP.ENRT_CVG_END_DT_RL ,ENP.RT_STRT_DT_CD ,ENP.RT_STRT_DT_RL ,ENP.RT_END_DT_CD ,ENP.RT_END_DT_RL FROM BEN_ENRT_PERD ENP ,BEN_PL_F PL ,BEN_POPL_ENRT_TYP_CYCL PET WHERE PET.PGM_ID = PL.PL_ID AND PET.POPL_ENRT_TYP_CYCL_ID = ENP.POPL_ENRT_TYP_CYCL_ID AND PET.ENRT_TYP_CYCL_CD IN ('O','A') 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 ) X ,HR_LOOKUPS HL1 ,HR_LOOKUPS HL2 ,HR_LOOKUPS HL3 ,HR_LOOKUPS HL4 ,HR_LOOKUPS HL5 ,HR_LOOKUPS HL6 ,FF_FORMULAS_F FF1 ,FF_FORMULAS_F FF2 ,FF_FORMULAS_F FF3 ,FF_FORMULAS_F FF4 ,FF_FORMULAS_F FF5 ,FF_FORMULAS_F FF6 WHERE HL1.LOOKUP_CODE(+) = X.ENRT_PERD_STRT_DT_CD AND HL1.LOOKUP_TYPE(+) = 'BEN_ENRT_PERD_STRT' AND HL2.LOOKUP_CODE(+) = X.ENRT_PERD_END_DT_CD AND HL2.LOOKUP_TYPE(+) = 'BEN_ENRT_PERD_END' AND HL3.LOOKUP_CODE(+) = X.ENRT_CVG_STRT_DT_CD AND HL3.LOOKUP_TYPE(+) = 'BEN_ENRT_CVG_STRT' AND HL4.LOOKUP_CODE(+) = X.ENRT_CVG_END_DT_CD AND HL4.LOOKUP_TYPE(+) = 'BEN_ENRT_CVG_END' AND HL5.LOOKUP_CODE(+) = X.RT_STRT_DT_CD AND HL5.LOOKUP_TYPE(+) = 'BEN_RT_STRT' AND HL6.LOOKUP_CODE(+) = X.RT_END_DT_CD AND HL6.LOOKUP_TYPE(+) = 'BEN_RT_END' AND FF1.FORMULA_ID(+) = X.ENRT_PERD_STRT_DT_RL AND FF2.FORMULA_ID(+) = X.ENRT_PERD_END_DT_RL AND FF3.FORMULA_ID(+) = X.ENRT_CVG_STRT_DT_RL AND FF4.FORMULA_ID(+) = X.ENRT_CVG_END_DT_RL AND FF5.FORMULA_ID(+) = X.RT_STRT_DT_RL AND FF6.FORMULA_ID(+) = X.RT_END_DT_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 TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF3.EFFECTIVE_START_DATE(+) AND FF3.EFFECTIVE_END_DATE(+) AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF4.EFFECTIVE_START_DATE(+) AND FF4.EFFECTIVE_END_DATE(+) AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF5.EFFECTIVE_START_DATE(+) AND FF5.EFFECTIVE_END_DATE(+) AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF6.EFFECTIVE_START_DATE(+) AND FF6.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 ) ) ) )