1. Tables and Views for HCM
  2. BEN_DIAG_FF_ATCHMNT_G7_V

BEN_DIAG_FF_ATCHMNT_G7_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",

'Rate' "Object Type",

'BEN_ACTY_BASE_RT_F' "Table Name",

'ELEMENT_DET_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",

ART.EFFECTIVE_START_DATE "Effective Start Date",

ART.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_ACTY_BASE_RT_F ART, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE ART.ELEMENT_DET_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Rate' "Object Type",

'BEN_ACTY_BASE_RT_F' "Table Name",

'EXCS_TRTMT_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",

ART.EFFECTIVE_START_DATE "Effective Start Date",

ART.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_ACTY_BASE_RT_F ART, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE ART.EXCS_TRTMT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Rate' "Object Type",

'BEN_ACTY_BASE_RT_F' "Table Name",

'INPUT_VA_CALC_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",

ART.EFFECTIVE_START_DATE "Effective Start Date",

ART.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_ACTY_BASE_RT_F ART, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE ART.INPUT_VA_CALC_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Rate' "Object Type",

'BEN_ACTY_BASE_RT_F' "Table Name",

'PRTL_MO_DET_MTHD_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",

ART.EFFECTIVE_START_DATE "Effective Start Date",

ART.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_ACTY_BASE_RT_F ART, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE ART.PRTL_MO_DET_MTHD_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Rate' "Object Type",

'BEN_ACTY_BASE_RT_F' "Table Name",

'PRTL_MO_EFF_DT_DET_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",

ART.EFFECTIVE_START_DATE "Effective Start Date",

ART.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_ACTY_BASE_RT_F ART, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE ART.PRTL_MO_EFF_DT_DET_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Rate' "Object Type",

'BEN_ACTY_BASE_RT_F' "Table Name",

'RATE_PERIODIZATION_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",

ART.EFFECTIVE_START_DATE "Effective Start Date",

ART.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_ACTY_BASE_RT_F ART, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE ART.RATE_PERIODIZATION_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Collapse LE Logic' "Object Type",

'BEN_CLPSE_LF_EVT' "Table Name",

'CLPSE_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_CLPSE_LF_EVT CLE, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE CLE.CLPSE_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Collapse LE Logic' "Object Type",

'BEN_CLPSE_LF_EVT' "Table Name",

'EVAL_LER_DET_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_CLPSE_LF_EVT CLE, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE CLE.EVAL_LER_DET_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Collapse LE Logic' "Object Type",

'BEN_CLPSE_LF_EVT' "Table Name",

'EVAL_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_CLPSE_LF_EVT CLE, FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE CLE.EVAL_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Life Event' "Object Type",

'BEN_LER_F' "Table Name",

'LER_EVAL_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",

LER.EFFECTIVE_START_DATE "Effective Start Date",

LER.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_LER_F LER,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE LER.LER_EVAL_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Life Event' "Object Type",

'BEN_LER_F' "Table Name",

'TMLNS_PERD_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",

LER.EFFECTIVE_START_DATE "Effective Start Date",

LER.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_LER_F LER,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE LER.TMLNS_PERD_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Life Event' "Object Type",

'BEN_LER_PER_INFO_CS_LER' "Table Name",

'LER_PER_INFO_CS_LER_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_LER_F LER,BEN_LER_PER_INFO_CS_LER LPIC,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE LER.LER_ID=LPIC.LER_ID

AND LPIC.LER_PER_INFO_CS_LER_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Life Event' "Object Type",

'BEN_LER_RLTD_PER_CS_LER' "Table Name",

'LER_RLTD_PER_CS_CHG_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_LER_F LER,BEN_LER_RLTD_PER_CS_LER LRPL,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE LER.LER_ID=LRPL.LER_ID

AND LRPL.LER_RLTD_PER_CS_CHG_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

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

'Plan :: Option' "Object Type",

'BEN_OIPL_F' "Table Name",

'MNDTRY_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",

OIPL.EFFECTIVE_START_DATE "Effective Start Date",

OIPL.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN , BEN_OPT_F OPT , BEN_OIPL_F OIPL , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID = OIPL.PL_ID

AND OPT.OPT_ID = OIPL.OPT_ID

AND OIPL.MNDTRY_RL = FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID = FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Personal Information Change' "Object Type",

'BEN_PER_INFO_CHG_CS_LER' "Table Name",

'PER_INFO_CHG_CS_LER_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_PER_INFO_CHG_CS_LER PICL,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PICL.PER_INFO_CHG_CS_LER_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Change Causing Related Person LE' "Object Type",

'BEN_RLTD_PER_CHG_CS_LER' "Table Name",

'RLTD_PER_CHG_CS_LER_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_RLTD_PER_CHG_CS_LER RPCC,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE RPCC.RLTD_PER_CHG_CS_LER_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID