BEN_DIAG_PGM_PLN_V

Details

• Schema: FUSION

• Object owner: BEN

• Object type: VIEW

Columns

Name
PGM PLTYP PL 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 ENRT_CD ENRT_MTHD_CD ENRT_RL DFLT_ENRT_CD DFLT_ENRT_DET_RL POSTELCN_EDIT_RL DPNT_CVG_STRT_DT_CD DPNT_CVG_STRT_DT_RL DPNT_CVG_END_DT_CD DPNT_CVG_END_DT_RL

Query

SQL_Statement

SELECT X.PGM PGM ,X.PLTYP ,X.PL PL ,HL4.MEANING ENRT_CVG_STRT_DT_CD ,FF1.FORMULA_NAME ENRT_CVG_STRT_DT_RL ,HL5.MEANING ENRT_CVG_END_DT_CD ,FF2.FORMULA_NAME ENRT_CVG_END_DT_RL ,HL6.MEANING RT_STRT_DT_CD ,FF3.FORMULA_NAME RT_STRT_DT_RL ,HL7.MEANING RT_END_DT_CD ,FF4.FORMULA_NAME RT_END_DT_RL ,HL8.MEANING ENRT_CD ,HL9.MEANING ENRT_MTHD_CD ,FF5.FORMULA_NAME ENRT_RL ,HL10.MEANING DFLT_ENRT_CD ,FF6.FORMULA_NAME DFLT_ENRT_DET_RL ,FF7.FORMULA_NAME POSTELCN_EDIT_RL ,HL13.MEANING DPNT_CVG_STRT_DT_CD ,FF8.FORMULA_NAME DPNT_CVG_STRT_DT_RL ,HL14.MEANING DPNT_CVG_END_DT_CD ,FF9.FORMULA_NAME DPNT_CVG_END_DT_RL FROM ( SELECT PGM.PGM_ID, -1 PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID ,PGM.NAME PGM, NULL PLTYP, NULL PL, NULL OPT ,PGM.EFFECTIVE_START_DATE ,PGM.EFFECTIVE_END_DATE ,PGM.PGM_STAT_CD STAT_CD ,PGM.ACTY_REF_PERD_CD ,PGM.ENRT_INFO_RT_FREQ_CD ,PGM.ENRT_CVG_STRT_DT_CD ,PGM.ENRT_CVG_STRT_DT_RL ,PGM.ENRT_CVG_END_DT_CD ,PGM.ENRT_CVG_END_DT_RL ,PGM.RT_STRT_DT_CD ,PGM.RT_STRT_DT_RL ,PGM.RT_END_DT_CD ,PGM.RT_END_DT_RL ,PGM.ENRT_CD ,PGM.ENRT_MTHD_CD ,PGM.ENRT_RL ,NULL DFLT_ENRT_CD ,NULL DFLT_ENRT_DET_RL ,NULL POSTELCN_EDIT_RL ,PGM.DPNT_DSGN_LVL_CD ,PGM.DPNT_CVG_STRT_DT_CD ,PGM.DPNT_CVG_STRT_DT_RL ,PGM.DPNT_CVG_END_DT_CD ,PGM.DPNT_CVG_END_DT_RL FROM BEN_PGM_F PGM WHERE 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 PGM.PGM_ID, CTP.PTIP_ID PTIP_ID, -1 PLIP_ID, -1 PL_ID, -1 OIPL_ID ,PGM.NAME PGM, PTP.NAME PLTYP, NULL PL, NULL OPT ,CTP.EFFECTIVE_START_DATE ,CTP.EFFECTIVE_END_DATE ,CTP.PTIP_STAT_CD STAT_CD ,NULL ACTY_REF_PERD_CD ,NULL ENRT_INFO_RT_FREQ_CD ,CTP.ENRT_CVG_STRT_DT_CD ,CTP.ENRT_CVG_STRT_DT_RL ,CTP.ENRT_CVG_END_DT_CD ,CTP.ENRT_CVG_END_DT_RL ,CTP.RT_STRT_DT_CD ,CTP.RT_STRT_DT_RL ,CTP.RT_END_DT_CD ,CTP.RT_END_DT_RL ,CTP.ENRT_CD ,CTP.ENRT_MTHD_CD ,CTP.ENRT_RL ,CTP.DFLT_ENRT_CD ,CTP.DFLT_ENRT_DET_RL ,CTP.POSTELCN_EDIT_RL ,NULL DPNT_DSGN_LVL_CD ,CTP.DPNT_CVG_STRT_DT_CD ,CTP.DPNT_CVG_STRT_DT_RL ,CTP.DPNT_CVG_END_DT_CD ,CTP.DPNT_CVG_END_DT_RL FROM BEN_PGM_F PGM ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP WHERE PGM.PGM_ID = CTP.PGM_ID AND PTP.PL_TYP_ID = CTP.PL_TYP_ID 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 PGM.PGM_ID, CTP.PTIP_ID PTIP_ID, CPP.PLIP_ID, PL.PL_ID, -1 OIPL_ID ,PGM.NAME PGM, PTP.NAME PLTYP, PL.NAME PL, NULL OPT ,CPP.EFFECTIVE_START_DATE ,CPP.EFFECTIVE_END_DATE ,CPP.PLIP_STAT_CD STAT_CD ,NULL ACTY_REF_PERD_CD ,NULL ENRT_INFO_RT_FREQ_CD ,CPP.ENRT_CVG_STRT_DT_CD ,CPP.ENRT_CVG_STRT_DT_RL ,CPP.ENRT_CVG_END_DT_CD ,CPP.ENRT_CVG_END_DT_RL ,CPP.RT_STRT_DT_CD ,CPP.RT_STRT_DT_RL ,CPP.RT_END_DT_CD ,CPP.RT_END_DT_RL ,CPP.ENRT_CD ,CPP.ENRT_MTHD_CD ,CPP.ENRT_RL ,CPP.DFLT_ENRT_CD ,CPP.DFLT_ENRT_DET_RL ,CPP.POSTELCN_EDIT_RL ,NULL DPNT_DSGN_LVL_CD ,NULL DPNT_CVG_STRT_DT_CD ,NULL DPNT_CVG_STRT_DT_RL ,NULL DPNT_CVG_END_DT_CD ,NULL DPNT_CVG_END_DT_RL FROM BEN_PGM_F PGM ,BEN_PLIP_F CPP ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP ,BEN_PL_F PL WHERE PGM.PGM_ID = CPP.PGM_ID AND CPP.PL_ID = PL.PL_ID AND PGM.PGM_ID = CTP.PGM_ID AND CTP.PL_TYP_ID = PTP.PL_TYP_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 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 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 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 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 -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 ,PL.EFFECTIVE_START_DATE ,PL.EFFECTIVE_END_DATE ,PL.PL_STAT_CD STAT_CD ,NULL ACTY_REF_PERD_CD ,NULL ENRT_INFO_RT_FREQ_CD ,PL.ENRT_CVG_STRT_DT_CD ,PL.ENRT_CVG_STRT_DT_RL ,PL.ENRT_CVG_END_DT_CD ,PL.ENRT_CVG_END_DT_RL ,PL.RT_STRT_DT_CD ,PL.RT_STRT_DT_RL ,PL.RT_END_DT_CD ,PL.RT_END_DT_RL ,PL.ENRT_CD ,PL.ENRT_MTHD_CD ,PL.ENRT_RL ,NULL DFLT_ENRT_CD ,NULL DFLT_ENRT_DET_RL ,NULL POSTELCN_EDIT_RL ,NULL DPNT_DSGN_LVL_CD ,PL.DPNT_CVG_STRT_DT_CD ,PL.DPNT_CVG_STRT_DT_RL ,PL.DPNT_CVG_END_DT_CD ,PL.DPNT_CVG_END_DT_RL FROM BEN_PL_F PL ,BEN_PL_TYP_F PTP WHERE 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 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 ) X ,HR_LOOKUPS HL1 ,HR_LOOKUPS HL2 ,HR_LOOKUPS HL3 ,HR_LOOKUPS HL4 ,FF_FORMULAS_F FF1 ,HR_LOOKUPS HL5 ,FF_FORMULAS_F FF2 ,HR_LOOKUPS HL6 ,FF_FORMULAS_F FF3 ,HR_LOOKUPS HL7 ,FF_FORMULAS_F FF4 ,HR_LOOKUPS HL8 ,HR_LOOKUPS HL9 ,FF_FORMULAS_F FF5 ,HR_LOOKUPS HL10 ,FF_FORMULAS_F FF6 ,FF_FORMULAS_F FF7 ,HR_LOOKUPS HL11 ,HR_LOOKUPS HL12 ,HR_LOOKUPS HL13 ,HR_LOOKUPS HL14 ,FF_FORMULAS_F FF8 ,FF_FORMULAS_F FF9 WHERE HL1.LOOKUP_CODE(+) = X.STAT_CD AND HL1.LOOKUP_TYPE(+) = 'BEN_STAT' AND HL2.LOOKUP_CODE(+) = X.ACTY_REF_PERD_CD AND HL2.LOOKUP_TYPE(+) = 'BEN_ACTY_REF_PERD' AND HL3.LOOKUP_CODE(+) = X.ENRT_INFO_RT_FREQ_CD AND HL3.LOOKUP_TYPE(+) = 'BEN_ENRT_INFO_RT_FREQ' AND HL4.LOOKUP_CODE(+) = X.ENRT_CVG_STRT_DT_CD AND HL4.LOOKUP_TYPE(+) = 'BEN_ENRT_CVG_STRT' AND FF1.FORMULA_ID(+) = X.ENRT_CVG_STRT_DT_RL AND HL5.LOOKUP_CODE(+) = X.ENRT_CVG_END_DT_CD AND HL5.LOOKUP_TYPE(+) = 'BEN_ENRT_CVG_END' AND FF2.FORMULA_ID(+) = X.ENRT_CVG_END_DT_RL AND HL6.LOOKUP_CODE(+) = X.RT_STRT_DT_CD AND HL6.LOOKUP_TYPE(+) = 'BEN_RT_STRT' AND FF3.FORMULA_ID(+) = X.RT_STRT_DT_RL AND HL7.LOOKUP_CODE(+) = X.RT_END_DT_CD AND HL7.LOOKUP_TYPE(+) = 'BEN_RT_END' AND FF4.FORMULA_ID(+) = X.RT_END_DT_RL AND HL8.LOOKUP_CODE(+) = X.ENRT_CD AND HL8.LOOKUP_TYPE(+) = 'BEN_ENRT' AND HL9.LOOKUP_CODE(+) = X.ENRT_MTHD_CD AND HL9.LOOKUP_TYPE(+) = 'BEN_ENRT_MTHD' AND FF5.FORMULA_ID(+) = X.ENRT_RL AND HL10.LOOKUP_CODE(+) = X.DFLT_ENRT_CD AND HL10.LOOKUP_TYPE(+) = 'BEN_DFLT_ENRT' AND FF6.FORMULA_ID(+) = X.DFLT_ENRT_DET_RL AND FF7.FORMULA_ID(+) = X.POSTELCN_EDIT_RL AND HL11.LOOKUP_CODE(+) = X.DPNT_DSGN_LVL_CD AND HL11.LOOKUP_TYPE(+) = 'BEN_DPNT_DSGN_LVL' AND HL12.LOOKUP_TYPE(+) = 'BEN_DPNT_DSGN' AND HL13.LOOKUP_CODE(+) = X.DPNT_CVG_STRT_DT_CD AND HL13.LOOKUP_TYPE(+) = 'BEN_DPNT_CVG_STRT' AND HL14.LOOKUP_CODE(+) = X.DPNT_CVG_END_DT_CD AND HL14.LOOKUP_TYPE(+) = 'BEN_DPNT_CVG_END' AND FF8.FORMULA_ID(+) = X.DPNT_CVG_STRT_DT_RL AND FF9.FORMULA_ID(+) = X.DPNT_CVG_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 TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF7.EFFECTIVE_START_DATE(+) AND FF7.EFFECTIVE_END_DATE(+) AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF8.EFFECTIVE_START_DATE(+) AND FF8.EFFECTIVE_END_DATE(+) AND TO_DATE(TO_CHAR(FND_DIAG_SDK_API.GET_DATE_INPUT_PARAM('effectiveDate'),'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN FF9.EFFECTIVE_START_DATE(+) AND FF9.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.ACTY_REF_PERD_CD IS NOT NULL OR X.ENRT_INFO_RT_FREQ_CD IS NOT NULL OR X.ENRT_CVG_STRT_DT_CD IS NOT NULL OR X.ENRT_CVG_STRT_DT_RL IS NOT NULL OR X.ENRT_CVG_END_DT_CD IS NOT NULL OR X.ENRT_CVG_END_DT_RL IS NOT NULL OR X.RT_STRT_DT_CD IS NOT NULL OR X.RT_STRT_DT_RL IS NOT NULL OR X.RT_END_DT_CD IS NOT NULL OR X.RT_END_DT_RL IS NOT NULL OR X.ENRT_CD IS NOT NULL OR X.ENRT_MTHD_CD = 'A' OR X.ENRT_RL IS NOT NULL OR X.DFLT_ENRT_CD IS NOT NULL OR X.DFLT_ENRT_DET_RL IS NOT NULL OR X.POSTELCN_EDIT_RL IS NOT NULL OR X.DPNT_DSGN_LVL_CD IS NOT NULL OR X.DPNT_CVG_STRT_DT_CD IS NOT NULL OR X.DPNT_CVG_STRT_DT_RL IS NOT NULL OR X.DPNT_CVG_END_DT_CD IS NOT NULL OR X.DPNT_CVG_END_DT_RL IS NOT NULL)