gifts2017

Формирование отчетов по обязательному медицинскому страхованию на СКД. Написание запроса по ОМС. Вычисление кода региона субъекта РФ в запросе.

Опубликовал Иван Пантелеев (RailMen) в раздел Печать - Универсальные печатные формы

В данной статье рассматриваются основные этапы построения запроса, который позволял бы формировать два вида отчета по ОМС:
1) отчет по принятым в организацию сотрудникам, подлежащим ОМС и
2) отчет по уволенным сотрудникам, подлежащим исключению из числа ОМС.

Прилагаемый файл - настройка для типовой обработки "Консоль отчетов" типовой конфигурации "1С:ЗиУП."

*ОМС – обязательное медицинское страхование

Благодарности:

- Р.С.В.

- Armando http://infostart.ru/profile/16019/

 

Постановка задачи.

Разработать запрос, который бы позволял формировать 2 вида отчета: 

1)                 отчет  по принятым сотрудникам, подлежащим ОМС; причем должно учитываться 2 момента:

1.2)            в отчет  по принятым сотрудникам, подлежащим ОМС, не должны попадать сотрудники, уволенные в периоде формирования отчета;

1.2) в отчет  по принятым сотрудникам, подлежащим ОМС, не должны попадать сотрудники, имеющие на дату отчета действующие полисы ОМС;

2)                 отчет  по уволенным сотрудникам, подлежащим исключению из числа ОМС.

 

Отчет должен иметь следующие настройки:

1)      даты «С» и «ПО» формирования отчета;

2)      «организация» – по какому юр. лицу будет формироваться отчет;

3)      «ПарамИсключениеПоПрописке» - если этот параметр равен строке ”Ростов”, то в отчет не должны попадать сотрудники, которые прописаны в Ростове.

4)      «ПринятыеУволенные» - текстовый параметр, который может принимать значения «Принятые» или «Уволенные», в зависимости от этого параметра строится либо отчет по принятым, либо по уволенным.

 Ключевые прикладные объекты метаданных, таблицы которых будут участвовать в запросе:

1)      регистр сведений РаботникиОрганизаций (с недавних пор у него синоним «Кадровая история сотрудников (по юрлицам)»).

2)      справочник «МедицинскиеСтраховыеПолисы»

3)      регистр сведений  ФИОФизЛиц, а точнее виртуальную таблицу «ФИОФизЛицСрезПоследних» – без нее не обходится ни один запрос и ни одна печатная форма, формируемые для кадров. Частенько даже опытные специалисты под полем «ФИО» подразумевают «Сотрудник.Наименование», что не правильно (некоторым просто лень добавить еще одну таблицу в запрос). В больших организациях это может обернуться конфузами в случаях, когда сотрудницы выходят замуж и меняют фамилию, а потом разводятся и снова меняют фамилию, а потом снова выходят и т.д. Обязательно стоит задавать параметры виртуальной таблицы, например, так:

РегистрСведений.ФИОФизЛиц.СрезПоследних( &ПарамКонПериода,  ФизЛицо В

                                                                              (ВЫБРАТЬ ВТ.Физлицо

                                                                              ИЗ ВТ_ПринятыеУволенныеЗаПериод КАК ВТ))

КАК ФИОФизЛицСрезПоследних

В идеале, если параметры заданы по всем измерениям виртуальной таблицы.

 

4)      справочник  ФизическиеЛица. Многие специалисты получают, например, дату рождения физ лица так: «Штатники.Сотрудник.ФизЛицо.ДатаРождения», что сильно проигрывает в производительности правильному варианту: «ФизЛицо.ДатаРождения». Т.е. три точки и более в получении поля – это бэдово. Причем, для клиент серверного варианта такой запрос может вызвать ошибку выполнения, если количество точек более 4 (точно не помню).  Лучше левом соединением присоединять нужную таблицу, а уж из нее получать необходимые поля. 

5)      виртуальная таблица регистра сведений ГражданствоФизЛицСрезПоследних – получим гражданство. Не забудем указать параметры виртуальной таблицы, например, так:

