Setting Timesheet Status with SQL

[Legacy User]

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

[Robert_Ensinger]

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

[Owen_R]

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