Category: Records

Record Type Values

digitaleagle Fields, PeopleTools, Records, Uncategorized 3 Comments

Here are the values for the RECTYPE field in the table that stores the information about the records in PeopleSoft: PSRECDEFN.  Here are what the values mean:

  • 0 = SQL Table
  • 1 = SQL View
  • 2 = Work Record
  • 3 = Subrecord
  • 5 = Dynamic View
  • 6 = Query View
  • 7 = Temp Table

For example, you can do something like this to find all the tables that have the OPRID field:

SELECT A.RECNAME
FROM PSRECDEFN A, PSRECFIELDALL B
WHERE A.RECNAME = B.RECNAME
AND A.RECTYPE = 0
AND B.FIELDNAME = 'OPRID'

See the PeopleTools Tables for more information.

Querying the Component

digitaleagle Fields, Records, SQL, Uncategorized Leave a comment

Here are some queries to find things out about the component.

This one is a simple list of all the fields in the component (doesn’t take into account subpages, etc):

SELECT CP.PNLGRPNAME, CP.MARKET, CP.DESCR,
           CPG.PNLNAME, CPG.ITEMLABEL,
           PG.DESCR,
           PGF.FIELDTYPE, PGF.LBLTEXT, PGF.RECNAME, PGF.FIELDNAME
FROM PSPNLGRPDEFN CP,
         PSPNLGROUP CPG,
         PSPNLDEFN PG,
         PSPNLFIELD PGF
WHERE CP.PNLGRPNAME = 'JOB_DATA'
     AND CP.MARKET = 'GBL'
    AND CP.PNLGRPNAME = CPG.PNLGRPNAME
    AND CP.MARKET = CP.MARKET
    AND PG.PNLNAME = CPG.PNLNAME
    AND PGF.PNLNAME = PG.PNLNAME;

This one shows just the records involved:

SELECT DISTINCT CP.PNLGRPNAME, CP.MARKET, CP.DESCR,  PGF.RECNAME, R.RECDESCR, R.RECTYPE
FROM PSPNLGRPDEFN CP,
         PSPNLGROUP CPG,
         PSPNLDEFN PG,
         PSPNLFIELD PGF,
         PSRECDEFN R
WHERE CP.PNLGRPNAME = 'JOB_DATA'
     AND CP.MARKET = 'GBL'
    AND CP.PNLGRPNAME = CPG.PNLGRPNAME
    AND CP.MARKET = CP.MARKET
    AND PG.PNLNAME = CPG.PNLNAME
    AND PGF.PNLNAME = PG.PNLNAME
    AND PGF.RECNAME = R.RECNAME;

I hope this helps.

PeopleTools Tip: Searching for Records

digitaleagle Fields, PeopleTools Tip, Records, Uncategorized Leave a comment

You can find records that contain a specific field with the find definition references tool in App Designer, but what if you want to know what record contains field1 and field2?

You can go to the database to file that answer:

SELECT A.RECNAME
FROM PSRECFIELDALL A, PSRECFIELDALL B
WHERE A.RECNAME = B.RECNAME
AND A.FIELDNAME = ‘FIELD1’
AND B.FIELDNAME = ‘FIELD2’