USP_CONSTITUENTUPDATEBATCH_CUSTOMVALIDATE

CREATE procedure dbo.USP_CONSTITUENTUPDATEBATCH_CUSTOMVALIDATE
(
    @BATCHROWID uniqueidentifier,
    @BATCHNUMBER nvarchar(100),
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @BATCHOWNERID uniqueidentifier
)
as
begin
    set nocount on;

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

    declare @CURRENTDATE datetime = getdate();
    declare @EXISTINGCONSTITUENTID uniqueidentifier;
    declare @EXISTINGCONSTITUENTNAME nvarchar(154);

    select
        @EXISTINGCONSTITUENTID = BATCHCONSTITUENTUPDATE.PRIMARYRECORDID,
        @EXISTINGCONSTITUENTNAME = CONSTITUENT.NAME
    from
        dbo.BATCHCONSTITUENTUPDATE
        inner join dbo.CONSTITUENT on BATCHCONSTITUENTUPDATE.PRIMARYRECORDID = CONSTITUENT.ID
    where
        BATCHCONSTITUENTUPDATE.ID = @BATCHROWID;

    --Warn the user of duplicate relationships in the batch row.

    if exists
    (
        select
            1
        from
            dbo.BATCHCONSTITUENTUPDATERELATIONSHIPS
        where
            BATCHCONSTITUENTUPDATEID = @BATCHROWID
        group by
            RECIPROCALCONSTITUENTID, RELATIONSHIPTYPECODEID, RECIPROCALTYPECODEID
        having
            count(*) > 1
    )
    begin
        exec dbo.USP_BATCHCONSTITUENTUPDATEBATCHSYSTEMMESSAGES_ADD @BATCHROWID, 'You cannot add duplicate relationships for this individual.', @CHANGEAGENTID, 0, 'RELATIONSHIPS', '', 0, 2;
    end

    --warn the user of a relationship with the reciprocal constituent if it exists.

    insert into dbo.BATCHCONSTITUENTUPDATEBATCHSYSTEMMESSAGES
    (
        BATCHCONSTITUENTUPDATEID, 
        MESSAGETEXT, 
        MESSAGETYPECODE, 
        SEVERITYCODE, 
        ORIGINCODE,
        INVALIDFIELDID,
        DATEADDED, 
        DATECHANGED, 
        ADDEDBYID, 
        CHANGEDBYID
    )                                    
    select
        @BATCHROWID,
        @EXISTINGCONSTITUENTNAME + ' and ' + RECIPROCAL.NAME + ' are already related.  '
            + @EXISTINGCONSTITUENTNAME + ' is the ' + dbo.UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION(RELATIONSHIP.RELATIONSHIPTYPECODEID) + '; '
            + RECIPROCAL.NAME + ' is the ' + dbo.UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION(RELATIONSHIP.RECIPROCALTYPECODEID)
            + '.  Please review additional relationships.',
        0,
        1, 
        2,
        'RELATIONSHIPS',
        @CURRENTDATE, 
        @CURRENTDATE, 
        @CHANGEAGENTID, 
        @CHANGEAGENTID
    from
        dbo.BATCHCONSTITUENTUPDATERELATIONSHIPS RT
        left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @EXISTINGCONSTITUENTID  
            and RT.RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        left join dbo.CONSTITUENT RECIPROCAL on RT.RECIPROCALCONSTITUENTID = RECIPROCAL.ID
    where
        RELATIONSHIP.ID is not null
        and RT.BATCHCONSTITUENTUPDATEID = @BATCHROWID;

    -- Set the row message when 'Generate row exception' rule is triggerd for constituentuent update rules

     update dbo.BATCHCONSTITUENTUPDATEBATCHSYSTEMMESSAGES
        set MESSAGETEXT = 'Data in batch is different from data for this constituent. Click here to review.'
      where 
        BATCHCONSTITUENTUPDATEID = @BATCHROWID and 
        MESSAGETYPECODE = 1 and 
        SEVERITYCODE = 0 and 
        exists(select
                  'x'
                from dbo.BATCHCONSTITUENTUPDATE where ID= @BATCHROWID and DOMANUALREVIEWFORAUTOMATCH = 1);    

end