Monday, July 18, 2016

Simple event-driven automations with Event hub and MEC - overview

At Inforum 2016 this year I presented a simple pattern we'd developed across multiple projects to automate and apply business rules when transactions occur in the M3 business engine.

Historically we have used JScripts to achieve this within Smart Office, but a number of factors have driven us away from this approach.  These include:
  • the increased maturity of the H5 client;
  • the inclusion of the H5 enterprise features within the foundation client as of Infor Xi;
  • an increasing diversity of data input mechanisms across our customer base (M3WM, custom applications, data feeds from PLCs etc.); and 
  • the problems inherent to applying business rules at the UI level that could be bypassed by users.
While JScripts still have their place, for set and forget application of business rules along the lines of "if this then that", there are better approaches now available.

We first investigated Infor's Ming.le a couple of years back, but when we started looking into Ming.le our assessment was that it was not mature enough to meet out needs.  Note that this has now changed, we have used Ming.le on a few projects and we are very excited by the possibilities offered by Ming.le within the Infor Xi stack.

Nonetheless, while at the time we were not convinced that Ming.le was appropriate for our needs, the technology underpinning Ming.le was of interest.  Specifically Event hub which monitors all transactions within M3 and allowed us to capture:
  • Start and stop of M3 programs including batch programs;
  • Create, Update and Delete transactions on M3 tables; and
  • Current and Prior values of records in M3 tables.
Many, if not most, of the processes we wished to automate occur when one of the transactions above occurs.  For example we wanted to:
  • Notify upstream planners for supply chain order connected orders when requested delivery date on CO, MO, PO line changes;
  • Update customer agreement / bulk order when quote changed;
  • Reclassify, move, or write off balance ID when goods were received into a location;
  • Load payment when a POS sale is invoiced;
  • Write to a log when a M3 program is started;
  • Export planned orders when the MRP run completed.
What we found was that Event hub existed within the M3 ecosystem as shown below:

Event hub is one of the technologies underpinning the M3 ION BODs and is responsible for creating the initiator message, much like a MBM, that instructs MEC to create the BOD and pass to ION for action.

One of the great things about Event hub is that it is available in all M3 10.1+ installations that have the business engine on the Grid.  So for sites that are on the Grid, but don't yet have ION, we can still leverage the functionality that Infor has delivered to support ION to build automations as described here.

The pattern we developed for creating a simple automation has three main parts:
  1. Create the Event hub subscription;
  2. Create the MEC subscription to Event hub; and
  3. Build the automation in MEC.
Over the next few days I'll detail how each of these steps work and how, with minimal MEC skills, simple event-driven automations can be built.

Saturday, July 16, 2016

Inforum 2016

Inforum 2016 in New York was a great opportunity to meet colleagues and friends, learn what's coming in the new versions of the Infor product suite, hear from thought leaders in a number of areas and enjoy the Maroon 5 concert :)

Friday, June 17, 2016

Windows 10 patch MS16-072 / 3163622 / 3163018 can break Smart Office

Microsoft released patch MS16-072 / 3163622 / 3163018 for Windows 10 this week and it's causing communications issues between some Smart Office clients and servers:

Looking at the server installation point - https is inaccessible from Internet Explorer and Edge, while it works fine from Chrome:

The Microsoft fix is impacting other areas of infrastructure as well.  The immediate workaround is to uninstall KB 3163622 / 3163018 from affected PCs until either Microsoft resolves the issue or Infor provides an official mitigation.  It also appears that there may be group policy changes that can be made to address this, but I haven't tested these yet:

