Ask Question
Help Others
Home › Blogs

Larry's blog

PeopleCode to AutoIncrement an AlphaNumeric Transaction Number

Tue, 08/26/2008 - 1:12pm — Larry

I have code to auto-increment an alphanumeric field, using SQL calls. It is specific to a 4-character field, but can easily be adapted to different length fields.

SQLExec("SELECT decode(substr(oldvalue,2,3),'ZZZ', TRANSLATE(UPPER(SUBSTR(oldvalue,1,1)),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,1,1)) ||decode(substr(oldvalue,3,2),'ZZ',TRANSLATE(UPPER(SUBSTR(oldvalue,2,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,2,1))||decode(substr(oldvalue,4,1),'Z', TRANSLATE(UPPER(SUBSTR(oldvalue,3,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,3,1))||TRANSLATE(UPPER(SUBSTR(oldvalue,4,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0') FROM dual, (select :1 as oldvalue from dual)", &priorvalue, &newvalue);
If &newvalue = "0000" Then
   &bOverrun_Error = True;
End-If;

Unanswered Forum Posts

Title Under Posted on
How to run nvision report through portal PeopleSoft Technical 08/31/2010 - 4:45am
Any1 here who can help? PeopleSoft Technical 08/31/2010 - 2:12am
How to create matrix layout PeopleSoft Technical 08/27/2010 - 2:22am
UnCaught C++ Exception PeopleSoft Technical 08/10/2010 - 9:05am
more