1c accounting 8.3 control of negative balances. Accounting info. Retrieving Document Data and Register Remains by Query

This report helps to obtain summary or detailed information about negative balances on 41 accounts at any time. The result of the report is displayed with default detail (see Fig. 1)

Because the report is completely written using the data composition scheme, then it will not be difficult for the user to change the sections of the report from the user mode (see Fig. 2)

The external report is intended for the configuration "1C: Enterprise Accounting 8, edition 3.0" and "Revision 3.0 (CORP)" running on platform version 8.2 in "MANAGED APP" mode.

Free support period: 1 month.

Reasons to buy

Negative balances are always a headache for any accountant. Negative balances on 41 accounts doubly exacerbate this situation. This report quickly and clearly shows all "redness" for 41 accounts in a convenient and visual form. And lany negative balance on 41 accounts can be deciphered using the reports “Analysis of subconto” and “Account card”. At the same time, by combining the use of these reports, it is possible to descend directly to the level of registrar documents that caused the movement of goods. To do this, just click on the required figure in the report and select the report to decrypt.

According to the numerous wishes of users, a separate version of the report “Control of negative balances on inventory accounts” has been created, in which the ability to control negative balances is added, not only for 41 accounts, but also for other main accounts of the movement of goods and materials:

Account 07 Equipment to be installed
- Account 08.04 Acquisition of fixed assets
- Score 10 all, except 10.07 (Materials transferred for processing to the side)
- Account 21 Semi-finished products of own production
- Account 41 all, except 41.12 (Goods in retail trade (in NTT at selling value))
- Account 42.01 Trade margin in automated outlets
- Account 43 Finished products

Also, remember that negative balances can occur not only in the accounts of the movement of goods and materials, but also in the account of the customs declaration. If you need to control this account too, we recommend that you familiarize yourself with the external report

Advantages

1. Connection through the mechanism of external processing and reporting. This allows you to use the report without making any changes to the default configuration. It is also possible to open a standard report via "File" -> "Open".
2. Ability to customize the report "for yourself" from the user mode.

Money Back Guarantee

Infostart LLC guarantees you a 100% refund if the program does not correspond to the declared functionality from the description. The money can be returned in full if you declare it within 14 days from the date of receipt of money to our account.

The program is so tested in work that we can give such a guarantee with full confidence. We want all of our customers to be happy with their purchase.

At trading or manufacturing enterprises, negative balances “jump out” in accounting in the accounting system. They reflect excessively written off goods/materials.

What are the reasons for their occurrence?

Lack of parish

Often the most common reason may be the lack of a posted goods receipt or entry of opening balances. For example, you have purchased a product, but have not yet managed to post it in the system, but you have already managed to sell it. And it happens - the goods were credited, then moved to the store and sold, but someone canceled the posting of the receipt document.

In this situation, the correct course of action would be to check the presence of the receipt document in the database. If it exists and has not been completed, check the filling and complete it. If there is no receipt document in the database at all, you must enter it retroactively. At the same time, it is necessary to understand that the addition of documents retroactively in the closed tax period may lead to changes in the amount of taxes, in particular - VAT.

regrading

Another common reason for the phenomenon we are considering is oversorting or an excess of one item of goods (material) and, at the same time, a lack of another. For example, in the program, only a black wallet in the amount of 10 pieces is marked in stock, and in the store the seller sells a red wallet and registers its sale in the amount of 5 pieces in the database. As a result, the balance of black wallets in the database does not decrease, and our balance appears in the red wallets.

In this case, the correction of balances is solved in the following way: the posting of one product and the write-off of another are registered. To do this, a document “Goods posting” is created and 5 items are posted in it. red wallets. Next, a document “Write-off of goods” is created and 5 pieces are written off in it. black wallets.

When in 1C: Accounting 3.0 there is a negative balance for a product (materials), then when posting the document “Sales of goods”, an information message is displayed stating that it is not possible to post this document, since the number of units displayed in the tabular part of the document exceeds the balance.

Fig.1 Message in the document when there is no balance in the warehouse

Cautionary signals in the balance sheet - negative balances are highlighted in red!

