German travel expenses in Dynamics 365 – Part 2

Confinued from German travel expenses in Dynamics 365 / Deutsche Reisekostenabrechnung 2022

Domestic business travel – Case 2 “3 days of training with hotel”

The 2nd case requires a receipt, a bill for the accommodation at a hotel. The VAT from domestic invoices may be recovered, and we need a country-specific setup for the VAT (…> Setup > General > Tax configurations) where the host country is associated with a tax group containing both full (19%) and reduced (7%) tax codes.

Set up an additional category of the type “Hotel”, assign the reduced 7% tax group to this category. Let the user enter a line against his category as an amount including tax, and select the Country/region = DEU. The domestic VAT is derived automatically by the system: €9,58.

The two lunches reduce the per diems by €22,40 down to €33,60 as required:

Domestic business travel – Case 3 “Hotel breakfast”

This time the hotel served Erika breakfasts. The breakfast is presented on the bill separately, since the accommodation and meals are subsidized through the reduced VAT of 7%, but beverages other than water are taxed at the full rate of 19%:

This requires an “Itemization” and at least 2 expense Subcategories under the main expense category Hotel. You can deactivate all standard subcategories but the Daily room rate, Restaurant and Loungebar and apply the full rate to the latter. The VAT amount by the “item” can be reviewed under Itemization details, the total VAT amount from the itemizations is not visible elsewhere.

If this is considered an unreasonable workload, you may abandon the itemization and simply edit the total VAT receivable in the Actual VAT amount field.

The entertainment (de: Bewirtung) may be dealt with similarly: 2 subcategories or even 3, as the tips (de: Trinkgeld) are VAT free. A mandatory list of guests may be enforced with a travel expense policy (Expense management > Setup > Policies > Expense report) where a submission to the workflow is prevented with an error message if the Number of persons entertained = 0 for any expense category of the Entertainment type:

Case 4 “Travelling abroad”

As we have learned already, daily allowances are country-specific. The reduced domestic rate for Germany is 50%, but the reduced foreign rates are close to 66% (but not exactly): here is an example for Switzerland, note the ratio:

	Full 24h day	“First/last day”	Ratio
Switzerland
– Geneva	€66	€44	66,67%
– the rest of Switzerland	€64	€43	67,19%

The reduced rates are rounded by the government to whole euros. To match the reduced foreign rate, I use a tailored percentage. It should approximate the daily rate with a deviation less than €0,01:
44/66 = 0,6666… => 66,00 * 66,67% = 44,0022 ~= 44,00
43/64 = 0,671875 => 64,00 * 67,19% = 43,0016 ~= 43,00

The respective setup of per diems and tiers per location is shown on the following screenshot:

Note that the rates are updated by the BMF every year and have to be regularly re-imported with validity dates. This is also true for the calculated percentage for first/last day.

The meals reduction and per diem calculation now caters for the rates in Geneva:

Switzerland does not belong to the EU, and their foreign VAT on the hotel bill is not recoverable, the selection of the right country code removes the VAT. No itemization is needed, the hotel bill may be entered as a lump sum in Swiss francs. The amount paid in Swiss francs is converted to euro at the intrinsic System exchange rate type, or at a user-defined rate.

Expense management blog series

German travel expenses in Dynamics 365 / Deutsche Reisekostenabrechnung 2022

German travel expenses in Dynamics 365 / Deutsche Reisekostenabrechnung 2022

Travel expense reporting in Germany may be implemented in Dynamics 365 for Finance with literally no customizations. A special attention must be paid to the per diem calculation.

Introduction

In general, per diem in Germany is an allowance for catering. Hotel bills are reimbursed in full. An employee of a German company – let’s call her Erika Mustermann – typically gets reimbursed for meals a fixed statutory amount per day. If she is paid above the legally prescribed daily rate, the difference would be subject to her income tax.

For domestic trips within Germany she becomes 28 Euro per day, yet this full rate is only applied from the second day on multi-day business trips. For the first and the last day Erika becomes only ½ of the daily ration = €14:

Length of the trip	Per diems rate 2020-2022
Less than 8 hours	0 EUR
More than 8, less than 24 hours	14 EUR
24 hours (a full calendar day)	28 EUR
First day (arrival) or last day (departure)	14 EUR