РегистрСведений.ГражданствоФизЛиц.СрезПоследних( &ПарамКонПериода, ФизЛицо В

                                                      (ВЫБРАТЬ ВТ.Физлицо

                                                      ИЗ ВТ_ПринятыеУволенныеЗаПериод КАК ВТ))

 КАК ГражданствоФизЛицСрезПоследних

6)      виртуальная таблица регистра сведений ПаспортныеДанныеФизЛицСрезПоследних – получим серию и номер паспорта гражданина РФ. Не забудем указать параметры виртуальной таблицы.

7)      реальная таблица «КонтактнаяИнформация» - получим юридический адрес физ. лица. При этом лучше переименовать таблицу «КонтактнаяИнформация» в «КонтактнаяИнформация_ЮрАдресФизЛица». Мне категорически не нравится когда,  многие прославленные специалисты переименовывают таблицы, давая им следующие имена «ФЛнеУ5» или «ЗТХ_4» или «КрупныеКлиенты». Нужно обязательно сохранять в названии таблицы «подсказку» - откуда она берется. На закладке «Связи» эту таблиц можно присоединить например так:

ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КонтактнаяИнформация КАК КонтактнаяИнформация_ЮрАдресФизЛица

                ПО ВТ_ПринятыеУволенныеЗаПериод.Физлицо = КонтактнаяИнформация_ЮрАдресФизЛица.Объект

                               И (КонтактнаяИнформация_ЮрАдресФизЛица.Вид = ЗНАЧЕНИЕ(Справочник.ВидыКонтактнойИнформации.ЮрАдресФизЛица))

8)      прочие таблицы, необходимые для получения полей отчета.

 

 

Основная идея построения запроса, структура пакетов

 

1 пакет запрос. Закладка «Дополнительно», создание временной таблицы ВТ_ПринятыеУволенныеЗаПериод

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

Для реализации подобно функционала в первом пакете получим записи реальной таблицы РС «РаботникиОрганизация», и в зависимости от входного значения «ПринятыеУволенные» будем получать записи за период только принятых или только уволенных сотрудников.

1 пакет нам понадобится для написания  во 2 пакете конструкций вида:

ФизЛицо В

                (ВЫБРАТЬ

                                ВТ.Физлицо

                ИЗ

                                ВТ_ПринятыеУволенныеЗаПериод КАК ВТ)

 

Таблица:

- РС РаботникиОрганизаций

Поля:      

- РаботникиОрганизаций.Сотрудник

- РаботникиОрганизаций.Сотрудник.ФизЛицо (это поле нужно проиндексировать)

- РаботникиОрганизаций.Регистратор               

Условия:

1) ограничение на период

ВЫБОР
 КОГДА &ПринятыеУволенные = "Принятые"
  ТОГДА РаботникиОрганизаций.Период МЕЖДУ &ПарамНачПериода И &ПарамКонПериода
 КОГДА &ПринятыеУволенные = "Уволенные"
  ТОГДА ДОБАВИТЬКДАТЕ(РаботникиОрганизаций.Период, ДЕНЬ, -1) МЕЖДУ &ПарамНачПериода И &ПарамКонПериода
КОНЕЦ

2) отбор по организации

РаботникиОрганизаций.Организация = &ПарамОрганизация

3) отбор штатников:

РаботникиОрганизаций.Сотрудник.ВидДоговора = ЗНАЧЕНИЕ(Перечисление.ВидыДоговоровСФизЛицами.ТрудовойДоговор) И

РаботникиОрганизаций.Сотрудник.ВидЗанятости = ЗНАЧЕНИЕ(Перечисление.ВидыЗанятостиВОрганизации.ОсновноеМестоРаботы)

4) когда формируем списки принятых сотрудников, учтем, что сотрудник может быть принят по срочному договору, и что дата окончания договора может быть ранее, чем дата конца формирования отчета; когда формируем списки уволенных сотрудников это безразлично

