USP_DISBURSEMENTPROCESS_FINALIZE

CREATE procedure dbo.USP_DISBURSEMENTPROCESS_FINALIZE (
    @ID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier
)
as begin

  -- Verify that this can be called.

  if not exists( select * from dbo.DISBURSEMENTPROCESS where ID = @ID and STATUSCODE = 3 ) -- Printing

    raiserror ('Cannot finalize a process that is not in the Printing status', 16, 1);

    -- Boilerplate

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    -- Update the FINANCIALTRANSACTIONAPPLICATION's status from pending to active

  update
    dbo.FINANCIALTRANSACTIONAPPLICATION
  set 
    STATUSCODE = 1 -- active

    -- standard update

    ,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


    -- 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(ID) = 0 then 1 else 0 end
        -- standard update

        ,DATECHANGED = @CURRENTDATE
        ,CHANGEDBYID = @CHANGEAGENTID
    where
        DISBURSEMENTPROCESSID=@ID;

    update
        dbo.CREDITMEMO
    set
        DISBURSEMENTPROCESSID=null
        ,ZEROBALANCE = case when dbo.UFN_CREDITMEMO_GETBALANCE(ID) = 0 then 1 else 0 end
        -- standard update

        ,DATECHANGED = @CURRENTDATE
        ,CHANGEDBYID = @CHANGEAGENTID
    where 
        DISBURSEMENTPROCESSID=@ID;     

    -- Delete the DISBURSEMENTPROCESSDISBURSEMENT entries

    delete
        from dbo.DISBURSEMENTPROCESSDISBURSEMENT
    where
        DISBURSEMENTPROCESSID = @ID;

  -- update the bankaccount 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
    -- standard update

    ,DATECHANGED = @CURRENTDATE
    ,CHANGEDBYID = @CHANGEAGENTID
  from
    dbo.BANKACCOUNTTRANSACTION as BAT
  where
    DISBURSEMENTPROCESSID = @ID;

    -- 3. Change the status of the disbursement process    

    update 
        dbo.DISBURSEMENTPROCESS
    set
        STATUSCODE = 4  -- Finished

        -- standard update

        ,DATECHANGED = @CURRENTDATE
        ,CHANGEDBYID = @CHANGEAGENTID
  where
    ID = @ID; 


end;