Performing movements in registers.

In my video tutorials, I often say that the 1C base must be prepared for closing the period and reporting. And one of the important points of such preparation is the control of negative balances of goods, materials and finished products. What reports should be used to check the status of inventory accounts in 1C: Accounting? Let's look at some of them.

1. Report "Turnover and balance sheet for the account"

Then we carefully review the report and analyze the errors found.

The balance sheet is convenient in that it allows you to evaluate not only the presence of negative quantitative balances, but also to detect other problem situations:
- quantitative balance of goods and materials without amount;
- total balance without quantity;
- negative balance.
However, if a large number of nomenclature positions are involved in accounting, then such a check can be quite laborious. In addition, SALT will have to be generated for each accounting account separately (10, 41, 43), which also somewhat complicates the work process.

2. Report "Control of negative balances"

Specify the period, organization and generate a report.

Only those nomenclature positions for which a negative quantitative balance is found are included in the report. A big plus is that the data on all inventory accounts are analyzed. Working with the report, in my opinion, is more convenient than with OSV.
But there is also a minus - the report allows you to control only negative quantitative residues, leaving behind the scenes other problems that can be detected by SALT.

3. Report "Analysis of subconto"

We select the “Nomenclature” subconto, we check that the display of quantitative indicators is enabled in the report settings.

Subconto analysis is good because it allows you to get information about the movement of goods and materials for all accounting accounts. For example, to track situations when goods arrived on one accounting account, and were sold from another.

However, with a large number of items, it can be difficult to analyze the data.
I talked about working with this report in more detail in the video tutorial How to work with the Subconto Analysis report in 1C - VIDEO.
Thus, each of the considered reports has its pros and cons. In work, I would recommend combining them:
- find gross errors in the report "Control of negative balances";
- then view the WWS for all inventory accounts;
- to identify the reasons for the incorrect balance, use the report "Analysis of subconto".
I also analyzed interesting examples related to finding and correcting errors in accounting for goods and materials in two useful videos:

Standard reports on commodity balances in the program "1C: Trade Management, ed. 10.3" do not signal the user about the need to order any product.

Moreover, if there were no movements for the product during the report period (i.e., there is no initial balance, income and expense), then the product will not be shown in the report at all!

To always maintain the optimal balance of goods in the warehouse, you can use the "Order point" mechanism. The essence of the mechanism is that you specify in the program the minimum quantity of goods that should be in stock. After that, in a special report, the system will show the product that needs to be ordered and calculate the required quantity.

The indication of the minimum balance of goods in the warehouse is made by the document "Setting the values ​​of the reorder point".

Menu: Documents - Planning - Set reorder point values

Create a new document by clicking the Add button. The document specifies all the products whose optimal quantity you want to support. Fill in for each line:

• Product.
• Characteristic - it is indicated if the product has characteristics (colors, sizes, etc.).
• Method of determination - a method for calculating the minimum and optimal balance. Choose one of 4 ways:
  1. Fixed - values ​​are entered manually by the user.
  2. Average lot size - values ​​will be calculated based on the average purchase volume of the goods.
  3. Optimal order size - the values ​​will be calculated based on the sales of the product.
  4. Do not control - values ​​are not set, the remainder is not controlled.
• The reorder point values ​​are the optimal quantity of goods for the "Fixed" method.
• Safety stock - the minimum quantity of goods for the "Fixed" method.
• Reorder Point Value % - percentage that will be used to calculate the optimal quantity (for the "Average Lot Size" and "Optimal Size" methods).
• Safety stock % – the percentage that will be used to calculate the minimum quantity (for the "Average Lot Size" and "Optimal Size" methods).
• Warehouse - you can specify the warehouse, the balances of which will be controlled. If the warehouse is not specified, then the total balance in all warehouses will be monitored.

An example of a completed document:

To analyze stock balances and the need to order goods, use the "Order Point Analysis" report.

