VIEW: APPS.BENBV_PTIP_V

Source

View Text - Preformatted

SELECT CTP.EFFECTIVE_START_DATE, CTP.EFFECTIVE_END_DATE, CTP.DPNT_CVG_STRT_DT_RL, CTP.DPNT_CVG_END_DT_RL, CTP.POSTELCN_EDIT_RL, CTP.MX_CVG_ALWD_AMT, CTP.MN_ENRD_RQD_OVRID_NUM, CTP.MX_ENRD_ALWD_OVRID_NUM, CTP.ORDR_NUM, CTP.RQD_PERD_ENRT_NENRT_VAL, CTP.RQD_PERD_ENRT_NENRT_TM_UOM, HR_BIS.BIS_DECODE_LOOKUP('BEN_RQD_PERD_ENRT_NENRT_TM_UOM',CTP.RQD_PERD_ENRT_NENRT_TM_UOM), CTP.IVR_IDENT, CTP.RQD_PERD_ENRT_NENRT_RL, CTP.ENRT_CVG_STRT_DT_RL, CTP.ENRT_CVG_END_DT_RL, CTP.RT_STRT_DT_RL, CTP.RT_END_DT_RL, CTP.URL_REF_NAME, CTP.AUTO_ENRT_MTHD_RL, CTP.ENRT_RL, CTP.DFLT_ENRT_DET_RL, CTP.VRFY_FMLY_MMBR_RL, CTP.SHORT_CODE, CTP.SHORT_NAME, CTP.LEGISLATION_CODE, CTP.LEGISLATION_SUBGROUP, /* descriptive flexfield */ '_DF:BEN:BEN_PTIP_F:CTP', /* codes */ CTP.PTIP_STAT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_STAT',CTP.PTIP_STAT_CD), CTP.DPNT_DSGN_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_DSGN',CTP.DPNT_DSGN_CD), CTP.DPNT_CVG_STRT_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_STRT',CTP.DPNT_CVG_STRT_DT_CD), CTP.DPNT_CVG_END_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_END',CTP.DPNT_CVG_END_DT_CD), CTP.RQD_ENRT_PERD_TCO_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_RQD_ENRT_PERD_TCO',CTP.RQD_ENRT_PERD_TCO_CD), CTP.ENRT_CVG_STRT_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_STRT',CTP.ENRT_CVG_STRT_DT_CD), CTP.ENRT_CVG_END_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_END',CTP.ENRT_CVG_END_DT_CD), CTP.RT_STRT_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_STRT',CTP.RT_STRT_DT_CD), CTP.RT_END_DT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_END',CTP.RT_END_DT_CD), CTP.ENRT_MTHD_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_MTHD',CTP.ENRT_MTHD_CD), CTP.ENRT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT',CTP.ENRT_CD), CTP.DFLT_ENRT_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_DFLT_ENRT',CTP.DFLT_ENRT_CD), CTP.VRFY_FMLY_MMBR_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_FMLY_MMBR',CTP.VRFY_FMLY_MMBR_CD), CTP.PER_CVRD_CD, HR_BIS.BIS_DECODE_LOOKUP('BEN_PER_CVRD',CTP.PER_CVRD_CD), CTP.COORD_CVG_FOR_ALL_PLS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.COORD_CVG_FOR_ALL_PLS_FLAG), CTP.DPNT_CVG_NO_CTFN_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.DPNT_CVG_NO_CTFN_RQD_FLAG), CTP.DPNT_ADRS_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.DPNT_ADRS_RQD_FLAG), CTP.DPNT_LEGV_ID_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.DPNT_LEGV_ID_RQD_FLAG), CTP.DPNT_DOB_RQD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.DPNT_DOB_RQD_FLAG), CTP.CRS_THIS_PL_TYP_ONLY_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.CRS_THIS_PL_TYP_ONLY_FLAG), CTP.NO_MX_PL_TYP_OVRID_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.NO_MX_PL_TYP_OVRID_FLAG), CTP.PRVDS_CR_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.PRVDS_CR_FLAG), CTP.WVBL_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.WVBL_FLAG), CTP.DRVD_FCTR_DPNT_CVG_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.DRVD_FCTR_DPNT_CVG_FLAG), CTP.NO_MN_PL_TYP_OVERID_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.NO_MN_PL_TYP_OVERID_FLAG), CTP.SBJ_TO_SPS_LF_INS_MX_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.SBJ_TO_SPS_LF_INS_MX_FLAG), CTP.SBJ_TO_DPNT_LF_INS_MX_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.SBJ_TO_DPNT_LF_INS_MX_FLAG), CTP.USE_TO_SUM_EE_LF_INS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.USE_TO_SUM_EE_LF_INS_FLAG), CTP.DRVBL_FCTR_APLS_RTS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.DRVBL_FCTR_APLS_RTS_FLAG), CTP.DRVBL_FCTR_PRTN_ELIG_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.DRVBL_FCTR_PRTN_ELIG_FLAG), CTP.ELIG_APLS_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.ELIG_APLS_FLAG), CTP.PRTN_ELIG_OVRID_ALWD_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.PRTN_ELIG_OVRID_ALWD_FLAG), CTP.TRK_INELIG_PER_FLAG, HR_BIS.BIS_DECODE_LOOKUP('YES_NO',CTP.TRK_INELIG_PER_FLAG), /* id columns */ CTP.PTIP_ID, CTP.PL_TYP_ID, CTP.PGM_ID, CTP.BUSINESS_GROUP_ID, CTP.ACRS_PTIP_CVG_ID, CTP.CMBN_PTIP_OPT_ID, CTP.CMBN_PTIP_ID, /* who columns */ CTP.LAST_UPDATE_DATE, CTP.LAST_UPDATED_BY, CTP.CREATED_BY, CTP.CREATION_DATE FROM BEN_PTIP_F CTP WHERE CTP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, CTP.BUSINESS_GROUP_ID) WITH READ ONLY

