Troubleshoot the VAT Declaration in D365 for Finance

Users of the VAT declarations (both Excel and XML) in the financial department are often shocked by error messages of this kind:

Evaluating binding of format component Excel/Sheet1/I_Turnover/Box_200
Fehler beim Bewerten des Ausdrucks für Pfad ‚Boxes/Box81_Base‘

The exact error message differs by country: the first is produced by the “VAT Declaration Excel (CH)” / “VAT Declaration XML (CH)” for Switzerland, the second one is from Germany. In reality, the error has nothing to do with the “Boxes” i.e. the Excel layout but a clear sign that the Application specific parameters / Setup fail to classify a specific VAT use case. Namely, a VAT code was used in a module / situation not imagined by the FiCo functional consultant who configured the VAT Declaration.

Follow the below algorithm to troubleshoot the VAT Declaration and add additional cases into Application specific parameters:

1. Use division by dichotomy to find the exact date

… when it happened. Most of the European VAT declarations are returned monthly, and it takes on average 7 attempts to narrow down the date. Locate the VAT declaration in the Organisation administration > Workspaces > Electronic reporting, Reporting configurations tree under the Tax declaration model node. Start the report this the Run button above, variate the dates. For example:

01.05.2024 – 15.05.2024: no error

16.05.2024 – 23.05.2024: error

16.05.2024 – 19.05.2024: no error

20.05.2024 – 21.05.2024: error

20.05.2024: no error

21.05.2024: bingo!

2. Find uncommon combinations of VAT code and Direction

Having spotted the date, filter the tax transactions by date and the VAT settlement period using the query Tax > Inquiries and reports > VAT inquiries > Posted VAT (hereafter I refer to the en-gb language of the D365 UI). Export the results into Excel and build a pivot table by VAT code and direction, counting the vouchers (= business cases). Start looking for outliers. Pay attention to the low count i.e. seldom use cases. Often the mistake is obvious:

VAT code	Duty-free purchase	Duty-free sale	Use tax	VAT payable	VAT receivable
euSt	1	7	1
exSt		7
FoodR				15
R				2	5
ServSt				11
St				39

The euSt code (intra-community acquisition with the standard tax rate of 19%) is not supposed to appear in the context of a VAT free purchase, but someone managed to make such a transaction in a general ledger journal when entering a EU supplier invoice.

You cannot fix the tax transactions, but the Application specific parameters may be amended to account for this irregularity. Open this list of Conditions and filter it by the tax code in question to validate the suspicion:

You have to add a new line with this combination of the Tax code and the Transaction classifier to capture this exceptional case. Namely, a pair euSt–PurchaseExempt must be added. Try the VAT declaration one more time to confirm that the problem is gone.

3. Use a fallback condition

Sometimes it is not that easy to spot an unusual pattern. The occurrences are too many even on a single day. Your next attempt may be using the placeholders *Not blank* for the Tax code and *Not blank* for the Transaction classifier. These lines must be placed at the very bottom of the list of conditions (use Move down if needed).

Leverage fallback lines to redirect the total tax amount of the questionable nature into a VAT declaration cell which usually stays empty, for example the cell 61-InputTaxCorrection on the German “UVA” tax declaration. This will give you an idea how much is missing in other cells, and what the tax origin amount may be.

Do not keep the placeholders in your PROD setup for long: by doing so, you are resigning and testifying that you do not really know what is happening in the tax ledger but playing dice with the tax authority, which is a very bad idea.

You may also have some tax transactions that do not need to be reported to the tax authority at all. Capture such a case with a combination of a VAT code and direction, and send it into the Other cell i.e. into oblivion:

4. If nothing helps

…then you may have encountered a true bug in the VAT declaration, a case that does not fit into any of the Transaction classifier categories but an Error one!

Indeed, recently we discovered that the reversal of a customer transaction settlement with a realised cash discount is accompanied by a positive adjustment of the VAT payable (i.e. we again own more VAT to the state, which is correct). But this adjustment is not assigned the VAT direction of Sales, it results in an Error in the VAT declaration.

Add lines with the explicit Transaction classifier = Error to manage this case:

Blog series on Finance and VAT in the EU

Import a D365 FO entity with a virtual KEY field

I’ve got a case recently where a delimited text file had to be imported into Dynamics where a key field was a virtual one. Namely, an external APS system sent a single ProdOprNum of the record, whereas the respective ProdRoute table in Dynamics 365 for SCM has a composite key ProdId + OprNum. Pre-processing the file with an XSLT and splitting the column was not an option, because the Microsoft XSLT parser still does not implement the XSLT 2.0 standard and cannot uptake a text file.

