Query for Component Path within PeopleSoft Portal

Your rating: None Average: 3.8 (17 votes)

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

SELECT PORTAL_NAME,
 PORTAL_OBJNAME AS CONTENT_REFERENCE,
 PORTAL_LABEL,
 PORTAL_URI_SEG1 AS MENU,
 PORTAL_URI_SEG2 AS COMPONENT,
 PORTAL_URI_SEG3 AS MARKET
FROM psprsmdefn
 WHERE PORTAL_NAME = 'EMPLOYEE'
   AND PORTAL_URI_SEG2 = :1;

-- Replace :1 with the component name you are looking for.

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.

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 = :1 CONNECT BY PRIOR portal_prntobjname = portal_objname)
SELECT path
FROM portal_registry
WHERE lvl =
  (SELECT MAX(lvl)
   FROM portal_registry);

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!

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Kritdeo Bais's picture
Kritdeo Bais (not verified)
Re: Query for Component Path within PeopleSoft Portal

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...:)

Lepa's picture
User offline. Last seen 4 days 2 hours ago. Offline
Joined: 06/23/2008
Posts: 590
Re: Query for Component Path within PeopleSoft Portal

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

Guest's picture
Guest (not verified)
Re: Query for Component Path within PeopleSoft Portal

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;

Rahul P's picture
Rahul P (not verified)
Re: Query for Component Path within PeopleSoft Portal

Works like a dream. Thanks.

WILL's picture
WILL (not verified)
Re: Query for Component Path within PeopleSoft Portal

this is awesome

Peter Morris's picture
Peter Morris (not verified)
Re: Query for Component Path within PeopleSoft Portal

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

personal loans's picture
personal loans (not verified)
reply this post

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

Guest's picture
Guest (not verified)
Re: Query for Component Path within PeopleSoft Portal

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

nbqjjt's picture
nbqjjt (not verified)
nbqjjt

qesqrl

ChiDONEt's picture
User offline. Last seen 1 year 43 weeks ago. Offline
Joined: 09/24/2008
Posts: 8
Re: Query for Component Path within PeopleSoft Portal

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'

Guest's picture
Guest
Re: Query for Component Path within PeopleSoft Portal

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

Guest's picture
Guest
Re: Query for Component Path within PeopleSoft Portal

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

Thank you very much!

Regards.

Guest's picture
Guest
Re: Query for Component Path within PeopleSoft Portal

Thank you very much.

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
7 + 9 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.