RowInit & RowInsert PeopleCode and database insert

Today I was debugging an issue where in a ghost row is getting inserted into the grid in level 2 even if the user is not entering any value for the grid in level 2. There are many instances where you find the ghost row issue in PeopleSoft. This one was particular to the sequencing number and which is almost a common case across the product. So I will explain a bit about the ghost row issue with the particular case.

The page has a structure up to level 3. And my level 2 additional key is a sequence number which is auto populated by the system. Now when the user goes in add mode and enter any data up to level 1 (assuming he is adding only one row which is presented by default) and saves the page, then everything seems fine. The data up to level one is inserted into the database as user has filled only up to level 1.

Now if he tries to add another row at level one and enter data only in level 1 or if he just clicks the + button on the level 2 of the level 1 first row, then a ghost row of level 2 is getting inserted into the database even though user has not entered any data into the level 2 rows.

Problem

The reason for the ghost row is the sequencing logic written in RowInsert event. Since the additional key for level 2 is a sequence number which is populated automatically by the system, whenever the sequencing logic executes and assigns a value from the RowInsert event, the system automatically sets that particular row as changed and will mark it for database insert during the time of save.

Most of the places where auto sequencing is used, the sequence number field is read only and user cannot delete its value thus forcing the system to insert the row into database; making the case more complicated.

PeopleBooks has clearly mentioned that if you change the value of any field in the RowInit or RowInsert events, the system will mark the row as changed and will be considered during the save processing.

But in most cases, you are forced to write the logic in RowInsert event because it is apt to display the next sequence number whenever user clicks the plus button.

Now back to the issue why there was no ghost row inserted my first default row at level 1? The reason is pretty straight forward, the RowInsert event will get fired only when the user adds a row, for default rows that event is ignored.

Solution

This might be a well known situation so that PeopleTools has provided the solution for the problem as well. There is a delivered property for rowset class called ChangeOnInit. All you need to do is to set that property to false during the component load processing or during the RowInit processing of the higher level (in my case it is level 1). Once this property is set to false, the component processor will no longer mark the row as changed whenever a field value is programmatically updated in RowInit or RowInsert events, thereby that row will not be considered for database insert until and unless user updates any value on the field. This is a pretty handy property which avoids programmatic defaults be inserted into the database when it is not actually supposed to be inserted. Thus, this one line code will take away the headache. You can see the sample code given below which will address the given situation of ghost row.

rem This code should be written on the RowInit of the primary record one level up;

rem In this case code is written at Level 1 to resolve ghost row issue at level 2;

GetRow().GetRowset(Scroll.LEVEL2_REC).ChangeOnInit = False;

Controlling Row Selecting Programmatically

Most widely used mechanism in PeopleSoft to bring data into scrolls and grids on pages is using the default AutoSelect option on the scroll or grid properties. The AutoSelect will automatically populate your grid or scroll based on the higher order keys. But there are some cases where you may not need to load the entire data based on the higher order keys. You might want to cut down some of the data based on some field values, such as show up the city details of a country only if the city is marked as a state capital. The best and most effective method to handle this situation is to use a view (which checks for the capital status) and then use the AutoSelect property of the scroll or grid. This will help you to achieve the result with less effort and more efficiently.

But sometimes there arise a scenario where in you need to populate the scroll or grid based on the values on the other fields in the page. I’m sure most of you might have gone through this scenario and might very well aware of the solution. De-Select the AutoSelect property and populate the grid or row programmatically using the delivered function ScrollSelect. For people who are on relatively higher versions of PeopleTools, PeopleTools has provided a much faster and efficient method for the rowset called Select method. In visual effects the result of the function and method are going to be same. The usage also does not vary much. In the select parameters you pass the main record name and the dynamically built where clause which will form the required criteria based on the values selected on the higher level of your page. Usually this piece of code is associated under the FieldChange event of a push button. But in certain occasions it is wiser to use in PageActivate event or component PostBuild event. Again where to write the code depends on your actual requirement.

The reason I posted this article is that there are some less known delivered functions which helps you in controlling the row selection into a grid or scroll. These functions may not find enough usage in your normal requirements, but you may find it very useful and simple in some other cases.

The functions I would like to talk here is DiscardRow and StopFetching.

DiscardRow

This function allows you to selectively eliminate or discard some rows being added to the grid or scroll. You can check for the values in the selected row and then call this function so that if the value is not required then it will not be selected into the page. This function needs to be written at RowSelect event and is valid only at this particular event.

Take the first scenario where you need to show only capital city in the grid or scroll. Suppose now your requirement is when you load the component for a state, you need to display all the cities except the capital city. The first thing that may come to your mid may be creating a view and then assigning AutoSelect property at grid or scroll level. Some people may think of deselecting the AutoSelect  and populating the grid/scroll with peoplecode functions such as ScrollSelect at the component load or page load events.

But the easiest approach will be to write a code in the RowSelect event so that check if the city is capital, if that is true then call this function. Three lines of code will solve all the complications.

