Set Processing and Row by row processing

Recently in a Peoplesoft thread, I have seen many questions on set processing and row by row processing. I think it’s just a lack of clear understanding on the subject is what makes this simple concept to be repeatedly asked in the forums. I thought of explaining it in a simpler way.

We might have heard these two words mostly when dealing with applications engines. But I would like to make it clear that, set processing and row by row processing are two different methods to update data in generic. So what I meant to say is that, these two processes are not tied only to application engines. Wherever you update to sql tables, (eg: PeopleCode or codes outside peoplesoft) these two processes are applicable. Now let me explain both the process.

Row by Row Processing

By definition, with this method, we select each row and do the action. Suppose I have a table COUNTRY with below data.

COUNTRY	CITY	STATUS
USA	LA	A
IND	BLR	I
GBR	LON	I
AUS	SYD	A

Now my requirement is to clear the CITY for all countries with STATUS = I.

If I do row by row method, then I will select the first country with status I and then update the city. Then the next country, do update and so on.

If implemented in app engine, it would look like below.

Do Select Action

%Select(COUNTRY)

SELECT COUNTRY FROM PS_COUNTRY WHERE STATUS = ‘I’

Sql Action

UPDATE PS_COUNTRY SET CITY=’ ‘ WHERE COUNTRY =%BIND(COUNTRY)

If you use PeopleCode then instead of Do Select action, it will be a sql fetch and SQL action will be replaced with a SQLExec(). This is the only difference. In any language or technology if you follow this process to make update, then it is called row by row processing.

Set based processing

As the name indicates, set based processing means processing a bunch of rows as a set. On the contrary to row by row processing, in set based processing all the affected rows are updated at a stretch. If we take the previous example, the set based implementation will be as below.

SQL Action

UPDATE PS_COUNTRY SET CITY=’ ‘ WHERE STATUS =’I’

If it is peoplecode implementation, then the sql action will be replaced with a SQLExec().

Set based processing has an advantage over row by row processing. In most of the cases set based processing will be faster. The reason is obvious; set based processing needs to issue only one sql instruction, there by one db trip and reduced timings. However there are scenarios where row by row processing has an edge over the set based processing. For instance, if your select query contains joins to 2 or more high volume tables. Then it will be better to split the sql to go by row by row method. But still it varies from case to case. In general set based processing has an edge over row by row processing when it comes to performance stand point.

Related Language Records

PeopleSoft supports your application to be used in a defined set of multiple languages. It means if you login with French you will see all the data including navigation, help texts, page and component names and transaction data in French. For the same transaction if you login with English you will see the data in English as well. PeopleSoft will internally take care of all the translation of languages. But if you have customized your application, this is how you can use the multi-lingual feature for transaction data – using related language records.

Related Language Records

If you want to store your transaction data in multiple languages, you need some place to store the data. That place is related language records.  All your data in base language (language for which your app is installed- SELECT LANGUAGE_CD FROM PSOPTIONS) will be stored in the actual transaction table where as the data entered in other language is stored in the related language record. Each language will be identified by additional key, LANGUAGE_CD, in your related language record.

When you create related language records, you should consider the following.

1.       The keys of the related language record should contain all the keys of the base record in the same order.

2.       You should add an extra key, LANGUAGE_CD, as the last key for the record (exception is only for EFFDT field).

3.       Apart from the keys, your language record needs only the fields which need to be translated. Ie. DECR etc..

For example I have a record ABC with two fields EMPLID(Key Field) and ADDRESS. Now if I want to create an language record (ABC_LNG), to translate the address, then the record should contain three fields EMPLID(Key), LANGUAGE_CD(Key) and ADDRESS.

Now for translation to happen you need to assign the related language record to the base record. To do this, open the properties of the base record. Then go to the “Use” tab and provide the name of “Related Language Record” as the one which you created now.

Now I will tell you how this translation is going to work. For that I am taking the record example mention earlier.

Base Record: ABC

EMPLID	ADDRESS
001	Hudson Street, Near Almond junction
002	ABCD, EFG

Related language record: ABC_LNG

EMPLID	LANGUAGE_CD	ADDRESS
001	FRA	Hudson Street, environs de Almond jonction
001	DUT	Hudson Street, zona de Almond unión

Let us assume the base language is in English. Now I have actually 2 entries in the table ABC. If you observe the related language record, you can see that there are two entries for employee id 001 and zero entry for employee id 002.

