Does anyone have sql that details a project's costs?
I need to list a projects' total non labour actuals and forecasts broken down by transtype against the plan of record cost plan.
I also need a projects total approved budget but not sure where to start.
Check this -
SQL Script on Project Cost Plan Details
I've tried a few of the pieces of sql but no joy. SHould i use the sql more as a guide? I don't need to break down by quarter etc. just totals.
I haven't got very far with this - i don't want the costs broken down by period or quarter for example. I just need the information as a total for the project. This is what I'm after if anyone has it:
Total Labour Actuals
Total Approved Budget.
Any help will be greatly appreciated.
I think I was looking at something like this recently, but not broken down by transtype at all.
Info that cracked what i was after came from this thread ; RE: Project - Financial Plans - Budget - Actual field is mapped to which t
and I ended up with NSQL like this;
( SELECT p.id inv_id
, p.code inv_code
, p.name inv_name
, fin.TOTAL_COST as planned_cost
, ( select SUM(WV.TOTALCOST) totalcost
FROM PPA_WIP W
INNER JOIN PPA_WIP_VALUES WV ON W.TRANSNO = WV.TRANSNO AND WV.CURRENCY_TYPE = 'HOME'
INNER JOIN INV_INVESTMENTS II ON W.INVESTMENT_ID = II.ID
INNER JOIN FIN_PLANS FP ON FP.PLAN_TYPE_CODE = 'BUDGET' AND FP.OBJECT_ID = II.ID AND FP.IS_PLAN_OF_RECORD = 1
INNER JOIN BIZ_COM_PERIODS SP ON FP.START_PERIOD_ID = SP.ID
INNER JOIN BIZ_COM_PERIODS EP ON FP.END_PERIOD_ID = EP.ID
WHERE W.STATUS = 0
AND W.INVESTMENT_ID = P.id
AND W.TRANSDATE >= SP.START_DATE
AND W.TRANSDATE < EP.END_DATE
) AS actual_cost
FROM INV_INVESTMENTS p
LEFT JOIN fin_plans fin ON fin.OBJECT_ID = p.ID
WHERE fin.plan_type_code = 'BUDGET'
AND fin.is_plan_of_record = 1
You could try using that as a start/guide?