Checking the balances
The general method of monitoring item balances during document posting is as follows: first, write the register records generated by the document without any checks, and then read the balance values from the database.
If a negative balance value is detected, the document cannot be posted. Instead the applied solution should inform the user about the missing materials and undo the posting.
If no negative balance values are detected, a document can be posted without hesitation.
You are actually half-finished with this task: you implemented the generation and writing of document register records. The only thing you still need to do is monitor the results of real-time posting and undo the posting if a negative balance value is found.
In Designer mode
Let us add a script that performs real-time posting checks.
- Add the following lines after the loop that iterates through the query results and before the end of the procedure (listing 14.35).
Listing 14.35. Posting() procedure (fragment)
... EndDo; RegisterRecords.Write(); If Mode = DocumentPostingMode.RealTime Then // Checking for negative balances EndIf; EndProcedureFirst, this script writes the register records.
Then it determines the document posting mode. In the Posting() procedure call, the second parameter (Mode) is the document posting mode, and the value of that variable is compared to the value of the DocumentPostingMode system enumeration. In the event of real-time posting the balance check is applied.
Let us start writing the negative balance check.
Since once again you need to get balance values only for the materials available in the document, let us specify that this query uses the same temporary tables manager (TTManager).
- Add the lines specified in listing 14.36 to the end of the posting procedure.
Listing 14.36. Posting() procedure (fragment)
If Mode = DocumentPostingMode.RealTime Then // Checking for negative balances Query3 = New Query; Query3.TempTablesManager = TTManager; Query3.Text = ""; EndIf;
- Right-click between the quotation marks and open the query wizard.
- Confirm that you want to create a new query.
- Select the BalanceOfMaterials.Balance table and two fields from this table: Material and QuantityBalance.
- In the Condition field, enter the condition specified in listing 14.37.
Listing 14.37. Virtual table condition
Material IN ( SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices) AND Warehouse = &WarehouseThis means that you only get totals for the materials available in the temporary table and only for the warehouse specified in the document.
- Click the Conditions tab, move the QuantityBalance field to the list of conditions, select the Arbitrary check box, and specify that you are only interested in negative balance values (listing 14.38).
Listing 14.38. Query condition
BalanceOfMaterialsBalance.QuantityBalance < 0
- Click OK.
The query text should look as shown in listing 14.39.
Listing 14.39. Query text
// Checking for negative balances Query3 = New Query; Query3.TempTablesManager = TTManager; Query3.Text = "SELECT | BalanceOfMaterialsBalance.Material, | BalanceOfMaterialsBalance.QuantityBalance |FROM | AccumulationRegister.BalanceOfMaterials.Balance( , Material IN ( | SELECT | DocumentMaterialsAndServices.MaterialOrService | FROM | DocumentMaterialsAndServices) | AND Warehouse = &Warehouse) AS BalanceOfMaterialsBalance |WHERE | BalanceOfMaterialsBalance.QuantityBalance < 0";Now you only need to specify the query parameter, iterate through the query results, and display notifications about negative balance values.
- Modify the posting procedure as shown in listing 14.40.
Listing 14.40. Posting() procedure (fragment)
// Checking for negative balances Query3 = New Query; Query3.TempTablesManager = TTManager; Query3.Text = "SELECT | BalanceOfMaterialsBalance.Material, ... |WHERE | BalanceOfMaterialsBalance.QuantityBalance < 0"; Query3.SetParameter("Warehouse", Warehouse); QueryResult = Query3.Execute(); SelectionDetailRecords = QueryResult.Select(); While SelectionDetailRecords.Next() Do Message = New UserMessage(); Message.Text = String(- SelectionDetailRecords.QuantityBalance) + " units shortage for """ + SelectionDetailRecords.Material + """."; Message.Message(); Cancel = True; EndDo;Let us review the added script lines.
In this check the warehouse specified in the document is passed to the query in the Warehouse parameter.
Then the query is executed to retrieve negative balances for materials that are stored in the temporary table and have the appropriate warehouse specified in the Warehouse parameter.
Then the selection of query records is iterated within the loop, and for each record with a negative balance a message is displayed.
Finally, the Cancel parameter of the document posting procedure is assigned the True value, which means that the document is not posted, the transaction is canceled, and the state of data modified in the process of posting is returned to the initial state (as it was before the document posting attempt).