Techno-functional Dynamics 365 F&S Data Model Explanations for Reporting, Part 4 of 25 – Purchase Order Invoice Posting

Techno-functional Dynamics 365 F&S Data Model Explanations for Reporting, Part 4 of 25 – Purchase Order Invoice Posting

blog-4-ERD REPORT

Things have changed a lot since I started blogging over a decade ago.  There are so many wonderful topics to blog and learn on today; it seems like one doesn’t even know where to start.  To make the process more friendly, I’ve introduced a new sort of post — the hybrid video/blog tutorial.  In this series, we will produce a video and blog for every topic covered.  Some very talented developers from my team have contributed their expertise to assist with this content/video approach to ensure that we keep our quality and quantity high.  I hope you all sincerely enjoy the new approaches that we’ve innovated..  And as always, don’t hesitate to reach out to me.. I’m always here to help. — Brandon Ahmad

Purchase Order Invoice Posting

In this part of the blog series, we look at the techno-functional process of purchase order invoice posting. We will explain the data models so you will get a clear understanding of the functional and technical aspects of the Dynamics 365 process for purchase order invoicing.

In part 3 of this blog series, you learned how to post a product receipt which updates the ‘physical’ inventory values while leaving the ‘financial’ values unchanged. Invoicing a purchase order not only increases the open vendor balance but also increases the financial values of the inventory and posts the transaction to the appropriate General Ledger tables. In Dynamics 365, posting the purchase order invoice is the final step of creating the purchase order. The entire process of purchase order completes when all the lines of the purchase order are invoiced and the transaction is posted.

Reminder – This is a very important step for your bookkeepers and accountants. They cannot reconcile General Ledger without this step being completed and are likely to become grumpy! 

For more information about vendor invoice or purchase invoice in Dynamics 365, a good overview article from Microsoft can be found here.

Now, let’s walk through the quick Functional process of purchase order invoicing.

Purchase Order Invoice Posting: Dynamics 365 Functional Walkthrough

Here is the functional process flow for a process of purchase order invoice posting:

 

Now let’s perform the function.

Step 1:

First, navigate to ‘All purchase orders’ by clicking on ‘Modules’ > ‘Accounts payable’ > ‘Purchase orders’ > ‘All purchase orders.’

Purchase Order Invoice Posting

Step 2:

After selecting the purchase order, select the ‘Invoice’ tab. Under the ‘GENERATE’ group click ‘Invoice’ to create the purchase order invoice.

Purchase Order Invoice Posting - Step 2Step 3:

After that, first enter the actual invoice ‘Number’ in the field under ‘INVOICE IDENTIFICATION’. Second, select the ‘Invoice date’ from the field under the ‘INVOICE DATES’. Finally click on ‘Post’ to post the invoice receipt.

Purchase Order Invoice Posting - Step 3

Step 4:

Now go to the ‘Invoice’ tab and click on ‘Invoice’ under the ‘JOURNALS’ group to check the posted invoice receipt.

Purchase Order Invoice Posting - Step 4

If you are a developer wishing to learn more then it may be wise to check out our Boot Camp, which takes you from zero to hero with some serious hands-on lab action.

Step 5:

After that, you will be directed to ‘Invoice journal’. Note that the ‘Overview’ option is active (underscored) by default. So go to ‘Preview/Print’ and click on ‘Original preview’ to open the invoice receipt.

Purchase Order Invoice Posting - Step 5

Now, let’s have a look at the technical aspect of this process.

Purchase Order Invoice Posting: Dynamics 365 Technical Walkthrough

When posting purchase order invoices, there are two primary tables you will be posting to:  “Vendinvoicejour” and “VendInvoicejour.” However, other table updates occur when you post the invoice receipts.

Entity Relationship Diagram (ERD)

The Entity Relationship Diagram (ERD) is included in this walkthrough as a quick look at the overall structure of the purchase order invoicing process.

Here is the Runnable class(job), which will post the invoice of a purchase order.

class ibPurchaseOrderLearningDemo
{        
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        PurchId         purchId;
        PurchTable      purchtable;
        PurchFormLetter purchFormLetter;
        
        purchId         = "00000275";               //replace this Purchase order number with yours.
        purchtable      = PurchTable::find(purchId);
                
        //Validate if Purchase order exist.
        if(!purchtable)
        {
            throw error(strFmt("The purchase order does not exist.")); 
        }
        
