• Skip to main content
  • Skip to primary sidebar
  • Home
  • Connect
  • Contact
  • About Us

BareFoot PeopleSoft

Oracle PeopleSoft Tips and Tricks

Tip 049: Finding the Portal Location

March 21, 2018

SQL | Finding out the portal location for a component or page

<< Previous  | Next >>

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.

 

See Also:

<< Previous  | Next >>

Filed Under: SQL Tagged With: Component, Page, Portal

Reader Interactions

Primary Sidebar

Categories

  • Administration
  • Application Engine
  • BI Publisher
  • COBOL
  • Data Mover
  • PeopleCode
  • PeopleTools
  • PS Query
  • Security
  • SQL
  • Utilities

Copyright © 2023 Elimbah Consulting Ltd