USP_RELATIONSHIPCONFIGURATION_CONFIGURE
This procedure configures the relationship type settings for a pair of relationship types.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @RECIPROCALCONSTITUENTID | uniqueidentifier | IN | |
| @RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE
(
@CONSTITUENTID uniqueidentifier,
@RECIPROCALCONSTITUENTID uniqueidentifier,
@RELATIONSHIPTYPECODEID uniqueidentifier,
@RECIPROCALTYPECODEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CURRENTDATE datetime
)
as
begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
--declare configuration settings
declare @APPLIESTOINDIVIDUAL bit;
declare @APPLIESTOORG bit;
declare @APPLIESTOGROUP bit;
declare @APPLIESTOSTUDENT bit;
declare @RELATESTOINDIVIDUAL bit;
declare @RELATESTOORG bit;
declare @RELATESTOGROUP bit;
declare @RELATESTOSTUDENT bit;
--Get relationship constituent type
declare @RELATIONSHIPCONSTITUENTTYPE int;
select
@RELATIONSHIPCONSTITUENTTYPE =
case ISGROUP
when 1 then 2
else
case ISORGANIZATION
when 1 then 1
else 0
end
end
from dbo.CONSTITUENT
where ID = @CONSTITUENTID;
--Get reciprocal relationship constituent type
declare @RECIPRICALCONSTITUENTTYPE int;
select
@RECIPRICALCONSTITUENTTYPE =
case ISGROUP
when 1 then 2
else
case ISORGANIZATION
when 1 then 1
else 0
end
end
from dbo.CONSTITUENT
where ID = @RECIPROCALCONSTITUENTID;
--used for readability in conditionals below
declare @RELATIONSHIPTYPECONFIGEXISTS bit;
declare @RECIPROCALTYPECONFIGEXISTS bit;
declare @RELATIONSHIPTYPESMATCH bit;
select @RELATIONSHIPTYPECONFIGEXISTS = case when exists( select ID from dbo.RELATIONSHIPCONFIGURATION where ID = @RELATIONSHIPTYPECODEID ) then 1 else 0 end
select @RECIPROCALTYPECONFIGEXISTS = case when exists( select ID from dbo.RELATIONSHIPCONFIGURATION where ID = @RECIPROCALTYPECODEID ) then 1 else 0 end
select @RELATIONSHIPTYPESMATCH = case when (@RECIPROCALTYPECODEID = @RELATIONSHIPTYPECODEID) then 1 else 0 end
--if both match only insert one record else insert to
if @RELATIONSHIPTYPESMATCH = 1
begin
--if both spouse and org relationship types are being set in the calling form and both spouse
--and org reciprocal relationship types match, set the 'relates to' bool for both constituent types.
select @APPLIESTOINDIVIDUAL = case when @RELATIONSHIPCONSTITUENTTYPE = 0 or @RECIPRICALCONSTITUENTTYPE = 0 then 1 else 0 end
select @APPLIESTOORG = case when @RELATIONSHIPCONSTITUENTTYPE = 1 or @RECIPRICALCONSTITUENTTYPE = 1 then 1 else 0 end
select @APPLIESTOGROUP = case when @RELATIONSHIPCONSTITUENTTYPE = 2 or @RECIPRICALCONSTITUENTTYPE = 2 then 1 else 0 end
select @APPLIESTOSTUDENT = case when (dbo.UFN_CONSTITUENT_ISSTUDENT(@CONSTITUENTID) = 1) or (dbo.UFN_CONSTITUENT_ISSTUDENT(@RECIPROCALCONSTITUENTID) = 1) then 1 else 0 end
select @RELATESTOINDIVIDUAL = @APPLIESTOINDIVIDUAL
select @RELATESTOORG = @APPLIESTOORG
select @RELATESTOGROUP = @APPLIESTOGROUP
select @RELATESTOSTUDENT = @APPLIESTOSTUDENT
if @RELATIONSHIPTYPECONFIGEXISTS = 0
--union the two configs
insert into dbo.RELATIONSHIPCONFIGURATION
(
ID,
GENDERCODE,
APPLIESTOINDIVIDUALS,
APPLIESTOORGANIZATIONS,
APPLIESTOGROUPS,
APPLIESTOSTUDENTS,
RELATESTOINDIVIDUALS,
RELATESTOORGANIZATIONS,
RELATESTOGROUPS,
RELATESTOSTUDENTS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@RELATIONSHIPTYPECODEID,
0,
@APPLIESTOINDIVIDUAL,
@APPLIESTOORG,
@APPLIESTOGROUP,
@APPLIESTOSTUDENT,
@RELATESTOINDIVIDUAL,
@RELATESTOORG,
@RELATESTOGROUP,
@RELATESTOSTUDENT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
else
update dbo.RELATIONSHIPCONFIGURATION
set
APPLIESTOINDIVIDUALS = case when @APPLIESTOINDIVIDUAL = 1 then 1 else APPLIESTOINDIVIDUALS end,
APPLIESTOORGANIZATIONS = case when @APPLIESTOORG = 1 then 1 else APPLIESTOORGANIZATIONS end,
APPLIESTOGROUPS = case when @APPLIESTOGROUP = 1 then 1 else APPLIESTOGROUPS end,
APPLIESTOSTUDENTS = case when @APPLIESTOSTUDENT = 1 then 1 else APPLIESTOSTUDENTS end,
RELATESTOINDIVIDUALS = case when @RELATESTOINDIVIDUAL = 1 then 1 else RELATESTOINDIVIDUALS end,
RELATESTOORGANIZATIONS = case when @RELATESTOORG = 1 then 1 else RELATESTOORGANIZATIONS end,
RELATESTOGROUPS = case when @RELATESTOGROUP = 1 then 1 else RELATESTOGROUPS end,
RELATESTOSTUDENTS = case when @RELATESTOSTUDENT = 1 then 1 else RELATESTOSTUDENTS end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @RELATIONSHIPTYPECODEID
end
else
begin
--get config settings
select @APPLIESTOINDIVIDUAL = case when @RELATIONSHIPCONSTITUENTTYPE = 0 then 1 else 0 end
select @APPLIESTOORG = case when @RELATIONSHIPCONSTITUENTTYPE = 1 then 1 else 0 end
select @APPLIESTOGROUP = case when @RELATIONSHIPCONSTITUENTTYPE = 2 then 1 else 0 end
select @APPLIESTOSTUDENT = case when (dbo.UFN_CONSTITUENT_ISSTUDENT(@CONSTITUENTID) = 1) then 1 else 0 end
select @RELATESTOINDIVIDUAL = case when @RECIPRICALCONSTITUENTTYPE = 0 then 1 else 0 end
select @RELATESTOORG = case when @RECIPRICALCONSTITUENTTYPE = 1 then 1 else 0 end
select @RELATESTOGROUP = case when @RECIPRICALCONSTITUENTTYPE = 2 then 1 else 0 end
select @RELATESTOSTUDENT = case when (dbo.UFN_CONSTITUENT_ISSTUDENT(@RECIPROCALCONSTITUENTID) = 1) then 1 else 0 end
if @RELATIONSHIPTYPECONFIGEXISTS = 0
insert into dbo.RELATIONSHIPCONFIGURATION
(
ID,
GENDERCODE,
APPLIESTOINDIVIDUALS,
APPLIESTOORGANIZATIONS,
APPLIESTOGROUPS,
APPLIESTOSTUDENTs,
RELATESTOINDIVIDUALS,
RELATESTOORGANIZATIONS,
RELATESTOGROUPS,
RELATESTOSTUDENTS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@RELATIONSHIPTYPECODEID,
0,
@APPLIESTOINDIVIDUAL,
@APPLIESTOORG,
@APPLIESTOGROUP,
@APPLIESTOSTUDENT,
@RELATESTOINDIVIDUAL,
@RELATESTOORG,
@RELATESTOGROUP,
@RELATESTOSTUDENT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
else
update dbo.RELATIONSHIPCONFIGURATION
set
APPLIESTOINDIVIDUALS = case when @APPLIESTOINDIVIDUAL = 1 then 1 else APPLIESTOINDIVIDUALS end,
APPLIESTOORGANIZATIONS = case when @APPLIESTOORG = 1 then 1 else APPLIESTOORGANIZATIONS end,
APPLIESTOGROUPS = case when @APPLIESTOGROUP = 1 then 1 else APPLIESTOGROUPS end,
APPLIESTOSTUDENTS = case when @APPLIESTOSTUDENT = 1 then 1 else APPLIESTOSTUDENTS end,
RELATESTOINDIVIDUALS = case when @RELATESTOINDIVIDUAL = 1 then 1 else RELATESTOINDIVIDUALS end,
RELATESTOORGANIZATIONS = case when @RELATESTOORG = 1 then 1 else RELATESTOORGANIZATIONS end,
RELATESTOGROUPS = case when @RELATESTOGROUP = 1 then 1 else RELATESTOGROUPS end,
RELATESTOSTUDENTS = case when @RELATESTOSTUDENT = 1 then 1 else RELATESTOSTUDENTS end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @RELATIONSHIPTYPECODEID
if @RECIPROCALTYPECONFIGEXISTS = 0
insert into dbo.RELATIONSHIPCONFIGURATION
(
ID,
GENDERCODE,
APPLIESTOINDIVIDUALS,
APPLIESTOORGANIZATIONS,
APPLIESTOGROUPS,
APPLIESTOSTUDENTS,
RELATESTOINDIVIDUALS,
RELATESTOORGANIZATIONS,
RELATESTOGROUPS,
RELATESTOSTUDENTS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@RECIPROCALTYPECODEID,
0,
@RELATESTOINDIVIDUAL,
@RELATESTOORG,
@RELATESTOGROUP,
@RELATESTOSTUDENT,
@APPLIESTOINDIVIDUAL,
@APPLIESTOORG,
@APPLIESTOGROUP,
@APPLIESTOSTUDENT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
else
update dbo.RELATIONSHIPCONFIGURATION
set
APPLIESTOINDIVIDUALS = case when @RELATESTOINDIVIDUAL = 1 then 1 else APPLIESTOINDIVIDUALS end,
APPLIESTOORGANIZATIONS = case when @RELATESTOORG = 1 then 1 else APPLIESTOORGANIZATIONS end,
APPLIESTOGROUPS = case when @RELATESTOGROUP = 1 then 1 else APPLIESTOGROUPS end,
APPLIESTOSTUDENTS = case when @RELATESTOSTUDENT = 1 then 1 else APPLIESTOSTUDENTS end,
RELATESTOINDIVIDUALS = case when @APPLIESTOINDIVIDUAL = 1 then 1 else RELATESTOINDIVIDUALS end,
RELATESTOORGANIZATIONS = case when @APPLIESTOORG = 1 then 1 else RELATESTOORGANIZATIONS end,
RELATESTOGROUPS = case when @APPLIESTOGROUP = 1 then 1 else RELATESTOGROUPS end,
RELATESTOSTUDENTS = case when @APPLIESTOSTUDENT= 1 then 1 else RELATESTOSTUDENTS end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @RECIPROCALTYPECODEID
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
end