locked
MS Project Server 2013 - Which table has information about the delegatee? RRS feed

  • Question

  • Hello,

    Let's figure out this scenario: Person A and Person B are users of EPM.

    Task 1 is assigned to Person A. He/she is the resource responsible.

    Person B is delegatee of person A, in other words, person B also has the required access to update the task 1 using his delegation (representing person A).

    Task 1 is updated and we can find in the table [MSP_ASSIGNMENT_TRANSACTIONS] the following fields:

    [ASSN_TRANS_DELEGATEE_RES_UID]
    [ASSN_TRANS_SUBMITTER_RES_UID]
    [ASSN_TRANS_APPROVER_RES_UID]

    How can I discover if the task 1 was updated by Person A or Person B, once that the field [ASSN_TRANS_DELEGATEE_RES_UID] is filled with NULL in most part of the lines?

    Note: No matter who updates the task, the field [ASSN_TRANS_SUBMITTER_RES_UID] will always be filled with Person A.

    Is there another table where I can find information about the delegatee (person A or B) that actually performed the update?

    Thanks in advance,

    Alex

    Thursday, October 8, 2015 7:32 PM

Answers

  • Hi,

    sorry for falling in ....

    Michael is absoutely correct, queries on schema pub are not supported - so use NO LOCK and be aware, that your report may stop working after applying a patch. Microsoft states clearly, that schema changes other than dbo (=Reporting) can happen at any time.

    Syed is also correct, delegate sessions are not tracked for task updates.

    However, if you start using Timesheets in Single Entry Mode (and force your users to use timesheets e.g. by hiding task view) and turn timesheet auditing on, you can see who was doing the changes by following query:

    SELECT     
              TSOwner.ResourceName AS Owner
            , TSEditor.ResourceName AS Editor
            , TSA.MOD_DATE
    FROM         pub.MSP_TIMESHEET_ACTUAL_AUDIT AS TSA WITH (NOLOCK)
          INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
                ON TSA.TS_LINE_UID = TSL.TimesheetLineUID 
          INNER JOIN dbo.MSP_EpmResource_UserView AS TSOwner 
                ON TSL.ResourceUID = TSOwner.ResourceUID 
          INNER JOIN dbo.MSP_EpmResource_UserView AS TSEditor 
                ON TSA.RES_UID = TSEditor.ResourceUID

    Perhaps a possibilty for you?
    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support! !!!!!!!! Join the Project Virtual Conference 2015 from Oct 22-23, 2015 with a lot of support from the Microsoft Project Community - http://projectvirtualconference.com/ !!!!!!!!

    Monday, October 12, 2015 5:15 AM

All replies

  • Hi Alex,

      You are right, what you have mentioned above about the id. If you look how the system work and behaves that would make perfect sense.

      When a user is delegated, the delegatee needs to activate the profile before he / she can perform any task. Once the profile is activated the delegatee virtually becomes the person for whom he is delegated for. 

      Consider above fact, the system shows the GUID. This is as per my knowledge. 

    Regards,

    Faizan.


    Regards, Syed Faizan ur Rehman, CBPM®,PRINCE2®, MCTS

    Sunday, October 11, 2015 6:37 AM
  • the tables are in the published database or schema.  Microsoft does not support reading data from this schema and if you do put a NO READ LOCK on SELECT Clause.

    The table is pub.MSP_RESOURCE_DELEGATES


    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Monday, October 12, 2015 3:29 AM
  • Hi,

    sorry for falling in ....

    Michael is absoutely correct, queries on schema pub are not supported - so use NO LOCK and be aware, that your report may stop working after applying a patch. Microsoft states clearly, that schema changes other than dbo (=Reporting) can happen at any time.

    Syed is also correct, delegate sessions are not tracked for task updates.

    However, if you start using Timesheets in Single Entry Mode (and force your users to use timesheets e.g. by hiding task view) and turn timesheet auditing on, you can see who was doing the changes by following query:

    SELECT     
              TSOwner.ResourceName AS Owner
            , TSEditor.ResourceName AS Editor
            , TSA.MOD_DATE
    FROM         pub.MSP_TIMESHEET_ACTUAL_AUDIT AS TSA WITH (NOLOCK)
          INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
                ON TSA.TS_LINE_UID = TSL.TimesheetLineUID 
          INNER JOIN dbo.MSP_EpmResource_UserView AS TSOwner 
                ON TSL.ResourceUID = TSOwner.ResourceUID 
          INNER JOIN dbo.MSP_EpmResource_UserView AS TSEditor 
                ON TSA.RES_UID = TSEditor.ResourceUID

    Perhaps a possibilty for you?
    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support! !!!!!!!! Join the Project Virtual Conference 2015 from Oct 22-23, 2015 with a lot of support from the Microsoft Project Community - http://projectvirtualconference.com/ !!!!!!!!

    Monday, October 12, 2015 5:15 AM