D365 Finance & Operations and Dynamics AX Forum

Vendor Invoice Automation and Excel's query "from OData Feed" to access and update data entities - Part 1

By Shilpi Thakur posted Jun 03, 2020 05:43 AM

  

1. Introduction

This is part one of the two-part blog series, which discusses about the AP automation framework for Microsoft Dynamics 365 Finance and Operations (D365FO). Microsoft introduced the framework to automate the vendor invoicing, which I will discuss in this post and has made some improvements to the AP automation process, including data integrations, which I will discuss in the following post.

I have briefly discussed the use of OData service to expose or access the data entities. Also, I have demonstrated the use of Excel's query "from OData Feed" to export, change add or delete tables for selected data entity as part of the export process within the AP automation framework.

1.1 Background

The Accounts Payable process begins when the AP department receives a vendor invoice. Invoices can arrive through different channels, including EDI, email, fax, interoffice deliveries, etc. When AP processes are not centralized, handling, and processing each new invoice becomes a challenge.

1.2 Business Value

Organizations are looking for ways to improve and streamline the Accounts Payable (AP) processes, primarily invoice processing, as one of the top process areas that should be more efficient.  In many cases, these organizations offload the processing of paper invoices to a third-party optical character recognition (OCR) service provider. They then receive machine-readable invoice metadata together with a scanned image of each invoice. To help with automation, a "last mile" solution is then built to enable the consumption of these artifacts in the invoicing system. Now this "last mile" automation is allowed out of the box through an invoice automation solution.

2. Solution context

The invoice automation solution enables a standard interface that can accept invoice metadata for the invoice header and invoice lines, and also attachments that apply to the invoice. Any external system that can generate artifacts that comply with this interface will be able to send the feed for automatic processing of invoices and attachments.

The following illustration shows a sample integration scenario where Contoso has partnered with an OCR service provider for vendor invoice processing. Contoso's vendors send invoices to the service provider by email. Through OCR processing, the service provider generates invoice metadata (header and lines) and a scanned image of the invoice. An integration layer then transforms these artifacts so that they can be consumed.

2.1 Data entities and Data Packages

A data package is the unit of work that must be sent so that invoice headers, invoice lines, and invoice attachments can be created. The following data entities are used for the artifacts that make up the data package:

  • Vendor invoice header
  • Vendor invoice line
  • Vendor invoice document attachment

Note: Vendor invoice document attachment is a new data entity that as part of this feature. The Vendor invoice header entity provided by Microsoft is modified so that it supports attachments. The Vendor invoice line entity is the same with no modifications.

For detailed information of data entities, data packages and how to consume it, please refer my following blog post:

3. Solution components

The solution footprint consists of the following components:

  • Data entities for the invoice header, invoice lines, and invoice attachments
  • Exception processing for invoices
  • A side-by-side attachment viewer in invoices

Refer the below link for complete vendor invoice automation framework details:

4. Solution Test in D365 Finance and Operations

Now let's look at the example of how this feature works.

Note: The following demonstration is part of the sample data with the required format from D365 F&O

4.1 Export data using data management

  • Create a sample pending vendor invoice with attachment
  • Then go to System administration > Data management workspace
  • Create and export project which consists of Vendor invoice header, Vendor invoice lines, and Vendor invoice document attachment entity
  • Generate, export, and download the package.
Vendor_invoice_automation_-_export.jpg

4.2 Excel Query from OData feed to access the data entities

You can also make use of the Excel query from OData feed to access the data entities by using an OData Endpoint.

I have used the following URL to expose the data entity using the OData as an example

  • [Your organization's root URL]/data/VendorInvoiceHeaders
Odata_query_-_Vendor_invoice_headers.jpg

Refer the below link to understand how to use OData services:

4.2.1 Consuming the OData source with Excel

Sign in with your AAD credentials, and you can expose the data entities via OData into excel through the OData feed.

 

Odata_feed.jpg
Odata_login_for_vendor_invoice_headers.jpg

 After you click on Connect, you will get a popup where you can choose which tables you want to import into Excel.

 
Vendor_invoice_headers_-_Choose_columns.jpg

Required fields:

  •  HeaderReference
  • Currency
  • Date
  • InvoiceAccount
  • InvoiceId
  • InvoiceNumber
  • IsApproved (Set to No)
  • VendorAccount
Vendor_invoice_headers_-_columns_added_-4.jpg

 Close and Load data in Excel.

A similar setup is possible using the OData endpoint service for Vendor invoice Lines data entity.

4.3 Invoice image as an attachment

Now it's essential to understand where the Invoice image or the Invoice file is placed. From the export package, you would notice the folder Resources > Vendor Invoice document attachment. Similarly, create one for the import process.

Explanation of each field on the Vendor invoice document attachment entity is given as below:


Reference No

Field name

Description

Values (examples)

1

DocumentID

Must be the GUID generated from the source system

{45DDD1D7-DDFE-408B-9B18-844507B36E5D}

2

DefaultAttachment

Must be set to Yes to display the invoice image side-by-side with the invoice metadata automatically

Yes

3

FileName

Name of the file without extension

ShowInvoice

4

FileType

Specify the file extension

png

5

HeaderReference

This is a unique value generated from the source system. The same value will be used in the invoice document attachment and invoice line file to link images and lines to the header.

15537

6

TypeID

Set to image

image

7

FileContentsFileName

Full name of the file

ShowInvoice.png

 
Now you can zip the folder to create a import package for this test.

Note: In a real-world solution, the source system should automatically zip the files after generating them.

4.4 Import data using data management

Now we go back into the Data management workspace to create an import project using the.ZIP file as an import package.


The import automatically configures the entity lines based on the manifest in the data package, including the invoice document attachment on the header of the invoice.

Vendor_invoice_-Import.jpg

When we go to the Pending supplier invoices screen in the Purchase ledger module, you can see that it now contains the invoice in the list.

Vendor_invoice_header_-002.jpg

When I click on the invoice, I see the invoice metadata side-by-side with the invoice image.

Side_by_side_view.jpg

4.5. Default attachment

If a vendor invoice has more than one attachment, you can set one of the documents as the default attachment on the Attachments page.

The Is default attachment option is a new option that is as part of this feature.

This option is also exposed in the Vendor invoice document attachment data entity. Therefore, the default attachment can be set through integrations. Only one document can be set as the default attachment. After you set a document as the default attachment, it's automatically shown in the attachment viewer when the invoice is opened. If you don't set any document as the default attachment, the viewer doesn't automatically show any attachment when the invoice is opened

 Is_default_attachment.jpg

4.6 Show/hide invoice attachments

A new button that is available on the Exception processing, Pending invoice, and Invoice journal inquiry pages lets you show or hide the attachment viewer.

Hide_attachment.jpg

5. Exception processing

In scenarios where vendor invoices come into Finance and Operations via integration, there must be an easy way for an Accounts payable team member to process exceptions or failed invoices, and to create pending invoices out of failed invoices. This exception processing for vendor invoices is now part of Finance and Operations.

5.1 Exceptions list page

The new list page for invoice exceptions is available at Accounts payable > Invoices > Import failures > Vendor invoices that failed to import. This page shows all the vendor invoice header records from the staging table of the Vendor invoice header data entity. Note that you can view the same records from the Data management workspace, where you can also perform the same actions that are provided in the exception handling feature.

In the next post, I will discuss another strategy that can be adopted to import the AP invoices using the recurring integration and use of a third-party application that can call to a REST endpoint to put the data.
0 comments
5 views

Permalink