UFN_SALESORDER_GETREFUNDSTATUS_BULK2

create function dbo.UFN_SALESORDER_GETREFUNDSTATUS_BULK2()
returns @REFUNDSTATUSTABLE TABLE
(
    ID uniqueidentifier,
    REFUNDSTATUS tinyint
)
as
begin
    with CREDITITEMQUANTITIES_CTE as
    (
    select SALESORDERITEM.ID, sum(coalesce(FTLI.QUANTITY,0)) as QUANTITY
    from dbo.CREDITITEM_EXT
        inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = CREDITITEM_EXT.SALESORDERITEMID
        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

    group by SALESORDERITEM.ID 
    )
    insert into @REFUNDSTATUSTABLE
    select
        SALESORDER.ID,
        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 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
    where
        (SALESORDERITEM.ID is null or SALESORDERITEM.TYPECODE in (0, 1, 2, 6, 14, 16) or (SALESORDERITEM.TYPECODE = 3 and SALESORDERITEMFEE.APPLIESTOCODE = 0)) -- see UFN_CREDIT_ISVALIDREFUNDTYPE

           and SALESORDER.SALESMETHODTYPECODE <> 3
    group by SALESORDER.ID 

    insert into @REFUNDSTATUSTABLE
    select 
        SALESORDER.ID,
        case when ((AMOUNTPAID > 0) and (REFUNDS > 0)) and ((AMOUNTPAID - REFUNDS) <= 0)
        then 2
        else
            case when ((AMOUNTPAID > 0) and (REFUNDS > 0)) and ((AMOUNTPAID - REFUNDS) < AMOUNTPAID)
                then 1
            else
                0
            end
        end
    from 
        dbo.SALESORDER
    outer apply
        dbo.UFN_SALESORDER_TOTALS(SALESORDER.ID)
    where 
        SALESORDER.SALESMETHODTYPECODE = 3


    return;
end