UFN_CREDIT_GETITEMLIST

CREATE function dbo.UFN_CREDIT_GETITEMLIST
(
    @CREDITID uniqueidentifier
)
returns nvarchar(max)
with execute as caller
as begin
    declare @LIST nvarchar(max) = null;

    -- Note:

    --        Excluding Tax and Discount on purpose as these are not items that can be chosen to be refunded.


    select
        @LIST = coalesce(@LIST + ', ', '') +
            case EXT.TYPECODE
                when 0 then    coalesce(PROGRAM.NAME, TICKETEVENT.NAME, FTLI.DESCRIPTION)    -- ticket

                when 2 then EXT.TYPE + ' (' + FTLI.DESCRIPTION    + ')'                    -- donation

                when 6 then EVENT.NAME                                                    -- event registration

                when 255 then                                                            -- Group Sales reservation

                    case
                        when right(RESERVATION.NAME, 12) = N' Reservation' then RESERVATION.NAME
                        else RESERVATION.NAME + N' Reservation'
                    end
                else FTLI.DESCRIPTION
            end
    from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
        inner join dbo.CREDITITEM_EXT EXT on FTLI.ID = EXT.ID
        left outer join dbo.CREDITITEMTICKET on FTLI.ID = CREDITITEMTICKET.ID
        left outer join dbo.TICKET on CREDITITEMTICKET.TICKETID = TICKET.ID
        left outer join dbo.PROGRAM on TICKET.PROGRAMID = PROGRAM.ID
        left outer join dbo.EVENT TICKETEVENT on TICKET.EVENTID = TICKETEVENT.ID
        left outer join dbo.CREDITITEMEVENTREGISTRATION on FTLI.ID = CREDITITEMEVENTREGISTRATION.ID
        left outer join dbo.REGISTRANT on CREDITITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
        left outer join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
        left outer join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on SOURCELI.ID = FTLI.SOURCELINEITEMID
        left outer join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = SOURCELI.FINANCIALTRANSACTIONID
        left outer join dbo.RESERVATION on RESERVATION.ID = SALESORDERPAYMENT.SALESORDERID
    where EXT.TYPECODE not in (4,5)-- Tax and Discount

        and (EVENT.ID is not null or EXT.TYPECODE <> 6 )  -- Event Registration 

        and EXT.CREDITID = @CREDITID;        

    return @LIST;
end