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.

Web Content Management System Checklist

Web Content Management (WCM) is the practice of creating, controlling and publishing web content using specialized tools.  The specialized tools are collectively know as a Web Content Management System or WCMS.   Most entities (business, municipalities, hospitals, etc) these days have some form of web presence, which is typically a dedicated website.  Most people think of a website as a collection of static web pages.   This was the case five or six years ago, but today most websites have functional requirements that go well beyond a simple What You See Is What You Get (WYSIWYG) website.   I have worked with several customers who still use technology that is focused on simply creating and publishing web pages.  However, the customer’s needs always go well beyond simply pushing HTML pages onto the web.  This post is intended to provide a checklist of features you should keep in mind, before deciding on a WCM solution.

Drawing

1. End User Contribution – Does the WCMS support contribution by end users (i.e. non web masters, programmer, designer types)?  These days, one of the quick value adds of a WCMS is that it frees up your technical resources from updating a web page.  The most basic feature of a WCMS system is to give knowledge workers the ability to update web pages.
2. Approval Workflow – Does the WCMS support approval workflow?  You want your end user’s to contribute directly to the website, but any edits made to the site may need to be vetted by a manager or subject matter expert first.  After all, people do make mistakes.
3. Security - Does the WCMS allow users to be given access only to specific sections of a website?  The website editing process is typically most successful when users are given responsibility for specific pieces of a site.
4. Asset Reuse -  Does the WCMS allow web site assets, such as code pages to be reused across the site?  When dealing with static web pages, its common to copy and paste an existing page, then make a few small changes  to suite your needs.  However if common code needs to be modified, that change needs to be made for every static page in the web site where it can be found.  Any respectable WCMS will allow you to create and define important assets once, and reuse them multiple times.  For those of you ASP.NET developers, think Master Pages.
5. Publishing - Does the WCMS system allow for publishing of a site from an internal (within your company’s intranet) ‘Contribution’ instance to an external (outside the firewall ) ‘Consumption’ instance.  Whenever possible, I recommend to my clients that they use two WCM systems, one for editing and the other for consumption.
6. Web Application Integration – Does the WCMS  integrate with or is it capable of  hosting web applications?  For instance, you may already have a web application that allows job applicants to submit a resume, check the resume for viruses, and pass it along to a hiring manager.  How will your WCM system integrate with this existing application?  The company I work for has a relatively straight forward website, but it has several web forms that have been integrated into our WCMS.
7. Web Application Stack – Which web application stack does the WCMS use, Java, .NET, Cold Fusion?  If you already have a significant investment in .NET web applications, then using a Java based WCM system may not be the best course of action.

This list is by no means exhaustive, but it gives you a good idea of the features that should be included in any WCMS you choose.

Tyson Magney
Senior Developer
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.