USP_REVENUEBATCH_CONSTITUENT_ADD
Saves an individual record with spouse and business information.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @BATCHREVENUECONSTITUENTID | uniqueidentifier | IN | |
| @BATCHREVENUECONSTITUENTACCOUNTID | uniqueidentifier | IN | |
| @CONSTITUENTACCOUNTID | uniqueidentifier | INOUT | |
| @BYPASSINDIVIDUALHOUSEHOLDADD | bit | IN | |
| @BYPASSINDIVIDUALSPOUSEADD | bit | IN | |
| @CURRENTRECOGNITIONS | xml | IN | |
| @UPDATEDRECOGNITIONS | xml | INOUT | |
| @UPDATEDAPPLICATIONRECOGNITIONS | xml | INOUT | |
| @IDMAPPING | xml | INOUT |
Definition
Copy
CREATE procedure dbo.USP_REVENUEBATCH_CONSTITUENT_ADD
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@BATCHREVENUECONSTITUENTID uniqueidentifier,
@BATCHREVENUECONSTITUENTACCOUNTID uniqueidentifier = null,
@CONSTITUENTACCOUNTID uniqueidentifier = null output,
@BYPASSINDIVIDUALHOUSEHOLDADD bit = 0,
@BYPASSINDIVIDUALSPOUSEADD bit = 0,
@CURRENTRECOGNITIONS xml = null,
@UPDATEDRECOGNITIONS xml = null output,
@UPDATEDAPPLICATIONRECOGNITIONS xml = null output,
@IDMAPPING 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 @GENDERCODEID uniqueidentifier
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
declare @NAMEFORMATFUNCTIONID uniqueidentifier
--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_GENDERCODEID uniqueidentifier
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)
declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
--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(100)
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 @JOBCATEGORYCODEID uniqueidentifier
declare @CAREERLEVELCODEID uniqueidentifier
declare @PHONE_DONOTCALL bit
declare @EMAILADDRESS_DONOTEMAIL bit
declare @ORGANIZATION_PRIMARYRELATIONSHIPEXISTS bit
declare @ORGANIZATION_PRIMARYMATCHFACTOR decimal(5,2)
declare @ORGANIZATION_RECIPROCALRELATIONSHIPEXISTS bit
declare @ORGANIZATION_RECIPROCALMATCHFACTOR decimal(5,2)
declare @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
declare @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
declare @SKIP_ADDING_NAMEFORMATS bit = 0;
declare @SITEID uniqueidentifier
declare @CONSTITUENTSECURITYGROUPID uniqueidentifier
declare @CONSTITUENCYCODEID uniqueidentifier
declare @INFOSOURCECODEID uniqueidentifier
declare @SPOUSECLASSOF dbo.UDT_YEAR
declare @NAMEFORMATS xml
declare @SETID uniqueidentifier
-- Create table variable to hold mapping between BATCHREVENUECONSTITUENT.ID and CONSTITUENT.ID
declare @IDMAPPINGTBL table
(
REVENUEBATCHCONSTITUENTID uniqueidentifier,
CONSTITUENTID uniqueidentifier
)
--load
exec dbo.USP_BATCHREVENUECONSTITUENT_LOAD @BATCHREVENUECONSTITUENTID, @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, @NAMEFORMATFUNCTIONID OUTPUT, 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, @JOBCATEGORYCODEID output, @CAREERLEVELCODEID output, @PHONE_DONOTCALL output, @EMAILADDRESS_DONOTEMAIL output, @PRIMARYRECOGNITIONTYPECODEID output, @RECIPROCALRECOGNITIONTYPECODEID output,
@ORGANIZATION_PRIMARYRELATIONSHIPEXISTS output, @ORGANIZATION_PRIMARYMATCHFACTOR output, @ORGANIZATION_RECIPROCALRELATIONSHIPEXISTS output, @ORGANIZATION_RECIPROCALMATCHFACTOR output, @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID output, @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID output, @SITEID output, @CONSTITUENTSECURITYGROUPID output, @CONSTITUENCYCODEID output, @INFOSOURCECODEID output, @SPOUSECLASSOF output, @NAMEFORMATS output,null,null,@GENDERCODEID output, @SPOUSE_GENDERCODEID 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 --------------
if not exists (select 1 from dbo.CONSTITUENT where ID = @BATCHREVENUECONSTITUENTID)
begin
insert into dbo.CONSTITUENT
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[KEYNAMEPREFIX],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[GENDERCODEID],
[BIRTHDATE],
[MARITALSTATUSCODEID],
[WEBADDRESS],
[ISGROUP],
[GIVESANONYMOUSLY],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@ISORGANIZATION,
@KEYNAME,
@KEYNAMEPREFIX,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@GENDERCODEID,
@BIRTHDATE,
@MARITALSTATUSCODEID,
@WEBADDRESS,
@ISGROUP,
@GIVESANONYMOUSLY,
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end;
else
begin
set @SKIP_ADDING_SECURITYGROUPS = 1;
set @SKIP_ADDING_SITES = 1;
set @SKIP_ADDING_NAMEFORMATS = 1;
update dbo.CONSTITUENT
set [ISORGANIZATION] = @ISORGANIZATION,
[KEYNAME] = @KEYNAME,
[KEYNAMEPREFIX] = @KEYNAMEPREFIX,
[FIRSTNAME] = @FIRSTNAME,
[MIDDLENAME] = @MIDDLENAME,
[MAIDENNAME] = @MAIDENNAME,
[NICKNAME] = @NICKNAME,
[TITLECODEID] = @TITLECODEID,
[SUFFIXCODEID] = @SUFFIXCODEID,
[GENDERCODE] = @GENDERCODE,
[GENDERCODEID] = @GENDERCODEID,
[BIRTHDATE] = @BIRTHDATE,
[MARITALSTATUSCODEID] = @MARITALSTATUSCODEID,
[WEBADDRESS] = @WEBADDRESS,
[ISGROUP] = @ISGROUP,
[GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,
[ISCONSTITUENT] = 1,
[ADDEDBYID] = @CHANGEAGENTID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATEADDED] = @CURRENTDATE,
[DATECHANGED] = @CURRENTDATE
where [ID] = @BATCHREVENUECONSTITUENTID;
end;
set @UPDATEDRECOGNITIONS = @CURRENTRECOGNITIONS
exec dbo.USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS
@BATCHREVENUECONSTITUENTID = @BATCHREVENUECONSTITUENTID,
@CONSTITUENTID = @ID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output,
@UPDATEDAPPLICATIONRECOGNITIONS = @UPDATEDAPPLICATIONRECOGNITIONS output
exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHREVENUECONSTITUENTID = @BATCHREVENUECONSTITUENTID,
@CONSTITUENTID = @ID,
@CHANGEAGENTID = @CHANGEAGENTID,
@BATCHREVENUECONSTITUENTACCOUNTID = @BATCHREVENUECONSTITUENTACCOUNTID,
@CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output;
insert into @IDMAPPINGTBL (REVENUEBATCHCONSTITUENTID, CONSTITUENTID)
values (@BATCHREVENUECONSTITUENTID,@ID )
/* 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;
select @ADDRESSID = ID
from dbo.ADDRESS
where CONSTITUENTID = @BATCHREVENUECONSTITUENTID and ISPRIMARY = 1;
if @ADDRESSID is null
begin
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],
[INFOSOURCECODEID]
)
values
(
@ADDRESSID,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@ADDRESS_OMITFROMVALIDATION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@INFOSOURCECODEID
);
end;
else
begin
update dbo.ADDRESS
set [CONSTITUENTID] = @ID,
[ADDRESSTYPECODEID] = @ADDRESS_ADDRESSTYPECODEID,
[ISPRIMARY] = 1,
[DONOTMAIL] = @ADDRESS_DONOTMAIL,
[DONOTMAILREASONCODEID] = @ADDRESS_DONOTMAILREASONCODEID,
[COUNTRYID] = @ADDRESS_COUNTRYID,
[STATEID] = @ADDRESS_STATEID,
[ADDRESSBLOCK] = @ADDRESS_ADDRESSBLOCK,
[CITY] = @ADDRESS_CITY,
[POSTCODE] = @ADDRESS_POSTCODE,
[CART] = @ADDRESS_CART,
[DPC] = @ADDRESS_DPC,
[LOT] = @ADDRESS_LOT,
[ADDEDBYID] = @CHANGEAGENTID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATEADDED] = @CURRENTDATE,
[DATECHANGED] = @CURRENTDATE
where [ID] = @ADDRESSID;
update dbo.ADDRESSVALIDATIONUPDATE
set [COUNTYCODEID] = @ADDRESS_COUNTYCODEID,
[CONGRESSIONALDISTRICTCODEID] = @ADDRESS_CONGRESSIONALDISTRICTCODEID,
[LASTVALIDATIONATTEMPTDATE] = @ADDRESS_LASTVALIDATIONATTEMPTDATE,
[VALIDATIONMESSAGE] = @ADDRESS_VALIDATIONMESSAGE,
[CERTIFICATIONDATA] = @ADDRESS_CERTIFICATIONDATA,
[OMITFROMVALIDATION] = @ADDRESS_OMITFROMVALIDATION,
[ADDEDBYID] = @CHANGEAGENTID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATEADDED] = @CURRENTDATE,
[DATECHANGED] = @CURRENTDATE
where [ID] = @ADDRESSID;
end;
end
/* End Individual Address */
/* Start Individual Primary Phone */
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
begin
declare @PHONEID uniqueidentifier;
select @PHONEID = ID
from dbo.PHONE
where CONSTITUENTID = @BATCHREVENUECONSTITUENTID and ISPRIMARY = 1;
declare @PHONE_COUNTRYID uniqueidentifier = @ADDRESS_COUNTRYID;
if @ADDRESS_COUNTRYID is null
exec @PHONE_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if @PHONEID is null
begin
insert into dbo.[PHONE]
(
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[DONOTCALL],
[COUNTRYID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[INFOSOURCECODEID]
)
values
(
@ID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
1,
coalesce(@PHONE_DONOTCALL, 0),
@PHONE_COUNTRYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@INFOSOURCECODEID
);
end;
else
begin
update dbo.[PHONE]
set [CONSTITUENTID] = @ID,
[PHONETYPECODEID] = @PHONE_PHONETYPECODEID,
[NUMBER] = @PHONE_NUMBER,
[ISPRIMARY] = 1,
[DONOTCALL] = coalesce(@PHONE_DONOTCALL, 0),
[ADDEDBYID] = @CHANGEAGENTID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATEADDED] = @CURRENTDATE,
[DATECHANGED] = @CURRENTDATE
where [ID] = @PHONEID;
end;
end
/* End Primary Phone */
/* Start Individual Primary Email Address */
if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
begin
declare @EMAILADDRESSID uniqueidentifier;
select @EMAILADDRESSID = ID
from dbo.EMAILADDRESS
where CONSTITUENTID = @BATCHREVENUECONSTITUENTID and ISPRIMARY = 1;
if @EMAILADDRESSID is null
begin
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[DONOTEMAIL],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[INFOSOURCECODEID]
)
values
(
@ID,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
1,
@EMAILADDRESS_DONOTEMAIL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@INFOSOURCECODEID
);
end;
else
begin
update dbo.[EMAILADDRESS]
set [CONSTITUENTID] = @ID,
[EMAILADDRESSTYPECODEID] = @EMAILADDRESS_EMAILADDRESSTYPECODEID,
[EMAILADDRESS] = @EMAILADDRESS_EMAILADDRESS,
[ISPRIMARY] = 1,
[DONOTEMAIL] = @EMAILADDRESS_DONOTEMAIL,
[ADDEDBYID] = @CHANGEAGENTID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATEADDED] = @CURRENTDATE,
[DATECHANGED] = @CURRENTDATE
where [ID] = @EMAILADDRESSID;
end;
end
/* End Primary Email Address */
if @CONSTITUENTSECURITYGROUPID is null
begin
/* 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*/
end
else
begin
if coalesce(@SKIP_ADDING_SECURITYGROUPS,0) = 0
insert into dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT(DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID, CONSTITUENTID, CONSTIT_SECURITY_ATTRIBUTEID)
select
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@ID,
@CONSTITUENTSECURITYGROUPID
end
if @SITEID is null
begin
/* 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*/
end
else
begin
if coalesce(@SKIP_ADDING_SITES,0) = 0
insert into dbo.CONSTITUENTSITE
(
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID,
CONSTITUENTID,
SITEID
)
select
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@ID,
@SITEID
end
/*Adding constituencies*/
if @CONSTITUENCYCODEID is not null
begin
declare @CONSTITUENCYTABLE as dbo.UDT_CONSTITUENTUPDATEBATCH_CONSTITUENCY
insert into @CONSTITUENCYTABLE
(
ID, CONSTITUENCYCODEID, DATEFROM, DATETO, ORIGINALCONSTITUENCYID
)
values (newid(), @CONSTITUENCYCODEID, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE), null, null)
exec dbo.USP_CONSTITUENTUPDATEBATCH_ADDCONSTITUENCIES @CONSTITUENCYTABLE, @ID, null, @CHANGEAGENTID;
end
/*Adding infosource code*/
if @INFOSOURCECODEID is not null
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTORIGIN @ID, @CHANGEAGENTID, @INFOSOURCECODEID, null;
if @SKIP_ADDING_NAMEFORMATS = 0 begin
/*Add name formats with values*/
if @ISORGANIZATION = 0 and @ISGROUP = 0 begin
exec dbo.USP_CONSTITUENTUPDATEBATCH_ADDUPDATENAMEFORMATS @ID, @NAMEFORMATS, @CHANGEAGENTID, @CURRENTDATE;
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
NFD.NAMEFORMATTYPECODEID not in (select NAMEFORMATTYPECODEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETNAMEFORMATS_FROMITEMLISTXML(@NAMEFORMATS));
end;
/*End name format defaults*/
end;
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
);
if @PARENTCORPID is not null
exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @ID, @PARENTCORPID,@CURRENTDATE,@CHANGEAGENTID,@CURRENTDATE,1,null
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
declare @GROUPMEMBER_PRIMARYRELATIONSHIPEXISTS1 bit
declare @GROUPMEMBER_PRIMARYMATCHFACTOR1 decimal(5,2)
declare @GROUPMEMBER_RECIPROCALRELATIONSHIPEXISTS1 bit
declare @GROUPMEMBER_RECIPROCALMATCHFACTOR1 decimal(5,2)
declare @GROUPMEMBER_PRIMARYRECOGNITIONTYPECODEID1 uniqueidentifier
declare @GROUPMEMBER_RECIPROCALRECOGNITIONTYPECODEID1 uniqueidentifier
declare @GROUPMEMBER_STARTDATE1 datetime
declare @GROUPMEMBER_RELATIONID1 uniqueidentifier
declare @GROUPMEMBER_CONSTITUENTID1 uniqueidentifier
declare @GROUPMEMBER_PRIMARYRELATIONSHIPEXISTS2 bit
declare @GROUPMEMBER_PRIMARYMATCHFACTOR2 decimal(5,2)
declare @GROUPMEMBER_RECIPROCALRELATIONSHIPEXISTS2 bit
declare @GROUPMEMBER_RECIPROCALMATCHFACTOR2 decimal(5,2)
declare @GROUPMEMBER_PRIMARYRECOGNITIONTYPECODEID2 uniqueidentifier
declare @GROUPMEMBER_RECIPROCALRECOGNITIONTYPECODEID2 uniqueidentifier
declare @GROUPMEMBER_STARTDATE2 datetime
declare @GROUPMEMBER_RELATIONID2 uniqueidentifier
declare @GROUPMEMBER_CONSTITUENTID2 uniqueidentifier
declare GROUPMEMBERCURSOR cursor local fast_forward for
select C.ID, C.EXISTINGCONSTITUENTID, GM.ISPRIMARY, GM.COPYGROUPCONTACTINFOTOMEMBER
from dbo.BATCHREVENUECONSTITUENT C
inner join dbo.BATCHREVENUECONSTITUENTGROUPMEMBER GM on C.ID = GM.MEMBERID
where GM.GROUPID = @BATCHREVENUECONSTITUENTID
open GROUPMEMBERCURSOR
declare @BATCHREVENUEMEMBERID uniqueidentifier, @EXISTINGMEMBERID uniqueidentifier, @ISPRIMARY bit, @COPYGROUPPRIMARYCONTACTINFO bit
fetch next from GROUPMEMBERCURSOR into @BATCHREVENUEMEMBERID, @EXISTINGMEMBERID, @ISPRIMARY, @COPYGROUPPRIMARYCONTACTINFO
while @@FETCH_STATUS = 0
begin
if @EXISTINGMEMBERID is null
begin
set @EXISTINGMEMBERID = newid()
exec dbo.USP_REVENUEBATCH_CONSTITUENT_ADD
@ID = @EXISTINGMEMBERID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@BATCHREVENUECONSTITUENTID = @BATCHREVENUEMEMBERID,
@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
begin
update dbo.GROUPMEMBER
set
ISPRIMARY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
where
ID = @GROUPMEMBERID;
exec dbo.USP_GROUP_COPYPHONE @ID, @EXISTINGMEMBERID, @PHONE_NUMBER, @CHANGEAGENTID;
end
if @COPYGROUPPRIMARYCONTACTINFO = 1
exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @ID, @EXISTINGMEMBERID
insert into @IDMAPPINGTBL (REVENUEBATCHCONSTITUENTID, CONSTITUENTID)
values (@BATCHREVENUEMEMBERID, @EXISTINGMEMBERID)
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGUREFORREVENUEBATCH @EXISTINGMEMBERID, @CHANGEAGENTID
set @GROUPMEMBER_RELATIONID1 = null
set @GROUPMEMBER_CONSTITUENTID1 = null
select
@GROUPMEMBER_PRIMARYRELATIONSHIPEXISTS1=R.PRIMARYRELATIONSHIPEXISTS,
@GROUPMEMBER_PRIMARYMATCHFACTOR1=R.PRIMARYMATCHFACTOR,
@GROUPMEMBER_RECIPROCALRELATIONSHIPEXISTS1=R.RECIPROCALRELATIONSHIPEXISTS,
@GROUPMEMBER_RECIPROCALMATCHFACTOR1=R.RECIPROCALMATCHFACTOR,
@GROUPMEMBER_PRIMARYRECOGNITIONTYPECODEID1=R.PRIMARYRECOGNITIONTYPECODEID,
@GROUPMEMBER_RECIPROCALRECOGNITIONTYPECODEID1=R.RECIPROCALRECOGNITIONTYPECODEID,
@GROUPMEMBER_STARTDATE1=R.STARTDATE,
@GROUPMEMBER_RELATIONID1 =C.ID
from dbo.BATCHREVENUECONSTITUENTRELATION R
inner join dbo.BATCHREVENUECONSTITUENT C on R.RELATIONID=C.ID
where R.CONSTITUENTID = @BATCHREVENUEMEMBERID
if not @GROUPMEMBER_RELATIONID1 is null
begin
select @GROUPMEMBER_CONSTITUENTID1 = IDM.CONSTITUENTID from @IDMAPPINGTBL IDM where IDM.REVENUEBATCHCONSTITUENTID=@GROUPMEMBER_RELATIONID1
if not @GROUPMEMBER_CONSTITUENTID1 is null
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @EXISTINGMEMBERID, @GROUPMEMBER_CONSTITUENTID1,
@GROUPMEMBER_STARTDATE1, NULL, @GROUPMEMBER_PRIMARYRELATIONSHIPEXISTS1, @GROUPMEMBER_PRIMARYMATCHFACTOR1,
@GROUPMEMBER_PRIMARYRECOGNITIONTYPECODEID1, @GROUPMEMBER_RECIPROCALRELATIONSHIPEXISTS1,
@GROUPMEMBER_RECIPROCALMATCHFACTOR1, @GROUPMEMBER_RECIPROCALRECOGNITIONTYPECODEID1, @CHANGEAGENTID, 0;
end
set @GROUPMEMBER_RELATIONID2 = null
set @GROUPMEMBER_CONSTITUENTID2 = null
select
@GROUPMEMBER_PRIMARYRELATIONSHIPEXISTS2=R.PRIMARYRELATIONSHIPEXISTS,
@GROUPMEMBER_PRIMARYMATCHFACTOR2=R.PRIMARYMATCHFACTOR,
@GROUPMEMBER_RECIPROCALRELATIONSHIPEXISTS2=R.RECIPROCALRELATIONSHIPEXISTS,
@GROUPMEMBER_RECIPROCALMATCHFACTOR2=R.RECIPROCALMATCHFACTOR,
@GROUPMEMBER_PRIMARYRECOGNITIONTYPECODEID2=R.PRIMARYRECOGNITIONTYPECODEID,
@GROUPMEMBER_RECIPROCALRECOGNITIONTYPECODEID2=R.RECIPROCALRECOGNITIONTYPECODEID,
@GROUPMEMBER_STARTDATE2=R.STARTDATE,
@GROUPMEMBER_RELATIONID2 =C.ID
from dbo.BATCHREVENUECONSTITUENTRELATION R
inner join dbo.BATCHREVENUECONSTITUENT C on R.CONSTITUENTID=C.ID
where R.RELATIONID = @BATCHREVENUEMEMBERID
if not @GROUPMEMBER_RELATIONID2 is null
begin
select @GROUPMEMBER_CONSTITUENTID2 = IDM.CONSTITUENTID from @IDMAPPINGTBL IDM where IDM.REVENUEBATCHCONSTITUENTID = @GROUPMEMBER_RELATIONID2
if not @GROUPMEMBER_CONSTITUENTID2 is null
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @GROUPMEMBER_CONSTITUENTID2, @EXISTINGMEMBERID,
@GROUPMEMBER_STARTDATE2, NULL, @GROUPMEMBER_PRIMARYRELATIONSHIPEXISTS2, @GROUPMEMBER_PRIMARYMATCHFACTOR2,
@GROUPMEMBER_PRIMARYRECOGNITIONTYPECODEID2, @GROUPMEMBER_RECIPROCALRELATIONSHIPEXISTS2,
@GROUPMEMBER_RECIPROCALMATCHFACTOR2, @GROUPMEMBER_RECIPROCALRECOGNITIONTYPECODEID2, @CHANGEAGENTID, 0;
end
fetch next from GROUPMEMBERCURSOR into @BATCHREVENUEMEMBERID, @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.BATCHREVENUECONSTITUENTRELATION R
-- Joining to @IDMAPPINGTBL ensures that only the added group members are inserted and
-- the BATCHREVENUEECONSTITUENT IDs can be mapped to CONSTITUENT IDs
inner join @IDMAPPINGTBL RELATIONSHIPCONSTITUENT on R.CONSTITUENTID = RELATIONSHIPCONSTITUENT.REVENUEBATCHCONSTITUENTID
inner join @IDMAPPINGTBL RECIPROCALCONSTITUENT on R.RELATIONID = RECIPROCALCONSTITUENT.REVENUEBATCHCONSTITUENTID
-- 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
begin
set @PRIMARYMATCHFACTOR = 0;
set @PRIMARYRECOGNITIONTYPECODEID = null;
end
if @RECIPROCALRELATIONSHIPEXISTS = 0
begin
set @RECIPROCALMATCHFACTOR = 0;
set @RECIPROCALRECOGNITIONTYPECODEID = null;
end
--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],
[GENDERCODEID],
[BIRTHDATE],
[PICTURE],
[PICTURETHUMBNAIL],
[WEBADDRESS],
[MARITALSTATUSCODEID],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@SPOUSEID,
0,
@SPOUSE_LASTNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_GENDERCODEID,
@SPOUSE_BIRTHDATE,
null,
null,
'',
@MARITALSTATUSCODEID,
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS
@BATCHREVENUECONSTITUENTID = @BATCHSPOUSEID,
@CONSTITUENTID = @SPOUSEID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output,
@UPDATEDAPPLICATIONRECOGNITIONS = @UPDATEDAPPLICATIONRECOGNITIONS output;
insert into @IDMAPPINGTBL (REVENUEBATCHCONSTITUENTID, CONSTITUENTID)
values (@BATCHSPOUSEID,@SPOUSEID )
exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHREVENUECONSTITUENTID = @BATCHSPOUSEID,
@CONSTITUENTID = @SPOUSEID,
@CHANGEAGENTID = @CHANGEAGENTID,
@BATCHREVENUECONSTITUENTACCOUNTID = @BATCHREVENUECONSTITUENTACCOUNTID,
@CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output;
/* 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*/
/*Adding infosource code*/
if @INFOSOURCECODEID is not null
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTORIGIN @SPOUSEID, @CHANGEAGENTID, @INFOSOURCECODEID, null;
/*End infosource code*/
end
else
begin
exec dbo.USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS
@BATCHREVENUECONSTITUENTID = @BATCHSPOUSEID,
@CONSTITUENTID = @SPOUSEID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output,
@UPDATEDAPPLICATIONRECOGNITIONS = @UPDATEDAPPLICATIONRECOGNITIONS output;
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, INFOSOURCECODEID)
values
(@SPOUSE_ADDRESSID, @ADDRESS_OMITFROMVALIDATION, @ADDRESS_COUNTYCODEID, @ADDRESS_CONGRESSIONALDISTRICTCODEID, @ADDRESS_LASTVALIDATIONATTEMPTDATE, @ADDRESS_VALIDATIONMESSAGE, @ADDRESS_CERTIFICATIONDATA, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID)
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,INFOSOURCECODEID)
values
(@SPOUSEID,@PHONE_PHONETYPECODEID,@PHONE_NUMBER,1,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@INFOSOURCECODEID)
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,INFOSOURCECODEID)
values
(@SPOUSEID,@EMAILADDRESS_EMAILADDRESSTYPECODEID,@EMAILADDRESS_EMAILADDRESS,1,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@INFOSOURCECODEID)
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 BATCHREVENUECONSTITUENTRELATIONSHIP 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
set @SETID = 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,
ISSPOUSE,
STARTDATE,
RELATIONSHIPSETID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@RELATIONSHIPID,
@ID,
@SPOUSEID,
@SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID,
@ISSPOUSERELATIONSHIP,
@SPOUSE_STARTDATE,
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @SPOUSEID, @SPOUSE_RELATIONSHIPTYPECODEID, @SPOUSE_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @ID, @SPOUSEID,
@SPOUSE_STARTDATE, NULL, @PRIMARYRELATIONSHIPEXISTS, @PRIMARYMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALRELATIONSHIPEXISTS,
@RECIPROCALMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;
if @ISSPOUSERELATIONSHIP = 1
exec dbo.USP_MARRIAGEOPTIONS_APPLYRULES @ID, @SPOUSEID, @CHANGEAGENTID, @CURRENTDATE;
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
declare @EXISTINGHOUSEHOLDID uniqueidentifier
-- check to see if the user's spouse already belongs to a household
if @SPOUSEID is not null
begin
declare @p2 bit
set @p2=1
set @EXISTINGHOUSEHOLDID=NULL
exec dbo.USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALSPOUSEHOUSEHOLD @ID=@SPOUSEID,@DATALOADED=@p2 output,@HOUSEHOLDID=@EXISTINGHOUSEHOLDID output
if @EXISTINGHOUSEHOLDID is not null
set @HOUSEHOLDID = @EXISTINGHOUSEHOLDID
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],
[ISCONSTITUENT],
[ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED]
)
values
(
@HOUSEHOLDID,
1, -- ISGROUP
@HOUSEHOLD_NAME,
1, -- ISCONSTITUENT
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
);
exec dbo.USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS
@BATCHREVENUECONSTITUENTID = @BATCHHOUSEHOLDID,
@CONSTITUENTID = @HOUSEHOLDID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output,
@UPDATEDAPPLICATIONRECOGNITIONS = @UPDATEDAPPLICATIONRECOGNITIONS output;
exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHREVENUECONSTITUENTID = @BATCHHOUSEHOLDID,
@CONSTITUENTID = @HOUSEHOLDID,
@CHANGEAGENTID = @CHANGEAGENTID,
@BATCHREVENUECONSTITUENTACCOUNTID = @BATCHREVENUECONSTITUENTACCOUNTID,
@CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output;
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*/
/*Adding infosource code*/
if @INFOSOURCECODEID is not null
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTORIGIN @HOUSEHOLDID, @CHANGEAGENTID, @INFOSOURCECODEID, null;
/*End infosource code*/
end
else
begin
exec dbo.USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS
@BATCHREVENUECONSTITUENTID = @BATCHHOUSEHOLDID,
@CONSTITUENTID = @HOUSEHOLDID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output,
@UPDATEDAPPLICATIONRECOGNITIONS = @UPDATEDAPPLICATIONRECOGNITIONS output;
end
exec dbo.USP_GROUPMEMBERADD null, @CHANGEAGENTID, @HOUSEHOLDID, @ID
if @HOUSEHOLDCOPYPRIMARYCONTACTINFO = 1
exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @ID, @HOUSEHOLDID
if @ADDSPOUSETOHOUSEHOLD = 1 and @EXISTINGHOUSEHOLDID is null
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 @ORGANIZATION_PRIMARYRELATIONSHIPEXISTS = 0
begin
set @ORGANIZATION_PRIMARYMATCHFACTOR = 0;
set @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID = null;
end
if @ORGANIZATION_RECIPROCALRELATIONSHIPEXISTS = 0
begin
set @ORGANIZATION_RECIPROCALMATCHFACTOR = 0;
set @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID = null;
end
--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],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ORGANIZATIONID,
-1, -- ISORGANIZATION
@ORGANIZATION_KEYNAME,
@ORGANIZATION_KEYNAMEPREFIX,
1, -- ISCONSTITUENT
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS
@BATCHREVENUECONSTITUENTID = @BATCHORGANIZATIONID,
@CONSTITUENTID = @ORGANIZATIONID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output,
@UPDATEDAPPLICATIONRECOGNITIONS = @UPDATEDAPPLICATIONRECOGNITIONS output;
exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
@BATCHREVENUECONSTITUENTID = @BATCHORGANIZATIONID,
@CONSTITUENTID = @ORGANIZATIONID,
@CHANGEAGENTID = @CHANGEAGENTID,
@BATCHREVENUECONSTITUENTACCOUNTID = @BATCHREVENUECONSTITUENTACCOUNTID,
@CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output;
/*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_REVENUEBATCH_CONSTITUENT_UPDATEIDS
@BATCHREVENUECONSTITUENTID = @BATCHORGANIZATIONID,
@CONSTITUENTID = @ORGANIZATIONID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTRECOGNITIONS = @UPDATEDRECOGNITIONS,
@UPDATEDRECOGNITIONS = @UPDATEDRECOGNITIONS output,
@UPDATEDAPPLICATIONRECOGNITIONS = @UPDATEDAPPLICATIONRECOGNITIONS output;
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;
set @SETID = 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,
'',
1,
@ISMATCHINGGIFTRELATIONSHIP,
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @ORGANIZATIONID, @ORGANIZATION_RELATIONSHIPTYPECODEID, @ORGANIZATION_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @ID, @ORGANIZATIONID,
@ORGANIZATION_STARTDATE, NULL, @ORGANIZATION_PRIMARYRELATIONSHIPEXISTS, @ORGANIZATION_PRIMARYMATCHFACTOR,
@ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID, @ORGANIZATION_RECIPROCALRELATIONSHIPEXISTS,
@ORGANIZATION_RECIPROCALMATCHFACTOR, @ORGANIZATION_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
end;
set @IDMAPPING =
(
select REVENUEBATCHCONSTITUENTID, CONSTITUENTID
from @IDMAPPINGTBL
for xml raw('ITEM'), type, elements, root('IDMAPPING'), BINARY BASE64
)
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end;