Identifying Page Field Type from Tools Tables

I was searching for a particular content in the internet and found a post which describes the field type code for the page field definition. I thought it was worth mentioning here so that it will supplement the first post People Tools Tables - Metadata and give you an idea on effectively using the tools meta data tables.

                             

To give a background, when you create a page in peoplesoft application designer, the page definition will be saved in PSPNLDEFN table. This will contain high level details of the page such as name, description, Owner, Page type etc… But have you ever wondered how the actual page on the PIA is drawn up when you visit the page? Peoplesoft actually stores the information of each and every field you place on the page in application designer in a tools table called PSPNLFIELD. It will contain all the details like Field name, label, position, field type, level, record name, field name, field style and so on. Based on this information, the page is dynamically drawn up each time you visit it from PIA. But if you query this table you see that almost all the information is stored as numerical values which you cannot infer or find a reference anywhere.

I will share the piece of information found over the net which list all the codes for the FIELDTYPE and FIELDUSE field.

FIELDTYPE – Defines the type of field

0 - STATIC TEXT
1 - FRAME
2 - GROUP BOX
3 - STATIS IMAGE
4 - EDIT BOX
5 - DROPDOWN LIST
6 - LONG EDIT BOX
7 - CHECK BOX
8 - RADIO BUTTON
9 - IMAGE
10 - SCROLL BAR
11 - SUBPAGE
12 - PEOPLECODE COMMAND - (BUTTON/HYPERLINK DESTINATION)
13 - SCROLL ACTION - (BUTTON/HYPERLINK DESTINATION)
14 - TOOLBAR ACTION - (BUTTON/HYPERLINK DESTINATION)
15 - EXTERNAL LINK - (BUTTON/HYPERLINK DESTINATION)
16 - INTERNAL LINK - (BUTTON/HYPERLINK DESTINATION)
17 - PROCESS - (BUTTON/HYPERLINK DESTINATION)
18 - SECONDARY PAGE
19 - GRID
20 - TREE
21 - SECONDARY PAGE - (BUTTON/HYPERLINK DESTINATION)
22 - <not identified>
23 - HORIZONTAL RULE
24 - TAB SEPARATOR
25 - HTML AREA
26 - PROMPT ACTION - (BUTTON/HYPERLINK DESTINATION)
27 - SCROLL AREA
28 - <not identified>
29 - PAGE ANCHOR
30 - CHART

FIELDUSE – Defines the properties you selected for the field in use tab

1 - Display Only 
2 - Invisible 
4 - <not identified>
8 - Display Control 
16 - Related Display 
32 - Multi Currency Field 
64 - No Auto Select 
128 - No Auto Update 
256 - No Row Insert 
512 - No Row Delete 
1024 - Show Label 
2048 - Freeze Grid Column 
4096 - Odd/Even Row Style 
8192 - <not identified>
16384 - <not identified>
32768 - <not identified>
65536 - <not identified>
131072 - <not identified>
262144 - Enable when display only 
524288 - <not identified>
1048576 - Row Delete 
2097152 - Row Insert 
4194304 - Next Page 
8388608 - Previous Page 
16777216 - <not identified>
33554432 - Show Row Counter 
67108864 - Hide Border 
134217728 - Unlimited Occurs 
268435456 - Fixed Height 
536870912 - <not identified>
1073741824 - Top 
2147483648 - Bottom 

Enabling Rowlevel security in PeopleSoft

Enabling Rowlevel security in PeopleSoft

You will know that you can enable security components by means of roles and permission lists. This means you won’t be able to access the component itself if you do not have permission granted for that page. But consider a scenario where you might be given access to the component but you may also want to prevent the user from selecting a particular data in that component. This is known as rowlevel security; Securing the row of data from the entire table. PeopleSoft has delivered a mechanism to secure the data at rowlevel. I will discuss on a very high level on how you can achieve this in PeopleSoft.

Although rowlevel security is an option provided by PeopleSoft, it is very restricted. That is you have only few fields for which this option can be enabled. And as you guessed these are the most important fields in the package. The fields which you can secure in PeopleSoft FSCM are Business Unit, Setid, Ledger, Book, Pay Cycle, Planning Instance and Project.

You can provide security by two means either by individual users or by permission list. You Should navigate to Setup Financials/Supply Chain > Security > Security Option and then select which security option you need and which all fields you need to provide the security.

Once you have selected the above option, then you need to populate a list which will map the Field Value and Permission List/User ID (based on your selection above).

Each field has two pages to store the list of secured values;one for the User ID and one for Permission List. You can find these pages in the navigation Setup Financials/Supply Chain > Security .

