Electronic reporting for data migration

Intro

There are still dozens of important tables in Dynamics 365 for Finance and SCM not exposed by any entity. Sometimes simple solutions like my Copy-paste with a keyboard script from Excel don’t work due to the volume or complexity of the data, and sometimes there is no UI at all.

One of the notorious examples is the table EcoResCatalogControl which is associated with product attributes bound to procurement categories, see the Searchable flag in my blog Searchable product attributes. An import of these “Product category attributes” through the entity EcoResProductCategoryAttributeEntity leaves the attributes dysfunctional: the internal table EcoResCategoryAttributeLookup is left out of sync and the new attributes remain invisible in the product master.

The only form where the EcoResCatalogControl is editable and visible is the Procurement category form, but an attempt to update the flag fails with a “Missing reference” error: the system expects a EcoResCatalogControl record to exist already. Dead end.

Electronic reporting: the last resort

Since the table browser is not an option anymore in a sandbox or production environment, the number of tools available to the consultant reduces to just one: Electronic reporting. Despite the name, the Electronic reporting module is able not only to read, but also freely insert, update or delete voluntary tables in D365FO.
In essence, the idea is to make a CSV text file and import it into the EcoResCatalogControl with the help of the Electronic reporting (ER).

This requires a so-called Mapping to destination where the “destination” is one or many tables or entities in D365FO. The concept is nicely outlined in the blog of Mr. Ties Philippi. In total, 4 “components” are required in at least 2 ER configurations (the Mapping to destination may in theory be detached into a separate – third – configuration).

Model
Model mapping “To destination”
[CSV file] Format
Model mapping [from format] ”To model”

The execution flows in the order (3) -> (4) -> (1) -> (2).
The 2 configurations I used can be downloaded here.

First, create a model, for the simplification its structure may follow exactly the table.

Next, create a mapping with the Direction = To destination. Add the target table to the right with the button Destination, then bind the model from the left to the table at the right side of the Model mapping designer. Change the status of the configuration to Complete.

Next, create a format configuration based on the model. It should describe the CSV file structure, namely a set of records (Lines) separated by CRLF, with 2 fields in every line, separated by the semicolon “;” (in a German version of the Excel; in an English version the comma “,” is more appropriate). The encoding of the CSV format should better be UTF-8 (Excel is able to produce UTF-8 encoded files).

Do not bind the format to the model, but use the button Map format to model to create a mapping of the same name. Bind the format to the model as shown on the screenshot below. The button Run can be used to test the mapping ad hoc: it takes a CSV file, interprets it according to the format definition, translates to the model internal container and exports this container to an XML file.

Once tested, update the format status to Completed. Make note that the import is started from the artefact (2) Model mapping to destination (with the button Run). The system looks for a format based on the model the mapping belongs to, and inside of that format it looks for a “To model” mapping.

Having found an appropriate format, the system asks for a CSV file and unleashes the import, there is no way back.

Update route

Added on 31.01.2020
Electronic reporting destination mappings can also be used to launch X++ code. If you have ever worked with production routes, you may have noted the Update route button. The class behind is called RouteUpdate and it is executed by the system automatically from the UI on any change in the order of operations. On plain production routes, it updates the Next operation numbers, and multiplies the variable scrap percentages to cache and store accumulated scrap factors. The problem is, this class cannot be run over all routes in the batch mode; in the Route headers entity they forgot to use it. Opening every single route out of thousands imported is obviously not an option.

I took the same CSV file and created a new destination mapping which updates the RouteTable. One column in the CSV file (Attribute) contains the key RouteId, the other (Category) bears the route name. I am updating the name by the key, but in the middle I am injecting the following code
RouteSearch.newRouteId(@.Attribute).routeId
like this
LEFT(RouteSearch.newRouteId(@.Attribute).routeId,0)&@.Category
It instantiates and executes the RouteSearch class which as a by-product calls RouteUpdate in its constructor method, then gives a routeId() value back which I neglect. Thousands of routes are updated blazing fast, and the Next operation and the Accumulated scrap is set everywhere:

The ER configuration can be downloaded here: RouteUpdateMapping.zip.

Open project milestones

Added on 16.04.2020
Finally, I have designed a data model and mappings for the fixed fee project On-account transactions aka Billing milestones. The configuration may take a project TransId from the CSV file, or it may create its own from the standard number sequence in the Project module parameters. Both the related tables ProjOnAccTrans + ProjOnAccTransSale are generated.

The ER configuration can be downloaded here: ProjOnAccTransModel.zip
Fun fact: a DMF entity for this existed in AX2012, but not D365.

Epilogue

The above crude trick works, but it is dangerous. Fixing a broken import might be a challenge, because to delete the data and start all over you need to change the destination Record action to Delete and then hope to find the right record by the primary key from the CSV file.

