USP_DATAFORMTEMPLATE_ADD_PLANNEDGIFTADDITION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PLANNEDGIFTADDITION
(
  @ID uniqueidentifier = null output,
  @PLANNEDGIFTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @EXPECTEDGIFTAMOUNT money = 0,
  @RECOGNITIONAMOUNT money = 0,
  @GIFTDATE datetime = null,
  @DESIGNATION xml = null,
  @ASSETS xml = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @DONOTRECEIPT bit = 1
)
as

set nocount on;

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

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.PLANNEDGIFT
where
  ID = @PLANNEDGIFTID;

--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, @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 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

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

declare @DESIGNATIONS table ([DESIGNATIONID] uniqueidentifier);

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

insert into dbo.PLANNEDGIFTADDITION(
  ID,
  PLANNEDGIFTID,
  EXPECTEDGIFTAMOUNT,
  RECOGNITIONAMOUNT,
  GIFTDATE,
  BASECURRENCYID,
  TRANSACTIONCURRENCYID,
  BASEEXCHANGERATEID,
  ORGANIZATIONEXCHANGERATEID,
  TRANSACTIONEXPECTEDGIFTAMOUNT,
  TRANSACTIONRECOGNITIONAMOUNT,
  ORGANIZATIONEXPECTEDGIFTAMOUNT,
  ORGANIZATIONRECOGNITIONAMOUNT,
  ADDEDBYID, 
  CHANGEDBYID,
  DATEADDED,                      
  DATECHANGED,
  DONOTRECEIPT
)values(
  @ID,
  @PLANNEDGIFTID,
  @BASEEXPECTEDGIFTAMOUNT,
  @BASERECOGNITIONAMOUNT,
  @GIFTDATE,
  @BASECURRENCYID,
  @TRANSACTIONCURRENCYID,
  @BASEEXCHANGERATEID,
  @ORGANIZATIONEXCHANGERATEID,
  @EXPECTEDGIFTAMOUNT,
  @RECOGNITIONAMOUNT,
  @ORGANIZATIONEXPECTEDGIFTAMOUNT,
  @ORGANIZATIONRECOGNITIONAMOUNT,
  @CHANGEAGENTID, 
  @CHANGEAGENTID, 
  @CHANGEDATE,
  @CHANGEDATE,
  @DONOTRECEIPT
)

--Multicurrency - Process the designations xml to calculate the base and organization amounts and place them in proper nodes.
--This function is generic enough to use with planned gift additions.
set @DESIGNATION = dbo.UFN_PLANNEDGIFTDESIGNATION_CONVERTAMOUNTSINXML(@DESIGNATION,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
exec dbo.USP_PLANNEDGIFTADDITION_GETDESIGNATIONS_ADDFROMXML @ID, @DESIGNATION, @CHANGEAGENTID, @CHANGEDATE;

--Multicurrency - 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_PLANNEDGIFTADDITION_GETASSETS_ADDFROMXML @ID, @ASSETS, @CHANGEAGENTID, @CHANGEDATE;

exec dbo.USP_PLANNEDGIFTADDITION_CLEARANDADDCAMPAIGNS @PLANNEDGIFTADDITIONID = @ID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE
end try

begin catch
  exec dbo.USP_RAISE_ERROR
  return 1
end catch

return 0