Clarity PPM1

Expand all | Collapse all

Cost SQL

  • 1.  Cost SQL

    Posted 03-24-2015 08:17 AM

    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



  • 2.  Re: Cost SQL

    Posted 03-24-2015 08:29 AM

    Check this -

    SQL Script on Project Cost Plan Details

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

     

    NJ



  • 3.  Re: Cost SQL

    Posted 03-24-2015 09:40 AM

    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.



  • 4.  Re: Cost SQL

    Posted 03-26-2015 10:23 AM

    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.



  • 5.  Re: Cost SQL

    Posted 03-26-2015 12:07 PM

    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?