PeopleSoft Wiki

/* Create temp table of operids to ignore*/
drop table #oprid_exc

go

CREATE TABLE #oprid_exc (
operid_excludes varchar(60))

INSERT INTO #oprid_exc
(operid_excludes)
VALUES ('PPLSOFT')

INSERT INTO #oprid_exc
(operid_excludes)
VALUES ('DVP1')

INSERT INTO #oprid_exc
(operid_excludes)
VALUES ('FVP1')

INSERT INTO #oprid_exc
(operid_excludes)
VALUES ('VP1')

INSERT INTO #oprid_exc
(operid_excludes)
VALUES ('VP2')

INSERT INTO #oprid_exc
(operid_excludes)
VALUES ('PS')

/*Set value for CUSTOM objects (ignore these)*/
DECLARE @object_name as varchar(30);

set @object_name = 'OX%'

/*Start listing customised objects*/
/*--------------——*/
/* Application Engine Programs… */
SET NOCOUNT ON

select 'Application Engine Programs… '

SET NOCOUNT OFF

select AE_APPLID
, LASTUPDOPRID
, LASTUPDDTTM
From PSAEAPPLDEFN
Where AE_APPLID NOT LIKE @object_name
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Application Engine Sections… */
SET NOCOUNT ON

select 'Application Engine Sections… '

SET NOCOUNT OFF

select AE_APPLID
, AE_SECTION
, LASTUPDOPRID
, LASTUPDDTTM
From PSAESECTDEFN
Where AE_APPLID NOT LIKE 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Components…. */
SET NOCOUNT ON

select 'Components…. '

SET NOCOUNT OFF

select PNLGRPNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSPNLGRPDEFN
Where PNLGRPNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Component Interfaces…. */
SET NOCOUNT ON

select 'Component Interfaces…. '

SET NOCOUNT OFF

select BCNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSBCDEFN
Where BCNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Field objects…. */
SET NOCOUNT ON

select 'Field objects…. '

SET NOCOUNT OFF

select FIELDNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSDBFIELD
Where FIELDNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Filelayout definitions…. */
SET NOCOUNT ON

select 'Filelayout definitions…. '

SET NOCOUNT OFF

select FLDDEFNNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSFLDDEFN
Where (FLDDEFNNAME Not Like 'OX%'
And FLDDEFNNAME Not Like 'OPAL%')
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* HTML objects…. */
SET NOCOUNT ON

select 'HTML objects…. '

SET NOCOUNT OFF

select CONTNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSCONTDEFN
Where CONTNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And CONTTYPE = 4

/* Images…. */
SET NOCOUNT ON

select 'Images…. '

SET NOCOUNT OFF

select CONTNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSCONTDEFN
Where CONTNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And CONTTYPE = 1

/* Messages…. */
SET NOCOUNT ON

select 'Messages…. '

SET NOCOUNT OFF

select MSGNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSMSGDEFN
Where MSGNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Messages Channels…. */
SET NOCOUNT ON

select 'Messages Channels…. '

SET NOCOUNT OFF

select CHNLNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSCHNLDEFN
Where CHNLNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Message Nodes…. */
SET NOCOUNT ON

select 'Message Nodes…. '

SET NOCOUNT OFF

select MSGNODENAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSMSGNODEDEFN
Where MSGNODENAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Page objects…. */
SET NOCOUNT ON

select 'Page objects…. '

SET NOCOUNT OFF

select PNLNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSPNLDEFN
Where PNLNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Record PeopleCode…. */
SET NOCOUNT ON

select 'Record PeopleCode…. '

SET NOCOUNT OFF

select distinct OBJECTVALUE1
, OBJECTVALUE2
, OBJECTVALUE3
, LASTUPDOPRID
, LASTUPDDTTM
From PSPCMPROG
WHERE OBJECTVALUE1 Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And OBJECTID1 = 1

/* Page PeopleCode…. */
SET NOCOUNT ON

select 'Page PeopleCode…. '

SET NOCOUNT OFF

select OBJECTVALUE1
, OBJECTVALUE2
, LASTUPDOPRID
, LASTUPDDTTM
From PSPCMPROG
WHERE OBJECTVALUE1 Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And OBJECTID1 = 9

/* Component PeopleCode… includes Component, Component Record and Component record Field…. */
SET NOCOUNT ON

select 'Component PeopleCode… includes Component, Component Record and Component record Field…. '

SET NOCOUNT OFF

select OBJECTVALUE1
, OBJECTVALUE2
, OBJECTVALUE3
, OBJECTVALUE4
, OBJECTVALUE5
, LASTUPDOPRID
, LASTUPDDTTM
From PSPCMPROG
WHERE OBJECTVALUE1 Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And OBJECTID1 = 10

/* Application Engine PeopleCode…. */
SET NOCOUNT ON

select 'Application Engine PeopleCode…. '

SET NOCOUNT OFF

select OBJECTVALUE1
, OBJECTVALUE2
, LASTUPDOPRID
, LASTUPDDTTM
From PSPCMPROG
WHERE OBJECTVALUE1 Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And OBJECTID1 = 66

/* Component Interface PeopleCode…. */
SET NOCOUNT ON

select 'Component Interface PeopleCode…. '

SET NOCOUNT OFF

select OBJECTVALUE1
, OBJECTVALUE2
, LASTUPDOPRID
, LASTUPDDTTM
From PSPCMPROG
WHERE OBJECTVALUE1 Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And OBJECTID1 = 74

/* Message PeopleCode…. */
SET NOCOUNT ON

select 'Message PeopleCode…. '

SET NOCOUNT OFF

select OBJECTVALUE1
, OBJECTVALUE2
, LASTUPDOPRID
, LASTUPDDTTM
From PSPCMPROG
WHERE OBJECTVALUE1 Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And OBJECTID1 = 60

