Useful Functions - Revision history

WikiSysop at 20:52, 27 May 2015

2015-05-27T20:52:03Z

← Older revision		Revision as of 20:52, 27 May 2015
Line 1:		Line 1:
		+	[[Microsoft SQL Server]] script useful for [[PeopleSoft]]:
	<pre>		<pre>
	/**************************************************************************************		/**************************************************************************************

WikiSysop at 21:55, 25 June 2014

2014-06-25T21:55:49Z

← Older revision		Revision as of 21:55, 25 June 2014
Line 39:		Line 39:

	select 'PS_JOB_JR',* from PS_JOB_JR where EMPLID=@in_emplid		select 'PS_JOB_JR',* from PS_JOB_JR where EMPLID=@in_emplid
-	order by 1,2,3,4	+	order by 1,2,3,4,5

	select 'PS_JOB',* from PS_JOB where EMPLID=@in_emplid		select 'PS_JOB',* from PS_JOB where EMPLID=@in_emplid
-	order by 1,2,3,4	+	order by 1,2,3,4,5

	select 'PS_COMPENSATION',* from PS_COMPENSATION where EMPLID=@in_emplid		select 'PS_COMPENSATION',* from PS_COMPENSATION where EMPLID=@in_emplid
-	order by 1,2,3,4	+	order by 1,2,3,4,5,6

	go		go

WikiSysop at 17:19, 24 June 2014

2014-06-24T17:19:34Z

← Older revision		Revision as of 17:19, 24 June 2014
Line 87:		Line 87:
	from PS_PERS_DATA_EFFDT		from PS_PERS_DATA_EFFDT
	where upper(EMPLID) like upper(@in_value)		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		select left('PS_NAMES (v8)' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME_TYPE,NAME
Line 516:		Line 517:


-	##comptab 'PS_DEPT_TBL','"~~SEVER1~~"','"SERVER1".DEV.dbo.PS_DEPT_TBL','DEPTID'	+	-- ##comptab 'PS_DEPT_TBL','"SERVER1"','"SERVER1".DEV.dbo.PS_DEPT_TBL','DEPTID'
-	go	+	-- go

	select * from sys.servers		select * from sys.servers
Line 712:		Line 713:
	AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME		AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
	WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' COLLATE DATABASE_DEFAULT )		WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' COLLATE DATABASE_DEFAULT )
-	SELECT ~~PORTAL_NAME~~	+	SELECT PORTAL_LABEL
	, PORTAL_URI_SEG2		, PORTAL_URI_SEG2
-	~~, PORTAL_LABEL~~
	, PATH1		, PATH1
	, PORTAL_OBJNAME		, PORTAL_OBJNAME
	, PORTAL_REFTYPE		, PORTAL_REFTYPE
		+	, PORTAL_NAME
	FROM PORTAL_REGISTRY		FROM PORTAL_REGISTRY
	WHERE PORTAL_REFTYPE != 'F' COLLATE DATABASE_DEFAULT		WHERE PORTAL_REFTYPE != 'F' COLLATE DATABASE_DEFAULT

WikiSysop at 21:35, 23 June 2014

2014-06-23T21:35:49Z

← Older revision		Revision as of 21:35, 23 June 2014
Line 713:		Line 713:
	WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' COLLATE DATABASE_DEFAULT )		WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' COLLATE DATABASE_DEFAULT )
	SELECT PORTAL_NAME		SELECT PORTAL_NAME
		+	, PORTAL_URI_SEG2
	, PORTAL_LABEL		, PORTAL_LABEL
	, PATH1		, PATH1

WikiSysop at 21:33, 23 June 2014

2014-06-23T21:33:43Z

← Older revision		Revision as of 21:33, 23 June 2014
Line 637:		Line 637:
	order by 1,2,3,4,5,6		order by 1,2,3,4,5,6

-	print 'PS_GPGB_KIT_DTL'	+	-- print 'PS_GPGB_KIT_DTL'
-	select 'PS_GPGB_KIT_DTL',* from PS_GPGB_KIT_DTL where EMPLID=@in_emplid	+	-- select 'PS_GPGB_KIT_DTL',* from PS_GPGB_KIT_DTL where EMPLID=@in_emplid
-	order by 1,2,3	+	-- order by 1,2,3

	print 'PS_GPGB_ABS_CMTS'		print 'PS_GPGB_ABS_CMTS'

WikiSysop at 07:03, 10 June 2014

2014-06-10T07:03:31Z

← Older revision		Revision as of 07:03, 10 June 2014
Line 76:		Line 76:
	upper(EMPLID) like upper(@in_value)		upper(EMPLID) like upper(@in_value)
	or upper(OPRID) 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		select left('PS_PERSONAL_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME
Line 81:		Line 82:
	where upper(EMPLID) like upper(@in_value)		where upper(EMPLID) like upper(@in_value)
	or upper(NAME) like upper(@pct + @in_value + @pct)		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		select left('PS_PERS_DATA_EFFDT' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,EFFDT
Line 90:		Line 92:
	where upper(EMPLID) like upper(@in_value)		where upper(EMPLID) like upper(@in_value)
	or upper(NAME) like upper(@pct + @in_value + @pct)		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		select left('PS_PERS_NID' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,COUNTRY,NATIONAL_ID_TYPE,NATIONAL_ID
Line 95:		Line 98:
	where upper(EMPLID) like upper(@in_value)		where upper(EMPLID) like upper(@in_value)
	or upper(NATIONAL_ID) like upper(@pct + @in_value + @pct)		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		select left('PS_HR_TBH_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,TBH_SEQ,RECNAME,FIELDNAME,TBH_CHAR
	from PS_HR_TBH_DATA		from PS_HR_TBH_DATA
	where upper(TBH_CHAR) like upper(@pct + @in_value + @pct)		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		select left('PSOPRDEFN' COLLATE DATABASE_DEFAULT + @pad,30) as Record,OPRID,EMPLID,OPRDEFNDESC
Line 106:		Line 111:
	or upper(OPRDEFNDESC) like upper(@pct + @in_value + @pct)		or upper(OPRDEFNDESC) like upper(@pct + @in_value + @pct)
	or upper(EMPLID) like upper(@pct + @in_value + @pct)		or upper(EMPLID) like upper(@pct + @in_value + @pct)
		+	order by 1,2,3,4

	go		go
Line 159:		Line 165:
	where		where
	upper(HR_SSTEXT_TEXT) like upper('%' COLLATE DATABASE_DEFAULT +@in_text+'%' COLLATE DATABASE_DEFAULT)		upper(HR_SSTEXT_TEXT) like upper('%' COLLATE DATABASE_DEFAULT +@in_text+'%' COLLATE DATABASE_DEFAULT)
		+	order by 1,2,3,4

	go		go

WikiSysop at 06:59, 4 June 2014

2014-06-04T06:59:41Z

← Older revision		Revision as of 06:59, 4 June 2014
Line 240:		Line 240:
	##mgr '0024'		##mgr '0024'
	***************************************************************************************/		***************************************************************************************/
