Enabling Auditing in PeopleSoft

It is very important that you keep track of the changes happening to your key or sensitive transaction records. This will help you in determining who has changed what record and at what time. Instead of searching in the darkness after having a mishap with your data, it is always better to keep track (audit) the changes that is happening to your key records. Towards this purpose PeopleSoft has provided two different methods to audit your records.

PeopleSoft provides two levels of auditing, Field Level Auditing & Record Level Auditing.

Field Level Auditing

You can use this level of auditing if you want to track each and every change happening to a particular field in a record.  You can enable as many as fields for auditing in a record. To enable field level auditing, open up the record field properties. There you will get three options, select the desired options.

Fig 1. Field Level Auditing

Field Add option will track whenever a value is added to that selected field. Checking Field Change option will create an entry whenever you change the value of the field. By checking Field Delete option, a new row is created in the audit record whenever the field value is deleted.

All your audit options are tracked in a PeopleSoft delivered audit record called PSAUDIT. The audit record will capture the details like who changed the field, what time the change was made, what was the change, the field name, old values and new values and the keys for your parent record.

AUDIT_ACTN field stores what was the change made to the field. The values of this field can be interpreted as follows.

A – Added new value or row

C – Changed the existing value or row

D – Deleted the old value or row

K – Row updated, Old Value

N –Row Updated, New Value

O – Original Value

You can check the changes to your base record field by using a query similar to the one below.

select * from psaudit where recname = '<Your Record Name>' and fieldname = '<Your Field Name>' and key1 = '<First Key Value for your base record>' and key2 = '<Second Key Value for your base record>' <and so on till you map all the keys>

Record Level Auditing

Field level auditing is auditing each fields on your record and will be creating a new row for change in all the fields. This can grow up your audit record size and end up in performance issue. Also if you have many fields to audit in a record, then it becomes difficult for you to see through the report to see all the actions done on one particular record as it will result in multiple rows.

To tackle this situation PeopleSoft has provided another level of auditing called Record Level Auditing. With record level auditing, you can enable the audit for entire record and select the fields to be included in the audit. This will be particularly helpful when you have multiple fields in a record to be audited.

For creating record level audit, you need to create a new audit record for your base record. This record should follow certain standards. The easiest way to create an audit record is to open your audit record and save it as AUDIT_<Your Name>. Once the audit record is saved you can delete the unwanted fields (fields which need not be audited).

Other important steps you need to make are as follows.

1.       Remove all your keys. Audit records are not supposed to contain keys.

2.       Remove any Query Security record associated with it.

3.       If there are any Parent record associated, then remove it.

4.       Remove any PeopleCode associated with the audit record.

5.       Add the below delivered fields as required

   a.       AUDIT_OPRID – This field will capture the operator id of the person who has made the change.

   b.      AUDIT_STAMP – This field stores the date time stamp at which the change is made.

   c.       AUDIT_ACTN – This field stores what action was taken to the record. The values are as below.

                                                               i.      A: Row is inserted

                                                             ii.      D: Row deleted

                                                            iii.      C: Row changed (updated), but no key fields changed.

The system writes existing values to the audit table.

                                                           iv.      K: Row changed (updated), and at least one key field changed.

The system writes existing values to the audit table.

                                                             v.      N: Row changed (updated), and at least one key field changed.

                                    The system writes new values to the audit table.

   d.      AUDIT_RECNAME – This field stores which record was audited. Include this field only if you are using the same audit record for multiple base records.

Once you are done with it, you are ready with your audit record. Now to enable the auditing for your base table, open the base record properties and go to the use tab. There you should specify the name of the new audit record you have created and check the audit actions that you need to enable for the base record.

Fig 2. Record Level Auditing

As opposed to the field level auditing, you have an additional option called Selective. This will insert a row in an audit table whenever a value of the field included in the audit table is changed in the base table. If you click on change option, then the audit record will capture the row regardless of whether it is included in the audit record or not.

With this, you need to make a special note that the auditing will happen only if the user adds/deletes/changes the data from PeopleSoft application. If the data is changed by sqls in backend or via any third party, PeopleSoft audit tables will not capture these details. To capture that details as well, then you need to consider enabling database triggers.

To enable database level triggers, PeopleSoft has delivered some options by going to the online portal. You can define your triggers by going to People Tools > Utilities > Audit > Update Database Level Auditing

Fig 3. Database level auditing

Top 10 Innovations in PeopleSoft 9.2 – 2 PeopleSoft Update Manager

