USP_DATAFORMTEMPLATE_EDIT_MGPLEDGE_5

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MGPLEDGE_5
(
  @ID uniqueidentifier, 
  @CHANGEAGENTID uniqueidentifier,
  @ORIGINALGIFTID uniqueidentifier,
  @MATCHINGORGANIZATIONID uniqueidentifier,
  @DATE datetime,
  @AMOUNT money,
  @SPLITS xml,
  @MATCHINGGIFTCONDITIONID uniqueidentifier,
  @RELATIONSHIPID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASEEXCHANGERATEID uniqueidentifier,
  @EXCHANGERATE decimal(20,8),
  @HADSPOTRATE bit,
  @RATECHANGED bit,
  @UPDATERECOGNITIONOPTION tinyint,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
declare @CHANGEDATE datetime;
set @CHANGEDATE = getdate();

declare @OLDDATE datetime;
declare @COUNT int;

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

begin try
  --Multicurrency - AdamBu 5/10/10 - If the revenue previously used a spot rate, but

  --    its rate has changed, store the old rate's ID, so we can remove it later.

  declare @OLDSPOTRATE uniqueidentifier
  if @HADSPOTRATE = 1 and @RATECHANGED = 1
  begin
    select 
      @OLDSPOTRATE = BASEEXCHANGERATEID
    from dbo.REVENUE
    where ID = @ID
  end
  --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,
      @CHANGEDATE,
      @CHANGEDATE
    );
  end

  exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS=@SPLITS, @REVENUEAMOUNT=@AMOUNT, @REVENUEID=@ID, @TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID;

  select 
    @COUNT = count(REVENUE.ID) 
  from dbo.REVENUE
    inner join dbo.REVENUEMATCHINGGIFT RMG on REVENUE.ID = RMG.ID
  where
    REVENUE.CONSTITUENTID = @MATCHINGORGANIZATIONID  and
    RMG.MGSOURCEREVENUEID = @ORIGINALGIFTID;

  --Bug 98537 - AdamBu 5/25/10 - We are not longer enforcing one claim per org per gift.

  --if (@COUNT - 1) > 0 --Use (@COUNT - 1) because the gift being edited will be counted in this.

  --    raiserror('An organization cannot match a single gift more than once.', 13, 1);


  select 
    @OLDDATE = DATE 
  from dbo.REVENUE 
  where ID = @ID;                        

  -- make sure we aren't trying to modify revenue in a locked/closed deposit/bank

  exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;

  --Multicurrency - AdamBu 5/7/10 - Retrieve and calculate the necessary multicurrency values.    

  declare @BASEAMOUNT money;                    
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONAMOUNT money;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

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

  update dbo.REVENUE
    set 
      DATE = @DATE,
      AMOUNT = @BASEAMOUNT,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CHANGEDATE,
      BASECURRENCYID = @BASECURRENCYID,
ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
      ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
      TRANSACTIONAMOUNT = @AMOUNT,
      TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
      -- Matching gift claims shouldn't have receipt amounts.  However, converted data may have it set so it needs to be set to 0 to prevent AMOUNT

      -- from being less than RECEIPTAMOUNT (which would result in a constraint violation).

      RECEIPTAMOUNT = 0 
    where ID = @ID;                        

  update dbo.REVENUEMATCHINGGIFT
    set MATCHINGGIFTCONDITIONID = @MATCHINGGIFTCONDITIONID,
      RELATIONSHIPID = @RELATIONSHIPID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CHANGEDATE
    where ID = @ID;

  if @DATE <> @OLDDATE
    update dbo.REVENUESCHEDULE
      set STARTDATE = @DATE,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CHANGEDATE
      where ID = @ID;

  update dbo.INSTALLMENT
    set AMOUNT = @BASEAMOUNT,
      DATE = @DATE,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CHANGEDATE,
      BASECURRENCYID = @BASECURRENCYID,
      ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
      ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
      TRANSACTIONAMOUNT = @AMOUNT,
      TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID = @BASEEXCHANGERATEID
    where REVENUEID = @ID;

  declare @SPLITSCHANGED bit
  set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS)

  -- Cache current values for recognition updates.

  declare @OLDSPLITS xml = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@ID);
  declare @OLDRECOGNITIONS xml = dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE_TOITEMLISTXML(@ID);

  set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
  if(
    exists(
      select
        1
      from
        dbo.INSTALLMENTSPLITPAYMENT 
        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID  
       where
        INSTALLMENTSPLITPAYMENT.PLEDGEID = @ID
        and REVENUESPLIT.APPLICATIONCODE = 7
        and REVENUESPLIT.DESIGNATIONID 
        not in(
      select 
        REVENUESTREAMS.DESIGNATIONID
      from 
        dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) REVENUESTREAMS
              )
           )
     )
  begin
    raiserror('BBERR_PAYMENTAPPLIED_APPLIEDDESIGNATIONMG', 13, 1);
  end
  exec dbo.USP_REVENUE_GETSPLITS_2_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CHANGEDATE;    

  -- Update revenue recognition based on user selection.

  if @UPDATERECOGNITIONOPTION != 0
    exec dbo.USP_REVENUE_UPDATERECOGNITION @ID, @OLDSPLITS, @UPDATERECOGNITIONOPTION, @CHANGEAGENTID, @CHANGEDATE, @OLDRECOGNITIONS, null, null, 3;

  exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CHANGEDATE;

  if @SPLITSCHANGED = 1
  begin
    --assume one installment

    declare @INSTALLMENTSPLITS xml;
    set @INSTALLMENTSPLITS = (
      select distinct
        INSTALLMENTSPLIT.ID, 
        INSTALLMENT.ID 
        INSTALLMENTID, 
        REVENUESPLIT.DESIGNATIONID, 
        REVENUESPLIT.AMOUNT,
        REVENUESPLIT.BASECURRENCYID,
        REVENUESPLIT.ORGANIZATIONAMOUNT,
        REVENUESPLIT.ORGANIZATIONEXCHANGERATEID,
        REVENUESPLIT.TRANSACTIONAMOUNT,
        REVENUESPLIT.TRANSACTIONCURRENCYID,
        REVENUESPLIT.BASEEXCHANGERATEID,
        REVENUESPLIT.ID as REVENUESPLITID
      from REVENUESPLIT
        inner join INSTALLMENT on INSTALLMENT.REVENUEID = REVENUESPLIT.REVENUEID
        left outer join INSTALLMENTSPLIT 
          on REVENUESPLIT.ID = INSTALLMENTSPLIT.REVENUESPLITID and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
      where REVENUESPLIT.REVENUEID = @ID
      for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64
    );

    exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_2_UPDATEFROMXML @ID, @INSTALLMENTSPLITS, @CHANGEAGENTID, @CHANGEDATE;

    exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CHANGEDATE = @CHANGEDATE
  end

  exec dbo.USP_PLEDGE_VALIDATE_2 @ID, 0;

  --Multicurrency - AdamBu 5/10/10 - If we stored an old spot rate earlier, now is the time to

  --    remove it.

  if @OLDSPOTRATE is not null
  begin
    delete CURRENCYEXCHANGERATE
    where ID=@OLDSPOTRATE
  end

  --Opportunity - Josh.Jones 9/3/15 - Link new splits to opportunity associated with claim

  declare @OPPORTUNITYID uniqueidentifier;
  select 
    @OPPORTUNITYID = REVENUEOPPORTUNITY.OPPORTUNITYID
  from 
    dbo.REVENUEOPPORTUNITY 
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEOPPORTUNITY.ID 
  where 
    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID;

  if @OPPORTUNITYID is not null
  begin
    exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEDATE;

    insert into dbo.REVENUEOPPORTUNITY (
      ID,
      OPPORTUNITYID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
      )
    select 
      FINANCIALTRANSACTIONLINEITEM.ID,
      @OPPORTUNITYID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CHANGEDATE,
      @CHANGEDATE
    from 
      dbo.FINANCIALTRANSACTIONLINEITEM
    where 
      FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
      and not exists (
                      select top 1 REVENUEOPPORTUNITY.ID
                      from REVENUEOPPORTUNITY
                      where REVENUEOPPORTUNITY.ID = FINANCIALTRANSACTIONLINEITEM.ID
                     )
      and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
      and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1;
  end
end try
begin catch
  exec dbo.USP_RAISE_ERROR;
  return 1;
end catch

return 0;