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.

Firing code on field change of a search page

Not sure whether every one is aware of this. I had a strange requirement; whenever user changes a value of a field on the Search Page to a particular value, the value on another field on the search page needs to be populated automatically and needs to be grayed out. I had to burn out my head, and finally found out the solution. But the solution made me feel as a fool ( that simple was the solution). Thought of sharing it over here so that anyone else facing the same problem will be helped.

Remember Panda’s Pa’s dialogue, “and the secret ingredient in the secret ingredient soup is ‘NOTHING’” J. Yes the solution is nothing. On the Add Search Record, write the corresponding code in the Field Change event of the field which controls the other fields. The Field Change event will fire as if you are in a normal PeopleSoft page. Sounds very interesting, isn’t it? The only disadvantage is that, this code will fire only if you are in add mode. On the normal search page this code will not fire. Still need to find out a solution (other than javascripts) to fire field change events in normal search page. Please don’t hesitate to share your ideas through the comments section.

PeopleSoft Active Analytics

PeopleSoft Active Analytics

Are you been into the business of writing custom codes to handling certain events in PeopleSoft applications such as firing an email or error when a certain product is added by procurement or notifying a employee and the manager when he is being promoted? If that is the case, PeopleSoft has brought forward an online technology with which you could handle all these events.

With PeopleSoft Active analytics framework, you only need to write the code once and attach your code to related component or application engine programs. The rest all is decided based on the configuration which the actual business user could go in and configure by himself. With the change in each and every business policy, you could just visit the configuration page and add the logics or event to be fired at each stage.  So with this framework in place, you need not re-write the code with each and every change in your business practice. You can do all these within a matter of minutes by going to the configuration page and setting up the appropriate configurations.

If you have not already started playing around with this smart decision making system, start now. You could get started with the PeopleBooks. If you need more in depth knowledge on this concept, refer Active Analytics in CRM PeopleBooks.

Embedding Pivot grids in Standard Pages

People who are working in Tools 8.52 or 8.53 must be familiar with the new feature called Pivot Grids. Pivot grids provide great abilities in analytics yet maintaining the simplicity. PeopleSoft Release 9.2 is all boasted with different analytic Pivot Grids delivered by PeopleSoft. Creating a Pivot Grid is also a simple task for those who are already familiar with PS Queries. To know how to create a Pivot Grid, read my previous post on the topic.

You might find Dashboards, WorkCenters, Related Actions and Pagelets as the most suited place to put a Pivot Grid. Have you ever felt like that it will be good if I could place my Pivot Grid inside a standard page I already have? If you have the same wish I have, I have something left for you. Yes, it is possible to embed a Pivot Grid inside a standard page and it is not that difficult as you might have thought. Just follow the three simple steps.

Step I: Publish the pivot grid as a pagelet. Make sure that you check the embeddable option and note down the Pagelet ID. If you missed to note the ID, you will get it from the pagelet wizard.

Step II: Create a HTML area on your standard page and assign a work record long field (say A_WRK.HTMLAREA).

Step III: On the rowinit of the work record field’s PeopleCode event write the below PeopleCode.

/* Import the Pagelet Wizard application */

import PTPPB_PAGELET:*;

Local object &PageletID;

/* Create the Pagelet Wizard */

&PGWiz = create PTPPB_PAGELET:PageletWizard();

&PageletName = "YOUR PAGELET NAME";

&Pagelet = &PGWiz.getPageletByID(&PageletName, False);

&myHTML = &Pagelet.Execute();

A_WRK.HTMLAREA.Value = "<div class=’PSTEXT’>" | &myHTML | "</div>";

If you want to pass context sensitive prompt values to the pivot grid, then you can even do that. Try and explore the class PageletWizard for more details.

Context Search Record

I know that everyone is familiar with the Search Record and Add Search Records in a component as it is one the first thing you might learn when you attend a PeopleSoft Technical training or interview. However, people who work with the latest Tools releases might have noticed a new search record type in the component properties, known as Context Search Records. This search record is not among the well-known list of the PeopleSoft technologies.

