Query to find Portal Navigation from Menu and Component name in People Tools 8.4X

Use the below Query to find the Navigation for a component. Provide
• Menu Name
• Component Name
• Market
in the below query as your input parameter. Run this on SQL Plus.


WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL)
, ' >> '))
, ' >> ') PATH
, LEVEL LVL
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE' START WITH PORTAL_REFTYPE = 'C'
AND PORTAL_URI_SEG1 = 'TM_ARCH_DATA' -- Menu Name
AND PORTAL_URI_SEG2 = 'TM_ARC_PAY_SUMMARY' -- Component Name
AND PORTAL_URI_SEG3 = 'USA' -- Market
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (
SELECT MAX(LVL)
FROM PORTAL_REGISTRY )

SQL Logging Syntax

SPOOL ;
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON;
SET TIME ON;

-- SQL Or PL/SQL

SPOOL OFF;


SPOOL ;
WHENEVER SQLERROR CONTINUE;
SET ECHO ON;
SET TIME ON;

-- SQL Or PL/SQL

SPOOL OFF;