USP_DATALIST_DISBURSEMENTPROCESS_FINALIZE

CREATE procedure dbo.USP_DATALIST_DISBURSEMENTPROCESS_FINALIZE(@DISBURSEMENTPROCESSID uniqueidentifier
                                                               ,@RANGESGRID xml
                                                               ,@SELECTEDITEM int)
as
    set nocount on;

  begin try

    declare @TOTALDISBURSEMENTS int;
    declare @BANKACCOUNTID uniqueidentifier;

      select
        @BANKACCOUNTID = DP.BANKACCOUNTID
    from
        dbo.DISBURSEMENTPROCESS as DP
    where
        DP.ID = @DISBURSEMENTPROCESSID;

    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        

    declare @TempTbl table
    (
      [ID] uniqueidentifier
      ,[DISBURSEMENTPROCESSID] uniqueidentifier
      ,[RANGESTART] int
      ,[RANGEEND] int
      ,[RANGECOUNT] int
      ,[USERACTION] tinyint
      ,[STARTNUMBER] nvarchar(max)
    )
    insert into @TempTbl 
      select
        isnull([ID], newid()) ID
        ,@DISBURSEMENTPROCESSID
        ,[RANGESTART]
        ,[RANGEEND]
        ,case when [RANGEEND]<>0
          THEN
            [RANGEEND]-[RANGESTART]+1 
          ELSE
            0
          END
         as [RANGECOUNT]
        ,[USERACTION]
        ,[STARTNUMBER]
      from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID)
      where [RANGESTART] = @SELECTEDITEM

      update @TempTbl
      set RANGECOUNT = @TOTALDISBURSEMENTS - (select SUM(RANGECOUNT) from @TempTbl)
      where RANGECOUNT = 0


    -- Do not allow zero for any check numbers as zero is not a valid check number.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWZERO(@RANGESGRID) = 1
      raiserror('Zero is not a valid check number.', 16, 1)   --ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_INVALIDCHECKNUMBERGRID



    -- Do not allow the check number to exceed 9 characters.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWCHECKNUMBERGREATERTHANNINE(@RANGESGRID)=1
      raiserror('Form number cannot exceed 9 characters.',16,1)   --ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_9DIGITSONLYGRID



    -- Validate that the start numbers are less than the end numbers.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWSTARTGREATERTHANEND(@DISBURSEMENTPROCESSID, @RANGESGRID)=1
      raiserror('Starting number cannot be greater than ending number.', 16, 1)   --ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_RANGESTARTGREATERTHANRANGEEND



    -- Validate that the ranges do not overlap.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOVERLAPPINGRANGES(@DISBURSEMENTPROCESSID, @RANGESGRID)=1
      raiserror('Form number ranges cannot overlap.', 16, 1)    --ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_RANGESCANNOTOVERLAP



    --Check to see if we are trying to renumber any checks.

    if (select count(*) from @TempTbl where [USERACTION] = 3)>0
      begin
        -- Validate that the new numbers are not in the bank account transaction table.

        if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWDUPLICATECHECKNUMBERS(@DISBURSEMENTPROCESSID, @BANKACCOUNTID, @RANGESGRID, 1, @SELECTEDITEM)=1
          raiserror('One or more form numbers has been used in a previous disbursement process.', 16, 1)   --ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_CHECKNUMBEREXISTSGRID

      end


    -- Validate that the range falls within the available range.

    if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOUTSIDERANGE(@DISBURSEMENTPROCESSID, @RANGESGRID)=1
      raiserror('The form number range you have defined for unsuccessful disbursements contains numbers that are not within the number range for this disbursement process.', 16, 1)    --ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_INCORRECTRANGE



    declare @InvalidIDs table
    (
      [ROWID] int
      ,[DISBURSEMENTPROCESSDISBURSEMENTID] uniqueidentifier
      ,[USERACTION] tinyint
      ,[STARTNUMBER] nvarchar(max)
    )
    insert into @InvalidIDs
      select 
        case 
          when TEMP.USERACTION=3 then
            ROW_NUMBER() over (order by DPD.DATEADDED) + TEMP.STARTNUMBER - 1 
          else
            ROW_NUMBER() over (order by DPD.DATEADDED) + TEMP.RANGESTART - 1 
        end as ROWID
        ,DPD.ID
        ,TEMP.USERACTION
        ,TEMP.STARTNUMBER
      from dbo.BANKACCOUNTTRANSACTION_EXT as BATEXT 
        inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD on BATEXT.ID = DPD.ID
        inner join dbo.BANKACCOUNTTRANSACTION as BAT on BATEXT.ID = BAT.ID
        inner join @TempTbl as TEMP on TEMP.DISBURSEMENTPROCESSID = BATEXT.DISBURSEMENTPROCESSID
                and BAT.TRANSACTIONNUMBER >= TEMP.RANGESTART and BAT.TRANSACTIONNUMBER <= TEMP.RANGEEND
      where DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
        order by TEMP.RANGESTART

    select
      C.KEYNAME as PAYEE
      ,BAT.TRANSACTIONNUMBER as CURRENTNUMBER
      ,BAT.AMOUNT as AMOUNT
      ,INV.USERACTION as USERACTION
      ,INV.ROWID as STARTNUMBER
    from dbo.BANKACCOUNTTRANSACTION as BAT 
      inner join dbo.FINANCIALTRANSACTION as FT on BAT.ID = FT.ID and FT.TYPECODE = 255
      inner join dbo.CONSTITUENT as C on FT.CONSTITUENTID = C.ID
      inner join @InvalidIDs as INV on INV.DISBURSEMENTPROCESSDISBURSEMENTID = FT.ID
    where 
      BAT.ID in (select DISBURSEMENTPROCESSDISBURSEMENTID from @InvalidIDs)    

  end try

  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch