Extra Hot

Of the basic PeopleTools tables, the USEEDIT field of the record field properties (PSRECFIELD) is perhaps the most intruiging. The USEEDIT field is a 32-bit integer field. For some reason (is it for maximizing space? is it forward-thinking? to eliminate the need to add fields on future attributes?), PeopleSoft crammed a bunch of field properties into this single field. PeopleSoft did this by assigning a property to each bit of the field value.

For example: if the value of USEEDIT is 257, its binary representation is 0000000100000001. As you can see, the rightmost 1st bit and the 9th bit are turned on. The 1st bit represents the Key attribute, whereas the 9th bit represents the Required attribute. This means that a USEEDIT value of 257 represents a field which is both a Key and is Required.

Ok. That sounds simple enough, right? But how could we query these attributes using SQL? In Oracle, we could use the builtin bitwise functions; in MS SQL Server, we could use the builtin bitwise operators. The bitwise operation to use is a bitwise AND operation. In Oracle, use the bitand() function; in MS SQL Server, we use the & operator.

To check if an attribute is turned on, simply perform the bitwise AND operation against the attribute you are checking. The result will be zero if the bits doesn’t match. To illustrate, to check if the Required attribute is on, we perform the operation on the 9th bit. In Oracle: bitand(USEEDIT, 256) > 0 ^[1]; in MS SQL Server: USEEDIT & 256 > 0. Note that 256 = 100000000 in binary.

In DB2, it is not so simple because there is no builtin bitwise operators or functions. There is still a solution: If the rightmost 1st bit of the binary representation of a number is turned on, this means that the number is odd, otherwise it is even. How do we check this? In DB2 (as is also true for MSSQL), the result of an arithmetic operation involving all integer operands will also be an integer. For a division, the remainder will be lost. This means that 3/2 = 1; 5/2 = 2; 7/2 = 3; etc. If any of the operands is a decimal, then the result will have decimal precision: 3/2.0 = 1.5; 5/2.0 = 2.5; 7/2.0 = 3.5. Using these facts, we can say that USEEDIT is odd if USEEDIT/2 <> USEEDIT/2.0. This then allows us to check for the rightmost bit. If USEEDIT/2 <> USEEDIT/2.0, then the Key attribute is on.

Now to check the other attributes, we just need to shift the bits to the right a certain number of places – depending on which bit we are looking at. Then we perform the same comparison on the shifted value. By DB2’s arithmetic behavior: USEEDIT/2 shifts 1 binary digit; USEEDIT/4 shifts 2 binary digits; USEEDIT/8 shifts 3 binary digit; etc. In general, USEEDIT/(2^n) shifts n binary digits. So, knowing all this, to check the Required attribute, we need to shift the original value by 8 digits, then perform the odd/even check: (USEEDIT/(2^8))/2 <> (USEEDIT/(2^8))/2.0. Giving us (USEEDIT/256)/2 <> (USEEDIT/256)/2.0.

The following table gives a list of the properties each bit represents, plus the corresponding SQL predicate to check attributes for each database product. Download Spreadsheet

Using this spreadsheet, the following query returns a view of record field properties that contains information similar to those found on the Use and Edits display in Application Designer.

DB2 Version


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
         RTRIM(CHAR(B.LENGTH)) || '.' || CHAR(B.DECIMALPOS)
    ELSE CHAR(B.LENGTH)
END AS FLDLEN,
CASE
    WHEN (A.USEEDIT/256)/2 <> (A.USEEDIT/256)/2.0 THEN
        'YES'
    ELSE 'NO'
END AS REQ,
CASE
    WHEN A.USEEDIT/2 <> A.USEEDIT/2.0 THEN
        'KEY'
    WHEN (A.USEEDIT/2)/2 <> (A.USEEDIT/2)/2.0 THEN
        'DUP'
    WHEN (A.USEEDIT/16)/2 <> (A.USEEDIT/16)/2.0 THEN
        'ALT'
    ELSE NULL
END AS KEY_TYPE,
CASE
    WHEN (A.USEEDIT/64)/2 <> (A.USEEDIT/64)/2.0 THEN
        'DESC'
    WHEN ( A.USEEDIT/2 <> A.USEEDIT/2.0
        OR (A.USEEDIT/2)/2 <> (A.USEEDIT/2)/2.0
        OR (A.USEEDIT/16)/2 <> (A.USEEDIT/16)/2.0 )
    AND  (A.USEEDIT/64)/2 = (A.USEEDIT/64)/2.0 THEN
        'ASC'
    ELSE NULL
END AS DIR,
CASE
    WHEN (A.USEEDIT/2048)/2 <> (A.USEEDIT/2048)/2.0 THEN
        'YES'
    ELSE 'NO'
