%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.