USP_BBNC_CREATECONTACT

            CREATE procedure dbo.USP_BBNC_CREATECONTACT
            (
                @INDIVIDUALID uniqueidentifier,
                @ORGANIZATIONID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
            set nocount on;

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

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

            begin try
                if (not @INDIVIDUALID is null) and (not @ORGANIZATIONID is null)
                begin
                    declare @RELATIONSHIPTYPECODEID uniqueidentifier;
                    declare @RECIPROCALTYPECODEID uniqueidentifier;
                    declare @CONTACTTYPECODEID uniqueidentifier;

                    select top 1 
                        @RELATIONSHIPTYPECODEID = PRIMARYBUSINESSRELATIONSHIPTYPECODEID,
                        @RECIPROCALTYPECODEID = PRIMARYBUSINESSRECIPROCALTYPECODEID,
                        @CONTACTTYPECODEID = CONTACTTYPECODEID
                    from dbo.NETCOMMUNITYDEFAULTCODEMAP;

                    /* TODO For now, I'm not requiring CONTACTTYPECODEID, so just pick one if it's null */
                    if @CONTACTTYPECODEID is null
                        select top 1 @CONTACTTYPECODEID = ID from dbo.CONTACTTYPECODE;

                    if (select count(ID) from dbo.RELATIONSHIP 
                        where RELATIONSHIPCONSTITUENTID = @INDIVIDUALID and RECIPROCALCONSTITUENTID = @ORGANIZATIONID
                            and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID and RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID) = 0
                    begin
                        insert into dbo.RELATIONSHIP(ID, RELATIONSHIPCONSTITUENTID, RECIPROCALCONSTITUENTID, RELATIONSHIPTYPECODEID, RECIPROCALTYPECODEID, ISCONTACT, CONTACTTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(newid(), @INDIVIDUALID, @ORGANIZATIONID, @RELATIONSHIPTYPECODEID, @RECIPROCALTYPECODEID, 1, @CONTACTTYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                        declare @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0
                        declare @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100
                        declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null
                        declare @PRIMARYCONSTITUENTTYPE tinyint = 0
                        declare @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0
                        declare @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100
                        declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null
                        declare @RECIPROCALCONSTITUENTTYPE tinyint = 0

                        select @PRIMARYCONSTITUENTTYPE = case 
                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household

                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group

                            when ISORGANIZATION = 1 then 1 --Organization

                            else 0 end --Individual

                        from dbo.CONSTITUENT where ID = @INDIVIDUALID;

                        select @RECIPROCALCONSTITUENTTYPE = case 
                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household

                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group

                            when ISORGANIZATION = 1 then 1 --Organization

                            else 0 end --Individual

                        from dbo.CONSTITUENT where ID = @ORGANIZATIONID;

                        -- add default recognition credits if creating a new relationship

                        select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
                            @PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                            @PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
                        from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                        where RRD.CONSTITUENTTYPECODE=@PRIMARYCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPECODEID

                        select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
                            @RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                            @RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
                        from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                        where RRD.CONSTITUENTTYPECODE=@RECIPROCALCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RECIPROCALTYPECODEID

                        exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @INDIVIDUALID, @ORGANIZATIONID,
                            NULL, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
                            @PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 
                            @RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;
                    end
                    else
                    begin
                        if (select top 1 ISCONTACT from dbo.RELATIONSHIP 
                            where RELATIONSHIPCONSTITUENTID = @INDIVIDUALID and RECIPROCALCONSTITUENTID = @ORGANIZATIONID
                                and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID and RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID order by ISCONTACT desc) = 0
                        begin
                            update dbo.RELATIONSHIP
                                set ISCONTACT = 1,
                                    CONTACTTYPECODEID = @CONTACTTYPECODEID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where RELATIONSHIPCONSTITUENTID = @INDIVIDUALID and RECIPROCALCONSTITUENTID = @ORGANIZATIONID
                                and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID and RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID;
                        end
                    end
                end    
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;