USP_DATAFORMTEMPLATE_VIEW_RESEARCHGROUP_PAGEEXPRESSIONS

The load procedure used by the view dataform template "Research Group Page Expression View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	NAME
@USERCANMODIFY	bit	INOUT	USERCANMODIFY
@USERISOWNERORADMIN	bit	INOUT	USERISOWNERORADMIN
@WEALTHUPDATEPENDING	bit	INOUT	WEALTHUPDATEPENDING
@ENABLEUPDATES	bit	INOUT	ENABLEUPDATES
@ENABLESYNCRE7	bit	INOUT	ENABLESYNCRE7
@HASWEALTHPOINTCREDENTIALS	bit	INOUT	HASWEALTHPOINTCREDENTIALS
@RE7SECURITYFEATURESGRANTED	bit	INOUT	RE7SECURITYFEATURESGRANTED
@MAPPINGCREDENTIALSEXIST	bit	INOUT	MAPPINGCREDENTIALSEXIST
@MAPCONTEXTID	nvarchar(72)	INOUT	MAPCONTEXTID
@ENABLEALLUPDATES	bit	INOUT	ENABLEALLUPDATES
@DISABLEALLUPDATES	bit	INOUT	DISABLEALLUPDATES
@WEALTHPOINTREGISTERED	bit	INOUT	WEALTHPOINTREGISTERED
@WEALTHPOINTACCOUNTTYPECODE	int	INOUT	WEALTHPOINTACCOUNTTYPECODE
@CANASSIGNPROSPECTMANAGER	bit	INOUT	CANASSIGNPROSPECTMANAGER
@DATAREFRESHAVAILABLE	bit	INOUT	DATAREFRESHAVAILABLE
@USER_GRANTED_MAPENTITY	bit	INOUT
@RE7CONFIGUREDFORUSER	bit	INOUT
@ALLOWREFRESH	bit	INOUT
@RE7CANVIEW	bit	INOUT
Definition

Copy

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESEARCHGROUP_PAGEEXPRESSIONS (
                    @ID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(100) = null output,
                    @USERCANMODIFY bit = null output,
                    @USERISOWNERORADMIN bit = null output,
                    @WEALTHUPDATEPENDING bit = null output,
                    @ENABLEUPDATES bit = null output,
                    @ENABLESYNCRE7 bit = null output,
                    @HASWEALTHPOINTCREDENTIALS bit = null output,
                    @RE7SECURITYFEATURESGRANTED bit = null output,
                    @MAPPINGCREDENTIALSEXIST bit = null output,
                    @MAPCONTEXTID nvarchar(72) = null output,
                    @ENABLEALLUPDATES bit = null output,
                    @DISABLEALLUPDATES bit = null output,
                    @WEALTHPOINTREGISTERED bit = null output,
                    @WEALTHPOINTACCOUNTTYPECODE integer = null output,
                    @CANASSIGNPROSPECTMANAGER bit = null output,
                    @DATAREFRESHAVAILABLE bit = null output,
                    @USER_GRANTED_MAPENTITY bit = null output,
                    @RE7CONFIGUREDFORUSER bit = null output,
                    @ALLOWREFRESH bit = null output,
                    @RE7CANVIEW bit = null output
                ) as
                    set nocount on;

                    declare @MAPENTITYID uniqueidentifier = N'065A6FAB-669C-4032-B616-D57254F340AE';

                    if (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('6f77d512-d0d1-444f-9b46-b8603a6fe5f1') = 1)
                        set @MAPENTITYID = N'20F0E455-384C-4E63-B3C8-DAA6A238AD7F';

                    select
                        @DATALOADED = 1,
                        @NAME = RG.NAME,
                        @USERCANMODIFY = dbo.UFN_RESEARCHGROUP_USERCANMODIFY(@ID, @CURRENTAPPUSERID),
                        @USERISOWNERORADMIN = dbo.UFN_RESEARCHGROUP_USERHASADMINRIGHTS(@ID, @CURRENTAPPUSERID),
            @ALLOWREFRESH = ALLOWREFRESH
                    from
                        dbo.RESEARCHGROUP RG
                    left join
                        dbo.APPUSER AU on AU.ID = RG.OWNERID
                    where
                        RG.ID = @ID
                        and dbo.UFN_RESEARCHGROUP_USERCANACCESS(RG.ID, @CURRENTAPPUSERID) = 1
                        and dbo.UFN_RESEARCHGROUP_USERHASSITEACCESS(RG.ID, @CURRENTAPPUSERID, '56204FFB-03E9-4B18-BFB3-59BB5D01080D', 1) = 1;

                    if (exists(
                            SELECT top(1) C.ID FROM dbo.RESEARCHGROUP RG
                            left join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                            join CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                            left join DISABLEDWEALTHUPDATES DWU on C.ID = DWU.ID
                            where RG.ID = @ID and
                            DWU.ID is null and
                            C.ISORGANIZATION = 0
                        ))
                        set @ENABLEUPDATES = 1;
                    else
                        set @ENABLEUPDATES = 0;

                    if (exists(
                            SELECT 
                                top(1) rgm.CONSTITUENTID
                            from 
                                RESEARCHGROUPMEMBER rgm
                            left join DISABLEDWEALTHUPDATES dwu
                                on rgm.CONSTITUENTID = dwu.ID
                            where rgm.RESEARCHGROUPID = @ID and dwu.ID is not null
                        ))
                        set @ENABLEALLUPDATES = 1;
                    else
                        set @ENABLEALLUPDATES = 0;

                    if (exists(
                            SELECT 
                                top(1) rgm.CONSTITUENTID
                            from 
                                RESEARCHGROUPMEMBER rgm
                            left join DISABLEDWEALTHUPDATES dwu
                                on rgm.CONSTITUENTID = dwu.ID
                            where rgm.RESEARCHGROUPID = @ID and dwu.ID is null
                        ))
                        set @DISABLEALLUPDATES = 1;
                    else
                        set @DISABLEALLUPDATES = 0;

                    if exists(select 1 from dbo.WPSEARCHHISTORY where RESEARCHGROUPID=@ID and STATUSCODE in(0,1)) begin
                        set @WEALTHUPDATEPENDING = 1;
                    end else begin
                        set @WEALTHUPDATEPENDING = 0;
                    end                    

                    if (exists(
                            SELECT 1 FROM dbo.RESEARCHGROUP RG
                            left join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                            inner join dbo.RE7INTEGRATIONCONSTITUENTMAP    RE7MAP on RE7MAP.ID = RGM.CONSTITUENTID
                            where RG.ID = @ID))
                        set @ENABLESYNCRE7 = 1;
                    else
                        set @ENABLESYNCRE7 = 0;

                    select top 1
                        @WEALTHPOINTREGISTERED = REGISTERED,
                        @WEALTHPOINTACCOUNTTYPECODE = ACCOUNTTYPECODE,
                        @DATAREFRESHAVAILABLE = case when ACCOUNTTYPECODE in (1,2,5,6,7) then 1 else 0 end
                    from
                        dbo.WEALTHPOINTCONFIGURATION;

                    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                    begin
                        set @RE7SECURITYFEATURESGRANTED = 1;
                        set @USER_GRANTED_MAPENTITY = 1;
                        set @RE7CANVIEW = 1;
                    end
                    else
                    begin
                        if (dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID ,'c81183fd-78df-4ebf-8855-b3dcc0b0be33')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID ,'505760bd-3e81-4c4f-8f21-f59edfd5562d')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID ,'49675912-2d9a-415b-8f03-ccf9c564796d')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID ,'6F678058-8A2C-4AEC-9935-8BF1F3FAA35D')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'ab0e06c4-dcea-414a-bf29-0b0758a3934b')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8DABE43F-6848-40EB-8638-8E4B6311CE72')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'1A5D63F0-4DDD-4DD3-A4E6-FB1E9FE17FE2')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'41B32DCD-4CFE-4FAC-83DA-FA9A3F893B71')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'437cd7d8-e49c-4591-9574-572f5f15fd3a')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'E1560C48-F5E0-4E4E-B9EF-9EE4CCE644F4')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'08F40ECE-26B9-4e11-B53D-1FA5BAFC038A')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'33EE2022-238B-4400-81F5-F81F19C9F755')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'38B7BE1C-6F54-4951-94C5-EE53C688CBBA')=1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'2dbf2cc3-958c-4454-b169-7eb1e656044e')=1 )
                        set @RE7SECURITYFEATURESGRANTED = 1;

                        if dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'E1560C48-F5E0-4E4E-B9EF-9EE4CCE644F4')=1
                            set @RE7CANVIEW = 1;

                        set @USER_GRANTED_MAPENTITY = dbo.UFN_SECURITY_APPUSER_GRANTED_MAPENTITY_IN_SYSTEMROLE(@CURRENTAPPUSERID, @MAPENTITYID);
                    end

                    select top 1 @RE7CONFIGUREDFORUSER = 1
                    from dbo.RE7INTEGRATIONCREDENTIALS
                    where ID = @CURRENTAPPUSERID;

                    declare @MAPPINGCREDENTIALSID uniqueidentifier;
                    select top 1
                        @MAPPINGCREDENTIALSID = ID
                    from
                        dbo.MAPPINGCREDENTIALS
                    where
                        len([KEY]) > 0;

                    if @MAPPINGCREDENTIALSID is not null
                        set @MAPPINGCREDENTIALSEXIST = 1;
                    else
                        set @MAPPINGCREDENTIALSEXIST = 0;

                    set @MAPCONTEXTID = dbo.UFN_MAPPING_BUILDCONTEXTRECORDID(@MAPENTITYID,@ID,null)

                    declare @MEMBERCOUNT int
                    select @MEMBERCOUNT = count(ID) from dbo.[RESEARCHGROUPMEMBER] where [RESEARCHGROUPID] = @ID

                    if @MEMBERCOUNT <= 500 and @MEMBERCOUNT > 0
                        set @CANASSIGNPROSPECTMANAGER = 1;
                    else
                        set @CANASSIGNPROSPECTMANAGER = 0;

                    if @DATALOADED = 1 
                        exec dbo.USP_RESEARCHGROUP_UPDATELASTACCESSED @ID, @CURRENTAPPUSERID;