USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENT_4
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @LASTNAME | nvarchar(100) | IN | |
| @ORGANIZATIONNAME | nvarchar(100) | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @NICKNAME | nvarchar(50) | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @GENDERCODE | tinyint | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @ADDRESS_DONOTMAIL | bit | IN | |
| @ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | |
| @ADDRESS_STATEID | uniqueidentifier | IN | |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | |
| @ADDRESS_CITY | nvarchar(50) | IN | |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | |
| @ADDRESS_UPDATEMATCHINGHOUSEHOLDADDRESSES | bit | IN | |
| @ADDRESS_OMITFROMVALIDATION | bit | IN | |
| @ADDRESS_CART | nvarchar(10) | IN | |
| @ADDRESS_DPC | nvarchar(8) | IN | |
| @ADDRESS_LOT | nvarchar(5) | IN | |
| @ADDRESS_COUNTYCODEID | uniqueidentifier | IN | |
| @ADDRESS_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @ADDRESS_LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @ADDRESS_VALIDATIONMESSAGE | nvarchar(200) | IN | |
| @ADDRESS_CERTIFICATIONDATA | int | IN | |
| @PHONE_PHONETYPECODEID | uniqueidentifier | IN | |
| @PHONE_NUMBER | nvarchar(100) | IN | |
| @EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @MARITALSTATUSCODEID | uniqueidentifier | IN | |
| @ORGANIZATION_ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @ORGANIZATION_DONOTMAIL | bit | IN | |
| @ORGANIZATION_DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @ORGANIZATION_COUNTRYID | uniqueidentifier | IN | |
| @ORGANIZATION_STATEID | uniqueidentifier | IN | |
| @ORGANIZATION_ADDRESSBLOCK | nvarchar(150) | IN | |
| @ORGANIZATION_CITY | nvarchar(50) | IN | |
| @ORGANIZATION_POSTCODE | nvarchar(12) | IN | |
| @ORGANIZATION_OMITFROMVALIDATION | bit | IN | |
| @ORGANIZATION_CART | nvarchar(10) | IN | |
| @ORGANIZATION_DPC | nvarchar(8) | IN | |
| @ORGANIZATION_LOT | nvarchar(5) | IN | |
| @ORGANIZATION_COUNTYCODEID | uniqueidentifier | IN | |
| @ORGANIZATION_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @ORGANIZATION_LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @ORGANIZATION_VALIDATIONMESSAGE | nvarchar(200) | IN | |
| @ORGANIZATION_CERTIFICATIONDATA | int | IN | |
| @ORGANIZATION_PHONETYPECODEID | uniqueidentifier | IN | |
| @ORGANIZATION_NUMBER | nvarchar(100) | IN | |
| @ORGANIZATION_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @ORGANIZATION_EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @WEBADDRESS | UDT_WEBADDRESS | IN | |
| @INDUSTRYCODEID | uniqueidentifier | IN | |
| @NUMEMPLOYEES | int | IN | |
| @NUMSUBSIDIARIES | int | IN | |
| @PARENTCORPID | uniqueidentifier | IN | |
| @BATCHSPOUSEID | uniqueidentifier | IN | |
| @BATCHSPOUSERELATIONID | uniqueidentifier | IN | |
| @SPOUSEID | uniqueidentifier | IN | |
| @EXISTINGSPOUSE | bit | IN | |
| @SPOUSE_LASTNAME | nvarchar(100) | IN | |
| @SPOUSE_FIRSTNAME | nvarchar(50) | IN | |
| @SPOUSE_MIDDLENAME | nvarchar(50) | IN | |
| @SPOUSE_MAIDENNAME | nvarchar(100) | IN | |
| @SPOUSE_NICKNAME | nvarchar(50) | IN | |
| @SPOUSE_TITLECODEID | uniqueidentifier | IN | |
| @SPOUSE_SUFFIXCODEID | uniqueidentifier | IN | |
| @SPOUSE_GENDERCODE | tinyint | IN | |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | IN | |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @COPYPRIMARYINFORMATION | bit | IN | |
| @SPOUSE_STARTDATE | datetime | IN | |
| @ISSPOUSERELATIONSHIP | bit | IN | |
| @BATCHBUSINESSID | uniqueidentifier | IN | |
| @BATCHBUSINESSRELATIONID | uniqueidentifier | IN | |
| @BUSINESSID | uniqueidentifier | IN | |
| @EXISTINGBUSINESS | bit | IN | |
| @BUSINESS_NAME | nvarchar(100) | IN | |
| @BUSINESS_ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_COUNTRYID | uniqueidentifier | IN | |
| @BUSINESS_STATEID | uniqueidentifier | IN | |
| @BUSINESS_ADDRESSBLOCK | nvarchar(150) | IN | |
| @BUSINESS_CITY | nvarchar(50) | IN | |
| @BUSINESS_POSTCODE | nvarchar(12) | IN | |
| @BUSINESS_DONOTMAIL | bit | IN | |
| @BUSINESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @BUSINESS_OMITFROMVALIDATION | bit | IN | |
| @BUSINESS_CART | nvarchar(10) | IN | |
| @BUSINESS_DPC | nvarchar(8) | IN | |
| @BUSINESS_LOT | nvarchar(5) | IN | |
| @BUSINESS_COUNTYCODEID | uniqueidentifier | IN | |
| @BUSINESS_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @BUSINESS_LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @BUSINESS_VALIDATIONMESSAGE | nvarchar(200) | IN | |
| @BUSINESS_CERTIFICATIONDATA | int | IN | |
| @BUSINESS_PHONETYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_NUMBER | nvarchar(100) | IN | |
| @BUSINESS_RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_STARTDATE | datetime | IN | |
| @ISCONTACT | bit | IN | |
| @ISPRIMARYCONTACT | bit | IN | |
| @CONTACTTYPECODEID | uniqueidentifier | IN | |
| @POSITION | nvarchar(100) | IN | |
| @ISMATCHINGGIFTRELATIONSHIP | bit | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @GROUP_NAME | nvarchar(100) | IN | |
| @GROUP_DESCRIPTION | nvarchar(300) | IN | |
| @GROUP_GIVESANONYMOUSLY | bit | IN | |
| @GROUP_ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @GROUP_COUNTRYID | uniqueidentifier | IN | |
| @GROUP_ADDRESSBLOCK | nvarchar(150) | IN | |
| @GROUP_CITY | nvarchar(50) | IN | |
| @GROUP_STATEID | uniqueidentifier | IN | |
| @GROUP_OMITFROMVALIDATION | bit | IN | |
| @GROUP_CART | nvarchar(10) | IN | |
| @GROUP_DPC | nvarchar(8) | IN | |
| @GROUP_LOT | nvarchar(5) | IN | |
| @GROUP_COUNTYCODEID | uniqueidentifier | IN | |
| @GROUP_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @GROUP_LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @GROUP_VALIDATIONMESSAGE | nvarchar(200) | IN | |
| @GROUP_CERTIFICATIONDATA | int | IN | |
| @GROUP_POSTCODE | nvarchar(12) | IN | |
| @GROUP_PHONETYPECODEID | uniqueidentifier | IN | |
| @GROUP_NUMBER | nvarchar(100) | IN | |
| @GROUP_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @GROUP_EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @GROUP_WEBADDRESS | UDT_WEBADDRESS | IN | |
| @GROUP_DONOTMAIL | bit | IN | |
| @GROUP_DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @GROUP_UPDATEMATCHINGHOUSEHOLDADDRESSES | bit | IN | |
| @GROUP_GROUPTYPEID | uniqueidentifier | IN | |
| @GROUP_PRIMARYCONTACTID | uniqueidentifier | IN | |
| @GROUP_MEMBERS | xml | IN | |
| @GROUP_NEWMEMBERSEARCHID | uniqueidentifier | IN | |
| @GROUP_STARTDATE | datetime | IN | |
| @HOUSEHOLD_PRIMARYCONTACTID | uniqueidentifier | IN | |
| @HOUSEHOLD_PRIMARYCONTACT_KEYNAME | nvarchar(100) | IN | |
| @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME | nvarchar(50) | IN | |
| @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME | nvarchar(50) | IN | |
| @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID | uniqueidentifier | IN | |
| @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID | uniqueidentifier | IN | |
| @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT | bit | IN | |
| @HOUSEHOLD_SECONDMEMBERID | uniqueidentifier | IN | |
| @HOUSEHOLD_SECONDMEMBER_KEYNAME | nvarchar(100) | IN | |
| @HOUSEHOLD_SECONDMEMBER_FIRSTNAME | nvarchar(50) | IN | |
| @HOUSEHOLD_SECONDMEMBER_MIDDLENAME | nvarchar(50) | IN | |
| @HOUSEHOLD_SECONDMEMBER_TITLECODEID | uniqueidentifier | IN | |
| @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID | uniqueidentifier | IN | |
| @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT | bit | IN | |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID | uniqueidentifier | IN | |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE | datetime | IN | |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE | bit | IN | |
| @HOUSEHOLDCOPYPRIMARYCONTACTINFO | bit | IN | |
| @JOBCATEGORYCODEID | uniqueidentifier | IN | |
| @CAREERLEVELCODEID | uniqueidentifier | IN | |
| @GROUP_DONOTCALL | bit | IN | |
| @GROUP_DONOTEMAIL | bit | IN | |
| @GENDERCODEID | uniqueidentifier | IN | |
| @SPOUSE_GENDERCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENT_4
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@LASTNAME nvarchar(100),
@ORGANIZATIONNAME nvarchar(100),
@FIRSTNAME nvarchar(50),
@MIDDLENAME nvarchar(50),
@MAIDENNAME nvarchar(100),
@NICKNAME nvarchar(50),
@TITLECODEID uniqueidentifier,
@SUFFIXCODEID uniqueidentifier,
@GENDERCODE tinyint,
@BIRTHDATE dbo.UDT_FUZZYDATE,
@ADDRESS_ADDRESSTYPECODEID uniqueidentifier,
@ADDRESS_DONOTMAIL bit,
@ADDRESS_DONOTMAILREASONCODEID uniqueidentifier,
@ADDRESS_COUNTRYID uniqueidentifier,
@ADDRESS_STATEID uniqueidentifier,
@ADDRESS_ADDRESSBLOCK nvarchar(150),
@ADDRESS_CITY nvarchar(50),
@ADDRESS_POSTCODE nvarchar(12),
@ADDRESS_UPDATEMATCHINGHOUSEHOLDADDRESSES bit,
-- Address Validation
@ADDRESS_OMITFROMVALIDATION bit,
@ADDRESS_CART nvarchar(10),
@ADDRESS_DPC nvarchar(8),
@ADDRESS_LOT nvarchar(5),
@ADDRESS_COUNTYCODEID uniqueidentifier,
@ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@ADDRESS_LASTVALIDATIONATTEMPTDATE datetime,
@ADDRESS_VALIDATIONMESSAGE nvarchar(200),
@ADDRESS_CERTIFICATIONDATA integer,
@PHONE_PHONETYPECODEID uniqueidentifier,
@PHONE_NUMBER nvarchar(100),
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,
@MARITALSTATUSCODEID uniqueidentifier,
@ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier,
@ORGANIZATION_DONOTMAIL bit,
@ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier,
@ORGANIZATION_COUNTRYID uniqueidentifier,
@ORGANIZATION_STATEID uniqueidentifier,
@ORGANIZATION_ADDRESSBLOCK nvarchar(150),
@ORGANIZATION_CITY nvarchar(50),
@ORGANIZATION_POSTCODE nvarchar(12),
-- Address Validation
@ORGANIZATION_OMITFROMVALIDATION bit,
@ORGANIZATION_CART nvarchar(10),
@ORGANIZATION_DPC nvarchar(8),
@ORGANIZATION_LOT nvarchar(5),
@ORGANIZATION_COUNTYCODEID uniqueidentifier,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime,
@ORGANIZATION_VALIDATIONMESSAGE nvarchar(200),
@ORGANIZATION_CERTIFICATIONDATA integer,
@ORGANIZATION_PHONETYPECODEID uniqueidentifier,
@ORGANIZATION_NUMBER nvarchar(100),
@ORGANIZATION_EMAILADDRESSTYPECODEID uniqueidentifier,
@ORGANIZATION_EMAILADDRESS dbo.UDT_EMAILADDRESS,
@WEBADDRESS dbo.UDT_WEBADDRESS,
@INDUSTRYCODEID uniqueidentifier,
@NUMEMPLOYEES int,
@NUMSUBSIDIARIES int,
@PARENTCORPID uniqueidentifier,
--Individual's relationship Variables
@BATCHSPOUSEID uniqueidentifier,
@BATCHSPOUSERELATIONID uniqueidentifier,
@SPOUSEID uniqueidentifier,
@EXISTINGSPOUSE bit,
@SPOUSE_LASTNAME nvarchar(100),
@SPOUSE_FIRSTNAME nvarchar(50),
@SPOUSE_MIDDLENAME nvarchar(50),
@SPOUSE_MAIDENNAME nvarchar(100),
@SPOUSE_NICKNAME nvarchar(50),
@SPOUSE_TITLECODEID uniqueidentifier,
@SPOUSE_SUFFIXCODEID uniqueidentifier,
@SPOUSE_GENDERCODE tinyint,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier,
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
@COPYPRIMARYINFORMATION bit,
@SPOUSE_STARTDATE datetime,
@ISSPOUSERELATIONSHIP bit,
--Organization variables
@BATCHBUSINESSID uniqueidentifier,
@BATCHBUSINESSRELATIONID uniqueidentifier,
@BUSINESSID uniqueidentifier,
@EXISTINGBUSINESS bit,
@BUSINESS_NAME nvarchar(100),
@BUSINESS_ADDRESSTYPECODEID uniqueidentifier,
@BUSINESS_COUNTRYID uniqueidentifier,
@BUSINESS_STATEID uniqueidentifier,
@BUSINESS_ADDRESSBLOCK nvarchar(150),
@BUSINESS_CITY nvarchar(50),
@BUSINESS_POSTCODE nvarchar(12),
@BUSINESS_DONOTMAIL bit,
@BUSINESS_DONOTMAILREASONCODEID uniqueidentifier,
-- Address Validation
@BUSINESS_OMITFROMVALIDATION bit,
@BUSINESS_CART nvarchar(10),
@BUSINESS_DPC nvarchar(8),
@BUSINESS_LOT nvarchar(5),
@BUSINESS_COUNTYCODEID uniqueidentifier,
@BUSINESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@BUSINESS_LASTVALIDATIONATTEMPTDATE datetime,
@BUSINESS_VALIDATIONMESSAGE nvarchar(200),
@BUSINESS_CERTIFICATIONDATA integer,
@BUSINESS_PHONETYPECODEID uniqueidentifier,
@BUSINESS_NUMBER nvarchar(100),
@BUSINESS_RECIPROCALTYPECODEID uniqueidentifier,
@BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier,
@BUSINESS_STARTDATE datetime,
@ISCONTACT bit,
@ISPRIMARYCONTACT bit,
@CONTACTTYPECODEID uniqueidentifier,
@POSITION nvarchar(100),
@ISMATCHINGGIFTRELATIONSHIP bit,
@CURRENTAPPUSERID uniqueidentifier,
-- Group/Household variables
@GROUP_NAME nvarchar(100),
@GROUP_DESCRIPTION nvarchar(300),
@GROUP_GIVESANONYMOUSLY bit,
@GROUP_ADDRESSTYPECODEID uniqueidentifier,
@GROUP_COUNTRYID uniqueidentifier,
@GROUP_ADDRESSBLOCK nvarchar(150),
@GROUP_CITY nvarchar(50),
@GROUP_STATEID uniqueidentifier,
@GROUP_OMITFROMVALIDATION bit,
@GROUP_CART nvarchar(10),
@GROUP_DPC nvarchar(8),
@GROUP_LOT nvarchar(5),
@GROUP_COUNTYCODEID uniqueidentifier,
@GROUP_CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@GROUP_LASTVALIDATIONATTEMPTDATE datetime,
@GROUP_VALIDATIONMESSAGE nvarchar(200),
@GROUP_CERTIFICATIONDATA integer,
@GROUP_POSTCODE nvarchar(12),
@GROUP_PHONETYPECODEID uniqueidentifier,
@GROUP_NUMBER nvarchar(100),
@GROUP_EMAILADDRESSTYPECODEID uniqueidentifier,
@GROUP_EMAILADDRESS dbo.UDT_EMAILADDRESS,
@GROUP_WEBADDRESS dbo.UDT_WEBADDRESS,
@GROUP_DONOTMAIL bit,
@GROUP_DONOTMAILREASONCODEID uniqueidentifier,
@GROUP_UPDATEMATCHINGHOUSEHOLDADDRESSES bit,
-- Group variables
@GROUP_GROUPTYPEID uniqueidentifier,
@GROUP_PRIMARYCONTACTID uniqueidentifier,
@GROUP_MEMBERS xml,
@GROUP_NEWMEMBERSEARCHID uniqueidentifier, -- Not used in save operation, only exists to setup new member lookup control.
@GROUP_STARTDATE datetime,
-- Household Primary Member variables
@HOUSEHOLD_PRIMARYCONTACTID uniqueidentifier,
@HOUSEHOLD_PRIMARYCONTACT_KEYNAME nvarchar(100),
@HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME nvarchar(50),
@HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME nvarchar(50),
@HOUSEHOLD_PRIMARYCONTACT_TITLECODEID uniqueidentifier,
@HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier,
@HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit,
-- Household Second Member variables
@HOUSEHOLD_SECONDMEMBERID uniqueidentifier,
@HOUSEHOLD_SECONDMEMBER_KEYNAME nvarchar(100),
@HOUSEHOLD_SECONDMEMBER_FIRSTNAME nvarchar(50),
@HOUSEHOLD_SECONDMEMBER_MIDDLENAME nvarchar(50),
@HOUSEHOLD_SECONDMEMBER_TITLECODEID uniqueidentifier,
@HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID uniqueidentifier,
@HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT bit,
-- Household Second Member Relationship variables
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier,
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier,
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE datetime,
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit,
-- Individual's household fields
@HOUSEHOLDCOPYPRIMARYCONTACTINFO bit,
@JOBCATEGORYCODEID uniqueidentifier,
@CAREERLEVELCODEID uniqueidentifier,
@GROUP_DONOTCALL bit,
@GROUP_DONOTEMAIL bit,
@GENDERCODEID uniqueidentifier,
@SPOUSE_GENDERCODEID uniqueidentifier
)
as
begin
set nocount on;
declare @CURRENTDATE datetime;
declare @KEYNAME nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(50);
if @ID is null
set @ID = newid();
if @ADDRESS_DONOTMAIL = 0
set @ADDRESS_DONOTMAILREASONCODEID = null
if @ORGANIZATION_DONOTMAIL = 0
set @ORGANIZATION_DONOTMAILREASONCODEID = null
if @BUSINESS_DONOTMAIL = 0
set @BUSINESS_DONOTMAILREASONCODEID = null
if @GROUP_DONOTMAIL = 0
set @GROUP_DONOTMAILREASONCODEID = null
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
begin try
declare @contextCache varbinary(128);
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
declare @ISEXISTINGCONSTITUENT bit;
declare @ISORGANIZATION bit;
declare @ISGROUP bit;
declare @ISHOUSEHOLD bit;
set @ISEXISTINGCONSTITUENT = 0;
set @ISORGANIZATION = 0;
set @ISGROUP = 0;
set @ISHOUSEHOLD = 0;
select @ISEXISTINGCONSTITUENT = 1,
@ISORGANIZATION = C.ISORGANIZATION,
@ISGROUP = C.ISGROUP,
@ISHOUSEHOLD = case
when GD.GROUPTYPECODE = 0
then 1
else 0
end
from dbo.CONSTITUENT C
left join dbo.GROUPDATA GD
on C.ID = GD.ID
where C.ID = @ID;
if @ISEXISTINGCONSTITUENT = 1
begin
-- Check constituent security. The check is done manually since it only applies to existing constituents.
if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 0)
and (dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, '9AB0F5C5-0FF0-4D5E-968B-8FFB340FBEEA', @ID) = 0)
raiserror (
'BBERR_DB_RECORDSECURITY_PERMISSION_DENIED',
13,
1
);
declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
declare @NULLDATE date = '9999-01-01';
-- Check for duplicate contact information
-- For some reason the values are passed in as separate parameters depending on the constituent type
-- normalize them first
declare @DUPE_ADDRESSBLOCK nvarchar(150);
declare @DUPE_ADDRESSTYPECODEID uniqueidentifier;
declare @DUPE_STATEID uniqueidentifier;
declare @DUPE_COUNTRYID uniqueidentifier;
declare @DUPE_POSTCODE nvarchar(12);
declare @DUPE_PHONENUMBER nvarchar(100);
declare @DUPE_PHONETYPECODEID uniqueidentifier;
declare @DUPE_EMAILADDRESS dbo.UDT_EMAILADDRESS;
declare @DUPE_EMAILADDRESSTYPECODEID uniqueidentifier;
declare @DUPE_CONSTITUENTTYPE_FORERROR nvarchar(100);
if @ISORGANIZATION <> 0
begin
set @DUPE_ADDRESSBLOCK = @ORGANIZATION_ADDRESSBLOCK;
set @DUPE_ADDRESSTYPECODEID = @ORGANIZATION_ADDRESSTYPECODEID;
set @DUPE_STATEID = @ORGANIZATION_STATEID;
set @DUPE_COUNTRYID = @ORGANIZATION_COUNTRYID;
set @DUPE_POSTCODE = @ORGANIZATION_POSTCODE;
set @DUPE_PHONENUMBER = @ORGANIZATION_NUMBER;
set @DUPE_PHONETYPECODEID = @ORGANIZATION_PHONETYPECODEID;
set @DUPE_EMAILADDRESS = @ORGANIZATION_EMAILADDRESS;
set @DUPE_EMAILADDRESSTYPECODEID = @ORGANIZATION_EMAILADDRESSTYPECODEID;
set @DUPE_CONSTITUENTTYPE_FORERROR = '_NONGROUP';
end
else if @ISGROUP <> 0
begin
set @DUPE_ADDRESSBLOCK = @GROUP_ADDRESSBLOCK;
set @DUPE_ADDRESSTYPECODEID = @GROUP_ADDRESSTYPECODEID;
set @DUPE_STATEID = @GROUP_STATEID;
set @DUPE_COUNTRYID = @GROUP_COUNTRYID;
set @DUPE_POSTCODE = @GROUP_POSTCODE;
set @DUPE_PHONENUMBER = @GROUP_NUMBER;
set @DUPE_PHONETYPECODEID = @GROUP_PHONETYPECODEID;
set @DUPE_EMAILADDRESS = @GROUP_EMAILADDRESS;
set @DUPE_EMAILADDRESSTYPECODEID = @GROUP_EMAILADDRESSTYPECODEID;
set @DUPE_CONSTITUENTTYPE_FORERROR = '_GROUP';
end
else
begin
set @DUPE_ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK;
set @DUPE_ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID;
set @DUPE_STATEID = @ADDRESS_STATEID;
set @DUPE_COUNTRYID = @ADDRESS_COUNTRYID;
set @DUPE_POSTCODE = @ADDRESS_POSTCODE;
set @DUPE_PHONENUMBER = @PHONE_NUMBER;
set @DUPE_PHONETYPECODEID = @PHONE_PHONETYPECODEID;
set @DUPE_EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS;
set @DUPE_EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID;
set @DUPE_CONSTITUENTTYPE_FORERROR = '_NONGROUP';
end
if exists(
select top 1 1
from
dbo.ADDRESS
where
ADDRESS.HISTORICALENDDATE is null
and coalesce(ADDRESS.ADDRESSBLOCK, '') = coalesce(@DUPE_ADDRESSBLOCK, '')
and coalesce(ADDRESS.ADDRESSTYPECODEID, @EMPTYGUID) = coalesce(@DUPE_ADDRESSTYPECODEID, @EMPTYGUID)
and coalesce(ADDRESS.STATEID, @EMPTYGUID) = coalesce(@DUPE_STATEID, @EMPTYGUID)
and coalesce(ADDRESS.COUNTRYID, @EMPTYGUID) = coalesce(@DUPE_COUNTRYID, @EMPTYGUID)
and coalesce(ADDRESS.POSTCODE, '') = coalesce(@DUPE_POSTCODE, '')
and ADDRESS.CONSTITUENTID = @ID
and ADDRESS.ISPRIMARY = 0
)
begin
declare @ADDRESSERROR nvarchar(100) = 'BBERR_ADDRESS_DUPLICATENOTALLOWED' + @DUPE_CONSTITUENTTYPE_FORERROR
raiserror(@ADDRESSERROR, 13, 1);
end
-- the country of the phone we are editing/adding is either
-- editing: the country of the current primary phone
-- adding: null
declare @NUMBERNOFORMAT nvarchar(100) = coalesce(dbo.UFN_PHONE_REMOVEFORMATTING(@DUPE_PHONENUMBER), '');
declare @PRIMARYPHONECOUNTRYID uniqueidentifier = null;
select
@PRIMARYPHONECOUNTRYID = PHONE.COUNTRYID
from
dbo.PHONE
where
PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1
if exists(
select top 1 1
from
dbo.PHONE
where
PHONE.ENDDATE is null
and coalesce(PHONE.NUMBERNOFORMAT, '') = @NUMBERNOFORMAT
and coalesce(PHONE.PHONETYPECODEID, @EMPTYGUID) = coalesce(@DUPE_PHONETYPECODEID, @EMPTYGUID)
and coalesce(PHONE.COUNTRYID, @EMPTYGUID) = coalesce(@PRIMARYPHONECOUNTRYID, @EMPTYGUID)
and PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 0
)
begin
declare @PHONEERROR nvarchar(100) = 'BBERR_PHONENUMBER_DUPLICATENOTALLOWED' + @DUPE_CONSTITUENTTYPE_FORERROR;
raiserror(@PHONEERROR, 13, 1);
end
if exists(
select top 1 1
from
dbo.EMAILADDRESS
where
EMAILADDRESS.ENDDATE is null
and coalesce(EMAILADDRESS.EMAILADDRESS, '') = coalesce(@DUPE_EMAILADDRESS, '')
and coalesce(EMAILADDRESS.EMAILADDRESSTYPECODEID, @EMPTYGUID) = coalesce(@DUPE_EMAILADDRESSTYPECODEID, @EMPTYGUID)
and EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 0
)
begin
declare @EMAILERROR nvarchar(100) = 'BBERR_EMAILADDRESS_DUPLICATENOTALLOWED' + @DUPE_CONSTITUENTTYPE_FORERROR
raiserror(@EMAILERROR, 13, 1);
end
if @ISORGANIZATION != 0
begin
exec dbo.USP_PARSE_ORGANIZATION_NAME @ORGANIZATIONNAME,
@KEYNAME output,
@KEYNAMEPREFIX output;
update dbo.CONSTITUENT
set KEYNAME = @KEYNAME,
KEYNAMEPREFIX = @KEYNAMEPREFIX,
WEBADDRESS = @WEBADDRESS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENT.ID = @ID;
if @NUMEMPLOYEES is null
set @NUMEMPLOYEES = 0;
if @NUMSUBSIDIARIES is null
set @NUMSUBSIDIARIES = 0;
declare @OLDPARENTORG uniqueidentifier
set @OLDPARENTORG = (
select PARENTCORPID
from dbo.ORGANIZATIONDATA
where ORGANIZATIONDATA.ID = @ID
)
update dbo.ORGANIZATIONDATA
set INDUSTRYCODEID = @INDUSTRYCODEID,
NUMEMPLOYEES = @NUMEMPLOYEES,
NUMSUBSIDIARIES = @NUMSUBSIDIARIES,
PARENTCORPID = @PARENTCORPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ORGANIZATIONDATA.ID = @ID;
exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @ID,
@PARENTCORPID,
@CURRENTDATE,
@CHANGEAGENTID,
@CURRENTDATE,
1,
@OLDPARENTORG
if @@ROWCOUNT = 0
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,
DATECHANGED,
DATEADDED
)
values (
@ID,
@INDUSTRYCODEID,
@NUMEMPLOYEES,
@NUMSUBSIDIARIES,
@PARENTCORPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
--Bug 163945 ASA: Workaround for checking @ORGANIZATION_STATEID before sending it to the ADDRESS table.
--Probably need to change the @ORGANIZATION_STATEID to uniqueidentifier to skip this check. In regression
--now, so changing to uniqueidentifier might be invasive.
declare @ORGANIZATIONSTATEID uniqueidentifier = null;
if @ORGANIZATION_STATEID is not null
if len(@ORGANIZATION_STATEID) <> 0
and len(@ORGANIZATION_STATEID) = 36
set @ORGANIZATIONSTATEID = convert(uniqueidentifier, @ORGANIZATION_STATEID)
update dbo.ADDRESS
set ADDRESSTYPECODEID = @ORGANIZATION_ADDRESSTYPECODEID,
DONOTMAIL = @ORGANIZATION_DONOTMAIL,
DONOTMAILREASONCODEID = @ORGANIZATION_DONOTMAILREASONCODEID,
COUNTRYID = @ORGANIZATION_COUNTRYID,
STATEID = @ORGANIZATIONSTATEID,
ADDRESSBLOCK = @ORGANIZATION_ADDRESSBLOCK,
CITY = @ORGANIZATION_CITY,
POSTCODE = @ORGANIZATION_POSTCODE,
CART = @ORGANIZATION_CART,
DPC = @ORGANIZATION_DPC,
LOT = @ORGANIZATION_LOT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ADDRESS.CONSTITUENTID = @ID
and ISPRIMARY = 1
declare @ORGANIZATION_ADDRESSID uniqueidentifier;
if @@ROWCOUNT = 0
and (
(@ADDRESS_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
set @ORGANIZATION_ADDRESSID = newID();
insert into dbo.ADDRESS (
ID,
CONSTITUENTID,
ISPRIMARY,
ADDRESSTYPECODEID,
DONOTMAIL,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ORGANIZATION_ADDRESSID,
@ID,
1,
@ORGANIZATION_ADDRESSTYPECODEID,
@ORGANIZATION_DONOTMAIL,
@ORGANIZATION_COUNTRYID,
@ORGANIZATION_STATEID,
@ORGANIZATION_ADDRESSBLOCK,
@ORGANIZATION_CITY,
@ORGANIZATION_POSTCODE,
@ORGANIZATION_CART,
@ORGANIZATION_DPC,
@ORGANIZATION_LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else
begin
select @ORGANIZATION_ADDRESSID = ID
from dbo.ADDRESS
where ADDRESS.CONSTITUENTID = @ID
and ISPRIMARY = 1
end
if @ORGANIZATION_ADDRESSID is not null
begin
update ADDRESSVALIDATIONUPDATE
set ADDRESSVALIDATIONUPDATE.COUNTYCODEID = @ORGANIZATION_COUNTYCODEID,
ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID = @ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE = @ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE = @ORGANIZATION_VALIDATIONMESSAGE,
ADDRESSVALIDATIONUPDATE.CERTIFICATIONDATA = @ORGANIZATION_CERTIFICATIONDATA,
ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION = @ORGANIZATION_OMITFROMVALIDATION,
ADDRESSVALIDATIONUPDATE.CHANGEDBYID = @CHANGEAGENTID,
ADDRESSVALIDATIONUPDATE.DATECHANGED = @CURRENTDATE
from dbo.ADDRESSVALIDATIONUPDATE
inner join dbo.ADDRESS
on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
where ADDRESS.CONSTITUENTID = @ID
and ADDRESS.ISPRIMARY = 1
if @@ROWCOUNT = 0
begin
insert into dbo.ADDRESSVALIDATIONUPDATE (
ID,
OMITFROMVALIDATION,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ORGANIZATION_ADDRESSID,
@ORGANIZATION_OMITFROMVALIDATION,
@ORGANIZATION_COUNTYCODEID,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
@ORGANIZATION_VALIDATIONMESSAGE,
@ORGANIZATION_CERTIFICATIONDATA,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
end
if @ORGANIZATION_PHONETYPECODEID is not null
or (
@ORGANIZATION_NUMBER is not null
and @ORGANIZATION_NUMBER != ''
)
begin
update dbo.PHONE
set PHONETYPECODEID = @ORGANIZATION_PHONETYPECODEID,
NUMBER = @ORGANIZATION_NUMBER,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1;
if @@ROWCOUNT = 0
if @ORGANIZATION_PHONETYPECODEID is not null
or @ORGANIZATION_NUMBER != 0
insert into dbo.PHONE (
CONSTITUENTID,
ISPRIMARY,
PHONETYPECODEID,
NUMBER,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
1,
@ORGANIZATION_PHONETYPECODEID,
@ORGANIZATION_NUMBER,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else if exists (
select ID
from dbo.PHONE
where CONSTITUENTID = @ID
and ISPRIMARY = 1
)
begin
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
delete
from dbo.PHONE
where CONSTITUENTID = @ID
and ISPRIMARY = 1;
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end;
if @ORGANIZATION_EMAILADDRESSTYPECODEID is not null
or (
@ORGANIZATION_EMAILADDRESS is not null
and @ORGANIZATION_EMAILADDRESS != ''
)
begin
update dbo.EMAILADDRESS
set [EMAILADDRESSTYPECODEID] = @ORGANIZATION_EMAILADDRESSTYPECODEID,
[EMAILADDRESS] = @ORGANIZATION_EMAILADDRESS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 1;
if @@ROWCOUNT = 0
insert into dbo.EMAILADDRESS (
CONSTITUENTID,
ISPRIMARY,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
1,
@ORGANIZATION_EMAILADDRESSTYPECODEID,
@ORGANIZATION_EMAILADDRESS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else if exists (
select ID
from dbo.EMAILADDRESS
where CONSTITUENTID = @ID
and ISPRIMARY = 1
)
begin
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
delete
from dbo.EMAILADDRESS
where CONSTITUENTID = @ID
and ISPRIMARY = 1;
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end;
end;
else
begin
if @ISGROUP != 0
begin
update dbo.CONSTITUENT
set KEYNAME = @GROUP_NAME,
GIVESANONYMOUSLY = @GROUP_GIVESANONYMOUSLY,
WEBADDRESS = @GROUP_WEBADDRESS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENT.ID = @ID;
update dbo.GROUPDATA
set DESCRIPTION = @GROUP_DESCRIPTION,
GROUPTYPEID = @GROUP_GROUPTYPEID,
STARTDATE = @GROUP_STARTDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where GROUPDATA.ID = @ID;
declare @GROUPPRIMARYADDRESSID uniqueidentifier
select @GROUPPRIMARYADDRESSID = ID
from dbo.ADDRESS
where ADDRESS.CONSTITUENTID = @ID
and ISPRIMARY = 1
-- Update matching household addresses
if @GROUP_UPDATEMATCHINGHOUSEHOLDADDRESSES = 1
and @GROUPPRIMARYADDRESSID is not null
begin
exec dbo.USP_ADDRESS_UPDATEMATCHINGADDRESSES @ADDRESSID = @GROUPPRIMARYADDRESSID,
@CHANGEAGENTID = @CHANGEAGENTID,
@ADDRESSTYPECODEID = @GROUP_ADDRESSTYPECODEID,
@DONOTMAIL = @GROUP_DONOTMAIL,
@COUNTRYID = @GROUP_COUNTRYID,
@STATEID = @GROUP_STATEID,
@ADDRESSBLOCK = @GROUP_ADDRESSBLOCK,
@CITY = @GROUP_CITY,
@POSTCODE = @GROUP_POSTCODE,
@CART = @GROUP_CART,
@DPC = @GROUP_DPC,
@LOT = @GROUP_LOT,
@OMITFROMVALIDATION = @GROUP_OMITFROMVALIDATION,
@COUNTYCODEID = @GROUP_COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID = @GROUP_CONGRESSIONALDISTRICTCODEID,
@LASTVALIDATIONATTEMPTDATE = @GROUP_LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE = @GROUP_VALIDATIONMESSAGE,
@CERTIFICATIONDATA = @GROUP_CERTIFICATIONDATA,
@DONOTMAILREASONCODEID = @GROUP_DONOTMAILREASONCODEID,
@UPDATEFROMREVENUEBATCH = 1
end
update dbo.ADDRESS
set ADDRESSTYPECODEID = @GROUP_ADDRESSTYPECODEID,
COUNTRYID = @GROUP_COUNTRYID,
STATEID = @GROUP_STATEID,
ADDRESSBLOCK = @GROUP_ADDRESSBLOCK,
CITY = @GROUP_CITY,
POSTCODE = @GROUP_POSTCODE,
CART = @GROUP_CART,
DPC = @GROUP_DPC,
LOT = @GROUP_LOT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DONOTMAIL = @GROUP_DONOTMAIL,
DONOTMAILREASONCODEID = @GROUP_DONOTMAILREASONCODEID
where ID = @GROUPPRIMARYADDRESSID
declare @GROUP_ADDRESSID uniqueidentifier;
if @@ROWCOUNT = 0
and (
(@GROUP_STATEID is not null)
or (coalesce(@GROUP_ADDRESSBLOCK, '') <> '')
or (coalesce(@GROUP_CITY, '') <> '')
or (coalesce(@GROUP_POSTCODE, '') <> '')
or (@GROUP_ADDRESSTYPECODEID is not null)
or (@GROUP_COUNTRYID is not null)
or (@GROUP_DONOTMAIL = 1)
or (@GROUP_DONOTMAILREASONCODEID is not null)
)
begin
set @GROUP_ADDRESSID = newID();
insert into dbo.ADDRESS (
ID,
CONSTITUENTID,
ISPRIMARY,
ADDRESSTYPECODEID,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@GROUP_ADDRESSID,
@ID,
1,
@GROUP_ADDRESSTYPECODEID,
@GROUP_COUNTRYID,
@GROUP_STATEID,
@GROUP_ADDRESSBLOCK,
@GROUP_CITY,
@GROUP_POSTCODE,
@GROUP_CART,
@GROUP_DPC,
@GROUP_LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else
begin
select @GROUP_ADDRESSID = ID
from dbo.ADDRESS
where ADDRESS.CONSTITUENTID = @ID
and ISPRIMARY = 1
end
if @GROUP_ADDRESSID is not null
begin
update ADDRESSVALIDATIONUPDATE
set ADDRESSVALIDATIONUPDATE.COUNTYCODEID = @GROUP_COUNTYCODEID,
ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID = @GROUP_CONGRESSIONALDISTRICTCODEID,
ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE = @GROUP_LASTVALIDATIONATTEMPTDATE,
ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE = @GROUP_VALIDATIONMESSAGE,
ADDRESSVALIDATIONUPDATE.CERTIFICATIONDATA = @GROUP_CERTIFICATIONDATA,
ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION = @GROUP_OMITFROMVALIDATION,
ADDRESSVALIDATIONUPDATE.CHANGEDBYID = @CHANGEAGENTID,
ADDRESSVALIDATIONUPDATE.DATECHANGED = @CURRENTDATE
from dbo.ADDRESSVALIDATIONUPDATE
where ADDRESSVALIDATIONUPDATE.ID = @GROUP_ADDRESSID
if @@ROWCOUNT = 0
begin
insert into dbo.ADDRESSVALIDATIONUPDATE (
ID,
OMITFROMVALIDATION,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@GROUP_ADDRESSID,
@ADDRESS_OMITFROMVALIDATION,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
end
if @GROUP_PHONETYPECODEID is not null
or coalesce(@GROUP_NUMBER, '') <> ''
begin
update dbo.PHONE
set PHONETYPECODEID = @GROUP_PHONETYPECODEID,
NUMBER = @GROUP_NUMBER,
DONOTCALL = @GROUP_DONOTCALL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1;
if @@ROWCOUNT = 0
insert into dbo.PHONE (
CONSTITUENTID,
ISPRIMARY,
PHONETYPECODEID,
NUMBER,
DONOTCALL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
1,
@GROUP_PHONETYPECODEID,
@GROUP_NUMBER,
@GROUP_DONOTCALL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else
delete
from dbo.PHONE
where PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1;
if @GROUP_EMAILADDRESSTYPECODEID is not null
or coalesce(@GROUP_EMAILADDRESS, '') <> ''
begin
update dbo.EMAILADDRESS
set [EMAILADDRESSTYPECODEID] = @GROUP_EMAILADDRESSTYPECODEID,
[EMAILADDRESS] = @GROUP_EMAILADDRESS,
DONOTEMAIL = @GROUP_DONOTEMAIL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 1;
if @@ROWCOUNT = 0
insert into dbo.EMAILADDRESS (
CONSTITUENTID,
ISPRIMARY,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
DONOTEMAIL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
1,
@GROUP_EMAILADDRESSTYPECODEID,
@GROUP_EMAILADDRESS,
@GROUP_DONOTEMAIL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else
delete
from dbo.EMAILADDRESS
where EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 1;
end;
else --Handle existing individual
begin
update dbo.CONSTITUENT
set KEYNAME = @LASTNAME,
FIRSTNAME = @FIRSTNAME,
MIDDLENAME = @MIDDLENAME,
MAIDENNAME = @MAIDENNAME,
NICKNAME = @NICKNAME,
TITLECODEID = @TITLECODEID,
SUFFIXCODEID = @SUFFIXCODEID,
GENDERCODE = @GENDERCODE,
BIRTHDATE = @BIRTHDATE,
MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
GENDERCODEID = @GENDERCODEID
where CONSTITUENT.ID = @ID
declare @PRIMARYADDRESSID uniqueidentifier
select @PRIMARYADDRESSID = ID
from dbo.ADDRESS
where ADDRESS.CONSTITUENTID = @ID
and ISPRIMARY = 1
-- Update matching household addresses
if @ADDRESS_UPDATEMATCHINGHOUSEHOLDADDRESSES = 1
and @PRIMARYADDRESSID is not null
begin
exec dbo.USP_ADDRESS_UPDATEMATCHINGADDRESSES @ADDRESSID = @PRIMARYADDRESSID,
@CHANGEAGENTID = @CHANGEAGENTID,
@ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID,
@DONOTMAIL = @ADDRESS_DONOTMAIL,
@COUNTRYID = @ADDRESS_COUNTRYID,
@STATEID = @ADDRESS_STATEID,
@ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
@CITY = @ADDRESS_CITY,
@POSTCODE = @ADDRESS_POSTCODE,
@CART = @ADDRESS_CART,
@DPC = @ADDRESS_DPC,
@LOT = @ADDRESS_LOT,
@OMITFROMVALIDATION = @ADDRESS_OMITFROMVALIDATION,
@COUNTYCODEID = @ADDRESS_COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID = @ADDRESS_CONGRESSIONALDISTRICTCODEID,
@LASTVALIDATIONATTEMPTDATE = @ADDRESS_LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE = @ADDRESS_VALIDATIONMESSAGE,
@CERTIFICATIONDATA = @ADDRESS_CERTIFICATIONDATA,
@DONOTMAILREASONCODEID = @ADDRESS_DONOTMAILREASONCODEID,
@UPDATEFROMREVENUEBATCH = 1
end
update dbo.ADDRESS
set ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID,
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,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @PRIMARYADDRESSID
declare @CONSTITUENT_ADDRESSID uniqueidentifier;
/* AAW 1/28/10: Changed because 'Country' is no longer a required field. Also changed for Organizations/Groups */
if @@ROWCOUNT = 0
and (
(@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
set @CONSTITUENT_ADDRESSID = newID();
insert into dbo.ADDRESS (
ID,
CONSTITUENTID,
ISPRIMARY,
ADDRESSTYPECODEID,
DONOTMAIL,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTMAILREASONCODEID
)
values (
@CONSTITUENT_ADDRESSID,
@ID,
1,
@ADDRESS_ADDRESSTYPECODEID,
@ADDRESS_DONOTMAIL,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ADDRESS_DONOTMAILREASONCODEID
)
end
else
begin
select @CONSTITUENT_ADDRESSID = ID
from dbo.ADDRESS
where ADDRESS.CONSTITUENTID = @ID
and ISPRIMARY = 1
end
if @CONSTITUENT_ADDRESSID is not null
begin
update ADDRESSVALIDATIONUPDATE
set ADDRESSVALIDATIONUPDATE.COUNTYCODEID = @ADDRESS_COUNTYCODEID,
ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID = @ADDRESS_CONGRESSIONALDISTRICTCODEID,
ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE = @ADDRESS_LASTVALIDATIONATTEMPTDATE,
ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE = @ADDRESS_VALIDATIONMESSAGE,
ADDRESSVALIDATIONUPDATE.CERTIFICATIONDATA = @ADDRESS_CERTIFICATIONDATA,
ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION = @ADDRESS_OMITFROMVALIDATION,
ADDRESSVALIDATIONUPDATE.CHANGEDBYID = @CHANGEAGENTID,
ADDRESSVALIDATIONUPDATE.DATECHANGED = @CURRENTDATE
from dbo.ADDRESSVALIDATIONUPDATE
inner join dbo.ADDRESS
on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
where ADDRESS.CONSTITUENTID = @ID
and ADDRESS.ISPRIMARY = 1
if @@ROWCOUNT = 0
begin
insert into dbo.ADDRESSVALIDATIONUPDATE (
ID,
OMITFROMVALIDATION,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENT_ADDRESSID,
@ADDRESS_OMITFROMVALIDATION,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
end
if @PHONE_PHONETYPECODEID is not null
or (
@PHONE_NUMBER is not null
and @PHONE_NUMBER <> ''
)
begin
update dbo.PHONE
set PHONETYPECODEID = @PHONE_PHONETYPECODEID,
NUMBER = @PHONE_NUMBER,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1;
if @@ROWCOUNT = 0
insert into dbo.PHONE (
CONSTITUENTID,
ISPRIMARY,
PHONETYPECODEID,
NUMBER,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
1,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else if exists (
select ID
from dbo.PHONE
where CONSTITUENTID = @ID
and ISPRIMARY = 1
)
begin
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
delete
from dbo.PHONE
where CONSTITUENTID = @ID
and ISPRIMARY = 1;
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end;
if @EMAILADDRESS_EMAILADDRESSTYPECODEID is not null
or (
@EMAILADDRESS_EMAILADDRESS is not null
and @EMAILADDRESS_EMAILADDRESS != ''
)
begin
update dbo.EMAILADDRESS
set [EMAILADDRESSTYPECODEID] = @EMAILADDRESS_EMAILADDRESSTYPECODEID,
[EMAILADDRESS] = @EMAILADDRESS_EMAILADDRESS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 1;
if @@ROWCOUNT = 0
insert into dbo.EMAILADDRESS (
CONSTITUENTID,
ISPRIMARY,
EMAILADDRESSTYPECODEID,
EMAILADDRESS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
1,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else if exists (
select ID
from dbo.EMAILADDRESS
where CONSTITUENTID = @ID
and ISPRIMARY = 1
)
begin
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
delete
from dbo.EMAILADDRESS
where CONSTITUENTID = @ID
and ISPRIMARY = 1;
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end;
end;
end;
end;
else
begin
select @ISORGANIZATION = ISORGANIZATION,
@ISGROUP = ISGROUP,
@ISHOUSEHOLD = case
when GROUPTYPECODE = 0
then 1
else 0
end
from dbo.BATCHREVENUECONSTITUENT
where ID = @ID
if @ISORGANIZATION != 0
begin
exec dbo.USP_PARSE_ORGANIZATION_NAME @ORGANIZATIONNAME,
@KEYNAME output,
@KEYNAMEPREFIX output;
update dbo.BATCHREVENUECONSTITUENT
set [KEYNAME] = @KEYNAME,
[KEYNAMEPREFIX] = @KEYNAMEPREFIX,
[ADDRESSTYPECODEID] = @ORGANIZATION_ADDRESSTYPECODEID,
[DONOTMAIL] = @ORGANIZATION_DONOTMAIL,
[DONOTMAILREASONCODEID] = @ORGANIZATION_DONOTMAILREASONCODEID,
[COUNTRYID] = @ORGANIZATION_COUNTRYID,
[STATEID] = @ORGANIZATION_STATEID,
[ADDRESSBLOCK] = @ORGANIZATION_ADDRESSBLOCK,
[CITY] = @ORGANIZATION_CITY,
[POSTCODE] = @ORGANIZATION_POSTCODE,
[OMITFROMVALIDATION] = @ORGANIZATION_OMITFROMVALIDATION,
[CART] = @ORGANIZATION_CART,
[DPC] = @ORGANIZATION_DPC,
[LOT] = @ORGANIZATION_LOT,
[COUNTYCODEID] = @ORGANIZATION_COUNTYCODEID,
[CONGRESSIONALDISTRICTCODEID] = @ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
[LASTVALIDATIONATTEMPTDATE] = @ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
[VALIDATIONMESSAGE] = @ORGANIZATION_VALIDATIONMESSAGE,
[CERTIFICATIONDATA] = @ORGANIZATION_CERTIFICATIONDATA,
[PHONETYPECODEID] = @ORGANIZATION_PHONETYPECODEID,
[NUMBER] = @ORGANIZATION_NUMBER,
[EMAILADDRESSTYPECODEID] = @ORGANIZATION_EMAILADDRESSTYPECODEID,
[EMAILADDRESS] = @ORGANIZATION_EMAILADDRESS,
[INDUSTRYCODEID] = @INDUSTRYCODEID,
[NUMEMPLOYEES] = @NUMEMPLOYEES,
[NUMSUBSIDIARIES] = @NUMSUBSIDIARIES,
[PARENTCORPID] = @PARENTCORPID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[WEBADDRESS] = @WEBADDRESS
where ID = @ID;
end;
else if @ISGROUP != 0
begin
if @ISHOUSEHOLD = 1
begin
-- Validate that the primary and secondary members aren't the same
if @HOUSEHOLD_PRIMARYCONTACTID = @HOUSEHOLD_SECONDMEMBERID
raiserror (
'ERRPRIMARYANDSECONDMEMBERSAME',
13,
1
)
end
update dbo.BATCHREVENUECONSTITUENT
set KEYNAME = @GROUP_NAME,
GROUPDESCRIPTION = @GROUP_DESCRIPTION,
GROUPTYPEID = @GROUP_GROUPTYPEID,
GIVESANONYMOUSLY = @GROUP_GIVESANONYMOUSLY,
ADDRESSTYPECODEID = @GROUP_ADDRESSTYPECODEID,
COUNTRYID = @GROUP_COUNTRYID,
ADDRESSBLOCK = @GROUP_ADDRESSBLOCK,
CITY = @GROUP_CITY,
STATEID = @GROUP_STATEID,
POSTCODE = @GROUP_POSTCODE,
[OMITFROMVALIDATION] = @GROUP_OMITFROMVALIDATION,
[CART] = @GROUP_CART,
[DPC] = @GROUP_DPC,
[LOT] = @GROUP_LOT,
[COUNTYCODEID] = @GROUP_COUNTYCODEID,
[CONGRESSIONALDISTRICTCODEID] = @GROUP_CONGRESSIONALDISTRICTCODEID,
[LASTVALIDATIONATTEMPTDATE] = @GROUP_LASTVALIDATIONATTEMPTDATE,
[VALIDATIONMESSAGE] = @GROUP_VALIDATIONMESSAGE,
[CERTIFICATIONDATA] = @GROUP_CERTIFICATIONDATA,
PHONETYPECODEID = @GROUP_PHONETYPECODEID,
NUMBER = @GROUP_NUMBER,
EMAILADDRESSTYPECODEID = @GROUP_EMAILADDRESSTYPECODEID,
EMAILADDRESS = @GROUP_EMAILADDRESS,
GROUPSTARTDATE = @GROUP_STARTDATE,
WEBADDRESS = @GROUP_WEBADDRESS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DONOTMAIL = @GROUP_DONOTMAIL,
DONOTMAILREASONCODEID = @GROUP_DONOTMAILREASONCODEID,
DONOTCALL = @GROUP_DONOTCALL,
DONOTEMAIL = @GROUP_DONOTEMAIL
where ID = @ID;
-- Handle group members
if @ISHOUSEHOLD = 0
begin
declare @MEMBERSTABLE table (
ISPRIMARY bit,
BATCHREVENUECONSTITUENTID uniqueidentifier,
MEMBERID uniqueidentifier
)
-- Deserialize the XML by hand since the root element
-- is different than the function's.
insert into @MEMBERSTABLE (
ISPRIMARY,
BATCHREVENUECONSTITUENTID,
MEMBERID
)
select T.c.value('(ISPRIMARY)[1]', 'bit'),
newid(),
T.c.value('(MEMBERID)[1]', 'uniqueidentifier')
from @GROUP_MEMBERS.nodes('/GROUP_MEMBERS/ITEM') T(c)
-- Create BATCHREVENUECONSTITUENT entries for the constituents that already exist
-- in the CONSTITUENT table and don't have records in BATCHREVENUECONSTITUENT that are
-- already members of the group.
insert into dbo.BATCHREVENUECONSTITUENT (
ID,
EXISTINGCONSTITUENTID,
ISORGANIZATION,
ISGROUP,
GROUPTYPECODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select M.BATCHREVENUECONSTITUENTID,
M.MEMBERID,
C.ISORGANIZATION,
C.ISGROUP,
coalesce(GD.GROUPTYPECODE, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERSTABLE M
inner join dbo.CONSTITUENT C
on M.MEMBERID = C.ID
left join dbo.GROUPDATA GD
on C.ID = GD.ID
-- The exist section of the join is to make sure the BATCHREVENUECONSTITUENT record
-- joined to already belonged to the group.
left join dbo.BATCHREVENUECONSTITUENT BRC
on C.ID = BRC.EXISTINGCONSTITUENTID
and exists (
select 1
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
where MEMBERID = BRC.ID
and GROUPID = @ID
)
where BRC.ID is null
-- For members that already exist in BATCHREVENUECONSTITUENT,
-- update @MEMBERSTABLE's BATCHREVENUECONSTITUENTID. @MEMBERSTABLE MEMBERID could contain either
-- a CONSTITUENT record ID or a BATCHREVENUECONSTITUENT record ID. Because of that, the below query
-- left joins to CONSTITUENT and BATCHREVENUECONSTITUENT. CONSTITUENT.ID and BATCHREVENUECONSTITUENT.ID
-- should only be null if @MEMBERSTABLE.MEMBERID contains a BATCHREVENUECONSTITUENT.ID.
update @MEMBERSTABLE
set BATCHREVENUECONSTITUENTID = case
when BRC.ID is not null
then BRC.ID
else MEMBERID
end
from @MEMBERSTABLE M
left join dbo.CONSTITUENT C
on M.MEMBERID = C.ID
-- Check for existences in BATCHREVENUECONSTITUENTGROUPMEMBER since the BATCHREVENUECONSTITUENT record should
-- only be used if they were already a member. If they weren't already a member, this constituent was created
-- for a different reason and could be changed when a different batch row is edited.
left join dbo.BATCHREVENUECONSTITUENT BRC
on BRC.EXISTINGCONSTITUENTID = M.MEMBERID
and exists (
select 1
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
where MEMBERID = BRC.ID
and GROUPID = @ID
)
where C.ID is null
or BRC.ID is not null
-- Clear the ISPRIMARY flag for this group's members
update dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
set ISPRIMARY = 0
where GROUPID = @ID;
-- Handle updating BATCHREVENUECONSTITUENTGROUPMEMBER
-- Remove old members
declare @CONSTITUENTSTOREMOVE table (BATCHREVENUECONSTITUENTID uniqueidentifier)
insert into @CONSTITUENTSTOREMOVE (BATCHREVENUECONSTITUENTID)
select MEMBERID
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
where MEMBERID not in (
select BATCHREVENUECONSTITUENTID
from @MEMBERSTABLE
)
and GROUPID = @ID
delete
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
where MEMBERID in (
select BATCHREVENUECONSTITUENTID
from @CONSTITUENTSTOREMOVE
)
and GROUPID = @ID
delete
from dbo.BATCHREVENUECONSTITUENT
where ID in (
select BATCHREVENUECONSTITUENTID
from @CONSTITUENTSTOREMOVE
)
-- Update the ISPRIMARY flag for existing members
update dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
set ISPRIMARY = M.ISPRIMARY
from BATCHREVENUECONSTITUENTGROUPMEMBER GM
inner join @MEMBERSTABLE M
on GM.MEMBERID = M.BATCHREVENUECONSTITUENTID
and GM.GROUPID = @ID
where GM.ISPRIMARY <> M.ISPRIMARY
-- Add new members
insert into dbo.BATCHREVENUECONSTITUENTGROUPMEMBER (
ID,
GROUPID,
MEMBERID,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select newid(),
@ID,
BATCHREVENUECONSTITUENTID,
ISPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERSTABLE as M
where not exists (
select MEMBERID
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER GM
where GM.MEMBERID = M.BATCHREVENUECONSTITUENTID
and GM.GROUPID = @ID
)
end
else
begin
-- Handle household members
-- Update primary member if needed
declare @PRIMARYMEMBERBATCHREVENUECONSTITUENTID uniqueidentifier
exec dbo.USP_BATCHREVENUE_UPDATEHOUSEHOLDMEMBER @PRIMARYMEMBERBATCHREVENUECONSTITUENTID output,
@ID,
1,
@HOUSEHOLD_PRIMARYCONTACTID,
@HOUSEHOLD_PRIMARYCONTACT_KEYNAME,
@HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME,
@HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME,
@HOUSEHOLD_PRIMARYCONTACT_TITLECODEID,
@HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID,
@HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT,
@CHANGEAGENTID
-- Update secondary member if needed
declare @SECONDMEMBERBATCHREVENUECONSTITUENTID uniqueidentifier
exec dbo.USP_BATCHREVENUE_UPDATEHOUSEHOLDMEMBER @SECONDMEMBERBATCHREVENUECONSTITUENTID output,
@ID,
0,
@HOUSEHOLD_SECONDMEMBERID,
@HOUSEHOLD_SECONDMEMBER_KEYNAME,
@HOUSEHOLD_SECONDMEMBER_FIRSTNAME,
@HOUSEHOLD_SECONDMEMBER_MIDDLENAME,
@HOUSEHOLD_SECONDMEMBER_TITLECODEID,
@HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID,
@HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT,
@CHANGEAGENTID
-- Update the members' relationship
-- Verify any old relationship doesn't exist
delete
from dbo.BATCHREVENUECONSTITUENTRELATION
where CONSTITUENTID in (
select MEMBERID
from BATCHREVENUECONSTITUENTGROUPMEMBER
where GROUPID = @ID
)
if @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID is not null
or @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID is not null
begin
-- Verify that if the members already exist, they aren't already related
if @HOUSEHOLD_PRIMARYCONTACTID is null
or @HOUSEHOLD_SECONDMEMBERID is null
or not exists (
select 1
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @HOUSEHOLD_PRIMARYCONTACTID
and RECIPROCALCONSTITUENTID = @HOUSEHOLD_SECONDMEMBERID
)
begin
declare @HOUSEHOLD_SECONDMEMBER_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
declare @HOUSEHOLD_SECONDMEMBER_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
declare @HOUSEHOLD_SECONDMEMBER_PRIMARYRELATIONSHIPEXISTS bit
declare @HOUSEHOLD_SECONDMEMBER_RECIPROCALRELATIONSHIPEXISTS bit
declare @HOUSEHOLD_SECONDMEMBER_PRIMARYMATCHFACTOR decimal(5, 2)
declare @HOUSEHOLD_SECONDMEMBER_RECIPROCALMATCHFACTOR decimal(5, 2)
select @HOUSEHOLD_SECONDMEMBER_PRIMARYRELATIONSHIPEXISTS = 1,
@HOUSEHOLD_SECONDMEMBER_PRIMARYRECOGNITIONTYPECODEID = RRD.REVENUERECOGNITIONTYPECODEID,
@HOUSEHOLD_SECONDMEMBER_PRIMARYMATCHFACTOR = RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE = 0
and RRD.RELATIONSHIPTYPECODEID = @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID
select @HOUSEHOLD_SECONDMEMBER_RECIPROCALRELATIONSHIPEXISTS = 1,
@HOUSEHOLD_SECONDMEMBER_RECIPROCALRECOGNITIONTYPECODEID = RRD.REVENUERECOGNITIONTYPECODEID,
@HOUSEHOLD_SECONDMEMBER_RECIPROCALMATCHFACTOR = RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE = 0
and RRD.RELATIONSHIPTYPECODEID = @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID
insert into dbo.BATCHREVENUECONSTITUENTRELATION (
CONSTITUENTID,
RELATIONID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
STARTDATE,
PRIMARYRELATIONSHIPEXISTS,
PRIMARYMATCHFACTOR,
PRIMARYRECOGNITIONTYPECODEID,
RECIPROCALRELATIONSHIPEXISTS,
RECIPROCALMATCHFACTOR,
RECIPROCALRECOGNITIONTYPECODEID,
ISSPOUSE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@PRIMARYMEMBERBATCHREVENUECONSTITUENTID,
@SECONDMEMBERBATCHREVENUECONSTITUENTID,
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID,
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID,
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE,
ISNULL(@HOUSEHOLD_SECONDMEMBER_PRIMARYRELATIONSHIPEXISTS, 0),
ISNULL(@HOUSEHOLD_SECONDMEMBER_PRIMARYMATCHFACTOR, 0),
@HOUSEHOLD_SECONDMEMBER_PRIMARYRECOGNITIONTYPECODEID,
ISNULL(@HOUSEHOLD_SECONDMEMBER_RECIPROCALRELATIONSHIPEXISTS, 0),
ISNULL(@HOUSEHOLD_SECONDMEMBER_RECIPROCALMATCHFACTOR, 0),
@HOUSEHOLD_SECONDMEMBER_RECIPROCALRECOGNITIONTYPECODEID,
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
end
end
end
else
begin
set @KEYNAME = @LASTNAME;
set @KEYNAMEPREFIX = '';
update dbo.BATCHREVENUECONSTITUENT
set [KEYNAME] = @KEYNAME,
[KEYNAMEPREFIX] = @KEYNAMEPREFIX,
[FIRSTNAME] = @FIRSTNAME,
[MIDDLENAME] = @MIDDLENAME,
[MAIDENNAME] = @MAIDENNAME,
[NICKNAME] = @NICKNAME,
[TITLECODEID] = @TITLECODEID,
[SUFFIXCODEID] = @SUFFIXCODEID,
[GENDERCODE] = @GENDERCODE,
[BIRTHDATE] = @BIRTHDATE,
[MARITALSTATUSCODEID] = @MARITALSTATUSCODEID,
[ADDRESSTYPECODEID] = @ADDRESS_ADDRESSTYPECODEID,
[DONOTMAIL] = @ADDRESS_DONOTMAIL,
[DONOTMAILREASONCODEID] = @ADDRESS_DONOTMAILREASONCODEID,
[COUNTRYID] = @ADDRESS_COUNTRYID,
[STATEID] = @ADDRESS_STATEID,
[ADDRESSBLOCK] = @ADDRESS_ADDRESSBLOCK,
[CITY] = @ADDRESS_CITY,
[POSTCODE] = @ADDRESS_POSTCODE,
[OMITFROMVALIDATION] = @ADDRESS_OMITFROMVALIDATION,
[CART] = @ADDRESS_CART,
[DPC] = @ADDRESS_DPC,
[LOT] = @ADDRESS_LOT,
[COUNTYCODEID] = @ADDRESS_COUNTYCODEID,
[CONGRESSIONALDISTRICTCODEID] = @ADDRESS_CONGRESSIONALDISTRICTCODEID,
[LASTVALIDATIONATTEMPTDATE] = @ADDRESS_LASTVALIDATIONATTEMPTDATE,
[VALIDATIONMESSAGE] = @ADDRESS_VALIDATIONMESSAGE,
[CERTIFICATIONDATA] = @ADDRESS_CERTIFICATIONDATA,
[PHONETYPECODEID] = @PHONE_PHONETYPECODEID,
[NUMBER] = @PHONE_NUMBER,
[EMAILADDRESSTYPECODEID] = @EMAILADDRESS_EMAILADDRESSTYPECODEID,
[EMAILADDRESS] = @EMAILADDRESS_EMAILADDRESS,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[GENDERCODEID] = @GENDERCODEID
where ID = @ID;
-------------- Update Spouse's Information --------------
declare @ISSPOUSE bit
if (@SPOUSEID is not null)
or (coalesce(@SPOUSE_LASTNAME, '') <> '')
set @ISSPOUSE = 1
else
set @ISSPOUSE = 0
declare @BATCHHOUSEHOLDID uniqueidentifier
select @BATCHHOUSEHOLDID = BRC.ID
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
inner join dbo.BATCHREVENUECONSTITUENT BRC
on BRCGM.GROUPID = BRC.ID
where BRC.GROUPTYPECODE = 0
and BRCGM.MEMBERID = @ID
--If their is a spouse create the relationship
if @ISSPOUSE = 1
begin
if @SPOUSE_GENDERCODE is null
set @SPOUSE_GENDERCODE = 0;
declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
declare @PRIMARYRELATIONSHIPEXISTS bit
declare @RECIPROCALRELATIONSHIPEXISTS bit
declare @PRIMARYMATCHFACTOR decimal(5, 2)
declare @RECIPROCALMATCHFACTOR decimal(5, 2)
select @PRIMARYRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONTYPECODEID = RRD.REVENUERECOGNITIONTYPECODEID,
@PRIMARYMATCHFACTOR = RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE = 0
and RRD.RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID
select @RECIPROCALRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONTYPECODEID = RRD.REVENUERECOGNITIONTYPECODEID,
@RECIPROCALMATCHFACTOR = RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE = 0
and RRD.RELATIONSHIPTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID
if @BATCHSPOUSEID is null
begin
set @BATCHSPOUSEID = newID();
insert into dbo.BATCHREVENUECONSTITUENT (
[ID],
[ISORGANIZATION],
[ISGROUP],
[EXISTINGCONSTITUENTID],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[MARITALSTATUSCODEID],
[CURRENTAPPUSERID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[GENDERCODEID]
)
values (
@BATCHSPOUSEID,
0,
0,
@SPOUSEID,
@SPOUSE_LASTNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_BIRTHDATE,
@MARITALSTATUSCODEID,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@SPOUSE_GENDERCODEID
);
insert into dbo.BATCHREVENUECONSTITUENTRELATION (
[CONSTITUENTID],
[RELATIONID],
[COPYPRIMARYINFORMATION],
[RELATIONSHIPTYPECODEID],
[RECIPROCALTYPECODEID],
[STARTDATE],
[PRIMARYRELATIONSHIPEXISTS],
[PRIMARYMATCHFACTOR],
[PRIMARYRECOGNITIONTYPECODEID],
[RECIPROCALRELATIONSHIPEXISTS],
[RECIPROCALMATCHFACTOR],
[RECIPROCALRECOGNITIONTYPECODEID],
[ISSPOUSE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values (
@ID,
@BATCHSPOUSEID,
@COPYPRIMARYINFORMATION,
@SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID,
@SPOUSE_STARTDATE,
ISNULL(@PRIMARYRELATIONSHIPEXISTS, 0),
ISNULL(@PRIMARYMATCHFACTOR, 0),
@PRIMARYRECOGNITIONTYPECODEID,
ISNULL(@RECIPROCALRELATIONSHIPEXISTS, 0),
ISNULL(@RECIPROCALMATCHFACTOR, 0),
@RECIPROCALRECOGNITIONTYPECODEID,
@ISSPOUSERELATIONSHIP,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- Create the household
exec dbo.USP_BATCHREVENUE_ADDINDIVIDUALHOUSEHOLD @ID,
@BATCHSPOUSEID,
@HOUSEHOLDCOPYPRIMARYCONTACTINFO,
@CURRENTAPPUSERID,
@CHANGEAGENTID;
end
else
begin
update dbo.BATCHREVENUECONSTITUENT
set [EXISTINGCONSTITUENTID] = @SPOUSEID,
[KEYNAME] = @SPOUSE_LASTNAME,
[FIRSTNAME] = @SPOUSE_FIRSTNAME,
[MIDDLENAME] = @SPOUSE_MIDDLENAME,
[MAIDENNAME] = @SPOUSE_MAIDENNAME,
[NICKNAME] = @SPOUSE_NICKNAME,
[TITLECODEID] = @SPOUSE_TITLECODEID,
[SUFFIXCODEID] = @SPOUSE_SUFFIXCODEID,
[GENDERCODE] = @SPOUSE_GENDERCODE,
[BIRTHDATE] = @SPOUSE_BIRTHDATE,
[MARITALSTATUSCODEID] = @MARITALSTATUSCODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[GENDERCODEID] = @SPOUSE_GENDERCODEID
where ID = @BATCHSPOUSEID;
update dbo.BATCHREVENUECONSTITUENTRELATION
set [COPYPRIMARYINFORMATION] = @COPYPRIMARYINFORMATION,
[RELATIONSHIPTYPECODEID] = @SPOUSE_RELATIONSHIPTYPECODEID,
[RECIPROCALTYPECODEID] = @SPOUSE_RECIPROCALTYPECODEID,
[STARTDATE] = @SPOUSE_STARTDATE,
[PRIMARYRELATIONSHIPEXISTS] = ISNULL(@PRIMARYRELATIONSHIPEXISTS, 0),
[PRIMARYMATCHFACTOR] = ISNULL(@PRIMARYMATCHFACTOR, 0),
[PRIMARYRECOGNITIONTYPECODEID] = @PRIMARYRECOGNITIONTYPECODEID,
[RECIPROCALRELATIONSHIPEXISTS] = ISNULL(@RECIPROCALRELATIONSHIPEXISTS, 0),
[RECIPROCALMATCHFACTOR] = ISNULL(@RECIPROCALMATCHFACTOR, 0),
[RECIPROCALRECOGNITIONTYPECODEID] = @RECIPROCALRECOGNITIONTYPECODEID,
[ISSPOUSE] = @ISSPOUSERELATIONSHIP,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where ID = @BATCHSPOUSERELATIONID;
-- Update the household related to this relationship if needed
-- If there isn't a current household, create a new one
if @BATCHHOUSEHOLDID is null
exec dbo.USP_BATCHREVENUE_ADDINDIVIDUALHOUSEHOLD @ID,
@BATCHSPOUSEID,
@HOUSEHOLDCOPYPRIMARYCONTACTINFO,
@CURRENTAPPUSERID,
@CHANGEAGENTID;
else
begin
-- Otherwise, update the existing household
declare @HOUSEHOLDID uniqueidentifier,
@HOUSEHOLDNAME nvarchar(100),
@USEHOUSEHOLDID bit
exec dbo.USP_BATCHREVENUE_GETHOUSEHOLDNAMEORID @ID,
@BATCHSPOUSEID,
@HOUSEHOLDID output,
@HOUSEHOLDNAME output,
@USEHOUSEHOLDID output
declare @EXISTINGHOUSEHOLDID uniqueidentifier,
@EXISTINGHOUSEHOLDNAME nvarchar(100)
select @EXISTINGHOUSEHOLDID = EXISTINGCONSTITUENTID,
@EXISTINGHOUSEHOLDNAME = KEYNAME
from dbo.BATCHREVENUECONSTITUENT
where ID = @BATCHHOUSEHOLDID
if @USEHOUSEHOLDID = 1
and (
@EXISTINGHOUSEHOLDID is null
or @EXISTINGHOUSEHOLDID <> @HOUSEHOLDID
)
begin
update dbo.BATCHREVENUECONSTITUENT
set EXISTINGCONSTITUENTID = @HOUSEHOLDID,
KEYNAME = '',
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @BATCHHOUSEHOLDID
end
if @USEHOUSEHOLDID = 0
and coalesce(@HOUSEHOLDNAME, '') <> coalesce(@EXISTINGHOUSEHOLDNAME, '')
begin
update dbo.BATCHREVENUECONSTITUENT
set EXISTINGCONSTITUENTID = null,
KEYNAME = @HOUSEHOLDNAME,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @BATCHHOUSEHOLDID
end
-- Clear or update the primary contact info
declare @CURRENTHOUSEHOLDCOPYPRIMARYCONTACTINFO bit
select @CURRENTHOUSEHOLDCOPYPRIMARYCONTACTINFO = COPYMEMBERCONTACTINFOTOGROUP
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
where GROUPID = @BATCHHOUSEHOLDID
and MEMBERID = @ID
if @CURRENTHOUSEHOLDCOPYPRIMARYCONTACTINFO <> @HOUSEHOLDCOPYPRIMARYCONTACTINFO
update dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
set COPYMEMBERCONTACTINFOTOGROUP = @HOUSEHOLDCOPYPRIMARYCONTACTINFO,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where GROUPID = @BATCHHOUSEHOLDID
and MEMBERID = @ID
end
end
end
else
begin
if @BATCHSPOUSERELATIONID is not null
exec dbo.USP_BATCHREVENUECONSTITUENTRELATION_DELETEBYID_WITHCHANGEAGENTID @BATCHSPOUSERELATIONID,
@CHANGEAGENTID;
if @BATCHSPOUSEID is not null
begin
-- Remove the added spouse and corresponding household
delete
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
where GROUPID = @BATCHHOUSEHOLDID
exec dbo.USP_BATCHREVENUECONSTITUENT_DELETEBYID_WITHCHANGEAGENTID @BATCHHOUSEHOLDID,
@CHANGEAGENTID;
exec dbo.USP_BATCHREVENUECONSTITUENT_DELETEBYID_WITHCHANGEAGENTID @BATCHSPOUSEID,
@CHANGEAGENTID;
end
end
-------------- update Organization Information --------------
declare @HASORGANIZATION bit
if (@BUSINESSID is not null)
or (coalesce(@BUSINESS_NAME, '') <> '')
set @HASORGANIZATION = 1
else
set @HASORGANIZATION = 0
--If their is an organization create the relationship
if @HASORGANIZATION = 1
begin
---------Organization Constituent Information---------
declare @ORGKEYNAME nvarchar(100);
declare @ORGKEYNAMEPREFIX nvarchar(50);
exec dbo.USP_PARSE_ORGANIZATION_NAME @BUSINESS_NAME,
@ORGKEYNAME output,
@ORGKEYNAMEPREFIX output;
declare @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
declare @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
declare @BUSINESS_PRIMARYRELATIONSHIPEXISTS bit
declare @BUSINESS_RECIPROCALRELATIONSHIPEXISTS bit
declare @BUSINESS_PRIMARYMATCHFACTOR decimal(5, 2)
declare @BUSINESS_RECIPROCALMATCHFACTOR decimal(5, 2)
select @BUSINESS_PRIMARYRELATIONSHIPEXISTS = 1,
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID = RRD.REVENUERECOGNITIONTYPECODEID,
@BUSINESS_PRIMARYMATCHFACTOR = RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE = 0
and RRD.RELATIONSHIPTYPECODEID = @BUSINESS_RELATIONSHIPTYPECODEID
select @BUSINESS_RECIPROCALRELATIONSHIPEXISTS = 1,
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = RRD.REVENUERECOGNITIONTYPECODEID,
@BUSINESS_RECIPROCALMATCHFACTOR = RRD.MATCHFACTOR
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE = 1
and RRD.RELATIONSHIPTYPECODEID = @BUSINESS_RECIPROCALTYPECODEID
if @BATCHBUSINESSID is null
begin
set @BATCHBUSINESSID = newID();
insert into dbo.BATCHREVENUECONSTITUENT (
[ID],
[ISORGANIZATION],
[EXISTINGCONSTITUENTID],
[KEYNAME],
[KEYNAMEPREFIX],
[ADDRESSTYPECODEID],
[DONOTMAIL],
[DONOTMAILREASONCODEID],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[OMITFROMVALIDATION],
[CART],
[DPC],
[LOT],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[CERTIFICATIONDATA],
[PHONETYPECODEID],
[NUMBER],
[CURRENTAPPUSERID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values (
@BATCHBUSINESSID,
1,
@BUSINESSID,
@ORGKEYNAME,
@ORGKEYNAMEPREFIX,
@BUSINESS_ADDRESSTYPECODEID,
@BUSINESS_DONOTMAIL,
@BUSINESS_DONOTMAILREASONCODEID,
@BUSINESS_COUNTRYID,
@BUSINESS_STATEID,
@BUSINESS_ADDRESSBLOCK,
@BUSINESS_CITY,
@BUSINESS_POSTCODE,
@BUSINESS_OMITFROMVALIDATION,
@BUSINESS_CART,
@BUSINESS_DPC,
@BUSINESS_LOT,
@BUSINESS_COUNTYCODEID,
@BUSINESS_CONGRESSIONALDISTRICTCODEID,
@BUSINESS_LASTVALIDATIONATTEMPTDATE,
@BUSINESS_VALIDATIONMESSAGE,
@BUSINESS_CERTIFICATIONDATA,
@BUSINESS_PHONETYPECODEID,
@BUSINESS_NUMBER,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.BATCHREVENUECONSTITUENTRELATION (
[CONSTITUENTID],
[RELATIONID],
[ISPRIMARYBUSINESS],
[RELATIONSHIPTYPECODEID],
[RECIPROCALTYPECODEID],
[ISCONTACT],
[ISPRIMARYCONTACT],
[CONTACTTYPECODEID],
[STARTDATE],
[PRIMARYRELATIONSHIPEXISTS],
[PRIMARYMATCHFACTOR],
[PRIMARYRECOGNITIONTYPECODEID],
[RECIPROCALRELATIONSHIPEXISTS],
[RECIPROCALMATCHFACTOR],
[RECIPROCALRECOGNITIONTYPECODEID],
[POSITION],
[ISMATCHINGGIFTRELATIONSHIP],
[JOBCATEGORYCODEID],
[CAREERLEVELCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values (
@ID,
@BATCHBUSINESSID,
1,
@BUSINESS_RELATIONSHIPTYPECODEID,
@BUSINESS_RECIPROCALTYPECODEID,
@ISCONTACT,
@ISPRIMARYCONTACT,
@CONTACTTYPECODEID,
@BUSINESS_STARTDATE,
ISNULL(@BUSINESS_PRIMARYRELATIONSHIPEXISTS, 0),
ISNULL(@BUSINESS_PRIMARYMATCHFACTOR, 0),
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID,
ISNULL(@BUSINESS_RECIPROCALRELATIONSHIPEXISTS, 0),
ISNULL(@BUSINESS_RECIPROCALMATCHFACTOR, 0),
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
@POSITION,
@ISMATCHINGGIFTRELATIONSHIP,
@JOBCATEGORYCODEID,
@CAREERLEVELCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else
begin
update dbo.BATCHREVENUECONSTITUENT
set [EXISTINGCONSTITUENTID] = @BUSINESSID,
[KEYNAME] = @ORGKEYNAME,
[KEYNAMEPREFIX] = @ORGKEYNAMEPREFIX,
[ADDRESSTYPECODEID] = @BUSINESS_ADDRESSTYPECODEID,
[DONOTMAIL] = @BUSINESS_DONOTMAIL,
[DONOTMAILREASONCODEID] = @BUSINESS_DONOTMAILREASONCODEID,
[COUNTRYID] = @BUSINESS_COUNTRYID,
[STATEID] = @BUSINESS_STATEID,
[ADDRESSBLOCK] = @BUSINESS_ADDRESSBLOCK,
[CITY] = @BUSINESS_CITY,
[POSTCODE] = @BUSINESS_POSTCODE,
[OMITFROMVALIDATION] = @BUSINESS_OMITFROMVALIDATION,
[CART] = @BUSINESS_CART,
[DPC] = @BUSINESS_DPC,
[LOT] = @BUSINESS_LOT,
[COUNTYCODEID] = @BUSINESS_COUNTYCODEID,
[CONGRESSIONALDISTRICTCODEID] = @BUSINESS_CONGRESSIONALDISTRICTCODEID,
[LASTVALIDATIONATTEMPTDATE] = @BUSINESS_LASTVALIDATIONATTEMPTDATE,
[VALIDATIONMESSAGE] = @BUSINESS_VALIDATIONMESSAGE,
[CERTIFICATIONDATA] = @BUSINESS_CERTIFICATIONDATA,
[PHONETYPECODEID] = @BUSINESS_PHONETYPECODEID,
[NUMBER] = @BUSINESS_NUMBER,
[ISORGANIZATION] = 1,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where ID = @BATCHBUSINESSID;
update dbo.BATCHREVENUECONSTITUENTRELATION
set [RELATIONSHIPTYPECODEID] = @BUSINESS_RELATIONSHIPTYPECODEID,
[RECIPROCALTYPECODEID] = @BUSINESS_RECIPROCALTYPECODEID,
[ISCONTACT] = @ISCONTACT,
[ISPRIMARYCONTACT] = @ISPRIMARYCONTACT,
[CONTACTTYPECODEID] = @CONTACTTYPECODEID,
[STARTDATE] = @BUSINESS_STARTDATE,
[PRIMARYRELATIONSHIPEXISTS] = ISNULL(@BUSINESS_PRIMARYRELATIONSHIPEXISTS, 0),
[PRIMARYMATCHFACTOR] = ISNULL(@BUSINESS_PRIMARYMATCHFACTOR, 0),
[PRIMARYRECOGNITIONTYPECODEID] = @BUSINESS_PRIMARYRECOGNITIONTYPECODEID,
[RECIPROCALRELATIONSHIPEXISTS] = ISNULL(@BUSINESS_RECIPROCALRELATIONSHIPEXISTS, 0),
[RECIPROCALMATCHFACTOR] = ISNULL(@BUSINESS_RECIPROCALMATCHFACTOR, 0),
[RECIPROCALRECOGNITIONTYPECODEID] = @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
[POSITION] = @POSITION,
[ISMATCHINGGIFTRELATIONSHIP] = @ISMATCHINGGIFTRELATIONSHIP,
[JOBCATEGORYCODEID] = @JOBCATEGORYCODEID,
[CAREERLEVELCODEID] = @CAREERLEVELCODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where ID = @BATCHBUSINESSRELATIONID;
end;
end;
else
begin
if @BATCHBUSINESSRELATIONID is not null
exec dbo.USP_BATCHREVENUECONSTITUENTRELATION_DELETEBYID_WITHCHANGEAGENTID @BATCHBUSINESSRELATIONID,
@CHANGEAGENTID;
if @BATCHBUSINESSID is not null
exec dbo.USP_BATCHREVENUECONSTITUENT_DELETEBYID_WITHCHANGEAGENTID @BATCHBUSINESSID,
@CHANGEAGENTID;
end;
end;
end;
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end;