DEV Community: Rohith_Veerapalli

Convert MT940 to xml in x++

Rohith_Veerapalli — Tue, 04 Aug 2026 11:09:40 +0000

/// <summary>
/// Process class to convert MT940 to XML
/// </summary>
class ProcessMT940BankFiles
{
    /// <summary>
    /// Method to get the request and return response based on process
    /// </summary>
    /// <param name = "_xmlContent">_xmlContent</param>
    /// <returns>xmlOutput</returns>
    public static str getIncomingFileContent(Notes _xmlContent)
    {
        MT940Parameters    mt940Params;

        select firstonly * from mt940Params;


        str xmlOutput = ProcessMT940BankFiles::transformFile(_xmlContent, mt940Params.ESIncomingXSLTFormat);
        xmlOutput = ProcessMT940BankFiles::transformFile(xmlOutput, mt940Params.ESXML2Reconciliation);
        xmlOutput = ProcessMT940BankFiles::transformFile(xmlOutput, mt940Params.ESRecon2Composite);

        return xmlOutput;

    }

    ///// <summary>
    ///// Method to process the MT940 files and translate them accordingly
    ///// </summary>
    ///// <param name = "_inputFileReader">_inputFileReader</param>
    ///// <param name = "_xslReader">_xslReader</param>
    ///// <returns>fileContent</returns>
    static public str transformFile(Notes _inputFileReader, Notes _xslReader)
    {

        System.Xml.XmlUrlResolver               resolver;
        System.Xml.Xsl.XsltSettings             managedSettings;
        System.Xml.Xsl.XslCompiledTransform     managedTransform;

        managedSettings = new System.Xml.Xsl.XsltSettings(true, true);
        resolver = new System.Xml.XmlUrlResolver();
        str fileContent;
        managedTransform = new System.Xml.Xsl.XslCompiledTransform();
        System.Xml.XmlReaderSettings settings = new System.Xml.XmlReaderSettings();
        settings.IgnoreWhitespace = true;
        settings.IgnoreComments = true;
        System.IO.Stream st = new System.IO.MemoryStream(System.Text.Encoding::UTF8.GetBytes(_xslReader));

        // Initialize reader for the xslt
        using(System.IO.StringReader xsltStringReader = new System.IO.StringReader(_xslReader))
        {
            using(System.Xml.XmlReader xsltXmlReader = System.Xml.XmlReader::Create(st,settings))
            {
                // Load xslt
                managedTransform.Load(xsltXmlReader, managedSettings, resolver);
                using(System.IO.StringReader xmlreader = new System.IO.StringReader(_inputFileReader))
                {
                    using(System.Xml.XmlReader inputFileXmlReader = System.Xml.XmlReader::Create(xmlreader))
                    {
                        using (var sw = new System.IO.StringWriter())
                        {
                            using (var xwo = System.Xml.XmlWriter::Create(sw, managedTransform.OutputSettings))
                            {
                                // Transform
                                managedTransform.Transform(inputFileXmlReader, xwo);
                                xwo.Flush();
                                fileContent = sw.ToString();
                                fileContent = strReplace(fileContent,"encoding=\"utf-16\"", '');
                            }
                        }
                    }
                }
            }
        }
        return fileContent;
    }

}

here is the mt940 parameters table data

Field - ESINCOMINGXSLTFORMAT

Value-

