USP_DATALIST_SALESORDER_ADJUSTMENTS2

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

    -- Fees

    select
        FEESOI.ID,
        FEESOI.DESCRIPTION,
        FEESOI.TOTAL,
        FEESOI.TYPE,
        FEESOI.TYPECODE
    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.TOTAL <> 0
        and (
            SALESORDERITEMFEE.APPLIESTOCODE = 0
            or FEESOI.PRICINGSTRUCTURECODE = 1
            or AFFECTEDSOI.PRICINGSTRUCTURECODE = 1
        )

    union all

    -- Taxes

    select
        SALESORDERITEM.ID,
        SALESORDERITEM.DESCRIPTION,
        SALESORDERITEM.TOTAL,
        SALESORDERITEM.TYPE,
        SALESORDERITEM.TYPECODE
    from
        dbo.SALESORDERITEM
    where 
        SALESORDERITEM.SALESORDERID = @CONTEXTID
        and SALESORDERITEM.TYPECODE = 4  -- Tax


    union all

    -- Discounts

    select
        SALESORDERITEM.ID,
        SALESORDERITEM.DESCRIPTION,
        (-SALESORDERITEM.TOTAL) as TOTAL,
        SALESORDERITEM.TYPE,
        SALESORDERITEM.TYPECODE
    from
        dbo.SALESORDERITEM
    where 
        SALESORDERITEM.SALESORDERID = @CONTEXTID
        and SALESORDERITEM.TYPECODE = 5  -- Ticket

        and SALESORDERITEM.TOTAL <> 0

    order by
        TYPECODE asc,
        DESCRIPTION asc

    return 0;