USP_SPONSORSHIPBATCH_CONSTITUENT_ADD
Saves an individual record with spouse and business information.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @BATCHSPONSORSHIPCONSTITUENTID | uniqueidentifier | IN | |
| @BATCHSPONSORSHIPCONSTITUENTACCOUNTID | uniqueidentifier | IN | |
| @CONSTITUENTACCOUNTID | uniqueidentifier | INOUT | |
| @BYPASSINDIVIDUALHOUSEHOLDADD | bit | IN | |
| @BYPASSINDIVIDUALSPOUSEADD | bit | IN | |
| @CURRENTRECOGNITIONS | xml | IN | |
| @UPDATEDRECOGNITIONS | xml | INOUT |
Definition
Copy
CREATE procedure dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_ADD
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@BATCHSPONSORSHIPCONSTITUENTID uniqueidentifier,
@BATCHSPONSORSHIPCONSTITUENTACCOUNTID uniqueidentifier = null,
@CONSTITUENTACCOUNTID uniqueidentifier = null output,
@BYPASSINDIVIDUALHOUSEHOLDADD bit = 0,
@BYPASSINDIVIDUALSPOUSEADD bit = 0,
@CURRENTRECOGNITIONS xml = null,
@UPDATEDRECOGNITIONS xml = null output
) as begin
set nocount on;
declare @CURRENTDATE datetime;
declare @CURRENTAPPUSERID uniqueidentifier
declare @ISORGANIZATION bit
declare @KEYNAME nvarchar(100)
declare @KEYNAMEPREFIX nvarchar(50)
declare @FIRSTNAME nvarchar(50)
declare @MIDDLENAME nvarchar(50)
declare @MAIDENNAME nvarchar(100)
declare @NICKNAME nvarchar(50)
declare @TITLECODEID uniqueidentifier
declare @SUFFIXCODEID uniqueidentifier
declare @GENDERCODE tinyint
declare @BIRTHDATE dbo.UDT_FUZZYDATE
declare @ADDRESS_ADDRESSTYPECODEID uniqueidentifier
declare @ADDRESS_DONOTMAIL bit
declare @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier
declare @ADDRESS_COUNTRYID uniqueidentifier
declare @ADDRESS_STATEID uniqueidentifier
declare @ADDRESS_ADDRESSBLOCK nvarchar(150)
declare @ADDRESS_CITY nvarchar(50)
declare @ADDRESS_POSTCODE nvarchar(12)
-- Address Validation
declare @ADDRESS_OMITFROMVALIDATION bit
declare @ADDRESS_CART nvarchar(10)
declare @ADDRESS_DPC nvarchar(8)
declare @ADDRESS_LOT nvarchar(5)
declare @ADDRESS_COUNTYCODEID uniqueidentifier
declare @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier
declare @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime
declare @ADDRESS_VALIDATIONMESSAGE nvarchar(200)
declare @ADDRESS_CERTIFICATIONDATA integer
declare @PHONE_PHONETYPECODEID uniqueidentifier
declare @PHONE_NUMBER nvarchar(100)
declare @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier
declare @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS
declare @MARITALSTATUSCODEID uniqueidentifier
declare @SKIP_ADDING_SECURITYGROUPS bit
declare @WEBADDRESS dbo.UDT_WEBADDRESS
declare @INDUSTRYCODEID uniqueidentifier
declare @NUMEMPLOYEES int
declare @NUMSUBSIDIARIES int
declare @PARENTCORPID uniqueidentifier
declare @ISGROUP bit
declare @ISHOUSEHOLD bit
declare @GIVESANONYMOUSLY bit
declare @GROUPTYPECODE tinyint
declare @GROUPTYPEID uniqueidentifier
declare @GROUPDESCRIPTION nvarchar(300)
declare @GROUPSTARTDATE datetime
--Spouse Variables
declare @BATCHSPOUSEID uniqueidentifier
declare @SPOUSEID uniqueidentifier
declare @EXISTINGSPOUSE bit
declare @SPOUSE_LASTNAME nvarchar(100)
declare @SPOUSE_FIRSTNAME nvarchar(50)
declare @SPOUSE_MIDDLENAME nvarchar(50)
declare @SPOUSE_MAIDENNAME nvarchar(100)
declare @SPOUSE_NICKNAME nvarchar(50)
declare @SPOUSE_TITLECODEID uniqueidentifier
declare @SPOUSE_SUFFIXCODEID uniqueidentifier
declare @SPOUSE_GENDERCODE tinyint
declare @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE
declare @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier
declare @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier
declare @COPYPRIMARYINFORMATION bit
declare @SPOUSE_STARTDATE datetime
declare @PRIMARYRELATIONSHIPEXISTS bit
declare @PRIMARYMATCHFACTOR decimal(5,2)
declare @RECIPROCALRELATIONSHIPEXISTS bit
declare @RECIPROCALMATCHFACTOR decimal(5,2)
--Organization variables
declare @BATCHORGANIZATIONID uniqueidentifier
declare @ORGANIZATIONID uniqueidentifier
declare @EXISTINGORGANIZATION bit
declare @ORGANIZATION_KEYNAME nvarchar(100)
declare @ORGANIZATION_KEYNAMEPREFIX nvarchar(50)
declare @ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier
declare @ORGANIZATION_COUNTRYID uniqueidentifier
declare @ORGANIZATION_STATEID uniqueidentifier
declare @ORGANIZATION_ADDRESSBLOCK nvarchar(150)
declare @ORGANIZATION_CITY nvarchar(50)
declare @ORGANIZATION_POSTCODE nvarchar(12)
declare @ORGANIZATION_DONOTMAIL bit
declare @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier
-- Address Validation
declare @ORGANIZATION_OMITFROMVALIDATION bit
declare @ORGANIZATION_CART nvarchar(10)
declare @ORGANIZATION_DPC nvarchar(8)
declare @ORGANIZATION_LOT nvarchar(5)
declare @ORGANIZATION_COUNTYCODEID uniqueidentifier
declare @ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier
declare @ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime
declare @ORGANIZATION_VALIDATIONMESSAGE nvarchar(200)
declare @ORGANIZATION_CERTIFICATIONDATA integer
declare @ORGANIZATION_PHONETYPECODEID uniqueidentifier
declare @ORGANIZATION_NUMBER nvarchar(100)
declare @ORGANIZATION_RECIPROCALTYPECODEID uniqueidentifier
declare @ORGANIZATION_RELATIONSHIPTYPECODEID uniqueidentifier
declare @ORGANIZATION_STARTDATE datetime
declare @ISCONTACT bit
declare @ISPRIMARYCONTACT bit
declare @CONTACTTYPECODEID uniqueidentifier
declare @POSITION nvarchar(50)
declare @ISMATCHINGGIFTRELATIONSHIP bit
declare @BATCHHOUSEHOLDID uniqueidentifier
declare @HOUSEHOLDID uniqueidentifier
declare @HOUSEHOLD_NAME nvarchar(100)
declare @ADDSPOUSETOHOUSEHOLD bit
declare @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit
declare @ISSPOUSERELATIONSHIP bit
declare @SKIP_ADDING_SITES bit
declare @NAMEFORMATFUNCTIONID uniqueidentifier
-- Additional fields needed to use enhanced rev batch UI.
declare @JOBCATEGORYCODEID uniqueidentifier
declare @CAREERLEVELCODEID uniqueidentifier
--load
exec dbo.USP_BATCHSPONSORSHIPCONSTITUENT_LOAD @BATCHSPONSORSHIPCONSTITUENTID, @CURRENTAPPUSERID OUTPUT, @ISORGANIZATION OUTPUT, @KEYNAME OUTPUT, @KEYNAMEPREFIX OUTPUT, @FIRSTNAME OUTPUT, @MIDDLENAME OUTPUT, @MAIDENNAME OUTPUT, @NICKNAME OUTPUT, @TITLECODEID OUTPUT, @SUFFIXCODEID OUTPUT, @GENDERCODE OUTPUT, @BIRTHDATE OUTPUT, @ADDRESS_ADDRESSTYPECODEID OUTPUT, @ADDRESS_DONOTMAIL OUTPUT, @ADDRESS_COUNTRYID OUTPUT, @ADDRESS_STATEID OUTPUT, @ADDRESS_ADDRESSBLOCK OUTPUT, @ADDRESS_CITY OUTPUT, @ADDRESS_POSTCODE OUTPUT,
@ADDRESS_OMITFROMVALIDATION OUTPUT, @ADDRESS_CART OUTPUT, @ADDRESS_DPC OUTPUT, @ADDRESS_LOT OUTPUT, @ADDRESS_COUNTYCODEID OUTPUT, @ADDRESS_CONGRESSIONALDISTRICTCODEID OUTPUT, @ADDRESS_LASTVALIDATIONATTEMPTDATE OUTPUT, @ADDRESS_VALIDATIONMESSAGE OUTPUT, @ADDRESS_CERTIFICATIONDATA OUTPUT, @PHONE_PHONETYPECODEID OUTPUT, @PHONE_NUMBER OUTPUT, @EMAILADDRESS_EMAILADDRESSTYPECODEID OUTPUT,
@EMAILADDRESS_EMAILADDRESS OUTPUT, @MARITALSTATUSCODEID OUTPUT, @WEBADDRESS output,@INDUSTRYCODEID output,@NUMEMPLOYEES output,@NUMSUBSIDIARIES output,@PARENTCORPID output, @BATCHSPOUSEID output, null, @SPOUSEID OUTPUT, @EXISTINGSPOUSE OUTPUT, @SPOUSE_LASTNAME OUTPUT, @SPOUSE_FIRSTNAME OUTPUT, @SPOUSE_MIDDLENAME OUTPUT, @SPOUSE_MAIDENNAME OUTPUT, @SPOUSE_NICKNAME OUTPUT, @SPOUSE_TITLECODEID OUTPUT, @SPOUSE_SUFFIXCODEID OUTPUT, @SPOUSE_GENDERCODE OUTPUT, @SPOUSE_BIRTHDATE OUTPUT, @SPOUSE_RECIPROCALTYPECODEID OUTPUT, @SPOUSE_RELATIONSHIPTYPECODEID OUTPUT, @COPYPRIMARYINFORMATION OUTPUT, @SPOUSE_STARTDATE OUTPUT,
@PRIMARYRELATIONSHIPEXISTS OUTPUT, @PRIMARYMATCHFACTOR OUTPUT, @RECIPROCALRELATIONSHIPEXISTS OUTPUT, @RECIPROCALMATCHFACTOR OUTPUT, @BATCHORGANIZATIONID OUTPUT, null, @ORGANIZATIONID OUTPUT, @EXISTINGORGANIZATION OUTPUT, @ORGANIZATION_KEYNAME OUTPUT, @ORGANIZATION_KEYNAMEPREFIX OUTPUT, @ORGANIZATION_ADDRESSTYPECODEID OUTPUT, @ORGANIZATION_COUNTRYID OUTPUT, @ORGANIZATION_STATEID OUTPUT, @ORGANIZATION_ADDRESSBLOCK OUTPUT, @ORGANIZATION_CITY OUTPUT, @ORGANIZATION_POSTCODE OUTPUT,
@ORGANIZATION_DONOTMAIL OUTPUT, @ORGANIZATION_OMITFROMVALIDATION OUTPUT, @ORGANIZATION_CART OUTPUT, @ORGANIZATION_DPC OUTPUT, @ORGANIZATION_LOT OUTPUT, @ORGANIZATION_COUNTYCODEID OUTPUT, @ORGANIZATION_CONGRESSIONALDISTRICTCODEID OUTPUT, @ORGANIZATION_LASTVALIDATIONATTEMPTDATE OUTPUT, @ORGANIZATION_VALIDATIONMESSAGE OUTPUT, @ORGANIZATION_CERTIFICATIONDATA OUTPUT, @ORGANIZATION_PHONETYPECODEID OUTPUT, @ORGANIZATION_NUMBER OUTPUT, @ORGANIZATION_RECIPROCALTYPECODEID OUTPUT, @ORGANIZATION_RELATIONSHIPTYPECODEID OUTPUT, @ORGANIZATION_STARTDATE OUTPUT, @ISCONTACT OUTPUT, @ISPRIMARYCONTACT OUTPUT, @CONTACTTYPECODEID OUTPUT, @POSITION OUTPUT, @ISMATCHINGGIFTRELATIONSHIP OUTPUT, @ISGROUP OUTPUT, @ISHOUSEHOLD OUTPUT, @GIVESANONYMOUSLY OUTPUT, @GROUPTYPECODE OUTPUT, @GROUPTYPEID OUTPUT, @GROUPDESCRIPTION OUTPUT, @GROUPSTARTDATE OUTPUT, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null, @BATCHHOUSEHOLDID output, @HOUSEHOLDID OUTPUT,
@HOUSEHOLD_NAME OUTPUT, @ADDSPOUSETOHOUSEHOLD OUTPUT, @HOUSEHOLDCOPYPRIMARYCONTACTINFO OUTPUT, @ISSPOUSERELATIONSHIP OUTPUT, @ADDRESS_DONOTMAILREASONCODEID OUTPUT, @ORGANIZATION_DONOTMAILREASONCODEID OUTPUT, @NAMEFORMATFUNCTIONID OUTPUT,@JOBCATEGORYCODEID OUTPUT,@CAREERLEVELCODEID OUTPUT;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
if @ISORGANIZATION is null
set @ISORGANIZATION = 0;
begin try
-------------- Insert Individual's Information --------------
insert into dbo.CONSTITUENT
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[KEYNAMEPREFIX],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[MARITALSTATUSCODEID],
[WEBADDRESS],
[ISGROUP],
[GIVESANONYMOUSLY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@ISORGANIZATION,
@KEYNAME,
@KEYNAMEPREFIX,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@BIRTHDATE,
@MARITALSTATUSCODEID,
@WEBADDRESS,
@ISGROUP,
@GIVESANONYMOUSLY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
set @UPDATEDRECOGNITIONS = @CURRENTRECOGNITIONS
--exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_UPDATEIDS
--@BATCHSPONSORSHIPCONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID,
--@CONSTITUENTID = @ID,
--@CHANGEAGENTID = @CHANGEAGENTID,
--@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
--@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output
update dbo.BATCHSPONSORSHIP
set CONSTITUENTID = @ID,
--APPLYTOSHOWNFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
--GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID
exec dbo.USP_SPONSORSHIPBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHSPONSORSHIPCONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID,
@CONSTITUENTID = @ID,
@CHANGEAGENTID = @CHANGEAGENTID,
@BATCHSPONSORSHIPCONSTITUENTACCOUNTID = @BATCHSPONSORSHIPCONSTITUENTACCOUNTID,
@CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output
/* Start Individual 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],
[DONOTMAILREASONCODEID],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@ID,
@ADDRESS_ADDRESSTYPECODEID,
1,
@ADDRESS_DONOTMAIL,
@ADDRESS_DONOTMAILREASONCODEID,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[CERTIFICATIONDATA],
[OMITFROMVALIDATION],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@ADDRESS_OMITFROMVALIDATION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/* End Individual Address */
/* Start Individual Primary Phone */
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
begin
insert into dbo.[PHONE]
(
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/* End Primary Phone */
/* Start Individual Primary Email Address */
if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
begin
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
1,
@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*/
/* Start sites*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@ID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/* End sites*/
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION])
SELECT
@ID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE ((NFD.APPLYTOCODE = 0 and @ISORGANIZATION = 0 and @ISGROUP = 0)
or (NFD.APPLYTOCODE = 1 and @ISORGANIZATION = 0 and @ISGROUP = 1 and @ISHOUSEHOLD = 1)
or (NFD.APPLYTOCODE = 2 and @ISORGANIZATION = 0 and @ISGROUP = 1 and @ISHOUSEHOLD = 0))
/*End name format defaults*/
if @ISORGANIZATION != 0
begin
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
)
values
(
@ID,
@INDUSTRYCODEID,
@NUMEMPLOYEES,
@NUMSUBSIDIARIES,
@PARENTCORPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end;
else
begin
if @ISGROUP != 0
begin
insert into dbo.GROUPDATA
(
ID,
GROUPTYPECODE,
GROUPTYPEID,
DESCRIPTION,
STARTDATE,
NAMEFORMATFUNCTIONID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
@GROUPTYPECODE,
@GROUPTYPEID,
@GROUPDESCRIPTION,
@GROUPSTARTDATE,
@NAMEFORMATFUNCTIONID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
-- Handle group's members
-- Create table variable to hold mapping between BATCHSPONSORSHIPCONSTITUENT.ID and
-- CONSTITUENT.ID
declare @IDMAPPING table
(
SPONSORSHIPBATCHCONSTITUENTID uniqueidentifier,
CONSTITUENTID uniqueidentifier
)
declare GROUPMEMBERCURSOR cursor local fast_forward for
select C.ID, C.EXISTINGCONSTITUENTID, GM.ISPRIMARY, GM.COPYGROUPCONTACTINFOTOMEMBER
from dbo.BATCHSPONSORSHIPCONSTITUENT C
inner join dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER GM on C.ID = GM.MEMBERID
where GM.GROUPID = @BATCHSPONSORSHIPCONSTITUENTID
open GROUPMEMBERCURSOR
declare @BATCHSPONSORSHIPMEMBERID uniqueidentifier, @EXISTINGMEMBERID uniqueidentifier, @ISPRIMARY bit, @COPYGROUPPRIMARYCONTACTINFO bit
fetch next from GROUPMEMBERCURSOR into @BATCHSPONSORSHIPMEMBERID, @EXISTINGMEMBERID, @ISPRIMARY, @COPYGROUPPRIMARYCONTACTINFO
while @@FETCH_STATUS = 0
begin
if @EXISTINGMEMBERID is null
begin
set @EXISTINGMEMBERID = newid()
exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_ADD
@ID = @EXISTINGMEMBERID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@BATCHSPONSORSHIPCONSTITUENTID = @BATCHSPONSORSHIPMEMBERID,
@BYPASSINDIVIDUALHOUSEHOLDADD = @ISHOUSEHOLD,
@BYPASSINDIVIDUALSPOUSEADD = @ISHOUSEHOLD,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output;
end
declare @GROUPMEMBERID uniqueidentifier
set @GROUPMEMBERID = newid()
insert into dbo.GROUPMEMBER
(
ID,
GROUPID,
MEMBERID,
ISPRIMARY,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@GROUPMEMBERID,
@ID,
@EXISTINGMEMBERID,
0,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
insert into dbo.GROUPMEMBERDATERANGE
(
ID,
GROUPMEMBERID,
DATEFROM,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
newid(),
@GROUPMEMBERID,
null,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
-- Update ISPRIMARY value rather than inserting since
-- the GROUPMEMBERDATERANGE record needs to exist before ISPRIMARY
-- can be true
if @ISPRIMARY = 1
update dbo.GROUPMEMBER
set
ISPRIMARY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
where
ID = @GROUPMEMBERID
if @COPYGROUPPRIMARYCONTACTINFO = 1
exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @ID, @EXISTINGMEMBERID
insert into @IDMAPPING (SPONSORSHIPBATCHCONSTITUENTID, CONSTITUENTID)
values (@BATCHSPONSORSHIPMEMBERID, @EXISTINGMEMBERID)
fetch next from GROUPMEMBERCURSOR into @BATCHSPONSORSHIPMEMBERID, @EXISTINGMEMBERID, @ISPRIMARY, @COPYGROUPPRIMARYCONTACTINFO
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close GROUPMEMBERCURSOR;
deallocate GROUPMEMBERCURSOR;
-- Create relationships between group members
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
ISSPOUSE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
newid(),
RELATIONSHIPCONSTITUENT.CONSTITUENTID,
RECIPROCALCONSTITUENT.CONSTITUENTID,
R.RELATIONSHIPTYPECODEID,
R.RECIPROCALTYPECODEID,
R.STARTDATE,
R.ISSPOUSE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.BATCHSPONSORSHIPCONSTITUENTRELATION R
-- Joining to @IDMAPPING ensures that only the added group members are inserted and
-- the BATCHSPONSORSHIPECONSTITUENT IDs can be mapped to CONSTITUENT IDs
inner join @IDMAPPING RELATIONSHIPCONSTITUENT on R.CONSTITUENTID = RELATIONSHIPCONSTITUENT.SPONSORSHIPBATCHCONSTITUENTID
inner join @IDMAPPING RECIPROCALCONSTITUENT on R.RELATIONID = RECIPROCALCONSTITUENT.SPONSORSHIPBATCHCONSTITUENTID
-- update recognition relationship defaults
declare @RELATIONSHIPCONSTITUENTID uniqueidentifier
declare @RECIPROCALCONSTITUENTID uniqueidentifier
declare @RELATIONSHIPTYPECODEID uniqueidentifier
declare @RECIPROCALTYPECODEID uniqueidentifier
select @RELATIONSHIPCONSTITUENTID = RELATIONSHIPCONSTITUENT.CONSTITUENTID,
@RECIPROCALCONSTITUENTID = RECIPROCALCONSTITUENT.CONSTITUENTID,
@RELATIONSHIPTYPECODEID = R.RELATIONSHIPTYPECODEID,
@RECIPROCALTYPECODEID = R.RECIPROCALTYPECODEID
from dbo.BATCHSPONSORSHIPCONSTITUENTRELATION R
-- Joining to @IDMAPPING ensures that only the added group members are inserted and
-- the BATCHSPONSORSHIPECONSTITUENT IDs can be mapped to CONSTITUENT IDs
inner join @IDMAPPING RELATIONSHIPCONSTITUENT on R.CONSTITUENTID = RELATIONSHIPCONSTITUENT.SPONSORSHIPBATCHCONSTITUENTID
inner join @IDMAPPING RECIPROCALCONSTITUENT on R.RELATIONID = RECIPROCALCONSTITUENT.SPONSORSHIPBATCHCONSTITUENTID
declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
declare @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit
declare @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2)
declare @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit
declare @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2)
set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0
set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0
select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPECODEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@RECIPROCALTYPECODEID
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @RELATIONSHIPCONSTITUENTID,
@RECIPROCALCONSTITUENTID,
@CURRENTDATE,
NULL,
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR,
@RECIPROCALRECOGNITIONTYPECODEID,
@CHANGEAGENTID,
0;
--end update recognition relationship defaults
-- Recalculate the household name if it is dynamically named
if @NAMEFORMATFUNCTIONID is not null
update dbo.CONSTITUENT
set CONSTITUENT.KEYNAME = left(dbo.UFN_NAMEFORMAT_FROMID(GD.NAMEFORMATFUNCTIONID, PM.MEMBERID), 100),
CONSTITUENT.[CHANGEDBYID] = @CHANGEAGENTID,
CONSTITUENT.[DATECHANGED] = @CURRENTDATE
from dbo.GROUPDATA GD
inner join dbo.GROUPMEMBER PM on GD.ID = PM.GROUPID and PM.ISPRIMARY <> 0
where CONSTITUENT.ID = @ID and GD.ID = @ID and GD.NAMEFORMATFUNCTIONID is not null
end
else
begin
-------------- Insert Spouse's Information --------------
declare @ISSPOUSE bit
declare @RELATIONSHIPID uniqueidentifier
if (@SPOUSEID is not null)
set @EXISTINGSPOUSE = 1
if (@SPOUSEID is not null) or (coalesce(@SPOUSE_LASTNAME,'') <> '')
set @ISSPOUSE = 1
else
set @ISSPOUSE = 0
-- Have matching factor makes sense only when relationship exists
if @PRIMARYRELATIONSHIPEXISTS = 0
set @PRIMARYMATCHFACTOR = 0;
if @RECIPROCALRELATIONSHIPEXISTS = 0
set @RECIPROCALMATCHFACTOR = 0;
--If there is a spouse create the relationship
if @ISSPOUSE = 1 and @BYPASSINDIVIDUALSPOUSEADD = 0
begin
if @RELATIONSHIPID is null
set @RELATIONSHIPID = newid();
--If you are creating a new spouse make new constituent entry
if @EXISTINGSPOUSE = 0
begin
if @SPOUSEID is null
set @SPOUSEID = newid();
---------Spouse Constituent Information---------
insert into dbo.CONSTITUENT
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[PICTURE],
[PICTURETHUMBNAIL],
[WEBADDRESS],
[MARITALSTATUSCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@SPOUSEID,
0,
@SPOUSE_LASTNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_BIRTHDATE,
null,
null,
'',
@MARITALSTATUSCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
--exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_UPDATEIDS
-- @BATCHSPONSORSHIPCONSTITUENTID = @BATCHSPOUSEID,
-- @CONSTITUENTID = @SPOUSEID,
-- @CHANGEAGENTID = @CHANGEAGENTID,
-- @CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
-- @UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output;
update dbo.BATCHSPONSORSHIP
set CONSTITUENTID = @ID,
--APPLYTOSHOWNFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
--GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID
exec dbo.USP_SPONSORSHIPBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHSPONSORSHIPCONSTITUENTID = @BATCHSPOUSEID,
@CONSTITUENTID = @SPOUSEID,
@CHANGEAGENTID = @CHANGEAGENTID;
/* 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 =@SPOUSEID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/* End security groups*/
/* Start sites*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@SPOUSEID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/* End sites*/
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION])
SELECT
@SPOUSEID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE (NFD.APPLYTOCODE = 0)
/*End name format defaults*/
end
else
begin
--exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_UPDATEIDS
-- @BATCHSPONSORSHIPCONSTITUENTID = @BATCHSPOUSEID,
-- @CONSTITUENTID = @SPOUSEID,
-- @CHANGEAGENTID = @CHANGEAGENTID,
-- @CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
-- @UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output;
update dbo.BATCHSPONSORSHIP
set CONSTITUENTID = @ID,
--APPLYTOSHOWNFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
--GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID
end
declare @EXISTINGID uniqueidentifier
set @EXISTINGID = null
if @COPYPRIMARYINFORMATION = 1
begin
/* Start Spouse 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
if exists (select ID from dbo.ADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
update dbo.ADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID = @SPOUSEID and
ISPRIMARY = 1
select
@EXISTINGID = ID
from
dbo.ADDRESS
where
CONSTITUENTID = @SPOUSEID and
ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID and
DONOTMAIL = @ADDRESS_DONOTMAIL and
DONOTMAILREASONCODEID = @ADDRESS_DONOTMAILREASONCODEID and
COUNTRYID = @ADDRESS_COUNTRYID and
STATEID = @ADDRESS_STATEID and
ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK and
CITY = @ADDRESS_CITY and
POSTCODE = @ADDRESS_POSTCODE
if @EXISTINGID is null
begin
declare @SPOUSE_ADDRESSID uniqueidentifier;
set @SPOUSE_ADDRESSID = newID();
insert into dbo.ADDRESS
(ID, CONSTITUENTID,ADDRESSTYPECODEID,ISPRIMARY,DONOTMAIL,DONOTMAILREASONCODEID,COUNTRYID,STATEID,ADDRESSBLOCK,CITY,POSTCODE, CART, DPC, LOT, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values
(@SPOUSE_ADDRESSID, @SPOUSEID,@ADDRESS_ADDRESSTYPECODEID,1,@ADDRESS_DONOTMAIL,@ADDRESS_DONOTMAILREASONCODEID,@ADDRESS_COUNTRYID,@ADDRESS_STATEID,@ADDRESS_ADDRESSBLOCK,@ADDRESS_CITY,@ADDRESS_POSTCODE, @ADDRESS_CART, @ADDRESS_DPC, @ADDRESS_LOT, @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values
(@SPOUSE_ADDRESSID, @ADDRESS_OMITFROMVALIDATION, @ADDRESS_COUNTYCODEID, @ADDRESS_CONGRESSIONALDISTRICTCODEID, @ADDRESS_LASTVALIDATIONATTEMPTDATE, @ADDRESS_VALIDATIONMESSAGE, @ADDRESS_CERTIFICATIONDATA, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
else
update dbo.ADDRESS
set
ISPRIMARY = 1
where
ID = @EXISTINGID
-- Remove blank address if one was created when adding an individual (no contact info entered creates blank address)
declare @BLANKADDRESSID uniqueidentifier
select
@BLANKADDRESSID = ID
from
dbo.ADDRESS
where
CONSTITUENTID = @SPOUSEID and
ADDRESSTYPECODEID is null and
DONOTMAIL = 0 and
STARTDATE = 0000 and
ENDDATE = 0000 and
STATEID is null and
ADDRESSBLOCK = '' and
CITY = '' and
POSTCODE = '' and
CART = '' and
DPC = '' and
LOT = '' and
ISPRIMARY = 0
if @BLANKADDRESSID is not null
delete from dbo.ADDRESS
where ID = @BLANKADDRESSID
end
/* End Spouse Address */
/* Start Spouse Primary Phone */
set @EXISTINGID = null
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
begin
if exists (select ID from dbo.PHONE where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
update dbo.PHONE
set
ISPRIMARY = 0
where
CONSTITUENTID = @SPOUSEID and
ISPRIMARY = 1
select
@EXISTINGID = ID
from
dbo.PHONE
where
CONSTITUENTID = @SPOUSEID and
PHONETYPECODEID = @PHONE_PHONETYPECODEID and
NUMBER = @PHONE_NUMBER
if @EXISTINGID is null
insert into dbo.PHONE
(CONSTITUENTID,PHONETYPECODEID,NUMBER,ISPRIMARY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values
(@SPOUSEID,@PHONE_PHONETYPECODEID,@PHONE_NUMBER,1,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
else
update dbo.PHONE
set
ISPRIMARY = 1
where
ID = @EXISTINGID
end
/* End Spouse Primary Phone */
/* Start Spouse Primary Email Address */
set @EXISTINGID = null
if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
begin
if exists (select ID from dbo.EMAILADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
update dbo.EMAILADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID = @SPOUSEID and
ISPRIMARY = 1
select
@EXISTINGID = ID
from
dbo.EMAILADDRESS
where
CONSTITUENTID = @SPOUSEID and
EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID and
EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS
if @EXISTINGID is null
insert into dbo.EMAILADDRESS
(CONSTITUENTID,EMAILADDRESSTYPECODEID,EMAILADDRESS,ISPRIMARY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values
(@SPOUSEID,@EMAILADDRESS_EMAILADDRESSTYPECODEID,@EMAILADDRESS_EMAILADDRESS,1,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
else
update dbo.EMAILADDRESS
set
ISPRIMARY = 1
where
ID = @EXISTINGID
end
/* End Spouse Email Address */
end
---------Spouse Relationship---------
-- Only create the relationship if the relationship types are set.
-- It's possible that the BATCHSPONSORSHIPCONSTITUENTRELATIONSHIP entry
-- exists to indicate the spouse constituent but no relationship
-- should actually be created.
if @SPOUSE_RELATIONSHIPTYPECODEID is not null and @SPOUSE_RECIPROCALTYPECODEID is not null
begin
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
STARTDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPID,
@ID,
@SPOUSEID,
@SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID,
@ISSPOUSERELATIONSHIP,
@SPOUSE_STARTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- update recognition relationship defaults
set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0
set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0
select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SPOUSE_RELATIONSHIPTYPECODEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SPOUSE_RECIPROCALTYPECODEID
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @ID,
@SPOUSEID,
@CURRENTDATE,
NULL,
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR,
@RECIPROCALRECOGNITIONTYPECODEID,
@CHANGEAGENTID,
0;
--end update recognition relationship defaults
end
if @ISSPOUSERELATIONSHIP = 1 and not exists (select ID from dbo.DISABLEDWEALTHUPDATES where ID = @SPOUSEID)
insert into dbo.DISABLEDWEALTHUPDATES
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SPOUSEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
-- Insert individual's household if needed
if @BYPASSINDIVIDUALHOUSEHOLDADD = 0 and (@HOUSEHOLDID is not null or coalesce(@HOUSEHOLD_NAME, '') <> '')
begin
if @HOUSEHOLDID is null
begin
set @HOUSEHOLDID = newid()
insert into dbo.CONSTITUENT
(
[ID],
[ISGROUP],
[KEYNAME],
[ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED]
)
values
(
@HOUSEHOLDID,
1,
@HOUSEHOLD_NAME,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
--exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_UPDATEIDS
-- @BATCHSPONSORSHIPCONSTITUENTID = @BATCHHOUSEHOLDID,
-- @CONSTITUENTID = @HOUSEHOLDID,
-- @CHANGEAGENTID = @CHANGEAGENTID,
-- @CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
-- @UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output;
update dbo.BATCHSPONSORSHIP
set CONSTITUENTID = @ID,
--APPLYTOSHOWNFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
--GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID
exec dbo.USP_SPONSORSHIPBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHSPONSORSHIPCONSTITUENTID = @BATCHHOUSEHOLDID,
@CONSTITUENTID = @HOUSEHOLDID,
@CHANGEAGENTID = @CHANGEAGENTID;
insert into dbo.GROUPDATA
(
[ID],
[GROUPTYPECODE],
[ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED]
)
values
(
@HOUSEHOLDID,
0, -- Indicates group is a household
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
/* 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 =@HOUSEHOLDID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/* End security groups*/
/* Start sites*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@HOUSEHOLDID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/* End sites*/
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION])
SELECT
@HOUSEHOLDID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE (NFD.APPLYTOCODE = 1)
/*End name format defaults*/
end
else
begin
--exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_UPDATEIDS
-- @BATCHSPONSORSHIPCONSTITUENTID = @BATCHHOUSEHOLDID,
-- @CONSTITUENTID = @HOUSEHOLDID,
-- @CHANGEAGENTID = @CHANGEAGENTID,
-- @CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
-- @UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output;
update dbo.BATCHSPONSORSHIP
set CONSTITUENTID = @ID,
--APPLYTOSHOWNFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
--GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID
end
exec dbo.USP_GROUPMEMBERADD null, @CHANGEAGENTID, @HOUSEHOLDID, @ID
if @HOUSEHOLDCOPYPRIMARYCONTACTINFO = 1
exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @ID, @HOUSEHOLDID
if @ADDSPOUSETOHOUSEHOLD = 1
exec dbo.USP_GROUPMEMBERADD null, @CHANGEAGENTID, @HOUSEHOLDID, @SPOUSEID
end
end
-------------- Insert Organization Information --------------
declare @HASBUSINESS bit
set @RELATIONSHIPID = null
if (@ORGANIZATIONID is not null)
set @EXISTINGORGANIZATION = 1
if (@ORGANIZATIONID is not null) or (coalesce(@ORGANIZATION_KEYNAME,'') <> '')
set @HASBUSINESS = 1
else
set @HASBUSINESS = 0
--If their is an organization create the relationship
if @HASBUSINESS = 1
begin
if @RELATIONSHIPID is null
set @RELATIONSHIPID = newid();
--if you are creating a new organization make a new constituent entry
if @EXISTINGORGANIZATION = 0
begin
if @ORGANIZATIONID is null
set @ORGANIZATIONID = newid();
---------Organization Constituent Information---------
insert into dbo.CONSTITUENT
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[KEYNAMEPREFIX],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ORGANIZATIONID,
-1,
@ORGANIZATION_KEYNAME,
@ORGANIZATION_KEYNAMEPREFIX,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
--exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_UPDATEIDS
-- @BATCHSPONSORSHIPCONSTITUENTID = @BATCHORGANIZATIONID,
-- @CONSTITUENTID = @ORGANIZATIONID,
-- @CHANGEAGENTID = @CHANGEAGENTID,
-- @CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
-- @UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output;
update dbo.BATCHSPONSORSHIP
set CONSTITUENTID = @ID,
--APPLYTOSHOWNFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
--GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID
exec dbo.USP_SPONSORSHIPBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHSPONSORSHIPCONSTITUENTID = @BATCHORGANIZATIONID,
@CONSTITUENTID = @ORGANIZATIONID,
@CHANGEAGENTID = @CHANGEAGENTID;
/*Start Primary Address */
if
(
(@ORGANIZATION_STATEID is not null)
or (coalesce(@ORGANIZATION_ADDRESSBLOCK,'') <> '')
or (coalesce(@ORGANIZATION_CITY,'') <> '')
or (coalesce(@ORGANIZATION_POSTCODE,'') <> '')
or (@ORGANIZATION_ADDRESSTYPECODEID is not null)
or (@ORGANIZATION_COUNTRYID is not null)
or (@ORGANIZATION_DONOTMAIL = 1)
or (@ORGANIZATION_DONOTMAILREASONCODEID is not null)
)
begin
declare @ORGANIZATION_ADDRESSID uniqueidentifier;
set @ORGANIZATION_ADDRESSID = newID();
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
DONOTMAIL,
DONOTMAILREASONCODEID,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGANIZATION_ADDRESSID,
@ORGANIZATIONID,
@ORGANIZATION_ADDRESSTYPECODEID,
1,
@ORGANIZATION_DONOTMAIL,
@ORGANIZATION_DONOTMAILREASONCODEID,
@ORGANIZATION_COUNTRYID,
@ORGANIZATION_STATEID,
@ORGANIZATION_ADDRESSBLOCK,
@ORGANIZATION_CITY,
@ORGANIZATION_POSTCODE,
@ORGANIZATION_CART,
@ORGANIZATION_DPC,
@ORGANIZATION_LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
OMITFROMVALIDATION,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGANIZATION_ADDRESSID,
@ORGANIZATION_COUNTYCODEID,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
@ORGANIZATION_VALIDATIONMESSAGE,
@ORGANIZATION_CERTIFICATIONDATA,
@ORGANIZATION_OMITFROMVALIDATION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/* End Primary Address */
/*Start Primary Phone */
if (@ORGANIZATION_PHONETYPECODEID is not null) or (coalesce(@ORGANIZATION_NUMBER,'') <> '')
insert into dbo.PHONE
(
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ORGANIZATIONID,
@ORGANIZATION_PHONETYPECODEID,
@ORGANIZATION_NUMBER,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/*End Primary Phone */
/*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 =@ORGANIZATIONID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/*end security groups*/
/* Start sites*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CONSTITUENTID =@ORGANIZATIONID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
/* End sites*/
end
else
begin
--exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_UPDATEIDS
--@BATCHSPONSORSHIPCONSTITUENTID = @BATCHORGANIZATIONID,
--@CONSTITUENTID = @ORGANIZATIONID,
--@CHANGEAGENTID = @CHANGEAGENTID,
--@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
--@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output;
update dbo.BATCHSPONSORSHIP
set CONSTITUENTID = @ID,
--APPLYTOSHOWNFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
--GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHSPONSORSHIP.CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID
end
---------Organization Relationship---------
if @ISMATCHINGGIFTRELATIONSHIP = 1
update
dbo.RELATIONSHIP
set
ISMATCHINGGIFTRELATIONSHIP = 0,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
RELATIONSHIPCONSTITUENTID = @ID and
RECIPROCALCONSTITUENTID = @ORGANIZATIONID and
ISMATCHINGGIFTRELATIONSHIP = 1;
declare @SETID uniqueidentifier = newid();
insert into dbo.RELATIONSHIPSET
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.RELATIONSHIP
(
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISCONTACT,
ISPRIMARYCONTACT,
CONTACTTYPECODEID,
STARTDATE,
POSITION,
ISPRIMARYBUSINESS,
ISMATCHINGGIFTRELATIONSHIP,
RELATIONSHIPSETID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPID,
@ID,
@ORGANIZATIONID,
@ORGANIZATION_RELATIONSHIPTYPECODEID,
@ORGANIZATION_RECIPROCALTYPECODEID,
@ISCONTACT,
@ISPRIMARYCONTACT,
@CONTACTTYPECODEID,
@ORGANIZATION_STARTDATE,
@POSITION,
1,
@ISMATCHINGGIFTRELATIONSHIP,
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- update recognition relationship defaults
set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0
set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0
select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@ORGANIZATION_RELATIONSHIPTYPECODEID
select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=1 and RRD.RELATIONSHIPTYPECODEID=@ORGANIZATION_RECIPROCALTYPECODEID
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @ORGANIZATIONID, @ORGANIZATION_RELATIONSHIPTYPECODEID, @ORGANIZATION_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @ID,
@ORGANIZATIONID,
@CURRENTDATE,
NULL,
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@PRIMARYSOFTCREDITMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID,
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR,
@RECIPROCALRECOGNITIONTYPECODEID,
@CHANGEAGENTID,
0;
if (@JOBCATEGORYCODEID is not null) or
(@CAREERLEVELCODEID is not null) or
(len(@POSITION) > 0)
insert into dbo.RELATIONSHIPJOBINFO
(
RELATIONSHIPSETID,
JOBTITLE,
JOBCATEGORYCODEID,
CAREERLEVELCODEID,
STARTDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SETID,
@POSITION,
@JOBCATEGORYCODEID,
@CAREERLEVELCODEID,
@ORGANIZATION_STARTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
--end update recognition relationship defaults
end
end;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end;