Cost SQL

[Legacy User]

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.

 

Thanks

 

Ben

[navzjoshi00]

Check this -

SQL Script on Project Cost Plan Details

https://communities.ca.com/message/241700100#241700100

 

NJ

[Legacy User]

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.

[Legacy User]

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 Actuals

Total Approved Budget.

 

Any help will be greatly appreciated.

[Anon Anon]

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 @SELECT:DIM:USER_DEF:IMPLIED:XX:inv_id:inv_id@

,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XX:inv_code:inv_code@

,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XX:inv_name:inv_name@

,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XX:planned_cost:planned_cost@

,@SELECT:DIM_PROP:USER_DEF:IMPLIED:XX:actual_cost:actual_cost@

FROM

( 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

) a

WHERE @FILTER@

 

 

You could try using that as a start/guide?