PeopleSoft Wiki

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 = 'PSRECDEFN'
  AND A.FIELDNAME = B.FIELDNAME
ORDER BY FIELDNUM