UFN_GLDISTRIBUTION_AUCTIONPURCHASE_CONVERTAMOUNTSINXML

            CREATE function dbo.UFN_GLDISTRIBUTION_AUCTIONPURCHASE_CONVERTAMOUNTSINXML
            (
                @GLDISTRIBUTION xml,
                @REVENUEID uniqueidentifier
            )
            returns xml
            as begin

                declare @DATE datetime = getdate();

                declare @NEWDISTRIBUTIONS xml;

                declare @TEMPDISTRIBUTIONS table
                (
                    ID uniqueidentifier,
                    ACCOUNT nvarchar(100),
                    AMOUNT money,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier, 
                    PROJECT nvarchar(100), 
                    REFERENCE nvarchar(100), 
                    TRANSACTIONCURRENCYID uniqueidentifier, 
                    TRANSACTIONTYPECODE tinyint,
                    BASECURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                    TRANSACTIONAMOUNT money,
                    ORGANIZATIONAMOUNT money
                )

                insert into @TEMPDISTRIBUTIONS
                (
                    ID,
                    ACCOUNT,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    PROJECT,
                    REFERENCE,
                    TRANSACTIONAMOUNT,
                    TRANSACTIONCURRENCYID,
                    TRANSACTIONTYPECODE,
                    BASECURRENCYID,
                    BASEEXCHANGERATEID,
                    ORGANIZATIONEXCHANGERATEID
                )
                select
                    PURCHASEDISTRIBUTIONS.ID,
                    PURCHASEDISTRIBUTIONS.ACCOUNT,
                    PURCHASEDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    PURCHASEDISTRIBUTIONS.PROJECT,
                    PURCHASEDISTRIBUTIONS.REFERENCE,
                    PURCHASEDISTRIBUTIONS.AMOUNT,
                    PURCHASEDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                    PURCHASEDISTRIBUTIONS.TRANSACTIONTYPECODE,
                    V.BASECURRENCYID,
                    FT.BASEEXCHANGERATEID,
                    FT.ORGEXCHANGERATEID
                from
                    dbo.UFN_REVENUE_GETAUCTIONPURCHASEGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) PURCHASEDISTRIBUTIONS
                    inner join dbo.JOURNALENTRY JE on JE.ID = PURCHASEDISTRIBUTIONS.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
                    inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V on V.FINANCIALTRANSACTIONID = FT.ID


                -- Grab this information for new distribution rows

                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASECURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

                select top 1
                    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                    @BASECURRENCYID = REVENUE.BASECURRENCYID,
                    @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID                
                from dbo.REVENUE
                where REVENUE.ID = @REVENUEID;

                update TEMP
                set
                    TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                    BASECURRENCYID = @BASECURRENCYID,
                    BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                    ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
                from
                    @TEMPDISTRIBUTIONS TEMP
                    left join dbo.AUCTIONPURCHASEGLDISTRIBUTION on TEMP.ID = AUCTIONPURCHASEGLDISTRIBUTION.ID
                where
                    AUCTIONPURCHASEGLDISTRIBUTION.ID is null

                update TEMP
                set 
                    TEMP.AMOUNT = 
                        case
                            when (AUCTIONPURCHASEGLDISTRIBUTION.ID is null) or (AUCTIONPURCHASEGLDISTRIBUTION.TRANSACTIONAMOUNT <> TEMP.TRANSACTIONAMOUNT) then
                                CURRENCYVALUES.BASEAMOUNT
                            else
                                AUCTIONPURCHASEGLDISTRIBUTION.AMOUNT
                        end,
                    TEMP.ORGANIZATIONAMOUNT = 
                        case
                            when(AUCTIONPURCHASEGLDISTRIBUTION.ID is null) or (AUCTIONPURCHASEGLDISTRIBUTION.TRANSACTIONAMOUNT <> TEMP.TRANSACTIONAMOUNT) then
                                CURRENCYVALUES.ORGANIZATIONAMOUNT
                            else
                                AUCTIONPURCHASEGLDISTRIBUTION.ORGANIZATIONAMOUNT
                        end
                from
                    @TEMPDISTRIBUTIONS TEMP
                    left join dbo.AUCTIONPURCHASEGLDISTRIBUTION on AUCTIONPURCHASEGLDISTRIBUTION.ID = TEMP.ID
                    outer apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(TEMP.TRANSACTIONAMOUNT,@DATE,TEMP.BASECURRENCYID,TEMP.BASEEXCHANGERATEID,TEMP.TRANSACTIONCURRENCYID,null,null,null,TEMP.ORGANIZATIONEXCHANGERATEID,0) CURRENCYVALUES

                set @NEWDISTRIBUTIONS = 
                    (
                        select
                            ACCOUNT, 
                            AMOUNT, 
                            GLPAYMENTMETHODREVENUETYPEMAPPINGID, 
                            ID,  
                            PROJECT, 
                            REFERENCE, 
                            TRANSACTIONCURRENCYID, 
                            TRANSACTIONTYPECODE,
                            BASECURRENCYID,
                            BASEEXCHANGERATEID,
                            ORGANIZATIONEXCHANGERATEID,
                            TRANSACTIONAMOUNT,
                            ORGANIZATIONAMOUNT
                        from @TEMPDISTRIBUTIONS
                        for xml raw('ITEM'),type,elements,root('GLDISTRIBUTION'),BINARY BASE64
                    );

                return @NEWDISTRIBUTIONS

            end