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

The Power of Meta-Data

In my last post, What is a WEBLIB, I said that WEBLIB_QUERY was in permission list PTPT1000. How did I know that? I queried the PeopleTools tables. PeopleTools tables are tables in your database prefixed with PS, but no underscore. As you know, generally speaking, all Application Designer created records are prefixed with PS_. Most of the PeopleTools record names, however, don't contain underscores. If you haven't done so, I encourage you to take some time to get to know your PeopleTools tables. You can find a good reference on the PSST0101 site. With a good understanding of the PeopleTools tables, you can write queries and tools to help you significantly reduce your PeopleSoft administration overhead. Combining your knowledge of the PeopleTools tables with IScripts and Web development methods, you can create Bookmarklets that generate security and/or development/meta-data reports for components, etc.

Meta-data is one of PeopleSoft's differentiating factors. Many vendors provide meta-data in configuration files, but because a meta-data driven development architecture requires extra thought, most ERP vendors drop meta-data in favor of rapid, hard-coded values. Because PeopleSoft stores meta-data in the database, you, as a developer, user, and administrator, can extend PeopleSoft by writing your own tools that leverage existing meta-data.

Caveat: Please don't update the PeopleTools Meta-Data tables directly, unless, of course, told to do so by GSC or some other trustworthy Oracle/PeopleSoft expert.

Query for Component and/or CREF Navigation

I regulary have to figure out the menu/portal navigation to a component given nothing more than a component or a CREF name (AKA Content Reference or Portal Object Name). For example, looking at a Pagelet definition in the Pagelet Wizard, you may see the cryptic content reference name for a component, but nothing telling you how to navigate to that component and no hint to help you find it in the portal registry. Likewise, a user might give you the menu and component name of a troubled component, but not the navigation to that component. It is situations like this that we, as developers, can realize value from the PeopleTools meta-data. Since the navigation, CREF name, and menu/component relationship is stored in the PeopleTools database, we can query a tools table to determine this navigation. The following Oracle specific SQL displays the navigation to a given CREF name. If you use a different database, and are familiar enough with your database's SQL, please translate this statement and post it as a comment for the benefit of the rest of the community.

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_OBJNAME = :1
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

NOTE: Even though I've written my fair share of SQL, I'm no expert. Please don't grade me on the elegance and efficiency of the previous statement. If you are an SQL expert, and have a better way of writing this recursive SQL statement, then, please, for the benefit of the community, paste that statement into a comment. That said, I'll continue with the topic at hand...

If we pass EP_STANDARD_CF_TMPLT_GBL as bind :1, then the result will be Root >> Set Up Financials/Supply Chain >> Common Definitions >> Design ChartFields >> Configure >> Standard Configuration.

Modifying the above SQL statement a little, we can determine the navigation for a given menu, component, and market (the Ctrl-J data our users usually give us):

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 = :1
   AND PORTAL_URI_SEG2 = :2
   AND PORTAL_URI_SEG3 = :3
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

If we pass DESIGN_CHARTFIELDS for bind :1 (menu), STANDARD_CF_TMPLT for bind :2 (component), and GBL for bind :3 (market), then we will get the same result as the previous query, but using the Ctrl-J data rather than the CREF name.