Ask Question
Help Others
Home › PeopleSoft › PeopleCode

PeopleCode to AutoIncrement an AlphaNumeric Transaction Number

Tue, 08/26/2008 - 1:12pm — Larry
Your rating: None Average: 4.3 (4 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;

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
  • Login to post comments
  • 16269 reads
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.
Tue, 08/26/2008 - 6:40pm
#1
Lepa's picture
Lepa
User offline. Last seen 2 years 25 weeks 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

Top
Tue, 08/26/2008 - 9:59pm
#2
Larry's picture
Larry
User offline. Last seen 3 years 24 weeks 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.

Top
Sat, 09/06/2008 - 3:59am
#3
Nitin's picture
Nitin
User offline. Last seen 5 years 48 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?

Top