/* Record objects….. SQL Tables…. */
SET NOCOUNT ON

select 'Record objects….. SQL Tables…. '

SET NOCOUNT OFF

select RECNAME
, RECTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PSRECDEFN
Where RECNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And RECTYPE = 0

/* Record objects….. Views…. */
SET NOCOUNT ON

select 'Record objects….. Views…. '

SET NOCOUNT OFF

select RECNAME
, RECTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PSRECDEFN
Where RECNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And RECTYPE = 1

/* Record objects….. Dynamic Views…. */
SET NOCOUNT ON

select 'Record objects….. Dynamic Views…. '

SET NOCOUNT OFF

select RECNAME
, RECTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PSRECDEFN
Where RECNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And RECTYPE = 5

/* Record objects….. Derived/Work…. */
SET NOCOUNT ON

select 'Record objects….. Derived/Work…. '

SET NOCOUNT OFF

select RECNAME
, RECTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PSRECDEFN
Where RECNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And RECTYPE = 2

/* Record objects….. Sub-records…. */
SET NOCOUNT ON

select 'Record objects….. Sub-records…. '

SET NOCOUNT OFF

select RECNAME
, RECTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PSRECDEFN
Where RECNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And RECTYPE = 3

/* Record objects….. Query view…. */
SET NOCOUNT ON

select 'Record objects….. Query view…. '

SET NOCOUNT OFF

select RECNAME
, RECTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PSRECDEFN
Where RECNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And RECTYPE = 6

/* Record objects….. Temporary Tables…. */
SET NOCOUNT ON

select 'Record objects….. Temporary Tables…. '

SET NOCOUNT OFF

select RECNAME
, RECTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PSRECDEFN
Where RECNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And RECTYPE = 7

/* SQL objects standalone…. */
SET NOCOUNT ON

select 'SQL objects standalone…. '

SET NOCOUNT OFF

select SQLID
, LASTUPDOPRID
, LASTUPDDTTM
From PSSQLDEFN
Where SQLID Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And SQLTYPE = 0

/* SQL Application Engine…….. */
SET NOCOUNT ON

select 'SQL Application Engine…….. '

SET NOCOUNT OFF

select SQLID
, LASTUPDOPRID
, LASTUPDDTTM
From PSSQLDEFN
Where SQLID Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And SQLTYPE = 1

/* SQL view texts…….. */
SET NOCOUNT ON

select 'SQL view texts…….. '

SET NOCOUNT OFF

select SQLID
, LASTUPDOPRID
, LASTUPDDTTM
From PSSQLDEFN
Where SQLID Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And SQLTYPE = 2

/* Stylesheets… */
SET NOCOUNT ON

select 'Stylesheets… '

SET NOCOUNT OFF

select STYLESHEETNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSSTYLSHEETDEFN
Where STYLESHEETNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* */
/* PeopleTools objects modifiable outside Application Designer */
/* */
/* Job definitions…. */
SET NOCOUNT ON

select 'Job definitions…. '

SET NOCOUNT OFF

select PRCSJOBNAME
, DESCR
, LASTUPDOPRID
, LASTUPDDTTM
From PS_PRCSJOBDEFN
Where PRCSJOBNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Process definitions…. */
SET NOCOUNT ON

select 'Process definitions…. '

SET NOCOUNT OFF

select PRCSNAME
, PRCSTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PS_PRCSDEFN
Where PRCSNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Process Type definitions…. */
SET NOCOUNT ON

select 'Process Type definitions…. '

SET NOCOUNT OFF

select PRCSTYPE
, LASTUPDOPRID
, LASTUPDDTTM
From PS_PRCSTYPEDEFN
Where PRCSTYPE Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Queries…. */
/* Check against vanilla DEMO database*/
SET NOCOUNT ON

select 'Queries…. '

SET NOCOUNT OFF

select source.QRYNAME
, source.DESCR
, source.LASTUPDOPRID
, source.LASTUPDDTTM
From PSQRYDEFN source
Where source.QRYNAME Not Like 'OX%'
And source.LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And Exists (select 'x'
From FSDMO89.dbo.PSQRYDEFN demo
Where source.QRYNAME = demo.QRYNAME)

/* Server definitions…. */
SET NOCOUNT ON

select 'Server definitions…. '

SET NOCOUNT OFF

select SERVERNAME
, LASTUPDOPRID
, LASTUPDDTTM
From PS_SERVERDEFN

/* Translate values…. */
SET NOCOUNT ON

select 'Translate values…. '

SET NOCOUNT OFF

select FIELDNAME
, FIELDVALUE
, LASTUPDOPRID
, LASTUPDDTTM
From PSXLATITEM
Where FIELDNAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)

/* Tree structures…. */
SET NOCOUNT ON

select 'Tree structures…. '

SET NOCOUNT OFF

select TREE_STRCT_ID
, DESCR
, LASTUPDOPRID
, LASTUPDDTTM
From PSTREESTRCT source
Where TREE_STRCT_ID Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And Exists (select 'x'
From FSDMO89.dbo.PSTREESTRCT demo
Where source.TREE_STRCT_ID = demo.TREE_STRCT_ID)

/* Trees…. */
SET NOCOUNT ON

select 'Trees…. '

SET NOCOUNT OFF

select SETID
, TREE_NAME
, LASTUPDOPRID
, LASTUPDDTTM
From PSTREEDEFN source
Where TREE_NAME Not Like 'OX%'
And LASTUPDOPRID Not In (select operid_excludes
from #oprid_exc)
And Exists (select 'x'
From FSDMO89.dbo.PSTREEDEFN demo
Where source.TREE_NAME = demo.TREE_NAME)