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

Luxembourg VAT Declaration for D365FO in PDF

There is the 2nd most prosperous country on this planet, which is apparently too small to get a proper VAT declaration in Dynamics 365 for Finance. Not too small for ER-Consult, though. We have released a complete Luxembourg VAT Declaration (TVA) built entirely with Electronic Reporting (ER) and delivered as an AED-compliant PDF form.

All relevant boxes of the declaration will be populated, including the sales regime, the key turnover fields, reverse-charge sections for both EU sales and EU purchases, the deductible VAT areas, adjustments, and all totals required for the final VAT balance, as well as the identification header. We support 17% (standard), 14% (intermediate), 8% (reduced), 3% (super-reduced) as the VAT rates, i.e. the status quo from the 1st of January 2024 onward. The title is automatically generated, based on the selected from-to date criteria.

In detail, the following sections and cells are calculated:

Section I.A: Grand total of sales A.1.b
Section I.B: Exempt sales, including “1” intra-community deliveries (457), “2” export of goods into 3rd countries (014), “3” other sales exempt everywhere (015, international transport), “4” exempt domestic sales (016, financial and insurance, postal services), “9.a” sales of goods acquired in triangular trade (018), “9.b.1” delivery of services and intangible goods into an EU country (423), “9.b.2” delivery of services and intangible goods into an EU country where they are exempt (424), and “9.c” rendering services for a non-EU customer and other operations abroad (019)
Section I.C: Total taxable sales turnover as a difference of I.A-I.B
Section II.A: Breakdown of domestic sales of goods and services (701-040)
Section II.B: Intra-community acquisitions of goods implemented as a Use tax in D365 (711-194)
Section II.D.1: Import of goods from outside of the EU for business purposes, either self-declared or recharged by the customs broker as an VAT receivable (721-195)
Section II.E.1: Receipt of reverse-charged services rendered in a foreign EU country and implemented as a Use tax (741-435)
Section II.E.2: Receipt of reverse-charged services rendered in a foreign country outside of the EU (ex: Britain, USA, India) and implemented as a Use tax (751-445)
Section II.F: Receipt of reverse-charged goods domestically in Luxembourg (for example: purchases of scrap; construction materials within the construction industry, 769-764)
Section II.H: Total of the VAT payable, aggregating all of the above
Section III.A: Totals of the VAT receivable, including “1” domestic purchases, “2” intra-community acquisitions from II.B, “3” import VAT from II.D.1, “4” reverse-charged VAT receivable offsetting the II.E.1, II.E.2 and 2.F
Section III.C: Grand total of the VAT receivable
Section IV: Grand totals and the resulting VAT load

A tax code lookup, implemented exactly like in all other supported VAT declarations in Dynamics 365 for Finance, namely under the Application specific parameters of the ER format, is fully configurable and free of hard-coding:

*This screenshot has been taken in the DEMF company of the “Contoso” demo database, hence the German rates.

All this makes the solution transparent, maintainable, and aligned with Microsoft’s Globalisation framework. The ER format is compatible with the Tax calculation add-on, too: in a multi-country, multi-VATID scenario it may be selected as the VAT declaration for the LUX country, alongside Microsoft’s standard VAT declarations for NLD or BEL.

Blog series on Finance and VAT in the EU

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:

Author Archives: Eugen Glasow

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

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

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

Electronic reporting blog series

Luxembourg VAT Declaration for D365FO in PDF

Luxembourg VAT Declaration for D365FO in PDF

Blog series on Finance and VAT in the EU

Matching own payments in Modern bank reconciliation

Matching own payments in Modern bank reconciliation

Credit transfers

Direct debits