Useful Functions
From TekiWiki
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 509: | Line 518: | ||
| - | ##comptab 'PS_DEPT_TBL','" | + | -- ##comptab 'PS_DEPT_TBL','"SERVER1"','"SERVER1".DEV.dbo.PS_DEPT_TBL','DEPTID' |
| - | go | + | -- go |
select * from sys.servers | select * from sys.servers | ||
| Line 630: | 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 705: | 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 | + | SELECT 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 | ||
