Project and Portfolio Management Practitioners Forum
cancel

Need to know the tables related to Staffing Profiles

SOLVED
Go to solution
Highlighted
Priya K
Frequent Contributor.

Need to know the tables related to Staffing Profiles

Hi,

I need to know the tables related to Staffing Profiles and FTE. I could find some of the following tables related to them.

For example I have a staffing profile having two roles (eg : Business Analyst and Quality Engineer) and their FTEs are defined for each and every month.

The table KRSC_STAFF_PROF_PERIOD_SUM gives me the sum of the FTE in each and every period (Say Sep 2008 or Oct 2008). But I am not able to retrieve the FTEs separately for each role in a specific period (say Business Analyst FTE in Sep 2008).

Please let me know what are tables related to retrieve the FTEs for a Staffing profile

Regards,
Priya

7 REPLIES
Erik Cole_1
Outstanding Contributor.

Re: Need to know the tables related to Staffing Profiles

RSC_POSITIONS
ITG_COMPRESSED_ELEMENTS

are where it is stored.
Priya K
Frequent Contributor.

Re: Need to know the tables related to Staffing Profiles

Thanks Erik!!! I could find the FTE values for each position in ITG_COMPRESSED_ELEMENTS table, but I couldn't identify the corresponding period (Sep 2008 or Oct 2008) for each FTE value.

Best Regards,
Priya
Erik Cole_1
Outstanding Contributor.
Solution

Re: Need to know the tables related to Staffing Profiles

Priya,

Not sure if I can explain this well but here goes...

You have to derive or uncompress the data in ITG_COMPRESSED_ELEMENTS in order to "build" the staffing plan assignments matrix.

OWNER_ID joins to RSC_POSITIONS
REPEATS_COUNT = days
VALUE = hours

You have to get the staffing profile's start date from RSC_STAFFING_PROFILES in order to know when the data starts from. So for this sample data:

select COMPRESSED_ELEMENT_ID,REPEATS_COUNT,VALUE
from ITG_COMPRESSED_ELEMENTS
where OWNER_ID = 32468 -- a position
and OWNER_TYPE = 'FORECAST'
order by COMPRESSED_ENTRY_SEQ

COMPRESSED_ELEMENT_ID REPEATS_COUNT VALUE
------------------------ ---------------- --------
260590 7 0
260591 5 8
260592 9 0
260593 5 4

It means the first 7 days have no hours, the next 5 days have 8 hours, the next 9 days have no hours, the last 5 days have 4 hours.

If the staffing profile starts on 1/1/2008 then this decompresses as:

DATE HOURS
--------- -------
1/1/2008 0
1/2/2008 0
1/3/2008 0
1/4/2008 0
1/5/2008 0
1/6/2008 0
1/7/2008 0
1/8/2008 8
1/9/2008 8
1/10/2008 8
1/11/2008 8
1/12/2008 8
1/13/2008 0
1/14/2008 0
1/15/2008 0
1/16/2008 0
1/17/2008 0
1/18/2008 0
1/19/2008 0
1/20/2008 0
1/21/2008 0
1/22/2008 4
1/23/2008 4
1/24/2008 4
1/25/2008 4
1/26/2008 4

Hope this helps!
Priya K
Frequent Contributor.

Re: Need to know the tables related to Staffing Profiles

Erik,

Thanks a lot!!! I could able to track the FTE values now (even if it is a bit complicated).

And similarly, kindly verify whether my below predictions are right...

If a Resource has been allocated to a particular position, then

OWNER_ID in ITG_COMPRESSED_ELEMENTS refers to RESOURCE_ASSIGNMENT_ID from RSC_RESOUCE_ASSIGNMENTS
and
OWNER_TYPE = ALOC_EFFORT
Erik Cole_1
Outstanding Contributor.

Re: Need to know the tables related to Staffing Profiles

Yes, it's RESOURCE_ASSIGNMENT_ID for types ALLOC_EFFORT and ACTUAL_EFFORT; POSITION_ID for type FORECAST.
Priya K
Frequent Contributor.

Re: Need to know the tables related to Staffing Profiles

Erik,

Thanks a Lot!!!
Priya K
Frequent Contributor.

Re: Need to know the tables related to Staffing Profiles

I am able to trace the FTE values from the solution given