UFN_CONSTITUENT_GETMEMBERSHIPS

CREATE function dbo.UFN_CONSTITUENT_GETMEMBERSHIPS
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @DATAFORMINSTANCEID uniqueidentifier = null
)
returns table
as
  return
  (
    with MEMBERS_CTE as
    (
      -- Include memberships where:

      -- the member is this constituent,

      -- the member is in this constituent group, and

      -- the member is this constituent's household

      select @CONSTITUENTID as ID

      union all

      select TVF.ID
      from dbo.UFN_GROUP_GETCURRENTMEMBERS(@CONSTITUENTID, @CURRENTAPPUSERID, @DATAFORMINSTANCEID, 0) as TVF

      union all

      select TVF.ID
      from dbo.UFN_CONSTITUENT_GETGROUPS(@CONSTITUENTID) as TVF
      where dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1
    )
      -- This function serves the ConstituentSummaryMembershipsTile.View.xml, which limits the number of records returned.

      -- If you change the number of records returned here, you must also update the tile's UIModel to allow one less than the new limit.

      select distinct top 501
        MEMBERSHIP.ID,
        MEMBERSHIP.MEMBERSHIPPROGRAMID,
        MEMBERSHIPPROGRAM.NAME as MEMBERSHIPPROGRAMNAME,
        MEMBERSHIPLEVEL.NAME as MEMBERSHIPLEVELNAME,
        PRIMARYMEMBER.CONSTITUENTID as PRIMARYMEMBERID,
        NF.NAME as PRIMARYMEMBERNAME,
        MEMBERSHIP.STATUS,
        MEMBERSHIP.STATUSCODE,
        MEMBERSHIP.LOOKUPID,
        MEMBERSHIP.JOINDATE,
        MEMBERSHIP.EXPIRATIONDATE,
        case MEMBERSHIP.STATUSCODE
          when 0 then 1
          when 2 then 2
          when 3 then 3
          when 4 then 4
          when 5 then 5
          when 1 then 6
          else 7
        end as SEQUENCE
      from dbo.MEMBERSHIP
        inner join dbo.MEMBER as PRIMARYMEMBER on PRIMARYMEMBER.MEMBERSHIPID = MEMBERSHIP.ID
        inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
        inner join MEMBERS_CTE as MEMBERS on MEMBER.CONSTITUENTID = MEMBERS.ID
        inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
        left join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PRIMARYMEMBER.CONSTITUENTID) as NF
      where
        PRIMARYMEMBER.ISPRIMARY = 1 and
        PRIMARYMEMBER.ISDROPPED = 0 and
        MEMBER.ISDROPPED = 0
    );