USP_DATAFORMTEMPLATE_LOAD_REVENUEBATCHCONSTITUENT

The load procedure used by the view dataform template "Revenue Batch Constituent View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ISORGANIZATION	bit	INOUT	Is organization
@ISEXISTINGCONSTITUENT	bit	INOUT	Is existing constituent
@LASTNAME	nvarchar(100)	INOUT	Last name
@ORGANIZATIONNAME	nvarchar(100)	INOUT	Org name
@FIRSTNAME	nvarchar(50)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@MAIDENNAME	nvarchar(100)	INOUT	Maiden name
@NICKNAME	nvarchar(50)	INOUT	Nickname
@TITLECODEID	nvarchar(50)	INOUT	Title
@SUFFIXCODEID	nvarchar(50)	INOUT	Suffix
@GENDERCODE	nvarchar(12)	INOUT	Gender
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@ADDRESS_ADDRESSTYPECODEID	nvarchar(50)	INOUT	Address type
@ADDRESS_DONOTMAIL	bit	INOUT	Do not send mail to this address
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT	Country
@ADDRESS_COUNTRY	nvarchar(50)	INOUT	Country
@ADDRESS_STATEID	nvarchar(50)	INOUT	State
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@ADDRESS_CITY	nvarchar(50)	INOUT	City
@ADDRESS_POSTCODE	nvarchar(12)	INOUT	ZIP
@PHONE_PHONETYPECODEID	nvarchar(50)	INOUT	Phone type
@PHONE_NUMBER	nvarchar(100)	INOUT	Phone number
@EMAILADDRESS_EMAILADDRESSTYPECODEID	nvarchar(50)	INOUT	Email type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@ORGANIZATION_ADDRESSTYPECODEID	nvarchar(50)	INOUT	Address type
@ORGANIZATION_DONOTMAIL	bit	INOUT	Do not send mail to this address
@ORGANIZATION_COUNTRYID	uniqueidentifier	INOUT	Country
@ORGANIZATION_COUNTRY	nvarchar(50)	INOUT	Country
@ORGANIZATION_STATEID	nvarchar(50)	INOUT	State
@ORGANIZATION_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@ORGANIZATION_CITY	nvarchar(50)	INOUT	City
@ORGANIZATION_POSTCODE	nvarchar(12)	INOUT	ZIP
@ORGANIZATION_PHONETYPECODEID	nvarchar(50)	INOUT	Phone type
@ORGANIZATION_NUMBER	nvarchar(100)	INOUT	Phone number
@ORGANIZATION_EMAILADDRESSTYPECODEID	nvarchar(50)	INOUT	Email type
@ORGANIZATION_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@MARITALSTATUS	nvarchar(50)	INOUT	Marital status
@WEBADDRESS	nvarchar(100)	INOUT	Website
@INDUSTRYCODEID	nvarchar(100)	INOUT	Industry
@NUMEMPLOYEES	int	INOUT	Number of employees
@NUMSUBSIDIARIES	int	INOUT	Number of subsidiaries
@PARENTCORPID	nvarchar(100)	INOUT	Parent org
@SPOUSEID	uniqueidentifier	INOUT	Name
@EXISTINGSPOUSE	bit	INOUT	Existing constituent
@SPOUSE_LASTNAME	nvarchar(100)	INOUT	Last name
@SPOUSE_FIRSTNAME	nvarchar(50)	INOUT	First name
@SPOUSE_MIDDLENAME	nvarchar(50)	INOUT	Middle name
@SPOUSE_MAIDENNAME	nvarchar(100)	INOUT	Maiden name
@SPOUSE_NICKNAME	nvarchar(50)	INOUT	Nickname
@SPOUSE_TITLECODEID	nvarchar(50)	INOUT	Title
@SPOUSE_SUFFIXCODEID	nvarchar(50)	INOUT	Suffix
@SPOUSE_GENDERCODE	nvarchar(50)	INOUT	Gender
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@SPOUSE_RECIPROCALTYPECODEID	nvarchar(50)	INOUT	Reciprocal relationship type
@SPOUSE_RELATIONSHIPTYPECODEID	nvarchar(50)	INOUT	Relationship type
@COPYPRIMARYINFORMATION	bit	INOUT	Copy primary information
@SPOUSE_STARTDATE	datetime	INOUT	Start date
@PRIMARYRELATIONSHIPEXISTS	bit	INOUT	Apply recognition credit to individual for constituent's payments
@PRIMARYMATCHFACTOR	decimal(5, 2)	INOUT	Recognition credit match percentage
@RECIPROCALRELATIONSHIPEXISTS	bit	INOUT	Apply recognition credit to constituent for individual's payments
@RECIPROCALMATCHFACTOR	decimal(5, 2)	INOUT	Reciprocal recognition credit match percentage
@BUSINESSID	uniqueidentifier	INOUT	Org name
@EXISTINGBUSINESS	bit	INOUT	Existing constituent
@BUSINESS_NAME	nvarchar(100)	INOUT	Org name
@BUSINESS_ADDRESSTYPECODEID	nvarchar(50)	INOUT	Address type
@BUSINESS_COUNTRYID	uniqueidentifier	INOUT	Country
@BUSINESS_COUNTRY	nvarchar(50)	INOUT	Country
@BUSINESS_STATEID	nvarchar(50)	INOUT	State
@BUSINESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@BUSINESS_CITY	nvarchar(50)	INOUT	City
@BUSINESS_POSTCODE	nvarchar(12)	INOUT	ZIP
@BUSINESS_DONOTMAIL	bit	INOUT	Do not send mail to this address
@BUSINESS_PHONETYPECODEID	nvarchar(50)	INOUT	Phone type
@BUSINESS_NUMBER	nvarchar(100)	INOUT	Phone number
@BUSINESS_RECIPROCALTYPECODEID	nvarchar(50)	INOUT	Reciprocal relationship type
@BUSINESS_RELATIONSHIPTYPECODEID	nvarchar(50)	INOUT	Relationship type
@BUSINESS_STARTDATE	datetime	INOUT	Start date
@ISCONTACT	bit	INOUT	Is contact
@ISPRIMARYCONTACT	bit	INOUT	Is primary contact
@CONTACTTYPECODEID	nvarchar(50)	INOUT	Contact type
@POSITION	nvarchar(50)	INOUT	Position
@ISMATCHINGGIFTRELATIONSHIP	bit	INOUT	Matching gift relationship
@INDIVIDUALSHOUSEHOLDNAME	nvarchar(100)	INOUT	Household name
@ISGROUP	bit	INOUT	Is group
@GROUP_NAME	nvarchar(100)	INOUT	Group name
@GROUP_TYPE	nvarchar(150)	INOUT	Group type
@GROUP_NUMBERMEMBERS	int	INOUT	No. of members
@GROUP_PRIMARYCONTACT	nvarchar(154)	INOUT	Primary contact
@GROUP_ADDRESSTYPECODEID	nvarchar(50)	INOUT	Address type
@GROUP_COUNTRYID	uniqueidentifier	INOUT	Country
@GROUP_COUNTRY	nvarchar(50)	INOUT	Country
@GROUP_STATEID	nvarchar(50)	INOUT	State
@GROUP_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@GROUP_CITY	nvarchar(50)	INOUT	City
@GROUP_POSTCODE	nvarchar(12)	INOUT	ZIP
@GROUP_PHONETYPECODEID	nvarchar(50)	INOUT	Phone type
@GROUP_NUMBER	nvarchar(100)	INOUT	Phone number
@GROUP_EMAILADDRESSTYPECODEID	nvarchar(50)	INOUT	Email type
@GROUP_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@ISHOUSEHOLD	bit	INOUT	Is household
@HOUSEHOLD_NAME	nvarchar(100)	INOUT	Household name
@HOUSEHOLD_NUMBERMEMBERS	int	INOUT	No. of members
@HOUSEHOLD_PRIMARYCONTACT	nvarchar(154)	INOUT	Primary contact
@HOUSEHOLD_MEMBERS	xml	INOUT	Members
@HOUSEHOLD_ADDRESSTYPECODEID	nvarchar(50)	INOUT	Address type
@HOUSEHOLD_COUNTRYID	uniqueidentifier	INOUT	Country
@HOUSEHOLD_COUNTRY	nvarchar(50)	INOUT	Country
@HOUSEHOLD_STATEID	nvarchar(50)	INOUT	State
@HOUSEHOLD_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@HOUSEHOLD_CITY	nvarchar(50)	INOUT	City
@HOUSEHOLD_POSTCODE	nvarchar(12)	INOUT	ZIP
@HOUSEHOLD_PHONETYPECODEID	nvarchar(50)	INOUT	Phone type
@HOUSEHOLD_NUMBER	nvarchar(100)	INOUT	Phone number
@HOUSEHOLD_EMAILADDRESSTYPECODEID	nvarchar(50)	INOUT	Email type
@HOUSEHOLD_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_REVENUEBATCHCONSTITUENT(
                        @ID uniqueidentifier,
                        @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(50) = 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

) as begin
    set nocount on;

    begin try

    set @ISEXISTINGCONSTITUENT = 0;
    select @ISEXISTINGCONSTITUENT=1 from dbo.CONSTITUENT where CONSTITUENT.ID = @ID;

    if @ISEXISTINGCONSTITUENT = 1        
            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,
                @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

    else
        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,
                    @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_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.NAME else BRC.NAME end
                        from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
                        inner join dbo.BATCHREVENUECONSTITUENT BRC on BRCGM.GROUPID = BRC.ID and BRC.GROUPTYPECODE = 0
                        left join dbo.CONSTITUENT C on BRC.EXISTINGCONSTITUENTID = C.ID
                        where BRCGM.MEMBERID = @ID
                    end

                select @BUSINESSID=BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID,
                    @BUSINESS_NAME=BATCHREVENUECONSTITUENT.NAME,
                    @BUSINESS_ADDRESSTYPECODEID=(select DESCRIPTION from dbo.ADDRESSTYPECODE where ID = BATCHREVENUECONSTITUENT.ADDRESSTYPECODEID),
                    @BUSINESS_COUNTRYID=BATCHREVENUECONSTITUENT.COUNTRYID,
                    @BUSINESS_COUNTRY=(select DESCRIPTION from dbo.COUNTRY where ID = BATCHREVENUECONSTITUENT.COUNTRYID),
                    @BUSINESS_STATEID=(select STATE.ABBREVIATION from dbo.STATE where ID = BATCHREVENUECONSTITUENT.STATEID),
                    @BUSINESS_ADDRESSBLOCK=dbo.UFN_BUILDFULLADDRESS(null, BATCHREVENUECONSTITUENT.ADDRESSBLOCK, BATCHREVENUECONSTITUENT.CITY, BATCHREVENUECONSTITUENT.STATEID, BATCHREVENUECONSTITUENT.POSTCODE, BATCHREVENUECONSTITUENT.COUNTRYID),                    
                    @BUSINESS_DONOTMAIL=BATCHREVENUECONSTITUENT.DONOTMAIL,
                    @BUSINESS_PHONETYPECODEID=(select PHONETYPECODE.DESCRIPTION from dbo.PHONETYPECODE where PHONETYPECODE.ID = BATCHREVENUECONSTITUENT.PHONETYPECODEID),
                    @BUSINESS_NUMBER=BATCHREVENUECONSTITUENT.NUMBER,
                    --@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
                    from dbo.BATCHREVENUECONSTITUENTRELATION 
                    left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.RELATIONID = BATCHREVENUECONSTITUENT.ID
                    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;

        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.NAME
                        from dbo.GROUPMEMBER GM
                        inner join dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                        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.NAME is null then BRC.NAME else C.NAME end
                        from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
                        inner join dbo.BATCHREVENUECONSTITUENT BRC on BRCGM.MEMBERID = BRC.ID
                        left join dbo.CONSTITUENT C on BRC.EXISTINGCONSTITUENTID = C.ID
                        where
                            BRCGM.GROUPID = @ID and
                            BRCGM.ISPRIMARY = 1
                    end

                if @ISHOUSEHOLD = 1
                    begin
                        if @ISEXISTINGCONSTITUENT = 1
                            begin
                                set @HOUSEHOLD_MEMBERS =    (
                                                                select top 4
                                                                    C.NAME
                                                                from dbo.GROUPMEMBER GM
                                                                inner join dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                                                                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.NAME is null then BRC.NAME else C.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
                                                                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;