USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYTILEPANEL

CREATE  procedure [dbo].[USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYTILEPANEL]
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier=null,
    @DATALOADED bit = 0 output,

    @AVAILABLETILES xml = null output,
    @SELECTEDTILES xml = null output,

    @USERHASRIGHTSTOCUSTOMIZETILESFORM bit = null output,
    @USERSELECTEDNOTILES bit = null output,
    @CONSTITUENTTYPECODE tinyint = null output,
    @LOOKUPID nvarchar(100) = null output,
    @ISDECEASED bit = null output,
    @ISINACTIVE bit = null output,
    @ISDISSOLVED bit = null output,
    @DECLARATIONSONFILE bit = null output,
    @CONSTITUENCIESDISPLAYORDER xml = null output,
    @ADVOCATECONSTITUENCYTEXT nvarchar(100) = null output,
    @ALUMNUSENROLLMENTID uniqueidentifier = null output,
    @ALUMNUSCONSTITUENCYTEXT nvarchar(100) = null output,
    @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
    @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @FACULTYCONSTITUENCYTEXT nvarchar(100) = null output,
    @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
    @FUNDRAISINGGROUPCONSTITUENCYTEXT nvarchar(100) = null output,
    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MATCHFINDERONLINERECORDID int = null output,
    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
    @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @NFGCONSTITUENCYTEXT nvarchar(100) = null output,
    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100)=null output,
    @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
    @RELATIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
    @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
    @STUDENTENROLLMENTID uniqueidentifier = null output,
    @STUDENTCONSTITUENCYTEXT nvarchar(100) = null output,
    @STUDENTRELATIONCONSTITUENCYTEXT nvarchar(4000) = null output,
    @STUDENTRELATIONCONSTITUENCIES xml = null output,
    @VENDORSTATUSTEXT nvarchar(100) = null output,
    @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,    
    @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
    @USERDEFINEDCONSTITUENCIES xml = null output,
    @SUMMARYTILECONTEXTID uniqueidentifier = null output,
    @STICKYSHOWTILES bit = null output