ВЫБОР

                КОГДА &ПринятыеУволенные = "Принятые"

                                ТОГДА РаботникиОрганизаций.ПричинаИзмененияСостояния = ЗНАЧЕНИЕ(Перечисление.ПричиныИзмененияСостояния.ПриемНаРаботу)

                                                               И ВЫБОР

                                                                              КОГДА РаботникиОрганизаций.ПериодЗавершения МЕЖДУ &ПарамНачПериода И &ПарамКонПериода

                                                                                                             И РаботникиОрганизаций.ПериодЗавершения <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)

                                                                                              ТОГДА РаботникиОрганизаций.ПричинаИзмененияСостоянияЗавершения

                                                                              ИНАЧЕ РаботникиОрганизаций.ПричинаИзмененияСостояния

                                                               КОНЕЦ <> ЗНАЧЕНИЕ(Перечисление.ПричиныИзмененияСостояния.Увольнение)

                КОГДА &ПринятыеУволенные = "Уволенные"

                                ТОГДА РаботникиОрганизаций.ПричинаИзмененияСостояния = ЗНАЧЕНИЕ(Перечисление.ПричиныИзмененияСостояния.Увольнение)

КОНЕЦ

5) когда формируем списки принятых сотрудников, учтем, что принятый сотрудник может быть уволен в том же  интервале, за который формируют отчет, поэтому нужно проверять чтобы принятый сотрудник не попал в списки уволенных за этот же временной интервал; когда формируем списки уволенных сотрудников это безразлично

ВЫБОР
 КОГДА &ПринятыеУволенные = "Принятые"
  ТОГДА (НЕ РаботникиОрганизаций.Сотрудник В
     (ВЫБРАТЬ
      РаботникиОрганизаций.Сотрудник КАК Сотрудник
     ИЗ
      РегистрСведений.РаботникиОрганизаций КАК РаботникиОрганизаций
     ГДЕ
      ДОБАВИТЬКДАТЕ(РаботникиОрганизаций.Период, ДЕНЬ, -1) МЕЖДУ &ПарамНачПериода И &ПарамКонПериода
      И РаботникиОрганизаций.ПричинаИзмененияСостояния = ЗНАЧЕНИЕ(Перечисление.ПричиныИзмененияСостояния.Увольнение)
      И РаботникиОрганизаций.Организация = &ПарамОрганизация
      И РаботникиОрганизаций.Сотрудник.ВидДоговора = ЗНАЧЕНИЕ(Перечисление.ВидыДоговоровСФизЛицами.ТрудовойДоговор)
      И РаботникиОрганизаций.Сотрудник.ВидЗанятости = ЗНАЧЕНИЕ(Перечисление.ВидыЗанятостиВОрганизации.ОсновноеМестоРаботы)))
 КОГДА &ПринятыеУволенные = "Уволенные"
  ТОГДА ИСТИНА
КОНЕЦ

2 пакет запрос. Закладка «Дополнительно», создание временной таблицы ВТ_Результат

В этом пакете мы к временной таблице ВТ_ПринятыеУволенныеЗаПериод левым соединением будет нанизывать таблицы ФИОФизЛицСрезПоследних, МедицинскиеСтраховыеПолисы, ПаспортныеДанныеФизЛицСрезПоследних и др.

На закладке «Условия» для решения задачи о том, что в отчет не должны попадать сотрудники, которые прописаны, например, в Ростове,  напишем:

 (НЕ ЕСТЬNULL(КонтактнаяИнформация_ЮрАдресФизЛица.Представление, "адрес прописки не указан") ПОДОБНО  & ПарамИсключениеПоПрописке)

 

Так как в отчет  по принятым сотрудникам, подлежащим ОМС, не должны попадать сотрудники, имеющие на дату отчета действующие полисы ОМС, то на закладке «Условия» напишем:

ВЫБОР

                КОГДА &ПринятыеУволенные = "Уволенные"

                                ТОГДА ИСТИНА

                КОГДА &ПринятыеУволенные = "Принятые"

                                ТОГДА ВЫБОР

                                                                КОГДА МедицинскиеСтраховыеПолисы.ДатаОкончанияПолиса > &ПарамКонПериода

                                                                              ТОГДА ЛОЖЬ

                                                               ИНАЧЕ ИСТИНА

                                               КОНЕЦ

КОНЕЦ

 

3 пакет запрос.

