Search This Blog
Convert Tab file to CSV and Load
If you got a tab limited file and want to read it as a CSV file, using the file layout, use the below code snippet.
Local File &TABFILE, &CSVFILE;
Local string &FILE_NAME, &DATA, &NEWDATA;
&FILE_NAME = "Test.txt";
&TAB = Char(9);
&TABFILE = GetFile(&FILE_NAME, "r");
&FileName = &TABFILE.Name;
&POS = Find(".", &FileName);
&NEWFILE_NAME = Substring(&FileName, 1, &POS) | "dat";
&CSVFILE = GetFile(&NEWFILE_NAME, "N", %FilePath_Absolute);
If &TABFILE.IsOpen And
&CSVFILE.IsOpen Then
While &TABFILE.ReadLine(&DATA);
&NEWDATA = Substitute(&DATA, &TAB, ",");
&CSVFILE.WriteLine(&NEWDATA);
End-While;
&TABFILE.Close();
&CSVFILE.Close();
End-If;
Will be handy...
Email Address Validation Function
Local string &Email_After@, &str_Host, &local_part, &Domain_part;
Local number &Loc@, &Loc_dot, &next_dot, &local_LENGTH, &Domain_length, &LOOP;
Local string &ALPHA_STRING = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz.-";
Local string &char_String = "#$%&'*+/=?^_`{|}~";
Local string &NUM_STRING = "1234567890";
&Email_Addr = RTrim(LTrim(&Email_Addr));
If All(&Email_Addr) Then
&Loc@ = Find("@", &Email_Addr);
&Loc_dot = Find(".", &Email_Addr);
&local_part = Substring(&Email_Addr, 1, &Loc@ - 1);
&Domain_part = Substring(&Email_Addr, &Loc@ + 1, Len(&Email_Addr) - &Loc@);
&local_LENGTH = Len(&local_part);
&Domain_length = Len(&Domain_part);
&Email_After@ = Substring(&Email_Addr, &Loc@ + 1, Len(&Email_Addr) - &Loc@);
/* The e-mail address must contain @ and . characters */
If &Loc@ = 0 Or
&Loc_dot = 0 Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 895, "The e-mail address must contain @ and . characters");
End-If;
/* The e-mail address shall not contain @ or . at the first character. */
If &Loc@ = 1 Or
&Loc_dot = 1 Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 896, "The e-mail address shall not contain @ or . at the first character.");
End-If;
/* Two at signs (@) are not allowed */
If (Find("@", &Email_After@) > 0) Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 899, "Two at signs (@) are not allowed");
End-If;
/* The e-mail address shall not contain - as the first character in Domain name. */
If (Find("-", &Email_After@) = 1) Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 900, "The e-mail address shall not contain - as the first character in Domain name.");
End-If;
/* The e-mail address shall contain atleast one character between @ and . */
If ((Substring(&Email_Addr, &Loc@ - 1, 1) = ".") Or
(Substring(&Email_Addr, &Loc@ + 1, 1) = ".")) Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 901, "The e-mail address shall contain atleast one character between @ and .");
End-If;
/* The e-mail address shall not contain @ or . at the last character. */
If ((Substring(&Email_Addr, Len(&Email_Addr), 1) = ".") Or
(Substring(&Email_Addr, Len(&Email_Addr), 1) = "@")) Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 897, "The e-mail address shall not contain @ or . at the last character.");
End-If;
If (&Loc_dot > 1) Then
/* */
&str_Host = Substring(&Email_Addr, &Loc_dot + 1, Len(&Email_Addr) - &Loc_dot);
&next_dot = Find(".", &str_Host);
While &next_dot > 0
If &next_dot = 1 Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 902, "The e-mail address shall contain atleast one character between . and .");
Break;
Else
&Loc_dot = &next_dot;
&Email_After@ = &str_Host;
&str_Host = Substring(&str_Host, &next_dot + 1, Len(&str_Host) - &next_dot);
&next_dot = Find(".", &str_Host);
End-If;
End-While;
If Len(&str_Host) < 2 Then SetCursorPos(%Page, @(&Page_Field)); Error MsgGet(14100, 903, "The e-mail address should contain atleast two characters after last ."); End-If; End-If; /* Validate that the local part consists of the ascii characters are described above for local part */ &LOOP = 1; While &LOOP <= &local_LENGTH /* check for alpha in the format */ If ((Find(Substring(&local_part, &LOOP, 1), &ALPHA_STRING) = 0) And (Find(Substring(&local_part, &LOOP, 1), &NUM_STRING) = 0) And (Find(Substring(&local_part, &LOOP, 1), &char_String) = 0)) Then SetCursorPos(%Page, @(&Page_Field)); Warning MsgGet(14100, 904, "Invalid character found in local part of email address."); Break; End-If; &LOOP = &LOOP + 1; End-While; /* Validate that domain name consists letters, digits and hyphens separated by dots */ &LOOP = 1; While &LOOP <= &Domain_length /* check for alpha in the format */ If ((Find(Substring(&Domain_part, &LOOP, 1), &ALPHA_STRING) = 0) And (Find(Substring(&Domain_part, &LOOP, 1), &NUM_STRING) = 0)) Then Warning MsgGet(14100, 905, "Invalid character found in domain part of email address."); Break; End-If; &LOOP = &LOOP + 1; End-While; /* Validate that the local part consists of at least 1 character before the @ sign but not more than 64 characters*/ If &local_LENGTH < 1 Or &local_LENGTH > 64 Then
SetCursorPos(%Page, @(&Page_Field));
Warning MsgGet(14100, 894, "Length of Local part of e-mail address before the @ sign must be >=1 and <= 64.");
End-If;
/* Make sure that atleast two characters are present between @ and . */
&Email_After@ = Substring(&Email_Addr, &Loc@ + 1, Len(&Email_Addr) - &Loc@);
&Loc_dot = Find(".", &Email_After@);
If (&Loc_dot = 0) Then
SetCursorPos(%Page, @(&Page_Field));
Error MsgGet(14100, 895, "The e-mail address must contain @ and . characters");
End-If;
End-If;
End-Function;
Auto Increment: Best practice
If you have a counter setup and wanted to increment the counter manually in the code, rather than writing a sqlexec increment step you can use this delivered functions: GetNextNumber,GetNextNumberWithGaps and GetNextNumberWithGapsCommit.
GetNextNumber:
Use the GetNextNumber function to increment the value in a record for the field you specify by one and returns that value. You might use this function to increment an employee ID field by one when you are adding a new employee. If the new value generated exceeds max_number, a negative value is returned and the field value isn't incremented.
GetNextNumberWithGaps:
Use the GetNextNumberWithGaps function to determine the highest value in a table for the field you specify, and return that value plus increment
GetNextNumberWithGapsCommit:
Use the GetNextNumberWithGapsCommit function to return the sequence number value plus increment for the given field residing in the given record. This function also enables you to specify a SQL Where clause as part of the function for maintaining multiple sequence numbers in a single record.
This function is typically used for obtaining a new sequence number for the application, for example, getting a new Purchase Order number to be used in the application transaction.
Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.
Context Sensitive Help
The Strings Table

