USP_DATALIST_MEMBERSHIPCARD

Displays a list of membership cards for a membership.
Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@MEMBERID	uniqueidentifier	IN	Member
@INCLUDEINACTIVECARDS	bit	IN	Include inactive cards
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_MEMBERSHIPCARD
                (
                    @MEMBERSHIPID uniqueidentifier,
                    @MEMBERID uniqueidentifier = null,
                    @INCLUDEINACTIVECARDS bit = 1
                )
                as
                    set nocount on;

                    declare @LOWERBOUND datetime;
                    set @LOWERBOUND = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    select
                        MC.ID,
                        MC.STATUSCODE,
                        case
                            when @LOWERBOUND > MC.EXPIRATIONDATE then
                                1
                            else
                                0
                        end ISEXPIRED,
                        NF.NAME MEMBER,
                        case
                            when MC.STATUSCODE <> 2 and @LOWERBOUND > MC.EXPIRATIONDATE then
                                'Expired'
                            else
                                MC.STATUS
                        end [STATUS],
                        MC.NAMEONCARD,
                        MC.DATEADDED,
                        MC.PRINTDATE,
                        MC.EXPIRATIONDATE,
                        MC.COMMENTS,
                        dbo.UFN_CHANGEAGENT_GETUSERNAME(MC.ADDEDBYID) ISSUEDBY,
                        dbo.UFN_CHANGEAGENT_GETUSERNAME(MC.PRINTEDBYID) PRINTEDBY
                    from
                        dbo.MEMBERSHIP MS
                    inner join
                        dbo.MEMBER M
                            on MS.ID = M.MEMBERSHIPID
                    inner join
                        dbo.MEMBERSHIPCARD MC
                            on M.ID = MC.MEMBERID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(M.CONSTITUENTID) NF
                    where
                        MS.ID = @MEMBERSHIPID
                        and (
                            @MEMBERID is null
                            or @MEMBERID = M.ID
                        )
                        and (
                            @INCLUDEINACTIVECARDS = 1
                            or (
                                MC.STATUSCODE <> 2
                                and @LOWERBOUND <= MC.EXPIRATIONDATE
                            )
                        )
                    order by
                        MEMBER,
                        MC.DATEADDED