Unexpected Export Data

R

russ

Hi all,

We are using EPMS2003 to track projects in our IT department and the
timesheet approved actuals are exported to a financials database.

We recently found that Saved Web edits were being uploaded with
approved web edits (oh my god - your whole project management system
is
"out of control" - damn microsoft and damn you - we'll never be
accountable again - or words to that effect!)

The SQL we used to filter records in MSP_WEB_WORK relied on the
following WHERE statement;
WHERE (MSP_WEB_WORK.WWORK_TYPE = 1) AND
(MSP_WEB_TRANSACTIONS.WTRANS_ACTION = 1)

this does not discriminate between edited/saved and
edited/saved/approved


so, we came up with this (which seems to work);


WHERE (MSP_WEB_WORK.WWORK_TYPE = 1) AND
(MSP_WEB_TRANSACTIONS.WTRANS_ACTION = 1),

but it gives the same results as;
WHERE (MSP_WEB_WORK.WWORK_UPDATE_STATUS = 0) AND
MSP_WEB_WORK.WWORK_APPROVAL_STATUS = 1)

The question is does anyone really know which field is the real one,
and which one is the imposteur?

P.S. PJSVRDB.HTM is not clear on this subject

Thanks a lot,
Russell.
 
R

russ

well for what it's worth i think i'll post a possible solution for
anyone who needs it,

it appears that web actuals are in MSP_WEB_WORK and not in
MSP_WEB_WORK_ACTUAL (no data in this table)

so we've used this statement to retrieve data for export to MIMS:

WHERE (MSP_WEB_WORK.WWORK_TYPE = 1) AND
(MSP_WEB_TRANSACTIONS.WTRANS_ACTION = 1)

this is our entire SQL statement that summarises the data into a work
order format

SELECT

MSP_VIEW_PROJ_RES_ENT.ResourceEnterpriseNumber1 AS PayNumber,
SUBSTRING(MSP_VIEW_PROJ_TASKS_ENT.TaskEnterpriseText1,1, 20) AS
WorkOrder,
MSP_WEB_WORK.WWORK_START AS StartDate,
MSP_WEB_WORK.WWORK_FINISH AS EndDate,
SUM(MSP_WEB_WORK.WWORK_VALUE) AS Hours,
MSP_VIEW_PROJ_RES_ENT_1.ResourceEnterpriseNumber1 AS PMPayNumber,
MSP_WEB_PROJECTS.PROJ_NAME AS ProjectName

FROM

MSP_WEB_ASSIGNMENTS INNER JOIN MSP_VIEW_PROJ_RES_ENT ON
MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_VIEW_PROJ_RES_ENT.WPROJ_ID INNER
JOIN
MSP_VIEW_PROJ_TASKS_ENT ON MSP_WEB_ASSIGNMENTS.TASK_UID =
MSP_VIEW_PROJ_TASKS_ENT.ENT_TaskUniqueID AND
MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_VIEW_PROJ_TASKS_ENT.WPROJ_ID INNER
JOIN MSP_WEB_TRANSACTIONS INNER JOIN
MSP_WEB_WORK ON MSP_WEB_TRANSACTIONS.WASSN_ID = MSP_WEB_WORK.WASSN_ID
INNER JOIN
(SELECT WASSN_ID, MAX(WTRANS_DATE) AS MAX_WTRANS_DATE FROM
MSP_WEB_TRANSACTIONS GROUP BY WASSN_ID) LATEST_WEB_TRANS ON
MSP_WEB_TRANSACTIONS.WASSN_ID = LATEST_WEB_TRANS.WASSN_ID AND
MSP_WEB_TRANSACTIONS.WTRANS_DATE = LATEST_WEB_TRANS.MAX_WTRANS_DATE
INNER JOIN MSP_WEB_RESOURCES ON MSP_WEB_WORK.WRES_ID =
MSP_WEB_RESOURCES.WRES_ID ON
MSP_WEB_ASSIGNMENTS.WASSN_ID = MSP_WEB_WORK.WASSN_ID AND
MSP_VIEW_PROJ_RES_ENT.ENT_ResourceEnterpriseUniqueID =
MSP_WEB_RESOURCES.RES_EUID INNER JOIN
MSP_WEB_PROJECTS ON MSP_WEB_ASSIGNMENTS.WPROJ_ID =
MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN
MSP_WEB_RESOURCES MSP_WEB_RESOURCES_1 ON MSP_WEB_PROJECTS.WRES_ID =
MSP_WEB_RESOURCES_1.WRES_ID LEFT OUTER JOIN
MSP_VIEW_PROJ_RES_ENT MSP_VIEW_PROJ_RES_ENT_1 ON
MSP_WEB_RESOURCES_1.RES_EUID =
MSP_VIEW_PROJ_RES_ENT_1.ENT_ResourceEnterpriseUniqueID AND
MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_VIEW_PROJ_RES_ENT_1.WPROJ_ID

WHERE

(MSP_WEB_WORK.WWORK_UPDATE_STATUS = 0) AND
(MSP_WEB_WORK.WWORK_APPROVAL_STATUS = 1) AND
(MSP_WEB_WORK.WWORK_FINISH >= CONVERT(DATETIME, '10-Sep-2004', 102))
AND
(MSP_WEB_WORK.WWORK_START <= CONVERT(DATETIME, '21-Oct-2004', 102))

GROUP BY

MSP_VIEW_PROJ_RES_ENT.ResourceEnterpriseNumber1,
SUBSTRING(MSP_VIEW_PROJ_TASKS_ENT.TaskEnterpriseText1, 1, 20),
MSP_WEB_WORK.WWORK_START, MSP_WEB_WORK.WWORK_FINISH,
MSP_VIEW_PROJ_RES_ENT_1.ResourceEnterpriseNumber1,
MSP_WEB_PROJECTS.PROJ_NAME

ORDER BY

MSP_WEB_WORK.WWORK_START

lots of luck
russ
 
Top