Many times before I have made changes to the salesFormLetter classes and every time I went in there I was afraid ( as a figure of speech ) of the reArrange method. The whole thing was clear to me from a functional point of view, but the technical side of it was another pair of sleeves. As for today, I needed to add some additional functionality to the sales invoice grouping and there for I had no other options than to start digging into the code for rearranging invoices. Well so the goal of this article is clear by now, so let’s start.
For the post, I will go through the code of rearranging invoice for 2 sales orders with a different customer account but with the same invoicing account.
When posting the invoices via Accounts Receivable à Setup à Sales Update à Invoice, we see the following when we have selected the two sales orders for invoicing.
SalesParmTable table before rearrange
Before rearranging, let’s take a look at the tables in SQL server to see what’s going on in there. As we can see in the following screen, the SalesParmTable table contains the following data for the current run. We can see that both orders have a SalesParmTable record in there.
Every time an instance of the SalesEditLines form is ran, a system sequence number is fetched to log every action that will be done for the current instance. This way we have a history for the invoice and how it has been grouped / posted. The TableRefID field will be an important aspect of the rearranging as to be seen later on.
SalesParmLine table before rearrange
The following data is currently in the SalesParmLine table. Here you will find a line for every related sales order line to be posted. Here we also see the references to the sales orders and lines and also important is the reference to the SalesParmTable records in the TableRefId field.
Hitting the rearrange button
Now we start rearranging. This happens in the SalesFormLetter_Invoice class, Method rearrange. Except from the check if the code is executed on the server, this is the first interesting piece of code.
This calls the SalesSummary classes. These are used here to build the query object with the invoice lines to be processed. So let us step into that.
In this case, the construction of the SalesSummary class will be this case:
Additionally, inside the new method, the SalesSummaryFields is built to determine which fields will be used to sum by. The setup of these fields can be found here: Accounts Receivable à Setup à Parameters. Tab page
Updates, Button Summary update parameters.
After the construction is done, the method will be called to create a query object containing the invoice lines to be posted. The actual query building happens on the SalesPurchSummaryModel_Account class.
Basically the related order table is added and all the fields that are in the update parameters are added as sort fields to begin with. (Detail : It aren’t actually sort fields, but the ordering mode on the query is set to group by)
Looping the SalesParmSubTables
After the query is built, we start looping the SalesParmSubTables and SalesParmSubLines. Important to note is that the subtables are actually a sort of copy of the parm tables to do the selections and to be able to alter the SalesParmTables while processing. At this point, this is the contents of the tables is like this:
Interesting thing to see is that the TableRefID fields are used to link the parm / sub tables together. At this point we arrive at the following code with the first SalesParmSubTable.
As we are doing this with the first line (VO000019) the UpdateParmTable is not executed here. What does happen is that a new SalesParmTable is created with a new TableRefId. (This parm table will be used to group the others on).
Now we have a SalesParmTable that will be the one where the lines will be grouped together on. The next step will be the code that moves the parm line from the original (TableRefId 00006286) to the new SalesParmTable. (TableRefId 00006288) and marking the SalesParmSubTable to be linked to the new SalesParmTable.
Now the second line from the second order is fetched and processed. First difference here is that the createNewJournal method will return false. When peeking in the insides of that we come to the following code.
This implicates that there will be no second SalesParmTable will be added. The SalesParmLine record will just be moved to the summary SalesParmTable.
The result after the second line is processed is this :
Now both the SalesParmLines and SalesParmSubLines are grouped on the newly created SalesParmTable.
What happens next is the splitting functionality for sites and delivery information but I will not go in the details of this here.
When the grouping is done, the SalesParmTables that have no reference anymore in the sub tables will be deleted. So the two lines are moved to a newly created SalesParmTable and then the originating SalesParmTables are erased.
The result is one invoice with two lines on it. Additionally, it is also possible to check the data in the SalesParmLines and SalesParmSubTables to check the history of the grouping.