Erika is obliged to report every free meal sponsored by the employer or customer, reduce the daily allowance in accordance with the “20-40-40” rule:

Meal type	Reduction	Reduction amount
“Free” breakfast at the hotel	20%	-€5,60
Free lunch at work	40%	-€11,20
Dinner sponsored by the boss	40%	-€11,20

The statutory per diem rates vary by country and sometimes specific to a city. The rates are regularly updated by the federal government and rounded to full euros: https://factorialhr.de/blog/verpflegungspauschale-2022/. The “20-40-40” meal reduction rule also applies to destinations abroad, based on the full 24h rate.

In the following chapters 4 distinct realistic cases are explored, with the appropriate setup in Dynamics 365 for Finance:

	Narrative	Per diems calculation
Case 1 “One-day training”	Erika attended a daily training nearby and was absent from 9:00 till 19:00. One lunch was provided by the training centre.	One day €14,00 Reduction -€11,20 Grand total €2,80
Case 2 “3 days of training with hotel”	Same as before, but the business trip now took 3 days in total: from 18:00 on Monday whole Tuesday until 19:00 on Wednesday. This included 2 nights at a hotel without breakfast at €73,25. 2 lunches were served.	First day €14,00 Day between €28,00 Last day €14,00 Reduction -€11,202 = -€22,40 Total €33,60* 2 nights €73,252 = €146,50 Grand total €180,10*
Case 3 “Hotel breakfast”	Same as before: 3 days in total, but the hotel bill included 2 breakfasts. The breakfasts are separately shown: €9 inkl. VAT 7% (e.g. bread) and 19% (e.g. coffee) each: 2 lunches were consumed for free at the training center. In addition, Erika invited a potential customer to a restaurant.	First day €14,00 Day between €28,00 Last day €14,00 Reduction -€11,202 -€5,602= -€33,60 Total €22,40 2 nights €73,252 = €146,50 2 breakfasts €92 = €18,00 Hotel €164,50 Dinner €115,00 Grand total €301,90
Case 4 “Travelling abroad”	Erika went to Switzerland. On the same dates as above she flew to Geneva, resided 2 nights at the “President Wilson” and slowly consumed 2 exquisite breakfasts served on the Lac Leman shore.	First day €44,00 Day between €66,00 Last day €44,00 Reduction -€13,202 = -€26,40 Total €127,60* 2 nights 2770,0 CHF = 1440,00 CHF 2 breakfasts 245,00 CHF = 90,00 CHF 2 city tax 24,75 CHF = 9,50 CHF Hotel 1639,50 CHF ~= €1573,92*

Domestic business travel – Case 1 “One-day training”

Let’s start setting up the system for the Case 1. First of all, we require a shared expense category PerDiem of the type “Allowance”. This expense category is then selected under local legal entity expense categories (Expense management > Setup > General > Expense categories). In Germany the allowance excludes tax, i.e. the VAT may not be claimed:

The right per diem parameters (Expense management > Setup > General > Expense management parameters) for Germany can be read from the following screenshot:

In our second case the allowance is paid for every of the 3 calendar days and the Base per diem calculation on must be Calendar day with time. The Meal reductions are self-explaining. The First and last day defaults are used to model the reduced rate on the days of arrival and departure. The Per diem rounding = Normal rounding, because we don’t round up cents to whole euros.

The Calculate meal reduction by may be set to either Meal type per day and Meal type per trip, since the breakfast weights less than lunch/dinner and the meal reduction amount stays the same for the first/last/middle day.

Per diems locations shall be created next. Every leg in on the business trip has to be given a “location” to deduct the right allowance for that country or city. The rate per location is assigned here: Expense management> Setup > Calculations and codes > Per diems. The fist and the last day assume a reduced allowance, this can be modelled by means of the Per diem rate tiers:

The reduction (€14/€28 = 50%) is the same for the day of arrival and the day of departure, therefore the most concise setting is Apply to = Both. On an overnight stay, the 8 hours threshold is ignored: in the above examples 2-4 the first day is eligible despite it only lasted 24:00-18:00 = 6 hours. Therefore we may not set Minimum hours = 8; the threshold can be enforced differently with a customized “Expense report policy”, see below.