-		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##mgr')
	drop procedure ##mgr		drop procedure ##mgr
	go		go
Line 314:		Line 314:
	##psquery 'Query'		##psquery 'Query'
	***************************************************************************************/		***************************************************************************************/
-		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##psquery')
	drop procedure ##psquery		drop procedure ##psquery
	go		go
Line 421:		Line 421:
	##comparetables 'table1','table2','column1,column2...'		##comparetables 'table1','table2','column1,column2...'
	***************************************************************************************/		***************************************************************************************/
-		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##comparetables')
	drop procedure ##comparetables		drop procedure ##comparetables
	go		go
Line 467:		Line 467:
	##comptab 'table1','table2','column1,column2...'		##comptab 'table1','table2','column1,column2...'
	***************************************************************************************/		***************************************************************************************/
-		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##comptab')
	drop procedure ##comptab		drop procedure ##comptab
	go		go
Line 523:		Line 523:
	returns 1 if field there, otherwise 0		returns 1 if field there, otherwise 0
	***************************************************************************************/		***************************************************************************************/
-		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##hasField')
	drop procedure ##hasField		drop procedure ##hasField
	go		go
Line 611:		Line 611:
	##abs '00007'		##abs '00007'
	***************************************************************************************/		***************************************************************************************/
		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##abs')
	drop procedure ##abs		drop procedure ##abs
	go		go
