Clarity PPM1

Expand all | Collapse all

SQL that lists Annual Forecasted Costs for 2015 & 2016 by Investment

Jump to Best Answer
  • 1.  SQL that lists Annual Forecasted Costs for 2015 & 2016 by Investment

    Posted 09-11-2014 05:25 AM

    I made a start at writing some sql for this before quickly realising I'm horribly out of my depth.  Does anyone have some sql for this already in place?

     

    I need to list each investment id & name, followed by their forecasted cost for 2015/16 broken down by cost type - as annual figures.

     

    Does anyone have somethink like this already?  This table displays our time periods i think: NBI_DIM_FISCAL_TIME

     

    Ben



  • 2.  Re: SQL that lists Annual Forecasted Costs for 2015 & 2016 by Investment
    Best Answer



  • 3.  Re: SQL that lists Annual Forecasted Costs for 2015 & 2016 by Investment

    Posted 09-12-2014 06:50 AM

    Thanks this has been really handy.  I ended up writing this which has done the job nicely, using your links to write it:

     

    SELECT DISTINCT
    invi.id,
    INVI.NAME,
    (select l.NAME FROM niku.CMN_LOOKUPS_V l WHERE L.LOOKUP_TYPE = 'LOOKUP_FIN_COSTTYPECODE' AND l.ID=FD.COST_TYPE_ID AND l.LANGUAGE_CODE='en') Cost_Type,
    SUM(CASE WHEN u.START_DATE = FY2015.ST THEN ROUND(U.SLICE * (U.FINISH_DATE - U.START_DATE)) END) FY2015,
    SUM(CASE WHEN u.START_DATE = FY2016.ST THEN ROUND(U.SLICE * (U.FINISH_DATE - U.START_DATE)) END) FY2016,
    SUM(CASE WHEN u.START_DATE = FY2017.ST THEN ROUND(U.SLICE * (U.FINISH_DATE - U.START_DATE)) END) FY2017,
    SUM(CASE WHEN u.START_DATE = FY2018.ST THEN ROUND(U.SLICE * (U.FINISH_DATE - U.START_DATE)) END) FY2018
    
    
    FROM
    niku.ODF_SSL_CST_DTL_COST U,
    niku.FIN_PLANS FP,
    niku.FIN_COST_PLAN_DETAILS FD,
    niku.INV_INVESTMENTS INVI,
    (SELECT biz.START_DATE ST from NIKU.biz_com_periods biz where biz.period_name = 'FY 2015' and biz.PERIOD_TYPE = 'ANNUALLY') FY2015,
    (SELECT biz.START_DATE ST from NIKU.biz_com_periods biz where biz.period_name = 'FY 2016' and biz.PERIOD_TYPE = 'ANNUALLY') FY2016,
    (SELECT biz.START_DATE ST from NIKU.biz_com_periods biz where biz.period_name = 'FY 2017' and biz.PERIOD_TYPE = 'ANNUALLY') FY2017,
    (SELECT biz.START_DATE ST from NIKU.biz_com_periods biz where biz.period_name = 'FY 2018' and biz.PERIOD_TYPE = 'ANNUALLY') FY2018,
    (SELECT m.pk_id FROM niku.ODF_MULTI_VALUED_LOOKUPS m WHERE m.attribute = 'grouping_attributes' AND m.value = 'cost_type_id') CP
    
    
    WHERE
    FP.OBJECT_ID = INVI.ID
    AND FP.IS_PLAN_OF_RECORD = 1
    AND FD.PLAN_ID(+) = FP.ID
    AND FD.ID = U.PRJ_OBJECT_ID(+)
    AND FP.period_type_code = 'ANNUALLY'
    AND CP.pk_id = FP.ID
    
    
    GROUP BY
    invi.id,
    INVI.NAME,
    FD.COST_TYPE_ID
    

     

    Thank you for your help.

     

    Ben