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
