Query to get the capacity of a resource pool

Hello all,

I am trying to return the capacity of resource pool with a query.

I've tried the following one but the numbers aren't correct.

My ressource pool capacity for January 2020 is 18 in PPM Frontend but the query return me a higher result.

I've tried :

  • the debug console to understand how it works but unfortunately, I can't understand.
  • to use the rsc_rp_distribution_entries table but I don't know how to use it correctly.

Regards.

 

Query

select
rrp.resource_pool_id,
rrp.resource_pool_name,
pfp.start_date,
(sum(krpps.total_fte)/100) "TOTAL_FTE"
from
krsc_rsc_pool_period_sum krpps
join ppm_fiscal_periods pfp on pfp.fiscal_period_id = krpps.period_id
join rsc_resource_pools rrp on rrp.resource_pool_id = krpps.resource_pool_id
where 1=1
and pfp.long_name like ' 20'
and rrp.resource_pool_name = '123-ResourcePoolName'
and rrp.enabled_flag='Y'
group by
rrp.resource_pool_id,
rrp.resource_pool_name,
pfp.start_date
order by 1,2,3;

  • Verified Answer

    Hello Bisol,

    The source of allocation value can be different (for instance, staffing profile, project or other entities).

    So we do not store the value in DB and it is calculated on fly when the portlet is displayed by using complicated logic written in Java.

    We recommend to contact PSO team to check if PSO can provide solution on this.

    Thanks,
    Bryan TF.