При разработке отчетов и доработке алгоритмов в конфигурациях 1С:ЗУП 3.1 и 1C:ERP часто возникает задача: получить данные по отработанному времени и начислениям, но строго ограничить список сотрудников теми, кто присутствует в определенном документе. В нашем случае — в документе «Распределение основного заработка».
Проанализируем ситуацию: разработчик пытается соединить данные из регистра расчета Начисления, регистра накопления БухучетНачисленияУдержанияПоСотрудникам и регистра сведений БухучетРаспределениеОсновногоЗаработка. Однако на выходе получается либо слишком мало сотрудников (теряются данные), либо список дублируется (строки умножаются), либо суммы и часы выдают значение NULL.
Рассмотрим подробнее, почему возникают эти ошибки, выясним причину дублирования данных и разберем по шагам, как построить корректный и оптимальный запрос.
В процессе поиска решения разработчик попробовал использовать внутреннее соединение таблиц (INNER JOIN):
ИЗ РегистрНакопления.БухучетНачисленияУдержанияПоСотрудникам КАК БухучетНачисленияУдержанияПоСотрудникам
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Доля КАК ВТ_Доля
ПО БухучетНачисленияУдержанияПоСотрудникам.Сотрудник = ВТ_Доля.Сотрудник
Результат оказался предсказуемым: количество сотрудников сократилось с ожидаемых 79 до 68. Внутреннее соединение оставляет только те записи, которые есть в обеих таблицах. Если у сотрудника было распределение, но в текущем запросе (с учетом всех фильтров, например, по полю ВклВРаспределениеПЭО) нет соответствующих начислений, он просто выпадает из итогового результата.
Чтобы список сотрудников не уменьшался, базовой таблицей в запросе должен выступать именно регистр распределения, к которому мы присоединяем всё остальное с помощью левого соединения (LEFT JOIN).
Когда разработчик сделал базовой таблицу распределения, он попытался связать ее с часами и начислениями следующим образом:
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Часы КАК ВТ_Часы
ПО БухучетРаспределениеОсновногоЗаработка.Сотрудник = ВТ_Часы.Сотрудник
И БухучетРаспределениеОсновногоЗаработка.Регистратор = ВТ_Часы.Регистратор
В результате часы полностью исчезли. Выясним причину: главная ошибка здесь заключается в соединении по полю Регистратор.
В системе 1С:ЗУП 3.1 для регистра сведений БухучетРаспределениеОсновногоЗаработка регистратором является документ «Распределение основного заработка». Однако для регистра расчета Начисления или регистра накопления БухучетНачисленияУдержанияПоСотрудникам регистратором чаще всего выступает документ «Начисление зарплаты и взносов», «Отпуск», «Больничный лист» и т.д. Поскольку это разные физические документы, условие связи по регистратору всегда будет ложным (пустым).
Связывать такие таблицы нужно исключительно по логическим ключам: Сотрудник, ПериодРегистрации (месяц начисления) и, при необходимости, Подразделение или Организация.
Отказ от связи по регистратору привел к новой проблеме: часы начали дублироваться, а данных по сотруднику стало выводиться по 4-5 строк. Посмотрим на причину этого явления. Оно связано с дискретностью (детализацией) данных в разных регистрах:
БухучетНачисленияУдержанияПоСотрудникам данные хранятся максимально детально: в разрезе документов-оснований и конкретных видов расчета (оклад, премия, надбавки).БухучетРаспределениеОсновногоЗаработка данные хранятся в разрезе статей финансирования и способов отражения зарплаты. У одного сотрудника может быть несколько строк распределения с разными долями (сумма которых стремится к 100%).Если мы соединяем эти таблицы «напрямую», без предварительной подготовки, происходит декартово произведение (каждая строка начислений умножается на каждую строку распределения). Отсюда и берется многократное дублирование.
Сформируем логически верную структуру запроса. Нам необходимо сначала сгруппировать данные начислений и часов до уровня сотрудника и месяца, а затем левым соединением присоединить их к таблице распределения.
Шаг 1. Получаем базу распределения.
Формируем временную таблицу (ВТ) из регистра сведений по распределению заработка. Это наш «золотой» список из 79 сотрудников.
Шаг 2. Получаем начисления.
Для регистра накопления настоятельно рекомендуется использовать виртуальную таблицу .Обороты. Это автоматически свернет данные по периодам и избавит от сторнировочных записей (минусов), которые дублируются в физической таблице.
Шаг 3. Соединение и обработка пустых значений (NULL).
Делаем левое соединение базы к начислениям. Так как у сотрудника может быть документ распределения, но может не оказаться рабочих часов, соединение вернет NULL. Для математических операций используем функцию ЕСТЬNULL(Значение, 0).
Рассмотрим пример итогового запроса, который решает все описанные проблемы:
ВЫБРАТЬ
Распределение.Сотрудник КАК Сотрудник,
Распределение.ПериодРегистрации КАК Месяц,
Распределение.СтатьяФинансирования КАК СтатьяФинансирования,
Распределение.СпособОтраженияЗарплатыВБухучете КАК СпособОтражения,
Распределение.ДоляРаспределения КАК ДоляРаспределения
ПОМЕСТИТЬ ВТ_БазаРаспределения
ИЗ
РегистрСведений.БухучетРаспределениеОсновногоЗаработка КАК Распределение
ГДЕ
Распределение.ПериодРегистрации МЕЖДУ &НачПериода И &КонПериода
И Распределение.СпособОтраженияЗарплатыВБухучете = &Способ
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
Месяц
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Начисления.Сотрудник КАК Сотрудник,
НАЧАЛОПЕРИОДА(Начисления.ПериодРегистрации, МЕСЯЦ) КАК Месяц,
СУММА(Начисления.ОтработаноЧасов) КАК ОтработаноЧасов
ПОМЕСТИТЬ ВТ_СводныеЧасы
ИЗ
РегистрРасчета.Начисления КАК Начисления
ГДЕ
Начисления.ПериодРегистрации МЕЖДУ &НачПериода И &КонПериода
И Начисления.ВидРасчета.ВидВремени = &ВидВремени
СГРУППИРОВАТЬ ПО
Начисления.Сотрудник,
НАЧАЛОПЕРИОДА(Начисления.ПериодРегистрации, МЕСЯЦ)
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
Месяц
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
БухучетОбороты.Сотрудник КАК Сотрудник,
НАЧАЛОПЕРИОДА(БухучетОбороты.Период, МЕСЯЦ) КАК Месяц,
СУММА(БухучетОбороты.СуммаОборот) КАК СуммаНачислений
ПОМЕСТИТЬ ВТ_СводныеНачисления
ИЗ
РегистрНакопления.БухучетНачисленияУдержанияПоСотрудникам.Обороты(
&НачПериода,
&КонПериода,
Запись,
НачислениеУдержание.ВклВРаспределениеПЭО = ИСТИНА
) КАК БухучетОбороты
СГРУППИРОВАТЬ ПО
БухучетОбороты.Сотрудник,
НАЧАЛОПЕРИОДА(БухучетОбороты.Период, МЕСЯЦ)
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
Месяц
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
База.Сотрудник КАК Сотрудник,
База.СтатьяФинансирования КАК СтатьяФинансирования,
База.СпособОтражения КАК СпособОтраженияЗарплатыВБухучете,
База.ДоляРаспределения КАК ДоляРаспределения,
ЕСТЬNULL(Часы.ОтработаноЧасов, 0) КАК ЧасыПолные,
ЕСТЬNULL(Начисления.СуммаНачислений, 0) КАК СуммаПолная,
ЕСТЬNULL(Часы.ОтработаноЧасов * База.ДоляРаспределения / 100, 0) КАК РаспределенныеЧасы
ИЗ
ВТ_БазаРаспределения КАК База
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СводныеЧасы КАК Часы
ПО База.Сотрудник = Часы.Сотрудник
И База.Месяц = Часы.Месяц
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СводныеНачисления КАК Начисления
ПО База.Сотрудник = Начисления.Сотрудник
И База.Месяц = Начисления.Месяц
Проанализировав данный код, мы видим, что применение архитектурно правильного подхода решает сразу все проблемы:
ВТ_БазаРаспределения гарантирует, что в результат попадут строго те самые 79 сотрудников из документа, так как она является левой таблицей в финальном соединении.СГРУППИРОВАТЬ ПО и использование виртуальных таблиц .Обороты сворачивают данные по сотруднику до одной строки за месяц. Это полностью исключает декартово умножение и раздувание результата.Сотрудник и Месяц, а не по физическому документу-регистратору.ЕСТЬNULL спасает арифметику при расчете РаспределенныеЧасы, когда начисления у сотрудника в данном месяце еще не были проведены.Используйте этот шаблон формирования запросов при работе с блоком зарплаты и кадров в 1С. Разделение получения списков и фактов начислений через временные таблицы с обязательной агрегацией — залог точных отчетов и быстрой работы программы.