USP_DATALIST_CREDIT_MEMBERSHIPS

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

    select
        LI.[ID],
        LI.[DESCRIPTION],
        (
            select top 1 [MEMBER].[ID]
            from dbo.[MEMBER]
            where 
                [MEMBER].[MEMBERSHIPID] = [CREDITITEMMEMBERSHIP].[MEMBERSHIPID] and
                [MEMBER].[ISPRIMARY] = 1 and
                [MEMBER].[ISDROPPED] = 0
        ) as [MEMBERID],
        [CREDITITEMMEMBERSHIP].[MEMBERSHIPID]
    from
        dbo.FINANCIALTRANSACTIONLINEITEM as LI
    inner join
        dbo.[CREDITITEMMEMBERSHIP] on [CREDITITEMMEMBERSHIP].[ID] = LI.ID
    where 
        LI.FINANCIALTRANSACTIONID = @CREDITID

    union all
    --We want to add in add-on items that do not have a membership on this refund.

    --We pick the ID of one of them randomly.

    select
        max(convert(nvarchar(36), [FINANCIALTRANSACTIONLINEITEM].[ID])),
        'Add-on(s)',
        (
            select top 1 [MEMBER].[ID]
            from dbo.[MEMBER]
            where 
                [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and
                [MEMBER].[ISPRIMARY] = 1 and
                [MEMBER].[ISDROPPED] = 0
        ) as [MEMBERID],
        [MEMBERSHIP].[ID] as [MEMBERSHIPID]
    from dbo.[FINANCIALTRANSACTIONLINEITEM]
    inner join [MEMBERSHIPADDON] on [MEMBERSHIPADDON].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[SOURCELINEITEMID]
    left join dbo.[CREDITITEMMEMBERSHIP] on [CREDITITEMMEMBERSHIP].[MEMBERSHIPID] = [MEMBERSHIPADDON].[MEMBERSHIPID]
    left join dbo.[CREDITITEM_EXT] [MEMBERSHIPCREDITITEM] on [MEMBERSHIPCREDITITEM].[ID] = [CREDITITEMMEMBERSHIP].[ID]
    inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [MEMBERSHIPADDON].[MEMBERSHIPID]
    where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = @CREDITID and
        ([MEMBERSHIPCREDITITEM].[ID] is null or [MEMBERSHIPCREDITITEM].[CREDITID] <> @CREDITID)
    group by [MEMBERSHIP].[ID]

    return 0;