do $$
declare
  _r record;
	_i int4 default 0;
	_j int4 default 0;	
	p_personprivilege_id int8;
begin
  for _r in (
		with dat as (
			select t.lgota_code_file::varchar
			from tmp.tmp_PROMEDWEB100647_data as t
			where t.lgota_code_file not in ('012','999')
			group by t.lgota_code_file
			order by t.lgota_code_file asc
			), dat1 as (
			select pt.privilegetype_id,	pt.privilegetype_code, row_number() over(partition by pt.privilegetype_code order by privilegetype_id) as rn
			from dat as d
			inner join dbo.v_privilegetype as pt on pt.privilegetype_code::varchar=d.lgota_code_file
			where pt.region_id=7
			), dat2 as (
			select * from dat1 
			where rn=1
			), dat3 as (
			select distinct pl.personprivilege_id,	t.person_id,	d2.privilegetype_id,	pc.lpu_id,	to_char(pl.personprivilege_begdate,'dd.mm.yyyy') as  personprivilege_begdate,	to_char(pl.personprivilege_enddate,'dd.mm.yyyy') as  personprivilege_enddate,	d.diag_id, t.lgota_code_file,	t.mkb_code,	t.lgot_beg, t.lgota_end, local_id,
			case  when pl.personprivilege_id is not null then 1 else 2 end as isUpdate , t.isLoad		
					from tmp.tmp_PROMEDWEB100647_data as t
			left join dat2 as d2 on d2.privilegetype_code::varchar=t.lgota_code_file::varchar
			left join v_personprivilege as pl on t.person_id=pl.person_id
			left join dbo.diag as d on d.diag_code::varchar=t.mkb_code::varchar
			left join v_personcard as pc on pc.person_id=t.person_id
			)
			select * 
			from dat3
			where 
				lgota_code_file not in ('012','999')
				-- and isUpdate=2 -- !!!
				-- and person_id in (93,157,180)  -- !!!
				and person_id is not null
			 and isLoad=1
			 and privilegetype_id is not null
			order by person_id,	privilegetype_id
) loop
    _i=_i+1;
		_j=_j+1;
	  if ((_r.personprivilege_begdate<>_r.lgot_beg) or (_r.personprivilege_enddate<>_r.lgota_end)) and _r.isUpdate=1 then
			update dbo.personprivilege 
			set 
				privilegetype_id = _r.privilegetype_id, 
				personprivilege_begdate = _r.lgot_beg::date, 
				personprivilege_enddate = _r.lgota_end::date, 
				diag_id = _r.diag_id,
				pmuser_updid = 1,
				personprivilege_upddt = now()::timestamp 
			where personprivilege_id = _r.personprivilege_id;
			
			update tmp.tmp_PROMEDWEB100647_data set personprivilege_id = _r.privilegetype_id where local_id = _r.local_id;
			
			--raise notice 'i=%, personprivilege_id=%, local_id=%',_i, _r.personprivilege_id, _r.local_id;			
						
		end if;
	  if (_r.isUpdate=2) then
		
		insert into dbo.personprivilege (server_id, personprivilege_id, person_id, privilegetype_id, lpu_id, personprivilege_begdate, personprivilege_enddate, pmuser_insid, personprivilege_insdt, diag_id)
		values (
		0,
		nextval('"dbo"."personprivilege_personprivilege_id_seq"'),
		_r.person_id,
		_r.privilegetype_id,
		_r.lpu_id,
		_r.lgot_beg::date,
		_r.lgota_end::date,
		1,
		now()::timestamp,
		_r.diag_id
		)
		
		returning personprivilege.personprivilege_id into p_personprivilege_id;
         
    update tmp.tmp_PROMEDWEB100647_data set isload = 2, personprivilege_id = p_personprivilege_id where local_id = _r.local_id;
		
		--raise notice 'j=%, local_id=%',_j, _r.local_id;						
		   
		end if;
						
	end loop;

end; $$