The most important lesson to learn was that the – generally useful – guidance Use a virtual field to receive and parse an inbound field | Microsoft Learn did not work, because the system kernel started looking for the record to update BEFORE the compound key was parsed in the mapEntityToDataSource() method of the entity. It was not able to find the right record to update, which lead to an error later on.

The solution is as follows:

Follow the above guidance from Microsoft and create an entity with the key fields ProdId, OprNum (both mapped) and one unmapped virtual field ProdOprNum.
With a right mouse click, create or refresh the corresponding staging table.
Here comes the crucial part: remove any relation such as Staging.ProdId=Entity.ProdId && Staging.OprNum=Entity.OprNum from the staging table. You should do so every time you refresh the staging table, D365 will namely re-create it over and over again. As a side effect, the Data management will think you insert the records while in reality the record is updated, but this does not matter.
Implement the splitting in the initializeEntityDataSource(), select the right record to update and plant it as shown below:

				
					 public void initializeEntityDataSource(DataEntityRuntimeContext _entityCtx, DataEntityDataSourceRuntimeContext _dataSourceCtx)
{
	super(_entityCtx, _dataSourceCtx);
	
	switch (_dataSourceCtx.name()) 
	{
		case dataEntityDataSourceStr(ProdRouteSchedEntity, ProdRoute):
			ProdRoute prodRoute = _dataSourceCtx.getBuffer() as ProdRoute;
			if (prodRoute)
			    break;
			    
			this.ProdId = substr(this.ProdOprNum, 1, 10);
			this.OprNum = any2int(substr(this.ProdOprNum, 11, 14));
			
			prodRoute = ProdRoute::find(this.ProdId, this.OprNum, RouteOprPriority::Primary, true);
			_dataSourceCtx.setBuffer(prodRoute);
	}
}

X++ programming blog series

Z4-Meldung an Bundesbank in D365 for Finance

The Z4-Meldung “Zahlungen im Außenwirtschaftsverkehr (abbreviated “AWV”) is a financial report that German companies must submit to the Deutsche Bundesbank as part of their external sector statistics. Companies are usually required to submit this report on a monthly basis, depending on the volume of transactions.

All German companies that engage in cross-border financial activities (e.g., loans, deposits, securities transactions) with a value above a specific threshold (currently €12500 per transaction) are required to submit this report. The worst part of it: not only transactions with non-EU countries must be reported, but inbound and outbound payments to EU countries as well. This is a nightmare in the tightly integrated EU economy.

Luckily, payments related to trade in goods (such as exports or imports) are not reported in the Z4-Meldung. These transactions are typically covered under other reporting mechanisms, such as the Intrastat for trade within the EU. Instead, the Z4-Meldung focuses on financial transactions unrelated to the direct exchange of goods e.g., loans, financial investments, and – most importantly – services. In particular, these transactions may be:

payments for services rendered for a foreign entity or paid to a foreign supplier,
for instance, freight costs
bank interests paid or received
cash discounts given to foreign customers or received from foreign suppliers
rebates received or paid
intercompany recharge to or from a foreign HQ or a subsidiary
VAT payments to a different EU tax authority.

The Z4 report has a simple XML structure following their proprietary “XMW” schema: XMW – Elektronisches Meldewesen im XML-Format. In the regular payment process, in- and outpayments are classified with a transaction type (BELEGART) of “1” or “2”, respectively. In every transaction the foreign country is listed (ISO2), along with the transaction description and the amount in tsd. Euros. The sub-type classifier “KENNZAHL” poses a significant challenge: there are ~1000 codes in total from “A” for airfare through “S” for services till “U” for the use of software. This list from hell is published here in an inconvenient Excel file: “Kennzahlenliste mit Belegarten”.

We are happy to share an Electronic Reporting configuration for the Z4 report in Dynamics 365 for Finance. Only the regular payment transactions (“DIKAPPOSTEN”) are supported, not shares or similar capital investments. To segregate payments by the highly detailed “KENNZAHL“, we suggest using the voucher prefix. For example, you may need to introduce different GL journal names for salary payments and commissions payments, both with distinct voucher number sequences. To avoid mistakes, we recommend separate payment methods, especially for goods and services, with every payment method to be processed in its own AP/AR payment journal.

Setup and use

