Electronic Reporting (ER) Cookbook

The Electronic Reporting module in Dynamics 365 for Operations, previously known as General Electronic Reporting (GER) was one of the coolest application inventions in Dynamics AX7. Developed apparently in my alma mater – the Dynamics development office in Moscow – this “ER” is a surprisingly powerful tool with an own in-built programming language for transformations. A consultant may configure any vendor payment format or any moderately complex GL export (such as the German GDPdU or French FEC) within days with literally no customizations, and test it ad hoc.

Over the last year, I implemented the British BACS18, the Swiss DTA, the Spanish SEPA sub-format and the North American ACH outbound payment format. While the AX wiki was a good start, the best way to learn the tool is practice. Below are some transformation code snippets that may help you along the steep learning curve.

Line counters

In the payment files, there are often record numbers. The ER element “Counter” is intended to be used, but it is incremented on every occurrence in the format. I.e. the counter has a global context, and the same count may not be used more than once in a row.
A total of records in a footer section is a special case: you may enumerate the lines with a Counter, then use a function of the Group by type over the data source to get a total record count, see the screenshot.

With 2 concurrent counters you have no other choice but to use the [real numeric] journal line number, and convert it into an integer:

NUMBERFORMAT(VALUE(model.Payments.PaymentIdentifications.SourceBusinessEntityDescription), "00000000000")
Beware of the major handicap of this trick: deleting payment journal lines creates holes in the numeration.

Simple transformation formula examples

Convert an amount into an amount with fixed 2 decimal places after the comma
NUMBERFORMAT(model.Payments.InstructedAmount, "#.00")
Remove the decimal separator, produce an amount in pence / cents, implemented as a subroutine (“transformation”) with a numeric parameter
REPLACE(NUMBERFORMAT(parameter,"0.00"), ".", "", false)
Remove line breaks from the postal address, and truncate to 70 characters
LEFT(REPLACE(model.Payments.Debtor.PostalAddress.AddressLine,"\n"," ",true),70)
Remove all non-numeric characters with a regular expression
REPLACE(model.Payments.DebtorAccount.Identification.Number, "[^0-9]", "", true)

Calculate a weighted checksum

The below expression adds the 9th checksum digit to a US ABA routing number, should the user only have entered the 8 significant digits. The RIGHT(NUMBERFORMAT(10 – VALUE(), “#”), 1) is a crude implementation of modulo 10.
CASE(LEN(parameter), 9, parameter, 8, parameter&RIGHT(NUMBERFORMAT(10-VALUE(RIGHT(NUMBERFORMAT( (VALUE(MID(parameter,1,1))+VALUE(MID(parameter,4,1)))+VALUE(MID(parameter,7,1)))*3+ (VALUE(MID(parameter,2,1))+VALUE(MID(parameter,5,1)))+VALUE(MID(parameter,8,1)))*7+ VALUE(MID(parameter,3,1))+VALUE(MID(parameter,6,1))), "#"), 1)), "#"), 1), RIGHT(parameter, 9))

Day number in a year (1-366)

