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 generally 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. All pluses as far as I can see…

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 (note you could expand the SQL to match on more). 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 '%'
    )
;

A naming convention for new views

The majority of views I've created are to return the latest effective dated/sequenced/active rows from a table. I refer to these as the current data. So my view name usually includes the text CURR which is short for current. For example, current fpr current people's names in the system, the view might be called:

PB_CURR_NAME_VW

You have 15 characters to work with:

  • 3 characters are going to go to your customisation prefix (e.g. PB_ in my example) unless you are fortunate enough to have a 1 character prefix (or unfortunate enough to have more!).
  • 3 characters are going to go to identifying that this is a view _VW

That leaves you with 9 characters, one of which is probably going to be an underscore (_) to separate words. So think about that when you go to name your view.

Creating new views for reuse

A couple of tips when creating a new view that you want others to use:

  • Include as many of the fields from the underlying tables as possible. So long as it make sense to do so based on the SQL in the view. The more fields, the more likely that your view will provide the information someone else wants.
  • Don't include more business rules/logic in the view than what is absolutely necessary. For example, if you have a view that returns current names, then don't just include primary name types - return all name types and then filter the name types you need in your own code. Of course don't make it too hard on yourself either - there's no point having a view that you have to add a number of conditions to just for the sake of reuse. Remember reuse is going to be a comprimise.
  • Give your views a decent name & description. Its hard to find views for reuse, don't make it any harder by using an obscure name and not providing a relevant description.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License