I will cover an overview on the new kind of search record introduced by PeopleSoft. Before that I would let the dust settle, this new record is no way going to impact any of your existing functionalities or applications. This new Search Record is solely intended for related content linkage.

You might have seen many pages with Search Records having no keys (mostly INSTALLATION). Some applications instead of using a PeopleSoft standard Search Page, they create their own Search Page and hence tend to use INSTALLATION (or other similar records) as the search record. So when you really create a related content to this component, you won’t be able to pass or map any parameters. To tackle this scenario, PeopleSoft has introduced this new Search Record called Context Search Record. If you define a record as Context Search Record in the component properties, even though your default Search Record does not have any keys, all the fields in the Context Search Record will be listed in the related content configuration page to map. Thus allowing you to pass some values to the context component. If your component has a default Search Record with keys, then the keys of that record will be listed for mapping. The earlier one will be used for mapping only if your default Search Record lacks any search keys.

%Next and %Previous MetaSqls

I came across debugging an application engine and got to these two interesting meta sql’s. Thought of sharing it over here so that those who are not already aware of it can make benefit of it when writing an application engine program next time. These two meta sql’s are exclusively for use in application engine programs.

As you have thought about, the sql name itself suggest its intended functionality. This sqls are useful when you are updating or inserting a table with sequence numbers. %Next will return the value in the associated field first and then increment the value of associated field with 1. This you can compare with the a++ methodology in c++ (for those who are aware of c++). The typical example for the usage is as follows.

rem In the PeopleCode section, you may assign the initial value of the bind field;

MY_STATE_RECORD_AET.SEQ_NUM.Value = 1;

rem Now in the Sql Update/Insert statement you may use this bind variable in conjunction with the %Next metasql;

UPDATE MY_TABLE SET SEQ_NUM = %Next(SEQ_NUM) WHERE …

During the first run of the sql, the above statement will update the SEQ_NUM in MY_TABLE with the value 1 and the value of state record field will be updated to 2. So that in the subsequent run, your db table is updated with a value of 2 and bind field with value of 3 and so on. As mentioned above, you may relate it to a c++ code of c = a++;

%Previous metasql does the opposite of what %Next does. It will assign the value to and decrements its own value with 1. This can be related to a—methodology in C++. Take the example code below.

rem PeopleCode section;

MY_STATE_RECORD_AET.SEQ_NUM.Value = 10;

rem SQL action

UPDATE MY_TABLE SET SEQ_NUM = %Previous(SEQ_NUM) WHERE …

After the execution of the code, your db field will be updated with a value of 10 and your bind field will be updated with a value of 9. The corresponding expression in c++ is c = a--;

Though simple, these functions will be extremely useful when working with an application engine program which handles sequencing logic. It helps you to avoid the extra step of handling the bind variable value as it is now system handled.

When using these sqls with DoSelect action, you should be careful as these increment or decrement will happen only with each executions. What this means is, if you have not selected the ReSelect option, then the increment or decrement will happen only once even though your sql returns a thousand rows.

What is PUM?

PUM is the new terminology which came along with the PeopleSoft applications release 9.2. PUM stands for PeopleSoft Update Manager. PeopleSoft Update Manager drastically changes the way you maintain your PeopleSoft applications.

As of present you might be doing all your maintenance, bug fixes or patches via bundles issued by PeopleSoft. The bundle method is cumbersome and involves a lot of cost. Though you have bought only one product, to update the product, you might end up in applying a bundle which includes bug fixes for all the products in that suite. Then testing these bugs against your own customizations and applying them take a lot of your time and money. The problem becomes more serious when there are dependencies between the bundles. Sometimes, the bundle 18 might be dependent on bundle 15 which in turn might be dependent on bundle 12 and so on. What this means is that, to apply bundle 18, you might have to apply bundle 15 first and so on. Eventually you end up in applying all the bundles. Imagine the situation where you are trying to apply a fix of 1 line code change and finally you end up in applying the whole bundle packages which cumulatively crosses 1000 code lines.

