Execute DB Queries Inside The Mapper

Executing direct SQL queries inside the MEC mapper is not encouraged due to the performance issues, but If you don't have any other solution from APIs from WebServices, you can still use DB queries inside the map.

This is not the "Database" tool available in Mapper toolbar. Inside a Java function, you can use the below to pull some values from the database.

 String sql = "SELECT CFFACI FROM "+schema+".CFACIL WHERE CFCONO="+CONO+" AND CFFANM = "+locationCode;  
 String FACI = "";  
  java.sql.Connection con =      
   com.intentia.ec.db.ConnectionPool.getConnection();  
  java.sql.Statement stmt = con.createStatement();  
  java.sql.ResultSet rs = stmt.executeQuery(sql);  
  if (rs.next())   
  {  
   FACI = rs.getString("FACI").trim();  
  }  
  rs.close();   
  stmt.close();  

Process Setup of Partner Agreements in Infor Partner Administration Tool (PAT)

In this post, I'll share three different types of process setups in PAT which can be used with a Flat file/Disk-In channel-based MEC integration.

1. Read the flat file and push the contained data into M3.
2. Use a trigger file to extract some data from M3 and dump it into a flat file
3. Mixture of both: Read some values from a flat file, get some data from M3 and dump it into a flat file.

Note: It's required to configure the channels and the map should be published prior to getting certain processes visible in the Process tab.

1. Read the flat file and push the contained data to M3.

2. Use a trigger file to extract some data from M3 and dump it in to a flat file

3. Mixture of both :

FLAT To XML/XML to FLAT process must be defined with the appropriate flat file definition and the XML Transform process with the appropriate Map.

How Verify the developed MEC Mapper using MEC Loggers

In this post, I'm going to describe two ways that I've used to verify the mapper logic that I have implemented using the mapper diagrams and behind code. This is not about the debugging option that is discussed in the "M3 Enterprise Collaborator Mapping Manager User Guide".

As a best practice, you can use the build logger options below.

ALL LOGGING WITHIN A MAP MAP MUST PREVENT THE USE OF 'System.Out.Println();' AND MUST ONLY USE THE FOLLOWING API'S
cat.info();
cat.warning();
cat.error();
cat.debug();
cat.fatal();

~ M3 Enterprise Collaborator - Best Practices Guide

But, using these loggers unnecessarily will cost some additional I/O costs resulting to drain on the performance of the server. So it's good to place the logging statements with a boolean flag (initiate a global variable of type boolean) where you can initiate it to true during the development and testing period and set it to false when moving to production.

Ex :

 if(debug) {  
    cat.info(/*Some comment*/);  
 }  

My advice is to always use a specific prefix at the start of your comment text to make them locate faster. So initiate a const string variable and assign a unique value that you can use to identify the map uniquely. For this, it's recommended to use the Spec Code, Doc Code, Function Code etc. which is given to you.

As an example :

 cat.info( MyMapIdentifier + ": Some Comment" );   

There are two ways to check this logged information.

1. Using the Infor LifeCycle Manager

First logged in to LifeCycle Manager, and expand the M3 Enterprise Collaborator x.x.x.x.

Then right click on MEC and select Manage Application.

Click on Events

This view shows only the last 500 events recorded.

The disadvantage in here is that you can only view the last 500 events (by default), you can specify a time frame, part of the text message, etc. by using the Log search option.

2. Using the MEC Storage Database
To use this method, you have to have permission to access the database server. If you are tricky enough, You can locate the login information from the Eclipse (MEC Mapper) preference. Look for the required information inside ION Mapper à  Database Connectivity section.

Then you have to log into the database i.e. if is it Ms-SQL server to use SQL Management Studio.  Use the below query to list the events recorded.

select LogTime, Message  
 from dbo.MecLog  
 where LogTime > 1412853722833  
 order by LogTime ASC  

You can build the query by directly executing this query.

declare @query as varchar(512)

declare @maxValue as bigint
select @maxValue= max(LogTime) from MecLog

select @query='select  LogTime, Message
from dbo.MecLog
where LogTime >'+ 
convert(varchar(30),@maxValue) +'order by LogTime ASC'

select @query

The advantage in here is that you can get all the events, and you have the power of using SQL to filter-out the result.

How to Set MEC Outbound File Name Using Code

This article describes how to apply a unique name to the out-bound file that is generated from the MEC.

My requirement was to generate it as :

<SpecCode> +  [yyyyMMddhhmmss]

Retrieving the time spam inside the mapper has been described in this blog post.

