Clarity PPM

Expand all | Collapse all

Setting Timesheet Status with SQL

  • 1.  Setting Timesheet Status with SQL

    Posted 07-08-2011 01:01 PM
    Hi,

    We need to set the Timesheet status using an SQL Stored Procedure.

    We select a lista of timesheet of the resources that is part ot the project team for an especific projet manager.

    Using this list the Manager selects the timesheets to Approve and submitts the list to a routine that sets the Timesheet Status ( PRTIMESHEET.PRSTATUS) to 3. This will be an HTML Portlet that calls an .NET page to do the JOB.

    Our tests shows that this is the only action we have to take. Is there another table that could affect the setting of the Timesheet Status ?

    Using the Timesheets - Approve All access right is not an option since All Managers gets this right. Using the Out-of-box Timesheet Project Manager Approval workflow is not an option since we don´t want the Project Manager to deal with the timesheet using the Action Itens. The timesheets must be approved in batch.

    All this could be done easly if the timesheet portlet had an Project Manager and Project Code Filter.

    Regards,

    Joni


  • 2.  RE: Setting Timesheet Status with SQL

    Posted 07-09-2011 11:32 AM
    Hi Joni.
    Yes, setting the PRTIMESHEET.PRSTATUS to whatever value 'Approved' is (I'm not in front a Clarity system right now) is all you'll need to do. If I understand your use case below properly, a manager needs to approve time for their team members in batches by project.

    Just FYI - since all of these parameters can be sent into the Stored Procedure, this could be done in the standard OOTB Jobs interface. The HTML portlet/.NET page to fire the job sounds excessive - unless there is additional functionality this provides that you haven't mentioned.

    HTH,
    -R


  • 3.  RE: Setting Timesheet Status with SQL

    Posted 07-11-2011 05:22 AM
    We do automatic approvals via SQL procedure/Clarity job call successfully each week, doing a blanket approval of any submitted timesheet. Here's the code for the SP bit in case it's of any use
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [niku].[AutoApprove] 
    
    
    @P_JOB_RUN_ID NUMERIC, 
    
    @P_JOB_USER_ID NUMERIC=1 
    
      
    AS
    DECLARE approve_time CURSOR
    FOR select prid from
     prtimeperiod  where
     prstart<getdate()-7 
      DECLARE @au_lname numeric
      OPEN approve_time
      
      WHILE 1=1
     
      BEGIN 
    FETCH approve_time INTO @au_lname
    if @@FETCH_STATUS < 0
    break
    
    update prtimesheet set prstatus=3 where prtimeperiodid=@au_lname and prstatus=1
     
      END 
    CLOSE approve_time
    DEALLOCATE approve_time
    GO
    
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO