PeopleSoft : Phone Number Validation Using Regular Expressions in PeopleSoft

Local JavaObject &oPhoneNumExpression = CreateJavaObject("java.lang.String", "\+{0,1}[0-9\s\-\(\)]+");
Local JavaObject &oPhoneNum = CreateJavaObject("java.lang.String", &PhoneValue);
If &oPhoneNum.matches(&oPhoneNumExpression) = False Then
   MessageBox(0, "", 0, 0, "Enter valid phone number");
End-If;

Regular Expression "\+{0,1}[0-9\s\-\(\)]+"   : It allows a number + in starting and - in the middle of the number.
&PhoneValue : Contains the entered phone number value.

PeopleSoft : Reading excel file using jxl in peoplesoft

Reading Excel using Java:

Step1: Create a java class to read excel and compile and place the class file in /app/psoft/psadmin/appserv/classes  .

/* Java class to read excel  file */

import java.io.*;

import jxl.*;

public class ReadExcelFile {

public static void main(String[] args){

System.out.println("Hi");

}

public Workbook Readworkbook(String Filename){

try {

Workbook ReadExcel = Workbook.getWorkbook(new File(Filename));

return ReadExcel;

}

catch (Exception i)

        {

            System.out.println(i);

            return null;

        }

}

}

Step 2: Use the below code to read data

Local string &strGetEnv = GetEnv("PS_HOME");

Local string &St_path_l = &strGetEnv | "/user/" | %DbName | "/ABC.xls";

Local JavaObject &Obj_class_l = CreateJavaObject("nExcel");

Local JavaObject &Obj_getworkbook_l = &Obj_class_l.readWorkbook(&St_path_l);

&rows = &Obj_getworkbook_l.getSheet(0).getRows();

&cols = &Obj_getworkbook_l.getSheet(0).getColumns();

WinMessage("No of rows:" | &rows | " No of columns:" | &cols, 0);

Local string &St_returnvalue_l;

For &i = 1 To &rows - 1

   For &j = 0 To &cols - 1;

      &St_returnvalue_l = &Obj_getworkbook_l.getSheet(0).getCell(&j, &i).getContents();

      MessageBox(0, "", 0, 0, "" | &St_returnvalue_l);

   End-For;

End-For;

&Obj_getworkbook_l.close();

PeopleSoft : Random Password Generation

Local string &a;
&j = Int(Rand() * 25) + 65;
&a = &a | Char(&j);
&j = Int(Rand() * 35) + 97;
&a = &a | Char(&j);
&j = Int(Rand() * 9) + 48;
&a = &a | Char(&j);
&j = Int(Rand() * 12) + 33;
&a = &a | Char(&j);
&j = Int(Rand() * 9) + 48;
&a = &a | Char(&j);

&j = Int(Rand() * 35) + 97;
&a = &a | Char(&j);
&j = Int(Rand() * 25) + 65;
&a = &a | Char(&j);

WinMessage(&a);

PeopleSoft : Add drop down list in order.

 For &j = 1 To &Rs.ActiveRowCount
   &value = &Rs(&j).CH_RATING_TBL.CH_RATING.Value;
   &descr = &Rs(&j).CH_RATING_TBL.DESCR.Value;

   N_JOBSKILL1_TBL.CH_PROFLEVEL.AddDropDownItem(&value, Rept(Char(9), &j) | &descr);
End-For;

&j should be activerowcount -1

the translate value which u want first in the list should be appended with activerowcount number of char(9)

PeopleSoft Charts

To create a chart:

1. Open PeopleSoft Application Designer.
2. Open the page where the chart is to be inserted.
3. Insert the chart control by either:

·                     Clicking on the chart icon in the toolbar, or

·                     Selecting Insert, Chart

4. Draw the chart control on the page.
5. Associate the chart control with a record field

Every chart control must be associated with a record field. This is just the field for the chart control. It is not the field used for drilling down for data in the chart.

Bring up the chart control properties by either

·                     Double-clicking on the chart control, or

·                     Right-clicking on the chart control and selecting Page Field Properties.

6. Select the record name and field for the chart.

On the Record tab of the chart control properties, select the record and field for the chart.

To make the control a page anchor, select the Enable as Page Anchor on the General tab.

7. Write your PeopleCode.

In some event on the page, such as Activate, put the PeopleCode you need for populating the chart.

8. Get the chart.

The first thing you must do is get the chart. You must use the record and field name that you associated with the chart control on the page.

&oChart = GetChart(QE_CHART_DUMREC.QE_CHART_FIELD);

9. Set the data records.

Set the data record. The SetData function associates the record data with the chart. Then use the SetDataYAxis and SetDataXAxis functions to associate specific fields in the record with the Y axis data and the X axis data.

&oChart.SetData(Record.QE_CHART_RECORD);

&oChart.SetDataYAxis(QE_CHART_RECORD.QE_CHART_SALES);

&oChart.SetDataXAxis(QE_CHART_RECORD.QE_CHART_PRODUCT);

This is all the code needed to create a chart. You don’t need to set the chart type: the default is a 2D bar chart. You don't need to set the series unless you want to group your data. Everything else is also optional.

10. (Optional) Set the data series.

In this example, we want to set the region as the series. This means that the data will be grouped according to the region.

&oChart.SetDataSeries(QE_CHART_RECORD.QE_CHART_REGION);

11. (Optional) Set the chart type.

Because we want a stacked bar for the chart, we must set the Type property of the chart. This means that each product (footballs, rackets, and so on) will have a single bar, and the data series (California, Oregon, and so on) will be what is 'stacked'.

&oChart.Type = %ChartType_2DStackedBar;

12. (Optional) Set legend and label attributes.

For this example, we want a legend, and want it to appear in the right side. In addition, because the text of the series labels is so large, they must be vertical to display all of them.

&oChart.HasLegend = True; &oChart.LegendPosition = %ChartLegend_Right;

&oChart.XAxisLabelOrient = %ChartText_Vertical;

Sample code to  prepare chart:

Local Chart &cChart;

Local Rowset &wecntrs;

Local SQL &WSQL;

Local number &i, &empcnt;

Local string &perrating;

/* Get data by using SQL*/

&wecntrs = GetLevel0()(1).GetRowset(Scroll.PERFORM_RATIN);

&WSQL = CreateSQL("select  COUNT(EMPLID),PERFORM_RATING from PS_PERFORM_RATIN group by PERFORM_RATING");

&i = 1;

/* Prepare Data */

While &WSQL.Fetch(&empcnt, &perrating)

   &wecntrs(&i).PERFORM_WRK.EMPL_COUNT.Value = &empcnt;

   &wecntrs(&i).PERFORM_WRK.PERFORM_RATING.Value = &perrating;

   &wecntrs.InsertRow(&i);

   &i = &i + 1;

End-While;

/**Chart properties **/

&cChart = GetChart(PERFORM_WRK.PERFORM_RATING);/* PERFORM_WRK.PERFORM_RATING assingned to chart */

&cChart.HasLegend = True;

&cChart.LegendPosition = %ChartLegend_Right;

&cChart.Type = %ChartType_3DPie;

&cChart.SetData(&wecntrs);  /* &wecntrs  is a rowset ,this contains data. */

&cChart.SetDataXAxis(PERFORM_WRK.PERFORM_RATING);

&cChart.SetDataYAxis(PERFORM_WRK.EMPL_COUNT);

&cChart.IsDrillable = True;  

/* set data hints */

&cChart.SetDataHints(PERFORM_WRK.PERFORM_RATING);

/* set data colors */

&cSliceColors = CreateArray(12, 10, 2, 5);

&cChart.SetColorArray(&cSliceColors);

/* To explode data */

Local array of number &ExplodedArray;

&ExplodedArray = CreateArray(4);

&cChart.SetExplodedSectorsArray(&ExplodedArray);

/* Set axis text properties */

&cChart.XAxisLabelOrient = %ChartText_Horizontal;

&cChart.XAxisTitle = "Performance";

&cChart.YAxisTitle = "Performance Count";

Sample Output:

NOTE:

•      &cChart.IsDrillable = True;  

If set this property as true then y-axis field change will trigger when we click on part of a chart.

PeopleSoft : Creating MSExcel with new sheets through peoplecode:

Local object &oWorkApp, &oWorkBook;

&sFilePath = "C:\temp\TEST_FL.xlsx";

