Custom Business Rules (Stored Procedures)

Customer specific business rules can be implemented to report on or enforce specific data validations.  These rules can be implemented via a database stored procedure.  These procedures can be configured to run at the time an item, such as a timesheet , is saved or upon submittal. Customers can work with Unanet’s Technical Services Group to have custom business rules implemented in one or more stored procedures.  Alternatively, they can have their own technical resources create the stored procedures provided they understand Oracle PL/SQL (for Cloud subscribers OR On Premise customers utilizing the Oracle database) or SQL Server Transact-SQL (for On Premise customers using the MS SQL Server database) and the Unanet data model.

In general, there are two steps for setting up the stored procedures: install the stored procedure, then configure the property setting to point to the stored procedure.

For On Premise customers, your IT department installs the stored procedures. The loading and setting of necessary permissions for the stored procedures are the responsibility of your local site administrator and DBA.

For Cloud customers, Unanet installs the stored procedures. An administrative Unanet user will then need to configure the property settings on the Admin>>Properties page in Unanet so that the system will call the specified stored procedures when appropriate.

When you click on the Save or Submit button, the Unanet system will first perform the standard built-in validations.  If all standard validations pass successfully, the 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, your 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 at the top of the screen).  When running a 'submit' stored procedure, however, users encountering an Error condition are only presented with an option to re-edit the item (the submittal is prevented), whereas in the case of a Warning condition, the user can optionally re-edit the item or choose to continue with the procedure.

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:

• Configure the Stored Procedure (more details about setting up a stored procedure in your system)

• Stored Procedure Assistance (common items to consider when defining requirements for a stored procedure)

You may also be interested in:

• Time Custom Business Rules (Stored Procedure)

• Expense Custom Business Rules (Stored Procedure)

• Manager Time Approval Preview Custom Business Rules (Stored Procedure)

• Financial Documents Custom Business Rules (Stored Procedure)

• FAQ - What are Business Rules Enforcement and Validation Stored Procedures? (search for this topic in the Knowledge Center)

Configure the Stored Procedure

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

• Edit and Install Stored Procedure:

• • For On Premise customers, 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.

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 an SQL Exception when the stored procedure is invoked.

• • For Cloud customers, Unanet will install the stored procedure.
     

• Setup Properties:

• • For On Premise customers, have your Unanet administrator 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 (timesheet example):
 
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.

• • If you are working with a Unanet consultant, they should be able to assist you with setting these properties.
     

• Restart Servlet Engine (for On Premise customers only) -- 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.

*** A Note about Properties

As mentioned above, there are two ways to set these properties.  The first way is to simply add the stored procedure name in the Admin>>Properties section of the Unanet UI.  The second is to add the applicable properties in the unanet.properties file.  Using the UI method will allow you to quickly and easily change (or temporarily remove) the stored procedures should the need arise.  No server downtime due to restarts is required.  If, on the other hand, you have a larger number of Unanet users with the administrative role and would prefer to prevent any changes to the stored procedure properties, you should use the unanet.properties method which would prevent any changes to the configuration by anyone in the UI.

Stored Procedure Assistance

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? (On Premise only)

• Which database are you using (Oracle or SQL Server)? (On Premise only)

• 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 (for a timesheet stored procedure):

• • 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 (i.e. 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.