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.
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, 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 😉

Electronic reporting blog series

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 envisioned 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. Keep starting the report with 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 => it happened on the 21.05.2024

2. Find uncommon combinations of VAT code and Direction

Having spotted the date, filter tax transactions by that 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. Build a pivot table by the VAT code and direction, counting the vouchers (= business cases) at the cross sections. Start looking for outliers, pay particular attention to the low counts 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

In this example, the euSt code designates VAT exempt intra-community sales, or intra-community acquisitions at the standard tax rate of 19% (= “use tax” or Import VAT / purchase VAT in en-gb, see Minimalistic EU VAT Configuration in Dynamics 365). This code 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 now gone.

3. Use a fallback condition

Sometimes it is not that easy to spot a pattern: the occurrences may be too many even on a single day. Your next approach may be to leverage 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 (note the Move down button).

Use these fallback lines with placeholders to redirect the total tax amount of the questionable nature into a separate VAT declaration cell which is typically empty, for example to the cell 61-InputTaxCorrection on the German “UVA” tax declaration. This will give you an idea how much is missing in tax, 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 don’t really know what is happening in the tax ledger but play 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 attributed to the VAT direction of Sales, it results in an Error in the VAT declaration instead.

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);
	}
}

Electronic Reporting

Amend GDPdU = GoBD = FEC

Amend GDPdU = GoBD = FEC

Electronic reporting blog series

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