First, the system date and time have to be generated and assigned to a string variable (as described in this blog post). Then call the below built in method inside a user-defined function (make sure that it's not inside a block that is controlled by logic). 

 setManifestInfo("map:M3_MEC_001", fileName);  

Then go to Partner Admin à Manage à Communication à Send Tab and select your channel configurations and click on edit. 

Now set the File name equally to what you have mentioned inside setManifestInfo method of your code, but with brackets. Set file extension to " - " (no spaces or quotation marks). Set the value of "Class to make file name unique" to MEC_InfixManifest.


Now trigger your inbound channel and check the output.

Get the Date and Time inside MEC Mapper

While doing some MEC developments my team encountered a requirement where we have to send the result to an outbound as a flat file, and the name of the file should be

<specificationCode>.yyyyddmmhhmmss.xml.

The difficulty that we faced was we cannot import any packages rather than the packages set which are imported by default. So get the date and the time without using java.util was a big challenge.

Using Code Assist (similar to IntelliSense in Visual Studio) I found the below class and methods which can cater the above file name generation requirement.

 com.infor.ecutil.util.DateTimeConverter c = new DateTimeConverter();  
 String fileName = "MyFileName." + c.getEDIDateTime()+""+c.getEDIDateTime("TS")+".xml";  

"TS" => the Format Qualifier

I've found the below Format Qualifies in Phillip Kuo's blog.

It seems X12 and EDIFACT are fairly equivalent regarding dates.
An example of the newer X12 standards for dates would be Data Element 1250
and how it defines the next element; 1251 (The actual date being reported).

Some of the valid values for DE1250 are:

CC – First two digits of the year expressed in the format CCYY (length of 2)
CM – Date in Format CCYYMM (length of 6)
CY – Year Expressed in Format CCYY (length of 4)
D6 – Complete Date expressed in the format YYMMDD (length of 6)
D8 – Complete Date expressed in the format CCYYMMDD (length of 8)
DB – Date Expressed in Format MMDDCYY
DD – Day of Month in Numeric Format (length of 2)
DT – Date and Time Expressed in Format CCYYMMDDHHMM (length of 12)
MD – Month of Year and Day of Month in Format MMDD (length of 4)
MM – Month of Year in Numeric Format (length of 2)
TM – Time Expressed in Format HHMM (length of 4)
TS – Time Expressed in Format HHMMSS (length of 6)
YM – Year and Month Expressed in Format YYMM (length of 4)
YY – Last two digits of Year Expressed in Format CCYY (length of 2)
RD2 – Range of Years Expressed in Format YY-YY (length of 5)
RD4 – Range of Years Expressed in Format CCYY-CCYY (length of 9)
RD5 – Range of Years and Months Expressed in Format CCYYMM-CCYYMM (length of 13)
RD6 – Range of Dates Expressed in Format YYMMDD-YYMMDD (length of 13)
RD8 – Range of Dates Expressed in Format CCYYMMDD-CCYYMMDD (length of 17)
RDM – Range of Dates Expressed in Format YYMMDD-MMDD (length of 11)
RDT – Range of Date and Time, Expressed in Format CCYYMMDDHHMM-CCYYMMDDHHMM (length of 25)
RMD – Range of Months and Days Expressed in format MMDD-MMDD (length of 9)
RTM – Range of Time Expressed in Format HHMM-HHMM (length of 9)

DE1251 is a 35 byte alpha-numeric field.

Verify the Basic Environment Setup for MEC Development

In this post, I thought of describing how to make sure that the basic environment is set up for the MEC development.

You should have the following installed and properly configured.

1. M3 Enterprise Collaborator (Should contain Flat File Repository Manager, Partner Administrator)
2. LifeCycle Manager
3. Infor Smart Office (in case if you need to verify what you have done)

Check for the Infor installation folder in one of your disk partitions, most probably it should be in your system partition. Inside that there should be a folder called MEC.  Inside that, check for the below files and folders.

1. CentralFiles : is the pre-configured folder where Inbound channels are configured to drop the files.
2. Mapper :  Eclips pre installed folder, Inside plugins folder, check whether for com.lawson.xxxx like folders.
3. Workspaces : Eclips work-spaces,

Check whether you have these applications installed.

1. Flat File Repository Manager
2. Partner Administrator
3. Eclips (from the Mapper folder, you might be prompted to enter ION password if you have configured the ION Mapper), This should be available inside your default Infor installation folder i.e. C:\Infor\MEC\Mapper. You need to apply ION configurations to your workspace, to that you need MEC database connection details, M3 API connection details etc. better to contact your technical consultant and get the required info.
4. LifeCycle Manager (Provide your user name/password)

Having these all installed doesn't say go ahead with the development. First, make sure you can log into Smart Office and LifeCycle Manager. Then run MITEST program from SmartOffice and verify whether the described APIs (in the functional specification) are available, and they return valid data.
Before starting the development, it's better to create some test data and verify all the APIs which are going to be used with the development. Until that step completes, do not start the mapper development.

Infor - M3 Enterprise Collaborator (MEC)

MEC is an Infor M3 software component that can be used to integrate M3 with different systems by enabling Infor M3 to create, send, and receive electronic documents in XML and flat file formats.

In simple words, Infor MEC is a Lawson M3-specific application connector like Microsoft BizTalk Server.  

The M3 Enterprise Collaborator (MEC) product and ION Connect provide the interface between Lawson M3 applications and other Infor applications.

So, What is Infor ION?

Infor ION is Infor’s business middleware. Infor ION is a tool that allows customers to connect business applications, organize business processes, and empowers users to be more effective. Infor ION is the gateway to share business object documents (BODs) with Infor applications.

MEC can be used in three different ways:

1. Messaging connector via XML or Flat messages:

MEC exposes M3 business logic as XML or Flat interface documents and interacts via request/reply scenarios.

2. As an EDI solution : 
Full EDI infrastructure with supplementary, ready-made message interfaces based on industry standards like EANCOM, ODETTE, VDA, TRADACOMS, and ANSI X.12.

3. Lightweight Message Broker
Easy and straightforward to integrate M3 applications with the messaging interfaces of other
applications.

MEC can use Lawson Web Services (LWS), MI-programs, External Program Connector (EPC), and M3 Output Management (MOM) to retrieve data from M3 or write data to M3. Requests to MEC and replies from MEG are sent in XML or Flat File format.

Sources :
1. MEC Fundamentals Training Workbook
2. Infor10 Lawson Process Automation Frequently Asked Questions