I would love to use standard entities, and make Microsoft add them to the application. Please vote for my 2 “favourites”:

the management of EcoResCatalogControl in an entity and…
the open (not invoiced) project milestones ProjOnAccTrans + ProjOnAccTransSale.

Electronic reporting blog series

Minimalistic EU VAT Configuration in Dynamics 365

Introduction

Let me present my 4th iteration of the EU VAT setup in Dynamics. The below concise VAT configuration in Dynamics 365 for Finance has been tested over 3 years of my operations. It has been constantly updated with the changes in taxation. It covers intra-community import, export and import of services within the European Union and beyond, domestic supplies, business travel within the EU and abroad.

Before we begin with the setup let me explain some background facts and assumptions:

- Any export of goods or services in or outside of the European Community is tax-free, but reported
- An export of services is special: in accordance with Directive 2008/8/EC any service rendered for customers abroad is subject to reverse charge
- In the case of an intra-community (IC) delivery, goods and services are presented separately on the EU Sales List
- On the contrary, goods and services delivered in the home country are reported together and should not be distinguished
- Intra-community deliveries are reported separately from the foreign trade with the so-called 3rd countries (i.e. countries which are not the 28 members of the EU, e.g. Norway or Switzerland). IC trade appears of the EU Sales list, while 3rd country trade does not. Deliveries of tangible goods are reported to the INTRASTAT in addition.
- Most of the countries apply a full rate and several reduced rates. The reduced rates are there mostly for the basic consumer services and goods (Austria: 10%). They normally do not affect enterprises, until the employees start reporting travel expenses.
- A semi-reduced “hotel” rate of 13% stands out in Austria, but also in Switzerland and France for accommodation. Update 2018: 13% were reverted back to 10% in Austria, but I am going to keep it in my scheme.
- A grocery may have separate tax codes for an IC acquisition of Polish potatoes (self-assessed 10%) or French wines (self-assessed 13%). However, the reduced-% goods and services are rarely imported by manufacturing or professional service businesses, with the exception of foreign books and printed media (self-assessed 10%).
- In Switzerland, the VAT receivable from investments is reported separately from the VAT received from current assets or services (de-ch: “Vorsteuer auf Investitionen und übrigem Betriebsaufwand” vs “Vorsteuer auf Material- und Dienstleistungsaufwand“) despite the same rate.
  Since 2019, a similar aspect has become valid in Austria too. The mandatory Chamber of Commerce contribution (de-at: “Kammerumlage 1” or KU1) is evaluated at 0,29% of the total VAT receivable excluding any investments i.e. assets acquired either domestically or abroad.
  The reduced VAT (foodstuffs, pharmaceuticals) may be neglected: milk is unlikely to become a long-term asset, unless condensed 😉
- From the system point of view, there is a difference between a “zero rate” and “no rate”. A tax code with a 0,00 rate still logs the tax base for reporting, as long as a record in the tax Values table exists.
  From the fiscal standpoint, there is a difference between a “zero” an “no” rate too. For example, in the UK public transportation is taxable at 0% which is considered a tax rate of its own. In France, 0% is applied to newspapers. To enable a zero rate, just open the Values table in connection with the tax code, let the system create a record, leave the Value = 0,00000 and save the record.

VAT codes (en-us: Sales tax codes)