PUM is the answer for the problem from PeopleSoft. The PeopleSoft Update Manger will help you to select and apply the particular fix alone which you are looking for. This is called as selective patching. If you want to get a fix from PeopleSoft, to resolve an error message on a page, go to PUM and search for the error details or the bug details and you will find the resolution of your problem. Just select that bug alone and create the change package for applying it to your production system. No need to worry about dependencies with the PUM model. If there is any dependency, the PUM system will automatically include all the changes required for your resolution into the single change package. Most of your work is done by the PUM system and the only thing you need to do is to test and apply the change package generated by the PUM system.

How this system works? You might be how you generate the change packages. Earlier it used to be zip files, but since it became selective patching, zips alone won’t work out. PeopleSoft has come up with the approach of virtual images. You can download the maintenance image for various releases from the Oracle Support center.  You can host the machine with these images which contain all the required servers deployed. Once it is up, you may select the fixes required for your maintenance and proceed to change package creation. You can then deploy the change package created directly to your test or production database depending on the IT strategy.

 Some organizations always like to apply all the fixes released by the PeopleSoft regardless of whether they have faced that issue or not. If you are among one of such organizations, you might be wondering whether you have to select all the bugs or issues reported manually and then create a change package. Luckily PeopleSoft has an answer to your question too. PeopleSoft is providing an option called “Make Me Current”. If you select that option in the PUM, your change package will include all the fixes that has gone till that image. Thus applying the package will make your application current with the PeopleSoft release mechanism.

Why there are too many images? Each of the bug will be reported or fixed in different points in the time line. So all the fixes till a fixed period of time comes up in an image. Any fix that is falling in the next window will come up in the next image and so on. So what is the best image for you to download? Always download the latest image released by the PeopleSoft as it is a cumulative image and contains fixes for all the bugs reported from image 1 till the last image. So you might be getting a chance to apply other relevant bugs or features pertaining to your module. So far so good, but the golden question that you might of thinking of will be “can I start using it right away?”. The answer will be a mixed one; yes you can use it only if your application is on PeopleSoft 9.2. For the prior releases you still have to go through the hurdles of bundles and maintenance package.

For an overview on the image concepts, view the below video from Oracle PeopleSoft channel on YouTube.

PeopleSoft Grants WorkCenter

The PeopleSoft Grants is an application that support and automate the research and government grants functionalities. With the new release of PeopleSoft 9.2 and PeopleTools 8.53, PeopleSoft has succeeded in improving the usability and productivity of the grants users. One of the major improvements toward the same is the PeopleSoft Grants WorkCenter.

The Grants WorkCenter is the single stop working place for the research administrator or the principle investigator. The Grants WorkCenter with an interactive dashboard showing the various research budgets, amounts and the amounts that are pending to be billed with the agency has direct option to take necessary action from the dashboard itself. It comes with various exceptions the research administrator needs to be aware of including the billing or revenue related action items which will be a crucial action item for a research administration. The WorkCenter incorporates various queries and reports about the grants or award process and shortcuts to almost all the major components in the Grants application making it really a single stop navigation to be working with.

PeopleSoft has released a video feature overview on the Grants WorkCenter, showcasing the power of the new feature. View the video to get a feel of the new feature.

PeopleSoft Forms and Approval builder

None of the enterprise application will able to convert 100% of daily work into digital format. The enterprise application will cover almost all the obvious scenarios. But the real use case may vary from company to company. PeopleSoft has tried reducing the paper work with the help of a highly configurable forms and approval builder.

Imagine a situation where your company placing a purchase order or hiring a candidate. The typical cycle in the system starts with creating a PO or an applicant in the system. But in real scenarios there will be a lot more paper work that has to be done before it happens in the real system. For example, the clerk has to fill in a paper form and get it signed from the authorities before creating a PO. For the hiring example the system entry may happen only once the candidate has passed the initial screening test. Again this process can vary from organization to organization. Now what is there for you to solve this problem? PeopleSoft now offers a feature which will help you to create custom forms in the system. Yes, it is truly custom but not treated as customization and hence you save the additional cost incurred in testing, deployment, maintenance and upgrade.

