Wednesday, October 24, 2012

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:

7 comments:

Unknown said...

Hi Jim,

Always good to have these SQLs.
A several of years ago, I got the same request from a client that kept forgetting the navigation path to his pages. I had created the following PL/SQL script to find the component in the portal menu.
http://bloggingaboutoracleapplications.org/peoplesoft-quick-tip-find-navigationpath-of-a-component/

Also, now a days, this functionality is out of the box in PeopleSoft, to be found via Set Up HRMS –> System Administration –> Utilities –> Portal Navigation Path for the users that do not have access directly to the database. See following blog with describes to functionality.
http://bloggingaboutoracleapplications.org/path-to/

Regards,

Hakan Biroglu

Unknown said...

Jim,

Sorry for my previous comment, it has no relation to essence of your post, being recursive common table expressions, but was related to finding the menu path in PeopleSoft using sql.

I will definitely give the recursive common table expressions a try.

I am a big fan of the CONNECT BY PRIOR, since I learned it. I just wanted to share that I have used CONNECT BY PRIOR in a conversion script for a client that was implementing PeopleSoft, to create the department tree, as alternative to using the vanilla TreeMover which consists of an input file with 7 different file layouts. By using the CONNECT BY PRIOR and some PeopleCode I was able to get an hierarchical view and create a department tree very simply and quickly. See for an example with the sql and peoplecode on how I did this:
http://bloggingaboutoracleapplications.org/importing-creatint/

Keep posting these great mind exhilarating blogs!

Regards,

Hakan Biroglu

Charlie said...

I get the following error when trying to run the first version (list whole registry):

ORA-01489: result of string concatenation is too long

Any suggestions?

Jim Marion said...

@Charlie, interesting. My only other suggestion is to try the older version here

Charlie said...

I was actually able to get it to work by casting all the pieces of the contatenation field with to_clob().

Ketan Kothari said...

Getting the following error in Oracle 11.2.0.3 ORA-01406 - fetched column value was truncated

To avoid this error use TO_CLOB function for both union.

TO_CLOB(P.PORTAL_LABEL) AS PATH
and
TO_CLOB(PATH || ' --> ' || P_ONE.PORTAL_LABEL) AS PATH


Casey said...

This SQL is excellent, but do you any idea how to add an additional field that would end up with a value showing if the content reference is under any folder that is "hidden" in the portal.

I assume the SQL would need to check each folder it's under in the tree. It seems like it might be possible, but changing your SQL is a little advanced for me.

Thanks!
Casey