Auto Numbering

There are a number of ID fields in PeopleSoft that are auto-numbered. A good example is the EMPLID (employee ID) field. The field EMPLID_LAST_EMPL stores the last system allocated EMPLID. This field can be found in the PS_INSTALLATION table. This means the next time a person (EMPLID) is created, they will get the value in in the EMPLID_LAST_EMPL field of the PS_INSTALLATION table + 1.

Getting the Next Number

Now you might think that if you wanted the next available ID for such a field, you would need to write some PeopleCode to increment the value by 1. However, this isn't necessary as there are two delivered functions to do this for you:

  • GetNextNumber
  • GetNextNumberWithGapsCommit

Note that the function GetNextNumberWithGaps is now deprecated.

What's the difference?

  • GetNextNumber doesn't auto-commit. This can lead to table locking issues if there are a lot of transactions requiring the sequence number field. GetNextNumberWithGapsCommit commits immediately after getting the next sequence number.
  • GetNextNumber can only increment by 1. GetNextNumberWithGapsCommit can increment by more than 1.
  • GetNextNumber can only be called in a SavePostChange event. GetNextNumberWithGapsCommit can be called form any PeopleCode event (according to PeopleBooks anyway …).
  • GetNextNumberWithGapsCommit can use a SQL WHERE clause when updating multiple sequence numbers at the same time.

GetNextNumber examples:

GetNextNumber(PS_INSTALLATION.EMPLID_LAST_EMPL, 9999999999)
GetNextNumber("PS_INSTALLATION", "EMPLID_LAST_EMP", 9999999999)

The value 9999999999 is the maximum value of the EMPLID_LAST_EMPL field which has a length of 10.

GetNextNumberWithGapsCommit examples:

GetNextNumberWithGapsCommit(PS_INSTALLATION.EMPLID_LAST_EMPL, 9999999999, 1)

The extra parameter with a value of 1 means the increment value (in this case increment by 1).

Finding where sequence numbers are stored.

  • Try searching for fields that end in _LAST e.g. in App Designer search for fields with %_LAST.
  • The PS_INSTALLATION (and product related PS_INSTALLATION tables) store a lot of these fields
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License