Query for Component and/or CREF Navigation Take II

Several years ago I wrote the post Query for Component and/or CREF Navigation which demonstrated how to use Oracle's connect by clause with the portal registry to find the navigation to a PeopleSoft component. Why? Most users are trained to send a Ctrl-J screenshot to their developers when they encounter issues. Knowing the menu, market, and component is great, but developers need to know the navigation in order to replicate the issue. The point of the query is to find the navigation to some component or CREF without having to ask a functional expert for more details. As of 11gR2, Oracle now supports recursive common table expressions, a T-SQL feature supported by Microsoft and DB2. Here is a new iteration of that old post that uses Common Table Expressions. For those using non-Oracle databases, be sure to update the concatenation character to match your database platform. If you use this SQL within PeopleSoft (as a view, etc), then save yourself some potential grief and use the %Concat Meta-SQL variable instead of a database specific concatenation operator.

Full portal registry path

WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH) AS (
SELECT P.PORTAL_NAME
     , P.PORTAL_REFTYPE
     , P.PORTAL_OBJNAME
     , P.PORTAL_LABEL
     , PORTAL_URI_SEG1
     , PORTAL_URI_SEG2
     , PORTAL_URI_SEG3
     , P.PORTAL_LABEL AS PATH
  FROM PSPRSMDEFN P
 WHERE P.PORTAL_PRNTOBJNAME = ' '
UNION ALL
SELECT P_ONE.PORTAL_NAME
     , P_ONE.PORTAL_REFTYPE
     , P_ONE.PORTAL_OBJNAME
     , P_ONE.PORTAL_LABEL
     , P_ONE.PORTAL_URI_SEG1
     , P_ONE.PORTAL_URI_SEG2
     , P_ONE.PORTAL_URI_SEG3
     , PATH || ' --> ' || P_ONE.PORTAL_LABEL AS PATH
  FROM PORTAL_REGISTRY P
 INNER JOIN PSPRSMDEFN P_ONE
    ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
   AND P.PORTAL_REFTYPE = 'F'
   AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
 WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' )
   
SELECT PORTAL_NAME
     , PORTAL_OBJNAME
     , PORTAL_REFTYPE
     , PATH
     , PORTAL_LABEL
  FROM PORTAL_REGISTRY
 WHERE PORTAL_REFTYPE != 'F'

Find the path when you know the CREF ID:

WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH) AS (
SELECT P.PORTAL_NAME
     , P.PORTAL_REFTYPE
     , P.PORTAL_OBJNAME
     , P.PORTAL_LABEL
     , PORTAL_URI_SEG1
     , PORTAL_URI_SEG2
     , PORTAL_URI_SEG3
     , P.PORTAL_LABEL AS PATH
  FROM PSPRSMDEFN P
 WHERE P.PORTAL_PRNTOBJNAME = ' '
UNION ALL
SELECT P_ONE.PORTAL_NAME
     , P_ONE.PORTAL_REFTYPE
     , P_ONE.PORTAL_OBJNAME
     , P_ONE.PORTAL_LABEL
     , P_ONE.PORTAL_URI_SEG1
     , P_ONE.PORTAL_URI_SEG2
     , P_ONE.PORTAL_URI_SEG3
     , PATH || ' --> ' || P_ONE.PORTAL_LABEL AS PATH
  FROM PORTAL_REGISTRY P
 INNER JOIN PSPRSMDEFN P_ONE
    ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
   AND P.PORTAL_REFTYPE = 'F'
   AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
 WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' )
   
SELECT PORTAL_NAME
     , PORTAL_OBJNAME
     , PORTAL_REFTYPE
     , PATH
     , PORTAL_LABEL
  FROM PORTAL_REGISTRY
 WHERE PORTAL_REFTYPE != 'F'
   AND PORTAL_NAME = 'EMPLOYEE'
   AND PORTAL_OBJNAME = 'PT_EMAIL_PSWD_GBL'

Find the path when you know the menu, component, and market

WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH) AS (
SELECT P.PORTAL_NAME
     , P.PORTAL_REFTYPE
     , P.PORTAL_OBJNAME
     , P.PORTAL_LABEL
     , PORTAL_URI_SEG1
     , PORTAL_URI_SEG2
     , PORTAL_URI_SEG3
     , P.PORTAL_LABEL AS PATH
  FROM PSPRSMDEFN P
 WHERE P.PORTAL_PRNTOBJNAME = ' '
UNION ALL
SELECT P_ONE.PORTAL_NAME
     , P_ONE.PORTAL_REFTYPE
     , P_ONE.PORTAL_OBJNAME
     , P_ONE.PORTAL_LABEL
     , P_ONE.PORTAL_URI_SEG1
     , P_ONE.PORTAL_URI_SEG2
     , P_ONE.PORTAL_URI_SEG3
     , PATH || ' --> ' || P_ONE.PORTAL_LABEL AS PATH
  FROM PORTAL_REGISTRY P
 INNER JOIN PSPRSMDEFN P_ONE
    ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
   AND P.PORTAL_REFTYPE = 'F'
   AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
 WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' )
   
SELECT PORTAL_NAME
     , PORTAL_OBJNAME
     , PORTAL_REFTYPE
     , PATH
     , PORTAL_LABEL
  FROM PORTAL_REGISTRY
 WHERE PORTAL_REFTYPE != 'F'
   AND PORTAL_NAME = 'EMPLOYEE'
   AND PORTAL_URI_SEG1 = 'UTILITIES'
   AND PORTAL_URI_SEG2 = 'PSOPTIONS'
   AND PORTAL_URI_SEG3 = 'GBL';

Notice that the main table expression remains unchanged. For each of the scenarios, I'm just manipulating the query that selects from the table expression. Are you interested in learning more about Common Table Expressions? Here is a list of posts I used to gain a better understanding of this feature:

• I Heart Recursive Subquery Factoring (Recursive Common Table Expressions)
• Recursive subquery factoring
• Recursive Queries Using Common Table Expressions