CREATE FUNCTION dbo.DepositumDiff (@ref int, @guestnumber int, @AReturnReceived SMALLINT) RETURNS money AS
BEGIN
/*
  Changes:
    KJ 20140501: Ext: BW get advance deposit. 
    KJ 20110802: Only select active followup in wanted deposit. Case #CR026312
    
*/

  declare @price money
  declare @debit int
  declare @recieved money   
  set @recieved = 0.00
  declare @wanted money     
  set @wanted = 0.00
  declare @diff money       
  set @diff = 0.00
  
  SET @AReturnReceived = ISNULL(@AReturnReceived, 0)
    
  declare dep_cur cursor for
    SELECT debit, (Transactions.Pieces * Transactions.PriceOverWrite) as price
    FROM FollowUp WITH (nolock) 
    INNER JOIN Transactions WITH (nolock) ON FollowUp.FollowUpNumber = Transactions.DepositfollowupNumber 
      AND ShowOnBillFlag = 1
    INNER JOIN ReservationFollowUpXTab WITH (nolock) ON FollowUp.FollowUpNumber = ReservationFollowUpXTab.FollowUpNumber
      AND ReservationFollowUpXTab.referencenumber = @ref 
      AND ReservationFollowUpXTab.guestnumber = @guestnumber
    INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIDNumber = Transactions.UserIDNumber
      AND (
        (NOT @AReturnReceived IN (1,2)) OR
        (@AReturnReceived = 1 AND CAST(Personel.UserTypeNumber AS VARCHAR) IN ('8')) OR
        (@AReturnReceived = 2 AND NOT CAST(Personel.UserTypeNumber AS VARCHAR) IN ('8'))
      )
    WHERE Followup.Type = 15 
  open dep_cur

  fetch next from dep_cur into @debit, @price

  while @@fetch_status = 0
  begin
    if @debit = 1 set @recieved = @recieved + @price
    fetch next from dep_cur into @debit, @price
  end
  close dep_cur
  deallocate dep_cur

  IF @AReturnReceived = 0
  BEGIN
    declare fol_cur cursor for
      select depositamount 
      from reservationfollowupxtab WITH (nolock) 
      inner join followup WITH (nolock) on followup.followupnumber = reservationfollowupxtab.followupnumber 
        and type = 15
        AND State <= 3
      where referencenumber = @ref and guestnumber = @guestnumber 
    open fol_cur

    fetch next from fol_cur into @price

    while @@fetch_status = 0
    begin  
      set @wanted = @wanted + @price
      fetch next from fol_cur into @price
    end
    close fol_cur
    deallocate fol_cur

    set @diff = @wanted - @recieved
  END
  ELSE
    SET @Diff = @Recieved
  RETURN(@diff)
END