Clarity

Expand all | Collapse all

SQL Script on Project Cost Plan Details

Jump to Best Answer
  • 1.  SQL Script on Project Cost Plan Details

    Posted 07-31-2014 02:30 AM

    Hi All, I need a help on SQL Script on Project Cost Plan Details section in Financial tab. From the SQL Script result, i should be able to see always the quarterly data for each and every availble period for the project irrespective of period type "Annually" or "Quarterly".Could you please help me out on this query? Thank you all in advance.



  • 2.  Re: SQL Script on Project Cost Plan Details

    Posted 07-31-2014 06:38 AM

    You can use the below query for getting the values based on the period type selected on the cost plan. To always show Quarterly data, you would probably need to create a custom timeslice.

     

    SELECT INV.CODE,
      INV.NAME,
      LKUP.NAME STATUS ,
      TRAN.DESCRIPTION GRP ,
      PRC.PRNAME SUB_GRP ,
      FP.TOTAL_UNITS,
      FP.TOTAL_COST,
      FP.TOTAL_REVENUE ,
      FP_DET.TOTAL_COST DET_COST,
      FP_DET.TOTAL_UNITS DET_UNITS,
      FP_DET.TOTAL_REVENUE DET_REVENUE,
      FP_DET.PERCENTAGE ,
      TO_CHAR(TSL.FINISH_DATE,'MM/YYYY') MONTH ,
      SUM(ROUND((TSL.FINISH_DATE-TSL.START_DATE)*TSL.SLICE,2)) COST
    FROM INV_INVESTMENTS INV
    INNER JOIN FIN_PLANS FP ON INV.ID            =FP.OBJECT_ID
                AND FP.OBJECT_CODE  ='project'
                AND PLAN_TYPE_CODE  ='FORECAST'
                AND IS_PLAN_OF_RECORD=1
    LEFT OUTER JOIN CMN_LOOKUPS_V LKUP ON FP.STATUS_CODE    =LKUP.LOOKUP_CODE
                                            AND LKUP.LOOKUP_TYPE  ='FIN_PLAN_STATUS'
                                            AND LKUP.LANGUAGE_CODE='en'
    LEFT OUTER JOIN FIN_COST_PLAN_DETAILS FP_DET ON FP.ID=FP_DET.PLAN_ID
    LEFT OUTER JOIN TRANSCLASS TRAN ON FP_DET.TRANSACTION_CLASS_ID = TRAN.ID
    LEFT OUTER JOIN PRCHARGECODE PRC ON FP_DET.CHARGE_CODE_ID=PRC.PRID
    LEFT OUTER JOIN ODF_SSL_CST_DTL_COST TSL ON FP_DET.ID=TSL.PRJ_OBJECT_ID
    GROUP BY INV.CODE,
      INV.NAME,
      LKUP.NAME,
      FP.TOTAL_COST,
      FP.TOTAL_UNITS,
      FP.TOTAL_REVENUE,
      TRAN.DESCRIPTION,
      PRC.PRNAME,
      FP_DET.PERCENTAGE,
      FP_DET.TOTAL_COST,
      FP_DET.TOTAL_UNITS,
      FP_DET.TOTAL_REVENUE,
      TSL.FINISH_DATE
    


  • 3.  Re: SQL Script on Project Cost Plan Details

    Posted 08-08-2014 04:35 AM


    Thank you for the reply. However, this script joins are already known when you want to have the dependency of period type defined in your 'POR' cost plan detailed while data extraction. I am desperately looking for a script, getting extraction of cost plan detail only in 'Quarterly' scale irrespective of original period type tagged to the plan in system, for  requirement in Xcelsius. If Custom slice is the only way, m' ready to accept that too. I created custom slice on both these two curves 'Project::Detailed Forecast Cost '  & ' Project::Detailed Planned Cost '. In neither on these two table i am getting data belonged to PRJ_OBJECT_ID of project financial cost plan detail table. Could you please help me out in this case ? ..              



  • 4.  Re: SQL Script on Project Cost Plan Details

    Posted 08-08-2014 05:46 AM

    The PRJ_OBJECT_ID for the custom time slices of those 2 curves should be linked to FIN_FINANCIALS.ID. The below query should help you.

     

    SELECT     
    INV.ID AS INV_ID, 
    INV.CODE,
    f.FORECAST_CST_TOTAL,
    f.PLANNED_CST_TOTAL, 
    f.BUDGET_CST_TOTAL,
    f.PLANNED_BEN_TOTAL
    
    FROM         
    FIN_FINANCIALS AS f INNER JOIN
    ODF_OBJECT_INSTANCE_MAPPING AS map ON f.ID = map.SECONDARY_OBJECT_INSTANCE_ID INNER JOIN
    INV_INVESTMENTS AS INV ON map.PRIMARY_OBJECT_INSTANCE_ID = INV.ID
    WHERE     
    (map.PRIMARY_OBJECT_INSTANCE_CODE = 'project') 
    AND (map.SECONDARY_OBJECT_INSTANCE_CODE = 'Financials')


  • 5.  Re: SQL Script on Project Cost Plan Details

    Posted 08-08-2014 06:18 AM

    I have tried to join with either of those two custom slice tables ' ODF_SL_FORECAST_COST ' as well as ' ODF_SL_PLAN_COST ' with FIN_FINANCIALS.ID, as you mentioned. However, it is not fetching any data. Time Slice job is running every minute in our system. Could you please confirm, if i am using the correct curvs while creating custom slice, mentioned in my previous post ? Please let me know if any solution. I requird this help badly.



  • 6.  Re: SQL Script on Project Cost Plan Details
    Best Answer

    Posted 08-08-2014 01:25 PM
      |   view attached

    I'm not sure what you are doing wrong. I created the time slice Project::Detailed Planned Cost in my environment and the below query is showing correct results. The blob curves for project planned cost and budgeted cost are stored in the FIN_FINANCIALS table so the links have to be from this table. I have attached a screenshot of the custom time slice.

     

    select inv.name, inv.code, s.slice_Date, s.slice
    FROM           
    FIN_FINANCIALS AS f 
    inner join odf_sl_plan_cost s on f.id = s.prj_object_id
    INNER JOIN  ODF_OBJECT_INSTANCE_MAPPING AS map ON f.ID = map.SECONDARY_OBJECT_INSTANCE_ID 
    INNER JOIN  INV_INVESTMENTS AS INV ON map.PRIMARY_OBJECT_INSTANCE_ID = INV.ID  
    WHERE       
    (map.PRIMARY_OBJECT_INSTANCE_CODE = 'project')   
    AND (map.SECONDARY_OBJECT_INSTANCE_CODE = 'Financials')  
    

     

    Thanks,

     

    Leo.



  • 7.  Re: SQL Script on Project Cost Plan Details

    Posted 08-12-2014 04:28 AM

    Thanks for being extremely patient with me .. .. I understood the issue for which i wasn't getting data in our system. In our system, the financial slice dates are not exactly similar as normal calendar slice dates. i mean, if you think of, Q4-2013 is ending in normal calender slice on 31st Dec'2013, for our financial slice dates in biz_com_period table is ending at 29th Dec'2013 for Q4. Same for other quarters..Q2 starting for financials from 3rd or 4th or 5th April instead of 1st April. So, in query i am in turn losing or gaining around 4-5% of original financial value rather than value showed in application. It's like, in application UI, m' seeing 3500 USD for Q2, in database i am getting 3475 USD, which is not exactly same. I really don't have any idea, why the financial periods have  been created like that in our system. I need to find a different way to gain that exact amount with the same query you shared. Please let me know, if you have any idea to achieve it. Till then, thank you so much for your help ..



  • 8.  Re: SQL Script on Project Cost Plan Details

    Posted 08-13-2014 06:31 AM

    If dates are a problem, then I would suggest to create a daily slice or weekly slice instead of Quarterly one. Performance could be an issue in this case, but you can try it out. You can sum the values based on the fiscal periods and you should get what you are looking for. You can use the same query and put a filter for the slice ID.

     

    Regards,

     

    Leo.



  • 9.  Re: SQL Script on Project Cost Plan Details

    Posted 08-13-2014 08:13 AM

    Thanks Leo. I have used this below query to get data on daily custom slice curve on ' Project::Detailed Planned Cost' and data value is correctly being shown. Additionaly, i have created a custom table which will hold specific sets of data from BIZ_COM_PERIOD table with a bit modified data-representation for showing Quarterly periods. However, i need a help on how to join PRCHARGECODE table with the below query. Any idea please, you can provide ? Just need to show it's on 'CAPITAL' or 'Expense' like chargecodes. I am not sure of the way to join with Fin_Financials. Below query from my side.

     

    select inv.name, inv.code, sum(s.SLICE),qf.period_name,qf.finyear

    FROM         

    FIN_FINANCIALS AS f

    inner join odf_sl_plan_cost s on f.id = s.prj_object_id

    inner join custom_table_QFIN qf on s.SLICE_DATE between qf.startdate and (qf.finishdate -1)

    INNER JOIN  ODF_OBJECT_INSTANCE_MAPPING AS map ON f.ID = map.SECONDARY_OBJECT_INSTANCE_ID

    INNER JOIN  INV_INVESTMENTS AS INV ON map.PRIMARY_OBJECT_INSTANCE_ID = INV.ID

    inner join

    WHERE     

    (map.PRIMARY_OBJECT_INSTANCE_CODE = 'project') 

    AND (map.SECONDARY_OBJECT_INSTANCE_CODE = 'Financials')

    and inv.code = '*********'

    and s.SLICE_REQUEST_ID = XXXXXXX

    group by inv.name, inv.code,qf.period_name,qf.finyear

    order by finyear,period_name



  • 10.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 05:18 AM

    Hi Leo/ All,  Any idea further on my above question .. how to join prchargecode with the above query..basically. m' not finding any way to join this with custom slice curve table ODF_SL_PLAN_COST..

     

    Regards,

     

    Tanmoy



  • 11.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 07:21 AM

    This slice is linked to the budgeted cost at project level and not at the cost plan level, so we cannot link this to chargecodes.



  • 12.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 07:25 AM

    Why don't you try the Investment cost plan slice and see if you can get data from there?



  • 13.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 08:37 AM

    Thanks Leo..I will try out using that slice and let you know by tomorrow..



  • 14.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 07:30 AM

    If it is linked to the project then the project should have a link to the charge code.



  • 15.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 07:45 AM

    urmas I'm assuming he wants to quarterly distribution of data by Chargecodes(probably at task level), which won't be possible using the Project: Detailed Planned Cost slice. If you want to link Chargecodes defined at project level, then you can link using PRJ_PROJECTS.PRCHARGECODEID.



  • 16.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 08:27 AM

    Agreed, if looking at project level data you could only look at project level charge code (if they are set), not task charge codes



  • 17.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 09:11 AM

    Hi Urmas/ Leo, just saw this thread. I am not looking at the task level chargecode too. It should be the chargecodes mapped to cost plan detail under financial plan tab in investments. Basically, we used to do this join with FIN_COST_PLAN_DETAILS.PRCHARGCODE_ID = PRCHARGECODE.PRID for normal data extraction. However, for this case, i am not finding any similar kind of join.

    Regards,

    Tanmoy



  • 18.  Re: SQL Script on Project Cost Plan Details

    Posted 08-18-2014 02:22 PM

    I checked the Investment Cost Plan slice and it is same as the Project Detailed Planned Cost slice. So, I don't think we have any options to customize cost plan slices.



  • 19.  Re: SQL Script on Project Cost Plan Details

    Posted 08-19-2014 07:37 AM

    Just would like to second you Leo. creating custom slice on investment cost plan curve is populating data on same table 'odf_sl_plan_cost'. so, not getting any way to join it back to cost plan chargecode.