USP_DATALIST_CREDIT_TICKETS

CREATE procedure dbo.USP_DATALIST_CREDIT_TICKETS
(
    @CREDITID uniqueidentifier
)
as
    set nocount on;

    --Refunded tickets

    select
        EXT.SALESORDERITEMID as ID,
        SALESORDERITEM.DESCRIPTION as DESCRIPTION,
        EVENT.STARTDATE,
        EVENT.STARTTIME,
        LI.QUANTITY,
        LI.UNITVALUE,
        EXT.DISCOUNTS,
        EXT.FEES,
        ((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) + EXT.FEES,
        SALESORDERITEM.SALESORDERID
    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
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    inner join
        dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
    left join
        dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
    left join
        dbo.EVENT on EVENT.ID = SALESORDERITEMTICKET.EVENTID
    where 
        FT.ID = @CREDITID
        and SALESORDERITEMTICKETCOMBINATION.ID is null

    union all
    --Refunded combo tickets

    select
        EXT.SALESORDERITEMID as ID,
        COMBINATION.NAME + ' - ' + PRICETYPECODE.DESCRIPTION + ' - ' +
        stuff((
                select 
                    ';' + case
                          when ITEMTICKET.PROGRAMID is not null then P.NAME 
                          else E.NAME + ' ' + convert(nvarchar(8),e.STARTDATE, 1) + ' ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(e.STARTTIME)
                    end
                from dbo.SALESORDERITEMTICKET ITEMTICKET
                inner join dbo.SALESORDERITEMTICKETCOMBINATION TICKETCOMBINATION on ITEMTICKET.ID = TICKETCOMBINATION.ID
                left join dbo.PROGRAM P on ITEMTICKET.PROGRAMID = P.ID
                left join dbo.EVENT E on ITEMTICKET.EVENTID = E.ID
                where 
                    TICKETCOMBINATION.TICKETCOMBINATIONID = SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID
                    and ITEMTICKET.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID 
                FOR XML PATH('')
            ), 1, 1, '')
        as DESCRIPTION,
        null as STARTDATE,
        null as STARTTIME,
        LI.QUANTITY,
        dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID),
        EXT.DISCOUNTS,
        dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED(LI.ID) as FEES,
        LI.QUANTITY * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED(LI.ID),
        SALESORDERITEM.SALESORDERID
    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
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    inner join
        dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
    inner join
        dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
    inner join
        dbo.COMBINATION on COMBINATION.ID = SALESORDERITEMTICKETCOMBINATION.COMBINATIONID
    inner join
        dbo.PRICETYPECODE on PRICETYPECODE.ID = SALESORDERITEMTICKET.PRICETYPECODEID
    left join
        dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID
    left join
        dbo.EVENT on SALESORDERITEMTICKET.EVENTID = EVENT.ID
    where 
        FT.ID = @CREDITID
        and SALESORDERITEMTICKETCOMBINATION.PROGRAMGROUPID in (
            select top 1 ID from dbo.PROGRAMGROUP  
            where PROGRAMGROUP.COMBINATIONID = SALESORDERITEMTICKETCOMBINATION.COMBINATIONID
        )

    order by
        DESCRIPTION

    return 0;