You need to open the corresponding pages based on your selection at first step. You need to provide the allowed values in the list as shown in the below screens.

Once you have done this most of your job is over. Now you need to run a process to apply the security settings.

Go to: Setup Financials/Supply Chain > Security > Apply Security Steps and run the process.

Once you run this process all your prompt views will be automatically replaced with new views which will apply the security selections you have done.

PeopleSoft has by default identified and delivered almost all areas where the rowlevel security needs to be implemented. If you want to add your own areas then you have the flexibility to do so.

Navigate to Setup Financials/Supply Chain > Security > Security View Names there you can specify the prompt view name to be used for all the three security options selected.

System will use the Search Text name to find the views to be replaced when you run the process. The existing view will be replaced with No Security View if you have selected “No Security” option in the first step. Similarly the system will be replacing with User ID Security View and Permission List Security View as per the selection on the first step. The last field will specify for which field this view needs to be applied as prompt view. While creating your own views you should make sure that your views point to the list records provided by peoplesoft for permission list or user id.

You can extend or leverage the existing feature to extend the security feature for other critical fields based on your business requirements.

Mail Classes in PeopleSoft

We have two methods in Peoplesoft to send emails from the PeopleSoft system. The first method is using SendMail built-in function. This function is useful if you want to send out quick email without any formatting and less peoplecode. However PeopleSoft has provided a powerful delivered class called mail class which can be used to create content rich mails with effective formatting, attachments and more. The application package will take care of most of the codes required to achieve the objective, however you need to know how to use the application class to send/receive emails which somewhat tricky.

I would like to give a glimpse on how to use this class for your purposes.  There are a set of application classes delivered along with the package. The classes include

·         MCFBodyPart

·         MCFEmail

·         MCFGetMail

·         MCFHeaders

·         MCFInboundEmail

·         MCFMailStore

·         MCFMultiPart

·         MCFMailUtil

·         MCFOutboundEmail

·         MCFPart

·         SMTPSession

The main classes which we need to import for sending emails are MCFBodyPart, MCFOutboundEmail and MCFMultiPart which I will explain in the coming paragraphs. The MCFMailUtil class sometimes needs to be implemented. The main purpose of the class is to access some utilities like encoding/decoding texts, validating email addresses and checking the status of SMTP email server.

To receive the emails and display it in PIA, the main class you need to access is MCFInboundEmail. Since this is a rare case scenario, I will not be explaining the same in this blog.

Let us see how we can send a formatted or rich content email using the mail classes. I will explain the three main classes mentioned earlier in step by step method.

MCF BodyPart Class

This class is used when we need to have attachments or HTML formatting or any other non-standard email methods. In all the cases you need to create an object of this class. Then using the methods and properties of this class we will assign the non-standard content or formatting to this class. This object is then later added as a property to MCFMultiPart Class.

Main Methods

The main methods of this class is...

1.       SetAttachmentContent – Use this method to make the content of the body part from a file.

Syntax: SetAttachmentContent({FilePath | FileURL}, FilePathType, FileName, FileDescr, OverrideContentType, OverrideCharset)

Main Properties

The main properties of the class are…

1.       AttachmentURL – Specify the URL for attachment here.

2.       ContentType – Mention the content format for the body part. Example “text/plain” , “text/html”.

3.       Disposition – This property can hold two values “inline” and “Attachment”. “inline” will display the message in the email body. “Attachment” method will make the content as an attachment to the email.

4.       MultiPart – If you have multiple parts for the same body then assign the multipart object to this property. In that case your text and attachment are ignored.

5.       Text – Specify the email text over here.

MCFMultiPart Class

A multipart class object can hold multiple body part objects you created using MCFBodyPart Class. You will assign the MCFMultiPart objects to Outbound email class and send the mail from Outbound email object class.

Main Methods

The main methods of MCFMultiPart Class is…

1.       AddBodyPart – It can be used to add body part objects created from the BodyPart class to this class object.

Main Property

The main property of MCFMultiPart Class is…

1.       SubType – Used to mention the type of sub objects of the class. The valid values can be alternative, related and mixed.

MCFOutboundEmail Class

This is the one stop class which you need to be accessed when your sole intension is for sending emails. This class internally inherits the MCFEmail class which is a sub of MCFPart class. So in effect, all the required properties and methods for sending emails are available from the Outbound email class. This class holds all the generic values like To Address, CC, BCC, Subject etc.

Main Methods

The main methods of this class are…

