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 '%'
)
;
No Comments