Validating Email Address in Peoplecode

We have a number of pages in PeopleSoft, where user/admin is free to enter email addresses. How to validate if a value entered in a PeopleSoft field is a valid email address?

In general, how does an email id look like? It will have few characters followed by @ symbol followed by few characters and then a .(dot) symbol followed by few more characters. So, we have to validate the presence of these.

For example, if &email_addr is a variable which contains the string entered in email address field and needs to be validated then try the below code:

&AT = Find("@", &email_addr);

&DOT = Find(".", &email_addr);

If ALL(%AT,&DOT) Then

/* Validation Success */

Else

/* Validation Fail*/

/* Issue Error message */

End-if;

Additionally we could also perform checks to ensure that:

• the position of @ is not the first character
• .(dot) is not the last character.
• @ is not repeated else where in the string
• Dot is not repeated else where in the string

This can be done by using instr, substr and len functions.

HAPPY LEARNING :)

Custom Run Control Page

Creating a Custom Run Control Page

This step is the same regardless of whether you will ultimately use the Run Control page with an SQR or App Engine program.  Do the following in Application Designer:

Creating the Run Control Record

1. Open Record definition PRCSRUNCNTL and make a copy by using "Save As" and giving the Record a new name (for example, "MY_PRCS_RC").
• When prompted to save a copy of the PeopleCode associated with PRCSRUNCNTL, choose "Yes".
2. Update the following Record PeopleCode definitions on your new Run Control Record, replacing references to PRCSRUNCNTL with your custom Run Control Record name:
• OPRID.RowInit
• RUN_CNTL_ID.SaveEdit
• LANGUAGE_CD.RowInit
• LANGUAGE_OPTION.FieldChange
3. Add any desired additional fields to your custom Run Control Record (for example, "STRM").
4. Build the Record.

Creating the Run Control Page

1. Create a new Page definition and save it with a new name (for example, "MY_PRCS_RC").
2. Insert a Subpage onto your page, and choose "PRCSRUNCNTL_SBP".
• On the Insert Subpage dialog, change the "Subpage Record Name Substitution" value to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Drag your custom Run Control fields onto the Page (i.e., "STRM").
• Note:  You do not need to add the default Run Control fields onto the page (OPRID, RUN_CNTL_ID, etc).  These values will be populated automatically through PeopleCode.

Creating the Run Control Component

1. Create a new Component definition.
2. Set the Component Search Record to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Save the Component with a new name (for example, "My PRCS_RC").
4. Register your Component using the Registration Wizard (Tools > Register Component).

Creating the Process Definition

In this step, we create a Process definition entry for our SQR or App Engine program, and associate the process definition with our custom Run Control component.

1. In the PIA, navigate to:  PeopleTools, Process Scheduler, Processes
2. Click the "Add a New Value" tab and enter the Process Type and Process Name.
• Note:  The Process Name must exactly match the name of your App Engine program, or in the case of an SQR, must exactly match the file name of your SQR file (without the ".sqr" extension suffix).
3. On the "Process Definition Options" tab:
1. Add your custom Run Control Component under the "Component" grid area on the bottom left of the page.
2. Add whichever Process Groups are appropriate to grant process security to the appropriate persons.

You should now be able to navigate to your Run Control page in the PIA, fill out the Run Control parameters, and schedule your process to run on the Process Scheduler.

Retrieving Run Control Parameters

Now that we have a way to provide Run Control parameters via a Run Control page, we need to be able retrieve and use those parameters from within a program.  This part of the process is different for App Engine and SQR programs.

Run Control Parameters in App Engine Programs

The standard way to store Run Control parameters in an App Engine program is to use a State Record.  To set up the State Record:

1. A requirement in naming State Records is that they must have "_AET" as their suffix.  Make a copy of your Run Control record, giving it a new name (for example, "MY_PRCS_AET").
1. When prompted to save a copy of the PeopleCode associated with the original Record, choose "No".
2. Change the Record Type from "SQL Table" to "Derived/Work".  A Derived/Work Record doesn't persist any data to the database.  Instead, it acts as an in-memory data structure while the program is running on the Process Scheduler.
3. Open your App Engine program, and navigate to: File, Definition Properties
4. On the "State Records" tab search for your State Record definition, and click the "Add" button to move the definition from the left-hand panel to the right-hand panel.

Now that the State Record is created and associated with the App Engine program, we need to populate it.

1. Add an SQL step as the first step in the App Engine program.  To populate the State Record with values from the Run Control Record, we use the Meta-SQL %Select function.  For example:
   
   %Select(OPRID, RUN_CNTL_ID, LANGUAGE_CD, LANGUAGE_OPTION, STRM)
   FROM PS_MY_PRCS_RC
   WHERE OPRID = %OperatorId
   AND RUN_CNTL_ID = %RunControl

Now that the Run Control parameters have been stored into the State Record, they can be referenced from PeopleCode via the State Record.  For example, the following would write the STRM Run Control parameter value to the message log:

MessageBox(0, "", 0 , 0, "STRM=" | MY_PRCS_AET.STRM);

HAPPY LEARNING :)

SQLExec function

Description:

Use the SQLExec function to execute a SQL command from within a PeopleCode program by passing a SQL command string. The SQL command bypasses the Component Processor and interacts with the database server directly. If you want to delete, insert, or update a single record, use the corresponding PeopleCode record object method.
If you want to delete, insert, or update a series of records, all of the same type, use the CreateSQL or GetSQL functions, then the Execute SQL class method.

Limitation of SQLExec SELECT Statement:

SQLExec can only Select a single row of data. If your SQL statement (or your SQL.sqlname statement) retrieves more than one row of data, SQLExec sends only the first row to its output variables. Any subsequent rows are discarded. This means if you want to fetch only a single row, SQLExec can perform better than the other SQL functions, because only a single row is fetched. If you need to SELECT multiple rows of data, use the CreateSQL or GetSQL functions and the Fetch SQL class method. You can also use ScrollSelect or one of the Select methods on a rowset object to read rows into a (usually hidden) work scroll.

SQLExec statements that result in a database update (specifically, UPDATE, INSERT, and DELETE) can only be issued in the following events:

• SavePreChange
  
• WorkFlow
  
• SavePostChange
  
• FieldChange 
  
  
  Syntax:
  SQLExec("Select sum(posted_total_amt) from PS_LEDGER 
  where deptid between :1 and :2", DEPTID_FROM, DEPTID_TO, &SUM);

  :1 is replaced by the value contained in the record field DEPTID_FROM;

  :2 is replaced by the value contained in the record field DEPTID_TO. 

  &SUM will be assigned value of sum(posted_total_amt) selected.
   

  HAPPY LEARNING :)