1.       AddAttachment – Use this method to add an attachment to the class. For multiple attachments and other content you can also create MCFBodyPart objects explained earlier and assign it to MCFMultiPart object which again needs to be assigned to MCFOutboundEmail class.

2.       AddHeader – Use this method to add headers to email.

3.       Send – Use this method to send the email.

Main Properties

The main properties of this class are…

1.       BCC – BCC mail addresses

2.       Bounce To – Email address to bounce if the delivery failed.

3.       CC – CC mail addresses

4.       Charset – The character set used for mail or attachment

5.       Content Language – Used to set language of the email. Multiple languages should be separated by comma.

6.       ContentType – Specify the content type of the email if the type is different from the default one.

7.       Importance – To set the importance value of the email. The values can low, normal and high.

8.       MultiPart – You will assign the MCFMultiPart object created in previous steps to this value.

9.       Priority – To set the priority of the email.  The values range from 1-5 with one having highest and 5 having lowest priority.

10.   Recipients – The main addresses to whom this mail should be sent.

11.   ReplyTo – Specify the reply to email address.

12.   Sender – Use it to mention the email address of the sender.

13.   StatusNotifyOptions – Use this to enable notification to the sender.

14.   Text – Specify email text of the email.

Example

You might be confused with the class methods and properties mentioned above. The picture will become clearer if we run through an example from PeopleBooks.

The typical steps involve

1.       Import the Mail classes

2.       Create corresponding objects

3.       Assign contents and attachments to MCFBodyPart class objects.

4.       Assign the MCFBodyPart objects to MCFMultiPart object

5.       Assign the MCFMultiPart object to MCFOutboundEmail object.

6.       Invoke the send method of MCFOutboundEmail class.

/*  Import Mail Classes  */

import PT_MCF_MAIL:*;

/*  Create MCFOutboundEmail class object  */

Local PT_MCF_MAIL:MCFOutboundEmail &email =

create PT_MCF_MAIL:MCFOutboundEmail();

/*  Create MCFBodyPart class object  */

  

Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();

/*  Assign Text property  */

&text.Text = "Hi There";

/* Create Second body part object*/

  

Local PT_MCF_MAIL:MCFBodyPart &html = create PT_MCF_MAIL:MCFBodyPart();

/*  Assign Text property  for object 2 and make it html type*/

&html.Text =

"<html><BODY><H1>EMail test with HTML content</H1><b>Hi There</b>" |

"<A href='http://www.peoplesoft.com'>Check this out!</A>" |

"<P></BODY></html>";

&html.ContentType = "text/html";

Local string &TestName = "Text and its alternate html body";

   /*  Create MCFMultiPart class object  */

Local PT_MCF_MAIL:MCFMultipart &mp = create PT_MCF_MAIL:MCFMultipart();

/*  Set the SubType property  */

&mp.SubType = "alternative; differences=Content-type";

/*  Add the body parts created earlier to the multi part object  */  

&mp.AddBodyPart(&text);

&mp.AddBodyPart(&html);

/*  Set the multi part object to the Outbound email object  */ 

&email.MultiPart = &mp;

/*  Set the required properties for the Outbound email class object*/

&email.From = &FromAddress;

&email.Recipients = &ToList;

&email.Subject = &Subject;

/*  Invoke the send method of OutboundEmail class object  */

  

Local integer &res = &email.Send();

Local boolean &done;

/*  Capture the return code of send and do further processing  */

  

Evaluate &resp

   When %ObEmail_Delivered

      /* every thing ok */

      &done = True;

      Break;

     

   When %ObEmail_NotDelivered

/*-- Check &email.InvalidAddresses, &email.ValidSentAddresses

and &email.ValidUnsentAddresses */

      &done = False;

      Break;

     

   When %ObEmail_PartiallyDelivered

/* Check &email.InvalidAddresses, &email.ValidSentAddresses

and &email.ValidUnsentAddresses; */

      &done = True;

      Break;

     

   When %ObEmail_FailedBeforeSending

/* Get the Message Set Number, message number;

Or just get the formatted messages from &email.ErrorDescription,

&email.ErrorDetails;*/

     

      &done = False;

      Break;

   End-Evaluate;

Delaying execution in PeopleSoft

Often we might have requirement to put a time delay between two statements in PeopleCode. This is often involved when we deal with files. We might need to wait for some time before the file is available and once it is available, we need to continue with the execution.

The frequent method we use in PeopleSoft is to put a while loop and we will loop it for 1000 or 10000 times depending upon the delay required. This will achieve the functionality we require and the delay is introduced. I was under the belief that this is the only and best method to achieve delay in PeopleCode until I read another thread regarding the execution delay code.

