Import a D365 FO entity with a virtual KEY field

Import a D365 FO entity with a virtual KEY field

I’ve got a case recently where a delimited text file had to be imported into Dynamics where a key field was a virtual one. Namely, an external APS system sent a single ProdOprNum of the record, whereas the respective ProdRoute table in Dynamics 365 for SCM has a composite key ProdId + OprNum. Pre-processing the file with an XSLT and splitting the column was not an option, because the Microsoft XSLT parser still does not implement the XSLT 2.0 standard and cannot uptake a text file.

The most important lesson to learn was that the – generally useful – guidance Use a virtual field to receive and parse an inbound field | Microsoft Learn did not work, because the system kernel started looking for the record to update BEFORE the compound key was parsed in the mapEntityToDataSource() method of the entity. It was not able to find the right record to update, which lead to an error later on.

The solution is as follows:

  • Follow the above guidance from Microsoft and create an entity with the key fields ProdId, OprNum (both mapped) and one unmapped virtual field ProdOprNum.
  • With a right mouse click, create or refresh the corresponding staging table.
  • Here comes the crucial part: remove any relation such as Staging.ProdId=Entity.ProdId && Staging.OprNum=Entity.OprNum from the staging table. You should do so every time you refresh the staging table, D365 will namely re-create it over and over again. As a side effect, the Data management will think you insert the records while in reality the record is updated, but this does not matter.
  • Implement the splitting in the initializeEntityDataSource(), select the right record to update and plant it as shown below:
				
					 public void initializeEntityDataSource(DataEntityRuntimeContext _entityCtx, DataEntityDataSourceRuntimeContext _dataSourceCtx)
{
	super(_entityCtx, _dataSourceCtx);
	
	switch (_dataSourceCtx.name()) 
	{
		case dataEntityDataSourceStr(ProdRouteSchedEntity, ProdRoute):
			ProdRoute prodRoute = _dataSourceCtx.getBuffer() as ProdRoute;
			if (prodRoute)
			    break;
			    
			this.ProdId = substr(this.ProdOprNum, 1, 10);
			this.OprNum = any2int(substr(this.ProdOprNum, 11, 14));
			
			prodRoute = ProdRoute::find(this.ProdId, this.OprNum, RouteOprPriority::Primary, true);
			_dataSourceCtx.setBuffer(prodRoute);
	}
}

X++ programming blog series

Further reading:

Print a custom product label: a Template solution in Process Guide Framework, with detours
Extending SysOperation contracts with DataMemberAttribute
X++ Decorator pattern in Dynamics 365
Get a cost center in D365FO
Find and merge a DimensionAttributeValue
SysExtension framework pitfall: avoid new()
Input validation and messaging in the Process Guide Framework

Z4-Meldung an Bundesbank in D365 for Finance

Z4-Meldung an Bundesbank in D365 for Finance

The Z4-Meldung “Zahlungen im Außenwirtschaftsverkehr (abbreviated “AWV”) is a financial report that German companies must submit to the Deutsche Bundesbank as part of their external sector statistics. Companies are usually required to submit this report on a monthly basis, depending on the volume of transactions.

All German companies that engage in cross-border financial activities (e.g., loans, deposits, securities transactions) with a value above a specific threshold (currently €12500 per transaction) are required to submit this report. The worst part of it: not only  transactions with  non-EU countries must be reported, but inbound and outbound payments to EU countries as well. This is a nightmare in the tightly integrated EU economy.

Luckily, payments related to trade in goods (such as exports or imports) are not reported in the Z4-Meldung. These transactions are typically covered under other reporting mechanisms, such as the Intrastat for trade within the EU. Instead, the Z4-Meldung focuses on financial transactions unrelated to the direct exchange of goods e.g., loans, financial investments, and – most importantly – services. In particular, these transactions may be:

  • payments for services rendered for a foreign entity or paid to a foreign supplier,
  • for instance, freight costs
  • bank interests paid or received
  • cash discounts given to foreign customers or received from foreign suppliers
  • rebates received or paid
  • intercompany recharge to or from a foreign HQ or a subsidiary
  • VAT payments to a different EU tax authority. 

