create or alter procedure ACCTOTAL_SEL (
    DATEREST type of column ACCTOTAL.DATEREST,
    KIND type of column ACCTOTAL.KIND,
    ACCID type of column ACCTOTAL.ACCID,
    SUBCONTO3 type of column ACCTOTAL.SUBCONTO3,
    SUBCONTO2 type of column ACCTOTAL.SUBCONTO2,
    SUBCONTO1 type of column ACCTOTAL.SUBCONTO1)
returns (
    ENDCT type of column ACCTOTAL.ENDCT,
    ENDDT type of column ACCTOTAL.ENDDT)
as
declare variable CMDTEXT varchar(200);
begin

  cmdtext = 'select first 1 enddt, endct from acctotal ';
  
  cmdtext = cmdtext || 'where (daterest < '||:daterest||') (accid = '||:accid||') and (kind = '||:kind||') ';

  if (:subconto1 is not null) then cmdtext = cmdtext || ' and (subconto1 = '||:subconto1||') ';
  if (:subconto2 is not null) then cmdtext = cmdtext || ' and (subconto2 = '||:subconto2||') ';
  if (:subconto3 is not null) then cmdtext = cmdtext || ' and (subconto3 = '||:subconto3||') ';

  cmdtext = cmdtext || 'order by daterest desc ';

  for execute statement cmdtext
   into :enddt, :endct
  do
   suspend;
end

CREATE OR ALTER trigger acctotal_bi for acctotal
active before insert or update position 0
as
begin
  if (new.id is null) then
  begin
    new.id = gen_id(gen_acctotal_id,1);

    execute procedure acctotal_sel(new.kind,
                                   new.accid,
                                   new.subconto3,
                                   new.subconto2,
                                   new.subconto1) 
                returning_values(new.begindt, new.beginct);
  end
end

  cmdtext = cmdtext || 'where (daterest < '||:daterest||') and (accid = '||:accid||') and (kind = '||:kind||') ';

  if (:subconto1 is not null) then cmdtext = cmdtext || ' and (subconto1 = '||:subconto1||') ';
  if (:subconto2 is not null) then cmdtext = cmdtext || ' and (subconto2 = '||:subconto2||') ';
  if (:subconto3 is not null) then cmdtext = cmdtext || ' and (subconto3 = '||:subconto3||') ';

select first 1 acc.enddt, acc.endct
    from acctotal acc
    where ((acc.daterest < new.daterest) and (acc.accid = new.accid) and (acc.kind = new.kind)
        and (acc.subconto1 = new.subconto1 or (new.subconto1 is null))
        and (acc.subconto2 = new.subconto2 or (new.subconto2 is null))
        and (acc.subconto3 = new.subconto3 or (new.subconto3 is null)))
    order by acc.daterest desc
    into new.enddt, new.endct;

and (acc.subconto1 = new.subconto1 or (new.subconto1 is null))