Before going to the discuss the alternative method to delay execution of code, I will tell why this is not the best method to do so.

11.       With do while we are introducing delay by looping for n times. So we cannot say to delay for a fixed time say 10 seconds.  The looping time varies from hardware to hardware and hence we cannot create a generalized formula which says n iterations equal 1 second or so. Every time we need to do a trial and error method to find the correlation between time and iterations.

22.      The major drawback of this method is that it is utilizing the application server, thereby reducing the available resource for other users. Since it is a loop, app server will be busy with executing the loop code the entire time delay we have provided. This is a kind of server overloading or a bad practice.

Now I will tell you the method I have seen to introduce the delay without having the major drawbacks mentioned above. The approach is to use sleep method of java class. With this method we can specify the time in seconds/milliseconds for the delay. Also unlike the loop method, the sleep will not cause the application server to execute and consume resources. It will simply stop execution of the thread and app server goes to sleep unless the time limit is reached, then the execution will be resumed. The code to achieve the same will be as below.

/* Assign the number of seconds to sleep */

&SecondToSleep = 2;

/* The below code will delay the execution for 2 seconds */

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

                                                                                                                                                                                 

You are welcome to share if there is any other better alternative for the same feature that you might be aware of.

Disabling TypeAhead functionality in PeopleSoft

Typeahead functionality is a good feature introduced by PeopleTools releases. This feature will bring up all the possible list of values for a prompt field when you start typing in the first letters of the word. This is brought up with the help of Ajax technology. This actually saves the time of the user by reducing the number of clicks and types. Instead of user clicking the prompt button, typing the search text, hitting search button and then selecting the result; now user can directly pick the required result from the same page. This is similar to the search suggestions we see on the google search field.

This is an interesting feature, but it too have the other side. When user types in a prompt field where the possible value list is in millions, the system started to retrieve the millions of rows as typeahead option and as a result the page was getting timed out. This affected most of the customers and they were not able to enter the prompt details.

At the initial release there was no option to disable this typeahead feature and users have to live with the performance issue. Now with the latest release of people tools, Oracle has addressed this issue and now we have two levels of options to disable the typeahead feature.

Option 1

Open the record field property in the app designer. Go to the Use Tab. Check the “Disable Autocomplete for this field”. This will disable typeahead for this record field.

Option 2

Go To: People Tools > Utilities > Administration > Lookup Exclusion Table

Add the record which you want to disable typeahead feature and save it. Unlike the option 1 this is applicable for all fields in the record.

Remember this is a very user friendly feature; disable it only if it is adversely affecting the system performance.

Pagelets in PeopleSoft

Pagelets is a powerfull feature introduced in later releases of People Tools. Pagelets can be considered as the widgets or apps which are familiar to us in the internet websites. Pagelets brings in information from the transaction tables in a very presentable format or widget style. These pagelets will be smaller in size and can be added to HomePages, WorkCenters and Dashboards. There are three sizes for pagelets narrow, wide and banner sizes. The page/space where we host the pagelet can hold multiple pagelets at the same time.

It can be used to display simple yet compelling information like notice; high priority items awaiting your approval, frequently used metrics etc. On pagelets users can further filter down the data to get the more relevant data. These filters can be saved and are called as Personalization. Like the usual widgets you see around in the normal life, PeopleSoft pagelets also has the option to refresh the particular piece of information without disturbing the actual page where this pagelet is hosted. Thus the specific information can be updated without navigating away or reloading the entire page. We also have an option to auto refresh the pagelet every n seconds we specify. It will be particularly helpful in displaying time sensitive information.

Peoplesoft pagelets can be created by using different technologies which includes PeopleSoft Component, PeopleSoft iScript and javascript. The easiest one will be the PeopleSoft Component based pagelets. I will explain how to create PeopleSoft Component based pagelets in the rest of the content.

While creating pagelet, you should be extremely concerned about the performance of the system. Because the pagelet will be hosted in some other space where there can be multiple pagelets. So if one pagelet hangs due to bad design, then user won’t be able to work on any other pagelets hosted on the page. So you should design your pagelet in such a manner that, the data and the page will be loaded very fast.

Below are the steps to create Pagelet in PeopleSoft.

Step 1: Create a normal Page, design it the way you want to be displayed in the pagelet.

Step 2: Create another page which you want to use as a personalization option for the pagelet. In this page you will put fields which will act as input values/filters for the data that is displayed in the pagelet.

