USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPRIMARYMEMBERADDITIONALDETAILS

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPRIMARYMEMBERADDITIONALDETAILS
                (
                    @ID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @SCHOOLNAME nvarchar(100) = null output,
                    @CLASSYEAR nvarchar(4) = null output,
                    @PROSPECTMANAGERID uniqueidentifier = null output,
                    @PROSPECTMANAGER nvarchar(100) = null output,
                    @ADVOCATECONSTITUENCYTEXT nvarchar(100) = null output,
                    @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
                    @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
                    @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
                    @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
                    @VENDORSTATUSTEXT nvarchar(100) = null output,
                    @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100) = null output,
                    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @SPONSORTYPECODE tinyint = null output,
                    @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
                    @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
                    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @FACULTYCONSTITUENCYTEXT nvarchar(100) = null output,
                    @STUDENTCONSTITUENCYTEXT nvarchar(100) = null output,
                    @ALUMNUSENROLLMENTID uniqueidentifier = null output,
                    @STUDENTENROLLMENTID uniqueidentifier = null output,
                    @ALUMNUSCONSTITUENCYTEXT nvarchar(100) = null output,
                    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
                    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
                    @USERDEFINEDCONSTITUENCIES xml = null output,
                    @CONSTITUENCIESDISPLAYORDER xml = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    -- Education info
                    select
                        @SCHOOLNAME = EI.NAME,
                        @CLASSYEAR = EH.CLASSOF
                    from dbo.EDUCATIONALHISTORY EH
                    left join dbo.EDUCATIONALINSTITUTION EI on EH.EDUCATIONALINSTITUTIONID = EI.ID
                    where
                        EH.CONSTITUENTID = @ID and
                        EH.ISPRIMARYRECORD = 1

                    -- Prospect manager
                    select
                        @PROSPECTMANAGERID = P.PROSPECTMANAGERFUNDRAISERID,
                        @PROSPECTMANAGER = NF.NAME
                    from
                        dbo.PROSPECT P
                            inner join dbo.CONSTITUENT C on P.PROSPECTMANAGERFUNDRAISERID = C.ID
                        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) NF
                    where
                        P.ID = @ID

                    --
                    -- Constituencies
                    --

                    declare @MATCHFINDERONLINERECORDID int
                    set @MATCHFINDERONLINERECORDID = 0;
                    select top 1
                        @MATCHFINDERONLINERECORDID = MATCHFINDERRECORDID
                    from
                        dbo.MATCHFINDERCONSTITUENT
                    where
                        ID = @ID;


                    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')
                        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')
                        end
                    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')
                        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')
                        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')
                        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')
                        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')
                        end
                    end 


                    set @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID);

                    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')
                        end
                    end 

                    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 @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')
                        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')
                        end
                    end 

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

                    set @SPONSORTYPECODE = dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@ID)
                    set @SPONSORCONSTITUENCYTEXT =
                    case @SPONSORTYPECODE
                        when 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4D746A03-A0AB-45F3-A30B-1AD4F304E622')
                        when 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F89E03BC-E724-4e5d-943B-72D4D1E1E916')
                        when 2 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('908E521C-B0A5-4832-B664-7D7B079D77C2')
                        else null
                    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')
                        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')
                        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');
                        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');
                        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');
                        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 @STUDENTCONSTITUENCYTEXT = null;
                    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC') = 1
                    begin
                        if dbo.UFN_CONSTITUENT_ISSTUDENT(@ID) = 1 
                        begin
                            set @STUDENTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4DB8F4FC-BC43-421D-B592-69BEF109B5FC');
                        end
                    end

                    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');
                        end
                    end

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

                    if @STUDENTCONSTITUENCYTEXT is not null
                    begin
                        set @STUDENTENROLLMENTID =
                        (
                            select top 1
                                EDUCATIONALHISTORY.ID
                            from dbo.EDUCATIONALHISTORY
                                inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                                inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
                            where
                                (EDUCATIONALINSTITUTION.ISAFFILIATED = 1) and
                                (EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 0) 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');
                        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');
                        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');
                        end
                    end


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

                    set @USERDEFINEDCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCIES_USERDEFINED(@ID);

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

                    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
                        )

                    set @DATALOADED = 1;

                    return 0;