1. Tables and Views for HCM
  2. BEN_DIAG_FF_ATCHMNT_G1_V

BEN_DIAG_FF_ATCHMNT_G1_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_CTFN_F' "Table Name",

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

ARC.EFFECTIVE_START_DATE "Effective Start Date",

ARC.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_ACTY_BASE_RT_F ART,BEN_ACTY_BASE_RT_CTFN_F ARC,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE ART.ACTY_BASE_RT_ID=ARC.ACTY_BASE_RT_ID

AND ARC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT decode(popl.comp_obj_type, 'PLN', PLN.NAME, 'OIPL',substr(PLN2.NAME,1,40)||' :: '||substr(OPT.NAME,1,40), 'PTIP',substr(PGM.NAME,1,40)||' :: '||substr(PLTYP.NAME,1,40) ) "Object Name",

hr_general.decode_lookup('BEN_COMP_OBJ',popl.comp_obj_type) "Object Type",

'BEN_BNFT_RSTRN_CTFN_F' "Table Name",

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

ENC.EFFECTIVE_START_DATE "Effective Start Date",

ENC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN

,BEN_POPL_ACTN_TYP_F POPL

,BEN_BNFT_RSTRN_CTFN_F ENC

,FF_FORMULAS_F FFL

,FF_FORMULA_TYPES FFT

,BEN_OIPL_F OIPL

,BEN_OPT_F OPT

,BEN_PL_F PLN2

,BEN_PTIP_F PTIP

,BEN_PL_TYP_F PLTYP

,BEN_PGM_F PGM

WHERE 1=1

AND POPL.POPL_ACTN_TYP_ID=ENC.POPL_ACTN_TYP_ID

AND ENC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

AND POPL.PL_ID = PLN.PL_ID (+)

AND POPL.OIPL_ID = OIPL.OIPL_ID (+)

AND OIPL.OPT_ID = OPT.OPT_ID (+)

AND OIPL.PL_ID = PLN2.PL_ID (+)

AND POPL.PTIP_ID = PTIP.PTIP_ID (+)

AND PTIP.PL_TYP_ID = PLTYP.PL_TYP_ID (+)

AND PTIP.PGM_ID = PGM.PGM_ID (+)

UNION ALL

SELECT decode(popl.comp_obj_type, 'PLN', PLN.NAME, 'OIPL',substr(PLN2.NAME,1,40)||' :: '||substr(OPT.NAME,1,40), 'PTIP',substr(PGM.NAME,1,40)||' :: '||substr(PLTYP.NAME,1,40) ) "Object Name",

hr_general.decode_lookup('BEN_COMP_OBJ',popl.comp_obj_type) "Object Type",

'BEN_ENRT_CTFN_F' "Table Name",

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

ENC.EFFECTIVE_START_DATE "Effective Start Date",

ENC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN

,BEN_POPL_ACTN_TYP_F POPL

,BEN_ENRT_CTFN_F ENC

,FF_FORMULAS_F FFL

,FF_FORMULA_TYPES FFT

,BEN_OIPL_F OIPL

,BEN_OPT_F OPT

,BEN_PL_F PLN2

,BEN_PTIP_F PTIP

,BEN_PL_TYP_F PLTYP

,BEN_PGM_F PGM

WHERE 1=1

AND POPL.POPL_ACTN_TYP_ID=ENC.POPL_ACTN_TYP_ID

AND ENC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

AND POPL.PL_ID = PLN.PL_ID (+)

AND POPL.OIPL_ID = OIPL.OIPL_ID (+)

AND OIPL.OPT_ID = OPT.OPT_ID (+)

AND OIPL.PL_ID = PLN2.PL_ID (+)

AND POPL.PTIP_ID = PTIP.PTIP_ID (+)

AND PTIP.PL_TYP_ID = PLTYP.PL_TYP_ID (+)

AND PTIP.PGM_ID = PGM.PGM_ID (+)

UNION ALL

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_LER_BNFT_RSTRN_CTFN_F' "Table Name",

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

LBRC.EFFECTIVE_START_DATE "Effective Start Date",

LBRC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_LER_POPL_ACTN_TYP_F LPOPL,BEN_LER_BNFT_RSTRN_CTFN_F LBRC,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=LPOPL.PL_ID

AND LPOPL.LER_POPL_ACTN_TYP_ID=LBRC.LER_POPL_ACTN_TYP_ID

AND LBRC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_LER_CHG_DPNT_CVG_CTFN_F' "Table Name",

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

LCDC.EFFECTIVE_START_DATE "Effective Start Date",

LCDC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_LER_POPL_ACTN_TYP_F LPOPL,BEN_LER_CHG_DPNT_CVG_CTFN_F LCDC,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=LPOPL.PL_ID

