USP_REVENUE_UPDATEPAYMENTGIFTFEE

CREATE procedure dbo.USP_REVENUE_UPDATEPAYMENTGIFTFEE(
  @REVENUEID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @PAYMENTMETHODCODE tinyint = null
)
as
begin try
    set nocount on;

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

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

  declare @DEFAULTREASONCODEID uniqueidentifier = null
  select @DEFAULTREASONCODEID = ID from dbo.GIFTFEEOVERRIDEREASONCODE where ISPAYMENTEDITDEFAULT=1 and ACTIVE=1

  if @DEFAULTREASONCODEID is not null
  begin

    --If there should be no gift fees, then remove existing gift fees

    if (dbo.UFN_GIFTFEE_ENABLED() = 0 or dbo.UFN_GIFTFEE_CONSTITUENTEXCLUDED(@CONSTITUENTID) = 1 or dbo.UFN_GIFTFEE_PAYMENTMETHODEXCLUDED_2(@PAYMENTMETHODCODE) = 1)
    begin
      update REVENUESPLITGIFTFEE set
        FEE=0,
        TRANSACTIONAMOUNT=0,
        ORGANIZATIONAMOUNT=0,
        WAIVED=1,
        CHANGEDBYID=@CHANGEAGENTID,
        DATECHANGED=@CURRENTDATE
      from
        dbo.REVENUESPLITGIFTFEE
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTFEE.ID
        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
      where
        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
    end
    else
    begin
      declare @FEES table (
        ID uniqueidentifier,
        FEE money,
        WAIVED bit,
        SPLITRECEIPTAMOUNT money,
        BASECURRENCYID uniqueidentifier,
        ORGANIZATIONAMOUNT money,
        ORGANIZATIONEXCHANGERATEID uniqueidentifier,
        TRANSACTIONAMOUNT money,
        TRANSACTIONCURRENCYID uniqueidentifier,
        BASEEXCHANGERATEID uniqueidentifier
      )

      insert @FEES 
        (ID, FEE, WAIVED, SPLITRECEIPTAMOUNT, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID)
      select 
        ID, FEE, WAIVED, SPLITRECEIPTAMOUNT, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID
      from dbo.UFN_REVENUE_GENERATEDEFAULTGIFTFEES(@REVENUEID, @CONSTITUENTID)

      --Rows are not allowed to be removed so update and insert are all we need

        update dbo.REVENUESPLITGIFTFEE
        set REVENUESPLITGIFTFEE.FEE = FEES.FEE,
            REVENUESPLITGIFTFEE.TRANSACTIONAMOUNT = FEES.TRANSACTIONAMOUNT,
            REVENUESPLITGIFTFEE.ORGANIZATIONAMOUNT = FEES.ORGANIZATIONAMOUNT,
            REVENUESPLITGIFTFEE.WAIVED = FEES.WAIVED,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from dbo.REVENUESPLITGIFTFEE
        inner join @FEES FEES
            on FEES.ID = REVENUESPLITGIFTFEE.ID
        where FEES.FEE <> REVENUESPLITGIFTFEE.FEE
            or FEES.WAIVED <> REVENUESPLITGIFTFEE.WAIVED

        insert into dbo.REVENUESPLITGIFTFEE(ID, FEE, WAIVED, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, CHANGEDBYID, ADDEDBYID, DATECHANGED,  DATEADDED)
        select ID, FEE, WAIVED, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @FEES FEES
        where not exists(select top 1 ID from REVENUESPLITGIFTFEE where REVENUESPLITGIFTFEE.ID = FEES.ID)
        and (FEE > 0 or WAIVED = 1)    

      --remove existing gift fees whose payment method is now excluded

      update dbo.REVENUESPLITGIFTFEE set
        FEE=0,
        TRANSACTIONAMOUNT=0,
        ORGANIZATIONAMOUNT=0,
        WAIVED=1,
        CHANGEDBYID=@CHANGEAGENTID,
        DATECHANGED=@CURRENTDATE
      from dbo.REVENUESPLITGIFTFEE
      inner join dbo.REVENUESPLIT on REVENUESPLITGIFTFEE.ID = REVENUESPLIT.ID
      left join @FEES FEES on FEES.ID = REVENUESPLITGIFTFEE.ID
      where FEES.ID is null and REVENUESPLIT.REVENUEID = @REVENUEID
    end

    update PAYMENTGIFTFEEOVERRIDE set
      REASONCODEID = @DEFAULTREASONCODEID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    from
      dbo.PAYMENTGIFTFEEOVERRIDE
    where
      ID = @REVENUEID;

    --if new then insert data

    if @@ROWCOUNT = 0
      insert into dbo.PAYMENTGIFTFEEOVERRIDE(ID, REASONCODEID, ADDEDBYID, CHANGEDBYID, DATECHANGED, DATEADDED)
      values(@REVENUEID, @DEFAULTREASONCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
  end
end try

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