USP_SEPAMANDATE_PAYMENTMADE

CREATE procedure dbo.USP_SEPAMANDATE_PAYMENTMADE 
  (
    @SEPAMANDATEID uniqueidentifier,
    @BATCHROWID uniqueidentifier,
    @FINANCIALTRANSACTIONID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
  )
  as
  begin

  set nocount on;
  begin try

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

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

    --If the SEPA mandate has changed on the payment, we need to update appropriately

    declare @ORIGINALSEPAMANDATEID uniqueidentifier;
    if @FINANCIALTRANSACTIONID is not null
    begin
        select 
            @ORIGINALSEPAMANDATEID = DIRECTDEBITPAYMENTMETHODDETAIL.SEPAMANDATEID
        from 
            dbo.DIRECTDEBITPAYMENTMETHODDETAIL
            inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = DIRECTDEBITPAYMENTMETHODDETAIL.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
        where 
            FINANCIALTRANSACTION.ID = @FINANCIALTRANSACTIONID;

        --If the SEPA mandate has changed on the payment, we need to update appropriately

        --Decrement the payment count for the old mandate

        if @ORIGINALSEPAMANDATEID is not null and (@SEPAMANDATEID <> @ORIGINALSEPAMANDATEID or @SEPAMANDATEID is null)
        begin
            update dbo.SEPAMANDATE
            set
                PAYMENTCOUNT = PAYMENTCOUNT - 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                SEPAMANDATE.ID = @ORIGINALSEPAMANDATEID
                and PAYMENTCOUNT > 0;
        end
    end

     --Only increment the paymentcount when its a brand new payment or the mandates have changed.

     if (@ORIGINALSEPAMANDATEID is null) or (@ORIGINALSEPAMANDATEID is not null and (@SEPAMANDATEID <> @ORIGINALSEPAMANDATEID))
     begin
      update dbo.SEPAMANDATE
      set 
        PAYMENTCOUNT = PAYMENTCOUNT + 1,
        STATUSCODE = case
                        when TYPECODE = 0 and STATUSCODE = 1 then 2
                        else STATUSCODE
                     end,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        SEPAMANDATE.ID = @SEPAMANDATEID;
     end

    if @BATCHROWID is not null
    begin
        update dbo.SEPAMANDATEACTIVITY
        set FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID
        where SEPAMANDATEACTIVITY.BATCHROWID = @BATCHROWID;
    end

  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch
end