XSLT transformation in Dynamics 365 for operation

 

As we all know we have used XSLT transformation in AX 2012 for AIF integration purpose. But in Dynamics 365 for operation AIF feature got deprecated and we should use data management framework(DMF) for integration scenarios.

Let’s see how we can apply XSLT transformation in DMF. During import scenarios, the other system generates a file format which we cannot directly use it as source data format in DMF. So, we need to convert it into a XML file format using XSLT which DMF can understand as a valid source data format.

For example, the third-party system generates a FLAT text file for purchase order which we need to import into AX 7 using data management framework.

For example, the purchase order file which we need to import will look like this

image2

Let me explain about the file first

The line start with no 1 means it header 2 means line 5 means end of the order

In PO header 110030000020160503

Vendor Account number = 100000100320160503

Date                                      = 100000100320160503

In PO line 2100000000000000    1000011000  000000100ea000032500

Item number                    = 2100000000000000    1         11        000000100ea000032500

Site                                        = 2100000000000000    2         21        000000100ea000032500

Warehouse                        = 2100000000000000    2         21        000000100ea000032500

Qty                                        = 2100000000000000    2         21        000000100ea000032500

Unit                                      = 2100000000000000    2         21        000000100ea000032500

Unit price                            = 2100000000000000    2         21        000000100ea000032500     

 

First, we need to create XSLT file. To do this go visual studio -> New -> File ->XSLT file save the file in local folder.

We can add XSLT files as resource in AX 7. to do that in AX 7 project add new item of type resource

While it asks you to select a file, select the XSLT file which we are saved in our local folder.

I am not going to explain XSLT codding syntax logic here.

 

<xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform&#8221; version=”2.0″>

<xsl:output method=”xml” omit-xml-declaration=”no” version=”2.0″ encoding=”utf-8″/>

<!– This is the entry point to the transform –>

<xsl:template match =”/” >

<xsl:element name=”Document”>

<xsl:variable name=”TransDate”/>

<!– Select all the text you need to PreProcess and send it to the PreProcessor

The PreProcessor returns the value in ‘txtToProcess’ –>

<xsl:variable name=”txtToPreProcess” select=”/Batch”/>

 

<xsl:call-template name =”inputTxtPreprocessor”>

<xsl:with-param name =”inputTxt” select =”$txtToPreProcess”/>

<xsl:with-param name=”delimiter” select=”‘ ‘”/>

</xsl:call-template>

</xsl:element>

</xsl:template>

 

<!– Template to create journal Template–>

<xsl:template name=”Header”>

<xsl:param name =”headerTxt”/>

<xsl:param name =”txtRem”/>

<xsl:choose>

<xsl:when test=”substring($headerTxt,1,1) != ‘5’ and string-length($headerTxt) > 0″>

<xsl:if test=”substring($headerTxt,1,1) = ‘1’”>

<xsl:call-template name=”PurchaseOrderHeader” >

<xsl:with-param name=”header”  select=”$headerTxt”/>

<xsl:with-param name =”txtRem” select=”$txtRem”/>

</xsl:call-template>

</xsl:if>

</xsl:when>

<xsl:otherwise>

<xsl:call-template name=”inputTxtPreprocessor”>

<xsl:with-param name=”inputTxt” select=”$txtRem”/>

<xsl:with-param name=”delimiter” select=”‘ ‘”/>

</xsl:call-template>

</xsl:otherwise>

</xsl:choose>

</xsl:template>

 

 

<!–Template to create header–>

<xsl:template name=”PurchaseOrderHeader”>

<xsl:param name =”header”/>

<xsl:param name =”txtRem”/>

 

<PurchPurchaseOrderHeaderEntity>

<PURCHASEORDERNUMBER/>

<CURRENCYCODE>USD</CURRENCYCODE>

<xsl:element name = “ORDERVENDORACCOUNTNUMBER”>

<xsl:value-of select=”substring-after(substring($header,2,9), substring-before(substring($header,2,9), substring(translate(substring($header,2,9), ‘0’, ”), 1, 1)))”/>

</xsl:element>

 

<xsl:call-template name=”formatDate”>

<xsl:with-param name=”datestr” select=”substring($header,11,8)”/>

</xsl:call-template>

 

<xsl:if  test=”substring($txtRem, 1, 1) =  ‘2’”>

<xsl:call-template name=”lineTxtPreprocessor”>

<xsl:with-param name=”lineTxt” select=”$txtRem”/>

<xsl:with-param name=”delimiter” select=”(‘ ‘)”/>

<xsl:with-param name=”lineNo” select=”number(‘1’)”/>

</xsl:call-template>

</xsl:if>

</PurchPurchaseOrderHeaderEntity>

 

<xsl:variable name=”txtUnProcessed”>

<xsl:value-of select=”substring-after(substring-after($txtRem,concat(‘ ‘, ‘5’)), ‘ ‘) “/>

</xsl:variable>

<xsl:if  test=”string-length($txtUnProcessed) > 0″>

<xsl:call-template name=”inputTxtPreprocessor”>

<xsl:with-param name=”inputTxt” select=”$txtUnProcessed”/>

<xsl:with-param name=”delimiter” select=”(‘ ‘)”/>

</xsl:call-template>

</xsl:if>

</xsl:template>

 

<!–Template to craete journal Lines–>

