USP_INVOICE_DELETE

CREATE procedure dbo.USP_INVOICE_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as begin
    --check deletion rules, if any

    set nocount on;

    declare @FTAID uniqueidentifier
    declare @ZEROBALANCE bit
  declare @AMOUNT money
  declare @BALANCE money

    set @ZEROBALANCE = (select ZEROBALANCE from dbo.INVOICE where INVOICE.ID = @ID)
  set @AMOUNT = (select TRANSACTIONAMOUNT from dbo.FINANCIALTRANSACTION where FINANCIALTRANSACTION.ID = @ID)
  set @BALANCE = (select BALANCE from dbo.INVOICE where INVOICE.ID = @ID)

    if exists(select DISBURSEMENTPROCESS.ID 
        from dbo.DISBURSEMENTPROCESS
        where DISBURSEMENTPROCESS.ID = (select DISBURSEMENTPROCESSID from dbo.INVOICE where INVOICE.ID = @ID))

        or 


        (select count(FTA.ID) from dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FT.ID = FTS.FINANCIALTRANSACTIONID and FTS.DELETED = 0
            inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
            where FT.ID = @ID)
         > 1
    begin
        raiserror('An invoice that is being processed cannot be deleted.', 13, 1);
        return 0;
    end

  -- raise error if it's fully or partially paid

  if dbo.UFN_PAYABLES_HASPAYMENTS(@ID) = 1
  begin
        raiserror('An invoice that is fully or partially paid cannot be deleted.', 13, 1);
        return 0;
    end

    -- if it has a financial transaction application, mark it as deleted otherwise, delete it

    if exists(select FTA.ID from dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FT.ID = FTS.FINANCIALTRANSACTIONID
            inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
            where FT.ID = @ID)
    begin
          if @ZEROBALANCE = 0 update dbo.FINANCIALTRANSACTION set DELETEDON = GETDATE() where ID = @ID;
    end
    else
        begin
            -- use the system generated delete routine to allow proper recording of the deleting agent

            exec USP_INVOICE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
            exec USP_FINANCIALTRANSACTION_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
        end
    return 0;

end