Menu: Reports - Purchasing - Purchasing Analysis - Reorder Point Analysis

The report for each product displays the following information:

• The reorder point value is the optimal quantity in stock (from the reorder point setting document).
• Safety stock is the minimum quantity in stock (from the reorder point setup document).
• Remaining - the current balance of this product.
• Late receipts - the expected quantity of goods that were not received on time.
• Expected receipts - the expected quantity of goods, the delivery date of which has not yet arrived.
• Recommended Purchase Quantity - the amount of goods recommended by the program to be purchased. It is calculated as the optimal amount minus the current balance. Ordered goods do not reduce this quantity, as the order may not arrive.
• Delivery time (in days) - the average delivery time of the supplier.

All products are divided into three categories:

1. No need to order - the balance of the goods is more than optimal, no need to order. For example, the balance of TVs is 80 pieces, and the optimal number is 50 pieces. TVs are not yet required.
2. It is recommended to order - the balance of the goods is less than the optimal, but more than the minimum quantity. It means that the goods need to be ordered, but the timing is not critical. For example, the balance of laptops is 25 pieces, which is less than optimal, so the program recommends purchasing 10 pieces (up to 35 - the optimal balance). At the same time, the minimum balance (15 pieces) is still supported.
3. Required to order - the product, the balance of which is less than the minimum value, it urgently needs to be ordered. For example, the minimum balance of phones in the warehouse is 50 pieces, and there are only 10 pieces in stock. The product urgently needs to be ordered, while the program recommends purchasing 110 pieces at once (up to 120 - the optimal quantity).

Note: to fill in the delivery time, you need to put down the main supplier and indicate its delivery time. The main supplier is filled in the product on the "Additional" tab. The delivery time is indicated in the counterparty card on the "As a supplier" tab below.

Ordering the necessary goods. Stock replenishment

You can place orders for the necessary goods directly from the report by clicking the "Orders" button:

A form for creating orders will open:

The form contains all the goods, the balance of which we track. Goods are divided by main suppliers, respectively, orders will also be created for the main suppliers.

For each product it says:

• Recommended Quantity - the quantity that the program recommends to purchase.
• Ordered Quantity – The quantity of the product that has already been ordered but not yet received.
• Order – quantity to be added to the order (difference between recommended and ordered quantity).

You can change the quantity in the "Order" column to your liking.

By clicking the "Execute" button, the program will create an order for each supplier.

On the "Orders to suppliers" tab, you can view all created orders:

Each order you can open (double click), post, print or delete if it was created by mistake.

After creating and posting orders, all ordered goods will be reflected in the report in the "Expected arrival" column:

There are two kinds of situation in which the adjustment of residue control is necessary.

First situation. Sometimes accountants have to deal with the problem of the impossibility of writing off materials or goods in the 1C 8.3 program due to their absence in accounting, although in fact they are available. And the accountant needs to urgently issue documents for the shipment of materials or goods:

This situation is possible if the organization has just started accounting in 1C 8.3 or the data is entered into the program out of time.

The second situation is when the accountant writes off materials that are not available in the warehouse. And the accountant draws up shipping documents, which is not very good for the company's reputation.

Residual control setup

In the 1C 8.3 Accounting program, there is a balance control setting. And when creating a database in the default settings, it is allowed to write off goods that, according to accounting data, are not in the program.

Balances are controlled in the context of a specific organization and warehouse in all types of documents: sales, requirement-invoice, transfers, etc., where accounts with the Warehouse analytics are involved.

Important! In order to control the balances at the warehouse level, in 1C 8.3 it is necessary to install warehouse analytics in the accounts of goods or materials.

Thus:

• For the first situation, you need to check the box and enter all the balances of the materials into the database. And it is desirable after that to enter the receipt of goods and materials in a timely manner.
• For the second situation, you must uncheck the box to prohibit the write-off of missing goods (materials).

How to disable balance control in 1C 8.3

