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 )
I just checked the V13.1 Tech ref, and this column is marked as "Unused, Obsolete" -
Same in 13.2 -
Same in 13.3 -
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
Ahh thats no good.
So how do we calculate "Total Cost" in SQL then?
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
This would be strange, as the screenshot seems to be from V13, and this column is marked as "Unused, Obsolete"
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... )
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
And if I look at the tech kb -
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.
Found this -
Document ID: TEC520157 Last Modified Date: 2/5/2013 Show Technical Document Details
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: 10Cost: $100Units for Actuals: 5Cost 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: 10Cost: $100Aggregate Actual Units: 35Aggregate Actual Cost: $350
Where do these numbers come from?
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.
The link is -
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).
This is as far as I could go -
SQL to bring back Costs and Actual Costs