Clarity

  • Private Community
 View Only
Back to discussions
Expand all | Collapse all

A project's (investment's) total cost?

navzjoshi00

navzjoshi00Jan 30, 2015 09:57 AM

Aaaaahhhhh..... At last NJ

  • 1.  A project's (investment's) total cost?

    Posted Jan 30, 2015 04:32 AM

    Is this just the value in inv_investments.AGGR_ACTUAL_COST ?

     

    i.e. that number is the same as you would see in the Cost Plan for the investment?  (and is this one of the fields populated by that "Investment Allocation" job?)

     

    If the answer is "no" - how do I (in SQL) calculate a project's total cost from the cost plan (where it is shown correctly) ?

     

    --

     

    (I don't have access to a financially-activated instance to look at real-data or run traces for myself just now )



  • 2.  Re: A project's (investment's) total cost?



  • 3.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 04:57 AM

    Ahh thats no good.

     

    So how do we calculate "Total Cost" in SQL then?



  • 4.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 05:00 AM

    For the Costs from the WIP transactions, you can check this tip from (Liz Williamson) wilel06

    CA PPM Tuesday Tip: Calculated Attribute for Actual Cost

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

     

    This would be strange, as the screenshot seems to be from V13, and this column is marked as "Unused, Obsolete"

     

    NJ



  • 5.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 05:04 AM

    Right, but surely that "Tip" is saying its just the AGGR_ACTUAL_COST?


    (unless the object's AGGR_ACTUAL_COST is not the inv_investments field, but a derived one... )



  • 6.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 05:07 AM

    Agree

     

    NJ



  • 7.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 05:11 AM

    One thing has to be sure that the Actual Costs from the cost plans would come from the ppa_wip and ppa_wip_values tables.

     

    Please correct me if I'm wrong

     

    NJ



  • 8.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 05:13 AM

    And if I look at the tech kb -

     

     

    What is the Investment Allocation job for?

    Document ID:  TEC525155
    Last Modified Date:  4/4/2013
      Show Technical Document Details

     

    Investment Allocation Job
    This job updates the ACTSUM, ETCSUM,BACESUM and EACSUM fields for investments based on the resources allocated to the investment.

    It calculates the sum of the Estimate To Complete values for all the investment's assignments and stores the value in the ETCSUM field.
    It calculates the sum of the Estimate At Completion (Total Effort) values for all the investment's assignments and stores the value in the EACSUM field.
    It calculates the sum of the Actual values for all the investment's assignments and stores the value in the ACTSUM field.
    It calculates the sum of the Baseline values for all the investment's assignments and stores the value in the BACESUM field.

    This job may influence data or performance in Demand Management.

    You can run this job immediately or on a scheduled basis.

    1. What table is the Investment Allocations job populating and using which slice requests?

      inv_investments.labor_actsum
      inv_investments.labor_etcsum
      inv_investments.labor_eacsum
      inv_investments.labor_basesum


    2. Does the job process all investments or only ones that have been updated?

      The job processes only active investments, starting Clarity 12.1.0 as fix for defect CLRT-46880

     

    NJ



  • 9.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 05:17 AM

    Found this -

     

     

    Where Do Units, Cost, Aggregate Actual Units and Aggregate Actual Cost on Cost Plan Aggregated View Come From?

    Document ID:  TEC520157
    Last Modified Date:  2/5/2013
      Show Technical Document Details

     

    Description:

    I have a project "My Master Project" that has a cost plan, the cost plan has the these values correctly displayed on the Flat view and Self View for the month of January 2010:

    Units: 10
    Cost: $100
    Units for Actuals: 5
    Cost for Actuals: $50

    This project has a sub project "My Sub Project" that does not have any cost plan but a manually entered amount of $100 for Planned Cost on the Simple Budget Page. This project also has a voucher processed with quantity of 30, with total cost of $300, for the month of January 2010.

    Qustion: When I open the Aggregated View for the cost plan on "My Master Project", I see the following for the month of January 2010:

    Unit: 10
    Cost: $100
    Aggregate Actual Units: 35
    Aggregate Actual Cost: $350

    Where do these numbers come from?

    Solution:

    Planned data such as Units and Cost come from the Cost Plan of Record* of the master project and the Cost Plan of Record of each of its subprojects. A Cost Plan of Record must exist on a sub project for its planned data to be included on master project's Cost Plan Aggregated View. "My Master Project" is displaying Units and Cost from its own Cost Plan of Record, since there is no Plan of Record from "My Sub Project"; the $100 Planned Cost amount manually entered on the Simple Budget Page is not used because it is not in a Cost Plan of Record.

    Actual data such as Units for Actuals and Cost for Actuals come from the Financial Transactions of both the master project and its subprojects. Financial Transactions are stored in a separate set of tables commonly known as the WIP tables, these actuals are displayed in financial plans automatically to present cost that already incurred. "My Master Project" is displaying the sum of $50 actuals from itself, and $300 actuals from "My Sub Projects" by pulling data from the financial tables.

    * a Cost Plan of Record is the cost plan on a project that has the checkmark for 'Plan of Record'. Each project will have only one Plan of Record at any given time. The first cost plan created for a project is automatically marked as the project's Plan of Record.

     

    NJ



  • 10.  Re: A project's (investment's) total cost?



  • 11.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 05:32 AM

    yeah thats sort of where I came in ; what I want to do is, for a given project show the "Total Cost" (this is the value on the Cost Plan) -> how is that derived? (in SQL).



  • 12.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 06:02 AM

    This is as far as I could go -

    SQL to bring back Costs and Actual Costs

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

     

    NJ



  • 13.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 06:03 AM

     

     

    NJ



  • 14.  Re: A project's (investment's) total cost?
    Best Answer

    Posted Jan 30, 2015 09:43 AM

    Ok, the answer was here ; https://communities.ca.com/message/101243118#101243118

     

    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 = 5006057--!!!!change for your project id

    AND W.TRANSDATE >= SP.START_DATE

    AND W.TRANSDATE < EP.END_DATE



  • 15.  Re: A project's (investment's) total cost?

    Posted Jan 30, 2015 09:57 AM
    Aaaaahhhhh..... At last


    NJ