Menu Item Locator

zschaaf

Member
I am wanting to create a new interactive application where the user inputs a report name, and the application shows the menu path to the application on the web client.

Any suggestions?
 

brandonkirsch

Active Member
Here's a (SQL Server) query that extracts & displays the entire JDE E1 menu. I use this sometimes to "find all menu items for a certain program."

You could build an application that does the same thing in JDE. Or you can be lazy and just save the query results to an Excel file.

Code:
WITH
Tops AS (
	SELECT 
		*
	FROM TESTCTL.F9000
	WHERE TMTASKID IN ('100','110','120','130','140') -- Only process these top-level menu items
),
Details AS (
	SELECT 
		*
	FROM 
		TESTCTL.F9000 LEFT JOIN TESTCTL.F9001 ON TMTASKID = TRCHILDTSK
),
Tree AS (
	SELECT 
		Tops.TMTASKID as TopLevelTaskID,
		Details.TRPARNTTSK as ParentID,
		Details.TRCHILDTSK as ChildID,
		1 as Level,
		CAST(RTRIM(Tops.TMLNGTASK) + ' | ' + RTRIM(Details.TMLNGTASK) AS VARCHAR(1000)) as FullPathDesc,
		RTRIM(Details.TMLNGTASK) as TaskName,
		CAST(CONCAT(RTRIM(Tops.TMTASKID), '|', RIGHT(CONCAT('0000',Details.TRPRSSEQ),4), '|') as VARCHAR(40)) as SortOrder,
		CASE WHEN Details.TMTASKTYPE = '07' THEN 'Folder' ELSE 'Item' END as ItemType,
		Details.TMOBNM,
		Details.TMVER,
		Details.TMFMNM
	FROM 
		Tops LEFT JOIN Details ON Tops.TMTASKID = Details.TRPARNTTSK
	
	UNION ALL
		
	SELECT 
		Tree.TopLevelTaskID,
		Details.TRPARNTTSK,
		Details.TRCHILDTSK,
		Tree.Level + 1,
		CAST( Tree.FullPathDesc + ' | ' + RTRIM(Details.TMLNGTASK) AS VARCHAR(1000)),
		RTRIM(Details.TMLNGTASK),
		CAST( CONCAT(Tree.SortOrder, RIGHT(CONCAT('0000',Details.TRPRSSEQ),4)) AS VARCHAR(40)),
		CASE WHEN Details.TMTASKTYPE = '07' THEN 'Folder' ELSE 'Item' END as ItemType,
		Details.TMOBNM,
		Details.TMVER,
		Details.TMFMNM
	FROM 
		Tree INNER JOIN Details ON Tree.ChildID = Details.TRPARNTTSK
		
),
Unioned AS (
	
	SELECT 
		FullPathDesc,
		ItemType,
		SortOrder,
		TMOBNM,
		TMVER,
		TMFMNM
	FROM Tree

	UNION ALL

	SELECT
		TMLNGTASK,
		'Folder',
		CONCAT(RTRIM(TMTASKID),'|'),
		'',
		'',
		''
	FROM Tops
)
SELECT *, CONCAT(RTRIM(TMOBNM),'|',RTRIM(TMFMNM),'|',RTRIM(TMVER)) as FastPath FROM Unioned
ORDER BY SortOrder

Caution: Our JDE 9.1 instance came with a corrupted menu entry that causes an "infinite loop." If you get an error regarding too much recursion, then your E1 Menu has a corrupted entry as well.
 

johndanter

Legendary Poster
I always use ObjectBrowser looking over F9000.
TMOBNM being your object

You could just create a simple find browse over F9000 using your own BSVW or anyone of the BSVWs you find in XREF using F9000
 
Top