We now can test Case 1, but first make sure that the Meal reduction, Breakfast, Lunch, Dinner, Location fields are activated in the Expense management > Setup > General > Display fields.

In Expense management > My expenses > Expense reports, add a new report, choose the location, add a new line PerDiem. The UI automatically switches to the detailed mode where you can enter the start and the end date/time of your trip and choose the Per diem location:

On the Per diems tab, set Lunch = 1. The reduction of €11,20 is applied as expected, and the total allowance amount becomes €2,80. To post the expense report an approval workflow has to be in place.

Remember, a one-day trip less than 8 hours does not count as business travel. The per diem rate tiers cannot be used to enforce it, but here are 2 possible alternatives:

solve it organizationally, establish “human-driven” approval rules
make a small extension to the class TrvRuleExpressionSetup to enable the total trip duration, the start and the end date as parameters in the expense policy definition:

public FromDate parmPerDiemFromDate(CompanyId _companyId, TableId _tableId, RecId _recId) { TrvExpTrans trvExpTrans = TrvExpTrans::find(_recId); return DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(trvExpTrans.DateFrom, DateTimeUtil::getUserPreferredTimeZone())); } public ToDate parmPerDiemToDate(CompanyId _companyId, TableId _tableId, RecId _recId) { TrvExpTrans trvExpTrans = TrvExpTrans::find(_recId); return DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(trvExpTrans.DateTo, DateTimeUtil::getUserPreferredTimeZone())); } public Hours parmPerDiemHours(CompanyId _companyId, TableId _tableId, RecId _recId) { TrvExpTrans trvExpTrans = TrvExpTrans::find(_recId); return any2real(DateTimeUtil::getDifference(trvExpTrans.DateTo, trvExpTrans.DateFrom)/3600); }

Continued…

Here: German travel expenses in Dynamics 365 – Part 2

Expense management blog series

D365: Import Mastercard CDF3 statements OOTB

Introduction

It has been 5 years since I first described how to import credit card statements into Dynamics AX2012 Expense management module with zero customizations and zero middleware.

The technology has changed a lot, the transport is now not through the AIF but the [unattended] bulk upload of packages aka Recurring integration or just through ODATA and the entity TrvCreditCardTransactionEntity.

The principle remained the same: a text file in a proprietary format is converted into an intermediary XML, it passes an XSL transformation pipeline to get mapped to the canonical Dynamics schema, becomes interpreted and uploaded into the TrvPBSMaindata staging table via the TrvCteditCardTransactionEntity. The transactions are pre-processed by the D365 entity and automatically assigned to the respective employees by the credit card number. They are picked up by the employee herself/himself and included into an expense report in a self-service manner.

XSL transformation: key facts and considerations

In that old blog of mine the tedious VISA format was tackled; this time we will explore the Mastercard Common Data Format 3, the simplest of them all: the input file is in an XML one, and to feed it to Dynamics 365 for Finance just one XSLT transformation suffices in the pipeline: XML -> XML, no need for a CDATA envelope.

The D365 schema has been simplified a lot: within the 1
envelope there are 1..N records. I described the mandatory fields in the entity and the interaction between the 3 different amounts in [potentially] 3 different currencies in another blog of mine: https://erconsult.eu/blog/credit-card-currency-1/.

The other important aspects to consider are:

