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 timesheet 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)
You may also be interested in:
Manager Time Approval Preview Custom Business Rules (Stored Procedure)
FAQ - Can we allow people to go into a negative balance up to 40 hours? (search for this topic in the Knowledge Center)
FAQ - How do I limit the total number of hours charged to a project? (search for this topic in the Knowledge Center)
<stored_procedure_name> (person_time_key IN number(15,0), |
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 store 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. 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 |
|
|
Identifies the specific timesheet being saved or submitted. This is a required input parameter. |
|
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). |
Output Parameters |
The Unanet system will be expecting two possible output parameters. |
|
The first output parameter is expected to be the return code from the stored procedure.
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. |
|
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. |
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.
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 |
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 |