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
Output will be
Component: USERMAINT
Menu Path: PeopleTools > Security > User Profiles > User Profiles
Component: DSUSRPROF2
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