We are looking for CDFTransmissionFile/IssuerEntity/CorporateEntity/AccountEntity/FinancialTransactionEntity nodes and converting them into TRVCREDITCARDTRANSACTIONENTITY records.
The essential credit card number which is needed to establish a connection to the employee is taken from the CDFTransmissionFile/IssuerEntity/CorporateEntity/AccountEntity node, its @AccountNumber attribute.
D365 only stores credit card hash numbers and the last 4 digits of the credit card number; in addition, the credit card number is “salted” with the Credit card type. In the XSLT this is hardcoded, it is case sensitive and MUST match the Credit card type configured in Dynamics 365 and specified in the employee records:
Mastercard
The numerical format in CDF3 it weird: monetary values are represented with integer numbers with an exp10(x) exponent; there is an embedded function monetaryNodeToFloat in the XSLT file to reformat them into the XML decimal data type;
moreover, there may be foreign currency conversion charges in a separate node CDFTransmissionFile/IssuerEntity/CorporateEntity/AccountEntity/FinancialAdjustmentRecord_5900. To locate one, a 0..1 cross reference over FinancialTransaction_5000/AcquirerReferenceData is required.
To match a proper D365 expense category, the /CardAcceptor_5001/CardAcceptorBusinessCode data is used. Mastercard leverages the full https://en.wikipedia.org/wiki/Standard_Industrial_Classification list with up to 9999 branches down to single airlines and hotel chains; this is quite an overkill for D365. I prepared an abridged list with ~1000 most useful numbers, mapped to the standard Contoso USMF expense categories: “Travel”, “Flights”, “Car Rental”, “Hotel”, “Meal”.
Here is a Data management package for a quick import: Credit_card_category_codes_Mastercard.zip
Mastercard may impose some generic charges such as late payment charges, annual fees, etc. They are present in a different set of nodes but affect the CC statement balance all the same: CDFTransmissionFile/IssuerEntity/CorporateEntity/AccountEntity /FinancialAdjustmentRecord_5900.

Step by step instructions

Get your hands on a sample CDF3 file! How about this one: GNU3571A_3.xml ?
Navigate to Expense management > Setup > Calculations and codes > Credit card types and add a Card type = Mastercard.
Check the Expense management > Setup > General > Expense management parameters: Enter employee credit card number must be set to Hash card numbers (store last 4 digits).
Extract a credit card number from the file, go to Human resources > Workers > Employees, select e.g. the enigmatic Julia Funderburk and click Expense / Credit cards.
These are the so-called employee payment methods: one employee may possess several cards; the Payment method controls who is charged – the company or the employee – and how the employee is reimbursed. Choose Card type = Mastercard, Card ID = 16-digit credit card number. On saving the record the system keeps the last 4 digits and stores a hash value for comparison.
Go to the Data management workspace, create a new import Data project, upload Credit_card_category_codes_Mastercard.zip package with the Credit card category codes.
The Import transactions button on the Expense management > Periodic tasks > Credit card transactions form is dysfunctional in the modern D365 versions. We have to make a tailored Data project: on the Data management workspace, create a new import Data project, Add file = your sample file, Entity name = Credit card transactions, Source data format = XML-Element, Skip staging = No. The mapping is not going to work at the first try (“Node with ‘TrvCreditCardTransactionEntity’ could not be found in the source…”), as it cannot interpret the native CDF3 schema.
Click on the View map error symbol, go to Transformations, New, upload the XSLT file: CDF3XML_to_D365.xslt
Click Apply transforms, then Generate source mapping; it will try to interpret the previous sample file again, this time after the XSLT pre-processing.
Once the mapping between the transformed file and the D365 staging table has been established, close the mapping, drill down into the Credit card transactions entity name, open Entity structure and turn off Run business validations: this will let the system upload credit card transactions with a failing employee matching.
In the mapping, set an Auto default value for the PAYMENTMETHOD = the Payment method “CreditCard” you created before, as the import procedure is not going to take it from Employee-CC mapping (see #4 above) for whatever reason the developers had.
Start the import. After a few attempts – try and error, try and error – you should get the records imported. Check Expense management > Periodic tasks > Credit card transactions. The Payment method is going to be empty, this seems to be a bug; if necessary, select Record state = Error and manually amend the employee, expense category, payment method for those transactions where the matching by the credit card number and/or the merchant code has failed.
Impersonated as Julia, go to Expense management > My expenses > Expense reports, create a new expense report, and click Add unattached expenses.
Mark the CC statement lines to add, and confirm. The lines are added to the current expense report for further processing (check the blog for more details what is editable by the employee and what it not). On the Credit card transactions screen, the lines migrate to the Records state = All list.

To automate the above steps, convert the import project into a recurring data job. Use Tomek’s Recurring Integrations Scheduler or any other middleware of your liking to periodically grab local data files and push them through the Recurring integration API.