Search This Blog

Showing posts with label code. Show all posts
Showing posts with label code. Show all posts

Convert Tab file to CSV and Load

Just a tip for the day.

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

Did some changes in the delivered code. Page fields were hard coded in the delivered code, made it dynamic.

Function validateEmailAddressEntry(&Email_Addr As string, &Page_Field As string)

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;

>> Tino Simon

Auto Increment: Best practice

I came across this function recently and found it quite useful and a best coding 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



If you are not able to view the document in the embed form above, download it by clicking on the PDF icon below

The Strings Table

I have seen this in a couple of SQR reports and this will be a good tip for the developers.

What is this Strings Table?

The PeopleTools Strings table (STRINGS_TBL) stores textual strings used for language sensitive labels and other text in PS/nVision and SQRs to avoid hard-coding labels into the report files themselves. The use of strings rather than hard-coded text in reports enables translators to translate the report layout in the database without editing the report’s code itself.This enables you to run a single copy of a report in multiple languages, while avoiding the duplication of code and report logic.Each row in the Strings table keys each string to a STRING_ID, which is associated with one of two different string types:

• The short (RFT Short) or long (RFT Long) field description of the field.

The STRING_ID must equal the field name.

• A free-form text string.

In either case, when a report requests a string in a particular language, the system returns the translated string if a translation is available. If no translation exists, the base language string is returned.

The Strings table is also keyed by a PROGRAM_ID field, which enables you to classify strings into groups that are used in similar reports. PROGRAM_ID can refer to a specific SQR or PS/nVision report name, or it can be a mnemonic for a group of common strings that are shared between reports

Click the picture to expand

Find Object Navigation

Might be a good handy tip. Those who knows this feature already please excuse. Searching the navigation is easy with this feature. try it out.


Click the picture to expand

PeopleSoft Ping

Just another good weekend. Expected some more work today, however it did not happen. Then I thought of just playing around the instance and saw this utility. Never seen or heard before. However it seemed interesting and gave a search in PeopleBooks. The details are given below. Hope this might be a new information for you too. For those who have worked with this utility; please feel free to comment.

The PeopleSoft Ping utility collects timestamps by sending a specific page to different tiers of the PeopleSoft system, starting at the browser, then going to the web server, the application server, the database and back. The timestamps that are collected are total time elapsed for the round trip, and arrival and departure time at each of the tiers.

To use the PeopleSoft Ping feature, select PeopleTools, Utilities, PeopleSoft Ping.

Nice weekend!

Dynamic Page Title

One of my colleagues had a small requirement to change the page title dynamically. The example is the RUNCTL_ASOFDATE page in HRMS.



















Where is it?

This might be a duplicate information for many, but I believe some one is going to get benefitted from this once again.

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

For those friends who havnt done the font setup for XML Publisher. I stumbled on this article while browsing through Oracle Support Site.You need to have an Oracle Support login to view the article.

Click this link to view

SignOn PeopleCode

Consider there is a requirement to warn the users while they login and before they land on the peoplesoft home page. How can we achieve this?

When this question came to me I was not able to understand how we can recognize the user who logs in through the login page. The user can be an employee, he can be a guest. But before landing the homepage how is it possible to check the role attached to the user? That was a challenge indeed at first.

SOPC - Sign on People code was one way for implementing the same. This people code will get fired upon the login to any PIA environment.

How to relate the people code we write to the login session. This can be achieved through: PeopleTools >> Security >> Security Objects >> Sign on people code

You can provide the record name, the field and the event on which the people code is written. Then use the checkbox to control when the code need to be fired.

Now consider, you need to show the user a page based on how he is verified. For this, you have to use the page under: Web profile >> Web profile configuration >> Look & Feel

Use the Password section to cater the above requirement. In the password warning page you can provide your own HTML page which you have created with the necessary warning for the user.

And use the SetAuthenticationResult function to do the rest of the work.

SetAuthenticationResult

This is one function I stumbled upon last week. I gave a search in people books and tried to understand how and in which context it works the best.

Came to know that it works with the PeopleSoft Sign on related area and provided the developer with robust ways of administering the security perspective beyond what’s delivered with the product
Syantax for the function is given below:

SetAuthenticationResult(AuthResult [, UserId] [,ResultDocument] [,PasswordExpired] [DaysLeftBeforeExpire])

When PasswordExpired is True, it indicates the password is expired, the passwordexpired.html page is displayed during login when signon PeopleCode is enabled.

When DaysLeftBeforeExpire is greater than 0, and PasswordExpired is False, indicating that the password will expire in x days, the passwordwarning.html page is displayed during login when signon PeopleCode is enabled.

Using Find function

The requirement was to remove the # field embedded at the first position of the Course ID Field which comes from the legacy and use the rest of the numbers into the peoplesoft system. The code is given below:

&Char_Pos=Find("#",&CourseID);
If &Char_Pos > 0 then
&Course_ID=Upper(Substring(&CourseID,1,&Char_Pos-1));
End-If;


Arvind

Using GUID

One instance of GUID usage I noticed is while implementing parallel processing in Application engine. You require to use unique run control ID's in this case. This can be achieved using creating unique GUID's

The unlucky part is that the peoplebooks doesn't have any documentation for the same. But my colleague pointed out a function for the GUID creation.

&nbr_GUID = UuidGen();

This can be assigned as the runcontrol parameters.

While gave a search on google to know more about the same, I stubled upon Jim's blog which gave more light on the same.

Local string &guid;
SQLExec("SELECT RAWTOHEX(SYS_GUID()) FROM PS_INSTALLATION", &guid);
MessageBox(0, "", 0, 0, "GUID from DB: " | &guid);

and GetJavaClass("java.util.UUID").randomUUID().toString();

Delaying Execution using PeopleCode

If you want to delay the execution through PeopleCode, try using this function. One example was when there was a requirement to delay the row insert interval. Another one, if to delay the process execution until the group parallel process need to be finished.

GetJavaClass("java.lang.Thread").sleep(&sleepSeconds * 1000);

Tree mover tables

TreeMover uses the following PeopleTools system tables for trees during both the tree import and tree export processes:

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

I was actually facing this requirement for the first time, when one of my colleague emailed asking for how to accomplish the same. PeopleBooks has clue how to do it, but i found a couple of links which can help further on the same.

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:

  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.
  1. 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.

  1. Write your PeopleCode.

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

  1. 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);

  1. 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.

  1. (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);

  1. (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;

  1. (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

I found this piece of code interesting as I didnt have the clue that there was an option to touch the app engine through a code wrapper.

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.