USP_GLOBALCHANGE_UPDATEMEMBERSHIPSTATUS

        CREATE procedure dbo.USP_GLOBALCHANGE_UPDATEMEMBERSHIPSTATUS
        (
            @CHANGEAGENTID uniqueidentifier = null,
            @ASOF as datetime = null,
            @NUMBERADDED int = 0 output,
            @NUMBEREDITED int = 0 output,
            @NUMBERDELETED int = 0 output,
            @IDSETREGISTERID uniqueidentifier = null
        )
        as            
            set nocount off;

            declare @CURRENTDATE date
            set @CURRENTDATE = getdate()
            set @NUMBERADDED = 0;
            set @NUMBEREDITED = 0;
            set @NUMBERDELETED = 0; 

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

          begin try
                      if @IDSETREGISTERID is null
            begin
              update dbo.MEMBERSHIP set STATUSCODE = NEWMEMBERSHIP.NEWSTATUSCODE,
                                              CHANGEDBYID = @CHANGEAGENTID,
                                              DATECHANGED = @CURRENTDATE    
             from (select case
                          when @CURRENTDATE > DATEADD(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE) then (MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE) --When the current date is greater than the after expiration date renewal period.

                          when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID is not null and MLR1.INTERVALCODE = 12 or @CURRENTDATE <= DATEADD(month,MLR1.INTERVALCODE+1,MEMBERSHIP.EXPIRATIONDATE)) then MLR1.STATUSCODE -- When the current date is within the first rules period

                          when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID is not null and MLR2.INTERVALCODE = 12 or @CURRENTDATE <= DATEADD(month,(MLR1.INTERVALCODE+1 + MLR2.INTERVALCODE+1),MEMBERSHIP.EXPIRATIONDATE)) then MLR2.STATUSCODE -- When the current date is within the second rules period

                          when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID is not null and MLR3.INTERVALCODE = 12 or @CURRENTDATE <= DATEADD(month,(MLR3.INTERVALCODE+1 + MLR1.INTERVALCODE+1 + MLR2.INTERVALCODE+1),MEMBERSHIP.EXPIRATIONDATE)) then MLR3.STATUSCODE-- When the current date is within the third rules period

                          else MEMBERSHIP.STATUSCODE
                          end as NEWSTATUSCODE,
                          MEMBERSHIP.MEMBERSHIPLEVELID,
                          MEMBERSHIP.MEMBERSHIPPROGRAMID,
                          MEMBERSHIP.ID as MEMBERSHIPID
                         from dbo.MEMBERSHIP 
                         inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID   
                         left join dbo.MEMBERSHIPLEVELRENEWAL MLR1 on MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID = MLR1.ID
                         left join dbo.MEMBERSHIPLEVELRENEWAL MLR2 on MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID = MLR2.ID
                         left join dbo.MEMBERSHIPLEVELRENEWAL MLR3 on MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID = MLR3.ID
                         where (MEMBERSHIP.STATUSCODE <> MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE or
                               ( MEMBERSHIP.STATUSCODE = MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE and @CURRENTDATE < DATEADD(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)) )
                               and MEMBERSHIP.STATUSCODE not in (1,2)) as NEWMEMBERSHIP      
             where @CURRENTDATE > MEMBERSHIP.EXPIRATIONDATE     
             and NEWMEMBERSHIP.NEWSTATUSCODE <> MEMBERSHIP.STATUSCODE  
             and NEWMEMBERSHIP.MEMBERSHIPID = MEMBERSHIP.ID
             set @NUMBEREDITED = @@ROWCOUNT
            end
            else
            begin
              update dbo.MEMBERSHIP set STATUSCODE = NEWMEMBERSHIP.NEWSTATUSCODE,
                                              CHANGEDBYID = @CHANGEAGENTID,
                                              DATECHANGED = @CURRENTDATE    
             from (select case
                          when @CURRENTDATE > DATEADD(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE) then (MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE)  --When the current date is greater than the after expiration date renewal period.

                          when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID is not null and MLR1.INTERVALCODE = 12 or @CURRENTDATE <= DATEADD(month,MLR1.INTERVALCODE+1,MEMBERSHIP.EXPIRATIONDATE)) then MLR1.STATUSCODE -- When the current date is within the first rules period

                          when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID is not null and MLR2.INTERVALCODE = 12 or @CURRENTDATE <= DATEADD(month,(MLR1.INTERVALCODE+1 + MLR2.INTERVALCODE+1),MEMBERSHIP.EXPIRATIONDATE)) then MLR2.STATUSCODE -- When the current date is within the second rules period

                          when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID is not null and MLR3.INTERVALCODE = 12 or @CURRENTDATE <= DATEADD(month,(MLR3.INTERVALCODE+1 + MLR1.INTERVALCODE+1 + MLR2.INTERVALCODE+1),MEMBERSHIP.EXPIRATIONDATE)) then MLR3.STATUSCODE-- When the current date is within the third rules period

                          else MEMBERSHIP.STATUSCODE
                          end as NEWSTATUSCODE,
                          MEMBERSHIP.MEMBERSHIPLEVELID,
                          MEMBERSHIP.MEMBERSHIPPROGRAMID,
                          MEMBERSHIP.ID as MEMBERSHIPID
                         from dbo.MEMBERSHIP 
                         inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                         inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) as SELECTION on MEMBERSHIP.MEMBERSHIPPROGRAMID = SELECTION.ID        
                         left join dbo.MEMBERSHIPLEVELRENEWAL MLR1 on MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID = MLR1.ID
                         left join dbo.MEMBERSHIPLEVELRENEWAL MLR2 on MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID = MLR2.ID
                         left join dbo.MEMBERSHIPLEVELRENEWAL MLR3 on MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID = MLR3.ID
                           where (MEMBERSHIP.STATUSCODE <> MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE or
                               ( MEMBERSHIP.STATUSCODE = MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE and @CURRENTDATE < DATEADD(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)) )
                               and MEMBERSHIP.STATUSCODE not in (1,2)) as NEWMEMBERSHIP      
             inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) as SELECTION on NEWMEMBERSHIP.MEMBERSHIPPROGRAMID = SELECTION.ID            
             where @CURRENTDATE > MEMBERSHIP.EXPIRATIONDATE     
             and NEWMEMBERSHIP.NEWSTATUSCODE <> MEMBERSHIP.STATUSCODE 
             and NEWMEMBERSHIP.MEMBERSHIPID = MEMBERSHIP.ID
             set @NUMBEREDITED = @@ROWCOUNT
            end
            end try

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