Как в 1С:Управление торговлей 10 сопоставить даты возникновения и погашения задолженности с помощью запроса?

← На главную

При анализе взаиморасчетов с контрагентами в конфигурации «1С:Управление торговлей 10.3» часто возникает задача получить отчет, в котором для каждого случая появления дебиторской задолженности отображается парный документ ее закрытия, а также даты начала и окончания долга. Ситуация усложняется тем, что в договорах контрагентов не всегда включен признак ведения расчетов по документам расчетов, а типовой регистр накопления ВзаиморасчетыСКонтрагентами хранит сводные суммы движений.

Разберем по шагам, как решить эту задачу средствами языка запросов 1С, выясним причину сложностей стандартного подхода и рассмотрим альтернативные варианты реализации.

Анализ проблемы: почему не работает простое условие смены знака остатка

Первая интуитивная попытка большинства разработчиков заключается в выборке из виртуальной таблицы ОстаткиИОбороты регистра ВзаиморасчетыСКонтрагентами с периодичностью Регистратор. В ней пытаются поймать моменты, когда сальдо меняет знак:

Возникновение долга: конечный остаток больше нуля, а начальный был меньше либо равен нулю.

Погашение долга: конечный остаток стал меньше либо равен нулю, а начальный был строго больше нуля.

Однако если просто вывести такие строки в один список, даты возникновения и закрытия окажутся в разных строках таблицы. Попытка связать их в единую строку через простое объединение не дает нужного результата — требуется корректное соединение строк между собой.

Решение 1: Пакетный запрос с временными таблицами и поиском ближайшей даты закрытия

Рассмотрим вариант реализации, предложенный в обсуждении. Логика решения строится на разделении потока движений на два независимых набора во временных таблицах с последующим сопоставлением по минимальной подходящей дате закрытия.

Разберем алгоритм по этапам:

Выбираем события появления долга во временную таблицу Вт_Появление, отбирая строки, где начальный остаток был неположительным, а конечный стал положительным.
Выбираем события закрытия долга во временную таблицу Вт_Закрытие, где начальный остаток был больше нуля, а конечный стал меньше либо равен нулю.
Соединяем появление с закрытием по условию нестрогого неравенства дат (Вт_Появление.ДолгПоявился <= Вт_Закрытие.ДатаЗакрылся) и с помощью агрегатной функции МИНИМУМ() находим самую раннюю дату погашения, следующую за появлением долга.
Через промежуточную буферную таблицу связываем полученную дату с конкретным регистратором закрытия и формируем итоговую выборку.

Посмотрим на пример текста запроса:


ВЫБРАТЬ
    ВЫБОР
        КОГДА Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток > 0
                И Взаиморасчеты.СуммаВзаиморасчетовНачальныйОстаток <= 0
            ТОГДА Взаиморасчеты.Период
        ИНАЧЕ 0
    КОНЕЦ КАК ДолгПоявился,
    ВЫБОР
        КОГДА Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток > 0
                И Взаиморасчеты.СуммаВзаиморасчетовНачальныйОстаток <= 0
            ТОГДА Взаиморасчеты.Регистратор
        ИНАЧЕ НЕОПРЕДЕЛЕНО
    КОНЕЦ КАК ДолгПоявился_Регистратор
ПОМЕСТИТЬ Вт_Появление
ИЗ
    РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&Дата1, &Дата2, Регистратор, , ДоговорКонтрагента = &ДоговорКонтрагента) КАК Взаиморасчеты
ГДЕ
    Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток > 0
    И Взаиморасчеты.СуммаВзаиморасчетовНачальныйОстаток <= 0
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВЫБОР
        КОГДА Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток <= 0
                И Взаиморасчеты.СуммаВзаиморасчетовНачальныйОстаток > 0
            ТОГДА Взаиморасчеты.Период
        ИНАЧЕ 0
    КОНЕЦ КАК ДатаЗакрылся,
    ВЫБОР
        КОГДА Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток <= 0
                И Взаиморасчеты.СуммаВзаиморасчетовНачальныйОстаток > 0
            ТОГДА Взаиморасчеты.Регистратор
        ИНАЧЕ НЕОПРЕДЕЛЕНО
    КОНЕЦ КАК ЗакрылсяРегистратор
