USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERS

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERS]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @MEMBERS xml = null output,
  @NUMBEROFMEMBERSALLOWED int = null output,
  @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
  @PROGRAMALLOWSMULTIPLEMEMBERSHIPS bit = null output,
  @NUMBEROFCHILDREN smallint = null output,
  @NUMBEROFCHILDRENALLOWED smallint = null output
)
as
  set nocount on;

  -- be sure to set these, in case the select returns no rows
  set @DATALOADED = 0
  set @TSLONG = 0

  set @NUMBEROFMEMBERSALLOWED = dbo.[UFN_MEMBER_NUMBEROFMEMBERSALLOWED](@ID);

  select
    @DATALOADED = 1,
    @TSLONG = MEMBERSHIP.TSLONG,
    @MEMBERSHIPPROGRAMID = [MEMBERSHIPPROGRAM].[ID],
    @PROGRAMALLOWSMULTIPLEMEMBERSHIPS = [MEMBERSHIPPROGRAM].[ALLOWMULTIPLEMEMBERSHIPS],
    @NUMBEROFCHILDRENALLOWED = [MEMBERSHIPLEVEL].CHILDRENALLOWED,
    @NUMBEROFCHILDREN = [MEMBERSHIP].NUMBEROFCHILDREN
  from dbo.[MEMBERSHIP]
  inner join dbo.[MEMBERSHIPPROGRAM] on [MEMBERSHIPPROGRAM].[ID] = [MEMBERSHIP].[MEMBERSHIPPROGRAMID]
  inner join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELID]
  where [MEMBERSHIP].[ID] = @ID;

  declare @MAXMEMBERTSLONG bigint = 0;
  select @MAXMEMBERTSLONG = max(TSLONG) from dbo.MEMBER where MEMBERSHIPID = @ID;

  -- Set @TSLONG to the greatest TSLONG value between the membership and members records for concurrency testing
  if @MAXMEMBERTSLONG > @TSLONG
    set @TSLONG = @MAXMEMBERTSLONG;

  declare @PRIMARYMEMBERCONSTITUENTID uniqueidentifier = (select top 1 CONSTITUENTID from dbo.MEMBER where MEMBERSHIPID = @ID and ISPRIMARY = 1 and ISDROPPED = 0);

  select @MEMBERS = (
    select
      MEMBER.ID as MEMBERID,
      CONSTITUENT.ID as CONSTITUENTID,
      RELATIONSHIPINFO.[DESCRIPTION],
      CONSTITUENT.LOOKUPID,
      MEMBER.ISPRIMARY
    from dbo.MEMBER
    inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID
    outer apply
    (
      select top 1 RELATIONSHIPTYPECODE.[DESCRIPTION]
      from dbo.RELATIONSHIP
        inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIP.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
      where RELATIONSHIP.RECIPROCALCONSTITUENTID = @PRIMARYMEMBERCONSTITUENTID
        and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
      order by RELATIONSHIPTYPECODE.SEQUENCE
    ) RELATIONSHIPINFO
    where MEMBER.ISDROPPED = 0
      and MEMBER.MEMBERSHIPID = @ID
    order by [ISPRIMARY] desc, [DESCRIPTION] desc, [NAME] asc
    for xml raw('ITEM'), type, elements, root('MEMBERS'), binary base64);

  return 0;