Product: | BEN - Advanced Benefits |
---|---|
Description: | This view identifies the details for a compensation program. A program is a way to group plans together which are typically offered as a package of benefits to a group of people. |
Implementation/DBA Data: | APPS.BENBV_PGM_V |
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