With the forms and approval builder wizard, you chose what all fields (with your own labels) that needs to be appeared on the actual form. You can also provide instructions to fill in the form and add attachments to the form. Once everything is specified and configured, just activate the form so that users can start working with. It’s not finished yet. Finally you will be able to pull in the data from the forms to a real transaction with the help of CI’s, thus complexly automating your custom scenarios without any customizations. Sounds exciting? Let’s check out how this can be achieved in a PeopleSoft system.

This feature is registered under enterprise components. Let us start with a simple form with just two fields.

Navigate to Enterprise Components > Forms > Design a Form and add a new value.

On the first step provide the basic details of the form such as form name and its valid dates.

Proceed to next step and provide the Instructions that you want to give to the users who fills in the form.

Now proceed to next step. In the PeopleSoft form builder you can have two columns of fields Left and Right columns.

In this example I will add one field to the left column and the other one to right column. If you require only one column then you can fill in the details in the corresponding one grid alone. In the label column specify the name that should be appeared for the field when user fills in the form. The Usage Type specifies the type of data (number, character etc) that is filled in for the field. In this example I will be adding a text and date type fields (applicant name and interview date).

Now proceed to next step. If you want to capture any attachments as part of this form specify the name over here. I will provide Applicant Guidelines as the attachment for the sample form. These extra details will be available for the user when he fills in the form.

Once you are done, click on next step. Now in this step you have to decide where to place this form in the navigational menu. Select the folder and provide the sequence number. The system will automatically generate the portal registry entry for you. In this example, I will register the form under employee self service.

Now proceed to the next step. Here you can decide the approver options. The lockdown option defines whether you want to allow changes to be made to the form after it is submitted. The approver list is the one you configure/create with the approval workflow engine. If you do not need any approvals, leave the form as it is and click on Ok.

Now click on the Activate Form link to make the form ready for the users to start with.

You are done with a sample form now. People can start using the form. Let us have a look at how the sample form will be. Go to the navigation where it is registered (in this case it is Employee Self Service > Sample Form).

You can see that the two fields specified appears in the left and right (as we created it so). Along with that there will be some default field which will be applicable for all the forms. The second and third tab displays the additional information and attachments that you have mentioned while creating the form. Now fill up the form and save it. That’s it, without even opening the application designer; you have automated a manual process within 5 minutes. Isn’t it really great? If you have not started using the forms, start reaping benefits out of it now itself and remember this is not considered as customization so you will be getting support from PeopleSoft on any issues surrounding it.

Want to automate further? Go to Enterprise Components > Forms > Form To CI Mapping.

Add a new value and map the fields created in the form with the property in the corresponding base transaction CI. Now once your form is submitted, PeopleSoft will automatically create an entry in the real transaction tables based on the values entered in the form. This particular portion is advised to be handled only by expert PeopleSoft developers as it involves technical parts.

In-Memory Project Discovery

The buzz words for the decade were “Big Data”, “Unstructured Data”, “In Memory Database”, “Analytics” etc… There were a lot of research and development going around the world around these concepts. Most of the bigger software vendors and startups have already come up with some solutions in these lines for enterprises. One of the major in-memory application available in the market for enterprise application is SAP’s hana. After the acquisition of Sun, Oracle was also following the line with their exa series of machines.

The good news for the PeopleSoft customers is that Oracle has released its first in-memory engineered system for PeopleSoft Project Costing, Program Management and Resource Management (FSCM suite). You might have noticed the term engineered system, it’s how Oracle markets its hardware and software integrated system. For the PeopleSoft In-Memory Project system, the PeopleSoft system is integrated with Oracle middleware (Endeca) into the Oracle hardware (exalytics). The engineered system as a whole delivers extreme performance and analytic powers. The middleware is so powerful that the analytics is done in extremely capable manner. Since the data set is hosted in silicon memory in the exalytics machine, the response time for the queries is much faster than you can imagine.

Watch out the below video for an overview on the new in-memory product from PeopleSoft.

The PeopleSoft Project Discovery which is now available for the customers is designed in much appealing dash board format. The senior officials of an organization will be quickly able to find out and filter various aspects of projects running on the organization in efficient manner. The additional capability includes performing a combined search on your structured and unstructured (eg. Attachments) project data and bringing up the information based on the relevance which was a nightmare earlier.

