eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.BEN_CWB_SUMM_DIR_REP_V

Source

View Text - Preformatted


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, 1)) Pct_Emp_Recv, round(Smry.Elig_Sal, planCurr.precision) Elig_Sal, round(decode(Seq, 1, Smry.ActMgrBdgt, Smry.Bdgt), planCurr.precision) Budget, decode(Smry.Elig_Sal, 0, null, round((decode(Seq, 1, Smry.ActMgrBdgt, Smry.Bdgt) / Smry.Elig_Sal) * 100, ben_cwb_utils.get_bdgt_pct_of_elig_sal_decs())) Pct_Of_Bdgt, round(Smry.Tot_Alct, planCurr.precision) Tot_Alct, Smry.Pl_Uom Pl_Uom, decode(Smry.Elig_Sal, 0, null, round((Smry.Tot_Alct/ Smry.Elig_Sal) * 100, ben_cwb_utils.get_alloc_pct_of_elig_sal_decs())) Pct_Of_Sal, round((decode(Seq, 1, Smry.ActMgrBdgt, Smry.Bdgt) - nvl(Smry.Tot_Alct, 0)), planCurr.precision) Available, decode(Smry.Emp_Recv, 0, null, round((Smry.Tot_Alct/ Smry.Emp_Recv), planCurr.precision)) Avg_Alct FROM ben_cwb_person_info People, (SELECT decode(max(Hrchy.lvl_num), 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(Pl.Pl_Uom) Pl_Uom_format, 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 ,fnd_currencies planCurr WHERE People.group_per_in_ler_id = Smry.Grp_Ler_Id and planCurr.currency_code = Smry.Pl_Uom_format ORDER BY Seq, Smry.Grp_Ler_Id

View Text - HTML Formatted

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
, 1)) PCT_EMP_RECV
, ROUND(SMRY.ELIG_SAL
, PLANCURR.PRECISION) ELIG_SAL
, ROUND(DECODE(SEQ
, 1
, SMRY.ACTMGRBDGT
, SMRY.BDGT)
, PLANCURR.PRECISION) BUDGET
, DECODE(SMRY.ELIG_SAL
, 0
, NULL
, ROUND((DECODE(SEQ
, 1
, SMRY.ACTMGRBDGT
, SMRY.BDGT) / SMRY.ELIG_SAL) * 100
, BEN_CWB_UTILS.GET_BDGT_PCT_OF_ELIG_SAL_DECS())) PCT_OF_BDGT
, ROUND(SMRY.TOT_ALCT
, PLANCURR.PRECISION) TOT_ALCT
, SMRY.PL_UOM PL_UOM
, DECODE(SMRY.ELIG_SAL
, 0
, NULL
, ROUND((SMRY.TOT_ALCT/ SMRY.ELIG_SAL) * 100
, BEN_CWB_UTILS.GET_ALLOC_PCT_OF_ELIG_SAL_DECS())) PCT_OF_SAL
, ROUND((DECODE(SEQ
, 1
, SMRY.ACTMGRBDGT
, SMRY.BDGT) - NVL(SMRY.TOT_ALCT
, 0))
, PLANCURR.PRECISION) AVAILABLE
, DECODE(SMRY.EMP_RECV
, 0
, NULL
, ROUND((SMRY.TOT_ALCT/ SMRY.EMP_RECV)
, PLANCURR.PRECISION)) AVG_ALCT
FROM BEN_CWB_PERSON_INFO PEOPLE
, (SELECT DECODE(MAX(HRCHY.LVL_NUM)
, 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(PL.PL_UOM) PL_UOM_FORMAT
, 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
, FND_CURRENCIES PLANCURR
WHERE PEOPLE.GROUP_PER_IN_LER_ID = SMRY.GRP_LER_ID
AND PLANCURR.CURRENCY_CODE = SMRY.PL_UOM_FORMAT ORDER BY SEQ
, SMRY.GRP_LER_ID