Where in the Database is the Resource Availability attribute stored?

Document ID : KB000018257
Last Modified Date : 14/02/2018
Show Technical Document Details

Question: 

Our report developer needs to know where in the Database the Resource Availability is stored.

Answer: 

The Resource Availability field is not stored in the database as a field that can be directly queried. Both Availability and Allocation is stored on BLOB fields and sliced into slice tables.

If you are looking for the capacity of a resource, there is a portlet from the PMO Accelerator that provides Availability = Capacity - Demand. The Resource Planning portlets provide this information too.

Look at the technical Reference Guide for the PRJ_RESOURCES table and you will see a PRAVAILCURVE field. This is the blob field that is cracked into slices.

You won't see a field on the SRM_RESOURCES table for the value that directly entered into the field. This is because the value can change by the application if the resource's calendar changes occur in the shifts.

You can use the Time Slice table to read resource availability.

Slice records using the Daily Resource Availability Curve, Slice ID = 1.

Substitute '?????' with your mixed case Resource Code ID

     SELECT 
     s.Slice, 
     s.slice_date, 
     r.full_name 
     FROM 
     prj_blb_slices s, 
     srm_resources r 
     WHERE 
     s.prj_object_id = r.id and 
     r.unique_name like '?????' and 
     slice_request_id = 1

NOTE: The result of the query above are dictated by the Time Slice definition 'From Date' and 'Number of Periods'.  

Additional Information: 

Reference TEC525604 : How are Allocation, %Allocation, and Availability calculated on the Resource, Projects or Detail pages?

Reference TEC435572 : How should I configure my Time Slices?

Reference TEC439197 : How to set the default Resource Availability Rate