Back to Home

Table Definitions Regarding Adding and Updating New Menus

When new Menus are created and need to be added. Table No 2, 3, and 5 must be updated via script. For example,

   declare @MenuNoBenefitResult int, @MenuNoTrending int, @UserNo int

   --Insert new reports
   insert into smart.sam_Master_Menu (MenuTitle, MenuGroupNo, WebPage, SortOrder, IconName, [Status], AutoDistribute, AdminNo, DateEntered)
   values
   ('Benefit Result BO', 13, 'p11_Report_BenefitResultBO.aspx', 0, 'Report.gif', 1, 1, 0, GETDATE()),
   ('Benefit Trending BO', 13, 'p11_Report_Trending_BO.aspx', 0, 'Report.gif', 1, 1, 0, GETDATE())

   Select @MenuNoBenefitResult = MenuNo from smart.sam_Master_Menu where MenuTitle = 'p11_Report_BenefitResultBO.aspx'
   Select @MenuNoTrending = MenuNo from smart.sam_Master_Menu where MenuTitle = 'p11_Report_Trending_BO.aspx'

   --add new menus to RoleTypeNo = 9 / BO
   insert into smart.p11_Menu_RoleType (MenuNo, RoleTypeNo, AdminNo, DateEntered, Deleted)
   values
   (@MenuNoBenefitResult, 9, 1, GETDATE(), 0),
   (@MenuNoTrending, 9, 1, GETDATE(), 0)

   --add new menu (p11_Report_BenefitResultBO) to existing BO's
   INSERT INTO  smart.sam_User_Menu (UserNo, MenuNo, SortOrder, PersonalMenu, AdminNo, DateEntered,Deleted)
   SELECT DISTINCT UserNo, (SELECT MenuNo FROM smart.sam_Master_Menu WHERE MenuTitle = 'p11_Report_BenefitResultBO.aspx'), 0, 0, 760346, GETDATE(), 0
   FROM Smart.p11_user_jobtype WHERE JobTypeNo = 9

   --add new menu (p11_Report_Trending_BO) to existing BO's
   INSERT INTO  smart.sam_User_Menu (UserNo, MenuNo, SortOrder, PersonalMenu, AdminNo, DateEntered,Deleted)
   SELECT DISTINCT UserNo, (SELECT MenuNo FROM smart.sam_Master_Menu WHERE MenuTitle = 'p11_Report_Trending_BO.aspx'), 0, 0, 760346, GETDATE(), 0
   FROM Smart.p11_user_jobtype WHERE JobTypeNo = 9

1. Smart.p11_UserList

smart.p11_UserList is a view and calls smart.sam_SetupUser which contains all user data.

2. Smart.sam_master_menu

Contains All menus

3. Smart.sam_user_menu

Contains UserNo's and assigned menus

4. Smart.p11_user_jobtype

Contains UserNo's and assigned JobType/RoleType.

5. Smart.p11_Menu_RoleType

Contains menus that are assigned for each Role Type.

6. Import Note