USP_RELATIONSHIPCONFIGURATION_BUILDCONFIGURATION

CREATE procedure dbo.USP_RELATIONSHIPCONFIGURATION_BUILDCONFIGURATION
(
    @CHANGEAGENTID uniqueidentifier = null
) as
    set nocount on;

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

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

    begin try
        insert into dbo.RELATIONSHIPCONFIGURATION
        (
            ID,
            GENDERCODE,
            APPLIESTOINDIVIDUALS,
            APPLIESTOORGANIZATIONS,
            APPLIESTOGROUPS,
            RELATESTOINDIVIDUALS,
            RELATESTOORGANIZATIONS,
            RELATESTOGROUPS,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            RELATIONSHIP.RELATIONSHIPTYPECODEID,
            0,
            case when EMPLOYEECORPORATERELATIONSHIPTYPE.ID is not null then 1
                when min(convert(int, RELATIONC.ISORGANIZATION) + convert(int, RELATIONC.ISGROUP)) = 0 then 1 
                else 0 end,
            max(convert(int, RELATIONC.ISORGANIZATION)),
            max(convert(int, RELATIONC.ISGROUP)),
            case when min(convert(int, RECIPROCALC.ISORGANIZATION) + convert(int, RECIPROCALC.ISGROUP)) = 0 then 1 
                else 0 end,
            case when EMPLOYEECORPORATERELATIONSHIPTYPE.ID is not null then 1
                else max(convert(int, RECIPROCALC.ISORGANIZATION)) end,
            max(convert(int, RECIPROCALC.ISGROUP)),
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from 
            dbo.RELATIONSHIP
        inner join
            dbo.CONSTITUENT RELATIONC on RELATIONC.ID = RELATIONSHIPCONSTITUENTID
        inner join
            dbo.CONSTITUENT RECIPROCALC on RECIPROCALC.ID = RECIPROCALCONSTITUENTID
        left join
            dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID
        where
            not exists(select ID from dbo.RELATIONSHIPCONFIGURATION where ID = RELATIONSHIP.RELATIONSHIPTYPECODEID)
        group by 
            RELATIONSHIP.RELATIONSHIPTYPECODEID, EMPLOYEECORPORATERELATIONSHIPTYPE.ID

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;