Welcome > Configure Kofax AP Agility > Manage Invoice Processing > Project and field configurations > Purchase order number validations > Configure database validations using stored procedures

Configure database validations using stored procedures

By default, the system accesses purchase order header and line item tables using an SQL select call. However, it is possible to use a custom stored procedure instead, which can improve data security. The stored procedure should be written to return a record set in the same way as a regular SQL select statement. The system can be configured to use a custom stored procedure for both purchase order header and line item validations.

You can configure database validations to use a stored procedure by following these steps.

  1. Using the menu, navigate to Settings > Invoice Processing > Capture Profiles > PO Number Settings > PO Number Validation tab.
  2. Select Use Stored Procedure.
  3. Enter the custom stored procedure name in the Stored Procedure Name setting.
  4. Select the relevant connection from the Database Connection Name setting.
  5. Save your changes.
  6. Navigate to Settings > Invoice Processing > Global Settings > Stored Procedure Settings.
  7. Set the formal interface parameter name in the stored procedure to represent an Kofax AP Agility field. Each of the following parameters is assigned a type, and if that type is VARCHAR, a length is assigned as well.

    • BOOLEAN

    • INT

    • DATE

    • DOUBLE

    • VARCHAR

    Note The parameter value, if set to represent a field, is case sensitive and must be the technical name of the field, such as PONumber, CompanyCode, DocumentType, Total, and so on. If the value entered is not the technical name of a field, the system assumes it is a hard-coded value. Input parameters passed to the stored procedure, such as the values passed from Kofax AP Agility, should have a direction of I. Output parameters coming from the stored procedure have a direction of O. If the direction is missing or invalid, the parameter is considered as an output parameter.
  8. Save your changes.

    Once the parameters are defined, assign them to the corresponding purchase order header stored procedure.