Как добавить цену номенклатуры из регистра сведений в запрос 1С, использующий временные таблицы и объединения?

← На главную

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

Анализируем исходный запрос и определяем задачу

У нас имеется запрос, который собирает информацию об остатках из РегистрБухгалтерии.Типовой и о резервах из РегистрНакопления.ТоварыВРезервеНаСкладах. Эти данные объединяются и помещаются во временную таблицу Врем. Затем из этой временной таблицы выбираются агрегированные данные. Наша задача — дополнить этот запрос актуальными ценами номенклатуры из регистра сведений "Цены номенклатуры".

Давайте посмотрим на первоначальный вид запроса:


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

Ключевые моменты, которые мы должны учесть для добавления цен:

В текущем запросе в результирующей выборке из Врем отсутствует поле Номенклатура. Это критично, так как без него мы не сможем связать данные с регистром цен.
Оператор ОБЪЕДИНИТЬ ВСЕ служит для объединения строк, а не для добавления колонок путем соединения. Попытка вставить ЛЕВОЕ СОЕДИНЕНИЕ непосредственно после одного из блоков ОБЪЕДИНИТЬ ВСЕ не даст желаемого результата и приведет к синтаксическим ошибкам или некорректным данным.
Цены номенклатуры обычно хранятся в регистре сведений и требуют получения среза последних цен на определенную дату.
Может потребоваться получение цен нескольких типов (например, РРЦ и ОПТ).

Шаг 1: Включаем номенклатуру во временную таблицу

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

В первой части запроса, где мы выбираем из РегистрБухгалтерии.Типовой.Остатки, номенклатура представлена полем Субконто1. Мы выберем его как Номенклатура.
Во второй части запроса, где мы выбираем из РегистрНакопления.ТоварыВРезервеНаСкладах.Остатки, номенклатура уже есть как поле Номенклатура.

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


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

Шаг 2: Присоединяем цены из регистра сведений в итоговом запросе

Теперь, когда наша временная таблица Врем содержит поле Номенклатура, мы можем присоединить к ней данные о ценах. Присоединение следует выполнять в последнем блоке запроса, который выбирает данные из Врем. Для получения актуальных цен мы будем использовать виртуальную таблицу СрезПоследних регистра сведений "Цены номенклатуры".

Если нам нужны цены разных типов (например, РРЦ и ОПТ), мы можем выполнить несколько ЛЕВОЕ СОЕДИНЕНИЕ с виртуальной таблицей ЦеныНоменклатуры.СрезПоследних, каждый раз устанавливая отбор по нужному типу цен. Присоединение должно быть по Номенклатура и, при необходимости, по Организация, если цены зависят от организации.

Важно помнить, что если для какой-то номенклатуры цена не будет найдена, ЛЕВОЕ СОЕДИНЕНИЕ вернет NULL. В этом случае полезно использовать функцию ЕСТЬNULL для замены NULL на 0 или другое значение по умолчанию.

Давайте скорректируем вторую часть запроса:


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

Шаг 3: Устанавливаем параметры запроса

Не забываем установить все необходимые параметры для нашего запроса:


Запрос.УстановитьПараметр("Номенклатура", ТекНоменклатура);
Запрос.УстановитьПараметр("Организация", Объект.Организация);
Запрос.УстановитьПараметр("ДатаСреза", ТекущаяДата()); // Параметр для среза остатков
Запрос.УстановитьПараметр("ДатаЦен", ТекущаяДата()); // Параметр для среза цен
// Если типы цен определяются строкой, как в примере из сообщения,
// то нужны параметры для наименований типов цен:
// Запрос.УстановитьПараметр("ТипЦенРРЦ", "РРЦ");
// Запрос.УстановитьПараметр("ТипЦенОПТ", "ОПТ");
// Однако, предпочтительнее использовать ссылки на справочник ТипыЦенНоменклатуры.

Обратите внимание на то, что мы использовали ЗНАЧЕНИЕ(Справочник.ТипыЦенНоменклатуры.РРЦ). Это более надежный способ отбора по типу цен, чем сравнение подстроки из наименования, так как он работает непосредственно с элементами справочника.

Ошибки и важные нюансы

Рассмотрим распространенную ошибку, которая могла возникнуть в процессе экспериментов, например, "Ожидается имя таблицы >ГДЕ". Такая ошибка обычно означает, что оператор ГДЕ был размещен не в том месте. В запросах 1С, после операторов ИЗ и СОЕДИНЕНИЕ, должны следовать условия соединения (ПО), затем фильтры (ГДЕ), а уже потом группировка (СГРУППИРОВАТЬ ПО) и упорядочивание (УПОРЯДОЧИТЬ ПО).

В случае, если необходимо получить курсы валют для цен, мы можем добавить еще одно ЛЕВОЕ СОЕДИНЕНИЕ с регистром сведений ВнутреннийКурсВалют (или аналогичным) к таблицам цен. Например:


