Timesheet - Custom Save / Submit Validations (Stored Procedure)

Customer specific logic can be defined in a database stored procedure to perform custom validations upon timesheet  entries.  These validations can be configured to run at the time a timesheet is saved (ie retrieved for display) or upon submittal.   Customers can have their own IT personnel create the stored procedures or work with Unanet's Professional Services team to have custom procedures created for them.  For On Premise customers, the loading and setting of necessary permissions for the stored procedures are the responsibility of your local site administrator and DBA.

To enable the stored procedure feature, your Administrator will need to supply the name of the custom stored procedure in one or both of the following properties: unatime.save.stored_procedure and/or  unatime.submit.stored_procedure.  

 

When a user clicks on the Save or Submit button, the Unanet system will first perform the standard built-in validations.  If all standard validations pass successfully, the timesheet data will be saved to the database.  At this point, the stored procedure will be called and the additional custom validations will be considered.  In this way, regardless of the success or failure of the stored procedure logic, the user's changes will be saved.

Stored procedure validations can be configured to validate for both Error or Warning conditions.   When running a 'save' stored procedure, there is no real difference between error and warning conditions, as any errors or warnings will be presented on the screen along with any built-in validation error messages (these will appear in red text above the timesheet).  When running a 'submit' stored procedure, however, users encountering an Error condition are only presented with an option to re-edit the timesheet (the submittal is prevented), where as in the case of a Warning condition, the user can optionally re-edit the timesheet or chose to continue with the time submittal.

Time Import / Time Populate Note: When enabled, the stored procedure is only invoked when a user is using the web interface timesheet screen.  That is, the stored procedure is not invoked for timesheets "submitted" via the  time import, or  bulk time populate mechanisms.  

This page covers the following topics:

Syntax

<stored_procedure_name> (person_time_key IN  number(15,0),
                         submitter_key   IN  number(15,0),      (use optional submitter_key with submit validations and saver_key with save validations)
                         return_code     OUT number(15,0),
                         error_message   OUT varchar(2000))

 

Note:  Passing of the submitter_key parameter is only enabled when the unatime.submit.stored_procedure.include_submitter property is enabled. Likewise, passing of the saver_key parameter is only enabled when the unatime.save.stored_procedure.include_saver property is enabled.

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 or unatime.save.stored_procedure property.

For example: unatime.submit.stored_procedure=sp_submit_validation.

Note: Be careful to not use the same name that you may have used for the expense report stored procedure if you have one already installed.

Input Parameters

 

  • Person Time Key

Identifies the specific timesheet being saved or submitted.

This is a required input parameter.

  • Submitter Key, or

  • Saver Key

Identifies the key of the user saving or submitting the timesheet.

This is a optional input parameter.

Note:  You should only pass this optional parameter if the stored procedure logic requires the additional input data.  When passing this optional parameter, you must also enable the corresponding  unatime.submit.stored_procedure.include_submitter or unatime.save.stored_procedure.include_saver property (as this property instructs the stored procedure call to expect the additional parameter).

Requiring the additional parameter and not providing it -- or -- providing the additional parameter while not setting the property to expect it will both result in a sql exception at runtime.

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
  • A positive value will indicate an Error condition
  • A negative value will indicate a Warning condition.  

 

When using the 'submit' validation (vs. the 'save' validation); 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 (Error or Warning), the contents of the second output parameter (Error Message) will be displayed to the screen and the submit logic will not continue.  In the case of the Warning message, the user is subsequently presented with an option to either re-edit the timesheet, or to proceed with the submittal.

  • Error Message

The second output parameter is expected to contain a 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.    

 

Configure the Stored Procedure

In order to configure the stored procedure in your system, you will need to take the following steps:

 

Important -- Please be sure to grant the necessary permissions to the unanet database user.  If the database user name is not 'unanet', you must replace the 'unanet' string in the script with your database user name.  If the permissions are not granted to the appropriate database user name, your users will get a SQL Exception when the stored procedure is invoked.

Examples

The following sample stored procedures could be used to validate that users have at least 40 hours on their timesheet.  There are two versions, to illustrate the syntax for both Oracle and SQL Server.

*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
    p_quantity number;
begin
    p_return_code := 0;
    p_error_message := null;
    select sum(quantity)
    into p_quantity
    from person_time_data
    where person_time_key = p_person_time_key;
    if p_quantity > 40
    then
        p_error_message := 'More than 40 hours have been reported for this timesheet.<br>'
        || 'No more than 40 hours may be reported for any given time period.<br>'
        || 'Please correct your timesheet before resubmitting.';
        p_return_code := 1;
    end if;
end;
/

grant all on sp_submit_validation to unanet
/

 

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
    select @returnCode = 0;
    select @errorMessage = null;
    declare @hours decimal(15,2)
    select @hours = sum(quantity)
    from person_time_data
    where person_time_key = @personTimeKey
    if (@hours > 40)
    begin
    select @errorMessage = 'More than 40 hours have been reported for this timesheet.<br>'
    + 'No more than 40 hours may be reported for any given time period.<br>'
    + 'Please correct your timesheet before resubmitting.'
    select @returnCode = 1
    end
go
grant all on sp_submit_validation to unanet

 

Stored Procedure Assistance

If you are requesting assistance from Unanet to create a stored procedure, consider including the following when submitting a request.

 

Related Topics