UFN_CREDIT_SALESORDERITEMTAXREMAININGREFUNDABLEAMOUNT

CREATE function dbo.UFN_CREDIT_SALESORDERITEMTAXREMAININGREFUNDABLEAMOUNT
(
    @SALESORDERITEMTAXID uniqueidentifier
)
returns money
as begin
    declare 
        @TAXTOTAL money,
        @AMTPAIDREFUNDEDORAPPLIEDTIX money,
        @AMTPAIDREFUNDEDMERCH money,
        @AMTPAIDREFUNDEDITEMFEES money,
        @AMTPAIDREFUNDEDORDERFEES money,
        @AMTPAIDALLTIX money,
        @AMTPAIDALLMERCH money,
        @AMTPAIDITEMFEES money,
        @AMTPAIDORDERFEES money;

    set @TAXTOTAL = (select TOTAL from dbo.SALESORDERITEM where ID = @SALESORDERITEMTAXID);

    -- 2014-01-17 WD:

    -- By the time this stored procedure is called, the relevant order will have been pulled up in the refund form,

    -- which means that TICKET and SALESORDERITEMMERCHANDISEUNIT records for all the items available for refund

    -- in the order will exist, so we're safe to use AMOUNTPAID. There is a slight concern related to refunds made

    -- before the partial refund work and merchandise, because CREDITITEM_EXT would not have a link to the

    -- SALESORDERITEMMERCHANDISEUNIT record via SALESORDERITEMIZEDITEMID in such a case. That is addressed below


    set @AMTPAIDALLTIX =    coalesce((
                                select 
                                    sum(TICKET.AMOUNTPAID)
                                from
                                    dbo.SALESORDERITEMTAX
                                    inner join dbo.TICKET
                                        on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTAX.SALESORDERITEMID
                                where
                                    SALESORDERITEMTAX.TAXITEMID = @SALESORDERITEMTAXID
                            ), 0);

    set @AMTPAIDALLMERCH =    coalesce((
                                select
                                    sum(SALESORDERITEMMERCHANDISEUNIT.AMOUNTPAID)
                                from
                                    dbo.SALESORDERITEMTAX
                                    inner join dbo.SALESORDERITEMMERCHANDISEUNIT
                                        on SALESORDERITEMMERCHANDISEUNIT.SALESORDERITEMMERCHANDISEID = SALESORDERITEMTAX.SALESORDERITEMID
                                where
                                    SALESORDERITEMTAX.TAXITEMID = @SALESORDERITEMTAXID
                            ), 0);

    set @AMTPAIDITEMFEES =    coalesce((
                                select
                                    sum(SALESORDERITEMTICKETFEE.AMOUNT)
                                from
                                    dbo.SALESORDERITEMTAX
                                    inner join dbo.SALESORDERITEMTICKETFEE
                                        on SALESORDERITEMTICKETFEE.SALESORDERITEMFEEID = SALESORDERITEMTAX.SALESORDERITEMID
                                where
                                    SALESORDERITEMTAX.TAXITEMID = @SALESORDERITEMTAXID
                            ), 0);

    set @AMTPAIDORDERFEES =    coalesce((
                                select
                                    sum(SALESORDERITEM.PRICE)
                                from
                                    dbo.SALESORDERITEMTAX
                                    inner join dbo.SALESORDERITEM
                                        on SALESORDERITEM.ID = SALESORDERITEMTAX.SALESORDERITEMID
                                    inner join dbo.SALESORDERITEMFEE
                                        on SALESORDERITEMFEE.ID = SALESORDERITEM.ID
                                where
                                    SALESORDERITEMTAX.TAXITEMID = @SALESORDERITEMTAXID
                                    and SALESORDERITEM.TYPECODE = 3
                                    and SALESORDERITEMFEE.APPLIESTOCODE = 0
                            ), 0);

    set @AMTPAIDREFUNDEDORAPPLIEDTIX =    coalesce((
                                            select 
                                                sum(TICKET.AMOUNTPAID)
                                            from
                                                dbo.SALESORDERITEMTAX
                                                inner join dbo.TICKET
                                                    on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTAX.SALESORDERITEMID
                                            where
                                                SALESORDERITEMTAX.TAXITEMID = @SALESORDERITEMTAXID
                                                and (TICKET.ISREFUNDED = 1 or TICKET.APPLIEDTOMEMBERSHIP = 1)
                                        ), 0);

    -- Because we don't know if there are old style refunds existing on an order with merchandise, we have to be prepared

    -- We're safe when relying on FTLI.BASEAMOUNT because, before non-partial refunds, if there was an order-level discount

    -- (which is not reflected in FTLI.BASEAMOUNT) you would have had to refund the whole order, or at least all the items

    -- that the discount applied to. Item-level discounts ARE reflected in FTLI.BASEAMOUNT, so it should be an accurate

    -- representation of the amount refunded, which is equal to amount paid in old-style refunds, of the merchandise


    set @AMTPAIDREFUNDEDMERCH =    coalesce((
                                            select 
                                                sum(AMOUNT)
                                            from (
                                                select
                                                    case
                                                        when SOIMU.ID is null then 
                                                            FTLI.BASEAMOUNT
                                                        else 
                                                            SOIMU.AMOUNTPAID
                                                    end AMOUNT
                                                from 
                                                    dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                                                    inner join dbo.CREDITITEM_EXT EXT 
                                                        on EXT.ID = FTLI.ID
                                                    inner join dbo.SALESORDERITEMTAX SOITAX
                                                        on SOITAX.SALESORDERITEMID = EXT.SALESORDERITEMID
                                                    left join dbo.SALESORDERITEMMERCHANDISEUNIT SOIMU 
                                                        on SOIMU.ID = EXT.SALESORDERITEMIZEDITEMID
                                                where 
                                                    SOITAX.TAXITEMID = @SALESORDERITEMTAXID
                                                    and EXT.TYPECODE = 14  -- Merch (you may not need to filter by typecode, depending on what's outside the subquery)

                                                    and FTLI.TYPECODE = 0  -- excluding discounts from original order - if not linking to refund FT, this may be necessary

                                            ) REFUDEDMERCH
                                        ), 0);

    set @AMTPAIDREFUNDEDITEMFEES =    coalesce((
                                        select
                                            sum(SALESORDERITEMTICKETFEE.AMOUNT)
                                        from
                                            dbo.SALESORDERITEMTAX
                                            inner join dbo.SALESORDERITEMTICKETFEE
                                                on SALESORDERITEMTICKETFEE.SALESORDERITEMFEEID = SALESORDERITEMTAX.SALESORDERITEMID
                                        where
                                            SALESORDERITEMTAX.TAXITEMID = @SALESORDERITEMTAXID
                                            and SALESORDERITEMTICKETFEE.REFUNDEDAMOUNT > 0
                                    ), 0);

    set @AMTPAIDREFUNDEDORDERFEES =    coalesce((
                                        select
                                            sum(SALESORDERITEM.PRICE)
                                        from
                                            dbo.SALESORDERITEMTAX
                                            inner join dbo.SALESORDERITEM
                                                on SALESORDERITEM.ID = SALESORDERITEMTAX.SALESORDERITEMID
                                            inner join dbo.CREDITITEM_EXT
                                                on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
                                            inner join dbo.SALESORDERITEMFEE
                                                on SALESORDERITEMFEE.ID = SALESORDERITEM.ID
                                        where
                                            SALESORDERITEMTAX.TAXITEMID = @SALESORDERITEMTAXID
                                            and SALESORDERITEM.TYPECODE = 3
                                            and SALESORDERITEMFEE.APPLIESTOCODE = 0
                                    ), 0);

    -- Do not factor out @TAXTOTAL. We require it to be inside the rounding function so that fractions of cents are treated the same

    -- in this calculation as they are when a refund is actually completed. If it is factored out, then fractions of cents will not

    -- be correctly calculated compared to the amount of tax actually refunded.

    return (@TAXTOTAL - ROUND((@TAXTOTAL) * ((@AMTPAIDREFUNDEDMERCH + @AMTPAIDREFUNDEDORAPPLIEDTIX + @AMTPAIDREFUNDEDITEMFEES + @AMTPAIDREFUNDEDORDERFEES) / (@AMTPAIDALLTIX + @AMTPAIDALLMERCH + @AMTPAIDITEMFEES + @AMTPAIDORDERFEES)), 2));
end