Как запросом определить наличие предоплаты за период в 1С:Управление торговлей?

← На главную

При разработке отчетов и написании запросов в конфигурации «1С:Управление торговлей» программисты часто сталкиваются с задачей определения авансов (предоплат) от клиентов. Ситуация усложняется, когда в настройках договора или соглашения с контрагентом выбран порядок расчетов в целом по договору или по заказам, то есть пользователи вручную не указывают расчетные документы в платежках. В этой статье мы подробно разберем, как решить данную задачу одним запросом для двух разных поколений платформ: УТ 10.3 (и аналогичных КА 1.1, УПП 1.3) и УТ 11 (а также ERP 2 и КА 2).

В чем сложность задачи?

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

Типичная ошибка при попытке решить эту задачу — получение остатка взаиморасчетов на конечную дату выбранного периода. Рассмотрим пример: на начало месяца у клиента был нулевой баланс, 10-го числа он внес предоплату 50 000 рублей, а 15-го числа мы отгрузили ему товар на эту же сумму. На конец месяца остаток снова равен нулю. Если мы просто снимем остаток на дату запроса, система покажет отсутствие предоплаты, хотя фактически в течение периода она была. Нам необходимо проанализировать динамику изменения взаиморасчетов внутри выбранного интервала дат.

---

Решение для конфигурации «Управление торговлей 10.3» (обычные формы)

В конфигурации УТ 10.3 вся информация о взаиморасчетах при ведении учета по договору в целом собирается в регистре накопления ВзаиморасчетыСКонтрагентами.

Давайте проанализируем логику этого регистра:

Положительный остаток (знак «плюс» в ресурсе СуммаВзаиморасчетов) означает дебиторскую задолженность, то есть клиент должен нам деньги за отгруженный товар.
Отрицательный остаток (знак «минус» в ресурсе СуммаВзаиморасчетов) означает кредиторскую задолженность, то есть мы должны клиенту. В контексте расчетов с покупателями это и есть наша предоплата (аванс).

Чтобы определить, была ли предоплата в периоде, нам нужно поймать момент, когда баланс по договору уходил "в минус". Для этого отлично подходит виртуальная таблица ОстаткиИОбороты. Нам обязательно нужно настроить периодичность таблицы. Если выбрать периодичность «Период» (за весь интервал), мы получим ту же ошибку со свернутыми итогами. Оптимальный вариант — использовать периодичность День или Регистратор.

Рассмотрим пример запроса, который анализирует остатки по дням внутри периода и определяет, был ли у контрагента отрицательный остаток (предоплата):


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

Разберем по шагам, как работает этот запрос:

Мы обращаемся к виртуальной таблице ОстаткиИОбороты регистра ВзаиморасчетыСКонтрагентами.
В параметрах таблицы мы передаем границы периода (&НачалоПериода и &КонецПериода), а также устанавливаем периодичность День. Это заставит систему рассчитать остатки на конец каждого дня, когда были движения.
В поле условий виртуальной таблицы мы можем наложить фильтр по типу ведения взаиморасчетов, чтобы выбирать только договоры "в целом".
В секции ГДЕ мы проверяем условие СуммаВзаиморасчетовКонечныйОстаток < 0. Если в какой-то из дней периода остаток по договору становился отрицательным, эта запись попадет в выборку.
Использование конструкции ВЫБРАТЬ РАЗЛИЧНЫЕ позволяет схлопнуть строки по клиенту и договору. Если хотя бы в один день за период была предоплата — мы получим одну строку с признаком БылаПредоплата = ИСТИНА.

---

Решение для конфигурации «Управление торговлей 11» (управляемые формы, ERP, КА)

В современных версиях «Управления торговлей» (начиная с УТ 11.4 / 11.5) архитектура взаиморасчетов кардинально изменилась. Даже если пользователь выбирает в соглашении порядок расчетов «По договорам» или «По заказам» и не указывает документы вручную, система в фоновом режиме (или в момент проведения документов при использовании онлайн-взаиморасчетов) автоматически распределяет платежи по документам отгрузки, используя алгоритм ФИФО.

Для хранения этой информации используются регистры накопления:

РасчетыСКлиентами — базовый регистр взаиморасчетов.
РасчетыСКлиентамиПоДокументам — регистр, детализирующий расчеты до документов возникновения задолженности.
РасчетыСКлиентамиПоСрокам — применяется в архитектуре "Взаиморасчеты онлайн" для контроля плановых и фактических дат оплат.

Самое главное отличие новой архитектуры от старой заключается в том, что нам больше не нужно анализировать математический знак остатка. Разработчики фирмы «1С» разделили долг и предоплату на разные ресурсы. В регистрах взаиморасчетов УТ 11 присутствуют отдельные ресурсы: Долг и Предоплата. Они всегда хранятся в положительных значениях. Если у клиента висит аванс — эта сумма будет находиться в ресурсе Предоплата.

Давайте рассмотрим, как правильно построить запрос к регистру РасчетыСКлиентамиПоДокументам для определения предоплаты за период:


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

Проанализируем особенности этого запроса:

В качестве источника данных мы берем виртуальную таблицу ОстаткиИОбороты регистра РасчетыСКлиентамиПоДокументам (или РасчетыСКлиентамиПоСрокам для онлайн-версии взаиморасчетов).
Условие ПредоплатаКонечныйОстаток > 0 накладывается на каждый день периода. Если на конец любого дня у нас числился аванс от клиента — система зафиксирует предоплату.
Дополнительно мы проверяем оборот ПредоплатаПриход > 0. Это необходимо для сценариев, когда предоплата пришла и закрылась отгрузкой в течение одного и того же дня. При периодичности День конечный остаток на этот день может оказаться нулевым, но оборот по приходу предоплаты покажет, что факт аванса зафиксирован системой.

---

Оптимизация запроса для повышения производительности

Использование периодичности День или Регистратор на больших базах данных может негативно сказаться на скорости выполнения запроса, особенно если анализируется большой период времени. Чтобы оптимизировать работу СУБД, рекомендуем соблюдать следующие правила:

Всегда ограничивайте выборку в параметрах виртуальной таблицы. Передавайте в первый и пятый параметры таблицы отборы по конкретным клиентам или договорам, если это возможно. Не выбирайте данные по всей базе без необходимости.
Используйте временные таблицы. Если вам нужно сопоставить данные о предоплатах с другими показателями (например, с продажами), сначала выберите уникальный список контрагентов во временную таблицу, а затем соединяйте её с виртуальной таблицей остатков и оборотов по индексу.

Рассмотрим пример оптимизированного запроса с использованием временной таблицы для конкретного списка партнеров:


// Шаг 1: Отбираем нужных нам клиентов во временную таблицу
ВЫБРАТЬ
    Партнеры.Ссылка КАК Партнер
ПОМЕСТИТЬ ВТ_Клиенты
ИЗ
    Справочник.Партнеры КАК Партнеры
ГДЕ
    Партнеры.Клиент = ИСТИНА
    И НЕ Партнеры.ПометкаУдаления
;

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

Этот подход позволяет СУБД построить оптимальный план запроса и использовать индексы регистра накопления, что значительно ускорит формирование отчета.