SFTP in MEC

Infor M3 Enterprise Collaborator (MEC) now includes support for SSH FTP (SFTP), a secure file transfer protocol.

FTP vs. PGP vs. SFTP vs. FTPS

Plain FTP does not provide security properties such as confidentiality (against eavesdropping) and integrity (against tampering). FTP provides authentication, but it is plain text. As such, plain FTP is insecure and strongly discouraged.

Even coupled with PGP file encryption and signature verification to protect the contents of the file, the protocol, the credentials, the files and the folders are still vulnerable.

On the other hand, SFTP provides secure file transfer over an insecure network. SFTP is part of the SSH specification. This is what I will explore in this post.

There is also FTPS (also known as FTP-SSL and FTP Secure). Maybe I will explore that in another post.

MEC 9.x, 10.x, 11.4.2.0

If you have MEC 11.4.2.0 or earlier, your MEC does not have full built-in support for SFTP or FTPS. I found some traces in MEC 10.4.2.0, 11.4.1 and 11.4.2. And I was told that support for SFTP was made as a plugin sort of, in some MEC 9.x version; maybe they meant FTPS. Anyway, if you are handy, you can make it work. Or you can manually install any SFTP/FTPS software of your choice and connect it to MEC. Do not wait to secure your file transfers.

MEC 11.4.3.0

MEC 11.4.3.0 comes with built-in support for SFTP, and I found traces of FTPS. Unfortunately, it did not yet ship with documentation. I was told a writer is documenting it now. Anyway, we can figure it out by ourselves. Let’s try.

Here are the release notes:
3

In Partner Admin > Managed > Advanced, there are two new SFTP channels, SFTPPollIn and SFTPOut, which are SFTP clients:
5 6

By looking deeper at the Java classes, we find JCraft JSch, a pure implementation of SSH2 in Java, and Apache Commons VFS2:
2 13

SFTPOut channel

In this post, I will explore the SFTPOut channel in MEC which is an SFTP client for MEC to exchange files with an existing SFTP server.

Unit tests

Prior to setting up MEC SFTPOut, we have to ensure our MEC host can connect to the SFTP server. In my case, I am connecting to example.com [11.22.33.44], on default port 22, with userid mecuser, and path /outbound. Contact the SFTP server administrator to get the values, and eventually contact the networking team to adjust firewall rules, name servers, etc.

Do the basic networking tests (ICMP ping, DNS resolution, TCP port, etc.):

Then, do an SSH test (in my example I use the OpenSSH client of Cygwin). As usual with SSH TOFU, verify the fingerprint on a side channel (e.g. via secure email, or via phone call to the administrator of the SFTP server assuming we already know their voice):

Then, do an SFTP test:
4b

Optionally, compile and execute the Sftp.java example of JSch. For that, download Ant, set JAVA_HOME and ANT_HOME in build.bat, set the user@host in Sftp.java, and execute this:
build.bat
javac -cp build examples\Sftp.java
java -cp build;examples Sftp

16

Those tests confirm our MEC host can successfully connect to the SFTP server, authenticate, and exchange files (in my case I have permissions to put and retrieve files, not to remove files).

Now, we are ready to do the same SFTP in MEC.

Partner Admin

In Partner Admin > Manage > Communication, create a new Send channel with protocol SFTPOut, hostname, port, userid, password, path, filename, extension, and other settings:
78

I have not yet played with all the file name options.

The option for private key file is for key-based client authentication (instead of password-based client authentication). For that, generate a public/private RSA key pair, for example with ssh-keygen, and send the public key to the SFTP server administrator, and keep the private key for MEC.

The button Send test message will send a file of our choice to that host/path:
10

The proxy settings are useful for troubleshooting.

A network trace in Wireshark confirms it is SSH:

Now, we are ready to use the channel as we would use any other channel in MEC.

