ERP Associates, Inc. - Searching the PeopleSoft Portal

Searching the PeopleSoft Portal

Monday, 06 February 2006

Remember the good old days of PeopleSoft 8.3, 7.5, or earlier? Back then, if I wanted to find out where a record was used on-line all I had to do was to pull it up in App Designer and keep running Find Object References to locate the page, then the component, and finally the menu. Once I knew the menu it was pretty easy to figure out how to get there.

But since PeopleTools 8.4x, the Portal makes everything a more difficult. Now, even if you know the menu, you still have no clue how to navigate there through the portal.

So I developed a couple of scripts to find components and processes in the PeopleSoft portal, and tell you exactly how to get there.

FindComponentNavigation: This script will show you the portal navigation to the component you specify. For example:

SQL> @c:\temp\findcomponentnavigation APPMSGMONITOR

NAVIGATION

-------------------------------------------------------

URL

-------------------------------------------------------

\PeopleTools\Integration Broker\Monitor\Monitor Message

/EMPLOYEE/ERP/c/APPMSGMONITOR.APPMSGMONITOR.GBL

The first line shows the navigation, and the second shows what the last part of the URL will look like when you navigate there in your browser.

FindProcessNavigation does the same thing for processes. For example:

SQL> @c:\temp\findprocessnavigation XRFWIN

NAVIGATION

-------------------------------------------------------

URL

-------------------------------------------------------

\PeopleTools\Process Scheduler\System Process Requests

/EMPLOYEE/ERP/c/PROCESS_SCHEDULER.PRCSMULTI.GBL

Warning: Both of these were queries written for an Oracle database using SQL Plus, and use Oracle-specific formatting and query syntax. They were written to go against PeopleTools 8.45 tables, but I believe they will work with all current PeopleTools 8.4x versions.

Enjoy!

Comments (3)

Comment from Joe : Joe : http://chiliblog.chili-mango.net

Excellent! SYS_CONNECT_BY_PATH is a very handy Oracle function.

For those using MSSQL or DB2, drilling down the navigation path can be achieved by performing a series of outer joins. The following query can be used to find a component that is up to 5 levels deep.


 
SELECT
 
E.PORTAL_LABEL AS Parent4_folder,
 
D.PORTAL_LABEL AS Parent3_folder,
 
C.PORTAL_LABEL AS Parent2_folder,
 
B.PORTAL_LABEL AS Parent_folder,
 
A.PORTAL_LABEL AS Component
 
FROM
 
PSPRSMDEFN A
 
LEFT JOIN PSPRSMDEFN B
 
ON B.PORTAL_NAME = A.PORTAL_NAME
 
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
 
LEFT JOIN PSPRSMDEFN C
 
ON C.PORTAL_NAME = B.PORTAL_NAME
 
AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME
 
LEFT JOIN PSPRSMDEFN D
 
ON D.PORTAL_NAME = C.PORTAL_NAME
 
AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME
 
LEFT JOIN PSPRSMDEFN E
 
ON E.PORTAL_NAME = D.PORTAL_NAME
 
AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
 
WHERE
 
A.PORTAL_URI_SEG2 = :component
 
AND A.PORTAL_REFTYPE = 'C'
 
-- (optional) AND A.PORTAL_NAME = :portal

February 07, 2006

Comment from psguyblogger : psguyblogger : http://psguyblog.blogsopt.com

I was not able to see the SQL anyways, this is what I use,

You will need to change the following statement

and D.PORTAL_LABEL like 'C%'

***********************

parents till 4 levels

***********************

select D.PORTAL_LABEL, C.PORTAL_LABEL,B.PORTAL_LABEL, A.PORTAL_LABEL, A.PORTAL_URLTEXT, A.DESCR254 from PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D where A.PORTAL_REFTYPE = 'C'

and D.PORTAL_LABEL like 'C%'

and A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME and B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME and C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME order by 1,2, 3, 4

++++++++++++++++++++++++++++++++

***********************

parents till 7 levels

***********************

select

F.PORTAL_LABEL,E.PORTAL_LABEL,D.PORTAL_LABEL,C.PORTAL_LABEL,B.PORTAL_LABEL,

A.PORTAL_LABEL, A.PORTAL_URLTEXT, A.DESCR254 from PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D, PSPRSMDEFN E, PSPRSMDEFN F where A.PORTAL_REFTYPE = 'C'

and F.PORTAL_LABEL like 'Set%'

and A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME and B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME and C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME and D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME and E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME UNION ALL select E.PORTAL_LABEL,D.PORTAL_LABEL,C.PORTAL_LABEL,B.PORTAL_LABEL,'

', A.PORTAL_LABEL, A.PORTAL_URLTEXT, A.DESCR254 from PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D, PSPRSMDEFN E where A.PORTAL_REFTYPE = 'C'

and E.PORTAL_LABEL like 'Set%'

and A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME and B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME and C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME and D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME UNION ALL select D.PORTAL_LABEL, C.PORTAL_LABEL,B.PORTAL_LABEL,'

',' ' ,A.PORTAL_LABEL, A.PORTAL_URLTEXT, A.DESCR254 from PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D where A.PORTAL_REFTYPE = 'C'

and D.PORTAL_LABEL like 'Set%'

and A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME and B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME and C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME UNION ALL select C.PORTAL_LABEL,B.PORTAL_LABEL,' ',' ',' ', A.PORTAL_LABEL, A.PORTAL_URLTEXT, A.DESCR254 from PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C where A.PORTAL_REFTYPE = 'C'

and C.PORTAL_LABEL like 'Set%'

and A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME and B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME UNION ALL select B.PORTAL_LABEL,' ', ' ',' ',' ', A.PORTAL_LABEL, A.PORTAL_URLTEXT, A.DESCR254 from PSPRSMDEFN A, PSPRSMDEFN B where A.PORTAL_REFTYPE = 'C'

and B.PORTAL_LABEL like 'Set%'

and A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME

February 20, 2006

PS Security Admin : Mallik Karasala

This is really helpful...This is the exact thing I was searching for a while.
Thanks a lot, buddy. smilies/grin.gif