USP_DATAFORMTEMPLATE_EDIT_PLANNEDGIFTADDITION_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLANNEDGIFTADDITION_2 (
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @EXPECTEDGIFTAMOUNT money,
  @RECOGNITIONAMOUNT money,
  @GIFTDATE datetime,
  @DESIGNATION xml,
  @ASSETS xml,
  @BASEEXCHANGERATEID uniqueidentifier,
  @EXCHANGERATE decimal(20,8),
  @HADSPOTRATE bit,
  @RATECHANGED bit,
  @CURRENTAPPUSERID uniqueidentifier,
  @DONOTRECEIPT bit
)
as

  set nocount on;

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

  declare @CHANGEDATE datetime
  set @CHANGEDATE = getdate()

  begin try

  ----------------------------------------------------------------------------------------
  --MULTICURRENCY--
  ----------------------------------------------------------------------------------------

  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;

  select
    @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
    @BASECURRENCYID = BASECURRENCYID
  from
    dbo.PLANNEDGIFTADDITION
  where
    ID = @ID;

  --Multicurrency - RobertDi 5/5/10 - If the planned gift 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.PLANNEDGIFT
    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()

    insert into dbo.CURRENCYEXCHANGERATE(
      ID, 
      FROMCURRENCYID,
      TOCURRENCYID,
      RATE,
      ASOFDATE,
      TYPECODE,
      SOURCECODEID,
      ADDEDBYID, 
      CHANGEDBYID, 
      DATEADDED, 
      DATECHANGED
    )
    values(
      @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID,
      @BASECURRENCYID,
      @EXCHANGERATE,
      coalesce(@GIFTDATE, @CHANGEDATE),
      2,
      null,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CHANGEDATE,
      @CHANGEDATE
    );
  end

  -- Convert amount fields from transaction to base.

  declare @BASEEXPECTEDGIFTAMOUNT money;
  declare @BASERECOGNITIONAMOUNT money;

  if @TRANSACTIONCURRENCYID = @BASECURRENCYID
  begin
    set @BASEEXPECTEDGIFTAMOUNT = @EXPECTEDGIFTAMOUNT;
    set @BASERECOGNITIONAMOUNT = @RECOGNITIONAMOUNT;
  end
  else
  begin
    set @BASEEXPECTEDGIFTAMOUNT = dbo.UFN_CURRENCY_CONVERT(@EXPECTEDGIFTAMOUNT, @BASEEXCHANGERATEID);
    set @BASERECOGNITIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@RECOGNITIONAMOUNT, @BASEEXCHANGERATEID);
  end

  -- Convert amount fields from base to organization.

  declare @ORGANIZATIONEXPECTEDGIFTAMOUNT money;
  declare @ORGANIZATIONRECOGNITIONAMOUNT money;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier

  if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
  begin 
    set @ORGANIZATIONEXCHANGERATEID = null;

    set @ORGANIZATIONEXPECTEDGIFTAMOUNT = @BASEEXPECTEDGIFTAMOUNT;
    set @ORGANIZATIONRECOGNITIONAMOUNT = @BASERECOGNITIONAMOUNT;
  end
  else
  begin                                
    --SlyyMu 8/12/10 modified the previous code by RobertDi 5/5/10 to use the new UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE and 
    --UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY function to get the correct rate and values
    set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @GIFTDATE, null, @TRANSACTIONCURRENCYID);

    set @ORGANIZATIONEXPECTEDGIFTAMOUNT = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASEEXPECTEDGIFTAMOUNT, @ORGANIZATIONEXCHANGERATEID, @EXPECTEDGIFTAMOUNT);
    set @ORGANIZATIONRECOGNITIONAMOUNT = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASERECOGNITIONAMOUNT, @ORGANIZATIONEXCHANGERATEID, @RECOGNITIONAMOUNT);
  end

  --Multicurrency - RobertDi 5/5/10 - Process the designations xml to calculate the base and organization amounts and place them in proper nodes.
  set @DESIGNATION = dbo.UFN_PLANNEDGIFTDESIGNATION_CONVERTAMOUNTSINXML(@DESIGNATION,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)

    --Multicurrency - RobertDi 5/5/10 - Process the assets xml to calculate the base and organization amounts and place them in proper nodes.
  set @ASSETS = dbo.UFN_PLANNEDGIFTASSET_CONVERTAMOUNTSINXML(@ASSETS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)

  ----------------------------------------------------------------------------------------
  --END MULTICURRENCY--
  ----------------------------------------------------------------------------------------

  declare @OLDINACTIVEDESIGNATIONTABLE table (
    DESIGNATIONID uniqueidentifier
  );

  declare @DESIGNATIONS table (
    DESIGNATIONID uniqueidentifier
  );

  insert into @OLDINACTIVEDESIGNATIONTABLE(DESIGNATIONID)
    select DESIGNATIONID 
    from dbo.UFN_PLANNEDGIFTADDITION_GETDESIGNATIONS(@ID)

  insert into @DESIGNATIONS 
    select DESIGNATIONID 
    from dbo.UFN_PLANNEDGIFTADDITION_GETDESIGNATIONS_FROMITEMLISTXML(@DESIGNATION);

  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 @OLDINACTIVEDESIGNATIONTABLE)) > 0
    raiserror('Revenue cannot be added to inactive designations.', 13, 2);

  update dbo.PLANNEDGIFTADDITION set
    EXPECTEDGIFTAMOUNT = @BASEEXPECTEDGIFTAMOUNT,
    RECOGNITIONAMOUNT = @BASERECOGNITIONAMOUNT,
    GIFTDATE = @GIFTDATE,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CHANGEDATE,
    BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
    ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
    TRANSACTIONEXPECTEDGIFTAMOUNT = @EXPECTEDGIFTAMOUNT,
    TRANSACTIONRECOGNITIONAMOUNT = @RECOGNITIONAMOUNT,
    ORGANIZATIONEXPECTEDGIFTAMOUNT = @ORGANIZATIONEXPECTEDGIFTAMOUNT,
    ORGANIZATIONRECOGNITIONAMOUNT = @ORGANIZATIONRECOGNITIONAMOUNT,
    DONOTRECEIPT = @DONOTRECEIPT
  where
    ID = @ID;

  declare @DESIGNATIONSCHANGED bit
  set @DESIGNATIONSCHANGED = dbo.UFN_PLANNEDGIFTADDITION_DESIGNATIONSCHANGED(@ID, @DESIGNATION);

  exec dbo.USP_PLANNEDGIFTADDITION_GETDESIGNATIONS_UPDATEFROMXML @ID, @DESIGNATION, @CHANGEAGENTID, @CHANGEDATE;

  exec dbo.USP_PLANNEDGIFTADDITION_GETASSETS_UPDATEFROMXML @ID, @ASSETS, @CHANGEAGENTID, @CHANGEDATE;

  if @DESIGNATIONSCHANGED = 1
  begin
    exec dbo.USP_PLANNEDGIFTADDITION_CLEARANDADDCAMPAIGNS @PLANNEDGIFTADDITIONID = @ID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CHANGEDATE = @CHANGEDATE;                                
  end

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

return 0;