-- Author:         Brent Martin, ERP Associates Inc.
-- Date:           February 6, 2006
-- Purpose:        Show the portal navigation path for a specified component and the URL for direct navigation
-- Compatibility:  This works with PeopleTools 8.4x releases
-- Usage:          FindComponentNavigation <ProcessName>
-- Example:        @findcomponentnavigation USERMAINT
-- Returns:        \PeopleTools\Security\User Profiles\User Profiles
--                 /EMPLOYEE/ERP/c/MAINTAIN_SECURITY.USERMAINT.GBL 

set pages 132
set lines 132
column navigation format a132
column url format a100
set verify off

select SYS_CONNECT_BY_PATH(A.PORTAL_LABEL,'\') navigation, '/EMPLOYEE/ERP/c/' || PORTAL_URI_SEG1 || '.' || PORTAL_URI_SEG2 || '.' || PORTAL_URI_SEG3 url
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
  A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE 
  FROM PSPRSMDEFN A
  WHERE 
      portal_name = 'EMPLOYEE' and
      portal_objname <> portal_prntobjname and
      not exists (
         select 'x' 
         from PSPRSMSYSATTRVL 
         where portal_name = A.PORTAL_NAME AND 
               portal_Reftype = A.PORTAL_REFTYPE and 
               portal_objname = A.PORTAL_OBJNAME and
               PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
               A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
         )
     ) A
where PORTAL_URI_SEG2 like '&&1' || '%'
start with A.portal_prntobjname = 'PORTAL_ROOT_OBJECT' 
connect by prior A.portal_objname = A.portal_prntobjname
/
undef 1