The invoicing application

In Chapter 2, A Sample Application, we introduced invoicing for an add-on solution. For the solution in this chapter, we'll take this one step further.

Our company is invoicing different logistics services, such as:

All these costs need to be combined in one invoice. Some customers may require monthly invoicing or some weekly and for incidental customers, we invoice directly. This requires a special module to handle the invoicing.

Let's have a look at the process to see where the invoicing is required:

The invoicing application

Everything that we want to invoice at one time to a customer, we store in a new table that we will call Income & Expense. This is a container where they will be kept until the periodical invoicing is done for this customer.

The Income & Expense records can be created manually by end users or automatically by the system. Let's have a look at them:

Income and expense

To create a new Income & Expense record, we need to fill in the following fields:

After the Income & Expenses are created, we can start the invoicing process. To support this, some minor changes are done in the invoicing part of Microsoft Dynamics NAV and as an example, we choose a slightly different approach compared to Chapter 2, A Sample Application.

Our add-on solution has three levels of automatic price calculation that are more or less identical. We can calculate prices for storage documents, logistics shipments, and routes.

Let's look at the storage prices as an example of how this is done.

The Income & Expenses are created using a Price Calc. Mgt. Codeunit, which we are familiar with from Chapter 2, A Sample Application, only this time we will not update the Unit Price but create the Income & Expenses.

The calculation for storage is done in codeunit 123456710:

FindStorageLinePrice

WITH StorageLine DO BEGIN
  Product.GET("No.");
  StorageLinePriceExists(StorageHeader, StorageLine);
  CreateIncExp(StorageHeader,StorageLine,TempStoragePrice);

END;

The FindStorageLinePrice function will call the standard StorageLinePriceExists function to find the storage prices that match the criteria. For all the storage prices in the filter, it calls the CreateIncExp function:

Each price will create a separate Income & Expense record.

One of the services we are providing is storage. This means that sometimes products can be in our warehouse for several days or even weeks or months. Our customers will be invoiced for the time they use our warehouse space.

Each time we receive a product in our warehouse or move a product to another region or shelf, a storage entry is created to keep track. For invoicing, we also create a Storage Invoice Entry. This is mainly because the inventory handling and invoicing are done on different moments by different persons. The products can be shipped to the customer when we start the invoicing process.

The Storage Invoice Entry is created with a From Storage Date that is inherited from the Storage Date of the Storage Entry. The Storage Invoice Entry also has a To Storage Date that maintains blank until the product leaves the warehouse or moves to another location that might have another price. The Income & Expense Code determines which price will be invoiced and is determined when posting a Storage Document.

The batch report Storage Invoicing (123456703) is used for the creation of the Income & Expenses. Let's have a look at how this is done.

Periodic invoicing

The report only has one Storage Invoice Entry DataItem, which is filtered on Open=Yes.

In the report, all the Storage Invoice Entries are moved to a buffer table first and handled later. There are two important reasons for implementing a solution like this:

When processing the buffer, we first check whether this entry has been invoiced before. If this is the case, we start invoicing from the previous date, if not; we use the From Storage Date.

Then, we check whether the products have already left the warehouse or have been moved. If this is the case, we can close this entry by invoicing until this date; otherwise, we will invoice until the Workdate.

ProcessBuffer()

StorageInvEntry.LOCKTABLE;

WITH TempStorageInvEntry DO
  IF FIND('-') THEN REPEAT
    StorageInvEntry.GET("Entry No.");
    
    IF "Last Invoice Date" <> 0D THEN
      FromDate := "Last Invoice Date"
    ELSE
      FromDate := "From Storage Date";

    IF "To Storage Date" <> 0D THEN
      StorageInvEntry."Last Invoice Date" := "To Storage Date"
    ELSE
      StorageInvEntry."Last Invoice Date" := WORKDATE;

    Date.SETRANGE("Period Type", Date."Period Type"::Datum);
    Date.SETRANGE("Period No.", 1, 5);
    Date.SETRANGE("Period Start", FromDate, 
      StorageInvEntry."Last Invoice Date");
    IncExp."Entry No." := 0;
    IncExp."Income & Expense Code" := "Income & Expense Code";
    IncExp.Type := IncExp.Type::Income;
    IncExp.Description := STRSUBSTNO(Text000, FromDate, 
      StorageInvEntry."Last Invoice Date");
    IncExp.Quantity := Date.COUNT;
    IncExp."Unit Cost" := "Unit Cost";
    IncExp."Total Cost" := IncExp.Quantity * "Total Cost";
    IncExp."Unit Price" := "Unit Price";
    IncExp."Total Price" := IncExp.Quantity * "Unit Price";
    IncExp."Global Dimension 1 Code" := 
      "Global Dimension 1 Code";
    IncExp."Global Dimension 2 Code" := 
      "Global Dimension 2 Code";
    IncExp."Bill-to Customer No." := "Bill-to Customer No.";
    IncExpCode.GET(IncExp."Income & Expense Code");
    IncExp."Gen. Prod. Posting Group" := 
      IncExpCode."Gen. Prod. Posting Group";
    IncExp."VAT Prod. Posting Group" := 
      IncExpCode."VAT Prod. Posting Group";
    IncExp."Unit of Measure Code" := 
       IncExpCode."Unit of Measure Code";
    IncExp."Applies-to Entry No." := "Entry No.";
    IncExp.INSERT;
    
    StorageInvEntry.Open := "To Storage Date" <> 0D;
    StorageInvEntry.MODIFY;
  UNTIL NEXT = 0;

The next step in our code is to calculate the number of workdays between the two dates. This will prevent our customer from paying for storage on Saturday and Sunday. We do this by using the virtual date table. This table contains all dates, weeks, months, quarters and years between January 1 0000 and December 31 9999 and can be very useful in date calculations.

With this result, we can now create the Income & Expense records that will be invoiced later. If the To Storage Date is populated, we close the Storage Invoice Entry.

The data model we use allows us to combine invoicing on all the services we provide for our customers. We can create one invoice that contains handling, storage, and transportation costs for our customers.

This is done by batch report 123456704 Combine Storage & Logistics, which works exactly the same as the report in Chapter 2, A Sample Application.