USP_DATAFORMTEMPLATE_EDIT_DISBURSEMENTPROCESS_FINALIZE_EDIT

CREATE procedure [dbo].[USP_DATAFORMTEMPLATE_EDIT_DISBURSEMENTPROCESS_FINALIZE_EDIT](
    @ID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier = null
  ,@UNSUCESSFULACTION tinyint
  ,@SUCCESSFULCHECKPRINTINGCODE tinyint
  ,@RANGESGRID xml
)
as

    set nocount on;

  begin try 

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @BANKACCOUNTID uniqueidentifier;
    declare @HASUNSUCCESSFUL bit
    set @HASUNSUCCESSFUL=0
    declare @CHANGESTATUSREPRINTED bit
    set @CHANGESTATUSREPRINTED=0
    declare @CHANGESTATUSCOMMITTED bit
    set @CHANGESTATUSCOMMITTED=0
    declare @PERFORMREPRINTACTION bit
    set @PERFORMREPRINTACTION=0
    declare @PERFORMVOIDACTION bit
    set @PERFORMVOIDACTION=0
    declare @PERFORMREPRINTANDVOIDACTION bit
    set @PERFORMREPRINTANDVOIDACTION=0
    declare @ReprintIDs table([DISBURSEMENTPROCESSDISBURSEMENTID] uniqueidentifier)
    declare @VoidBATIDs udt_genericid
    declare @VoidReprintBATIds udt_genericid
    declare @VoidReprintDPDIDs table([DISBURSEMENTPROCESSDISBURSEMENTID] uniqueidentifier, [NEWID] uniqueidentifier, [POSTDATE] date, [POSTSTATUSCODE] tinyint)
    declare @RenumberIDs table([ROWID] int, [DISBURSEMENTPROCESSDISBURSEMENTID] uniqueidentifier)

    --Make sure the process is in the printing state.
    if not exists( select * from dbo.DISBURSEMENTPROCESS where ID = @ID and (STATUSCODE = 1 or HASUNSUCCESSFUL = 1)) -- 1 = Created
      raiserror ('Cannot commit a process that has a status other than created', 16, 1);


    if @CHANGEAGENTID is null  
          exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output


      declare @CURRENTDATE datetime
      set @CURRENTDATE = getdate()    


      select
          @BANKACCOUNTID = DP.BANKACCOUNTID
      ,@TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
      ,@PDACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID
      from
          dbo.DISBURSEMENTPROCESS as DP
      inner join dbo.BANKACCOUNT as BA on DP.BANKACCOUNTID = BA.ID
      where
          DP.ID = @ID;


    if @SUCCESSFULCHECKPRINTINGCODE=1 -- Marking all unsuccessful
      begin
        set @HASUNSUCCESSFUL = 1  
        if @UNSUCESSFULACTION=0 --Reprint
          insert into @ReprintIDs
            select 
              DPD.ID
            from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
            where DPD.DISBURSEMENTPROCESSID = @ID
        if @UNSUCESSFULACTION=1 --Void
          insert into @VoidBATIDs
            select 
              DPD.ID
            from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
            where DPD.DISBURSEMENTPROCESSID = @ID
        if @UNSUCESSFULACTION=2 --Void and Reprint
          begin
            insert into @VoidReprintBATIDs
              select 
                DPD.ID
              from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD 
              where DPD.DISBURSEMENTPROCESSID = @ID
            insert into @VoidReprintDPDIDs
              select 
                DPD.ID
                ,newid()
                ,FT.[POSTDATE]
                ,FT.[POSTSTATUSCODE]
              from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD 
                inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = DPD.ID
              where DPD.DISBURSEMENTPROCESSID = @ID
          end
      end

    if @SUCCESSFULCHECKPRINTINGCODE=2 -- Marking some unsuccessful
      begin
        set @HASUNSUCCESSFUL = 1      

        declare @TOTALDISBURSEMENTS int
        select    
            @TOTALDISBURSEMENTS=COUNT(*)
          from
            dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
            inner join dbo.FINANCIALTRANSACTION as FTD on DPD.ID = FTD.ID
        where
          DPD.DISBURSEMENTPROCESSID = @ID
          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
            ,@ID
            ,[RANGESTART]
            ,[RANGEEND]
            ,case when [RANGEEND]<>0
              THEN
                [RANGEEND]-[RANGESTART]+1 
              ELSE
                0
              END
             as [RANGECOUNT]
            ,[USERACTION]
            ,[STARTNUMBER]
          from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID)

        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('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_INVALIDCHECKNUMBERGRID', 16, 1)


        -- Do not allow the check number to exceed 9 characters.
        if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWCHECKNUMBERGREATERTHANNINE(@RANGESGRID)=1
          raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_9DIGITSONLYGRID',16,1)


        -- Validate that the start numbers are less than the end numbers.
        if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWSTARTGREATERTHANEND(@ID, @RANGESGRID)=1
          raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_RANGESTARTGREATERTHANRANGEEND', 16, 1)


        -- Validate that the ranges do not overlap.
        if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOVERLAPPINGRANGES(@ID, @RANGESGRID)=1
          raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_RANGESCANNOTOVERLAP', 16, 1)


        -- Validate that the range falls within the available range.
        if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOUTSIDERANGE(@ID, @RANGESGRID)=1
          raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_INCORRECTRANGE', 16, 1)

                -- Validate that the STARTNUMBER is set when the action is set to renumber
                if exists(select * from @TempTbl where [USERACTION] = 3 and len([STARTNUMBER]) = 0)
                    raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_STARTNUMBER_REQUIRED', 16, 1)

        -- Get a list of disbursement process disbursement ids which will not be committed since they will be reprinted.
        insert into @ReprintIDs
          select 
            DPD.ID
          from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD 
            inner join dbo.BANKACCOUNTTRANSACTION as BAT on DPD.ID = BAT.ID
            inner join @TempTbl as TEMP on 
              (case when TEMP.RANGEEND=0 then TEMP.RANGESTART+TEMP.RANGECOUNT-1 else TEMP.RANGEEND end >= BAT.TRANSACTIONNUMBER) 
              and 
              (TEMP.RANGESTART <= BAT.TRANSACTIONNUMBER)            
          where DPD.DISBURSEMENTPROCESSID = @ID
            and TEMP.USERACTION=0 -- 0 = Reprint

        -- Get a list of disbursement process disbursement ids which will be voided.
        insert into @VoidBATIDs
          select
            BAT.ID
          from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD 
            inner join dbo.BANKACCOUNTTRANSACTION as BAT on DPD.ID = BAT.ID
            inner join @TempTbl as TEMP on 
              (case when TEMP.RANGEEND=0 then TEMP.RANGESTART+TEMP.RANGECOUNT-1 else TEMP.RANGEEND end >= BAT.TRANSACTIONNUMBER) 
              and 
              (TEMP.RANGESTART <= BAT.TRANSACTIONNUMBER)            
          where DPD.DISBURSEMENTPROCESSID = @ID
            and TEMP.USERACTION=1  -- 1 = Void

        -- Get a list of disbursement process disbursement ids which will be voided and reprinted.
        insert into @VoidReprintBATIDs
          select
            BAT.ID
          from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
            inner join dbo.BANKACCOUNTTRANSACTION as BAT on DPD.ID = BAT.ID
            inner join @TempTbl as TEMP on 
     (case when TEMP.RANGEEND=0 then TEMP.RANGESTART+TEMP.RANGECOUNT-1 else TEMP.RANGEEND end >= BAT.TRANSACTIONNUMBER) 
              and 
              (TEMP.RANGESTART <= BAT.TRANSACTIONNUMBER)            
          where DPD.DISBURSEMENTPROCESSID = @ID
            and TEMP.USERACTION=2  -- 1 = Void and Reprint        
        insert into @VoidReprintDPDIDs
          select 
            DPD.ID
            ,newid()
            ,FT.[POSTDATE]
            ,FT.[POSTSTATUSCODE]
          from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD 
            inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = DPD.ID
            inner join dbo.BANKACCOUNTTRANSACTION as BAT on DPD.ID = BAT.ID
            inner join @TempTbl as TEMP on 
              (case when TEMP.RANGEEND=0 then TEMP.RANGESTART+TEMP.RANGECOUNT-1 else TEMP.RANGEEND end >= BAT.TRANSACTIONNUMBER) 
              and 
              (TEMP.RANGESTART <= BAT.TRANSACTIONNUMBER)            
          where DPD.DISBURSEMENTPROCESSID = @ID
            and TEMP.USERACTION=2 -- 0 = Void and Reprint


        -- Get a list of disbursement process disbursement ids which will be renumbered.
        insert into @RenumberIDs
          select
            ROW_NUMBER() over (order by DPD.DATEADDED) + TEMP.STARTNUMBER - 1 as ROWID
            ,DPD.ID
          from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
            inner join dbo.BANKACCOUNTTRANSACTION as BAT on DPD.ID = BAT.ID
            inner join @TempTbl as TEMP on (TEMP.RANGESTART <= BAT.TRANSACTIONNUMBER and (TEMP.RANGESTART + TEMP.RANGECOUNT - 1) >= BAT.TRANSACTIONNUMBER)
          where DPD.DISBURSEMENTPROCESSID = @ID
            and (TEMP.USERACTION=3) -- Renumber


        --Check to see if we are trying to renumber any checks.
        if (select count(*) from @RenumberIDs)>0
          -- Validate that the new numbers are not in the bank account transaction table.
          if dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWDUPLICATECHECKNUMBERS(@ID, @BANKACCOUNTID, @RANGESGRID, 1, 0)=1
            raiserror('ERR_DISBURSEMENTPROCESSFORMNUMBERRANGE_CHECKNUMBEREXISTSGRID', 16, 1)

      end    

        declare @CONTEXTCACHE varbinary(128); 
        set @CONTEXTCACHE = CONTEXT_INFO();

        if @CHANGEAGENTID is not null 
            set CONTEXT_INFO @CHANGEAGENTID; 

    -- Delete all the rows in DISBURSEMENTPROCESSPRINTFINALIZERANGE that match our disbursementprocess
    delete from dbo.DISBURSEMENTPROCESSPRINTFINALIZERANGE 
    where DISBURSEMENTPROCESSID = @ID

        if not @CONTEXTCACHE is null 
            set CONTEXT_INFO @CONTEXTCACHE;

    -- Take corrective action as needed.
    if @HASUNSUCCESSFUL=1
      begin
        if @SUCCESSFULCHECKPRINTINGCODE=1
          if @UNSUCESSFULACTION=0 
            set @PERFORMREPRINTACTION=1
          if @UNSUCESSFULACTION=1
            set @PERFORMVOIDACTION=1
          if @UNSUCESSFULACTION=2
            set @PERFORMREPRINTANDVOIDACTION=1
        else
          begin
            -- Reprint any items in the ReprintIDs table.
            set @PERFORMREPRINTACTION=1

            -- Void any items in the VoidBATIDs table.
            set @PERFORMVOIDACTION=1

            -- Void and Reprint any items in the table.
            set @PERFORMREPRINTANDVOIDACTION = 1

                        --insert "renumbered" records for the history tab
                        insert into dbo.DISBURSEMENTHISTORY
                        (
                            ID
                            ,FINANCIALTRANSACTIONID
                            ,ACTIONCODE
                            ,ORIGINALNUMBER
                            ,CHANGEDNUMBER
                          -- Standard stuff
                            , DATEADDED
                            , DATECHANGED
                            , CHANGEDBYID
                            , ADDEDBYID
                        )
                        select
                            NEWID()
                            ,IDS.DISBURSEMENTPROCESSDISBURSEMENTID
                            ,4 --renumbered
                            ,BAT.TRANSACTIONNUMBER
                            ,IDS.ROWID
                            -- Standard stuff
                            ,@CURRENTDATE
                            ,@CURRENTDATE
                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                        from @RenumberIDs IDS
                        inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = IDS.DISBURSEMENTPROCESSDISBURSEMENTID


                        update dbo.BANKACCOUNTTRANSACTION_EXT
                        set 
                          TRANSACTIONNUMBER = REN.ROWID
                          -- Boilerplate
                          ,CHANGEDBYID = @CHANGEAGENTID
                          ,DATECHANGED = @CURRENTDATE
                        from dbo.BANKACCOUNTTRANSACTION_EXT
                          inner join @RenumberIDs as REN on REN.DISBURSEMENTPROCESSDISBURSEMENTID = BANKACCOUNTTRANSACTION_EXT.ID
                        where 
                          DISBURSEMENTPROCESSID = @ID 
                        ;

          end

        if @PERFORMREPRINTACTION=1
          begin
            declare @RemoveIDs table([BANKACCOUNTTRANSACTIONID] uniqueidentifier)
            insert into @RemoveIDs
              select 
                BAT.ID
              from dbo.BANKACCOUNTTRANSACTION as BAT 
              where
                BAT.ID in (select DISBURSEMENTPROCESSDISBURSEMENTID from @ReprintIDs)
            -- update the bankaccount transaction table to remove the checks that were in the register
                        set @CONTEXTCACHE = CONTEXT_INFO(); 

                        if @CHANGEAGENTID is not null 
                          set CONTEXT_INFO @CHANGEAGENTID; 

            delete from dbo.BANKACCOUNTTRANSACTION where ID in (select BANKACCOUNTTRANSACTIONID from @RemoveIDs)

                        if not @CONTEXTCACHE is null 
                          set CONTEXT_INFO @CONTEXTCACHE;

          end

        declare @Schedules UDT_GENERICID;

        if @PERFORMVOIDACTION=1
          exec USP_BANKACCOUNTDISBURSEMENTS_VOID @VoidBATIDs, null, null, null, null, @CHANGEAGENTID, @Schedules

        if @PERFORMREPRINTANDVOIDACTION=1
          begin

                        --Get the schedule IDs so they will not be deleted for a void and reprint action
                        declare @SCHEDULESNOTTODELETE UDT_GENERICID;
                        insert into @SCHEDULESNOTTODELETE (ID)
                        select
                    distinct FTA.FINANCIALTRANSACTIONSCHEDULEID
            from @VoidReprintDPDIDs as VOID
              inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTA.FINANCIALTRANSACTIONID = VOID.DISBURSEMENTPROCESSDISBURSEMENTID

            -- Void the checks.
            exec USP_BANKACCOUNTDISBURSEMENTS_VOID @VoidReprintBATIDs, null, null, null, null, @CHANGEAGENTID, @SCHEDULESNOTTODELETE

            -- Add back in new FTs, FTAs, and DPDs so that the next create will be able to add new checks.
            insert into dbo.FINANCIALTRANSACTION
                  ( 
                      ID
                      ,TRANSACTIONAMOUNT
              ,BASEAMOUNT
              ,ORGAMOUNT
                      ,CONSTITUENTID
                      -- From the process
                      ,[DATE]
                      ,POSTDATE
                      ,POSTSTATUSCODE
              ,PDACCOUNTSYSTEMID
              ,TRANSACTIONCURRENCYID
                      -- Standard stuff
                      ,DATEADDED
                      ,DATECHANGED
                      ,CHANGEDBYID
                      ,ADDEDBYID
                      -- Constants
                      ,DELETEDON
                      ,TYPECODE
                  )
            select 
              VOID.[NEWID]
                      ,FT.TRANSACTIONAMOUNT
              ,FT.TRANSACTIONAMOUNT
              ,FT.TRANSACTIONAMOUNT
                      ,FT.CONSTITUENTID
                      -- From the process
                      ,FT.[DATE]
                      ,VOID.POSTDATE
                      ,VOID.POSTSTATUSCODE
              ,@PDACCOUNTSYSTEMID
              ,@TRANSACTIONCURRENCYID
                      -- Standard stuff
                      ,FT.DATEADDED
                      ,FT.DATECHANGED
                      ,FT.CHANGEDBYID
                      ,FT.ADDEDBYID
                      -- Constants
                      ,NULL
                      ,FT.TYPECODE
            from @VoidReprintDPDIDs as VOID
              inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = VOID.DISBURSEMENTPROCESSDISBURSEMENTID

            insert into dbo.DISBURSEMENTPROCESSDISBURSEMENT
                (
                    ID
                    ,DISBURSEMENTPROCESSID    
                    ,REMITADDRESSID
                    -- Standard stuff
                    , DATEADDED
                    , DATECHANGED
                    , CHANGEDBYID
                    , ADDEDBYID
                )
            select
              VOID.[NEWID]
                    ,DPD.DISBURSEMENTPROCESSID    
                    ,DPD.REMITADDRESSID
                    -- Standard stuff
                    ,DPD.DATEADDED
                    ,DPD.DATECHANGED
                    ,DPD.CHANGEDBYID
                    ,DPD.ADDEDBYID
            from @VoidReprintDPDIDs as VOID
              inner join DISBURSEMENTPROCESSDISBURSEMENT as DPD on DPD.ID = VOID.DISBURSEMENTPROCESSDISBURSEMENTID

            insert into dbo.FINANCIALTRANSACTIONAPPLICATION
                (
                    ID
                    ,AMOUNT
                    ,FINANCIALTRANSACTIONID
                    ,FINANCIALTRANSACTIONSCHEDULEID
                    ,TYPECODE
                    -- Standard stuff
                    ,DATEADDED
                    ,DATECHANGED
                    ,CHANGEDBYID
                    ,ADDEDBYID
                )    
            select
              newid()
                    ,FTA.AMOUNT
                    ,VOID.[NEWID]
                    ,FTA.FINANCIALTRANSACTIONSCHEDULEID
                    ,FTA.TYPECODE
                    -- Standard stuff
                    ,FTA.DATEADDED
                    ,FTA.DATECHANGED
                    ,FTA.CHANGEDBYID
                    ,FTA.ADDEDBYID
            from @VoidReprintDPDIDs as VOID
              inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTA.FINANCIALTRANSACTIONID = VOID.DISBURSEMENTPROCESSDISBURSEMENTID

                        -- update the history records with the future disbursement IDs
                        update dbo.DISBURSEMENTHISTORY
                            set FUTUREFINANCIALTRANSACTIONID = VOID.[NEWID]
                                ,CHANGEDBYID = @CHANGEAGENTID
                                ,DATECHANGED = @CURRENTDATE
                        from @VoidReprintDPDIDs VOID
                        inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID =  VOID.DISBURSEMENTPROCESSDISBURSEMENTID
                        inner join dbo.DISBURSEMENTHISTORY H on H.FINANCIALTRANSACTIONID =  VOID.DISBURSEMENTPROCESSDISBURSEMENTID and H.ACTIONCODE = 1 and H.ORIGINALNUMBER = BAT.TRANSACTIONNUMBER

          end        
      end


    -- Update the bank account transaction table and set the processing flag to 0 so the transactions can
    -- start appearing in the register.

    update
      dbo.BANKACCOUNTTRANSACTION_EXT
    set
      PROCESSING = 0
      ,DATECHANGED = @CURRENTDATE
      ,CHANGEDBYID = @CHANGEAGENTID
    from
      dbo.BANKACCOUNTTRANSACTION as BAT
      inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD on BAT.ID = DPD.ID
    where
      BANKACCOUNTTRANSACTION_EXT.DISBURSEMENTPROCESSID = @ID 
      and DPD.ID not in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @ReprintIDs)
      and DPD.ID not in (select [NEWID] from @VoidReprintDPDIDs)

    -- Update the FINANCIALTRANSACTIONAPPLICATION's status from pending to active
    update
      dbo.FINANCIALTRANSACTIONAPPLICATION
    set 
      STATUSCODE = 1 -- active
      ,DATECHANGED = @CURRENTDATE
      ,CHANGEDBYID = @CHANGEAGENTID
    from
      dbo.FINANCIALTRANSACTIONAPPLICATION as FTA
      inner join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
        on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
      left outer join dbo.INVOICE as I 
        on I.ID = FTS.FINANCIALTRANSACTIONID
      left outer join dbo.CREDITMEMO as CM
        on CM.ID = FTS.FINANCIALTRANSACTIONID
    where
      COALESCE(I.DISBURSEMENTPROCESSID,CM.DISBURSEMENTPROCESSID) = @ID
      and STATUSCODE = 0 -- pending
      and FTA.FINANCIALTRANSACTIONID not in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @ReprintIDs)
      and FTA.FINANCIALTRANSACTIONID not in (select [NEWID] from @VoidReprintDPDIDs)


        exec dbo.USP_FINANCIALTRANSACTION_APPLIACTION_CREATE_1099DISTRIBUTION @ID, @CHANGEAGENTID

      -- Remove the flag from invoices and credit memos
      -- and update the invoice and credit memo zerobalance flag.    
      update 
          dbo.INVOICE
      set
          DISBURSEMENTPROCESSID=null
          ,ZEROBALANCE = case when dbo.UFN_INVOICE_GETBALANCE(I.ID) = 0 then 1 else 0 end
          ,DATECHANGED = @CURRENTDATE
          ,CHANGEDBYID = @CHANGEAGENTID
    from dbo.INVOICE as I
      where
          DISBURSEMENTPROCESSID=@ID 
            and I.ID not in (select FTS.FINANCIALTRANSACTIONID
                from dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
                inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
                where FTA.FINANCIALTRANSACTIONID in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @ReprintIDs)
                    or FTA.FINANCIALTRANSACTIONID in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @VoidReprintDPDIDs)
                    or FTA.FINANCIALTRANSACTIONID in (select [NEWID] from @VoidReprintDPDIDs))

      update
          dbo.CREDITMEMO
      set
          DISBURSEMENTPROCESSID=null
          ,ZEROBALANCE = case when dbo.UFN_CREDITMEMO_GETBALANCE(CM.ID) = 0 then 1 else 0 end
          ,DATECHANGED = @CURRENTDATE
          ,CHANGEDBYID = @CHANGEAGENTID
    from dbo.CREDITMEMO as CM
      where 
          DISBURSEMENTPROCESSID=@ID
            and CM.ID not in (select FTS.FINANCIALTRANSACTIONID
                from dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
                inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
                where FTA.FINANCIALTRANSACTIONID in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @ReprintIDs)
                    or FTA.FINANCIALTRANSACTIONID in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @VoidReprintDPDIDs)
                    or FTA.FINANCIALTRANSACTIONID in (select [NEWID] from @VoidReprintDPDIDs))

    --insert "renumbered" records for the history tab
        insert into dbo.DISBURSEMENTHISTORY
        (
            ID
            ,FINANCIALTRANSACTIONID
            ,ACTIONCODE
            ,ORIGINALNUMBER
            -- Standard stuff
            , DATEADDED
            , DATECHANGED
            , CHANGEDBYID
            , ADDEDBYID
        )
        select
            NEWID()
            ,BAT.ID
            ,5
            ,BAT.TRANSACTIONNUMBER
            -- Standard stuff
            ,@CURRENTDATE
            ,@CURRENTDATE
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
        from dbo.BANKACCOUNTTRANSACTION BAT
      inner join dbo.BANKACCOUNTTRANSACTION_EXT as BATEXT on BAT.ID = BATEXT.ID
            inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD on BAT.ID = DPD.ID
    where
      BATEXT.DISBURSEMENTPROCESSID = @ID 
      and BAT.ID not in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @ReprintIDs)
      and BAT.ID not in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @VoidReprintDPDIDs)
            and BAT.ID not in (select [NEWID] from @VoidReprintDPDIDs)
            and BAT.ID not in (select [ID] from @VoidBATIDs)

      -- Delete the DISBURSEMENTPROCESSDISBURSEMENT entries
      delete
          from dbo.DISBURSEMENTPROCESSDISBURSEMENT 
      where
          DISBURSEMENTPROCESSID = @ID 
      and ID not in (select [DISBURSEMENTPROCESSDISBURSEMENTID] from @ReprintIDs)
      and ID not in (select [NEWID] from @VoidReprintDPDIDs)


    -- If applicable we need to change the status of the disbursement process.
    if @HASUNSUCCESSFUL=0
      set @CHANGESTATUSCOMMITTED=1
    else
      begin
        if @SUCCESSFULCHECKPRINTINGCODE=1 
          begin
            if (@UNSUCESSFULACTION=0 or @UNSUCESSFULACTION=2)
              set @CHANGESTATUSREPRINTED=1
            else
              set @CHANGESTATUSCOMMITTED=1
          end
        if @SUCCESSFULCHECKPRINTINGCODE=2
          begin
            if (((select count(*) from @ReprintIDs)>0) or ((select count(*) from @VoidReprintDPDIDs)>0))
              set @CHANGESTATUSREPRINTED=1
            else
              set @CHANGESTATUSCOMMITTED=1
          end
      end

    if @CHANGESTATUSCOMMITTED=1
      update 
            dbo.DISBURSEMENTPROCESS
        set
            STATUSCODE = 2  -- 2 = Committed
        ,HASUNSUCCESSFUL = 0
            ,DATECHANGED = @CURRENTDATE
            ,CHANGEDBYID = @CHANGEAGENTID
      where
        ID = @ID; 

    if @CHANGESTATUSREPRINTED=1
      update
        dbo.DISBURSEMENTPROCESS
      set
        STATUSCODE = 1  -- 1 = Created
        ,HASUNSUCCESSFUL = 1
        ,DATECHANGED = @CURRENTDATE
        ,CHANGEDBYID = @CHANGEAGENTID
      where
        ID = @ID; 


  end try

  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

return 0;