Tax code	Name	Rate AT	Example
doF	Domestic VAT, full	20%	Sales or purchase of regular goods and services, e.g. raw materials, office consumables, car expenses (electric cars or ‘fiscal trucks’ only)
doA	Domestic VAT from assets, full	20%	Investments in long-term assets, i.e. machines, office equipment, electric car fleet
doH	Reduced “hotel” rate	10%	This reduced rate applies to accommodation, but not restaurant bills
doR	Domestic VAT, reduced	10%	Public transportation, taxi, and basic foodstuffs, including restaurant bills with non-alcoholic beverages, books and newspapers; in Germany it is slightly different: dishes served in a restaurant are charged with the full tax rate, while the ‘take away’ foodstuff is reduced
TF	Tax free Out of scope	0%	Exempt international air and sea transportation, or services delivered by “non-genuine” tax exempt suppliers such as insurances, postal services (de-at: “unechte Steuerbefreiung“), but also the City tax (de-at: “Tourismusabgabe“, “Ortstaxe“) component on hotel bills. The base on purchases may still be reported to the authorities.
NR	Not recoverable	–	On a travel abroad, services consumed by the employee are taxable per se, and within the European Community the tax may even be recovered, but very few companies do so. The code is not reported to the authorities. Running expenses from traditional personal cars with internal combustion engines fall into this category too.
euF	IC export IC acquisition	0% 20%	Delivery of goods to another country of the European Community is tax free, but the value of the goods is reported to the tax authority. On a regular import of goods from another member of the European Community the full domestic tax is levied; the tax is self-assessed with a zero effective tax load (what you pay is what you get recovered). This is achieved with the Use tax (en-us) i.e. Import VAT / purchase VAT (en-gb) setting in the VAT group.
euA	IC asset export IC asset acq.	0% 20%	It is highly likely, that the investments into long-term assets (machinery, other equipment) flow into another EU country. Less probably but also possible, is that used assets are sold to an EU neighbour.
euR	Reduced IC export IC acquisition	0% 10%	Delivery of goods to another country of the European Community is tax free, but the value of the goods is reported to the tax authority. On goods that would be subject to the reduced tax rate domestically (e.g. books), the same reduced tax rate is applied when procuring such goods from another member of the EU; the tax is self-assessed with a zero effective tax load and the Use tax setting in the VAT group.
euS	IC services export IC services import	0% 20%	Delivery of services in another country of the European Community is tax free but reported. The buyer, however, must obey the reverse charge principle. The setup for the reverse charge and the zero tax impact is similar to the above IC goods acquisition.
exF	Export 3rd county Import 3rd country	0% ~20%	The export of goods is tax free in most of the countries, but the value has to be reported. Tangible goods imported into the EU from a 3rd country is subject to an import tax, whose base is hard to calculate because it includes the portion of insurance and freight up until the border. Normally the import tax is calculated and paid by the customs broker; in the exotic import tax self-assessment mode (de-at: “Einfuhrumsatzsteuer geschuldet”) this exF tax code may be used to post the tax.
exS	Services export 3rd Services import 3rd	0% 20%	If the place of supply of services is outside of the EC, the export is tax-free but still have to be reported (as taxable elsewhere under the reverse charge regime). The procurement of services outside of the EC is subject to a self-assessed reverse charge.

VAT groups (en-us: Sales tax groups)

In every business case in Dynamics 365 for Finance, the tax code is deducted by the system from an intersection between the customer/supplier VAT group and the product Item VAT group. Aside of the basic “F”, “S”, “TF” item VAT groups I recommend “H”, “Food” and “PubT” for the travel expense recording. The reason to separate the latter 2 groups is my reverence to companies holding multiple VAT identification numbers (UIDs) in different EU countries: tax rates for these categories vary across Europe.

It is a good practice to have a valid tax code, i.e. a valid Customer/Item group combination in every business case whenever it is present on the tax declaration or not. This is enforced by the tax parameter Check tax code. For example, on a business trip from Germany to Hungary the VAT may theoretically be recovered, but hardly any company does it. One way is omit the tax completely, but to stay compliant to the Check tax code setting, the tax code NR has been introduced. The respective VAT group AP-NR may be then assigned to the country HUN in the Travel and Expense module, and any foreign Hungarian VAT will be neglected yet formally recorded on the tax code NR.

Any business case we do not anticipate or consider a human mistake is going to result in a missing VAT code, e.g. an attempt to sell milk by a consulting company, or a foreign trade operation in a society without an EORI number. With the strong Check tax code setting such an attempt is going to result in an error and roll the transaction back, preventing havoc in the ledgers.

Customer or supplier VAT group	Item VAT group “F” (full)	Item VAT group “A” (assets)	Item VAT group “S“ (services)	Item VAT group “TF“ (tax free)	Item VAT group “H” (hotel)	Item VAT group “Food“	Item VAT group “PubT“ (public transp.)
*Suppliers*
AP-DO	doF	doA	doF	TF*	doH	doR	doR
AP-EU	euF**	euA	euS	TF	NR	euR	NR
AP-EX	exF		exS	TF	NR	NR	NR
AP-NR	NR	NR	NR	NR	NR	NR	NR
*Customers*
AR-DO	doF	doA	doF	TF
AR-EU	euF	euA	euS	TF
AR-EX	exF		exS	TF

*) Tax codes to be marked Exempt in the VAT group configuration are stroke through,

**) and those marked as a Use tax are in italic.

Disclaimer

The above configuration is going to work well in the D-A-CH countries, in Scandinavia and even in Spain. Yet it doesn’t account for the “VAT on payment” aka Conditional tax common in France (de: “IST-Besteuerung“), and it will probably not work for countries with inflated reporting requirements (Italy). The Conditional tax may however become highly relevant if the customer is required to pay a significant advance payment. That case is treated in the blog Advance payment invoices in Fixed fee projects in D365, D-A-CH style.

Blog series on VAT in the EU

Accrued revenue (Revenue recognition)

Introduction

Let’s build upon the yesterday’s business case. A guarantee, a maintenance, an insurance, a rental service is backed by a contract and span over a period of time, as opposed to a single delivery of goods. If the service is pre-paid at the beginning of the contract period, then the revenue should be allocated over time. This is known as

