USP_CREATE_HISTORICALNAME

CREATE procedure dbo.USP_CREATE_HISTORICALNAME 
(
    @ID uniqueidentifier,
    @KEYNAME nvarchar(100) = '',
    @FIRSTNAME nvarchar(50) = '',
    @MIDDLENAME nvarchar(50) = '',
    @TITLECODEID uniqueidentifier = null,
    @SUFFIXCODEID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin

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

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

    declare @HISTORICALNAMETYPEID  uniqueidentifier = '98AFE848-8F04-4479-8430-64C860512DD4'

    declare @EXISTINGKEYNAME nvarchar(100), @EXISTINGFIRSTNAME nvarchar(50), @EXISTINGMIDDLENAME nvarchar(50)
    declare @EXISTINGSUFFIXCODEID uniqueidentifier, @EXISTINGTITLECODEID uniqueidentifier

    select @EXISTINGKEYNAME = KEYNAME, @EXISTINGFIRSTNAME = FIRSTNAME, @EXISTINGMIDDLENAME = MIDDLENAME, @EXISTINGSUFFIXCODEID = SUFFIXCODEID, @EXISTINGTITLECODEID = TITLECODEID
    from dbo.CONSTITUENT
    where ID = @ID

    -- If there is a conflict write out old name to alias table as historical name.

   if not ((@EXISTINGFIRSTNAME = '' or @EXISTINGFIRSTNAME = @FIRSTNAME or @FIRSTNAME = '') and 
            (@EXISTINGKEYNAME = '' or @EXISTINGKEYNAME = @KEYNAME or @KEYNAME = '') and
            (@EXISTINGMIDDLENAME = '' or @EXISTINGMIDDLENAME = @MIDDLENAME or @MIDDLENAME = '') and
            (@EXISTINGSUFFIXCODEID is null or @SUFFIXCODEID is null or @EXISTINGSUFFIXCODEID = @SUFFIXCODEID ) and
            (@EXISTINGTITLECODEID is null  or @TITLECODEID is null or @EXISTINGTITLECODEID = @TITLECODEID))
   begin
        begin try
            begin try
                insert into dbo.ALIAS
                    (CONSTITUENTID, ALIASTYPECODEID, KEYNAME, FIRSTNAME,  MIDDLENAME,  SUFFIXCODEID, TITLECODEID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                values
                    (@ID,@HISTORICALNAMETYPEID, @EXISTINGKEYNAME, @EXISTINGFIRSTNAME, @EXISTINGMIDDLENAME, @EXISTINGSUFFIXCODEID,  @EXISTINGTITLECODEID, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
            end try
            begin catch
              --assumption that it failed either 

              --1. ERROR_MESSAGE contains FK_ALIAS_ALIASTYPECODEID. The ID in dbo.ALIASTYPECODE is either different from the hard coded value for  "Historical Name" or there isn't one.

              --2. ERROR_MESSAGE contains UIX_ALIAS_CONSTITUENTID_KEYNAME_FIRSTNAME_MIDDLENAME_TITLECODEID_TITLE2CODEID_SUFFIXCODEID_SUFFIX2CODEID.  

              --   A duplicate name already exists in the alias table for this constituent.


                if CHARINDEX('FK_ALIAS_ALIASTYPECODEID',ERROR_MESSAGE()) > 0 
                begin
                    -- get Historical Name type code. If there is not an existing one, create one

                    declare @ALTERNATE_HISTORICALNAMETYPECODEID uniqueidentifier = null
                    SELECT @ALTERNATE_HISTORICALNAMETYPECODEID = dbo.UFN_ALIASTYPECODE_GETID('Historical Name')

                    if @ALTERNATE_HISTORICALNAMETYPECODEID is null
                    begin
                        insert into dbo.ALIASTYPECODE (ID,DESCRIPTION,ACTIVE,SEQUENCE,ADDEDBYID,CHANGEDBYID,DATEADDED, DATECHANGED) 
                        select @HISTORICALNAMETYPEID,'Historical Name',1,
                        isnull(MAX(sequence), 0)+1 ,
                        @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE from dbo.ALIASTYPECODE;
                        set @ALTERNATE_HISTORICALNAMETYPECODEID = @HISTORICALNAMETYPEID
                    end

                    -- try  insert again with either new or alternate alias type code

                     insert into dbo.ALIAS
                        (CONSTITUENTID, ALIASTYPECODEID, KEYNAME, FIRSTNAME,  MIDDLENAME,  SUFFIXCODEID, TITLECODEID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                    values
                        (@ID,@ALTERNATE_HISTORICALNAMETYPECODEID, @EXISTINGKEYNAME, @EXISTINGFIRSTNAME, @EXISTINGMIDDLENAME, @EXISTINGSUFFIXCODEID,  @EXISTINGTITLECODEID, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
                end
                else
                  exec dbo.USP_RAISE_ERROR;

            end catch
        end try 
        begin catch 
            IF NOT (CHARINDEX('UIX_ALIAS_CONSTITUENTID_KEYNAME_FIRSTNAME_MIDDLENAME_TITLECODEID_TITLE2CODEID_SUFFIXCODEID_SUFFIX2CODEID',ERROR_MESSAGE()) > 0 )
                 exec dbo.USP_RAISE_ERROR;

       end catch
     end
end