UFN_SALESORDER_GETREFUNDSTATUS_BULK

CREATE function dbo.UFN_SALESORDER_GETREFUNDSTATUS_BULK()
returns table
as
    return
        with CREDITITEMQUANTITIES_CTE as
        (
            select 
                CREDITITEM_EXT.SALESORDERITEMID as ID, 
                sum(coalesce(FTLI.QUANTITY,0)) as QUANTITY
            from
                dbo.CREDITITEM_EXT
            inner join 
                dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = CREDITITEM_EXT.ID
            inner join 
                dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
            where 
                FT.TYPECODE = 23 -- Refund

                and CREDITITEM_EXT.SALESORDERITEMID is not null
            group by
                CREDITITEM_EXT.SALESORDERITEMID
        ),
        GROUPSALESREFUNDS_CTE as
        (
            select 
                RESERVATION.ID,
                case
                    when REFUNDS > 0 then
                        case
                            when AMOUNTPAID = REFUNDS then
                                2  -- Fully refunded

                            else
                                1  -- Partially refunded 

                        end
                    else
                        0  -- No refund

                end as GROUPSALESREFUNDSTATUS
            from 
                dbo.RESERVATION
            outer apply
                dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID)
        )
        select
            SALESORDER.ID,
            case when SALESORDER.SALESMETHODTYPECODE = 3
                then GROUPSALESREFUNDS.GROUPSALESREFUNDSTATUS
            else
                case
                    when sum(coalesce(CREDITITEMS.QUANTITY, 0)) = 0 -- No part has been refunded

                        then 0
                    when sum(coalesce(SALESORDERITEM.QUANTITY, 0)) = sum(coalesce(CREDITITEMS.QUANTITY, 0)) -- Fully refunded

                        then 2
                    else 
                        1    -- Partially refunded

                end
            end as REFUNDSTATUS
        from 
            dbo.SALESORDER
        left join 
            dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        left join 
            dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
        left join 
            CREDITITEMQUANTITIES_CTE CREDITITEMS on CREDITITEMS.ID = SALESORDERITEM.ID
        left join
            GROUPSALESREFUNDS_CTE GROUPSALESREFUNDS on GROUPSALESREFUNDS.ID = SALESORDER.ID
        where
            (SALESORDERITEM.ID is null or SALESORDERITEM.TYPECODE in (0, 1, 2, 3, 6, 14, 16))
        group by SALESORDER.ID, GROUPSALESREFUNDS.GROUPSALESREFUNDSTATUS, SALESORDER.SALESMETHODTYPECODE