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:
- Handling costs for storage receipt and shipments
- Storage costs for the period we keep the inventory
- Costs for transporting the products to the end consumer
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:
- Income & Expense Code: This is a reference to the group of Income & Expense.
- Type: This can be either Income or Expense. The former will be used on sales invoices and the latter is reserved for future use on purchase invoices if we decide to hire other companies to handle our logistics.
- Description: This is the description that will be printed on the sales invoice.
- Quantity: This is the number of services that we have done. For example, the number of storage days or number of kilometers or miles in a route.
- Unit Cost/Total Cost: This can be used to calculate the profit of a service.
- Unit Price/Total Price: This is the price the customer will see on the sales invoice.
- Unit of Measure Code: This is a reference to the calculation method such as
BOX,KM,MILES, orDAY. - Applies-to Document Subtype: This is a reference to Storage Header, Registered Storage Header, Logistics Shipment, or Logistics Route. If necessary, this can be expanded to accommodate other add-ons.
- Applies-to Document (Line) No.: This is a reference to the Storage and Logistics documents that this Income & Expense record belongs to.
- Applies-to Entry No.: This is a reference to the Storage Invoice Entry.
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:
- Changing Record Set: This report filters on Storage Invoice Entries, which are open for invoicing. When the Storage Invoice Entry is completely invoiced, we want to change this value. This means that the record set we use is changing during the process. This is something the SQL Server backend cannot handle and this will result in very poor performance. By first moving all records to a buffer table, the filtering will be done on a virtual table that is maintained on the Service Tier rather than SQL Server.
- Locking: If we were to filter on open entries and modify our dataset, it would result in locking more records than necessary. Filtering on a non-clustered index will result in SQL Server moving to Range Locks rather than Row Locks. By reading the actual Storage Invoice Entry one by one using the clustered index, we will make sure that SQL Server only locks the records we use for this process, allowing other users to keep creating new records at the end of this table.
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.