USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPTRANSACTION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPTRANSACTION
(
    @ID uniqueidentifier,  -- represents MEMBERSHIPTRANSACTION.ID
    @DATALOADED bit = 0 output,
    @ACTIONCODE tinyint = null output,
    @ACTIONDESCRIPTION nvarchar(max) = null output,
    @MEMBERSHIPPROGRAMNAME nvarchar(max) = null output,
    @MEMBERSHIPLEVELNAME nvarchar(max) = null output,
    @MEMBERSHIPLEVELTERMDESCRIPTION nvarchar(max) = null output
)
as
    set nocount on;
    set @DATALOADED = 0;

    -- I took the term description concatenation from the Total Revenue report.
    select @DATALOADED = 1,
           @ACTIONCODE = MT.ACTIONCODE,
           @ACTIONDESCRIPTION = MT.ACTION,
           @MEMBERSHIPPROGRAMNAME = MP.NAME,
           @MEMBERSHIPLEVELNAME = ML.NAME,
           @MEMBERSHIPLEVELTERMDESCRIPTION = isnull(
                case MP.PROGRAMTYPECODE
                    when 0 then cast(MLT.TERMTIMELENGTH as nvarchar(5)) + ' ' + MLT.TERMLENGTH
                    when 1 then MLT.RECURRINGPAYMENTOPTION
                    when 2 then case when MLT.LIFETIMEPAYMENTOPTIONCODE = 0 then MLT.LIFETIMEPAYMENTOPTION
                    end
                end
            , '')
    from dbo.MEMBERSHIPTRANSACTION MT
    inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = MT.MEMBERSHIPLEVELID
    inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = ML.MEMBERSHIPPROGRAMID
    inner join dbo.MEMBERSHIPLEVELTERM MLT on MLT.ID = MT.MEMBERSHIPLEVELTERMID
    where MT.ID = @ID;

    return 0;