AnsweredAssumed Answered

mass update of child roles

Question asked by Wouter.Revenberg on Oct 31, 2012
Latest reply on Nov 10, 2012 by Rajini
One of our BU's has changed their child role setup to better reflect the competences of their resources.

They have given their resources (100+) a new primar role. but this did'n't update the project roles on the project team pages. Updating this manually would require a huge amount of effort as people are allocated to multiple projects.

We updated the project roles in our test instance via below query:
select srm.id Resource_db_id,
srm.full_name Resource_Name,
prj.prprimaryroleid primary_role_db_id,
SRPRI.FULL_NAME primary_role
from srm_resources srm,
prj_resources prj,
srm_resources srpri
where SRM.ID=prj.prid
and prj.prprimaryroleid=srpri.id
and SRPRI.FULL_NAME in (select distinct full_name
from srm_resources
where full_name like '***%')

This worked well for the project team pages and the resource planning portlets (e.g. weekly detail, role capacity portlets), but for the role allocations capacity portlet (select a role, select allocation tab, capacity page), the demand figures were missing for the new roles. We noticed this was because the query didn't trigger the slices to update, when we changed an allocation and saved it, the demand figures would show up.

On our test system we then ran another query to update the slice status and then ran the time slicing job:

Update prteam pr set pr.slice_status=2
where PR.PRRESOURCEID in (select distinct srm.id
from srm_resources srm,
prj_resources prj,
srm_resources srpri
where SRM.ID=prj.prid
and prj.prprimaryroleid=srpri.id
and SRPRI.FULL_NAME in (select distinct full_name
from srm_resources
where full_name like ***%'))

This all worked fine and resolved the issue. But we are still in doubt to run the same queries on our Production instance. We feel a bit uncomforable and are afraid missed some other issues.

Has anybody experience with such a mass update of roles? is there an alternative way of doing this when manual updates is not an option?

Thanks in advance!

Regards,
Wouter

Outcomes