Find Object Navigation
PeopleSoft Ping
Dynamic Page Title
Where is it?
The PeopleCode is stored in PSPCMPROG, which has all the PC objects details. If you want to quickly check the existance of your application engine program codes, the issue the SQL against this table.
SELECT * FROM PSPCMPROG WHERE OBJECTVALUE7 LIKE 'OnExecute%'
PSSQLTEXTDEFN: Contains all SQL objects in the database.
SELECT * FROM PSSQLTEXTDEFN WHERE SQLID like '%Step%'
How to print bar codes and micr fonts using XML Publisher
Click this link to view
SignOn PeopleCode
SetAuthenticationResult
Using Find function
&Char_Pos=Find("#",&CourseID);
If &Char_Pos > 0 then
&Course_ID=Upper(Substring(&CourseID,1,&Char_Pos-1));
End-If;
Using GUID
Local string &guid;
SQLExec("SELECT RAWTOHEX(SYS_GUID()) FROM PS_INSTALLATION", &guid);
MessageBox(0, "", 0, 0, "GUID from DB: " | &guid);
Delaying Execution using PeopleCode
GetJavaClass("java.lang.Thread").sleep(&sleepSeconds * 1000);
Tree mover tables
PSTREEDEFN
PSTREENODE
PSTREELEAF
PSTREESTRCT
PSTREELEVEL
PS_TREE_LEVEL_TBL
PS_TREE_NODE_TBL
TreeMover also reads the PSSTATUS table during the tree export process, to identify the version of the tree data exported.
Creating XML's with SOAP Envelop using IB
Oracle Blog: IB Out Bound Soap Messages with WS-Security
Using PeopleSoft SOAP Classes
If anyone has a pre-experience on the same, you can share the code snippets/article here.
Creating Chart using PeopleCode
For those who haven't tried creating a chart using peoplecode. I do not have the code which I did while I was working with my first company. But this example below from peoplebooks will give you an idea. You can create great charts using pagelet wizard also , but when the performance part is considered, I will recommend the usage of chart class.
The below chart is created from a record with three fields, relating to sales, month, and product. The series in the chart are based on the product. The following is the complete code sample.
Global Chart &oChart;
&oChart = GetChart(QE_CHART_DUMREC.QE_CHART_FIELD);
&oChart.SetData(Record.QE_CHART_RECORD);
&oChart.SetDataYAxis(QE_CHART_RECORD.QE_CHART_SALES);
&oChart.SetDataXAxis(QE_CHART_RECORD.QE_CHART_PRODUCT);
&oChart.SetDataSeries(QE_CHART_RECORD.QE_CHART_REGION);
&oChart.Type = %ChartType_2DStackedBar;
&oChart.HasLegend = True;
&oChart.LegendPosition = %ChartLegend_Right;
&oChart.XAxisLabelOrient = %ChartText_Vertical;
To create a chart:
- Open PeopleSoft Application Designer.
- Open the page where the chart is to be inserted.
- Insert the chart control by either:
- Clicking on the chart icon in the toolbar, or
- Selecting Insert, Chart
- Draw the chart control on the page.
- 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.
- 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.
- Write your PeopleCode.
In some event on the page, such as Activate, put the PeopleCode you need for populating the chart.
- 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);
- 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.
- (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);
- (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;
- (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;
Access app engine section through peoplecode
Local AESection &Section;
&Section = GetAESection("RULES", "DYN_SECT");
/* Open the base section */
&Section.SetTemplate("MY_APPL", "TEMPLATE");
/* Set the template section */
&Section.AddStep("NewStep2");
/* Insert NewStep2 */
/* Do some SQL stuff here */
&Section.SetSQL("DO_SELECT", &MySql);
/* Modify the SQL in the added step */
&Section.Save();
&Section.Close();
/* Save and close */
The SetSQL method replaces the SQL associated with the given action type in the current step in the base section with the SQL in string.






