USP_DATAFORMTEMPLATE_LOAD_REVENUEBATCHCONSTITUENT_3
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(110) | IN | |
| @DATALOADED | bit | INOUT | |
| @ISORGANIZATION | bit | INOUT | |
| @ISEXISTINGCONSTITUENT | bit | INOUT | |
| @LASTNAME | nvarchar(100) | INOUT | |
| @ORGANIZATIONNAME | nvarchar(100) | INOUT | |
| @FIRSTNAME | nvarchar(50) | INOUT | |
| @MIDDLENAME | nvarchar(50) | INOUT | |
| @MAIDENNAME | nvarchar(100) | INOUT | |
| @NICKNAME | nvarchar(50) | INOUT | |
| @TITLECODEID | nvarchar(50) | INOUT | |
| @SUFFIXCODEID | nvarchar(50) | INOUT | |
| @GENDERCODE | nvarchar(12) | INOUT | |
| @BIRTHDATE | UDT_FUZZYDATE | INOUT | |
| @ADDRESS_ADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @ADDRESS_DONOTMAIL | bit | INOUT | |
| @ADDRESS_COUNTRYID | uniqueidentifier | INOUT | |
| @ADDRESS_COUNTRY | nvarchar(50) | INOUT | |
| @ADDRESS_STATEID | nvarchar(50) | INOUT | |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @ADDRESS_CITY | nvarchar(50) | INOUT | |
| @ADDRESS_POSTCODE | nvarchar(12) | INOUT | |
| @PHONE_PHONETYPECODEID | nvarchar(50) | INOUT | |
| @PHONE_NUMBER | nvarchar(100) | INOUT | |
| @EMAILADDRESS_EMAILADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | INOUT | |
| @ORGANIZATION_ADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @ORGANIZATION_DONOTMAIL | bit | INOUT | |
| @ORGANIZATION_COUNTRYID | uniqueidentifier | INOUT | |
| @ORGANIZATION_COUNTRY | nvarchar(50) | INOUT | |
| @ORGANIZATION_STATEID | nvarchar(50) | INOUT | |
| @ORGANIZATION_ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @ORGANIZATION_CITY | nvarchar(50) | INOUT | |
| @ORGANIZATION_POSTCODE | nvarchar(12) | INOUT | |
| @ORGANIZATION_PHONETYPECODEID | nvarchar(50) | INOUT | |
| @ORGANIZATION_NUMBER | nvarchar(100) | INOUT | |
| @ORGANIZATION_EMAILADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @ORGANIZATION_EMAILADDRESS | UDT_EMAILADDRESS | INOUT | |
| @MARITALSTATUS | nvarchar(50) | INOUT | |
| @WEBADDRESS | nvarchar(100) | INOUT | |
| @INDUSTRYCODEID | nvarchar(100) | INOUT | |
| @NUMEMPLOYEES | int | INOUT | |
| @NUMSUBSIDIARIES | int | INOUT | |
| @PARENTCORPID | nvarchar(100) | INOUT | |
| @SPOUSEID | uniqueidentifier | INOUT | |
| @EXISTINGSPOUSE | bit | INOUT | |
| @SPOUSE_LASTNAME | nvarchar(100) | INOUT | |
| @SPOUSE_FIRSTNAME | nvarchar(50) | INOUT | |
| @SPOUSE_MIDDLENAME | nvarchar(50) | INOUT | |
| @SPOUSE_MAIDENNAME | nvarchar(100) | INOUT | |
| @SPOUSE_NICKNAME | nvarchar(50) | INOUT | |
| @SPOUSE_TITLECODEID | nvarchar(50) | INOUT | |
| @SPOUSE_SUFFIXCODEID | nvarchar(50) | INOUT | |
| @SPOUSE_GENDERCODE | nvarchar(50) | INOUT | |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | INOUT | |
| @SPOUSE_RECIPROCALTYPECODEID | nvarchar(50) | INOUT | |
| @SPOUSE_RELATIONSHIPTYPECODEID | nvarchar(50) | INOUT | |
| @COPYPRIMARYINFORMATION | bit | INOUT | |
| @SPOUSE_STARTDATE | datetime | INOUT | |
| @PRIMARYRELATIONSHIPEXISTS | bit | INOUT | |
| @PRIMARYMATCHFACTOR | decimal(5, 2) | INOUT | |
| @RECIPROCALRELATIONSHIPEXISTS | bit | INOUT | |
| @RECIPROCALMATCHFACTOR | decimal(5, 2) | INOUT | |
| @BUSINESSID | uniqueidentifier | INOUT | |
| @EXISTINGBUSINESS | bit | INOUT | |
| @BUSINESS_NAME | nvarchar(100) | INOUT | |
| @BUSINESS_ADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @BUSINESS_COUNTRYID | uniqueidentifier | INOUT | |
| @BUSINESS_COUNTRY | nvarchar(50) | INOUT | |
| @BUSINESS_STATEID | nvarchar(50) | INOUT | |
| @BUSINESS_ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @BUSINESS_CITY | nvarchar(50) | INOUT | |
| @BUSINESS_POSTCODE | nvarchar(12) | INOUT | |
| @BUSINESS_DONOTMAIL | bit | INOUT | |
| @BUSINESS_PHONETYPECODEID | nvarchar(50) | INOUT | |
| @BUSINESS_NUMBER | nvarchar(100) | INOUT | |
| @BUSINESS_RECIPROCALTYPECODEID | nvarchar(50) | INOUT | |
| @BUSINESS_RELATIONSHIPTYPECODEID | nvarchar(50) | INOUT | |
| @BUSINESS_STARTDATE | datetime | INOUT | |
| @ISCONTACT | bit | INOUT | |
| @ISPRIMARYCONTACT | bit | INOUT | |
| @CONTACTTYPECODEID | nvarchar(50) | INOUT | |
| @POSITION | nvarchar(100) | INOUT | |
| @ISMATCHINGGIFTRELATIONSHIP | bit | INOUT | |
| @INDIVIDUALSHOUSEHOLDNAME | nvarchar(100) | INOUT | |
| @ISGROUP | bit | INOUT | |
| @GROUP_NAME | nvarchar(100) | INOUT | |
| @GROUP_TYPE | nvarchar(150) | INOUT | |
| @GROUP_NUMBERMEMBERS | int | INOUT | |
| @GROUP_PRIMARYCONTACT | nvarchar(154) | INOUT | |
| @GROUP_ADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @GROUP_COUNTRYID | uniqueidentifier | INOUT | |
| @GROUP_COUNTRY | nvarchar(50) | INOUT | |
| @GROUP_STATEID | nvarchar(50) | INOUT | |
| @GROUP_ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @GROUP_CITY | nvarchar(50) | INOUT | |
| @GROUP_POSTCODE | nvarchar(12) | INOUT | |
| @GROUP_PHONETYPECODEID | nvarchar(50) | INOUT | |
| @GROUP_NUMBER | nvarchar(100) | INOUT | |
| @GROUP_EMAILADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @GROUP_EMAILADDRESS | UDT_EMAILADDRESS | INOUT | |
| @ISHOUSEHOLD | bit | INOUT | |
| @HOUSEHOLD_NAME | nvarchar(100) | INOUT | |
| @HOUSEHOLD_NUMBERMEMBERS | int | INOUT | |
| @HOUSEHOLD_PRIMARYCONTACT | nvarchar(154) | INOUT | |
| @HOUSEHOLD_MEMBERS | xml | INOUT | |
| @HOUSEHOLD_ADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @HOUSEHOLD_COUNTRYID | uniqueidentifier | INOUT | |
| @HOUSEHOLD_COUNTRY | nvarchar(50) | INOUT | |
| @HOUSEHOLD_STATEID | nvarchar(50) | INOUT | |
| @HOUSEHOLD_ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @HOUSEHOLD_CITY | nvarchar(50) | INOUT | |
| @HOUSEHOLD_POSTCODE | nvarchar(12) | INOUT | |
| @HOUSEHOLD_PHONETYPECODEID | nvarchar(50) | INOUT | |
| @HOUSEHOLD_NUMBER | nvarchar(100) | INOUT | |
| @HOUSEHOLD_EMAILADDRESSTYPECODEID | nvarchar(50) | INOUT | |
| @HOUSEHOLD_EMAILADDRESS | UDT_EMAILADDRESS | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | INOUT | |
| @APPLICATIONS | xml | INOUT | |
| @BALANCE | money | INOUT | |
| @EXISTINGTAXDECLARATIONS | xml | INOUT | |
| @NEWTAXDECLARATIONS | xml | INOUT | |
| @JOBCATEGORYCODEID | nvarchar(50) | INOUT | |
| @CAREERLEVELCODEID | nvarchar(50) | INOUT | |
| @MATCHINGGIFTS | xml | INOUT | |
| @CONSTITUENTHASEDITS | bit | INOUT | |
| @GENDERCODEID | nvarchar(50) | INOUT | |
| @SPOUSE_GENDERCODEID | nvarchar(50) | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_REVENUEBATCHCONSTITUENT_3
(
@ID nvarchar(110),
@DATALOADED bit = 0 output,
@ISORGANIZATION bit = null output,
@ISEXISTINGCONSTITUENT bit = null output,
@LASTNAME nvarchar(100) = null output,
@ORGANIZATIONNAME nvarchar(100) = null output,
@FIRSTNAME nvarchar(50) = null output,
@MIDDLENAME nvarchar(50) = null output,
@MAIDENNAME nvarchar(100) = null output,
@NICKNAME nvarchar(50) = null output,
@TITLECODEID nvarchar(50) = null output,
@SUFFIXCODEID nvarchar(50) = null output,
@GENDERCODE nvarchar(12) = null output,
@BIRTHDATE dbo.UDT_FUZZYDATE = null output,
@ADDRESS_ADDRESSTYPECODEID nvarchar(50) = null output,
@ADDRESS_DONOTMAIL bit = null output,
@ADDRESS_COUNTRYID uniqueidentifier = null output,
@ADDRESS_COUNTRY nvarchar(50) = null output,
@ADDRESS_STATEID nvarchar(50) = null output,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
@ADDRESS_CITY nvarchar(50) = null output,
@ADDRESS_POSTCODE nvarchar(12) = null output,
@PHONE_PHONETYPECODEID nvarchar(50) = null output,
@PHONE_NUMBER nvarchar(100) = null output,
@EMAILADDRESS_EMAILADDRESSTYPECODEID nvarchar(50) = null output,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
@ORGANIZATION_ADDRESSTYPECODEID nvarchar(50) = null output,
@ORGANIZATION_DONOTMAIL bit = null output,
@ORGANIZATION_COUNTRYID uniqueidentifier = null output,
@ORGANIZATION_COUNTRY nvarchar(50) = null output,
@ORGANIZATION_STATEID nvarchar(50) = null output,
@ORGANIZATION_ADDRESSBLOCK nvarchar(150) = null output,
@ORGANIZATION_CITY nvarchar(50) = null output,
@ORGANIZATION_POSTCODE nvarchar(12) = null output,
@ORGANIZATION_PHONETYPECODEID nvarchar(50) = null output,
@ORGANIZATION_NUMBER nvarchar(100) = null output,
@ORGANIZATION_EMAILADDRESSTYPECODEID nvarchar(50) = null output,
@ORGANIZATION_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
@MARITALSTATUS nvarchar(50) = null output,
@WEBADDRESS nvarchar(100) = null output,
@INDUSTRYCODEID nvarchar(100) = null output,
@NUMEMPLOYEES int = null output,
@NUMSUBSIDIARIES int = null output,
@PARENTCORPID nvarchar(100) = null output,
--Individual's Household Variables
@SPOUSEID uniqueidentifier = null output,
@EXISTINGSPOUSE bit = null output,
@SPOUSE_LASTNAME nvarchar(100) = null output,
@SPOUSE_FIRSTNAME nvarchar(50) = null output,
@SPOUSE_MIDDLENAME nvarchar(50) = null output,
@SPOUSE_MAIDENNAME nvarchar(100) = null output,
@SPOUSE_NICKNAME nvarchar(50) = null output,
@SPOUSE_TITLECODEID nvarchar(50) = null output,
@SPOUSE_SUFFIXCODEID nvarchar(50) = null output,
@SPOUSE_GENDERCODE nvarchar(50) = null output,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = null output,
@SPOUSE_RECIPROCALTYPECODEID nvarchar(50) = null output,
@SPOUSE_RELATIONSHIPTYPECODEID nvarchar(50) = null output,
@COPYPRIMARYINFORMATION bit = null output,
@SPOUSE_STARTDATE datetime = null output,
@PRIMARYRELATIONSHIPEXISTS bit = null output,
@PRIMARYMATCHFACTOR decimal(5,2) = null output,
@RECIPROCALRELATIONSHIPEXISTS bit = null output,
@RECIPROCALMATCHFACTOR decimal(5,2) = null output,
--Organization variables
@BUSINESSID uniqueidentifier = null output,
@EXISTINGBUSINESS bit = null output,
@BUSINESS_NAME nvarchar(100) = null output,
@BUSINESS_ADDRESSTYPECODEID nvarchar(50) = null output,
@BUSINESS_COUNTRYID uniqueidentifier = null output,
@BUSINESS_COUNTRY nvarchar(50) = null output,
@BUSINESS_STATEID nvarchar(50) = null output,
@BUSINESS_ADDRESSBLOCK nvarchar(150) = null output,
@BUSINESS_CITY nvarchar(50) = null output,
@BUSINESS_POSTCODE nvarchar(12) = null output,
@BUSINESS_DONOTMAIL bit = null output,
@BUSINESS_PHONETYPECODEID nvarchar(50) = null output,
@BUSINESS_NUMBER nvarchar(100) = null output,
@BUSINESS_RECIPROCALTYPECODEID nvarchar(50) = null output,
@BUSINESS_RELATIONSHIPTYPECODEID nvarchar(50) = null output,
@BUSINESS_STARTDATE datetime = null output,
@ISCONTACT bit = null output,
@ISPRIMARYCONTACT bit = null output,
@CONTACTTYPECODEID nvarchar(50) = null output,
@POSITION nvarchar(100) = null output,
@ISMATCHINGGIFTRELATIONSHIP bit = null output,
-- Individual's household variables continued
@INDIVIDUALSHOUSEHOLDNAME nvarchar(100) = null output,
--Group variables
@ISGROUP bit = null output,
@GROUP_NAME nvarchar(100) = null output,
@GROUP_TYPE nvarchar(150) = null output,
@GROUP_NUMBERMEMBERS int = null output,
@GROUP_PRIMARYCONTACT nvarchar(154) = null output,
@GROUP_ADDRESSTYPECODEID nvarchar(50) = null output,
@GROUP_COUNTRYID uniqueidentifier = null output,
@GROUP_COUNTRY nvarchar(50) = null output,
@GROUP_STATEID nvarchar(50) = null output,
@GROUP_ADDRESSBLOCK nvarchar(150) = null output,
@GROUP_CITY nvarchar(50) = null output,
@GROUP_POSTCODE nvarchar(12) = null output,
@GROUP_PHONETYPECODEID nvarchar(50) = null output,
@GROUP_NUMBER nvarchar(100) = null output,
@GROUP_EMAILADDRESSTYPECODEID nvarchar(50) = null output,
@GROUP_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
--Household variables
@ISHOUSEHOLD bit = null output,
@HOUSEHOLD_NAME nvarchar(100) = null output,
@HOUSEHOLD_NUMBERMEMBERS int = null output,
@HOUSEHOLD_PRIMARYCONTACT nvarchar(154) = null output,
@HOUSEHOLD_MEMBERS xml = null output,
@HOUSEHOLD_ADDRESSTYPECODEID nvarchar(50) = null output,
@HOUSEHOLD_COUNTRYID uniqueidentifier = null output,
@HOUSEHOLD_COUNTRY nvarchar(50) = null output,
@HOUSEHOLD_STATEID nvarchar(50) = null output,
@HOUSEHOLD_ADDRESSBLOCK nvarchar(150) = null output,
@HOUSEHOLD_CITY nvarchar(50) = null output,
@HOUSEHOLD_POSTCODE nvarchar(12) = null output,
@HOUSEHOLD_PHONETYPECODEID nvarchar(50) = null output,
@HOUSEHOLD_NUMBER nvarchar(100) = null output,
@HOUSEHOLD_EMAILADDRESSTYPECODEID nvarchar(50) = null output,
@HOUSEHOLD_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
@CURRENTAPPUSERID uniqueidentifier,
@CONSTITUENTID uniqueidentifier = null output,
@APPLICATIONS xml = null output,
@BALANCE money = null output,
@EXISTINGTAXDECLARATIONS xml = null output,
@NEWTAXDECLARATIONS xml = null output,
@JOBCATEGORYCODEID nvarchar(50) = null output,
@CAREERLEVELCODEID nvarchar(50) = null output,
@MATCHINGGIFTS xml = null output,
@CONSTITUENTHASEDITS bit = null output,
@GENDERCODEID nvarchar(50) = null output,
@SPOUSE_GENDERCODEID nvarchar(50) = null output
) as begin
set nocount on;
begin try
--retrieve the batch row id and the constituent value
declare @BATCHROWID uniqueidentifier = cast(substring(@ID,75,36) as uniqueidentifier);
set @ID = substring(@ID,1,36)
set @CONSTITUENTID = @ID
set @ISEXISTINGCONSTITUENT = 0;
select @ISEXISTINGCONSTITUENT=1 from dbo.CONSTITUENT where CONSTITUENT.ID = @ID;
if @ISEXISTINGCONSTITUENT = 1
begin
-- check to see if this constituent has edits saved in the CUB table
select @CONSTITUENTHASEDITS = 1 from dbo.BATCHCONSTITUENTUPDATE where ID= @BATCHROWID and PRIMARYRECORDID = @CONSTITUENTID
-- Check constituent security. The check is done manually since it only applies to existing constituents.
if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1) or
((dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, '15445353-18d6-46af-8461-778480e30eb0', @ID) = 1) and -- Constituent group security
exists (select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@ID) where dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, '15445353-18d6-46af-8461-778480e30eb0', SITEID) = 1)) -- Site security
begin
select @ISORGANIZATION=CONSTITUENT.ISORGANIZATION,
@LASTNAME = CONSTITUENT.KEYNAME,
@FIRSTNAME = CONSTITUENT.FIRSTNAME,
@MIDDLENAME = CONSTITUENT.MIDDLENAME,
@MAIDENNAME = CONSTITUENT.MAIDENNAME,
@NICKNAME=CONSTITUENT.NICKNAME,
@TITLECODEID=(select TITLECODE.DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLECODEID),
@SUFFIXCODEID=(select DESCRIPTION from dbo.SUFFIXCODE where ID = CONSTITUENT.SUFFIXCODEID),
@GENDERCODE=CONSTITUENT.GENDER,
@GENDERCODEID= (select dbo.UFN_GENDERCODE_GETDESCRIPTION(CONSTITUENT.GENDERCODEID)),
@BIRTHDATE=CONSTITUENT.BIRTHDATE,
@ADDRESS_ADDRESSTYPECODEID=(select DESCRIPTION from dbo.ADDRESSTYPECODE where ID = ADDRESS.ADDRESSTYPECODEID),
@ADDRESS_DONOTMAIL=ADDRESS.DONOTMAIL,
@ADDRESS_COUNTRYID=ADDRESS.COUNTRYID,
@ADDRESS_COUNTRY=(select DESCRIPTION from dbo.COUNTRY where ID = ADDRESS.COUNTRYID),
@ADDRESS_STATEID=(select ABBREVIATION from dbo.STATE where ID = ADDRESS.STATEID),
@ADDRESS_ADDRESSBLOCK=dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
@ADDRESS_CITY=ADDRESS.CITY,
@ADDRESS_POSTCODE=ADDRESS.POSTCODE,
@PHONE_PHONETYPECODEID=(select DESCRIPTION from dbo.PHONETYPECODE where ID = PHONE.PHONETYPECODEID),
@PHONE_NUMBER=PHONE.NUMBER,
@EMAILADDRESS_EMAILADDRESSTYPECODEID=(select DESCRIPTION from dbo.EMAILADDRESSTYPECODE where ID = EMAILADDRESS.EMAILADDRESSTYPECODEID),
@EMAILADDRESS_EMAILADDRESS=EMAILADDRESS.EMAILADDRESS,
@MARITALSTATUS=(select DESCRIPTION from dbo.MARITALSTATUSCODE where ID = CONSTITUENT.MARITALSTATUSCODEID),
@WEBADDRESS=CONSTITUENT.WEBADDRESS,
@INDUSTRYCODEID=(select DESCRIPTION from dbo.INDUSTRYCODE where ID = INDUSTRYCODEID),
@NUMEMPLOYEES=ORGANIZATIONDATA.NUMEMPLOYEES,
@NUMSUBSIDIARIES=ORGANIZATIONDATA.NUMSUBSIDIARIES,
@PARENTCORPID=case when ORGANIZATIONDATA.PARENTCORPID is null then '' else (select NAME from dbo.CONSTITUENT where ID = ORGANIZATIONDATA.PARENTCORPID) end,
@ISGROUP = CONSTITUENT.ISGROUP,
@ISHOUSEHOLD = case when GROUPDATA.GROUPTYPECODE = 0 and CONSTITUENT.ISGROUP = 1 then 1 else 0 end
from dbo.CONSTITUENT
left join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
left join dbo.PHONE on CONSTITUENT.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
left join dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1
left join dbo.ORGANIZATIONDATA on CONSTITUENT.ID = ORGANIZATIONDATA.ID
left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
where CONSTITUENT.ID = @ID
-- If the constituent is an individual, return their tax declarations
if @ISORGANIZATION = 0 and @ISGROUP = 0
set @EXISTINGTAXDECLARATIONS = (
select
(CHARITYCLAIMREFERENCENUMBER.REFERENCENUMBER + ' - ' + CHARITYCLAIMREFERENCENUMBER.DESCRIPTION) as REFERENCENUMBER,
TAXDECLARATION.DECLARATIONSTARTS,
TAXDECLARATION.DECLARATIONENDS,
TAXDECLARATION.DECLARATIONINDICATOR,
TAXDECLARATION.PAYSTAX
from dbo.TAXDECLARATION
inner join dbo.CHARITYCLAIMREFERENCENUMBER on TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID
where
TAXDECLARATION.CONSTITUENTID = @CONSTITUENTID and
exists (select 1 from dbo.UFN_APPUSER_HASPERMISSIONFORDECLARATION(@CURRENTAPPUSERID, CHARITYCLAIMREFERENCENUMBER.ID))
for xml raw('ITEM'),type,elements,root('EXISTINGTAXDECLARATIONS'),BINARY BASE64)
select @SPOUSEID=CONSTITUENT.ID,
@SPOUSE_LASTNAME=CONSTITUENT.KEYNAME,
@SPOUSE_FIRSTNAME=CONSTITUENT.FIRSTNAME,
@SPOUSE_MIDDLENAME=CONSTITUENT.MIDDLENAME,
@SPOUSE_MAIDENNAME=CONSTITUENT.MAIDENNAME,
@SPOUSE_NICKNAME=CONSTITUENT.NICKNAME,
@SPOUSE_TITLECODEID=(select DESCRIPTION from dbo.TITLECODE where ID = CONSTITUENT.TITLECODEID),
@SPOUSE_SUFFIXCODEID=(select DESCRIPTION from dbo.SUFFIXCODE where ID = CONSTITUENT.SUFFIXCODEID),
@SPOUSE_GENDERCODE=CONSTITUENT.GENDER,
@SPOUSE_GENDERCODEID=(select dbo.UFN_GENDERCODE_GETDESCRIPTION(CONSTITUENT.GENDERCODEID)),
@SPOUSE_BIRTHDATE=CONSTITUENT.BIRTHDATE,
@SPOUSE_RECIPROCALTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = RELATIONSHIP.RELATIONSHIPTYPECODEID),
@SPOUSE_RELATIONSHIPTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = RELATIONSHIP.RECIPROCALTYPECODEID),
@SPOUSE_STARTDATE=RELATIONSHIP.STARTDATE
from dbo.RELATIONSHIP
left join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and CONSTITUENT.ISORGANIZATION = 0 and RELATIONSHIP.ISSPOUSE = 1
if @@ROWCOUNT = 0
set @EXISTINGSPOUSE = 1;
else
if @SPOUSEID is null
set @EXISTINGSPOUSE = 0;
else
set @EXISTINGSPOUSE = 1;
if @PRIMARYRELATIONSHIPEXISTS = 0
set @PRIMARYMATCHFACTOR = null;
if @RECIPROCALRELATIONSHIPEXISTS = 0
set @RECIPROCALMATCHFACTOR = null;
if @ISGROUP = 0 and @ISORGANIZATION = 0
begin
select
@INDIVIDUALSHOUSEHOLDNAME = C_NF.NAME
from dbo.GROUPMEMBER
inner join dbo.CONSTITUENT on GROUPMEMBER.GROUPID = CONSTITUENT.ID
inner join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) C_NF
where GROUPMEMBER.MEMBERID = @ID and GROUPDATA.GROUPTYPECODE = 0
end
select @BUSINESSID=CONSTITUENT.ID,
@BUSINESS_NAME=C_NF.NAME,
@BUSINESS_ADDRESSTYPECODEID=(select DESCRIPTION from dbo.ADDRESSTYPECODE where ID = ADDRESS.ADDRESSTYPECODEID),
@BUSINESS_COUNTRYID=ADDRESS.COUNTRYID,
@BUSINESS_COUNTRY=(select DESCRIPTION from dbo.COUNTRY where ID = ADDRESS.COUNTRYID),
@BUSINESS_STATEID=(select STATE.ABBREVIATION from dbo.STATE where ID = ADDRESS.STATEID),
@BUSINESS_ADDRESSBLOCK=dbo.UFN_BUILDFULLADDRESS(null, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
@BUSINESS_DONOTMAIL=ADDRESS.DONOTMAIL,
@BUSINESS_PHONETYPECODEID=(select PHONETYPECODE.DESCRIPTION from dbo.PHONETYPECODE where PHONETYPECODE.ID = PHONE.PHONETYPECODEID),
@BUSINESS_NUMBER=PHONE.NUMBER,
@BUSINESS_RECIPROCALTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = RELATIONSHIP.RELATIONSHIPTYPECODEID),
@BUSINESS_RELATIONSHIPTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = RELATIONSHIP.RECIPROCALTYPECODEID),
@BUSINESS_STARTDATE=RELATIONSHIP.STARTDATE,
@ISCONTACT=RELATIONSHIP.ISCONTACT,
@ISPRIMARYCONTACT=RELATIONSHIP.ISPRIMARYCONTACT,
@CONTACTTYPECODEID=(select DESCRIPTION from dbo.CONTACTTYPECODE where ID = RELATIONSHIP.CONTACTTYPECODEID),
@POSITION=RELATIONSHIPJOBINFO.JOBTITLE,
@ISMATCHINGGIFTRELATIONSHIP=RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP,
@JOBCATEGORYCODEID=(select DESCRIPTION from dbo.JOBCATEGORYCODE where ID = RELATIONSHIPJOBINFO.JOBCATEGORYCODEID),
@CAREERLEVELCODEID=(select DESCRIPTION from dbo.CAREERLEVELCODE where ID = RELATIONSHIPJOBINFO.CAREERLEVELCODEID)
from dbo.RELATIONSHIP
left join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) C_NF
left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
left join dbo.RELATIONSHIPJOBINFO on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and RELATIONSHIP.ISPRIMARYBUSINESS = 1
if @@ROWCOUNT = 0
set @EXISTINGBUSINESS = 1;
else
if @BUSINESSID is null
set @EXISTINGBUSINESS = 0;
else
set @EXISTINGBUSINESS = 1;
end
else
begin
raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED', 13, 1)
end
end
else
begin
if exists(select 1 from dbo.[BATCHREVENUECONSTITUENT] where [ID] = @ID)
begin
select @ISORGANIZATION=ISORGANIZATION,
@ISGROUP=BATCHREVENUECONSTITUENT.ISGROUP,
@ISHOUSEHOLD = case when BATCHREVENUECONSTITUENT.GROUPTYPECODE = 0 and BATCHREVENUECONSTITUENT.ISGROUP = 1 then 1 else 0 end,
@LASTNAME = BATCHREVENUECONSTITUENT.KEYNAME,
@FIRSTNAME = BATCHREVENUECONSTITUENT.FIRSTNAME,
@MIDDLENAME = BATCHREVENUECONSTITUENT.MIDDLENAME,
@MAIDENNAME = BATCHREVENUECONSTITUENT.MAIDENNAME,
@NICKNAME=BATCHREVENUECONSTITUENT.NICKNAME,
@TITLECODEID=(select DESCRIPTION from dbo.TITLECODE where ID = BATCHREVENUECONSTITUENT.TITLECODEID),
@SUFFIXCODEID=(select DESCRIPTION from dbo.SUFFIXCODE where ID = BATCHREVENUECONSTITUENT.SUFFIXCODEID),
@GENDERCODE=BATCHREVENUECONSTITUENT.GENDER,
@GENDERCODEID=(select dbo.UFN_GENDERCODE_GETDESCRIPTION(BATCHREVENUECONSTITUENT.GENDERCODEID)),
@BIRTHDATE=BATCHREVENUECONSTITUENT.BIRTHDATE,
@ADDRESS_ADDRESSTYPECODEID=(select DESCRIPTION from dbo.ADDRESSTYPECODE where ID = BATCHREVENUECONSTITUENT.ADDRESSTYPECODEID),
@ADDRESS_DONOTMAIL=BATCHREVENUECONSTITUENT.DONOTMAIL,
@ADDRESS_COUNTRYID=BATCHREVENUECONSTITUENT.COUNTRYID,
@ADDRESS_COUNTRY=(select DESCRIPTION from dbo.COUNTRY where ID = BATCHREVENUECONSTITUENT.COUNTRYID),
@ADDRESS_STATEID=(select ABBREVIATION from dbo.STATE where ID = BATCHREVENUECONSTITUENT.STATEID),
@ADDRESS_ADDRESSBLOCK=dbo.UFN_BUILDFULLADDRESS(null, BATCHREVENUECONSTITUENT.ADDRESSBLOCK, BATCHREVENUECONSTITUENT.CITY, BATCHREVENUECONSTITUENT.STATEID, BATCHREVENUECONSTITUENT.POSTCODE, BATCHREVENUECONSTITUENT.COUNTRYID),
@ADDRESS_CITY=BATCHREVENUECONSTITUENT.CITY,
@ADDRESS_POSTCODE=BATCHREVENUECONSTITUENT.POSTCODE,
@PHONE_PHONETYPECODEID=(select DESCRIPTION from dbo.PHONETYPECODE where ID = BATCHREVENUECONSTITUENT.PHONETYPECODEID),
@PHONE_NUMBER=BATCHREVENUECONSTITUENT.NUMBER,
@EMAILADDRESS_EMAILADDRESSTYPECODEID=(select DESCRIPTION from dbo.EMAILADDRESSTYPECODE where ID = BATCHREVENUECONSTITUENT.EMAILADDRESSTYPECODEID),
@EMAILADDRESS_EMAILADDRESS=BATCHREVENUECONSTITUENT.EMAILADDRESS,
@MARITALSTATUS=(select DESCRIPTION from dbo.MARITALSTATUSCODE where ID = BATCHREVENUECONSTITUENT.MARITALSTATUSCODEID),
@WEBADDRESS=BATCHREVENUECONSTITUENT.WEBADDRESS,
@INDUSTRYCODEID=(select DESCRIPTION from dbo.INDUSTRYCODE where ID = BATCHREVENUECONSTITUENT.INDUSTRYCODEID),
@NUMEMPLOYEES=BATCHREVENUECONSTITUENT.NUMEMPLOYEES,
@NUMSUBSIDIARIES=BATCHREVENUECONSTITUENT.NUMSUBSIDIARIES,
@PARENTCORPID=(select NAME from dbo.CONSTITUENT where ID = PARENTCORPID)
from dbo.BATCHREVENUECONSTITUENT where BATCHREVENUECONSTITUENT.ID = @ID
select @SPOUSEID=BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID,
@SPOUSE_LASTNAME=case when C.ID is not null then C.KEYNAME else BATCHREVENUECONSTITUENT.KEYNAME end,
@SPOUSE_FIRSTNAME=case when C.ID is not null then C.FIRSTNAME else BATCHREVENUECONSTITUENT.FIRSTNAME end,
@SPOUSE_MIDDLENAME=case when C.ID is not null then C.MIDDLENAME else BATCHREVENUECONSTITUENT.MIDDLENAME end,
@SPOUSE_MAIDENNAME=case when C.ID is not null then C.MAIDENNAME else BATCHREVENUECONSTITUENT.MAIDENNAME end,
@SPOUSE_NICKNAME=case when C.ID is not null then C.NICKNAME else BATCHREVENUECONSTITUENT.NICKNAME end,
@SPOUSE_TITLECODEID=(select DESCRIPTION from dbo.TITLECODE where ID = case when C.ID is not null then C.TITLECODEID else BATCHREVENUECONSTITUENT.TITLECODEID end),
@SPOUSE_SUFFIXCODEID=(select DESCRIPTION from dbo.SUFFIXCODE where ID = case when C.ID is not null then C.SUFFIXCODEID else BATCHREVENUECONSTITUENT.SUFFIXCODEID end),
@SPOUSE_GENDERCODE=case when C.ID is not null then C.GENDER else BATCHREVENUECONSTITUENT.GENDER end,
@SPOUSE_GENDERCODEID=(select dbo.UFN_GENDERCODE_GETDESCRIPTION(case when C.ID is not null then C.GENDERCODEID else BATCHREVENUECONSTITUENT.GENDERCODEID end)),
@SPOUSE_BIRTHDATE=case when C.ID is not null then C.BIRTHDATE else BATCHREVENUECONSTITUENT.BIRTHDATE end,
@SPOUSE_RECIPROCALTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID),
@SPOUSE_RELATIONSHIPTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = BATCHREVENUECONSTITUENTRELATION.RECIPROCALTYPECODEID),
@COPYPRIMARYINFORMATION=BATCHREVENUECONSTITUENTRELATION.COPYPRIMARYINFORMATION,
@SPOUSE_STARTDATE=BATCHREVENUECONSTITUENTRELATION.STARTDATE,
@PRIMARYRELATIONSHIPEXISTS=BATCHREVENUECONSTITUENTRELATION.PRIMARYRELATIONSHIPEXISTS,
@PRIMARYMATCHFACTOR=BATCHREVENUECONSTITUENTRELATION.PRIMARYMATCHFACTOR,
@RECIPROCALRELATIONSHIPEXISTS=BATCHREVENUECONSTITUENTRELATION.RECIPROCALRELATIONSHIPEXISTS,
@RECIPROCALMATCHFACTOR=BATCHREVENUECONSTITUENTRELATION.RECIPROCALMATCHFACTOR
from dbo.BATCHREVENUECONSTITUENTRELATION
left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.RELATIONID = BATCHREVENUECONSTITUENT.ID
left join dbo.CONSTITUENT C on BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID = C.ID
where BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHREVENUECONSTITUENT.ISORGANIZATION = 0
if @@ROWCOUNT = 0
set @EXISTINGSPOUSE = 1;
else
if @SPOUSEID is null
set @EXISTINGSPOUSE = 0;
else
set @EXISTINGSPOUSE = 1;
if @PRIMARYRELATIONSHIPEXISTS = 0
set @PRIMARYMATCHFACTOR = null;
if @RECIPROCALRELATIONSHIPEXISTS = 0
set @RECIPROCALMATCHFACTOR = null;
if @ISGROUP = 0 and @ISORGANIZATION = 0
begin
select
@INDIVIDUALSHOUSEHOLDNAME = case when BRC.EXISTINGCONSTITUENTID is not null then C_NF.NAME else BRC.NAME end
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
inner join dbo.BATCHREVENUECONSTITUENT BRC on BRCGM.GROUPID = BRC.ID and BRC.GROUPTYPECODE = 0
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BRC.EXISTINGCONSTITUENTID) C_NF
where BRCGM.MEMBERID = @ID
end
select @BUSINESSID=BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID,
@BUSINESS_NAME=case when C.ID is not null then C_NF.NAME else BATCHREVENUECONSTITUENT.NAME end,
@BUSINESS_ADDRESSTYPECODEID=case when C.ID is not null then
(select DESCRIPTION from dbo.ADDRESSTYPECODE where ID = CADDRESS.ADDRESSTYPECODEID)
else (select DESCRIPTION from dbo.ADDRESSTYPECODE where ID = BATCHREVENUECONSTITUENT.ADDRESSTYPECODEID) end,
@BUSINESS_COUNTRYID=case when C.ID is not null then CADDRESS.COUNTRYID else BATCHREVENUECONSTITUENT.COUNTRYID end,
@BUSINESS_COUNTRY=case when C.ID is not null then
(select DESCRIPTION from dbo.COUNTRY where ID = CADDRESS.COUNTRYID)
else (select DESCRIPTION from dbo.COUNTRY where ID = BATCHREVENUECONSTITUENT.COUNTRYID) end,
@BUSINESS_STATEID=case when C.ID is not null then
(select STATE.ABBREVIATION from dbo.STATE where ID = CADDRESS.STATEID)
else (select STATE.ABBREVIATION from dbo.STATE where ID = BATCHREVENUECONSTITUENT.STATEID) end,
@BUSINESS_ADDRESSBLOCK=case when C.ID is not null then
dbo.UFN_BUILDFULLADDRESS(null, CADDRESS.ADDRESSBLOCK, CADDRESS.CITY, CADDRESS.STATEID, CADDRESS.POSTCODE, CADDRESS.COUNTRYID)
else dbo.UFN_BUILDFULLADDRESS(null, BATCHREVENUECONSTITUENT.ADDRESSBLOCK, BATCHREVENUECONSTITUENT.CITY, BATCHREVENUECONSTITUENT.STATEID, BATCHREVENUECONSTITUENT.POSTCODE, BATCHREVENUECONSTITUENT.COUNTRYID) end,
@BUSINESS_DONOTMAIL=case when C.ID is not null then CADDRESS.DONOTMAIL else BATCHREVENUECONSTITUENT.DONOTMAIL end,
@BUSINESS_PHONETYPECODEID=case when C.ID is not null then
(select PHONETYPECODE.DESCRIPTION from dbo.PHONETYPECODE where PHONETYPECODE.ID = CPHONE.PHONETYPECODEID)
else (select PHONETYPECODE.DESCRIPTION from dbo.PHONETYPECODE where PHONETYPECODE.ID = BATCHREVENUECONSTITUENT.PHONETYPECODEID) end,
@BUSINESS_NUMBER=case when C.ID is not null then CPHONE.NUMBER else BATCHREVENUECONSTITUENT.NUMBER end,
--@BUSINESS_RECIPROCALTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = BATCHREVENUECONSTITUENTRELATION.RECIPROCALTYPECODEID),
--@BUSINESS_RELATIONSHIPTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID),
@BUSINESS_RECIPROCALTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID),
@BUSINESS_RELATIONSHIPTYPECODEID=(select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = BATCHREVENUECONSTITUENTRELATION.RECIPROCALTYPECODEID),
@BUSINESS_STARTDATE=BATCHREVENUECONSTITUENTRELATION.STARTDATE,
@ISCONTACT=BATCHREVENUECONSTITUENTRELATION.ISCONTACT,
@ISPRIMARYCONTACT=BATCHREVENUECONSTITUENTRELATION.ISPRIMARYCONTACT,
@CONTACTTYPECODEID=(select DESCRIPTION from dbo.CONTACTTYPECODE where ID = BATCHREVENUECONSTITUENTRELATION.CONTACTTYPECODEID),
@POSITION=BATCHREVENUECONSTITUENTRELATION.POSITION,
@ISMATCHINGGIFTRELATIONSHIP=BATCHREVENUECONSTITUENTRELATION.ISMATCHINGGIFTRELATIONSHIP,
@JOBCATEGORYCODEID=(select DESCRIPTION from dbo.JOBCATEGORYCODE where ID = BATCHREVENUECONSTITUENTRELATION.JOBCATEGORYCODEID),
@CAREERLEVELCODEID=(select DESCRIPTION from dbo.CAREERLEVELCODE where ID = BATCHREVENUECONSTITUENTRELATION.CAREERLEVELCODEID)
from dbo.BATCHREVENUECONSTITUENTRELATION
left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.RELATIONID = BATCHREVENUECONSTITUENT.ID
left join dbo.CONSTITUENT C on BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID = C.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) C_NF
left join dbo.ADDRESS CADDRESS on CADDRESS.CONSTITUENTID = C.ID and CADDRESS.ISPRIMARY = 1
left join dbo.PHONE CPHONE on CPHONE.CONSTITUENTID = C.ID and CPHONE.ISPRIMARY = 1
where BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHREVENUECONSTITUENTRELATION.ISPRIMARYBUSINESS = 1
if @@ROWCOUNT = 0
set @EXISTINGBUSINESS = 1;
else
if @BUSINESSID is null
set @EXISTINGBUSINESS = 0;
else
set @EXISTINGBUSINESS = 1;
end;
else
begin
select @ISORGANIZATION=ISORGANIZATION,
@ISGROUP=[MKTFINDERNUMBERCONSTITUENT].ISGROUP,
@ISHOUSEHOLD = case when [MKTFINDERNUMBERCONSTITUENT].GROUPTYPECODE = 0 and [MKTFINDERNUMBERCONSTITUENT].ISGROUP = 1 then 1 else 0 end,
@LASTNAME = [MKTFINDERNUMBERCONSTITUENT].KEYNAME,
@FIRSTNAME = [MKTFINDERNUMBERCONSTITUENT].FIRSTNAME,
@MIDDLENAME = [MKTFINDERNUMBERCONSTITUENT].MIDDLENAME,
@MAIDENNAME = [MKTFINDERNUMBERCONSTITUENT].MAIDENNAME,
@NICKNAME=[MKTFINDERNUMBERCONSTITUENT].NICKNAME,
@TITLECODEID=(select DESCRIPTION from dbo.TITLECODE where ID = [MKTFINDERNUMBERCONSTITUENT].TITLECODEID),
@SUFFIXCODEID=(select DESCRIPTION from dbo.SUFFIXCODE where ID = [MKTFINDERNUMBERCONSTITUENT].SUFFIXCODEID),
@GENDERCODE=[MKTFINDERNUMBERCONSTITUENT].GENDER,
@GENDERCODEID=(select dbo.UFN_GENDERCODE_GETDESCRIPTION(MKTFINDERNUMBERCONSTITUENT.GENDERCODEID)),
@BIRTHDATE=[MKTFINDERNUMBERCONSTITUENT].BIRTHDATE,
@ADDRESS_ADDRESSTYPECODEID=(select DESCRIPTION from dbo.ADDRESSTYPECODE where ID = [MKTFINDERNUMBERCONSTITUENT].ADDRESSTYPECODEID),
@ADDRESS_DONOTMAIL=[MKTFINDERNUMBERCONSTITUENT].DONOTMAIL,
@ADDRESS_COUNTRYID=[MKTFINDERNUMBERCONSTITUENT].COUNTRYID,
@ADDRESS_COUNTRY=(select DESCRIPTION from dbo.COUNTRY where ID = [MKTFINDERNUMBERCONSTITUENT].COUNTRYID),
@ADDRESS_STATEID=(select ABBREVIATION from dbo.STATE where ID = [MKTFINDERNUMBERCONSTITUENT].STATEID),
@ADDRESS_ADDRESSBLOCK=dbo.UFN_BUILDFULLADDRESS(null, [MKTFINDERNUMBERCONSTITUENT].ADDRESSBLOCK, [MKTFINDERNUMBERCONSTITUENT].CITY, [MKTFINDERNUMBERCONSTITUENT].STATEID, [MKTFINDERNUMBERCONSTITUENT].POSTCODE, [MKTFINDERNUMBERCONSTITUENT].COUNTRYID),
@ADDRESS_CITY=[MKTFINDERNUMBERCONSTITUENT].CITY,
@ADDRESS_POSTCODE=[MKTFINDERNUMBERCONSTITUENT].POSTCODE,
@PHONE_PHONETYPECODEID=(select DESCRIPTION from dbo.PHONETYPECODE where ID = [MKTFINDERNUMBERCONSTITUENT].PHONETYPECODEID),
@PHONE_NUMBER=[MKTFINDERNUMBERCONSTITUENT].NUMBER,
@EMAILADDRESS_EMAILADDRESSTYPECODEID=(select DESCRIPTION from dbo.EMAILADDRESSTYPECODE where ID = [MKTFINDERNUMBERCONSTITUENT].EMAILADDRESSTYPECODEID),
@EMAILADDRESS_EMAILADDRESS=[MKTFINDERNUMBERCONSTITUENT].EMAILADDRESS,
@MARITALSTATUS=(select DESCRIPTION from dbo.MARITALSTATUSCODE where ID = [MKTFINDERNUMBERCONSTITUENT].MARITALSTATUSCODEID),
@WEBADDRESS=[MKTFINDERNUMBERCONSTITUENT].WEBADDRESS,
@INDUSTRYCODEID=(select DESCRIPTION from dbo.INDUSTRYCODE where ID = [MKTFINDERNUMBERCONSTITUENT].INDUSTRYCODEID),
@NUMEMPLOYEES=[MKTFINDERNUMBERCONSTITUENT].NUMEMPLOYEES,
@NUMSUBSIDIARIES=[MKTFINDERNUMBERCONSTITUENT].NUMSUBSIDIARIES,
@PARENTCORPID=(select NAME from dbo.CONSTITUENT where ID = PARENTCORPID)
from dbo.[MKTFINDERNUMBERCONSTITUENT] where [MKTFINDERNUMBERCONSTITUENT].ID = @ID;
set @EXISTINGSPOUSE = 0;
set @PRIMARYMATCHFACTOR = null;
set @RECIPROCALMATCHFACTOR = null;
end
end;
if @ISORGANIZATION = 1
begin
set @ORGANIZATIONNAME = @LASTNAME;
set @ORGANIZATION_ADDRESSTYPECODEID=@ADDRESS_ADDRESSTYPECODEID;
set @ORGANIZATION_DONOTMAIL=@ADDRESS_DONOTMAIL;
set @ORGANIZATION_COUNTRYID=@ADDRESS_COUNTRYID;
set @ORGANIZATION_COUNTRY=@ADDRESS_COUNTRY;
set @ORGANIZATION_STATEID=@ADDRESS_STATEID;
set @ORGANIZATION_ADDRESSBLOCK=@ADDRESS_ADDRESSBLOCK;
set @ORGANIZATION_CITY=@ADDRESS_CITY;
set @ORGANIZATION_POSTCODE=@ADDRESS_POSTCODE;
set @ORGANIZATION_PHONETYPECODEID=@PHONE_PHONETYPECODEID;
set @ORGANIZATION_NUMBER=@PHONE_NUMBER;
set @ORGANIZATION_EMAILADDRESSTYPECODEID=@EMAILADDRESS_EMAILADDRESSTYPECODEID;
set @ORGANIZATION_EMAILADDRESS=@EMAILADDRESS_EMAILADDRESS;
end;
if @ISGROUP = 1
begin
declare @CURRENTDATEEARLIEST datetime;
set @CURRENTDATEEARLIEST = dbo.UFN_DATE_GETEARLIESTTIME(getdate())
declare @NUMBERMEMBERS int
declare @PRIMARYCONTACT nvarchar(154)
if @ISEXISTINGCONSTITUENT = 1
begin
select @NUMBERMEMBERS = count(GM.MEMBERID)
from dbo.GROUPMEMBER GM
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where GROUPID = @ID
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIEST))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIEST))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIEST and GMDR.DATETO > @CURRENTDATEEARLIEST))
select
@PRIMARYCONTACT = C_NF.NAME
from dbo.GROUPMEMBER GM
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GM.MEMBERID) C_NF
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.GROUPID = @ID and
GM.ISPRIMARY = 1
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIEST))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIEST))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIEST and GMDR.DATETO > @CURRENTDATEEARLIEST))
end
else
begin
select @NUMBERMEMBERS = count(GM.MEMBERID)
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER GM
where GROUPID = @ID
select
@PRIMARYCONTACT = case when C_NF.NAME is null then BRC.NAME else C_NF.NAME end
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
inner join dbo.BATCHREVENUECONSTITUENT BRC on BRCGM.MEMBERID = BRC.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BRC.EXISTINGCONSTITUENTID) C_NF
where
BRCGM.GROUPID = @ID and
BRCGM.ISPRIMARY = 1
end
if @ISHOUSEHOLD = 1
begin
if @ISEXISTINGCONSTITUENT = 1
begin
set @HOUSEHOLD_MEMBERS = (
select top 4
C_NF.NAME
from dbo.GROUPMEMBER GM
inner join dbo.CONSTITUENT C on GM.MEMBERID = C.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) C_NF
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.GROUPID = @ID
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIEST))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIEST))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIEST and GMDR.DATETO > @CURRENTDATEEARLIEST))
order by C.KEYNAME, C.FIRSTNAME
for xml raw('ITEM'), type, elements, root('HOUSEHOLD_MEMBERS'),binary base64
)
end
else
begin
set @HOUSEHOLD_MEMBERS = (
select top 4
case when C_NF.NAME is null then BRC.NAME else C_NF.NAME end NAME
from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
inner join dbo.BATCHREVENUECONSTITUENT BRC on BRCGM.MEMBERID = BRC.ID
left join dbo.CONSTITUENT C on BRC.EXISTINGCONSTITUENTID = C.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) C_NF
where
BRCGM.GROUPID = @ID
order by BRC.KEYNAME, BRC.FIRSTNAME, C.KEYNAME, C.FIRSTNAME
for xml raw('ITEM'), type, elements, root('HOUSEHOLD_MEMBERS'),binary base64
)
end
set @HOUSEHOLD_NAME = @LASTNAME
set @HOUSEHOLD_NUMBERMEMBERS = @NUMBERMEMBERS
set @HOUSEHOLD_PRIMARYCONTACT = @PRIMARYCONTACT
set @HOUSEHOLD_ADDRESSTYPECODEID=@ADDRESS_ADDRESSTYPECODEID;
set @HOUSEHOLD_COUNTRYID=@ADDRESS_COUNTRYID;
set @HOUSEHOLD_COUNTRY=@ADDRESS_COUNTRY;
set @HOUSEHOLD_STATEID=@ADDRESS_STATEID;
set @HOUSEHOLD_ADDRESSBLOCK=@ADDRESS_ADDRESSBLOCK;
set @HOUSEHOLD_CITY=@ADDRESS_CITY;
set @HOUSEHOLD_POSTCODE=@ADDRESS_POSTCODE;
set @HOUSEHOLD_PHONETYPECODEID=@PHONE_PHONETYPECODEID;
set @HOUSEHOLD_NUMBER=@PHONE_NUMBER;
set @HOUSEHOLD_EMAILADDRESSTYPECODEID=@EMAILADDRESS_EMAILADDRESSTYPECODEID;
set @HOUSEHOLD_EMAILADDRESS=@EMAILADDRESS_EMAILADDRESS;
end
else
begin
if @ISEXISTINGCONSTITUENT = 1
select @GROUP_TYPE = GT.NAME
from dbo.GROUPDATA GD
inner join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
where GD.ID = @ID
else
select @GROUP_TYPE = GT.NAME
from dbo.BATCHREVENUECONSTITUENT BRC
inner join dbo.GROUPTYPE GT on BRC.GROUPTYPEID = GT.ID
where BRC.ID = @ID
set @GROUP_NAME = @LASTNAME
set @GROUP_NUMBERMEMBERS = @NUMBERMEMBERS
set @GROUP_PRIMARYCONTACT = @PRIMARYCONTACT
set @GROUP_ADDRESSTYPECODEID=@ADDRESS_ADDRESSTYPECODEID;
set @GROUP_COUNTRYID=@ADDRESS_COUNTRYID;
set @GROUP_COUNTRY=@ADDRESS_COUNTRY;
set @GROUP_STATEID=@ADDRESS_STATEID;
set @GROUP_ADDRESSBLOCK=@ADDRESS_ADDRESSBLOCK;
set @GROUP_CITY=@ADDRESS_CITY;
set @GROUP_POSTCODE=@ADDRESS_POSTCODE;
set @GROUP_PHONETYPECODEID=@PHONE_PHONETYPECODEID;
set @GROUP_NUMBER=@PHONE_NUMBER;
set @GROUP_EMAILADDRESSTYPECODEID=@EMAILADDRESS_EMAILADDRESSTYPECODEID;
set @GROUP_EMAILADDRESS=@EMAILADDRESS_EMAILADDRESS;
end
end;
if @LASTNAME is not null
set @DATALOADED = -1;
if @ADDRESS_COUNTRYID is null
exec @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if @BUSINESS_COUNTRYID is null
exec @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end;