Interfaces to Dynamics AX 2009

A while back I penned about our dismal interlude into the BizTalk to Dynamics Application Interface Framework. I am led to believe that the problem was not with the AIF but with Dynamics and that it has been resolved. We however did not have any slack time in our very ambitious timetable to wait for it so had to take a different route.

I spent countless hours talking to people and browsing the Internet trying to find a robust way of creating interfaces and found absolutely nothing worth reading. Here is the route that I took.

I created a new Database specifically for interfaces into Dynamics AX. This Database is housed on the same server as our Dynamics Database. Within this database we then created a number of tables that are populated by our other systems within the organization. Based on our install I had 2 distinct types of interfaces. Invoices for Accounts Payable and Journals for the General Ledger. Most of the fields for both are shared anyway. Each system that was being integrated was assigned a "Source" code so that we would never lose track of where the data originated from. In a more complex environment I would suggest that you create a schema within the database for each source system. This will help with control and security.

Within Dynamics AX (DAX) we then created a new setup table and created a form called "External Interface Import Parameters". This table/form held the following information:

• Import Type – A short identifier of the interface
  
• Name – A full description of the external interface
  
• External Server Name – The name of the server that housed the Interface Database being called.
  
• Database Name – The name of the interface database mentioned above.
  
• Journal Name – We set up a different journal name for each interface so that they could easily be identified when looking at the transactions in an account.
  
• One Voucher Number Only – A bit switch. If set on this will create one voucher number for the entire interface run. If set off it will generate a new voucher number when it reaches a point of balance.
  
• Stored Procedure – This is the name of the stored procedure within the database to call in order to get the data.
  
• Write Back Table Name – The table that will be used to stamp back information once the interface has successfully run.
  
• ODBC Source Name – We had to use ODBC connections due to the way the systems run from the client or from the batch server. (Some history here but it was necessary).
  
• Batch Class Name – If this job was going to be called by the Batch server this is the name of the job to call.
  
• Auto Post – If set on it will load and auto post the interface journal.
  

This information is then used repeatedly throughout the interfaces. Here are the steps that all interfaces go through:

1. An interface call is made either from a menu item by an operator or from the batch manager in DAX.
   
2. Based on the Import Parameters mentioned above the job will then call a Stored Procedure on the Interface database and will pass it the Company code as the only parameter. This is often referred to as the DataAreaId in DAX.
   
3. The Stored Procedure fires on the Server and passes back to the job all the records for the interface.
   
4. DAX then creates the Journal based on the Journal Name and will either auto post or leave it as unposted.
   
5. When complete it will the write back to the table the Journal Number and Date/Time Completed.
   
6. The batch job will keep firing for the company until the stored procedure returns no records back.
   

So what do the Stored Procedures do?

All records that are new have a TransactionStatus of "NEW". Those in the process of being posted are stamped "WIP" and those completed are stamped "FIN". The stored procedures finds records that are "NEW" and then selects the Top 1 record summarized by your extraction criteria into a table variable. As an example our POS Journals are summarized by Company, Trading Date, Currency and ExtractID from the LOB System. This table variable is then used to join back to the table to return the result set. As part of this procedure we also stamp the records as "WIP" with the date/time it was sent to Dynamics.

Each interface has been specifically written to meet the LOB criteria. I am busy working on a more generic system that will completely future proof the business. Once the framework is in place no additional development will be required. The only work required will be set up a record in the Import Parameters Table, set up a Journal Name, Number Sequence and just populate the interface tables.

I would be very interested to hear from other parties how they tackled this.

- Paul Steynberg