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.

Expense management blog series

On currency in credit card expense transactions – Part 2

In the previous blog On currency in credit card expense transactions – Part 1 we have explored a case where the credit card currency and the local accounting currency in D365FO did match. But what if the card is issued in a currency different from the accounting currency of the legal entity?

Case 2: credit card currency <> accounting currency

For example, Hungarian employees (the official currency of Hungary is the Forint, abbreviated as HUF) may be given credit cards billed in Euro, since they are surrounded by the Euro zone. If we follow the same logic as before, if such a credit card statement is expressed in EUR, then it must be posted in EUR into the AP subledger:

01.06.2019 Paid a hotel in Bratislava 500 EUR (the credit card provider applied the cross rate of 1.00)
04.06.2019 Posted in AP subledger as 500 EUR (the daily rate was 322 HUF/EUR)
30.06.2019 The credit card balance of June was withdrawn by the bank from the employee’s daily account in HUF @323,39 = 161 695 HUF. This is not recorded in D365FO for personal credit cards.
30.06.2019 An AP revaluation is performed in D365FO. The liability is now worth 161 450 HUF at the current exchange rate of 322,9 HUF/EUR
01.07.2019 The employee is reimbursed in the local currency by the employer with 161 450 HUF @322,9 HUF/EUR.

As a result, the employee will suffer a loss of 245 HUF but have to arrange himself/herself, as the benefits of travelling with an Euro card should overweigh any potential losses from the imperfect execution in D365FO by far.

Case 3: credit card currency <> accounting currency <> local currency

What if the trip was not to Slovakia (EUR) but to the Czech Republic who refused to adopt the Euro and retained their own Czech koruna (CZK)? Now all 3 currencies in the transaction are different:

The legal entity Accounting currency = HUF
The transaction Local currency = CZK
The Credit card Currency = EUR

The equation is still the same, the credit card balance is posted as a Forex liability in EUR. In the General Ledger voucher / accounting distribution the cross rate CZK -> HUF is kind of triangulated through the Euro: CZK -> EUR -> HUF where CZK -> EUR is the rate applied by the credit card institution. With that in mind, let us summarize the different business cases with the 3rd being the most generic and sound:

	Case 1	Case 2	Case 3
Credit card Currency	CHF	EUR	EUR
CC “Local” currency	THB	EUR	CZK
Accounting currency	CHF	HUF	HUF
Expense line	THB	EUR	CZK
Line itemizations	THB	EUR	CZK
Acc. distribution	THB	EUR	CZK*
GL voucher	Dr THB – Cr THB**	Dr EUR – Cr EUR	Dr CZK – Cr CZK**
AP subledger	CHF	EUR	EUR

(*) The accounting currency HUF equivalent i.e. ‘amount MST’ is evaluated through the rate EUR -> HUF (**) Note the currency mismatch between the AP and GL ledgers. This spoils the AP ledger account with transactions in foreign currencies and may potentially kick the subledgers off balance.

Expense management blog series

On currency in credit card expense transactions – Part 1

Introduction

The Expense management module in D365FO comes with an interface to credit card providers. I addressed this topic in an old blog of mine: visa-vcf-plain-text-credit-card-statement-into-dynamics-ax-with-xslt. Imported credit card transactions end up in the [Unattached] Credit card transactions list, ready to be taken over by the employee into an expense report.

There used to be a lot of misunderstanding with regards to the different currency rates and amounts and their meaning. Moreover, for a decade the Dynamics system had been posting the liability in the merchant transaction currency, which led to the expense report being split by currency code on posting into the AP ledger. It was wrong in many ways until we fixed it in a tremendous collaboration effort with Microsoft Support in 2018.

Case 1: credit card currency = accounting currency

In the expense report line there is an obligatory Payment method associated with the credit card type. It mandates how the employee is reimbursed for this part of the expense report. Typically, the Payment method (Expense management > Setup > General > Payment methods) is configured with the Offset account type = Worker which creates a liability against the AP account (vendor) associated with the employee.

If this liability is expressed in a currency different from the accounting currency of the legal entity, it starts accumulating exchange rate difference. If the payment takes place in a different accounting period, the employee may be reimbursed too much or too little. Indeed, the credit card it typically bound to a bank account of the employee, and the exchange rate charged by the bank to the cardholder is very unlikely to be the same as the current corporate exchange rate maintained in D365FO. Ergo, the AP transaction should be expressed in the accounting currency.

Take a look at the below example, the employee visited Thailand:

The Local currency code is not what you would expect but the currency the merchant charged the transaction in, here the Thai baht. Internally it is called EXCHCODE_LOCALCURRENCY.
Amount in local currency is the transaction amount in baht
The Currency code is the currency the credit card issued in (a.k.a. EXCHCODE_CREDITCARDCURRENCY), typically the currency of the state where the cardholder lives (here: the Swiss Franc)
Transaction amount is the amount the credit card issuer will settle against the bank account backing the credit card, i.e. an amount in CHF to be withheld from the cardholder at the end of the month. Internally it is called AMOUNT_CREDITCARDCURRENCY.

The exchange rate between the “Local currency” and the “Currency” must not follow the standard exchange rate setting in D365FO, but it should be what the credit card institute provides on the account statement. Take the next example (Expense management > Periodic tasks > Credit card transactions):

The exchange rate was developing over the same time span as shown below:
01.01.2018 2,995 CHF for 100 THB
01.02.2018 2,975 CHF for 100 THB
01.03.2018 3,001 CHF for 100 THB

The exchange rate applied by the bank in February was 15,11 CHF/ 500 THB = 0,03022 CHF/THB = 3,022 CHF for 100 THB. Clearly, it was higher than the average to the disadvantage of the employee, right as expected of a greedy bank.

What will you see in Dynamics 365 for Finance and Operations (version 8.x+, all bugs fixed) upon attaching the imported credit card transactions to an expense report and posting the same?

Expense report line currency: THB
Expense report line Transaction amount and currency: 77,67 CHF (!)
Expense report line Local amount: 2550
Accounting distribution currency and amount: 2550 THB
Expense report line Exchange rate: 3,046 CHF/100 THB, not editable (*)
GL Voucher transactions: Debit expense THB – Credit liability THB (**)
AP Subledger transaction: Credit liability CHF (!!)

(*) This is the unique exchange rate as charged by the credit card provider, and not the default legal entity exchange rate on the transaction date.

(**) The GL transaction currency does not match the subledger currency, as you can see. In theory, it is possible to have voucher transactions with the debit in one currency (THB) and a credit on another currency (CHF), as long as the equivalent in the local currency (CHF) matches and the voucher is in balance. However, it was not feasible for Microsoft to implement it in the subledger / accounting distribution programming model.

To be continued: Part 2