How to detect projects/tasks with unpublished approved transactions (task updates) - Project Server 2007 RRS feed

  • Question

  • Question in 2 parts.

    1. Please confirm the behaviour I see with regards to unpublished approved transactions
    2. Then tell me how I detect approved transactions waiting to be published

    What I see:

    1. Approver has project open in MS Project
    2. User submits time
    3. Approver approves in PWA
    4. Approver Saves and Publishes from MS Project
    5. Nothing comes through to Reporting database
    6. Close plan
    7. Approver goes into Projects Center. Edit Project Properties.
    8. Edit Project Properties says there is unpublished time waiting to be saved
    9. Time is still waiting to be added to plan so was not in the plan and not published. Until you then save and publish in Edit Project Properties

    This means that I cannot simply report on transactions in the Published database which are approved after the publish date. The transaction could have been approved well before publish - but with plan open in MS Project. if no one has gone back and reopened the plan, published and saved it, then the time is still not in reporting.

    I don't suppose the Publish concept goes away ever in Project Server? 2013?



    Thursday, February 27, 2014 10:11 AM

All replies

  • MikeCro,

    Please find my inputs

    1) What you have been experiencing is the design behavior. The updates wont get applied to the Project Plan if plan is checked out while approving the task updates. Also Actual work will not sync to reporting database until you publish the plan. The Project manager can setup auto approval rule to accept and published the task updates automatically. In this case all task updates will get approved and published automatically (Provided Project Plan is not checked out)

    2) Project Manager can find the all unpublished approved transactions by accessing Status History page (Approval Center >> History >> Status History). The Approval history contains "Status" column which shows whether transaction has been published or not.

    Hope this helps.

    Thank you,

    Kiran K.

    Friday, February 28, 2014 5:17 PM
  • Thanks Kiran.

    Really helpful to know that is the designed behaviour. And helpful tip on automatic approve/publish. Though in fact I would want to discourage our approvers from doing that as we are paying externals based on time approved!

    With (2) is there a way I can get the same information as the GUI but from one of the SQL databases?

    That way I can report across all projects and know who needs to publish - especially for our contractors who we pay based on published data in the Reporting database.



    Friday, February 28, 2014 6:24 PM
  • Hello MikeCro,

    The information you are looking for can be retrieve from published DB. However AFAIK running query on published DB is not recommended.

    The information about current status of the transaction is stored in the MSP_ASSIGNMENT_TRANSACTIONS table in published DB (in Ps 2013 pub.MSP_ASSIGNMENT_TRANSACTIONS schema). You can find more information about this table columns at

    You need to query using MSP_ASSIGNMENT_TRANSACTIONS table to get the current state of status updates.

    You need to include other tables in your query (Joins) like MSP_Projects, MSP_resource, MSP_tasks to get the information about Projects,Resources and tasks along with status updates.

    The MSP_ASSIGNMENT_TRANSACTIONS table does not contain information on whether updates are published or not. You can get this information by adding criteria in your query to check wproj_Last_PUB (MSP_Projects Table) is greater than ASSN_TRANS_SUBMIT_DATE (MSP_ASSIGNMENT_TRANSACTIONS table). If wproj_Last_PUB > ASSN_TRANS_SUBMIT_DATE  then that means project has been published after last assignment changes.

    You can use query given in below thread as stating point and then build on it

    Hope this helps.

    Kiran K

    Friday, February 28, 2014 7:17 PM
  • Kiran - not sure if I am being an awkward customer but ...

    ... I am reporting from Published database (though strange that these transactions are not necessarily published yet - but I guess they are a queue) and have discovered that excellent article about MSP_ASSIGNMENT_TRANSACTIONS. I have heard the warning on Published database - our reasoning is that we are just using it for working out where time is in the system. Not the billing itself.

    But the reason I talked about the behaviour in (1) above is that under these circumstances (plan open when approval made) you could easily have a publish date AFTER an approval transaction but that transaction not be in the plan. And somehow PWA->Edit Properties and MS Project both know about these "queued" approved transactions which have not hit the plan yet.

    Tantalisingly, the MSP_ASSIGNMENT_TRANSACTIONS table does have a PUB_Status (or something like it, have shutdown work laptop) field. But I don't see that field change.

    If I have pushed the reporting as far as it will go I will accept defeat semi-gracefully. But if there is any chance of getting to the bottom of this (a PSI call if absolutely necessary) then I'd like to battle on.



    • Edited by MikeCro Saturday, March 1, 2014 10:50 AM
    Friday, February 28, 2014 8:14 PM
  • Hello MikeCro,

    You are correct, in above behavior (1) you could easily have a publish date AFTER an approval transaction.

    I have done some quick testing and below are findings. Lets check if this helps you.

    1. Open the Project From Project Professional (to keep it checkedout)
    2. Submit the Task Update for above Project. MSP_ASSIGNMENT_TRANSACTIONS shows below data

    ASSN_TRANS_STATE_ENUM =1 (Submitted)

    ASSN_TRANS_ACTION_ENUM = 0 (Pending)

    1. Approve the task updates. Project Plan is still checked out. In the manage queue you will find that “Status Update” job is skipped for optimization. So I believe the status update job gets skipped because of checked out plan and will get applied when plan gets checked in.

    ASSN_TRANS_STATE_ENUM =2 (InProcess)

    ASSN_TRANS_ACTION_ENUM = 1 (Approved)

    1. Save >> publish and  check-in the Project Plan

    ASSN_TRANS_STATE_ENUM =3 (ApplySuccessful)

    ASSN_TRANS_ACTION_ENUM = 1 (Approved)

    Status gets changed to “ApplySuccessful” once we check-in the plan and the updates will get applied to the draft db (plan). So if you have ASSN_TRANS_STATE_ENUM =3 and ASSN_TRANS_ACTION_ENUM = 1 that means actuals have been applied to the plan (at least in draftDB).  The next successful publish will move data to published and reporting.

    More information on ASSN_TRANS_STATE_ENUM

    Thank you,

    Kiran K.

    Monday, March 3, 2014 8:59 AM