Project and Portfolio Management Practitioners Forum
cancel

Automating Project Health

Highlighted
pepdwill
Honored Contributor.

Automating Project Health

Hi -

 

Is anyone aware of a method to automatically update a project's health score via a workflow execution step spawned from a child request?

 

I am pretty sure this must be possible, but hopefully someone who has implemented this can help.  Any examples would be appreciated.

 

 

Thanks!

 

Danny

7 REPLIES
AlfredoMonasi
Honored Contributor.

Re: Automating Project Health

I 'm aware that the process is automated by the rollup process each 30 minutes, I dont thinks you are capable to force it by workflow, but maybe I'm wrong.

pepdwill
Honored Contributor.

Re: Automating Project Health

Perhaps another way of asking the question... is it possible to programattically update the Project Health as if overriding it?

AlfredoMonasi
Honored Contributor.

Re: Automating Project Health

What we did for a client is to create our own PLSQL that update the Project Health & Descriptions instead of using the Project Health automated process based on Gantt Task, Risks and & Cost. I don't know if that helps you.

pepdwill
Honored Contributor.

Re: Automating Project Health

Alfredo -

 

What you described sounds like what we'd be looking to possibly implement.

 

Is there any way you could share the PLSQL stuff that was used for this?

 

 

Thanks!

Danny

 

 

AlfredoMonasi
Honored Contributor.

Re: Automating Project Health

Hi pepdwil,

 

Remember to desactive the health project form the types of projects for Gantt, Risks & Cost.

 

This is a Glimpsy of the scripting:

 

 

PROCEDURE TR_SB_NOCONTEMPLADOS as
BEGIN

UPDATE
PM_PROJECT_ROLLUP ROLL2 SET ROLL2.OVERRIDE_DATE = sysdate + 365
,ROLL2.OVERRIDE_DESCRIPTION = (
SELECT
total.semaforo
FROM
(SELECT
R.REQUEST_ID REQ_ID, CASE WHEN (S.STATUS_NAME = 'CCP -Construcción') THEN 'Se inicializa sin semáforo.' ELSE ' ' END SEMAFORO
FROM
KCRT_STATUSES_NLS S,
KCRT_REQUEST_TYPES_NLS RT,
kcrt_requests r,
kcrt_request_details rd
WHERE
S.STATUS_ID=R.STATUS_ID
AND r.STATUS_CODE = 'IN_PROGRESS'
AND R.REQUEST_TYPE_ID=RT.REQUEST_TYPE_ID
AND RD.REQUEST_ID = R.REQUEST_ID
AND RD.BATCH_NUMBER = 1
AND S.STATUS_NAME NOT IN ('AC - En Activación', 'AC - Revisión Registro', 'AC - Registro y Envío Alta', 'Proyecto Troncal en Espera de Activación Contable',
'PC - En Revisión', 'PC - En Post Capitalización', 'PC - Evaluación Post Capitalización', 'AC - Revisión Facturación')
AND (rt.REQUEST_TYPE_NAME like 'DSI - Proyecto Troncal' OR rt.REQUEST_TYPE_NAME like 'DSI - SubProyecto')) TOTAL,
PM_PROJECT_ROLLUP PR1,
PM_PROJECTS P
WHERE
TOTAL.REQ_ID = P.PFM_REQUEST_ID
AND P.ROLLUP_ID = PR1.ROLLUP_ID
AND PR1.ROLLUP_ID = ROLL2.ROLLUP_ID
)
,ROLL2.OVERALL_HEALTH_INDICATOR = (
SELECT
total.semaforo
FROM
(SELECT
R.REQUEST_ID REQ_ID, CASE WHEN (S.STATUS_NAME = 'CCP -Construcción') THEN 'NONE' ELSE 'GREEN' END SEMAFORO
FROM
KCRT_STATUSES_NLS S,
KCRT_REQUEST_TYPES_NLS RT,
kcrt_requests r,
kcrt_request_details rd
WHERE
S.STATUS_ID=R.STATUS_ID
AND r.STATUS_CODE = 'IN_PROGRESS'
AND R.REQUEST_TYPE_ID=RT.REQUEST_TYPE_ID
AND RD.REQUEST_ID = R.REQUEST_ID
AND RD.BATCH_NUMBER = 1
AND S.STATUS_NAME NOT IN ('AC - En Activación', 'AC - Revisión Registro', 'AC - Registro y Envío Alta', 'Proyecto Troncal en Espera de Activación Contable',
'PC - En Revisión', 'PC - En Post Capitalización', 'PC - Evaluación Post Capitalización', 'AC - Revisión Facturación')
AND (rt.REQUEST_TYPE_NAME like 'DSI - Proyecto Troncal' OR rt.REQUEST_TYPE_NAME like 'DSI - SubProyecto')) TOTAL,
PM_PROJECT_ROLLUP PR1,
PM_PROJECTS P
WHERE
TOTAL.REQ_ID = P.PFM_REQUEST_ID
AND P.ROLLUP_ID = PR1.ROLLUP_ID
AND PR1.ROLLUP_ID = ROLL2.ROLLUP_ID
)
WHERE EXISTS (
SELECT
total.semaforo
FROM
(SELECT
R.REQUEST_ID REQ_ID, CASE WHEN (S.STATUS_NAME = 'CCP -Construcción') THEN 'NONE' ELSE 'GREEN' END SEMAFORO
FROM
KCRT_STATUSES_NLS S,
KCRT_REQUEST_TYPES_NLS RT,
kcrt_requests r,
kcrt_request_details rd
WHERE
S.STATUS_ID=R.STATUS_ID
AND r.STATUS_CODE = 'IN_PROGRESS'
AND R.REQUEST_TYPE_ID=RT.REQUEST_TYPE_ID
AND RD.REQUEST_ID = R.REQUEST_ID
AND RD.BATCH_NUMBER = 1
AND S.STATUS_NAME NOT IN ('AC - En Activación', 'AC - Revisión Registro', 'AC - Registro y Envío Alta', 'Proyecto Troncal en Espera de Activación Contable',
'PC - En Revisión', 'PC - En Post Capitalización', 'PC - Evaluación Post Capitalización', 'AC - Revisión Facturación')
AND (rt.REQUEST_TYPE_NAME like 'DSI - Proyecto Troncal' OR rt.REQUEST_TYPE_NAME like 'DSI - SubProyecto')) TOTAL,
PM_PROJECT_ROLLUP PR1,
PM_PROJECTS P
WHERE
TOTAL.REQ_ID = P.PFM_REQUEST_ID
AND P.ROLLUP_ID = PR1.ROLLUP_ID
AND PR1.ROLLUP_ID = ROLL2.ROLLUP_ID
);

END TR_SB_NOCONTEMPLADOS;

pepdwill
Honored Contributor.

Re: Automating Project Health

Thanks Alfredo for sharing.

 

Based on the script example you provided, looks like only the PM_PROJECT_ROLLUP table would need to be modified.  Is it that simple?

AlfredoMonasi
Honored Contributor.

Re: Automating Project Health

Hi pepdwill,

 

That is the only table you will need to update, as always as you desactive the rollup health of that specific type of project.