UFN_GLDISTRIBUTION_CONVERTAMOUNTSINXML

            CREATE function dbo.UFN_GLDISTRIBUTION_CONVERTAMOUNTSINXML(
                @GLDISTRIBUTION xml,
                @BASECURRENCYID uniqueidentifier,
                @DEBITORGANIZATIONEXCHANGERATEID uniqueidentifier,
                @TRANSACTIONCURRENCYID uniqueidentifier,
                @DEBITBASEEXCHANGERATEID uniqueidentifier,
                @CREDITORGANIZATIONEXCHANGERATEID uniqueidentifier,
                @CREDITBASEEXCHANGERATEID uniqueidentifier
            )
            returns xml
            as
            begin

                --Get the full transaction amount from the splits collection.

                declare @FULLAMOUNTTRANSACTIONCURRENCY money;
                set @FULLAMOUNTTRANSACTIONCURRENCY = coalesce(
                    (
                        select sum(GLDISTRIBUTIONITEM.ELEMENT.value('AMOUNT[1]', 'money'))
                        from @GLDISTRIBUTION.nodes('/GLDISTRIBUTION/ITEM') GLDISTRIBUTIONITEM(ELEMENT)
                        where GLDISTRIBUTIONITEM.ELEMENT.value('TRANSACTIONTYPECODE[1]', 'tinyint') = 0
                    )
                    ,0
                );

                --Get the full base and full org amounts, as well as the organization currency

                declare @FULLDEBITAMOUNTBASECURRENCY money;
                declare @FULLDEBITAMOUNTORGANIZATIONCURRENCY money;
                declare @FULLCREDITAMOUNTBASECURRENCY money;
                declare @FULLCREDITAMOUNTORGANIZATIONCURRENCY money;
                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                select 
                    @FULLDEBITAMOUNTBASECURRENCY = BASEAMOUNT,
                    @FULLDEBITAMOUNTORGANIZATIONCURRENCY = ORGANIZATIONAMOUNT,
                    @ORGANIZATIONCURRENCYID = ORGANIZATIONCURRENCYID
                from dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(
                    @FULLAMOUNTTRANSACTIONCURRENCY,
                    null,
                    @BASECURRENCYID,
                    @DEBITBASEEXCHANGERATEID,
                    @TRANSACTIONCURRENCYID,
                    default,
                    default,
                    default,
                    @DEBITORGANIZATIONEXCHANGERATEID,
                    0
                );

                select 
                    @FULLCREDITAMOUNTBASECURRENCY = BASEAMOUNT,
                    @FULLCREDITAMOUNTORGANIZATIONCURRENCY = ORGANIZATIONAMOUNT
                from dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(
                    @FULLAMOUNTTRANSACTIONCURRENCY,
                    null,
                    @BASECURRENCYID,
                    @CREDITBASEEXCHANGERATEID,
                    @TRANSACTIONCURRENCYID,
                    default,
                    default,
                    default,
                    @CREDITORGANIZATIONEXCHANGERATEID,
                    0
                );

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

                --Replace the <GLDISTRIBUTION>...</GLDISTRIBUTION> wrapping the given XML with <ITEMLIST>...</ITEMLIST> (no longer necessary) and

                --filter out non-debit items for use by UFN_CURRENCY_CONVERTBYPROPORTIONINXML.

                declare @DEBITITEMLIST xml = (
                    select 
                        GLDISTRIBUTION.ELEMENT.query('(ITEM[TRANSACTIONTYPECODE = 0])')
                    from 
                        @GLDISTRIBUTION.nodes('/GLDISTRIBUTION') GLDISTRIBUTION(ELEMENT)            
                    for xml raw(''),type,elements,root('ITEMLIST'),BINARY BASE64
                );

                --Replace the <GLDISTRIBUTION>...</GLDISTRIBUTION> wrapping the given XML with <ITEMLIST>...</ITEMLIST> (no longer necessary) and

                --filter out non-credit items for use by UFN_CURRENCY_CONVERTBYPROPORTIONINXML.

                declare @CREDITITEMLIST xml = (
                    select 
                        GLDISTRIBUTION.ELEMENT.query('(ITEM[TRANSACTIONTYPECODE = 1])')
                    from 
                        @GLDISTRIBUTION.nodes('/GLDISTRIBUTION') GLDISTRIBUTION(ELEMENT)            
                    for xml raw(''),type,elements,root('ITEMLIST'),BINARY BASE64
                );

                return (
                    select * from (
                        select         
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/ID)[1]','uniqueidentifier') as ID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/TRANSACTIONTYPECODE)[1]','tinyint') as TRANSACTIONTYPECODE, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/GLPAYMENTMETHODREVENUETYPEMAPPINGID)[1]','uniqueidentifier') as GLPAYMENTMETHODREVENUETYPEMAPPINGID, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/ACCOUNT)[1]','nvarchar(100)') as ACCOUNT, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/PROJECT)[1]','nvarchar(100)') as PROJECT, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/REFERENCE)[1]','nvarchar(255)') as REFERENCE, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/AMOUNT)[1]','money') as TRANSACTIONAMOUNT, --rename AMOUNT field

                            BASEAMOUNT AMOUNT,
                            ORGANIZATIONAMOUNT ORGANIZATIONAMOUNT,
                            @BASECURRENCYID BASECURRENCYID,
                            @DEBITORGANIZATIONEXCHANGERATEID ORGANIZATIONEXCHANGERATEID,        
                            @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID,
                            @DEBITBASEEXCHANGERATEID BASEEXCHANGERATEID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/GLACCOUNTID)[1]','uniqueidentifier') as GLACCOUNTID    ,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/GLTRANSACTIONID)[1]','uniqueidentifier') as GLTRANSACTIONID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/REVENUEBENEFITID)[1]','uniqueidentifier') as REVENUEBENEFITID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/FINANCIALTRANSACTIONLINEITEMID)[1]','uniqueidentifier') as FINANCIALTRANSACTIONLINEITEMID                             
                        from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(
                                @DEBITITEMLIST,
                                @TRANSACTIONCURRENCYID,
                                @BASECURRENCYID,
                                @ORGANIZATIONCURRENCYID,
                                @FULLAMOUNTTRANSACTIONCURRENCY,
                                @FULLDEBITAMOUNTBASECURRENCY,
                                @DECIMALDIGITSBASECURRENCY,
                                @FULLDEBITAMOUNTORGANIZATIONCURRENCY,
                                @DECIMALDIGITSORGANIZATIONCURRENCY
                            ) ITEMLISTCONVERTED

                        union all

                        select         
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/ID)[1]','uniqueidentifier') as ID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/TRANSACTIONTYPECODE)[1]','tinyint') as TRANSACTIONTYPECODE, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/GLPAYMENTMETHODREVENUETYPEMAPPINGID)[1]','uniqueidentifier') as GLPAYMENTMETHODREVENUETYPEMAPPINGID, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/ACCOUNT)[1]','nvarchar(100)') as ACCOUNT, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/PROJECT)[1]','nvarchar(100)') as PROJECT, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/REFERENCE)[1]','nvarchar(255)') as REFERENCE, 
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/AMOUNT)[1]','money') as TRANSACTIONAMOUNT, --rename AMOUNT field

                            BASEAMOUNT AMOUNT,
                            ORGANIZATIONAMOUNT ORGANIZATIONAMOUNT,
                            @BASECURRENCYID BASECURRENCYID,
                            @CREDITORGANIZATIONEXCHANGERATEID ORGANIZATIONEXCHANGERATEID,        
                            @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID,
                            @CREDITBASEEXCHANGERATEID BASEEXCHANGERATEID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/GLACCOUNTID)[1]','uniqueidentifier') as GLACCOUNTID    ,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/GLTRANSACTIONID)[1]','uniqueidentifier') as GLTRANSACTIONID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/REVENUEBENEFITID)[1]','uniqueidentifier') as REVENUEBENEFITID,
                            ITEMLISTCONVERTED.ITEM.value('(ITEM/FINANCIALTRANSACTIONLINEITEMID)[1]','uniqueidentifier') as FINANCIALTRANSACTIONLINEITEMID                            
                        from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(
                                @CREDITITEMLIST,
                                @TRANSACTIONCURRENCYID,
                                @BASECURRENCYID,
                                @ORGANIZATIONCURRENCYID,
                                @FULLAMOUNTTRANSACTIONCURRENCY,
                                @FULLCREDITAMOUNTBASECURRENCY,
                                @DECIMALDIGITSBASECURRENCY,
                                @FULLCREDITAMOUNTORGANIZATIONCURRENCY,
                                @DECIMALDIGITSORGANIZATIONCURRENCY
                            ) ITEMLISTCONVERTED
                    ) DATA
                for xml raw('ITEM'),type,elements,root('GLDISTRIBUTION'),BINARY BASE64
                );
            end