When accepting sales orders it is key to make sure that you will be paid upon delivery (sounds fair, no?).  Therefore it is important verify if the customer is credit worthy upon acceptance of a new order.
Sometimes this is not enough however. Since the processing of a sales order can take quite a while, and the customer’s situation can change in the mean time, it is important to execute additional credit checks at key moments in the order2delivery process.
E.g.

• when the order goes into production (MTO)
• when picking the order
• when transport is planned
• when transport is leaving
• …

At all these stages (could be more/less/other moments too), Manage-It executes an automated, real-time credit check based upon business rules in Shape-IT.
The rules at each of these stages can the same, but can also be very different (or somewhere in between)/.

When the credit check is successful, the order can progress in the flow.  If not, the order will be held and an exception workflow will start to handle the order further.  Typically, the order can either be cancelled completely, or can be sent back in the flow after additional guarantees have been given by the customer.

At any given time in the lifecycle of a sales order, the current credit check status can be consulted, as are the reasons why the order may have been revoked.

All of the above is supported by a flexible, configurable and extensible engine that allow the process to be tuned to the needs of your business.

• Business rules: all rules are modeled in shape-it, allowing very fast change when the business changes (no programming required).  I’ll explain a bit more the capabilities of these models in a next blog item.
  On top of that, external systems can be integrated perform additional validation (e.g. check Dun&Bradstreet number)
• Status flow: a status flow can consist of 1 to unlimited statuses, with rules governing the status changes
• Credit check points : a number of common transaction points have been integrated out-of-the-box with the credit check service. This can be extended to support specific scenario’s.

I hope this gives a good overview of what this module is about – stay posted for more details!

In my previous post about the usage of our new XML batch import feature I promised to explain a bit further how to set up this mechanism.

So here we are!

2 things need to be done:

1. Create 1 or more Excel templates
2. Configure the ExternalImportService

How it works

Perhaps first a few words on the internal workings of this feature:

When importing the input Excel file, it is first converted into an ImportDataObject by the ExcelWorkBookTransformer in our Build-IT framework.
Next, the ExternalImportService will process the content by looping over all sheets in the workbook.
It will verify if the name of the sheet is configured in the configuration file for the ExternalImportService.  If so, it will attempt to convert the data in the sheets into XML messages that can be processed on the service bus.
During this last step, the metadata in the first header row of each sheet is used to determine the meaning of each column.

Creation of the Excel template

I’ll explain this through an example.

Let’s assume we want to put messages on the bus according to following XSD specification

The Excel file for such a message could look like this:

The column header explicitly identifies the elements in the XSD allowing the ExternalImportService to correctly map the data in the Excel file with the message structure.
Through the usage of a second header, each column also has a more human-readable header.
The first header is typically hidden, which leaves a normal looking excel file that can be created/maintained by any user.

Through this mapping mechanism we obtain a good abstraction between the XML message structure and the Excel structure:

• Column sequence can be whatever you like.  You can even add columns that you don’t want to import by leaving the header on the first row empty.  This can be useful if you build the data through formula.
• Not all elements in the XSD need to be present in the Excel file.  Of course only optional elements can be left out.
• You can use Excel features such as range validation, combo/list boxes to increase the data quality.  Also formula, etc. can be used to build the content.
• Locking down the Excel sheet, optionally hiding any content that can be misleading is always a good idea: it will enhance the data quality even further.

Beware of the name of the sheet – when configuring the ExternalImportService, this name will need to be mapped in the appropriate configuration file.

Naming patterns

Of course, the structure of an XML message can get a bit more complex than in the above example.
Therefore we (currently) identify 5 naming patterns:

• Explicitly defined element (as in the above example)
• Implicitly defined property
• Explicitly defined property
• XML Child prefix
• List

Implicitly defined property

Our standard SalesOrder message contains a bunch of often-used properties for a sales order header that can be defined as an explicitly defined element as you’ve seen above.

As we can foresee every property that you’d ever want to store on a sales order header, we also provide the possibility to store “Properties’” in a generic table structure.  This is a data pattern that we use often in our domain model/interfacing model and is therefore out-of-the-box supported in the ExternalImportService.

