Useful Functions
From TekiWiki
Microsoft SQL Server script useful for PeopleSoft:
/**************************************************************************************
aaaaUsefulFunctions.sql - a useful set of global temporary procedures
for use with Psoft SQLServer
***************************************************************************************/
/**************************************************************************************
Global Temporary Procedure for Employee Info Summary
Usage:
##emp '0024'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##emp')
drop procedure ##emp
go
CREATE PROCEDURE ##emp
@in_emplid varchar(11)
AS
select 'PSOPRDEFN',* from PSOPRDEFN where EMPLID=@in_emplid
order by 1,2,3,4
select 'PS_PERSON',* from PS_PERSON where EMPLID=@in_emplid
order by 1,2,3,4
select 'PS_PERS_DATA_EFFDT',* from PS_PERS_DATA_EFFDT where EMPLID=@in_emplid
order by 1,2,3,4
select 'PS_PERS_NID',* from PS_PERS_NID where EMPLID=@in_emplid
order by 1,2,3,4
select 'PS_EMPLOYMENT',* from PS_EMPLOYMENT where EMPLID=@in_emplid
order by 1,2,3,4
select 'PS_NAMES',* from PS_NAMES where EMPLID=@in_emplid
order by 1,2,3,4
select 'PS_PERSONAL_PHONE',* from PS_PERSONAL_PHONE where EMPLID=@in_emplid
order by 1,2,3,4
select 'PS_JOB_JR',* from PS_JOB_JR where EMPLID=@in_emplid
order by 1,2,3,4,5
select 'PS_JOB',* from PS_JOB where EMPLID=@in_emplid
order by 1,2,3,4,5
select 'PS_COMPENSATION',* from PS_COMPENSATION where EMPLID=@in_emplid
order by 1,2,3,4,5,6
go
##emp '0024'
go
/**************************************************************************************
Global Temporary Procedure for Finding Employee
Usage:
##findemp 'Ander'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##findemp')
drop procedure ##findemp
go
CREATE PROCEDURE ##findemp
@in_value varchar(1000)
AS
declare @pad char(30)
set @pad = REPLICATE(' ', 30)
declare @pct char(1)
set @pct = '%'
select left('PSOPRDEFN' COLLATE DATABASE_DEFAULT + @pad,30) as Record,*
from PSOPRDEFN
where
upper(EMPLID) like upper(@in_value)
or upper(OPRID) like upper(@in_value)
order by 1,2,3,4
select left('PS_PERSONAL_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME
from PS_PERSONAL_DATA
where upper(EMPLID) like upper(@in_value)
or upper(NAME) like upper(@pct + @in_value + @pct)
order by 1,2,3
select left('PS_PERS_DATA_EFFDT' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,EFFDT
from PS_PERS_DATA_EFFDT
where upper(EMPLID) like upper(@in_value)
order by 1,2,3
select left('PS_NAMES (v8)' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME_TYPE,NAME
from PS_NAMES
where upper(EMPLID) like upper(@in_value)
or upper(NAME) like upper(@pct + @in_value + @pct)
order by 1,2,3,4
select left('PS_PERS_NID' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,COUNTRY,NATIONAL_ID_TYPE,NATIONAL_ID
from PS_PERS_NID
where upper(EMPLID) like upper(@in_value)
or upper(NATIONAL_ID) like upper(@pct + @in_value + @pct)
order by 1,2,3,4
select left('PS_HR_TBH_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,TBH_SEQ,RECNAME,FIELDNAME,TBH_CHAR
from PS_HR_TBH_DATA
where upper(TBH_CHAR) like upper(@pct + @in_value + @pct)
order by 1,2,3,4
select left('PSOPRDEFN' COLLATE DATABASE_DEFAULT + @pad,30) as Record,OPRID,EMPLID,OPRDEFNDESC
from PSOPRDEFN
where upper(OPRID) like upper(@in_value)
or upper(OPRID) like upper(@pct + @in_value + @pct)
or upper(OPRDEFNDESC) like upper(@pct + @in_value + @pct)
or upper(EMPLID) like upper(@pct + @in_value + @pct)
order by 1,2,3,4
go
##findemp NX
/**************************************************************************************
Global Temporary Procedure for Employee Job Info Summary
Usage:
##jstar '0024'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##jstar')
drop procedure ##jstar
go
CREATE PROCEDURE ##jstar
@in_emplid varchar(11)
AS
select * from PS_JOB where EMPLID=@in_emplid
order by 1,2,3,4
go
##jstar '0024'
go
/**************************************************************************************
Global Temporary Procedure for Message Catalog Text Summary
Usage:
##msg 'text'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##msg')
drop procedure ##msg
go
CREATE PROCEDURE ##msg
@in_text varchar(2000)
AS
print 'PSMSGCATDEFN'
select * from PSMSGCATDEFN
where
upper(MESSAGE_TEXT) like upper('%' COLLATE DATABASE_DEFAULT +@in_text+'%' COLLATE DATABASE_DEFAULT)
or upper(DESCRLONG) like upper('%' COLLATE DATABASE_DEFAULT+@in_text+'%' COLLATE DATABASE_DEFAULT)
or replace(@in_text,' ' COLLATE DATABASE_DEFAULT,'') = ltrim(str(MESSAGE_SET_NBR))+','+ ltrim(str(MESSAGE_NBR))
order by 1,2,3,4
print 'PSMSGCATLANG'
select * from PSMSGCATLANG
where
upper(MESSAGE_TEXT) like upper('%' COLLATE DATABASE_DEFAULT +@in_text+'%' COLLATE DATABASE_DEFAULT)
or upper(DESCRLONG) like upper('%' COLLATE DATABASE_DEFAULT+@in_text+'%' COLLATE DATABASE_DEFAULT)
or replace(@in_text,' ' COLLATE DATABASE_DEFAULT,'') = ltrim(str(MESSAGE_SET_NBR))+','+ ltrim(str(MESSAGE_NBR))
order by 1,2,3,4
print 'PS_HR_SSTEXT_TEXT'
select * from PS_HR_SSTEXT_TEXT
where
upper(HR_SSTEXT_TEXT) like upper('%' COLLATE DATABASE_DEFAULT +@in_text+'%' COLLATE DATABASE_DEFAULT)
order by 1,2,3,4
go
##msg Explanatory
go
##msg '14510,221'
go
/**************************************************************************************
Global Temporary Procedure for Employee Position Info Summary
Usage:
##posstar '6000008'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##posstar')
drop procedure ##posstar
go
CREATE PROCEDURE ##posstar
@in_pos varchar(11)
AS
select * from PS_POSITION_DATA where POSITION_NBR=@in_pos
order by 1,2,3,4
go
##posstar 6000008
go
/**************************************************************************************
Global Temporary Procedure for Template based Hire (TBH)/ Smart Hire check
Usage:
##tbh 321
where 321 is the TBH Sequence number (blank is latest)
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##tbh')
drop procedure ##tbh
go
CREATE PROCEDURE ##tbh
@in_seq integer = null
AS
if @in_seq is not null
begin
select * from PS_HR_REQUEST R where R.TBH_SEQ=@in_seq
select * from PS_HR_TBH_HDR H where H.TBH_SEQ=@in_seq
select * from PS_HR_TBH_DATA D where D.TBH_SEQ=@in_seq order by 1,2,3,4,5,6
end
else
begin
select * from PS_HR_REQUEST R
where R.TBH_SEQ=(select MAX(H1.TBH_SEQ) from PS_HR_TBH_HDR H1)
select * from PS_HR_TBH_HDR H
where H.TBH_SEQ=(select MAX(H1.TBH_SEQ) from PS_HR_TBH_HDR H1)
select * from PS_HR_TBH_DATA D
where D.TBH_SEQ=(select MAX(H1.TBH_SEQ) from PS_HR_TBH_HDR H1)
order by 1,2,3,4,5,6
end
go
##tbh 127
/**************************************************************************************
Global Temporary Procedure for Get Manager (Position Management)
Usage:
##mgr '0024'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##mgr')
drop procedure ##mgr
go
CREATE PROCEDURE ##mgr
@in_emplid varchar(11)
AS
-- Finding the reports to automatically
declare @reports_to varchar(1000)
declare @emplid varchar(1000)
declare @managerEmplid varchar(1000)
set @emplid=@in_emplid
select @reports_to = REPORTS_TO
from PS_JOB J
where
EMPLID=@emplid
and J.EFFDT=(
SELECT MAX(J1.EFFDT)
FROM PS_JOB J1
WHERE
J1.EMPLID=J.EMPLID
AND J1.EMPL_RCD=J.EMPL_RCD
AND J1.EFFDT<=getdate()
)
AND J.EFFSEQ=(
SELECT MAX(J2.EFFSEQ)
FROM PS_JOB J2
WHERE
J2.EMPLID=J.EMPLID
AND J2.EMPL_RCD=J.EMPL_RCD
AND J2.EFFDT=J.EFFDT
)
-- AND J.EMPL_STATUS IN ('A','P','L','S','W')
print @emplid + ' reports to position ' COLLATE DATABASE_DEFAULT + @reports_to
select @managerEmplid = EMPLID
from PS_JOB J
where
POSITION_NBR=@reports_to
and J.EFFDT=(
SELECT MAX(J1.EFFDT)
FROM PS_JOB J1
WHERE
J1.EMPLID=J.EMPLID
AND J1.EMPL_RCD=J.EMPL_RCD
AND J1.EFFDT<=getdate()
)
AND J.EFFSEQ=(
SELECT MAX(J2.EFFSEQ)
FROM PS_JOB J2
WHERE
J2.EMPLID=J.EMPLID
AND J2.EMPL_RCD=J.EMPL_RCD
AND J2.EFFDT=J.EFFDT
)
AND J.EMPL_STATUS IN ('A','P','L','S','W')
print @reports_to + ' is currently held by ' COLLATE DATABASE_DEFAULT + @managerEmplid
go
##mgr '0024'
go
/**************************************************************************************
Global Temporary Procedure for showing psquery (Query Name)
Usage:
##psquery 'Query'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##psquery')
drop procedure ##psquery
go
CREATE PROCEDURE ##psquery
@query varchar(30)
AS
print @query + ' Query defined as...' COLLATE DATABASE_DEFAULT
select * from PSQRYDEFN where upper(QRYNAME) like upper(@query)
order by 1,2,3,4;
print @query + ' Query selects fields...' COLLATE DATABASE_DEFAULT
select * from PSQRYFIELD where upper(QRYNAME) like upper(@query)
order by 1,2,3,4;
print @query + ' From these records...' COLLATE DATABASE_DEFAULT
select * from PSQRYRECORD where upper(QRYNAME) like upper(@query)
order by 1,2,3,4;
print @query + ' With these criteria...' COLLATE DATABASE_DEFAULT
select * from PSQRYCRITERIA where upper(QRYNAME) like upper(@query)
order by 1,2,3,4;
print @query + ' With these expressions...' COLLATE DATABASE_DEFAULT
select * from PSQRYEXPR where upper(QRYNAME) like upper(@query)
order by 1,2,3,4;
print @query + ' With these bind variables...' COLLATE DATABASE_DEFAULT
select * from PSQRYBIND where upper(QRYNAME) like upper(@query)
order by 1,2,3,4;
print @query + ' With these links...' COLLATE DATABASE_DEFAULT
select * from PSQRYLINK where upper(QRYNAME) like upper(@query)
order by 1,2;
print @query + ' With this select...' COLLATE DATABASE_DEFAULT
select * from PSQRYSELECT where upper(QRYNAME) like upper(@query)
order by 1,2,3,4;
print @query + ' Has it been deleted?' COLLATE DATABASE_DEFAULT
select * from PSQRYDEL where upper(QRYNAME) like upper(@query)
order by 1,2,3;
-- print @query + ' PSDIAGQRYCLASS' COLLATE DATABASE_DEFAULT
-- select * from PSDIAGQRYCLASS where upper(QRYNAME) like upper(@query)
-- order by 1,2,3;
-- print @query + ' PSQRYACCLSTRECS' COLLATE DATABASE_DEFAULT
-- select * from PSQRYACCLSTRECS where upper(QRYNAME) like upper(@query)
-- order by 1,2,3;
print @query + ' PSQRYEXECLOG' COLLATE DATABASE_DEFAULT
select * from PSQRYEXECLOG where upper(QRYNAME) like upper(@query)
order by 1,2,3;
print @query + ' PSQRYFAVORITES' COLLATE DATABASE_DEFAULT
select * from PSQRYFAVORITES where upper(QRYNAME) like upper(@query)
order by 1,2,3;
-- print @query + ' PSQRYFLAGS' COLLATE DATABASE_DEFAULT
-- select * from PSQRYFLAGS where upper(QRYNAME) like upper(@query)
-- order by 1,2,3;
-- print @query + ' PSQRYPREFS' COLLATE DATABASE_DEFAULT
-- select * from PSQRYPREFS where upper(QRYNAME) like upper(@query)
-- order by 1,2,3;
print @query + ' PSQRYSTATS' COLLATE DATABASE_DEFAULT
select * from PSQRYSTATS where upper(QRYNAME) like upper(@query)
order by 1,2,3;
print @query + ' PSQRYTRANS' COLLATE DATABASE_DEFAULT
select * from PSQRYTRANS where upper(QRYNAME) like upper(@query)
order by 1,2,3;
go
##psquery 'EMPLOYEES'
go
/**************************************************************************************
Global Temporary Procedure for comparing tables
Usage:
##comparetables 'table1','table2','column1,column2...'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##comparetables')
drop procedure ##comparetables
go
CREATE PROCEDURE ##comparetables
(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = ''
)
AS
-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all rows from either table that do NOT match
-- the other table in all columns specified, along with which table that
-- row is from.
declare @SQL varchar(8000);
IF @T2ColumnList = '' COLLATE DATABASE_DEFAULT SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' COLLATE DATABASE_DEFAULT + @table1 + ''' AS TableName, ' COLLATE DATABASE_DEFAULT + @T1ColumnList +
' FROM ' COLLATE DATABASE_DEFAULT + @table1 + ' UNION ALL SELECT ''' COLLATE DATABASE_DEFAULT + @table2 + ''' As TableName, ' COLLATE DATABASE_DEFAULT +
@T2ColumnList + ' FROM ' COLLATE DATABASE_DEFAULT + @table2
set @SQL = 'SELECT Max(TableName) as TableName, ' COLLATE DATABASE_DEFAULT + @T1ColumnList +
' FROM (' COLLATE DATABASE_DEFAULT + @SQL + ') A GROUP BY ' COLLATE DATABASE_DEFAULT + @T1ColumnList +
' HAVING COUNT(*) = 1' COLLATE DATABASE_DEFAULT
exec ( @SQL);
go
##comparetables 'PS_JOB','PS_EMPLOYMENT','EMPLID'
go
/**************************************************************************************
Global Temporary Procedure for comparing remote tables via open query
Usage:
##comptab 'table1','table2','column1,column2...'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##comptab')
drop procedure ##comptab
go
CREATE PROCEDURE ##comptab
(@table1 varchar(100),
@server Varchar(100),
@table2 Varchar(100),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = ''
)
AS
-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all rows from either table that do NOT match
-- the other table in all columns specified, along with which table that
-- row is from.
declare @SQL varchar(8000);
IF @T2ColumnList = '' COLLATE DATABASE_DEFAULT SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' COLLATE DATABASE_DEFAULT + @table1 + ''' AS TableName, ' COLLATE DATABASE_DEFAULT + @T1ColumnList +
' FROM ' COLLATE DATABASE_DEFAULT + @table1 + ' UNION ALL SELECT * FROM openquery(' COLLATE DATABASE_DEFAULT + @server + ',''SELECT ''''' COLLATE DATABASE_DEFAULT + @table2 + ''''' As TableName, ' COLLATE DATABASE_DEFAULT +
@T2ColumnList + ' FROM ' COLLATE DATABASE_DEFAULT + @table2 + ''')'
set @SQL = 'SELECT Max(TableName) as TableName, ' COLLATE DATABASE_DEFAULT + @T1ColumnList +
' FROM (' COLLATE DATABASE_DEFAULT + @SQL + ') A GROUP BY ' COLLATE DATABASE_DEFAULT + @T1ColumnList +
' HAVING COUNT(*) = 1' COLLATE DATABASE_DEFAULT
print @SQL
exec ( @SQL);
go
-- ##comptab 'PS_DEPT_TBL','"SERVER1"','"SERVER1".DEV.dbo.PS_DEPT_TBL','DEPTID'
-- go
select * from sys.servers
/**************************************************************************************
Global Temporary Procedure for checking if table has a particular field
Usage:
##hasField 'recname', 'Fieldname'
returns 1 if field there, otherwise 0
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##hasField')
drop procedure ##hasField
go
CREATE PROCEDURE ##hasField
(@table varchar(100)
,@fieldName varchar(100)
)
AS
declare @iFieldCount int
select @iFieldCount = count(*)
from PSRECFIELD
where
@table in (RECNAME,'PS_'+RECNAME)
and FIELDNAME=@fieldName
return @iFieldCount
go
declare @iField int
exec @iField = ##hasField 'PS_JOB','EFFDT'
print @iField
go
/**************************************************************************************
Global Temporary Procedure for getting keys of a PS RECORD as a comma sep list
Usage:
##getkey 'recname'
Function cannot be temporary, procedure can only return a number
drop procedure ##getkey
go
CREATE PROCEDURE ##getkey
(@table1 varchar(100)
)
returns varchar(1000)
AS
DECLARE @cKeyField varchar(100), @cKeyFieldList varchar(1000)
DECLARE cKey CURSOR FAST_FORWARD
FOR
select FIELDNAME
from PSKEYDEFN
where
INDEXID='_'
and RECNAME='ADDRESS_TYP_TBL'
order by KEYPOSN
@cKeyFieldList=''
OPEN cKey
FETCH NEXT FROM cKey
INTO @cKeyFieldList
WHILE @@FETCH_STATUS = 0
BEGIN
@cKeyFieldList = @cKeyFieldList + ',' + @cKeyField
PRINT @cKeyFieldList
FETCH NEXT FROM cKey
INTO @cKeyFieldList
END
CLOSE cKey
DEALLOCATE cKey
return @cKeyFieldList
go
***************************************************************************************/
/**************************************************************************************
Global Temporary Procedure for Absence Info Summary
Usage:
##abs '00007'
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##abs')
drop procedure ##abs
go
CREATE PROCEDURE ##abs
@in_emplid varchar(11)
AS
print 'PS_GP_ABS_EVENT'
select 'PS_GP_ABS_EVENT',* from PS_GP_ABS_EVENT where EMPLID=@in_emplid
order by 1,2,3,4,5,6
print 'PS_GPGB_ABS_EVT_JR'
select 'PS_GPGB_ABS_EVT_JR',* from PS_GPGB_ABS_EVT_JR where EMPLID=@in_emplid
order by 1,2,3,4,5,6
print 'PS_GPGB_ABS_CERT'
select 'PS_GPGB_ABS_CERT',* from PS_GPGB_ABS_CERT where EMPLID=@in_emplid
order by 1,2,3,4,5,6
-- print 'PS_GPGB_KIT_DTL'
-- select 'PS_GPGB_KIT_DTL',* from PS_GPGB_KIT_DTL where EMPLID=@in_emplid
-- order by 1,2,3
print 'PS_GPGB_ABS_CMTS'
select 'PS_GPGB_ABS_CMTS',* from PS_GPGB_ABS_CMTS where EMPLID=@in_emplid
order by 1,2,3,4,5,6
print 'PS_GPXPYE_MSG_VW'
select 'PS_GPXPYE_MSG_VW',* from PS_GPXPYE_MSG_VW where EMPLID=@in_emplid
order by 1,2,3
print 'PS_GP_ABS_SS_DAT'
select 'PS_GP_ABS_SS_DAT',* from PS_GP_ABS_SS_DAT where EMPLID=@in_emplid
order by 1,2,3
print 'PS_GP_ABS_EVT_FCST'
select 'PS_GP_ABS_EVT_FCST',* from PS_GP_ABS_EVT_FCST where EMPLID=@in_emplid
order by 1,2,3
print 'PS_GP_ABS_SS_STA'
select 'PS_GP_ABS_SS_STA',* from PS_GP_ABS_SS_STA where EMPLID=@in_emplid
order by 1,2,3
print 'PS_GP_ABSSS_V_XREF'
select 'PS_GP_ABSSS_V_XREF',* from PS_GP_ABSSS_V_XREF where EMPLID=@in_emplid
order by 1,2,3
go
##abs 00007
go
/**************************************************************************************
Global Temporary Procedure for Finding a Menu
Usage:
##findmenu Establishment
***************************************************************************************/
if exists (select * from tempdb..sysobjects where xtype='P' and name='##findmenu')
drop procedure ##findmenu
go
CREATE PROCEDURE ##findmenu
@in_menu varchar(300)
AS
declare @pct char(1)
set @pct = '%';
WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH1) AS (
SELECT P.PORTAL_NAME
, P.PORTAL_REFTYPE
, P.PORTAL_OBJNAME
, P.PORTAL_LABEL
, PORTAL_URI_SEG1
, PORTAL_URI_SEG2
, PORTAL_URI_SEG3
, CAST(P.PORTAL_LABEL COLLATE DATABASE_DEFAULT as VARCHAR(5000)) AS PATH1
FROM PSPRSMDEFN P
WHERE P.PORTAL_PRNTOBJNAME = ' ' COLLATE DATABASE_DEFAULT
UNION ALL
SELECT P_ONE.PORTAL_NAME
, P_ONE.PORTAL_REFTYPE
, P_ONE.PORTAL_OBJNAME
, P_ONE.PORTAL_LABEL
, P_ONE.PORTAL_URI_SEG1
, P_ONE.PORTAL_URI_SEG2
, P_ONE.PORTAL_URI_SEG3
, CAST(PATH1 + ' > ' COLLATE DATABASE_DEFAULT + P_ONE.PORTAL_LABEL as VARCHAR(5000)) AS PATH1
FROM PORTAL_REGISTRY P
INNER JOIN PSPRSMDEFN P_ONE
ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
AND P.PORTAL_REFTYPE = 'F' COLLATE DATABASE_DEFAULT
AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' COLLATE DATABASE_DEFAULT )
SELECT PORTAL_LABEL
, PORTAL_URI_SEG2
, PATH1
, PORTAL_OBJNAME
, PORTAL_REFTYPE
, PORTAL_NAME
FROM PORTAL_REGISTRY
WHERE PORTAL_REFTYPE != 'F' COLLATE DATABASE_DEFAULT
-- AND PORTAL_NAME = 'EMPLOYEE'
AND (
PORTAL_URI_SEG1 like @pct + @in_menu + @pct COLLATE DATABASE_DEFAULT
OR PORTAL_URI_SEG2 like @pct + @in_menu + @pct COLLATE DATABASE_DEFAULT
OR PORTAL_URI_SEG3 like @pct + @in_menu + @pct COLLATE DATABASE_DEFAULT
OR PORTAL_LABEL like @pct + @in_menu + @pct COLLATE DATABASE_DEFAULT
OR PORTAL_OBJNAME like @pct + @in_menu + @pct COLLATE DATABASE_DEFAULT
)
;
