Find Portal Menu Navigation Path

PORTAL_OBJNAME is the key -

1. If a few page heading text are known, whether at Folder or Content level, use this sql to get PORTAL_OBJNAME:

Select *  From psprsmdefn Where portal_name ='XXXXX' And upper(DESCR254) Like upper('%fuzzy header%')
or

Select * From psprsmdefn Where portal_name ='XXXXX'   And upper(PORTAL_LABEL) Like upper('%fuzzy header%')

Use this sql with the PORTAL_OBJNAME:

With psnav As(Select RTRIM(Reverse(sys_connect_by_path(Reverse(portal_label), ' >----- ')), ' >----- ') navpath, Level lvl From psprsmdefn A Where portal_name = 'XXXXX' START With PORTAL_OBJNAME = 'YYYYY' CONNECT By PORTAL_OBJNAME = Prior PORTAL_PRNTOBJNAME)Select navpath From psnav Where lvl=(Select max(lvl) From psnav)

This traverses from the Portal root down to the queried object. To go from queried object down:

Select LTRIM((sys_connect_by_path(portal_label, ' =====> ')),'=====> ' ) path, Level lvl
From psprsmdefn A
Where portal_name = 'XXXXX'
START With PORTAL_OBJNAME = 'EPGL_IMPORT_JOURNALS'
CONNECT By PORTAL_PRNTOBJNAME = Prior PORTAL_OBJNAME

2. If RECNAME/PANEL/PANELGROUP is known:

Select * From ps_XREF_PANEL_VW Where recname=upper('MY_RECNAME') and fieldname = upper('_____' )-- get PNLNAME
Select * From ps_PNLGROUP_VW Where PNLNAME=upper('MY_PNLNAME')     -- get PNLGRPNAME
Select * From psprsmdefn Where PORTAL_NAME = upper( 'XXXXX' )And PORTAL_URI_SEG2 = 'MY_PNLGRPNAME' -- find PORTAL_OBJNAME

Use above sqls to find navigation up and down from the PORTAL_OBJNAME.

3. If a few field label texts on the page are known:

Select * From pspnlfield Where upper(lbltext) Like upper('%XXXXX%') --- find page name

if label is in Message Cat:

Select * From pspnlfield where (GRDLBLMSGSET, GRDLBLMSGNUM) in (
select MESSAGE_SET_NBR, MESSAGE_NBR from PSMSGCATDEFN
where upper(MESSAGE_TEXT) like upper ('%XXXXX%'))

put together:

Select * From pspnlfield Where upper(lbltext) Like upper('%XXXXX%')union Select * From pspnlfield where (GRDLBLMSGSET, GRDLBLMSGNUM) in (select MESSAGE_SET_NBR, MESSAGE_NBR from PSMSGCATDEFN where upper(MESSAGE_TEXT) like upper ('%XXXXX%'))

use above sqls to find navigation up and down from the PORTAL_OBJNAME.

(LABEL_ID - select * from PSDBFLDLABL where fieldname ='DESCR254'  )

4. If Process Name is known:

select * from ps_PRCSDEFNPNL where prcsname='VCHR_APRV_AF'

apply PNLGRPNAME in the query above.