Fig.2 Tracking by OSV

How to enable or disable the control of negative balances in 1C BP 3.0

You can set up control in the "Administration" section, then click on the "Documents posting" link.

Fig.3 Setting

To disable control, you need to activate the flag on the parameter "Allow inventory write-off in the absence of balances according to accounting data".

Fig. 4 Permission to write off units with negative balance indicators

It happens that for an urgent sale of goods that also urgently need to be shipped, it is necessary to turn off the control for a while. The "Realizations" document is posted in the system and then control is turned on again. Then, be sure not to forget to analyze the balances in order to correct the error in accounting, due to which a negative product arose.

In order to control the balances in the context of warehouses, you need to set up analytics for them in the "Accounting Settings" through the "Administration".

Fig.5 Parameters

Click "Chart of Account Settings".

Fig.6 Setting parameter

Click "By nomenclature, batches and warehouses (by quantity and amount)".

Fig.7 Inventory accounting parameters

When installing analytics, by clicking "By warehouses (storage places)", we choose how to keep records.

Fig.8 Activation of analytics by warehouses

If the setting “by quantity and amount” is selected, respectively, accounting will be in quantitative and total accounting for warehouses separately, and if “by quantity”, then only quantitative in the context of each warehouse, and the write-off amounts are determined by dividing the inventory price by the entire quantity in all warehouses.

Control reports

The report "Control of negative balances" is used to analyze the detected negative balances of goods of organizations. You can open it through the "Warehouse - Control of negative balances".

Fig.9 Reports for the control of residues

Fig.10 Control report form

In the report settings, you can specify what data to build a report on, for example, group data by Organization, Warehouse, Write-off Document, Nomenclature, etc.

Fig.11 Control report settings

In the selection, you can specify on which data to generate a report, for example, for a particular warehouse or for a problematic item.

Fig.12 Selections in the control report

Fig.13 Formation of a control report

Negative balances are a kind of indicator of errors in accounting. It is important to constantly monitor stock balances and correct them in a timely manner. Existing incorrect balances create problems for the operational work of users, and can also cause incorrect calculation of cost, revaluation and other important accounting indicators.

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.

In this way:

• 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 particular 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:

Control over warehouse balances is a mandatory accounting procedure for any enterprise working with goods. Often you may encounter a situation where there is no product in the program, but it is actually in stock. In such a situation, there are two options:

• Submit it for sale
• Leave in stock until the circumstances of such a situation are clarified.

The choice depends on several factors, such as organizational policy or the specific situation. When the product is on the counter and the buyer is interested in it (holds it in his hands), it is not advisable to refuse to sell.

Some enterprises practice the formation of a sales document without posting it, but not all use this practice. In case of such situations, the 1C program in its latest versions suggests the possibility of disabling the control of negative balances.

When the control is activated, the sale of goods that are not in stock according to the program will give the user a warning: “The column “Quantity” is incorrectly filled in line 1 of the list “Goods”. “The indicated amount exceeds the balance. Remainder: 18. Not enough 111.093.

Disabling the control of negative balances in 1C

The operation of turning on / off the control of balances in 1C is carried out through the menu "Main" - "Settings" - "Accounting parameters" - "Stocks". Here you need to check the box "Allow inventory write-off when there is no stock according to accounting data".

After that, the action is confirmed by the "Record and close" button. In turn, such actions are guaranteed to become the basis for the formation of negative balances in accounting. They will need to be eliminated.

Report "Control of negative balances"

The formation of this report is carried out through the menu "Warehouse" - "Reports", where the document is presented. The user is required to define the request interval and click on the "Generate" button. The absence of a specified period will not allow showing negative balances, which is a feature of the system that requires mandatory filling in the "Period" column.

The finished report has the following appearance.

For the report itself, a standard set of filters is available, including grouping, sorting and other data transformations in accordance with the requests and needs of users. Through the "Show settings" button, you can manually include additional lines in the report.

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
Proceed;
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 the 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?

Not! 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's 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:

• AT 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
• AT BP 3.0 data on cost and balances are stored in one accounting register - the old methodology for controlling balances is used.