UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOUTSIDERANGE

CREATE function dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOUTSIDERANGE(@DISBURSEMENTPROCESSID uniqueidentifier, @RANGESGRID xml)
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
      );

  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;

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

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

  if not exists
    (
      select * from dbo.UFN_DISBURSEMENTPROCESS_GETCONSOLIDATED_RANGES(@DISBURSEMENTPROCESSID) as T1 
      inner join @TempTable as T2 on ((T2.[START] >= T1.RANGESTART) and (T2.[END] <= T1.RANGEEND))
    )
    set @ReturnValue = 1;

    return @ReturnValue;
end