При построении отчетов по старению дебиторской задолженности (расчет остатков по методу ФИФО) разработчики часто сталкиваются с проблемой "размытия" взаиморасчетов. В конфигурациях уровня 1С:Управление торговлей 10.3 или 1С:Управление производственным предприятием 1.3 учет ведется в разрезе контрагентов и договоров. Однако на практике у одного клиента (холдинга) может быть создано несколько карточек контрагентов с одинаковым ИНН (например, филиалы или разные юридические лица).
Если оплаты приходят на одного контрагента, а отгрузки производятся на другого, стандартный запрос ФИФО начинает работать некорректно: он показывает огромную просроченную задолженность по одной карточке контрагента, игнорируя переплату (аванс) на другой. Разберем по шагам, почему возникает эта проблема и рассмотрим эффективные технические и методологические способы её решения.
Проанализируем ситуацию, описанную в исходном запросе. Расчет ФИФО (определение неоплаченных "хвостов" накладных) происходит внутри виртуальной таблицы DN_НеЗакрытыеРеализации. Этот расчет жестко завязан на измерения регистра накопления — конкретного контрагента и его договор.
Когда в базе существуют два контрагента с одинаковым ИНН (например, "Вася 1" и "Вася 2"):
DN_НеЗакрытыеРеализации видит все его реализации как 100% неоплаченные.ПО вт_Долг_контрагента.КонтрагентИНН = DN_НеЗакрытыеРеализации.ДоговорКонтрагентаВладелец.ИНН.На этапе левого соединения СУБД просто "склеивает" строки по совпадению ИНН, но она не может перераспределить деньги с "Васи 2" на "Вася 1". В результате отчет выводит недостоверные данные: показывает "зависший" долг по конкретным накладным "Васи 1", хотя в целом по ИНН долга может не быть вовсе или он перекрыт авансом.
Попытка решить проблему схлопывания долгов разных контрагентов в рамках одного ИНН прямо в запросе — это классический методологический антипаттерн. Выясним причину, почему это так:
Если административно заставить пользователей делать взаимозачеты невозможно, разработчику приходится решать задачу программно. Рассмотрим три технических варианта реализации.
Попытка рассчитать сквозное ФИФО по ИНН в одном SQL-запросе приводит к написанию тяжелых вложенных запросов, которые соединяют таблицы "сами на себя" (self-join). На больших базах данных это гарантированно вызовет критическое падение производительности и переполнение базы данных tempdb на сервере СУБД.
Рекомендуем использовать "Набор данных - Объект" в Системе компоновки данных (СКД). Мы получим данные простым быстрым запросом, а само распределение по алгоритму ФИФО выполним во временных таблицах в оперативной памяти на встроенном языке 1С. Рассмотрим этот алгоритм подробнее:
Рассмотрим пример кода для распределения долгов по ФИФО в модуле отчета:
// Запрос для получения первичных данных (все отгрузки и все оплаты в хронологическом порядке)
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Взаиморасчеты.Контрагент.ИНН КАК ИНН,
| Взаиморасчеты.Регистратор КАК Документ,
| Взаиморасчеты.Период КАК Период,
| ВЫБОР КОГДА Взаиморасчеты.СуммаВзаиморасчетовОборот > 0 ТОГДА Взаиморасчеты.СуммаВзаиморасчетовОборот ИНАЧЕ 0 КОНЕЦ КАК СуммаРасход,
| ВЫБОР КОГДА Взаиморасчеты.СуммаВзаиморасчетовОборот < 0 ТОГДА -Взаиморасчеты.СуммаВзаиморасчетовОборот ИНАЧЕ 0 КОНЕЦ КАК СуммаПриход
|ИЗ
| РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты(&НачалоПериода, &КонецПериода, Регистратор, ДоговорКонтрагента.Наименование = ""Договор А"") КАК Взаиморасчеты
|УПОРЯДОЧИТЬ ПО
| ИНН,
| Период";
РезультатЗапроса = Запрос.Выполнить();
ТаблицаДанных = РезультатЗапроса.Выгрузить();
// Таблица для хранения результатов распределения (неоплаченных хвостов)
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("ИНН", Новый ОписаниеТипов("Строка"));
ТаблицаРезультат.Колонки.Добавить("Реализация", Новый ОписаниеТипов("ДокументСсылка"));
ТаблицаРезультат.Колонки.Добавить("СуммаДолга", Новый ОписаниеТипов("Число"));
ТаблицаРезультат.Колонки.Добавить("ДнейПросрочки", Новый ОписаниеТипов("Число"));
// Группируем данные по ИНН для обработки каждого холдинга отдельно
ТаблицаИНН = ТаблицаДанных.Скопировать();
ТаблицаИНН.Свернуть("ИНН");
Для Каждого СтрокаИНН Из ТаблицаИНН Цикл
// Получаем все движения по текущему ИНН
Отбор = Новый Структура("ИНН", СтрокаИНН.ИНН);
ДвиженияПоИНН = ТаблицаДанных.Скопировать(Отбор);
// Считаем общую сумму всех оплат (приходов) по данному ИНН
ОбщаяСуммаОплат = ДвиженияПоИНН.Итог("СуммаПриход");
// Теперь распределяем эту сумму оплат на документы отгрузки (расхода) в хронологическом порядке
Для Каждого СтрокаДвижения Из ДвиженияПоИНН Цикл
Если СтрокаДвижения.СуммаРасход = 0 Тогда
Продолжить; // Это документ оплаты, пропускаем его
КонецЕсли;
СуммаОтгрузки = СтрокаДвижения.СуммаРасход;
// Оплата гасит отгрузку по ФИФО
Если ОбщаяСуммаОплат >= СуммаОтгрузки Тогда
ОбщаяСуммаОплат = ОбщаяСуммаОплат - СуммаОтгрузки;
// Данная реализация полностью оплачена за счет общего пула денег
Иначе
// Реализация оплачена частично или не оплачена вовсе
НеоплаченныйОстаток = СуммаОтгрузки - ОбщаяСуммаОплат;
ОбщаяСуммаОплат = 0; // Все оплаты исчерпаны
// Записываем неоплаченный остаток реализации в итоговую таблицу
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.ИНН = СтрокаИНН.ИНН;
НоваяСтрока.Реализация = СтрокаДвижения.Документ;
НоваяСтрока.СуммаДолга = НеоплаченныйОстаток;
НоваяСтрока.ДнейПросрочки = (ТекущаяДата() - СтрокаДвижения.Период) / 86400;
КонецЕсли;
КонецЦикла;
КонецЦикла;
Этот способ работает в десятки раз быстрее сложных вложенных SQL-запросов, так как база данных выполняет только простую выборку строк, а "тяжелый" перебор и сопоставление выполняются на сервере приложений 1С.
Если вам принципиально решить задачу исключительно в рамках одного запроса (например, для использования в типовом отчете без изменения кода модуля), необходимо полностью переписать алгоритм расчета нарастающего итога внутри виртуальной таблицы DN_НеЗакрытыеРеализации.
Проанализируем ситуацию: чтобы оплаты распределялись корректно, связи между таблицами приходов (оплат) и расходов (отгрузок) должны идти строго по полю Контрагент.ИНН, а не по конкретным договорам или ссылкам контрагентов. Оплаты по всем договорам всех "Васей" должны суммироваться в один общий виртуальный "котел" по ИНН, а затем хронологически распределяться по реализациям этого же ИНН.
Пример изменения условий соединения таблиц при расчете нарастающего итога в запросе:
// В исходном запросе связь идет по Договору:
// ГДЕ ТаблицаПоследующихПриходов.ДоговорКонтрагента = Остатки.ДоговорКонтрагента
// Для сквозного расчета по ИНН связь необходимо переписать следующим образом:
ГДЕ ТаблицаПоследующихПриходов.ДоговорКонтрагента.Владелец.ИНН = Остатки.ДоговорКонтрагента.Владелец.ИНН
Важное предупреждение: В УТ 10.3 договоры разных контрагентов с одинаковым ИНН могут быть заключены в разных валютах (например, один в USD, другой в рублях) и иметь разные условия отсрочки. Сведение их к одному ИНН в запросе потребует принудительного пересчета всех сумм в единую валюту (например, валюту регламентированного учета). Это сделает и без того громоздкий запрос практически нечитаемым и крайне медленным.
Наиболее правильный технический путь, предусмотренный разработчиками платформы 1С — это использование встроенных регистров детализации. Проанализируем конфигурацию: в УТ 10.3 существует встроенный механизм ведения расчетов по конкретным документам.
Если в договорах контрагентов включить признак "Вести взаиморасчеты по документам расчетов", то при проведении документов оплаты пользователи (или система автоматически по ФИФО) будут указывать конкретную накладную в табличной части "Расшифровка платежа".
В этом случае сложный трехслойный запрос для расчета ФИФО становится ненужным. Всю информацию о неоплаченных остатках накладных и сроках их возникновения можно мгновенно получить простым и быстрым запросом к остаткам регистра накопления ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов:
ВЫБРАТЬ
Взаиморасчеты.ДоговорКонтрагента.Владелец.ИНН КАК ИНН,
Взаиморасчеты.ДоговорКонтрагента.Владелец КАК Контрагент,
Взаиморасчеты.ДокументРасчетовСКонтрагентом КАК ДокументОтгрузки,
Взаиморасчеты.СуммаВзаиморасчетовОстаток КАК СуммаДолга,
РАЗНОСТЬДАТ(Взаиморасчеты.ДокументРасчетовСКонтрагентом.Дата, &ТекущаяДата, ДЕНЬ) КАК ДнейПросрочки
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов.Остатки(&Период, ) КАК Взаиморасчеты
ГДЕ
Взаиморасчеты.СуммаВзаиморасчетовОстаток > 0
Этот запрос выполняется за доли секунды, абсолютно надежен, не нагружает СУБД и всегда показывает точные данные, подтвержденные первичными документами.
Если вы все же решите оставить исходный запрос, обязательно устраните в нем две грубые технические ошибки оптимизации:
ВТ, а затем её данные без каких-либо изменений переносятся в таблицу вт_Долг_контрагента через конструкцию ПОМЕСТИТЬ. Это бесполезное действие, которое удваивает нагрузку на оперативную память SQL-сервера. Объедините эти два шага в один пакет.РАЗРЕШЕННЫЕ: В одних пакетах вашего запроса это слово используется, в других — нет. При наличии настроенных прав доступа на уровне записей (RLS) это приведет к ошибкам выполнения запроса у обычных пользователей. Укажите ВЫБРАТЬ РАЗРЕШЕННЫЕ во всех пакетах запроса.