create procedure Rcalc_Set_Group_House_Pipe_Code
as
declare variable House_Pipe_Code         Tcode;
declare variable Group_House_Pipe_Code   Tcode;
declare variable R_Group_House_Pipe_Code Tcode;
begin

  Group_House_Pipe_Code = 0;
  for select House_Pipe_Code
        from Rcalc_Temp_Pipe_List_20
        order by House_Pipe_Code
      into :House_Pipe_Code
  do begin -- По домовым вводам
    -- Проверим а не цепляются ли вводы на лицевые для домового ввода House_Pipe_Code к другим домовым вводам
    R_Group_House_Pipe_Code = null;
    select first 1 R.Group_House_Pipe_Code
      from Rcalc_Temp_Pipe_List_10 L
        join Rcalc_Temp_Pipe_List_10 R on R.Account_Pipe_Code = L.Account_Pipe_Code and
              R.Group_House_Pipe_Code is not null
      where L.House_Pipe_Code = :House_Pipe_Code
        and R.House_Pipe_Code < :House_Pipe_Code
    into :R_Group_House_Pipe_Code;

    if (R_Group_House_Pipe_Code is not null) then
    begin -- Цепляются
      --> Установим существующую группу
      update Rcalc_Temp_Pipe_List_20
        set Group_House_Pipe_Code = :R_Group_House_Pipe_Code
        where House_Pipe_Code = :House_Pipe_Code;

      update Rcalc_Temp_Pipe_List_10
        set Group_House_Pipe_Code = :R_Group_House_Pipe_Code
        where House_Pipe_Code = :House_Pipe_Code;
    end -- Цепляются
    else begin -- Не цепляются
      --> Установим новую группу
      Group_House_Pipe_Code = Group_House_Pipe_Code + 1;
      update Rcalc_Temp_Pipe_List_20
        set Group_House_Pipe_Code = :Group_House_Pipe_Code
        where House_Pipe_Code = :House_Pipe_Code;

      update Rcalc_Temp_Pipe_List_10
        set Group_House_Pipe_Code = :Group_House_Pipe_Code
        where House_Pipe_Code = :House_Pipe_Code;
    end -- Не цепляются
  end -- По домовым вводам
end

create or alter procedure Rcalc_Group_House_Pipe_Utl (
  A_House_Pipe_Code  Tcode,
  A_Group_House_Pipe Tcode)
as
declare variable House_Pipe_Code Tcode;
begin

  --> Установим  группу
  update Rcalc_Temp_Pipe_List_20
    set Group_House_Pipe = :A_Group_House_Pipe
    where House_Pipe_Code = :A_House_Pipe_Code;

  update Rcalc_Temp_Pipe_List_10
    set Group_House_Pipe = :A_Group_House_Pipe
    where House_Pipe_Code = :A_House_Pipe_Code;

  -- Проверим а не цепляются ли вводы на лицевые для домового ввода A_House_Pipe_Code к другим домовым вводам
  for select distinct R.House_Pipe_Code
        from Rcalc_Temp_Pipe_List_10 L
          join Rcalc_Temp_Pipe_List_10 R on R.Account_Pipe_Code = L.Account_Pipe_Code and
                R.Group_House_Pipe is not null
        where L.House_Pipe_Code = :A_House_Pipe_Code
      into :House_Pipe_Code
  do begin
    -- Установим существующую группу
    execute procedure Rcalc_Group_House_Pipe_Utl(House_Pipe_Code, :A_Group_House_Pipe);
  end
end
-------------
create or alter procedure Rcalc_Group_House_Pipe
as
declare variable House_Pipe_Code   Tcode;
declare variable Group_House_Pipe  Tcode;
declare variable Account_Pipe_Code Tcode;
begin

  Group_House_Pipe = 0;
  for select House_Pipe_Code
        from Rcalc_Temp_Pipe_List_20
        order by House_Pipe_Code
      into :House_Pipe_Code
  do begin -- По домовым вводам
    if (exists(select *
                 from Rcalc_Temp_Pipe_List_20 R20
                 where R20.House_Pipe_Code = :House_Pipe_Code
                   and R20.Group_House_Pipe is null)) then
    begin -- Для этого ввода группу еще не установили
      --> Установим новую группу
      Group_House_Pipe = Group_House_Pipe + 1;
      execute procedure Rcalc_Group_House_Pipe_Utl(House_Pipe_Code, :Group_House_Pipe);
    end -- Для этого ввода группу еще не установили
  end -- По домовым вводам

  -- Проверка правильности работы
  select first 1 R10.Account_Pipe_Code --,count(distinct r10.group_house_pipe)
    from Rcalc_Temp_Pipe_List_10 R10
    group by R10.Account_Pipe_Code
    having count(distinct R10.Group_House_Pipe) > 1
  into :Account_Pipe_Code;

  if (Account_Pipe_Code is not null) then
    exception Esys_Error 'Более одной группы для ввода лицевого =' || cast(Account_Pipe_Code as varchar(16));

  select first 1 R10.Account_Pipe_Code
    from Rcalc_Temp_Pipe_List_10 R10
    where R10.Group_House_Pipe is null
  into :Account_Pipe_Code;
  if (Account_Pipe_Code is not null) then
    exception Esys_Error 'Отсутствует группа для ввода лицевого =' || cast(Account_Pipe_Code as varchar(16));
end

CREATE GLOBAL TEMPORARY TABLE RCALC_TEMP_PIPE_LIST_20 (
    CODE                  TCODENN /* TCODENN = INTEGER NOT NULL */,
    HOUSE_PIPE_CODE       TCODE /* TCODE = INTEGER */,
-- Здесь еще несколько полей к делу не относящихся
    GROUP_HOUSE_PIPE      TCODE /* TCODE = INTEGER */
) ON COMMIT PRESERVE ROWS;

ALTER TABLE RCALC_TEMP_PIPE_LIST_20 ADD CONSTRAINT PK_RCALC_TEMP_PIPE_LIST_20 PRIMARY KEY (CODE);
CREATE UNIQUE INDEX RCALC_TEMP_PIPE_LIST_20_IDX1 ON RCALC_TEMP_PIPE_LIST_20 (HOUSE_PIPE_CODE);
--------------------------------------------------------

CREATE GLOBAL TEMPORARY TABLE RCALC_TEMP_PIPE_LIST_10 (
    CODE                  TCODENN  /* TCODENN = INTEGER NOT NULL */,
    HOUSE_PIPE_CODE       TCODE /* TCODE = INTEGER */,
-- Здесь еще несколько полей к делу не относящихся
    ACCOUNT_PIPE_CODE     TCODE /* TCODE = INTEGER */,
    GROUP_HOUSE_PIPE      TCODE /* TCODE = INTEGER */
) ON COMMIT PRESERVE ROWS;


ALTER TABLE RCALC_TEMP_PIPE_LIST_10 ADD CONSTRAINT PK_RCALC_TEMP_PIPE_LIST_10 PRIMARY KEY (CODE);
CREATE INDEX RCALC_TEMP_PIPE_LIST_10_IDX1 ON RCALC_TEMP_PIPE_LIST_10 (HOUSE_PIPE_CODE);
CREATE INDEX RCALC_TEMP_PIPE_LIST_10_IDX2 ON RCALC_TEMP_PIPE_LIST_10 (ACCOUNT_PIPE_CODE);