The project discovery also helps the Resource Managers to quickly and efficiently find out resources across the organization for a project. Based on the criteria, various analytics of the resource such as utilization, geographical spread etc are brought up immediately with a mouse click. There are much more functionality offered in the suite which will definitely be a handy tool for project managers, resource managers and senior officials.

To have an illustration on how this helps your company to get ahead of the industry, read the below article from PeopleSoft strategy blog.

https://blogs.oracle.com/peoplesoft/entry/announcing_the_general_availability_of

Archiving Data in PeopleSoft

Managing data is one of biggest job in bigger industries. Each and every day millions of rows are added into the database which eventually forces the table space to grow and finally results in meager performance of the application. In many cases, most of the data will lose the relevance as days pass by. For example consider the case of a service industry where the assignment of each employee for each day is stored as rows in database. For the ongoing and future assignments, it makes sense to read this data. But think of a situation where you have the 10 year old assignment data for an employee. By this time the employee may not even exists in the company. This is a big space where you could give some relief to your database and physical storage. The idea is to remove the old data from the actual transaction table. But you may have a question in mind like, what if for some reason I need that data? The answer is simple, delete the data from the real table and insert the same into another clone table called history table. This will highly improve the performance of your transactions and reports and will levy some of the load from the db server. This is exactly what is delivered by PeopleSoft called Data Archive Manager. It is a simple and easy method to archive the data on bigger tables in your enterprise. You need not be a PeopleSoft technical or database expert to use this tool. You just have to follow some simple and obvious steps to achieve the mission.

To create an archival system you have follow few steps such as creating history records, defining archival objects and templates and finally running the archival templates. I will be explaining the steps in details in the following sections.

The first step towards archival is creating history tables. Some of the PeopleSoft applications deliver the history tables along with the application. Even if there is no delivered table, you can create your own history table. Open the table for which history table needs to be created and save as <table name>_H and add the subrecord PSARCHIVE_SBR as the first field. Build the table and your history record is ready.

The next step is to create archive objects. This is nothing but defining the tables and corresponding history tables which requires archival. This can be defined at People Tools > Data Archive Manager > Manage Archive Objects. The one marked as Base Record will the one archived first and the rest of the tables will be archived based on that.

The next step is creating archival queries. It is nothing but PS QUERY of type Archive or Restore. This query should contain the where clause to filter the data that needs to be selected for archiving.

Once the above steps are done, you can create the Archival template. A archival template defines what tables needs to be archived, what is the criteria for selecting for archival and what are the process that needs to be done before and after archival. It can be accessed at People Tools > Data Archive Manager > Manage Archive Templates.

By this you have successfully defined the archival templates and process. Now all you need to do is to run the archival template. Run the process by going to People Tools > Data Archive Manager > Archive Data To History and providing the template which is created in the previous steps.

Decoding Project Items Table

As you all might know, PeopleSoft is a table driven application. i.e PeopleSoft stores most of its meta data in the PeopleTools tables/Metadata tables. The same is applicable for the projects you create in application designer. All the objects that you include in the project are stored as a separate row in a PeopleTools metadata table called PSPROJECTITEM. In normal situations you need not bother about these tables as these are purely reserved for PeopleTools internal processing. But there will be some specials scenarios where you need to drill into the metadata tables. This is especially applicable when something wrong happens in production and if you are in charge of analyzing which project or change broke the code line in your production system. I have formulated a sql which will list all the objects with their name and type as it appears in the application designer. I thought of sharing it over here so that anyone looking for similar information is also benefitted. The sql provided below is a simple sql which decodes the object type code used by the PeopleTools. But the real benefit comes if you can take the decoded values and join with other criteria which can directly fetch an impacted project name from the database instead of investing a lot of your valuable time in manually searching for projects. The intention is to give you a hint to start with.

The sql provided below is supposed to work fine for Oracle database and PeopleTools version 8.53. If you have any other database, make the appropriate changes to the sql to fetch the result.

