Where is the resource Default Allocation % field stored in the database

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:   

Where is the resource Default  Allocation % field stored in the database?

 

 

 

Environment

16.1.1

Resolution

This is a virtual field and not stored directly in the database.

 

In Clarity versions 16.1.1 and higher there is a new function which will extract the Default Allocation %

The function is PRJ_DEFAULT_ALLOC_RATE_FCT(PRALLOCCURVE)

A simple query would be 

select prid, PRJ_DEFAULT_ALLOC_RATE_FCT(PRALLOCCURVE) from prteam where prprojectid = 5000000

 

A more detailed query adding the investment and resources would be

SELECT 
  i.CODE,
  i.NAME,
    r.UNIQUE_NAME,
  r.FULL_NAME,
    PRJ_DEFAULT_ALLOC_RATE_FCT(t.PRALLOCCURVE),
t.PRID,
  t.PRPROJECTID,
  t.PRRESOURCEID
FROM prteam t
INNER JOIN SRM_RESOURCES r
ON t.PRRESOURCEID = r.ID
INNER JOIN INV_INVESTMENTS  i
ON t.PRPROJECTID    = i.ID

 

You could then add a filter for a specific investment or resource.

 

Prior to 16.1.1 there is no way to extract the Default Allocation.

 

 

Additional Information

The data for Default Allocation % is stored in a blob field.

PRTEAM.PRALLOCCURVE

All resources are automatically allocated at 100% of their available working days. The Default Allocation % field on a Team record is stored in a blob field. The Project Manager has the ability to edit Staff Member details for the Team record.

Default % Allocation

Defines the percentage of time you want the resource to be allocated to this investment (you can enter "0%"). This change updates the Allocation and Allocation % columns on the investment's Team: Staff page.

From the Bookshelf, locate the Technical Reference Guide

PRTEAM TABLE DOCUMENTATION:

PRTEAM.PRALLOCCURVE field

This column refers to the system-generated allocation rate curve. The rate curve value is calculated by multiplying the PRALLOCDEFAULT rate by the resource calendar's hours per week. Users can create exceptions in CA PPM, Open Workbench (OWB), or Microsoft® Project (MSP) Allocation Segments.

This is the only reference to the PRALLOCDEFAULT attribute, which is included in the curve.

The PRALLOCCURVE field is a BLOB field which can only be parsed in Clarity versions 16.1.1 and higher.