To remove the ban on posting documents with missing goods, you need to go to the settings of the 1C 8.3 program in the Administration section - then Settings for posting documents:

set the checkbox Allowed to write off inventories in the absence of balances according to accounting data:

Control of balances in the context of warehouses

To prevent negative balances in warehouses, set the analytics By warehouses. You can enable warehouse analytics in the accounting settings: section Administration - then Accounting settings. Choose Chart of accounts setup and in inventory, click on By nomenclature and warehouses (by quantity):

Set the flag in the new window Warehouses (storage places). This checkbox will affect the control of balances in the context of warehouses:

• If the checkbox is unchecked, the control of balances is carried out in the context of organizations;
• If the checkbox is checked - in the context of the organization and a specific warehouse:

Control of negative balances in 1C 8.3

If it is not possible to uncheck the box or there are reasons to keep records with negative balances, a report is implemented in 1C 8.3 Accounting that allows you to control negative balances.

Report Control of negative balances in 1C 8.3 is located in the section Warehouses - then Control of negative balances:

The report can be generated for a certain period with details on documents, warehouses, nomenclature and other accounting objects. And also display data for a certain warehouse or nomenclature using selection:

Important! The Negative Balance Control report shows negative balances only for the specified period. If there were write-off documents before the beginning of the report, then negative balances on them will not be included in the report.

To change the report settings, use the Show settings command. Navigating through the settings tabs Grouping, Selection, Additional fields, you can give the report the desired look:

The site can be found on the configuration 1C Accounting 8.3.

More about setting up the program 1C 8.2 (8.3) Prohibition of write-off of inventories in the absence of balances how it affects financial results, see the following video:

This article is intended for 1C implementers - and especially for those who are preparing for Certification on 1C: Platform Specialist.

Today we will analyze 2 methods for controlling balances - and not only balances in the warehouse, but also, for example, mutual settlements (“what is the current debt of the client and is it possible to ship goods to him”)

Both methods are used both in typical configurations and in the Qualification Tasks. And since there are two you need to clearly understand when the “new” technique is applicable, and when only the “old” one.

This is basic knowledge for 1C programmers, we recommend that you do not leave gaps in such areas. You must go to study 15 minutes :)

Formulation of the problem

Let's take a simple configuration with the documents "Receipt of goods" and "Sale of goods":

To account for balances, the accumulation register “Free balances” is used:

When posting the document “Receipt of goods”, the following movements-receipt are performed:

Procedure HandlingPerforming(Failure, Mode)

For Each TekRowProducts From Products Cycle
Movement = Movement.FreeRemains.Add();
Movement.MovementType = MovementTypeAccumulation.Incoming;
Movement.Period = Date;
Movement.Nomenclature = CurrentLineProducts.Nomenclature;
Movement.Quantity = CurrentStringProducts.Quantity;
EndCycle;

EndProcedure

Processing of the posting of the Goods Receipt document was performed using the movement designer and is of no interest, since stock control is not needed upon receipt at the warehouse.

Sometimes control of balances is also implemented for the document "Receipt of goods" - so that when the posting is canceled or the document is reposted, a negative balance is not formed.

For example, the warehouse received 10 new LG TVs, 6 of which were sold. If there are 10 pieces in the receipt document. fix by 5 pcs. - a negative remainder "minus 1 piece" is formed.

In typical UT 11, such control is enabled using the functional option “Control goods of organizations when canceling receipts”.

When posting the document "Sales of goods" it is necessary to organize the control of residues. If there is not enough stock left, the document is not posted and a diagnostic message is issued. This is the problem to be solved.

We are deliberately working on a simple task where the cost of write-off is not calculated. This will allow us to focus on the nuances of residual control.

Note– the algorithms presented below are designed for learning and should be as clear as possible.
They can be optimized, but then the "comprehension coefficient" will be lower, so in this article we do not stop there.

