Find Component Path Within PeopleSoft Portal

Your rating: None Average: 2 (1 vote)

Update: You might want to check my new post to find the path with no level limitations.

I wrote an SQL to find the portal path for a specific component. SO, no more guessing and questions like "Where the heck is this component at?" or "How do I get to this component?"

SELECT LEVEL0.PORTAL_LABEL || ' >> ' || LEVEL1.PORTAL_LABEL || ' >> ' || LEVEL2.PORTAL_LABEL || ' >> ' || level3.PORTAL_LABEL PATH_TO_COMPONENT, LEVEL3.*
FROM PSPRSMDEFN level3
, PSPRSMDEFN level2
, PSPRSMDEFN level1
, PSPRSMDEFN LEVEL0
WHERE level3.PORTAL_URI_SEG2 = 'component_u_r_looking_for'
AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME
AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME
AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME
AND level3.PORTAL_NAME = level2.PORTAL_NAME
AND level2.PORTAL_NAME = level1.PORTAL_NAME
AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME;

There is one limitation for the SQL though and that is I'm assuming that the component is always 3 levels down or more. Root > one more > one more > my component

It will work fine if you are looking for a component that is more than 3 levels down but you will not see the whole path. BUT if you are searching for a component that is 2 levels down (Root >Self Service) the SQL will not bring back any results.

If you would like the SQL to find components that are 2 levels in, then just get rid of PSPRSMDEFN level3 and adjust the SQL accordingly.

Hope this was worth my time :)

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!