Timesheet Submit Stored Procedure

This feature is enabled if the unatime.submit.stored_procedure property is defined. This setting is used to define the name of a database stored procedure that should be called prior to the submission of a timesheet.

Customer specific logic could be defined in the stored procedure to perform installation specific validations upon a timesheet, prior to its submission for approval. Unanet does not provide the stored procedure itself, but simply has the capability to call the stored procedure if desired by the customer. Creating, saving and establishing the necessary permissions for the stored procedure is the responsibility of the site administrator.

When a timesheet user clicks on the Submit button, the Unanet system will first perform the standard validations, and save changes made upon the success of the validations. At this point, the stored procedure may be called (if configured properly). In this way, regardless of the success or failure of the stored procedure logic, the user's changes will be saved.

Note: When configured, the stored procedure is only invoked when a user submits a timesheet via the standard timesheet screen. The stored procedure is not invoked for timesheets "submitted" via the time import, bulk time populate or IVR mechanisms.

Syntax

<stored_procedure_name> (person_time_key IN  number(15,0),
                         return_code     OUT number(15,0),
                         error_message   OUT varchar(2000))

The following rules must be followed when using this feature.

Stored Procedure Name - The name of the store procedure is defined by the customer. This name must match the value that is set with the unatime.submit.stored_procedure property. For example: unatime.submit.stored_procedure=sp_submit_validation.

Input Parameters - Unanet will be passing only one input parameter for the stored procedure

Person Time Key - Identifies the specific timesheet being submitted.

Output Parameters - The Unanet system will be expecting two possible output parameters.

Return Code - The first output parameter is expected to be the return code from the stored procedure. A value of 0 (zero) will indicate success and a non-zero numeric value will indicate failure. In the case that a zero (0) (success) is returned, the Unanet submit logic will continue. In the case of a non-zero return code (failure), the contents of the second output parameter (Error Message) will be displayed to the screen and the submit logic will not continue.

Error Message - The second output parameter is expected to contain some type of message to accompany the non-zero return code, presumably to explain the reason for failure. If a non-zero return code is returned, but no value is supplied in the message parameter, Unanet will supply a default message indicating no custom message provided.

Examples

The following is a stored procedure for example purposes only. There are two versions (one for SQL Server and one for Oracle). This particular example validates that no more than 8 hours can be charged on a particular work day.

*For specific information regarding the creation and maintenance of stored procedures, please refer to your database specific documentation.

Oracle Stored Procedure

create or replace procedure sp_submit_validation(
   p_person_time_key in number,
   p_return_code out number,
   p_error_message out varchar)
as
   cursor too_many_hours_cur is
      select work_date, sum(quantity)
      from person_time_data
      where person_time_key = p_person_time_key
      group by work_date
      having sum(quantity) > 8
      order by work_date;
begin
   p_return_code := 0;
   p_error_message := null;
   for p_day in too_many_hours_cur
   loop
      if p_return_code = 0
      then
         p_error_message := 'More than 8 hours have been reported for the following dates: ' ||
            to_char(p_day.work_date, 'MM-DD-YYYY');
      else
         p_error_message := p_error_message || ', ' || to_char(p_day.work_date, 'MM-DD-YYYY');
      end if;
      p_return_code := p_return_code + 1;
   end loop;

   if p_return_code > 0
   then
      p_error_message := p_error_message || '.  No more than 8 hours may be reported for any given day.';
      p_error_message := p_error_message || '  Please correct your timesheet before resubmitting.';
   end if;
end;

SQL Server Stored Procedure

if exists(select name from sysobjects where name = 'sp_submit_validation' AND type = 'P')
   drop procedure sp_submit_validation
go

create procedure sp_submit_validation
   @personTimeKey decimal(15,0),
   @returnCode    decimal(15,0) output,
   @errorMessage  varchar(2000) output
as
   declare @workDate datetime;
   declare @hours decimal(15,2);
   declare too_many_hours_cur cursor for
      select work_date, sum(quantity)
      from person_time_data
      where person_time_key = @personTimeKey
      group by work_date
      having sum(quantity) > 8
      order by work_date;

   select @returnCode = 0;
   select @errorMessage = null;
   open too_many_hours_cur;
   fetch next from too_many_hours_cur into @workDate, @hours;
   while @@fetch_status = 0
   begin
      if (@returnCode = 0)
      begin
         select @errorMessage = 'More than 8 hours have been reported for the following dates: ' +
            convert(varchar(8), @workDate, 1);
      end
      else
      begin
         select @errorMessage = @errorMessage + ', ' + convert(varchar(8), @workDate, 1);
      end
      select @returnCode = @returnCode + 1;
      fetch next from too_many_hours_cur into @workDate, @hours;
   end
   close too_many_hours_cur;
   deallocate too_many_hours_cur;

   if (@returnCode > 0)
   begin
      select @errorMessage = @errorMessage + '.  No more than 8 hours may be reported for any given day.';
      select @errorMessage = @errorMessage + '  Please correct your timesheet before resubmitting.';
   end
go

Return to Unanet Table of Contents page