UFN_SECURITY_APPUSER_GRANTED_BATCHDEFAULTS

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHDEFAULTS
(@APPUSERID uniqueidentifier,@BATCHTEMPLATEID uniqueidentifier)
returns bit
as
/*
Returns true if user has been granted and not denied rights for data entry to the batch for a system role.
*/
begin
    --If at least one grant and no deny then return true

    --otherwise, false

    declare @grant bit;
    set @grant=0;

    select @grant = ISSYSADMIN from dbo.APPUSER where ID = @APPUSERID;

    if @grant = 0
        begin
            select top 1 @grant = 1 from dbo.BATCH where BATCH.APPUSERID = @APPUSERID and BATCH.BATCHTEMPLATEID = @BATCHTEMPLATEID;

            if @@ROWCOUNT = 0 
                begin--order by GRANTORDENY, deny will be first.                

                    select 
                        top 1 @grant=SECURITYVIEW.GRANTORDENY
                    from 
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE SECURITYVIEW
                        with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE_BATCHWORKFLOWSTATEID_APPUSERID))                    
                    inner join 
                        dbo.BATCHWORKFLOWSTATE on SECURITYVIEW.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID
                    inner join 
                        dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHWORKFLOWID = BATCHWORKFLOWSTATE.BATCHWORKFLOWID
                    where 
                        SECURITYVIEW.APPUSERID = @APPUSERID
                    and 
                        BATCHTEMPLATE.ID=@BATCHTEMPLATEID and BATCHWORKFLOWSTATE.ISINITIALSTATE = 1
                    and
                        ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
                    order by 
                        SECURITYVIEW.GRANTORDENY ASC;                    

                    if @@ROWCOUNT = 0 
                        begin
                            select 
                                top 1 @grant=SECURITYVIEW.GRANTORDENY
                            from 
                                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE SECURITYVIEW
                                with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))                                
                            inner join
                                dbo.BATCHTEMPLATE on SECURITYVIEW.BATCHTEMPLATEID = BATCHTEMPLATE.ID
                            where 
                                SECURITYVIEW.APPUSERID = @APPUSERID
                            and 
                                SECURITYVIEW.BATCHTEMPLATEID=@BATCHTEMPLATEID
                            and
                                ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
                            order by 
                                SECURITYVIEW.GRANTORDENY ASC;
                        end

                    if @@ROWCOUNT = 0 
                        begin
                            select 
                                top 1 @grant=SECURITYVIEW.GRANTORDENY
                            from 
                                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE SECURITYVIEW
                            inner join 
                                dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = SECURITYVIEW.BATCHTYPECATALOGID                                
                            where 
                                SECURITYVIEW.APPUSERID = @APPUSERID
                            and 
                                BATCHTEMPLATE.ID=@BATCHTEMPLATEID
                            and
                                ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
                            order by 
                                SECURITYVIEW.GRANTORDENY ASC;
                        end
                end
        end

    return @grant;

end