Specifics of connecting to the virtual balances table

Introduction

Developers of configurations that automate retail and merchandising activities frequently need algorithms that check the availability of a required product quantity in a warehouse before processing the papers. This is handled efficiently with the query language by connecting a document tabular section with a balances table in order to check balances. This section covers a few issues that developers might encounter during the implementation of this feature.

Checking balances

A query to get the balances of products available in a tabular section has the following format:

SELECT
    SalesOrderContent.Items,
    ItemAccountingBalance.QuantityBalance
FROM
    Document.SalesOrder.Content AS SalesOrderContent
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
        ON ItemAccountingBalance.Items = SalesOrderContent.Items
WHERE
    SalesOrderContent.Ref = &Document

Be sure to pay attention to the following factors. First of all, when a product is not available in the warehouse, the ItemAccountingBalance.QuantityBalance field has the NULL value, so any comparison with it is invalid and returns a result that is treated as the FALSE value. Take this into account when creating a query. For example, to get products whose warehouse quantities might be lower than the quantities in a document, use the following query format:

SELECT
    SalesOrderContent.Items,
    ItemAccounting.Balance.QuantityBalance
FROM
    Document.SalesOrder.Content AS SalesOrderContent
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
        ON ItemAccounting.Balance.Items = SalesOrderContent.Items
WHERE
    SalesOrderContent.Ref = &Document AND
    (ItemAccounting.Balance.QuantityBalance < SalesOrderContent.Quantity OR
        ItemAccounting.Balance.QuantityBalance IS NULL)

This query checks that the warehouse quantity of a product is less than the required quantity or the product is not available at all.

The next important factor is limiting the number of balance calculations. In this example the virtual table of balances first calculates balances for all the products and then connects the products found in the tabular section to the tabular section. To ensure the calculation of balances of only those products that are on the sales order, you can pass the limitation to the balances table (see Using filters in queries with virtual tables). The corrected query is as follows:

SELECT
    SalesOrderContent.Items,
    ItemAccountingBalance.QuantityBalance
FROM
    Document.SalesOrder.Content AS SalesOrderContent
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance(,
            Item IN (
                SELECT Items
                FROM Document.SalesOrder.Content
                WHERE Ref = &Document)) AS ItemAccountingBalance
        ON ItemAcountingBalance.Items = SalesOrderContent.Items
WHERE
    ItemAccountingBalance.Ref = &Document AND
    (ItemAccountingBalance.QuantityBalance < SalesOrderContent.Quantity OR
        ItemAccountingBalance.QuantityBalance IS NULL)

Another important thing is that a document might have multiple rows with the same product. To check balances correctly, the query should be modified so that the total quantity of a product in the document is compared to the balances. The modified query is as follows:

SELECT
    Content.Items,
    Content.Quantity,
    ItemAccountingBalances.QuantityBalance
FROM
    (SELECT 
         SalesOrderContent.Items AS Items,
         SUM(SalesOrderContent.Quantity) AS Quantity
     FROM
         Document.SalesOrder.Content AS SalesOrderContent
     WHERE SalesOrderContent.Ref = &Document
     GROUP BY SalesOrderContent.Items) AS Content
         LEFT JOIN AccumulationRegister.ItemAccounting.Balance(,
             Items IN (
                 SELECT 
                     Document.SalesOrder.Content.Items 
                 FROM
                     Document.SalesOrder.Content 
                 WHERE
                     Document.SalesOrder.Content.Ref = &Document)
             ) AS ItemAccountingBalances 
         ON ItemAccountingBalances.Items = Content.Items
WHERE
    (ItemAccountingBalances.QuantityBalance < Content.Quantity 
    OR (ItemAccountingBalances.QuantityBalance) IS NULL )

As you can see from the query, we started by calculating the total number of each product in the document and only then executed all the operations that involve the comparison of balances.

The last thing to be aware of is displaying information on insufficient product quantity to a user. As we have described in detail in Display of reference fields, in order to display product information to a user, we recommend that you get product presentation using a query and display this presentation to the user. Here is a modified query that additionally gets product presentations:

SELECT
    Content.Items, 
    Content.Items.Presentation AS Presentation, 
    Content.Quantity,
    ItemAccountingBalances.QuantityBalance
FROM
    (SELECT 
         SalesOrderContent.Items AS Items,
         SUM(SalesOrderContent.Quantity) AS Quantity
     FROM
         Document.SalesOrder.Content AS SalesOrderContent
         WHERE SalesOrderContent.Ref = &Document
         GROUP BY SalesOrderContent.Items) AS Content
             LEFT JOIN AccumulationRegister.ItemAccounting.Balance(,
                 Items IN (
                     SELECT 
                         Document.SalesOrder.Content.Items 
                     FROM
                         Document.SalesOrder.Content 
                     WHERE
                         Document.SalesOrder.Content.Ref = &Document)
                 ) AS ItemAccountingBalances
             ON ItemAccountingBalances.Items = Content.Items
WHERE
    (ItemAccountingBalances.QuantityBalance < Content.Quantity 
     OR (ItemAccountingBalances.QuantityBalance) IS NULL )

Then you need to display the Presentation field to the user.

Example:

Result = Query.Execute();
If Not Result.IsEmpty() Then
    Message("Insufficient goods:");
    Selection = Result.Choose();
    While Selection.Next() Do
        Message(Selection.Presentation + ": required: " + Row(Selection.Quantity)
                  + ", available: " + Row(Selection.QuantityBalance));
    EndDo;
EndIf;

Conclusion

The section describes some specifics of checking product balances. We have provided example queries for getting data required for checking balances. You can use these examples when you implement actual queries.