USP_DATALIST_AVAILABLEMEMBERSHIPPROGRAMACTIONS

                CREATE procedure dbo.USP_DATALIST_AVAILABLEMEMBERSHIPPROGRAMACTIONS(@CONSTITUENTID uniqueidentifier)
                as
                    set nocount on;

                     declare @CURRENTDATE datetime = getdate();

                    -- Membership programs available to join

                    select '00000000-0000-0000-0000-000000000000' as ID,
                        MEMBERSHIPPROGRAM.NAME as [DESCRIPTION],
                        MEMBERSHIPPROGRAM.ID as MEMBERSHIPPROGRAMID,
                        '00000000-0000-0000-0000-000000000000' as MEMBERSHIPLEVELID,
                        '00000000-0000-0000-0000-000000000000' as MEMBERSHIPLEVELTERMID,
                        '00000000-0000-0000-0000-000000000000' as MEMBERSHIPLEVELTYPECODEID,
                        0 as ACTIONTYPECODE, -- join

                        0 as ISINACTIVELEVEL,
                        0 as ISINACTIVETERM,
                        0 as ISINSACTIVETYPE
                    from dbo.MEMBERSHIPPROGRAM
                    where not exists(select * from dbo.MEMBER 
                                        inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                                        where MEMBER.CONSTITUENTID = @CONSTITUENTID
                                            and MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                                            and MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS = 0
                                            and MEMBER.ISDROPPED = 0)
                          and MEMBERSHIPPROGRAM.ISACTIVE = 1 and 
                        MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 and 
                        MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE = 0 and
                        MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM = 1
                    union all
                    -- Membership programs available to renew or rejoin

                    select
                        MEMBERSHIP.ID,
                        dbo.UFN_MEMBERSHIP_GETDESCRIPTION(MEMBERSHIP.ID) [DESCRIPTION],
                        MEMBERSHIP.MEMBERSHIPPROGRAMID,
                        MEMBERSHIP.MEMBERSHIPLEVELID,
                        MEMBERSHIP.MEMBERSHIPLEVELTERMID,
                        isnull(MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID, '00000000-0000-0000-0000-000000000000'),
                        case
                            when dateadd(month, [MEMBERSHIPLEVEL].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE])  < @CURRENTDATE then 2 --Rejoin

                            when MEMBERSHIP.STATUSCODE = 1 then 2 --Rejoin

                            else 1 --Renew

                        end,
                        ~MEMBERSHIPLEVEL.ISACTIVE,
                        ~MEMBERSHIPLEVELTERM.ISACTIVE,
                        isnull(~MEMBERSHIPLEVELTYPECODE.ACTIVE, 0)
                    from
                        dbo.MEMBERSHIP
                    inner join
                        dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                    inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                    left join dbo.MEMBERSHIPLEVELTYPECODE on MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID = MEMBERSHIPLEVELTYPECODE.ID
                    where
                        MEMBER.CONSTITUENTID = @CONSTITUENTID and
                        MEMBER.ISDROPPED = 0 and
                        [MEMBERSHIP].[STATUSCODE] <> 2 and 
                        MEMBERSHIPPROGRAM.ISACTIVE = 1 and 
                        MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 and 
                        MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE = 0 and
                        MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM = 1