Clarity

  • Private Community
 View Only

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

    Posted Sep 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 Sep 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