Monthly Actual Cost values from Cost Plan tables

[mailtojustine]

Hi,

 

I need to get the actual cost values (month wise) of cost plan from niku financial tables. Can anyone help me to get these actual cost values?

 

Thanks,

Justine

[mailtojustine]

I wrote query in ODF_SSL_CST_DTL_COST table, and I am getting the Cost values. But I need to get Actual Cost values. For that I checked in some slice tables like ODF_SSL_CST_DTL_ACOST, but there are no records in those tables.

[Allen Bollinger]

Try something like this. Posted Actuals come from ppa_wip table.

 

SELECT * FROM
(
SELECT PPA.INVESTMENT_ID PPAINVESTMENT, VAL.STDCOST STD, PPA.QUANTITY QTY, CAL.YEAR_KEY YEAR, CAL.MONTH CALENDAR
FROM NIKU.PPA_WIP PPA
JOIN NIKU.PPA_WIP_VALUES VAL
ON PPA.TRANSNO = VAL.TRANSNO
LEFT JOIN NIKU.NBI_DIM_CALENDAR_TIME CAL
ON PPA.TRANSDATE = CAL.DAY
WHERE CAL.YEAR_KEY = '2017'
AND PPA.TRANSTYPE = 'L'
AND VAL.CURRENCY_TYPE = 'HOME'
)
PIVOT (
SUM(STD * QTY)
FOR CALENDAR IN ('1' COST_JAN, '2' COST_FEB, '3' COST_MAR, '4' COST_APR, '5' COST_MAY, '6' COST_JUN,
'7' COST_JUL, '8' COST_AUG, '9' COST_SEP, '10' COST_OCT, '11' COST_NOV, '12' COST_DEC))
WHERE PPAINVESTMENT = xxxyyyz; (use a project internal id value here that has a cost plan that shows actuals. Note the year value as you may need to substitute.)

[mailtojustine]

Thanks for your reply. There will be miss-match between PPA_WIP table values and Actual cost from Cost Plan current plan of record. wip tables contain always live actuals data. But I need the actual cost values showing in Cost Plan. I checked the table ODF_SSL_CST_DTL_ACOST but there is no records in the database, even though this particular slice is active in back-end (Slice Request table).

[Jerin Sebastian]

Hi Justine,

The actual cost in the Cost Plan gets populated from the ppa_wip table (ppa_wip_values is the corresponding cost table for ppa_wip). 

You might seen unidentified line items in the cost plan and they are actuals in the system but haven't made it to the WIP table.

So make sure all actuals have been posted to wip and then query the ppa_wip_values tables and the actuals should match.

 

Thanks,

Jerin 

[mailtojustine]

Hi Jerin,

 

I got the idea of data population from PPA_WIP. Suppose I have a Cost Plan which is created last year and I need to populate the Monthly Actual Cost exact values displaying in that Cost Plan through query. PPA_WIP will always contains the current data right? Correct me if I am wrong? For this I refered ODF_SSL_CST_DTL_ACOST slice table, but no records found.

[Jerin Sebastian]

Hi Justine,

Yes, you are right, PPA_WIP will always have the current and historical data and not just the data from the Cost Plan. If you need the cost for only the Cost Plan date range, you might want to try joining the cost plan details table with ppa_wip, get the date range of the CP POR from fin plan details table and use it in the ppa_wip_values.

 As mentioned in the other thread, not all slice tables will have values in it and I think ODF_SSL_CST_DTL_ACOST  is one of them. 

 

Thanks,

Jerin

[mailtojustine]

Thanks Jerin. It really helps.

[Jerin Sebastian]

No worries, always happy to help !! 

 

Thanks,

Jerin