Step 3: Add the pages to corresponding components.

Step 4: Write any business logic, such as filtering the data in the first page based on the data from the personalization page and other business logic you want to implement.

Step 5: Add your component to a menu.

Till now you have been doing the regular steps in creating PeopleSoft components. Now you need to register this as a pagelet.

Step 6:  Go to Structure and content: People Tools > Portal > Structure and Content

Step 7: Navigate to: Root > Portal Objects > Pagelets > [Folder where you want to register]

Step 8:  Click on Add Content Reference

Step 9: Provide the content name, label, description etc. Give the usage type as Pagelet.

Step 10: In the URL information group box, provide the node, menu, market and component of the pagelet page (page created in step 1). Select the URL Type as PeopleSoft Component.

Step 11:  [Optional: required only if personalization is needed] On the pagelet Attributes group box, provide the menu, component and market for the personalization page (Page created in step 2). Select the URL Type as PeopleSoft Component. Populate the Refresh Time (sec) field with a value in seconds if you want your pagelet to be refreshed automatically every nth second.

Step 12: Save the Content reference.

Step 13: Go to: People Tools > Security > Permissions & Roles > Permission Lists and open the permission list which is supposed for the pagelet users and provide required access to the new components. Save the Permission List.

With the above simple steps you have successfully created a pagelet. Now in order to use the pagelet, select the pagelet from the corresponding pages personalization option and save it. For example, to add the new pagelet to the homepage, click on Home and on the home page select Personalization hyperlink. From the list of pagelets displayed, select your pagelet and click on save. When you go back to the homepage, you can see your pagelet in the homepage. Now you can drag to arrange the pagelet in the required order among the other pagelets.

Working with Collections in CI

This blog is intended for readers who have basic knowledge of CI’s and like advanced actions like navigating through and updating collections.

What is a collection in CI? In simple words collection is same as a rowset in your component buffer. You can navigate through the collection and update/delete any row in the collection as you do with rowset in the component. Let’s see how this can be achieved by PeopleCode.

To assign the collection (rowset) to a variable you can use the below code.

&CI = %Session.GetCompIntfc(CompIntfc.MY_CI);

&CI.KEY =”Key”;

&CI.Get();

/* Get the collection(rowset) */

&Collection = &CI.MY_COLLECTION;

Your collection name may not always be same as the rowset.  So you should verify with the CI to get the correct collection name.

To get the number of rows in the rowset, similar to the ActiveRowCount property in the rowsets you can use the Count property.

/* To get the number of rows */

&nRows = &Collection.Count;

Now to insert a new row to the collection you can use InserItem method.  You need to specify the index (row number in rowset) while using the method.

&myItem = &Collection.InsertItem(1);

The above code will insert a new row at the first row. It is, the new row’s row count will be 2. This is similar to clicking the + button in the PIA page. If we click the + button on the first row, the new row will be inserted as the second row.

Now to update the value in this row, you can directly call the property (corresponding to Field in component) and assign.

&myItem.NAME = “My Name”;

Now if your component interface is in interactive mode and your grid in the component has some code to sort the grid at field change you should be very careful. Eg: when you change a date, the grid will sort based on the ascending value of date. In such case it can happen that when you enter the value, the rowset will get sorted and when you update the second field, you may be actually updating the second field in some other row as the index has changed due to sorting. In that case you need to loop back and get the original Item(row) and then proceed with updating.

Now to get the Nth row of the collection (rowset) you can use the below code.

&myItem = &Collection.Item(&RowNumber);

Now to delete the row from the component, you can deleteItem method. When using the method, you should specify the index of the row to be deleted.

&Collection.DeleteItem(2);

If your component is effective dated, to get the effective dated row you can use CurrentItem method.

&effDatedItem = &Collection.CurrentItem;

To fetch the row number of the effective dated row, you can directly use the CurrentItemNum property.

&CurrentRowNumber = &Collection.CurrentItemNum;

All these above logics can be clubbed to loop up to level 3. For example you can use the below code.

&CI  = %Session.GetCompIntfc(CompIntfc.MY_CI);

/* Pass the Key */

&CI.KEY = “Key”;

&CI.Get();

/* Get Level 1 Collection */

&Collection1 = &CI.COLLECTION_ONE;

&MyItem1 = &Collection1.Item(1);

/*Get Level 2 Collection */

&Collection2 = &MyItem1.COLLECTION_TWO;

&MyItem2 = &Collection2.Item(1);

/*Get Level3 Row */

&Collection3 = &MyItem2.COLLECTION_THREE;

