Timesheet - Custom Business Rules via Save / Submit Validations (Stored Procedure)

Please see this page first - Custom Business Rules (Stored Procedures) - for details common to all stored procedures, then read this page for details specific to Timesheet Custom Business Rules.

To enable the stored procedure feature, your Unanet Administrator will need to supply the name of the custom stored procedure in one or both of the following properties:

Save Stored Procedure (unatime.save.stored_procedure) and/or Submit Stored Procedure (unatime.submit.stored_procedure).

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

If your site is using a stored procedure prepared by the Unanet Technical Services Group, note that they often earmark the Person UDF #5 for the purposes of controlling stored procedure Test Mode indication.

This page covers the following topics:

Syntax (review the syntax including the parameters that are communicated to the stored procedure)
Example Stored Procedures (validate users have at least 40 hours entered per week)
- Oracle - Example
- SQL Server - Example

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 Include Submitter when calling Submit Stored Procedure (unatime.submit.stored_procedure.include_submitter) property is enabled. Likewise, passing of the saver_key parameter is only enabled when the Include Saver when calling Save Stored Procedure (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 stored procedure is defined by the customer. This name must match the value that is set with the Submit Stored Procedure (unatime.submit.stored_procedure) property or Save Stored Procedure (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 Include Submitter when calling Submit Stored Procedure (unatime.submit.stored_procedure.include_submitter) or Include Saver when calling Save Stored Procedure (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 an 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.

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