USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTBUSINESSRULESSETTINGS_2

Parameters

Parameter	Parameter Type	Mode
@CHANGEAGENTID	uniqueidentifier	IN
@NAMECODE	tinyint	IN
@SIMILARADDRESSCODE	tinyint	IN
@UNSIMILARADDRESSCODE	tinyint	IN
@NEWADDRESSENDDATECODE	tinyint	IN
@NEWADDRESSPRIMARYCODE	tinyint	IN
@BIRTHDATERULECODE	tinyint	IN
@DIFFERENTPHONECODE	tinyint	IN
@NEWPHONEENDDATECODE	tinyint	IN
@NEWPHONEPRIMARYCODE	tinyint	IN
@DIFFERENTEMAILCODE	tinyint	IN
@NEWEMAILENDDATECODE	tinyint	IN
@NEWEMAILPRIMARYCODE	tinyint	IN
@CREATEHISTORICALNAMECODE	tinyint	IN
@BATCHTEMPLATEID	uniqueidentifier	IN
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTBUSINESSRULESSETTINGS_2(
      @CHANGEAGENTID uniqueidentifier = null,
      @NAMECODE tinyint,
    @SIMILARADDRESSCODE tinyint,
    @UNSIMILARADDRESSCODE tinyint,
    @NEWADDRESSENDDATECODE tinyint,
    @NEWADDRESSPRIMARYCODE tinyint,
    @BIRTHDATERULECODE tinyint,
    @DIFFERENTPHONECODE tinyint,
    @NEWPHONEENDDATECODE tinyint,
    @NEWPHONEPRIMARYCODE tinyint,
    @DIFFERENTEMAILCODE tinyint,
    @NEWEMAILENDDATECODE tinyint,
    @NEWEMAILPRIMARYCODE tinyint,
    @CREATEHISTORICALNAMECODE tinyint,
    @BATCHTEMPLATEID uniqueidentifier
)
with execute as OWNER
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

  declare @SQLTOEXEC nvarchar(1000);
  declare @QUERY nvarchar(1000);

    begin try
        -- handle updating the data

    if @BATCHTEMPLATEID is null
    begin
      update dbo.CONSTITUENTBUSINESSRULESSETTINGS set
              NAMECODE = @NAMECODE,
        SIMILARADDRESSCODE = @SIMILARADDRESSCODE,
        UNSIMILARADDRESSCODE = @UNSIMILARADDRESSCODE,
        NEWADDRESSENDDATECODE = @NEWADDRESSENDDATECODE,
        NEWADDRESSPRIMARYCODE = @NEWADDRESSPRIMARYCODE,      
        BIRTHDATERULECODE = @BIRTHDATERULECODE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE,
        DIFFERENTPHONECODE = @DIFFERENTPHONECODE,
        NEWPHONEENDDATECODE = @NEWPHONEENDDATECODE,
        NEWPHONEPRIMARYCODE = @NEWPHONEPRIMARYCODE,
        DIFFERENTEMAILCODE = @DIFFERENTEMAILCODE,
        NEWEMAILENDDATECODE = @NEWEMAILENDDATECODE,
        NEWEMAILPRIMARYCODE = @NEWEMAILPRIMARYCODE,
        CREATEHISTORICALNAMECODE = @CREATEHISTORICALNAMECODE

      -- Update batch templates where global settings are used.
      set @QUERY ='with xmlnamespaces (default ''bb_appfx_dataforms'')
      update dbo.BATCHTEMPLATE set 
      PARAMETERSXML.modify(''replace value of (/DataFormItem/Values/fv[@ID="idAttribute"]/Value/text())[1] with  "idValue"'')
      where BATCHTEMPLATE.BATCHTYPECATALOGID in (''877DBC83-98F9-4008-98A0-902FDB35E819'', ''196a2540-005a-4547-91a7-b301c464e28c'', ''326c43a6-d162-4fd4-8d61-fef9a0ee8c5e'') and PARAMETERSXML.value(''(/DataFormItem/Values/fv[@ID="USEGLOBALSETTINGS"]/Value/text())[1]'',''VARCHAR(10)'') = ''true'';'

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','NAMECODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NAMECODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','SIMILARADDRESSCODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@SIMILARADDRESSCODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','UNSIMILARADDRESSCODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@UNSIMILARADDRESSCODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWADDRESSENDDATECODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWADDRESSENDDATECODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWADDRESSPRIMARYCODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWADDRESSPRIMARYCODE);
      exec sp_executesql @SQLTOEXEC;


      set @SQLTOEXEC = replace(@QUERY,'idAttribute','BIRTHDATERULECODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@BIRTHDATERULECODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','DIFFERENTPHONECODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@DIFFERENTPHONECODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWPHONEENDDATECODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWPHONEENDDATECODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWPHONEPRIMARYCODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWPHONEPRIMARYCODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','DIFFERENTEMAILCODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@DIFFERENTEMAILCODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWEMAILENDDATECODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWEMAILENDDATECODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWEMAILPRIMARYCODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWEMAILPRIMARYCODE);
      exec sp_executesql @SQLTOEXEC;

      set @SQLTOEXEC = replace(@QUERY,'idAttribute','CREATEHISTORICALNAMECODE');
      set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@CREATEHISTORICALNAMECODE);
      exec sp_executesql @SQLTOEXEC;
    end

    -- Update Import Processes for batch template settings where the batch template uses global settings.
    set @QUERY ='with xmlnamespaces (default ''bb_appfx_dataforms'')
    update dbo.IMPORTPROCESS set 
      PARAMETERSXML.modify(''replace value of (/DataFormItem/Values/fv[@ID="idAttribute"]/Value/text())[1] with  "idValue"'')';

    if @BATCHTEMPLATEID is null
      set @QUERY = @QUERY +' from dbo.BATCHTEMPLATE where BATCHTEMPLATE.BATCHTYPECATALOGID in (''877DBC83-98F9-4008-98A0-902FDB35E819'', ''196a2540-005a-4547-91a7-b301c464e28c'', ''326c43a6-d162-4fd4-8d61-fef9a0ee8c5e'')'
                          + ' and IMPORTPROCESS.BATCHTEMPLATEID = BATCHTEMPLATE.ID and (BATCHTEMPLATE.PARAMETERSXML.value(''(/DataFormItem/Values/fv[@ID="USEGLOBALSETTINGS"]/Value/text())[1]'',''VARCHAR(10)'') = ''true'')';
    else
      set @QUERY = @QUERY +'where IMPORTPROCESS.BATCHTEMPLATEID =''' + convert(nvarchar(255), @BATCHTEMPLATEID) + ''''
                          + ' and IMPORTPROCESS.PARAMETERSXML.value(''(/DataFormItem/Values/fv[@ID="USEGLOBALSETTINGS"]/Value/text())[1]'',''VARCHAR(10)'') = ''true'';'

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','NAMECODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NAMECODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','SIMILARADDRESSCODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@SIMILARADDRESSCODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','UNSIMILARADDRESSCODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@UNSIMILARADDRESSCODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWADDRESSENDDATECODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWADDRESSENDDATECODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWADDRESSPRIMARYCODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWADDRESSPRIMARYCODE);
    exec sp_executesql @SQLTOEXEC;


    set @SQLTOEXEC = replace(@QUERY,'idAttribute','BIRTHDATERULECODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@BIRTHDATERULECODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','DIFFERENTPHONECODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@DIFFERENTPHONECODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWPHONEENDDATECODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWPHONEENDDATECODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWPHONEPRIMARYCODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWPHONEPRIMARYCODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','DIFFERENTEMAILCODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@DIFFERENTEMAILCODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWEMAILENDDATECODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWEMAILENDDATECODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','NEWEMAILPRIMARYCODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@NEWEMAILPRIMARYCODE);
    exec sp_executesql @SQLTOEXEC;

    set @SQLTOEXEC = replace(@QUERY,'idAttribute','CREATEHISTORICALNAMECODE');
    set @SQLTOEXEC = replace(@SQLTOEXEC,'idValue',@CREATEHISTORICALNAMECODE);
    exec sp_executesql @SQLTOEXEC;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;