Financial Documents - Custom Business Rules via Save / Submit / Post Validations (Stored Procedure)

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, 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, 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.

This page covers the following topics:

Syntax (review the syntax including the parameters that are communicated to the stored procedure)
Example Stored Procedures
- Oracle - Example
- SQL Server - Example

Syntax

<stored_procedure_name> (fin_document_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))

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. For example: unanet.financials.vi.save.stored_procedure=vi_submit_validation.
Input Parameters
Financial Document Key	Identifies the specific document being saved, submitted, or posted. This is a required input parameter.
Submitter Key, or Saver Key	Identifies the key of the user saving or submitting the document.
Output Parameters	The Unanet system will be expecting two possible output parameters.
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 document, 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.

Examples

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.

Oracle Stored Procedure

-- 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)

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

SQL Server Stored Procedure

-- 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

-- 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

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

grant all on sp_submit_validation_vi to unanet