Consider employee 001.

When you open in English, the address will be displayed as “Hudson Street, Near Almond junction”.

If you login with French, the address will be displayed as “Hudson Street, environs de Almond jonction ».

Now if you login with Dutch then the address will be « Hudson Street, zona de Almond unión”.

Consider employee 002.

Since this employee does not contain any data in any of the language records, if you login with any language the description will be same “ABCD, EFG”.

The logic is, when you login to the system, it will check if the base language and logged in language is same. If it is same then the data is displayed from the base record. If it is different;  then system will check the related language record to see if it has the data for logged in language. If data is found in the related language record, then it is displayed from the related language record otherwise from the base record.

Related Language Views

 It is an extension to the related language table concept. All the rules remain same for views also. This will come in picture when you are creating views for the transaction tables which have translation data enabled.

The only thing you need to take care is to join the related table in the related language view. Take the previous examples.

I will create a view (simple view for demonstration) with below specifications.

View: ABC_VW

Fields: EMPLID (Key), NAME, ADDRESS

SQL:  SELECT A.EMPLID, P.NAME, A.ADDRESS FROM  PS_ABC A, PS_PERSONAL_DATA P WHERE A.EMPLID = P.EMPLID

Now the structure of my related language view should be as below.

View: ABC_LNG_VW

Fields: EMPLID(Key), LANGUAGE_CD(Key), NAME, ADDRESS

SQL: SELECT A.EMPLID, L.LANGUAGE_CD, P.NAME, A.ADDRESS FROM PS_ABC A, PS_PERSONAL_DATA P, PS_ABC_LNG_VW L WHERE A.EMPLID = P.EMPLID AND A.EMPLID = L.EMPLID

Now you need to add the record ABC_LNG_VW to the record ABC_VW. The address field will get translated in the same way as your base table ABC does. If you want to translate the name field also, then you need to add the related language record for PERSONAL_DATA in the language view sql.

Rich Text Editor in PeopleSoft pages

I don’t know how many are aware of this cool feature which has come up from Tools release 8.50.

For those who miss the formatting and alignment in boring peoplesoft long text boxes, that is no more the case now. Now we can enable rich text editor in peoplesoft long text fields and do all the cool stuffs like format, font, size, color, alignment, inserting images, hyperlinks, tables, bullets and much more. It’s almost competing with your wordpad. You can save all your documents from word by copying and pasting to peoplesoft page. You even have the option to take print outs and previews from the PS page.

Interested? Here is the three simple step to implement it in your application.

1.       Open the field properties.

2.       Go to “Options” tab. This will be present only for Long fields and from tools release 8.50 on-wards.

3.       Check “Enable Rich Text” check box.

That’s it. You are done. Isn't it really a cool stuff??

OriginalValue and PriorValue

I thought of describing the difference and usages of the functions OriginalValue and PriorValue.

OriginalValue

This a useful property of a field class if you want to revert the changes or compare with the previous value of the field.

&Value = RECORD.FIELD.OriginalValue;

This statement always returns the value of the field in the database at that particular point of time. For the same reason, this property will not work for derived records.

Say my field has value “FIRST” in the database and I change it to “SECOND” and save it. Let us analyze the return value at various point of time.

n  Component Loaded & value not yet changed.

&Value = RECORD.FIELD.OriginalValue; /* Returns FIRST */

&Value = RECORD.FIELD.Value;/* Returns FIRST */

n  After  changing the value to SECOND & before saving

&Value = RECORD.FIELD.OriginalValue; /* Returns FIRST */

&Value = RECORD.FIELD.Value;/* Returns SECOND */

n  After saving the changes

&Value = RECORD.FIELD.OriginalValue; /* Returns SECOND */

&Value = RECORD.FIELD.Value;/* Returns SECOND */

This function can be used to compare the value with previous value and do some complex functionality. It is, if the change in amount field is more than 10%, then trigger the workflow.

Another use case will be to revert the changes back if some criteria is not met.

Well this is quite useful, but if the field is a derived work record field, this property will not work at all. PriorValue() is a lifesaver here.

PriorValue

Although it is similar to OriginalValue, there is a notable difference. This always returns the value in the buffer just before the change.

Let me explain it. Suppose the value in data base is FIRST and after that I changes the value in the below pattern.

FIRST -> SECOND -> THIRD -> FOURTH

Now if I execute Original value, it will return FIRST where as PriorValue will return THIRD (the value just before the last change)