Этот пакет в принципе не нужен. Тут ничего не вычисляем и не соединяем. Мы просто выбираем временную таблицу «ВТ_Результат» из 2 пакета и все ее поля. Но все же этот пакет удобен на для отладки. Например, хотим мы посмотреть, что получается в результате выполнения 1 пакета – удаляем из таблиц «ВТ_Результат», выбираем временную таблицу «ВТ_ПринятыеУволенныеЗаПериод» и все ее поля. Все – готово.

Или можем создать еще один пакет-запрос, в котором что-то с чем-то соединить, поместить во временную таблицу и точно так же вывести.

 

История изменений:

07.06.2010 - текст запроса был изменен после рекомендаций Armando, более доходчиво написано для чего нужет 1 пакет-запрос.

08.06.2010 - в первом пакете измен текст условия на периоды

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
ОМС для консоли отчетов 82
.dcf 64,18Kb
09.06.10
108
.dcf 64,18Kb 108 Скачать
ОМС текст запроса
.txt 16,26Kb
09.06.10
39
.txt 16,26Kb 39 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. МagIvan (RailMen) 04.06.10 16:57
Прошу всех заинтересованных лиц и просто любителей писать запросы в студию.
2. МagIvan (RailMen) 04.06.10 17:30
Посмотрел работы других товарищей по тематике ОМС. Многие из них написаны по принципу "работает - ну и ладно". А вот как именно это работает, большинству пользующих эти работы без разницы, а сами создатели, в основном сотрудники франчей (как и я в прошлом), работают по принципу "быстрее сделал->получил деньги->быстрее сделал..." не сильно задумываются о качестве своих работ. Это нормальное явление, обусловленное правилами игры в этом бизнесе.
3. Armando Armando (Armando) 05.06.10 15:42
У меня консоль отчетов валится при попытке открыть файл.
Платформа 8.1.15.14, ЗУП 2.5.24.4
Прикрепленные файлы:
4. Armando Armando (Armando) 05.06.10 15:44
5. Armando Armando (Armando) 05.06.10 23:14
Похвально, что автор так старался вылизать запрос и даже объяснил что, зачем и как делается. Но лично мне режет глаза вот что:

ВЫБОР
	КОГДА &ПринятыеУволенные = "Принятые"
		ТОГДА (НЕ РаботникиОрганизаций.Сотрудник В
					(ВЫБРАТЬ
						РаботникиОрганизаций.Сотрудник КАК Сотрудник
					ИЗ
						РегистрСведений.РаботникиОрганизаций КАК РаботникиОрганизаций
					ГДЕ
						РаботникиОрганизаций.Период МЕЖДУ &ПарамНачПериода И &ПарамКонПериода
						И РаботникиОрганизаций.ПричинаИзмененияСостояния = ЗНАЧЕНИЕ(Перечисление.ПричиныИзмененияСостояния.Увольнение)
						И РаботникиОрганизаций.Организация = &ПарамОрганизация
						И РаботникиОрганизаций.Сотрудник.ВидДоговора = ЗНАЧЕНИЕ(Перечисление.ВидыДоговоровСФизЛицами.ТрудовойДоговор)
						И РаботникиОрганизаций.Сотрудник.ВидЗанятости = ЗНАЧЕНИЕ(Перечисление.ВидыЗанятостиВОрганизации.ОсновноеМестоРаботы)))
	КОГДА &ПринятыеУволенные = "Уволенные"
		ТОГДА ИСТИНА
КОНЕЦ
...Показать Скрыть

