USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENT
The save procedure used by the edit dataform template "Revenue Batch Constituent Edit Form".
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_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_OMITFROMVALIDATION | bit | IN | Omit from validation |
| @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_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 from validation |
| @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 | No. of employees |
| @NUMSUBSIDIARIES | int | IN | No. of subsidiary orgs |
| @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 |
| @PRIMARYRELATIONSHIPEXISTS | bit | IN | Apply recognition credit to individual for constituent's payments |
| @PRIMARYMATCHFACTOR | decimal(5, 2) | IN | Primary recognition credit factor |
| @RECIPROCALRELATIONSHIPEXISTS | bit | IN | Apply recognition credit to constituent for individual's payments |
| @RECIPROCALMATCHFACTOR | decimal(5, 2) | IN | Reciprocal recognition credit factor |
| @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_OMITFROMVALIDATION | bit | IN | Omit from validation |
| @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 | This organization will match individual's contributions |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENT
(
@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_COUNTRYID uniqueidentifier,
@ADDRESS_STATEID uniqueidentifier,
@ADDRESS_ADDRESSBLOCK nvarchar(150),
@ADDRESS_CITY nvarchar(50),
@ADDRESS_POSTCODE nvarchar(12),
-- 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_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),
--Spouse 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,
@PRIMARYRELATIONSHIPEXISTS bit,
@PRIMARYMATCHFACTOR decimal(5,2),
@RECIPROCALRELATIONSHIPEXISTS bit,
@RECIPROCALMATCHFACTOR decimal(5,2),
--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,
-- 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
) as begin
set nocount on;
declare @CURRENTDATE datetime;
declare @KEYNAME nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(50);
declare @contextCache varbinary(128);
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
begin try
declare @ISEXISTINGCONSTITUENT bit;
declare @ISORGANIZATION bit;
set @ISEXISTINGCONSTITUENT = 0;
set @ISORGANIZATION = 0;
select @ISEXISTINGCONSTITUENT=1, @ISORGANIZATION=ISORGANIZATION from dbo.CONSTITUENT where CONSTITUENT.ID = @ID;
if @ISEXISTINGCONSTITUENT= 1
begin
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,
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
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
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
update dbo.ADDRESS
set 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,
CHANGEDBYID=@CHANGEAGENTID,
DATECHANGED=@CURRENTDATE
where ADDRESS.CONSTITUENTID = @ID and ISPRIMARY = 1
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)
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)
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;
else
begin
select @ISORGANIZATION=ISORGANIZATION 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,
[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
where ID = @ID;
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,
[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
--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
select @PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID
from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD
where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SPOUSE_RELATIONSHIPTYPECODEID
select @RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID
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],
[EXISTINGCONSTITUENTID],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[MARITALSTATUSCODEID],
[COUNTRYID],
[CURRENTAPPUSERID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@BATCHSPOUSEID,
0,
@SPOUSEID,
@SPOUSE_LASTNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_BIRTHDATE,
@MARITALSTATUSCODEID,
dbo.UFN_COUNTRY_GETDEFAULT(),
@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,
@PRIMARYRELATIONSHIPEXISTS,
@PRIMARYMATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODEID,
@RECIPROCALRELATIONSHIPEXISTS,
@RECIPROCALMATCHFACTOR,
@RECIPROCALRECOGNITIONTYPECODEID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
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,
[PRIMARYRELATIONSHIPEXISTS]=@PRIMARYRELATIONSHIPEXISTS,
[PRIMARYMATCHFACTOR]=@PRIMARYMATCHFACTOR,
[PRIMARYRECOGNITIONTYPECODEID]=@PRIMARYRECOGNITIONTYPECODEID,
[RECIPROCALRELATIONSHIPEXISTS]=@RECIPROCALRELATIONSHIPEXISTS,
[RECIPROCALMATCHFACTOR]=@RECIPROCALMATCHFACTOR,
[RECIPROCALRECOGNITIONTYPECODEID]=@RECIPROCALRECOGNITIONTYPECODEID,
[CHANGEDBYID]=@CHANGEAGENTID,
[DATECHANGED]=@CURRENTDATE
where ID = @BATCHSPOUSERELATIONID;
end
end
else
begin
if @BATCHSPOUSERELATIONID is not null
exec dbo.USP_BATCHREVENUECONSTITUENTRELATION_DELETEBYID_WITHCHANGEAGENTID @BATCHSPOUSERELATIONID, @CHANGEAGENTID;
if @BATCHSPOUSEID is not null
exec dbo.USP_BATCHREVENUECONSTITUENT_DELETEBYID_WITHCHANGEAGENTID @BATCHSPOUSEID, @CHANGEAGENTID;
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],
[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_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,
[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;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end;