"MS16-072 changes the security context with which user group policies are retrieved. This by-design behavior change protects customers' computers from a security vulnerability. Before MS16-072 is installed, user group policies were retrieved by using the user's security context. After MS16-072 is installed, user group policies are retrieved by using the machines security context. This issue is applicable for the following KB articles:
3159398 MS16-072: Description of the security update for Group Policy: June 14, 2016
3163017 Cumulative update for Windows 10: June 14, 2016
3163018 Cumulative update for Windows 10 Version 1511 and Windows Server 2016 Technical Preview 4: June 14, 2016
3163016 Cumulative Update for Windows Server 2016 Technical Preview 5: June 14 2016
All user Group Policy, including those that have been security filtered on user accounts or security groups, or both, may fail to apply on domain joined computers.
This issue may occur if the Group Policy Object is missing the Read permissions for the Authenticated Users group or if you are using security filtering and are missing Read permissions for the domain computers group.
To resolve this issue, use the Group Policy Management Console (GPMC.MSC) and follow one of the following steps:
Add the Authenticated Users group with Read Permissions on the Group Policy Object (GPO).
If you are using security filtering, add the Domain Computers group with read permission."

Monday, June 1, 2015

Upgrading M3 to 13.3

I've been looking at the M3 13.3 installation, and while the documentation is a little unclear on the process to upgrade from 13.2 to 13.3, there is a video that provides a little more information.  After working my way through this, my quick notes on the process I followed are below:

Step 1 - Upgrade the technology stack

  • Download Lifecycle Manager Server, Core technology package and Session provider package;
  • Update Lifecycle Manager Server from the command prompt.  This is similar to the initial install process, however we select the Upgrade path and upgrade the existing Server instance; 
  • Start Lifecycle Manager - we are prompted to restart and LCM will then be upgraded;
  • Upload the core technology package and restart LCM when prompted;
  • Upload the session provider package and restart LCM when prompted;
  • Upgrade the Grid;
  • Upgrade GDBC;
  • Upgrade Event analytics and Event hub;

Step 2 - Upgrade the business engine

  • Apply a major upgrade to the BE instance;
  • On the BE, upload the FP3 feature pack for MVX and the country variant;
  • On the BE, apply the feature pack and select FP3;
  • Run upgrade database with fix/feature pack for MVX and the country variant;
  • Run import database data for MVX and the country variant;
  • Run apply MI metadata;

Step 3 - Apply MCP1 for 15.1.3 and then run fix programs

  • The documentation suggests that the next step would be to run the fix programs, but I found these failed to start.  So I downloaded and applied MCP1 (including apply feature pack, upgrade database and import database data for this).  Once MCP1 was installed the fix programs would start;
  • Run fix programs.  This is a new function that replaces the process where these were previously applied one by one from Serverview;

At this stage the "M3 Core" applications have been updated to 13.3 levels.  We still however need to upgrade the other components:

Step 4 - Upgrade other components

  • Download Smart Office and H5 packages;
  • Upload Smart Office and H5 packages;
  • Upgrade UI Adapter;
  • Upgrade Mango server (includes upgrade, installation point export, sign and import);
  • etc.

In general the 13.3 upgrade process is a massive improvement, in particular for the BE and core M3 components.  The complexity of upgrading the add-on components hasn't really changed though and these still vary from a simple right-click upgrade, through complexity approaching that of the initial install.

Thursday, May 28, 2015

M3 13.3 (BE 15.1.3) and M3 on Linux

Infor has just released to GA M3 13.3 (BE 15.1.3) along with a day 1 mandatory MCP.

On InforXtreme there is the M3 13.3 release summary, what's new and BE release notes.

A highlight for me from 13.3 is the increase to 10 character dimension field.  Many sites I work with struggle to fit the information they want to store into the space available in the dimensions, so this is a great improvement.  A minor niggle is given we were looking at a fairly significant database change for this, it would have been nice to have moved to 15 characters for each dimension so we could post large item codes directly into the GL without using surrogate keys, but this is still a massive improvement.

The grower contract management module, discussed in its early stages of development at Inforum 2014, is also of interest.  This leverages purchase agreements and the QMS module for testing while the product is growing.  It also allows for provisional payments to suppliers and final payments based on receipt and testing.

A minor but very nice change is price list identifiers are increased from 2 to 10 characters.  This is a very welcome change.

There's a lot of good changes in this release, as there were in 13.1 and 13.2 before it, way too many to list here.

M3 on Linux
At Inforum last year, M3 running on Linux with PostgreSQL as a back-end was promised.  With M3 13.3 Infor have met this promise for the core M3 application.  The M3/Linux installation guide is up on InforXtreme for this.  This requires RHEL and Postgres Plus Advanced Server.  CentOS is likely compatible, if unsupported, and there is a 60 day trial available for Postgres Plus.  Time to fire up a VM and take a look.