View Text - HTML Formatted

SELECT CTP.EFFECTIVE_START_DATE
, CTP.EFFECTIVE_END_DATE
, CTP.DPNT_CVG_STRT_DT_RL
, CTP.DPNT_CVG_END_DT_RL
, CTP.POSTELCN_EDIT_RL
, CTP.MX_CVG_ALWD_AMT
, CTP.MN_ENRD_RQD_OVRID_NUM
, CTP.MX_ENRD_ALWD_OVRID_NUM
, CTP.ORDR_NUM
, CTP.RQD_PERD_ENRT_NENRT_VAL
, CTP.RQD_PERD_ENRT_NENRT_TM_UOM
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RQD_PERD_ENRT_NENRT_TM_UOM'
, CTP.RQD_PERD_ENRT_NENRT_TM_UOM)
, CTP.IVR_IDENT
, CTP.RQD_PERD_ENRT_NENRT_RL
, CTP.ENRT_CVG_STRT_DT_RL
, CTP.ENRT_CVG_END_DT_RL
, CTP.RT_STRT_DT_RL
, CTP.RT_END_DT_RL
, CTP.URL_REF_NAME
, CTP.AUTO_ENRT_MTHD_RL
, CTP.ENRT_RL
, CTP.DFLT_ENRT_DET_RL
, CTP.VRFY_FMLY_MMBR_RL
, CTP.SHORT_CODE
, CTP.SHORT_NAME
, CTP.LEGISLATION_CODE
, CTP.LEGISLATION_SUBGROUP
, /* DESCRIPTIVE FLEXFIELD */ '_DF:BEN:BEN_PTIP_F:CTP'
, /* CODES */ CTP.PTIP_STAT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_STAT'
, CTP.PTIP_STAT_CD)
, CTP.DPNT_DSGN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_DSGN'
, CTP.DPNT_DSGN_CD)
, CTP.DPNT_CVG_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_STRT'
, CTP.DPNT_CVG_STRT_DT_CD)
, CTP.DPNT_CVG_END_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DPNT_CVG_END'
, CTP.DPNT_CVG_END_DT_CD)
, CTP.RQD_ENRT_PERD_TCO_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RQD_ENRT_PERD_TCO'
, CTP.RQD_ENRT_PERD_TCO_CD)
, CTP.ENRT_CVG_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_STRT'
, CTP.ENRT_CVG_STRT_DT_CD)
, CTP.ENRT_CVG_END_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_CVG_END'
, CTP.ENRT_CVG_END_DT_CD)
, CTP.RT_STRT_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_STRT'
, CTP.RT_STRT_DT_CD)
, CTP.RT_END_DT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RT_END'
, CTP.RT_END_DT_CD)
, CTP.ENRT_MTHD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT_MTHD'
, CTP.ENRT_MTHD_CD)
, CTP.ENRT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_ENRT'
, CTP.ENRT_CD)
, CTP.DFLT_ENRT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DFLT_ENRT'
, CTP.DFLT_ENRT_CD)
, CTP.VRFY_FMLY_MMBR_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_FMLY_MMBR'
, CTP.VRFY_FMLY_MMBR_CD)
, CTP.PER_CVRD_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_PER_CVRD'
, CTP.PER_CVRD_CD)
, CTP.COORD_CVG_FOR_ALL_PLS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.COORD_CVG_FOR_ALL_PLS_FLAG)
, CTP.DPNT_CVG_NO_CTFN_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.DPNT_CVG_NO_CTFN_RQD_FLAG)
, CTP.DPNT_ADRS_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.DPNT_ADRS_RQD_FLAG)
, CTP.DPNT_LEGV_ID_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.DPNT_LEGV_ID_RQD_FLAG)
, CTP.DPNT_DOB_RQD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.DPNT_DOB_RQD_FLAG)
, CTP.CRS_THIS_PL_TYP_ONLY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.CRS_THIS_PL_TYP_ONLY_FLAG)
, CTP.NO_MX_PL_TYP_OVRID_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.NO_MX_PL_TYP_OVRID_FLAG)
, CTP.PRVDS_CR_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.PRVDS_CR_FLAG)
, CTP.WVBL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.WVBL_FLAG)
, CTP.DRVD_FCTR_DPNT_CVG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.DRVD_FCTR_DPNT_CVG_FLAG)
, CTP.NO_MN_PL_TYP_OVERID_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.NO_MN_PL_TYP_OVERID_FLAG)
, CTP.SBJ_TO_SPS_LF_INS_MX_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.SBJ_TO_SPS_LF_INS_MX_FLAG)
, CTP.SBJ_TO_DPNT_LF_INS_MX_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.SBJ_TO_DPNT_LF_INS_MX_FLAG)
, CTP.USE_TO_SUM_EE_LF_INS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.USE_TO_SUM_EE_LF_INS_FLAG)
, CTP.DRVBL_FCTR_APLS_RTS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.DRVBL_FCTR_APLS_RTS_FLAG)
, CTP.DRVBL_FCTR_PRTN_ELIG_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.DRVBL_FCTR_PRTN_ELIG_FLAG)
, CTP.ELIG_APLS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.ELIG_APLS_FLAG)
, CTP.PRTN_ELIG_OVRID_ALWD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.PRTN_ELIG_OVRID_ALWD_FLAG)
, CTP.TRK_INELIG_PER_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, CTP.TRK_INELIG_PER_FLAG)
, /* ID COLUMNS */ CTP.PTIP_ID
, CTP.PL_TYP_ID
, CTP.PGM_ID
, CTP.BUSINESS_GROUP_ID
, CTP.ACRS_PTIP_CVG_ID
, CTP.CMBN_PTIP_OPT_ID
, CTP.CMBN_PTIP_ID
, /* WHO COLUMNS */ CTP.LAST_UPDATE_DATE
, CTP.LAST_UPDATED_BY
, CTP.CREATED_BY
, CTP.CREATION_DATE
FROM BEN_PTIP_F CTP
WHERE CTP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, CTP.BUSINESS_GROUP_ID) WITH READ ONLY