Matching own payments in Modern bank reconciliation

Enter the house of pain.001

Matching own payments in Modern bank reconciliation

Trying to reconcile all-standard outbound payments ISO20022 pain.001 CT produced by Dynamics 365 for Finance with a Camt.053 bank statement imported by the Microsoft’s own Advanced bank reconciliation (ABR) Camt.053 format is futile, probably because of the patchwork development of the AP and Bank modules.

The Reconciliation matching rule in the Modern bank reconciliation sub-module will try to look for the Document number in the bank transaction BankAccountTrans, but there won’t be any, while the payment reference (see below) may not be sent back by the bank with the statement.

Credit transfers

For credit transfers, a good way to recognize self-initiated payments may be by the <EndToEndId> element generated by the Electronic Reporting (ER) configuration for ISO20022 Credit Transfer. Dynamics builds this identifier from the Accounts Payable payment journal line by concatenating two or three components separated by dashes:

VoucherNumber-RecIdofLedgerJournalTrans[-optional counter]

The counter is added when multiple attempts are made to generate the same pain.001 file. Example:

				
					<NtryDtls>
  <TxDtls>
    <Refs>
      <AcctSvcrRef>BLA-BLA-BLA</AcctSvcrRef>
      <EndToEndId>APP001547-5637312576-2</EndToEndId>
    </Refs>
  </TxDtls>
</NtryDtls>

Here, the general ledger voucher APP001547 forms part of the <EndToEndId>. In the Microsoft ABR Camt.053 format, this element is written into the Description field of the imported bank statement line. The reconciliation matching rule (Cash and bank management > Setup > Advanced bank reconciliation setup > Reconciliation matching rules) can then compare the voucher number against the Description.

However, the Contains operator will not help, because the Description is longer than the voucher: the voucher does not contain, it is contained. The In operator (Voucher is contained in the Description) is not available here, and the less “<” operator if not going to help, either. 

To carve out the voucher number only and let the exact match “=” operator to work, you may modify either the ABR Camt.053 format (where the XML file is interpreted) or the ABR Bank statement mapping to destination (where the parsed data is written to the bank statement entity).

To adjust the format, open Organization administration > Electronic reporting > Configurations, find ABR Camt.053 format under Advanced bank reconciliation statement model, press +Create configuration to derive a new from the ABR Camt.053 format. In the Designer, open Map format to model and launch its Designer.

The DATA SOURCES to the left follow the XML structure:

				
					BkToCstmrStmt
 └─ Stmt
     └─ Ntry
         └─ NtryDtls
             └─ TxDtls
                 └─ Refs
                     └─ EndToEndId

On the right side, …NtryDtls/TxDtls/Refs/EndToEndId maps to Statement/StatementLine/EndToEndId.

By default, the mapping expression is:

IF(AND(@.Refs.IsMatched, @.Refs.Data.EndToEndId.IsMatched), @.Refs.Data.EndToEndId.Data.Str, "")

If your voucher numbers are fixed at nine characters, replace it with:

IF(AND(@.Refs.IsMatched, @.Refs.Data.EndToEndId.IsMatched), LEFT(@.Refs.Data.EndToEndId.Data.Str, 9), "")

Save and close, mark the version as Completed, and assign this format under Cash and bank management > Setup > Advanced bank reconciliation setup > Bank statement format. From that point, the <EndToEndId> will travel through the following path:

Vendor payment journal line Voucher → ISO20022 Credit Transfer → Bank → [ISO20022 pain.002 confirmation] → ABR Camt.053 format → ABR Bank statement mapping to destination → Bank statement line Description → Bank reconciliation worksheet.

Direct debits

For direct debits, where funds are collected from customer accounts via SEPA mandates, the flow differs. There is a <Ref> element which represents the Structured Creditor Reference, and it may be preferable over <EndToEndId>. In Dynamics 365, the Creditor reference corresponds to the Payment ID on the invoice.

Customer payment journal line Payment Id → ISO20022 Direct debit → Bank → ISO20022 Camt.054 or ABR Camt.053 format → Mapping to destination → Bank statement line Creditor reference information → Bank reconciliation worksheet.

The structure in the pain.008 file appears as: 

				
					BkToCstmrStmt
 └─ Stmt
     └─ Ntry
         └─ NtryDtls
             └─ TxDtls
                 └─ RmtInf
                     └─ Strd
                         └─ CdtrRefInf
                             └─ Ref

				
			
				
					<RmtInf>
  <Strd>
    <CdtrRefInf>
      <Tp>
        <CdOrPrtry>
          <Cd>SCOR</Cd>
        </CdOrPrtry>
      </Tp>
      <Ref>RFxx123456789</Ref>
    </CdtrRefInf>
  </Strd>
</RmtInf>

According to EPC142-08 (https://www.europeanpaymentscouncil.eu/sites/default/files/KB/files/EPC142-08-EPC-Guidance-on-Creditor-Reference-ISO-Std.pdf) the bank must return this structured reference. Unfortunately, Dynamics 365 cannot currently produce a proper Payment ID compliant with the Mod 97 checksum required for structured creditor references, since the so-called “Norwegian” Payment ID is not flexible enough.

You can vote for an improvement here: Dynamics Idea Portal – Structured Creditor Reference.

As a workaround, assuming invoice numbers contain only digits, you can form a pseudo-structured reference by prefixing the invoice number with RF00 with the feature NO-00002 Customer payment based on payment ID – Finance | Dynamics 365 | Microsoft Learn. This synthetic reference can then be used by the reconciliation rule to match incoming customer payments in the bank statement:

Manipulate PDF files in Dynamics 365 with iText

Manipulate PDF files in Dynamics 365 with iText

When making archivable invoices in Dynamics 365 Finance & Supply Chain Management, PDF handling may be a topic: we must ensure PDF/A-3 compliance, and guarantee that invoices remain readable for customers and auditors. In this series, I will show you how to manipulate PDFs directly from X++ using the iText 7 for .NET library.

The iText is a popular open-source library for creating and manipulating PDF files programmatically. It offers support for advanced use cases such as: converting HTML to PDF, filling interactive PDF forms, embedding attachments, converting to PDF/A (archival standards). This makes iText a versatile tool when extending Electronic Reporting (ER) or custom document generation in Dynamics 365 FO.

At first glance, you might be tempted to take the latest version of iText. Unfortunately, in Dynamics 365 FO, that’s not possible. From version 8 onwards, iText introduced a breaking change; it requires one of two mutually exclusive cryptography connectors: BouncyCastle Adapter or BouncyCastle FIPS Adapter. In a normal .NET Core or Java application, you could choose one. But in Dynamics 365 FO, we are bound by the AOS linking rules: both cannot coexist, and we cannot configure mutually exclusive linking at runtime. That’s why we stick to iText 7 for .NET, the last major version without this restriction.

DLL libraries

Library Purpose NuGet / Download
itext.commons Common utilities, required by all modules commons.nuget 7.2.6
itext.kernel Core PDF document model itext7.nuget 7.26
itext.io Low-level PDF parsing and writing, required by kernel
itext.forms AcroForms support (reading/writing fields)
itext.layout High-level layout, form value appearances
BouncyCastle.Crypto Cryptography provider, required by kernel Portable.BouncyCastle 1.9.0

Put these into the bin folder of the model, then add these to your project -> References, browsing and picking them from the folder.

PdfDocument initialisation and “Stamping” 

Here’s a real-world method implemented in Dynamics 365 FO. It takes an existing PDF, flattens all form fields (so values from XFDF imports remain visible), and returns a memory stream you can pass further to the archive as an attachment.

Stamping mode means that you take an existing PDF, open it for reading, and at the same time prepare a writer to save changes into a new file or stream. In iText this is controlled by the constructor of PdfDocument. If you pass only a PdfWriter, you start with a blank PDF and stamping is not possible. If you pass only a PdfReader, you can read the document but you cannot modify it. If you pass both a PdfReader and a PdfWriter, the document is opened in stamping mode. That is the case in the below Dynamics 365 example.

				
					using iText.Kernel.Pdf;
public class ERFileDestinationPostProcessor
{
    protected System.IO.MemoryStream postProcessPDF(System.IO.Stream _pdfStream)
    {
        System.IO.MemoryStream outputStream = new System.IO.MemoryStream();
        try
        {
            // Setup reader + writer
            PdfWriter writer = new PdfWriter(outputStream); // itext.commons and bouncyCastle invoked
            writer.SetCloseStream(false); // keep outputStream usable after close
            PdfReader reader = new PdfReader(_pdfStream);

            PdfDocument pdfDoc = new PdfDocument(reader, writer);

            // Flatten AcroForm fields so values remain visible (XFDF values preserved)
            iText.Forms.PdfAcroForm form = iText.Forms.PdfAcroForm::GetAcroForm(pdfDoc, true);
            if (form != null)
            {
                form.SetGenerateAppearance(true);            
                var Fields = form.GetFormFields();
                if (Fields.Count > 0) // Touch every field to generate appearence
                {
                    var Enumerator = Fields.GetEnumerator();
                    while (Enumerator.MoveNext())
                    {
                        var KeyValuePair = Enumerator.get_Current();
                        var Field = KeyValuePair.get_Value();
                        str value = Field.GetValueAsString();

                        if (value)
                        {
                            // Set the value to itself, Force regenerate appearance
                            Field.SetValue(value); // itext.layout is leveraged here
                        }
                    }
                }
                form.SetNeedAppearances(false); // PDF/A requirement: NeedAppearances must be false or absent
                form.FlattenFields();
            }            
            pdfDoc.Close();
            reader.Close();
            writer.Flush();
        }
        catch (Exception::CLRError)
        {
            System.Exception exception = CLRInterop::getLastException();
            if (exception != null)
            {
                warning(exception.get_InnerException() ? exception.get_InnerException().get_Message() : exception.get_Message());
            }
        }
        return outputStream;
    }
}

This flattening is important because it guarantees that all the values filled into the PDF form remain permanently visible and readable even if the file is opened outside of Adobe Reader. To meet PDF/A compliance rules, the NeedAppearances flag must be set to false, so viewers do not depend on dynamic rendering of the fields. By assigning each field’s value back to itself, the code forces iText to regenerate the visual appearance; otherwise, the .FlattenFields() call is going to wipe all form data from the document.

Embedding an ICC profile 

A PDF/A document must declare its output intent. The output intent tells a PDF viewer how colours should be interpreted, and this is done by embedding an ICC profile. For most business documents, the standard profile is sRGB IEC61966-2.1, which defines an RGB color space suitable for PDFs primarily displayed on the screen. You may download it here: https://www.color.org/srgbprofiles.xalter. Without a profile, the PDF is not going not pass validation for PDF/A.

In Dynamics 365 we cannot rely on a file path, so the ICC profile is added as an embedded resource (AOT/Resources). At runtime the code checks whether the PDF already contains an output intent, then loads the profile from the resources and attaches it to the document. There should not be more than 1 profile in the document:

				
					            // Check if there is already an ICC profile embedded
            PdfDictionary catalog = pdfDoc.GetCatalog().GetPdfObject();
            PdfArray outputIntents = catalog.GetAsArray(new PdfName("OutputIntents"));
            if (outputIntents == null || outputIntents.Size() == 0)
            {
                // Load sRGB Preference ICC profile from the embedded resource
                ResourceNode iccResourceNode = SysResource::getResourceNode(resourceStr(sRGB_v4_ICC_preference));
                container iccInContainer = SysResource::getResourceNodeData(iccResourceNode);

                System.IO.Stream iccStream = Binary::constructFromContainer(iccInContainer).getMemoryStream();
                if (iccStream)
                {
                    // Add output intent (required by PDF/A)
                    PdfOutputIntent outputIntent = new PdfOutputIntent(
                        'Custom',
                        "", // outputCondition
                        'http://www.color.org',
                        'sRGB IEC61966-2.1',
                        iccStream);
                    pdfDoc.AddOutputIntent(outputIntent);
                }
                else
                {
                    warning("ICC profile is not found in D365...");
                }
            }

Replacing the PDF before archiving

Microsoft provides the ERDocuManagementEvents class and event handlers that allow you to intercept how Electronic Reporting saves files in Organisation administration > Electronic reporting > Electronic reporting jobs, see https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/er-custom-storage-generated-documents.

The following event handler subscribes to the point where ER is about to save the file. We check if the file is a PDF we wish to intercept, and if so, we run our own post-processing logic before handing the stream back to ER. If the “handled” state is sent back to ER with  fileEventArgs.markAsHandled(), then the archive receives the replaced file.

				
					using Microsoft.Dynamics365.LocalizationFramework;
/// <summary>
/// This is the main trigger for the PDF post-processing
/// </summary>
class ERDocuSubscriptionCopy
{
    /// <summary>
    /// Capture an attempt by ER to save an attachment, react to a DocuTypeId with a magic pattern in the name
    /// </summary>
    /// <param name = "_args">Attachment context</param>
    [SubscribesTo(classStr(ERDocuManagementEvents), staticDelegateStr(ERDocuManagementEvents, attachingFile))]
    public static void ERDocuManagementEvents_attachingFile(ERDocuManagementAttachingFileEventArgs _args)
    {
        ERDocuManagementAttachingFileEventArgs fileEventArgs = _args;
        if (fileEventArgs.isHandled())
        {
            return;
        }
        if (! strContains(fileEventArgs.getDocuTypeId(), "MyDocuType"))
        {
            return;
        }

        var inputStream = fileEventArgs.getStream();
        // Rewind
        if (inputStream.CanSeek)
        {
            inputStream.Seek(0, System.IO.SeekOrigin::Begin);
        }

        ERFileDestinationPostProcessor postProcessor = new ERFileDestinationPostProcessor();
        System.IO.MemoryStream outputStream = postProcessor.postProcessPDF(inputStream);

        // Do something with the  PDF
        if (outputStream != null)
        {
            boolean attachmentHandled;            
            try
            {
                DocuRef     docuRef;
                docuRef = this.createDocuRef(outputStream,
                                         fileEventArgs.getOwner().TableId,
                                         fileEventArgs.getOwner().RecId,
                                         fileEventArgs.getOwner().DataAreaId);
                if (docuRef)
                {
                    fileEventArgs.setDocuRef(docuRef);
                    attachmentHandled = true;
                }
            }
            finally
            {
                attachmentHandled = false;
            }

            if (attachmentHandled)
            {
                fileEventArgs.markAsHandled();
            }
        }
    }
}

X++ programming blog series

Further reading:

Import a D365 FO entity with a virtual KEY field
Print a custom product label: a Template solution in Process Guide Framework, with detours
Extending SysOperation contracts with DataMemberAttribute
X++ Decorator pattern in Dynamics 365
Get a cost center in D365FO
Find and merge a DimensionAttributeValue
SysExtension framework pitfall: avoid new()
Input validation and messaging in the Process Guide Framework

Customer invoice falsifier for D365 for Finance

Customer invoice falsifier for D365 for Finance

In certain business scenarios, it may become necessary to update and reprint customer invoices in Dynamics 365 for Finance with corrected or intentionally adjusted figures. By selectively mapping only specific invoice header and invoice line fields for update, the ER configuration we are sharing here enables targeted adjustments without triggering recalculations of dependent totals. These electronic reporting configurations may also be used as a tutorial for the Excel imports in D365.

Check the above screenshot: assuming we’ve initially issued an invoice with 19% VAT instead of the reduced 7% VAT, the ratio between the SalesBalance (amount before VAT) and SumTax (total VAT amount) may be shifted while keeping the total invoice amount the same. Beware: to reflect the VAT manipulations in the tax books a separate [cumulative] GL journal must be posted manually. Please ensure any misaligned invoice reprints are clearly marked as adjusted copies to avoid confusion or compliance issues, though.

The underlying method uses the Electronic Reporting import configuration capabilities in Dynamics 365 for Finance. You may download the ER configurations here:  CustInvoiceUpdater.zip. Unpack and import into the ER tree one by one, starting with the model. Use it at your own risk!

Key elements include:

  1. Data Model – definition of the conceptual invoice structure (header amounts, line amounts, tax values, etc.).
  2. Model Mapping for export and a model mapping To destination for the import (both in the same model mapping ER configuration) – links the data model fields to the relevant Dynamics 365 tables and columns.
  3. Formats – define the export file structure (Excel) and the same import file structure and maps the file’s data to the model fields.

For a detailed description of the ER import concept and practical configuration guidance, check Electronic Reporting in Data Migration.

The process follows a three-step cycle:

  1. Export: extract invoice data from Dynamics 365 into an Excel using the “CustInvoiceJourDump format” ER export configuration.

  2. Adjust / Transform: modify the extracted data in Excel, changing selected amounts while leaving others untouched.

  3. Import: re-import the adjusted file by selecting the “CustInvoiceJour mapping” in the ER tree, clicking Designer, then Run at the “To destination” mapping, and uploading the Excel file. If the file is well formed and there is a match for every RecId of the invoice headers/lines, it updates the records in Dynamics 365 and says “Operation completed“.

In our scenario, the import mapping is deliberately limited to only 3 or better to say 6 amounts, both in the transaction currency and the accounting currency (the “MST” amounts): Sales subtotal amount, Total charges, VAT amount. A built-in validation ensures these three values still sum to the original invoice total, maintaining internal consistency while allowing the reprint to reflect updated breakdowns.

The “CustInvoiceTrans mapping” for invoice lines does not have this safeguard, since the invoice lines to not hold grand totals in D365.

The total invoice amount field was intentionally excluded from the update, because this may lead to a mismatch between the general ledger and the AR subledger.

Electronic reporting blog series

Further reading:

Customer invoice falsifier for D365 for Finance
Amend GDPdU = GoBD = FEC
Z4-Meldung an Bundesbank
Enumerate lines in Configurable Business Documents
D365 Electronic Reporting: JOIN records in pairs
Electronic Reporting (ER) Cookbook 4: References in a model
Electronic reporting for data migration
Electronic Reporting (ER) Cookbook 3: Working with dates
Electronic Reporting (ER) Cookbook 2: new tips from the kitchen
Electronic Reporting (ER) Cookbook