USP_DATAFORMTEMPLATE_PLANNEDGIFT_ADD_SAVE

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

  set nocount on;

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

  --Set PROBATESTATUSCODE to default value of 4 if gift does not require PROBATESTATUSCODE.
  if @VEHICLECODE NOT IN (10,11,13)
    set @PROBATESTATUSCODE = 4  

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

  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 @CONSTITUENTID uniqueidentifier;
  select @CONSTITUENTID=PROSPECTID from dbo.PROSPECTPLAN where ID=@CONTEXT_PROSPECTPLANID;

  declare @CHANGEDATE datetime
  set @CHANGEDATE = getdate()

  if @SUBTYPECODE is null
    set @SUBTYPECODE = 0;

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

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

  declare @DESIGNATIONS table ([DESIGNATIONID] uniqueidentifier);

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

  declare @COMBOISVALID bit = 0;
  set @COMBOISVALID = dbo.UFN_CURRENCYSET_BASEANDTRANSACTIONCURRENCYCOMBOISVALID(@BASECURRENCYID, @TRANSACTIONCURRENCYID)
  if @COMBOISVALID = 0
    raiserror('BBERR_INVALIDCURRENCYIDCOMBINATION', 13, 1);

  --Multicurrency - RobertDi 5/3/10 - Add a spot exchange rate if an existing rate hasn't
  --been selected, the base and transaction currencies are different, and the rate
  --entered isn't zero (which indicates that the user wants to enter the record without a rate).
  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

  -- 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 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 @ORGANIZATIONLIFEINSURANCEFACEVALUE = @BASELIFEINSURANCEFACEVALUE;
  set @ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT = @BASELIFEINSURANCEOUTSTANDINGLOANAMOUNT;

    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


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

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

    exec dbo.USP_PLANNEDGIFT_BENEFICIARIES_ADDFROMXML @ID, @BENEFICIARIES, @CHANGEAGENTID, @CHANGEDATE;
    exec dbo.USP_PLANNEDGIFT_RELATIONSHIPS_ADDFROMXML @ID, @RELATIONSHIPS, @CHANGEAGENTID, @CHANGEDATE;
    exec dbo.USP_PLANNEDGIFT_CLEARANDADDCAMPAIGNS @PLANNEDGIFTID = @ID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CHANGEDATE = @CHANGEDATE;
    exec dbo.USP_PLANNEDGIFT_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID, @CHANGEDATE;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;