Problems

  • The SFTPOut channel does not allow us to verify the key fingerprint it receives from the SFTP server. Depending on your threat model, this is a security vulnerability.
  • There is a lack of documentation (they are working on it)
  • At first, I could not get the Send test message to work because of the unique file name (I am not familiar with the options) and the jzlib JAR file (see below).
  • MEC is missing JAR file jzlib, and I got this Java stacktrace:
    com.jcraft.jsch.JSchException: java.lang.NoClassDefFoundError: com/jcraft/jzlib/ZStream
    at com.jcraft.jsch.Session.initDeflater(Session.java:2219)
    at com.jcraft.jsch.Session.updateKeys(Session.java:1188)
    at com.jcraft.jsch.Session.receive_newkeys(Session.java:1080)
    at com.jcraft.jsch.Session.connect(Session.java:360)
    at com.jcraft.jsch.Session.connect(Session.java:183)
    at com.intentia.ec.communication.SFTPHandler.jsch(SFTPHandler.java:238)
    at com.intentia.ec.communication.SFTPOut.send(SFTPOut.java:66)
    at com.intentia.ec.partneradmin.swt.manage.SFTPOutPanel.widgetSelected(SFTPOutPanel.java:414)

    I was told it should be resolved in the latest Infor CCSS fix. Meanwhile, download the JAR file from JCraft JZlib, copy/paste it to the following folders, and restart MEC Grid application and Partner Admin:
    MecMapGen\lib\
    MecServer\lib\
    Partner Admin\classes\
  • Passwords are stored in clear text in the database, that is a security vulnerability yikes! SELECT PropValue FROM PR_Basic_Property WHERE PropKey='Password' . I was told it should be fixed in the Infor Cloud branch, and is scheduled to be merged back.
  • With the proxy (Fiddler in my case), I was only able to intercept a CONNECT request, nothing else; I do not know if that is the intention.
  • In one of our customer environments, the SFTPOut panel threw:
    java.lang.ClassNotFoundException: com.intentia.ec.partneradmin.swt.manage.SFTPOutPanel

Future work

When I have time, I would like to:

  • Try the SFTPPollIn channel, it is an SFTP client that polls an existing SFTP server at a certain time interval
  • Try the private key-based authentication
  • Try SFTP through the proxy
  • Try FTPS
  • Keep an eye for the three fixes (documentation, jzlib JAR file, and password protection)

Conclusion

This was an introduction about MEC’s support for SFTP, and how to setup the SFTPOut channel for MEC to act as an SFTP client and securely exchange files with an existing SFTP server. There is more to explore in future posts.

Please like, comment, subscribe, share, author. Thank you.

UPDATES 2017-01-13

  • Corrected definition of SFTPPollIn (it is not an SFTP server as I had incorrectly said)
  • Added security vulnerability about lack of key fingerprint verification in MEC SFTPOut channel
  • Emphasized the security vulnerability of the passwords in clear text

HTTP channels in MEC (part 5 bis)

I will re-visit the HTTPSOut communication channel of Infor M3 Enterprise Collaborator (MEC) as a follow-up of my previous post part 5.

TLDR; I stopped using the HTTPSOut channel for its various problems. Now, I use the Send Web Service process.

Previously

In part 5, I had used the sample HTTPSOut channel from the MEC documentation, for MEC to make HTTPS requests, but it turned out to have a variety of problems.

Then, in a previous post I had explored the Send Web Service process for MEC to make SOAP requests, and I had realized it can be used for any content, not just for SOAP. I will explore that here.

Nowadays

Instead of HTTPSOut, use the Send Web Service process. It is easy to use: set the Service Endpoint to the desired URL, put whatever value in the SOAP Action (it will be ignored), set the Content type (e.g. text/plain, application/xml), select the checkbox if you want the response, and set the user/password if you need Basic authentication:

The process will use Java’s HttpsURLConnection and its default hostname verifier and certificate validation. If the URL endpoint uses a certificate that is not part of the MEC JRE truststore, you will have to add it with keytool import (see part 5).

Orchestration

In MEC, the processes are piped in order: the output of one process is the input of the next process. In my example, I send a message to my agreement, that goes through an XML Transform with my mapping, the output of that is sent to example.com, and the HTTP response of that is archived.

Maintenance

One problem with the Send Web Service is the URL, user, and password are hard-coded in the agreement, and I do not know how to un-hard-code them; I would like to use an external property instead.

We have many agreements, and several environments (e.g. DEV, EDU, TST, PRD) each with its URL, user, and password. When we migrate the agreements to the other environments, we have to manually update each value in each agreement, it is a maintenance nightmare.

As a workaround, I mass SQL UPDATE the values in MEC’s table PR_Process_Property:

SELECT REPLACE(PropValue, 'https://DEV:8082', 'https://TST:8083') FROM PR_Process_Property WHERE (PropKey='Service Endpoint')
SELECT REPLACE(PropValue, 'john1', 'john2') FROM PR_Process_Property WHERE (PropKey='User')
SELECT REPLACE(PropValue, 'password123', 'secret123') FROM PR_Process_Property WHERE (PropKey='Password')

Note: Use UPDATE instead of SELECT.

4

Conclusion

That is my new setup for MEC to do HTTPS outbound.

Related articles

That’s it.

HTTP channels in MEC (parts 3,6 bis)

I will re-visit the HTTPSyncIn and HTTPSyncOut communication channels of Infor M3 Enterprise Collaborator (MEC) as a follow-up of my previous posts part 3 and part 6.

TL;DR: It turns out there is no obligation to use a channel detection, we can use any of the available detections (e.g. XML detection). Also, it turns out we can re-use the HTTPSyncIn in multiple agreements (i.e. shared port).

