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:
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:
To create a new Income & Expense record, we need to fill in the following fields:
BOX
, KM
, MILES
, or DAY
.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.
The Sales Line table (37) has gotten some minor modifications. We have added an extra type for Income and implemented a table relation for the No. field:
This enables us to also create new entries on a sales invoice without having to create an Income & Expense first.
The Sales Line also has a reference to the Income & Expense Entry No. and the Apply-to fields. This enables us to create the Income & Expense Journal Lines in the Sales Post Code Unit.
The sales post code unit has only one change to populate the Income & Expense Journal:
OnRun() ... SalesLine.Type::Income: //* Chapter 7 PostIncome; PostIncome() IF SalesLine."Qty. to Invoice" = 0 THEN EXIT; WITH IncExpJnlLn DO BEGIN INIT; "Posting Date" := "Posting Date"; ... "Source Code" := SrcCode; "Posting No. Series" := "Posting No. Series"; "Dimension Set ID" := SalesLine."Dimension Set ID"; IncExpJnlPostLine.RunWithCheck(IncExpJnlLn); END;
This is done in the same way as the Resource Journal, however, we moved the code that creates the journal line to a function, and this improves readability and upgradability of our code.
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.
In the Storage Price table, we can register prices for different storage activities.
When the price is calculated, the system will filter down in this table to find the price that matches best. For example, if a product has a price for receipt without a warehouse code, this price is used in all warehouses, but if one warehouse code is populated, this warehouse has a special price.
Prices can be differentiated to receipt, shipment, pick, put-away, movement, and storage. The first options are used on the storage documents, the latter when calculating storage cost.
The Income & Expense Code determines which type of Income & Expense will be created for this combination. A storage document can have more than one Income & Expense, for example, a normal receipt line and a customs surplus.
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:
CreateIncExp()
IncExp.SETRANGE("Applies-to Document Type", IncExp."Applies-to Document Type"::"Storage Header");
IncExp.SETRANGE("Applies-to Document No.", StorageHeader."No.");
IncExp.SETRANGE("Applies-to Document Line No.", StorageLine."Line No.");
IncExp.DELETEALL;
WITH StoragePrice DO BEGIN
FoundStoragePrice := FINDSET;
IF FoundStoragePrice THEN BEGIN
REPEAT
IncExpCode.GET(StoragePrice."Income & Expense Code");
IncExp.INIT;
IncExp."Entry No." := 0; //* For Autoincrement
IncExp.Type := IncExpCode.Type;
IncExp."Income & Expense Code" :=
"Income & Expense Code";
IncExp.Description := Description;
IncExp.Quantity := StorageLine.Quantity;
IncExp."Unit Cost" := IncExpCode."Unit Cost";
IncExp."Total Cost" := IncExp.Quantity *
IncExp."Unit Cost";
IncExp."Unit Price" := StoragePrice."Unit Price";
IncExp."Total Price" := IncExp.Quantity *
IncExp."Unit Price";
IncExp."Applies-to Document Type" :=
IncExp."Applies-to Document Type"::"Storage Header";
IncExp."Applies-to Document No." := StorageHeader."No.";
IncExp."Applies-to Document Line No." :=
StorageLine."Line No.";
IncExp."Bill-to Customer No." :=
StorageHeader."Bill-to Customer No.";
IncExp."Gen. Prod. Posting Group" :=
IncExpCode."Gen. Prod. Posting Group";
IncExp."VAT Prod. Posting Group" :=
IncExpCode."VAT Prod. Posting Group";
IncExp.INSERT;
UNTIL NEXT = 0;
END;
END;
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.
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.