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.

My Quick PL/SQL

Read a File:

Create Directory TMP_DIR As 'xxxxxxxx';

Declare
input_file utl_file.file_type;
input_buffer varchar2(98);
pos NUMBER := 0;

Begin
input_file := utl_file.fopen ('TMP_DIR','aaaa.bbb', 'R');

If utl_file.is_open(input_file) Then
Loop
Begin
utl_file.get_line(input_file, input_buffer);

If input_buffer Is Null Then
EXIT;
End If;

Select instr(input_buffer, 'NUMBER OF ROWS FETCHED') Into pos From dual;

If pos > 0
Then
Select to_number(substr(input_buffer, 92,7)) Into inrcgl_cnt From dual;
dbms_output.put_line('INRCGL:'|| inrcgl_cnt);
End If;

Exception
When NO_DATA_FOUND Then
EXIT;
End;
End Loop;

End If;

utl_file.fclose(input_file);*/