What we do is, again, quite simple: if the technical column header cannot be resolved as an XML element, and the object has a list of properties, then a property/value pair will be added with

• as key: the technical column header
• value: the content of the field

In other words, in the Excel header, this pattern looks the same as for an explicitly defined element: the exact name of the property

Explicitly defined property

Sometimes the key name of a property contains dots (“.”).  In such a case, it is necessary to explicitly identify the column as a property.

This is done by prefixing the property name with the text “property.”
E.g. property.net.pearlchain.manage-it.services.someproperty

XML Child prefix

When an XML object is embedded into the parent XML object that we are importing, a prefix identifying the child can be used to identify the elements, properties or children a level deeper.

Example:
Let’s assume a “product” xml object can be embedded into a “salesorderline” object.

In that  case, the header “product.no” would identify the property “no” of the embedded product.
If such element doesn’t exist, the import service will try to resolve the column as an implicitly defined property (same logic applies as above)

List

A  last pattern “List” is used to define a list of simple values within a object.

E.g.

In this example, a “vehicle” can have up to 3 collectionSystem values in a list.

Since the type of CollectionSystem is string, the XSD would look like this:

The header name is prefixed with the “List.” to make sure the ExternalImportService maps the columns to elements in the list.

Remark: this pattern of course has it’s limits: when the list consists of many elements, it is better to create separate messages that can be imported independently.

Configuration of the ExternalImportService

2 steps need to be taken, the first being to define excelImportAliases.properties.

This file, which you can find/create in the config folder of your installation,  contains 2 things:

• mapping of sheet names with dto objects that define the XML messages
• definition of the data type of lists

Example:

As a last step, you need to make sure that the referenced dto objects exist on the class path of your solution.

These DTO objects can be generated using JAXB from your XSD schema (you can also work in the other direction).
The ExternalImportService will populate these DTO objects through reflection and put them on the message bus.

Our PearlChain product suite features a number of standard XML interfaces to communicate data back and forth with other systems and between the services inside our product.  On top of that, additional interfaces/events may be created in your implementation.

We use the concept of a service bus (based on Apache Camel) to avoid the pitfall of proprietary interfaces.  Running an interface implies implicitly executing a Camel route that can be configured differently depending on your requirements.

These routes are executed in the back-end on 1 or more application servers and are typically fired of by events in some system.  There are however times when you may want to push data into the system yourself without having to create massive XML files (which I find a rather painful experience).  E.g.:

• Initial data loads
• To create test data sets for functional test, performance tests, …
• Batch data changes as part of system upgrades
• Temporary solution until screens/interfaces are available (or final solution if certain messages need to be sent only very often)

Therefore we’ve added a new XML batch import screen in our solution. With this screen you can provide data in XLS format to the XML interfaces that are running in your configuration.

The advantages of the Excel format over XML are obvious:

• Known, easy user interface (especially compared to xml)
• Possibility for data validation
• Fast data creation and editing
• Export of Excel files from other tools is often easy.

Behind the scenes the data in the XLS file is converted into the correct XML format that the interfaces require.  The conversion is steered by metadata in the XLS file itself (contained in the sheet names and column names, but I’ll explain that further down).

Using this mechanism consists of 2 steps:

Step 1 : Create the input file

First you need to create the Excel file containing the data you want to import.

You probably start of with a template that was provided to you.  I’ll describe in a next post how this is done.

We support all recent versions of the .xls format which means you can create the files using MS Office, OpenOffice, LibreOffice, Google Docs, etc.

The file consists of 1 or more sheets, 1 for each interface/ESB process you wish to call.
Within each sheet, the first header row describes the XML elements that will be created from your file (see Figure 1).
The second row contains a human readable column name.  The top row may be hidden to hide the technical details for user friendliness.

 
Figure 2: Message input file

Once you got started, simply add lines to the sheet with the data to import.
It may be allowed to leave fields empty, depending on the optionality of the field in the XSD of the messages you’re creating.

Step 2:  Run the import of the file

Next, we feed the file with message content to the applicable service process that (see figure 2):

• Open the “Import XML” screen
• Select the file you just created
• Push Import
• Verify the feedback in the logging field to make sure that the import was successful.
• Done!

 
Figure 2: Import XML screen