UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML

            CREATE function dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(
                @ITEMLIST xml,
                @TRANSACTIONCURRENCYID uniqueidentifier,
                @BASECURRENCYID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @FULLAMOUNTTRANSACTIONCURRENCY money,
                @FULLAMOUNTBASECURRENCY money,
                @DECIMALDIGITSBASECURRENCY int,
                @FULLAMOUNTORGANIZATIONCURRENCY money,
                @DECIMALDIGITSORGANIZATIONCURRENCY int
            ) 
            returns table
            as 
            return(

                ------

                -- This function will take a properly formatted XML parameter, and various currency information 

                --    and return a table containing the correct base and org amount for each row in the XML.  This

                --    is a 5 step process:

                --    0)    The ITEMLIST XML must consist of a single root element that contains ITEM elements.

                --        Each ITEM element must contain a single AMOUNT element.  The value in this AMOUNT element

                --        is treated as the transaction amount for the row.  All other elements in the ITEM element

                --        are ignored, though they will be passed out in the ITEM column in the returned table.

                --    1)    Build a table with a row for each item in the ITEMLIST XML parameter.  Each row will have

                --        a column for an assigned row number, a column for the AMOUNT value, and a column to contain

                --        the ITEM element the row represents.

                --    2)    Given a row number and a transaction amount for each item, calculate a running total in

                --        the base currency.

                --    3)    Given a row number and base running total, subtract each successive total to get a base

                --        amount for the row.  We also incorporate info about the origin of the org amount here, for

                --        use by steps 4 and 5.

                --    4)  Given a row number, transaction amount, base amount, and origin info, calculate a running 

                --        total in the organization currency.

                --    5)    Given a row number, organization running total, and origin info, subtract each successive

                --        total to get an organization amount for each row.



                with CTE_ITEMLIST as ( --Generate row numbers and amounts for each item in the given list.

                    select
                        ITEMNUMBER.ROW,
                        ITEMLIST.ITEM.query('(ITEM[position() = sql:column("ITEMNUMBER.ROW")])[1]') ITEM,
                        ITEMLIST.ITEM.value('(ITEM[position() = sql:column("ITEMNUMBER.ROW")]/AMOUNT)[1]','money') AMOUNT
                    from @ITEMLIST.nodes('/*') ITEMLIST(ITEM)
                        cross join (
                            select
                                row_number() over (order by ITEMLIST.ITEM) ROW
                            from
                                @ITEMLIST.nodes('/*/ITEM') ITEMLIST(ITEM)
                        ) ITEMNUMBER
                ),
                CTE_ITEMBASERUNNINGTOTAL as ( --Calculate the running total for the base amount.

                    select
                        ROW,
                        ITEM,
                        AMOUNT,
                        case  --If transaction currency equals base currency, no conversion is necessary.

                            when @TRANSACTIONCURRENCYID <> @BASECURRENCYID
                                --If currencies are different, use a proportional running total based on trans full amount.

                                then dbo.UFN_CURRENCY_CONVERTBYPROPORTION(
                                        (
                                            select sum(ALLPREVIOUSITEMAMOUNT.AMOUNT) 
                                            from CTE_ITEMLIST ALLPREVIOUSITEMAMOUNT 
                                            where ALLPREVIOUSITEMAMOUNT.ROW <= ITEMLIST.ROW
                                        ),
                                        @FULLAMOUNTTRANSACTIONCURRENCY,
                                        @FULLAMOUNTBASECURRENCY,
                                        @DECIMALDIGITSBASECURRENCY
                                    )
                            else --Otherwise, just use the trans amount 

                                AMOUNT
                        end BASEAMOUNTORRUNNINGTOTAL
                    from CTE_ITEMLIST as ITEMLIST
                ),
                CTE_ITEMBASEAMOUNT as (    --Calculate the actual base amounts.

                    select
                        ROW,
                        ITEM,
                        AMOUNT,
                        case
                            when @TRANSACTIONCURRENCYID <> @BASECURRENCYID
                                --If currencies are different, we know a proportional running total was created above, so 

                                --    do subtraction here.

                                 then ITEMBASERUNNINGTOTAL.BASEAMOUNTORRUNNINGTOTAL 
                                    - coalesce(
                                        (
                                            select PREVIOUSCONVERTEDRUNNINGTOTAL.BASEAMOUNTORRUNNINGTOTAL
                                            from CTE_ITEMBASERUNNINGTOTAL as PREVIOUSCONVERTEDRUNNINGTOTAL
                                            where PREVIOUSCONVERTEDRUNNINGTOTAL.ROW = ITEMBASERUNNINGTOTAL.ROW - 1
                                        )
                                        ,0
                                    )
                             else  --Otherwise, we know the "running total" is really just the trans amount, so no math needed.

                                ITEMBASERUNNINGTOTAL.BASEAMOUNTORRUNNINGTOTAL
                        end BASEAMOUNT,
                        BASEAMOUNTORRUNNINGTOTAL,
                        ORGANIZATIONORIGIN.ORGANIZATIONAMOUNTORIGINCODE,
                        ORGANIZATIONORIGIN.ORGANIZATIONEQUALSTRANSACTIONCURRENCY,
                        ORGANIZATIONORIGIN.ORGANIZATIONEQUALSBASECURRENCY
                    from CTE_ITEMBASERUNNINGTOTAL as ITEMBASERUNNINGTOTAL
                        cross join( --Join in info about the organization amount origin for use in later CTEs.

                            select    
                                coalesce(
                                    (
                                        select top 1 ORGANIZATIONAMOUNTORIGINCODE 
                                        from dbo.MULTICURRENCYCONFIGURATION
                                    )
                                    ,0
                                ) ORGANIZATIONAMOUNTORIGINCODE,
                                case
                                    when dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() = @TRANSACTIONCURRENCYID
                                        then 1
                                    else 0
                                end ORGANIZATIONEQUALSTRANSACTIONCURRENCY,
                                case
                                    when dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() = @BASECURRENCYID
                                        then 1
                                    else 0
                                end ORGANIZATIONEQUALSBASECURRENCY
                        ) ORGANIZATIONORIGIN
                ),
                CTE_ITEMORGANIZATIONRUNNINGTOTAL as ( --Calculate running total for the organization amount.

                    select
                        ROW,
                        ITEM,
                        AMOUNT,
                        BASEAMOUNT,
                        BASEAMOUNTORRUNNINGTOTAL,
                        case  --Consider org amount origin when calculating running total.

                            when ORGANIZATIONAMOUNTORIGINCODE = 1 and ORGANIZATIONEQUALSTRANSACTIONCURRENCY = 0
                                --Using trans amount to calc org amount, but currencies don't match, so use a proportional running 

                                --    total based on trans full amount.

                                then dbo.UFN_CURRENCY_CONVERTBYPROPORTION(
                                        (
                                            select sum(ALLPREVIOUSITEMORGAMOUNT.AMOUNT) 
                                            from CTE_ITEMBASEAMOUNT as ALLPREVIOUSITEMORGAMOUNT 
                                            where ALLPREVIOUSITEMORGAMOUNT.ROW <= ITEMLIST.ROW
                                        ),
                                        @FULLAMOUNTTRANSACTIONCURRENCY,
                                        @FULLAMOUNTORGANIZATIONCURRENCY,
                                        @DECIMALDIGITSORGANIZATIONCURRENCY
                                    )        
                            when ORGANIZATIONAMOUNTORIGINCODE = 1 and ORGANIZATIONEQUALSTRANSACTIONCURRENCY = 1
                                --Using trans amount to calc org amount and currencies do match, so just put in the trans amount.

                                then AMOUNT         
                            when ORGANIZATIONAMOUNTORIGINCODE = 0 and ORGANIZATIONEQUALSBASECURRENCY = 0
                                --Using base amount to calc org amount, but currencies don't match, so use a proportional running 

                                --    total based on base full amount.

                                then dbo.UFN_CURRENCY_CONVERTBYPROPORTION(
                                        (
                                            select sum(ALLPREVIOUSITEMORGAMOUNT.BASEAMOUNT) 
                                            from CTE_ITEMBASEAMOUNT as ALLPREVIOUSITEMORGAMOUNT 
                                            where ALLPREVIOUSITEMORGAMOUNT.ROW <= ITEMLIST.ROW
                                        ),
                                        @FULLAMOUNTBASECURRENCY,
                                        @FULLAMOUNTORGANIZATIONCURRENCY,
                                        @DECIMALDIGITSORGANIZATIONCURRENCY
                                    )
                            when ORGANIZATIONAMOUNTORIGINCODE = 0 and ORGANIZATIONEQUALSBASECURRENCY = 1
                                --Using base amount to calc org amount and currencies do match, so just put in the base amount.

                                then BASEAMOUNT
                        end ORGANIZATIONAMOUNTORRUNNINGTOTAL,
                        ORGANIZATIONAMOUNTORIGINCODE,
                        ORGANIZATIONEQUALSTRANSACTIONCURRENCY,
                        ORGANIZATIONEQUALSBASECURRENCY
                    from CTE_ITEMBASEAMOUNT as ITEMLIST
                )
                select --Calculate the actual organization amounts and return the other information needed.

                    ROW,
                    ITEM, 
                    BASEAMOUNT,
                    case
                        when (ORGANIZATIONAMOUNTORIGINCODE = 1 and ORGANIZATIONEQUALSTRANSACTIONCURRENCY = 0)
                                or (ORGANIZATIONAMOUNTORIGINCODE = 0 and ORGANIZATIONEQUALSBASECURRENCY = 0)
                            --If org currency doesn't match the currency of the currency of the origin amount, we know a proportional 

                            --    running total was created above, so do subtraction here.

                            then ITEMRUNNINGTOTAL.ORGANIZATIONAMOUNTORRUNNINGTOTAL
                                - coalesce(
                                    (
                                        select PREVIOUSCONVERTEDORGANIZATIONRUNNINGTOTAL.ORGANIZATIONAMOUNTORRUNNINGTOTAL
                                        from CTE_ITEMORGANIZATIONRUNNINGTOTAL as PREVIOUSCONVERTEDORGANIZATIONRUNNINGTOTAL
                                        where PREVIOUSCONVERTEDORGANIZATIONRUNNINGTOTAL.ROW = ITEMRUNNINGTOTAL.ROW - 1
                                    )
                                    ,0
                                )
                        else --Otherwise, we know the "running total" is really just the desired amount, so no math needed. 

                            ITEMRUNNINGTOTAL.ORGANIZATIONAMOUNTORRUNNINGTOTAL 
                    end ORGANIZATIONAMOUNT
                from CTE_ITEMORGANIZATIONRUNNINGTOTAL as ITEMRUNNINGTOTAL

            );