Having said the above, this function is not supposed to use every now and then. Use this function only if the amount of data selected is large and the number of rows to be discarded is negligible. Like the capital city example, there is only one row to be discarded so using this function holds good. If you have many more rows to be discarded, better go for view or select functions. If you use this function when there are many rows to be discarded, it may tamper the performance of the application.

rem see the example for the usage ;

If CITY_TBL.CAPITAL_FLAG = “Y” Then

DiscardRow();

End-IF

Stop Fetching

This is another function which is rarely used but very useful in some scenarios. The StopFetching function will stop retrieving any more additional rows from the database and adding it to the page. This function should also be used in RowSelect event only. The current row which is being processed will be added to the page. If you don’t want to add the current row as well then use DiscardRow() first and then StopFecthing().

Consider using this function when you are trying to do a data chunking logic where the order by costs a lot for the database you use. For this scenario, all you need to have is a component variable as a counter and increment whenever a row is selected. Once the counter meets the required count, call this function and it will save some time by not loading all the rows. But where ever possible and your db supports, then the chunking logic with row number and order by will be the best fit.

Another rare use case where you can consider this is when you need to display only first few rows based on some criteria selected on the page. Suppose you are displaying all the cities in a grid for the state page. And you have a rare case where for state Washington you need to display all the cities and for New York display only first three cities (imagine there is such a requirement). Then you could write the code in RowSelect event to count and an if clause to check if count is 3 and city is New York. If the conditions are met then call this function. Again try avoiding this function if the volume of data to be selected in the database is large. That may again increase the cost of the SQL query.

rem see the example for usage;

Component Number &nCount;

If STATE_TBL.STATE_CD = “NY” Then

&nCount = &nCount + 1;

If &nCount = 3 Then

StopFetching();

End-If;

End-If;

Though these functions prove to be handy at some scenarios, these may prove worst in other cases. So these functions need to be implemented judiciously and based on case by case.

PeopleSoft 9.2 Demo Environments

For those who want to have a look and feel of PeopleSoft 9.2 applications at tools release 8.53, Oracle has made available PeopleSoft 9.2 Demo VM’s for them. The demo environments are available for CRM, ELM, HCM and FSCM 9.2 applications. It is delivered as Virtualbox virtual machines. All the servers including appserver, webserver and database are pre-deployed in the VM images. So you no longer need to configure any of these. Just download the images and mount the image, your demo environment is up and ready for exploration. The demo environments contain the full functionality offered by PeopleSoft 9.2 applications. Oracle made these images free for all the people under limited licenses. You could download and use this to see the look and feel and the functional capabilities of the 9.2 applications.

These images can be deployed even in your desktop machines. You need to have enough storage (nearly 25 GB) capacity and enough memory (8GB should be sufficient) to deploy these applications.

To get the downloadable files and instructions to deploy the image, visit the Oracle support site (provided below). You may need to have a single user id with oracle to download these files.

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=345220936521188&id=1552548.1&_afrWindowMode=0&_adf.ctrl-state=8162yd9pk_4

Check out the below video posted by Oracle PeopleSoft group to learn more about Virtual Machine deployment.

Branding your PeopleSoft 9.2 Application

All set to upgrade your application to PeopleSoft release 9.2? The one mandatory step which most of the customers does when they implement a PeopleSoft application is personalizing or branding the application with the company logos and styles. PeopleSoft administers must be aware of incorporating all the non-PeopleSoft stuff with the PeopleSoft applications.

Now with the combination of PeopleSoft 9.2 and PeopleTools 8.53, branding the application to your company specific needs has became a simpler task. PeopleTools is delivering an online page which helps in uploading your organization specific Logo’s, StyleSheets, HTML sub pages and Javascripts. Yes, with this you no longer need to login to the servers and administer the profiles. Your application can be customized or branded from within the application itself.

All you need to do is, with administrator access log in to PeopleSoft and navigate to

PeopleTools > Portal > Branding > Branding Objects

You will get 4 tabs to upload your custom HTML code, Javascript Code, Style Sheet Code and Images.

For instance to upload the image, go to the image tab and click on Upload Image Object hyperlink.

This will bring up a modal page to enter the details of the logo.

If you are using the default style sheet, it is recommended that you use the image name which is same as the default image name (as shown above). For custom style sheets, the image name depends on how you defined it. Be careful when you give the name, if there already exists an object with the same name, then the file you are planning to upload will replace the existing object.

Once you upload and save, you are almost done. Check your portal and see if the changes are reflected there. If it is not reflected, then clear your AppServer, WebServer and WebBrowser caches and try again.

Similar to uploading the logo, you can apply other brandings such as HTML to display the password expiry text, JavaScript to control other LogoOn validations, StyleSheets to change the color, text and other themes. You can upload the scripts in their respective tabs.

Similar to branding your overall portal experience, PeopleSoft now allows you to customize your PeopleSoft pagelets to show your custom icons and styles. Branding the pagelets is also similar to branding the application. You can find the page to brand your pagelet in the same folder as you do for the portal (i.e. PeopleTools > Portal > Branding ). To read more about branding, refer PeopleBooks corresponding chapter.