VIEW: APPS.BENBV_PGM_V

Source

View Text - Preformatted

SELECT PGM.EFFECTIVE_START_DATE, PGM.EFFECTIVE_END_DATE, PGM.NAME, PGM.COORD_CVG_FOR_ALL_PLS_FLG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.COORD_CVG_FOR_ALL_PLS_FLG), PGM.ENRT_CVG_END_DT_RL, PGM.IVR_IDENT, PGM.ENRT_CVG_STRT_DT_RL, PGM.DPNT_CVG_STRT_DT_RL, PGM.URL_REF_NAME, PGM.PGM_DESC, PGM.MX_DPNT_PCT_PRTT_LF_AMT, PGM.MX_SPS_PCT_PRTT_LF_AMT, PGM.DPNT_CVG_END_DT_RL, PGM.RT_STRT_DT_RL, PGM.RT_END_DT_RL, PGM.PGM_UOM, PGM.AUTO_ENRT_MTHD_RL, PGM.ENRT_RL, PGM.VRFY_FMLY_MMBR_RL, PGM.SHORT_CODE, PGM.SHORT_NAME, PGM.DFLT_STEP_RL, PGM.SCORES_CALC_RL, PGM.SALARY_CALC_MTHD_RL, PGM.LEGISLATION_CODE, PGM.LEGISLATION_SUBGROUP, /* descriptive flexfield */ '_DF:BEN:BEN_PGM_F:PGM', /* codes */ PGM.DPNT_DSGN_LVL_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_DSGN_LVL',PGM.DPNT_DSGN_LVL_CD), PGM.PGM_STAT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_STAT',PGM.PGM_STAT_CD), PGM.PGM_TYP_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_PGM_TYP',PGM.PGM_TYP_CD), PGM.DPNT_DSGN_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_DSGN',PGM.DPNT_DSGN_CD), PGM.ACTY_REF_PERD_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ACTY_REF_PERD',PGM.ACTY_REF_PERD_CD), PGM.ENRT_CVG_END_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_END',PGM.ENRT_CVG_END_DT_CD), PGM.DPNT_CVG_END_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_END',PGM.DPNT_CVG_END_DT_CD), PGM.DPNT_CVG_STRT_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_STRT',PGM.DPNT_CVG_STRT_DT_CD), PGM.ENRT_CVG_STRT_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_STRT',PGM.ENRT_CVG_STRT_DT_CD), PGM.ENRT_INFO_RT_FREQ_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_INFO_RT_FREQ',PGM.ENRT_INFO_RT_FREQ_CD), PGM.RT_STRT_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_STRT',PGM.RT_STRT_DT_CD), PGM.RT_END_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_END',PGM.RT_END_DT_CD), PGM.PGM_GRP_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_PGM_GRP',PGM.PGM_GRP_CD), PGM.ENRT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT',PGM.ENRT_CD), PGM.ENRT_MTHD_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_MTHD',PGM.ENRT_MTHD_CD), PGM.POE_LVL_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_POE_LVL',PGM.POE_LVL_CD), PGM.VRFY_FMLY_MMBR_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_FMLY_MMBR',PGM.VRFY_FMLY_MMBR_CD), PGM.PER_CVRD_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_PER_CVRD',PGM.PER_CVRD_CD), PGM.DFLT_STEP_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DFLT_STEP',PGM.DFLT_STEP_CD), PGM.UPDATE_SALARY_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_UPDATE_SALARY',PGM.UPDATE_SALARY_CD), PGM.USE_SCORES_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_USE_SCORES',PGM.USE_SCORES_CD), PGM.SCORES_CALC_MTHD_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_SCORES_CALC_MTHD',PGM.SCORES_CALC_MTHD_CD), PGM.SALARY_CALC_MTHD_CD, HR_BIS.BIS_DECODE_LOOKUP('PQH_GSP_SAL_CALC_METH',PGM.SALARY_CALC_MTHD_CD), PGM.DPNT_ADRS_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DPNT_ADRS_RQD_FLAG), PGM.PGM_PRVDS_NO_AUTO_ENRT_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.PGM_PRVDS_NO_AUTO_ENRT_FLAG), PGM.DPNT_DOB_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DPNT_DOB_RQD_FLAG), PGM.PGM_PRVDS_NO_DFLT_ENRT_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.PGM_PRVDS_NO_DFLT_ENRT_FLAG), PGM.DPNT_LEGV_ID_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DPNT_LEGV_ID_RQD_FLAG), PGM.ELIG_APLS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.ELIG_APLS_FLAG), PGM.USES_ALL_ASMTS_FOR_RTS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.USES_ALL_ASMTS_FOR_RTS_FLAG), PGM.PRTN_ELIG_OVRID_ALWD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.PRTN_ELIG_OVRID_ALWD_FLAG), PGM.PGM_USE_ALL_ASNTS_ELIG_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.PGM_USE_ALL_ASNTS_ELIG_FLAG), PGM.DPNT_DSGN_NO_CTFN_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DPNT_DSGN_NO_CTFN_RQD_FLAG), PGM.DRVBL_FCTR_DPNT_ELIG_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DRVBL_FCTR_DPNT_ELIG_FLAG), PGM.DRVBL_FCTR_PRTN_ELIG_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DRVBL_FCTR_PRTN_ELIG_FLAG), PGM.DRVBL_FCTR_APLS_RTS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DRVBL_FCTR_APLS_RTS_FLAG), PGM.TRK_INELIG_PER_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.TRK_INELIG_PER_FLAG), PGM.ALWS_UNRSTRCTD_ENRT_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.ALWS_UNRSTRCTD_ENRT_FLAG), PGM.DFLT_PGM_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.DFLT_PGM_FLAG), PGM.USE_PROG_POINTS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.USE_PROG_POINTS_FLAG), PGM.USE_MULTI_PAY_RATES_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.USE_MULTI_PAY_RATES_FLAG), PGM.USE_VARIABLE_RATES_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.USE_VARIABLE_RATES_FLAG), PGM.GSP_ALLOW_OVERRIDE_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',PGM.GSP_ALLOW_OVERRIDE_FLAG), /* id columns */ PGM.PGM_ID, PGM.BUSINESS_GROUP_ID, PGM.DFLT_ELEMENT_TYPE_ID, PGM.DFLT_INPUT_VALUE_ID, /* who columns */ PGM.LAST_UPDATE_DATE, PGM.LAST_UPDATED_BY, PGM.CREATED_BY, PGM.CREATION_DATE FROM BEN_PGM_F PGM WHERE PGM.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PGM.BUSINESS_GROUP_ID) WITH READ ONLY