Previously

In part 3, I showed how to use the HTTPSyncIn and HTTPSyncOut channels for MEC to receive documents over HTTP (instead of traditionally FTP). And in part 6, I showed how to add nginx as a reverse proxy with TLS termination for MEC to receive the documents securely over HTTPS (encrypted).

As for MEC, an HTTPSyncIn and its port number can only be used in one agreement; otherwise MEC will throw “Target values already defined”:
reuse3

As a workaround, I had to setup one HTTPSyncIn per agreement, i.e one port number per agreement. It is feasible (a computer has plenty of ports available), but in the configuration of nginx I had to setup one server block per agreement, which is a maintenance nightmare.

It turns out…

It turns out the HTTPSyncIn persists the message to disk and queues it for detection and processing:
0_

That means I do not have to use a channel detection in the agreement, I can use any of the other detections, such as XML or flat file detection.

Channel

Like previously, setup one HTTPSyncIn (e.g. port 8085), it will apply to the entire environment:
1-2

And like previously, setup one HTTPSyncOut, it can be used by any agreement:
1-3

Detection

Setup as many XML detections as needed, e.g.:
2-1 2-2 2-3 2-4

Agreement

Here is the key difference compared to previously: select XML Detection, not channel detection:
3-2__

In the processes, like previously, use the Send process with the HTTPSyncOut:
3-4

Setup as many agreements like that as needed.

nginx

If you use nginx for HTTPS, setup one server block only for all agreements (instead of one per agreement), listen on any port (e.g. 8086) and forward to the HTTPSyncIn port (e.g. 8085):

server {
    listen 8086 ssl;
    ssl_certificate .crt;
    ssl_certificate_key .key;
    location / {
        auth_basic "Restricted";
        auth_basic_user_file .htpasswd;
        proxy_pass http://localhost:8085/;
    }
}

Conclusion

Those were my latest findings on the easiest way to setup HTTPSyncIn in MEC to receive HTTP(S) when having multiple agreements. My previous recommendation to use one channel (one port) per agreement does not apply anymore. Everything will go through the same port, will be persisted and queued, and MEC will iterate through the detections to sort it out and process. I do not know if there are any drawbacks to this setup; for future discovery.

That’s it!

Let me know what you think in the comments below, click Like, click Follow to subscribe, share with your colleagues, and come write the next blog post with us.

SOAP WS-Security in the context of M3

Here is my high-level understanding of SOAP Web Services Security (WS-Security, or WSS), at least the WSS X.509 Certificate Token Profile, and how to apply it in the context of Infor M3.

WS-Security

WS-Security is a standard by the OASIS consortium to provide message encryption and digital signature, the usual security properties to prevent eavesdropping and tampering of a message. It uses asymmetric cryptography with public-private keys and digital certificates. There is an additional property which is central to WSS: the security happens at the message level, not at the transport level, i.e. the security follows the message even across proxies and deep packet inspection gateways, for end-to-end security. WSS is common for example in financial institutions that need to inspect and route a message through several nodes that can read the non-secure part of the SOAP envelope yet not reveal the secret in the message, until it reaches the appropriate destination. If a node on the path gets compromised, the security of the message is not compromised. Despite its continued use, WSS has only had few major updates in 10 years, is not considered secure [1] [2], the Internet agrees it is complicated and design-by-committee, and there is no industry momentum behind it.
versus1_

SSL/TLS, on the other hand, provides similar security properties with encryption and digital signature, using public key cryptography as well, but the security happens at the transport level, i.e. before the message level, for point-to-point security only. Thus, intermediate proxies and deep packet inspection gateways are unable to reveal the message to inspect it and route it, unless they have a copy of the destination’s private key. The workaround is to setup a chain of TLS segments, but the compromise of a node on the path, would compromise the message. TLS has additional security properties such as cipher suite negotiation, forward secrecy, and certificate revocation. TLS is constantly being updated with the latest security properties, and is widely supported and documented.

I have seen WSS interfaces used at banks and credit card companies that still have ancient mainframes and old middleware, and WSS is always used in combination with TLS, with peer authentication, thus four sets of public/private keys and digital certificates.
versus3

Infor Grid

Several applications of the Infor Grid expose SOAP web services, but I could not find how to setup WS-Security at the Grid level, so I assume it is not supported at the Grid level, only at the application level; that’s OK as SOAP over HTTPS is sufficient for the Grid’s needs.

grid

M3 Web Services (MWS)

The MWS application does have settings to configure WS-Security (X.509 Token policy); that would be useful for an external partner to call M3 with message-level security (otherwise there is always SOAP over HTTPS); I will explore this in a future post:
MWS

M3 Enterprise Collaborator (MEC)