EN: Accrued revenue or deferred revenue/income
DE: Passive Rechnungsabgrenzung
RU: Доходы будущих периодов
FR: Régulation passif

Setup

Once the Revenue recognition module is activated, there is a bit of configuration to be done.

First of all, a general journal (General ledger > Journal setup > Journal names or Revenue recognition > Setup > Journal names) of the novel type Revenue recognition must be created. It is used to preview the periodic (monthly) revenue recognition before posting. The journal name is then selected as a default Revenue recognition journal name in General ledger parameters (this seems to be an innovation relative to the original Armanino module).

Next, the Item group of the service must be provided (Revenue recognition > Setup > Inventory and product setup > Posting) at least one additional account: Deferred revenue. It is a liability (we owe something to the customer for a service not yet rendered in full), a passive balance account.

In the case of a back-to-back contract if we are reselling a 3rd party [financial] service to the customer, the service has a cost price. It must be spread over time along with the revenue. This can be reflected by buying the service item ‘to the stock’ and treating it as a tangible item. The sales order produces COGS, and to accrue this expense we may need a separate account Deferred cost of goods sold. It is an asset (the services supplier owes something to us), an active balance account.

Finally, a Revenue recognition > Setup > Revenue schedules must be created:

Occurrences: the number of periods to allocate;
Recognition basis: the revenue may be allocated to calendar periods equally (1/12, 1/12, 1/12, 1/12, …) or in the proportion of the days in the month (31/365, 28/365, 31/365, 30/365…), or non-uniformly (then the allocation percentages are entered by the user in the Revenue schedule details);
Recognition convention: when to start recognizing. Sadly, if you choose Actual start date, and the service was billed on the 3rd etc. of the month, the revenue is going to be recognized on every 3rd on the following months;
Auto hold: any new revenue recognition records are put on hold and must be manually unlocked before taken over into the revenue GL journal;
Automatic contract terms: apparently, this option did not exist in the original Armanino ISV module. It pre-populates the start and the end date of contract in the sales order line, based on the number of occurrences and the length of the period.

If the service always brings about deferred revenue, the revenue schedule may be assigned to the Released product on the Revenue recognition tab:

The selected revenue schedule is then automatically applied to every new sales order line with this item. It works even with bundle components. Furthermore, a default Revenue schedule may be assigned to the whole item group to pre-populate the items.

Process

The service item is added to a service item. Pay attention to the Revenue schedule column far to the right:

It is pre-populated with the default Revenue schedule from the item master, or it may be amended / entered ad-hoc by the user. Next, the begin and the end of the contract must be specified in the Contract terms field group. With the Automatic contract terms on, the terms are evaluated automatically, starting from the day of today.

The allocation may be pre-viewed: check the Expected revenue recognition schedule on the Manage button ribbon. The expected revenue is persistent and rebuilt on every sales order confirmation.

Now deliver and bill the sales order. The revenue (and COGS) are posted not into the P&L but the BS accounts chosen before. Any due revenue from the current and the past periods (imagine the service was billed in the middle of the contractual period) is “caught up” with the first periodical revenue recognition.

A Revenue recognition schedule has been set up for the sales order (Manage ribbon or the Revenue recognition > Inquiries and reports > Revenue recognition inquiry). The financial dimensions in the schedule are retrieved from the sales order header and lines. The percentages are taken from the revenue schedule details.

Once a month the user should check the Unprocessed revenue recognition schedules (workspace Revenue recognition > Workspaces > Revenue management) and apply the action Create journal:

The system is going to pick up any outstanding revenue records up to the As of date (i.e. catching up the revenue if the service was billed in the middle of the contractual period) and produce a GL journal with both the revenue and the COGS.

This journal (Revenue recognition > Journal entries > Revenue recognition journals) is left for approval and posting by the accounting department.

Conclusion

In the past, one of the few options to get an Accrued revenue was to use the straight-line revenue recognition mode in the Project accounting module, but the Revenue recognition module now offers a better, user-friendly solution that supports sales orders.
In the future, this offer may be rounded up by the Deferrals, providing a comparable user experience for the Accrued expenses.

Monthly Archives: January 2020

Electronic reporting for data migration

Electronic reporting for data migration

Intro

Electronic reporting: the last resort

Update route

Open project milestones

Epilogue

Electronic reporting blog series

Minimalistic EU VAT Configuration in Dynamics 365

Minimalistic EU VAT Configuration in Dynamics 365

Introduction

VAT codes (en-us: Sales tax codes)

VAT groups (en-us: Sales tax groups)

Disclaimer

Blog series on VAT in the EU

Accrued revenue (Revenue recognition)

Accrued revenue (Revenue recognition)

Introduction

Setup

Process

Conclusion

Revenue recognition and deferred cost blog series