USP_BATCHSPONSORSHIP_ADDGROUP
Adds a new sponsorship batch group.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @NAME | nvarchar(100) | IN | |
| @DESCRIPTION | nvarchar(300) | IN | |
| @GROUPTYPEID | uniqueidentifier | IN | |
| @GIVESANONYMOUSLY | bit | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @PHONETYPECODEID | uniqueidentifier | IN | |
| @NUMBER | nvarchar(100) | IN | |
| @EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @WEBADDRESS | UDT_WEBADDRESS | IN | |
| @MEMBERS | xml | IN | |
| @STARTDATE | datetime | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @DONOTMAIL | bit | IN | |
| @DONOTMAILREASONCODEID | uniqueidentifier | 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 | |
| @DONOTCALL | bit | IN | |
| @DONOTEMAIL | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_BATCHSPONSORSHIP_ADDGROUP
(
@ID uniqueidentifier output,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(300) = '',
@GROUPTYPEID uniqueidentifier = null,
@GIVESANONYMOUSLY bit = null,
@ADDRESSTYPECODEID uniqueidentifier = null,
@COUNTRYID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@STATEID uniqueidentifier = null,
@POSTCODE nvarchar(12) = '',
@PHONETYPECODEID uniqueidentifier = null,
@NUMBER nvarchar(100) = '',
@EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@WEBADDRESS dbo.UDT_WEBADDRESS = '',
@MEMBERS xml = null,
@STARTDATE datetime = null,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@DONOTMAIL bit = 0,
@DONOTMAILREASONCODEID uniqueidentifier = null,
@OMITFROMVALIDATION bit = 0,
@CART nvarchar(10) = '',
@DPC nvarchar(8) = '',
@LOT nvarchar(5) = '',
@COUNTYCODEID uniqueidentifier = null,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@LASTVALIDATIONATTEMPTDATE datetime = null,
@VALIDATIONMESSAGE nvarchar(100) = '',
@CERTIFICATIONDATA integer = 0,
@DONOTCALL bit = 0,
@DONOTEMAIL bit = 0
)
as
set nocount on;
begin try
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @ID is null
set @ID = newid();
if @DONOTMAIL = 0
set @DONOTMAILREASONCODEID = null
-- Create the batch constituent record for the group
insert into dbo.BATCHSPONSORSHIPCONSTITUENT
(
ID,
ISGROUP,
KEYNAME,
GIVESANONYMOUSLY,
GROUPTYPECODE,
GROUPTYPEID,
GROUPDESCRIPTION,
GROUPSTARTDATE,
ADDRESSTYPECODEID,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
OMITFROMVALIDATION,
CART,
DPC,
LOT,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
PHONETYPECODEID,
NUMBER,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
WEBADDRESS,
CURRENTAPPUSERID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
DONOTMAIL,
DONOTMAILREASONCODEID,
DONOTCALL,
DONOTEMAIL
)
values
(
@ID,
1,
@NAME,
@GIVESANONYMOUSLY,
1, -- Indicates group
@GROUPTYPEID,
@DESCRIPTION,
@STARTDATE,
@ADDRESSTYPECODEID,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@OMITFROMVALIDATION,
@CART,
@DPC,
@LOT,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@PHONETYPECODEID,
@NUMBER,
@EMAILADDRESSTYPECODEID,
@EMAILADDRESS,
@WEBADDRESS,
@CURRENTAPPUSERID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
@DONOTMAIL,
@DONOTMAILREASONCODEID,
@DONOTCALL,
@DONOTEMAIL
);
-- Handle group members
declare @MEMBERSTABLE table
(
ID uniqueidentifier,
ISPRIMARY bit,
BATCHSPONSORSHIPCONSTITUENTID uniqueidentifier,
MEMBERID uniqueidentifier
)
insert into @MEMBERSTABLE
(
ID,
ISPRIMARY,
BATCHSPONSORSHIPCONSTITUENTID,
MEMBERID
)
select
case when ID is null then newid() else ID end,
ISPRIMARY,
newid(),
MEMBERID -- For constituents that exist CONSTITUENT, this is their CONSTITUENT.ID. If they only exist in BATCHSPONSORSHIPCONSTITUENT, then it's BATCHSPONSORSHIPCONSTITUENT.ID.
from dbo.UFN_BATCHSPONSORSHIPCONSTITUENT_GETGROUPMEMBERS_FROMITEMLISTXML(@MEMBERS)
if (exists(select top 1 1 from @MEMBERSTABLE group by MEMBERID having count(1) > 1))
begin
raiserror('ERR_MEMBERSUNIQUE : Group members must be unique.', 13, 1);
end
-- Create BATCHSPONSORSHIPCONSTITUENT entries for the constituents that had a
-- CONSTITUENT.ID passed in for MEMBERID.
insert into dbo.BATCHSPONSORSHIPCONSTITUENT
(
ID,
EXISTINGCONSTITUENTID,
ISORGANIZATION,
ISGROUP,
GROUPTYPECODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
M.BATCHSPONSORSHIPCONSTITUENTID,
M.MEMBERID,
C.ISORGANIZATION,
C.ISGROUP,
coalesce(GD.GROUPTYPECODE, 0),
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @MEMBERSTABLE M
left join dbo.CONSTITUENT C on M.MEMBERID = C.ID
left join dbo.GROUPDATA GD on C.ID = GD.ID
where C.ID is not null
-- For records that had a BATCHSPONSORSHIPCONSTITUENTID.ID passed
-- in for MEMBERID, update BATCHSPONSORSHIPCONSTITUENTID to MEMBERID.
update @MEMBERSTABLE set BATCHSPONSORSHIPCONSTITUENTID = MEMBERID
from @MEMBERSTABLE M
left join dbo.CONSTITUENT C on M.MEMBERID = C.ID
where C.ID is null
insert into dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER
(
ID,
GROUPID,
MEMBERID,
ISPRIMARY,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ID,
@ID,
BATCHSPONSORSHIPCONSTITUENTID,
ISPRIMARY,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @MEMBERSTABLE
end try
begin catch
exec USP_RAISE_ERROR;
return 1;
end catch