With the 13.x series Infor continues to demonstrate its willingness and capability to invest in M3 to deliver new functionality to published and predictable schedules.

Thursday, October 2, 2014

Building a M3 Web Service over an API and SQL Server's FOR XML

As part of the series of posts on agile interface development with ION there are a couple of foundation aspects we need:

  • Building a M3 Web Service; and
  • Creating the XML file for for the Web Service

Building a M3 Web Service over APIs

I've covered building a M3 web service over M3 display screens before and building these over APIs is fundamentally the same, but to recap briefly there are two basic steps to this:

1. Create the Web Service in MWS Designer

Once you've determined the API transaction you want to use (in this example I'll use MMS025MI.AddAlias) build the web service in MWS designer by:

Creating a new web service

and specify the API transaction to use

Check the aliases on the fields for naming conflicts and resolve as required.  I've seen this as an issue with, for example, some Warning message fields where these often do not have unique aliases.
Once it's set up then deploy the web service.

This will have published the SOAP web service to your MWS service.  From here any SOAP compliant application can call the web service to interact with M3.

2. Validate the Web Service

From LCM, manage the MWS application.  From the List function you can then find your deployed web service.  Copy the http link for the default router.

To validate the web service works correctly and to get the exact format of the XML file required by the web service, I tend to use SOAPUI.  

In SOAPUI create a new web service

then manipulate the default request to reflect the call you want to make:

Execute the test and validate that the data has been posted into M3. 

The XML above is the template for the file we will create below.  We want everything apart from the <soapenv> tags so:

    <ItemNumber>  02010</ItemNumber>   

Note that when using SOAPUI, if your MWS is on the Grid then you'll use the authentication mechanism setup within LCM for this.  If your MWS is pre-Grid then you'll normally pass in the authentication as part of the XML string.  For this example I used MWS on the Grid which requires the authentication to be added via the Auth tab at the bottom of the SOAPUI screen.

Creating the XML file for for the Web Service

The starting point for this is a SQL Server table that has the field names and formats exactly the same as the Aliases in the M3 Web Service.  Note however that a date for M3 should be in the format YYYY-MM-DD.  It's better to use SQL type char(10) for this rather than datetime.

