XML Intrastat message for older Navision Dynamics Financials

Nav123: Navision, Showare, OrderApp

Print Friendly, PDF & Email

Since January 2022, the Federal Statistical Office has required the Intrastat declaration to be submitted in XML format. Submitting the old CSV format is now no longer possible, not even via the back door. Is your Navision, Navision Dynamics or Business Central also able to issue current Intrastat statistics on foreign trade?

Yes!

Even if your dealer tells you that your Navision and the first versions of Business Central are "too old": This is of course nonsense. Your "old" Navision 2009R2, 2005, 2.01b, in principle everything including to DOS Navision 3.53/3.56euro, can of course create INSTAT/XML Intrastat messages. Never allow yourself to be told that "your" Navision belongs to the scrap heap!

And we can teach your Navision the Intrastat XML from 2022 even if you can no longer activate new objects, i.e. if you are already out of maintenance!

Note: All screenshots are from "old" Navision versions 2.01 to 2009 R2, the customization can also be built into your RTC Navision 2013, 2015, 2016 (do you really use it?), 2017. As of 2018 there is finally a solution from Microsoft (CU4 and up).

Preparation

My solution assumes that you already have the correct data in your system, i.e. federal states, customs tariff numbers, sales tax numbers of your customers and suppliers are already correctly maintained.

Be careful! The solution (like all my solutions) is explicitly not offered for the company Landefeld Druckluft & Pneumatik in Kassel or any directly associated sub-companies ("organizations"). A transfer of my adjustments is generally not permitted, the copyright remains with me as usual. Non-negotiable legal steps would be taken if the data were passed on to the Landefeld Druckluft & Pneumatik company in Kassel.

Requirements for XML Instrastat INSTAT in your Navision/Financials

Your country table must either already be structured according to ISO-3166-1 ALPHA-2 (e.g. country code Austria = AT), or have this 2-digit code in an additional column. In Navision, approximately starting from version 3, there are e.g. the columns EU country code or Intrastat code. In newer versions there is the explicit column ISO 3166 country code. If these country codes are not yet included in your country table, you can enter them in advance, or I will do this for you.

Your number ranges must be unique because the standard Intrastat only stores one document number. With this, my solution attempts to determine all fields required for the new XML Intrastat, such as sales tax ID and type of transaction. If your number ranges for

  • Purchase Invoice
  • Purchase Credits
  • Sales Invoices
  • Sales Credits

are not clear, there are other ways to determine this data, this adjustment will then be made at time and effort. A receipt number = a receipt -> unique. One document number = several different documents -> not unique.

Please clarify in advance (possibly in dialogue with me): What should happen to lines that have no or an invalid (00000000) customs tariff number (commodity number)? Or if positions are sold for 0 euros? This would result in complaints about the created Intrastat XML file from the Federal Statistical Office.

By default I delete these lines, as they were used in most projects for e.g. postage or services, montages.

I can also stop at these lines with an error message if I want.

What happens to rows that don't have a set? In the standard, these are not even transferred to the Intrastat journal. These are usually the consequences of an adjustment, e.g. in the case of food vision by mode.

In my default setting, these lines are also simply deleted, since they cannot represent any value.

Please also apply for a material number at https://www-idev.destatis.de/idev/OnlineMeldung.

You can find additional information about registration here: https://www-idev.destatis.de/idev/doc/intra/hilfe1_3.html

After registration, you must first issue a test file with the new interface from your Navision or Navision Financials, this is done with the material number XGTEST.

You will only receive your final material number after checking the test file. Of course I will support you in this process. The first test file is included in the price, all further support will be charged at cost.

XML capabilities of your Navision/Financials/Business Central

Depending on the Navision version, XML capability varies from "Has heard something about it before" (2.01, 3.60 etc) to "Speaks XML fluently" (e.g. from the 2005 (5er) version of Financials.)

In order not to have to maintain half a dozen “Navision Intrastat XML” program versions, the entire XML structure is created in…let's say…unorthodox way. The advantage is that the core programs for the XML output have to be adapted very little for the different Navision, Financials and Dynamics versions.

