Content Reference Menu Path SQL

How to find a "lost"content reference.

(Note that the 'Valid From Date' of the parent Folder must be less than a content reference/common mistake post migration)

Graciously lifted from Jim's PeopleSoft Journal (check out his book too).
http://jjmpsj.blogspot.com.au/2007/12/query-for-component-andor-cref.html


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 )

0 comments: