Electronic reporting: Italian eInvoicing FatturaPA, CIG and CUP numbers, RIBA

Introduction

The use of eInvoices in public procurement in Italy is mandatory for ministries, tax agencies and national security agencies since June 2014. Since 31 March 2015, it is mandatory for all public entities. We also see private companies embarking this platform. Every supplier must comply and provide invoices in the “FatturaPA” format which is perfectly described in English (!) here.

In Dynamics 365 for Finance and Operations, the [public] client must have the parameter eInvoice set to Yes to activate electronic invoicing. If the eInvoice attachment has been activated as well, D365FO even embeds a copy of the invoice into the XML stream (as a PDF encoded by Base64 algorithm and stored in a CDATA block).

The eInvoices are driven by the Electronic Reporting. As I am writing this, the most recent version is the format “Sales invoice (IT)” v 29.34, based on the “Customer invoice model” and mapping v 29 from the 15th of January 2019.

In essence, you download the above 2 configurations, deploy them at Organization administration > Electronic reporting > Configurations, put an anchor on the Electronic documents tab at Accounts receivable > Setup > Accounts receivable parameters.
With this setup on hand, you post your sales or project invoice and it creates a record in the Accounts receivable > Invoices > E-Invoices > Electronic invoices list. These records are then processed manually or in a batch and the ER generates and drops XML files.

The problem is, the output is full of bugs.

DatiOrdineAcquisto, CodiceCIG, CodiceCUP, RiferimentoNumeroLinea

To crack down on mafia, the government issues CUP and CIG codes to identify contracts or assignments with public investments. They are tracked end-to-end from the invoice down to the payment (e.g. RIBA bills of exchange, see below). In Dynamics 365, these numbers need to be entered into the Base document part in the sales order header. As you post the sales invoice, it creates a persisting copy of the BaseDocument_IT record and attaches it to the invoice record CustInvoiceJour. The ER picks it up and the Base document type triggers one of the 5 sections <DatiOrdineAcquisto>, <DatiContratto>, <DatiFattureCollegate> and so on. So much for theory.

The code in \Data Dictionary\Tables\BaseDocument_IT\Methods\updateRefRecords is wrong in so many ways. It is going to fail creating a record for 2 invoices for the same order, 2 orders for the same public tender. The ER cannot find the record BaseDocument_IT, and the section <DatiOrdineAcquisto> is then missing in the output.

To mitigate this, you must create a mapping derived from Customer invoice model, declare it Default for model mapping and put a fallback into the InvoiceBase/BaseDocument_IT node:
IF(NOT(ISEMPTY(CustInvoiceJour.'<Relations'.BaseDocument_IT)), CustInvoiceJour.'<Relations'.BaseDocument_IT, CustInvoiceJour.'salesTable()'.'<Relations'.BaseDocument_IT)

Furthermore, the user may fail to provide the Line number in the Base document group. As a result, the <RiferimentoNumeroLinea> XML element is going to be missing in the output. The FatturaPA documentation told this element was not mandatory but in practice it is.

You need to derive a custom format from “Sales invoice (IT)” and improve it by substituting an empty line number (if not entered by the user) with “1” as long as any of the Base document types in the SO header has been selected. The below change needs to be replicated 5 times for the 5 respective types of the public sector Base documents.
For example, the node XMLHeader/p:FatturaElettronica/FatturaElettronicaBody/DatiGenerali/DatiOrdineAcquisto/RiferimentoNumeroLinea can be bound to
IF(@.LineNumber>0, @.LineNumber, 1)

RIBA CIG and CUP codes in the record 50

The Ri.Ba. is an electronic format for exchange bills remittances common in Italy. It is well described by bella Mrs. Santoro: Italian Ri.Ba. (Bill of Exchange) setup and process. This guidance was for AX2009, but the process remained the same and is valid in all points with the exception of the data export engine.
The current version of the ER configuration is “RIBA collection remittance (IT)” v 25.27 based on the “Payment model” 25 with the mapping “Payment model mapping 1611” v 25.27.

In the record 50 of the remittance it should provide the CIG and CUP codes in a long string. With the BaseDocument_IT record missing, this is going to fail. The workaround is similar: in a custom derived model at 2 places with
@.'$CustInvoiceJour'.'<Relations'.BaseDocument_IT.xxx

you need to put
IF(NOT(ISEMPTY(@.'$CustInvoiceJour'.'<Relations'.BaseDocument_IT)), @.'$CustInvoiceJour'.'<Relations'.BaseDocument_IT.TenderCode, @.'$CustInvoiceJour'.'salesTable()'.'<Relations'.BaseDocument_IT.TenderCode)

CodiceFiscale and IdCodice

The own VAT Code (e.g. IT00XXXXXXXXX) may differ from the Fiscal code (e.g. 00YYYYYYYYY) which is entered in a separate field CoRegNum in the company information. The eInvoice format in D365FO takes wrongly the Fiscal Code for the VAT Code value. You need to derive a custom format from Sales invoice (IT), and replace the binding for the <IdCodice> element and its “Enabled” (“?”) condition with
Invoice.InvoiceBase.CompanyInfo.VATNumber
Similarly, the EU VAT number of the customer may not match the fiscal code; moreover, the standard ER configuration erroneously takes the data from the Order customer in the sales order and not the Invoice customer account.
First and foremost, in the mapping the FiscalCode of the customer is not bound to the table. Fix this, then replace the binding in the derived format at 3 places and 3 conditions like this:
Invoice.InvoiceCustomerAccount.FiscalCode

Continued: Italian eInvoicing FatturaPA: Part 2

E-invoice and configurable business documents blog series

Extending SysOperation contracts with DataMemberAttribute

