USP_SIMPLEDATALIST_UPGRADEABLEMEMBERSHIP

CREATE procedure dbo.USP_SIMPLEDATALIST_UPGRADEABLEMEMBERSHIP
(
    @CURRENTAPPUSERID uniqueidentifier,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @CONSTITUENTID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier = null,
    @BASEDONDATE datetime = null
)
as
    declare @ISUSERSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)

    declare @SITESTEMPTABLE TABLE(SITEID uniqueidentifier)

    if @ISUSERSYSADMIN = 0
    begin
        insert into @SITESTEMPTABLE
        select SITEID from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
    end

    if (@BASEDONDATE = null) set @BASEDONDATE = getdate()

    declare @MEMBERSHIPS table (
        ID uniqueidentifier,
        MEMBERSHIPPROGRAMID uniqueidentifier,
        LOOKUPID nvarchar(100)
    );

    insert into @MEMBERSHIPS (
        ID,
        MEMBERSHIPPROGRAMID,
        LOOKUPID
    )
    select
        MEMBERSHIP.ID,
        MEMBERSHIP.MEMBERSHIPPROGRAMID,
        MEMBERSHIP.LOOKUPID
    from dbo.MEMBERSHIP
    inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    where MEMBER.CONSTITUENTID = @CONSTITUENTID and MEMBER.ISDROPPED = 0

    select
        MEMBERSHIPS.ID as VALUE, 
        case when DUP_MEMBERSHIPS.MEMBERSHIPPROGRAMID is not null then MEMBERSHIPPROGRAM.NAME + ' (' + MEMBERSHIPS.LOOKUPID + ')' else MEMBERSHIPPROGRAM.NAME end as LABEL
    from 
        dbo.MEMBERSHIPPROGRAM
        inner join @MEMBERSHIPS MEMBERSHIPS on MEMBERSHIPS.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
        left join (
            select MEMBERSHIPS.MEMBERSHIPPROGRAMID
            from @MEMBERSHIPS as MEMBERSHIPS 
            group by MEMBERSHIPS.MEMBERSHIPPROGRAMID
            having count(*) > 1
        ) DUP_MEMBERSHIPS on MEMBERSHIPPROGRAM.ID = DUP_MEMBERSHIPS.MEMBERSHIPPROGRAMID
    where
        dbo.UFN_MEMBERSHIP_ISUPGRADEABLE(MEMBERSHIPS.ID, @BASEDONDATE) = 1 and
        (@BASECURRENCYID = MEMBERSHIPPROGRAM.BASECURRENCYID or @BASECURRENCYID is null)
        and (@ISUSERSYSADMIN = 1 or exists (select 1 from @SITESTEMPTABLE where SITEID=[MEMBERSHIPPROGRAM].[SITEID] or (SITEID is null and [MEMBERSHIPPROGRAM].[SITEID] is null)))
    order by
        LABEL

    return 0;