Of all the innovations done with the release 9.2 this is my favorite. With the new PeopleSoft Update Manager, it is totally going to change the way how you maintain your system. You no need to apply any more bundles or maintenance packs for just one bug you have reported with the oracle. Yes, its true. Now with the new system, you can select which all bugs needs to be applied and the system will automatically create a change package which you can apply blindly. Wait; did I say blindly? Yes because you no more have to apply the pre-requisites or post-requisites. Whenever you select your bug, the system will automatically select the pre-requisites required and the same will also be included in the change package created for you. So you can directly apply the package.  And with the PeopleSoft Test Framework (PTF) you can easily do the regression testing on your test database.

For selecting the bug, PeopleSoft will be providing you the latest image which contains all the fixes and enhancements and you can choose whichever you want to implement. Really reducing your Total Cost of Ownership and innovative. Isn’t it?

Check out the video at youtube.

Top 10 Innovations in PeopleSoft 9.2 – 3 Enhanced Self Service in SRM

The major improvement part in PeopleSoft SRM 9.2 is the automation of supplier on-boarding process. Say bye to the old process of filling form, emails, phone calls and fax. Now with the new release everything is automated and supplier can added to the system by a step-by step process automated based on your business need. We can even set configurable approval process before the supplier is made active.

eProcurement is made more intuitive by providing a common shopping site experience, where in you could search or scroll by the products and add them to the cart and finally prepare an order for the selected items. With the integration with SES, searching for the product has became much easier. The same feature is available with mobile application also which is further more colorful and attractive

For more details read the Oracle Apps Strategy blog.

https://blogs.oracle.com/peoplesoft/entry/10_innovations_in_peoplesoft_92

Deleting rows from a rowset programmatically

When you are dealing with components having grids and scrolls and having complex rules beneath, it is a common scenario where you need to update or delete the rows in the rowset programmatically based on the business rules. There is a PeopleSoft delivered method called “DeleteRow” for the rowset class which you can use to delete the row. Most of you must be familiar with it. Anyhow I will give a pseudo code which will do a delete action on a rowset.

&rsMyRowset.DeleteRow(&nRowNum);

Here &nRowNum is the variable which will hold the index of the row to be deleted. If &nRowNum = 2, then the above statement will delete the second row of the rowset. But there is a common mistake or error people tend to make when using the DeleteRow method. For instance consider the below table and sample code.

Table: EMPLOYEE

Row Number	Employee ID	Employee Status	Country
1	45365	A	USA
2	45366	I	IND
3	45367	I	CHN
4	45368	A	GBR

Suppose your requirement is to delete the Inactive (I) employees from grid. Most of the people might be writing code shown as below.

Local Rowset &rsEmployee;

/* Assuming the grid is on Level 1 */

&rsEmployee = GetLevel0()(1).GetRowset(Scroll.EMPLOYEE);

For &I=1 To &rsEmployee.ActiveRowCount

 If &rsEmployee(&I).EMPLOYEE.EMPLOYEE_STATUS.Value = “I” Then

  &rsEmployee.DeleteRow(&I);

 End-If;

End-For;

The above code may work for some scenarios, for example if you have only one inactive employee and he is in the last row. But most of the cases, when doing the transaction the system will throw a peoplecode error stating “Invalid row number”.

To resolve the issue, you need to know how peoplesoft is handling the deleterow method. Whenever you delete a row, that row is not deleted from the buffer. It is present on the buffer. The two things PeopleSoft does to make that row as deleted are

a.       In the component buffer, the system will flag the row as deleted. You can check this by code using IsDeleted property.

b.      The second and most crucial step PeopleSoft does is; it will rearrange the rows in component buffer. The deleted rows are moved to the end of the rowset stack and all the other subsequent rows are pushed one step further. For example in the previous table, when you delete the second row, the second row is moved to the fourth position and the 3^rd and 4^th rows are pushed to the second and third positions respectively.

The reason for the error you get is the second one mentioned above. When you delete the second and third row, the grid in the buffer structure is re-arranged in the buffer structure as shown below.

Row Number	Employee ID	Employee Status	Country	Is Deleted
1	45365	A	USA	False
2	45368	A	4	False
3	45366	I	IND	True
4	45367	I	CHN	True

So when the iteration goes to the fourth round, there is no fourth row as it is marked as deleted. So obviously, the below statement will produce an error “Invalid row number”.

If &rsEmployee(&I).EMPLOYEE.EMPLOYEE_STATUS.Value = “I” Then

Because the row &I=4 never exists. Even though there is a fourth row, since it is marked as deleted, it is ignored.

