Clarity

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.

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

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

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

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.

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.

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

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.

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

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

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.

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

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

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

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.

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

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

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.

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.