Since January 2019, it has become possible to decorate new “parm” methods on extension classes with “DataMember” attributes. In other words, it is now feasible to add a new parameter onto a dialog window of a standard class, even if this class was implemented within the SysOperation framework.
This was enabled by the so-called “4th extension wave” in Platform update 23.

Before PU 23, the below code

				
					[ExtensionOf(ClassStr(AssetRollForwardContract))]
final class AssetRollForwardContract_Extension
{
    private boolean updateDeprInfo;
    
    [DataMemberAttribute(identifierStr(AssetAdditionalAcqDepreciation))]
    public boolean parmUpdateDeprInfo(boolean _updateDeprInfo = updateDeprInfo)
    {
        updateDeprInfo = _updateDeprInfo;
        return updateDeprInfo;
    }
}

did not compile but brought up the error message “DataMemberAttributeOnExtensionClassNotSupported: The DataMemberAttribute is not supported on extension classes.“

Now it works, and the new parameter is plugged in nicely at the desired place. Initializing the parameters with default values posed a challenge. As suggested by Mr. Dráb here, the class should be declared with the SysOperationInitializable interface, and amended by an initialize() method. However, if the original class did not implement SysOperationInitializable, your extension cannot do any better.
The only solution that worked was simple as a brick: an inline assignment of the respective field variable (which is another feature in X++, quite a new one):

				
					[ExtensionOf(ClassStr(AssetRollForwardContract))]
final class AssetRollForwardContract_Extension
{
    private boolean updateDeprInfo = true;

    [DataMemberAttribute(identifierStr(AssetAdditionalAcqDepreciation)),
        SysOperationLabelAttribute(literalstr("@SYS4080213")),
        SysOperationHelpTextAttribute(literalstr("@SYS321607")),
        SysOperationDisplayOrderAttribute('4')
        ]
    public boolean parmUpdateDeprInfo(boolean _updateDeprInfo = updateDeprInfo)
    {
        updateDeprInfo = _updateDeprInfo;
        return updateDeprInfo;
    }
}

The parameter becomes active by default, but the last value selected by the user is still serialized and de-serialized nicely, right as it should be.

X++ programming blog series

Searchable product attributes

Introduction

There is this cool feature in Dynamics 365 for Finance and Operations: the product attributes. You can assign an arbitrary number of flexible attributes to a product. Free text, lookup from a list, integer, real, boolean values are supported. The users are super excited and their first question is going to be: “Can I search by an attribute”?
No, you cannot. At least, not in the D365FO browser UI but only at a retail Point of sales. The excitement fades, and you should quickly choose a more pleasant topic for your demo.

In fact, you can. It is super tricky to configure but once configured and saved as a query, filtering by attribute becomes pretty straightforward.

Configuration

Disclaimer: the below walkthrough only works with product attributes assigned through a procurement hierarchy. Those imposed by a Retail hierarchy through attribute groups cannot be configured for the search. Which is a pity, because the Retail hierarchy is a candy. It can be used as a collection of item templates, by far more powerful than a usual product record template.

What we need here is the CatProdSerchableAttrFilterMaterialized (sic!) table. To get that table populated, you need a procurement hierarchy and a procurement catalogue.

Create an attribute, for example of the a text attribute type: Product information management > Setup > Categories and attributes > Attributes.
Make sure there is a category hierarchy given the Procurement category hierarchy role (Product information management > Setup > Categories and attributes > Category hierarchy role associations).
In Procurement and sourcing > Procurement categories, select a category and add the attribute into the list of Product attributes.
It is essential to set the Searchable mark in this EcoResCatalogControl record. This declares a subset of attributes available for searching.
Choose a few released products, assign them to the above procurement category. The attribute should appear under the Product attributes button. Enter or import the attribute values.
Open Procurement and sourcing > Catalogs > Procurement catalogs. Create a new dummy catalog, for instance “AttributeSearch”.
Use the Publish catalog button. It is important to update the catalog on a recurring basis as you provide more products with the attribute.
Now you should get your hands on the Synchronize product search data periodic function in the Procurement catalog menu. It may be disabled, but you can invoke it by the https://xxx.operations.dynamics.com/?mi=SysClassRunner&cls=CatProductFilterRefreshCacheBatch URL.
This program populates the CatProdSerchableAttrFilterMaterialized table. You can configure it for recurring batch execution.

Design an advanced filter

1. In the Released product list, open the Advanced filter… (Ctrl-Shift-F3) query.
2. On the Joins tab, locate the “Products” and join the “CatProdSerchableAttrFilterMaterialized” to it.
3. To the “CatProdSerchableAttrFilterMaterialized”, add 2 joins side by side: “Attribute” and “The base table for other value tables that each stores values of a different data type”. What a name!
4. Finally, join the latter with one of the “The value of the Text data type for the attributes” tables depending on the type of the attribute, since every value type is stored in a separate table.
5. By now, you should have gotten something like this:
6. The goal is close. On the Range tab, apply a filter to the “Attributes (6)” and “The value of…” tables as shown below. The attribute name filter let the system search in this attribute only; there may be many searchable attributes with the same value type.
7. Save the query for re-use and apply the filter. Enjoy the result!

Author Archives: Eugen Glasow

Electronic reporting: Italian eInvoicing FatturaPA, CIG and CUP numbers, RIBA

Electronic reporting: Italian eInvoicing FatturaPA, CIG and CUP numbers, RIBA

Introduction

DatiOrdineAcquisto, CodiceCIG, CodiceCUP, RiferimentoNumeroLinea

RIBA CIG and CUP codes in the record 50

CodiceFiscale and IdCodice

E-invoice and configurable business documents blog series

Extending SysOperation contracts with DataMemberAttribute

Extending SysOperation contracts with DataMemberAttribute

X++ programming blog series

Searchable product attributes

Searchable product attributes

Introduction

Configuration

Design an advanced filter