Expense Report - Custom Save / Submit Validations (Stored Procedure)

Customer specific logic can be defined in a database stored procedure to perform custom validations upon expense report entries. These validations can be configured to run at the time an expense report 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: unasense.save.stored_procedure and/or unasense.submit.stored_procedure.

Note: This feature will not apply to expense requests (that is, these additional validations will only occur for expense reports).

When an expense 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 expense report 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 expense report). When running a 'submit' stored procedure, however, users encountering an Error condition are only presented with an option to re-edit the expense report (the submittal is prevented), where as in the case of a Warning condition, the user can optionally re-edit the expense report or chose to continue with the expense submittal.

Expense Import Note: When enabled, the stored procedure is only invoked when a user is using the web interface expense report screen. That is, the stored procedure is not invoked for expense reports "submitted" via the expense import mechanism.

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 (includes Warning and Error example)
- Oracle - Example
- SQL Server - Example
Stored Procedure Assistance (common items to consider when defining requirements for a stored procedure)

Syntax

<stored_procedure_name> (expense_report_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 unasense.submit.stored_procedure.include_submitter property is enabled. Likewise, passing of the saver_key parameter is only enabled when the unasense.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 unasense.submit.stored_procedure property or unasense.save.stored_procedure property. For example: unasense.submit.stored_procedure=sp_expense_submit_validation. Note: Be careful to not use the same name that you may have used for the timesheet stored procedure if you have one already installed.
Input Parameters
Person Expense Key	Identifies the specific expense report 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 expense report. 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 unasense.submit.stored_procedure.include_submitter or unasense.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.
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 expense report, 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:

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:

unasense.submit.stored_procedure=sp_expense_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. JRun, Tomcat, etc.), 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.

Examples

The following section includes an oracle and sql server version of a stored procedure that include both a Warning and an Error example.

*For specific information regarding the creation and maintenance of stored procedures, please refer to your database specific documentation.

Oracle Stored Procedure

-- Create the validation procedure.

-- The procedure checks if a non-employee has charged expense to the type AIR.

-- If true, then an error is presented to the user.

CREATE OR REPLACE procedure sp_submit_validation_expense(

expenseReportKey in number,

returnCode out number,

errorMessage out varchar)

nonEmployeeCount number;

nonExemptCount number;

expTypeKey number;

begin

returnCode := 0;

errorMessage := null;

select expense_type_key into expTypeKey from expense_type where expense_type = 'AIR';

select count(*) into nonEmployeeCount

from expense_report er

join expense_data ed on ed.expense_report_key = er.expense_report_key

join person_rate pr on pr.person_key = er.owner_key

where er.expense_report_key = expenseReportKey

and ed.expense_type_key = expTypeKey

and pr.exempt_status = 'X'

and ed.expense_date between pr.begin_date and pr.end_date;

select count(*) into nonExemptCount

from expense_report er

join expense_data ed on ed.expense_report_key = er.expense_report_key

join person_rate pr on pr.person_key = er.owner_key

where er.expense_report_key = expenseReportKey

and ed.expense_type_key = expTypeKey

and pr.exempt_status = 'N'

and ed.expense_date between pr.begin_date and pr.end_date;

if nonEmployeeCount > 0

then

returnCode := 1;

errorMessage := 'Non-employees cannot charge expenses for expense type AIR.';

else

if nonExemptCount > 0

then

returnCode := -1;

errorMessage := 'WARNING: Non-exempt employee can only charge preapproved expenses for expense type AIR.';

end if;

end;

grant all on sp_submit_validation_expense to unanet
/

SQL Server Stored Procedure

SET QUOTED_IDENTIFIER OFF

-- Drop the stored procedures if they already exist.

if exists(select name from sysobjects where name = 'sp_submit_validation_expense' AND type = 'P')

drop procedure sp_submit_validation_expense

-- Create the validation procedure.

-- The procedure checks if a non-employee has charged expense to the type AIR.

-- If true, then an error is presented to the user.

create procedure sp_submit_validation_expense

@expenseReportKey decimal(15,0),

@returnCode decimal(15,0) output,

@errorMessage varchar(2000) output

declare @nonEmployeeCount decimal(15,0);

declare @nonExemptCount decimal(15,0);

declare @expTypeKey decimal(15,0);

set @returnCode = 0;

set @errorMessage = '';

select @expTypeKey = expense_type_key from expense_type where expense_type = 'AIR';

select @nonEmployeeCount = count(*)

from expense_report er

join expense_data ed on ed.expense_report_key = er.expense_report_key

join person_rate pr on pr.person_key = er.owner_key

where er.expense_report_key = @expenseReportKey

and ed.expense_type_key = @expTypeKey

and pr.exempt_status = 'X'

and ed.expense_date between pr.begin_date and pr.end_date;

select @nonExemptCount = count(*)

from expense_report er

join expense_data ed on ed.expense_report_key = er.expense_report_key

join person_rate pr on pr.person_key = er.owner_key

where er.expense_report_key = @expenseReportKey

and ed.expense_type_key = @expTypeKey

and pr.exempt_status = 'N'

and ed.expense_date between pr.begin_date and pr.end_date;

if(@nonEmployeeCount > 0 )

begin

set @returnCode = 1

set @errorMessage = 'Non-employees cannot charge expenses for expense type AIR.'

end

else if(@nonExemptCount > 0 )

begin

set @returnCode = -1

set @errorMessage = 'WARNING: Non-exempt employee can only charge preapproved expenses for expense type AIR.'

end

grant all on sp_submit_validation_expense 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.

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 'billable' or 'non-billable' – include the exact project types being used.
- 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.