View Text - HTML Formatted

SELECT PGM.EFFECTIVE_START_DATE
, PGM.EFFECTIVE_END_DATE
, PGM.NAME
, PGM.COORD_CVG_FOR_ALL_PLS_FLG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.COORD_CVG_FOR_ALL_PLS_FLG)
, PGM.ENRT_CVG_END_DT_RL
, PGM.IVR_IDENT
, PGM.ENRT_CVG_STRT_DT_RL
, PGM.DPNT_CVG_STRT_DT_RL
, PGM.URL_REF_NAME
, PGM.PGM_DESC
, PGM.MX_DPNT_PCT_PRTT_LF_AMT
, PGM.MX_SPS_PCT_PRTT_LF_AMT
, PGM.DPNT_CVG_END_DT_RL
, PGM.RT_STRT_DT_RL
, PGM.RT_END_DT_RL
, PGM.PGM_UOM
, PGM.AUTO_ENRT_MTHD_RL
, PGM.ENRT_RL
, PGM.VRFY_FMLY_MMBR_RL
, PGM.SHORT_CODE
, PGM.SHORT_NAME
, PGM.DFLT_STEP_RL
, PGM.SCORES_CALC_RL
, PGM.SALARY_CALC_MTHD_RL
, PGM.LEGISLATION_CODE
, PGM.LEGISLATION_SUBGROUP
, /* DESCRIPTIVE FLEXFIELD */ '_DF:BEN:BEN_PGM_F:PGM'
, /* CODES */ PGM.DPNT_DSGN_LVL_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_DSGN_LVL'
, PGM.DPNT_DSGN_LVL_CD)
, PGM.PGM_STAT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_STAT'
, PGM.PGM_STAT_CD)
, PGM.PGM_TYP_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_PGM_TYP'
, PGM.PGM_TYP_CD)
, PGM.DPNT_DSGN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_DSGN'
, PGM.DPNT_DSGN_CD)
, PGM.ACTY_REF_PERD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ACTY_REF_PERD'
, PGM.ACTY_REF_PERD_CD)
, PGM.ENRT_CVG_END_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_END'
, PGM.ENRT_CVG_END_DT_CD)
, PGM.DPNT_CVG_END_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_END'
, PGM.DPNT_CVG_END_DT_CD)
, PGM.DPNT_CVG_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_STRT'
, PGM.DPNT_CVG_STRT_DT_CD)
, PGM.ENRT_CVG_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_STRT'
, PGM.ENRT_CVG_STRT_DT_CD)
, PGM.ENRT_INFO_RT_FREQ_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_INFO_RT_FREQ'
, PGM.ENRT_INFO_RT_FREQ_CD)
, PGM.RT_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_STRT'
, PGM.RT_STRT_DT_CD)
, PGM.RT_END_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_END'
, PGM.RT_END_DT_CD)
, PGM.PGM_GRP_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_PGM_GRP'
, PGM.PGM_GRP_CD)
, PGM.ENRT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT'
, PGM.ENRT_CD)
, PGM.ENRT_MTHD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_MTHD'
, PGM.ENRT_MTHD_CD)
, PGM.POE_LVL_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_POE_LVL'
, PGM.POE_LVL_CD)
, PGM.VRFY_FMLY_MMBR_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_FMLY_MMBR'
, PGM.VRFY_FMLY_MMBR_CD)
, PGM.PER_CVRD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_PER_CVRD'
, PGM.PER_CVRD_CD)
, PGM.DFLT_STEP_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DFLT_STEP'
, PGM.DFLT_STEP_CD)
, PGM.UPDATE_SALARY_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_UPDATE_SALARY'
, PGM.UPDATE_SALARY_CD)
, PGM.USE_SCORES_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_USE_SCORES'
, PGM.USE_SCORES_CD)
, PGM.SCORES_CALC_MTHD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_SCORES_CALC_MTHD'
, PGM.SCORES_CALC_MTHD_CD)
, PGM.SALARY_CALC_MTHD_CD
, HR_BIS.BIS_DECODE_LOOKUP('PQH_GSP_SAL_CALC_METH'
, PGM.SALARY_CALC_MTHD_CD)
, PGM.DPNT_ADRS_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DPNT_ADRS_RQD_FLAG)
, PGM.PGM_PRVDS_NO_AUTO_ENRT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.PGM_PRVDS_NO_AUTO_ENRT_FLAG)
, PGM.DPNT_DOB_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DPNT_DOB_RQD_FLAG)
, PGM.PGM_PRVDS_NO_DFLT_ENRT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.PGM_PRVDS_NO_DFLT_ENRT_FLAG)
, PGM.DPNT_LEGV_ID_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DPNT_LEGV_ID_RQD_FLAG)
, PGM.ELIG_APLS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.ELIG_APLS_FLAG)
, PGM.USES_ALL_ASMTS_FOR_RTS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.USES_ALL_ASMTS_FOR_RTS_FLAG)
, PGM.PRTN_ELIG_OVRID_ALWD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.PRTN_ELIG_OVRID_ALWD_FLAG)
, PGM.PGM_USE_ALL_ASNTS_ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.PGM_USE_ALL_ASNTS_ELIG_FLAG)
, PGM.DPNT_DSGN_NO_CTFN_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DPNT_DSGN_NO_CTFN_RQD_FLAG)
, PGM.DRVBL_FCTR_DPNT_ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DRVBL_FCTR_DPNT_ELIG_FLAG)
, PGM.DRVBL_FCTR_PRTN_ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DRVBL_FCTR_PRTN_ELIG_FLAG)
, PGM.DRVBL_FCTR_APLS_RTS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DRVBL_FCTR_APLS_RTS_FLAG)
, PGM.TRK_INELIG_PER_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.TRK_INELIG_PER_FLAG)
, PGM.ALWS_UNRSTRCTD_ENRT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.ALWS_UNRSTRCTD_ENRT_FLAG)
, PGM.DFLT_PGM_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.DFLT_PGM_FLAG)
, PGM.USE_PROG_POINTS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.USE_PROG_POINTS_FLAG)
, PGM.USE_MULTI_PAY_RATES_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.USE_MULTI_PAY_RATES_FLAG)
, PGM.USE_VARIABLE_RATES_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.USE_VARIABLE_RATES_FLAG)
, PGM.GSP_ALLOW_OVERRIDE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PGM.GSP_ALLOW_OVERRIDE_FLAG)
, /* ID COLUMNS */ PGM.PGM_ID
, PGM.BUSINESS_GROUP_ID
, PGM.DFLT_ELEMENT_TYPE_ID
, PGM.DFLT_INPUT_VALUE_ID
, /* WHO COLUMNS */ PGM.LAST_UPDATE_DATE
, PGM.LAST_UPDATED_BY
, PGM.CREATED_BY
, PGM.CREATION_DATE
FROM BEN_PGM_F PGM
WHERE PGM.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PGM.BUSINESS_GROUP_ID) WITH READ ONLY