USP_DATAFORMTEMPLATE_PLANNEDGIFT_EDITSAVE_9

CREATE procedure dbo.USP_DATAFORMTEMPLATE_PLANNEDGIFT_EDITSAVE_9 (
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @VEHICLECODE tinyint,
  @SUBTYPECODE tinyint,
  @EXPECTEDGIFTAMOUNT money,
  @ISREVOCABLE bit,
  @ISANONYMOUS bit,
  @EXPECTEDMATURITY dbo.UDT_YEAR,
  @DISCOUNTRATE decimal(6,1),
  @NETPRESENTVALUE money,
  @NETPRESENTVALUEDATE datetime,
  @REMAINDERVALUE money,
  @REMAINDERVALUEDATE datetime,
  @RECOGNITIONAMOUNT money,
  @GIFTDATE datetime,
  @PAYOUTRATE decimal(6,3),
  @PAYOUTAMOUNT money,
  @PAYMENTPERIODSTART datetime,
  @PAYMENTPERIODEND datetime,
  @PAYMENTFREQUENCYCODE tinyint,
  @TERMTYPECODE tinyint,
  @TERMENDDATE datetime,
  @YEARSINTERM tinyint,
  @TRUSTTAXIDNUMBER nvarchar(100),
  @POOLEDINCOMEFUNDCODEID uniqueidentifier,
  @POOLEDINCOMEFUNDUNITS int,
  @POOLEDINCOMEFUNDTOTALUNITS int,
  @LIFEINSURANCEPREMIUM money,
  @LIFEINSURANCEPREMIUMFREQUENCYCODE tinyint,
  @LIFEINSURANCEPREMIUMDUEDATE datetime,
  @STATUSCODE tinyint,
  @DESIGNATION xml,
  @ASSETS xml,
  @BENEFICIARIES xml,
  @RELATIONSHIPS xml,
  @PROBATEDATE datetime,
  @DISCOUNTDATE datetime,
  @REMAINDERMANPERCENT decimal(7,4),
  @PROBATESTATUSCODE tinyint,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITES xml,
  @ISTESTAMENTARY bit,
  @OTHERSUBTYPECODEID uniqueidentifier,
  @ISLIVINGTRUST bit,
  @GIFTVALUEISNOMINAL bit,
  @ISCONTINGENT bit,
  @ORGISPOLICY bit,
  @ORGISBENEFICIARY bit,
  @TRUSTHELDOUTSIDE bit,
  @TOTALPAYOUT money,
  @REALIZEDVALUE money,
  @BASECURRENCYID uniqueidentifier,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASEEXCHANGERATEID uniqueidentifier,
  @EXCHANGERATE decimal(20,8),
  @HADSPOTRATE bit,
  @RATECHANGED bit,
  @TRIGGERDATE datetime,
  @ISFLIP bit,
  @TRIGGEREVENTCODEID uniqueidentifier,
  @EXCL_RATIO decimal(6,3),
  @EXCL_EXPDATE datetime,
  @DONOTRECEIPT bit,
  @LIFEINSURANCEPOLICYNUMBER nvarchar(50),
  @LIFEINSURANCEDATEISSUED datetime,
  @LIFEINSURANCEFACEVALUE money,
  @LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION bit,
  @LIFEINSURANCEISLOANALLOWED bit,
  @LIFEINSURANCEOUTSTANDINGLOANAMOUNT money
 ) as

  set nocount on;

 --Set PROBATESTATUSCODE to default value of 4 if gift does not require PROBATESTATUSCODE.

  if @VEHICLECODE NOT IN (10,11,13)
    set @PROBATESTATUSCODE = 4  

  if @SITES is null begin 
    if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 begin
      raiserror('Site is required.',13,1)
      return
    end
  end

  exec dbo.USP_PLANNEDGIFTSITE_VALIDATESITES @SITES;

  declare @HASADDITIONS bit;
  if exists(select top 1 ID from dbo.PLANNEDGIFTADDITION where PLANNEDGIFTID = @ID)
    set @HASADDITIONS = 1

  if (@VEHICLECODE not in (1,3,5,6,10,11,12,13,14) and @HASADDITIONS = 1)
  begin
    raiserror('BBERR_PLANNEDGIFT_VEHICLECODE_VALIDWITHADDITIONS',13,1)
    return
  end

  if @VEHICLECODE <> 6 
    begin
      select
        @LIFEINSURANCEPOLICYNUMBER = '',
        @LIFEINSURANCEDATEISSUED = null,
        @LIFEINSURANCEFACEVALUE = 0,
        @LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION = 0,
        @LIFEINSURANCEISLOANALLOWED = 0,
        @LIFEINSURANCEOUTSTANDINGLOANAMOUNT = 0
      end
  else
    if @LIFEINSURANCEISLOANALLOWED = 0
      set @LIFEINSURANCEOUTSTANDINGLOANAMOUNT = 0

  if @ID is null
    set @ID = newid();

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

  if @SUBTYPECODE is null
    set @SUBTYPECODE = 0;

          if @EXCL_RATIO > 1
    set @EXCL_RATIO = @EXCL_RATIO/100

  declare @CHANGEDATE datetime
  set @CHANGEDATE = getdate()

  -- Don't allow the transaction currency to be modified if the planned gift

  -- has been added as revenue.

  declare @ADDEDASREVENUE bit = 0;
  declare @OLDTRANSACTIONCURRENCYID uniqueidentifier;
              declare @OLDISREVOCABLE bit;

  select @ADDEDASREVENUE = 1 from dbo.PLANNEDGIFTREVENUE where ID = @ID;
  select @OLDTRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID, @OLDISREVOCABLE = ISREVOCABLE from dbo.PLANNEDGIFT where ID = @ID;

  if @ADDEDASREVENUE = 1 and (@OLDTRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID and (@OLDTRANSACTIONCURRENCYID is not null or @TRANSACTIONCURRENCYID is not null))
  begin
    raiserror('The transaction currency of this gift cannot be modified because it has been added as revenue.',13,1)
    return
  end


  if @ADDEDASREVENUE = 1 and (@ISREVOCABLE <> @OLDISREVOCABLE)
  begin
    raiserror('The revocable status of this gift cannot be modified because it has been added as revenue.',13,1)
    return
  end


  begin try

    --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, cast(convert(varchar,@CHANGEDATE,110) as datetime)),
        2,
        null,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CHANGEDATE,
        @CHANGEDATE
      );
    end

    declare @OLDINACTIVEDESIGNATIONTABLE table (
      DESIGNATIONID uniqueidentifier
    );

    declare @DESIGNATIONS table (
      DESIGNATIONID uniqueidentifier
    );

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

    insert into @DESIGNATIONS 
      select DESIGNATIONID 
      from dbo.UFN_PLANNEDGIFT_DESIGNATION_2_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);


    select
      @REALIZEDVALUE =    case dbo.UFN_PLANNEDGIFT_AUTOCALCULATE_REALIZEDAMOUNT(@ID)
                    when 0 then @REALIZEDVALUE
                    when 1 then TRANSACTIONREALIZEDVALUE
                end
    from
      dbo.PLANNEDGIFT
    where ID = @ID


    -- Convert amount fields from transaction to base.


    declare @BASEEXPECTEDGIFTAMOUNT money;
    declare @BASERECOGNITIONAMOUNT money;
    declare @BASENETPRESENTVALUE money;
    declare @BASEREMAINDERVALUE money;
    declare @BASEPAYOUTAMOUNT money;
    declare @BASELIFEINSURANCEPREMIUM money;
    declare @BASETOTALPAYOUT money;
    declare @BASEREALIZEDVALUE money;
    declare @BASELIFEINSURANCEFACEVALUE money;
    declare @BASELIFEINSURANCEOUTSTANDINGLOANAMOUNT money;

    if @TRANSACTIONCURRENCYID = @BASECURRENCYID
    begin
      set @BASEEXPECTEDGIFTAMOUNT = @EXPECTEDGIFTAMOUNT;
      set @BASERECOGNITIONAMOUNT = @RECOGNITIONAMOUNT;
      set @BASENETPRESENTVALUE = @NETPRESENTVALUE;
      set @BASEREMAINDERVALUE = @REMAINDERVALUE;
      set @BASEPAYOUTAMOUNT = @PAYOUTAMOUNT;
      set @BASELIFEINSURANCEPREMIUM = @LIFEINSURANCEPREMIUM;
      set @BASETOTALPAYOUT = @TOTALPAYOUT;
      set @BASEREALIZEDVALUE = @REALIZEDVALUE;
      set @BASELIFEINSURANCEFACEVALUE = @LIFEINSURANCEFACEVALUE;
      set @BASELIFEINSURANCEOUTSTANDINGLOANAMOUNT = @LIFEINSURANCEOUTSTANDINGLOANAMOUNT;
    end
    else
    begin
      set @BASEEXPECTEDGIFTAMOUNT = dbo.UFN_CURRENCY_CONVERT(@EXPECTEDGIFTAMOUNT, @BASEEXCHANGERATEID);
      set @BASERECOGNITIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@RECOGNITIONAMOUNT, @BASEEXCHANGERATEID);
      set @BASENETPRESENTVALUE = dbo.UFN_CURRENCY_CONVERT(@NETPRESENTVALUE, @BASEEXCHANGERATEID);
      set @BASEREMAINDERVALUE = dbo.UFN_CURRENCY_CONVERT(@REMAINDERVALUE, @BASEEXCHANGERATEID);
      set @BASEPAYOUTAMOUNT = dbo.UFN_CURRENCY_CONVERT(@PAYOUTAMOUNT, @BASEEXCHANGERATEID);
      set @BASELIFEINSURANCEPREMIUM = dbo.UFN_CURRENCY_CONVERT(@LIFEINSURANCEPREMIUM, @BASEEXCHANGERATEID);
      set @BASETOTALPAYOUT = dbo.UFN_CURRENCY_CONVERT(@TOTALPAYOUT, @BASEEXCHANGERATEID);
      set @BASEREALIZEDVALUE = dbo.UFN_CURRENCY_CONVERT(@REALIZEDVALUE, @BASEEXCHANGERATEID);
                    set @BASELIFEINSURANCEFACEVALUE = dbo.UFN_CURRENCY_CONVERT(@LIFEINSURANCEFACEVALUE, @BASEEXCHANGERATEID);
                set @BASELIFEINSURANCEOUTSTANDINGLOANAMOUNT = dbo.UFN_CURRENCY_CONVERT(@LIFEINSURANCEOUTSTANDINGLOANAMOUNT, @BASEEXCHANGERATEID);
    end

    -- Convert amount fields from base to organization.


    declare @ORGANIZATIONEXPECTEDGIFTAMOUNT money;
    declare @ORGANIZATIONRECOGNITIONAMOUNT money;
    declare @ORGANIZATIONNETPRESENTVALUE money;
    declare @ORGANIZATIONREMAINDERVALUE money;
    declare @ORGANIZATIONPAYOUTAMOUNT money;
    declare @ORGANIZATIONLIFEINSURANCEPREMIUM money;
    declare @ORGANIZATIONTOTALPAYOUT money;
    declare @ORGANIZATIONREALIZEDVALUE money;
    declare @ORGANIZATIONLIFEINSURANCEFACEVALUE money;
    declare @ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT money;

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

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

      set @ORGANIZATIONEXPECTEDGIFTAMOUNT = @BASEEXPECTEDGIFTAMOUNT;
      set @ORGANIZATIONRECOGNITIONAMOUNT = @BASERECOGNITIONAMOUNT;
      set @ORGANIZATIONNETPRESENTVALUE = @BASENETPRESENTVALUE;
      set @ORGANIZATIONREMAINDERVALUE = @BASEREMAINDERVALUE;
      set @ORGANIZATIONPAYOUTAMOUNT = @BASEPAYOUTAMOUNT;
      set @ORGANIZATIONLIFEINSURANCEPREMIUM = @BASELIFEINSURANCEPREMIUM;
      set @ORGANIZATIONTOTALPAYOUT = @BASETOTALPAYOUT;
      set @ORGANIZATIONREALIZEDVALUE = @BASEREALIZEDVALUE;
      set @ORGANIZATIONLIFEINSURANCEFACEVALUE = @BASELIFEINSURANCEFACEVALUE;
      set @ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT = @BASELIFEINSURANCEOUTSTANDINGLOANAMOUNT;
    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);
      set @ORGANIZATIONNETPRESENTVALUE = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASENETPRESENTVALUE, @ORGANIZATIONEXCHANGERATEID, @NETPRESENTVALUE);
      set @ORGANIZATIONREMAINDERVALUE = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASEREMAINDERVALUE, @ORGANIZATIONEXCHANGERATEID, @REMAINDERVALUE);
      set @ORGANIZATIONPAYOUTAMOUNT = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASEPAYOUTAMOUNT, @ORGANIZATIONEXCHANGERATEID, @PAYOUTAMOUNT);
      set @ORGANIZATIONLIFEINSURANCEPREMIUM = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASELIFEINSURANCEPREMIUM, @ORGANIZATIONEXCHANGERATEID, @LIFEINSURANCEPREMIUM);
      set @ORGANIZATIONTOTALPAYOUT = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASETOTALPAYOUT, @ORGANIZATIONEXCHANGERATEID, @TOTALPAYOUT);
      set @ORGANIZATIONREALIZEDVALUE = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASEREALIZEDVALUE, @ORGANIZATIONEXCHANGERATEID, @REALIZEDVALUE);
      set @ORGANIZATIONLIFEINSURANCEFACEVALUE = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASELIFEINSURANCEFACEVALUE, @ORGANIZATIONEXCHANGERATEID, @REALIZEDVALUE);
      set @ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASELIFEINSURANCEOUTSTANDINGLOANAMOUNT, @ORGANIZATIONEXCHANGERATEID, @REALIZEDVALUE);
    end

    update dbo.PLANNEDGIFT set
      VEHICLECODE = @VEHICLECODE,
      SUBTYPECODE = @SUBTYPECODE,
      EXPECTEDGIFTAMOUNT = @BASEEXPECTEDGIFTAMOUNT,
      ISREVOCABLE = @ISREVOCABLE,
      ISANONYMOUS = @ISANONYMOUS,
      EXPECTEDMATURITY = @EXPECTEDMATURITY,
      DISCOUNTRATE = @DISCOUNTRATE/100,
      NETPRESENTVALUE = @BASENETPRESENTVALUE,
      NETPRESENTVALUEDATE = @NETPRESENTVALUEDATE,
      REMAINDERVALUE = @BASEREMAINDERVALUE,
      REMAINDERVALUEDATE = @REMAINDERVALUEDATE,
      RECOGNITIONAMOUNT = @BASERECOGNITIONAMOUNT,
      GIFTDATE = @GIFTDATE,
      PAYOUTRATE = @PAYOUTRATE/100,
      PAYOUTAMOUNT = @BASEPAYOUTAMOUNT,
      PAYMENTPERIODSTART = @PAYMENTPERIODSTART,
      PAYMENTPERIODEND = @PAYMENTPERIODEND,
      PAYMENTFREQUENCYCODE = @PAYMENTFREQUENCYCODE,
      TERMTYPECODE = @TERMTYPECODE,
      TERMENDDATE = @TERMENDDATE,
      YEARSINTERM = @YEARSINTERM,
      TRUSTTAXIDNUMBER = @TRUSTTAXIDNUMBER,
      POOLEDINCOMEFUNDCODEID = @POOLEDINCOMEFUNDCODEID,
      POOLEDINCOMEFUNDUNITS = @POOLEDINCOMEFUNDUNITS,
      POOLEDINCOMEFUNDTOTALUNITS = @POOLEDINCOMEFUNDTOTALUNITS,
      LIFEINSURANCEPREMIUM = @BASELIFEINSURANCEPREMIUM,
      LIFEINSURANCEPREMIUMFREQUENCYCODE = @LIFEINSURANCEPREMIUMFREQUENCYCODE,
      LIFEINSURANCEPREMIUMDUEDATE = @LIFEINSURANCEPREMIUMDUEDATE,
      STATUSCODE = @STATUSCODE,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CHANGEDATE,
      PROBATEDATE = @PROBATEDATE,
      DISCOUNTDATE = @DISCOUNTDATE,
      REMAINDERMANPERCENT = @REMAINDERMANPERCENT/100.0,
      PROBATESTATUSCODE = @PROBATESTATUSCODE,
      ISTESTAMENTARY = @ISTESTAMENTARY,
      OTHERSUBTYPECODEID = @OTHERSUBTYPECODEID,
      ISLIVINGTRUST = @ISLIVINGTRUST,
      GIFTVALUEISNOMINAL = @GIFTVALUEISNOMINAL,
      ISCONTINGENT = @ISCONTINGENT,
      ORGISPOLICY = @ORGISPOLICY,
      ORGISBENEFICIARY = @ORGISBENEFICIARY,
      TRUSTHELDOUTSIDE = @TRUSTHELDOUTSIDE,
      TOTALPAYOUT = @BASETOTALPAYOUT,
      REALIZEDVALUE = @BASEREALIZEDVALUE,
      TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
      BASECURRENCYID = @BASECURRENCYID,
      BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
      ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
      TRANSACTIONEXPECTEDGIFTAMOUNT = @EXPECTEDGIFTAMOUNT,
      TRANSACTIONRECOGNITIONAMOUNT = @RECOGNITIONAMOUNT,
      TRANSACTIONNETPRESENTVALUE = @NETPRESENTVALUE,
      TRANSACTIONREMAINDERVALUE = @REMAINDERVALUE,
      TRANSACTIONPAYOUTAMOUNT = @PAYOUTAMOUNT,
      TRANSACTIONLIFEINSURANCEPREMIUM = @LIFEINSURANCEPREMIUM,
      TRANSACTIONTOTALPAYOUT = @TOTALPAYOUT,
      TRANSACTIONREALIZEDVALUE = @REALIZEDVALUE,
      ORGANIZATIONEXPECTEDGIFTAMOUNT = @ORGANIZATIONEXPECTEDGIFTAMOUNT,
      ORGANIZATIONRECOGNITIONAMOUNT = @ORGANIZATIONRECOGNITIONAMOUNT,
      ORGANIZATIONNETPRESENTVALUE = @ORGANIZATIONNETPRESENTVALUE,
      ORGANIZATIONREMAINDERVALUE = @ORGANIZATIONREMAINDERVALUE,
      ORGANIZATIONPAYOUTAMOUNT = @ORGANIZATIONPAYOUTAMOUNT,
      ORGANIZATIONLIFEINSURANCEPREMIUM = @ORGANIZATIONLIFEINSURANCEPREMIUM,
      ORGANIZATIONTOTALPAYOUT = @ORGANIZATIONTOTALPAYOUT,
      ORGANIZATIONREALIZEDVALUE = @ORGANIZATIONREALIZEDVALUE,
      TRIGGERDATE = @TRIGGERDATE,
      ISFLIP = @ISFLIP,
      TRIGGEREVENTCODEID = @TRIGGEREVENTCODEID,
      EXCL_RATIO = @EXCL_RATIO,
      EXCL_EXPDATE = @EXCL_EXPDATE,
      DONOTRECEIPT = @DONOTRECEIPT,
      LIFEINSURANCEPOLICYNUMBER = @LIFEINSURANCEPOLICYNUMBER,
      LIFEINSURANCEDATEISSUED = @LIFEINSURANCEDATEISSUED,
      LIFEINSURANCEFACEVALUE = @BASELIFEINSURANCEFACEVALUE,
      TRANSACTIONLIFEINSURANCEFACEVALUE = @LIFEINSURANCEFACEVALUE,
      ORGANIZATIONLIFEINSURANCEFACEVALUE = @ORGANIZATIONLIFEINSURANCEFACEVALUE,
      LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION = @LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION,
      LIFEINSURANCEISLOANALLOWED = @LIFEINSURANCEISLOANALLOWED,
      LIFEINSURANCEOUTSTANDINGLOANAMOUNT = @BASELIFEINSURANCEOUTSTANDINGLOANAMOUNT,
      TRANSACTIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT = @LIFEINSURANCEOUTSTANDINGLOANAMOUNT,
      ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT = @ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT
    where
      ID = @ID;

    --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)

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

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

    --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)
    exec dbo.USP_PLANNEDGIFT_ASSETS_2_UPDATEFROMXML @ID, @ASSETS, @CHANGEAGENTID, @CHANGEDATE;


    exec dbo.USP_PLANNEDGIFT_BENEFICIARIES_UPDATEFROMXML @ID, @BENEFICIARIES, @CHANGEAGENTID, @CHANGEDATE;
    exec dbo.USP_PLANNEDGIFT_RELATIONSHIPS_UPDATEFROMXML @ID, @RELATIONSHIPS, @CHANGEAGENTID, @CHANGEDATE;

    if @DESIGNATIONSCHANGED = 1
    begin
      exec dbo.USP_PLANNEDGIFT_CLEARANDADDCAMPAIGNS @PLANNEDGIFTID = @ID,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @CHANGEDATE = @CHANGEDATE;                                
    end

    exec dbo.USP_PLANNEDGIFT_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID, @CHANGEDATE;

    --Receipting - force associated additions to do not receipt when the planned gift is no longer receiptable

    if @ISREVOCABLE = 1 or @TRUSTHELDOUTSIDE = 1 or dbo.UFN_PLANNEDGIFT_ISRECEIPTABLE(@VEHICLECODE) = 0
      update dbo.PLANNEDGIFTADDITION set DONOTRECEIPT = 1 where PLANNEDGIFTADDITION.PLANNEDGIFTID = @ID 

    --Multicurrency - KeithAb 9/24/10 - Update planned gift addition transaction currency when we have updated the transaction

    --currency on its parent planned gift

    declare @PLANNEDGIFTADDITIONID uniqueidentifier;
    declare PLANNEDGIFTADDITIONCURSOR cursor local fast_forward for
      select
        ID
      from
        dbo.PLANNEDGIFTADDITION
      where
        PLANNEDGIFTADDITION.PLANNEDGIFTID = @ID;
    open PLANNEDGIFTADDITIONCURSOR
    fetch next from PLANNEDGIFTADDITIONCURSOR into @PLANNEDGIFTADDITIONID;
    while @@FETCH_STATUS = 0
    begin
      exec dbo.USP_PLANNEDGIFTADDITION_UPDATECURRENCIES @PLANNEDGIFTADDITIONID, @TRANSACTIONCURRENCYID, @CHANGEAGENTID, @CHANGEDATE;
      fetch next from PLANNEDGIFTADDITIONCURSOR into @PLANNEDGIFTADDITIONID;
    end
    close PLANNEDGIFTADDITIONCURSOR
    deallocate PLANNEDGIFTADDITIONCURSOR

    if @OLDSPOTRATE is not null
      exec dbo.USP_CURRENCYEXCHANGERATE_DELETEBYID_WITHCHANGEAGENTID @OLDSPOTRATE, @CHANGEAGENTID;

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

  return 0;