USP_DATAFORMTEMPLATE_ADD_CONSTITUENTQUICK
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @SOURCECONSTITUENTID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @LASTNAME | nvarchar(50) | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @GENDERCODE | tinyint | IN | |
| @PHONENUMBER | nvarchar(100) | IN | |
| @EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @ADDASRELATIONSHIP | bit | IN | |
| @RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @ISSPOUSE | bit | IN | |
| @COPYADDRESS | bit | IN | |
| @MEMBERSOFSAMEHOUSEHOLD | bit | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTQUICK
(
@ID uniqueidentifier = null output,
@SOURCECONSTITUENTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@LASTNAME nvarchar(50) = null,
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@TITLECODEID uniqueidentifier = null,
@SUFFIXCODEID uniqueidentifier = null,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@GENDERCODE tinyint = 0,
@PHONENUMBER nvarchar(100) = '',
@EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@ADDASRELATIONSHIP bit = 0,
@RELATIONSHIPTYPECODEID uniqueidentifier = null,
@RECIPROCALTYPECODEID uniqueidentifier = null,
@ISSPOUSE bit = 0,
@COPYADDRESS bit = 0,
@MEMBERSOFSAMEHOUSEHOLD bit = 0,
@CURRENTAPPUSERID uniqueidentifier
)
as
set nocount on;
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
insert into dbo.CONSTITUENT
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISCONSTITUENT]
)
values
(
@ID,
0,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@BIRTHDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
1
);
if (coalesce(@PHONENUMBER,'') <> '')
begin
insert into dbo.PHONE
(
[CONSTITUENTID],
[NUMBER],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@PHONENUMBER,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if (coalesce(@EMAILADDRESS,'') <> '')
begin
insert into dbo.EMAILADDRESS
(
[CONSTITUENTID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@EMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
INSERT INTO [dbo].[NAMEFORMAT]
(
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION],
[SEQUENCE]
)
SELECT
@ID,
NFD.NAMEFORMATTYPECODEID,
NFD.NAMEFORMATFUNCTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
NFD.PRIMARYADDRESSEE,
NFD.PRIMARYSALUTATION,
(
select count(ID)
from dbo.NAMEFORMATDEFAULT as SUBNFD
where NFD.ID > SUBNFD.ID
and NFD.APPLYTOCODE = SUBNFD.APPLYTOCODE
)
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE NFD.APPLYTOCODE = 0
if @ADDASRELATIONSHIP = 1
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_INDTOIND_RELATIONSHIP_2
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@CONSTITUENTID = @SOURCECONSTITUENTID,
@RECIPROCALCONSTITUENTID = @ID,
@RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID,
@RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID,
@ISSPOUSE = @ISSPOUSE,
@COPYCONTACTINFO = @COPYADDRESS,
@KEYNAME = @LASTNAME,
@FIRSTNAME = @FIRSTNAME,
@MEMBERSOFSAMEHOUSEHOLD = @MEMBERSOFSAMEHOUSEHOLD
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0