USP_PLANNEDGIFTADDITION_UPDATECURRENCIES

          create procedure dbo.USP_PLANNEDGIFTADDITION_UPDATECURRENCIES
          (
            @PLANNEDGIFTADDITIONID uniqueidentifier,
            @TRANSACTIONCURRENCYID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier,
            @CHANGEDATE datetime
          )
          as
          begin

              declare @DESIGNATION xml;
              declare @ASSETS xml;
              declare @BASECURRENCYID uniqueidentifier;
              declare @EXPECTEDGIFTAMOUNT money;
              declare @RECOGNITIONAMOUNT money;
              declare @GIFTDATE datetime;
              declare @HADSPOTRATE bit;
              declare @OLDTRANSACTIONCURRENCYID uniqueidentifier;

              select 
                @BASECURRENCYID = BASECURRENCYID,
                @EXPECTEDGIFTAMOUNT = EXPECTEDGIFTAMOUNT,
                @RECOGNITIONAMOUNT = RECOGNITIONAMOUNT,
                @OLDTRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
                @HADSPOTRATE =  
                  case
                      when CURRENCYEXCHANGERATE.TYPECODE = 2
                          then 1
                      else 0
                  end
              from dbo.PLANNEDGIFTADDITION
              left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = PLANNEDGIFTADDITION.BASEEXCHANGERATEID
              where PLANNEDGIFTADDITION.ID = @PLANNEDGIFTADDITIONID;

              if (@OLDTRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID)
              begin

                set @DESIGNATION = dbo.UFN_PLANNEDGIFTADDITION_GETDESIGNATIONS_TOITEMLISTXML(@PLANNEDGIFTADDITIONID);
                set @ASSETS = dbo.UFN_PLANNEDGIFTADDITION_GETASSETS_TOITEMLISTXML(@PLANNEDGIFTADDITIONID);

                declare @OLDSPOTRATE uniqueidentifier;
                if @HADSPOTRATE = 1
                begin
                  select 
                    @OLDSPOTRATE = BASEEXCHANGERATEID
                  from dbo.PLANNEDGIFT
                  where ID = @PLANNEDGIFTADDITIONID
                end

                -- Convert amount fields from transaction to base.


                declare @BASEEXPECTEDGIFTAMOUNT money;
                declare @BASERECOGNITIONAMOUNT money;

                declare @BASEEXCHANGERATEID uniqueidentifier;
                set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @BASECURRENCYID, @GIFTDATE,1 , null)

                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

                update dbo.PLANNEDGIFTADDITION set
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CHANGEDATE,
                  TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                  BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                  ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                  TRANSACTIONEXPECTEDGIFTAMOUNT = @EXPECTEDGIFTAMOUNT,
                  TRANSACTIONRECOGNITIONAMOUNT = @RECOGNITIONAMOUNT,
                  ORGANIZATIONEXPECTEDGIFTAMOUNT = @ORGANIZATIONEXPECTEDGIFTAMOUNT,
                  ORGANIZATIONRECOGNITIONAMOUNT = @ORGANIZATIONRECOGNITIONAMOUNT
                where
                  ID = @PLANNEDGIFTADDITIONID;

                --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_PLANNEDGIFTADDITION_GETDESIGNATIONS_UPDATEFROMXML @PLANNEDGIFTADDITIONID, @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_PLANNEDGIFTADDITION_GETASSETS_UPDATEFROMXML @PLANNEDGIFTADDITIONID, @ASSETS, @CHANGEAGENTID, @CHANGEDATE;

                delete from dbo.CURRENCYEXCHANGERATE where ID = @OLDSPOTRATE

              end

          end