&sFileDestPath = "C:\temp\Test.xlsx";

&oWorkApp = CreateObject("COM", "Excel.Application");

&oWorkApp.DisplayAlerts = "False";

ObjectSetProperty(&oWorkApp, "Visible", True);

&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");

&oWorkBook.Open(&sFilePath);

&oWorkSheet = &oWorkApp.Worksheets("Sheet1");

&oWorkSheet.Range("A1:C5").Font.Bold = True;

&oWorkApp.WorkSheets.Add().Name = "Test";

&oWorkSheet = &oWorkApp.Worksheets("Test");

&oWorkSheet.Range("A1:C5").Font.Bold = True;

&oWorkSheet.Cells(1, 1).Value = "I'm adding stuff to be bolded";

&oWorkSheet.Cells(1, 1).Font.Bold = True;

&oWorkSheet.Cells(1, 1).Font.Size = 24;

&oWorkSheet.Cells(1, 1).Font.ColorIndex = 3;

&oWorkApp.ActiveWorkbook.SaveAs(&sFileDestPath);

&oWorkApp.ActiveWorkBook.Close();

&oWorkApp.DisplayAlerts = "True";

&oWorkApp.Quit();

PeopleSoft : Check whether user belongs to a role:

IsUserInRole(rolename1 [, rolename2]. . .)

Returns a Boolean value: True if the current user belongs to one or more of the roles in the user role array, False otherwise.

Example:

    

     &flag=IsUserInRole("Admin");

PeopleSoft : Passing values while transfer to another page:

&queryString = "&SOURCE=R";

            /* generate a url */

            &url = GenerateComponentRelativeURL(%Portal, %Node, MENUNAME.MenuName, MARKET.marketname,COMPONENT.componentname,PAGE.pagename,action);

           

            /* Append parameters */

            &url = &url | &queryString;

           

            ViewURL(&url, False);

Syntax:

1. 1      GenerateComponentRelativeURL(PORTAL.portalname, NODE.nodename, MENUNAME.menuname, MARKET.marketname, COMPONENT.componentname, PAGE.pagename, action, [, keylist])
2.    ViewURL(URL_str | URL.URL_ID [, NewWindow])
3.    ViewContentURL(URL_str | URL.URL_ID)

Use the ViewContentURL function to launch a new browser window

Use the ViewURL function to launch the default browser

exmple:

&queryString = "&SOURCE=R";

&url=GenerateComponentRelativeURL(%Portal, %Node, MENUNAME.CH_MENU,"GBL",COMPONENT.CH_COMPONENT,PAGE.CH_PAGE,"U");

 &url = &url | &queryString;

           

 ViewURL(&url, False);

PeopleSoft : Check whether user belongs to a role through peoplecode:

IsUserInRole(rolename1 [, rolename2]. . .)

IsUserInRole() function is used to check whether current user has  the role in his profile or not.

This function returns  a Boolean value:
                                       True if the current user belongs to one or more of the roles in the user role array,
                                        False otherwise.

Example:
 local boolean &hasRole;
 &hasRole= IsUserInRole(CH_ROLE_EMPLOYEE);


&hasRole contains true if the currnet user has CH_ROLE_EMPLOYEE in his profile.

PeopleSoft : SQLEXEC () in RowInit PeopleCode

We cannot perform INSERT,UPDATE statement's through SQLEXEC( ) in Rowinit PeopleCode event.

Below is the alternative to perform insertion and updation in rowinit .

Creating a stroed procedure, that perform's the insertion or updation.
Execute the procedure using SQLEXEC( ) in rowinit.

Example:

1.Creating the stored procedures in Oracle

CREATE OR REPLACE PROCEDURE CH_SAMPLE_PROCEDURE
AS 
BEGIN
    UPDATE PS_CH_TEST_TBL
            SET  COUNT=100;
    COMMIT;
END CH_SAMPLE_PROCEDURE;

2.Execute the procedure
 
EXECUTE CH_SAMPLE_PROCEDURE;

Sample code in ROWINIT:

1.Create a sql object,that contains the sql for creating stored procedure.
    SQLEXEC(SQL.CH_TESTPROCEDURE_SQL);
   Here CH_TESTPROCEDURE_SQL contains a sql for creating stored procedure.
