09.12.2015, 04:29
|
#1 |
|
Участник
|
dynamics-coe: VISA VCF plain text credit card statement into Dynamics AX with XSLT
Источник: http://blogs.msdn.com/b/dynamics-coe...with-xslt.aspx
============== 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:
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:
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 with the the whole message has to be additionally embedded into a 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 , 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/.../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 , 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 per 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 is not a mandatory element. Another added „sugar“ is a <strong> 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 in the XML header. On an attempt to import a second file with the same AX is going to show an error message „Duplicate message. Message has already been processed.“ Logically the corresponds to the VCF download Sequence Number which is unique in every credit card statement file. However, AX expects a GUID in the 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 http://stackoverflow.com/questions/2...ministic-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 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: {feb9aab5-8853-9501-0381-3a7d6075d3d1} USMF http://schemas.microsoft.com/dynamic...Service/create Original 257 176.96 Airport Store ################ VISA 5309 ###################604 CHE CHF MARTHA S. Lovely UK Countryside 2015-10-21 19.47 19.47 Amazon UK Marketplace ################ VISA 5999 ####################984 LUX GBP Luxembourg 2015-10-19 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 remaining credit card providers is still unsatisfactory. Please vote for my product suggestion on MSConnect. Источник: http://blogs.msdn.com/b/dynamics-coe...with-xslt.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
|
10.12.2015, 12:30
|
#2 |
|
Модератор
|
Цитата:
An XSLT transformation can be nicely debugged in Visual Studio
Так-то все конечно очень круто, но когда пришлось реализовывать импорт банковской выписки из альтернативного формата, гораздо быстрее и проще в отладке оказался вариант с олдскульным парсером на X++ (RunBase), который разбирает файл и дергает стандартный сервис выписки. То же с отдельной сборкой "заворачивающей" фодержимое файла в CDATA и C# функциями в XSLT А так - повторюсь, все круто и Microsoft-style. Спасибо. Надеюсь кому-то пригодится
__________________
-ТСЯ или -ТЬСЯ ? |
|
|
|
| За это сообщение автора поблагодарили: Logger (1). | |
| Теги |
| mt940, swift, банк, выписка |
|
|
|
Линейный вид
