View: BENBV_BNFT_PRVDR_POOL_V

Product:	BEN - Advanced Benefits
Description:	This view specifies the pools or groups of compensation objects that may provide credits or additional funds to pay for participant benefits.
Implementation/DBA Data:	APPS.BENBV_BNFT_PRVDR_POOL_V

View Text

SELECT BPP.EFFECTIVE_START_DATE
, BPP.EFFECTIVE_END_DATE
, BPP.NAME
, BPP.PCT_RNDG_RL
, BPP.VAL_RNDG_RL
, BPP.DFLT_EXCS_TRTMT_RL
, BPP.MN_DSTRBL_PCT_NUM
, BPP.MN_DSTRBL_VAL
, BPP.MX_DSTRBL_PCT_NUM
, BPP.MX_DSTRBL_VAL
, BPP.MX_DFCIT_PCT_POOL_CRS_NUM
, BPP.MX_DFCIT_PCT_COMP_NUM
, CASE WHEN (BPP.OIPLIP_ID IS NOT NULL) THEN BEN_BIS_UTILS.GET_OIPLIP_NAME(BPP.OIPLIP_ID
, BPP.BUSINESS_GROUP_ID
, BPP.EFFECTIVE_START_DATE) WHEN (BPP.PLIP_ID IS NOT NULL) THEN BEN_BIS_UTILS.GET_PLIP_NAME(BPP.PLIP_ID
, BPP.BUSINESS_GROUP_ID
, BPP.EFFECTIVE_START_DATE) WHEN (BPP.PTIP_ID IS NOT NULL) THEN BEN_BIS_UTILS.GET_PTIP_NAME(BPP.PTIP_ID
, BPP.BUSINESS_GROUP_ID
, BPP.EFFECTIVE_START_DATE) WHEN (BPP.CMBN_PLIP_ID IS NOT NULL) THEN BEN_BIS_UTILS.GET_CMBN_PLIP_NAME(BPP.CMBN_PLIP_ID
, BPP.BUSINESS_GROUP_ID
, BPP.EFFECTIVE_START_DATE) WHEN (BPP.CMBN_PTIP_ID IS NOT NULL) THEN BEN_BIS_UTILS.GET_CMBN_PTIP_NAME(BPP.CMBN_PTIP_ID
, BPP.BUSINESS_GROUP_ID
, BPP.EFFECTIVE_START_DATE) WHEN (BPP.CMBN_PTIP_OPT_ID IS NOT NULL) THEN BEN_BIS_UTILS.GET_CMBN_PTIP_OPT_NAME(BPP.CMBN_PTIP_OPT_ID
, BPP.BUSINESS_GROUP_ID
, BPP.EFFECTIVE_START_DATE) ELSE NULL END COMP_OBJECT
, /* DESCRIPTIVE FLEXFIELD */ '_DF:BEN:BEN_BNFT_PRVDR_POOL_F:BPP'
, /* CODES */ BPP.PCT_RNDG_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RNDG'
, BPP.PCT_RNDG_CD)
, BPP.VAL_RNDG_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RNDG'
, BPP.VAL_RNDG_CD)
, BPP.DFLT_EXCS_TRTMT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_DFLT_EXCS_TRTMT'
, BPP.DFLT_EXCS_TRTMT_CD)
, BPP.RLOVR_RSTRCN_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_RLOVR_RSTRN'
, BPP.RLOVR_RSTRCN_CD)
, BPP.EXCS_TRTMT_CD
, HR_BIS.BIS_DECODE_LOOKUP('BEN_EXCS_TRTMT'
, BPP.EXCS_TRTMT_CD)
, BPP.PGM_POOL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.PGM_POOL_FLAG)
, BPP.EXCS_ALWYS_FFTD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.EXCS_ALWYS_FFTD_FLAG)
, BPP.USE_FOR_PGM_POOL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.USE_FOR_PGM_POOL_FLAG)
, BPP.NO_MN_DSTRBL_PCT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.NO_MN_DSTRBL_PCT_FLAG)
, BPP.NO_MN_DSTRBL_VAL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.NO_MN_DSTRBL_VAL_FLAG)
, BPP.NO_MX_DSTRBL_PCT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.NO_MX_DSTRBL_PCT_FLAG)
, BPP.NO_MX_DSTRBL_VAL_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.NO_MX_DSTRBL_VAL_FLAG)
, BPP.AUTO_ALCT_EXCS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.AUTO_ALCT_EXCS_FLAG)
, BPP.ALWS_NGTV_CRS_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.ALWS_NGTV_CRS_FLAG)
, BPP.USES_NET_CRS_MTHD_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BPP.USES_NET_CRS_MTHD_FLAG)
, /* ID COLUMNS */ BPP.BNFT_PRVDR_POOL_ID
, BPP.BUSINESS_GROUP_ID
, BPP.PGM_ID
, BPP.CMBN_PTIP_ID
, BPP.CMBN_PTIP_OPT_ID
, BPP.OIPLIP_ID
, BPP.CMBN_PLIP_ID
, BPP.PLIP_ID
, BPP.PTIP_ID
, BPP.COMP_LVL_FCTR_ID
, /* WHO COLUMNS */ BPP.LAST_UPDATE_DATE
, BPP.LAST_UPDATED_BY
, BPP.CREATED_BY
, BPP.CREATION_DATE
FROM BEN_BNFT_PRVDR_POOL_F BPP
WHERE BPP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, BPP.BUSINESS_GROUP_ID) WITH READ ONLY

Columns