USP_CMSSOLICITCODE_DATA_SYNC

    CREATE procedure dbo.USP_CMSSOLICITCODE_DATA_SYNC
    (
    @MAXRECORDS int,
    @RECORDPROCESS int = 0 output,
    @NOOFERRORS int = 0 output
    )
    as
    begin

      declare @CONSENT as xml,
              @TRANSACTIONID as int,
              @CONSTITUENTID as  uniqueidentifier = null,
              @CHANGEAGENTID as uniqueidentifier,
              @TEMPTRANID uniqueidentifier,
              @CONSETNUPDATEID uniqueidentifier,
              @ERRORMESSAGE as nvarchar(1024) = '';

      declare @CONSENTSTATUS TABLE(ID uniqueidentifier, TRANSACTIONID int, TRANSACTIONGUIDID uniqueidentifier, CONSENT xml);

      insert into @CONSENTSTATUS (ID, TRANSACTIONID, TRANSACTIONGUIDID, CONSENT)
      select top(@MAXRECORDS) ID, TRANSACTIONID, TRANSACTIONGUIDID, CONSENT from CONSENTTRANSACTIONSTATUS where CONSENTUPDATE = 0;

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

      declare @CURRENTDATE datetime
      set @CURRENTDATE = getdate()

        while exists (select 1 from @CONSENTSTATUS)
          begin
            begin try
              select top 1 @TRANSACTIONID = TRANSACTIONID, @TEMPTRANID = TRANSACTIONGUIDID, @CONSETNUPDATEID = ID, @CONSENT = CONSENT
                from @CONSENTSTATUS
                order by ID asc


                 select @CONSTITUENTID = bmd.BILLTOCONSTITUENTID from CONSENTTRANSACTIONSTATUS cts 
                  inner join MembershipTransactions t on t.ID = cts.TRANSACTIONID
                  inner join BATCHMEMBERSHIPDUES bmd on bmd.TRANSACTIONID = @TEMPTRANID
                  inner join BATCH b on b.ID = bmd.BATCHID
                  left join BATCHMEMBERSHIPDUESBATCHSYSTEMMESSAGES m on m.BATCHMEMBERSHIPDUESID = bmd.ID
                  where b.STATUSCODE = 1 and m.ID is null;

              if (@CONSTITUENTID is not null and @CONSTITUENTID <> '00000000-0000-0000-0000-000000000000' and exists(select 1 from constituent where ID = @CONSTITUENTID))
                begin
                  exec USP_ADDUPDATE_CONTACTCONSENT @CONSTITUENTID ,@CONSENT;

                  exec USP_ADDUPDATE_CONSENTTRANSACTIONSTATUS @TRANSACTIONID, @TEMPTRANID, 1;

                  set @RECORDPROCESS = @RECORDPROCESS + 1
                end

              delete from @CONSENTSTATUS where TRANSACTIONGUIDID = @TEMPTRANID;
            end try

            begin catch
              set @NOOFERRORS = @NOOFERRORS + 1
              delete from @CONSENTSTATUS where TRANSACTIONGUIDID = @TEMPTRANID;
              set @ERRORMESSAGE = ERROR_MESSAGE();
              exec dbo.spAddUpdate_Error 0, 'CMS Solicit Code Data Sync Business Process', @ERRORMESSAGE, null
            end catch
          end
  return 0
end