USP_MEMBERSHIP_ADD

CREATE procedure dbo.USP_MEMBERSHIP_ADD
(
  @MEMBERSHIPID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @CONSTITUENTID uniqueidentifier,
  @MEMBERSHIPPROGRAMID uniqueidentifier = null,
  @MEMBERSHIPLEVELID uniqueidentifier = null,
  @MEMBERSHIPLEVELTERMID uniqueidentifier = null,
  @MEMBERSHIPTYPECODEID uniqueidentifier = null,
  @STATUSCODE smallint = 0,
  @TRANSACTIONDATE datetime = null,
  @NUMBEROFCHILDREN smallint = 0,
  @COMMENTS nvarchar(1000) = null,
  @ISGIFT bit = 0,
  @SENDRENEWALCODE smallint = 1,
  @EXPIRATIONDATE datetime = null,
  @MEMBERS xml = null,
  @GIVENBYID uniqueidentifier = null,
  @REVENUESPLITID uniqueidentifier = null,
  @TRANSACTIONID uniqueidentifier = null output
)
as
begin
  set nocount on;

  if @MEMBERSHIPID is null
    set @MEMBERSHIPID = newid();

  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  begin try
    --check the expiration date

    if @EXPIRATIONDATE is not null
      begin
        if @EXPIRATIONDATE < @TRANSACTIONDATE
          raiserror('The expiration date must be after the transaction date.',13,1);
      end

    -- Assert that there's at least one primary member:

    if not exists(
      select 1 from @MEMBERS.nodes('MEMBERS/ITEM') as [MEMBERS]([ITEM])
      where [MEMBERS].[ITEM].value('ISPRIMARY[1]', 'bit') = 1
    )
      raiserror('Please select one member as a primary member.', 13, 1);

    -- Do not allow backdate transaction unless it is for a pending membership

    declare @CURRENTSTATUS tinyint = 0;
    declare @JOINDATE datetime;

    select
      @CURRENTSTATUS = STATUSCODE,
      @JOINDATE = JOINDATE
    from dbo.MEMBERSHIP 
    where ID = @MEMBERSHIPID;

    if exists(select *
              from dbo.MEMBERSHIPTRANSACTION 
              where MEMBERSHIPID = @MEMBERSHIPID
              and TRANSACTIONDATE > @TRANSACTIONDATE
             ) and @CURRENTSTATUS <> 2
      raiserror('Transaction date cannot be earlier than the last transaction of the membership.', 13,1);

    -- Bug 317265

    if @ISGIFT = 0
        set @SENDRENEWALCODE = 1;

    declare @MEMBERS_TABLE table (
      [ID] uniqueidentifier,
      [CONSTITUENTID] uniqueidentifier,
      [ISPRIMARY] bit,
      [MEMBERID] uniqueidentifier,
      [ISCAREGIVER] bit
    );

    insert into @MEMBERS_TABLE
      select
        T.members.value('(ID)[1]','uniqueidentifier') as 'ID',
        T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
        T.members.value('(ISPRIMARY)[1]','bit') as 'ISPRIMARY',
        T.members.value('(MEMBERID)[1]','uniqueidentifier') as 'MEMBERID',
        T.members.value('(ISCAREGIVER)[1]','bit') as 'ISCAREGIVER'
      from @MEMBERS.nodes('/MEMBERS/ITEM') T(members);

    update @MEMBERS_TABLE set
      ID = newid()
    where (ID is null)
    or (ID = '00000000-0000-0000-0000-000000000000');


    -- build a temporary table containing the values from the XML

    declare @CARDS_TABLE table (
      [ID] uniqueidentifier,
      [MEMBERSHIPCARDID] uniqueidentifier,
      [NAMEONCARD] nvarchar(700),
      [EXPIRATIONDATE] datetime,
      [MEMBERID] uniqueidentifier,
      [CONSTITUENTID] uniqueidentifier
    );

    insert into @CARDS_TABLE
      select 
        T.cards.value('(ID)[1]','uniqueidentifier') as 'ID',
        T.cards.value('(MEMBERSHIPCARDID)[1]','uniqueidentifier') as 'MEMBERSHIPCARDID',
        T.cards.value('(NAMEONCARD)[1]','nvarchar(700)') as 'NAMEONCARD',
        --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

        dateadd(ms, -003, dateadd(d, 1, cast(cast(T.cards.value('(EXPIRATIONDATE)[1]','datetime') as date) as datetime))) as 'EXPIRATIONDATE',
        T.cards.value('(../../ID)[1]','uniqueidentifier') as 'MEMBERID',
        T.cards.value('(../../CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID'
      from @MEMBERS.nodes('/MEMBERS/ITEM/MEMBERSHIPCARDS/ITEM') T(cards)
      where T.cards.value('(NAMEONCARD)[1]','nvarchar(700)') <> '';

    update @CARDS_TABLE set
      ID = newid()
    where (ID is null)
    or (ID = '00000000-0000-0000-0000-000000000000');

    update @CARDS_TABLE set
      MEMBERID = M.ID
    from @CARDS_TABLE C
    inner join @MEMBERS_TABLE M on C.CONSTITUENTID = M.CONSTITUENTID;

    -- Check to make sure the current constituent is associated with this membership

    if @GIVENBYID is null or @GIVENBYID <> @CONSTITUENTID 
      if not exists
      (
        select 1
        from @MEMBERS_TABLE
        where CONSTITUENTID = @CONSTITUENTID
      )
      begin
        raiserror('Please add the current constituent as a member or as the gift giver.',13,1);
      end

    -- Check to make sure at least one member is the primary member

    if not exists
    (
      select 1
      from @MEMBERS_TABLE
      where ISPRIMARY = 1
    )
    begin
      raiserror('Please select one member as a primary member.',13,1)
    end

    -- Check for correct number of children, members, and cards

    declare @NUMBEROFMEMBERS smallint;
    declare @CHILDREN smallint;
    declare @CARDSALLOWED smallint;
    declare @ALLOWMULTIPLEMEMBERSHIPS bit;

    select
      @CHILDREN = ML.CHILDRENALLOWED, 
      @NUMBEROFMEMBERS = ML.MEMBERSALLOWED, 
      @CARDSALLOWED = ML.CARDSALLOWED,
      @ALLOWMULTIPLEMEMBERSHIPS = MP.ALLOWMULTIPLEMEMBERSHIPS,
      @MEMBERSHIPPROGRAMID = MP.ID
    from dbo.MEMBERSHIPLEVEL ML
    inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
    where ML.ID = @MEMBERSHIPLEVELID;

    declare @ERRORMSG nvarchar(52);
    set @ERRORMSG = 'Only ' +convert(nvarchar(6), @NUMBEROFMEMBERS)+ ' members are allowed for this membership.';

    -- Number of members validation

    if @MEMBERS is null
      raiserror('Please enter at least one member.', 13, 1);

    if @NUMBEROFMEMBERS < (select count(ID) from @MEMBERS_TABLE) --and @NUMBEROFMEMBERS > 0

    begin
      raiserror(@ERRORMSG, 13, 1);
    end

    set @ERRORMSG = 'The number of children may not be larger than '+convert(nvarchar(6), @CHILDREN)+'.';

    -- Number of children validation

    if @CHILDREN < @NUMBEROFCHILDREN --and @CHILDREN > 0

    begin
      raiserror(@ERRORMSG, 13, 1);
    end

    --only validate cards if a number is set

    --if @CARDSALLOWED > 0

      --begin

        declare @CARDCOUNT smallint

        select @CARDCOUNT = count(ID)
        from @CARDS_TABLE;

        -- Number of cards validation

        set @ERRORMSG = 'This membership level only allows up to '+convert(nvarchar(6),@CARDSALLOWED)+' card(s).'
        if @CARDCOUNT > @CARDSALLOWED
        begin
          raiserror(@ERRORMSG, 13,1)
        end
      --end


    if @STATUSCODE = 2 --pending

      set @EXPIRATIONDATE = null;

    if 6 = (select TERMCODE from dbo.MEMBERSHIPLEVELTERM where ID = @MEMBERSHIPLEVELTERMID) --lifetime membership

      set @EXPIRATIONDATE = null;

    if @EXPIRATIONDATE is not null
      set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@EXPIRATIONDATE);

    if exists
    (
      select count(CONSTITID)
      from
      (
        select CONSTITUENTID CONSTITID
        from @MEMBERS_TABLE
      ) Result
      group by CONSTITID
      having count(*) > 1
    )
      raiserror('Please do not add a constituent more than once to the membership.', 13,1);

    -- check if this is a backdated transaction

    -- it is a backdated transaction if there are transactions after this one, or

    -- if the renewal window of the transaction date is not the current

    declare @ISLASTTRANSACTION bit = 1
    if exists(select 1
              from dbo.MEMBERSHIPTRANSACTION 
              where MEMBERSHIPID = @MEMBERSHIPID
              and TRANSACTIONDATE > @TRANSACTIONDATE)
      set @ISLASTTRANSACTION = 0;

    declare @ACTIONCODE tinyint;

    -- determine action code

    declare @PREVEXPRDATE datetime,
            @PREVLEVELID uniqueidentifier,
            @PREVACTIONCODE tinyint;

    select top 1
      @PREVEXPRDATE = EXPIRATIONDATE,
      @PREVLEVELID = MEMBERSHIPLEVELID,
      @PREVACTIONCODE = ACTIONCODE
    from dbo.MEMBERSHIPTRANSACTION
    where MEMBERSHIPID = @MEMBERSHIPID
    and TRANSACTIONDATE <= @TRANSACTIONDATE
    order by TRANSACTIONDATE desc, DATEADDED desc;

    set @ACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE(@PREVLEVELID,
                                                                           @PREVEXPRDATE,
                                                                           @PREVACTIONCODE,
                                                                           @MEMBERSHIPLEVELID,
                                                                           @TRANSACTIONDATE);

    if @ACTIONCODE = 0 and @CURRENTSTATUS <> 2--join, so add a new membership

      begin
        -- Multiple memberships validation

        if @ALLOWMULTIPLEMEMBERSHIPS = 0
          if exists
          (
            select 1 
            from dbo.MEMBER 
            inner join dbo.MEMBERSHIP
            on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
            where
              MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID 
              and STATUSCODE <> 1
              and MEMBER.ISDROPPED <> 1
              and MEMBER.CONSTITUENTID in (
                select CONSTITUENTID
                from @MEMBERS_TABLE
              )
          )
          begin
            raiserror('This membership program does not allow multiple memberships from the same constituent.', 13, 1);
          end


        insert into dbo.MEMBERSHIP
        (
          ID, 
          MEMBERSHIPPROGRAMID,
          MEMBERSHIPLEVELID, 
          MEMBERSHIPLEVELTERMID, 
          MEMBERSHIPLEVELTYPECODEID, 
          NUMBEROFCHILDREN,
          COMMENTS,
          ISGIFT,
          SENDRENEWALCODE,
          JOINDATE,
          EXPIRATIONDATE,
          STATUSCODE,
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED,
          GIVENBYID
        )
        values
        (
          @MEMBERSHIPID, 
          @MEMBERSHIPPROGRAMID,
          @MEMBERSHIPLEVELID, 
          @MEMBERSHIPLEVELTERMID, 
          @MEMBERSHIPTYPECODEID, 
          @NUMBEROFCHILDREN,
          @COMMENTS,
          @ISGIFT,
          @SENDRENEWALCODE,
          @TRANSACTIONDATE,
          @EXPIRATIONDATE,
          @STATUSCODE,
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CURRENTDATE, 
          @CURRENTDATE,
          @GIVENBYID
        );
      end
    else
      begin

        if (@EXPIRATIONDATE is not null) and (@JOINDATE is not null)
        begin
          if @EXPIRATIONDATE < @JOINDATE
            raiserror('The expiration date must come after the transaction date.',13,1);
        end

          -- If this is the last transaction, update membership

          if @ISLASTTRANSACTION = 1
            update dbo.MEMBERSHIP set
              MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID, 
              MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID, 
              MEMBERSHIPLEVELTYPECODEID = @MEMBERSHIPTYPECODEID, 
              NUMBEROFCHILDREN = @NUMBEROFCHILDREN,
              COMMENTS = @COMMENTS,
              ISGIFT = @ISGIFT,
              SENDRENEWALCODE = @SENDRENEWALCODE,
              EXPIRATIONDATE = @EXPIRATIONDATE,
              LASTRENEWEDON = case when @CURRENTSTATUS = 2 then LASTRENEWEDON else @TRANSACTIONDATE end,
              STATUSCODE = @STATUSCODE,
              CHANGEDBYID = @CHANGEAGENTID, 
              DATECHANGED = @CURRENTDATE,
              GIVENBYID = @GIVENBYID
            where
              ID = @MEMBERSHIPID;
      end

    if @CURRENTSTATUS = 2
      begin
        select top 1 @TRANSACTIONID = ID
        from dbo.MEMBERSHIPTRANSACTION
        where MEMBERSHIPID = @MEMBERSHIPID
        order by TRANSACTIONDATE desc;

        declare @NEWEXPIRATIONDATE datetime 
        set @NEWEXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @TRANSACTIONDATE);
        update dbo.MEMBERSHIPTRANSACTION
          set TRANSACTIONDATE = @TRANSACTIONDATE,
            EXPIRATIONDATE = @NEWEXPIRATIONDATE,
            REVENUESPLITID = @REVENUESPLITID,
            MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
            MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
            ISGIFT = @ISGIFT,
            DONORID = @GIVENBYID,
            NUMBEROFCHILDREN = @NUMBEROFCHILDREN,
            COMMENTS = @COMMENTS,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @TRANSACTIONID;

        update dbo.MEMBERSHIP
          set JOINDATE = @TRANSACTIONDATE,
            EXPIRATIONDATE = @NEWEXPIRATIONDATE,
            STATUSCODE = @STATUSCODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @MEMBERSHIPID;

        update @CARDS_TABLE set
          EXPIRATIONDATE = @NEWEXPIRATIONDATE
        where EXPIRATIONDATE = @EXPIRATIONDATE;

        set @ACTIONCODE = 1;
      end
    else
      begin    
        if @TRANSACTIONID is null
          set @TRANSACTIONID = newid();

        insert into dbo.MEMBERSHIPTRANSACTION
        (
          ID,
          MEMBERSHIPID,
          MEMBERSHIPLEVELID,
          MEMBERSHIPLEVELTERMID,
          MEMBERSHIPLEVELTYPECODEID,
          TRANSACTIONDATE,
          EXPIRATIONDATE,
          REVENUESPLITID,
          ISGIFT,
          DONORID,
          NUMBEROFCHILDREN,
          COMMENTS,
          ACTIONCODE,
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
          values
        (
          @TRANSACTIONID,
          @MEMBERSHIPID,
          @MEMBERSHIPLEVELID,
          @MEMBERSHIPLEVELTERMID,
          @MEMBERSHIPTYPECODEID,
          @TRANSACTIONDATE,
          @EXPIRATIONDATE,
          @REVENUESPLITID,
          @ISGIFT,
          @GIVENBYID,
          @NUMBEROFCHILDREN,
          @COMMENTS,
          @ACTIONCODE,
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CURRENTDATE, 
          @CURRENTDATE
        );

      end

    -- if this is not the last transaction then need to update the action code of the next transaction

    if @ISLASTTRANSACTION = 0
    begin
      declare @NEXTTRANSDATE datetime,
              @NEXTLEVELID uniqueidentifier,
              @NEXTTRANSID uniqueidentifier,
              @NEXTACTIONCODE tinyint;

      select top 1
        @NEXTTRANSDATE = TRANSACTIONDATE,
        @NEXTLEVELID = MEMBERSHIPLEVELID,
        @NEXTACTIONCODE = ACTIONCODE,
        @NEXTTRANSID = ID
      from dbo.MEMBERSHIPTRANSACTION
      where MEMBERSHIPID = @MEMBERSHIPID
      and TRANSACTIONDATE > @TRANSACTIONDATE
      order by TRANSACTIONDATE asc, DATEADDED asc;

      if @NEXTTRANSID is not null
        begin
          set @NEXTACTIONCODE = case @NEXTACTIONCODE
              when 4 then 4
              else
                dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE(@MEMBERSHIPLEVELID,
                                                                     @EXPIRATIONDATE,
                                                                     @ACTIONCODE,
                                                                     @NEXTLEVELID,
                                                                     @NEXTTRANSDATE)
              end

            update dbo.MEMBERSHIPTRANSACTION set
              ACTIONCODE = @NEXTACTIONCODE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE
            where ID = @NEXTTRANSID;
        end
    end      

    if @ACTIONCODE = 0
      begin
        -- The following commented out execute is refactored to be inline below.

        --exec dbo.USP_MEMBERSHIP_GETMEMBERS_ADDFROMXML @MEMBERSHIPID, @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;                        

        insert into dbo.[MEMBER] (
          [MEMBERSHIPID], 
          [CONSTITUENTID],
          [ID],
          [ISPRIMARY],                
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
        select
          @MEMBERSHIPID, 
          [CONSTITUENTID],
          [ID],
          [ISPRIMARY], 
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CURRENTDATE, 
          @CURRENTDATE
        from @MEMBERS_TABLE as [temp]
        where not exists (select 1 from dbo.MEMBER where MEMBER.ID = [temp].ID);

        -- The following commented out execute is refactored to be inline below.

        --exec dbo.USP_MEMBERSHIP_GETMEMBERSHIPCARDS_ADDFROMXML @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;

        insert into dbo.[MEMBERSHIPCARD] (
          [ID],
          [MEMBERID],
          [NAMEONCARD],
          [EXPIRATIONDATE],                
          COMMENTS,
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
        select
          [ID], 
          [MEMBERID],
          [NAMEONCARD],
          [EXPIRATIONDATE],
          '',
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CURRENTDATE, 
          @CURRENTDATE
        from @CARDS_TABLE as [temp]
        where not exists (select 1 from dbo.MEMBERSHIPCARD where MEMBERSHIPCARD.ID = [temp].ID);
      end
    else
      begin
        -- The following commented out execute is refactored to be inline below.

        --exec dbo.USP_MEMBERSHIP_GETMEMBER_UPDATEFROMXML @MEMBERSHIPID, @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;

        declare @contextCache varbinary(128);

        -- cache current context information 

        set @contextCache = CONTEXT_INFO();

        -- set CONTEXT_INFO to @CHANGEAGENTID 

        if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

        -- delete any items that no longer exist in the XML table

        update dbo.[MEMBER] set
          ISDROPPED = 1, 
          ISPRIMARY = 0,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where [MEMBER].ID in (
          select ID from dbo.UFN_MEMBERSHIP_GETMEMBERS(@MEMBERSHIPID)
          EXCEPT
          select ID from @MEMBERS_TABLE
        );

        -- reset CONTEXT_INFO to previous value 

        if not @contextCache is null
          set CONTEXT_INFO @contextCache;


        -- update the items that exist in the XML table and the db

        update dbo.[MEMBER] set
          [MEMBER].[CONSTITUENTID]=temp.[CONSTITUENTID],
          [MEMBER].[ID]=temp.[ID],
          [MEMBER].[ISPRIMARY]=temp.[ISPRIMARY],
          [MEMBER].CHANGEDBYID = @CHANGEAGENTID,
          [MEMBER].DATECHANGED = @CURRENTDATE
        from dbo.[MEMBER] inner join @MEMBERS_TABLE as [temp] on [MEMBER].ID = [temp].ID
        where ([MEMBER].[CONSTITUENTID]<>temp.[CONSTITUENTID]) or 
              ([MEMBER].[CONSTITUENTID] is null and temp.[CONSTITUENTID] is not null) or 
              ([MEMBER].[CONSTITUENTID] is not null and temp.[CONSTITUENTID] is null) or 
              ([MEMBER].[ID]<>temp.[ID]) or 
              ([MEMBER].[ID] is null and temp.[ID] is not null) or 
              ([MEMBER].[ID] is not null and temp.[ID] is null) or 
              ([MEMBER].[ISPRIMARY]<>temp.[ISPRIMARY]) or 
              ([MEMBER].[ISPRIMARY] is null and temp.[ISPRIMARY] is not null) or 
              ([MEMBER].[ISPRIMARY] is not null and temp.[ISPRIMARY] is null);

        -- insert new items

        insert into dbo.[MEMBER] (
          [MEMBERSHIPID], 
          [CONSTITUENTID],
          [ID],
          [ISPRIMARY],                
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
        select
          @MEMBERSHIPID, 
          [CONSTITUENTID],
          [ID],
          [ISPRIMARY], 
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CURRENTDATE, 
          @CURRENTDATE
        from @MEMBERS_TABLE as [temp]
        where not exists (select ID from dbo.[MEMBER] as data where data.ID = [temp].ID);

        -- The following commented out execute is refactored to be inline below.

        --exec dbo.USP_MEMBERSHIP_GETMEMBERSHIPCARDS_UPDATEFROMXML @MEMBERSHIPID, @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;

        -- cache current context information 

        set @contextCache = CONTEXT_INFO();

        -- set CONTEXT_INFO to @CHANGEAGENTID 

        if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

        -- delete any items that no longer exist in the XML table

        update dbo.[MEMBERSHIPCARD] set
          STATUSCODE = 2
        where [MEMBERSHIPCARD].ID in (
          -- JLM 3/3/2012 : WI 199320

          /*    Using a locking hint here to alleviate contention for the MEMBERSHIPCARD table.  I'm not
            a huge fan of adding locking hints vs refactoring, but given the nature of this subquery,
            the hint should be ok here since we have the context of the specific membership record. */
          select MEMBERSHIPCARD.ID
          from dbo.MEMBERSHIPCARD with (nolock)
          inner join dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID
          where MEMBER.MEMBERSHIPID = @MEMBERSHIPID
          EXCEPT
          select ID from @CARDS_TABLE
        );

        -- reset CONTEXT_INFO to previous value 

        if not @contextCache is null
          set CONTEXT_INFO @contextCache;


        -- if existing membership card is printed and has an expiration date, cancel the old card and issue a new card

        update dbo.MEMBERSHIPCARD set
          STATUSCODE = 2
        from dbo.MEMBERSHIPCARD 
        inner join @CARDS_TABLE TEMPTBL on TEMPTBL.ID = MEMBERSHIPCARD.ID
        where MEMBERSHIPCARD.STATUSCODE = 1 and (((MEMBERSHIPCARD.EXPIRATIONDATE is not null) and MEMBERSHIPCARD.EXPIRATIONDATE < TEMPTBL.EXPIRATIONDATE) or MEMBERSHIPCARD.NAMEONCARD <> TEMPTBL.NAMEONCARD);

        update TEMPTBL set
          ID = newid()
        from @CARDS_TABLE TEMPTBL
        inner join dbo.MEMBERSHIPCARD on TEMPTBL.ID = MEMBERSHIPCARD.ID
        where MEMBERSHIPCARD.STATUSCODE = 2 and (((MEMBERSHIPCARD.EXPIRATIONDATE is not null) and MEMBERSHIPCARD.EXPIRATIONDATE < TEMPTBL.EXPIRATIONDATE) or MEMBERSHIPCARD.NAMEONCARD <> TEMPTBL.NAMEONCARD);

        update TEMPTBL set
          EXPIRATIONDATE = null
        from @CARDS_TABLE TEMPTBL
        inner join dbo.MEMBERSHIPCARD on TEMPTBL.ID = MEMBERSHIPCARD.ID
        where MEMBERSHIPCARD.EXPIRATIONDATE is null;

        -- update the items that exist in the XML table and the db

        update dbo.[MEMBERSHIPCARD] set
          [MEMBERSHIPCARD].[ID]=temp.[ID],
          [MEMBERSHIPCARD].[NAMEONCARD]=temp.[NAMEONCARD],
          [MEMBERSHIPCARD].[EXPIRATIONDATE]=temp.[EXPIRATIONDATE],
          [MEMBERSHIPCARD].[MEMBERID]=temp.[MEMBERID],
          [MEMBERSHIPCARD].CHANGEDBYID = @CHANGEAGENTID,
          [MEMBERSHIPCARD].DATECHANGED = @CURRENTDATE
        from dbo.[MEMBERSHIPCARD]
        inner join @CARDS_TABLE as [temp] on [MEMBERSHIPCARD].ID = [temp].ID
        where ([MEMBERSHIPCARD].[ID]<>temp.[ID]) or 
              ([MEMBERSHIPCARD].[ID] is null and temp.[ID] is not null) or 
              ([MEMBERSHIPCARD].[ID] is not null and temp.[ID] is null) or 
              ([MEMBERSHIPCARD].[NAMEONCARD]<>temp.[NAMEONCARD]) or 
              ([MEMBERSHIPCARD].[NAMEONCARD] is null and temp.[NAMEONCARD] is not null) or 
              ([MEMBERSHIPCARD].[NAMEONCARD] is not null and temp.[NAMEONCARD] is null) or 
              ([MEMBERSHIPCARD].[EXPIRATIONDATE]<>temp.[EXPIRATIONDATE]) or 
              ([MEMBERSHIPCARD].[EXPIRATIONDATE] is null and temp.[EXPIRATIONDATE] is not null) or 
              ([MEMBERSHIPCARD].[EXPIRATIONDATE] is not null and temp.[EXPIRATIONDATE] is null) or
              ([MEMBERSHIPCARD].[MEMBERID]<>temp.[MEMBERID]) or 
              ([MEMBERSHIPCARD].[MEMBERID] is null and temp.[MEMBERID] is not null) or 
              ([MEMBERSHIPCARD].[MEMBERID] is not null and temp.[MEMBERID] is null);

        -- insert new items

        insert into dbo.[MEMBERSHIPCARD] (
          [ID], 
          [MEMBERID],
          [NAMEONCARD],
          [EXPIRATIONDATE],                
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
        select
          [ID], 
          [MEMBERID],
          [NAMEONCARD],
          [EXPIRATIONDATE],
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CURRENTDATE, 
          @CURRENTDATE
        from @CARDS_TABLE as [temp]
        where not exists (select ID from dbo.[MEMBERSHIPCARD] as data where data.ID = [temp].ID);
      end
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end