Script that lists applications a role has access from E1 menu

[Amidug]

Works with Oracle Database only

SELECT tmobnm APPL, tmver VER, tmfmnm FORM, tmlngtask TNAME, TFOLDER, tmtasktype
FROM (
SELECT LEVEL, t0.tmtasktype, t0.tmlngtask, t1.TRCHILDTSK,
(SELECT tmlngtask FROM prodctl.f9000 t0 WHERE t0.tmtaskid=t1.TRPARNTTSK) tFOLDER,
t1.TRPARNTTSK, t0.tmobnm, t0.tmver, t0.tmfmnm
FROM prodctl.f9001 t1, prodctl.f9000 t0
WHERE t1.TRCHILDTSK=t0.TMTASKID
CONNECT BY PRIOR t1.TRCHILDTSK = t1.TRPARNTTSK
AND t1.TRPARNTTSK NOT IN
(SELECT TDCHILDTSK FROM PRODCTL.F9006 WHERE tdrltype='77' AND TDVARACTIVE='N' AND tdvarname=UPPER('&ROLE'))
START WITH TRPARNTTSK ='77'
)
WHERE tmtasktype in('01', '02')