USP_ORGANIZATION_ADD_EDIT
Add or edit an organization constituent record
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @NAME | nvarchar(100) | IN | |
| @WEBADDRESS | UDT_WEBADDRESS | IN | |
| @PICTURE | varbinary | IN | |
| @PICTURETHUMBNAIL | varbinary | IN | |
| @INDUSTRYCODEID | uniqueidentifier | IN | |
| @NUMEMPLOYEES | int | IN | |
| @NUMSUBSIDIARIES | int | IN | |
| @PARENTCORPID | uniqueidentifier | IN | |
| @ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @ADDRESS_DONOTMAIL | bit | IN | |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | |
| @ADDRESS_STATEID | uniqueidentifier | IN | |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | |
| @ADDRESS_CITY | nvarchar(50) | IN | |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | |
| @PHONE_PHONETYPECODEID | uniqueidentifier | IN | |
| @PHONE_NUMBER | nvarchar(100) | IN | |
| @SKIP_ADDING_SECURITYGROUPS | bit | IN | |
| @EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @OMITFROMVALIDATION | bit | IN | |
| @CART | nvarchar(10) | IN | |
| @DPC | nvarchar(8) | IN | |
| @LOT | nvarchar(5) | IN | |
| @COUNTYCODEID | uniqueidentifier | IN | |
| @CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @VALIDATIONMESSAGE | nvarchar(100) | IN | |
| @CERTIFICATIONDATA | int | IN | |
| @ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @ISPRIMARY | bit | IN | |
| @SKIP_ADDING_SITES | bit | IN | |
| @INFOSOURCECODEID | uniqueidentifier | IN | |
| @ISCONSTITUENT | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_ORGANIZATION_ADD_EDIT (
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100) = null,
@WEBADDRESS dbo.UDT_WEBADDRESS = null,
@PICTURE varbinary(max) = null,
@PICTURETHUMBNAIL varbinary(max) = null,
@INDUSTRYCODEID uniqueidentifier = null,
@NUMEMPLOYEES int = null,
@NUMSUBSIDIARIES int = null,
@PARENTCORPID uniqueidentifier = null,
@ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@ADDRESS_DONOTMAIL bit = null,
@ADDRESS_COUNTRYID uniqueidentifier = null,
@ADDRESS_STATEID uniqueidentifier = null,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = null,
@ADDRESS_CITY nvarchar(50) = null,
@ADDRESS_POSTCODE nvarchar(12) = null,
@PHONE_PHONETYPECODEID uniqueidentifier = null,
@PHONE_NUMBER nvarchar(100) = null,
@SKIP_ADDING_SECURITYGROUPS bit=0,
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null,
@OMITFROMVALIDATION bit = 0,
@CART nvarchar(10) = null,
@DPC nvarchar(8) = null,
@LOT nvarchar(5) = null,
@COUNTYCODEID uniqueidentifier = null,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@LASTVALIDATIONATTEMPTDATE datetime = null,
@VALIDATIONMESSAGE nvarchar(100) = null,
@CERTIFICATIONDATA integer = null,
@ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@ISPRIMARY bit = null,
@SKIP_ADDING_SITES bit = null,
@INFOSOURCECODEID uniqueidentifier = null,
@ISCONSTITUENT bit = 1
) as
set nocount on;
declare @CURRENTDATE datetime;
declare @CHILDID uniqueidentifier;
declare @KEYNAME nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(50);
declare @UpdateOrgData bit;
declare @NewOrg bit;
set @NewOrg = 0 -- not a new record
if @ID is null
begin
set @ID = newid();
set @NewOrg = 1
end
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
if @ADDRESS_DONOTMAIL = 0 -- Do not mail = false (0)
set @ADDRESS_DONOTMAILREASONCODEID = null
if @ID is null
begin
if @NUMEMPLOYEES is null
set @NUMEMPLOYEES = 0;
if @NUMSUBSIDIARIES is null
set @NUMSUBSIDIARIES = 0;
end
begin try
if @NAME is null
begin
set @KEYNAME = null
set @KEYNAMEPREFIX = null
end
else
exec dbo.USP_PARSE_ORGANIZATION_NAME @NAME, @KEYNAME output, @KEYNAMEPREFIX output;
if exists(select ID from dbo.CONSTITUENT where ID= @ID)
update dbo.CONSTITUENT set
KEYNAME = ISNULL(@KEYNAME, KEYNAME),
WEBADDRESS = ISNULL(@WEBADDRESS, WEBADDRESS),
PICTURE = ISNULL(@PICTURE, PICTURE),
PICTURETHUMBNAIL = ISNULL(@PICTURETHUMBNAIL, PICTURETHUMBNAIL),
KEYNAMEPREFIX = ISNULL(@KEYNAMEPREFIX, KEYNAMEPREFIX),
ISCONSTITUENT= ISNULL(@ISCONSTITUENT, ISCONSTITUENT),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
else
insert into dbo.CONSTITUENT
(
ID,
ISORGANIZATION,
KEYNAME,
WEBADDRESS,
PICTURE,
PICTURETHUMBNAIL,
KEYNAMEPREFIX,
ISCONSTITUENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
-1,
@KEYNAME,
@WEBADDRESS,
@PICTURE,
@PICTURETHUMBNAIL,
@KEYNAMEPREFIX,
@ISCONSTITUENT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if exists(select ID from dbo.ORGANIZATIONDATA where ID= @ID)
update dbo.ORGANIZATIONDATA set
INDUSTRYCODEID = ISNULL(@INDUSTRYCODEID, INDUSTRYCODEID),
NUMEMPLOYEES = ISNULL(@NUMEMPLOYEES, NUMEMPLOYEES),
NUMSUBSIDIARIES = ISNULL(@NUMSUBSIDIARIES, NUMSUBSIDIARIES),
PARENTCORPID = ISNULL(@PARENTCORPID, PARENTCORPID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
ISPRIMARY = ISNULL(@ISPRIMARY, ISPRIMARY)
where ID = @ID
else
begin
if @NUMEMPLOYEES is null
set @NUMEMPLOYEES = 0
if @NUMSUBSIDIARIES is null
set @NUMSUBSIDIARIES = 0
if @ISPRIMARY is null
set @ISPRIMARY = 1
insert into dbo.ORGANIZATIONDATA
(
ID,
INDUSTRYCODEID,
NUMEMPLOYEES,
NUMSUBSIDIARIES,
PARENTCORPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ISPRIMARY
)
values
(
@ID,
@INDUSTRYCODEID,
@NUMEMPLOYEES,
@NUMSUBSIDIARIES,
@PARENTCORPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISPRIMARY
);
end
/*Start Primary Address */
if
(
(@ADDRESS_STATEID is not null)
or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '')
or (coalesce(@ADDRESS_CITY,'') <> '')
or (coalesce(@ADDRESS_POSTCODE,'') <> '')
or (@ADDRESS_ADDRESSTYPECODEID is not null)
or (@ADDRESS_COUNTRYID is not null)
or (@ADDRESS_DONOTMAIL = 1)
or (@ADDRESS_DONOTMAILREASONCODEID is not null)
)
begin
declare @ADDRESSID uniqueidentifier;
select @ADDRESSID = ID from dbo.ADDRESS where CONSTITUENTID = @ID and ISPRIMARY = 1
if @ADDRESSID is null
set @ADDRESSID = newID();
if exists(select ID from dbo.ADDRESS where ID = @ADDRESSID)
update dbo.ADDRESS set
ADDRESSTYPECODEID = ISNULL(@ADDRESS_ADDRESSTYPECODEID, ADDRESSTYPECODEID),
DONOTMAIL = ISNULL(@ADDRESS_DONOTMAIL, DONOTMAIL),
COUNTRYID = ISNULL(@ADDRESS_COUNTRYID, COUNTRYID),
STATEID = ISNULL(@ADDRESS_STATEID, STATEID),
ADDRESSBLOCK = ISNULL(@ADDRESS_ADDRESSBLOCK, ADDRESSBLOCK),
CITY = ISNULL(@ADDRESS_CITY, CITY),
POSTCODE = ISNULL(@ADDRESS_POSTCODE, POSTCODE),
CART = ISNULL(@CART, CART),
DPC = ISNULL(@DPC, DPC),
LOT = ISNULL(@LOT, LOT),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DONOTMAILREASONCODEID = ISNULL(@ADDRESS_DONOTMAILREASONCODEID, DONOTMAILREASONCODEID)
where ID = @ADDRESSID
else
begin
if @CART is null
set @CART = ''
if @DPC is null
set @DPC = ''
if @LOT is null
set @LOT = ''
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
DONOTMAIL,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTMAILREASONCODEID
)
values
(
@ADDRESSID,
@ID,
@ADDRESS_ADDRESSTYPECODEID,
1,
@ADDRESS_DONOTMAIL,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@CART,
@DPC,
@LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ADDRESS_DONOTMAILREASONCODEID
);
end
if exists(select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ADDRESSID)
update dbo.ADDRESSVALIDATIONUPDATE set
COUNTYCODEID = ISNULL(@COUNTYCODEID, COUNTYCODEID),
CONGRESSIONALDISTRICTCODEID = ISNULL(@CONGRESSIONALDISTRICTCODEID, CONGRESSIONALDISTRICTCODEID),
LASTVALIDATIONATTEMPTDATE = ISNULL(@LASTVALIDATIONATTEMPTDATE, LASTVALIDATIONATTEMPTDATE),
VALIDATIONMESSAGE = ISNULL(@VALIDATIONMESSAGE, VALIDATIONMESSAGE),
CERTIFICATIONDATA = ISNULL(@CERTIFICATIONDATA, CERTIFICATIONDATA),
OMITFROMVALIDATION = ISNULL(@OMITFROMVALIDATION, OMITFROMVALIDATION),
INFOSOURCECODEID = ISNULL(@INFOSOURCECODEID, INFOSOURCECODEID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ADDRESSID
else
begin
if @VALIDATIONMESSAGE is null
set @VALIDATIONMESSAGE = ''
if @CERTIFICATIONDATA is null
set @CERTIFICATIONDATA = ''
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
OMITFROMVALIDATION,
INFOSOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ADDRESSID,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@CURRENTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@OMITFROMVALIDATION,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
/*End Primary Address */
/*Start Primary Phone */
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
begin
declare @PhoneID uniqueidentifier
select @PhoneID = ID from dbo.PHONE where CONSTITUENTID = @ID and ISPRIMARY = 1
if @PhoneID is null
set @PhoneID = newID();
if exists(select ID from dbo.PHONE where ID= @PhoneID)
update dbo.PHONE set
PHONETYPECODEID = ISNULL(@PHONE_PHONETYPECODEID, PHONETYPECODEID),
NUMBER = ISNULL(@PHONE_NUMBER, NUMBER),
COUNTRYID = ISNULL(@ADDRESS_COUNTRYID, COUNTRYID),
INFOSOURCECODEID = ISNULL(@INFOSOURCECODEID, INFOSOURCECODEID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @PhoneID
else
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
COUNTRYID,
INFOSOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
1,
@ADDRESS_COUNTRYID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/*End Primary Phone */
/*Start Primary Email Address */
if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
begin
declare @EmailID uniqueidentifier
select @EmailID = ID from dbo.EMAILADDRESS where CONSTITUENTID = @ID and ISPRIMARY = 1
if @EmailID is null
set @EmailID = newID();
if exists(select ID from dbo.EMAILADDRESS where ID = @EmailID)
update dbo.EMAILADDRESS set
EMAILADDRESSTYPECODEID = ISNULL(@EMAILADDRESS_EMAILADDRESSTYPECODEID, EMAILADDRESSTYPECODEID),
EMAILADDRESS = ISNULL(@EMAILADDRESS_EMAILADDRESS, EMAILADDRESS),
INFOSOURCECODEID = ISNULL(@INFOSOURCECODEID, INFOSOURCECODEID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @EmailID
else
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[INFOSOURCECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
1,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/*End Primary Email Address */
/*Start security groups*/
if @NewOrg = 1 and COALESCE(@SKIP_ADDING_SECURITYGROUPS,0) = 0
BEGIN
exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@ID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
END
/*end security groups*/
if @NewOrg = 1 and coalesce(@SKIP_ADDING_SITES,0) = 0
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID = @CURRENTAPPUSERID,
@DATAFORMTEMPLATEID = '9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID = @ID,
@DATEADDEDTOUSE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;