USP_DATAFORMTEMPLATE_ADD_RE7INTEGRATIONFROMRE7DATA
The save procedure used by the add dataform template "RE7 Integration From The Raiser's Edge Data Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CONSTITUENTID | uniqueidentifier | IN | CONSTITUENTID |
| @SYNCBIOGRAPHICAL | bit | IN | SYNCBIOGRAPHICAL |
| @LASTNAME | nvarchar(100) | IN | LASTNAME |
| @FIRSTNAME | nvarchar(50) | IN | FIRSTNAME |
| @MIDDLENAME | nvarchar(50) | IN | MIDDLENAME |
| @DATEOFBIRTH | UDT_FUZZYDATE | IN | DATEOFBIRTH |
| @SYNCADDRESS | bit | IN | SYNCADDRESS |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | ADDRESS_ADDRESSBLOCK |
| @ADDRESS_CITY | nvarchar(50) | IN | ADDRESS_CITY |
| @ADDRESS_STATE | nvarchar(50) | IN | ADDRESS_STATE |
| @ADDRESS_STATE_ABBREV | nvarchar(5) | IN | ADDRESS_STATE_ABBREV |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ADDRESS_POSTCODE |
| @ADDRESS_COUNTRY | nvarchar(50) | IN | ADDRESS_COUNTRY |
| @ADDRESS_COUNTRY_ABBREV | nvarchar(5) | IN | ADDRESS_COUNTRY_ABBREV |
| @ADDRESS_ADDRESSTYPE | nvarchar(50) | IN | ADDRESS_ADDRESSTYPE |
| @SYNCBUSINESS | bit | IN | SYNCBUSINESS |
| @BUSINESS_NAME | nvarchar(100) | IN | BUSINESS_NAME |
| @BUSINESS_ADDRESSBLOCK | nvarchar(150) | IN | BUSINESS_ADDRESSBLOCK |
| @BUSINESS_CITY | nvarchar(50) | IN | BUSINESS_CITY |
| @BUSINESS_STATE | nvarchar(50) | IN | BUSINESS_STATE |
| @BUSINESS_STATE_ABBREV | nvarchar(5) | IN | BUSINESS_STATE_ABBREV |
| @BUSINESS_POSTCODE | nvarchar(12) | IN | BUSINESS_POSTCODE |
| @BUSINESS_COUNTRY | nvarchar(50) | IN | BUSINESS_COUNTRY |
| @BUSINESS_COUNTRY_ABBREV | nvarchar(5) | IN | BUSINESS_COUNTRY_ABBREV |
| @BUSINESS_ADDRESSTYPE | nvarchar(50) | IN | BUSINESS_ADDRESSTYPE |
| @SYNCGIVING | bit | IN | SYNCGIVINGHISTORY |
| @FIRSTGIFTAMOUNT | money | IN | FIRSTGIFTAMOUNT |
| @FIRSTGIFTDATE | datetime | IN | FIRSTGIFTDATE |
| @LATESTGIFTAMOUNT | money | IN | LATESTGIFTAMOUNT |
| @LATESTGIFTDATE | datetime | IN | LATESTGIFTDATE |
| @LARGESTGIFTAMOUNT | money | IN | LARGESTGIFTAMOUNT |
| @LARGESTGIFTDATE | datetime | IN | LARGESTGIFTDATE |
| @TOTALGIFTSGIVEN | int | IN | TOTALGIFTSGIVEN |
| @TOTALGIFTAMOUNT | money | IN | TOTALGIFTAMOUNT |
| @FIRSTGIFTTYPE | nvarchar(100) | IN | FIRSTGIFTTYPE |
| @FIRSTGIFTDESIGNATION | nvarchar(100) | IN | FIRSTGIFTDESIGNATION |
| @LARGESTGIFTTYPE | nvarchar(100) | IN | LARGESTGIFTTYPE |
| @LARGESTGIFTDESIGNATION | nvarchar(100) | IN | LARGESTGIFTDESIGNATION |
| @LATESTGIFTTYPE | nvarchar(100) | IN | LATESTGIFTTYPE |
| @LATESTGIFTDESIGNATION | nvarchar(100) | IN | LATESTGIFTDESIGNATION |
| @NICKNAME | nvarchar(50) | IN | NICKNAME |
| @TITLE1 | nvarchar(60) | IN | TITLE1 |
| @TITLE2 | nvarchar(60) | IN | TITLE2 |
| @SUFFIX1 | nvarchar(60) | IN | SUFFIX1 |
| @SUFFIX2 | nvarchar(60) | IN | SUFFIX2 |
| @GENDER | nvarchar(50) | IN | GENDER |
| @RE7CONSTITUENTID | nvarchar(20) | IN | RE7CONSTITUENTID |
| @RE7RECORDID | int | IN | RE7RECORDID |
| @SYNCPHONES | bit | IN | SYNCPHONES |
| @PHONES | xml | IN | PHONES |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RE7INTEGRATIONFROMRE7DATA (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@SYNCBIOGRAPHICAL bit = 0,
@LASTNAME nvarchar(100) = '',
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@DATEOFBIRTH dbo.UDT_FUZZYDATE = '00000000',
@SYNCADDRESS bit = 0,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@ADDRESS_CITY nvarchar(50) = '',
@ADDRESS_STATE nvarchar(50) = '',
@ADDRESS_STATE_ABBREV nvarchar(5) = '',
@ADDRESS_POSTCODE nvarchar(12) = '',
@ADDRESS_COUNTRY nvarchar(50) = '',
@ADDRESS_COUNTRY_ABBREV nvarchar(5) = '',
@ADDRESS_ADDRESSTYPE nvarchar(50) = '',
@SYNCBUSINESS bit = 0,
@BUSINESS_NAME nvarchar(100) = '',
@BUSINESS_ADDRESSBLOCK nvarchar(150) = '',
@BUSINESS_CITY nvarchar(50) = '',
@BUSINESS_STATE nvarchar(50) = null,
@BUSINESS_STATE_ABBREV nvarchar(5) = '',
@BUSINESS_POSTCODE nvarchar(12) = '',
@BUSINESS_COUNTRY nvarchar(50) = '',
@BUSINESS_COUNTRY_ABBREV nvarchar(5) = '',
@BUSINESS_ADDRESSTYPE nvarchar(50) = '',
@SYNCGIVING bit = 0,
@FIRSTGIFTAMOUNT money = 0,
@FIRSTGIFTDATE datetime = null,
@LATESTGIFTAMOUNT money = 0,
@LATESTGIFTDATE datetime = null,
@LARGESTGIFTAMOUNT money = 0,
@LARGESTGIFTDATE datetime = null,
@TOTALGIFTSGIVEN int = 0,
@TOTALGIFTAMOUNT money = 0,
@FIRSTGIFTTYPE nvarchar(100) = null,
@FIRSTGIFTDESIGNATION nvarchar(100) = null,
@LARGESTGIFTTYPE nvarchar(100) = null,
@LARGESTGIFTDESIGNATION nvarchar(100) = null,
@LATESTGIFTTYPE nvarchar(100) = null,
@LATESTGIFTDESIGNATION nvarchar(100) = null,
@NICKNAME nvarchar(50) = null,
@TITLE1 nvarchar(60) = null,
@TITLE2 nvarchar(60) = null,
@SUFFIX1 nvarchar(60) = null,
@SUFFIX2 nvarchar(60) = null,
@GENDER nvarchar(50) = null,
@RE7CONSTITUENTID nvarchar(20) = null,
@RE7RECORDID int = 0,
@SYNCPHONES bit = 0,
@PHONES xml = null
) as
set nocount on;
set @ID = @CONSTITUENTID;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
-- Update Biographical Information
if @SYNCBIOGRAPHICAL = 1 and
@LASTNAME != ''
begin
-- Get title 1 type code
declare @TITLECODEID uniqueidentifier
if len(@TITLE1) > 0 begin
select
@TITLECODEID = TITLECODE.ID
from
dbo.TITLECODE
where
TITLECODE.DESCRIPTION = @TITLE1
if @TITLECODEID is null
begin
exec dbo.USP_TITLECODE_CREATEENTRY
@TITLE1,
1,
null,
@CHANGEAGENTID,
@TITLECODEID output
end
end
-- Get title 2 type code
declare @TITLE2CODEID uniqueidentifier
if len(@TITLE2) > 0 begin
select
@TITLE2CODEID = TITLECODE.ID
from
dbo.TITLECODE
where
TITLECODE.DESCRIPTION = @TITLE2
if @TITLE2CODEID is null
begin
exec dbo.USP_TITLECODE_CREATEENTRY
@TITLE2,
1,
null,
@CHANGEAGENTID,
@TITLE2CODEID output
end
end
-- Get suffix 1 type code
declare @SUFFIXCODEID uniqueidentifier
if len(@SUFFIX1) > 0 begin
select
@SUFFIXCODEID = SUFFIXCODE.ID
from
dbo.SUFFIXCODE
where
SUFFIXCODE.DESCRIPTION = @SUFFIX1
if @SUFFIXCODEID is null
begin
exec dbo.USP_SUFFIXCODE_CREATEENTRY
@SUFFIX1,
1,
null,
@CHANGEAGENTID,
@SUFFIXCODEID output
end
end
-- Get suffix 2 type code
declare @SUFFIX2CODEID uniqueidentifier
if len(@SUFFIX2) > 0 begin
select
@SUFFIX2CODEID = SUFFIXCODE.ID
from
dbo.SUFFIXCODE
where
SUFFIXCODE.DESCRIPTION = @SUFFIX2
if @SUFFIX2CODEID is null
begin
exec dbo.USP_SUFFIXCODE_CREATEENTRY
@SUFFIX2,
1,
null,
@CHANGEAGENTID,
@SUFFIX2CODEID output
end
end
--update existing constituent
if (select count(ID) from dbo.CONSTITUENT where CONSTITUENT.ID = @ID) > 0
begin
-- Get alternate lookup ID type code for old RP lookup ID
declare @OLDRPTYPECODEID uniqueidentifier
select
@OLDRPTYPECODEID = ALTERNATELOOKUPIDTYPECODE.ID
from
dbo.ALTERNATELOOKUPIDTYPECODE
where
ALTERNATELOOKUPIDTYPECODE.DESCRIPTION = 'Original RP Lookup ID'
if @OLDRPTYPECODEID is null
begin
exec dbo.USP_ALTERNATELOOKUPIDTYPECODE_CREATEENTRY
'Original RP Lookup ID',
1,
null,
@CHANGEAGENTID,
@OLDRPTYPECODEID output
end
-- Insert old lookup ID into alternate lookup ID
if not exists(select 1 from ALTERNATELOOKUPID where CONSTITUENTID = @ID and ALTERNATELOOKUPIDTYPECODEID = @OLDRPTYPECODEID)
begin
declare @OLDLOOKUPID nvarchar(100)
select @OLDLOOKUPID = LOOKUPID from dbo.CONSTITUENT where ID = @ID
if @OLDLOOKUPID <> @RE7CONSTITUENTID
insert into dbo.ALTERNATELOOKUPID(
CONSTITUENTID,
ALTERNATELOOKUPIDTYPECODEID,
ALTERNATELOOKUPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@ID,
@OLDRPTYPECODEID,
@OLDLOOKUPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
update
dbo.CONSTITUENT
set
KEYNAME = @LASTNAME,
FIRSTNAME = @FIRSTNAME,
MIDDLENAME = @MIDDLENAME,
NICKNAME = @NICKNAME,
BIRTHDATE = @DATEOFBIRTH,
GENDERCODE = case when @GENDER = 'Male' then 1 when @GENDER = 'Female' then 2 else 0 end,
TITLECODEID = @TITLECODEID,
TITLE2CODEID = @TITLE2CODEID,
SUFFIXCODEID = @SUFFIXCODEID,
SUFFIX2CODEID = @SUFFIX2CODEID,
CUSTOMIDENTIFIER = @RE7CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
end
--add new constituent
else
begin
insert into dbo.CONSTITUENT(
ID,
KEYNAME,
FIRSTNAME,
MIDDLENAME,
NICKNAME,
BIRTHDATE,
GENDERCODE,
TITLECODEID,
TITLE2CODEID,
SUFFIXCODEID,
SUFFIX2CODEID,
CUSTOMIDENTIFIER,
CHANGEDBYID,
ADDEDBYID
)values(
@ID,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@NICKNAME,
@DATEOFBIRTH,
case when @GENDER = 'Male' then 1 when @GENDER = 'Female' then 2 else 0 end,
@TITLECODEID,
@TITLE2CODEID,
@SUFFIXCODEID,
@SUFFIX2CODEID,
@RE7CONSTITUENTID,
@CHANGEAGENTID,
@CHANGEAGENTID
)
end
-- Link constituent
if @RE7RECORDID > 0 begin
if not exists(select 1 from dbo.RE7INTEGRATIONCONSTITUENTMAP where ID = @ID)
insert into dbo.RE7INTEGRATIONCONSTITUENTMAP (
ID,
RE7RECORDID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
values (
@ID,
@RE7RECORDID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE);
end
end
-- Update Preferred Address Information
if @SYNCADDRESS = 1 and
@ADDRESS_ADDRESSTYPE != '' and
(@ADDRESS_STATE != '' or @ADDRESS_STATE_ABBREV != '' or @ADDRESS_POSTCODE !='' or @ADDRESS_CITY !='' or @ADDRESS_ADDRESSBLOCK != '')
begin
declare @CURRENT_ADDRESS_ID uniqueidentifier
select
@CURRENT_ADDRESS_ID = ADDRESS.ID
from
dbo.ADDRESS
left outer join dbo.STATE
on ADDRESS.STATEID = STATE.ID
left outer join dbo.COUNTRY
on ADDRESS.COUNTRYID = COUNTRY.ID
left outer join dbo.ADDRESSTYPECODE
on ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
where
ADDRESS.CONSTITUENTID = @ID and
ADDRESS.ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK and
ADDRESS.CITY = @ADDRESS_CITY and
coalesce(STATE.ABBREVIATION,'') = coalesce(@ADDRESS_STATE_ABBREV,'') and
ADDRESS.POSTCODE = @ADDRESS_POSTCODE and
(coalesce(COUNTRY.DESCRIPTION,'') = coalesce(@ADDRESS_COUNTRY,'') or @ADDRESS_COUNTRY = '') and
coalesce(ADDRESSTYPECODE.DESCRIPTION,'') = coalesce(@ADDRESS_ADDRESSTYPE,'')
if @CURRENT_ADDRESS_ID is not null
begin
update
dbo.ADDRESS
set
ADDRESS.ISPRIMARY = 0,
ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
ADDRESS.DATECHANGED = getdate()
where
ADDRESS.ISPRIMARY = 1 and
ADDRESS.CONSTITUENTID = @ID and
ADDRESS.ID != @CURRENT_ADDRESS_ID
update
dbo.ADDRESS
set
ADDRESS.ISPRIMARY = 1,
ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
ADDRESS.DATECHANGED = getdate()
where
ADDRESS.ID = @CURRENT_ADDRESS_ID and
ADDRESS.CONSTITUENTID = @ID and
ADDRESS.ISPRIMARY = 0
end
else
begin
declare @COUNTRYID uniqueidentifier;
if @ADDRESS_COUNTRY = ''
begin
set @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT()
end
if @COUNTRYID is null
begin
set @COUNTRYID = dbo.UFN_COUNTRY_GETID(@ADDRESS_COUNTRY,0)
end
if @COUNTRYID is null
begin
set @COUNTRYID = dbo.UFN_COUNTRY_GETID(@ADDRESS_COUNTRY_ABBREV,1)
end
if @COUNTRYID is null
begin
declare @ADDRESSFORMATID uniqueidentifier
select
@ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID
from
dbo.COUNTRYADDRESSFORMAT
where
COUNTRYADDRESSFORMAT.FORMATNAME = 'United States'
if @ADDRESSFORMATID is null
begin
select top(1)
@ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID
from
dbo.COUNTRYADDRESSFORMAT
end
if @ADDRESSFORMATID is not null
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_COUNTRY
@COUNTRYID output,
@ADDRESS_COUNTRY,
@ADDRESS_COUNTRY_ABBREV,
@ADDRESSFORMATID,
0, --INACTIVE = false
0, --ALLOWFALIDATION = false
0, --VALIDATIONCOUNTRYCODE = false
@CHANGEAGENTID
end
end
-- Get State ID if possible
declare @STATEID uniqueidentifier
if @COUNTRYID is not null
begin
set @STATEID = dbo.UFN_STATE_GETID(@COUNTRYID,@ADDRESS_STATE,0)
if @STATEID is null
begin
set @STATEID = dbo.UFN_STATE_GETID(@COUNTRYID,@ADDRESS_STATE_ABBREV,1)
end
if @STATEID is null and
@ADDRESS_STATE != '' and
@ADDRESS_STATE_ABBREV != ''
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_STATE
@STATEID output,
@COUNTRYID,
@ADDRESS_STATE,
@ADDRESS_STATE_ABBREV,
0,
@CHANGEAGENTID
end
end
-- Get address type code
declare @ADDRESSTYPECODEID uniqueidentifier
select
@ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
from
dbo.ADDRESSTYPECODE
where
ADDRESSTYPECODE.DESCRIPTION = @ADDRESS_ADDRESSTYPE
if @ADDRESSTYPECODEID is null
begin
exec dbo.USP_ADDRESSTYPECODE_CREATEENTRY
@ADDRESS_ADDRESSTYPE,
1,
null,
@CHANGEAGENTID,
@ADDRESSTYPECODEID output
end
-- Add address
if @COUNTRYID is not null and
@ADDRESSTYPECODEID is not null
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS
null,
@CHANGEAGENTID,
@ID,
@ADDRESSTYPECODEID,
1,
0,
'0000',
'0000',
@COUNTRYID,
@STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE
end
end
end
-- Update Phone Information
if @SYNCPHONES = 1 and
@PHONES is not null
begin
declare @PHONETABLE table(
PHONEID uniqueidentifier,
RE7PHONEID int,
PHONETYPE nvarchar(100),
NUMBER nvarchar(100),
DONOTCALL bit,
ISPRIMARY bit,
SEQUENCE int,
ISNEWPHONE bit,
ISNEWMAP bit
)
insert into @PHONETABLE
select
coalesce(MAP.ID, PHONE.ID, newid()),
PHONES.RE7PHONEID,
PHONES.PHONETYPE,
PHONES.NUMBER,
coalesce(PHONES.DONOTCALL, 0),
coalesce(PHONES.ISPRIMARY, 0),
row_number() over (order by PHONES.ISPRIMARY desc),
case when PHONE.ID is null and MAP.ID is null then 1 else 0 end,
case when MAP.ID is null then 1 else 0 end
from
dbo.UFN_RE7INTEGRATION_GETPHONES_FROMXML(@PHONES) PHONES
left join
dbo.RE7INTEGRATIONPHONEMAP MAP on MAP.RE7PHONEID = PHONES.RE7PHONEID
left join
dbo.PHONE on PHONES.PHONETYPE = (select DESCRIPTION from dbo.PHONETYPECODE where ID = PHONE.PHONETYPECODEID)
and (PHONES.NUMBER = PHONE.NUMBER or PHONES.NUMBER = PHONE.NUMBERNOFORMAT)
and (PHONE.CONSTITUENTID = @ID)
where
len(coalesce(PHONES.NUMBER, '')) > 0
--Get distinct type codes to prevent attempting to add duplicates
declare @PHONETYPECODETABLE table
(
TYPECODEDESCRIPTION nvarchar(100)
)
insert into @PHONETYPECODETABLE
select distinct PHONETYPE from @PHONETABLE
-- Insert any code table values that aren't present
insert into dbo.PHONETYPECODE
(
DESCRIPTION,
ACTIVE,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
T.TYPECODEDESCRIPTION,
1,
row_number() over (order by T.TYPECODEDESCRIPTION asc) + (select coalesce(max(SEQUENCE), 1) from dbo.PHONETYPECODE),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@PHONETYPECODETABLE T
left join
dbo.PHONETYPECODE PTC on T.TYPECODEDESCRIPTION = PTC.DESCRIPTION
where
PTC.ID is null
-- Clear the constituent's primary flag so we don't clash with constraints
update dbo.PHONE
set ISPRIMARY = 0
where
CONSTITUENTID = @ID and ISPRIMARY = 1
-- Update any phone numbers that already exist
update dbo.PHONE
set
PHONETYPECODEID = (select ID from dbo.PHONETYPECODE where DESCRIPTION = T.PHONETYPE),
NUMBER = T.NUMBER,
DONOTCALL = T.DONOTCALL,
ISPRIMARY = T.ISPRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@PHONETABLE T
where
ID = T.PHONEID and T.ISNEWPHONE = 0
-- Insert any phone numbers that aren't present
insert into dbo.PHONE(
ID,
CONSTITUENTID,
PHONETYPECODEID,
NUMBER,
ISPRIMARY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTCALL,
COUNTRYID,
ISCONFIDENTIAL,
ORIGINCODE
)
select
T.PHONEID,
@ID,
(select ID from dbo.PHONETYPECODE where DESCRIPTION = T.PHONETYPE),
T.NUMBER,
T.ISPRIMARY,
T.SEQUENCE + (select coalesce(max(SEQUENCE), 0) from dbo.PHONE where CONSTITUENTID = @ID),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
T.DONOTCALL,
null,
0,
0
from
@PHONETABLE T
where
T.ISNEWPHONE = 1
-- Update any phone numbers that already exist
update RE7INTEGRATIONPHONEMAP
set
RE7PHONEID = T.RE7PHONEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.RE7INTEGRATIONPHONEMAP
inner join @PHONETABLE T
on T.PHONEID = RE7INTEGRATIONPHONEMAP.ID
where
T.ISNEWMAP = 1
if @@rowcount = 0
-- Add new mappings between RE phone numbers and RP phone numbers
insert into dbo.RE7INTEGRATIONPHONEMAP(
ID,
RE7PHONEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
T.PHONEID,
T.RE7PHONEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@PHONETABLE T
where
T.ISNEWMAP = 1;
-- Delete mappings that are no longer valid because the
-- number was deleted in RE7
delete from dbo.RE7INTEGRATIONPHONEMAP
where ID in (
select ID
from
dbo.PHONE
left join
@PHONETABLE T on PHONE.ID = T.PHONEID
where
PHONE.CONSTITUENTID = @ID and T.PHONEID is null
)
end
-- Update Preferred Business Information
if @SYNCBUSINESS = 1 and
@BUSINESS_NAME != ''
begin
declare @RELATIONSHIP_ID uniqueidentifier
declare @BUSINESS_ID uniqueidentifier
-- Check current business relationships
select
@RELATIONSHIP_ID = RELATIONSHIP.ID,
@BUSINESS_ID = BUSINESS.ID
from
dbo.RELATIONSHIP
left outer join
dbo.CONSTITUENT as BUSINESS
on
BUSINESS.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
BUSINESS.NAME = @BUSINESS_NAME
if @RELATIONSHIP_ID is not null
--Set relationship to primary business
begin
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISPRIMARYBUSINESS = 1,
RELATIONSHIP.CHANGEDBYID = @CHANGEAGENTID,
RELATIONSHIP.DATECHANGED = getdate()
where
RELATIONSHIP.ID = @RELATIONSHIP_ID
end
else
begin
-- Check if organization exists
select
@BUSINESS_ID = BUSINESS.ID
from
dbo.CONSTITUENT as BUSINESS
where
BUSINESS.KEYNAME = @BUSINESS_NAME and
BUSINESS.ISORGANIZATION = 1
if @BUSINESS_ID is null
begin
set @BUSINESS_ID = newid()
insert into dbo.CONSTITUENT
(
ID,
ISORGANIZATION,
KEYNAME,
ADDEDBYID,
CHANGEDBYID
) values (
@BUSINESS_ID,
1,
@BUSINESS_NAME,
@CHANGEAGENTID,
@CHANGEAGENTID
);
end
declare @RECIPROCALTYPECODEID uniqueidentifier
declare @RELATIONSHIPTYPECODEID uniqueidentifier
select
@RECIPROCALTYPECODEID = RELATIONSHIPTYPECODE.ID
from
dbo.RELATIONSHIPTYPECODE
where
RELATIONSHIPTYPECODE.DESCRIPTION = 'Employer'
if @RECIPROCALTYPECODEID is null
begin
exec USP_RELATIONSHIPTYPECODE_CREATEENTRY 'Employer',
1,
null,
@CHANGEAGENTID,
@RECIPROCALTYPECODEID output;
end
select
@RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
from
dbo.RELATIONSHIPTYPECODE
where
RELATIONSHIPTYPECODE.DESCRIPTION = 'Employee'
if @RELATIONSHIPTYPECODEID is null
begin
exec USP_RELATIONSHIPTYPECODE_CREATEENTRY 'Employee',
1,
null,
@CHANGEAGENTID,
@RELATIONSHIPTYPECODEID output;
end
exec dbo.USP_DATAFORMTEMPLATE_ADD_INDTOORG_RELATIONSHIP
@RELATIONSHIP_ID,
@CHANGEAGENTID,
@ID,
@BUSINESS_ID,
@RECIPROCALTYPECODEID,
@RELATIONSHIPTYPECODEID,
null,
null,
0,
0,
null,
'',
1,
0
end
--Set business address
if @BUSINESS_ADDRESSTYPE != '' and
(@BUSINESS_STATE != '' or @BUSINESS_STATE_ABBREV != '' or @BUSINESS_POSTCODE !='' or @BUSINESS_CITY !='' or @BUSINESS_ADDRESSBLOCK != '')
begin
declare @CURRENT_BUSINESS_ADDRESS_ID uniqueidentifier
select
@CURRENT_BUSINESS_ADDRESS_ID = ADDRESS.ID
from
dbo.ADDRESS
left outer join dbo.STATE
on ADDRESS.STATEID = STATE.ID
left outer join dbo.COUNTRY
on ADDRESS.COUNTRYID = COUNTRY.ID
left outer join dbo.ADDRESSTYPECODE
on ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
where
ADDRESS.CONSTITUENTID = @BUSINESS_ID and
ADDRESS.ADDRESSBLOCK = @BUSINESS_ADDRESSBLOCK and
ADDRESS.CITY = @BUSINESS_CITY and
coalesce(STATE.ABBREVIATION,'') = coalesce(@BUSINESS_STATE_ABBREV,'')and
ADDRESS.POSTCODE = @BUSINESS_POSTCODE and
(coalesce(COUNTRY.DESCRIPTION,'') = coalesce(@BUSINESS_COUNTRY,'') or @BUSINESS_COUNTRY = '') and
coalesce(ADDRESSTYPECODE.DESCRIPTION,'') = coalesce(@BUSINESS_ADDRESSTYPE,'')
if @CURRENT_BUSINESS_ADDRESS_ID is not null
begin
update
dbo.ADDRESS
set
ADDRESS.ISPRIMARY = 0,
ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
ADDRESS.DATECHANGED = getdate()
where
ADDRESS.ISPRIMARY = 1 and
ADDRESS.CONSTITUENTID = @BUSINESS_ID and
ADDRESS.ID != @CURRENT_BUSINESS_ADDRESS_ID
update
dbo.ADDRESS
set
ADDRESS.ISPRIMARY = 1,
ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
ADDRESS.DATECHANGED = getdate()
where
ADDRESS.ID = @CURRENT_BUSINESS_ADDRESS_ID and
ADDRESS.CONSTITUENTID = @BUSINESS_ID and
ADDRESS.ISPRIMARY = 0
end
else
begin
declare @BUSINESS_COUNTRYID uniqueidentifier;
if @BUSINESS_COUNTRY = ''
begin
set @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT()
end
if @BUSINESS_COUNTRYID is null
begin
set @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETID(@BUSINESS_COUNTRY,0)
end
if @BUSINESS_COUNTRYID is null
begin
set @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETID(@BUSINESS_COUNTRY_ABBREV,1)
end
if @BUSINESS_COUNTRYID is null
begin
declare @BUSINESS_ADDRESSFORMATID uniqueidentifier
select
@BUSINESS_ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID
from
dbo.COUNTRYADDRESSFORMAT
where
COUNTRYADDRESSFORMAT.FORMATNAME = 'United States'
if @BUSINESS_ADDRESSFORMATID is null
begin
select top(1)
@BUSINESS_ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID
from
dbo.COUNTRYADDRESSFORMAT
end
if @BUSINESS_ADDRESSFORMATID is not null
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_COUNTRY
@BUSINESS_COUNTRYID output,
@BUSINESS_COUNTRY,
@BUSINESS_COUNTRY_ABBREV,
@BUSINESS_ADDRESSFORMATID,
0, --INACTIVE = false
0, --ALLOWFALIDATION = false
0, --VALIDATIONCOUNTRYCODE = false
@CHANGEAGENTID
end
end
--Get State ID if possible
declare @BUSINESS_STATEID uniqueidentifier
if @BUSINESS_COUNTRYID is not null
begin
set @BUSINESS_STATEID = dbo.UFN_STATE_GETID(@BUSINESS_COUNTRYID,@BUSINESS_STATE,0)
if @BUSINESS_STATEID is null
begin
set @BUSINESS_STATEID = dbo.UFN_STATE_GETID(@BUSINESS_COUNTRYID,@BUSINESS_STATE_ABBREV,1)
end
if @BUSINESS_STATEID is null and
@BUSINESS_STATE != '' and
@BUSINESS_STATE_ABBREV != ''
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_STATE
@BUSINESS_STATEID output,
@BUSINESS_COUNTRYID,
@BUSINESS_STATE,
@BUSINESS_STATE_ABBREV,
0,
@CHANGEAGENTID
end
end
-- Get business address type code
declare @BUSINESS_ADDRESSTYPECODEID uniqueidentifier
select
@BUSINESS_ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
from
dbo.ADDRESSTYPECODE
where
ADDRESSTYPECODE.DESCRIPTION = @BUSINESS_ADDRESSTYPE
if @BUSINESS_ADDRESSTYPECODEID is null
begin
exec dbo.USP_ADDRESSTYPECODE_CREATEENTRY
@BUSINESS_ADDRESSTYPE,
1,
null,
@CHANGEAGENTID,
@BUSINESS_ADDRESSTYPECODEID output
end
-- Add business address
if @BUSINESS_COUNTRYID is not null and
@BUSINESS_ADDRESSTYPECODEID is not null
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS
null,
@CHANGEAGENTID,
@BUSINESS_ID,
@BUSINESS_ADDRESSTYPECODEID,
1,
0,
'0000',
'0000',
@BUSINESS_COUNTRYID,
@BUSINESS_STATEID,
@BUSINESS_ADDRESSBLOCK,
@BUSINESS_CITY,
@BUSINESS_POSTCODE
end
end
end
end
-- Update Giving Information
if @SYNCGIVING = 1
begin
if (select
count(ID)
from
dbo.RE7INTEGRATIONGIVINGSUMMARY
where
RE7INTEGRATIONGIVINGSUMMARY.ID = @ID
) > 0
begin
update
dbo.RE7INTEGRATIONGIVINGSUMMARY
set
FIRSTGIFTAMOUNT = @FIRSTGIFTAMOUNT,
FIRSTGIFTDATE = @FIRSTGIFTDATE,
LARGESTGIFTAMOUNT = @LARGESTGIFTAMOUNT,
LARGESTGIFTDATE = @LARGESTGIFTDATE,
LATESTGIFTAMOUNT = @LATESTGIFTAMOUNT,
LATESTGIFTDATE = @LATESTGIFTDATE,
TOTALGIFTSGIVEN = @TOTALGIFTSGIVEN,
TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT,
FIRSTGIFTTYPE = @FIRSTGIFTTYPE,
LARGESTGIFTTYPE = @LARGESTGIFTTYPE,
LATESTGIFTTYPE = @LATESTGIFTTYPE,
LATESTGIFTDESIGNATION = @LATESTGIFTDESIGNATION,
FIRSTGIFTDESIGNATION = @FIRSTGIFTDESIGNATION,
LARGESTGIFTDESIGNATION = @LARGESTGIFTDESIGNATION,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
where
RE7INTEGRATIONGIVINGSUMMARY.ID = @ID
end
else
begin
insert into dbo.RE7INTEGRATIONGIVINGSUMMARY(
ID,
FIRSTGIFTAMOUNT,
FIRSTGIFTDATE,
LARGESTGIFTAMOUNT,
LARGESTGIFTDATE,
LATESTGIFTAMOUNT,
LATESTGIFTDATE,
TOTALGIFTSGIVEN,
TOTALGIFTAMOUNT,
FIRSTGIFTTYPE,
LARGESTGIFTTYPE,
LATESTGIFTTYPE,
LATESTGIFTDESIGNATION,
FIRSTGIFTDESIGNATION,
LARGESTGIFTDESIGNATION,
CHANGEDBYID,
ADDEDBYID
)values(
@ID,
@FIRSTGIFTAMOUNT,
@FIRSTGIFTDATE,
@LARGESTGIFTAMOUNT,
@LARGESTGIFTDATE,
@LATESTGIFTAMOUNT,
@LATESTGIFTDATE,
@TOTALGIFTSGIVEN,
@TOTALGIFTAMOUNT,
@FIRSTGIFTTYPE,
@LARGESTGIFTTYPE,
@LATESTGIFTTYPE,
@LATESTGIFTDESIGNATION,
@FIRSTGIFTDESIGNATION,
@LARGESTGIFTDESIGNATION,
@CHANGEAGENTID,
@CHANGEAGENTID
)
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;