UFN_APPLYTICKETSTOMEMBERSHIP_GETTICKETS

CREATE function dbo.UFN_APPLYTICKETSTOMEMBERSHIP_GETTICKETS
(
    @SALESORDERID uniqueidentifier
) returns @SALESORDERITEMS table
(
    APPLIEDSALESORDERITEMID uniqueidentifier,
    TICKETID uniqueidentifier,
    SALESORDERITEMID uniqueidentifier,
    DESCRIPTION nvarchar(510),
    PRICE money,
    DISCOUNT money,
    AMOUNTALREADYAPPLIED money
)
begin

    -- Get unapplied tickets from the sales order

    insert into @SALESORDERITEMS     
    select
        null,
        TICKETS.ID,
        SALESORDERITEM.ID,
        SALESORDERITEM.DESCRIPTION,
        SALESORDERITEMTICKET.PRICE,
        TICKETS.TOTALDISCOUNTSAPPLIED,
        0.0
    from
        dbo.SALESORDER
        inner join dbo.SALESORDERITEM on
            SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join dbo.SALESORDERITEMTICKET on
            SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        inner join
            (
                select
                    ID,
                    SALESORDERITEMTICKETID,
                    ORDERLEVELDISCOUNTSAPPLIED + ITEMLEVELDISCOUNTSAPPLIED as TOTALDISCOUNTSAPPLIED
                from
                    dbo.TICKET
                where
                    TICKET.STATUSCODE in (0, 1)
                    and TICKET.ISREFUNDED = 0
                    and TICKET.APPLIEDTOMEMBERSHIP = 0
            ) TICKETS on
            SALESORDERITEMTICKET.ID = TICKETS.SALESORDERITEMTICKETID
        left join dbo.SALESORDERITEMTICKETCOMBINATION on
            SALESORDERITEM.ID = SALESORDERITEMTICKETCOMBINATION.ID
    where
        SALESORDER.ID = @SALESORDERID and
        SALESORDERITEMTICKETCOMBINATION.ID is null and
        SALESORDERITEM.TYPECODE = 0

    -- Get applied tickets from the sales order

    insert into @SALESORDERITEMS
    select
        SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID,
        TICKET.ID,
        SALESORDERITEM.ID,
        SALESORDERITEM.DESCRIPTION,
        SALESORDERITEM.PRICE,
        TICKET.ORDERLEVELDISCOUNTSAPPLIED + TICKET.ITEMLEVELDISCOUNTSAPPLIED,
        PROMO.AMOUNTAPPLIED
    from 
        dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION 
        inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM on
            SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
        inner join dbo.SALESORDERITEM on
            SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.APPLIEDSALESORDERITEMID = SALESORDERITEM.ID
        inner join SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMTICKET PROMO on
            PROMO.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMID = SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.ID
        inner join dbo.TICKET on
            PROMO.TICKETID = TICKET.ID
    where
        SALESORDERITEM.SALESORDERID = @SALESORDERID
        and TICKET.STATUSCODE in (0, 1)
        and TICKET.ISREFUNDED = 0
        and TICKET.APPLIEDTOMEMBERSHIP = 1

return
end