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:
becomes

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:

  <AddAlias>   
   <AddAliasItem>   
    <Company>100</Company>   
    <AliasCategory>2</AliasCategory>   
    <AliasQualifier>EA13</AliasQualifier>   
    <ItemNumber>  02010</ItemNumber>   
    <AliasNumber>4434567001143</AliasNumber>   
    <AliasType>EA13</AliasType>   
   </AddAliasItem>   
  </AddAlias>   


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  
 WITH XMLNAMESPACES (   
 DEFAULT '""http://your.company.net/MMS025MI/AddAlias""')            
 select @xml = (  
 select   
 *  
 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:

 WITH XMLNAMESPACES (    
  DEFAULT '""http://your.company.net/MMS025MI/AddAlias""')  

This adds the XML namespace http://your.company.net/MMS025MI/AddAlias to the AddAlias node.

 select    
  *   
  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

Tuesday, September 24, 2013

M3 13.1 / BE 15 & SmartOffice 10.1.1 UI improvements

I've been using BE 15 for a few weeks now with Smart Office 10.1.1 and I wanted to mention a few of my favourite UI improvements.

There are simple things like the Text Block now defaulting the active control to the Next button.  This is a great time saver when you have T in your panel sequence and you are pressing ENTER to move between the panels.
With BE 14 and below the default control was the text field so you needed to use the mouse or Tab key to get to the Next button to submit which interrupted a user's flow.  Now with BE 15 you can just keep pressing ENTER to move between the panels including Text panels.


The new toolbox screens are great.  Infor has built a new toolbox screen standard (see NCR 5418 on InforXtreme for details) which provides both additional toolbox screens but also enhanced capability within the toolbox screens.  

There are three major parts to this change:
  1. Views can now be linked to Sorting Orders.  With previous versions of the BE toolbox screens could use any view with any sorting order.  This would often lead to combinations that made little workflow sense.  You can now specify on a View that this should be restricted to a specified Sorting Order.
  2. Views are extended to 30 columns with up to 250 characters which is close to double the previous limits.  This allows us to make proper use of widescreen monitors.
  3. Columns within the Views can be logically formatted.  We can specify date formatting, numerical formatting, financial formatting (appending CR to negative values) etc.
This is a big change and very beneficial to the screens that support the new toolbox standard.  However screens that were previously toolbox screens (OIS300, MMS200, PMS100, PMS170, PPS170 etc.) have not been updated to support the new standard.  This is a shame as the new standard is a significant improvement over what was offered before.  This does make BE 15 a bit of a transitional release with different approaches to creation and maintenance of the toolbox screens depending on which program you are in.


Perhaps my favourite new function however is the addition of related tables and virtual fields.  These are a framework that allow us to easily build functionality that extends M3.  The related tables can be found from the new program CMS005.
Here I have created a new custom table and linked it to MMS001 based on the primary keys
With a very simple JScript I am now able to easily add additional fields to MMS001 from the custom table I created in CMS011 and read and write these via the native M3 APIs e.g.
It's also now possible to show these custom fields in the new toolbox screens.

We were able to do this already with Mashups, JScripts and Web Services, but this new functionality bakes this into the core of M3 and makes building extensions to M3 significantly easier.


Some of the functionality highlights I see as particularly valuable for my customers includes:
  • Customer Order workflow improvements to significantly reduce the number of panels required to enter a CO
  • Improvements to the Purchase Order process to ease the analysis of alternate acquisition methods and print a PO without sending it to the supplier
  • Improvements to the Balance ID toolbox to bring some of the DO creation functionality initially introduced in Warehouse Mobility back into the core M3 package
  • Changes to the stocktake functionality to reduce the cost of undertaking cyclic stocktakes by initiating stocktakes on empty locations and during picking
  • Significant improvements for the F&B industry including best before & harvest / kill dates, ageing in hours and minutes, GS1-style extensions to PO processes and addition of allocation restrictions to ensure that a customer is never shipped older product than has already been dispatched to them
  • Lot blending within silos and Lab Inspection changes to allow Lab Inspections at the Balance ID rather than lot level
  • An API for GLS850
  • Improvements to the ability to reconcile the logistics and general ledger systems
  • The ability to mark a user as "Deactivated" within MNS150 without deleting the user.

As time allows I'll cover some of these in additional posts.

Wednesday, September 4, 2013

M3 SDK alternative

There's a great post over on the Smart Office blog about how to build Smart Office applications when you don't have access to the Smart Office SDK.  It would be great to see wider distribution of the Smart Office SDK, as it allows developers to quickly and easily extend the core functionality of M3 using Visual Studio, but this is the next best thing.  All that appears to be missing is the logic for deploying a Smart Office package in LCM, though that appears to be a zip file with a manifest so should be able to be reverse-engineered.

Thursday, June 13, 2013

Infor's 10x webcast sessions

Infor have published their 10x sessions on the Internet.  The M3 session is accessible here here, and the other sessions are available from this link.  The M3 session provides a high-level overview of the 13.1 / BE v 15 functionality.

Monday, June 10, 2013

M3 BE v15 / 13.1

Infor has recently released M3 v 13.1 / BE v 15.  The release notes are all up on the InforXtreme documentation site.  Over the next few months I'll be working on a BE 14 -> BE 15 upgrade so I've been trawling through the release notes.  There's some interesting stuff there.  Highlights on a first read from a technology perspective are mashup / custom list enhancements and lots more programs supporting customisable Browse panels.

From a functionality perspective the extensions to the advance invoicing functionality look interesting, especially with the ability to enforce this and extend this into cashflow planning.  Lab Inspection approval at a Balance ID level is critically needed for the F&B industry so it's good to see it there.  Inbound transportation management is a good addition filling a hole in the M3 solution.  The API for GLS850 is well overdue filling an area where most sites have built custom solutions to solve.

There's lots of other interesting changes there and I'll review areas as I get to them in the coming months.

Friday, November 23, 2012

LSO & M3 discussion forums

I was trolling through some older posts on Karin's blog and spotted a reference to a LSO discussion forum in the comments.

The address is http://www.lawsonguru.com/forums/ux/lso/   Thanks Karin for pointing this out :-)

For generic M3 / Movex issues the discussion forum http://erp.ittoolbox.com/groups/technical-functional/intentia-l/ is a great place to ask questions and contribute to collective community knowledge base.