USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPCONSTITUENT

                CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPCONSTITUENT]
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @NAME nvarchar(700) = null output,
                    @LOOKUPID nvarchar(36) = null output,
                    @ADDRESSID uniqueidentifier = null output,
                    @ADDRESSTYPE nvarchar(100) = null output,
                    @ADDRESS nvarchar(300) = null output,
                    @EMAILADDRESSID uniqueidentifier = null output,
                    @EMAILADDRESS dbo.[UDT_EMAILADDRESS] = null output,
                    @PHONENUMBERID uniqueidentifier = null output,
                    @PHONENUMBER nvarchar(100) = null output,
                    @SCHOOLNAME nvarchar(100) = null output,
                    @CLASSOF dbo.[UDT_YEAR] = null output,
                    @ISORGANIZATION bit = null output,
                    @ISGROUP bit = null output,
                    @ISHOUSEHOLD bit = null output
                )
                as
                begin
                    set @DATALOADED = 0;

                    if exists (select top 1 ID from dbo.CONSTITUENT where ID = @ID)
                    begin
                        -- check constituent security and site security in the context of the membership dues add form
                        if (dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1) or
                            ((dbo.[UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT](@CURRENTAPPUSERID, '2852e35e-9b16-4f98-906c-51b7740e1ab4', @ID) = 1) and -- constituent group security
                            exists (select top 1 1 from dbo.[UFN_SITEID_MAPFROM_CONSTITUENTID](@ID) as [SITE]                                           -- site security
                                    where dbo.[UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE](@CURRENTAPPUSERID, '2852e35e-9b16-4f98-906c-51b7740e1ab4', [SITE].[SITEID]) = 1))
                            select
                                @DATALOADED = 1,
                                @NAME = [NAMEFORMAT].[NAME],
                                @LOOKUPID = [CONSTITUENT].[LOOKUPID],
                                @ADDRESSID = [ADDRESS].[ID],
                                @ADDRESSTYPE = [ADDRESSTYPECODE].[DESCRIPTION],
                                @ADDRESS = dbo.[UFN_BUILDFULLADDRESS]([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [ADDRESS].[STATEID], [ADDRESS].[POSTCODE], [ADDRESS].[COUNTRYID]),
                                @EMAILADDRESSID = [EMAILADDRESS].[ID],
                                @EMAILADDRESS = [EMAILADDRESS].[EMAILADDRESS],
                                @PHONENUMBERID = [PHONE].[ID],
                                @PHONENUMBER = dbo.[UFN_PHONE_GETINTERNATIONALNUMBER]([PHONE].[COUNTRYID], [PHONE].[NUMBER]),
                                @SCHOOLNAME = [EDUCATIONALINSTITUTION].[NAME],
                                @CLASSOF = [EDUCATIONALHISTORY].[CLASSOF],
                                @ISORGANIZATION = [CONSTITUENT].[ISORGANIZATION],
                                @ISGROUP = [CONSTITUENT].[ISGROUP],
                                @ISHOUSEHOLD = dbo.[UFN_CONSTITUENT_ISHOUSEHOLD]([CONSTITUENT].[ID])
                            from dbo.CONSTITUENT
                                left join dbo.[ADDRESS] on [ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [ADDRESS].[ISPRIMARY] = 1
                                left join dbo.[ADDRESSTYPECODE] on [ADDRESS].[ADDRESSTYPECODEID] = [ADDRESSTYPECODE].[ID]
                                left join dbo.[EMAILADDRESS] on [EMAILADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [EMAILADDRESS].[ISPRIMARY] = 1
                                left join dbo.[PHONE] on [PHONE].[CONSTITUENTID] = [CONSTITUENT].ID and [PHONE].[ISPRIMARY] = 1
                                left join dbo.[EDUCATIONALHISTORY] on [CONSTITUENT].[ID] = [EDUCATIONALHISTORY].[CONSTITUENTID] and [EDUCATIONALHISTORY].[ISPRIMARYRECORD] = 1
                                left join dbo.[EDUCATIONALINSTITUTION] on [EDUCATIONALHISTORY].[EDUCATIONALINSTITUTIONID] = [EDUCATIONALINSTITUTION].[ID]
                                outer apply dbo.[UFN_CONSTITUENT_DISPLAYNAME]([CONSTITUENT].[ID]) as [NAMEFORMAT]
                            where [CONSTITUENT].[ID] = @ID;
                    end
                    else
                    begin
                        select
                            @DATALOADED = 1,
                            @NAME = BATCHREVENUECONSTITUENT.NAME,
                            @ADDRESSTYPE = ADDRESSTYPECODE.DESCRIPTION,
                            @ADDRESS = dbo.UFN_BUILDFULLADDRESS(null, BATCHREVENUECONSTITUENT.ADDRESSBLOCK, BATCHREVENUECONSTITUENT.CITY, BATCHREVENUECONSTITUENT.STATEID, BATCHREVENUECONSTITUENT.POSTCODE, BATCHREVENUECONSTITUENT.COUNTRYID),
                            @EMAILADDRESS = BATCHREVENUECONSTITUENT.EMAILADDRESS,
                            @PHONENUMBER = BATCHREVENUECONSTITUENT.NUMBER,
                            @ISORGANIZATION=ISORGANIZATION,
                            @ISGROUP=BATCHREVENUECONSTITUENT.ISGROUP,
                            @ISHOUSEHOLD = case when BATCHREVENUECONSTITUENT.GROUPTYPECODE = 0 and BATCHREVENUECONSTITUENT.ISGROUP = 1 then 1 else 0 end
                        from dbo.BATCHREVENUECONSTITUENT
                            left join dbo.ADDRESSTYPECODE on BATCHREVENUECONSTITUENT.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
                        where BATCHREVENUECONSTITUENT.ID = @ID
                    end
                end