The MEC application on the Grid does not have built-in SOAP web services. But in MEC Partner Admin the MEC developer can setup SOAP. The SOAP client Send Web Service process does not support WS-Security; this is most unfortunate as here is precisely where I want to setup the secure interfaces with the banks and credit card companies, bummer, I will have to develop my own WS-Security client in Java. On the other hand, the SOAP server WebServiceSyncIn does support WS-Security; I will explore this in a future post:
1

Future work

In future posts, I will:

  • Explore WS-Security in MWS
  • Explore WS-Security in MEC SOAP client
  • Explore WS-Security in MEC SOAP server

Conclusion

That was my high level understanding of WS-Security, at least the WSS X.509 Certificate Token Profile, and how to apply it in the context of M3. I am no expert in WS-Security, but this understanding is sufficient for my needs with M3.

That’s it!

Please subscribe, comment, like, share, and come author with us.

–Thibaud

 

Calling SOAP web services from MEC

Here is a primer on how to invoke SOAP web services from Infor M3 Enterprise Collaborator (MEC) using the Send Web Service process, where MEC is the SOAP client calling SOAP servers.

MEC process

I am using MEC version 11.4.3.

We find the Send Web Service process in Partner Admin > Agreement > Processes:
1

The properties are the following:
1_

The MEC Partner Admin Tool User Guide does not have much information:
3

The MEC training workbook does not have information either.

I decompiled MEC and found the Java class com.intentia.ec.server.process.SendWebServiceProcess. I was expecting it to use a legitimate SOAP client such as Apache CXF, but it uses a mere java.net.HttpURLConnection:
2

Consequently, this process does no more than the HTTPOut process albeit the additional SOAP action property.

Problems

There are many problems with this type of implementation:

  • It does not validate the message against the web service’s WSDL, the XML Schemas, not even against XML syntax
  • It does not have a factory to create client stubs
  • It is byte-based (we could send whatever content) whereas SOAP clients are more RPC-like with setters and getters for parameters
  • It is HTTP-centric, not SOAP-centric
  • It is restricted to HTTP whereas SOAP is agnostic to the underlying transport protocol, e.g. SOAP supports FTP
  • It does not support WS-Security for XML Encryption and XML Signature
  • It does not support the use of HTTP proxy
  • Etcetera

Anyway, let’s give it a try.

Sample web service

I have a sample web service from TMW SystemsLink, a Transportation Management Software. It is available on my customer’s network. It is only setup for HTTP (not HTTPS), without authentication, thus it is insecure, but it is easy for illustration purposes.

First, I ensure I can get to the WSDL:
5_

Test with SoapUI

Then, I test the web service with a SOAP client such as SoapUI:

Then, I get the SOAP action and content type to be used later (we can get them from the WS-A and http log tabs of SoapUI or from Fiddler):

Test with Java – optional

Optionally, I test the web service with the URLConnection in Java, from the same location and JRE as MEC:

javac Test.java && java -cp . Test
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.URL;
import java.net.URLConnection;

class Test {
	public static void main(String[] args) throws Exception {
		URL url = new URL("http://tmwsl/TMWSystemsLink/APIWCFServices.svc");
		URLConnection con = url.openConnection();
		con.setDoOutput(true);
		con.setRequestProperty("Content-Type", "text/xml;charset=UTF-8");
		con.setRequestProperty("SOAPAction", "http://tempuri.org/IAPIWCFServices/RetrieveCarrier");
		String data = "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:tem=\"http://tempuri.org/\" xmlns:tmw=\"http://schemas.datacontract.org/2004/07/TMWSystems.SystemsLink.APIClasses\"><soapenv:Header/><soapenv:Body><tem:RetrieveCarrier><tem:criteria><tmw:CarrierID>JONCOL</tmw:CarrierID></tem:criteria></tem:RetrieveCarrier></soapenv:Body></soapenv:Envelope>";
		con.setRequestProperty("Content-Length", "" + data.length());
		OutputStreamWriter out = new OutputStreamWriter(con.getOutputStream());
		out.write(data);
		out.close();
		BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream()));
		String s;
		while ((s = in.readLine()) != null) {
			System.out.println(s);
		}
		in.close();		
	}
}

The result is the SOAP response:
7

Test in Partner Admin

Now, I am ready to test in Partner Admin. I create a simple test agreement with the Send Web Service process configured to the end point address, content type, and SOAP action:
8

Then, I add a simple detection such as the default DiskIn, I reload the communication channel in the MEC Grid Management Pages, I create a file with the sample SOAP request, I drop the file in the DiskIn folder, I wait for MEC to process the file, and I check the result; the usual steps in MEC.

Result

Here is the result in MEC Grid Management Pages, it successfully finished sending the SOAP request, and getting the SOAP response:
9

Here is the resulting POST HTTP request: 10

