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.