How to overcome this error? Those who are not familiar with this feature might think it is a difficult task to delete a row by iterating. But the answer is quite simple. Instead of looping from the first row to last row, you do the looping from last row to the first row. If you do that, even if the component processor shifts the deleted rows to the last row, the position or index of the rows which occur earlier to the present row remains unaffected.

For the same example I mentioned above, you should be re-writing the code to the one shown below.

Local Rowset &rsEmployee;

/* Assuming the grid is on Level 1 */

&rsEmployee = GetLevel0()(1).GetRowset(Scroll.EMPLOYEE);

For &I= &rsEmployee.ActiveRowCount To 1 Step -1

 If &rsEmployee(&I).EMPLOYEE.EMPLOYEE_STATUS.Value = “I” Then

  &rsEmployee.DeleteRow(&I);

 End-If;

End-For;

Feel free to add any feedback or modifications in the comments section.

Providing Security to PS Queries

PS Query is user friendly (at least for developers J) to pull out and see data residing in database tables. Usually business users are given access to PS Query components to create and view new/existing queries. These data they will be able to be downloaded to their system in different formats. This posses a critical risk on your sensitive data. Displaying or exposing data to a wrong set of users is a real threat and if sometimes auditors will catch it, it can affect your company certification as well other than the data exposure threat.

PeopleSoft has provided 3 different levels of control to restrict your data to only the right set of users. I will try to provide a brief idea on the various security levels available for PS Queries.

1.       Component Security

You will get delivered Query Manager, Query Viewer and Schedule Query components with your application package.  Not all of your business users may need to access data other than from the transaction components. Usually very few core business users would need transaction data’s that need to be exported and processed further. So restrict the access to these components via permission list and assign the permission list to only the right set of users. This should be your first level of security to prevent the data explosion risk.

2.       Query Security Trees

You may have different set of users acting on the system. For example if you consider the financial domain, you will have accountants who deals with the accounting entry and you may have a separate procurement team who will be concerned only with procuring goods for the company. Both the team might have requirement to access PS Queries. So you will be providing access to the PS Query components. But if you look further, the procurement team should not be given the opportunity to view accountant’s data at general ledger and vice versa. To tackle this situation, PeopleSoft has provided another layer called Query Security Trees. Basically at this level you will be restricting the access to each back end tables (records) to the concerned groups only. If you do not add a record to a query security tree which is assigned to a user via permission lists, that user will not be able to view or create a new query containing that particular record.

Steps to be followed to provide this level of access is as follows.

a.       Go To: PeopleTools > Security > Query Security > Query Access Manager. Open a query tree corresponding to your module. If it doesn’t exists, go ahead and create one.

b.      Add your record as a node to the tree and save.

c.       Now you should associate the Query Tree with a permission list. For that open the permission list (People Tools > Security > Permission & Roles > Permission List) and go to the Query Tab.

d.      Click on the Access Group Permissions and provide your Tree and Branch names and save it.

e.      Assign the permission list to a role and the role to a user.

With the above steps you have successfully provided the second level security for the data, i.e individual record level security.

3.       Query Security Record

Implementing the above will put in place most of the security to the data that you require. But there are some cases where you want to add more security. As per the setting above, the user has full data access to the records assigned to the query security tree. But what about the data inside the record? Consider the case of HRMS where you have sensitive data. For example take the PERSONAL_DATA record. The user getting access to other people’s personal details is not acceptable. To overcome this situation PeopleSoft has delivered the third layer of security via Query Security Records also called as row level security.  Query security records are views which will select the data from the base table and also adds additional where clause to restrict the data fetched.

You can follow the below steps to implement row level security for PS Query output.

a.       Create a view which fetches the desired data from the base table

b.      Add all the keys of underlying record to the view

c.       Add one more additional key from OPRID, OPRCLASS & ROWSECCLASS. PeopleSoft will automatically create the where clause for this extra field.

d.      Now add the record created to the Query Security Record field of the underlying records property in the Use tab. PeopleSoft will automatically join this record with the underlying record when the Query is executed. You can verify the same by looking at the SQL statement of the PS Query.

Once you have done with these three steps, you are done with all the three layers of security. Want more? Do you want to hide the data based on values of multiple columns? As per my best knowledge there are no other options now.  J  You can leave the comments, if you know some methods. Otherwise let’s wait until People Tools brings up row level security to multiple fields.

Mobile Apps

Although late in the game, PeopleSoft has delivered compelling mobile applications to their customers. There were prior mobile applications which were delivered with Oracle ADF technology, but it was not catching the customer attention due the intricate problems. The latest on the suit is best in class and innovative solution delivered with jQuery, CSS3, HTML5 and REST services. The main highlight of the release is that it comes free of cost for the existing 9.1 customers. Below is the list of few applications which were delivered in 9.1 bundles.