...
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(...) КАК ЦеныРРЦ
| ПО Врем.Номенклатура = ЦеныРРЦ.Номенклатура
| И Врем.Организация = ЦеныРРЦ.Организация
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ВнутреннийКурсВалют.СрезПоследних(&ДатаЦен) КАК КурсыВалютРРЦ
| ПО ЦеныРРЦ.Валюта = КурсыВалютРРЦ.Валюта
...
|ВЫБРАТЬ
...
| ЕСТЬNULL(КурсыВалютРРЦ.Курс, 1) КАК КурсРРЦ,
...

Не забывайте включать все новые поля в секцию СГРУППИРОВАТЬ ПО, если они не являются агрегирующими функциями.

Итоговый запрос

Собираем все изменения в один полный запрос. Мы получим данные об остатках и резервах с учетом организации и склада, а также актуальные цены (РРЦ и ОПТ) для каждой номенклатуры на указанную дату.


Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
    | ТиповойОстатки.Организация КАК Организация,
    | ТиповойОстатки.Субконто2 КАК Склад,
    | ТиповойОстатки.Субконто1 КАК Номенклатура,
    | ТиповойОстатки.КоличествоОстаток КАК КоличествоОстаток,
    | 0 КАК Резерв,
    | ТиповойОстатки.СуммаОстаток КАК СуммаОстаток
    |ПОМЕСТИТЬ Врем
    |ИЗ
    | РегистрБухгалтерии.Типовой.Остатки(
    | &ДатаСреза,
    | Счет = ЗНАЧЕНИЕ(ПланСчетов.Типовой.Товары),
    | ,
    | (Организация = &Организация
    | ИЛИ &Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка))
    | И Субконто1 = &Номенклатура) КАК ТиповойОстатки
    |
    |ОБЪЕДИНИТЬ ВСЕ
    |
    |ВЫБРАТЬ
    | ТоварыВРезервеНаСкладахОстатки.Организация,
    | ТоварыВРезервеНаСкладахОстатки.Склад,
    | ТоварыВРезервеНаСкладахОстатки.Номенклатура КАК Номенклатура,
    | 0,
    | ТоварыВРезервеНаСкладахОстатки.КоличествоОстаток,
    | 0
    |ИЗ
    | РегистрНакопления.ТоварыВРезервеНаСкладах.Остатки(
    | &ДатаСреза,
    | (Организация = &Организация
    | ИЛИ &Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка))
    | И Номенклатура = &Номенклатура) КАК ТоварыВРезервеНаСкладахОстатки
    |;
    |
    ////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    | Врем.Организация КАК Организация,
    | Врем.Склад КАК Склад,
    | Врем.Номенклатура КАК Номенклатура,
    | СУММА(Врем.КоличествоОстаток) КАК КоличествоОстаток,
    | СУММА(Врем.Резерв) КАК Резерв,
    | СУММА(Врем.СуммаОстаток) КАК СуммаОстаток,
    | ЕСТЬNULL(ЦеныРРЦ.Цена, 0) КАК ЦенаРРЦ,
    | ЕСТЬNULL(ЦеныОПТ.Цена, 0) КАК ЦенаОПТ,
    | ЦеныРРЦ.Валюта КАК ВалютаЦен
    |ИЗ
    | Врем КАК Врем
    | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
    | &ДатаЦен,
    | ТипЦен = ЗНАЧЕНИЕ(Справочник.ТипыЦенНоменклатуры.РРЦ)
    | И (Номенклатура = &Номенклатура
    | ИЛИ &Номенклатура = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка))
    | И (Организация = &Организация
    | ИЛИ &Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка))
    | ) КАК ЦеныРРЦ
    | ПО Врем.Номенклатура = ЦеныРРЦ.Номенклатура
    | И Врем.Организация = ЦеныРРЦ.Организация
    |
    | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
    | &ДатаЦен,
    | ТипЦен = ЗНАЧЕНИЕ(Справочник.ТипыЦенНоменклатуры.Оптовая)
    | И (Номенклатура = &Номенклатура
    | ИЛИ &Номенклатура = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка))
    | И (Организация = &Организация
    | ИЛИ &Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка))
    | ) КАК ЦеныОПТ
    | ПО Врем.Номенклатура = ЦеныОПТ.Номенклатура
    | И Врем.Организация = ЦеныОПТ.Организация
    |
    |СГРУППИРОВАТЬ ПО
    | Врем.Организация,
    | Врем.Склад,
    | Врем.Номенклатура,
    | ЕСТЬNULL(ЦеныРРЦ.Цена, 0),
    | ЕСТЬNULL(ЦеныОПТ.Цена, 0),
    | ЦеныРРЦ.Валюта
    |
    |УПОРЯДОЧИТЬ ПО
    | Организация,
    | Склад,
    | Номенклатура
    |АВТОУПОРЯДОЧИВАНИЕ";
Запрос.УстановитьПараметр("Номенклатура", ТекНоменклатура);
Запрос.УстановитьПараметр("Организация", Объект.Организация);
Запрос.УстановитьПараметр("ДатаСреза", ТекущаяДата());
Запрос.УстановитьПараметр("ДатаЦен", ТекущаяДата());

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