Clarity

I need to be able to retrieve the data from the Resource Planning Allocations Tab > Monthly Detail into a crystal report. Where can i find the Monthly Allocation data and what DB links do i need to make?

Mike R.

That is blob data cracked on the fly.

You would have to take it from prj_blb_slices where the slice_request is MONTHLYRESOURCEALLOCCURVE.

If that is properly populated. If not then you would have to use the daily allocation slices.

Martti K.

Mike,

Using sql trace you can get the sql for monthly detail portlet.Below is the one for allocation.you need to get the same for Availability,Actual and hard Allocation.

You can build an SP which will retrieve the data from sql adding your requirements (to suit your input parameters) and show the results.

-- Allocation
SELECT INV_INVESTMENTS.code,PRTEAM.PRID ID
, periods.start_date PERIOD
, SUM(slices.slice) TOTAL
FROM PRJ_BLB_SLICES_M_ALC slices
,(SELECT MONTH_KEY
, min(day) start_date
, max(day) end_date
FROM nbi_dim_calendar_time
WHERE day >= '01-may-2013' AND day < '01-jun-2013'
GROUP BY MONTH_KEY
) periods
, INV_INVESTMENTS
, PRTEAM
, SRM_RESOURCES RS
, PRJ_RESOURCES PRES

WHERE slices.SLICE_DATE >= periods.start_date
AND slices.SLICE_DATE <= periods.end_date
AND slices.SLICE_DATE >= '01-may-2013' AND slices.SLICE_DATE < '01-jun-2013'
AND slices.PRJ_OBJECT_ID = PRTEAM.PRID
AND RS.ID = slices.RESOURCE_ID
AND PRES.PRID = slices.RESOURCE_ID
AND INV_INVESTMENTS.ID = slices.INVESTMENT_ID
AND NVL((SELECT IS_TEMPLATE FROM INV_PROJECTS WHERE INV_PROJECTS.PRID = INV_INVESTMENTS.ID), 0) = 0
AND INV_INVESTMENTS.IS_ACTIVE = 0 AND PRTEAM.PRID IN (5111880)
GROUP BY INV_INVESTMENTS.code,PRTEAM.PRID, periods.start_date

cheers,
sundar

Thank you Sundar that is pulling what I need. How would i go about adding the Availability to it? I'm guessing it would be the slices_m_avl, but any way i link it it changes the results.

Mike R.

Mike,


For Availability -->

SELECT slices.PRJ_OBJECT_ID ID, periods.start_date PERIOD, SUM(slices.slice) TOTAL

FROM PRJ_BLB_SLICES_M_AVL slices

,(SELECT MONTH_KEY, min(day) start_date, max(day) end_date

FROM nbi_dim_calendar_time WHERE day >= '01-JUN-2013' AND day < '01-DEC-2013'

GROUP BY MONTH_KEY

) periods

, SRM_RESOURCES RS, PRJ_RESOURCES PRS


WHERE slices.SLICE_DATE >= periods.start_date

AND slices.SLICE_DATE <= periods.end_date

AND slices.SLICE_DATE >= '01-JUN-2013'

AND slices.SLICE_DATE < '01-DEC-2013'

AND slices.PRJ_OBJECT_ID = RS.ID

AND RS.ID = PRS.PRID

AND PRS.PRISROLE = 0

AND slices.PRJ_OBJECT_ID IN (6505251) GROUP BY slices.PRJ_OBJECT_ID, periods.start_date

cheers,
sundar