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.

Revision #1
Created Mon, Sep 9, 2019 6:05 PM by PeopleSoft Wiki
Updated Mon, Sep 9, 2019 6:08 PM by PeopleSoft Wiki