AND LPOPL.LER_POPL_ACTN_TYP_ID=LCDC.LER_POPL_ACTN_TYP_ID

AND LCDC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_LER_CHG_OIPL_ENRT_F' "Table Name",

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

LCOE.EFFECTIVE_START_DATE "Effective Start Date",

LCOE.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_OIPL_F OIPL,BEN_LER_CHG_OIPL_ENRT_F LCOE,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=OIPL.PL_ID

AND OIPL.OIPL_ID=LCOE.OIPL_ID

AND LCOE.DFLT_TO_ASN_PNDG_CTFN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_LER_CHG_PL_NIP_ENRT_F' "Table Name",

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

LCPNE.EFFECTIVE_START_DATE "Effective Start Date",

LCPNE.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_LER_CHG_PL_NIP_ENRT_F LCPNE,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=LCPNE.PL_ID

AND LCPNE.DFLT_TO_ASN_PNDG_CTFN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLTY.NAME "Object Name",

'PlanType' "Object Type",

'BEN_LER_CHG_PTIP_ENRT_F' "Table Name",

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

LCPTE.EFFECTIVE_START_DATE "Effective Start Date",

LCPTE.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_TYP_F PLTY,BEN_PTIP_F PTIP,BEN_LER_CHG_PTIP_ENRT_F LCPTE,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLTY.PL_TYP_ID=PTIP.PL_TYP_Id

AND PTIP.PTIP_ID=LCPTE.PTIP_ID

AND LCPTE.DFLT_TO_ASN_PNDG_CTFN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_LER_ENRT_CTFN_F' "Table Name",

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

LEC.EFFECTIVE_START_DATE "Effective Start Date",

LEC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_LER_POPL_ACTN_TYP_F LPAT,BEN_LER_ENRT_CTFN_F LEC,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=LPAT.PL_ID

AND LPAT.LER_POPL_ACTN_TYP_ID=LEC.LER_POPL_ACTN_TYP_ID

AND LEC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT NAME "Object Name",

'Plan' "Object Type",

'BEN_LER_POPL_ACTN_TYP_F' "Table Name",

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

LPAT.EFFECTIVE_START_DATE "Effective Start Date",

LPAT.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN,BEN_LER_POPL_ACTN_TYP_F LPAT,FF_FORMULAS_F FFL, FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=LPAT.PL_ID

AND LPAT.ACTN_TYP_DUE_DT_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",

'DFLT_TO_ASN_PNDG_CTFN_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.DFLT_TO_ASN_PNDG_CTFN_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_DPNT_CVG_CTFN_F' "Table Name",

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

PDCC.EFFECTIVE_START_DATE "Effective Start Date",

PDCC.EFFECTIVE_END_DATE "Effective End Date",

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PGM_F PGM , BEN_PGM_DPNT_CVG_CTFN_F PDCC , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

WHERE PGM.PGM_ID=PDCC.PGM_ID

AND PDCC.CTFN_RQD_WHEN_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",

'DFLT_TO_ASN_PNDG_CTFN_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.DFLT_TO_ASN_PNDG_CTFN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT decode(popl.comp_obj_type, 'PLN', PLN.NAME, 'OIPL',substr(PLN2.NAME,1,40)||' :: '||substr(OPT.NAME,1,40), 'PTIP',substr(PGM.NAME,1,40)||' :: '||substr(PLTYP.NAME,1,40) ) "Object Name",

hr_general.decode_lookup('BEN_COMP_OBJ',popl.comp_obj_type) "Object Type",

'BEN_PL_BNF_CTFN_F' "Table Name",

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

PBC.EFFECTIVE_START_DATE "Effective Start Date",

PBC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN

,BEN_POPL_ACTN_TYP_F POPL

,BEN_PL_BNF_CTFN_F PBC

,FF_FORMULAS_F FFL

,FF_FORMULA_TYPES FFT

,BEN_OIPL_F OIPL

,BEN_OPT_F OPT

,BEN_PL_F PLN2

,BEN_PTIP_F PTIP

,BEN_PL_TYP_F PLTYP

,BEN_PGM_F PGM

WHERE 1=1

AND POPL.POPL_ACTN_TYP_ID=PBC.POPL_ACTN_TYP_ID

AND PBC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

AND POPL.PL_ID = PLN.PL_ID (+)

AND POPL.OIPL_ID = OIPL.OIPL_ID (+)

AND OIPL.OPT_ID = OPT.OPT_ID (+)

AND OIPL.PL_ID = PLN2.PL_ID (+)

AND POPL.PTIP_ID = PTIP.PTIP_ID (+)

