USP_PAYMENT_ADJUST

CREATE procedure [dbo].[USP_PAYMENT_ADJUST] (
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime,
  @DATE datetime,
  @AMOUNT money,
  @RECEIPTAMOUNT money,
  @REVENUESTREAMS xml,
  @SOURCECODE nvarchar(50),
  @APPEALID uniqueidentifier,
  @BENEFITS xml,
  @BENEFITSWAIVED bit,
  @GIVENANONYMOUSLY bit,
  @MAILINGID uniqueidentifier,
  @CHANNELCODEID uniqueidentifier,
  @DONOTRECEIPT bit,
  @REFERENCE nvarchar(255),
  @DONOTACKNOWLEDGE bit = 0,
  @SPLITSDECLININGGIFTAID xml = null output,
  @PERCENTAGEBENEFITS xml = null output,
  @GIFTAIDSPONSORSHIPSPLITS xml = null output,
  @BASEEXCHANGERATEID uniqueidentifier = null output,
  @EXCHANGERATE decimal(20, 8) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @BENEFITSADJUSTMENTID uniqueidentifier = null,
  @ADJUSTMENTPOSTDATE datetime = null,
  @PAYMENTMETHODCODE tinyint,
  @ORIGINALPAYMETHODID uniqueidentifier,
  @CHECKDATE dbo.UDT_FUZZYDATE,
  @CHECKNUMBER nvarchar(20),
  @CONSTITUENTACCOUNTID uniqueidentifier,
  @REFERENCEDATE dbo.UDT_FUZZYDATE,
  @REFERENCENUMBER nvarchar(20),
  @CARDHOLDERNAME nvarchar(255),
  @CREDITCARDNUMBER nvarchar(4),
  @CREDITTYPECODEID uniqueidentifier,
  @AUTHORIZATIONCODE nvarchar(20),
  @EXPIRESON dbo.UDT_FUZZYDATE,
  @ISSUER nvarchar(100),
  @NUMBEROFUNITS decimal(20,3),
  @SYMBOL nvarchar(25),
  @MEDIANPRICE decimal(19,4),
  @PROPERTYSUBTYPECODEID uniqueidentifier,
  @GIFTINKINDSUBTYPECODEID uniqueidentifier,
  @KEYALREADYOPEN bit,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier,
  @DIRECTDEBITRESULTCODE nvarchar(10),
  @LOWPRICE decimal(19,4),
  @HIGHPRICE decimal(19,4),
  @SALEDATE datetime = null,
  @SALEAMOUNT money = null,
  @BROKERFEE money = null,
  @SALEPOSTDATE datetime = null,
  @SALEPOSTSTATUSCODE tinyint = null,
  @GIFTINKINDITEMNAME nvarchar(100),
  @GIFTINKINDDISPOSITIONCODE tinyint,
  @GIFTINKINDNUMBEROFUNITS int,
  @GIFTINKINDFAIRMARKETVALUE money,
  @DIRECTDEBITISREJECTED bit,
  @ORIGINALPAYMENTMETHODCODE tinyint,
  @ORIGINALVENDORID nvarchar(50), 
  @ORIGINALTRANSACTIONID uniqueidentifier, 
  @SEPAMANDATEID uniqueidentifier, 
  @OVERRIDESAVEDVENDORID bit,
  @OVERRIDESAVEDBBPSTRANSACTIONID bit, 
  @OVERRIDESAVEDSEPAMANDATEID bit,
    @OLDSPOTRATEID uniqueidentifier = null output
  )
as
set nocount on;

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

if @CURRENTDATE is null
  set @CURRENTDATE = GetDate();

declare @CONSTITUENTID uniqueidentifier;
declare @ORIGINALGIVENANONYMOUSLY bit;
declare @ORGANIZATIONAMOUNT money;
declare @BASEAMOUNT money;
declare @BASECURRENCYID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @PREVIOUSDATE datetime;
declare @PREVIOUSDONOTRECEIPT bit;
declare @PREVIOUSRECEIPTAMOUNT money;

