IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_OwePaidLodgMonth'))) THEN
Drop View v_OwePaidLodgMonth
END IF

GO

create view v_OwePaidLodgMonth as
select
 Guest as GNo,
 Month=ImeMesec(bvrSpravkaM),
 Year=bvrSpravkaY,
 G.Name,
 Uni=U.FullName,
 Kurs=K.Name,
 Spec=S.Name,
 G.FN,
 G.Address,
 City=(select SettleMentName from Settlements where N = G.SettlementN),
 G.Sex,
 PriceKind=PK.Description,
 Usluga=GetNoshtuvkaNameForMonthBook(B.RegNum,1), 
 Cena=B.Price*C.ExchangeRate,
 Kol=GetQtyLodgForMonth(B.RegNum,B.NumLodgings),
 SumOwe=Round(Kol*Cena,2),
 KolPaid=GetQtyLodgForMonth(B.RegNum,B.PayedLodgings),
 SumPaid=Round(KolPaid*Cena,2),
 Razlika=SumOwe-SumPaid,
 RealDatePayed=GetRealDatePayedLodgForMonth(B.RegNum),
 BrStudenti=1,
 RName=R.RoomName,
 CountryName=Countries.Description
from 
  Book B, PriceKinds PK, Guests G, Kursove K, Specialities S, Universities U, Currencies C, Rooms R, Countries
where 
  B.PriceKind=PK.PriceKind 
  AND G.GuestNum=B.Guest 
  AND PK.NachinPlat=0 
  AND G.Kurs=K.N 
  AND G.SpecNo=S.N 
  AND G.UniN=U.N 
  AND B.KodCurrency=C.KodCurrency 
  AND R.RoomNum=B.Room
  and G.NativeCountry=Countries.CountryKod
  and B.DateRegDt < dateadd(month,1,date('1.'||bvrSpravkaM||'.'||bvrSpravkaY))
  and dateadd(day,B.NumLodgings,B.DateRegDt) > date('1.'||bvrSpravkaM||'.'||bvrSpravkaY)

go

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

GO

create procedure
DBA.FillOweRentLodg()
begin
  set bvrSpravkaM=bvrSpravkaM1;
  set bvrSpravkaY=bvrSpravkaY1;
  delete from #OwePaidRentLodg;

  while ((bvrSpravkaY < bvrSpravkaY2) or ((bvrSpravkaY=bvrSpravkaY2) and (bvrSpravkaM<=bvrSpravkaM2))) loop
    insert into #OwePaidRentLodg (select 
      GNo, "Month","Year","Name",Uni,Kurs,Spec,FN,Address,City,Sex,PriceKind,Usluga,Cena,Kol,SumOwe,KolPaid,SumPaid,
      Razlika,RealDatePayed,BrStudenti,RName,CountryName
    from
      v_OwePaidLodgMonth);
	if (bvrSpravkaM >= 12) then 
	  set bvrSpravkaM = 1;
	  set bvrSpravkaY = bvrSpravkaY + 1;
	else 
	  set bvrSpravkaM = bvrSpravkaM + 1;
	end if;
  end loop;
end
go

if (not exists(select N from Relations where TableName='#OwePaidRentLodg' and ColName='CountryName')) then
  INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
  VALUES('#OwePaidRentLodg','CountryName','Дължими и платени нощувки','Държава','Lodgings information','CountryName',2,GetFreeNInRelations('Lodgings information', Null, Null, 2),'CountryName','','',0,0,'','','','',120,'',0,0,1,today(*))
end if

Go