USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENT_2
The save procedure used by the edit dataform template "Revenue Batch Constituent Edit Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @LASTNAME | nvarchar(100) | IN | Last name |
| @ORGANIZATIONNAME | nvarchar(100) | IN | Name |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @MIDDLENAME | nvarchar(50) | IN | Middle name |
| @MAIDENNAME | nvarchar(100) | IN | Maiden name |
| @NICKNAME | nvarchar(50) | IN | Nickname |
| @TITLECODEID | uniqueidentifier | IN | Title |
| @SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @GENDERCODE | tinyint | IN | Gender |
| @BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @ADDRESS_DONOTMAIL | bit | IN | Do not send mail to this address |
| @ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Reason |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | Country |
| @ADDRESS_STATEID | uniqueidentifier | IN | State |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ADDRESS_CITY | nvarchar(50) | IN | City |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ZIP |
| @ADDRESS_UPDATEMATCHINGHOUSEHOLDADDRESSES | bit | IN | Update matching addresses in household |
| @ADDRESS_OMITFROMVALIDATION | bit | IN | Omit this address |
| @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(100) | IN | |
| @ADDRESS_CERTIFICATIONDATA | int | IN | |
| @PHONE_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @MARITALSTATUSCODEID | uniqueidentifier | IN | Marital status |
| @ORGANIZATION_ADDRESSTYPECODEID | nvarchar(50) | IN | Address type |
| @ORGANIZATION_DONOTMAIL | bit | IN | Do not send mail to this address |
| @ORGANIZATION_DONOTMAILREASONCODEID | uniqueidentifier | IN | Reason |
| @ORGANIZATION_COUNTRYID | uniqueidentifier | IN | Country |
| @ORGANIZATION_STATEID | nvarchar(50) | IN | State |
| @ORGANIZATION_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ORGANIZATION_CITY | nvarchar(50) | IN | City |
| @ORGANIZATION_POSTCODE | nvarchar(12) | IN | ZIP |
| @ORGANIZATION_OMITFROMVALIDATION | bit | IN | Omit this address |
| @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(100) | IN | |
| @ORGANIZATION_CERTIFICATIONDATA | int | IN | |
| @ORGANIZATION_PHONETYPECODEID | nvarchar(50) | IN | Phone type |
| @ORGANIZATION_NUMBER | nvarchar(100) | IN | Phone number |
| @ORGANIZATION_EMAILADDRESSTYPECODEID | nvarchar(50) | IN | Email type |
| @ORGANIZATION_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @WEBADDRESS | UDT_WEBADDRESS | IN | Website |
| @INDUSTRYCODEID | nvarchar(100) | IN | Industry |
| @NUMEMPLOYEES | int | IN | Number of employees |
| @NUMSUBSIDIARIES | int | IN | Number of subsidiaries |
| @PARENTCORPID | nvarchar(100) | IN | Parent org |
| @BATCHSPOUSEID | uniqueidentifier | IN | Batch spouse id |
| @BATCHSPOUSERELATIONID | uniqueidentifier | IN | Batch spouse relation id |
| @SPOUSEID | uniqueidentifier | IN | Full name |
| @EXISTINGSPOUSE | bit | IN | Existing constituent |
| @SPOUSE_LASTNAME | nvarchar(100) | IN | Last Name |
| @SPOUSE_FIRSTNAME | nvarchar(50) | IN | First Name |
| @SPOUSE_MIDDLENAME | nvarchar(50) | IN | Middle Name |
| @SPOUSE_MAIDENNAME | nvarchar(100) | IN | Maiden Name |
| @SPOUSE_NICKNAME | nvarchar(50) | IN | Nickname |
| @SPOUSE_TITLECODEID | uniqueidentifier | IN | Title |
| @SPOUSE_SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @SPOUSE_GENDERCODE | tinyint | IN | Gender |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @COPYPRIMARYINFORMATION | bit | IN | Copy primary information |
| @SPOUSE_STARTDATE | datetime | IN | Start date |
| @ISSPOUSERELATIONSHIP | bit | IN | Is spouse relationship |
| @BATCHBUSINESSID | uniqueidentifier | IN | Batch organization id |
| @BATCHBUSINESSRELATIONID | uniqueidentifier | IN | Batch organization relation id |
| @BUSINESSID | uniqueidentifier | IN | Org name |
| @EXISTINGBUSINESS | bit | IN | Existing constituent |
| @BUSINESS_NAME | nvarchar(100) | IN | Org name |
| @BUSINESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @BUSINESS_COUNTRYID | uniqueidentifier | IN | Country |
| @BUSINESS_STATEID | uniqueidentifier | IN | State |
| @BUSINESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @BUSINESS_CITY | nvarchar(50) | IN | City |
| @BUSINESS_POSTCODE | nvarchar(12) | IN | ZIP |
| @BUSINESS_DONOTMAIL | bit | IN | Do not send mail to this address |
| @BUSINESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Reason |
| @BUSINESS_OMITFROMVALIDATION | bit | IN | Omit this address |
| @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(100) | IN | |
| @BUSINESS_CERTIFICATIONDATA | int | IN | |
| @BUSINESS_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @BUSINESS_NUMBER | nvarchar(100) | IN | Phone number |
| @BUSINESS_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @BUSINESS_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @BUSINESS_STARTDATE | datetime | IN | Start date |
| @ISCONTACT | bit | IN | Is contact |
| @ISPRIMARYCONTACT | bit | IN | Primary contact |
| @CONTACTTYPECODEID | uniqueidentifier | IN | Contact type |
| @POSITION | nvarchar(50) | IN | Position |
| @ISMATCHINGGIFTRELATIONSHIP | bit | IN | Matching gift relationship |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @GROUP_NAME | nvarchar(100) | IN | Name |
| @GROUP_DESCRIPTION | nvarchar(300) | IN | Description |
| @GROUP_GIVESANONYMOUSLY | bit | IN | Group gives anonymously |
| @GROUP_ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @GROUP_COUNTRYID | uniqueidentifier | IN | Country |
| @GROUP_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @GROUP_CITY | nvarchar(50) | IN | City |
| @GROUP_STATEID | uniqueidentifier | IN | State |
| @GROUP_OMITFROMVALIDATION | bit | IN | Omit this address |
| @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(100) | IN | |
| @GROUP_CERTIFICATIONDATA | int | IN | |
| @GROUP_POSTCODE | nvarchar(12) | IN | ZIP/Postal code |
| @GROUP_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @GROUP_NUMBER | nvarchar(100) | IN | Phone number |
| @GROUP_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @GROUP_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @GROUP_WEBADDRESS | UDT_WEBADDRESS | IN | Website |
| @GROUP_DONOTMAIL | bit | IN | Do not send mail to this address |
| @GROUP_DONOTMAILREASONCODEID | uniqueidentifier | IN | Reason |
| @GROUP_UPDATEMATCHINGHOUSEHOLDADDRESSES | bit | IN | Update matching addresses in household |
| @GROUP_GROUPTYPEID | uniqueidentifier | IN | Group type |
| @GROUP_PRIMARYCONTACTID | uniqueidentifier | IN | Primary contact |
| @GROUP_MEMBERS | xml | IN | Members |
| @GROUP_NEWMEMBERSEARCHID | uniqueidentifier | IN | Search for constituents to include in the group |
| @GROUP_STARTDATE | datetime | IN | Start date |
| @HOUSEHOLD_PRIMARYCONTACTID | uniqueidentifier | IN | Full name |
| @HOUSEHOLD_PRIMARYCONTACT_KEYNAME | nvarchar(100) | IN | Last name |
| @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME | nvarchar(50) | IN | First name |
| @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME | nvarchar(50) | IN | Middle name |
| @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID | uniqueidentifier | IN | Title |
| @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT | bit | IN | Copy household contact information |
| @HOUSEHOLD_SECONDMEMBERID | uniqueidentifier | IN | Full name |
| @HOUSEHOLD_SECONDMEMBER_KEYNAME | nvarchar(100) | IN | Last name |
| @HOUSEHOLD_SECONDMEMBER_FIRSTNAME | nvarchar(50) | IN | First name |
| @HOUSEHOLD_SECONDMEMBER_MIDDLENAME | nvarchar(50) | IN | Middle name |
| @HOUSEHOLD_SECONDMEMBER_TITLECODEID | uniqueidentifier | IN | Title |
| @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT | bit | IN | Copy household contact information |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE | datetime | IN | Start date |
| @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE | bit | IN | This is the spouse relationship |
| @HOUSEHOLDCOPYPRIMARYCONTACTINFO | bit | IN | Copy primary contact information to household |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENT_2
(
@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(100),
@ADDRESS_CERTIFICATIONDATA integer,
@PHONE_PHONETYPECODEID uniqueidentifier,
@PHONE_NUMBER nvarchar(100),
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,
@MARITALSTATUSCODEID uniqueidentifier,
@ORGANIZATION_ADDRESSTYPECODEID nvarchar(50),
@ORGANIZATION_DONOTMAIL bit,
@ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier,
@ORGANIZATION_COUNTRYID uniqueidentifier,
@ORGANIZATION_STATEID nvarchar(50),
@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(100),
@ORGANIZATION_CERTIFICATIONDATA integer,
@ORGANIZATION_PHONETYPECODEID nvarchar(50),
@ORGANIZATION_NUMBER nvarchar(100),
@ORGANIZATION_EMAILADDRESSTYPECODEID nvarchar(50),
@ORGANIZATION_EMAILADDRESS dbo.UDT_EMAILADDRESS,
@WEBADDRESS dbo.UDT_WEBADDRESS,
@INDUSTRYCODEID nvarchar(100),
@NUMEMPLOYEES int,
@NUMSUBSIDIARIES int,
@PARENTCORPID nvarchar(100),
--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(100),
@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(50),
@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(100),
@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
) 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, '77AF3E0E-4CE8-4cb5-A88D-EAC148779CC3', @ID) = 0)
raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED', 13, 1);
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;
update dbo.ORGANIZATIONDATA
set INDUSTRYCODEID=@INDUSTRYCODEID,
NUMEMPLOYEES=@NUMEMPLOYEES,
NUMSUBSIDIARIES=@NUMSUBSIDIARIES,
PARENTCORPID=@PARENTCORPID,
CHANGEDBYID=@CHANGEAGENTID,
DATECHANGED=@CURRENTDATE
where ORGANIZATIONDATA.ID = @ID;
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)
update dbo.ADDRESS
set ADDRESSTYPECODEID=@ORGANIZATION_ADDRESSTYPECODEID,
DONOTMAIL=@ORGANIZATION_DONOTMAIL,
DONOTMAILREASONCODEID = @ORGANIZATION_DONOTMAILREASONCODEID,
COUNTRYID=@ORGANIZATION_COUNTRYID,
STATEID=@ORGANIZATION_STATEID,
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
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
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
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
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
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
if @GROUP_PHONETYPECODEID is not null or coalesce(@GROUP_NUMBER, '') <> ''
begin
update dbo.PHONE
set PHONETYPECODEID=@GROUP_PHONETYPECODEID,
NUMBER=@GROUP_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, @GROUP_PHONETYPECODEID, @GROUP_NUMBER, @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,
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, @GROUP_EMAILADDRESSTYPECODEID, @GROUP_EMAILADDRESS, @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
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;
if @@ROWCOUNT = 0
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
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
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
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
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]
)
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
);
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
where ID = @BATCHSPOUSEID;
update dbo.BATCHREVENUECONSTITUENTRELATION
set [COPYPRIMARYINFORMATION]=@COPYPRIMARYINFORMATION,
[RELATIONSHIPTYPECODEID]=@SPOUSE_RELATIONSHIPTYPECODEID,
[RECIPROCALTYPECODEID]=@SPOUSE_RECIPROCALTYPECODEID,
[STARTDATE]=@SPOUSE_STARTDATE,
[ISSPOUSE]=@ISSPOUSERELATIONSHIP,
[PRIMARYRELATIONSHIPEXISTS]=ISNULL(@PRIMARYRELATIONSHIPEXISTS,0),
[PRIMARYMATCHFACTOR]=ISNULL(@PRIMARYMATCHFACTOR,0),
[PRIMARYRECOGNITIONTYPECODEID]=@PRIMARYRECOGNITIONTYPECODEID,
[RECIPROCALRELATIONSHIPEXISTS]=ISNULL(@RECIPROCALRELATIONSHIPEXISTS,0),
[RECIPROCALMATCHFACTOR]=ISNULL(@RECIPROCALMATCHFACTOR,0),
[RECIPROCALRECOGNITIONTYPECODEID]=@RECIPROCALRECOGNITIONTYPECODEID,
[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],
[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,
@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,
[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;