DROP PROCEDURE IF EXISTS public.sp_generateschetoutnumber;
DROP FUNCTION IF EXISTS public.sp_generateschetoutnumber;

CREATE PROCEDURE public.sp_generateschetoutnumber
(
  IN "SchetID" uuid,
  OUT "Next" varchar(64)
)
as $$
declare 
	_nextnumber int;
	_numbername varchar(64);
	_year varchar(4);
	_okato_oms varchar(5);
begin
	select s.year::varchar(4), s.okato_oms into _year, _okato_oms from ies.t_schet s where s.schetid = "SchetID";
	_numbername := 'SchetNumber-' || _year || '-' || _okato_oms;
	select nextnumber into _nextnumber from ies.t_gen_numbers where entityid = 539 and numbername like _numbername; 

	if _nextnumber is null then
		_nextnumber := 1;
		insert into ies.t_gen_numbers (gennumbersID, entityid, numbername, nextnumber) 
		values (gen_random_uuid(), 539, _numbername, _nextnumber);
	else
		_nextnumber := (_nextnumber + 1);
	
		update ies.t_gen_numbers 
		set nextnumber = _nextnumber 
		where entityid = 539 and numbername like _numbername;
	end if;
	
	"Next" := _nextnumber::varchar(64); 
end;
$$ language plpgsql;