0

Does anyone out there know where the information for "Status Update History" in the Approval Center is located in the database? In particular I am looking for the Status Flag (unpublished or published) for a given update. I am trying to build a report off of this information and I am hoping someone has found this flag in the database. I am using Project Server 2013.

enter image description here

abauman
  • 96
  • 6

1 Answers1

0

Answered Post from Technet by Raushan_kumar

I am still interested in linking the actual hours approved to the query, but so far have been unsuccessful. 

Status History related information are available in MSP_ASSIGNMENT_TRANSACTIONS table however Status Flag can be check by comparing WPROJ_LAST_PUB and ASSN_TRANS_SUBMIT_DATE fields.

SQL Query:

SELECT T.ASSN_TRANS_SUBMIT_DATE, P.PROJ_NAME, A.TASK_NAME,

IS_PUBLISHED = CASE WHEN P.WPROJ_LAST_PUB > T.ASSN_TRANS_SUBMIT_DATE THEN 1 ELSE 0 END

FROM PUB.MSP_ASSIGNMENT_TRANSACTIONS T

LEFT OUTER JOIN PUB.MSP_ASSIGNMENTS_SAVED A ON T.ASSN_UID = A.ASSN_UID

LEFT OUTER JOIN PUB.MSP_PROJECTS P ON A.PROJ_UID = P.PROJ_UID

abauman
  • 96
  • 6