View: BENBV_PTIP_V

Product:	BEN - Advanced Benefits
Description:	This view identifies processing requirements for a plan type that is supported within a program.
Implementation/DBA Data:	APPS.BENBV_PTIP_V

View Text

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

Columns