Как в СКД получить стоимость поступления из двух таблиц документа («Запасы» и «Расходы»)?

← На главную

При разработке кастомных отчетов по валовой прибыли в конфигурации «1С:Управление нашей фирмой» (УНФ 1.6) программисты часто сталкиваются с задачей вывода цены последнего прихода для анализа маржинальности. Стандартный механизм себестоимости не всегда устраивает руководство, и возникает необходимость рассчитывать прибыль на основе цен документов поступления. При этом важно учесть, что поступление может содержать как товары (табличная часть Запасы), так и услуги (табличная часть Расходы).

В этой статье мы подробно разберем, почему попытки прямого объединения таблиц могут не выводить данные по услугам, проанализируем ключевые логические и синтаксические ошибки в запросах и построим правильный, оптимизированный пакет запросов для схемы компоновки данных (СКД).

Анализ ошибок в исходном запросе

Рассмотрим подробнее причины, по которым отчет автора темы не выводил данные по услугам, а также укажем на критические ошибки оптимизации.

Несовместимость типов в операции ОБЪЕДИНИТЬ. В первом запросе пакета (для товаров) выбирается поле Характеристика. Во втором запросе (для услуг) на месте характеристики жестко прописано число 0. В СКД при левом соединении таблицы продаж с ценами по условию ЦеныПоступления.Характеристика = ПродажиОбороты.Характеристика система пытается сравнить ссылку на справочник характеристик с числом 0. В СУБД такое сравнение всегда возвращает ЛОЖЬ, из-за чего данные по услугам полностью исключаются из результирующей выборки.
Логическая ошибка получения последней цены через МАКСИМУМ. Конструкция МАКСИМУМ(Дата) и МАКСИМУМ(Цена) с группировкой по номенклатуре работает некорректно. Функция МАКСИМУМ(Цена) вернет не цену последнего документа, а просто самую высокую цену товара за весь период. Если товар когда-то стоил 100 рублей, а в последней накладной пришел по 50 рублей, запрос вернет дату последнего прихода, но цену — 100 рублей.
Использование избыточного свойства .Ссылка. Конструкции вида Номенклатура.Ссылка и Характеристика.Ссылка приводят к созданию лишних неявных левых соединений в СУБД. Поля табличной части Номенклатура и Характеристика уже содержат ссылки.
Фильтрация по строковому наименованию организации. Условие Организация.Наименование = "Общество..." крайне непроизводительно и ненадежно. При малейшем изменении названия организации (например, добавлении лишнего пробела) отчет перестанет работать. Правильно использовать параметры запроса.

Шаг 1. Исправление несовместимости типов

Выясним причину сбоя при объединении. У услуг (в отличие от товаров) нет характеристик. Чтобы левое соединение с регистром продаж не «отваливалось», мы должны передать во вторую часть объединения пустую ссылку нужного типа, а не число. Для этого воспользуемся конструкцией ЗНАЧЕНИЕ(...).

Вместо числового нуля напишем:

ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка) КАК Характеристика

Шаг 2. Разработка правильного среза последних цен в пакете запросов

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

ВТ_МаксимальныеДаты: Сначала соберем все даты последних поступлений в разрезе номенклатуры и характеристик, объединив таблицы запасов и расходов.
ВТ_ЦеныПоступления: Затем соединим полученные максимальные даты с исходными табличными частями документов, чтобы вытащить точную цену, актуальную именно на эту дату.

Проанализируем пример реализации этого алгоритма в виде готового запроса для СКД:


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

СГРУППИРОВАТЬ ПО
	ПоступлениеЗапасы.Номенклатура,
	ПоступлениеЗапасы.Характеристика

ОБЪЕДИНИТЬ ВСЕ

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

СГРУППИРОВАТЬ ПО
	ПоступлениеРасходы.Номенклатура
;

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

СГРУППИРОВАТЬ ПО
	ВТ_МаксимальныеДаты.Номенклатура,
	ВТ_МаксимальныеДаты.Характеристика
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2.3: Получаем реальные цены на эти даты
ВЫБРАТЬ
	ВТ_ИтоговыеДаты.Номенклатура КАК Номенклатура,
	ВТ_ИтоговыеДаты.Характеристика КАК Характеристика,
	МАКСИМУМ(ПоступлениеЗапасы.Цена) КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныПоступления
