USP_ORGANIZATION_ADD
Creates a new organization.
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(200) | 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 (
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100),
@WEBADDRESS dbo.UDT_WEBADDRESS = '',
@PICTURE varbinary(max) = null,
@PICTURETHUMBNAIL varbinary(max) = null,
@INDUSTRYCODEID uniqueidentifier = null,
@NUMEMPLOYEES int = 0,
@NUMSUBSIDIARIES int = 0,
@PARENTCORPID uniqueidentifier = null,
@ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@ADDRESS_DONOTMAIL bit = 0,
@ADDRESS_COUNTRYID uniqueidentifier = null,
@ADDRESS_STATEID uniqueidentifier = null,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@ADDRESS_CITY nvarchar(50) = '',
@ADDRESS_POSTCODE nvarchar(12) = '',
@PHONE_PHONETYPECODEID uniqueidentifier = null,
@PHONE_NUMBER nvarchar(100) = '',
@SKIP_ADDING_SECURITYGROUPS bit=0,
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@OMITFROMVALIDATION bit = 0,
@CART nvarchar(10) = '',
@DPC nvarchar(8) = '',
@LOT nvarchar(5) = '',
@COUNTYCODEID uniqueidentifier = null,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@LASTVALIDATIONATTEMPTDATE datetime = null,
@VALIDATIONMESSAGE nvarchar(200) = '',
@CERTIFICATIONDATA integer = 0,
@ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@ISPRIMARY bit = 0,
@SKIP_ADDING_SITES bit = 0,
@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;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
if @ADDRESS_DONOTMAIL = 0
set @ADDRESS_DONOTMAILREASONCODEID = null
if @NUMEMPLOYEES is null
set @NUMEMPLOYEES = 0;
if @NUMSUBSIDIARIES is null
set @NUMSUBSIDIARIES = 0;
begin try
exec dbo.USP_PARSE_ORGANIZATION_NAME @NAME, @KEYNAME output, @KEYNAMEPREFIX output;
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
);
-- CR 12845 - AdamBu - Since the ISPRIMARY flag will always be set, the org data record should always be created.
-- if @INDUSTRYCODEID is not null
-- or @PARENTCORPID is not null
-- or @PARENTCORPID is not null
-- or @NUMEMPLOYEES != 0
-- or @NUMSUBSIDIARIES != 0
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
);
/*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;
set @ADDRESSID = newID();
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
);
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 Primary Address */
/*Start Primary Phone */
declare @PHONE_COUNTRYID uniqueidentifier = @ADDRESS_COUNTRYID;
if @ADDRESS_COUNTRYID is null
exec @PHONE_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
COUNTRYID,
INFOSOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
1,
@PHONE_COUNTRYID,
@INFOSOURCECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/*End Primary Phone */
/*Start Primary Email Address */
if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
begin
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 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 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;
if @PARENTCORPID is not null
exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @ID, @PARENTCORPID,@CURRENTDATE,@CHANGEAGENTID,@CURRENTDATE,1,null
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;