1. Tables and Views for HCM
  2. BEN_DIAG_FF_ATCHMNT_G9_V

BEN_DIAG_FF_ATCHMNT_G9_V

Details

  • Schema: FUSION

  • Object owner: BEN

  • Object type: VIEW

Columns

Name

Object Name

Object Type

Table Name

Column Name

Formula Name

Formula Id

Formula Type Name

Formula Type Id

Effective Start Date

Effective End Date

Enterprise ID

Query

SQL_Statement

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_ENRT_PERD' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

TO_DATE(NULL) "Effective Start Date",

TO_DATE(NULL) "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_ENRT_PERD ENP,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=POPL.PL_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=ENP.POPL_ENRT_TYP_CYCL_ID

AND ENP.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Program' "Object Type",

'BEN_ENRT_PERD' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

TO_DATE(NULL) "Effective Start Date",

TO_DATE(NULL) "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_ENRT_PERD ENP,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PGM.PGM_ID=POPL.PGM_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=ENP.POPL_ENRT_TYP_CYCL_ID

AND ENP.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_ENRT_PERD' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

TO_DATE(NULL) "Effective Start Date",

TO_DATE(NULL) "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_ENRT_PERD ENP,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=POPL.PL_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=ENP.POPL_ENRT_TYP_CYCL_ID

AND ENP.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Program' "Object Type",

'BEN_ENRT_PERD' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

TO_DATE(NULL) "Effective Start Date",

TO_DATE(NULL) "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_ENRT_PERD ENP,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PGM.PGM_ID=POPL.PGM_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=ENP.POPL_ENRT_TYP_CYCL_ID

AND ENP.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_ENRT_PERD_FOR_PL_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

EPPL.EFFECTIVE_START_DATE "Effective Start Date",

EPPL.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_ENRT_PERD_FOR_PL_F EPPL,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=EPPL.PL_ID

AND EPPL.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_ENRT_PERD_FOR_PL_F' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

EPPL.EFFECTIVE_START_DATE "Effective Start Date",

EPPL.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_ENRT_PERD_FOR_PL_F EPPL,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=EPPL.PL_ID

AND EPPL.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_LEE_RSN_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

LRN.EFFECTIVE_START_DATE "Effective Start Date",

LRN.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_LEE_RSN_F LRN,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=POPL.PL_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=LRN.POPL_ENRT_TYP_CYCL_ID

AND LRN.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Program' "Object Type",

'BEN_LEE_RSN_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

LRN.EFFECTIVE_START_DATE "Effective Start Date",

LRN.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_LEE_RSN_F LRN,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PGM.PGM_ID=POPL.PGM_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=LRN.POPL_ENRT_TYP_CYCL_ID

AND LRN.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_LEE_RSN_F' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

LRN.EFFECTIVE_START_DATE "Effective Start Date",

LRN.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_LEE_RSN_F LRN,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=POPL.PL_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=LRN.POPL_ENRT_TYP_CYCL_ID

AND LRN.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Program' "Object Type",

'BEN_LEE_RSN_F' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

LRN.EFFECTIVE_START_DATE "Effective Start Date",

LRN.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM,BEN_POPL_ENRT_TYP_CYCL POPL,BEN_LEE_RSN_F LRN,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PGM.PGM_ID=POPL.PGM_ID

AND POPL.POPL_ENRT_TYP_CYCL_ID=LRN.POPL_ENRT_TYP_CYCL_ID

AND LRN.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PGM.NAME "Object Name",

'Program' "Object Type",

'BEN_PGM_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PGM.EFFECTIVE_START_DATE "Effective Start Date",

PGM.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

WHERE PGM.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PGM.NAME "Object Name",

'Program' "Object Type",

'BEN_PGM_F' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PGM.EFFECTIVE_START_DATE "Effective Start Date",

PGM.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

WHERE PGM.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT SUBSTR(PGM.NAME,1,40)||' :: '||SUBSTR(PLN.NAME,1,40) "Object Name",

'Program :: Plan' "Object Type",

'BEN_PLIP_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PLIP.EFFECTIVE_START_DATE "Effective Start Date",

PLIP.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM , BEN_PL_F PLN , BEN_PLIP_F PLIP , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

Where PGM.PGM_ID=PLIP.PGM_ID

AND PLN.PL_ID=PLIP.PL_ID

AND PLIP.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT SUBSTR(PGM.NAME,1,40)||' :: '||SUBSTR(PLN.NAME,1,40) "Object Name",

'Program :: Plan' "Object Type",

'BEN_PLIP_F' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PLIP.EFFECTIVE_START_DATE "Effective Start Date",

PLIP.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM , BEN_PL_F PLN , BEN_PLIP_F PLIP , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

Where PGM.PGM_ID=PLIP.PGM_ID

AND PLN.PL_ID=PLIP.PL_ID

AND PLIP.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_PL_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PLN.EFFECTIVE_START_DATE "Effective Start Date",

PLN.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

WHERE PLN.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_PL_F' "Table Name",

'ENRT_CVG_STRT_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PLN.EFFECTIVE_START_DATE "Effective Start Date",

PLN.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

WHERE PLN.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT SUBSTR(PGM.NAME,1,40)||' :: '||SUBSTR(PLTY.NAME,1,40) "Object Name",

'Program :: PlanType' "Object Type",

'BEN_PTIP_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PTIP.EFFECTIVE_START_DATE "Effective Start Date",

PTIP.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM , BEN_PL_TYP_F PLTY , BEN_PTIP_F PTIP , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

Where PGM.PGM_ID=PTIP.PGM_ID

AND PLTY.PL_TYP_ID=PTIP.PL_TYP_ID

AND PTIP.ENRT_CVG_END_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT SUBSTR(PGM.NAME,1,40)||' :: '||SUBSTR(PLTY.NAME,1,40) "Object Name",

'Program :: PlanType' "Object Type",

'BEN_PTIP_F' "Table Name",

'ENRT_CVG_END_DT_RL' "Column Name",

FFL.FORMULA_NAME "Formula Name",

FFL.FORMULA_ID "Formula Id",

FFT.FORMULA_TYPE_NAME "Formula Type Name",

FFT.FORMULA_TYPE_ID "Formula Type Id",

PTIP.EFFECTIVE_START_DATE "Effective Start Date",

PTIP.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM , BEN_PL_TYP_F PLTY , BEN_PTIP_F PTIP , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

Where PGM.PGM_ID=PTIP.PGM_ID

AND PLTY.PL_TYP_ID=PTIP.PL_TYP_ID

AND PTIP.ENRT_CVG_STRT_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID