xTuple ERP has long had the ability to send a few types of document via FTP and email. The increase in the number of xTuple ERP users and proliferation of electronic documents has lead to a number of requests to support electronic transmission of additional document types and more flexibility in how documents and their delivery are to be specified.

Functional Requirements

The EDI capabilities of xTuple ERP should be changed in the following ways:

• Retain existing functionality, although the mechanics of achieving that may change (see below).
• Make it easier to add to the set of document types supported by the EDI subsystem.
• Expand the set of supported document types:
  • Credit Memos (issue 8110).
  • A/R Statements (issue 8110).
  • Quotes (issue 6365).
• Normalize the storage of EDI Profile data.
• Generalize the functionality to allow sending a single document to multiple recipients (issue 5467).
• Store the actual messages sent, including the attachments, in the database with links to the appropriate records (issue 5468).
• Support additional email header properties (issues 8322 and 8614):
  • Reply-to
  • Bcc
  • From
• EDI sent should be stored for use as a knowledge base

This feature is not explicitly intended to move existing Batch Manager hooks from the core C++ GUI client to the xtbatch package, but this might be a fortunate side effect of the development effort.

New Terms and Definitions

N/A

Related Existing Functionality

The Standard and Manufacturing editions of xTuple ERP currently support electronic mailing of certain documents: Alarms, Events, Incidents, Invoices, Purchase Orders, Sales Orders. These documents are generated by sending jobs to the Batch Manager describing the report to run, the parameters to use when running the report, and the EDI profile to use.

EDI Profiles currently support two delivery mechanisms: FTP and electronic mail. If you choose FTP delivery then you are allowed to enter the server, login, password, and destination directory at the top level, and at a lower level you can select between running a user-defined query and predefined report or generating a comma- or tab-delimited file from one or more user-defined queries. If you choose email delivery then you can enter to, cc, from, subject, message body boilerplate, and either html or plain-text delivery.

Different parts of the application tap in to the EDI subsystem in different ways:

System -> Master Information -> Database Information lets you define a default from email address. Every time the Batch Manager reads a job from its queue it checks the batch_from field of the job. If this is empty or NULL then the Batch Manager uses the default email address. There are essentially two ways to submit a Batch Manager job - call submitActionToBatch(), which leaves the field for a from address blank, and submitEmailToBatch(), which sets the from address if the caller supplies it (in three out of four versions of of the procedure!).

The To address of an email EDI message is set in several ways. Sometimes the GUI client passes an explicit email address. If this isn't passed, then sometimes the application defaults to the email address of the current user and sometimes it is left blank.

You can link EDI Profiles to Incidents through Incident Categories, each of which can be associated with its own email delivery EDI Profile. If you have configured CRM to send email for Incidents, then the email will be constructed using the EDI Profile associated with the Incident Category. If the Incident Category's EDI Profile is undefined then a generic CRM Incident Email Profile will be used if it is set on the Configure CRM window.

The Customer record currently has two EDI profiles, one for invoices and one for sales orders. Choices allowed are No EDI, Custom email, or any of the EDI Profiles of type 'invoice' created via System -> Master Information -> EDI Profiles. These do not appear to be working correctly now (issue 7670).

The application supplies a Customer Form Assignments window which allows associating a Customer Type or pattern of the Customer Type with forms to use for Invoices, Credit Memos, Statements, Quotes, Packing Lists, and Sales Order Pick Lists. These forms are not used consistently by the EDI Profiles.

There are different processes for constructing email messages, depending on what message is being created:

Similar and Related Requests

The following Mantis issues record open bugs and feature requests related to EDI profiles. Inclusion in this list does not mean that this specification addresses any particular issue.

Feature requests 6625 and 4933 are included in this list because doing so might lead to simplification of the EDI processing, even though the overall application might become more complex. At the moment, some of the differences between Sales and Purchasing EDI involve using Sales Order forms as opposed to hardcoding the name of the Purchasing report. If all EDI documents operated through forms then the internals of EDI handling would need less special case handling.

Feature request 8345, bundling the Batch Manager into a separate package, is also related, as EDI profiles are currently used in conjunction with the Batch Manager.

There are also comments in the source files for the Return Authorization and Open Return Authorizations windows to allow sending Return Authorizations via e-mail.

Conflicting Features

N/A

User-Level Functionality

Currently there are several places in the application that allow the user to enter EDI Profile information. The entry method for profile information will be standardized, all EDI Profiles will be stored in a central location, the user interface will be modified to allow associating multiple EDI Profiles with a single parent record (such as Customer or Vendor), and the code for interactive and batch processing of EDI transfers will be normalized. This should reduce the amount of code required overall, allow supporting a number of new document types, allow more flexibility in the tokens accepted for embedding in EDI messages, and make the internals easier to follow.

The artificial distinction between using generating PDF for email EDI and PDF or tab- or comma-delimited files for FTP will be eliminated. The artificial distinction between associating document types with FTP profiles and not doing so with email profiles will also be eliminated.

As much as possible, the code to handle EDI Profiles will be removed from the guiclient C++ code, where it is currently hidden if the Batch Manager is unavailable, and reimplemented as scripts in the xtbatch package. This will make the generic core smaller and easier to maintain. This will also allow experimenting with defining generic signals to be emitted by the core GUI client for capture by scripts.

Window Changes

The EDI Profile window will be rearranged to allow email as well as FTP profiles to have associated form details and document types. The Forms may be left blank for email profiles. New fields will be added to expand the functionality of email handling: From, CC, BCC, and Reply-to addresses. Any or all of these may be left blank by the user. If the From address is left blank, email messages constructed from this EDI Profile will use the current methods for determining the From address. If the other fields are left blank then the corresponding fields in the email header will not be set. The contents of the From, Reply To, To, Cc, Bcc, Subject, and Message Body fields will all be processed for </name> tokens before the message gets sent.

When the user defines a report form for the EDI Profile (as opposed to a CSV or TSV form), s/he will be given a combo box that lists the defined reports. If the document type is one that has a form mapping, such as Invoice, then the combo box will show "Use Pre-Assigned Form". The combo box will be disabled if the application has not yet been extended to allow form selections or if report names are still hardcoded for this document type (for example, the hard coded "PurchaseOrder" in 3.2.x and earlier).

After EDI Profiles are created, they may be associated with particular Customers, Vendors, Ship-To Addresses, Alarms, Incident Categories, etc. For example, the Vendor window's Transmission tab will be changed to look like this:

The Attach button will allow the user to attach a previously-defined EDI Profile to this Vendor. The Detach button will allow disassociating a selected EDI Profile from this Vendor. The New and Edit buttons will allow creating and modifying EDI Profiles for general use, although by default new EDI Profiles will only be associated with this one vendor. When the user clicks the Edit button for a Profile that is shared with other Vendors, Customers, etc., s/he will be asked if s/he wants to edit the shared Profile or make a copy of that shared profile for use with just this Vendor.

Similar changes will be made to the Customer window.

Whenever the user chooses to print a document, the xTuple ERP GUI client will check to see if there is an appropriate EDI Profile for this document and its intended recipient (e.g. there is an EDI Profile associated with this Customer's Ship-To address for this Sales Order). If the EDI Profile is for email delivery and is marked as "review before sending" then the user will be given a window resembling the current deliverInvoice, deliverPurchaseOrder, deliverSalesOrder, and deliverEmail window with the </name> tokens replaced by the appropriate values. The user will be allowed to edit the email addresses, subject, and message body before sending. As in the current implementation, the user will not be given the option to see the generated report PDF or CSV. If the EDI Profile is for FTP delivery or is not marked for review before sending, the EDI message will be submitted to the Batch Manager for completion and delivery and the user will be shown a message box showing how many documents have been submitted.

The deliverInvoice, deliverPurchaseOrder, deliverSalesOrder will be merged into a single window, and possibly merged with the deliverEmail window.

One feature hinted at by the existing code is the ability to have the locally-printed version of the document to differ from the electronic version sent to the recipient. This appears to be partially implemented for Invoices through the ediform table and for Sales Orders through the presence of a Report combo box on the deliverSalesOrder window. This feature must be preserved but made more transparent if a better user interface can be found.

There will be a new EDI History window. This window will allow the user to select the EDI Profile type (email vs. FTP), a date range, either a particular document type or all document types, and either all recipients or some subset of recipients. insert image here

The following windows should remain relatively unchanged visually but may need to be modified internally to use the new EDI Profiles:

• Reprint Invoices
• Print Invoices
• Purchase Order
• Unposted Purchase Orders
• Email Purchase Order Form - this will be the merged deliver* window
• Sales Order - if cust email then deliverSalesOrder @ save&Add and at Save if printed
• Email Invoice Form - this will be the merged deliver* window
• Email Sales Order Form - this will be the merged deliver* window
• Open Return Authorizations - this will get a new right-click option to send EDI if appropriate
• Open Sales Orders
• Print Invoices By Shipvia
• Return Authorization - this will be modified to send EDI much like the Sales Order window does
• Ship-To
• Incident

It's unclear if there's a better way to associate EDI profiles with Incidents than through the Incident Category Definitions, although I'd like to think there is.

</token> Replacement for Email Profiles

All EDI Profiles will have three replacement tokens pre-defined: </doctype>, </docid>, and </docnumber>. For each type of document, there will be another token pre-defined for the primary document id (e.g. </invchead_id> for invoices, </pohead_id> for purchase orders, and </incdt_id> for incidents). In addition to these standardly available tokens, the person who creates an EDI Profile will be able to supply custom MetaSQL queries. The MetaSQL parser will be passed the standardly available tokens and their values in a parameter list. Each custom query belonging to the EDI Profile and defined for the current document type will then be executed and the first line of its results appended to the parameter list. This allows writing queries to build arbitrary lists of tokens to replace in the message body.

Report Changes

The EDIProfilesMasterList report will have to change to show new columns. If time permits it will be rewritten to group the email and ftp profiles separately and show different column headings for each.

Batch Manager Changes

The logic for determining which report to run for an EDI message, if any, will be removed from the Batch Manager code. All of the decisions on how to build the EDI message will be made by the GUI client and the stored procedures it calls. The flow inside the Batch Manager will be simplified to the following steps:

1. Read Parameters
2. Replace tokens (possibly recursive if there are token-replacement queries)
3. Generate output
4. Transmit output

The details of how to accomplish each of these steps will be embedded in a class hierarchy consisting of a parent class EDI and the child classes Email and FTP (find less generic names). The parent class will have a factory method that returns an instance of the proper subclass given an ediprofile_id.

Usability Considerations

EDI Profiles will be more complex than before, but hopefully the symmetry will make them easier to use than in earlier versions.

The </name> tokens need to be documented, including the use of queries to generate arbitrary tokens. The documentation should point to examples such as Alarms and Events, which will have system-supplied queries as well as profile fields that use them.

One potential problem is that people are used to looking for EDI Profile information on the Customer and Vendor windows and, because of problems in the existing code, might not be aware of uses of EDI Profiles in parts of the application they don't often visit. Therefore, they may not be aware that changes they make to EDI Profiles in the new system could affect other parts of the application (changing an EDI Profile used for a particular customer might also affect a Vendor, for example). To minimize the risk of harm from this, the application will ask users if they want to edit the shared version or work on a copy if they change an EDI Profile used multiple places.

How do we make more transparent the selection of different versions of a report to run for EDI purposes vs. local printing?

This spec specifically excludes generating the PDF or FTP files when the user previews the EDI before sending. Some users would find the ability to preview these data very helpful. This functionality should be implemented if we can address the following questions:

• Do we really want to run the reports or queries twice, once for the user to preview and again when composing the message?
• How do we present the paired message and attachments without taking over the entire display?
• Have we misled the user if the final PDF differs because of last-minute changes to the document?

Problems and Alternatives

It would be nice to consolidate the EDI configuration into a single window, rather than leave it scattered about. The EDI Profiles window could be rewritten to be a more general Configure EDI/EMail window. The Default From Email Address should be moved from the Database Information window to Configure EDI/EMail. The EDI Profiles would be displayed as a list, along with the usual New, Edit, View, Delete buttons and possibly a Copy button.

It's unclear whether the Incident EMail options belong on the Configure CRM window or here, in part because it's unclear how to associate the individual categories of Incident email with ediprofiles. It might not be feasible to regularize Incident email in the first implementation pass because of this problem.

The fact that EDI Profiles are tied to specific recipients in this spec means that each recipient needs to be configured by hand. It would be nice if there were a default configuration by document type - every time a document of type X is generated, look for a recipient-specific profile/relation; if none is found then look for a recipient-generic profile/relation; use the first found profile/relation to generate the EDI. This could be implemented by allowing all three columns recipient id and type and email to be NULL in the edirelation table, as long as the document type were defined. The UI could get messy and there are some problems with data storage given the following questions:

• Where in the GUI does this configuration take place? It could go in a second XTreeWidget on the generic EDI Profiles configuration window above.
• How do we handle the case where most recipients share generic profiles but some potential recipients do not want to receive EDI?
• How do we handle the case where half of the recipients want generic Email profiles and the other half want generic FTP profiles?

It would be nice to have the ability to consolidate EDI. For example, most recipients might like getting one email with all of their invoices for the day rather than one per invoice. On the other hand

• It's hard to reconcile the one-at-a-time nature of generating the documents and associated batch manager submissions with the potential desire to group the results.
• It's hard to know how to construct consolidated messages that aren't paper-centric. For example, how should a CSV file that contains multiple sales orders be organized? How does the Batch Manager know how to construct this file and also construct a single-sales-order file?

Is there a performance problem running all of the queries associated with an EDI Profile to get replacement tokens? Do we want to differentiate between 'get parameter' queries and 'get data' queries in the profile definition? Doing so would require an additional column or two and associated widgets.

Why do EDI Profiles use </name> instead of MetaSQL syntax? It would be nice if we could use MetaSQL. This would require that the upgrade rewrite EDI Profiles that already exist but would lead to more consistent usage throughout the application and give a lot more flexibility. For example, if an EDI Profile could be shared between Vendor and Customer docs, then the message body could contain a clause like this:

Attached is a copy of <? value("doctype") ?> <? value("docnumber") ?>.
Thank you for your <? if exists("cust_id") ?> business <? else ?> partnership <? endif ?>,
xTuple <? if exists("cust_id") ?> Sales <? else ?> Purchasing <? endif ?>

Would MetaSQL usage in EDI Profiles conflict in any way with sending HTML email? Would the MetaSQL directives interfere with saving the data from the windows into the database (QTextEdit::toPlainText() or QTextEdit::toRichText())? The potential problem with MetaSQL is the copious white-space it leaves behind after processing. There would have to be a regex replacement to consolidate unnecessary blanks and newlines. How can we be sure of the definition of 'unnecessary'?

Now that EDI handling is bundled as part of the xtbatch package instead of existing as components that are used in Manufacturing and Standard editions but not in PostBooks, there may be some problems that arise from the required changes to various views. The only way to handle this might be through vigorous documentation. There may be problems running upgrade scripts if there are site-specific views and functions that refer to the tables and views being changed.

If the adhoc email feature for mass mailings gets implemented (issue 5467, not specifically described in this spec), we should warn users about the risk of being blacklisted by Internet Service Providers. This feature would be for small-scale mailings only and isn't a substitute for using email marketing services.

Is it really appropriate for there to be only one ediprofile table? The ediprofile_option* columns are hard to figure out because they're overloaded. Maybe we should split this into child tables, one for email and another for ftp. That might make it easier to add a third edi type (about which we've recently heard inquiries on the forum). The parent ediprofile table would be empty; the data would actually be stored in its child tables.

