Database

Alter Audit

The term alter audit in PeopleSoft refers to running an alter build script in application designer on a number of records (tables) to check if there are any differences between the PeopleSoft definitions and what exists in your database.

To perform a comprehensive alter audit of your current environment:

Start Application Designer

Create a new project, e.g. ALTER_AUDIT

Choose Insert > Definitions into Project (CTRL + F7)

In the insert into project search box, set your definition type to Records and the type to Table. Don't specify anything in the name field so you get all tables back. Press the enter key to perform the search. Start from the top and highlight all records from top to bottom. Then press insert to add them to your alter project (this can take some time). The number of record definitions found in the search //should// match the number of record definitions added to your project.

At this point, you have should have all PeopleSoft record definitions for database tables in your project. Save your project (this can take some time).

Choose Tools > Validate project. Make sure you say NO to the message about deleting invalid objects from your project. You should investigate any invalid records identified at this step, as it indicates problems with your PeopleSoft definitions. Once you know which definitions are invalid, you can do the validate project step again and this time say yes to the message about deleting invalid objects from your project. This will remove the invalid definitions from your project, which you do not want to attempt to build.

Choose Build > Project:

Check the Alter Tables box

Make sure the Build script file option is selected. You do not want to be making any changes to your database!

Click on the Settings button

Switch to the alter tab

Confirm the settings are:

Drop column if data present

Truncate data if field too short

Alter any - all check boxes selected

Alter by Table Rename

Click on build to create your alter script. Say yes to the warning about potentially data destructive settings are active. In this case you are just building a script, not actually running it against your database. The build script will take some time to generate.

Check your build log for any warnings. Then open your build script to see what is listed. If you find any cases where an alter on a table is shown in the SQL, then it indicates that there is a difference between the table structure in the PeopleSoft record definition and what is stored in the database.

Analyze Tables

You can use the ANALYZE TABLE command to store table statistics for Oracle (and many other DBMS) execution plans. A simple version of this script involves estimating statistics using the command:

analyze table 'TABLE_NAME' estimate statistics

Adding Sequence Numbers

To add a new sequence number based on existing key structures where the sequence number increments based on those keys the following query logic applies. This would be used when there is data already present and PeopleCode will not be triggered to set the sequence number.

How this query works is to group results based on keys and then find the count of rows with a rowid greater than or less than its rowid.

UPDATE RECORD_NAME T1

 SET T1.SEQNUM_FIELD= (

 SELECT Count(*)

 FROM RECORD_NAME T2

 WHERE

 T1.KEY_FIELD_1 = T2.KEY_FIELD_1 

 AND T1.KEY_FIELD_2 =T2. KEY_FIELD_2

 AND T2.ROWID >= T1.ROWID 

 GROUP BY T2.KEY_FIELD_1, T2.KEY_FIELD_2 

) 

Approximate Database Size

The following SQL is for an Oracle Database and it attempts to approximate the size of a PeopleSoft database using the user extents table. Basically, it looks at size of all tables and indexes (through table spaces) in the PeopleSoft database. I emphasize that this is just an approximate size and will be less than the true size.

Note, you will need to change the <OWNER> parameters to the owner of your PeopleSoft database (e.g. SYSADM). Each query takes a while to run, give them at least a few minutes.

This query gives you the total size of all tables:

select

 SUM(BYTES)/(1024*1024) TOTAL_SIZE_OF_TABLES_IN_MB

from 

 USER_EXTENTS UE

where 

 UE.SEGMENT_TYPE = 'TABLE'

 and UE.TABLESPACE_NAME in (

 select distinct TABLESPACE_NAME 

 from ALL_TABLES

 where OWNER = 'SYSADM'

 and TABLE_NAME like 'PS%'

 )

; 

While this query gives you the total size of all indexes:

select

 SUM(BYTES)/(1024*1024) TOTAL_SIZE_OF_INDEXS_IN_MB

from 

 USER_EXTENTS UE

where 

 UE.SEGMENT_TYPE = 'INDEX'

 and UE.TABLESPACE_NAME in (

 select distinct TABLESPACE_NAME

 from ALL_INDEXES

 where TABLE_OWNER = 'SYSADM'

 and TABLE_NAME like 'PS%'

 )

;

Add the two sizes together to get your approximate database size.Audit Records

If you are creating an audit record, there are normally three fields that need to go at the start of the audit record (remember to use the AUDIT_ prefix). These three fields will uniquely identify your audit data. They are (in the order they should be put on the audit record):

AUDIT_OPRID

AUDIT_STAMP

AUDIT_ACTN

The audit stamp field is a date/time field. However in order for it to be correctly populated, make sure that you set it to Auto-Update so that it is automatically set to the correct date/time stamp.

If you are using the same audit record for multiple records then add the field AUDIT_RECNAME. Ensure that this is system maintained and this will tell you which record the audit action relates to.Non Key-Preserved Table ORA-01779

You may get the following Oracle error when building a component with multiple scroll levels:

ORA-01779 cannot modify a column which maps to a non key-preserved table

What this is telling you is that PeopleSoft is trying to automatically write (insert/update) into a table that it should not be able to.

More than likely, this will be a view.Auto Generate a Select Statement

The following SQL will automatically generate a formatted select statement with all the fields of the record you specify. Note that this only works in Oracle:

select

 'select '

 || substr(SYS_CONNECT_BY_PATH(chr(13) || chr(9) || FIELDNAME, ','), 2)

 || chr(13)

 || 'from '

 || (case when substr(RECNAME, 1, 2) != 'PS' then 'PS_' || RECNAME else RECNAME end)

 || ';' as SELECT_STATEMENT

from

 (

 select

 RECNAME,

 FIELDNAME,

 FIELDNUM,

 count(*) OVER ( partition by RECNAME ) as FIELDCOUNT,

 ROW_NUMBER () OVER ( partition by RECNAME order by FIELDNUM) FIELDSEQUENCE

 from

 PSRECFIELDDB

 where

 RECNAME = '<RECNAME>')

where

 FIELDCOUNT=FIELDSEQUENCE

start with

 FIELDSEQUENCE = 1

connect by prior

 FIELDSEQUENCE+1=FIELDSEQUENCE

and prior

 RECNAME=RECNAME

order by FIELDNUM

;

