Where is that Component? March 20, 2007
Posted by Duncan in PIA.trackback
A fairly common scenario. You have a component name (or even a page name) but you don’t know the navigation path to find it in the PIA. Maybe you’re working on an area of PeopleSoft you haven’t used before, or perhaps it’s just somewhere you haven’t seen in a familiar module. Either way, you’ve got to find it.
There are a number of options.
The first – and probably the easiest – is to use the search box at the top of the left hand menu on the homepage (Tools 8.4x only). Just type the Component Name in there and it’ll display your page and show you the navigation steps. This relies on the ‘Build Registry Search Index’ process being run (PeopleTools > Portal > Build Registry Search Index).
The next alternative is using a piece of SQL to query the PRSMDEFN record to retrieve the path.
SELECT P3.PORTAL_LABEL L3
, P2.PORTAL_LABEL L2
, P1.PORTAL_LABEL L1
, P.PORTAL_LABEL L0
, P.*
FROM PSPRSMDEFN P
, PSPRSMDEFN P1
, PSPRSMDEFN P2
, PSPRSMDEFN P3
WHERE P.PORTAL_URI_SEG2 = 'COMPONENT'
AND P.PORTAL_PRNTOBJNAME = P1.PORTAL_OBJNAME
AND P1.PORTAL_PRNTOBJNAME = P2.PORTAL_OBJNAME
AND P2.PORTAL_PRNTOBJNAME = P3.PORTAL_OBJNAME
AND P.PORTAL_NAME = P1.PORTAL_NAME
AND P1.PORTAL_NAME = P2.PORTAL_NAME
AND P2.PORTAL_NAME = P3.PORTAL_NAME
You can get slightly more sophisticated if you’re running on an Oracle DB.
SELECT replace(navigation,'',' > ') "PIA Navigation"
, url
FROM (SELECT SYS_CONNECT_BY_PATH (a.portal_label, '') navigation
, '/EMPLOYEE/ERP/c/' || a.portal_uri_seg1 || '.' || a.portal_uri_seg2 || '.' || a.portal_uri_seg3 url
FROM (SELECT DISTINCT a.portal_name
, a.portal_label
, a.portal_objname
, a.portal_prntobjname
, a.portal_uri_seg1
, a.portal_uri_seg2
, a.portal_uri_seg3
, a.portal_reftype
FROM psprsmdefn a
WHERE portal_name = 'EMPLOYEE'
AND portal_objname <> portal_prntobjname
AND NOT EXISTS (SELECT 'x'
FROM psprsmsysattrvl
WHERE portal_name = a.portal_name
AND portal_reftype = a.portal_reftype
AND portal_objname = a.portal_objname
AND portal_attr_nam = 'PORTAL_HIDE_FROM_NAV'
AND a.portal_objname NOT IN ('CO_NAVIGATION_COLLECTIONS' ,'PORTAL_BASE_DATA'))) a
WHERE portal_uri_seg2 LIKE '&&1' || '%'
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR a.portal_objname = a.portal_prntobjname)
WHERE navigation NOT LIKE '%Navigation Collections%'
If anyone has a better method, let me know and I’ll post it up.
Comments
Sorry comments are closed for this entry
The PeopleSoft Tipster Blog 
If your running DB2 V8+ (z/os) (not sure about luw). The following SQL can use recursion built in at V8+. it will return a answer like (this example used PRCSMULTI)
PeopleTools->Process Scheduler->System Process Requests
SQL:
WITH TEMP(PORTAL_OBJNAME, PORTAL_PRNTOBJNAME, PORTAL_LABEL, N, PATH, PORTAL_URI_SEG2 ) AS
(SELECT PORTAL_OBJNAME, PORTAL_PRNTOBJNAME, PORTAL_LABEL, 1, CAST(PORTAL_LABEL AS CHAR(100)) AS PATH, PORTAL_URI_SEG2
FROM PSPRSMDEFN
where PORTAL_URI_SEG2 = ‘COMPONENT’
UNION ALL
SELECT b.PORTAL_OBJNAME, b.PORTAL_PRNTOBJNAME, b.PORTAL_LABEL, N + 1, RTRIM(B.PORTAL_LABEL) || ‘->’ || RTRIM(A.PATH), A.PORTAL_URI_SEG2
FROM TEMP a, PSPRSMDEFN b
WHERE a.PORTAL_PRNTOBJNAME = b.PORTAL_OBJNAME
and b.PORTAL_OBJNAME ‘PORTAL_ROOT_OBJECT’
)
SELECT PATH
FROM TEMP
ORDER BY N DESC
FETCH FIRST 1 ROWS ONLY
WITH UR;
How about using
Peopletools – Portal – View Menu Item Detail?
I get an error when I try to run build registry search index.
PeopleTools, Portal, Build Registry Search Index
“Error getting portal . (96,5)
Unable to open portal. PortalName = (95,307)”
I can’t locate in Peoplebooks what setup I need to do to create the search index.
Did you create a new run control ID? You need to create one each time you run this. It seems to have problems if you reuse an existing Run Control ID. It is mentioned in PeopleBooks, but I can’t recall the details off-hand.
After running the process, the search button works fine. But I have a problem here. This search only gives the delivered links n components. The custom components are not being serached.
Any inputs on this??
Ratish > I’ve not looked into this, but there has to be a way to get the verity search to include custom components. Have you tried PeopleBooks?
SELECT DISTINCT RTRIM(REVERSE (SYS_CONNECT_BY_PATH (REVERSE (portal_label),’ > ‘)),’ > ‘) PATH
FROM psprsmdefn
WHERE portal_name = ‘EMPLOYEE’
AND portal_prntobjname = ‘PORTAL_ROOT_OBJECT’
START WITH portal_uri_seg2 = ‘COMPONENT’
CONNECT BY PRIOR portal_prntobjname = portal_objname
@new to the game:
You might find this sometimes fails with “ORA-01436: CONNECT BY loop in user data” as the same component can exist in more than one portal. You can fix this by joining on all the keys:
select rtrim(reverse(sys_connect_by_path(reverse(portal_label), ‘ > ‘)), ‘ > ‘) path
from psprsmdefn
where portal_prntobjname = ‘PORTAL_ROOT_OBJECT’
start with
portal_name = :portal_name
and portal_reftype = ‘C’
and portal_uri_seg2 = :component_name
connect by prior portal_prntobjname = portal_objname
and prior portal_name = portal_name
and portal_reftype = ‘F’
There is an alternate way too:
If you have the component name, find out the corresponding menu name and enter the menuname.compname at the end of the URL to get navigated to the page directly, like
http:// …… /MENU_NAME.COMPONENT_NAME
Thank you Tim Palmer. I kept getting the connect y loop error. I finally came across your post. It worked!
Thank you , It worked . very useful