UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWDUPLICATECHECKNUMBERS

CREATE function dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWDUPLICATECHECKNUMBERS(@DISBURSEMENTPROCESSID uniqueidentifier, @BANKACCOUNTID uniqueidentifier, @RANGESGRID xml, @RENUMBER bit, @SELECTEDITEM int)
returns bit
with execute as caller
as begin
    declare @ReturnValue bit;
    set @ReturnValue=0;
  declare @TOTALDISBURSEMENTS int

  declare @TempTable table
      (
          [START] int
      ,[END] int
      ,[COUNT] int
      ,[BANKACCOUNTID] uniqueidentifier
      );

    declare @TempTableValidRenumber table
    (
      [START] int
      ,[END] int
      ,[COUNT] int
      ,[BANKACCOUNTID] uniqueidentifier
    );    


  select    
    @TOTALDISBURSEMENTS=COUNT(*)
  from
    dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
    inner join dbo.FINANCIALTRANSACTION as FTD on DPD.ID = FTD.ID
  where
    DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
  group by
    DPD.DISBURSEMENTPROCESSID;

  if @RENUMBER=0
      insert into @TempTable
          select 
              [RANGESTART]
        ,[RANGEEND]
        ,case when [RANGEEND]<>0
           then
             [RANGEEND]-[RANGESTART]+1 
           else
             0
           end
        ,@BANKACCOUNTID
          from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID);
  else
    begin

      -- When we are renumbering essentially any of the renumbering rows check numbers are valid

      -- so declaring a table to hold valid numbers.

      insert into @TempTableValidRenumber
        select
          [RANGESTART]
          ,[RANGEEND]
          ,[RANGECOUNT]
          ,@BANKACCOUNTID
        from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID)
        where USERACTION = 3;

      if @SELECTEDITEM>0
        begin
        insert into @TempTable
          select
            [STARTNUMBER]
            ,[STARTNUMBER] + [RANGECOUNT] - 1
            ,[STARTNUMBER]
            ,@BANKACCOUNTID
              from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID)
          where [RANGESTART] = @SELECTEDITEM and USERACTION = 3;
        end
      else
        insert into @TempTable
          select
            [STARTNUMBER]
            ,[STARTNUMBER] + [RANGECOUNT] - 1
            ,[STARTNUMBER]
            ,@BANKACCOUNTID
              from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID)
                    where USERACTION = 3;
    end


  update @TempTable
  set [COUNT] = @TOTALDISBURSEMENTS - (select SUM([COUNT]) from @TempTable)
  where [COUNT] = 0;    

  if exists(
      select * from dbo.BANKACCOUNTTRANSACTION BAT
      inner join @TempTable as T1 on
        (T1.[START] <= BAT.TRANSACTIONNUMBER and case when T1.[END]<>0 then T1.[END] else T1.[START]+T1.[COUNT]-1 end >= BAT.TRANSACTIONNUMBER)
    where
      BAT.TRANSACTIONNUMBER not in 
      (
            select BAT.TRANSACTIONNUMBER from dbo.BANKACCOUNTTRANSACTION BAT
            inner join @TempTableValidRenumber T on BAT.TRANSACTIONNUMBER >= T.START and BAT.TRANSACTIONNUMBER <= T.[END]
      )
      and BAT.BANKACCOUNTID = @BANKACCOUNTID
      and BAT.DELETED = 0          
  )
  begin
     set @ReturnValue = 1;
  end

    return @ReturnValue;
end