There are many articles showing how to use FOR XML to create XML output.  I won't reinvent the wheel here and will instead just jump straight to the solution we developed:

 declare @xml xml                    --Used to build the initial XML record  
 declare @output varchar(max);     --Used for the final XML record  
 --Obtain the inner data  
 DEFAULT '""""')            
 select @xml = (  
 FROM [dbo].AddAlias AddAliasItem       
 FOR XML AUTO , ELEMENTS,root('AddAlias') )       
 --Fix the quotes around the namespace  
 select @output = (select convert(varchar(max),@xml))  
 set @output = REPLACE ( @output , '&quot;' , '"')  
 declare @cmd varchar(8000)  
 select @cmd = 'bcp "select ''' + @output + '''" queryout "C:\OUTPATH\data.xml"'  
      + ' -S SQLSERVERNAME -T -r -w -t -c -C RAW';  
 exec xp_cmdshell @cmd, NO_OUTPUT;  

Looking at the components of this:

  DEFAULT '""""')  

This adds the XML namespace to the AddAlias node.

  FROM [dbo].AddAlias AddAliasItem  

This changes the node wrapping the actual data to AddAliasItem instead of AddAlias which is the table name.

 FOR XML AUTO , ELEMENTS,root('AddAlias') )   

This does two things.  Firstly by default FOR XML AUTO will treat the data as XML attributes on the AddAlias node.  Specifying ELEMENTS makes these appear as XML elements rather than attributes.  Secondly, while we renames the AddAlias node to AddAliasItem above, we still need an outer AddAlias node.  The command root('AddAlias') will add an outer AddAlias node wrapping the AddAliasItem node.

 select @output = (select convert(varchar(max),@xml))   
 set @output = REPLACE ( @output , '&quot;' , '"')  

The XML output from the FOR XML AUTO command will convert quote marks (") to &quot;.  ION however requires that quote marks appear as quote marks, so we use the SQL REPLACE command to find and correct these.

 select @cmd = 'bcp "select ''' + @output + '''" queryout "C:\OUTPATH\data.xml"'   
    + ' -S SQLSERVERNAME -T -r -w -t -c -C RAW';   
  exec xp_cmdshell @cmd, NO_OUTPUT;  

The final step is to output the generated XML data to the file system.  Here we use the SQL Server bulk copy command BCP to write the XML data to the disk.  We can call command line programs via xp_cmdshell and the NO_OUTPUT statement suppresses success or failure messages and makes the call cleaner.

This SQL logic will then take all records in the AddAlias and output these as a single XML file.  However, the M3 Web Service doesn't like multiple API commands in a single call, so we wrap this logic in a SQL cursor and output one record per XML file.  I'll cover the cursor logic in the last post in this series.

We can then take the XML file generated and test this in SOAP UI to confirm that what we have generated is compliant with the data required for the XML call.

Tuesday, September 9, 2014

Agile interface development using ION

I'm attending Inforum next week and will be presenting there on our experience using ION as part of an Agile interface development project.

In my presentation I cover briefly the details of the methodology we developed for this project which allows us to build a basic interface between two systems in about 30 minutes.  In this post I'll outline this methodology and, over the next few posts, I will build out the detail of how this works.

Note that the interface approach I'll describe here focuses on the plumbing between the two systems and intentionally moves business logic for any transformations, control flow or quality of service required outside the interface itself.

There are 5 main components of the interface:

  1. M3 Web Service
  2. ION Endpoint for Web Service
  3. ION Endpoint for file system
  4. ION Document flow to connect Web Service and file system
  5. SQL server stored procedure to create XML file for ION

Each of these components take about 5 minutes to set up, and the way they are set up is the same for all web services which makes for a very quick, repeatable process.

M3 Web Service

A M3 Web Service can be built over M3 APIs, most M3 programs, or SQL statements or stored procedures.  We've covered how to build web services over M3 programs and SQL statements before.  As part of this series I'll cover how to build a web service over an API.

Via M3 Web Services and the M3 Web Services Designer (both licensed modules from Infor) we expose the API as a SOAP web service, which can be widely consumed to allow easy integration with M3.  Conveniently, ION can consume these Web Services and thereby expose standard M3 APIs for List, Get, Create, Update and Delete functions.

ION Endpoint for Web Service

This advises ION the url that the web service will be exposed on, and the methods that will be exposed via this web service.  We also define the authentication with M3 (using a M3 username and password) and the identifier that will be used to track the processing of the ION message.

Through this endpoint ION will be able to call the web service we defined above.

ION Endpoint for File System

For this interface design we're accepting an XML file from an external system (in this case SQL Server) and passing this through ION to M3.  So here we define the source directory where ION will find the XML files.  This source directory can be on a network share, so we also specify the authentication to connect to that share.

Note that we could instead of using the file connector be querying a SQL Server directly via a stored procedure, but we chose the file system approach as it was ultimately simpler and led itself more to code reuse than the direct to SQL approach did.

ION Document flow

The document flow connects the endpoints together and the XML data that will be passed from the file system to the web service.  The document flow is where we can use the ION mapper to define business logic (like we can in MEC), but with this approach we do not.  ION is simply used to pass the message between systems.  Again this design decision reduced complexity and enabled reuse.

SQL Server to create XML file

Here we use SQL server to create the XML file that ION requires.  The SQL Server command "FOR XML" allows us to take the output from a SQL query and generate an XML document.  We then use bcp to create the XML file.

When we put these components together the architecture looks like this:

The two components on the left refer to the solutions we were integrating with M3 via ION, in this case Infor's Advanced Planner and Advanced Scheduler and a manufacturing plant.

In my presentation I go into some detail about the challenges inherent in those integrations and why an Agile approach was required.

The following posts will cover:
  • Building a M3 Web Service over an API and SQL Server's FOR XML
  • Building the endpoints and document flow in ION
  • Control logic and other considerations