Select

rownum as Serial_Number,

decode(objecttype,

0,'Record',

1,'Index',

2,'Field',

3,'Field Format',

4,'Translate',

5,'Page',

6,'Menu',

7,'Component',

8,'Record PeopleCode',

10,'Query',

11,'Tree Structure',

12,'Tree',

6,'Access Group',

17,'Business Process',

18,'Activity',

19,'Role',

20,'Process Definition',

21,'Server Definition',

22,'Process Type',

23,'Job Definition',

24,'Recurrence Defn',

25,'Message Catalog',

29,'Business Interlink',

30,'SQL',

31,'File Layout',

32,'Component Interface',

33,'App Engine',

34,'App Engine Section',

35,'Message Node',

36,'Message Channel',

37,'App Message',

38,'Approval Rule Set',

39,'Message PeopleCode',

40,'Subscribe PeopleCode',

42,'Comp Intfc PeopleCode',

43,'App Engine PeopleCode',

44,'Page PeopleCode',

45,'Page Field PeopleCode',

46,'Component PeopleCode',

47,'Component Rec PeopleCode',

48,'Comp RecField PeopleCode',

49,'Image',

50,'Stylesheet',

51,'HTML',

53,'Permission List',

54,'Portal Registry Defn',

55,'Portal Folder',

56,'Portal CREF',

57,'App Package',

58,'App Class',

62,'XSLT',

64,'Mobile Page',

68,'File Reference',

69,'File Type',

70,'Archive Object',

71,'Archive Template',

72,'Diagnostic',

73,'Analytic Model',

79,'Service',

80,'Service Operation',

81,'Handler',

82,'Service Oper Version',

83,'Routing',

84,'Queue',

85,'BI Template',

86,'BI Report Defn',

87,'BI File Defn',

88,'XMLP Data Source',

89,'WSDL',

90,'Schema',

91,'Connected Query',

92,'Logical Schema',

93,'Physical Schema',

94,'Relational Schema',

95,'Logical Schema Dep',

96,'Document Schema',

97,'Cube Dimension',

98,'Cube Outline',

99,'Cube Connection',

100,'Cube Template',

101,'Delimited Schema',

102,'Positional Schema',

103,'App DataSet',

104,'Test Framework',

105,'Test Case',

106,'App DataSet Binding',

107,'Feed',

108,'Feed Category',

109,'Feed Data Type',

110,'JSON Schema',

111,'RC Service Defn',

112,'RC Service',

113,'RC Service Config',

114,'RC Layout',

115,'Search Attribute',

116,'Search Definition',

117,'Search Category',

118,'Search Context',

119,'Integration Group') As Object_Type,

objectvalue1||decode(objectvalue2,' ','','.'||objectvalue2)||decode(objectvalue3,' ','','.'||objectvalue3)||decode(objectvalue4,' ','','.'||objectvalue4) As Object_Name

from psprojectitem where projectname = 'YOUR_PROJECTNAME';

Less known properties of Session Class

Session is the one of the API classes provided by PeopleSoft to interact with system variables. PeopleCode developers writing code for component interface must have been very much familiar with the session class. People might have visited the session class documentation in PeopleBooks as well. I have visited the page many a times. But what had surprised me was some of the properties of session class which may prove to be very handy when you work for localization, has never caught my attention. Normally these properties will not catch your attention.

The Session Class has three major classes as its property. The PS Message Class, Regional Settings Class and Trace Setting class. Everyone who might have worked with CI’s might be already familiar with the Message Classes. And the Trace Class is to enable various trace parameters via PeopleCode. What I feel most useful and the less known is the Regional Settings class.

If you are working for a customer who is spread globally and you are asked to write code which will work for each regions, then the Regional Setting class will become handy. Some of the major properties of the regional setting class are ClientTimeZone (returns the time zone of the client), Currency Format, DateFormat, DateSeparator, LanguageCode etc. There are some more properties for this class, which you can get by referring PeopleBooks. So what is the highlight? Each of these properties, take example of DateFormat, will return the format in which dates needs to be displayed for that particular logged in user based on the user preference, browser settings, market and overall settings. So finally the property will return a value specifying which format should be used to display a date for a user in a region using that specific browser.

