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 crucial key 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, they 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!
- 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.
- 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.
The post D365: Import Mastercard CDF3 statements OOTB appeared first on ER-Consult.