At this point we can use Partner Admin and MEC Mapper to transform an M3 MBM and generate the SOAP request, we can process the SOAP response, etc.

Conclusion

That was an illustration of how to call SOAP web services from Infor M3 Enterprise Collaborator (MEC) using the Send Web Service process, for MEC to be a SOAP client calling SOAP servers. Despite “Web Service” in its name, the process is deceitfully not very SOAP oriented, but we can manage with it.

Future work

In future posts I will:

  • Explore how to securely call a web service over HTTPS (not HTTP); if I use a custom server certificate, I have to setup the JRE keystore, or explore the Partner Admin > Manage > Certificate Keystores:
    future1
  • Explore how to use the Partner Admin > Manage > Web Service Definitions; I do not know what this is for:
    future2
  • Explore how to use the Manage > Advanced > WebServiceSyncIn/Out; I think that is for MEC to be a SOAP server, accept requests, and serve responses:
    future3 future3_

That’s it.

Please leave a comment, click Like, click Follow to subscribe, share around you, and come write the next blog post.

Custom UI for MEC

Here is how to create a custom user interface for M3 Enterprise Collaborator (MEC) in the Infor Grid.

The problem

At my current customer, users create purchase orders like this: a buyer creates a purchase order in PPS170/PPS200, then M3 sends an MBM document to MEC, and then MEC sends a cXML PunchOut OrderRequest to the supplier. For some reason PPS200 does not allow changing the status of an order then, so MEC cannot give feedback to the buyer that the supplier acknowledged the order or that there was an error, i.e. the buyer does not know the outcome. The workaround is to setup error handling to have MEC send an email to an administrator in case of error, and the administrator to tell the buyer. But it is a poor design because it is a negative goal – it informs the user only in the absence of acknowledgment – it is a weak link – the administrator may or may not inform the buyer – and it is reactive, not pro-active.

A solution

Instead, I will build a custom page that shows the status of the outgoing purchase orders in MEC, with timestamp, company CONO, division DIVI, and purchase order number PUNO, I will give access to M3 users, and I will place it as a shortcut in PPS200. It will be a useful feedback loop for end-users. It is not as tight a loop as I would like it to be, but at least buyers will be able to verify the status of their orders on their own.

Inspiration

I was inspired by the MessageSearchPage of the Infor ION Grid Management Pages > MEC_UI:1

I decompiled the ec-gridui-x-y-z.jar file to see how it builds the contents of the page:

and to see how StateQuery gets the data from the MEC database:
3

My own content

The mapping adds the CONO, DIVI, and PUNO to the manifest in a Java function with:

setManifestInfo("map:keyValue1", CONO);
setManifestInfo("map:keyValue2", DIVI);
setManifestInfo("map:keyValue3", PUNO);

I prepared an SQL query that gets the status of the outgoing purchase orders with the CONO, DIVI and PUNO from the MEC manifests:
4

HelloWorld Page

Here is a simple page:

package thibaud;

import com.lawson.grid.ui.framework.PageProvider;
import com.lawson.grid.ui.framework.UIRequest;
import com.lawson.grid.ui.framework.UIResponse;
import com.lawson.grid.ui.meta.widget.Page;

public class HelloPage implements PageProvider {
    public Page getPage(UIRequest request, UIResponse response) {
        Page p = new Page("Hello Page");
        p.add("Hello, World!");
        return p;
    }
}

I drop the class in the custom folder of the MEC Grid server at:

D:\Infor\LifeCycle\?\grid\?\grids\?\applications\MECSRV\MecServer\custom\

5

Without restarting the server, I access the page at https://hostname:22108/grid/ui/#thibaud.HelloPage/MECSRVDEV:
6

I add the usual UI widgets: labels, buttons, images, table, links, etc.:
8

I restrict access to authenticated users by adding this annotation:

@Session

I can also restrict access to a specific Grid role mapping, such as app-user, or to my own role PunchOut that I would have to create and assign users to:

@Session(roles={"app-user"}) // MECSRVDEV/app-user
@Session(roles={"PunchOut"}) // MECSRVDEV/PunchOut

When I modify the class file, I stop and start the MEC_UI node:
7

Result

Here is the result of my page; it is similar to the built-in MessageSearchPage, but it is filtered to show outgoing purchase orders only, with additional columns company CONO, division DIVI, and purchase order number PUNO, and it is accessible by end users, except the link to the logs which is for administrators:

Full source code

Here is the full source code of my page; this code is more recent than the older screenshot above, and the code is not finished, but it is a start for you:

package thibaud;

