Records in a PeopleSoft Component

This simple SQL can be used to identify the records that are used behind an online PeopleSoft component.  Sometimes you just need to look at the data, sometimes you need to write scripts to manipulate or export the data.  The code below includes only the SQL Table and SQL View record types and filters out records and fields that are only related-display.

Yes, you could open App Designer to find the pages and records within the component, but I wanted a quicker way that would show only the records most likely to contain the data.  I especially do not want to parse through the numerous temp tables and work records that will show up in App Designer for some components.

This should work on Oracle databases.  It has bind variables to accept the component name as input so you may need to replace or pass the values, depending on your SQL tool.

There are several ways to find the component name while online, if you need to.  In some environments Ctrl+Shift+J may work, or you can also look at the URL.  Hint: it goes /c/MENUNAME.COMPONENT_NAME.  Good luck!

-- RECORDS USED IN A COMPONENT V2, INCLUDES SUBPAGES
SELECT C.RECNAME, C.RECTYPE, C.RELLANGRECNAME
FROM PSPNLFIELD B, PSRECDEFN C
WHERE B.RECNAME = C.RECNAME
AND C.RECTYPE IN ('0','1')      -- return only physical database RECORDS or VIEWS, no work records
AND BITAND (B.FIELDUSE,16) = 0  --PSPNLFIELD.FIELDUSE bit 4, value 16 represents Related Display field
AND BITAND (B.FIELDUSE,1) = 0  --PSPNLFIELD.FIELDUSE bit 1, value 1 represents Display Only property
AND (B.PNLNAME IN (SELECT DISTINCT A.PNLNAME FROM PSPNLGROUP A 
                  WHERE A.MARKET = 'GBL'
                  AND A.PNLGRPNAME = :PNLGRPNAME
                  AND A.PNLNAME = B.PNLNAME 
                 )
OR B.PNLNAME IN (SELECT DISTINCT X.SUBPNLNAME FROM PSPNLFIELD X
                 WHERE X.SUBPNLNAME <> ' '
                 AND EXISTS (SELECT 'X' FROM PSPNLGROUP A 
                               WHERE A.MARKET = 'GBL'
                               AND A.PNLGRPNAME = :PNLGRPNAME
                               AND A.PNLNAME = X.PNLNAME 
                              )
                 ))         
GROUP BY C.RECNAME, C.RECTYPE, C.RELLANGRECNAME
ORDER BY C.RECNAME