<xsl:template name=”PurchaseLine”>

<xsl:param name =”lineTxt”/>

<xsl:param name =”lineTxtRem”/>

<xsl:param name =”linenum”/>

<xsl:element name=”PurchPurchaseOrderLineEntity”>

<xsl:variable name=”txt” select=”$lineTxt”/>

 

<LINENUMBER>

<xsl:value-of select=” $linenum “/>

</LINENUMBER>

<ITEMNUMBER>

<xsl:value-of select=” substring-after(substring($txt,2, 15), substring-before(substring($txt,2, 15), substring(translate(substring($txt,2, 15), ‘0’, ”), 1, 1))) “/>

</ITEMNUMBER>

<RECEIVINGSITEID>

<xsl:value-of select=” normalize-space(substring($txt,21,10))”/>

</RECEIVINGSITEID>

 

<RECEIVINGWAREHOUSEID>

<xsl:value-of select=” normalize-space(substring($txt,31,10))”/>

</RECEIVINGWAREHOUSEID>

 

<PURCHASEPRICEQUANTITY>

<xsl:value-of select=”substring-after(substring($txt,41,9), substring-before(substring($txt,41,9), substring(translate(substring($txt,41,9), ‘0’, ”), 1, 1)))”/>

</PURCHASEPRICEQUANTITY>

 

<PURCHASEUNITSYMBOL>

<xsl:value-of select=” substring($txt,50,2) “/>

</PURCHASEUNITSYMBOL>

<xsl:variable name=”amountWithZeros” select=”substring($txt,52, 9)”/>

<xsl:variable name=”amount” select=”substring-after($amountWithZeros, substring-before($amountWithZeros, substring(translate($amountWithZeros, ‘0’, ”), 1, 1)))”/>

<xsl:variable name=”amountReal” select=”substring($amount, 1, string-length($amount)-2)”/>

<xsl:variable name=”Transamount” select=”concat(substring($amount, 1, string-length($amount)-2) ,’.’ ,substring-after($amount, $amountReal))”/>

<PURCHASEPRICE>

<xsl:value-of select=” $Transamount “/>

</PURCHASEPRICE>

</xsl:element>

<xsl:if test=”substring($lineTxtRem, 1, 1) =  ‘2’”>

<xsl:call-template name=”lineTxtPreprocessor”>

<xsl:with-param name=”lineTxt” select=”$lineTxtRem”/>

<xsl:with-param name=”delimiter” select=”(‘ ‘)”/>

<xsl:with-param name=”lineNo” select=”$linenum + 1″/>

</xsl:call-template>

</xsl:if>

</xsl:template>

 

<xsl:template name=”inputTxtPreprocessor”>

<xsl:param name=”inputTxt”/>

<xsl:param name=”delimiter”/>

<xsl:choose>

<xsl:when test=”contains($inputTxt, $delimiter)”>

<xsl:call-template name=”Header” >

<xsl:with-param name=”headerTxt”  select=”substring-before($inputTxt,$delimiter)”/>

<xsl:with-param name=”txtRem”  select=”substring-after($inputTxt,$delimiter)”/>

</xsl:call-template>

</xsl:when>

</xsl:choose>

</xsl:template>

 

 

<xsl:template name=”lineTxtPreprocessor”>

<xsl:param name=”lineTxt”/>

<xsl:param name=”delimiter”/>

<xsl:param name=”lineNo”/>

<xsl:choose>

<xsl:when test=”contains($lineTxt, $delimiter)”>

<xsl:call-template name=”PurchaseLine” >

<xsl:with-param name=”lineTxt”  select=”substring-before($lineTxt,$delimiter)”/>

<xsl:with-param name =”lineTxtRem” select=”substring-after($lineTxt,$delimiter)”/>

<xsl:with-param name=”linenum” select=”$lineNo”/>

</xsl:call-template>

</xsl:when>

</xsl:choose>

</xsl:template>

 

<xsl:template name=”formatDate”>

<xsl:param name=”datestr”/>

<xsl:variable name=”month”>

<xsl:value-of select=”substring($datestr,5,2)” />

</xsl:variable>

<xsl:variable name=”day”>

<xsl:value-of select=”substring($datestr,7,2)” />

</xsl:variable>

<xsl:variable name=”year”>

<xsl:value-of select=”substring($datestr,1,4)” />

</xsl:variable>

<xsl:element name=”REQUESTEDDELIVERYDATE”>

<xsl:value-of select=”concat($year, ‘/’, $month, ‘/’, $day) “/>

</xsl:element>

</xsl:template>

</xsl:stylesheet>

 

 

To debug your XSLT file, open the text file and add <batch> tag like shown in below

image5

Open the XSLT file in Visual studio place the break point. Go to property window of the XSLT file in input file path give the text file .

image3

Then select the Start with debug to debug the XSLT file from VS. At the end it will create a Xml file . in the output location, which we mentioned in the property.

image3

Now we see how we can apply this XSLT transformation in DMF. Create an Import data project. It will ask you to upload document, use the output XML document which is generated in the previous step for field mapping. After all mapping are done go to the Transformation tab from view map and upload the XSLT file and click Apply button

image6

image7

From data management work space go to your Data project and click Run project.

image8

Upload the text file which need to be import into AX 7

image9

image10

Then click import button to create the PO orders .

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s