При переходе на новые версии конфигураций, таких как «Управление торговлей 11.5», «Комплексная автоматизация 2» или «ERP 2», многие разработчики сталкиваются с изменением архитектуры хранения складских остатков. Если раньше существовал регистр «СвободныеОстатки», из которого можно было получить готовую цифру, то в актуальных версиях этот регистр упразднен для повышения производительности и устранения блокировок.
Теперь свободный остаток — это расчетная величина, которую необходимо вычислять динамически в момент запроса. В этой статье мы подробно разберем, как правильно составить запрос к базе данных, чтобы получить данные, аналогичные колонке «Доступно» в подборе товаров.
Для начала определимся с формулой. В упрощенном виде для ордерной схемы и адресного склада (а также обычного склада в УТ 11) свободный остаток рассчитывается следующим образом:
Свободный остаток = (Остаток на складе) - (В резерве) - (К отгрузке)
Эти данные хранятся в двух разных регистрах накопления:
ТоварыНаСкладах — здесь хранится фактический остаток товара (ресурс ВНаличии).ТоварыКОтгрузке — здесь хранятся обязательства. Нас интересуют два ресурса:
ВРезерве — товары, зарезервированные под заказ клиента.КОтгрузке — товары, по которым уже созданы распоряжения на отгрузку (ордера или накладные), но товар еще физически не ушел со склада.Основная сложность заключается в том, чтобы правильно соединить эти две таблицы. Рассмотрим два основных способа решения этой задачи, которые обсуждались в теме.
Самый надежный способ собрать данные из двух независимых источников — использовать конструкцию ОБЪЕДИНИТЬ ВСЕ. Этот метод позволяет избежать потери данных, если, например, товара нет на остатке (0 шт.), но на него «висит» резерв (например, при пересортице или разрешенных отрицательных остатках) — для этого есть обработка поиска отрицательных остатков в 1С:ERP, УТ, КА.
Алгоритм действий следующий:
ТоварыНаСкладах. В колонки резервов ставим 0.ТоварыКОтгрузке. В колонку наличия ставим 0.Разберем пример кода, который реализует этот подход. Обратите внимание на использование СУММА во внешнем запросе и явное указание нулей во вложенных запросах.
ВЫБРАТЬ
ВложенныйЗапрос.Номенклатура КАК Номенклатура,
ВложенныйЗапрос.Склад КАК Склад,
СУММА(ВложенныйЗапрос.ВНаличииОстаток) КАК ВНаличииОстаток,
СУММА(ВложенныйЗапрос.КОтгрузкеОстаток) КАК КОтгрузкеОстаток,
СУММА(ВложенныйЗапрос.ВРезервеОстаток) КАК ВРезервеОстаток,
// Вычисляемое поле свободного остатка
СУММА(ВложенныйЗапрос.ВНаличииОстаток - ВложенныйЗапрос.КОтгрузкеОстаток - ВложенныйЗапрос.ВРезервеОстаток) КАК СвободныйОстаток
ИЗ
(
// Часть 1: Получаем фактические остатки
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Номенклатура.Ссылка КАК Номенклатура,
ТоварыНаСкладахОстатки.Склад.Ссылка КАК Склад,
ТоварыНаСкладахОстатки.ВНаличииОстаток КАК ВНаличииОстаток,
0 КАК КОтгрузкеОстаток,
0 КАК ВРезервеОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&МоментПолучения, Склад В ИЕРАРХИИ (&Склад)) КАК ТоварыНаСкладахОстатки
ОБЪЕДИНИТЬ ВСЕ
// Часть 2: Получаем резервы и товары к отгрузке
ВЫБРАТЬ
ТоварыКОтгрузкеОстатки.Номенклатура,
ТоварыКОтгрузкеОстатки.Склад,
0, // В наличии здесь нет, ставим 0
ТоварыКОтгрузкеОстатки.КОтгрузкеОстаток,
ТоварыКОтгрузкеОстатки.ВРезервеОстаток
ИЗ
РегистрНакопления.ТоварыКОтгрузке.Остатки(&МоментПолучения, Склад В ИЕРАРХИИ (&Склад)) КАК ТоварыКОтгрузкеОстатки
) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.Номенклатура,
ВложенныйЗапрос.Склад
Этот запрос является наиболее универсальным. Он корректно отработает ситуации, когда товар только в резерве (но еще не поступил на баланс при определенных настройках) или когда товар есть на остатке, но нет резервов.
В ходе обсуждения также был предложен вариант с использованием ЛЕВОЕ СОЕДИНЕНИЕ. Этот способ часто кажется более наглядным, но он таит в себе опасность. Если вы используете левое соединение от таблицы ТоварыНаСкладах к таблице ТоварыКОтгрузке, вы рискуете потерять информацию о резервах, если по какой-то причине остаток товара в регистре ТоварыНаСкладах равен нулю или запись отсутствует.
Однако, если мы уверены, что нас интересуют свободные остатки только по тем товарам, которые физически есть на складе, такой подход допустим. Рассмотрим пример с использованием функции ЕСТЬNULL, которая обязательна при соединениях, чтобы избежать математических операций с пустотой.
ВЫБРАТЬ
СвободныеОстаткиОстатки.Номенклатура КАК Номенклатура,
СвободныеОстаткиОстатки.Склад КАК Склад,
// Формула: Наличие - (КОтгрузке + ВРезерве)
СУММА(
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0)
- ЕСТЬNULL(ВложенныйЗапрос.КОтгрузкеОстаток, 0)
- ЕСТЬNULL(ВложенныйЗапрос.ВРезервеОстаток, 0)
) КАК СвободныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаОстатков, Склад = &Склад) КАК СвободныеОстаткиОстатки
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
ТоварыКОтгрузкеОстатки.КОтгрузкеОстаток КАК КОтгрузкеОстаток,
ТоварыКОтгрузкеОстатки.ВРезервеОстаток КАК ВРезервеОстаток,
ТоварыКОтгрузкеОстатки.Номенклатура КАК Номенклатура,
ТоварыКОтгрузкеОстатки.Склад КАК Склад
ИЗ
РегистрНакопления.ТоварыКОтгрузке.Остатки(&ДатаОстатков, Склад = &Склад) КАК ТоварыКОтгрузкеОстатки
) КАК ВложенныйЗапрос
ПО СвободныеОстаткиОстатки.Номенклатура = ВложенныйЗапрос.Номенклатура
И СвободныеОстаткиОстатки.Склад = ВложенныйЗапрос.Склад
СГРУППИРОВАТЬ ПО
СвободныеОстаткиОстатки.Склад,
СвободныеОстаткиОстатки.Номенклатура
Важное примечание: Чтобы сделать этот запрос безопасным и аналогом первого способа, вместо ЛЕВОЕ СОЕДИНЕНИЕ следует использовать ПОЛНОЕ СОЕДИНЕНИЕ, однако конструкция ОБЪЕДИНИТЬ ВСЕ (Способ 1) обычно работает быстрее и предсказуемее в плане производительности на больших объемах данных.
При написании подобных запросов в реальных базах УТ 11.5 часто приходится учитывать дополнительные аналитики, которые мы опустили для простоты примеров:
Характеристика обязательно должно быть добавлено в список полей выборки и группировки. Иначе вы получите общий остаток по артикулу, что может быть неверно.Назначение.
Назначение = ЗНАЧЕНИЕ(Справочник.Назначения.ПустаяСсылка) (или соответствующий пустой ключ аналитики).Разберем ошибки, с которыми столкнулся автор темы, чтобы вы их не повторяли:
ОБЪЕДИНИТЬ без последующей группировки. Сама по себе команда объединения просто "склеивает" две таблицы одна под другой. Группировка СГРУППИРОВАТЬ ПО обязательна.NULL, результат будет NULL. Всегда оборачивайте поля из присоединенных таблиц в функцию ЕСТЬNULL(Поле, 0). В примере с ОБЪЕДИНИТЬ ВСЕ мы решаем эту проблему, явно прописывая 0 в колонках, которых нет в исходной таблице.СУММА(...) внутри частей объединения, если вы потом снова суммируете это во внешнем запросе. Агрегацию лучше проводить один раз — на самом верхнем уровне.Используя первый приведенный метод (с объединением), вы получите наиболее корректные данные о доступности товаров, которые будут совпадать с типовыми отчетами 1С:Управление торговлей.