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

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.

Field Data Type Description
OrderAccount string This field contains the vendor account id information.
PurchId string It stores Purchase order ID.
ParmId string It contains parm id reference of purchaseparmtable.
DocumentOrigin string It stores Sales Order status.
InventLocationId string It has location information for which Sales Order is created.
InventSiteId string It contains Site information.
Invoicedate datetime It holds the information about invoice date.
InvoiceAmount real It 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.

Field Data Type Description
InvoiceId string This field contains the vendor account id information
InventTransId string It contains the inventory transaction Id of a purchase order process.
ItemId string It stores item id of purchase order lines.
PurchId string It contains information about Purchase order ID.
InventDimId string It stores inventory dimension details Id of purchase order lines.
Invoicedate datetime It holds the information of invoice date.
Qty real It contains the invoiced quantity of purchase invoice lines.
LineAmount real It 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

 

Videos

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

    • Daniela.Mendez says:

      Thank you for your comments! We appreciate it. I would like to invite you to have a demo in a meeting with us where we will give you a walkthrough of all our courses, our group learning Dynamics 365 F&S developer program for companies, and assist you with any questions you may have. If you are interested in our courses, please let us know at training@instructorbrandon.com.

Comments are closed.