Naturally, you can optimize them yourself, or take our 1C Acceleration and Optimization course :)

As you already understood, the problem can be solved in two ways. Let's start with the methodology that has been used since the days of 1C:Enterprise 8.0.

Old Residue Control Method

The principle of the old method of residue control is as follows: check whether there is a balance of goods in the required quantity. If there is, we write it off, if not, we report an error..

The algorithm in the old technique consists of several blocks:

1. The request receives the balance of goods and document data
2. The cycle controls the sufficiency of goods
3. If there are not enough goods, then the document is not posted
4. If there are enough goods, movement-expenditure is performed

This is what the code looks like:

// 1. Clean up old register movements
Movements.FreeRemains.Write = true;
Motion.Record();

// 2. Retrieve document data and register balances by query
Request = New Request;
Request.Text =
"CHOOSE

|PUT Products
| FROM
|WHERE
| Products.Link = &Link
|GROUP BY
| Products. Nomenclature
|INDEX BY
| Nomenclature
|;

|CHOOSE
,
| REFERENCE REPRESENTATION(Products.Nomenclature) AS NomenclatureRepresentation,
| Goods.Quantity AS Quantity,
| IS NULL(Remainders. Quantity Remaining, 0) AS Remaining
| FROM
| Products AS Products
| LEFT JOIN
| &Moment of time,
| Nomenclature B
| (CHOOSE
| Products.Nomenclature AS Nomenclature
| FROM
| PO Goods.Nomenclature = Remains.Nomenclature";
Query.SetParameter("MomentTime", MomentTime());

// 3. Traverse query results

// 4. Checking for the sufficiency of goods
Scarcity = SampleProducts.Quantity - SampleProducts.Remainder;
If Deficit>0 Then
Rejection = true;
Message.Text = "Product "+SelectionProducts.NomenclatureRepresentation+" is not enough in the amount of "+Deficit+" pieces.";
Message.Notify();
EndIf;

// 5. Jump to the beginning of the loop if there were errors
If Failure Then
Continue;
EndIf;

// 6. Execution of movements in registers
Movement.Period = Date;

EndCycle;

// 7. Setting the flag for recording movements at the end of the transaction
Movements.FreeRemains.Write = true;

EndProcedure

Let us comment on the key points of the algorithm.

1. Cleaning up old register movements

Below in the algorithm there will be a request for the remainder of the register.

If the current document was previously posted, then there is probability of getting old document movements in the query is a serious problem.

When is such a situation possible? When is the document date moves forward.

Let's use an example to show what this will lead to:

1. The rest of the table lamps 10 pcs.
2. The document dated 02/16/17 is being carried out, we write off 6 lamps
3. The date in the document changes to 02/17/17 (the date can be shifted at least 1 second forward), we will retransmit the document.

If you do not clean up the movements, the system will report a shortage of 2 pieces. Why? Yes, because the old document movements wrote off 6 out of 10 existing lamps. Next, the system tries to write off 6 more pieces, and there are only 4 on the balance.

The problem is solved in 3 lines of code:

• The recordset is being cleared (it may have been read on the form or in previous handlers)
• The record set has the "Record" flag set.
• Recording of all sets that have the "Write" flag set

Strictly speaking, we can control the cleanup of movements when posting documents:

The option of deleting moves when canceling a hold is the recommended one - we have control over when to actually delete moves.

2. Obtaining document data and register balances by request

The request consists of two packets:

• In the first one, the grouped data of the tabular part is obtained - a temporary table is created
• In the second query, the balances from the register are attached to the document data.

What you should pay attention to in this request:

1. When creating a temporary table, the field is indexed, on which the join will be performed further - this is done for optimal performance
2. The moment of receipt of the balances - correspond to the position of the document on the time axis
3. There may be no remainders in the register - therefore, a left join is performed and the ECTNULL function is applied to the "Quantity" resource - the NULL value is reduced to zero.

3. Bypass query results

The developed query contains the grouped data of the document and balances by item positions.

Loop through the result of this query.

4. Checking for the sufficiency of goods

We determine the shortage of goods.

If the deficit is greater than zero, then the product is in short supply:

• Issuing a diagnostic message
• We set the "Failure" parameter of the processing of the conduction to the value "True"

If "Rejection" is equal to "True", then the result of the document posting transaction will not be fixed. In simple terms, this is a command to the system not to post this document.

5. Go to the beginning of the loop if there were errors

If there were errors at this or previous steps of the cycle (Failure = True), then it makes no sense to form movements. All the same, they will not be written to the database.

6. Performing movements in registers

If the check of balances was successful, we form the movement-expense.

7. Setting the flag for recording movements at the end of the transaction

If this flag is not set, the movements will NOT be recorded.

At the end of the document posting transaction, only those sets of records that have the "Record" flag set are written.

For the sake of fairness, we note that setting the “Record” property of a recordset makes sense under one condition - the document property “Record movements during posting” must have the value “Record selected”:

However, it is the “Write Selected” value that is the de facto standard:

• It is used in standard solutions
• Set by default when creating new documents.

Another value of the property - “Write modified” is obsolete and practically does not occur in modern configurations.

New Residue Control Method

The new methodology uses the principle: we write off the necessary goods, then we check whether there are negative balances for the goods of the document. If so, then you need to roll back the posting of the document.

As you can see, the fundamental difference in the moment of control of residues:

• The old technique - first check the balance, then write off
• A new technique - first we write off, then we check the balance.

As a result, the program code will look like this:

Procedure Handling Posting(Failure, Posting Mode)

// 1. Retrieve document data by request
Request = New Request;
Query.TempTableManager = NewTempTableManager;
Request.Text =
"CHOOSE
| Goods. Nomenclature AS Nomenclature,
| SUM(Items.Quantity) AS Quantity
|PUT Products
| FROM
| Document.Sale of GoodsServices.Goods AS Goods
|WHERE
| Products.Link = &Link
|GROUP BY
| Products. Nomenclature
|INDEX BY
| Nomenclature
|;
|////////////////////////////////////////////////////////////////////////////////
|CHOOSE
| Goods. Nomenclature AS Nomenclature,
| Goods. Quantity AS Quantity
| FROM
| Goods AS Goods";
Query.SetParameter("Link", Link);
QueryResult = Query.Execute();

// 2. Formation of movements-register consumption
Movements.FreeRemains.Clear();
SelectionProducts = QueryResult.Select();
While SelectionProducts.Next() Loop
Movement = Movement.FreeRemains.AddExpense();
Movement.Period = Date;
Movement.Nomenclature = SelectionProducts.Nomenclature;
Movement.Quantity = SelectionProducts.Quantity;
EndCycle;

// 3. Writing movements to the database
Movements.FreeRemains.Write = true;
Motion.Record();

// 4. Query that receives negative balances from the register
Request.Text =
"CHOOSE
| Remains. Nomenclature AS Nomenclature,
| REFERENCE REPRESENTATION(Remains.Nomenclature) AS NomenclatureRepresentation,
| -Balances.QuantityBalance AS Deficiency
| FROM
| Accumulation Register.FreeRemains.Remains(
| &Moment of time,
| Nomenclature B
| (CHOOSE
| Products.Nomenclature AS Nomenclature
| FROM
| Goods AS Goods)) AS Remains
|WHERE
| Remains.QuantityRemainder< 0";

ControlBoundary = New Boundary(InstTime(),BorderType.Including);
Query.SetParameter("PointInTime",ControlBorder);
QueryResult = Query.Execute();

// 5. Output of messages about the lack of goods
If Not QueryResult.Empty() Then
Rejection = true;
FetchErrors = QueryResult.Select();
While FetchErrors.Next() Loop
Message = New MessageToUser;
Message.Text = "Product "+SelectionErrors.NomenclatureRepresentation+" is not enough in the quantity "+SelectionErrors.Deficiency+" pcs.";
Message.Notify();
EndCycle;
EndIf;

EndProcedure

Let's analyze the key points of the algorithm.

1. Receiving document data by request

This query is needed to group the data of the tabular part of the document.

Note that a temporary table is created in the first query of the batch - it will be used in the next query as well. This is possible thanks to the temporary table manager that was created for this query.

2. Formation of movements-flow register

In the cycle, data is written from the document to the register - that is, an unconditional (without verification) write-off of goods is performed.

3. Recording movements in the database

In order for the balances in the register to change, the movements must be recorded.

4. Request that receives negative balances from the register

And now, with a simple query, we select negative balances for the goods of the document.

It is here that the temporary table created at the first step is used - a condition is imposed on the nomenclature (for this we do not create a new object of the "Request" type, but use the one created earlier).

Pay attention to how the moment of time is transmitted - the data type "Border" is used. The balances must be received at a point in time immediately AFTER the current document.

Was it possible to get residuals without a border, for example, by adding 1 second to the document date?

No! Indeed, in one second there can be a large number of documents. Therefore, the only correct option is to use the “Including” border type.

5. Output of messages about the lack of goods

If the query result is not empty, then there are negative balances - in this case, the document is not posted and all errors are reported.

Benefits of Residue Control with the New Method

So, both algorithms solve the same problem.

The difference between the algorithms is visible, but the benefits are not obvious.

So let's highlight them:

1. No need to clear old document movements. In fact, this is the operation of writing an empty set of movements to the database and deleting existing movements - these are quite resource-intensive operations.
2. A query that receives data on negative balances refers to only one table - there is no need to do a left join with the document data and use the "ISNULL()" function

In addition, in the normal course of business processes, the user specifies a quantity that does not exceed the balance in the warehouse.

In this case, the second request will not return any data and the document will be posted as quickly as possible.

Are those milliseconds really that important?

On databases with a small amount of data and users, the difference will not be noticeable. But in busy systems with dozens of users, the cost of every millisecond is high.

In addition, at the 1C: Platform Specialist exam, it is imperative to use a new way to control balances, if a specific task allows it.

Ok, so you should always use a new technique, right?

No, it's not!

The new methodology can only be used if there is all the necessary data in the document itself to post the document.

That is, to obtain data, you do not need to access the registers by which the control of balances is carried out.

So, for example, if the amount was also taken into account in the “Free Balances” register, then the old control method would have to be used.

By the way, in the standard "1C: Trade Management 11" the balance control is implemented according to the new method, and in "1C: Accounting 8" - according to the old method.

But that is not all!

The above algorithms can only be used for educational purposes. The problem is that they do not take into account managed locks, which must be applied if more than one user is working in the system.

Blocking for both methods of residual control is devoted. Also in this article, we solve a more complex problem - in addition to controlling the balances, we calculate the cost of the decommissioned item. We recommend that you study it carefully.

And for the "seed" just say that setting the lock in the new technique is very simple– and this is another advantage of the new way to control residues.

Results

Let's summarize briefly.

We reviewed two residue control techniques, both of which are used in today's typical configurations.

Key difference between methods at the time of residue control:

• The old technique - control before recording movements in registers
• New technique - control after recording movements in registers

In general, the new technique is more efficient, but it is not always applicable.

Applicability criterion- if there is no need to refer to the data of the controlled register for the formation of movements, a new methodology can be used.

If we talk about the control of balances by nomenclature, then the use of a new methodology is possible when data on cost and stock balances are stored in different registers.

Finally, examples from typical configurations:

• IN UT 11 there are 2 main registers for accounting for the item: Free balances (quantity) and Cost of goods (data on cost) - a new methodology is used
• IN BP 3.0 data on cost and balances are stored in one accounting register - the old methodology for controlling balances is used.