2.Execute procedure.
    SQLEXEC("EXECUTE CH_SAMPLE_PROCEDURE");

Note:
1. To create a stored procedure , you need to have permissions in database level.
2. If you want to check whether the stored procedure is created or not use the below sql.
SELECT * FROM ALL_PROCEDURES WHERE OBJECT_NAME='CH_SAMPLE_PROCEDURE'

PeopleSoft : Preventing user from making changes to the field

Gray function:
   
      Gray( ) is used to make the field unavailable to change in page.
 
      Syntax:
                 Gray(scrollpath,targetrow,Record.Fieldname);

      Example:
                 Gray(Record.CH_EMPSTATE, &i, CH_EMPSTATE.STATE);

PeopleSoft : Add Drop-down values through peoplecode

/* Get the field reference into &field variable */

&field = GetRecord(Record.CH_EMPTYPE).GetField(Field.CH_EMPMAIN);

/* Delete all the values from dropdown list */
&field.cleardropdownlist();

/* Add values to the dropdown list */
&field.adddropdownitem(Descr, Value);
   [Value]: It is actual value which is mapped to the dropdown item
   [Descr]: It is actual text which is visible as a dropdown item
Examples:
&field.adddropdownitem("A", "A");
&field.adddropdownitem("B", "B");
&field.adddropdownitem("C", "C");

PeopleSoft : Handling files in PeopleCode

/*********** To move file data***************/

Exec("cmd /c move " | &file1 | " " | &file2, %Exec_Asynchronous %FilePath_Absolute);

/*********** To copy file data***************/
Exec("cmd /c copy " | &file1 | " " | &file2, %Exec_Asynchronous %FilePath_Absolute);

/*********** To delete the file**************/
Exec("cmd /c del " | &file1, %Exec_Asynchronous %FilePath_Absolute);

***********************************************************OR**********************************************************  

/*********** Create java objects corresponds to files ****************************/                           

Local JavaObject &f = CreateJavaObject("java.io.File", "/the/path/to/the/file.txt"); 
Local JavaObject &target = CreateJavaObject("java.io.File", "/the/target/file.txt"); 

&f.renameTo(&target); 

Local JavaObject &f = CreateJavaObject("java.io.File", "/the/path/to/the/file"); 

&f.delete(); /* delete the file from source */

PeopleSoft : PeopleCode for disable the row delete option in scroll and grid

For &i = 1 To &Asmfloor.ActiveRowCount
   &Asmwing = &Asmfloor(&i).GetRowset(Scroll.ASM_WING_TBL);

   For &j = 1 To &Asmwing.ActiveRowCount
      &Asmcabin = &Asmwing(&j).GetRowset(Scroll.ASM_CABIN_TBL);
   
      If &Asmcabin.ActiveRowCount = 1 Then
         &Asmwing.DeleteEnabled = True; /* to enable the row delete option */

         If &Asmwing.ActiveRowCount = 1 Then
            &Asmfloor.DeleteEnabled = True;
         Else
            &Asmfloor.DeleteEnabled = False; /* to disable the row delete option */
         End-If;
         rem &Asmfloor.DeleteEnabled = True;

      Else
         &Asmwing.DeleteEnabled = False;
         &Asmfloor.DeleteEnabled = False;
       
      End-If;
   
   End-For; /* end-of wing */
             
End-For; /* end-of floor */

PeopleSoft : PeopleCode to find special characters in file other than ASCII characters

Local string &File_path, &File_path1;

&File_path = "C:\temp\ascii.xml";
&bud_rep = GetFile(&File_path, "r", %FilePath_Absolute);
&File_path1 = "C:\temp\ascii_1.txt";
&bud_rep1 = GetFile(&File_path1, "a", %FilePath_Absolute);
&count = 0;

While &bud_rep.readline(&line)
   &line1 = Len(&line);
   &count = &count + 1;

   For &i = 1 To &line1
      &char = Substring(&line, &i, 1);
      &naciichar = Code(&char); /* returns the number corresponds to character */
      /* check whether the character is in the range of ascii char's or not */
      If &naciichar >= 128 Or
            &naciichar < 0 Then
         &bud_rep1.writeline(&count | " " | &char); /* write special character to file */
      End-If;

   End-For;

End-While;