&Value = RECORD.FIELD.OriginalValue; /* Returns FIRST */

&Value = RECORD.FIELD.Value;/* Returns FOURTH*/ 

&Value = PriorValue(RECORD.FIELD); /* Returns THIRD */

The advantage is that you can use this function for a derived record also as the value is fetched from buffer and not from database.

Note:- There are two restrictions for usage of PriorValue to work correctly.

1.       It should be placed in FieldChange or FieldEdit events

2.       It  should be placed in the same record field event. For example if the record field is JOURNAL.JOURNAL_ID.  Then the code should be placed in either JOURNAL.JOURNAL_ID.FieldEdit or JOURNAL.JOURNAL_ID.FieldChange.

In all other cases PriorValue() will return the current value of field instead of prior value.

Controlling Related Fields by Peoplecode

Most of you might be already familiar with this, but thought of sharing again.

We come across many scenarios where field property needs to be controlled by peoplecode to match with the business logic. I want to mention about controlling a related display field.

Say I have Employee Id as the control field and Name as the related display field. For user friendly behavior, I hide the Employee Id field and display only the related field. So the name is displayed on the page instead of the employee id which is difficult to understand.

Now I need to hide this name from the page based on some user actions. As you have thought it is a very simple job, just write a code as seen below.

RECORD.FIELD.Visible = False;

Here comes the complex scenario. Suppose I have my Project Manager Id and HR Manager Id on the same page. I want to display the name for both Project manager and HR manager. For that I assign the same related display field, say PERSONAL_DATA.NAME, to both the fields.

My requirement still remains the same. I want to hide only the name for the employee and the project manager and HR manager name should still be visible. But all the name fields are same (PERSONAL_DATA.NAME).

If I use the previous code, which name will become hidden? Employees, project manager’s or HR manager’s? It will hide only the name which comes first in the page order.

Here is a method to tackle it.

Say the display control fields be:

Employee – EMPL_ID

Project Manager – PROJ_MGR_ID

HR Manager – HR_MGR_ID

To hide the name of the employee you can use the below code.

Local Field &fRelDisplayField;

&fRelDisplayField = GetField(RECORD.EMPL_ID).GetRelated(PERSONAL_DATA.NAME);

&fRelDisplayField.Visible = False;

This will hide only the employee’s name and other two names will remain visible. To hide other fields replace EMPL_ID in the above code with appropriate field.

iScript in PeopleSoft

For those who have heard about iScript and wondering what it is, I could give a basic understanding on the iScripts. iScript is a method by which Peoplesoft  offers control to the developer in overriding the people tools frame work. In simple words using iScript, developer will be able to get complete control on the browser. You can create pages which looks like other websites (eg: facebook, amazon etc) still within the Peoplesoft system.

iScript have two main classes, Request and Response. Those who have experience with web development could easily relate these words. For others I could explain it in simple words.

Request class will get the parameters passed from the browser. For eg: in google if you search for “Peoplesoft”, then the browser will send a request to the server with parameter q and parameter value “PeopleSoft”. So in our iScript we can get the parameter value “PeopleSoft” using the request class and send out appropriate result (response).

As you have thought, Response class is the class responsible to give result to a query. In the above example once we receive the search word “Peoplesoft” and performed the search, we need to display the search result in the browser. For this we will formulate the result html and prints it on the user browser using the Write() or WriteLine() method of the response class.

Writing iScript Programs

1.       You should write iscript programs in record field event of any record starting with WEBLIB_ .

2.       The iScript program will be written like a normal function except that the function name starts with IScript_

For example I want to display a hello world page to the users. The basic steps are as follows.

First I choose a record, say WEBLIB_HELLOWORLD and on the record field (say HTML) Field Formula and write a function IScript_HelloWorld.

Now I create a HTML object with the html code to be printed.

HTML object name is HELLOWORLD.

<!DOCTYPE html>

<html>

<body>

<h1>Hello World</h1>

</body>

</html>

Now my code looks as below.

Function IScript_HelloWorld()

Local string &sHTML;

rem Get the text to be printed from the HTML object;

&sHTML = GetHTMLText(HTML.HELLOWORLD);

rem Print the html to the client browser;

%Response.Write(&sHTML);

End-Function;

3.       Now I need to give permission to the iScript. It is like normal activity, go to permission list open web libraries section and give necessary permission to the iScript (you need to choose iScript by drilling down from the underlying record).

