User login
Sponsored links
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;
If &newvalue = "0000" Then
&bOverrun_Error = True;
End-If;
Bookmark/Search this post with:
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!
Unanswered Forum Posts
| Title | Under | Posted on |
|---|---|---|
| Regarding Error "SQR 6002) CANNOT OPEN THE PRINTER FILE: " in SQR | PeopleSoft Technical | 11/21/2008 - 1:17am |
| Pay calendar Setup | PeopleSoft Functional | 11/18/2008 - 5:30pm |
| Issue linking to Excel 2007 from PS HRMS 8.8 | PeopleSoft Technical | 11/17/2008 - 9:48am |
| Spreadsheet-Based Control System Analysis and Design | Programming | 11/11/2008 - 5:10am |
Stay updated
CompShack is a user driven Dev2Dev IT community. Developers can ask questions, help answer questions, or just browse code and articles contributed by users. No registration required.
You can register if you want to contribute code samples, share your knowledge at your own blog, and become adored in the community!
About Larry
Job title
Consultant
IT skills
Peoplesoft (everything from v2.4 on) - Tools, HR, Ben, Fin, Custom, SQL, Unix, HTML, Javascript, Dreamweaver, COBOL, Fortran , FOCUS, lotsa other stuff
Comments
Re: PeopleCode to AutoIncrement an AlphaNumeric
Join date: 06/23/08
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
Re: AutoIncrement an AlphaNumeric Transaction Number
Join date: 03/18/08
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.
Re: PeopleCode to AutoIncrement an AlphaNumeric Transaction Num
Join date: 06/30/08
In Inventory the MSR no gets generated automatically.
Could that PeopleCode logic will also work for your requirement?
Post new comment