1.       PeopleSoft Mobile Inventory

2.       PeopleSoft Mobile Approvals

3.       PeopleSoft Mobile Expenses

4.       PeopleSoft Mobile Company Directory

5.       PeopleSoft Mobile eProcurement

All these applications are tested for various mobile devices listed below.

1.       Samsung Galaxy SIII (Android)

2.       Apple iPhone 4 & 4S (iOS 5.1)

3.       Apple iPad2 & iPad3 (iOS 5.1)

4.       Google Nexus Tablet (Android)

5.       Samsung Galaxy Tablet 10.1(Android)

Don’t get misguided by the term Mobile App. It is not like your normal mobile applications which you will download from the web stores for Android, Windows or iOS markets. The PeopleSoft mobile applications are completely web based applications with fluidic design. What it means to you is that none of your company mobile users need to download the application and install it on their phone. Users just needs to go to the mobile home URL provided by their company from their mobile web browsers. Believe me; the UI looks far superior to most of the native mobile application clients. Apart from that, since it is a web based application, users will get additional benefit of using the application from other devices (other than that mentioned above) which supports HTML5 , CSS3 & javascripts without much distortion to the content.  Yes, web based applications have its own drawback as it will work only with an internet connection; but I don’t think that will be a limitation as there are very rare smart phone users without an internet connection.

So far so good, but the major question will be how will my company uptake the mobile applications provided by the PeopleSoft. It is very simple. PeopleSoft has delivered the mobile applications through their general maintenance approach of bundles. What you need to do is to simply upgrade your applications to the latest bundle. For example, PeopleSoft Mobile Expenses is delivered in 9.1 FSCM bundle# 23.

See the below video to get a glimpse on PeopleSoft Mobile Expenses. You can  other  apps demo in You tube PSFTOracle Channel.

I know I haven’t answered your question completely. How will you get the URL that the user’s needs to be browsed in their mobile web browser? Simplest method is go to your PeopleSoft portal: Main Menu > Employee self Service > Mobile Applications. There you will see a list of applications available in your package. Select the required one and copy the URL from the browser address bar. If you paste the same URL in your mobile application, you will see the general login page and with successful login, your mobile application is launched in your browser. But if your company firewalls and network security will not allow to use the PeopleSoft URL outside the company network or if your company wants a separate URL , then you can create a separate PIA site and pass that particular URL to your users.

Now you may think how secure is the mobile applications? To understand that you need to know the underlying technology. PeopleSoft has used jQuery to implement mobile applications with the help of iScripts. So how the page is rendered on your mobile device is via iScripts. And all your data is handled by either PeopleCode or by Component Interface. What does it means? It means all the objects are managed objects and you can give separate access to the objects like iScript and CI using your permission lists. So if a user is not having access to a permission list which contains the iScript, it means that page will not be visible to the user like normal PeopleSoft pages or components access control. For instance, Expenses use iScripts in the weblibrary WEBLIB_MBL_EX. Giving access to the scripts in the weblibrary to a user means; he is getting access to a mobile page and related services.

To get a more detailed view on implementing mobile apps in FSCM vistit the below link. HCM app will also be on the similar lines, only some minor details like bundle levels and update id’s change.

http://bloggingaboutoracle.com/peoplesoft-mobile-applications/

What more? You can create your own mobile applications with very less effort if you have a resource with jQuery & PeopleCode skills. You need not be a jQuery or javascript guru to develop it because you will be using it only for the look and feel. And in most cases importing the jQuery library’s itself will do half of the job. Do your data populations and logics using PeopleCode & CI’s. Just give a try, it will be very interesting once you start with. 

PeopleSoft HCM 9.2 Video Overview

Checkout the latest PeopleSoft 9.2 HCM, the game changing innovative suite.

Top 10 innovations in PeopleSoft 9.2 – 4 managing daily life with PeopleSoft HCM 9.2

The fourth most innovative feature in PeopleSoft 9.2 release is the easy management of life events with PeopleSoft HCM 9.2. The feature description starts with user logging in to a collaborative company interaction hub and searching for life events using SES search. From there on, the user is taken to directly to the page to add the life event in order to update the benefits available. The life events activity guide allows the user to complete the steps with less effort by the help of interactive guided navigations. Later the user searches for a better job posting within the company that too without navigating away from the pages. The dashboards available in the PeopleSoft 9.2 release help the user to analyze various statics and take appropriate actions. With the help of pivot grids, user is able to get a snap shot of the performance review status and take action on it. And the story goes on…

