In the Task we have Actual cost column , which shows the product of rate and hours posted.
I have seen PRTASK and PRSASSIGNMENT table and in PRASSIGNMENT the column ACTCOST_SUM stores the Hours posted, but not the amount.
Question 1:- So which table i need to go with ? Should I choose the Rate matrix
Questions 2:- So the Actual cost column in the task which we see in application, fetches the rate from Rate matrix or from WIP ?
If I have understood your question correctly, I think you can pick the actual_cost/total_cost from ppa_wip_values and group by task id.
I was incomplete in the question. I'm not supposed to use WIP tables, I have found NBI_PROJ_RES_RATES_AND_COSTS table, but when I check for a project,task and resource filtered with 5 million ID the rate is different.Any Data mart Job needs to run ? what other table do we have ?
You can execute the " Rate Matrix Extraction" job to populate the NBI_PROJ_RES_RATES_AND_COSTS table. You can pick the rate from this table.
I think you may need Actual by Post TimeSheet and ACWP cost.
PRASSIGNMENT table has PRACTSUM column and ACTCOST_SUM column.
If you have post 2 hours by TimeSheet, PRACTSUM column value will be 2*3600(seconds) = 7200, and ACTCOST_SUM value will be ACWP cost.
You can sum up these values with group by task id and you will get amount of Actual Cost for Task.
If you run [Update Earned Value and Cost Totals] job or [Task list]->Actions->Update Cost Totals, you will see ACWP in Task List.