Peoplesoft Query to Find Records in Component

One of the common request to us, developers, is to identify the tables under a component. This is commonly requested when we need to do Data Mover Script (DMS) for a configuration/set-up page. 

I know we already have the Data Migration Workbench in place but many of us are still using DMS to migrate these set-up/configuration to higher environment (test or Prod).

I've wrote a simple SQL to easily identify which are the tables or records used in a Peoplesoft Component.


SELECT DISTINCT B.RECNAME--A.PNLNAME, B.RECNAME, B.FIELDNAME, B.LBLTEXT, D.RECTYPE 
FROM            PSPNLGROUP A , 
                PSPNLFIELD B , 
                PSRECFIELD C , 
                PSRECDEFN D 
WHERE           (A.PNLNAME = B.PNLNAME 
                 OR A.PNLNAME = B.SUBPNLNAME) 
AND             B.RECNAME = C.RECNAME 
AND             C.RECNAME = D.RECNAME 
AND             PNLGRPNAME = 'JOB_DATA' --COMPONENT NAME 
AND             D.RECTYPE = 0;                 --(0 - SQL Table, 1 - SQL View) 


You can modify this SQL depending on your needs. For example, if you want to see SQL Views from the result, just add RECTYPE = 1. Same as the details you want, like what are the RECORD.FIELD used in a page or component.