END AS SRCH,
CASE
    WHEN (A.USEEDIT/32)/2 <> (A.USEEDIT/32)/2.0 THEN
        'YES'
    ELSE 'NO'
END AS LIST,
CASE
    WHEN (A.USEEDIT/4)/2 <> (A.USEEDIT/4)/2.0 THEN
        'YES'
    ELSE 'NO'
END AS SYS,
CASE
    WHEN A.DEFRECNAME = '' THEN
        A.DEFFIELDNAME
    ELSE
        RTRIM(A.DEFRECNAME) || '.' || A.DEFFIELDNAME
END AS DEFAULT_VALUE,
CASE
    WHEN (A.USEEDIT/8)/2 <> (A.USEEDIT/8)/2.0
    AND  (A.USEEDIT/128)/2 = (A.USEEDIT/128)/2.0
    AND  (A.USEEDIT/1024)/2 = (A.USEEDIT/1024)/2.0 THEN
        'A'
    WHEN (A.USEEDIT/8)/2 <> (A.USEEDIT/8)/2.0
    AND  (A.USEEDIT/128)/2 <> (A.USEEDIT/128)/2.0
    AND  (A.USEEDIT/1024)/2 = (A.USEEDIT/1024)/2.0 THEN
        'AC'
    WHEN (A.USEEDIT/8)/2 <> (A.USEEDIT/8)/2.0
    AND  (A.USEEDIT/128)/2 <> (A.USEEDIT/128)/2.0
    AND  (A.USEEDIT/1024)/2 <> (A.USEEDIT/1024)/2.0 THEN
        'ACD'
    WHEN (A.USEEDIT/8)/2 = (A.USEEDIT/8)/2.0
    AND  (A.USEEDIT/128)/2 <> (A.USEEDIT/128)/2.0
    AND  (A.USEEDIT/1024)/2 = (A.USEEDIT/1024)/2.0 THEN
        'C'
    WHEN (A.USEEDIT/8)/2 = (A.USEEDIT/8)/2.0
    AND  (A.USEEDIT/128)/2 <> (A.USEEDIT/128)/2.0
    AND  (A.USEEDIT/1024)/2 <> (A.USEEDIT/1024)/2.0 THEN
        'CD'
    WHEN (A.USEEDIT/8)/2 = (A.USEEDIT/8)/2.0
    AND  (A.USEEDIT/128)/2 = (A.USEEDIT/128)/2.0
    AND  (A.USEEDIT/1024)/2 <> (A.USEEDIT/1024)/2.0 THEN
        'D'
    ELSE NULL
END AS AUDT,
CASE
    WHEN (A.USEEDIT/16384)/2 <> (A.USEEDIT/16384)/2.0 THEN
        'PROMPT'
    WHEN (A.USEEDIT/512)/2 <> (A.USEEDIT/512)/2.0 THEN
        'XLAT'
    WHEN (A.USEEDIT/8192)/2 <> (A.USEEDIT/8192)/2.0 THEN
        'Y/N'
    ELSE NULL
END AS EDIT,
A.EDITTABLE AS PROMPT_TABLE,
A.SETCNTRLFLD AS SET_CONTROL_FLD,
CASE
    WHEN (A.USEEDIT/4096)/2 <> (A.USEEDIT/4096)/2.0 THEN
        'YES'
    ELSE 'NO'
END AS REASONABLE_DT,
CASE
    WHEN (A.USEEDIT/32768)/2 <> (A.USEEDIT/32768)/2.0 THEN
        'YES'
    ELSE 'NO'
END AS AUTO_UPDT,
CASE
    WHEN (A.USEEDIT/262144)/2 <> (A.USEEDIT/262144)/2.0 THEN
        'FROM'
    WHEN (A.USEEDIT/524288)/2 <> (A.USEEDIT/524288)/2.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
  AND A.FIELDNAME = B.FIELDNAME
ORDER BY FIELDNUM

Oracle Version


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

MS SQL Server Version


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
         LTRIM(RTRIM(STR(B.LENGTH))) + '.' + LTRIM(RTRIM(STR(B.DECIMALPOS)))
    ELSE LTRIM(RTRIM(STR(B.LENGTH)))
END AS FLDLEN,
CASE
    WHEN A.USEEDIT & 256 > 0 THEN
        'YES'
    ELSE 'NO'
END AS REQ,
CASE
    WHEN A.USEEDIT & 1 > 0 THEN
        'KEY'
    WHEN A.USEEDIT & 2 > 0 THEN
        'DUP'
    WHEN A.USEEDIT & 16 > 0 THEN
        'ALT'
    ELSE NULL
END AS KEY_TYPE,
CASE
    WHEN A.USEEDIT & 64 > 0 THEN
        'DESC'
    WHEN ( A.USEEDIT & 1 > 0
        OR A.USEEDIT & 2 > 0
        OR A.USEEDIT & 16 > 0 )
    AND  A.USEEDIT & 64 = 0 THEN
        'ASC'
    ELSE NULL