/xsl:call-template /xsl:variable  /xsl:call-template /xsl:template  /xsl:variable /xsl:call-template /xsl:if /xsl:template  /xsl:variable xsl:choose  /xsl:call-template /xsl:when  /xsl:value-of /xsl:attribute /xsl:call-template /xsl:when  /xsl:value-of /xsl:attribute /xsl:value-of /xsl:attribute /xsl:call-template /xsl:when  /xsl:value-of /xsl:attribute /xsl:value-of /xsl:attribute /xsl:call-template /xsl:when  /xsl:value-of /xsl:attribute /xsl:value-of /xsl:attribute /xsl:call-template /xsl:when  /xsl:value-of /xsl:attribute /xsl:value-of /xsl:attribute /xsl:call-template /xsl:when /xsl:choose xsl:choose  /xsl:call-template /xsl:if /xsl:when  /xsl:call-template /xsl:if /xsl:when xsl:otherwise  /xsl:call-template /xsl:if /xsl:otherwise /xsl:choose /xsl:template /xsl:template xsl:choose /xsl:variable /xsl:variable /xsl:variable /xsl:when /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:template /xsl:template /xsl:call-template /xsl:template xsl:choose /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:variable /xsl:variable /xsl:variable /xsl:variable /xsl:variable /xsl:variable /xsl:call-template /xsl:variable /xsl:variable /xsl:variable /xsl:variable /xsl:variable /xsl:call-template /xsl:if /xsl:call-template /xsl:if /xsl:call-template /xsl:if /xsl:call-template /xsl:if /xsl:call-template /xsl:if /xsl:call-template /xsl:if /xsl:if /xsl:if /xsl:template xsl:choose /xsl:variable /xsl:variable xsl:choose /xsl:variable /xsl:call-template /xsl:when /xsl:variable /xsl:call-template /xsl:when /xsl:choose /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:template xsl:choose /xsl:when  /xsl:when /xsl:when /xsl:when /xsl:choose /xsl:call-template /xsl:if /xsl:template   /xsl:call-template /xsl:variable /xsl:call-template /xsl:variable  /xsl:call-template /xsl:variable  /xsl:variable /xsl:template  xsl:choose /xsl:call-template /xsl:call-template /xsl:when /xsl:otherwise /xsl:choose /xsl:template  xsl:choose /xsl:call-template /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:template /xsl:call-template /xsl:template  xsl:choose /xsl:call-template /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:template <![CDATA[ public string isIdentificationCodeSupported(string input) { string isIdentificationCodeSupported = "No"; bool answer = input.Contains("?00") || input.Contains("?31") || input.Contains("?32") || input.Contains("?33"); if (answer == true) { isIdentificationCodeSupported = "Yes"; } return isIdentificationCodeSupported; } public string lineExtensionSectionCount(string input) { int count; count = input.Split('?').Length - 1; return count.ToString(); } public string splitNumberAndAlpha(string originalLine, string seperator) { //split the string by number and alpha, return them sepearted by seperator, and end with seperator string result = string.Empty; int i = 1; if (originalLine == null || originalLine.Length == 0) { return ""; } bool isLetter = Char.IsLetter(originalLine[0]); foreach (char c in originalLine) { bool isLetterCurrent; isLetterCurrent = Char.IsLetter(c); if (isLetterCurrent != isLetter) { result += (i++) + seperator; isLetter = isLetterCurrent; } result += c; } result += (i++) + seperator; return result; } public string splitMT940TransactionLine(string originalLine) { //split the string by number and alpha, return them sepearted by seperator, and end with seperator string result = string.Empty; int i = 1; int count = 0; bool isSkip = false; string seperator = ";"; if (originalLine == null || originalLine.Length == 0) { return ""; } bool isLetter = Char.IsLetter(originalLine[0]); foreach (char c in originalLine) { isSkip = false; if (i <= 3) { bool isLetterCurrent; isLetterCurrent = Char.IsLetter(c); if (isLetterCurrent != isLetter) { result += (i++) + seperator; isLetter = isLetterCurrent; } } else if (i <= 4) { if (count == 0) { count = 1; } count++; if (count == 5) { result += (i++) + seperator; count = 0; } } else if (i<=5) { if (c == '/') { isSkip = true; count++; } if (count == 2) { result += (i++) + seperator; count = 0; } } else if (i <= 6) { if (c == '\n') { isSkip = true; result += (i++) + seperator; } } else if (i <= 8) { if (c == '/') { count++; } if (count == 4) { result += (i++) + seperator; count = 0; } } if (!isSkip) { result += c; } } result += (i++) + seperator; return result; } public string addFileEnd(string lines) { int lastDataLine = lines.LastIndexOf("\n:"); if (lastDataLine > 0) { int lastLine = lines.IndexOf("\n", lastDataLine + 1); if (lastLine > 0) { lines = lines.Substring(0, lastLine + 1) + ":" + lines.Substring(lastLine + 1); } else { lines = lines + "\n:"; } } return lines; } ]]> /msxsl:script <![CDATA[ public string splitBySpeartors(string originalLine, string seperator, string originalSeperator1, string originalSeperator2 = "", string originalSeperator3 = "") { string line = originalLine; int i = 1; if (originalSeperator1 != null && originalSeperator1.Length > 0) { line = line.Replace(originalSeperator1, seperator); } if (originalSeperator2 != null && originalSeperator2.Length > 0) { line = line.Replace(originalSeperator2, seperator); } if (originalSeperator3 != null && originalSeperator3.Length > 0) { line = line.Replace(originalSeperator3, seperator); } if (seperator.Length == 0) { return line; } string result = string.Empty; foreach (char c in line) { if (c == seperator[0]) { result += i++; } result += c; } result += i++; result += seperator; return result; } ]]> /msxsl:script /xsl:stylesheet

Field

ESXML2RECONCILIATION-

Value -

http://tempuri.org/BankStmtService/create xsl:apply-templates/ /xsl:template  MT940 1  /xsl:template  /xsl:variable /xsl:variable /xsl:value-of Reconciliation  /xsl:with-param /xsl:with-param /xsl:call-template /xsl:with-param /xsl:with-param /xsl:call-template  /xsl:with-param /xsl:call-template /xsl:for-each /xsl:for-each /xsl:template  /xsl:param /xsl:param /xsl:with-param /xsl:call-template /xsl:call-template /xsl:template /xsl:param /xsl:call-template /xsl:with-param /xsl:call-template /xsl:call-template xsl:choose /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:variable /xsl:with-param /xsl:call-template /xsl:with-param /xsl:call-template /xsl:if /xsl:for-each /xsl:template  /xsl:template  xsl:choose /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:template xsl:choose /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:template xsl:choose /xsl:when /xsl:when xsl:otherwise /xsl:otherwise /xsl:choose /xsl:template <![CDATA[ public string convertToDateTime(string strDate, string strTime) { if (strDate == "") return "1900-01-01T00:00:00Z"; if (strTime == "") strTime = "0000"; string stringToConvert = strDate + strTime; DateTime dateTime = DateTime.ParseExact(stringToConvert, "yyMMddHHmm", System.Globalization.DateTimeFormatInfo.InvariantInfo); return dateTime.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ"); } ]]> /msxsl:script /xsl:stylesheet

Field - ESRECON2COMPOSITE

Value -

Fixing Unbalanced TTS Error in D365FO Excel Upload

Rohith_Veerapalli — Sat, 27 Dec 2025 17:14:16 +0000

When developing Excel upload in custom script functionality in Dynamics 365 Finance & Operations (D365FO) using X++, we commonly encounter the unbalanced X++ TTSBEGIN/TTSCOMMIT error. This guide shows you exactly how to fix it.

The Error You'll Encounter

Understanding How Custom Scripts Process Transactions

Lets understand how custom script is processing, when we see the class AppConsistencyCustomScriptStateChange,
when we do Run test the operation runs under ttsbegin and ttsabort, when we do Run operation, it process under ttsbegin, ttscommit.
the process is already in transaction when we upload the package

Why This Error Occurs

When you read an Excel file using classes like OfficeOpenXml.ExcelPackage or similar COM objects, these operations internally manage transactions. If your code already has an active TTS level, you'll get an unbalanced transaction error.

The Solution: Abort Transactions Before Reading Excel

The fix is simple but crucial: abort all existing transactions before reading the Excel file, then restore the TTS level afterward.

while (appl.ttsLevel() > 0)
{
    ttsAbort;
}

Since we are aborting the transaction, we need to add ttsBegin transaction begin in the end of the process to not get Unbalanced transaction errors

Complete Working Code Example

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;

class ExcelReaderForCustomScript 
{

    public  CustAccount      accountNum;
    public CCMNum_BR         ccmNum ;
    /// <summary>
    /// Class entry point. The system will call this method when a designated menu
    /// is selected or when execution starts and this class is set as the startup class.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        ExcelReaderForCustomScript curentClass = new ExcelReaderForCustomScript();
        curentClass.importData();

        Info('script executed');
    }

    void importData()
    {
        System.IO.Stream                        stream;
        DialogGroup                             dlgUploadGroup;
        FileUploadBuild                         fileUploadBuild;
        FormBuildControl                        formBuildControl;
        list List = new List(types::String);

        while (appl.ttsLevel() > 0)
        {
            ttsAbort;
        }

        Dialog           dialog =    new Dialog("Upload file");

        dlgUploadGroup          = dialog.addGroup("File");
        formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx');

        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
            CustTable   custTable;
            if(fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (ExcelPackage Package = new ExcelPackage(stream))
                {
                    int rowCount, i,columncount;

                    Package.Load(stream);
                    ExcelWorksheet worksheet           = package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange range     = worksheet.Cells;

                    rowCount           = (worksheet.Dimension.End.Row) - (worksheet.Dimension.Start.Row)  + 1;
                    columncount        = (worksheet.Dimension.End.Column);

                    for (i = 2; i<= rowCount; i++)
                    {
                        accountNum = (range.get_Item(i,1).Value);
                        ccmNum   = (range.get_Item(i,2).Value);
                        List.addEnd(accountNum);

                        ttsbegin;
                        update_recordset custTable setting CCMNum_BR = ccmNum
                            where custTable.AccountNum == accountNum;
                        ttscommit;

                    }
                    info(strFmt('AccountNum %1',list));
                    **ttsbegin;**
                }
            }
        }
    }

}

Critical Implementation Note

Keep in mind, since we are aborting transaction, Run Test process will write the data to system. we dont have to process Run operation again.

Computed columns in D365FO – Using Correlated Subqueries with Real SQL

Rohith_Veerapalli — Thu, 18 Dec 2025 16:30:25 +0000

When working in Dynamics 365 Finance & Operations, we often need to display or filter data that doesn’t physically exist in a table. Adding a new field and populating it with batch jobs or event handlers is sometimes overkill — and that’s where computed columns shine.

In this post, I’ll explain computed columns in D365FO, when to use them, and a real-world SQL subquery pattern .

Computed Columns in D365FO

Computed Column in D365FO x++?

A computed column is a virtual field whose value is calculated at runtime using SQL, not stored in the database.

Key points:

No physical column is created in SQL

Logic is executed at query time

Implemented using an X++ static method that returns an SQL expression

Commonly used in views, data entities, and computed fields

The Pattern Most People Use (and Its Limitation)

Most examples online show simple patterns like:

Concatenating fields
Simple CASE WHEN
Basic SUM or COUNT

But in real projects, we often need context-aware values, based on:

Date ranges
Related transactions
Conditional joins

That’s where subqueries inside computed columns become powerful.

A Real-World Example: Fetching a Request ID Based on Transaction Date

Let’s look at a computed column that returns a Leave Request ID for a worker, based on whether a transaction date falls within a leave period.

public static str requestId()
{
    str sqlQuery;

    sqlQuery = strFmt(
        "(SELECT TOP 1 LR.REQUESTID " +
        " FROM LeaveRequest LR " +
        " JOIN LeaveOfAbsenceRequestDetail LRD " +
        "   ON LRD.REQUEST = LR.RECID " +
        "  AND LRD.DATAAREAID = LR.DATAAREAID " +
        " WHERE LR.WORKER = T1.WORKER " +
        "   AND LRD.STARTDATE <= T1.TRANSACTIONDATE " +
        "   AND LRD.ENDDATE   >= T1.TRANSACTIONDATE " +
        "   AND LR.DATAAREAID = T1.DATAAREAID " +
        "   AND LR.PARTITION  = T1.PARTITION)"
    );

    return sqlQuery;
}

Understanding What’s Happening

1. Why T1?

T1 refers to the primary datasource of the view or entity where this computed column is defined.

For example:

T1.WORKER

T1.TRANSACTIONDATE

These values come from the current record being queried.

2. Why SELECT TOP 1?

Computed columns must return a single scalar value.
If multiple records are possible, SQL must be constrained to one result.

You can also:

Add ORDER BY
Add additional filters
Use MAX() or MIN() if appropriate

3. Why This Is Powerful

This pattern allows you to:

Dynamically resolve relationships
Use date-effective logic
Avoid storing derived data

Use the field in:

Filters
Sorting
Data entities
Reports

All without writing X++ loops.

More examples

Lets say we have multiple approvers for single invoice, we can concatenate multiple pending approvers like this

public static str pendingApprovers()
{
    str sqlQuery;


    sqlQuery = strFmt( 
                     "STUFF((" +
                    " SELECT ', ' + USERINFO.Name" +
" FROM WORKFLOWWORKITEMTABLE WWI" +
" JOIN DIRPERSONUSER PU ON PU.USER_ = WWI.USERID" +
" JOIN HCMWORKER HW ON HW.PERSON = PU.PERSONPARTY" +
" join USERINFO ON USERINFO.ID = WWI.USERID"+
" WHERE WWI.PURCHINVOICE = T1.RECID" +
" AND WWI.STATUS  = 0"+
" AND WWI.PARTITION = T1.PARTITION" +
" FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')" +
", 1, 2, '')"
        );

        return sqlQuery;


}

Here T1 is the primary data source table

This is how the view design will be

and final result

Keep experimenting 🚀