4.       Now you can access your iScript page by going to the below navigation http://server:port/psc/site_name/portal_name/node_name/s/WEBLIB_name.FIELDNAME.FieldFormula.IScript_name

If you are already logged in, you will be taken to the hello world page. Otherwise peoplesoft sign in page will appear and from there you will be directly taken to the iscript page on successful login.

Now if you come to real applications, instead of printing hello world, if you want to print a value from data base. In the place of string “Hello world” in the HTML definition replace it with %bind(:1).

Now in the code where you get the html text, use the below.

rem I print “iscript example”  as the text;

&sHTML = GetHTMLText(HTML.HELLOWORLD,”iscript example);

The hard coded string can be replaced with a variable with has fetched the value from the database. Thus making your program more interactive. To add on more functionality you could use post functionality in the HTML and obtain the parameters using the Request class and printing sensitive data.

rem Getting parameter passed by the browser.

&sParm = %Request.GetParameter(“search”);

Now you can do processing based on the obtained string and print the output method.

iScripts are interesting method in peoplesoft to un-cuff the restrictions put by peoplesoft application designer. You can create content rich pages, implement ajax/json services, create mobile pages, redefine portels that has visibility in larger crowds, create mobile applications etc using the peoplesoft iscript.

While using iscript, you should take care of the below 3 points

1.       Browser compatibility of the HTML rendered

2.       Translation of the text printed on the browser

3.       Multi-lingual, multi-market and multi-currency situations should be taken care.

You can start with the hello world example and unwind yourself. I’m sure it will be fun experience.

For more details on iScript, visit : http://docs.oracle.com/cd/E25688_01/pt852pbr0/eng/psbooks/tpcr/htm/tpcr23.htm#g037ee99c9453fb39_ef90c_10c791ddc07__185c

%UpdateStats MetaSQL

Have you ever wondered why a sql step with %UpdateStats is frequently used in the application engines? I was always wondering why it is used until I got a chance to use the same. Let me explain why it is required.

Before explaining the metasql, I need to explain a bit on the database statistics. We know that if we create an index on the table, sql queries will fetch result much faster if we query based on the indexed fields. Now suppose a table is having multiple indexes with fields overlapping between the indexes. In that case how will the system pick the correct index so that the result is fetched faster? For that, database store meta information on the tables known as statistics. This statistics can be updated manually by the db administrator or can be scheduled to run on a fixed interval (widely used).

Let me go back to the original question, why it is required in application engines if this can be scheduled in the db? If you notice the usage of metasql, it will be mostly used for temporary or intermediate tables. Here comes the answer, the temporary or intermediate tables hold data only during the app engine time frame. So at the time when the scheduled db update happens, these tables do not contain any data and hence statistics will not be updated properly.

When should I consider using this? The answer is, in your process, if you have an intermediate table where you store large volume of data for further processing then you should call this metasql in the very next step. The condition to use this is, the step just before the %updatestsats should issue a commit. Otherwise the statistics updated will be wrong again. As I stated above it make sense to use this only for temp tables which hold large volume of data. If your tables hold only few rows of data, there is no point in index selection and hence statistics update. Further it may dampen your performance with an additional commit and database action.

Hope this gives a brief understanding on %updatestats.

People Tools Tables - Metadata

I thought of giving a brief idea of how a developer can cash in the knowledge on tools tables.

PeopleTools tables are database tables where all the metadata of the application are stored. It is sometimes handy to query the tools tables to find out answers rather than to going by app designer. For example, if you want to find all the tables which have used record ABC as a prompt table, you just need to write a query similar to below.

Select * from PSRECFIELD where editable = ‘ABC’;

Similarly you can use PSXLATITEM table to quickly find out the translate value of a field. PSROLEUSER can be used to find out roles assigned to a user. If you are doing a production support, you could insert rows to this table instead of going to the portal and assigning roles. This saves much of your precious time. PSOPRDEF can be queried to find out the user account details, to quickly check if the account is locked out, emplid etc… PSAUTHITEM can be queried to quickly find out the permission list to page linkage.

If you start using tools tables, it makes the life of a developer much easier. Always ensure that you do not modify the structure of these tables otherwise it could severely impact your application.

If you are looking out for more tools tables check out this link.

http://www.pscustomizer.com/peoplesoft-examples/peopletools-metadata/#record-meta-data

For a detailed list of all tools tables, you can refer http://www.go-faster.co.uk/peopletools/index.htm.