For complete story board visit https://blogs.oracle.com/peoplesoft/entry/10_innovations_in_peoplesoft_4

8 different methods to run a PSQuery

PS Query might appear as simple and less extensible tool provided by PeopleSoft. But in real world, some business users cannot even live without a PS Query. They prioritize their daily task based on some query output. What all simple information required by the user can be made available with this simple tool. Further even business users can have the ability to create a query of their own.

I will discuss here the different methods to utilize this simple tool, so as to extend the power of simplicity.

 1.       Query Viewer

This is the simplest and straight forward method to run a PS Query and view the output. To try this method, go to: Reporting Tools > Query > Query Viewer and give the query name. You will have the option to view the output online (HTML), as excel or xml file.

 2.       Schedule Query

If you are a regular user of PS Query and you run the query daily to get a list of transactions that you need to work on, then this is the best option you can try out. Schedule your query for a specific time frame and set the destination as email. So that you will get regular emails in your inbox and you don’t need to open the PeopleSoft page to see the list of actionable items.

To schedule your query, go to: Reporting Tools > Query > Schedule Query. Add a new runcontrol, select your query and click on run. On the run page makesure that you set the correct Recurrence option so that it is run daily/weekly/monthly etc…

 3.       Register as a content

Another prominent mechanism to reach out to your query is to register it as a content reference in the portal. PeopleSoft users are used to the culture of opening pages and components by the menu navigational structure. So registering the query as menu navigation link will be the best method for the traditional PS user community. Like opening any PeopleSoft component, you will be able to open the PS Query by going through the navigation and clicking on the link.

To register the PS Query as a menu link create a new content reference in the desired folder with the below options.

a.       In the URL Information group select the URL Type as PeopleSoft Generic URL

b.      In the Portal URL box, give the URL as q/?ICAction=ICQryNameURL=MY_QUERY. Replace the term MY_QUERY with your actual query name.

 4.       Running from a Push Button

This method is most suited when you want to run a PS Query from a push button or a hyperlink on your transaction page. You can think of this if your main truncation has some child transaction for which some action should be performed. In such scenario’s you can create a hyperlink/push button on the main transaction page, which will fetch all the child transactions for which actions are required.

You can implement it in two ways.

 a.       Create a push button/ hyperlink and set the destination as process. Now you can give the process name as PSQUERY and type as Application Engine. If your query contains prompts, it is your responsibility to pass the run parameters and destination types.

 b.      You can set the target of push button/hyperlink as PeopleCode and generate the query URL using any of the built in functions such as GenerateQueryContentURL, GenerateQueryPortalURL and GenerateQueryRelativeURL. Use these functions in conjunction with ViewURL() function to launch the query. This method have the advantage of opening the query results directly on the page or a new window.

 5.       Accessing query as a related content to a component or page

From tools 8.52 onwards you have this pretty stable option of linking related contents to a component or page. You can attach your query as a related content to your transaction component so that when you select it the query will appear on a separate frame on the right or bottom side of the existing component. This method has the advantage of (a) it will not disturb the real estate and UI of the existing page and (b) this method is a zero customization method of linking a query to a transaction component. For further details on creating related content you can refer my previous blogs on Related Contents and Related Content Services.

 6.       Query access from Feeds

From Tools 8.50 onwards you have the option of subscribing to a PS Query with RSS Feeds. To subscribe to a PS Query, you need to publish it as a Feed. To do that go to: Reporting Tools > Query > Query Manager and open the query. On the bottom, you have a hyperlink called Publish as Feed. Click on that give your options. Now on top of your query manager page you will get the feed option enabled. Clicking on it will take you to the feed URL. Use the URL to subscribe to the query using any standard subscriber tools. You can also retrieve the feed by going to: Main Menu > My Feeds link and search for the feed.

 7.       Query As a Service

This is another interesting option provided by PeopleSoft to invoke a PS Query from a third party application or external system. PeopleSoft has delivered Service and Service Operations to execute your query from an external source. You can utilize the power by enabling the service QAS_QRY_SERVICE service and its related service operations. To use this feature you need to add the service operations to the necessary permission lists.

 8.       Query from WorkCenters

With the release of PeopleSoft 9.2, PeopleSoft has introduced a one stop place to do all your works. Yes, your WorkCenters will also support PS Query’s. You can access the PS Query by going to the Report/Queries tab of the WorkCenter.

To add your own query to the WorkCenter, navigate to: Enterprise Components > WorkCenter/Dashboards > Configure Pagelets, open your WorkCenter and go to the tab Queries and add your query there. Remember to click on Activate Query Settings button to activate the changes you have made to the WorkCenter.