How to use USEEDIT field to find out Record Field Properties in PeopleSoft

This handy SQL script will provide how to find Record Field properties such as Key Fields, Edit Fields, Default values etc.. of a Record. Presented here are two versions of SQLs. First one give is quite simple and give you enough information about record field properties of a record and it uses PSRECFIELD tools table. Second one is bit complex and will give you more in-depth information such as last update datetime and operator id, etc..

Side Note: The SQLs are using Oracle Function BITAND. Also if you want to know more info about USEEDIT field please click here.
USEEDIT field assigns a decimal value to each individual bit value.

SQL 1 (simple)

SELECT
  FIELDNAME,
  DECODE(BITAND(USEEDIT, 1), 1 ,'Y', DECODE(BITAND(USEEDIT, 16), 16, 'Alt')) as Key,
  DECODE(BITAND(USEEDIT, 2048),2048 , 'Y' , '') as Search,
  DECODE(BITAND(USEEDIT, 32), 32 ,'Y','') AS List,
  DECODE(BITAND(USEEDIT, 256), 256 ,'Y','')AS Req,
  DECODE(BITAND(USEEDIT, 512), 512 ,'Xlat', DECODE(BITAND(USEEDIT, 8192), 8192 ,'Y/N', DECODE(BITAND(USEEDIT, 16384),16384 ,'Prompt',''))) AS Edit,
  EDITTABLE, DEFFIELDNAME
FROM PSRECFIELDDB
WHERE
  RECNAME = 'ACAD_PROG' --Record Name
ORDER BY FIELDNUM;

SQL 2 (complex)

SELECT A.recname,        A.fieldname,
       CASE
         WHEN B.fieldtype = 0 THEN 'CHAR'
         WHEN B.fieldtype = 1 THEN 'LONG CHAR'
         WHEN B.fieldtype = 2 THEN 'NUMBER'
         WHEN B.fieldtype = 3 THEN 'SIGNED NBR'
         WHEN B.fieldtype = 4 THEN 'DATE'
         WHEN B.fieldtype = 5 THEN 'TIME'
         WHEN B.fieldtype = 6 THEN 'DATETIME'
         WHEN B.fieldtype = 7
               OR B.fieldtype = 8 THEN 'IMAGE'
         ELSE NULL
       END           AS FIELDTYPE,
       CASE
         WHEN B.fieldtype = 2                OR B.fieldtype = 3 THEN Trim(To_char(B.length))                                       || '.' || To_char(B.decimalpos)
         ELSE To_char(B.length)
       END           AS FLDLEN,
       CASE
         WHEN Bitand(A.useedit, 256) > 0 THEN 'YES'
         ELSE 'NO'
       END           AS REQ,
       CASE
         WHEN Bitand(A.useedit, 1) > 0 THEN 'KEY'
         WHEN Bitand(A.useedit, 2) > 0 THEN 'DUP'
         WHEN Bitand(A.useedit, 16) > 0 THEN 'ALT'
         ELSE NULL 
       END           AS KEY_TYPE, 
       CASE 
         WHEN Bitand(A.useedit, 64) > 0 THEN 'DESC' 
         WHEN ( Bitand(A.useedit, 1) > 0 
                 OR Bitand(A.useedit, 2) > 0 
                 OR Bitand(A.useedit, 16) > 0 ) 
              AND Bitand(A.useedit, 64) = 0 THEN 'ASC' 
         ELSE NULL 
       END           AS DIR, 
       CASE 
         WHEN Bitand(A.useedit, 2048) > 0 THEN 'YES' 
         ELSE 'NO' 
       END           AS SRCH, 
       CASE 
         WHEN Bitand(A.useedit, 32) > 0 THEN 'YES' 
         ELSE 'NO' 
       END           AS LIST, 
       CASE 
         WHEN Bitand(A.useedit, 4) > 0 THEN 'YES' 
         ELSE 'NO' 
       END           AS SYS, 
       CASE 
         WHEN Trim(A.defrecname) = '' THEN A.deffieldname 
         ELSE Trim(A.defrecname) 
              || '.' 
              || A.deffieldname 
       END           AS DEFAULT_VALUE, 
       CASE 
         WHEN Bitand(A.useedit, 8) > 0 
              AND Bitand(A.useedit, 128) = 0 
              AND Bitand(A.useedit, 1024) = 0 THEN 'A' 
         WHEN Bitand(A.useedit, 8) > 0 
              AND Bitand(A.useedit, 128) > 0 
              AND Bitand(A.useedit, 1024) = 0 THEN 'AC' 
         WHEN Bitand(A.useedit, 8) > 0 
              AND Bitand(A.useedit, 128) > 0 
              AND Bitand(A.useedit, 1024) > 0 THEN 'ACD' 
         WHEN Bitand(A.useedit, 8) = 0 
              AND Bitand(A.useedit, 128) > 0 
              AND Bitand(A.useedit, 1024) = 0 THEN 'C' 
         WHEN Bitand(A.useedit, 8) = 0 
              AND Bitand(A.useedit, 128) > 0 
              AND Bitand(A.useedit, 1024) > 0 THEN 'CD' 
         WHEN Bitand(A.useedit, 8) = 0 
              AND Bitand(A.useedit, 128) = 0 
              AND Bitand(A.useedit, 1024) > 0 THEN 'D' 
         ELSE NULL 
       END           AS AUDT, 
       CASE 
         WHEN Bitand(A.useedit, 16384) > 0 THEN 'PROMPT' 
         WHEN Bitand(A.useedit, 512) > 0 THEN 'XLAT' 
         WHEN Bitand(A.useedit, 8192) > 0 THEN 'Y/N' 
         ELSE NULL 
       END           AS EDIT, 
       A.edittable   AS PROMPT_TABLE, 
       A.setcntrlfld AS SET_CONTROL_FLD, 
       CASE 
         WHEN Bitand(A.useedit, 4096) > 0 THEN 'YES' 
         ELSE 'NO' 
       END           AS REASONABLE_DT, 
       CASE 
         WHEN Bitand(A.useedit, 32768) > 0 THEN 'YES' 
         ELSE 'NO' 
       END           AS AUTO_UPDT, 
       CASE 
         WHEN Bitand(A.useedit, 262144) > 0 THEN 'FROM' 
         WHEN Bitand(A.useedit, 524288) > 0 THEN 'THROUGH' 
         ELSE NULL 
       END           AS SEARCH_FIELD, 
       CASE 
         WHEN A.subrecord = 'Y' THEN 'YES' 
         ELSE 'NO' 
       END           AS SUBRECORD, 
       A.lastupddttm, 
       A.lastupdoprid 
FROM   psrecfield A, 
       psdbfield B 
WHERE  A.recname = :1 --paste recname here 
       AND A.fieldname = B.fieldname 
ORDER  BY fieldnum;