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:
GetNextNumberGetNextNumberWithGapsCommit
Note that the function GetNextNumberWithGaps is now deprecated.
What's the difference?
-
GetNextNumberdoesn't auto-commit. This can lead to table locking issues if there are a lot of transactions requiring the sequence number field.GetNextNumberWithGapsCommitcommits immediately after getting the next sequence number. -
GetNextNumbercan only increment by 1.GetNextNumberWithGapsCommitcan increment by more than 1. -
GetNextNumbercan only be called in aSavePostChangeevent.GetNextNumberWithGapsCommitcan be called form any PeopleCode event (according to PeopleBooks anyway ...). -
GetNextNumberWithGapsCommitcan use a SQLWHEREclause 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
_LASTe.g. in App Designer search for fields with%_LAST. - The
PS_INSTALLATION(and product relatedPS_INSTALLATIONtables) store a lot of these fields
No Comments