Совершенно не нужный подзапрос в условии.
------------------------------------------------------------------------
ВЫБОР
	КОГДА КонтактнаяИнформация_ЮрАдресФизЛица.Поле2 ПОДОБНО "%Адыгея%"
		ТОГДА "001"
	КОГДА КонтактнаяИнформация_ЮрАдресФизЛица.Поле2 ПОДОБНО "%Башкортостан%"
		ТОГДА "002"
	КОГДА КонтактнаяИнформация_ЮрАдресФизЛица.Поле2 ПОДОБНО "%Бурятия%"
		ТОГДА "003"
	КОГДА КонтактнаяИнформация_ЮрАдресФизЛица.Поле2 ПОДОБНО "%Алтай%"
		ТОГДА "004"
	КОГДА КонтактнаяИнформация_ЮрАдресФизЛица.Поле2 ПОДОБНО "%Дагестан%"
		ТОГДА "005"
	КОГДА КонтактнаяИнформация_ЮрАдресФизЛица.Поле2 ПОДОБНО "%Ингушетия%"
		ТОГДА "006"
	КОГДА КонтактнаяИнформация_ЮрАдресФизЛица.Поле2 ПОДОБНО "%Кабардино%Балкарская%"
		ТОГДА "007"
	ИНАЧЕ "-----"
КОНЕЦ
...Показать Скрыть

Как насчет соединения контактной информации с адресным классификатором с отбором ТипАдресногоЭлемента = 1?
------------------------------------------------------------------------
	УвольнениеИзОрганизацийРаботникиОрганизации.Ссылка.Дата КАК ДатаПриказаОбУвольнении,
	УвольнениеИзОрганизацийРаботникиОрганизации.Ссылка.Номер КАК НомерПриказаОбУвольнении,
	УвольнениеИзОрганизацийРаботникиОрганизации.ДатаУвольнения

Сам же говорил про получение полей через точку ;)
		ЛЕВОЕ СОЕДИНЕНИЕ Документ.УвольнениеИзОрганизаций.РаботникиОрганизации КАК УвольнениеИзОрганизацийРаботникиОрганизации
		ПО (ВЫБОР
				КОГДА &ПринятыеУволенные = "Уволенные"
					ТОГДА ВТ_ПринятыеУволенныеЗаПериод.Сотрудник = УвольнениеИзОрганизацийРаботникиОрганизации.Сотрудник
				КОГДА &ПринятыеУволенные = "Принятые"
					ТОГДА ЛОЖЬ
			КОНЕЦ)
...Показать Скрыть

Использование таблицы 'Документ.УвольнениеИзОрганизаций.РаботникиОрганизации' для получение получения даты увольнения - грубейшая ошибка. Введи на уже уволенного сотрудника еще один приказ об увольнении и посмотри отчет по уволенным.
6. МagIvan (RailMen) 06.06.10 23:06
(5) Очень рад, что Armando так по делу прокоментировал запрос. Хорошо, что на этом сайте есть такие спецы. А то напишешь что-нибудь и нужен кто-то, кто бы смог незамыленным взглядов посмотреть.
1) "Совершенно не нужный подзапрос в условии" - глаза и мне режет, но это сделал специально, т.к. проверку нужно выполнять только для "Принятые". Если просто добавить таблицу, то данные из нее будут браться всегда, а нужно только для "Принятые". Предложи лучше ;)
2) "Как насчет соединения контактной информации с адресным классификатором с отбором ТипАдресногоЭлемента = 1" - по какому полю соединять? и самое главное: если пользователи регион могут вводить руками, тогда что?
3) "Сам же говорил про получение полей через точку" - конечно переделать надо, спешил как обычно.
4) " Использование таблицы 'Документ.УвольнениеИзОрганизаций.РаботникиОрганизации' для получение получения даты увольнения - грубейшая ошибка " - не понимаю как руки смогли такое написать. Наверно был конец пятницы, ну сам понимаешь. Спасибо что пристыдил.

Переделаю и выложу новый вариант.
7. Armando Armando (Armando) 07.06.10 10:07
(6)
1. Глядя на запрос начинает казаться, что все будет работать и без этого условия. Кстати, в регистре РаботникиОрганизаций есть ресурсы ПериодЗавершения и ПричинаИзмененияСостоянияЗавершения. Они бы тоже пригодились.
2.
ВЫБРАТЬ
	КонтактнаяИнформация.Объект,
	КонтактнаяИнформация.Поле2,
	АдресныйКлассификатор.КодРегионаВКоде
ИЗ
	РегистрСведений.КонтактнаяИнформация КАК КонтактнаяИнформация
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АдресныйКлассификатор КАК АдресныйКлассификатор
		ПО (КонтактнаяИнформация.Поле2 ПОДОБНО АдресныйКлассификатор.Наименование + "%")
