User login
Sponsored links
Recent blog posts
- How to resubmit on Integration Broker message with DONE status
- Message cannot be changed. Message referenced in runtime tables (Doc ID 660969.1)
- PeopleSoft Object Type List
- PeopleSoft Data Integrity Error (124,85)
- Unwanted Save Warnings in Display Only Pages
- Breadcrumb SQL to find the Navigation path for the component in the PIA
- Application Designer Index naming convention
- How to create Trigger in DB2
- Emplid increment automatically
- How can we romove multiple duplicate rows of data printing under each Print command in SQR Program?
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
- 14410 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!
Unanswered Forum Posts
| Title | Under | Posted on |
|---|---|---|
| ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
| TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
| how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
| How to add custom process in Paycycle PeopleSoft Accounts payable. | PeopleSoft Technical | 06/07/2017 - 4:39am |
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
Poll
New forum topics
- ADD 24 months starting from current month.(peoplesoft)
- TRC values dropdown
- OHS is rendering XML instead of the view or data
- Search Results
- PS Query - Need Dependency and Prunable examples
- PS HCM AWE - Transaction routing to Requestor for approval, when he is the Delegatee by his 1 up Manager
- how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode
- How to add custom process in Paycycle PeopleSoft Accounts payable.
Recent comments
- ООО "СИТИ-СПА" +79266772255 89266772255 КИДАЛЫ!!
4 days 13 hours ago - ООО "СИТИ-СПА" +79266772255 89266772255 КИДАЛЫ!!
4 days 13 hours ago - Вы знаете, что пишут о Вас ваши клиенты?
1 week 21 hours ago - Вы знаете, что пишут о Вас ваши клиенты?
1 week 21 hours ago - кресла качалки для детей
1 week 21 hours ago - Куплю бизнес по обслуживанию домофонов.
1 week 23 hours ago - Куплю бизнес по обслуживанию домофонов.
1 week 23 hours ago - Перезвоните,
1 week 2 days ago - Перезвоните,
1 week 2 days ago - Перезвоните,
1 week 2 days ago
Comments
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
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.
In Inventory the MSR no gets generated automatically.
Could that PeopleCode logic will also work for your requirement?
Post new comment