        purchFormLetter = purchFormLetter::construct(DocumentStatus::Invoice);
        purchFormLetter.showQueryForm(false);
        purchFormLetter.update(
            purchTable,
            strFmt("PINV-%1", purchtable.PurchId),   // use your Invoice number in this parameter
            DateTimeUtil::date(DateTimeUtil::utcNow()),
            PurchUpdate::All, 
            AccountOrder::None, 
            false, 
            true);
        info(strFmt("Purchase Invoice posted successfully."));
    }
}

Let’s start with the two primary tables: VendInvoiceJour and VendInvoiceTrans.

The PurchLine table contains all purchase order lines, regardless of whether they have been posted or not. VendInvoiceJour and VendInvoiceTrans make it possible to run reports based on invoice status, invoice data regardless of the purchase order status, or by joining it with other tables’ data.

VendInvoiceJour

This table stores the header information of the Purchase Order Invoice. In Dynamics 365 F&S, this is also called the master table for this record.

This table:

  • has the vendor account details of posted purchase orders, which includes vendor balances and transactions.
  • is helpful for finding posted purchase order details, which are very useful for reporting purposes or for finding already invoiced purchase orders.
  • contains charge details and financial transactions can also be tracked from this table.

The VendInvoiceJour report can be found in the Application Object Tree (AOT) by navigating to Accounts payable > Reports > Statistics > Invoice > Invoice journal.

More information on report filters and how to work with the reporting tools can be found here.

Now we will go through some important fields of VendInvoiceJour with a few of the technical details.

FieldData TypeDescription
OrderAccountstringThis field contains the vendor account id information.
PurchIdstringIt stores Purchase order ID.
ParmIdstringIt contains parm id reference of purchaseparmtable.
DocumentOriginstringIt stores Sales Order status.
InventLocationIdstringIt has location information for which Sales Order is created.
InventSiteIdstringIt contains Site information.
InvoicedatedatetimeIt holds the information about invoice date.
InvoiceAmountrealIt contains information about final invoice amount.

 

This table is important for reporting and tracking purposes by a vendor. For example, if you want to see a report or list of all invoices for a certain vendor between specific dates, this is the table that would aggregate that information into an easily read report.

VendInvoiceTrans

The line-item details of purchase orders are populated in this table. You can also view the detailed transaction information for the line items from within this table. The VendInvoiceTrans table holds all of the line-item information for every invoice. It is therefore important to understand what fields can be pulled in a report for the invoices, and that it ‘hooks up’ to the VendInvoiceJour table and the PurchLine table by PurchId. Together, these three tables hold the most important data for reporting and BI functions. This table contains:

  • the information of posted purchase order line-items.
  • the financial transactions of purchase order line-item details, which makes it very easy when running reports or searching for specific financial inventory transactions on posted purchase orders.
  • Invoice transaction details searchable by using the purchase order number or invoice id.

Now we will go through some important fields of the VendInvoiceTrans with a few of the technical details.

FieldData TypeDescription
InvoiceIdstringThis field contains the vendor account id information
InventTransIdstringIt contains the inventory transaction Id of a purchase order process.
ItemIdstringIt stores item id of purchase order lines.
PurchIdstringIt contains information about Purchase order ID.
InventDimIdstringIt stores inventory dimension details Id of purchase order lines.
InvoicedatedatetimeIt holds the information of invoice date.
QtyrealIt contains the invoiced quantity of purchase invoice lines.
LineAmountrealIt contains information about invoiced amount for each invoice line.

Summary

In this post, we

  • functionally introduced the Purchase Order Invoice posting process.
  • learned the process for invoicing, posting, and reviewing the purchase orders in Dynamics 365 F&S.
  • saw the runnable code that posts the invoice and closes it out.
  • examined the technical data model and table fields behind invoicing a purchase order.
  • explored the main tables involved in invoicing a purchase order in Dynamics 365 Finance and Operations.

We sincerely hope that you enjoyed part 4 of our exciting series on the Dynamics 365 data model. We aim to provide quality service at all times.  And as always, if you need to reach me, you know how to get in touch by reaching out to me here.  — Brandon Ahmad, founder of InstructorBrandon and Dynatuners

 

Leave a Reply

Your email address will not be published. Required fields are marked *