PeopleSoft Autoincrement character field

Irrespective of character length below function will return next character

Function auto_incr_char(&Var As string) Returns string
   Local string &substr;
   
   &nc = Len(&Var);
   &var1 = "";
   For &c = &nc To 1 Step - 1
      &substr = Substring(&Var, &c, 1);
      rem check between 65 to 90;
      If Code(&substr) >= 65 And
            Code(&substr) < 90 Then
         &substr = Char(Code(&substr) + 1);
         &var1 = &substr | &var1;
         &var1 = Substring(&Var, 1, &c - 1) | &var1;
         Break;
      Else
         &var1 = "A" | &var1;
      End-If;
   End-For;
   
   Return &var1;
End-Function;

Autoincrement Alphanumeric number using PeopleCode

Below function was created to autoincrement aplhanumeric field, 

3 parameters are needed for executing the function, Previous or max string value, numbers of characters in a field, number of integer values in a field.

Function auto_incr(&Var As string, &Nc As integer, &ni As integer) Returns string

   Local integer &max_int, &i_str;

   Local boolean &maxint;

   Local boolean &max_str;

   Local string &str_i, &str_e, &substr;

   

   &str_i = "";

   For &c = 1 To &ni

      &str_i = &str_i | "9";

   End-For;

   &max_int = Value(&str_i);

   &str_e = Substring(&Var, &Nc + 1, &ni);

   &i_str = Value(&str_e);

   

   If &i_str < &max_int Then

      &i_str = &i_str + 1;

      &var1 = Substring(&Var, 1, &Nc) | &i_str;

   Else

      &maxint = True;

      &str_i = "";

      For &c = 1 To &ni - 1

         &str_i = &str_i | "0";

      End-For;

      &str_i = &str_i | "1";

   End-If;

   

   If &maxint = True Then

      For &c = &Nc To 1 Step - 1

         &substr = Substring(&Var, &c, 1);

         rem check between 65 to 90;

         If Code(&substr) >= 65 And

               Code(&substr) < 90 Then

            &substr = Char(Code(&substr) + 1);

            &var1 = Replace(&Var, &c, 1, &substr);

            &var1 = Replace(&var1, &Nc + 1, &ni, &str_i);

            Break;

         End-If;

      End-For;

   End-If;

   Return &var1;

End-Function;

&str = "AZZ899";

&res = auto_incr(&str, 3, 3);

WinMessage(&res);

Create / Drop table in PeopleCode

There are instances where we need to create and drop the table dynamically using PeopleCode / AE.

In sqr we have on-error if there are any issues in creating a table.
In the same way for PeopleCode we can use try..catch block.

Example: 

try
sqlexec("Drop table PS_XXXX");
catch (exception e)
Rem ignore exception;
End-try;

sqlexec("Create table PS_XXX ....");

Crack VBA excel password

Step 1:

To crack the password of an VBA Macro / coding first download an HEX Editor, its available from the site http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm

Step 2:

Close the workbook and open the workbook file in the hex editor.
Find the string "DPB" and change it to "DPx" (x is in small letter).
Save the file.

Step 3:

Open the workbook and click OK until the workbook is open (one or more dialogs are displayed describing various problems with the VBA project).

Press ALT+F11, choose the menu command Tools->VBAProject Properties, navigate to the Protection tab, and change the password but do not remove it (note the new password). Save, close,

Step 4:

Re-open the workbook. Press ALT+F11 and enter the new password.

Choose Tools->VBAProject Properties, navigate to the Protection tab, and remove the password. Save the workbook.

Now open the same workbook, You can use the password that you already given.

Aggregate multiple rows of data into 1 column and insert into multiple columns

There will be scenario where we want to extract multiple rows data into one column and may insert column data for multiple columns in a single or multiple columns. 
This can be done using listagg and substr functions using oracle.

Below SQL will extract multiple rows of compensation in a single column, each value separated by delimiter comma (,).

 SELECT COUNT(*) c
