При построении управленческих складских отчетов часто возникает необходимость вывести не только количественные движения номенклатуры, но и стоимостную оценку каждого документа по определенному типу цен. Типовая сложность заключается в том, что цены периодически меняются, а виртуальная таблица СрезПоследних регистра сведений возвращает срез только на одну фиксированную дату. Если в отчете соединить все движения за период с ценами на конец интервала, документы прошлых периодов рассчитаются по неактуальной стоимости. Кроме того, документ переоценки (установки цен) сам по себе меняет ценность текущего складского остатка на разницу между новой и старой ценой.
Разберем подробно, как решить данную задачу с помощью пакетного запроса, как получить срез цен на момент проведения каждого документа и как рассчитать влияние изменения цен на общую стоимость товара.
Проанализируем типовую ошибку: разработчик берет обороты регистра накопления ТоварыОрганизаций за период и выполняет соединение с таблицей РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонПер, ...). В таком случае оценка всех приходов и расходов за месяц или год рассчитывается по финальной цене, что полностью искажает хронологию и промежуточные итоги.
Вторая проблема — отображение самого факта переоценки. Если складской остаток составлял 10 штук по цене 100 рублей, а документом установки цен стоимость поднялась до 150 рублей, то суммарная оценка склада увеличилась на 500 рублей: (150 - 100) * 10. В отчете этот документ должен быть показан с суммой +500, даже если движения в регистре накопления по нему не формировались.
Рассмотрим логику построения многоэтапного пакетного запроса во временных таблицах:
ЦеныНоменклатуры по требуемому виду цен только для тех позиций, которые фигурировали в оборотах склада. Это позволяет отсечь лишние данные и ускорить дальнейшую обработку.Цены.Период <= Товары.Период и берем максимальную дату цены с помощью группировки МАКСИМУМ(Цены.Период).(НоваяЦена - СтараяЦена).ОБЪЕДИНИТЬ ВСЕ соединяем входящий остаток на начало периода, движения товаров по ценам на момент проведения документов и суммы переоценок, умноженные на накопленный остаток.Посмотрим на практический пример запроса, решающего поставленную задачу:
ВЫБРАТЬ
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Регистратор КАК Регистратор,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.Характеристика КАК Характеристика,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Цена КАК Цена
ПОМЕСТИТЬ ВсеЦеныНаНужныеТовары
ИЗ
РегистрНакопления.ТоварыОрганизаций.Обороты(
,
,
Регистратор,
Склад = &Склад
И Организация = &Организация) КАК ТоварыОрганизацийОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО (ТоварыОрганизацийОбороты.Номенклатура = ЦеныНоменклатуры.Номенклатура)
И (ТоварыОрганизацийОбороты.Характеристика = ЦеныНоменклатуры.Характеристика)
ГДЕ
ЦеныНоменклатуры.ВидЦены = &ВидЦены
СГРУППИРОВАТЬ ПО
ЦеныНоменклатуры.Регистратор,
ЦеныНоменклатуры.Номенклатура,
ЦеныНоменклатуры.Характеристика,
ЦеныНоменклатуры.ВидЦены,
ЦеныНоменклатуры.Цена,
ЦеныНоменклатуры.Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТоварыОрганизаций.Регистратор КАК РегистраторТО,
ТоварыОрганизаций.Номенклатура КАК НоменклатураТО,
ТоварыОрганизаций.Период КАК Период,
МАКСИМУМ(ВсеЦены.Период) КАК ПериодТО,
ТоварыОрганизаций.Характеристика КАК ХарактеристикаТО,
ТоварыОрганизаций.КоличествоОборот КАК КоличествоОборот
ПОМЕСТИТЬ ЦеныТоваровИВремяПоследнейЦены
ИЗ
РегистрНакопления.ТоварыОрганизаций.Обороты(
,
,
Регистратор,
Склад = &Склад
И Организация = &Организация) КАК ТоварыОрганизаций
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВсеЦеныНаНужныеТовары КАК ВсеЦены
ПО ТоварыОрганизаций.Период >= ВсеЦены.Период
И (ВсеЦены.Номенклатура = ТоварыОрганизаций.Номенклатура)
И (ВсеЦены.Характеристика = ТоварыОрганизаций.Характеристика)
СГРУППИРОВАТЬ ПО
ТоварыОрганизаций.Номенклатура,
ТоварыОрганизаций.Характеристика,
ТоварыОрганизаций.Регистратор,
ТоварыОрганизаций.Период,
ТоварыОрганизаций.КоличествоОборот
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
МАКСИМУМ(ЦеныНоменклатуры.Период) КАК Период,
МАКСИМУМ(ЦеныДоИзменения.Период) КАК ПериодДо,
ЦеныНоменклатуры.Регистратор КАК Регистратор,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.Характеристика КАК Характеристика
ПОМЕСТИТЬ ТаблицаЦен
ИЗ
ВсеЦеныНаНужныеТовары КАК ЦеныНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ ВсеЦеныНаНужныеТовары КАК ЦеныДоИзменения
ПО ЦеныНоменклатуры.Период > ЦеныДоИзменения.Период
И (ЦеныДоИзменения.Номенклатура = ЦеныНоменклатуры.Номенклатура)
И (ЦеныДоИзменения.Характеристика = ЦеныНоменклатуры.Характеристика)
СГРУППИРОВАТЬ ПО
ЦеныНоменклатуры.Регистратор,
ЦеныНоменклатуры.Номенклатура,
ЦеныНоменклатуры.Характеристика
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТаблицаЦен.Регистратор КАК Регистратор,
ТаблицаЦен.Номенклатура КАК Номенклатура,
ТаблицаЦен.Характеристика КАК Характеристика,
ВсеЦеныНаНужныеТовары.Цена КАК Цена,
ТаблицаЦен.Период КАК Период
ПОМЕСТИТЬ ТаблицаНовыхЦен
ИЗ
ТаблицаЦен КАК ТаблицаЦен
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВсеЦеныНаНужныеТовары КАК ВсеЦеныНаНужныеТовары
ПО ТаблицаЦен.Период = ВсеЦеныНаНужныеТовары.Период
И (ВсеЦеныНаНужныеТовары.Номенклатура = ТаблицаЦен.Номенклатура)
И (ВсеЦеныНаНужныеТовары.Характеристика = ТаблицаЦен.Характеристика)
СГРУППИРОВАТЬ ПО
ТаблицаЦен.Регистратор,
ТаблицаЦен.Номенклатура,
ТаблицаЦен.Характеристика,
ВсеЦеныНаНужныеТовары.Цена,
ТаблицаЦен.Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТаблицаЦен.Регистратор КАК Регистратор,
ТаблицаЦен.Номенклатура КАК Номенклатура,
ТаблицаЦен.Характеристика КАК Характеристика,
ВсеЦеныНаНужныеТовары.Цена КАК Цена
ПОМЕСТИТЬ ТаблицаСтарыхЦен
ИЗ
ТаблицаЦен КАК ТаблицаЦен
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВсеЦеныНаНужныеТовары КАК ВсеЦеныНаНужныеТовары
ПО ТаблицаЦен.Номенклатура = ВсеЦеныНаНужныеТовары.Номенклатура
И ТаблицаЦен.Характеристика = ВсеЦеныНаНужныеТовары.Характеристика
И ТаблицаЦен.ПериодДо = ВсеЦеныНаНужныеТовары.Период
СГРУППИРОВАТЬ ПО
ТаблицаЦен.Регистратор,
ТаблицаЦен.Номенклатура,
ТаблицаЦен.Характеристика,
ВсеЦеныНаНужныеТовары.Цена
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТаблицаНовыхЦен.Регистратор КАК Регистратор,
ТаблицаНовыхЦен.Номенклатура КАК Номенклатура,
ТаблицаНовыхЦен.Характеристика КАК Характеристика,
ТаблицаНовыхЦен.Цена - ТаблицаСтарыхЦен.Цена КАК Цена,
ТаблицаНовыхЦен.Период КАК Период
ПОМЕСТИТЬ ФинальныеЦены
ИЗ
ТаблицаСтарыхЦен КАК ТаблицаСтарыхЦен
ЛЕВОЕ СОЕДИНЕНИЕ ТаблицаНовыхЦен КАК ТаблицаНовыхЦен
ПО ТаблицаСтарыхЦен.Номенклатура = ТаблицаНовыхЦен.Номенклатура
И ТаблицаСтарыхЦен.Характеристика = ТаблицаНовыхЦен.Характеристика
ГДЕ
ТаблицаНовыхЦен.Регистратор = ТаблицаСтарыхЦен.Регистратор
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
"Остаток на начало периода" КАК Регистратор,
СУММА(ТоварыДо.КоличествоОборот * ЦеныДо.Цена) КАК Оценка
ИЗ
(ВЫБРАТЬ
ТоварыОрганизацийОбороты.Номенклатура КАК Номенклатура,
ТоварыОрганизацийОбороты.Характеристика КАК Характеристика,
ТоварыОрганизацийОбороты.КоличествоОборот КАК КоличествоОборот,
ТоварыОрганизацийОбороты.Регистратор КАК Регистратор
ИЗ
РегистрНакопления.ТоварыОрганизаций.Обороты(
,
&НачПер,
Регистратор,
Склад = &Склад
И Организация = &Организация) КАК ТоварыОрганизацийОбороты) КАК ТоварыДо
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Характеристика КАК Характеристика,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&НачПер, ВидЦены = &ВидЦены) КАК ЦеныНоменклатурыСрезПоследних) КАК ЦеныДо
ПО (ТоварыДо.Номенклатура = ЦеныДо.Номенклатура)
И (ТоварыДо.Характеристика = ЦеныДо.Характеристика)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ЦеныТоваровИВремяПоследнейЦены.РегистраторТО,
СУММА(ЦеныНоменклатуры.Цена * ЦеныТоваровИВремяПоследнейЦены.КоличествоОборот)
ИЗ
ЦеныТоваровИВремяПоследнейЦены КАК ЦеныТоваровИВремяПоследнейЦены
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВсеЦеныНаНужныеТовары КАК ЦеныНоменклатуры
ПО ЦеныТоваровИВремяПоследнейЦены.НоменклатураТО = ЦеныНоменклатуры.Номенклатура
И ЦеныТоваровИВремяПоследнейЦены.ХарактеристикаТО = ЦеныНоменклатуры.Характеристика
И ЦеныТоваровИВремяПоследнейЦены.ПериодТО = ЦеныНоменклатуры.Период
ГДЕ
ЦеныНоменклатуры.ВидЦены = &ВидЦены
И ЦеныТоваровИВремяПоследнейЦены.Период МЕЖДУ &НачПер И &КонПер
СГРУППИРОВАТЬ ПО
ЦеныТоваровИВремяПоследнейЦены.РегистраторТО
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ФинальныеЦены.Регистратор,
СУММА(ФинальныеЦены.Цена * ТоварыОрганизацийОбороты.КоличествоОборот)
ИЗ
ФинальныеЦены КАК ФинальныеЦены
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций.Обороты(
,
&КонПер,
Регистратор,
Склад = &Склад
И Организация = &Организация) КАК ТоварыОрганизацийОбороты
ПО (ФинальныеЦены.Номенклатура = ТоварыОрганизацийОбороты.Номенклатура)
И (ФинальныеЦены.Характеристика = ТоварыОрганизацийОбороты.Характеристика)
И (ФинальныеЦены.Период >= ТоварыОрганизацийОбороты.Период)
ГДЕ
ФинальныеЦены.Период > &НачПер
СГРУППИРОВАТЬ ПО
ФинальныеЦены.Регистратор
ИТОГИ ПО
ОБЩИЕ
Рассмотренный выше подход полностью реализуем средствами языка запросов 1С, однако имеет ряд ограничений при росте объема базы данных. Проанализируем важные нюансы, которые следует учитывать при промышленной эксплуатации:
Период >= Период вынуждает СУБД выполнять сканирование с высокой вычислительной нагрузкой. Если записей много, во временных таблицах критически важно добавлять директиву ИНДЕКСИРОВАТЬ ПО Номенклатура, Характеристика, Период.СрезПоследних с параметром &Период, куда в настройках связи передается дата регистратора из первого набора. Платформа самостоятельно оптимизирует такие запросы на уровне СУБД.ROW_NUMBER() OVER (PARTITION BY Номенклатура, Характеристика ORDER BY Период УБЫВ), что исключает тяжелые самосоединения временных таблиц.