USP_MATCHFINDER_GETSYSTEMDATA_FORCONSTITUENT

            CREATE procedure dbo.USP_MATCHFINDER_GETSYSTEMDATA_FORCONSTITUENT(@CONSTITUENTID uniqueidentifier, @XMLOUTPUT xml = null output, @RETURNDATA bit = 1)
            as
            set nocount on;

            begin try
                declare @ADDRESSTYPECODEID uniqueidentifier;
                declare @PHONETYPECODEID uniqueidentifier;
                declare @FAXTYPECODEID uniqueidentifier;
                declare @CONTACTTYPECODEID uniqueidentifier;
                declare @CONTACTRELATIONSHIPTYPECODEID uniqueidentifier; 
                declare @CONTACTRECIPROCALRELATIONSHIPTYPECODEID uniqueidentifier;
                declare @CONTACTEMAILADDRESSTYPECODEID uniqueidentifier;
                declare @ALIASTYPECODEID uniqueidentifier;
                declare @MATCHINGGIFTCONDITIONTYPECODEID uniqueidentifier;

                exec dbo.USP_MATCHFINDER_LOADCONFIG 1, @ADDRESSTYPECODEID output, 1, @PHONETYPECODEID output, 1, @FAXTYPECODEID output, 
                                                    1, @CONTACTTYPECODEID output, 1, @CONTACTRELATIONSHIPTYPECODEID output, 
                                                    1, @CONTACTRECIPROCALRELATIONSHIPTYPECODEID output, 1, @CONTACTEMAILADDRESSTYPECODEID output, 
                                                    0, @ALIASTYPECODEID output, 1, @MATCHINGGIFTCONDITIONTYPECODEID output

                declare @R table
                (
                    ORGNAME nvarchar(100),
                    WEBADDRESS nvarchar(2047),
                    ADDRESS nvarchar(150),
                    PHONE nvarchar(100),
                    FAX nvarchar(100),
                    CONTACTID uniqueidentifier,
                    CONTACTNAME nvarchar(160),
                    CONTACTEMAIL nvarchar(100),
                    ALIAS nvarchar(100),
                    INDUSTRY nvarchar(100),
                    MATCHINGGIFTCONDITIONID uniqueidentifier,
                    MATCHINGFACTOR decimal(5,2),
                    MINMATCHPERGIFT money,
                    MAXMATCHPERGIFT money,
                    MAXMATCHANNUAL money,
                    MAXMATCHTOTAL money,
                    MATCHNOTES nvarchar(max),
                    CURRENCYID uniqueidentifier
                );

                insert into @R(ORGNAME, WEBADDRESS, ADDRESS, PHONE, FAX, CONTACTID, CONTACTNAME, CONTACTEMAIL, ALIAS, INDUSTRY, MATCHINGGIFTCONDITIONID, MATCHINGFACTOR, MINMATCHPERGIFT, MAXMATCHPERGIFT, MAXMATCHANNUAL, MAXMATCHTOTAL, MATCHNOTES, CURRENCYID)
                    select
                        CONSTITUENT.KEYNAME as [ORGNAME],
                        CONSTITUENT.WEBADDRESS as [WEBADDRESS],
                        case when ADDRESS.ID is null then N'' 
                             else dbo.UFN_BUILDFULLADDRESS([ADDRESS].ID, [ADDRESS].ADDRESSBLOCK, [ADDRESS].CITY, [ADDRESS].STATEID, [ADDRESS].POSTCODE, [ADDRESS].COUNTRYID) end as [ADDRESS],
                        case when PHONE.ID is null then N''
                            else PHONE.NUMBER end as [PHONE],
                        case when [FAX].ID is null then N''
                            else [FAX].NUMBER end as [FAX],
                        [CONTACT].ID as [CONTACTID],
                        case when [CONTACT].ID is null then N''
                            else [CONTACT].NAME end as [CONTACTNAME],
                        case when EMAILADDRESS.ID is null then N''
                            else EMAILADDRESS.EMAILADDRESS end as [CONTACTEMAIL],
                        case when [ALIAS].ID is null then N''
                            else [ALIAS].ALIAS end as [ALIAS],
                        case when ORGANIZATIONDATA.INDUSTRYCODEID is null then N''
                            else (select DESCRIPTION from dbo.INDUSTRYCODE where INDUSTRYCODE.ID = ORGANIZATIONDATA.INDUSTRYCODEID) end as [INDUSTRY],
                        MATCHINGGIFTCONDITION.ID as [MATCHINGGIFTCONDITIONID],
                        case when MATCHINGGIFTCONDITION.ID is null then 0
                            else MATCHINGGIFTCONDITION.MATCHINGFACTOR end as [MATCHINGFACTOR],
                        case when MATCHINGGIFTCONDITION.ID is null then 0
                            else MATCHINGGIFTCONDITION.MINMATCHPERGIFT end as [MINMATCHPERGIFT],
                        case when MATCHINGGIFTCONDITION.ID is null then 0
                            else MATCHINGGIFTCONDITION.MAXMATCHPERGIFT end as [MAXMATCHPERGIFT],
                        case when MATCHINGGIFTCONDITION.ID is null then 0
                            else MATCHINGGIFTCONDITION.MAXMATCHANNUAL end as [MAXMATCHANNUAL],
                        case when MATCHINGGIFTCONDITION.ID is null then 0
                            else MATCHINGGIFTCONDITION.MAXMATCHTOTAL end as [MAXMATCHTOTAL],
                        case when MATCHINGGIFTCONDITION.ID is null then N''
                            else MATCHINGGIFTCONDITION.NOTES end as [MATCHNOTES],
                        case when MATCHINGGIFTCONDITION.ID is null then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                            else MATCHINGGIFTCONDITION.BASECURRENCYID end as [CURRENCYID]
                    from dbo.CONSTITUENT
                    left join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = CONSTITUENT.ID
                    left join 
                    (
                        select top 1
                            ADDRESS.CONSTITUENTID as [ORGID],
                            ADDRESS.ID,
                            ADDRESS.ADDRESSBLOCK,
                            ADDRESS.CITY,
                            ADDRESS.STATEID,
                            ADDRESS.POSTCODE, 
                            ADDRESS.COUNTRYID
                        from dbo.ADDRESS
                        where ADDRESS.CONSTITUENTID = @CONSTITUENTID
                            and ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID
                        order by ADDRESS.ISPRIMARY desc, ADDRESS.CITY, ADDRESS.ADDRESSBLOCK
                    ) as [ADDRESS] on [ADDRESS].ORGID = CONSTITUENT.ID
                    left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.PHONETYPECODEID = @PHONETYPECODEID
                    left join dbo.PHONE as [FAX] on [FAX].CONSTITUENTID = CONSTITUENT.ID and [FAX].PHONETYPECODEID = @FAXTYPECODEID
                    left join
                    (
                        --select top 0

                            --@CONSTITUENTID as [ORGID],

                            --CONSTITUENT.NAME,

                            --CONSTITUENT.ID

                        --from dbo.CONSTITUENT


                        select top 1
                            @CONSTITUENTID as [ORGID],
                            [CONTACTCONSTITUENT].NAME,
                            [CONTACTCONSTITUENT].ID                        
                        from dbo.RELATIONSHIP
                        inner join dbo.CONSTITUENT as [CONTACTCONSTITUENT] on [CONTACTCONSTITUENT].ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                        where RELATIONSHIP.RECIPROCALCONSTITUENTID = @CONSTITUENTID
                        and RELATIONSHIP.ISCONTACT = 1
                        and RELATIONSHIP.CONTACTTYPECODEID = @CONTACTTYPECODEID
                        order by RELATIONSHIP.ISPRIMARYCONTACT desc, [CONTACTCONSTITUENT].KEYNAME, [CONTACTCONSTITUENT].FIRSTNAME, [CONTACTCONSTITUENT].MIDDLENAME, RELATIONSHIP.DATEADDED
                    ) as [CONTACT] on [CONTACT].ORGID = CONSTITUENT.ID
                    left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = [CONTACT].ID and EMAILADDRESS.EMAILADDRESSTYPECODEID = @CONTACTEMAILADDRESSTYPECODEID
                    left join
                    (
                        select top 1
                            @CONSTITUENTID as [ORGID],
                            ALIAS.KEYNAME as [ALIAS],
                            ALIAS.ID 
                        from dbo.ALIAS
                        where ALIAS.CONSTITUENTID = @CONSTITUENTID 
                            and ALIAS.ALIASTYPECODEID = @ALIASTYPECODEID
                    ) as [ALIAS] on [ALIAS].ORGID = CONSTITUENT.ID
                    left join dbo.MATCHINGGIFTCONDITION on MATCHINGGIFTCONDITION.ORGANIZATIONID = CONSTITUENT.ID and MATCHINGGIFTCONDITION.MATCHINGGIFTCONDITIONTYPECODEID = @MATCHINGGIFTCONDITIONTYPECODEID
                    where CONSTITUENT.ID = @CONSTITUENTID
                    and CONSTITUENT.ISORGANIZATION = 1

                    --I want to have this wrapped up for other procedures to be able to call to get the data. I can't use a TVF because I want to call raiserror

                    --when various settings aren't configured (at the top of this SP). So pass the data out as XML. Only do the final select if the caller 

                    --passes 1 for @RETURNDATA (so that the caller can choose to use the XML, the result set from this SP, or both)

                    set @XMLOUTPUT = 
                    (
                        select 
                            ORGNAME, WEBADDRESS, ADDRESS, PHONE, FAX, CONTACTID, CONTACTNAME, CONTACTEMAIL, ALIAS, INDUSTRY, MATCHINGGIFTCONDITIONID, MATCHINGFACTOR, MINMATCHPERGIFT, MAXMATCHPERGIFT, MAXMATCHANNUAL, MAXMATCHTOTAL, MATCHNOTES, CURRENCYID
                        from @R 
                        for xml raw('ITEM'), type, elements, root('SYSTEMDATA'), binary base64

                    )        

                    if @RETURNDATA = 1
                        select
                            ORGNAME, WEBADDRESS, ADDRESS, PHONE, FAX, CONTACTID, CONTACTNAME, CONTACTEMAIL, ALIAS, INDUSTRY, MATCHINGGIFTCONDITIONID, MATCHINGFACTOR, MINMATCHPERGIFT, MAXMATCHPERGIFT, MAXMATCHANNUAL, MAXMATCHTOTAL, MATCHNOTES, CURRENCYID
                        from @R;

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;