,comp.emplid
,comp.effdt
,COMP.EMPL_RCD
,COMP.EFFSEQ
, listagg (comp.comp_ratecd||','||comp.comprate, ',') WITHIN
GROUP (
ORDER BY comp.comp_ratecd) comprates
FROM PS_COMPENSATION COMP
, PS_COMP_RATECD_TBL B
WHERE COMP.COMP_RATECD= B.COMP_RATECD
AND B.EFF_STATUS = 'A'
AND B.COMP_BASE_PAY_SW ='N'
AND B.EFFDT=(
SELECT MAX(B1.EFFDT)
FROM PS_COMP_RATECD_TBL B1
WHERE B.COMP_RATECD=B1.COMP_RATECD
AND b1.effdt <=COMP.EFFDT)
GROUP BY comp.emplid,comp.effdt,COMP.EMPL_RCD,COMP.EFFSEQ

And using substr and instr we can split one column data to multiple columns.
Below sql will show how multiple rows of data are extracted into 1 column and insert into multiple columns (10 columns).
SELECT A.EMPLID 
 ,A.EFFDT 
 ,a.EMPL_RCD 
 ,A.EFFSEQ 
 , NVL((CASE WHEN A.C >= '1' THEN substr(a.comprates  ,1 
 ,instr(a.comprates ,','  ,1  ,1)-1) ELSE '' END)  ,' ') COL1 
 , NVL((CASE WHEN A.C=0 THEN '' 
WHEN A.C=1 THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,1)+1 
 ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1  ,1)+1)
 WHEN A.C > '1' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,1)+1  ,instr(a.comprates  ,','  ,1  ,2)-(instr(a.comprates ,','  ,1  ,1)+1)) END)  ,0) COL2 
 , NVL((CASE WHEN A.C<=1 THEN '' 
WHEN A.C >= '2' THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,2)+1  ,instr(a.comprates  ,','  ,1  ,3)-(instr(a.comprates ,',' ,1 
 ,2)+1)) END)  ,' ') COL3 
 , NVL((CASE WHEN A.C=0 THEN '' 
WHEN A.C=1 THEN '' WHEN A.C ='2' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,3)+1  ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1 
 ,3)+1) 
WHEN A.C > '2' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,3)+1 
 ,instr(a.comprates  ,','  ,1  ,4)-(instr(a.comprates  ,','  ,1  ,3)+1)) END)  ,0) COL4 
 , NVL((CASE 
WHEN A.C<=2 THEN '' WHEN A.C >= '3' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,4)+1  ,instr(a.comprates  ,','  ,1  ,5)-(instr(a.comprates  ,','  ,1 
 ,4)+1)) END)  ,' ') COL5 
 , NVL((CASE 
WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C ='3' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,5)+1  ,LENGTH(a.comprates)-instr(a.comprates 
 ,','  ,1  ,5)+1) 
WHEN A.C > '3' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,5)+1  ,instr(a.comprates  ,','  ,1  ,6)-(instr(a.comprates  ,','  ,1  ,5)+1)) END)  ,0) COL6 
 , NVL((CASE WHEN A.C<=3 THEN '' 
WHEN A.C >= '4' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,6)+1  ,instr(a.comprates  ,','  ,1  ,7)-(instr(a.comprates  ,','  ,1 
 ,6)+1)) END)  ,' ') COL7 
 , NVL((CASE WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C=3 THEN '' WHEN A.C ='4' THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,7)+1  ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1  ,7)+1) 
WHEN A.C > '4' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,7)+1  ,instr(a.comprates 
 ,','  ,1  ,8)-(instr(a.comprates  ,','  ,1  ,7)+1)) END)  ,0) COL8 
 , NVL((CASE WHEN A.C<=4 THEN '' WHEN A.C >= '5' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,8)+1  ,instr(a.comprates  ,','  ,1  ,9)-(instr(a.comprates  ,','  ,1  ,8)+1)) END)  ,' ') COL9 
 , NVL((CASE WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C=3 THEN '' WHEN A.C=4 THEN '' WHEN A.C >= '5' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,9)+1  ,9) END)  ,0) COL10 
  FROM ( 
 SELECT COUNT(*) c 
 ,comp.emplid 
 ,comp.effdt 
 ,COMP.EMPL_RCD 
 ,COMP.EFFSEQ 
 , listagg (comp.comp_ratecd||','||comp.comprate 
 , ',') WITHIN 
  GROUP ( 
  ORDER BY comp.comp_ratecd) comprates 
  FROM PS_COMPENSATION COMP 
  , PS_COMP_RATECD_TBL B 
 WHERE COMP.COMP_RATECD= B.COMP_RATECD 
   AND B.EFF_STATUS = 'A' 
   AND B.COMP_BASE_PAY_SW ='N' 
   AND B.EFFDT=( 
 SELECT MAX(B1.EFFDT) 
  FROM PS_COMP_RATECD_TBL B1 
 WHERE B.COMP_RATECD=B1.COMP_RATECD 
   AND b1.effdt <=COMP.EFFDT) 
  GROUP BY comp.emplid,comp.effdt,COMP.EMPL_RCD,COMP.EFFSEQ) A, PS_xx_stage_tbl STG 
 WHERE a.emplid=STG.EMPLID 
   AND a.EFFDT=STG.EFFDT 
   AND A.empl_rcd = STG.empl_rcd 
   AND A.effseq = STG.effseq  

