Back to Home

AD HOC Report

User_RoleType_Menu.sql

"User Job Type" part, Report of all the users in SAM3 with their assigned job type. "User Menu" part, Report of all the users in SAM3 with their assigned menu.

/***
User Job Type

CIWSQLXP0053
***/

SELECT a.FirstName, a.Lastname, a.EmployeeID, a.NTID, ISNULL(c.LookUpTitle,'N/A') as JobType, ISNULL(d.LookUpTitle,'N/A') as ApplicationName FROM smart.sam_master_userlist a
    left join smart.sam_user_jobtype b on a.userNo = b.userNo
    left join smart.sam_system_codelist c on b.jobtypeNo = c.lookupcode and c.lookupcategory = 'jobtype'
    left join smart.sam_system_codelist d on c.Option3 = d.lookupcode and d.lookupcategory = 'ApplicationName'
where a.LoginAllowed = 1 and a.status = 1

/***
User Menu
***/

SELECT a.FirstName, a.Lastname, a.EmployeeID, a.NTID, MenuTitle, e.LookUpTitle as ApplicationName FROM smart.sam_master_userlist a
    LEFT JOIN smart.sam_User_Menu b ON a.UserNo = b.UserNo
    INNER JOIN smart.sam_master_menu c on b.MenuNo = c.MenuNo and c.Status = 1
    left join smart.sam_system_codelist d ON c.MenugroupNo = d.lookupcode and d.lookupcategory = 'menugroup'
    left join smart.sam_system_codelist e on d.Option3 = e.lookupcode and e.lookupcategory = 'ApplicationName'
where a.LoginAllowed = 1 and a.status = 1

select * from smart.sam_system_codelist where lookupcategory = 'menugroup'