&MyItem3 = &Collection3.Item(1);

/* Updating Level 3 row */

&MyItem3.FIELD_PROPERTY  = “My Value”;

With the set of commands mentioned in this blog you will be able achieve most the functions desired for the business logic with rowsets in component interface. For the full list of commands that can be used for a component interface in PeopleSoft, you can refer the corresponding chapter in PeopleBooks.

http://docs.oracle.com/cd/E25688_01/pt852pbr0/eng/psbooks/tcpi/book.htm

Inserting or updating long fields to database

It often errors out when we try to insert or update a record with long fields. Different databases handle the long fields differently and there are often errors and headaches included when dealing with a long field. As a peoplesoft developer, you might have often experienced the problem when dealing with application engine sql actions or sqlexec() function in peoplecode events.

However PeopleSoft has delivered some solution to handle the long fields (although not a full solution). The way PeopleSoft wants us to handle the long fields is via metasql’s. PeopleSoft has delivered two metasql’s towards this intension.

1.       %InsertSelectWithLongs

This metasql can be used in PeopleCode, Application Engine, SQL, Views & Dynamic Views. This metasql is helpful when you are trying to insert a value into the database. 

The syntax of the metasql is as below.

%InsertSelectWithLongs([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .) 

Eg: %InsertSelectWithLongs(DISTINCT, A,B,C)

FROM A,B

WHERE %Join(COMMON_KEYS,B,C)

2.       %TextIn

This metasql can be used in PeopleCode, Application Engine, SQL, Views & Dynamic Views. It can be used when you are writing an update statement as well as insert statement.

The syntax is as below.

%TextIn(BindVariable)

Eg: SqlExec(“Update PS_TABLE SET LONG_FIELD=%TextIn(:1) WHERE CRITERIA_FIELD =’TRUE’”,”My Long Text”);

Drill down URL in PSQuery

Drill down URL is an exciting feature offered by PeopleSoft for users who base their daily work based on the query results. The drill down URL will take you directly to the context sensitive transaction page as soon as you click on the link that appears along with the query results. This eliminates the need of noting down the value and navigating to the component manually to enter the data and open up the page for editing.

Despite of the extended value this feature provides, most of the users are still using the old approach of noting down values and opening up the component manually. The main reason behind this should be the lack of awareness of the feature or lack of knowledge on how to configure a drill down URL in PS Queries. Since most of the PS queries are handled and created by the business users (although with the help of developers), they might not be updated on the enhancement done on PS QUERY by the PeopleSoft Tools team.

I will demonstrate a simple step by step instruction on how to configure drill down urls in PeopleSoft PS QUERY.

Step 1 : Go to expressions tab on your PS QUERY and click on Add Expression button.

Step 2: Select the expression type as drilling URL.

Step 3: Click on the appropriate type of URL you want to create.

Select Query URL if you want your URL to point to a different query result. Select Component URL if you want to open up a transaction page. Select External URL if you want to open up an external page like google. Select Attachment URL if you want to open up an attachment by clicking on the link. Select Free Form URL to create your own type of URL.

Step 3.1: Query URL – Enter the Portal, Node, Query and Output Formats for your destination PS QUERY. If your destination query has prompt fields, then you can pass the prompt values by clicking on the prompt button. It will list all the prompts in the destination query. You just select the corresponding fields from the present query to pass on the value.

If you want to display the link based on some information retrieved by the query then click on the select fields. Your query results will display these values as a hyper link to the destination PS Query.

Step 3.2: Component URL  - Click on the add content reference link and then select your destination transaction component.

Click on the Search Fields and select the corresponding field from the query result field. This will map your URL to the corresponding transaction.

Using Map URL option, you can make one of your query result field as the URL. The URL will display the corresponding field value, but when you click on it, it will take you to the transaction page.

Step 3.3: External URL -You can type in the URL in the URL field.

Step 3.4: Attachment URL – You needs to enter the details and the URL will point to the attachment.

Step 3.5: Free Form URL – In this option you can enter whatever URL you want to point to.

Step 4: If you have not selected any mapping fields in any of the above steps, then to display the URL you need to ass the expression as a field. To do so just click on the “Use as Field” on the corresponding expression. If you had already selected a mapping field, the URL will appear on the corresponding query output field.

These are the four simple steps that you need to do in order to invoke this powerful feature in PSQUERY. Even if you are a functional or business user of PeopleSoft system, you can configure your query within 5 minutes which will save much of your valuable time from the manual process you are following currently.

Pivot Grids in PeopleSoft

Pivot grids are simple yet powerful analytics tool introduced from PeopleTools 8.52. Although the Pivot Grids are at skeleton form at release 8.52, it has been enhanced much better in the latter release 8.53.

Pivot grids enable users to slice and dice the data enabling the users to get multiple dimensions for the same data. It also offers interactive charts, which will be refreshed as and when the grid data is redrawn. The combination of the grid and chart offers a powerful mechanism to interpret complex data. This eliminates the need for exporting the data to excel and pivoting it over there. Another added advantage is that user could configure related actions to the pivot grid data, thereby making it easy to take context sensitive actions.

Let us look into how we can create a pivot grid from scratch. It is much easier than any of you might have thought about.

Here are the steps you need to follow.

Step 1: Create a PSQUERY which will fetch the required data that needs to be pivoted. Note. All the query security applicable for the PS Query will be applicable for the Pivot Grid as well.

Step 2: Go to- Reporting Tools > Pivot Grid > Pivot Grid Wizard.  Provide a name to the Pivot Grid and click on Add.

Step 3: On the First step of the wizard provide the below information.

Pivot Grid Title – This will be the Title appearing on the final pivot grid created.

Description – A description to identify the purpose of the pivot grid.

Pivot Grid Type - Select “Public” if you want other users to access your pivot grid, otherwise select “Private”.

Owner - Select the owning module.

Now click on next to proceed to the next step.

Step 4: On this step, leave the “Data Source Type” as it is (it is intended for future developments). Select the query you created in the Step 1 in the field “Query Name”. Now you should select all the query columns which you want to use/display in the pivot grid. Click on next.

Step 5: You need to configure each field in the “Select Data Source Information” group box to align it with the usage of the pivot grid.

Column Type – we have 3 options. Use “Display” only if you want to display this field in the detailed drill down. If you select this, this field will not be present on the pivot grid or the chart.

Select “Axis” option if you are going to take a report on this parameter. In the chart you can select the X-axis from these fields only.

Select “Value” option if it is to be count or aggregate amount that needs to be selected. Usually the chart Y-axis will be selected from this Value fields.

Total – Select this option if you want your pivot grid to display the total value for the field. If it is unchecked each item will be displayed as separate rows.

Aggregate – This option is available only for value fields. This is selected to display how the value needs to be displayed. “Average” will display the average value, “Count” will display the count of rows, “Maximum” will display the maximum value, “Minimum” will display the minimum value & “Sum” will display the sum of values.

You can provide the default prompt values for the underlying PS Query in the “Select Query Prompt Values” space. If you do not want the user to change the prompt value from the final pivot grid, you can uncheck the “Visible Prompt” from the corresponding prompt field.

Now click on Next.

Step 6: This is the final step where you decide how your pivot grid/chart appears. From the default view, select an option. “Grid Only” contains the pivot grid alone, “Chart Only” contains pivot chart alone &  “Grid & Chart” option displays both the grid and chart on the same page.

“Specify Axis Information” – This is the space where you configure which field behaves which way. Let us check how we can configure it.

Grid Axis – This has 3 options. Select the “Column” option if you want to display this field as a column in the grid. Select “Row” option if you want the field to be displayed as row in the pivot grid.

	Business Unit	Department
Revenue
Expenses

Here BU & Department are column fields and Revenue & Expenses are row fields.

The third option is “Filter”, if you select it, this field will come on top of the grid/chart as a filter based on whose values you can filter the entire grid/chart. If you do not select any value for this field, it become a drill down option, so that when user clicks on the chart or grid user can drill on this field value.

Chart Axis – This field has 4 options and is used to set the axis for the chart. Select “X-axis” if you want to set the field as x axis. Select “Y-axis” if you want to set the field as y axis. Select “Overlay” if you want to overlay the field in the y axis. This option is particularly good if you want to draw to values on the same Y axis. The overlay will appear as a line chart. Leave this field blank if you want to make it as a drill down. Select “Filter” if you want to show this field as a filter on the top of chart.

On the Grid Options: Select “Collapsible Data Area” if you want to collapse the grid. Select “Expanded State” to make the grid expanded by default. The third one is important one. Use “No Drag and Drop” if you do not want the user to drag and drop the columns or fields across the grid.

In the “Chart Options”, you can configure the chart title, chart type, axis labels. In the advanced options, you can even set the height & width of the chart, whether or where to show the legends, precession of the Y axis and if it is a pie chart you can make it even an exploded pie making visually appealing if the segments are smaller.

In the “Viewer Options” you can configure the options that are visible for the end user when it is accessed from anywhere else.

Step 7:  Now you can save the pivot grid and click on next to preview the grid.

On the preview page of the wizard, you have options to publish the pivot grid as a pagelet so that it can be used in other places such as dashboards, home pages, workcenters & other peoplesoft pages. Also you can attach related actions to the pivot grid from this page. The related actions and pagelet will be covered in a different section.

Implementing Parallel processing in Peoplesoft - Part III/III

How to implement parallel processing?

Implementing parallel processing in Peoplesoft is a simple task if you make sure that you follow all the steps systematically. Below is the generic guide line to implement parallel processing in peoplesoft.

   1.       Include locking fields in the base table – The main/ driver table for your transaction should contain a field to map that the table is used by an application engine program. The best way to do so is to add field PROCESS_INSTANCE in your base table. If you are allowing users to run the same process in online mode also it is recommended that you add one more fied PROCESSED_FALG. The significance of these fields will be explained in the following steps.

   2.       Create temporary tables – Temporary tables is the heart of peoplesoft parallel processing. For all the temporary data collections and manipulations, it is recommended that you do it inside a table. This will avoid data being transferred out of the database and makes the processing faster. The temporary tables should contain PROCESS_INSTANCE as one of the key. The usual naming convention will be the table name ending with _TAO or _TMP. The record type should be selected as Temporary Table.

   3.       Add the Temporary Table to application engine – Go to the Temp Tables tab of application engine properties and all the temp tables created for your process in this tab. Also provide the Instance count at this tab. Instance count will be the number of process you need to run in parallel in batch mode. To provide the instance count for online mode set it in the PeopleTools Options page (People Tools > Utilities > Administration >PeopleTools Options). There will be another option in the Temp Tables tab of app engine property called “Runtime”. If you select Shared, then the program will use your base temp table if the total number of parallel instances exceeds the count you have mentioned. The program will Abort if you have selected “Abort” option for the same.

   4.       Build the Temp Tables – You need to build the temp tables only after assigning it to the app engine. Once you build the table, it will generate copies for the table for as many instance you have mentioned (online+batch), the maximum being 99. Each instance will end with specific instance count TAO1, TAO2 etc…

   5.       Locking logic – Now you are done with the configurations and needs to write the code to suite parallel processing. Locking logic is important one. If two processes are initiated for same transactions at the same time, then both will process the transaction and at the end when inserting data back to the tables. Both the process will try to insert the same data and as a result the process errors out or in case of updating the process will end up in updating wrong data. To overcome this situation, we use locking logics. For this we have created fields in step 1. At the beginning of the program, write an update statement as below to update the base table with the process instance of the current process.

UPDATE PS_BASE_TABLE SET PROCESS_INSTANCE =%ProcessInstance WHERE PROCESS_INSTANCE = 0

So when the second program tries to work on this transaction, it will see that this is used by some other application engine and leaves it. Thus avoiding duplicate processing and chances of error. Now comes another scenario, if you are also running the process instance in online mode then your Process Instance will be always zero and the previous sql will not help you out. In such cases we need to use the second field added in step 1. Then the sql needs to be modified as below.

UPDATE PS_BASE_TABLE SET PROCESS_INSTANCE = %ProcessInstance , PROCESSED_FLAG=’Y’ WHERE PROCESS_INSTANCE = 0 and PROCESSED_FLAG = ‘N’

   6.       Drag data to temp table and do the processing – Now you can start dragging the data from driver table to the temp tables based on the process instance and the do the processing.

   7.       Use %Table() metasql – When you do all the processing with temp tables, always make sure that you wrap your Temporary record name with %Table() metasql. It will automatically unwind to the current instance name. For example if your current instance is 6, then the below sql will unwind to UPDATE PS_SAL_TAO6 SET SAL=SAL*10

UPDATE %Table(SAL_TAO) SET SAL+SAL*10

   8.       Unlock the base table when done with processing -  Once you are done with the processing you can now unlock the base tables by setting the PROCESS_INSTANCE to 0. Otherwise any other process run at a later time for the same transactions will never be picked up for processing. If your business logic needs that transaction to be processed only once, then you can avoid this step.

   9.       Avoid Truncating tables – Most people are used to truncating the temporary tables as the last step for the processing to clear up the temporary data. You can now avoid this step as in the latest versions of PeopleTools, this is taken care internally. Once your program ends, application engine will automatically issue the truncate statements.

I am sure if you follow these steps carefully while creating your program, you can easily build up your process to work in parallel.

Read Related: Implementing Parallel processing in Peoplesoft -Part II/III