Generating xls file in PeopleSoft using OLE Automation

Generating xls or formatted excel file is tedious with SQR.

But we can generate them using Peoplecode with OLE Automation.

We need to create a Excel object and then we can use almost all VBA commands in PeopleCode with Peoplesoft notations, Remember vbconstants like vbblue will not be used here.

Also Make sure you are running this code in windows server with excel installed.

Local object &workApp;Local object &workBook;
Local object &workSheet1, &workSheet2, &workSheet3, &workSheet4;
try
 &workApp = CreateObject("COM", "Excel.Application"); 
 ObjectSetProperty(&workApp, "Visible", False);
 &workBook = &workApp.WorkBooks.Add(); 
 &workSheet1 = &workBook.WorkSheets(1); 
 &workSheet2 = &workBook.WorkSheets(2);  
 &workSheet3 = &workBook.WorkSheets(3); 
 &workSheet3.activate();  
 &workSheet4 = &workBook.Worksheets.Add(4);

 Rem - Name worksheets   
 &workSheet1.name = "Hires";  
 &workSheet2.name = "Terminations"; 
 &workSheet3.name = "Contract Changes"; 
 &workSheet4.name = "New";   

 Rem - Assign values in different worksheets  
 &workSheet1.Cells(1, 1).Value = "Sample1";  
 &workSheet1.Cells(1, 2).Value = "Heading2";    
 &workSheet1.rows(1).Font.Bold = True;  
 &workSheet1.rows(1).Font.colorindex = 5;    
 &workSheet1.rows(1).Interior.colorindex = 3;   
 &workSheet1.rows(1).Font.size = 8;

 &workSheet2.Cells(1, 1).Value = "Sample2 for big text";  
 &workSheet2.Cells(1, 2).Value = "Sample2 for big text, even big";  
 &workSheet2.rows(1).Font.size = 10; 
 &workSheet2.rows(1).Font.Name = "Verdana";  
 &workSheet2.Columns.AutoFit(); 
 &workSheet3.Cells(1, 1).Value = "Sample3"; 
 &workSheet4.Cells(1, 1).Value = "Sample4"; 
 SQLExec("select PRCSOUTPUTDIR from psprcsparms where prcsinstance= 
 (select max(prcsinstance) from psprcsrqst where prcsname='AENAME')", 
 &path); 
 &sFileName = &path | "\ExportData2Excel.xls";   
 &workApp.ActiveWorkBook.SaveAs(&sFileName, 18);
 &workApp.Quit();  
 MessageBox(0, "", 0, 0, "Information has been exported to an Excel file """ |  
 &sFileName | """.");  catch Exception &exp  
throw
 CreateException(1000, 22407, "Error saving MS Excel file." | &exp);
end-try;

Process Groups in PeopleSoft

The process groups are not stored in any setup table of its own.
They are stored in the table: PRCSDEFNGRP and PRCSJOBGRP where we store the process and job definitions.
If you take a look a look at the prompt for PRCSGRP field in those tables, it is a 'Prompt with no edit', so u can keep adding new Process groups on the fly, on process and job definition components (Page: Process Definition Options)

Significance of the process group:

They are linked with the user ID through a permission list. 

On the permission list setup, in the "Process tab", you can link the process groups.
This plays very important role in securing the processes and jobs in the system. Process groups to users are tied as below

Process Group ->Permission list -> Roles->User profiles

To find which permission lists have access to which process groups use the following query:

select * from PSAUTHPRCS order by CLASSID, PRCSGRP;

Below screens will show how the new process group is created and mapped through user profile.