A T-SQL Script That Lists Navigations For Component

How to find navigation paths for a PeopleSoft component has been a question frequently asked and there are already quite a few posts on Internet discussing about this. For example, you can visit here or here for some useful SQL scripts that can do this. However, these SQLs are written for Oracle and can't be used for MS SQL Server because they make use of an Oracle specific function SYS_CONNECT_BY_PATH() which is not implemented in MSSQL.


Recently I created a script using T-SQL, as requested by my colleagues who are working on MSSQL 2008. This script supports two search mode:

• Search by Component Name. Assigning a component name to @COMP_NAME, the script will list out all navigation paths for that component. For example

     declare @COMP_NAME as nvarchar(18) = 'USERMAINT';

          Output will be

      Component: USERMAINT
      Menu Path: PeopleTools > Security > User Profiles > User Profiles

• Search by Component Label. Assigning a component navigation label to@COMP_LABEL, the script will list our all components with the particular lable. For example

           declare @COMP_LABEL as nvarchar(30) = 'User Profiles';

           Output will be

      Component: USERMAINT 

      Menu Path: PeopleTools > Security > User Profiles > User Profiles
      Component: DSUSRPROF2 

      Menu Path: Enterprise Components > Directory Interface > Mappings > User Profiles          

           If 'Y' is set for @IGNORE_CASE you can even search for label without worrying about case sensitivity.

           The script is as follows:

/******************************************************************************
 Filename   : FIND_MENUPATHS.SQL
 Version    : 1.0
 Description: This script query PeopleSoft table PSPRSMDEFN to find out menu
              paths for a component
 Author     : devwfb@gmail.com
 Date       : 08 NOV 2012
 ******************************************************************************/

--
-- Parameters
--
--=============================================================================
-- QUERY MODE 1 - By Component
declare @COMP_NAME as nvarchar(18) = '';
--=============================================================================
-- QUERY MODE 2 - By Label
declare @COMP_LABEL as nvarchar(30) = '';
declare @IGNORE_CASE as nvarchar(1) = 'Y';

--
-- Constants & Variables
--
declare @PORTAL_NAME as nvarchar(30) = 'EMPLOYEE';
declare @SEP as nvarchar(3) = '>';
declare @component_name as nvarchar(30);
declare @prev_component as nvarchar(30) = '';
declare @portal_label as nvarchar(30);
declare @menu_path as nvarchar(max);
declare @parent_obj as nvarchar(30);

--
-- Verification for parameters
--
set @COMP_NAME = ltrim(rtrim(@COMP_NAME));
set @COMP_LABEL = ltrim(rtrim(@COMP_LABEL));
if @COMP_NAME = '' and @COMP_LABEL = '' 
begin
 print 'Alert: Please provide parameter Component Name or Component Label';
 return
end
else if @COMP_NAME <> '' and @COMP_LABEL <> '' 
begin
 print 'Alert: Component Name and Component Label are mutually exclusive. Please provide only one parameter';
 return
end

--
-- Search menu paths
--
if @COMP_NAME <> ''
 -- Mode 1
 declare cur_navi cursor for
  select PORTAL_PRNTOBJNAME, 
      PORTAL_LABEL,
      PORTAL_URI_SEG2
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_CREF_URLT = 'UPGE'
     and (PORTAL_URI_SEG2 = @COMP_NAME or PORTAL_URI_SEG2 like @COMP_NAME);
else if @IGNORE_CASE = 'Y'
 declare cur_navi cursor for
  select PORTAL_PRNTOBJNAME, 
      PORTAL_LABEL,
      PORTAL_URI_SEG2
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_CREF_URLT = 'UPGE'
     and (upper(PORTAL_LABEL) = upper(@COMP_LABEL) or upper(PORTAL_LABEL) like upper(@COMP_LABEL));
else
 declare cur_navi cursor for
  select PORTAL_PRNTOBJNAME, 
      PORTAL_LABEL,
      PORTAL_URI_SEG2
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_CREF_URLT = 'UPGE'
     and (PORTAL_LABEL = @COMP_LABEL or PORTAL_LABEL like @COMP_LABEL);
open cur_navi
fetch next from cur_navi into @parent_obj, @portal_label, @component_name
if @@fetch_status <> 0
begin
 close cur_navi
 deallocate cur_navi
 print 'No menu paths found matching given parameter.'
 return
end
while @@fetch_status = 0
begin
 set @menu_path = @portal_label;
 while 1=1
 begin
  select @parent_obj = PORTAL_PRNTOBJNAME, 
      @portal_label = PORTAL_LABEL
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_OBJNAME <> PORTAL_PRNTOBJNAME
     and PORTAL_OBJNAME = @parent_obj;
  if @parent_obj is null or @portal_label = 'Root' break;
  set @menu_path = @portal_label + ' ' + @SEP + ' ' + @menu_path
 end 
 if @component_name <> @prev_component 
 begin 
  print ''
  print 'Component: ' + @component_name;
  set @prev_component = @component_name;
 end
 print 'Menu Path: ' + @menu_path;
 fetch next from cur_navi into @parent_obj, @portal_label, @component_name
end
close cur_navi
deallocate cur_navi