USP_CONSTITUENTUPDATEBATCH_APPLYBIRTHDATERULES

CREATE procedure dbo.USP_CONSTITUENTUPDATEBATCH_APPLYBIRTHDATERULES(
@ID uniqueidentifier,
@PRIMARYRECORDID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@BIRTHDATERULECODE tinyint = 1
)
as
begin

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @BIRTHDATE UDT_FUZZYDATE
    declare @BIRTHDATE_YR nvarchar(4)
    declare @BIRTHDATE_M nvarchar(2)
    declare @BIRTHDATE_D nvarchar(2)
    declare @EXISTINGBIRTHDATE UDT_FUZZYDATE
    declare @EXISTINGBIRTHDATE_YR nvarchar(4)
    declare @EXISTINGBIRTHDATE_M nvarchar(2)
    declare @EXISTINGBIRTHDATE_D nvarchar(2)

    -- Get existing constituent birth date

    select 
        @EXISTINGBIRTHDATE = BIRTHDATE,
        @EXISTINGBIRTHDATE_YR = SUBSTRING(BIRTHDATE,1,4),
        @EXISTINGBIRTHDATE_M = SUBSTRING(BIRTHDATE,5,2),
        @EXISTINGBIRTHDATE_D = SUBSTRING(BIRTHDATE,7,2)
    from dbo.CONSTITUENT
    where ID = @PRIMARYRECORDID

    -- Get birth date in batch

    select 
        @BIRTHDATE = BIRTHDATE,
        @BIRTHDATE_YR = SUBSTRING(BIRTHDATE,1,4),
        @BIRTHDATE_M = SUBSTRING(BIRTHDATE,5,2),
        @BIRTHDATE_D = SUBSTRING(BIRTHDATE,7,2)
    from dbo.BATCHCONSTITUENTUPDATE
    where ID = @ID

    declare @CONFLICT bit = 1;

    -- check to see if there are any conflicts.  If none, we are allowed to apply any new information.


    if 
       (@EXISTINGBIRTHDATE_YR = '0000' or @EXISTINGBIRTHDATE_YR = @BIRTHDATE_YR or @BIRTHDATE_YR = '0000') and 
       (@EXISTINGBIRTHDATE_M = '00' or @EXISTINGBIRTHDATE_M = @BIRTHDATE_M or @BIRTHDATE_M = '00') and
       (@EXISTINGBIRTHDATE_D = '00' or @EXISTINGBIRTHDATE_D = @BIRTHDATE_D or @BIRTHDATE_D = '00') 
        set @CONFLICT = 0


    --If the setting is replace and there are conflicts, put in the new value.

    --If there are no conflicts we join with existing values  


    if @CONFLICT = 0
        update dbo.BATCHCONSTITUENTUPDATE set    
            BIRTHDATE =  coalesce(NULLIF(@BIRTHDATE_YR,'0000'),@EXISTINGBIRTHDATE_YR) + coalesce(NULLIF(@BIRTHDATE_M,'00'),@EXISTINGBIRTHDATE_M) + coalesce(NULLIF(@BIRTHDATE_D,'00'),@EXISTINGBIRTHDATE_D),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID  
    else
      begin
            -- If there?s a conflict and setting is ignore we want to keep the original value.  If the setting is replace and there is a conflict, we don't need to do anything.

            if @BIRTHDATERULECODE = 0 
                 update dbo.BATCHCONSTITUENTUPDATE set
                BIRTHDATE = @EXISTINGBIRTHDATE,
                CHANGEDBYID  = @CHANGEAGENTID,
                DATECHANGED  = @CURRENTDATE            
            where ID = @ID 
      end

end