USP_RECURRINGGIFT_UPDATEPAYMENT

CREATE procedure dbo.USP_RECURRINGGIFT_UPDATEPAYMENT
(
  @ID uniqueidentifier,
  @APPLIEDAMOUNT money,
  @CHANGEDATE datetime = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @UPDATERECOGNITIONOPTION tinyint = null,
  @BASEAPPLIEDAMOUNT money = null,
  @ORGANIZATIONAPPLIEDAMOUNT money = null,
  @UPDATESOLICITORSOPTION tinyint = null
)
as
  set nocount on;

  -- Get multicurrency values from the revenue.

  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @BASEEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @DATE datetime;
    declare @REVENUEID uniqueidentifier;

    select
    @DATE = cast(REVENUE.DATE as datetime),
    @BASECURRENCYID = CS.BASECURRENCYID,
    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
    @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
    @ORGANIZATIONEXCHANGERATEID = REVENUE.ORGEXCHANGERATEID,
        @REVENUEID = REVENUE.ID
  from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
    inner join dbo.PDACCOUNTSYSTEM PD on PD.ID = REVENUE.PDACCOUNTSYSTEMID
    inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
  where REVENUESPLIT.ID = @ID;

  -- Convert the applied amount into base and organization amounts.

  if @BASEAPPLIEDAMOUNT is null or @ORGANIZATIONAPPLIEDAMOUNT is null
    exec dbo.USP_CURRENCY_GETCURRENCYVALUES
      @APPLIEDAMOUNT,
      null,
      @BASECURRENCYID,
      @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID,
      @BASEAPPLIEDAMOUNT output,
      null,
      @ORGANIZATIONAPPLIEDAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID,
      0;

  --Business units - AdiSa 6/14/10 - Store all existing splits business units in a table.

  declare @BUSINESSUNITS table (DESIGNATIONID uniqueidentifier, OVERRIDEBUSINESSUNITS bit, REASON uniqueidentifier, BUSINESSUNITCODEID uniqueidentifier, RATIO float)
  insert into @BUSINESSUNITS
  select
    REVENUESPLIT_EXT.DESIGNATIONID,
    REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS,
    REVENUESPLIT_EXT.REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
    REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID,
    REVENUESPLITBUSINESSUNIT.AMOUNT/REVENUESPLIT.BASEAMOUNT as RATIO
  from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
    inner join dbo.REVENUESPLITBUSINESSUNIT on REVENUESPLITBUSINESSUNIT.REVENUESPLITID = REVENUESPLIT.ID
  where REVENUESPLIT.ID = @ID and REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS = 1

  declare @ORIGINALREVENUESPLITAMOUNT money;
  select @ORIGINALREVENUESPLITAMOUNT = BASEAMOUNT from dbo.FINANCIALTRANSACTIONLINEITEM where ID = @ID;

    update dbo.FINANCIALTRANSACTIONLINEITEM set
        TRANSACTIONAMOUNT = @APPLIEDAMOUNT
        ,BASEAMOUNT = @BASEAPPLIEDAMOUNT
        ,ORGAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CHANGEDATE
    where ID = @ID
        and (
            TRANSACTIONAMOUNT != @APPLIEDAMOUNT or
            BASEAMOUNT != @BASEAPPLIEDAMOUNT or
            ORGAMOUNT != @ORGANIZATIONAPPLIEDAMOUNT);

  exec dbo.USP_REVENUESPLIT_UPDATESOLICITORS
    @REVENUESPLITID = @ID,
    @CHANGEAGENTID = @CHANGEAGENTID,
    @CHANGEDATE = @CHANGEDATE,
    @UPDATEOPTION = @UPDATESOLICITORSOPTION;

  --Get the application exchange rate ID from the installment split payment, which has already been updated

  with RECURRINGGIFTAPPLICATION
  as
  (
    select
      RECURRINGGIFTACTIVITY.ID,
      case
        when RECURRINGGIFTACTIVITY.APPLICATIONCURRENCYID = @TRANSACTIONCURRENCYID
          then null
        else 
          (
            select top 1
              RECURRINGGIFTINSTALLMENTPAYMENT.APPLICATIONEXCHANGERATEID
            from
              dbo.RECURRINGGIFTINSTALLMENT
              inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
              inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = REVENUESPLIT.FINANCIALTRANSACTIONID
                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
            where
              REVENUESPLIT.ID = @ID
              and RECURRINGGIFTINSTALLMENT.REVENUEID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
                            and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
            order by
              RECURRINGGIFTINSTALLMENT.DATE desc
          )
      end [APPLICATIONEXCHANGERATEID],
      case
        when RECURRINGGIFTACTIVITY.APPLICATIONCURRENCYID = @TRANSACTIONCURRENCYID
          then 1
        else
          0
      end [TRANSACTIONANDAPPLICAITONCURRENCIESARESAME]
    from
      dbo.RECURRINGGIFTACTIVITY
    where
      RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = @ID
  )
  update dbo.RECURRINGGIFTACTIVITY
  set
    AMOUNT =
      case
        when RECURRINGGIFTAPPLICATION.TRANSACTIONANDAPPLICAITONCURRENCIESARESAME = 1
          then @APPLIEDAMOUNT
        else
          dbo.UFN_CURRENCY_CONVERT(@APPLIEDAMOUNT, RECURRINGGIFTAPPLICATION.APPLICATIONEXCHANGERATEID)
      end,
    APPLICATIONEXCHANGERATEID = RECURRINGGIFTAPPLICATION.APPLICATIONEXCHANGERATEID,
    CHANGEDBYID = @CHANGEAGENTID, 
    DATECHANGED = @CHANGEDATE
  from
    dbo.RECURRINGGIFTACTIVITY
    inner join RECURRINGGIFTAPPLICATION on RECURRINGGIFTACTIVITY.ID = RECURRINGGIFTAPPLICATION.ID
  where RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = @ID
  and 
  (
    RECURRINGGIFTACTIVITY.AMOUNT <> 
      case
        when RECURRINGGIFTAPPLICATION.TRANSACTIONANDAPPLICAITONCURRENCIESARESAME = 1
          then @APPLIEDAMOUNT
        else
          dbo.UFN_CURRENCY_CONVERT(@APPLIEDAMOUNT, RECURRINGGIFTAPPLICATION.APPLICATIONEXCHANGERATEID)
      end
    or
    RECURRINGGIFTACTIVITY.APPLICATIONEXCHANGERATEID <> RECURRINGGIFTAPPLICATION.APPLICATIONEXCHANGERATEID
  );

  --Update recognition based on user selection

  exec dbo.USP_REVENUESPLIT_UPDATERECOGNITION @ID, @ORIGINALREVENUESPLITAMOUNT, @UPDATERECOGNITIONOPTION, @CHANGEAGENTID, @CHANGEDATE;

  --Business units - AdiSa 6/12/10 - Re-adjust business unit ratio's with new revenuesplit amount for gift.       

  declare @REVENUESPLITBUSINESSUNITID uniqueidentifier;
  declare BUSINESSUNITS cursor local fast_forward for
  select 
    REVENUESPLITBUSINESSUNIT.ID
  from dbo.REVENUESPLITBUSINESSUNIT
  inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITBUSINESSUNIT.REVENUESPLITID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
  where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID
        and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
  open BUSINESSUNITS;

  begin try
    fetch next from BUSINESSUNITS into @REVENUESPLITBUSINESSUNITID
    while (@@FETCH_STATUS = 0)
    begin
      exec dbo.USP_REVENUESPLITBUSINESSUNIT_DELETEBYID_WITHCHANGEAGENTID @REVENUESPLITBUSINESSUNITID
    fetch next from BUSINESSUNITS into @REVENUESPLITBUSINESSUNITID
    end
    close BUSINESSUNITS;
    deallocate BUSINESSUNITS;
  end try
  begin catch
    close BUSINESSUNITS;
    deallocate BUSINESSUNITS;
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  update dbo.REVENUESPLITBUSINESSUNIT set
    REVENUESPLITBUSINESSUNIT.AMOUNT = REVENUESPLIT.BASEAMOUNT * BU.RATIO
  from dbo.REVENUESPLITBUSINESSUNIT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITBUSINESSUNIT.REVENUESPLITID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
  inner join @BUSINESSUNITS BU on BU.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
  where REVENUESPLIT.ID = @ID and REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID = BU.BUSINESSUNITCODEID
        and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1;