Download the 3 configurations here: XMW_Model_Mapping_Format_v2
Install them in the following order: XMW Deutsche Bundesbank model -> XMW Mapping -> Z4 (format). Beware: the configurations are provided to you “AS IS” and with all faults and defects without warranty of any kind.
Select the Z4 configuration and open Application specific parameters / Setup. You may Import a rudimentary setup from the ZIP file.
With this setup you match patterns in the voucher of the bank transaction to the “KENNZAHL” codes. In some cases, you may decide to start looking for keywords in the payment description instead. This will require a slight modification of the formula in model/Messages/Message/Transactions/$ClassifierEnum. Alternatively, you may start probing for vendors or introduce a special vendor group with regards to the Z4 reporting; this is going to require a minor adjustment in the mapping and the format.
Set the status of the application specific parameters to Completed when ready.
Select the Z4 format in the Electronic reporting tree and hit Run.
The Firmennummer is the unique end-to-end ID issued by the Bundesbank at the registration. You may also set the Extranet-ID of the user, if known. The mandatory phone, e-mail and other contact information are extracted from the employee card of the current user (the user who is executing the report i.e. you).
With the Period date you select the reporting period (month). An empty period date means the current [session] month. Try to exclude bank transactions with regards to the trade of goods with the filter of Records to include, for example by the voucher number, if possible. You may also consider opening a different bank account for the regular intracommunity business. Again, try to separate payments of service, commissions etc. with distinct journals = voucher number.
Hit OK and execute the report.
You may also run the report in an unattended, batch mode. Configure an Electronic reporting destination such as an internal e-mail address or a SharePoint folder, schedule a monthly batch and keep the period date empty to support the rolling date. You can monitor the batch runs in Organisation administration > Electronic reporting > Electronic reporting archived jobs.

Result

The resulting file may look like this:

				
					<LIEFERUNG-AWZEL xmlns="http://www.bundesbank.de/xmw/2003-01-01" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.bundesbank.de/xmw/2003-01-01/BbkXmwAwzel.xsd" version="1.0" erstellzeit="2024-09-27T08:30:33+02:00" stufe="Produktion" bereich="Statistik" dateireferenz="1">
    <ABSENDER>
        <FIRMENNR>00345678</FIRMENNR>
        <NAME>ER-Consult GmbH</NAME>
        <STRASSE>Franz Lehar-Gasse 18</STRASSE>
        <PLZ>7111</PLZ>
        <ORT>Parndorf</ORT>
        <LAND>AT</LAND>
        <KONTAKT>
            <ANREDE>Miss</ANREDE>
            <VORNAME>Julia</VORNAME>
            <ZUNAME>Funderburk</ZUNAME>
            <ABTEILUNG>Sales & Marketing</ABTEILUNG>
            <TELEFON>425-555-5053</TELEFON>
            <EMAIL>julia@contoso.com</EMAIL>
            <EXTRANET-ID>EXNTESTA</EXTRANET-ID>
        </KONTAKT>
    </ABSENDER>
    <KOMMENTAR>Und ich dachte, ich bleibe unter dem Radar...</KOMMENTAR>
    <MELDUNG erstellzeit="2024-09-27T08:30:34+02:00">
        <MELDEPFLICHTIGER>
            <FIRMENNR>00345678</FIRMENNR>
            <NAME>ER-Consult GmbH</NAME>
            <STRASSE>Franz Lehar-Gasse 18</STRASSE>
            <PLZ>7111</PLZ>
            <ORT>Parndorf</ORT>
            <LAND>AT</LAND>
            <KONTAKT>
                <ANREDE>Miss</ANREDE>
                <VORNAME>Julia</VORNAME>
                <ZUNAME>Funderburk</ZUNAME>
                <ABTEILUNG>Sales & Marketing</ABTEILUNG>
                <TELEFON>425-555-5053</TELEFON>
                <EMAIL>julia@contoso.com</EMAIL>
                <EXTRANET-ID>EXNTESTA</EXTRANET-ID>
            </KONTAKT>
        </MELDEPFLICHTIGER>
        <MELDETERMIN>2024-09</MELDETERMIN>
        <VDR_04>
            <DIKAPPOSTEN belegart="2" kennzahl="523" zahlungszweck="Provision 2024">
                <BETRAG land="CN" landname="China" betragsref="APPM000063">-18</BETRAG>
            </DIKAPPOSTEN>
        </VDR_04>
    </MELDUNG>
</LIEFERUNG-AWZEL>

Electronic Reporting

Troubleshoot the VAT Declaration in D365 for Finance

Troubleshoot the VAT Declaration in D365 for Finance

1. Use division by dichotomy to find the exact date

2. Find uncommon combinations of VAT code and Direction

3. Use a fallback condition

4. If nothing helps

Blog series on Finance and VAT in the EU

Import a D365 FO entity with a virtual KEY field

Import a D365 FO entity with a virtual KEY field

X++ programming blog series

Z4-Meldung an Bundesbank in D365 for Finance

Z4-Meldung an Bundesbank in D365 for Finance

Setup and use

Result

Electronic reporting blog series