AND PTIP.PL_TYP_ID = PLTYP.PL_TYP_ID (+)

AND PTIP.PGM_ID = PGM.PGM_ID (+)

UNION ALL

SELECT decode(popl.comp_obj_type, 'PLN', PLN.NAME, 'OIPL',substr(PLN2.NAME,1,40)||' :: '||substr(OPT.NAME,1,40), 'PTIP',substr(PGM.NAME,1,40)||' :: '||substr(PLTYP.NAME,1,40) ) "Object Name",

hr_general.decode_lookup('BEN_COMP_OBJ',popl.comp_obj_type) "Object Type",

'BEN_PL_DPNT_CVG_CTFN_F' "Table Name",

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

PDCC.EFFECTIVE_START_DATE "Effective Start Date",

PDCC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN

,BEN_POPL_ACTN_TYP_F POPL

,BEN_PL_DPNT_CVG_CTFN_F PDCC

,FF_FORMULAS_F FFL

,FF_FORMULA_TYPES FFT

,BEN_OIPL_F OIPL

,BEN_OPT_F OPT

,BEN_PL_F PLN2

,BEN_PTIP_F PTIP

,BEN_PL_TYP_F PLTYP

,BEN_PGM_F PGM

WHERE 1=1

AND POPL.POPL_ACTN_TYP_ID=PDCC.POPL_ACTN_TYP_ID

AND PDCC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

AND POPL.PL_ID = PLN.PL_ID (+)

AND POPL.OIPL_ID = OIPL.OIPL_ID (+)

AND OIPL.OPT_ID = OPT.OPT_ID (+)

AND OIPL.PL_ID = PLN2.PL_ID (+)

AND POPL.PTIP_ID = PTIP.PTIP_ID (+)

AND PTIP.PL_TYP_ID = PLTYP.PL_TYP_ID (+)

AND PTIP.PGM_ID = PGM.PGM_ID (+)

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_PL_F' "Table Name",

'DFLT_TO_ASN_PNDG_CTFN_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.DFLT_TO_ASN_PNDG_CTFN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT PLN.NAME "Object Name",

'Plan' "Object Type",

'BEN_POPL_ACTN_TYP_F' "Table Name",

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

POPL.EFFECTIVE_START_DATE "Effective Start Date",

POPL.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN , BEN_POPL_ACTN_TYP_F POPL , FF_FORMULAS_F FFL , FF_FORMULA_TYPES FFT

WHERE PLN.PL_ID=POPL.PL_ID

AND POPL.ACTN_TYP_DUE_DT_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

UNION ALL

SELECT decode(popl.comp_obj_type, 'PLN', PLN.NAME, 'OIPL',substr(PLN2.NAME,1,40)||' :: '||substr(OPT.NAME,1,40), 'PTIP',substr(PGM.NAME,1,40)||' :: '||substr(PLTYP.NAME,1,40) ) "Object Name",

hr_general.decode_lookup('BEN_COMP_OBJ',popl.comp_obj_type) "Object Type",

'BEN_PTIP_DPNT_CVG_CTFN_F' "Table Name",

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

PDCC.EFFECTIVE_START_DATE "Effective Start Date",

PDCC.EFFECTIVE_END_DATE "Effective End Date" ,

FFL.ENTERPRISE_ID "Enterprise ID"

FROM BEN_PL_F PLN

,BEN_POPL_ACTN_TYP_F POPL

,BEN_PTIP_DPNT_CVG_CTFN_F PDCC

,FF_FORMULAS_F FFL

,FF_FORMULA_TYPES FFT

,BEN_OIPL_F OIPL

,BEN_OPT_F OPT

,BEN_PL_F PLN2

,BEN_PTIP_F PTIP

,BEN_PL_TYP_F PLTYP

,BEN_PGM_F PGM

WHERE 1=1

AND POPL.POPL_ACTN_TYP_ID=PDCC.POPL_ACTN_TYP_ID

AND PDCC.CTFN_RQD_WHEN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID

AND POPL.PL_ID = PLN.PL_ID (+)

AND POPL.OIPL_ID = OIPL.OIPL_ID (+)

AND OIPL.OPT_ID = OPT.OPT_ID (+)

AND OIPL.PL_ID = PLN2.PL_ID (+)

AND POPL.PTIP_ID = PTIP.PTIP_ID (+)

AND PTIP.PL_TYP_ID = PLTYP.PL_TYP_ID (+)

AND PTIP.PGM_ID = PGM.PGM_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",

'DFLT_TO_ASN_PNDG_CTFN_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.DFLT_TO_ASN_PNDG_CTFN_RL=FFL.FORMULA_ID

AND FFL.FORMULA_TYPE_ID=FFT.FORMULA_TYPE_ID