Automatic emailing collection letter to customer with relevant invoice(s) attached using Electronic Reporting (ER) within D365FO

Jean Donald KOUADIO Laure Ouvrelle Robert Steiner Martin Sengel Patrick PHRASSIAS Pierre Fournier Matthieu Ricard Lionel Yanzeu Anne Leyris Aurélien CLERE Aurelien Brungard Aurélien Langagne Marie Michèle André Bruno GOBARDHAN Bruno Castagnetti Rémi SALMON Mika?l P. Gladys TJANI Guillaume FAGES Nadine N. Wafa Hadj Mabrouk Romaric Tegou Zambou Daniel TCHAKOUNTE Arnold KUMGNE WAFO David Nunes

Hello and welcome to our today new blog post about collections process within D365FO where I will be showcasing how to automatically email collection letter to customer with relevant invoice(s) attached without writing a single line of code since I will leverage Electronic Reporting (ER) capabilities within D365FO.

Background:

A couple of months ago, I got a client requirement related to dunning process (customer collections process). The underlined need was to automatically email collection letter to customers with relevant invoice(s) attached in order to avoid a lot of manual work (fetching invoices , printing them and write a reply email to the customer with invoices attached) which is time consuming and costs money in situation where after sending the collection letter to the customer and the customer sends back the answer by asking to provide him with more details about the corresponding collection letter (eg: invoice(s) details).

As you may know, built-in collection letter printing functionality within D365FO (until application version 10.0.39) is not shipped with a feature which allows to automatically attached relevant outstanding invoice(s).

Introduction.

In the today post, I will demonstrate how to bridge the gap by leveraging ER capabilities within D365FO.

Someone may be asking, hey... Arnaud, where would you find the invoices and afterwards attach them to the corresponding collection letter in one go and in an automated way?

Solution:

The way how the proposed solution is realized is in five (05) steps:

Step 1: Activate the <eInvoice attachment> checkbox on the customer master

Step 2: Modify the standard Microsoft <Reminder model> Electronic Reporting configurations:

Step 2.1: Extend the ER Model

Step 2.2: Extend the ER Model Mapping

Step 2.3: Extend the ER Format

Step 3: Configure the Electronic Reporting destination

Step 4: Configure the Print management options

Step 5: Print the collection letter

Now, let's go through the solution step by step. ??

Step 1: Activate the <eInvoice attachment> checkbox on the customer master

As you may know, the <eInvoice attachment> checkbox located at the customer master data within D365FO allows to generate and attach PDF invoice report to the posted invoice header when post and print-out the customer invoice (see screenshot below):

Note: After activating the checkbox, you should print-out the already posted invoices to get the PDF attached (use post and print options for the future customer invoices posting to get the PDF attached in one go).

Step 2: Modify the standard Microsoft <Reminder model> Electronic Reporting configurations

2.1 - Extend the ER Model

a) Modify the Reminder model ER configuration by adding two (02) fields to expose invoice attached FileName and FileContent as follows (If the Reminder model ER configuration does not exist in your ER configurations workspace, import it from MS repository):

b) Save and change the status to Completed as follows:

2.2 - Extend the ER Model mapping

a) Create an extension of the Reminder model ER Model mapping by deriving from the MS Model mapping and based on the extended Reminder model created at step 2-1) above.

b) Add three (03) data sources below to the Model mapping designer related to <CustInvoiceJour>, <ProjInvoiceJour> and <DocuRef> tables to expose Customer invoice headerS and related attached PDF documents.

Note: I am using <CustInvoiceJour> & <ProjInvoiceJour> tables since I need to grab both regular customer invoice and Project invoice headers.

c) Add the AYO_InvoiceNum calculated field to <CustInvoiceJour> & <ProjInvoiceJour> tables in order to build a nice mapping since I would need to combine those two tables using the LISTJOIN() ER function.

d) Combine <CustInvoiceJour> & <ProjInvoiceJour> tables using LISTJOIN() ER function as follows:

e) Add the $CustInvoiceNumber calculated field to <AYO_DocuRef> table to grab the invoice number from both Customer invoice journal and Project invoice journal based on LISTJOIN <ALL> record list.

f) Create a subset of <DocuRef> table to grab only document with type = "File" since I am only interested on invoice files from reference documents (DocuRef) table.

g) I noticed that the Reminder model Data Model uses the $CollectionLeterLines Data source to expose the collection letter lines. Then, I need to update the $CollectionLetterLines Data source to expose all the data I need to handle the attachments. To achieve this, I added a nested calculated field <#ALL> into $CollectionLetterLines Record list to grab the invoice headers for each invoice of the reminder line as shown below:

h) Once I grab the invoice header record for every reminder line, I found the corresponding attachment with type = "File" by adding another nested element as shown below:

Note: I am finding only attachment with type = "File" since it may exist other attachment type (eg: note, ...) linked to the invoice headers.

i) Our Model mapping is almost done and now I need to bind the added FileContent and FileName fields of the Data model as follows:

j) Save the Model mapping and Validate for Error/Warning checking purposes.

k) Change the status to Completed.

2.3 - Extend the ER Format

a) Create a derived Format from the standard MS Format configuration as follows:

b) Add a <Folder> Root data type to the Format designer to expose the collection note and the relevant invoices as follows:

c) Add the <Invoice> File data type to the Format designer to expose the PDF Invoices under Folder node as follows:

d) Add the <Content> Binary content data type to the Format designer to expose the content of the PDF Invoices under Invoice node as follows:

e) Bind the corresponding fields and define the invoice file name as follows:

Note: I've activated the Enabled option to the Invoice File data type since I don't want to generate any File in situation where there is not attached invoice for the invoice found on the reminder lines.

f) Save and Validate the Format configuration for Error/Warning checking purposes:

g) Activate some User parameters configurations in order to run the Draft version and use ER destination for Draft status as follows:

Step 3: Configure the Electronic Reporting destination

a) Configure the ER destination to automatically email the collection letter with relevant invoice(s) attached to the customer as follows:

• Folder destination: I first configured the Folder destination to send out the folder to nowhere since I only need to email the collection letter and all the relevant attached invoices.

• Collection letter destination: I've configured the collection letter email destination and converted it to PDF as follows:

Below are the ER formulas used:

• Invoice destination: I've configured the attached invoice email destination by grouping the collection letter destination and Invoice destination since I want to have a one group of output per collection letter with all relevant invoices attached that should be emailed at once as follows:

By Grouping, the Attached invoice destination inherited from Collection letter destination as shown below (I like this nice ER functionality ??):

At this stage we are done done with ER stuff. Let's go for Print management setting. ??

Step 4: Configure the Print management options

Let's switch the default SSRS Collection letter note report to our newly extended ER collection letter note: Configurable Business Document (CBD).

At this stage we are done with all required configurations. Now let's print the collection letter and enjoy.

Step 5: Print the collection letter

Before printing the collection letter I've updated my testing customer master by adding an email contact information with Invoice purpose as follows:

Now let's print the collection letter (I was too impatient to reach this stage ??).

a) Collection letter printing process

b) Email automatically sent to the customer (myself in this case since I've entered my email address at the customer email contact information)

c) Email history:

Closing note:

Electronic Reporting (ER) is a very powerful tool within D365FO from my standpoint and you could find here a kind of walk trough and a very good book about ER wrote by CA Neeraj Kumar and Ramita Chaurasiya . I've the chance to read the book and discuss with CA Neeraj Kumar and it is a real golden recipe for those who wants to make the move to ER framework within D365FO.

That's it all for our today blog post. I will be back soon for the new blog posts. ??