To simplify programming and operation, some constants were also stored directly in the program code, e.g. the partner identification of the Federal Office is 00.
This has turned out to be very practical. Here, too, you can of course proceed differently if you wish.

ISO-8859-1

The older Navision versions, in particular, are somewhat "strange" with the Latin code page. Therefore, in text fields I only convert the most important characters (ÄÖÜäöüß) from the Navision OEM (something like CP 852) character stream, all other characters above ASCII-128 are removed from the data stream.

Instructions

Setup

After the adjustments made by me you will find some new fields in your Navision. These must now be set up correctly.

You need to apply for a new material number for the XML notification, see the relevant links above in the introduction.

Please enter the newly assigned material number in your company data, Intrastat page, "Sales material number" field. I have already entered XGTEST here for the test operation.

A major change comes in the redefinition of the definition of 'type of transaction'.

Here it is common practice in most companies to enter 11 (final purchase/sale) in both the import declaration and the export declaration. Adjustments have been made in many Navision solutions so that exactly this 11 is always entered in the Intrastat journal and thus reported.

If you look at it very closely and benevolently, sales from end customers are more likely to be business type 12 {(billed) inspection or sample shipments, shipments with right of return and consignment business (including consignment stock)}.

Since 2022, this has been defined more clearly by a directive: purchases and sales to companies (B2B business) are to be reported as "type of business" 11 , purchases and sales to/from end customers are to be reported as "type of business" 12 (B2C).

Depending on the company, there are mainly end customers (B2C = 12) or business customers (B2B = 11) in sales (probably less often in purchasing).

In order to keep the maintenance effort low, you now have the following setup option in the company data:

Vorbelegung der Intrast für Art des Geschäfts 11 (B2B) oder 12 (B2C)
Preassignment of Intrast for type of transaction 11 (B2B) or 12 (B2C). You can then store a separate transaction type for each customer or supplier. You should now deactivate older program logic that generally entered 11. If you don't have access to it (anymore), I can also ignore this via customization.

Generating a monthly report

You create the Intrastat declaration as before using your Intrastat journal. To do this, you create a new journal.

Recommendation: Name it YYYY-MM, e.g. 2022-01 for January in 2022. This way your journals will be neatly arranged chronologically one below the other.

You also continue to fill out the statistics period as before YYMM, e.g. 2201 for January 2022.

Screenshot aus Navision für die Anlage eines Buchungsblattes für die XML Intrastatausgabe in Dynamics und Financials.
Screenshot from Navision for journals for XML Intrastat output in Navision Dynamics and Financials.

As before, Navision fills your booking sheet with the purchase and sales items for the specified statistical period via “Get items”.

Beispiel in Navision für das Holen von Artikelposten einer gegebenen Periode für die neue XML Intrastatmeldung.
Retrieve item entries in Navision for a period with the new XML Intrastat declaration.

You generate the new XML file according to the INSTAT specifications (Intrastat XML) using the new "Create XML file" function:

Please upload the first file, still marked as a test file, e.g. with the file name XGTEST, using your usual route to the federal authority.

As soon as you receive the OK and your new material number from there, please enter this material number in the Navision company data in the Sales material number field. The field Purchase material number no longer has any meaning in your Navision/Dynamics/Financials

With the function XML-file generation your Navision Dynamics starts 2 processes:

In the first process, the data in the Intrastat journal is enriched/completed. The customer/creditor number is entered, the sales tax ID, the type of transaction...
To do this, Navision or Business Central fetches the data directly from the linked business process in the first attempt, e.g. to fetch the sales tax ID directly from the invoice/credit note for various customers.

Your Navision or Business Central then tries in various other ways to collect this necessary data. Once the data has been found, it is no longer touched, since the results are becoming more and more general and less and less specific.

Each level of enrichment is secured! In this way, you can then simply correct incomplete data directly in the journal or via Navigate directly in the document after the corresponding error message, and start the output again.

Following the enrichment, Navision/Business Central/Dynamics/Financials then outputs the completed Intrastat journal to a file.

You can specify the path in the company master data, the file name is specified by the definitions of the INSTAT file definition and must not be changed.

If data is still missing during this second run of the Intrastat XM file output, this is requested/complained about in plain text via an error message. You only need to enter the missing data and then start the export again.