|
|
|
|
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: Save Stored Procedure (unatime.save.stored_procedure) and/or Submit Stored Procedure (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 (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)
Configure the Stored Procedure (set up a stored procedure in your system)
Example Stored Procedures (validate users have at least 40 hours entered per week)
Stored Procedure Assistance (common items to consider when defining requirements for a stored procedure)
|
<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. |
In order to configure the stored procedure in your system, you will need to take the following steps:
Edit and Install Stored Procedure -- Have your DBA run the stored procedure database script. Make sure it does not generate any errors. If it does, please contact Unanet Customer Support.
Important: If Unanet assisted with the creation of the stored procedure, you may need to edit the username at the bottom of the script if you use anything other than 'unanet' as your database schema owner.
Setup Properties -- Add the appropriate stored procedure name to the desired stored procedure property via the Admin >> Properties screen, or if editing the value in the unanet.properties file it may appear as follows:
Submit Stored Procedure (unatime.submit.stored_procedure)=sp_submit_validation
You may also need to set the property related to passing the submitter or saver key if the procedure requires that parameter.
Restart Servlet Engine -- If you are setting the property via the unanet.properties file you will need to stop and restart your servlet engine (e.g. Tomcat), otherwise, if setting the property via the Admin >> Properties screen this step is not necessary.
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.
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 |
If you are requesting assistance from Unanet to create a stored procedure, consider including the following when submitting a request.
Which version of Unanet are you running?
Which database are you using (oracle or sql server)?
Specify whether the various validations should result in a WARNING or ERROR condition.
Provide the exact wording of the messages you would like to present to the users.
Document and confirm the logic to be implemented, please include all details, for example:
When referring to validations of ’regular time’ vs ’overtime’ – include the exact pay codes that represent regular time, overtime, etc.
When limiting the condition to a set of users or projects – spell out the fields and values used in this determination.
Consider negative conditions (ie when specifying something like IF ABC THEN XYZ --- consider what should happen if it’s not ABC)
Consider whether logic should change for someone hiring on mid-week, etc.
