Mostly for my own benefit, but perhaps someone else might find this useful…

In PeopleCode there’s a built in mechanism for autonumbering records. Using the built in assign_auto_number function you can grab the next number from a table in the Peoplesoft db (in MS SQL Server it’s called PS_AUTO_NUM_TBL; don’t know if that’s different on other architectures).

One way of setting up this functionality in the auto-num table is to use T-SQL to directly update the database with the appropriate record that you’ll access in your PeopleCode, like so:

INSERT INTO PS_AUTO_NUM_TBL VALUES (
'%SETID%',
'%NUMBER_TYPE%',
'%PREFIX%',
'%LENGTH%',
'%DESCRIPTION%',
%NEXT NUMBER%,
'%ACTIVE?%'
)


This works fine and the PeoplCode can access records created in this way. The drawback is that records inserted directly into the database do not allow the Peoplesoft PIA (Peoplesoft Internet Architecture - the web-based front-end GUI for Peoplsoft) to appropriately manage these records - when you try to use the PIA to add or manage these you won’t be able to tell which numbers are which because in the web display you’ll be missing the number type field; they’ll look like this pic:

As you can tell, it’s hard to figure out what number is what in that list. On top of that, you can’t add a new automatic number without having a number type ready for it.

The answer to this conundrum isn’t easy to find in the Peoplsoft online help (it may be in Oracle’s PS portal, but since my company’s terminated our support contract I can’t access that). I found the answer somewhere online a few months back, but in a fit of laziness neglected to add it to del.icio.us, so I couldn’t find it. Luckily a small reminder was all I needed. The number type in PeopleSoft is controlled by a translate value called NUM_TYPE.

So to make use of the built-in PeopleSoft management of automatic number values, first you’d need to navigate to the Translate Value management [PeopleTools | Utilities | Administration | Translate Values] and look at the NUM_TYPE filed. If you’re creating a new automatic number, you’ll want to create a new value for the number type you’ll be using to hold your autonumber here beforte creating the autonumber itself. If you’ve managed these directly in the database before you’ll want to make sure the value you choose isn’t already in the autonumber table; ones directly inserted into the database won’t show up here.

Next you’ll go to the automatic numbering setup component [Set Up CRM | Common Definitions | Codes and Automatic Numbering | Automatic Numbering]. You’ll need to select the SetID and number type you used in the translate value you ust set up. Then add the characteristics you’d have inserted manually into the database and you’re all set.