begin try
  if @AMOUNT < 0
    raiserror (
        'BBERR_NEGATIVEAMOUNT.'
        ,13
        ,1
        );

  -- check to see if amount or receipt amount have changed

  declare @FIELDCHANGED bit;
  declare @AMOUNTCHANGED bit;

  set @FIELDCHANGED = 0;

  if (
      select count(FINANCIALTRANSACTION.ID)
      from dbo.FINANCIALTRANSACTION
      inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
      where FINANCIALTRANSACTION.ID = @ID
        and FINANCIALTRANSACTION.BASEAMOUNT = @AMOUNT
        and REVENUE_EXT.RECEIPTAMOUNT = @RECEIPTAMOUNT
        and FINANCIALTRANSACTION.DELETEDON is null
      ) = 0
    set @FIELDCHANGED = 1;
  set @AMOUNTCHANGED = @FIELDCHANGED;

  -- check to see if designations have changed

  if @FIELDCHANGED = 0
    if dbo.UFN_CHECKDETAIL_STREAMSCHANGED(@ID, @REVENUESTREAMS) = 1
      set @FIELDCHANGED = 1;

  -- if a field has changed, determine if the revenue needs to be re-receipted or re-acknowledged

  if @FIELDCHANGED = 1
  begin
    declare @OLDDESIGNATIONS table (DESIGNATIONID uniqueidentifier);
    declare @DESIGNATIONS table (DESIGNATIONID uniqueidentifier);
    declare @OLDGIFTFIELDS xml;
    declare @GIFTFIELDS xml;

    set @OLDGIFTFIELDS = dbo.[UFN_REVENUE_GETAPPLICATIONS_TOITEMLISTXML](@ID)
    set @GIFTFIELDS = (
        select [GIFTFIELDS]
        from dbo.[UFN_REVENUE_GETAPPLICATIONS_FROMXML](@REVENUESTREAMS)
        for xml raw('ITEM')
          ,type
          ,elements
          ,root('REVENUESTREAMS')
          ,binary BASE64
        )

    insert into @OLDDESIGNATIONS (DESIGNATIONID)
    select T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier') as DESIGNATIONID
    from @OLDGIFTFIELDS.nodes('REVENUESTREAMS/ITEM/GIFTFIELDS/ITEM') T(c)

    insert into @DESIGNATIONS (DESIGNATIONID)
    select T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier') as DESIGNATIONID
    from @GIFTFIELDS.nodes('REVENUESTREAMS/ITEM/GIFTFIELDS/GIFTFIELDS/ITEM') T(c)

    if (
        select count(*)
        from @DESIGNATIONS [DES]
        inner join dbo.DESIGNATION on [DES].DESIGNATIONID = DESIGNATION.ID
        where DESIGNATION.ISACTIVE = 0
          and [DES].DESIGNATIONID not in (
            select DESIGNATIONID
            from @OLDDESIGNATIONS
            )
        ) > 0
      raiserror (
          'Revenue cannot be added to inactive designations.'
          ,13
          ,2
          );

    exec dbo.USP_REVENUE_UPDATERERECEIPTS @ID
      ,@CHANGEAGENTID
      ,@CURRENTDATE;

    exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID
      ,@CHANGEAGENTID
      ,@CURRENTDATE;
  end

  select @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
    ,@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
    ,@BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
    ,@OLDSPOTRATEID = case 
      when CURRENCYEXCHANGERATE.TYPECODE = 2
        and not (
          @BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
          or (
            @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
            and @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE
            )
          )
        then CURRENCYEXCHANGERATE.ID
      else null
      end
    ,@PREVIOUSDATE = cast(FINANCIALTRANSACTION.date as datetime)
    ,@PREVIOUSDONOTRECEIPT = REVENUE_EXT.DONOTRECEIPT
    ,@PREVIOUSRECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
  inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
  left join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
  where FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null;

  --If the record uses a new spot rate, create it and set the rate ID.

  if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
  begin
    set @BASEEXCHANGERATEID = newid();

    --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

    /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
      and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
    begin
      raiserror('User does not have the right to add a new spot rate.', 13, 1);
      return 1;
    end*/
    insert into dbo.CURRENCYEXCHANGERATE (
      ID
      ,FROMCURRENCYID
      ,TOCURRENCYID
      ,RATE
      ,ASOFDATE
      ,TYPECODE
      ,SOURCECODEID
      ,ADDEDBYID
      ,CHANGEDBYID
      ,DATEADDED
      ,DATECHANGED
      )
    values (
      @BASEEXCHANGERATEID
      ,@TRANSACTIONCURRENCYID
      ,@BASECURRENCYID
      ,@EXCHANGERATE
      ,@DATE
      ,2
      ,null
      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@CURRENTDATE
      );
  end

  exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT
    ,@DATE
    ,@BASECURRENCYID
    ,@BASEEXCHANGERATEID
    ,@TRANSACTIONCURRENCYID
    ,@BASEAMOUNT output
    ,@ORGANIZATIONCURRENCYID output
    ,@ORGANIZATIONAMOUNT output
    ,@ORGANIZATIONEXCHANGERATEID output
    ,1;

  /*This assumes one paymethod row and will have to be changed in CASSI for Tenders*/
  update dbo.REVENUEPAYMENTMETHOD
  set AMOUNT = @BASEAMOUNT
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CURRENTDATE
  where REVENUEPAYMENTMETHOD.REVENUEID = @ID;

  select @ORIGINALGIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where FINANCIALTRANSACTION.ID = @ID;

  if @AMOUNTCHANGED = 1
    -- make sure we aren't trying to modify revenue in a locked/closed deposit/bank

    exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;

  if @RECEIPTAMOUNT < 0
    raiserror (
        'CK_REVENUE_RECEIPTAMOUNTNOTNEGATIVE'
        ,16
        ,1
        );

  --Update Transaction

  update dbo.FINANCIALTRANSACTION
  set date = @DATE
    ,BASEAMOUNT = @BASEAMOUNT
    ,TRANSACTIONAMOUNT = @AMOUNT
    ,ORGAMOUNT = @ORGANIZATIONAMOUNT
    ,ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
    ,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CURRENTDATE
  where ID = @ID

  update dbo.REVENUE_EXT
  set RECEIPTAMOUNT = @RECEIPTAMOUNT
    ,SOURCECODE = @SOURCECODE
    ,APPEALID = @APPEALID
    ,GIVENANONYMOUSLY = @GIVENANONYMOUSLY
    ,DONOTRECEIPT = @DONOTRECEIPT
    ,BENEFITSWAIVED = @BENEFITSWAIVED
    ,MAILINGID = @MAILINGID
    ,DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE
    ,CHANNELCODEID = @CHANNELCODEID
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CURRENTDATE
    ,REFERENCE = isnull(@REFERENCE, '')
  where ID = @ID

  if not (
      @REFERENCE is null
      or len(@REFERENCE) = 0
      )
  begin
    --update revenue gl reference as well

    --if exists(select top 1 ID from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID )

    if exists (
        select 1
        from dbo.JOURNALENTRY
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
        where FINANCIALTRANSACTIONLINEITEM.DELETEDON is not null
          and JOURNALENTRY_EXT.TABLENAMECODE = 1
          and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
        )
    begin
      if @REFERENCE is not null
        and len(@REFERENCE) > 0
        update dbo.JOURNALENTRY
        set COMMENT = case 
            when len(isnull(BANKACCOUNTTRANSACTION.DESCRIPTION, '')) > 0
              and JOURNALENTRY.TRANSACTIONTYPECODE = 0
              then BANKACCOUNTTRANSACTION.DESCRIPTION
            else @REFERENCE
            end
          ,CHANGEDBYID = @CHANGEAGENTID
          ,DATECHANGED = @CURRENTDATE
        from dbo.JOURNALENTRY
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        left join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        left join dbo.BANKACCOUNTTRANSACTION_EXT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION_EXT.ID
        left join dbo.FINANCIALTRANSACTION as BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION_EXT.ID = BANKACCOUNTTRANSACTION.ID
        left join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION_EXT.BANKACCOUNTID
        left join dbo.DEPOSITGLDISTRIBUTIONLINK on DEPOSITGLDISTRIBUTIONLINK.ID = JOURNALENTRY.ID
        left join dbo.INSTALLMENTSPLITPAYMENT as ISP on ISP.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
          and (
            isnull(ISP.ID, BANKACCOUNTTRANSACTION_EXT.ID) is null
            or (
              JOURNALENTRY.TYPECODE = 0
              and JOURNALENTRY.TRANSACTIONTYPECODE = 1
              and JOURNALENTRY.TRANSACTIONCURRENCYID = ISNULL(ISP.APPLICATIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            or (
              JOURNALENTRY.TYPECODE = 0
              and JOURNALENTRY.TRANSACTIONTYPECODE = 0
              and len(isnull(BANKACCOUNTTRANSACTION.DESCRIPTION, '')) = 0
              and JOURNALENTRY.TRANSACTIONCURRENCYID = isnull(BANKACCOUNT.TRANSACTIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            or (
              JOURNALENTRY.TYPECODE = 1
              and DEPOSITGLDISTRIBUTIONLINK.ID is not null
              and JOURNALENTRY.TRANSACTIONTYPECODE = 0
              and JOURNALENTRY.TRANSACTIONCURRENCYID = isnull(BANKACCOUNT.TRANSACTIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            or (
              JOURNALENTRY.TYPECODE = 1
              and ISP.ID is not null
              and JOURNALENTRY.TRANSACTIONTYPECODE = 1
              and JOURNALENTRY.TRANSACTIONCURRENCYID = isnull(ISP.APPLICATIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            );
      else
        update dbo.JOURNALENTRY
        set COMMENT = case 
            when len(isnull(BANKACCOUNTTRANSACTION.DESCRIPTION, '')) > 0
              and JOURNALENTRY.TRANSACTIONTYPECODE = 0
              then BANKACCOUNTTRANSACTION.DESCRIPTION
            else dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE(FINANCIALTRANSACTIONLINEITEM.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, case REVENUESPLIT_EXT.APPLICATIONCODE
                  when 2
                    then case PLEDGE.POSTSTATUSCODE
                        when 3
                          then 'Pledge Payment for non-bookable pledges'
                        else 'Pledge Payment for bookable pledges'
                        end
                  else REVENUESPLIT_EXT.APPLICATION
                  end)
            end
          ,CHANGEDBYID = @CHANGEAGENTID
          ,DATECHANGED = @CURRENTDATE
        from dbo.JOURNALENTRY
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
        inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.ID
        left join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = FINANCIALTRANSACTION.ID
        left join dbo.BANKACCOUNTTRANSACTION_EXT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION_EXT.ID
        left join dbo.FINANCIALTRANSACTION as BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION_EXT.ID = BANKACCOUNTTRANSACTION.ID
        left join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION_EXT.BANKACCOUNTID
        left join dbo.INSTALLMENTSPLITPAYMENT as ISP on ISP.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
        left join dbo.FINANCIALTRANSACTION as PLEDGE on ISP.PLEDGEID = PLEDGE.ID
        where FINANCIALTRANSACTION.ID = @ID
          and (
            isnull(ISP.ID, BANKACCOUNTTRANSACTION.ID) is null
            or (
              JOURNALENTRY.TYPECODE = 0
              and JOURNALENTRY.TRANSACTIONTYPECODE = 1
              and JOURNALENTRY.TRANSACTIONCURRENCYID = ISNULL(ISP.APPLICATIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            or (
              JOURNALENTRY.TYPECODE = 0
              and JOURNALENTRY.TRANSACTIONTYPECODE = 0
              and len(isnull(BANKACCOUNTTRANSACTION.DESCRIPTION, '')) = 0
              and JOURNALENTRY.TRANSACTIONCURRENCYID = isnull(BANKACCOUNT.TRANSACTIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            or (
              JOURNALENTRY.TYPECODE = 1
              and JOURNALENTRY.TRANSACTIONTYPECODE = 0
              and JOURNALENTRY.TRANSACTIONCURRENCYID = isnull(BANKACCOUNT.TRANSACTIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            or (
              JOURNALENTRY.TYPECODE = 1
              and ISP.ID is not null
              and JOURNALENTRY.TRANSACTIONTYPECODE = 1
              and JOURNALENTRY.TRANSACTIONCURRENCYID = isnull(ISP.APPLICATIONCURRENCYID, JOURNALENTRY.TRANSACTIONCURRENCYID)
              )
            )
          and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
          and FINANCIALTRANSACTION.TYPECODE != 2
          and FINANCIALTRANSACTION.TYPECODE != 3;
    end
  end

  -- update benefits

  declare @TOTALBENEFITS xml;

  set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);
  --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.

  set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS, @TRANSACTIONCURRENCYID, @BASECURRENCYID);

  if dbo.UFN_CHECKDETAIL_BENEFITSCHANGEDFORGL(@ID, @TOTALBENEFITS) = 1 
    begin
      exec dbo.USP_REVENUE_GETBENEFITS_5_UPDATEFROMXML @ID
      ,@TOTALBENEFITS
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@BENEFITSADJUSTMENTID
      ,@ADJUSTMENTPOSTDATE;
    end

  --Error if an exchange rate isn't entered, but the transaction and base currencies are different,

  --  and the payment is for anything other than a donation, other, Miscellaneous, or unapplied MG.

  if @BASEEXCHANGERATEID is null
    and @TRANSACTIONCURRENCYID <> @BASECURRENCYID
  begin
    if exists (
        select 1
        from dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS)
        where not (
            APPLICATIONCODE in (
              0
              ,4
              ,11
              ,100
              )
            )
        )
    begin
      raiserror (
          'BBERR_INVALIDAPPLICATIONSWITHNORATE : Payments without an exchange rate can only be applied to the donation, other, or unapplied matching gift application types.'
          ,13
          ,1
          );

      return 1;
    end
  end

  update dbo.REVENUEPAYMENTMETHOD 
  set 
    PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where 
    ID = @ORIGINALPAYMETHODID

  exec dbo.USP_REVENUE_UPDATEPAYMENTDETAILS 
    @PAYMENTMETHODID = @ORIGINALPAYMETHODID,
    @PAYMENTMETHODCODE = @PAYMENTMETHODCODE, 
    @CHECKDATE = @CHECKDATE, 
    @CHECKNUMBER = @CHECKNUMBER, 
    @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID, 
    @REFERENCEDATE = @REFERENCEDATE, 
    @REFERENCENUMBER = @REFERENCENUMBER, 
    @CARDHOLDERNAME = @CARDHOLDERNAME, 
    @CREDITCARDNUMBER = @CREDITCARDNUMBER, 
    @CREDITTYPECODEID = @CREDITTYPECODEID, 
    @AUTHORIZATIONCODE = @AUTHORIZATIONCODE, 
    @EXPIRESON = @EXPIRESON, 
    @ISSUER = @ISSUER, 
    @NUMBEROFUNITS = @NUMBEROFUNITS, 
    @SYMBOL = @SYMBOL, 
    @MEDIANPRICE = @MEDIANPRICE, 
    @PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID, 
    @GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID, 
    @CHANGEAGENTID = @CHANGEAGENTID,
    @CHANGEDATE = @CURRENTDATE, 
    @KEYALREADYOPEN = @KEYALREADYOPEN,
    @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
    @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
    @LOWPRICE = @LOWPRICE,
    @HIGHPRICE = @HIGHPRICE,
    @SALEDATE = @SALEDATE,
    @SALEAMOUNT = @SALEAMOUNT,
    @BROKERFEE = @BROKERFEE,
    @SALEPOSTDATE = @SALEPOSTDATE,
    @SALEPOSTSTATUSCODE = @SALEPOSTSTATUSCODE,
    @REVENUEAMOUNT = @AMOUNT, 
    @GIFTINKINDITEMNAME = @GIFTINKINDITEMNAME, 
@GIFTINKINDDISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE, 
    @GIFTINKINDNUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS, 
    @GIFTINKINDFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE,
    @DIRECTDEBITISREJECTED = @DIRECTDEBITISREJECTED,
    @BASECURRENCYID = @BASECURRENCYID,
    @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
    @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
    @ORIGINALPAYMENTMETHODCODE = @ORIGINALPAYMENTMETHODCODE,
    @VENDORID = @ORIGINALVENDORID,
    @BBPSTRANSACTIONID = @ORIGINALTRANSACTIONID,
    @SEPAMANDATEID = @SEPAMANDATEID,
    @OVERRIDESAVEDVENDORID = @OVERRIDESAVEDVENDORID,
    @OVERRIDESAVEDBBPSTRANSACTIONID = @OVERRIDESAVEDBBPSTRANSACTIONID,
    @OVERRIDESAVEDSEPAMANDATEID = @OVERRIDESAVEDSEPAMANDATEID;

  -- update streams

  exec dbo.USP_REVENUE_UPDATEREVENUESTREAMS @ID
    ,@CONSTITUENTID
    ,@DATE
    ,@REVENUESTREAMS
    ,@CHANGEAGENTID
    ,@CURRENTDATE
    ,@SPLITSDECLININGGIFTAID output
    ,@ORIGINALGIVENANONYMOUSLY
    ,@GIFTAIDSPONSORSHIPSPLITS output
    ,@PREVIOUSDATE
    ,@PREVIOUSDONOTRECEIPT
    ,@PREVIOUSRECEIPTAMOUNT;

  if @AMOUNT <> (
      select sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
      from dbo.FINANCIALTRANSACTIONLINEITEM
      inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
      where FINANCIALTRANSACTIONID = @ID
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
      )
    raiserror (
        'BBERR_ALLMONEYNOTAPPLIED'
        ,13
        ,1
        );

  if (
      select count(*)
      from dbo.FINANCIALTRANSACTIONLINEITEM
      inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
      where FINANCIALTRANSACTIONID = @ID
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
      ) = 0
    raiserror (
        'BBERR_NOAPPLICATIONS'
        ,13
        ,1
        );
end try

begin catch
  exec dbo.USP_RAISE_ERROR;

  return 1;
end catch

return 0;