ГДЕ
	КонтактнаяИнформация.Тип = ЗНАЧЕНИЕ(Перечисление.ТипыКонтактнойИнформации.Адрес)
	И АдресныйКлассификатор.ТипАдресногоЭлемента = 1
...Показать Скрыть

если пользователи регион могут вводить руками, тогда что?

тогда будут перезаполнять по классификатору))
3. На самом деле получение через точку не так критично. В профайлере эти запросы одинаковые. Критично только для полей составного типа.
8. МagIvan (RailMen) 07.06.10 10:24
(7)

1. ДА, плюс тебе.
2. (КонтактнаяИнформация.Поле2 ПОДОБНО АдресныйКлассификатор.Наименование + "%") - сам бы, наверно, не догадался. Но опять же меня терзают смутные сомнения насчет того, как были введены данные - мой вариант будет работать и без кладра. Но твой академически правильный.
3. Получение через точку критично в зависимости от кол-ва точек и от типа базы. Наверно, этому есть более книжное "объяснение". На практике 2 точки - это нормально. НО! Такой пример: Подразделение.Родитель.Родитель.Родитель.Родитель - в клиент-серверном варианте вылетает с ошибкой, при том, что 4 родитель у подразделения есть.
9. МagIvan (RailMen) 07.06.10 11:16
(5)
<Использование таблицы 'Документ.УвольнениеИзОрганизаций.РаботникиОрганизации' для получение получения даты увольнения - грубейшая ошибка. Введи на уже уволенного сотрудника еще один приказ об увольнении и посмотри отчет по уволенным.> - такие поля как "Статья ТК РФ" и "основание увольнения" можно взять только из ТЧ документа. Поэтому ТЧ оставил, но конечно, добавил новую связь:
ВЫБОР
КОГДА &ПринятыеУволенные = "Уволенные"
ТОГДА ВТ_ПринятыеУволенныеЗаПериод.Сотрудник = УвольнениеИзОрганизацийРаботникиОрганизации.Сотрудник
И ВТ_ПринятыеУволенныеЗаПериод.Регистратор = УвольнениеИзОрганизацийРаботникиОрганизации.Ссылка
КОГДА &ПринятыеУволенные = "Принятые"
ТОГДА ЛОЖЬ
КОНЕЦ

Вместо:

ВЫБОР
КОГДА &ПринятыеУволенные = "Уволенные"
ТОГДА ВТ_ПринятыеУволенныеЗаПериод.Сотрудник = УвольнениеИзОрганизацийРаботникиОрганизации.Сотрудник
КОГДА &ПринятыеУволенные = "Принятые"
ТОГДА ЛОЖЬ
КОНЕЦ

10. Никита Уколов (zzerro) 09.06.10 10:08
О как... Спасибо, как раз меня такое попросили сделать: список для получения полисов ОМС. Плюс однозначно!
11. МagIvan (RailMen) 09.06.10 13:47
(10) акуратнее с условиями в первом пакете. тебе надо натянуть их под свои условия задачи.
12. Илья И (Илюх@) 26.07.10 12:41
(9) При строчном ТД дата увольнения может заполняться и через документ Прием на работу в организацию, возможно автор это не учел
Использование таблицы 'Документ.УвольнениеИзОрганизаций.РаботникиОрганизации' для получение получения даты увольнения - грубейшая ошибка
13. МagIvan (RailMen) 30.08.10 21:51
Эх, давненько меня тут не было. Дела.
(12) При строчном ТД дата увольнения может заполняться и через документ Прием на работу в организацию - это действительно так. Но не в моей конторе. Считаю, что любое творение на ИСе надо адаптировать под свои нужды. Я тут последнее время беру только идеи, ибо их исполнение меня никак не устраивает.

А что касается таблицы 'Документ.УвольнениеИзОрганизаций.РаботникиОрганизации' - то это не ошибка. Дело в том, что поля "причина увольнения" и "статья увольнения", которые так нужны кадровикам в отчете, не храняться в регистре. Так что все тут оптимально.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа