/**
*  
*  Return list of security modules and minimum access level for the provided user and branches.
*
*  @inSysUserId - System user Id
*    @inBranchPriKeys - list of Branch Prikeys to check if the user has access to the branches
*/

CREATE function [dbo].[udfGetPermissionsForUserAndBranches]
(
    @inBranchPriKeys IntList readonly,
    @inSysUserId int
)
returns table as return
(
    select
    SecurityModuleName = SecModules.Description,
    MinAccessLevel = min(
        case when SecProfileDetail.SecProfilePriKey is null then 0
        when SecProfileDetail.ManagerFlag = 1 then 3
        when SecAccessTypes.Description = 'Read Only' then 1
        else 2
        end)
    from UserAccess
    cross join SecModules
    cross join @inBranchPriKeys branches
    left outer join SecProfileDetail on UserAccess.SecProfilePriKey = SecProfileDetail.SecProfilePriKey
    and SecProfileDetail.BranchPriKey = branches.Item and SecProfileDetail.ModulePriKey = SecModules.ModulePriKey
    left outer join SecAccessTypes on SecProfileDetail.AccessPriKey = SecAccessTypes.AccessPriKey
    where UserAccess.SysUserID = @inSysUserId
    group by SecModules.Description
)
GO
GRANT SELECT ON  [dbo].[udfGetPermissionsForUserAndBranches] TO [MssExec]
GO