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