BEN_DIAG_EPA_V

Details

• Schema: FUSION

• Object owner: BEN

• Object type: VIEW

Columns

Name
PGM PLTYP PL OPT 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_APLS_FLAG ELIGY_PRFL_CNT

Query

SQL_Statement

SELECT X.PGM, X.PLTYP, X.PL, X.OPT ,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 ,X.ELIG_APLS_FLAG ,NVL(X.ELIGY_PRFL_CNT, 0) ELIGY_PRFL_CNT FROM ( SELECT EPA.PRTN_ELIG_ID, PGM.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID ,PGM.NAME PGM, NULL PLTYP, NULL PL, NULL OPT ,EPA.EFFECTIVE_START_DATE ,EPA.EFFECTIVE_END_DATE ,EPA.PRTN_EFF_STRT_DT_CD ,EPA.PRTN_EFF_END_DT_CD ,EPA.PRTN_EFF_STRT_DT_RL ,EPA.PRTN_EFF_END_DT_RL ,EPA.WAIT_PERD_VAL ,EPA.WAIT_PERD_UOM ,PGM.ELIG_APLS_FLAG ,(SELECT COUNT(EPRF.ELIGY_PRFL_ID) FROM BEN_PRTN_ELIG_PRFL_F EPRF WHERE EPA.PRTN_ELIG_ID = EPRF.PRTN_ELIG_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPRF.EFFECTIVE_START_DATE AND EPRF.EFFECTIVE_END_DATE ) ELIGY_PRFL_CNT FROM BEN_PRTN_ELIG_F EPA ,BEN_PGM_F PGM WHERE EPA.PGM_ID = PGM.PGM_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPA.EFFECTIVE_START_DATE AND EPA.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 UNION ALL SELECT EPA.PRTN_ELIG_ID, PGM.PGM_ID, CTP.PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID ,PGM.NAME PGM, PTP.NAME PLTYP, NULL PL, NULL OPT ,EPA.EFFECTIVE_START_DATE ,EPA.EFFECTIVE_END_DATE ,EPA.PRTN_EFF_STRT_DT_CD ,EPA.PRTN_EFF_END_DT_CD ,EPA.PRTN_EFF_STRT_DT_RL ,EPA.PRTN_EFF_END_DT_RL ,EPA.WAIT_PERD_VAL ,EPA.WAIT_PERD_UOM ,CTP.ELIG_APLS_FLAG ,(SELECT COUNT(EPRF.ELIGY_PRFL_ID) FROM BEN_PRTN_ELIG_PRFL_F EPRF WHERE EPA.PRTN_ELIG_ID = EPRF.PRTN_ELIG_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPRF.EFFECTIVE_START_DATE AND EPRF.EFFECTIVE_END_DATE ) ELIGY_PRFL_CNT FROM BEN_PRTN_ELIG_F EPA ,BEN_PGM_F PGM ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP WHERE CTP.PTIP_ID = EPA.PTIP_ID AND CTP.PL_TYP_ID = PTP.PL_TYP_ID AND PGM.PGM_ID = CTP.PGM_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPA.EFFECTIVE_START_DATE AND EPA.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 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 EPA.PRTN_ELIG_ID, PGM.PGM_ID, CTP.PTIP_ID, CPP.PLIP_ID, PL.PL_ID, -1 OIPL_ID ,PGM.NAME PGM, PTP.NAME PLTYP, PL.NAME PL, NULL OPT ,EPA.EFFECTIVE_START_DATE ,EPA.EFFECTIVE_END_DATE ,EPA.PRTN_EFF_STRT_DT_CD ,EPA.PRTN_EFF_END_DT_CD ,EPA.PRTN_EFF_STRT_DT_RL ,EPA.PRTN_EFF_END_DT_RL ,EPA.WAIT_PERD_VAL ,EPA.WAIT_PERD_UOM ,CPP.ELIG_APLS_FLAG ,(SELECT COUNT(EPRF.ELIGY_PRFL_ID) FROM BEN_PRTN_ELIG_PRFL_F EPRF WHERE EPA.PRTN_ELIG_ID = EPRF.PRTN_ELIG_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPRF.EFFECTIVE_START_DATE AND EPRF.EFFECTIVE_END_DATE ) ELIGY_PRFL_CNT FROM BEN_PRTN_ELIG_F EPA ,BEN_PGM_F PGM ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP ,BEN_PLIP_F CPP ,BEN_PL_F PL WHERE CPP.PLIP_ID = EPA.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 TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPA.EFFECTIVE_START_DATE AND EPA.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 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 EPA.PRTN_ELIG_ID, -1 PGM_ID, -1 PTIP_ID, -1 PLIP_ID, PL.PL_ID, -1 OIPL_ID ,NULL PGM, PTP.NAME PLTYP, PL.NAME PL, NULL OPT ,EPA.EFFECTIVE_START_DATE ,EPA.EFFECTIVE_END_DATE ,EPA.PRTN_EFF_STRT_DT_CD ,EPA.PRTN_EFF_END_DT_CD ,EPA.PRTN_EFF_STRT_DT_RL ,EPA.PRTN_EFF_END_DT_RL ,EPA.WAIT_PERD_VAL ,EPA.WAIT_PERD_UOM ,PL.ELIG_APLS_FLAG ,(SELECT COUNT(EPRF.ELIGY_PRFL_ID) FROM BEN_PRTN_ELIG_PRFL_F EPRF WHERE EPA.PRTN_ELIG_ID = EPRF.PRTN_ELIG_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPRF.EFFECTIVE_START_DATE AND EPRF.EFFECTIVE_END_DATE ) ELIGY_PRFL_CNT FROM BEN_PRTN_ELIG_F EPA ,BEN_PL_F PL ,BEN_PL_TYP_F PTP WHERE PL.PL_ID = EPA.PL_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 EPA.EFFECTIVE_START_DATE AND EPA.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 PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE UNION ALL SELECT EPA.PRTN_ELIG_ID, -1 PGM_ID, -1 PTIP_ID, -1 PLIP_ID, PL.PL_ID, COP.OIPL_ID ,NULL PGM, PTP.NAME PLTYP, PL.NAME PL, OPT.NAME OPT ,EPA.EFFECTIVE_START_DATE ,EPA.EFFECTIVE_END_DATE ,EPA.PRTN_EFF_STRT_DT_CD ,EPA.PRTN_EFF_END_DT_CD ,EPA.PRTN_EFF_STRT_DT_RL ,EPA.PRTN_EFF_END_DT_RL ,EPA.WAIT_PERD_VAL ,EPA.WAIT_PERD_UOM ,COP.ELIG_APLS_FLAG ,(SELECT COUNT(EPRF.ELIGY_PRFL_ID) FROM BEN_PRTN_ELIG_PRFL_F EPRF WHERE EPA.PRTN_ELIG_ID = EPRF.PRTN_ELIG_ID AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN EPRF.EFFECTIVE_START_DATE AND EPRF.EFFECTIVE_END_DATE ) ELIGY_PRFL_CNT FROM BEN_PRTN_ELIG_F EPA ,BEN_PL_F PL ,BEN_PL_TYP_F PTP ,BEN_OIPL_F COP ,BEN_OPT_F OPT WHERE COP.OIPL_ID = EPA.OIPL_ID AND COP.PL_ID = PL.PL_ID AND COP.OPT_ID = OPT.OPT_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 EPA.EFFECTIVE_START_DATE AND EPA.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 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 ,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 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)