USP_EVENT_UPDATEPAYMENT

CREATE procedure dbo.USP_EVENT_UPDATEPAYMENT
(
  @ID uniqueidentifier,
  @AMOUNT money,
  @CHANGEDATE datetime = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @PREVIOUSDATE datetime = null,
  @UPDATERECOGNITIONOPTION tinyint = null,
  @BASEAPPLIEDAMOUNT money = null,
  @ORGANIZATIONAPPLIEDAMOUNT money = null,
  @UPDATESOLICITORSOPTION tinyint = null,
  @CATEGORYCODEID uniqueidentifier = 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 @ORIGINALAPPLIEDAMOUNTCONVERTED money;
  declare @APPLICATIONID uniqueidentifier;

  select
    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
    @BASECURRENCYID = CS.BASECURRENCYID,
    @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
    @ORGANIZATIONEXCHANGERATEID = REVENUE.ORGEXCHANGERATEID,
    @DATE = cast(REVENUE.DATE as datetime),
    @ORIGINALAPPLIEDAMOUNTCONVERTED = EVENTREGISTRANTPAYMENT.AMOUNT,
    @APPLICATIONID = EVENTREGISTRANTPAYMENT.REGISTRANTID
  from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
    inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID
    inner join dbo.PDACCOUNTSYSTEM PD on PD.ID = REVENUE.PDACCOUNTSYSTEMID
    inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
  inner join dbo.EVENTREGISTRANTPAYMENT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
  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
      @AMOUNT,
      null,
      @BASECURRENCYID,
      @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID,
      @BASEAPPLIEDAMOUNT output,
      null,
      @ORGANIZATIONAPPLIEDAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID,
      0;

    declare @BUSINESSUNITS table (FINANCIALTRANSACTIONLINEITEMID uniqueidentifier, DESIGNATIONID uniqueidentifier, OVERRIDEBUSINESSUNITS bit, REASON uniqueidentifier, BUSINESSUNITCODEID uniqueidentifier, RATIO float)
    insert into @BUSINESSUNITS
    select
        REVENUESPLIT.ID,
        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 @REVENUEID uniqueidentifier;
  select @REVENUEID = FINANCIALTRANSACTIONID from dbo.FINANCIALTRANSACTIONLINEITEM where ID=@ID;
  declare @ORIGINALREVENUESPLITAMOUNT money;
  select @ORIGINALREVENUESPLITAMOUNT = BASEAMOUNT from dbo.FINANCIALTRANSACTIONLINEITEM where ID = @ID;

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

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

  declare @APPLICATIONCURRENCYID uniqueidentifier;
  declare @PREVIOUSAPPLICATIONEXCHANGERATEID uniqueidentifier;
  declare @APPLICATIONEXCHANGERATEID uniqueidentifier;
  declare @APPLIEDAMOUNTCONVERTED money;

  select
    @APPLICATIONCURRENCYID = EVENTREGISTRANTPAYMENT.APPLICATIONCURRENCYID,
    @PREVIOUSAPPLICATIONEXCHANGERATEID = EVENTREGISTRANTPAYMENT.APPLICATIONEXCHANGERATEID
  from
    dbo.EVENTREGISTRANTPAYMENT
  where
    EVENTREGISTRANTPAYMENT.PAYMENTID = @ID;

  exec dbo.USP_REVENUE_GETUPDATEDAPPLICATIONEXCHANGERATE
    @PREVIOUSAPPLICATIONEXCHANGERATEID = @PREVIOUSAPPLICATIONEXCHANGERATEID,
    @PREVIOUSDATE = @PREVIOUSDATE,
    @APPLIEDAMOUNT = @AMOUNT,
    @DATE = @DATE,
    @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
    @BASECURRENCYID = @BASECURRENCYID,
    @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
    @APPLICATIONCURRENCYID = @APPLICATIONCURRENCYID,
    @APPLICATIONEXCHANGERATEID = @APPLICATIONEXCHANGERATEID output,
    @APPLIEDAMOUNTCONVERTED = @APPLIEDAMOUNTCONVERTED output;

  update dbo.EVENTREGISTRANTPAYMENT
  set
    AMOUNT = @APPLIEDAMOUNTCONVERTED,
    APPLICATIONEXCHANGERATEID = @APPLICATIONEXCHANGERATEID,
    CHANGEDBYID = @CHANGEAGENTID, 
    DATECHANGED = @CHANGEDATE
  where
    PAYMENTID = @ID
    and
    (
      AMOUNT <> @APPLIEDAMOUNTCONVERTED
      or (APPLICATIONEXCHANGERATEID is null and @APPLICATIONEXCHANGERATEID is not null)
      or (APPLICATIONEXCHANGERATEID is not null and @APPLICATIONEXCHANGERATEID is null)
      or (APPLICATIONEXCHANGERATEID is not null and @APPLICATIONEXCHANGERATEID is not null and APPLICATIONEXCHANGERATEID <> @APPLICATIONEXCHANGERATEID)
    );

  /* Update recognitions based on user selection */
  exec dbo.USP_REVENUESPLIT_UPDATERECOGNITION @ID, @ORIGINALREVENUESPLITAMOUNT, @UPDATERECOGNITIONOPTION, @CHANGEAGENTID, @CHANGEDATE;

    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.FINANCIALTRANSACTIONLINEITEMID = REVENUESPLIT_EXT.ID
    where 
        REVENUESPLIT.ID = @ID 
        and REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID = BU.BUSINESSUNITCODEID
        and REVENUESPLIT.DELETEDON is null 
        and REVENUESPLIT.TYPECODE != 1;

   exec dbo.USP_REVENUECATEGORY_ADDEDIT @ID, @CATEGORYCODEID, @CHANGEAGENTID;