The Z4 report has a simple XML structure following their proprietary “XMW” schema: XMW – Elektronisches Meldewesen im XML-Format. In the regular payment process, in- and outpayments are classified with a transaction type (BELEGART) of “1” or “2”, respectively. In every transaction the foreign country is listed (ISO2), along with the transaction description and the amount in tsd. Euros. The sub-type classifier “KENNZAHL” poses a significant challenge: there are ~1000 codes in total from “A” for airfare through “S” for services till “U” for the use of software. This list from hell is published here in an inconvenient Excel file:  “Kennzahlenliste mit Belegarten”.

We are happy to share an Electronic Reporting configuration for the Z4 report in Dynamics 365 for Finance. Only the regular payment transactions (“DIKAPPOSTEN”) are supported, not shares or similar capital investments. To segregate payments by the highly detailed “KENNZAHL“, we suggest using the voucher prefix. For example, you may need to introduce different GL journal names for salary payments and commissions payments, both with distinct voucher number sequences. To avoid mistakes, we recommend separate payment methods, especially for goods and services, with every payment method to be processed in its own AP/AR payment journal.

Setup and use

  1. Download the 3 configurations here: XMW_Model_Mapping_Format_v2
  2. Install them in the following order: XMW Deutsche Bundesbank model -> XMW Mapping -> Z4 (format). Beware: the configurations are provided to you “AS IS” and with all faults and defects without warranty of any kind.
  3. Select the Z4 configuration and open Application specific parameters / Setup. You may Import a rudimentary setup from the ZIP file.
  4. With this setup you match patterns in the voucher of the bank transaction to the “KENNZAHL” codes. In some cases, you may decide to start looking for keywords in the payment description instead. This will require a slight modification of the formula in model/Messages/Message/Transactions/$ClassifierEnum. Alternatively, you may start probing for vendors or introduce a special vendor group with regards to the Z4 reporting; this is going to require a minor adjustment in the mapping and the format.
  5. Set the status of the application specific parameters to Completed when ready.
  6. Select the Z4 format in the Electronic reporting tree and hit Run.
  7. The Firmennummer is the unique end-to-end ID issued by the Bundesbank at the registration. You may also set the Extranet-ID of the user, if known. The mandatory phone, e-mail and other contact information are extracted from the employee card of the current user (the user who is executing the report i.e. you).
  8. With the Period date you select the reporting period (month). An empty period date means the current [session] month. Try to exclude bank transactions with regards to the trade of goods with the filter of Records to include, for example by the voucher number, if possible. You may also consider opening a different bank account for the regular intracommunity business. Again, try to separate payments of service, commissions etc. with distinct journals = voucher number.
  9. Hit OK and execute the report.
  10. You may also run the report in an unattended, batch mode. Configure an Electronic reporting destination such as an internal e-mail address or a SharePoint folder, schedule a monthly batch and keep the period date empty to support the rolling date. You can monitor the batch runs in Organisation administration > Electronic reporting > Electronic reporting archived jobs.

Result

