Useful Functions

<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>