I have just merged your queries into one and by replacing :1 bind variable with component name in following query and running user can find the navigation of the component (Thanks for your article, it was of great help):

WITH portal_registry AS
(SELECT RTRIM(REVERSE(sys_connect_by_path(REVERSE(portal_label), ' >> ')), ' >> ') path,
LEVEL lvl
FROM psprsmdefn
WHERE portal_name = 'EMPLOYEE'
START WITH PORTAL_OBJNAME = (SELECT PORTAL_OBJNAME FROM psprsmdefn WHERE PORTAL_NAME = 'EMPLOYEE' AND PORTAL_URI_SEG2 = :1)
CONNECT BY PRIOR portal_prntobjname = portal_objname)
SELECT path
FROM portal_registry
WHERE lvl =
(SELECT MAX(lvl)
FROM portal_registry);

Now user has to run only one query...:)

Thanks Kritdeo Bais, appreciate that!

Hi,

Here is an another similar way to find the path of your component.

SELECT LPAD('--',2*(LEVEL-1)) || PORTAL_LABEL "NAVIGATION"
FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A
WHERE PORTAL_NAME = 'EMPLOYEE' ) B
WHERE B.PORTAL_PRNTOBJNAME != ' '
START WITH (B.PORTAL_URI_SEG2 IN 'Component name in caps')
CONNECT BY PRIOR B.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME;

Works like a dream. Thanks.

this is awesome

There's also this

--
-- SHOW THE PORTAL NAVIGATION MENU USED TO ACCESS A COMPONENT
--
SELECT DISTINCT
CASE WHEN G.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(G.PORTAL_LABEL)+'>' END
+CASE WHEN F.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(F.PORTAL_LABEL)+'>' END
+CASE WHEN E.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(E.PORTAL_LABEL)+'>' END
+CASE WHEN D.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(D.PORTAL_LABEL)+'>' END
+CASE WHEN C.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(C.PORTAL_LABEL)+'>' END
+CASE WHEN B.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(B.PORTAL_LABEL)+'>' END
+CASE WHEN A.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(A.PORTAL_LABEL) END
FROM PSPRSMDEFN A
LEFT JOIN PSPRSMDEFN B ON A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN C ON B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN D ON C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN E ON D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN F ON E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN G ON F.PORTAL_PRNTOBJNAME = G.PORTAL_OBJNAME
WHERE A.PORTAL_URI_SEG2 = ''

That shows you the navigation to the component

This is cool that we are able to get the personal loans moreover, that opens up completely new possibilities.

Hello, Peter. After much searching, your SQL is the best I've found for my SQL Server environment. All of the other information for this need is Oracle specific. Thank you!

I'm not familiar with this table and have a question . . . The results of this SQL shows the navigation for all components. What do I include in the select to show the menu, component, and / or page names so I can find the navigation for a specific component? Alternatively, how do get the navigation for one specific component or page?

Thanks for contributing this information!
Pete

qesqrl

a little correction to work at Oracle ( and with my favorite SQL Benthic )

SELECT DISTINCT
CASE WHEN G.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(G.PORTAL_LABEL) || '>' END As Level1,
CASE WHEN F.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(F.PORTAL_LABEL) || '>' END As Level2,
CASE WHEN E.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(E.PORTAL_LABEL) || '>' END As Level3 ,
CASE WHEN D.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(D.PORTAL_LABEL) || '>' END As Level4 ,
CASE WHEN C.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(C.PORTAL_LABEL) || '>' END As Level5 ,
CASE WHEN B.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(B.PORTAL_LABEL) || '>' END As Level6 ,
CASE WHEN A.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(A.PORTAL_LABEL) END As Level7
FROM PSPRSMDEFN A
LEFT JOIN PSPRSMDEFN B ON A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN C ON B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN D ON C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN E ON D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN F ON E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN G ON F.PORTAL_PRNTOBJNAME = G.PORTAL_OBJNAME
WHERE A.PORTAL_URI_SEG2 = '&COMPONENT'

I use this query inside another bigger. That's why I've developed this query a little bit to insert it as a field of the bigger query. In addition, it allows the using of translate tables, in my case, to Spanish language.

SELECT ...,
...,
,(SELECT RTRIM(REVERSE(sys_connect_by_path(REVERSE(NVL(psprsmdefnlang.portal_label,psprsmdefn.portal_label)), ' > ')), ' > ') path--, LEVEL lvl, portal_prntobjname, CONNECT_BY_ROOT PSPRSMDEFN.PORTAL_OBJNAME AS PON
FROM (SELECT * FROM psprsmdefn WHERE psprsmdefn.portal_name = 'EMPLOYEE') psprsmdefn
LEFT JOIN psprsmdefnlang ON psprsmdefn.PORTAL_NAME = psprsmdefnlang.PORTAL_NAME AND psprsmdefn.PORTAL_REFTYPE = psprsmdefnlang.PORTAL_REFTYPE AND psprsmdefn.PORTAL_OBJNAME = psprsmdefnlang.PORTAL_OBJNAME AND LANGUAGE_CD = 'ESP'
WHERE psprsmdefn.portal_name = 'EMPLOYEE' AND CONNECT_BY_ISLEAF = 1 AND psprsmdefn.portal_reftype = 'F' START WITH psprsmdefn.PORTAL_OBJNAME = A.PORTAL_OBJNAME CONNECT BY NOCYCLE PRIOR psprsmdefn.portal_prntobjname = psprsmdefn.portal_objname AND PORTAL_PRNTOBJNAME <> ' '
) PATH,
...,
...

FROM psprsmdefn A

Hope be useful.
Jesús

Hi!
How can I get the path of process in PeopleSoft 8.15?
I hope can you help me.

Thank you very much!

Regards.

Thank you very much.