How To Check % Allocation / % Availability For The Classic Project Team

Products

• Clarity PPM SaaS

Issue/Introduction

In the Classic > Home > Project > Team > Resource Properties > Planned Allocation > % Allocation
Is there a way to check a resource's team % Allocation with a database query?

It is not  possible to find the query with an action trace.

Resolution

The following is the best-effort query (Oracle) to find out the % Allocation for a project team.
Substitute/filter for the project id/resource id:

SELECT
    pt.prid AS team_id,
    inv.name AS investment_name,
    inv.code AS investment_code,
    res.full_name AS resource_name,
    DATE '1899-12-30' + seg.segment_start AS start_date,
    DATE '1899-12-30' + (seg.segment_finish - 1) AS finish_date,
    TO_CHAR(ROUND(seg.rate * 800 /
        COALESCE(
            (SELECT p.hours_per_day
             FROM prcalendar p
             WHERE p.prresourceid = pt.prresourceid
             AND ROWNUM = 1),
            8
        ), 2), 'FM999990.00') AS pct_allocation
FROM prteam pt,
     TABLE(pt.odf_nk_odf_pralloccurve.segments) seg,
     inv_investments inv,
     srm_resources res
WHERE pt.odf_nk_odf_pralloccurve IS NOT NULL
  AND pt.prprojectid = inv.id
  AND pt.prresourceid = res.id
  AND pt.prprojectid IN (5PPPPP1, 5PPPPP2)
  AND pt.prresourceid = 5RRRRRR
ORDER BY inv.name, res.full_name, seg.segment_start;

Additional Information

Ensure the following are checked:
-Resource Hire and Termination Dates are reviewed
-Time Slicing and Time Slicing Sync jobs run and complete.