USP_DATALIST_SALESORDER_ADJUSTMENTS

CREATE procedure dbo.USP_DATALIST_SALESORDER_ADJUSTMENTS
(
    @CONTEXTID uniqueidentifier
)
as
    set nocount on;

    -- Fees

    select
        FEESOI.ID,
        FEESOI.DESCRIPTION,
        FEESOI.TOTAL,
        FEESOI.TYPE,
        FEESOI.TYPECODE,
        0 as ISREFUND,
        FEESOI.DESCRIPTION as ORDERBYDESCRIPTION
    from
        dbo.SALESORDERITEM FEESOI
    inner join
        dbo.SALESORDERITEMFEE on FEESOI.ID = SALESORDERITEMFEE.ID
    left join
        dbo.SALESORDERITEM AFFECTEDSOI on SALESORDERITEMFEE.SALESORDERITEMID = AFFECTEDSOI.ID
    where
        FEESOI.SALESORDERID = @CONTEXTID
        and FEESOI.TYPECODE = 3
        and FEESOI.ID not in (
            select
                EXT.SALESORDERITEMID
            from
                dbo.FINANCIALTRANSACTION as FT
            inner join
                dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
            inner join
                dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            where
                FT.TYPECODE = 23  -- Refund

                and EXT.SALESORDERITEMID is not null
        )
        and FEESOI.TOTAL <> 0
        and (
            SALESORDERITEMFEE.APPLIESTOCODE = 0
            or FEESOI.PRICINGSTRUCTURECODE = 1
            or AFFECTEDSOI.PRICINGSTRUCTURECODE = 1
        )

    -- Refunded Fees

    union all
    select
        SALESORDERITEM.ID,
        'Refund ' + convert(nvarchar(20), cast(FT.DATE as datetime), 1) + ', ' + SALESORDERITEM.DESCRIPTION,
        SALESORDERITEM.TOTAL,
        SALESORDERITEM.TYPE,
        SALESORDERITEM.TYPECODE,
        1 as ISREFUND,
        SALESORDERITEM.DESCRIPTION as ORDERBYDESCRIPTION
    from
        dbo.SALESORDERITEM
    inner join
        dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
    where 
        SALESORDERITEM.SALESORDERID = @CONTEXTID
        and SALESORDERITEMFEE.APPLIESTOCODE = 0

    -- Taxes

    union all
    select
        SALESORDERITEM.ID,
        SALESORDERITEM.DESCRIPTION,
        SALESORDERITEM.TOTAL - REFUNDTOTALS.AMOUNT as TOTAL,
        SALESORDERITEM.TYPE,
        SALESORDERITEM.TYPECODE,
        0 as ISREFUND,
        SALESORDERITEM.DESCRIPTION as ORDERBYDESCRIPTION
    from
        dbo.SALESORDERITEM
    outer apply
        dbo.UFN_SALESORDERITEM_REFUNDTOTALS(SALESORDERITEM.ID) as REFUNDTOTALS
    where 
        SALESORDERITEM.SALESORDERID = @CONTEXTID and
        SALESORDERITEM.TYPECODE = 4 and  -- Tax

        SALESORDERITEM.TOTAL <> REFUNDTOTALS.AMOUNT

    --Refunded Taxes

    union all
    select
        SALESORDERITEM.ID,
        'Refund ' + convert(nvarchar(20), cast(FT.DATE as datetime), 1) + ', ' + SALESORDERITEM.DESCRIPTION,
        ((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as TOTAL,
        SALESORDERITEM.TYPE,
        SALESORDERITEM.TYPECODE,
        1 as ISREFUND,
        SALESORDERITEM.DESCRIPTION as ORDERBYDESCRIPTION
    from
        dbo.SALESORDERITEM
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
    where 
        SALESORDERITEM.SALESORDERID = @CONTEXTID and
        SALESORDERITEM.TYPECODE = 4  -- Tax


    -- Discounts

    union all
    select
        SALESORDERITEM.ID,
        case 
            when FT.ID is not null then
                'Refund ' + convert(nvarchar(20), cast(FT.DATE as datetime), 1) + ', ' + SALESORDERITEM.DESCRIPTION
            else
                SALESORDERITEM.DESCRIPTION
        end as DESCRIPTION,
        (-SALESORDERITEM.TOTAL) as TOTAL,
        SALESORDERITEM.TYPE,
        SALESORDERITEM.TYPECODE,
        case 
            when FT.ID is not null then
                1
            else
                0
        end as ISREFUND,
        SALESORDERITEM.DESCRIPTION as ORDERBYDESCRIPTION
    from
        dbo.SALESORDERITEM
    left outer join
        dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
    left outer join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
    left outer join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
    where 
        SALESORDERITEM.SALESORDERID = @CONTEXTID and
        SALESORDERITEM.TYPECODE = 5 and  -- Ticket

        SALESORDERITEM.TOTAL <> 0

    order by
        TYPECODE asc,
        ORDERBYDESCRIPTION asc,
        ISREFUND asc;

    return 0;