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

 

You may also be interested in:

 


Syntax

<stored_procedure_name> (fin_document_key IN  number(15,0),

                         submitter_key   IN  number(15,0),      
                         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)

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

/

 

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

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

 

 

Related Topics