Where in the Database is the Resource Availability attribute stored?

book

Article ID: 18257

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Our report developer needs to know where in the database the resource Availability is stored.

 

Environment

Release: All PPM releases
Component: CA PPM RESOURCE MANAGEMENT

Resolution

The resource Availability field is not stored in the database as a field that can be directly queried. It is stored as a BLOB field and sliced into slice tables.

The PRJ_RESOURCES table in the database contains a PRAVAILCURVE field. This is the blob field that is cracked into slices.

None of the resource tables will contain the Availability value that is directly entered into the field in the UI. This is because the value can be recalculated by the application if the resource's calendar shift changes.

However, the Time Slice tables can be used to read resource availability by period.

For example, the following query can be used to read the resource's Daily Availability (using slice ID = 1).

Substitute '?????' with the 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'.