Useful Functions

From TekiWiki

Jump to: navigation, search

This whole wiki has now moved to http://www.springboardsolutions.com/wiki

Go to this page Useful Functions

 
Line 1: Line 1:
 +
[[Microsoft SQL Server]] script useful for [[PeopleSoft]]:
<pre>
<pre>
/**************************************************************************************
/**************************************************************************************
Line 39: Line 40:
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
Line 76: Line 77:
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 83:
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
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 90: Line 94:
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 100:
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 113:
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 167:
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
Line 240: Line 249:
               ##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 323:
               ##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 430:
               ##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 476:
               ##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 509: Line 518:
-
##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 523: Line 532:
               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 620:
               ##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 629: Line 639:
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'
Line 668: Line 678:
               ##findmenu Establishment
               ##findmenu Establishment
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##findmenu')
drop procedure ##findmenu
drop procedure ##findmenu
go
go
Line 703: Line 714:
   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_LABEL
+
     , PORTAL_URI_SEG2
     , 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

Latest revision as of 20:52, 27 May 2015

Retrieved from "http://www.homemarketeer.com/wiki/index.php?title=Useful_Functions"
Personal tools