USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENTINBATCH_5
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(110) | 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 | |
| @ADDRESSID | uniqueidentifier | IN | |
| @PHONEID | uniqueidentifier | IN | |
| @EMAILADDRESSID | uniqueidentifier | IN | |
| @SITEID | uniqueidentifier | IN | |
| @CONSTITUENTSECURITYGROUPID | uniqueidentifier | IN | |
| @CONSTITUENCYCODEID | uniqueidentifier | IN | |
| @INFOSOURCECODEID | uniqueidentifier | IN | |
| @NAMEFORMATS | xml | IN | |
| @PHONE_COUNTRYID | uniqueidentifier | IN | |
| @SPOUSE_LOOKUPID | nvarchar(100) | IN | |
| @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @SPOUSE_PRIMARYRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @BUSINESS_PRIMARYRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS | bit | IN | |
| @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR | decimal(5, 2) | IN | |
| @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID | uniqueidentifier | IN | |
| @ORIGINAL_KEYNAME | nvarchar(100) | IN | |
| @ORIGINAL_FIRSTNAME | nvarchar(50) | IN | |
| @GENDERCODEID | uniqueidentifier | IN | |
| @SPOUSE_GENDERCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENTINBATCH_5
(
@ID nvarchar(110),
@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,
-- The records that were shown on the form.
@ADDRESSID uniqueidentifier,
@PHONEID uniqueidentifier,
@EMAILADDRESSID uniqueidentifier,
-- Fields specific to BBIS created ERB
@SITEID uniqueidentifier,
@CONSTITUENTSECURITYGROUPID uniqueidentifier,
@CONSTITUENCYCODEID uniqueidentifier,
@INFOSOURCECODEID uniqueidentifier,
@NAMEFORMATS xml,
@PHONE_COUNTRYID uniqueidentifier,
@SPOUSE_LOOKUPID nvarchar(100),
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
@ORIGINAL_KEYNAME nvarchar(100),
@ORIGINAL_FIRSTNAME nvarchar(50),
@GENDERCODEID uniqueidentifier,
@SPOUSE_GENDERCODEID uniqueidentifier
) as begin
set nocount on;
declare @CONSTITUENTID uniqueidentifier = cast(substring(@ID,1,36) as uniqueidentifier);
declare @BATCHID uniqueidentifier = cast(substring(@ID,38,36) as uniqueidentifier);
declare @BATCHROWID uniqueidentifier = cast(substring(@ID,75,36) as uniqueidentifier);
declare @CURRENTDATE datetime;
declare @KEYNAME nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(50);
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 = @CONSTITUENTID;
if @ISEXISTINGCONSTITUENT= 1
begin
-- Check constituent security. The check is done manually since it only applies to existing constituents. If @CURRENTAPPUSERID is null then we're dealing with auto-match during commit.
if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 0) and
(dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, '08F0259C-46C1-4162-B93B-178CB455A7BE', @CONSTITUENTID) = 0) and (@CURRENTAPPUSERID is not null)
raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED', 13, 1);
-- Use constituent update batch for existing constituent edits.
declare @CONSTITUENTTYPECODE tinyint = case when @ISORGANIZATION = 1 then 1
when @ISHOUSEHOLD = 1 then 2
when @ISGROUP = 1 then 3
else 0 end
if @ISORGANIZATION = 1
begin
set @LASTNAME = @ORGANIZATIONNAME
set @ADDRESS_ADDRESSTYPECODEID = @ORGANIZATION_ADDRESSTYPECODEID
set @ADDRESS_DONOTMAIL = @ORGANIZATION_DONOTMAIL
set @ADDRESS_DONOTMAILREASONCODEID = @ORGANIZATION_DONOTMAILREASONCODEID
set @ADDRESS_COUNTRYID = @ORGANIZATION_COUNTRYID
set @ADDRESS_STATEID = @ORGANIZATION_STATEID
set @ADDRESS_ADDRESSBLOCK = @ORGANIZATION_ADDRESSBLOCK
set @ADDRESS_CITY = @ORGANIZATION_CITY
set @ADDRESS_POSTCODE = @ORGANIZATION_POSTCODE
set @ADDRESS_OMITFROMVALIDATION = @ORGANIZATION_OMITFROMVALIDATION
set @ADDRESS_CART = @ORGANIZATION_CART
set @ADDRESS_DPC = @ORGANIZATION_DPC
set @ADDRESS_LOT = @ORGANIZATION_LOT
set @ADDRESS_COUNTYCODEID = @ORGANIZATION_COUNTYCODEID
set @ADDRESS_CONGRESSIONALDISTRICTCODEID = @ORGANIZATION_CONGRESSIONALDISTRICTCODEID
set @ADDRESS_LASTVALIDATIONATTEMPTDATE = @ORGANIZATION_LASTVALIDATIONATTEMPTDATE
set @ADDRESS_VALIDATIONMESSAGE = @ORGANIZATION_VALIDATIONMESSAGE
set @ADDRESS_CERTIFICATIONDATA = @ORGANIZATION_CERTIFICATIONDATA
set @PHONE_PHONETYPECODEID = @ORGANIZATION_PHONETYPECODEID
set @PHONE_NUMBER = @ORGANIZATION_NUMBER
set @EMAILADDRESS_EMAILADDRESSTYPECODEID = @ORGANIZATION_EMAILADDRESSTYPECODEID
set @EMAILADDRESS_EMAILADDRESS = @ORGANIZATION_EMAILADDRESS
end;
else if @ISGROUP = 1
begin
set @LASTNAME = @GROUP_NAME
set @ADDRESS_ADDRESSTYPECODEID = @GROUP_ADDRESSTYPECODEID
set @ADDRESS_COUNTRYID = @GROUP_COUNTRYID
set @ADDRESS_STATEID = @GROUP_STATEID
set @ADDRESS_ADDRESSBLOCK = @GROUP_ADDRESSBLOCK
set @ADDRESS_CITY = @GROUP_CITY
set @ADDRESS_POSTCODE = @GROUP_POSTCODE
set @ADDRESS_OMITFROMVALIDATION = @GROUP_OMITFROMVALIDATION
set @ADDRESS_CART = @GROUP_CART
set @ADDRESS_DPC = @GROUP_DPC
set @ADDRESS_LOT = @GROUP_LOT
set @ADDRESS_COUNTYCODEID = @GROUP_COUNTYCODEID
set @ADDRESS_CONGRESSIONALDISTRICTCODEID = @GROUP_CONGRESSIONALDISTRICTCODEID
set @ADDRESS_LASTVALIDATIONATTEMPTDATE = @GROUP_LASTVALIDATIONATTEMPTDATE
set @ADDRESS_VALIDATIONMESSAGE = @GROUP_VALIDATIONMESSAGE
set @ADDRESS_CERTIFICATIONDATA = @GROUP_CERTIFICATIONDATA
set @PHONE_PHONETYPECODEID = @GROUP_PHONETYPECODEID
set @PHONE_NUMBER = @GROUP_NUMBER
set @EMAILADDRESS_EMAILADDRESSTYPECODEID = @GROUP_EMAILADDRESSTYPECODEID
set @EMAILADDRESS_EMAILADDRESS = @GROUP_EMAILADDRESS
set @WEBADDRESS = @GROUP_WEBADDRESS
set @ADDRESS_DONOTMAIL = @GROUP_DONOTMAIL
set @ADDRESS_DONOTMAILREASONCODEID = @GROUP_DONOTMAILREASONCODEID
set @GROUP_DONOTCALL = @GROUP_DONOTCALL
set @GROUP_DONOTEMAIL = @GROUP_DONOTEMAIL
end
declare @ADDRESSES xml, @PHONES xml, @EMAILADDRESSES xml;
-- Rather than having to keep pulling in CUB fields that are not handled by this form, we're just populating those fields with nulls or null-equivalents.
-- The proper values for those fields will be pulled from the existing constituent prior to calling CUB commit edit save.
-- CUB add will properly convert nulls to null-equivalents for all fields.
-- CUB edit will properly convert nulls to null-equivalents for non-collection fields only.
-- The generated collection USPs do not handle nulls, so we must set the right values here. We do this by pulling those field values from the CUB tables.
-- CUBFLAG:
-- 0 = Add
-- 1 = Edit
-- 2 = Change of constituent, delete CUB for previous constituent, then Add
declare @CUBFLAG tinyint = 0;
select @CUBFLAG = case when PRIMARYRECORDID = @CONSTITUENTID then 1 else 2 end from dbo.BATCHCONSTITUENTUPDATE where ID = @BATCHROWID;
-- Delete CUB row for a different constituent if it exists.
if @CUBFLAG = 2
begin
delete from dbo.BATCHCONSTITUENTUPDATE
where ID = @BATCHROWID;
-- From here forward, treat as an add.
set @CUBFLAG = 0;
end
set @ADDRESSES = (
select b.ID,
@ADDRESSID ADDRESSID,
@ADDRESS_ADDRESSBLOCK ADDRESSBLOCK,
@ADDRESS_ADDRESSTYPECODEID ADDRESSTYPECODEID,
@ADDRESS_CART CART,
@ADDRESS_CERTIFICATIONDATA CERTIFICATIONDATA,
@ADDRESS_CITY CITY,
@ADDRESS_CONGRESSIONALDISTRICTCODEID CONGRESSIONALDISTRICTCODEID,
@ADDRESS_COUNTRYID COUNTRYID,
@ADDRESS_COUNTYCODEID COUNTYCODEID,
@ADDRESS_DONOTMAIL DONOTMAIL,
@ADDRESS_DONOTMAILREASONCODEID DONOTMAILREASONCODEID,
@ADDRESS_DPC DPC,
coalesce(b.ENDDATE,a.ENDDATE,'0000') ENDDATE,
coalesce(b.HISTORICALENDDATE,a.HISTORICALENDDATE) HISTORICALENDDATE,
coalesce(b.HISTORICALSTARTDATE,a.HISTORICALSTARTDATE) HISTORICALSTARTDATE,
coalesce(b.INFOSOURCECODEID,v.INFOSOURCECODEID,@INFOSOURCECODEID) INFOSOURCECODEID,
coalesce(b.INFOSOURCECOMMENTS,v.INFOSOURCECOMMENTS,'') INFOSOURCECOMMENTS,
coalesce(b.ISPRIMARY,a.ISPRIMARY,1) ISPRIMARY,
@ADDRESS_LASTVALIDATIONATTEMPTDATE LASTVALIDATIONATTEMPTDATE,
coalesce(b.LOCALPRECINCTCODEID,v.LOCALPRECINCTCODEID) LOCALPRECINCTCODEID,
@ADDRESS_LOT LOT,
@ADDRESS_OMITFROMVALIDATION OMITFROMVALIDATION,
@ADDRESS_POSTCODE POSTCODE,
coalesce(b.REGIONCODEID,v.REGIONCODEID) REGIONCODEID,
coalesce(b.SEQUENCE,a.SEQUENCE,0) SEQUENCE,
coalesce(b.STARTDATE,a.STARTDATE,'0000') STARTDATE,
coalesce(b.STATEHOUSEDISTRICTCODEID,v.STATEHOUSEDISTRICTCODEID) STATEHOUSEDISTRICTCODEID,
@ADDRESS_STATEID STATEID,
coalesce(b.STATESENATEDISTRICTCODEID,v.STATESENATEDISTRICTCODEID) STATESENATEDISTRICTCODEID,
coalesce(b.UPDATEHOUSEHOLD,0) UPDATEHOUSEHOLD,
@ADDRESS_VALIDATIONMESSAGE VALIDATIONMESSAGE
from (select 1 dummy) x
left outer join dbo.BATCHCONSTITUENTUPDATEADDRESSES b on b.BATCHCONSTITUENTUPDATEID = @BATCHROWID
left outer join dbo.ADDRESS a on a.ID = @ADDRESSID
left outer join dbo.ADDRESSVALIDATIONUPDATE v on v.ID = a.ID
where @ADDRESS_COUNTRYID is not null
for xml raw('ITEM'),type,elements,root('ADDRESSES'),BINARY BASE64
)
set @PHONES = (
select b.ID,
@PHONEID PHONEID,
@PHONE_COUNTRYID COUNTRYID,
@GROUP_DONOTCALL DONOTCALL,
b.ENDDATE,
coalesce(b.ENDTIME,p.ENDTIME,'0000') ENDTIME,
coalesce(b.INFOSOURCECODEID,@INFOSOURCECODEID) INFOSOURCECODEID,
isnull(b.ISPRIMARY,1) ISPRIMARY,
@PHONE_NUMBER NUMBER,
@PHONE_PHONETYPECODEID PHONETYPECODEID,
coalesce(b.SEASONALENDDATE,p.SEASONALENDDATE,'0000') SEASONALENDDATE,
coalesce(b.SEASONALSTARTDATE,p.SEASONALSTARTDATE,'0000') SEASONALSTARTDATE,
coalesce(b.SEQUENCE,p.SEQUENCE,0) SEQUENCE,
b.STARTDATE,
coalesce(b.STARTTIME,p.STARTTIME,'0000') STARTTIME,
coalesce(b.UPDATEHOUSEHOLD,0) UPDATEHOUSEHOLD
from (select 1 dummy) x
left outer join dbo.BATCHCONSTITUENTUPDATEPHONES b on b.BATCHCONSTITUENTUPDATEID = @BATCHROWID
left outer join dbo.PHONE p on p.ID = @PHONEID
where @PHONE_NUMBER <> ''
for xml raw('ITEM'),type,elements,root('PHONES'),BINARY BASE64
)
set @EMAILADDRESSES = (
select b.ID,
@EMAILADDRESSID EMAILADDRESSID,
@GROUP_DONOTEMAIL DONOTEMAIL,
@EMAILADDRESS_EMAILADDRESS EMAILADDRESS,
@EMAILADDRESS_EMAILADDRESSTYPECODEID EMAILADDRESSTYPECODEID,
coalesce(b.INFOSOURCECODEID,@INFOSOURCECODEID) INFOSOURCECODEID,
isnull(b.ISPRIMARY,1) ISPRIMARY,
coalesce(b.SEQUENCE,0) SEQUENCE,
b.STARTDATE,
coalesce(b.UPDATEHOUSEHOLD,0) UPDATEHOUSEHOLD
from (select 1 dummy) x
left outer join dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES b on b.BATCHCONSTITUENTUPDATEID = @BATCHROWID
where @EMAILADDRESS_EMAILADDRESS <> ''
for xml raw('ITEM'),type,elements,root('EMAILADDRESSES'),BINARY BASE64
)
-- BBIS fields for saving constituency, site and security group
declare @CONSTITUENCIES xml, @CONSTITUENT_SITES xml, @SECURITYATTRIBUTES xml;
set @CONSTITUENCIES = (
select b.ID,
case when @CUBFLAG = 0 then @CONSTITUENCYCODEID
else b.CONSTITUENCYCODEID end as CONSTITUENCYCODEID,
isnull(b.DATEFROM, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)) DATEFROM,
b.DATETO DATETO,
b.ORIGINALCONSTITUENCYID ORIGINALCONSTITUENCYID
from (select 1 dummy) x
left outer join dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES b on b.BATCHCONSTITUENTUPDATEID = @BATCHROWID
where @CONSTITUENCYCODEID is not null
for xml raw('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64
)
set @CONSTITUENT_SITES = (
select b.ID,
@SITEID SITEID
from (select 1 dummy) x
left outer join dbo.BATCHCONSTITUENTUPDATESITES b on b.BATCHCONSTITUENTUPDATEID = @BATCHROWID
where @SITEID is not null
for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
)
set @SECURITYATTRIBUTES = (
select b.ID,
@CONSTITUENTSECURITYGROUPID CONSTIT_SECURITY_ATTRIBUTEID
from (select 1 dummy) x
left outer join dbo.BATCHCONSTITUENTUPDATESECURITYATTRIBUTES b on b.BATCHCONSTITUENTUPDATEID = @BATCHROWID
where @CONSTITUENTSECURITYGROUPID is not null
for xml raw('ITEM'),type,elements,root('SECURITYATTRIBUTES'),BINARY BASE64
)
if @CUBFLAG = 0
begin
-- Constituent is not yet in CUB for this batch. Use Add USP.
declare @CUB_SEQUENCE int
select @CUB_SEQUENCE = isnull(max(SEQUENCE),0)+1
from dbo.BATCHCONSTITUENTUPDATE
where BATCHID = @BATCHID;
exec dbo.USP_ADD_BATCHCONSTITUENTUPDATEBATCHROW
@ID = @BATCHROWID,
@BATCHID = @BATCHID,
@CHANGEAGENTID = @CHANGEAGENTID,
@SEQUENCE = @CUB_SEQUENCE,
@PRIMARYRECORDID = @CONSTITUENTID,
@CONSTITUENTTYPECODE = @CONSTITUENTTYPECODE,
@BIRTHDATE = @BIRTHDATE,
@FIRSTNAME = @FIRSTNAME,
@GENDERCODE = @GENDERCODE,
@GIVESANONYMOUSLY = @GROUP_GIVESANONYMOUSLY,
@KEYNAME = @LASTNAME,
--@LOOKUP_ID = @LOOKUP_ID,
@MAIDENNAME = @MAIDENNAME,
@MIDDLENAME = @MIDDLENAME,
@NICKNAME = @NICKNAME,
@SUFFIXCODEID = @SUFFIXCODEID,
@TITLECODEID = @TITLECODEID,
@WEBADDRESS = @WEBADDRESS,
/*
@ALTERNATELOOKUPIDS = @ALTERNATELOOKUPIDS,
*/
@SECURITYATTRIBUTES = @SECURITYATTRIBUTES,
/*
@DECEASED = @DECEASED,
@DECEASEDDATE = @DECEASEDDATE,
@CONSTITUENT_SITEID = @CONSTITUENT_SITEID,
@INTERESTS = @INTERESTS,
@PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
*/
@ADDRESSES = @ADDRESSES,
@PHONES = @PHONES,
@EMAILADDRESSES = @EMAILADDRESSES,
@CONSTITUENCIES = @CONSTITUENCIES,
@GROUPTYPEID = @GROUP_GROUPTYPEID,
@GROUPDESCRIPTION = @GROUP_DESCRIPTION,
@GROUPSTARTDATE = @GROUP_STARTDATE,
@ORG_INDUSTRYCODEID = @INDUSTRYCODEID,
@ORG_NUMEMPLOYEES = @NUMEMPLOYEES,
@ORG_NUMSUBSIDIARIES = @NUMSUBSIDIARIES,
@ORG_PARENTCORPID = @PARENTCORPID,
@MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
@SPOUSE_ID = @SPOUSEID,
@SPOUSE_TITLECODEID = @SPOUSE_TITLECODEID,
@SPOUSE_FIRSTNAME = @SPOUSE_FIRSTNAME,
@SPOUSE_NICKNAME = @SPOUSE_NICKNAME,
@SPOUSE_MIDDLENAME = @SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME = @SPOUSE_MAIDENNAME,
@SPOUSE_LASTNAME = @SPOUSE_LASTNAME,
@SPOUSE_SUFFIXCODEID = @SPOUSE_SUFFIXCODEID,
@SPOUSE_BIRTHDATE = @SPOUSE_BIRTHDATE,
@SPOUSE_GENDERCODE = @SPOUSE_GENDERCODE,
@SPOUSE_LOOKUPID = @SPOUSE_LOOKUPID,
@SPOUSE_RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID,
@BUSINESS_ID = @BUSINESSID,
@BUSINESS_ADDRESSBLOCK = @BUSINESS_ADDRESSBLOCK,
@BUSINESS_ADDRESSTYPECODEID = @BUSINESS_ADDRESSTYPECODEID,
@BUSINESS_CART = @BUSINESS_CART,
@BUSINESS_CITY = @BUSINESS_CITY,
@BUSINESS_COUNTRYID = @BUSINESS_COUNTRYID,
@BUSINESS_DONOTMAIL = @BUSINESS_DONOTMAIL,
@BUSINESS_DONOTMAILREASONCODEID = @BUSINESS_DONOTMAILREASONCODEID,
@BUSINESS_DPC = @BUSINESS_DPC,
--@BUSINESS_EMAILADDRESS = @BUSINESS_EMAILADDRESS,
--@BUSINESS_EMAILADDRESSTYPECODEID = @BUSINESS_EMAILADDRESSTYPECODEID,
--@BUSINESS_INDUSTRYCODEID = @BUSINESS_INDUSTRYCODEID,
--@BUSINESS_LOOKUPID = @BUSINESS_LOOKUPID,
@BUSINESS_LOT = @BUSINESS_LOT,
@BUSINESS_NAME = @BUSINESS_NAME,
--@BUSINESS_NUMEMPLOYEES = @BUSINESS_NUMEMPLOYEES,
--@BUSINESS_NUMSUBSIDIARIES = @BUSINESS_NUMSUBSIDIARIES,
--@BUSINESS_PARENTCORPID = @BUSINESS_PARENTCORPID,
@BUSINESS_PHONENUMBER = @BUSINESS_NUMBER,
--@BUSINESS_PHONE_COUNTRYID = @BUSINESS_PHONE_COUNTRYID,
@BUSINESS_PHONETYPECODEID = @BUSINESS_PHONETYPECODEID,
@BUSINESS_RELATIONSHIPTYPECODEID = @BUSINESS_RELATIONSHIPTYPECODEID,
@BUSINESS_RECIPROCALTYPECODEID = @BUSINESS_RECIPROCALTYPECODEID,
@BUSINESS_STATEID = @BUSINESS_STATEID,
--@BUSINESS_WEBADDRESS = @BUSINESS_WEBADDRESS,
@BUSINESS_POSTCODE = @BUSINESS_POSTCODE,
/*
@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST = @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST,
@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST = @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST,
@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST = @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST,
@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST = @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST,
*/
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR,
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR,
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID = @BUSINESS_PRIMARYRECOGNITIONTYPECODEID,
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
/*
@BUSINESS_EMAILADDRESSSTARTDATE = @BUSINESS_EMAILADDRESSSTARTDATE,
*/
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR,
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID = @SPOUSE_PRIMARYRECOGNITIONTYPECODEID,
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
@CONSTITUENT_SITES = @CONSTITUENT_SITES,
/*
@SPOUSE_ALTERNATELOOKUPIDS = @SPOUSE_ALTERNATELOOKUPIDS,
@SPOUSE_IMPORTLOOKUPID = @SPOUSE_IMPORTLOOKUPID,
@ROWFROMBATCHUI = @ROWFROMBATCHUI,
@BBNCTRANID = @BBNCTRANID,
@PAGEID = @PAGEID,
@PAGENAME = @PAGENAME,
@BBNCUSERID = @BBNCUSERID,
@REMOVESPOUSE = @REMOVESPOUSE,
@DUPLICATERECORDID = @DUPLICATERECORDID
@SOCIALMEDIAACCOUNTS = @SOCIALMEDIAACCOUNTS,
@NETCOMMUNITYTRANSACTIONPROCESSORID = @NETCOMMUNITYTRANSACTIONPROCESSORID,
@BBNCID = @BBNCID,
*/
@NAMEFORMATS = @NAMEFORMATS,
@ORIGINAL_KEYNAME = @ORIGINAL_KEYNAME,
@ORIGINAL_FIRSTNAME = @ORIGINAL_FIRSTNAME,
@GENDERCODEID = @GENDERCODEID,
@SPOUSE_GENDERCODEID = @SPOUSE_GENDERCODEID
end
else
-- Constituent is already in CUB for this batch. Use Edit USP.
exec dbo.USP_EDITSAVE_BATCHCONSTITUENTUPDATEBATCHROW_4
@ID = @BATCHROWID,
@CHANGEAGENTID = @CHANGEAGENTID,
@SEQUENCE = null,
@PRIMARYRECORDID = @CONSTITUENTID,
@CONSTITUENTTYPECODE = @CONSTITUENTTYPECODE,
@BIRTHDATE = @BIRTHDATE,
@FIRSTNAME = @FIRSTNAME,
@GENDERCODE = @GENDERCODE,
@GIVESANONYMOUSLY = @GROUP_GIVESANONYMOUSLY,
@KEYNAME = @LASTNAME,
@LOOKUP_ID = null,
@MAIDENNAME = @MAIDENNAME,
@MIDDLENAME = @MIDDLENAME,
@NICKNAME = @NICKNAME,
@SUFFIXCODEID = @SUFFIXCODEID,
@TITLECODEID = @TITLECODEID,
@WEBADDRESS = @WEBADDRESS,
@ALTERNATELOOKUPIDS = null,
@SECURITYATTRIBUTES = @SECURITYATTRIBUTES,
@DECEASED = null,
@DECEASEDDATE = null,
@CONSTITUENT_SITEID = null,
@INTERESTS = null,
@PROSPECTMANAGERFUNDRAISERID = null,
@ADDRESSES = @ADDRESSES,
@PHONES = @PHONES,
@EMAILADDRESSES = @EMAILADDRESSES,
@CONSTITUENCIES = @CONSTITUENCIES,
@GROUPTYPEID = @GROUP_GROUPTYPEID,
@GROUPDESCRIPTION = @GROUP_DESCRIPTION,
@GROUPSTARTDATE = @GROUP_STARTDATE,
@ORG_INDUSTRYCODEID = @INDUSTRYCODEID,
@ORG_NUMEMPLOYEES = @NUMEMPLOYEES,
@ORG_NUMSUBSIDIARIES = @NUMSUBSIDIARIES,
@ORG_PARENTCORPID = @PARENTCORPID,
@MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
@SPOUSE_ID = @SPOUSEID,
@SPOUSE_TITLECODEID = @SPOUSE_TITLECODEID,
@SPOUSE_FIRSTNAME = @SPOUSE_FIRSTNAME,
@SPOUSE_NICKNAME = @SPOUSE_NICKNAME,
@SPOUSE_MIDDLENAME = @SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME = @SPOUSE_MAIDENNAME,
@SPOUSE_LASTNAME = @SPOUSE_LASTNAME,
@SPOUSE_SUFFIXCODEID = @SPOUSE_SUFFIXCODEID,
@SPOUSE_BIRTHDATE = @SPOUSE_BIRTHDATE,
@SPOUSE_GENDERCODE = @SPOUSE_GENDERCODE,
@SPOUSE_LOOKUPID = @SPOUSE_LOOKUPID,
@SPOUSE_RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID,
@BUSINESS_ID = @BUSINESSID,
@BUSINESS_ADDRESSBLOCK = @BUSINESS_ADDRESSBLOCK,
@BUSINESS_ADDRESSTYPECODEID = @BUSINESS_ADDRESSTYPECODEID,
@BUSINESS_CART = @BUSINESS_CART,
@BUSINESS_CITY = @BUSINESS_CITY,
@BUSINESS_COUNTRYID = @BUSINESS_COUNTRYID,
@BUSINESS_DONOTMAIL = @BUSINESS_DONOTMAIL,
@BUSINESS_DONOTMAILREASONCODEID = @BUSINESS_DONOTMAILREASONCODEID,
@BUSINESS_DPC = @BUSINESS_DPC,
@BUSINESS_EMAILADDRESS = null,
@BUSINESS_EMAILADDRESSTYPECODEID = null,
@BUSINESS_INDUSTRYCODEID = null,
@BUSINESS_LOOKUPID = null,
@BUSINESS_LOT = @BUSINESS_LOT,
@BUSINESS_NAME = @BUSINESS_NAME,
@BUSINESS_NUMEMPLOYEES = null,
@BUSINESS_NUMSUBSIDIARIES = null,
@BUSINESS_PARENTCORPID = null,
@BUSINESS_PHONENUMBER = @BUSINESS_NUMBER,
@BUSINESS_PHONE_COUNTRYID = null,
@BUSINESS_PHONETYPECODEID = @BUSINESS_PHONETYPECODEID,
@BUSINESS_RELATIONSHIPTYPECODEID = @BUSINESS_RELATIONSHIPTYPECODEID,
@BUSINESS_RECIPROCALTYPECODEID = @BUSINESS_RECIPROCALTYPECODEID,
@BUSINESS_STATEID = @BUSINESS_STATEID,
@BUSINESS_WEBADDRESS = null,
@BUSINESS_POSTCODE = @BUSINESS_POSTCODE,
@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST = @SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST = @SPOUSE_RECIPROCALTYPECODEID,
@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST = @BUSINESS_RELATIONSHIPTYPECODEID,
@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST = @BUSINESS_RECIPROCALTYPECODEID,
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR,
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR,
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID = @BUSINESS_PRIMARYRECOGNITIONTYPECODEID,
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
@BUSINESS_EMAILADDRESSSTARTDATE = null,
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR,
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID = @SPOUSE_PRIMARYRECOGNITIONTYPECODEID,
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
@CONSTITUENT_SITES = @CONSTITUENT_SITES,
@SPOUSE_ALTERNATELOOKUPIDS = null,
@SPOUSE_IMPORTLOOKUPID = null,
@ROWFROMBATCHUI = null,
@BBNCTRANID = null,
@PAGEID = null,
@PAGENAME = null,
@BBNCUSERID = null,
@REMOVESPOUSE = null,
@DUPLICATERECORDID = null,
@SOCIALMEDIAACCOUNTS = null,
@RELATIONSHIPS = null,
@NETCOMMUNITYTRANSACTIONPROCESSORID = null,
@BBNCID = null,
@NAMEFORMATS = @NAMEFORMATS,
@ORIGINAL_KEYNAME = @ORIGINAL_KEYNAME,
@ORIGINAL_FIRSTNAME = @ORIGINAL_FIRSTNAME,
@SOLICITCODES = null,
@GENDERCODEID = @GENDERCODEID,
@SPOUSE_GENDERCODEID = @SPOUSE_GENDERCODEID
end
else
begin
-- new constituent, batchrevenueconstituent
select
@ISORGANIZATION = ISORGANIZATION,
@ISGROUP = ISGROUP,
@ISHOUSEHOLD = case when GROUPTYPECODE = 0 then 1 else 0 end
from dbo.BATCHREVENUECONSTITUENT
where ID = @CONSTITUENTID
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,
[SITEID] = @SITEID,
[CONSTITUENTSECURITYGROUPID] = @CONSTITUENTSECURITYGROUPID
where ID = @CONSTITUENTID;
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,
[SITEID] = @SITEID,
[CONSTITUENTSECURITYGROUPID] = @CONSTITUENTSECURITYGROUPID
where ID = @CONSTITUENTID;
-- 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 = @CONSTITUENTID)
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 = @CONSTITUENTID)
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 = @CONSTITUENTID;
-- 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 = @CONSTITUENTID
delete from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
where
MEMBERID in (select BATCHREVENUECONSTITUENTID from @CONSTITUENTSTOREMOVE) and
GROUPID = @CONSTITUENTID
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 = @CONSTITUENTID
where GM.ISPRIMARY <> M.ISPRIMARY
-- Add new members
insert into dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
(
ID,
GROUPID,
MEMBERID,
ISPRIMARY,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
newid(),
@CONSTITUENTID,
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 = @CONSTITUENTID
)
end
else
begin
-- Handle household members
-- Update primary member if needed
declare @PRIMARYMEMBERBATCHREVENUECONSTITUENTID uniqueidentifier
exec dbo.USP_BATCHREVENUE_UPDATEHOUSEHOLDMEMBER @PRIMARYMEMBERBATCHREVENUECONSTITUENTID output,
@CONSTITUENTID, 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,
@CONSTITUENTID, 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 = @CONSTITUENTID)
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,
[GENDERCODEID]=@GENDERCODEID,
[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,
[SITEID] = @SITEID,
[CONSTITUENTSECURITYGROUPID] = @CONSTITUENTSECURITYGROUPID,
[NAMEFORMATS] = @NAMEFORMATS
where ID = @CONSTITUENTID;
-------------- 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 = @CONSTITUENTID
--If their is a spouse create the relationship
if @ISSPOUSE = 1
begin
if @SPOUSE_GENDERCODE is null
set @SPOUSE_GENDERCODE = 0;
if @BATCHSPOUSEID is null
begin
set @BATCHSPOUSEID = newID();
insert into dbo.BATCHREVENUECONSTITUENT
(
[ID],
[ISORGANIZATION],
[ISGROUP],
[EXISTINGCONSTITUENTID],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[GENDERCODEID],
[BIRTHDATE],
[MARITALSTATUSCODEID],
[CURRENTAPPUSERID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[SITEID],
[CONSTITUENTSECURITYGROUPID]
)
values
(
@BATCHSPOUSEID,
0,
0,
@SPOUSEID,
@SPOUSE_LASTNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_GENDERCODEID,
@SPOUSE_BIRTHDATE,
@MARITALSTATUSCODEID,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@SITEID,
@CONSTITUENTSECURITYGROUPID
);
insert into dbo.BATCHREVENUECONSTITUENTRELATION
(
[CONSTITUENTID],
[RELATIONID],
[COPYPRIMARYINFORMATION],
[RELATIONSHIPTYPECODEID],
[RECIPROCALTYPECODEID],
[STARTDATE],
[PRIMARYRELATIONSHIPEXISTS],
[PRIMARYMATCHFACTOR],
[PRIMARYRECOGNITIONTYPECODEID],
[RECIPROCALRELATIONSHIPEXISTS],
[RECIPROCALMATCHFACTOR],
[RECIPROCALRECOGNITIONTYPECODEID],
[ISSPOUSE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@CONSTITUENTID,
@BATCHSPOUSEID,
@COPYPRIMARYINFORMATION,
@SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID,
@SPOUSE_STARTDATE,
ISNULL(@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,0),
ISNULL(@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,0),
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID,
ISNULL(@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,0),
ISNULL(@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR,0),
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
@ISSPOUSERELATIONSHIP,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- Create the household
exec dbo.USP_BATCHREVENUE_ADDINDIVIDUALHOUSEHOLD @CONSTITUENTID, @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,
[GENDERCODEID]=@SPOUSE_GENDERCODEID,
[BIRTHDATE]=@SPOUSE_BIRTHDATE,
[MARITALSTATUSCODEID]=@MARITALSTATUSCODEID,
[CHANGEDBYID]=@CHANGEAGENTID,
[DATECHANGED]=@CURRENTDATE,
[SITEID] = @SITEID,
[CONSTITUENTSECURITYGROUPID] = @CONSTITUENTSECURITYGROUPID
where ID = @BATCHSPOUSEID;
update dbo.BATCHREVENUECONSTITUENTRELATION
set [COPYPRIMARYINFORMATION]=@COPYPRIMARYINFORMATION,
[RELATIONSHIPTYPECODEID]=@SPOUSE_RELATIONSHIPTYPECODEID,
[RECIPROCALTYPECODEID]=@SPOUSE_RECIPROCALTYPECODEID,
[STARTDATE]=@SPOUSE_STARTDATE,
[PRIMARYRELATIONSHIPEXISTS]=ISNULL(@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,0),
[PRIMARYMATCHFACTOR]=ISNULL(@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,0),
[PRIMARYRECOGNITIONTYPECODEID]=@SPOUSE_PRIMARYRECOGNITIONTYPECODEID,
[RECIPROCALRELATIONSHIPEXISTS]=ISNULL(@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,0),
[RECIPROCALMATCHFACTOR]=ISNULL(@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR,0),
[RECIPROCALRECOGNITIONTYPECODEID]=@SPOUSE_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 @CONSTITUENTID, @BATCHSPOUSEID, @HOUSEHOLDCOPYPRIMARYCONTACTINFO, @CURRENTAPPUSERID, @CHANGEAGENTID;
else
begin
-- Otherwise, update the existing household
declare @HOUSEHOLDID uniqueidentifier, @HOUSEHOLDNAME nvarchar(100), @USEHOUSEHOLDID bit
exec dbo.USP_BATCHREVENUE_GETHOUSEHOLDNAMEORID @CONSTITUENTID, @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 = @CONSTITUENTID
if @CURRENTHOUSEHOLDCOPYPRIMARYCONTACTINFO <> @HOUSEHOLDCOPYPRIMARYCONTACTINFO
update dbo.BATCHREVENUECONSTITUENTGROUPMEMBER set
COPYMEMBERCONTACTINFOTOGROUP = @HOUSEHOLDCOPYPRIMARYCONTACTINFO,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where GROUPID = @BATCHHOUSEHOLDID and MEMBERID = @CONSTITUENTID
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;
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],
[SITEID],
[CONSTITUENTSECURITYGROUPID]
)
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,
@SITEID,
@CONSTITUENTSECURITYGROUPID
);
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
(
@CONSTITUENTID,
@BATCHBUSINESSID,
1,
@BUSINESS_RELATIONSHIPTYPECODEID,
@BUSINESS_RECIPROCALTYPECODEID,
@ISCONTACT,
@ISPRIMARYCONTACT,
@CONTACTTYPECODEID,
@BUSINESS_STARTDATE,
ISNULL(@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,0),
ISNULL(@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR,0),
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID,
ISNULL(@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,0),
ISNULL(@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR,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,
[SITEID] = @SITEID,
[CONSTITUENTSECURITYGROUPID] = @CONSTITUENTSECURITYGROUPID
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_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,0),
[PRIMARYMATCHFACTOR]=ISNULL(@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR,0),
[PRIMARYRECOGNITIONTYPECODEID]=@BUSINESS_PRIMARYRECOGNITIONTYPECODEID,
[RECIPROCALRELATIONSHIPEXISTS]=ISNULL(@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,0),
[RECIPROCALMATCHFACTOR]=ISNULL(@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR,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;