IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetSmetkaWithDepozits')))
THEN Drop procedure GetSmetkaWithDepozits
END IF

GO

create function DBA.GetSmetkaWithDepozits(in @SmetkaNo integer)
returns integer
begin
  declare @Smetki integer;
  declare @G integer;
  declare @C integer;
  select MAX(GuestNo),MAX(ContractNo) into @G,@C from ResMoney where InvoiceNo = @SmetkaNo;
  set @Smetki=99999999;
  if((@G <> 0) and(@C = 0)) then
    for F as Curs scroll cursor for
      select InvoiceNo as @Inv from
        ResMoney where
        GuestNo = @G and
        Status = 1 and
        DepositNo < 0 and
        GuestNo <> 0 and
        ContractNo = 0 do
      if((locate(@Smetki,@Inv) = 0) and(@Inv > @SmetkaNo) and(@Smetki > @Inv)) then
        set @Smetki=@Inv
      end if end for
  end if;
  if((@G = 0) and(@C <> 0)) then
    for F2 as Curs2 scroll cursor for
      select GlobalInvoice as @GlobalInvoice from
        DepositForContracts where
        SubInvoice = @SmetkaNo and ContractNo = @C and
        GlobalInvoice <> 0 do
      if((locate(@Smetki,@GlobalInvoice) = 0) and(@GlobalInvoice <> @SmetkaNo)) then
        set @Smetki=@Smetki || @GlobalInvoice || ','
      end if end for
  end if;
  return(@Smetki)
end