VIEW: APPS.BENBV_BNFT_PRVDR_POOL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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