The British BACS18 format contained a date format that really made me suffer: for the 1st of February 2016 it expected “ 16032” where 32 was the day count from the 1st of January. The below code took me a while; it used the Gregorian calendar formula and predated the fact that if you start counting months from March, the length of the months oscillates 31-30-31…
The repetitive IF(…) is an implementation of (month + 9) mod 12.
(VALUE(DATEFORMAT(parameter, "yyyy"))- ROUNDDOWN(IF(VALUE(DATEFORMAT(parameter, "MM")) >2, VALUE(DATEFORMAT(parameter, "MM")) -3, VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0) )*365 +ROUNDDOWN( (VALUE(DATEFORMAT(parameter, "yyyy"))- ROUNDDOWN(IF(VALUE(DATEFORMAT(parameter, "MM")) >2, VALUE(DATEFORMAT(parameter, "MM")) -3, VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0) )/4, 0) +ROUNDDOWN( (IF(VALUE(DATEFORMAT(parameter, "MM")) >2, VALUE(DATEFORMAT(parameter, "MM")) -3, VALUE(DATEFORMAT(parameter, "MM")) +9) * 306 + 5)/10, 0) +VALUE(DATEFORMAT(parameter, "dd")) - 1 -( (VALUE(DATEFORMAT(parameter, "yyyy")) - 1) * 365 + ROUNDDOWN( (VALUE(DATEFORMAT(parameter, "yyyy")) -1)/4, 0) + 305)
This function should work correctly for the next 83 years. We IT people think in shorter terms than the Roman Catholic Church.

Update 02.05.2017: the November 2016 update to Dynamics 365 for Operations features an undocumented function DAYOFYEAR, and the above code reduces to just
DAYOFYEAR(parameter)

Data model

To master the file format, you must first learn the data model, the ER’s configurable abstraction layer over the Dynamics database. Outbound vendor payments use the Payment model.

It provides generic payment attributes,

Attribute	Model node
Payment date (user parm or journal date)	model.Payments.RequestedExecutionDate
Amount	model.Payments.InstructedAmount
ISO payment currency (journal line currency)	model.Payments.Currency
Journal line number (see Line counters)	model.Payments.PaymentIdentifications.SourceBusinessEntityDescription
Payment / Note	model.Payments.PaymentsNotes
Voucher+RecId (a unique line ID)	model.Payments.PaymentIdentifications.EndToEndIdentification
Date of today	model.CreationDateTime

our bank details,

Attribute	Model node
Our name (company name)	model.Payments.Debtor.Name
Our full address (company address)	model.Payments.Debtor.PostalAddress.AddressLine
Name of our bank	model.Payments.DebtorAgent.Name
Our bank account number	model.Payments.DebtorAccount.Identification.Number
Our IBAN	model.Payments.DebtorAccount.Identification.IBAN
Routing number of our bank	model.Payments.DebtorAgent.RoutingNumber
BIC of our bank	model.Payments.DebtorAgent.BICFI

and their (beneficiary) bank details:

Attribute	Model node
Their name (vendor’s name)	model.Payments.Creditor.Name
Their full address (vendor’s payment address)	model.Payments.Creditor.PostalAddress.AddressLine
Name of their bank	model.Payments.CreditorAgent.Name
Their bank account number	model.Payments.CreditorAccount.Identification.Number
Their IBAN	model.Payments.CreditorAccount.Identification.IBAN
Routing number of their bank	model.Payments.CreditorAgent.RoutingNumber
BIC of their bank	model.Payments.CreditorAgent.BICFI

Electronic reporting blog series

Austrian VAT declaration / Umsatzsteuervoranmeldung 2020

Following up on my latest blog, let me continue with the specific setup for the VAT return in Austria. This electronic VAT declaration called Umsatzsteuervoranmeldung “UVA” must be uploaded monthly or quarterly (depending on the company’s turnover) in a proprietary XML format to the web site FinanzOnline of BMF, the Austrian Ministry of Finance. The interface is well maintained by the DACH support team for Dynamics, but there was nowhere on the Internet a description of the barebone setup. Well, up until now.

The only documentation is the class TaxReport_ReportAT, which is used by the periodic function VAT payment in the Tax module to produce a “UVA” declaration, both printed and the electronic. I reverse engineered it, and it should be noted that

It requires a non-continuous number sequence XML VAT package number to form a message ID.
The printed PDF version is not accepted by the ministry anymore, but it is quite useful to visualize and check the VAT return prior to the booking. The latest template required by the class can be downloaded here.
The printed form expects an exotic address format of the legal entity primary address, one with a separate house number Street number.
The tax number (not to mix with the European VAT (UID)-number) MUST be present in the legal entity, Tax registration number field in the format xxx/yyyy-ZZ, where ZZ is the number of the BMF’s local office. The same can be seen without dashes and slashes on FinanzOnline.
The class uses a set of hardcoded 4 digit codes to map tax codes to cells on the tax return. There are cells (and the respective XML elements) for a total tax base per tax period per VAT rate, or a tax amount per tax period per VAT rate.

Below are the most relevant VAT return cell codes. For more information on the individual VAT return cells, please refer to the comprehensive description (de-at) on the BMF site.
You should add the 16 codes to the table VAT reporting codes with the Report layout = Austrian report layout.

Reporting code	Report text	Brief description
1122	Gesamt Umsatzsteuer: 20% Normalsteuersatz	Total sales VAT: full 20%
1022	Gesamt Bemessungsgrundlage: 20% Normalsteuersatz	Total sales base: full 20%
1011	Bemessungsgrundlage: Ausfuhrlieferungen § 6 Abs. 1 Z 1 iVm § 7	Sales base: Export 3rd country tangible
1021	Bemessungsgrundlage: Steuerschuldübergang § 19, grenzüberschreitende Leistungen	Sales base: Reverse charge incl. export of services IC, 3rd
1020	Bemessungsgrundlage: übrige steuerfreie Umsätze ohne Vorsteuerabzug	Sales base: Other exempt sales
1017	Bemessungsgrundlage: IgL Art. 6 Abs. 1 ohne Fahrz	Sales base: IC delivery
1160	Vorsteuer ohne…	Total purchase VAT except for…
1157	Steuerschuldübergang bei Bezug gemäß § 19 Abs. 1 zweiter Satz, § 19 Abs. 1c	IC purchase payable VAT: Reverse charge on service acquis
1166	Vorsteuer: Steuerschuldübergang gemäß §19, grenzüberschreitende Leistungen	IC purchase receivable VAT: reverse charge on service acquis
1183	Vorsteuer: Einfuhrumsatzsteuer geschuldet (§ 12 Abs. 1 Z 2 lit. b)	Purchase 3rd country import VAT (payable)
1172	IgE Steuerschuld: 20% Normalsteuersatz	IC purchase payable VAT: full 20%
1072	IgE Bemessungsgrundlage: 20% Normalsteuersatz	IC purchase payable base: full 20%
1106	Gesamt Umsatzsteuer: 13% ermäßigter Steuersatz	Total sales VAT: reduced 13% (agro B2C)
1006	Gesamt Bemessungsgrundlage: 13% ermäßigter Steuersatz	Total sales base: reduced 13% (agro B2C)
1129	Gesamt Umsatzsteuer: 10% ermäßigter Steuersatz	Total sales VAT: reduced 10% (foodstuffs, print media)
1029	Gesamt Bemessungsgrundlage: 10% ermäßigter Steuersatz	Total sales base: reduced 10% (foodstuffs, print media)

Finally, the above 16 reporting cells are assigned to my 12 VAT codes in the VAT codes table:

Report setup

exF

exS

euF

euA

euR

euS

doF

doA

doH

doR

Taxable sales

1022

1006

1029

Duty-free sale

1011

1021

1017

1021

1020

VAT payable

1122

1106

1129

Taxable purchases

Duty-free purchase

VAT receivable

1160

Taxable import

1072

1073

Offset taxable import

Import VAT/ purchase VAT

1183

1172

1173

1157

Offset import VAT/ purchase VAT

1166

(EU sales list excluded)

yes

(Country type)

3rd

Dom

The Report setup – credit note of the VAT code records is identical.

The full list of the Austrian VAT reporting codes can be downloaded here. The not relevant codes are marked “N/A”. My criteria for the relevance for a typical manufacturing or a service business were:

Reselling of cars, scrap, gas, electricity, CO2 certificates are ‘exotic’ businesses, as well as seafaring, trucking, taxi services, real estate agencies;
Small businesses below the 100 000 EUR turnover threshold would never use Dynamics AX either;
The Jungholz and Mittelberg exclave valleys with the German VAT are too small to get noticed;
It is not practical to apply a dedicated tax code to goods intended for the self-consumption by the company owner: such goods do not reduce the income/corporate tax base either and should better not appear in the accounting at all, or be simply sold to the owner via a sales order as everything else.

Happy tax paying!

Monthly Archives: April 2017