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 Financial Document Custom Business Rules.
Customer specific business rules can be implemented to enforce specific data validations. These rules can be implemented via a database stored procedure. These procedures can be configured to run at the time a document is saved, approved (vendor invoices only), upon submittal, or upon post. They can also be triggered when bulk posting (e.g. Vendor Invoice bulk post), and when copying Journal Entries.
To enable the stored procedure feature, your Unanet Administrator will need to supply the name of the custom stored procedure in the following properties (depending on your needs):
Vendor Invoice Save Stored Procedure (unanet.financials.vi.save.stored_procedure) and/or Vendor Invoice Submit/Post Stored Procedure (unanet.financials.vi.submit.stored_procedure)
Vendor Payment Save Stored Procedure (unanet.financials.vp.save.stored_procedure) and/or Vendor Payment Submit/Post Stored Procedure (unanet.financials.vp.submit.stored_procedure)
Customer Payment Save Stored Procedure (unanet.financials.cp.save.stored_procedure) and/or Customer Payment Submit/Post Stored Procedure (unanet.financials.cp.submit.stored_procedure)
Deposit Save Stored Procedure (unanet.financials.ds.save.stored_procedure) and/or Deposit Submit/Post Stored Procedure (unanet.financials.ds.submit.stored_procedure)
Journal Entry Save Stored Procedure (unanet.financials.je.save.stored_procedure) and/or Journal Entry Submit/Post Stored Procedure (unanet.financials.je.submit.stored_procedure)
When a user clicks on the Save, Submit, or Post button, or approves a vendor invoice, the Unanet system will perform the standard built-in validations and 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 at the top of the screen). Please see the detailed behavior using the table below. Behavior varies depending on the action.
Action | Code | Result |
Save or Save/Next button | Warning or Error | document is saved |
Submit button | Warning | presents user with option to re-edit the document or choose to continue with the submittal |
Submit button | Error | presents user with option to re-edit the document (submittal is prevented) |
Submit/Next or Post/Next | Warning | presents user with option to re-edit the document or choose to continue with the submittal or posting |
Submit/Next or Post/Next | Error | submittal or posting is prevented (document status stays INUSE) |
Bulk Post (Post in spite of custom warnings checkbox in Posting Options section on Search page is checked) | Warning | continue with posting |
Bulk Post (Post in spite of custom warnings checkbox in Posting Options section on Search page is unchecked) | Warning | posting is prevented (document status stays SUBMITTED) |
Bulk Post (regardless of Post in spite of custom warnings checkbox in Posting Options section on Search page) | Error | posting is prevented (document status stays SUBMITTED) |
copy Journal Entry | Warning or Error | journal entry created with status INUSE |
Note: The stored procedures are not invoked for documents "submitted" via the imports, nor for Bulk Vendor Payment Creation or Create Vendor Invoices from Expense Reports.
Note: Vendor invoice approvals use the same stored procedures as the save/submit/post actions.
This page covers the following topics:
Syntax (review the syntax including the parameters that are communicated to the stored procedure)
You may also be interested in:
Quick Topic - Financial Validation Stored Procedures (search for this topic in the Knowledge Center)
<stored_procedure_name> (fin_document_key IN number(15,0), submitter_key IN number(15,0), |
The following rules must be followed when using this feature:
Stored Procedure Name |
The name of the stored procedure is defined by the customer. This name must match the value that is set with the property setting.
|
Input Parameters |
|
|
Identifies the specific document being saved, submitted, or posted. This is a required input parameter. |
|
Identifies the key of the user saving or submitting the document. |
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 document, 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 the vendor invoice organization matches the project's owning organization. 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 the validation procedure. -- The procedure checks if a vendor invoice organization selected does not -- match the project's owning organization -- If true, then an error is presented to the user. --
CREATE OR REPLACE procedure sp_submit_validation_vi( docKey in number, submitterKey in number, returnCode out number, errorMessage out varchar) as mismatchOrg number; begin returnCode := 0; errorMessage := null;
select count(*) into mismatchOrg from fin_document fd left join fin_document_detail fdd on (fd.fin_document_key = fdd.fin_document_key) left join fin_document_detail_project fddp on (fdd.fin_document_detail_key = fddp.fin_document_detail_key) left join project p on (fddp.project_key = p.project_key) where fd.fin_document_key = docKey and fdd.organization_key != p.owning_customer_key;
if mismatchOrg > 0 then returnCode := 1; errorMessage := 'Vendor invoice organization does not match project''s owning organization.'; end if; end; / grant all on sp_submit_validation_vi to unanet / |
-- Drop the stored procedures if they already exist. --
if exists(select name from sysobjects where name = 'sp_submit_validation_vi' AND type = 'P') drop procedure sp_submit_validation_vi go
-- -- Create the validation procedure. -- The procedure checks if a vendor invoice organization selected does not -- match the project's owning organization -- If true, then an error is presented to the user. --
create procedure sp_submit_validation_vi @docKey decimal(15,0), @submitterKey decimal(15,0), @returnCode decimal(15,0) output, @errorMessage varchar(2000) output as declare @mismatchOrg decimal(15,0);
set @returnCode = 0; set @errorMessage = '';
select @mismatchOrg = count(*) from fin_document fd left join fin_document_detail fdd on (fd.fin_document_key = fdd.fin_document_key) left join fin_document_detail_project fddp on (fdd.fin_document_detail_key = fddp.fin_document_detail_key) left join project p on (fddp.project_key = p.project_key) where fd.fin_document_key = @docKey and fdd.organization_key != p.owning_customer_key
if(@mismatchOrg > 0 ) begin set @returnCode = 1 set @errorMessage = 'Vendor invoice organization does not match project''s owning organization.' end go grant all on sp_submit_validation_vi to unanet |