Checking for needed updates

[Inquisit1]

Hello,

Does anyone know if there is a field in one of PWA's databases that will
indicate whether or not tasks have been submitted when updated?

I'd like to create some alerts based on this.

Thanks for any help!

 

[Barbara - Austria]

Hi Inquisit1,

you need to use Publish DB for that (please be aware of the fact that MS
does not support queries in DBs other than Reporting!!!). This query is
working for me:

SELECT dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_PROJECTS.PROJ_NAME,
Selected_ASSN.TASK_NAME, Selected_ASSN.Update_Status,
MSP_RESOURCES_1.RES_NAME AS Status_MGR, Selected_ASSN.MOD_DATE,
Selected_ASSN.ActualHours AS Hours_DIFF
FROM (SELECT dbo.MSP_ASSIGNMENTS_SAVED.ASSN_UID,
dbo.MSP_ASSIGNMENTS_SAVED.RES_UID, dbo.MSP_ASSIGNMENTS_SAVED.PROJ_UID,
dbo.MSP_ASSIGNMENTS_SAVED.TASK_NAME, CASE WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 0 THEN 'Saved' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 2 THEN 'Rejected' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 0 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 0 THEN 'Submitted' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 1 THEN 'Submitted' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 0 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 1 THEN 'Accepted' END AS
Update_Status, dbo.MSP_ASSIGNMENTS_SAVED.WRES_UID_MANAGER,
dbo.MSP_ASSIGNMENTS_SAVED.MOD_DATE,
(dbo.MSP_ASSIGNMENTS_SAVED.ASSN_ACT_WORK -
ISNULL(dbo.MSP_ASSIGNMENTS.ASSN_ACT_WORK, 0)) / 60000 AS ActualHours
FROM dbo.MSP_ASSIGNMENTS_SAVED LEFT OUTER JOIN
dbo.MSP_ASSIGNMENTS ON dbo.MSP_ASSIGNMENTS_SAVED.ASSN_UID =
dbo.MSP_ASSIGNMENTS.ASSN_UID
WHERE ((dbo.MSP_ASSIGNMENTS_SAVED.ASSN_ACT_WORK -
ISNULL(dbo.MSP_ASSIGNMENTS.ASSN_ACT_WORK, 0)) / 60000 <> 0)) AS
Selected_ASSN INNER JOIN
dbo.MSP_RESOURCES ON Selected_ASSN.RES_UID = dbo.MSP_RESOURCES.RES_UID
INNER JOIN
dbo.MSP_PROJECTS ON Selected_ASSN.PROJ_UID = dbo.MSP_PROJECTS.PROJ_UID
INNER JOIN
dbo.MSP_RESOURCES AS MSP_RESOURCES_1 ON Selected_ASSN.WRES_UID_MANAGER
= MSP_RESOURCES_1.RES_UID
WHERE (Selected_ASSN.ActualHours <> 0)

Please verify it with your data before using!

Regards
Barbara

Am 24.02.2010 19:43, schrieb Inquisit1: