Как в запросе 1С распределить оплаты по реализациям по принципу ФИФО

При построении отчетов по взаиморасчетам в 1С часто возникает необходимость сопоставить документы отгрузки (реализации) и документы оплаты, если они не связаны напрямую реквизитами сделки или документами-основаниями. Рассмотрим задачу, когда по объекту учета (например, по заказу или контрагенту) есть список реализаций и список оплат, и требуется распределить поступающие платежи по отгрузкам по порядку их возникновения (алгоритм ФИФО) исключительно с помощью запроса.

Постановка задачи и исходные данные

Проанализируем типовой пример. Пусть у нас есть две таблицы данных:

1. Реализации:
   • Р1 от 01.10 — 1000 руб.
   • Р2 от 03.10 — 2000 руб.
   • Р3 от 05.10 — 3000 руб.
2. Оплаты:
   • О1 от 02.10 — 1500 руб.
   • О2 от 04.10 — 1000 руб.
   • О3 от 10.10 — 3000 руб.

Наш цель — получить единый результат запроса со следующей структурой распределения:

• Р1 — О1 от 02.10 — 1000 руб. (Р1 полностью зарыта)
• Р2 — О1 от 02.10 — 500 руб. (остаток оплаты О1)
• Р2 — О2 от 04.10 — 1000 руб. (оплата О2 полностью)
• Р2 — О3 от 10.10 — 500 руб. (Р2 полностью закрыта)
• Р3 — О3 от 10.10 — 2500 руб. (остаток оплаты О3)

Алгоритм решения: построение интервалов нарастающего итога

Для связывания двух последовательностей без прямой ссылки применяется математический подход с перевод сумм в числовые интервалы. Разберем его по шагам:

1. Для каждого документа реализации вычисляется накопительный итог до него и с учетом него. Это дает числовой интервал вида [ПорядокРаспределенияС, ПорядокРаспределенияПо].
2. Аналогичные интервалы рассчитываются для каждого документа оплаты.
3. Таблицы соединяются по условию пересечения эти интервалов. На пересечении рассчитывается конкретная сумма оплаты, приходящаяся на данную реализацию.

Готовое решение на языке запросов 1С

Посмотрим на практическую реализацию этого алгоритма в виде пакета запросов:

ВЫБРАТЬ "Р1" КАК Ячейка, 1000 КАК Количество, ДАТАВРЕМЯ(2016, 10, 1) КАК Дата ПОМЕСТИТЬ Реализация
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Р2", 2000, ДАТАВРЕМЯ(2016, 10, 3)
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Р3", 3000, ДАТАВРЕМЯ(2016, 10, 5) ;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ "О1" КАК Товар, 1500 КАК Количество, ДАТАВРЕМЯ(2016, 10, 2) КАК Дата ПОМЕСТИТЬ Оплата
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "О2", 1000, ДАТАВРЕМЯ(2016, 10, 4)
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "О3", 3000, ДАТАВРЕМЯ(2016, 10, 10) ;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Реализация.Ячейка КАК Ячейка,
	Реализация.Количество КАК Количество,
	ЕСТЬNULL(СУММА(Реализация1.Количество), 0) + 1 КАК ПорядокРаспределенияС,
	ЕСТЬNULL(СУММА(Реализация1.Количество), 0) + Реализация.Количество КАК ПорядокРаспределенияПо,
	Реализация.Дата
ПОМЕСТИТЬ РеализацияПоПорядку
ИЗ
	Реализация КАК Реализация
		ЛЕВОЕ СОЕДИНЕНИЕ Реализация КАК Реализация1
		ПО Реализация.Ячейка > Реализация1.Ячейка
СГРУППИРОВАТЬ ПО
	Реализация.Ячейка,
	Реализация.Количество,
	Реализация.Дата ;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Оплата.Товар КАК Товар,
	Оплата.Количество КАК Количество,
	ЕСТЬNULL(СУММА(Оплата1.Количество), 0) + 1 КАК ПорядокРаспределенияС,
	ЕСТЬNULL(СУММА(Оплата1.Количество), 0) + Оплата.Количество КАК ПорядокРаспределенияПо,
	Оплата.Дата
ПОМЕСТИТЬ ОплатаПоПорядку
ИЗ
	Оплата КАК Оплата
		ЛЕВОЕ СОЕДИНЕНИЕ Оплата КАК Оплата1
		ПО Оплата.Товар > Оплата1.Товар
СГРУППИРОВАТЬ ПО
	Оплата.Товар,
	Оплата.Количество,
	Оплата.Дата ;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	РеализацияПоПорядку.Ячейка КАК Реализация,
	ОплатаПоПорядку.Товар КАК Оплата,
	ВЫБОР
		КОГДА ОплатаПоПорядку.ПорядокРаспределенияПо < РеализацияПоПорядку.ПорядокРаспределенияС
			ТОГДА ОплатаПоПорядку.ПорядокРаспределенияС
		ИНАЧЕ РеализацияПоПорядку.ПорядокРаспределенияС
	КОНЕЦ + 1 КАК Количество,
	ОплатаПоПорядку.Дата КАК ДатаОплаты
ИЗ
	РеализацияПоПорядку КАК РеализацияПоПорядку
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОплатаПоПорядку КАК ОплатаПоПорядку
		ПО РеализацияПоПорядку.ПорядокРаспределенияС <= ОплатаПоПорядку.ПорядокРаспределенияПо
			И РеализацияПоПорядку.ПорядокРаспределенияПо >= ОплатаПоПорядку.ПорядокРаспределенияС

Особенности реализации и подводные камни

При использовании данного подхода в реальных конфигурациях (например, «Управление торговлей 10.3») нужно учитывать несколько важных аспектов:

1. Производительность (сложность $O(N^2)$): Расчет нарастающих итогов через соединение таблицы «самой с собой» (условие Реализация.Ссылка > Реализация1.Ссылка) при большом количестве документов создает значительную нагрузку на СУБД. Для больших объемов данных рекомендуется выполнять распределение программно во временных таблицах значений BSL с помощью циклов.
2. Оконные функции: В современных версиях платформы 1С:Предприятие (начиная с 8.3.20) расчет накопительных итогов можно выполнять с использованием аналитических функций СУММА(...) НАД (...), что существенно ускоряет выполнение запроса.
3. Сортировка и детерминированность: Чтобы распределение работало строго по хронологии, условия левого соединения для нарастающего итога должны строиться по моменту времени документа (или по сочетанию даты и ссылки), а не только по наименованию.
4. Использование штатных регистров: Если в конфигурации ведется взаиморасчет по документам расчетов, готовые данные о распределении оплат уже присутствуют в движениях регистров накопления (например, ВзаиморасчетыСКонтрагентами). В этом случае предпочтительнее получать данные из виртуальных таблиц оборотов регистра, не совершая ручных вычислений в запросе.