PeopleCode to AutoIncrement an AlphaNumeric Transaction Number

Your rating: None Average: 5 (3 votes)

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;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Lepa's picture
User offline. Last seen 2 weeks 2 days ago. Offline
Joined: 06/23/2008
Posts: 591
Re: PeopleCode to AutoIncrement an AlphaNumeric

Larry as your SQL works fine I'm just curious on what kind of requirement derived such code. I find it odd to increment an alpha numeric character :) - none the less, your SQL works perfect.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Larry's picture
User offline. Last seen 7 weeks 5 days ago. Offline
Joined: 03/18/2008
Posts: 19
Re: AutoIncrement an AlphaNumeric Transaction Number

Have an alphanumeric field, ACTIVITY_ID. Was given requirements to create a Mass Add page, allowing user to auto-generate a large number of Project_Activity rows given a starting point within an alphanumeric range, along with the # of new rows desired, along with other common field values. Tried to get user to agree to only numeric incrementing, but they insisted upon alphanumeric. Go figure.

Nitin's picture
User offline. Last seen 2 years 31 weeks ago. Offline
Joined: 06/30/2008
Posts: 73
Re: PeopleCode to AutoIncrement an AlphaNumeric Transaction Num

In Inventory the MSR no gets generated automatically.
Could that PeopleCode logic will also work for your requirement?

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
11 + 5 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.