Remember to replace <RECNAME> with the appropriate PeopleSoft record (e.g. PSUSEREMAIL. This script should correctly prefix your record name with PS_ where appropriate.

Note the use of the record RECFIELDDB which includes all sub-record files so this will work on records that use sub-records such as EMPLOYEES. Thanks to Jonathan Kearney for this advice.

If you use the example of PSUSEREMAIL the output of the query will look like this:

select

	EMAILID,

	EMAILTYPE,

	OPRID,

	PRIMARY_EMAIL

from PSUSEREMAIL;

Note that if you run this in a query tool such as SQLTools++ or TOAD, then you may only see the single word select in your query output - the rest of the text is there, it is just not visible because of the new line characters. Simply copy and paste the output to an editor window and it should display.

If you want to prefix your columns with an alias and give your record an alias use the following version and replace <RECNAME> and <ALIAS> with the appropriate values.

For example, PSUSEREMAIL as record and A as the alias.

select

 'select '

 || substr(SYS_CONNECT_BY_PATH(chr(13) || chr(9) || ALIAS || '.' || FIELDNAME, ','), 2)

 || chr(13)

 || 'from '

 || (case when substr(RECNAME, 1, 2) != 'PS' then 'PS_' || RECNAME else RECNAME end) 

 || ' ' || ALIAS

 || ';' as SELECT_STATEMENT

from

 (

 select

 RECNAME,

 '<ALIAS>' as ALIAS,

 FIELDNAME,

 FIELDNUM,

 count(*) OVER ( partition by RECNAME ) as FIELDCOUNT,

 ROW_NUMBER () OVER ( partition by RECNAME order by FIELDNUM) FIELDSEQUENCE

 from

 PSRECFIELD

 where

 RECNAME = '<RECNAME>')

where

 FIELDCOUNT=FIELDSEQUENCE

start with

 FIELDSEQUENCE = 1

connect by prior

 FIELDSEQUENCE+1=FIELDSEQUENCE

and prior

 RECNAME=RECNAME

order by FIELDNUM

;

Auto Numbering

There are a number of ID fields in PeopleSoft that are auto-numbered. A good example is the EMPLID (employee ID) field. The field EMPLID_LAST_EMPL stores the last system allocated EMPLID. This field can be found in the PS_INSTALLATION table. This means the next time a person (EMPLID) is created, they will get the value in in the EMPLID_LAST_EMPL field of the PS_INSTALLATION table + 1.

Getting the Next Number

Now you might think that if you wanted the next available ID for such a field, you would need to write some PeopleCode to increment the value by 1. However, this isn't necessary as there are two delivered functions to do this for you:

GetNextNumber

GetNextNumberWithGapsCommit

Note that the function GetNextNumberWithGaps is now deprecated.

What's the difference?

GetNextNumber doesn't auto-commit. This can lead to table locking issues if there are a lot of transactions requiring the sequence number field. GetNextNumberWithGapsCommit commits immediately after getting the next sequence number.

GetNextNumber can only increment by 1. GetNextNumberWithGapsCommit can increment by more than 1.

GetNextNumber can only be called in a SavePostChange event. GetNextNumberWithGapsCommit can be called form any PeopleCode event (according to PeopleBooks anyway ...).

GetNextNumberWithGapsCommit can use a SQL WHERE clause when updating multiple sequence numbers at the same time.

GetNextNumber examples:

GetNextNumber(PS_INSTALLATION.EMPLID_LAST_EMPL, 9999999999)

GetNextNumber("PS_INSTALLATION", "EMPLID_LAST_EMP", 9999999999)

The value 9999999999 is the maximum value of the EMPLID_LAST_EMPL field which has a length of 10.

GetNextNumberWithGapsCommit examples:

GetNextNumberWithGapsCommit(PS_INSTALLATION.EMPLID_LAST_EMPL, 9999999999, 1)

The extra parameter with a value of 1 means the increment value (in this case increment by 1).

Finding where sequence numbers are stored.

Try searching for fields that end in _LAST e.g. in App Designer search for fields with %_LAST.

The PS_INSTALLATION (and product related PS_INSTALLATION tables) store a lot of these fields

Breadcrumbs not updated from Pagelet Link

Sometimes, clicking the hyperlink in a pagelet to another component, takes you to the component but the breadcrumbs (in PeopleTools 8.5) are not updated. This occurs if the target component has the additional parameter, NAVSTACK=Clear. The NAVSTACK=Clear parameter is used for context sensitive return to links.

In addition to this, the NAVSTACK=Clear parameter has the undocumented feature of not updating breadcrumbs. To see which content references have this parameter, use the following SQL:

select * 

from PSPRSMDEFN 

where PORTAL_URLTEXT like '%NAVSTACK=Clear%';

Note that NAVSTACK=Clear is mainly used in PeopleSoft HRMS.Changing your Oracle Session Schema

Use the following code to alter your session's current schema:

ALTER SESSION SET current_schema = SYSADM

This will change the current schema to SYSADM when you are logged in as a different schema.Generate Oracle Public Synonyms

The following SQL can be used to generate a script to create public synonyms (in Oracle). This example uses the SYSADM user but it can be adapted to suit:

select

 'create public synonym ' || table_name || ' for SYSADM.' || table_name || ';'

from ALL_TABLES

where owner = 'SYSADM';

SQL Account Is Locked ORA-28000

If you ever get the following message:

SQL Access Manager

SQL error. Stmt #: 2 Error Position: 0 Return 2800 - ORA-28000: the account is locked.

This means that the system account used by PeopleSoft (typically SYSADM in Oracle) has been locked. If you (can) try to login to the environment using SYSADM through a SQL tool you would get the same error. This error occurred when I was attempting to do a compare report in application designer but could happen at any time.

Ask your DBA to unlock the account, as they will need to login with SYS access to an Oracle database.Custom Sorting with Decode

When trying to sort based on values that do not fit the standard ascending and descending sort logic provide by your database engine you can use the following decode() function to sort based on custom value ordering.

By ordering your value list in a decode function you can sort based on the integer values you assign to each value.

SELECT X.FIELDVALUE, X.XLATLONGNAME

FROM PSXLATITEM X

WHERE X.FIELDNAME = 'NOR_IMPRINT_TYPE' 

ORDER BY decode( X.FIELDVALUE, 'S', 1, 'N', 2, 'O', 3 )

The result is then:

FIELDVALUE

 XLATLONGNAME

 

S

 Standard

N

 Optional

O

 Optional, additional charge

Last Refresh of Environment

Sometimes you'll need to known when an environment was last refreshed and may not have the details of this. One way to approximate this using the PeopleTools tables is to look at the process request table.

Specifically you can use a query like this to check the first process that was run:

select * from PSPRCSRQST 

where DBNAME = '{your database}' 

order by RUNDTTM asc;

It may only be an approximation as it relies on at least one process running after the refresh (typically a system process) which may or may not have happened on the same day as the refresh. Even so it, gives you a timeframe to work with.Case When Exists SQL

The Case-When-Exists expression in Oracle is really handy. Here's an example of how to use it in a sub-select to return a status. This SQL checks for a match between the PS_PERSON and PSOPRDEFN records to determine the person status. The idea is that if the operator is not in PS_PERSON then they are not a true person in PeopleSoft.

Please be aware that this SQL will only work if:

You are using PeopleSoft HRMS/CS 8.9 or above

You are using an Oracle database

select

 O.OPRID,

 O.EMPLID,

 case when exists (

 select 1

 from PS_PERSON P

 where P.EMPLID = O.EMPLID

 ) then 'Person' else 'Not a Person' end as PERSON_STATUS

from PSOPRDEFN O;

Enumerate Rows in SQL

The following SQL is an example of how to enumerate (number) rows in SQL. This SQL will only work on Oracle databases.

select

 ROW_NUMBER () OVER (partition by ROLEUSER order by ROLENAME) as SEQNO,

 ROLEUSER,

 ROLENAME

from PSROLEUSER

order by ROLEUSER;

In this example, the roles for each user will be enumerated.

So for example, the results for the user PS would be something like this (roles 1, 2, 3 … n)

SEQNO

 ROLEUSER

 ROLENAME

 

1

 PS

 ADHOCUSR

 

2

 PS

 AM Administrator

 

3

 PS

 AppServer Administrator

 

n

 PS

 Rolename

 

This SQL is very useful if you need to insert rows with a sequence number or effective sequence number into a table.Conditional Counting In SQL

If you ever want to conditionally count the number of times a particular condition occurs in SQL, you can do it in Oracle using the case and count functions. Here's a simple example which counts the number of males/females stored in PS_PERSONAL_DATA.

select

 count(case when SEX = 'M' then 1 end) as MALES,

 count(case when SEX = 'F' then 1 end) as FEMALES

from PS_PERSONAL_DATA

All that is happening is that the case statement returns a 1 for every instance where the gender is M or F and the count, counts each returned value of 1 giving a summary like this:

MALES

 FEMALES

 

10004

 20421

 

The conditions in the case statement can be a lot more complex and can include sub-queries.Field Requires Unsupported Conversion

While performing an alter on a table, I received the following error in the SQL Build log (PSBUILD.LOG):

Error: PS_EXAMPLE_REC - Field EXAMPLE_FIELD requires an unsupported conversion. Record skipped. (76,29)

In this scenario, I had updated a field on a record after incorrectly making it a character field instead of a signed number. The conversion was from character (e.g. '1') to signed number (e.g. 1). However, the alter could not do this as indicated by the error.

Because this was a mistake in the field definition, I ended up dropping the field from the record definition, and performing the alter which dropped the column from the table. Then, I added the field back to the record definition and performed the alter which worked.

This works fine, if you have the luxury of dropping the data in the field ...Oracle Partition By

Partition By is a construct in Oracle that groups data in a select clause rather than by using group by

Here's a PeopleSoft example. This SQL partitions mail addresses by EMPLID, EFFDT, EFF_STATUS and ADDR_TYPE and returns the maximum dated row for each partition.

select *

from

(

 select EMPLID, EFFDT, EFF_STATUS, ADDR_TYPE,

 max(EFFDT) OVER (partition by EFF_STATUS, ADDR_TYPE) as MAX_DATE

 from PS_ADDRESSES

 where ADDR_TYPE = 'MAIL'

) B

where EFFDT = B.MAX_DATE

and EFF_STATUS = B.EFF_STATUS;

Paritioning data is really useful for grouping data prior to manipulation (as opposed to group by which does grouping after manipulation). This is because group by is for grouping results while partition by is actually partition by the data and returning the appropriate partitioned data as the results.Date End Field

In PeopleSoft the DATE_END field is defined as a date field. If the date 12/31/2009 is selected for this field it will show up like 2009-12-31 00:00:00. On 12/31/09 if you are comparing this field to SYSDATE via SQL your results might not return as expected since SYSDATE would return 2009-12-31 10:42:18 (i.e. the timestamp as well). To get around this you can use the trunc() function to to output trunc(SYSDATE) in a format that compares nicely with your data.

SELECT EOEP_RULE_ID, DATE_BEGIN, DATE_END 

FROM PS_EOEP_FORMULA 

WHERE DATE_BEGIN <= trunc(SYSDATE) AND DATE_END >= trunc(SYSDATE)

EOEP_RULE_ID

 DATE_BEGIN

 DATE_END

 

10001_05_EXTENDED

 2009-01-01 00:00:00

 2009-12-31 00:00:00

 

10001_05_EXTENDED

 2009-01-01 00:00:00

 2009-12-31 00:00:00

 

10001_05_EXTENDED

 2009-01-01 00:00:00

 2009-12-31 00:00:00

 

10001_05_EXTENDED

 2009-01-01 00:00:00

 2009-12-31 00:00:00

 

Kill an Oracle Database Session

If you are logged into the system with enough rights (e.g. SYSADM) you can kill database sessions that are not ACTIVE. To do so, first identify the appropriate session ID (sid) and serial number using this query:

SELECT s.sid,

 s.serial#,

 s.osuser,

 s.program,

 s.machine, 

 s.status,

 s.state

FROM v$session s

WHERE OSUSER = ':UserID'

AND STATUS <> 'ACTIVE';

Replace User ID with your OS username.

To kill the session use the following command:

alter session kill session 'sid,serial#' immediate;

Note that the IMMEDIATE keyword is only if you want the session to be killed instantly. That is you know it should be killed. You will need to enter the sid and the serial number to kill the session correctly.Oracle Database Links

A database link (dblink) links databases. It allows you to query against other databases using the syntax TABLENAME@DBLINK.

Use the following code to create a database link:

create database link "database_link_name"

connect to username

identified by "password"

using 'connection_name';

The values you need to plugin are:

database_link_name the name you want to give your database link and what you enter after the @ in TABLENAME@DBLINK

username the username to connect to connect to the database

password the password for the username to connect to the database

connect_name the TNS or full domain name to uniquely identify the database connection

 NOTE: You need to create a new connection to test the database link - it won't work in the same connection used to create the database link. If you get a TNS error, ensure that TNSNAMES.ORA is up to date on the database server you are creating the link on.

PL/SQL for Long Data Type Fields

On Oracle databases, you can't use standard SQL insert statements on fields that have a type of LONG. In order to work with data in tables with these types of fields, you'll need to use PL/SQL. Here's a very simple example of inserting into the message catalog from another database (database connection):

BEGIN

FOR ROW IN

 (SELECT MESSAGE_SET_NBR,

 MESSAGE_NBR,

 MESSAGE_TEXT,

 MSG_SEVERITY,

 LAST_UPDATE_DTTM,

 DESCRLONG

 FROM SYSADM.PSMSGCATDEFN@REMOTE_DB

 WHERE MESSAGE_SET_NBR = '12345'

 )

 LOOP

 INSERT INTO SYSADM.PSMSGCATDEFN

 VALUES ( 

 ROW.MESSAGE_SET_NBR,

 ROW.MESSAGE_NBR,

 ROW.MESSAGE_TEXT,

 ROW.MSG_SEVERITY,

 ROW.LAST_UPDATE_DTTM,

 ROW.DESCRLONG

 );

END LOOP;

END;

/

 NOTE: this isn't a problem with newer PeopleTools/Application versions where such fields have now changed to a data type of CLOB.

No Tablespaces

After completing an PeopleSoft installation, I found that I had missed a step as there were no table spaces available to save a new record definition into.

You can check this in application designer using the following steps:

Open a record definition

Tools > Data Administration > Set Table Space

Look at the available space Name-DB Name. If you have no options (blank) then no table spaces are defined.

Another way to check this is through:

PeopleTools > Utilities > Administration > Tablespace Utilities:

No tablespaces! Behind the scenes, the table that needs to be populated is PSTBLSPCCAT.

The step that I had missed as part of the installation was to run the SQR, setspace.sqr.

Start sqrw.exe (found under PS_HOME\bin\sqr\<DB>\binw\). Specify the appropriate parameters and run.

 NOTE: Replace PS_HOME above with the appropriate location for your PeopleSoft installation.

Unique Constraint Errors

Unique constraint errors are a part of life as a PeopleSoft developer. The most common way to troubleshoot them is to set up a SQL trace to determine the point of failure. Then its a matter of determining the bind variables that were passed to the insert statement and to figure out which key values are being duplicated ...

However, there's an alternative approach that works really well when you are dealing with a complex process. Once you've identified the table the error is occurring on, try dropping the unique index on that table (usually called PS_<TABLENAME>) and then letting the process insert the duplicate rows.

You can then use SQL to group by the key fields to find the duplicated rows causing the unique constraint error. This can often be a lot faster than trying to track down bind variables being passed to the insert statement at the relevant point in a large log file!Database Signon Error

While attempting to start the application server on an Oracle database, the following error message kept appearing in the application sever logs:

File: /vob/peopletools/src/pssys/stmget.cppSQL error. Stmt #: 2736 Error Position: 23 

Return: 942 - ORA-00942: table or view does not exist

Failed SQL stmt:SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1

PSAPPSRV.9554 (0) [05/26/11 09:48:10](1) GenMessageBox(200, 0, M): 

/vob/peopletools/src/pssys/stmget.cpp: A SQL error occurred. 

Please consult your system log for details.

PSAPPSRV.9554 (0) [05/26/11 09:48:10](1) GenMessageBox(0, 0, M): 

Database Signon: Could not sign on to database <DBNAME> with user <USERID>

PSAPPSRV.9554 (0) [05/26/11 09:48:10](0) Server failed to start

One of the things that can cause this is if the public synonym for PS.PSDBOWNER has not been created or granted public access. To fix, run the following in the database:

CREATE PUBLIC SYNONYM PSDBOWNER FOR PS.PSDBOWNER;

 

GRANT SELECT ON PS.PSDBOWNER TO PUBLIC;

 NOTE: also confirm that the database owner and name correctly matches the databases you are in, particularly if working with a refreshed environment.

Only One Primary Email Permitted

After upgrading to PeopleTools 8.51, the sign on page started displaying the following message:

Only one Primary Email Address is permitted. (48,9)

This message may also appear when you are trying to update a user profile. It is caused by users having more than one primary email address flagged in the PSUSEREMAIL table:

select OPRID, count(*)

from PSUSEREMAIL

where PRIMARY_EMAIL = 'Y'

group by OPRID

having count(PRIMARY_EMAIL) > 1;

The fix is to change the PRIMARY_EMAIL flag from Y for all but one email address for each User (OPRID).

One way is to set the primary email flag to N where an email address has not been specified (this may not always be an option).

update PSUSEREMAIL

set PRIMARY_EMAIL = 'N'

where PRIMARY_EMAIL = 'Y'

and EMAILID = ' '

and OPRID in (

 select OPRID

 from PSUSEREMAIL

 where PRIMARY_EMAIL = 'Y'

 group by OPRID

 having count(PRIMARY_EMAIL) > 1

);

Other fixes might including picking a specific email type (e.g. BUS) as the primary email and changing all other types so that the primary email flag is N.Writing Data Conversion Scripts

Here are some general guidelines around how to write a data conversion script:

Truncate target table(s). This is only necessary if you are inserting new data. It is always a good idea to err on the side of deleting and replacing the data. The last thing you want is data integrity issues. Note that it is better to do a truncate than a delete here.

Drop the index(es) if you are inserting data. This speeds up the insert as the database does not have to update all indexes as it is inserting rows into the table(s).

Perform the Insert or Update

Recreate the indexes. You should always drop and recreate indexes if you are making large changes to data. This will ensure that the database optimiser uses the appropriate indexes.

Update table statistics (table analysis). This ensures that the database has a good understanding of the contents of your table(s) after inserts and updates and uses the most efficient execution plan.

Select Random Users

When testing, a common requirement is to select from a list of random Users (OPRID) or IDs (EMPLID). The following SQL provides a way to do this using:

PSOPRDEFN for a list of users (OPRIDs)

PS_PERSON for a list of people (EMPLIDs)

 NOTE: that this SQL is database platform specific.

Oracle

Select 100 random users (OPRIDs):

select OPRID

from 

(

 select OPRID

 from PSOPRDEFN

 order by dbms_random.value

) 

where rownum <= 100;

Select 100 random people (EMPLIDs):

select EMPLID

from 

(

 select EMPLID

 from PS_PERSON

 order by dbms_random.value

) 

where rownum <= 100;

Change the rownum <= 100 line to adjust the number of rows returned.

SQL Server

Select 100 random users (OPRIDs):

select top 100 OPRID

from PSOPRDEFN

order by NEWID();

Select 100 random people (EMPLIDs):

select top 100 EMPLID

from PS_PERSON

order by NEWID();

Oracle Client

The Oracle client is required on your PC if you want to connect to the PeopleSoft database in two tier with applications such as application designer, data mover, and configuration manager.

This is where you can download the latest version of the Oracle client. Note that the Oracle client is backwards compatible so you can use a version that is newer than the version of the Oracle database you are connecting to. So for example, you can use the 11g client and connect to a 10g database. There a number of versions of the Oracle client, the one I generally tend to install is the runtime client, although all you really need is the Instant Client provided you install it correctly.

Remember that you PeopleSoft applications are 32 bit applications, so while you can install a 64 bit client on your PC, PeopleSoft will not work. If you do this, you'll get an error like this:

Missing or invalid version of SQL ibrary PSORA (200, 0)

This is what happened when I tried to run application designer with an Oracle 64 bit client installed by mistake.Data Archive Candidates

You can use the following SQL in Oracle to identify the top 100 tables that may be good candidates for archiving. Replace <OWNER> with the owner of the PeopleSoft schema (e.g. SYSADM). The field NUM_ROWS tells you the number of rows in the table (from when it was last analysed). The query also provides the approximate size of the table in MB.

Note this query can take quite a while to run - give it at least 1-2 minutes. For a quicker version, remove the sub-query that returns the approximate size of each table.

select

 OWNER,

 TABLE_NAME,

 NUM_ROWS,

 TABLESPACE_NAME,

 STATUS,

 LOGGING,

 LAST_ANALYZED,

 (

 select ltrim(to_char((sum(BYTES)/(1024*1024)), '9G999D99'), ' ') || ' MB'

 from USER_EXTENTS

 where SEGMENT_TYPE = 'TABLE'

 and SEGMENT_NAME = ARCHIVE_CANDIDATES.TABLE_NAME

 ) APPROX_SIZE

from (

 select

 OWNER,

 TABLE_NAME,

 NUM_ROWS,

 TABLESPACE_NAME,

 STATUS,

 LOGGING, 

 LAST_ANALYZED

 from 

 ALL_TABLES A

 where 

 OWNER = '<OWNER>' 

 and TABLE_NAME like 'PS%'

 and NUM_ROWS is not null 

 order by NUM_ROWS desc

) ARCHIVE_CANDIDATES 

where

 ROWNUM <= 100

;

Returning SQL Result as Boolean

If you return either a true or false string as the result of executing SQL through PeopleCode, it can be stored directly into a PeopleCode boolean variable.

To explain what I mean, here's a simple example. Say you to want check if a user has a role (for example, the PeopleSoft Administrator role). You could create the following SQL definition called HAS_ROLE to give you a true or false result:

SELECT CASE WHEN EXISTS ( 

 SELECT 1 

 FROM PSROLEUSER 

 WHERE ROLEUSER = :1

 AND ROLENAME = :2) THEN 'true' ELSE 'false' END AS HAS_ROLE

 FROM DUAL

 NOTE: this is an Oracle example using case when exists syntax but you can do something similar in other database platforms.

You can execute this in a SQLExec statement and store the result in a boolean variable:

Local boolean &bHasRole;

SQLExec(SQL.HAS_ROLE, "<OPRID>", "<ROLE>", &bHasRole);

Another example is to show or hide a field. You can directly pass the result of the HAS_ROLE SQL to the visible property of a field like this:

SQLExec(SQL.HAS_ROLE, "<OPRID>", "<ROLE>", RECORD.FIELD.Visible);

If the result is true, the field is visible, if it is false, the field is invisible. All in one nifty piece of code. You could do something similar with other boolean (true/false) properties such as Enabled or DisplayOnly.Oracle Data Dictionary

There's a view of all the data dictionary views in oracle called DICTIONARY. So you can query it like so:

select 

 TABLE_NAME,

 COMMENTS 

from 

 DICTIONARY 

order by 

 TABLE_NAME;

This includes comments about most of the views which is really handly. Almost all the views use plurals (ALL_TABLES not ALL_TABLE)

To find all the tables associated with a schema, query ALL_TABLES and specify the appropriate OWNER e.g. SYSADM.Oracle Dynamic Performance Views

Oracle dynamic performance views (v$) are built on the virtual tables ($x). The views (v$) are much more useful than the virtual tables ($x). To get a listing of all them use the following query:

select

 NAME

from 

 V$FIXED_TABLE

where 

 TYPE = 'VIEW';

Some of the most useful views include:

v$database Details about the nature and state of the database

v$transaction What transactions are currently uncommitted

v$session What users have logged on

v$controlfile Names and locations of all control files

v$logfile Names and locations of all online redo logs

v$datafile Names and locations of all data files

v$instance Details about the nature and state of the instance

v$sqltext What queries are users issuing

v$process What background and server processes are running

Global dynamic performance views have the prefix gv$. They are viewing across all instances when using a real application cluster (RAC) configuration.SQL*Plus

If you've ever worked on Oracle databases then you've probably heard of, and stay well away from SQL*Plus - unless you happen to be a DBA!

I totally understand your position. As a general querying tool, SQL*Plus is less than ideal For such work you should be using something like SQLTools++ or TOAD.

However for running SQL updates, inserts, and PL-SQL scripts, SQL*\Plus should be your tool of choice.

Here's a few reasons why:

Everyone should have SQL*Plus (as part of the Oracle client)

SQL*Plus options are set in a script, not somewhere in an IDE. So its more likely that your script will be run with consistent options.

SQL*Plus is less forgiving. While this is often a source of frustration, it does mean you're not writing scripts where your IDE assumes certain things.

SQL*Plus can be run from the command line, and it as far less resource intensive than any IDE, especially products like TOAD!

SQL*Plus can spool a log file for tracking what your script did. While you can do this in other IDEs, this is something you write directly into your script.

There is one exception to this rule - PeopleSoft Data Mover. Data Mover scripts are a bit inflexible and lack logging capabilities. However the one thing that datamover does extremely well is exporting data to flat files (DAT files) and importing it back in. If you ever need to write a script where you first want to take a backup of the data in one or more tables or you want to import data from a flat file from somewhere else - then Data mover should be your tool of choice. Data mover is also the best choice if you need to support more than one database platform.

When you run any script, you should generate a log file to indicate what is happening. This is where things like spooling output to a file come into play.

To generate a basic log file, all you need to do is:

Spool to a specified log file at the start of your script

Add any custom messages through the PROMPT command

Turn off spooling at the end of your script

Here's a simple example of a sqlplus script that spools output to a file and indicates what is happening.

SPOOL Example.log

PROMPT Script Starting

PROMPT Creating Example.log in the current directory

 

PROMPT Truncating PS_EXAMPLE_TBL

TRUNCATE TABLE PS_EXAMPLE_TBL;

 

PROMPT Inserting data into PS_EXAMPLE_TBL

INSERT INTO PS_EXAMPLE_TBL VALUES("TEST", 1);

 

COMMIT;

 

PROMPT Script Complete

SPOOL OFF

Looping in SQL

The following SQL is an example of looping through user data and presenting it as a comma separated list of roles for each user.

This SQL will only work on Oracle databases.

select

 ROLEUSER as USER_ID,

 substr(SYS_CONNECT_BY_PATH(ROLENAME, ','),2) as ROLES

from

 (

 select

 ROLEUSER,

 ROLENAME,

 count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,

 ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE

 from

 PSROLEUSER

 where

 ROLEUSER is not null)

where

 ROLECOUNT=ROLESEQUENCE

start with

 ROLESEQUENCE = 1

connect by prior

 ROLESEQUENCE+1=ROLESEQUENCE

and prior

 ROLEUSER=ROLEUSER

;

How does it work? Well here's a break down.

The query inside the from clause essentially groups the data for looping.

select

 ROLEUSER,

 ROLENAME,

 count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,

 ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE

from

 PSROLEUSER

where

 ROLEUSER is not null

It returns the user (ROLEUSER), the name of the role (ROLENAME), a count of the number of roles for that user (ROLECOUNT) and a sequence number for each role row (ROLESEQUENCE).

So for example if the user PS has the roles:

PeopleSoft Administrator

PeopleTools

Portal Administrator

ProcessSchedulerAdmin

Security Administrator

The result would look like this:

ROLEUSER

 ROLENAME

 ROLECOUNT

 ROLESEQUENCE

 

PS

 PeopleSoft Administrator

 5

 1

 

PS

 PeopleTools

 5

 2

 

PS

 Portal Administrator

 5

 3

 

PS

 ProcessSchedulerAdmin

 5

 4

 

PS

 Security Administrator

 5

 5

 

This essentially sets up the loop - PS has 5 roles and each of them has been placed in sequence using the Oracle system ROW_NUMBER partitioned over the role user and ordered by the role name.

The outside part of the query then uses the SYS_CONNECT_BY_PAT starting with the ROLECOUNT equal to the ROLESEQUENCE which starts at 1 and is incremented by 1 each time. To ensure that we are looping for the same user each time, the SYS_CONNECT_BY_PATH uses the line prior ROLEUSER=ROLEUSER. So when we move to the next user, the loop starts again with a ROLESEQUENCE of 1.

You don't have to fully understand the syntax, but what this SQL demonstrates is how to create a simple loop in Oracle using plain old SQL.No Matching Buffer Errors

A no matching buffer error occurs when there is a mismatch in the data within the component buffer scroll hierarchy (scroll 0, scroll 1, scroll 2, scroll 3).

It occurs due to one or more orphaned data rows in the lower scroll levels that do not link back to the higher scroll levels through keys. The component buffer is where PeopleSoft enforces its own referential integrity. This means that while there may not be any relationships between tables defined at the database level, when records are loaded into a component, they need to maintain a key based relationship according to the scroll level structure in that buffer.

The classic message is something like this:

No matching buffer found for level. (15,26)

Typically no matching buffer errors are caused by processes that delete or update rows from parent records without updating or removing rows from the appropriate child record within the component scroll level structure. If the component buffer can load a row from a lower (child/grand-child) scroll that doesn't match on keys to the scroll levels above it, the error will occur.

There are two fundamental ways to fix a no matching buffer error:

Put the missing parent-record row back (or change the keys back)

Remove the orphaned row from the child record

 NOTE: no matching buffer errors are particularly common on effective dated and sequenced records.

A SQL trace is a good place to start with investigating buffer errors. The point at which a rollback happens in the trace is typically where the no matching buffer error has occurred. This is usually at the point when the orphaned row in the child record was loaded.

Please see this page about querying the structure of a component which provides a great way to disect a component for troubleshooting no matching bfufer errors.

After you've identified the culprit child record, the next step is to examine the component scroll structure and find candidate records in the scroll levels above to find the cause of the issue. So if the child record is at scroll level 2, look at records at scroll level 1, and 0. Remember this error is key-based so you only need to be concerned with records that have one or more of keys in the child record.

The error is not necessarily with data from the record you selected the — the error may be on any other child record being loaded in the component that shares the same keys. So don't make any assumptions about the culprit record. You may need to systematically check tables loaded by the component.

Sometimes, opening the component with include history (all effective dates) will prevent the no matching buffer error. This is a sign that the error is a result of an effective date or effective sequence mismatch that is causing the problem.Structure of a Component

The following queries give you the structure of a component.

This query gives you the pages, occurs level, record and field name for a given component:

select distinct

 C.PNLGRPNAME,

 P.PNLNAME,

 P.OCCURSLEVEL,

 R.RECTYPE,

 R.RECNAME,

 P.FIELDNAME

from

 PSPNLGROUP C inner join PSPNLFIELD P

 on C.PNLNAME = P.PNLNAME

 inner join PSRECDEFN R

 on R.RECNAME = P.RECNAME

where

 C.PNLGRPNAME = 'YOUR_COMPONENT_NAME'

order by P.OCCURSLEVEL, R.RECNAME asc;

This query gives a summary of the records, what occurs level they are on, and what type of record they are (record type):

select distinct

 P.OCCURSLEVEL,

 R.RECTYPE,

 R.RECNAME, 

 R.RECDESCR

from

 PSPNLGROUP C inner join PSPNLFIELD P

 on C.PNLNAME = P.PNLNAME

 inner join PSRECDEFN R

 on R.RECNAME = P.RECNAME 

where

 C.PNLGRPNAME = 'YOUR_COMPONENT_NAME'

order by P.OCCURSLEVEL, R.RECNAME asc;

 NOTE: A RECTYPE value of 0 indicates a database table.

Getting the Current Environment

For security and consistency you should wrap code that is environment specific around logic that determines the environment you are in.

You should apply this to anything you wouldn't want to run in a development environment the same way as a production environment. Some common examples include interfaces to 3rd party systems, sending out notifications (email) and dangerous database operations.

There are a number of ways to work out which environment you are in, and these vary in success depending on your environment and configuration. This article covers 4 options:

Use the Environment Settings in PSOPTIONS.

Use the distribution node in PS_SERVERDEFN

Look at the database name field in the process request table - PSPRCSRQST.DBNAME

Use the %DbName PeopleCode system variable

Query the database meta-tables.

Option 1: Use the Environment Settings in PSOPTIONS.

This is configured under:

PeopleTools > Utilities > Administration > PeopleTools Options

You can set:

An environment long name (a description for your environment, e.g. Development Master)

An environment short name (typically your 8 character database name)

A system type (e.g. Demo, Development, Production, Sandbox, System Test, Training etc)

As long as this information is maintained and kept to date between refreshes then this is certainly the best way to store information about your database. However, the issue with this approach is it relies on consistency and setup to work. It can fail for example if there is a refresh and PSOPTIONS doesn't get updated, so you think you are in production when in fact you are in test or development.

Option 2: Use the distribution node in PS_SERVERDEFN

Another option is to use the process scheduler server distribution settings. However this assumes that your report node definition name matches the database name, which may not always be the case. So this is not a very reliable way of getting the database name and one of the other options is a far better choice.

 Note that report nodes are stored in the system table PS_CDM_DIST_NODE.

Option 3: Look at the database name field in the process request table - PSPRCSRQST.DBNAME

This is the database name passed through the process run parameters. If you have a look under:

PeopleTools > Process Scheduler > Process Types

For various process types that you can run through process scheduler (e.g. Application Engine, COBOL and SQR), this is usually the process scheduler variable %%DBNAME%%.

Option 4: Use the %DbName PeopleCode system variable

Very simple - here's a message box example:

MessageBox(0, "", 0, 0, "Database Name = " | %DbName);

This has the advantage of just "working" in PeopleCode without query the database.

Option 5: Query the database meta-tables.

Oracle: select name from v$database;

Oracle: select ora_database_name from dual for the fully qualified database name

Microsoft SQL Server: select db_name()

Of course this one is database platform specific.

Which option is best?

As usual, it depends here are the caveats:

Option 1 can provide the most information provided that database information is maintained when new environments are created and refreshed. I like the fact that you can also store the database type. However, it has the issue of having to be maintained and updated which can mean errors with the data in the table.

Option 2 is unreliable as it depends on the report node having the same name as the database. I've included it to show an option you should avoid as there are better ways to get the current environment.

Options 3 and 4 use the internal database name. Option 3 should work after a refresh provided the environment is using the correct process schedulers. This appears to be same for Option 4 however you might want to verify this for yourself.

Option 5 is quite reliable as it just queries the database itself but is database platform specific

Search Message Catalog Explain Text

The following PL/SQL is my attempt to search the message catalog for a particular piece of text stated as a regular expression. It is designed for use in Oracle 10g+ databases as it makes use of Oracle's regular expressions functionality.

There are some limitations with it - in particular, the conversion from LONG to VARCHAR2 is limited to a size of 32,000 characters, but you can have more than that many characters in a message catalog entry, and if you hit such an entry, the PL/SQL will fail.

To use the PL/SQL you will need to do two things:

Specify the message set number you want to search

Specify your search string as a regular expression for the Oracle REGEXP_INSTR function.

 NOTE: you can modify the cursor SEARCH_MESSAGE_CATALOG SQL to suite your needs — whether you want to search for a specific message set, a range of message sets or the entire message catalog.

/*

 Search Message Catalog for a Text String 

 http://www.peoplesoftwiki.com/books/database/search-message-catalog-explain-text

 Please run this in either sql*plus or SQLTools++.

*/

SET SERVEROUTPUT ON

SET PAGESIZE 500;

SET LINESIZE 500;

 

DECLARE

 

 vMSG_SET PSMSGCATDEFN.MESSAGE_SET_NBR%Type;

 vMSG_NBR PSMSGCATDEFN.MESSAGE_NBR%Type;

 vMSG_TEXT PSMSGCATDEFN.MESSAGE_TEXT%Type;

 vMSG_SEVERITY PSMSGCATDEFN.MSG_SEVERITY%Type;

 vMSG_EXPLAIN varchar2(32000);

 vMSG_CONTEXT varchar2(32000);

 nPOSITION number(10);

 vSEARCH_REGEX varchar2(100);

 

 CURSOR SEARCH_MESSAGE_CATALOG IS

 SELECT

 MESSAGE_SET_NBR,

 MESSAGE_NBR,

 MESSAGE_TEXT,

 MSG_SEVERITY,

 DESCRLONG

 FROM PSMSGCATDEFN

 WHERE MESSAGE_SET_NBR = 99999 -- Specify message set

 ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;

 

BEGIN

 

 vSEARCH_REGEX := '\sTest\s'; -- Specify your regular expression

 

 OPEN SEARCH_MESSAGE_CATALOG;

 

 LOOP

 

 FETCH SEARCH_MESSAGE_CATALOG

 INTO vMSG_SET, vMSG_NBR, vMSG_TEXT, vMSG_SEVERITY, vMSG_EXPLAIN;

 

 EXIT WHEN SEARCH_MESSAGE_CATALOG%NOTFOUND;

 

 nPOSITION := REGEXP_INSTR(vMSG_EXPLAIN, vSEARCH_REGEX);

 

 IF nPOSITION > 0 THEN

 

 /* Show the "text" around where the search string was found */

 vMSG_CONTEXT := SUBSTR(vMSG_EXPLAIN, (nPOSITION - 25), 50);

 vMSG_CONTEXT := REPLACE(vMSG_CONTEXT, chr(10), ' ');

 vMSG_CONTEXT := REPLACE(vMSG_CONTEXT, chr(13), ' ');

 

 DBMS_OUTPUT.ENABLE(1000000);

 DBMS_OUTPUT.PUT_LINE(chr(13));

 DBMS_OUTPUT.PUT_LINE('Message Set : ' || vMSG_SET);

 DBMS_OUTPUT.PUT_LINE('Message Nbr : ' || vMSG_NBR);

 DBMS_OUTPUT.PUT_LINE('Message Text : ' || vMSG_TEXT);

 DBMS_OUTPUT.PUT_LINE('Message Severity : ' || vMSG_SEVERITY);

 DBMS_OUTPUT.PUT_LINE('Match Position : ' || nPOSITION);

 DBMS_OUTPUT.PUT_LINE('Message Context : ... ' || vMSG_CONTEXT || ' ...');

 DBMS_OUTPUT.PUT_LINE(chr(13));

 

 END IF;

 

 END LOOP;

 

 DBMS_OUTPUT.PUT_LINE(chr(13));

 DBMS_OUTPUT.PUT_LINE('Search Complete.');

 

 CLOSE SEARCH_MESSAGE_CATALOG;

 

END;

/

Structure of a Rowset-Based Message

The SQL below will return the structure of your message if it is rowset based (uses PeopleSoft record definitions to define the structure).

select RF.FIELDNAME

from PSRECFIELD RF

where

 RF.RECNAME = 'MESSAGE_RECNAME'

 and not exists (

 select 1

 from PSMSGFLDOVR MFO

 where MFO.MSGNAME = 'MSGNAME'

 and MFO.APMSGVER = 'VERSION_N'

 and MFO.RECNAME = RF.RECNAME

 and MFO.FIELDNAME = RF.FIELDNAME

 and MFO.DONOTPUBLISH = 1

 )

order by RF.FIELDNUM;

Formatting SQL in Notepad++

Suppose you have the following text in your editor:

select FIELD1, FIELD2, FIELD3 from TABLE1;

And you want this formatted to look like this:

select 

 FIELD1,

 FIELD2,

 FIELD3

from TABLE1;

How would you do it?

Well here's an example using search-replace and regular expressions in Notepad++ though you can easily do this in any other good text editor.

There are three steps involved:

Replace the select and white space with a select, new line and tab.

Replace the commas and white space with a comma, new line and tab.

Replace the white space and from with a new line

Navigate to:

Search > Replace (or CTRL+H)

Set the Search Mode to Regular expression (see screenshots below)

Replace the select and white space with select, new line and tab:

Find what: select (don't forget the whitespace at the end)

Replace with: select \n\t (newline & tab)

This gives you:

select 

 FIELD1, FIELD2, FIELD3 from TABLE1;

Replace the commas and white space with a comma, new line and tab:

Find what: , (don't forget the whitespace at the end!)

Replace with: ,\n\t

This gives you:

select 

 FIELD1,

 FIELD2,

 FIELD3 from TABLE1;

Replace the white space and from with a new line and from:

Find what: from (don't forget the whitespace at the start!)

Replace with: \nfrom

This gives you the desired result:

select 

 FIELD1,

 FIELD2,

 FIELD3

from TABLE1;

Also, don't forget to check out the article on auto generating a select statement.PeopleTools Tables

PeopleTools tables are the tables in your PeopleSoft environment that store PeopleTools meta-data - data about the underlying development platform for your environment. So this includes things like fields, records, pages, components, menus, and portal structures.

Access to these tables is available in every PeopleTools installation through application designer. But how do you find out which table is used for what?

This comes down to a bit of reverse engineering ..

Naming convention for PeopleTools tables

The majority of PeopleTools tables start with PS and do not have an underscore between PS and the table name (e.g. PSRECNAME). You also don't need to prefix the table name with PS in this case as the record definition and table names match.

To see what I mean, open up PSRECDEFN in application designer and have a look at the Non-Standard SQL Table Name field on the Record Type tab:

Ordinary application tables such as the INSTALLATION table do not have a non-standard SQL name set which is why you need to prefix PS_ to them when you query them out of the database. There are some strange exceptions to this, notably some of the process scheduler tables such as the process definition table, PRCSDEFN which has the a non-standard SQL name of PS_PRCSDEFN.

Incidentally, if you want to query which table record definitions have non-standard SQL table names, use the SQLTABLENAME field in PSRECDEFN like so:

select RECNAME, RECDESCR, SQLTABLENAME

from PSRECDEFN

where SQLTABLENAME <> ' '

and RECTYPE = '0'

order by RECNAME;

 NOTE: not every PeopleTools record has a description just to keep you guessing ;)

Identifying PeopleTools tables through Data mover Scripts

On your PeopleTools file server, if you have access to %PS_HOME%\scripts you should be able to find the data mover (.dms) scripts delivered with the PeopleSoft installation.

The DMS script mvprdexp.dms contains a full list of PeopleTools tables and pt_release_export.dms lists the core PeopleTools tables. Both scripts group PeopleTools tables by type. For example, PeopleTools tables for records & indexes, fields and translates, field formats, pages, menus, components etc. Very handy information. If you can get a copy of this file, keep it close at as a reference.

Identifying PeopleTools tables through PeopleSoft Query

If you run the PeopleSoft Query application (not through the PIA) either using Go > Query in application designer or starting the executable, psqed.exe, you can get it to show you access groups.

To do this, choose:

View > Preferences > Component View tab and select the Show Access Groups tab

Your database window where you used to select record definitions should now be grouped. Scroll down and find the PeopleTools access group PT_ACCESS_GROUP and expand the folder.

This should now show you the PeopleTools tables by grouping (e.g. XMLPUBLISHER for XML Publisher, RECORD_DEFINITION for Record Definition etc).

 NOTE: it does not appear that you can do this through Query Manager in the PIA.

Resources on the Internet

PTRef by GoFaster which also has schema information is the best resource out there.

You can also get the utlity from Github to extract this information yourself. Thanks David Kurtz !Reusing Views

PeopleSoft has thousands of views! If you want to know just how many there are, try this query to give you the distinct views (RECTYPE = 1) from the PeopleTools record definition table, PSRECDEFN.

select count(distinct RECNAME)

from PSRECDEFN

where RECTYPE = '1';

If you want to know who created these views, use the following query. Note that a LASTUPDOPRID of PPLSOFT means that the view was delivered by PeopleSoft.

select LASTUPDOPRID, count(distinct RECNAME)

from PSRECDEFN

where RECTYPE = '1'

group by LASTUPDOPRID

order by count(distinct RECNAME) desc;

With that many views out there, there's a (good) chance you'll stumble on one that serves your needs. This saves you creating a new customisation, and reduces the development and testing effort as well.

Generally, in order to reuse a view, you need to find views that have the fields you are after. The following SQL does this by matching on up to three fields.This doesn't mean that the views returned are ideal - you'll still need to check the view SQL yourself, it just helps to locate them faster.

Replace the % with the fields you want to match on. If you have less than three fields, leave the other matches as %.

select

 RD.RECNAME,

 RD.RECDESCR,

 RD.FIELDCOUNT,

 RD.LASTUPDDTTM,

 RD.LASTUPDOPRID,

 STD.SQLTEXT

from

 PSRECDEFN RD inner join PSSQLTEXTDEFN STD

 on RD.RECNAME = STD.SQLID

where

 RD.RECTYPE = '1'

 and STD.SQLTYPE = '2'

 and exists (

 select 1

 from PSRECFIELD RF1

 where RF1.RECNAME = RD.RECNAME

 and RF1.FIELDNAME like '%'

 )

 and exists (

 select 1

 from PSRECFIELD RF2

 where RF2.RECNAME = RD.RECNAME

 and RF2.FIELDNAME like '%'

 )

 and exists (

 select 1

 from PSRECFIELD RF3

 where RF3.RECNAME = RD.RECNAME

 and RF3.FIELDNAME like '%'

 )

;

 NOTE: you can expand the SQL to match on more fields if required just keeping adding exist conditions in the format shown.