END AS DIR,
CASE
    WHEN A.USEEDIT & 2048 > 0 THEN
        'YES'
    ELSE 'NO'
END AS SRCH,
CASE
    WHEN A.USEEDIT & 32 > 0 THEN
        'YES'
    ELSE 'NO'
END AS LIST,
CASE
    WHEN A.USEEDIT & 4 > 0 THEN
        'YES'
    ELSE 'NO'
END AS SYS,
CASE
    WHEN A.DEFRECNAME = '' THEN
        A.DEFFIELDNAME
    ELSE
        RTRIM(A.DEFRECNAME) + '.' + A.DEFFIELDNAME
END AS DEFAULT_VALUE,
CASE
    WHEN A.USEEDIT & 8 > 0
    AND  A.USEEDIT & 128 = 0
    AND  A.USEEDIT & 1024 = 0 THEN
        'A'
    WHEN A.USEEDIT & 8 > 0
    AND  A.USEEDIT & 128 > 0
    AND  A.USEEDIT & 1024 = 0 THEN
        'AC'
    WHEN A.USEEDIT & 8 > 0
    AND  A.USEEDIT & 128 > 0
    AND  A.USEEDIT & 1024 > 0 THEN
        'ACD'
    WHEN A.USEEDIT & 8 = 0
    AND  A.USEEDIT & 128 > 0
    AND  A.USEEDIT & 1024 = 0 THEN
        'C'
    WHEN A.USEEDIT & 8 = 0
    AND  A.USEEDIT & 128 > 0
    AND  A.USEEDIT & 1024 > 0 THEN
        'CD'
    WHEN A.USEEDIT & 8 = 0
    AND  A.USEEDIT & 128 = 0
    AND  A.USEEDIT & 1024 > 0 THEN
        'D'
    ELSE NULL
END AS AUDT,
CASE
    WHEN A.USEEDIT & 16384 > 0 THEN
        'PROMPT'
    WHEN A.USEEDIT & 512 > 0 THEN
        'XLAT'
    WHEN 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 A.USEEDIT & 4096 > 0 THEN
        'YES'
    ELSE 'NO'
END AS REASONABLE_DT,
CASE
    WHEN A.USEEDIT & 32768 > 0 THEN
        'YES'
    ELSE 'NO'
END AS AUTO_UPDT,
CASE
    WHEN A.USEEDIT & 262144 > 0 THEN
        'FROM'
    WHEN 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
  AND A.FIELDNAME = B.FIELDNAME
ORDER BY FIELDNUM

Acknowledgment: Kudos to mhean for helping me test the Oracle solution.

[1]If bitand() does not work, you’re probably using an older Oracle version, try adding a +0 to the operation. See this discussion.

Mhean ( Singapore

) Says:
December 2nd, 2005 at 7:59 pm

It’s always a pleasure to help out a cool guru. ;)

Luis Felipe Trigo ( Mexico

) Says:
March 7th, 2006 at 6:40 am

Hola:
Very useful query on RECORD/FIELD properties
A couple of years ago I did an Excel on USEEDIT.
http://peoplesoft.ittoolbox.com/code/archives.asp?a=s&d=1769

Ganesh ( India

) Says:
April 9th, 2007 at 7:59 pm

Very good. very very usefull

Frank ( Netherlands

) Says:
April 15th, 2008 at 11:24 pm

Very nice. Just what I needed today!

Iouri (

) Says:
August 20th, 2008 at 1:17 am

Great post, thanks a lot

Cipher ( Canada

) Says:
March 26th, 2009 at 12:38 am

Just what I needed… thanks a lot.

reddy1979 ( United States

) Says:
October 15th, 2009 at 3:12 am

Wow..Great Cool stuff!! Thanks to person who did the hard work

Val ( United States

) Says:
July 21st, 2010 at 1:14 am

Solid work - Thank you! My goal was the SQL update all fields on a record to audit = ‘ACD’, though this does not seem possible…
Thanks again!

Riaz ( United States

) Says:
August 14th, 2010 at 6:07 am

Awesome!!!
It saved a lot of time in figuring out the Record field properties.

Thanks for the help

Herb ( United Kingdom

) Says:
December 6th, 2010 at 2:31 am

This is in all probability the most effective article that ever cross my reference. I don’t see why anybody should disagree. It might be too simple #for them# to comprehend…anyway good work i’m coming back here for More Nice Stuff!!

Manoj Negi ( Japan

) Says:
May 9th, 2011 at 7:10 pm

Thank you very much… You saved me… I was not able to get the value for required field for my automatic configuration workbook… You are genius… thank you once again…