UFN_RECURRINGGIFT_GETREVALUEDINSTALLMENTPASTDUEAMOUNTS

            create function dbo.UFN_RECURRINGGIFT_GETREVALUEDINSTALLMENTPASTDUEAMOUNTS
            (
                @RECURRINGGIFTID uniqueidentifier,
                @ASOFDATE datetime
            ) 
            returns @INSTALLMENTS table (
                ID uniqueidentifier,
                TRANSACTIONPASTDUEAMOUNT money,
                BASEPASTDUEAMOUNT money,
                ORGANIZATIONPASTDUEAMOUNT money
            )
            with execute as caller
            as begin
                -- Get currency data in preparation for conversion.


                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASECURRENCYID uniqueidentifier;
                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                declare @DECIMALDIGITSBASECURRENCY int;
                declare @DECIMALDIGITSORGANIZATIONCURRENCY int;

                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

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

                select @DECIMALDIGITSBASECURRENCY = DECIMALDIGITS from dbo.CURRENCY where ID = @BASECURRENCYID;
                select @DECIMALDIGITSORGANIZATIONCURRENCY = DECIMALDIGITS from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;

                -- Get the total balance of the revenue in each currency.


                declare @TRANSACTIONFULLAMOUNT money;
                declare @BASEFULLAMOUNT money;
                declare @ORGANIZATIONFULLAMOUNT money;

                set @TRANSACTIONFULLAMOUNT = dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT(@RECURRINGGIFTID, @ASOFDATE);
                set @BASEFULLAMOUNT = dbo.UFN_RECURRINGGIFT_GETREVALUEDBASEPASTDUEAMOUNTASOF(@RECURRINGGIFTID, @ASOFDATE, 0);
                set @ORGANIZATIONFULLAMOUNT = dbo.UFN_RECURRINGGIFT_GETREVALUEDORGANIZATIONPASTDUEAMOUNTASOF(@RECURRINGGIFTID, @ASOFDATE, 0);

                -- Convert the installment balances proportionally, so they will add up to the total

                -- recurring gift pasta amount due in each currency.


                declare @ITEMLIST xml = (
                    select
                        RECURRINGGIFTINSTALLMENT.ID,
                        dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEASOF(RECURRINGGIFTINSTALLMENT.ID, @ASOFDATE) as AMOUNT
                    from dbo.RECURRINGGIFTINSTALLMENT 
                    where RECURRINGGIFTINSTALLMENT.REVENUEID = @RECURRINGGIFTID
                        and RECURRINGGIFTINSTALLMENT.DATE < @ASOFDATE
                    for xml raw('ITEM'),type,elements,root('ITEMLIST'),BINARY BASE64
                )                

                insert into @INSTALLMENTS (
                    ID, 
                    TRANSACTIONPASTDUEAMOUNT, 
                    BASEPASTDUEAMOUNT, 
                    ORGANIZATIONPASTDUEAMOUNT
                )
                select
                    ITEMLISTCONVERTED.ITEM.value('(ITEM/ID)[1]','uniqueidentifier') as ID,
                    ITEMLISTCONVERTED.ITEM.value('(ITEM/AMOUNT)[1]','money') as TRANSACTIONBALANCE,
                    ITEMLISTCONVERTED.BASEAMOUNT BASEBALANCE,
                    ITEMLISTCONVERTED.ORGANIZATIONAMOUNT ORGANIZATIONBALANCE
                from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(
                        @ITEMLIST,
                        @TRANSACTIONCURRENCYID,
                        @BASECURRENCYID,
                        @ORGANIZATIONCURRENCYID,
                        @TRANSACTIONFULLAMOUNT,
                        @BASEFULLAMOUNT,
                        @DECIMALDIGITSBASECURRENCY,
                        @ORGANIZATIONFULLAMOUNT,
                        @DECIMALDIGITSORGANIZATIONCURRENCY
                    ) ITEMLISTCONVERTED;

                return
            end