AnsweredAssumed Answered

More forecast details/planing units SQL woes

Question asked by matpj on Apr 3, 2009
Latest reply on May 1, 2009 by matpj
Hi all, I am using this script to compare the budget units from the forecast tables, with the value rolled up at project level in the NBI_PROJECT_CURRENT_FACTS table. There are several projects (i'm talking a LOT) where the NBI table shows the correct value accoring to the 'planning' screen, but the forecast details values come back as a lot more.Has anyone else experienced the problem? this is my SQL. SELECT nbi.PROJECT_CODE, nbi.budget_hours, sum(FD.UNITS) as budget
FROM
NIKU.NBI_PROJECT_CURRENT_FACTS nbi,
NIKU.PAC_MNT_PROJECTS MP,
NIKU.ODF_CA_PROJECT cst,
NIKU.PAC_FRC_FORECAST_PROPERTIES FP,
NIKU.PAC_FRC_FORECAST_DETAILS FD,
NIKU.PRCHARGECODE C,
niku.PAC_FRC_FORECAST_VALUES FV

WHERE
nbi.project_code = MP.project_code AND
cst.ID=nbi.PROJECT_ID AND
FP.PROJECT_ID=MP.ID AND
FP.ID=FD.FORECAST_ID AND
FD.DETAIL_ID=C.PRID AND
FD.ID=FV.FORECAST_DETAILS_ID AND
FP.USE_DETAIL_TYPE = 2 AND
FV.CURRENCY_TYPE = 'HOME' AND
MP.APPROVED = 1 AND
MP.STATUS 'C' AND
FP.REVISION = (SELECT max(revision) from niku.pac_frc_forecast_properties x where x.status = 2 AND x.project_id = fp.project_id)

group by
nbi.PROJECT_CODE, nbi.budget_hours

having sum(fd.units) nbi.budget_hours   can anybody see anything wrong with this? It is returning the latest revision on open, approved budgets.Thanks in advance,Matt

Outcomes