Similarly the Date Separator method will return the separator that should be used to display dates for a user. You can use these properties and display items to a user as per his own regional settings. Now the next question is how to use these properties. You cannot use the property directly because it is a property of a property of the session class. Below is an example of how to use the properties pertaining to regional settings.

&sSeperator = %Session.RegionalSettings.DateSeparator;

Interested in exploring all the available properties? You can visit the below PeopleBook link to get the details on all the available properties.

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

Mouseover Popup Pages in PeopleSoft

When you are browsing over internet, you might have seen web pages which will show a single dotted underline over certain words or texts in the page. Once you hover your mouse over such words, you can see that an explanation of the word pops ups in a small rectangular region as an additional layer over the existing page and once you changes the mouse pointer, the context popup is also gone. Have you ever thought that what happens if this feature is available in PeopleSoft? Yes, now this feature is available in PeopleSoft and is called as Mouse over Popup Pages. Currently PeopleSoft delivers mouse over popup pages for Applicant and Employee details in HRMS and Sold To, Bill To and Ship To customers in PeopleSoft FSCM.

What this means is, in selected pages, whenever you hover over an employee id in the page, a popup page displaying the details of the employee will be displayed to you.

Fig 1. MouseOver popup Page (image from PeopleBooks)

Right now the mouse over popup pages are limited and any addition of the fields to these pages are considered as customization.

Are you interested in creating similar popup pages for fields which are not delivered by PeopleSoft? It is a simple 3 step process. Follow the below steps to have your own popup pages assuming you have a tools version of 8.53 or higher.

Step 1: Create a page with the details you need to be displayed. Make sure the keys of the primary record of this page are also present in the context where you use it.

Step 2: Save the page and set the page type as Popup Page in the page properties.

Fig 2. Page Settings

Step 3: Now open your transaction page and select the field to enable popup. On the Page field properties of the selected field, go to the USE tab and Select Mouse Over Popup Option as Page Popup  and provide the page name you have created in the previous step. Make sure all the keys required for the popup page is present in the context.

Fig 3. Page Field properties

Now that you have enabled a related content as the popup page for a specific field, what if my popup is always constant or I may want to display some sort of help text. If that is the scenario, then it is much simpler. You need not have to create a popup page. Instead you can create a message catalog entry and provide the Mouse Over Pop option as Message Catalog Popup  and provide the message number (step 3 of above process).

Special fields in PeopleSoft

PeopleSoft reserves some of the fields with some special characteristics and those fields behave differently from other fields. These fields are meant to be used only for some specific purposes. I will be discussing about two such fields, OPRID and EFFDT, in this post.

OPRID is the field which stores the operator id of a user in the PeopleSoft system. The users use operator id to log in to the PeopleSoft system. PeopleSoft, hence, has a special processing routine for this field. One of the interesting things you can note is by trying to make the field OPRID as a KEY field to any of the table and try to load the data using component processor. Even though you have more than one row of data for that specific table, PeopleSoft always loads only one row of data for the table. And if you observe, you will note that the one row is the row specific to the logged in user. What it means is, PeopleSoft will automatically append a condition OPRID = %OperatorId in the where clause when the data is loaded from the database. So the big question is, how can I overcome this situation? There is one workaround to overcome this situation. The workaround is to check the List Box Item property of the field in the record field definition. Now if you load the page, you will be able to see the full data list. I am still not sure whether this is a bug or it is designed so.

Another interesting and most widely used field is EFFDT. EFFDT is not a normal date field. PeopleSoft has many in-built logic wrapping this field. So if your purpose is to store a date, it is better you use a different date field. The main specialty of the field is, if you use this field then whenever you load the data on the page it will always display the rows of data which is effective as of today and future. To view the past data, you need to log in with Include History mode. But still you won’t be able to edit the data. To edit the data you need to be visiting the component with Correct History mode. All these are captured automatically by PeopleSoft. Also this field has special treatment in some queries, where the effective dated row is automatically returned by the query. Another advantage of the field is, if you have this field on any of your table, then whenever you add a second row, all the details from the previous row will be automatically populated in the new row as well. If you have noticed, you can see another specialty like, if you have this field on a table, then the same table can be used as level 0 and level 1 record. You can include all the keys upto EFFDT into level 0 and the rest of the fields in level 1. And the search and component behaves like a normal parent child relation.

