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 Expense Report 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 (unasense.save.stored_procedure) and/or Submit Stored Procedure (unasense.submit.stored_procedure).
Note: This feature will not apply to expense requests (that is, these additional validations will only occur for expense reports).
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.
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 (includes Warning and Error example)
You may also be interested in:
FAQ - Can I prevent a user from submitting an expense with the wrong project type or payment method? (search for this topic in the Knowledge Center)
<stored_procedure_name> (expense_report_key IN number(15,0), |
Note: Passing of the submitter_key parameter is only enabled when the Include Submitter when calling Submit Stored Procedure (unasense.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 (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 Submit Stored Procedure (unasense.submit.stored_procedure) property or Save Stored Procedure (unasense.save.stored_procedure) property. 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 |
|
|
Identifies the specific expense report being saved or submitted. This is a required input parameter. |
|
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 Include Submitter when calling Submit Stored Procedure (unasense.submit.stored_procedure.include_submitter) or Include Saver when calling Save Stored Procedure (unasense.save.stored_procedure.include_saver) property (as this property instructs the stored procedure call to expect the additional parameter). |
|
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 expense report, 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 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.
-- -- 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) as 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 if; end; / grant all on sp_submit_validation_expense to unanet |
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 go -- -- 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 as 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 go grant all on sp_submit_validation_expense to unanet |