ИЗ
	ВТ_ИтоговыеДаты КАК ВТ_ИтоговыеДаты
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная.Запасы КАК ПоступлениеЗапасы
		ПО ВТ_ИтоговыеДаты.Номенклатура = ПоступлениеЗапасы.Номенклатура
			И ВТ_ИтоговыеДаты.Характеристика = ПоступлениеЗапасы.Характеристика
			И ВТ_ИтоговыеДаты.Дата = ПоступлениеЗапасы.Ссылка.Дата

СГРУППИРОВАТЬ ПО
	ВТ_ИтоговыеДаты.Номенклатура,
	ВТ_ИтоговыеДаты.Характеристика

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	ВТ_ИтоговыеДаты.Номенклатура,
	ВТ_ИтоговыеДаты.Характеристика,
	МАКСИМУМ(ПоступлениеРасходы.Цена)
ИЗ
	ВТ_ИтоговыеДаты КАК ВТ_ИтоговыеДаты
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная.Расходы КАК ПоступлениеРасходы
		ПО ВТ_ИтоговыеДаты.Номенклатура = ПоступлениеРасходы.Номенклатура
			И ВТ_ИтоговыеДаты.Дата = ПоступлениеРасходы.Ссылка.Дата
ГДЕ
	ВТ_ИтоговыеДаты.Характеристика = ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка)

СГРУППИРОВАТЬ ПО
	ВТ_ИтоговыеДаты.Номенклатура,
	ВТ_ИтоговыеДаты.Характеристика
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2.4: Финальное объединение цен (устранение возможных дублей)
ВЫБРАТЬ
	ВТ_ЦеныПоступления.Номенклатура КАК Номенклатура,
	ВТ_ЦеныПоступления.Характеристика КАК Характеристика,
	МАКСИМУМ(ВТ_ЦеныПоступления.Цена) КАК Цена
ПОМЕСТИТЬ ЦеныПоступления
ИЗ
	ВТ_ЦеныПоступления КАК ВТ_ЦеныПоступления

СГРУППИРОВАТЬ ПО
	ВТ_ЦеныПоступления.Номенклатура,
	ВТ_ЦеныПоступления.Характеристика

Обратите внимание на использование оператора ОБЪЕДИНИТЬ ВСЕ вместо ОБЪЕДИНИТЬ. Оператор без приставки ВСЕ заставляет СУБД выполнять дополнительное сравнение строк для удаления дублей, что сильно снижает скорость выполнения запроса на больших базах данных.

Шаг 3. Соединение цен с продажами в основном запросе

Теперь мы можем связать полученную временную таблицу цен с оборотами регистра продаж. Код связи в результирующем запросе будет выглядеть следующим образом:


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

Альтернативный архитектурный подход (Рекомендуемый)

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

В конфигурации УНФ 1.6 для этих целей существует специализированный регистр накопления «Закупки». В нем уже содержатся готовые, проведенные данные как по закупкам товаров, так и по закупкам услуг. Обращение к виртуальной таблице оборотов этого регистра существенно упростит логику и повысит быстродействие отчета.

Посмотрим на пример изящного получения цен закупки через регистр накопления:


ВЫБРАТЬ
	ЗакупкиОбороты.Номенклатура КАК Номенклатура,
	ЗакупкиОбороты.Характеристика КАК Характеристика,
	МАКСИМУМ(ЗакупкиОбороты.Регистратор.Дата) КАК Дата
ПОМЕСТИТЬ ВТ_ДатыЗакупок
ИЗ
	РегистрНакопления.Закупки.Обороты(, &КонецПериода, Авто, Организация = &Организация) КАК ЗакупкиОбороты

СГРУППИРОВАТЬ ПО
	ЗакупкиОбороты.Номенклатура,
	ЗакупкиОбороты.Характеристика
;

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

СГРУППИРОВАТЬ ПО
	ВТ_ДатыЗакупок.Номенклатура,
	ВТ_ДатыЗакупок.Характеристика

Использование регистра Закупки избавляет от необходимости вручную объединять таблицы запасов и расходов документов, автоматически отсекает непроведенные документы и учитывает любые другие документы поступления (например, закупку через подотчетника в авансовом отчете).