VISA VCF plain text credit card statement into Dynamics AX with XSLT
Introduction
A good level of travel expense automation in Microsoft Dynamics AX 2012 R3 requires daily import of credit card files. The credit card number must be specified for the employee in the Human Resources module. The daily credit card statements with unbilled credit card transactions are picked up by the Travel and expense / Periodic / Credit cards / Credit card import from folder batch job. The data is stored in the TrvPBSMaindata table. Every time an employee enters a new expense report in the AX Enterprise Portal those unreconciled transactions appear on the screen. The employee enriches the transactions with missing data and necessary notes and takes them over into the expense report.
The three major credit card companies offer the following integration services:
- AMEX - American Express – gives a plain text KR 1025 file, currently being replaced by the GL 1025 format
- Master Card uses the Common Data Format 3.0 (CDF3), an XML file.
- VISA provides the Visa Commercial Format 4.0 (VCF4), a plain tab-separated text file
None of the formats above are supported in Dynamics AX out-of-the box. Nevertheless, the credit card import routine uses an AIF (Application Integration Framework) SOAP service and as any other service in AX it can be fed with an XSLT transformation to map the external file to the AX data structures.
This approach will work with an CDF3 XML file, but not with the plain text KR1025 and VCF4 files, and here is why:
- The AIF framework is built upon the WCF and .NET libraries, but
- NET does not support XSLT 2.0 specification with its unparsed-text() and tokenize() functions and this is not expected to change in the near future, and
- XSLT 1.0 transformations cannot take a plain text file, the input to an XSLT 1.0 transformation must be a well-formed XML. An attempt to convert a text file ends up in a “Line 1, column 1” error message, i.e. an XML header is expected by the XSLT processor.
- Ergo, we are doomed.
There are basically 2 solutions:
- Pre-process the text file and put it into a CDATA node of a well-formed XML document
- Perform the transformation in an external .NET library.
The latter approach is well illustrated in the blog importing-credit-card-statements-into-dynamics-ax-2012-using-aif-and-transformation-library. However, this implementation is far from being an industry grade solution. Firstly, this implementation if not official and it uses hardcoded text constants. Without the DLL source code, itit impossible to maintain. Secondly, for every change the DLL must be recompiled and re-deployed, and the AOS service must be restarted. While you are developing your interface you might need to restart the AOS service 100-200 times; you must be really masochistic to bring it upon you.
In contrary, a XSLT transformation may be replaced on-the-fly without even restarting the Inbound port service (to do that, use Tools / Application Integration Framework / Manage transforms, button Load). The XSLT language is a complex but powerful functional language, and it is a well-recognized industry standard. An XSLT transformation can be nicely debugged in Visual Studio. It can be adjusted by the system administrator, and it can accommodate to future changes.
Inspired by the AX2012 R3 “Advanced Bank Reconciliation”, which is able to import BAI2 and MT940 plain text files through an AIF inbound port, I developed a VISA VCF4 interface to 99% with XLST transformations only.
The remaining 1% is a generic, very simple .NET DLL that takes any text file and wraps it into a CDATA section. In the Bank Reconciliation this is done not in a transformation pipeline but in the X++ code, see \Classes\BankStatementFileImport\getImportFileContainer.
The transformation pipeline consists of 3 transformations:
The target format must conform to the AIF batch message schema: aside of the typical AX <Envelope> with the <MessageParts> the whole message has to be additionally embedded into a <Batch> node.
Processing of such a message can take place in an AX batch job. Beware: an attempt to call the function synchronously Travel and expense / Periodic / Credit cards / Credit card import from file from the AX client may lead to a processing error “Cannot logon to AX…”, because different credentials may be expected by the service. Either way, the above function is very useful in testing the transformation itself and the XML schema: you first call it interactively to check if the message reaches AX (System administration / Services and Application Integration Framework / Queue manager), then launch it in the batch mode to test the final stages when the message is being de-serialized and written to the TrvPBSMaindata table.
Visa Commercial Format 4.0 interface implementation
A VCF file consists of one or more transaction sets, one set per company (i.e. legal entity in AX). A transaction set consists of one or more blocks of transaction records, it is delimited by a header transaction “6” at the beginning and a trailer transaction “7” at the end.
One transaction set may have up to 13 blocks of transactions. A transaction block is a group of transactions that belong to the same record type. Like transaction sets, each block requires a header record “8” at the beginning and a trailer record “9” at the end.
Hence, the 6-7 and 8-9 lines always come in pairs, and this structure is very similar to an XML file with opening and closing tags:
6 Company Header Transaction Set
8 Header Block Transactions – Type “01”
Transaction 1
Transaction 2
Transaction n
9 Trailer Block Transactions – Type “01”
…
8 Header Block Transactions – Type “05”
Transaction 1
Transaction 2
Transaction n
9 Trailer Block Transactions – Type “05”
…
7 Company Trailer Transaction Set
The following record types are to be provided daily, e.g. transactions
- Car Rental Summary Type “02” (if available)
- Card Transaction Type “05” (if available)
- Line Item Detail Type “07” (if available)
- Line Item Summary Type “08”
- Lodging Summary Type “09”
- Period Type “11”
- Passenger Itinerary Type “14” (if available)
- Leg-Specific Information Type “15” (if available)
For the purpose of expense entry automation the Type 05 records are of most interest.
Potentially we may also import “02”s, “14”s and “15”s into an TrvEnhancedData record by sending a nested element such as
<TrvEnhancedData class="entity" type ="tns:AxdEntity_TrvEnhancedData_TrvEnhancedItineraryData">, but the TrvEnhancedItineraryData inherits from TrvEnhancedData, while AX programmers forgot to provide any of the \Classes\TrvTrvPBSMaindata_TrvEnhancedData_XXX container classes. Standard Dynamics AX simply cannot uptake the TrvEnhancedData descendant tables.
The first stage of conversion is provided by a puristic C# assembly AX2012_TransformAnyTextToXML.DLL. It writes the text into an XML file with a large CDATA section.
using System.Text;
using System.IO;
using System.Xml;
using Microsoft.Dynamics.IntegrationFramework.Transform;
namespace AX2012_TransformAnyTextToXML
{
public class TransformAnyTextToXML : ITransform
{
public void Transform(System.IO.Stream input, System.IO.Stream output, string config)
{
StreamReader sreader = new StreamReader(input);
XmlTextWriter xwriter = new XmlTextWriter(output, Encoding.UTF8);
xwriter.Formatting = Formatting.None;
xwriter.WriteStartDocument();
xwriter.WriteStartElement("root");
xwriter.WriteCData(sreader.ReadToEnd());
sreader.Close();
xwriter.WriteEndElement();
xwriter.Close();
}
}
}
Build and deploy it as described here: https://technet.microsoft.com/en-us/library/gg863930.aspx.
The second stage is an XSLT transformation VCF4TXT_to_XML.xslt that recursively breaks down the long text string into records by the “LF” character, and into fields by the “TAB” character. The result is a pre-digested XML file with enumerated fields. Headers and footers are records of their own, but I elevated their fields up to the <TransactionSet>, <TransactionBlock> level to facilitate cross-record lookups and XPath axis addressing.
The third stage is an XSLT transformation VCF4XML_to_AX.xslt that applies business logic and converts the resulting XML file to the Dynamics AX AIF message format. It makes one <Envelope> per <TransactionSet> i.e. company.
The mapping from a long 10 digits “Company Identification” code to the 4 char AX legal entity code is hard-coded into the XSLT transformation. In an environment with just one company or there only one company issues VISA cards you may omit his section: the <Company> is not a mandatory element.
Another added „sugar“ is a <MessageId> tag for idempotency. AX may not import the same file twice. This is enforced at later stages through the uniqness of the TrvPBSMaindata.CCTransUniqueID field, but also by the <MessageId> in the XML header. On an attempt to import a second file with the same <MessageId> AX is going to show an error message „Duplicate message. Message has already been processed. “
Logically the <MessageId> corresponds to the VCF download Sequence Number which is unique in every credit card statement file. However, AX expects a GUID in the <MessageId> element. In our case this GUID must be deterministic. For example, it must reproduceably return the same GUID {feb9aab5-8853-9501-0381-3a7d6075d3d1} for the same Sequence Number „02746“ in the VCF4 file. This logic is implemented in an inline C# script stringToGuid() through a SHA1 hash, courtesy of a great guy called Ben Gripka (see <stackoverflow.com/questions/2642141/how-to-create-deterministic-guids>).
The conversion tables from the ISO numeric codes for countries and currencies into the ISO symbolic codes are embedded directly into the VCF4XML_to_AX.xslt.
The mapping between merchant SIC codes <CatCode> such as 5411 “Retail-Grocery Stores” and expense categories is configurable in Dynamics AX (Travel and expense / Setup / Credit cards / Credit card category codes). The credit card type “VISA” is hardcoded in the XSLT.
Here is a sample of the resulting XML file, ready to be consumed by the AX service:
<?xml version="1.0" encoding="utf-8"?>
<Batch xmlns="schemas.microsoft.com/dynamics/2009/06/documents/Batch" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:tns="schemas.microsoft.com/dynamics/2008/01/documents/TrvPBSMaindata">
<Envelope xmlns="schemas.microsoft.com/dynamics/2011/01/documents/Message">
<Header>
<MessageId>{feb9aab5-8853-9501-0381-3a7d6075d3d1}</MessageId>
<Company>USMF</Company>
<Action>schemas.microsoft.com/dynamics/2008/01/services/TrvPBSMaindataService/create</Action>
</Header>
<Body>
<MessageParts>
<TrvPBSMaindata xmlns="schemas.microsoft.com/dynamics/2008/01/documents/TrvPBSMaindata">
<DocPurpose>Original</DocPurpose>
<TrvPBSMaindata class="entity">
<AmountCurr>257</AmountCurr>
<AmountLocal>176.96</AmountLocal>
<BusinessName>Airport Store</BusinessName>
<CardNumber>################</CardNumber>
<CardType>VISA</CardType>
<CatCode>5309</CatCode>
<CCTransUniqueId>###################604</CCTransUniqueId>
<Country>CHE</Country>
<ExchCode>CHF</ExchCode>
<Name>MARTHA S.</Name>
<Town>Lovely UK Countryside</Town>
<TransDate>2015-10-21</TransDate>
</TrvPBSMaindata>
<TrvPBSMaindata class="entity">
<AmountCurr>19.47</AmountCurr>
<AmountLocal>19.47</AmountLocal>
<BusinessName>Amazon UK Marketplace</BusinessName>
<CardNumber>################</CardNumber>
<CardType>VISA</CardType>
<CatCode>5999</CatCode>
<CCTransUniqueId>####################984</CCTransUniqueId>
<Country>LUX</Country>
<ExchCode>GBP</ExchCode>
<Name></Name>
<Town>Luxembourg</Town>
<TransDate>2015-10-19</TransDate>
</TrvPBSMaindata>
</TrvPBSMaindata>
</MessageParts>
</Body>
</Envelope>
</Batch>
Conslusion
A VISA credit card interface with virtually no customizations in Dynamics AX is feasible. The reference XSLT transformations are attached.
The situation with the support for the remaining credit card providers is still unsatisfactory. Please vote for my product suggestion on MSConnect.