import java.io.UnsupportedEncodingException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.TimeZone;
import com.intentia.ec.db.ConnectionPool;
import com.lawson.ec.gridui.PropertiesSourceFactory;
import com.lawson.ec.gridui.page.MessageDetailPage;
import com.lawson.ec.gridui.page.ViewFilePage;
import com.lawson.ec.gridui.page.ViewMessageLogsPage;
import com.lawson.ec.gridui.util.DateUtility;
import com.lawson.grid.proxy.access.Session;
import com.lawson.grid.ui.framework.PageProvider;
import com.lawson.grid.ui.framework.UIRequest;
import com.lawson.grid.ui.framework.UIResponse;
import com.lawson.grid.ui.meta.widget.Label;
import com.lawson.grid.ui.meta.widget.Link;
import com.lawson.grid.ui.meta.widget.Page;
import com.lawson.grid.ui.meta.widget.URLLink;
import com.lawson.grid.ui.meta.widget.WidgetTable;
import com.lawson.grid.ui.meta.widget.iface.Container;
import com.lawson.grid.ui.meta.widget.iface.Param;
import com.lawson.grid.ui.meta.widget.iface.StyledText;
import com.lawson.grid.util.logging.GridLogger;

@Session
public class OrderRequestsPage implements PageProvider {

	static GridLogger cat = GridLogger.getLogger(OrderRequestsPage.class);

	public Page getPage(UIRequest request, UIResponse response) {

		// prepare the results table
		Page p = new Page("PunchOut OrderRequests Page");
		WidgetTable table = new WidgetTable();
		table.setHeaders(new String[] { "", "Time (" + TimeZone.getDefault().getDisplayName() + ")", "Company", "Division", "Purchase order", "State", "Message Details", "Message Logs", "MBM identifier", "cXML OrderRequest", "Response" /*, "IsOK", "IsReprocessed", "IsRetried", "IsVerified", "IsWaiting", "IsRecoverable"*/ });
		table.setColumnAlignment(new Container.HAlign[] {
			Container.HAlign.RIGHT,   // #
			Container.HAlign.LEFT,    // Time
			Container.HAlign.CENTER,  // Company
			Container.HAlign.CENTER,  // Division
			Container.HAlign.CENTER,  // Purchase order
			Container.HAlign.LEFT,    // State
			Container.HAlign.CENTER,  // Message Details
			Container.HAlign.CENTER,  // Message Logs
			Container.HAlign.LEFT,    // MBM identifier
			Container.HAlign.CENTER,  // cXML OrderRequest
			Container.HAlign.CENTER,  // Response
		});
		table.setBorderStyle(Container.BorderStyle.SOLID_WEAK);
		table.setAutoSequenceRowHighlight(WidgetTable.RowHighlight.BLUE_WEAK);
		p.add(table);

		// build the query
		String query =
			"SELECT DISTINCT TOP 100\n" +
				"H.UUID,\n" +
				"S.LogTime,\n" +
				"M1.ManifestValue AS CONO,\n" +
				"M2.ManifestValue AS DIVI,\n" +
				"M3.ManifestValue AS PUNO,\n" +
				"M4.ManifestValue AS mbmIdentifier,\n" +
				"M5.ManifestValue AS FilePath,\n" +
				"S.State,\n" +
				"S.IsOK,\n" +
				"S.IsReprocessed,\n" +
				"S.IsRetried,\n" +
				"S.IsVerified,\n" +
				"S.IsWaiting,\n" +
				"S.IsRecoverable\n" +
			"FROM\n" +
				"dbo.DocManifestsHeader AS H,\n" +
				"dbo.DocStates AS S,\n" +
				"dbo.DocManifests AS M1,\n" +
				"dbo.DocManifests AS M2,\n" +
				"dbo.DocManifests AS M3,\n" +
				"dbo.DocManifests AS M4,\n" +
				"dbo.DocManifests AS M5\n" +
			"WHERE H.UUID=S.UUID AND S.UUID=M1.UUID AND M1.UUID=M2.UUID AND M2.UUID=M3.UUID AND M3.UUID=M4.UUID AND M4.UUID=M5.UUID\n" +
				"AND Partner='PunchOut' AND Agreement='Out_PunchOut_OrderRequest_SendAll'\n" +
				"AND M1.Name='map:keyValue1'\n" +
				"AND M2.Name='map:keyValue2'\n" +
				"AND M3.Name='map:keyValue3'\n" +
				"AND M4.Name='mvx:mbmIdentifier'\n" +
				"AND M5.Name='map:keyValue4'\n" +
			"ORDER BY LogTime DESC\n";

		query = query.replace("dbo.", ConnectionPool.getCatalogSchema());
		cat.debug(query);

		Connection con;
		Statement stmt;
		ResultSet rs;
		try {
			// execute the query
			con = ConnectionPool.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			// render the result
			int row = 0;
			while (rs.next()) {
				String uuid = rs.getString("UUID");
				// row number
				table.setData(row, 0,  "" + (row + 1) + ".");
				// time
				Date date = new Date(rs.getLong("LogTime"));
				table.setData(row, 1,  DateUtility.getFormattedDate(date));
				// CONO
				String CONO = rs.getString("CONO");
				table.setData(row, 2,  CONO);
				// DIVI
				table.setData(row, 3,  rs.getString("DIVI"));
				// PUNO
				String PUNO = rs.getString("PUNO");
				try {
					String CONO_ = URLEncoder.encode(CONO, "UTF-8");
					String PUNO_ = URLEncoder.encode(PUNO, "UTF-8");
					String bookmark = "mforms://bookmark/?program=PPS200&tablename=MPHEAD&panel=B&includestartpanel=True&requirepanel=True&suppressconfirm=False&sortingorder=1&view=STD01-01&keys=IACONO%2c" + CONO_ + "%2cIAPUNO%2c" + PUNO_ + "&fields=IAPUNO%2c" + PUNO_;
					URLLink bookmarkLink = new URLLink(new URL(bookmark), PUNO); // PENDING
					table.setData(row, 4,  bookmarkLink);
				} catch (UnsupportedEncodingException e) {
					table.setData(row, 4,  PUNO);
				} catch (MalformedURLException e) {
					table.setData(row, 4,  PUNO);
				}
				// State...
				String state = rs.getString("State");
				int isOK = rs.getInt("IsOK");
				StyledText.Category c;
				if (isOK == 0) {
					c = StyledText.Category.ERROR;
				} else {
					c = StyledText.Category.OK;
				}
				table.setData(row, 5,  new Label(state,  StyledText.Size.NORMAL, StyledText.Style.BOLD, c));
				// Message Details
				Link messageLink = new Link("show", MessageDetailPage.class, new Param[] { new Param("uuid", uuid) });
				messageLink.setOpenInNewWindow(true);
				table.setData(row, 6,  messageLink);
				// Message Logs
				Link eventLink = new Link("show", ViewMessageLogsPage.class, new Param[] { new Param("uuid", uuid) });
				eventLink.setOpenInNewWindow(true);
				table.setData(row, 7,  eventLink);
				// MBM identifier; PENDING: see com.lawson.ec.gridui.page.MessageDetailPage.getAllData(String uuid)
				table.setData(row, 8,  rs.getString("mbmIdentifier"));
				// cXML OrderRequest
				String host = PropertiesSourceFactory.getInstance().getProperties().getProperty("Server.Context");
				cat.debug(host);
				String filepath = rs.getString("FilePath");
				Link xmlLink = new Link("show", ViewFilePage.class, new Param[] { new Param("host", host == null ? "local" : host), new Param("type", "xml"), new Param("path", filepath) });
				xmlLink.setOpenInNewWindow(true);
				table.setData(row, 9,  xmlLink);
				// cXML response
				table.setData(row, 10,  "PENDING");
				row++;
			}
			// cleanup
			rs.close();
			stmt.close();
			ConnectionPool.putConnection(con);
		} catch (SQLException e) {
			cat.error("SQLException when building PunchOut page", e);
			p.add(new Label("SQLException when building PunchOut page: " + e.getMessage()));
		}
					
		return p;
	}
}

