UFN_SECURITY_APPUSER_GRANTED_BATCHTEMPLATE_FORSITE

Returns a table of Site IDs for which the user has been granted the BATCHTEMPLATE according to the role sites.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@APPUSERID	uniqueidentifier	IN
@BATCHTEMPLATEID	uniqueidentifier	IN
@SITEID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHTEMPLATE_FORSITE
            (
                @APPUSERID uniqueidentifier,
                @BATCHTEMPLATEID uniqueidentifier,
                @SITEID uniqueidentifier
            )
            returns bit 
            as
            /*Returns a 1 if the the user has rights to according to site security.*/
            begin
                if exists
                (
                    select 
                        1
                    from 
                        dbo.SYSTEMROLEPERM_BATCHTEMPLATE as ST
                        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = ST.SYSTEMROLEID
                        inner join dbo.BATCHTEMPLATE on ST.BATCHTEMPLATEID = BATCHTEMPLATE.ID
                        left join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SYSTEMROLEAPPUSER.APPUSERID and SITEPERMISSION.SYSTEMROLEID = ST.SYSTEMROLEID
                    where 
                        SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and
                        ST.BATCHTEMPLATEID = @BATCHTEMPLATEID and
                        ST.GRANTORDENY = 1  
                        and    (
                            SYSTEMROLEAPPUSER.SECURITYMODECODE = 0
                            or (SYSTEMROLEAPPUSER.SECURITYMODECODE = 1 and @SITEID is null)
                            or ((SYSTEMROLEAPPUSER.SECURITYMODECODE = 2 or SYSTEMROLEAPPUSER.SECURITYMODECODE = 3) and SITEPERMISSION.SITEID = @SITEID)
                        )
                        and ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
                    union
                    select 
                        1
                    from 
                        dbo.SYSTEMROLEPERM_BATCHTYPE as ST
                        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = ST.SYSTEMROLEID
                        inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = ST.BATCHTYPECATALOGID
                        left join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SYSTEMROLEAPPUSER.APPUSERID and SITEPERMISSION.SYSTEMROLEID = ST.SYSTEMROLEID                    
                    where 
                        SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and 
                        BATCHTEMPLATE.ID = @BATCHTEMPLATEID and
                        ST.GRANTORDENY = 1 
                        and    (
                            SYSTEMROLEAPPUSER.SECURITYMODECODE = 0
                            or (SYSTEMROLEAPPUSER.SECURITYMODECODE = 1 and @SITEID is null)
                            or ((SYSTEMROLEAPPUSER.SECURITYMODECODE = 2 or SYSTEMROLEAPPUSER.SECURITYMODECODE = 3) and SITEPERMISSION.SITEID = @SITEID)
                        )
                        and ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
                    union
                    select
                        1
                    from 
                        dbo.SYSTEMROLEPERM_BATCHWORKFLOWSTATE as ST
                        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = ST.SYSTEMROLEID
                        inner join dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.ID = ST.BATCHWORKFLOWSTATEID
                        inner join dbo.BATCHWORKFLOW on BATCHWORKFLOW.ID = BATCHWORKFLOWSTATE.BATCHWORKFLOWID
                        inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHWORKFLOW.BATCHTYPECATALOGID
                        left join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SYSTEMROLEAPPUSER.APPUSERID and SITEPERMISSION.SYSTEMROLEID = ST.SYSTEMROLEID
                    where 
                        SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and 
                        BATCHTEMPLATE.ID = @BATCHTEMPLATEID and
                        ST.GRANTORDENY = 1 
                        and    (
                            SYSTEMROLEAPPUSER.SECURITYMODECODE = 0
                            or (SYSTEMROLEAPPUSER.SECURITYMODECODE = 1 and @SITEID is null)
                            or ((SYSTEMROLEAPPUSER.SECURITYMODECODE = 2 or SYSTEMROLEAPPUSER.SECURITYMODECODE = 3) and SITEPERMISSION.SITEID = @SITEID)
                        )
                        and ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
                )
                    return 1;

                return 0;
            end