Clarity

I have a requirement to extract the data from the Cost blob attribute of Budget Plans using a SQL Query that pulls the details for each line and month

I have set up a Time Slice for  Project: Detailed Budget Cost with a Rollover Interval of 'Monthly' a Slice Period of 'Monthly' and Number of Periods of '12'. After running both the Datamart Extraction and Datamart Rollup jobs, I find that the odf_sl_budget_cost table is loaded with information. I use the following query to get slice data:

SELECT
  I.ID,
  I.CODE,
  FP.NAME,
  FP.ID FP_ID,
  FPD.ID FPD_ID,
  FPD.TOTAL_UNITS,
  SL.SLICE_DATE,
  SL.SLICE
FROM INV_INVESTMENTS I
     INNER JOIN FIN_PLANS FP ON FP.OBJECT_ID = I.ID
                AND FP.OBJECT_CODE = I.ODF_OBJECT_CODE
                AND FP.IS_PLAN_OF_RECORD = 1
                AND FP.PLAN_TYPE_CODE = 'BUDGET'
     INNER JOIN FIN_COST_PLAN_DETAILS FPD on FPD.PLAN_ID = FP.ID
     INNER JOIN ODF_SL_BUDGET_COST SL ON SL.PRJ_OBJECT_ID = FP.ID          
WHERE I.CODE = '<project_code>';

It return the following data:

Our organization has never dealt with Budget Slices so I have no idea how to move beyond this level. I cannot find anything beyond this level one the web that show me how to get the Charge Code and period amounts.

Thanks,

Garth

Can you check if this helps ?

How to build custom grid portlet with time-scaled values? 

NJ

For a monthly slice, the SLICE_DATE might say 1/1/2016 but it really means that that slice if for the month of January.

The charge-code - or anything else that isn't the sliced data (the numbers) - you would get based upon the thing that has been "sliced" (in this case the budget tables) - I'm not really familiar with that bit of the data model though so can't give you the immediate answer.

If your intention is to extract cost from Budget plan details, then you may use the slice table - odf_ssl_cst_dtl_cost where odf_ssl_cst_dtl_cost.prj_object_id = fin_cost_plan_details.id

I can see in my query the slice_date and it makes sense, but the slice itself makes no sense. I have used Sridhar's suggestion and added the odf_ssl_cst_dtl_cost to the query but there are no records in that table for the project I'm querying. I've run the Datamart Extraction and Datamart Rollup jobs but there is no data.

Did you run time slicing job?

Yep. Agree.

To get cost per row, you need to do something like this:

select (finish_date - start_date)*slice
from odf_ssl_cst_dtl_cost
where prj_object_id = <fin_cost_plan_details.id>

And then connect to biz_com_periods table if you want the per cell number.

I just ran the TIME SLICING job and I can see data now in my query, I still can't see if for the specific project I'm looking for. I'll try Jeevan's query and see what happens for other projects.

Thanks

Hi harrisge - We you able to resolve it using Jeevan.B's query? Thanks, Chris