Traditionally, salespersons used to work with paper order forms. They would write down the customer name and address and the items or services required.
Paper order form
In Microsoft Dynamics NAV, the paper document is replaced by a sales and purchase document using a header for the general order information and lines to register the items and services.
The posting process breaks down the information in the document into the journals and posts them, so the end user does not have to worry about this. The application reuses the same posting routines as we discussed in earlier chapters.
Let's look at how the documents and journals tie together by drawing the table and transaction scheme for this:
The first step is creating the document. When we create this Sales Document (Sales Header and Sales Line), nothing is posted. We are only entering the information into the system that can be changed at any time.
When we start the Codeunit Sales-Post (80), the system will create all the journals for us and post them. When we sell an item, the system will create an Item Journal Line, and when we sell a resource, the system creates a Resource Journal Line, and so on.
The Invoice Posting buffer is used to create the entries in the General Journal Line. We already discussed this feature in Chapter 3, Financial Management.
Microsoft Dynamics NAV allows us to create four different kinds of posted sales documents: invoices, shipments, credit memos, and return receipts. We will discuss all these types later in this chapter.
The unique concept of sales and purchase in Microsoft Dynamics NAV is the mirroring of the transaction structure. Once we understand how the sales transactions fit together, it will be easy to understand the structure of a purchase.
Let's demonstrate this by comparing the first fields in Table 37 Sales Line and Table 39 Purchase Line, as shown in the following screenshot:
The fields in both tables are equally numbered and serve the same process even though they use different terminology, for example, field 18, Qty. to Ship (sale) and Qty. To Receive (purchase).
Some fields are different because they don't make sense to be in both processes, for example, Unit Price (LCY) (field 31) in purchase and Customer Price Group (field 42) in sales.
The purchase process also uses the same posting methodology. The purchase header (38) and purchase line (39) tables are posted using the codeunit Purch.-Post (90) in the purchase receipt, invoice, credit memo, and return shipment documents.
Let's have a closer look at the sales process.
The sales process supports six document types that are normalized into two tables, sales header (36) and sales line (37).
Each process can have its own Number Series and has a special card and list page, but they all share the same business logic. Let's discuss the document types:
- Quote: When a customer would like to know the terms and conditions of making a purchase, we can make a quote. This will show all the calculations such as pricing and VAT.
- Blanket Order: This is a pre-order status. When used, we have an agreement with the customer without knowing the exact shipment date.
- Order: This is used for the actual order document.
- Invoice: This can be used in two ways; directly, without a sales order if the company only invoices directly on G/L Accounts, or we can use the invoices to invoice one or more shipments.
- Credit Memo: We can use a credit memo when we credit on a G/L Account.
- Return Order: If a customer returns an item, we can use a return order to reverse the inventory process.
The main process is the order. The other document types are designed to support this. Sales orders can be created directly or via a quote or blanket order. There are two differences between a quote and a blanket order:
- Quotes can only be fully transferred into a sales order, not in parts. For example, a blanket order of 100 items can be split into 10 deliveries of 10 items with different shipping dates.
- A customer with a quote has the possibility to say yes or no. When the answer is no, there will be no transaction. Therefore, quotes are not used in the supply and demand calculation, as we discussed in the previous chapter. A blanket order is a real order. The customer should eventually purchase the complete quantity that was agreed. Therefore, the blanket orders are used in the supply and demand calculation.
Although the quotes and blanket to orders are stored in the same table, the records are physically deleted from the table and inserted using another document type. This is done in codeunits sales-quote to order (86) and blanket sales order to order (87).
When comparing these codeunits in compare tools, such as Beyond Compare or Araxis, we can see that there is a lot of similarity. They both create a new sales order.
When moving a quote to an order, the complete quote is copied and then deleted. A quote can be created from an opportunity in CRM as we discussed in Chapter 4, Relationship Management. Therefore, the opportunity is updated when this happens.
In order to understand the examples in this chapter, we will discuss the most important fields of the sales order. A sales document contains one header and multiple lines.
While the sales header table contains more static registration of information, the sales line has more real business logic, such as price calculation, inventory availability, and VAT. We will discuss how this business logic is normalized.
All document types are uniquely numbered. The primary key fields of the sales header table are Document Type and No..
The sales document contains the following two different customer no. fields:
- Sell-to Customer No.: This is the primary customer no. field, which defines the customer who requested the order to be created. This customer number is used to calculate the discounts.
- Bill-to Customer No.: By default, the Sell-to Customer No. will also receive the invoice. By changing this field to another customer, this will make the invoice print out containing other customer details.
A sales document contains some dates that are used for different purposes:
- Posting Date: This date is used for posting to the various ledgers
- Document Date: This date is used for the accounts receivable
- Shipment Date: This date is for the calculation or the inventory availability
- Due Date: This date is the last date at which the bill-to customer is expected to pay the invoice
Each sales document can contain an almost unlimited number of sales lines. By default, the sales lines are numbered 10000, 20000, 30000, and so on.
The numbering is done using the AutoSplitKey property on the sales line page and the increment cannot be changed. When a user inserts new records between two existing lines, the program will calculate the new number to be exactly between the old values, for example, 10000, 15000, 17500, 18750, 19375, 19687, 19843, 19921, 19960, 19980, 19990, 19995, 19997, 19998, 19999, and 20000. If there is no more room, the system will generate a runtime error message, as shown in the following screenshot:
A sales line can contain a reference to six types of master data defined by the Type field. These types are: Text (blank option), G/L Account, Item, Resource, Fixed Asset, and Charge (item).
The type that we specify here determines which journal will be used later when we post this sales document. However, each line can contain financial information, which will be processed to the general ledger via the posting buffer table.
In the next chapter, we will discuss how to add a new type to this process.
To create a new sales line and start the important business logic in Microsoft Dynamics NAV, we need to know about the following fields:
- Type: This defines the master data type the sales line uses and eventually the journal that will be used during posting
- No.: This is the actual reference to the unique number of the master data type that is used
- Quantity: This is used to calculate the sales amounts for the invoicing and in the case of an item, and also the physical quantity of the changes in inventory
- Outstanding Quantity, Qty. to Invoice, and Qty. to Ship: These fields are designed to use for partial shipping and invoicing of an order
- Unit Price and Unit Cost (LCY): The fields are used to calculate the sales amount and profit
- Line Discount % and Line Discount Amount: These fields are used to determine the discounts
The sales line table has a specific validation flow of functions that is important to understand before making changes to the table. This flow is based on the normal way an end user creates a sales line.
To create a sales line, only four fields are populated and the line is ready to use. After setting the type and choosing a no., the end user types in the Quantity field and if necessary, the Unit Price field.
Let's analyze the C/AL code in the OnValidate trigger of the three fields that can calculate the sales line.
Note
When changing these C/AL routines, make sure to use the Test near, Test far, Do it, and Clean up methods that we discussed in Chapter 1, Introduction to Microsoft Dynamics NAV.
The C/AL code in the OnValidate trigger starts by doing the initial testing, if the change is allowed. After this, the record is cleared and the old values for the No. field and Quantity fields are applied, as follows:
TempSalesLine := Rec; INIT; Type := TempSalesLine.Type; "No." := TempSalesLine."No."; IF "No." = '' THEN EXIT; IF Type <> Type::" " THEN Quantity := TempSalesLine.Quantity;
Then, the sales line inherits the values from the sales header, if required, and the date fields are calculated, as follows:
"Sell-to Customer No." := SalesHeader."Sell-to Customer No."; "Currency Code" := SalesHeader."Currency Code"; ... "Promised Delivery Date" := SalesHeader."Promised Delivery Date"; ... UpdateDates;
When this is done, we see a CASE statement where the master data is acquired. This would be the place where we would move newly added fields from master data to the sales line table.
CASE Type OF
Type::" ":
...
Type::"G/L Account":
...
Type::Item:
...
Type::Resource:
...
Type::"Fixed Asset":
...
Type::"Charge (Item)":
...
END;When this is done, the quantities are calculated and the unit price is calculated.
IF Type <> Type::" " THEN BEGIN
IF Type <> Type::"Fixed Asset" THEN
VALIDATE("VAT Prod. Posting Group");
VALIDATE("Unit of Measure Code");
IF Quantity <> 0 THEN BEGIN
InitOutstanding;
IF "Document Type" IN ["Document Type"::"Return Order","Document Type"::"Credit Memo"] THEN
InitQtyToReceive
ELSE
InitQtyToShip;
UpdateWithWarehouseShip;
END;
UpdateUnitPrice(FIELDNO("No."));
END;The latter is very important for our analysis. After this function, other code is executed but this is not important for this example.
Just like the No. field, the Quantity field also first checks whether the change is allowed. When this is done, the following section of C/AL code is important:
IF Type = Type::Item THEN BEGIN
UpdateUnitPrice(FIELDNO(Quantity));
...
CheckApplFromItemLedgEntry(ItemLedgEntry);
END ELSE
VALIDATE("Line Discount %");In the preceding C/AL code, we should notice again the UpdateUnitPrice function and also the validation of the Line Discount % field.
This field has little C/AL code. When changing the unit price manually, the C/AL code will trigger the Line Discount % field:
TestStatusOpen;
VALIDATE("Line Discount %");
Before going to this field, let's first have a look at the UpdateUnitPrice function we noticed earlier in the Quantity and No. field.
The UpdateUnitPrice function executes the following C/AL code:
IF (CalledByFieldNo <> CurrFieldNo) AND (CurrFieldNo <> 0) THEN
EXIT;
GetSalesHeader;
TESTFIELD("Qty. per Unit of Measure");
CASE Type OF
Type::Item,Type::Resource:
BEGIN
PriceCalcMgt.FindSalesLineLineDisc(SalesHeader,Rec);
PriceCalcMgt.FindSalesLinePrice(SalesHeader,Rec,
CalledByFieldNo);
END;
END;
VALIDATE("Unit Price");
After doing the checks, the sales price calculation routines we discussed in Chapter 2, A Sample Application, are executed. This is codeunit Sales Price Calc. Mgt. 7000.
When this is done, it validates the field Unit Price that we already analyzed. This leads us to one single point; the OnValidate trigger of Line Discount %.
The C/AL code in this OnValidate trigger first calculates the line discount amount based on the unit price and then starts the UpdateAmounts function, as follows:
TestJobPlanningLine;
TestStatusOpen;
"Line Discount Amount" :=
ROUND(
ROUND(Quantity * "Unit Price",Currency."Amount Rounding Precision") *
"Line Discount %" / 100,Currency."Amount Rounding Precision");
"Inv. Discount Amount" := 0;
"Inv. Disc. Amount to Invoice" := 0;
UpdateAmounts;
The UpdateAmounts function completes the creation of the sales line and this is where our quest ends.
The two most important other functions that are executed in this function are the UpdateVATAmounts for VAT calculation and the credit limit check for the customer in CustCheckCreditLimit.SalesLineCheck(Rec).
The VAT calculation in Microsoft Dynamics NAV is not normalized in one application area but redeveloped everywhere. This makes VAT calculation one of the most complex application areas to make changes to.
The VAT calculation is not only done in the sales line, purchase line, and general journal line table, but also in more specific function tables such as the service line. This is done by making a full copy of the C/AL code.
This phenomenon is known as code cloning in computer science. Although code cloning simplifies application design, it is considered bad practice and should be avoided at all times. In this case, it would have been better if VAT would have been calculated in a generic engine.
It is therefore highly recommended not to change VAT calculation in Microsoft Dynamics NAV.
In Microsoft Dynamics NAV, a sales order can be shipped and invoiced directly from the document.
However, not all companies have a combined shipping and invoicing process. Some companies ship the goods first and send the invoice later, most of the time using combined invoicing.
Besides separating the invoice moment from the shipping moment, Microsoft Dynamics NAV also allows a prepayment process. This prepayment process is designed to work on top of the normal invoicing process. This means it does not replace the invoice but instead creates an extra invoice.
This invoice is not created in codeunit sales-post (80) but in codeunit sales-post prepayments (461).
When the order is eventually invoiced, the prepayment invoice is deducted from the invoice amount.
Combined invoicing of shipments can be done manually or using a batch report.
To manually combine shipments on a sales invoice, we can use the Sales-Get Shipment Codeunit (64).
This codeunit can be started from the actions on a sales invoice subpage (47) and displays the sales shipment lines that are not yet completely invoiced.
The C/AL code, however, is not completely within the codeunit; the process starts in the codeunit and runs the page. The page then again starts a function in the codeunit.
The Combine Shipments report (295) can be used to create one invoice for multiple shipments in batch. It works in a similar way as the Combine Invoice report we created in Chapter 2, A Sample Application.
The C/AL code that creates the sales line for the invoice is normalized and used in both codeunit sales-get shipment (64) and combine shipments report (295). The function is located in the sales shipment line table (111) and is called InsertInvLineFromShptLine.
Before we can ship the items we sold, we first need to purchase or produce them. We discussed the production process in the previous chapter, so let's focus on the purchasing process.
Technically, the sales and purchase process are mirrored transactions and the application design is similar. The purchase header table has the same document types: quote, order, invoice, credit memo, blanket order, and return order, and the same posting process.
So instead of going into the similarities, we will discuss the differences.
In Microsoft Dynamics NAV, it is not possible to purchase resources. When we take a closer look at the Type field (5), we can see that the option is left blank:
When selling items that are not in the inventory, it is possible to purchase the items from a vendor and have them directly shipped to the customer. This process is called drop shipments.
This process can be handled manually and using the requisition worksheet.
To create a drop shipment manually, the purchase order should first be created using the Sell-to Customer No. from the corresponding sales order as the shipping address:
When this is done, we can start the codeunit Purch.-Get Drop Shpt. (76) from ACTIONS on the purchase order. This function will show a list of all sales orders for this Sell-to Customer No. regardless of whether drop shipment is possible.
If we select a sales order without sales lines that are marked for drop shipment, we get the following error message:
After retrieving the sales information, the sales line and purchase line table are connected to each other by populating the Purchase Order No., Purch. Order Line No., Sales Order No., and Sales Order Line No. fields.
These fields are numbered 71 and 72 in the sales line and purchase line table.
We introduced the requisition worksheet in the previous chapter when we discussed the planning process. The requisition worksheet can also be used for the Drop Shipment functionality:
This will start the Get Sales Orders report (698), which will filter on all sales lines marked for drop shipment and creates a line in the requisition worksheet table.
This line can be processed by carrying out the action messages. This function will also connect the sales order to the purchase order using fields 71 and 72.
Within the sales and purchase document process, there is a workflow available for releasing and approving a document. This is taken care of by a single status field and two processes.
The Status field (120) in the sales header and purchase header table indicates the status of the process. There are four options: Open, Released, Pending Approval, and Pending Prepayment.
Two of these status fields, Open and Released, are mandatory to use. Pending Approval and Pending Prepayment are optional.
We have already discussed prepayments earlier in this chapter.
Before a document can be posted, it is mandatory to release it. This is done by the codeunits Release Sales Document (414) and Release Purchase Document (415). These codeunits are, as you may have guessed, almost identical.
The codeunit performs a number of tests before setting the status to Released. Let's discuss some of these checks:
- A typical example of Test Near, the customer number should not be blank:
TESTFIELD("Sell-to Customer No."); - There should be at least one sales line with a
Quantity:SalesLine.SETRANGE("Document Type","Document Type"); SalesLine.SETRANGE("Document No.","No."); SalesLine.SETFILTER(Type,'>0'), SalesLine.SETFILTER(Quantity,'<>0'), IF NOT SalesLine.FIND('-') THEN ERROR(Text001,"Document Type","No."); - When the testing is done, some final calculations are implemented. These calculations are document calculations that span over the individual sales lines:
SalesSetup.GET; IF SalesSetup."Calc. Inv. Discount" THEN BEGIN CODEUNIT.RUN(CODEUNIT::"Sales-Calc. Discount",SalesLine); GET("Document Type","No."); END; - The following codeunit calculates the invoice discount:
SalesLine.SetSalesHeader(Rec); SalesLine.CalcVATAmountLines(0,Rec,SalesLine,TempVATAmountLine0); SalesLine.CalcVATAmountLines(1,Rec,SalesLine,TempVATAmountLine1); SalesLine.UpdateVATOnLines(0,Rec,SalesLine,TempVATAmountLine0); SalesLine.UpdateVATOnLines(1,Rec,SalesLine,TempVATAmountLine1);
- At the end of the releasing process, the VAT calculation is completed.
- Releasing a document also calculates the Amount and Amount Including VAT fields on the sales line.
By default, Microsoft Dynamics NAV releases the document automatically. The posting codeunits sales-post (80) and purchase-post (90) contain the following C/AL code:
IF (Status = Status::Open) OR (Status = Status::"Pending Prepayment") THEN BEGIN TempInvoice := Invoice; TempShpt := Ship; TempReturn := Receive; GetOpenLinkedATOs(TempAsmHeader); CODEUNIT.RUN(CODEUNIT::"Release Sales Document",SalesHeader); TESTFIELD(Status,Status::Released); Status := Status::Open; Invoice := TempInvoice; Ship := TempShpt; Receive := TempReturn; ReopenAsmOrders(TempAsmHeader); MODIFY; COMMIT; Status := Status::Released; END;
This code temporarily releases the document by starting the release codeunit but then sets the status back to Open, modifies the records, and commits the transaction. Then, the status is set to Released.
Whenever there is an error afterwards, the status will still be Open since that was the status before the COMMIT.
During the life cycle of our application, many documents will be created. There might come a day when this exceeds the point where some maintenance is required.
In the IT Administration section of the Departments Role Center, we can find a Data Deletion section, which is designed for IT administrators to clean up data, as shown in the following screenshot:
When a sales order is invoiced using Get Shipment Lines or Combined Invoicing, the sales order is not automatically deleted, nor are completely handled blanket orders.
Leaving old orders in the database may lead to large tables. Since these document tables are heavily inserted and modified throughout the working day by many people, this may lead to unnecessary overhead in the database.
Microsoft Dynamics NAV allows users to delete posted shipments and invoices when they are printed.
Although it should be considered carefully, it might be necessary for some companies to periodically clean up this data. Most companies never look at the shipments once the items are delivered to their customers.
Cleaning up these tables will have a positive impact on the performance and maintainability of your system if it reaches the size of roughly 50-100 GB.