Wednesday, December 10, 2014

Get MEC User Name (USID) inside the Map

There are two ways to retrieve the MEC username inside the MEC mapper.

1. Using a Standard API

    API : CRS008MI - Customer Order Interface
    Transaction: GetUserInfo
    Input: none
    Output Field Which contains USID : ZZUSID

2. Using the build in methods in com.intentia.ec.mapper package

string usid = getMvxUser();  

Sunday, November 16, 2014

MEC How to Get the Last API Name, Status and the Error Message

Recently, I've been working with Logging API errors using another custom M3 API (CRKR03) as a part of MEC development. Some API's had returned NOKs with additional white spaces i.e. TABS and Spaces exceeding the field (MESG) length of the CRKR03 that we have used to log errors.

First, let me explain how I've received the API NOK message by setting the ErrorHandling Property of the M3 API to Ignore M3 NOK.


if(!isLastAPICallOK() && getLastMIProgram().trim().equals("PDS001MI"))
{
    g_ErrorMessage = getLastAPIMessage().trim().replaceAll("\\s+", " ");
    ........ 
}  
Some API NOK messages are like the below (Note the additional white spaces)
'Item number 010411 does not exist                     
                                            
                                            
                                            
              WIT0103          ' 

Monday, November 10, 2014

Get the Inbound Trigger File Name

This article describes how to receive the inbound trigger file name (when using the Disk-in channels mode) using the code.

You can cross-verify the trigger file name by observing the archived manifest from the LifeCycle Manager.
 

Then locate the below value :



You can use the below code to get the trigger file name inside the mapper. (g_ProcessFileName is a string type global variable).

 g_ProcessFileName = getManifestInfo(ManifestConstants.COM_FILENAME);


Similarly, You can get most of the fields shown in manifest by using the getManifestInfo method and the ManifestConstant class. Please note that the ManifestConstants only contains the name of the key/attribute.

Get the UUID of the MEC Process

Recently, We were asked to log the MEC errors to a custom API. MEC process ID was required to be passed into this API as an input parameter.

You can verify the UUID from the LifeCycle Manager.


Using the below code, we could retrieve the UUID inside from the MEC mapper. (g_MapUUID is a global variable)
 g_MapUUID = com.intentia.ec.mapper.Mapping.this.strUUID; 

this variable can be even directly access as below.
 g_MapUUID = strUUID; 

or you can get this by using the manifest and global.
g_MapUUID = getManifestInfo(ManifestConstants.CMN_UUID); 

Please note that the ManifestConstants class contains only the names of the properties. 

Friday, October 31, 2014

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 EANCOMODETTE, 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

Thursday, August 28, 2014

SQL Server Connections Leaks

I've came across with an exam question on MCTS data access dump and found this topic, I just googled and thought of writing a blog with what I found.

Connection leak basically happens when we open a connection to the database from our application and forget to close it, or due to some reasons it doesn't get closed.

Symptoms of Connection Leaks :
  1. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
  2. SQL Server Does Not Exist Or Access Denied 
  3. General Network Error 

If your application is leaking connections to Microsoft SQL Server, you can find the last SQL statement that was run for a connection by running this query


SQL SERVER 2008 R2
SELECT recent.text AS 'Last SQL Statement',
               connection.client_net_address AS 'client IP',
               connection.connect_time AS 'connected since'
FROM sys.dm_exec_connections AS connection
CROSS APPLY sys.dm_exec_sql_text(connection.most_recent_sql_handle) AS recent
ORDER BY connection.connect_time ASC

SQL SERVER 2008
SELECT recent.text AS 'Last SQL Statement',
               connection.client_ip_address AS 'client IP',
              connection.connect_time AS 'connected since'
FROM sys.dm_exec_connections AS connection
CROSS APPLY sys.dm_exec_sql_text(connection.most_recent_sql_handle) AS recent
ORDER BY connection.connect_time ASC

You can use the below code to find out whether your application is explicitly closing or disposing the SQL connections.

 string instanceName = Assembly.GetEntryAssembly().FullName;  
 PerformanceCounter pCount = new PerformanceCounter(".NET Data Provider for Sqlserver",  
                                             "NumberOfReclaimedConnections",  
                                             instanceName,  
                                             true );  
 int leakedConnetionsCount = (int) instanceName.NextValue();  


Read more at this MSDN article

Friday, July 11, 2014

Free Source Version Control Facility from Microsoft for Small Teams

They have introduced Visual Studio Online with the capabilities of Team Foundation Server and additional cloud services, Where we can create our source code repository on the cloud and work in the same way as what we do now.

This online user plan includes everything from the hosted GIT repos, project tracking tools, and continuous integration tools. These user plans are described in
Visual Studio Online User Plans

The Visual Studio online basic is given free for up to five users. In this blog, I’ll elaborate on each and every step of how you can get your projects migrated to online TFS and work as a small team.

 ** You need Visual Studio 2013 Professional or Ultimate installation to use this feature.

Steps :
1. You must create a Live account, and verify the given email and phone number. Go to http://www.visualstudio.com/en-us/products/what-is-visual-studio-online-vs.aspx​ and logged in using your credentials.

 If all the validations and verification are completed, you must see your own name as below.

3. Then click on Create a free account now to create a Visual Studio online account.


4. Now you should be landed on this page.


5. Now type your project name, chose the version control system TFVC / Git, Process Control Template, and click on Create project. Read more on the SVC system supported by Microsoft Visual Studio : Use version control

6. Now you must see this page.


7. Now click on "Open with Visual Studio to connect". You might be asked for your credentials, please provide them. Then you will be connected to the online TFS account that you have just created.


8. Now you can add your solution to the online TFS.