View: BEN_CWB_SUMM_DIR_REP_V

Product:	BEN - Advanced Benefits
Description:	View to download the Summary by Direct Report using webadi
Implementation/DBA Data:	APPS.BEN_CWB_SUMM_DIR_REP_V

View Text

SELECT SMRY.MGR_GRP_LER_ID GRP_LER_ID
, SMRY.GRP_PL_ID GRP_PL_ID
, SMRY.GRP_OIPL_ID GRP_OIPL_ID
, DECODE(FND_PROFILE.VALUE('BEN_CWB_PROFILE_DISP_NAME')
, 'FN'
, PEOPLE.FULL_NAME
, 'CN'
, PEOPLE.CUSTOM_NAME
, PEOPLE.BRIEF_NAME) NAME
, SMRY.ELIG_EMP ELIG_EMP
, SMRY.EMP_RECV EMP_RECV
, DECODE(SMRY.ELIG_EMP
, 0
, NULL
, ROUND((SMRY.EMP_RECV/ SMRY.ELIG_EMP) * 100
, 4)) PCT_EMP_RECV
, SMRY.ELIG_SAL ELIG_SAL
, DECODE(SEQ
, 1
, SMRY.ACTMGRBDGT
, SMRY.BDGT) BUDGET
, DECODE(SMRY.ELIG_SAL
, 0
, NULL
, ROUND((DECODE(SEQ
, 1
, SMRY.ACTMGRBDGT
, SMRY.BDGT) / SMRY.ELIG_SAL) * 100
, 4)) PCT_OF_BDGT
, SMRY.TOT_ALCT TOT_ALCT
, SMRY.PL_UOM PL_UOM
, DECODE(SMRY.ELIG_SAL
, 0
, NULL
, ROUND((SMRY.TOT_ALCT/ SMRY.ELIG_SAL) * 100
, 4)) PCT_OF_SAL
, (DECODE(SEQ
, 1
, SMRY.ACTMGRBDGT
, SMRY.BDGT) - NVL(SMRY.TOT_ALCT
, 0)) AVAILABLE
, DECODE(SMRY.EMP_RECV
, 0
, NULL
, ROUND((SMRY.TOT_ALCT/ SMRY.EMP_RECV)
, 2)) AVG_ALCT
FROM BEN_CWB_PERSON_INFO PEOPLE
, (SELECT DECODE(MAX(HRCHY.LVL_NUM)
, 0
, DECODE(MAX(GRP.BDGT_POP_CD)
, NULL
, 0
, 1)
, 2) SEQ
, HRCHY.MGR_PER_IN_LER_ID MGR_GRP_LER_ID
, SUMM.GROUP_PER_IN_LER_ID GRP_LER_ID
, SUMM.GROUP_PL_ID GRP_PL_ID
, SUMM.GROUP_OIPL_ID GRP_OIPL_ID
, MAX(NVL(NNMNTRY.MEANING
, PL.PL_UOM)) PL_UOM
, DECODE(MAX(HRCHY.LVL_NUM)
, 0
, SUM(SUMM.ELIG_COUNT_DIRECT)
, SUM(SUMM.ELIG_COUNT_ALL)) ELIG_EMP
, DECODE(MAX(HRCHY.LVL_NUM)
, 0
, SUM(SUMM.EMP_RECV_COUNT_DIRECT)
, SUM(SUMM.EMP_RECV_COUNT_ALL)) EMP_RECV
, DECODE(MAX(HRCHY.LVL_NUM)
, 0
, SUM(SUMM.ELIG_SAL_VAL_DIRECT)
, SUM(SUMM.ELIG_SAL_VAL_ALL)) ELIG_SAL
, DECODE(MAX(HRCHY.LVL_NUM)
, 0
, SUM(SUMM.WS_VAL_DIRECT)
, SUM(SUMM.WS_VAL_ALL)) TOT_ALCT
, NVL(DECODE(MAX(HRCHY.LVL_NUM)
, 0
, (DECODE(MAX(PL.PRSRV_CD)
, 'P'
, SUM(SUMM.ELIG_SAL_VAL_DIRECT)/100
, 1) * MAX(GRP.WS_BDGT_ISS_VAL)))
, 0) ACTMGRBDGT
, NVL(DECODE(MAX(GRP.DIST_BDGT_ISS_VAL)
, NULL
, (NVL(SUM(SUMM.WS_BDGT_ISS_VAL_ALL)
, 0) + (DECODE(MAX(PL.PRSRV_CD)
, 'P'
, SUM(SUMM.ELIG_SAL_VAL_DIRECT)/100
, 1) * MAX(GRP.WS_BDGT_ISS_VAL)))
, (DECODE(MAX(PL.PRSRV_CD)
, 'P'
, SUM(SUMM.ELIG_SAL_VAL_ALL)/100
, 1) * MAX(GRP.DIST_BDGT_ISS_VAL)))
, 0) BDGT
, NVL(MAX(GRP.RSRV_VAL)
, 0) RSRV
FROM BEN_CWB_SUMMARY SUMM
, BEN_CWB_PERSON_GROUPS GRP
, BEN_CWB_GROUP_HRCHY HRCHY
, HR_LOOKUPS NNMNTRY
, (SELECT PL.GROUP_PL_ID PL_ID
, PL.GROUP_OIPL_ID OIPL_ID
, PL.LF_EVT_OCRD_DT LF_EVT_DT
, MAX(PL.PRSRV_BDGT_CD) PRSRV_CD
, MAX(PL.WS_NNMNTRY_UOM) NNMNTRY_UOM
, MAX(GRP_PL.PL_UOM) PL_UOM
FROM BEN_CWB_PL_DSGN PL
, BEN_CWB_PL_DSGN GRP_PL
WHERE PL.ACTUAL_FLAG = 'Y'
AND NVL(PL.OPT_HIDDEN_FLAG
, 'N') = 'N'
AND GRP_PL.GROUP_PL_ID = PL.GROUP_PL_ID
AND GRP_PL.LF_EVT_OCRD_DT = PL.LF_EVT_OCRD_DT
AND GRP_PL.GROUP_PL_ID = GRP_PL.PL_ID
AND GRP_PL.GROUP_OIPL_ID = -1 GROUP BY PL.GROUP_PL_ID
, PL.GROUP_OIPL_ID
, PL.LF_EVT_OCRD_DT) PL
WHERE HRCHY.LVL_NUM <= 1
AND SUMM.GROUP_PER_IN_LER_ID = HRCHY.EMP_PER_IN_LER_ID
AND SUMM.GROUP_PL_ID = PL.PL_ID
AND SUMM.GROUP_OIPL_ID = PL.OIPL_ID
AND SUMM.LF_EVT_OCRD_DT = PL.LF_EVT_DT
AND GRP.GROUP_PER_IN_LER_ID = SUMM.GROUP_PER_IN_LER_ID
AND GRP.GROUP_PL_ID = SUMM.GROUP_PL_ID
AND GRP.GROUP_OIPL_ID = SUMM.GROUP_OIPL_ID
AND GRP.LF_EVT_OCRD_DT = SUMM.LF_EVT_OCRD_DT
AND NNMNTRY.LOOKUP_CODE (+) = PL.NNMNTRY_UOM
AND NNMNTRY.LOOKUP_TYPE (+) = 'BEN_NNMNTRY_UOM' GROUP BY HRCHY.MGR_PER_IN_LER_ID
, SUMM.GROUP_PER_IN_LER_ID
, SUMM.GROUP_PL_ID
, SUMM.GROUP_OIPL_ID) SMRY
WHERE PEOPLE.GROUP_PER_IN_LER_ID = SMRY.GRP_LER_ID ORDER BY SEQ
, SMRY.GRP_LER_ID

Columns