-- Author:         Brent Martin, ERP Associates Inc.
-- Date:           February 6, 2006
-- Purpose:        Show the portal navigation path for a specified process and the URL for direct navigation
-- Compatibility:  This works with PeopleTools 8.4x releases
-- Usage:          FindProcessNavigation <ProcessName>
-- Example:        @findprocessnavigation XRFWIN
-- Returns:        \PeopleTools\Process Scheduler\System Process Requests
--                 /EMPLOYEE/ERP/c/PROCESS_SCHEDULER.PRCSMULTI.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 in (select PNLGRPNAME from ps_prcsdefnpnl where prcsname like '&&1' || '%')
start with A.portal_prntobjname = 'PORTAL_ROOT_OBJECT' 
connect by prior A.portal_objname = A.portal_prntobjname
/
undef 1