USP_DATALIST_SALESORDER_TICKETPROGRAMS

CREATE procedure dbo.USP_DATALIST_SALESORDER_TICKETPROGRAMS
(
    @SALESORDERID uniqueidentifier,
    @REPRINTJOB uniqueidentifier = null
)
as
    set nocount on;

    select distinct
        coalesce(SCHEDULED.ID, DAILY.ID) as PROGRAMID,
        coalesce(SCHEDULED.NAME, DAILY.NAME) as PROGRAMNAME
    from dbo.SALESORDERITEMTICKET SOIT
        inner join dbo.SALESORDERITEM SOI on SOI.ID = SOIT.ID
        left join dbo.EVENT on SOIT.EVENTID = EVENT.ID
        left join dbo.PROGRAM SCHEDULED on EVENT.PROGRAMID = SCHEDULED.ID
        left join dbo.PROGRAM DAILY on SOIT.PROGRAMID = DAILY.ID
    where
        SOI.SALESORDERID = @SALESORDERID and
        (
            @REPRINTJOB is null or
            SOIT.ID in (select TICKET.SALESORDERITEMTICKETID
                        from dbo.TICKET
                            inner join dbo.TICKETPRINTINFO TPI on TPI.TICKETID = TICKET.ID
                        where TPI.REPRINTJOB = @REPRINTJOB)
        )