Then there would be a child table called ediemail to hold email-specific records with the following additional columns:

For ftp EDI Profiles there would be an ediftp table for ftp-specific data:

Internal Design

Now what does it look like from the inside?

Basic Algorithms

In the GUI client application when it's time to send EDI:

find the EDI profile for this document (see below)
if no EDI profile is found
  return - no EDI should be sent
end if
create a parameter list containing docid, doctype, and docnumber
if email and user should/can review
  for each query associated with this edi profile for this document type
    preprocess the query with metasql
    run the query limit 1
    add the column names and values to the parameter list passed in
  end for
  replace all tokens with values from the parameter list (could be a simple metasql call)
  display for editing
  if user clicks cancel
    return without saving
  else if user clicks save
    write the edited/token-substituted result to the batch and batch parameter tables
  end if
else // either an ftp profile or no review of email profile
  write the unprocessed message and parameters to the batch and batch parameter tables
end if

In the Batch Manager, processing is similar to the way it's done now, only more consistent across document types and edi profile types:

for each query associated with this edi profile for this document type
  preprocess the query with metasql
  run the query limit 1
  add the column names and values to the parameter list
end for
if this is a report EDI
  run the report with the populated parameter list to a .pdf
else if this is a CSV or TSV EDI
  for each query in the proper order
    preprocess the query with metasql
    run the query to completion
    write the data to a file with the proper delimiter
  end for
