BEN_DIAG_LDC_V

Details

• Schema: FUSION

• Object owner: BEN

• Object type: VIEW

Columns

Name
PGM PLTYP PL LER ESD EED ADD_RMV_CVG_CD LER_CHG_DPNT_CVG_CD LER_CHG_DPNT_CVG_RL CVG_EFF_STRT_CD CVG_EFF_END_CD CVG_EFF_STRT_RL CVG_EFF_END_RL

Query

SQL_Statement

SELECT X.PGM, X.PLTYP, X.PL, X.LER ,TRUNC(X.EFFECTIVE_START_DATE) ESD ,TRUNC(X.EFFECTIVE_END_DATE) EED ,HL1.MEANING ADD_RMV_CVG_CD ,HL2.MEANING LER_CHG_DPNT_CVG_CD ,FF1.FORMULA_NAME LER_CHG_DPNT_CVG_RL ,HL3.MEANING CVG_EFF_STRT_CD ,HL4.MEANING CVG_EFF_END_CD ,FF2.FORMULA_NAME CVG_EFF_STRT_RL ,FF3.FORMULA_NAME CVG_EFF_END_RL FROM ( SELECT LDC.LER_CHG_DPNT_CVG_ID, PGM.PGM_ID, -1 PTIP_ID, -1 PL_ID, LER.LER_ID ,PGM.NAME PGM, NULL PLTYP, NULL PL, LER.NAME LER ,LDC.EFFECTIVE_START_DATE ,LDC.EFFECTIVE_END_DATE ,LDC.ADD_RMV_CVG_CD ,LDC.LER_CHG_DPNT_CVG_CD ,LDC.LER_CHG_DPNT_CVG_RL ,LDC.CVG_EFF_STRT_CD ,LDC.CVG_EFF_END_CD ,LDC.CVG_EFF_STRT_RL ,LDC.CVG_EFF_END_RL FROM BEN_LER_CHG_DPNT_CVG_F LDC ,BEN_PGM_F PGM ,BEN_LER_F LER WHERE PGM.PGM_ID = LDC.PGM_ID AND LDC.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 LDC.EFFECTIVE_START_DATE AND LDC.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 LDC.LER_CHG_DPNT_CVG_ID, PGM.PGM_ID, CTP.PTIP_ID, -1 PL_ID, LER.LER_ID ,PGM.NAME PGM, PTP.NAME PLTYP, NULL PL, LER.NAME LER ,LDC.EFFECTIVE_START_DATE ,LDC.EFFECTIVE_END_DATE ,LDC.ADD_RMV_CVG_CD ,LDC.LER_CHG_DPNT_CVG_CD ,LDC.LER_CHG_DPNT_CVG_RL ,LDC.CVG_EFF_STRT_CD ,LDC.CVG_EFF_END_CD ,LDC.CVG_EFF_STRT_RL ,LDC.CVG_EFF_END_RL FROM BEN_LER_CHG_DPNT_CVG_F LDC ,BEN_PGM_F PGM ,BEN_PTIP_F CTP ,BEN_PL_TYP_F PTP ,BEN_LER_F LER WHERE CTP.PTIP_ID = LDC.PTIP_ID AND CTP.PL_TYP_ID = PTP.PL_TYP_ID AND PGM.PGM_ID = CTP.PGM_ID AND LDC.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 LDC.EFFECTIVE_START_DATE AND LDC.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 LDC.LER_CHG_DPNT_CVG_ID, -1 PGM_ID, -1 PTIP_ID, PL.PL_ID, LER.LER_ID ,NULL PGM, PTP.NAME PLTYP, PL.NAME PL, LER.NAME LER ,LDC.EFFECTIVE_START_DATE ,LDC.EFFECTIVE_END_DATE ,LDC.ADD_RMV_CVG_CD ,LDC.LER_CHG_DPNT_CVG_CD ,LDC.LER_CHG_DPNT_CVG_RL ,LDC.CVG_EFF_STRT_CD ,LDC.CVG_EFF_END_CD ,LDC.CVG_EFF_STRT_RL ,LDC.CVG_EFF_END_RL FROM BEN_LER_CHG_DPNT_CVG_F LDC ,BEN_LER_F LER ,BEN_PL_TYP_F PTP ,BEN_PL_F PL WHERE PL.PL_ID = LDC.PL_ID AND PL.PL_TYP_ID = PTP.PL_TYP_ID AND LDC.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 LDC.EFFECTIVE_START_DATE AND LDC.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 ) X ,HR_LOOKUPS HL1 ,HR_LOOKUPS HL2 ,HR_LOOKUPS HL3 ,HR_LOOKUPS HL4 ,FF_FORMULAS_F FF1 ,FF_FORMULAS_F FF2 ,FF_FORMULAS_F FF3 WHERE HL1.LOOKUP_CODE(+) = X.ADD_RMV_CVG_CD AND HL1.LOOKUP_TYPE(+) = 'BEN_ADD_RMV' AND HL2.LOOKUP_CODE(+) = X.LER_CHG_DPNT_CVG_CD AND HL2.LOOKUP_TYPE(+) = 'BEN_LER_CHG_DPNT_CVG' AND HL3.LOOKUP_CODE(+) = X.CVG_EFF_STRT_CD AND HL3.LOOKUP_TYPE(+) = 'BEN_CVG_EFF_STRT' AND HL4.LOOKUP_CODE(+) = X.CVG_EFF_END_CD AND HL4.LOOKUP_TYPE(+) = 'BEN_CVG_EFF_END' AND FF1.FORMULA_ID(+) = X.LER_CHG_DPNT_CVG_RL AND FF2.FORMULA_ID(+) = X.CVG_EFF_STRT_RL AND FF3.FORMULA_ID(+) = X.CVG_EFF_END_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 ((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 (ADD_RMV_CVG_CD IS NOT NULL OR LER_CHG_DPNT_CVG_CD IS NOT NULL OR LER_CHG_DPNT_CVG_RL IS NOT NULL OR CVG_EFF_STRT_CD IS NOT NULL OR CVG_EFF_END_CD IS NOT NULL OR CVG_EFF_STRT_RL IS NOT NULL OR CVG_EFF_END_RL IS NOT NULL)