USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_FORMS

Gets state of feature permissions for data form instances for the given System Role ID.
Parameters

Parameter	Parameter Type	Mode
@SYSTEMROLEID	uniqueidentifier	IN
@ONLYASSIGNED	bit	IN
@ONLYDISPLAYED	bit	IN
Definition

Copy



                CREATE procedure [dbo].[USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_FORMS]
                @SYSTEMROLEID uniqueidentifier,
                @ONLYASSIGNED bit=0,
                @ONLYDISPLAYED bit=0  

                /*
                Used by the SecurityGetRoleFeaturePermTreeRequest web service

                */
                as

                set nocount on;

                if @SYSTEMROLEID is null 
                    begin
                    raiserror('@SYSTEMROLEID is required',16,10);
                    return 8;
                    end;

                select 
                    INST.ID,
                    INST.FORMNAME as DISPLAYNAME, 
                    CAT.SECURITYUIFOLDER,
                    CAT.MODE,
                    PERM.GRANTORDENY,
                    CAT.DESCRIPTION,
                    PERM.ID as PERMISSIONID,
                    case when CAT.MODE = 0 then
                        cast(coalesce(CAT.TEMPLATESPECXML.value('declare namespace bbfa="bb_appfx_viewdataformtemplate";/bbfa:ViewDataFormTemplateSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit)
                    else 
                        case when CAT.MODE = 1 then
                            cast(coalesce(CAT.TEMPLATESPECXML.value('declare namespace bbfa="bb_appfx_editdataformtemplate";/bbfa:EditDataFormTemplateSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit)
                            else 
                            cast(coalesce(CAT.TEMPLATESPECXML.value('declare namespace bbfa="bb_appfx_adddataformtemplate";/bbfa:AddDataFormTemplateSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit)
                        end
                    end  DISPLAYFEATURE,
                    CAT.TEMPLATESPECXML as SPECXML
                from 
                    dbo.DATAFORMTEMPLATECATALOG as CAT
                inner join 
                    dbo.DATAFORMINSTANCECATALOG as INST on CAT.ID=INST.DATAFORMTEMPLATECATALOGID
                left outer join 
                    (
                    select 
                        ID,
                        GRANTORDENY,
                        DATAFORMINSTANCECATALOGID 
                    from  
                        dbo.SYSTEMROLEPERM_DATAFORMINSTANCE as PERM
                    where 
                        SYSTEMROLEID=@SYSTEMROLEID
                    )
                    as PERM on INST.ID=PERM.DATAFORMINSTANCECATALOGID
                where  
                    (PERM.ID is not null or @ONLYASSIGNED=0)
                    and
                    (@ONLYDISPLAYED = 0 or 
                      case when CAT.MODE = 0 then  
                        cast(coalesce(CAT.TEMPLATESPECXML.value('declare namespace bbfa="bb_appfx_viewdataformtemplate";/bbfa:ViewDataFormTemplateSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit)  
                       else   
                        case when CAT.MODE = 1 then  
                         cast(coalesce(CAT.TEMPLATESPECXML.value('declare namespace bbfa="bb_appfx_editdataformtemplate";/bbfa:EditDataFormTemplateSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit)  
                         else   
                         cast(coalesce(CAT.TEMPLATESPECXML.value('declare namespace bbfa="bb_appfx_adddataformtemplate";/bbfa:AddDataFormTemplateSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit)  
                        end  
                       end = 1  
                    )     
                order by 
                    CAT.SECURITYUIFOLDER, INST.FORMNAME;