Clarity

Expand all | Collapse all

Monthly Actual Cost values from Cost Plan tables

Jump to Best Answer
  • 1.  Monthly Actual Cost values from Cost Plan tables

    Posted 07-06-2017 08:09 AM

    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



  • 2.  Re: Monthly Actual Cost values from Cost Plan tables

    Posted 07-06-2017 09:58 AM

    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.



  • 3.  Re: Monthly Actual Cost values from Cost Plan tables

    Posted 07-06-2017 01:17 PM

    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.)



  • 4.  Re: Monthly Actual Cost values from Cost Plan tables

    Posted 07-06-2017 03:50 PM

    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).



  • 5.  Re: Monthly Actual Cost values from Cost Plan tables
    Best Answer

    Broadcom Employee
    Posted 07-06-2017 07:57 PM

    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 



  • 6.  Re: Monthly Actual Cost values from Cost Plan tables

    Posted 07-07-2017 04:14 AM

    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.



  • 7.  Re: Monthly Actual Cost values from Cost Plan tables

    Broadcom Employee
    Posted 07-10-2017 02:08 AM

    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



  • 8.  Re: Monthly Actual Cost values from Cost Plan tables

    Posted 07-20-2017 05:58 AM

    Thanks Jerin. It really helps.



  • 9.  Re: Monthly Actual Cost values from Cost Plan tables

    Broadcom Employee
    Posted 07-22-2017 09:31 PM

    No worries, always happy to help !! 

     

    Thanks,

    Jerin