BEN_DIAG_PEO_V

Details

• Schema: FUSION

• Object owner: BEN

• Object type: VIEW

Columns

Name
PGM PLTYP PL OPT LER ESD EED PRTN_EFF_STRT_DT_CD PRTN_EFF_END_DT_CD PRTN_EFF_STRT_DT_RL PRTN_EFF_END_DT_RL WAIT_PERD_VAL WAIT_PERD_UOM ELIG_INELIG_CD FMLY_MMBR

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 ,HL1.MEANING PRTN_EFF_STRT_DT_CD ,HL2.MEANING PRTN_EFF_END_DT_CD ,FF1.FORMULA_NAME PRTN_EFF_STRT_DT_RL ,FF2.FORMULA_NAME PRTN_EFF_END_DT_RL ,X.WAIT_PERD_VAL ,X.WAIT_PERD_UOM ,HL3.MEANING ELIG_INELIG_CD ,X.FMLY_MMBR FROM ( SELECT PEO.ELIG_TO_PRTE_RSN_ID, PGM.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 ,PEO.EFFECTIVE_START_DATE ,PEO.EFFECTIVE_END_DATE ,PEO.PRTN_EFF_STRT_DT_CD ,PEO.PRTN_EFF_END_DT_CD ,PEO.PRTN_EFF_STRT_DT_RL ,PEO.PRTN_EFF_END_DT_RL ,PEO.WAIT_PERD_VAL ,PEO.WAIT_PERD_UOM ,PEO.ELIG_INELIG_CD ,decode(PEO.VRFY_FMLY_MMBR_CD,NULL,decode(PEO.VRFY_FMLY_MMBR_RL,NULL,'N','Y'),'Y') FMLY_MMBR FROM BEN_ELIG_TO_PRTE_RSN_F PEO ,BEN_PGM_F PGM ,BEN_LER_F LER WHERE PEO.PGM_ID = PGM.PGM_ID AND PEO.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 PEO.EFFECTIVE_START_DATE AND PEO.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 PEO.ELIG_TO_PRTE_RSN_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 ,PEO.EFFECTIVE_START_DATE ,PEO.EFFECTIVE_END_DATE ,PEO.PRTN_EFF_STRT_DT_CD ,PEO.PRTN_EFF_END_DT_CD ,PEO.PRTN_EFF_STRT_DT_RL ,PEO.PRTN_EFF_END_DT_RL ,PEO.WAIT_PERD_VAL ,PEO.WAIT_PERD_UOM ,PEO.ELIG_INELIG_CD ,decode(PEO.VRFY_FMLY_MMBR_CD,NULL,decode(PEO.VRFY_FMLY_MMBR_RL,NULL,'N','Y'),'Y') FMLY_MMBR FROM BEN_ELIG_TO_PRTE_RSN_F PEO ,BEN_PGM_F PGM ,BEN_LER_F LER ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP WHERE CTP.PTIP_ID = PEO.PTIP_ID AND CTP.PL_TYP_ID = PTP.PL_TYP_ID AND PGM.PGM_ID = CTP.PGM_ID AND PEO.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 PEO.EFFECTIVE_START_DATE AND PEO.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 PEO.ELIG_TO_PRTE_RSN_ID, PGM.PGM_ID, CTP.PTIP_ID, CPP.PLIP_ID, PL.PL_ID, -1 OIPL_ID, LER.LER_ID ,PGM.NAME PGM, PTP.NAME PLTYP, PL.NAME PL, NULL OPT, LER.NAME LER ,PEO.EFFECTIVE_START_DATE ,PEO.EFFECTIVE_END_DATE ,PEO.PRTN_EFF_STRT_DT_CD ,PEO.PRTN_EFF_END_DT_CD ,PEO.PRTN_EFF_STRT_DT_RL ,PEO.PRTN_EFF_END_DT_RL ,PEO.WAIT_PERD_VAL ,PEO.WAIT_PERD_UOM ,PEO.ELIG_INELIG_CD ,decode(PEO.VRFY_FMLY_MMBR_CD,NULL,decode(PEO.VRFY_FMLY_MMBR_RL,NULL,'N','Y'),'Y') FMLY_MMBR FROM BEN_ELIG_TO_PRTE_RSN_F PEO ,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 = PEO.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 PEO.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 PEO.EFFECTIVE_START_DATE AND PEO.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 PEO.ELIG_TO_PRTE_RSN_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 ,PEO.EFFECTIVE_START_DATE ,PEO.EFFECTIVE_END_DATE ,PEO.PRTN_EFF_STRT_DT_CD ,PEO.PRTN_EFF_END_DT_CD ,PEO.PRTN_EFF_STRT_DT_RL ,PEO.PRTN_EFF_END_DT_RL ,PEO.WAIT_PERD_VAL ,PEO.WAIT_PERD_UOM ,PEO.ELIG_INELIG_CD ,decode(PEO.VRFY_FMLY_MMBR_CD,NULL,decode(PEO.VRFY_FMLY_MMBR_RL,NULL,'N','Y'),'Y') FMLY_MMBR FROM BEN_ELIG_TO_PRTE_RSN_F PEO ,BEN_PL_F PL ,BEN_PL_TYP_F PTP ,BEN_LER_F LER WHERE PL.PL_ID = PEO.PL_ID AND PEO.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 PEO.EFFECTIVE_START_DATE AND PEO.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 PEO.ELIG_TO_PRTE_RSN_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 ,PEO.EFFECTIVE_START_DATE ,PEO.EFFECTIVE_END_DATE ,PEO.PRTN_EFF_STRT_DT_CD ,PEO.PRTN_EFF_END_DT_CD ,PEO.PRTN_EFF_STRT_DT_RL ,PEO.PRTN_EFF_END_DT_RL ,PEO.WAIT_PERD_VAL ,PEO.WAIT_PERD_UOM ,PEO.ELIG_INELIG_CD ,decode(PEO.VRFY_FMLY_MMBR_CD,NULL,decode(PEO.VRFY_FMLY_MMBR_RL,NULL,'N','Y'),'Y') FMLY_MMBR FROM BEN_ELIG_TO_PRTE_RSN_F PEO ,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 = PEO.OIPL_ID AND COP.PL_ID = PL.PL_ID AND COP.OPT_ID = OPT.OPT_ID AND PEO.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 PEO.EFFECTIVE_START_DATE AND PEO.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 ,FF_FORMULAS_F FF1 ,FF_FORMULAS_F FF2 WHERE HL1.LOOKUP_CODE(+) = X.PRTN_EFF_STRT_DT_CD AND HL1.LOOKUP_TYPE(+) = 'BEN_PRTN_ELIG_STRT' AND HL2.LOOKUP_CODE(+) = X.PRTN_EFF_END_DT_CD AND HL2.LOOKUP_TYPE(+) = 'BEN_PRTN_ELIG_END' AND HL3.LOOKUP_CODE(+) = X.ELIG_INELIG_CD AND HL3.LOOKUP_TYPE(+) = 'BEN_ELIG_INELIG' AND FF1.FORMULA_ID(+) = X.PRTN_EFF_STRT_DT_RL AND FF2.FORMULA_ID(+) = X.PRTN_EFF_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 ( (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.PRTN_EFF_STRT_DT_CD IS NOT NULL OR X.PRTN_EFF_END_DT_CD IS NOT NULL OR X.PRTN_EFF_STRT_DT_RL IS NOT NULL OR X.PRTN_EFF_END_DT_RL IS NOT NULL OR X.WAIT_PERD_VAL IS NOT NULL OR X.WAIT_PERD_UOM IS NOT NULL OR X.ELIG_INELIG_CD IS NOT NULL)