Purchasing Documents - Custom Business Rules via Save / Submit / Approve 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 Purchasing 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, submitted, or approved.

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

Purchase Order Save Stored Procedure (unanet.purchasing.po.save.stored_procedure) and/or Purchase Order Submit Stored Procedure (unanet.purchasing.po.submit.stored_procedure)

Purchase Requisition Save Stored Procedure (unanet.purchasing.pr.save.stored_procedure) and/or Purchase Requisition Submit Stored Procedure (unanet.purchasing.pr.submit.stored_procedure)

When a user clicks on the Save or Submit button, or approves a purchasing document, 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. The custom validations will be performed on the summary and detail line pages.

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

Note: The stored procedures are not invoked for Purchase Orders "submitted" via the imports.

Note: Purchasing document approvals use the same stored procedures as the submit actions.

Concerning approvals, the custom validations are performed when a user hits the approval icon from an approval queue page, and when displaying the document view page for approvers, and when the composition of the list of approvers changes during the approval process (e.g., if a purchasing manager is removed from the approval group, or the customer approver on the project is changed, or the project approver on the project is changed).

If there is an error on the approval view page and the user clicks approve, the document will be disapproved. For bulk / automatic approvals, warnings are ignored, and the document will be disapproved if there are errors.

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> (purchasing_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.purchasing.po.save.stored_procedure=po_submit_validation.
Input Parameters
Purchasing 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 PO owning 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_po(

docKey in number,

submitterKey in number,

returnCode out number,

errorMessage out varchar)

mismatchOrg number;

begin

returnCode := 0;

errorMessage := null;

select count(*) into mismatchOrg

from purchase_order po

left join project p

on (p.project_key = po.project_key)

where po.po_key = docKey

and po.owning_org_key != p.owning_customer_key;

if mismatchOrg > 0

then

returnCode := 1;

errorMessage := 'PO owning organization does not match project''s owning organization.';

end if;

end;

grant all on sp_submit_validation_po 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_po' AND type = 'P')

drop procedure sp_submit_validation_po

-- Create the validation procedure.

-- The procedure checks if a PO owning 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_po

@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 purchase_order po

left join project p

on (p.project_key = po.project_key)

where po.po_key = @docKey

and po.owning_org_key != p.owning_customer_key

if(@mismatchOrg > 0 )

begin

set @returnCode = 1

set @errorMessage = 'PO owning organization does not match project''s owning organization.'

end

grant all on sp_submit_validation_po to unanet