Line 668:		Line 669:
	##findmenu Establishment		##findmenu Establishment
	***************************************************************************************/		***************************************************************************************/
		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##findmenu')
	drop procedure ##findmenu		drop procedure ##findmenu
	go		go

WikiSysop at 18:17, 3 June 2014

2014-06-03T18:17:48Z

← Older revision		Revision as of 18:17, 3 June 2014
Line 10:		Line 10:
	##emp '0024'		##emp '0024'
	***************************************************************************************/		***************************************************************************************/
		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##emp')
	drop procedure ##emp		drop procedure ##emp
	go		go
Line 56:		Line 57:
	##findemp 'Ander'		##findemp 'Ander'
	***************************************************************************************/		***************************************************************************************/
		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##findemp')
	drop procedure ##findemp		drop procedure ##findemp
	go		go
Line 114:		Line 116:
	##jstar '0024'		##jstar '0024'
	***************************************************************************************/		***************************************************************************************/
		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##jstar')
	drop procedure ##jstar		drop procedure ##jstar
	go		go
Line 134:		Line 137:
	##msg 'text'		##msg 'text'
	***************************************************************************************/		***************************************************************************************/
		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##msg')
	drop procedure ##msg		drop procedure ##msg
	go		go
Line 170:		Line 174:
	##posstar '6000008'		##posstar '6000008'
	***************************************************************************************/		***************************************************************************************/
		+	if exists (select * from tempdb..sysobjects where xtype='P' and name='##posstar')
	drop procedure ##posstar		drop procedure ##posstar
	go		go
Line 191:		Line 196:
	where 321 is the TBH Sequence number (blank is latest)		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		drop procedure ##tbh
	go		go

WikiSysop: Created page with "

 /************************************************************************************** aaaaUsefulFunctions.sql - a useful set of global temporary procedures  for use with ..."

2014-02-25T21:47:36Z

Created page with "<pre> /************************************************************************************** aaaaUsefulFunctions.sql - a useful set of global temporary procedures for use with ..."

New page

<pre>
/**************************************************************************************
aaaaUsefulFunctions.sql - a useful set of global temporary procedures
for use with Psoft SQLServer
***************************************************************************************/

/**************************************************************************************
Global Temporary Procedure for Employee Info Summary
Usage:
##emp '0024'
***************************************************************************************/
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

select 'PS_JOB',* from PS_JOB where EMPLID=@in_emplid
order by 1,2,3,4

select 'PS_COMPENSATION',* from PS_COMPENSATION where EMPLID=@in_emplid
order by 1,2,3,4

go

##emp '0024'
go

/**************************************************************************************
Global Temporary Procedure for Finding Employee
Usage:
##findemp 'Ander'
***************************************************************************************/
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)

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)

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)

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)

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)

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)

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)

go

##findemp NX

/**************************************************************************************
Global Temporary Procedure for Employee Job Info Summary
Usage:
##jstar '0024'
***************************************************************************************/
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'
***************************************************************************************/
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 '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)

go

##msg Explanatory
go

##msg '14510,221'
go

/**************************************************************************************
Global Temporary Procedure for Employee Position Info Summary
Usage:
##posstar '6000008'
***************************************************************************************/
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)
***************************************************************************************/
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'
***************************************************************************************/

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'
***************************************************************************************/

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...'
***************************************************************************************/

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...'
***************************************************************************************/

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','"SEVER1"','"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
***************************************************************************************/

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'
***************************************************************************************/
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
***************************************************************************************/
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_NAME
, PORTAL_LABEL
, PATH1
, PORTAL_OBJNAME
, PORTAL_REFTYPE
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
)
;
</pre>