Ask Question
Help Others
Home › Forums › PeopleSoft › PeopleSoft Technical

You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

AutoIncrement of AlphaNumeric Transaction Number

3 replies [Last post]
Wed, 07/16/2008 - 9:30am
venkat.srinivas18's picture
venkat.srinivas18
User offline. Last seen 12 years 28 weeks ago. Offline
Joined: 07/16/2008
Posts: 6

Suppose Say we have

Transaction Id :                        QC0001
This Code Will give output as : QC0002
                                                      .
                                                      .
                                                 
-----------------------------------------

Local number &qcno;

If %Component = Component.CB_QC_CMP And
      %Mode = "A" Then
   
   CB_QC_TBL.CB_QC_NO.Enabled = False;
   SQLExec("SELECT MAX(SUBSTR(CB_QC_NO,3,4)) FROM PS_CB_QC_TBL", &qcno);
   &qcno = Int(&qcno) + 1;
   &DECIMAL = 4 - Len(String(&qcno));
   If &DECIMAL < 0 Then
      Error ("FIELD LENGTH EXCEEDS REQUIRED LENGTH.");
   Else
      &myvalue = NumberToString("%04", &qcno);
      CB_QC_TBL.CB_QC_NO.Value = "QC" | &myvalue
   End-If;
   
End-If;
--------------

Any Suggestions Revert Back to me

Regards,
Venkat

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Top
  • Login to post comments
  • 3363 reads
Wed, 07/23/2008 - 8:46pm
#1
Lepa's picture
Lepa
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: AutoIncrement of AlphaNumeric Transaction Number

Thanks for sharing your code with us. You should be able to share your knowledge with us by posting them as "code sample" or "article". Login and then click on create content and in this example you would want to choose "code sample".

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 - 11:32am
#2
Larry's picture
Larry
User offline. Last seen 3 years 25 weeks ago. Offline
Joined: 03/18/2008
Posts: 19
Re: AutoIncrement of AlphaNumeric Transaction Number

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;

Top
Tue, 08/26/2008 - 1:22pm
#3
Lepa's picture
Lepa
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: AutoIncrement of AlphaNumeric Transaction Number

Hey Larry, thanks for sharing your code with us. I have added your above post to your blog at http://www.compshack.com/blog/larry - Please feel free to share more with us anytime using your own blog. In doing so, your posts will get more exposure in the community and thus provides a much better value :)

Thanks again - cheers!

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