Embedding external pages on a PeopleSoft page

Although rare, you may have come across a situation where you want to display an external website or a portion of external website within your PeopleSoft transaction page. For instance you have your expense report creation page and you may want to display all the recent policy changes related to expense reimbursement as a scroll or news bar within your PeopleSoft page. And your company might already have hosted the relevant information in your company portal. The traditional options you might try out is to provide a link to the company portal or duplicate the information in PeopleSoft. I will talk about an alternative to handle this scenario, which is displaying the relevant portion of your company portal site within the PeopleSoft page. This option is best suited only if your PeopleSoft transaction page has the necessary real estate to accommodate the external site.

To customize the page generated by People Tools, PeopleSoft has provided a mechanism called “HTML Areas” to include your own custom HTML codes. You can extrapolate this option and can extend this to bring up an external site within the PeopleSoft page. iFrames are one of the best options available to bring up other contents without disturbing the existing landscape or architecture.

Let us take a step by step approach on how to bring up a third party page to PeopleSoft page. For that you have two cases. First one is you know the URL upfront and the second case is the URL to be opened up is dynamic.

Case 1: Static URL

Step 1: Open the page in application designer and draw an HTML area of size that you may want to dedicate for the external site.

Step 2: Open the properties of HTML area and on the HTML tab select the value type as Constant.

Step 3: On the edit area copy paste the below code. Remember to replace the URL with the URL which you may want to bring up on the page.

<iframe src="http://www.google.com"></iframe>

Step 4: Save the page and start testing  J.

Case 2: Dynamic URL

Consider the case of bring google page itself on your PeopleSoft page. But this time you want to bring up the google result page directly based on value specified on another field on the PeopleSoft page. So the target external URL may vary from each transaction in this case. The steps are pretty much same except for minor variations.

Step 1: Open the page in application designer and draw an HTML area of size that you may want to dedicate for the external site.

Step 2: Open the properties of HTML area and on the HTML tab select the value type as Field. Provide the record name and field name of any work record field that you may want to use.

Step 3: Now open your page activate event and insert the below code. Note: The event can vary depending upon your requirement.

Rem Change the &sURL value as per the target page you want to bring up;

&sURL = “http://www.google.com/#bav=on.2,or.&fp=1&q=” |TRANSACTION_RECORD.MY_FIELD.Value ;

DERIVED.HTMLAREA.Value = "<iframe src=""” | &sURL | “””> </iframe> “;

Step 4: Save the code and start testing  J.

Overriding Temporary Table Instance

The temporary table instance in an application engine program is controlled by the PeopleSoft processor and it is assigned whenever you start an application engine program. Recently I have seen some threads in online forums asking whether we can set the temporary table instance while running an application engine program. Well, the answer is both Yes and No.

The default instance of a temporary table is decided by the PeopleSoft processor and it is done when you initiate an application engine program. But PeopleSoft has provided some options to override this instance number via PeopleCode. You have a delivered built in function SetTempTableInstance which overrides the existing temporary table instance. The function will return the previous instance number. Once this function is called the entire % Table construct thereafter will then start using the new instance.

/* Change the temporary table instance to 3 */

&nPriorInstance = SetTempTableInstance(3);

/* Change the instance back once you are done. */

SetTempTableInstance(&nPriorInstance);

You should be very careful while using this function. This function is not provided to entirely override the default instance that was created. If you have a scenario where you want to process same set of data temporarily without affecting the already selected data in the default processing, then you can consider using it. Whenever you use this function, try to revert the changes at the end by using the same function so that your default processing is not affected.

Whatever way you are using this function to override the temporary table instance, the key thing to keep in your mind is that you should have a complete control over the data that is handled in the tables. Otherwise you may see varied behaviors in your output.