ПОМЕСТИТЬ Вт_Закрытие
ИЗ
    РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&Дата1, &Дата2, Регистратор, , ДоговорКонтрагента = &ДоговорКонтрагента) КАК Взаиморасчеты
ГДЕ
    Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток <= 0
    И Взаиморасчеты.СуммаВзаиморасчетовНачальныйОстаток > 0
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Вт_Появление.ДолгПоявился КАК ДолгПоявился_свзь,
    Вт_Появление.ДолгПоявился_Регистратор,
    МИНИМУМ(Вт_Закрытие.ДатаЗакрылся) КАК ДатаЗакрылся
ПОМЕСТИТЬ ВТ_Данные
ИЗ
    Вт_Появление КАК Вт_Появление
        ПОЛНОЕ СОЕДИНЕНИЕ Вт_Закрытие КАК Вт_Закрытие
        ПО Вт_Появление.ДолгПоявился <= Вт_Закрытие.ДатаЗакрылся
СГРУППИРОВАТЬ ПО
    Вт_Появление.ДолгПоявился_Регистратор,
    Вт_Появление.ДолгПоявился
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Данные.ДатаЗакрылся,
    Вт_Закрытие.ЗакрылсяРегистратор,
    ВТ_Данные.ДолгПоявился_Регистратор,
    ВТ_Данные.ДолгПоявился_свзь
ПОМЕСТИТЬ ВТ_Буфер
ИЗ
    Вт_Закрытие КАК Вт_Закрытие
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК ВТ_Данные
        ПО Вт_Закрытие.ДатаЗакрылся = ВТ_Данные.ДатаЗакрылся
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Вт_Появление.ДолгПоявился,
    Вт_Появление.ДолгПоявился_Регистратор,
    ВТ_Буфер.ДатаЗакрылся,
    ВТ_Буфер.ЗакрылсяРегистратор
ИЗ
    Вт_Появление КАК Вт_Появление
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Буфер КАК ВТ_Буфер
        ПО Вт_Появление.ДолгПоявился = ВТ_Буфер.ДолгПоявился_свзь
            И Вт_Появление.ДолгПоявился_Регистратор = ВТ_Буфер.ДолгПоявился_Регистратор

Подводные камни и ограничения запроса по смене знака остатка

Проанализируем ограничения, с которыми можно столкнуться при использовании приведенного выше подхода:

Частичные оплаты и повторные отгрузки: Условия смены знака остатка работают корректно только по идеальной схеме «отгрузили в ноль — полностью оплатили в ноль». Если отгрузка закрывается тремя платежами, промежуточные платежи в выборку не попадут. Если до момента оплаты первой отгрузки произойдет вторая, событие перехода через ноль вообще не зафиксируется для второй накладной.
Коллизии при соединении по неравенству: Условие ДатаПоявления <= ДатаЗакрытия с выбором минимума может привязать несколько документов возникновения к одному и тому же первому документу погашения. Кроме того, соединение по неравенству порождает декартово произведение, что на больших объемах данных существенно снижает производительность СУБД.
Границы расчетного периода: Если долг возник до даты начала отчета (&Дата1), а погашен внутри периода, документ возникновения окажется вне выборки.
Подсчет дней задолженности: Обратите внимание, что вычитать даты через функцию ДЕНЬ(Дата2) - ДЕНЬ(Дата1) нельзя, так как она возвращает лишь номер дня в месяце (от 1 до 31). Для корректного вычисления продолжительности долга в днях обязательно используйте функцию платформы РАЗНОСТЬДАТ(ДатаНачала, ДатаОкончания, ДЕНЬ).

Решение 2: Сопоставление по методу ФИФО в алгоритме на встроенном языке

В языке запросов платформы 1С:Предприятие отсутствуют классические оконные функции. Поэтому расчет нарастающего итога и интервалов погашения исключительно в запросе получается громоздким. Если по договору ведется непрерывный документооборот с частичными закрытиями, наиболее надежным и быстрым способом является линейная обработка выборки на встроенном языке:

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

Такой программный обход выполняется за один линейный проход по таблице, исключает блокировки базы данных и дает математически точное сопоставление дат возникновения и закрытия задолженности при любых графиках расчетов.