SQL | Finding out the portal location for a component or page
A. The Challenge
Sometimes, the greatest challenge of being a PeopleSoft developer has nothing to do with the technical side. Rather, the challenge comes in extracting enough useful information from end users.
Users can be quite vague in the information they provide about a problematic page, presumably because they think that developers are just as familiar with all the pages as they are. So rather than giving you a helpful piece of information – such as the full menu path on the portal – they’ll give you a component name or a page name as it appears in the front end, or even just an airy-fairy general description. The expenses page isn’t working.
If you’re lucky, you’ll have a user who’s familiar with the CTRL-J functionality, so she or he will at least provide you with a component object name or page name. But the actual location on the portal still remains a mystery.
B. The Queries
Therefore to help with the troubleshooting process, I’ve included eight queries below that are all basically variations on the same theme. All these queries select data from a portal definition table (PSPRSMDEFN) and then string the end result together into a complete menu path. Therefore, depending on what piece of information you know in advance – component name, page name, component menu label, or page label – you can go ahead and run the relevant query below. In all cases, replace the text in bold with your example.
These examples also assume a Portal Name of ‘EMPLOYEE’. If this is different at your site, you will need to replace ‘EMPLOYEE’ with ‘YOUR_PORTAL_NAME’. If you’re not sure of your portal name, take a look at the ‘Portal Name’ field in:
PeopleTools -> Portal -> General Settings
(a) When the component object name is known:
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 = 'INSTALLATION_TBL'
connect by prior
portal_prntobjname = portal_objname;
(b) When the component object name is suspected and you want to do a ‘like’ search:
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 like '%INSTALL%'
connect by prior
portal_prntobjname = portal_objname;
(c) When the component menu label is known exactly:
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_label = 'Student Admin Installation'
connect by prior
portal_prntobjname = portal_objname;
(d) When the component menu label is ‘sort of’ known and you want to do a ‘like’ search:
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_label like '%Installation%'
connect by prior
portal_prntobjname = portal_objname;
(e) When the page object name is known:
select distinct rtrim(reverse
(sys_connect_by_path(reverse
(portal_label), ' > ')), ' > ') path
from psprsmdefn a
where portal_name = 'EMPLOYEE'
and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
start with portal_uri_seg2 in (select pnlgrpname
from pspnlgroup b
where a.portal_uri_seg2 = b.pnlgrpname
and b.pnlname = 'INSTALLATION_TBL1')
connect by prior
portal_prntobjname = portal_objname;
(f) When the page object name is suspected and you want to do a ‘like’ search:
select distinct rtrim(reverse
(sys_connect_by_path(reverse
(portal_label), ' > ')), ' > ') path
from psprsmdefn a
where portal_name = 'EMPLOYEE'
and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
start with portal_uri_seg2 in (select pnlgrpname
from pspnlgroup b
where a.portal_uri_seg2 = b.pnlgrpname
and b.pnlname like '%INSTALL%')
connect by prior
portal_prntobjname = portal_objname;
(g) When the page label is known exactly (this is what appears in component tabs and hyperlinks):
select distinct rtrim(reverse
(sys_connect_by_path(reverse
(portal_label), ' > ')), ' > ') path
from psprsmdefn a
where portal_name = 'EMPLOYEE'
and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
start with portal_uri_seg2 in (select pnlgrpname
from pspnlgroup b
where a.portal_uri_seg2 = b.pnlgrpname
and b.itemlabel = 'Override')
connect by prior
portal_prntobjname = portal_objname;
(h) When the page label is suspected and you want to do a ‘like’ search:
select distinct rtrim(reverse
(sys_connect_by_path(reverse
(portal_label), ' > ')), ' > ') path
from psprsmdefn a
where portal_name = 'EMPLOYEE'
and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
start with portal_uri_seg2 in (select pnlgrpname
from pspnlgroup b
where a.portal_uri_seg2 = b.pnlgrpname
and b.itemlabel like '%Override%')
connect by prior
portal_prntobjname = portal_objname;
Incidentally, the core part of the SQL statement above was discovered on a PeopleSoft technical support website many years ago. I can no longer find the original page, so have no idea who the developer was. If anyone knows, feel free to get in touch and I will give the appropriate credit.