create function [dbo].[udfGetAllFreeTaskUsers]()
returns table as return
(
-- These are the users who have full security access to the Tasks module.
    with ActiveTaskModule as(
        select ModulePriKey from SecModules where [Description] = 'Tasks' and Active = 1
    ), FullSecurityAccess as (
        select AccessPriKey from SecAccessTypes where [Description] = 'Full'
    ), ActiveUserStatus as (
        select PriKey from Status where Status = 'Active'
    )
    select distinct
        Sysuser.SysuserID,
        Sysuser.EmpNo,
        dbo.GetSysuserName( Sysuser.SysuserID, 0 ) as FullName,
        case when LaborType.LaborType = 'Coor' then 'Coordinator'
            when LaborType.LaborType = 'Disp' then 'Dispatch'
            when LaborType.LaborType = 'LD Disp' then 'LD Dispatch'
            else LaborType.LaborType
        end as LaborType,
        LaborTypePriKey = LaborType.PriKey,
        case when LaborType.LaborType = 'Coor' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (Coordinator)'
            when LaborType.LaborType = 'Disp' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (Dispatch)'
            when LaborType.LaborType = 'LD Disp' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (LD Dispatch)'
            else dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (' + LaborType.LaborType + ')'
        end as FullNameAndLaborType
    from UserAccess UserAccess
    inner join SysUser SysUser on ( SysUser.SysUserID = UserAccess.SysUserID)
    inner join ActiveUserStatus on SysUser.Status  = ActiveUserStatus.PriKey
    inner join dbo.SysUserLaborTypeMap on SysUserLaborTypeMap.SysUserFID = SysUser.SysUserID
    inner join LaborType LaborType on LaborType.PriKey = SysUserLaborTypeMap.LaborTypeFID
    where UserAccess.SecProfilePriKey in(
                    select distinct SecProfilePriKey
                    from SecProfileDetail
                    inner join ActiveTaskModule on ActiveTaskModule.ModulePriKey = SecProfileDetail.ModulePriKey
                    inner join FullSecurityAccess on FullSecurityAccess.AccessPriKey = SecProfileDetail.AccessPriKey
                    )
    -- We want unions here (as opposed to union alls) so that we get a distinct dataset across off three select statements.
    union

    -- These are all of the active Coordinators, Salespersons, OA Surveyors, Revenue Clerks and Claims Adjusters.
    -- In other words, the labor types in the CoordinatorType table.
    select
        Sysuser.SysuserID,
        Sysuser.EmpNo,
        dbo.GetSysuserName( Sysuser.SysuserID, 0 ) as FullName,
        case when LaborType.LaborType = 'Coor' then 'Coordinator'
            else LaborType.LaborType
        end as LaborType,
        LaborTypePriKey = LaborType.PriKey,
        case when LaborType.LaborType = 'Coor' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (Coordinator)'
            else dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (' + LaborType.LaborType + ')'
        end as FullNameAndLaborType
    from Sysuser
    inner join SysUserLaborTypeMap on SysUserLaborTypeMap.SysUserFID = SysUser.SysUserID
    inner join LaborType on LaborType.PriKey = SysUserLaborTypeMap.LaborTypeFID and
    LaborType.LaborType in ('Coor', 'Sales', 'OA Surveyor', 'Revenue Clerk', 'Claims Adjuster')
    inner join ActiveUserStatus on SysUser.Status  = ActiveUserStatus.PriKey
)
GO
GRANT SELECT ON  [dbo].[udfGetAllFreeTaskUsers] TO [MssExec]
GO