The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Monday, 6 January 2014

Finding components where a particular field is used

As a part of impact analysis while doing any major development work in PeopleSoft, often you may have to search for definition references of fields and other objects.  The Find In will bring out list of all the pages where the field might be used. Now to find out the component list where these pages are used, you may have to end up doing the Find In for each and every page. Sometimes if the referenced pages are more, it will become a tiring work. I want to share a simple SQL which could reduce most of your work if you are looking for component references for a field.

Again, the SQL provided here is a simple one and for example purpose. However you could explore more on the system tables and come up with such simpler and time saving SQL’s. If you already do have some, keep sharing the same in the comments section.

SELECT nvl(b.pnlgrpname,'Not added in any component.') AS Component,
  a.pnlname         AS Page,
  a.pnlfldid        AS FieldId
FROM pspnlfield a,
  pspnlgroup b
WHERE a.recname = 'RECORD_NAME'
AND a.fieldname = 'FIELD_NAME'

AND a.pnlname   = b.pnlname(+);


  1. How about fields in secondary pages?

  2. Hi Ashar,

    That is a good point. The SQL provided above is a simple one to start with. It will not display the corresponding component if the record field is placed in a secondary page or a subpage, but it will just list the secondary or subpage name.

    If you want to cover all types of pages, it is better to write a simple PL/SQL program than making the SQL more and more complex. Basically, you should loop through each instance in the PSPNLFIELD table and if the page type is subpage or secondary page, then you may have to find in the secondary or subpage references again in the PSPNLFIELD table. Repeat the looping until the page is a standard page and then you could easily list down the corresponding components. The fields FIELDTYPE & SUBPNLNAME will help you to reach to the base standard page. Though it sounds bit complicated, this shouldn’t take more than 30 lines of PL/SQL.