Future work

Some ideas for future work are:

  • Tighten the loop even more, i.e. inform the user directly in PPS200 when they are creating the order
  • Get the state label; see com.lawson.ec.gridui.page.MessageSearchPage.getStateLabel()
  • Finish the MForms Bookmark link
  • Add a link to the MBM document (.rcv file)
  • Add a link to the cXML OrderRequest response received from the supplier
  • Register the page in the MEC menu
  • Get URL parameters from UIClientRequest
  • Add pagination, see com.lawson.ec.gridui.page.MessageSearchPage.buildPagingPanel()
  • Develop a Mashup that combines PPS200 and the SQL from MEC DB

Conclusion

That was a solution to develop custom UI pages for MEC; for example in my case a page for buyers to see the status of the outgoing purchase orders, with timestamp, company CONO, division DIVI, and purchase order number PUNO. It is a simple solution to provide feedback to end users so they can be pro-active and verify the outcome of their actions on their own.

That’s it.

Thanks for reading. Leave us a comment in the section below. And if you liked this, please subscribe, like, share, and come write the next blog post with us.

Error handling in MEC

Here are several ways to handle errors in Infor M3 Enterprise Collaborator (MEC).

(Disclaimer: I have not been to a MEC training, so these are my own findings, not necessarily the official Infor recommendation.)

1. Message detail page

By default, errors in MEC are reported in MEC Management Pages > Message > Status > State; we can drill down to see the MessageDetailPage with the manifest, the input/output files of each process, and the status:
3_

We can also go directly to the file system and get the manifest, and the input/output files of each process:
3c

