Часто перед бухгалтерией и разработчиками 1С встает задача сформировать отчет по срокам возникновения дебиторской задолженности, чтобы разделить ее на группы: до 3 месяцев, от 3 до 12 месяцев, более года и так далее. Задача усложняется, если в информационной базе не используется механизм ведения взаиморасчетов по документам, и мы имеем только общую сумму долга по договору. В такой ситуации стандартные отчеты бессильны.
Рассмотрим, как решить эту проблему с помощью одного сложного, но эффективного запроса. Мы разберем классический метод, основанный на принципе FIFO (First-In, First-Out), который предполагает, что любая оплата от клиента идет в счет погашения самой старой задолженности.
Логика метода проста и интуитивно понятна. Если клиент должен нам 10 000 рублей, а отгрузки ему были 1-го, 10-го и 20-го числа, то текущий долг сформирован за счет самых последних отгрузок. Чтобы определить, какие именно отгрузки не оплачены, мы мысленно "отматываем" историю операций назад, пока не наберем сумму долга.
Например, итоговый долг 10 000 руб. Последние отгрузки:
Распределяем долг: 4 000 (от 25.01) + 2 500 (от 20.01) + 3 500 (часть отгрузки от 10.01) = 10 000. Таким образом, мы определили, что долг состоит из трех частей с разными датами возникновения. Теперь эту логику нужно реализовать на языке запросов 1С.
Для реализации метода нам понадобится последовательно создать несколько временных таблиц. Проанализируем каждый шаг.
Шаг 1. Получение итоговой суммы долга
Сначала нам нужна общая сумма задолженности на текущую дату по каждому контрагенту и договору (или в других нужных разрезах). Для этого создаем первую временную таблицу, получая остатки из регистра бухгалтерии или регистра накопления взаиморасчетов.
Важный момент: сразу отбираем только тех контрагентов, у которых есть положительный остаток долга. Это значительно сократит объем данных для последующих шагов, что критически важно для больших баз.
// Получаем итоговые остатки по долгам
ВЫБРАТЬ
Взаиморасчеты.Контрагент КАК Контрагент,
Взаиморасчеты.Договор КАК Договор,
СУММА(Взаиморасчеты.СуммаОстаток) КАК СуммаДолга
ПОМЕСТИТЬ ВТ_ДолгиКонтрагентов
ИЗ
РегистрБухгалтерии.Хозрасчетный.Остатки(
&ДатаОтчета,
Счет В (&СписокСчетов),
,
Организация = &Организация) КАК Взаиморасчеты
СГРУППИРОВАТЬ ПО
Взаиморасчеты.Контрагент,
Взаиморасчеты.Договор
ИМЕЮЩИЕ
СУММА(Взаиморасчеты.СуммаОстаток) > 0
Шаг 2. Сбор всех увеличений долга
Теперь нужно собрать все операции, которые увеличивали дебиторскую задолженность (например, реализации товаров и услуг). Нам важны дата операции и сумма. Эти данные помещаем во вторую временную таблицу. Для оптимизации выбираем обороты только по тем контрагентам, которых мы отобрали на первом шаге.
// Получаем все документы, увеличившие долг (дебетовые обороты)
ВЫБРАТЬ
Обороты.Контрагент КАК Контрагент,
Обороты.Договор КАК Договор,
КОНЕЦПЕРИОДА(Обороты.Период, ДЕНЬ) КАК ДатаВозникновения,
СУММА(Обороты.СуммаОборотДт) КАК УвеличениеДолга
ПОМЕСТИТЬ ВТ_УвеличениеДолгов
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(
,
&ДатаОтчета,
Регистратор,
Счет В (&СписокСчетов)
И Контрагент В (ВЫБРАТЬ Контрагент ИЗ ВТ_ДолгиКонтрагентов),
,
Организация = &Организация,
,) КАК Обороты
СГРУППИРОВАТЬ ПО
Обороты.Контрагент,
Обороты.Договор,
КОНЕЦПЕРИОДА(Обороты.Период, ДЕНЬ)
ИМЕЮЩИЕ
СУММА(Обороты.СуммаОборотДт) > 0
Шаг 3. Расчет накопленного долга с конца (самый хитрый шаг)
Это ключевой момент всего алгоритма. Нам нужно для каждой записи об увеличении долга рассчитать сумму всех более поздних увеличений. Для этого мы соединяем таблицу ВТ_УвеличениеДолгов саму с собой по условию, что дата во второй таблице больше даты в первой.
Посмотрим на пример. Если у нас есть увеличения долга:
После соединения для строки от 10.01 мы получим накопленную сумму увеличений за 20.01 и 25.01 (2500 + 4000 = 6500). Для строки от 20.01 — сумму за 25.01 (4000). А для последней строки от 25.01 накопленная сумма будет равна 0.
ВЫБРАТЬ
УвеличениеДолгов.Контрагент,
УвеличениеДолгов.Договор,
УвеличениеДолгов.ДатаВозникновения,
МАКСИМУМ(УвеличениеДолгов.УвеличениеДолга) КАК УвеличениеДолга,
СУММА(ЕСТЬNULL(УвеличениеДолгов1.УвеличениеДолга, 0)) КАК НакопленныйДолгСКонца
ПОМЕСТИТЬ ВТ_ТаблицаСНакоплением
ИЗ
ВТ_УвеличениеДолгов КАК УвеличениеДолгов
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_УвеличениеДолгов КАК УвеличениеДолгов1
ПО УвеличениеДолгов.Контрагент = УвеличениеДолгов1.Контрагент
И УвеличениеДолгов.Договор = УвеличениеДолгов1.Договор
И УвеличениеДолгов.ДатаВозникновения < УвеличениеДолгов1.ДатаВозникновения
СГРУППИРОВАТЬ ПО
УвеличениеДолгов.Контрагент,
УвеличениеДолгов.Договор,
УвеличениеДолгов.ДатаВозникновения
Примечание: в оригинальном примере из форума было условие ДатаВозникновения > ДатаВозникновения1, что означало расчет накоплений "из прошлого". Для метода FIFO нам нужен обратный порядок: мы считаем накопления "из будущего", поэтому условие ДатаВозникновения < ДатаВозникновения1. Это эквивалентно сортировке по убыванию даты.
Шаг 4. Распределение итогового долга и получение результата
Теперь у нас есть все компоненты. Соединяем таблицу с накоплениями (ВТ_ТаблицаСНакоплением) и таблицу с итоговым долгом (ВТ_ДолгиКонтрагентов). Далее применяем простую логику:
НакопленныйДолгСКонца >= СуммаДолга. Это означает, что данное увеличение долга и все более ранние были полностью погашены, так как даже суммы более поздних отгрузок превышают текущий долг.МИН(СуммаУвеличения, ИтоговыйДолг - НакопленныйДолгСКонца).Это и есть финальный результат: таблица, где для каждого контрагента и договора указаны конкретные суммы долга с датами их возникновения.
ВЫБРАТЬ
ТаблицаСНакоплением.Контрагент,
ТаблицаСНакоплением.Договор,
ТаблицаСНакоплением.ДатаВозникновения,
ВЫБОР
// Если долг "помещается" в сумму текущего увеличения и всех последующих,
// то берем остаток долга, который не покрыт более поздними отгрузками.
КОГДА ТаблицаСНакоплением.НакопленныйДолгСКонца + ТаблицаСНакоплением.УвеличениеДолга > ДолгиКонтрагентов.СуммаДолга
ТОГДА ДолгиКонтрагентов.СуммаДолга - ТаблицаСНакоплением.НакопленныйДолгСКонца
// Иначе берем всю сумму увеличения долга.
ИНАЧЕ ТаблицаСНакоплением.УвеличениеДолга
КОНЕЦ КАК ДолгНаДату
ИЗ
ВТ_ТаблицаСНакоплением КАК ТаблицаСНакоплением
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДолгиКонтрагентов КАК ДолгиКонтрагентов
ПО ТаблицаСНакоплением.Контрагент = ДолгиКонтрагентов.Контрагент
И ТаблицаСНакоплением.Договор = ДолгиКонтрагентов.Договор
ГДЕ
// Отсекаем строки, которые точно погашены
ДолгиКонтрагентов.СуммаДолга > ТаблицаСНакоплением.НакопленныйДолгСКонца
Получив эту итоговую таблицу, сгруппировать долги по периодам (до 30 дней, 30-90 дней и т.д.) с помощью конструкции ВЫБОР КОГДА в системе компоновки данных (СКД) уже не составляет труда.
Описанный метод, особенно шаг с самосоединением таблицы, может быть очень ресурсоемким на базах большого объема (сотни гигабайт и миллионы записей). Вот несколько советов по оптимизации:
ВТ_ДолгиКонтрагентов и ВТ_УвеличениеДолгов создайте по ним индексы по полям, участвующим в соединениях (Контрагент, Договор, ДатаВозникновения).Таким образом, используя метод FIFO и пошаговое построение запроса через временные таблицы, можно эффективно решать задачу анализа сроков задолженности даже в сложных случаях, когда стандартные механизмы учета недоступны.