UFN_GENERATETRANSACTIONS_GETRECURRINGGIFTMULTICURRENCYVALUES

Returns multicurrency values for a recurring gift being processed in the generate transactions process.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@RECURRINGGIFTID	uniqueidentifier	IN
@AMOUNT	money	IN
@PAYMENTDATE	datetime	IN
@PDACCOUNTSYSTEMID	uniqueidentifier	IN
Definition

Copy


            create function dbo.UFN_GENERATETRANSACTIONS_GETRECURRINGGIFTMULTICURRENCYVALUES
            (
                @RECURRINGGIFTID uniqueidentifier,
                @AMOUNT money,
                @PAYMENTDATE datetime,
                @PDACCOUNTSYSTEMID uniqueidentifier
            )
            returns @MULTICURRENCYVALUES table
            (
                AMOUNT money,
                AMOUNTINRECURRINGGIFTCURRENCY money,
                TRANSACTIONCURRENCYID uniqueidentifier,
                BASECURRENCYID uniqueidentifier,
                EXCHANGERATEID uniqueidentifier
            )
            with execute as caller
            as begin
                declare
                    @RECURRINGGIFTTRANSACTIONCURRENCYID uniqueidentifier;

                select @RECURRINGGIFTTRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                from dbo.REVENUE
                where
                    ID = @RECURRINGGIFTID;

                declare
                    @OUTPUTAMOUNT money,
                    @OUTPUTTRANSACTIONCURRENCYID uniqueidentifier,
                    @OUTPUTBASECURRENCYID uniqueidentifier,
                    @OUTPUTBASEEXCHANGERATEID uniqueidentifier;

                select 
                    @OUTPUTBASECURRENCYID = BASECURRENCYID 
                from dbo.PDACCOUNTSYSTEM 
                left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID 
                where 
                    PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID;

                if exists(    select 
                                PDACCOUNTSYSTEM.ID
                            from dbo.PDACCOUNTSYSTEM
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            inner join dbo.CURRENCYSETTRANSACTIONCURRENCY on CURRENCYSET.ID = CURRENCYSETTRANSACTIONCURRENCY.CURRENCYSETID
                            where 
                                CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID = @RECURRINGGIFTTRANSACTIONCURRENCYID
                                and CURRENCYSET.BASECURRENCYID != @RECURRINGGIFTTRANSACTIONCURRENCYID
                                and PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID)
                begin
                    set @OUTPUTAMOUNT = @AMOUNT;
                    set @OUTPUTTRANSACTIONCURRENCYID = @RECURRINGGIFTTRANSACTIONCURRENCYID;
                    set @OUTPUTBASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@RECURRINGGIFTTRANSACTIONCURRENCYID, @OUTPUTBASECURRENCYID, @PAYMENTDATE, 1, null);
                end
                else
                begin
                    select 
                        @OUTPUTAMOUNT = case 
                            when @RECURRINGGIFTTRANSACTIONCURRENCYID <> BASECURRENCYID then dbo.UFN_CURRENCY_CONVERT(@AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@RECURRINGGIFTTRANSACTIONCURRENCYID, BASECURRENCYID, @PAYMENTDATE, 1, null))
                            else @AMOUNT
                        end,
                        @OUTPUTTRANSACTIONCURRENCYID = BASECURRENCYID,
                        @OUTPUTBASEEXCHANGERATEID = null
                    from dbo.PDACCOUNTSYSTEM
                    left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                    where
                        PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID;
                end

                insert into @MULTICURRENCYVALUES (AMOUNT, AMOUNTINRECURRINGGIFTCURRENCY, TRANSACTIONCURRENCYID, BASECURRENCYID, EXCHANGERATEID)
                values (@OUTPUTAMOUNT, @AMOUNT, @OUTPUTTRANSACTIONCURRENCYID, @OUTPUTBASECURRENCYID, @OUTPUTBASEEXCHANGERATEID);                

                return;
            end