Clarity

Hi Everyone!!!

I've been trying to do a portlet that brings me the information of the planed budget cost (total cost, cost to date and actuals) for every project. It would look like this

PROJECT    TOTAL PLANED COST    PLANED COST TO DATE    ACTUAL COST
-------    -----------------    --------------------    ----------
Project 1       100.000                 50.000            45.000
Project 2       200.000                 70.000            60.000

It seemed Quite simple because it should be a simple query on the details of the financial plans. However I've stumbled with a roadblock :(

I know that this budget information is on the tables:

 fin_cost_plan_details
 fin_plans
 ppa_wip

I can extract the total planed cost and its actuals, however, the problem lies with the planed cost to date. The detailed cost for a budget plan is on the [color=#FD0303]cost[color] column on the [color=#FF0000]fin_cost_plan_details[color] table. The problem with this table is that it holds a record for every transclass, but the cost column contains all the sliced information on a binary format.

Is there a way to extract (with a SQL query in order to use it with a portlet) the sliced information in a readable format in order to query it?. I knew that on previous versions, there was a table called pac_frc_forecast_values wich holded this detailed info, but this table is deprecated on Clarity v12.

Thanks for your support, kind regards

Rafa Larios

Hi Rafa,

Use these -
odf_ssl_cst_dtl_cost cst
fin_cost_plan_details dtl
fin_plans fin

Join conditions

cst.prj_object_id = dtl.id and
dtl.plan_id = fin.id and
fin.is_plan_of_record = 1 and
fin.object_id = <project_id> and
fin.plan_type_code = 'BUDGET'

Sliced Financial information is available under tables prefixed with "ODF_SSL_".

Hope this helps..

~D

Hello DKanthe

Thanks for your response. However it didn't work. I Tried to extract said information, but it didn't matched the information on the Budget plan and on the query.

The query I made on the Server itself was:

SELECT p.NAME, fin.TOTAL_COST, cst.*
FROM INV_INVESTMENTS p


left join fin_plans fin



left join fin_cost_plan_details dtl




LEFT JOIN ODF_SSL_CST_DTL_COST cst




on cst.prj_object_id = dtl.id 



on dtl.PLAN_ID = fin.id


on fin.OBJECT_ID = p.ID
WHERE
fin.plan_type_code = 'BUDGET' and
fin.is_plan_of_record = 1 and
p.CODE = <P.CODE>

The budget plan has 3 values, 1000, 5000 and 30000, however the information on the query extract 3 totally different values (161.290323, 322.580645, 1000.000000).

What could be the problem. It is a slices problem?.

Kind regards

Rafa

Hello DKanthe

I started to play with the numbers the query gave me, and it turns out that the information I needed was there all along. The "weird" slice values I was getting resulted from this:

SliceValue = Cost/DaysOnSlice 

So, in order to get the value, my query was:

SELECT p.NAME, fin.TOTAL_COST, (cst.slice*convert(bigint,(cst.finish_date - cst.start_date))) as CostValue
FROM INV_INVESTMENTS p


left join fin_plans fin



left join fin_cost_plan_details dtl




LEFT JOIN niku.ODF_SSL_CST_DTL_COST cst




on cst.prj_object_id = dtl.id 



on dtl.PLAN_ID = fin.id


on fin.OBJECT_ID = p.ID
WHERE
fin.plan_type_code = 'BUDGET' and
fin.is_plan_of_record = 1 and
p.CODE= <Project Code>

At the end, what i Needed was extracted using:

Cost = SliceValue*DaysOnSlice

Thank you very much MR DKanthe!!!, Kind regards

Rafa

Glad to know your problem is solved....! :)

~D