Proposal for a shorter EndToEndId in pain.001, or a 3-letter hash for RecId

As mentioned in the recent blog Matching own payments in Modern bank reconciliation, the <EndToEndId> element generated by the Electronic Reporting configuration for ISO20022 Credit Transfers is built from the AP payment journal line voucher + RecId of the journal line + a 2-digit counter if multiple attempts are made to generate the same pain.001 file:

				
					<EndToEndId>APP-0036644-5637312576-02</EndToEndId>

In my opinion, the RecId (the “5637312576” part in the example) is unnecessary. Consider the arguments:

The voucher number is unique in every journal line, unless the Voucher series parameter at the Journal name is set to Manual or One voucher number only, or the Number allocation at posting is activated. All three setups are truly exotic in Dynamics 365 for Finance implementations for a number of reasons:
The One voucher number only option contradicts the recommended GL master setting [Dis]allow multiple transactions within one voucher. An attempt to post is going to produce 2 error messages for every journal line – “There can only be one vendor or customer transaction per voucher.” and “There can only be one bank account transaction per voucher.“
The Number allocation at posting defies the purpose: in the unposted journal, the system assigns temporary voucher numbers, which are then replaced at posting. The SEPA pain.001 file is made before posting, and its EndToEndId is going to hold the temporary voucher number: <EndToEndId>Temp001-5637312576-02</EndToEndId>. But such a line cannot be reconciled with the Modern bank reconciliation (see Matching own payments in Modern bank reconciliation), since the bank transaction will receive a different, the final voucher number on posting.
The RecId has a low entropy: it is usually monotonously rising in a payment journal, since the lines are created all at once by the Payment proposal. The number of lines is typically below 1000, as a longer journal takes increasingly longer to post. Consequently, just the last 3-4 digits of the RecId are significant.
In certain geographies, the EndToEndId may not be that long (India: 16 characters only). Truncating from the right will remove the important attempt counter, and the bank upload will fail on a second attempt. Truncating from the left will corrupt the voucher number: now the bank statement reconciliation will fail.

I suggest replacing CONCATENATE(@.PaymentIdentifications.EndToEndIdentification, "_", LEFT("00", 2-LEN(@.'$paymentEnumerator')), @.'$paymentEnumerator') in model.Payments.$PreprocessedEndToEndId …

…with CONCATENATE(REPLACE(@.PaymentIdentifications.EndToEndIdentification,”-\d+$”,””,true),”_”, LEFT(“00”, 2-LEN(@.’$paymentEnumerator’)), @.’$paymentEnumerator’)

This eliminates the last group of digits together with the trailing “-” from the EndToEndIdentification element, and the result is going to be like this:

				
					<CdtTrfTxInf>
        <PmtId>
          <InstrId>APP-0036644-2026</InstrId>
          <EndToEndId>APP-0036644-02</EndToEndId>
        </PmtId>

Shorten the RecId with a 3-letter hash “ABC”

If for some reason the RecId as a unique element must be preserved (I would not know, why, but let’s assume), then it may be reduced from a long 64-integer to a pseudo-unique 3 letter combination, derived from the last 4 digits of the RecId. These 4 digits are shuffled and dispersed, then the resulting integer value is converted into a base-26 number, encoded by capital latin letters:

				
					str input = REPLACE(model.Payments.PaymentIdentifications.EndToEndIdentification, "^.*-", "", true)
int last4 = INTVALUE(RIGHT(@.input, 4))
int mixed = @.last4*73 + 41                             ' prime numbers *salt*
int mod17576 = @.mixed-INTVALUE(@.mixed/17576)*17576    ' 17576 = 26³

int c1v  = INTVALUE(@.mod17576/676)  ' 676 = 26²
int rem1 = @.mod17576 - @.c1v*676
int c2v  = INTVALUE(@.rem1/26)       ' same as div(26)
int c3v  = @.rem1 - @.c2v*26

str output = MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", @.c1v+1, 1)&
             MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", @.c2v+1, 1)&
             MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", @.c3v+1, 1)

The input is the EndToEndIdentification stripped of the preceding voucher number up to the last “-” i.e. the RecId at the end. The result is a short deterministic string at the place of the longer RecId number:

<EndToEndId>APP-0036644UUT29</EndToEndId>

<EndToEndId>APP-0036645UXO01</EndToEndId>

Electronic reporting blog series

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:

Amend GDPdU = GoBD = FEC

The audit export file in Dynamics 365 for Finance may be easily extended, a power user can do it on his/her own. For example, the auditors often ask for a “system date” or time column in the audit file for their DATEV applications: in a nutshell, they need to know when the invoice was really posted, not only the invoice date.

Here is how to fulfil this requirement:

Open the Electronic reporting workspace (Organisation administration > Workspaces). Make sure a custom “provider” is selected as “active”. Check here to learn how and why: Electronic Reporting (ER) Cookbook 2: new tips from the kitchen
Click the Reporting configurations tile and locate the root Data export model node. To amend the French FEC file, select the French FEC model mapping instead.
Click +Create configuration and select the “Model Mapping based on data model Data export model” option. Namely, the model itself is very abstract in the case of this data export, the interesting stuff is hidden in the model mapping.
Select the new model mapping in the tree and use the Designer, then click Designer again.
In the Model mapping designer, look for the odd looking objects like _01xxxx, _02xxxx etc. at the bottom of the Data sources list. These are the “Table metadata” objects. I have a feeling that these artifacts were invented solely for this usage scenario of generic data exports with variable columns. In our case, choose _01Sachkonten, then Edit, then Editor. In the French FEC export, the “Table” is called TblGroup1 or “FEC files with details“:
The Table metadata editor has a unique UI. In the leftmost list, select the Sachkontobuchungen table. In the rightmost list, locate the $Entries/$GeneralJournalEntry/createdDateTime node and Add it to the middle list of fields (see the screenshot above). Rename the new field, if necessary.
That’s it, hit Save, close the table editor, confirm with OK and save the changes in the model mapping designer ultimately.
In the end, Change status of your custom model mapping to Complete and mark it as the Default for model mapping to inactivate the standard one.
If you need to format the date-time column in a special way, you’d need to make a format derived from the standard German audit file output, too. In that one, you’d need to modify the Transformation fmtdatatime. You will learn in the blog Electronic Reporting (ER) Cookbook 3: Working with dates how to do it.
To test your changes, launch General ledger > Periodic tasks > Data export, select the Format mapping (choose your own if you modified it at step 9). Select Table Group = Ledger accounts, choose the dates, launch and verify the output in one of files in the resulting ZIP offered for download:

"130300-001-";2025-02-21;"Period 2";"68721077482";"Benutzung";"Verkauf - Lieferschein-Umsatzerlös, Gegenkonto";"Nein";"Nein";499,96;"EUR";499,96;"";21.02.2025;"SPS-1000165";21.02.2025;"SPK-000166";"Aktuell";"";"";68719785542;"SPK-000166"

"140200-001-";2025-02-21;"Period 2";"68721077479";"Benutzung";"Kosten der gelieferten Einheiten";"Nein";"Ja";-315,50;"EUR";-315,50;"";21.02.2025;"SPS-1000165";21.02.2025;"SPK-000166";"Aktuell";"";"";68719785542;"SPK-000166"

By the way, in the German export you may use the parameter Include “Create by” field to comply with another common requirement: export the user ID to prove to the auditor that it is the accountant who makes the ledger transactions, not an IT consultant 😉