This is sufficient for MEC administrators to troubleshoot.

But only users with the app-admin or grid-admin roles have access to this, and this is unannounced, so nobody will know there was an error unless somebody looks.

2. Retry/re-detect

The MessageDetailPage has buttons Retry to rerun from last failed process, and buttons Redetect to rerun from message detection:
9

If it helps, the MEC Fundamentals workbook says: “If an error occurs in an XMLTransform step, and Retry is selected, the Process starts with re-sending the input to the XMLTransform step. Meaning that the mapping is executed from the beginning.”

3. Event logs

MEC also shows detailed event logs at MEC Management Pages > Event > Log; it uses the Apache Log4J logger with levels DEBUG|INFO|WARN|ERROR|FATAL; it has a search form to search by UUID, class, text, time, etc.; the loggers are configurable via the web form or via the log.config file; and the logs are persisted in the MecLog table of the MEC database:
4__ 4___ 4____ 4_

This shows many more details than the message detail page, most interestingly the DEBUG|TRACE  levels, and the Java stack trace.

4. Error reports

We can drill down the MessageDetailPage to see the HTML error report; it contains the Java stack trace:
3__

We can also get the error reports directly from the file system:
3___

We can also generate a zipped error report package, e.g. to send to Infor Support:

We can change the DocErrorHandler properties to change the XSLT that renders the report, or to change the error report to text format:
3____ 3_____ 3______

5. ErrorMail

We can configure the ErrorMail properties in MEC to automatically send the error reports to a comma separated list of email addresses; the input file (.rcv) is zipped as an attachment:
5__

This is good to automatically notify administrators in case of errors, and it is great for deputies that don’t have the administrative privileges.

But the recipients risk receiving too many emails and becoming desensitized, resulting in the opposite intention.

6. Agreement Email

We can also set a comma separated list of email addresses in the agreement’s Basic tab, and MEC will send the error reports there instead:
6

This is good to delegate administration to the respective deputies of each agreement, instead of spamming the MEC administrators.

7. Mapping

If our mapping calls an M3 API and the API responds with an error, or if our mapping throws an exception, by default the mapping will stop and will call sendMail() to bubble up to the error report.

We can change this default behavior with the ErrorHandling property:

• Exit map on M3 NOK
• Exit loop on M3 NOK
• Ignore M3 NOK

1

And we can add a Java function that calls the methods isLastAPICallOK()hasNOKOccurred(), and getLastAPIMessage() of class com.intentia.ec.mapper.Mapping:

We can also throw an exception with throw new MeCError(“CRASH!”):
2_

All this to attempt recovering from errors, and to do our own error handling.

One of the MEC Development workbook exercises has the opinion that: “all API calls should have their ErrorHandling property set to “Ignore M3 NOK”to prevent the map from failing, and use method hasNOKOcurred() to determine if any API has returned NOK.”

Note: Sometimes it is desirable to produce a specific error output. However, mappings are unable to produce an output other than the XML schema they were designed for, i.e. there is no secondary schema we can output in case of error. The workaround is to do the error handling offline, e.g. build a custom error report and send it in a custom email.

I can summarize it like this:

// default
if (OK) use output schema
else send error report

// desired, but not possible
if (OK) use output schema
else use error schema

// workaround
if (OK) use output schema
else do custom error handling

We can analyze the generated mapping source to have a better understanding:
7

8. Error handling tab

We can use the agreement’s Error Handling tab to handle the error with one or more of these processes:

  • Apply Envelope
  • Create ConfirmBOD
  • Outbound MBM Status
  • Retrieve MBM Identifier
  • Send
  • XML Transform
  • XSL Transform

8

According to class ErrorTab, these processes are stored in table PR_Process with Standard=0, which gives us their class name:
8_ 8__

We can analyze the corresponding Java classes to understand what they do; they seem to made for peculiar requirements.

I created my own processes as illustrated in a previous post, and it works great:
8___

Future work

In the DocErrorHandler properties, there are some AdminException regex properties such that if an exception matches the regex, the DocErrorHandler will send the error report to both the email addresses set in the ErrorMail, and the email addresses set in the agreement. To be tested.

We can learn more about error handling in MEC by diving into ec-core-11.4.2.0.0.jar:
3b 3b_

Conclusion

There are several ways to handle errors in MEC:

  • Use the default message detail page, event logs, and error reports for the administrators to troubleshoot in detail, and eventually retry/re-detect
  • Set the ErrorMail and agreement email to automatically notify the deputies and delegate administration
  • Write custom Java code in the mappings to attempt recovering from errors and to do our own error handling
  • Develop our own error handling processes to have more control

That’s it!

Thanks for reading. If you liked this, please consider subscribing, give a like, leave a comment, share around you, and help us write the next post.