end if
if email
  replace all tokens in the message body and header with values from the parameter list (could be a simple metasql call)
  send the email
else if ftp
  send the .pdf, .csv, or .tsv file to the EDI Profile-defined destination
end if

Note that the token replacement is used twice, in the GUI client and in the Batch Manager. This will be implemented in an EDI helper class in the guiclient source directory and made available for linking into the Batch Manager. The knowledge of how to retrieve and format the data, put the pieces together into a properly constructed email message or transfer file, and deliver the completed message will remain in the Batch Manager. To simplify adding a third message format or delivery mechanism it might be worthwhile creating an abstract class with basic methods to generate the data, assemble the message, and deliver it, with the details of how this is done embedded in concrete classes (one for email and another for ftp).

To find the EDI Profile for a given document:

find the recipient's id (vend_id, cust_id, crmacct_id, ...) based on the doctype and docid
build a list of potential ediprofile records by searching edirelation for this recipient, looking for either this doctype or no doctype
if an ediprofile is found for the given doctype
  return the ediprofile
else if no ediprofile is found for the given doctype but an ediprofile is found for this recipient but with no doctype
  return the ediprofile
else
  there is no EDI to send
end if

Custom Widget Changes

The EDI History table and the potential need for an edirelation maintenance window might justify the creation of an EDIRecipient widget.

Do widgets/alarmMaint.cpp and widgets/alarms.cpp need to change?

Widgets that allow selecting an EDI Profile will be configured so the user can only select EDI Profiles defined for the current document type or for all document types. This will probably be an ad-hoc XComboBox.

Schema Changes

Table Changes

This feature will require a number of changes to the database schema, some of which are related to the creation of a separate xtbatch package feature 8345). The ediprofile and ediform tables will be removed from the public schema, since they are not part of PostBooks and their remaining there has lead to at least one bug.

In particular, the following columns will be removed:

The ediprofile table will change. It isn't yet clear whether new columns will be added for new functionality or if the ediprofile will become a parent table with one child for each transmission protocol. See Problems and Alternatives for more information. The discussion there lists the data that need to be stored, regardless of whether one table is used or three.

They will be replaced by a new table in the xtbatch schema that connects EDI profiles to the relevant parent records:

The new edirelation table

The table is unique on parent_id, parent_type, doctype. Correlate edirelation_doctype with xtbatch.ediprofile.ediprofile_type and xtbatch.ediform_type. Remove the type fields from ediprofile and ediform?

The ediprofile table will get new fields for replyto, bcc, and preview options. The storage formats of these fields are described above in the Problems and Alternatives description of ediprofile and possible child tables.

The edihist table will be added to capture all of the messages sent by EDI.

View Changes in public

The removal of ediform and ediprofile from the public schema will require that certain views in the public schema be changed: cust, shipto, and vend. If time permits, these views will be removed - they are known to cause both confusion and performance problems - but there are ~160 source files that refer to cust, ~70 to vend, and ~30 to shipto. If they cannot be removed then each of these views will need two versions, one for PostBooks and another in the xtbatch package. The versions in PostBooks will not have the EDI columns while the versions in the xtbatch package will have them but the underlying queries will be modified. The affected columns for each view are:

API View Changes

The removal of ediform and ediprofile from the public schema will require that the API views named customer, custshipto, and vendor be changed. As the goal of the API Views is to mirror the windows in the GUI client and these fields are being moved off these windows, the EDI-related columns will be removed from the API views. The affected columns for each view are:

A new view will be created to allow importing EDI Relations and EDI Profiles together.

Privileges

There will be a new privilege to allow the user to edit an EDI message before it gets sent. Currently the user can replace much of the text, including email addresses, when presented with an EDI message for review. The ability to see this message and the ability to change it should be controlled separately. For the first pass, the ability to see it will be controlled by the preview flag on the EDI Profile itself. Users without this new privilege will be able Cancel the sending of EDI.