Licensed Menu Items

Does anyone know how to filter the Ice.Menu table to only show menu items that are licensed?

I looked at the structure of the Ice.Menu table in SQL Server Management Studio and I don’t really see any static fields directly linked to licensing, so I imagine you’d need to find a link between this table and whatever table holds licensing information and join them in a query.

You probably have already figured out that’s what you need to do, and might even be what you’re asking how to do here, but just in case it’s not, I thought I’d show what I found. Here is a list of the columns available in Ice.Menu for what it’s worth (as of 10.1.600.5):

[Company]
[MenuID]
[MenuDesc]
[ParentMenuID]
[Sequence]
[OptionType]
[OptionSubType]
[Program]
[Enabled]
[SecCode]
[DoNotDisplayInMenu]
[Arguments]
[Module]
[MenuType]
[CGCCode]
[DashboardID]
[ExpressAvailable]
[SystemCode]
[OldProgram]
[Comment]
[Status]
[CRMMenu]
[SystemFlag]
[SysRevID]
[SysRowID]
[SaaSParam]
[CompanyVisibility]

Thanks so much, the problem is that the Menu table shows all modules even if they aren’t licensed. I can’t seem to find what is licensed and enabled anywhere but Epicor Admin Console and I’m not sure where that data is stored.

Just curious… How are you looking at the Menu Table? SQL Query? BAQ? Is it a Menu item in Epicor?

Just pulling the Ice.Menu table with:

WITH MenuSec (Company, MenuID, MenuDesc, SecCode, EntryList, ParentMenuID, FullMenuName, MenuLevel)
	AS
	(
	SELECT m1.Company, m1.MenuID, m1.MenuDesc, m1.SecCode, s1.EntryList, m1.ParentMenuID, CAST(m1.MenuDesc AS VARCHAR(255)) AS FullMenuName, 1 AS MenuLevel
	FROM Ice.Menu AS m1
	JOIN Ice.Security s1 ON m1.SecCode=s1.SecCode
	WHERE m1.ParentMenuID=''
	UNION ALL
	SELECT m2.Company, m2.MenuID, m2.MenuDesc, m2.SecCode, s2.EntryList, m2.ParentMenuID, CAST(FullMenuName + ' - ' + CAST(m2.MenuDesc AS VARCHAR(255)) AS VARCHAR(255)) AS FullMenuName , (MenuLevel + 1) AS MenuLevel
	FROM Ice.Menu m2
	JOIN MenuSec ON MenuSec.MenuID=m2.ParentMenuID
	JOIN Ice.Security s2 ON m2.SecCode=s2.SecCode
	)
	
	SELECT Company, MenuID, MenuDesc, SecCode, EntryList, ParentMenuID, FullMenuName, MenuLevel
	FROM MenuSec
	ORDER BY MenuLevel, FullMenuName ASC

Have you found where this list is? I am running into the same problem.

No, I haven’t ever been able to find anything in the DB that shows it.

@hkeric.wci How do you guys handle menu access reporting especially since you have to deal with SOX?

You could check the upser long License Content filed on SELECT * FROM Ice.SysLicense;

In a Nutshell:

  • We have Role based User Security Groups
  • Every Menu Folder/Item has Access to one or more Role Security Groups
  • Every Custom Menu Item always gets a unique Security ID USECXXXX
  • User is assigned always to their Role’s Security Groups

What we don’t have yet is Company Based Menu Structure, We have over 14 Multi-Companies in Epicor and sometimes a User has access to Corporate + Local, then they get all menus in both companies - definitely something to work on =)

Reporting: At this time we don’t have a query written, but we have a Master Excel Spreadsheet we have with every Menu Item and then Horizonally 100s of Role Groups with Y/N.

Have anyone found a solution to this? in E10.2.400, it looks like the data is shown in Company Config Tracker.

I was not able to locate the table where the data is stored.