Portal Navigation December 9, 2007
Posted by Tipster in Oracle, PeopleSoft, SQL.trackback
I would imagine many of you are familiar with the PSPRSMDEFN table as it is tremendously useful. I would gamble most have a SQL snippet tucked away somewhere that will query it to find the PIA navigation to any given component. However Jim Marion has posted some SQL on his blog that is the tidiest I’ve seen to date (it’s Oracle specific BTW).
Most SQL snippets use multiple joins to the table, I’ve seen one before that used connect_by_prior, but this one is the smallest script yet.
Thanks Jim!
Edit:
this slightly more succinct version may be even better.
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 = :1
connect by prior
portal_prntobjname = portal_objname
Comments»
No comments yet — be the first.