While back, I've created an SQL to find the exact same thing but with some level limitations. I have came across another query written by Jim's PeopleSopft Journal blog that works a little bit better and thought to share it with my visitors.
Jim's query however requires the developer to know the content reference of a component before the query could be used. What I really wanted is the ability to find the path by just knowing the component and not the content reference. Chances are, if you know the component reference you know the path to that component as well. So, to find the component path without knowing the content reference, follow the steps below:
1) Run the below SQL to get the content reference name for your component
2) From the query above - copy the value in the CONTENT_REFERENCE field and replace the ":1" variable and you will have the path to your component.
So, the 1st query is to get the content reference for a component name that you know and then using Jim's query to find the path!
Title | Under | Posted on |
---|---|---|
Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
Comments
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!
Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog
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.