Dynamic view in PeopleSoft

Views are a useful feature of SQL databases, letting us create virtual tables based on SQL select statements.
PeopleSoft 8 provides the functionality to create dynamic views. These are essentially SQL statements executed on the fly by the PeopleSoft component processor. We can use dynamic views in Peoplesoft pages only because they are PeopleTools objects, not SQL Objects.

Using Dynamic view as Prompt Table

In this article, we will see the use of Dynamic view as a prompt table. Please note that a dynamic view can be used for other purposes such as search records which will not be discussed in the article.

One major question that pops up to many of us the first time we use a dynamic view, why not use a normal view instead of a dynamic view. A dynamic view’s select statement may include Peoplesoft’s meta-SQL, and it may be replaced by a different SQL statement while the user is using the page. And also there might be situations where which you do not save the view in the database.

Using Edit Table for prompt tables

Assume that you have a page in which the user selects a country and depending on the selection you want a different prompt table for the states. For example, if the user selects US show states for US and user selects Canada show states of Canada.

This can be achieved by following the steps below

Step 1: Create two views/dynamic views to show the states of the US and Canada.
Step 2: Go to record field, in our case it is state field (Right click and select View Definition on the state field on the page).
Step 3: Right Click and select Record Field Properties
Step 4: Select Edits tab and then select Table Edit. Select Prompt table with edit( or No Edit) as per your requirement. Write %EDITTABLE against Prompt table.*EDITTABLE is a field in table named DERIVED, provided by PeopleSoft.

Step 5: Place the EDITTABLE field from DERIVED Table on the page.
Step 6: On field change of Country Field, write the below code

if YOURRECORDNAME.COUNTRY = "US";
  DERIVED.EDITTABLE = "STATE_US_DVW"; // dynamic view of list of states of US
Else
  DERIVED.EDITTABLE = "STATE_CAN_DVW"; // dynamic view of list of states of CAN
End-if;

Using SQL Text

Instead of creating two views/dynamic views, you can also create one dynamic view and pass the SQL dynamically to change the prompt table.

Step 1: Create and save A Dynamic view
Step 2: Give the dynamic view as a prompt table to the state field.
Step 3: On field change of your country field you can write.

if YOURRECORDNAME.COUNTRY = "US";
  YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'US'";
ELSE
  YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'CAN'";
END-IF;

HAPPY LEARNING :)

Insert rows using PeopleCode

When inserting rows using PeopleCode, you can either use the Insert method with a record object or create a SQL Insert statement using the SQL object. If you do a single insert, use the Record Insert method. If you are in a loop and,therefore, calling the insert more than once, use the SQL object. The SQL object uses dedicated cursors and, if the database you are working with supports it, bulk insert.

A dedicated cursor means that the SQL gets compiled only once on the database, so PeopleTools looks for the meta-SQL only once. This can increase performance.

For bulk insert, inserted rows are buffered and sent to the database server only when the buffer is full or a commit occurs. This reduces the number of round-trips to the database. Again, this can increase performance.

⇒ The following is an example of using the Record Insert method:

&REC = CreateRecord(Record.GREG); 
&REC.DESCR.Value = "Y";
&REC.EMPLID.Value = "12180014"; 
&REC.Insert();

⇒ Insert()

The Insert method uses the field names of the record and their values to build and execute an Insert SQL statement which adds the given record (row of data) to the SQL table.Because this method results in a database change, it can only be issued in the following events:



SavePreChange


WorkFlow


SavePostChange



Example:

Local record &REC;
&REC = CreateRecord(RECORD.MYRECORD);
&REC.KEYF1.Value = "A";
&REC.KEYF2.Value = "B";
&REC.MYRF3.Value = "X";
&REC.MYRF4.Value = "Y";
&REC.Insert();

⇒The following is an example using a SQL object to insert rows:

&SQL = CreateSQL("%INSERT(:1)"); 
&REC = CreateRecord(Record.GREG); 
&SQL.BulkMode = True; 
For &I = 1 to 10 
   &REC.DESCR.Value = "Y" | &I; 
   &REC.EMPLID.Value = &I; 
   &SQL.Execute(&REC); 
End-For;

HAPPY LEARNING:)

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 :)

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 :)

Access level 1 field from level 0

/*Declaring Variables*/
Local Rowset &rowset;
Local Record &rec;
Local Field &field;

&rowset0=getlevel0();  /*Get level 0 rowset*/
&row=&rowset0.getrow(1);  /*Get row 0 of level 0 rowset*/
&rowset1=&row0.getrowset(scroll.RECORD_NAME1);  /*Get rowset from row 0 using the scroll name(can also be the name of the primary record in the scroll)*/
&row1=&rowset1.getrow(1);  /*get row1 of level 1 rowset*/
&rec1=&row1.getrecord(record.RECORD_NAME2);  /*get record in the row1*/
&field1=&rec1.FIELD_NAME;  /*finallay access the field in the record*/ 

OR------

&rowset1 = getlevel0(1).getrowset(scroll.RECORD_NAME1);
&field = &rowset.getrecord(record.RECORD_NAME).FIELD_NAME;


HAPPY LEARNING :) 

Trigger Peoplecode on selection of radio button.

When we are working with radio buttons, there is a problem say associated peoplecode does not trigger when we select radio button. Lets learn how associated peoplecode can be triggered on selecting radio button below.

Create a simple Peoplesoft page having radio buttons.



Now go to page properties and uncheck allow Defferred Processing.



Now you will see that associated peoplecode will trigger when you select a radio button. 

HAPPY LEARNING :)