The resulting file may look like this:

				
					<LIEFERUNG-AWZEL xmlns="http://www.bundesbank.de/xmw/2003-01-01" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.bundesbank.de/xmw/2003-01-01/BbkXmwAwzel.xsd" version="1.0" erstellzeit="2024-09-27T08:30:33+02:00" stufe="Produktion" bereich="Statistik" dateireferenz="1">
    <ABSENDER>
        <FIRMENNR>00345678</FIRMENNR>
        <NAME>ER-Consult GmbH</NAME>
        <STRASSE>Franz Lehar-Gasse 18</STRASSE>
        <PLZ>7111</PLZ>
        <ORT>Parndorf</ORT>
        <LAND>AT</LAND>
        <KONTAKT>
            <ANREDE>Miss</ANREDE>
            <VORNAME>Julia</VORNAME>
            <ZUNAME>Funderburk</ZUNAME>
            <ABTEILUNG>Sales & Marketing</ABTEILUNG>
            <TELEFON>425-555-5053</TELEFON>
            <EMAIL>julia@contoso.com</EMAIL>
            <EXTRANET-ID>EXNTESTA</EXTRANET-ID>
        </KONTAKT>
    </ABSENDER>
    <KOMMENTAR>Und ich dachte, ich bleibe unter dem Radar...</KOMMENTAR>
    <MELDUNG erstellzeit="2024-09-27T08:30:34+02:00">
        <MELDEPFLICHTIGER>
            <FIRMENNR>00345678</FIRMENNR>
            <NAME>ER-Consult GmbH</NAME>
            <STRASSE>Franz Lehar-Gasse 18</STRASSE>
            <PLZ>7111</PLZ>
            <ORT>Parndorf</ORT>
            <LAND>AT</LAND>
            <KONTAKT>
                <ANREDE>Miss</ANREDE>
                <VORNAME>Julia</VORNAME>
                <ZUNAME>Funderburk</ZUNAME>
                <ABTEILUNG>Sales & Marketing</ABTEILUNG>
                <TELEFON>425-555-5053</TELEFON>
                <EMAIL>julia@contoso.com</EMAIL>
                <EXTRANET-ID>EXNTESTA</EXTRANET-ID>
            </KONTAKT>
        </MELDEPFLICHTIGER>
        <MELDETERMIN>2024-09</MELDETERMIN>
        <VDR_04>
            <DIKAPPOSTEN belegart="2" kennzahl="523" zahlungszweck="Provision 2024">
                <BETRAG land="CN" landname="China" betragsref="APPM000063">-18</BETRAG>
            </DIKAPPOSTEN>
        </VDR_04>
    </MELDUNG>
</LIEFERUNG-AWZEL>

Electronic reporting blog series

Further reading:

Z4-Meldung an Bundesbank
Enumerate lines in Configurable Business Documents
D365 Electronic Reporting: JOIN records in pairs
Electronic Reporting (ER) Cookbook 4: References in a model
Electronic reporting for data migration
Electronic Reporting (ER) Cookbook 3: Working with dates
Electronic Reporting (ER) Cookbook 2: new tips from the kitchen
Electronic Reporting (ER) Cookbook

ZUGFeRD 2.2 import for Dynamics 365 for Finance

ZUGFeRD 2.2 import for Dynamics 365 for Finance

ZUGFeRD (Zentraler User Guide des Forums elektronische Rechnung Deutschland) is an e-invoicing format combining a human-readable PDF/A-3 and an embedded XML file using the CII (Cross-Industry Invoice) syntax. The CII syntax may also be used in the xRechnung, which is a pure XML version of the German e-Invoice. From 2025, the ability to receive and process electronic invoices becomes mandatory for B2B transactions in Germany.

Microsoft’s current solution in Dynamics 365 for Finance provides support specifically for the UBL (Universal Business Language) format of the xRechnung: Import vendor electronic invoices – Finance | Dynamics 365 | Microsoft Learn. It enables the import of vendor invoices received in this format, mapping them as pending vendor invoices in D365. The invoice may be uploaded directly into the system or placed in a SharePoint folder for unattended processing.

We are happy to announce the release of the CII (Cross-Industry Invoice) dialect of the xRechnung / ZUGFeRD electronic invoice. This solution is implemented via Electronic Reporting (ER) configurations, which means it does not require any customisations and can be easily installed across all active versions of D365. It sits on top of the standard Invoice model v280, Vendor Invoice Mapping to Destination v280.46, side by side with the configuration Vendor Invoice Import v280.7.