as
    set nocount on;

        declare @CURRENTDATE date;
        set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

         -- Pull sticky settings to determine if tiles should be shown

        select
            @STICKYSHOWTILES = SHOWTILES
        from 
            dbo.USERSETTINGSCONSTITUENTSUMMARYTILES
        where
            ID = @CURRENTAPPUSERID

        -- Default to show tiles if no settings found

        if @STICKYSHOWTILES is null
            set @STICKYSHOWTILES = 1;

    set @USERHASRIGHTSTOCUSTOMIZETILESFORM = 0;
    if (
        dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or
        dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'cc38d921-1c31-4042-9c00-015e1914b225') = 1 --ConstituentSummaryUpdateTiles.RecordOperation

    )
        set @USERHASRIGHTSTOCUSTOMIZETILESFORM = 1;

    set @CONSTITUENTTYPECODE = 0
    if dbo.UFN_CONSTITUENT_ISORGANIZATION(@ID) = 1
    begin
        set @CONSTITUENTTYPECODE = 1
    end
    else
    begin
        if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@ID) = 1
        begin
            set @CONSTITUENTTYPECODE = 2
        end
        else
        begin
            if dbo.UFN_CONSTITUENT_ISGROUP(@ID) = 1
            begin
                set @CONSTITUENTTYPECODE = 3
            end
        end
    end

    set @SUMMARYTILECONTEXTID =
        dbo.UFN_CONSTITUENTSUMMARY_GETSUMMARYTILECONTEXTFORCONSTITUENTTYPECODE(@CONSTITUENTTYPECODE);

    select
        @LOOKUPID = CONSTITUENT.LOOKUPID,
        @ISINACTIVE = CONSTITUENT.ISINACTIVE,
        @DATALOADED = 1
    from dbo.CONSTITUENT
    where CONSTITUENT.ID = @ID;

    set @ISDISSOLVED = 0;
    select
        @ISDISSOLVED = 1
    from dbo.DISSOLVEDGROUP
    where DISSOLVEDGROUP.ID = @ID;

    set @ISDECEASED = 0;
    select
        @ISDECEASED = 1
    from dbo.DECEASEDCONSTITUENT
    where DECEASEDCONSTITUENT.ID = @ID;

    select 
        @DECLARATIONSONFILE = case  
            when count(*) > 0 then 1
            else 0
        end
    from 
        dbo.TAXDECLARATION
    where 
        TAXDECLARATION.CONSTITUENTID = @ID and 
        (
            (TAXDECLARATION.DECLARATIONSTARTS <= getdate() and TAXDECLARATION.DECLARATIONENDS >= getdate())
            or (TAXDECLARATION.DECLARATIONSTARTS <= getdate() and TAXDECLARATION.DECLARATIONENDS is null)
        ) and 
        exists (select PERMISSIONGRANTED from dbo.UFN_APPUSER_HASPERMISSIONFORDECLARATION(@CURRENTAPPUSERID, TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID));

    set @AVAILABLETILES = (
        select
            AVAILABLETILES.ID,
            AVAILABLETILES.DATAFORMINSTANCECATALOGID as TILEID,
            AVAILABLETILES.TILENAME,
            AVAILABLETILES.SEQUENCE
        from dbo.UFN_APPUSER_GETAVAILABLESUMMARYTILES(@CURRENTAPPUSERID, @SUMMARYTILECONTEXTID) as AVAILABLETILES
        order by AVAILABLETILES.SEQUENCE
        for xml raw('ITEM'),type,elements,root('AVAILABLETILES'),BINARY BASE64
    );

    declare @APPUSERSUMMARYTILECONTEXTID uniqueidentifier;
        select 
        @APPUSERSUMMARYTILECONTEXTID = ID
    from dbo.APPUSERSUMMARYTILECONTEXT
        where
        APPUSERID = @CURRENTAPPUSERID
        and SUMMARYTILECONTEXTID = @SUMMARYTILECONTEXTID;

    set @SELECTEDTILES = (
        select
            SELECTEDTILES.DATAFORMINSTANCECATALOGID as TILEID,
            SELECTEDTILES.SEQUENCE,
            SELECTEDTILES.AVAILABLESUMMARYTILEID as CONSTITUENTSUMMARYAVAILABLETILEID
        from dbo.UFN_APPUSERSUMMARYTILECONTEXT_GETSELECTEDTILES(@APPUSERSUMMARYTILECONTEXTID) as SELECTEDTILES
        order by SELECTEDTILES.SEQUENCE
        for xml raw('ITEM'),type,elements,root('SELECTEDTILES'),BINARY BASE64
    );

    --Determine if the user manually selected to show no tiles.

    if @APPUSERSUMMARYTILECONTEXTID is not null and @SELECTEDTILES is null
    begin
        set @USERSELECTEDNOTILES = 1;
    end
    else
    begin
        set @USERSELECTEDNOTILES = 0;

        if @SELECTEDTILES is null
        begin
            set @SELECTEDTILES = (
                select 
                    AVAILABLETILES.DATAFORMINSTANCECATALOGID as TILEID,
                    AVAILABLETILES.SEQUENCE,
                    AVAILABLETILES.ID as CONSTITUENTSUMMARYAVAILABLETILEID
                from dbo.UFN_APPUSER_GETAVAILABLESUMMARYTILES(@CURRENTAPPUSERID, @SUMMARYTILECONTEXTID) as AVAILABLETILES
                where
                    AVAILABLETILES.ISVISIBLEBYDEFAULT = 1
                order by AVAILABLETILES.SEQUENCE
                for xml raw('ITEM'),type,elements,root('SELECTEDTILES'),BINARY BASE64
            )
        end
    end

    set @CONSTITUENCIESDISPLAYORDER = (
        select 
            ID,
            DESCRIPTION,
            SEQUENCE,
            ISSYSTEM
        from dbo.V_CONSTITUENCYDISPLAYORDER
        order by SEQUENCE, DESCRIPTION
        for xml raw('ITEM'),type,elements,root('CONSTITUENCIESDISPLAYORDER'),BINARY BASE64
    )

    /************************************/
    /******  Constituency fields  *******/
    /************************************/
    declare @HASCONSTITUENCY as bit = 0;

    set    @ADVOCATECONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C6899') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISADVOCATE(@ID) = 1
        begin
            set @ADVOCATECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C6899');

            if(len(isnull(@ADVOCATECONSTITUENCYTEXT,'')) > 0)
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID);
    set @ALUMNUSCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISALUMNUS(@ID) = 1
        begin
            set @ALUMNUSCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('46EC3424-BA54-4431-A7DC-C6CEBB3B4592');

            if(len(isnull(@ALUMNUSCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end
    if @ALUMNUSCONSTITUENCYTEXT is not null 
    begin
        set @ALUMNUSENROLLMENTID = (
            select top 1 (EDUCATIONALHISTORY.ID) 
            from dbo.EDUCATIONALHISTORY 
                inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
                inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORYSTATUS.ID = EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID
            where EDUCATIONALINSTITUTION.ISAFFILIATED = 1 
                and EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 1
                and EDUCATIONALHISTORY.CONSTITUENTID = @ID
            order by dbo.EDUCATIONALHISTORY.DATEADDED
        )
    end

    set @BANKCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '171AB3CD-C4E1-4825-B693-10F524A7A594') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISBANK(@ID) = 1
        begin
            set @BANKCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594');

            if(len(isnull(@BANKCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @BOARDMEMBERCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1
        begin
            set @BOARDMEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF');

            if(len(isnull(@BOARDMEMBERCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @COMMITTEECONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISCOMMITTEE(@ID) = 1
        begin
            set @COMMITTEECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799');

            if(len(isnull(@COMMITTEECONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @COMMITTEEMEMBERCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '093A3D4F-2974-447F-AD92-870EB4A04593') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID) = 1
        begin
            set @COMMITTEEMEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593');

            if(len(isnull(@COMMITTEEMEMBERCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @DONORCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '70165682-4324-46EC-9439-83FC0CC67E7F') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISDONOR(@ID) = 1 
        begin
            set @DONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F');

            if(len(isnull(@DONORCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @FACULTYCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '08D55D6A-10C8-4a72-92A0-EF87033AD7B6') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISFACULTY(@ID) = 1 
        begin
            set @FACULTYCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('08D55D6A-10C8-4a72-92A0-EF87033AD7B6');
        end
    end

    set @FUNDRAISERCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID) = 1
        begin
            set @FUNDRAISERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923');

            if(len(isnull(@FUNDRAISERCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @FUNDRAISINGGROUPCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '264818E9-3F59-44C1-9BBF-1BB1F9C78CA5') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISFUNDRAISINGGROUP(@ID) = 1
        begin
            set @FUNDRAISINGGROUPCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('264818E9-3F59-44C1-9BBF-1BB1F9C78CA5');

            if(len(isnull(@FUNDRAISINGGROUPCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @GRANTORCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'D9982C99-15C1-4C90-873E-56FD4B164056') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISGRANTOR(@ID) = 1
        begin
            set @GRANTORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D9982C99-15C1-4C90-873E-56FD4B164056');

            if(len(isnull(@GRANTORCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @LOYALDONORCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 1
        begin
            set @LOYALDONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B');

            if(len(isnull(@LOYALDONORCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @MAJORDONORCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '1A9BFE80-604D-4B5B-8065-E751DDF6EF39') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 1
        begin
            set @MAJORDONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39');

            if(len(isnull(@MAJORDONORCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    if len(@LOYALDONORCONSTITUENCYTEXT) > 0 or len(@MAJORDONORCONSTITUENCYTEXT) > 0
    begin
        set @DONORCONSTITUENCYTEXT = null;
    end

    set @MATCHFINDERCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '2D04A9C5-27D0-4646-BF0F-6826E4C12632') = 1
    begin
    set @MATCHFINDERONLINERECORDID = 0;    
    select top 1
        @MATCHFINDERONLINERECORDID = MATCHFINDERRECORDID 
    from dbo.MATCHFINDERCONSTITUENT
    where ID = @ID;
        if not @MATCHFINDERONLINERECORDID is null and @MATCHFINDERONLINERECORDID <> 0
        begin
            set @MATCHFINDERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2D04A9C5-27D0-4646-BF0F-6826E4C12632');

            if(len(isnull(@MATCHFINDERCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @MEMBERCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '2d11326e-8f3b-4322-9797-57c1aacfa5df') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISMEMBER(@ID,@CURRENTAPPUSERID) = 1 
        begin
            set @MEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df');

            if(len(isnull(@MEMBERCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @NFGCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'A499228A-7619-40DF-AE75-BBA1EE20FB4E') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISNATIONALFUNDRAISINGGROUP(@ID) = 1
        begin
            set @NFGCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A499228A-7619-40DF-AE75-BBA1EE20FB4E')
        end
    end

    set @PATRONCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'A843B859-4C6B-445B-97F3-179582E270A5') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1
        begin
            set @PATRONCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5');

            if(len(isnull(@PATRONCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @PLANNEDGIVERCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID) = 1
        begin
            set @PLANNEDGIVERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634');

            if(len(isnull(@PLANNEDGIVERCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @PROSPECTCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '00E748FB-940D-4A7D-A133-C148B29410A8') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISPROSPECT(@ID) = 1
        begin
            set @PROSPECTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8');

            if(len(isnull(@PROSPECTCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @RECOGNITIONCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '3dfac92e-78bd-4051-abdc-02c675deb8f6') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISRECOGNITION(@ID,@CURRENTAPPUSERID) = 1
        begin
            set @RECOGNITIONCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6');

            if(len(isnull(@RECOGNITIONCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @REGISTRANTSTATUSTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'C49D4B46-72A7-4206-91AA-BEABA2323E3C') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID) = 1
        begin
            set @REGISTRANTSTATUSTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C');

            if(len(isnull(@REGISTRANTSTATUSTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    declare @SPONSORTYPECODE tinyint
    set @SPONSORTYPECODE = dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@ID);
    set @SPONSORCONSTITUENCYTEXT = 
        case
            when @SPONSORTYPECODE = 0
                and (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '4D746A03-A0AB-45F3-A30B-1AD4F304E622') = 1
                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4D746A03-A0AB-45F3-A30B-1AD4F304E622') --Sponsor

            when @SPONSORTYPECODE = 1
                and (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'F89E03BC-E724-4e5d-943B-72D4D1E1E916') = 1
                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F89E03BC-E724-4e5d-943B-72D4D1E1E916') --Sponsorship recipient

            when @SPONSORTYPECODE = 2
                and (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '908E521C-B0A5-4832-B664-7D7B079D77C2') = 1
                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('908E521C-B0A5-4832-B664-7D7B079D77C2') --Sponsorship donor

            else null 
        end;    

        if(len(isnull(@SPONSORCONSTITUENCYTEXT,'')) > 0) 
        begin
            set @HASCONSTITUENCY = 1;
        end

    set @STAFFCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '6093915E-ADE9-42BE-88AE-304731754467') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISSTAFF(@ID) = 1
        begin
            set @STAFFCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467');

            if(len(isnull(@STAFFCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @STUDENTCONSTITUENCYTEXT = null;
    declare @ISSTUDENTACTIVE bit = (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC');
    if @ISSTUDENTACTIVE = 1
    begin
        if dbo.UFN_CONSTITUENT_ISSTUDENT(@ID) = 1 
        begin
            set @STUDENTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4DB8F4FC-BC43-421D-B592-69BEF109B5FC');

            if(len(isnull(@STUDENTCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    if @STUDENTCONSTITUENCYTEXT is not null
    begin
        set @STUDENTENROLLMENTID = (
            select top 1 EDUCATIONALHISTORY.ID
            from dbo.EDUCATIONALHISTORY 
                inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
                inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORYSTATUS.ID = EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID
            where EDUCATIONALINSTITUTION.ISAFFILIATED = 1
                and EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 0
                and EDUCATIONALHISTORY.CONSTITUENTID = @ID
            order by dbo.EDUCATIONALHISTORY.DATEADDED
        )
    end

    if @ISSTUDENTACTIVE = 1
        or (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592') = 1
    begin
    set @STUDENTRELATIONCONSTITUENCYTEXT = (
        select dbo.UDA_BUILDLIST(distinct CONSTITUENCYCODEDESCRIPTION) 
        from dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES() 
        where CONSTITUENTID = @ID
    )
    end

    set @STUDENTRELATIONCONSTITUENCIES = (
        select distinct
            CONSTITUENCYCODEDESCRIPTION as DESCRIPTION,
            CONSTITUENCYCODESEQUENCE as SEQUENCE
        from dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES()
        where CONSTITUENTID = @ID
        order by CONSTITUENCYCODESEQUENCE
        for xml raw('ITEM'), type, elements, root('STUDENTRELATIONCONSTITUENCIES'), binary base64
    )

    set @VENDORSTATUSTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '5435C96D-8617-46C3-9A62-5AFF08451A53') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISVENDOR(@ID) = 1
        begin
            set @VENDORSTATUSTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53');
        end
    end

    set @VOLUNTEERCONSTITUENCYTEXT = null;
    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'E7489703-3D63-4017-A2BC-88C092563C5D') = 1
    begin
        if dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID) = 1
        begin
            set @VOLUNTEERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D');

            if(len(isnull(@VOLUNTEERCONSTITUENCYTEXT,'')) > 0) 
            begin
                set @HASCONSTITUENCY = 1;
            end
        end
    end

    set @USERDEFINEDCONSTITUENCIES = dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_USERDEFINED_TOITEMLISTXML(@ID, @CURRENTDATE);

    set @RELATIONCONSTITUENCYTEXT = null;
    if(@HASCONSTITUENCY = 0 and @USERDEFINEDCONSTITUENCIES is null) 
    begin
        if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '8B6ECCFD-6829-4FBC-B092-58CA083F9322') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISRELATION(@ID) = 1
            begin
                set @RELATIONCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('8B6ECCFD-6829-4FBC-B092-58CA083F9322');
            end
        end
    end
    /************************************/
    /***  End of constituency fields  ***/
    /************************************/

    return 0;