ECM Best Practices: Integration with ERP

Integration with ERP is something most SE’s (Systems Engineers) and PMs (Project Managers) should keep in mind when designing a project.  ERP, or Enterprise Resource Planning, is the process of using modularized software and hardware within a centralized network and datastore.  In regards to ECM, proper integration with ERP is important for maintaining data integrity with the larger organization.  ImageSource has successfully incorporated ERP Integration with many of it’s customer partners.

As an example, ERP Integration can be done within an Oracle IPM Process Workflow, using a combination of scripts and forms to access the centralized data store for use within an IPM Package.  The centralized data is usually populated and maintained by sources within the organization, but external to Oracle IPM Process Workflow.

The following examples demonstrate how ERP Integration can be used to maintain data integrity.  In this example, we have a workflow package that requires 3 pieces of Vendor Information: the Name, Address and ID Number.  Information about all of our example organization’s Vendors is located in a pre-existing database.  We can harness this data within the workflow, using some techniques demonstrated below:

Oracle IPM Workflow Screenshot

The above screenshot illustrates one way that ERP Integration can be handled within a process workflow. The package would enter the workflow containing a rudimentary amount of metadata gathered during  the Document Capture phase.  It would then be processed by the Vendor Lookup Script, which would use data in the package (like the Vendor Name) to query the centralized data store for the appropriate vendor information (like Address and ID Number).  If matching data is successfully found, it would then populate the package with it.  From there, the package would move on to the Review Queue where all of that data would be made viewable to the user.

ERP Integration can also be handled within an IPM Process Workflow form.  Observe the ERP data lookup demonstrated below:

IPM Process Form with data populated from the Vendor Lookup Script.

If the user needs to change the Vendor Information, they can do so by clicking on the Vendor Name dropdown.  The dropdown list is populated with vendor names pulled from the centralized data store.

List of Vendor Names from the centralized data store.

Upon selecting a new Vendor Name, the workflow form performs a query against the data store for the matching vendor information, as pictured below.

Vendor Information after a lookup in the centralized data store.

As you can see, the only data that was needed was the Vendor Name.  The Vendor ID and Address were updated to reflect the change on the form.  This kind of ERP Integration keeps data intact and makes metadata capture easier for the user, as they do not need search through piles of paper or external applications to find the appropriate information.

Richard Franzen
ImageSource, Inc.

IPM Process Scripts Tips and Tricks

Process workflow scripts, the very mentioning can make a developer shudder.  However, scripts are the unsung heroes of the Process workflow and have been an integral part of ImageSource’s Customer Solutions.  While the Oracle IPM Process workflow now supports using .NET dlls as script events, that was not always the case.  VBScript code was originally used to handle workflow events and that option for such is still around.  There are a few reasons why you might still need to code a process script in VBScript, either because it is an upgrade of legacy code, the system is a pre 7.7 version of IPM or if the required custom functionality is so small that writing a script is much quicker than a full blown .NET Module.

Below are some helpful tips and tricks if you find yourself writing a VBScript workflow event.

Write Logs That Make Sense
While flagging parts of the code as A, B and C might make sense to you while developing the script, trying to remember what they meant years down the line (like for an enhancement or bug fix) will be a futile exercise.  Better yet, try explaining cryptic log messages to the Systems Engineer or another developer.  After they’ve finished giving you annoyed looks, it might be time to make the logging messages understandable to another human being.  When writing logs, output something useful like current field values, sql parameters or the result of conditional operations.  Just make sure not to log sensitive data and also provide an option to turn logging off if it is not needed.

Error Handling Is Key
By default, IPM scripts use the following option: On Error Resume Next.  This means that if an error occurs in the code, it will continue operating until the error is handled or the code finishes.  Normally, error handling code is put after code like SQL calls, file system operations and usage of the IPM SDK. However, sometimes developers can get lazy and forget to put error handling in the code where it would be useful, only to have it handled later on with an error log message that does not make sense.  For example take a look at the following code:

oCon.Open "File Name=C:\process\connection.udl"
oRS.Open "select * from DATA_TABLE_1 where ID=" & iId, oCon, Readonly, Readonly
If Not oRS.EOF Then
    iValue = oRS("VALUE_1")
End If
oRS.Close

oCon.Execute  "insert into DATA_TABLE_2 (VALUE_1) values (" & iValue & ")"
If Eval("Err.Number <> 0") Then
     objExecutionContext.ErrorDescription = objExecutionContext.ErrorDescription & _
          "Example Script: Error inserting data into DATA_TABLE_2 - " & Err.Description & vbCrLf
     oCon.Close
     Set oCon = Nothing
     Exit Sub
End If

If an error were to occur at either the open connection or select data steps, the error log would still report that there was an error inserting data into DATA_TABLE_2.  A better solution would have error handling checks after the open connection and sql select lines of code.

Remember To Set Objects
Working in the .NET environment, you tend to forget some of the nuances of writing unmanaged code.  One of these is remembering to set objects when they are assigned in VBScript.  This can be the source of much unneeded frustration when the code continually errors out without a very helpful reason why.  Always keep in mind that when not assigning a primitive variable, always use Set.

Clean Up After Yourself
Another thing to keep in mind when writing scripts is to clean up your variables when you are done using them.  This is especially important for preventing memory leaks in the script, which might have to run 24/7.  Always remember to set your objects to nothing, to close all sql connections and recordsets, and even clear string values when they are not needed.

Be Careful With String Concatenation
VBScript string concatenation has an N-Squared cost.  Repeatedly concatenating a string within a loop is a very expensive operation.  While okay for small strings, it is not recommended for building out long strings, like from database information.  The suggested alternative to the built in concatenation is to preallocate memory for the concatenation operation.  A more detailed solution can be found at Microsoft’s support site.

Hopefully this advice will help you in the world of IPM Process script writing.

Richard Franzen
Developer
ImageSource, Inc.