Ask Question
Help Others
Home › Blogs › Lepa's blog

Query for Component Path within PeopleSoft Portal

Tue, 12/18/2007 - 7:23pm — Lepa
Your rating: None Average: 4.8 (6 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!

Bookmark/Search this post with:
  • Delicious
  • Digg
  • StumbleUpon
  • Reddit
  • Furl
  • Facebook
  • Google
  • Yahoo
  • Technorati
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.

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
3 + 7 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.