The functionality aligns with the standard Microsoft UBL import, utilizing the key data elements for processing and ensuring PO matching:

  • Header: Invoice ID, Invoice date, Due date, Vendor ID, Currency code, PO number, a header-level note and header attachments (in XML embedded MIME documents).
  • Lines: Line number, Item ID, Quantity, Sales unit of measure, Sales price, Discount (percentage discounts are converted to absolute values as the XML schema doesn’t support percentages), Price unit, Name, Description, and Line amount (excluding VAT).

The solution supports the ZUGFeRD 2.2/Factur-X 1.0 profile EN 16931, also known as the “COMFORT” profile (being also a subset of the EXTENDED profile), which meets all tax law requirements and must be accepted by customers unless explicitly denied.

Price

The solution is offered at a fixed fee per end customer / D365 tenant:
Version Price Deliverables Notes
Light €890 The Vendor invoice import CII Electronic Reporting configuration This option does not require any X++ customisations or 3rd party software and can be installed and used right away.
Light+ €1200 Same as the Light version but with 5 hours of prepaid support and installation  

Your can acquire the solution on the official Microsoft AppSource marketplace: ZUGFeRD CII import (microsoft.com)

 

Setup and use

 
  1. Follow the steps outlined in the Microsoft’s guidance Import vendor electronic invoices – Finance | Dynamics 365 | Microsoft Learn:
  2. Import the same three configurations from Microsoft (Prerequisites);
  3. Import the configuration Vendor invoice import CII.xml provided to you;
  4. Open Accounts payable > Setup > Accounts payable parameters; on the Electronic documents tab, select the imported Vendor invoice import CII format.
  5. To run either one of the formats – UBL or CII – by choice, you may download and import a complimentary X++ extension with an additional parameter CII vendor invoice and an own menu item underneath the standard one: VendImportInvoice_CII.axpp. In the batch execution mode this does not matter, because each of the formats becomes its own Electronic source with a set of SharePoint folders etc.
  6. Prepare the master data: make sure the vendors are stored with proper VAT numbers in Dynamics 365 for Finance.
  7. Make sure that the vendor’s external item ID mapping exists. Any Buyer Assigned ID (our item number) potentially sent by the vendor is neglected by the Microsoft’s mapping, and the conversion to the internal Item ID is always handled via the external item IDs. Under these conditions, the import works as expected and the PO lines may be matched.
  8. Make sure that the units of measure are properly mapped to the UNECE Recommendation No. 20 list called “Codes for Units of Measure Used in International Trade”. E.q. the UNECE unit H87 must be mapped to either PCS or EA or STK, see also a longer list in https://erconsult.eu/de/blog/aktueller-stand-der-erechnung-in-d365-for-finance-deutschland/
  9. Perform an interactive test upload by using the function Accounts payable > Periodic tasks > Import vendor invoices (or Import CII vendor invoices if you installed and configured the above extension from step 5). Beware: contrary to the UBL format, the CII (“COMFORT”) invoice does not include any line-by-line reference to the purchase order lines. Just the PO number is sent in the header. We assume that the numeration of the invoice lines mostly follows the PO lines numeration. If not, the standard mapping will fall back to the matching by the line purchase price.
  10. Configure the batch mode with Electronic sources as outlined in the guidance at step 1.

Useful links

E-invoice and configurable business documents blog series

E-Invoice in D365

Further reading:

ZUGFeRD 2.2 import for Dynamics 365 for Finance
Aktueller Stand der eRechnung in D365 for Finance (Deutschland)
Austrian Peppol-UBL e-invoice
ZUGFeRD 2.2/ Factur-X 1.0 for Dynamics 365 FO
How NOT to send an order confirmation per e-mail
Enumerate lines in Configurable Business Documents
Canvas sections in D365 Configurable Business Documents
Italian eInvoicing FatturaPA, CIG and CUP numbers, RIBA
Italian eInvoicing FatturaPA: Part 2