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 varchar(50)
begin
  declare @Smetki varchar(50);
  declare @G integer;
  declare @C integer;
  select MAX(GuestNo),MAX(ContractNo) into @G,@C from ResMoney where InvoiceNo = @SmetkaNo;
  set @Smetki='';
  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 and
		InvoiceNo > @SmetkaNo
		do
          set @Smetki = @Smetki || @Inv || ','
    end for;
  end if;
  if((@G = 0) and(@C <> 0)) then
	-- proverka za VTD fakturi
	for F2 as Curs2 scroll cursor for
	  select GlobalInvoice as @GlobalInvoice 
		from DepositForContracts DFC join VaucherInvoices VI on DFC.GlobalInvoice = VI.VaucherInvoiceNo 
		where SubInvoice = @SmetkaNo and DFC.ContractNo = @C and VI.ContractNo=@C and GlobalInvoice <> 0 and CancelInvoiceNo = 0 and GlobalInvoice <> @SmetkaNo
	do
	  set @Smetki=@Smetki || @GlobalInvoice || ','
	end for;
	-- ako ne e turoperator - tursi v Invoices
	for F3 as Curs3 scroll cursor for
	  select GlobalInvoice as @GlobalInvoice 
		from DepositForContracts DFC join Invoices VI on DFC.GlobalInvoice = VI.InvoiceNo 
		where SubInvoice = @SmetkaNo and DFC.ContractNo = @C and VI.ContractNo=@C and GlobalInvoice <> 0 and CancelInvoiceNo = 0 and GlobalInvoice <> @SmetkaNo
	do
	  set @Smetki=@Smetki || @GlobalInvoice || ','
	end for;
  end if;
  return(@Smetki)
end